One Hat Cyber Team

View File Name : schema.sql

-- Farmers Loan Management SAAS System
-- Complete Database Schema
-- Version: 1.0.0
-- Created: 2024

-- Create database
CREATE DATABASE IF NOT EXISTS `farmers_loan_management` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `farmers_loan_management`;

-- Disable foreign key checks temporarily
SET FOREIGN_KEY_CHECKS = 0;

-- =============================================
-- USERS AND AUTHENTICATION TABLES
-- =============================================

-- Roles table
CREATE TABLE `roles` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,
    `description` TEXT,
    `hierarchy_level` INT DEFAULT 0,
    `is_system` TINYINT(1) DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_role_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Permissions table
CREATE TABLE `permissions` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `description` TEXT,
    `module` VARCHAR(50) NOT NULL,
    `action` VARCHAR(50) NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_permission_name` (`name`),
    KEY `idx_permission_module` (`module`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Branches table (created before users due to foreign key)
CREATE TABLE `branches` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `code` VARCHAR(20) NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `address` TEXT,
    `phone` VARCHAR(20),
    `email` VARCHAR(100),
    `manager_id` INT UNSIGNED,
    `opening_date` DATE,
    `status` ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    `latitude` DECIMAL(10, 8),
    `longitude` DECIMAL(11, 8),
    `timezone` VARCHAR(50) DEFAULT 'UTC',
    `currency_code` VARCHAR(3) DEFAULT 'USD',
    `settings` JSON,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_branch_code` (`code`),
    UNIQUE KEY `uk_branch_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Users table
CREATE TABLE `users` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(50) NOT NULL,
    `email` VARCHAR(100) NOT NULL,
    `password_hash` VARCHAR(255) NOT NULL,
    `first_name` VARCHAR(50) NOT NULL,
    `last_name` VARCHAR(50) NOT NULL,
    `phone` VARCHAR(20),
    `profile_image` VARCHAR(255),
    `role_id` INT UNSIGNED NOT NULL,
    `branch_id` INT UNSIGNED,
    `is_active` TINYINT(1) DEFAULT 1,
    `last_login` TIMESTAMP NULL,
    `login_attempts` INT DEFAULT 0,
    `locked_until` TIMESTAMP NULL,
    `password_reset_token` VARCHAR(100),
    `password_reset_expires` TIMESTAMP NULL,
    `two_factor_enabled` TINYINT(1) DEFAULT 0,
    `two_factor_secret` VARCHAR(255),
    `created_by` INT UNSIGNED,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_user_username` (`username`),
    UNIQUE KEY `uk_user_email` (`email`),
    KEY `idx_user_role` (`role_id`),
    KEY `idx_user_branch` (`branch_id`),
    KEY `idx_user_created_by` (`created_by`),
    CONSTRAINT `fk_user_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE RESTRICT,
    CONSTRAINT `fk_user_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_user_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Update branches foreign key after users table exists
