-- CreateTable
CREATE TABLE `article_themes` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(80) NOT NULL,
    `description` VARCHAR(300) NOT NULL,
    `icon_type` ENUM('EMOJI', 'LUCIDE') NOT NULL,
    `icon` VARCHAR(64) NOT NULL,
    `color` VARCHAR(7) NOT NULL,
    `position` INTEGER NOT NULL DEFAULT 0,
    `is_default` BOOLEAN NOT NULL DEFAULT false,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,

    UNIQUE INDEX `article_themes_title_key`(`title`),
    INDEX `article_themes_position_idx`(`position`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Seed: titles and order from the product owner; "Nature-based Solutions" is the fixed default.
INSERT INTO `article_themes` (`title`, `description`, `icon_type`, `icon`, `color`, `position`, `is_default`, `updated_at`) VALUES
    ('Nature-based Solutions', 'Working with natural processes to restore lakes and the landscapes around them.', 'LUCIDE', 'leaf', '#009E73', 0, true, CURRENT_TIMESTAMP(3)),
    ('Flood Resilience', 'Reducing flood risk by giving water room: wetlands, floodplains and natural retention.', 'LUCIDE', 'waves', '#0072B2', 1, false, CURRENT_TIMESTAMP(3)),
    ('Water Quality', 'Cutting nutrient and pollution loads so lake water stays clean and healthy.', 'LUCIDE', 'droplets', '#56B4E9', 2, false, CURRENT_TIMESTAMP(3)),
    ('Biodiversity & Ecology', 'Protecting and restoring the habitats, species and food webs that lakes support.', 'LUCIDE', 'fish', '#E69F00', 3, false, CURRENT_TIMESTAMP(3)),
    ('Monitoring & Technology', 'Sensors, sampling and data tools for tracking lake health over time.', 'EMOJI', '📡', '#CC79A7', 4, false, CURRENT_TIMESTAMP(3)),
    ('Policy & Governance', 'Rules, funding and cooperation that make lake restoration happen and last.', 'EMOJI', '📋', '#332288', 5, false, CURRENT_TIMESTAMP(3));

-- AlterTable: every existing article joins the default theme, then the column becomes required.
ALTER TABLE `articles` ADD COLUMN `theme_id` INTEGER NULL;
UPDATE `articles` SET `theme_id` = (SELECT `id` FROM `article_themes` WHERE `is_default` = true LIMIT 1);
ALTER TABLE `articles` MODIFY `theme_id` INTEGER NOT NULL;

-- CreateIndex
CREATE INDEX `articles_theme_id_idx` ON `articles`(`theme_id`);

-- AddForeignKey
ALTER TABLE `articles` ADD CONSTRAINT `articles_theme_id_fkey` FOREIGN KEY (`theme_id`) REFERENCES `article_themes`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- AlterTable
ALTER TABLE `article_versions` ADD COLUMN `reading_minutes` INTEGER NOT NULL DEFAULT 1;