-- ================================================================
-- 创建 duplicate_business_cards 表脚本
-- 用于存储重复名片处理记录
-- 创建日期: 2024年
-- ================================================================

-- 创建 duplicate_business_cards 表
CREATE TABLE duplicate_business_cards (
    id SERIAL PRIMARY KEY,
    main_card_id INTEGER NOT NULL,
    suspected_duplicates JSONB NOT NULL,
    duplicate_reason VARCHAR(200) NOT NULL,
    processing_status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    processed_at TIMESTAMP,
    processed_by VARCHAR(50),
    processing_notes TEXT
);

-- 添加外键约束
ALTER TABLE duplicate_business_cards 
ADD CONSTRAINT fk_duplicate_business_cards_main_card_id 
FOREIGN KEY (main_card_id) REFERENCES business_cards(id) ON DELETE CASCADE;

-- 添加表和字段注释
COMMENT ON TABLE duplicate_business_cards IS '重复名片处理记录表，用于存储发现的疑似重复名片信息和处理状态';

COMMENT ON COLUMN duplicate_business_cards.id IS '主键ID，自增序列';
COMMENT ON COLUMN duplicate_business_cards.main_card_id IS '新创建的主记录ID，关联business_cards表';
COMMENT ON COLUMN duplicate_business_cards.suspected_duplicates IS '疑似重复记录列表，JSON格式存储';
COMMENT ON COLUMN duplicate_business_cards.duplicate_reason IS '重复原因描述，最大200字符';
COMMENT ON COLUMN duplicate_business_cards.processing_status IS '处理状态：pending(待处理)/processed(已处理)/ignored(已忽略)';
COMMENT ON COLUMN duplicate_business_cards.created_at IS '记录创建时间';
COMMENT ON COLUMN duplicate_business_cards.processed_at IS '处理时间，记录被处理时的时间戳';
COMMENT ON COLUMN duplicate_business_cards.processed_by IS '处理人员标识，最大50字符';
COMMENT ON COLUMN duplicate_business_cards.processing_notes IS '处理备注，记录处理过程中的详细说明';