-- ============================================================================
-- Kenyan Pension & Life Insurance Management System
-- MySQL 8.0 Database Schema
-- 
-- This system implements rule-based withdrawal calculations per scheme,
-- compliant with Kenyan RBA regulations and scheme trust deeds.
-- ============================================================================

-- Drop database if exists (for fresh installation)
-- DROP DATABASE IF EXISTS kenyan_pension_system;
-- CREATE DATABASE kenyan_pension_system CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- USE kenyan_pension_system;

-- ============================================================================
-- CORE REFERENCE TABLES
-- ============================================================================

-- Pension Schemes Table
-- Stores scheme-specific withdrawal rules and configurations
CREATE TABLE IF NOT EXISTS pension_schemes (
    scheme_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    scheme_code VARCHAR(50) NOT NULL UNIQUE COMMENT 'Unique scheme identifier',
    scheme_name VARCHAR(255) NOT NULL,
    scheme_type ENUM('OCCUPATIONAL', 'UMBRELLA', 'INDIVIDUAL_PENSION_PLAN') NOT NULL,
    
    -- Retirement Age Configuration
    retirement_age INT UNSIGNED NOT NULL DEFAULT 60 COMMENT 'Standard retirement age for this scheme',
    
    -- Early Exit Rules (Before Retirement Age)
    early_exit_max_cash_percentage DECIMAL(5,2) NOT NULL DEFAULT 50.00 
        COMMENT 'Maximum % of accrued benefits payable as cash for early exit (Occupational/Umbrella: 50%, IPP: up to 100%)',
    early_exit_balance_action ENUM('DEFER', 'TRANSFER', 'ANNUITY') NOT NULL DEFAULT 'DEFER'
        COMMENT 'What happens to remaining balance after early exit cash withdrawal',
    
    -- Retirement Withdrawal Rules
    retirement_commutation_percentage DECIMAL(5,2) DEFAULT NULL
        COMMENT 'Maximum % of accrued benefits that can be taken as lump sum at retirement (NULL = scheme-defined)',
    retirement_balance_action ENUM('ANNUITY', 'PROGRAMMED_WITHDRAWAL', 'DEFER') NOT NULL DEFAULT 'ANNUITY'
        COMMENT 'What happens to remaining balance after retirement commutation',
    
    -- Special Withdrawal Conditions
    disability_full_withdrawal_allowed BOOLEAN NOT NULL DEFAULT TRUE
        COMMENT 'Whether permanent disability allows 100% withdrawal',
    emigration_full_withdrawal_allowed BOOLEAN NOT NULL DEFAULT TRUE
        COMMENT 'Whether permanent emigration allows 100% withdrawal',
    
    -- Investment Return Allocation
    investment_return_allocation_frequency ENUM('MONTHLY', 'QUARTERLY', 'ANNUALLY') NOT NULL DEFAULT 'QUARTERLY'
        COMMENT 'How often investment returns are allocated to member accounts',
    
    -- Status and Metadata
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    trust_deed_reference VARCHAR(255) DEFAULT NULL,
    rba_registration_number VARCHAR(100) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_scheme_type (scheme_type),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Pension schemes with configurable withdrawal rules per Kenyan RBA regulations';

-- Members Table
CREATE TABLE IF NOT EXISTS members (
    member_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    scheme_id INT UNSIGNED NOT NULL,
    employer_id INT UNSIGNED DEFAULT NULL COMMENT 'Employer for occupational/umbrella schemes',
    member_number VARCHAR(50) NOT NULL,
    national_id VARCHAR(20) DEFAULT NULL COMMENT 'Kenyan National ID',
    first_name VARCHAR(100) NOT NULL,
    middle_name VARCHAR(100) DEFAULT NULL,
    last_name VARCHAR(100) NOT NULL,
    date_of_birth DATE NOT NULL,
    gender ENUM('MALE', 'FEMALE', 'OTHER') DEFAULT NULL,
    email VARCHAR(255) DEFAULT NULL,
    phone_number VARCHAR(20) DEFAULT NULL,
    
    -- Employment Details
    employment_status ENUM('ACTIVE', 'TERMINATED', 'RETIRED', 'DISABLED', 'EMIGRATED') NOT NULL DEFAULT 'ACTIVE',
    date_of_employment DATE DEFAULT NULL,
    date_of_termination DATE DEFAULT NULL,
    
    -- Special Status Flags
    is_permanently_disabled BOOLEAN NOT NULL DEFAULT FALSE,
    is_emigrated BOOLEAN NOT NULL DEFAULT FALSE,
    disability_certificate_date DATE DEFAULT NULL,
    emigration_date DATE DEFAULT NULL,
    
    -- Status
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (scheme_id) REFERENCES pension_schemes(scheme_id) ON DELETE RESTRICT,
    FOREIGN KEY (employer_id) REFERENCES employers(employer_id) ON DELETE SET NULL,
    UNIQUE KEY uk_member_scheme (scheme_id, member_number),
    INDEX idx_national_id (national_id),
    INDEX idx_employment_status (employment_status),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Member information and status';

-- Member Accounts Table
-- Tracks account balances and accrued benefits
CREATE TABLE IF NOT EXISTS member_accounts (
    account_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    member_id INT UNSIGNED NOT NULL,
    
    -- Balance Components (as per Kenyan pension structure)
    total_employee_contributions DECIMAL(15,2) NOT NULL DEFAULT 0.00
        COMMENT 'Cumulative employee contributions',
    total_employer_contributions DECIMAL(15,2) NOT NULL DEFAULT 0.00
        COMMENT 'Cumulative employer contributions',
    allocated_investment_returns DECIMAL(15,2) NOT NULL DEFAULT 0.00
        COMMENT 'Investment returns allocated to this member',
    
    -- Computed Accrued Benefits (recalculated periodically)
    accrued_benefits DECIMAL(15,2) NOT NULL DEFAULT 0.00
        COMMENT 'Total accrued benefits = employee + employer + investment returns',
    
    -- Withdrawal Tracking
    total_withdrawn DECIMAL(15,2) NOT NULL DEFAULT 0.00
        COMMENT 'Total amount withdrawn to date',
    available_balance DECIMAL(15,2) NOT NULL DEFAULT 0.00
        COMMENT 'Available balance = accrued_benefits - total_withdrawn',
    
    -- Account Status
    account_status ENUM('ACTIVE', 'FROZEN', 'CLOSED', 'DEFERRED') NOT NULL DEFAULT 'ACTIVE',
    last_calculation_date DATE DEFAULT NULL
        COMMENT 'Date when accrued benefits were last calculated',
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (member_id) REFERENCES members(member_id) ON DELETE RESTRICT,
    UNIQUE KEY uk_member_account (member_id),
    INDEX idx_account_status (account_status),
    INDEX idx_last_calculation (last_calculation_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Member account balances and accrued benefits';

-- Contributions Table
-- Records all contribution transactions
CREATE TABLE IF NOT EXISTS contributions (
    contribution_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    member_id INT UNSIGNED NOT NULL,
    account_id INT UNSIGNED NOT NULL,
    
    contribution_type ENUM('EMPLOYEE', 'EMPLOYER', 'VOLUNTARY') NOT NULL,
    contribution_period DATE NOT NULL COMMENT 'Period this contribution covers (typically month-end)',
    contribution_amount DECIMAL(15,2) NOT NULL,
    
    -- Contribution Details
    employee_contribution DECIMAL(15,2) DEFAULT NULL,
    employer_contribution DECIMAL(15,2) DEFAULT NULL,
    
    -- Processing Status
    status ENUM('PENDING', 'PROCESSED', 'REVERSED') NOT NULL DEFAULT 'PENDING',
    processed_at TIMESTAMP NULL DEFAULT NULL,
    
    -- Metadata
    reference_number VARCHAR(100) DEFAULT NULL,
    payment_method VARCHAR(50) DEFAULT NULL,
    notes TEXT DEFAULT NULL,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (member_id) REFERENCES members(member_id) ON DELETE RESTRICT,
    FOREIGN KEY (account_id) REFERENCES member_accounts(account_id) ON DELETE RESTRICT,
    INDEX idx_member_period (member_id, contribution_period),
    INDEX idx_status (status),
    INDEX idx_contribution_type (contribution_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Member contribution records';

-- Fund Returns Table
-- Records investment returns allocated to members
CREATE TABLE IF NOT EXISTS fund_returns (
    return_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    scheme_id INT UNSIGNED NOT NULL,
    
    return_period_start DATE NOT NULL,
    return_period_end DATE NOT NULL,
    return_percentage DECIMAL(8,4) NOT NULL COMMENT 'Investment return percentage for this period',
    return_amount DECIMAL(15,2) NOT NULL COMMENT 'Total return amount allocated',
    
    allocation_frequency ENUM('MONTHLY', 'QUARTERLY', 'ANNUALLY') NOT NULL,
    allocation_date DATE NOT NULL COMMENT 'Date when returns were allocated',
    
    -- Status
    status ENUM('PENDING', 'ALLOCATED', 'REVERSED') NOT NULL DEFAULT 'PENDING',
    allocated_at TIMESTAMP NULL DEFAULT NULL,
    
    -- Metadata
    fund_performance_notes TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (scheme_id) REFERENCES pension_schemes(scheme_id) ON DELETE RESTRICT,
    INDEX idx_scheme_period (scheme_id, return_period_start, return_period_end),
    INDEX idx_allocation_date (allocation_date),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Investment returns for schemes, allocated to members periodically';

-- Member Fund Returns Allocation Table
-- Tracks how fund returns are allocated to individual members
CREATE TABLE IF NOT EXISTS member_fund_returns (
    allocation_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    return_id INT UNSIGNED NOT NULL,
    member_id INT UNSIGNED NOT NULL,
    account_id INT UNSIGNED NOT NULL,
    
    allocation_amount DECIMAL(15,2) NOT NULL COMMENT 'Amount allocated to this member',
    allocation_percentage DECIMAL(8,4) DEFAULT NULL COMMENT 'Percentage of member balance that generated this return',
    
    allocated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (return_id) REFERENCES fund_returns(return_id) ON DELETE RESTRICT,
    FOREIGN KEY (member_id) REFERENCES members(member_id) ON DELETE RESTRICT,
    FOREIGN KEY (account_id) REFERENCES member_accounts(account_id) ON DELETE RESTRICT,
    INDEX idx_member_return (member_id, return_id),
    INDEX idx_account (account_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Individual member allocations of fund returns';

-- Withdrawal Requests Table
CREATE TABLE IF NOT EXISTS withdrawal_requests (
    request_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    member_id INT UNSIGNED NOT NULL,
    account_id INT UNSIGNED NOT NULL,
    scheme_id INT UNSIGNED NOT NULL,
    
    -- Request Details
    withdrawal_type ENUM('EARLY_EXIT', 'RETIREMENT', 'DISABILITY', 'EMIGRATION', 'PARTIAL') NOT NULL,
    requested_amount DECIMAL(15,2) NOT NULL,
    requested_date DATE NOT NULL,
    
    -- Eligibility and Calculation
    is_eligible BOOLEAN DEFAULT NULL COMMENT 'Eligibility validation result',
    eligible_amount DECIMAL(15,2) DEFAULT NULL COMMENT 'Maximum eligible withdrawal amount',
    calculated_amount DECIMAL(15,2) DEFAULT NULL COMMENT 'Final calculated withdrawal amount',
    
    -- Status
    status ENUM('PENDING', 'APPROVED', 'REJECTED', 'PROCESSED', 'CANCELLED') NOT NULL DEFAULT 'PENDING',
    
    -- Approval Workflow
    approved_by INT UNSIGNED DEFAULT NULL COMMENT 'User ID who approved',
    approved_at TIMESTAMP NULL DEFAULT NULL,
    rejection_reason TEXT DEFAULT NULL,
    
    -- Processing
    processed_at TIMESTAMP NULL DEFAULT NULL,
    payment_reference VARCHAR(100) DEFAULT NULL,
    
    -- Metadata
    request_notes TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (member_id) REFERENCES members(member_id) ON DELETE RESTRICT,
    FOREIGN KEY (account_id) REFERENCES member_accounts(account_id) ON DELETE RESTRICT,
    FOREIGN KEY (scheme_id) REFERENCES pension_schemes(scheme_id) ON DELETE RESTRICT,
    INDEX idx_member_status (member_id, status),
    INDEX idx_withdrawal_type (withdrawal_type),
    INDEX idx_status (status),
    INDEX idx_requested_date (requested_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Withdrawal requests and their processing status';

-- Payout Calculations Table
-- Detailed breakdown of withdrawal calculations for audit trail
CREATE TABLE IF NOT EXISTS payout_calculations (
    calculation_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    request_id INT UNSIGNED NOT NULL,
    member_id INT UNSIGNED NOT NULL,
    account_id INT UNSIGNED NOT NULL,
    
    -- Calculation Inputs
    calculation_date DATE NOT NULL,
    accrued_benefits_at_calculation DECIMAL(15,2) NOT NULL,
    total_withdrawn_before DECIMAL(15,2) NOT NULL,
    available_balance_before DECIMAL(15,2) NOT NULL,
    
    -- Calculation Components
    employee_contributions_component DECIMAL(15,2) NOT NULL,
    employer_contributions_component DECIMAL(15,2) NOT NULL,
    investment_returns_component DECIMAL(15,2) NOT NULL,
    
    -- Withdrawal Rules Applied
    withdrawal_percentage_applied DECIMAL(5,2) DEFAULT NULL COMMENT 'Percentage rule applied (e.g., 50% for early exit)',
    withdrawal_type_applied VARCHAR(50) NOT NULL,
    
    -- Calculation Results
    calculated_withdrawal_amount DECIMAL(15,2) NOT NULL,
    remaining_balance DECIMAL(15,2) NOT NULL,
    balance_action ENUM('DEFER', 'TRANSFER', 'ANNUITY', 'PROGRAMMED_WITHDRAWAL') DEFAULT NULL,
    
    -- Calculation Metadata
    calculation_method VARCHAR(100) DEFAULT NULL COMMENT 'Method/rule used for calculation',
    calculation_notes TEXT DEFAULT NULL,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (request_id) REFERENCES withdrawal_requests(request_id) ON DELETE RESTRICT,
    FOREIGN KEY (member_id) REFERENCES members(member_id) ON DELETE RESTRICT,
    FOREIGN KEY (account_id) REFERENCES member_accounts(account_id) ON DELETE RESTRICT,
    INDEX idx_request (request_id),
    INDEX idx_member (member_id),
    INDEX idx_calculation_date (calculation_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Detailed audit trail of withdrawal calculations';

-- Audit Logs Table
-- Comprehensive audit trail for all financial transactions and calculations
CREATE TABLE IF NOT EXISTS audit_logs (
    log_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    
    -- Event Details
    event_type VARCHAR(100) NOT NULL COMMENT 'Type of event: CONTRIBUTION, WITHDRAWAL, CALCULATION, etc.',
    event_category ENUM('FINANCIAL', 'CALCULATION', 'ACCOUNT_UPDATE', 'WITHDRAWAL', 'CONTRIBUTION', 'LIFE_INSURANCE', 'CLAIM', 'SURRENDER', 'PREMIUM', 'SYSTEM') NOT NULL,
    
    -- Entity References
    member_id INT UNSIGNED DEFAULT NULL,
    account_id INT UNSIGNED DEFAULT NULL,
    scheme_id INT UNSIGNED DEFAULT NULL,
    request_id INT UNSIGNED DEFAULT NULL,
    calculation_id INT UNSIGNED DEFAULT NULL,
    policy_id INT UNSIGNED DEFAULT NULL COMMENT 'Life insurance policy reference',
    claim_id INT UNSIGNED DEFAULT NULL COMMENT 'Life insurance claim reference',
    surrender_id INT UNSIGNED DEFAULT NULL COMMENT 'Surrender request reference',
    payout_id INT UNSIGNED DEFAULT NULL COMMENT 'Payout reference',
    
    -- Event Data
    event_description TEXT NOT NULL,
    old_value JSON DEFAULT NULL COMMENT 'Previous state (JSON format)',
    new_value JSON DEFAULT NULL COMMENT 'New state (JSON format)',
    amount DECIMAL(15,2) DEFAULT NULL COMMENT 'Financial amount if applicable',
    
    -- User/Actor Information
    performed_by VARCHAR(100) DEFAULT NULL COMMENT 'User/system that performed the action',
    ip_address VARCHAR(45) DEFAULT NULL,
    
    -- Timestamp
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_event_type (event_type),
    INDEX idx_event_category (event_category),
    INDEX idx_member (member_id),
    INDEX idx_account (account_id),
    INDEX idx_created_at (created_at),
    INDEX idx_scheme (scheme_id),
    INDEX idx_policy (policy_id),
    INDEX idx_claim (claim_id),
    INDEX idx_surrender (surrender_id),
    INDEX idx_payout (payout_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Comprehensive audit log for all system events';

-- ============================================================================
-- RBAC (ROLE-BASED ACCESS CONTROL) TABLES
-- ============================================================================

-- Roles Table
CREATE TABLE IF NOT EXISTS roles (
    role_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role_name VARCHAR(100) NOT NULL UNIQUE,
    role_description TEXT DEFAULT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_role_name (role_name),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='System roles: Admin, Trustee, Employer, Member, Insurance Officer, Agent';

-- Permissions Table
CREATE TABLE IF NOT EXISTS permissions (
    permission_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    permission_name VARCHAR(100) NOT NULL UNIQUE,
    permission_description TEXT DEFAULT NULL,
    module VARCHAR(50) DEFAULT NULL COMMENT 'Module this permission belongs to',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_permission_name (permission_name),
    INDEX idx_module (module)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='System permissions for RBAC';

-- Role Permissions Table
CREATE TABLE IF NOT EXISTS role_permissions (
    role_permission_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role_id INT UNSIGNED NOT NULL,
    permission_id INT UNSIGNED NOT NULL,
    granted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (role_id) REFERENCES roles(role_id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(permission_id) ON DELETE CASCADE,
    UNIQUE KEY uk_role_permission (role_id, permission_id),
    INDEX idx_role (role_id),
    INDEX idx_permission (permission_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Many-to-many relationship between roles and permissions';

-- Users Table
CREATE TABLE IF NOT EXISTS users (
    user_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role_id INT UNSIGNED NOT NULL,
    username VARCHAR(100) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL COMMENT 'Bcrypt hashed password',
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    phone_number VARCHAR(20) DEFAULT NULL,
    
    -- Authentication
    two_factor_enabled BOOLEAN NOT NULL DEFAULT FALSE,
    two_factor_secret VARCHAR(255) DEFAULT NULL,
    
    -- Status
    status ENUM('ACTIVE', 'INACTIVE', 'SUSPENDED', 'LOCKED') NOT NULL DEFAULT 'ACTIVE',
    last_login TIMESTAMP NULL DEFAULT NULL,
    failed_login_attempts INT UNSIGNED NOT NULL DEFAULT 0,
    locked_until TIMESTAMP NULL DEFAULT NULL,
    
    -- Soft Delete
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (role_id) REFERENCES roles(role_id) ON DELETE RESTRICT,
    INDEX idx_username (username),
    INDEX idx_email (email),
    INDEX idx_status (status),
    INDEX idx_role (role_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='System users with RBAC';

-- ============================================================================
-- EMPLOYERS & TRUSTEES TABLES
-- ============================================================================

-- Employers Table
CREATE TABLE IF NOT EXISTS employers (
    employer_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    employer_code VARCHAR(50) NOT NULL UNIQUE,
    employer_name VARCHAR(255) NOT NULL,
    registration_number VARCHAR(100) DEFAULT NULL COMMENT 'Business registration number',
    tax_pin VARCHAR(20) DEFAULT NULL COMMENT 'KRA PIN',
    contact_person VARCHAR(255) DEFAULT NULL,
    email VARCHAR(255) DEFAULT NULL,
    phone_number VARCHAR(20) DEFAULT NULL,
    address TEXT DEFAULT NULL,
    
    -- Scheme Association
    scheme_id INT UNSIGNED DEFAULT NULL COMMENT 'Primary scheme for this employer',
    
    -- Status
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (scheme_id) REFERENCES pension_schemes(scheme_id) ON DELETE SET NULL,
    INDEX idx_employer_code (employer_code),
    INDEX idx_scheme (scheme_id),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Employers participating in pension schemes';

-- Trustees Table
CREATE TABLE IF NOT EXISTS trustees (
    trustee_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    trustee_code VARCHAR(50) NOT NULL UNIQUE,
    trustee_name VARCHAR(255) NOT NULL,
    trustee_type ENUM('INDIVIDUAL', 'CORPORATE') NOT NULL,
    national_id VARCHAR(20) DEFAULT NULL COMMENT 'For individual trustees',
    registration_number VARCHAR(100) DEFAULT NULL COMMENT 'For corporate trustees',
    email VARCHAR(255) DEFAULT NULL,
    phone_number VARCHAR(20) DEFAULT NULL,
    address TEXT DEFAULT NULL,
    
    -- Status
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_trustee_code (trustee_code),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Trustees managing pension schemes';

-- Scheme Trustees Table (Many-to-Many)
CREATE TABLE IF NOT EXISTS scheme_trustees (
    scheme_trustee_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    scheme_id INT UNSIGNED NOT NULL,
    trustee_id INT UNSIGNED NOT NULL,
    role ENUM('CHAIRPERSON', 'TRUSTEE', 'SECRETARY') DEFAULT 'TRUSTEE',
    appointment_date DATE NOT NULL,
    termination_date DATE DEFAULT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    
    FOREIGN KEY (scheme_id) REFERENCES pension_schemes(scheme_id) ON DELETE CASCADE,
    FOREIGN KEY (trustee_id) REFERENCES trustees(trustee_id) ON DELETE RESTRICT,
    INDEX idx_scheme (scheme_id),
    INDEX idx_trustee (trustee_id),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Association between schemes and trustees';

-- ============================================================================
-- BENEFICIARIES & NOMINEES TABLES
-- ============================================================================

-- Beneficiaries Table
CREATE TABLE IF NOT EXISTS beneficiaries (
    beneficiary_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    member_id INT UNSIGNED NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    relationship ENUM('SPOUSE', 'CHILD', 'PARENT', 'SIBLING', 'OTHER') NOT NULL,
    date_of_birth DATE DEFAULT NULL,
    national_id VARCHAR(20) DEFAULT NULL,
    phone_number VARCHAR(20) DEFAULT NULL,
    email VARCHAR(255) DEFAULT NULL,
    address TEXT DEFAULT NULL,
    
    -- Allocation Percentage
    allocation_percentage DECIMAL(5,2) NOT NULL DEFAULT 100.00
        COMMENT 'Percentage of benefits allocated to this beneficiary',
    
    -- Status
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (member_id) REFERENCES members(member_id) ON DELETE CASCADE,
    INDEX idx_member (member_id),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Beneficiaries for pension and life insurance payouts';

-- ============================================================================
-- LIFE INSURANCE TABLES
-- ============================================================================

-- Life Insurance Products Table
CREATE TABLE IF NOT EXISTS life_insurance_products (
    product_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_code VARCHAR(50) NOT NULL UNIQUE,
    product_name VARCHAR(255) NOT NULL,
    product_type ENUM('TERM_LIFE', 'WHOLE_LIFE', 'ENDOWMENT', 'GROUP_LIFE', 'CREDIT_LIFE') NOT NULL,
    description TEXT DEFAULT NULL,
    
    -- Surrender Rules
    minimum_surrender_years INT UNSIGNED NOT NULL DEFAULT 3
        COMMENT 'Minimum policy years before surrender is allowed',
    surrender_value_calculation_method VARCHAR(100) DEFAULT NULL
        COMMENT 'Method for calculating surrender value',
    
    -- Bonus Rules
    bonus_allocation_enabled BOOLEAN NOT NULL DEFAULT FALSE,
    bonus_type ENUM('REVERSIONARY', 'TERMINAL', 'BOTH', 'NONE') DEFAULT 'NONE',
    
    -- Status
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_product_code (product_code),
    INDEX idx_product_type (product_type),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Life insurance product definitions';

-- Policy Rules Table (Surrender Factors per Policy Year)
CREATE TABLE IF NOT EXISTS policy_rules (
    rule_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id INT UNSIGNED NOT NULL,
    policy_year INT UNSIGNED NOT NULL COMMENT 'Policy year (1, 2, 3, etc.)',
    surrender_factor DECIMAL(5,2) NOT NULL COMMENT 'Percentage factor for surrender value calculation',
    bonus_allocation_percentage DECIMAL(5,2) DEFAULT NULL COMMENT 'Bonus allocation % for this year',
    
    -- Rule Metadata
    effective_date DATE NOT NULL,
    expiry_date DATE DEFAULT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    
    FOREIGN KEY (product_id) REFERENCES life_insurance_products(product_id) ON DELETE CASCADE,
    UNIQUE KEY uk_product_year (product_id, policy_year),
    INDEX idx_product (product_id),
    INDEX idx_policy_year (policy_year)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Policy rules including surrender factors per policy year';

-- Life Policies Table
CREATE TABLE IF NOT EXISTS life_policies (
    policy_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    policy_number VARCHAR(100) NOT NULL UNIQUE,
    product_id INT UNSIGNED NOT NULL,
    member_id INT UNSIGNED DEFAULT NULL COMMENT 'Policyholder (can be member or external)',
    
    -- Policy Details
    sum_assured DECIMAL(15,2) NOT NULL,
    premium_amount DECIMAL(15,2) NOT NULL,
    premium_frequency ENUM('MONTHLY', 'QUARTERLY', 'SEMI_ANNUAL', 'ANNUAL') NOT NULL DEFAULT 'ANNUAL',
    
    -- Policy Dates
    policy_start_date DATE NOT NULL,
    policy_end_date DATE DEFAULT NULL COMMENT 'For term policies',
    maturity_date DATE DEFAULT NULL,
    
    -- Policy Status
    policy_status ENUM('ACTIVE', 'LAPSED', 'SURRENDERED', 'MATURED', 'DEATH_CLAIM', 'CANCELLED') NOT NULL DEFAULT 'ACTIVE',
    lapse_date DATE DEFAULT NULL,
    surrender_date DATE DEFAULT NULL,
    maturity_processed_date DATE DEFAULT NULL,
    
    -- Financial Tracking
    total_premiums_paid DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    total_bonuses_allocated DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    surrender_value DECIMAL(15,2) DEFAULT NULL COMMENT 'Calculated surrender value',
    current_policy_value DECIMAL(15,2) DEFAULT NULL COMMENT 'Sum assured + bonuses',
    
    -- Credit Life Specific
    linked_loan_id VARCHAR(100) DEFAULT NULL COMMENT 'For credit life policies',
    outstanding_loan_balance DECIMAL(15,2) DEFAULT NULL COMMENT 'For credit life policies',
    
    -- Metadata
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (product_id) REFERENCES life_insurance_products(product_id) ON DELETE RESTRICT,
    FOREIGN KEY (member_id) REFERENCES members(member_id) ON DELETE SET NULL,
    INDEX idx_policy_number (policy_number),
    INDEX idx_product (product_id),
    INDEX idx_member (member_id),
    INDEX idx_policy_status (policy_status),
    INDEX idx_policy_dates (policy_start_date, policy_end_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Life insurance policies';

-- Premiums Table (Life Insurance)
CREATE TABLE IF NOT EXISTS premiums (
    premium_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    policy_id INT UNSIGNED NOT NULL,
    premium_due_date DATE NOT NULL,
    premium_amount DECIMAL(15,2) NOT NULL,
    
    -- Payment Status
    payment_status ENUM('PENDING', 'PAID', 'OVERDUE', 'WAIVED', 'LAPSED') NOT NULL DEFAULT 'PENDING',
    paid_date DATE DEFAULT NULL,
    paid_amount DECIMAL(15,2) DEFAULT NULL,
    
    -- Payment Details
    payment_method VARCHAR(50) DEFAULT NULL COMMENT 'M-Pesa, Bank, etc.',
    payment_reference VARCHAR(100) DEFAULT NULL,
    
    -- Late Payment
    is_late BOOLEAN NOT NULL DEFAULT FALSE,
    late_payment_penalty DECIMAL(15,2) DEFAULT NULL,
    days_overdue INT DEFAULT NULL,
    
    -- Metadata
    notes TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (policy_id) REFERENCES life_policies(policy_id) ON DELETE RESTRICT,
    INDEX idx_policy_due_date (policy_id, premium_due_date),
    INDEX idx_payment_status (payment_status),
    INDEX idx_premium_due_date (premium_due_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Life insurance premium payments';

-- Bonuses Table (Life Insurance)
CREATE TABLE IF NOT EXISTS bonuses (
    bonus_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    policy_id INT UNSIGNED NOT NULL,
    bonus_type ENUM('REVERSIONARY', 'TERMINAL') NOT NULL,
    bonus_amount DECIMAL(15,2) NOT NULL,
    bonus_percentage DECIMAL(5,2) DEFAULT NULL COMMENT 'Percentage of sum assured or premium',
    
    -- Allocation Details
    allocation_date DATE NOT NULL,
    policy_year INT UNSIGNED DEFAULT NULL COMMENT 'Policy year when bonus was allocated',
    
    -- Status
    is_allocated BOOLEAN NOT NULL DEFAULT TRUE,
    allocated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Metadata
    notes TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (policy_id) REFERENCES life_policies(policy_id) ON DELETE RESTRICT,
    INDEX idx_policy (policy_id),
    INDEX idx_bonus_type (bonus_type),
    INDEX idx_allocation_date (allocation_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Life insurance bonus allocations';

-- Life Claims Table
CREATE TABLE IF NOT EXISTS life_claims (
    claim_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    policy_id INT UNSIGNED NOT NULL,
    claim_type ENUM('DEATH', 'MATURITY', 'DISABILITY', 'ACCIDENTAL') NOT NULL,
    
    -- Claim Details
    claim_amount DECIMAL(15,2) NOT NULL COMMENT 'Calculated claim amount',
    sum_assured_component DECIMAL(15,2) NOT NULL,
    bonus_component DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    
    -- Claim Status
    claim_status ENUM('PENDING', 'UNDER_REVIEW', 'APPROVED', 'REJECTED', 'PAID') NOT NULL DEFAULT 'PENDING',
    submitted_date DATE NOT NULL,
    approved_date DATE DEFAULT NULL,
    paid_date DATE DEFAULT NULL,
    
    -- Death Claim Specific
    date_of_death DATE DEFAULT NULL,
    death_certificate_number VARCHAR(100) DEFAULT NULL,
    cause_of_death TEXT DEFAULT NULL,
    
    -- Maturity Claim Specific
    maturity_date DATE DEFAULT NULL,
    
    -- Approval Workflow
    reviewed_by INT UNSIGNED DEFAULT NULL COMMENT 'User ID who reviewed',
    approved_by INT UNSIGNED DEFAULT NULL COMMENT 'User ID who approved',
    rejection_reason TEXT DEFAULT NULL,
    
    -- Payment
    payment_reference VARCHAR(100) DEFAULT NULL,
    payment_method VARCHAR(50) DEFAULT NULL,
    
    -- Metadata
    claim_notes TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (policy_id) REFERENCES life_policies(policy_id) ON DELETE RESTRICT,
    INDEX idx_policy (policy_id),
    INDEX idx_claim_type (claim_type),
    INDEX idx_claim_status (claim_status),
    INDEX idx_submitted_date (submitted_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Life insurance claims (death, maturity, disability)';

-- Surrender Requests Table
CREATE TABLE IF NOT EXISTS surrender_requests (
    surrender_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    policy_id INT UNSIGNED NOT NULL,
    
    -- Request Details
    requested_date DATE NOT NULL,
    requested_amount DECIMAL(15,2) DEFAULT NULL COMMENT 'If partial surrender',
    policy_year_at_surrender INT UNSIGNED NOT NULL COMMENT 'Policy year when surrender requested',
    
    -- Calculation
    surrender_factor_applied DECIMAL(5,2) DEFAULT NULL,
    total_premiums_paid DECIMAL(15,2) NOT NULL,
    bonuses_included DECIMAL(15,2) DEFAULT 0.00,
    calculated_surrender_value DECIMAL(15,2) NOT NULL,
    
    -- Eligibility
    is_eligible BOOLEAN DEFAULT NULL COMMENT 'Eligibility validation result',
    eligibility_reason TEXT DEFAULT NULL,
    
    -- Status
    status ENUM('PENDING', 'APPROVED', 'REJECTED', 'PROCESSED', 'CANCELLED') NOT NULL DEFAULT 'PENDING',
    
    -- Approval Workflow
    approved_by INT UNSIGNED DEFAULT NULL,
    approved_at TIMESTAMP NULL DEFAULT NULL,
    rejection_reason TEXT DEFAULT NULL,
    
    -- Processing
    processed_at TIMESTAMP NULL DEFAULT NULL,
    payment_reference VARCHAR(100) DEFAULT NULL,
    
    -- Metadata
    request_notes TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (policy_id) REFERENCES life_policies(policy_id) ON DELETE RESTRICT,
    INDEX idx_policy (policy_id),
    INDEX idx_status (status),
    INDEX idx_requested_date (requested_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Life insurance surrender requests';

-- ============================================================================
-- ENHANCED PAYOUT SYSTEM
-- ============================================================================

-- Payouts Table (Unified for Pension & Life Insurance)
CREATE TABLE IF NOT EXISTS payouts (
    payout_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    
    -- Payout Source
    payout_type ENUM('PENSION_WITHDRAWAL', 'LIFE_CLAIM', 'LIFE_SURRENDER', 'LIFE_MATURITY', 'TRANSFER') NOT NULL,
    source_request_id INT UNSIGNED DEFAULT NULL COMMENT 'Withdrawal request ID or claim ID',
    
    -- Beneficiary/Member
    member_id INT UNSIGNED DEFAULT NULL,
    beneficiary_id INT UNSIGNED DEFAULT NULL,
    policy_id INT UNSIGNED DEFAULT NULL,
    
    -- Payout Details
    payout_amount DECIMAL(15,2) NOT NULL,
    currency VARCHAR(3) NOT NULL DEFAULT 'KES',
    
    -- Payment Method
    payment_method ENUM('MPESA', 'BANK_TRANSFER', 'CHEQUE', 'CASH', 'OTHER') NOT NULL,
    payment_reference VARCHAR(100) DEFAULT NULL,
    bank_account_number VARCHAR(50) DEFAULT NULL,
    bank_name VARCHAR(100) DEFAULT NULL,
    mpesa_number VARCHAR(20) DEFAULT NULL,
    
    -- Status
    payout_status ENUM('PENDING', 'APPROVED', 'PROCESSING', 'COMPLETED', 'FAILED', 'CANCELLED') NOT NULL DEFAULT 'PENDING',
    
    -- Processing
    approved_by INT UNSIGNED DEFAULT NULL,
    approved_at TIMESTAMP NULL DEFAULT NULL,
    processed_at TIMESTAMP NULL DEFAULT NULL,
    completed_at TIMESTAMP NULL DEFAULT NULL,
    
    -- Metadata
    payout_notes TEXT DEFAULT NULL,
    failure_reason TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (member_id) REFERENCES members(member_id) ON DELETE RESTRICT,
    FOREIGN KEY (beneficiary_id) REFERENCES beneficiaries(beneficiary_id) ON DELETE SET NULL,
    FOREIGN KEY (policy_id) REFERENCES life_policies(policy_id) ON DELETE SET NULL,
    INDEX idx_payout_type (payout_type),
    INDEX idx_member (member_id),
    INDEX idx_policy (policy_id),
    INDEX idx_payout_status (payout_status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Unified payout system for pension withdrawals and life insurance claims';

-- ============================================================================
-- FUND TRANSACTIONS TABLE (Enhanced)
-- ============================================================================

-- Fund Transactions Table
CREATE TABLE IF NOT EXISTS fund_transactions (
    transaction_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    
    -- Transaction Type
    transaction_type ENUM('CONTRIBUTION', 'WITHDRAWAL', 'RETURN_ALLOCATION', 'TRANSFER_IN', 'TRANSFER_OUT', 'ADJUSTMENT') NOT NULL,
    
    -- References
    member_id INT UNSIGNED DEFAULT NULL,
    account_id INT UNSIGNED DEFAULT NULL,
    scheme_id INT UNSIGNED DEFAULT NULL,
    contribution_id INT UNSIGNED DEFAULT NULL,
    withdrawal_request_id INT UNSIGNED DEFAULT NULL,
    
    -- Transaction Details
    transaction_date DATE NOT NULL,
    transaction_amount DECIMAL(15,2) NOT NULL,
    balance_before DECIMAL(15,2) DEFAULT NULL,
    balance_after DECIMAL(15,2) DEFAULT NULL,
    
    -- Transaction Status
    status ENUM('PENDING', 'PROCESSED', 'REVERSED', 'FAILED') NOT NULL DEFAULT 'PENDING',
    
    -- Metadata
    reference_number VARCHAR(100) DEFAULT NULL,
    description TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (member_id) REFERENCES members(member_id) ON DELETE RESTRICT,
    FOREIGN KEY (account_id) REFERENCES member_accounts(account_id) ON DELETE RESTRICT,
    FOREIGN KEY (scheme_id) REFERENCES pension_schemes(scheme_id) ON DELETE RESTRICT,
    INDEX idx_transaction_type (transaction_type),
    INDEX idx_member (member_id),
    INDEX idx_account (account_id),
    INDEX idx_transaction_date (transaction_date),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='All fund transactions for audit and reconciliation';

-- ============================================================================
-- STORED PROCEDURES
-- ============================================================================

DELIMITER //

-- Procedure: Calculate Accrued Benefits
-- Recalculates accrued benefits for a member based on contributions and investment returns
CREATE PROCEDURE IF NOT EXISTS sp_calculate_accrued_benefits(
    IN p_member_id INT UNSIGNED,
    IN p_calculation_date DATE
)
BEGIN
    DECLARE v_account_id INT UNSIGNED;
    DECLARE v_employee_contributions DECIMAL(15,2);
    DECLARE v_employer_contributions DECIMAL(15,2);
    DECLARE v_investment_returns DECIMAL(15,2);
    DECLARE v_accrued_benefits DECIMAL(15,2);
    DECLARE v_total_withdrawn DECIMAL(15,2);
    DECLARE v_available_balance DECIMAL(15,2);
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    -- Get account ID
    SELECT account_id INTO v_account_id
    FROM member_accounts
    WHERE member_id = p_member_id;
    
    IF v_account_id IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Member account not found';
    END IF;
    
    -- Calculate total employee contributions
    SELECT COALESCE(SUM(contribution_amount), 0) INTO v_employee_contributions
    FROM contributions
    WHERE member_id = p_member_id
      AND contribution_type = 'EMPLOYEE'
      AND status = 'PROCESSED'
      AND DATE(processed_at) <= p_calculation_date;
    
    -- Calculate total employer contributions
    SELECT COALESCE(SUM(contribution_amount), 0) INTO v_employer_contributions
    FROM contributions
    WHERE member_id = p_member_id
      AND contribution_type = 'EMPLOYER'
      AND status = 'PROCESSED'
      AND DATE(processed_at) <= p_calculation_date;
    
    -- Calculate allocated investment returns
    SELECT COALESCE(SUM(allocation_amount), 0) INTO v_investment_returns
    FROM member_fund_returns mfr
    WHERE mfr.member_id = p_member_id
      AND DATE(mfr.allocated_at) <= p_calculation_date;
    
    -- Calculate accrued benefits (Kenyan pension formula)
    SET v_accrued_benefits = v_employee_contributions + v_employer_contributions + v_investment_returns;
    
    -- Get total withdrawn
    SELECT COALESCE(SUM(calculated_amount), 0) INTO v_total_withdrawn
    FROM withdrawal_requests
    WHERE member_id = p_member_id
      AND status = 'PROCESSED'
      AND DATE(processed_at) <= p_calculation_date;
    
    -- Calculate available balance
    SET v_available_balance = v_accrued_benefits - v_total_withdrawn;
    
    -- Update member account
    UPDATE member_accounts
    SET total_employee_contributions = v_employee_contributions,
        total_employer_contributions = v_employer_contributions,
        allocated_investment_returns = v_investment_returns,
        accrued_benefits = v_accrued_benefits,
        total_withdrawn = v_total_withdrawn,
        available_balance = v_available_balance,
        last_calculation_date = p_calculation_date,
        updated_at = CURRENT_TIMESTAMP
    WHERE account_id = v_account_id;
    
    -- Log the calculation
    INSERT INTO audit_logs (
        event_type, event_category, member_id, account_id,
        event_description, amount, performed_by
    ) VALUES (
        'ACCRUED_BENEFITS_CALCULATION', 'CALCULATION', p_member_id, v_account_id,
        CONCAT('Recalculated accrued benefits: Employee=', v_employee_contributions,
               ', Employer=', v_employer_contributions,
               ', Returns=', v_investment_returns,
               ', Total=', v_accrued_benefits),
        v_accrued_benefits, 'SYSTEM'
    );
    
    COMMIT;
    
    -- Return results
    SELECT v_account_id AS account_id,
           v_employee_contributions AS employee_contributions,
           v_employer_contributions AS employer_contributions,
           v_investment_returns AS investment_returns,
           v_accrued_benefits AS accrued_benefits,
           v_total_withdrawn AS total_withdrawn,
           v_available_balance AS available_balance;
END //

-- Procedure: Validate Withdrawal Eligibility
-- Validates if a member is eligible for withdrawal based on scheme rules and Kenyan regulations
CREATE PROCEDURE IF NOT EXISTS sp_validate_withdrawal_eligibility(
    IN p_member_id INT UNSIGNED,
    IN p_withdrawal_type VARCHAR(50),
    IN p_requested_date DATE,
    OUT p_is_eligible BOOLEAN,
    OUT p_eligible_amount DECIMAL(15,2),
    OUT p_rejection_reason TEXT
)
BEGIN
    DECLARE v_scheme_id INT UNSIGNED;
    DECLARE v_scheme_type VARCHAR(50);
    DECLARE v_date_of_birth DATE;
    DECLARE v_member_age INT;
    DECLARE v_retirement_age INT;
    DECLARE v_employment_status VARCHAR(50);
    DECLARE v_is_disabled BOOLEAN;
    DECLARE v_is_emigrated BOOLEAN;
    DECLARE v_available_balance DECIMAL(15,2);
    DECLARE v_accrued_benefits DECIMAL(15,2);
    DECLARE v_early_exit_max_percentage DECIMAL(5,2);
    DECLARE v_disability_allowed BOOLEAN;
    DECLARE v_emigration_allowed BOOLEAN;
    DECLARE v_calculation_date DATE;
    
    SET p_is_eligible = FALSE;
    SET p_eligible_amount = 0.00;
    SET p_rejection_reason = NULL;
    SET v_calculation_date = COALESCE(p_requested_date, CURDATE());
    
    -- Get member and scheme information
    SELECT m.scheme_id, m.employment_status, m.is_permanently_disabled, m.is_emigrated,
           m.date_of_birth, ps.scheme_type, ps.retirement_age,
           ps.early_exit_max_cash_percentage, ps.disability_full_withdrawal_allowed,
           ps.emigration_full_withdrawal_allowed, ma.available_balance, ma.accrued_benefits
    INTO v_scheme_id, v_employment_status, v_is_disabled, v_is_emigrated,
         v_date_of_birth, v_scheme_type, v_retirement_age,
         v_early_exit_max_percentage, v_disability_allowed, v_emigration_allowed,
         v_available_balance, v_accrued_benefits
    FROM members m
    INNER JOIN pension_schemes ps ON m.scheme_id = ps.scheme_id
    INNER JOIN member_accounts ma ON m.member_id = ma.member_id
    WHERE m.member_id = p_member_id;
    
    IF v_scheme_id IS NULL THEN
        SET p_rejection_reason = 'Member or scheme not found';
        SET p_is_eligible = FALSE;
        SET p_eligible_amount = 0.00;
    ELSE
        -- Calculate member age
        SET v_member_age = TIMESTAMPDIFF(YEAR, v_date_of_birth, v_calculation_date);
        
        -- Recalculate accrued benefits if needed
        CALL sp_calculate_accrued_benefits(p_member_id, v_calculation_date);
        
        -- Refresh account balances
        SELECT available_balance, accrued_benefits
        INTO v_available_balance, v_accrued_benefits
        FROM member_accounts
        WHERE member_id = p_member_id;
        
        -- Validate based on withdrawal type
        eligibility_check: BEGIN
        -- Permanent Disability Withdrawal
        IF p_withdrawal_type = 'DISABILITY' THEN
            IF NOT v_is_disabled THEN
                SET p_rejection_reason = 'Member is not marked as permanently disabled';
                LEAVE eligibility_check;
            END IF;
            
            IF NOT v_disability_allowed THEN
                SET p_rejection_reason = 'Scheme does not allow full withdrawal for disability';
                LEAVE eligibility_check;
            END IF;
            
            SET p_is_eligible = TRUE;
            SET p_eligible_amount = v_available_balance;
            LEAVE eligibility_check;
        END IF;
        
        -- Emigration Withdrawal
        IF p_withdrawal_type = 'EMIGRATION' THEN
            IF NOT v_is_emigrated THEN
                SET p_rejection_reason = 'Member is not marked as permanently emigrated';
                LEAVE eligibility_check;
            END IF;
            
            IF NOT v_emigration_allowed THEN
                SET p_rejection_reason = 'Scheme does not allow full withdrawal for emigration';
                LEAVE eligibility_check;
            END IF;
            
            SET p_is_eligible = TRUE;
            SET p_eligible_amount = v_available_balance;
            LEAVE eligibility_check;
        END IF;
        
        -- Retirement Withdrawal
        IF p_withdrawal_type = 'RETIREMENT' THEN
            IF v_member_age < v_retirement_age THEN
                SET p_rejection_reason = CONCAT('Member age (', v_member_age, ') is below retirement age (', v_retirement_age, ')');
                LEAVE eligibility_check;
            END IF;
            
            SET p_is_eligible = TRUE;
            SET p_eligible_amount = v_available_balance;
            LEAVE eligibility_check;
        END IF;
        
        -- Early Exit (Before Retirement Age)
        IF p_withdrawal_type = 'EARLY_EXIT' THEN
            IF v_member_age >= v_retirement_age THEN
                SET p_rejection_reason = 'Member has reached retirement age. Use RETIREMENT withdrawal type instead';
                LEAVE eligibility_check;
            END IF;
            
            -- Apply scheme-specific early exit rules
            -- Occupational & Umbrella: Max 50%, IPP: Up to 100%
            IF v_scheme_type IN ('OCCUPATIONAL', 'UMBRELLA') THEN
                SET p_eligible_amount = v_available_balance * (v_early_exit_max_percentage / 100.00);
            ELSEIF v_scheme_type = 'INDIVIDUAL_PENSION_PLAN' THEN
                SET p_eligible_amount = v_available_balance * (v_early_exit_max_percentage / 100.00);
            ELSE
                SET p_eligible_amount = v_available_balance * (v_early_exit_max_percentage / 100.00);
            END IF;
            
            SET p_is_eligible = TRUE;
            LEAVE eligibility_check;
        END IF;
        
        -- Partial Withdrawal (if scheme allows)
        IF p_withdrawal_type = 'PARTIAL' THEN
            SET p_is_eligible = TRUE;
            SET p_eligible_amount = v_available_balance;
            LEAVE eligibility_check;
        END IF;
        
        -- Default: Not eligible
        SET p_rejection_reason = CONCAT('Unknown or invalid withdrawal type: ', p_withdrawal_type);
        
    END eligibility_check;
    END IF;
    
    -- Log eligibility check
    INSERT INTO audit_logs (
        event_type, event_category, member_id, scheme_id,
        event_description, amount, performed_by
    ) VALUES (
        'WITHDRAWAL_ELIGIBILITY_CHECK', 'WITHDRAWAL', p_member_id, v_scheme_id,
        CONCAT('Eligibility check: Type=', p_withdrawal_type,
               ', Eligible=', p_is_eligible,
               ', Amount=', p_eligible_amount,
               IF(p_rejection_reason IS NOT NULL, CONCAT(', Reason=', p_rejection_reason), '')),
        p_eligible_amount, 'SYSTEM'
    );
END //

-- Procedure: Calculate Withdrawal Amount
-- Calculates the actual withdrawal amount based on scheme rules and requested amount
CREATE PROCEDURE IF NOT EXISTS sp_calculate_withdrawal_amount(
    IN p_request_id INT UNSIGNED,
    OUT p_calculated_amount DECIMAL(15,2),
    OUT p_remaining_balance DECIMAL(15,2),
    OUT p_balance_action VARCHAR(50)
)
BEGIN
    DECLARE v_member_id INT UNSIGNED;
    DECLARE v_account_id INT UNSIGNED;
    DECLARE v_scheme_id INT UNSIGNED;
    DECLARE v_withdrawal_type VARCHAR(50);
    DECLARE v_requested_amount DECIMAL(15,2);
    DECLARE v_eligible_amount DECIMAL(15,2);
    DECLARE v_available_balance DECIMAL(15,2);
    DECLARE v_accrued_benefits DECIMAL(15,2);
    DECLARE v_employee_contrib DECIMAL(15,2);
    DECLARE v_employer_contrib DECIMAL(15,2);
    DECLARE v_investment_returns DECIMAL(15,2);
    
    DECLARE v_scheme_type VARCHAR(50);
    DECLARE v_early_exit_max_percentage DECIMAL(5,2);
    DECLARE v_early_exit_balance_action VARCHAR(50);
    DECLARE v_retirement_commutation_percentage DECIMAL(5,2);
    DECLARE v_retirement_balance_action VARCHAR(50);
    DECLARE v_withdrawal_percentage DECIMAL(5,2);
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    -- Get withdrawal request details
    SELECT wr.member_id, wr.account_id, wr.scheme_id, wr.withdrawal_type,
           wr.requested_amount, wr.eligible_amount
    INTO v_member_id, v_account_id, v_scheme_id, v_withdrawal_type,
         v_requested_amount, v_eligible_amount
    FROM withdrawal_requests wr
    WHERE wr.request_id = p_request_id;
    
    IF v_member_id IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Withdrawal request not found';
    END IF;
    
    -- Get scheme rules
    SELECT ps.scheme_type, ps.early_exit_max_cash_percentage, ps.early_exit_balance_action,
           ps.retirement_commutation_percentage, ps.retirement_balance_action
    INTO v_scheme_type, v_early_exit_max_percentage, v_early_exit_balance_action,
         v_retirement_commutation_percentage, v_retirement_balance_action
    FROM pension_schemes ps
    WHERE ps.scheme_id = v_scheme_id;
    
    -- Recalculate accrued benefits
    CALL sp_calculate_accrued_benefits(v_member_id, CURDATE());
    
    -- Get current account balances
    SELECT available_balance, accrued_benefits,
           total_employee_contributions, total_employer_contributions,
           allocated_investment_returns
    INTO v_available_balance, v_accrued_benefits,
         v_employee_contrib, v_employer_contrib, v_investment_returns
    FROM member_accounts
    WHERE account_id = v_account_id;
    
    -- Calculate withdrawal amount based on type
    CASE v_withdrawal_type
        WHEN 'DISABILITY' THEN
            -- Full withdrawal allowed for permanent disability
            SET p_calculated_amount = LEAST(v_requested_amount, v_available_balance);
            SET p_balance_action = NULL;
            SET v_withdrawal_percentage = 100.00;
            
        WHEN 'EMIGRATION' THEN
            -- Full withdrawal allowed for permanent emigration
            SET p_calculated_amount = LEAST(v_requested_amount, v_available_balance);
            SET p_balance_action = NULL;
            SET v_withdrawal_percentage = 100.00;
            
        WHEN 'RETIREMENT' THEN
            -- Retirement: Allow partial commutation (lump sum) + remaining for annuity/withdrawals
            IF v_retirement_commutation_percentage IS NOT NULL THEN
                SET p_calculated_amount = LEAST(
                    v_requested_amount,
                    v_available_balance * (v_retirement_commutation_percentage / 100.00),
                    v_available_balance
                );
                SET v_withdrawal_percentage = v_retirement_commutation_percentage;
            ELSE
                SET p_calculated_amount = LEAST(v_requested_amount, v_available_balance);
                SET v_withdrawal_percentage = NULL;
            END IF;
            SET p_balance_action = v_retirement_balance_action;
            
        WHEN 'EARLY_EXIT' THEN
            -- Early exit: Max percentage as cash, remaining deferred/transferred
            SET p_calculated_amount = LEAST(
                v_requested_amount,
                v_available_balance * (v_early_exit_max_percentage / 100.00),
                v_available_balance
            );
            SET v_withdrawal_percentage = v_early_exit_max_percentage;
            SET p_balance_action = v_early_exit_balance_action;
            
        WHEN 'PARTIAL' THEN
            -- Partial withdrawal (scheme-specific rules apply)
            SET p_calculated_amount = LEAST(v_requested_amount, v_available_balance);
            SET p_balance_action = NULL;
            SET v_withdrawal_percentage = NULL;
            
        ELSE
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = CONCAT('Invalid withdrawal type: ', v_withdrawal_type);
    END CASE;
    
    -- Ensure calculated amount doesn't exceed eligible amount
    IF v_eligible_amount IS NOT NULL THEN
        SET p_calculated_amount = LEAST(p_calculated_amount, v_eligible_amount);
    END IF;
    
    -- Calculate remaining balance
    SET p_remaining_balance = v_available_balance - p_calculated_amount;
    
    -- Create detailed calculation record
    INSERT INTO payout_calculations (
        request_id, member_id, account_id, calculation_date,
        accrued_benefits_at_calculation, total_withdrawn_before, available_balance_before,
        employee_contributions_component, employer_contributions_component,
        investment_returns_component, withdrawal_percentage_applied, withdrawal_type_applied,
        calculated_withdrawal_amount, remaining_balance, balance_action,
        calculation_method, calculation_notes
    ) VALUES (
        p_request_id, v_member_id, v_account_id, CURDATE(),
        v_accrued_benefits, v_available_balance - v_accrued_benefits, v_available_balance,
        v_employee_contrib, v_employer_contrib, v_investment_returns,
        v_withdrawal_percentage, v_withdrawal_type,
        p_calculated_amount, p_remaining_balance, p_balance_action,
        CONCAT('Rule-based calculation for ', v_withdrawal_type),
        CONCAT('Scheme: ', v_scheme_type, ', Applied percentage: ', COALESCE(v_withdrawal_percentage, 'N/A'))
    );
    
    -- Update withdrawal request with calculated amount
    UPDATE withdrawal_requests
    SET calculated_amount = p_calculated_amount,
        updated_at = CURRENT_TIMESTAMP
    WHERE request_id = p_request_id;
    
    -- Log calculation
    INSERT INTO audit_logs (
        event_type, event_category, member_id, account_id, request_id,
        event_description, amount, performed_by
    ) VALUES (
        'WITHDRAWAL_CALCULATION', 'CALCULATION', v_member_id, v_account_id, p_request_id,
        CONCAT('Withdrawal calculated: Type=', v_withdrawal_type,
               ', Amount=', p_calculated_amount,
               ', Remaining=', p_remaining_balance),
        p_calculated_amount, 'SYSTEM'
    );
    
    COMMIT;
END //

-- Procedure: Calculate Life Insurance Surrender Value
-- Calculates surrender value based on policy rules and premiums paid
CREATE PROCEDURE IF NOT EXISTS sp_calculate_surrender_value(
    IN p_policy_id INT UNSIGNED,
    IN p_surrender_date DATE,
    OUT p_surrender_value DECIMAL(15,2),
    OUT p_is_eligible BOOLEAN,
    OUT p_eligibility_reason TEXT
)
BEGIN
    DECLARE v_product_id INT UNSIGNED;
    DECLARE v_policy_start_date DATE;
    DECLARE v_policy_year INT;
    DECLARE v_minimum_years INT;
    DECLARE v_total_premiums DECIMAL(15,2);
    DECLARE v_total_bonuses DECIMAL(15,2);
    DECLARE v_surrender_factor DECIMAL(5,2);
    DECLARE v_policy_status VARCHAR(50);
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    SET p_surrender_value = 0.00;
    SET p_is_eligible = FALSE;
    SET p_eligibility_reason = NULL;
    
    -- Get policy details
    SELECT lp.product_id, lp.policy_start_date, lp.policy_status,
           lp.total_premiums_paid, lp.total_bonuses_allocated
    INTO v_product_id, v_policy_start_date, v_policy_status,
         v_total_premiums, v_total_bonuses
    FROM life_policies lp
    WHERE lp.policy_id = p_policy_id;
    
    IF v_product_id IS NULL THEN
        SET p_eligibility_reason = 'Policy not found';
        SET p_is_eligible = FALSE;
        SET p_surrender_value = 0.00;
    ELSE
        -- Check policy status
        IF v_policy_status NOT IN ('ACTIVE', 'LAPSED') THEN
            SET p_eligibility_reason = CONCAT('Policy status (', v_policy_status, ') does not allow surrender');
            SET p_is_eligible = FALSE;
            SET p_surrender_value = 0.00;
        ELSE
            -- Calculate policy year
            SET v_policy_year = TIMESTAMPDIFF(YEAR, v_policy_start_date, p_surrender_date);
            
            -- Get minimum surrender years from product
            SELECT minimum_surrender_years INTO v_minimum_years
            FROM life_insurance_products
            WHERE product_id = v_product_id;
            
            -- Check eligibility (minimum years)
            IF v_policy_year < v_minimum_years THEN
                SET p_eligibility_reason = CONCAT('Policy must be active for at least ', v_minimum_years, ' years. Current: ', v_policy_year);
                SET p_is_eligible = FALSE;
                SET p_surrender_value = 0.00;
            ELSE
                -- Get surrender factor for this policy year
                SELECT surrender_factor INTO v_surrender_factor
                FROM policy_rules
                WHERE product_id = v_product_id
                  AND policy_year = v_policy_year
                  AND is_active = TRUE
                  AND (expiry_date IS NULL OR expiry_date >= p_surrender_date)
                ORDER BY policy_year DESC
                LIMIT 1;
                
                -- If no specific factor for this year, get the highest available
                IF v_surrender_factor IS NULL THEN
                    SELECT surrender_factor INTO v_surrender_factor
                    FROM policy_rules
                    WHERE product_id = v_product_id
                      AND policy_year <= v_policy_year
                      AND is_active = TRUE
                    ORDER BY policy_year DESC
                    LIMIT 1;
                END IF;
                
                IF v_surrender_factor IS NULL THEN
                    SET p_eligibility_reason = 'No surrender factor defined for this policy year';
                    SET p_is_eligible = FALSE;
                    SET p_surrender_value = 0.00;
                ELSE
                    -- Calculate surrender value (Kenyan market formula)
                -- Surrender Value = (Total Premiums Paid × Surrender Factor) + Bonuses
                SET p_surrender_value = (v_total_premiums * (v_surrender_factor / 100.00)) + COALESCE(v_total_bonuses, 0.00);
                
                SET p_is_eligible = TRUE;
                
                -- Update policy with calculated surrender value
                UPDATE life_policies
                SET surrender_value = p_surrender_value,
                    updated_at = CURRENT_TIMESTAMP
                WHERE policy_id = p_policy_id;
                
                -- Log calculation
                INSERT INTO audit_logs (
                    event_type, event_category, policy_id,
                    event_description, amount, performed_by
                ) VALUES (
                    'SURRENDER_VALUE_CALCULATION', 'CALCULATION', p_policy_id,
                    CONCAT('Surrender value calculated: Policy Year=', v_policy_year,
                           ', Premiums=', v_total_premiums,
                           ', Factor=', v_surrender_factor,
                           ', Bonuses=', COALESCE(v_total_bonuses, 0),
                           ', Surrender Value=', p_surrender_value),
                    p_surrender_value, 'SYSTEM'
                );
                END IF;
            END IF;
        END IF;
    END IF;
    
    COMMIT;
END //

-- Procedure: Calculate Life Insurance Claim Amount
-- Calculates claim amount for death, maturity, or disability claims
CREATE PROCEDURE IF NOT EXISTS sp_calculate_life_claim_amount(
    IN p_policy_id INT UNSIGNED,
    IN p_claim_type VARCHAR(50),
    OUT p_claim_amount DECIMAL(15,2),
    OUT p_sum_assured_component DECIMAL(15,2),
    OUT p_bonus_component DECIMAL(15,2)
)
BEGIN
    DECLARE v_sum_assured DECIMAL(15,2);
    DECLARE v_total_bonuses DECIMAL(15,2);
    DECLARE v_product_type VARCHAR(50);
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    SET p_claim_amount = 0.00;
    SET p_sum_assured_component = 0.00;
    SET p_bonus_component = 0.00;
    
    -- Get policy details
    SELECT lp.sum_assured, lp.total_bonuses_allocated, lip.product_type
    INTO v_sum_assured, v_total_bonuses, v_product_type
    FROM life_policies lp
    INNER JOIN life_insurance_products lip ON lp.product_id = lip.product_id
    WHERE lp.policy_id = p_policy_id;
    
    IF v_sum_assured IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Policy not found';
    END IF;
    
    -- For credit life, claim is limited to outstanding loan balance
    IF v_product_type = 'CREDIT_LIFE' THEN
        SELECT COALESCE(outstanding_loan_balance, v_sum_assured) INTO v_sum_assured
        FROM life_policies
        WHERE policy_id = p_policy_id;
    END IF;
    
    SET p_sum_assured_component = v_sum_assured;
    SET p_bonus_component = COALESCE(v_total_bonuses, 0.00);
    
    -- Claim amount = Sum Assured + Bonuses (Kenyan market formula)
    SET p_claim_amount = p_sum_assured_component + p_bonus_component;
    
    -- Log calculation
    INSERT INTO audit_logs (
        event_type, event_category, policy_id,
        event_description, amount, performed_by
    ) VALUES (
        'LIFE_CLAIM_CALCULATION', 'CALCULATION', p_policy_id,
        CONCAT('Claim calculated: Type=', p_claim_type,
               ', Sum Assured=', p_sum_assured_component,
               ', Bonuses=', p_bonus_component,
               ', Total=', p_claim_amount),
        p_claim_amount, 'SYSTEM'
    );
    
    COMMIT;
END //

DELIMITER ;

-- ============================================================================
-- TRIGGERS
-- ============================================================================

DELIMITER //

-- Trigger: Prevent Over-Withdrawal
-- Ensures that withdrawal amounts do not exceed available balance
CREATE TRIGGER trg_prevent_over_withdrawal
BEFORE UPDATE ON withdrawal_requests
FOR EACH ROW
BEGIN
    DECLARE v_available_balance DECIMAL(15,2);
    DECLARE v_total_pending_withdrawals DECIMAL(15,2);
    
    -- Only check when status changes to PROCESSED
    IF NEW.status = 'PROCESSED' AND (OLD.status IS NULL OR OLD.status != 'PROCESSED') THEN
        -- Get current available balance
        SELECT available_balance INTO v_available_balance
        FROM member_accounts
        WHERE account_id = NEW.account_id;
        
        -- Get total of other pending/approved withdrawals for this account
        SELECT COALESCE(SUM(calculated_amount), 0) INTO v_total_pending_withdrawals
        FROM withdrawal_requests
        WHERE account_id = NEW.account_id
          AND request_id != NEW.request_id
          AND status IN ('PENDING', 'APPROVED', 'PROCESSED')
          AND request_id != NEW.request_id;
        
        -- Check if withdrawal exceeds available balance
        IF NEW.calculated_amount > (v_available_balance - v_total_pending_withdrawals) THEN
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = CONCAT(
                'Withdrawal amount (', NEW.calculated_amount,
                ') exceeds available balance (', v_available_balance - v_total_pending_withdrawals,
                '). Over-withdrawal prevented.'
            );
        END IF;
    END IF;
END //

-- Trigger: Update Account on Withdrawal Processing
-- Updates member account when withdrawal is processed
CREATE TRIGGER trg_update_account_on_withdrawal
AFTER UPDATE ON withdrawal_requests
FOR EACH ROW
BEGIN
    DECLARE v_account_id INT UNSIGNED;
    
    -- Only process when status changes to PROCESSED
    IF NEW.status = 'PROCESSED' AND (OLD.status IS NULL OR OLD.status != 'PROCESSED') THEN
        SET v_account_id = NEW.account_id;
        
        -- Recalculate account balances
        CALL sp_calculate_accrued_benefits(NEW.member_id, CURDATE());
        
        -- Log the withdrawal processing
        INSERT INTO audit_logs (
            event_type, event_category, member_id, account_id, request_id,
            event_description, amount, performed_by
        ) VALUES (
            'WITHDRAWAL_PROCESSED', 'WITHDRAWAL', NEW.member_id, v_account_id, NEW.request_id,
            CONCAT('Withdrawal processed: Amount=', NEW.calculated_amount,
                   ', Type=', NEW.withdrawal_type),
            NEW.calculated_amount, 'SYSTEM'
        );
    END IF;
END //

-- Trigger: Log Contribution Processing
-- Logs all contribution transactions for audit trail
CREATE TRIGGER trg_log_contribution_processing
AFTER UPDATE ON contributions
FOR EACH ROW
BEGIN
    -- Log when contribution is processed
    IF NEW.status = 'PROCESSED' AND (OLD.status IS NULL OR OLD.status != 'PROCESSED') THEN
        INSERT INTO audit_logs (
            event_type, event_category, member_id, account_id,
            event_description, amount, performed_by
        ) VALUES (
            'CONTRIBUTION_PROCESSED', 'CONTRIBUTION', NEW.member_id, NEW.account_id,
            CONCAT('Contribution processed: Type=', NEW.contribution_type,
                   ', Amount=', NEW.contribution_amount,
                   ', Period=', NEW.contribution_period),
            NEW.contribution_amount, 'SYSTEM'
        );
        
        -- Recalculate accrued benefits after contribution
        CALL sp_calculate_accrued_benefits(NEW.member_id, CURDATE());
    END IF;
END //

-- Trigger: Log Fund Return Allocation
-- Logs investment return allocations to members
CREATE TRIGGER trg_log_fund_return_allocation
AFTER INSERT ON member_fund_returns
FOR EACH ROW
BEGIN
    INSERT INTO audit_logs (
        event_type, event_category, member_id, account_id,
        event_description, amount, performed_by
    ) VALUES (
        'FUND_RETURN_ALLOCATED', 'FINANCIAL', NEW.member_id, NEW.account_id,
        CONCAT('Investment return allocated: Amount=', NEW.allocation_amount,
               ', Percentage=', NEW.allocation_percentage),
        NEW.allocation_amount, 'SYSTEM'
    );
    
    -- Recalculate accrued benefits after return allocation
    CALL sp_calculate_accrued_benefits(NEW.member_id, CURDATE());
END //

-- Trigger: Log Account Balance Changes
-- Comprehensive logging of all account balance updates
CREATE TRIGGER trg_log_account_balance_changes
AFTER UPDATE ON member_accounts
FOR EACH ROW
BEGIN
    -- Only log if there are actual balance changes
    IF OLD.accrued_benefits != NEW.accrued_benefits OR
       OLD.available_balance != NEW.available_balance OR
       OLD.total_withdrawn != NEW.total_withdrawn THEN
        
        INSERT INTO audit_logs (
            event_type, event_category, member_id, account_id,
            event_description, old_value, new_value, amount, performed_by
        ) VALUES (
            'ACCOUNT_BALANCE_UPDATE', 'ACCOUNT_UPDATE', NEW.member_id, NEW.account_id,
            'Account balance updated',
            JSON_OBJECT(
                'accrued_benefits', OLD.accrued_benefits,
                'available_balance', OLD.available_balance,
                'total_withdrawn', OLD.total_withdrawn
            ),
            JSON_OBJECT(
                'accrued_benefits', NEW.accrued_benefits,
                'available_balance', NEW.available_balance,
                'total_withdrawn', NEW.total_withdrawn
            ),
            NEW.accrued_benefits, 'SYSTEM'
        );
    END IF;
END //

-- Trigger: Prevent Early Surrender
-- Prevents surrender before minimum policy years
CREATE TRIGGER trg_prevent_early_surrender
BEFORE INSERT ON surrender_requests
FOR EACH ROW
BEGIN
    DECLARE v_policy_start_date DATE;
    DECLARE v_policy_year INT;
    DECLARE v_minimum_years INT;
    DECLARE v_product_id INT UNSIGNED;
    
    -- Get policy details
    SELECT lp.policy_start_date, lp.product_id
    INTO v_policy_start_date, v_product_id
    FROM life_policies lp
    WHERE lp.policy_id = NEW.policy_id;
    
    IF v_policy_start_date IS NOT NULL THEN
        -- Calculate policy year
        SET v_policy_year = TIMESTAMPDIFF(YEAR, v_policy_start_date, NEW.requested_date);
        SET NEW.policy_year_at_surrender = v_policy_year;
        
        -- Get minimum surrender years
        SELECT minimum_surrender_years INTO v_minimum_years
        FROM life_insurance_products
        WHERE product_id = v_product_id;
        
        -- Check if eligible
        IF v_policy_year < v_minimum_years THEN
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = CONCAT(
                'Policy must be active for at least ', v_minimum_years,
                ' years before surrender. Current: ', v_policy_year, ' years'
            );
        END IF;
    END IF;
END //

-- Trigger: Update Policy on Premium Payment
-- Updates policy totals when premium is paid
CREATE TRIGGER trg_update_policy_on_premium_payment
AFTER UPDATE ON premiums
FOR EACH ROW
BEGIN
    -- Only process when payment status changes to PAID
    IF NEW.payment_status = 'PAID' AND (OLD.payment_status IS NULL OR OLD.payment_status != 'PAID') THEN
        -- Update policy total premiums paid
        UPDATE life_policies
        SET total_premiums_paid = total_premiums_paid + NEW.paid_amount,
            updated_at = CURRENT_TIMESTAMP
        WHERE policy_id = NEW.policy_id;
        
        -- Log premium payment
        INSERT INTO audit_logs (
            event_type, event_category, policy_id,
            event_description, amount, performed_by
        ) VALUES (
            'PREMIUM_PAID', 'FINANCIAL', NEW.policy_id,
            CONCAT('Premium paid: Amount=', NEW.paid_amount,
                   ', Due Date=', NEW.premium_due_date,
                   ', Method=', NEW.payment_method),
            NEW.paid_amount, 'SYSTEM'
        );
    END IF;
END //

-- Trigger: Log Life Claim Processing
-- Logs all life insurance claim processing
CREATE TRIGGER trg_log_life_claim_processing
AFTER UPDATE ON life_claims
FOR EACH ROW
BEGIN
    -- Log when claim is processed/paid
    IF NEW.claim_status = 'PAID' AND (OLD.claim_status IS NULL OR OLD.claim_status != 'PAID') THEN
        INSERT INTO audit_logs (
            event_type, event_category, policy_id,
            event_description, amount, performed_by
        ) VALUES (
            'LIFE_CLAIM_PAID', 'FINANCIAL', NEW.policy_id,
            CONCAT('Life claim paid: Type=', NEW.claim_type,
                   ', Amount=', NEW.claim_amount,
                   ', Sum Assured=', NEW.sum_assured_component,
                   ', Bonuses=', NEW.bonus_component),
            NEW.claim_amount, 'SYSTEM'
        );
    END IF;
END //

-- Trigger: Log Surrender Processing
-- Logs surrender request processing
CREATE TRIGGER trg_log_surrender_processing
AFTER UPDATE ON surrender_requests
FOR EACH ROW
BEGIN
    -- Log when surrender is processed
    IF NEW.status = 'PROCESSED' AND (OLD.status IS NULL OR OLD.status != 'PROCESSED') THEN
        -- Update policy status
        UPDATE life_policies
        SET policy_status = 'SURRENDERED',
            surrender_date = NEW.requested_date,
            updated_at = CURRENT_TIMESTAMP
        WHERE policy_id = NEW.policy_id;
        
        -- Log surrender processing
        INSERT INTO audit_logs (
            event_type, event_category, policy_id,
            event_description, amount, performed_by
        ) VALUES (
            'SURRENDER_PROCESSED', 'FINANCIAL', NEW.policy_id,
            CONCAT('Surrender processed: Policy Year=', NEW.policy_year_at_surrender,
                   ', Surrender Value=', NEW.calculated_surrender_value,
                   ', Premiums Paid=', NEW.total_premiums_paid,
                   ', Bonuses=', NEW.bonuses_included),
            NEW.calculated_surrender_value, 'SYSTEM'
        );
    END IF;
END //

-- Trigger: Log Bonus Allocation
-- Logs bonus allocations to policies
CREATE TRIGGER trg_log_bonus_allocation
AFTER INSERT ON bonuses
FOR EACH ROW
BEGIN
    -- Update policy total bonuses
    UPDATE life_policies
    SET total_bonuses_allocated = total_bonuses_allocated + NEW.bonus_amount,
        current_policy_value = sum_assured + total_bonuses_allocated + NEW.bonus_amount,
        updated_at = CURRENT_TIMESTAMP
    WHERE policy_id = NEW.policy_id;
    
    -- Log bonus allocation
    INSERT INTO audit_logs (
        event_type, event_category, policy_id,
        event_description, amount, performed_by
    ) VALUES (
        'BONUS_ALLOCATED', 'FINANCIAL', NEW.policy_id,
        CONCAT('Bonus allocated: Type=', NEW.bonus_type,
               ', Amount=', NEW.bonus_amount,
               ', Policy Year=', NEW.policy_year),
        NEW.bonus_amount, 'SYSTEM'
    );
END //

DELIMITER ;

-- ============================================================================
-- INDEXES FOR PERFORMANCE
-- ============================================================================

-- Additional composite indexes for common query patterns
CREATE INDEX idx_contributions_member_period_type ON contributions(member_id, contribution_period, contribution_type);
CREATE INDEX idx_withdrawal_requests_member_type_status ON withdrawal_requests(member_id, withdrawal_type, status);
CREATE INDEX idx_audit_logs_member_date ON audit_logs(member_id, created_at);
CREATE INDEX idx_payout_calculations_request_member ON payout_calculations(request_id, member_id);

-- ============================================================================
-- VIEWS FOR REPORTING
-- ============================================================================

-- View: Member Account Summary
CREATE OR REPLACE VIEW vw_member_account_summary AS
SELECT 
    m.member_id,
    m.member_number,
    m.first_name,
    m.last_name,
    m.national_id,
    ps.scheme_name,
    ps.scheme_type,
    ma.accrued_benefits,
    ma.available_balance,
    ma.total_withdrawn,
    ma.total_employee_contributions,
    ma.total_employer_contributions,
    ma.allocated_investment_returns,
    m.employment_status,
    TIMESTAMPDIFF(YEAR, m.date_of_birth, CURDATE()) AS current_age,
    ps.retirement_age,
    ma.last_calculation_date
FROM members m
INNER JOIN pension_schemes ps ON m.scheme_id = ps.scheme_id
INNER JOIN member_accounts ma ON m.member_id = ma.member_id
WHERE m.is_active = TRUE;

-- View: Withdrawal Request Summary
CREATE OR REPLACE VIEW vw_withdrawal_request_summary AS
SELECT 
    wr.request_id,
    wr.member_id,
    m.member_number,
    m.first_name,
    m.last_name,
    ps.scheme_name,
    wr.withdrawal_type,
    wr.requested_amount,
    wr.eligible_amount,
    wr.calculated_amount,
    wr.status,
    wr.requested_date,
    wr.approved_at,
    wr.processed_at,
    pc.remaining_balance,
    pc.balance_action
FROM withdrawal_requests wr
INNER JOIN members m ON wr.member_id = m.member_id
INNER JOIN pension_schemes ps ON wr.scheme_id = ps.scheme_id
LEFT JOIN payout_calculations pc ON wr.request_id = pc.request_id;

-- ============================================================================
-- SAMPLE DATA AND USAGE EXAMPLES (COMMENTED)
-- ============================================================================

/*
-- Example 1: Create a Pension Scheme
INSERT INTO pension_schemes (
    scheme_code, scheme_name, scheme_type, retirement_age,
    early_exit_max_cash_percentage, early_exit_balance_action,
    retirement_commutation_percentage, retirement_balance_action,
    disability_full_withdrawal_allowed, emigration_full_withdrawal_allowed,
    investment_return_allocation_frequency, rba_registration_number
) VALUES (
    'OCC-001', 'ABC Company Pension Scheme', 'OCCUPATIONAL', 60,
    50.00, 'DEFER',  -- Early exit: 50% cash, rest deferred
    30.00, 'ANNUITY',  -- Retirement: 30% lump sum, rest annuity
    TRUE, TRUE,  -- Allow full withdrawal for disability/emigration
    'QUARTERLY', 'RBA/2023/001'
);

-- Example 2: Create an Individual Pension Plan
INSERT INTO pension_schemes (
    scheme_code, scheme_name, scheme_type, retirement_age,
    early_exit_max_cash_percentage, early_exit_balance_action,
    retirement_commutation_percentage, retirement_balance_action,
    disability_full_withdrawal_allowed, emigration_full_withdrawal_allowed,
    investment_return_allocation_frequency
) VALUES (
    'IPP-001', 'Premium Individual Pension Plan', 'INDIVIDUAL_PENSION_PLAN', 60,
    100.00, 'TRANSFER',  -- IPP: Up to 100% withdrawal allowed
    50.00, 'PROGRAMMED_WITHDRAWAL',  -- Retirement: 50% lump sum, rest programmed
    TRUE, TRUE,
    'MONTHLY'
);

-- Example 3: Create a Member
INSERT INTO members (
    scheme_id, member_number, national_id, first_name, last_name,
    date_of_birth, gender, email, phone_number, employment_status
) VALUES (
    1, 'MEM-001', '12345678', 'John', 'Doe',
    '1980-01-15', 'MALE', 'john.doe@example.com', '+254712345678', 'ACTIVE'
);

-- Example 4: Create Member Account
INSERT INTO member_accounts (member_id) VALUES (1);

-- Example 5: Record Contributions
INSERT INTO contributions (
    member_id, account_id, contribution_type, contribution_period,
    contribution_amount, employee_contribution, employer_contribution,
    status, reference_number
) VALUES (
    1, 1, 'EMPLOYEE', '2024-01-31', 5000.00, 5000.00, NULL,
    'PROCESSED', 'CONT-2024-01-001'
),
(
    1, 1, 'EMPLOYER', '2024-01-31', 5000.00, NULL, 5000.00,
    'PROCESSED', 'CONT-2024-01-002'
);

-- Example 6: Allocate Fund Returns
INSERT INTO fund_returns (
    scheme_id, return_period_start, return_period_end,
    return_percentage, return_amount, allocation_frequency,
    allocation_date, status
) VALUES (
    1, '2024-01-01', '2024-03-31', 2.50, 25000.00,
    'QUARTERLY', '2024-04-15', 'ALLOCATED'
);

-- Example 7: Create Withdrawal Request
INSERT INTO withdrawal_requests (
    member_id, account_id, scheme_id, withdrawal_type,
    requested_amount, requested_date, status
) VALUES (
    1, 1, 1, 'EARLY_EXIT', 50000.00, '2024-06-01', 'PENDING'
);

-- Example 8: Validate Eligibility
CALL sp_validate_withdrawal_eligibility(1, 'EARLY_EXIT', '2024-06-01', @is_eligible, @eligible_amount, @rejection_reason);
SELECT @is_eligible, @eligible_amount, @rejection_reason;

-- Example 9: Calculate Withdrawal Amount
CALL sp_calculate_withdrawal_amount(@request_id, @calculated_amount, @remaining_balance, @balance_action);
SELECT @calculated_amount, @remaining_balance, @balance_action;

-- Example 10: Process Withdrawal
UPDATE withdrawal_requests
SET status = 'APPROVED', approved_by = 1, approved_at = CURRENT_TIMESTAMP
WHERE request_id = @request_id;

UPDATE withdrawal_requests
SET status = 'PROCESSED', processed_at = CURRENT_TIMESTAMP, payment_reference = 'PAY-2024-001'
WHERE request_id = @request_id;
*/

-- ============================================================================
-- END OF SCHEMA
-- ============================================================================
