-- ============================================================
-- 完整的直播间模块数据库同步脚本
-- 包含所有模型与数据库不一致的字段
-- 执行前请先备份数据库
-- 
-- 注意：此脚本已处理字段存在的情况，可安全重复执行
-- ============================================================

-- ============================================================
-- 1. livestream_activities 表
-- ============================================================

-- 1.1 承诺机制字段
-- 检查并添加 commitment_algo
SET @col_exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'livestream_activities' AND column_name = 'commitment_algo');
SET @sql = IF(@col_exists = 0, "ALTER TABLE livestream_activities ADD COLUMN commitment_algo VARCHAR(32) DEFAULT 'commit-v1' COMMENT '承诺算法版本' AFTER status", 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 检查并添加 commitment_seed_master
SET @col_exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'livestream_activities' AND column_name = 'commitment_seed_master');
SET @sql = IF(@col_exists = 0, "ALTER TABLE livestream_activities ADD COLUMN commitment_seed_master BLOB COMMENT '主种子(32字节)' AFTER commitment_algo", 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 检查并添加 commitment_seed_hash
SET @col_exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'livestream_activities' AND column_name = 'commitment_seed_hash');
SET @sql = IF(@col_exists = 0, "ALTER TABLE livestream_activities ADD COLUMN commitment_seed_hash BLOB COMMENT '种子SHA256哈希' AFTER commitment_seed_master", 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 检查并添加 commitment_state_version
SET @col_exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'livestream_activities' AND column_name = 'commitment_state_version');
SET @sql = IF(@col_exists = 0, "ALTER TABLE livestream_activities ADD COLUMN commitment_state_version INT DEFAULT 0 COMMENT '状态版本' AFTER commitment_seed_hash", 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 1.2 下单奖励字段
-- 检查并添加 order_reward_type
SET @col_exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'livestream_activities' AND column_name = 'order_reward_type');
SET @sql = IF(@col_exists = 0, "ALTER TABLE livestream_activities ADD COLUMN order_reward_type VARCHAR(32) DEFAULT '' COMMENT '下单奖励类型: flip_card/minesweeper' AFTER douyin_product_id", 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 检查并添加 order_reward_quantity
SET @col_exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'livestream_activities' AND column_name = 'order_reward_quantity');
SET @sql = IF(@col_exists = 0, "ALTER TABLE livestream_activities ADD COLUMN order_reward_quantity INT DEFAULT 1 COMMENT '下单奖励数量: 1-100' AFTER order_reward_type", 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 1.3 门票价格字段
SET @col_exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'livestream_activities' AND column_name = 'ticket_price');
SET @sql = IF(@col_exists = 0, "ALTER TABLE livestream_activities ADD COLUMN ticket_price INT DEFAULT 0 COMMENT '门票价格(分)' AFTER end_time", 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ============================================================
-- 2. livestream_prizes 表
-- ============================================================

-- 2.1 成本价字段
SET @col_exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'livestream_prizes' AND column_name = 'cost_price');
SET @sql = IF(@col_exists = 0, "ALTER TABLE livestream_prizes ADD COLUMN cost_price BIGINT DEFAULT 0 COMMENT '成本价(分)' AFTER sort", 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ============================================================
-- 3. livestream_draw_logs 表
-- ============================================================

-- 3.1 抖店订单号快照
SET @col_exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'livestream_draw_logs' AND column_name = 'shop_order_id');
SET @sql = IF(@col_exists = 0, "ALTER TABLE livestream_draw_logs ADD COLUMN shop_order_id VARCHAR(64) DEFAULT '' COMMENT '抖店订单号' AFTER douyin_order_id", 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 3.2 用户昵称
SET @col_exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'livestream_draw_logs' AND column_name = 'user_nickname');
SET @sql = IF(@col_exists = 0, "ALTER TABLE livestream_draw_logs ADD COLUMN user_nickname VARCHAR(128) DEFAULT '' COMMENT '用户昵称' AFTER douyin_user_id", 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 3.3 是否已发放奖品
SET @col_exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'livestream_draw_logs' AND column_name = 'is_granted');
SET @sql = IF(@col_exists = 0, "ALTER TABLE livestream_draw_logs ADD COLUMN is_granted TINYINT DEFAULT 0 COMMENT '是否已发放奖品' AFTER weights_total", 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 3.4 订单是否已退款
SET @col_exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'livestream_draw_logs' AND column_name = 'is_refunded');
SET @sql = IF(@col_exists = 0, "ALTER TABLE livestream_draw_logs ADD COLUMN is_refunded TINYINT DEFAULT 0 COMMENT '订单是否已退款' AFTER is_granted", 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ============================================================
-- 4. douyin_orders 表
-- ============================================================

-- 4.1 产品ID
SET @col_exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'douyin_orders' AND column_name = 'douyin_product_id');
SET @sql = IF(@col_exists = 0, "ALTER TABLE douyin_orders ADD COLUMN douyin_product_id VARCHAR(64) DEFAULT '' COMMENT '关联商品ID' AFTER shop_order_id", 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 4.2 商品数量
SET @col_exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'douyin_orders' AND column_name = 'product_count');
SET @sql = IF(@col_exists = 0, "ALTER TABLE douyin_orders ADD COLUMN product_count INT NOT NULL DEFAULT 1 COMMENT '商品数量' AFTER order_status", 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 4.3 已发放次数
SET @col_exists = (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'douyin_orders' AND column_name = 'reward_granted');
SET @sql = IF(@col_exists = 0, "ALTER TABLE douyin_orders ADD COLUMN reward_granted INT NOT NULL DEFAULT 0 COMMENT '已发放次数' AFTER product_count", 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 4.4 添加索引
SET @idx_exists = (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'douyin_orders' AND index_name = 'idx_douyin_product_id');
SET @sql = IF(@idx_exists = 0, 'CREATE INDEX idx_douyin_product_id ON douyin_orders(douyin_product_id)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ============================================================
-- 5. douyin_blacklist 表
-- ============================================================
CREATE TABLE IF NOT EXISTS `douyin_blacklist` (
  `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `douyin_user_id` VARCHAR(64) NOT NULL COMMENT '抖音用户ID',
  `reason` VARCHAR(255) DEFAULT '' COMMENT '拉黑原因',
  `operator_id` BIGINT DEFAULT 0 COMMENT '操作人ID',
  `status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态: 1=生效, 0=已解除',
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
  `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_douyin_user_id` (`douyin_user_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='抖音用户黑名单表';

-- ============================================================
-- 6. douyin_product_rewards 表
-- ============================================================
CREATE TABLE IF NOT EXISTS `douyin_product_rewards` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `product_id` VARCHAR(64) NOT NULL COMMENT '抖店商品ID',
    `product_name` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '商品名称（便于识别）',
    `reward_type` VARCHAR(32) NOT NULL COMMENT '奖励类型: game_ticket/coupon/points/product/item_card/title',
    `reward_payload` JSON COMMENT '奖励参数JSON',
    `quantity` INT NOT NULL DEFAULT 1 COMMENT '发放数量',
    `status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态: 1=启用 0=禁用',
    `created_at` DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
    UNIQUE KEY `uk_product_id` (`product_id`),
    KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='抖店商品奖励规则';

-- ============================================================
-- 完成
-- ============================================================
SELECT 'Migration completed successfully!' AS status;