-- 监管任务结项功能数据库表结构
-- 创建时间: 2025-01-23
-- 数据库: MySQL 8.0+

USE nxxmdata;

-- 1. 监管任务结项表
CREATE TABLE IF NOT EXISTS regulatory_task_completions (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '结项ID',
    application_no VARCHAR(50) NOT NULL COMMENT '申请编号',
    policy_no VARCHAR(50) NOT NULL COMMENT '保单编号',
    product_name VARCHAR(100) NOT NULL COMMENT '产品名称',
    customer_name VARCHAR(100) NOT NULL COMMENT '客户姓名',
    id_type ENUM('身份证', '护照', '军官证', '其他') NOT NULL DEFAULT '身份证' COMMENT '证件类型',
    id_number VARCHAR(50) NOT NULL COMMENT '证件号码',
    product_type_premium DECIMAL(15,2) NOT NULL COMMENT '产品类型保费',
    regulatory_effective_premium DECIMAL(15,2) NOT NULL COMMENT '监管生效保费',
    insurance_period VARCHAR(100) NOT NULL COMMENT '保险期间',
    insurance_amount DECIMAL(15,2) NOT NULL COMMENT '保险金额',
    payment_method VARCHAR(50) NOT NULL COMMENT '缴费方式',
    payment_period VARCHAR(50) NOT NULL COMMENT '缴费期间',
    
    -- 结项相关字段
    completion_status ENUM('pending', 'in_progress', 'completed', 'rejected') NOT NULL DEFAULT 'pending' COMMENT '结项状态',
    completion_date DATE NULL COMMENT '结项日期',
    completion_notes TEXT NULL COMMENT '结项备注',
    completion_documents JSON NULL COMMENT '结项文档',
    
    -- 审核相关字段
    reviewer_id INT NULL COMMENT '审核人ID',
    review_date DATE NULL COMMENT '审核日期',
    review_notes TEXT NULL COMMENT '审核备注',
    
    -- 关联字段（暂不设置外键约束）
    policy_id INT NULL COMMENT '关联保单ID',
    application_id INT NULL COMMENT '关联申请ID',
    insurance_type_id INT NULL COMMENT '保险类型ID',
    customer_id INT NULL COMMENT '客户ID',
    
    -- 系统字段
    created_by INT NULL COMMENT '创建人ID',
    updated_by INT NULL COMMENT '更新人ID',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    
    -- 索引
    UNIQUE INDEX idx_application_no (application_no),
    INDEX idx_policy_no (policy_no),
    INDEX idx_customer_name (customer_name),
    INDEX idx_id_number (id_number),
    INDEX idx_completion_status (completion_status),
    INDEX idx_completion_date (completion_date),
    INDEX idx_reviewer_id (reviewer_id),
    INDEX idx_policy_id (policy_id),
    INDEX idx_application_id (application_id),
    INDEX idx_customer_id (customer_id),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='监管任务结项表';

-- 2. 监管任务结项操作日志表
CREATE TABLE IF NOT EXISTS regulatory_task_completion_logs (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '日志ID',
    completion_id INT NOT NULL COMMENT '结项ID',
    operation_type ENUM('create', 'update', 'review', 'approve', 'reject', 'complete') NOT NULL COMMENT '操作类型',
    operation_description TEXT NOT NULL COMMENT '操作描述',
    old_status VARCHAR(50) NULL COMMENT '原状态',
    new_status VARCHAR(50) NULL COMMENT '新状态',
    operator_id INT NOT NULL COMMENT '操作人ID',
    operation_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
    ip_address VARCHAR(45) NULL COMMENT 'IP地址',
    user_agent TEXT NULL COMMENT '用户代理',
    
    INDEX idx_completion_id (completion_id),
    INDEX idx_operation_type (operation_type),
    INDEX idx_operator_id (operator_id),
    INDEX idx_operation_time (operation_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='监管任务结项操作日志表';

-- 3. 监管任务结项附件表
CREATE TABLE IF NOT EXISTS regulatory_task_completion_attachments (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '附件ID',
    completion_id INT NOT NULL COMMENT '结项ID',
    file_name VARCHAR(255) NOT NULL COMMENT '文件名',
    file_path VARCHAR(500) NOT NULL COMMENT '文件路径',
    file_size BIGINT NOT NULL COMMENT '文件大小(字节)',
    file_type VARCHAR(50) NOT NULL COMMENT '文件类型',
    mime_type VARCHAR(100) NOT NULL COMMENT 'MIME类型',
    upload_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '上传时间',
    uploaded_by INT NOT NULL COMMENT '上传人ID',
    
    INDEX idx_completion_id (completion_id),
    INDEX idx_file_type (file_type),
    INDEX idx_upload_time (upload_time),
    INDEX idx_uploaded_by (uploaded_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='监管任务结项附件表';

-- 插入示例数据
INSERT INTO regulatory_task_completions (
    application_no, policy_no, product_name, customer_name, id_type, id_number,
    product_type_premium, regulatory_effective_premium, insurance_period, 
    insurance_amount, payment_method, payment_period, completion_status
) VALUES 
('APP20250123001', 'POL20250123001', '养殖保险', '张三', '身份证', '110101199001011234',
 5000.00, 4800.00, '2025-01-01至2025-12-31', 100000.00, '年缴', '1年', 'pending'),
('APP20250123002', 'POL20250123002', '牲畜保险', '李四', '身份证', '110101199002021234',
 3000.00, 2900.00, '2025-01-01至2025-12-31', 80000.00, '年缴', '1年', 'in_progress'),
('APP20250123003', 'POL20250123003', '农业保险', '王五', '身份证', '110101199003031234',
 4000.00, 3800.00, '2025-01-01至2025-12-31', 90000.00, '年缴', '1年', 'completed');