-- 健康模块步骤一：老人基本信息采集字段。
-- 仅增加字段，不删除或改写既有身份、志愿者、家属数据；脚本可重复执行。
SET NAMES utf8mb4;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.columns
               WHERE table_schema = DATABASE() AND table_name = 'health_identity' AND column_name = 'id_card') = 0,
              'ALTER TABLE health_identity ADD COLUMN id_card varchar(256) NULL COMMENT ''身份证号，使用 EncryptTypeHandler AES 加密存储'' AFTER real_name',
              'DO 0');
PREPARE health_step1_stmt FROM @sql;
EXECUTE health_step1_stmt;
DEALLOCATE PREPARE health_step1_stmt;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.columns
               WHERE table_schema = DATABASE() AND table_name = 'health_identity' AND column_name = 'ethnicity') = 0,
              'ALTER TABLE health_identity ADD COLUMN ethnicity varchar(30) NULL COMMENT ''民族'' AFTER id_card',
              'DO 0');
PREPARE health_step1_stmt FROM @sql;
EXECUTE health_step1_stmt;
DEALLOCATE PREPARE health_step1_stmt;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.columns
               WHERE table_schema = DATABASE() AND table_name = 'health_identity' AND column_name = 'has_wechat') = 0,
              'ALTER TABLE health_identity ADD COLUMN has_wechat bit(1) NULL COMMENT ''是否有微信'' AFTER ethnicity',
              'DO 0');
PREPARE health_step1_stmt FROM @sql;
EXECUTE health_step1_stmt;
DEALLOCATE PREPARE health_step1_stmt;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.columns
               WHERE table_schema = DATABASE() AND table_name = 'health_elderly_profile' AND column_name = 'collection_step') = 0,
              'ALTER TABLE health_elderly_profile ADD COLUMN collection_step tinyint NOT NULL DEFAULT 1 COMMENT ''资料采集当前步骤，1-7'' AFTER special_needs',
              'DO 0');
PREPARE health_step1_stmt FROM @sql;
EXECUTE health_step1_stmt;
DEALLOCATE PREPARE health_step1_stmt;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.columns
               WHERE table_schema = DATABASE() AND table_name = 'health_elderly_profile' AND column_name = 'special_case') = 0,
              'ALTER TABLE health_elderly_profile ADD COLUMN special_case bit(1) NOT NULL DEFAULT b''0'' COMMENT ''管理员强制录入特殊情况标记'' AFTER collection_step',
              'DO 0');
PREPARE health_step1_stmt FROM @sql;
EXECUTE health_step1_stmt;
DEALLOCATE PREPARE health_step1_stmt;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.statistics
               WHERE table_schema = DATABASE() AND table_name = 'health_identity'
                 AND index_name = 'uk_health_identity_id_card') = 0,
              'ALTER TABLE health_identity ADD UNIQUE KEY uk_health_identity_id_card (tenant_id, id_card)',
              'DO 0');
PREPARE health_step1_stmt FROM @sql;
EXECUTE health_step1_stmt;
DEALLOCATE PREPARE health_step1_stmt;