--
-- Host: localhost    Database: amatsuro_posbase
-- ------------------------------------------------------


--
-- Table structure for table `AAX7019_business_settings`
--

CREATE TABLE `AAX7019_business_settings` (
  `setting_id` int(11) NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text NOT NULL,
  `setting_type` enum('string','boolean','number','json') NOT NULL DEFAULT 'string',
  `setting_group` int(11) NOT NULL DEFAULT '1' COMMENT '1=POS Settings, 2=Receipt Settings, 3=Printer Settings, 4=Staff Settings, 5=Inventory Settings',
  `setting_title` varchar(200) NOT NULL,
  `setting_description` varchar(500) DEFAULT NULL,
  `setting_text` varchar(200) DEFAULT NULL COMMENT 'Placeholder text for inputs',
  `setting_order` int(11) NOT NULL DEFAULT '0',
  `is_required` tinyint(1) NOT NULL DEFAULT '0',
  `setting_options` text COMMENT 'JSON string for dropdown/radio options',
  `description` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`setting_id`),
  UNIQUE KEY `unique_setting_key` (`setting_key`),
  KEY `idx_setting_group` (`setting_group`),
  KEY `idx_setting_order` (`setting_order`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `AAX7019_business_settings`
--

INSERT INTO `AAX7019_business_settings` VALUES (1,'show_item_search_bar','true','boolean',1,'Show Item Search Bar','Display search bar in POS items view for quick product lookup','',1,0,NULL,NULL,'2025-08-05 01:57:37','2025-08-05 01:57:37'),(2,'show_item_quick_stats','false','boolean',1,'Show Item Quick Stats','Display quick statistics like stock levels in POS items view','',2,0,NULL,NULL,'2025-08-05 01:57:37','2025-08-05 01:57:37'),(3,'show_category_filter','false','boolean',1,'Show Category Filter','Display category filter in POS items view','',3,0,NULL,NULL,'2025-08-05 01:57:37','2025-08-05 01:57:37'),(4,'allow_fast_checkout','false','boolean',1,'Allow Fast Checkout','Perform fast Cash Sales checkout in one click.','',4,0,NULL,NULL,'2025-08-05 01:57:37','2025-08-05 01:57:37'),(5,'show_invoice_upon_checkout','true','boolean',1,'Show Invoice Upon Checkout','Allow display of receipt details upon completing checkout.','',5,0,NULL,NULL,'2025-08-05 01:57:37','2025-08-05 01:57:37'),(6,'require_cashier_shift','false','boolean',2,'Require Cashier Shift','Monitor cashier cash drawer balance and other transaction.','',1,0,NULL,NULL,'2025-08-05 01:57:37','2025-08-05 01:57:37'),(7,'auto_print_receipt','false','boolean',3,'Auto Print Receipt','Automatically print receipt after successful checkout','',0,0,NULL,NULL,'2025-08-05 01:57:37','2025-08-05 01:57:37'),(8,'receipt_business_name','Manoktok Chicken','string',3,'Business Name','Business name shown on receipt (required)','',0,0,NULL,NULL,'2025-08-05 01:57:37','2025-08-05 01:59:37'),(9,'receipt_phone','09393896326','string',3,'Phone Number','Display business phone number on receipt','',4,0,NULL,NULL,'2025-08-05 01:57:37','2025-08-05 01:58:20'),(10,'receipt_address','B83 L10 Clyde Avenue, Celine Homes Subd. Bacolod City, Neg. Occ.','string',3,'Address','Display business address on receipt','',5,0,NULL,NULL,'2025-08-05 01:57:37','2025-08-05 01:58:52'),(11,'receipt_tax_id','000-000-000-000','string',3,'Tax ID/TIN','Display business tax identification number on receipt','',6,0,NULL,NULL,'2025-08-05 01:57:37','2025-08-05 01:57:37'),(12,'receipt_website','https://your.website.org','string',3,'Website','Display business website URL on receipt','',7,0,NULL,NULL,'2025-08-05 01:57:37','2025-08-05 01:57:37'),(13,'receipt_title','** Official Receipt **','string',3,'Receipt Title','Display custom receipt title/header','',8,0,NULL,NULL,'2025-08-05 01:57:37','2025-08-05 01:57:37'),(14,'show_cashier_name','true','boolean',3,'Show Cashier Name','Display cashier/staff name on receipt','',10,0,NULL,NULL,'2025-08-05 01:57:37','2025-08-05 01:57:37'),(15,'show_customer_name','true','boolean',3,'Show Customer Name','Display customer name on receipt when available','',11,0,NULL,NULL,'2025-08-05 01:57:37','2025-08-05 01:57:37'),(16,'show_customer_address','true','boolean',3,'Show Customer Address','Display customer address on receipt when available','',12,0,NULL,NULL,'2025-08-05 01:57:37','2025-08-05 01:57:37'),(17,'show_customer_tax_no','true','boolean',3,'Show Customer Tax Number','Display customer tax identification number when available','',13,0,NULL,NULL,'2025-08-05 01:57:37','2025-08-05 01:57:37'),(18,'receipt_price_inclusive','true','boolean',3,'Price Inclusive of Tax','Display note that prices are inclusive of applicable taxes','',14,0,NULL,NULL,'2025-08-05 01:57:37','2025-08-05 01:57:37'),(19,'show_total_item_count','true','boolean',3,'Show Total Item Count','Display total number of items in transaction','',15,0,NULL,NULL,'2025-08-05 01:57:37','2025-08-05 01:57:37'),(20,'show_change_amount','true','boolean',3,'Show Change Amount','Display change amount given to customer','',16,0,NULL,NULL,'2025-08-05 01:57:37','2025-08-05 01:57:37'),(21,'show_payment_details','true','boolean',3,'Show Payment Details','Display payment method and transaction details','',17,0,NULL,NULL,'2025-08-05 01:57:37','2025-08-05 01:57:37'),(22,'thank_you_note','Thank you for your business!','string',3,'Thank You Note','Display custom thank you message at receipt footer','',18,0,NULL,NULL,'2025-08-05 01:57:37','2025-08-05 01:57:37'),(23,'receipt_order_items_by','name','json',3,'Order Items By','How to order items on receipt: by name or order added','',20,0,'{\"name\":\"Product Name\",\"order_added\":\"Order Added\"}',NULL,'2025-08-05 01:57:37','2025-08-05 01:57:37');

--
-- Table structure for table `AAX7019_cashflow_transactions`
--

CREATE TABLE `AAX7019_cashflow_transactions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `transaction_type` enum('income','expense') COLLATE utf8mb4_unicode_ci NOT NULL,
  `category_id` int(11) NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `transaction_date` datetime NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `reference_number` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `payment_method_id` int(11) NOT NULL,
  `receipt_image_path` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `linked_sales_transaction_id` int(11) DEFAULT NULL,
  `created_by` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_transaction_type` (`transaction_type`),
  KEY `idx_category_id` (`category_id`),
  KEY `idx_transaction_date` (`transaction_date`),
  KEY `idx_linked_sales` (`linked_sales_transaction_id`),
  KEY `idx_payment_method_id` (`payment_method_id`)
) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAX7019_cashflow_transactions`
--

INSERT INTO `AAX7019_cashflow_transactions` VALUES (1,'income',1,4.00,'2025-07-31 17:13:41','Sales Transaction','TXN202507310003',3,NULL,'Auto-generated from POS sale',NULL,'Tbl Seven','2025-07-31 09:13:42','2025-07-31 09:13:42'),(2,'expense',13,2.00,'2025-07-31 17:20:19','Expense: Delivery of Goods','EXP202507310001',1,NULL,'Auto-created from expense management system [VOIDED: ]',NULL,NULL,'2025-07-31 09:20:34','2025-08-01 00:24:35'),(3,'income',1,8.00,'2025-07-31 20:32:22','Sales Transaction','TXN202507310004',3,NULL,'Auto-generated from POS sale',NULL,'Tbl Seven','2025-07-31 12:32:23','2025-07-31 12:32:23'),(4,'income',1,15000.00,'2025-07-31 20:33:20','Initial Fund',NULL,3,NULL,'',NULL,'Tbl Seven','2025-07-31 12:33:22','2025-07-31 12:33:22'),(5,'income',1,16.00,'2025-07-31 21:31:03','Sales Transaction','TXN202507310005',1,NULL,'Auto-generated from POS sale',NULL,'Tbl Seven','2025-07-31 13:31:04','2025-07-31 13:31:04'),(6,'income',1,24.00,'2025-07-31 21:32:24','Sales Transaction','TXN202507310006',2,NULL,'Auto-generated from POS sale',NULL,'Tbl Seven','2025-07-31 13:32:25','2025-07-31 13:32:25'),(7,'income',4,2.00,'2025-07-31 00:00:00','Expense Void Reversal: Delivery of Goods','EXP202507310001-VOID',1,NULL,'Auto-created reversal for voided expense: ',NULL,NULL,'2025-08-01 00:24:35','2025-08-01 00:24:35'),(8,'income',1,4.00,'2025-08-01 08:25:00','Sales Transaction','TXN202508010001',1,NULL,'Auto-generated from POS sale',NULL,'Tbl Seven','2025-08-01 00:25:01','2025-08-01 00:25:01'),(9,'expense',1,4.00,'2025-08-01 00:00:00','VOID: Transaction voided','TXN202508010001',1,NULL,'Auto-created from sales return/void system',NULL,NULL,'2025-08-01 00:25:16','2025-08-01 00:25:16'),(10,'income',1,8.00,'2025-08-01 12:18:47','Sales Transaction','TXN202508010002',1,NULL,'Auto-generated from POS sale',NULL,'Tbl Seven','2025-08-01 04:18:49','2025-08-01 04:18:49'),(11,'income',1,4.00,'2025-08-01 18:01:32','Sales Transaction','TXN202508010003',1,NULL,'Auto-generated from POS sale',NULL,'Tbl Seven','2025-08-01 10:01:33','2025-08-01 10:01:33'),(12,'expense',13,100.00,'2025-08-01 20:43:11','Expense: Delivery of Goods','EXP202508010001',1,NULL,'Auto-created from expense management system',NULL,NULL,'2025-08-01 12:43:20','2025-08-01 12:43:20'),(13,'expense',13,100.00,'2025-08-01 20:52:56','Expense: Purchases - Delivery of Goods','EXP202508010002',1,NULL,'Auto-created from expense management system',NULL,NULL,'2025-08-01 12:53:09','2025-08-01 12:53:09'),(14,'expense',1,250.00,'2025-08-01 21:00:32','Expense: Supplies - Office Supplies','EXP202508010003',1,NULL,'Auto-created from expense management system',NULL,NULL,'2025-08-01 13:00:36','2025-08-01 13:00:36'),(15,'income',3,4.00,'2025-08-01 00:00:00','Credit Settlement - Elda Lacson',NULL,1,NULL,'Auto-generated from customer credit payment',NULL,'AfrpyFPcjvb4YMDvjJkfX6X0UF83','2025-08-01 23:27:59','2025-08-01 23:27:59'),(16,'income',1,1665.00,'2025-08-02 07:33:24','Additional Fund',NULL,1,NULL,'change 977205',NULL,'Tbl Seven','2025-08-01 23:33:25','2025-08-01 23:33:25'),(17,'income',1,8.00,'2025-08-04 12:51:56','Sales Transaction','TXN202508040001',1,NULL,'POS Sale',NULL,'Tbl Seven','2025-08-04 04:51:57','2025-08-04 04:51:57'),(18,'expense',1,8.00,'2025-08-04 00:00:00','VOID: Transaction voided','TXN202508040001',1,NULL,'Auto-created from sales return/void system',NULL,NULL,'2025-08-04 04:52:30','2025-08-04 04:52:30'),(19,'income',1,12.00,'2025-08-04 12:53:46','Sales Transaction','TXN202508040002',1,NULL,'POS Sale',NULL,'Tbl Seven','2025-08-04 04:53:47','2025-08-04 04:53:47'),(20,'expense',1,12.00,'2025-08-04 00:00:00','VOID: Transaction voided','TXN202508040002',1,NULL,'Auto-created from sales return/void system',NULL,NULL,'2025-08-04 04:54:33','2025-08-04 04:54:33'),(21,'income',1,4.00,'2025-08-04 12:59:34','Sales Transaction','TXN202508040003',1,NULL,'POS Sale',NULL,'Tbl Seven','2025-08-04 04:59:35','2025-08-04 04:59:35'),(22,'expense',1,4.00,'2025-08-04 00:00:00','VOID: Transaction voided','TXN202508040003',1,NULL,'Auto-created from sales return/void system',NULL,NULL,'2025-08-04 05:00:05','2025-08-04 05:00:05'),(23,'income',1,4.00,'2025-08-04 13:03:50','Sales Transaction','TXN202508040004',1,NULL,'POS Sale',NULL,'Tbl Seven','2025-08-04 05:03:50','2025-08-04 05:03:50'),(24,'income',1,4.00,'2025-08-04 13:05:05','Sales Transaction','TXN202508040005',1,NULL,'POS Sale',NULL,'Tbl Seven','2025-08-04 05:05:06','2025-08-04 05:05:06'),(25,'expense',1,4.00,'2025-08-04 00:00:00','VOID: Transaction voided','TXN202508040005',1,NULL,'Auto-created from sales return/void system',NULL,NULL,'2025-08-04 05:07:15','2025-08-04 05:07:15'),(26,'expense',1,4.00,'2025-08-04 00:00:00','VOID: Transaction voided','TXN202508040004',1,NULL,'Auto-created from sales return/void system',NULL,NULL,'2025-08-04 05:07:48','2025-08-04 05:07:48'),(27,'income',1,40.00,'2025-08-04 13:14:28','Sales Transaction','TXN202508040006',1,NULL,'POS Sale',NULL,'Tbl Seven','2025-08-04 05:14:29','2025-08-04 05:14:29'),(28,'income',1,40.00,'2025-08-04 13:15:10','Sales Transaction','TXN202508040007',1,NULL,'POS Sale',NULL,'Tbl Seven','2025-08-04 05:15:11','2025-08-04 05:15:11'),(29,'income',1,40.00,'2025-08-04 13:23:54','Sales Transaction','TXN202508040008',1,NULL,'POS Sale',NULL,'Tbl Seven','2025-08-04 05:23:55','2025-08-04 05:23:55'),(30,'income',1,40.00,'2025-08-04 13:24:18','Sales Transaction','TXN202508040009',1,NULL,'POS Sale',NULL,'Tbl Seven','2025-08-04 05:24:18','2025-08-04 05:24:18'),(31,'income',1,40.00,'2025-08-04 13:29:42','Sales Transaction','TXN202508040010',1,NULL,'POS Sale',NULL,'Tbl Seven','2025-08-04 05:29:43','2025-08-04 05:29:43'),(32,'income',1,40.00,'2025-08-04 13:29:51','Sales Transaction','TXN202508040011',1,NULL,'POS Sale',NULL,'Tbl Seven','2025-08-04 05:29:52','2025-08-04 05:29:52'),(33,'income',1,320.00,'2025-08-04 13:30:12','Sales Transaction','TXN202508040012',1,NULL,'POS Sale',NULL,'Tbl Seven','2025-08-04 05:30:13','2025-08-04 05:30:13'),(34,'income',1,1.00,'2025-08-04 15:33:11','Sales Transaction','TXN202508040013',1,NULL,'POS Sale',NULL,'Tbl Seven','2025-08-04 07:33:12','2025-08-04 07:33:12'),(35,'expense',1,1.00,'2025-08-04 00:00:00','VOID: Transaction voided','TXN202508040013',1,NULL,'Auto-created from sales return/void system',NULL,NULL,'2025-08-04 07:33:36','2025-08-04 07:33:36'),(36,'income',1,1.00,'2025-08-04 18:52:29','Sales Transaction','OFF-1754304749848',1,NULL,'POS Sale',NULL,'Tbl Seven','2025-08-04 10:52:31','2025-08-04 10:52:31'),(37,'income',1,1.00,'2025-08-04 18:54:19','Sales Transaction','OFF-1754304859793',1,NULL,'POS Sale',NULL,'Tbl Seven','2025-08-04 10:54:20','2025-08-04 10:54:20'),(38,'income',1,1.00,'2025-08-04 19:09:31','Sales Transaction','TXN202508040016',1,NULL,'POS Sale',NULL,'Tbl Seven','2025-08-04 11:09:32','2025-08-04 11:09:32'),(39,'expense',1,1.00,'2025-08-04 00:00:00','VOID: Transaction voided','TXN202508040016',1,NULL,'Auto-created from sales return/void system',NULL,NULL,'2025-08-04 11:09:45','2025-08-04 11:09:45'),(40,'expense',1,1.00,'2025-08-04 00:00:00','VOID: Transaction voided','TXN202508040017',1,NULL,'Auto-created from sales return/void system',NULL,NULL,'2025-08-04 11:11:43','2025-08-04 11:11:43'),(41,'income',1,1.00,'2025-08-04 19:12:52','Sales Transaction','TXN202508040018',1,NULL,'POS Sale',NULL,'Tbl Seven','2025-08-04 11:12:53','2025-08-04 11:12:53'),(42,'expense',1,1.00,'2025-08-04 00:00:00','VOID: Transaction voided','TXN202508040018',1,NULL,'Auto-created from sales return/void system',NULL,NULL,'2025-08-04 11:13:17','2025-08-04 11:13:17'),(43,'income',1,4.00,'2025-08-04 22:05:12','Sales Transaction','TXN202508040019',1,NULL,'POS Sale',NULL,'Tbl Seven','2025-08-04 14:05:13','2025-08-04 14:05:13'),(44,'income',1,8.00,'2025-08-04 22:06:01','Sales Transaction','TXN202508040020',1,NULL,'POS Sale',NULL,'Tbl Seven','2025-08-04 14:06:02','2025-08-04 14:06:02'),(45,'income',1,5.00,'2025-08-04 22:34:40','Sales Transaction','TXN202508040021',1,NULL,'POS Sale',NULL,'Tbl Seven','2025-08-04 14:34:41','2025-08-04 14:34:41'),(46,'expense',1,1.00,'2025-08-05 06:35:18','Adjust',NULL,1,NULL,'',NULL,'Tbl Seven','2025-08-04 22:35:19','2025-08-04 22:35:19'),(47,'expense',1,12.00,'2025-08-05 06:35:50','Adjust',NULL,1,NULL,'',NULL,'Tbl Seven','2025-08-04 22:35:51','2025-08-04 22:35:51'),(48,'income',1,13.00,'2025-08-06 05:00:14','Sales Transaction','TXN202508060001',1,NULL,'POS Sale',NULL,'Tbl Seven','2025-08-05 21:00:15','2025-08-05 21:00:15');

--
-- Table structure for table `AAX7019_cashier_counts`
--

CREATE TABLE `AAX7019_cashier_counts` (
  `count_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `shift_id` bigint(20) NOT NULL,
  `count_type` enum('opening','closing','mid_shift') COLLATE utf8mb4_unicode_ci NOT NULL,
  `total_amount` decimal(10,2) NOT NULL,
  `counted_at` datetime NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`count_id`),
  KEY `idx_shift_counts` (`shift_id`,`count_type`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAX7019_cashier_counts`
--

INSERT INTO `AAX7019_cashier_counts` VALUES (1,1,'opening',1000.00,'2025-08-01 02:43:54','2025-08-01 09:43:54'),(2,2,'opening',1000.00,'2025-08-01 02:57:55','2025-08-01 09:57:55'),(3,2,'closing',50.00,'2025-08-04 06:39:54','2025-08-04 13:39:54'),(4,3,'opening',1000.00,'2025-08-04 06:44:38','2025-08-04 13:44:38'),(5,3,'closing',1000.00,'2025-08-04 06:45:07','2025-08-04 13:45:07'),(6,4,'opening',1000.00,'2025-08-04 06:46:04','2025-08-04 13:46:04'),(7,4,'closing',1000.00,'2025-08-04 06:46:40','2025-08-04 13:46:40'),(8,5,'opening',500.00,'2025-08-04 06:47:51','2025-08-04 13:47:51'),(9,5,'closing',500.00,'2025-08-04 06:49:36','2025-08-04 13:49:36'),(10,6,'opening',1000.00,'2025-08-04 07:04:55','2025-08-04 14:04:55'),(11,6,'closing',1000.00,'2025-08-04 16:25:16','2025-08-04 23:25:16'),(12,7,'opening',1000.00,'2025-08-04 16:38:44','2025-08-04 23:38:44'),(13,7,'closing',1000.00,'2025-08-04 16:39:10','2025-08-04 23:39:10'),(14,8,'opening',1000.00,'2025-08-04 16:39:38','2025-08-04 23:39:38'),(15,8,'closing',1000.00,'2025-08-04 16:39:58','2025-08-04 23:39:58'),(16,9,'opening',1000.00,'2025-08-04 16:48:54','2025-08-04 23:48:54'),(17,9,'closing',1000.00,'2025-08-04 16:49:07','2025-08-04 23:49:07');

--
-- Table structure for table `AAX7019_cashier_denominations`
--

CREATE TABLE `AAX7019_cashier_denominations` (
  `denomination_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `count_id` bigint(20) NOT NULL,
  `denomination_value` decimal(10,2) NOT NULL,
  `quantity` int(11) NOT NULL,
  `subtotal` decimal(10,2) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`denomination_id`),
  KEY `idx_count_denoms` (`count_id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAX7019_cashier_denominations`
--

INSERT INTO `AAX7019_cashier_denominations` VALUES (1,1,1000.00,1,1000.00,'2025-08-01 09:43:54'),(2,2,1000.00,1,1000.00,'2025-08-01 09:57:55'),(3,3,50.00,1,50.00,'2025-08-04 13:39:54'),(4,4,1000.00,1,1000.00,'2025-08-04 13:44:38'),(5,5,1000.00,1,1000.00,'2025-08-04 13:45:07'),(6,6,1000.00,1,1000.00,'2025-08-04 13:46:04'),(7,7,1000.00,1,1000.00,'2025-08-04 13:46:40'),(8,8,500.00,1,500.00,'2025-08-04 13:47:51'),(9,9,500.00,1,500.00,'2025-08-04 13:49:36'),(10,10,1000.00,1,1000.00,'2025-08-04 14:04:55'),(11,11,1000.00,1,1000.00,'2025-08-04 23:25:16'),(12,12,1000.00,1,1000.00,'2025-08-04 23:38:44'),(13,13,1000.00,1,1000.00,'2025-08-04 23:39:10'),(14,14,1000.00,1,1000.00,'2025-08-04 23:39:38'),(15,15,1000.00,1,1000.00,'2025-08-04 23:39:58'),(16,16,1000.00,1,1000.00,'2025-08-04 23:48:54'),(17,17,1000.00,1,1000.00,'2025-08-04 23:49:07');

--
-- Table structure for table `AAX7019_cashier_shifts`
--

CREATE TABLE `AAX7019_cashier_shifts` (
  `shift_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `cashier_id` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `shift_start` datetime NOT NULL,
  `shift_end` datetime DEFAULT NULL,
  `opening_amount` decimal(10,2) NOT NULL,
  `closing_amount` decimal(10,2) DEFAULT NULL,
  `expected_amount` decimal(10,2) DEFAULT NULL,
  `difference_amount` decimal(10,2) DEFAULT NULL,
  `status` enum('open','closed') COLLATE utf8mb4_unicode_ci DEFAULT 'open',
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`shift_id`),
  KEY `idx_cashier_status` (`cashier_id`,`status`),
  KEY `idx_shift_dates` (`shift_start`,`shift_end`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAX7019_cashier_shifts`
--

INSERT INTO `AAX7019_cashier_shifts` VALUES (2,'AfrpyFPcjvb4YMDvjJkfX6X0UF83','2025-08-01 02:57:55','2025-08-04 21:39:53',1000.00,50.00,1085.00,-1035.00,'closed',NULL,'2025-08-01 09:57:55','2025-08-04 13:39:54'),(3,'AfrpyFPcjvb4YMDvjJkfX6X0UF83','2025-08-04 21:44:37','2025-08-04 21:45:06',1000.00,1000.00,1000.00,0.00,'closed',NULL,'2025-08-04 13:44:38','2025-08-04 13:45:07'),(4,'AfrpyFPcjvb4YMDvjJkfX6X0UF83','2025-08-04 21:46:03','2025-08-04 21:46:39',1000.00,1000.00,1000.00,0.00,'closed',NULL,'2025-08-04 13:46:04','2025-08-04 13:46:40'),(5,'AfrpyFPcjvb4YMDvjJkfX6X0UF83','2025-08-04 21:47:50','2025-08-04 21:49:35',500.00,500.00,500.00,0.00,'closed',NULL,'2025-08-04 13:47:51','2025-08-04 13:49:36'),(6,'AfrpyFPcjvb4YMDvjJkfX6X0UF83','2025-08-04 22:04:54','2025-08-05 07:25:16',1000.00,1000.00,1004.00,-4.00,'closed',NULL,'2025-08-04 14:04:55','2025-08-04 23:25:16'),(7,'scOM1lGNfnURLDD1ybjcuTJQ8yE2','2025-08-05 07:38:43','2025-08-05 07:39:09',1000.00,1000.00,1000.00,0.00,'closed',NULL,'2025-08-04 23:38:44','2025-08-04 23:39:10'),(8,'AfrpyFPcjvb4YMDvjJkfX6X0UF83','2025-08-05 07:39:37','2025-08-05 07:39:57',1000.00,1000.00,1000.00,0.00,'closed',NULL,'2025-08-04 23:39:38','2025-08-04 23:39:58'),(9,'AfrpyFPcjvb4YMDvjJkfX6X0UF83','2025-08-05 07:48:53','2025-08-05 07:49:06',1000.00,1000.00,1000.00,0.00,'closed',NULL,'2025-08-04 23:48:54','2025-08-04 23:49:07');

--
-- Table structure for table `AAX7019_cashier_transactions`
--

CREATE TABLE `AAX7019_cashier_transactions` (
  `transaction_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `shift_id` bigint(20) NOT NULL,
  `transaction_type` enum('sale','refund','void','payout','cash_in','cash_out','expense') COLLATE utf8mb4_unicode_ci NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `reference_id` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `payment_method` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`transaction_id`),
  KEY `idx_shift_transactions` (`shift_id`,`transaction_type`),
  KEY `idx_reference` (`reference_id`)
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAX7019_cashier_transactions`
--

INSERT INTO `AAX7019_cashier_transactions` VALUES (1,2,'sale',4.00,'TXN202508010003','Sale: Walk-in Customer','cash','2025-08-02 01:01:32'),(2,2,'expense',100.00,'EXP202508010001','Expense: Delivery of Goods','cash','2025-08-02 03:43:20'),(3,2,'expense',100.00,'EXP202508010002','Expense: Purchases - Delivery of Goods','cash','2025-08-02 03:53:09'),(4,2,'expense',250.00,'EXP202508010003','Expense: Supplies - Office Supplies','cash','2025-08-02 04:00:36'),(5,2,'cash_in',4.00,'1','Credit Settlement: Elda Lacson','cash','2025-08-02 14:27:59'),(6,2,'sale',8.00,'TXN202508040001','Credit Sale: Customer','credit','2025-08-04 19:51:56'),(7,2,'void',8.00,'RTN202508040001','VOID: TXN202508040001','cash','2025-08-04 19:52:30'),(8,2,'sale',12.00,'TXN202508040002','Credit Sale: Customer','credit','2025-08-04 19:53:46'),(9,2,'void',12.00,'RTN202508040002','VOID: TXN202508040002','cash','2025-08-04 19:54:33'),(10,2,'sale',4.00,'TXN202508040003','Credit Sale: Customer','credit','2025-08-04 19:59:34'),(11,2,'void',4.00,'RTN202508040003','VOID: TXN202508040003','cash','2025-08-04 20:00:05'),(12,2,'sale',4.00,'TXN202508040004','Credit Sale: Customer','credit','2025-08-04 20:03:50'),(13,2,'sale',4.00,'TXN202508040005','Credit Sale: Customer','credit','2025-08-04 20:05:05'),(14,2,'void',4.00,'RTN202508040004','VOID: TXN202508040005','cash','2025-08-04 20:07:15'),(15,2,'void',4.00,'RTN202508040005','VOID: TXN202508040004','cash','2025-08-04 20:07:48'),(16,2,'sale',40.00,'TXN202508040006','Sale: Walk-in Customer','cash','2025-08-04 20:14:28'),(17,2,'sale',40.00,'TXN202508040007','Sale: Walk-in Customer','cash','2025-08-04 20:15:10'),(18,2,'sale',40.00,'TXN202508040008','Sale: Walk-in Customer','cash','2025-08-04 20:23:54'),(19,2,'sale',40.00,'TXN202508040009','Sale: Walk-in Customer','cash','2025-08-04 20:24:18'),(20,2,'sale',40.00,'TXN202508040010','Sale: Walk-in Customer','cash','2025-08-04 20:29:42'),(21,2,'sale',40.00,'TXN202508040011','Sale: Walk-in Customer','cash','2025-08-04 20:29:51'),(22,2,'sale',320.00,'TXN202508040012','Sale: Walk-in Customer','cash','2025-08-04 20:30:12'),(23,2,'sale',1.00,'TXN202508040013','Credit Sale: Customer','credit','2025-08-04 22:33:11'),(24,2,'void',1.00,'RTN202508040006','VOID: TXN202508040013','cash','2025-08-04 22:33:36'),(25,2,'sale',1.00,'TXN202508040014','Sale: Walk-in Customer','cash','2025-08-05 02:09:24'),(26,2,'sale',1.00,'TXN202508040015','Sale: Walk-in Customer','cash','2025-08-05 02:09:25'),(27,2,'sale',1.00,'TXN202508040016','Sale: Walk-in Customer','cash','2025-08-05 02:09:31'),(28,2,'void',1.00,'RTN202508040007','VOID: TXN202508040016','cash','2025-08-05 02:09:45'),(29,2,'sale',1.00,'TXN202508040017','Sale: Walk-in Customer','credit','2025-08-05 02:11:01'),(30,2,'void',1.00,'RTN202508040008','VOID: TXN202508040017','cash','2025-08-05 02:11:43'),(31,2,'sale',1.00,'TXN202508040018','Credit Sale: Customer','credit','2025-08-05 02:12:52'),(32,2,'void',1.00,'RTN202508040009','VOID: TXN202508040018','cash','2025-08-05 02:13:17'),(33,6,'sale',4.00,'TXN202508040019','Sale: Walk-in Customer','cash','2025-08-05 05:05:12'),(34,6,'sale',8.00,'TXN202508040020','Credit Sale: Customer','credit','2025-08-05 05:06:01'),(35,6,'sale',5.00,'TXN202508040021','Credit Sale: Customer','credit','2025-08-05 05:34:40');

--
-- Table structure for table `AAX7019_customer_accounts`
--

CREATE TABLE `AAX7019_customer_accounts` (
  `customer_id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_code` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Unique customer identifier within business',
  `customer_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `customer_phone` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `customer_email` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `customer_address` text COLLATE utf8mb4_unicode_ci,
  `customer_type` enum('individual','business') COLLATE utf8mb4_unicode_ci DEFAULT 'individual',
  `tax_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'TIN or business registration number',
  `credit_enabled` tinyint(1) DEFAULT '0' COMMENT 'Whether customer can purchase on credit',
  `customer_status` enum('active','inactive','suspended') COLLATE utf8mb4_unicode_ci DEFAULT 'active',
  `date_of_birth` date DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `password_hash` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Hashed password for email login',
  `google_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Google OAuth ID',
  `facebook_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Facebook OAuth ID',
  `oauth_provider` enum('google','facebook','email') COLLATE utf8mb4_unicode_ci DEFAULT 'email' COMMENT 'Authentication provider',
  `email_verified` tinyint(1) DEFAULT '0' COMMENT 'Email verification status',
  `verification_token` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Email verification token',
  `reset_token` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Password reset token',
  `reset_token_expires` datetime DEFAULT NULL COMMENT 'Password reset token expiry',
  `last_login` timestamp NULL DEFAULT NULL COMMENT 'Last login timestamp',
  `login_count` int(11) DEFAULT '0' COMMENT 'Number of successful logins',
  `login_method` enum('password','passwordless') COLLATE utf8mb4_unicode_ci DEFAULT 'password' COMMENT 'Login method preference',
  `login_token` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Passwordless login token',
  `login_token_expires` datetime DEFAULT NULL COMMENT 'Passwordless login token expiry',
  PRIMARY KEY (`customer_id`),
  UNIQUE KEY `uk_customer_code` (`customer_code`),
  UNIQUE KEY `uk_customer_phone` (`customer_phone`),
  KEY `idx_customer_name` (`customer_name`),
  KEY `idx_customer_status` (`customer_status`),
  KEY `idx_credit_enabled` (`credit_enabled`),
  KEY `idx_google_id` (`google_id`),
  KEY `idx_facebook_id` (`facebook_id`),
  KEY `idx_verification_token` (`verification_token`),
  KEY `idx_reset_token` (`reset_token`),
  KEY `idx_oauth_provider` (`oauth_provider`),
  KEY `idx_login_token` (`login_token`),
  KEY `idx_login_method` (`login_method`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAX7019_customer_accounts`
--

INSERT INTO `AAX7019_customer_accounts` VALUES (1,'CUST-AAX7019-0001','Elda Lacson','09176475738',NULL,NULL,'individual',NULL,1,'active',NULL,NULL,'2025-07-31 13:32:10','2025-07-31 13:32:10',NULL,NULL,NULL,'email',0,NULL,NULL,NULL,NULL,0,'password',NULL,NULL),(18,'CUST0001','Pending Verification',NULL,'tlacson7@gmail.com',NULL,'individual',NULL,0,'active',NULL,NULL,'2025-08-07 03:15:36','2025-08-07 03:15:54',NULL,NULL,NULL,'email',1,NULL,NULL,'2025-08-07 20:15:36',NULL,0,'password',NULL,NULL);

--
-- Table structure for table `AAX7019_customer_credit_accounts`
--

CREATE TABLE `AAX7019_customer_credit_accounts` (
  `credit_account_id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `credit_limit` decimal(15,2) DEFAULT '0.00' COMMENT 'Maximum credit limit',
  `current_balance` decimal(15,2) DEFAULT '0.00' COMMENT 'Current outstanding balance',
  `available_credit` decimal(15,2) DEFAULT '0.00' COMMENT 'Available credit (limit - balance)',
  `payment_terms` enum('cash','net_7','net_15','net_30','net_45','net_60') COLLATE utf8mb4_unicode_ci DEFAULT 'net_30',
  `interest_rate` decimal(5,2) DEFAULT '0.00' COMMENT 'Monthly interest rate percentage',
  `last_payment_date` date DEFAULT NULL,
  `last_purchase_date` date DEFAULT NULL,
  `overdue_amount` decimal(15,2) DEFAULT '0.00',
  `days_overdue` int(11) DEFAULT '0',
  `credit_status` enum('good','warning','overdue','suspended') COLLATE utf8mb4_unicode_ci DEFAULT 'good',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`credit_account_id`),
  UNIQUE KEY `uk_customer_credit` (`customer_id`),
  CONSTRAINT `AAX7019_customer_credit_accounts_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `AAX7019_customer_accounts` (`customer_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAX7019_customer_credit_accounts`
--

INSERT INTO `AAX7019_customer_credit_accounts` VALUES (1,1,1000.00,53.00,947.00,'net_30',0.00,'2025-08-01','2025-08-04',0.00,0,'good','2025-07-31 13:32:10','2025-08-04 14:34:40'),(2,2,500.00,4.00,496.00,'net_30',0.00,NULL,'2025-08-04',0.00,0,'good','2025-08-04 04:59:22','2025-08-04 11:13:17'),(3,3,10.00,0.00,10.00,'net_30',0.00,NULL,'2025-08-04',0.00,0,'good','2025-08-04 07:31:03','2025-08-04 07:33:36');

--
-- Temporary table structure for view `AAX7019_customer_outstanding_view`
--

/*!50001 DROP VIEW IF EXISTS `AAX7019_customer_outstanding_view`*/;
/*!50001 CREATE VIEW `AAX7019_customer_outstanding_view` AS SELECT 
 1 AS `customer_id`,
 1 AS `customer_code`,
 1 AS `customer_name`,
 1 AS `customer_phone`,
 1 AS `current_balance`,
 1 AS `overdue_amount`,
 1 AS `days_overdue`,
 1 AS `credit_status`,
 1 AS `last_payment_date`,
 1 AS `next_due_date`*/;

--
-- Table structure for table `AAX7019_customer_payment_history`
--

CREATE TABLE `AAX7019_customer_payment_history` (
  `payment_id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `customer_transaction_id` int(11) NOT NULL,
  `payment_amount` decimal(15,2) NOT NULL,
  `payment_method_id` int(11) NOT NULL,
  `reference_number` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `payment_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `processed_by` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `bank_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `check_date` date DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`payment_id`),
  KEY `idx_customer_id` (`customer_id`),
  KEY `idx_payment_date` (`payment_date`),
  KEY `idx_payment_method_id` (`payment_method_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAX7019_customer_payment_history`
--

INSERT INTO `AAX7019_customer_payment_history` VALUES (1,1,2,4.00,1,NULL,'2025-08-01 23:27:59','AfrpyFPcjvb4YMDvjJkfX6X0UF83',NULL,NULL,NULL,'2025-08-01 23:27:59');

--
-- Table structure for table `AAX7019_customer_sequences`
--

CREATE TABLE `AAX7019_customer_sequences` (
  `sequence_name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `current_value` int(11) NOT NULL DEFAULT '0',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`sequence_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAX7019_customer_sequences`
--

INSERT INTO `AAX7019_customer_sequences` VALUES ('customer_code',4,'2025-08-04 10:14:32');

--
-- Table structure for table `AAX7019_customer_sessions`
--

CREATE TABLE `AAX7019_customer_sessions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `session_token` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_agent` text COLLATE utf8mb4_unicode_ci,
  `expires_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `session_token` (`session_token`),
  KEY `idx_session_token` (`session_token`),
  KEY `idx_customer_id` (`customer_id`),
  KEY `idx_expires_at` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAX7019_customer_sessions`
--


--
-- Temporary table structure for view `AAX7019_customer_summary_view`
--

/*!50001 DROP VIEW IF EXISTS `AAX7019_customer_summary_view`*/;
/*!50001 CREATE VIEW `AAX7019_customer_summary_view` AS SELECT 
 1 AS `customer_id`,
 1 AS `customer_code`,
 1 AS `customer_name`,
 1 AS `customer_phone`,
 1 AS `customer_email`,
 1 AS `customer_status`,
 1 AS `credit_enabled`,
 1 AS `credit_limit`,
 1 AS `current_balance`,
 1 AS `available_credit`,
 1 AS `overdue_amount`,
 1 AS `days_overdue`,
 1 AS `credit_status`,
 1 AS `last_purchase_date`,
 1 AS `last_payment_date`,
 1 AS `total_purchases`,
 1 AS `total_purchase_amount`,
 1 AS `customer_since`*/;

--
-- Table structure for table `AAX7019_customer_transactions`
--

CREATE TABLE `AAX7019_customer_transactions` (
  `customer_transaction_id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `sales_transaction_id` int(11) DEFAULT NULL COMMENT 'Link to sales transaction if applicable',
  `transaction_type` enum('sale','payment','credit_adjustment','interest_charge','fee') COLLATE utf8mb4_unicode_ci NOT NULL,
  `amount` decimal(15,2) NOT NULL COMMENT 'Transaction amount (positive for charges, negative for payments)',
  `balance_before` decimal(15,2) NOT NULL COMMENT 'Balance before this transaction',
  `balance_after` decimal(15,2) NOT NULL COMMENT 'Balance after this transaction',
  `payment_method_id` int(11) DEFAULT NULL,
  `reference_number` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Check number, transfer reference, etc.',
  `transaction_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `due_date` date DEFAULT NULL COMMENT 'Due date for credit sales',
  `notes` text COLLATE utf8mb4_unicode_ci,
  `processed_by` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Staff member who processed',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`customer_transaction_id`),
  KEY `idx_customer_id` (`customer_id`),
  KEY `idx_transaction_type` (`transaction_type`),
  KEY `idx_transaction_date` (`transaction_date`),
  KEY `idx_sales_transaction` (`sales_transaction_id`),
  KEY `idx_payment_method_id` (`payment_method_id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAX7019_customer_transactions`
--

INSERT INTO `AAX7019_customer_transactions` VALUES (1,1,6,'sale',24.00,0.00,24.00,NULL,NULL,'2025-07-31 13:32:25','2025-08-30','Credit sale transaction','Tbl Seven','2025-07-31 13:32:25'),(2,1,NULL,'payment',-4.00,24.00,20.00,1,NULL,'2025-08-01 23:27:59',NULL,NULL,'AfrpyFPcjvb4YMDvjJkfX6X0UF83','2025-08-01 23:27:59'),(3,1,10,'sale',8.00,20.00,28.00,NULL,NULL,'2025-08-04 04:51:56','2025-09-03','Credit sale transaction','Tbl Seven','2025-08-04 04:51:56'),(4,1,11,'sale',12.00,28.00,40.00,NULL,NULL,'2025-08-04 04:53:46','2025-09-03','Credit sale transaction','Tbl Seven','2025-08-04 04:53:46'),(5,2,12,'sale',4.00,0.00,4.00,NULL,NULL,'2025-08-04 04:59:34','2025-09-03','Credit sale transaction','Tbl Seven','2025-08-04 04:59:34'),(6,2,13,'sale',4.00,4.00,8.00,NULL,NULL,'2025-08-04 05:03:50','2025-09-03','Credit sale transaction','Tbl Seven','2025-08-04 05:03:50'),(7,2,14,'sale',4.00,8.00,12.00,NULL,NULL,'2025-08-04 05:05:05','2025-09-03','Credit sale transaction','Tbl Seven','2025-08-04 05:05:05'),(8,2,14,'credit_adjustment',-4.00,12.00,8.00,NULL,NULL,'2025-08-04 05:07:15',NULL,'Credit sale voided','AfrpyFPcjvb4YMDvjJkfX6X0UF83','2025-08-04 05:07:15'),(9,2,13,'credit_adjustment',-4.00,8.00,4.00,NULL,NULL,'2025-08-04 05:07:48',NULL,'Credit sale voided','AfrpyFPcjvb4YMDvjJkfX6X0UF83','2025-08-04 05:07:48'),(10,3,22,'sale',1.00,0.00,1.00,NULL,NULL,'2025-08-04 07:33:11','2025-09-03','Credit sale transaction','Tbl Seven','2025-08-04 07:33:11'),(11,3,22,'credit_adjustment',-1.00,1.00,0.00,NULL,NULL,'2025-08-04 07:33:36',NULL,'Credit sale voided','AfrpyFPcjvb4YMDvjJkfX6X0UF83','2025-08-04 07:33:36'),(12,2,90,'sale',1.00,4.00,5.00,NULL,NULL,'2025-08-04 11:12:52','2025-09-03','Credit sale transaction','Tbl Seven','2025-08-04 11:12:52'),(13,2,90,'credit_adjustment',-1.00,5.00,4.00,NULL,NULL,'2025-08-04 11:13:17',NULL,'Credit sale voided','AfrpyFPcjvb4YMDvjJkfX6X0UF83','2025-08-04 11:13:17'),(14,1,92,'sale',8.00,40.00,48.00,NULL,NULL,'2025-08-04 14:06:01','2025-09-03','Credit sale transaction','Tbl Seven','2025-08-04 14:06:01'),(15,1,93,'sale',5.00,48.00,53.00,NULL,NULL,'2025-08-04 14:34:40','2025-09-03','Credit sale transaction','Tbl Seven','2025-08-04 14:34:40');

--
-- Table structure for table `AAX7019_denominations_config`
--

CREATE TABLE `AAX7019_denominations_config` (
  `config_id` int(11) NOT NULL AUTO_INCREMENT,
  `denomination_value` decimal(10,2) NOT NULL,
  `currency_code` varchar(3) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'PHP',
  `currency_symbol` varchar(5) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '₱',
  `is_coin` tinyint(1) NOT NULL DEFAULT '0',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `display_order` int(11) NOT NULL DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`config_id`),
  UNIQUE KEY `idx_denom_value` (`denomination_value`),
  KEY `idx_active_order` (`is_active`,`display_order`),
  KEY `idx_coin_active` (`is_coin`,`is_active`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAX7019_denominations_config`
--

INSERT INTO `AAX7019_denominations_config` VALUES (1,0.01,'PHP','₱',1,1,1,'2025-07-31 08:24:04','2025-07-31 08:24:04'),(2,0.05,'PHP','₱',1,1,2,'2025-07-31 08:24:04','2025-07-31 08:24:04'),(3,0.10,'PHP','₱',1,1,3,'2025-07-31 08:24:04','2025-07-31 08:24:04'),(4,0.25,'PHP','₱',1,1,4,'2025-07-31 08:24:04','2025-07-31 08:24:04'),(5,1.00,'PHP','₱',1,1,5,'2025-07-31 08:24:04','2025-07-31 08:24:04'),(6,5.00,'PHP','₱',1,1,6,'2025-07-31 08:24:04','2025-07-31 08:24:04'),(7,10.00,'PHP','₱',1,1,7,'2025-07-31 08:24:04','2025-07-31 08:24:04'),(8,20.00,'PHP','₱',0,1,8,'2025-07-31 08:24:04','2025-07-31 08:24:04'),(9,50.00,'PHP','₱',0,1,9,'2025-07-31 08:24:04','2025-07-31 08:24:04'),(10,100.00,'PHP','₱',0,1,10,'2025-07-31 08:24:04','2025-07-31 08:24:04'),(11,200.00,'PHP','₱',0,1,11,'2025-07-31 08:24:04','2025-07-31 08:24:04'),(12,500.00,'PHP','₱',0,1,12,'2025-07-31 08:24:04','2025-07-31 08:24:04'),(13,1000.00,'PHP','₱',0,1,13,'2025-07-31 08:24:04','2025-07-31 08:24:04');

--
-- Table structure for table `AAX7019_expenses_approvals`
--

CREATE TABLE `AAX7019_expenses_approvals` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `expense_id` int(11) NOT NULL,
  `approver_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `approval_level` int(11) DEFAULT '1',
  `status` enum('PENDING','APPROVED','REJECTED') COLLATE utf8mb4_unicode_ci DEFAULT 'PENDING',
  `comments` text COLLATE utf8mb4_unicode_ci,
  `approved_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `expense_id` (`expense_id`),
  CONSTRAINT `AAX7019_expenses_approvals_ibfk_1` FOREIGN KEY (`expense_id`) REFERENCES `AAX7019_expenses_transactions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAX7019_expenses_approvals`
--


--
-- Table structure for table `AAX7019_expenses_categories`
--

CREATE TABLE `AAX7019_expenses_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `category_code` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `parent_category_id` int(11) DEFAULT NULL,
  `is_system_category` tinyint(1) DEFAULT '0',
  `requires_approval` tinyint(1) DEFAULT '0',
  `approval_limit` decimal(15,2) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `category_code` (`category_code`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAX7019_expenses_categories`
--

INSERT INTO `AAX7019_expenses_categories` VALUES (1,'Supplies and Office Materials','SUPPLIES','Stationery, cleaning supplies, packaging materials, and minor operational items.',NULL,1,0,0.00,1,'2025-07-31 08:24:01','2025-07-31 08:24:01'),(2,'Rent and Lease','RENT','Expenses for renting or leasing storefronts, offices, or storage spaces.',NULL,1,0,0.00,1,'2025-07-31 08:24:01','2025-07-31 08:24:01'),(3,'Marketing and Advertising','MARKETING','Promotional activities, social media marketing, printing, and signage.',NULL,1,0,0.00,1,'2025-07-31 08:24:01','2025-07-31 08:24:01'),(4,'Professional Services','PROFESSIONAL','Fees for legal, accounting, consulting, or other professional services.',NULL,1,0,0.00,1,'2025-07-31 08:24:01','2025-07-31 08:24:01'),(5,'Technology and Software','TECHNOLOGY','Expenses for POS software, business management tools, and IT support.',NULL,1,0,0.00,1,'2025-07-31 08:24:01','2025-07-31 08:24:01'),(6,'Transportation and Delivery','TRANSPORTATION','Costs for vehicle expenses, fuel, courier, or delivery services.',NULL,1,0,0.00,1,'2025-07-31 08:24:01','2025-07-31 08:24:01'),(7,'Insurance','INSURANCE','Business, property, liability, and worker\'s compensation insurance premiums.',NULL,1,0,0.00,1,'2025-07-31 08:24:01','2025-07-31 08:24:01'),(8,'Training and Development','TRAINING','Employee training programs, workshops, and skill development.',NULL,1,0,0.00,1,'2025-07-31 08:24:01','2025-07-31 08:24:01'),(9,'Equipment and Maintenance','MAINTENANCE','Purchase or repair of equipment, machinery, kitchen appliances, or POS hardware.',NULL,1,0,0.00,1,'2025-07-31 08:24:01','2025-07-31 08:24:01'),(10,'Banking and Financial Charges','BANK_FEES','Bank fees, interest on loans, credit card processing fees.',NULL,1,0,0.00,1,'2025-07-31 08:24:01','2025-07-31 08:24:01'),(11,'Licenses and Permits','PERMITS','Fees paid to government authorities for operational licenses and permits.',NULL,1,0,0.00,1,'2025-07-31 08:24:01','2025-07-31 08:24:01'),(12,'Payroll Taxes and Benefits','BENEFITS','Employer contributions to social security, health insurance, retirement plans, and other benefits.',NULL,1,0,0.00,1,'2025-07-31 08:24:01','2025-07-31 08:24:01'),(13,'Inventory Purchases','INVENTORY','Cost of goods bought for resale or use in operations, including wholesale stock.',NULL,1,0,0.00,1,'2025-07-31 08:24:01','2025-07-31 08:24:01'),(14,'Utilities','UTILITIES','Costs for electricity, water, gas, internet, and phone services.',NULL,1,0,0.00,1,'2025-07-31 08:24:01','2025-07-31 08:24:01'),(15,'Salaries and Wages','SALARIES','Payments to employees, including wages, salaries, and overtime.',NULL,1,0,0.00,1,'2025-07-31 08:24:01','2025-07-31 08:24:01'),(16,'Food and Beverage Costs','FB_COST','Expenses for purchasing ingredients, beverages, and related consumables.',NULL,1,0,0.00,1,'2025-07-31 08:24:01','2025-07-31 08:24:01'),(17,'Depreciation and Amortization','DEPRECIATION','Gradual expense recognition for tangible and intangible assets.',NULL,1,0,0.00,1,'2025-07-31 08:24:01','2025-07-31 08:24:01'),(18,'Miscellaneous','MISCELLANEOUS','Unclassified or irregular expenses that don\'t fit into other categories.',NULL,1,0,0.00,1,'2025-07-31 08:24:01','2025-07-31 08:24:01');

--
-- Table structure for table `AAX7019_expenses_line_items`
--

CREATE TABLE `AAX7019_expenses_line_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `expense_id` int(11) NOT NULL,
  `item_description` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `quantity` decimal(10,3) DEFAULT '1.000',
  `unit_price` decimal(15,2) NOT NULL,
  `line_total` decimal(15,2) NOT NULL,
  `tax_rate` decimal(5,2) DEFAULT '0.00',
  `tax_amount` decimal(15,2) DEFAULT '0.00',
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_expenses_line_items_expense` (`expense_id`),
  CONSTRAINT `AAX7019_expenses_line_items_ibfk_1` FOREIGN KEY (`expense_id`) REFERENCES `AAX7019_expenses_transactions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAX7019_expenses_line_items`
--


--
-- Table structure for table `AAX7019_expenses_payments`
--

CREATE TABLE `AAX7019_expenses_payments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `expense_id` int(11) NOT NULL,
  `payment_amount` decimal(15,2) NOT NULL,
  `payment_date` date NOT NULL,
  `payment_method_id` int(11) NOT NULL,
  `reference_number` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_by` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_payment_method_id` (`payment_method_id`),
  KEY `idx_expenses_payments_expense` (`expense_id`),
  CONSTRAINT `AAX7019_expenses_payments_ibfk_1` FOREIGN KEY (`expense_id`) REFERENCES `AAX7019_expenses_transactions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `AAX7019_expenses_payments_ibfk_2` FOREIGN KEY (`payment_method_id`) REFERENCES `AAX7019_payment_method` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAX7019_expenses_payments`
--


--
-- Table structure for table `AAX7019_expenses_recurring`
--

CREATE TABLE `AAX7019_expenses_recurring` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `template_expense_id` int(11) NOT NULL,
  `next_due_date` date NOT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  `last_generated_date` date DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `template_expense_id` (`template_expense_id`),
  CONSTRAINT `AAX7019_expenses_recurring_ibfk_1` FOREIGN KEY (`template_expense_id`) REFERENCES `AAX7019_expenses_transactions` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAX7019_expenses_recurring`
--


--
-- Table structure for table `AAX7019_expenses_transactions`
--

CREATE TABLE `AAX7019_expenses_transactions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `expense_number` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `category_id` int(11) NOT NULL,
  `vendor_id` int(11) DEFAULT NULL,
  `shift_id` int(11) DEFAULT NULL,
  `expense_date` datetime NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `tax_amount` decimal(15,2) DEFAULT '0.00',
  `total_amount` decimal(15,2) NOT NULL,
  `currency_code` varchar(3) COLLATE utf8mb4_unicode_ci DEFAULT 'PHP',
  `exchange_rate` decimal(10,4) DEFAULT '1.0000',
  `description` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `reference_number` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `payment_method_id` int(11) NOT NULL,
  `payment_status` enum('PENDING','PAID','PARTIALLY_PAID','OVERDUE','CANCELLED') COLLATE utf8mb4_unicode_ci DEFAULT 'PENDING',
  `approval_status` enum('DRAFT','PENDING_APPROVAL','APPROVED','REJECTED') COLLATE utf8mb4_unicode_ci DEFAULT 'DRAFT',
  `recurring_type` enum('NONE','DAILY','WEEKLY','MONTHLY','QUARTERLY','YEARLY') COLLATE utf8mb4_unicode_ci DEFAULT 'NONE',
  `recurring_interval` int(11) DEFAULT '1',
  `recurring_until` date DEFAULT NULL,
  `parent_expense_id` int(11) DEFAULT NULL,
  `receipt_attachment` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_by` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `approved_by` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `approved_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `expense_number` (`expense_number`),
  KEY `idx_shift_id` (`shift_id`),
  KEY `idx_payment_method_id` (`payment_method_id`),
  KEY `idx_expenses_transactions_date` (`expense_date`),
  KEY `idx_expenses_transactions_status` (`payment_status`,`approval_status`),
  KEY `idx_expenses_transactions_vendor` (`vendor_id`),
  KEY `idx_expenses_transactions_category` (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAX7019_expenses_transactions`
--

INSERT INTO `AAX7019_expenses_transactions` VALUES (1,'EXP202507310001',13,NULL,NULL,'2025-07-31 17:20:19',2.00,0.00,2.00,'PHP',1.0000,'Delivery of Goods','',1,'CANCELLED','REJECTED','NONE',1,NULL,NULL,'',' [VOIDED]','AfrpyFPcjvb4YMDvjJkfX6X0UF83',NULL,NULL,'2025-07-31 09:20:34','2025-08-01 00:24:35'),(2,'EXP202508010001',13,NULL,2,'2025-08-01 20:43:11',100.00,0.00,100.00,'PHP',1.0000,'Delivery of Goods','',1,'PAID','APPROVED','NONE',1,NULL,NULL,'','','AfrpyFPcjvb4YMDvjJkfX6X0UF83',NULL,NULL,'2025-08-01 12:43:20','2025-08-01 12:43:20'),(3,'EXP202508010002',13,NULL,2,'2025-08-01 20:52:56',100.00,0.00,100.00,'PHP',1.0000,'Purchases - Delivery of Goods','',1,'PAID','APPROVED','NONE',1,NULL,NULL,'','','AfrpyFPcjvb4YMDvjJkfX6X0UF83',NULL,NULL,'2025-08-01 12:53:09','2025-08-01 12:53:09'),(4,'EXP202508010003',1,NULL,2,'2025-08-01 21:00:32',250.00,0.00,250.00,'PHP',1.0000,'Supplies - Office Supplies','',1,'PAID','APPROVED','NONE',1,NULL,NULL,'','','AfrpyFPcjvb4YMDvjJkfX6X0UF83',NULL,NULL,'2025-08-01 13:00:36','2025-08-01 13:00:36');

--
-- Table structure for table `AAX7019_expenses_vendors`
--

CREATE TABLE `AAX7019_expenses_vendors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `vendor_code` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `vendor_name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `contact_person` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `phone` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `address` text COLLATE utf8mb4_unicode_ci,
  `tax_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `payment_terms` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `currency_code` varchar(3) COLLATE utf8mb4_unicode_ci DEFAULT 'PHP',
  `is_active` tinyint(1) DEFAULT '1',
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `vendor_code` (`vendor_code`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAX7019_expenses_vendors`
--

INSERT INTO `AAX7019_expenses_vendors` VALUES (1,'VENDOR001','General Supplier','Contact Person','contact@supplier.com','+1234567890','123 Supplier St, City','TAX123456','Net 30','PHP',1,NULL,'2025-07-31 08:24:01','2025-07-31 08:24:01'),(2,'VENDOR002','Office Supplies Co.','Office Manager','office@supplies.com','+1234567891','456 Office Ave, City','TAX123457','Net 15','PHP',1,NULL,'2025-07-31 08:24:01','2025-07-31 08:24:01'),(3,'VENDOR003','Utility Company','Billing Dept','billing@utility.com','+1234567892','789 Utility Blvd, City','TAX123458','Due on receipt','PHP',1,NULL,'2025-07-31 08:24:01','2025-07-31 08:24:01'),(4,'VENDOR004','Marketing Agency','Account Manager','accounts@marketing.com','+1234567893','321 Marketing Rd, City','TAX123459','Net 30','PHP',1,NULL,'2025-07-31 08:24:01','2025-07-31 08:24:01'),(5,'VENDOR005','Maintenance Services','Service Manager','service@maintenance.com','+1234567894','654 Service Ln, City','TAX123460','Net 15','PHP',1,NULL,'2025-07-31 08:24:01','2025-07-31 08:24:01');

--
-- Table structure for table `AAX7019_invoice_shares`
--

CREATE TABLE `AAX7019_invoice_shares` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `transaction_id` bigint(20) NOT NULL,
  `share_token` varchar(255) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `expires_at` timestamp NULL DEFAULT NULL,
  `view_count` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `share_token` (`share_token`),
  KEY `idx_token` (`share_token`),
  KEY `idx_transaction` (`transaction_id`),
  KEY `idx_expires` (`expires_at`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `AAX7019_invoice_shares`
--

INSERT INTO `AAX7019_invoice_shares` VALUES (1,94,'60d60b529e81a6f7aa53944b390d2f3448ec95e3d02d89024ad2f1f2d168e555','2025-08-05 21:00:42','2025-09-05 12:00:42',0),(2,94,'0921550fb3ce555f1723ed7de9d3f9d58fe4ced937103b798a279ed578d282ae','2025-08-05 21:10:15','2025-09-05 12:10:15',0),(3,94,'b12c0566ea8582135698f6d0acd0e66598e3deb15e2fc4ab7caf40e71a7f136b','2025-08-05 21:10:27','2025-09-05 12:10:27',0);

--
-- Table structure for table `AAX7019_payment_method`
--

CREATE TABLE `AAX7019_payment_method` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `method_code` varchar(50) NOT NULL,
  `method_name` varchar(100) NOT NULL,
  `account_number` varchar(100) DEFAULT NULL COMMENT 'Account number, card number, or other identifier',
  `description` text,
  `is_system` tinyint(1) NOT NULL DEFAULT '0' COMMENT '1 for system methods (CASH, CREDIT), 0 for user methods',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `display_order` int(11) DEFAULT '0',
  `requires_reference` tinyint(1) DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_method_code_account` (`method_code`,`account_number`),
  KEY `idx_method_code` (`method_code`),
  KEY `idx_is_system` (`is_system`),
  KEY `idx_is_active` (`is_active`),
  KEY `idx_display_order` (`display_order`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `AAX7019_payment_method`
--

INSERT INTO `AAX7019_payment_method` VALUES (1,'CASH','Cash',NULL,NULL,1,1,1,0,'2025-07-31 08:24:01','2025-07-31 08:24:01'),(2,'CREDIT','Credit',NULL,NULL,1,1,2,0,'2025-07-31 08:24:01','2025-07-31 08:24:01'),(3,'DIGITAL_WALLET','GCash','09776506236',NULL,0,1,0,0,'2025-07-31 08:24:58','2025-07-31 08:24:58');

--
-- Table structure for table `AAX7019_product_barcodes`
--

CREATE TABLE `AAX7019_product_barcodes` (
  `barcode_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `variant_id` int(11) DEFAULT NULL,
  `barcode` varchar(255) NOT NULL,
  `barcode_type` enum('UPC','EAN8','EAN13','CODE128','CODE39','QR','CUSTOM') NOT NULL DEFAULT 'EAN13',
  `is_primary` tinyint(1) DEFAULT '0',
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`barcode_id`),
  UNIQUE KEY `unique_barcode` (`barcode`),
  KEY `idx_product_barcode` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `AAX7019_product_barcodes`
--


--
-- Table structure for table `AAX7019_product_brands`
--

CREATE TABLE `AAX7019_product_brands` (
  `brand_id` int(11) NOT NULL AUTO_INCREMENT,
  `brand_name` varchar(100) NOT NULL,
  `brand_description` text,
  `brand_logo` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`brand_id`),
  UNIQUE KEY `unique_brand_name` (`brand_name`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `AAX7019_product_brands`
--

INSERT INTO `AAX7019_product_brands` VALUES (1,'No Brand','Default brand for products without specific brand',NULL,1,'2025-07-31 08:24:01','2025-07-31 08:24:01');

--
-- Table structure for table `AAX7019_product_categories`
--

CREATE TABLE `AAX7019_product_categories` (
  `category_id` int(11) NOT NULL AUTO_INCREMENT,
  `category_name` varchar(100) NOT NULL,
  `category_description` text,
  `parent_category_id` int(11) DEFAULT NULL,
  `category_color` varchar(7) DEFAULT '#2196F3',
  `category_icon` varchar(50) DEFAULT 'category',
  `sort_order` int(11) DEFAULT '0',
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`category_id`),
  KEY `idx_parent_category` (`parent_category_id`),
  KEY `idx_active_sort` (`is_active`,`sort_order`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `AAX7019_product_categories`
--

INSERT INTO `AAX7019_product_categories` VALUES (1,'General','Default category for products',NULL,'#2196F3','category',0,1,'2025-07-31 08:24:01','2025-07-31 08:24:01');

--
-- Table structure for table `AAX7019_product_combos`
--

CREATE TABLE `AAX7019_product_combos` (
  `combo_id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_product_id` int(11) NOT NULL,
  `component_product_id` int(11) NOT NULL,
  `component_variant_id` int(11) DEFAULT NULL,
  `quantity_required` decimal(10,3) NOT NULL,
  `is_optional` tinyint(1) DEFAULT '0',
  `option_group` varchar(50) DEFAULT NULL,
  `max_selections` int(11) DEFAULT '1',
  `sort_order` int(11) DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`combo_id`),
  KEY `idx_parent_product` (`parent_product_id`),
  KEY `idx_component_product` (`component_product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `AAX7019_product_combos`
--


--
-- Table structure for table `AAX7019_product_extras`
--

CREATE TABLE `AAX7019_product_extras` (
  `extra_id` int(11) NOT NULL AUTO_INCREMENT,
  `extra_name` varchar(255) NOT NULL,
  `extra_description` text,
  `extra_type` enum('addon','service','charge','discount') NOT NULL DEFAULT 'addon',
  `extra_price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `is_percentage` tinyint(1) DEFAULT '0',
  `applicable_categories` json DEFAULT NULL,
  `applicable_products` json DEFAULT NULL,
  `min_quantity` decimal(10,3) DEFAULT '1.000',
  `max_quantity` decimal(10,3) DEFAULT NULL,
  `is_required` tinyint(1) DEFAULT '0',
  `requires_input` tinyint(1) DEFAULT '0',
  `is_active` tinyint(1) DEFAULT '1',
  `sort_order` int(11) DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`extra_id`),
  KEY `idx_extra_type` (`extra_type`),
  KEY `idx_active_sort` (`is_active`,`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `AAX7019_product_extras`
--


--
-- Table structure for table `AAX7019_product_serial_numbers`
--

CREATE TABLE `AAX7019_product_serial_numbers` (
  `serial_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `variant_id` int(11) DEFAULT NULL,
  `serial_number` varchar(100) NOT NULL,
  `batch_lot_number` varchar(50) DEFAULT NULL,
  `manufacturing_date` date DEFAULT NULL,
  `expiry_date` date DEFAULT NULL,
  `purchase_date` date DEFAULT NULL,
  `supplier_id` int(11) DEFAULT NULL,
  `cost_price` decimal(10,2) DEFAULT NULL,
  `status` enum('available','sold','reserved','damaged','returned','transferred') DEFAULT 'available',
  `location_id` int(11) DEFAULT NULL,
  `notes` text,
  `sold_date` datetime DEFAULT NULL,
  `sold_to_customer` varchar(255) DEFAULT NULL,
  `warranty_expires` date DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_by` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`serial_id`),
  UNIQUE KEY `idx_product_serial_unique` (`product_id`,`serial_number`),
  KEY `idx_product_serial` (`product_id`),
  KEY `idx_status` (`status`),
  KEY `idx_expiry_date` (`expiry_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `AAX7019_product_serial_numbers`
--


--
-- Table structure for table `AAX7019_product_units`
--

CREATE TABLE `AAX7019_product_units` (
  `unit_id` int(11) NOT NULL AUTO_INCREMENT,
  `unit_name` varchar(50) NOT NULL,
  `unit_symbol` varchar(10) NOT NULL,
  `unit_type` enum('weight','volume','length','quantity','time','area') NOT NULL,
  `base_unit_conversion` decimal(15,6) DEFAULT '1.000000',
  `is_decimal_allowed` tinyint(1) DEFAULT '1',
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`unit_id`),
  UNIQUE KEY `unique_unit_symbol` (`unit_symbol`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `AAX7019_product_units`
--

INSERT INTO `AAX7019_product_units` VALUES (1,'Piece','pcs','quantity',1.000000,1,1,'2025-07-31 08:24:01'),(2,'Kilogram','kg','weight',1.000000,1,1,'2025-07-31 08:24:01'),(3,'Gram','g','weight',1.000000,1,1,'2025-07-31 08:24:01'),(4,'Liter','L','volume',1.000000,1,1,'2025-07-31 08:24:01'),(5,'Milliliter','mL','volume',1.000000,1,1,'2025-07-31 08:24:01'),(6,'Meter','m','length',1.000000,1,1,'2025-07-31 08:24:01'),(7,'Centimeter','cm','length',1.000000,1,1,'2025-07-31 08:24:01'),(8,'Box','box','quantity',1.000000,1,1,'2025-07-31 08:24:01'),(9,'Pack','pack','quantity',1.000000,1,1,'2025-07-31 08:24:01'),(10,'Bottle','bottle','quantity',1.000000,1,1,'2025-07-31 08:24:01');

--
-- Table structure for table `AAX7019_product_variants`
--

CREATE TABLE `AAX7019_product_variants` (
  `variant_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `variant_name` varchar(100) NOT NULL,
  `variant_type` varchar(50) NOT NULL,
  `variant_code` varchar(50) DEFAULT NULL,
  `variant_description` text,
  `price_adjustment` decimal(10,2) DEFAULT '0.00',
  `cost_adjustment` decimal(10,2) DEFAULT '0.00',
  `stock_quantity` decimal(15,3) DEFAULT '0.000',
  `variant_image` varchar(500) DEFAULT NULL,
  `sort_order` int(11) DEFAULT '0',
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`variant_id`),
  KEY `idx_product_variant` (`product_id`,`variant_type`),
  KEY `idx_variant_code` (`variant_code`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `AAX7019_product_variants`
--

INSERT INTO `AAX7019_product_variants` VALUES (1,2,'Regular','Flavor',NULL,NULL,0.00,0.00,2.000,NULL,0,1,'2025-08-04 05:14:06','2025-08-05 21:00:14'),(2,2,'Spicy','Flavor',NULL,NULL,0.00,0.00,1.000,NULL,0,1,'2025-08-04 05:14:06','2025-08-04 07:32:54');

--
-- Table structure for table `AAX7019_products`
--

CREATE TABLE `AAX7019_products` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_code` varchar(50) DEFAULT NULL,
  `product_name` varchar(255) NOT NULL,
  `product_description` text,
  `short_description` varchar(500) DEFAULT NULL,
  `category_id` int(11) NOT NULL,
  `brand_id` int(11) DEFAULT NULL,
  `unit_id` int(11) NOT NULL,
  `sell_type` enum('unit','weight','combo','service') NOT NULL DEFAULT 'unit',
  `sell_price` decimal(12,2) NOT NULL DEFAULT '0.00',
  `cost_price` decimal(12,2) DEFAULT '0.00',
  `wholesale_price` decimal(12,2) DEFAULT '0.00',
  `tax_rate` decimal(5,2) DEFAULT '0.00',
  `discount_percentage` decimal(5,2) DEFAULT '0.00',
  `track_inventory` tinyint(1) DEFAULT '1',
  `current_stock` decimal(15,3) DEFAULT '0.000',
  `reserved_stock` decimal(15,3) DEFAULT '0.000',
  `reorder_level` decimal(15,3) DEFAULT '0.000',
  `reorder_quantity` decimal(15,3) DEFAULT '0.000',
  `max_stock_level` decimal(15,3) DEFAULT NULL,
  `image_path` varchar(500) DEFAULT NULL,
  `additional_images` json DEFAULT NULL,
  `product_specifications` json DEFAULT NULL,
  `is_favorite` tinyint(1) DEFAULT '0',
  `is_featured` tinyint(1) DEFAULT '0',
  `is_active` tinyint(1) DEFAULT '1',
  `allow_decimal_quantity` tinyint(1) DEFAULT '0',
  `has_variants` tinyint(1) DEFAULT '0',
  `has_combo` tinyint(1) DEFAULT '0',
  `has_serial_numbers` tinyint(1) DEFAULT '0',
  `has_expiry_date` tinyint(1) DEFAULT '0',
  `view_count` int(11) DEFAULT '0',
  `sale_count` int(11) DEFAULT '0',
  `rating_average` decimal(3,2) DEFAULT '0.00',
  `rating_count` int(11) DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_by` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`product_id`),
  UNIQUE KEY `unique_product_code` (`product_code`),
  KEY `idx_category` (`category_id`),
  KEY `idx_brand` (`brand_id`),
  KEY `idx_unit` (`unit_id`),
  KEY `idx_sell_type` (`sell_type`),
  KEY `idx_active_featured` (`is_active`,`is_featured`),
  KEY `idx_stock_alert` (`track_inventory`,`current_stock`,`reorder_level`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `AAX7019_products`
--

INSERT INTO `AAX7019_products` VALUES (1,NULL,'Teat',NULL,NULL,1,NULL,1,'unit',4.00,0.00,0.00,0.00,0.00,0,0.000,0.000,0.000,0.000,NULL,NULL,NULL,NULL,0,0,1,0,0,0,0,0,0,0,0.00,0,'2025-07-31 08:31:03','2025-07-31 08:31:03',NULL),(2,NULL,'Premium',NULL,NULL,1,NULL,1,'unit',1.00,0.00,0.00,0.00,0.00,1,-1.000,0.000,0.000,0.000,NULL,NULL,NULL,NULL,1,0,1,0,1,0,0,0,0,5,0.00,0,'2025-08-04 05:14:05','2025-08-04 11:11:43',NULL);

--
-- Table structure for table `AAX7019_quick_expense_types`
--

CREATE TABLE `AAX7019_quick_expense_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `icon` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT 'lightbulb',
  `category_id` int(11) NOT NULL,
  `vendor_id` int(11) DEFAULT NULL,
  `payment_method_id` int(11) NOT NULL,
  `payment_status` enum('PENDING','PAID','PARTIALLY_PAID','OVERDUE') COLLATE utf8mb4_unicode_ci DEFAULT 'PAID',
  `approval_status` enum('DRAFT','PENDING_APPROVAL','APPROVED','REJECTED') COLLATE utf8mb4_unicode_ci DEFAULT 'APPROVED',
  `common_amounts` json DEFAULT NULL,
  `usage_count` int(11) DEFAULT '0',
  `last_used_at` timestamp NULL DEFAULT NULL,
  `display_order` int(11) DEFAULT '0',
  `is_default` tinyint(1) DEFAULT '0',
  `is_active` tinyint(1) DEFAULT '1',
  `created_by` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_category` (`category_id`),
  KEY `idx_vendor` (`vendor_id`),
  KEY `idx_display_order` (`display_order`),
  KEY `idx_active` (`is_active`),
  KEY `idx_usage` (`usage_count`,`last_used_at`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAX7019_quick_expense_types`
--

INSERT INTO `AAX7019_quick_expense_types` VALUES (1,'Purchases','Delivery of Goods','?',13,NULL,1,'PAID','APPROVED','[100, 250, 500, 1000]',0,NULL,1,1,1,NULL,'2025-07-31 08:24:01','2025-07-31 08:24:01'),(2,'Supplies','Office Supplies','?',1,NULL,1,'PAID','APPROVED','[100, 250, 500, 1000]',0,NULL,2,1,1,NULL,'2025-07-31 08:24:01','2025-07-31 08:24:01'),(3,'Power Bills','Power Bills','?',14,NULL,1,'PAID','APPROVED','[1000, 2000, 3000, 5000]',0,NULL,3,1,1,NULL,'2025-07-31 08:24:01','2025-07-31 08:24:01'),(4,'Water Bills','Water Bills','?',14,NULL,1,'PAID','APPROVED','[1000, 2000, 3000, 5000]',0,NULL,4,1,1,NULL,'2025-07-31 08:24:01','2025-07-31 08:24:01'),(5,'Salaries','Salaries','?',15,NULL,1,'PAID','APPROVED','[500, 1000, 2000, 5000]',0,NULL,5,1,1,NULL,'2025-07-31 08:24:01','2025-07-31 08:24:01'),(6,'Internet Bills','Phone/Internet Bills','?',14,NULL,1,'PAID','APPROVED','[500, 1000, 1500, 2000]',0,NULL,6,1,1,NULL,'2025-07-31 08:24:01','2025-07-31 08:24:01');

--
-- Table structure for table `AAX7019_sales_cash_sessions`
--

CREATE TABLE `AAX7019_sales_cash_sessions` (
  `session_id` int(11) NOT NULL AUTO_INCREMENT,
  `cashier_id` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `cashier_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `opening_cash` decimal(15,2) NOT NULL DEFAULT '0.00',
  `closing_cash` decimal(15,2) DEFAULT NULL,
  `expected_cash` decimal(15,2) DEFAULT NULL,
  `cash_difference` decimal(15,2) DEFAULT NULL,
  `session_start` datetime NOT NULL,
  `session_end` datetime DEFAULT NULL,
  `session_status` enum('open','closed') COLLATE utf8mb4_unicode_ci DEFAULT 'open',
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`session_id`),
  KEY `idx_cashier_id` (`cashier_id`),
  KEY `idx_session_start` (`session_start`),
  KEY `idx_session_status` (`session_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAX7019_sales_cash_sessions`
--


--
-- Table structure for table `AAX7019_sales_daily_summary`
--

CREATE TABLE `AAX7019_sales_daily_summary` (
  `summary_id` int(11) NOT NULL AUTO_INCREMENT,
  `summary_date` date NOT NULL,
  `total_transactions` int(11) DEFAULT '0',
  `voided_transactions` int(11) DEFAULT '0',
  `returned_transactions` int(11) DEFAULT '0',
  `gross_sales` decimal(15,2) DEFAULT '0.00',
  `total_discounts` decimal(15,2) DEFAULT '0.00',
  `total_tax` decimal(15,2) DEFAULT '0.00',
  `net_sales` decimal(15,2) DEFAULT '0.00',
  `total_returns` decimal(15,2) DEFAULT '0.00',
  `total_voids` decimal(15,2) DEFAULT '0.00',
  `cash_sales` decimal(15,2) DEFAULT '0.00',
  `card_sales` decimal(15,2) DEFAULT '0.00',
  `digital_wallet_sales` decimal(15,2) DEFAULT '0.00',
  `credit_sales` decimal(15,2) DEFAULT '0.00',
  `other_payments` decimal(15,2) DEFAULT '0.00',
  `total_items_sold` int(11) DEFAULT '0',
  `total_weight_sold` decimal(10,3) DEFAULT '0.000',
  `hourly_sales` json DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`summary_id`),
  UNIQUE KEY `summary_date` (`summary_date`),
  KEY `idx_summary_date` (`summary_date`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAX7019_sales_daily_summary`
--

INSERT INTO `AAX7019_sales_daily_summary` VALUES (1,'2025-07-31',6,0,0,60.00,0.00,0.00,60.00,0.00,0.00,16.00,0.00,20.00,24.00,0.00,15,0.000,NULL,'2025-07-31 09:03:43','2025-07-31 13:32:25'),(2,'2025-08-01',3,1,0,16.00,0.00,0.00,12.00,0.00,4.00,16.00,0.00,0.00,0.00,0.00,4,0.000,NULL,'2025-08-01 00:25:01','2025-08-01 10:01:32'),(3,'2025-08-04',21,9,0,615.00,0.00,0.00,579.00,0.00,36.00,567.00,0.00,0.00,48.00,0.00,33,0.000,NULL,'2025-08-04 04:51:56','2025-08-04 14:34:40'),(4,'2025-08-06',1,0,0,13.00,0.00,0.00,13.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,4,0.000,NULL,'2025-08-05 21:00:14','2025-08-05 21:00:14');

--
-- Table structure for table `AAX7019_sales_items`
--

CREATE TABLE `AAX7019_sales_items` (
  `item_id` int(11) NOT NULL AUTO_INCREMENT,
  `transaction_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `variant_id` int(11) DEFAULT NULL,
  `product_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `product_code` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `category_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sell_type` enum('unit','weight','combo','service') COLLATE utf8mb4_unicode_ci NOT NULL,
  `unit_symbol` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `quantity` decimal(10,3) NOT NULL,
  `weight` decimal(10,3) DEFAULT NULL,
  `unit_price` decimal(15,2) NOT NULL,
  `line_subtotal` decimal(15,2) NOT NULL,
  `line_discount_amount` decimal(15,2) DEFAULT '0.00',
  `line_total` decimal(15,2) NOT NULL,
  `is_returned` tinyint(1) DEFAULT '0',
  `returned_quantity` decimal(10,3) DEFAULT '0.000',
  `returned_weight` decimal(10,3) DEFAULT '0.000',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`item_id`),
  KEY `idx_product_id` (`product_id`),
  KEY `idx_transaction_id` (`transaction_id`),
  KEY `idx_variant_id` (`variant_id`)
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAX7019_sales_items`
--

INSERT INTO `AAX7019_sales_items` VALUES (1,1,1,NULL,'Teat',NULL,NULL,'unit',NULL,1.000,NULL,4.00,4.00,0.00,4.00,0,0.000,0.000,'2025-07-31 09:03:43'),(2,2,1,NULL,'Teat',NULL,NULL,'unit',NULL,1.000,NULL,4.00,4.00,0.00,4.00,0,0.000,0.000,'2025-07-31 09:03:43'),(3,3,1,NULL,'Teat',NULL,NULL,'unit',NULL,1.000,NULL,4.00,4.00,0.00,4.00,0,0.000,0.000,'2025-07-31 09:13:41'),(4,4,1,NULL,'Teat',NULL,NULL,'unit',NULL,2.000,NULL,4.00,8.00,0.00,8.00,0,0.000,0.000,'2025-07-31 12:32:22'),(5,5,1,NULL,'Teat',NULL,NULL,'unit',NULL,4.000,NULL,4.00,16.00,0.00,16.00,0,0.000,0.000,'2025-07-31 13:31:03'),(6,6,1,NULL,'Teat',NULL,NULL,'unit',NULL,6.000,NULL,4.00,24.00,0.00,24.00,0,0.000,0.000,'2025-07-31 13:32:25'),(7,7,1,NULL,'Teat',NULL,NULL,'unit',NULL,1.000,NULL,4.00,4.00,0.00,4.00,1,1.000,0.000,'2025-08-01 00:25:01'),(8,8,1,NULL,'Teat',NULL,NULL,'unit',NULL,2.000,NULL,4.00,8.00,0.00,8.00,0,0.000,0.000,'2025-08-01 04:18:48'),(9,9,1,NULL,'Teat',NULL,NULL,'unit',NULL,1.000,NULL,4.00,4.00,0.00,4.00,0,0.000,0.000,'2025-08-01 10:01:32'),(10,10,1,NULL,'Teat',NULL,NULL,'unit',NULL,2.000,NULL,4.00,8.00,0.00,8.00,1,2.000,0.000,'2025-08-04 04:51:56'),(11,11,1,NULL,'Teat',NULL,NULL,'unit',NULL,3.000,NULL,4.00,12.00,0.00,12.00,1,3.000,0.000,'2025-08-04 04:53:46'),(12,12,1,NULL,'Teat',NULL,NULL,'unit',NULL,1.000,NULL,4.00,4.00,0.00,4.00,1,1.000,0.000,'2025-08-04 04:59:34'),(13,13,1,NULL,'Teat',NULL,NULL,'unit',NULL,1.000,NULL,4.00,4.00,0.00,4.00,1,1.000,0.000,'2025-08-04 05:03:50'),(14,14,1,NULL,'Teat',NULL,NULL,'unit',NULL,1.000,NULL,4.00,4.00,0.00,4.00,1,1.000,0.000,'2025-08-04 05:05:05'),(15,15,200002,NULL,'Premium (Spicy)','',NULL,'unit',NULL,1.000,NULL,40.00,40.00,0.00,40.00,0,0.000,0.000,'2025-08-04 05:14:28'),(16,16,200002,NULL,'Premium (Spicy)','',NULL,'unit',NULL,1.000,NULL,40.00,40.00,0.00,40.00,0,0.000,0.000,'2025-08-04 05:15:10'),(17,17,200001,NULL,'Premium (Regular)',NULL,NULL,'unit',NULL,1.000,NULL,40.00,40.00,0.00,40.00,0,0.000,0.000,'2025-08-04 05:23:54'),(18,18,200002,NULL,'Premium (Spicy)',NULL,NULL,'unit',NULL,1.000,NULL,40.00,40.00,0.00,40.00,0,0.000,0.000,'2025-08-04 05:24:18'),(19,19,2,NULL,'Premium (Spicy)',NULL,NULL,'unit',NULL,1.000,NULL,40.00,40.00,0.00,40.00,0,0.000,0.000,'2025-08-04 05:29:42'),(20,20,2,NULL,'Premium (Spicy)',NULL,NULL,'unit',NULL,1.000,NULL,40.00,40.00,0.00,40.00,0,0.000,0.000,'2025-08-04 05:29:51'),(21,21,2,NULL,'Premium (Spicy)',NULL,NULL,'unit',NULL,8.000,NULL,40.00,320.00,0.00,320.00,0,0.000,0.000,'2025-08-04 05:30:12'),(22,22,2,NULL,'Premium (Regular)','',NULL,'unit',NULL,1.000,NULL,1.00,1.00,0.00,1.00,1,1.000,0.000,'2025-08-04 07:33:11'),(23,86,2,NULL,'Premium (Regular)',NULL,NULL,'unit',NULL,1.000,NULL,1.00,1.00,0.00,1.00,0,0.000,0.000,'2025-08-04 11:09:24'),(24,87,2,NULL,'Premium (Regular)',NULL,NULL,'unit',NULL,1.000,NULL,1.00,1.00,0.00,1.00,0,0.000,0.000,'2025-08-04 11:09:25'),(25,88,2,1,'Premium (Regular)',NULL,NULL,'unit',NULL,1.000,NULL,1.00,1.00,0.00,1.00,1,1.000,0.000,'2025-08-04 11:09:31'),(26,89,2,NULL,'Premium (Regular)',NULL,NULL,'unit',NULL,1.000,NULL,1.00,1.00,0.00,1.00,1,1.000,0.000,'2025-08-04 11:11:01'),(27,90,2,1,'Premium (Regular)',NULL,NULL,'unit',NULL,1.000,NULL,1.00,1.00,0.00,1.00,1,1.000,0.000,'2025-08-04 11:12:52'),(28,91,1,NULL,'Teat',NULL,NULL,'unit',NULL,1.000,NULL,4.00,4.00,0.00,4.00,0,0.000,0.000,'2025-08-04 14:05:12'),(29,92,1,NULL,'Teat',NULL,NULL,'unit',NULL,2.000,NULL,4.00,8.00,0.00,8.00,0,0.000,0.000,'2025-08-04 14:06:01'),(30,93,2,1,'Premium (Regular)',NULL,NULL,'unit',NULL,1.000,NULL,1.00,1.00,0.00,1.00,0,0.000,0.000,'2025-08-04 14:34:40'),(31,93,1,NULL,'Teat',NULL,NULL,'unit',NULL,1.000,NULL,4.00,4.00,0.00,4.00,0,0.000,0.000,'2025-08-04 14:34:40'),(32,94,1,NULL,'Teat',NULL,NULL,'unit',NULL,3.000,NULL,4.00,12.00,0.00,12.00,0,0.000,0.000,'2025-08-05 21:00:14'),(33,94,2,1,'Premium (Regular)',NULL,NULL,'unit',NULL,1.000,NULL,1.00,1.00,0.00,1.00,0,0.000,0.000,'2025-08-05 21:00:14');

--
-- Table structure for table `AAX7019_sales_payments`
--

CREATE TABLE `AAX7019_sales_payments` (
  `payment_id` int(11) NOT NULL AUTO_INCREMENT,
  `transaction_id` int(11) NOT NULL,
  `payment_method_id` int(11) NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `payment_date` datetime NOT NULL,
  `reference_number` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `card_last_four` varchar(4) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `approval_code` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`payment_id`),
  KEY `idx_transaction_id` (`transaction_id`),
  KEY `idx_payment_method_id` (`payment_method_id`),
  KEY `idx_payment_date` (`payment_date`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAX7019_sales_payments`
--

INSERT INTO `AAX7019_sales_payments` VALUES (1,1,3,4.00,'2025-07-31 17:03:43',NULL,NULL,NULL,'2025-07-31 09:03:43'),(2,2,3,4.00,'2025-07-31 17:03:43',NULL,NULL,NULL,'2025-07-31 09:03:43'),(3,3,3,4.00,'2025-07-31 17:13:41',NULL,NULL,NULL,'2025-07-31 09:13:41'),(4,4,3,8.00,'2025-07-31 20:32:22',NULL,NULL,NULL,'2025-07-31 12:32:22'),(5,5,1,16.00,'2025-07-31 21:31:03',NULL,NULL,NULL,'2025-07-31 13:31:03'),(6,7,1,4.00,'2025-08-01 08:25:01',NULL,NULL,NULL,'2025-08-01 00:25:01'),(7,8,1,8.00,'2025-08-01 12:18:48',NULL,NULL,NULL,'2025-08-01 04:18:48'),(8,9,1,4.00,'2025-08-01 18:01:32',NULL,NULL,NULL,'2025-08-01 10:01:32'),(9,15,1,40.00,'2025-08-04 13:14:28',NULL,NULL,NULL,'2025-08-04 05:14:28'),(10,16,1,40.00,'2025-08-04 13:15:10',NULL,NULL,NULL,'2025-08-04 05:15:10'),(11,17,1,40.00,'2025-08-04 13:23:54',NULL,NULL,NULL,'2025-08-04 05:23:54'),(12,18,1,40.00,'2025-08-04 13:24:18',NULL,NULL,NULL,'2025-08-04 05:24:18'),(13,19,1,40.00,'2025-08-04 13:29:42',NULL,NULL,NULL,'2025-08-04 05:29:42'),(14,20,1,40.00,'2025-08-04 13:29:51',NULL,NULL,NULL,'2025-08-04 05:29:51'),(15,21,1,320.00,'2025-08-04 13:30:12',NULL,NULL,NULL,'2025-08-04 05:30:12'),(16,86,1,1.00,'2025-08-04 19:09:24',NULL,NULL,NULL,'2025-08-04 11:09:24'),(17,87,1,1.00,'2025-08-04 19:09:25',NULL,NULL,NULL,'2025-08-04 11:09:25'),(18,88,1,1.00,'2025-08-04 19:09:31',NULL,NULL,NULL,'2025-08-04 11:09:31'),(19,89,2,1.00,'2025-08-04 19:11:01',NULL,NULL,NULL,'2025-08-04 11:11:01'),(20,91,1,4.00,'2025-08-04 22:05:12',NULL,NULL,NULL,'2025-08-04 14:05:12'),(21,93,2,5.00,'2025-08-04 22:34:40',NULL,NULL,NULL,'2025-08-04 14:34:40'),(22,6,2,24.00,'2025-07-31 21:32:25','TXN202507310006',NULL,NULL,'2025-08-04 14:39:27'),(23,92,2,8.00,'2025-08-04 22:06:01','TXN202508040020',NULL,NULL,'2025-08-04 14:39:27');

--
-- Table structure for table `AAX7019_sales_receipt_tokens`
--

CREATE TABLE `AAX7019_sales_receipt_tokens` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `transaction_id` int(11) NOT NULL,
  `token_hash` varchar(64) NOT NULL,
  `expires_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `used_at` timestamp NULL DEFAULT NULL,
  `is_valid` tinyint(1) DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `idx_transaction_id` (`transaction_id`),
  KEY `idx_token_hash` (`token_hash`),
  KEY `idx_expires_at` (`expires_at`),
  KEY `idx_is_valid` (`is_valid`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `AAX7019_sales_receipt_tokens`
--

INSERT INTO `AAX7019_sales_receipt_tokens` VALUES (11,93,'0402ee846a111fd94079076e566c2a0260f52b546ff6ab9a81cc997e46487ede','2025-08-13 17:51:27','2025-08-06 02:51:27',NULL,1),(10,93,'5b138d7e74865e5fc3c22b5cb9956d0d4ddc299c7cc59ec428ce7fb4377fa5ad','2025-08-13 17:50:56','2025-08-06 02:50:56',NULL,1),(9,93,'9a728b865bc763e8c4ce0c077121ea3248a3aeead03e3492795434772e5ec4f7','2025-08-13 17:40:49','2025-08-06 02:40:49',NULL,1),(12,93,'c5dda87b6becb2958f058475f29fcb4a7dcaf44d5395c405ffa77fb38d22a24d','2025-08-13 17:51:43','2025-08-06 02:51:43',NULL,1);

--
-- Table structure for table `AAX7019_sales_return_items`
--

CREATE TABLE `AAX7019_sales_return_items` (
  `return_item_id` int(11) NOT NULL AUTO_INCREMENT,
  `return_id` int(11) NOT NULL,
  `original_item_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `product_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `quantity_returned` decimal(10,3) NOT NULL,
  `weight_returned` decimal(10,3) DEFAULT NULL,
  `unit_price` decimal(15,2) NOT NULL,
  `return_amount` decimal(15,2) NOT NULL,
  `restocked` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`return_item_id`),
  KEY `idx_return_id` (`return_id`),
  KEY `idx_product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAX7019_sales_return_items`
--


--
-- Table structure for table `AAX7019_sales_returns`
--

CREATE TABLE `AAX7019_sales_returns` (
  `return_id` int(11) NOT NULL AUTO_INCREMENT,
  `return_number` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `original_transaction_id` int(11) NOT NULL,
  `return_type` enum('full_void','partial_return','exchange','refund') COLLATE utf8mb4_unicode_ci NOT NULL,
  `total_return_amount` decimal(15,2) NOT NULL,
  `refund_method` enum('cash','original_payment','store_credit') COLLATE utf8mb4_unicode_ci DEFAULT 'cash',
  `reason` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `processed_by` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `processor_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `return_date` datetime NOT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`return_id`),
  UNIQUE KEY `return_number` (`return_number`),
  KEY `idx_return_number` (`return_number`),
  KEY `idx_original_transaction` (`original_transaction_id`),
  KEY `idx_return_date` (`return_date`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAX7019_sales_returns`
--

INSERT INTO `AAX7019_sales_returns` VALUES (1,'RTN202508010001',7,'full_void',4.00,'cash','','AfrpyFPcjvb4YMDvjJkfX6X0UF83','Tbl Seven','2025-08-01 08:25:16',NULL,'2025-08-01 00:25:16'),(2,'RTN202508040001',10,'full_void',8.00,'cash','','AfrpyFPcjvb4YMDvjJkfX6X0UF83','Tbl Seven','2025-08-04 12:52:30',NULL,'2025-08-04 04:52:30'),(3,'RTN202508040002',11,'full_void',12.00,'cash','','AfrpyFPcjvb4YMDvjJkfX6X0UF83','Tbl Seven','2025-08-04 12:54:33',NULL,'2025-08-04 04:54:33'),(4,'RTN202508040003',12,'full_void',4.00,'cash','','AfrpyFPcjvb4YMDvjJkfX6X0UF83','Tbl Seven','2025-08-04 13:00:05',NULL,'2025-08-04 05:00:05'),(18,'RTN202508040004',14,'full_void',4.00,'cash','','AfrpyFPcjvb4YMDvjJkfX6X0UF83','Tbl Seven','2025-08-04 13:07:15',NULL,'2025-08-04 05:07:15'),(19,'RTN202508040005',13,'full_void',4.00,'cash','','AfrpyFPcjvb4YMDvjJkfX6X0UF83','Tbl Seven','2025-08-04 13:07:48',NULL,'2025-08-04 05:07:48'),(20,'RTN202508040006',22,'full_void',1.00,'cash','','AfrpyFPcjvb4YMDvjJkfX6X0UF83','Tbl Seven','2025-08-04 15:33:36',NULL,'2025-08-04 07:33:36'),(21,'RTN202508040007',88,'full_void',1.00,'cash','','AfrpyFPcjvb4YMDvjJkfX6X0UF83','Tbl Seven','2025-08-04 19:09:45',NULL,'2025-08-04 11:09:45'),(22,'RTN202508040008',89,'full_void',1.00,'cash','','AfrpyFPcjvb4YMDvjJkfX6X0UF83','Tbl Seven','2025-08-04 19:11:43',NULL,'2025-08-04 11:11:43'),(23,'RTN202508040009',90,'full_void',1.00,'cash','','AfrpyFPcjvb4YMDvjJkfX6X0UF83','Tbl Seven','2025-08-04 19:13:17',NULL,'2025-08-04 11:13:17');

--
-- Table structure for table `AAX7019_sales_sequences`
--

CREATE TABLE `AAX7019_sales_sequences` (
  `sequence_type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `current_number` int(11) NOT NULL DEFAULT '0',
  `prefix` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT '',
  `suffix` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT '',
  `date_format` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT '',
  `reset_daily` tinyint(1) DEFAULT '0',
  `last_reset_date` date DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`sequence_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAX7019_sales_sequences`
--

INSERT INTO `AAX7019_sales_sequences` VALUES ('return',9,'RTN','','Ymd',1,'2025-08-04','2025-08-04 11:13:17'),('transaction',1,'TXN','','Ymd',1,'2025-08-06','2025-08-05 21:00:14');

--
-- Table structure for table `AAX7019_sales_shared_receipts`
--

CREATE TABLE `AAX7019_sales_shared_receipts` (
  `shared_receipt_id` int(11) NOT NULL AUTO_INCREMENT,
  `transaction_id` int(11) NOT NULL,
  `transaction_number` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `shared_to` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Email, phone number, or platform identifier',
  `share_type` enum('email','sms','whatsapp','social','other') COLLATE utf8mb4_unicode_ci NOT NULL,
  `share_platform` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Specific platform name (WhatsApp, Facebook, etc.)',
  `share_status` enum('pending','sent','delivered','failed') COLLATE utf8mb4_unicode_ci DEFAULT 'pending',
  `share_content` text COLLATE utf8mb4_unicode_ci COMMENT 'Content of the shared message',
  `error_message` text COLLATE utf8mb4_unicode_ci COMMENT 'Error details if sharing failed',
  `shared_by` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Staff member who initiated the share',
  `shared_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `delivered_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`shared_receipt_id`),
  KEY `idx_transaction_id` (`transaction_id`),
  KEY `idx_transaction_number` (`transaction_number`),
  KEY `idx_share_type` (`share_type`),
  KEY `idx_share_status` (`share_status`),
  KEY `idx_shared_at` (`shared_at`),
  CONSTRAINT `AAX7019_sales_shared_receipts_ibfk_1` FOREIGN KEY (`transaction_id`) REFERENCES `AAX7019_sales_transactions` (`transaction_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Tracks all receipt sharing activities for audit and delivery status';

--
-- Dumping data for table `AAX7019_sales_shared_receipts`
--


--
-- Table structure for table `AAX7019_sales_transactions`
--

CREATE TABLE `AAX7019_sales_transactions` (
  `transaction_id` int(11) NOT NULL AUTO_INCREMENT,
  `transaction_number` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `customer_id` int(11) DEFAULT NULL,
  `cashier_id` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `cashier_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `shift_id` bigint(20) DEFAULT NULL,
  `subtotal` decimal(15,2) NOT NULL DEFAULT '0.00',
  `tax_rate` decimal(5,2) NOT NULL DEFAULT '0.00',
  `tax_amount` decimal(15,2) NOT NULL DEFAULT '0.00',
  `discount_type` enum('none','percentage','fixed') COLLATE utf8mb4_unicode_ci DEFAULT 'none',
  `discount_value` decimal(15,2) DEFAULT '0.00',
  `discount_amount` decimal(15,2) NOT NULL DEFAULT '0.00',
  `total_amount` decimal(15,2) NOT NULL,
  `amount_paid` decimal(15,2) NOT NULL DEFAULT '0.00',
  `change_amount` decimal(15,2) NOT NULL DEFAULT '0.00',
  `payment_status` enum('pending','paid','partially_paid','voided','returned','credit') COLLATE utf8mb4_unicode_ci DEFAULT 'pending',
  `transaction_type` enum('sale','return','void') COLLATE utf8mb4_unicode_ci DEFAULT 'sale',
  `transaction_date` datetime NOT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `receipt_printed` tinyint(1) DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`transaction_id`),
  UNIQUE KEY `transaction_number` (`transaction_number`),
  KEY `idx_transaction_number` (`transaction_number`),
  KEY `idx_transaction_date` (`transaction_date`),
  KEY `idx_cashier` (`cashier_id`),
  KEY `idx_payment_status` (`payment_status`),
  KEY `idx_shift` (`shift_id`)
) ENGINE=InnoDB AUTO_INCREMENT=95 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAX7019_sales_transactions`
--

INSERT INTO `AAX7019_sales_transactions` VALUES (1,'TXN202507310001',NULL,'AfrpyFPcjvb4YMDvjJkfX6X0UF83','Tbl Seven',NULL,4.00,0.00,0.00,'none',0.00,0.00,4.00,4.00,0.00,'paid','sale','2025-07-31 17:03:43',NULL,0,'2025-07-31 09:03:43','2025-07-31 09:03:43'),(2,'TXN202507310002',NULL,'AfrpyFPcjvb4YMDvjJkfX6X0UF83','Tbl Seven',NULL,4.00,0.00,0.00,'none',0.00,0.00,4.00,4.00,0.00,'paid','sale','2025-07-31 17:03:43',NULL,0,'2025-07-31 09:03:43','2025-07-31 09:03:43'),(3,'TXN202507310003',NULL,'AfrpyFPcjvb4YMDvjJkfX6X0UF83','Tbl Seven',NULL,4.00,0.00,0.00,'none',0.00,0.00,4.00,4.00,0.00,'paid','sale','2025-07-31 17:13:41',NULL,0,'2025-07-31 09:13:41','2025-07-31 09:13:41'),(4,'TXN202507310004',NULL,'AfrpyFPcjvb4YMDvjJkfX6X0UF83','Tbl Seven',NULL,8.00,0.00,0.00,'none',0.00,0.00,8.00,8.00,0.00,'paid','sale','2025-07-31 20:32:22',NULL,0,'2025-07-31 12:32:22','2025-07-31 12:32:22'),(5,'TXN202507310005',NULL,'AfrpyFPcjvb4YMDvjJkfX6X0UF83','Tbl Seven',NULL,16.00,0.00,0.00,'none',0.00,0.00,16.00,16.00,0.00,'paid','sale','2025-07-31 21:31:03',NULL,0,'2025-07-31 13:31:03','2025-07-31 13:31:03'),(6,'TXN202507310006',1,'AfrpyFPcjvb4YMDvjJkfX6X0UF83','Tbl Seven',NULL,24.00,0.00,0.00,'none',0.00,0.00,24.00,0.00,0.00,'credit','sale','2025-07-31 21:32:25',NULL,0,'2025-07-31 13:32:25','2025-07-31 13:32:25'),(7,'TXN202508010001',NULL,'AfrpyFPcjvb4YMDvjJkfX6X0UF83','Tbl Seven',NULL,4.00,0.00,0.00,'none',0.00,0.00,4.00,4.00,0.00,'voided','sale','2025-08-01 08:25:01',NULL,0,'2025-08-01 00:25:01','2025-08-01 00:25:16'),(8,'TXN202508010002',NULL,'AfrpyFPcjvb4YMDvjJkfX6X0UF83','Tbl Seven',NULL,8.00,0.00,0.00,'none',0.00,0.00,8.00,8.00,0.00,'paid','sale','2025-08-01 12:18:48',NULL,0,'2025-08-01 04:18:48','2025-08-01 04:18:48'),(9,'TXN202508010003',NULL,'AfrpyFPcjvb4YMDvjJkfX6X0UF83','Tbl Seven',2,4.00,0.00,0.00,'none',0.00,0.00,4.00,4.00,0.00,'paid','sale','2025-08-01 18:01:32',NULL,0,'2025-08-01 10:01:32','2025-08-01 10:01:32'),(10,'TXN202508040001',1,'AfrpyFPcjvb4YMDvjJkfX6X0UF83','Tbl Seven',2,8.00,0.00,0.00,'none',0.00,0.00,8.00,0.00,0.00,'voided','sale','2025-08-04 12:51:56',NULL,0,'2025-08-04 04:51:56','2025-08-04 04:52:30'),(11,'TXN202508040002',1,'AfrpyFPcjvb4YMDvjJkfX6X0UF83','Tbl Seven',2,12.00,0.00,0.00,'none',0.00,0.00,12.00,0.00,0.00,'voided','sale','2025-08-04 12:53:46',NULL,0,'2025-08-04 04:53:46','2025-08-04 04:54:33'),(12,'TXN202508040003',2,'AfrpyFPcjvb4YMDvjJkfX6X0UF83','Tbl Seven',2,4.00,0.00,0.00,'none',0.00,0.00,4.00,0.00,0.00,'voided','sale','2025-08-04 12:59:34',NULL,0,'2025-08-04 04:59:34','2025-08-04 05:00:05'),(13,'TXN202508040004',2,'AfrpyFPcjvb4YMDvjJkfX6X0UF83','Tbl Seven',2,4.00,0.00,0.00,'none',0.00,0.00,4.00,0.00,0.00,'voided','sale','2025-08-04 13:03:50',NULL,0,'2025-08-04 05:03:50','2025-08-04 05:07:48'),(14,'TXN202508040005',2,'AfrpyFPcjvb4YMDvjJkfX6X0UF83','Tbl Seven',2,4.00,0.00,0.00,'none',0.00,0.00,4.00,0.00,0.00,'voided','sale','2025-08-04 13:05:05',NULL,0,'2025-08-04 05:05:05','2025-08-04 05:07:15'),(15,'TXN202508040006',NULL,'AfrpyFPcjvb4YMDvjJkfX6X0UF83','Tbl Seven',2,40.00,0.00,0.00,'none',0.00,0.00,40.00,40.00,0.00,'paid','sale','2025-08-04 13:14:28',NULL,0,'2025-08-04 05:14:28','2025-08-04 05:14:28'),(16,'TXN202508040007',NULL,'AfrpyFPcjvb4YMDvjJkfX6X0UF83','Tbl Seven',2,40.00,0.00,0.00,'none',0.00,0.00,40.00,40.00,0.00,'paid','sale','2025-08-04 13:15:10',NULL,0,'2025-08-04 05:15:10','2025-08-04 05:15:10'),(17,'TXN202508040008',NULL,'AfrpyFPcjvb4YMDvjJkfX6X0UF83','Tbl Seven',2,40.00,0.00,0.00,'none',0.00,0.00,40.00,40.00,0.00,'paid','sale','2025-08-04 13:23:54',NULL,0,'2025-08-04 05:23:54','2025-08-04 05:23:54'),(18,'TXN202508040009',NULL,'AfrpyFPcjvb4YMDvjJkfX6X0UF83','Tbl Seven',2,40.00,0.00,0.00,'none',0.00,0.00,40.00,40.00,0.00,'paid','sale','2025-08-04 13:24:18',NULL,0,'2025-08-04 05:24:18','2025-08-04 05:24:18'),(19,'TXN202508040010',NULL,'AfrpyFPcjvb4YMDvjJkfX6X0UF83','Tbl Seven',2,40.00,0.00,0.00,'none',0.00,0.00,40.00,40.00,0.00,'paid','sale','2025-08-04 13:29:42',NULL,0,'2025-08-04 05:29:42','2025-08-04 05:29:42'),(20,'TXN202508040011',NULL,'AfrpyFPcjvb4YMDvjJkfX6X0UF83','Tbl Seven',2,40.00,0.00,0.00,'none',0.00,0.00,40.00,40.00,0.00,'paid','sale','2025-08-04 13:29:51',NULL,0,'2025-08-04 05:29:51','2025-08-04 05:29:51'),(21,'TXN202508040012',NULL,'AfrpyFPcjvb4YMDvjJkfX6X0UF83','Tbl Seven',2,320.00,0.00,0.00,'none',0.00,0.00,320.00,320.00,0.00,'paid','sale','2025-08-04 13:30:12',NULL,0,'2025-08-04 05:30:12','2025-08-04 05:30:12'),(22,'TXN202508040013',3,'AfrpyFPcjvb4YMDvjJkfX6X0UF83','Tbl Seven',2,1.00,0.00,0.00,'none',0.00,0.00,1.00,0.00,0.00,'voided','sale','2025-08-04 15:33:11',NULL,0,'2025-08-04 07:33:11','2025-08-04 07:33:36'),(86,'TXN202508040014',NULL,'AfrpyFPcjvb4YMDvjJkfX6X0UF83','Tbl Seven',2,1.00,0.00,0.00,'none',0.00,0.00,1.00,1.00,0.00,'paid','sale','2025-08-04 19:09:24',NULL,0,'2025-08-04 11:09:24','2025-08-04 11:09:24'),(87,'TXN202508040015',NULL,'AfrpyFPcjvb4YMDvjJkfX6X0UF83','Tbl Seven',2,1.00,0.00,0.00,'none',0.00,0.00,1.00,1.00,0.00,'paid','sale','2025-08-04 19:09:25',NULL,0,'2025-08-04 11:09:25','2025-08-04 11:09:25'),(88,'TXN202508040016',NULL,'AfrpyFPcjvb4YMDvjJkfX6X0UF83','Tbl Seven',2,1.00,0.00,0.00,'none',0.00,0.00,1.00,1.00,0.00,'voided','sale','2025-08-04 19:09:31',NULL,0,'2025-08-04 11:09:31','2025-08-04 11:09:45'),(89,'TXN202508040017',NULL,'AfrpyFPcjvb4YMDvjJkfX6X0UF83','Tbl Seven',2,1.00,0.00,0.00,'none',0.00,0.00,1.00,1.00,0.00,'voided','sale','2025-08-04 19:11:01',NULL,0,'2025-08-04 11:11:01','2025-08-04 11:11:43'),(90,'TXN202508040018',2,'AfrpyFPcjvb4YMDvjJkfX6X0UF83','Tbl Seven',2,1.00,0.00,0.00,'none',0.00,0.00,1.00,0.00,0.00,'voided','sale','2025-08-04 19:12:52',NULL,0,'2025-08-04 11:12:52','2025-08-04 11:13:17'),(91,'TXN202508040019',1,'AfrpyFPcjvb4YMDvjJkfX6X0UF83','Tbl Seven',6,4.00,0.00,0.00,'none',0.00,0.00,4.00,4.00,0.00,'paid','sale','2025-08-04 22:05:12',NULL,0,'2025-08-04 14:05:12','2025-08-04 14:05:12'),(92,'TXN202508040020',1,'AfrpyFPcjvb4YMDvjJkfX6X0UF83','Tbl Seven',6,8.00,0.00,0.00,'none',0.00,0.00,8.00,0.00,0.00,'credit','sale','2025-08-04 22:06:01',NULL,0,'2025-08-04 14:06:01','2025-08-04 14:06:01'),(93,'TXN202508040021',1,'AfrpyFPcjvb4YMDvjJkfX6X0UF83','Tbl Seven',6,5.00,0.00,0.00,'none',0.00,0.00,5.00,0.00,0.00,'credit','sale','2025-08-04 22:34:40',NULL,0,'2025-08-04 14:34:40','2025-08-04 14:34:40'),(94,'TXN202508060001',NULL,'AfrpyFPcjvb4YMDvjJkfX6X0UF83','Tbl Seven',NULL,13.00,0.00,0.00,'none',0.00,0.00,13.00,0.00,0.00,'paid','sale','2025-08-06 05:00:14',NULL,0,'2025-08-05 21:00:14','2025-08-05 21:00:14');

--
-- Table structure for table `AAX7019_stock_alerts`
--

CREATE TABLE `AAX7019_stock_alerts` (
  `alert_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `variant_id` int(11) DEFAULT NULL,
  `alert_type` enum('low_stock','out_of_stock','expiry_warning','expiry_critical') NOT NULL,
  `alert_message` text NOT NULL,
  `severity` enum('info','warning','critical') NOT NULL DEFAULT 'warning',
  `current_stock` decimal(15,3) DEFAULT NULL,
  `reorder_level` decimal(15,3) DEFAULT NULL,
  `expiry_date` date DEFAULT NULL,
  `is_acknowledged` tinyint(1) DEFAULT '0',
  `acknowledged_by` varchar(128) DEFAULT NULL,
  `acknowledged_at` timestamp NULL DEFAULT NULL,
  `is_resolved` tinyint(1) DEFAULT '0',
  `resolved_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`alert_id`),
  KEY `idx_product_alert` (`product_id`,`alert_type`),
  KEY `idx_severity_ack` (`severity`,`is_acknowledged`),
  KEY `idx_expiry_date` (`expiry_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `AAX7019_stock_alerts`
--


--
-- Table structure for table `AAX7019_stock_movements`
--

CREATE TABLE `AAX7019_stock_movements` (
  `movement_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `variant_id` int(11) DEFAULT NULL,
  `movement_type` enum('purchase','sale','adjustment','transfer','return','damaged','expired','initial','receipt','delivery') NOT NULL,
  `quantity_before` decimal(15,3) NOT NULL,
  `quantity_change` decimal(15,3) NOT NULL,
  `quantity_after` decimal(15,3) NOT NULL,
  `unit_cost` decimal(12,2) DEFAULT NULL,
  `total_value` decimal(15,2) DEFAULT NULL,
  `reference_type` enum('purchase_order','sale_invoice','adjustment','transfer','return','manual') DEFAULT 'manual',
  `reference_id` varchar(100) DEFAULT NULL,
  `reason` varchar(255) DEFAULT NULL,
  `notes` text,
  `movement_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` varchar(128) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`movement_id`),
  KEY `idx_product_movement` (`product_id`,`movement_type`),
  KEY `idx_movement_date` (`movement_date`),
  KEY `idx_reference` (`reference_type`,`reference_id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `AAX7019_stock_movements`
--

INSERT INTO `AAX7019_stock_movements` VALUES (1,2,2,'sale',5.000,-1.000,4.000,NULL,NULL,'sale_invoice',NULL,NULL,NULL,'2025-08-04 20:29:42','POS_SYSTEM','2025-08-04 05:29:42'),(2,2,2,'sale',4.000,-1.000,3.000,NULL,NULL,'sale_invoice',NULL,NULL,NULL,'2025-08-04 20:29:51','POS_SYSTEM','2025-08-04 05:29:51'),(3,2,2,'sale',3.000,-8.000,-5.000,NULL,NULL,'sale_invoice',NULL,NULL,NULL,'2025-08-04 20:30:12','POS_SYSTEM','2025-08-04 05:30:12'),(4,2,2,'receipt',-5.000,6.000,1.000,NULL,NULL,'manual',NULL,NULL,NULL,'2025-08-04 05:33:41',NULL,'2025-08-04 05:33:41'),(5,2,1,'sale',5.000,-1.000,4.000,NULL,NULL,'sale_invoice',NULL,NULL,NULL,'2025-08-04 22:33:11','POS_SYSTEM','2025-08-04 07:33:11'),(6,2,NULL,'sale',0.000,1.000,1.000,NULL,NULL,'sale_invoice',NULL,NULL,NULL,'2025-08-04 22:33:36','POS_SYSTEM','2025-08-04 07:33:36'),(7,2,NULL,'sale',1.000,-1.000,0.000,NULL,NULL,'sale_invoice',NULL,NULL,NULL,'2025-08-05 02:09:24','POS_SYSTEM','2025-08-04 11:09:24'),(8,2,NULL,'sale',0.000,-1.000,-1.000,NULL,NULL,'sale_invoice',NULL,NULL,NULL,'2025-08-05 02:09:25','POS_SYSTEM','2025-08-04 11:09:25'),(9,2,1,'sale',4.000,-1.000,3.000,NULL,NULL,'sale_invoice',NULL,NULL,NULL,'2025-08-05 02:09:31','POS_SYSTEM','2025-08-04 11:09:31'),(10,2,1,'sale',3.000,1.000,4.000,NULL,NULL,'sale_invoice',NULL,NULL,NULL,'2025-08-05 02:09:45','POS_SYSTEM','2025-08-04 11:09:45'),(11,2,NULL,'sale',-1.000,-1.000,-2.000,NULL,NULL,'sale_invoice',NULL,NULL,NULL,'2025-08-05 02:11:01','POS_SYSTEM','2025-08-04 11:11:01'),(12,2,NULL,'sale',-2.000,1.000,-1.000,NULL,NULL,'sale_invoice',NULL,NULL,NULL,'2025-08-05 02:11:43','POS_SYSTEM','2025-08-04 11:11:43'),(13,2,1,'sale',4.000,-1.000,3.000,NULL,NULL,'sale_invoice',NULL,NULL,NULL,'2025-08-05 02:12:52','POS_SYSTEM','2025-08-04 11:12:52'),(14,2,1,'sale',3.000,1.000,4.000,NULL,NULL,'sale_invoice',NULL,NULL,NULL,'2025-08-05 02:13:17','POS_SYSTEM','2025-08-04 11:13:17'),(15,2,1,'sale',4.000,-1.000,3.000,NULL,NULL,'sale_invoice',NULL,NULL,NULL,'2025-08-05 05:34:40','POS_SYSTEM','2025-08-04 14:34:40'),(16,2,1,'sale',3.000,-1.000,2.000,NULL,NULL,'sale_invoice',NULL,NULL,NULL,'2025-08-06 12:00:14','POS_SYSTEM','2025-08-05 21:00:14');

--
-- Table structure for table `AAY6074_business_settings`
--

CREATE TABLE `AAY6074_business_settings` (
  `setting_id` int(11) NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text NOT NULL,
  `setting_type` enum('string','boolean','number','json') NOT NULL DEFAULT 'string',
  `setting_group` int(11) NOT NULL DEFAULT '1' COMMENT '1=POS Settings, 2=Receipt Settings, 3=Printer Settings, 4=Staff Settings, 5=Inventory Settings',
  `setting_title` varchar(200) NOT NULL,
  `setting_description` varchar(500) DEFAULT NULL,
  `setting_text` varchar(200) DEFAULT NULL COMMENT 'Placeholder text for inputs',
  `setting_order` int(11) NOT NULL DEFAULT '0',
  `is_required` tinyint(1) NOT NULL DEFAULT '0',
  `setting_options` text COMMENT 'JSON string for dropdown/radio options',
  `description` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`setting_id`),
  UNIQUE KEY `unique_setting_key` (`setting_key`),
  KEY `idx_setting_group` (`setting_group`),
  KEY `idx_setting_order` (`setting_order`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `AAY6074_business_settings`
--

INSERT INTO `AAY6074_business_settings` VALUES (1,'show_item_search_bar','true','boolean',1,'Show Item Search Bar','Display search bar in POS items view for quick product lookup','',1,0,NULL,NULL,'2025-07-31 07:11:58','2025-07-31 07:11:58'),(2,'show_item_quick_stats','false','boolean',1,'Show Item Quick Stats','Display quick statistics like stock levels in POS items view','',2,0,NULL,NULL,'2025-07-31 07:11:58','2025-07-31 07:11:58'),(3,'show_category_filter','false','boolean',1,'Show Category Filter','Display category filter in POS items view','',3,0,NULL,NULL,'2025-07-31 07:11:58','2025-07-31 07:11:58'),(4,'allow_fast_checkout','false','boolean',1,'Allow Fast Checkout','Perform fast Cash Sales checkout in one click.','',4,0,NULL,NULL,'2025-07-31 07:11:58','2025-07-31 07:11:58'),(5,'show_invoice_upon_checkout','true','boolean',1,'Show Invoice Upon Checkout','Allow display of receipt details upon completing checkout.','',5,0,NULL,NULL,'2025-07-31 07:11:58','2025-07-31 07:11:58'),(6,'require_cashier_shift','true','boolean',2,'Require Cashier Shift','Monitor cashier cash drawer balance and other transaction.','',1,0,NULL,NULL,'2025-07-31 07:11:58','2025-08-01 10:04:34'),(7,'auto_print_receipt','false','boolean',3,'Auto Print Receipt','Automatically print receipt after successful checkout','',1,0,NULL,NULL,'2025-07-31 07:11:58','2025-07-31 07:11:58'),(8,'business_logo_path','','string',3,'Business Logo Path','Path to business logo for receipt printing','',2,0,NULL,NULL,'2025-07-31 07:11:58','2025-07-31 07:11:58'),(9,'receipt_business_name','My Business','string',3,'Business Name','Business name shown on receipt (required)','',3,0,NULL,NULL,'2025-07-31 07:11:58','2025-07-31 07:11:58'),(10,'show_receipt_phone','true','boolean',3,'Show Receipt Phone','Display business phone number on receipt','',4,0,NULL,NULL,'2025-07-31 07:11:58','2025-07-31 07:11:58'),(11,'show_receipt_address','true','boolean',3,'Show Receipt Address','Display business address on receipt','',5,0,NULL,NULL,'2025-07-31 07:11:58','2025-07-31 07:11:58'),(12,'show_receipt_tax_id','true','boolean',3,'Show Receipt Tax ID','Display business tax identification number on receipt','',6,0,NULL,NULL,'2025-07-31 07:11:58','2025-07-31 07:11:58'),(13,'show_receipt_website','true','boolean',3,'Show Receipt Website','Display business website URL on receipt','',7,0,NULL,NULL,'2025-07-31 07:11:58','2025-07-31 07:11:58'),(14,'show_receipt_title','true','boolean',3,'Show Receipt Title','Display receipt title/header on receipt','',8,0,NULL,NULL,'2025-07-31 07:11:58','2025-07-31 07:11:58'),(15,'receipt_title_text','** Invoice **','string',3,'Receipt Title Text','Custom receipt title/header text','',9,0,NULL,NULL,'2025-07-31 07:11:58','2025-07-31 07:11:58'),(16,'show_cashier_name','true','boolean',3,'Show Cashier Name','Display cashier/staff name on receipt','',10,0,NULL,NULL,'2025-07-31 07:11:58','2025-07-31 07:11:58'),(17,'show_customer_name','true','boolean',3,'Show Customer Name','Display customer name on receipt when available','',11,0,NULL,NULL,'2025-07-31 07:11:58','2025-07-31 07:11:58'),(18,'show_customer_address','true','boolean',3,'Show Customer Address','Display customer address on receipt when available','',12,0,NULL,NULL,'2025-07-31 07:11:58','2025-07-31 07:11:58'),(19,'show_customer_tax_no','true','boolean',3,'Show Customer Tax Number','Display customer tax identification number when available','',13,0,NULL,NULL,'2025-07-31 07:11:58','2025-07-31 07:11:58'),(20,'receipt_price_inclusive','true','boolean',3,'Price Inclusive of Tax','Display note that prices are inclusive of applicable taxes','',14,0,NULL,NULL,'2025-07-31 07:11:58','2025-07-31 07:11:58'),(21,'show_total_item_count','true','boolean',3,'Show Total Item Count','Display total number of items in transaction','',15,0,NULL,NULL,'2025-07-31 07:11:58','2025-07-31 07:11:58'),(22,'show_change_amount','true','boolean',3,'Show Change Amount','Display change amount given to customer','',16,0,NULL,NULL,'2025-07-31 07:11:58','2025-07-31 07:11:58'),(23,'show_payment_details','true','boolean',3,'Show Payment Details','Display payment method and transaction details','',17,0,NULL,NULL,'2025-07-31 07:11:58','2025-07-31 07:11:58'),(24,'show_thank_you_note','true','boolean',3,'Show Thank You Note','Display thank you message at receipt footer','',18,0,NULL,NULL,'2025-07-31 07:11:58','2025-07-31 07:11:58'),(25,'thank_you_note_text','Thank You, Visit Again.','string',3,'Thank You Note Text','Custom thank you message for receipt footer','',19,0,NULL,NULL,'2025-07-31 07:11:58','2025-07-31 07:11:58'),(26,'receipt_order_items_by','name','json',3,'Order Items By','How to order items on receipt: by name or order added','',20,0,'{\"name\":\"Product Name\",\"order_added\":\"Order Added\"}',NULL,'2025-07-31 07:11:58','2025-07-31 07:11:58');

--
-- Table structure for table `AAY6074_cashflow_transactions`
--

CREATE TABLE `AAY6074_cashflow_transactions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `transaction_type` enum('income','expense') COLLATE utf8mb4_unicode_ci NOT NULL,
  `category_id` int(11) NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `transaction_date` datetime NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `reference_number` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `payment_method_id` int(11) NOT NULL,
  `receipt_image_path` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `linked_sales_transaction_id` int(11) DEFAULT NULL,
  `created_by` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_transaction_type` (`transaction_type`),
  KEY `idx_category_id` (`category_id`),
  KEY `idx_transaction_date` (`transaction_date`),
  KEY `idx_linked_sales` (`linked_sales_transaction_id`),
  KEY `idx_payment_method_id` (`payment_method_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAY6074_cashflow_transactions`
--

INSERT INTO `AAY6074_cashflow_transactions` VALUES (1,'income',1,8.00,'2025-08-01 12:03:40','Sales Transaction','TXN202508010001',1,NULL,'Auto-generated from POS sale',NULL,'Tbl Seven','2025-08-01 10:03:41','2025-08-01 10:03:41');

--
-- Table structure for table `AAY6074_cashier_counts`
--

CREATE TABLE `AAY6074_cashier_counts` (
  `count_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `shift_id` bigint(20) NOT NULL,
  `count_type` enum('opening','closing','mid_shift') COLLATE utf8mb4_unicode_ci NOT NULL,
  `total_amount` decimal(10,2) NOT NULL,
  `counted_at` datetime NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`count_id`),
  KEY `idx_shift_counts` (`shift_id`,`count_type`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAY6074_cashier_counts`
--

INSERT INTO `AAY6074_cashier_counts` VALUES (1,1,'opening',1000.00,'2025-08-01 03:04:52','2025-08-01 10:04:52'),(2,1,'closing',1000.00,'2025-08-01 03:06:28','2025-08-01 10:06:28'),(3,2,'opening',1000.00,'2025-08-01 03:23:30','2025-08-01 10:23:30'),(4,2,'closing',200.00,'2025-08-01 03:25:05','2025-08-01 10:25:05'),(5,3,'opening',1000.00,'2025-08-01 03:29:27','2025-08-01 10:29:27'),(6,3,'closing',200.00,'2025-08-01 03:29:48','2025-08-01 10:29:48');

--
-- Table structure for table `AAY6074_cashier_denominations`
--

CREATE TABLE `AAY6074_cashier_denominations` (
  `denomination_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `count_id` bigint(20) NOT NULL,
  `denomination_value` decimal(10,2) NOT NULL,
  `quantity` int(11) NOT NULL,
  `subtotal` decimal(10,2) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`denomination_id`),
  KEY `idx_count_denoms` (`count_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAY6074_cashier_denominations`
--

INSERT INTO `AAY6074_cashier_denominations` VALUES (1,1,1000.00,1,1000.00,'2025-08-01 10:04:52'),(2,2,1000.00,1,1000.00,'2025-08-01 10:06:28'),(3,3,1000.00,1,1000.00,'2025-08-01 10:23:30'),(4,4,200.00,1,200.00,'2025-08-01 10:25:05'),(5,5,1000.00,1,1000.00,'2025-08-01 10:29:27'),(6,6,200.00,1,200.00,'2025-08-01 10:29:48');

--
-- Table structure for table `AAY6074_cashier_shifts`
--

CREATE TABLE `AAY6074_cashier_shifts` (
  `shift_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `cashier_id` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `shift_start` datetime NOT NULL,
  `shift_end` datetime DEFAULT NULL,
  `opening_amount` decimal(10,2) NOT NULL,
  `closing_amount` decimal(10,2) DEFAULT NULL,
  `expected_amount` decimal(10,2) DEFAULT NULL,
  `difference_amount` decimal(10,2) DEFAULT NULL,
  `status` enum('open','closed') COLLATE utf8mb4_unicode_ci DEFAULT 'open',
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`shift_id`),
  KEY `idx_cashier_status` (`cashier_id`,`status`),
  KEY `idx_shift_dates` (`shift_start`,`shift_end`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAY6074_cashier_shifts`
--

INSERT INTO `AAY6074_cashier_shifts` VALUES (1,'AfrpyFPcjvb4YMDvjJkfX6X0UF83','2025-08-01 03:04:52','2025-08-01 03:06:28',1000.00,1000.00,1000.00,0.00,'closed',NULL,'2025-08-01 10:04:52','2025-08-01 10:06:28'),(2,'AfrpyFPcjvb4YMDvjJkfX6X0UF83','2025-08-01 03:23:30','2025-08-01 03:25:05',1000.00,200.00,1000.00,-800.00,'closed',NULL,'2025-08-01 10:23:30','2025-08-01 10:25:05'),(3,'AfrpyFPcjvb4YMDvjJkfX6X0UF83','2025-08-01 12:29:25','2025-08-01 12:29:48',1000.00,200.00,1000.00,-800.00,'closed',NULL,'2025-08-01 10:29:27','2025-08-01 10:29:48');

--
-- Table structure for table `AAY6074_cashier_transactions`
--

CREATE TABLE `AAY6074_cashier_transactions` (
  `transaction_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `shift_id` bigint(20) NOT NULL,
  `transaction_type` enum('sale','refund','void','payout','cash_in','cash_out','expense') COLLATE utf8mb4_unicode_ci NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `reference_id` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `payment_method` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`transaction_id`),
  KEY `idx_shift_transactions` (`shift_id`,`transaction_type`),
  KEY `idx_reference` (`reference_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAY6074_cashier_transactions`
--


--
-- Table structure for table `AAY6074_customer_accounts`
--

CREATE TABLE `AAY6074_customer_accounts` (
  `customer_id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_code` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Unique customer identifier within business',
  `customer_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `customer_phone` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `customer_email` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `customer_address` text COLLATE utf8mb4_unicode_ci,
  `customer_type` enum('individual','business') COLLATE utf8mb4_unicode_ci DEFAULT 'individual',
  `tax_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'TIN or business registration number',
  `credit_enabled` tinyint(1) DEFAULT '0' COMMENT 'Whether customer can purchase on credit',
  `customer_status` enum('active','inactive','suspended') COLLATE utf8mb4_unicode_ci DEFAULT 'active',
  `date_of_birth` date DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`customer_id`),
  UNIQUE KEY `uk_customer_code` (`customer_code`),
  UNIQUE KEY `uk_customer_phone` (`customer_phone`),
  KEY `idx_customer_name` (`customer_name`),
  KEY `idx_customer_status` (`customer_status`),
  KEY `idx_credit_enabled` (`credit_enabled`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAY6074_customer_accounts`
--


--
-- Table structure for table `AAY6074_customer_credit_accounts`
--

CREATE TABLE `AAY6074_customer_credit_accounts` (
  `credit_account_id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `credit_limit` decimal(15,2) DEFAULT '0.00' COMMENT 'Maximum credit limit',
  `current_balance` decimal(15,2) DEFAULT '0.00' COMMENT 'Current outstanding balance',
  `available_credit` decimal(15,2) DEFAULT '0.00' COMMENT 'Available credit (limit - balance)',
  `payment_terms` enum('cash','net_7','net_15','net_30','net_45','net_60') COLLATE utf8mb4_unicode_ci DEFAULT 'net_30',
  `interest_rate` decimal(5,2) DEFAULT '0.00' COMMENT 'Monthly interest rate percentage',
  `last_payment_date` date DEFAULT NULL,
  `last_purchase_date` date DEFAULT NULL,
  `overdue_amount` decimal(15,2) DEFAULT '0.00',
  `days_overdue` int(11) DEFAULT '0',
  `credit_status` enum('good','warning','overdue','suspended') COLLATE utf8mb4_unicode_ci DEFAULT 'good',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`credit_account_id`),
  UNIQUE KEY `uk_customer_credit` (`customer_id`),
  CONSTRAINT `AAY6074_customer_credit_accounts_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `AAY6074_customer_accounts` (`customer_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAY6074_customer_credit_accounts`
--


--
-- Temporary table structure for view `AAY6074_customer_outstanding_view`
--

/*!50001 DROP VIEW IF EXISTS `AAY6074_customer_outstanding_view`*/;
/*!50001 CREATE VIEW `AAY6074_customer_outstanding_view` AS SELECT 
 1 AS `customer_id`,
 1 AS `customer_code`,
 1 AS `customer_name`,
 1 AS `customer_phone`,
 1 AS `current_balance`,
 1 AS `overdue_amount`,
 1 AS `days_overdue`,
 1 AS `credit_status`,
 1 AS `last_payment_date`,
 1 AS `next_due_date`*/;

--
-- Table structure for table `AAY6074_customer_payment_history`
--

CREATE TABLE `AAY6074_customer_payment_history` (
  `payment_id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `customer_transaction_id` int(11) NOT NULL,
  `payment_amount` decimal(15,2) NOT NULL,
  `payment_method_id` int(11) NOT NULL,
  `reference_number` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `payment_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `processed_by` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `bank_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `check_date` date DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`payment_id`),
  KEY `idx_customer_id` (`customer_id`),
  KEY `idx_payment_date` (`payment_date`),
  KEY `idx_payment_method_id` (`payment_method_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAY6074_customer_payment_history`
--


--
-- Table structure for table `AAY6074_customer_sequences`
--

CREATE TABLE `AAY6074_customer_sequences` (
  `sequence_name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `current_value` int(11) NOT NULL DEFAULT '0',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`sequence_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAY6074_customer_sequences`
--


--
-- Temporary table structure for view `AAY6074_customer_summary_view`
--

/*!50001 DROP VIEW IF EXISTS `AAY6074_customer_summary_view`*/;
/*!50001 CREATE VIEW `AAY6074_customer_summary_view` AS SELECT 
 1 AS `customer_id`,
 1 AS `customer_code`,
 1 AS `customer_name`,
 1 AS `customer_phone`,
 1 AS `customer_email`,
 1 AS `customer_status`,
 1 AS `credit_enabled`,
 1 AS `credit_limit`,
 1 AS `current_balance`,
 1 AS `available_credit`,
 1 AS `overdue_amount`,
 1 AS `days_overdue`,
 1 AS `credit_status`,
 1 AS `last_purchase_date`,
 1 AS `last_payment_date`,
 1 AS `total_purchases`,
 1 AS `total_purchase_amount`,
 1 AS `customer_since`*/;

--
-- Table structure for table `AAY6074_customer_transactions`
--

CREATE TABLE `AAY6074_customer_transactions` (
  `customer_transaction_id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `sales_transaction_id` int(11) DEFAULT NULL COMMENT 'Link to sales transaction if applicable',
  `transaction_type` enum('sale','payment','credit_adjustment','interest_charge','fee') COLLATE utf8mb4_unicode_ci NOT NULL,
  `amount` decimal(15,2) NOT NULL COMMENT 'Transaction amount (positive for charges, negative for payments)',
  `balance_before` decimal(15,2) NOT NULL COMMENT 'Balance before this transaction',
  `balance_after` decimal(15,2) NOT NULL COMMENT 'Balance after this transaction',
  `payment_method_id` int(11) DEFAULT NULL,
  `reference_number` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Check number, transfer reference, etc.',
  `transaction_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `due_date` date DEFAULT NULL COMMENT 'Due date for credit sales',
  `notes` text COLLATE utf8mb4_unicode_ci,
  `processed_by` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Staff member who processed',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`customer_transaction_id`),
  KEY `idx_customer_id` (`customer_id`),
  KEY `idx_transaction_type` (`transaction_type`),
  KEY `idx_transaction_date` (`transaction_date`),
  KEY `idx_sales_transaction` (`sales_transaction_id`),
  KEY `idx_payment_method_id` (`payment_method_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAY6074_customer_transactions`
--


--
-- Table structure for table `AAY6074_denominations_config`
--

CREATE TABLE `AAY6074_denominations_config` (
  `config_id` int(11) NOT NULL AUTO_INCREMENT,
  `denomination_value` decimal(10,2) NOT NULL,
  `currency_code` varchar(3) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'PHP',
  `currency_symbol` varchar(5) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '₱',
  `is_coin` tinyint(1) NOT NULL DEFAULT '0',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `display_order` int(11) NOT NULL DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`config_id`),
  UNIQUE KEY `idx_denom_value` (`denomination_value`),
  KEY `idx_active_order` (`is_active`,`display_order`),
  KEY `idx_coin_active` (`is_coin`,`is_active`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAY6074_denominations_config`
--

INSERT INTO `AAY6074_denominations_config` VALUES (1,0.01,'PHP','₱',1,1,1,'2025-07-31 07:11:58','2025-07-31 07:11:58'),(2,0.05,'PHP','₱',1,1,2,'2025-07-31 07:11:58','2025-07-31 07:11:58'),(3,0.10,'PHP','₱',1,1,3,'2025-07-31 07:11:58','2025-07-31 07:11:58'),(4,0.25,'PHP','₱',1,1,4,'2025-07-31 07:11:58','2025-07-31 07:11:58'),(5,1.00,'PHP','₱',1,1,5,'2025-07-31 07:11:58','2025-07-31 07:11:58'),(6,5.00,'PHP','₱',1,1,6,'2025-07-31 07:11:58','2025-07-31 07:11:58'),(7,10.00,'PHP','₱',1,1,7,'2025-07-31 07:11:58','2025-07-31 07:11:58'),(8,20.00,'PHP','₱',0,1,8,'2025-07-31 07:11:58','2025-07-31 07:11:58'),(9,50.00,'PHP','₱',0,1,9,'2025-07-31 07:11:58','2025-07-31 07:11:58'),(10,100.00,'PHP','₱',0,1,10,'2025-07-31 07:11:58','2025-07-31 07:11:58'),(11,200.00,'PHP','₱',0,1,11,'2025-07-31 07:11:58','2025-07-31 07:11:58'),(12,500.00,'PHP','₱',0,1,12,'2025-07-31 07:11:58','2025-07-31 07:11:58'),(13,1000.00,'PHP','₱',0,1,13,'2025-07-31 07:11:58','2025-07-31 07:11:58');

--
-- Table structure for table `AAY6074_expenses_approvals`
--

CREATE TABLE `AAY6074_expenses_approvals` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `expense_id` int(11) NOT NULL,
  `approver_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `approval_level` int(11) DEFAULT '1',
  `status` enum('PENDING','APPROVED','REJECTED') COLLATE utf8mb4_unicode_ci DEFAULT 'PENDING',
  `comments` text COLLATE utf8mb4_unicode_ci,
  `approved_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `expense_id` (`expense_id`),
  CONSTRAINT `AAY6074_expenses_approvals_ibfk_1` FOREIGN KEY (`expense_id`) REFERENCES `AAY6074_expenses_transactions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAY6074_expenses_approvals`
--


--
-- Table structure for table `AAY6074_expenses_categories`
--

CREATE TABLE `AAY6074_expenses_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `category_code` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `parent_category_id` int(11) DEFAULT NULL,
  `is_system_category` tinyint(1) DEFAULT '0',
  `requires_approval` tinyint(1) DEFAULT '0',
  `approval_limit` decimal(15,2) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `category_code` (`category_code`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAY6074_expenses_categories`
--

INSERT INTO `AAY6074_expenses_categories` VALUES (1,'Supplies and Office Materials','SUPPLIES','Stationery, cleaning supplies, packaging materials, and minor operational items.',NULL,1,0,0.00,1,'2025-07-31 07:11:56','2025-07-31 07:11:56'),(2,'Rent and Lease','RENT','Expenses for renting or leasing storefronts, offices, or storage spaces.',NULL,1,0,0.00,1,'2025-07-31 07:11:56','2025-07-31 07:11:56'),(3,'Marketing and Advertising','MARKETING','Promotional activities, social media marketing, printing, and signage.',NULL,1,0,0.00,1,'2025-07-31 07:11:56','2025-07-31 07:11:56'),(4,'Professional Services','PROFESSIONAL','Fees for legal, accounting, consulting, or other professional services.',NULL,1,0,0.00,1,'2025-07-31 07:11:56','2025-07-31 07:11:56'),(5,'Technology and Software','TECHNOLOGY','Expenses for POS software, business management tools, and IT support.',NULL,1,0,0.00,1,'2025-07-31 07:11:56','2025-07-31 07:11:56'),(6,'Transportation and Delivery','TRANSPORTATION','Costs for vehicle expenses, fuel, courier, or delivery services.',NULL,1,0,0.00,1,'2025-07-31 07:11:56','2025-07-31 07:11:56'),(7,'Insurance','INSURANCE','Business, property, liability, and worker\'s compensation insurance premiums.',NULL,1,0,0.00,1,'2025-07-31 07:11:56','2025-07-31 07:11:56'),(8,'Training and Development','TRAINING','Employee training programs, workshops, and skill development.',NULL,1,0,0.00,1,'2025-07-31 07:11:56','2025-07-31 07:11:56'),(9,'Equipment and Maintenance','MAINTENANCE','Purchase or repair of equipment, machinery, kitchen appliances, or POS hardware.',NULL,1,0,0.00,1,'2025-07-31 07:11:56','2025-07-31 07:11:56'),(10,'Banking and Financial Charges','BANK_FEES','Bank fees, interest on loans, credit card processing fees.',NULL,1,0,0.00,1,'2025-07-31 07:11:56','2025-07-31 07:11:56'),(11,'Licenses and Permits','PERMITS','Fees paid to government authorities for operational licenses and permits.',NULL,1,0,0.00,1,'2025-07-31 07:11:56','2025-07-31 07:11:56'),(12,'Payroll Taxes and Benefits','BENEFITS','Employer contributions to social security, health insurance, retirement plans, and other benefits.',NULL,1,0,0.00,1,'2025-07-31 07:11:56','2025-07-31 07:11:56'),(13,'Inventory Purchases','INVENTORY','Cost of goods bought for resale or use in operations, including wholesale stock.',NULL,1,0,0.00,1,'2025-07-31 07:11:56','2025-07-31 07:11:56'),(14,'Utilities','UTILITIES','Costs for electricity, water, gas, internet, and phone services.',NULL,1,0,0.00,1,'2025-07-31 07:11:56','2025-07-31 07:11:56'),(15,'Salaries and Wages','SALARIES','Payments to employees, including wages, salaries, and overtime.',NULL,1,0,0.00,1,'2025-07-31 07:11:56','2025-07-31 07:11:56'),(16,'Food and Beverage Costs','FB_COST','Expenses for purchasing ingredients, beverages, and related consumables.',NULL,1,0,0.00,1,'2025-07-31 07:11:56','2025-07-31 07:11:56'),(17,'Depreciation and Amortization','DEPRECIATION','Gradual expense recognition for tangible and intangible assets.',NULL,1,0,0.00,1,'2025-07-31 07:11:56','2025-07-31 07:11:56'),(18,'Miscellaneous','MISCELLANEOUS','Unclassified or irregular expenses that don\'t fit into other categories.',NULL,1,0,0.00,1,'2025-07-31 07:11:56','2025-07-31 07:11:56');

--
-- Table structure for table `AAY6074_expenses_line_items`
--

CREATE TABLE `AAY6074_expenses_line_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `expense_id` int(11) NOT NULL,
  `item_description` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `quantity` decimal(10,3) DEFAULT '1.000',
  `unit_price` decimal(15,2) NOT NULL,
  `line_total` decimal(15,2) NOT NULL,
  `tax_rate` decimal(5,2) DEFAULT '0.00',
  `tax_amount` decimal(15,2) DEFAULT '0.00',
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_expenses_line_items_expense` (`expense_id`),
  CONSTRAINT `AAY6074_expenses_line_items_ibfk_1` FOREIGN KEY (`expense_id`) REFERENCES `AAY6074_expenses_transactions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAY6074_expenses_line_items`
--


--
-- Table structure for table `AAY6074_expenses_payments`
--

CREATE TABLE `AAY6074_expenses_payments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `expense_id` int(11) NOT NULL,
  `payment_amount` decimal(15,2) NOT NULL,
  `payment_date` date NOT NULL,
  `payment_method_id` int(11) NOT NULL,
  `reference_number` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_by` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_payment_method_id` (`payment_method_id`),
  KEY `idx_expenses_payments_expense` (`expense_id`),
  CONSTRAINT `AAY6074_expenses_payments_ibfk_1` FOREIGN KEY (`expense_id`) REFERENCES `AAY6074_expenses_transactions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `AAY6074_expenses_payments_ibfk_2` FOREIGN KEY (`payment_method_id`) REFERENCES `AAY6074_payment_method` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAY6074_expenses_payments`
--


--
-- Table structure for table `AAY6074_expenses_recurring`
--

CREATE TABLE `AAY6074_expenses_recurring` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `template_expense_id` int(11) NOT NULL,
  `next_due_date` date NOT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  `last_generated_date` date DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `template_expense_id` (`template_expense_id`),
  CONSTRAINT `AAY6074_expenses_recurring_ibfk_1` FOREIGN KEY (`template_expense_id`) REFERENCES `AAY6074_expenses_transactions` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAY6074_expenses_recurring`
--


--
-- Table structure for table `AAY6074_expenses_transactions`
--

CREATE TABLE `AAY6074_expenses_transactions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `expense_number` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `category_id` int(11) NOT NULL,
  `vendor_id` int(11) DEFAULT NULL,
  `shift_id` int(11) DEFAULT NULL,
  `expense_date` datetime NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `tax_amount` decimal(15,2) DEFAULT '0.00',
  `total_amount` decimal(15,2) NOT NULL,
  `currency_code` varchar(3) COLLATE utf8mb4_unicode_ci DEFAULT 'PHP',
  `exchange_rate` decimal(10,4) DEFAULT '1.0000',
  `description` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `reference_number` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `payment_method_id` int(11) NOT NULL,
  `payment_status` enum('PENDING','PAID','PARTIALLY_PAID','OVERDUE','CANCELLED') COLLATE utf8mb4_unicode_ci DEFAULT 'PENDING',
  `approval_status` enum('DRAFT','PENDING_APPROVAL','APPROVED','REJECTED') COLLATE utf8mb4_unicode_ci DEFAULT 'DRAFT',
  `recurring_type` enum('NONE','DAILY','WEEKLY','MONTHLY','QUARTERLY','YEARLY') COLLATE utf8mb4_unicode_ci DEFAULT 'NONE',
  `recurring_interval` int(11) DEFAULT '1',
  `recurring_until` date DEFAULT NULL,
  `parent_expense_id` int(11) DEFAULT NULL,
  `receipt_attachment` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_by` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `approved_by` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `approved_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `expense_number` (`expense_number`),
  KEY `idx_shift_id` (`shift_id`),
  KEY `idx_payment_method_id` (`payment_method_id`),
  KEY `idx_expenses_transactions_date` (`expense_date`),
  KEY `idx_expenses_transactions_status` (`payment_status`,`approval_status`),
  KEY `idx_expenses_transactions_vendor` (`vendor_id`),
  KEY `idx_expenses_transactions_category` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAY6074_expenses_transactions`
--


--
-- Table structure for table `AAY6074_expenses_vendors`
--

CREATE TABLE `AAY6074_expenses_vendors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `vendor_code` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `vendor_name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `contact_person` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `phone` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `address` text COLLATE utf8mb4_unicode_ci,
  `tax_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `payment_terms` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `currency_code` varchar(3) COLLATE utf8mb4_unicode_ci DEFAULT 'PHP',
  `is_active` tinyint(1) DEFAULT '1',
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `vendor_code` (`vendor_code`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAY6074_expenses_vendors`
--

INSERT INTO `AAY6074_expenses_vendors` VALUES (1,'VENDOR001','General Supplier','Contact Person','contact@supplier.com','+1234567890','123 Supplier St, City','TAX123456','Net 30','PHP',1,NULL,'2025-07-31 07:11:56','2025-07-31 07:11:56'),(2,'VENDOR002','Office Supplies Co.','Office Manager','office@supplies.com','+1234567891','456 Office Ave, City','TAX123457','Net 15','PHP',1,NULL,'2025-07-31 07:11:56','2025-07-31 07:11:56'),(3,'VENDOR003','Utility Company','Billing Dept','billing@utility.com','+1234567892','789 Utility Blvd, City','TAX123458','Due on receipt','PHP',1,NULL,'2025-07-31 07:11:56','2025-07-31 07:11:56'),(4,'VENDOR004','Marketing Agency','Account Manager','accounts@marketing.com','+1234567893','321 Marketing Rd, City','TAX123459','Net 30','PHP',1,NULL,'2025-07-31 07:11:56','2025-07-31 07:11:56'),(5,'VENDOR005','Maintenance Services','Service Manager','service@maintenance.com','+1234567894','654 Service Ln, City','TAX123460','Net 15','PHP',1,NULL,'2025-07-31 07:11:56','2025-07-31 07:11:56');

--
-- Table structure for table `AAY6074_payment_method`
--

CREATE TABLE `AAY6074_payment_method` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `method_code` varchar(50) NOT NULL,
  `method_name` varchar(100) NOT NULL,
  `account_number` varchar(100) DEFAULT NULL COMMENT 'Account number, card number, or other identifier',
  `description` text,
  `is_system` tinyint(1) NOT NULL DEFAULT '0' COMMENT '1 for system methods (CASH, CREDIT), 0 for user methods',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `display_order` int(11) DEFAULT '0',
  `requires_reference` tinyint(1) DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_method_code_account` (`method_code`,`account_number`),
  KEY `idx_method_code` (`method_code`),
  KEY `idx_is_system` (`is_system`),
  KEY `idx_is_active` (`is_active`),
  KEY `idx_display_order` (`display_order`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `AAY6074_payment_method`
--

INSERT INTO `AAY6074_payment_method` VALUES (1,'CASH','Cash',NULL,NULL,1,1,1,0,'2025-07-31 07:11:56','2025-07-31 07:11:56'),(2,'CREDIT','Credit',NULL,NULL,1,1,2,0,'2025-07-31 07:11:56','2025-07-31 07:11:56'),(3,'DEBIT_CARD','Debit Card',NULL,NULL,0,1,3,0,'2025-07-31 07:11:56','2025-07-31 07:11:56'),(4,'CREDIT_CARD','Credit Card',NULL,NULL,0,1,4,0,'2025-07-31 07:11:56','2025-07-31 07:11:56'),(5,'DIGITAL_WALLET','Digital Wallet',NULL,NULL,0,1,5,0,'2025-07-31 07:11:56','2025-07-31 07:11:56'),(6,'BANK_TRANSFER','Bank Transfer',NULL,NULL,0,1,6,0,'2025-07-31 07:11:56','2025-07-31 07:11:56'),(7,'CHECK','Check',NULL,NULL,0,1,7,0,'2025-07-31 07:11:56','2025-07-31 07:11:56'),(8,'DIGITAL_WALLET','GCash','09292827262',NULL,0,1,0,0,'2025-07-31 07:14:03','2025-07-31 07:14:03');

--
-- Table structure for table `AAY6074_product_barcodes`
--

CREATE TABLE `AAY6074_product_barcodes` (
  `barcode_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `variant_id` int(11) DEFAULT NULL,
  `barcode` varchar(255) NOT NULL,
  `barcode_type` enum('UPC','EAN8','EAN13','CODE128','CODE39','QR','CUSTOM') NOT NULL DEFAULT 'EAN13',
  `is_primary` tinyint(1) DEFAULT '0',
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`barcode_id`),
  UNIQUE KEY `unique_barcode` (`barcode`),
  KEY `idx_product_barcode` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `AAY6074_product_barcodes`
--


--
-- Table structure for table `AAY6074_product_brands`
--

CREATE TABLE `AAY6074_product_brands` (
  `brand_id` int(11) NOT NULL AUTO_INCREMENT,
  `brand_name` varchar(100) NOT NULL,
  `brand_description` text,
  `brand_logo` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`brand_id`),
  UNIQUE KEY `unique_brand_name` (`brand_name`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `AAY6074_product_brands`
--

INSERT INTO `AAY6074_product_brands` VALUES (1,'No Brand','Default brand for products without specific brand',NULL,1,'2025-07-31 07:11:56','2025-07-31 07:11:56');

--
-- Table structure for table `AAY6074_product_categories`
--

CREATE TABLE `AAY6074_product_categories` (
  `category_id` int(11) NOT NULL AUTO_INCREMENT,
  `category_name` varchar(100) NOT NULL,
  `category_description` text,
  `parent_category_id` int(11) DEFAULT NULL,
  `category_color` varchar(7) DEFAULT '#2196F3',
  `category_icon` varchar(50) DEFAULT 'category',
  `sort_order` int(11) DEFAULT '0',
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`category_id`),
  KEY `idx_parent_category` (`parent_category_id`),
  KEY `idx_active_sort` (`is_active`,`sort_order`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `AAY6074_product_categories`
--

INSERT INTO `AAY6074_product_categories` VALUES (1,'General','Default category for products',NULL,'#2196F3','category',0,1,'2025-07-31 07:11:56','2025-07-31 07:11:56');

--
-- Table structure for table `AAY6074_product_combos`
--

CREATE TABLE `AAY6074_product_combos` (
  `combo_id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_product_id` int(11) NOT NULL,
  `component_product_id` int(11) NOT NULL,
  `component_variant_id` int(11) DEFAULT NULL,
  `quantity_required` decimal(10,3) NOT NULL,
  `is_optional` tinyint(1) DEFAULT '0',
  `option_group` varchar(50) DEFAULT NULL,
  `max_selections` int(11) DEFAULT '1',
  `sort_order` int(11) DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`combo_id`),
  KEY `idx_parent_product` (`parent_product_id`),
  KEY `idx_component_product` (`component_product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `AAY6074_product_combos`
--


--
-- Table structure for table `AAY6074_product_extras`
--

CREATE TABLE `AAY6074_product_extras` (
  `extra_id` int(11) NOT NULL AUTO_INCREMENT,
  `extra_name` varchar(255) NOT NULL,
  `extra_description` text,
  `extra_type` enum('addon','service','charge','discount') NOT NULL DEFAULT 'addon',
  `extra_price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `is_percentage` tinyint(1) DEFAULT '0',
  `applicable_categories` json DEFAULT NULL,
  `applicable_products` json DEFAULT NULL,
  `min_quantity` decimal(10,3) DEFAULT '1.000',
  `max_quantity` decimal(10,3) DEFAULT NULL,
  `is_required` tinyint(1) DEFAULT '0',
  `requires_input` tinyint(1) DEFAULT '0',
  `is_active` tinyint(1) DEFAULT '1',
  `sort_order` int(11) DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`extra_id`),
  KEY `idx_extra_type` (`extra_type`),
  KEY `idx_active_sort` (`is_active`,`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `AAY6074_product_extras`
--


--
-- Table structure for table `AAY6074_product_serial_numbers`
--

CREATE TABLE `AAY6074_product_serial_numbers` (
  `serial_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `variant_id` int(11) DEFAULT NULL,
  `serial_number` varchar(100) NOT NULL,
  `batch_lot_number` varchar(50) DEFAULT NULL,
  `manufacturing_date` date DEFAULT NULL,
  `expiry_date` date DEFAULT NULL,
  `purchase_date` date DEFAULT NULL,
  `supplier_id` int(11) DEFAULT NULL,
  `cost_price` decimal(10,2) DEFAULT NULL,
  `status` enum('available','sold','reserved','damaged','returned','transferred') DEFAULT 'available',
  `location_id` int(11) DEFAULT NULL,
  `notes` text,
  `sold_date` datetime DEFAULT NULL,
  `sold_to_customer` varchar(255) DEFAULT NULL,
  `warranty_expires` date DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_by` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`serial_id`),
  UNIQUE KEY `idx_product_serial_unique` (`product_id`,`serial_number`),
  KEY `idx_product_serial` (`product_id`),
  KEY `idx_status` (`status`),
  KEY `idx_expiry_date` (`expiry_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `AAY6074_product_serial_numbers`
--


--
-- Table structure for table `AAY6074_product_units`
--

CREATE TABLE `AAY6074_product_units` (
  `unit_id` int(11) NOT NULL AUTO_INCREMENT,
  `unit_name` varchar(50) NOT NULL,
  `unit_symbol` varchar(10) NOT NULL,
  `unit_type` enum('weight','volume','length','quantity','time','area') NOT NULL,
  `base_unit_conversion` decimal(15,6) DEFAULT '1.000000',
  `is_decimal_allowed` tinyint(1) DEFAULT '1',
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`unit_id`),
  UNIQUE KEY `unique_unit_symbol` (`unit_symbol`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `AAY6074_product_units`
--

INSERT INTO `AAY6074_product_units` VALUES (1,'Piece','pcs','quantity',1.000000,1,1,'2025-07-31 07:11:56'),(2,'Kilogram','kg','weight',1.000000,1,1,'2025-07-31 07:11:56'),(3,'Gram','g','weight',1.000000,1,1,'2025-07-31 07:11:56'),(4,'Liter','L','volume',1.000000,1,1,'2025-07-31 07:11:56'),(5,'Milliliter','mL','volume',1.000000,1,1,'2025-07-31 07:11:56'),(6,'Meter','m','length',1.000000,1,1,'2025-07-31 07:11:56'),(7,'Centimeter','cm','length',1.000000,1,1,'2025-07-31 07:11:56'),(8,'Box','box','quantity',1.000000,1,1,'2025-07-31 07:11:56'),(9,'Pack','pack','quantity',1.000000,1,1,'2025-07-31 07:11:56'),(10,'Bottle','bottle','quantity',1.000000,1,1,'2025-07-31 07:11:56');

--
-- Table structure for table `AAY6074_product_variants`
--

CREATE TABLE `AAY6074_product_variants` (
  `variant_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `variant_name` varchar(100) NOT NULL,
  `variant_type` varchar(50) NOT NULL,
  `variant_code` varchar(50) DEFAULT NULL,
  `variant_description` text,
  `price_adjustment` decimal(10,2) DEFAULT '0.00',
  `cost_adjustment` decimal(10,2) DEFAULT '0.00',
  `stock_quantity` decimal(15,3) DEFAULT '0.000',
  `variant_image` varchar(500) DEFAULT NULL,
  `sort_order` int(11) DEFAULT '0',
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`variant_id`),
  KEY `idx_product_variant` (`product_id`,`variant_type`),
  KEY `idx_variant_code` (`variant_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `AAY6074_product_variants`
--


--
-- Table structure for table `AAY6074_products`
--

CREATE TABLE `AAY6074_products` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_code` varchar(50) DEFAULT NULL,
  `product_name` varchar(255) NOT NULL,
  `product_description` text,
  `short_description` varchar(500) DEFAULT NULL,
  `category_id` int(11) NOT NULL,
  `brand_id` int(11) DEFAULT NULL,
  `unit_id` int(11) NOT NULL,
  `sell_type` enum('unit','weight','combo','service') NOT NULL DEFAULT 'unit',
  `sell_price` decimal(12,2) NOT NULL DEFAULT '0.00',
  `cost_price` decimal(12,2) DEFAULT '0.00',
  `wholesale_price` decimal(12,2) DEFAULT '0.00',
  `tax_rate` decimal(5,2) DEFAULT '0.00',
  `discount_percentage` decimal(5,2) DEFAULT '0.00',
  `track_inventory` tinyint(1) DEFAULT '1',
  `current_stock` decimal(15,3) DEFAULT '0.000',
  `reserved_stock` decimal(15,3) DEFAULT '0.000',
  `reorder_level` decimal(15,3) DEFAULT '0.000',
  `reorder_quantity` decimal(15,3) DEFAULT '0.000',
  `max_stock_level` decimal(15,3) DEFAULT NULL,
  `image_path` varchar(500) DEFAULT NULL,
  `additional_images` json DEFAULT NULL,
  `product_specifications` json DEFAULT NULL,
  `is_favorite` tinyint(1) DEFAULT '0',
  `is_featured` tinyint(1) DEFAULT '0',
  `is_active` tinyint(1) DEFAULT '1',
  `allow_decimal_quantity` tinyint(1) DEFAULT '0',
  `has_variants` tinyint(1) DEFAULT '0',
  `has_combo` tinyint(1) DEFAULT '0',
  `has_serial_numbers` tinyint(1) DEFAULT '0',
  `has_expiry_date` tinyint(1) DEFAULT '0',
  `view_count` int(11) DEFAULT '0',
  `sale_count` int(11) DEFAULT '0',
  `rating_average` decimal(3,2) DEFAULT '0.00',
  `rating_count` int(11) DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_by` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`product_id`),
  UNIQUE KEY `unique_product_code` (`product_code`),
  KEY `idx_category` (`category_id`),
  KEY `idx_brand` (`brand_id`),
  KEY `idx_unit` (`unit_id`),
  KEY `idx_sell_type` (`sell_type`),
  KEY `idx_active_featured` (`is_active`,`is_featured`),
  KEY `idx_stock_alert` (`track_inventory`,`current_stock`,`reorder_level`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `AAY6074_products`
--

INSERT INTO `AAY6074_products` VALUES (1,NULL,'Tes',NULL,NULL,1,NULL,1,'unit',8.00,0.00,0.00,0.00,0.00,0,0.000,0.000,0.000,0.000,NULL,NULL,NULL,NULL,0,0,1,0,0,0,0,0,0,0,0.00,0,'2025-08-01 10:03:35','2025-08-01 10:03:35',NULL);

--
-- Table structure for table `AAY6074_quick_expense_types`
--

CREATE TABLE `AAY6074_quick_expense_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `icon` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT 'lightbulb',
  `category_id` int(11) NOT NULL,
  `vendor_id` int(11) DEFAULT NULL,
  `payment_method_id` int(11) NOT NULL,
  `payment_status` enum('PENDING','PAID','PARTIALLY_PAID','OVERDUE') COLLATE utf8mb4_unicode_ci DEFAULT 'PAID',
  `approval_status` enum('DRAFT','PENDING_APPROVAL','APPROVED','REJECTED') COLLATE utf8mb4_unicode_ci DEFAULT 'APPROVED',
  `common_amounts` json DEFAULT NULL,
  `usage_count` int(11) DEFAULT '0',
  `last_used_at` timestamp NULL DEFAULT NULL,
  `display_order` int(11) DEFAULT '0',
  `is_default` tinyint(1) DEFAULT '0',
  `is_active` tinyint(1) DEFAULT '1',
  `created_by` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_category` (`category_id`),
  KEY `idx_vendor` (`vendor_id`),
  KEY `idx_display_order` (`display_order`),
  KEY `idx_active` (`is_active`),
  KEY `idx_usage` (`usage_count`,`last_used_at`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAY6074_quick_expense_types`
--

INSERT INTO `AAY6074_quick_expense_types` VALUES (1,'Purchases','Delivery of Goods','?',13,NULL,1,'PAID','APPROVED','[100, 250, 500, 1000]',0,NULL,1,1,1,NULL,'2025-07-31 07:11:56','2025-07-31 07:11:56'),(2,'Supplies','Office Supplies','?',1,NULL,1,'PAID','APPROVED','[100, 250, 500, 1000]',0,NULL,2,1,1,NULL,'2025-07-31 07:11:56','2025-07-31 07:11:56'),(3,'Power Bills','Power Bills','?',14,NULL,1,'PAID','APPROVED','[1000, 2000, 3000, 5000]',0,NULL,3,1,1,NULL,'2025-07-31 07:11:56','2025-07-31 07:11:56'),(4,'Water Bills','Water Bills','?',14,NULL,1,'PAID','APPROVED','[1000, 2000, 3000, 5000]',0,NULL,4,1,1,NULL,'2025-07-31 07:11:56','2025-07-31 07:11:56'),(5,'Salaries','Salaries','?',15,NULL,1,'PAID','APPROVED','[500, 1000, 2000, 5000]',0,NULL,5,1,1,NULL,'2025-07-31 07:11:56','2025-07-31 07:11:56'),(6,'Internet Bills','Phone/Internet Bills','?',14,NULL,1,'PAID','APPROVED','[500, 1000, 1500, 2000]',0,NULL,6,1,1,NULL,'2025-07-31 07:11:56','2025-07-31 07:11:56');

--
-- Table structure for table `AAY6074_sales_cash_sessions`
--

CREATE TABLE `AAY6074_sales_cash_sessions` (
  `session_id` int(11) NOT NULL AUTO_INCREMENT,
  `cashier_id` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `cashier_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `opening_cash` decimal(15,2) NOT NULL DEFAULT '0.00',
  `closing_cash` decimal(15,2) DEFAULT NULL,
  `expected_cash` decimal(15,2) DEFAULT NULL,
  `cash_difference` decimal(15,2) DEFAULT NULL,
  `session_start` datetime NOT NULL,
  `session_end` datetime DEFAULT NULL,
  `session_status` enum('open','closed') COLLATE utf8mb4_unicode_ci DEFAULT 'open',
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`session_id`),
  KEY `idx_cashier_id` (`cashier_id`),
  KEY `idx_session_start` (`session_start`),
  KEY `idx_session_status` (`session_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAY6074_sales_cash_sessions`
--


--
-- Table structure for table `AAY6074_sales_daily_summary`
--

CREATE TABLE `AAY6074_sales_daily_summary` (
  `summary_id` int(11) NOT NULL AUTO_INCREMENT,
  `summary_date` date NOT NULL,
  `total_transactions` int(11) DEFAULT '0',
  `voided_transactions` int(11) DEFAULT '0',
  `returned_transactions` int(11) DEFAULT '0',
  `gross_sales` decimal(15,2) DEFAULT '0.00',
  `total_discounts` decimal(15,2) DEFAULT '0.00',
  `total_tax` decimal(15,2) DEFAULT '0.00',
  `net_sales` decimal(15,2) DEFAULT '0.00',
  `total_returns` decimal(15,2) DEFAULT '0.00',
  `total_voids` decimal(15,2) DEFAULT '0.00',
  `cash_sales` decimal(15,2) DEFAULT '0.00',
  `card_sales` decimal(15,2) DEFAULT '0.00',
  `digital_wallet_sales` decimal(15,2) DEFAULT '0.00',
  `credit_sales` decimal(15,2) DEFAULT '0.00',
  `other_payments` decimal(15,2) DEFAULT '0.00',
  `total_items_sold` int(11) DEFAULT '0',
  `total_weight_sold` decimal(10,3) DEFAULT '0.000',
  `hourly_sales` json DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`summary_id`),
  UNIQUE KEY `summary_date` (`summary_date`),
  KEY `idx_summary_date` (`summary_date`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAY6074_sales_daily_summary`
--

INSERT INTO `AAY6074_sales_daily_summary` VALUES (1,'2025-08-01',1,0,0,8.00,0.00,0.00,8.00,0.00,0.00,8.00,0.00,0.00,0.00,0.00,1,0.000,NULL,'2025-08-01 10:03:40','2025-08-01 10:03:40');

--
-- Table structure for table `AAY6074_sales_items`
--

CREATE TABLE `AAY6074_sales_items` (
  `item_id` int(11) NOT NULL AUTO_INCREMENT,
  `transaction_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `variant_id` int(11) DEFAULT NULL,
  `product_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `product_code` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `category_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sell_type` enum('unit','weight','combo','service') COLLATE utf8mb4_unicode_ci NOT NULL,
  `unit_symbol` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `quantity` decimal(10,3) NOT NULL,
  `weight` decimal(10,3) DEFAULT NULL,
  `unit_price` decimal(15,2) NOT NULL,
  `line_subtotal` decimal(15,2) NOT NULL,
  `line_discount_amount` decimal(15,2) DEFAULT '0.00',
  `line_total` decimal(15,2) NOT NULL,
  `is_returned` tinyint(1) DEFAULT '0',
  `returned_quantity` decimal(10,3) DEFAULT '0.000',
  `returned_weight` decimal(10,3) DEFAULT '0.000',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`item_id`),
  KEY `idx_product_id` (`product_id`),
  KEY `idx_transaction_id` (`transaction_id`),
  KEY `idx_variant_id` (`variant_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAY6074_sales_items`
--

INSERT INTO `AAY6074_sales_items` VALUES (1,1,1,NULL,'Tes',NULL,NULL,'unit',NULL,1.000,NULL,8.00,8.00,0.00,8.00,0,0.000,0.000,'2025-08-01 10:03:40');

--
-- Table structure for table `AAY6074_sales_payments`
--

CREATE TABLE `AAY6074_sales_payments` (
  `payment_id` int(11) NOT NULL AUTO_INCREMENT,
  `transaction_id` int(11) NOT NULL,
  `payment_method_id` int(11) NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `payment_date` datetime NOT NULL,
  `reference_number` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `card_last_four` varchar(4) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `approval_code` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`payment_id`),
  KEY `idx_transaction_id` (`transaction_id`),
  KEY `idx_payment_method_id` (`payment_method_id`),
  KEY `idx_payment_date` (`payment_date`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAY6074_sales_payments`
--

INSERT INTO `AAY6074_sales_payments` VALUES (1,1,1,8.00,'2025-08-01 12:03:40',NULL,NULL,NULL,'2025-08-01 10:03:40');

--
-- Table structure for table `AAY6074_sales_receipt_tokens`
--

CREATE TABLE `AAY6074_sales_receipt_tokens` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `transaction_id` int(11) NOT NULL,
  `token_hash` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `expires_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `used_at` timestamp NULL DEFAULT NULL,
  `is_valid` tinyint(1) DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `idx_transaction_id` (`transaction_id`),
  KEY `idx_token_hash` (`token_hash`),
  KEY `idx_expires_at` (`expires_at`),
  KEY `idx_is_valid` (`is_valid`),
  CONSTRAINT `AAY6074_sales_receipt_tokens_ibfk_1` FOREIGN KEY (`transaction_id`) REFERENCES `AAY6074_sales_transactions` (`transaction_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAY6074_sales_receipt_tokens`
--

INSERT INTO `AAY6074_sales_receipt_tokens` VALUES (1,1,'64f0090be3de2f78898ccdf718233cbb331b582e15c37475079bf036eed5d179','2025-08-07 01:42:52','2025-08-06 01:42:52',NULL,1);

--
-- Table structure for table `AAY6074_sales_return_items`
--

CREATE TABLE `AAY6074_sales_return_items` (
  `return_item_id` int(11) NOT NULL AUTO_INCREMENT,
  `return_id` int(11) NOT NULL,
  `original_item_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `product_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `quantity_returned` decimal(10,3) NOT NULL,
  `weight_returned` decimal(10,3) DEFAULT NULL,
  `unit_price` decimal(15,2) NOT NULL,
  `return_amount` decimal(15,2) NOT NULL,
  `restocked` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`return_item_id`),
  KEY `idx_return_id` (`return_id`),
  KEY `idx_product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAY6074_sales_return_items`
--


--
-- Table structure for table `AAY6074_sales_returns`
--

CREATE TABLE `AAY6074_sales_returns` (
  `return_id` int(11) NOT NULL AUTO_INCREMENT,
  `return_number` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `original_transaction_id` int(11) NOT NULL,
  `return_type` enum('full_void','partial_return','exchange','refund') COLLATE utf8mb4_unicode_ci NOT NULL,
  `total_return_amount` decimal(15,2) NOT NULL,
  `refund_method` enum('cash','original_payment','store_credit') COLLATE utf8mb4_unicode_ci DEFAULT 'cash',
  `reason` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `processed_by` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `processor_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `return_date` datetime NOT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`return_id`),
  UNIQUE KEY `return_number` (`return_number`),
  KEY `idx_return_number` (`return_number`),
  KEY `idx_original_transaction` (`original_transaction_id`),
  KEY `idx_return_date` (`return_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAY6074_sales_returns`
--


--
-- Table structure for table `AAY6074_sales_sequences`
--

CREATE TABLE `AAY6074_sales_sequences` (
  `sequence_type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `current_number` int(11) NOT NULL DEFAULT '0',
  `prefix` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT '',
  `suffix` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT '',
  `date_format` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT '',
  `reset_daily` tinyint(1) DEFAULT '0',
  `last_reset_date` date DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`sequence_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAY6074_sales_sequences`
--

INSERT INTO `AAY6074_sales_sequences` VALUES ('return',0,'RTN','','Ymd',1,NULL,'2025-07-31 07:11:56'),('transaction',1,'TXN','','Ymd',1,'2025-08-01','2025-08-01 10:03:40');

--
-- Table structure for table `AAY6074_sales_shared_receipts`
--

CREATE TABLE `AAY6074_sales_shared_receipts` (
  `shared_receipt_id` int(11) NOT NULL AUTO_INCREMENT,
  `transaction_id` int(11) NOT NULL,
  `transaction_number` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `shared_to` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Email, phone number, or platform identifier',
  `share_type` enum('email','sms','whatsapp','social','browser','copy','other') COLLATE utf8mb4_unicode_ci NOT NULL,
  `share_platform` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Specific platform name (WhatsApp, Facebook, etc.)',
  `share_status` enum('pending','sent','delivered','failed') COLLATE utf8mb4_unicode_ci DEFAULT 'pending',
  `share_content` text COLLATE utf8mb4_unicode_ci COMMENT 'Content of the shared message',
  `error_message` text COLLATE utf8mb4_unicode_ci COMMENT 'Error details if sharing failed',
  `shared_by` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Staff member who initiated the share',
  `shared_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `delivered_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`shared_receipt_id`),
  KEY `idx_transaction_id` (`transaction_id`),
  KEY `idx_transaction_number` (`transaction_number`),
  KEY `idx_share_type` (`share_type`),
  KEY `idx_share_status` (`share_status`),
  KEY `idx_shared_at` (`shared_at`),
  CONSTRAINT `AAY6074_sales_shared_receipts_ibfk_1` FOREIGN KEY (`transaction_id`) REFERENCES `AAY6074_sales_transactions` (`transaction_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Tracks all receipt sharing activities for audit and delivery status';

--
-- Dumping data for table `AAY6074_sales_shared_receipts`
--


--
-- Table structure for table `AAY6074_sales_transactions`
--

CREATE TABLE `AAY6074_sales_transactions` (
  `transaction_id` int(11) NOT NULL AUTO_INCREMENT,
  `transaction_number` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `customer_id` int(11) DEFAULT NULL,
  `cashier_id` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `cashier_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `shift_id` bigint(20) DEFAULT NULL,
  `subtotal` decimal(15,2) NOT NULL DEFAULT '0.00',
  `tax_rate` decimal(5,2) NOT NULL DEFAULT '0.00',
  `tax_amount` decimal(15,2) NOT NULL DEFAULT '0.00',
  `discount_type` enum('none','percentage','fixed') COLLATE utf8mb4_unicode_ci DEFAULT 'none',
  `discount_value` decimal(15,2) DEFAULT '0.00',
  `discount_amount` decimal(15,2) NOT NULL DEFAULT '0.00',
  `total_amount` decimal(15,2) NOT NULL,
  `amount_paid` decimal(15,2) NOT NULL DEFAULT '0.00',
  `change_amount` decimal(15,2) NOT NULL DEFAULT '0.00',
  `payment_status` enum('pending','paid','partially_paid','voided','returned','credit') COLLATE utf8mb4_unicode_ci DEFAULT 'pending',
  `transaction_type` enum('sale','return','void') COLLATE utf8mb4_unicode_ci DEFAULT 'sale',
  `transaction_date` datetime NOT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `receipt_printed` tinyint(1) DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`transaction_id`),
  UNIQUE KEY `transaction_number` (`transaction_number`),
  KEY `idx_transaction_number` (`transaction_number`),
  KEY `idx_transaction_date` (`transaction_date`),
  KEY `idx_cashier` (`cashier_id`),
  KEY `idx_payment_status` (`payment_status`),
  KEY `idx_shift` (`shift_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAY6074_sales_transactions`
--

INSERT INTO `AAY6074_sales_transactions` VALUES (1,'TXN202508010001',NULL,'AfrpyFPcjvb4YMDvjJkfX6X0UF83','Tbl Seven',NULL,8.00,0.00,0.00,'none',0.00,0.00,8.00,8.00,0.00,'paid','sale','2025-08-01 12:03:40',NULL,0,'2025-08-01 10:03:40','2025-08-01 10:03:40');

--
-- Table structure for table `AAY6074_stock_alerts`
--

CREATE TABLE `AAY6074_stock_alerts` (
  `alert_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `variant_id` int(11) DEFAULT NULL,
  `alert_type` enum('low_stock','out_of_stock','expiry_warning','expiry_critical') NOT NULL,
  `alert_message` text NOT NULL,
  `severity` enum('info','warning','critical') NOT NULL DEFAULT 'warning',
  `current_stock` decimal(15,3) DEFAULT NULL,
  `reorder_level` decimal(15,3) DEFAULT NULL,
  `expiry_date` date DEFAULT NULL,
  `is_acknowledged` tinyint(1) DEFAULT '0',
  `acknowledged_by` varchar(128) DEFAULT NULL,
  `acknowledged_at` timestamp NULL DEFAULT NULL,
  `is_resolved` tinyint(1) DEFAULT '0',
  `resolved_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`alert_id`),
  KEY `idx_product_alert` (`product_id`,`alert_type`),
  KEY `idx_severity_ack` (`severity`,`is_acknowledged`),
  KEY `idx_expiry_date` (`expiry_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `AAY6074_stock_alerts`
--


--
-- Table structure for table `AAY6074_stock_movements`
--

CREATE TABLE `AAY6074_stock_movements` (
  `movement_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `variant_id` int(11) DEFAULT NULL,
  `movement_type` enum('purchase','sale','adjustment','transfer','return','damaged','expired','initial','receipt','delivery') NOT NULL,
  `quantity_before` decimal(15,3) NOT NULL,
  `quantity_change` decimal(15,3) NOT NULL,
  `quantity_after` decimal(15,3) NOT NULL,
  `unit_cost` decimal(12,2) DEFAULT NULL,
  `total_value` decimal(15,2) DEFAULT NULL,
  `reference_type` enum('purchase_order','sale_invoice','adjustment','transfer','return','manual') DEFAULT 'manual',
  `reference_id` varchar(100) DEFAULT NULL,
  `reason` varchar(255) DEFAULT NULL,
  `notes` text,
  `movement_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` varchar(128) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`movement_id`),
  KEY `idx_product_movement` (`product_id`,`movement_type`),
  KEY `idx_movement_date` (`movement_date`),
  KEY `idx_reference` (`reference_type`,`reference_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `AAY6074_stock_movements`
--


--
-- Table structure for table `AAZ5063_business_settings`
--

CREATE TABLE `AAZ5063_business_settings` (
  `setting_id` int(11) NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text NOT NULL,
  `setting_type` enum('string','boolean','number','json') NOT NULL DEFAULT 'string',
  `setting_group` int(11) NOT NULL DEFAULT '1' COMMENT '1=POS Settings, 2=Receipt Settings, 3=Printer Settings, 4=Staff Settings, 5=Inventory Settings',
  `setting_title` varchar(200) NOT NULL,
  `setting_description` varchar(500) DEFAULT NULL,
  `setting_text` varchar(200) DEFAULT NULL COMMENT 'Placeholder text for inputs',
  `setting_order` int(11) NOT NULL DEFAULT '0',
  `is_required` tinyint(1) NOT NULL DEFAULT '0',
  `setting_options` text COMMENT 'JSON string for dropdown/radio options',
  `description` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`setting_id`),
  UNIQUE KEY `unique_setting_key` (`setting_key`),
  KEY `idx_setting_group` (`setting_group`),
  KEY `idx_setting_order` (`setting_order`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `AAZ5063_business_settings`
--

INSERT INTO `AAZ5063_business_settings` VALUES (1,'show_item_search_bar','true','boolean',1,'Show Item Search Bar','Display search bar in POS items view for quick product lookup','',1,0,NULL,NULL,'2025-07-31 06:05:05','2025-07-31 06:05:05'),(2,'show_item_quick_stats','false','boolean',1,'Show Item Quick Stats','Display quick statistics like stock levels in POS items view','',2,0,NULL,NULL,'2025-07-31 06:05:05','2025-07-31 06:05:05'),(3,'show_category_filter','false','boolean',1,'Show Category Filter','Display category filter in POS items view','',3,0,NULL,NULL,'2025-07-31 06:05:05','2025-07-31 06:05:05'),(4,'allow_fast_checkout','false','boolean',1,'Allow Fast Checkout','Perform fast Cash Sales checkout in one click.','',4,0,NULL,NULL,'2025-07-31 06:05:05','2025-07-31 06:05:05'),(5,'show_invoice_upon_checkout','true','boolean',1,'Show Invoice Upon Checkout','Allow display of receipt details upon completing checkout.','',5,0,NULL,NULL,'2025-07-31 06:05:05','2025-07-31 06:05:05'),(6,'require_cashier_shift','false','boolean',2,'Require Cashier Shift','Monitor cashier cash drawer balance and other transaction.','',1,0,NULL,NULL,'2025-07-31 06:05:05','2025-07-31 06:05:05'),(7,'auto_print_receipt','false','boolean',3,'Auto Print Receipt','Automatically print receipt after successful checkout','',1,0,NULL,NULL,'2025-07-31 06:05:05','2025-07-31 06:05:05'),(8,'business_logo_path','','string',3,'Business Logo Path','Path to business logo for receipt printing','',2,0,NULL,NULL,'2025-07-31 06:05:05','2025-07-31 06:05:05'),(9,'receipt_business_name','My Business','string',3,'Business Name','Business name shown on receipt (required)','',3,0,NULL,NULL,'2025-07-31 06:05:05','2025-07-31 06:05:05'),(10,'show_receipt_phone','true','boolean',3,'Show Receipt Phone','Display business phone number on receipt','',4,0,NULL,NULL,'2025-07-31 06:05:05','2025-07-31 06:05:05'),(11,'show_receipt_address','true','boolean',3,'Show Receipt Address','Display business address on receipt','',5,0,NULL,NULL,'2025-07-31 06:05:05','2025-07-31 06:05:05'),(12,'show_receipt_tax_id','true','boolean',3,'Show Receipt Tax ID','Display business tax identification number on receipt','',6,0,NULL,NULL,'2025-07-31 06:05:05','2025-07-31 06:05:05'),(13,'show_receipt_website','true','boolean',3,'Show Receipt Website','Display business website URL on receipt','',7,0,NULL,NULL,'2025-07-31 06:05:05','2025-07-31 06:05:05'),(14,'show_receipt_title','true','boolean',3,'Show Receipt Title','Display receipt title/header on receipt','',8,0,NULL,NULL,'2025-07-31 06:05:05','2025-07-31 06:05:05'),(15,'receipt_title_text','** Invoice **','string',3,'Receipt Title Text','Custom receipt title/header text','',9,0,NULL,NULL,'2025-07-31 06:05:05','2025-07-31 06:05:05'),(16,'show_cashier_name','true','boolean',3,'Show Cashier Name','Display cashier/staff name on receipt','',10,0,NULL,NULL,'2025-07-31 06:05:05','2025-07-31 06:05:05'),(17,'show_customer_name','true','boolean',3,'Show Customer Name','Display customer name on receipt when available','',11,0,NULL,NULL,'2025-07-31 06:05:05','2025-07-31 06:05:05'),(18,'show_customer_address','true','boolean',3,'Show Customer Address','Display customer address on receipt when available','',12,0,NULL,NULL,'2025-07-31 06:05:05','2025-07-31 06:05:05'),(19,'show_customer_tax_no','true','boolean',3,'Show Customer Tax Number','Display customer tax identification number when available','',13,0,NULL,NULL,'2025-07-31 06:05:05','2025-07-31 06:05:05'),(20,'receipt_price_inclusive','true','boolean',3,'Price Inclusive of Tax','Display note that prices are inclusive of applicable taxes','',14,0,NULL,NULL,'2025-07-31 06:05:05','2025-07-31 06:05:05'),(21,'show_total_item_count','true','boolean',3,'Show Total Item Count','Display total number of items in transaction','',15,0,NULL,NULL,'2025-07-31 06:05:05','2025-07-31 06:05:05'),(22,'show_change_amount','true','boolean',3,'Show Change Amount','Display change amount given to customer','',16,0,NULL,NULL,'2025-07-31 06:05:05','2025-07-31 06:05:05'),(23,'show_payment_details','true','boolean',3,'Show Payment Details','Display payment method and transaction details','',17,0,NULL,NULL,'2025-07-31 06:05:05','2025-07-31 06:05:05'),(24,'show_thank_you_note','true','boolean',3,'Show Thank You Note','Display thank you message at receipt footer','',18,0,NULL,NULL,'2025-07-31 06:05:05','2025-07-31 06:05:05'),(25,'thank_you_note_text','Thank You, Visit Again.','string',3,'Thank You Note Text','Custom thank you message for receipt footer','',19,0,NULL,NULL,'2025-07-31 06:05:05','2025-07-31 06:05:05'),(26,'receipt_order_items_by','name','json',3,'Order Items By','How to order items on receipt: by name or order added','',20,0,'{\"name\":\"Product Name\",\"order_added\":\"Order Added\"}',NULL,'2025-07-31 06:05:05','2025-07-31 06:05:05');

--
-- Table structure for table `AAZ5063_cashflow_transactions`
--

CREATE TABLE `AAZ5063_cashflow_transactions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `transaction_type` enum('income','expense') COLLATE utf8mb4_unicode_ci NOT NULL,
  `category_id` int(11) NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `transaction_date` datetime NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `reference_number` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `payment_method_id` int(11) NOT NULL,
  `receipt_image_path` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `linked_sales_transaction_id` int(11) DEFAULT NULL,
  `created_by` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_transaction_type` (`transaction_type`),
  KEY `idx_category_id` (`category_id`),
  KEY `idx_transaction_date` (`transaction_date`),
  KEY `idx_linked_sales` (`linked_sales_transaction_id`),
  KEY `idx_payment_method_id` (`payment_method_id`),
  CONSTRAINT `fk_cashflow_payment_method` FOREIGN KEY (`payment_method_id`) REFERENCES `AAZ5063_payment_method` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAZ5063_cashflow_transactions`
--


--
-- Table structure for table `AAZ5063_cashier_counts`
--

CREATE TABLE `AAZ5063_cashier_counts` (
  `count_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `shift_id` bigint(20) NOT NULL,
  `count_type` enum('opening','closing','mid_shift') COLLATE utf8mb4_unicode_ci NOT NULL,
  `total_amount` decimal(10,2) NOT NULL,
  `counted_at` datetime NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`count_id`),
  KEY `idx_shift_counts` (`shift_id`,`count_type`),
  CONSTRAINT `fk_count_shift` FOREIGN KEY (`shift_id`) REFERENCES `AAZ5063_cashier_shifts` (`shift_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAZ5063_cashier_counts`
--


--
-- Table structure for table `AAZ5063_cashier_denominations`
--

CREATE TABLE `AAZ5063_cashier_denominations` (
  `denomination_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `count_id` bigint(20) NOT NULL,
  `denomination_value` decimal(10,2) NOT NULL,
  `quantity` int(11) NOT NULL,
  `subtotal` decimal(10,2) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`denomination_id`),
  KEY `idx_count_denoms` (`count_id`),
  CONSTRAINT `fk_denomination_count` FOREIGN KEY (`count_id`) REFERENCES `AAZ5063_cashier_counts` (`count_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAZ5063_cashier_denominations`
--


--
-- Table structure for table `AAZ5063_cashier_shifts`
--

CREATE TABLE `AAZ5063_cashier_shifts` (
  `shift_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `cashier_id` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `shift_start` datetime NOT NULL,
  `shift_end` datetime DEFAULT NULL,
  `opening_amount` decimal(10,2) NOT NULL,
  `closing_amount` decimal(10,2) DEFAULT NULL,
  `expected_amount` decimal(10,2) DEFAULT NULL,
  `difference_amount` decimal(10,2) DEFAULT NULL,
  `status` enum('open','closed') COLLATE utf8mb4_unicode_ci DEFAULT 'open',
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`shift_id`),
  KEY `idx_cashier_status` (`cashier_id`,`status`),
  KEY `idx_shift_dates` (`shift_start`,`shift_end`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAZ5063_cashier_shifts`
--


--
-- Table structure for table `AAZ5063_cashier_transactions`
--

CREATE TABLE `AAZ5063_cashier_transactions` (
  `transaction_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `shift_id` bigint(20) NOT NULL,
  `transaction_type` enum('sale','refund','void','payout','cash_in','cash_out','expense') COLLATE utf8mb4_unicode_ci NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `reference_id` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `payment_method` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`transaction_id`),
  KEY `idx_shift_transactions` (`shift_id`,`transaction_type`),
  KEY `idx_reference` (`reference_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAZ5063_cashier_transactions`
--


--
-- Table structure for table `AAZ5063_customer_accounts`
--

CREATE TABLE `AAZ5063_customer_accounts` (
  `customer_id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_code` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Unique customer identifier within business',
  `customer_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `customer_phone` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `customer_email` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `customer_address` text COLLATE utf8mb4_unicode_ci,
  `customer_type` enum('individual','business') COLLATE utf8mb4_unicode_ci DEFAULT 'individual',
  `tax_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'TIN or business registration number',
  `credit_enabled` tinyint(1) DEFAULT '0' COMMENT 'Whether customer can purchase on credit',
  `customer_status` enum('active','inactive','suspended') COLLATE utf8mb4_unicode_ci DEFAULT 'active',
  `date_of_birth` date DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`customer_id`),
  UNIQUE KEY `uk_customer_code` (`customer_code`),
  UNIQUE KEY `uk_customer_phone` (`customer_phone`),
  KEY `idx_customer_name` (`customer_name`),
  KEY `idx_customer_status` (`customer_status`),
  KEY `idx_credit_enabled` (`credit_enabled`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAZ5063_customer_accounts`
--

INSERT INTO `AAZ5063_customer_accounts` VALUES (1,'CUST-AAZ5063-0001','Tes Ads','09332252222',NULL,NULL,'individual',NULL,1,'active',NULL,NULL,'2025-07-31 06:10:58','2025-07-31 06:10:58');

--
-- Table structure for table `AAZ5063_customer_credit_accounts`
--

CREATE TABLE `AAZ5063_customer_credit_accounts` (
  `credit_account_id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `credit_limit` decimal(15,2) DEFAULT '0.00' COMMENT 'Maximum credit limit',
  `current_balance` decimal(15,2) DEFAULT '0.00' COMMENT 'Current outstanding balance',
  `available_credit` decimal(15,2) DEFAULT '0.00' COMMENT 'Available credit (limit - balance)',
  `payment_terms` enum('cash','net_7','net_15','net_30','net_45','net_60') COLLATE utf8mb4_unicode_ci DEFAULT 'net_30',
  `interest_rate` decimal(5,2) DEFAULT '0.00' COMMENT 'Monthly interest rate percentage',
  `last_payment_date` date DEFAULT NULL,
  `last_purchase_date` date DEFAULT NULL,
  `overdue_amount` decimal(15,2) DEFAULT '0.00',
  `days_overdue` int(11) DEFAULT '0',
  `credit_status` enum('good','warning','overdue','suspended') COLLATE utf8mb4_unicode_ci DEFAULT 'good',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`credit_account_id`),
  UNIQUE KEY `uk_customer_credit` (`customer_id`),
  CONSTRAINT `AAZ5063_customer_credit_accounts_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `AAZ5063_customer_accounts` (`customer_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAZ5063_customer_credit_accounts`
--

INSERT INTO `AAZ5063_customer_credit_accounts` VALUES (1,1,1000.00,0.00,1000.00,'net_30',0.00,NULL,NULL,0.00,0,'good','2025-07-31 06:10:58','2025-07-31 06:10:58');

--
-- Temporary table structure for view `AAZ5063_customer_outstanding_view`
--

/*!50001 DROP VIEW IF EXISTS `AAZ5063_customer_outstanding_view`*/;
/*!50001 CREATE VIEW `AAZ5063_customer_outstanding_view` AS SELECT 
 1 AS `customer_id`,
 1 AS `customer_code`,
 1 AS `customer_name`,
 1 AS `customer_phone`,
 1 AS `current_balance`,
 1 AS `overdue_amount`,
 1 AS `days_overdue`,
 1 AS `credit_status`,
 1 AS `last_payment_date`,
 1 AS `next_due_date`*/;

--
-- Table structure for table `AAZ5063_customer_payment_history`
--

CREATE TABLE `AAZ5063_customer_payment_history` (
  `payment_id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `customer_transaction_id` int(11) NOT NULL,
  `payment_amount` decimal(15,2) NOT NULL,
  `payment_method_id` int(11) NOT NULL,
  `reference_number` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `payment_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `processed_by` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `bank_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `check_date` date DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`payment_id`),
  KEY `idx_customer_id` (`customer_id`),
  KEY `idx_payment_date` (`payment_date`),
  KEY `idx_payment_method_id` (`payment_method_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAZ5063_customer_payment_history`
--


--
-- Table structure for table `AAZ5063_customer_sequences`
--

CREATE TABLE `AAZ5063_customer_sequences` (
  `sequence_name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `current_value` int(11) NOT NULL DEFAULT '0',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`sequence_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAZ5063_customer_sequences`
--

INSERT INTO `AAZ5063_customer_sequences` VALUES ('customer_code',1,'2025-07-31 06:10:58');

--
-- Temporary table structure for view `AAZ5063_customer_summary_view`
--

/*!50001 DROP VIEW IF EXISTS `AAZ5063_customer_summary_view`*/;
/*!50001 CREATE VIEW `AAZ5063_customer_summary_view` AS SELECT 
 1 AS `customer_id`,
 1 AS `customer_code`,
 1 AS `customer_name`,
 1 AS `customer_phone`,
 1 AS `customer_email`,
 1 AS `customer_status`,
 1 AS `credit_enabled`,
 1 AS `credit_limit`,
 1 AS `current_balance`,
 1 AS `available_credit`,
 1 AS `overdue_amount`,
 1 AS `days_overdue`,
 1 AS `credit_status`,
 1 AS `last_purchase_date`,
 1 AS `last_payment_date`,
 1 AS `total_purchases`,
 1 AS `total_purchase_amount`,
 1 AS `customer_since`*/;

--
-- Table structure for table `AAZ5063_customer_transactions`
--

CREATE TABLE `AAZ5063_customer_transactions` (
  `customer_transaction_id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `sales_transaction_id` int(11) DEFAULT NULL COMMENT 'Link to sales transaction if applicable',
  `transaction_type` enum('sale','payment','credit_adjustment','interest_charge','fee') COLLATE utf8mb4_unicode_ci NOT NULL,
  `amount` decimal(15,2) NOT NULL COMMENT 'Transaction amount (positive for charges, negative for payments)',
  `balance_before` decimal(15,2) NOT NULL COMMENT 'Balance before this transaction',
  `balance_after` decimal(15,2) NOT NULL COMMENT 'Balance after this transaction',
  `payment_method_id` int(11) DEFAULT NULL,
  `reference_number` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Check number, transfer reference, etc.',
  `transaction_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `due_date` date DEFAULT NULL COMMENT 'Due date for credit sales',
  `notes` text COLLATE utf8mb4_unicode_ci,
  `processed_by` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Staff member who processed',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`customer_transaction_id`),
  KEY `idx_customer_id` (`customer_id`),
  KEY `idx_transaction_type` (`transaction_type`),
  KEY `idx_transaction_date` (`transaction_date`),
  KEY `idx_sales_transaction` (`sales_transaction_id`),
  KEY `idx_payment_method_id` (`payment_method_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAZ5063_customer_transactions`
--


--
-- Table structure for table `AAZ5063_denominations_config`
--

CREATE TABLE `AAZ5063_denominations_config` (
  `config_id` int(11) NOT NULL AUTO_INCREMENT,
  `denomination_value` decimal(10,2) NOT NULL,
  `currency_code` varchar(3) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'PHP',
  `currency_symbol` varchar(5) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '₱',
  `is_coin` tinyint(1) NOT NULL DEFAULT '0',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `display_order` int(11) NOT NULL DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`config_id`),
  UNIQUE KEY `idx_denom_value` (`denomination_value`),
  KEY `idx_active_order` (`is_active`,`display_order`),
  KEY `idx_coin_active` (`is_coin`,`is_active`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAZ5063_denominations_config`
--

INSERT INTO `AAZ5063_denominations_config` VALUES (1,0.01,'PHP','₱',1,1,1,'2025-07-31 06:05:09','2025-07-31 06:05:09'),(2,0.05,'PHP','₱',1,1,2,'2025-07-31 06:05:09','2025-07-31 06:05:09'),(3,0.10,'PHP','₱',1,1,3,'2025-07-31 06:05:09','2025-07-31 06:05:09'),(4,0.25,'PHP','₱',1,1,4,'2025-07-31 06:05:09','2025-07-31 06:05:09'),(5,1.00,'PHP','₱',1,1,5,'2025-07-31 06:05:09','2025-07-31 06:05:09'),(6,5.00,'PHP','₱',1,1,6,'2025-07-31 06:05:09','2025-07-31 06:05:09'),(7,10.00,'PHP','₱',1,1,7,'2025-07-31 06:05:09','2025-07-31 06:05:09'),(8,20.00,'PHP','₱',0,1,8,'2025-07-31 06:05:09','2025-07-31 06:05:09'),(9,50.00,'PHP','₱',0,1,9,'2025-07-31 06:05:09','2025-07-31 06:05:09'),(10,100.00,'PHP','₱',0,1,10,'2025-07-31 06:05:09','2025-07-31 06:05:09'),(11,200.00,'PHP','₱',0,1,11,'2025-07-31 06:05:09','2025-07-31 06:05:09'),(12,500.00,'PHP','₱',0,1,12,'2025-07-31 06:05:09','2025-07-31 06:05:09'),(13,1000.00,'PHP','₱',0,1,13,'2025-07-31 06:05:09','2025-07-31 06:05:09');

--
-- Table structure for table `AAZ5063_expenses_approvals`
--

CREATE TABLE `AAZ5063_expenses_approvals` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `expense_id` int(11) NOT NULL,
  `approver_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `approval_level` int(11) DEFAULT '1',
  `status` enum('PENDING','APPROVED','REJECTED') COLLATE utf8mb4_unicode_ci DEFAULT 'PENDING',
  `comments` text COLLATE utf8mb4_unicode_ci,
  `approved_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `expense_id` (`expense_id`),
  CONSTRAINT `AAZ5063_expenses_approvals_ibfk_1` FOREIGN KEY (`expense_id`) REFERENCES `AAZ5063_expenses_transactions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAZ5063_expenses_approvals`
--


--
-- Table structure for table `AAZ5063_expenses_categories`
--

CREATE TABLE `AAZ5063_expenses_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `category_code` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `parent_category_id` int(11) DEFAULT NULL,
  `is_system_category` tinyint(1) DEFAULT '0',
  `requires_approval` tinyint(1) DEFAULT '0',
  `approval_limit` decimal(15,2) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `category_code` (`category_code`),
  KEY `fk_parent_category` (`parent_category_id`),
  CONSTRAINT `fk_parent_category` FOREIGN KEY (`parent_category_id`) REFERENCES `AAZ5063_expenses_categories` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAZ5063_expenses_categories`
--

INSERT INTO `AAZ5063_expenses_categories` VALUES (1,'Supplies and Office Materials','SUPPLIES','Stationery, cleaning supplies, packaging materials, and minor operational items.',NULL,1,0,0.00,1,'2025-07-31 06:04:55','2025-07-31 06:04:55'),(2,'Rent and Lease','RENT','Expenses for renting or leasing storefronts, offices, or storage spaces.',NULL,1,0,0.00,1,'2025-07-31 06:04:55','2025-07-31 06:04:55'),(3,'Marketing and Advertising','MARKETING','Promotional activities, social media marketing, printing, and signage.',NULL,1,0,0.00,1,'2025-07-31 06:04:55','2025-07-31 06:04:55'),(4,'Professional Services','PROFESSIONAL','Fees for legal, accounting, consulting, or other professional services.',NULL,1,0,0.00,1,'2025-07-31 06:04:55','2025-07-31 06:04:55'),(5,'Technology and Software','TECHNOLOGY','Expenses for POS software, business management tools, and IT support.',NULL,1,0,0.00,1,'2025-07-31 06:04:55','2025-07-31 06:04:55'),(6,'Transportation and Delivery','TRANSPORTATION','Costs for vehicle expenses, fuel, courier, or delivery services.',NULL,1,0,0.00,1,'2025-07-31 06:04:55','2025-07-31 06:04:55'),(7,'Insurance','INSURANCE','Business, property, liability, and worker\'s compensation insurance premiums.',NULL,1,0,0.00,1,'2025-07-31 06:04:55','2025-07-31 06:04:55'),(8,'Training and Development','TRAINING','Employee training programs, workshops, and skill development.',NULL,1,0,0.00,1,'2025-07-31 06:04:55','2025-07-31 06:04:55'),(9,'Equipment and Maintenance','MAINTENANCE','Purchase or repair of equipment, machinery, kitchen appliances, or POS hardware.',NULL,1,0,0.00,1,'2025-07-31 06:04:55','2025-07-31 06:04:55'),(10,'Banking and Financial Charges','BANK_FEES','Bank fees, interest on loans, credit card processing fees.',NULL,1,0,0.00,1,'2025-07-31 06:04:55','2025-07-31 06:04:55'),(11,'Licenses and Permits','PERMITS','Fees paid to government authorities for operational licenses and permits.',NULL,1,0,0.00,1,'2025-07-31 06:04:55','2025-07-31 06:04:55'),(12,'Payroll Taxes and Benefits','BENEFITS','Employer contributions to social security, health insurance, retirement plans, and other benefits.',NULL,1,0,0.00,1,'2025-07-31 06:04:55','2025-07-31 06:04:55'),(13,'Inventory Purchases','INVENTORY','Cost of goods bought for resale or use in operations, including wholesale stock.',NULL,1,0,0.00,1,'2025-07-31 06:04:55','2025-07-31 06:04:55'),(14,'Utilities','UTILITIES','Costs for electricity, water, gas, internet, and phone services.',NULL,1,0,0.00,1,'2025-07-31 06:04:55','2025-07-31 06:04:55'),(15,'Salaries and Wages','SALARIES','Payments to employees, including wages, salaries, and overtime.',NULL,1,0,0.00,1,'2025-07-31 06:04:55','2025-07-31 06:04:55'),(16,'Food and Beverage Costs','FB_COST','Expenses for purchasing ingredients, beverages, and related consumables.',NULL,1,0,0.00,1,'2025-07-31 06:04:55','2025-07-31 06:04:55'),(17,'Depreciation and Amortization','DEPRECIATION','Gradual expense recognition for tangible and intangible assets.',NULL,1,0,0.00,1,'2025-07-31 06:04:55','2025-07-31 06:04:55'),(18,'Miscellaneous','MISCELLANEOUS','Unclassified or irregular expenses that don\'t fit into other categories.',NULL,1,0,0.00,1,'2025-07-31 06:04:55','2025-07-31 06:04:55');

--
-- Table structure for table `AAZ5063_expenses_line_items`
--

CREATE TABLE `AAZ5063_expenses_line_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `expense_id` int(11) NOT NULL,
  `item_description` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `quantity` decimal(10,3) DEFAULT '1.000',
  `unit_price` decimal(15,2) NOT NULL,
  `line_total` decimal(15,2) NOT NULL,
  `tax_rate` decimal(5,2) DEFAULT '0.00',
  `tax_amount` decimal(15,2) DEFAULT '0.00',
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_expenses_line_items_expense` (`expense_id`),
  CONSTRAINT `AAZ5063_expenses_line_items_ibfk_1` FOREIGN KEY (`expense_id`) REFERENCES `AAZ5063_expenses_transactions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAZ5063_expenses_line_items`
--


--
-- Table structure for table `AAZ5063_expenses_payments`
--

CREATE TABLE `AAZ5063_expenses_payments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `expense_id` int(11) NOT NULL,
  `payment_amount` decimal(15,2) NOT NULL,
  `payment_date` date NOT NULL,
  `payment_method_id` int(11) NOT NULL,
  `reference_number` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_by` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_payment_method_id` (`payment_method_id`),
  KEY `idx_expenses_payments_expense` (`expense_id`),
  CONSTRAINT `AAZ5063_expenses_payments_ibfk_1` FOREIGN KEY (`expense_id`) REFERENCES `AAZ5063_expenses_transactions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `AAZ5063_expenses_payments_ibfk_2` FOREIGN KEY (`payment_method_id`) REFERENCES `AAZ5063_payment_method` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAZ5063_expenses_payments`
--


--
-- Table structure for table `AAZ5063_expenses_recurring`
--

CREATE TABLE `AAZ5063_expenses_recurring` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `template_expense_id` int(11) NOT NULL,
  `next_due_date` date NOT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  `last_generated_date` date DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `template_expense_id` (`template_expense_id`),
  CONSTRAINT `AAZ5063_expenses_recurring_ibfk_1` FOREIGN KEY (`template_expense_id`) REFERENCES `AAZ5063_expenses_transactions` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAZ5063_expenses_recurring`
--


--
-- Table structure for table `AAZ5063_expenses_transactions`
--

CREATE TABLE `AAZ5063_expenses_transactions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `expense_number` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `category_id` int(11) NOT NULL,
  `vendor_id` int(11) DEFAULT NULL,
  `shift_id` int(11) DEFAULT NULL,
  `expense_date` datetime NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `tax_amount` decimal(15,2) DEFAULT '0.00',
  `total_amount` decimal(15,2) NOT NULL,
  `currency_code` varchar(3) COLLATE utf8mb4_unicode_ci DEFAULT 'PHP',
  `exchange_rate` decimal(10,4) DEFAULT '1.0000',
  `description` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `reference_number` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `payment_method_id` int(11) NOT NULL,
  `payment_status` enum('PENDING','PAID','PARTIALLY_PAID','OVERDUE','CANCELLED') COLLATE utf8mb4_unicode_ci DEFAULT 'PENDING',
  `approval_status` enum('DRAFT','PENDING_APPROVAL','APPROVED','REJECTED') COLLATE utf8mb4_unicode_ci DEFAULT 'DRAFT',
  `recurring_type` enum('NONE','DAILY','WEEKLY','MONTHLY','QUARTERLY','YEARLY') COLLATE utf8mb4_unicode_ci DEFAULT 'NONE',
  `recurring_interval` int(11) DEFAULT '1',
  `recurring_until` date DEFAULT NULL,
  `parent_expense_id` int(11) DEFAULT NULL,
  `receipt_attachment` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_by` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `approved_by` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `approved_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `expense_number` (`expense_number`),
  KEY `idx_shift_id` (`shift_id`),
  KEY `idx_payment_method_id` (`payment_method_id`),
  KEY `idx_expenses_transactions_date` (`expense_date`),
  KEY `idx_expenses_transactions_status` (`payment_status`,`approval_status`),
  KEY `idx_expenses_transactions_vendor` (`vendor_id`),
  KEY `idx_expenses_transactions_category` (`category_id`),
  KEY `fk_parent_expense` (`parent_expense_id`),
  CONSTRAINT `fk_expense_category` FOREIGN KEY (`category_id`) REFERENCES `AAZ5063_expenses_categories` (`id`),
  CONSTRAINT `fk_expense_vendor` FOREIGN KEY (`vendor_id`) REFERENCES `AAZ5063_expenses_vendors` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_parent_expense` FOREIGN KEY (`parent_expense_id`) REFERENCES `AAZ5063_expenses_transactions` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAZ5063_expenses_transactions`
--


--
-- Table structure for table `AAZ5063_expenses_vendors`
--

CREATE TABLE `AAZ5063_expenses_vendors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `vendor_code` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `vendor_name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `contact_person` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `phone` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `address` text COLLATE utf8mb4_unicode_ci,
  `tax_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `payment_terms` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `currency_code` varchar(3) COLLATE utf8mb4_unicode_ci DEFAULT 'PHP',
  `is_active` tinyint(1) DEFAULT '1',
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `vendor_code` (`vendor_code`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `AAZ5063_expenses_vendors`
--

INSERT INTO `AAZ5063_expenses_vendors` VALUES (1,'VENDOR001','General Supplier','Contact Person','contact@supplier.com','+1234567890','123 Supplier St, City','TAX123456','Net 30','PHP',1,NULL,'2025-07-31 06:04:55','2025-07-31 06:04:55'),(2,'VENDOR002','Office Supplies Co.','Office Manager','office@supplies.com','+1234567891','456 Office Ave, City','TAX123457','Net 15','PHP',1,NULL,'2025-07-31 06:04:55','2025-07-31 06:04:55'),(3,'VENDOR003','Utility Company','Billing Dept','billing@utility.com','+1234567892','789 Utility Blvd, City','TAX123458','Due on receipt','PHP',1,NULL,'2025-07-31 06:04:55','2025-07-31 06:04:55'),(4,'VENDOR004','Marketing Agency','Account Manager','accounts@marketing.com','+1234567893','321 Marketing Rd, City','TAX123459','Net 30','PHP',1,NULL,'2025-07-31 06:04:55','2025-07-31 06:04:55'),(5,'VENDOR005','Maintenance Services','Service Manager','service@maintenance.com','+1234567894','654 Service Ln, City','TAX123460','Net 15','PHP',1,NULL,'2025-07-31 06:04:55','2025-07-31 06:04:55');

--
-- Table structure for table `AAZ5063_payment_method`
--

CREATE TABLE `AAZ5063_payment_method` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `method_code` varchar(50) NOT NULL,
  `method_name` varchar(100) NOT NULL,
  `account_number` varchar(100) DEFAULT NULL COMMENT 'Account number, card number, or other identifier',
  `description` text,
  `is_system` tinyint(1) NOT NULL DEFAULT '0' COMMENT '1 for system methods (CASH, CREDIT), 0 for user methods',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `display_order` int(11) DEFAULT '0',
  `requires_reference` tinyint(1) DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_method_code_account` (`method_code`,`account_number`),
  KEY `idx_method_code` (`method_code`),
  KEY `idx_is_system` (`is_system`),
  KEY `idx_is_active` (`is_active`),
  KEY `idx_display_order` (`display_order`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `AAZ5063_payment_method`
--

INSERT INTO `AAZ5063_payment_method` VALUES (1,'CASH','Cash',NULL,NULL,1,1,1,0,'2025-07-31 06:04:55','2025-07-31 06:04:55'),(2,'CREDIT','Credit',NULL,NULL,1,1,2,0,'2025-07-31 06:04:55','2025-07-31 06:04:55'),(3,'DEBIT_CARD','Debit Card',NULL,NULL,0,0,0,0,'2025-07-31 06:04:55','2025-07-31 08:14:32'),(4,'CREDIT_CARD','Credit Card',NULL,NULL,0,0,0,0,'2025-07-31 06:04:55','2025-07-31 08:14:38'),(5,'DIGITAL_WALLET','Digital Wallet',NULL,NULL,0,0,0,0,'2025-07-31 06:04:55','2025-07-31 08:14:41'),(6,'BANK_TRANSFER','Bank Transfer',NULL,NULL,0,0,0,0,'2025-07-31 06:04:55','2025-07-31 08:14:49'),(7,'CHECK','Check',NULL,NULL,0,0,0,0,'2025-07-31 06:04:55','2025-07-31 08:14:52'),(8,'DIGITAL_WALLET','GCash','09776506236',NULL,0,1,0,0,'2025-07-31 06:58:13','2025-07-31 07:00:37'),(11,'DIGITAL_WALLET','Maya','09393896326',NULL,0,1,0,0,'2025-07-31 07:01:43','2025-07-31 07:01:43'),(12,'BANK_TRANSFER','BPI','1722267882',NULL,0,1,0,0,'2025-07-31 07:02:50','2025-07-31 07:02:50');

--
-- Table structure for table `AAZ5063_product_barcodes`
--

CREATE TABLE `AAZ5063_product_barcodes` (
  `barcode_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `variant_id` int(11) DEFAULT NULL,
  `barcode` varchar(255) NOT NULL,
  `barcode_type` enum('UPC','EAN8','EAN13','CODE128','CODE39','QR','CUSTOM') NOT NULL DEFAULT 'EAN13',
  `is_primary` tinyint(1) DEFAULT '0',
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`barcode_id`),
  UNIQUE KEY `unique_barcode` (`barcode`),
  KEY `idx_product_barcode` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `AAZ5063_product_barcodes`
--


--
-- Table structure for table `AAZ5063_product_brands`
--

CREATE TABLE `AAZ5063_product_brands` (
  `brand_id` int(11) NOT NULL AUTO_INCREMENT,
  `brand_name` varchar(100) NOT NULL,
  `brand_description` text,
  `brand_logo` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`brand_id`),
  UNIQUE KEY `unique_brand_name` (`brand_name`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `AAZ5063_product_brands`
--

INSERT INTO `AAZ5063_product_brands` VALUES (1,'No Brand','Default brand for products without specific brand',NULL,1,'2025-07-31 06:04:55','2025-07-31 06:04:55');

--
-- Table structure for table `AAZ5063_product_categories`
--

CREATE TABLE `AAZ5063_product_categories` (
  `category_id` int(11) NOT NULL AUTO_INCREMENT,
  `category_name` varchar(100) NOT NULL,
  `category_description` text,
  `parent_category_id` int(11) DEFAULT NULL,
  `category_color` varchar(7) DEFAULT '#2196F3',
  `category_icon` varchar(50) DEFAULT 'category',
  `sort_order` int(11) DEFAULT '0',
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`category_id`),
  KEY `idx_parent_category` (`parent_category_id`),
  KEY `idx_active_sort` (`is_active`,`sort_order`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `AAZ5063_product_categories`
--

INSERT INTO `AAZ5063_product_categories` VALUES (1,'General','Default category for products',NULL,'#2196F3','category',0,1,'2025-07-31 06:04:55','2025-07-31 06:04:55');

--
-- Table structure for table `AAZ5063_product_combos`
--

CREATE TABLE `AAZ5063_product_combos` (
  `combo_id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_product_id` int(11) NOT NULL,
  `component_product_id` int(11) NOT NULL,
  `component_variant_id` int(11) DEFAULT NULL,
  `quantity_required` decimal(10,3) NOT NULL,
  `is_optional` tinyint(1) DEFAULT '0',
  `option_group` varchar(50) DEFAULT NULL,
  `max_selections` int(11) DEFAULT '1',
  `sort_order` int(11) DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`combo_id`),
  KEY `idx_parent_product` (`parent_product_id`),
  KEY `idx_component_product` (`component_product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `AAZ5063_product_combos`
--


--
-- Table structure for table `AAZ5063_product_extras`
--

CREATE TABLE `AAZ5063_product_extras` (
  `extra_id` int(11) NOT NULL AUTO_INCREMENT,
  `extra_name` varchar(255) NOT NULL,
  `extra_description` text,
  `extra_type` enum('addon','service','charge','discount') NOT NULL DEFAULT 'addon',
  `extra_price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `is_percentage` tinyint(1) DEFAULT '0',
  `applicable_categories` json DEFAULT NULL,
  `applicable_products` json DEFAULT NULL,
  `min_quantity` decimal(10,3) DEFAULT '1.000',
  `max_quantity` decimal(10,3) DEFAULT NULL,
  `is_required` tinyint(1) DEFAULT '0',
  `requires_input` tinyint(1) DEFAULT '0',
  `is_active` tinyint(1) DEFAULT '1',
  `sort_order` int(11) DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`extra_id`),
  KEY `idx_extra_type` (`extra_type`),
  KEY `idx_active_sort` (`is_active`,`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `AAZ5063_product_extras`
--


--
-- Table structure for table `AAZ5063_product_serial_numbers`
--

CREATE TABLE `AAZ5063_product_serial_numbers` (
  `serial_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `variant_id` int(11) DEFAULT NULL,
  `serial_number` varchar(100) NOT NULL,
  `batch_lot_number` varchar(50) DEFAULT NULL,
  `manufacturing_date` date DEFAULT NULL,
  `expiry_date` date DEFAULT NULL,
  `purchase_date` date DEFAULT NULL,
  `supplier_id` int(11) DEFAULT NULL,
  `cost_price` decimal(10,2) DEFAULT NULL,
  `status` enum('available','sold','reserved','damaged','returned','transferred') DEFAULT 'available',
  `location_id` int(11) DEFAULT NULL,
  `notes` text,
  `sold_date` datetime DEFAULT NULL,
  `sold_to_customer` varchar(255) DEFAULT NULL,
  `warranty_expires` date DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_by` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`serial_id`),
  UNIQUE KEY `idx_product_serial_unique` (`product_id`,`serial_number`),
  KEY `idx_product_serial` (`product_id`),
  KEY `idx_status` (`status`),
  KEY `idx_expiry_date` (`expiry_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `AAZ5063_product_serial_numbers`
--


--
-- Table structure for table `AAZ5063_product_units`
--

CREATE TABLE `AAZ5063_product_units` (
  `unit_id` int(11) NOT NULL AUTO_INCREMENT,
  `unit_name` varchar(50) NOT NULL,
  `unit_symbol` varchar(10) NOT NULL,
  `unit_type` enum('weight','volume','length','quantity','time','area') NOT NULL,
  `base_unit_conversion` decimal(15,6) DEFAULT '1.000000',
  `is_decimal_allowed` tinyint(1) DEFAULT '1',
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`unit_id`),
  UNIQUE KEY `unique_unit_symbol` (`unit_symbol`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `AAZ5063_product_units`
--

INSERT INTO `AAZ5063_product_units` VALUES (1,'Piece','pcs','quantity',1.000000,1,1,'2025-07-31 06:04:55'),(2,'Kilogram','kg','weight',1.000000,1,1,'2025-07-31 06:04:55'),(3,'Gram','g','weight',1.000000,1,1,'2025-07-31 06:04:55'),(4,'Liter','L','volume',1.000000,1,1,'2025-07-31 06:04:55'),(5,'Milliliter','mL','volume',1.000000,1,1,'2025-07-31 06:04:55'),(6,'Meter','m','length',1.000000,1,1,'2025-07-31 06:04:55'),(7,'Centimeter','cm','length',1.000000,1,1,'2025-07-31 06:04:55'),(8,'Box','box','quantity',1.000000,1,1,'2025-07-31 06:04:55'),(9,'Pack','pack','quantity',1.000000,1,1,'2025-07-31 06:04:55'),(10,'Bottle','bottle','quantity',1.000000,1,1,'2025-07-31 06:04:55');

--
-- Table structure for table `AAZ5063_product_variants`
--

CREATE TABLE `AAZ5063_product_variants` (
  `variant_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `variant_name` varchar(100) NOT NULL,
  `variant_type` varchar(50) NOT NULL,
  `variant_code` varchar(50) DEFAULT NULL,
  `variant_description` text,
  `price_adjustment` decimal(10,2) DEFAULT '0.00',
  `cost_adjustment` decimal(10,2) DEFAULT '0.00',
  `stock_quantity` decimal(15,3) DEFAULT '0.000',
  `variant_image` varchar(500) DEFAULT NULL,
  `sort_order` int(11) DEFAULT '0',
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`variant_id`),
  KEY `idx_product_variant` (`product_id`,`variant_type`),
  KEY `idx_variant_code` (`variant_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `AAZ5063_product_variants`
--


--
-- Table structure for table `AAZ5063_products`
--

CREATE TABLE `AAZ5063_products` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_code` varchar(50) DEFAULT NULL,
  `product_name` varchar(255) NOT NULL,
  `product_description` text,
  `short_description` varchar(500) DEFAULT NULL,
  `category_id` int(11) NOT NULL,
  `brand_id` int(11) DEFAULT NULL,
  `unit_id` int(11) NOT NULL,
  `sell_type` enum('unit','weight','combo','service') NOT NULL DEFAULT 'unit',
  `sell_price` decimal(12,2) NOT NULL DEFAULT '0.00',
  `cost_price` decimal(12,2) DEFAULT '0.00',
  `wholesale_price` decimal(12,2) DEFAULT '0.00',
  `tax_rate` decimal(5,2) DEFAULT '0.00',
  `discount_percentage` decimal(5,2) DEFAULT '0.00',
  `track_inventory` tinyint(1) DEFAULT '1',
  `current_stock` decimal(15,3) DEFAULT '0.000',
  `reserved_stock` decimal(15,3) DEFAULT '0.000',
  `reorder_level` decimal(15,3) DEFAULT '0.000',
  `reorder_quantity` decimal(15,3) DEFAULT '0.000',
  `max_stock_level` decimal(15,3) DEFAULT NULL,
  `image_path` varchar(500) DEFAULT NULL,
  `additional_images` json DEFAULT NULL,
  `product_specifications` json DEFAULT NULL,
  `is_favorite` tinyint(1) DEFAULT '0',
  `is_featured` tinyint(1) DEFAULT '0',
  `is_active` tinyint(1) DEFAULT '1',
  `allow_decimal_quantity` tinyint(1) DEFAULT '0',
  `has_variants` tinyint(1) DEFAULT '0',
  `has_combo` tinyint(1) DEFAULT '0',
  `has_serial_numbers` tinyint(1) DEFAULT '0',
  `has_expiry_date` tinyint(1) DEFAULT '0',
  `view_count` int(11) DEFAULT '0',
  `sale_count` int(11) DEFAULT '0',
  `rating_average` decimal(3,2) DEFAULT '0.00',
  `rating_count` int(11) DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_by` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`product_id`),
  UNIQUE KEY `unique_product_code` (`product_code`),
  KEY `idx_category` (`category_id`),
  KEY `idx_brand` (`brand_id`),
  KEY `idx_unit` (`unit_id`),
  KEY `idx_sell_type` (`sell_type`),
  KEY `idx_active_featured` (`is_active`,`is_featured`),
  KEY `idx_stock_alert` (`track_inventory`,`current_stock`,`reorder_level`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `AAZ5063_products`
--

INSERT INTO `AAZ5063_products` VALUES (1,NULL,'Test',NULL,NULL,1,NULL,1,'unit',1.00,0.00,0.00,0.00,0.00,0,0.000,0.000,0.000,0.000,NULL,NULL,NULL,NULL,0,0,1,0,0,0,0,0,0,0,0.00,0,'2025-07-31 06:09:42','2025-07-31 06:09:42',NULL);

--
-- Table structure for table `AAZ5063_quick_expense_types`
--

CREATE TABLE `AAZ5063_quick_expense_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `icon` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT 'lightbulb',
  `category_id` int(11) NOT NULL,
  `vendor_id` int(11) DEFAULT NULL,
  `payment_method_id` int(11) NOT NULL,
  `payment_status` enum('PENDING','PAID','PARTIALLY_PAID','OVERDUE') COLLATE utf8mb4_unicode_ci DEFAULT 'PAID',
  `approval_status` enum('DRAFT','PENDING_APPROVAL','APPROVED','REJECTED') COLLATE utf8mb4_unicode_ci DEFAULT 'APPROVED',
  `common_amounts` json DEFAULT NULL,
  `usage_count` int(11) DEFAULT '0',
  `last_used_at` timestamp NULL DEFAULT NULL,
  `display_order` int(11) DEFAULT '0',
  `is_default` tinyint(1) DEFAULT '0',
  `is_active` tinyint(1) DEFAULT '1',
  `created_by` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_category` (`category_id`),
  KEY `idx_vendor` (`vendor_id`),
  KEY `idx_display_order` (`display_order`),
  KEY `idx_active` (`is_active`),
  KEY `idx_usage` (`usage_count`,`last_used_at`),
  KEY `fk_quick_expense_payment_method` (`payment_method_id`),
  CONSTRAINT `fk_quick_expense_category` FOREIGN KEY (`category_id`) REFERENCES `AAZ5063_expenses_categories` (`id`),
  CONSTRAINT `fk_quick_expense_payment_method` FOREIGN KEY (`payment_method_id`) REFERENCES `AAZ5063_payment_method` (`id`),
  CONSTRAINT `fk_quick_expense_vendor` FOREIGN KEY (`vendor_id`) REFERENCES `AAZ5063_expenses_vendors` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
