AiDOPWarehouse/server/Admin.NET.Web.Entry/UpdateScripts/1.0.332.sql

-- 1.0.332.sql  S0：EmployeeMaster 补齐旧系统字段——新增 城市/国家/业务电话 三列（幂等）。
--   背景：雇员编辑表单对齐旧系统雇员信息，AiDOP EmployeeMaster 缺 City/Country/BusinessPhone 三列，
--         其余字段（出生/入职/离职日期、婚姻、工作中心、卡号、默认工作地点）实体已有、本 DDL 不涉及。
--   仅新增可空列，不改既有列类型、不改 tenant_id 可空性、不插入/更新/删除任何业务数据。
--   EmployeeMaster 为 CodeFirst 管理实体，本 DDL 与 EnableIncreTable 双保险（列存在即跳过，重启不冲突）。
-- ============================================================================

-- ========== City（城市） ==========
SET @has := (SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'EmployeeMaster' AND COLUMN_NAME = 'City');
SET @sql := IF(@has = 0, 'ALTER TABLE EmployeeMaster ADD COLUMN `City` varchar(100) NULL COMMENT ''城市''', 'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ========== Country（国家） ==========
SET @has := (SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'EmployeeMaster' AND COLUMN_NAME = 'Country');
SET @sql := IF(@has = 0, 'ALTER TABLE EmployeeMaster ADD COLUMN `Country` varchar(100) NULL COMMENT ''国家''', 'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ========== BusinessPhone（业务电话） ==========
SET @has := (SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'EmployeeMaster' AND COLUMN_NAME = 'BusinessPhone');
SET @sql := IF(@has = 0, 'ALTER TABLE EmployeeMaster ADD COLUMN `BusinessPhone` varchar(50) NULL COMMENT ''业务电话''', 'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ========== 验证 ==========
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'EmployeeMaster'
  AND COLUMN_NAME IN ('City', 'Country', 'BusinessPhone')
ORDER BY COLUMN_NAME;
-- 期望：City/Country/BusinessPhone 三列均存在且可空。

-- ============================================================================
-- 回滚 SQL（如需）：
--   ALTER TABLE EmployeeMaster DROP COLUMN `City`, DROP COLUMN `Country`, DROP COLUMN `BusinessPhone`;
-- ============================================================================