-- Topics — referential catalogue of "themes" (5..10 picked by each user
-- at onboarding) and the user-side join table.
--
-- The same catalogue feeds three matching directions, hence the neutral
-- `topic` name (vs the user-centric `interest`):
--   1. user → content     : "show me media in topics I follow"
--   2. content → user     : "notify users following topic X when a new
--                            media tagged X is published"
--   3. user → user        : suggestion engine cross-referencing the
--                            users' topic sets
--
-- Why this shape:
--   - `slug` is the API-visible identity (stable, human-readable). The
--     hex `id` stays internal for FKs. Front never sees a BINARY(16).
--   - i18n labels live in a separate `topic_translation` table — easier
--     to index per-locale, no JSON parsing, simple WHERE locale IN (...)
--     with applicative fallback.
--   - `is_active` lets the BO retire a topic without breaking historical
--     `user_topic` rows (the listing repository filters it out; the
--     hydrate-by-id path keeps returning archived rows so existing user
--     choices stay intelligible).
--   - `position` is the BO-controlled display order on the onboarding
--     picker. Sorted with `(is_active, position)` for a single index.
--   - No `media_topic` / `establishment_topic` in this migration — those
--     come when the matching service is built. The catalogue table is
--     the only thing they need to depend on.

CREATE TABLE `topic` (
    `id`         BINARY(16)   NOT NULL,
    `slug`       VARCHAR(64)  NOT NULL,
    `icon`       VARCHAR(64)  NULL,
    `position`   SMALLINT     NOT NULL DEFAULT 0,
    `is_active`  TINYINT(1)   NOT NULL DEFAULT 1,
    `created_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME     NULL,
    PRIMARY KEY (`id`),
    -- API identity. Stable across reseeds and human-readable in URLs.
    UNIQUE KEY `uq_tp_slug` (`slug`),
    -- "Active topics in display order" — the hot path of the catalogue
    -- listing. Tiebreak by id to keep pagination deterministic if two
    -- positions ever collide (BO tools usually prevent it but indexes
    -- can't trust users).
    KEY `idx_tp_active_position` (`is_active`, `position`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Per-locale label + optional description. PK composite (topic, locale)
-- so the same topic can't have two FR labels. ON DELETE CASCADE because
-- a translation is meaningless without its parent.
CREATE TABLE `topic_translation` (
    `topic_id`    BINARY(16)   NOT NULL,
    `locale`      VARCHAR(10)  NOT NULL,
    `label`       VARCHAR(80)  NOT NULL,
    `description` VARCHAR(255) NULL,
    PRIMARY KEY (`topic_id`, `locale`),
    -- "Give me all labels for these topic ids in locale X" — covers the
    -- hydrate path of the listing action.
    KEY `idx_tt_locale_topic` (`locale`, `topic_id`),
    CONSTRAINT `fk_tt_topic` FOREIGN KEY (`topic_id`)
        REFERENCES `topic`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- User ↔ topic many-to-many. PK composite kills duplicate INSERTs by
-- construction (no surrogate id needed). The 5..10 cardinality is
-- enforced in `UserTopicService` (env-driven, NOT a CHECK constraint —
-- product limits move faster than schemas).
--
-- The reverse index `(topic_id, user_id)` is mandatory for phase 2's
-- "find users interested in topic X" queries (cross-suggestion,
-- topic-targeted notifications).
CREATE TABLE `user_topic` (
    `user_id`    BINARY(16)  NOT NULL,
    `topic_id`   BINARY(16)  NOT NULL,
    `created_at` DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`user_id`, `topic_id`),
    KEY `idx_ut_topic_user` (`topic_id`, `user_id`),
    CONSTRAINT `fk_ut_user`  FOREIGN KEY (`user_id`)  REFERENCES `user`(`id`)  ON DELETE CASCADE,
    CONSTRAINT `fk_ut_topic` FOREIGN KEY (`topic_id`) REFERENCES `topic`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
