-- Database Backup
-- Generated: 2026-08-25 20:45:57

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

DROP TABLE IF EXISTS `branch`;
CREATE TABLE `branch` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `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(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `created_by` (`created_by`),
  KEY `idx_is_active` (`is_active`),
  CONSTRAINT `branch_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `branch` VALUES ('1', 'VIP BAR', 'NICETIME NIGHT CLUB', '0785462058', 'info@nicetime.co.zw', 'FORGIVE', '1', '1', '2026-08-25 02:54:49', '2026-08-25 14:53:36');
INSERT INTO `branch` VALUES ('2', 'COCKTAIL BAR', 'NICETIME NIGHT CLUB', '0785462058', 'info@nicetime.co.zw', 'FORGIVE', '1', '1', '2026-08-25 02:54:49', '2026-08-25 14:47:31');
INSERT INTO `branch` VALUES ('3', 'MAIN BAR', 'NICETIME', '0785462058', 'info@nicetime.co.zw', 'FORGIVE', '1', '1', '2026-08-25 02:54:49', '2026-08-25 14:46:40');

DROP TABLE IF EXISTS `deposit_types`;
CREATE TABLE `deposit_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `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(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `created_by` (`created_by`),
  KEY `idx_is_active` (`is_active`),
  CONSTRAINT `deposit_types_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `deposit_types` VALUES ('1', 'Client Loan Payment', 'Regular loan repayments from clients', '1', '1', '1', '2026-08-25 08:07:07', '2026-08-25 08:07:07');
INSERT INTO `deposit_types` VALUES ('2', 'Interest Income', 'Interest earned from loans', '1', '1', '1', '2026-08-25 08:07:07', '2026-08-25 08:07:07');
INSERT INTO `deposit_types` VALUES ('3', 'Capital Injection', 'Owner or shareholder capital injection', '1', '1', '1', '2026-08-25 08:07:07', '2026-08-25 08:07:07');
INSERT INTO `deposit_types` VALUES ('4', 'Service Fees', 'Processing and service fees', '1', '0', '1', '2026-08-25 08:07:07', '2026-08-25 08:07:07');
INSERT INTO `deposit_types` VALUES ('5', 'Investment Income', 'Income from investments', '1', '0', '1', '2026-08-25 08:07:07', '2026-08-25 08:07:07');
INSERT INTO `deposit_types` VALUES ('6', 'Other Income', 'Miscellaneous income', '1', '0', '1', '2026-08-25 08:07:07', '2026-08-25 08:07:07');

DROP TABLE IF EXISTS `deposits`;
CREATE TABLE `deposits` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `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(),
  PRIMARY KEY (`id`),
  KEY `deposit_type_id` (`deposit_type_id`),
  KEY `recorded_by` (`recorded_by`),
  KEY `idx_deposit_date` (`deposit_date`),
  KEY `idx_full_name` (`full_name`),
  KEY `branch_id` (`branch_id`),
  CONSTRAINT `deposits_ibfk_1` FOREIGN KEY (`deposit_type_id`) REFERENCES `deposit_types` (`id`),
  CONSTRAINT `deposits_ibfk_2` FOREIGN KEY (`recorded_by`) REFERENCES `users` (`id`),
  CONSTRAINT `deposits_ibfk_3` FOREIGN KEY (`branch_id`) REFERENCES `branch` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=79 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `deposits` VALUES ('1', '1', '1', 'John Moyo', '2500.00', '2026-08-20', '1', '2026-08-25 08:07:07', '2026-08-25 08:07:07');
INSERT INTO `deposits` VALUES ('2', '1', '2', 'Mary Ncube', '1800.00', '2026-08-20', '1', '2026-08-25 08:07:07', '2026-08-25 08:07:07');
INSERT INTO `deposits` VALUES ('3', '2', '1', 'Interest Payment', '450.00', '2026-08-21', '1', '2026-08-25 08:07:07', '2026-08-25 08:07:07');
INSERT INTO `deposits` VALUES ('4', '3', '3', 'Shareholder Capital', '5000.00', '2026-08-21', '1', '2026-08-25 08:07:07', '2026-08-25 08:07:07');
INSERT INTO `deposits` VALUES ('5', '4', '2', 'Processing Fees', '150.00', '2026-08-22', '1', '2026-08-25 08:07:07', '2026-08-25 08:07:07');
INSERT INTO `deposits` VALUES ('6', '1', '3', 'Peter Moyo', '3200.00', '2026-08-22', '1', '2026-08-25 08:07:07', '2026-08-25 08:07:07');
INSERT INTO `deposits` VALUES ('7', '1', '1', 'John Moyo', '2500.00', '2026-08-24', '1', '2026-08-25 08:15:31', '2026-08-25 08:15:31');
INSERT INTO `deposits` VALUES ('8', '1', '2', 'Mary Ncube', '1800.00', '2026-08-24', '1', '2026-08-25 08:15:31', '2026-08-25 08:15:31');
INSERT INTO `deposits` VALUES ('9', '2', '1', 'Interest Payment', '450.00', '2026-08-24', '1', '2026-08-25 08:15:31', '2026-08-25 08:15:31');
INSERT INTO `deposits` VALUES ('10', '3', '3', 'Shareholder Capital', '5000.00', '2026-08-24', '1', '2026-08-25 08:15:31', '2026-08-25 08:15:31');
INSERT INTO `deposits` VALUES ('11', '4', '2', 'Processing Fees', '150.00', '2026-08-24', '1', '2026-08-25 08:15:31', '2026-08-25 08:15:31');
INSERT INTO `deposits` VALUES ('12', '1', '3', 'Peter Moyo', '3200.00', '2026-08-24', '1', '2026-08-25 08:15:31', '2026-08-25 08:15:31');
INSERT INTO `deposits` VALUES ('13', '1', '1', 'John Moyo', '2500.00', '2026-08-24', '1', '2026-08-25 08:15:51', '2026-08-25 08:15:51');
INSERT INTO `deposits` VALUES ('14', '1', '2', 'Mary Ncube', '1800.00', '2026-08-24', '1', '2026-08-25 08:15:51', '2026-08-25 08:15:51');
INSERT INTO `deposits` VALUES ('15', '2', '1', 'Interest Payment', '450.00', '2026-08-25', '1', '2026-08-25 08:15:51', '2026-08-25 08:27:15');
INSERT INTO `deposits` VALUES ('16', '3', '3', 'Shareholder Capital', '5000.00', '2026-08-25', '1', '2026-08-25 08:15:51', '2026-08-25 08:27:07');
INSERT INTO `deposits` VALUES ('17', '4', '2', 'Processing Fees', '150.00', '2026-08-25', '1', '2026-08-25 08:15:51', '2026-08-25 08:27:01');
INSERT INTO `deposits` VALUES ('18', '1', '3', 'Peter Moyo', '3200.00', '2026-08-25', '1', '2026-08-25 08:15:51', '2026-08-25 08:26:53');
INSERT INTO `deposits` VALUES ('19', '1', '1', 'John Moyo', '2500.00', '2026-08-01', '1', '2026-08-25 12:08:56', '2026-08-25 12:08:56');
INSERT INTO `deposits` VALUES ('20', '2', '1', 'Interest Payment', '450.00', '2026-08-02', '1', '2026-08-25 12:08:56', '2026-08-25 12:08:56');
INSERT INTO `deposits` VALUES ('21', '1', '2', 'Mary Ncube', '1800.00', '2026-08-03', '1', '2026-08-25 12:08:56', '2026-08-25 12:08:56');
INSERT INTO `deposits` VALUES ('22', '3', '3', 'Shareholder Capital', '5000.00', '2026-08-04', '1', '2026-08-25 12:08:56', '2026-08-25 12:08:56');
INSERT INTO `deposits` VALUES ('23', '4', '2', 'Processing Fees', '150.00', '2026-08-05', '1', '2026-08-25 12:08:56', '2026-08-25 12:08:56');
INSERT INTO `deposits` VALUES ('24', '1', '3', 'Peter Moyo', '3200.00', '2026-08-06', '1', '2026-08-25 12:08:56', '2026-08-25 12:08:56');
INSERT INTO `deposits` VALUES ('25', '1', '1', 'Tendai Chikwanha', '2100.00', '2026-08-07', '1', '2026-08-25 12:08:56', '2026-08-25 12:08:56');
INSERT INTO `deposits` VALUES ('26', '2', '2', 'Interest Income', '380.00', '2026-08-08', '1', '2026-08-25 12:08:56', '2026-08-25 12:08:56');
INSERT INTO `deposits` VALUES ('27', '3', '1', 'Capital Injection', '10000.00', '2026-08-09', '1', '2026-08-25 12:08:56', '2026-08-25 12:08:56');
INSERT INTO `deposits` VALUES ('28', '1', '3', 'Sarah Ndlovu', '1500.00', '2026-08-10', '1', '2026-08-25 12:08:56', '2026-08-25 12:08:56');
INSERT INTO `deposits` VALUES ('29', '5', '2', 'Investment Return', '750.00', '2026-08-11', '1', '2026-08-25 12:08:56', '2026-08-25 12:08:56');
INSERT INTO `deposits` VALUES ('30', '1', '1', 'David Moyo', '3200.00', '2026-08-12', '1', '2026-08-25 12:08:56', '2026-08-25 12:08:56');
INSERT INTO `deposits` VALUES ('31', '6', '3', 'Other Income', '200.00', '2026-08-13', '1', '2026-08-25 12:08:56', '2026-08-25 12:08:56');
INSERT INTO `deposits` VALUES ('32', '4', '1', 'Service Fees', '95.00', '2026-08-14', '1', '2026-08-25 12:08:56', '2026-08-25 12:08:56');
INSERT INTO `deposits` VALUES ('33', '1', '2', 'Loan Repayment', '4500.00', '2026-08-15', '1', '2026-08-25 12:08:56', '2026-08-25 12:08:56');
INSERT INTO `deposits` VALUES ('34', '1', '1', 'John Moyo', '2500.00', '2026-08-01', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `deposits` VALUES ('35', '2', '1', 'Interest Payment', '450.00', '2026-08-02', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `deposits` VALUES ('36', '1', '2', 'Mary Ncube', '1800.00', '2026-08-03', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `deposits` VALUES ('37', '3', '3', 'Shareholder Capital', '5000.00', '2026-08-04', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `deposits` VALUES ('38', '4', '2', 'Processing Fees', '150.00', '2026-08-05', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `deposits` VALUES ('39', '1', '3', 'Peter Moyo', '3200.00', '2026-08-06', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `deposits` VALUES ('40', '1', '1', 'Tendai Chikwanha', '2100.00', '2026-08-07', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `deposits` VALUES ('41', '2', '2', 'Interest Income', '380.00', '2026-08-08', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `deposits` VALUES ('42', '3', '1', 'Capital Injection', '10000.00', '2026-08-09', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `deposits` VALUES ('43', '1', '3', 'Sarah Ndlovu', '1500.00', '2026-08-10', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `deposits` VALUES ('44', '5', '2', 'Investment Return', '750.00', '2026-08-11', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `deposits` VALUES ('45', '1', '1', 'David Moyo', '3200.00', '2026-08-12', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `deposits` VALUES ('46', '6', '3', 'Other Income', '200.00', '2026-08-13', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `deposits` VALUES ('47', '4', '1', 'Service Fees', '95.00', '2026-08-14', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `deposits` VALUES ('48', '1', '2', 'Loan Repayment', '4500.00', '2026-08-15', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `deposits` VALUES ('49', '1', '1', 'John Moyo', '2500.00', '2026-08-01', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `deposits` VALUES ('50', '2', '1', 'Interest Payment', '450.00', '2026-08-02', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `deposits` VALUES ('51', '1', '2', 'Mary Ncube', '1800.00', '2026-08-03', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `deposits` VALUES ('52', '3', '3', 'Shareholder Capital', '5000.00', '2026-08-04', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `deposits` VALUES ('53', '4', '2', 'Processing Fees', '150.00', '2026-08-05', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `deposits` VALUES ('54', '1', '3', 'Peter Moyo', '3200.00', '2026-08-06', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `deposits` VALUES ('55', '1', '1', 'Tendai Chikwanha', '2100.00', '2026-08-07', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `deposits` VALUES ('56', '2', '2', 'Interest Income', '380.00', '2026-08-08', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `deposits` VALUES ('57', '3', '1', 'Capital Injection', '10000.00', '2026-08-09', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `deposits` VALUES ('58', '1', '3', 'Sarah Ndlovu', '1500.00', '2026-08-10', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `deposits` VALUES ('59', '5', '2', 'Investment Return', '750.00', '2026-08-11', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `deposits` VALUES ('60', '1', '1', 'David Moyo', '3200.00', '2026-08-12', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `deposits` VALUES ('61', '6', '3', 'Other Income', '200.00', '2026-08-13', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `deposits` VALUES ('62', '4', '1', 'Service Fees', '95.00', '2026-08-14', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `deposits` VALUES ('63', '1', '2', 'Loan Repayment', '4500.00', '2026-08-15', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `deposits` VALUES ('64', '1', '1', 'John Moyo', '2500.00', '2026-08-01', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `deposits` VALUES ('65', '2', '1', 'Interest Payment', '450.00', '2026-08-02', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `deposits` VALUES ('66', '1', '2', 'Mary Ncube', '1800.00', '2026-08-03', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `deposits` VALUES ('67', '3', '3', 'Shareholder Capital', '5000.00', '2026-08-04', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `deposits` VALUES ('68', '4', '2', 'Processing Fees', '150.00', '2026-08-05', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `deposits` VALUES ('69', '1', '3', 'Peter Moyo', '3200.00', '2026-08-06', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `deposits` VALUES ('70', '1', '1', 'Tendai Chikwanha', '2100.00', '2026-08-07', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `deposits` VALUES ('71', '2', '2', 'Interest Income', '380.00', '2026-08-08', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `deposits` VALUES ('72', '3', '1', 'Capital Injection', '10000.00', '2026-08-09', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `deposits` VALUES ('73', '1', '3', 'Sarah Ndlovu', '1500.00', '2026-08-10', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `deposits` VALUES ('74', '5', '2', 'Investment Return', '750.00', '2026-08-11', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `deposits` VALUES ('75', '1', '1', 'David Moyo', '3200.00', '2026-08-12', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `deposits` VALUES ('76', '6', '3', 'Other Income', '200.00', '2026-08-13', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `deposits` VALUES ('77', '4', '1', 'Service Fees', '95.00', '2026-08-14', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `deposits` VALUES ('78', '1', '2', 'Loan Repayment', '4500.00', '2026-08-15', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');

DROP TABLE IF EXISTS `expense_categories`;
CREATE TABLE `expense_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `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(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `parent_category_id` (`parent_category_id`),
  KEY `created_by` (`created_by`),
  KEY `idx_is_active` (`is_active`),
  CONSTRAINT `expense_categories_ibfk_1` FOREIGN KEY (`parent_category_id`) REFERENCES `expense_categories` (`id`) ON DELETE SET NULL,
  CONSTRAINT `expense_categories_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `expense_categories` VALUES ('1', 'FUEL', 'FUEL', NULL, '0', '0', '1', '2026-08-25 02:01:37', '2026-08-25 02:05:10');
INSERT INTO `expense_categories` VALUES ('2', 'Office Rent', 'Monthly office rental expenses', NULL, '1', '0', '1', '2026-08-25 12:08:56', '2026-08-25 12:08:56');
INSERT INTO `expense_categories` VALUES ('3', 'Utilities', 'Electricity, water, internet, and other utilities', NULL, '1', '0', '1', '2026-08-25 12:08:56', '2026-08-25 12:08:56');
INSERT INTO `expense_categories` VALUES ('4', 'Salaries', 'Staff salaries and wages', NULL, '1', '0', '1', '2026-08-25 12:08:56', '2026-08-25 12:08:56');
INSERT INTO `expense_categories` VALUES ('5', 'Marketing', 'Marketing and advertising expenses', NULL, '0', '0', '1', '2026-08-25 12:08:56', '2026-08-25 20:25:20');
INSERT INTO `expense_categories` VALUES ('6', 'Stationery', 'Office stationery and supplies', NULL, '1', '0', '1', '2026-08-25 12:08:56', '2026-08-25 12:08:56');
INSERT INTO `expense_categories` VALUES ('7', 'Transport', 'Transportation and fuel costs', NULL, '1', '0', '1', '2026-08-25 12:08:56', '2026-08-25 12:08:56');
INSERT INTO `expense_categories` VALUES ('8', 'Legal Fees', 'Legal and professional fees', NULL, '1', '0', '1', '2026-08-25 12:08:56', '2026-08-25 12:08:56');
INSERT INTO `expense_categories` VALUES ('9', 'Software', 'Software licenses and subscriptions', NULL, '1', '0', '1', '2026-08-25 12:08:56', '2026-08-25 12:08:56');

DROP TABLE IF EXISTS `expenses`;
CREATE TABLE `expenses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `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(),
  PRIMARY KEY (`id`),
  KEY `expense_category_id` (`expense_category_id`),
  KEY `recorded_by` (`recorded_by`),
  KEY `idx_expense_date` (`expense_date`),
  KEY `idx_full_name` (`full_name`),
  CONSTRAINT `expenses_ibfk_1` FOREIGN KEY (`expense_category_id`) REFERENCES `expense_categories` (`id`),
  CONSTRAINT `expenses_ibfk_2` FOREIGN KEY (`recorded_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=61 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `expenses` VALUES ('1', '1', 'ABC Properties', 'Monthly office rent for August', '1200.00', '2026-08-01', '1', '2026-08-25 12:08:58', '2026-08-25 12:08:58');
INSERT INTO `expenses` VALUES ('2', '2', 'ZESA', 'Electricity bill payment', '450.00', '2026-08-02', '1', '2026-08-25 12:08:58', '2026-08-25 12:08:58');
INSERT INTO `expenses` VALUES ('3', '3', 'Staff Payroll', 'Salary payment for August', '8000.00', '2026-08-03', '1', '2026-08-25 12:08:58', '2026-08-25 12:08:58');
INSERT INTO `expenses` VALUES ('4', '4', 'Marketing Agency', 'Social media advertising', '300.00', '2026-08-04', '1', '2026-08-25 12:08:58', '2026-08-25 12:08:58');
INSERT INTO `expenses` VALUES ('5', '5', 'Office Express', 'Stationery and office supplies', '150.00', '2026-08-05', '1', '2026-08-25 12:08:58', '2026-08-25 12:08:58');
INSERT INTO `expenses` VALUES ('6', '6', 'Total Fuel', 'Fuel for company vehicles', '200.00', '2026-08-06', '1', '2026-08-25 12:08:58', '2026-08-25 12:08:58');
INSERT INTO `expenses` VALUES ('7', '7', 'Legal Firm', 'Legal consultation fees', '500.00', '2026-08-07', '1', '2026-08-25 12:08:58', '2026-08-25 12:08:58');
INSERT INTO `expenses` VALUES ('8', '8', 'Microsoft', 'Software license subscriptions', '350.00', '2026-08-08', '1', '2026-08-25 12:08:58', '2026-08-25 12:08:58');
INSERT INTO `expenses` VALUES ('9', '1', 'City Properties', 'Office rent payment', '1200.00', '2026-08-09', '1', '2026-08-25 12:08:58', '2026-08-25 12:08:58');
INSERT INTO `expenses` VALUES ('10', '2', 'TelOne', 'Internet and phone bills', '180.00', '2026-08-10', '1', '2026-08-25 12:08:58', '2026-08-25 12:08:58');
INSERT INTO `expenses` VALUES ('11', '3', 'Staff Bonuses', 'Performance bonuses', '1500.00', '2026-08-11', '1', '2026-08-25 12:08:58', '2026-08-25 12:08:58');
INSERT INTO `expenses` VALUES ('12', '4', 'Google Ads', 'Online advertising campaign', '250.00', '2026-08-12', '1', '2026-08-25 12:08:58', '2026-08-25 12:08:58');
INSERT INTO `expenses` VALUES ('13', '6', 'Fuel Station', 'Vehicle fuel', '180.00', '2026-08-13', '1', '2026-08-25 12:08:58', '2026-08-25 12:08:58');
INSERT INTO `expenses` VALUES ('14', '5', 'Office Depot', 'Printer ink and paper', '95.00', '2026-08-14', '1', '2026-08-25 12:08:58', '2026-08-25 12:08:58');
INSERT INTO `expenses` VALUES ('15', '8', 'Adobe', 'Creative cloud subscription', '120.00', '2026-08-15', '1', '2026-08-25 12:08:58', '2026-08-25 12:08:58');
INSERT INTO `expenses` VALUES ('16', '1', 'ABC Properties', 'Monthly office rent for August', '1200.00', '2026-08-01', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `expenses` VALUES ('17', '2', 'ZESA', 'Electricity bill payment', '450.00', '2026-08-02', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `expenses` VALUES ('18', '3', 'Staff Payroll', 'Salary payment for August', '8000.00', '2026-08-03', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `expenses` VALUES ('19', '4', 'Marketing Agency', 'Social media advertising', '300.00', '2026-08-04', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `expenses` VALUES ('20', '5', 'Office Express', 'Stationery and office supplies', '150.00', '2026-08-05', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `expenses` VALUES ('21', '6', 'Total Fuel', 'Fuel for company vehicles', '200.00', '2026-08-06', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `expenses` VALUES ('22', '7', 'Legal Firm', 'Legal consultation fees', '500.00', '2026-08-07', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `expenses` VALUES ('23', '8', 'Microsoft', 'Software license subscriptions', '350.00', '2026-08-08', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `expenses` VALUES ('24', '1', 'City Properties', 'Office rent payment', '1200.00', '2026-08-09', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `expenses` VALUES ('25', '2', 'TelOne', 'Internet and phone bills', '180.00', '2026-08-10', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `expenses` VALUES ('26', '3', 'Staff Bonuses', 'Performance bonuses', '1500.00', '2026-08-11', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `expenses` VALUES ('27', '4', 'Google Ads', 'Online advertising campaign', '250.00', '2026-08-12', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `expenses` VALUES ('28', '6', 'Fuel Station', 'Vehicle fuel', '180.00', '2026-08-13', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `expenses` VALUES ('29', '5', 'Office Depot', 'Printer ink and paper', '95.00', '2026-08-14', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `expenses` VALUES ('30', '8', 'Adobe', 'Creative cloud subscription', '120.00', '2026-08-15', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `expenses` VALUES ('31', '1', 'ABC Properties', 'Monthly office rent for August', '1200.00', '2026-08-01', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `expenses` VALUES ('32', '2', 'ZESA', 'Electricity bill payment', '450.00', '2026-08-02', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `expenses` VALUES ('33', '3', 'Staff Payroll', 'Salary payment for August', '8000.00', '2026-08-03', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `expenses` VALUES ('34', '4', 'Marketing Agency', 'Social media advertising', '300.00', '2026-08-04', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `expenses` VALUES ('35', '5', 'Office Express', 'Stationery and office supplies', '150.00', '2026-08-05', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `expenses` VALUES ('36', '6', 'Total Fuel', 'Fuel for company vehicles', '200.00', '2026-08-06', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `expenses` VALUES ('37', '7', 'Legal Firm', 'Legal consultation fees', '500.00', '2026-08-07', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `expenses` VALUES ('38', '8', 'Microsoft', 'Software license subscriptions', '350.00', '2026-08-08', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `expenses` VALUES ('39', '1', 'City Properties', 'Office rent payment', '1200.00', '2026-08-09', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `expenses` VALUES ('40', '2', 'TelOne', 'Internet and phone bills', '180.00', '2026-08-10', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `expenses` VALUES ('41', '3', 'Staff Bonuses', 'Performance bonuses', '1500.00', '2026-08-11', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `expenses` VALUES ('42', '4', 'Google Ads', 'Online advertising campaign', '250.00', '2026-08-12', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `expenses` VALUES ('43', '6', 'Fuel Station', 'Vehicle fuel', '180.00', '2026-08-13', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `expenses` VALUES ('44', '5', 'Office Depot', 'Printer ink and paper', '95.00', '2026-08-14', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `expenses` VALUES ('45', '8', 'Adobe', 'Creative cloud subscription', '120.00', '2026-08-15', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `expenses` VALUES ('46', '1', 'ABC Properties', 'Monthly office rent for August', '1200.00', '2026-08-01', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `expenses` VALUES ('47', '2', 'ZESA', 'Electricity bill payment', '450.00', '2026-08-02', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `expenses` VALUES ('48', '3', 'Staff Payroll', 'Salary payment for August', '8000.00', '2026-08-03', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `expenses` VALUES ('49', '4', 'Marketing Agency', 'Social media advertising', '300.00', '2026-08-04', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `expenses` VALUES ('50', '5', 'Office Express', 'Stationery and office supplies', '150.00', '2026-08-05', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `expenses` VALUES ('51', '6', 'Total Fuel', 'Fuel for company vehicles', '200.00', '2026-08-06', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `expenses` VALUES ('52', '7', 'Legal Firm', 'Legal consultation fees', '500.00', '2026-08-07', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `expenses` VALUES ('53', '8', 'Microsoft', 'Software license subscriptions', '350.00', '2026-08-08', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `expenses` VALUES ('54', '1', 'City Properties', 'Office rent payment', '1200.00', '2026-08-09', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `expenses` VALUES ('55', '2', 'TelOne', 'Internet and phone bills', '180.00', '2026-08-10', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `expenses` VALUES ('56', '3', 'Staff Bonuses', 'Performance bonuses', '1500.00', '2026-08-11', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `expenses` VALUES ('57', '4', 'Google Ads', 'Online advertising campaign', '250.00', '2026-08-12', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `expenses` VALUES ('58', '6', 'Fuel Station', 'Vehicle fuel', '180.00', '2026-08-13', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `expenses` VALUES ('59', '5', 'Office Depot', 'Printer ink and paper', '95.00', '2026-08-14', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `expenses` VALUES ('60', '8', 'Adobe', 'Creative cloud subscription', '120.00', '2026-08-15', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');

DROP TABLE IF EXISTS `products`;
CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `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(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `product_code` (`product_code`),
  KEY `created_by` (`created_by`),
  KEY `deleted_by` (`deleted_by`),
  KEY `idx_product_name` (`product_name`),
  KEY `idx_is_active` (`is_active`),
  KEY `idx_category` (`category`),
  CONSTRAINT `products_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `products_ibfk_2` FOREIGN KEY (`deleted_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `products` 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 09:12:44', '2026-08-25 09:12:44');
INSERT INTO `products` VALUES ('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 09:12:44', '2026-08-25 09:12:44');
INSERT INTO `products` VALUES ('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 09:12:44', '2026-08-25 09:12:44');
INSERT INTO `products` VALUES ('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 09:12:44', '2026-08-25 09:12:44');
INSERT INTO `products` VALUES ('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 09:12:44', '2026-08-25 09:12:44');
INSERT INTO `products` VALUES ('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 09:12:44', '2026-08-25 09:12:44');

DROP TABLE IF EXISTS `purchase_types`;
CREATE TABLE `purchase_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `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(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `created_by` (`created_by`),
  KEY `deleted_by` (`deleted_by`),
  KEY `idx_name` (`name`),
  KEY `idx_is_active` (`is_active`),
  KEY `idx_purchase_date` (`purchase_date`),
  CONSTRAINT `purchase_types_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `purchase_types_ibfk_2` FOREIGN KEY (`deleted_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `purchase_types` VALUES ('1', 'Electronics Supply', 'Electronics and gadgets supply', '2026-08-25', '1', NULL, NULL, '1', '2026-08-25 09:12:44', '2026-08-25 09:12:44');
INSERT INTO `purchase_types` VALUES ('2', 'Office Furniture', 'Office furniture and equipment', '2026-08-25', '1', NULL, NULL, '1', '2026-08-25 09:12:44', '2026-08-25 09:12:44');
INSERT INTO `purchase_types` VALUES ('3', 'Office Supplies', 'General office supplies', '2026-08-25', '1', NULL, NULL, '1', '2026-08-25 09:12:44', '2026-08-25 09:12:44');

DROP TABLE IF EXISTS `purchases`;
CREATE TABLE `purchases` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `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(),
  PRIMARY KEY (`id`),
  KEY `purchase_type_id` (`purchase_type_id`),
  KEY `product_id` (`product_id`),
  KEY `created_by` (`created_by`),
  KEY `deleted_by` (`deleted_by`),
  KEY `idx_purchase_date` (`purchase_date`),
  KEY `idx_is_active` (`is_active`),
  KEY `idx_purchaser_name` (`purchaser_name`),
  CONSTRAINT `purchases_ibfk_1` FOREIGN KEY (`purchase_type_id`) REFERENCES `purchase_types` (`id`),
  CONSTRAINT `purchases_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
  CONSTRAINT `purchases_ibfk_3` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `purchases_ibfk_4` FOREIGN KEY (`deleted_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `purchases` 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 09:12:44', '2026-08-25 09:12:44');
INSERT INTO `purchases` VALUES ('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 09:12:44', '2026-08-25 09:12:44');
INSERT INTO `purchases` VALUES ('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 09:12:44', '2026-08-25 09:12:44');
INSERT INTO `purchases` VALUES ('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 09:12:44', '2026-08-25 09:12:44');
INSERT INTO `purchases` VALUES ('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 09:12:44', '2026-08-25 09:12:44');

DROP TABLE IF EXISTS `sales`;
CREATE TABLE `sales` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `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,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_sales_record` (`sales_date`,`branch_id`),
  KEY `branch_id` (`branch_id`),
  KEY `created_by` (`created_by`),
  KEY `submitted_by` (`submitted_by`),
  KEY `approved_by` (`approved_by`),
  KEY `idx_sales_date` (`sales_date`),
  KEY `idx_status` (`status`),
  CONSTRAINT `sales_ibfk_1` FOREIGN KEY (`branch_id`) REFERENCES `branch` (`id`),
  CONSTRAINT `sales_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `sales_ibfk_3` FOREIGN KEY (`submitted_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `sales_ibfk_4` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `sales` 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 08:34:47', '2026-08-25 08:34:47', '21000');
INSERT INTO `sales` VALUES ('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 08:35:58', '2026-08-25 08:56:59', '15000');
INSERT INTO `sales` VALUES ('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 11:13:11', '2026-08-25 11:13:11', '15000');
INSERT INTO `sales` VALUES ('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 12:11:11', '2026-08-25 12:11:11', '21000');
INSERT INTO `sales` VALUES ('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 12:11:11', '2026-08-25 12:11:11', '15000');
INSERT INTO `sales` VALUES ('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 12:11:11', '2026-08-25 12:11:11', '21000');
INSERT INTO `sales` VALUES ('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 12:11:11', '2026-08-25 12:11:11', '18000');
INSERT INTO `sales` VALUES ('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 12:11:11', '2026-08-25 12:11:11', '15000');
INSERT INTO `sales` VALUES ('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 12:11:11', '2026-08-25 12:11:11', '21000');
INSERT INTO `sales` VALUES ('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 12:11:11', '2026-08-25 12:11:11', '18000');
INSERT INTO `sales` VALUES ('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 12:11:11', '2026-08-25 12:11:11', '15000');
INSERT INTO `sales` VALUES ('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 12:11:11', '2026-08-25 12:11:11', '21000');
INSERT INTO `sales` VALUES ('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 12:11:11', '2026-08-25 12:11:11', '18000');

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

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

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

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

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

DROP TABLE IF EXISTS `target_cashiers`;
CREATE TABLE `target_cashiers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `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(),
  PRIMARY KEY (`id`),
  KEY `target_id` (`target_id`),
  KEY `cashier_user_id` (`cashier_user_id`),
  CONSTRAINT `target_cashiers_ibfk_1` FOREIGN KEY (`target_id`) REFERENCES `targets` (`id`) ON DELETE CASCADE,
  CONSTRAINT `target_cashiers_ibfk_2` FOREIGN KEY (`cashier_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `target_cashiers` VALUES ('1', '1', 'John Doe', '1', '2026-08-25 12:11:11');
INSERT INTO `target_cashiers` VALUES ('2', '1', 'Jane Smith', '1', '2026-08-25 12:11:11');
INSERT INTO `target_cashiers` VALUES ('3', '1', 'Peter Moyo', '1', '2026-08-25 12:11:11');
INSERT INTO `target_cashiers` VALUES ('4', '2', 'Mary Ncube', '1', '2026-08-25 12:11:11');
INSERT INTO `target_cashiers` VALUES ('5', '2', 'Tendai Chikwanha', '1', '2026-08-25 12:11:11');
INSERT INTO `target_cashiers` VALUES ('6', '3', 'Sarah Ndlovu', '1', '2026-08-25 12:11:11');
INSERT INTO `target_cashiers` VALUES ('7', '3', 'David Moyo', '1', '2026-08-25 12:11:11');
INSERT INTO `target_cashiers` VALUES ('8', '4', 'John Doe', '1', '2026-08-25 12:11:11');
INSERT INTO `target_cashiers` VALUES ('9', '4', 'Jane Smith', '1', '2026-08-25 12:11:11');
INSERT INTO `target_cashiers` VALUES ('10', '5', 'Peter Moyo', '1', '2026-08-25 12:11:11');

DROP TABLE IF EXISTS `target_cost`;
CREATE TABLE `target_cost` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `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(),
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  KEY `idx_branch_name` (`branch_name`),
  KEY `idx_is_active` (`is_active`),
  CONSTRAINT `target_cost_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `target_cost` VALUES ('2', '700.00', 'MAIN BAR', '0.00', '1', '1', '2026-08-25 08:15:51', '2026-08-25 15:05:52');
INSERT INTO `target_cost` VALUES ('3', '500.00', 'VIP BAR', '0.00', '1', '1', '2026-08-25 08:15:51', '2026-08-25 15:06:13');
INSERT INTO `target_cost` VALUES ('4', '500.00', 'COCKTAIL BAR', '0.00', '1', '1', '2026-08-25 08:15:51', '2026-08-25 15:06:26');

DROP TABLE IF EXISTS `targets`;
CREATE TABLE `targets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `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(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_target` (`branch_id`,`target_date`,`is_active`),
  KEY `target_cost_id` (`target_cost_id`),
  KEY `created_by` (`created_by`),
  KEY `deleted_by` (`deleted_by`),
  KEY `idx_target_date` (`target_date`),
  KEY `idx_status` (`status`),
  KEY `idx_is_active` (`is_active`),
  CONSTRAINT `targets_ibfk_1` FOREIGN KEY (`branch_id`) REFERENCES `branch` (`id`),
  CONSTRAINT `targets_ibfk_2` FOREIGN KEY (`target_cost_id`) REFERENCES `target_cost` (`id`),
  CONSTRAINT `targets_ibfk_3` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `targets` VALUES ('1', '1', '2', '2026-08-01', '21000.00', '18500.00', 'approved', '1', NULL, NULL, '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `targets` VALUES ('2', '2', '3', '2026-08-01', '15000.00', '12000.00', 'approved', '1', NULL, NULL, '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `targets` VALUES ('3', '3', '4', '2026-08-02', '18000.00', '16500.00', 'approved', '1', NULL, NULL, '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `targets` VALUES ('4', '1', '2', '2026-08-03', '21000.00', '22000.00', 'approved', '1', NULL, NULL, '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `targets` VALUES ('5', '2', '3', '2026-08-04', '15000.00', '14500.00', 'submitted', '1', NULL, NULL, '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `targets` VALUES ('6', '3', '4', '2026-08-05', '18000.00', '17000.00', 'approved', '1', NULL, NULL, '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `targets` VALUES ('7', '1', '2', '2026-08-06', '21000.00', '19500.00', 'approved', '1', NULL, NULL, '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `targets` VALUES ('8', '2', '3', '2026-08-07', '15000.00', '15500.00', 'draft', '1', NULL, NULL, '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `targets` VALUES ('9', '3', '4', '2026-08-08', '18000.00', '19000.00', 'approved', '1', NULL, NULL, '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `targets` VALUES ('10', '1', '2', '2026-08-09', '21000.00', '20500.00', 'approved', '1', NULL, NULL, '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');

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

INSERT INTO `users` VALUES ('1', 'admin', 'admin@accountsystem.com', '$2y$10$UMUHB/OwoM.dUgahf8yqJOVI88TvlOEZl57WLYNTqwlyrUrJ6ihP.', 'System Administrator', NULL, 'admin', NULL, '2026-08-25 20:11:11', '1', '2026-06-26 09:01:51', '2026-08-25 20:11:11');

DROP TABLE IF EXISTS `withdrawal_types`;
CREATE TABLE `withdrawal_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `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(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `created_by` (`created_by`),
  KEY `idx_is_active` (`is_active`),
  CONSTRAINT `withdrawal_types_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `withdrawal_types` VALUES ('1', 'Loan Disbursement', 'Loan disbursement to clients', '1', '1', '1', '2026-08-25 08:07:08', '2026-08-25 08:07:08');
INSERT INTO `withdrawal_types` VALUES ('2', 'Operating Expense', 'General business operating expenses', '1', '1', '1', '2026-08-25 08:07:08', '2026-08-25 08:07:08');
INSERT INTO `withdrawal_types` VALUES ('3', 'Capital Withdrawal', 'Capital withdrawal or owner drawings', '1', '1', '1', '2026-08-25 08:07:08', '2026-08-25 08:07:08');
INSERT INTO `withdrawal_types` VALUES ('4', 'Supplier Payment', 'Payments to suppliers and vendors', '1', '0', '1', '2026-08-25 08:07:08', '2026-08-25 08:07:08');
INSERT INTO `withdrawal_types` VALUES ('5', 'SAVINGS', 'To the company's wallet', '1', '0', '1', '2026-08-25 08:07:08', '2026-08-25 15:19:16');
INSERT INTO `withdrawal_types` VALUES ('6', 'Tax Payment', 'Tax and regulatory payments', '1', '0', '1', '2026-08-25 08:07:08', '2026-08-25 08:07:08');

DROP TABLE IF EXISTS `withdrawals`;
CREATE TABLE `withdrawals` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `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(),
  PRIMARY KEY (`id`),
  KEY `withdrawal_type_id` (`withdrawal_type_id`),
  KEY `recorded_by` (`recorded_by`),
  KEY `idx_withdrawal_date` (`withdrawal_date`),
  KEY `idx_full_name` (`full_name`),
  KEY `branch_id` (`branch_id`),
  CONSTRAINT `withdrawals_ibfk_1` FOREIGN KEY (`withdrawal_type_id`) REFERENCES `withdrawal_types` (`id`),
  CONSTRAINT `withdrawals_ibfk_2` FOREIGN KEY (`recorded_by`) REFERENCES `users` (`id`),
  CONSTRAINT `withdrawals_ibfk_7` FOREIGN KEY (`branch_id`) REFERENCES `branch` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=73 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `withdrawals` VALUES ('1', '1', '1', 'Tendai Chikwanha', 'Personal loan disbursement', '10000.00', '2026-08-20', '1', '2026-08-25 08:07:08', '2026-08-25 08:07:08');
INSERT INTO `withdrawals` VALUES ('2', '2', '2', 'Office Supplies Ltd', 'Purchase of office furniture and supplies', '3500.00', '2026-08-20', '1', '2026-08-25 08:07:08', '2026-08-25 08:07:08');
INSERT INTO `withdrawals` VALUES ('3', '3', '3', 'Owner Drawings', 'Owner drawings for personal use', '2000.00', '2026-08-21', '1', '2026-08-25 08:07:08', '2026-08-25 08:07:08');
INSERT INTO `withdrawals` VALUES ('4', '4', '1', 'ABC Suppliers', 'Payment for inventory supplies', '4500.00', '2026-08-21', '1', '2026-08-25 08:07:08', '2026-08-25 08:07:08');
INSERT INTO `withdrawals` VALUES ('5', '5', '2', 'Petty Cash', 'Office petty cash replenishment', '500.00', '2026-08-22', '1', '2026-08-25 08:07:08', '2026-08-25 08:07:08');
INSERT INTO `withdrawals` VALUES ('6', '6', '3', 'ZIMRA', 'Tax payment for the month', '2500.00', '2026-08-22', '1', '2026-08-25 08:07:08', '2026-08-25 08:07:08');
INSERT INTO `withdrawals` VALUES ('7', '1', '1', 'Tendai Chikwanha', 'Personal loan disbursement', '10000.00', '2026-08-24', '1', '2026-08-25 08:15:51', '2026-08-25 08:15:51');
INSERT INTO `withdrawals` VALUES ('8', '2', '2', 'Office Supplies Ltd', 'Purchase of office furniture', '3500.00', '2026-08-24', '1', '2026-08-25 08:15:51', '2026-08-25 08:15:51');
INSERT INTO `withdrawals` VALUES ('9', '3', '3', 'Owner Drawings', 'Owner drawings for personal use', '2000.00', '2026-08-24', '1', '2026-08-25 08:15:51', '2026-08-25 08:15:51');
INSERT INTO `withdrawals` VALUES ('10', '4', '1', 'ABC Suppliers', 'Payment for inventory supplies', '4500.00', '2026-08-25', '1', '2026-08-25 08:15:51', '2026-08-25 08:26:37');
INSERT INTO `withdrawals` VALUES ('11', '5', '2', 'Petty Cash', 'Office petty cash replenishment', '500.00', '2026-08-25', '1', '2026-08-25 08:15:51', '2026-08-25 08:26:26');
INSERT INTO `withdrawals` VALUES ('12', '6', '3', 'ZIMRA', 'Tax payment for the month', '2500.00', '2026-08-25', '1', '2026-08-25 08:15:51', '2026-08-25 08:26:18');
INSERT INTO `withdrawals` VALUES ('13', '1', '1', 'Tendai Chikwanha', 'Personal loan disbursement', '10000.00', '2026-08-01', '1', '2026-08-25 12:08:58', '2026-08-25 12:08:58');
INSERT INTO `withdrawals` VALUES ('14', '2', '2', 'Office Supplies Ltd', 'Purchase of office furniture', '3500.00', '2026-08-02', '1', '2026-08-25 12:08:58', '2026-08-25 12:08:58');
INSERT INTO `withdrawals` VALUES ('15', '3', '3', 'Owner Drawings', 'Owner drawings for personal use', '2000.00', '2026-08-03', '1', '2026-08-25 12:08:58', '2026-08-25 12:08:58');
INSERT INTO `withdrawals` VALUES ('16', '4', '1', 'ABC Suppliers', 'Payment for inventory supplies', '4500.00', '2026-08-04', '1', '2026-08-25 12:08:58', '2026-08-25 12:08:58');
INSERT INTO `withdrawals` VALUES ('17', '5', '2', 'Petty Cash', 'Office petty cash replenishment', '500.00', '2026-08-05', '1', '2026-08-25 12:08:58', '2026-08-25 12:08:58');
INSERT INTO `withdrawals` VALUES ('18', '6', '3', 'ZIMRA', 'Tax payment for the month', '2500.00', '2026-08-06', '1', '2026-08-25 12:08:58', '2026-08-25 12:08:58');
INSERT INTO `withdrawals` VALUES ('19', '2', '1', 'Salaries Payroll', 'Monthly staff salaries', '8000.00', '2026-08-07', '1', '2026-08-25 12:08:58', '2026-08-25 12:08:58');
INSERT INTO `withdrawals` VALUES ('20', '1', '2', 'Mary Ncube', 'Business loan disbursement', '5000.00', '2026-08-08', '1', '2026-08-25 12:08:58', '2026-08-25 12:08:58');
INSERT INTO `withdrawals` VALUES ('21', '4', '3', 'Global Supplies', 'Office equipment purchase', '3200.00', '2026-08-09', '1', '2026-08-25 12:08:58', '2026-08-25 12:08:58');
INSERT INTO `withdrawals` VALUES ('22', '2', '1', 'Internet & Phone', 'Monthly utility bills', '450.00', '2026-08-10', '1', '2026-08-25 12:08:58', '2026-08-25 12:08:58');
INSERT INTO `withdrawals` VALUES ('23', '3', '2', 'Capital Withdrawal', 'Partner drawings', '1500.00', '2026-08-11', '1', '2026-08-25 12:08:58', '2026-08-25 12:08:58');
INSERT INTO `withdrawals` VALUES ('24', '6', '1', 'NSSA', 'NSSA contributions', '1200.00', '2026-08-12', '1', '2026-08-25 12:08:58', '2026-08-25 12:08:58');
INSERT INTO `withdrawals` VALUES ('25', '5', '3', 'Petty Cash', 'Office supplies', '300.00', '2026-08-13', '1', '2026-08-25 12:08:58', '2026-08-25 12:08:58');
INSERT INTO `withdrawals` VALUES ('26', '1', '2', 'John Smith', 'Car loan disbursement', '7500.00', '2026-08-14', '1', '2026-08-25 12:08:58', '2026-08-25 12:08:58');
INSERT INTO `withdrawals` VALUES ('27', '4', '1', 'Tech Solutions', 'Software license renewal', '2500.00', '2026-08-15', '1', '2026-08-25 12:08:58', '2026-08-25 12:08:58');
INSERT INTO `withdrawals` VALUES ('28', '1', '1', 'Tendai Chikwanha', 'Personal loan disbursement', '10000.00', '2026-08-01', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `withdrawals` VALUES ('29', '2', '2', 'Office Supplies Ltd', 'Purchase of office furniture', '3500.00', '2026-08-02', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `withdrawals` VALUES ('30', '3', '3', 'Owner Drawings', 'Owner drawings for personal use', '2000.00', '2026-08-03', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `withdrawals` VALUES ('31', '4', '1', 'ABC Suppliers', 'Payment for inventory supplies', '4500.00', '2026-08-04', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `withdrawals` VALUES ('32', '5', '2', 'Petty Cash', 'Office petty cash replenishment', '500.00', '2026-08-05', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `withdrawals` VALUES ('33', '6', '3', 'ZIMRA', 'Tax payment for the month', '2500.00', '2026-08-06', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `withdrawals` VALUES ('34', '2', '1', 'Salaries Payroll', 'Monthly staff salaries', '8000.00', '2026-08-07', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `withdrawals` VALUES ('35', '1', '2', 'Mary Ncube', 'Business loan disbursement', '5000.00', '2026-08-08', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `withdrawals` VALUES ('36', '4', '3', 'Global Supplies', 'Office equipment purchase', '3200.00', '2026-08-09', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `withdrawals` VALUES ('37', '2', '1', 'Internet & Phone', 'Monthly utility bills', '450.00', '2026-08-10', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `withdrawals` VALUES ('38', '3', '2', 'Capital Withdrawal', 'Partner drawings', '1500.00', '2026-08-11', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `withdrawals` VALUES ('39', '6', '1', 'NSSA', 'NSSA contributions', '1200.00', '2026-08-12', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `withdrawals` VALUES ('40', '5', '3', 'Petty Cash', 'Office supplies', '300.00', '2026-08-13', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `withdrawals` VALUES ('41', '1', '2', 'John Smith', 'Car loan disbursement', '7500.00', '2026-08-14', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `withdrawals` VALUES ('42', '4', '1', 'Tech Solutions', 'Software license renewal', '2500.00', '2026-08-15', '1', '2026-08-25 12:09:48', '2026-08-25 12:09:48');
INSERT INTO `withdrawals` VALUES ('43', '1', '1', 'Tendai Chikwanha', 'Personal loan disbursement', '10000.00', '2026-08-01', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `withdrawals` VALUES ('44', '2', '2', 'Office Supplies Ltd', 'Purchase of office furniture', '3500.00', '2026-08-02', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `withdrawals` VALUES ('45', '3', '3', 'Owner Drawings', 'Owner drawings for personal use', '2000.00', '2026-08-03', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `withdrawals` VALUES ('46', '4', '1', 'ABC Suppliers', 'Payment for inventory supplies', '4500.00', '2026-08-04', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `withdrawals` VALUES ('47', '5', '2', 'Petty Cash', 'Office petty cash replenishment', '500.00', '2026-08-05', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `withdrawals` VALUES ('48', '6', '3', 'ZIMRA', 'Tax payment for the month', '2500.00', '2026-08-06', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `withdrawals` VALUES ('49', '2', '1', 'Salaries Payroll', 'Monthly staff salaries', '8000.00', '2026-08-07', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `withdrawals` VALUES ('50', '1', '2', 'Mary Ncube', 'Business loan disbursement', '5000.00', '2026-08-08', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `withdrawals` VALUES ('51', '4', '3', 'Global Supplies', 'Office equipment purchase', '3200.00', '2026-08-09', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `withdrawals` VALUES ('52', '2', '1', 'Internet & Phone', 'Monthly utility bills', '450.00', '2026-08-10', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `withdrawals` VALUES ('53', '3', '2', 'Capital Withdrawal', 'Partner drawings', '1500.00', '2026-08-11', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `withdrawals` VALUES ('54', '6', '1', 'NSSA', 'NSSA contributions', '1200.00', '2026-08-12', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `withdrawals` VALUES ('55', '5', '3', 'Petty Cash', 'Office supplies', '300.00', '2026-08-13', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `withdrawals` VALUES ('56', '1', '2', 'John Smith', 'Car loan disbursement', '7500.00', '2026-08-14', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `withdrawals` VALUES ('57', '4', '1', 'Tech Solutions', 'Software license renewal', '2500.00', '2026-08-15', '1', '2026-08-25 12:10:48', '2026-08-25 12:10:48');
INSERT INTO `withdrawals` VALUES ('58', '1', '1', 'Tendai Chikwanha', 'Personal loan disbursement', '10000.00', '2026-08-01', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `withdrawals` VALUES ('59', '2', '2', 'Office Supplies Ltd', 'Purchase of office furniture', '3500.00', '2026-08-02', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `withdrawals` VALUES ('60', '3', '3', 'Owner Drawings', 'Owner drawings for personal use', '2000.00', '2026-08-03', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `withdrawals` VALUES ('61', '4', '1', 'ABC Suppliers', 'Payment for inventory supplies', '4500.00', '2026-08-04', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `withdrawals` VALUES ('62', '5', '2', 'Petty Cash', 'Office petty cash replenishment', '500.00', '2026-08-05', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `withdrawals` VALUES ('63', '6', '3', 'ZIMRA', 'Tax payment for the month', '2500.00', '2026-08-06', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `withdrawals` VALUES ('64', '2', '1', 'Salaries Payroll', 'Monthly staff salaries', '8000.00', '2026-08-07', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `withdrawals` VALUES ('65', '1', '2', 'Mary Ncube', 'Business loan disbursement', '5000.00', '2026-08-08', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `withdrawals` VALUES ('66', '4', '3', 'Global Supplies', 'Office equipment purchase', '3200.00', '2026-08-09', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `withdrawals` VALUES ('67', '2', '1', 'Internet & Phone', 'Monthly utility bills', '450.00', '2026-08-10', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `withdrawals` VALUES ('68', '3', '2', 'Capital Withdrawal', 'Partner drawings', '1500.00', '2026-08-11', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `withdrawals` VALUES ('69', '6', '1', 'NSSA', 'NSSA contributions', '1200.00', '2026-08-12', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `withdrawals` VALUES ('70', '5', '3', 'Petty Cash', 'Office supplies', '300.00', '2026-08-13', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `withdrawals` VALUES ('71', '1', '2', 'John Smith', 'Car loan disbursement', '7500.00', '2026-08-14', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');
INSERT INTO `withdrawals` VALUES ('72', '4', '1', 'Tech Solutions', 'Software license renewal', '2500.00', '2026-08-15', '1', '2026-08-25 12:11:11', '2026-08-25 12:11:11');