-- GDS Integration Tables
-- Run this SQL to create GDS connection management tables

-- GDS Connections (API credentials)
CREATE TABLE IF NOT EXISTS gds_connections (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    agency_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(255) NOT NULL,
    gds_source VARCHAR(50) NOT NULL,
    api_key VARCHAR(500) DEFAULT NULL,
    api_secret VARCHAR(500) DEFAULT NULL,
    username VARCHAR(255) DEFAULT NULL,
    password VARCHAR(255) DEFAULT NULL,
    target_branch VARCHAR(50) DEFAULT NULL,
    target_city VARCHAR(50) DEFAULT NULL,
    test_mode TINYINT(1) DEFAULT 1,
    is_active TINYINT(1) DEFAULT 1,
    last_test_at DATETIME DEFAULT NULL,
    last_test_status VARCHAR(50) DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX (agency_id, gds_source),
    INDEX (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- GDS API Logs
CREATE TABLE IF NOT EXISTS gds_api_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    agency_id BIGINT UNSIGNED NOT NULL,
    connection_id BIGINT UNSIGNED DEFAULT NULL,
    gds_source VARCHAR(50) NOT NULL,
    request_type VARCHAR(100) NOT NULL,
    request_data JSON DEFAULT NULL,
    response_data LONGTEXT DEFAULT NULL,
    status VARCHAR(20) DEFAULT 'success',
    response_time_ms INT DEFAULT 0,
    ip_address VARCHAR(45) DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX (agency_id, gds_source, created_at),
    INDEX (connection_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- NDC Airline Connections
CREATE TABLE IF NOT EXISTS ndc_connections (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    agency_id BIGINT UNSIGNED NOT NULL,
    airline_code VARCHAR(3) NOT NULL,
    airline_name VARCHAR(255) NOT NULL,
    connection_type ENUM('ndc', 'api', 'direct') DEFAULT 'ndc',
    api_endpoint VARCHAR(500) DEFAULT NULL,
    client_id VARCHAR(255) DEFAULT NULL,
    client_secret VARCHAR(500) DEFAULT NULL,
    certificate_path VARCHAR(500) DEFAULT NULL,
    test_mode TINYINT(1) DEFAULT 1,
    is_active TINYINT(1) DEFAULT 1,
    last_test_at DATETIME DEFAULT NULL,
    last_test_status VARCHAR(50) DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX (agency_id, airline_code),
    INDEX (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- PNR Tracking History
CREATE TABLE IF NOT EXISTS pnr_tracking (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    agency_id BIGINT UNSIGNED NOT NULL,
    booking_id BIGINT UNSIGNED DEFAULT NULL,
    pnr VARCHAR(20) NOT NULL,
    gds_source VARCHAR(50) DEFAULT NULL,
    status VARCHAR(50) NOT NULL,
    previous_status VARCHAR(50) DEFAULT NULL,
    status_details JSON DEFAULT NULL,
    flight_details JSON DEFAULT NULL,
    checked_at DATETIME DEFAULT NULL,
    next_check_at DATETIME DEFAULT NULL,
    notification_sent TINYINT(1) DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX (agency_id, pnr),
    INDEX (booking_id),
    INDEX (status, next_check_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;