-- @database: content
--
-- Editorial "documents" distributed raw via the API — legal pages (CGU,
-- privacy policy…), help articles, and any free-form Markdown page an operator
-- wants to add AT WILL through the admin API. Consumed by both the mobile app
-- and the website, so a document carries a display `title` + web SEO `meta_*`
-- on top of its Markdown `body`.
--
-- Lives in its own `content` database (not `hxa`): this is operator-authored
-- runtime content, decoupled from the app schema, and survives deploys cleanly
-- (unlike file-based editorial, which the deploy delta could clobber).
--
-- Three tables:
--   document              → the entity (locale-independent), keyed by `slug`
--                           (the API identity, like topic/badge). Carries the
--                           legal version + effective date, document-wide.
--   document_translation  → one row per (document, locale): title + SEO meta +
--                           Markdown body. Exact-locale, no fallback at rest.
--   document_revision     → append-only history. A snapshot is written on EVERY
--                           translation write, so "which text was in force on
--                           day D" is always answerable (legal requirement).
--
-- No cross-database FK (documents reference nothing in `hxa`). The internal
-- BINARY(16) id is a UUIDv7 surrogate so translations/revisions have a stable
-- parent key even if a slug were ever renamed.

CREATE TABLE `document` (
    `id`              BINARY(16)                    NOT NULL,
    `slug`            VARCHAR(80)                   NOT NULL,
    `category`        VARCHAR(32)                   NULL,
    `status`          ENUM('draft','published')     NOT NULL DEFAULT 'draft',
    -- Legal version in force, document-wide (a CGU v2.1 spans all locales).
    `current_version` VARCHAR(20)                   NULL,
    -- When the current version takes effect (NULL = not dated / immediate).
    `effective_at`    DATETIME                      NULL,
    `created_at`      DATETIME                      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`      DATETIME                      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uniq_slug` (`slug`),
    -- Public listing filters on (status, category) and orders by slug.
    KEY `idx_status_category` (`status`, `category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `document_translation` (
    `document_id`      BINARY(16)   NOT NULL,
    `locale`           VARCHAR(5)   NOT NULL,
    `title`            VARCHAR(255) NOT NULL,
    -- Web SEO overrides; when NULL the surface falls back to `title` / body.
    `meta_title`       VARCHAR(255) NULL,
    `meta_description` VARCHAR(500) NULL,
    `body`             MEDIUMTEXT   NOT NULL,
    `updated_at`       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`document_id`, `locale`),
    CONSTRAINT `fk_translation_document`
        FOREIGN KEY (`document_id`) REFERENCES `document` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `document_revision` (
    `id`               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `document_id`      BINARY(16)      NOT NULL,
    `locale`           VARCHAR(5)      NOT NULL,
    -- Version label in force at snapshot time (copied from document).
    `version`          VARCHAR(20)     NULL,
    `title`            VARCHAR(255)    NOT NULL,
    `meta_title`       VARCHAR(255)    NULL,
    `meta_description` VARCHAR(500)    NULL,
    `body`             MEDIUMTEXT      NOT NULL,
    `effective_at`     DATETIME        NULL,
    -- Nominative editor (staff username) or NULL when the static admin token
    -- was used (system / Talend). No FK: the trail must outlive the operator.
    `editor`           VARCHAR(20)     NULL,
    `created_at`       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    -- Keyset history for one translation: WHERE document_id=? AND locale=? DESC.
    KEY `idx_doc_locale` (`document_id`, `locale`, `id`),
    CONSTRAINT `fk_revision_document`
        FOREIGN KEY (`document_id`) REFERENCES `document` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
