-- Polymorphic moderation report.
--
-- Lives in `hxa_bo` (back-office) — the front API never lists raw rows; it
-- only inserts (POST /api/{target}/reports) and the moderator UI reads them
-- via /admin/reports. Storing this on the BO side keeps user-facing schemas
-- clean of moderation noise and lets us GDPR-flush reports on a separate
-- cadence from the main app.
--
-- Polymorphism via (target_type, target_id):
--   - target_type ENUM('media','user','comment')
--   - target_id   BINARY(16)  — `media.id`, `user.id` or `media_comment.id`
--     depending on `target_type`. No FK on purpose: the target lives in
--     `hydrogen.<table>`, a different schema; moderation must survive a hard
--     delete of the target too (we want to know what was reported even after
--     a user wipes their account).
--
-- Idempotency:
--   UNIQUE (reporter_user_id, target_type, target_id) — the same person
--   can't pile up reports on the same target. A re-report on an existing
--   pending or already-resolved row returns the existing row (HTTP 200,
--   service layer handles the "silent" semantics).
--
-- Lifecycle:
--   status = 'pending'      → fresh, awaits moderator
--          | 'reviewed'     → moderator looked, decided to keep watching
--          | 'action_taken' → content removed / user banned / …
--          | 'dismissed'    → false alarm
--   resolved_by_user_id / resolved_at / resolution_note set on any
--   transition out of 'pending'. The application keeps the verdict on
--   re-report (the existing row is returned as-is).
--
-- Indexes:
--   - uniq_reporter_target → idempotent insert + "did I already report X?"
--   - idx_target           → "how many open reports on this target?"
--                            (target_type, target_id, status)
--   - idx_queue            → moderator queue scan (status, created_at)

CREATE TABLE `report` (
    `id`                    BINARY(16)                                                          NOT NULL,
    `reporter_user_id`      BINARY(16)                                                          NOT NULL,
    `target_type`           ENUM('media','user','comment')                                      NOT NULL,
    `target_id`             BINARY(16)                                                          NOT NULL,
    `reason_code`           VARCHAR(64)                                                         NOT NULL,
    `details`               TEXT                                                                NULL,
    `status`                ENUM('pending','reviewed','action_taken','dismissed')               NOT NULL DEFAULT 'pending',
    `resolved_by_user_id`   BINARY(16)                                                          NULL,
    `resolved_at`           DATETIME                                                            NULL,
    `resolution_note`       TEXT                                                                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_reporter_target` (`reporter_user_id`, `target_type`, `target_id`),
    KEY `idx_target`                  (`target_type`, `target_id`, `status`),
    KEY `idx_queue`                   (`status`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
