-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Jun 26, 2026 at 09:04 AM
-- Server version: 10.4.32-MariaDB
-- PHP Version: 8.0.30

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `loan_management_system`
--

DELIMITER $$
--
-- Functions
--
CREATE DEFINER=`root`@`localhost` FUNCTION `fn_get_client_balance` (`p_client_id` INT) RETURNS DECIMAL(15,2) DETERMINISTIC BEGIN
    DECLARE v_balance DECIMAL(15,2);
    
    SELECT COALESCE(SUM(l.total_repayable - COALESCE(
        (SELECT SUM(amount) FROM loan_repayments 
         WHERE loan_id = l.id AND status = 'completed'), 0
    )), 0) INTO v_balance
    FROM loans l
    WHERE l.client_id = p_client_id 
      AND l.status IN ('active', 'disbursed');
    
    RETURN v_balance;
END$$

CREATE DEFINER=`root`@`localhost` FUNCTION `fn_get_document_completion` (`p_client_id` INT) RETURNS DECIMAL(5,2) DETERMINISTIC BEGIN
    DECLARE v_total_required INT;
    DECLARE v_uploaded INT;
    DECLARE v_percentage DECIMAL(5,2);
    
    SELECT COUNT(*) INTO v_total_required 
    FROM document_types 
    WHERE is_mandatory = 1 AND status = 'active';
    
    SELECT COUNT(DISTINCT cd.document_type_id) INTO v_uploaded
    FROM client_documents cd
    JOIN document_types dt ON cd.document_type_id = dt.id
    WHERE cd.client_id = p_client_id 
      AND cd.is_active = 1
      AND dt.is_mandatory = 1;
    
    IF v_total_required > 0 THEN
        SET v_percentage = (v_uploaded / v_total_required) * 100;
    ELSE
        SET v_percentage = 0;
    END IF;
    
    RETURN v_percentage;
END$$

DELIMITER ;

-- --------------------------------------------------------

--
-- Table structure for table `audit_trail`
--

CREATE TABLE `audit_trail` (
  `id` int(11) NOT NULL,
  `table_name` varchar(50) NOT NULL,
  `record_id` int(11) NOT NULL,
  `action` enum('INSERT','UPDATE','DELETE') NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `old_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`old_data`)),
  `new_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`new_data`)),
  `ip_address` varchar(45) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `clients`
--

