/*M!999999\- enable the sandbox mode */ -- MariaDB dump 10.19-11.4.10-MariaDB, for Linux (x86_64) -- -- Host: localhost Database: littrea1_nelqua-erp -- ------------------------------------------------------ -- Server version 11.4.10-MariaDB /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*M!100616 SET @OLD_NOTE_VERBOSITY=@@NOTE_VERBOSITY, NOTE_VERBOSITY=0 */; -- -- Table structure for table `account_categories` -- DROP TABLE IF EXISTS `account_categories`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `account_categories` ( `id` int(11) NOT NULL AUTO_INCREMENT, `category_name` varchar(80) NOT NULL, `statement_type` enum('BALANCE_SHEET','PROFIT_LOSS','CASH_FLOW') NOT NULL, `normal_balance` enum('DEBIT','CREDIT') NOT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `category_name` (`category_name`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `account_categories` -- LOCK TABLES `account_categories` WRITE; /*!40000 ALTER TABLE `account_categories` DISABLE KEYS */; INSERT INTO `account_categories` (`id`, `category_name`, `statement_type`, `normal_balance`, `created_at`) VALUES (1,'Assets','BALANCE_SHEET','DEBIT','2026-06-21 14:58:58'), (2,'Liabilities','BALANCE_SHEET','CREDIT','2026-06-21 14:58:58'), (3,'Equity','BALANCE_SHEET','CREDIT','2026-06-21 14:58:58'), (4,'Revenue','PROFIT_LOSS','CREDIT','2026-06-21 14:58:58'), (5,'Cost of Sales','PROFIT_LOSS','DEBIT','2026-06-21 14:58:58'), (6,'Expenses','PROFIT_LOSS','DEBIT','2026-06-21 14:58:58'), (7,'Other Income','PROFIT_LOSS','CREDIT','2026-06-21 14:58:58'); /*!40000 ALTER TABLE `account_categories` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `approvals` -- DROP TABLE IF EXISTS `approvals`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `approvals` ( `id` int(11) NOT NULL AUTO_INCREMENT, `module_name` varchar(80) NOT NULL, `record_id` int(11) NOT NULL, `action_type` enum('EDIT','DELETE','APPROVE','REJECT') NOT NULL, `old_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`old_data`)), `new_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`new_data`)), `requested_by` int(11) NOT NULL, `approved_by` int(11) DEFAULT NULL, `status` enum('PENDING','APPROVED','REJECTED') DEFAULT 'PENDING', `requested_at` timestamp NULL DEFAULT current_timestamp(), `decided_at` datetime DEFAULT NULL, `remarks` text DEFAULT NULL, PRIMARY KEY (`id`), KEY `requested_by` (`requested_by`), KEY `approved_by` (`approved_by`), CONSTRAINT `approvals_ibfk_1` FOREIGN KEY (`requested_by`) REFERENCES `users` (`id`), CONSTRAINT `approvals_ibfk_2` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `approvals` -- LOCK TABLES `approvals` WRITE; /*!40000 ALTER TABLE `approvals` DISABLE KEYS */; /*!40000 ALTER TABLE `approvals` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `asset_register` -- DROP TABLE IF EXISTS `asset_register`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `asset_register` ( `id` int(11) NOT NULL AUTO_INCREMENT, `asset_code` varchar(50) DEFAULT NULL, `asset_name` varchar(180) NOT NULL, `supplier_id` int(11) DEFAULT NULL, `purchase_id` int(11) DEFAULT NULL, `purchase_item_id` int(11) DEFAULT NULL, `acquisition_date` date NOT NULL, `acquisition_cost` decimal(12,2) NOT NULL DEFAULT 0.00, `asset_category` varchar(100) DEFAULT NULL, `location` varchar(150) DEFAULT NULL, `custodian` varchar(120) DEFAULT NULL, `status` enum('ACTIVE','DISPOSED','LOST','DAMAGED','INACTIVE') DEFAULT 'ACTIVE', `notes` text DEFAULT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `asset_code` (`asset_code`), KEY `supplier_id` (`supplier_id`), KEY `purchase_id` (`purchase_id`), KEY `asset_category` (`asset_category`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `asset_register` -- LOCK TABLES `asset_register` WRITE; /*!40000 ALTER TABLE `asset_register` DISABLE KEYS */; /*!40000 ALTER TABLE `asset_register` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `assets` -- DROP TABLE IF EXISTS `assets`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `assets` ( `id` int(11) NOT NULL AUTO_INCREMENT, `asset_code` varchar(60) DEFAULT NULL, `asset_name` varchar(180) NOT NULL, `purchase_date` date DEFAULT NULL, `purchase_value` decimal(12,2) DEFAULT 0.00, `current_value` decimal(12,2) DEFAULT 0.00, `payment_mode` enum('CASH','MPESA','BANK','CREDIT') DEFAULT 'CASH', `status` enum('ACTIVE','DISPOSED','LOST','DAMAGED') DEFAULT 'ACTIVE', `created_by` int(11) NOT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `asset_code` (`asset_code`), KEY `created_by` (`created_by`), CONSTRAINT `assets_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `assets` -- LOCK TABLES `assets` WRITE; /*!40000 ALTER TABLE `assets` DISABLE KEYS */; /*!40000 ALTER TABLE `assets` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `audit_logs` -- DROP TABLE IF EXISTS `audit_logs`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `audit_logs` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) DEFAULT NULL, `action` varchar(120) NOT NULL, `module_name` varchar(80) DEFAULT NULL, `record_id` int(11) DEFAULT NULL, `description` text DEFAULT NULL, `ip_address` varchar(45) DEFAULT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `user_id` (`user_id`), CONSTRAINT `audit_logs_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `audit_logs` -- LOCK TABLES `audit_logs` WRITE; /*!40000 ALTER TABLE `audit_logs` DISABLE KEYS */; /*!40000 ALTER TABLE `audit_logs` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `banking_register` -- DROP TABLE IF EXISTS `banking_register`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `banking_register` ( `id` int(11) NOT NULL AUTO_INCREMENT, `till_session_id` int(11) DEFAULT NULL, `business_date` date NOT NULL, `banking_type` enum('CASH','MPESA','BANK','OTHER') NOT NULL, `amount_expected` decimal(12,2) NOT NULL DEFAULT 0.00, `amount_banked` decimal(12,2) NOT NULL DEFAULT 0.00, `charges` decimal(12,2) NOT NULL DEFAULT 0.00, `net_banked` decimal(12,2) NOT NULL DEFAULT 0.00, `variance` decimal(12,2) NOT NULL DEFAULT 0.00, `bank_reference` varchar(100) DEFAULT NULL, `remarks` text DEFAULT NULL, `status` enum('PENDING','BANKED','RECONCILED') DEFAULT 'PENDING', `created_by` int(11) DEFAULT NULL, `approved_by` int(11) DEFAULT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), `approved_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `till_session_id` (`till_session_id`), KEY `business_date` (`business_date`), KEY `banking_type` (`banking_type`), KEY `status` (`status`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `banking_register` -- LOCK TABLES `banking_register` WRITE; /*!40000 ALTER TABLE `banking_register` DISABLE KEYS */; /*!40000 ALTER TABLE `banking_register` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `branches` -- DROP TABLE IF EXISTS `branches`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `branches` ( `id` int(11) NOT NULL AUTO_INCREMENT, `branch_name` varchar(120) NOT NULL, `location` varchar(255) DEFAULT NULL, `phone` varchar(30) DEFAULT NULL, `status` enum('ACTIVE','INACTIVE') DEFAULT 'ACTIVE', `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `branches` -- LOCK TABLES `branches` WRITE; /*!40000 ALTER TABLE `branches` DISABLE KEYS */; INSERT INTO `branches` (`id`, `branch_name`, `location`, `phone`, `status`, `created_at`) VALUES (1,'Main Branch','Main shop','','ACTIVE','2026-06-14 07:39:02'); /*!40000 ALTER TABLE `branches` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `brands` -- DROP TABLE IF EXISTS `brands`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `brands` ( `id` int(11) NOT NULL AUTO_INCREMENT, `category_id` int(11) NOT NULL, `brand_name` varchar(100) NOT NULL, `status` varchar(20) DEFAULT 'ACTIVE', `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `unique_category_brand` (`category_id`,`brand_name`), KEY `idx_category_id` (`category_id`) ) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `brands` -- LOCK TABLES `brands` WRITE; /*!40000 ALTER TABLE `brands` DISABLE KEYS */; INSERT INTO `brands` (`id`, `category_id`, `brand_name`, `status`, `created_at`) VALUES (1,2,'Pro Gas','ACTIVE','2026-06-14 16:02:05'), (2,2,'Afrigas','ACTIVE','2026-06-14 16:02:05'), (3,2,'Total','ACTIVE','2026-06-14 16:02:05'), (4,2,'Hashi','ACTIVE','2026-06-14 16:02:05'), (5,2,'K-Gas','ACTIVE','2026-06-14 16:02:05'), (6,2,'Supa Gas','ACTIVE','2026-06-14 16:02:05'), (7,2,'Mpishi Gas','ACTIVE','2026-06-14 16:02:05'), (8,2,'Taifa Gas','ACTIVE','2026-06-14 16:02:05'), (9,2,'Sea Gas','ACTIVE','2026-06-14 16:02:05'), (10,2,'Lake Gas','ACTIVE','2026-06-14 16:02:05'), (11,2,'Gulf Petrochem','ACTIVE','2026-06-14 16:02:05'), (12,12,'Woyez','ACTIVE','2026-06-14 16:02:05'), (13,12,'Aqua Bliss','ACTIVE','2026-06-14 16:02:05'), (14,12,'Other Brands','ACTIVE','2026-06-14 16:02:05'), (15,3,'Primax','ACTIVE','2026-06-17 17:05:15'), (16,3,'O Gaz','ACTIVE','2026-06-17 17:05:15'), (17,3,'Pine','ACTIVE','2026-06-17 17:05:15'), (18,3,'Lite Gas','ACTIVE','2026-06-17 17:05:15'), (19,3,'Hade Gaz','ACTIVE','2026-06-17 17:05:15'), (20,3,'HR','ACTIVE','2026-06-17 17:05:15'), (21,14,'Fine Line','ACTIVE','2026-06-18 18:09:33'), (22,14,'Other Brands','ACTIVE','2026-06-18 18:09:33'), (23,14,'Pro Gas','ACTIVE','2026-06-18 18:09:33'), (24,14,'Afrigas','ACTIVE','2026-06-18 18:09:33'), (25,14,'Total','ACTIVE','2026-06-18 18:09:33'), (26,14,'Hashi','ACTIVE','2026-06-18 18:09:33'), (27,14,'K-Gas','ACTIVE','2026-06-18 18:09:33'), (28,14,'Supa Gas','ACTIVE','2026-06-18 18:09:33'), (29,14,'Mpishi Gas','ACTIVE','2026-06-18 18:09:33'), (30,14,'Taifa Gas','ACTIVE','2026-06-18 18:09:33'), (31,14,'Sea Gas','ACTIVE','2026-06-18 18:09:33'), (32,14,'Lake Gas','ACTIVE','2026-06-18 18:09:33'), (33,14,'Gulf Petrochem','ACTIVE','2026-06-18 18:09:33'); /*!40000 ALTER TABLE `brands` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `cash_reconciliation` -- DROP TABLE IF EXISTS `cash_reconciliation`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `cash_reconciliation` ( `id` int(11) NOT NULL AUTO_INCREMENT, `till_session_id` int(11) DEFAULT NULL, `business_date` date NOT NULL, `expected_cash` decimal(12,2) NOT NULL DEFAULT 0.00, `actual_cash` decimal(12,2) NOT NULL DEFAULT 0.00, `expected_mpesa` decimal(12,2) NOT NULL DEFAULT 0.00, `actual_mpesa` decimal(12,2) NOT NULL DEFAULT 0.00, `expected_bank` decimal(12,2) NOT NULL DEFAULT 0.00, `actual_bank` decimal(12,2) NOT NULL DEFAULT 0.00, `cash_variance` decimal(12,2) NOT NULL DEFAULT 0.00, `mpesa_variance` decimal(12,2) NOT NULL DEFAULT 0.00, `bank_variance` decimal(12,2) NOT NULL DEFAULT 0.00, `reason` text DEFAULT NULL, `status` enum('PENDING','APPROVED','REJECTED') DEFAULT 'PENDING', `reconciled_by` int(11) DEFAULT NULL, `approved_by` int(11) DEFAULT NULL, `reconciled_at` timestamp NULL DEFAULT current_timestamp(), `approved_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `till_session_id` (`till_session_id`), KEY `business_date` (`business_date`), KEY `status` (`status`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `cash_reconciliation` -- LOCK TABLES `cash_reconciliation` WRITE; /*!40000 ALTER TABLE `cash_reconciliation` DISABLE KEYS */; /*!40000 ALTER TABLE `cash_reconciliation` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `cashbook` -- DROP TABLE IF EXISTS `cashbook`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `cashbook` ( `id` int(11) NOT NULL AUTO_INCREMENT, `transaction_date` datetime NOT NULL, `account_type` enum('CASH','MPESA','BANK') NOT NULL, `transaction_type` enum('IN','OUT') NOT NULL, `source_module` varchar(80) DEFAULT NULL, `reference_id` int(11) DEFAULT NULL, `description` text DEFAULT NULL, `amount` decimal(12,2) NOT NULL, `balance_after` decimal(12,2) DEFAULT 0.00, `created_by` int(11) NOT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `created_by` (`created_by`), KEY `idx_cashbook_date` (`transaction_date`), KEY `idx_cashbook_account` (`account_type`), CONSTRAINT `cashbook_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `cashbook` -- LOCK TABLES `cashbook` WRITE; /*!40000 ALTER TABLE `cashbook` DISABLE KEYS */; /*!40000 ALTER TABLE `cashbook` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `categories` -- DROP TABLE IF EXISTS `categories`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `categories` ( `id` int(11) NOT NULL AUTO_INCREMENT, `category_name` varchar(100) NOT NULL, `description` text DEFAULT NULL, `status` enum('ACTIVE','INACTIVE') DEFAULT 'ACTIVE', `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `category_name` (`category_name`) ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `categories` -- LOCK TABLES `categories` WRITE; /*!40000 ALTER TABLE `categories` DISABLE KEYS */; INSERT INTO `categories` (`id`, `category_name`, `description`, `status`, `created_at`) VALUES (1,'Water Refill',NULL,'ACTIVE','2026-06-14 07:39:02'), (2,'Gas',NULL,'ACTIVE','2026-06-14 07:39:02'), (3,'Accessories',NULL,'ACTIVE','2026-06-14 07:39:02'), (11,'Tap Water',NULL,'ACTIVE','2026-06-14 14:34:57'), (12,'Bottled Water',NULL,'ACTIVE','2026-06-14 14:34:57'), (13,'Packaging Materials',NULL,'ACTIVE','2026-06-14 14:34:57'), (14,'Containers',NULL,'ACTIVE','2026-06-14 14:34:57'); /*!40000 ALTER TABLE `categories` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `chart_of_accounts` -- DROP TABLE IF EXISTS `chart_of_accounts`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `chart_of_accounts` ( `id` int(11) NOT NULL AUTO_INCREMENT, `account_code` varchar(30) NOT NULL, `account_name` varchar(150) NOT NULL, `category_id` int(11) NOT NULL, `account_type` varchar(80) DEFAULT NULL, `is_control_account` tinyint(1) DEFAULT 0, `status` enum('ACTIVE','INACTIVE') DEFAULT 'ACTIVE', `created_at` timestamp NULL DEFAULT current_timestamp(), `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `account_code` (`account_code`), KEY `category_id` (`category_id`), KEY `account_code_2` (`account_code`), KEY `status` (`status`) ) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `chart_of_accounts` -- LOCK TABLES `chart_of_accounts` WRITE; /*!40000 ALTER TABLE `chart_of_accounts` DISABLE KEYS */; INSERT INTO `chart_of_accounts` (`id`, `account_code`, `account_name`, `category_id`, `account_type`, `is_control_account`, `status`, `created_at`, `updated_at`) VALUES (1,'1000','Cash / Till',1,'Current Asset',1,'ACTIVE','2026-06-21 14:58:58',NULL), (2,'1010','M-Pesa / Mobile Money',1,'Current Asset',1,'ACTIVE','2026-06-21 14:58:58',NULL), (3,'1020','Bank Account',1,'Current Asset',1,'ACTIVE','2026-06-21 14:58:58',NULL), (4,'1100','Accounts Receivable / Customer Balances',1,'Current Asset',1,'ACTIVE','2026-06-21 14:58:58',NULL), (5,'1200','Inventory / Stock',1,'Current Asset',1,'ACTIVE','2026-06-21 14:58:58',NULL), (6,'1300','Fixed Assets',1,'Non-Current Asset',1,'ACTIVE','2026-06-21 14:58:58',NULL), (7,'2000','Accounts Payable / Supplier Balances',2,'Current Liability',1,'ACTIVE','2026-06-21 14:58:58',NULL), (8,'3000','Owner Capital',3,'Equity',1,'ACTIVE','2026-06-21 14:58:58',NULL), (9,'3100','Retained Earnings',3,'Equity',1,'ACTIVE','2026-06-21 14:58:58',NULL), (10,'4000','Sales Revenue',4,'Revenue',1,'ACTIVE','2026-06-21 14:58:58',NULL), (11,'5000','Cost of Goods Sold',5,'Cost of Sales',1,'ACTIVE','2026-06-21 14:58:58',NULL), (12,'6000','General Expenses',6,'Operating Expense',1,'ACTIVE','2026-06-21 14:58:58',NULL), (13,'6010','Rent Expense',6,'Operating Expense',0,'ACTIVE','2026-06-21 14:58:58',NULL), (14,'6020','Transport Expense',6,'Operating Expense',0,'ACTIVE','2026-06-21 14:58:58',NULL), (15,'6030','Utilities Expense',6,'Operating Expense',0,'ACTIVE','2026-06-21 14:58:58',NULL), (16,'6040','Repairs and Maintenance',6,'Operating Expense',0,'ACTIVE','2026-06-21 14:58:58',NULL), (17,'6050','Internet and Communication',6,'Operating Expense',0,'ACTIVE','2026-06-21 14:58:58',NULL); /*!40000 ALTER TABLE `chart_of_accounts` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `creditor_payments` -- DROP TABLE IF EXISTS `creditor_payments`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `creditor_payments` ( `id` int(11) NOT NULL AUTO_INCREMENT, `creditor_id` int(11) NOT NULL, `amount` decimal(12,2) NOT NULL, `payment_mode` enum('CASH','MPESA','BANK') NOT NULL, `reference_no` varchar(100) DEFAULT NULL, `payment_date` date NOT NULL, `paid_by` int(11) NOT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `creditor_id` (`creditor_id`), KEY `paid_by` (`paid_by`), CONSTRAINT `creditor_payments_ibfk_1` FOREIGN KEY (`creditor_id`) REFERENCES `creditors` (`id`), CONSTRAINT `creditor_payments_ibfk_2` FOREIGN KEY (`paid_by`) REFERENCES `users` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `creditor_payments` -- LOCK TABLES `creditor_payments` WRITE; /*!40000 ALTER TABLE `creditor_payments` DISABLE KEYS */; /*!40000 ALTER TABLE `creditor_payments` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `creditors` -- DROP TABLE IF EXISTS `creditors`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `creditors` ( `id` int(11) NOT NULL AUTO_INCREMENT, `supplier_id` int(11) DEFAULT NULL, `purchase_id` int(11) DEFAULT NULL, `creditor_name` varchar(150) DEFAULT NULL, `amount` decimal(12,2) NOT NULL, `balance` decimal(12,2) NOT NULL, `due_date` date DEFAULT NULL, `status` enum('OPEN','PARTIAL','PAID','OVERDUE') DEFAULT 'OPEN', `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `supplier_id` (`supplier_id`), KEY `purchase_id` (`purchase_id`), CONSTRAINT `creditors_ibfk_1` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`), CONSTRAINT `creditors_ibfk_2` FOREIGN KEY (`purchase_id`) REFERENCES `purchases` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `creditors` -- LOCK TABLES `creditors` WRITE; /*!40000 ALTER TABLE `creditors` DISABLE KEYS */; /*!40000 ALTER TABLE `creditors` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `crm_settings` -- DROP TABLE IF EXISTS `crm_settings`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `crm_settings` ( `id` int(11) NOT NULL AUTO_INCREMENT, `setting_key` varchar(120) NOT NULL, `setting_value` varchar(255) DEFAULT NULL, `description` varchar(255) DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `setting_key` (`setting_key`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `crm_settings` -- LOCK TABLES `crm_settings` WRITE; /*!40000 ALTER TABLE `crm_settings` DISABLE KEYS */; INSERT INTO `crm_settings` (`id`, `setting_key`, `setting_value`, `description`, `updated_at`) VALUES (1,'crm_enabled','YES','Enable CRM module',NULL), (2,'default_ticket_priority','MEDIUM','Default service ticket priority',NULL), (3,'sla_engine_enabled','YES','Enable SLA tracking',NULL), (4,'customer_timeline_enabled','YES','Enable customer timeline',NULL); /*!40000 ALTER TABLE `crm_settings` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `crm_sla_rules` -- DROP TABLE IF EXISTS `crm_sla_rules`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `crm_sla_rules` ( `id` int(11) NOT NULL AUTO_INCREMENT, `priority` enum('LOW','MEDIUM','HIGH','URGENT') NOT NULL, `response_hours` decimal(8,2) NOT NULL DEFAULT 24.00, `resolution_hours` decimal(8,2) NOT NULL DEFAULT 72.00, `status` enum('ACTIVE','INACTIVE') DEFAULT 'ACTIVE', `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `priority` (`priority`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `crm_sla_rules` -- LOCK TABLES `crm_sla_rules` WRITE; /*!40000 ALTER TABLE `crm_sla_rules` DISABLE KEYS */; INSERT INTO `crm_sla_rules` (`id`, `priority`, `response_hours`, `resolution_hours`, `status`, `created_at`) VALUES (1,'LOW',24.00,120.00,'ACTIVE','2026-06-27 05:09:26'), (2,'MEDIUM',8.00,72.00,'ACTIVE','2026-06-27 05:09:26'), (3,'HIGH',4.00,24.00,'ACTIVE','2026-06-27 05:09:26'), (4,'URGENT',1.00,4.00,'ACTIVE','2026-06-27 05:09:26'); /*!40000 ALTER TABLE `crm_sla_rules` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `customer_documents` -- DROP TABLE IF EXISTS `customer_documents`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `customer_documents` ( `id` int(11) NOT NULL AUTO_INCREMENT, `customer_id` int(11) NOT NULL, `document_type` varchar(100) DEFAULT NULL, `document_title` varchar(180) NOT NULL, `file_name` varchar(180) DEFAULT NULL, `file_path` varchar(255) DEFAULT NULL, `notes` text DEFAULT NULL, `uploaded_by` int(11) DEFAULT NULL, `uploaded_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `customer_id` (`customer_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `customer_documents` -- LOCK TABLES `customer_documents` WRITE; /*!40000 ALTER TABLE `customer_documents` DISABLE KEYS */; /*!40000 ALTER TABLE `customer_documents` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `customer_equipment` -- DROP TABLE IF EXISTS `customer_equipment`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `customer_equipment` ( `id` int(11) NOT NULL AUTO_INCREMENT, `customer_id` int(11) NOT NULL, `equipment_master_id` int(11) DEFAULT NULL, `equipment_type` enum('CYLINDER','DISPENSER','BOTTLE','CRATE','PUMP','OTHER') NOT NULL, `equipment_name` varchar(150) NOT NULL, `serial_no` varchar(100) DEFAULT NULL, `quantity` decimal(12,2) DEFAULT 1.00, `deposit_amount` decimal(12,2) DEFAULT 0.00, `issue_date` date DEFAULT NULL, `issued_at` datetime DEFAULT NULL, `issued_by` int(11) DEFAULT NULL, `return_date` date DEFAULT NULL, `returned_at` datetime DEFAULT NULL, `returned_by` int(11) DEFAULT NULL, `status` enum('WITH_CUSTOMER','RETURNED','LOST','DAMAGED') DEFAULT 'WITH_CUSTOMER', `notes` text DEFAULT NULL, `created_by` int(11) DEFAULT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), `ownership_status` enum('COMPANY_OWNED','CUSTOMER_OWNED','LAYAWAY') DEFAULT 'COMPANY_OWNED', PRIMARY KEY (`id`), KEY `customer_id` (`customer_id`), KEY `equipment_type` (`equipment_type`), KEY `status` (`status`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `customer_equipment` -- LOCK TABLES `customer_equipment` WRITE; /*!40000 ALTER TABLE `customer_equipment` DISABLE KEYS */; INSERT INTO `customer_equipment` (`id`, `customer_id`, `equipment_master_id`, `equipment_type`, `equipment_name`, `serial_no`, `quantity`, `deposit_amount`, `issue_date`, `issued_at`, `issued_by`, `return_date`, `returned_at`, `returned_by`, `status`, `notes`, `created_by`, `created_at`, `ownership_status`) VALUES (1,3,NULL,'CYLINDER','13 kg gas cylinder -Hashi','',1.00,0.00,'2026-06-27',NULL,NULL,NULL,NULL,NULL,'RETURNED','',1,'2026-06-27 06:12:34','COMPANY_OWNED'); /*!40000 ALTER TABLE `customer_equipment` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `customer_feedback` -- DROP TABLE IF EXISTS `customer_feedback`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `customer_feedback` ( `id` int(11) NOT NULL AUTO_INCREMENT, `feedback_no` varchar(50) NOT NULL, `customer_id` int(11) DEFAULT NULL, `customer_name` varchar(150) DEFAULT NULL, `phone` varchar(30) DEFAULT NULL, `feedback_date` date NOT NULL, `feedback_type` enum('COMPLAINT','SUGGESTION','COMPLIMENT','INQUIRY','OTHER') DEFAULT 'COMPLAINT', `category_id` int(11) DEFAULT NULL, `channel` enum('QR','PHONE','WALK_IN','WHATSAPP','SOCIAL_MEDIA','OTHER') DEFAULT 'QR', `rating` int(11) DEFAULT NULL, `priority` enum('LOW','MEDIUM','HIGH','URGENT') DEFAULT 'MEDIUM', `escalation_level` enum('NONE','SUPERVISOR','MANAGER','DIRECTOR') DEFAULT 'NONE', `escalated_to` int(11) DEFAULT NULL, `escalated_at` timestamp NULL DEFAULT NULL, `escalation_reason` text DEFAULT NULL, `escalation_status` enum('NOT_ESCALATED','ESCALATED','ACKNOWLEDGED','RESOLVED') DEFAULT 'NOT_ESCALATED', `subject` varchar(180) DEFAULT NULL, `description` text NOT NULL, `assigned_to` int(11) DEFAULT NULL, `status` enum('OPEN','IN_PROGRESS','RESOLVED','CLOSED') DEFAULT 'OPEN', `resolution_notes` text DEFAULT NULL, `customer_notified` enum('NO','YES') DEFAULT 'NO', `closed_at` timestamp NULL DEFAULT NULL, `created_by` int(11) DEFAULT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(), `sla_response_due_at` datetime DEFAULT NULL, `sla_resolution_due_at` datetime DEFAULT NULL, `first_response_at` datetime DEFAULT NULL, `sla_status` enum('ON_TRACK','RESPONSE_DUE','RESOLUTION_DUE','BREACHED','COMPLETED') DEFAULT 'ON_TRACK', `satisfaction_rating` int(11) DEFAULT NULL, `satisfaction_comment` text DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `feedback_no` (`feedback_no`), KEY `customer_id` (`customer_id`), KEY `feedback_date` (`feedback_date`), KEY `status` (`status`), KEY `priority` (`priority`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `customer_feedback` -- LOCK TABLES `customer_feedback` WRITE; /*!40000 ALTER TABLE `customer_feedback` DISABLE KEYS */; INSERT INTO `customer_feedback` (`id`, `feedback_no`, `customer_id`, `customer_name`, `phone`, `feedback_date`, `feedback_type`, `category_id`, `channel`, `rating`, `priority`, `escalation_level`, `escalated_to`, `escalated_at`, `escalation_reason`, `escalation_status`, `subject`, `description`, `assigned_to`, `status`, `resolution_notes`, `customer_notified`, `closed_at`, `created_by`, `created_at`, `updated_at`, `sla_response_due_at`, `sla_resolution_due_at`, `first_response_at`, `sla_status`, `satisfaction_rating`, `satisfaction_comment`) VALUES (1,'FB00001',2,'George','0728569381','2026-06-25','COMPLAINT',1,'QR',NULL,'LOW','NONE',NULL,NULL,NULL,'NOT_ESCALATED','Complaint on delayed delivery','The client complained we delayed delivering gas for 2 hours',NULL,'CLOSED','Apologised to the client and promised next time will be better','YES','2026-06-25 19:18:12',1,'2026-06-25 21:55:40','2026-06-27 06:31:48',NULL,NULL,NULL,'COMPLETED',NULL,NULL), (2,'FB00002',4,'Nelvine','0723930299','2026-06-27','COMPLAINT',6,'WALK_IN',3,'HIGH','NONE',NULL,NULL,NULL,'NOT_ESCALATED','Doing a good Job','Client is happy with our services',NULL,'CLOSED','','YES','2026-06-27 07:13:17',1,'2026-06-27 05:17:47','2026-06-28 09:59:52','2026-06-27 12:17:47','2026-06-28 08:17:47',NULL,'COMPLETED',NULL,NULL), (3,'FB00003',NULL,NULL,NULL,'2026-06-27','COMPLAINT',NULL,'QR',NULL,'LOW','NONE',NULL,NULL,NULL,'NOT_ESCALATED','made an order','Meko 6kg cylinder',3,'OPEN',NULL,'NO',NULL,1,'2026-06-27 05:54:51',NULL,NULL,NULL,NULL,'ON_TRACK',NULL,NULL); /*!40000 ALTER TABLE `customer_feedback` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `customer_import_batches` -- DROP TABLE IF EXISTS `customer_import_batches`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `customer_import_batches` ( `id` int(11) NOT NULL AUTO_INCREMENT, `batch_no` varchar(50) DEFAULT NULL, `file_name` varchar(180) DEFAULT NULL, `total_rows` int(11) DEFAULT 0, `imported_rows` int(11) DEFAULT 0, `skipped_rows` int(11) DEFAULT 0, `imported_by` int(11) DEFAULT NULL, `imported_at` timestamp NULL DEFAULT current_timestamp(), `notes` text DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `batch_no` (`batch_no`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `customer_import_batches` -- LOCK TABLES `customer_import_batches` WRITE; /*!40000 ALTER TABLE `customer_import_batches` DISABLE KEYS */; /*!40000 ALTER TABLE `customer_import_batches` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `customer_ledger` -- DROP TABLE IF EXISTS `customer_ledger`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `customer_ledger` ( `id` int(11) NOT NULL AUTO_INCREMENT, `customer_id` int(11) NOT NULL, `transaction_date` date NOT NULL, `transaction_type` enum('OPENING','SALE','PAYMENT','DISCOUNT','LOYALTY_REDEMPTION','ADJUSTMENT','REVERSAL') NOT NULL, `reference_type` varchar(60) DEFAULT NULL, `reference_id` int(11) DEFAULT NULL, `debit` decimal(12,2) DEFAULT 0.00, `credit` decimal(12,2) DEFAULT 0.00, `balance_after` decimal(12,2) DEFAULT 0.00, `description` varchar(255) DEFAULT NULL, `created_by` int(11) DEFAULT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `customer_id` (`customer_id`), KEY `transaction_date` (`transaction_date`), KEY `transaction_type` (`transaction_type`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `customer_ledger` -- LOCK TABLES `customer_ledger` WRITE; /*!40000 ALTER TABLE `customer_ledger` DISABLE KEYS */; /*!40000 ALTER TABLE `customer_ledger` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `customer_timeline` -- DROP TABLE IF EXISTS `customer_timeline`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `customer_timeline` ( `id` int(11) NOT NULL AUTO_INCREMENT, `customer_id` int(11) NOT NULL, `event_date` datetime NOT NULL DEFAULT current_timestamp(), `event_type` varchar(80) NOT NULL, `reference_type` varchar(80) DEFAULT NULL, `reference_id` int(11) DEFAULT NULL, `title` varchar(180) NOT NULL, `description` text DEFAULT NULL, `created_by` int(11) DEFAULT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `customer_id` (`customer_id`), KEY `event_date` (`event_date`), KEY `event_type` (`event_type`) ) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `customer_timeline` -- LOCK TABLES `customer_timeline` WRITE; /*!40000 ALTER TABLE `customer_timeline` DISABLE KEYS */; INSERT INTO `customer_timeline` (`id`, `customer_id`, `event_date`, `event_type`, `reference_type`, `reference_id`, `title`, `description`, `created_by`, `created_at`) VALUES (1,3,'2026-06-27 09:12:34','EQUIPMENT','CUSTOMER_EQUIPMENT',1,'Equipment issued','13 kg gas cylinder -Hashi',1,'2026-06-27 06:12:34'), (2,3,'2026-06-27 09:15:02','EQUIPMENT','CUSTOMER_EQUIPMENT',1,'Equipment updated','13 kg gas cylinder -Hashi - RETURNED',1,'2026-06-27 06:15:02'), (3,2,'2026-06-27 10:09:21','CYLINDER_LAYAWAY','CYLINDER_LAYAWAY',1,'Cylinder layaway opened','CL00001 - 13kg LPG Cylinder',1,'2026-06-27 07:09:21'), (4,2,'2026-06-27 10:15:10','CYLINDER_LAYAWAY','CYLINDER_LAYAWAY',1,'Cylinder layaway payment','Paid KES 500.00. Balance KES 3,500.00',1,'2026-06-27 07:15:10'), (5,2,'2026-06-28 15:59:38','SALE','SALE',1,'POS sale RCP260628125938','Sale amount KES 1,100.00',1,'2026-06-28 12:59:38'), (6,2,'2026-06-28 18:12:29','SALE','SALE',2,'POS sale RCP260628151229','Sale amount KES 410.00',1,'2026-06-28 15:12:29'), (7,5,'2026-06-28 18:59:57','SALE','SALE',4,'POS sale RCP260628155957','Sale amount KES 200.00',1,'2026-06-28 15:59:57'), (8,2,'2026-06-29 23:25:35','SALE','SALE',5,'POS sale RCP260629202535','Sale amount KES 1,200.00',1,'2026-06-29 20:25:35'), (9,6,'2026-06-29 23:33:32','SALE','SALE',6,'POS sale RCP260629203332','Sale amount KES 250.00',1,'2026-06-29 20:33:32'), (10,2,'2026-06-30 12:11:26','SALE','SALE',8,'POS sale RCP260630091126','Sale amount KES 200.00',1,'2026-06-30 09:11:26'), (11,3,'2026-06-30 12:21:28','SALE','SALE',9,'POS sale RCP260630092128','Sale amount KES 3,000.00',1,'2026-06-30 09:21:28'), (12,5,'2026-06-30 12:58:25','SALE','SALE',10,'POS sale RCP260630095825','Sale amount KES 3,000.00',1,'2026-06-30 09:58:25'), (13,6,'2026-06-30 13:34:04','SALE','SALE',11,'POS sale RCP260630103404','Sale amount KES 3,000.00',1,'2026-06-30 10:34:04'), (14,6,'2026-06-30 14:00:52','SALE','SALE',13,'POS sale RCP260630110052','Sale amount KES 1,300.00',1,'2026-06-30 11:00:52'), (15,6,'2026-06-30 19:08:15','SALE','SALE',14,'POS sale RCP260630160815','Sale amount KES 2,200.00',1,'2026-06-30 16:08:15'), (16,6,'2026-06-30 20:30:46','SALE','SALE',15,'POS sale RCP260630173046','Sale amount KES 200.00',1,'2026-06-30 17:30:46'), (17,6,'2026-06-30 20:35:33','SALE','SALE',17,'POS sale RCP260630173533','Sale amount KES 5.00',1,'2026-06-30 17:35:33'), (18,6,'2026-06-30 20:52:51','SALE','SALE',18,'POS sale RCP260630175251','Sale amount KES 200.00',1,'2026-06-30 17:52:51'); /*!40000 ALTER TABLE `customer_timeline` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `customer_wallet_transactions` -- DROP TABLE IF EXISTS `customer_wallet_transactions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `customer_wallet_transactions` ( `id` int(11) NOT NULL AUTO_INCREMENT, `transaction_no` varchar(50) NOT NULL, `customer_id` int(11) NOT NULL, `transaction_date` datetime NOT NULL DEFAULT current_timestamp(), `transaction_type` enum('DEPOSIT','SALE_PAYMENT','REFUND','CREDIT_NOTE','ADJUSTMENT','TRANSFER','REVERSAL') NOT NULL, `debit` decimal(12,2) DEFAULT 0.00, `credit` decimal(12,2) DEFAULT 0.00, `balance_after` decimal(12,2) DEFAULT 0.00, `payment_mode` enum('CASH','MPESA','BANK','OTHER','SYSTEM') DEFAULT 'CASH', `reference_type` varchar(80) DEFAULT NULL, `reference_id` int(11) DEFAULT NULL, `reference_no` varchar(100) DEFAULT NULL, `description` text DEFAULT NULL, `created_by` int(11) DEFAULT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `transaction_no` (`transaction_no`), KEY `customer_id` (`customer_id`), KEY `transaction_date` (`transaction_date`), KEY `transaction_type` (`transaction_type`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `customer_wallet_transactions` -- LOCK TABLES `customer_wallet_transactions` WRITE; /*!40000 ALTER TABLE `customer_wallet_transactions` DISABLE KEYS */; /*!40000 ALTER TABLE `customer_wallet_transactions` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `customer_wallets` -- DROP TABLE IF EXISTS `customer_wallets`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `customer_wallets` ( `id` int(11) NOT NULL AUTO_INCREMENT, `customer_id` int(11) NOT NULL, `wallet_balance` decimal(12,2) NOT NULL DEFAULT 0.00, `status` enum('ACTIVE','FROZEN','CLOSED') DEFAULT 'ACTIVE', `created_at` timestamp NULL DEFAULT current_timestamp(), `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `customer_id` (`customer_id`), KEY `customer_id_2` (`customer_id`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `customer_wallets` -- LOCK TABLES `customer_wallets` WRITE; /*!40000 ALTER TABLE `customer_wallets` DISABLE KEYS */; INSERT INTO `customer_wallets` (`id`, `customer_id`, `wallet_balance`, `status`, `created_at`, `updated_at`) VALUES (1,1,0.00,'ACTIVE','2026-06-27 07:27:56',NULL), (2,2,0.00,'ACTIVE','2026-06-27 07:27:56',NULL), (3,3,0.00,'ACTIVE','2026-06-27 07:27:56',NULL), (4,4,0.00,'ACTIVE','2026-06-27 07:27:56',NULL), (5,5,0.00,'ACTIVE','2026-06-27 07:27:56',NULL), (6,6,0.00,'ACTIVE','2026-06-27 07:27:56',NULL); /*!40000 ALTER TABLE `customer_wallets` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `customers` -- DROP TABLE IF EXISTS `customers`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `customers` ( `id` int(11) NOT NULL AUTO_INCREMENT, `customer_code` varchar(50) DEFAULT NULL, `customer_name` varchar(150) NOT NULL, `contact_name_1` varchar(120) DEFAULT NULL, `contact_phone_1` varchar(20) DEFAULT NULL, `contact_name_2` varchar(120) DEFAULT NULL, `contact_phone_2` varchar(20) DEFAULT NULL, `unique_identifier` varchar(80) DEFAULT NULL, `national_id` varchar(50) DEFAULT NULL, `phone` varchar(30) DEFAULT NULL, `location` varchar(180) DEFAULT NULL, `street` varchar(120) DEFAULT NULL, `plot_number` varchar(80) DEFAULT NULL, `house_number` varchar(80) DEFAULT NULL, `landmark` varchar(255) DEFAULT NULL, `route_zone` varchar(120) DEFAULT NULL, `opening_balance` decimal(12,2) DEFAULT 0.00, `loyalty_points_balance` decimal(12,2) DEFAULT 0.00, `credit_limit` decimal(12,2) DEFAULT 0.00, `current_balance` decimal(12,2) DEFAULT 0.00, `customer_type` enum('NEW','EXISTING','RETAIL','WHOLESALE','CORPORATE','GOVERNMENT','STAFF','DISTRIBUTOR','OTHER') DEFAULT 'RETAIL', `status` enum('ACTIVE','INACTIVE') DEFAULT 'ACTIVE', `created_by` int(11) DEFAULT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(), `notes` text DEFAULT NULL, `wallet_balance` decimal(12,2) DEFAULT 0.00, PRIMARY KEY (`id`), UNIQUE KEY `customer_code` (`customer_code`), UNIQUE KEY `customer_code_2` (`customer_code`), KEY `created_by` (`created_by`), KEY `idx_customers_name` (`customer_name`), KEY `idx_customers_phone` (`phone`), KEY `idx_customers_status` (`status`), CONSTRAINT `customers_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `customers` -- LOCK TABLES `customers` WRITE; /*!40000 ALTER TABLE `customers` DISABLE KEYS */; INSERT INTO `customers` (`id`, `customer_code`, `customer_name`, `contact_name_1`, `contact_phone_1`, `contact_name_2`, `contact_phone_2`, `unique_identifier`, `national_id`, `phone`, `location`, `street`, `plot_number`, `house_number`, `landmark`, `route_zone`, `opening_balance`, `loyalty_points_balance`, `credit_limit`, `current_balance`, `customer_type`, `status`, `created_by`, `created_at`, `updated_at`, `notes`, `wallet_balance`) VALUES (1,'N00001','Test Client',NULL,NULL,NULL,NULL,NULL,NULL,'0712345678',NULL,NULL,NULL,NULL,NULL,NULL,0.00,0.00,0.00,0.00,'NEW','ACTIVE',NULL,'2026-06-25 16:47:24','2026-06-25 17:49:30',NULL,0.00), (2,'N00002','George','george','00000000000','','',NULL,NULL,'0728569381','phase 13','iiii','14','2','Muhinja','',0.00,0.00,0.00,0.00,'RETAIL','ACTIVE',1,'2026-06-25 17:34:59','2026-06-25 17:49:30','',0.00), (3,'N00003','George','','','','',NULL,NULL,'0742000888','','','14','2','carwash blue gate','',0.00,0.00,0.00,0.00,'RETAIL','ACTIVE',1,'2026-06-25 17:43:10','2026-06-25 17:49:30','',0.00), (4,'N00004','Nelvine','','','','',NULL,NULL,'0723930299','phase 13','4','14','4','carwash blue gate','7',0.00,0.00,0.00,0.00,'RETAIL','ACTIVE',1,'2026-06-25 17:50:17',NULL,'',0.00), (5,'N00005','George','','','','',NULL,NULL,'0722635887','phase 13','iiii','14','b2','carwash blue gate','rrr',0.00,0.00,0.00,0.00,'RETAIL','ACTIVE',1,'2026-06-27 05:11:47',NULL,'',0.00), (6,'N00006','Martin','','','','',NULL,NULL,'333333333333','phase 13','4','14','b2','Muhinja','1',0.00,0.00,0.00,0.00,'RETAIL','ACTIVE',1,'2026-06-27 05:53:59',NULL,'',0.00), (7,'N00053','Walk-in Client',NULL,NULL,NULL,NULL,NULL,NULL,'WALKIN',NULL,NULL,NULL,NULL,NULL,NULL,0.00,0.00,0.00,0.00,'NEW','ACTIVE',NULL,'2026-06-27 11:06:46',NULL,NULL,0.00), (8,'N00054','Mary ann','Mary','0757569090','John','09993938889',NULL,NULL,'0757569090','phase 13','4','14','5','Next to soko jirani','7',0.00,0.00,0.00,0.00,'RETAIL','ACTIVE',1,'2026-06-30 17:45:41',NULL,'',0.00); /*!40000 ALTER TABLE `customers` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `cylinder_layaway` -- DROP TABLE IF EXISTS `cylinder_layaway`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `cylinder_layaway` ( `id` int(11) NOT NULL AUTO_INCREMENT, `layaway_no` varchar(50) NOT NULL, `customer_id` int(11) NOT NULL, `equipment_master_id` int(11) NOT NULL, `target_amount` decimal(12,2) NOT NULL DEFAULT 0.00, `amount_paid` decimal(12,2) NOT NULL DEFAULT 0.00, `balance` decimal(12,2) NOT NULL DEFAULT 0.00, `start_date` date NOT NULL, `status` enum('ACTIVE','COMPLETED','CANCELLED') DEFAULT 'ACTIVE', `completed_at` datetime DEFAULT NULL, `created_by` int(11) DEFAULT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `layaway_no` (`layaway_no`), KEY `customer_id` (`customer_id`), KEY `equipment_master_id` (`equipment_master_id`), KEY `status` (`status`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `cylinder_layaway` -- LOCK TABLES `cylinder_layaway` WRITE; /*!40000 ALTER TABLE `cylinder_layaway` DISABLE KEYS */; INSERT INTO `cylinder_layaway` (`id`, `layaway_no`, `customer_id`, `equipment_master_id`, `target_amount`, `amount_paid`, `balance`, `start_date`, `status`, `completed_at`, `created_by`, `created_at`, `updated_at`) VALUES (1,'CL00001',2,2,5000.00,1500.00,3500.00,'2026-06-27','ACTIVE',NULL,1,'2026-06-27 07:09:21','2026-06-27 07:15:10'); /*!40000 ALTER TABLE `cylinder_layaway` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `cylinder_layaway_payments` -- DROP TABLE IF EXISTS `cylinder_layaway_payments`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `cylinder_layaway_payments` ( `id` int(11) NOT NULL AUTO_INCREMENT, `layaway_id` int(11) NOT NULL, `payment_date` datetime NOT NULL DEFAULT current_timestamp(), `amount` decimal(12,2) NOT NULL, `payment_mode` enum('CASH','MPESA','BANK','OTHER') DEFAULT 'CASH', `reference_no` varchar(100) DEFAULT NULL, `notes` text DEFAULT NULL, `created_by` int(11) DEFAULT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `layaway_id` (`layaway_id`), KEY `payment_date` (`payment_date`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `cylinder_layaway_payments` -- LOCK TABLES `cylinder_layaway_payments` WRITE; /*!40000 ALTER TABLE `cylinder_layaway_payments` DISABLE KEYS */; INSERT INTO `cylinder_layaway_payments` (`id`, `layaway_id`, `payment_date`, `amount`, `payment_mode`, `reference_no`, `notes`, `created_by`, `created_at`) VALUES (1,1,'2026-06-27 10:09:21',1000.00,'CASH','','Opening payment',1,'2026-06-27 07:09:21'), (2,1,'2026-06-27 10:15:10',500.00,'MPESA','','',1,'2026-06-27 07:15:10'); /*!40000 ALTER TABLE `cylinder_layaway_payments` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `dashboard_role_settings` -- DROP TABLE IF EXISTS `dashboard_role_settings`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `dashboard_role_settings` ( `id` int(11) NOT NULL AUTO_INCREMENT, `role_name` varchar(80) NOT NULL, `widget_key` varchar(100) NOT NULL, `can_view` tinyint(1) DEFAULT 1, `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `unique_role_widget` (`role_name`,`widget_key`), KEY `role_name` (`role_name`), KEY `widget_key` (`widget_key`) ) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `dashboard_role_settings` -- LOCK TABLES `dashboard_role_settings` WRITE; /*!40000 ALTER TABLE `dashboard_role_settings` DISABLE KEYS */; INSERT INTO `dashboard_role_settings` (`id`, `role_name`, `widget_key`, `can_view`, `created_at`) VALUES (1,'SALES_AGENT','today_sales',1,'2026-06-21 15:20:40'), (2,'SALES_AGENT','week_sales',1,'2026-06-21 15:20:40'), (3,'SALES_AGENT','cash_till',1,'2026-06-21 15:20:40'), (4,'SALES_AGENT','mpesa_till',1,'2026-06-21 15:20:40'), (5,'SALES_AGENT','bank_till',1,'2026-06-21 15:20:40'), (6,'SALES_AGENT','bank_balance',0,'2026-06-21 15:20:40'), (7,'SALES_AGENT','profit',0,'2026-06-21 15:20:40'), (8,'SALES_AGENT','expenses',0,'2026-06-21 15:20:40'), (9,'SALES_AGENT','supplier_balances',0,'2026-06-21 15:20:40'), (10,'SUPERVISOR','today_sales',1,'2026-06-21 15:20:40'), (11,'SUPERVISOR','week_sales',1,'2026-06-21 15:20:40'), (12,'SUPERVISOR','cash_till',1,'2026-06-21 15:20:40'), (13,'SUPERVISOR','mpesa_till',1,'2026-06-21 15:20:40'), (14,'SUPERVISOR','bank_till',1,'2026-06-21 15:20:40'), (15,'SUPERVISOR','till_reconciliation',1,'2026-06-21 15:20:40'), (16,'SUPERVISOR','home_consumption',1,'2026-06-21 15:20:40'), (17,'SUPERVISOR','bank_balance',0,'2026-06-21 15:20:40'), (18,'ACCOUNTANT','expenses',1,'2026-06-21 15:20:40'), (19,'ACCOUNTANT','assets',1,'2026-06-21 15:20:40'), (20,'ACCOUNTANT','supplier_balances',1,'2026-06-21 15:20:40'), (21,'ACCOUNTANT','customer_balances',1,'2026-06-21 15:20:40'), (22,'ACCOUNTANT','banking_reconciliation',1,'2026-06-21 15:20:40'), (23,'SUPER_ADMIN','today_sales',1,'2026-06-21 15:20:40'), (24,'SUPER_ADMIN','week_sales',1,'2026-06-21 15:20:40'), (25,'SUPER_ADMIN','cash_till',1,'2026-06-21 15:20:40'), (26,'SUPER_ADMIN','mpesa_till',1,'2026-06-21 15:20:40'), (27,'SUPER_ADMIN','bank_till',1,'2026-06-21 15:20:40'), (28,'SUPER_ADMIN','bank_balance',1,'2026-06-21 15:20:40'), (29,'SUPER_ADMIN','profit',1,'2026-06-21 15:20:40'), (30,'SUPER_ADMIN','expenses',1,'2026-06-21 15:20:40'), (31,'SUPER_ADMIN','supplier_balances',1,'2026-06-21 15:20:40'), (32,'SUPER_ADMIN','customer_balances',1,'2026-06-21 15:20:40'), (33,'SUPER_ADMIN','stock_value',1,'2026-06-21 15:20:40'), (34,'SUPER_ADMIN','asset_value',1,'2026-06-21 15:20:40'), (35,'SUPER_ADMIN','home_consumption',1,'2026-06-21 15:20:40'), (36,'SUPER_ADMIN','business_ratios',1,'2026-06-21 15:20:40'), (37,'SUPER_ADMIN','trial_balance',1,'2026-06-21 15:20:40'), (38,'SUPER_ADMIN','profit_loss',1,'2026-06-21 15:20:40'), (39,'SUPER_ADMIN','balance_sheet',1,'2026-06-21 15:20:40'); /*!40000 ALTER TABLE `dashboard_role_settings` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `debtor_payments` -- DROP TABLE IF EXISTS `debtor_payments`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `debtor_payments` ( `id` int(11) NOT NULL AUTO_INCREMENT, `debtor_id` int(11) NOT NULL, `amount` decimal(12,2) NOT NULL, `payment_mode` enum('CASH','MPESA','BANK') NOT NULL, `reference_no` varchar(100) DEFAULT NULL, `payment_date` date NOT NULL, `received_by` int(11) NOT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `debtor_id` (`debtor_id`), KEY `received_by` (`received_by`), CONSTRAINT `debtor_payments_ibfk_1` FOREIGN KEY (`debtor_id`) REFERENCES `debtors` (`id`), CONSTRAINT `debtor_payments_ibfk_2` FOREIGN KEY (`received_by`) REFERENCES `users` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `debtor_payments` -- LOCK TABLES `debtor_payments` WRITE; /*!40000 ALTER TABLE `debtor_payments` DISABLE KEYS */; /*!40000 ALTER TABLE `debtor_payments` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `debtors` -- DROP TABLE IF EXISTS `debtors`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `debtors` ( `id` int(11) NOT NULL AUTO_INCREMENT, `customer_id` int(11) NOT NULL, `sale_id` int(11) DEFAULT NULL, `amount` decimal(12,2) NOT NULL, `balance` decimal(12,2) NOT NULL, `due_date` date DEFAULT NULL, `status` enum('OPEN','PARTIAL','PAID','OVERDUE') DEFAULT 'OPEN', `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `customer_id` (`customer_id`), KEY `sale_id` (`sale_id`), CONSTRAINT `debtors_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`), CONSTRAINT `debtors_ibfk_2` FOREIGN KEY (`sale_id`) REFERENCES `sales` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `debtors` -- LOCK TABLES `debtors` WRITE; /*!40000 ALTER TABLE `debtors` DISABLE KEYS */; /*!40000 ALTER TABLE `debtors` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `director_capital` -- DROP TABLE IF EXISTS `director_capital`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `director_capital` ( `id` int(11) NOT NULL AUTO_INCREMENT, `director_name` varchar(150) NOT NULL, `transaction_type` enum('INJECTION','DRAWING') DEFAULT 'INJECTION', `amount` decimal(12,2) NOT NULL, `payment_mode` enum('CASH','MPESA','BANK') NOT NULL, `transaction_date` date NOT NULL, `remarks` text DEFAULT NULL, `created_by` int(11) NOT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `created_by` (`created_by`), CONSTRAINT `director_capital_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `director_capital` -- LOCK TABLES `director_capital` WRITE; /*!40000 ALTER TABLE `director_capital` DISABLE KEYS */; /*!40000 ALTER TABLE `director_capital` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `equipment_master` -- DROP TABLE IF EXISTS `equipment_master`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `equipment_master` ( `id` int(11) NOT NULL AUTO_INCREMENT, `equipment_code` varchar(50) NOT NULL, `equipment_type` enum('CYLINDER','DISPENSER','BOTTLE','CRATE','PUMP','OTHER') NOT NULL, `equipment_name` varchar(150) NOT NULL, `default_deposit` decimal(12,2) DEFAULT 0.00, `ownership_price` decimal(12,2) DEFAULT 0.00, `status` enum('ACTIVE','INACTIVE') DEFAULT 'ACTIVE', `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `equipment_code` (`equipment_code`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `equipment_master` -- LOCK TABLES `equipment_master` WRITE; /*!40000 ALTER TABLE `equipment_master` DISABLE KEYS */; INSERT INTO `equipment_master` (`id`, `equipment_code`, `equipment_type`, `equipment_name`, `default_deposit`, `ownership_price`, `status`, `created_at`) VALUES (1,'EQ0001','CYLINDER','6kg LPG Cylinder',3000.00,3000.00,'ACTIVE','2026-06-27 06:34:07'), (2,'EQ0002','CYLINDER','13kg LPG Cylinder',5000.00,5000.00,'ACTIVE','2026-06-27 06:34:07'), (3,'EQ0003','CYLINDER','50kg LPG Cylinder',12000.00,12000.00,'ACTIVE','2026-06-27 06:34:07'), (4,'EQ0004','DISPENSER','Water Dispenser',4500.00,4500.00,'ACTIVE','2026-06-27 06:34:07'), (5,'EQ0005','BOTTLE','20L Water Bottle',800.00,800.00,'ACTIVE','2026-06-27 06:34:07'); /*!40000 ALTER TABLE `equipment_master` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `expense_categories` -- DROP TABLE IF EXISTS `expense_categories`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `expense_categories` ( `id` int(11) NOT NULL AUTO_INCREMENT, `category_name` varchar(120) NOT NULL, `status` enum('ACTIVE','INACTIVE') DEFAULT 'ACTIVE', PRIMARY KEY (`id`), UNIQUE KEY `category_name` (`category_name`) ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `expense_categories` -- LOCK TABLES `expense_categories` WRITE; /*!40000 ALTER TABLE `expense_categories` DISABLE KEYS */; INSERT INTO `expense_categories` (`id`, `category_name`, `status`) VALUES (1,'Water','ACTIVE'), (2,'Electricity','ACTIVE'), (3,'Telephone Airtime/Data','ACTIVE'), (4,'Rent','ACTIVE'), (5,'Salaries','ACTIVE'), (6,'Bicycle repair','ACTIVE'), (7,'Branding','ACTIVE'), (8,'Marketing','ACTIVE'), (9,'Food','ACTIVE'), (10,'Transport','ACTIVE'), (11,'Transaction fee','ACTIVE'), (12,'Brush','ACTIVE'), (13,'Establishment cost - Stamp','ACTIVE'), (14,'Laundry','ACTIVE'), (15,'Cleaning, detergents and repellants','ACTIVE'), (16,'Security','ACTIVE'); /*!40000 ALTER TABLE `expense_categories` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `expense_register` -- DROP TABLE IF EXISTS `expense_register`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `expense_register` ( `id` int(11) NOT NULL AUTO_INCREMENT, `expense_no` varchar(50) DEFAULT NULL, `supplier_id` int(11) DEFAULT NULL, `purchase_id` int(11) DEFAULT NULL, `purchase_item_id` int(11) DEFAULT NULL, `expense_date` date NOT NULL, `expense_category` varchar(100) DEFAULT NULL, `expense_name` varchar(180) NOT NULL, `amount` decimal(12,2) NOT NULL DEFAULT 0.00, `payment_mode` enum('CASH','MPESA','BANK','CREDIT','OTHER') DEFAULT 'CASH', `reference_no` varchar(100) DEFAULT NULL, `status` enum('POSTED','VOID') DEFAULT 'POSTED', `notes` text DEFAULT NULL, `created_by` int(11) DEFAULT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `expense_no` (`expense_no`), KEY `supplier_id` (`supplier_id`), KEY `purchase_id` (`purchase_id`), KEY `expense_date` (`expense_date`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `expense_register` -- LOCK TABLES `expense_register` WRITE; /*!40000 ALTER TABLE `expense_register` DISABLE KEYS */; INSERT INTO `expense_register` (`id`, `expense_no`, `supplier_id`, `purchase_id`, `purchase_item_id`, `expense_date`, `expense_category`, `expense_name`, `amount`, `payment_mode`, `reference_no`, `status`, `notes`, `created_by`, `created_at`) VALUES (1,'EXP-00005',21,8,5,'2026-06-21','Purchase Expense','Trading License',12000.00,'CASH',NULL,'POSTED','',1,'2026-06-23 17:57:07'), (2,'EXP-00009',21,12,9,'2026-06-22','Purchase Expense','Purchase Expense',2000.00,'CASH',NULL,'POSTED','',1,'2026-06-23 17:57:48'); /*!40000 ALTER TABLE `expense_register` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `expenses` -- DROP TABLE IF EXISTS `expenses`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `expenses` ( `id` int(11) NOT NULL AUTO_INCREMENT, `expense_category_id` int(11) NOT NULL, `expense_date` date NOT NULL, `description` text DEFAULT NULL, `amount` decimal(12,2) NOT NULL, `payment_mode` enum('CASH','MPESA','BANK','CREDIT') NOT NULL, `created_by` int(11) NOT NULL, `approved_by` int(11) DEFAULT NULL, `approval_status` enum('PENDING','APPROVED','REJECTED') DEFAULT 'APPROVED', `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `expense_category_id` (`expense_category_id`), KEY `created_by` (`created_by`), KEY `approved_by` (`approved_by`), KEY `idx_expense_date` (`expense_date`), CONSTRAINT `expenses_ibfk_1` FOREIGN KEY (`expense_category_id`) REFERENCES `expense_categories` (`id`), CONSTRAINT `expenses_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`), CONSTRAINT `expenses_ibfk_3` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `expenses` -- LOCK TABLES `expenses` WRITE; /*!40000 ALTER TABLE `expenses` DISABLE KEYS */; INSERT INTO `expenses` (`id`, `expense_category_id`, `expense_date`, `description`, `amount`, `payment_mode`, `created_by`, `approved_by`, `approval_status`, `created_at`) VALUES (1,6,'2026-06-30','',20.00,'CASH',1,NULL,'APPROVED','2026-06-30 11:43:44'), (2,9,'2026-06-30','',2000.00,'CASH',1,NULL,'APPROVED','2026-06-30 11:44:25'), (3,6,'2026-06-30','',1500.00,'MPESA',1,NULL,'APPROVED','2026-06-30 12:02:44'), (4,6,'2026-06-30','',5000.00,'CASH',1,NULL,'APPROVED','2026-06-30 13:24:32'); /*!40000 ALTER TABLE `expenses` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `feedback_actions` -- DROP TABLE IF EXISTS `feedback_actions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `feedback_actions` ( `id` int(11) NOT NULL AUTO_INCREMENT, `feedback_id` int(11) NOT NULL, `action_date` datetime NOT NULL DEFAULT current_timestamp(), `action_type` enum('CREATED','ASSIGNED','STATUS_CHANGE','CALLBACK','FOLLOW_UP','RESOLUTION','CUSTOMER_NOTIFIED','NOTE') DEFAULT 'NOTE', `old_status` varchar(50) DEFAULT NULL, `new_status` varchar(50) DEFAULT NULL, `action_notes` text DEFAULT NULL, `created_by` int(11) DEFAULT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `feedback_id` (`feedback_id`), KEY `action_date` (`action_date`), KEY `action_type` (`action_type`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `feedback_actions` -- LOCK TABLES `feedback_actions` WRITE; /*!40000 ALTER TABLE `feedback_actions` DISABLE KEYS */; INSERT INTO `feedback_actions` (`id`, `feedback_id`, `action_date`, `action_type`, `old_status`, `new_status`, `action_notes`, `created_by`, `created_at`) VALUES (1,1,'2026-06-26 00:55:40','CREATED',NULL,'OPEN','Feedback captured',1,'2026-06-25 21:55:40'), (2,1,'2026-06-26 01:17:58','NOTE','OPEN','OPEN','Apologised to the client and promised next time will be better',1,'2026-06-25 22:17:58'), (3,1,'2026-06-26 01:18:12','STATUS_CHANGE','OPEN','CLOSED','Apologised to the client and promised next time will be better',1,'2026-06-25 22:18:12'), (4,2,'2026-06-27 08:17:47','CREATED',NULL,'OPEN','Feedback captured',1,'2026-06-27 05:17:47'), (5,2,'2026-06-27 08:18:22','STATUS_CHANGE','OPEN','IN_PROGRESS','',1,'2026-06-27 05:18:22'), (6,3,'2026-06-27 08:54:51','CREATED',NULL,'OPEN','Feedback captured',1,'2026-06-27 05:54:51'), (7,2,'2026-06-27 13:13:17','STATUS_CHANGE','IN_PROGRESS','CLOSED','',1,'2026-06-27 10:13:17'); /*!40000 ALTER TABLE `feedback_actions` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `feedback_attachments` -- DROP TABLE IF EXISTS `feedback_attachments`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `feedback_attachments` ( `id` int(11) NOT NULL AUTO_INCREMENT, `feedback_id` int(11) NOT NULL, `file_name` varchar(180) NOT NULL, `file_path` varchar(255) NOT NULL, `file_type` varchar(80) DEFAULT NULL, `uploaded_by` int(11) DEFAULT NULL, `uploaded_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `feedback_id` (`feedback_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `feedback_attachments` -- LOCK TABLES `feedback_attachments` WRITE; /*!40000 ALTER TABLE `feedback_attachments` DISABLE KEYS */; /*!40000 ALTER TABLE `feedback_attachments` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `feedback_categories` -- DROP TABLE IF EXISTS `feedback_categories`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `feedback_categories` ( `id` int(11) NOT NULL AUTO_INCREMENT, `category_name` varchar(120) NOT NULL, `description` varchar(255) DEFAULT NULL, `status` enum('ACTIVE','INACTIVE') DEFAULT 'ACTIVE', `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `category_name` (`category_name`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `feedback_categories` -- LOCK TABLES `feedback_categories` WRITE; /*!40000 ALTER TABLE `feedback_categories` DISABLE KEYS */; INSERT INTO `feedback_categories` (`id`, `category_name`, `description`, `status`, `created_at`) VALUES (1,'Delivery Delay','Late or missed delivery','ACTIVE','2026-06-25 20:45:26'), (2,'Product Quality','Water, gas, cylinder, or accessory quality issue','ACTIVE','2026-06-25 20:45:26'), (3,'Customer Service','Staff attitude, communication, or service experience','ACTIVE','2026-06-25 20:45:26'), (4,'Billing / Payment','Payment, pricing, receipt, or account issue','ACTIVE','2026-06-25 20:45:26'), (5,'General Suggestion','Improvement suggestion','ACTIVE','2026-06-25 20:45:26'), (6,'Compliment','Positive feedback','ACTIVE','2026-06-25 20:45:26'); /*!40000 ALTER TABLE `feedback_categories` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `feedback_escalations` -- DROP TABLE IF EXISTS `feedback_escalations`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `feedback_escalations` ( `id` int(11) NOT NULL AUTO_INCREMENT, `feedback_id` int(11) NOT NULL, `escalation_date` datetime NOT NULL DEFAULT current_timestamp(), `escalation_level` enum('SUPERVISOR','MANAGER','DIRECTOR') NOT NULL, `escalated_from` int(11) DEFAULT NULL, `escalated_to` int(11) DEFAULT NULL, `reason` text NOT NULL, `status` enum('ESCALATED','ACKNOWLEDGED','RESOLVED') DEFAULT 'ESCALATED', `resolution_notes` text DEFAULT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `feedback_id` (`feedback_id`), KEY `escalated_to` (`escalated_to`), KEY `status` (`status`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `feedback_escalations` -- LOCK TABLES `feedback_escalations` WRITE; /*!40000 ALTER TABLE `feedback_escalations` DISABLE KEYS */; /*!40000 ALTER TABLE `feedback_escalations` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `feedback_settings` -- DROP TABLE IF EXISTS `feedback_settings`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `feedback_settings` ( `id` int(11) NOT NULL AUTO_INCREMENT, `setting_key` varchar(100) NOT NULL, `setting_value` varchar(255) DEFAULT NULL, `description` varchar(255) DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `setting_key` (`setting_key`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `feedback_settings` -- LOCK TABLES `feedback_settings` WRITE; /*!40000 ALTER TABLE `feedback_settings` DISABLE KEYS */; INSERT INTO `feedback_settings` (`id`, `setting_key`, `setting_value`, `description`, `updated_at`) VALUES (1,'feedback_enabled','YES','Enable feedback module',NULL), (2,'feedback_qr_url','','External URL behind QR code; update later if required',NULL), (3,'default_priority','MEDIUM','Default feedback priority',NULL), (4,'auto_assign_user_id','','Default staff ID for new feedback',NULL), (5,'public_feedback_enabled','YES','Allow public customer feedback form from QR',NULL); /*!40000 ALTER TABLE `feedback_settings` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `feedback_tickets` -- DROP TABLE IF EXISTS `feedback_tickets`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `feedback_tickets` ( `id` int(11) NOT NULL AUTO_INCREMENT, `ticket_no` varchar(60) NOT NULL, `customer_id` int(11) DEFAULT NULL, `customer_name` varchar(150) DEFAULT NULL, `phone` varchar(30) DEFAULT NULL, `issue_description` text NOT NULL, `action_taken` text DEFAULT NULL, `action_owner` int(11) DEFAULT NULL, `status` enum('OPEN','IN_PROGRESS','CLOSED','ESCALATED') DEFAULT 'OPEN', `date_raised` date NOT NULL, `closure_comments` text DEFAULT NULL, `closed_at` datetime DEFAULT NULL, `created_by` int(11) NOT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `ticket_no` (`ticket_no`), KEY `customer_id` (`customer_id`), KEY `action_owner` (`action_owner`), KEY `created_by` (`created_by`), CONSTRAINT `feedback_tickets_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`), CONSTRAINT `feedback_tickets_ibfk_2` FOREIGN KEY (`action_owner`) REFERENCES `users` (`id`), CONSTRAINT `feedback_tickets_ibfk_3` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `feedback_tickets` -- LOCK TABLES `feedback_tickets` WRITE; /*!40000 ALTER TABLE `feedback_tickets` DISABLE KEYS */; /*!40000 ALTER TABLE `feedback_tickets` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `finance_accounts` -- DROP TABLE IF EXISTS `finance_accounts`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `finance_accounts` ( `id` int(11) NOT NULL AUTO_INCREMENT, `account_code` varchar(30) NOT NULL, `account_name` varchar(100) NOT NULL, `account_type` enum('CASH','MPESA','BANK') NOT NULL, `opening_balance` decimal(12,2) DEFAULT 0.00, `current_balance` decimal(12,2) DEFAULT 0.00, `status` enum('ACTIVE','INACTIVE') DEFAULT 'ACTIVE', `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `account_code` (`account_code`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `finance_accounts` -- LOCK TABLES `finance_accounts` WRITE; /*!40000 ALTER TABLE `finance_accounts` DISABLE KEYS */; INSERT INTO `finance_accounts` (`id`, `account_code`, `account_name`, `account_type`, `opening_balance`, `current_balance`, `status`, `created_at`) VALUES (1,'CASH-TILL','Cash Till / Cashier Cash','CASH',0.00,0.00,'ACTIVE','2026-06-30 13:17:55'), (2,'MPESA-TILL','M-Pesa Till','MPESA',0.00,0.00,'ACTIVE','2026-06-30 13:17:55'), (3,'BANK-MAIN','Main Bank Account','BANK',0.00,0.00,'ACTIVE','2026-06-30 13:17:55'); /*!40000 ALTER TABLE `finance_accounts` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `finance_reconciliations` -- DROP TABLE IF EXISTS `finance_reconciliations`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `finance_reconciliations` ( `id` int(11) NOT NULL AUTO_INCREMENT, `recon_date` date NOT NULL, `account_type` enum('CASH','MPESA','BANK') NOT NULL, `expected_balance` decimal(12,2) NOT NULL DEFAULT 0.00, `actual_balance` decimal(12,2) NOT NULL DEFAULT 0.00, `variance` decimal(12,2) NOT NULL DEFAULT 0.00, `notes` text DEFAULT NULL, `created_by` int(11) DEFAULT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `uniq_recon_day_type` (`recon_date`,`account_type`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `finance_reconciliations` -- LOCK TABLES `finance_reconciliations` WRITE; /*!40000 ALTER TABLE `finance_reconciliations` DISABLE KEYS */; /*!40000 ALTER TABLE `finance_reconciliations` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `finance_transfers` -- DROP TABLE IF EXISTS `finance_transfers`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `finance_transfers` ( `id` int(11) NOT NULL AUTO_INCREMENT, `transfer_no` varchar(40) NOT NULL, `transfer_date` datetime NOT NULL, `from_account_id` int(11) NOT NULL, `to_account_id` int(11) NOT NULL, `amount` decimal(12,2) NOT NULL, `reference_no` varchar(100) DEFAULT NULL, `notes` text DEFAULT NULL, `created_by` int(11) DEFAULT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `transfer_no` (`transfer_no`), KEY `from_account_id` (`from_account_id`), KEY `to_account_id` (`to_account_id`), CONSTRAINT `finance_transfers_ibfk_1` FOREIGN KEY (`from_account_id`) REFERENCES `finance_accounts` (`id`), CONSTRAINT `finance_transfers_ibfk_2` FOREIGN KEY (`to_account_id`) REFERENCES `finance_accounts` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `finance_transfers` -- LOCK TABLES `finance_transfers` WRITE; /*!40000 ALTER TABLE `finance_transfers` DISABLE KEYS */; INSERT INTO `finance_transfers` (`id`, `transfer_no`, `transfer_date`, `from_account_id`, `to_account_id`, `amount`, `reference_no`, `notes`, `created_by`, `created_at`) VALUES (1,'TRF260630132019','2026-06-30 16:20:19',1,3,9400.00,'','',1,'2026-06-30 13:20:19'); /*!40000 ALTER TABLE `finance_transfers` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `financial_report_settings` -- DROP TABLE IF EXISTS `financial_report_settings`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `financial_report_settings` ( `id` int(11) NOT NULL AUTO_INCREMENT, `setting_key` varchar(100) NOT NULL, `setting_value` varchar(255) DEFAULT NULL, `description` varchar(255) DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `setting_key` (`setting_key`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `financial_report_settings` -- LOCK TABLES `financial_report_settings` WRITE; /*!40000 ALTER TABLE `financial_report_settings` DISABLE KEYS */; INSERT INTO `financial_report_settings` (`id`, `setting_key`, `setting_value`, `description`, `updated_at`) VALUES (1,'financial_year_start_month','1','Month number when the financial year starts',NULL), (2,'default_currency','KES','Default reporting currency',NULL), (3,'enable_auto_journals','YES','Automatically post journals from sales, purchases, expenses and assets',NULL); /*!40000 ALTER TABLE `financial_report_settings` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `goods_receipt_items` -- DROP TABLE IF EXISTS `goods_receipt_items`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `goods_receipt_items` ( `id` int(11) NOT NULL AUTO_INCREMENT, `goods_receipt_id` int(11) NOT NULL, `purchase_item_id` int(11) DEFAULT NULL, `product_id` int(11) DEFAULT NULL, `quantity_received` decimal(12,2) NOT NULL DEFAULT 0.00, `unit_cost` decimal(12,2) DEFAULT 0.00, `total_cost` decimal(12,2) DEFAULT 0.00, `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `goods_receipt_id` (`goods_receipt_id`), KEY `purchase_item_id` (`purchase_item_id`), KEY `product_id` (`product_id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `goods_receipt_items` -- LOCK TABLES `goods_receipt_items` WRITE; /*!40000 ALTER TABLE `goods_receipt_items` DISABLE KEYS */; INSERT INTO `goods_receipt_items` (`id`, `goods_receipt_id`, `purchase_item_id`, `product_id`, `quantity_received`, `unit_cost`, `total_cost`, `created_at`) VALUES (1,1,8,19,1.00,500.00,500.00,'2026-06-23 17:53:22'), (2,2,11,59,3.00,2000.00,6000.00,'2026-06-23 17:54:30'), (3,3,10,69,3000.00,2.00,6000.00,'2026-06-23 17:59:46'), (4,4,3,69,3000.00,2.00,6000.00,'2026-06-23 18:01:52'), (5,4,4,65,24.00,25.00,600.00,'2026-06-23 18:01:52'), (6,5,2,76,1.00,3500.00,3500.00,'2026-06-23 18:02:47'), (7,6,1,106,60.00,160.00,9600.00,'2026-06-23 18:03:18'), (8,7,12,60,4.00,2000.00,8000.00,'2026-06-23 20:30:08'), (9,8,13,80,5.00,3000.00,15000.00,'2026-06-25 17:36:53'), (10,9,14,50,5.00,400.00,2000.00,'2026-06-25 17:38:53'); /*!40000 ALTER TABLE `goods_receipt_items` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `goods_receipts` -- DROP TABLE IF EXISTS `goods_receipts`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `goods_receipts` ( `id` int(11) NOT NULL AUTO_INCREMENT, `grn_no` varchar(50) DEFAULT NULL, `purchase_id` int(11) NOT NULL, `supplier_id` int(11) DEFAULT NULL, `receipt_date` date NOT NULL, `total_items` decimal(12,2) DEFAULT 0.00, `status` enum('DRAFT','RECEIVED','CANCELLED') DEFAULT 'RECEIVED', `received_by` int(11) DEFAULT NULL, `notes` text DEFAULT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `grn_no` (`grn_no`), KEY `purchase_id` (`purchase_id`), KEY `supplier_id` (`supplier_id`), KEY `receipt_date` (`receipt_date`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `goods_receipts` -- LOCK TABLES `goods_receipts` WRITE; /*!40000 ALTER TABLE `goods_receipts` DISABLE KEYS */; INSERT INTO `goods_receipts` (`id`, `grn_no`, `purchase_id`, `supplier_id`, `receipt_date`, `total_items`, `status`, `received_by`, `notes`, `created_at`) VALUES (1,'GRN-00011',11,12,'2026-06-22',1.00,'RECEIVED',1,'Auto-created from posted purchase PUR-00011','2026-06-23 17:53:22'), (2,'GRN-00014',14,23,'2026-06-23',3.00,'RECEIVED',1,'Auto-created from posted purchase PUR-00014','2026-06-23 17:54:30'), (3,'GRN-00013',13,1,'2026-06-22',3000.00,'RECEIVED',1,'Auto-created from posted purchase PUR-00013','2026-06-23 17:59:46'), (4,'GRN-00003',3,1,'2026-06-21',3024.00,'RECEIVED',1,'Auto-created from posted purchase PUR-00003','2026-06-23 18:01:52'), (5,'GRN-00002',2,12,'2026-06-21',1.00,'RECEIVED',1,'Auto-created from posted purchase PUR-00002','2026-06-23 18:02:47'), (6,'GRN-00001',1,13,'2026-06-21',60.00,'RECEIVED',1,'Auto-created from posted purchase PUR-00001','2026-06-23 18:03:18'), (7,'GRN-00015',15,23,'2026-06-23',4.00,'RECEIVED',1,'Auto-created from posted purchase PUR-00015','2026-06-23 20:30:08'), (8,'GRN-00016',16,12,'2026-06-25',5.00,'RECEIVED',1,'Auto-created from posted purchase PUR-00016','2026-06-25 17:36:53'), (9,'GRN-00017',17,13,'2026-06-25',5.00,'RECEIVED',1,'Auto-created from posted purchase PUR-00017','2026-06-25 17:38:52'); /*!40000 ALTER TABLE `goods_receipts` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `home_consumption` -- DROP TABLE IF EXISTS `home_consumption`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `home_consumption` ( `id` int(11) NOT NULL AUTO_INCREMENT, `consumption_no` varchar(50) DEFAULT NULL, `business_date` date NOT NULL, `consumption_type` enum('OFFICE_CONSUMPTION','STAFF_WELFARE','OWNER_DRAWINGS','PROMOTION','OTHER') DEFAULT 'OFFICE_CONSUMPTION', `total_value` decimal(12,2) NOT NULL DEFAULT 0.00, `status` enum('DRAFT','POSTED','VOID') DEFAULT 'POSTED', `requested_by` int(11) DEFAULT NULL, `approved_by` int(11) DEFAULT NULL, `notes` text DEFAULT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), `approved_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `consumption_no` (`consumption_no`), KEY `business_date` (`business_date`), KEY `consumption_type` (`consumption_type`), KEY `status` (`status`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `home_consumption` -- LOCK TABLES `home_consumption` WRITE; /*!40000 ALTER TABLE `home_consumption` DISABLE KEYS */; /*!40000 ALTER TABLE `home_consumption` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `home_consumption_items` -- DROP TABLE IF EXISTS `home_consumption_items`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `home_consumption_items` ( `id` int(11) NOT NULL AUTO_INCREMENT, `home_consumption_id` int(11) NOT NULL, `product_id` int(11) DEFAULT NULL, `item_name` varchar(180) DEFAULT NULL, `quantity` decimal(12,2) NOT NULL DEFAULT 0.00, `unit_cost` decimal(12,2) NOT NULL DEFAULT 0.00, `total_cost` decimal(12,2) NOT NULL DEFAULT 0.00, `notes` varchar(255) DEFAULT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `home_consumption_id` (`home_consumption_id`), KEY `product_id` (`product_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `home_consumption_items` -- LOCK TABLES `home_consumption_items` WRITE; /*!40000 ALTER TABLE `home_consumption_items` DISABLE KEYS */; /*!40000 ALTER TABLE `home_consumption_items` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `journal_entries` -- DROP TABLE IF EXISTS `journal_entries`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `journal_entries` ( `id` int(11) NOT NULL AUTO_INCREMENT, `journal_no` varchar(40) DEFAULT NULL, `journal_date` date NOT NULL, `source_module` varchar(50) DEFAULT NULL, `source_id` int(11) DEFAULT NULL, `description` varchar(255) DEFAULT NULL, `total_debit` decimal(12,2) DEFAULT 0.00, `total_credit` decimal(12,2) DEFAULT 0.00, `status` enum('DRAFT','POSTED','VOID') DEFAULT 'POSTED', `created_by` int(11) DEFAULT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `journal_no` (`journal_no`), KEY `journal_date` (`journal_date`), KEY `source_module` (`source_module`), KEY `source_id` (`source_id`), KEY `status` (`status`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `journal_entries` -- LOCK TABLES `journal_entries` WRITE; /*!40000 ALTER TABLE `journal_entries` DISABLE KEYS */; /*!40000 ALTER TABLE `journal_entries` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `journal_entry_lines` -- DROP TABLE IF EXISTS `journal_entry_lines`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `journal_entry_lines` ( `id` int(11) NOT NULL AUTO_INCREMENT, `journal_entry_id` int(11) NOT NULL, `account_id` int(11) NOT NULL, `debit` decimal(12,2) DEFAULT 0.00, `credit` decimal(12,2) DEFAULT 0.00, `narration` varchar(255) DEFAULT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `journal_entry_id` (`journal_entry_id`), KEY `account_id` (`account_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `journal_entry_lines` -- LOCK TABLES `journal_entry_lines` WRITE; /*!40000 ALTER TABLE `journal_entry_lines` DISABLE KEYS */; /*!40000 ALTER TABLE `journal_entry_lines` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `leave_requests` -- DROP TABLE IF EXISTS `leave_requests`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `leave_requests` ( `id` int(11) NOT NULL AUTO_INCREMENT, `staff_id` int(11) NOT NULL, `leave_type` varchar(80) DEFAULT NULL, `start_date` date NOT NULL, `end_date` date NOT NULL, `days_requested` decimal(6,2) DEFAULT 0.00, `reason` text DEFAULT NULL, `status` enum('PENDING','APPROVED','REJECTED') DEFAULT 'PENDING', `approved_by` int(11) DEFAULT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `staff_id` (`staff_id`), KEY `approved_by` (`approved_by`), CONSTRAINT `leave_requests_ibfk_1` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`id`), CONSTRAINT `leave_requests_ibfk_2` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `leave_requests` -- LOCK TABLES `leave_requests` WRITE; /*!40000 ALTER TABLE `leave_requests` DISABLE KEYS */; /*!40000 ALTER TABLE `leave_requests` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `loyalty_redemptions` -- DROP TABLE IF EXISTS `loyalty_redemptions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `loyalty_redemptions` ( `id` int(11) NOT NULL AUTO_INCREMENT, `customer_id` int(11) NOT NULL, `redemption_date` date NOT NULL, `points_redeemed` decimal(12,2) NOT NULL DEFAULT 0.00, `redemption_value` decimal(12,2) NOT NULL DEFAULT 0.00, `redemption_type` enum('DISCOUNT','MANUAL') DEFAULT 'DISCOUNT', `reference_type` varchar(60) DEFAULT NULL, `reference_id` int(11) DEFAULT NULL, `status` enum('POSTED','REVERSED') DEFAULT 'POSTED', `notes` text DEFAULT NULL, `approved_by` int(11) DEFAULT NULL, `created_by` int(11) DEFAULT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `customer_id` (`customer_id`), KEY `redemption_date` (`redemption_date`), KEY `status` (`status`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `loyalty_redemptions` -- LOCK TABLES `loyalty_redemptions` WRITE; /*!40000 ALTER TABLE `loyalty_redemptions` DISABLE KEYS */; /*!40000 ALTER TABLE `loyalty_redemptions` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `loyalty_settings` -- DROP TABLE IF EXISTS `loyalty_settings`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `loyalty_settings` ( `id` int(11) NOT NULL AUTO_INCREMENT, `setting_key` varchar(100) NOT NULL, `setting_value` varchar(255) DEFAULT NULL, `description` varchar(255) DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `setting_key` (`setting_key`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `loyalty_settings` -- LOCK TABLES `loyalty_settings` WRITE; /*!40000 ALTER TABLE `loyalty_settings` DISABLE KEYS */; INSERT INTO `loyalty_settings` (`id`, `setting_key`, `setting_value`, `description`, `updated_at`) VALUES (1,'loyalty_enabled','YES','Enable customer loyalty points',NULL), (2,'points_per_kes','0.01','Points earned per KES spent. Example 0.01 means 1 point per KES 100',NULL), (3,'minimum_redeem_points','100','Minimum points required before customer can redeem',NULL), (4,'point_value_kes','1','KES value of one loyalty point when redeemed',NULL), (5,'allow_discount_redemption','YES','Allow loyalty points to be redeemed as sales discount',NULL), (6,'max_redeem_percent_of_sale','50','Maximum percentage of sale value redeemable using points',NULL), (7,'loyalty_expiry_days','365','Number of days before points expire; 0 means no expiry',NULL); /*!40000 ALTER TABLE `loyalty_settings` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `loyalty_transactions` -- DROP TABLE IF EXISTS `loyalty_transactions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `loyalty_transactions` ( `id` int(11) NOT NULL AUTO_INCREMENT, `customer_id` int(11) NOT NULL, `transaction_date` date NOT NULL, `transaction_type` enum('EARN','REDEEM','ADJUSTMENT','REVERSAL','EXPIRY') NOT NULL, `points` decimal(12,2) NOT NULL DEFAULT 0.00, `reference_type` varchar(60) DEFAULT NULL, `reference_id` int(11) DEFAULT NULL, `sale_amount` decimal(12,2) DEFAULT 0.00, `redemption_value` decimal(12,2) DEFAULT 0.00, `balance_after` decimal(12,2) DEFAULT 0.00, `notes` text DEFAULT NULL, `created_by` int(11) DEFAULT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `customer_id` (`customer_id`), KEY `transaction_date` (`transaction_date`), KEY `transaction_type` (`transaction_type`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `loyalty_transactions` -- LOCK TABLES `loyalty_transactions` WRITE; /*!40000 ALTER TABLE `loyalty_transactions` DISABLE KEYS */; /*!40000 ALTER TABLE `loyalty_transactions` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `operational_settings` -- DROP TABLE IF EXISTS `operational_settings`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `operational_settings` ( `id` int(11) NOT NULL AUTO_INCREMENT, `setting_key` varchar(100) NOT NULL, `setting_value` varchar(255) DEFAULT NULL, `description` varchar(255) DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `setting_key` (`setting_key`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `operational_settings` -- LOCK TABLES `operational_settings` WRITE; /*!40000 ALTER TABLE `operational_settings` DISABLE KEYS */; INSERT INTO `operational_settings` (`id`, `setting_key`, `setting_value`, `description`, `updated_at`) VALUES (1,'default_opening_float','2000','Default cash float retained in till every business day',NULL), (2,'allow_supervisor_float_override','YES','Allows supervisor to change opening float when opening till',NULL), (3,'enable_cash_till','YES','Enable cash till tracking',NULL), (4,'enable_mpesa_till','YES','Enable M-Pesa till tracking',NULL), (5,'enable_bank_till','YES','Enable bank payment till tracking',NULL), (6,'enable_home_consumption','YES','Enable home/office/staff/owner consumption tracking',NULL), (7,'require_till_close_before_next_day','YES','Require till closure before starting new business date',NULL), (8,'auto_bank_cash_at_close','YES','Suggest cash banking at close of day',NULL), (9,'auto_bank_mpesa_at_close','YES','Suggest M-Pesa banking at close of day',NULL); /*!40000 ALTER TABLE `operational_settings` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `pos_approvals` -- DROP TABLE IF EXISTS `pos_approvals`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `pos_approvals` ( `id` int(11) NOT NULL AUTO_INCREMENT, `approval_type` varchar(80) NOT NULL, `reference_type` varchar(80) DEFAULT NULL, `reference_id` int(11) DEFAULT NULL, `approved_by` int(11) DEFAULT NULL, `approval_note` text DEFAULT NULL, `created_by` int(11) DEFAULT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `approval_type` (`approval_type`), KEY `reference_type` (`reference_type`), KEY `reference_id` (`reference_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `pos_approvals` -- LOCK TABLES `pos_approvals` WRITE; /*!40000 ALTER TABLE `pos_approvals` DISABLE KEYS */; /*!40000 ALTER TABLE `pos_approvals` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `pos_settings` -- DROP TABLE IF EXISTS `pos_settings`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `pos_settings` ( `id` int(11) NOT NULL AUTO_INCREMENT, `setting_key` varchar(120) NOT NULL, `setting_value` varchar(255) DEFAULT NULL, `description` varchar(255) DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `setting_key` (`setting_key`) ) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `pos_settings` -- LOCK TABLES `pos_settings` WRITE; /*!40000 ALTER TABLE `pos_settings` DISABLE KEYS */; INSERT INTO `pos_settings` (`id`, `setting_key`, `setting_value`, `description`, `updated_at`) VALUES (1,'pos_enabled','YES','Enable POS Professional',NULL), (2,'allow_wallet_payment','YES','Allow customer wallet payment at POS',NULL), (3,'allow_credit_sales','YES','Allow credit sales',NULL), (4,'allow_home_consumption','YES','Allow home consumption sales',NULL), (5,'receipt_prefix','RCP','Receipt number prefix',NULL), (6,'supervisor_override_code','1234','Temporary supervisor code for POS discount approval. Change before live use.',NULL), (7,'price_override_allowed','NO','Price changes should be done through approved discounts only.',NULL); /*!40000 ALTER TABLE `pos_settings` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `product_bom` -- DROP TABLE IF EXISTS `product_bom`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `product_bom` ( `id` int(11) NOT NULL AUTO_INCREMENT, `product_id` int(11) NOT NULL, `component_product_id` int(11) NOT NULL, `quantity` decimal(12,2) NOT NULL DEFAULT 1.00, `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `product_bom` -- LOCK TABLES `product_bom` WRITE; /*!40000 ALTER TABLE `product_bom` DISABLE KEYS */; /*!40000 ALTER TABLE `product_bom` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `products` -- DROP TABLE IF EXISTS `products`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `products` ( `id` int(11) NOT NULL AUTO_INCREMENT, `category_id` int(11) NOT NULL, `sub_category_id` int(11) DEFAULT NULL, `brand_id` int(11) DEFAULT NULL, `size` varchar(30) DEFAULT NULL, `product_type` varchar(60) DEFAULT NULL, `pos_category` varchar(50) DEFAULT NULL, `pos_group` varchar(80) DEFAULT NULL, `product_code` varchar(50) DEFAULT NULL, `product_name` varchar(180) NOT NULL, `unit_of_measure` varchar(40) DEFAULT 'Unit', `cost_price` decimal(12,2) DEFAULT 0.00, `selling_price` decimal(12,2) DEFAULT 0.00, `reorder_level` decimal(12,2) DEFAULT 0.00, `stock_balance` decimal(12,2) DEFAULT 0.00, `is_stock_item` tinyint(1) DEFAULT 1, `status` enum('ACTIVE','INACTIVE') DEFAULT 'ACTIVE', `created_by` int(11) DEFAULT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `product_code` (`product_code`), KEY `category_id` (`category_id`), KEY `created_by` (`created_by`), KEY `idx_products_sub_category_id` (`sub_category_id`), KEY `idx_products_brand_id` (`brand_id`), CONSTRAINT `products_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`), CONSTRAINT `products_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=111 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `products` -- LOCK TABLES `products` WRITE; /*!40000 ALTER TABLE `products` DISABLE KEYS */; INSERT INTO `products` (`id`, `category_id`, `sub_category_id`, `brand_id`, `size`, `product_type`, `pos_category`, `pos_group`, `product_code`, `product_name`, `unit_of_measure`, `cost_price`, `selling_price`, `reorder_level`, `stock_balance`, `is_stock_item`, `status`, `created_by`, `created_at`, `updated_at`) VALUES (1,1,NULL,NULL,NULL,NULL,'Water','Water Refill','W-005-REFILL','0.5 Litre Refill','Litre',1.00,5.00,0.00,0.00,0,'ACTIVE',NULL,'2026-06-14 07:39:02','2026-06-30 09:57:15'), (2,1,NULL,NULL,NULL,NULL,'Water','Water Refill','W-010-REFILL','1.0 Litre Refill','Litre',2.00,10.00,0.00,0.00,0,'ACTIVE',NULL,'2026-06-14 07:39:02','2026-06-30 09:57:15'), (3,1,NULL,NULL,NULL,NULL,'Water','Water Refill','W-050-REFILL','5.0 Litre Refill','Litre',10.00,50.00,0.00,0.00,0,'ACTIVE',NULL,'2026-06-14 07:39:02','2026-06-30 09:57:15'), (4,1,NULL,NULL,NULL,NULL,'Water','Water Refill','W-100-REFILL','10.0 Litre Refill','Litre',34.00,100.00,0.00,0.00,0,'ACTIVE',NULL,'2026-06-14 07:39:02','2026-06-30 09:57:15'), (5,1,NULL,NULL,NULL,NULL,'Water','Water Refill','W-200-REFILL','20.0 Litre Refill','Litre',54.00,200.00,0.00,0.00,0,'ACTIVE',NULL,'2026-06-14 07:39:02','2026-06-30 09:57:15'), (13,1,NULL,NULL,NULL,NULL,'Water','Water Refill','W-TAP','Tap water','Litre',1.00,2.00,0.00,0.00,0,'ACTIVE',NULL,'2026-06-14 07:39:02','2026-06-30 09:57:15'), (18,14,33,24,'6kg','Gas Cylinder','Containers','Gas Cylinder','CYL-AFRIGAS-6KG','Gas Cylinder','Unit',0.00,0.00,2.00,0.00,1,'ACTIVE',NULL,'2026-06-14 07:39:02','2026-06-28 15:09:18'), (19,14,33,24,'13kg','Gas Cylinder','Containers','Gas Cylinder','CYL-AFRIGAS-13KG','Gas Cylinder','Unit',0.00,0.00,2.00,1.00,1,'ACTIVE',NULL,'2026-06-14 07:39:02','2026-06-28 15:09:18'), (20,14,33,25,'6kg','Gas Cylinder','Containers','Gas Cylinder','CYL-TOTAL-6KG','Gas Cylinder','Unit',0.00,0.00,2.00,0.00,1,'ACTIVE',NULL,'2026-06-14 07:39:02','2026-06-28 15:09:18'), (21,14,33,25,'13kg','Gas Cylinder','Containers','Gas Cylinder','CYL-TOTAL-13KG','Gas Cylinder','Unit',0.00,0.00,2.00,0.00,1,'ACTIVE',NULL,'2026-06-14 07:39:02','2026-06-28 15:09:18'), (22,14,33,22,'6kg','Gas Cylinder','Containers','Gas Cylinder','CYL-OTHER-6KG','Gas Cylinder','Unit',1900.00,2400.00,2.00,0.00,1,'ACTIVE',NULL,'2026-06-14 07:39:02','2026-06-28 15:09:18'), (23,14,33,22,'13kg','Gas Cylinder','Containers','Gas Cylinder','CYL-OTHER-13KG','Gas Cylinder','Unit',0.00,3500.00,2.00,0.00,1,'ACTIVE',NULL,'2026-06-14 07:39:02','2026-06-28 15:09:18'), (32,3,NULL,NULL,NULL,NULL,'Accessories','Accessories','ACC-CLIPS','Clips','Unit',15.00,30.00,10.00,0.00,1,'ACTIVE',NULL,'2026-06-14 07:39:02','2026-06-28 15:09:18'), (38,3,NULL,NULL,NULL,NULL,'Accessories','Accessories','ACC-PIPE','Pipe','Metre',120.00,200.00,10.00,0.00,1,'ACTIVE',NULL,'2026-06-14 07:39:02','2026-06-28 15:09:18'), (39,2,7,2,'6kg','Gas Refill','Gas','Gas Refill','GAS-AFR-6KG-REFILL','Gas Refill','Kg',900.00,1100.00,4.00,2.00,1,'ACTIVE',1,'2026-06-14 19:10:03','2026-06-30 11:00:52'), (41,3,10,15,NULL,NULL,'Accessories','Accessories','ACC-BURNER-PRIMAX','Burner','Unit',130.00,300.00,2.00,0.00,1,'ACTIVE',1,'2026-06-17 17:29:51','2026-06-28 15:09:18'), (42,3,10,16,NULL,NULL,'Accessories','Accessories','ACC-BURNER-OGAZ','Burner','Unit',130.00,300.00,2.00,0.00,1,'ACTIVE',1,'2026-06-17 17:29:51','2026-06-28 15:09:18'), (43,3,10,17,NULL,NULL,'Accessories','Accessories','ACC-BURNER-PINE','Burner','Unit',200.00,450.00,2.00,0.00,1,'ACTIVE',1,'2026-06-17 17:29:51','2026-06-28 15:09:18'), (44,3,10,18,NULL,NULL,'Accessories','Accessories','ACC-BURNER-LITEGAS','Burner','Unit',0.00,0.00,2.00,0.00,1,'ACTIVE',1,'2026-06-17 17:29:51','2026-06-28 15:09:18'), (45,3,10,19,NULL,NULL,'Accessories','Accessories','ACC-BURNER-HADEGAZ','Burner','Unit',0.00,0.00,2.00,0.00,1,'ACTIVE',1,'2026-06-17 17:29:51','2026-06-28 15:09:18'), (46,3,10,20,NULL,NULL,'Accessories','Accessories','ACC-BURNER-HR','Burner','Unit',250.00,500.00,2.00,0.00,1,'ACTIVE',1,'2026-06-17 17:29:51','2026-06-28 15:09:18'), (47,3,9,3,'6kg',NULL,'Accessories','Accessories','ACC-REG-TOTAL-6KG','Regulator - Total','Unit',850.00,1100.00,2.00,0.00,1,'ACTIVE',1,'2026-06-17 17:29:51','2026-06-28 15:09:18'), (48,3,9,3,'13kg',NULL,'Accessories','Accessories','ACC-REG-TOTAL-13KG','Regulator - Total','Unit',900.00,1100.00,2.00,0.00,1,'ACTIVE',1,'2026-06-17 17:29:51','2026-06-28 15:09:18'), (49,3,12,NULL,NULL,NULL,'Accessories','Accessories','ACC-GRILL-LIGHT','Light Gauge Grill','Unit',205.00,300.00,2.00,0.00,1,'ACTIVE',1,'2026-06-17 17:29:51','2026-06-28 15:09:18'), (50,3,12,NULL,NULL,NULL,'Accessories','Accessories','ACC-GRILL-MEDIUM','Medium Gauge Grill','Unit',200.00,400.00,2.00,5.00,1,'ACTIVE',1,'2026-06-17 17:29:51','2026-06-28 15:09:18'), (51,3,12,NULL,NULL,NULL,'Accessories','Accessories','ACC-GRILL-HEAVY','Heavy Gauge Grill','Unit',350.00,600.00,2.00,0.00,1,'ACTIVE',1,'2026-06-17 17:29:51','2026-06-28 15:09:18'), (52,3,13,NULL,NULL,NULL,'Accessories','Accessories','ACC-PUMP-PORTABLE-AUTO','Portable Automatic Water Pump','Unit',500.00,700.00,2.00,0.00,1,'ACTIVE',1,'2026-06-17 17:29:51','2026-06-28 15:09:18'), (53,3,13,NULL,NULL,NULL,'Accessories','Accessories','ACC-DISPENSER-AUTO','Automatic Water Dispenser','Unit',350.00,500.00,2.00,0.00,1,'ACTIVE',1,'2026-06-17 17:29:51','2026-06-28 15:09:18'), (54,13,20,NULL,NULL,NULL,'Accessories','Accessories','PACK-WCAP','Water Caps','Unit',0.00,0.00,50.00,0.00,1,'ACTIVE',1,'2026-06-18 15:38:45','2026-06-28 15:09:18'), (55,13,21,NULL,NULL,NULL,'Accessories','Accessories','PACK-WSEAL','Water Security Seals','Unit',0.00,0.00,50.00,0.00,1,'ACTIVE',1,'2026-06-18 15:38:45','2026-06-28 15:09:18'), (56,13,22,NULL,NULL,NULL,'Accessories','Accessories','PACK-WSTICK','Water Stickers','Unit',0.00,0.00,50.00,0.00,1,'ACTIVE',1,'2026-06-18 15:38:45','2026-06-28 15:09:18'), (57,13,23,NULL,NULL,NULL,'Accessories','Accessories','PACK-GCAP','Gas Caps','Unit',0.00,0.00,50.00,0.00,1,'ACTIVE',1,'2026-06-18 15:38:45','2026-06-28 15:09:18'), (58,13,24,NULL,NULL,NULL,'Accessories','Accessories','PACK-GSEAL','Gas Security Seals','Unit',0.00,0.00,50.00,0.00,1,'ACTIVE',1,'2026-06-18 15:38:45','2026-06-28 15:09:18'), (59,14,32,22,'5L','Soft Bottle','Accessories','Accessories','BOT-5L','Soft Water Bottle','Unit',0.00,0.00,5.00,3.00,1,'ACTIVE',1,'2026-06-18 15:38:45','2026-06-28 15:09:18'), (60,14,32,22,'10L','Soft Bottle','Accessories','Accessories','BOT-10L','Soft Water Bottle','Unit',0.00,0.00,5.00,4.00,1,'ACTIVE',1,'2026-06-18 15:38:45','2026-06-28 15:09:18'), (61,14,32,22,'20L','Soft Bottle','Accessories','Accessories','BOT-20L-SOFT','Soft Water Bottle','Unit',0.00,0.00,5.00,0.00,1,'ACTIVE',1,'2026-06-18 15:38:45','2026-06-28 15:09:18'), (62,14,32,21,'20L','Hard Bottle','Containers','Water Bottle','BOT-20L-CLEAR','Fine Line Clear Water Bottle','Unit',0.00,0.00,5.00,0.00,1,'ACTIVE',1,'2026-06-18 15:38:45','2026-06-28 15:09:18'), (63,14,32,21,'20L','Hard Bottle','Containers','Water Bottle','BOT-20L-BLUE','Fine Line Blue Water Bottle','Unit',0.00,0.00,5.00,0.00,1,'ACTIVE',1,'2026-06-18 15:38:45','2026-06-28 15:09:18'), (64,14,32,22,'20L','Hard Bottle','Accessories','Accessories','BOT-20L-HARD','Hard Water Bottle With Handle','Unit',0.00,0.00,5.00,0.00,1,'ACTIVE',1,'2026-06-18 15:38:45','2026-06-28 15:09:18'), (65,12,17,12,'0.5L',NULL,'Water','Bottled Water','WOY-05L','Bottled Water','Bottle',18.75,30.00,10.00,19.00,1,'ACTIVE',1,'2026-06-18 15:38:45','2026-06-29 20:33:32'), (66,12,17,12,'1L',NULL,'Water','Bottled Water','WOY-1L','Bottled Water','Bottle',37.50,50.00,10.00,0.00,1,'ACTIVE',1,'2026-06-18 15:38:45','2026-06-28 15:09:18'), (67,12,18,13,'0.5L',NULL,'Water','Bottled Water','AQUA-05L','Bottled Water','Bottle',14.20,30.00,10.00,0.00,1,'ACTIVE',1,'2026-06-18 15:38:45','2026-06-28 15:09:18'), (68,12,18,13,'1L',NULL,'Water','Bottled Water','AQUA-1L','Bottled Water','Bottle',28.40,50.00,10.00,0.00,1,'ACTIVE',1,'2026-06-18 15:38:45','2026-06-28 15:09:18'), (69,1,NULL,NULL,NULL,NULL,'Water','Main Tank','WR-BULK-WATER','Bulk Purified Water','Litre',0.00,0.00,100.00,6000.00,1,'INACTIVE',1,'2026-06-18 15:38:45','2026-06-30 09:57:15'), (70,14,33,23,'6kg','Gas Cylinder','Containers','Gas Cylinder','CYL-PRO-6KG','Gas Cylinder','Unit',0.00,0.00,1.00,0.00,1,'ACTIVE',1,'2026-06-19 16:00:12','2026-06-28 15:09:18'), (71,14,33,23,'13kg','Gas Cylinder','Containers','Gas Cylinder','CYL-PRO-13KG','Gas Cylinder','Unit',0.00,0.00,1.00,0.00,1,'ACTIVE',1,'2026-06-19 16:00:12','2026-06-28 15:09:18'), (72,14,33,26,'6kg','Gas Cylinder','Containers','Gas Cylinder','CYL-HASHI-6KG','Gas Cylinder','Unit',0.00,0.00,1.00,0.00,1,'ACTIVE',1,'2026-06-19 16:00:12','2026-06-28 15:09:18'), (73,14,33,26,'13kg','Gas Cylinder','Containers','Gas Cylinder','CYL-HASHI-13KG','Gas Cylinder','Unit',0.00,0.00,1.00,0.00,1,'ACTIVE',1,'2026-06-19 16:00:12','2026-06-28 15:09:18'), (74,14,33,27,'6kg','Gas Cylinder','Containers','Gas Cylinder','CYL-KGAS-6KG','Gas Cylinder','Unit',0.00,0.00,1.00,0.00,1,'ACTIVE',1,'2026-06-19 16:00:12','2026-06-28 15:09:18'), (75,14,33,27,'13kg','Gas Cylinder','Containers','Gas Cylinder','CYL-KGAS-13KG','Gas Cylinder','Unit',0.00,0.00,1.00,0.00,1,'ACTIVE',1,'2026-06-19 16:00:12','2026-06-28 15:09:18'), (76,14,33,28,'6kg','Gas Cylinder','Containers','Gas Cylinder','CYL-SUPA-6KG','Gas Cylinder','Unit',0.00,0.00,1.00,1.00,1,'ACTIVE',1,'2026-06-19 16:00:12','2026-06-28 15:09:18'), (77,14,33,28,'13kg','Gas Cylinder','Containers','Gas Cylinder','CYL-SUPA-13KG','Gas Cylinder','Unit',0.00,0.00,1.00,0.00,1,'ACTIVE',1,'2026-06-19 16:00:12','2026-06-28 15:09:18'), (78,14,33,29,'6kg','Gas Cylinder','Containers','Gas Cylinder','CYL-MPISHI-6KG','Gas Cylinder','Unit',0.00,0.00,1.00,0.00,1,'ACTIVE',1,'2026-06-19 16:00:12','2026-06-28 15:09:18'), (79,14,33,29,'13kg','Gas Cylinder','Containers','Gas Cylinder','CYL-MPISHI-13KG','Gas Cylinder','Unit',0.00,0.00,1.00,0.00,1,'ACTIVE',1,'2026-06-19 16:00:12','2026-06-28 15:09:18'), (80,14,33,30,'6kg','Gas Cylinder','Containers','Gas Cylinder','CYL-TAIFA-6KG','Gas Cylinder','Unit',0.00,0.00,1.00,5.00,1,'ACTIVE',1,'2026-06-19 16:00:12','2026-06-28 15:09:18'), (81,14,33,30,'13kg','Gas Cylinder','Containers','Gas Cylinder','CYL-TAIFA-13KG','Gas Cylinder','Unit',0.00,0.00,1.00,0.00,1,'ACTIVE',1,'2026-06-19 16:00:12','2026-06-28 15:09:18'), (82,14,33,31,'6kg','Gas Cylinder','Containers','Gas Cylinder','CYL-SEA-6KG','Gas Cylinder','Unit',0.00,0.00,1.00,0.00,1,'ACTIVE',1,'2026-06-19 16:00:12','2026-06-28 15:09:18'), (83,14,33,31,'13kg','Gas Cylinder','Containers','Gas Cylinder','CYL-SEA-13KG','Gas Cylinder','Unit',0.00,0.00,1.00,0.00,1,'ACTIVE',1,'2026-06-19 16:00:12','2026-06-28 15:09:18'), (84,14,33,32,'6kg','Gas Cylinder','Containers','Gas Cylinder','CYL-LAKE-6KG','Gas Cylinder','Unit',0.00,0.00,1.00,0.00,1,'ACTIVE',1,'2026-06-19 16:00:12','2026-06-28 15:09:18'), (85,14,33,32,'13kg','Gas Cylinder','Containers','Gas Cylinder','CYL-LAKE-13KG','Gas Cylinder','Unit',0.00,0.00,1.00,0.00,1,'ACTIVE',1,'2026-06-19 16:00:12','2026-06-28 15:09:18'), (86,14,33,33,'6kg','Gas Cylinder','Containers','Gas Cylinder','CYL-GULF-6KG','Gas Cylinder','Unit',0.00,0.00,1.00,0.00,1,'ACTIVE',1,'2026-06-19 16:00:12','2026-06-28 15:09:18'), (87,14,33,33,'13kg','Gas Cylinder','Containers','Gas Cylinder','CYL-GULF-13KG','Gas Cylinder','Unit',0.00,0.00,1.00,0.00,1,'ACTIVE',1,'2026-06-19 16:00:12','2026-06-28 15:09:18'), (88,2,7,1,'6kg','Gas Refill','Gas','Gas Refill','GAS-PRO-6KG-REFILL','Gas Refill','Kg',0.00,1100.00,4.00,0.00,1,'ACTIVE',1,'2026-06-19 16:51:45','2026-06-28 15:09:18'), (89,2,7,1,'13kg','Gas Refill','Gas','Gas Refill','GAS-PRO-13KG-REFILL','Gas Refill','Kg',0.00,2200.00,4.00,0.00,1,'ACTIVE',1,'2026-06-19 16:51:45','2026-06-28 15:09:18'), (90,2,7,2,'13kg','Gas Refill','Gas','Gas Refill','GAS-AFR-13KG-REFILL','Gas Refill','Kg',0.00,2200.00,4.00,0.00,1,'ACTIVE',1,'2026-06-19 16:51:45','2026-06-28 15:09:18'), (91,2,7,3,'6kg','Gas Refill','Gas','Gas Refill','GAS-TOTAL-6KG-REFILL','Gas Refill','Kg',0.00,1100.00,4.00,0.00,1,'ACTIVE',1,'2026-06-19 16:51:45','2026-06-28 15:09:18'), (92,2,7,3,'13kg','Gas Refill','Gas','Gas Refill','GAS-TOTAL-13KG-REFILL','Gas Refill','Kg',0.00,2200.00,4.00,0.00,1,'ACTIVE',1,'2026-06-19 16:51:45','2026-06-28 15:09:18'), (93,2,7,4,'6kg','Gas Refill','Gas','Gas Refill','GAS-HASHI-6KG-REFILL','Gas Refill','Kg',0.00,1100.00,4.00,0.00,1,'ACTIVE',1,'2026-06-19 16:51:45','2026-06-28 15:09:18'), (94,2,7,4,'13kg','Gas Refill','Gas','Gas Refill','GAS-HASHI-13KG-REFILL','Gas Refill','Kg',0.00,2200.00,4.00,0.00,1,'ACTIVE',1,'2026-06-19 16:51:45','2026-06-28 15:09:18'), (95,2,7,5,'6kg','Gas Refill','Gas','Gas Refill','GAS-KGAS-6KG-REFILL','Gas Refill','Kg',0.00,1100.00,4.00,0.00,1,'ACTIVE',1,'2026-06-19 16:51:45','2026-06-28 15:09:18'), (96,2,7,5,'13kg','Gas Refill','Gas','Gas Refill','GAS-KGAS-13KG-REFILL','Gas Refill','Kg',0.00,2200.00,4.00,0.00,1,'ACTIVE',1,'2026-06-19 16:51:45','2026-06-28 15:09:18'), (97,2,7,6,'6kg','Gas Refill','Gas','Gas Refill','GAS-SUPA-6KG-REFILL','Gas Refill','Kg',0.00,1100.00,4.00,0.00,1,'ACTIVE',1,'2026-06-19 16:51:45','2026-06-28 15:09:18'), (98,2,7,6,'13kg','Gas Refill','Gas','Gas Refill','GAS-SUPA-13KG-REFILL','Gas Refill','Kg',0.00,2200.00,4.00,0.00,1,'ACTIVE',1,'2026-06-19 16:51:45','2026-06-28 15:09:18'), (99,2,7,7,'6kg','Gas Refill','Gas','Gas Refill','GAS-MPISHI-6KG-REFILL','Gas Refill','Kg',0.00,1100.00,4.00,0.00,1,'ACTIVE',1,'2026-06-19 16:51:45','2026-06-28 15:09:18'), (100,2,7,7,'13kg','Gas Refill','Gas','Gas Refill','GAS-MPISHI-13KG-REFILL','Gas Refill','Kg',0.00,2200.00,4.00,0.00,1,'ACTIVE',1,'2026-06-19 16:51:45','2026-06-28 15:09:18'), (101,2,7,8,'6kg','Gas Refill','Gas','Gas Refill','GAS-TAIFA-6KG-REFILL','Gas Refill','Kg',0.00,1100.00,4.00,0.00,1,'ACTIVE',1,'2026-06-19 16:51:45','2026-06-28 15:09:18'), (102,2,7,8,'13kg','Gas Refill','Gas','Gas Refill','GAS-TAIFA-13KG-REFILL','Gas Refill','Kg',0.00,2200.00,4.00,0.00,1,'ACTIVE',1,'2026-06-19 16:51:45','2026-06-28 15:09:18'), (103,2,7,9,'6kg','Gas Refill','Gas','Gas Refill','GAS-SEA-6KG-REFILL','Gas Refill','Kg',0.00,1100.00,4.00,0.00,1,'ACTIVE',1,'2026-06-19 16:51:45','2026-06-28 15:09:18'), (104,2,7,9,'13kg','Gas Refill','Gas','Gas Refill','GAS-SEA-13KG-REFILL','Gas Refill','Kg',0.00,2200.00,4.00,0.00,1,'ACTIVE',1,'2026-06-19 16:51:45','2026-06-28 15:09:18'), (105,2,7,10,'6kg','Gas Refill','Gas','Gas Refill','GAS-LAKE-6KG-REFILL','Gas Refill','Kg',0.00,1100.00,4.00,0.00,1,'ACTIVE',1,'2026-06-19 16:51:45','2026-06-28 15:09:18'), (106,2,7,10,'13kg','Gas Refill','Gas','Gas Refill','GAS-LAKE-13KG-REFILL','Gas Refill','Kg',0.00,2200.00,4.00,60.00,1,'ACTIVE',1,'2026-06-19 16:51:45','2026-06-28 15:09:18'), (107,2,7,11,'6kg','Gas Refill','Gas','Gas Refill','GAS-GULF-6KG-REFILL','Gas Refill','Kg',0.00,1100.00,4.00,0.00,1,'ACTIVE',1,'2026-06-19 16:51:45','2026-06-28 15:09:18'), (108,2,7,11,'13kg','Gas Refill','Gas','Gas Refill','GAS-GULF-13KG-REFILL','Gas Refill','Kg',0.00,2200.00,4.00,0.00,1,'ACTIVE',1,'2026-06-19 16:51:45','2026-06-28 15:09:18'), (110,12,NULL,NULL,'Litres','Water','Water','Main Tank','W-MAIN-TANK','Main Water Tank','Litre',0.00,0.00,0.00,5399.00,1,'ACTIVE',NULL,'2026-06-30 09:19:48','2026-06-30 17:52:51'); /*!40000 ALTER TABLE `products` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `purchase_items` -- DROP TABLE IF EXISTS `purchase_items`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `purchase_items` ( `id` int(11) NOT NULL AUTO_INCREMENT, `purchase_id` int(11) NOT NULL, `product_id` int(11) DEFAULT NULL, `item_type` enum('STOCK','ASSET','EXPENSE','OTHER') DEFAULT 'STOCK', `item_name` varchar(180) DEFAULT NULL, `quantity` decimal(12,2) NOT NULL, `unit_cost` decimal(12,2) NOT NULL, `line_total` decimal(12,2) NOT NULL, `tax_amount` decimal(12,2) DEFAULT 0.00, `notes` varchar(255) DEFAULT NULL, `status` enum('ACTIVE','CANCELLED') DEFAULT 'ACTIVE', PRIMARY KEY (`id`), KEY `purchase_id` (`purchase_id`), KEY `product_id` (`product_id`), CONSTRAINT `purchase_items_ibfk_1` FOREIGN KEY (`purchase_id`) REFERENCES `purchases` (`id`) ON DELETE CASCADE, CONSTRAINT `purchase_items_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `purchase_items` -- LOCK TABLES `purchase_items` WRITE; /*!40000 ALTER TABLE `purchase_items` DISABLE KEYS */; INSERT INTO `purchase_items` (`id`, `purchase_id`, `product_id`, `item_type`, `item_name`, `quantity`, `unit_cost`, `line_total`, `tax_amount`, `notes`, `status`) VALUES (1,1,106,'STOCK','',60.00,160.00,9600.00,0.00,'','ACTIVE'), (2,2,76,'STOCK','',1.00,3500.00,3500.00,0.00,'','ACTIVE'), (3,3,69,'STOCK','',3000.00,2.00,6000.00,0.00,'','ACTIVE'), (4,3,65,'STOCK','',24.00,25.00,600.00,0.00,'','ACTIVE'), (5,8,NULL,'EXPENSE','Trading License',1.00,12000.00,12000.00,0.00,'','ACTIVE'), (6,9,NULL,'STOCK','',1.00,3000.00,3000.00,0.00,'','ACTIVE'), (7,10,NULL,'STOCK','',1.00,3000.00,3000.00,0.00,'','ACTIVE'), (8,11,19,'STOCK','',1.00,500.00,500.00,0.00,'','ACTIVE'), (9,12,NULL,'OTHER','',1.00,2000.00,2000.00,0.00,'','ACTIVE'), (10,13,69,'STOCK','',3000.00,2.00,6000.00,0.00,'','ACTIVE'), (11,14,59,'STOCK','',3.00,2000.00,6000.00,0.00,'','ACTIVE'), (12,15,60,'STOCK','',4.00,2000.00,8000.00,0.00,'','ACTIVE'), (13,16,80,'STOCK','',5.00,3000.00,15000.00,0.00,'','ACTIVE'), (14,17,50,'STOCK','',5.00,400.00,2000.00,0.00,'','ACTIVE'); /*!40000 ALTER TABLE `purchase_items` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `purchase_payments` -- DROP TABLE IF EXISTS `purchase_payments`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `purchase_payments` ( `id` int(11) NOT NULL AUTO_INCREMENT, `purchase_id` int(11) NOT NULL, `supplier_id` int(11) NOT NULL, `payment_date` date NOT NULL, `payment_mode` enum('CASH','MPESA','BANK','CREDIT','OTHER') DEFAULT 'CASH', `amount` decimal(12,2) NOT NULL DEFAULT 0.00, `reference_no` varchar(100) DEFAULT NULL, `notes` text DEFAULT NULL, `created_by` int(11) DEFAULT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `purchase_id` (`purchase_id`), KEY `supplier_id` (`supplier_id`), KEY `payment_date` (`payment_date`) ) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `purchase_payments` -- LOCK TABLES `purchase_payments` WRITE; /*!40000 ALTER TABLE `purchase_payments` DISABLE KEYS */; INSERT INTO `purchase_payments` (`id`, `purchase_id`, `supplier_id`, `payment_date`, `payment_mode`, `amount`, `reference_no`, `notes`, `created_by`, `created_at`) VALUES (1,10,12,'2026-06-22','CASH',3000.00,'PUR-00010','Auto payment from paid purchase',1,'2026-06-23 17:52:02'), (2,11,12,'2026-06-22','CASH',500.00,'PUR-00011','Auto payment from paid purchase',1,'2026-06-23 17:53:22'), (3,14,23,'2026-06-23','CASH',6000.00,'PUR-00014','Auto payment from paid purchase',1,'2026-06-23 17:54:30'), (4,8,21,'2026-06-21','CASH',12000.00,'PUR-00008','Auto payment from paid purchase',1,'2026-06-23 17:57:07'), (5,12,21,'2026-06-22','CASH',2000.00,'PUR-00012','Auto payment from paid purchase',1,'2026-06-23 17:57:48'), (6,13,1,'2026-06-22','CASH',6000.00,'PUR-00013','Auto payment from paid purchase',1,'2026-06-23 17:59:46'), (7,9,21,'2026-06-21','CASH',3000.00,'PUR-00009','Auto payment from paid purchase',1,'2026-06-23 18:00:08'), (8,3,1,'2026-06-21','CASH',6600.00,'PUR-00003','Auto payment from paid purchase',1,'2026-06-23 18:01:52'), (9,2,12,'2026-06-21','CASH',3500.00,'PUR-00002','Auto payment from paid purchase',1,'2026-06-23 18:02:47'), (10,1,13,'2026-06-21','CASH',9600.00,'PUR-00001','Auto payment from paid purchase',1,'2026-06-23 18:03:18'), (11,15,23,'2026-06-23','CASH',8000.00,'PUR-00015','Auto payment from paid purchase',1,'2026-06-23 20:30:08'), (12,16,12,'2026-06-25','CASH',15000.00,'PUR-00016','Auto payment from paid purchase',1,'2026-06-25 17:36:54'), (13,17,13,'2026-06-25','CASH',2000.00,'PUR-00017','Auto payment from paid purchase',1,'2026-06-25 17:38:53'); /*!40000 ALTER TABLE `purchase_payments` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `purchase_settings` -- DROP TABLE IF EXISTS `purchase_settings`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `purchase_settings` ( `id` int(11) NOT NULL AUTO_INCREMENT, `setting_key` varchar(100) NOT NULL, `setting_value` varchar(255) DEFAULT NULL, `description` varchar(255) DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `setting_key` (`setting_key`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `purchase_settings` -- LOCK TABLES `purchase_settings` WRITE; /*!40000 ALTER TABLE `purchase_settings` DISABLE KEYS */; INSERT INTO `purchase_settings` (`id`, `setting_key`, `setting_value`, `description`, `updated_at`) VALUES (1,'enable_mixed_purchases','YES','Allow one invoice to contain stock, asset and expense lines',NULL), (2,'auto_update_stock_on_post','YES','Update product stock when stock purchase is posted',NULL), (3,'auto_create_goods_receipt','YES','Create goods receipt when stock purchase is posted',NULL), (4,'auto_update_supplier_ledger','YES','Update supplier ledger when purchase is posted',NULL), (5,'auto_create_asset_record','YES','Create asset record from asset purchase lines',NULL), (6,'auto_create_expense_record','YES','Create expense record from expense purchase lines',NULL), (7,'default_purchase_status','DRAFT','New purchases start as draft before posting',NULL); /*!40000 ALTER TABLE `purchase_settings` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `purchases` -- DROP TABLE IF EXISTS `purchases`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `purchases` ( `id` int(11) NOT NULL AUTO_INCREMENT, `supplier_id` int(11) DEFAULT NULL, `purchase_no` varchar(60) NOT NULL, `purchase_type` enum('STOCK','ASSET','EXPENSE','MIXED') DEFAULT 'STOCK', `purchase_date` date NOT NULL, `total_amount` decimal(12,2) DEFAULT 0.00, `payment_status` enum('PAID','PARTIAL','CREDIT') DEFAULT 'PAID', `status` enum('DRAFT','POSTED','CANCELLED') DEFAULT 'DRAFT', `created_by` int(11) NOT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), `posted_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `purchase_no` (`purchase_no`), KEY `supplier_id` (`supplier_id`), KEY `created_by` (`created_by`), CONSTRAINT `purchases_ibfk_1` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`), CONSTRAINT `purchases_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `purchases` -- LOCK TABLES `purchases` WRITE; /*!40000 ALTER TABLE `purchases` DISABLE KEYS */; INSERT INTO `purchases` (`id`, `supplier_id`, `purchase_no`, `purchase_type`, `purchase_date`, `total_amount`, `payment_status`, `status`, `created_by`, `created_at`, `posted_at`) VALUES (1,13,'PUR-00001','STOCK','2026-06-21',9600.00,'PAID','POSTED',1,'2026-06-21 22:38:29','2026-06-23 18:03:18'), (2,12,'PUR-00002','STOCK','2026-06-21',3500.00,'PAID','POSTED',1,'2026-06-21 22:39:49','2026-06-23 18:02:47'), (3,1,'PUR-00003','STOCK','2026-06-21',6600.00,'PAID','POSTED',1,'2026-06-21 22:41:00','2026-06-23 18:01:52'), (8,21,'PUR-00008','STOCK','2026-06-21',12000.00,'PAID','POSTED',1,'2026-06-21 22:51:00','2026-06-23 17:57:07'), (9,21,'PUR-00009','STOCK','2026-06-21',3000.00,'PAID','POSTED',1,'2026-06-21 22:51:43','2026-06-23 18:00:08'), (10,12,'PUR-00010','EXPENSE','2026-06-22',3000.00,'PAID','POSTED',1,'2026-06-22 17:13:35','2026-06-23 17:52:02'), (11,12,'PUR-00011','ASSET','2026-06-22',500.00,'PAID','POSTED',1,'2026-06-22 17:48:05','2026-06-23 17:53:22'), (12,21,'PUR-00012','EXPENSE','2026-06-22',2000.00,'PAID','POSTED',1,'2026-06-22 19:08:30','2026-06-23 17:57:48'), (13,1,'PUR-00013','STOCK','2026-06-22',6000.00,'PAID','POSTED',1,'2026-06-22 19:37:44','2026-06-23 17:59:46'), (14,23,'PUR-00014','MIXED','2026-06-23',6000.00,'PAID','POSTED',1,'2026-06-23 15:34:42','2026-06-23 17:54:30'), (15,23,'PUR-00015','MIXED','2026-06-23',8000.00,'PAID','POSTED',1,'2026-06-23 20:30:01','2026-06-23 20:30:08'), (16,12,'PUR-00016','STOCK','2026-06-25',15000.00,'PAID','POSTED',1,'2026-06-25 17:36:42','2026-06-25 17:36:54'), (17,13,'PUR-00017','STOCK','2026-06-25',2000.00,'PAID','POSTED',1,'2026-06-25 17:38:50','2026-06-25 17:38:53'); /*!40000 ALTER TABLE `purchases` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `roles` -- DROP TABLE IF EXISTS `roles`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `roles` ( `id` int(11) NOT NULL AUTO_INCREMENT, `role_name` varchar(50) NOT NULL, `description` varchar(255) DEFAULT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `role_name` (`role_name`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `roles` -- LOCK TABLES `roles` WRITE; /*!40000 ALTER TABLE `roles` DISABLE KEYS */; INSERT INTO `roles` (`id`, `role_name`, `description`, `created_at`) VALUES (1,'SUPER_ADMIN','Full access to the entire ERP','2026-06-14 07:39:02'), (2,'SUPERVISOR','Can approve, delete, and view reports','2026-06-14 07:39:02'), (3,'STAFF','Can add daily operational records','2026-06-14 07:39:02'); /*!40000 ALTER TABLE `roles` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `sale_items` -- DROP TABLE IF EXISTS `sale_items`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `sale_items` ( `id` int(11) NOT NULL AUTO_INCREMENT, `sale_id` int(11) NOT NULL, `product_id` int(11) NOT NULL, `quantity` decimal(12,2) NOT NULL, `unit_price` decimal(12,2) NOT NULL, `cost_price` decimal(12,2) DEFAULT 0.00, `line_total` decimal(12,2) NOT NULL, `line_cost` decimal(12,2) DEFAULT 0.00, `line_profit` decimal(12,2) DEFAULT 0.00, PRIMARY KEY (`id`), KEY `sale_id` (`sale_id`), KEY `product_id` (`product_id`), CONSTRAINT `sale_items_ibfk_1` FOREIGN KEY (`sale_id`) REFERENCES `sales` (`id`) ON DELETE CASCADE, CONSTRAINT `sale_items_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `sale_items` -- LOCK TABLES `sale_items` WRITE; /*!40000 ALTER TABLE `sale_items` DISABLE KEYS */; INSERT INTO `sale_items` (`id`, `sale_id`, `product_id`, `quantity`, `unit_price`, `cost_price`, `line_total`, `line_cost`, `line_profit`) VALUES (1,1,39,1.00,1100.00,900.00,1100.00,900.00,200.00), (2,2,2,1.00,10.00,2.00,10.00,2.00,8.00), (3,2,5,2.00,200.00,54.00,400.00,108.00,292.00), (4,3,5,4.00,200.00,54.00,800.00,216.00,584.00), (5,3,4,4.00,100.00,34.00,400.00,136.00,264.00), (6,4,5,1.00,200.00,54.00,200.00,54.00,146.00), (7,5,4,1.00,100.00,34.00,100.00,34.00,66.00), (8,5,39,1.00,1100.00,900.00,1100.00,900.00,200.00), (9,6,4,1.00,100.00,34.00,100.00,34.00,66.00), (10,6,65,5.00,30.00,18.75,150.00,93.75,56.25), (11,7,39,1.00,1100.00,900.00,1100.00,900.00,200.00), (12,8,5,1.00,200.00,54.00,200.00,54.00,146.00), (13,9,5,15.00,200.00,54.00,3000.00,810.00,2190.00), (14,10,5,15.00,200.00,54.00,3000.00,810.00,2190.00), (15,11,5,15.00,200.00,54.00,3000.00,810.00,2190.00), (16,12,4,2.00,100.00,34.00,200.00,68.00,132.00), (17,13,5,1.00,200.00,54.00,200.00,54.00,146.00), (18,13,39,1.00,1100.00,900.00,1100.00,900.00,200.00), (19,14,5,11.00,200.00,54.00,2200.00,594.00,1606.00), (20,15,5,1.00,200.00,54.00,200.00,54.00,146.00), (21,16,1,1.00,5.00,1.00,5.00,1.00,4.00), (22,17,1,1.00,5.00,1.00,5.00,1.00,4.00), (23,18,5,1.00,200.00,54.00,200.00,54.00,146.00); /*!40000 ALTER TABLE `sale_items` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `sale_payments` -- DROP TABLE IF EXISTS `sale_payments`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `sale_payments` ( `id` int(11) NOT NULL AUTO_INCREMENT, `sale_id` int(11) NOT NULL, `payment_mode` enum('CASH','MPESA','BANK','CREDIT','HOME') NOT NULL, `amount` decimal(12,2) NOT NULL, `reference_no` varchar(100) DEFAULT NULL, `paid_at` datetime DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `sale_id` (`sale_id`), KEY `idx_payment_mode` (`payment_mode`), CONSTRAINT `sale_payments_ibfk_1` FOREIGN KEY (`sale_id`) REFERENCES `sales` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `sale_payments` -- LOCK TABLES `sale_payments` WRITE; /*!40000 ALTER TABLE `sale_payments` DISABLE KEYS */; INSERT INTO `sale_payments` (`id`, `sale_id`, `payment_mode`, `amount`, `reference_no`, `paid_at`) VALUES (1,3,'CASH',1000.00,NULL,'2026-06-28 18:58:33'), (2,3,'MPESA',200.00,'','2026-06-28 18:58:33'), (3,4,'BANK',200.00,'','2026-06-28 18:59:57'), (4,5,'BANK',1000.00,'','2026-06-29 23:25:35'), (5,6,'CASH',30.00,NULL,'2026-06-29 23:33:32'), (6,6,'MPESA',220.00,'','2026-06-29 23:33:32'), (7,7,'CASH',1100.00,NULL,'2026-06-29 23:36:43'), (8,8,'CASH',200.00,NULL,'2026-06-30 12:11:26'), (9,9,'CASH',3000.00,NULL,'2026-06-30 12:21:28'), (10,10,'CASH',3000.00,NULL,'2026-06-30 12:58:25'), (11,11,'CASH',3000.00,NULL,'2026-06-30 13:34:04'), (12,12,'CASH',200.00,NULL,'2026-06-30 13:35:17'), (13,13,'MPESA',1300.00,'','2026-06-30 14:00:52'), (14,14,'CASH',2200.00,NULL,'2026-06-30 19:08:15'), (15,15,'BANK',200.00,'','2026-06-30 20:30:46'), (16,17,'MPESA',5.00,'','2026-06-30 20:35:33'), (17,18,'CASH',100.00,NULL,'2026-06-30 20:52:51'), (18,18,'BANK',100.00,'','2026-06-30 20:52:51'); /*!40000 ALTER TABLE `sale_payments` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `sales` -- DROP TABLE IF EXISTS `sales`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `sales` ( `id` int(11) NOT NULL AUTO_INCREMENT, `branch_id` int(11) NOT NULL DEFAULT 1, `receipt_no` varchar(60) NOT NULL, `customer_id` int(11) DEFAULT NULL, `sale_date` datetime NOT NULL, `subtotal` decimal(12,2) DEFAULT 0.00, `discount` decimal(12,2) DEFAULT 0.00, `total_amount` decimal(12,2) DEFAULT 0.00, `total_cost` decimal(12,2) DEFAULT 0.00, `gross_profit` decimal(12,2) DEFAULT 0.00, `payment_status` enum('PAID','PARTIAL','CREDIT','HOME') DEFAULT 'PAID', `notes` text DEFAULT NULL, `created_by` int(11) NOT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(), `wallet_amount` decimal(12,2) DEFAULT 0.00, `cash_amount` decimal(12,2) DEFAULT 0.00, `mpesa_amount` decimal(12,2) DEFAULT 0.00, `bank_amount` decimal(12,2) DEFAULT 0.00, `credit_amount` decimal(12,2) DEFAULT 0.00, `home_amount` decimal(12,2) DEFAULT 0.00, `loyalty_points_earned` decimal(12,2) DEFAULT 0.00, `loyalty_points_redeemed` decimal(12,2) DEFAULT 0.00, `loyalty_discount_amount` decimal(12,2) DEFAULT 0.00, PRIMARY KEY (`id`), UNIQUE KEY `receipt_no` (`receipt_no`), KEY `branch_id` (`branch_id`), KEY `customer_id` (`customer_id`), KEY `created_by` (`created_by`), KEY `idx_sale_date` (`sale_date`), CONSTRAINT `sales_ibfk_1` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`), CONSTRAINT `sales_ibfk_2` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`), CONSTRAINT `sales_ibfk_3` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `sales` -- LOCK TABLES `sales` WRITE; /*!40000 ALTER TABLE `sales` DISABLE KEYS */; INSERT INTO `sales` (`id`, `branch_id`, `receipt_no`, `customer_id`, `sale_date`, `subtotal`, `discount`, `total_amount`, `total_cost`, `gross_profit`, `payment_status`, `notes`, `created_by`, `created_at`, `updated_at`, `wallet_amount`, `cash_amount`, `mpesa_amount`, `bank_amount`, `credit_amount`, `home_amount`, `loyalty_points_earned`, `loyalty_points_redeemed`, `loyalty_discount_amount`) VALUES (1,1,'RCP260628125938',2,'2026-06-28 15:59:38',1100.00,0.00,1100.00,900.00,200.00,'PARTIAL','',1,'2026-06-28 12:59:38',NULL,0.00,0.00,0.00,0.00,0.00,0.00,11.00,0.00,0.00), (2,1,'RCP260628151229',2,'2026-06-28 18:12:29',410.00,0.00,410.00,110.00,300.00,'PARTIAL','',1,'2026-06-28 15:12:29',NULL,0.00,0.00,0.00,0.00,0.00,0.00,4.10,0.00,0.00), (3,1,'RCP260628155833',7,'2026-06-28 18:58:33',1200.00,0.00,1200.00,352.00,848.00,'PAID','',1,'2026-06-28 15:58:33',NULL,0.00,1000.00,200.00,0.00,0.00,0.00,0.00,0.00,0.00), (4,1,'RCP260628155957',5,'2026-06-28 18:59:57',200.00,0.00,200.00,54.00,146.00,'PAID','',1,'2026-06-28 15:59:57',NULL,0.00,0.00,0.00,200.00,0.00,0.00,2.00,0.00,0.00), (5,1,'RCP260629202535',2,'2026-06-29 23:25:35',1200.00,0.00,1200.00,934.00,266.00,'PARTIAL','',1,'2026-06-29 20:25:35',NULL,0.00,0.00,0.00,1000.00,0.00,0.00,12.00,0.00,0.00), (6,1,'RCP260629203332',6,'2026-06-29 23:33:32',250.00,0.00,250.00,127.75,122.25,'PAID','',1,'2026-06-29 20:33:32',NULL,0.00,30.00,220.00,0.00,0.00,0.00,2.50,0.00,0.00), (7,1,'RCP260629203643',7,'2026-06-29 23:36:43',1100.00,0.00,1100.00,900.00,200.00,'PAID','',1,'2026-06-29 20:36:43',NULL,0.00,1100.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00), (8,1,'RCP260630091126',2,'2026-06-30 12:11:26',200.00,0.00,200.00,54.00,146.00,'PAID','',1,'2026-06-30 09:11:26',NULL,0.00,200.00,0.00,0.00,0.00,0.00,2.00,0.00,0.00), (9,1,'RCP260630092128',3,'2026-06-30 12:21:28',3000.00,0.00,3000.00,810.00,2190.00,'PAID','',1,'2026-06-30 09:21:28',NULL,0.00,3000.00,0.00,0.00,0.00,0.00,30.00,0.00,0.00), (10,1,'RCP260630095825',5,'2026-06-30 12:58:25',3000.00,0.00,3000.00,810.00,2190.00,'PAID','',1,'2026-06-30 09:58:25',NULL,0.00,3000.00,0.00,0.00,0.00,0.00,30.00,0.00,0.00), (11,1,'RCP260630103404',6,'2026-06-30 13:34:04',3000.00,0.00,3000.00,810.00,2190.00,'PAID','',1,'2026-06-30 10:34:04',NULL,0.00,3000.00,0.00,0.00,0.00,0.00,30.00,0.00,0.00), (12,1,'RCP260630103517',7,'2026-06-30 13:35:17',200.00,0.00,200.00,68.00,132.00,'PAID','',1,'2026-06-30 10:35:17',NULL,0.00,200.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00), (13,1,'RCP260630110052',6,'2026-06-30 14:00:52',1300.00,0.00,1300.00,954.00,346.00,'PAID','',1,'2026-06-30 11:00:52',NULL,0.00,0.00,1300.00,0.00,0.00,0.00,13.00,0.00,0.00), (14,1,'RCP260630160815',6,'2026-06-30 19:08:15',2200.00,0.00,2200.00,594.00,1606.00,'PAID','',1,'2026-06-30 16:08:15',NULL,0.00,2200.00,0.00,0.00,0.00,0.00,22.00,0.00,0.00), (15,1,'RCP260630173046',6,'2026-06-30 20:30:46',200.00,0.00,200.00,54.00,146.00,'PAID','',1,'2026-06-30 17:30:46',NULL,0.00,0.00,0.00,200.00,0.00,0.00,2.00,0.00,0.00), (16,1,'RCP260630173309',7,'2026-06-30 20:33:09',5.00,0.00,5.00,1.00,4.00,'PARTIAL','',1,'2026-06-30 17:33:09',NULL,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00), (17,1,'RCP260630173533',6,'2026-06-30 20:35:33',5.00,0.00,5.00,1.00,4.00,'PAID','',1,'2026-06-30 17:35:33',NULL,0.00,0.00,5.00,0.00,0.00,0.00,0.05,0.00,0.00), (18,1,'RCP260630175251',6,'2026-06-30 20:52:51',200.00,0.00,200.00,54.00,146.00,'PAID','',1,'2026-06-30 17:52:51',NULL,0.00,100.00,0.00,100.00,0.00,0.00,2.00,0.00,0.00); /*!40000 ALTER TABLE `sales` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `settings` -- DROP TABLE IF EXISTS `settings`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `settings` ( `id` int(11) NOT NULL AUTO_INCREMENT, `setting_key` varchar(100) NOT NULL, `setting_value` text DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `setting_key` (`setting_key`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `settings` -- LOCK TABLES `settings` WRITE; /*!40000 ALTER TABLE `settings` DISABLE KEYS */; INSERT INTO `settings` (`id`, `setting_key`, `setting_value`, `updated_at`) VALUES (1,'company_name','NELQUA',NULL), (2,'currency','KES',NULL), (3,'timezone','Africa/Nairobi',NULL), (4,'receipt_prefix','RCP',NULL), (5,'purchase_prefix','PUR',NULL), (6,'ticket_prefix','FBK',NULL); /*!40000 ALTER TABLE `settings` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `staff` -- DROP TABLE IF EXISTS `staff`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `staff` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) DEFAULT NULL, `staff_no` varchar(60) DEFAULT NULL, `full_name` varchar(150) NOT NULL, `phone` varchar(30) DEFAULT NULL, `email` varchar(120) DEFAULT NULL, `national_id` varchar(50) DEFAULT NULL, `next_of_kin_name` varchar(150) DEFAULT NULL, `next_of_kin_phone` varchar(30) DEFAULT NULL, `high_school` varchar(150) DEFAULT NULL, `college` varchar(150) DEFAULT NULL, `monthly_salary` decimal(12,2) DEFAULT 0.00, `status` enum('ACTIVE','INACTIVE') DEFAULT 'ACTIVE', `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `staff_no` (`staff_no`), KEY `user_id` (`user_id`), CONSTRAINT `staff_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `staff` -- LOCK TABLES `staff` WRITE; /*!40000 ALTER TABLE `staff` DISABLE KEYS */; /*!40000 ALTER TABLE `staff` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `staff_loans` -- DROP TABLE IF EXISTS `staff_loans`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `staff_loans` ( `id` int(11) NOT NULL AUTO_INCREMENT, `staff_id` int(11) NOT NULL, `loan_date` date NOT NULL, `amount` decimal(12,2) NOT NULL, `balance` decimal(12,2) NOT NULL, `payment_mode` enum('CASH','MPESA','BANK') DEFAULT 'CASH', `approval_status` enum('PENDING','APPROVED','REJECTED') DEFAULT 'PENDING', `approved_by` int(11) DEFAULT NULL, `created_by` int(11) NOT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `staff_id` (`staff_id`), KEY `approved_by` (`approved_by`), KEY `created_by` (`created_by`), CONSTRAINT `staff_loans_ibfk_1` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`id`), CONSTRAINT `staff_loans_ibfk_2` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`), CONSTRAINT `staff_loans_ibfk_3` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `staff_loans` -- LOCK TABLES `staff_loans` WRITE; /*!40000 ALTER TABLE `staff_loans` DISABLE KEYS */; /*!40000 ALTER TABLE `staff_loans` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `stock_movements` -- DROP TABLE IF EXISTS `stock_movements`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `stock_movements` ( `id` int(11) NOT NULL AUTO_INCREMENT, `branch_id` int(11) NOT NULL DEFAULT 1, `product_id` int(11) NOT NULL, `movement_type` enum('OPENING','PURCHASE','SALE','ADJUSTMENT_IN','ADJUSTMENT_OUT','RETURN_IN','RETURN_OUT','TRANSFER_IN','TRANSFER_OUT','HOME_CONSUMPTION') NOT NULL, `quantity` decimal(12,2) NOT NULL, `balance_before` decimal(12,2) DEFAULT 0.00, `balance_after` decimal(12,2) DEFAULT 0.00, `reference_type` varchar(60) DEFAULT NULL, `reference_id` int(11) DEFAULT NULL, `remarks` text DEFAULT NULL, `created_by` int(11) NOT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `branch_id` (`branch_id`), KEY `created_by` (`created_by`), KEY `idx_product_movement` (`product_id`,`movement_type`), CONSTRAINT `stock_movements_ibfk_1` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`), CONSTRAINT `stock_movements_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`), CONSTRAINT `stock_movements_ibfk_3` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `stock_movements` -- LOCK TABLES `stock_movements` WRITE; /*!40000 ALTER TABLE `stock_movements` DISABLE KEYS */; INSERT INTO `stock_movements` (`id`, `branch_id`, `product_id`, `movement_type`, `quantity`, `balance_before`, `balance_after`, `reference_type`, `reference_id`, `remarks`, `created_by`, `created_at`) VALUES (1,1,19,'PURCHASE',1.00,0.00,1.00,'PURCHASE',11,'Purchase posting PUR-00011',1,'2026-06-23 17:53:22'), (2,1,59,'PURCHASE',3.00,0.00,3.00,'PURCHASE',14,'Purchase posting PUR-00014',1,'2026-06-23 17:54:30'), (3,1,69,'PURCHASE',3000.00,0.00,3000.00,'PURCHASE',13,'Purchase posting PUR-00013',1,'2026-06-23 17:59:46'), (4,1,69,'PURCHASE',3000.00,0.00,6000.00,'PURCHASE',3,'Purchase posting PUR-00003',1,'2026-06-23 18:01:52'), (5,1,65,'PURCHASE',24.00,0.00,24.00,'PURCHASE',3,'Purchase posting PUR-00003',1,'2026-06-23 18:01:52'), (6,1,76,'PURCHASE',1.00,0.00,1.00,'PURCHASE',2,'Purchase posting PUR-00002',1,'2026-06-23 18:02:47'), (7,1,106,'PURCHASE',60.00,0.00,60.00,'PURCHASE',1,'Purchase posting PUR-00001',1,'2026-06-23 18:03:18'), (8,1,60,'PURCHASE',4.00,0.00,4.00,'PURCHASE',15,'Purchase posting PUR-00015',1,'2026-06-23 20:30:08'), (9,1,80,'PURCHASE',5.00,0.00,5.00,'PURCHASE',16,'Purchase posting PUR-00016',1,'2026-06-25 17:36:53'), (10,1,50,'PURCHASE',5.00,0.00,5.00,'PURCHASE',17,'Purchase posting PUR-00017',1,'2026-06-25 17:38:53'), (11,1,39,'SALE',1.00,6.00,5.00,'SALE',1,'POS sale RCP260628125938',1,'2026-06-28 12:59:38'), (12,1,39,'SALE',1.00,5.00,4.00,'SALE',5,'POS sale RCP260629202535',1,'2026-06-29 20:25:35'), (13,1,65,'SALE',5.00,24.00,19.00,'SALE',6,'POS sale RCP260629203332',1,'2026-06-29 20:33:32'), (14,1,39,'SALE',1.00,4.00,3.00,'SALE',7,'POS sale RCP260629203643',1,'2026-06-29 20:36:43'), (15,1,110,'SALE',300.00,6000.00,5700.00,'SALE',11,'Water refill deduction from Main Tank - POS sale RCP260630103404',1,'2026-06-30 10:34:04'), (16,1,110,'SALE',20.00,5700.00,5680.00,'SALE',12,'Water refill deduction from Main Tank - POS sale RCP260630103517',1,'2026-06-30 10:35:17'), (17,1,110,'SALE',20.00,5680.00,5660.00,'SALE',13,'Water refill deduction from Main Tank - POS sale RCP260630110052',1,'2026-06-30 11:00:52'), (18,1,39,'SALE',1.00,3.00,2.00,'SALE',13,'POS sale RCP260630110052',1,'2026-06-30 11:00:52'), (19,1,110,'SALE',220.00,5660.00,5440.00,'SALE',14,'Water refill deduction from Main Tank - POS sale RCP260630160815',1,'2026-06-30 16:08:15'), (20,1,110,'SALE',20.00,5440.00,5420.00,'SALE',15,'Water refill deduction from Main Tank - POS sale RCP260630173046',1,'2026-06-30 17:30:46'), (21,1,110,'SALE',0.50,5420.00,5419.50,'SALE',16,'Water refill deduction from Main Tank - POS sale RCP260630173309',1,'2026-06-30 17:33:09'), (22,1,110,'SALE',0.50,5419.50,5419.00,'SALE',17,'Water refill deduction from Main Tank - POS sale RCP260630173533',1,'2026-06-30 17:35:33'), (23,1,110,'SALE',20.00,5419.00,5399.00,'SALE',18,'Water refill deduction from Main Tank - POS sale RCP260630175251',1,'2026-06-30 17:52:51'); /*!40000 ALTER TABLE `stock_movements` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `sub_categories` -- DROP TABLE IF EXISTS `sub_categories`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `sub_categories` ( `id` int(11) NOT NULL AUTO_INCREMENT, `category_id` int(11) NOT NULL, `sub_category_name` varchar(100) NOT NULL, `status` varchar(20) DEFAULT 'ACTIVE', `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `sub_categories` -- LOCK TABLES `sub_categories` WRITE; /*!40000 ALTER TABLE `sub_categories` DISABLE KEYS */; INSERT INTO `sub_categories` (`id`, `category_id`, `sub_category_name`, `status`, `created_at`) VALUES (1,1,'0.5L Refill','ACTIVE','2026-06-14 16:14:19'), (2,1,'1L Refill','ACTIVE','2026-06-14 16:14:19'), (3,1,'2L Refill','ACTIVE','2026-06-14 16:14:19'), (4,1,'5L Refill','ACTIVE','2026-06-14 16:14:19'), (5,1,'10L Refill','ACTIVE','2026-06-14 16:14:19'), (6,1,'20L Refill','ACTIVE','2026-06-14 16:14:19'), (7,2,'Refills','ACTIVE','2026-06-14 16:14:19'), (8,2,'Cylinders','ACTIVE','2026-06-14 16:14:19'), (9,3,'Regulators','ACTIVE','2026-06-14 16:14:19'), (10,3,'Burners','ACTIVE','2026-06-14 16:14:19'), (11,3,'Pipes','ACTIVE','2026-06-14 16:14:19'), (12,3,'Grills','ACTIVE','2026-06-14 16:14:19'), (13,3,'Pumps','ACTIVE','2026-06-14 16:14:19'), (14,3,'Clips','ACTIVE','2026-06-14 16:14:19'), (15,11,'Retail','ACTIVE','2026-06-14 16:14:19'), (16,11,'Wholesale','ACTIVE','2026-06-14 16:14:19'), (17,12,'Woyez','ACTIVE','2026-06-14 16:14:19'), (18,12,'Aqua Bliss','ACTIVE','2026-06-14 16:14:19'), (19,12,'Other Brands','ACTIVE','2026-06-14 16:14:19'), (20,13,'Water Caps','ACTIVE','2026-06-14 16:14:19'), (21,13,'Water Security Seals','ACTIVE','2026-06-14 16:14:19'), (22,13,'Water Stickers','ACTIVE','2026-06-14 16:14:19'), (23,13,'Gas Caps','ACTIVE','2026-06-14 16:14:19'), (24,13,'Gas Security Seals','ACTIVE','2026-06-14 16:14:19'), (25,14,'5L Bottles','INACTIVE','2026-06-14 16:14:19'), (26,14,'10L Bottles','INACTIVE','2026-06-14 16:14:19'), (27,14,'20L Soft Bottles','INACTIVE','2026-06-14 16:14:19'), (28,14,'20L Fine Line Clear','INACTIVE','2026-06-14 16:14:19'), (29,14,'20L Fine Line Blue','INACTIVE','2026-06-14 16:14:19'), (30,14,'20L Hard Bottles','INACTIVE','2026-06-14 16:14:19'), (31,2,'Complete Sets','ACTIVE','2026-06-14 17:42:34'), (32,14,'Water Bottles','ACTIVE','2026-06-18 18:09:33'), (33,14,'Gas Cylinders','ACTIVE','2026-06-18 18:09:33'); /*!40000 ALTER TABLE `sub_categories` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `supplier_assignment_settings` -- DROP TABLE IF EXISTS `supplier_assignment_settings`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `supplier_assignment_settings` ( `id` int(11) NOT NULL AUTO_INCREMENT, `setting_key` varchar(100) NOT NULL, `setting_value` varchar(255) DEFAULT NULL, `description` varchar(255) DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `setting_key` (`setting_key`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `supplier_assignment_settings` -- LOCK TABLES `supplier_assignment_settings` WRITE; /*!40000 ALTER TABLE `supplier_assignment_settings` DISABLE KEYS */; INSERT INTO `supplier_assignment_settings` (`id`, `setting_key`, `setting_value`, `description`, `updated_at`) VALUES (1,'allow_bulk_category_assignment','YES','Allow assigning all visible products under selected categories to a supplier',NULL), (2,'allow_bulk_brand_assignment','YES','Allow assigning all visible products under selected brands to a supplier',NULL), (3,'allow_select_all_visible_products','YES','Allow select all after filtering products',NULL), (4,'enforce_supplier_product_control_on_purchase','YES','Purchases should only allow products linked to selected supplier',NULL); /*!40000 ALTER TABLE `supplier_assignment_settings` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `supplier_categories` -- DROP TABLE IF EXISTS `supplier_categories`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `supplier_categories` ( `id` int(11) NOT NULL AUTO_INCREMENT, `supplier_id` int(11) NOT NULL, `category_name` varchar(80) NOT NULL, `status` enum('ACTIVE','INACTIVE') DEFAULT 'ACTIVE', `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `supplier_category_unique` (`supplier_id`,`category_name`), KEY `supplier_id` (`supplier_id`), KEY `category_name` (`category_name`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `supplier_categories` -- LOCK TABLES `supplier_categories` WRITE; /*!40000 ALTER TABLE `supplier_categories` DISABLE KEYS */; INSERT INTO `supplier_categories` (`id`, `supplier_id`, `category_name`, `status`, `created_at`) VALUES (1,16,'Expenses','ACTIVE','2026-06-21 22:56:24'), (2,21,'Other','ACTIVE','2026-06-21 22:57:25'), (3,12,'Containers','ACTIVE','2026-06-22 17:47:16'), (4,17,'Expenses','ACTIVE','2026-06-22 18:07:43'), (5,1,'Water Refill','ACTIVE','2026-06-22 19:37:14'); /*!40000 ALTER TABLE `supplier_categories` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `supplier_ledger` -- DROP TABLE IF EXISTS `supplier_ledger`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `supplier_ledger` ( `id` int(11) NOT NULL AUTO_INCREMENT, `supplier_id` int(11) NOT NULL, `transaction_date` date NOT NULL, `transaction_type` enum('PURCHASE','PAYMENT','DEBIT_NOTE','CREDIT_NOTE','ADJUSTMENT') NOT NULL, `reference_type` varchar(60) DEFAULT NULL, `reference_id` int(11) DEFAULT NULL, `debit` decimal(12,2) DEFAULT 0.00, `credit` decimal(12,2) DEFAULT 0.00, `balance_after` decimal(12,2) DEFAULT 0.00, `description` varchar(255) DEFAULT NULL, `created_by` int(11) DEFAULT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `supplier_id` (`supplier_id`), KEY `transaction_date` (`transaction_date`), KEY `transaction_type` (`transaction_type`) ) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `supplier_ledger` -- LOCK TABLES `supplier_ledger` WRITE; /*!40000 ALTER TABLE `supplier_ledger` DISABLE KEYS */; INSERT INTO `supplier_ledger` (`id`, `supplier_id`, `transaction_date`, `transaction_type`, `reference_type`, `reference_id`, `debit`, `credit`, `balance_after`, `description`, `created_by`, `created_at`) VALUES (1,12,'2026-06-22','PURCHASE','PURCHASE',10,0.00,3000.00,3000.00,'Posted purchase PUR-00010',1,'2026-06-23 17:52:02'), (2,12,'2026-06-22','PAYMENT','PURCHASE_PAYMENT',10,3000.00,0.00,0.00,'Payment for purchase PUR-00010',1,'2026-06-23 17:52:02'), (3,12,'2026-06-22','PURCHASE','PURCHASE',11,0.00,500.00,500.00,'Posted purchase PUR-00011',1,'2026-06-23 17:53:22'), (4,12,'2026-06-22','PAYMENT','PURCHASE_PAYMENT',11,500.00,0.00,0.00,'Payment for purchase PUR-00011',1,'2026-06-23 17:53:22'), (5,23,'2026-06-23','PURCHASE','PURCHASE',14,0.00,6000.00,6000.00,'Posted purchase PUR-00014',1,'2026-06-23 17:54:30'), (6,23,'2026-06-23','PAYMENT','PURCHASE_PAYMENT',14,6000.00,0.00,0.00,'Payment for purchase PUR-00014',1,'2026-06-23 17:54:30'), (7,21,'2026-06-21','PURCHASE','PURCHASE',8,0.00,12000.00,12000.00,'Posted purchase PUR-00008',1,'2026-06-23 17:57:07'), (8,21,'2026-06-21','PAYMENT','PURCHASE_PAYMENT',8,12000.00,0.00,0.00,'Payment for purchase PUR-00008',1,'2026-06-23 17:57:07'), (9,21,'2026-06-22','PURCHASE','PURCHASE',12,0.00,2000.00,2000.00,'Posted purchase PUR-00012',1,'2026-06-23 17:57:48'), (10,21,'2026-06-22','PAYMENT','PURCHASE_PAYMENT',12,2000.00,0.00,0.00,'Payment for purchase PUR-00012',1,'2026-06-23 17:57:48'), (11,1,'2026-06-22','PURCHASE','PURCHASE',13,0.00,6000.00,6000.00,'Posted purchase PUR-00013',1,'2026-06-23 17:59:46'), (12,1,'2026-06-22','PAYMENT','PURCHASE_PAYMENT',13,6000.00,0.00,0.00,'Payment for purchase PUR-00013',1,'2026-06-23 17:59:46'), (13,21,'2026-06-21','PURCHASE','PURCHASE',9,0.00,3000.00,3000.00,'Posted purchase PUR-00009',1,'2026-06-23 18:00:08'), (14,21,'2026-06-21','PAYMENT','PURCHASE_PAYMENT',9,3000.00,0.00,0.00,'Payment for purchase PUR-00009',1,'2026-06-23 18:00:08'), (15,1,'2026-06-21','PURCHASE','PURCHASE',3,0.00,6600.00,6600.00,'Posted purchase PUR-00003',1,'2026-06-23 18:01:52'), (16,1,'2026-06-21','PAYMENT','PURCHASE_PAYMENT',3,6600.00,0.00,0.00,'Payment for purchase PUR-00003',1,'2026-06-23 18:01:52'), (17,12,'2026-06-21','PURCHASE','PURCHASE',2,0.00,3500.00,3500.00,'Posted purchase PUR-00002',1,'2026-06-23 18:02:47'), (18,12,'2026-06-21','PAYMENT','PURCHASE_PAYMENT',2,3500.00,0.00,0.00,'Payment for purchase PUR-00002',1,'2026-06-23 18:02:47'), (19,13,'2026-06-21','PURCHASE','PURCHASE',1,0.00,9600.00,9600.00,'Posted purchase PUR-00001',1,'2026-06-23 18:03:18'), (20,13,'2026-06-21','PAYMENT','PURCHASE_PAYMENT',1,9600.00,0.00,0.00,'Payment for purchase PUR-00001',1,'2026-06-23 18:03:18'), (21,23,'2026-06-23','PURCHASE','PURCHASE',15,0.00,8000.00,8000.00,'Posted purchase PUR-00015',1,'2026-06-23 20:30:08'), (22,23,'2026-06-23','PAYMENT','PURCHASE_PAYMENT',15,8000.00,0.00,0.00,'Payment for purchase PUR-00015',1,'2026-06-23 20:30:08'), (23,12,'2026-06-25','PURCHASE','PURCHASE',16,0.00,15000.00,15000.00,'Posted purchase PUR-00016',1,'2026-06-25 17:36:54'), (24,12,'2026-06-25','PAYMENT','PURCHASE_PAYMENT',16,15000.00,0.00,0.00,'Payment for purchase PUR-00016',1,'2026-06-25 17:36:54'), (25,13,'2026-06-25','PURCHASE','PURCHASE',17,0.00,2000.00,2000.00,'Posted purchase PUR-00017',1,'2026-06-25 17:38:53'), (26,13,'2026-06-25','PAYMENT','PURCHASE_PAYMENT',17,2000.00,0.00,0.00,'Payment for purchase PUR-00017',1,'2026-06-25 17:38:53'); /*!40000 ALTER TABLE `supplier_ledger` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `supplier_other_items` -- DROP TABLE IF EXISTS `supplier_other_items`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `supplier_other_items` ( `id` int(11) NOT NULL AUTO_INCREMENT, `supplier_id` int(11) NOT NULL, `item_type` enum('ASSET','SERVICE','EXPENSE','OTHER') DEFAULT 'OTHER', `item_name` varchar(180) NOT NULL, `description` text DEFAULT NULL, `status` enum('ACTIVE','INACTIVE') DEFAULT 'ACTIVE', `created_at` timestamp NULL DEFAULT current_timestamp(), `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(), PRIMARY KEY (`id`), KEY `supplier_id` (`supplier_id`), KEY `item_type` (`item_type`), KEY `status` (`status`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `supplier_other_items` -- LOCK TABLES `supplier_other_items` WRITE; /*!40000 ALTER TABLE `supplier_other_items` DISABLE KEYS */; INSERT INTO `supplier_other_items` (`id`, `supplier_id`, `item_type`, `item_name`, `description`, `status`, `created_at`, `updated_at`) VALUES (1,16,'SERVICE','Security','Security','ACTIVE','2026-06-21 22:56:24',NULL), (2,21,'EXPENSE','Licences','Trading Licences','ACTIVE','2026-06-21 22:57:25',NULL), (3,17,'EXPENSE','Printing and stationery','','ACTIVE','2026-06-22 18:07:43',NULL); /*!40000 ALTER TABLE `supplier_other_items` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `supplier_products` -- DROP TABLE IF EXISTS `supplier_products`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `supplier_products` ( `id` int(11) NOT NULL AUTO_INCREMENT, `supplier_id` int(11) NOT NULL, `product_id` int(11) NOT NULL, `status` enum('ACTIVE','INACTIVE') DEFAULT 'ACTIVE', `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `supplier_product_unique` (`supplier_id`,`product_id`), UNIQUE KEY `supplier_product_unique_check` (`supplier_id`,`product_id`), KEY `supplier_id` (`supplier_id`), KEY `product_id` (`product_id`), KEY `idx_supplier_products_supplier` (`supplier_id`), KEY `idx_supplier_products_product` (`product_id`) ) ENGINE=InnoDB AUTO_INCREMENT=93 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `supplier_products` -- LOCK TABLES `supplier_products` WRITE; /*!40000 ALTER TABLE `supplier_products` DISABLE KEYS */; INSERT INTO `supplier_products` (`id`, `supplier_id`, `product_id`, `status`, `created_at`) VALUES (1,18,32,'ACTIVE','2026-06-21 10:11:42'), (2,18,45,'ACTIVE','2026-06-21 10:11:42'), (3,23,63,'ACTIVE','2026-06-21 10:29:56'), (4,23,62,'ACTIVE','2026-06-21 10:29:56'), (5,23,60,'ACTIVE','2026-06-21 10:29:56'), (6,23,64,'ACTIVE','2026-06-21 10:29:56'), (7,23,61,'ACTIVE','2026-06-21 10:29:56'), (8,23,59,'ACTIVE','2026-06-21 10:29:56'), (9,23,54,'ACTIVE','2026-06-21 10:29:56'), (10,12,19,'ACTIVE','2026-06-22 17:47:16'), (11,12,18,'ACTIVE','2026-06-22 17:47:16'), (12,12,59,'ACTIVE','2026-06-22 17:47:16'), (13,1,69,'ACTIVE','2026-06-22 19:37:14'), (14,12,86,'ACTIVE','2026-06-22 19:42:19'), (15,12,87,'ACTIVE','2026-06-22 19:42:19'), (16,12,72,'ACTIVE','2026-06-22 19:42:19'), (17,12,73,'ACTIVE','2026-06-22 19:42:19'), (18,12,74,'ACTIVE','2026-06-22 19:42:19'), (19,12,75,'ACTIVE','2026-06-22 19:42:19'), (20,12,84,'ACTIVE','2026-06-22 19:42:19'), (21,12,85,'ACTIVE','2026-06-22 19:42:19'), (22,12,78,'ACTIVE','2026-06-22 19:42:19'), (23,12,79,'ACTIVE','2026-06-22 19:42:19'), (24,12,22,'ACTIVE','2026-06-22 19:42:19'), (25,12,23,'ACTIVE','2026-06-22 19:42:19'), (26,12,70,'ACTIVE','2026-06-22 19:42:19'), (27,12,71,'ACTIVE','2026-06-22 19:42:19'), (28,12,82,'ACTIVE','2026-06-22 19:42:19'), (29,12,83,'ACTIVE','2026-06-22 19:42:19'), (30,12,76,'ACTIVE','2026-06-22 19:42:19'), (31,12,77,'ACTIVE','2026-06-22 19:42:19'), (32,12,81,'ACTIVE','2026-06-22 19:42:19'), (33,12,80,'ACTIVE','2026-06-22 19:42:19'), (34,12,20,'ACTIVE','2026-06-22 19:42:19'), (35,12,21,'ACTIVE','2026-06-22 19:42:19'), (36,12,63,'ACTIVE','2026-06-22 19:42:19'), (37,12,62,'ACTIVE','2026-06-22 19:42:19'), (38,12,64,'ACTIVE','2026-06-22 19:42:19'), (39,12,60,'ACTIVE','2026-06-22 19:42:19'), (40,12,61,'ACTIVE','2026-06-22 19:42:19'), (41,12,32,'ACTIVE','2026-06-22 19:45:04'), (42,12,38,'ACTIVE','2026-06-22 19:45:04'), (43,12,45,'ACTIVE','2026-06-22 19:45:04'), (44,12,46,'ACTIVE','2026-06-22 19:45:04'), (45,12,44,'ACTIVE','2026-06-22 19:45:04'), (46,12,42,'ACTIVE','2026-06-22 19:45:04'), (47,12,43,'ACTIVE','2026-06-22 19:45:04'), (48,12,41,'ACTIVE','2026-06-22 19:45:04'), (49,12,51,'ACTIVE','2026-06-22 19:45:04'), (50,12,49,'ACTIVE','2026-06-22 19:45:04'), (51,12,50,'ACTIVE','2026-06-22 19:45:04'), (52,12,53,'ACTIVE','2026-06-22 19:45:04'), (53,12,52,'ACTIVE','2026-06-22 19:45:04'), (54,12,47,'ACTIVE','2026-06-22 19:45:04'), (55,12,48,'ACTIVE','2026-06-22 19:45:04'), (56,13,90,'ACTIVE','2026-06-25 17:37:50'), (57,13,39,'ACTIVE','2026-06-25 17:37:50'), (58,13,108,'ACTIVE','2026-06-25 17:37:50'), (59,13,107,'ACTIVE','2026-06-25 17:37:50'), (60,13,94,'ACTIVE','2026-06-25 17:37:50'), (61,13,93,'ACTIVE','2026-06-25 17:37:50'), (62,13,96,'ACTIVE','2026-06-25 17:37:50'), (63,13,95,'ACTIVE','2026-06-25 17:37:50'), (64,13,106,'ACTIVE','2026-06-25 17:37:50'), (65,13,105,'ACTIVE','2026-06-25 17:37:50'), (66,13,100,'ACTIVE','2026-06-25 17:37:50'), (67,13,99,'ACTIVE','2026-06-25 17:37:50'), (68,13,89,'ACTIVE','2026-06-25 17:37:50'), (69,13,88,'ACTIVE','2026-06-25 17:37:50'), (70,13,104,'ACTIVE','2026-06-25 17:37:50'), (71,13,103,'ACTIVE','2026-06-25 17:37:50'), (72,13,98,'ACTIVE','2026-06-25 17:37:50'), (73,13,97,'ACTIVE','2026-06-25 17:37:50'), (74,13,102,'ACTIVE','2026-06-25 17:37:50'), (75,13,101,'ACTIVE','2026-06-25 17:37:50'), (76,13,92,'ACTIVE','2026-06-25 17:37:50'), (77,13,91,'ACTIVE','2026-06-25 17:37:50'), (78,13,32,'ACTIVE','2026-06-25 17:38:13'), (79,13,38,'ACTIVE','2026-06-25 17:38:13'), (80,13,45,'ACTIVE','2026-06-25 17:38:13'), (81,13,46,'ACTIVE','2026-06-25 17:38:13'), (82,13,44,'ACTIVE','2026-06-25 17:38:13'), (83,13,42,'ACTIVE','2026-06-25 17:38:13'), (84,13,43,'ACTIVE','2026-06-25 17:38:13'), (85,13,41,'ACTIVE','2026-06-25 17:38:13'), (86,13,51,'ACTIVE','2026-06-25 17:38:13'), (87,13,49,'ACTIVE','2026-06-25 17:38:13'), (88,13,50,'ACTIVE','2026-06-25 17:38:13'), (89,13,53,'ACTIVE','2026-06-25 17:38:13'), (90,13,52,'ACTIVE','2026-06-25 17:38:13'), (91,13,48,'ACTIVE','2026-06-25 17:38:13'), (92,13,47,'ACTIVE','2026-06-25 17:38:13'); /*!40000 ALTER TABLE `supplier_products` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `suppliers` -- DROP TABLE IF EXISTS `suppliers`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `suppliers` ( `id` int(11) NOT NULL AUTO_INCREMENT, `supplier_code` varchar(30) DEFAULT NULL, `supplier_name` varchar(150) NOT NULL, `supplier_type` varchar(50) DEFAULT NULL, `phone` varchar(30) DEFAULT NULL, `email` varchar(120) DEFAULT NULL, `address` text DEFAULT NULL, `current_balance` decimal(12,2) DEFAULT 0.00, `status` enum('ACTIVE','INACTIVE') DEFAULT 'ACTIVE', `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `suppliers` -- LOCK TABLES `suppliers` WRITE; /*!40000 ALTER TABLE `suppliers` DISABLE KEYS */; INSERT INTO `suppliers` (`id`, `supplier_code`, `supplier_name`, `supplier_type`, `phone`, `email`, `address`, `current_balance`, `status`, `created_at`) VALUES (1,'SUP-0001','Everest/cockrel','Multiple','0728569381','','P.o Box17887-00100 GPO\r\nUtawala Shopping Mall.',0.00,'ACTIVE','2026-06-21 07:26:50'), (12,'SUP-0012','Bene Kyalo Syengo','Multiple','0722635887','','Umoja Market',0.00,'ACTIVE','2026-06-21 07:51:08'), (13,'SUP-0013','Nelgas Supplies','Multiple Products','0718422533','','Landless',0.00,'ACTIVE','2026-06-21 07:52:26'), (14,'SUP-0014','Aqua bliss','Bottled Water','','','Makongeni',0.00,'ACTIVE','2026-06-21 07:53:22'), (16,'SUP-0016','Makoneni phase 13 security','Multiple','0740177497/0790181035','','P.O BOX 3736 THIKA',0.00,'ACTIVE','2026-06-21 09:37:09'), (17,'SUP-0017','Benykyms Printers','Multiple','0723930299','','P.O. Box 315 Thika',0.00,'ACTIVE','2026-06-21 09:38:33'), (18,'SUP-0018','Malimali wholesalers','Multiple Products','0740457616','','Along Temple road OTC wholesale mall building',0.00,'ACTIVE','2026-06-21 10:11:42'), (19,'SUP-0019','Saruji Hardware','Multiple Products','0706775325','','PO Box 4272-01002 Thika',0.00,'ACTIVE','2026-06-21 10:17:07'), (20,'SUP-0020','Universe Fire Protection Company','Multiple Products','0724645169','universefire.co@gmail.com','P.O Box 20170-00100 Nairobi',0.00,'ACTIVE','2026-06-21 10:19:11'), (21,'SUP-0021','County government','Multiple','0742000888','revenue@kiambu.go.ke','P.O Box 2344-00900',0.00,'ACTIVE','2026-06-21 10:20:43'), (22,'SUP-0022','Airtel smart connect Home wifi','Multiple Products','0736571788','','',0.00,'ACTIVE','2026-06-21 10:23:14'), (23,'SUP-0023','Fineline industries','Multiple Products','0726991999','info@fineline.co.ke','P.O Box 39193-00623, Nairobi',0.00,'ACTIVE','2026-06-21 10:29:56'); /*!40000 ALTER TABLE `suppliers` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `till_movements` -- DROP TABLE IF EXISTS `till_movements`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `till_movements` ( `id` int(11) NOT NULL AUTO_INCREMENT, `till_session_id` int(11) DEFAULT NULL, `business_date` date NOT NULL, `movement_type` enum('OPENING_FLOAT','CASH_SALE','MPESA_SALE','BANK_SALE','CASH_BANKING','MPESA_BANKING','BANK_DEPOSIT','CASH_WITHDRAWAL','FLOAT_ADJUSTMENT','CASH_VARIANCE','MPESA_CHARGE','BANK_CHARGE','OTHER') NOT NULL, `amount` decimal(12,2) NOT NULL DEFAULT 0.00, `reference_type` varchar(60) DEFAULT NULL, `reference_id` int(11) DEFAULT NULL, `description` varchar(255) DEFAULT NULL, `created_by` int(11) DEFAULT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`), KEY `till_session_id` (`till_session_id`), KEY `business_date` (`business_date`), KEY `movement_type` (`movement_type`), KEY `reference_type` (`reference_type`), KEY `reference_id` (`reference_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `till_movements` -- LOCK TABLES `till_movements` WRITE; /*!40000 ALTER TABLE `till_movements` DISABLE KEYS */; /*!40000 ALTER TABLE `till_movements` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `till_sessions` -- DROP TABLE IF EXISTS `till_sessions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `till_sessions` ( `id` int(11) NOT NULL AUTO_INCREMENT, `business_date` date NOT NULL, `user_id` int(11) DEFAULT NULL, `opening_float` decimal(12,2) NOT NULL DEFAULT 2000.00, `cash_sales` decimal(12,2) NOT NULL DEFAULT 0.00, `mpesa_sales` decimal(12,2) NOT NULL DEFAULT 0.00, `bank_sales` decimal(12,2) NOT NULL DEFAULT 0.00, `expected_cash` decimal(12,2) NOT NULL DEFAULT 0.00, `actual_cash` decimal(12,2) DEFAULT NULL, `cash_to_bank` decimal(12,2) NOT NULL DEFAULT 0.00, `mpesa_to_bank` decimal(12,2) NOT NULL DEFAULT 0.00, `retained_float` decimal(12,2) NOT NULL DEFAULT 2000.00, `variance` decimal(12,2) NOT NULL DEFAULT 0.00, `status` enum('OPEN','CLOSED','RECONCILED') DEFAULT 'OPEN', `opened_by` int(11) DEFAULT NULL, `closed_by` int(11) DEFAULT NULL, `opened_at` timestamp NULL DEFAULT current_timestamp(), `closed_at` timestamp NULL DEFAULT NULL, `notes` text DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `unique_till_user_date` (`business_date`,`user_id`), KEY `business_date` (`business_date`), KEY `user_id` (`user_id`), KEY `status` (`status`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `till_sessions` -- LOCK TABLES `till_sessions` WRITE; /*!40000 ALTER TABLE `till_sessions` DISABLE KEYS */; /*!40000 ALTER TABLE `till_sessions` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `users` -- DROP TABLE IF EXISTS `users`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8mb4 */; CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `role_id` int(11) NOT NULL, `full_name` varchar(120) NOT NULL, `username` varchar(60) NOT NULL, `email` varchar(120) DEFAULT NULL, `phone` varchar(30) DEFAULT NULL, `password_hash` varchar(255) NOT NULL, `status` enum('ACTIVE','INACTIVE') DEFAULT 'ACTIVE', `last_login` datetime DEFAULT NULL, `created_at` timestamp NULL DEFAULT current_timestamp(), `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(), PRIMARY KEY (`id`), UNIQUE KEY `username` (`username`), KEY `role_id` (`role_id`), CONSTRAINT `users_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `users` -- LOCK TABLES `users` WRITE; /*!40000 ALTER TABLE `users` DISABLE KEYS */; INSERT INTO `users` (`id`, `role_id`, `full_name`, `username`, `email`, `phone`, `password_hash`, `status`, `last_login`, `created_at`, `updated_at`) VALUES (1,1,'George','george',NULL,NULL,'$2y$10$8uEJ1l.j0PBowobMXTEJIOQDTxeFvaASF.r0hvQt6XO9zI5mvyugm','ACTIVE','2026-06-30 23:06:29','2026-06-14 07:39:02','2026-06-30 20:06:29'), (2,2,'Grace','grace',NULL,NULL,'$2y$10$GkekUyaZZKo0J0kR8hUq5O6/ny3z5sLKIF2QQqUKxJmh8tJGFEt.C','ACTIVE',NULL,'2026-06-14 07:39:02','2026-06-14 12:56:23'), (3,3,'Mary Ann','maryann',NULL,NULL,'$2y$10$GkekUyaZZKo0J0kR8hUq5O6/ny3z5sLKIF2QQqUKxJmh8tJGFEt.C','ACTIVE','2026-06-30 21:35:39','2026-06-14 07:39:02','2026-06-30 18:35:39'), (4,3,'Bedan','bedan',NULL,NULL,'$2y$10$GkekUyaZZKo0J0kR8hUq5O6/ny3z5sLKIF2QQqUKxJmh8tJGFEt.C','ACTIVE','2026-06-14 15:58:21','2026-06-14 07:39:02','2026-06-14 12:58:21'), (5,3,'John','john',NULL,NULL,'$2y$10$GkekUyaZZKo0J0kR8hUq5O6/ny3z5sLKIF2QQqUKxJmh8tJGFEt.C','ACTIVE','2026-06-30 21:02:29','2026-06-14 07:39:02','2026-06-30 18:02:29'); /*!40000 ALTER TABLE `users` ENABLE KEYS */; UNLOCK TABLES; -- -- Temporary table structure for view `v_current_stock_control` -- DROP TABLE IF EXISTS `v_current_stock_control`; /*!50001 DROP VIEW IF EXISTS `v_current_stock_control`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8mb4; /*!50001 CREATE VIEW `v_current_stock_control` AS SELECT 1 AS `id`, 1 AS `product_code`, 1 AS `product_name`, 1 AS `pos_category`, 1 AS `pos_group`, 1 AS `brand_name`, 1 AS `size_name`, 1 AS `unit_of_measure`, 1 AS `stock_balance`, 1 AS `reorder_level`, 1 AS `status`, 1 AS `is_stock_item` */; SET character_set_client = @saved_cs_client; -- -- Temporary table structure for view `v_expense_collections` -- DROP TABLE IF EXISTS `v_expense_collections`; /*!50001 DROP VIEW IF EXISTS `v_expense_collections`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8mb4; /*!50001 CREATE VIEW `v_expense_collections` AS SELECT 1 AS `expense_date`, 1 AS `cash_expenses`, 1 AS `mpesa_expenses`, 1 AS `bank_expenses` */; SET character_set_client = @saved_cs_client; -- -- Temporary table structure for view `v_finance_account_balances` -- DROP TABLE IF EXISTS `v_finance_account_balances`; /*!50001 DROP VIEW IF EXISTS `v_finance_account_balances`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8mb4; /*!50001 CREATE VIEW `v_finance_account_balances` AS SELECT 1 AS `id`, 1 AS `account_code`, 1 AS `account_name`, 1 AS `account_type`, 1 AS `calculated_balance` */; SET character_set_client = @saved_cs_client; -- -- Temporary table structure for view `v_finance_cashbook` -- DROP TABLE IF EXISTS `v_finance_cashbook`; /*!50001 DROP VIEW IF EXISTS `v_finance_cashbook`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8mb4; /*!50001 CREATE VIEW `v_finance_cashbook` AS SELECT 1 AS `movement_date`, 1 AS `movement_type`, 1 AS `reference_no`, 1 AS `description`, 1 AS `account_type`, 1 AS `money_in`, 1 AS `money_out`, 1 AS `created_by` */; SET character_set_client = @saved_cs_client; -- -- Temporary table structure for view `v_finance_today_summary` -- DROP TABLE IF EXISTS `v_finance_today_summary`; /*!50001 DROP VIEW IF EXISTS `v_finance_today_summary`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8mb4; /*!50001 CREATE VIEW `v_finance_today_summary` AS SELECT 1 AS `today`, 1 AS `sales_total`, 1 AS `gross_profit`, 1 AS `expenses_total`, 1 AS `transfers_total` */; SET character_set_client = @saved_cs_client; -- -- Temporary table structure for view `v_pos_collections` -- DROP TABLE IF EXISTS `v_pos_collections`; /*!50001 DROP VIEW IF EXISTS `v_pos_collections`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8mb4; /*!50001 CREATE VIEW `v_pos_collections` AS SELECT 1 AS `sale_day`, 1 AS `cash_collected`, 1 AS `mpesa_collected`, 1 AS `bank_collected`, 1 AS `wallet_used`, 1 AS `credit_sales`, 1 AS `home_consumption`, 1 AS `total_sales` */; SET character_set_client = @saved_cs_client; -- -- Temporary table structure for view `v_sale_items_summary` -- DROP TABLE IF EXISTS `v_sale_items_summary`; /*!50001 DROP VIEW IF EXISTS `v_sale_items_summary`*/; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8mb4; /*!50001 CREATE VIEW `v_sale_items_summary` AS SELECT 1 AS `sale_id`, 1 AS `items_sold` */; SET character_set_client = @saved_cs_client; -- -- Dumping events for database 'littrea1_nelqua-erp' -- -- -- Dumping routines for database 'littrea1_nelqua-erp' -- /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = '' */ ; /*!50003 DROP PROCEDURE IF EXISTS `nelqua_add_column_if_missing` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; DELIMITER ;; CREATE DEFINER=`root`@`localhost` PROCEDURE `nelqua_add_column_if_missing`( IN p_table_name VARCHAR(64), IN p_column_name VARCHAR(64), IN p_column_definition TEXT ) BEGIN IF NOT EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = p_table_name AND COLUMN_NAME = p_column_name ) THEN SET @sql_text = CONCAT('ALTER TABLE ', p_table_name, ' ADD COLUMN ', p_column_name, ' ', p_column_definition); PREPARE stmt FROM @sql_text; EXECUTE stmt; DEALLOCATE PREPARE stmt; END IF; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; -- -- Final view structure for view `v_current_stock_control` -- /*!50001 DROP VIEW IF EXISTS `v_current_stock_control`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = utf8mb4 */; /*!50001 SET character_set_results = utf8mb4 */; /*!50001 SET collation_connection = utf8mb4_unicode_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`cpses_licduhwnxd`@`localhost` SQL SECURITY DEFINER */ /*!50001 VIEW `v_current_stock_control` AS select `p`.`id` AS `id`,`p`.`product_code` AS `product_code`,`p`.`product_name` AS `product_name`,coalesce(`p`.`pos_category`,`p`.`product_type`,'General') AS `pos_category`,coalesce(`p`.`pos_group`,`p`.`product_name`) AS `pos_group`,coalesce(`b`.`brand_name`,'-') AS `brand_name`,coalesce(nullif(`p`.`size`,''),'-') AS `size_name`,coalesce(`p`.`unit_of_measure`,'Unit') AS `unit_of_measure`,`p`.`stock_balance` AS `stock_balance`,`p`.`reorder_level` AS `reorder_level`,`p`.`status` AS `status`,`p`.`is_stock_item` AS `is_stock_item` from (`products` `p` left join `brands` `b` on(`b`.`id` = `p`.`brand_id`)) where `p`.`status` = 'ACTIVE' and `p`.`is_stock_item` = 1 */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -- -- Final view structure for view `v_expense_collections` -- /*!50001 DROP VIEW IF EXISTS `v_expense_collections`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = utf8mb4 */; /*!50001 SET character_set_results = utf8mb4 */; /*!50001 SET collation_connection = utf8mb4_unicode_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`cpses_licqy5l54f`@`localhost` SQL SECURITY DEFINER */ /*!50001 VIEW `v_expense_collections` AS select `expenses`.`expense_date` AS `expense_date`,sum(case when `expenses`.`payment_mode` = 'CASH' and `expenses`.`approval_status` = 'APPROVED' then `expenses`.`amount` else 0 end) AS `cash_expenses`,sum(case when `expenses`.`payment_mode` = 'MPESA' and `expenses`.`approval_status` = 'APPROVED' then `expenses`.`amount` else 0 end) AS `mpesa_expenses`,sum(case when `expenses`.`payment_mode` = 'BANK' and `expenses`.`approval_status` = 'APPROVED' then `expenses`.`amount` else 0 end) AS `bank_expenses` from `expenses` group by `expenses`.`expense_date` */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -- -- Final view structure for view `v_finance_account_balances` -- /*!50001 DROP VIEW IF EXISTS `v_finance_account_balances`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = utf8mb4 */; /*!50001 SET character_set_results = utf8mb4 */; /*!50001 SET collation_connection = utf8mb4_unicode_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`cpses_licqy5l54f`@`localhost` SQL SECURITY DEFINER */ /*!50001 VIEW `v_finance_account_balances` AS select `a`.`id` AS `id`,`a`.`account_code` AS `account_code`,`a`.`account_name` AS `account_name`,`a`.`account_type` AS `account_type`,`a`.`opening_balance` + case when `a`.`account_type` = 'CASH' then coalesce((select sum(`sales`.`cash_amount`) from `sales`),0) when `a`.`account_type` = 'MPESA' then coalesce((select sum(`sales`.`mpesa_amount`) from `sales`),0) when `a`.`account_type` = 'BANK' then coalesce((select sum(`sales`.`bank_amount`) from `sales`),0) else 0 end - case when `a`.`account_type` = 'CASH' then coalesce((select sum(`expenses`.`amount`) from `expenses` where `expenses`.`payment_mode` = 'CASH' and `expenses`.`approval_status` = 'APPROVED'),0) when `a`.`account_type` = 'MPESA' then coalesce((select sum(`expenses`.`amount`) from `expenses` where `expenses`.`payment_mode` = 'MPESA' and `expenses`.`approval_status` = 'APPROVED'),0) when `a`.`account_type` = 'BANK' then coalesce((select sum(`expenses`.`amount`) from `expenses` where `expenses`.`payment_mode` = 'BANK' and `expenses`.`approval_status` = 'APPROVED'),0) else 0 end + coalesce((select sum(`t`.`amount`) from `finance_transfers` `t` where `t`.`to_account_id` = `a`.`id`),0) - coalesce((select sum(`t`.`amount`) from `finance_transfers` `t` where `t`.`from_account_id` = `a`.`id`),0) AS `calculated_balance` from `finance_accounts` `a` where `a`.`status` = 'ACTIVE' */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -- -- Final view structure for view `v_finance_cashbook` -- /*!50001 DROP VIEW IF EXISTS `v_finance_cashbook`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = utf8mb4 */; /*!50001 SET character_set_results = utf8mb4 */; /*!50001 SET collation_connection = utf8mb4_unicode_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`cpses_licqy5l54f`@`localhost` SQL SECURITY DEFINER */ /*!50001 VIEW `v_finance_cashbook` AS select `s`.`sale_date` AS `movement_date`,'SALE' AS `movement_type`,`s`.`receipt_no` AS `reference_no`,'Cash sale collection' AS `description`,'CASH' AS `account_type`,`s`.`cash_amount` AS `money_in`,0 AS `money_out`,`s`.`created_by` AS `created_by` from `sales` `s` where coalesce(`s`.`cash_amount`,0) > 0 union all select `s`.`sale_date` AS `sale_date`,'SALE' AS `SALE`,`s`.`receipt_no` AS `receipt_no`,'M-Pesa sale collection' AS `M-Pesa sale collection`,'MPESA' AS `MPESA`,`s`.`mpesa_amount` AS `mpesa_amount`,0 AS `0`,`s`.`created_by` AS `created_by` from `sales` `s` where coalesce(`s`.`mpesa_amount`,0) > 0 union all select `s`.`sale_date` AS `sale_date`,'SALE' AS `SALE`,`s`.`receipt_no` AS `receipt_no`,'Bank sale collection' AS `Bank sale collection`,'BANK' AS `BANK`,`s`.`bank_amount` AS `bank_amount`,0 AS `0`,`s`.`created_by` AS `created_by` from `sales` `s` where coalesce(`s`.`bank_amount`,0) > 0 union all select `e`.`expense_date` AS `expense_date`,'EXPENSE' AS `EXPENSE`,concat('EXP-',`e`.`id`) AS `CONCAT('EXP-',e.id)`,`e`.`description` AS `description`,`e`.`payment_mode` AS `payment_mode`,0 AS `0`,`e`.`amount` AS `amount`,`e`.`created_by` AS `created_by` from `expenses` `e` where `e`.`approval_status` = 'APPROVED' and `e`.`payment_mode` in ('CASH','MPESA','BANK') union all select `t`.`transfer_date` AS `transfer_date`,'TRANSFER OUT' AS `TRANSFER OUT`,`t`.`transfer_no` AS `transfer_no`,concat('Transfer to ',`ta`.`account_name`) AS `CONCAT('Transfer to ',ta.account_name)`,`fa`.`account_type` AS `account_type`,0 AS `0`,`t`.`amount` AS `amount`,`t`.`created_by` AS `created_by` from ((`finance_transfers` `t` join `finance_accounts` `fa` on(`fa`.`id` = `t`.`from_account_id`)) join `finance_accounts` `ta` on(`ta`.`id` = `t`.`to_account_id`)) union all select `t`.`transfer_date` AS `transfer_date`,'TRANSFER IN' AS `TRANSFER IN`,`t`.`transfer_no` AS `transfer_no`,concat('Transfer from ',`fa`.`account_name`) AS `CONCAT('Transfer from ',fa.account_name)`,`ta`.`account_type` AS `account_type`,`t`.`amount` AS `amount`,0 AS `0`,`t`.`created_by` AS `created_by` from ((`finance_transfers` `t` join `finance_accounts` `fa` on(`fa`.`id` = `t`.`from_account_id`)) join `finance_accounts` `ta` on(`ta`.`id` = `t`.`to_account_id`)) */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -- -- Final view structure for view `v_finance_today_summary` -- /*!50001 DROP VIEW IF EXISTS `v_finance_today_summary`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = utf8mb4 */; /*!50001 SET character_set_results = utf8mb4 */; /*!50001 SET collation_connection = utf8mb4_unicode_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`cpses_licqy5l54f`@`localhost` SQL SECURITY DEFINER */ /*!50001 VIEW `v_finance_today_summary` AS select curdate() AS `today`,coalesce((select sum(`sales`.`total_amount`) from `sales` where cast(`sales`.`sale_date` as date) = curdate()),0) AS `sales_total`,coalesce((select sum(`sales`.`gross_profit`) from `sales` where cast(`sales`.`sale_date` as date) = curdate()),0) AS `gross_profit`,coalesce((select sum(`expenses`.`amount`) from `expenses` where `expenses`.`expense_date` = curdate() and `expenses`.`approval_status` = 'APPROVED'),0) AS `expenses_total`,coalesce((select sum(`finance_transfers`.`amount`) from `finance_transfers` where cast(`finance_transfers`.`transfer_date` as date) = curdate()),0) AS `transfers_total` */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -- -- Final view structure for view `v_pos_collections` -- /*!50001 DROP VIEW IF EXISTS `v_pos_collections`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = utf8mb4 */; /*!50001 SET character_set_results = utf8mb4 */; /*!50001 SET collation_connection = utf8mb4_unicode_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`cpses_licqy5l54f`@`localhost` SQL SECURITY DEFINER */ /*!50001 VIEW `v_pos_collections` AS select cast(`s`.`sale_date` as date) AS `sale_day`,sum(coalesce(`s`.`cash_amount`,0)) AS `cash_collected`,sum(coalesce(`s`.`mpesa_amount`,0)) AS `mpesa_collected`,sum(coalesce(`s`.`bank_amount`,0)) AS `bank_collected`,sum(coalesce(`s`.`wallet_amount`,0)) AS `wallet_used`,sum(coalesce(`s`.`credit_amount`,0)) AS `credit_sales`,sum(coalesce(`s`.`home_amount`,0)) AS `home_consumption`,sum(coalesce(`s`.`total_amount`,0)) AS `total_sales` from `sales` `s` group by cast(`s`.`sale_date` as date) */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -- -- Final view structure for view `v_sale_items_summary` -- /*!50001 DROP VIEW IF EXISTS `v_sale_items_summary`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = utf8mb4 */; /*!50001 SET character_set_results = utf8mb4 */; /*!50001 SET collation_connection = utf8mb4_unicode_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`cpses_licduhwnxd`@`localhost` SQL SECURITY DEFINER */ /*!50001 VIEW `v_sale_items_summary` AS select `si`.`sale_id` AS `sale_id`,group_concat(concat(case when coalesce(`p`.`pos_category`,'') = 'Water' and coalesce(`p`.`pos_group`,'') = 'Water Refill' then concat('Water Refill ',coalesce(nullif(`p`.`size`,''),'')) when coalesce(`p`.`pos_category`,'') = 'Gas' and coalesce(`p`.`pos_group`,'') = 'Gas Refill' then concat('Gas Refill ',coalesce(`b`.`brand_name`,''),' ',coalesce(nullif(`p`.`size`,''),'')) else concat(coalesce(`p`.`pos_group`,`p`.`product_name`),case when coalesce(`b`.`brand_name`,'') <> '' then concat(' ',`b`.`brand_name`) else '' end,case when coalesce(`p`.`size`,'') <> '' then concat(' ',`p`.`size`) else '' end) end,' x ',format(`si`.`quantity`,0)) order by `si`.`id` ASC separator ', ') AS `items_sold` from ((`sale_items` `si` join `products` `p` on(`p`.`id` = `si`.`product_id`)) left join `brands` `b` on(`b`.`id` = `p`.`brand_id`)) group by `si`.`sale_id` */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*M!100616 SET NOTE_VERBOSITY=@OLD_NOTE_VERBOSITY */; -- Dump completed on 2026-06-30 23:33:50