-- ============================================================
-- Msg 服务数据库初始化脚本（v1.0 完整实现）
-- 对齐 services/msg/src/notifications/notifications.schema.ts
--      services/msg/src/shared/outbox/outbox.schema.ts
-- 仲裁依据：02-architecture-design.md §3.1 + G11（cuid2 varchar(32)）
-- ============================================================

-- 数据库已由 docker-compose 创建（msg_db），此处仅建表
-- 若需切换数据库：USE msg_db;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ============================================================
-- 1. msg_notifications（通知主表 · 扩展）
-- ============================================================
CREATE TABLE IF NOT EXISTS `msg_notifications` (
  `id` VARCHAR(32) NOT NULL COMMENT 'cuid2 主键',
  `user_id` VARCHAR(32) NOT NULL COMMENT '接收用户 ID',
  `type` VARCHAR(32) NOT NULL COMMENT '通知类型: system/exam/homework/grade/attendance/mastery',
  `title` VARCHAR(255) NOT NULL COMMENT '通知标题',
  `content` TEXT NOT NULL COMMENT '通知正文',
  `channel` VARCHAR(32) NOT NULL COMMENT '渠道: in_app/email/sms/push/wechat',
  `is_read` BOOLEAN NOT NULL DEFAULT FALSE COMMENT '是否已读',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `status` VARCHAR(32) NOT NULL DEFAULT 'pending' COMMENT '状态: pending/sent/delivered/read/recalled/failed',
  `metadata` JSON NULL COMMENT '扩展元数据',
  `related_entity_type` VARCHAR(64) NULL COMMENT '关联实体类型(exam/homework/grade/attendance/mastery)',
  `related_entity_id` VARCHAR(32) NULL COMMENT '关联实体 ID',
  `group_id` VARCHAR(32) NULL COMMENT '群组 ID（批量发送/撤回用）',
  `sender_id` VARCHAR(32) NULL COMMENT '发送者 ID',
  `template_id` VARCHAR(32) NULL COMMENT '使用的模板 ID',
  `event_id` VARCHAR(64) NULL COMMENT '幂等键（Kafka event_id 或自生成）',
  `read_at` TIMESTAMP NULL COMMENT '已读时间',
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_notifications_event_id` (`event_id`),
  KEY `idx_notifications_user_id` (`user_id`),
  KEY `idx_notifications_is_read` (`is_read`),
  KEY `idx_notifications_created_at` (`created_at`),
  KEY `idx_notifications_user_read` (`user_id`, `is_read`),
  KEY `idx_notifications_group_id` (`group_id`),
  KEY `idx_notifications_related` (`related_entity_type`, `related_entity_id`),
  KEY `idx_notifications_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='msg 通知主表';

-- ============================================================
-- 2. msg_notification_preferences（用户偏好 · 扩展）
-- ============================================================
CREATE TABLE IF NOT EXISTS `msg_notification_preferences` (
  `id` VARCHAR(32) NOT NULL COMMENT 'cuid2 主键',
  `user_id` VARCHAR(32) NOT NULL COMMENT '用户 ID',
  `type` VARCHAR(32) NOT NULL COMMENT '通知类型',
  `channels` JSON NOT NULL COMMENT '启用的渠道数组,如 ["in_app","email"]',
  `frequency_limit` INT NULL COMMENT '频率限制(每小时最大通知数)',
  `quiet_hours_start` VARCHAR(8) NULL COMMENT '免打扰开始(HH:MM)',
  `quiet_hours_end` VARCHAR(8) NULL COMMENT '免打扰结束(HH:MM)',
  `quiet_hours_timezone` VARCHAR(64) NULL DEFAULT 'Asia/Shanghai',
  `enabled` BOOLEAN NOT NULL DEFAULT TRUE COMMENT '是否启用该类型偏好',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_preferences_user_type` (`user_id`, `type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='msg 用户通知偏好';

-- ============================================================
-- 3. msg_notification_templates（通知模板）
-- ============================================================
CREATE TABLE IF NOT EXISTS `msg_notification_templates` (
  `id` VARCHAR(32) NOT NULL COMMENT 'cuid2 主键',
  `code` VARCHAR(64) NOT NULL COMMENT '模板编码(业务可读)',
  `type` VARCHAR(32) NOT NULL COMMENT '通知类型',
  `title_template` VARCHAR(255) NOT NULL COMMENT '标题模板(含 {{var}})',
  `content_template` TEXT NOT NULL COMMENT '正文模板(含 {{var}})',
  `default_channels` JSON NOT NULL COMMENT '默认渠道数组',
  `variables` JSON NOT NULL COMMENT '变量声明数组,如 ["userName","examTitle"]',
  `locale` VARCHAR(16) NOT NULL DEFAULT 'zh-CN',
  `status` VARCHAR(32) NOT NULL DEFAULT 'draft' COMMENT '状态: draft/active/archived',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_templates_code` (`code`),
  KEY `idx_templates_type` (`type`),
  KEY `idx_templates_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='msg 通知模板';

-- ============================================================
-- 4. msg_notification_deliveries（投递记录）
-- ============================================================
CREATE TABLE IF NOT EXISTS `msg_notification_deliveries` (
  `id` VARCHAR(32) NOT NULL COMMENT 'cuid2 主键',
  `notification_id` VARCHAR(32) NOT NULL COMMENT '关联通知 ID',
  `channel` VARCHAR(32) NOT NULL COMMENT '投递渠道',
  `status` VARCHAR(32) NOT NULL DEFAULT 'pending' COMMENT '状态: pending/sent/delivered/failed/retrying',
  `external_id` VARCHAR(128) NULL COMMENT '外部服务返回的消息 ID',
  `attempt_count` INT NOT NULL DEFAULT 0,
  `max_retry` INT NOT NULL DEFAULT 3,
  `last_error` TEXT NULL,
  `next_retry_at` TIMESTAMP NULL,
  `delivered_at` TIMESTAMP NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_deliveries_notification_id` (`notification_id`),
  KEY `idx_deliveries_status` (`status`),
  KEY `idx_deliveries_next_retry` (`next_retry_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='msg 通知投递记录';

-- ============================================================
-- 5. msg_outbox_events（事务性 Outbox）
-- ============================================================
CREATE TABLE IF NOT EXISTS `msg_outbox_events` (
  `event_id` VARCHAR(64) NOT NULL COMMENT '事件 ID(Kafka 幂等键)',
  `aggregate_type` VARCHAR(64) NOT NULL COMMENT '聚合类型: notification',
  `aggregate_id` VARCHAR(32) NOT NULL COMMENT '聚合 ID(通知 ID)',
  `event_type` VARCHAR(64) NOT NULL COMMENT '事件类型: notification.sent/read/recalled/failed',
  `topic` VARCHAR(128) NOT NULL COMMENT '目标 Kafka topic',
  `payload` JSON NOT NULL COMMENT '事件 payload',
  `status` VARCHAR(16) NOT NULL DEFAULT 'pending' COMMENT '状态: pending/published/failed',
  `retry_count` INT NOT NULL DEFAULT 0,
  `max_retry_count` INT NOT NULL DEFAULT 5,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `published_at` TIMESTAMP NULL,
  `next_retry_at` TIMESTAMP NULL,
  `last_error` TEXT NULL,
  `metadata` JSON NULL,
  PRIMARY KEY (`event_id`),
  KEY `idx_outbox_status_next_retry` (`status`, `next_retry_at`),
  KEY `idx_outbox_topic` (`topic`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='msg Outbox 事务性事件';

-- ============================================================
-- 6. processed_events（消费幂等 · Redis 降级表）
-- ============================================================
CREATE TABLE IF NOT EXISTS `processed_events` (
  `event_id` VARCHAR(64) NOT NULL COMMENT '事件 ID(幂等键)',
  `topic` VARCHAR(128) NOT NULL COMMENT '来源 topic',
  `processed_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`event_id`),
  KEY `idx_processed_topic` (`topic`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='msg 消费幂等记录';

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
-- 初始化数据：默认通知模板（可选）
-- ============================================================
INSERT INTO `msg_notification_templates` (`id`, `code`, `type`, `title_template`, `content_template`, `default_channels`, `variables`, `locale`, `status`)
SELECT * FROM (SELECT
  'tmpl_welcome_001' AS id,
  'welcome' AS code,
  'system' AS type,
  '欢迎加入 Edu 云课堂' AS title_template,
  '你好 {{userName}}，欢迎加入 Edu 云课堂！开始你的学习之旅吧。' AS content_template,
  JSON_ARRAY('in_app') AS default_channels,
  JSON_ARRAY('userName') AS variables,
  'zh-CN' AS locale,
  'active' AS status
) AS tmp
WHERE NOT EXISTS (SELECT 1 FROM `msg_notification_templates` WHERE `code` = 'welcome');

INSERT INTO `msg_notification_templates` (`id`, `code`, `type`, `title_template`, `content_template`, `default_channels`, `variables`, `locale`, `status`)
SELECT * FROM (SELECT
  'tmpl_exam_published_001' AS id,
  'exam_published' AS code,
  'exam' AS type,
  '新考试通知' AS title_template,
  '「{{className}}」班级发布了新考试：{{examTitle}}' AS content_template,
  JSON_ARRAY('in_app') AS default_channels,
  JSON_ARRAY('className', 'examTitle') AS variables,
  'zh-CN' AS locale,
  'active' AS status
) AS tmp
WHERE NOT EXISTS (SELECT 1 FROM `msg_notification_templates` WHERE `code` = 'exam_published');

INSERT INTO `msg_notification_templates` (`id`, `code`, `type`, `title_template`, `content_template`, `default_channels`, `variables`, `locale`, `status`)
SELECT * FROM (SELECT
  'tmpl_grade_recorded_001' AS id,
  'grade_recorded' AS code,
  'grade' AS type,
  '成绩录入通知' AS title_template,
  '你的{{subject}}成绩已录入{{score}}' AS content_template,
  JSON_ARRAY('in_app') AS default_channels,
  JSON_ARRAY('subject', 'score') AS variables,
  'zh-CN' AS locale,
  'active' AS status
) AS tmp
WHERE NOT EXISTS (SELECT 1 FROM `msg_notification_templates` WHERE `code` = 'grade_recorded');