-- phpMyAdmin SQL Dump
-- version 5.2.2
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: Nov 29, 2025 at 04:37 PM
-- Server version: 8.0.42
-- PHP Version: 8.3.23

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: `acbptxvs_branch_system`
--

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

--
-- Table structure for table `auth_group`
--

CREATE TABLE `auth_group` (
  `id` int NOT NULL,
  `name` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `auth_group_permissions`
--

CREATE TABLE `auth_group_permissions` (
  `id` bigint NOT NULL,
  `group_id` int NOT NULL,
  `permission_id` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `auth_permission`
--

CREATE TABLE `auth_permission` (
  `id` int NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `content_type_id` int NOT NULL,
  `codename` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `cache_table`
--

CREATE TABLE `cache_table` (
  `cache_key` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `value` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `expires` datetime(6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `customer_requests`
--

CREATE TABLE `customer_requests` (
  `id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `request_number` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `customer_id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `request_type` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `subject` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `priority` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'medium',
  `status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
  `assigned_to_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `related_loan_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `related_application_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `resolution_notes` longtext COLLATE utf8mb4_unicode_ci,
  `resolved_by_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `resolved_at` datetime(6) DEFAULT NULL,
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `default_role_permissions`
--

CREATE TABLE `default_role_permissions` (
  `id` bigint NOT NULL,
  `role` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `module` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `action` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_allowed` tinyint(1) NOT NULL,
  `description` longtext COLLATE utf8mb4_unicode_ci,
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `django_admin_log`
--

CREATE TABLE `django_admin_log` (
  `id` int NOT NULL,
  `action_time` datetime(6) NOT NULL,
  `object_id` longtext COLLATE utf8mb4_unicode_ci,
  `object_repr` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `action_flag` smallint UNSIGNED NOT NULL,
  `change_message` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `content_type_id` int DEFAULT NULL,
  `user_id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL
) ;

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

--
-- Table structure for table `django_content_type`
--

CREATE TABLE `django_content_type` (
  `id` int NOT NULL,
  `app_label` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `model` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `django_migrations`
--

CREATE TABLE `django_migrations` (
  `id` bigint NOT NULL,
  `app` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `applied` datetime(6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `django_migrations_backup`
--

CREATE TABLE `django_migrations_backup` (
  `id` bigint NOT NULL DEFAULT '0',
  `app` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `applied` datetime(6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `django_session`
--

CREATE TABLE `django_session` (
  `session_key` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  `session_data` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `expire_date` datetime(6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `document_templates`
--

CREATE TABLE `document_templates` (
  `id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `template_type` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` longtext COLLATE utf8mb4_unicode_ci,
  `html_template` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `css_styles` longtext COLLATE utf8mb4_unicode_ci,
  `is_active` tinyint(1) NOT NULL,
  `is_default` tinyint(1) NOT NULL,
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `email_templates`
--

CREATE TABLE `email_templates` (
  `id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `template_type` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `subject` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `html_content` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `text_content` longtext COLLATE utf8mb4_unicode_ci,
  `is_active` tinyint(1) NOT NULL,
  `is_default` tinyint(1) NOT NULL,
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `expenses`
--

CREATE TABLE `expenses` (
  `id` bigint NOT NULL,
  `title` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` longtext COLLATE utf8mb4_unicode_ci,
  `category` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `expense_date` date NOT NULL,
  `payment_method` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `paid_to` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `reference_number` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `receipt_path` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
  `notes` longtext COLLATE utf8mb4_unicode_ci,
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL,
  `approved_at` datetime(6) DEFAULT NULL,
  `rejection_reason` longtext COLLATE utf8mb4_unicode_ci,
  `branch_id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `loan_id` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `staff_id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `approved_by_id` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `generated_reports`
--

CREATE TABLE `generated_reports` (
  `id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `report_type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `report_name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `format` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `file_path` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `file_size` int UNSIGNED DEFAULT NULL,
  `parameters` json DEFAULT NULL,
  `generated_at` datetime(6) NOT NULL,
  `generated_by_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ;

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

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

CREATE TABLE `loans` (
  `id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `loan_number` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `principal_amount` decimal(12,2) NOT NULL,
  `interest_amount` decimal(12,2) NOT NULL,
  `processing_fee` decimal(12,2) NOT NULL,
  `total_amount` decimal(12,2) NOT NULL,
  `disbursement_date` datetime(6) NOT NULL,
  `due_date` datetime(6) NOT NULL,
  `duration_days` int UNSIGNED NOT NULL,
  `status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `amount_paid` decimal(12,2) NOT NULL,
  `last_payment_date` datetime(6) DEFAULT NULL,
  `is_rolled_over` tinyint(1) NOT NULL,
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL,
  `borrower` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `original_loan` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `application` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `application_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `borrower_id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `original_loan_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `insurance_fee` decimal(10,2) DEFAULT NULL,
  `total_fees` decimal(10,2) DEFAULT NULL,
  `net_amount` decimal(10,2) DEFAULT NULL,
  `outstanding_balance` decimal(10,2) DEFAULT NULL,
  `total_paid` decimal(10,2) DEFAULT NULL,
  `next_payment_date` date DEFAULT NULL,
  `payment_frequency` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT 'monthly',
  `collateral_type` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `collateral_value` decimal(15,2) DEFAULT NULL,
  `guarantor_name` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `guarantor_phone` varchar(17) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `guarantor_id` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `loan_officer_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `approved_by_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `disbursed_by_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_deleted` tinyint(1) DEFAULT '0',
  `deleted_at` datetime(6) DEFAULT NULL,
  `deleted_by_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_by_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `updated_by_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `approved_at` datetime(6) DEFAULT NULL,
  `disbursed_at` datetime(6) DEFAULT NULL,
  `first_payment_date` date DEFAULT NULL,
  `maturity_date` date DEFAULT NULL,
  `grace_period_days` int DEFAULT '0',
  `penalty_rate` decimal(5,2) DEFAULT '0.00',
  `late_fee_amount` decimal(10,2) DEFAULT '0.00',
  `rollover_count` int DEFAULT '0',
  `rollover_fee` decimal(10,2) DEFAULT '0.00',
  `early_settlement_fee` decimal(10,2) DEFAULT '0.00',
  `loan_purpose` text COLLATE utf8mb4_unicode_ci,
  `repayment_method` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT 'monthly',
  `auto_debit_enabled` tinyint(1) DEFAULT '0',
  `insurance_premium` decimal(10,2) DEFAULT '0.00',
  `insurance_policy_number` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `collateral_description` text COLLATE utf8mb4_unicode_ci,
  `collateral_location` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `guarantor_consent` tinyint(1) DEFAULT '0',
  `guarantor_signature` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `loan_agreement_signed` tinyint(1) DEFAULT '0',
  `loan_agreement_date` datetime(6) DEFAULT NULL,
  `disbursement_reference` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `disbursement_charges` decimal(10,2) DEFAULT '0.00',
  `external_loan_id` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `credit_bureau_reported` tinyint(1) DEFAULT '0',
  `restructured` tinyint(1) DEFAULT '0',
  `restructure_date` datetime(6) DEFAULT NULL,
  `restructure_reason` text COLLATE utf8mb4_unicode_ci,
  `write_off_date` datetime(6) DEFAULT NULL,
  `write_off_amount` decimal(10,2) DEFAULT NULL,
  `write_off_reason` text COLLATE utf8mb4_unicode_ci,
  `recovery_amount` decimal(10,2) DEFAULT '0.00',
  `legal_action_initiated` tinyint(1) DEFAULT '0',
  `legal_action_date` datetime(6) DEFAULT NULL,
  `settlement_amount` decimal(10,2) DEFAULT NULL,
  `settlement_date` datetime(6) DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `internal_rating` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `risk_category` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `days_past_due` int DEFAULT '0',
  `classification` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT 'performing',
  `provision_amount` decimal(10,2) DEFAULT '0.00',
  `accrued_interest` decimal(10,2) DEFAULT '0.00',
  `accrued_penalties` decimal(10,2) DEFAULT '0.00',
  `last_statement_date` date DEFAULT NULL,
  `next_review_date` date DEFAULT NULL,
  `registration_fee` decimal(12,2) NOT NULL DEFAULT '0.00'
) ;

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

--
-- Table structure for table `loans_loan`
--

CREATE TABLE `loans_loan` (
  `id` bigint NOT NULL,
  `loan_number` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `borrower_id` bigint NOT NULL,
  `loan_product_id` bigint NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `interest_rate` decimal(5,2) NOT NULL,
  `duration_days` int NOT NULL,
  `status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `application_date` datetime(6) NOT NULL,
  `approval_date` datetime(6) DEFAULT NULL,
  `disbursement_date` datetime(6) DEFAULT NULL,
  `due_date` datetime(6) DEFAULT NULL,
  `total_amount` decimal(10,2) NOT NULL,
  `amount_paid` decimal(10,2) NOT NULL DEFAULT '0.00',
  `balance` decimal(10,2) NOT NULL,
  `approved_by_id` bigint DEFAULT NULL,
  `disbursed_by_id` bigint DEFAULT NULL,
  `notes` longtext COLLATE utf8mb4_unicode_ci,
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL,
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0',
  `deleted_at` datetime(6) DEFAULT NULL,
  `deleted_by_id` bigint DEFAULT NULL,
  `registration_fee` decimal(10,2) DEFAULT NULL,
  `processing_fee` decimal(10,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `loans_loanapplication`
--

CREATE TABLE `loans_loanapplication` (
  `id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `application_number` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `borrower` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `loan_product` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `requested_amount` decimal(12,2) NOT NULL,
  `requested_duration` int NOT NULL,
  `purpose` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `repayment_method` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `interest_amount` decimal(12,2) NOT NULL,
  `processing_fee_amount` decimal(12,2) NOT NULL,
  `total_amount` decimal(12,2) NOT NULL,
  `status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `submitted_at` datetime(6) NOT NULL,
  `reviewed_by` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reviewed_at` datetime(6) DEFAULT NULL,
  `approval_notes` text COLLATE utf8mb4_unicode_ci,
  `supporting_documents` json DEFAULT NULL,
  `auto_approved` tinyint(1) DEFAULT '0',
  `credit_score` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `loans_loanproduct`
--

CREATE TABLE `loans_loanproduct` (
  `id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `product_type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `min_amount` decimal(12,2) NOT NULL,
  `max_amount` decimal(12,2) NOT NULL,
  `interest_rate` decimal(5,2) NOT NULL,
  `processing_fee` decimal(12,2) NOT NULL,
  `late_payment_penalty` decimal(5,2) NOT NULL,
  `duration_months` int NOT NULL,
  `min_duration` int NOT NULL,
  `max_duration` int NOT NULL,
  `available_repayment_methods` json DEFAULT NULL,
  `requires_guarantor` tinyint(1) DEFAULT '0',
  `requires_collateral` tinyint(1) DEFAULT '0',
  `minimum_income` decimal(12,2) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `loans_repayment`
--

CREATE TABLE `loans_repayment` (
  `id` bigint NOT NULL,
  `loan_id` bigint NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `payment_date` datetime(6) NOT NULL,
  `payment_method` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `transaction_reference` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `notes` longtext COLLATE utf8mb4_unicode_ci,
  `recorded_by_id` bigint DEFAULT NULL,
  `created_at` datetime(6) NOT NULL,
  `mpesa_transaction_id` bigint DEFAULT NULL,
  `payment_source` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'manual'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `loan_applications`
--

CREATE TABLE `loan_applications` (
  `id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `application_number` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `requested_amount` decimal(12,2) NOT NULL,
  `requested_duration` int UNSIGNED NOT NULL,
  `purpose` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `interest_amount` decimal(12,2) DEFAULT NULL,
  `processing_fee_amount` decimal(12,2) DEFAULT NULL,
  `total_amount` decimal(12,2) DEFAULT NULL,
  `status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `submitted_at` datetime(6) NOT NULL,
  `reviewed_at` datetime(6) DEFAULT NULL,
  `approval_notes` longtext COLLATE utf8mb4_unicode_ci,
  `supporting_documents` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `auto_approved` tinyint(1) NOT NULL,
  `credit_score` int UNSIGNED DEFAULT NULL,
  `borrower` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reviewed_by` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `loan_product` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `borrower_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `loan_product_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reviewed_by_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `repayment_method` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT 'monthly',
  `processing_fee` decimal(10,2) DEFAULT NULL,
  `insurance_fee` decimal(10,2) DEFAULT NULL,
  `total_fees` decimal(10,2) DEFAULT NULL,
  `net_amount` decimal(10,2) DEFAULT NULL,
  `collateral_type` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `collateral_value` decimal(15,2) DEFAULT NULL,
  `guarantor_name` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `guarantor_phone` varchar(17) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `guarantor_id` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `risk_assessment` text COLLATE utf8mb4_unicode_ci,
  `rejection_reason` text COLLATE utf8mb4_unicode_ci,
  `disbursement_method` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `disbursement_account` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `loan_officer_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `approved_by_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `disbursed_by_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `registration_fee_amount` decimal(12,2) DEFAULT '0.00'
) ;

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

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

CREATE TABLE `loan_products` (
  `id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `product_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `min_amount` decimal(12,2) NOT NULL,
  `max_amount` decimal(12,2) NOT NULL,
  `interest_rate` decimal(5,2) NOT NULL,
  `processing_fee` decimal(5,2) NOT NULL,
  `late_payment_penalty` decimal(5,2) NOT NULL,
  `min_duration` int UNSIGNED NOT NULL,
  `max_duration` int UNSIGNED NOT NULL,
  `requires_guarantor` tinyint(1) NOT NULL,
  `requires_collateral` tinyint(1) NOT NULL,
  `minimum_income` decimal(12,2) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL,
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL,
  `available_repayment_methods` json NOT NULL DEFAULT (_utf8mb4'[]'),
  `duration_months` int UNSIGNED NOT NULL,
  `rollover_fee_percentage` decimal(5,2) DEFAULT '5.00',
  `max_rollover_count` int DEFAULT '3',
  `max_rollover_days` int DEFAULT '30',
  `available_durations` json DEFAULT NULL,
  `processing_fee_rate` decimal(5,2) DEFAULT '0.00',
  `processing_fee_fixed` decimal(10,2) DEFAULT '0.00',
  `insurance_fee_rate` decimal(5,2) DEFAULT '0.00',
  `late_payment_penalty_rate` decimal(5,2) DEFAULT '0.00',
  `grace_period_days` int DEFAULT '0',
  `minimum_credit_score` int DEFAULT NULL,
  `maximum_credit_score` int DEFAULT NULL,
  `collateral_required` tinyint(1) DEFAULT '0',
  `guarantor_required` tinyint(1) DEFAULT '0',
  `auto_approval_enabled` tinyint(1) DEFAULT '0',
  `auto_approval_max_amount` decimal(10,2) DEFAULT NULL,
  `eligibility_criteria` json DEFAULT NULL,
  `required_documents` json DEFAULT NULL,
  `repayment_frequencies` json DEFAULT NULL,
  `early_settlement_allowed` tinyint(1) DEFAULT '1',
  `early_settlement_fee_rate` decimal(5,2) DEFAULT '0.00',
  `rollover_allowed` tinyint(1) DEFAULT '0',
  `rollover_fee_rate` decimal(5,2) DEFAULT '0.00',
  `product_code` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `category` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `target_market` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `currency` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT 'KES',
  `compound_interest` tinyint(1) DEFAULT '0',
  `interest_calculation_method` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT 'reducing_balance',
  `disbursement_methods` json DEFAULT NULL,
  `repayment_methods` json DEFAULT NULL,
  `approval_workflow` json DEFAULT NULL,
  `risk_parameters` json DEFAULT NULL,
  `promotional_rate` decimal(5,2) DEFAULT NULL,
  `promotional_period_days` int DEFAULT NULL,
  `seasonal_availability` json DEFAULT NULL,
  `geographic_restrictions` json DEFAULT NULL,
  `age_restrictions` json DEFAULT NULL,
  `income_requirements` json DEFAULT NULL,
  `employment_requirements` json DEFAULT NULL,
  `credit_history_requirements` json DEFAULT NULL,
  `marketing_materials` json DEFAULT NULL,
  `terms_and_conditions` text COLLATE utf8mb4_unicode_ci,
  `privacy_policy` text COLLATE utf8mb4_unicode_ci,
  `regulatory_compliance` json DEFAULT NULL,
  `audit_trail` json DEFAULT NULL,
  `performance_metrics` json DEFAULT NULL,
  `created_by_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `updated_by_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `approved_by_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `approved_at` datetime(6) DEFAULT NULL,
  `is_deleted` tinyint(1) DEFAULT '0',
  `deleted_at` datetime(6) DEFAULT NULL,
  `deleted_by_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `version` int DEFAULT '1',
  `effective_from` date DEFAULT NULL,
  `effective_to` date DEFAULT NULL,
  `sort_order` int DEFAULT '0',
  `featured` tinyint(1) DEFAULT '0',
  `icon` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `color_scheme` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ;

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

--
-- Table structure for table `loan_scoring`
--

CREATE TABLE `loan_scoring` (
  `id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `repayment_history_score` int UNSIGNED NOT NULL,
  `income_score` int UNSIGNED NOT NULL,
  `rollover_frequency_score` int UNSIGNED NOT NULL,
  `employment_stability_score` int UNSIGNED NOT NULL,
  `total_score` int UNSIGNED NOT NULL,
  `credit_limit` decimal(12,2) NOT NULL,
  `is_eligible` tinyint(1) NOT NULL,
  `risk_level` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `calculated_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL,
  `user` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ;

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

--
-- Table structure for table `loan_statements`
--

CREATE TABLE `loan_statements` (
  `id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `statement_number` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `statement_date` datetime(6) NOT NULL,
  `period_from` datetime(6) NOT NULL,
  `period_to` datetime(6) NOT NULL,
  `principal_amount` decimal(12,2) NOT NULL,
  `interest_amount` decimal(12,2) NOT NULL,
  `total_amount` decimal(12,2) NOT NULL,
  `amount_paid` decimal(12,2) NOT NULL,
  `outstanding_balance` decimal(12,2) NOT NULL,
  `pdf_file` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` datetime(6) NOT NULL,
  `borrower` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `loan` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `loan_id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `borrower_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `mpesa_access_tokens`
--

CREATE TABLE `mpesa_access_tokens` (
  `id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `access_token` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `expires_at` datetime(6) NOT NULL,
  `created_at` datetime(6) NOT NULL,
  `configuration_id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `mpesa_callbacks`
--

CREATE TABLE `mpesa_callbacks` (
  `id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `callback_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `raw_data` json NOT NULL,
  `ip_address` char(39) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_agent` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `processed` tinyint(1) NOT NULL,
  `response_sent` json DEFAULT NULL,
  `created_at` datetime(6) NOT NULL,
  `transaction_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `mpesa_configurations`
--

CREATE TABLE `mpesa_configurations` (
  `id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `environment` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `consumer_key` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `consumer_secret` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `business_short_code` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `passkey` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `validation_url` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `confirmation_url` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `response_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_active` tinyint(1) NOT NULL,
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `mpesa_transactions`
--

CREATE TABLE `mpesa_transactions` (
  `id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `transaction_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `phone_number` varchar(17) COLLATE utf8mb4_unicode_ci NOT NULL,
  `mpesa_transaction_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `merchant_request_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `checkout_request_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `result_code` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `result_description` longtext COLLATE utf8mb4_unicode_ci,
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL,
  `loan` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `repayment` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `loan_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `repayment_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_automatic` tinyint(1) NOT NULL,
  `payment_source` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `trans_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `trans_time` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `business_short_code` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `bill_ref_number` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `invoice_number` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `org_account_balance` decimal(10,2) DEFAULT NULL,
  `third_party_trans_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `msisdn` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `first_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `middle_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `last_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `raw_confirmation_data` json DEFAULT NULL,
  `processing_notes` text COLLATE utf8mb4_unicode_ci,
  `borrower_id` varchar(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `processed_at` datetime DEFAULT NULL,
  `receipt_number` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `result_desc` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `conversation_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `originator_conversation_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `response_code` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `response_description` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `error_code` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `error_message` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `callback_metadata` json DEFAULT NULL,
  `validation_metadata` json DEFAULT NULL,
  `confirmation_metadata` json DEFAULT NULL,
  `stk_push_metadata` json DEFAULT NULL,
  `account_reference` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `transaction_desc` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `party_a` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `party_b` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `identifier_type` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `remarks` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `occasion` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `queue_timeout_url` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `result_url` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `initiator` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `security_credential` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `command_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `password` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `timestamp` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `callback_url` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `timeout_url` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `short_code` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `passkey` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `environment` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT 'production',
  `is_sandbox` tinyint(1) DEFAULT '0',
  `retry_count` int DEFAULT '0',
  `max_retries` int DEFAULT '3',
  `last_retry_at` datetime DEFAULT NULL,
  `next_retry_at` datetime DEFAULT NULL,
  `is_processed` tinyint(1) DEFAULT '0',
  `is_successful` tinyint(1) DEFAULT '0',
  `is_failed` tinyint(1) DEFAULT '0',
  `failure_reason` text COLLATE utf8mb4_unicode_ci,
  `success_message` text COLLATE utf8mb4_unicode_ci,
  `external_reference` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `internal_reference` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `correlation_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `request_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `response_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `callback_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `webhook_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `event_type` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `event_source` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `event_data` json DEFAULT NULL,
  `metadata` json DEFAULT NULL,
  `tags` json DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `comments` text COLLATE utf8mb4_unicode_ci,
  `description` text COLLATE utf8mb4_unicode_ci,
  `summary` text COLLATE utf8mb4_unicode_ci,
  `details` text COLLATE utf8mb4_unicode_ci,
  `extra_data` json DEFAULT NULL,
  `custom_fields` json DEFAULT NULL,
  `user_agent` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_id` varchar(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `session_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `request_headers` json DEFAULT NULL,
  `response_headers` json DEFAULT NULL,
  `request_body` text COLLATE utf8mb4_unicode_ci,
  `response_body` text COLLATE utf8mb4_unicode_ci,
  `duration_ms` int DEFAULT NULL,
  `memory_usage` int DEFAULT NULL,
  `cpu_usage` decimal(5,2) DEFAULT NULL,
  `disk_usage` int DEFAULT NULL,
  `network_usage` int DEFAULT NULL,
  `performance_metrics` json DEFAULT NULL,
  `health_status` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT 'healthy',
  `monitoring_data` json DEFAULT NULL,
  `alert_data` json DEFAULT NULL,
  `log_data` json DEFAULT NULL,
  `debug_data` json DEFAULT NULL,
  `trace_data` json DEFAULT NULL,
  `audit_data` json DEFAULT NULL,
  `compliance_data` json DEFAULT NULL,
  `security_data` json DEFAULT NULL,
  `encryption_data` json DEFAULT NULL,
  `signature_data` json DEFAULT NULL,
  `hash_data` json DEFAULT NULL,
  `checksum_data` json DEFAULT NULL,
  `validation_data` json DEFAULT NULL,
  `verification_data` json DEFAULT NULL,
  `authentication_data` json DEFAULT NULL,
  `authorization_data` json DEFAULT NULL,
  `permission_data` json DEFAULT NULL,
  `role_data` json DEFAULT NULL,
  `access_data` json DEFAULT NULL,
  `session_data` json DEFAULT NULL,
  `token_data` json DEFAULT NULL,
  `credential_data` json DEFAULT NULL,
  `key_data` json DEFAULT NULL,
  `certificate_data` json DEFAULT NULL,
  `certificate_chain` json DEFAULT NULL,
  `public_key` text COLLATE utf8mb4_unicode_ci,
  `private_key` text COLLATE utf8mb4_unicode_ci,
  `secret_key` text COLLATE utf8mb4_unicode_ci,
  `api_key` text COLLATE utf8mb4_unicode_ci,
  `access_token` text COLLATE utf8mb4_unicode_ci,
  `refresh_token` text COLLATE utf8mb4_unicode_ci,
  `id_token` text COLLATE utf8mb4_unicode_ci,
  `jwt_token` text COLLATE utf8mb4_unicode_ci,
  `bearer_token` text COLLATE utf8mb4_unicode_ci,
  `oauth_token` text COLLATE utf8mb4_unicode_ci,
  `oauth_secret` text COLLATE utf8mb4_unicode_ci,
  `oauth_verifier` text COLLATE utf8mb4_unicode_ci,
  `oauth_callback` text COLLATE utf8mb4_unicode_ci,
  `oauth_scope` text COLLATE utf8mb4_unicode_ci,
  `oauth_state` text COLLATE utf8mb4_unicode_ci,
  `oauth_nonce` text COLLATE utf8mb4_unicode_ci,
  `oauth_timestamp` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `oauth_signature` text COLLATE utf8mb4_unicode_ci,
  `oauth_signature_method` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `oauth_version` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `oauth_consumer_key` text COLLATE utf8mb4_unicode_ci,
  `oauth_consumer_secret` text COLLATE utf8mb4_unicode_ci,
  `oauth_token_secret` text COLLATE utf8mb4_unicode_ci,
  `oauth_callback_confirmed` tinyint(1) DEFAULT '0',
  `oauth_authorized` tinyint(1) DEFAULT '0',
  `oauth_expires_in` int DEFAULT NULL,
  `oauth_expires_at` datetime DEFAULT NULL,
  `oauth_issued_at` datetime DEFAULT NULL,
  `oauth_not_before` datetime DEFAULT NULL,
  `oauth_audience` text COLLATE utf8mb4_unicode_ci,
  `oauth_issuer` text COLLATE utf8mb4_unicode_ci,
  `oauth_subject` text COLLATE utf8mb4_unicode_ci,
  `oauth_algorithm` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `oauth_key_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `oauth_key_type` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `oauth_key_use` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `oauth_key_ops` json DEFAULT NULL,
  `oauth_key_alg` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `oauth_key_kty` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `oauth_key_crv` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `oauth_key_x` text COLLATE utf8mb4_unicode_ci,
  `oauth_key_y` text COLLATE utf8mb4_unicode_ci,
  `oauth_key_d` text COLLATE utf8mb4_unicode_ci,
  `oauth_key_n` text COLLATE utf8mb4_unicode_ci,
  `oauth_key_e` text COLLATE utf8mb4_unicode_ci,
  `oauth_key_p` text COLLATE utf8mb4_unicode_ci,
  `oauth_key_q` text COLLATE utf8mb4_unicode_ci,
  `oauth_key_dp` text COLLATE utf8mb4_unicode_ci,
  `oauth_key_dq` text COLLATE utf8mb4_unicode_ci,
  `oauth_key_qi` text COLLATE utf8mb4_unicode_ci,
  `oauth_key_oth` json DEFAULT NULL,
  `oauth_key_k` text COLLATE utf8mb4_unicode_ci,
  `oauth_key_use_enc` tinyint(1) DEFAULT '0',
  `oauth_key_use_sig` tinyint(1) DEFAULT '0',
  `oauth_key_use_wrap` tinyint(1) DEFAULT '0',
  `oauth_key_use_unwrap` tinyint(1) DEFAULT '0',
  `oauth_key_use_derive` tinyint(1) DEFAULT '0',
  `oauth_key_use_derive_bits` tinyint(1) DEFAULT '0',
  `oauth_key_use_encrypt` tinyint(1) DEFAULT '0',
  `oauth_key_use_decrypt` tinyint(1) DEFAULT '0',
  `oauth_key_use_sign` tinyint(1) DEFAULT '0',
  `oauth_key_use_verify` tinyint(1) DEFAULT '0',
  `oauth_key_use_wrap_key` tinyint(1) DEFAULT '0',
  `oauth_key_use_unwrap_key` tinyint(1) DEFAULT '0',
  `oauth_key_use_derive_key` tinyint(1) DEFAULT '0',
  `oauth_key_use_derive_bits_key` tinyint(1) DEFAULT '0',
  `processed_by_id` varchar(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `raw_validation_data` json DEFAULT NULL,
  `raw_callback_data` json DEFAULT NULL,
  `raw_stk_data` json DEFAULT NULL,
  `raw_query_data` json DEFAULT NULL,
  `raw_balance_data` json DEFAULT NULL,
  `raw_reversal_data` json DEFAULT NULL,
  `validation_result` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `callback_result` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `stk_result` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `query_result` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `balance_result` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reversal_result` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `validation_status` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `callback_status` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `stk_status` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `query_status` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `balance_status` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reversal_status` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `validation_message` text COLLATE utf8mb4_unicode_ci,
  `callback_message` text COLLATE utf8mb4_unicode_ci,
  `stk_message` text COLLATE utf8mb4_unicode_ci,
  `query_message` text COLLATE utf8mb4_unicode_ci,
  `balance_message` text COLLATE utf8mb4_unicode_ci,
  `reversal_message` text COLLATE utf8mb4_unicode_ci,
  `validation_code` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `callback_code` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `stk_code` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `query_code` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `balance_code` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reversal_code` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `validation_time` datetime DEFAULT NULL,
  `callback_time` datetime DEFAULT NULL,
  `stk_time` datetime DEFAULT NULL,
  `query_time` datetime DEFAULT NULL,
  `balance_time` datetime DEFAULT NULL,
  `reversal_time` datetime DEFAULT NULL,
  `validation_attempts` int DEFAULT '0',
  `callback_attempts` int DEFAULT '0',
  `stk_attempts` int DEFAULT '0',
  `query_attempts` int DEFAULT '0',
  `balance_attempts` int DEFAULT '0',
  `reversal_attempts` int DEFAULT '0',
  `validation_success` tinyint(1) DEFAULT '0',
  `callback_success` tinyint(1) DEFAULT '0',
  `stk_success` tinyint(1) DEFAULT '0',
  `query_success` tinyint(1) DEFAULT '0',
  `balance_success` tinyint(1) DEFAULT '0',
  `reversal_success` tinyint(1) DEFAULT '0',
  `validation_failure` tinyint(1) DEFAULT '0',
  `callback_failure` tinyint(1) DEFAULT '0',
  `stk_failure` tinyint(1) DEFAULT '0',
  `query_failure` tinyint(1) DEFAULT '0',
  `balance_failure` tinyint(1) DEFAULT '0',
  `reversal_failure` tinyint(1) DEFAULT '0',
  `validation_error` text COLLATE utf8mb4_unicode_ci,
  `validation_timeout` int DEFAULT NULL,
  `callback_timeout` int DEFAULT NULL,
  `stk_timeout` int DEFAULT NULL,
  `query_timeout` int DEFAULT NULL,
  `balance_timeout` int DEFAULT NULL,
  `reversal_timeout` int DEFAULT NULL,
  `validation_retry_backoff` tinyint(1) DEFAULT '0',
  `callback_retry_backoff` tinyint(1) DEFAULT '0',
  `stk_retry_backoff` tinyint(1) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

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

CREATE TABLE `notifications` (
  `id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `notification_type` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `title` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `message` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `channel` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_sent` tinyint(1) NOT NULL,
  `sent_at` datetime(6) DEFAULT NULL,
  `created_at` datetime(6) NOT NULL,
  `related_application` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `related_loan` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `related_loan_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `related_application_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `offer_letters`
--

CREATE TABLE `offer_letters` (
  `id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `offer_number` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `loan_amount` decimal(12,2) NOT NULL,
  `interest_rate` decimal(5,2) NOT NULL,
  `duration_days` int UNSIGNED NOT NULL,
  `processing_fee` decimal(12,2) NOT NULL,
  `total_amount` decimal(12,2) NOT NULL,
  `terms_conditions` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `special_conditions` longtext COLLATE utf8mb4_unicode_ci,
  `status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `valid_until` datetime(6) NOT NULL,
  `pdf_file` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `borrower_signature` longtext COLLATE utf8mb4_unicode_ci,
  `signed_at` datetime(6) DEFAULT NULL,
  `created_at` datetime(6) NOT NULL,
  `sent_at` datetime(6) DEFAULT NULL,
  `application` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `borrower` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `application_id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `borrower_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ;

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

--
-- Table structure for table `otp_verifications`
--

CREATE TABLE `otp_verifications` (
  `id` bigint NOT NULL,
  `otp_code` varchar(6) COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_used` tinyint(1) NOT NULL,
  `created_at` datetime(6) NOT NULL,
  `expires_at` datetime(6) NOT NULL,
  `user_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `payment_allocations`
--

CREATE TABLE `payment_allocations` (
  `id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `allocated_amount` decimal(12,2) NOT NULL,
  `created_at` datetime(6) NOT NULL,
  `loan_id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `mpesa_transaction_id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `repayment_id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `penalty_charges`
--

CREATE TABLE `penalty_charges` (
  `id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `penalty_rate` decimal(5,2) NOT NULL COMMENT 'Daily penalty rate used',
  `days_overdue` int UNSIGNED NOT NULL COMMENT 'Number of days overdue when penalty was applied',
  `outstanding_amount` decimal(12,2) NOT NULL COMMENT 'Outstanding amount when penalty was calculated',
  `applied_date` datetime(6) NOT NULL,
  `loan_id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `portfolio_assignments`
--

CREATE TABLE `portfolio_assignments` (
  `id` int NOT NULL,
  `assignment_number` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `portfolio_manager_id` char(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `client_id` char(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `assigned_date` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'active',
  `notes` longtext COLLATE utf8mb4_unicode_ci,
  `created_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updated_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `assigned_by_id` char(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `unassigned_date` datetime DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  `reason` text COLLATE utf8mb4_unicode_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `portfolio_performance`
--

CREATE TABLE `portfolio_performance` (
  `id` bigint NOT NULL,
  `portfolio_manager_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `date` date NOT NULL,
  `total_loans` int DEFAULT '0',
  `active_loans` int DEFAULT '0',
  `overdue_loans` int DEFAULT '0',
  `total_amount` decimal(15,2) DEFAULT '0.00',
  `collected_amount` decimal(15,2) DEFAULT '0.00',
  `outstanding_amount` decimal(15,2) DEFAULT '0.00',
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL,
  `verified_by_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `receipts`
--

CREATE TABLE `receipts` (
  `id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `receipt_number` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `amount_paid` decimal(12,2) NOT NULL,
  `payment_method` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_date` datetime(6) NOT NULL,
  `previous_balance` decimal(12,2) NOT NULL,
  `new_balance` decimal(12,2) NOT NULL,
  `pdf_file` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` datetime(6) NOT NULL,
  `borrower` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `loan` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `repayment` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `repayment_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `loan_id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `borrower_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `registration_fees`
--

CREATE TABLE `registration_fees` (
  `id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `product_type` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `fee_name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `description` longtext COLLATE utf8mb4_unicode_ci,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `effective_from` datetime(6) NOT NULL,
  `effective_to` datetime(6) DEFAULT NULL,
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL,
  `created_by_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `registration_fee_payments`
--

CREATE TABLE `registration_fee_payments` (
  `id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `receipt_number` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `customer_id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `registration_fee_id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `amount_paid` decimal(10,2) NOT NULL,
  `payment_method` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_date` datetime(6) NOT NULL,
  `transaction_reference` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `payment_notes` longtext COLLATE utf8mb4_unicode_ci,
  `related_loan_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `related_application_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `processed_by_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` datetime(6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `repayments`
--

CREATE TABLE `repayments` (
  `id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `payment_method` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `mpesa_transaction_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `mpesa_phone_number` varchar(17) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `receipt_number` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `payment_date` datetime(6) NOT NULL,
  `created_at` datetime(6) NOT NULL,
  `loan` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `loan_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `payment_source` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `reports_notification`
--

CREATE TABLE `reports_notification` (
  `id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `notification_type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `title` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `message` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `channel` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_sent` tinyint(1) DEFAULT '0',
  `sent_at` datetime(6) DEFAULT NULL,
  `related_loan` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `related_application` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` datetime(6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `report_schedules`
--

CREATE TABLE `report_schedules` (
  `id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `report_type` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `frequency` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `next_run` datetime(6) NOT NULL,
  `last_run` datetime(6) DEFAULT NULL,
  `parameters` json NOT NULL,
  `created_at` datetime(6) NOT NULL,
  `created_by_id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `report_schedules_recipients`
--

CREATE TABLE `report_schedules_recipients` (
  `id` bigint NOT NULL,
  `reportschedule_id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `customuser_id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `report_templates`
--

CREATE TABLE `report_templates` (
  `id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `report_type` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` longtext COLLATE utf8mb4_unicode_ci,
  `template_file` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL,
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `role_permissions`
--

CREATE TABLE `role_permissions` (
  `id` bigint NOT NULL,
  `role` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `module` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `action` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_allowed` tinyint(1) NOT NULL,
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `rollover_requests`
--

CREATE TABLE `rollover_requests` (
  `id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `requested_duration` int UNSIGNED NOT NULL,
  `reason` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `rollover_fee` decimal(12,2) DEFAULT NULL,
  `status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `reviewed_at` datetime(6) DEFAULT NULL,
  `review_notes` longtext COLLATE utf8mb4_unicode_ci,
  `requested_at` datetime(6) NOT NULL,
  `borrower` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `loan` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reviewed_by` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `loan_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `borrower_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reviewed_by_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `requested_amount` decimal(12,2) NOT NULL DEFAULT '0.00',
  `requested_interest_rate` decimal(5,2) DEFAULT NULL,
  `requested_processing_fee` decimal(12,2) DEFAULT NULL,
  `rollover_date` date DEFAULT NULL COMMENT 'Preferred rollover date'
) ;

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

--
-- Table structure for table `sms_templates`
--

CREATE TABLE `sms_templates` (
  `id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `template_type` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `message_template` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_active` tinyint(1) NOT NULL,
  `is_default` tinyint(1) NOT NULL,
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `system_settings`
--

CREATE TABLE `system_settings` (
  `id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `default_interest_rate` decimal(5,2) NOT NULL,
  `default_processing_fee` decimal(5,2) NOT NULL,
  `default_late_penalty` decimal(5,2) NOT NULL,
  `rollover_fee_percentage` decimal(5,2) NOT NULL,
  `max_rollover_days` int UNSIGNED NOT NULL,
  `max_rollover_count` int UNSIGNED NOT NULL,
  `auto_approval_enabled` tinyint(1) NOT NULL,
  `auto_approval_min_score` int UNSIGNED NOT NULL,
  `auto_approval_max_amount` decimal(12,2) NOT NULL,
  `email_notifications_enabled` tinyint(1) NOT NULL,
  `sms_notifications_enabled` tinyint(1) NOT NULL,
  `mpesa_business_shortcode` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `mpesa_passkey` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `mpesa_environment` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL
) ;

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

--
-- Table structure for table `unconfirmed_payments`
--

CREATE TABLE `unconfirmed_payments` (
  `id` char(32) NOT NULL,
  `mpesa_transaction_id` char(32) NOT NULL,
  `payment_phone` varchar(17) DEFAULT NULL,
  `payment_id_number` varchar(50) DEFAULT NULL,
  `suggested_borrower_id` char(32) DEFAULT NULL,
  `match_type` varchar(20) NOT NULL DEFAULT 'none',
  `status` varchar(20) NOT NULL DEFAULT 'pending',
  `approved_by_id` char(32) DEFAULT NULL,
  `approved_at` datetime(6) DEFAULT NULL,
  `rejection_reason` longtext,
  `admin_notes` longtext,
  `notes` longtext,
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

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

CREATE TABLE `users` (
  `password` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
  `last_login` datetime(6) DEFAULT NULL,
  `is_superuser` tinyint(1) NOT NULL,
  `username` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `first_name` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `last_name` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_staff` tinyint(1) NOT NULL,
  `is_active` tinyint(1) NOT NULL,
  `date_joined` datetime(6) NOT NULL,
  `id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(254) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `phone_number` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `role` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `id_number` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `kra_pin` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `gender` varchar(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `nationality` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `marital_status` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `address` longtext COLLATE utf8mb4_unicode_ci,
  `city` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `county` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `postal_code` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `employer` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `business_name` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `business_type` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `monthly_income` decimal(12,2) DEFAULT NULL,
  `emergency_contact_name` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `emergency_contact_phone` varchar(17) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `emergency_contact_relationship` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `pin` varchar(6) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_phone_verified` tinyint(1) NOT NULL,
  `is_email_verified` tinyint(1) NOT NULL,
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL,
  `last_login_at` datetime(6) DEFAULT NULL,
  `id_document` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `selfie` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `utility_bill` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `crb_report` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `bank_statement` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `physical_address` text COLLATE utf8mb4_unicode_ci,
  `postal_address` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `country` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT 'Kenya',
  `business_address` text COLLATE utf8mb4_unicode_ci,
  `business_registration_number` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `business_license` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `tax_certificate` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `other_documents` json DEFAULT NULL,
  `is_verified` tinyint(1) DEFAULT '0',
  `verification_date` datetime(6) DEFAULT NULL,
  `verified_by` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `verified_by_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `logbook` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `title_deed` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `signature` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `nickname` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `capital_invested` decimal(15,2) DEFAULT NULL,
  `cp_domicile` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `declaration_name` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `domicile` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `expected_turnover` decimal(15,2) DEFAULT NULL,
  `guarantor_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `guarantor_mobile` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `guarantor_name` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `guarantor_residence` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `guarantor_tel` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `other_business_type` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `personal_pin` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `physical_location` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `place_of_birth` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `postal_code_business` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `recommender_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `recommender_mobile` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `recommender_name` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `recommender_residence` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `recommender_tel` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `registration_date` date DEFAULT NULL,
  `source_of_funds` text COLLATE utf8mb4_unicode_ci,
  `start_time` time DEFAULT NULL,
  `profile_image` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `portfolio_manager_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `assigned_date` datetime(6) DEFAULT NULL,
  `assigned_by_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `credit_limit` decimal(12,2) DEFAULT NULL,
  `credit_score` int DEFAULT NULL,
  `risk_rating` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `kyc_status` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT 'pending',
  `kyc_verified_at` datetime(6) DEFAULT NULL,
  `kyc_verified_by_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `account_status` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT 'active',
  `suspension_reason` text COLLATE utf8mb4_unicode_ci,
  `suspended_at` datetime(6) DEFAULT NULL,
  `suspended_by_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_by_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `updated_by_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `deleted_at` datetime(6) DEFAULT NULL,
  `deleted_by_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `last_activity_at` datetime(6) DEFAULT NULL,
  `login_attempts` int DEFAULT '0',
  `locked_until` datetime(6) DEFAULT NULL,
  `password_changed_at` datetime(6) DEFAULT NULL,
  `email_verified_at` datetime(6) DEFAULT NULL,
  `phone_verified_at` datetime(6) DEFAULT NULL,
  `terms_accepted_at` datetime(6) DEFAULT NULL,
  `privacy_accepted_at` datetime(6) DEFAULT NULL,
  `marketing_consent` tinyint(1) DEFAULT '0',
  `notification_preferences` json DEFAULT NULL,
  `language_preference` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT 'en',
  `timezone` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT 'Africa/Nairobi',
  `two_factor_enabled` tinyint(1) DEFAULT '0',
  `two_factor_secret` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `backup_codes` json DEFAULT NULL,
  `api_key` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `api_key_created_at` datetime(6) DEFAULT NULL,
  `referral_code` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `referred_by_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `referral_bonus_earned` decimal(10,2) DEFAULT '0.00',
  `total_loans_taken` int DEFAULT '0',
  `total_amount_borrowed` decimal(15,2) DEFAULT '0.00',
  `total_amount_repaid` decimal(15,2) DEFAULT '0.00',
  `current_outstanding` decimal(15,2) DEFAULT '0.00',
  `default_count` int DEFAULT '0',
  `last_loan_date` date DEFAULT NULL,
  `next_eligible_date` date DEFAULT NULL,
  `employment_status` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `employment_start_date` date DEFAULT NULL,
  `salary_account_bank` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `salary_account_number` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `next_of_kin_name` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `next_of_kin_phone` varchar(17) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `next_of_kin_relationship` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `next_of_kin_id_number` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `bank_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `bank_branch` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `account_number` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `account_name` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `mpesa_number` varchar(17) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `preferred_payment_method` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT 'mpesa',
  `registration_fee_amount` decimal(10,2) DEFAULT NULL,
  `registration_fee_paid` tinyint(1) DEFAULT '0',
  `registration_fee_payment_date` datetime DEFAULT NULL,
  `registration_fee_payment_method` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `registration_fee_receipt_number` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `registration_fee_notes` longtext COLLATE utf8mb4_unicode_ci,
  `branch_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `approval_reason` text COLLATE utf8mb4_unicode_ci,
  `approved_at` datetime DEFAULT NULL,
  `approved_by_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `rejected_at` datetime DEFAULT NULL,
  `rejected_by_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `rejection_reason` text COLLATE utf8mb4_unicode_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `users_accessible_branches`
--

CREATE TABLE `users_accessible_branches` (
  `id` bigint NOT NULL,
  `customuser_id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `branch_id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `users_branch`
--

CREATE TABLE `users_branch` (
  `id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `code` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `address` longtext COLLATE utf8mb4_unicode_ci,
  `phone_number` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(254) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_main_branch` tinyint(1) NOT NULL DEFAULT '0',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL,
  `mpesa_shortcode` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `mpesa_consumer_key` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `mpesa_consumer_secret` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `mpesa_passkey` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `users_customuser`
--

CREATE TABLE `users_customuser` (
  `id` bigint NOT NULL,
  `password` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
  `last_login` datetime(6) DEFAULT NULL,
  `is_superuser` tinyint(1) NOT NULL,
  `username` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `first_name` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `last_name` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(254) COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_staff` tinyint(1) NOT NULL,
  `is_active` tinyint(1) NOT NULL,
  `date_joined` datetime(6) NOT NULL,
  `phone_number` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `national_id` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `role` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `monthly_income` decimal(10,2) DEFAULT NULL,
  `logbook` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `signature` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `id_front` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `id_back` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `capital_invested` decimal(15,2) DEFAULT NULL,
  `county` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sub_county` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ward` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `branch_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `portfolio_manager_id` bigint DEFAULT NULL,
  `profile_image` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_email_verified` tinyint(1) NOT NULL DEFAULT '0',
  `is_phone_verified` tinyint(1) NOT NULL DEFAULT '0',
  `email_verification_token` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `phone_verification_code` varchar(6) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `registration_fee_amount` decimal(10,2) DEFAULT NULL,
  `registration_fee_paid` tinyint(1) NOT NULL DEFAULT '0',
  `registration_fee_paid_at` datetime(6) DEFAULT NULL,
  `unassigned_date` datetime(6) DEFAULT NULL,
  `approval_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
  `approved_by_id` bigint DEFAULT NULL,
  `approved_at` datetime(6) DEFAULT NULL,
  `approval_reason` longtext COLLATE utf8mb4_unicode_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `users_customuser_groups`
--

CREATE TABLE `users_customuser_groups` (
  `id` int NOT NULL,
  `customuser_id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `group_id` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `users_customuser_user_permissions`
--

CREATE TABLE `users_customuser_user_permissions` (
  `id` int NOT NULL,
  `customuser_id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `permission_id` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `users_groups`
--

CREATE TABLE `users_groups` (
  `id` int NOT NULL,
  `customuser_id` char(36) COLLATE utf8mb4_unicode_ci NOT NULL,
  `group_id` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `users_user_permissions`
--

CREATE TABLE `users_user_permissions` (
  `id` int NOT NULL,
  `customuser_id` char(36) COLLATE utf8mb4_unicode_ci NOT NULL,
  `permission_id` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `user_access_logs`
--

CREATE TABLE `user_access_logs` (
  `id` bigint NOT NULL,
  `action` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `module` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `object_type` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `object_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `ip_address` char(39) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_agent` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `session_id` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `accessed_at` datetime(6) NOT NULL,
  `response_time` double DEFAULT NULL,
  `status_code` int DEFAULT NULL,
  `user_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `user_permissions`
--

CREATE TABLE `user_permissions` (
  `id` int NOT NULL,
  `user_id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `module` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `action` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_allowed` tinyint(1) NOT NULL DEFAULT '0',
  `permission_id` int NOT NULL DEFAULT '0',
  `created_at` datetime(6) DEFAULT CURRENT_TIMESTAMP(6),
  `granted_by_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reason` text COLLATE utf8mb4_unicode_ci,
  `expires_at` datetime DEFAULT NULL,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `user_permissions_backup_20251014_173638`
--

CREATE TABLE `user_permissions_backup_20251014_173638` (
  `id` int NOT NULL DEFAULT '0',
  `user_id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `module` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `action` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_allowed` tinyint(1) NOT NULL DEFAULT '0',
  `permission_id` int NOT NULL,
  `created_at` datetime(6) DEFAULT CURRENT_TIMESTAMP(6),
  `granted_by_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `utils_auditlog`
--

CREATE TABLE `utils_auditlog` (
  `id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `action` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `model_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `object_id` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `ip_address` char(39) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_agent` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` datetime(6) NOT NULL,
  `user_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `utils_document`
--

CREATE TABLE `utils_document` (
  `id` bigint NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `file` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `document_type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL,
  `description` longtext COLLATE utf8mb4_unicode_ci,
  `thumbnail` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `file_size` int UNSIGNED NOT NULL,
  `mime_type` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_public` tinyint(1) NOT NULL,
  `uploaded_by` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `uploaded_by_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ;

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

--
-- Table structure for table `utils_documentshare`
--

CREATE TABLE `utils_documentshare` (
  `id` bigint NOT NULL,
  `shared_at` datetime(6) NOT NULL,
  `message` longtext COLLATE utf8mb4_unicode_ci,
  `is_read` tinyint(1) NOT NULL,
  `document` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `shared_by` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `shared_with` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `shared_with_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `document_id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `utils_documenttag`
--

CREATE TABLE `utils_documenttag` (
  `id` bigint NOT NULL,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` datetime(6) NOT NULL,
  `created_by` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `utils_document_tags`
--

CREATE TABLE `utils_document_tags` (
  `id` bigint NOT NULL,
  `document` bigint DEFAULT NULL,
  `documenttag` bigint DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `utils_notification`
--

CREATE TABLE `utils_notification` (
  `id` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `notification_type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `title` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `message` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `priority` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` datetime(6) NOT NULL,
  `read_at` datetime(6) DEFAULT NULL,
  `action_url` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `expires_at` datetime(6) DEFAULT NULL,
  `icon` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `loan_app_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `related_loan_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `loan_id` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `action_required` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Whether this notification requires immediate action',
  `alert_data` json DEFAULT NULL COMMENT 'Additional alert data in JSON format'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `utils_systemsetting`
--

CREATE TABLE `utils_systemsetting` (
  `id` bigint NOT NULL,
  `key` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `value` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `category` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` longtext COLLATE utf8mb4_unicode_ci,
  `is_public` tinyint(1) NOT NULL,
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `auth_group`
--
ALTER TABLE `auth_group`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `name` (`name`);

--
-- Indexes for table `auth_group_permissions`
--
ALTER TABLE `auth_group_permissions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `auth_group_permissions_group_id_permission_id_0cd325b0_uniq` (`group_id`,`permission_id`),
  ADD KEY `auth_group_permissio_permission_id_84c5c92e_fk_auth_perm` (`permission_id`);

--
-- Indexes for table `auth_permission`
--
ALTER TABLE `auth_permission`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `auth_permission_content_type_id_codename_01ab375a_uniq` (`content_type_id`,`codename`);

--
-- Indexes for table `cache_table`
--
ALTER TABLE `cache_table`
  ADD PRIMARY KEY (`cache_key`),
  ADD KEY `cache_table_expires` (`expires`);

--
-- Indexes for table `customer_requests`
--
ALTER TABLE `customer_requests`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `request_number` (`request_number`),
  ADD KEY `customer_requests_customer_id_idx` (`customer_id`),
  ADD KEY `customer_requests_status_idx` (`status`),
  ADD KEY `customer_requests_created_at_idx` (`created_at`);

--
-- Indexes for table `default_role_permissions`
--
ALTER TABLE `default_role_permissions`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `django_admin_log`
--
ALTER TABLE `django_admin_log`
  ADD PRIMARY KEY (`id`),
  ADD KEY `django_admin_log_content_type_id_c4bce8eb_fk_django_co` (`content_type_id`),
  ADD KEY `django_admin_log_user_id_c564eba6_fk_users_id` (`user_id`);

--
-- Indexes for table `django_content_type`
--
ALTER TABLE `django_content_type`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `django_content_type_app_label_model_76bd3d3b_uniq` (`app_label`,`model`);

--
-- Indexes for table `django_migrations`
--
ALTER TABLE `django_migrations`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `django_session`
--
ALTER TABLE `django_session`
  ADD PRIMARY KEY (`session_key`),
  ADD KEY `django_session_expire_date_a5c62663` (`expire_date`);

--
-- Indexes for table `document_templates`
--
ALTER TABLE `document_templates`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `email_templates`
--
ALTER TABLE `email_templates`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `expenses`
--
ALTER TABLE `expenses`
  ADD PRIMARY KEY (`id`),
  ADD KEY `expenses_branch__f377ac_idx` (`branch_id`,`expense_date`),
  ADD KEY `expenses_status_0e583e_idx` (`status`,`expense_date`),
  ADD KEY `expenses_categor_a8c11b_idx` (`category`,`expense_date`),
  ADD KEY `expenses_staff_i_a58db8_idx` (`staff_id`,`expense_date`),
  ADD KEY `expenses_expense_loan_id` (`loan_id`),
  ADD KEY `expenses_expense_staff_id` (`staff_id`),
  ADD KEY `expenses_expense_approved_by_id` (`approved_by_id`),
  ADD KEY `expenses_expense_branch_id` (`branch_id`);

--
-- Indexes for table `generated_reports`
--
ALTER TABLE `generated_reports`
  ADD PRIMARY KEY (`id`),
  ADD KEY `generated_reports_generated_by_id_9393ec13_fk_users_id` (`generated_by_id`);

--
-- Indexes for table `loans`
--
ALTER TABLE `loans`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `loan_number` (`loan_number`),
  ADD UNIQUE KEY `application_id` (`application`),
  ADD KEY `loans_borrower_id_aaffb8a4_fk_users_id` (`borrower`),
  ADD KEY `loans_original_loan_id_5b15332d_fk_loans_id` (`original_loan`),
  ADD KEY `idx_loans_borrower` (`borrower_id`),
  ADD KEY `idx_loans_status` (`status`),
  ADD KEY `idx_loans_is_deleted` (`is_deleted`),
  ADD KEY `idx_loans_deleted_by` (`deleted_by_id`),
  ADD KEY `idx_loans_original_loan` (`original_loan_id`);

--
-- Indexes for table `loans_loan`
--
ALTER TABLE `loans_loan`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `loan_number` (`loan_number`),
  ADD KEY `loans_loan_borrower_id` (`borrower_id`),
  ADD KEY `loans_loan_loan_product_id` (`loan_product_id`),
  ADD KEY `loans_loan_approved_by_id` (`approved_by_id`),
  ADD KEY `loans_loan_disbursed_by_id` (`disbursed_by_id`),
  ADD KEY `loans_loan_deleted_by_id` (`deleted_by_id`);

--
-- Indexes for table `loans_loanapplication`
--
ALTER TABLE `loans_loanapplication`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `application_number` (`application_number`);

--
-- Indexes for table `loans_loanproduct`
--
ALTER TABLE `loans_loanproduct`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `loans_repayment`
--
ALTER TABLE `loans_repayment`
  ADD PRIMARY KEY (`id`),
  ADD KEY `loans_repayment_loan_id` (`loan_id`),
  ADD KEY `loans_repayment_recorded_by_id` (`recorded_by_id`),
  ADD KEY `loans_repayment_mpesa_transaction_id` (`mpesa_transaction_id`);

--
-- Indexes for table `loan_applications`
--
ALTER TABLE `loan_applications`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `application_number` (`application_number`),
  ADD KEY `loan_applications_borrower_id_80b5f00e_fk_users_id` (`borrower`),
  ADD KEY `loan_applications_reviewed_by_id_761723e9_fk_users_id` (`reviewed_by`),
  ADD KEY `loan_applications_loan_product_id_46294b99_fk_loan_products_id` (`loan_product`),
  ADD KEY `idx_loan_app_status` (`status`);

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

--
-- Indexes for table `loan_scoring`
--
ALTER TABLE `loan_scoring`
  ADD PRIMARY KEY (`id`),
  ADD KEY `loan_scoring_user_id_c41ac245_fk_users_id` (`user`),
  ADD KEY `idx_loan_scoring_user_id` (`user_id`),
  ADD KEY `idx_loan_scoring_user` (`user_id`);

--
-- Indexes for table `loan_statements`
--
ALTER TABLE `loan_statements`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `statement_number` (`statement_number`),
  ADD KEY `loan_statements_borrower_id_b430163b_fk_users_id` (`borrower`),
  ADD KEY `loan_statements_loan_id_2bf4f203_fk_loans_id` (`loan`),
  ADD KEY `loan_statements_loan_id_idx` (`loan_id`),
  ADD KEY `idx_loan_statements_borrower` (`borrower_id`);

--
-- Indexes for table `mpesa_access_tokens`
--
ALTER TABLE `mpesa_access_tokens`
  ADD PRIMARY KEY (`id`),
  ADD KEY `mpesa_access_tokens_configuration_id_de2bf233_fk_mpesa_con` (`configuration_id`);

--
-- Indexes for table `mpesa_callbacks`
--
ALTER TABLE `mpesa_callbacks`
  ADD PRIMARY KEY (`id`),
  ADD KEY `mpesa_callbacks_transaction_id_426bc1e5_fk_mpesa_transactions_id` (`transaction_id`);

--
-- Indexes for table `mpesa_configurations`
--
ALTER TABLE `mpesa_configurations`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `mpesa_transactions`
--
ALTER TABLE `mpesa_transactions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `mpesa_transactions_loan_id_ab4152e1_fk_loans_id` (`loan`),
  ADD KEY `mpesa_transactions_repayment_id_903b355f_fk_repayments_id` (`repayment`);

--
-- Indexes for table `notifications`
--
ALTER TABLE `notifications`
  ADD PRIMARY KEY (`id`),
  ADD KEY `notifications_related_application__f2562b71_fk_loan_appl` (`related_application`),
  ADD KEY `notifications_related_loan_id_e196b54c_fk_loans_id` (`related_loan`),
  ADD KEY `notifications_user_id_468e288d_fk_users_id` (`user`),
  ADD KEY `notifications_related_loan_id_idx` (`related_loan_id`),
  ADD KEY `notifications_related_application_id_idx` (`related_application_id`);

--
-- Indexes for table `offer_letters`
--
ALTER TABLE `offer_letters`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `offer_number` (`offer_number`),
  ADD UNIQUE KEY `application_id` (`application`),
  ADD KEY `offer_letters_borrower_id_25187cf5_fk_users_id` (`borrower`),
  ADD KEY `offer_letters_application_id_idx` (`application_id`),
  ADD KEY `idx_offer_letters_borrower` (`borrower_id`);

--
-- Indexes for table `otp_verifications`
--
ALTER TABLE `otp_verifications`
  ADD PRIMARY KEY (`id`),
  ADD KEY `otp_verifications_user_id_3340c576_fk_users_id` (`user_id`);

--
-- Indexes for table `payment_allocations`
--
ALTER TABLE `payment_allocations`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `repayment_id` (`repayment_id`),
  ADD KEY `payment_allocations_loan_id_e9be27ef_fk_loans_id` (`loan_id`),
  ADD KEY `payment_allocations_mpesa_transaction_id_f31fa711_fk_mpesa_tra` (`mpesa_transaction_id`);

--
-- Indexes for table `penalty_charges`
--
ALTER TABLE `penalty_charges`
  ADD PRIMARY KEY (`id`),
  ADD KEY `penalty_charges_loan_id_idx` (`loan_id`),
  ADD KEY `penalty_charges_applied_date_idx` (`applied_date`);

--
-- Indexes for table `portfolio_assignments`
--
ALTER TABLE `portfolio_assignments`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `assignment_number` (`assignment_number`),
  ADD KEY `portfolio_assignments_portfolio_manager_id_idx` (`portfolio_manager_id`),
  ADD KEY `portfolio_assignments_client_id_idx` (`client_id`),
  ADD KEY `portfolio_assignments_status_idx` (`status`),
  ADD KEY `portfolio_assignments_assigned_date_idx` (`assigned_date`);

--
-- Indexes for table `portfolio_performance`
--
ALTER TABLE `portfolio_performance`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `date` (`date`),
  ADD KEY `idx_portfolio_manager_id` (`portfolio_manager_id`),
  ADD KEY `portfolio_performance_verified_by_id_idx` (`verified_by_id`);

--
-- Indexes for table `receipts`
--
ALTER TABLE `receipts`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `receipt_number` (`receipt_number`),
  ADD UNIQUE KEY `repayment_id` (`repayment`),
  ADD KEY `receipts_borrower_id_e77bb056_fk_users_id` (`borrower`),
  ADD KEY `receipts_loan_id_b4736316_fk_loans_id` (`loan`),
  ADD KEY `receipts_repayment_id_idx` (`repayment_id`),
  ADD KEY `receipts_loan_id_idx` (`loan_id`);

--
-- Indexes for table `registration_fees`
--
ALTER TABLE `registration_fees`
  ADD PRIMARY KEY (`id`),
  ADD KEY `registration_fees_product_type_idx` (`product_type`),
  ADD KEY `registration_fees_is_active_idx` (`is_active`);

--
-- Indexes for table `registration_fee_payments`
--
ALTER TABLE `registration_fee_payments`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `receipt_number` (`receipt_number`),
  ADD KEY `registration_fee_payments_customer_id_idx` (`customer_id`),
  ADD KEY `registration_fee_payments_payment_date_idx` (`payment_date`),
  ADD KEY `registration_fee_payments_registration_fee_id_idx` (`registration_fee_id`);

--
-- Indexes for table `repayments`
--
ALTER TABLE `repayments`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `receipt_number` (`receipt_number`),
  ADD KEY `repayments_loan_id_58f9f13b_fk_loans_id` (`loan`);

--
-- Indexes for table `reports_notification`
--
ALTER TABLE `reports_notification`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `report_schedules`
--
ALTER TABLE `report_schedules`
  ADD PRIMARY KEY (`id`),
  ADD KEY `report_schedules_report_type_idx` (`report_type`),
  ADD KEY `report_schedules_is_active_idx` (`is_active`),
  ADD KEY `report_schedules_next_run_idx` (`next_run`);

--
-- Indexes for table `report_schedules_recipients`
--
ALTER TABLE `report_schedules_recipients`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `report_schedules_recipients_unique` (`reportschedule_id`,`customuser_id`),
  ADD KEY `report_schedules_recipients_reportschedule_id_idx` (`reportschedule_id`),
  ADD KEY `report_schedules_recipients_user_id_idx` (`customuser_id`);

--
-- Indexes for table `report_templates`
--
ALTER TABLE `report_templates`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `role_permissions`
--
ALTER TABLE `role_permissions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `role_permissions_role_module_action_b947a1b8_uniq` (`role`,`module`,`action`);

--
-- Indexes for table `rollover_requests`
--
ALTER TABLE `rollover_requests`
  ADD PRIMARY KEY (`id`),
  ADD KEY `rollover_requests_borrower_id_a13a7c75_fk_users_id` (`borrower`),
  ADD KEY `rollover_requests_loan_id_427ee293_fk_loans_id` (`loan`),
  ADD KEY `rollover_requests_reviewed_by_id_ffce375d_fk_users_id` (`reviewed_by`);

--
-- Indexes for table `sms_templates`
--
ALTER TABLE `sms_templates`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `system_settings`
--
ALTER TABLE `system_settings`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `unconfirmed_payments`
--
ALTER TABLE `unconfirmed_payments`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `mpesa_transaction_id` (`mpesa_transaction_id`),
  ADD KEY `unconfirmed_payments_mpesa_transaction_id_idx` (`mpesa_transaction_id`),
  ADD KEY `unconfirmed_payments_suggested_borrower_id_idx` (`suggested_borrower_id`),
  ADD KEY `unconfirmed_payments_approved_by_id_idx` (`approved_by_id`),
  ADD KEY `unconfirmed_payments_status_idx` (`status`),
  ADD KEY `unconfirmed_payments_match_type_idx` (`match_type`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `username` (`username`),
  ADD UNIQUE KEY `phone_number` (`phone_number`),
  ADD UNIQUE KEY `email` (`email`),
  ADD UNIQUE KEY `id_number` (`id_number`),
  ADD UNIQUE KEY `email_2` (`email`),
  ADD KEY `fk_users_verified_by` (`verified_by_id`),
  ADD KEY `idx_users_role` (`role`),
  ADD KEY `idx_users_status` (`status`),
  ADD KEY `idx_users_phone` (`phone_number`),
  ADD KEY `idx_users_email` (`email`),
  ADD KEY `idx_users_portfolio_manager` (`portfolio_manager_id`),
  ADD KEY `fk_users_assigned_by` (`assigned_by_id`),
  ADD KEY `fk_users_kyc_verified_by` (`kyc_verified_by_id`),
  ADD KEY `fk_users_suspended_by` (`suspended_by_id`),
  ADD KEY `fk_users_created_by` (`created_by_id`),
  ADD KEY `fk_users_updated_by` (`updated_by_id`),
  ADD KEY `fk_users_deleted_by` (`deleted_by_id`),
  ADD KEY `fk_users_referred_by` (`referred_by_id`),
  ADD KEY `idx_users_verified_by` (`verified_by_id`),
  ADD KEY `idx_users_assigned_date` (`assigned_date`),
  ADD KEY `idx_users_credit_score` (`credit_score`),
  ADD KEY `idx_users_kyc_status` (`kyc_status`),
  ADD KEY `idx_users_account_status` (`account_status`),
  ADD KEY `idx_users_last_activity` (`last_activity_at`),
  ADD KEY `idx_users_referral_code` (`referral_code`),
  ADD KEY `idx_users_mpesa_number` (`mpesa_number`),
  ADD KEY `users_branch_id_fk` (`branch_id`);

--
-- Indexes for table `users_accessible_branches`
--
ALTER TABLE `users_accessible_branches`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `users_customuser_accessi_customuser_id_branch_id_unique` (`customuser_id`,`branch_id`),
  ADD KEY `users_customuser_accessible_branches_branch_id_fk` (`branch_id`);

--
-- Indexes for table `users_branch`
--
ALTER TABLE `users_branch`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `code` (`code`);

--
-- Indexes for table `users_customuser`
--
ALTER TABLE `users_customuser`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `username` (`username`),
  ADD KEY `users_customuser_branch_id` (`branch_id`),
  ADD KEY `users_customuser_portfolio_manager_id` (`portfolio_manager_id`),
  ADD KEY `users_customuser_approved_by_id` (`approved_by_id`);

--
-- Indexes for table `users_customuser_groups`
--
ALTER TABLE `users_customuser_groups`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `users_customuser_groups_customuser_id_group_id` (`customuser_id`,`group_id`),
  ADD KEY `idx_users_customuser_groups_customuser_id` (`customuser_id`),
  ADD KEY `idx_users_customuser_groups_group_id` (`group_id`);

--
-- Indexes for table `users_customuser_user_permissions`
--
ALTER TABLE `users_customuser_user_permissions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `users_customuser_user_permissions_customuser_id_permission_id` (`customuser_id`,`permission_id`),
  ADD KEY `idx_users_customuser_user_permissions_customuser_id` (`customuser_id`),
  ADD KEY `idx_users_customuser_user_permissions_permission_id` (`permission_id`);

--
-- Indexes for table `users_groups`
--
ALTER TABLE `users_groups`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `users_groups_customuser_id_group_id_uniq` (`customuser_id`,`group_id`),
  ADD KEY `users_groups_customuser_id` (`customuser_id`),
  ADD KEY `users_groups_group_id` (`group_id`);

--
-- Indexes for table `users_user_permissions`
--
ALTER TABLE `users_user_permissions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `users_user_permissions_customuser_id_permission_id_uniq` (`customuser_id`,`permission_id`),
  ADD KEY `users_user_permissions_customuser_id` (`customuser_id`),
  ADD KEY `users_user_permissions_permission_id` (`permission_id`);

--
-- Indexes for table `user_access_logs`
--
ALTER TABLE `user_access_logs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_access_user_id_416563_idx` (`user_id`,`accessed_at`),
  ADD KEY `user_access_action_a90b01_idx` (`action`,`accessed_at`),
  ADD KEY `user_access_module_47fa23_idx` (`module`,`accessed_at`);

--
-- Indexes for table `user_permissions`
--
ALTER TABLE `user_permissions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_user_permission` (`user_id`,`module`,`action`),
  ADD KEY `idx_user_permissions_user_id` (`user_id`),
  ADD KEY `idx_user_permissions_permission_id` (`permission_id`),
  ADD KEY `idx_user_expires` (`user_id`,`expires_at`),
  ADD KEY `idx_module_action` (`module`,`action`);

--
-- Indexes for table `utils_auditlog`
--
ALTER TABLE `utils_auditlog`
  ADD PRIMARY KEY (`id`),
  ADD KEY `utils_auditlog_user_id_bba0ec05_fk_users_id` (`user_id`);

--
-- Indexes for table `utils_document`
--
ALTER TABLE `utils_document`
  ADD PRIMARY KEY (`id`),
  ADD KEY `utils_document_uploaded_by_id_c44b6c29_fk_users_id` (`uploaded_by`),
  ADD KEY `idx_utils_document_uploaded_by` (`uploaded_by_id`),
  ADD KEY `idx_document_uploaded_by` (`uploaded_by_id`),
  ADD KEY `idx_document_type` (`document_type`);

--
-- Indexes for table `utils_documentshare`
--
ALTER TABLE `utils_documentshare`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `utils_documentshare_document_id_shared_with_id_5c637053_uniq` (`document`,`shared_with`),
  ADD KEY `utils_documentshare_shared_by_id_00694da5_fk_users_id` (`shared_by`),
  ADD KEY `utils_documentshare_shared_with_id_65546475_fk_users_id` (`shared_with`),
  ADD KEY `idx_utils_documentshare_shared_with` (`shared_with_id`),
  ADD KEY `idx_documentshare_shared_with` (`shared_with_id`),
  ADD KEY `idx_utils_documentshare_document` (`document_id`),
  ADD KEY `idx_documentshare_document` (`document_id`);

--
-- Indexes for table `utils_documenttag`
--
ALTER TABLE `utils_documenttag`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `name` (`name`),
  ADD KEY `utils_documenttag_created_by_id_7d2b3d90_fk_users_id` (`created_by`);

--
-- Indexes for table `utils_document_tags`
--
ALTER TABLE `utils_document_tags`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `utils_document_tags_document_id_documenttag_id_43d6f1f7_uniq` (`document`,`documenttag`),
  ADD KEY `utils_document_tags_documenttag_id_6b2c49ec_fk_utils_doc` (`documenttag`);

--
-- Indexes for table `utils_notification`
--
ALTER TABLE `utils_notification`
  ADD PRIMARY KEY (`id`),
  ADD KEY `utils_notif_user_id_36dc1c_idx` (`user`,`created_at` DESC),
  ADD KEY `utils_notif_notific_d43fdf_idx` (`notification_type`),
  ADD KEY `utils_notif_read_at_52948b_idx` (`read_at`),
  ADD KEY `idx_utils_notification_user_id` (`user_id`),
  ADD KEY `idx_notification_user` (`user_id`),
  ADD KEY `idx_notification_read` (`read_at`),
  ADD KEY `utils_notification_loan_app_id_idx` (`loan_app_id`),
  ADD KEY `utils_notification_related_loan_id_idx` (`related_loan_id`),
  ADD KEY `utils_notification_loan_id_idx` (`loan_id`);

--
-- Indexes for table `utils_systemsetting`
--
ALTER TABLE `utils_systemsetting`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `key` (`key`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `auth_group`
--
ALTER TABLE `auth_group`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `auth_group_permissions`
--
ALTER TABLE `auth_group_permissions`
  MODIFY `id` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `auth_permission`
--
ALTER TABLE `auth_permission`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `default_role_permissions`
--
ALTER TABLE `default_role_permissions`
  MODIFY `id` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `django_admin_log`
--
ALTER TABLE `django_admin_log`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `django_content_type`
--
ALTER TABLE `django_content_type`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `django_migrations`
--
ALTER TABLE `django_migrations`
  MODIFY `id` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `expenses`
--
ALTER TABLE `expenses`
  MODIFY `id` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `loans_loan`
--
ALTER TABLE `loans_loan`
  MODIFY `id` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `loans_repayment`
--
ALTER TABLE `loans_repayment`
  MODIFY `id` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `otp_verifications`
--
ALTER TABLE `otp_verifications`
  MODIFY `id` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `portfolio_assignments`
--
ALTER TABLE `portfolio_assignments`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `portfolio_performance`
--
ALTER TABLE `portfolio_performance`
  MODIFY `id` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `report_schedules_recipients`
--
ALTER TABLE `report_schedules_recipients`
  MODIFY `id` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `role_permissions`
--
ALTER TABLE `role_permissions`
  MODIFY `id` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `users_accessible_branches`
--
ALTER TABLE `users_accessible_branches`
  MODIFY `id` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `users_customuser`
--
ALTER TABLE `users_customuser`
  MODIFY `id` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `users_customuser_groups`
--
ALTER TABLE `users_customuser_groups`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `users_customuser_user_permissions`
--
ALTER TABLE `users_customuser_user_permissions`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `users_groups`
--
ALTER TABLE `users_groups`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `users_user_permissions`
--
ALTER TABLE `users_user_permissions`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `user_access_logs`
--
ALTER TABLE `user_access_logs`
  MODIFY `id` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `user_permissions`
--
ALTER TABLE `user_permissions`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `utils_document`
--
ALTER TABLE `utils_document`
  MODIFY `id` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `utils_documentshare`
--
ALTER TABLE `utils_documentshare`
  MODIFY `id` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `utils_documenttag`
--
ALTER TABLE `utils_documenttag`
  MODIFY `id` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `utils_document_tags`
--
ALTER TABLE `utils_document_tags`
  MODIFY `id` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `utils_systemsetting`
--
ALTER TABLE `utils_systemsetting`
  MODIFY `id` bigint NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `auth_group_permissions`
--
ALTER TABLE `auth_group_permissions`
  ADD CONSTRAINT `auth_group_permissio_permission_id_84c5c92e_fk_auth_perm` FOREIGN KEY (`permission_id`) REFERENCES `auth_permission` (`id`),
  ADD CONSTRAINT `auth_group_permissions_group_id_b120cbf9_fk_auth_group_id` FOREIGN KEY (`group_id`) REFERENCES `auth_group` (`id`);

--
-- Constraints for table `auth_permission`
--
ALTER TABLE `auth_permission`
  ADD CONSTRAINT `auth_permission_content_type_id_2f476e4b_fk_django_co` FOREIGN KEY (`content_type_id`) REFERENCES `django_content_type` (`id`);

--
-- Constraints for table `django_admin_log`
--
ALTER TABLE `django_admin_log`
  ADD CONSTRAINT `django_admin_log_content_type_id_c4bce8eb_fk_django_co_new` FOREIGN KEY (`content_type_id`) REFERENCES `django_content_type` (`id`);

--
-- Constraints for table `mpesa_access_tokens`
--
ALTER TABLE `mpesa_access_tokens`
  ADD CONSTRAINT `mpesa_access_tokens_configuration_id_de2bf233_fk_mpesa_con` FOREIGN KEY (`configuration_id`) REFERENCES `mpesa_configurations` (`id`);

--
-- Constraints for table `mpesa_callbacks`
--
ALTER TABLE `mpesa_callbacks`
  ADD CONSTRAINT `mpesa_callbacks_transaction_id_426bc1e5_fk_mpesa_transactions_id` FOREIGN KEY (`transaction_id`) REFERENCES `mpesa_transactions` (`id`);

--
-- Constraints for table `payment_allocations`
--
ALTER TABLE `payment_allocations`
  ADD CONSTRAINT `payment_allocations_loan_id_e9be27ef_fk_loans_id` FOREIGN KEY (`loan_id`) REFERENCES `loans` (`id`),
  ADD CONSTRAINT `payment_allocations_mpesa_transaction_id_f31fa711_fk_mpesa_tra` FOREIGN KEY (`mpesa_transaction_id`) REFERENCES `mpesa_transactions` (`id`),
  ADD CONSTRAINT `payment_allocations_repayment_id_61a101ee_fk_repayments_id` FOREIGN KEY (`repayment_id`) REFERENCES `repayments` (`id`);

--
-- Constraints for table `users`
--
ALTER TABLE `users`
  ADD CONSTRAINT `users_branch_id_fk` FOREIGN KEY (`branch_id`) REFERENCES `users_branch` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `users_accessible_branches`
--
ALTER TABLE `users_accessible_branches`
  ADD CONSTRAINT `users_customuser_accessible_branches_branch_id_fk` FOREIGN KEY (`branch_id`) REFERENCES `users_branch` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `users_customuser_accessible_branches_customuser_id_fk` FOREIGN KEY (`customuser_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `users_groups`
--
ALTER TABLE `users_groups`
  ADD CONSTRAINT `users_groups_customuser_id_fk` FOREIGN KEY (`customuser_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `users_groups_group_id_fk` FOREIGN KEY (`group_id`) REFERENCES `auth_group` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `users_user_permissions`
--
ALTER TABLE `users_user_permissions`
  ADD CONSTRAINT `users_user_permissions_customuser_id_fk` FOREIGN KEY (`customuser_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `users_user_permissions_permission_id_fk` FOREIGN KEY (`permission_id`) REFERENCES `auth_permission` (`id`) ON DELETE CASCADE;
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 */;