-- ----------------------------
-- 会员价格配置表
-- ----------------------------
CREATE TABLE IF NOT EXISTS `tcm_member_price_config` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `month_card_price` DECIMAL(10,2) NOT NULL COMMENT '月卡会员价格',
  `month_card_days` INT NOT NULL COMMENT '月卡时长',
  `month_card_enabled` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '月卡是否启用',
  `quarter_card_price` DECIMAL(10,2) NOT NULL COMMENT '季卡会员价格',
  `quarter_card_days` INT NOT NULL COMMENT '季卡时长',
  `quarter_card_enabled` TINYINT(1) NOT NULL DEFAULT 1 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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='会员价格配置表';

SET @has_month_card_price = (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'tcm_member_price_config'
    AND COLUMN_NAME = 'month_card_price'
);
SET @sql_add_month_card_price = IF(
  @has_month_card_price = 0,
  'ALTER TABLE `tcm_member_price_config` ADD COLUMN `month_card_price` DECIMAL(10,2) NOT NULL DEFAULT 69.00 COMMENT ''月卡会员价格'' AFTER `id`',
  'SELECT 1'
);
PREPARE stmt_add_month_card_price FROM @sql_add_month_card_price;
EXECUTE stmt_add_month_card_price;
DEALLOCATE PREPARE stmt_add_month_card_price;

SET @has_month_card_days = (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'tcm_member_price_config'
    AND COLUMN_NAME = 'month_card_days'
);
SET @sql_add_month_card_days = IF(
  @has_month_card_days = 0,
  'ALTER TABLE `tcm_member_price_config` ADD COLUMN `month_card_days` INT NOT NULL DEFAULT 30 COMMENT ''月卡时长'' AFTER `month_card_price`',
  'SELECT 1'
);
PREPARE stmt_add_month_card_days FROM @sql_add_month_card_days;
EXECUTE stmt_add_month_card_days;
DEALLOCATE PREPARE stmt_add_month_card_days;

SET @has_month_card_enabled = (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'tcm_member_price_config'
    AND COLUMN_NAME = 'month_card_enabled'
);
SET @sql_add_month_card_enabled = IF(
  @has_month_card_enabled = 0,
  'ALTER TABLE `tcm_member_price_config` ADD COLUMN `month_card_enabled` TINYINT(1) NOT NULL DEFAULT 1 COMMENT ''月卡是否启用'' AFTER `month_card_days`',
  'SELECT 1'
);
PREPARE stmt_add_month_card_enabled FROM @sql_add_month_card_enabled;
EXECUTE stmt_add_month_card_enabled;
DEALLOCATE PREPARE stmt_add_month_card_enabled;

SET @has_quarter_card_price = (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'tcm_member_price_config'
    AND COLUMN_NAME = 'quarter_card_price'
);
SET @sql_add_quarter_card_price = IF(
  @has_quarter_card_price = 0,
  'ALTER TABLE `tcm_member_price_config` ADD COLUMN `quarter_card_price` DECIMAL(10,2) NOT NULL DEFAULT 199.00 COMMENT ''季卡会员价格'' AFTER `month_card_enabled`',
  'SELECT 1'
);
PREPARE stmt_add_quarter_card_price FROM @sql_add_quarter_card_price;
EXECUTE stmt_add_quarter_card_price;
DEALLOCATE PREPARE stmt_add_quarter_card_price;

SET @has_quarter_card_days = (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'tcm_member_price_config'
    AND COLUMN_NAME = 'quarter_card_days'
);
SET @sql_add_quarter_card_days = IF(
  @has_quarter_card_days = 0,
  'ALTER TABLE `tcm_member_price_config` ADD COLUMN `quarter_card_days` INT NOT NULL DEFAULT 90 COMMENT ''季卡时长'' AFTER `quarter_card_price`',
  'SELECT 1'
);
PREPARE stmt_add_quarter_card_days FROM @sql_add_quarter_card_days;
EXECUTE stmt_add_quarter_card_days;
DEALLOCATE PREPARE stmt_add_quarter_card_days;

