-- --------------------------------------------------------
-- Host:                         127.0.0.1
-- Server version:               10.4.27-MariaDB - mariadb.org binary distribution
-- Server OS:                    Win64
-- HeidiSQL Version:             12.21.0.7344
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

-- Dumping structure for table trustaxisdb.approval_rules
CREATE TABLE IF NOT EXISTS `approval_rules` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `purpose` enum('loan_disbursement','investment_withdrawal','savings_withdrawal') NOT NULL,
  `mode` enum('Never','Always','Threshold') NOT NULL DEFAULT 'Threshold',
  `threshold_amount` decimal(15,2) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `updated_by` bigint(20) unsigned DEFAULT NULL,
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_purpose_active` (`purpose`,`is_active`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `approval_rules_ibfk_1` FOREIGN KEY (`updated_by`) REFERENCES `staff` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table trustaxisdb.approval_rules: ~0 rows (approximately)

-- Dumping structure for table trustaxisdb.approvals
CREATE TABLE IF NOT EXISTS `approvals` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `approvable_type` varchar(50) NOT NULL,
  `approvable_id` bigint(20) unsigned NOT NULL,
  `approval_rule_id` int(10) unsigned DEFAULT NULL,
  `rule_snapshot` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`rule_snapshot`)),
  `requested_by` bigint(20) unsigned NOT NULL,
  `requested_at` datetime NOT NULL DEFAULT current_timestamp(),
  `status` enum('Pending','Approved','Rejected','Not_Required') NOT NULL DEFAULT 'Pending',
  `decided_by` bigint(20) unsigned DEFAULT NULL,
  `decided_at` datetime DEFAULT NULL,
  `decision_notes` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `approval_rule_id` (`approval_rule_id`),
  KEY `requested_by` (`requested_by`),
  KEY `decided_by` (`decided_by`),
  KEY `idx_approvable` (`approvable_type`,`approvable_id`),
  CONSTRAINT `approvals_ibfk_1` FOREIGN KEY (`approval_rule_id`) REFERENCES `approval_rules` (`id`),
  CONSTRAINT `approvals_ibfk_2` FOREIGN KEY (`requested_by`) REFERENCES `staff` (`id`),
  CONSTRAINT `approvals_ibfk_3` FOREIGN KEY (`decided_by`) REFERENCES `staff` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table trustaxisdb.approvals: ~0 rows (approximately)

-- Dumping structure for table trustaxisdb.audit_logs
CREATE TABLE IF NOT EXISTS `audit_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `session_id` varchar(64) DEFAULT NULL,
  `trace_id` varchar(64) DEFAULT NULL,
  `action` varchar(100) NOT NULL,
  `table_name` varchar(50) NOT NULL,
  `record_id` bigint(20) unsigned NOT 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`)),
  `description` varchar(255) DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `module` varchar(100) DEFAULT NULL,
  `action_timestamp` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_entity` (`table_name`,`record_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table trustaxisdb.audit_logs: ~0 rows (approximately)

-- Dumping structure for table trustaxisdb.bank_transactions
CREATE TABLE IF NOT EXISTS `bank_transactions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `amount` decimal(15,2) NOT NULL,
  `transaction_date` date NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `reconciled` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table trustaxisdb.bank_transactions: ~0 rows (approximately)

-- Dumping structure for table trustaxisdb.branches
CREATE TABLE IF NOT EXISTS `branches` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `location` varchar(150) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table trustaxisdb.branches: ~1 rows (approximately)
INSERT INTO `branches` (`id`, `name`, `location`, `is_active`) VALUES
	(1, 'MAIN BRANCH', 'NAIROBI-KENYA', 1);

-- Dumping structure for table trustaxisdb.chart_of_accounts
CREATE TABLE IF NOT EXISTS `chart_of_accounts` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(20) NOT NULL,
  `name` varchar(100) NOT NULL,
  `account_type` enum('Asset','Liability','Equity','Income','Expense') NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table trustaxisdb.chart_of_accounts: ~36 rows (approximately)
INSERT INTO `chart_of_accounts` (`id`, `code`, `name`, `account_type`, `description`) VALUES
	(1, '1000', 'M-PESA Paybill Cash', 'Asset', NULL),
	(2, '1010', 'Bank Account - Operating', 'Asset', NULL),
	(3, '1020', 'M-PESA B2C Utility Account', 'Asset', NULL),
	(4, '1100', 'Loans Receivable - Principal', 'Asset', NULL),
	(5, '1110', 'Loans Receivable - Interest', 'Asset', NULL),
	(6, '1120', 'Loan Fees Receivable', 'Asset', NULL),
	(7, '1200', 'Provision for Loan Losses (Contra)', 'Asset', 'Contra-asset reducing gross loan receivables to expected collectible value'),
	(8, '1300', 'Prepaid Expenses', 'Asset', NULL),
	(9, '1400', 'M-PESA Suspense - Unmatched Deposits', 'Asset', 'Unmatched C2B M-PESA deposits pending manual assignment by Finance Officer (FR-O3.2 UNMATCHED status)'),
	(10, '2000', 'Akiba Yangu Savings Payable', 'Liability', NULL),
	(11, '2010', 'Akiba Flow Investment Payable', 'Liability', NULL),
	(12, '2020', 'Corporate Bond Payable', 'Liability', NULL),
	(13, '2030', 'Interest Payable - Akiba Yangu', 'Liability', NULL),
	(14, '2040', 'Interest Payable - Akiba Flow', 'Liability', 'Daily interest accrual per FRS 2.2 CRON job, credited to this liability until paid at maturity'),
	(15, '2050', 'Coupon Payable - Bonds', 'Liability', NULL),
	(16, '2100', 'Pending Withdrawals Payable', 'Liability', NULL),
	(17, '2200', 'Statutory Payables', 'Liability', NULL),
	(18, '3000', 'Share Capital', 'Equity', NULL),
	(19, '3100', 'Retained Earnings', 'Equity', NULL),
	(20, '3200', 'Current Year Net Profit', 'Equity', NULL),
	(21, '4000', 'Interest Income - Loans', 'Income', NULL),
	(22, '4010', 'Processing Fee Income', 'Income', NULL),
	(23, '4020', 'Fast-Track Fee Income', 'Income', NULL),
	(24, '4030', 'Onboarding Fee Income', 'Income', NULL),
	(25, '4100', 'Recovery Income (Written-Off Loans)', 'Income', NULL),
	(26, '4900', 'Other Income', 'Income', NULL),
	(27, '5000', 'Interest Expense - Akiba Yangu', 'Expense', NULL),
	(28, '5010', 'Interest Expense - Akiba Flow', 'Expense', NULL),
	(29, '5020', 'Coupon Expense - Bonds', 'Expense', NULL),
	(30, '5100', 'Bad Debt Expense / Loans Written Off', 'Expense', NULL),
	(31, '5200', 'Bank & M-PESA Transaction Charges', 'Expense', NULL),
	(32, '5210', 'Airtime & SMS Costs', 'Expense', NULL),
	(33, '5220', 'CRB Check Fees', 'Expense', NULL),
	(34, '5230', 'Rent', 'Expense', NULL),
	(35, '5240', 'Salaries & Wages', 'Expense', NULL),
	(36, '5900', 'Other Operating Expenses', 'Expense', NULL);

-- Dumping structure for table trustaxisdb.ci_sessions
CREATE TABLE IF NOT EXISTS `ci_sessions` (
  `id` varchar(40) NOT NULL,
  `ip_address` varchar(45) NOT NULL,
  `user_agent` varchar(50) NOT NULL DEFAULT '',
  `last_activity` int(11) NOT NULL DEFAULT 0,
  `timestamp` int(10) unsigned NOT NULL DEFAULT 0,
  `data` blob NOT NULL,
  `user_data` text DEFAULT NULL,
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0,
  `deleted_at` datetime DEFAULT NULL,
  `deleted_by` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- Dumping data for table trustaxisdb.ci_sessions: ~5 rows (approximately)
INSERT INTO `ci_sessions` (`id`, `ip_address`, `user_agent`, `last_activity`, `timestamp`, `data`, `user_data`, `is_deleted`, `deleted_at`, `deleted_by`) VALUES
	('sip6bochjrj5513k44lhsu33ilcfvho0', '127.0.0.1', '', 0, 1789284272, _binary 0x5f5f63695f6c6173745f726567656e65726174657c693a313738393238343237323b, NULL, 0, NULL, NULL),
	('7j093461eq9vjb1gka6dup00c4mn9aj2', '127.0.0.1', '', 0, 1789284690, _binary 0x5f5f63695f6c6173745f726567656e65726174657c693a313738393238343639303b, NULL, 0, NULL, NULL),
	('skpmqp2om9s60qfl82cqgctum94lfog3', '127.0.0.1', '', 0, 1789285270, _binary 0x5f5f63695f6c6173745f726567656e65726174657c693a313738393238353237303b, NULL, 0, NULL, NULL),
	('jof83shpuk40k0khhbdo34v8s40eqhpk', '127.0.0.1', '', 0, 1789285688, _binary 0x5f5f63695f6c6173745f726567656e65726174657c693a313738393238353638383b, NULL, 0, NULL, NULL),
	('5ub4risp8jg3r2011tfjgpend167hou2', '127.0.0.1', '', 0, 1789286262, _binary 0x5f5f63695f6c6173745f726567656e65726174657c693a313738393238363236323b, NULL, 0, NULL, NULL),
	('or0tmlousvdlmus2trevtnio2t4r8gsu', '127.0.0.1', '', 0, 1789286835, _binary 0x5f5f63695f6c6173745f726567656e65726174657c693a313738393238363833353b, NULL, 0, NULL, NULL),
	('46angcijf1v33q1tj8bm9d05qfbp9mno', '127.0.0.1', '', 0, 1789286837, _binary 0x5f5f63695f6c6173745f726567656e65726174657c693a313738393238363833353b, NULL, 0, NULL, NULL);

-- Dumping structure for table trustaxisdb.coupon_payments
CREATE TABLE IF NOT EXISTS `coupon_payments` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `investment_id` bigint(20) unsigned NOT NULL,
  `due_date` date NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `status` enum('Pending','Paid') NOT NULL DEFAULT 'Pending',
  `paid_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `investment_id` (`investment_id`),
  CONSTRAINT `coupon_payments_ibfk_1` FOREIGN KEY (`investment_id`) REFERENCES `investments` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table trustaxisdb.coupon_payments: ~0 rows (approximately)

-- Dumping structure for table trustaxisdb.credit_limit_history
CREATE TABLE IF NOT EXISTS `credit_limit_history` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` bigint(20) unsigned NOT NULL,
  `calculated_limit` decimal(15,2) NOT NULL,
  `base_limit` decimal(15,2) NOT NULL,
  `loyalty_bonus` decimal(15,2) NOT NULL DEFAULT 0.00,
  `interest_bonus` decimal(15,2) NOT NULL DEFAULT 0.00,
  `risk_adjustment` decimal(15,2) NOT NULL DEFAULT 0.00,
  `calculated_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_customer` (`customer_id`),
  CONSTRAINT `credit_limit_history_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customer_profiles` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table trustaxisdb.credit_limit_history: ~0 rows (approximately)

-- Dumping structure for table trustaxisdb.customer_profiles
CREATE TABLE IF NOT EXISTS `customer_profiles` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `full_name` varchar(150) NOT NULL,
  `id_number` varchar(30) DEFAULT NULL,
  `id_number_encrypted` varbinary(255) DEFAULT NULL,
  `business_type` varchar(100) DEFAULT NULL,
  `physical_structure` tinyint(1) DEFAULT 0,
  `location` varchar(150) DEFAULT NULL,
  `referrer_id` bigint(20) unsigned DEFAULT NULL,
  `onboarding_date` date NOT NULL,
  `crb_score` int(11) DEFAULT NULL,
  `trust_score` decimal(5,2) NOT NULL DEFAULT 0.00,
  `status` enum('Active','Inactive','Defaulted') NOT NULL DEFAULT 'Active',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `referrer_id` (`referrer_id`),
  KEY `idx_status` (`status`),
  CONSTRAINT `customer_profiles_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  CONSTRAINT `customer_profiles_ibfk_2` FOREIGN KEY (`referrer_id`) REFERENCES `customer_profiles` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table trustaxisdb.customer_profiles: ~0 rows (approximately)

-- Dumping structure for table trustaxisdb.documents
CREATE TABLE IF NOT EXISTS `documents` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `owner_type` enum('customer','loan_application') NOT NULL,
  `owner_id` bigint(20) unsigned NOT NULL,
  `document_type` varchar(50) NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `uploaded_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table trustaxisdb.documents: ~0 rows (approximately)

-- Dumping structure for table trustaxisdb.investment_products
CREATE TABLE IF NOT EXISTS `investment_products` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `rate_type` enum('Flat_Total','Per_Annum') NOT NULL,
  `interest_rate` decimal(6,3) NOT NULL,
  `tenor_months` smallint(5) unsigned DEFAULT NULL,
  `min_principal` decimal(15,2) NOT NULL,
  `max_principal` decimal(15,2) DEFAULT NULL,
  `early_withdrawal_rule` enum('Forfeit_Interest','Prorated') NOT NULL,
  `has_coupon` tinyint(1) NOT NULL DEFAULT 0,
  `coupon_frequency` enum('Semi_Annual','Monthly') DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table trustaxisdb.investment_products: ~0 rows (approximately)

-- Dumping structure for table trustaxisdb.investment_transactions
CREATE TABLE IF NOT EXISTS `investment_transactions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `investment_id` bigint(20) unsigned NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `type` enum('Deposit','Withdrawal','Interest_Accrual','Coupon') NOT NULL,
  `mpesa_reference` varchar(50) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `investment_id` (`investment_id`),
  CONSTRAINT `investment_transactions_ibfk_1` FOREIGN KEY (`investment_id`) REFERENCES `investments` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table trustaxisdb.investment_transactions: ~0 rows (approximately)

-- Dumping structure for table trustaxisdb.investments
CREATE TABLE IF NOT EXISTS `investments` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `investor_id` bigint(20) unsigned NOT NULL,
  `product_id` int(10) unsigned NOT NULL,
  `principal` decimal(15,2) NOT NULL,
  `interest_rate` decimal(6,3) NOT NULL,
  `issue_date` date NOT NULL,
  `maturity_date` date NOT NULL,
  `interest_accrued_to_date` decimal(15,2) NOT NULL DEFAULT 0.00,
  `status` enum('Active','Matured','Withdrawn') NOT NULL DEFAULT 'Active',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `product_id` (`product_id`),
  KEY `idx_investor` (`investor_id`),
  KEY `idx_maturity` (`maturity_date`),
  CONSTRAINT `investments_ibfk_1` FOREIGN KEY (`investor_id`) REFERENCES `investor_profiles` (`id`),
  CONSTRAINT `investments_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `investment_products` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table trustaxisdb.investments: ~0 rows (approximately)

-- Dumping structure for table trustaxisdb.investor_profiles
CREATE TABLE IF NOT EXISTS `investor_profiles` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `full_name` varchar(150) NOT NULL,
  `investor_type` enum('Akiba Flow','Bond Holder','Both') NOT NULL DEFAULT 'Akiba Flow',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `investor_profiles_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table trustaxisdb.investor_profiles: ~0 rows (approximately)

-- Dumping structure for table trustaxisdb.journal_entries
CREATE TABLE IF NOT EXISTS `journal_entries` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `entry_date` datetime NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `is_system_generated` tinyint(1) NOT NULL DEFAULT 1,
  `override_reason` varchar(255) DEFAULT NULL,
  `reference_type` varchar(50) DEFAULT NULL,
  `reference_id` bigint(20) unsigned DEFAULT NULL,
  `created_by` bigint(20) unsigned NOT NULL,
  `voided_at` datetime DEFAULT NULL,
  `voided_by` bigint(20) unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  KEY `voided_by` (`voided_by`),
  KEY `idx_reference` (`reference_type`,`reference_id`),
  CONSTRAINT `journal_entries_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `staff` (`id`),
  CONSTRAINT `journal_entries_ibfk_2` FOREIGN KEY (`voided_by`) REFERENCES `staff` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table trustaxisdb.journal_entries: ~0 rows (approximately)

-- Dumping structure for table trustaxisdb.journal_entry_lines
CREATE TABLE IF NOT EXISTS `journal_entry_lines` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `journal_entry_id` bigint(20) unsigned NOT NULL,
  `account_id` int(10) unsigned NOT NULL,
  `debit` decimal(15,2) NOT NULL DEFAULT 0.00,
  `credit` decimal(15,2) NOT NULL DEFAULT 0.00,
  `narration` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `journal_entry_id` (`journal_entry_id`),
  KEY `account_id` (`account_id`),
  CONSTRAINT `journal_entry_lines_ibfk_1` FOREIGN KEY (`journal_entry_id`) REFERENCES `journal_entries` (`id`),
  CONSTRAINT `journal_entry_lines_ibfk_2` FOREIGN KEY (`account_id`) REFERENCES `chart_of_accounts` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table trustaxisdb.journal_entry_lines: ~0 rows (approximately)

-- Dumping structure for table trustaxisdb.loan_applications
CREATE TABLE IF NOT EXISTS `loan_applications` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` bigint(20) unsigned NOT NULL,
  `product_id` int(10) unsigned NOT NULL,
  `requested_amount` decimal(15,2) NOT NULL,
  `max_eligible_at_request` decimal(15,2) NOT NULL,
  `status` enum('Submitted','Pending_Underwriting','Approved','Rejected','Converted') NOT NULL DEFAULT 'Submitted',
  `reviewed_by` bigint(20) unsigned DEFAULT NULL,
  `reviewed_at` datetime DEFAULT NULL,
  `rejection_reason` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `customer_id` (`customer_id`),
  KEY `product_id` (`product_id`),
  KEY `reviewed_by` (`reviewed_by`),
  CONSTRAINT `loan_applications_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customer_profiles` (`id`),
  CONSTRAINT `loan_applications_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `loan_products` (`id`),
  CONSTRAINT `loan_applications_ibfk_3` FOREIGN KEY (`reviewed_by`) REFERENCES `staff` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table trustaxisdb.loan_applications: ~0 rows (approximately)

-- Dumping structure for table trustaxisdb.loan_product_fees
CREATE TABLE IF NOT EXISTS `loan_product_fees` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `loan_product_id` int(10) unsigned NOT NULL,
  `fee_name` varchar(50) NOT NULL,
  `fee_type` enum('Fixed','Percent') NOT NULL DEFAULT 'Fixed',
  `fee_value` decimal(15,2) NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  KEY `loan_product_id` (`loan_product_id`),
  CONSTRAINT `loan_product_fees_ibfk_1` FOREIGN KEY (`loan_product_id`) REFERENCES `loan_products` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table trustaxisdb.loan_product_fees: ~0 rows (approximately)

-- Dumping structure for table trustaxisdb.loan_products
CREATE TABLE IF NOT EXISTS `loan_products` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `interest_rate` decimal(6,3) NOT NULL,
  `tenor_days` smallint(5) unsigned NOT NULL,
  `repayment_frequency` enum('Daily','Weekly','Monthly') NOT NULL,
  `min_principal` decimal(15,2) NOT NULL,
  `max_principal` decimal(15,2) NOT NULL,
  `business_days_divisor` smallint(5) unsigned DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table trustaxisdb.loan_products: ~0 rows (approximately)

-- Dumping structure for table trustaxisdb.loan_repayment_schedule
CREATE TABLE IF NOT EXISTS `loan_repayment_schedule` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `loan_id` bigint(20) unsigned NOT NULL,
  `installment_no` smallint(5) unsigned NOT NULL,
  `due_date` date NOT NULL,
  `amount_due` decimal(15,2) NOT NULL,
  `amount_paid` decimal(15,2) NOT NULL DEFAULT 0.00,
  `status` enum('Pending','Paid','Partial','Missed') NOT NULL DEFAULT 'Pending',
  PRIMARY KEY (`id`),
  KEY `idx_loan_due` (`loan_id`,`due_date`),
  CONSTRAINT `loan_repayment_schedule_ibfk_1` FOREIGN KEY (`loan_id`) REFERENCES `loans` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table trustaxisdb.loan_repayment_schedule: ~0 rows (approximately)

-- Dumping structure for table trustaxisdb.loan_status_history
CREATE TABLE IF NOT EXISTS `loan_status_history` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `loan_id` bigint(20) unsigned NOT NULL,
  `from_status_id` int(10) unsigned DEFAULT NULL,
  `to_status_id` int(10) unsigned NOT NULL,
  `changed_by` bigint(20) unsigned DEFAULT NULL,
  `reason` varchar(255) DEFAULT NULL,
  `changed_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `loan_id` (`loan_id`),
  KEY `from_status_id` (`from_status_id`),
  KEY `to_status_id` (`to_status_id`),
  KEY `changed_by` (`changed_by`),
  CONSTRAINT `loan_status_history_ibfk_1` FOREIGN KEY (`loan_id`) REFERENCES `loans` (`id`),
  CONSTRAINT `loan_status_history_ibfk_2` FOREIGN KEY (`from_status_id`) REFERENCES `loan_statuses` (`id`),
  CONSTRAINT `loan_status_history_ibfk_3` FOREIGN KEY (`to_status_id`) REFERENCES `loan_statuses` (`id`),
  CONSTRAINT `loan_status_history_ibfk_4` FOREIGN KEY (`changed_by`) REFERENCES `staff` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table trustaxisdb.loan_status_history: ~0 rows (approximately)

-- Dumping structure for table trustaxisdb.loan_statuses
CREATE TABLE IF NOT EXISTS `loan_statuses` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(30) NOT NULL,
  `label` varchar(60) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table trustaxisdb.loan_statuses: ~0 rows (approximately)

-- Dumping structure for table trustaxisdb.loans
CREATE TABLE IF NOT EXISTS `loans` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `loan_number` varchar(30) NOT NULL,
  `loan_application_id` bigint(20) unsigned DEFAULT NULL,
  `customer_id` bigint(20) unsigned NOT NULL,
  `product_id` int(10) unsigned NOT NULL,
  `loan_officer_id` bigint(20) unsigned DEFAULT NULL,
  `principal_amount` decimal(15,2) NOT NULL,
  `interest_amount` decimal(15,2) NOT NULL,
  `total_fees` decimal(15,2) NOT NULL DEFAULT 0.00,
  `total_repayable` decimal(15,2) NOT NULL,
  `outstanding_balance` decimal(15,2) NOT NULL,
  `disbursement_date` date DEFAULT NULL,
  `due_date` date DEFAULT NULL,
  `next_payment_date` date DEFAULT NULL,
  `status_id` int(10) unsigned NOT NULL,
  `par_bucket_id` int(10) unsigned DEFAULT NULL,
  `days_past_due` smallint(5) unsigned NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `loan_number` (`loan_number`),
  KEY `loan_application_id` (`loan_application_id`),
  KEY `product_id` (`product_id`),
  KEY `loan_officer_id` (`loan_officer_id`),
  KEY `par_bucket_id` (`par_bucket_id`),
  KEY `idx_customer` (`customer_id`),
  KEY `idx_status` (`status_id`),
  KEY `idx_next_payment` (`next_payment_date`),
  CONSTRAINT `loans_ibfk_1` FOREIGN KEY (`loan_application_id`) REFERENCES `loan_applications` (`id`),
  CONSTRAINT `loans_ibfk_2` FOREIGN KEY (`customer_id`) REFERENCES `customer_profiles` (`id`),
  CONSTRAINT `loans_ibfk_3` FOREIGN KEY (`product_id`) REFERENCES `loan_products` (`id`),
  CONSTRAINT `loans_ibfk_4` FOREIGN KEY (`loan_officer_id`) REFERENCES `staff` (`id`),
  CONSTRAINT `loans_ibfk_5` FOREIGN KEY (`status_id`) REFERENCES `loan_statuses` (`id`),
  CONSTRAINT `loans_ibfk_6` FOREIGN KEY (`par_bucket_id`) REFERENCES `par_buckets` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table trustaxisdb.loans: ~0 rows (approximately)

-- Dumping structure for table trustaxisdb.mpesa_b2c_requests
CREATE TABLE IF NOT EXISTS `mpesa_b2c_requests` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `purpose` enum('loan_disbursement','investment_withdrawal','savings_withdrawal') NOT NULL,
  `reference_type` varchar(30) NOT NULL,
  `reference_id` bigint(20) unsigned NOT NULL,
  `command_id` enum('BusinessPayment','SalaryPayment','PromotionPayment') NOT NULL DEFAULT 'BusinessPayment',
  `phone_number` varchar(20) NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `conversation_id` varchar(50) DEFAULT NULL,
  `originator_conversation_id` varchar(50) DEFAULT NULL,
  `approval_status` enum('Not_Required','Pending','Approved','Rejected') NOT NULL DEFAULT 'Not_Required',
  `status` enum('Awaiting_Approval','Queued','Pending','Success','Failed','Rejected') NOT NULL DEFAULT 'Awaiting_Approval',
  `result_code` varchar(10) DEFAULT NULL,
  `result_desc` varchar(255) DEFAULT NULL,
  `mpesa_transaction_id` bigint(20) unsigned DEFAULT NULL,
  `initiated_by` bigint(20) unsigned NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `conversation_id` (`conversation_id`),
  KEY `mpesa_transaction_id` (`mpesa_transaction_id`),
  KEY `initiated_by` (`initiated_by`),
  KEY `idx_status` (`status`),
  CONSTRAINT `mpesa_b2c_requests_ibfk_1` FOREIGN KEY (`mpesa_transaction_id`) REFERENCES `mpesa_transactions` (`id`),
  CONSTRAINT `mpesa_b2c_requests_ibfk_2` FOREIGN KEY (`initiated_by`) REFERENCES `staff` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table trustaxisdb.mpesa_b2c_requests: ~0 rows (approximately)

-- Dumping structure for table trustaxisdb.mpesa_stk_requests
CREATE TABLE IF NOT EXISTS `mpesa_stk_requests` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `purpose` enum('loan_repayment','investment_deposit','savings_deposit') NOT NULL,
  `reference_type` varchar(30) NOT NULL,
  `reference_id` bigint(20) unsigned NOT NULL,
  `phone_number` varchar(20) NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `merchant_request_id` varchar(50) NOT NULL,
  `checkout_request_id` varchar(50) NOT NULL,
  `status` enum('Pending','Success','Failed','Cancelled','Timeout') NOT NULL DEFAULT 'Pending',
  `result_code` varchar(10) DEFAULT NULL,
  `result_desc` varchar(255) DEFAULT NULL,
  `mpesa_transaction_id` bigint(20) unsigned DEFAULT NULL,
  `initiated_by` bigint(20) unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `checkout_request_id` (`checkout_request_id`),
  KEY `mpesa_transaction_id` (`mpesa_transaction_id`),
  KEY `initiated_by` (`initiated_by`),
  CONSTRAINT `mpesa_stk_requests_ibfk_1` FOREIGN KEY (`mpesa_transaction_id`) REFERENCES `mpesa_transactions` (`id`),
  CONSTRAINT `mpesa_stk_requests_ibfk_2` FOREIGN KEY (`initiated_by`) REFERENCES `staff` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table trustaxisdb.mpesa_stk_requests: ~0 rows (approximately)

-- Dumping structure for table trustaxisdb.mpesa_transactions
CREATE TABLE IF NOT EXISTS `mpesa_transactions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `transaction_type` enum('C2B','STK_PUSH','B2C') NOT NULL,
  `direction` enum('Inbound','Outbound') NOT NULL,
  `mpesa_receipt_number` varchar(20) NOT NULL,
  `msisdn` varchar(20) NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `account_reference` varchar(50) DEFAULT NULL,
  `transaction_time` datetime NOT NULL,
  `reference_type` enum('loan_repayment','loan_disbursement','investment_deposit','investment_withdrawal','savings_deposit','savings_withdrawal','unmatched') NOT NULL DEFAULT 'unmatched',
  `reference_id` bigint(20) unsigned DEFAULT NULL,
  `match_status` enum('Matched','Unmatched','Manually_Assigned') NOT NULL DEFAULT 'Unmatched',
  `matched_by` bigint(20) unsigned DEFAULT NULL,
  `raw_payload` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`raw_payload`)),
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `mpesa_receipt_number` (`mpesa_receipt_number`),
  KEY `matched_by` (`matched_by`),
  KEY `idx_msisdn` (`msisdn`),
  KEY `idx_match_status` (`match_status`),
  CONSTRAINT `mpesa_transactions_ibfk_1` FOREIGN KEY (`matched_by`) REFERENCES `staff` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table trustaxisdb.mpesa_transactions: ~0 rows (approximately)

