-- @database: main
--
-- Frees the `notification` table name for Hydrogen's notification domain.
--
-- Two unrelated designs carried the same table name:
--   - the LEGACY one, still in place on some databases — `type_id` (FK to
--     `notification_type`), `value`, `is_viewed`, `user_emitter_id`,
--     `source_id`;
--   - Hydrogen's, created by 2026_06_07_200000_create_notification_table.sql —
--     `type` (slug), `data` (JSON), `dedup_key`, `read_at`, `pushed_at`.
--
-- Because that earlier migration has no `IF NOT EXISTS`, it simply FAILED
-- wherever the legacy table already existed, and was never applied there. The
-- consequence was silent: `NotificationRepository` inserts into columns that do
-- not exist, and every dispatch site swallows the error (soft-fail), so no
-- notification was ever stored — only the digest flush surfaced it, as
-- "Unknown column 'pushed_at'".
--
-- This migration renames the legacy table out of the way (data preserved, NOT
-- migrated — the two shapes have no faithful mapping) and then creates
-- Hydrogen's table.
--
-- SAFE TO RUN ANYWHERE. Both steps are conditional:
--   - the rename only fires when a `notification` table carrying the legacy
--     marker column `type_id` is found, so a database that already holds
--     Hydrogen's table is left untouched;
--   - the CREATE is `IF NOT EXISTS`.
-- `notification_type` (legacy lookup) is left in place: no application code
-- reads it, and it only makes sense alongside `notification_legacy`.

-- --------------------------------------------------------------------------
-- 1. Move the legacy table aside — only if it is really the legacy one.
-- --------------------------------------------------------------------------
SET @is_legacy := (
    SELECT COUNT(*)
      FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME   = 'notification'
       AND COLUMN_NAME  = 'type_id'
);

SET @sql := IF(
    @is_legacy > 0,
    'RENAME TABLE `notification` TO `notification_legacy`',
    'DO 0'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- --------------------------------------------------------------------------
-- 2. Create Hydrogen's table (identical to the 2026_06_07 definition).
-- --------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `notification` (
    `id`         BINARY(16)   NOT NULL,
    `user_id`    BINARY(16)   NOT NULL,
    `type`       VARCHAR(64)  NOT NULL,
    `data`       JSON         NOT NULL,
    `dedup_key`  VARCHAR(128) NULL,
    `read_at`    DATETIME     NULL,
    `pushed_at`  DATETIME     NULL,
    `created_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_notif_user_created` (`user_id`, `created_at`),
    KEY `idx_notif_user_read`    (`user_id`, `read_at`),
    KEY `idx_notif_user_dedup`   (`user_id`, `dedup_key`, `read_at`),
    KEY `idx_notif_pushed`       (`pushed_at`, `user_id`),
    CONSTRAINT `fk_notif_user`
        FOREIGN KEY (`user_id`) REFERENCES `user`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
