-- Gate Gap API — initial schema.
--
-- Notes that matter for security and for the client:
--
--  · Every row that belongs to a person carries user_id and every query in the
--    application filters on it. Identifiers are chosen by the client (the app
--    already works offline and mints UUIDs), so ownership is never inferred
--    from the identifier — it is always checked.
--  · Deletions are recorded in `tombstones` so a device that was offline when
--    something was deleted learns about it on the next pull instead of
--    resurrecting it.
--  · Booking references and document numbers are stored encrypted; the column
--    holds AES-256-GCM ciphertext, so a database dump on its own does not give
--    up a passport number.
--  · utf8mb4 throughout: airport names and notes contain accents and emoji.

SET NAMES utf8mb4;
SET time_zone = '+00:00';

-- ---------------------------------------------------------------- accounts

CREATE TABLE IF NOT EXISTS users (
    id                CHAR(36)     NOT NULL,
    email             VARCHAR(254) NOT NULL,
    -- Lower-cased copy used for uniqueness and lookup, so two people cannot
    -- register the same address in different cases.
    email_normalised  VARCHAR(254) NOT NULL,
    password_hash     VARCHAR(255) NOT NULL,
    display_name      VARCHAR(80)  NOT NULL DEFAULT '',
    -- Bumped whenever every session must fall over: password change, or the
    -- traveller pressing "sign out everywhere".
    token_version     INT UNSIGNED NOT NULL DEFAULT 1,
    failed_logins     INT UNSIGNED NOT NULL DEFAULT 0,
    locked_until      DATETIME     NULL DEFAULT NULL,
    created_at        DATETIME     NOT NULL,
    updated_at        DATETIME     NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_users_email (email_normalised)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- One row per signed-in device.
CREATE TABLE IF NOT EXISTS sessions (
    id                  CHAR(36)     NOT NULL,
    user_id             CHAR(36)     NOT NULL,
    device_label        VARCHAR(80)  NOT NULL DEFAULT '',
    user_agent          VARCHAR(255) NOT NULL DEFAULT '',
    ip_hash             CHAR(64)     NOT NULL DEFAULT '',
    created_at          DATETIME     NOT NULL,
    last_used_at        DATETIME     NOT NULL,
    expires_at          DATETIME     NOT NULL,
    revoked_at          DATETIME     NULL DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_sessions_user (user_id, revoked_at),
    CONSTRAINT fk_sessions_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- The chain of refresh tokens for a session.
--
-- Each token may be spent exactly once, and the spent row is KEPT. That is the
-- whole point: if a token that has already been used turns up again, either
-- the real device is replaying it or somebody copied it, and there is no way
-- to tell which — so the session dies and both have to sign in. Overwriting
-- the row instead would make the old token merely unknown, and a theft would
-- look exactly like an expired session.
CREATE TABLE IF NOT EXISTS refresh_tokens (
    id           CHAR(36) NOT NULL,
    session_id   CHAR(36) NOT NULL,
    user_id      CHAR(36) NOT NULL,
    -- SHA-256 of the token. The token itself is never stored, so a database
    -- dump cannot be replayed against the API.
    token_hash   CHAR(64) NOT NULL,
    created_at   DATETIME NOT NULL,
    expires_at   DATETIME NOT NULL,
    used_at      DATETIME NULL DEFAULT NULL,
    revoked_at   DATETIME NULL DEFAULT NULL,
    replaced_by  CHAR(36) NULL DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_refresh_hash (token_hash),
    KEY idx_refresh_session (session_id),
    CONSTRAINT fk_refresh_session FOREIGN KEY (session_id) REFERENCES sessions (id) ON DELETE CASCADE,
    CONSTRAINT fk_refresh_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Short-lived bearer tokens. Kept server-side so "sign out" is immediate
-- rather than "immediate once the token expires".
CREATE TABLE IF NOT EXISTS access_tokens (
    id          CHAR(36) NOT NULL,
    session_id  CHAR(36) NOT NULL,
    user_id     CHAR(36) NOT NULL,
    token_hash  CHAR(64) NOT NULL,
    created_at  DATETIME NOT NULL,
    expires_at  DATETIME NOT NULL,
    revoked_at  DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_access_hash (token_hash),
    KEY idx_access_session (session_id),
    CONSTRAINT fk_access_session FOREIGN KEY (session_id) REFERENCES sessions (id) ON DELETE CASCADE,
    CONSTRAINT fk_access_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------- traveller

CREATE TABLE IF NOT EXISTS profiles (
    user_id                      CHAR(36)     NOT NULL,
    display_name                 VARCHAR(80)  NOT NULL DEFAULT '',
    passport_country             VARCHAR(80)  NOT NULL DEFAULT '',
    second_passport_country      VARCHAR(80)  NOT NULL DEFAULT '',
    walking_pace                 VARCHAR(32)  NOT NULL DEFAULT 'normal',
    mobility_needs               JSON         NULL,
    usual_baggage                VARCHAR(32)  NOT NULL DEFAULT 'oneChecked',
    home_airport                 VARCHAR(8)   NOT NULL DEFAULT '',
    travel_to_airport_minutes    SMALLINT UNSIGNED NOT NULL DEFAULT 60,
    arrive_before_minutes        SMALLINT UNSIGNED NOT NULL DEFAULT 120,
    applies_six_month_rule       TINYINT(1)   NOT NULL DEFAULT 0,
    updated_at                   DATETIME     NOT NULL,
    PRIMARY KEY (user_id),
    CONSTRAINT fk_profiles_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------- the trip

CREATE TABLE IF NOT EXISTS trips (
    id                  CHAR(36)     NOT NULL,
    user_id             CHAR(36)     NOT NULL,
    name                VARCHAR(120) NOT NULL DEFAULT '',
    purpose             VARCHAR(32)  NOT NULL DEFAULT 'holiday',
    start_date          DATE         NOT NULL,
    end_date            DATE         NOT NULL,
    travellers          TINYINT UNSIGNED NOT NULL DEFAULT 1,
    ticket_structure    VARCHAR(16)  NOT NULL DEFAULT 'single',
    status              VARCHAR(16)  NOT NULL DEFAULT 'draft',
    draft_step          TINYINT UNSIGNED NOT NULL DEFAULT 0,
    -- Baggage plan, per-connection overrides, packing, events, live state and
    -- recap travel as documents: they are edited as a whole by one device and
    -- never queried field by field.
    baggage             JSON         NULL,
    connection_settings JSON         NULL,
    baggage_rules       JSON         NULL,
    packing             JSON         NULL,
    events              JSON         NULL,
    live                JSON         NULL,
    recap               JSON         NULL,
    created_at          DATETIME     NOT NULL,
    updated_at          DATETIME     NOT NULL,
    PRIMARY KEY (id),
    KEY idx_trips_user_updated (user_id, updated_at),
    CONSTRAINT fk_trips_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS flights (
    id                    CHAR(36)     NOT NULL,
    trip_id               CHAR(36)     NOT NULL,
    user_id               CHAR(36)     NOT NULL,
    number                VARCHAR(16)  NOT NULL DEFAULT '',
    airline               VARCHAR(80)  NOT NULL DEFAULT '',
    from_code             VARCHAR(8)   NOT NULL DEFAULT '',
    to_code               VARCHAR(8)   NOT NULL DEFAULT '',
    from_terminal         VARCHAR(16)  NOT NULL DEFAULT '',
    to_terminal           VARCHAR(16)  NOT NULL DEFAULT '',
    -- Wall clock at the airport concerned, exactly as the client stores it.
    departure_at          DATETIME     NOT NULL,
    arrival_at            DATETIME     NOT NULL,
    -- AES-256-GCM ciphertext. Never queried, only returned to its owner.
    booking_reference_enc VARBINARY(512) NULL,
    seat                  VARCHAR(16)  NOT NULL DEFAULT '',
    cabin                 VARCHAR(24)  NOT NULL DEFAULT 'economy',
    checked_bags          TINYINT UNSIGNED NOT NULL DEFAULT 0,
    notes                 TEXT         NULL,
    ticket_group          TINYINT UNSIGNED NOT NULL DEFAULT 0,
    gate_closes_minutes   TINYINT UNSIGNED NOT NULL DEFAULT 20,
    state                 VARCHAR(16)  NOT NULL DEFAULT 'scheduled',
    delay_minutes         SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    actual_departure_at   DATETIME     NULL DEFAULT NULL,
    actual_arrival_at     DATETIME     NULL DEFAULT NULL,
    source                VARCHAR(24)  NOT NULL DEFAULT 'manual',
    edited_fields         JSON         NULL,
    created_at            DATETIME     NOT NULL,
    updated_at            DATETIME     NOT NULL,
    PRIMARY KEY (id),
    KEY idx_flights_trip (trip_id),
    KEY idx_flights_user_updated (user_id, updated_at),
    CONSTRAINT fk_flights_trip FOREIGN KEY (trip_id) REFERENCES trips (id) ON DELETE CASCADE,
    CONSTRAINT fk_flights_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------- paperwork

CREATE TABLE IF NOT EXISTS documents (
    id                     CHAR(36)     NOT NULL,
    user_id                CHAR(36)     NOT NULL,
    trip_id                CHAR(36)     NULL DEFAULT NULL,
    type                   VARCHAR(32)  NOT NULL DEFAULT 'passport',
    belongs_to             VARCHAR(80)  NOT NULL DEFAULT '',
    valid_until            DATE         NULL DEFAULT NULL,
    -- AES-256-GCM ciphertext of the document number.
    reference_number_enc   VARBINARY(512) NULL,
    notes                  TEXT         NULL,
    -- Photographs stay on the device. Only the file name travels, so a lost
    -- phone does not put a passport scan on a server.
    photo_name             VARCHAR(128) NULL DEFAULT NULL,
    created_at             DATETIME     NOT NULL,
    updated_at             DATETIME     NOT NULL,
    PRIMARY KEY (id),
    KEY idx_documents_user_updated (user_id, updated_at),
    KEY idx_documents_trip (trip_id),
    CONSTRAINT fk_documents_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
    CONSTRAINT fk_documents_trip FOREIGN KEY (trip_id) REFERENCES trips (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS transit_requirements (
    id                      CHAR(36)     NOT NULL,
    user_id                 CHAR(36)     NOT NULL,
    trip_id                 CHAR(36)     NULL DEFAULT NULL,
    country                 VARCHAR(80)  NOT NULL DEFAULT '',
    airport_code            VARCHAR(8)   NOT NULL DEFAULT '',
    passport_used           VARCHAR(80)  NOT NULL DEFAULT '',
    transit_visa_required   VARCHAR(16)  NOT NULL DEFAULT 'unknown',
    airside_transit_allowed VARCHAR(16)  NOT NULL DEFAULT 'unknown',
    confirmed_by            VARCHAR(120) NOT NULL DEFAULT '',
    confirmation_date       DATE         NULL DEFAULT NULL,
    notes                   TEXT         NULL,
    document_id             CHAR(36)     NULL DEFAULT NULL,
    state                   VARCHAR(24)  NOT NULL DEFAULT 'notChecked',
    created_at              DATETIME     NOT NULL,
    updated_at              DATETIME     NOT NULL,
    PRIMARY KEY (id),
    KEY idx_transit_user_updated (user_id, updated_at),
    CONSTRAINT fk_transit_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
    CONSTRAINT fk_transit_trip FOREIGN KEY (trip_id) REFERENCES trips (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------- knowledge

CREATE TABLE IF NOT EXISTS airport_cards (
    user_id                          CHAR(36)     NOT NULL,
    code                             VARCHAR(8)   NOT NULL,
    name                             VARCHAR(120) NOT NULL DEFAULT '',
    city                             VARCHAR(120) NOT NULL DEFAULT '',
    country                          VARCHAR(80)  NOT NULL DEFAULT '',
    country_code                     VARCHAR(4)   NOT NULL DEFAULT '',
    time_zone_id                     VARCHAR(64)  NULL DEFAULT NULL,
    terminals                        JSON         NULL,
    terminals_are_suggestions        TINYINT(1)   NOT NULL DEFAULT 0,
    transfer_mode                    VARCHAR(24)  NOT NULL DEFAULT 'unknown',
    transfer_minutes                 SMALLINT UNSIGNED NULL DEFAULT NULL,
    minimum_connection_domestic      SMALLINT UNSIGNED NULL DEFAULT NULL,
    minimum_connection_international SMALLINT UNSIGNED NULL DEFAULT NULL,
    passport_control_place           VARCHAR(24)  NOT NULL DEFAULT 'unknown',
    security_after_transfer          VARCHAR(16)  NOT NULL DEFAULT 'unknown',
    notes                            TEXT         NULL,
    is_user_created                  TINYINT(1)   NOT NULL DEFAULT 0,
    updated_at                       DATETIME     NOT NULL,
    PRIMARY KEY (user_id, code),
    KEY idx_airports_user_updated (user_id, updated_at),
    CONSTRAINT fk_airports_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- The measurements that make the connection engine better than an average.
CREATE TABLE IF NOT EXISTS own_timings (
    id            CHAR(36)     NOT NULL,
    user_id       CHAR(36)     NOT NULL,
    airport_code  VARCHAR(8)   NOT NULL,
    step          VARCHAR(32)  NOT NULL,
    minutes       SMALLINT UNSIGNED NOT NULL,
    hour_of_day   TINYINT UNSIGNED NOT NULL,
    note          VARCHAR(255) NOT NULL DEFAULT '',
    recorded_at   DATETIME     NOT NULL,
    updated_at    DATETIME     NOT NULL,
    PRIMARY KEY (id),
    KEY idx_timings_user_updated (user_id, updated_at),
    KEY idx_timings_airport (user_id, airport_code),
    CONSTRAINT fk_timings_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------- plumbing

-- What was deleted and when, so a device that was offline at the time stops
-- pushing it back.
CREATE TABLE IF NOT EXISTS tombstones (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id     CHAR(36)    NOT NULL,
    entity      VARCHAR(32) NOT NULL,
    entity_id   VARCHAR(80) NOT NULL,
    deleted_at  DATETIME    NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_tombstone (user_id, entity, entity_id),
    KEY idx_tombstones_user_time (user_id, deleted_at),
    CONSTRAINT fk_tombstones_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Counts attempts per action per identity. Used for login, registration,
-- password change and refresh, so a stolen address cannot be brute forced.
CREATE TABLE IF NOT EXISTS rate_limits (
    bucket       VARCHAR(160) NOT NULL,
    window_start DATETIME     NOT NULL,
    hits         INT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (bucket),
    KEY idx_rate_window (window_start)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Security-relevant events. No request bodies, no tokens — just what happened.
CREATE TABLE IF NOT EXISTS audit_log (
    id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id    CHAR(36)     NULL DEFAULT NULL,
    event      VARCHAR(48)  NOT NULL,
    detail     VARCHAR(255) NOT NULL DEFAULT '',
    ip_hash    CHAR(64)     NOT NULL DEFAULT '',
    created_at DATETIME     NOT NULL,
    PRIMARY KEY (id),
    KEY idx_audit_user (user_id, created_at),
    KEY idx_audit_event (event, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
