SET @db_name = DATABASE();

-- ----------------------------
-- APP 我的课程相关表结构升级
-- 目标：
-- 1. 养生课程播放记录表新增续播位置字段 last_play_position_seconds
-- 2. 新增养生课程下载记录表 tcm_health_course_download_record
-- ----------------------------

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

SET @sql_assert_play_record_table = IF(
  @play_record_table_exists = 0,
  'SELECT ''skip: tcm_health_course_play_record not exists''',
  'SELECT 1'
);
PREPARE stmt_assert_play_record_table FROM @sql_assert_play_record_table;
EXECUTE stmt_assert_play_record_table;
DEALLOCATE PREPARE stmt_assert_play_record_table;

SET @has_last_play_position_seconds = (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @db_name
    AND TABLE_NAME = 'tcm_health_course_play_record'
    AND COLUMN_NAME = 'last_play_position_seconds'
);

SET @sql_add_last_play_position_seconds = IF(
  @play_record_table_exists = 1 AND @has_last_play_position_seconds = 0,
  'ALTER TABLE `tcm_health_course_play_record`
   ADD COLUMN `last_play_position_seconds` INT NOT NULL DEFAULT 0 COMMENT ''本次结束时播放位置（秒）'' AFTER `is_completed`',
  'SELECT 1'
);
PREPARE stmt_add_last_play_position_seconds FROM @sql_add_last_play_position_seconds;
EXECUTE stmt_add_last_play_position_seconds;
DEALLOCATE PREPARE stmt_add_last_play_position_seconds;

SET @sql_fill_last_play_position_seconds = IF(
  @play_record_table_exists = 1,
  'UPDATE `tcm_health_course_play_record`
   SET `last_play_position_seconds` = 0
   WHERE `last_play_position_seconds` IS NULL',
  'SELECT 1'
);
PREPARE stmt_fill_last_play_position_seconds FROM @sql_fill_last_play_position_seconds;
EXECUTE stmt_fill_last_play_position_seconds;
DEALLOCATE PREPARE stmt_fill_last_play_position_seconds;

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

SET @sql_create_download_record_table = IF(
  @download_record_table_exists = 0,
  'CREATE TABLE `tcm_health_course_download_record` (
      `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
      `app_user_id` BIGINT UNSIGNED NOT NULL COMMENT ''用户ID'',
      `course_id` BIGINT UNSIGNED NOT NULL COMMENT ''养生课程ID'',
      `course_video_id` BIGINT UNSIGNED NOT NULL COMMENT ''视频小节ID'',
      `course_title_snapshot` VARCHAR(128) NOT NULL DEFAULT '''' COMMENT ''课程标题快照'',
      `chapter_name_snapshot` VARCHAR(128) NOT NULL DEFAULT '''' COMMENT ''章节标题快照'',
      `cover_img_snapshot` VARCHAR(255) NOT NULL DEFAULT '''' COMMENT ''封面图快照'',
      `video_url_snapshot` VARCHAR(512) NOT NULL DEFAULT '''' COMMENT ''视频地址快照'',
      `duration_seconds_snapshot` INT NOT NULL DEFAULT 0 COMMENT ''视频时长快照（秒）'',
      `deleted` TINYINT(1) NOT NULL DEFAULT 0,
      `created_by` VARCHAR(64) NOT NULL DEFAULT ''系统初始化'' COMMENT ''创建人'',
      `updated_by` VARCHAR(64) NOT NULL DEFAULT ''系统初始化'' COMMENT ''更新人'',
      `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (`id`),
      KEY `idx_health_course_download_user_deleted_created` (`app_user_id`, `deleted`, `created_at`),
      KEY `idx_health_course_download_user_video_deleted` (`app_user_id`, `course_video_id`, `deleted`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=''养生课程下载记录表''',
  'SELECT 1'
);
PREPARE stmt_create_download_record_table FROM @sql_create_download_record_table;
EXECUTE stmt_create_download_record_table;
DEALLOCATE PREPARE stmt_create_download_record_table;