INSERT INTO `sys_menu`
(`id`, `parent_id`, `name`, `menu_code`, `type`, `path`, `component`, `icon`, `sort`, `status`, `visible`, `permission_key`, `remark`, `deleted`)
SELECT
  1300, 1000, '系统设置', 'system_setting_group', 'M', '/system/settings', NULL, NULL, 30, 1, 1, NULL, '系统设置目录', 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 1300 OR `menu_code` = 'system_setting_group');

INSERT INTO `sys_menu`
(`id`, `parent_id`, `name`, `menu_code`, `type`, `path`, `component`, `icon`, `sort`, `status`, `visible`, `permission_key`, `remark`, `deleted`)
SELECT
  1310, 1300, '积分设置', 'system_points_settings', 'C', '/system/settings/points', 'System/PointsSettings', NULL, 10, 1, 1, 'system:points-settings:view', '积分设置菜单', 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 1310 OR `menu_code` = 'system_points_settings');

INSERT INTO `sys_menu`
(`id`, `parent_id`, `name`, `menu_code`, `type`, `path`, `component`, `icon`, `sort`, `status`, `visible`, `permission_key`, `remark`, `deleted`)
SELECT
  1311, 1310, '编辑积分设置', 'system_points_settings_edit', 'B', NULL, NULL, NULL, 1, 1, 1, 'system:points-settings:edit', '积分设置编辑按钮', 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 1311 OR `menu_code` = 'system_points_settings_edit');

INSERT INTO `sys_menu`
(`id`, `parent_id`, `name`, `menu_code`, `type`, `path`, `component`, `icon`, `sort`, `status`, `visible`, `permission_key`, `remark`, `deleted`)
SELECT
  4000, 0, '交易与财务', 'trade_root', 'M', '/trade', NULL, 'moneyCollect', 400, 1, 1, NULL, '交易与财务模块', 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 4000 OR `menu_code` = 'trade_root');

INSERT INTO `sys_menu`
(`id`, `parent_id`, `name`, `menu_code`, `type`, `path`, `component`, `icon`, `sort`, `status`, `visible`, `permission_key`, `remark`, `deleted`)
SELECT
  4100, 4000, '订单', 'trade_order_group', 'M', '/trade/order', NULL, NULL, 10, 1, 1, NULL, '订单目录', 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 4100 OR `menu_code` = 'trade_order_group');

INSERT INTO `sys_menu`
(`id`, `parent_id`, `name`, `menu_code`, `type`, `path`, `component`, `icon`, `sort`, `status`, `visible`, `permission_key`, `remark`, `deleted`)
SELECT
  4110, 4100, '积分兑换', 'trade_points_exchange', 'C', '/trade/order/points-exchange', 'Trade/PointsExchange', NULL, 10, 1, 1, 'points:exchange:view', '积分兑换菜单', 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 4110 OR `menu_code` = 'trade_points_exchange');

INSERT INTO `sys_menu`
(`id`, `parent_id`, `name`, `menu_code`, `type`, `path`, `component`, `icon`, `sort`, `status`, `visible`, `permission_key`, `remark`, `deleted`)
SELECT
  4111, 4110, '兑换详情', 'trade_points_exchange_detail', 'B', NULL, NULL, NULL, 1, 1, 1, 'points:exchange:detail', '积分兑换详情按钮', 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 4111 OR `menu_code` = 'trade_points_exchange_detail');

INSERT INTO `sys_menu`
(`id`, `parent_id`, `name`, `menu_code`, `type`, `path`, `component`, `icon`, `sort`, `status`, `visible`, `permission_key`, `remark`, `deleted`)
SELECT
  4112, 4110, '积分兑换发货', 'trade_points_exchange_ship', 'B', NULL, NULL, NULL, 2, 1, 1, 'points:exchange:ship', '积分兑换发货按钮', 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 4112 OR `menu_code` = 'trade_points_exchange_ship');

INSERT INTO `sys_menu`
(`id`, `parent_id`, `name`, `menu_code`, `type`, `path`, `component`, `icon`, `sort`, `status`, `visible`, `permission_key`, `remark`, `deleted`)
SELECT
  4113, 4110, '积分兑换删除', 'trade_points_exchange_delete', 'B', NULL, NULL, NULL, 3, 1, 1, 'points:exchange:delete', '积分兑换删除按钮', 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 4113 OR `menu_code` = 'trade_points_exchange_delete');

