-- ============================================
-- 用户实名认证功能 - 数据库迁移脚本
-- 迁移文件: 051_add_user_verification_fields.sql
-- 描述: 为用户表添加实名认证相关字段
-- 数据库: PostgreSQL
-- Schema: aigcspace
-- ============================================

-- 设置 schema
SET search_path TO aigcspace;

-- ============================================
-- 正向迁移: 添加实名认证字段
-- ============================================

-- 1. 添加真实姓名字段
ALTER TABLE aigcspace.users 
ADD COLUMN IF NOT EXISTS real_name VARCHAR(100);

-- 2. 添加身份证号字段
ALTER TABLE aigcspace.users 
ADD COLUMN IF NOT EXISTS id_card VARCHAR(18);

-- 3. 添加认证状态字段
ALTER TABLE aigcspace.users 
ADD COLUMN IF NOT EXISTS is_verified VARCHAR(20) NOT NULL DEFAULT 'unverified';

-- 4. 添加认证通过时间字段
ALTER TABLE aigcspace.users 
ADD COLUMN IF NOT EXISTS verified_at TIMESTAMP;

-- ============================================
-- 添加字段注释
-- ============================================

COMMENT ON COLUMN aigcspace.users.real_name IS '真实姓名';
COMMENT ON COLUMN aigcspace.users.id_card IS '身份证号';
COMMENT ON COLUMN aigcspace.users.is_verified IS '实名认证状态：unverified-未认证, pending-待审核, verified-已认证, rejected-已拒绝';
COMMENT ON COLUMN aigcspace.users.verified_at IS '认证通过时间';

-- ============================================
-- 创建索引
-- ============================================

-- 为认证状态创建索引（用于筛选已认证/未认证用户）
CREATE INDEX IF NOT EXISTS idx_users_is_verified ON aigcspace.users(is_verified);

-- 为身份证号创建索引（用于查重和查询）
CREATE INDEX IF NOT EXISTS idx_users_id_card ON aigcspace.users(id_card);

-- ============================================
-- 添加约束（可选）
-- ============================================

-- 为已认证用户的身份证号添加唯一约束（防止一个身份证号被多个用户使用）
-- 注意：只对已认证的用户生效
CREATE UNIQUE INDEX IF NOT EXISTS idx_users_id_card_verified_unique 
    ON aigcspace.users(id_card) 
    WHERE is_verified = 'verified' AND id_card IS NOT NULL;

-- ============================================
-- 数据迁移（可选）
-- ============================================

-- 将现有用户的认证状态设置为未认证
UPDATE aigcspace.users 
SET is_verified = 'unverified' 
WHERE is_verified IS NULL;

-- ============================================
-- 回滚迁移（注释掉，需要时取消注释）
-- ============================================

-- 删除索引
-- DROP INDEX IF EXISTS aigcspace.idx_users_id_card_verified_unique;
-- DROP INDEX IF EXISTS aigcspace.idx_users_id_card;
-- DROP INDEX IF EXISTS aigcspace.idx_users_is_verified;

-- 删除字段
-- ALTER TABLE aigcspace.users DROP COLUMN IF EXISTS verified_at;
-- ALTER TABLE aigcspace.users DROP COLUMN IF EXISTS is_verified;
-- ALTER TABLE aigcspace.users DROP COLUMN IF EXISTS id_card;
-- ALTER TABLE aigcspace.users DROP COLUMN IF EXISTS real_name;

-- ============================================
-- 验证脚本
-- ============================================

-- 查看表结构
-- \d aigcspace.users

-- 查看新增字段
-- SELECT column_name, data_type, is_nullable, column_default 
-- FROM information_schema.columns 
-- WHERE table_schema = 'aigcspace' 
--   AND table_name = 'users' 
--   AND column_name IN ('real_name', 'id_card', 'is_verified', 'verified_at');

-- 查看索引
-- SELECT indexname, indexdef 
-- FROM pg_indexes 
-- WHERE schemaname = 'aigcspace' 
--   AND tablename = 'users' 
--   AND indexname LIKE '%verified%';

-- 统计认证状态分布
-- SELECT is_verified, COUNT(*) as count 
-- FROM aigcspace.users 
-- GROUP BY is_verified;

-- ============================================
-- 完成
-- ============================================