-- Dumping structure for table trustaxisdb.opex_entries
CREATE TABLE IF NOT EXISTS `opex_entries` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `month` date NOT NULL,
  `category` varchar(50) NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `notes` varchar(255) DEFAULT NULL,
  `entered_by` bigint(20) unsigned NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `entered_by` (`entered_by`),
  CONSTRAINT `opex_entries_ibfk_1` FOREIGN KEY (`entered_by`) REFERENCES `staff` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table trustaxisdb.opex_entries: ~0 rows (approximately)

-- Dumping structure for table trustaxisdb.par_buckets
CREATE TABLE IF NOT EXISTS `par_buckets` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(30) NOT NULL,
  `label` varchar(60) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table trustaxisdb.par_buckets: ~0 rows (approximately)

-- Dumping structure for table trustaxisdb.permissions
CREATE TABLE IF NOT EXISTS `permissions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(80) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `module` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table trustaxisdb.permissions: ~14 rows (approximately)
INSERT INTO `permissions` (`id`, `code`, `description`, `module`) VALUES
	(1, 'product.view', 'View loan/investment product configs', 'products'),
	(2, 'product.edit_terms', 'Create or edit product terms', 'products'),
	(3, 'customer.onboard', 'Onboard new customers', 'customers'),
	(4, 'customer.view', 'View customer/loan details', 'customers'),
	(5, 'loan.underwrite', 'Review underwriting queue, assign credit limits', 'loans'),
	(6, 'loan.disburse', 'Trigger loan disbursement', 'loans'),
	(7, 'reconciliation.manage', 'Reconcile M-PESA transactions', 'reconciliation'),
	(8, 'reserves.manage', 'Manage reserve calculations', 'finance'),
	(9, 'withdrawal.process', 'Process investor/savings withdrawals', 'finance'),
	(10, 'approval.decide', 'Approve or reject pending B2C approvals', 'finance'),
	(11, 'report.profitability', 'View Profitability report', 'reports'),
	(12, 'report.salaries', 'View Salaries / opex report', 'reports'),
	(13, 'report.portfolio', 'View Portfolio Overview / Loan Ledger', 'reports'),
	(14, 'report.investment', 'View Investment & Liability report', 'reports');

-- Dumping structure for table trustaxisdb.repayments
CREATE TABLE IF NOT EXISTS `repayments` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `loan_id` bigint(20) unsigned NOT NULL,
  `schedule_id` bigint(20) unsigned DEFAULT NULL,
  `amount` decimal(15,2) NOT NULL,
  `amount_type` enum('Principal','Interest','Fee','Penalty') NOT NULL,
  `transaction_reference` varchar(50) DEFAULT NULL,
  `paid_at` datetime NOT NULL,
  `channel` enum('M-PESA','Manual','Bank') NOT NULL,
  `recorded_by` bigint(20) unsigned DEFAULT NULL,
  `voided_at` datetime DEFAULT NULL,
  `voided_by` bigint(20) unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `schedule_id` (`schedule_id`),
  KEY `recorded_by` (`recorded_by`),
  KEY `voided_by` (`voided_by`),
  KEY `idx_loan` (`loan_id`),
  KEY `idx_paid_at` (`paid_at`),
  CONSTRAINT `repayments_ibfk_1` FOREIGN KEY (`loan_id`) REFERENCES `loans` (`id`),
  CONSTRAINT `repayments_ibfk_2` FOREIGN KEY (`schedule_id`) REFERENCES `loan_repayment_schedule` (`id`),
  CONSTRAINT `repayments_ibfk_3` FOREIGN KEY (`recorded_by`) REFERENCES `staff` (`id`),
  CONSTRAINT `repayments_ibfk_4` FOREIGN KEY (`voided_by`) REFERENCES `staff` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table trustaxisdb.repayments: ~0 rows (approximately)

-- Dumping structure for table trustaxisdb.reserve_calculations
CREATE TABLE IF NOT EXISTS `reserve_calculations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `calc_date` date NOT NULL,
  `akiba_yangu_reserve` decimal(15,2) NOT NULL,
  `bond_reserve` decimal(15,2) NOT NULL,
  `akiba_flow_reserve` decimal(15,2) NOT NULL,
  `total_cash_balance` decimal(15,2) NOT NULL,
  `available_for_lending` decimal(15,2) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table trustaxisdb.reserve_calculations: ~0 rows (approximately)