SET @has_quarter_card_enabled = (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'tcm_member_price_config'
    AND COLUMN_NAME = 'quarter_card_enabled'
);
SET @sql_add_quarter_card_enabled = IF(
  @has_quarter_card_enabled = 0,
  'ALTER TABLE `tcm_member_price_config` ADD COLUMN `quarter_card_enabled` TINYINT(1) NOT NULL DEFAULT 1 COMMENT ''季卡是否启用'' AFTER `quarter_card_days`',
  'SELECT 1'
);
PREPARE stmt_add_quarter_card_enabled FROM @sql_add_quarter_card_enabled;
EXECUTE stmt_add_quarter_card_enabled;
DEALLOCATE PREPARE stmt_add_quarter_card_enabled;

SET @has_legacy_member_price_columns = (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'tcm_member_price_config'
    AND COLUMN_NAME IN ('tier_one_days', 'tier_one_original_price', 'tier_one_sale_price', 'tier_two_days', 'tier_two_original_price', 'tier_two_sale_price')
);

SET @member_price_migration_sql = IF(
  @has_legacy_member_price_columns = 6,
  'UPDATE `tcm_member_price_config`
   SET
     `month_card_price` = CASE
       WHEN `month_card_price` IS NULL OR `month_card_price` <= 0 THEN COALESCE(`tier_one_sale_price`, `tier_one_original_price`, 69.00)
       ELSE `month_card_price`
     END,
     `month_card_days` = CASE
       WHEN `month_card_days` IS NULL OR `month_card_days` <= 0 THEN COALESCE(`tier_one_days`, 30)
       ELSE `month_card_days`
     END,
     `quarter_card_price` = CASE
       WHEN `quarter_card_price` IS NULL OR `quarter_card_price` <= 0 THEN COALESCE(`tier_two_sale_price`, `tier_two_original_price`, 199.00)
       ELSE `quarter_card_price`
     END,
     `quarter_card_days` = CASE
       WHEN `quarter_card_days` IS NULL OR `quarter_card_days` <= 0 THEN COALESCE(`tier_two_days`, 90)
       ELSE `quarter_card_days`
     END',
  'SELECT 1'
);

PREPARE member_price_migration_stmt FROM @member_price_migration_sql;
EXECUTE member_price_migration_stmt;
DEALLOCATE PREPARE member_price_migration_stmt;

SET @has_tier_one_name = (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'tcm_member_price_config'
    AND COLUMN_NAME = 'tier_one_name'
);
SET @sql_drop_tier_one_name = IF(
  @has_tier_one_name = 1,
  'ALTER TABLE `tcm_member_price_config` DROP COLUMN `tier_one_name`',
  'SELECT 1'
);
PREPARE stmt_drop_tier_one_name FROM @sql_drop_tier_one_name;
EXECUTE stmt_drop_tier_one_name;
DEALLOCATE PREPARE stmt_drop_tier_one_name;

SET @has_tier_one_days = (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'tcm_member_price_config'
    AND COLUMN_NAME = 'tier_one_days'
);
SET @sql_drop_tier_one_days = IF(
  @has_tier_one_days = 1,
  'ALTER TABLE `tcm_member_price_config` DROP COLUMN `tier_one_days`',
  'SELECT 1'
);
PREPARE stmt_drop_tier_one_days FROM @sql_drop_tier_one_days;
EXECUTE stmt_drop_tier_one_days;
DEALLOCATE PREPARE stmt_drop_tier_one_days;

SET @has_tier_one_original_price = (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'tcm_member_price_config'
    AND COLUMN_NAME = 'tier_one_original_price'
);
SET @sql_drop_tier_one_original_price = IF(
  @has_tier_one_original_price = 1,
  'ALTER TABLE `tcm_member_price_config` DROP COLUMN `tier_one_original_price`',
  'SELECT 1'
);
PREPARE stmt_drop_tier_one_original_price FROM @sql_drop_tier_one_original_price;
EXECUTE stmt_drop_tier_one_original_price;
DEALLOCATE PREPARE stmt_drop_tier_one_original_price;

