-- Migration: 002_auth_media_drawing_reference.sql
-- Refresh tokens, password resets, richer media/drawing metadata,
-- annotation storage and the admin-managed Quick Reference.

SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------------
-- Authentication support
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `refresh_tokens` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `token_hash` VARCHAR(255) NOT NULL,
  `expires_at` DATETIME NOT NULL,
  `revoked_at` DATETIME DEFAULT NULL,
  `user_agent` VARCHAR(512) DEFAULT NULL,
  `ip` VARCHAR(64) DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ux_refresh_token_hash` (`token_hash`),
  INDEX `idx_refresh_tokens_user` (`user_id`),
  CONSTRAINT `fk_refresh_tokens_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `password_reset_tokens` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `token_hash` VARCHAR(255) NOT NULL,
  `expires_at` DATETIME NOT NULL,
  `used_at` DATETIME DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ux_password_reset_hash` (`token_hash`),
  INDEX `idx_password_reset_user` (`user_id`),
  CONSTRAINT `fk_password_reset_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------------
-- Media library metadata (section 19 of the requirements)
-- ---------------------------------------------------------------------------

ALTER TABLE `media`
  ADD COLUMN `file_type` VARCHAR(32) NOT NULL DEFAULT 'OTHER' AFTER `file_name`,
  ADD COLUMN `caption` VARCHAR(512) DEFAULT NULL AFTER `thumbnail_path`,
  ADD COLUMN `alt_text` VARCHAR(512) DEFAULT NULL AFTER `caption`,
  ADD COLUMN `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `created_at`;

-- ---------------------------------------------------------------------------
-- Drawing engine (section 13 of the requirements)
-- ---------------------------------------------------------------------------

ALTER TABLE `drawings`
  ADD COLUMN `template_id` BIGINT UNSIGNED DEFAULT NULL AFTER `anonymous_session_id`,
  ADD COLUMN `canvas_width` INT DEFAULT NULL AFTER `template_id`,
  ADD COLUMN `canvas_height` INT DEFAULT NULL AFTER `canvas_width`,
  ADD COLUMN `title` VARCHAR(255) DEFAULT NULL AFTER `canvas_height`,
  ADD COLUMN `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `created_at`,
  ADD INDEX `idx_drawings_session` (`anonymous_session_id`),
  ADD CONSTRAINT `fk_drawings_template` FOREIGN KEY (`template_id`) REFERENCES `drawing_templates`(`id`) ON DELETE SET NULL;

-- ---------------------------------------------------------------------------
-- Annotation engine (section 14) - annotations never modify the source image
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `annotations` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `anonymous_session_id` VARCHAR(128) NOT NULL,
  `activity_id` BIGINT UNSIGNED DEFAULT NULL,
  `media_id` BIGINT UNSIGNED DEFAULT NULL,
  `entity_type` VARCHAR(128) DEFAULT NULL,
  `entity_id` BIGINT UNSIGNED DEFAULT NULL,
  `annotations_json` JSON,
  `preview_image_path` VARCHAR(1024) DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_annotations_session` (`anonymous_session_id`),
  INDEX `idx_annotations_activity` (`activity_id`),
  CONSTRAINT `fk_annotations_activity` FOREIGN KEY (`activity_id`) REFERENCES `activities`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_annotations_media` FOREIGN KEY (`media_id`) REFERENCES `media`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------------
-- Quick Reference (section 34) - fully admin managed, no hard-coded content
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `quick_reference_items` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `slug` VARCHAR(255) NOT NULL,
  `category` VARCHAR(128) NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `summary` TEXT,
  `blocks` JSON,
  `order_index` INT DEFAULT 0,
  `status` ENUM('DRAFT','IN_REVIEW','PUBLISHED','ARCHIVED') NOT NULL DEFAULT 'DRAFT',
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ux_quick_reference_slug` (`slug`),
  INDEX `idx_quick_reference_category` (`category`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------------
-- Columns the ORM entities already expect
-- ---------------------------------------------------------------------------

ALTER TABLE `cases`
  ADD COLUMN `status` ENUM('DRAFT','IN_REVIEW','PUBLISHED','ARCHIVED') NOT NULL DEFAULT 'DRAFT' AFTER `meta`,
  ADD COLUMN `anonymous_session_id` VARCHAR(128) DEFAULT NULL AFTER `status`,
  ADD COLUMN `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `created_at`,
  ADD INDEX `idx_cases_session` (`anonymous_session_id`);

ALTER TABLE `decision_trees`
  ADD COLUMN `slug` VARCHAR(255) DEFAULT NULL AFTER `title`,
  ADD COLUMN `description` TEXT AFTER `slug`,
  ADD COLUMN `status` ENUM('DRAFT','IN_REVIEW','PUBLISHED','ARCHIVED') NOT NULL DEFAULT 'DRAFT' AFTER `meta`,
  ADD COLUMN `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `created_at`;

ALTER TABLE `decision_tree_nodes`
  ADD COLUMN `order_index` INT DEFAULT 0;

ALTER TABLE `decision_tree_edges`
  ADD COLUMN `label` VARCHAR(255) DEFAULT NULL AFTER `condition`,
  ADD COLUMN `order_index` INT DEFAULT 0;

ALTER TABLE `activities`
  ADD COLUMN `description` TEXT AFTER `title`,
  ADD COLUMN `instructions` TEXT AFTER `description`,
  ADD COLUMN `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `created_at`;

ALTER TABLE `lessons`
  ADD COLUMN `description` TEXT AFTER `title`,
  ADD COLUMN `status` ENUM('DRAFT','IN_REVIEW','PUBLISHED','ARCHIVED') NOT NULL DEFAULT 'DRAFT' AFTER `order_index`,
  ADD COLUMN `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `created_at`;

ALTER TABLE `content_blocks`
  ADD COLUMN `title` VARCHAR(255) DEFAULT NULL AFTER `type`,
  ADD COLUMN `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `created_at`;

ALTER TABLE `progress`
  ADD COLUMN `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP AFTER `progress_json`,
  ADD UNIQUE KEY `ux_progress_entity` (`anonymous_session_id`,`entity_type`,`entity_id`);

SET FOREIGN_KEY_CHECKS = 1;
