-- 生资保单相关数据库表结构
-- 创建时间: 2024-01-XX
-- 说明: 包含牲畜类型、生资保单、理赔记录等表

USE insurance_data;

-- 1. 生资保险类型表（牲畜类型）
CREATE TABLE IF NOT EXISTS livestock_types (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '牲畜类型ID',
    name VARCHAR(50) NOT NULL UNIQUE COMMENT '牲畜类型名称（如：牛、羊、猪等）',
    code VARCHAR(20) NOT NULL UNIQUE COMMENT '牲畜类型代码',
    description TEXT NULL COMMENT '牲畜类型描述',
    unit_price_min DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '单头最低价值',
    unit_price_max DECIMAL(10,2) NOT NULL DEFAULT 50000.00 COMMENT '单头最高价值',
    premium_rate DECIMAL(5,4) NOT NULL DEFAULT 0.0050 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_livestock_type_name (name),
    INDEX idx_livestock_type_code (code),
    INDEX idx_livestock_type_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='牲畜类型表';

-- 2. 生资保单表
CREATE TABLE IF NOT EXISTS livestock_policies (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '生资保单ID',
    policy_no VARCHAR(50) NOT NULL UNIQUE COMMENT '保单编号',
    policyholder_name VARCHAR(100) NOT NULL COMMENT '投保人姓名',
    policyholder_id_card VARCHAR(18) NOT NULL COMMENT '投保人身份证号',
    policyholder_phone VARCHAR(20) NOT NULL COMMENT '投保人手机号',
    policyholder_address VARCHAR(500) NOT NULL COMMENT '投保人地址',
    
    livestock_type_id INT NOT NULL COMMENT '牲畜类型ID',
    livestock_count INT NOT NULL DEFAULT 1 COMMENT '参保数量',
    unit_value DECIMAL(10,2) NOT NULL COMMENT '单头价值',
    total_value DECIMAL(15,2) NOT NULL COMMENT '总保额',
    premium_amount DECIMAL(10,2) NOT NULL COMMENT '保费金额',
    
    start_date DATE NOT NULL COMMENT '保险开始日期',
    end_date DATE NOT NULL COMMENT '保险结束日期',
    
    farm_name VARCHAR(200) NULL COMMENT '养殖场名称',
    farm_address VARCHAR(500) NOT NULL COMMENT '养殖场地址',
    farm_license VARCHAR(100) 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',
    notes 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_livestock_policy_no (policy_no),
    INDEX idx_livestock_policy_holder (policyholder_name),
    INDEX idx_livestock_policy_id_card (policyholder_id_card),
    INDEX idx_livestock_policy_phone (policyholder_phone),
    INDEX idx_livestock_policy_status (policy_status),
    INDEX idx_livestock_policy_payment_status (payment_status),
    INDEX idx_livestock_policy_dates (start_date, end_date),
    INDEX idx_livestock_policy_type (livestock_type_id),
    
    FOREIGN KEY (livestock_type_id) REFERENCES livestock_types(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='生资保单表';

-- 3. 生资保单理赔表
CREATE TABLE IF NOT EXISTS livestock_claims (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '理赔ID',
    claim_no VARCHAR(50) NOT NULL UNIQUE COMMENT '理赔编号',
    policy_id INT NOT NULL COMMENT '关联的生资保单ID',
    
    claim_type ENUM('death', 'disease', 'accident', 'natural_disaster') NOT NULL COMMENT '理赔类型',
    affected_count INT NOT NULL DEFAULT 1 COMMENT '受损数量',
    claim_amount DECIMAL(15,2) NOT NULL COMMENT '理赔金额',
    
    incident_date DATE NOT NULL COMMENT '事故发生日期',
    report_date DATE NOT NULL COMMENT '报案日期',
    incident_description TEXT NOT NULL COMMENT '事故描述',
    incident_location VARCHAR(500) NOT NULL COMMENT '事故地点',
    
    claim_status ENUM('pending', 'investigating', 'approved', 'rejected', 'paid') NOT NULL DEFAULT 'pending' COMMENT '理赔状态',
    
    investigator_id INT NULL COMMENT '调查员ID',
    investigation_report TEXT NULL COMMENT '调查报告',
    investigation_date DATE NULL COMMENT '调查日期',
    
    reviewer_id INT NULL COMMENT '审核人ID',
    review_notes TEXT NULL COMMENT '审核备注',
    review_date DATE NULL COMMENT '审核日期',
    
    payment_date DATE NULL COMMENT '支付日期',
    payment_amount DECIMAL(15,2) 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_livestock_claim_no (claim_no),
    INDEX idx_livestock_claim_policy (policy_id),
    INDEX idx_livestock_claim_status (claim_status),
    INDEX idx_livestock_claim_type (claim_type),
    INDEX idx_livestock_claim_date (incident_date),
    INDEX idx_livestock_claim_investigator (investigator_id),
    INDEX idx_livestock_claim_reviewer (reviewer_id),
    
    FOREIGN KEY (policy_id) REFERENCES livestock_policies(id) ON DELETE RESTRICT,
    FOREIGN KEY (investigator_id) REFERENCES users(id) ON DELETE SET NULL,
    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='生资保单理赔表';

-- 插入默认牲畜类型数据
INSERT INTO livestock_types (name, code, description, unit_price_min, unit_price_max, premium_rate, status) VALUES 
('牛', 'CATTLE', '肉牛、奶牛等牛类牲畜', 5000.00, 30000.00, 0.0050, 'active'),
('羊', 'SHEEP', '绵羊、山羊等羊类牲畜', 500.00, 3000.00, 0.0060, 'active'),
('猪', 'PIG', '生猪、种猪等猪类牲畜', 1000.00, 5000.00, 0.0040, 'active'),
('鸡', 'CHICKEN', '肉鸡、蛋鸡等鸡类家禽', 20.00, 100.00, 0.0080, 'active'),
('鸭', 'DUCK', '肉鸭、蛋鸭等鸭类家禽', 30.00, 150.00, 0.0070, 'active');