-- Multi-currency support table
CREATE TABLE IF NOT EXISTS currencies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(3) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    symbol VARCHAR(10) NOT NULL,
    exchange_rate DECIMAL(15, 6) DEFAULT 1,
    is_default BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO currencies (code, name, symbol, exchange_rate, is_default) VALUES 
('USD', 'US Dollar', '$', 1.000000, TRUE);
INSERT INTO currencies (code, name, symbol, exchange_rate) VALUES 
('EUR', 'Euro', '€', 0.920000),
('GBP', 'British Pound', '£', 0.790000),
('AED', 'UAE Dirham', 'د.إ', 3.670000),
('SAR', 'Saudi Riyal', 'ر.س', 3.750000),
('INR', 'Indian Rupee', '₹', 83.120000),
('AUD', 'Australian Dollar', 'A$', 1.530000),
('CAD', 'Canadian Dollar', 'C$', 1.360000);

-- Credit control settings
CREATE TABLE IF NOT EXISTS credit_limits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    agency_id BIGINT UNSIGNED NOT NULL,
    client_id BIGINT UNSIGNED DEFAULT NULL,
    branch_id BIGINT UNSIGNED DEFAULT NULL,
    credit_limit DECIMAL(15, 2) DEFAULT 0,
    credit_period_days INT DEFAULT 30,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_client (agency_id, client_id),
    UNIQUE KEY unique_branch (agency_id, branch_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Credit alerts log
CREATE TABLE IF NOT EXISTS credit_alerts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    agency_id BIGINT UNSIGNED NOT NULL,
    client_id BIGINT UNSIGNED DEFAULT NULL,
    branch_id BIGINT UNSIGNED DEFAULT NULL,
    alert_type ENUM('credit_limit', 'payment_due', 'overdue') NOT NULL,
    message TEXT NOT NULL,
    amount DECIMAL(15, 2),
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- BSP Reconciliation
CREATE TABLE IF NOT EXISTS bsp_reconciliation (
    id INT AUTO_INCREMENT PRIMARY KEY,
    agency_id BIGINT UNSIGNED NOT NULL,
    ticket_number VARCHAR(50) NOT NULL,
    gds ENUM('amadeus', 'sabre', 'galileo', 'travelport') DEFAULT 'amadeus',
    issue_date DATE,
    sale_amount DECIMAL(15, 2) NOT NULL,
    cost_amount DECIMAL(15, 2) NOT NULL,
    commission DECIMAL(15, 2) DEFAULT 0,
    tax_amount DECIMAL(15, 2) DEFAULT 0,
    billing_status ENUM('pending', 'billed', 'paid', 'disputed') DEFAULT 'pending',
    payment_status ENUM('unpaid', 'partial', 'paid') DEFAULT 'unpaid',
    bsp_amount DECIMAL(15, 2) DEFAULT NULL,
    bsp_date DATE,
    variance DECIMAL(15, 2) DEFAULT 0,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_ticket (agency_id, ticket_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;