-- Dumping structure for table trustaxisdb.role_permissions
CREATE TABLE IF NOT EXISTS `role_permissions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `role_id` int(10) unsigned NOT NULL,
  `permission_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_role_permission` (`role_id`,`permission_id`),
  KEY `permission_id` (`permission_id`),
  CONSTRAINT `role_permissions_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`),
  CONSTRAINT `role_permissions_ibfk_2` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table trustaxisdb.role_permissions: ~26 rows (approximately)
INSERT INTO `role_permissions` (`id`, `role_id`, `permission_id`) VALUES
	(1, 1, 10),
	(2, 1, 3),
	(3, 1, 4),
	(4, 1, 6),
	(5, 1, 5),
	(6, 1, 2),
	(7, 1, 1),
	(8, 1, 7),
	(9, 1, 14),
	(10, 1, 13),
	(11, 1, 11),
	(12, 1, 12),
	(13, 1, 8),
	(14, 1, 9),
	(16, 2, 3),
	(17, 2, 4),
	(18, 2, 6),
	(19, 2, 5),
	(20, 2, 1),
	(23, 3, 10),
	(24, 3, 4),
	(25, 3, 7),
	(26, 3, 14),
	(27, 3, 13),
	(28, 3, 8),
	(29, 3, 9);

-- Dumping structure for table trustaxisdb.roles
CREATE TABLE IF NOT EXISTS `roles` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table trustaxisdb.roles: ~3 rows (approximately)
INSERT INTO `roles` (`id`, `name`, `description`) VALUES
	(1, 'Admin', 'Full access to all data, product configs, and reports'),
	(2, 'Loan Officer', 'Onboard customers, view loan details, trigger disbursements'),
	(3, 'Finance Officer', 'Reconcile M-PESA, manage reserves, process withdrawals, view P&L');

-- Dumping structure for table trustaxisdb.savings_accounts
CREATE TABLE IF NOT EXISTS `savings_accounts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` bigint(20) unsigned NOT NULL,
  `balance` decimal(15,2) NOT NULL DEFAULT 0.00,
  `bonus_rate` decimal(6,3) NOT NULL DEFAULT 0.000,
  `maturity_date` date DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `customer_id` (`customer_id`),
  CONSTRAINT `savings_accounts_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customer_profiles` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table trustaxisdb.savings_accounts: ~0 rows (approximately)

-- Dumping structure for table trustaxisdb.savings_transactions
CREATE TABLE IF NOT EXISTS `savings_transactions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `savings_account_id` bigint(20) unsigned NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `type` enum('Deposit','Withdrawal','Interest') NOT NULL,
  `reference` varchar(50) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `savings_account_id` (`savings_account_id`),
  CONSTRAINT `savings_transactions_ibfk_1` FOREIGN KEY (`savings_account_id`) REFERENCES `savings_accounts` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table trustaxisdb.savings_transactions: ~0 rows (approximately)

