-- GDS Workflow Tables
-- Run this SQL in phpMyAdmin to create the necessary tables

-- First check if gds_files exists, create if not
CREATE TABLE IF NOT EXISTS gds_files (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    agency_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NULL,
    filename VARCHAR(255) NOT NULL,
    original_filename VARCHAR(255) NOT NULL,
    gds_source VARCHAR(255) NOT NULL,
    file_type VARCHAR(255) NOT NULL,
    records_count INTEGER DEFAULT 0,
    file_path VARCHAR(255) NOT NULL,
    status ENUM('pending', 'processing', 'completed', 'failed') DEFAULT 'pending',
    error_message TEXT NULL,
    parsing_results JSON NULL,
    created_at TIMESTAMP NULL DEFAULT NULL,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    INDEX (agency_id, status),
    INDEX (gds_source)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- GDS Records table - stores parsed passenger records from GDS files
CREATE TABLE IF NOT EXISTS gds_records (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    file_id BIGINT UNSIGNED NOT NULL,
    agency_id BIGINT UNSIGNED DEFAULT 1,
    record_number INTEGER DEFAULT 0,
    pnr VARCHAR(20) DEFAULT NULL,
    ticket_number VARCHAR(50) DEFAULT NULL,
    passenger_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) DEFAULT NULL,
    phone VARCHAR(50) DEFAULT NULL,
    airline_code VARCHAR(10) DEFAULT NULL,
    airline_name VARCHAR(255) DEFAULT NULL,
    route VARCHAR(50) DEFAULT NULL,
    departure_date DATE DEFAULT NULL,
    return_date DATE DEFAULT NULL,
    amount DECIMAL(12, 2) DEFAULT 0,
    currency VARCHAR(10) DEFAULT 'USD',
    status ENUM('pending', 'processed', 'converted', 'failed') DEFAULT 'pending',
    client_id BIGINT UNSIGNED DEFAULT NULL,
    booking_id BIGINT UNSIGNED DEFAULT NULL,
    invoice_id BIGINT UNSIGNED DEFAULT NULL,
    error_message TEXT DEFAULT NULL,
    processed_at DATETIME DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (file_id) REFERENCES gds_files(id) ON DELETE CASCADE,
    INDEX (file_id),
    INDEX (agency_id),
    INDEX (pnr),
    INDEX (status),
    INDEX (client_id),
    INDEX (booking_id),
    INDEX (invoice_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;