-- ===============================================
-- 修改business_cards表，新增age和native_place字段
-- 执行日期: 请在执行前填写实际日期
-- 修改说明: 为名片表新增年龄和籍贯字段，支持更完整的人员信息管理
-- ===============================================

-- 1. 新增age字段 (年龄字段)
ALTER TABLE business_cards 
ADD COLUMN age INTEGER;

-- 2. 新增native_place字段 (籍贯字段)  
ALTER TABLE business_cards 
ADD COLUMN native_place TEXT;

-- 3. 为新增字段添加注释
COMMENT ON COLUMN business_cards.age IS '年龄字段 - 存储人员年龄信息，取值范围1-150';
COMMENT ON COLUMN business_cards.native_place IS '籍贯字段 - 存储人员籍贯或出生地信息';

-- 4. 验证字段是否成功添加
SELECT 
    column_name,
    data_type,
    is_nullable,
    column_default
FROM information_schema.columns 
WHERE table_name = 'business_cards' 
    AND column_name IN ('age', 'native_place')
ORDER BY column_name;

-- 5. 查看字段注释
SELECT 
    a.attname AS column_name,
    format_type(a.atttypid, a.atttypmod) AS data_type,
    COALESCE(pg_catalog.col_description(a.attrelid, a.attnum), '无注释') AS description
FROM 
    pg_catalog.pg_attribute a
JOIN 
    pg_catalog.pg_class c ON a.attrelid = c.oid
JOIN 
    pg_catalog.pg_namespace n ON c.relnamespace = n.oid
WHERE 
    c.relname = 'business_cards' 
    AND a.attname IN ('age', 'native_place')
    AND a.attnum > 0 
    AND NOT a.attisdropped
ORDER BY a.attname;

-- ===============================================
-- 执行说明：
-- 1. 请在生产环境执行前先在测试环境验证
-- 2. 建议在业务低峰期执行此脚本
-- 3. 执行前请备份相关数据
-- 4. 新增字段允许NULL值，不会影响现有数据
-- ===============================================

-- 可选：如果需要为现有记录设置默认值，可以执行以下语句
-- UPDATE business_cards SET age = NULL WHERE age IS NULL;
-- UPDATE business_cards SET native_place = '' WHERE native_place IS NULL;

-- 执行完成后检查表结构
\d business_cards;