CREATE DATABASE IF NOT EXISTS air_ticketing_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE air_ticketing_db;

-- 1. Roles & Permissions
CREATE TABLE roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    role_name VARCHAR(50) NOT NULL UNIQUE,
    can_book TINYINT(1) DEFAULT 0,
    can_accounts TINYINT(1) DEFAULT 0,
    can_reports TINYINT(1) DEFAULT 0,
    can_manage_users TINYINT(1) DEFAULT 0
);

INSERT INTO roles (id, role_name, can_book, can_accounts, can_reports, can_manage_users) VALUES
(1, 'Super Admin', 1, 1, 1, 1),
(2, 'Ticketing Agent', 1, 0, 0, 0),
(3, 'Accountant', 0, 1, 1, 0);

-- 2. Users Table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role_id INT NOT NULL,
    preferred_theme VARCHAR(30) DEFAULT 'aviation-blue',
    preferred_lang VARCHAR(10) DEFAULT 'en',
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (role_id) REFERENCES roles(id)
);

-- Default Admin (Password: admin123)
INSERT INTO users (full_name, username, password_hash, role_id) VALUES
('System Administrator', 'admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 1);

-- 3. Chart of Accounts (Customers, Airlines/Suppliers, Cash/Bank, Income/Expense)
CREATE TABLE accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    account_code VARCHAR(20) NOT NULL UNIQUE,
    account_name VARCHAR(120) NOT NULL,
    account_type ENUM('Asset', 'Liability', 'Income', 'Expense', 'Customer', 'Airline_Supplier') NOT NULL,
    phone VARCHAR(30) NULL,
    opening_balance DECIMAL(15,2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO accounts (account_code, account_name, account_type) VALUES
('1001', 'Cash in Hand', 'Asset'),
('1002', 'HBL Corporate Bank Account', 'Asset'),
('2001', 'IATA / BSP Payable', 'Airline_Supplier'),
('2002', 'PIA Direct Supplier', 'Airline_Supplier'),
('2003', 'Emirates Airlines Supplier', 'Airline_Supplier'),
('3001', 'Walk-in Customers', 'Customer'),
('3002', 'Al-Makkah Corporate Travels', 'Customer'),
('4001', 'Ticket Commission & Service Income', 'Income'),
('5001', 'Office & GDS Expenses', 'Expense');

-- 4. Air Tickets Table
CREATE TABLE tickets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    issue_date DATE NOT NULL,
    pnr VARCHAR(20) NOT NULL,
    ticket_no VARCHAR(30) NOT NULL UNIQUE,
    passenger_name VARCHAR(120) NOT NULL,
    sector VARCHAR(50) NOT NULL,
    airline_code VARCHAR(10) NOT NULL,
    travel_date DATE NULL,
    customer_id INT NOT NULL,
    supplier_id INT NOT NULL,
    basic_fare DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    taxes DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    supplier_comm DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    service_charge DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    net_payable DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    customer_receivable DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    net_profit DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    status ENUM('Issued', 'Refunded', 'Void', 'Reissued') DEFAULT 'Issued',
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES accounts(id),
    FOREIGN KEY (supplier_id) REFERENCES accounts(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
);

-- 5. Double-Entry General Ledger Transactions
CREATE TABLE ledger_entries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    voucher_no VARCHAR(30) NOT NULL,
    entry_date DATE NOT NULL,
    account_id INT NOT NULL,
    reference_type ENUM('Ticket', 'Receipt', 'Payment', 'Journal') NOT NULL,
    reference_id INT NULL,
    description VARCHAR(255) NOT NULL,
    debit DECIMAL(15,2) DEFAULT 0.00,
    credit DECIMAL(15,2) DEFAULT 0.00,
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (account_id) REFERENCES accounts(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
);