-- Gate Gap API — device attribution.
--
-- Names on this layer are deliberately this app's own. The wire fields the app
-- sends (`trace`, `lane`, `crate`, `beacon`, `berth`, `chime`, `region`,
-- `mark`, `ledger`) are mapped here to the columns below, and only when the
-- server forwards outward does it switch to the fixed names the analytics
-- service expects. Two apps built from the same recipe should not look alike
-- from the outside; the analytics contract is the one thing that must not vary.
--
-- The device key is the AppsFlyer id, not an identifier of ours: a reinstall
-- produces a new one, and a new one is deliberately a new row.

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

CREATE TABLE IF NOT EXISTS handsets (
    id              CHAR(36)     NOT NULL,
    -- af_id on the wire as `trace`. Unique: one row per install.
    trace_key       VARCHAR(64)  NOT NULL,
    os_line         VARCHAR(32)  NOT NULL DEFAULT '',
    bundle          VARCHAR(128) NOT NULL DEFAULT '',
    beacon_project  VARCHAR(64)  NOT NULL DEFAULT '',
    store_ref       VARCHAR(32)  NOT NULL DEFAULT '',
    chime_token     VARCHAR(255) NOT NULL DEFAULT '',
    region_tag      VARCHAR(32)  NOT NULL DEFAULT '',
    ad_mark         VARCHAR(64)  NOT NULL DEFAULT '',
    -- Kept whole, not hashed: it is what the analytics service is given, and a
    -- hash of it would only mean storing something we cannot forward.
    source_ip       VARCHAR(45)  NOT NULL DEFAULT '',

    -- The digest of the attribution — the full JSON lives in handset_signals.
    attr_status     VARCHAR(32)  NOT NULL DEFAULT '',
    attr_source     VARCHAR(128) NOT NULL DEFAULT '',
    attr_campaign   VARCHAR(190) NOT NULL DEFAULT '',

    opens           INT UNSIGNED NOT NULL DEFAULT 0,
    first_seen_at   DATETIME     NOT NULL,
    last_seen_at    DATETIME     NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_handset_trace (trace_key),
    KEY idx_handset_seen (last_seen_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add-only. Every attribution the app ever reported, in full, deduplicated by
-- a digest of the body so a retry does not produce a second row.
CREATE TABLE IF NOT EXISTS handset_signals (
    id           CHAR(36)     NOT NULL,
    handset_id   CHAR(36)     NOT NULL,
    trace_key    VARCHAR(64)  NOT NULL,
    body         JSON         NULL,
    body_digest  CHAR(64)     NOT NULL,
    received_at  DATETIME     NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_signal_digest (handset_id, body_digest),
    KEY idx_signal_trace (trace_key, received_at),
    CONSTRAINT fk_signal_handset FOREIGN KEY (handset_id) REFERENCES handsets (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- What was sent outward and what came back. Kept so a disagreement with the
-- analytics service can be settled with a record rather than a memory.
CREATE TABLE IF NOT EXISTS handset_forwards (
    id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    handset_id    CHAR(36)     NULL DEFAULT NULL,
    trace_key     VARCHAR(64)  NOT NULL DEFAULT '',
    endpoint      VARCHAR(255) NOT NULL DEFAULT '',
    status_code   SMALLINT     NOT NULL DEFAULT 0,
    accepted      TINYINT(1)   NOT NULL DEFAULT 0,
    route_url     VARCHAR(512) NOT NULL DEFAULT '',
    note          VARCHAR(255) NOT NULL DEFAULT '',
    created_at    DATETIME     NOT NULL,
    PRIMARY KEY (id),
    KEY idx_forward_trace (trace_key, created_at),
    CONSTRAINT fk_forward_handset FOREIGN KEY (handset_id) REFERENCES handsets (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Which account has signed in on which install. Written when the app signs in,
-- and read by the final request to answer "is this device authorised".
CREATE TABLE IF NOT EXISTS handset_accounts (
    handset_id  CHAR(36) NOT NULL,
    user_id     CHAR(36) NOT NULL,
    linked_at   DATETIME NOT NULL,
    PRIMARY KEY (handset_id, user_id),
    KEY idx_handset_account_user (user_id),
    CONSTRAINT fk_link_handset FOREIGN KEY (handset_id) REFERENCES handsets (id) ON DELETE CASCADE,
    CONSTRAINT fk_link_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;