CREATE TABLE `clients` (
  `id` int(11) NOT NULL,
  `client_code` varchar(50) NOT NULL,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `middle_name` varchar(50) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `phone` varchar(20) NOT NULL,
  `alternative_phone` varchar(20) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `city` varchar(50) DEFAULT NULL,
  `state` varchar(50) DEFAULT NULL,
  `postal_code` varchar(20) DEFAULT NULL,
  `country` varchar(50) DEFAULT 'Kenya',
  `date_of_birth` date DEFAULT NULL,
  `gender` enum('Male','Female','Other') DEFAULT NULL,
  `marital_status` enum('Single','Married','Divorced','Widowed') DEFAULT NULL,
  `occupation` varchar(100) DEFAULT NULL,
  `employer` varchar(100) DEFAULT NULL,
  `monthly_income` decimal(15,2) DEFAULT NULL,
  `national_id` varchar(50) DEFAULT NULL,
  `passport_photo` varchar(255) DEFAULT NULL,
  `id_document` varchar(255) DEFAULT NULL,
  `status` enum('active','inactive','blacklisted','deceased') DEFAULT 'active',
  `blacklist_reason` text DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `documents_submitted` tinyint(1) DEFAULT 0,
  `documents_verified` tinyint(1) DEFAULT 0,
  `document_submission_date` datetime DEFAULT NULL,
  `document_verification_date` datetime DEFAULT NULL,
  `onboarding_status` enum('pending','documents_submitted','under_review','verified','rejected','incomplete') DEFAULT 'pending',
  `id_type` enum('national_id','passport') DEFAULT 'national_id',
  `id_number` varchar(50) DEFAULT NULL,
  `id_expiry_date` date DEFAULT NULL,
  `residence_proof_date` date DEFAULT NULL,
  `income_proof_date` date DEFAULT NULL,
  `id_verified` tinyint(1) DEFAULT 0,
  `id_verification_date` datetime DEFAULT NULL,
  `id_verified_by` int(11) DEFAULT NULL,
  `address_verified` tinyint(1) DEFAULT 0,
  `address_verification_date` datetime DEFAULT NULL,
  `address_verified_by` int(11) DEFAULT NULL,
  `income_verified` tinyint(1) DEFAULT 0,
  `income_verification_date` datetime DEFAULT NULL,
  `income_verified_by` int(11) DEFAULT NULL,
  `kyc_completed` tinyint(1) DEFAULT 0,
  `kyc_completion_date` datetime DEFAULT NULL,
  `last_verification_date` datetime DEFAULT NULL,
  `verification_risk_score` decimal(5,2) DEFAULT NULL,
  `risk_level` enum('low','medium','high','critical') DEFAULT 'low'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `client_documents`
--

CREATE TABLE `client_documents` (
  `id` int(11) NOT NULL,
  `client_id` int(11) NOT NULL,
  `document_type_id` int(11) NOT NULL,
  `document_number` varchar(100) DEFAULT NULL,
  `file_name` varchar(255) NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `file_size` int(11) DEFAULT NULL,
  `file_type` varchar(50) DEFAULT NULL,
  `uploaded_by` int(11) DEFAULT NULL,
  `upload_date` timestamp NOT NULL DEFAULT current_timestamp(),
  `expiry_date` date DEFAULT NULL,
  `issuing_authority` varchar(100) DEFAULT NULL,
  `verification_status` enum('pending','verified','rejected','expired') DEFAULT 'pending',
  `verification_notes` text DEFAULT NULL,
  `verified_by` int(11) DEFAULT NULL,
  `verification_date` datetime DEFAULT NULL,
  `rejection_reason` text DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `client_guarantors`
--

CREATE TABLE `client_guarantors` (
  `id` int(11) NOT NULL,
  `client_id` int(11) NOT NULL,
  `guarantor_name` varchar(100) NOT NULL,
  `guarantor_phone` varchar(20) NOT NULL,
  `guarantor_email` varchar(100) DEFAULT NULL,
  `guarantor_address` text DEFAULT NULL,
  `relationship` varchar(50) DEFAULT NULL,
  `employer` varchar(100) DEFAULT NULL,
  `monthly_income` decimal(15,2) DEFAULT NULL,
  `national_id` varchar(50) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `document_types`
--

CREATE TABLE `document_types` (
  `id` int(11) NOT NULL,
  `document_code` varchar(50) NOT NULL,
  `document_name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `is_mandatory` tinyint(1) DEFAULT 0,
  `requires_expiry` tinyint(1) DEFAULT 0,
  `status` enum('active','inactive') DEFAULT 'active',
  `display_order` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `document_types`
--

INSERT INTO `document_types` (`id`, `document_code`, `document_name`, `description`, `is_mandatory`, `requires_expiry`, `status`, `display_order`, `created_at`) VALUES
(1, 'ID', 'National ID', 'Government issued National ID Card', 1, 1, 'active', 1, '2026-06-26 07:01:51'),
(2, 'PASSPORT', 'Passport', 'Valid International Passport', 0, 1, 'active', 2, '2026-06-26 07:01:51'),
(3, 'POR', 'Proof of Residence', 'Utility Bill, Bank Statement, or Lease Agreement (not older than 3 months)', 1, 1, 'active', 3, '2026-06-26 07:01:51'),
(4, 'POI', 'Proof of Income', 'Payslips (last 3 months), Bank Statements, or Income Tax Returns', 1, 0, 'active', 4, '2026-06-26 07:01:51'),
(5, 'OTHER', 'Other Documents', 'Any additional supporting documents', 0, 0, 'active', 5, '2026-06-26 07:01:51');

-- --------------------------------------------------------

--
-- Table structure for table `document_verification_history`
--

CREATE TABLE `document_verification_history` (
  `id` int(11) NOT NULL,
  `document_id` int(11) NOT NULL,
  `previous_status` enum('pending','verified','rejected','expired') DEFAULT NULL,
  `new_status` enum('pending','verified','rejected','expired') DEFAULT NULL,
  `changed_by` int(11) NOT NULL,
  `change_date` datetime DEFAULT current_timestamp(),
  `comments` text DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `fees_and_charges`
--

CREATE TABLE `fees_and_charges` (
  `id` int(11) NOT NULL,
  `loan_id` int(11) NOT NULL,
  `fee_type` enum('processing','late_payment','default','maintenance','other') NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `description` text DEFAULT NULL,
  `charge_date` date NOT NULL,
  `due_date` date DEFAULT NULL,
  `status` enum('pending','paid','waived') DEFAULT 'pending',
  `waived_by` int(11) DEFAULT NULL,
  `waiver_reason` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `loans`
--

CREATE TABLE `loans` (
  `id` int(11) NOT NULL,
  `loan_number` varchar(50) NOT NULL,
  `client_id` int(11) NOT NULL,
  `product_id` int(11) DEFAULT NULL,
  `amount` decimal(15,2) NOT NULL,
  `interest_rate` decimal(5,2) NOT NULL,
  `term_months` int(11) NOT NULL,
  `total_repayable` decimal(15,2) DEFAULT NULL,
  `monthly_payment` decimal(15,2) DEFAULT NULL,
  `total_interest` decimal(15,2) DEFAULT NULL,
  `processing_fee` decimal(15,2) DEFAULT 0.00,
  `purpose` text DEFAULT NULL,
  `application_date` date NOT NULL,
  `expected_disbursement_date` date DEFAULT NULL,
  `approval_date` date DEFAULT NULL,
  `disbursement_date` date DEFAULT NULL,
  `first_payment_date` date DEFAULT NULL,
  `maturity_date` date DEFAULT NULL,
  `status` enum('draft','pending','under_review','approved','rejected','disbursed','active','completed','defaulted','written_off') DEFAULT 'draft',
  `approval_notes` text DEFAULT NULL,
  `rejection_reason` text DEFAULT NULL,
  `approved_by` int(11) DEFAULT NULL,
  `disbursed_by` int(11) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `loan_approvals`
--

CREATE TABLE `loan_approvals` (
  `id` int(11) NOT NULL,
  `loan_id` int(11) NOT NULL,
  `approver_id` int(11) NOT NULL,
  `approval_level` int(11) DEFAULT 1,
  `status` enum('pending','approved','rejected','returned') DEFAULT 'pending',
  `comments` text DEFAULT NULL,
  `approval_date` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `loan_collaterals`
--

CREATE TABLE `loan_collaterals` (
  `id` int(11) NOT NULL,
  `loan_id` int(11) NOT NULL,
  `collateral_type` enum('land','vehicle','building','equipment','savings','other') NOT NULL,
  `description` text NOT NULL,
  `estimated_value` decimal(15,2) NOT NULL,
  `appraised_value` decimal(15,2) DEFAULT NULL,
  `location` text DEFAULT NULL,
  `ownership_document` varchar(255) DEFAULT NULL,
  `verification_status` enum('pending','verified','rejected') DEFAULT 'pending',
  `verified_by` int(11) DEFAULT NULL,
  `verification_date` date DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `loan_disbursements`
--

CREATE TABLE `loan_disbursements` (
  `id` int(11) NOT NULL,
  `loan_id` int(11) NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `disbursement_date` date NOT NULL,
  `disbursement_method` enum('bank_transfer','cash','cheque','mobile_money') NOT NULL,
  `bank_name` varchar(100) DEFAULT NULL,
  `bank_branch` varchar(100) DEFAULT NULL,
  `account_number` varchar(50) DEFAULT NULL,
  `account_name` varchar(100) DEFAULT NULL,
  `cheque_number` varchar(50) DEFAULT NULL,
  `mobile_number` varchar(20) DEFAULT NULL,
  `reference_number` varchar(100) DEFAULT NULL,
  `status` enum('pending','processed','completed','failed','cancelled') DEFAULT 'pending',
  `processed_by` int(11) DEFAULT NULL,
  `authorized_by` int(11) DEFAULT NULL,
  `authorization_date` date DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `loan_products`
--

CREATE TABLE `loan_products` (
  `id` int(11) NOT NULL,
  `product_code` varchar(50) NOT NULL,
  `product_name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `min_amount` decimal(15,2) NOT NULL,
  `max_amount` decimal(15,2) NOT NULL,
  `default_interest_rate` decimal(5,2) NOT NULL,
  `default_term_months` int(11) NOT NULL,
  `processing_fee` decimal(15,2) DEFAULT 0.00,
  `late_penalty_rate` decimal(5,2) DEFAULT 5.00,
  `early_repayment_discount` decimal(5,2) DEFAULT 0.00,
  `requires_guarantor` tinyint(1) DEFAULT 0,
  `requires_collateral` tinyint(1) DEFAULT 0,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `loan_products`
--

INSERT INTO `loan_products` (`id`, `product_code`, `product_name`, `description`, `min_amount`, `max_amount`, `default_interest_rate`, `default_term_months`, `processing_fee`, `late_penalty_rate`, `early_repayment_discount`, `requires_guarantor`, `requires_collateral`, `status`, `created_by`, `created_at`, `updated_at`) VALUES
(1, 'P001', 'Personal Loan', 'Personal loans for individuals', 1000.00, 500000.00, 12.00, 12, 500.00, 5.00, 0.00, 0, 0, 'active', NULL, '2026-06-26 07:01:51', '2026-06-26 07:01:51'),
(2, 'P002', 'Business Loan', 'Loans for small businesses', 50000.00, 5000000.00, 15.00, 24, 1000.00, 5.00, 0.00, 1, 0, 'active', NULL, '2026-06-26 07:01:51', '2026-06-26 07:01:51'),
(3, 'P003', 'Emergency Loan', 'Quick emergency loans', 500.00, 100000.00, 18.00, 6, 200.00, 10.00, 0.00, 0, 0, 'active', NULL, '2026-06-26 07:01:51', '2026-06-26 07:01:51'),
(4, 'P004', 'Asset Finance', 'Financing for assets and equipment', 100000.00, 10000000.00, 10.00, 36, 2000.00, 5.00, 0.00, 1, 0, 'active', NULL, '2026-06-26 07:01:51', '2026-06-26 07:01:51');

-- --------------------------------------------------------

--
-- Table structure for table `loan_repayments`
--

CREATE TABLE `loan_repayments` (
  `id` int(11) NOT NULL,
  `loan_id` int(11) NOT NULL,
  `payment_number` int(11) DEFAULT NULL,
  `amount` decimal(15,2) NOT NULL,
  `principal_paid` decimal(15,2) DEFAULT NULL,
  `interest_paid` decimal(15,2) DEFAULT NULL,
  `penalty_paid` decimal(15,2) DEFAULT 0.00,
  `payment_date` date NOT NULL,
  `due_date` date NOT NULL,
  `payment_method` enum('cash','bank_transfer','mobile_money','cheque','standing_order') NOT NULL,
  `reference_number` varchar(100) DEFAULT NULL,
  `transaction_id` varchar(100) DEFAULT NULL,
  `receipt_number` varchar(50) DEFAULT NULL,
  `status` enum('pending','completed','failed','reversed') DEFAULT 'pending',
  `received_by` int(11) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Triggers `loan_repayments`
--
DELIMITER $$
CREATE TRIGGER `update_loan_status_on_repayment` AFTER INSERT ON `loan_repayments` FOR EACH ROW BEGIN
    DECLARE total_paid DECIMAL(15,2);
    DECLARE total_due DECIMAL(15,2);
    
    SELECT SUM(amount) INTO total_paid 
    FROM loan_repayments 
    WHERE loan_id = NEW.loan_id AND status = 'completed';
    
    SELECT total_repayable INTO total_due 
    FROM loans 
    WHERE id = NEW.loan_id;
    
    IF total_paid >= total_due THEN
        UPDATE loans 
        SET status = 'completed', 
            completion_date = CURDATE() 
        WHERE id = NEW.loan_id;
    END IF;
END
$$
DELIMITER ;

-- --------------------------------------------------------

--
-- Table structure for table `loan_repayment_schedules`
--

CREATE TABLE `loan_repayment_schedules` (
  `id` int(11) NOT NULL,
  `loan_id` int(11) NOT NULL,
  `payment_number` int(11) NOT NULL,
  `due_date` date NOT NULL,
  `total_due` decimal(15,2) NOT NULL,
  `principal_due` decimal(15,2) NOT NULL,
  `interest_due` decimal(15,2) NOT NULL,
  `penalty_due` decimal(15,2) DEFAULT 0.00,
  `status` enum('pending','paid','overdue','partial') DEFAULT 'pending',
  `paid_amount` decimal(15,2) DEFAULT 0.00,
  `balance` decimal(15,2) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `notifications`
--

CREATE TABLE `notifications` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `type` enum('info','warning','success','error') DEFAULT 'info',
  `title` varchar(200) NOT NULL,
  `message` text NOT NULL,
  `link` varchar(255) DEFAULT NULL,
  `is_read` tinyint(1) DEFAULT 0,
  `read_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `reports`
--

CREATE TABLE `reports` (
  `id` int(11) NOT NULL,
  `report_code` varchar(50) NOT NULL,
  `report_type` enum('loan','transaction','client','financial','custom') NOT NULL,
  `title` varchar(200) NOT NULL,
  `description` text DEFAULT NULL,
  `parameters` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`parameters`)),
  `file_path` varchar(255) DEFAULT NULL,
  `file_format` enum('pdf','excel','csv','html') DEFAULT NULL,
  `generated_by` int(11) DEFAULT NULL,
  `generated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `settings`
--

CREATE TABLE `settings` (
  `id` int(11) NOT NULL,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text DEFAULT NULL,
  `setting_group` varchar(50) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `is_encrypted` tinyint(1) DEFAULT 0,
  `updated_by` int(11) DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `settings`
--

INSERT INTO `settings` (`id`, `setting_key`, `setting_value`, `setting_group`, `description`, `is_encrypted`, `updated_by`, `updated_at`) VALUES
(1, 'company_name', 'ABC Loan Management', 'company', 'Company Name', 0, NULL, '2026-06-26 07:01:51'),
(2, 'company_address', 'Nairobi, Kenya', 'company', 'Company Address', 0, NULL, '2026-06-26 07:01:51'),
(3, 'company_phone', '+254700000000', 'company', 'Company Phone', 0, NULL, '2026-06-26 07:01:51'),
(4, 'company_email', 'info@abcloans.com', 'company', 'Company Email', 0, NULL, '2026-06-26 07:01:51'),
(5, 'currency', 'KES', 'system', 'Default Currency', 0, NULL, '2026-06-26 07:01:51'),
(6, 'date_format', 'Y-m-d', 'system', 'Date Format', 0, NULL, '2026-06-26 07:01:51'),
(7, 'timezone', 'Africa/Nairobi', 'system', 'System Timezone', 0, NULL, '2026-06-26 07:01:51'),
(8, 'max_loan_amount', '5000000', 'loan', 'Maximum Loan Amount', 0, NULL, '2026-06-26 07:01:51'),
(9, 'min_loan_amount', '1000', 'loan', 'Minimum Loan Amount', 0, NULL, '2026-06-26 07:01:51'),
(10, 'default_interest_rate', '12.00', 'loan', 'Default Interest Rate (%)', 0, NULL, '2026-06-26 07:01:51'),
(11, 'late_penalty_rate', '5.00', 'loan', 'Late Payment Penalty Rate (%)', 0, NULL, '2026-06-26 07:01:51'),
(12, 'processing_fee_rate', '1.00', 'loan', 'Processing Fee Rate (%)', 0, NULL, '2026-06-26 07:01:51'),
(13, 'max_document_size', '5242880', 'documents', 'Maximum Document Upload Size (bytes)', 0, NULL, '2026-06-26 07:01:51'),
(14, 'allowed_document_types', 'jpg,jpeg,png,pdf', 'documents', 'Allowed Document File Types', 0, NULL, '2026-06-26 07:01:51'),
(15, 'document_retention_days', '2555', 'documents', 'Document Retention Period (days)', 0, NULL, '2026-06-26 07:01:51');

-- --------------------------------------------------------

--
-- Table structure for table `staff`
--

CREATE TABLE `staff` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `staff_number` varchar(50) NOT NULL,
  `department` varchar(100) DEFAULT NULL,
  `position` varchar(100) DEFAULT NULL,
  `hire_date` date DEFAULT NULL,
  `supervisor_id` int(11) DEFAULT NULL,
  `salary` decimal(15,2) DEFAULT NULL,
  `emergency_contact` varchar(100) DEFAULT NULL,
  `emergency_phone` varchar(20) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `staff`
--

INSERT INTO `staff` (`id`, `user_id`, `staff_number`, `department`, `position`, `hire_date`, `supervisor_id`, `salary`, `emergency_contact`, `emergency_phone`, `created_at`) VALUES
(1, 1, 'STF-001', 'Administration', 'System Administrator', '2026-06-26', NULL, NULL, NULL, NULL, '2026-06-26 07:01:51');

-- --------------------------------------------------------

--
-- Table structure for table `system_logs`
--

CREATE TABLE `system_logs` (
  `id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `action` varchar(100) NOT NULL,
  `module` varchar(50) NOT NULL,
  `entity_type` varchar(50) DEFAULT NULL,
  `entity_id` int(11) DEFAULT NULL,
  `old_values` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`old_values`)),
  `new_values` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`new_values`)),
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `transactions`
--

CREATE TABLE `transactions` (
  `id` int(11) NOT NULL,
  `transaction_reference` varchar(50) NOT NULL,
  `transaction_type` enum('loan_disbursement','loan_repayment','fee_charge','penalty_charge','refund','write_off') NOT NULL,
  `loan_id` int(11) DEFAULT NULL,
  `client_id` int(11) DEFAULT NULL,
  `amount` decimal(15,2) NOT NULL,
  `balance_after` decimal(15,2) DEFAULT NULL,
  `transaction_date` datetime NOT NULL,
  `payment_method` varchar(50) DEFAULT NULL,
  `reference_number` varchar(100) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `status` enum('pending','completed','failed','reversed') DEFAULT 'completed',
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `username` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `full_name` varchar(100) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `role` enum('admin','manager','loan_officer','accountant','viewer') DEFAULT 'loan_officer',
  `profile_image` varchar(255) DEFAULT NULL,
  `last_login` datetime DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `username`, `email`, `password_hash`, `full_name`, `phone`, `role`, `profile_image`, `last_login`, `is_active`, `created_at`, `updated_at`) VALUES
(1, 'admin', 'admin@loansystem.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'System Administrator', NULL, 'admin', NULL, NULL, 1, '2026-06-26 07:01:51', '2026-06-26 07:01:51');

-- --------------------------------------------------------

--
-- Stand-in structure for view `vw_client_document_status`
-- (See below for the actual view)
--
CREATE TABLE `vw_client_document_status` (
`client_id` int(11)
,`client_code` varchar(50)
,`client_name` varchar(101)
,`documents_submitted` tinyint(1)
,`documents_verified` tinyint(1)
,`onboarding_status` enum('pending','documents_submitted','under_review','verified','rejected','incomplete')
,`total_documents` bigint(21)
,`verified_documents` decimal(22,0)
,`pending_documents` decimal(22,0)
,`rejected_documents` decimal(22,0)
,`last_upload_date` timestamp
);

-- --------------------------------------------------------

--
-- Stand-in structure for view `vw_client_document_summary`
-- (See below for the actual view)
--
CREATE TABLE `vw_client_document_summary` (
`client_id` int(11)
,`client_code` varchar(50)
,`client_name` varchar(101)
,`onboarding_status` enum('pending','documents_submitted','under_review','verified','rejected','incomplete')
,`documents_submitted` tinyint(1)
,`documents_verified` tinyint(1)
,`document_types_uploaded` bigint(21)
,`verified_count` decimal(22,0)
,`pending_count` decimal(22,0)
,`rejected_count` decimal(22,0)
,`last_upload` timestamp
,`days_since_last_upload` int(7)
);

-- --------------------------------------------------------

--
-- Stand-in structure for view `vw_client_loan_status`
-- (See below for the actual view)
--
CREATE TABLE `vw_client_loan_status` (
`client_id` int(11)
,`client_code` varchar(50)
,`client_name` varchar(101)
,`phone` varchar(20)
,`email` varchar(100)
,`total_loans` bigint(21)
,`active_loans` decimal(22,0)
,`defaulted_loans` decimal(22,0)
,`total_outstanding` decimal(37,2)
,`total_completed` decimal(37,2)
,`last_loan_date` date
);

-- --------------------------------------------------------

--
-- Stand-in structure for view `vw_loan_disbursement_summary`
-- (See below for the actual view)
--
CREATE TABLE `vw_loan_disbursement_summary` (
`loan_id` int(11)
,`loan_number` varchar(50)
,`client_name` varchar(101)
,`loan_amount` decimal(15,2)
,`disbursed_amount` decimal(15,2)
,`disbursement_date` date
,`disbursement_method` enum('bank_transfer','cash','cheque','mobile_money')
,`disbursement_status` enum('pending','processed','completed','failed','cancelled')
,`processed_by_name` varchar(100)
,`reference_number` varchar(100)
,`loan_status` enum('draft','pending','under_review','approved','rejected','disbursed','active','completed','defaulted','written_off')
);

-- --------------------------------------------------------

--
-- Stand-in structure for view `vw_loan_summary`
-- (See below for the actual view)
--
CREATE TABLE `vw_loan_summary` (
`id` int(11)
,`loan_number` varchar(50)
,`client_code` varchar(50)
,`client_name` varchar(101)
,`amount` decimal(15,2)
,`interest_rate` decimal(5,2)
,`term_months` int(11)
,`total_repayable` decimal(15,2)
,`monthly_payment` decimal(15,2)
,`status` enum('draft','pending','under_review','approved','rejected','disbursed','active','completed','defaulted','written_off')
,`application_date` date
,`approval_date` date
,`disbursement_date` date
,`maturity_date` date
,`approved_by_name` varchar(100)
,`created_at` timestamp
);

-- --------------------------------------------------------

--
-- Stand-in structure for view `vw_monthly_repayment_summary`
-- (See below for the actual view)
--
CREATE TABLE `vw_monthly_repayment_summary` (
`month_year` varchar(7)
,`total_payments` bigint(21)
,`total_amount_collected` decimal(37,2)
,`total_principal_collected` decimal(37,2)
,`total_interest_collected` decimal(37,2)
,`total_penalty_collected` decimal(37,2)
,`loans_with_payments` bigint(21)
);

-- --------------------------------------------------------

--
-- Stand-in structure for view `vw_overdue_loans`
-- (See below for the actual view)
--
CREATE TABLE `vw_overdue_loans` (
`loan_id` int(11)
,`loan_number` varchar(50)
,`client_name` varchar(101)
,`phone` varchar(20)
,`email` varchar(100)
,`amount` decimal(15,2)
,`total_repayable` decimal(15,2)
,`total_paid` decimal(37,2)
,`outstanding_balance` decimal(38,2)
,`days_overdue` int(7)
,`maturity_date` date
,`status` enum('draft','pending','under_review','approved','rejected','disbursed','active','completed','defaulted','written_off')
);

-- --------------------------------------------------------

--
-- Stand-in structure for view `vw_repayment_performance`
-- (See below for the actual view)
--
CREATE TABLE `vw_repayment_performance` (
`loan_id` int(11)
,`loan_number` varchar(50)
,`client_code` varchar(50)
,`client_name` varchar(101)
,`total_repayable` decimal(15,2)
,`total_paid` decimal(37,2)
,`balance` decimal(38,2)
,`percentage_paid` decimal(43,2)
,`payments_made` bigint(21)
,`first_payment_date` date
,`last_payment_date` date
);

-- --------------------------------------------------------

--
-- Stand-in structure for view `vw_staff_performance`
-- (See below for the actual view)
--
CREATE TABLE `vw_staff_performance` (
`user_id` int(11)
,`full_name` varchar(100)
,`role` enum('admin','manager','loan_officer','accountant','viewer')
,`loans_processed` bigint(21)
,`disbursements_processed` bigint(21)
,`repayments_received` bigint(21)
,`total_loan_amount` decimal(37,2)
,`total_collections` decimal(37,2)
);

-- --------------------------------------------------------

--
-- Structure for view `vw_client_document_status`
--
DROP TABLE IF EXISTS `vw_client_document_status`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_client_document_status`  AS SELECT `c`.`id` AS `client_id`, `c`.`client_code` AS `client_code`, concat(`c`.`first_name`,' ',`c`.`last_name`) AS `client_name`, `c`.`documents_submitted` AS `documents_submitted`, `c`.`documents_verified` AS `documents_verified`, `c`.`onboarding_status` AS `onboarding_status`, count(`cd`.`id`) AS `total_documents`, sum(case when `cd`.`verification_status` = 'verified' then 1 else 0 end) AS `verified_documents`, sum(case when `cd`.`verification_status` = 'pending' then 1 else 0 end) AS `pending_documents`, sum(case when `cd`.`verification_status` = 'rejected' then 1 else 0 end) AS `rejected_documents`, max(`cd`.`upload_date`) AS `last_upload_date` FROM (`clients` `c` left join `client_documents` `cd` on(`c`.`id` = `cd`.`client_id` and `cd`.`is_active` = 1)) GROUP BY `c`.`id` ;

-- --------------------------------------------------------

--
-- Structure for view `vw_client_document_summary`
--
DROP TABLE IF EXISTS `vw_client_document_summary`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_client_document_summary`  AS SELECT `c`.`id` AS `client_id`, `c`.`client_code` AS `client_code`, concat(`c`.`first_name`,' ',`c`.`last_name`) AS `client_name`, `c`.`onboarding_status` AS `onboarding_status`, `c`.`documents_submitted` AS `documents_submitted`, `c`.`documents_verified` AS `documents_verified`, count(distinct `cd`.`document_type_id`) AS `document_types_uploaded`, sum(case when `cd`.`verification_status` = 'verified' then 1 else 0 end) AS `verified_count`, sum(case when `cd`.`verification_status` = 'pending' then 1 else 0 end) AS `pending_count`, sum(case when `cd`.`verification_status` = 'rejected' then 1 else 0 end) AS `rejected_count`, max(`cd`.`upload_date`) AS `last_upload`, to_days(current_timestamp()) - to_days(max(`cd`.`upload_date`)) AS `days_since_last_upload` FROM (`clients` `c` left join `client_documents` `cd` on(`c`.`id` = `cd`.`client_id` and `cd`.`is_active` = 1)) GROUP BY `c`.`id` ;

-- --------------------------------------------------------

--
-- Structure for view `vw_client_loan_status`
--
DROP TABLE IF EXISTS `vw_client_loan_status`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_client_loan_status`  AS SELECT `c`.`id` AS `client_id`, `c`.`client_code` AS `client_code`, concat(`c`.`first_name`,' ',`c`.`last_name`) AS `client_name`, `c`.`phone` AS `phone`, `c`.`email` AS `email`, count(`l`.`id`) AS `total_loans`, sum(case when `l`.`status` = 'active' then 1 else 0 end) AS `active_loans`, sum(case when `l`.`status` = 'defaulted' then 1 else 0 end) AS `defaulted_loans`, sum(case when `l`.`status` in ('active','disbursed') then `l`.`amount` else 0 end) AS `total_outstanding`, sum(case when `l`.`status` = 'completed' then `l`.`amount` else 0 end) AS `total_completed`, max(`l`.`application_date`) AS `last_loan_date` FROM (`clients` `c` left join `loans` `l` on(`c`.`id` = `l`.`client_id`)) GROUP BY `c`.`id` ;

-- --------------------------------------------------------

--
-- Structure for view `vw_loan_disbursement_summary`
--
DROP TABLE IF EXISTS `vw_loan_disbursement_summary`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_loan_disbursement_summary`  AS SELECT `l`.`id` AS `loan_id`, `l`.`loan_number` AS `loan_number`, concat(`c`.`first_name`,' ',`c`.`last_name`) AS `client_name`, `l`.`amount` AS `loan_amount`, `ld`.`amount` AS `disbursed_amount`, `ld`.`disbursement_date` AS `disbursement_date`, `ld`.`disbursement_method` AS `disbursement_method`, `ld`.`status` AS `disbursement_status`, `u`.`full_name` AS `processed_by_name`, `ld`.`reference_number` AS `reference_number`, `l`.`status` AS `loan_status` FROM (((`loan_disbursements` `ld` join `loans` `l` on(`ld`.`loan_id` = `l`.`id`)) join `clients` `c` on(`l`.`client_id` = `c`.`id`)) left join `users` `u` on(`ld`.`processed_by` = `u`.`id`)) ;

-- --------------------------------------------------------

--
-- Structure for view `vw_loan_summary`
--
DROP TABLE IF EXISTS `vw_loan_summary`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_loan_summary`  AS SELECT `l`.`id` AS `id`, `l`.`loan_number` AS `loan_number`, `c`.`client_code` AS `client_code`, concat(`c`.`first_name`,' ',`c`.`last_name`) AS `client_name`, `l`.`amount` AS `amount`, `l`.`interest_rate` AS `interest_rate`, `l`.`term_months` AS `term_months`, `l`.`total_repayable` AS `total_repayable`, `l`.`monthly_payment` AS `monthly_payment`, `l`.`status` AS `status`, `l`.`application_date` AS `application_date`, `l`.`approval_date` AS `approval_date`, `l`.`disbursement_date` AS `disbursement_date`, `l`.`maturity_date` AS `maturity_date`, `u`.`full_name` AS `approved_by_name`, `l`.`created_at` AS `created_at` FROM ((`loans` `l` left join `clients` `c` on(`l`.`client_id` = `c`.`id`)) left join `users` `u` on(`l`.`approved_by` = `u`.`id`)) ;

-- --------------------------------------------------------

--
-- Structure for view `vw_monthly_repayment_summary`
--
DROP TABLE IF EXISTS `vw_monthly_repayment_summary`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_monthly_repayment_summary`  AS SELECT date_format(`lr`.`payment_date`,'%Y-%m') AS `month_year`, count(`lr`.`id`) AS `total_payments`, sum(`lr`.`amount`) AS `total_amount_collected`, sum(`lr`.`principal_paid`) AS `total_principal_collected`, sum(`lr`.`interest_paid`) AS `total_interest_collected`, sum(`lr`.`penalty_paid`) AS `total_penalty_collected`, count(distinct `lr`.`loan_id`) AS `loans_with_payments` FROM `loan_repayments` AS `lr` WHERE `lr`.`status` = 'completed' GROUP BY date_format(`lr`.`payment_date`,'%Y-%m') ORDER BY date_format(`lr`.`payment_date`,'%Y-%m') DESC ;

-- --------------------------------------------------------

--
-- Structure for view `vw_overdue_loans`
--
DROP TABLE IF EXISTS `vw_overdue_loans`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_overdue_loans`  AS SELECT `l`.`id` AS `loan_id`, `l`.`loan_number` AS `loan_number`, concat(`c`.`first_name`,' ',`c`.`last_name`) AS `client_name`, `c`.`phone` AS `phone`, `c`.`email` AS `email`, `l`.`amount` AS `amount`, `l`.`total_repayable` AS `total_repayable`, coalesce(sum(`lr`.`amount`),0) AS `total_paid`, `l`.`total_repayable`- coalesce(sum(`lr`.`amount`),0) AS `outstanding_balance`, to_days(current_timestamp()) - to_days(`l`.`maturity_date`) AS `days_overdue`, `l`.`maturity_date` AS `maturity_date`, `l`.`status` AS `status` FROM ((`loans` `l` join `clients` `c` on(`l`.`client_id` = `c`.`id`)) left join `loan_repayments` `lr` on(`l`.`id` = `lr`.`loan_id` and `lr`.`status` = 'completed')) WHERE `l`.`status` in ('active','disbursed') AND `l`.`maturity_date` < curdate() GROUP BY `l`.`id` ;

-- --------------------------------------------------------

--
-- Structure for view `vw_repayment_performance`
--
DROP TABLE IF EXISTS `vw_repayment_performance`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_repayment_performance`  AS SELECT `l`.`id` AS `loan_id`, `l`.`loan_number` AS `loan_number`, `c`.`client_code` AS `client_code`, concat(`c`.`first_name`,' ',`c`.`last_name`) AS `client_name`, `l`.`total_repayable` AS `total_repayable`, coalesce(sum(`lr`.`amount`),0) AS `total_paid`, `l`.`total_repayable`- coalesce(sum(`lr`.`amount`),0) AS `balance`, round(coalesce(sum(`lr`.`amount`) / nullif(`l`.`total_repayable`,0) * 100,0),2) AS `percentage_paid`, count(`lr`.`id`) AS `payments_made`, min(`lr`.`payment_date`) AS `first_payment_date`, max(`lr`.`payment_date`) AS `last_payment_date` FROM ((`loans` `l` left join `clients` `c` on(`l`.`client_id` = `c`.`id`)) left join `loan_repayments` `lr` on(`l`.`id` = `lr`.`loan_id` and `lr`.`status` = 'completed')) GROUP BY `l`.`id` ;

-- --------------------------------------------------------

--
-- Structure for view `vw_staff_performance`
--
DROP TABLE IF EXISTS `vw_staff_performance`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_staff_performance`  AS SELECT `u`.`id` AS `user_id`, `u`.`full_name` AS `full_name`, `u`.`role` AS `role`, count(distinct `l`.`id`) AS `loans_processed`, count(distinct `ld`.`id`) AS `disbursements_processed`, count(distinct `lr`.`id`) AS `repayments_received`, sum(`l`.`amount`) AS `total_loan_amount`, sum(`lr`.`amount`) AS `total_collections` FROM (((`users` `u` left join `loans` `l` on(`u`.`id` = `l`.`created_by`)) left join `loan_disbursements` `ld` on(`u`.`id` = `ld`.`processed_by`)) left join `loan_repayments` `lr` on(`u`.`id` = `lr`.`received_by`)) WHERE `u`.`is_active` = 1 GROUP BY `u`.`id` ;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `audit_trail`
--
ALTER TABLE `audit_trail`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `idx_table_name` (`table_name`),
  ADD KEY `idx_record_id` (`record_id`),
  ADD KEY `idx_action` (`action`),
  ADD KEY `idx_created_at` (`created_at`);

--
-- Indexes for table `clients`
--
ALTER TABLE `clients`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `client_code` (`client_code`),
  ADD UNIQUE KEY `national_id` (`national_id`),
  ADD KEY `created_by` (`created_by`),
  ADD KEY `id_verified_by` (`id_verified_by`),
  ADD KEY `address_verified_by` (`address_verified_by`),
  ADD KEY `income_verified_by` (`income_verified_by`),
  ADD KEY `idx_client_code` (`client_code`),
  ADD KEY `idx_phone` (`phone`),
  ADD KEY `idx_email` (`email`),
  ADD KEY `idx_national_id` (`national_id`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_onboarding_status` (`onboarding_status`);

--
-- Indexes for table `client_documents`
--
ALTER TABLE `client_documents`
  ADD PRIMARY KEY (`id`),
  ADD KEY `uploaded_by` (`uploaded_by`),
  ADD KEY `verified_by` (`verified_by`),
  ADD KEY `idx_client_id` (`client_id`),
  ADD KEY `idx_document_type` (`document_type_id`),
  ADD KEY `idx_verification_status` (`verification_status`),
  ADD KEY `idx_expiry_date` (`expiry_date`);

--
-- Indexes for table `client_guarantors`
--
ALTER TABLE `client_guarantors`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_client_id` (`client_id`);

--
-- Indexes for table `document_types`
--
ALTER TABLE `document_types`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `document_code` (`document_code`),
  ADD KEY `idx_document_code` (`document_code`),
  ADD KEY `idx_status` (`status`);

--
-- Indexes for table `document_verification_history`
--
ALTER TABLE `document_verification_history`
  ADD PRIMARY KEY (`id`),
  ADD KEY `changed_by` (`changed_by`),
  ADD KEY `idx_document_id` (`document_id`),
  ADD KEY `idx_change_date` (`change_date`);

--
-- Indexes for table `fees_and_charges`
--
ALTER TABLE `fees_and_charges`
  ADD PRIMARY KEY (`id`),
  ADD KEY `waived_by` (`waived_by`),
  ADD KEY `created_by` (`created_by`),
  ADD KEY `idx_loan_id` (`loan_id`),
  ADD KEY `idx_fee_type` (`fee_type`),
  ADD KEY `idx_status` (`status`);

--
-- Indexes for table `loans`
--
ALTER TABLE `loans`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `loan_number` (`loan_number`),
  ADD KEY `product_id` (`product_id`),
  ADD KEY `approved_by` (`approved_by`),
  ADD KEY `disbursed_by` (`disbursed_by`),
  ADD KEY `created_by` (`created_by`),
  ADD KEY `idx_loan_number` (`loan_number`),
  ADD KEY `idx_client_id` (`client_id`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_application_date` (`application_date`),
  ADD KEY `idx_maturity_date` (`maturity_date`);

--
-- Indexes for table `loan_approvals`
--
ALTER TABLE `loan_approvals`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_loan_id` (`loan_id`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_approver` (`approver_id`);

--
-- Indexes for table `loan_collaterals`
--
ALTER TABLE `loan_collaterals`
  ADD PRIMARY KEY (`id`),
  ADD KEY `verified_by` (`verified_by`),
  ADD KEY `idx_loan_id` (`loan_id`);

--
-- Indexes for table `loan_disbursements`
--
ALTER TABLE `loan_disbursements`
  ADD PRIMARY KEY (`id`),
  ADD KEY `processed_by` (`processed_by`),
  ADD KEY `authorized_by` (`authorized_by`),
  ADD KEY `idx_loan_id` (`loan_id`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_disbursement_date` (`disbursement_date`);

--
-- Indexes for table `loan_products`
--
ALTER TABLE `loan_products`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `product_code` (`product_code`),
  ADD KEY `created_by` (`created_by`),
  ADD KEY `idx_product_code` (`product_code`),
  ADD KEY `idx_status` (`status`);

--
-- Indexes for table `loan_repayments`
--
ALTER TABLE `loan_repayments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `received_by` (`received_by`),
  ADD KEY `idx_loan_id` (`loan_id`),
  ADD KEY `idx_payment_date` (`payment_date`),
  ADD KEY `idx_due_date` (`due_date`),
  ADD KEY `idx_reference` (`reference_number`),
  ADD KEY `idx_status` (`status`);

--
-- Indexes for table `loan_repayment_schedules`
--
ALTER TABLE `loan_repayment_schedules`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_loan_id` (`loan_id`),
  ADD KEY `idx_due_date` (`due_date`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_payment_number` (`payment_number`);

--
-- Indexes for table `notifications`
--
ALTER TABLE `notifications`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_user_id` (`user_id`),
  ADD KEY `idx_is_read` (`is_read`),
  ADD KEY `idx_created_at` (`created_at`);

--
-- Indexes for table `reports`
--
ALTER TABLE `reports`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `report_code` (`report_code`),
  ADD KEY `generated_by` (`generated_by`),
  ADD KEY `idx_report_code` (`report_code`),
  ADD KEY `idx_report_type` (`report_type`),
  ADD KEY `idx_generated_at` (`generated_at`);

--
-- Indexes for table `settings`
--
ALTER TABLE `settings`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `setting_key` (`setting_key`),
  ADD KEY `updated_by` (`updated_by`),
  ADD KEY `idx_setting_key` (`setting_key`),
  ADD KEY `idx_setting_group` (`setting_group`);

--
-- Indexes for table `staff`
--
ALTER TABLE `staff`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `user_id` (`user_id`),
  ADD UNIQUE KEY `staff_number` (`staff_number`),
  ADD KEY `supervisor_id` (`supervisor_id`),
  ADD KEY `idx_staff_number` (`staff_number`),
  ADD KEY `idx_department` (`department`);

--
-- Indexes for table `system_logs`
--
ALTER TABLE `system_logs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_user_id` (`user_id`),
  ADD KEY `idx_module` (`module`),
  ADD KEY `idx_action` (`action`),
  ADD KEY `idx_created_at` (`created_at`);

--
-- Indexes for table `transactions`
--
ALTER TABLE `transactions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `transaction_reference` (`transaction_reference`),
  ADD KEY `created_by` (`created_by`),
  ADD KEY `idx_transaction_reference` (`transaction_reference`),
  ADD KEY `idx_loan_id` (`loan_id`),
  ADD KEY `idx_client_id` (`client_id`),
  ADD KEY `idx_date` (`transaction_date`),
  ADD KEY `idx_type` (`transaction_type`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `username` (`username`),
  ADD UNIQUE KEY `email` (`email`),
  ADD KEY `idx_email` (`email`),
  ADD KEY `idx_username` (`username`),
  ADD KEY `idx_role` (`role`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `audit_trail`
--
ALTER TABLE `audit_trail`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `clients`
--
ALTER TABLE `clients`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `client_documents`
--
ALTER TABLE `client_documents`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `client_guarantors`
--
ALTER TABLE `client_guarantors`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `document_types`
--
ALTER TABLE `document_types`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `document_verification_history`
--
ALTER TABLE `document_verification_history`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `fees_and_charges`
--
ALTER TABLE `fees_and_charges`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `loans`
--
ALTER TABLE `loans`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `loan_approvals`
--
ALTER TABLE `loan_approvals`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `loan_collaterals`
--
ALTER TABLE `loan_collaterals`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `loan_disbursements`
--
ALTER TABLE `loan_disbursements`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `loan_products`
--
ALTER TABLE `loan_products`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `loan_repayments`
--
ALTER TABLE `loan_repayments`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `loan_repayment_schedules`
--
ALTER TABLE `loan_repayment_schedules`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `notifications`
--
ALTER TABLE `notifications`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `reports`
--
ALTER TABLE `reports`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `settings`
--
ALTER TABLE `settings`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=16;

--
-- AUTO_INCREMENT for table `staff`
--
ALTER TABLE `staff`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `system_logs`
--
ALTER TABLE `system_logs`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `transactions`
--
ALTER TABLE `transactions`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `audit_trail`
--
ALTER TABLE `audit_trail`
  ADD CONSTRAINT `audit_trail_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

--
-- Constraints for table `clients`
--
ALTER TABLE `clients`
  ADD CONSTRAINT `clients_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `clients_ibfk_2` FOREIGN KEY (`id_verified_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `clients_ibfk_3` FOREIGN KEY (`address_verified_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `clients_ibfk_4` FOREIGN KEY (`income_verified_by`) REFERENCES `users` (`id`);

--
-- Constraints for table `client_documents`
--
ALTER TABLE `client_documents`
  ADD CONSTRAINT `client_documents_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `client_documents_ibfk_2` FOREIGN KEY (`document_type_id`) REFERENCES `document_types` (`id`),
  ADD CONSTRAINT `client_documents_ibfk_3` FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `client_documents_ibfk_4` FOREIGN KEY (`verified_by`) REFERENCES `users` (`id`);

--
-- Constraints for table `client_guarantors`
--
ALTER TABLE `client_guarantors`
  ADD CONSTRAINT `client_guarantors_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `document_verification_history`
--
ALTER TABLE `document_verification_history`
  ADD CONSTRAINT `document_verification_history_ibfk_1` FOREIGN KEY (`document_id`) REFERENCES `client_documents` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `document_verification_history_ibfk_2` FOREIGN KEY (`changed_by`) REFERENCES `users` (`id`);

--
-- Constraints for table `fees_and_charges`
--
ALTER TABLE `fees_and_charges`
  ADD CONSTRAINT `fees_and_charges_ibfk_1` FOREIGN KEY (`loan_id`) REFERENCES `loans` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fees_and_charges_ibfk_2` FOREIGN KEY (`waived_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `fees_and_charges_ibfk_3` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`);

--
-- Constraints for table `loans`
--
ALTER TABLE `loans`
  ADD CONSTRAINT `loans_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
  ADD CONSTRAINT `loans_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `loan_products` (`id`),
  ADD CONSTRAINT `loans_ibfk_3` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `loans_ibfk_4` FOREIGN KEY (`disbursed_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `loans_ibfk_5` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`);

--
-- Constraints for table `loan_approvals`
--
ALTER TABLE `loan_approvals`
  ADD CONSTRAINT `loan_approvals_ibfk_1` FOREIGN KEY (`loan_id`) REFERENCES `loans` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `loan_approvals_ibfk_2` FOREIGN KEY (`approver_id`) REFERENCES `users` (`id`);

--
-- Constraints for table `loan_collaterals`
--
ALTER TABLE `loan_collaterals`
  ADD CONSTRAINT `loan_collaterals_ibfk_1` FOREIGN KEY (`loan_id`) REFERENCES `loans` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `loan_collaterals_ibfk_2` FOREIGN KEY (`verified_by`) REFERENCES `users` (`id`);

--
-- Constraints for table `loan_disbursements`
--
ALTER TABLE `loan_disbursements`
  ADD CONSTRAINT `loan_disbursements_ibfk_1` FOREIGN KEY (`loan_id`) REFERENCES `loans` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `loan_disbursements_ibfk_2` FOREIGN KEY (`processed_by`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `loan_disbursements_ibfk_3` FOREIGN KEY (`authorized_by`) REFERENCES `users` (`id`);

--
-- Constraints for table `loan_products`
--
ALTER TABLE `loan_products`
  ADD CONSTRAINT `loan_products_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`);

--
-- Constraints for table `loan_repayments`
--
ALTER TABLE `loan_repayments`
  ADD CONSTRAINT `loan_repayments_ibfk_1` FOREIGN KEY (`loan_id`) REFERENCES `loans` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `loan_repayments_ibfk_2` FOREIGN KEY (`received_by`) REFERENCES `users` (`id`);

--
-- Constraints for table `loan_repayment_schedules`
--
ALTER TABLE `loan_repayment_schedules`
  ADD CONSTRAINT `loan_repayment_schedules_ibfk_1` FOREIGN KEY (`loan_id`) REFERENCES `loans` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `notifications`
--
ALTER TABLE `notifications`
  ADD CONSTRAINT `notifications_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `reports`
--
ALTER TABLE `reports`
  ADD CONSTRAINT `reports_ibfk_1` FOREIGN KEY (`generated_by`) REFERENCES `users` (`id`);

--
-- Constraints for table `settings`
--
ALTER TABLE `settings`
  ADD CONSTRAINT `settings_ibfk_1` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`);

--
-- Constraints for table `staff`
--
ALTER TABLE `staff`
  ADD CONSTRAINT `staff_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `staff_ibfk_2` FOREIGN KEY (`supervisor_id`) REFERENCES `users` (`id`);

--
-- Constraints for table `system_logs`
--
ALTER TABLE `system_logs`
  ADD CONSTRAINT `system_logs_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

--
-- Constraints for table `transactions`
--
ALTER TABLE `transactions`
  ADD CONSTRAINT `transactions_ibfk_1` FOREIGN KEY (`loan_id`) REFERENCES `loans` (`id`),
  ADD CONSTRAINT `transactions_ibfk_2` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
  ADD CONSTRAINT `transactions_ibfk_3` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`);
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;