ALTER TABLE `branches` ADD CONSTRAINT `fk_branch_manager` FOREIGN KEY (`manager_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;

-- Role permissions junction table
CREATE TABLE `role_permissions` (
    `role_id` INT UNSIGNED NOT NULL,
    `permission_id` INT UNSIGNED NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`role_id`, `permission_id`),
    KEY `idx_role_permission` (`permission_id`),
    CONSTRAINT `fk_role_permission_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_role_permission_permission` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Sessions table
CREATE TABLE `sessions` (
    `id` VARCHAR(128) NOT NULL,
    `user_id` INT UNSIGNED,
    `ip_address` VARCHAR(45),
    `user_agent` TEXT,
    `payload` TEXT NOT NULL,
    `last_activity` INT NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_session_user` (`user_id`),
    KEY `idx_session_last_activity` (`last_activity`),
    CONSTRAINT `fk_session_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- BORROWERS MANAGEMENT
-- =============================================

-- Borrowers table
CREATE TABLE `borrowers` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `borrower_code` VARCHAR(20) NOT NULL,
    `first_name` VARCHAR(50) NOT NULL,
    `last_name` VARCHAR(50) NOT NULL,
    `gender` ENUM('male', 'female', 'other') NOT NULL,
    `date_of_birth` DATE,
    `marital_status` ENUM('single', 'married', 'divorced', 'widowed'),
    `national_id` VARCHAR(50),
    `passport_number` VARCHAR(50),
    `tax_id` VARCHAR(50),
    `phone` VARCHAR(20) NOT NULL,
    `email` VARCHAR(100),
    `address` TEXT NOT NULL,
    `city` VARCHAR(50),
    `state` VARCHAR(50),
    `country_code` VARCHAR(2) DEFAULT 'US',
    `postal_code` VARCHAR(20),
    `occupation` VARCHAR(100),
    `monthly_income` DECIMAL(15, 2),
    `employment_status` ENUM('employed', 'self_employed', 'unemployed', 'retired'),
    `employer_name` VARCHAR(100),
    `employment_duration` INT,
    `bank_name` VARCHAR(100),
    `bank_account` VARCHAR(50),
    `next_of_kin_name` VARCHAR(100),
    `next_of_kin_phone` VARCHAR(20),
    `next_of_kin_relationship` VARCHAR(50),
    `branch_id` INT UNSIGNED NOT NULL,
    `loan_officer_id` INT UNSIGNED,
    `credit_score` INT DEFAULT 500,
    `risk_category` ENUM('low', 'medium', 'high') DEFAULT 'medium',
    `status` ENUM('active', 'inactive', 'pending', 'blacklisted') DEFAULT 'pending',
    `blacklist_reason` TEXT,
    `profile_image` VARCHAR(255),
    `created_by` INT UNSIGNED,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_borrower_code` (`borrower_code`),
    UNIQUE KEY `uk_borrower_national_id` (`national_id`),
    UNIQUE KEY `uk_borrower_phone` (`phone`),
    UNIQUE KEY `uk_borrower_email` (`email`),
    KEY `idx_borrower_branch` (`branch_id`),
    KEY `idx_borrower_officer` (`loan_officer_id`),
    KEY `idx_borrower_created_by` (`created_by`),
    KEY `idx_borrower_status` (`status`),
    KEY `idx_borrower_risk` (`risk_category`),
    CONSTRAINT `fk_borrower_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE RESTRICT,
    CONSTRAINT `fk_borrower_officer` FOREIGN KEY (`loan_officer_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_borrower_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Borrower credit history
CREATE TABLE `borrower_credit_history` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `borrower_id` INT UNSIGNED NOT NULL,
    `credit_score` INT NOT NULL,
    `score_date` DATE NOT NULL,
    `reason` TEXT,
    `calculated_by` INT UNSIGNED,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_credit_borrower` (`borrower_id`),
    KEY `idx_credit_date` (`score_date`),
    KEY `idx_credit_calculated_by` (`calculated_by`),
    CONSTRAINT `fk_credit_borrower` FOREIGN KEY (`borrower_id`) REFERENCES `borrowers` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_credit_calculated_by` FOREIGN KEY (`calculated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Borrower documents
CREATE TABLE `borrower_documents` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `borrower_id` INT UNSIGNED NOT NULL,
    `document_type` ENUM('national_id', 'passport', 'utility_bill', 'bank_statement', 'pay_slip', 'tax_return', 'other') NOT NULL,
    `document_name` VARCHAR(255) NOT NULL,
    `file_path` VARCHAR(255) NOT NULL,
    `file_size` INT,
    `mime_type` VARCHAR(100),
    `verified` TINYINT(1) DEFAULT 0,
    `verified_by` INT UNSIGNED,
    `verified_at` TIMESTAMP NULL,
    `notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_document_borrower` (`borrower_id`),
    KEY `idx_document_type` (`document_type`),
    KEY `idx_document_verified` (`verified`),
    CONSTRAINT `fk_document_borrower` FOREIGN KEY (`borrower_id`) REFERENCES `borrowers` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_document_verified_by` FOREIGN KEY (`verified_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- LOAN PRODUCTS AND CONFIGURATION
-- =============================================

-- Loan products table
CREATE TABLE `loan_products` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `code` VARCHAR(20) NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `description` TEXT,
    `product_type` ENUM('agriculture', 'livestock', 'equipment', 'emergency', 'education', 'business', 'other') DEFAULT 'agriculture',
    `min_amount` DECIMAL(15, 2) NOT NULL,
    `max_amount` DECIMAL(15, 2) NOT NULL,
    `interest_rate` DECIMAL(5, 2) NOT NULL,
    `interest_type` ENUM('flat', 'reducing', 'compound') DEFAULT 'reducing',
    `min_term` INT NOT NULL,
    `max_term` INT NOT NULL,
    `term_unit` ENUM('days', 'weeks', 'months', 'years') DEFAULT 'months',
    `grace_period` INT DEFAULT 0,
    `penalty_rate` DECIMAL(5, 2) DEFAULT 0,
    `penalty_type` ENUM('fixed', 'percentage') DEFAULT 'percentage',
    `requires_guarantor` TINYINT(1) DEFAULT 0,
    `min_guarantors` INT DEFAULT 0,
    `requires_collateral` TINYINT(1) DEFAULT 0,
    `collateral_coverage` DECIMAL(5, 2) DEFAULT 100,
    `processing_fee` DECIMAL(5, 2) DEFAULT 0,
    `processing_fee_type` ENUM('fixed', 'percentage') DEFAULT 'percentage',
    `insurance_required` TINYINT(1) DEFAULT 0,
    `insurance_rate` DECIMAL(5, 2) DEFAULT 0,
    `eligibility_criteria` JSON,
    `branch_id` INT UNSIGNED,
    `status` ENUM('active', 'inactive', 'archived') DEFAULT 'active',
    `created_by` INT UNSIGNED,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_product_code` (`code`),
    UNIQUE KEY `uk_product_name` (`name`),
    KEY `idx_product_branch` (`branch_id`),
    KEY `idx_product_type` (`product_type`),
    KEY `idx_product_status` (`status`),
    CONSTRAINT `fk_product_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_product_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- LOANS MANAGEMENT
-- =============================================

-- Loans table
CREATE TABLE `loans` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `loan_number` VARCHAR(20) NOT NULL,
    `borrower_id` INT UNSIGNED NOT NULL,
    `loan_product_id` INT UNSIGNED NOT NULL,
    `branch_id` INT UNSIGNED NOT NULL,
    `principal_amount` DECIMAL(15, 2) NOT NULL,
    `interest_rate` DECIMAL(5, 2) NOT NULL,
    `interest_type` ENUM('flat', 'reducing', 'compound') DEFAULT 'reducing',
    `term` INT NOT NULL,
    `term_unit` ENUM('days', 'weeks', 'months', 'years') DEFAULT 'months',
    `total_interest` DECIMAL(15, 2) NOT NULL,
    `total_amount` DECIMAL(15, 2) NOT NULL,
    `installment_amount` DECIMAL(15, 2) NOT NULL,
    `purpose` TEXT NOT NULL,
    `application_date` DATE NOT NULL,
    `approved_date` DATE,
    `approved_by` INT UNSIGNED,
    `disbursement_date` DATE,
    `disbursed_by` INT UNSIGNED,
    `first_installment_date` DATE,
    `maturity_date` DATE NOT NULL,
    `grace_period` INT DEFAULT 0,
    `penalty_rate` DECIMAL(5, 2) DEFAULT 0,
    `processing_fee` DECIMAL(15, 2) DEFAULT 0,
    `insurance_amount` DECIMAL(15, 2) DEFAULT 0,
    `collateral_required` TINYINT(1) DEFAULT 0,
    `guarantors_required` TINYINT(1) DEFAULT 0,
    `status` ENUM('pending', 'approved', 'rejected', 'disbursed', 'active', 'completed', 'defaulted', 'written_off') DEFAULT 'pending',
    `rejection_reason` TEXT,
    `notes` TEXT,
    `created_by` INT UNSIGNED,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_loan_number` (`loan_number`),
    KEY `idx_loan_borrower` (`borrower_id`),
    KEY `idx_loan_product` (`loan_product_id`),
    KEY `idx_loan_branch` (`branch_id`),
    KEY `idx_loan_status` (`status`),
    KEY `idx_loan_application_date` (`application_date`),
    KEY `idx_loan_maturity_date` (`maturity_date`),
    CONSTRAINT `fk_loan_borrower` FOREIGN KEY (`borrower_id`) REFERENCES `borrowers` (`id`) ON DELETE RESTRICT,
    CONSTRAINT `fk_loan_product` FOREIGN KEY (`loan_product_id`) REFERENCES `loan_products` (`id`) ON DELETE RESTRICT,
    CONSTRAINT `fk_loan_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE RESTRICT,
    CONSTRAINT `fk_loan_approved_by` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_loan_disbursed_by` FOREIGN KEY (`disbursed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_loan_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Loan installments table
CREATE TABLE `loan_installments` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `loan_id` INT UNSIGNED NOT NULL,
    `installment_number` INT NOT NULL,
    `due_date` DATE NOT NULL,
    `principal_amount` DECIMAL(15, 2) NOT NULL,
    `interest_amount` DECIMAL(15, 2) NOT NULL,
    `amount_due` DECIMAL(15, 2) NOT NULL,
    `paid_amount` DECIMAL(15, 2) DEFAULT 0,
    `paid_date` DATE,
    `penalty_amount` DECIMAL(15, 2) DEFAULT 0,
    `paid_penalty` DECIMAL(15, 2) DEFAULT 0,
    `status` ENUM('pending', 'paid', 'partial', 'overdue', 'waived') DEFAULT 'pending',
    `days_overdue` INT DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_loan_installment` (`loan_id`, `installment_number`),
    KEY `idx_installment_loan` (`loan_id`),
    KEY `idx_installment_due_date` (`due_date`),
    KEY `idx_installment_status` (`status`),
    CONSTRAINT `fk_installment_loan` FOREIGN KEY (`loan_id`) REFERENCES `loans` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Loan guarantors table
CREATE TABLE `loan_guarantors` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `loan_id` INT UNSIGNED NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `phone` VARCHAR(20) NOT NULL,
    `email` VARCHAR(100),
    `relationship` VARCHAR(50) NOT NULL,
    `id_number` VARCHAR(50),
    `address` TEXT,
    `occupation` VARCHAR(100),
    `monthly_income` DECIMAL(15, 2),
    `guarantee_amount` DECIMAL(15, 2) NOT NULL,
    `status` ENUM('active', 'released') DEFAULT 'active',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_guarantor_loan` (`loan_id`),
    CONSTRAINT `fk_guarantor_loan` FOREIGN KEY (`loan_id`) REFERENCES `loans` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Loan collateral table
CREATE TABLE `loan_collateral` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `loan_id` INT UNSIGNED NOT NULL,
    `collateral_type` ENUM('land', 'vehicle', 'equipment', 'livestock', 'building', 'jewelry', 'other') NOT NULL,
    `description` TEXT NOT NULL,
    `estimated_value` DECIMAL(15, 2) NOT NULL,
    `location` TEXT,
    `document_path` VARCHAR(255),
    `condition` ENUM('excellent', 'good', 'fair', 'poor') DEFAULT 'good',
    `status` ENUM('active', 'released', 'seized') DEFAULT 'active',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_collateral_loan` (`loan_id`),
    CONSTRAINT `fk_collateral_loan` FOREIGN KEY (`loan_id`) REFERENCES `loans` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- REPAYMENTS AND TRANSACTIONS
-- =============================================

-- Payment methods table
CREATE TABLE `payment_methods` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,
    `code` VARCHAR(20) NOT NULL,
    `description` TEXT,
    `is_active` TINYINT(1) DEFAULT 1,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_payment_method_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Repayments table
CREATE TABLE `repayments` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `repayment_number` VARCHAR(20) NOT NULL,
    `loan_id` INT UNSIGNED NOT NULL,
    `borrower_id` INT UNSIGNED NOT NULL,
    `installment_id` INT UNSIGNED,
    `amount` DECIMAL(15, 2) NOT NULL,
    `principal_amount` DECIMAL(15, 2) NOT NULL,
    `interest_amount` DECIMAL(15, 2) NOT NULL,
    `penalty_amount` DECIMAL(15, 2) DEFAULT 0,
    `payment_method_id` INT UNSIGNED NOT NULL,
    `payment_reference` VARCHAR(100),
    `received_by` INT UNSIGNED NOT NULL,
    `branch_id` INT UNSIGNED,
    `notes` TEXT,
    `status` ENUM('pending', 'completed', 'reversed') DEFAULT 'completed',
    `payment_date` DATETIME NOT NULL,
    `reversed_by` INT UNSIGNED,
    `reversed_date` DATETIME,
    `reversal_reason` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_repayment_number` (`repayment_number`),
    KEY `idx_repayment_loan` (`loan_id`),
    KEY `idx_repayment_borrower` (`borrower_id`),
    KEY `idx_repayment_installment` (`installment_id`),
    KEY `idx_repayment_payment_method` (`payment_method_id`),
    KEY `idx_repayment_received_by` (`received_by`),
    KEY `idx_repayment_payment_date` (`payment_date`),
    CONSTRAINT `fk_repayment_loan` FOREIGN KEY (`loan_id`) REFERENCES `loans` (`id`) ON DELETE RESTRICT,
    CONSTRAINT `fk_repayment_borrower` FOREIGN KEY (`borrower_id`) REFERENCES `borrowers` (`id`) ON DELETE RESTRICT,
    CONSTRAINT `fk_repayment_installment` FOREIGN KEY (`installment_id`) REFERENCES `loan_installments` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_repayment_payment_method` FOREIGN KEY (`payment_method_id`) REFERENCES `payment_methods` (`id`) ON DELETE RESTRICT,
    CONSTRAINT `fk_repayment_received_by` FOREIGN KEY (`received_by`) REFERENCES `users` (`id`) ON DELETE RESTRICT,
    CONSTRAINT `fk_repayment_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_repayment_reversed_by` FOREIGN KEY (`reversed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Transactions table (for all financial transactions)
CREATE TABLE `transactions` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `transaction_number` VARCHAR(20) NOT NULL,
    `loan_id` INT UNSIGNED,
    `borrower_id` INT UNSIGNED,
    `transaction_type` ENUM('disbursement', 'repayment', 'fee', 'penalty', 'interest', 'adjustment', 'refund', 'other') NOT NULL,
    `amount` DECIMAL(15, 2) NOT NULL,
    `description` TEXT,
    `payment_method_id` INT UNSIGNED,
    `reference` VARCHAR(100),
    `status` ENUM('pending', 'completed', 'failed', 'cancelled') DEFAULT 'completed',
    `created_by` INT UNSIGNED,
    `branch_id` INT UNSIGNED,
    `transaction_date` DATETIME NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_transaction_number` (`transaction_number`),
    KEY `idx_transaction_loan` (`loan_id`),
    KEY `idx_transaction_borrower` (`borrower_id`),
    KEY `idx_transaction_type` (`transaction_type`),
    KEY `idx_transaction_payment_method` (`payment_method_id`),
    KEY `idx_transaction_date` (`transaction_date`),
    CONSTRAINT `fk_transaction_loan` FOREIGN KEY (`loan_id`) REFERENCES `loans` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_transaction_borrower` FOREIGN KEY (`borrower_id`) REFERENCES `borrowers` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_transaction_payment_method` FOREIGN KEY (`payment_method_id`) REFERENCES `payment_methods` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_transaction_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_transaction_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- SAVINGS AND ACCOUNTS
-- =============================================

-- Savings products table
CREATE TABLE `savings_products` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `code` VARCHAR(20) NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `description` TEXT,
    `interest_rate` DECIMAL(5, 2) NOT NULL,
    `min_balance` DECIMAL(15, 2) DEFAULT 0,
    `max_balance` DECIMAL(15, 2),
    `interest_calculation` ENUM('daily', 'monthly', 'quarterly', 'yearly') DEFAULT 'monthly',
    `interest_posting` ENUM('monthly', 'quarterly', 'yearly', 'at_maturity') DEFAULT 'monthly',
    `withdrawal_fee` DECIMAL(5, 2) DEFAULT 0,
    `monthly_fee` DECIMAL(5, 2) DEFAULT 0,
    `opening_fee` DECIMAL(5, 2) DEFAULT 0,
    `status` ENUM('active', 'inactive') DEFAULT 'active',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_savings_product_code` (`code`),
    UNIQUE KEY `uk_savings_product_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Savings accounts table
CREATE TABLE `savings_accounts` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `account_number` VARCHAR(20) NOT NULL,
    `borrower_id` INT UNSIGNED NOT NULL,
    `product_id` INT UNSIGNED NOT NULL,
    `account_name` VARCHAR(100) NOT NULL,
    `balance` DECIMAL(15, 2) DEFAULT 0,
    `available_balance` DECIMAL(15, 2) DEFAULT 0,
    `interest_accrued` DECIMAL(15, 2) DEFAULT 0,
    `last_interest_date` DATE,
    `status` ENUM('active', 'inactive', 'dormant', 'closed') DEFAULT 'active',
    `opened_date` DATE NOT NULL,
    `closed_date` DATE,
    `closed_by` INT UNSIGNED,
    `created_by` INT UNSIGNED,
    `branch_id` INT UNSIGNED,
    `notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_savings_account_number` (`account_number`),
    KEY `idx_savings_borrower` (`borrower_id`),
    KEY `idx_savings_product` (`product_id`),
    KEY `idx_savings_branch` (`branch_id`),
    KEY `idx_savings_status` (`status`),
    CONSTRAINT `fk_savings_borrower` FOREIGN KEY (`borrower_id`) REFERENCES `borrowers` (`id`) ON DELETE RESTRICT,
    CONSTRAINT `fk_savings_product` FOREIGN KEY (`product_id`) REFERENCES `savings_products` (`id`) ON DELETE RESTRICT,
    CONSTRAINT `fk_savings_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_savings_closed_by` FOREIGN KEY (`closed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_savings_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Savings transactions table
CREATE TABLE `savings_transactions` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `transaction_number` VARCHAR(20) NOT NULL,
    `account_id` INT UNSIGNED NOT NULL,
    `transaction_type` ENUM('deposit', 'withdrawal', 'interest', 'fee', 'transfer', 'adjustment') NOT NULL,
    `amount` DECIMAL(15, 2) NOT NULL,
    `balance_before` DECIMAL(15, 2) NOT NULL,
    `balance_after` DECIMAL(15, 2) NOT NULL,
    `description` TEXT,
    `payment_method_id` INT UNSIGNED,
    `reference` VARCHAR(100),
    `performed_by` INT UNSIGNED NOT NULL,
    `branch_id` INT UNSIGNED,
    `transaction_date` DATETIME NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_savings_transaction_number` (`transaction_number`),
    KEY `idx_savings_transaction_account` (`account_id`),
    KEY `idx_savings_transaction_type` (`transaction_type`),
    KEY `idx_savings_transaction_date` (`transaction_date`),
    CONSTRAINT `fk_savings_transaction_account` FOREIGN KEY (`account_id`) REFERENCES `savings_accounts` (`id`) ON DELETE RESTRICT,
    CONSTRAINT `fk_savings_transaction_payment_method` FOREIGN KEY (`payment_method_id`) REFERENCES `payment_methods` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_savings_transaction_performed_by` FOREIGN KEY (`performed_by`) REFERENCES `users` (`id`) ON DELETE RESTRICT,
    CONSTRAINT `fk_savings_transaction_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- ACCOUNTING AND FINANCIAL MANAGEMENT
-- =============================================

-- Chart of accounts
CREATE TABLE `chart_of_accounts` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `code` VARCHAR(20) NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `account_type` ENUM('asset', 'liability', 'equity', 'income', 'expense') NOT NULL,
    `subtype` VARCHAR(50),
    `description` TEXT,
    `parent_id` INT UNSIGNED,
    `balance` DECIMAL(15, 2) DEFAULT 0,
    `status` ENUM('active', 'inactive') DEFAULT 'active',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_account_code` (`code`),
    KEY `idx_account_parent` (`parent_id`),
    KEY `idx_account_type` (`account_type`),
    CONSTRAINT `fk_account_parent` FOREIGN KEY (`parent_id`) REFERENCES `chart_of_accounts` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Journal entries
CREATE TABLE `journal_entries` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `entry_number` VARCHAR(20) NOT NULL,
    `entry_date` DATE NOT NULL,
    `description` TEXT,
    `reference_type` ENUM('loan', 'repayment', 'savings', 'expense', 'income', 'adjustment', 'other') DEFAULT 'other',
    `reference_id` INT UNSIGNED,
    `total_debit` DECIMAL(15, 2) NOT NULL,
    `    `total_credit` DECIMAL(15, 2) NOT NULL,
    `status` ENUM('draft', 'posted', 'reversed') DEFAULT 'draft',
    `posted_by` INT UNSIGNED,
    `posted_date` DATETIME,
    `created_by` INT UNSIGNED NOT NULL,
    `branch_id` INT UNSIGNED,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_entry_number` (`entry_number`),
    KEY `idx_entry_date` (`entry_date`),
    KEY `idx_entry_reference` (`reference_type`, `reference_id`),
    KEY `idx_entry_created_by` (`created_by`),
    KEY `idx_entry_branch` (`branch_id`),
    CONSTRAINT `fk_entry_posted_by` FOREIGN KEY (`posted_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_entry_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE RESTRICT,
    CONSTRAINT `fk_entry_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Journal items
CREATE TABLE `journal_items` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `journal_entry_id` INT UNSIGNED NOT NULL,
    `account_id` INT UNSIGNED NOT NULL,
    `debit` DECIMAL(15, 2) DEFAULT 0,
    `credit` DECIMAL(15, 2) DEFAULT 0,
    `description` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_item_journal` (`journal_entry_id`),
    KEY `idx_item_account` (`account_id`),
    CONSTRAINT `fk_item_journal` FOREIGN KEY (`journal_entry_id`) REFERENCES `journal_entries` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_item_account` FOREIGN KEY (`account_id`) REFERENCES `chart_of_accounts` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Expense categories
CREATE TABLE `expense_categories` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `description` TEXT,
    `status` ENUM('active', 'inactive') DEFAULT 'active',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_expense_category_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Expenses
CREATE TABLE `expenses` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `expense_number` VARCHAR(20) NOT NULL,
    `category_id` INT UNSIGNED NOT NULL,
    `amount` DECIMAL(15, 2) NOT NULL,
    `description` TEXT,
    `payment_method_id` INT UNSIGNED,
    `reference` VARCHAR(100),
    `paid_to` VARCHAR(100),
    `approved_by` INT UNSIGNED,
    `paid_by` INT UNSIGNED NOT NULL,
    `branch_id` INT UNSIGNED,
    `expense_date` DATE NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_expense_number` (`expense_number`),
    KEY `idx_expense_category` (`category_id`),
    KEY `idx_expense_payment_method` (`payment_method_id`),
    KEY `idx_expense_date` (`expense_date`),
    KEY `idx_expense_branch` (`branch_id`),
    CONSTRAINT `fk_expense_category` FOREIGN KEY (`category_id`) REFERENCES `expense_categories` (`id`) ON DELETE RESTRICT,
    CONSTRAINT `fk_expense_payment_method` FOREIGN KEY (`payment_method_id`) REFERENCES `payment_methods` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_expense_approved_by` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_expense_paid_by` FOREIGN KEY (`paid_by`) REFERENCES `users` (`id`) ON DELETE RESTRICT,
    CONSTRAINT `fk_expense_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- SYSTEM TABLES
-- =============================================

-- Audit logs
CREATE TABLE `audit_logs` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INT UNSIGNED,
    `action` VARCHAR(100) NOT NULL,
    `entity_type` VARCHAR(50),
    `entity_id` INT UNSIGNED,
    `old_values` JSON,
    `new_values` JSON,
    `description` TEXT,
    `ip_address` VARCHAR(45),
    `user_agent` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_audit_user` (`user_id`),
    KEY `idx_audit_action` (`action`),
    KEY `idx_audit_entity` (`entity_type`, `entity_id`),
    KEY `idx_audit_created_at` (`created_at`),
    CONSTRAINT `fk_audit_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Notifications
CREATE TABLE `notifications` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INT UNSIGNED NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `message` TEXT NOT NULL,
    `type` ENUM('info', 'success', 'warning', 'error', 'system') DEFAULT 'info',
    `is_read` TINYINT(1) DEFAULT 0,
    `action_url` VARCHAR(255),
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_notification_user` (`user_id`),
    KEY `idx_notification_read` (`is_read`),
    KEY `idx_notification_created_at` (`created_at`),
    CONSTRAINT `fk_notification_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Settings
CREATE TABLE `settings` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `setting_key` VARCHAR(100) NOT NULL,
    `setting_value` TEXT,
    `setting_group` VARCHAR(50) DEFAULT 'general',
    `setting_type` ENUM('text', 'number', 'textarea', 'select', 'checkbox', 'radio', 'password', 'email') DEFAULT 'text',
    `options` TEXT,
    `description` TEXT,
    `is_public` TINYINT(1) DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_setting_key` (`setting_key`),
    KEY `idx_setting_group` (`setting_group`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Email templates
CREATE TABLE `email_templates` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `slug` VARCHAR(100) NOT NULL,
    `subject` VARCHAR(255) NOT NULL,
    `body` TEXT NOT NULL,
    `variables` TEXT,
    `is_active` TINYINT(1) DEFAULT 1,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_email_template_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- SMS templates
CREATE TABLE `sms_templates` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `slug` VARCHAR(100) NOT NULL,
    `message` TEXT NOT NULL,
    `variables` TEXT,
    `is_active` TINYINT(1) DEFAULT 1,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_sms_template_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Plugins
CREATE TABLE `plugins` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `slug` VARCHAR(100) NOT NULL,
    `version` VARCHAR(20) NOT NULL,
    `author` VARCHAR(100),
    `description` TEXT,
    `plugin_path` VARCHAR(255) NOT NULL,
    `settings` JSON,
    `is_active` TINYINT(1) DEFAULT 0,
    `is_system` TINYINT(1) DEFAULT 0,
    `installed_at` TIMESTAMP NULL,
    `activated_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_plugin_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Plugin hooks
CREATE TABLE `plugin_hooks` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `plugin_id` INT UNSIGNED NOT NULL,
    `hook_name` VARCHAR(100) NOT NULL,
    `callback` VARCHAR(255) NOT NULL,
    `priority` INT DEFAULT 10,
    `is_active` TINYINT(1) DEFAULT 1,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_hook_plugin` (`plugin_id`),
    KEY `idx_hook_name` (`hook_name`),
    CONSTRAINT `fk_hook_plugin` FOREIGN KEY (`plugin_id`) REFERENCES `plugins` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- ADDITIONAL FEATURES TABLES
-- =============================================

-- Countries
CREATE TABLE `countries` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `code` VARCHAR(2) NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `phone_code` VARCHAR(10),
    `currency_code` VARCHAR(3),
    `currency_symbol` VARCHAR(10),
    `status` ENUM('active', 'inactive') DEFAULT 'active',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_country_code` (`code`),
    UNIQUE KEY `uk_country_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Currencies
CREATE TABLE `currencies` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `code` VARCHAR(3) NOT NULL,
    `name` VARCHAR(50) NOT NULL,
    `symbol` VARCHAR(10),
    `exchange_rate` DECIMAL(10, 4) DEFAULT 1.0000,
    `is_default` TINYINT(1) DEFAULT 0,
    `status` ENUM('active', 'inactive') DEFAULT 'active',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_currency_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Holidays
CREATE TABLE `holidays` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `date` DATE NOT NULL,
    `year` INT,
    `recurring` TINYINT(1) DEFAULT 0,
    `branch_id` INT UNSIGNED,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_holiday_date` (`date`),
    KEY `idx_holiday_branch` (`branch_id`),
    CONSTRAINT `fk_holiday_branch` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Reports
CREATE TABLE `reports` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `slug` VARCHAR(100) NOT NULL,
    `description` TEXT,
    `query` TEXT NOT NULL,
    `parameters` JSON,
    `category` VARCHAR(50) DEFAULT 'general',
    `is_active` TINYINT(1) DEFAULT 1,
    `created_by` INT UNSIGNED,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_report_slug` (`slug`),
    KEY `idx_report_category` (`category`),
    CONSTRAINT `fk_report_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Document templates
CREATE TABLE `document_templates` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `type` ENUM('loan_agreement', 'receipt', 'statement', 'certificate', 'other') DEFAULT 'other',
    `content` TEXT NOT NULL,
    `variables` TEXT,
    `is_active` TINYINT(1) DEFAULT 1,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Documents
CREATE TABLE `documents` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `document_number` VARCHAR(20) NOT NULL,
    `borrower_id` INT UNSIGNED,
    `loan_id` INT UNSIGNED,
    `type` VARCHAR(50) NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `file_path` VARCHAR(255) NOT NULL,
    `file_size` INT,
    `file_type` VARCHAR(50),
    `uploaded_by` INT UNSIGNED NOT NULL,
    `description` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_document_number` (`document_number`),
    KEY `idx_document_borrower` (`borrower_id`),
    KEY `idx_document_loan` (`loan_id`),
    KEY `idx_document_type` (`type`),
    CONSTRAINT `fk_document_borrower` FOREIGN KEY (`borrower_id`) REFERENCES `borrowers` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_document_loan` FOREIGN KEY (`loan_id`) REFERENCES `loans` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_document_uploaded_by` FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Backups
CREATE TABLE `backups` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `filename` VARCHAR(255) NOT NULL,
    `size` INT,
    `type` ENUM('full', 'partial', 'auto', 'manual') DEFAULT 'manual',
    `created_by` INT UNSIGNED,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_backup_created_at` (`created_at`),
    CONSTRAINT `fk_backup_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- API keys
CREATE TABLE `api_keys` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `api_key` VARCHAR(64) NOT NULL,
    `secret_key` VARCHAR(64) NOT NULL,
    `permissions` JSON,
    `expires_at` DATETIME,
    `last_used` DATETIME,
    `status` ENUM('active', 'inactive', 'expired') DEFAULT 'active',
    `created_by` INT UNSIGNED,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_api_key` (`api_key`),
    CONSTRAINT `fk_api_key_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Queued jobs
CREATE TABLE `queued_jobs` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `queue` VARCHAR(50