-- Agency Partnership Management
CREATE TABLE IF NOT EXISTS agency_partnerships (
    id INT AUTO_INCREMENT PRIMARY KEY,
    iata_agency_id BIGINT UNSIGNED NOT NULL,
    non_iata_agency_id BIGINT UNSIGNED NOT NULL,
    commission_rate DECIMAL(5,2) DEFAULT 0,
    markup_rate DECIMAL(5,2) DEFAULT 0,
    status ENUM('active', 'suspended', 'pending') DEFAULT 'active',
    created_by INT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_partnership (iata_agency_id, non_iata_agency_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Agency Booking Routing Log
CREATE TABLE IF NOT EXISTS booking_routes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    booking_id BIGINT UNSIGNED NOT NULL,
    from_agency_id BIGINT UNSIGNED NOT NULL,
    to_agency_id BIGINT UNSIGNED NOT NULL,
    route_type ENUM('ticketing', 'reissue', 'refund', 'query') DEFAULT 'ticketing',
    status ENUM('pending', 'processing', 'completed', 'rejected') DEFAULT 'pending',
    notes TEXT,
    processed_by INT DEFAULT NULL,
    processed_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Partner Transaction Summary (for real-time tracking)
CREATE TABLE IF NOT EXISTS partner_transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    iata_agency_id BIGINT UNSIGNED NOT NULL,
    non_iata_agency_id BIGINT UNSIGNED NOT NULL,
    period_month INT NOT NULL,
    period_year INT NOT NULL,
    total_bookings INT DEFAULT 0,
    total_tickets INT DEFAULT 0,
    total_revenue DECIMAL(15,2) DEFAULT 0,
    commission_earned DECIMAL(15,2) DEFAULT 0,
    markup_charged DECIMAL(15,2) DEFAULT 0,
    refunds DECIMAL(15,2) DEFAULT 0,
    net_amount DECIMAL(15,2) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_period (iata_agency_id, non_iata_agency_id, period_month, period_year)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Add routing fields to bookings table
ALTER TABLE bookings ADD COLUMN IF NOT EXISTS routed_to_agency_id BIGINT UNSIGNED DEFAULT NULL;
ALTER TABLE bookings ADD COLUMN IF NOT EXISTS routing_status ENUM('none', 'pending', 'routed', 'ticketed', 'issued', 'rejected') DEFAULT 'none';
ALTER TABLE bookings ADD COLUMN IF NOT EXISTS routed_at TIMESTAMP NULL;
ALTER TABLE bookings ADD COLUMN IF NOT EXISTS ticket_issued_at TIMESTAMP NULL;

-- Add IATA fields to agencies
ALTER TABLE agencies ADD COLUMN IF NOT EXISTS parent_agency_id BIGINT UNSIGNED DEFAULT NULL;
ALTER TABLE agencies ADD COLUMN IF NOT EXISTS is_iata_approved BOOLEAN DEFAULT FALSE;
ALTER TABLE agencies ADD COLUMN IF NOT EXISTS iata_approval_date DATE DEFAULT NULL;