-- Media comments with threaded replies (adjacency list + denormalised root).
--
-- Design notes:
--
-- * `parent_id` is the immediate parent (NULL = top-level on the media).
-- * `root_id` is the top-level ancestor of the thread. For a top-level
--   comment, `root_id = id`. This denormalisation lets us fetch a whole
--   thread (top-level + every reply at any depth) with a single indexed
--   `WHERE root_id = ?` — no recursive CTE needed. Acceptable because
--   threads are immutable trees (we never re-parent a comment).
-- * `depth` ∈ [0, COMMENT_MAX_DEPTH - 1]; the cap is enforced at the
--   application layer (env-driven) and re-asserted by a `CHECK` here.
--   With the default env (`COMMENT_MAX_DEPTH=5`) the legal range is 0..4.
-- * `deleted_at` is a soft-delete tombstone: the row stays around so child
--   replies are not orphaned, but the API returns `body: null` + a
--   `meta.deleted` flag. Hard delete is never performed by the app.
-- * `reply_count` is the count of DIRECT, non-deleted children of this row.
--   Maintained applicatively by `MediaCommentService` (UPDATE atomique
--   dans la même transaction que l'INSERT/soft-delete du fils).
-- * `media_stats.comment_count` counts non-deleted TOP-LEVEL comments of a
--   media (added by the ALTER below). Same applicative maintenance pattern.

CREATE TABLE `media_comment` (
    `id`          BINARY(16)       NOT NULL,
    `media_id`    BINARY(16)       NOT NULL,
    `user_id`     BINARY(16)       NOT NULL,
    `parent_id`   BINARY(16)       NULL,
    `root_id`     BINARY(16)       NOT NULL,
    `depth`       TINYINT UNSIGNED NOT NULL,
    `body`        TEXT             NOT NULL,
    `reply_count` INT UNSIGNED     NOT NULL DEFAULT 0,
    `created_at`  DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `edited_at`   DATETIME         NULL,
    `deleted_at`  DATETIME         NULL,
    PRIMARY KEY (`id`),
    -- Top-level listing of a media's comments, newest-first.
    KEY `idx_mc_media_created`  (`media_id`, `created_at`),
    -- Whole-thread fetch (`WHERE root_id = ? ORDER BY depth, created_at`).
    KEY `idx_mc_root_depth`     (`root_id`, `depth`, `created_at`),
    -- Children of a given comment + parent existence check on insert.
    KEY `idx_mc_parent_created` (`parent_id`, `created_at`),
    -- "Comments by user X" reverse lookup (profile / moderation).
    KEY `idx_mc_user_created`   (`user_id`, `created_at`),
    CONSTRAINT `fk_mc_media`  FOREIGN KEY (`media_id`)  REFERENCES `media`(`id`)         ON DELETE CASCADE,
    CONSTRAINT `fk_mc_user`   FOREIGN KEY (`user_id`)   REFERENCES `user`(`id`)          ON DELETE CASCADE,
    CONSTRAINT `fk_mc_parent` FOREIGN KEY (`parent_id`) REFERENCES `media_comment`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_mc_root`   FOREIGN KEY (`root_id`)   REFERENCES `media_comment`(`id`) ON DELETE CASCADE,
    CONSTRAINT `chk_mc_depth` CHECK (`depth` BETWEEN 0 AND 4)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Top-level comment counter, sibling of `likes_count`/`dislikes_count`.
ALTER TABLE `media_stats`
    ADD COLUMN `comments_count` INT UNSIGNED NOT NULL DEFAULT 0 AFTER `views_count`;
