-- 保险端口系统数据库表结构（仅表结构）- 添加insurance_前缀避免冲突
-- 创建时间: 2025-01-01
-- 数据库: MySQL 8.0+

-- 使用现有数据库
USE nxxmdata;

-- 1. 保险角色表（添加insurance_前缀避免与现有roles表冲突）
CREATE TABLE IF NOT EXISTS insurance_roles (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '角色ID',
    name VARCHAR(50) NOT NULL UNIQUE COMMENT '角色名称',
    description VARCHAR(255) NULL COMMENT '角色描述',
    permissions JSON NOT NULL COMMENT '权限配置',
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active' COMMENT '状态',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    
    INDEX idx_roles_name (name),
    INDEX idx_roles_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='角色表';

-- 2. 保险用户表（添加insurance_前缀避免与现有users表冲突）
CREATE TABLE IF NOT EXISTS insurance_users (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
    password VARCHAR(255) NOT NULL COMMENT '密码',
    real_name VARCHAR(50) NOT NULL COMMENT '真实姓名',
    email VARCHAR(100) NOT NULL UNIQUE COMMENT '邮箱',
    phone VARCHAR(20) NOT NULL COMMENT '手机号',
    role_id INT NOT NULL COMMENT '角色ID',
    status ENUM('active', 'inactive', 'suspended') NOT NULL DEFAULT 'active' COMMENT '状态',
    last_login TIMESTAMP NULL COMMENT '最后登录时间',
    avatar VARCHAR(255) NULL COMMENT '头像URL',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    
    INDEX idx_users_username (username),
    INDEX idx_users_email (email),
    INDEX idx_users_phone (phone),
    INDEX idx_users_role_id (role_id),
    INDEX idx_users_status (status),
    
    FOREIGN KEY (role_id) REFERENCES insurance_roles(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='保险用户表';

-- 3. 保险类型表
CREATE TABLE IF NOT EXISTS insurance_types (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '保险类型ID',
    name VARCHAR(100) NOT NULL UNIQUE COMMENT '保险类型名称',
    description TEXT NULL COMMENT '保险类型描述',
    coverage_amount_min DECIMAL(15,2) NOT NULL DEFAULT 0.00 COMMENT '最低保额',
    coverage_amount_max DECIMAL(15,2) NOT NULL DEFAULT 1000000.00 COMMENT '最高保额',
    premium_rate DECIMAL(5,4) NOT NULL DEFAULT 0.001 COMMENT '保险费率',
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active' COMMENT '状态',
    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 '更新时间',
    
    INDEX idx_insurance_type_name (name),
    INDEX idx_insurance_type_status (status),
    
    FOREIGN KEY (created_by) REFERENCES insurance_users(id) ON DELETE SET NULL,
    FOREIGN KEY (updated_by) REFERENCES insurance_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='保险类型表';

-- 4. 保险申请表
CREATE TABLE IF NOT EXISTS insurance_applications (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '申请ID',
    application_no VARCHAR(50) NOT NULL UNIQUE COMMENT '申请编号',
    customer_name VARCHAR(100) NOT NULL COMMENT '客户姓名',
    customer_id_card VARCHAR(18) NOT NULL COMMENT '客户身份证号',
    customer_phone VARCHAR(20) NOT NULL COMMENT '客户手机号',
    customer_address VARCHAR(255) NOT NULL COMMENT '客户地址',
    insurance_type_id INT NOT NULL COMMENT '保险类型ID',
    application_amount DECIMAL(15,2) NOT NULL COMMENT '申请金额',
    status ENUM('pending', 'approved', 'rejected', 'under_review') NOT NULL DEFAULT 'pending' COMMENT '状态',
    application_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '申请日期',
    review_notes TEXT NULL COMMENT '审核备注',
    reviewer_id INT NULL COMMENT '审核人ID',
    review_date TIMESTAMP NULL COMMENT '审核日期',
    documents JSON NULL COMMENT '申请文档',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    
    INDEX idx_applications_application_no (application_no),
    INDEX idx_applications_customer_id_card (customer_id_card),
    INDEX idx_applications_customer_phone (customer_phone),
    INDEX idx_applications_status (status),
    INDEX idx_applications_application_date (application_date),
    INDEX idx_applications_insurance_type_id (insurance_type_id),
    INDEX idx_applications_reviewer_id (reviewer_id),
    
    FOREIGN KEY (insurance_type_id) REFERENCES insurance_types(id) ON DELETE RESTRICT,
    FOREIGN KEY (reviewer_id) REFERENCES insurance_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='保险申请表';

-- 5. 保单表
CREATE TABLE IF NOT EXISTS insurance_policies (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '保单ID',
    policy_no VARCHAR(50) NOT NULL UNIQUE COMMENT '保单编号',
    application_id INT NOT NULL COMMENT '关联的保险申请ID',
    insurance_type_id INT NOT NULL COMMENT '保险类型ID',
    customer_id INT NOT NULL COMMENT '客户ID',
    coverage_amount DECIMAL(15,2) NOT NULL COMMENT '保额',
    premium_amount DECIMAL(15,2) NOT NULL COMMENT '保费金额',
    start_date DATE NOT NULL COMMENT '保险开始日期',
    end_date DATE NOT NULL COMMENT '保险结束日期',
    policy_status ENUM('active', 'expired', 'cancelled', 'suspended') NOT NULL DEFAULT 'active' COMMENT '保单状态',
    payment_status ENUM('paid', 'unpaid', 'partial') NOT NULL DEFAULT 'unpaid' COMMENT '支付状态',
    payment_date DATE NULL COMMENT '支付日期',
    policy_document_url VARCHAR(500) NULL COMMENT '保单文件URL',
    terms_and_conditions TEXT NULL COMMENT '条款和条件',
    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_policy_no (policy_no),
    INDEX idx_policy_customer (customer_id),
    INDEX idx_policy_application (application_id),
    INDEX idx_policy_status (policy_status),
    INDEX idx_policy_payment_status (payment_status),
    INDEX idx_policy_dates (start_date, end_date),
    
    FOREIGN KEY (application_id) REFERENCES insurance_applications(id) ON DELETE RESTRICT,
    FOREIGN KEY (insurance_type_id) REFERENCES insurance_types(id) ON DELETE RESTRICT,
    FOREIGN KEY (customer_id) REFERENCES insurance_users(id) ON DELETE RESTRICT,
    FOREIGN KEY (created_by) REFERENCES insurance_users(id) ON DELETE SET NULL,
    FOREIGN KEY (updated_by) REFERENCES insurance_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='保单表';

-- 6. 理赔表
CREATE TABLE IF NOT EXISTS insurance_claims (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '理赔ID',
    claim_no VARCHAR(50) NOT NULL UNIQUE COMMENT '理赔编号',
    policy_id INT NOT NULL COMMENT '关联的保单ID',
    customer_id INT NOT NULL COMMENT '客户ID',
    claim_amount DECIMAL(15,2) NOT NULL COMMENT '理赔金额',
    claim_date DATE NOT NULL COMMENT '理赔发生日期',
    incident_description TEXT NOT NULL COMMENT '事故描述',
    claim_status ENUM('pending', 'approved', 'rejected', 'processing', 'paid') NOT NULL DEFAULT 'pending' COMMENT '理赔状态',
    review_notes TEXT NULL COMMENT '审核备注',
    reviewer_id INT NULL COMMENT '审核人ID',
    review_date DATE NULL COMMENT '审核日期',
    payment_date DATE NULL COMMENT '支付日期',
    supporting_documents JSON NULL COMMENT '支持文件',
    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_claim_no (claim_no),
    INDEX idx_claim_policy (policy_id),
    INDEX idx_claim_customer (customer_id),
    INDEX idx_claim_status (claim_status),
    INDEX idx_claim_date (claim_date),
    
    FOREIGN KEY (policy_id) REFERENCES insurance_policies(id) ON DELETE RESTRICT,
    FOREIGN KEY (customer_id) REFERENCES insurance_users(id) ON DELETE RESTRICT,
    FOREIGN KEY (reviewer_id) REFERENCES insurance_users(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES insurance_users(id) ON DELETE SET NULL,
    FOREIGN KEY (updated_by) REFERENCES insurance_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='理赔表';

-- 7. 保险系统配置表（添加insurance_前缀避免与现有system_configs表冲突）
CREATE TABLE IF NOT EXISTS insurance_system_configs (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '配置ID',
    config_key VARCHAR(100) NOT NULL UNIQUE COMMENT '配置键',
    config_value JSON NOT NULL COMMENT '配置值',
    description VARCHAR(255) NULL COMMENT '配置描述',
    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 '更新时间',
    
    INDEX idx_system_config_key (config_key),
    
    FOREIGN KEY (created_by) REFERENCES insurance_users(id) ON DELETE SET NULL,
    FOREIGN KEY (updated_by) REFERENCES insurance_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='保险系统配置表';

-- 8. 保险系统日志表
CREATE TABLE IF NOT EXISTS insurance_system_logs (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '日志ID',
    level ENUM('info', 'warning', 'error', 'debug') NOT NULL DEFAULT 'info' COMMENT '日志级别',
    message TEXT NOT NULL COMMENT '日志消息',
    module VARCHAR(100) NOT NULL COMMENT '模块名称',
    user_id INT NULL COMMENT '用户ID',
    ip_address VARCHAR(45) NULL COMMENT 'IP地址',
    user_agent TEXT NULL COMMENT '用户代理',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    
    INDEX idx_system_logs_level (level),
    INDEX idx_system_logs_module (module),
    INDEX idx_system_logs_user_id (user_id),
    INDEX idx_system_logs_created_at (created_at),
    
    FOREIGN KEY (user_id) REFERENCES insurance_users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='保险系统日志表';

COMMIT;

-- 显示表结构信息
SHOW TABLES LIKE 'insurance_%';

-- 显示各保险相关表注释
SELECT 
    TABLE_NAME,
    TABLE_COMMENT 
FROM 
    INFORMATION_SCHEMA.TABLES 
WHERE 
    TABLE_SCHEMA = 'nxxmdata'
    AND TABLE_NAME LIKE 'insurance_%';