-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- 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);
    
    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(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 `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 `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 `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;

