-- phpMyAdmin SQL Dump -- version 5.2.1 -- https://www.phpmyadmin.net/ -- -- Host: 127.0.0.1 -- Generation Time: Aug 25, 2026 at 01:44 PM -- Server version: 10.4.32-MariaDB -- PHP Version: 8.0.30 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; START TRANSACTION; SET time_zone = "+00:00"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8mb4 */; -- -- Database: `accounting_management_system` -- DELIMITER $$ -- -- Functions -- CREATE DEFINER=`root`@`localhost` FUNCTION `fn_get_client_balance` (`p_client_id` INT) RETURNS DECIMAL(15,2) DETERMINISTIC BEGIN DECLARE v_balance DECIMAL(15,2); SELECT COALESCE(SUM(l.total_repayable - COALESCE( (SELECT SUM(amount) FROM loan_repayments WHERE loan_id = l.id AND status = 'completed'), 0 )), 0) INTO v_balance FROM loans l WHERE l.client_id = p_client_id AND l.status IN ('active', 'disbursed'); RETURN v_balance; END$$ CREATE DEFINER=`root`@`localhost` FUNCTION `fn_get_document_completion` (`p_client_id` INT) RETURNS DECIMAL(5,2) DETERMINISTIC BEGIN DECLARE v_total_required INT; DECLARE v_uploaded INT; DECLARE v_percentage DECIMAL(5,2); SELECT COUNT(*) INTO v_total_required FROM document_types WHERE is_mandatory = 1 AND status = 'active'; SELECT COUNT(DISTINCT cd.document_type_id) INTO v_uploaded FROM client_documents cd JOIN document_types dt ON cd.document_type_id = dt.id WHERE cd.client_id = p_client_id AND cd.is_active = 1 AND dt.is_mandatory = 1; IF v_total_required > 0 THEN SET v_percentage = (v_uploaded / v_total_required) * 100; ELSE SET v_percentage = 0; END IF; RETURN v_percentage; END$$ DELIMITER ; -- -------------------------------------------------------- -- -- Table structure for table `audit_trail` -- CREATE TABLE `audit_trail` ( `id` int(11) NOT NULL, `table_name` varchar(50) NOT NULL, `record_id` int(11) NOT NULL, `action` enum('INSERT','UPDATE','DELETE') NOT NULL, `user_id` int(11) DEFAULT NULL, `old_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`old_data`)), `new_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`new_data`)), `ip_address` varchar(45) DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -------------------------------------------------------- -- -- Table structure for table `branch` -- CREATE TABLE `branch` ( `id` int(11) NOT NULL, `name` varchar(100) NOT NULL, `address` text NOT NULL, `phone` varchar(20) DEFAULT NULL, `email` varchar(100) DEFAULT NULL, `manager` varchar(100) DEFAULT NULL, `is_active` tinyint(1) DEFAULT 1 COMMENT 'Soft delete: 1=active, 0=deleted', `created_by` int(11) DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `branch` -- INSERT INTO `branch` (`id`, `name`, `address`, `phone`, `email`, `manager`, `is_active`, `created_by`, `created_at`, `updated_at`) VALUES (1, 'Head Office', '123 Main Street, Harare', '+263 242 123456', 'headoffice@company.com', 'John Doe', 1, 1, '2026-08-25 00:54:49', '2026-08-25 00:54:49'), (2, 'Harare Branch', '45 Samora Machel Ave, Harare', '+263 242 789012', 'harare@company.com', 'Jane Smith', 1, 1, '2026-08-25 00:54:49', '2026-08-25 00:54:49'), (3, 'Bulawayo Branch', '78 Joshua Nkomo St, Bulawayo', '+263 292 345678', 'bulawayo@company.com', 'Peter Moyo', 1, 1, '2026-08-25 00:54:49', '2026-08-25 00:54:49'); -- -------------------------------------------------------- -- -- Table structure for table `deposits` -- CREATE TABLE `deposits` ( `id` int(11) NOT NULL, `deposit_type_id` int(11) NOT NULL, `branch_id` int(11) DEFAULT NULL, `full_name` varchar(100) NOT NULL COMMENT 'Full name of person/entity making deposit', `amount` decimal(15,2) NOT NULL, `deposit_date` date NOT NULL, `recorded_by` int(11) NOT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `deposits` -- INSERT INTO `deposits` (`id`, `deposit_type_id`, `branch_id`, `full_name`, `amount`, `deposit_date`, `recorded_by`, `created_at`, `updated_at`) VALUES (1, 1, 1, 'John Moyo', 2500.00, '2026-08-20', 1, '2026-08-25 06:07:07', '2026-08-25 06:07:07'), (2, 1, 2, 'Mary Ncube', 1800.00, '2026-08-20', 1, '2026-08-25 06:07:07', '2026-08-25 06:07:07'), (3, 2, 1, 'Interest Payment', 450.00, '2026-08-21', 1, '2026-08-25 06:07:07', '2026-08-25 06:07:07'), (4, 3, 3, 'Shareholder Capital', 5000.00, '2026-08-21', 1, '2026-08-25 06:07:07', '2026-08-25 06:07:07'), (5, 4, 2, 'Processing Fees', 150.00, '2026-08-22', 1, '2026-08-25 06:07:07', '2026-08-25 06:07:07'), (6, 1, 3, 'Peter Moyo', 3200.00, '2026-08-22', 1, '2026-08-25 06:07:07', '2026-08-25 06:07:07'), (7, 1, 1, 'John Moyo', 2500.00, '2026-08-24', 1, '2026-08-25 06:15:31', '2026-08-25 06:15:31'), (8, 1, 2, 'Mary Ncube', 1800.00, '2026-08-24', 1, '2026-08-25 06:15:31', '2026-08-25 06:15:31'), (9, 2, 1, 'Interest Payment', 450.00, '2026-08-24', 1, '2026-08-25 06:15:31', '2026-08-25 06:15:31'), (10, 3, 3, 'Shareholder Capital', 5000.00, '2026-08-24', 1, '2026-08-25 06:15:31', '2026-08-25 06:15:31'), (11, 4, 2, 'Processing Fees', 150.00, '2026-08-24', 1, '2026-08-25 06:15:31', '2026-08-25 06:15:31'), (12, 1, 3, 'Peter Moyo', 3200.00, '2026-08-24', 1, '2026-08-25 06:15:31', '2026-08-25 06:15:31'), (13, 1, 1, 'John Moyo', 2500.00, '2026-08-24', 1, '2026-08-25 06:15:51', '2026-08-25 06:15:51'), (14, 1, 2, 'Mary Ncube', 1800.00, '2026-08-24', 1, '2026-08-25 06:15:51', '2026-08-25 06:15:51'), (15, 2, 1, 'Interest Payment', 450.00, '2026-08-25', 1, '2026-08-25 06:15:51', '2026-08-25 06:27:15'), (16, 3, 3, 'Shareholder Capital', 5000.00, '2026-08-25', 1, '2026-08-25 06:15:51', '2026-08-25 06:27:07'), (17, 4, 2, 'Processing Fees', 150.00, '2026-08-25', 1, '2026-08-25 06:15:51', '2026-08-25 06:27:01'), (18, 1, 3, 'Peter Moyo', 3200.00, '2026-08-25', 1, '2026-08-25 06:15:51', '2026-08-25 06:26:53'), (19, 1, 1, 'John Moyo', 2500.00, '2026-08-01', 1, '2026-08-25 10:08:56', '2026-08-25 10:08:56'), (20, 2, 1, 'Interest Payment', 450.00, '2026-08-02', 1, '2026-08-25 10:08:56', '2026-08-25 10:08:56'), (21, 1, 2, 'Mary Ncube', 1800.00, '2026-08-03', 1, '2026-08-25 10:08:56', '2026-08-25 10:08:56'), (22, 3, 3, 'Shareholder Capital', 5000.00, '2026-08-04', 1, '2026-08-25 10:08:56', '2026-08-25 10:08:56'), (23, 4, 2, 'Processing Fees', 150.00, '2026-08-05', 1, '2026-08-25 10:08:56', '2026-08-25 10:08:56'), (24, 1, 3, 'Peter Moyo', 3200.00, '2026-08-06', 1, '2026-08-25 10:08:56', '2026-08-25 10:08:56'), (25, 1, 1, 'Tendai Chikwanha', 2100.00, '2026-08-07', 1, '2026-08-25 10:08:56', '2026-08-25 10:08:56'), (26, 2, 2, 'Interest Income', 380.00, '2026-08-08', 1, '2026-08-25 10:08:56', '2026-08-25 10:08:56'), (27, 3, 1, 'Capital Injection', 10000.00, '2026-08-09', 1, '2026-08-25 10:08:56', '2026-08-25 10:08:56'), (28, 1, 3, 'Sarah Ndlovu', 1500.00, '2026-08-10', 1, '2026-08-25 10:08:56', '2026-08-25 10:08:56'), (29, 5, 2, 'Investment Return', 750.00, '2026-08-11', 1, '2026-08-25 10:08:56', '2026-08-25 10:08:56'), (30, 1, 1, 'David Moyo', 3200.00, '2026-08-12', 1, '2026-08-25 10:08:56', '2026-08-25 10:08:56'), (31, 6, 3, 'Other Income', 200.00, '2026-08-13', 1, '2026-08-25 10:08:56', '2026-08-25 10:08:56'), (32, 4, 1, 'Service Fees', 95.00, '2026-08-14', 1, '2026-08-25 10:08:56', '2026-08-25 10:08:56'), (33, 1, 2, 'Loan Repayment', 4500.00, '2026-08-15', 1, '2026-08-25 10:08:56', '2026-08-25 10:08:56'), (34, 1, 1, 'John Moyo', 2500.00, '2026-08-01', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (35, 2, 1, 'Interest Payment', 450.00, '2026-08-02', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (36, 1, 2, 'Mary Ncube', 1800.00, '2026-08-03', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (37, 3, 3, 'Shareholder Capital', 5000.00, '2026-08-04', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (38, 4, 2, 'Processing Fees', 150.00, '2026-08-05', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (39, 1, 3, 'Peter Moyo', 3200.00, '2026-08-06', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (40, 1, 1, 'Tendai Chikwanha', 2100.00, '2026-08-07', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (41, 2, 2, 'Interest Income', 380.00, '2026-08-08', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (42, 3, 1, 'Capital Injection', 10000.00, '2026-08-09', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (43, 1, 3, 'Sarah Ndlovu', 1500.00, '2026-08-10', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (44, 5, 2, 'Investment Return', 750.00, '2026-08-11', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (45, 1, 1, 'David Moyo', 3200.00, '2026-08-12', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (46, 6, 3, 'Other Income', 200.00, '2026-08-13', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (47, 4, 1, 'Service Fees', 95.00, '2026-08-14', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (48, 1, 2, 'Loan Repayment', 4500.00, '2026-08-15', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (49, 1, 1, 'John Moyo', 2500.00, '2026-08-01', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (50, 2, 1, 'Interest Payment', 450.00, '2026-08-02', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (51, 1, 2, 'Mary Ncube', 1800.00, '2026-08-03', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (52, 3, 3, 'Shareholder Capital', 5000.00, '2026-08-04', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (53, 4, 2, 'Processing Fees', 150.00, '2026-08-05', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (54, 1, 3, 'Peter Moyo', 3200.00, '2026-08-06', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (55, 1, 1, 'Tendai Chikwanha', 2100.00, '2026-08-07', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (56, 2, 2, 'Interest Income', 380.00, '2026-08-08', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (57, 3, 1, 'Capital Injection', 10000.00, '2026-08-09', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (58, 1, 3, 'Sarah Ndlovu', 1500.00, '2026-08-10', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (59, 5, 2, 'Investment Return', 750.00, '2026-08-11', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (60, 1, 1, 'David Moyo', 3200.00, '2026-08-12', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (61, 6, 3, 'Other Income', 200.00, '2026-08-13', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (62, 4, 1, 'Service Fees', 95.00, '2026-08-14', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (63, 1, 2, 'Loan Repayment', 4500.00, '2026-08-15', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (64, 1, 1, 'John Moyo', 2500.00, '2026-08-01', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (65, 2, 1, 'Interest Payment', 450.00, '2026-08-02', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (66, 1, 2, 'Mary Ncube', 1800.00, '2026-08-03', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (67, 3, 3, 'Shareholder Capital', 5000.00, '2026-08-04', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (68, 4, 2, 'Processing Fees', 150.00, '2026-08-05', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (69, 1, 3, 'Peter Moyo', 3200.00, '2026-08-06', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (70, 1, 1, 'Tendai Chikwanha', 2100.00, '2026-08-07', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (71, 2, 2, 'Interest Income', 380.00, '2026-08-08', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (72, 3, 1, 'Capital Injection', 10000.00, '2026-08-09', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (73, 1, 3, 'Sarah Ndlovu', 1500.00, '2026-08-10', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (74, 5, 2, 'Investment Return', 750.00, '2026-08-11', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (75, 1, 1, 'David Moyo', 3200.00, '2026-08-12', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (76, 6, 3, 'Other Income', 200.00, '2026-08-13', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (77, 4, 1, 'Service Fees', 95.00, '2026-08-14', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (78, 1, 2, 'Loan Repayment', 4500.00, '2026-08-15', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'); -- -------------------------------------------------------- -- -- Table structure for table `deposit_types` -- CREATE TABLE `deposit_types` ( `id` int(11) NOT NULL, `name` varchar(100) NOT NULL, `description` text DEFAULT NULL, `is_active` tinyint(1) DEFAULT 1, `is_system` tinyint(1) DEFAULT 0 COMMENT 'System predefined types', `created_by` int(11) DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `deposit_types` -- INSERT INTO `deposit_types` (`id`, `name`, `description`, `is_active`, `is_system`, `created_by`, `created_at`, `updated_at`) VALUES (1, 'Client Loan Payment', 'Regular loan repayments from clients', 1, 1, 1, '2026-08-25 06:07:07', '2026-08-25 06:07:07'), (2, 'Interest Income', 'Interest earned from loans', 1, 1, 1, '2026-08-25 06:07:07', '2026-08-25 06:07:07'), (3, 'Capital Injection', 'Owner or shareholder capital injection', 1, 1, 1, '2026-08-25 06:07:07', '2026-08-25 06:07:07'), (4, 'Service Fees', 'Processing and service fees', 1, 0, 1, '2026-08-25 06:07:07', '2026-08-25 06:07:07'), (5, 'Investment Income', 'Income from investments', 1, 0, 1, '2026-08-25 06:07:07', '2026-08-25 06:07:07'), (6, 'Other Income', 'Miscellaneous income', 1, 0, 1, '2026-08-25 06:07:07', '2026-08-25 06:07:07'); -- -------------------------------------------------------- -- -- Table structure for table `expenses` -- CREATE TABLE `expenses` ( `id` int(11) NOT NULL, `expense_category_id` int(11) NOT NULL, `full_name` varchar(100) NOT NULL COMMENT 'Full name of person/entity expense is for', `purpose` text NOT NULL COMMENT 'Purpose of the expense', `amount` decimal(15,2) NOT NULL, `expense_date` date NOT NULL, `recorded_by` int(11) NOT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `expenses` -- INSERT INTO `expenses` (`id`, `expense_category_id`, `full_name`, `purpose`, `amount`, `expense_date`, `recorded_by`, `created_at`, `updated_at`) VALUES (1, 1, 'ABC Properties', 'Monthly office rent for August', 1200.00, '2026-08-01', 1, '2026-08-25 10:08:58', '2026-08-25 10:08:58'), (2, 2, 'ZESA', 'Electricity bill payment', 450.00, '2026-08-02', 1, '2026-08-25 10:08:58', '2026-08-25 10:08:58'), (3, 3, 'Staff Payroll', 'Salary payment for August', 8000.00, '2026-08-03', 1, '2026-08-25 10:08:58', '2026-08-25 10:08:58'), (4, 4, 'Marketing Agency', 'Social media advertising', 300.00, '2026-08-04', 1, '2026-08-25 10:08:58', '2026-08-25 10:08:58'), (5, 5, 'Office Express', 'Stationery and office supplies', 150.00, '2026-08-05', 1, '2026-08-25 10:08:58', '2026-08-25 10:08:58'), (6, 6, 'Total Fuel', 'Fuel for company vehicles', 200.00, '2026-08-06', 1, '2026-08-25 10:08:58', '2026-08-25 10:08:58'), (7, 7, 'Legal Firm', 'Legal consultation fees', 500.00, '2026-08-07', 1, '2026-08-25 10:08:58', '2026-08-25 10:08:58'), (8, 8, 'Microsoft', 'Software license subscriptions', 350.00, '2026-08-08', 1, '2026-08-25 10:08:58', '2026-08-25 10:08:58'), (9, 1, 'City Properties', 'Office rent payment', 1200.00, '2026-08-09', 1, '2026-08-25 10:08:58', '2026-08-25 10:08:58'), (10, 2, 'TelOne', 'Internet and phone bills', 180.00, '2026-08-10', 1, '2026-08-25 10:08:58', '2026-08-25 10:08:58'), (11, 3, 'Staff Bonuses', 'Performance bonuses', 1500.00, '2026-08-11', 1, '2026-08-25 10:08:58', '2026-08-25 10:08:58'), (12, 4, 'Google Ads', 'Online advertising campaign', 250.00, '2026-08-12', 1, '2026-08-25 10:08:58', '2026-08-25 10:08:58'), (13, 6, 'Fuel Station', 'Vehicle fuel', 180.00, '2026-08-13', 1, '2026-08-25 10:08:58', '2026-08-25 10:08:58'), (14, 5, 'Office Depot', 'Printer ink and paper', 95.00, '2026-08-14', 1, '2026-08-25 10:08:58', '2026-08-25 10:08:58'), (15, 8, 'Adobe', 'Creative cloud subscription', 120.00, '2026-08-15', 1, '2026-08-25 10:08:58', '2026-08-25 10:08:58'), (16, 1, 'ABC Properties', 'Monthly office rent for August', 1200.00, '2026-08-01', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (17, 2, 'ZESA', 'Electricity bill payment', 450.00, '2026-08-02', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (18, 3, 'Staff Payroll', 'Salary payment for August', 8000.00, '2026-08-03', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (19, 4, 'Marketing Agency', 'Social media advertising', 300.00, '2026-08-04', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (20, 5, 'Office Express', 'Stationery and office supplies', 150.00, '2026-08-05', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (21, 6, 'Total Fuel', 'Fuel for company vehicles', 200.00, '2026-08-06', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (22, 7, 'Legal Firm', 'Legal consultation fees', 500.00, '2026-08-07', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (23, 8, 'Microsoft', 'Software license subscriptions', 350.00, '2026-08-08', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (24, 1, 'City Properties', 'Office rent payment', 1200.00, '2026-08-09', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (25, 2, 'TelOne', 'Internet and phone bills', 180.00, '2026-08-10', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (26, 3, 'Staff Bonuses', 'Performance bonuses', 1500.00, '2026-08-11', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (27, 4, 'Google Ads', 'Online advertising campaign', 250.00, '2026-08-12', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (28, 6, 'Fuel Station', 'Vehicle fuel', 180.00, '2026-08-13', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (29, 5, 'Office Depot', 'Printer ink and paper', 95.00, '2026-08-14', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (30, 8, 'Adobe', 'Creative cloud subscription', 120.00, '2026-08-15', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (31, 1, 'ABC Properties', 'Monthly office rent for August', 1200.00, '2026-08-01', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (32, 2, 'ZESA', 'Electricity bill payment', 450.00, '2026-08-02', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (33, 3, 'Staff Payroll', 'Salary payment for August', 8000.00, '2026-08-03', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (34, 4, 'Marketing Agency', 'Social media advertising', 300.00, '2026-08-04', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (35, 5, 'Office Express', 'Stationery and office supplies', 150.00, '2026-08-05', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (36, 6, 'Total Fuel', 'Fuel for company vehicles', 200.00, '2026-08-06', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (37, 7, 'Legal Firm', 'Legal consultation fees', 500.00, '2026-08-07', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (38, 8, 'Microsoft', 'Software license subscriptions', 350.00, '2026-08-08', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (39, 1, 'City Properties', 'Office rent payment', 1200.00, '2026-08-09', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (40, 2, 'TelOne', 'Internet and phone bills', 180.00, '2026-08-10', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (41, 3, 'Staff Bonuses', 'Performance bonuses', 1500.00, '2026-08-11', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (42, 4, 'Google Ads', 'Online advertising campaign', 250.00, '2026-08-12', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (43, 6, 'Fuel Station', 'Vehicle fuel', 180.00, '2026-08-13', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (44, 5, 'Office Depot', 'Printer ink and paper', 95.00, '2026-08-14', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (45, 8, 'Adobe', 'Creative cloud subscription', 120.00, '2026-08-15', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (46, 1, 'ABC Properties', 'Monthly office rent for August', 1200.00, '2026-08-01', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (47, 2, 'ZESA', 'Electricity bill payment', 450.00, '2026-08-02', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (48, 3, 'Staff Payroll', 'Salary payment for August', 8000.00, '2026-08-03', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (49, 4, 'Marketing Agency', 'Social media advertising', 300.00, '2026-08-04', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (50, 5, 'Office Express', 'Stationery and office supplies', 150.00, '2026-08-05', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (51, 6, 'Total Fuel', 'Fuel for company vehicles', 200.00, '2026-08-06', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (52, 7, 'Legal Firm', 'Legal consultation fees', 500.00, '2026-08-07', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (53, 8, 'Microsoft', 'Software license subscriptions', 350.00, '2026-08-08', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (54, 1, 'City Properties', 'Office rent payment', 1200.00, '2026-08-09', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (55, 2, 'TelOne', 'Internet and phone bills', 180.00, '2026-08-10', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (56, 3, 'Staff Bonuses', 'Performance bonuses', 1500.00, '2026-08-11', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (57, 4, 'Google Ads', 'Online advertising campaign', 250.00, '2026-08-12', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (58, 6, 'Fuel Station', 'Vehicle fuel', 180.00, '2026-08-13', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (59, 5, 'Office Depot', 'Printer ink and paper', 95.00, '2026-08-14', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (60, 8, 'Adobe', 'Creative cloud subscription', 120.00, '2026-08-15', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'); -- -------------------------------------------------------- -- -- Table structure for table `expense_categories` -- CREATE TABLE `expense_categories` ( `id` int(11) NOT NULL, `name` varchar(100) NOT NULL, `description` text DEFAULT NULL, `parent_category_id` int(11) DEFAULT NULL, `is_active` tinyint(1) DEFAULT 1, `is_system` tinyint(1) DEFAULT 0 COMMENT 'System predefined categories', `created_by` int(11) DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `expense_categories` -- INSERT INTO `expense_categories` (`id`, `name`, `description`, `parent_category_id`, `is_active`, `is_system`, `created_by`, `created_at`, `updated_at`) VALUES (1, 'FUEL', 'FUEL', NULL, 0, 0, 1, '2026-08-25 00:01:37', '2026-08-25 00:05:10'), (2, 'Office Rent', 'Monthly office rental expenses', NULL, 1, 0, 1, '2026-08-25 10:08:56', '2026-08-25 10:08:56'), (3, 'Utilities', 'Electricity, water, internet, and other utilities', NULL, 1, 0, 1, '2026-08-25 10:08:56', '2026-08-25 10:08:56'), (4, 'Salaries', 'Staff salaries and wages', NULL, 1, 0, 1, '2026-08-25 10:08:56', '2026-08-25 10:08:56'), (5, 'Marketing', 'Marketing and advertising expenses', NULL, 1, 0, 1, '2026-08-25 10:08:56', '2026-08-25 10:08:56'), (6, 'Stationery', 'Office stationery and supplies', NULL, 1, 0, 1, '2026-08-25 10:08:56', '2026-08-25 10:08:56'), (7, 'Transport', 'Transportation and fuel costs', NULL, 1, 0, 1, '2026-08-25 10:08:56', '2026-08-25 10:08:56'), (8, 'Legal Fees', 'Legal and professional fees', NULL, 1, 0, 1, '2026-08-25 10:08:56', '2026-08-25 10:08:56'), (9, 'Software', 'Software licenses and subscriptions', NULL, 1, 0, 1, '2026-08-25 10:08:56', '2026-08-25 10:08:56'); -- -------------------------------------------------------- -- -- Table structure for table `products` -- CREATE TABLE `products` ( `id` int(11) NOT NULL, `product_name` varchar(200) NOT NULL COMMENT 'Name of the product', `product_code` varchar(50) DEFAULT NULL COMMENT 'Unique product code/SKU', `description` text DEFAULT NULL COMMENT 'Product description', `category` varchar(100) DEFAULT NULL COMMENT 'Product category', `unit_of_measure` varchar(50) DEFAULT 'pcs' COMMENT 'Unit of measure (pcs, kg, etc.)', `quantity` decimal(15,2) DEFAULT 0.00 COMMENT 'Current stock quantity', `selling_price` decimal(15,2) DEFAULT 0.00 COMMENT 'Selling price per unit', `cost_price` decimal(15,2) DEFAULT 0.00 COMMENT 'Cost price per unit', `min_stock_level` decimal(15,2) DEFAULT 0.00 COMMENT 'Minimum stock alert level', `is_active` tinyint(1) DEFAULT 1 COMMENT 'Soft delete: 1=active, 0=deleted', `deleted_at` datetime DEFAULT NULL COMMENT 'Soft delete timestamp', `deleted_by` int(11) DEFAULT NULL COMMENT 'User who deleted the record', `created_by` int(11) NOT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `products` -- INSERT INTO `products` (`id`, `product_name`, `product_code`, `description`, `category`, `unit_of_measure`, `quantity`, `selling_price`, `cost_price`, `min_stock_level`, `is_active`, `deleted_at`, `deleted_by`, `created_by`, `created_at`, `updated_at`) VALUES (1, 'Laptop Dell XPS 13', 'PRD-001', 'Dell XPS 13 Laptop, 16GB RAM, 512GB SSD', 'Electronics', 'pcs', 15.00, 1200.00, 950.00, 5.00, 1, NULL, NULL, 1, '2026-08-25 07:12:44', '2026-08-25 07:12:44'), (2, 'Smartphone Samsung Galaxy S23', 'PRD-002', 'Samsung Galaxy S23 5G, 256GB', 'Electronics', 'pcs', 25.00, 850.00, 650.00, 10.00, 1, NULL, NULL, 1, '2026-08-25 07:12:44', '2026-08-25 07:12:44'), (3, 'Office Chair Ergonomic', 'PRD-003', 'Ergonomic office chair with lumbar support', 'Furniture', 'pcs', 8.00, 250.00, 180.00, 3.00, 1, NULL, NULL, 1, '2026-08-25 07:12:44', '2026-08-25 07:12:44'), (4, 'Printer HP LaserJet', 'PRD-004', 'HP LaserJet Pro MFP', 'Office Equipment', 'pcs', 5.00, 400.00, 320.00, 2.00, 1, NULL, NULL, 1, '2026-08-25 07:12:44', '2026-08-25 07:12:44'), (5, 'Desk Executive', 'PRD-005', 'Executive desk 180cm x 90cm', 'Furniture', 'pcs', 6.00, 500.00, 380.00, 2.00, 1, NULL, NULL, 1, '2026-08-25 07:12:44', '2026-08-25 07:12:44'), (6, 'Monitor 27\" LED', 'PRD-006', '27 Inch LED Monitor, 4K', 'Electronics', 'pcs', 12.00, 350.00, 250.00, 4.00, 1, NULL, NULL, 1, '2026-08-25 07:12:44', '2026-08-25 07:12:44'); -- -------------------------------------------------------- -- -- Table structure for table `purchases` -- CREATE TABLE `purchases` ( `id` int(11) NOT NULL, `purchase_type_id` int(11) NOT NULL COMMENT 'Reference to purchase_types table', `product_id` int(11) NOT NULL COMMENT 'Reference to products table', `purchaser_name` varchar(100) NOT NULL COMMENT 'Name of the purchaser', `purpose` text NOT NULL COMMENT 'Purpose of the purchase', `quantity` decimal(15,2) NOT NULL COMMENT 'Quantity purchased', `cost_price` decimal(15,2) NOT NULL COMMENT 'Cost price per unit', `selling_price` decimal(15,2) NOT NULL COMMENT 'Selling price per unit (pulled from product)', `total_cost` decimal(15,2) DEFAULT 0.00 COMMENT 'Quantity × Cost Price', `price_per_unit` decimal(15,2) DEFAULT 0.00 COMMENT 'Total Cost / Quantity', `profit` decimal(15,2) DEFAULT 0.00 COMMENT '(Selling Price - Cost Price) × Quantity', `purchase_date` date NOT NULL COMMENT 'Purchase date', `is_active` tinyint(1) DEFAULT 1 COMMENT 'Soft delete: 1=active, 0=deleted', `deleted_at` datetime DEFAULT NULL COMMENT 'Soft delete timestamp', `deleted_by` int(11) DEFAULT NULL COMMENT 'User who deleted the record', `created_by` int(11) NOT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `purchases` -- INSERT INTO `purchases` (`id`, `purchase_type_id`, `product_id`, `purchaser_name`, `purpose`, `quantity`, `cost_price`, `selling_price`, `total_cost`, `price_per_unit`, `profit`, `purchase_date`, `is_active`, `deleted_at`, `deleted_by`, `created_by`, `created_at`, `updated_at`) VALUES (1, 1, 1, 'John Doe', 'Bulk laptop purchase for staff', 10.00, 950.00, 1200.00, 9500.00, 950.00, 2500.00, '2026-08-25', 1, NULL, NULL, 1, '2026-08-25 07:12:44', '2026-08-25 07:12:44'), (2, 1, 2, 'John Doe', 'Smartphone inventory restock', 15.00, 650.00, 850.00, 9750.00, 650.00, 3000.00, '2026-08-25', 1, NULL, NULL, 1, '2026-08-25 07:12:44', '2026-08-25 07:12:44'), (3, 2, 3, 'Jane Smith', 'New office chairs', 8.00, 180.00, 250.00, 1440.00, 180.00, 560.00, '2026-08-25', 1, NULL, NULL, 1, '2026-08-25 07:12:44', '2026-08-25 07:12:44'), (4, 2, 5, 'Jane Smith', 'Executive desks for management', 6.00, 380.00, 500.00, 2280.00, 380.00, 720.00, '2026-08-25', 1, NULL, NULL, 1, '2026-08-25 07:12:44', '2026-08-25 07:12:44'), (5, 3, 4, 'Peter Moyo', 'Printer for accounts department', 3.00, 320.00, 400.00, 960.00, 320.00, 240.00, '2026-08-25', 1, NULL, NULL, 1, '2026-08-25 07:12:44', '2026-08-25 07:12:44'); -- -------------------------------------------------------- -- -- Table structure for table `purchase_types` -- CREATE TABLE `purchase_types` ( `id` int(11) NOT NULL, `name` varchar(100) NOT NULL COMMENT 'Purchase type name', `description` text DEFAULT NULL COMMENT 'Purchase type description', `purchase_date` date NOT NULL COMMENT 'Purchase date', `is_active` tinyint(1) DEFAULT 1 COMMENT 'Soft delete: 1=active, 0=deleted', `deleted_at` datetime DEFAULT NULL COMMENT 'Soft delete timestamp', `deleted_by` int(11) DEFAULT NULL COMMENT 'User who deleted the record', `created_by` int(11) NOT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `purchase_types` -- INSERT INTO `purchase_types` (`id`, `name`, `description`, `purchase_date`, `is_active`, `deleted_at`, `deleted_by`, `created_by`, `created_at`, `updated_at`) VALUES (1, 'Electronics Supply', 'Electronics and gadgets supply', '2026-08-25', 1, NULL, NULL, 1, '2026-08-25 07:12:44', '2026-08-25 07:12:44'), (2, 'Office Furniture', 'Office furniture and equipment', '2026-08-25', 1, NULL, NULL, 1, '2026-08-25 07:12:44', '2026-08-25 07:12:44'), (3, 'Office Supplies', 'General office supplies', '2026-08-25', 1, NULL, NULL, 1, '2026-08-25 07:12:44', '2026-08-25 07:12:44'); -- -------------------------------------------------------- -- -- Table structure for table `sales` -- CREATE TABLE `sales` ( `id` int(11) NOT NULL, `sales_date` date NOT NULL, `branch_id` int(11) NOT NULL COMMENT 'Branch this sales record belongs to', `daily_sales` decimal(15,2) DEFAULT 0.00 COMMENT 'Total daily sales made', `daily_credits` decimal(15,2) DEFAULT 0.00 COMMENT 'Total daily credits made', `tokens` decimal(15,2) DEFAULT 0.00 COMMENT 'Snooker tokens sold (deducted from total sales)', `daily_expected_sales` decimal(15,2) DEFAULT 0.00 COMMENT 'Daily Sales - Daily Credits', `daily_collected` decimal(15,2) DEFAULT 0.00 COMMENT 'Total collected for the day', `daily_shortfall` decimal(15,2) DEFAULT 0.00 COMMENT 'Daily Collected - Daily Expected (Surplus/Shortfall)', `opening_balance` decimal(15,2) DEFAULT 0.00 COMMENT 'Opening balance from previous day', `closing_balance` decimal(15,2) DEFAULT 0.00 COMMENT 'Closing balance for the day', `total_deposits` decimal(15,2) DEFAULT 0.00 COMMENT 'Total deposits from deposits table for this date and branch', `total_withdrawals` decimal(15,2) DEFAULT 0.00 COMMENT 'Total withdrawals from withdrawals table for this date and branch', `status` enum('draft','submitted','approved','closed') DEFAULT 'draft', `submitted_by` int(11) DEFAULT NULL, `submitted_at` datetime DEFAULT NULL, `approved_by` int(11) DEFAULT NULL, `approved_at` datetime DEFAULT NULL, `created_by` int(11) NOT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), `branch_target` double NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `sales` -- INSERT INTO `sales` (`id`, `sales_date`, `branch_id`, `daily_sales`, `daily_credits`, `tokens`, `daily_expected_sales`, `daily_collected`, `daily_shortfall`, `opening_balance`, `closing_balance`, `total_deposits`, `total_withdrawals`, `status`, `submitted_by`, `submitted_at`, `approved_by`, `approved_at`, `created_by`, `created_at`, `updated_at`, `branch_target`) VALUES (1, '2026-08-25', 1, 20000.00, 0.00, 0.00, 20000.00, 15950.00, -4050.00, 0.00, 15950.00, 450.00, 4500.00, 'closed', NULL, NULL, NULL, NULL, 1, '2026-08-25 06:34:47', '2026-08-25 06:34:47', 21000), (2, '2026-08-25', 2, 15000.00, 1200.00, 0.00, 13800.00, 13450.00, -350.00, 0.00, 13450.00, 150.00, 500.00, 'closed', NULL, NULL, NULL, NULL, 1, '2026-08-25 06:35:58', '2026-08-25 06:56:59', 15000), (3, '2026-08-23', 2, 10000.00, 1000.00, 100.02, 8899.98, 8899.98, 0.00, 0.00, 8899.98, 0.00, 0.00, 'draft', NULL, NULL, NULL, NULL, 1, '2026-08-25 09:13:11', '2026-08-25 09:13:11', 15000), (4, '2026-08-01', 1, 25000.00, 2000.00, 150.00, 22850.00, 18500.00, -4350.00, 0.00, 18500.00, 2500.00, 10000.00, 'approved', NULL, NULL, NULL, NULL, 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11', 21000), (5, '2026-08-01', 2, 15000.00, 1000.00, 80.00, 13920.00, 12000.00, -1920.00, 0.00, 12000.00, 1800.00, 3500.00, 'approved', NULL, NULL, NULL, NULL, 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11', 15000), (6, '2026-08-02', 1, 22000.00, 1500.00, 120.00, 20380.00, 18500.00, -1880.00, 18500.00, 37000.00, 450.00, 3500.00, 'approved', NULL, NULL, NULL, NULL, 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11', 21000), (7, '2026-08-02', 3, 20000.00, 1200.00, 100.00, 18700.00, 16500.00, -2200.00, 0.00, 16500.00, 5000.00, 2000.00, 'approved', NULL, NULL, NULL, NULL, 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11', 18000), (8, '2026-08-03', 2, 18000.00, 800.00, 60.00, 17140.00, 14500.00, -2640.00, 12000.00, 26500.00, 150.00, 4500.00, 'approved', NULL, NULL, NULL, NULL, 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11', 15000), (9, '2026-08-04', 1, 28000.00, 2500.00, 200.00, 25300.00, 22000.00, -3300.00, 37000.00, 59000.00, 5000.00, 8000.00, 'approved', NULL, NULL, NULL, NULL, 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11', 21000), (10, '2026-08-05', 3, 22000.00, 1500.00, 120.00, 20380.00, 19000.00, -1380.00, 16500.00, 35500.00, 3200.00, 500.00, 'approved', NULL, NULL, NULL, NULL, 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11', 18000), (11, '2026-08-06', 2, 16000.00, 1000.00, 80.00, 14920.00, 13500.00, -1420.00, 26500.00, 40000.00, 380.00, 5000.00, 'submitted', NULL, NULL, NULL, NULL, 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11', 15000), (12, '2026-08-07', 1, 30000.00, 3000.00, 250.00, 26750.00, 24000.00, -2750.00, 59000.00, 83000.00, 2100.00, 1200.00, 'approved', NULL, NULL, NULL, NULL, 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11', 21000), (13, '2026-08-08', 3, 25000.00, 2000.00, 150.00, 22850.00, 21000.00, -1850.00, 35500.00, 56500.00, 1500.00, 300.00, 'approved', NULL, NULL, NULL, NULL, 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11', 18000); -- -------------------------------------------------------- -- -- Table structure for table `settings` -- CREATE TABLE `settings` ( `id` int(11) NOT NULL, `setting_key` varchar(100) NOT NULL, `setting_value` text DEFAULT NULL, `setting_group` varchar(50) DEFAULT NULL, `description` text DEFAULT NULL, `is_encrypted` tinyint(1) DEFAULT 0, `updated_by` int(11) DEFAULT NULL, `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `settings` -- INSERT INTO `settings` (`id`, `setting_key`, `setting_value`, `setting_group`, `description`, `is_encrypted`, `updated_by`, `updated_at`) VALUES (16, 'company_name', 'ABC Loan Management', 'company', NULL, 0, NULL, '2026-06-26 10:15:19'), (17, 'company_address', 'Harare, Zimbabwe', 'company', NULL, 0, NULL, '2026-06-26 10:15:19'), (18, 'company_phone', '+26370000000', 'company', NULL, 0, NULL, '2026-06-26 10:15:19'), (19, 'company_email', 'info@nissaloans.co.zw', 'company', NULL, 0, NULL, '2026-06-26 10:15:19'), (20, 'currency', 'USD', 'system', NULL, 0, NULL, '2026-06-26 10:15:24'), (21, 'date_format', 'Y-m-d', 'system', NULL, 0, NULL, '2026-06-26 10:15:19'), (22, 'timezone', 'Africa/Harare', 'system', NULL, 0, NULL, '2026-06-26 10:15:19'), (23, 'max_loan_amount', '5000000', 'loan', NULL, 0, NULL, '2026-06-26 10:15:19'), (24, 'min_loan_amount', '1000', 'loan', NULL, 0, NULL, '2026-06-26 10:15:19'), (25, 'default_interest_rate', '12', 'loan', NULL, 0, NULL, '2026-06-26 10:15:24'), (26, 'late_penalty_rate', '5', 'loan', NULL, 0, NULL, '2026-06-26 10:15:24'), (27, 'processing_fee_rate', '1', 'loan', NULL, 0, NULL, '2026-06-26 10:15:24'), (28, 'max_document_size', '5242880', 'documents', NULL, 0, NULL, '2026-06-26 10:15:19'), (29, 'allowed_document_types', 'jpg,jpeg,png,pdf', 'documents', NULL, 0, NULL, '2026-06-26 10:15:19'), (30, 'document_retention_days', '2555', 'documents', NULL, 0, NULL, '2026-06-26 10:15:19'); -- -------------------------------------------------------- -- -- Table structure for table `staff` -- CREATE TABLE `staff` ( `id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `staff_number` varchar(50) NOT NULL, `department` varchar(100) DEFAULT NULL, `position` varchar(100) DEFAULT NULL, `hire_date` date DEFAULT NULL, `supervisor_id` int(11) DEFAULT NULL, `salary` decimal(15,2) DEFAULT NULL, `emergency_contact` varchar(100) DEFAULT NULL, `emergency_phone` varchar(20) DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `staff` -- INSERT INTO `staff` (`id`, `user_id`, `staff_number`, `department`, `position`, `hire_date`, `supervisor_id`, `salary`, `emergency_contact`, `emergency_phone`, `created_at`) VALUES (1, 1, 'STF-001', 'Administration', 'System Administrator', '2026-06-26', NULL, NULL, NULL, NULL, '2026-06-26 07:01:51'); -- -------------------------------------------------------- -- -- Table structure for table `system_logs` -- CREATE TABLE `system_logs` ( `id` int(11) NOT NULL, `user_id` int(11) DEFAULT NULL, `action` varchar(100) NOT NULL, `module` varchar(50) NOT NULL, `entity_type` varchar(50) DEFAULT NULL, `entity_id` int(11) DEFAULT NULL, `old_values` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`old_values`)), `new_values` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`new_values`)), `ip_address` varchar(45) DEFAULT NULL, `user_agent` text DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -------------------------------------------------------- -- -- Table structure for table `targets` -- CREATE TABLE `targets` ( `id` int(11) NOT NULL, `branch_id` int(11) NOT NULL COMMENT 'Branch this target belongs to', `target_cost_id` int(11) NOT NULL COMMENT 'Reference to target_cost table', `target_date` date NOT NULL COMMENT 'Date of the target', `target_amount` decimal(15,2) DEFAULT 0.00 COMMENT 'Pulled from target_cost table', `sales_amount` decimal(15,2) DEFAULT 0.00 COMMENT 'Shared sales amount for all cashiers', `status` enum('draft','submitted','approved','closed') DEFAULT 'draft', `is_active` tinyint(1) DEFAULT 1 COMMENT 'Soft delete: 1=active, 0=deleted', `deleted_at` datetime DEFAULT NULL COMMENT 'Soft delete timestamp', `deleted_by` int(11) DEFAULT NULL COMMENT 'User who deleted the record', `created_by` int(11) NOT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `targets` -- INSERT INTO `targets` (`id`, `branch_id`, `target_cost_id`, `target_date`, `target_amount`, `sales_amount`, `status`, `is_active`, `deleted_at`, `deleted_by`, `created_by`, `created_at`, `updated_at`) VALUES (1, 1, 2, '2026-08-01', 21000.00, 18500.00, 'approved', 1, NULL, NULL, 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (2, 2, 3, '2026-08-01', 15000.00, 12000.00, 'approved', 1, NULL, NULL, 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (3, 3, 4, '2026-08-02', 18000.00, 16500.00, 'approved', 1, NULL, NULL, 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (4, 1, 2, '2026-08-03', 21000.00, 22000.00, 'approved', 1, NULL, NULL, 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (5, 2, 3, '2026-08-04', 15000.00, 14500.00, 'submitted', 1, NULL, NULL, 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (6, 3, 4, '2026-08-05', 18000.00, 17000.00, 'approved', 1, NULL, NULL, 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (7, 1, 2, '2026-08-06', 21000.00, 19500.00, 'approved', 1, NULL, NULL, 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (8, 2, 3, '2026-08-07', 15000.00, 15500.00, 'draft', 1, NULL, NULL, 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (9, 3, 4, '2026-08-08', 18000.00, 19000.00, 'approved', 1, NULL, NULL, 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (10, 1, 2, '2026-08-09', 21000.00, 20500.00, 'approved', 1, NULL, NULL, 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'); -- -------------------------------------------------------- -- -- Table structure for table `target_cashiers` -- CREATE TABLE `target_cashiers` ( `id` int(11) NOT NULL, `target_id` int(11) NOT NULL COMMENT 'Reference to targets table', `cashier_name` varchar(100) NOT NULL COMMENT 'Name of the cashier', `cashier_user_id` int(11) DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `target_cashiers` -- INSERT INTO `target_cashiers` (`id`, `target_id`, `cashier_name`, `cashier_user_id`, `created_at`) VALUES (1, 1, 'John Doe', 1, '2026-08-25 10:11:11'), (2, 1, 'Jane Smith', 1, '2026-08-25 10:11:11'), (3, 1, 'Peter Moyo', 1, '2026-08-25 10:11:11'), (4, 2, 'Mary Ncube', 1, '2026-08-25 10:11:11'), (5, 2, 'Tendai Chikwanha', 1, '2026-08-25 10:11:11'), (6, 3, 'Sarah Ndlovu', 1, '2026-08-25 10:11:11'), (7, 3, 'David Moyo', 1, '2026-08-25 10:11:11'), (8, 4, 'John Doe', 1, '2026-08-25 10:11:11'), (9, 4, 'Jane Smith', 1, '2026-08-25 10:11:11'), (10, 5, 'Peter Moyo', 1, '2026-08-25 10:11:11'); -- -------------------------------------------------------- -- -- Table structure for table `target_cost` -- CREATE TABLE `target_cost` ( `id` int(11) NOT NULL, `target_amount` decimal(15,2) NOT NULL COMMENT 'Target amount to be achieved', `branch_name` varchar(100) NOT NULL COMMENT 'Branch name', `amount` decimal(15,2) NOT NULL COMMENT 'Current amount/cost', `is_active` tinyint(1) DEFAULT 1 COMMENT 'Active status', `created_by` int(11) DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `target_cost` -- INSERT INTO `target_cost` (`id`, `target_amount`, `branch_name`, `amount`, `is_active`, `created_by`, `created_at`, `updated_at`) VALUES (1, 21000.00, 'Bulawayo Branch', 0.00, 1, 1, '2026-08-25 04:51:08', '2026-08-25 04:51:08'), (2, 21000.00, 'Head Office', 0.00, 1, 1, '2026-08-25 06:15:51', '2026-08-25 06:15:51'), (3, 15000.00, 'Harare Branch', 0.00, 1, 1, '2026-08-25 06:15:51', '2026-08-25 06:15:51'), (4, 18000.00, 'Bulawayo Branch', 0.00, 1, 1, '2026-08-25 06:15:51', '2026-08-25 06:15:51'), (5, 21000.00, 'Head Office', 0.00, 1, 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (6, 15000.00, 'Harare Branch', 0.00, 1, 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (7, 18000.00, 'Bulawayo Branch', 0.00, 1, 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'); -- -------------------------------------------------------- -- -- Table structure for table `users` -- CREATE TABLE `users` ( `id` int(11) NOT NULL, `username` varchar(50) NOT NULL, `email` varchar(100) NOT NULL, `password_hash` varchar(255) NOT NULL, `full_name` varchar(100) NOT NULL, `phone` varchar(20) DEFAULT NULL, `role` enum('admin','manager','loan_officer','accountant','viewer') DEFAULT 'loan_officer', `profile_image` varchar(255) DEFAULT NULL, `last_login` datetime DEFAULT NULL, `is_active` tinyint(1) DEFAULT 1, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `users` -- INSERT INTO `users` (`id`, `username`, `email`, `password_hash`, `full_name`, `phone`, `role`, `profile_image`, `last_login`, `is_active`, `created_at`, `updated_at`) VALUES (1, 'admin', 'admin@accountsystem.com', '$2y$10$UMUHB/OwoM.dUgahf8yqJOVI88TvlOEZl57WLYNTqwlyrUrJ6ihP.', 'System Administrator', NULL, 'admin', NULL, '2026-08-25 04:33:56', 1, '2026-06-26 07:01:51', '2026-08-25 11:44:13'); -- -------------------------------------------------------- -- -- Table structure for table `withdrawals` -- CREATE TABLE `withdrawals` ( `id` int(11) NOT NULL, `withdrawal_type_id` int(11) NOT NULL, `branch_id` int(11) DEFAULT NULL, `full_name` varchar(100) NOT NULL COMMENT 'Full name of person/entity receiving withdrawal', `purpose` text NOT NULL COMMENT 'Purpose of the withdrawal', `amount` decimal(15,2) NOT NULL, `withdrawal_date` date NOT NULL, `recorded_by` int(11) NOT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `withdrawals` -- INSERT INTO `withdrawals` (`id`, `withdrawal_type_id`, `branch_id`, `full_name`, `purpose`, `amount`, `withdrawal_date`, `recorded_by`, `created_at`, `updated_at`) VALUES (1, 1, 1, 'Tendai Chikwanha', 'Personal loan disbursement', 10000.00, '2026-08-20', 1, '2026-08-25 06:07:08', '2026-08-25 06:07:08'), (2, 2, 2, 'Office Supplies Ltd', 'Purchase of office furniture and supplies', 3500.00, '2026-08-20', 1, '2026-08-25 06:07:08', '2026-08-25 06:07:08'), (3, 3, 3, 'Owner Drawings', 'Owner drawings for personal use', 2000.00, '2026-08-21', 1, '2026-08-25 06:07:08', '2026-08-25 06:07:08'), (4, 4, 1, 'ABC Suppliers', 'Payment for inventory supplies', 4500.00, '2026-08-21', 1, '2026-08-25 06:07:08', '2026-08-25 06:07:08'), (5, 5, 2, 'Petty Cash', 'Office petty cash replenishment', 500.00, '2026-08-22', 1, '2026-08-25 06:07:08', '2026-08-25 06:07:08'), (6, 6, 3, 'ZIMRA', 'Tax payment for the month', 2500.00, '2026-08-22', 1, '2026-08-25 06:07:08', '2026-08-25 06:07:08'), (7, 1, 1, 'Tendai Chikwanha', 'Personal loan disbursement', 10000.00, '2026-08-24', 1, '2026-08-25 06:15:51', '2026-08-25 06:15:51'), (8, 2, 2, 'Office Supplies Ltd', 'Purchase of office furniture', 3500.00, '2026-08-24', 1, '2026-08-25 06:15:51', '2026-08-25 06:15:51'), (9, 3, 3, 'Owner Drawings', 'Owner drawings for personal use', 2000.00, '2026-08-24', 1, '2026-08-25 06:15:51', '2026-08-25 06:15:51'), (10, 4, 1, 'ABC Suppliers', 'Payment for inventory supplies', 4500.00, '2026-08-25', 1, '2026-08-25 06:15:51', '2026-08-25 06:26:37'), (11, 5, 2, 'Petty Cash', 'Office petty cash replenishment', 500.00, '2026-08-25', 1, '2026-08-25 06:15:51', '2026-08-25 06:26:26'), (12, 6, 3, 'ZIMRA', 'Tax payment for the month', 2500.00, '2026-08-25', 1, '2026-08-25 06:15:51', '2026-08-25 06:26:18'), (13, 1, 1, 'Tendai Chikwanha', 'Personal loan disbursement', 10000.00, '2026-08-01', 1, '2026-08-25 10:08:58', '2026-08-25 10:08:58'), (14, 2, 2, 'Office Supplies Ltd', 'Purchase of office furniture', 3500.00, '2026-08-02', 1, '2026-08-25 10:08:58', '2026-08-25 10:08:58'), (15, 3, 3, 'Owner Drawings', 'Owner drawings for personal use', 2000.00, '2026-08-03', 1, '2026-08-25 10:08:58', '2026-08-25 10:08:58'), (16, 4, 1, 'ABC Suppliers', 'Payment for inventory supplies', 4500.00, '2026-08-04', 1, '2026-08-25 10:08:58', '2026-08-25 10:08:58'), (17, 5, 2, 'Petty Cash', 'Office petty cash replenishment', 500.00, '2026-08-05', 1, '2026-08-25 10:08:58', '2026-08-25 10:08:58'), (18, 6, 3, 'ZIMRA', 'Tax payment for the month', 2500.00, '2026-08-06', 1, '2026-08-25 10:08:58', '2026-08-25 10:08:58'), (19, 2, 1, 'Salaries Payroll', 'Monthly staff salaries', 8000.00, '2026-08-07', 1, '2026-08-25 10:08:58', '2026-08-25 10:08:58'), (20, 1, 2, 'Mary Ncube', 'Business loan disbursement', 5000.00, '2026-08-08', 1, '2026-08-25 10:08:58', '2026-08-25 10:08:58'), (21, 4, 3, 'Global Supplies', 'Office equipment purchase', 3200.00, '2026-08-09', 1, '2026-08-25 10:08:58', '2026-08-25 10:08:58'), (22, 2, 1, 'Internet & Phone', 'Monthly utility bills', 450.00, '2026-08-10', 1, '2026-08-25 10:08:58', '2026-08-25 10:08:58'), (23, 3, 2, 'Capital Withdrawal', 'Partner drawings', 1500.00, '2026-08-11', 1, '2026-08-25 10:08:58', '2026-08-25 10:08:58'), (24, 6, 1, 'NSSA', 'NSSA contributions', 1200.00, '2026-08-12', 1, '2026-08-25 10:08:58', '2026-08-25 10:08:58'), (25, 5, 3, 'Petty Cash', 'Office supplies', 300.00, '2026-08-13', 1, '2026-08-25 10:08:58', '2026-08-25 10:08:58'), (26, 1, 2, 'John Smith', 'Car loan disbursement', 7500.00, '2026-08-14', 1, '2026-08-25 10:08:58', '2026-08-25 10:08:58'), (27, 4, 1, 'Tech Solutions', 'Software license renewal', 2500.00, '2026-08-15', 1, '2026-08-25 10:08:58', '2026-08-25 10:08:58'), (28, 1, 1, 'Tendai Chikwanha', 'Personal loan disbursement', 10000.00, '2026-08-01', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (29, 2, 2, 'Office Supplies Ltd', 'Purchase of office furniture', 3500.00, '2026-08-02', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (30, 3, 3, 'Owner Drawings', 'Owner drawings for personal use', 2000.00, '2026-08-03', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (31, 4, 1, 'ABC Suppliers', 'Payment for inventory supplies', 4500.00, '2026-08-04', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (32, 5, 2, 'Petty Cash', 'Office petty cash replenishment', 500.00, '2026-08-05', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (33, 6, 3, 'ZIMRA', 'Tax payment for the month', 2500.00, '2026-08-06', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (34, 2, 1, 'Salaries Payroll', 'Monthly staff salaries', 8000.00, '2026-08-07', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (35, 1, 2, 'Mary Ncube', 'Business loan disbursement', 5000.00, '2026-08-08', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (36, 4, 3, 'Global Supplies', 'Office equipment purchase', 3200.00, '2026-08-09', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (37, 2, 1, 'Internet & Phone', 'Monthly utility bills', 450.00, '2026-08-10', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (38, 3, 2, 'Capital Withdrawal', 'Partner drawings', 1500.00, '2026-08-11', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (39, 6, 1, 'NSSA', 'NSSA contributions', 1200.00, '2026-08-12', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (40, 5, 3, 'Petty Cash', 'Office supplies', 300.00, '2026-08-13', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (41, 1, 2, 'John Smith', 'Car loan disbursement', 7500.00, '2026-08-14', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (42, 4, 1, 'Tech Solutions', 'Software license renewal', 2500.00, '2026-08-15', 1, '2026-08-25 10:09:48', '2026-08-25 10:09:48'), (43, 1, 1, 'Tendai Chikwanha', 'Personal loan disbursement', 10000.00, '2026-08-01', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (44, 2, 2, 'Office Supplies Ltd', 'Purchase of office furniture', 3500.00, '2026-08-02', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (45, 3, 3, 'Owner Drawings', 'Owner drawings for personal use', 2000.00, '2026-08-03', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (46, 4, 1, 'ABC Suppliers', 'Payment for inventory supplies', 4500.00, '2026-08-04', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (47, 5, 2, 'Petty Cash', 'Office petty cash replenishment', 500.00, '2026-08-05', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (48, 6, 3, 'ZIMRA', 'Tax payment for the month', 2500.00, '2026-08-06', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (49, 2, 1, 'Salaries Payroll', 'Monthly staff salaries', 8000.00, '2026-08-07', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (50, 1, 2, 'Mary Ncube', 'Business loan disbursement', 5000.00, '2026-08-08', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (51, 4, 3, 'Global Supplies', 'Office equipment purchase', 3200.00, '2026-08-09', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (52, 2, 1, 'Internet & Phone', 'Monthly utility bills', 450.00, '2026-08-10', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (53, 3, 2, 'Capital Withdrawal', 'Partner drawings', 1500.00, '2026-08-11', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (54, 6, 1, 'NSSA', 'NSSA contributions', 1200.00, '2026-08-12', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (55, 5, 3, 'Petty Cash', 'Office supplies', 300.00, '2026-08-13', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (56, 1, 2, 'John Smith', 'Car loan disbursement', 7500.00, '2026-08-14', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (57, 4, 1, 'Tech Solutions', 'Software license renewal', 2500.00, '2026-08-15', 1, '2026-08-25 10:10:48', '2026-08-25 10:10:48'), (58, 1, 1, 'Tendai Chikwanha', 'Personal loan disbursement', 10000.00, '2026-08-01', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (59, 2, 2, 'Office Supplies Ltd', 'Purchase of office furniture', 3500.00, '2026-08-02', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (60, 3, 3, 'Owner Drawings', 'Owner drawings for personal use', 2000.00, '2026-08-03', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (61, 4, 1, 'ABC Suppliers', 'Payment for inventory supplies', 4500.00, '2026-08-04', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (62, 5, 2, 'Petty Cash', 'Office petty cash replenishment', 500.00, '2026-08-05', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (63, 6, 3, 'ZIMRA', 'Tax payment for the month', 2500.00, '2026-08-06', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (64, 2, 1, 'Salaries Payroll', 'Monthly staff salaries', 8000.00, '2026-08-07', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (65, 1, 2, 'Mary Ncube', 'Business loan disbursement', 5000.00, '2026-08-08', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (66, 4, 3, 'Global Supplies', 'Office equipment purchase', 3200.00, '2026-08-09', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (67, 2, 1, 'Internet & Phone', 'Monthly utility bills', 450.00, '2026-08-10', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (68, 3, 2, 'Capital Withdrawal', 'Partner drawings', 1500.00, '2026-08-11', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (69, 6, 1, 'NSSA', 'NSSA contributions', 1200.00, '2026-08-12', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (70, 5, 3, 'Petty Cash', 'Office supplies', 300.00, '2026-08-13', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (71, 1, 2, 'John Smith', 'Car loan disbursement', 7500.00, '2026-08-14', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'), (72, 4, 1, 'Tech Solutions', 'Software license renewal', 2500.00, '2026-08-15', 1, '2026-08-25 10:11:11', '2026-08-25 10:11:11'); -- -------------------------------------------------------- -- -- Table structure for table `withdrawal_types` -- CREATE TABLE `withdrawal_types` ( `id` int(11) NOT NULL, `name` varchar(100) NOT NULL, `description` text DEFAULT NULL, `is_active` tinyint(1) DEFAULT 1, `is_system` tinyint(1) DEFAULT 0 COMMENT 'System predefined types', `created_by` int(11) DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -- -- Dumping data for table `withdrawal_types` -- INSERT INTO `withdrawal_types` (`id`, `name`, `description`, `is_active`, `is_system`, `created_by`, `created_at`, `updated_at`) VALUES (1, 'Loan Disbursement', 'Loan disbursement to clients', 1, 1, 1, '2026-08-25 06:07:08', '2026-08-25 06:07:08'), (2, 'Operating Expense', 'General business operating expenses', 1, 1, 1, '2026-08-25 06:07:08', '2026-08-25 06:07:08'), (3, 'Capital Withdrawal', 'Capital withdrawal or owner drawings', 1, 1, 1, '2026-08-25 06:07:08', '2026-08-25 06:07:08'), (4, 'Supplier Payment', 'Payments to suppliers and vendors', 1, 0, 1, '2026-08-25 06:07:08', '2026-08-25 06:07:08'), (5, 'Petty Cash', 'Petty cash expenses and replenishment', 1, 0, 1, '2026-08-25 06:07:08', '2026-08-25 06:07:08'), (6, 'Tax Payment', 'Tax and regulatory payments', 1, 0, 1, '2026-08-25 06:07:08', '2026-08-25 06:07:08'); -- -- Indexes for dumped tables -- -- -- Indexes for table `audit_trail` -- ALTER TABLE `audit_trail` ADD PRIMARY KEY (`id`), ADD KEY `user_id` (`user_id`), ADD KEY `idx_table_name` (`table_name`), ADD KEY `idx_record_id` (`record_id`), ADD KEY `idx_action` (`action`), ADD KEY `idx_created_at` (`created_at`); -- -- Indexes for table `branch` -- ALTER TABLE `branch` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `name` (`name`), ADD KEY `created_by` (`created_by`), ADD KEY `idx_is_active` (`is_active`); -- -- Indexes for table `deposits` -- ALTER TABLE `deposits` ADD PRIMARY KEY (`id`), ADD KEY `deposit_type_id` (`deposit_type_id`), ADD KEY `recorded_by` (`recorded_by`), ADD KEY `idx_deposit_date` (`deposit_date`), ADD KEY `idx_full_name` (`full_name`), ADD KEY `branch_id` (`branch_id`); -- -- Indexes for table `deposit_types` -- ALTER TABLE `deposit_types` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `name` (`name`), ADD KEY `created_by` (`created_by`), ADD KEY `idx_is_active` (`is_active`); -- -- Indexes for table `expenses` -- ALTER TABLE `expenses` ADD PRIMARY KEY (`id`), ADD KEY `expense_category_id` (`expense_category_id`), ADD KEY `recorded_by` (`recorded_by`), ADD KEY `idx_expense_date` (`expense_date`), ADD KEY `idx_full_name` (`full_name`); -- -- Indexes for table `expense_categories` -- ALTER TABLE `expense_categories` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `name` (`name`), ADD KEY `parent_category_id` (`parent_category_id`), ADD KEY `created_by` (`created_by`), ADD KEY `idx_is_active` (`is_active`); -- -- Indexes for table `products` -- ALTER TABLE `products` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `product_code` (`product_code`), ADD KEY `created_by` (`created_by`), ADD KEY `deleted_by` (`deleted_by`), ADD KEY `idx_product_name` (`product_name`), ADD KEY `idx_is_active` (`is_active`), ADD KEY `idx_category` (`category`); -- -- Indexes for table `purchases` -- ALTER TABLE `purchases` ADD PRIMARY KEY (`id`), ADD KEY `purchase_type_id` (`purchase_type_id`), ADD KEY `product_id` (`product_id`), ADD KEY `created_by` (`created_by`), ADD KEY `deleted_by` (`deleted_by`), ADD KEY `idx_purchase_date` (`purchase_date`), ADD KEY `idx_is_active` (`is_active`), ADD KEY `idx_purchaser_name` (`purchaser_name`); -- -- Indexes for table `purchase_types` -- ALTER TABLE `purchase_types` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `name` (`name`), ADD KEY `created_by` (`created_by`), ADD KEY `deleted_by` (`deleted_by`), ADD KEY `idx_name` (`name`), ADD KEY `idx_is_active` (`is_active`), ADD KEY `idx_purchase_date` (`purchase_date`); -- -- Indexes for table `sales` -- ALTER TABLE `sales` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `unique_sales_record` (`sales_date`,`branch_id`), ADD KEY `branch_id` (`branch_id`), ADD KEY `created_by` (`created_by`), ADD KEY `submitted_by` (`submitted_by`), ADD KEY `approved_by` (`approved_by`), ADD KEY `idx_sales_date` (`sales_date`), ADD KEY `idx_status` (`status`); -- -- Indexes for table `settings` -- ALTER TABLE `settings` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `setting_key` (`setting_key`), ADD KEY `updated_by` (`updated_by`), ADD KEY `idx_setting_key` (`setting_key`), ADD KEY `idx_setting_group` (`setting_group`); -- -- Indexes for table `staff` -- ALTER TABLE `staff` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `user_id` (`user_id`), ADD UNIQUE KEY `staff_number` (`staff_number`), ADD KEY `supervisor_id` (`supervisor_id`), ADD KEY `idx_staff_number` (`staff_number`), ADD KEY `idx_department` (`department`); -- -- Indexes for table `system_logs` -- ALTER TABLE `system_logs` ADD PRIMARY KEY (`id`), ADD KEY `idx_user_id` (`user_id`), ADD KEY `idx_module` (`module`), ADD KEY `idx_action` (`action`), ADD KEY `idx_created_at` (`created_at`); -- -- Indexes for table `targets` -- ALTER TABLE `targets` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `unique_target` (`branch_id`,`target_date`,`is_active`), ADD KEY `target_cost_id` (`target_cost_id`), ADD KEY `created_by` (`created_by`), ADD KEY `deleted_by` (`deleted_by`), ADD KEY `idx_target_date` (`target_date`), ADD KEY `idx_status` (`status`), ADD KEY `idx_is_active` (`is_active`); -- -- Indexes for table `target_cashiers` -- ALTER TABLE `target_cashiers` ADD PRIMARY KEY (`id`), ADD KEY `target_id` (`target_id`), ADD KEY `cashier_user_id` (`cashier_user_id`); -- -- Indexes for table `target_cost` -- ALTER TABLE `target_cost` ADD PRIMARY KEY (`id`), ADD KEY `created_by` (`created_by`), ADD KEY `idx_branch_name` (`branch_name`), ADD KEY `idx_is_active` (`is_active`); -- -- Indexes for table `users` -- ALTER TABLE `users` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `username` (`username`), ADD UNIQUE KEY `email` (`email`), ADD KEY `idx_email` (`email`), ADD KEY `idx_username` (`username`), ADD KEY `idx_role` (`role`); -- -- Indexes for table `withdrawals` -- ALTER TABLE `withdrawals` ADD PRIMARY KEY (`id`), ADD KEY `withdrawal_type_id` (`withdrawal_type_id`), ADD KEY `recorded_by` (`recorded_by`), ADD KEY `idx_withdrawal_date` (`withdrawal_date`), ADD KEY `idx_full_name` (`full_name`), ADD KEY `branch_id` (`branch_id`); -- -- Indexes for table `withdrawal_types` -- ALTER TABLE `withdrawal_types` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `name` (`name`), ADD KEY `created_by` (`created_by`), ADD KEY `idx_is_active` (`is_active`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `audit_trail` -- ALTER TABLE `audit_trail` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `branch` -- ALTER TABLE `branch` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4; -- -- AUTO_INCREMENT for table `deposits` -- ALTER TABLE `deposits` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=79; -- -- AUTO_INCREMENT for table `deposit_types` -- ALTER TABLE `deposit_types` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=29; -- -- AUTO_INCREMENT for table `expenses` -- ALTER TABLE `expenses` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=61; -- -- AUTO_INCREMENT for table `expense_categories` -- ALTER TABLE `expense_categories` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11; -- -- AUTO_INCREMENT for table `products` -- ALTER TABLE `products` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7; -- -- AUTO_INCREMENT for table `purchases` -- ALTER TABLE `purchases` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=23; -- -- AUTO_INCREMENT for table `purchase_types` -- ALTER TABLE `purchase_types` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5; -- -- AUTO_INCREMENT for table `sales` -- ALTER TABLE `sales` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=14; -- -- AUTO_INCREMENT for table `settings` -- ALTER TABLE `settings` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=31; -- -- AUTO_INCREMENT for table `staff` -- ALTER TABLE `staff` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2; -- -- AUTO_INCREMENT for table `system_logs` -- ALTER TABLE `system_logs` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; -- -- AUTO_INCREMENT for table `targets` -- ALTER TABLE `targets` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11; -- -- AUTO_INCREMENT for table `target_cashiers` -- ALTER TABLE `target_cashiers` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11; -- -- AUTO_INCREMENT for table `target_cost` -- ALTER TABLE `target_cost` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8; -- -- AUTO_INCREMENT for table `users` -- ALTER TABLE `users` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2; -- -- AUTO_INCREMENT for table `withdrawals` -- ALTER TABLE `withdrawals` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=73; -- -- AUTO_INCREMENT for table `withdrawal_types` -- ALTER TABLE `withdrawal_types` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8; -- -- Constraints for dumped tables -- -- -- Constraints for table `audit_trail` -- ALTER TABLE `audit_trail` ADD CONSTRAINT `audit_trail_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`); -- -- Constraints for table `branch` -- ALTER TABLE `branch` ADD CONSTRAINT `branch_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL; -- -- Constraints for table `deposits` -- ALTER TABLE `deposits` ADD CONSTRAINT `deposits_ibfk_1` FOREIGN KEY (`deposit_type_id`) REFERENCES `deposit_types` (`id`), ADD CONSTRAINT `deposits_ibfk_2` FOREIGN KEY (`recorded_by`) REFERENCES `users` (`id`), ADD CONSTRAINT `deposits_ibfk_3` FOREIGN KEY (`branch_id`) REFERENCES `branch` (`id`) ON DELETE SET NULL; -- -- Constraints for table `deposit_types` -- ALTER TABLE `deposit_types` ADD CONSTRAINT `deposit_types_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL; -- -- Constraints for table `expenses` -- ALTER TABLE `expenses` ADD CONSTRAINT `expenses_ibfk_1` FOREIGN KEY (`expense_category_id`) REFERENCES `expense_categories` (`id`), ADD CONSTRAINT `expenses_ibfk_2` FOREIGN KEY (`recorded_by`) REFERENCES `users` (`id`); -- -- Constraints for table `expense_categories` -- ALTER TABLE `expense_categories` ADD CONSTRAINT `expense_categories_ibfk_1` FOREIGN KEY (`parent_category_id`) REFERENCES `expense_categories` (`id`) ON DELETE SET NULL, ADD CONSTRAINT `expense_categories_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL; -- -- Constraints for table `products` -- ALTER TABLE `products` ADD CONSTRAINT `products_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`), ADD CONSTRAINT `products_ibfk_2` FOREIGN KEY (`deleted_by`) REFERENCES `users` (`id`) ON DELETE SET NULL; -- -- Constraints for table `purchases` -- ALTER TABLE `purchases` ADD CONSTRAINT `purchases_ibfk_1` FOREIGN KEY (`purchase_type_id`) REFERENCES `purchase_types` (`id`), ADD CONSTRAINT `purchases_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`), ADD CONSTRAINT `purchases_ibfk_3` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`), ADD CONSTRAINT `purchases_ibfk_4` FOREIGN KEY (`deleted_by`) REFERENCES `users` (`id`) ON DELETE SET NULL; -- -- Constraints for table `purchase_types` -- ALTER TABLE `purchase_types` ADD CONSTRAINT `purchase_types_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`), ADD CONSTRAINT `purchase_types_ibfk_2` FOREIGN KEY (`deleted_by`) REFERENCES `users` (`id`) ON DELETE SET NULL; -- -- Constraints for table `sales` -- ALTER TABLE `sales` ADD CONSTRAINT `sales_ibfk_1` FOREIGN KEY (`branch_id`) REFERENCES `branch` (`id`), ADD CONSTRAINT `sales_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`), ADD CONSTRAINT `sales_ibfk_3` FOREIGN KEY (`submitted_by`) REFERENCES `users` (`id`) ON DELETE SET NULL, ADD CONSTRAINT `sales_ibfk_4` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`) ON DELETE SET NULL; -- -- Constraints for table `targets` -- ALTER TABLE `targets` ADD CONSTRAINT `targets_ibfk_1` FOREIGN KEY (`branch_id`) REFERENCES `branch` (`id`), ADD CONSTRAINT `targets_ibfk_2` FOREIGN KEY (`target_cost_id`) REFERENCES `target_cost` (`id`), ADD CONSTRAINT `targets_ibfk_3` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`); -- -- Constraints for table `target_cashiers` -- ALTER TABLE `target_cashiers` ADD CONSTRAINT `target_cashiers_ibfk_1` FOREIGN KEY (`target_id`) REFERENCES `targets` (`id`) ON DELETE CASCADE, ADD CONSTRAINT `target_cashiers_ibfk_2` FOREIGN KEY (`cashier_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL; -- -- Constraints for table `target_cost` -- ALTER TABLE `target_cost` ADD CONSTRAINT `target_cost_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL; -- -- Constraints for table `withdrawals` -- ALTER TABLE `withdrawals` ADD CONSTRAINT `withdrawals_ibfk_1` FOREIGN KEY (`withdrawal_type_id`) REFERENCES `withdrawal_types` (`id`), ADD CONSTRAINT `withdrawals_ibfk_2` FOREIGN KEY (`recorded_by`) REFERENCES `users` (`id`), ADD CONSTRAINT `withdrawals_ibfk_7` FOREIGN KEY (`branch_id`) REFERENCES `branch` (`id`) ON DELETE SET NULL; -- -- Constraints for table `withdrawal_types` -- ALTER TABLE `withdrawal_types` ADD CONSTRAINT `withdrawal_types_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL; COMMIT; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;