-- Database Backup
-- Generated: 2026-06-26 13:15:46

DROP TABLE IF EXISTS `audit_trail`;
CREATE TABLE `audit_trail` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `table_name` varchar(50) NOT NULL,
  `record_id` int(11) NOT NULL,
  `action` enum('INSERT','UPDATE','DELETE') NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `old_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`old_data`)),
  `new_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`new_data`)),
  `ip_address` varchar(45) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `idx_table_name` (`table_name`),
  KEY `idx_record_id` (`record_id`),
  KEY `idx_action` (`action`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `audit_trail_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `client_documents`;
CREATE TABLE `client_documents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `client_id` int(11) NOT NULL,
  `document_type_id` int(11) NOT NULL,
  `document_number` varchar(100) DEFAULT NULL,
  `file_name` varchar(255) NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `file_size` int(11) DEFAULT NULL,
  `file_type` varchar(50) DEFAULT NULL,
  `uploaded_by` int(11) DEFAULT NULL,
  `upload_date` timestamp NOT NULL DEFAULT current_timestamp(),
  `expiry_date` date DEFAULT NULL,
  `issuing_authority` varchar(100) DEFAULT NULL,
  `verification_status` enum('pending','verified','rejected','expired') DEFAULT 'pending',
  `verification_notes` text DEFAULT NULL,
  `verified_by` int(11) DEFAULT NULL,
  `verification_date` datetime DEFAULT NULL,
  `rejection_reason` text DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `uploaded_by` (`uploaded_by`),
  KEY `verified_by` (`verified_by`),
  KEY `idx_client_id` (`client_id`),
  KEY `idx_document_type` (`document_type_id`),
  KEY `idx_verification_status` (`verification_status`),
  KEY `idx_expiry_date` (`expiry_date`),
  CONSTRAINT `client_documents_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE,
  CONSTRAINT `client_documents_ibfk_2` FOREIGN KEY (`document_type_id`) REFERENCES `document_types` (`id`),
  CONSTRAINT `client_documents_ibfk_3` FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`id`),
  CONSTRAINT `client_documents_ibfk_4` FOREIGN KEY (`verified_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `client_documents` VALUES ('1', '2', '1', NULL, 'R241971423062026.pdf', '../uploads/clients/temp/1782464866_R241971423062026.pdf', '72298', 'application/pdf', '1', '2026-06-26 02:08:09', NULL, NULL, 'pending', NULL, NULL, NULL, NULL, '1', NULL, '2026-06-26 02:08:09', '2026-06-26 02:08:09');
INSERT INTO `client_documents` VALUES ('2', '2', '3', NULL, 'Screenshot_20260608-093250.png.png', '../uploads/clients/temp/1782464866_Screenshot_20260608-093250.png.png', '390810', 'image/png', '1', '2026-06-26 02:08:09', NULL, NULL, 'pending', NULL, NULL, NULL, NULL, '1', NULL, '2026-06-26 02:08:09', '2026-06-26 02:08:09');
INSERT INTO `client_documents` VALUES ('3', '2', '4', NULL, 'IMG-20260604-WA0021.jpg', '../uploads/clients/temp/1782464866_IMG-20260604-WA0021.jpg', '109094', 'image/jpeg', '1', '2026-06-26 02:08:09', NULL, NULL, 'pending', NULL, NULL, NULL, NULL, '1', NULL, '2026-06-26 02:08:09', '2026-06-26 02:08:09');

DROP TABLE IF EXISTS `client_guarantors`;
CREATE TABLE `client_guarantors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `client_id` int(11) NOT NULL,
  `guarantor_name` varchar(100) NOT NULL,
  `guarantor_phone` varchar(20) NOT NULL,
  `guarantor_email` varchar(100) DEFAULT NULL,
  `guarantor_address` text DEFAULT NULL,
  `relationship` varchar(50) DEFAULT NULL,
  `employer` varchar(100) DEFAULT NULL,
  `monthly_income` decimal(15,2) DEFAULT NULL,
  `national_id` varchar(50) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_client_id` (`client_id`),
  CONSTRAINT `client_guarantors_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `client_guarantors` VALUES ('1', '2', 'nelson', '0776223112', 'nelson@gmail.com', '', 'father', '', '0.00', '', '2026-06-26 02:08:09');

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

INSERT INTO `clients` VALUES ('1', 'CL-2026-00001', 'daniel', 'maodzeka', 'kennedy', 'dkma@gmail.com', '0774233045', 'none', '3206grannary', 'harare', 'harare', '0000', 'Other', '1996-05-12', 'Male', 'Single', 'software dev', 'TSAPO', '200.00', '632094833a32', NULL, NULL, 'active', NULL, '', '1', '2026-06-26 01:52:15', '2026-06-26 01:52:15', '0', '0', NULL, NULL, 'pending', 'national_id', NULL, NULL, NULL, NULL, '0', NULL, NULL, '0', NULL, NULL, '0', NULL, NULL, '0', NULL, NULL, NULL, 'low');
INSERT INTO `clients` VALUES ('2', 'CL-2026-00002', 'daniel', 'hhhhh', 'hhhh', 'hh@gmail.com', '0775223123', '', 'Mufakose', 'kadoma1', 'kadoma', '0000', 'Kenya', '1996-10-10', 'Male', 'Married', 'CLEANER', 'TSAPO', '200.00', '6290233812', NULL, NULL, 'active', NULL, 'new', '1', '2026-06-26 02:08:09', '2026-06-26 02:15:36', '0', '0', NULL, NULL, 'pending', 'national_id', NULL, NULL, NULL, NULL, '0', NULL, NULL, '0', NULL, NULL, '0', NULL, NULL, '0', NULL, NULL, NULL, 'low');

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

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

DROP TABLE IF EXISTS `document_verification_history`;
CREATE TABLE `document_verification_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `document_id` int(11) NOT NULL,
  `previous_status` enum('pending','verified','rejected','expired') DEFAULT NULL,
  `new_status` enum('pending','verified','rejected','expired') DEFAULT NULL,
  `changed_by` int(11) NOT NULL,
  `change_date` datetime DEFAULT current_timestamp(),
  `comments` text DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `changed_by` (`changed_by`),
  KEY `idx_document_id` (`document_id`),
  KEY `idx_change_date` (`change_date`),
  CONSTRAINT `document_verification_history_ibfk_1` FOREIGN KEY (`document_id`) REFERENCES `client_documents` (`id`) ON DELETE CASCADE,
  CONSTRAINT `document_verification_history_ibfk_2` FOREIGN KEY (`changed_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `fees_and_charges`;
CREATE TABLE `fees_and_charges` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `loan_id` int(11) NOT NULL,
  `fee_type` enum('processing','late_payment','default','maintenance','other') NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `description` text DEFAULT NULL,
  `charge_date` date NOT NULL,
  `due_date` date DEFAULT NULL,
  `status` enum('pending','paid','waived') DEFAULT 'pending',
  `waived_by` int(11) DEFAULT NULL,
  `waiver_reason` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `waived_by` (`waived_by`),
  KEY `created_by` (`created_by`),
  KEY `idx_loan_id` (`loan_id`),
  KEY `idx_fee_type` (`fee_type`),
  KEY `idx_status` (`status`),
  CONSTRAINT `fees_and_charges_ibfk_1` FOREIGN KEY (`loan_id`) REFERENCES `loans` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fees_and_charges_ibfk_2` FOREIGN KEY (`waived_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fees_and_charges_ibfk_3` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `loan_approvals`;
CREATE TABLE `loan_approvals` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `loan_id` int(11) NOT NULL,
  `approver_id` int(11) NOT NULL,
  `approval_level` int(11) DEFAULT 1,
  `status` enum('pending','approved','rejected','returned') DEFAULT 'pending',
  `comments` text DEFAULT NULL,
  `approval_date` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_loan_id` (`loan_id`),
  KEY `idx_status` (`status`),
  KEY `idx_approver` (`approver_id`),
  CONSTRAINT `loan_approvals_ibfk_1` FOREIGN KEY (`loan_id`) REFERENCES `loans` (`id`) ON DELETE CASCADE,
  CONSTRAINT `loan_approvals_ibfk_2` FOREIGN KEY (`approver_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `loan_collaterals`;
CREATE TABLE `loan_collaterals` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `loan_id` int(11) NOT NULL,
  `collateral_type` enum('land','vehicle','building','equipment','savings','other') NOT NULL,
  `description` text NOT NULL,
  `estimated_value` decimal(15,2) NOT NULL,
  `appraised_value` decimal(15,2) DEFAULT NULL,
  `location` text DEFAULT NULL,
  `ownership_document` varchar(255) DEFAULT NULL,
  `verification_status` enum('pending','verified','rejected') DEFAULT 'pending',
  `verified_by` int(11) DEFAULT NULL,
  `verification_date` date DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `verified_by` (`verified_by`),
  KEY `idx_loan_id` (`loan_id`),
  CONSTRAINT `loan_collaterals_ibfk_1` FOREIGN KEY (`loan_id`) REFERENCES `loans` (`id`) ON DELETE CASCADE,
  CONSTRAINT `loan_collaterals_ibfk_2` FOREIGN KEY (`verified_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `loan_disbursements`;
CREATE TABLE `loan_disbursements` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `loan_id` int(11) NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `disbursement_date` date NOT NULL,
  `disbursement_method` enum('bank_transfer','cash','cheque','mobile_money') NOT NULL,
  `bank_name` varchar(100) DEFAULT NULL,
  `bank_branch` varchar(100) DEFAULT NULL,
  `account_number` varchar(50) DEFAULT NULL,
  `account_name` varchar(100) DEFAULT NULL,
  `cheque_number` varchar(50) DEFAULT NULL,
  `mobile_number` varchar(20) DEFAULT NULL,
  `reference_number` varchar(100) DEFAULT NULL,
  `status` enum('pending','processed','completed','failed','cancelled') DEFAULT 'pending',
  `processed_by` int(11) DEFAULT NULL,
  `authorized_by` int(11) DEFAULT NULL,
  `authorization_date` date DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `processed_by` (`processed_by`),
  KEY `authorized_by` (`authorized_by`),
  KEY `idx_loan_id` (`loan_id`),
  KEY `idx_status` (`status`),
  KEY `idx_disbursement_date` (`disbursement_date`),
  CONSTRAINT `loan_disbursements_ibfk_1` FOREIGN KEY (`loan_id`) REFERENCES `loans` (`id`) ON DELETE CASCADE,
  CONSTRAINT `loan_disbursements_ibfk_2` FOREIGN KEY (`processed_by`) REFERENCES `users` (`id`),
  CONSTRAINT `loan_disbursements_ibfk_3` FOREIGN KEY (`authorized_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

INSERT INTO `loan_products` VALUES ('1', 'P001', 'Personal Loan', 'Personal loans for individuals', '1000.00', '500000.00', '12.00', '12', '500.00', '5.00', '0.00', '0', '0', 'active', NULL, '2026-06-26 00:01:51', '2026-06-26 00:01:51');
INSERT INTO `loan_products` VALUES ('2', 'P002', 'Business Loan', 'Loans for small businesses', '50000.00', '5000000.00', '15.00', '24', '1000.00', '5.00', '0.00', '1', '0', 'active', NULL, '2026-06-26 00:01:51', '2026-06-26 00:01:51');
INSERT INTO `loan_products` VALUES ('3', 'P003', 'Emergency Loan', 'Quick emergency loans', '500.00', '100000.00', '18.00', '6', '200.00', '10.00', '0.00', '0', '0', 'active', NULL, '2026-06-26 00:01:51', '2026-06-26 00:01:51');
INSERT INTO `loan_products` VALUES ('4', 'P004', 'Asset Finance', 'Financing for assets and equipment', '100000.00', '10000000.00', '10.00', '36', '2000.00', '5.00', '0.00', '1', '0', 'active', NULL, '2026-06-26 00:01:51', '2026-06-26 00:01:51');

DROP TABLE IF EXISTS `loan_repayment_schedules`;
CREATE TABLE `loan_repayment_schedules` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `loan_id` int(11) NOT NULL,
  `payment_number` int(11) NOT NULL,
  `due_date` date NOT NULL,
  `total_due` decimal(15,2) NOT NULL,
  `principal_due` decimal(15,2) NOT NULL,
  `interest_due` decimal(15,2) NOT NULL,
  `penalty_due` decimal(15,2) DEFAULT 0.00,
  `status` enum('pending','paid','overdue','partial') DEFAULT 'pending',
  `paid_amount` decimal(15,2) DEFAULT 0.00,
  `balance` decimal(15,2) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_loan_id` (`loan_id`),
  KEY `idx_due_date` (`due_date`),
  KEY `idx_status` (`status`),
  KEY `idx_payment_number` (`payment_number`),
  CONSTRAINT `loan_repayment_schedules_ibfk_1` FOREIGN KEY (`loan_id`) REFERENCES `loans` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `loan_repayments`;
CREATE TABLE `loan_repayments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `loan_id` int(11) NOT NULL,
  `payment_number` int(11) DEFAULT NULL,
  `amount` decimal(15,2) NOT NULL,
  `principal_paid` decimal(15,2) DEFAULT NULL,
  `interest_paid` decimal(15,2) DEFAULT NULL,
  `penalty_paid` decimal(15,2) DEFAULT 0.00,
  `payment_date` date NOT NULL,
  `due_date` date NOT NULL,
  `payment_method` enum('cash','bank_transfer','mobile_money','cheque','standing_order') NOT NULL,
  `reference_number` varchar(100) DEFAULT NULL,
  `transaction_id` varchar(100) DEFAULT NULL,
  `receipt_number` varchar(50) DEFAULT NULL,
  `status` enum('pending','completed','failed','reversed') DEFAULT 'pending',
  `received_by` int(11) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `received_by` (`received_by`),
  KEY `idx_loan_id` (`loan_id`),
  KEY `idx_payment_date` (`payment_date`),
  KEY `idx_due_date` (`due_date`),
  KEY `idx_reference` (`reference_number`),
  KEY `idx_status` (`status`),
  CONSTRAINT `loan_repayments_ibfk_1` FOREIGN KEY (`loan_id`) REFERENCES `loans` (`id`) ON DELETE CASCADE,
  CONSTRAINT `loan_repayments_ibfk_2` FOREIGN KEY (`received_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `loans`;
CREATE TABLE `loans` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `loan_number` varchar(50) NOT NULL,
  `client_id` int(11) NOT NULL,
  `product_id` int(11) DEFAULT NULL,
  `amount` decimal(15,2) NOT NULL,
  `interest_rate` decimal(5,2) NOT NULL,
  `term_months` int(11) NOT NULL,
  `total_repayable` decimal(15,2) DEFAULT NULL,
  `monthly_payment` decimal(15,2) DEFAULT NULL,
  `total_interest` decimal(15,2) DEFAULT NULL,
  `processing_fee` decimal(15,2) DEFAULT 0.00,
  `purpose` text DEFAULT NULL,
  `application_date` date NOT NULL,
  `expected_disbursement_date` date DEFAULT NULL,
  `approval_date` date DEFAULT NULL,
  `disbursement_date` date DEFAULT NULL,
  `first_payment_date` date DEFAULT NULL,
  `maturity_date` date DEFAULT NULL,
  `status` enum('draft','pending','under_review','approved','rejected','disbursed','active','completed','defaulted','written_off') DEFAULT 'draft',
  `approval_notes` text DEFAULT NULL,
  `rejection_reason` text DEFAULT NULL,
  `approved_by` int(11) DEFAULT NULL,
  `disbursed_by` int(11) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `loan_number` (`loan_number`),
  KEY `product_id` (`product_id`),
  KEY `approved_by` (`approved_by`),
  KEY `disbursed_by` (`disbursed_by`),
  KEY `created_by` (`created_by`),
  KEY `idx_loan_number` (`loan_number`),
  KEY `idx_client_id` (`client_id`),
  KEY `idx_status` (`status`),
  KEY `idx_application_date` (`application_date`),
  KEY `idx_maturity_date` (`maturity_date`),
  CONSTRAINT `loans_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
  CONSTRAINT `loans_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `loan_products` (`id`),
  CONSTRAINT `loans_ibfk_3` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`),
  CONSTRAINT `loans_ibfk_4` FOREIGN KEY (`disbursed_by`) REFERENCES `users` (`id`),
  CONSTRAINT `loans_ibfk_5` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `notifications`;
CREATE TABLE `notifications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `type` enum('info','warning','success','error') DEFAULT 'info',
  `title` varchar(200) NOT NULL,
  `message` text NOT NULL,
  `link` varchar(255) DEFAULT NULL,
  `is_read` tinyint(1) DEFAULT 0,
  `read_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_is_read` (`is_read`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `notifications_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `reports`;
CREATE TABLE `reports` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `report_code` varchar(50) NOT NULL,
  `report_type` enum('loan','transaction','client','financial','custom') NOT NULL,
  `title` varchar(200) NOT NULL,
  `description` text DEFAULT NULL,
  `parameters` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`parameters`)),
  `file_path` varchar(255) DEFAULT NULL,
  `file_format` enum('pdf','excel','csv','html') DEFAULT NULL,
  `generated_by` int(11) DEFAULT NULL,
  `generated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `report_code` (`report_code`),
  KEY `generated_by` (`generated_by`),
  KEY `idx_report_code` (`report_code`),
  KEY `idx_report_type` (`report_type`),
  KEY `idx_generated_at` (`generated_at`),
  CONSTRAINT `reports_ibfk_1` FOREIGN KEY (`generated_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `settings`;
CREATE TABLE `settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text DEFAULT NULL,
  `setting_group` varchar(50) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `is_encrypted` tinyint(1) DEFAULT 0,
  `updated_by` int(11) DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `setting_key` (`setting_key`),
  KEY `updated_by` (`updated_by`),
  KEY `idx_setting_key` (`setting_key`),
  KEY `idx_setting_group` (`setting_group`),
  CONSTRAINT `settings_ibfk_1` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `settings` VALUES ('16', 'company_name', 'ABC Loan Management', 'company', NULL, '0', NULL, '2026-06-26 03:15:19');
INSERT INTO `settings` VALUES ('17', 'company_address', 'Harare, Zimbabwe', 'company', NULL, '0', NULL, '2026-06-26 03:15:19');
INSERT INTO `settings` VALUES ('18', 'company_phone', '+26370000000', 'company', NULL, '0', NULL, '2026-06-26 03:15:19');
INSERT INTO `settings` VALUES ('19', 'company_email', 'info@nissaloans.co.zw', 'company', NULL, '0', NULL, '2026-06-26 03:15:19');
INSERT INTO `settings` VALUES ('20', 'currency', 'USD', 'system', NULL, '0', NULL, '2026-06-26 03:15:24');
INSERT INTO `settings` VALUES ('21', 'date_format', 'Y-m-d', 'system', NULL, '0', NULL, '2026-06-26 03:15:19');
INSERT INTO `settings` VALUES ('22', 'timezone', 'Africa/Harare', 'system', NULL, '0', NULL, '2026-06-26 03:15:19');
INSERT INTO `settings` VALUES ('23', 'max_loan_amount', '5000000', 'loan', NULL, '0', NULL, '2026-06-26 03:15:19');
INSERT INTO `settings` VALUES ('24', 'min_loan_amount', '1000', 'loan', NULL, '0', NULL, '2026-06-26 03:15:19');
INSERT INTO `settings` VALUES ('25', 'default_interest_rate', '12', 'loan', NULL, '0', NULL, '2026-06-26 03:15:24');
INSERT INTO `settings` VALUES ('26', 'late_penalty_rate', '5', 'loan', NULL, '0', NULL, '2026-06-26 03:15:24');
INSERT INTO `settings` VALUES ('27', 'processing_fee_rate', '1', 'loan', NULL, '0', NULL, '2026-06-26 03:15:24');
INSERT INTO `settings` VALUES ('28', 'max_document_size', '5242880', 'documents', NULL, '0', NULL, '2026-06-26 03:15:19');
INSERT INTO `settings` VALUES ('29', 'allowed_document_types', 'jpg,jpeg,png,pdf', 'documents', NULL, '0', NULL, '2026-06-26 03:15:19');
INSERT INTO `settings` VALUES ('30', 'document_retention_days', '2555', 'documents', NULL, '0', NULL, '2026-06-26 03:15:19');

DROP TABLE IF EXISTS `staff`;
CREATE TABLE `staff` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `staff_number` varchar(50) NOT NULL,
  `department` varchar(100) DEFAULT NULL,
  `position` varchar(100) DEFAULT NULL,
  `hire_date` date DEFAULT NULL,
  `supervisor_id` int(11) DEFAULT NULL,
  `salary` decimal(15,2) DEFAULT NULL,
  `emergency_contact` varchar(100) DEFAULT NULL,
  `emergency_phone` varchar(20) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`),
  UNIQUE KEY `staff_number` (`staff_number`),
  KEY `supervisor_id` (`supervisor_id`),
  KEY `idx_staff_number` (`staff_number`),
  KEY `idx_department` (`department`),
  CONSTRAINT `staff_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `staff_ibfk_2` FOREIGN KEY (`supervisor_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `staff` VALUES ('1', '1', 'STF-001', 'Administration', 'System Administrator', '2026-06-26', NULL, NULL, NULL, NULL, '2026-06-26 00:01:51');

DROP TABLE IF EXISTS `system_logs`;
CREATE TABLE `system_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `action` varchar(100) NOT NULL,
  `module` varchar(50) NOT NULL,
  `entity_type` varchar(50) DEFAULT NULL,
  `entity_id` int(11) DEFAULT NULL,
  `old_values` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`old_values`)),
  `new_values` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`new_values`)),
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_module` (`module`),
  KEY `idx_action` (`action`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `system_logs_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `transactions`;
CREATE TABLE `transactions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `transaction_reference` varchar(50) NOT NULL,
  `transaction_type` enum('loan_disbursement','loan_repayment','fee_charge','penalty_charge','refund','write_off') NOT NULL,
  `loan_id` int(11) DEFAULT NULL,
  `client_id` int(11) DEFAULT NULL,
  `amount` decimal(15,2) NOT NULL,
  `balance_after` decimal(15,2) DEFAULT NULL,
  `transaction_date` datetime NOT NULL,
  `payment_method` varchar(50) DEFAULT NULL,
  `reference_number` varchar(100) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `status` enum('pending','completed','failed','reversed') DEFAULT 'completed',
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `transaction_reference` (`transaction_reference`),
  KEY `created_by` (`created_by`),
  KEY `idx_transaction_reference` (`transaction_reference`),
  KEY `idx_loan_id` (`loan_id`),
  KEY `idx_client_id` (`client_id`),
  KEY `idx_date` (`transaction_date`),
  KEY `idx_type` (`transaction_type`),
  CONSTRAINT `transactions_ibfk_1` FOREIGN KEY (`loan_id`) REFERENCES `loans` (`id`),
  CONSTRAINT `transactions_ibfk_2` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
  CONSTRAINT `transactions_ibfk_3` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

INSERT INTO `users` VALUES ('1', 'admin', 'admin@loansystem.com', '$2y$10$UMUHB/OwoM.dUgahf8yqJOVI88TvlOEZl57WLYNTqwlyrUrJ6ihP.', 'System Administrator', NULL, 'admin', NULL, '2026-06-26 01:13:35', '1', '2026-06-26 00:01:51', '2026-06-26 01:13:35');

DROP TABLE IF EXISTS `vw_client_document_status`;
;

INSERT INTO `vw_client_document_status` VALUES ('1', 'CL-2026-00001', 'daniel maodzeka', '0', '0', 'pending', '0', '0', '0', '0', NULL);
INSERT INTO `vw_client_document_status` VALUES ('2', 'CL-2026-00002', 'daniel hhhhh', '0', '0', 'pending', '3', '0', '3', '0', '2026-06-26 02:08:09');

DROP TABLE IF EXISTS `vw_client_document_summary`;
;

INSERT INTO `vw_client_document_summary` VALUES ('1', 'CL-2026-00001', 'daniel maodzeka', 'pending', '0', '0', '0', '0', '0', '0', NULL, NULL);
INSERT INTO `vw_client_document_summary` VALUES ('2', 'CL-2026-00002', 'daniel hhhhh', 'pending', '0', '0', '3', '0', '3', '0', '2026-06-26 02:08:09', '0');

DROP TABLE IF EXISTS `vw_client_loan_status`;
;

INSERT INTO `vw_client_loan_status` VALUES ('1', 'CL-2026-00001', 'daniel maodzeka', '0774233045', 'dkma@gmail.com', '0', '0', '0', '0.00', '0.00', NULL);
INSERT INTO `vw_client_loan_status` VALUES ('2', 'CL-2026-00002', 'daniel hhhhh', '0775223123', 'hh@gmail.com', '0', '0', '0', '0.00', '0.00', NULL);

DROP TABLE IF EXISTS `vw_loan_disbursement_summary`;
;

DROP TABLE IF EXISTS `vw_loan_summary`;
;

DROP TABLE IF EXISTS `vw_monthly_repayment_summary`;
;

DROP TABLE IF EXISTS `vw_overdue_loans`;
;

DROP TABLE IF EXISTS `vw_repayment_performance`;
;

DROP TABLE IF EXISTS `vw_staff_performance`;
;

INSERT INTO `vw_staff_performance` VALUES ('1', 'System Administrator', 'admin', '0', '0', '0', NULL, NULL);