-- 保险端口系统数据库表结构
-- 创建时间: 2025-01-01
-- 数据库: MySQL 8.0+

-- 创建数据库
CREATE DATABASE IF NOT EXISTS insurance_data 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE insurance_data;

-- 1. 角色表
CREATE TABLE IF NOT EXISTS 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. 用户表
CREATE TABLE IF NOT EXISTS 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 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 users(id) ON DELETE SET NULL,
    FOREIGN KEY (updated_by) REFERENCES 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 users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='保险申请表';

-- 5. 保单表
CREATE TABLE IF NOT EXISTS 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 users(id) ON DELETE RESTRICT,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='保单表';

-- 6. 理赔表
CREATE TABLE IF NOT EXISTS 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 policies(id) ON DELETE RESTRICT,
    FOREIGN KEY (customer_id) REFERENCES users(id) ON DELETE RESTRICT,
    FOREIGN KEY (reviewer_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='理赔表';

-- 7. 系统配置表
CREATE TABLE IF NOT EXISTS 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 users(id) ON DELETE SET NULL,
    FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统配置表';

-- 8. 系统日志表
CREATE TABLE IF NOT EXISTS 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 users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统日志表';

-- 插入初始数据
-- 插入默认角色
INSERT INTO roles (name, description, permissions, status) VALUES 
('admin', '系统管理员', '["user:read","user:create","user:update","user:delete","insurance:read","insurance:create","insurance:update","insurance:delete","insurance:review","policy:read","policy:create","policy:update","policy:delete","claim:read","claim:create","claim:update","claim:review","system:read","system:update","system:admin"]', 'active'),
('agent', '保险代理人', '["insurance:read","insurance:create","policy:read","policy:create","claim:read","claim:create"]', 'active'),
('customer', '客户', '["insurance:read","policy:read","claim:read","claim:create"]', 'active');

-- 插入默认保险类型
INSERT INTO insurance_types (name, description, coverage_amount_min, coverage_amount_max, premium_rate, status) VALUES 
('人寿保险', '提供生命保障的保险产品', 10000.00, 1000000.00, 0.0025, 'active'),
('健康保险', '提供医疗保障的保险产品', 5000.00, 500000.00, 0.0030, 'active'),
('意外伤害保险', '提供意外伤害保障的保险产品', 1000.00, 200000.00, 0.0015, 'active'),
('财产保险', '提供财产保障的保险产品', 5000.00, 1000000.00, 0.0020, 'active'),
('车险', '提供车辆保障的保险产品', 1000.00, 500000.00, 0.0040, 'active');

-- 插入默认系统配置
INSERT INTO system_configs (config_key, config_value, description) VALUES 
('system_name', '"保险端口系统"', '系统名称'),
('company_name', '"XX保险公司"', '公司名称'),
('contact_email', '"support@insurance.com"', '联系邮箱'),
('contact_phone', '"400-123-4567"', '联系电话'),
('max_file_size', '10485760', '最大文件上传大小（字节）'),
('allowed_file_types', '["jpg", "jpeg", "png", "pdf", "doc", "docx"]', '允许上传的文件类型');

-- 创建管理员用户（密码需要在前端加密后存储）
-- 默认密码: admin123 (需要在前端使用bcrypt加密)
INSERT INTO users (username, password, real_name, email, phone, role_id, status) VALUES 
('admin', '$2b$12$r6KcJ3V9q8wY7hLmNpQZ0uBf3dG7hJ2kL9mNpQr3tY6zX8vB1cF4', '系统管理员', 'admin@insurance.com', '13800138000', 1, 'active');

COMMIT;

-- 显示表结构信息
SHOW TABLES;

-- 显示各表注释
SELECT 
    TABLE_NAME,
    TABLE_COMMENT 
FROM 
    INFORMATION_SCHEMA.TABLES 
WHERE 
    TABLE_SCHEMA = 'nxxmdata';

-- 显示表创建语句示例
SHOW CREATE TABLE users;