SET @db_name = DATABASE();

-- ----------------------------
-- 养生课程小节资源表结构升级
-- 目标：
-- 1. 新增 course_type / video_source_type / video_file_name
-- 2. 迁移旧 sale_type 到 sale_price 语义
-- 3. 删除 chapter_remark / sale_type / play_count
-- 4. 新增 course_type 维度索引
-- ----------------------------

SET @video_table_exists = (
  SELECT COUNT(1)
  FROM information_schema.TABLES
  WHERE TABLE_SCHEMA = @db_name
    AND TABLE_NAME = 'tcm_health_course_video'
);

SET @sql_assert_video_table = IF(
  @video_table_exists = 0,
  'SELECT ''skip: tcm_health_course_video not exists''',
  'SELECT 1'
);
PREPARE stmt_assert_video_table FROM @sql_assert_video_table;
EXECUTE stmt_assert_video_table;
DEALLOCATE PREPARE stmt_assert_video_table;

SET @has_course_type = (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db_name
    AND TABLE_NAME = 'tcm_health_course_video'
    AND COLUMN_NAME = 'course_type'
);
SET @sql_add_course_type = IF(
  @video_table_exists = 1 AND @has_course_type = 0,
  'ALTER TABLE `tcm_health_course_video` ADD COLUMN `course_type` TINYINT NOT NULL DEFAULT 1 COMMENT ''课程类型：1养生课程'' AFTER `id`',
  'SELECT 1'
);
PREPARE stmt_add_course_type FROM @sql_add_course_type;
EXECUTE stmt_add_course_type;
DEALLOCATE PREPARE stmt_add_course_type;

SET @has_video_source_type = (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db_name
    AND TABLE_NAME = 'tcm_health_course_video'
    AND COLUMN_NAME = 'video_source_type'
);
SET @sql_add_video_source_type = IF(
  @video_table_exists = 1 AND @has_video_source_type = 0,
  'ALTER TABLE `tcm_health_course_video` ADD COLUMN `video_source_type` TINYINT NOT NULL DEFAULT 1 COMMENT ''视频来源：1视频地址 2本地上传'' AFTER `chapter_name`',
  'SELECT 1'
);
PREPARE stmt_add_video_source_type FROM @sql_add_video_source_type;
EXECUTE stmt_add_video_source_type;
DEALLOCATE PREPARE stmt_add_video_source_type;

SET @has_video_file_name = (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db_name
    AND TABLE_NAME = 'tcm_health_course_video'
    AND COLUMN_NAME = 'video_file_name'
);
SET @sql_add_video_file_name = IF(
  @video_table_exists = 1 AND @has_video_file_name = 0,
  'ALTER TABLE `tcm_health_course_video` ADD COLUMN `video_file_name` VARCHAR(255) DEFAULT NULL COMMENT ''本地上传原始文件名'' AFTER `video_url`',
  'SELECT 1'
);
PREPARE stmt_add_video_file_name FROM @sql_add_video_file_name;
EXECUTE stmt_add_video_file_name;
DEALLOCATE PREPARE stmt_add_video_file_name;

SET @has_sale_type = (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db_name
    AND TABLE_NAME = 'tcm_health_course_video'
    AND COLUMN_NAME = 'sale_type'
);

SET @sql_migrate_sale_price = IF(
  @video_table_exists = 1 AND @has_sale_type = 1,
  'UPDATE `tcm_health_course_video`
   SET `sale_price` = CASE
     WHEN `sale_type` = 0 THEN 0.00
     WHEN `sale_price` IS NULL THEN 0.00
     ELSE `sale_price`
   END',
  'SELECT 1'
);
PREPARE stmt_migrate_sale_price FROM @sql_migrate_sale_price;
EXECUTE stmt_migrate_sale_price;
DEALLOCATE PREPARE stmt_migrate_sale_price;

