-- ============================================================
-- Roscroft Jets — Landing Page API
-- Migration: Run once against urbexadmin_roscroft
-- ============================================================

-- 1. Flight enquiries submitted via the landing page
CREATE TABLE IF NOT EXISTS website_enquiries (
    id           INT          NOT NULL AUTO_INCREMENT,
    reference    VARCHAR(25)  NOT NULL,
    trip_type    ENUM('oneWay','roundTrip','multiLeg') NOT NULL,
    legs_json    TEXT         NOT NULL,
    pax_count    TINYINT      NOT NULL DEFAULT 1,
    full_name    VARCHAR(255) NOT NULL,
    email        VARCHAR(255) NOT NULL,
    phone        VARCHAR(50)  DEFAULT NULL,
    requests     TEXT         DEFAULT NULL,
    status       ENUM('new','reviewing','quoted','booked','cancelled') NOT NULL DEFAULT 'new',
    admin_notes  TEXT         DEFAULT NULL,
    ip_address   VARCHAR(45)  DEFAULT NULL,
    created_at   INT          NOT NULL,
    updated_at   INT          NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_reference (reference),
    KEY idx_status      (status),
    KEY idx_email       (email),
    KEY idx_created_at  (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 2. Contact page submissions
CREATE TABLE IF NOT EXISTS website_contacts (
    id           INT          NOT NULL AUTO_INCREMENT,
    full_name    VARCHAR(255) NOT NULL,
    email        VARCHAR(255) NOT NULL,
    phone        VARCHAR(50)  DEFAULT NULL,
    message      TEXT         NOT NULL,
    status       ENUM('new','read','replied') NOT NULL DEFAULT 'new',
    admin_notes  TEXT         DEFAULT NULL,
    ip_address   VARCHAR(45)  DEFAULT NULL,
    created_at   INT          NOT NULL,
    PRIMARY KEY (id),
    KEY idx_status     (status),
    KEY idx_email      (email),
    KEY idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 3. Newsletter subscribers
CREATE TABLE IF NOT EXISTS newsletter_subscribers (
    id                INT          NOT NULL AUTO_INCREMENT,
    email             VARCHAR(255) NOT NULL,
    full_name         VARCHAR(255) DEFAULT NULL,
    unsubscribe_token VARCHAR(64)  NOT NULL,
    status            ENUM('active','unsubscribed') NOT NULL DEFAULT 'active',
    source            VARCHAR(100) NOT NULL DEFAULT 'landing_page',
    subscribed_at     INT          NOT NULL,
    unsubscribed_at   INT          DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_email (email),
    KEY idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 4. Admin users for the landing page CMS (separate from customer accounts)
CREATE TABLE IF NOT EXISTS landing_admin_users (
    id          INT          NOT NULL AUTO_INCREMENT,
    email       VARCHAR(255) NOT NULL,
    password    VARCHAR(255) NOT NULL,
    full_name   VARCHAR(255) NOT NULL,
    role        ENUM('super_admin','ops') NOT NULL DEFAULT 'ops',
    last_login  INT          DEFAULT NULL,
    created_at  INT          NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 5. Admin sessions
CREATE TABLE IF NOT EXISTS landing_admin_sessions (
    id          INT         NOT NULL AUTO_INCREMENT,
    admin_id    INT         NOT NULL,
    token       VARCHAR(64) NOT NULL,
    expires_at  INT         NOT NULL,
    created_at  INT         NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_token (token),
    KEY idx_admin_id (admin_id),
    CONSTRAINT fk_admin_session FOREIGN KEY (admin_id)
        REFERENCES landing_admin_users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 6. Rate limit log (DB-based, no Redis needed)
CREATE TABLE IF NOT EXISTS rate_limit_log (
    id          INT          NOT NULL AUTO_INCREMENT,
    identifier  VARCHAR(100) NOT NULL,
    endpoint    VARCHAR(100) NOT NULL,
    created_at  INT          NOT NULL,
    PRIMARY KEY (id),
    KEY idx_rate (identifier, endpoint, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================
-- Seed: initial super-admin account
-- Password: ChangeMe2026! — CHANGE IMMEDIATELY after first login
-- ============================================================
INSERT IGNORE INTO landing_admin_users (email, password, full_name, role, created_at)
VALUES (
    'admin@roscroftjets.com',
    '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uSc/PUuy6', -- bcrypt of "ChangeMe2026!"
    'Roscroft Admin',
    'super_admin',
    UNIX_TIMESTAMP()
);

-- 7. Membership invitation requests from the landing page modal
CREATE TABLE IF NOT EXISTS membership_invitations (
    id             INT          NOT NULL AUTO_INCREMENT,
    full_name      VARCHAR(255) NOT NULL,
    email          VARCHAR(255) NOT NULL,
    phone          VARCHAR(50)  DEFAULT NULL,
    tier_interest  ENUM('tier_1','tier_2','tier_3','open') NOT NULL DEFAULT 'open',
    message        TEXT         DEFAULT NULL,
    status         ENUM('new','contacted','closed') NOT NULL DEFAULT 'new',
    admin_notes    TEXT         DEFAULT NULL,
    ip_address     VARCHAR(45)  DEFAULT NULL,
    created_at     INT          NOT NULL,
    updated_at     INT          DEFAULT NULL,
    PRIMARY KEY (id),
    INDEX idx_email     (email),
    INDEX idx_status    (status),
    INDEX idx_created   (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;