-- Polymorphic click + conversion tracking pipeline (affiliation/commission base).
--
-- Design goals (mirrors the media-counters pipeline, no Redis):
--   - CLICKS are high-volume and non-blocking: the `/go/...` redirect actions
--     buffer click events in-process and flush them as a single multi-row
--     INSERT into `tracking_event` (append-only tampon) via
--     `register_shutdown_function()`, so the visitor is already redirected
--     (302) when the row hits disk. A dedicated worker
--     (`bin/tracking-flush.php`) drains the tampon FIFO and UPSERTs
--     `tracking_stats` + `tracking_daily` in one transaction.
--   - CONVERSIONS are low-volume and money-bearing: they are written
--     SYNCHRONOUSLY (not buffered) through the Admin API postback endpoint
--     into the immutable `tracking_conversion` ledger, and the same
--     transaction bumps `tracking_stats` + `tracking_daily`. Idempotency is
--     enforced by the affiliate network's `external_ref`.
--
-- Polymorphism: every row is keyed by `(target_type, target_id)`. v1 ships
-- `target_type = 'offer'` (target_id = 32-char hex offer id) but the schema is
-- agnostic — adding `'brand'`, `'establishment'`, ... later needs zero DDL.
--
-- Money: `amount`/`commission`/`revenue_amount`/`commission_amount` are stored
-- as signed BIGINT in the currency's MINOR units (e.g. cents) to avoid float
-- drift. v1 assumes a SINGLE currency per target — `tracking_stats.currency`
-- holds the last-seen ISO-4217 code; multi-currency split is out of scope.

-- Tampon (append-only) for CLICKS only. Each row carries a pre-aggregated
-- `delta` so a single request flushes at most one row per (target). The
-- AUTO_INCREMENT id powers a stable FIFO drain order and a cheap
-- `WHERE id <= :high_water` cut point for the worker.
CREATE TABLE `tracking_event` (
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `target_type` VARCHAR(32)     NOT NULL,
    `target_id`   VARCHAR(64)     NOT NULL,
    `kind`        ENUM('click')   NOT NULL,
    `delta`       INT UNSIGNED    NOT NULL DEFAULT 1,
    `created_at`  DATETIME(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Lifetime aggregate per target. `clicks` is bumped by the flush worker;
-- `conversions`/`revenue_amount`/`commission_amount`/`currency` are bumped
-- synchronously by the conversion postback. Read by the admin analytics
-- endpoint and (later) the `/commissions` web page.
CREATE TABLE `tracking_stats` (
    `target_type`       VARCHAR(32)     NOT NULL,
    `target_id`         VARCHAR(64)     NOT NULL,
    `clicks`            BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `conversions`       BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `revenue_amount`    BIGINT          NOT NULL DEFAULT 0,
    `commission_amount` BIGINT          NOT NULL DEFAULT 0,
    `currency`          CHAR(3)         NULL,
    `updated_at`        DATETIME(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
    PRIMARY KEY (`target_type`, `target_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Daily aggregate per (target, day). Powers time-series analytics and the
-- commission dashboards. UPSERTed in the same transaction as the lifetime
-- total so the series and the running total cannot drift.
CREATE TABLE `tracking_daily` (
    `target_type`       VARCHAR(32)     NOT NULL,
    `target_id`         VARCHAR(64)     NOT NULL,
    `day`               DATE            NOT NULL,
    `clicks`            BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `conversions`       BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `revenue_amount`    BIGINT          NOT NULL DEFAULT 0,
    `commission_amount` BIGINT          NOT NULL DEFAULT 0,
    PRIMARY KEY (`target_type`, `target_id`, `day`),
    KEY `idx_day_clicks`      (`day`, `clicks`),
    KEY `idx_day_conversions` (`day`, `conversions`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Immutable conversion ledger. One row per affiliate-network conversion.
-- `external_ref` is the network's order/transaction id; the UNIQUE key makes
-- postback retries idempotent (a duplicate postback is a no-op, the stats are
-- bumped exactly once). `occurred_at` is the network-reported event time;
-- `created_at` is our ingestion time.
CREATE TABLE `tracking_conversion` (
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `target_type`  VARCHAR(32)     NOT NULL,
    `target_id`    VARCHAR(64)     NOT NULL,
    `external_ref` VARCHAR(128)    NOT NULL,
    `amount`       BIGINT          NOT NULL DEFAULT 0,
    `commission`   BIGINT          NOT NULL DEFAULT 0,
    `currency`     CHAR(3)         NOT NULL,
    `occurred_at`  DATETIME        NOT NULL,
    `created_at`   DATETIME(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_external` (`target_type`, `external_ref`),
    KEY `idx_target` (`target_type`, `target_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
