-- migration-version: 20260729_143000_EXPAND_GUIDE_REGISTRATION
-- 作用：补齐导游注册页所需的个人资料、证件有效期和收款信息字段；仅需在已有数据库执行一次。

USE `tour_guide`;

ALTER TABLE `guide_application`
    ADD COLUMN `gender` TINYINT DEFAULT NULL COMMENT '性别：1男，2女' AFTER `mobile`,
    ADD COLUMN `age_range` VARCHAR(20) DEFAULT NULL COMMENT '年龄区间' AFTER `gender`,
    ADD COLUMN `work_year_range` VARCHAR(20) DEFAULT NULL COMMENT '工作年限区间' AFTER `age_range`,
    ADD COLUMN `guide_type` VARCHAR(20) NOT NULL DEFAULT 'personal' COMMENT '导游类型：personal个人，team讲解队' AFTER `work_year_range`,
    ADD COLUMN `qualification_certificate_url` VARCHAR(500) DEFAULT NULL COMMENT '导游资格证书图片地址' AFTER `certificate_url`,
    ADD COLUMN `certificate_valid_until` DATE DEFAULT NULL COMMENT '导游证有效截止日期' AFTER `qualification_certificate_url`,
    ADD COLUMN `bank_card_no` VARCHAR(32) DEFAULT NULL COMMENT '收款银行卡号' AFTER `certificate_valid_until`,
    ADD COLUMN `bank_city_id` BIGINT UNSIGNED DEFAULT NULL COMMENT '开户城市ID' AFTER `bank_card_no`,
    ADD COLUMN `bank_city_name` VARCHAR(64) DEFAULT NULL COMMENT '开户城市名称快照' AFTER `bank_city_id`,
    ADD KEY `idx_guide_application_id_card` (`id_card_no`),
    ADD KEY `idx_guide_application_bank_city` (`bank_city_id`);

INSERT INTO `sys_schema_migration` (`version`, `description`)
VALUES ('20260729_143000_EXPAND_GUIDE_REGISTRATION', '补齐导游注册资料、证件有效期和收款信息字段');