-- Fare Management Tables
-- Run this SQL to create fare markup and cache tables

CREATE TABLE IF NOT EXISTS fare_markup_rules (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    agency_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(255) NOT NULL,
    airline_code VARCHAR(10) DEFAULT NULL,
    origin VARCHAR(10) DEFAULT NULL,
    destination VARCHAR(10) DEFAULT NULL,
    cabin_class VARCHAR(20) DEFAULT NULL,
    markup_type ENUM('percentage', 'fixed') DEFAULT 'percentage',
    markup_value DECIMAL(10,2) NOT NULL DEFAULT 0,
    min_amount DECIMAL(10,2) DEFAULT 0,
    max_amount DECIMAL(10,2) DEFAULT NULL,
    valid_from DATE DEFAULT NULL,
    valid_until DATE DEFAULT NULL,
    priority INT DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX (agency_id, is_active),
    INDEX (airline_code),
    INDEX (origin, destination)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS fare_cache (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    agency_id BIGINT UNSIGNED NOT NULL,
    search_key VARCHAR(255) NOT NULL,
    fare_data LONGTEXT NOT NULL,
    cached_at DATETIME NOT NULL,
    expires_at DATETIME NOT NULL,
    is_valid TINYINT(1) DEFAULT 1,
    INDEX (agency_id, search_key),
    INDEX (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;