INSERT INTO `sys_menu`
(`id`, `parent_id`, `name`, `menu_code`, `type`, `path`, `component`, `icon`, `sort`, `status`, `visible`, `permission_key`, `remark`, `deleted`)
SELECT
  4114, 4110, '积分兑换导出', 'trade_points_exchange_export', 'B', NULL, NULL, NULL, 4, 1, 1, 'points:exchange:export', '积分兑换导出按钮', 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 4114 OR `menu_code` = 'trade_points_exchange_export');

INSERT INTO `sys_menu`
(`id`, `parent_id`, `name`, `menu_code`, `type`, `path`, `component`, `icon`, `sort`, `status`, `visible`, `permission_key`, `remark`, `deleted`)
SELECT
  4200, 4000, '财务流水', 'trade_finance_flow_group', 'M', '/trade/flow', NULL, NULL, 20, 1, 1, NULL, '财务流水目录', 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 4200 OR `menu_code` = 'trade_finance_flow_group');

INSERT INTO `sys_menu`
(`id`, `parent_id`, `name`, `menu_code`, `type`, `path`, `component`, `icon`, `sort`, `status`, `visible`, `permission_key`, `remark`, `deleted`)
SELECT
  4210, 4200, '积分收支明细', 'trade_points_ledger', 'C', '/trade/flow/points-ledger', 'Trade/PointsLedger', NULL, 10, 1, 1, 'points:ledger:view', '积分收支明细菜单', 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 4210 OR `menu_code` = 'trade_points_ledger');

INSERT INTO `sys_menu`
(`id`, `parent_id`, `name`, `menu_code`, `type`, `path`, `component`, `icon`, `sort`, `status`, `visible`, `permission_key`, `remark`, `deleted`)
SELECT
  4211, 4210, '导出积分收支明细', 'trade_points_ledger_export', 'B', NULL, NULL, NULL, 1, 1, 1, 'points:ledger:export', '积分收支明细导出按钮', 0
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `sys_menu` WHERE `id` = 4211 OR `menu_code` = 'trade_points_ledger_export');

INSERT IGNORE INTO `sys_role_menu` (`role_id`, `menu_id`)
SELECT sr.id, sm.id
FROM `sys_role` sr
JOIN `sys_menu` sm ON sm.id IN (1300, 1310, 1311, 4000, 4100, 4110, 4111, 4112, 4113, 4114, 4200, 4210, 4211)
WHERE sr.deleted = 0
  AND sr.status = 1
  AND EXISTS (
    SELECT 1
    FROM `sys_role_menu` rm
    WHERE rm.role_id = sr.id
      AND rm.menu_id IN (1100, 1200, 3060, 3080, 3081)
  );

CREATE TABLE IF NOT EXISTS `tcm_points_config` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `activity_start_at` DATETIME NOT NULL COMMENT '活动开始时间',
  `activity_end_at` DATETIME NOT NULL COMMENT '活动结束时间',
  `gift_cover` VARCHAR(255) NOT NULL COMMENT '礼品封面图',
  `gift_name` VARCHAR(64) NOT NULL COMMENT '礼品名称',
  `required_points` INT NOT NULL COMMENT '所需积分',
  `settlement_status` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '结算状态：0待结算 1已结算',
  `settled_at` DATETIME 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`),
  KEY `idx_points_config_end` (`activity_end_at`),
  KEY `idx_points_config_status` (`settlement_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='积分设置表';

CREATE TABLE IF NOT EXISTS `tcm_user_points_account` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `app_user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
  `points_balance` INT NOT NULL DEFAULT 0 COMMENT '当前积分余额',
  `last_settled_config_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '最后一次结算配置ID',
  `last_cleared_at` DATETIME 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`),
  UNIQUE KEY `uk_points_account_user` (`app_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户积分账户表';