-- Dumping structure for table trustaxisdb.sms_logs
CREATE TABLE IF NOT EXISTS `sms_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `sms_template_id` int(10) unsigned DEFAULT NULL,
  `event_code` varchar(20) NOT NULL,
  `recipient` varchar(20) NOT NULL,
  `variables_used` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`variables_used`)),
  `message` text NOT NULL,
  `status` enum('Sent','Failed','Pending') NOT NULL DEFAULT 'Pending',
  `provider_reference` varchar(100) DEFAULT NULL,
  `sent_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `sms_template_id` (`sms_template_id`),
  KEY `idx_event_code` (`event_code`),
  KEY `idx_recipient` (`recipient`),
  CONSTRAINT `sms_logs_ibfk_1` FOREIGN KEY (`sms_template_id`) REFERENCES `sms_templates` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table trustaxisdb.sms_logs: ~0 rows (approximately)

-- Dumping structure for table trustaxisdb.sms_templates
CREATE TABLE IF NOT EXISTS `sms_templates` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `event_code` varchar(20) NOT NULL,
  `name` varchar(100) NOT NULL,
  `recipient_type` enum('Borrower','Investor','Admin') NOT NULL,
  `template_text` varchar(500) NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `updated_by` bigint(20) unsigned DEFAULT NULL,
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `event_code` (`event_code`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `sms_templates_ibfk_1` FOREIGN KEY (`updated_by`) REFERENCES `staff` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table trustaxisdb.sms_templates: ~7 rows (approximately)
INSERT INTO `sms_templates` (`id`, `event_code`, `name`, `recipient_type`, `template_text`, `is_active`, `updated_by`, `updated_at`) VALUES
	(1, 'SMS_01', 'Loan Application Submitted', 'Borrower', 'TrustAxis: Your loan request for KES {amount} has been received. We are reviewing it.', 1, NULL, '2026-09-13 08:20:44'),
	(2, 'SMS_02', 'Loan Disbursed', 'Borrower', 'TrustAxis: KES {amount} has been sent to your M-PESA. Your daily repayment is KES {daily_amount} for {tenor} days.', 1, NULL, '2026-09-13 08:20:44'),
	(3, 'SMS_03', 'Payment Received', 'Borrower', 'TrustAxis: We have received KES {amount} for Loan {loan_number}. Outstanding: KES {outstanding}. Thank you.', 1, NULL, '2026-09-13 08:20:44'),
	(4, 'SMS_04', 'Payment Missed (8:00 AM)', 'Borrower', 'TrustAxis: Reminder, your payment of KES {amount} is due today. Please pay via Paybill 4328599.', 1, NULL, '2026-09-13 08:20:44'),
	(5, 'SMS_05', 'New Application Alert', 'Admin', 'TrustAxis: {name} has requested KES {amount}. Login to approve.', 1, NULL, '2026-09-13 08:20:44'),
	(6, 'SMS_06', 'Investment Maturity', 'Investor', 'TrustAxis: Your Akiba Flow investment of KES {principal} matures on {maturity_date}. You have earned KES {interest_earned}.', 1, NULL, '2026-09-13 08:20:44'),
	(7, 'SMS_07', 'Withdrawal Processed', 'Investor', 'TrustAxis: KES {amount} has been sent to your M-PESA. Thank you for investing with us.', 1, NULL, '2026-09-13 08:20:44');

-- Dumping structure for table trustaxisdb.staff
CREATE TABLE IF NOT EXISTS `staff` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `role_id` int(10) unsigned NOT NULL,
  `branch_id` int(10) unsigned DEFAULT NULL,
  `full_name` varchar(150) NOT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `role_id` (`role_id`),
  KEY `branch_id` (`branch_id`),
  CONSTRAINT `staff_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  CONSTRAINT `staff_ibfk_2` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`),
  CONSTRAINT `staff_ibfk_3` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table trustaxisdb.staff: ~1 rows (approximately)