SET @has_tier_one_sale_price = (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'tcm_member_price_config'
    AND COLUMN_NAME = 'tier_one_sale_price'
);
SET @sql_drop_tier_one_sale_price = IF(
  @has_tier_one_sale_price = 1,
  'ALTER TABLE `tcm_member_price_config` DROP COLUMN `tier_one_sale_price`',
  'SELECT 1'
);
PREPARE stmt_drop_tier_one_sale_price FROM @sql_drop_tier_one_sale_price;
EXECUTE stmt_drop_tier_one_sale_price;
DEALLOCATE PREPARE stmt_drop_tier_one_sale_price;

SET @has_tier_two_name = (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'tcm_member_price_config'
    AND COLUMN_NAME = 'tier_two_name'
);
SET @sql_drop_tier_two_name = IF(
  @has_tier_two_name = 1,
  'ALTER TABLE `tcm_member_price_config` DROP COLUMN `tier_two_name`',
  'SELECT 1'
);
PREPARE stmt_drop_tier_two_name FROM @sql_drop_tier_two_name;
EXECUTE stmt_drop_tier_two_name;
DEALLOCATE PREPARE stmt_drop_tier_two_name;

SET @has_tier_two_days = (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'tcm_member_price_config'
    AND COLUMN_NAME = 'tier_two_days'
);
SET @sql_drop_tier_two_days = IF(
  @has_tier_two_days = 1,
  'ALTER TABLE `tcm_member_price_config` DROP COLUMN `tier_two_days`',
  'SELECT 1'
);
PREPARE stmt_drop_tier_two_days FROM @sql_drop_tier_two_days;
EXECUTE stmt_drop_tier_two_days;
DEALLOCATE PREPARE stmt_drop_tier_two_days;

SET @has_tier_two_original_price = (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'tcm_member_price_config'
    AND COLUMN_NAME = 'tier_two_original_price'
);
SET @sql_drop_tier_two_original_price = IF(
  @has_tier_two_original_price = 1,
  'ALTER TABLE `tcm_member_price_config` DROP COLUMN `tier_two_original_price`',
  'SELECT 1'
);
PREPARE stmt_drop_tier_two_original_price FROM @sql_drop_tier_two_original_price;
EXECUTE stmt_drop_tier_two_original_price;
DEALLOCATE PREPARE stmt_drop_tier_two_original_price;

SET @has_tier_two_sale_price = (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'tcm_member_price_config'
    AND COLUMN_NAME = 'tier_two_sale_price'
);
SET @sql_drop_tier_two_sale_price = IF(
  @has_tier_two_sale_price = 1,
  'ALTER TABLE `tcm_member_price_config` DROP COLUMN `tier_two_sale_price`',
  'SELECT 1'
);
PREPARE stmt_drop_tier_two_sale_price FROM @sql_drop_tier_two_sale_price;
EXECUTE stmt_drop_tier_two_sale_price;
DEALLOCATE PREPARE stmt_drop_tier_two_sale_price;

-- ----------------------------
-- 会员介绍配置主表
-- ----------------------------
CREATE TABLE IF NOT EXISTS `tcm_member_intro_config` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `module_count` INT NOT NULL DEFAULT 0 COMMENT '模块数量',
  `description` TEXT DEFAULT NULL 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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='会员介绍配置主表';

SET @has_member_intro_description = (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'tcm_member_intro_config'
    AND COLUMN_NAME = 'description'
);
SET @sql_add_member_intro_description = IF(
  @has_member_intro_description = 0,
  'ALTER TABLE `tcm_member_intro_config` ADD COLUMN `description` TEXT DEFAULT NULL COMMENT ''会员权益说明'' AFTER `module_count`',
  'SELECT 1'
);
PREPARE stmt_add_member_intro_description FROM @sql_add_member_intro_description;
EXECUTE stmt_add_member_intro_description;
DEALLOCATE PREPARE stmt_add_member_intro_description;

