USE `tour_guide`;

-- 目的：持久化导游端个人/企业进件资料、结算卡审核状态以及提现收款卡快照。
ALTER TABLE `t_user_withdrawal`
    ADD COLUMN `subjectType` VARCHAR(16) DEFAULT NULL COMMENT '进件主体：individual、enterprise' AFTER `userType`,
    ADD COLUMN `enterpriseName` VARCHAR(128) DEFAULT NULL COMMENT '企业名称' AFTER `subjectType`,
    ADD COLUMN `creditCode` VARCHAR(18) DEFAULT NULL COMMENT '统一社会信用代码' AFTER `enterpriseName`,
    ADD COLUMN `licenseValidityType` VARCHAR(16) DEFAULT NULL COMMENT '营业执照期限：fixed、long_term' AFTER `creditCode`,
    ADD COLUMN `licenseBeginDate` VARCHAR(10) DEFAULT NULL COMMENT '营业执照开始日期' AFTER `licenseValidityType`,
    ADD COLUMN `licenseEndDate` VARCHAR(10) DEFAULT NULL COMMENT '营业执照结束日期' AFTER `licenseBeginDate`,
    ADD COLUMN `registeredProvinceCode` VARCHAR(16) DEFAULT NULL COMMENT '注册地址省编码' AFTER `licenseEndDate`,
    ADD COLUMN `registeredCityCode` VARCHAR(16) DEFAULT NULL COMMENT '注册地址市编码' AFTER `registeredProvinceCode`,
    ADD COLUMN `registeredDistrictCode` VARCHAR(16) DEFAULT NULL COMMENT '注册地址区县编码' AFTER `registeredCityCode`,
    ADD COLUMN `registeredAddress` VARCHAR(255) DEFAULT NULL COMMENT '详细注册地址' AFTER `registeredDistrictCode`,
    ADD COLUMN `contactName` VARCHAR(64) DEFAULT NULL COMMENT '联系人姓名' AFTER `registeredAddress`,
    ADD COLUMN `bankBranchName` VARCHAR(128) DEFAULT NULL COMMENT '开户行名称' AFTER `bankBranchCode`,
    ADD COLUMN `bankCardStatus` VARCHAR(16) DEFAULT NULL COMMENT '结算卡状态：local、pending、approved、rejected' AFTER `tokenNo`,
    ADD COLUMN `bankCardReason` VARCHAR(255) DEFAULT NULL COMMENT '结算卡失败原因' AFTER `bankCardStatus`,
    ADD KEY `idx_withdrawal_user_active` (`userId`, `userType`, `type`, `status`);

UPDATE `t_user_withdrawal`
SET `subjectType` = COALESCE(`subjectType`, 'individual'),
    `bankCardStatus` = CASE
        WHEN `tokenNo` IS NOT NULL AND `tokenNo` <> '' THEN 'approved'
        WHEN `bankCardCode` IS NOT NULL AND `bankCardCode` <> '' THEN 'pending'
        ELSE NULL
    END
WHERE `userType` = 2 AND `type` = 1;

ALTER TABLE `guide_withdrawal`
    ADD COLUMN `bank_card_id` INT DEFAULT NULL COMMENT '提现结算卡账户主键' AFTER `amount`,
    ADD COLUMN `bank_name` VARCHAR(64) DEFAULT NULL COMMENT '收款银行快照' AFTER `bank_card_id`,
    ADD COLUMN `card_masked` VARCHAR(32) DEFAULT NULL COMMENT '收款卡号掩码' AFTER `bank_name`,
    ADD KEY `idx_guide_withdrawal_guide_status` (`guide_id`, `status`);