INSERT INTO `staff` (`id`, `user_id`, `role_id`, `branch_id`, `full_name`, `is_active`, `created_at`) VALUES
	(1, 1, 1, 1, 'SYSTEM ADMIN', 1, '2026-09-13 10:25:29');

-- Dumping structure for table trustaxisdb.system_settings
CREATE TABLE IF NOT EXISTS `system_settings` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` varchar(255) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `updated_by` bigint(20) unsigned DEFAULT NULL,
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `setting_key` (`setting_key`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `system_settings_ibfk_1` FOREIGN KEY (`updated_by`) REFERENCES `staff` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table trustaxisdb.system_settings: ~0 rows (approximately)

-- Dumping structure for table trustaxisdb.trace_log
CREATE TABLE IF NOT EXISTS `trace_log` (
  `trace_id` varchar(50) NOT NULL,
  `trace_type` varchar(100) NOT NULL,
  `entry_point` varchar(255) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `request_method` varchar(10) DEFAULT NULL,
  `request_payload` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `response_summary` varchar(500) DEFAULT NULL,
  `status` enum('Success','Failed','Partial') DEFAULT 'Success',
  `started_at` datetime(3) NOT NULL,
  `ended_at` datetime(3) DEFAULT NULL,
  `duration_ms` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`trace_id`) USING BTREE,
  KEY `idx_trace_type` (`trace_type`) USING BTREE,
  KEY `idx_trace_started` (`started_at`) USING BTREE,
  CONSTRAINT `request_payload` CHECK (json_valid(`request_payload`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table trustaxisdb.trace_log: ~18 rows (approximately)
INSERT INTO `trace_log` (`trace_id`, `trace_type`, `entry_point`, `user_id`, `ip_address`, `request_method`, `request_payload`, `response_summary`, `status`, `started_at`, `ended_at`, `duration_ms`, `created_at`) VALUES
	('TRC-20260913-101605-9683f2e4', 'web_request', 'welcome/index', NULL, '127.0.0.1', 'GET', NULL, NULL, 'Success', '2026-09-13 10:16:05.270', NULL, NULL, '2026-09-13 07:16:05'),
	('TRC-20260913-101758-facba078', 'web_request', 'welcome/index', NULL, '127.0.0.1', 'GET', NULL, NULL, 'Success', '2026-09-13 10:17:58.823', NULL, NULL, '2026-09-13 07:17:58'),
	('TRC-20260913-101824-3b241bed', 'web_request', 'welcome/index', NULL, '127.0.0.1', 'GET', NULL, NULL, 'Success', '2026-09-13 10:18:24.944', NULL, NULL, '2026-09-13 07:18:24'),
	('TRC-20260913-101931-835a20d6', 'web_request', 'welcome/index', NULL, '127.0.0.1', 'GET', NULL, 'Request completed', 'Success', '2026-09-13 10:19:31.301', '2026-09-13 10:19:31.322', 21, '2026-09-13 07:19:31'),
	('TRC-20260913-102432-4a3ca0cb', 'web_request', 'welcome/index', NULL, '127.0.0.1', 'GET', NULL, 'Request completed', 'Success', '2026-09-13 10:24:32.659', '2026-09-13 10:24:32.676', 18, '2026-09-13 07:24:32'),
	('TRC-20260913-102732-04b5995e', 'web_request', 'welcome/index', NULL, '127.0.0.1', 'GET', NULL, 'Request completed', 'Success', '2026-09-13 10:27:32.460', '2026-09-13 10:27:32.481', 20, '2026-09-13 07:27:32'),
	('TRC-20260913-102854-e7481784', 'web_request', 'welcome/index', NULL, '127.0.0.1', 'GET', NULL, 'Request completed', 'Success', '2026-09-13 10:28:54.450', '2026-09-13 10:28:54.469', 19, '2026-09-13 07:28:54'),
	('TRC-20260913-103130-fb49813f', 'web_request', 'welcome/index', NULL, '127.0.0.1', 'GET', NULL, 'Request completed', 'Success', '2026-09-13 10:31:30.404', '2026-09-13 10:31:30.420', 15, '2026-09-13 07:31:30'),
	('TRC-20260913-103257-5b5442bd', 'web_request', 'welcome/index', NULL, '127.0.0.1', 'GET', NULL, 'Request completed', 'Success', '2026-09-13 10:32:57.952', '2026-09-13 10:32:57.970', 17, '2026-09-13 07:32:57'),
	('TRC-20260913-103510-0ab83cdc', 'web_request', 'welcome/index', NULL, '127.0.0.1', 'GET', NULL, 'Request completed', 'Success', '2026-09-13 10:35:10.964', '2026-09-13 10:35:10.980', 16, '2026-09-13 07:35:10'),
	('TRC-20260913-103616-e9cb9233', 'web_request', 'welcome/index', NULL, '127.0.0.1', 'GET', NULL, 'Request completed', 'Success', '2026-09-13 10:36:16.190', '2026-09-13 10:36:16.209', 18, '2026-09-13 07:36:16'),
	('TRC-20260913-104110-8a6cabd0', 'web_request', 'welcome/index', NULL, '127.0.0.1', 'GET', NULL, 'Request completed', 'Success', '2026-09-13 10:41:10.841', '2026-09-13 10:41:10.854', 14, '2026-09-13 07:41:10'),
	('TRC-20260913-104158-e99acdb2', 'web_request', 'welcome/index', NULL, '127.0.0.1', 'GET', NULL, 'Request completed', 'Success', '2026-09-13 10:41:58.275', '2026-09-13 10:41:58.295', 20, '2026-09-13 07:41:58'),
	('TRC-20260913-104808-4d011ee0', 'web_request', 'welcome/index', NULL, '127.0.0.1', 'GET', NULL, 'Request completed', 'Success', '2026-09-13 10:48:08.080', '2026-09-13 10:48:08.093', 12, '2026-09-13 07:48:08'),
	('TRC-20260913-104812-4e6169d3', 'web_request', 'auth/login', NULL, '127.0.0.1', 'POST', '{"user_id":"1","iusername":"admin@glob.admin","ipassword":"admin123","is_ajax":"1","form":"login"}', NULL, 'Success', '2026-09-13 10:48:12.571', NULL, NULL, '2026-09-13 07:48:12'),
	('TRC-20260913-105017-e60116e3', 'web_request', 'welcome/index', NULL, '127.0.0.1', 'GET', NULL, 'Request completed', 'Success', '2026-09-13 10:50:17.443', '2026-09-13 10:50:17.457', 14, '2026-09-13 07:50:17'),
	('TRC-20260913-105019-b549b078', 'web_request', 'auth/login', NULL, '127.0.0.1', 'POST', '{"user_id":"1","iusername":"admin@glob.admin","ipassword":"admin123","is_ajax":"1","form":"login"}', NULL, 'Success', '2026-09-13 10:50:19.398', NULL, NULL, '2026-09-13 07:50:19'),
	('TRC-20260913-105632-34b002e2', 'web_request', 'auth/login', NULL, '127.0.0.1', 'POST', '{"user_id":"1","iusername":"admin@glob.admin","ipassword":"admin123","is_ajax":"1","form":"login"}', NULL, 'Success', '2026-09-13 10:56:32.142', NULL, NULL, '2026-09-13 07:56:32'),
	('TRC-20260913-105713-2336e419', 'web_request', 'auth/login', NULL, '127.0.0.1', 'POST', '{"user_id":"1","iusername":"admin@glob.admin","ipassword":"admin123","is_ajax":"1","form":"login"}', NULL, 'Success', '2026-09-13 10:57:13.908', NULL, NULL, '2026-09-13 07:57:13'),
	('TRC-20260913-105721-90a6b14d', 'web_request', 'auth/login', NULL, '127.0.0.1', 'POST', '{"user_id":"1","iusername":"admin@glob.admin","ipassword":"admin123","is_ajax":"1","form":"login"}', NULL, 'Success', '2026-09-13 10:57:21.945', NULL, NULL, '2026-09-13 07:57:21'),
	('TRC-20260913-105742-d6dadc42', 'web_request', 'welcome/index', NULL, '127.0.0.1', 'GET', NULL, 'Request completed', 'Success', '2026-09-13 10:57:42.999', '2026-09-13 10:57:43.015', 16, '2026-09-13 07:57:42'),
	('TRC-20260913-105746-78ccec3e', 'web_request', 'auth/login', NULL, '127.0.0.1', 'POST', '{"user_id":"1","iusername":"admin@glob.admin","ipassword":"admin123","is_ajax":"1","form":"login"}', NULL, 'Success', '2026-09-13 10:57:46.731', NULL, NULL, '2026-09-13 07:57:46'),
	('TRC-20260913-110715-b8ee8eb0', 'web_request', 'welcome/index', NULL, '127.0.0.1', 'GET', NULL, 'Request completed', 'Success', '2026-09-13 11:07:15.705', '2026-09-13 11:07:15.715', 9, '2026-09-13 08:07:15');

-- Dumping structure for table trustaxisdb.users
CREATE TABLE IF NOT EXISTS `users` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_type` enum('customer','investor','staff') NOT NULL,
  `phone_number` varchar(20) DEFAULT NULL,
  `username` varchar(150) DEFAULT NULL,
  `password_hash` varchar(255) NOT NULL,
  `pin_hash` varchar(255) DEFAULT NULL,
  `mfa_secret` varchar(255) DEFAULT NULL,
  `status` enum('Active','Suspended','Locked') NOT NULL DEFAULT 'Active',
  `is_active` int(11) NOT NULL DEFAULT 1,
  `last_login_at` datetime DEFAULT NULL,
  `last_login_ip` varchar(45) DEFAULT NULL,
  `is_logged_in` tinyint(1) NOT NULL DEFAULT 0,
  `failed_login_attempts` tinyint(3) unsigned NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `phone_number` (`phone_number`),
  UNIQUE KEY `email` (`username`) USING BTREE,
  KEY `idx_user_type` (`user_type`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table trustaxisdb.users: ~1 rows (approximately)
INSERT INTO `users` (`id`, `user_type`, `phone_number`, `username`, `password_hash`, `pin_hash`, `mfa_secret`, `status`, `is_active`, `last_login_at`, `last_login_ip`, `is_logged_in`, `failed_login_attempts`, `created_at`, `updated_at`) VALUES
	(1, 'staff', '0708843466', 'admin@glob.admin', '$2y$12$UjxfpfzhRF9qAsCWl5HPceRbkeJfQci7xs2ODzCl6BhJ3Lb3GxELu', NULL, NULL, 'Locked', 1, '2026-09-13 10:12:32', NULL, 0, 0, '2026-09-13 10:12:38', '2026-09-13 10:57:38');

-- Dumping structure for table trustaxisdb.xin_company_info
CREATE TABLE IF NOT EXISTS `xin_company_info` (
  `company_info_id` int(111) NOT NULL AUTO_INCREMENT,
  `logo` varchar(255) NOT NULL,
  `supported_by` varchar(255) NOT NULL,
  `logo_second` varchar(255) NOT NULL,
  `sign_in_logo` varchar(255) NOT NULL,
  `favicon` varchar(255) NOT NULL,
  `website_url` mediumtext NOT NULL,
  `start_year` varchar(255) NOT NULL,
  `company_name` varchar(255) NOT NULL,
  `moe_code` varchar(100) NOT NULL,
  `reg_number` varchar(255) NOT NULL,
  `knec_code` varchar(100) NOT NULL,
  `tsc_code` varchar(100) NOT NULL,
  `starting_year` varchar(100) NOT NULL,
  `motto` varchar(255) NOT NULL,
  `school_status` varchar(255) NOT NULL,
  `school_sponsor` varchar(255) NOT NULL,
  `school_type` varchar(255) NOT NULL,
  `school_category` varchar(255) NOT NULL,
  `school_mission` text NOT NULL,
  `school_vision` text NOT NULL,
  `company_email` varchar(255) NOT NULL,
  `company_contact` varchar(255) NOT NULL,
  `contact_person` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(255) NOT NULL,
  `address_1` mediumtext NOT NULL,
  `address_2` mediumtext NOT NULL,
  `city` varchar(255) NOT NULL,
  `state` varchar(255) NOT NULL,
  `zipcode` varchar(255) NOT NULL,
  `updated_by` varchar(255) DEFAULT NULL,
  `country` int(111) NOT NULL,
  `updated_at` datetime NOT NULL,
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0,
  `deleted_at` datetime DEFAULT NULL,
  `deleted_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`company_info_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- Dumping data for table trustaxisdb.xin_company_info: ~1 rows (approximately)
INSERT INTO `xin_company_info` (`company_info_id`, `logo`, `supported_by`, `logo_second`, `sign_in_logo`, `favicon`, `website_url`, `start_year`, `company_name`, `moe_code`, `reg_number`, `knec_code`, `tsc_code`, `starting_year`, `motto`, `school_status`, `school_sponsor`, `school_type`, `school_category`, `school_mission`, `school_vision`, `company_email`, `company_contact`, `contact_person`, `email`, `phone`, `address_1`, `address_2`, `city`, `state`, `zipcode`, `updated_by`, `country`, `updated_at`, `is_deleted`, `deleted_at`, `deleted_by`) VALUES
	(1, 'logo_1784295969.jpeg', 'TRUSTAXIS', 'logo2_1520609223.png', 'tufa.jpeg', 'favicon_1784297662.jpeg', '', '1963', 'TRUSTAXIS CAPITAL LIMITED', 'MOE', 'NUMBER020202', 'KNEC_CODE', 'TSD', '', 'The solution that works', 'Private', 'TUSA MICROCREDIT LIMITED', 'Mixed', 'Boarding', 'To be a leading center of academic excellence, character development, and innovation for a better society.', 'To provide quality, inclusive, and value-based education that equips learners with knowledge, skills, and attitudes necessary for personal and national development.', '', '', '', 'technical@vsoft.co.ke', '123456789', 'Address Line 1', 'Address Line 2', '', '', '', '1', 113, '2026-07-19 18:47:35', 0, NULL, NULL);

-- Dumping structure for table trustaxisdb.xin_system_setting
CREATE TABLE IF NOT EXISTS `xin_system_setting` (
  `setting_id` int(111) NOT NULL,
  `school_id` int(11) NOT NULL DEFAULT 0,
  `application_name` varchar(255) NOT NULL,
  `supported_by` varchar(255) NOT NULL,
  `software_version` varchar(255) NOT NULL,
  `default_currency` varchar(255) NOT NULL,
  `default_currency_id` int(11) NOT NULL,
  `default_currency_symbol` varchar(255) NOT NULL,
  `show_currency` varchar(255) NOT NULL,
  `currency_position` varchar(255) NOT NULL,
  `notification_position` varchar(255) NOT NULL,
  `notification_close_btn` varchar(255) NOT NULL,
  `notification_bar` varchar(255) NOT NULL,
  `enable_registration` varchar(255) NOT NULL,
  `login_with` varchar(255) NOT NULL,
  `date_format_xi` varchar(255) NOT NULL,
  `employee_manage_own_contact` varchar(255) NOT NULL,
  `employee_manage_own_profile` varchar(255) NOT NULL,
  `employee_manage_own_qualification` varchar(255) NOT NULL,
  `employee_manage_own_work_experience` varchar(255) NOT NULL,
  `employee_manage_own_document` varchar(255) NOT NULL,
  `employee_manage_own_picture` varchar(255) NOT NULL,
  `employee_manage_own_social` varchar(255) NOT NULL,
  `employee_manage_own_bank_account` varchar(255) NOT NULL,
  `enable_attendance` varchar(255) NOT NULL,
  `enable_clock_in_btn` varchar(255) NOT NULL,
  `enable_email_notification` varchar(255) NOT NULL,
  `payroll_include_day_summary` varchar(255) NOT NULL,
  `payroll_include_hour_summary` varchar(255) NOT NULL,
  `payroll_include_leave_summary` varchar(255) NOT NULL,
  `enable_job_application_candidates` varchar(255) NOT NULL,
  `job_logo` varchar(255) NOT NULL,
  `payroll_logo` varchar(255) NOT NULL,
  `is_payslip_password_generate` int(11) NOT NULL,
  `payslip_password_format` varchar(255) NOT NULL,
  `nssf_model` varchar(50) NOT NULL,
  `tax_relief` decimal(10,2) NOT NULL DEFAULT 0.00,
  `enable_profile_background` varchar(255) NOT NULL,
  `enable_policy_link` varchar(255) NOT NULL,
  `enable_layout` varchar(255) NOT NULL,
  `job_application_format` mediumtext NOT NULL,
  `output_competencies` mediumtext DEFAULT NULL,
  `teamwork_competencies` mediumtext DEFAULT NULL,
  `quality_work` text DEFAULT NULL,
  `job_knowledge` text DEFAULT NULL,
  `initiative_competencies` text DEFAULT NULL,
  `planning_organising_competencies` text DEFAULT NULL,
  `problem_competencies` text DEFAULT NULL,
  `dependability_competencies` text DEFAULT NULL,
  `leadership_competencies` text DEFAULT NULL,
  `judgement_competencies` text DEFAULT NULL,
  `interpersonal_competencies` mediumtext DEFAULT NULL,
  `communication_competencies` mediumtext DEFAULT NULL,
  `performance_option` varchar(255) DEFAULT NULL,
  `project_email` varchar(255) NOT NULL,
  `holiday_email` varchar(255) NOT NULL,
  `leave_email` varchar(255) NOT NULL,
  `payslip_email` varchar(255) NOT NULL,
  `award_email` varchar(255) NOT NULL,
  `recruitment_email` varchar(255) NOT NULL,
  `announcement_email` varchar(255) NOT NULL,
  `training_email` varchar(255) NOT NULL,
  `task_email` varchar(255) NOT NULL,
  `compact_sidebar` varchar(255) NOT NULL,
  `fixed_header` varchar(255) NOT NULL,
  `fixed_sidebar` varchar(255) NOT NULL,
  `boxed_wrapper` varchar(255) NOT NULL,
  `layout_static` varchar(255) NOT NULL,
  `system_skin` varchar(255) NOT NULL,
  `animation_effect` varchar(255) NOT NULL,
  `animation_effect_modal` varchar(255) NOT NULL,
  `animation_effect_topmenu` varchar(255) NOT NULL,
  `footer_text` varchar(255) NOT NULL,
  `is_ssl_available` varchar(50) NOT NULL,
  `is_active_sub_departments` varchar(10) NOT NULL,
  `default_language` varchar(200) NOT NULL,
  `statutory_fixed` varchar(100) NOT NULL,
  `system_timezone` varchar(200) NOT NULL,
  `system_ip_address` varchar(255) NOT NULL,
  `system_ip_restriction` varchar(200) NOT NULL,
  `google_maps_api_key` mediumtext NOT NULL,
  `module_recruitment` varchar(100) NOT NULL,
  `module_travel` varchar(100) NOT NULL,
  `module_performance` varchar(100) NOT NULL,
  `module_payroll` varchar(10) NOT NULL,
  `module_files` varchar(100) NOT NULL,
  `module_awards` varchar(100) NOT NULL,
  `module_training` varchar(100) NOT NULL,
  `module_inquiry` varchar(100) NOT NULL,
  `module_language` varchar(100) NOT NULL,
  `module_orgchart` varchar(100) NOT NULL,
  `module_accounting` varchar(111) NOT NULL,
  `module_events` varchar(100) NOT NULL,
  `module_goal_tracking` varchar(100) NOT NULL,
  `module_assets` varchar(100) NOT NULL,
  `module_projects_tasks` varchar(100) NOT NULL,
  `module_chat_box` varchar(100) NOT NULL,
  `enable_page_rendered` varchar(255) NOT NULL,
  `enable_current_year` varchar(255) NOT NULL,
  `employee_login_id` varchar(200) NOT NULL,
  `paypal_email` varchar(100) NOT NULL,
  `paypal_sandbox` varchar(10) NOT NULL,
  `paypal_active` varchar(10) NOT NULL,
  `stripe_secret_key` varchar(200) NOT NULL,
  `stripe_publishable_key` varchar(200) NOT NULL,
  `stripe_active` varchar(10) NOT NULL,
  `online_payment_account` int(11) NOT NULL,
  `is_half_monthly` tinyint(1) NOT NULL,
  `half_deduct_month` tinyint(1) NOT NULL,
  `invoice_terms_condition` text DEFAULT NULL,
  `estimate_terms_condition` text DEFAULT NULL,
  `show_projects` int(11) NOT NULL DEFAULT 0,
  `show_tasks` int(11) NOT NULL DEFAULT 0,
  `enable_saudi_gosi` int(11) NOT NULL DEFAULT 0,
  `staff_dashboard` int(11) DEFAULT NULL,
  `project_dashboard` int(11) DEFAULT NULL,
  `enable_auth_background` varchar(11) NOT NULL,
  `hr_version` varchar(200) NOT NULL,
  `hr_release_date` varchar(100) NOT NULL,
  `hr_top_menu` text NOT NULL,
  `updated_at` varchar(255) NOT NULL,
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0,
  `deleted_at` datetime DEFAULT NULL,
  `deleted_by` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- Dumping data for table trustaxisdb.xin_system_setting: ~1 rows (approximately)
INSERT INTO `xin_system_setting` (`setting_id`, `school_id`, `application_name`, `supported_by`, `software_version`, `default_currency`, `default_currency_id`, `default_currency_symbol`, `show_currency`, `currency_position`, `notification_position`, `notification_close_btn`, `notification_bar`, `enable_registration`, `login_with`, `date_format_xi`, `employee_manage_own_contact`, `employee_manage_own_profile`, `employee_manage_own_qualification`, `employee_manage_own_work_experience`, `employee_manage_own_document`, `employee_manage_own_picture`, `employee_manage_own_social`, `employee_manage_own_bank_account`, `enable_attendance`, `enable_clock_in_btn`, `enable_email_notification`, `payroll_include_day_summary`, `payroll_include_hour_summary`, `payroll_include_leave_summary`, `enable_job_application_candidates`, `job_logo`, `payroll_logo`, `is_payslip_password_generate`, `payslip_password_format`, `nssf_model`, `tax_relief`, `enable_profile_background`, `enable_policy_link`, `enable_layout`, `job_application_format`, `output_competencies`, `teamwork_competencies`, `quality_work`, `job_knowledge`, `initiative_competencies`, `planning_organising_competencies`, `problem_competencies`, `dependability_competencies`, `leadership_competencies`, `judgement_competencies`, `interpersonal_competencies`, `communication_competencies`, `performance_option`, `project_email`, `holiday_email`, `leave_email`, `payslip_email`, `award_email`, `recruitment_email`, `announcement_email`, `training_email`, `task_email`, `compact_sidebar`, `fixed_header`, `fixed_sidebar`, `boxed_wrapper`, `layout_static`, `system_skin`, `animation_effect`, `animation_effect_modal`, `animation_effect_topmenu`, `footer_text`, `is_ssl_available`, `is_active_sub_departments`, `default_language`, `statutory_fixed`, `system_timezone`, `system_ip_address`, `system_ip_restriction`, `google_maps_api_key`, `module_recruitment`, `module_travel`, `module_performance`, `module_payroll`, `module_files`, `module_awards`, `module_training`, `module_inquiry`, `module_language`, `module_orgchart`, `module_accounting`, `module_events`, `module_goal_tracking`, `module_assets`, `module_projects_tasks`, `module_chat_box`, `enable_page_rendered`, `enable_current_year`, `employee_login_id`, `paypal_email`, `paypal_sandbox`, `paypal_active`, `stripe_secret_key`, `stripe_publishable_key`, `stripe_active`, `online_payment_account`, `is_half_monthly`, `half_deduct_month`, `invoice_terms_condition`, `estimate_terms_condition`, `show_projects`, `show_tasks`, `enable_saudi_gosi`, `staff_dashboard`, `project_dashboard`, `enable_auth_background`, `hr_version`, `hr_release_date`, `hr_top_menu`, `updated_at`, `is_deleted`, `deleted_at`, `deleted_by`) VALUES
	(1, 1, 'TRUSTAXIS CAPITAL LIMITED', 'Developed and Maintained By TRUSTAXIS ICT', '1.0.0', 'KES - KSh', 1, 'KES - KSh', 'symbol', 'Prefix', 'toast-top-center', 'true', 'true', 'no', 'username', 'd-M-Y', 'yes', '', '', '', 'yes', 'yes', 'yes', '', 'no', 'no', 'no', 'yes', 'yes', 'yes', '1', 'job_logo_1784295986.jpeg', 'payroll_logo_1784295961.jpeg', 0, 'employee_id', 'Tier1_2', 2400.00, 'yes', 'yes', 'yes', 'doc,docx,pdf', 'Competence,Thoroughness,Neatness and Accuracy,Effeciency of Work', 'Establish and maintain effective working relationship with others,Shares information and resource with others,Follows instructions of supervisors and respond to requests from others in the team in a helpful manner,Contributing work and effort to group perfromance to meet agreed upon and achieve team work', 'Effectiveness,Use of time and volume of work accomplished,Ability to meet standards,Accuracy', 'Application of appropriate level of technical and procedural in specific field,Degree of technical competence,Understanding of job procedure methods facts and infomation related to assignments,Perform duties with minimal supervision but seek guidance where and when appropriate to the job consults the appropriate staff', 'Sets own constructive work practice and recommends and creates own procedures,Self-starter develop and implement new methods procedures solutions concepts designs and or applications of existing designs or procedures,Accepts additional challenges and resposnbilities and willingly assist others self-reliant,Completes assignement on time', 'Adapting to changes and using resources effectively,Maintains confidentiality as appropriate,Setting objectives establishing priorities developing plans,Arranging work schedules and priorizing work to meet deadlines,Know whento ask for clarification before proceeding on a work', 'Anticipating problems and faciitate problem resolution,Willingness to make necessary and immediate decision given incomplete information,Understanding practical and workable solutions,Recognizing when a decision is necessry asking for input making decisions and providing information and feedback in a timely manner', 'Starts work at appropriate time,Follows policies for requesting and reporting time off,Completion assignments on time and carries out,Meeting deadlines and Supervior\'s requirement', 'Inspires subordinates to give their best,Gets the work done,Monitoring and follow up of subrodinates,Integrity and impartiality', 'Judgement consistency sound and well thought out.,Takes a reasonable viiew on most matters,His judgement tends not to be erratic.,Makes sound and reliable judgement', 'Cooperative,Considerate,Gets on well in dealing with supervisors subordinates and peers', 'Able to listen and understand information,Present information in clear and concise manner,Knows appropriate ways of communicating with immediate supervisors and management,Demonstrate respect for all individuals regardless of their background in all forms of communication', 'appraisal', 'yes', 'yes', 'yes', 'yes', 'yes', 'yes', 'yes', 'yes', 'yes', 'sidebar_layout_hrsale', '', 'fixed-sidebar', 'boxed_layout_hrsale', '', 'skin-purple', 'fadeInDown', 'tada', 'tada', 'TUSHIRIKIANE AFRICA TRUST (TUSA) MICROCREDIT', '', '', 'english', 'yes', 'Africa/Nairobi', '::1', '', 'none', '', '', 'yes', 'yes', '', 'true', '', 'true', '', '', 'true', '', 'true', '', '', 'true', '', 'yes', 'username', 'vsoft-facilitator@gmail.com', 'yes', 'yes', 'sk_test_2XEyr1hQFGByITfQjSwFqNtm', 'pk_test_zVFISCqeQPnniD0ywHBHikMd', 'yes', 1, 0, 0, '', '', 0, 0, 0, NULL, NULL, '', '1.0.3', '2018-03-28', '0,assets,settings', '2018-03-28 04:27:32', 0, NULL, NULL);

-- Dumping structure for table trustaxisdb.xin_theme_settings
CREATE TABLE IF NOT EXISTS `xin_theme_settings` (
  `theme_settings_id` int(11) NOT NULL,
  `fixed_layout` varchar(200) NOT NULL,
  `fixed_footer` varchar(200) NOT NULL,
  `boxed_layout` varchar(200) NOT NULL,
  `page_header` varchar(200) NOT NULL,
  `footer_layout` varchar(200) NOT NULL,
  `statistics_cards` varchar(200) NOT NULL,
  `animation_style` varchar(100) NOT NULL,
  `theme_option` varchar(100) NOT NULL,
  `dashboard_option` varchar(100) NOT NULL,
  `dashboard_calendar` varchar(100) NOT NULL,
  `login_page_options` varchar(100) NOT NULL,
  `sub_menu_icons` varchar(100) NOT NULL,
  `statistics_cards_background` varchar(200) NOT NULL,
  `employee_cards` varchar(200) NOT NULL,
  `card_border_color` varchar(200) NOT NULL,
  `compact_menu` varchar(200) NOT NULL,
  `flipped_menu` varchar(200) NOT NULL,
  `right_side_icons` varchar(200) NOT NULL,
  `bordered_menu` varchar(200) NOT NULL,
  `form_design` varchar(200) NOT NULL,
  `is_semi_dark` int(11) NOT NULL,
  `semi_dark_color` varchar(200) NOT NULL,
  `top_nav_dark_color` varchar(200) NOT NULL,
  `menu_color_option` varchar(200) NOT NULL,
  `export_orgchart` varchar(100) NOT NULL,
  `export_file_title` mediumtext NOT NULL,
  `org_chart_layout` varchar(200) NOT NULL,
  `org_chart_zoom` varchar(100) NOT NULL,
  `org_chart_pan` varchar(100) NOT NULL,
  `login_page_text` text NOT NULL,
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0,
  `deleted_at` datetime DEFAULT NULL,
  `deleted_by` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- Dumping data for table trustaxisdb.xin_theme_settings: ~1 rows (approximately)
INSERT INTO `xin_theme_settings` (`theme_settings_id`, `fixed_layout`, `fixed_footer`, `boxed_layout`, `page_header`, `footer_layout`, `statistics_cards`, `animation_style`, `theme_option`, `dashboard_option`, `dashboard_calendar`, `login_page_options`, `sub_menu_icons`, `statistics_cards_background`, `employee_cards`, `card_border_color`, `compact_menu`, `flipped_menu`, `right_side_icons`, `bordered_menu`, `form_design`, `is_semi_dark`, `semi_dark_color`, `top_nav_dark_color`, `menu_color_option`, `export_orgchart`, `export_file_title`, `org_chart_layout`, `org_chart_zoom`, `org_chart_pan`, `login_page_text`, `is_deleted`, `deleted_at`, `deleted_by`) VALUES
	(1, 'false', 'true', 'false', 'breadcrumb-transparent', 'footer-light', '4', 'rollIn', 'template_2', 'dashboard_2', '', 'login_page_1', 'fa-check', '', '', '', 'true', 'false', 'false', 'false', 'rounded_form', 1, 'bg-primary', 'bg-blue-grey', 'menu-dark', 'true', 'HRMS', 't2b', 'true', 'true', 'No more hassle, no more delays. Stay connected, informed, and in control of your HR processes anytime, anywhere. Log in now to streamline your workday and experience efficiency at its best.', 0, NULL, NULL);

-- Dumping structure for trigger trustaxisdb.trg_audit_logs_no_delete
SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION';
DELIMITER //
CREATE TRIGGER trg_audit_logs_no_delete
BEFORE DELETE ON audit_logs
FOR EACH ROW
BEGIN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'audit_logs is append-only: DELETE is not permitted';
END//
DELIMITER ;
SET SQL_MODE=@OLDTMP_SQL_MODE;

-- Dumping structure for trigger trustaxisdb.trg_audit_logs_no_update
SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION';
DELIMITER //
CREATE TRIGGER trg_audit_logs_no_update
BEFORE UPDATE ON audit_logs
FOR EACH ROW
BEGIN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'audit_logs is append-only: UPDATE is not permitted';
END//
DELIMITER ;
SET SQL_MODE=@OLDTMP_SQL_MODE;

/*!40103 SET TIME_ZONE=IFNULL(@OLD_TIME_ZONE, 'system') */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;