SET @sql_fill_course_type = IF(
  @video_table_exists = 1,
  'UPDATE `tcm_health_course_video` SET `course_type` = 1 WHERE `course_type` IS NULL OR `course_type` = 0',
  'SELECT 1'
);
PREPARE stmt_fill_course_type FROM @sql_fill_course_type;
EXECUTE stmt_fill_course_type;
DEALLOCATE PREPARE stmt_fill_course_type;

SET @sql_fill_video_source_type = IF(
  @video_table_exists = 1,
  'UPDATE `tcm_health_course_video` SET `video_source_type` = 1 WHERE `video_source_type` IS NULL OR `video_source_type` = 0',
  'SELECT 1'
);
PREPARE stmt_fill_video_source_type FROM @sql_fill_video_source_type;
EXECUTE stmt_fill_video_source_type;
DEALLOCATE PREPARE stmt_fill_video_source_type;

SET @has_sale_type_index = (
  SELECT COUNT(1)
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = @db_name
    AND TABLE_NAME = 'tcm_health_course_video'
    AND INDEX_NAME = 'idx_health_course_video_sale_type'
);
SET @sql_drop_sale_type_index = IF(
  @video_table_exists = 1 AND @has_sale_type_index = 1,
  'ALTER TABLE `tcm_health_course_video` DROP INDEX `idx_health_course_video_sale_type`',
  'SELECT 1'
);
PREPARE stmt_drop_sale_type_index FROM @sql_drop_sale_type_index;
EXECUTE stmt_drop_sale_type_index;
DEALLOCATE PREPARE stmt_drop_sale_type_index;

SET @has_course_type_status_index = (
  SELECT COUNT(1)
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = @db_name
    AND TABLE_NAME = 'tcm_health_course_video'
    AND INDEX_NAME = 'idx_health_course_video_type_status'
);
SET @sql_add_course_type_status_index = IF(
  @video_table_exists = 1 AND @has_course_type_status_index = 0,
  'ALTER TABLE `tcm_health_course_video` ADD INDEX `idx_health_course_video_type_status` (`course_type`, `status`)',
  'SELECT 1'
);
PREPARE stmt_add_course_type_status_index FROM @sql_add_course_type_status_index;
EXECUTE stmt_add_course_type_status_index;
DEALLOCATE PREPARE stmt_add_course_type_status_index;

SET @has_chapter_remark = (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db_name
    AND TABLE_NAME = 'tcm_health_course_video'
    AND COLUMN_NAME = 'chapter_remark'
);
SET @sql_drop_chapter_remark = IF(
  @video_table_exists = 1 AND @has_chapter_remark = 1,
  'ALTER TABLE `tcm_health_course_video` DROP COLUMN `chapter_remark`',
  'SELECT 1'
);
PREPARE stmt_drop_chapter_remark FROM @sql_drop_chapter_remark;
EXECUTE stmt_drop_chapter_remark;
DEALLOCATE PREPARE stmt_drop_chapter_remark;

SET @sql_drop_sale_type_column = IF(
  @video_table_exists = 1 AND @has_sale_type = 1,
  'ALTER TABLE `tcm_health_course_video` DROP COLUMN `sale_type`',
  'SELECT 1'
);
PREPARE stmt_drop_sale_type_column FROM @sql_drop_sale_type_column;
EXECUTE stmt_drop_sale_type_column;
DEALLOCATE PREPARE stmt_drop_sale_type_column;

SET @has_play_count = (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db_name
    AND TABLE_NAME = 'tcm_health_course_video'
    AND COLUMN_NAME = 'play_count'
);
SET @sql_drop_play_count = IF(
  @video_table_exists = 1 AND @has_play_count = 1,
  'ALTER TABLE `tcm_health_course_video` DROP COLUMN `play_count`',
  'SELECT 1'
);
PREPARE stmt_drop_play_count FROM @sql_drop_play_count;
EXECUTE stmt_drop_play_count;
DEALLOCATE PREPARE stmt_drop_play_count;