-- Trip Venue CRM schema
-- Import this into your MySQL database (phpMyAdmin, or `mysql -u user -p dbname < schema.sql`).

CREATE TABLE IF NOT EXISTS agents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    email VARCHAR(160) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,     -- store via PHP's password_hash(), never plain text
    is_admin TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS leads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ref VARCHAR(20) NOT NULL UNIQUE,          -- enquiry reference shown to the customer, e.g. TV48213
    name VARCHAR(160) NOT NULL,
    phone VARCHAR(40) NOT NULL,
    email VARCHAR(160) NULL,
    call_time VARCHAR(60) NULL,               -- e.g. "Morning (9am–12pm)"

    route_from VARCHAR(10) NOT NULL,          -- IATA code
    route_to VARCHAR(10) NOT NULL,
    travel_date DATE NOT NULL,
    return_date DATE NULL,
    cabin_class VARCHAR(30) NOT NULL DEFAULT 'Economy',
    adults INT NOT NULL DEFAULT 1,
    fare_estimate DECIMAL(10,2) NULL,

    status ENUM('New','Yes','No','Follow-up') NOT NULL DEFAULT 'New',
    assigned_agent INT NULL,
    payment_amount DECIMAL(10,2) NULL,        -- filled in by the agent once a deal is confirmed
    notes TEXT NULL,

    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (assigned_agent) REFERENCES agents(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;