CREATE TABLE IF NOT EXISTS `tcm_points_exchange_order` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_no` VARCHAR(32) NOT NULL COMMENT '汇总订单编号',
  `config_id` BIGINT UNSIGNED NOT NULL COMMENT '积分配置ID',
  `gift_cover` VARCHAR(255) DEFAULT NULL COMMENT '礼品封面图',
  `gift_name` VARCHAR(64) NOT NULL COMMENT '礼品名称',
  `required_points` INT NOT NULL COMMENT '所需积分',
  `activity_start_at` DATETIME NOT NULL COMMENT '活动开始时间',
  `activity_end_at` DATETIME NOT NULL COMMENT '活动结束时间',
  `clinic_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '诊所ID',
  `clinic_app_user_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '诊所用户ID',
  `clinic_name` VARCHAR(128) DEFAULT NULL COMMENT '诊所名称快照',
  `receiver_name` VARCHAR(64) DEFAULT NULL COMMENT '收件人姓名',
  `receiver_phone` VARCHAR(20) DEFAULT NULL COMMENT '收件人电话',
  `receiver_address` VARCHAR(255) DEFAULT NULL COMMENT '收货地址',
  `item_count` INT NOT NULL DEFAULT 0 COMMENT '兑换人数',
  `total_points` INT NOT NULL DEFAULT 0 COMMENT '汇总积分',
  `status` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '状态：0待发货 1已发货 2已收货',
  `tracking_no` VARCHAR(32) DEFAULT NULL COMMENT '物流单号',
  `shipped_at` DATETIME DEFAULT NULL COMMENT '发货时间',
  `completed_at` DATETIME DEFAULT NULL COMMENT '完成时间',
  `cancelled_at` DATETIME 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`),
  UNIQUE KEY `uk_points_exchange_order_no` (`order_no`),
  KEY `idx_points_exchange_order_config` (`config_id`),
  KEY `idx_points_exchange_order_clinic_app_user_id` (`clinic_app_user_id`),
  KEY `idx_points_exchange_order_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='积分兑换汇总订单表';

SET @ddl = IF (
  EXISTS (
    SELECT 1
    FROM `information_schema`.`COLUMNS`
    WHERE `TABLE_SCHEMA` = DATABASE()
      AND `TABLE_NAME` = 'tcm_points_exchange_order'
      AND `COLUMN_NAME` = 'clinic_app_user_id'
  ),
  'SELECT 1',
  'ALTER TABLE `tcm_points_exchange_order` ADD COLUMN `clinic_app_user_id` BIGINT UNSIGNED DEFAULT NULL COMMENT ''诊所用户ID'' AFTER `clinic_id`'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = IF (
  EXISTS (
    SELECT 1
    FROM `information_schema`.`STATISTICS`
    WHERE `TABLE_SCHEMA` = DATABASE()
      AND `TABLE_NAME` = 'tcm_points_exchange_order'
      AND `INDEX_NAME` = 'idx_points_exchange_order_clinic_app_user_id'
  ),
  'SELECT 1',
  'ALTER TABLE `tcm_points_exchange_order` ADD INDEX `idx_points_exchange_order_clinic_app_user_id` (`clinic_app_user_id`)'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

CREATE TABLE IF NOT EXISTS `tcm_points_exchange_order_item` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '汇总订单ID',
  `order_no` VARCHAR(32) NOT NULL COMMENT '明细订单编号',
  `app_user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
  `user_nickname` VARCHAR(64) DEFAULT NULL COMMENT '用户昵称快照',
  `user_phone` VARCHAR(20) DEFAULT NULL COMMENT '用户电话快照',
  `clinic_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '诊所ID',
  `clinic_name` VARCHAR(128) DEFAULT NULL COMMENT '诊所名称快照',
  `province_code` VARCHAR(32) DEFAULT NULL COMMENT '省编码快照',
  `province_name` VARCHAR(64) DEFAULT NULL COMMENT '省名称快照',
  `city_code` VARCHAR(32) DEFAULT NULL COMMENT '市编码快照',
  `city_name` VARCHAR(64) DEFAULT NULL COMMENT '市名称快照',
  `district_code` VARCHAR(32) DEFAULT NULL COMMENT '区编码快照',
  `district_name` VARCHAR(64) DEFAULT NULL COMMENT '区名称快照',
  `settled_points` INT NOT NULL DEFAULT 0 COMMENT '结算前积分',
  `status` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '状态：0待发货 1待取货 2兑换完成',
  `tracking_no` VARCHAR(32) DEFAULT NULL COMMENT '物流单号',
  `shipped_at` DATETIME DEFAULT NULL COMMENT '发货时间',
  `completed_at` DATETIME DEFAULT NULL COMMENT '完成时间',
  `cancelled_at` DATETIME 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`),
  UNIQUE KEY `uk_points_exchange_item_order_no` (`order_no`),
  KEY `idx_points_exchange_item_order_id` (`order_id`),
  KEY `idx_points_exchange_item_user_id` (`app_user_id`),
  KEY `idx_points_exchange_item_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='积分兑换明细订单表';

SET @ddl = IF (
  EXISTS (
    SELECT 1
    FROM `information_schema`.`STATISTICS`
    WHERE `TABLE_SCHEMA` = DATABASE()
      AND `TABLE_NAME` = 'tcm_points_exchange_order_item'
      AND `INDEX_NAME` = 'idx_points_exchange_item_config_id'
  ),
  'ALTER TABLE `tcm_points_exchange_order_item` DROP INDEX `idx_points_exchange_item_config_id`',
  'SELECT 1'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = IF (
  EXISTS (
    SELECT 1
    FROM `information_schema`.`COLUMNS`
    WHERE `TABLE_SCHEMA` = DATABASE()
      AND `TABLE_NAME` = 'tcm_points_exchange_order_item'
      AND `COLUMN_NAME` = 'config_id'
  ),
  'ALTER TABLE `tcm_points_exchange_order_item` DROP COLUMN `config_id`',
  'SELECT 1'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = IF (
  EXISTS (
    SELECT 1
    FROM `information_schema`.`COLUMNS`
    WHERE `TABLE_SCHEMA` = DATABASE()
      AND `TABLE_NAME` = 'tcm_points_exchange_order_item'
      AND `COLUMN_NAME` = 'receiver_name'
  ),
  'ALTER TABLE `tcm_points_exchange_order_item` DROP COLUMN `receiver_name`',
  'SELECT 1'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = IF (
  EXISTS (
    SELECT 1
    FROM `information_schema`.`COLUMNS`
    WHERE `TABLE_SCHEMA` = DATABASE()
      AND `TABLE_NAME` = 'tcm_points_exchange_order_item'
      AND `COLUMN_NAME` = 'receiver_phone'
  ),
  'ALTER TABLE `tcm_points_exchange_order_item` DROP COLUMN `receiver_phone`',
  'SELECT 1'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = IF (
  EXISTS (
    SELECT 1
    FROM `information_schema`.`COLUMNS`
    WHERE `TABLE_SCHEMA` = DATABASE()
      AND `TABLE_NAME` = 'tcm_points_exchange_order_item'
      AND `COLUMN_NAME` = 'receiver_address'
  ),
  'ALTER TABLE `tcm_points_exchange_order_item` DROP COLUMN `receiver_address`',
  'SELECT 1'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = IF (
  EXISTS (
    SELECT 1
    FROM `information_schema`.`COLUMNS`
    WHERE `TABLE_SCHEMA` = DATABASE()
      AND `TABLE_NAME` = 'tcm_points_exchange_order_item'
      AND `COLUMN_NAME` = 'gift_cover'
  ),
  'ALTER TABLE `tcm_points_exchange_order_item` DROP COLUMN `gift_cover`',
  'SELECT 1'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = IF (
  EXISTS (
    SELECT 1
    FROM `information_schema`.`COLUMNS`
    WHERE `TABLE_SCHEMA` = DATABASE()
      AND `TABLE_NAME` = 'tcm_points_exchange_order_item'
      AND `COLUMN_NAME` = 'gift_name'
  ),
  'ALTER TABLE `tcm_points_exchange_order_item` DROP COLUMN `gift_name`',
  'SELECT 1'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = IF (
  EXISTS (
    SELECT 1
    FROM `information_schema`.`COLUMNS`
    WHERE `TABLE_SCHEMA` = DATABASE()
      AND `TABLE_NAME` = 'tcm_points_exchange_order_item'
      AND `COLUMN_NAME` = 'required_points'
  ),
  'ALTER TABLE `tcm_points_exchange_order_item` DROP COLUMN `required_points`',
  'SELECT 1'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = IF (
  EXISTS (
    SELECT 1
    FROM `information_schema`.`COLUMNS`
    WHERE `TABLE_SCHEMA` = DATABASE()
      AND `TABLE_NAME` = 'tcm_clinic'
      AND `COLUMN_NAME` = 'province_code'
  ),
  'SELECT 1',
  'ALTER TABLE `tcm_clinic` ADD COLUMN `province_code` VARCHAR(32) DEFAULT NULL COMMENT ''省编码'' AFTER `contact_phone`'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = IF (
  EXISTS (
    SELECT 1
    FROM `information_schema`.`COLUMNS`
    WHERE `TABLE_SCHEMA` = DATABASE()
      AND `TABLE_NAME` = 'tcm_clinic'
      AND `COLUMN_NAME` = 'province_name'
  ),
  'SELECT 1',
  'ALTER TABLE `tcm_clinic` ADD COLUMN `province_name` VARCHAR(64) DEFAULT NULL COMMENT ''省名称'' AFTER `province_code`'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = IF (
  EXISTS (
    SELECT 1
    FROM `information_schema`.`COLUMNS`
    WHERE `TABLE_SCHEMA` = DATABASE()
      AND `TABLE_NAME` = 'tcm_clinic'
      AND `COLUMN_NAME` = 'city_code'
  ),
  'SELECT 1',
  'ALTER TABLE `tcm_clinic` ADD COLUMN `city_code` VARCHAR(32) DEFAULT NULL COMMENT ''市编码'' AFTER `province_name`'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = IF (
  EXISTS (
    SELECT 1
    FROM `information_schema`.`COLUMNS`
    WHERE `TABLE_SCHEMA` = DATABASE()
      AND `TABLE_NAME` = 'tcm_clinic'
      AND `COLUMN_NAME` = 'city_name'
  ),
  'SELECT 1',
  'ALTER TABLE `tcm_clinic` ADD COLUMN `city_name` VARCHAR(64) DEFAULT NULL COMMENT ''市名称'' AFTER `city_code`'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = IF (
  EXISTS (
    SELECT 1
    FROM `information_schema`.`COLUMNS`
    WHERE `TABLE_SCHEMA` = DATABASE()
      AND `TABLE_NAME` = 'tcm_clinic'
      AND `COLUMN_NAME` = 'district_code'
  ),
  'SELECT 1',
  'ALTER TABLE `tcm_clinic` ADD COLUMN `district_code` VARCHAR(32) DEFAULT NULL COMMENT ''区编码'' AFTER `city_name`'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = IF (
  EXISTS (
    SELECT 1
    FROM `information_schema`.`COLUMNS`
    WHERE `TABLE_SCHEMA` = DATABASE()
      AND `TABLE_NAME` = 'tcm_clinic'
      AND `COLUMN_NAME` = 'district_name'
  ),
  'SELECT 1',
  'ALTER TABLE `tcm_clinic` ADD COLUMN `district_name` VARCHAR(64) DEFAULT NULL COMMENT ''区名称'' AFTER `district_code`'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = IF (
  EXISTS (
    SELECT 1
    FROM `information_schema`.`COLUMNS`
    WHERE `TABLE_SCHEMA` = DATABASE()
      AND `TABLE_NAME` = 'tcm_points_exchange_order_item'
      AND `COLUMN_NAME` = 'province_code'
  ),
  'SELECT 1',
  'ALTER TABLE `tcm_points_exchange_order_item` ADD COLUMN `province_code` VARCHAR(32) DEFAULT NULL COMMENT ''省编码快照'' AFTER `clinic_name`'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = IF (
  EXISTS (
    SELECT 1
    FROM `information_schema`.`COLUMNS`
    WHERE `TABLE_SCHEMA` = DATABASE()
      AND `TABLE_NAME` = 'tcm_points_exchange_order_item'
      AND `COLUMN_NAME` = 'province_name'
  ),
  'SELECT 1',
  'ALTER TABLE `tcm_points_exchange_order_item` ADD COLUMN `province_name` VARCHAR(64) DEFAULT NULL COMMENT ''省名称快照'' AFTER `province_code`'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = IF (
  EXISTS (
    SELECT 1
    FROM `information_schema`.`COLUMNS`
    WHERE `TABLE_SCHEMA` = DATABASE()
      AND `TABLE_NAME` = 'tcm_points_exchange_order_item'
      AND `COLUMN_NAME` = 'city_code'
  ),
  'SELECT 1',
  'ALTER TABLE `tcm_points_exchange_order_item` ADD COLUMN `city_code` VARCHAR(32) DEFAULT NULL COMMENT ''市编码快照'' AFTER `province_name`'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = IF (
  EXISTS (
    SELECT 1
    FROM `information_schema`.`COLUMNS`
    WHERE `TABLE_SCHEMA` = DATABASE()
      AND `TABLE_NAME` = 'tcm_points_exchange_order_item'
      AND `COLUMN_NAME` = 'city_name'
  ),
  'SELECT 1',
  'ALTER TABLE `tcm_points_exchange_order_item` ADD COLUMN `city_name` VARCHAR(64) DEFAULT NULL COMMENT ''市名称快照'' AFTER `city_code`'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = IF (
  EXISTS (
    SELECT 1
    FROM `information_schema`.`COLUMNS`
    WHERE `TABLE_SCHEMA` = DATABASE()
      AND `TABLE_NAME` = 'tcm_points_exchange_order_item'
      AND `COLUMN_NAME` = 'district_code'
  ),
  'SELECT 1',
  'ALTER TABLE `tcm_points_exchange_order_item` ADD COLUMN `district_code` VARCHAR(32) DEFAULT NULL COMMENT ''区编码快照'' AFTER `city_name`'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = IF (
  EXISTS (
    SELECT 1
    FROM `information_schema`.`COLUMNS`
    WHERE `TABLE_SCHEMA` = DATABASE()
      AND `TABLE_NAME` = 'tcm_points_exchange_order_item'
      AND `COLUMN_NAME` = 'district_name'
  ),
  'SELECT 1',
  'ALTER TABLE `tcm_points_exchange_order_item` ADD COLUMN `district_name` VARCHAR(64) DEFAULT NULL COMMENT ''区名称快照'' AFTER `district_code`'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

CREATE TABLE IF NOT EXISTS `tcm_points_ledger` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `app_user_id` BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
  `user_nickname` VARCHAR(64) DEFAULT NULL COMMENT '用户昵称快照',
  `user_phone` VARCHAR(20) DEFAULT NULL COMMENT '用户电话快照',
  `clinic_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '诊所ID',
  `clinic_name` VARCHAR(128) DEFAULT NULL COMMENT '诊所名称快照',
  `province_code` VARCHAR(32) DEFAULT NULL COMMENT '省编码快照',
  `province_name` VARCHAR(64) DEFAULT NULL COMMENT '省名称快照',
  `city_code` VARCHAR(32) DEFAULT NULL COMMENT '市编码快照',
  `city_name` VARCHAR(64) DEFAULT NULL COMMENT '市名称快照',
  `district_code` VARCHAR(32) DEFAULT NULL COMMENT '区编码快照',
  `district_name` VARCHAR(64) DEFAULT NULL COMMENT '区名称快照',
  `income_expense_type` VARCHAR(32) NOT NULL COMMENT '收支类型：INCOME/EXPENSE',
  `business_type` VARCHAR(32) NOT NULL COMMENT '业务类型：LIVE_WATCH/POINTS_EXCHANGE',
  `content_name` VARCHAR(128) DEFAULT NULL COMMENT '内容名称',
  `reference_type` VARCHAR(32) DEFAULT NULL COMMENT '引用类型',
  `reference_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '引用ID',
  `reference_no` VARCHAR(64) DEFAULT NULL COMMENT '引用编号',
  `points_delta` INT NOT NULL DEFAULT 0 COMMENT '积分变动值',
  `operated_at` DATETIME NOT NULL COMMENT '操作时间',
  `source_type` VARCHAR(32) NOT NULL COMMENT '源类型',
  `source_id` BIGINT UNSIGNED NOT NULL COMMENT '源数据ID',
  `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`),
  UNIQUE KEY `uk_points_ledger_source` (`source_type`, `source_id`),
  KEY `idx_points_ledger_user` (`app_user_id`),
  KEY `idx_points_ledger_operated_at` (`operated_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='积分收支流水表';