-- Insurance System Database Schema with insurance_ prefix
-- Created: 2025-01-01
-- Database: MySQL 5.7+

USE nxxmdata;

-- 1. Insurance roles table
CREATE TABLE IF NOT EXISTS insurance_roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    description VARCHAR(255) NULL,
    permissions JSON NOT NULL,
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_roles_name (name),
    INDEX idx_roles_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Insurance users table
CREATE TABLE IF NOT EXISTS insurance_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    real_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone VARCHAR(20) NOT NULL,
    role_id INT NOT NULL,
    status ENUM('active', 'inactive', 'suspended') NOT NULL DEFAULT 'active',
    last_login TIMESTAMP NULL,
    avatar VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    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;

-- 3. Insurance types table
CREATE TABLE IF NOT EXISTS insurance_types (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT NULL,
    coverage_amount_min DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    coverage_amount_max DECIMAL(15,2) NOT NULL DEFAULT 1000000.00,
    premium_rate DECIMAL(5,4) NOT NULL DEFAULT 0.001,
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    created_by INT NULL,
    updated_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    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;

-- 4. Insurance applications table
CREATE TABLE IF NOT EXISTS insurance_applications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    application_no VARCHAR(50) NOT NULL UNIQUE,
    customer_name VARCHAR(100) NOT NULL,
    customer_id_card VARCHAR(18) NOT NULL,
    customer_phone VARCHAR(20) NOT NULL,
    customer_address VARCHAR(255) NOT NULL,
    insurance_type_id INT NOT NULL,
    application_amount DECIMAL(15,2) NOT NULL,
    status ENUM('pending', 'approved', 'rejected', 'under_review') NOT NULL DEFAULT 'pending',
    application_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    review_notes TEXT NULL,
    reviewer_id INT NULL,
    review_date TIMESTAMP NULL,
    documents JSON NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    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;

-- 5. Insurance policies table
CREATE TABLE IF NOT EXISTS insurance_policies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    policy_no VARCHAR(50) NOT NULL UNIQUE,
    application_id INT NOT NULL,
    insurance_type_id INT NOT NULL,
    customer_id INT NOT NULL,
    coverage_amount DECIMAL(15,2) NOT NULL,
    premium_amount DECIMAL(15,2) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    policy_status ENUM('active', 'expired', 'cancelled', 'suspended') NOT NULL DEFAULT 'active',
    payment_status ENUM('paid', 'unpaid', 'partial') NOT NULL DEFAULT 'unpaid',
    payment_date DATE NULL,
    policy_document_url VARCHAR(500) NULL,
    terms_and_conditions TEXT NULL,
    created_by INT NULL,
    updated_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    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;

-- 6. Insurance claims table
CREATE TABLE IF NOT EXISTS insurance_claims (
    id INT AUTO_INCREMENT PRIMARY KEY,
    claim_no VARCHAR(50) NOT NULL UNIQUE,
    policy_id INT NOT NULL,
    customer_id INT NOT NULL,
    claim_amount DECIMAL(15,2) NOT NULL,
    claim_date DATE NOT NULL,
    incident_description TEXT NOT NULL,
    claim_status ENUM('pending', 'approved', 'rejected', 'processing', 'paid') NOT NULL DEFAULT 'pending',
    review_notes TEXT NULL,
    reviewer_id INT NULL,
    review_date DATE NULL,
    payment_date DATE NULL,
    supporting_documents JSON NULL,
    created_by INT NULL,
    updated_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    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;

-- 7. Insurance system configs table
CREATE TABLE IF NOT EXISTS insurance_system_configs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    config_key VARCHAR(100) NOT NULL UNIQUE,
    config_value JSON NOT NULL,
    description VARCHAR(255) NULL,
    created_by INT NULL,
    updated_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    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;

-- 8. Insurance system logs table
CREATE TABLE IF NOT EXISTS insurance_system_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    level ENUM('info', 'warning', 'error', 'debug') NOT NULL DEFAULT 'info',
    message TEXT NOT NULL,
    module VARCHAR(100) NOT NULL,
    user_id INT NULL,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    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;

-- Show all insurance tables
SHOW TABLES LIKE 'insurance_%';