-- @database: main
--
-- Unifies the two parallel theme referentials into ONE, named `topic`.
--
-- Before this migration the project carried both:
--   - `focus*` : the REAL referential, hand-filled — 135 themes on 2 levels
--                (12 roots + 123 children), emoji, a hierarchy table, and the
--                links used by media / social feeds / users;
--   - `topic*` : a newer Hydrogen-side design (slug identity, per-locale
--                labels, position, is_active) that was NEVER seeded — 0 rows,
--                which left `GET /api/topics` and `PUT /api/users/me/topics`
--                answering off an empty catalogue.
--
-- WHY `topic` WINS THE NAME: this is a photo platform, and in photography
-- "focus" means the focal point / sharpness. `media_focus` reads as "the focus
-- point of the image", not "the theme of the image" — an ambiguity sitting on
-- the project's core domain. `topic` is the industry-standard word for subject
-- matter, and it pluralises cleanly (`focuses`/`foci` do not). `theme` was not
-- an option: it is already taken by the user setting (light/dark/system).
--
-- WHY THE `focus` STRUCTURE WINS THE SCHEMA: it holds the data, and its
-- SMALLINT primary key is the right choice here — the referential is small and
-- bounded (~135 rows) while the link tables (`media_topic`) grow with the media
-- library, where a 2-byte key beats a 16-byte UUID. The good parts of the
-- `topic` design (position, is_active, timestamps, richer translations) are
-- carried over as new columns.
--
-- SAFE: `focus_translation` is EMPTY, so no translation is lost by adopting the
-- better `(locale, label, description)` shape. The four `topic*` tables are all
-- empty and simply dropped. Real data preserved: 135 themes, 136 hierarchy
-- links, 2 user associations, 13 media links.

-- 1. Drop the never-seeded topic-side tables (children first: they FK to `topic`).

-- `focus_translation` is empty and its shape (iso_639_2 CHAR(2) + name) is
-- poorer than the target one; recreated further down under the new name.

-- 2. Release the foreign keys so the parent can be renamed with clean names.
ALTER TABLE `media_focus`       DROP FOREIGN KEY `fk_media_focus_focus`;
ALTER TABLE `social_feed_focus` DROP FOREIGN KEY `social_feed_focus_focus`;
ALTER TABLE `user_focus`        DROP FOREIGN KEY `fk_user_focus_focus`;

-- 3. Rename the tables (data is carried over untouched).
RENAME TABLE
    `focus`             TO `topic`,
    `focus_hierarchy`   TO `topic_hierarchy`,
    `user_focus`        TO `user_topic`,
    `media_focus`       TO `media_topic`,
    `social_feed_focus` TO `social_feed_topic`;

-- 4. Rename the columns to the new vocabulary.
--    `name` becomes `slug`: the 135 values are already slug-shaped
--    ("travel", "outdoor", "sports") and unique, so it becomes the public API
--    identity — consistent with badges / titles / documents.
ALTER TABLE `topic`
    RENAME COLUMN `name` TO `slug`;

ALTER TABLE `topic_hierarchy`
    RENAME COLUMN `focus_id`        TO `topic_id`,
    RENAME COLUMN `focus_parent_id` TO `parent_topic_id`;

ALTER TABLE `user_topic`        RENAME COLUMN `focus_id` TO `topic_id`;
ALTER TABLE `media_topic`       RENAME COLUMN `focus_id` TO `topic_id`;
ALTER TABLE `social_feed_topic` RENAME COLUMN `focus_id` TO `topic_id`;

-- 5. Enrich `topic` with what the (better) topic design brought.
--    `position` orders the catalogue for display; `is_active` retires a theme
--    without losing history (a user keeps a theme they already picked).
ALTER TABLE `topic`
    MODIFY COLUMN `slug` VARCHAR(64) NOT NULL,
    ADD COLUMN `position`   SMALLINT   NOT NULL DEFAULT 0 AFTER `emoji`,
    ADD COLUMN `is_active`  TINYINT(1) NOT NULL DEFAULT 1 AFTER `position`,
    ADD COLUMN `created_at` DATETIME   NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `is_active`,
    ADD COLUMN `updated_at` DATETIME   NULL     DEFAULT NULL AFTER `created_at`,
    ADD UNIQUE KEY `uq_topic_slug` (`slug`),
    -- "walk the active catalogue in display order" — the hot path of the listing.
    ADD KEY `idx_topic_active_position` (`is_active`, `position`);

-- Seed `position` from the current id so the catalogue keeps a stable,
-- deterministic order until the back-office reorders it.
UPDATE `topic` SET `position` = `id`;

-- 6. Per-locale labels, adopting the richer shape (the old table was empty).
--    A theme with no translation falls back to its slug at read time.
CREATE TABLE `topic_translation` (
    `topic_id`    SMALLINT UNSIGNED NOT NULL,
    `locale`      VARCHAR(10)       NOT NULL,
    `label`       VARCHAR(80)       NOT NULL,
    `description` VARCHAR(255)      NULL,
    PRIMARY KEY (`topic_id`, `locale`),
    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;

-- 7. Hashtag mapping, re-keyed on the SMALLINT id (was empty).
CREATE TABLE `topic_hashtag` (
    `topic_id` SMALLINT UNSIGNED NOT NULL,
    `hashtag`  VARCHAR(64)       NOT NULL,
    PRIMARY KEY (`topic_id`, `hashtag`),
    KEY `idx_th_hashtag` (`hashtag`),
    CONSTRAINT `fk_th_topic` FOREIGN KEY (`topic_id`)
        REFERENCES `topic`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8. Restore the foreign keys under the new vocabulary.
ALTER TABLE `user_topic`
    ADD CONSTRAINT `fk_user_topic_topic` FOREIGN KEY (`topic_id`)
        REFERENCES `topic`(`id`) ON DELETE CASCADE;

ALTER TABLE `media_topic`
    ADD CONSTRAINT `fk_media_topic_topic` FOREIGN KEY (`topic_id`)
        REFERENCES `topic`(`id`) ON DELETE CASCADE;

ALTER TABLE `social_feed_topic`
    ADD CONSTRAINT `fk_social_feed_topic_topic` FOREIGN KEY (`topic_id`)
        REFERENCES `topic`(`id`) ON DELETE CASCADE;

-- The hierarchy is a DAG, not a tree: 107 themes have one parent, 13 have two,
-- one has three. A `parent_id` column would therefore be wrong — the link table
-- stays, and both ends are constrained.
ALTER TABLE `topic_hierarchy`
    ADD CONSTRAINT `fk_topic_hierarchy_topic` FOREIGN KEY (`topic_id`)
        REFERENCES `topic`(`id`) ON DELETE CASCADE,
    ADD CONSTRAINT `fk_topic_hierarchy_parent` FOREIGN KEY (`parent_topic_id`)
        REFERENCES `topic`(`id`) ON DELETE CASCADE;