UPDATE `tcm_member_intro_config`
SET `description` = COALESCE(`description`, '')
WHERE `description` IS NULL;

-- ----------------------------
-- 会员介绍图文模块表
-- ----------------------------
CREATE TABLE IF NOT EXISTS `tcm_member_intro_item` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `config_id` BIGINT UNSIGNED NOT NULL COMMENT '配置ID',
  `image_url` VARCHAR(255) NOT NULL COMMENT '权益图标',
  `description` VARCHAR(16) NOT NULL COMMENT '权益名称',
  `sort` INT NOT NULL DEFAULT 1 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_member_intro_item_config_sort` (`config_id`, `sort`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='会员介绍图文模块表';

SET @has_member_intro_item_description = (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'tcm_member_intro_item'
    AND COLUMN_NAME = 'description'
);

SET @sql_fill_member_intro_description = IF(
  @has_member_intro_item_description = 1,
  'UPDATE `tcm_member_intro_config` config
   LEFT JOIN (
     SELECT
       `config_id`,
       GROUP_CONCAT(TRIM(`description`) ORDER BY `sort`, `id` SEPARATOR ''\n'') AS `legacy_description`
     FROM `tcm_member_intro_item`
     WHERE `deleted` = 0
       AND `description` IS NOT NULL
       AND TRIM(`description`) <> ''''
     GROUP BY `config_id`
   ) legacy_item ON legacy_item.`config_id` = config.`id`
   SET config.`description` = CASE
     WHEN (config.`description` IS NULL OR TRIM(config.`description`) = '''')
       AND legacy_item.`legacy_description` IS NOT NULL
     THEN legacy_item.`legacy_description`
     ELSE config.`description`
   END
   WHERE config.`deleted` = 0',
  'SELECT 1'
);
PREPARE stmt_fill_member_intro_description FROM @sql_fill_member_intro_description;
EXECUTE stmt_fill_member_intro_description;
DEALLOCATE PREPARE stmt_fill_member_intro_description;

SET @sql_normalize_member_intro_item_name = IF(
  @has_member_intro_item_description = 1,
  'UPDATE `tcm_member_intro_item`
   SET `description` = CASE
     WHEN `description` IS NULL OR TRIM(`description`) = '''' THEN CONCAT(''权益'', `id`)
     ELSE LEFT(
       TRIM(
         REPLACE(
           REPLACE(
             REPLACE(`description`, CHAR(13), ''''),
             CHAR(10), ''''
           ),
           CHAR(9), ''''
         )
       ),
       16
     )
   END
   WHERE `deleted` = 0',
  'SELECT 1'
);
PREPARE stmt_normalize_member_intro_item_name FROM @sql_normalize_member_intro_item_name;
EXECUTE stmt_normalize_member_intro_item_name;
DEALLOCATE PREPARE stmt_normalize_member_intro_item_name;

SET @sql_modify_member_intro_item_description = IF(
  @has_member_intro_item_description = 1,
  'ALTER TABLE `tcm_member_intro_item` MODIFY COLUMN `description` VARCHAR(16) NOT NULL COMMENT ''权益名称''',
  'SELECT 1'
);
PREPARE stmt_modify_member_intro_item_description FROM @sql_modify_member_intro_item_description;
EXECUTE stmt_modify_member_intro_item_description;
DEALLOCATE PREPARE stmt_modify_member_intro_item_description;

UPDATE `tcm_member_intro_config` config
LEFT JOIN (
  SELECT `config_id`, COUNT(1) AS `module_count`
  FROM `tcm_member_intro_item`
  WHERE `deleted` = 0
  GROUP BY `config_id`
) item_stat ON item_stat.`config_id` = config.`id`
SET config.`module_count` = COALESCE(item_stat.`module_count`, 0)
WHERE config.`deleted` = 0;