-- ============================================================
-- 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;


-- 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;

-- 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;
