--
-- Host: 127.0.0.1    Database: CCW
-- ------------------------------------------------------


--
-- Current Database: `CCW`
--

/*!40000 DROP DATABASE IF EXISTS `CCW`*/;

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `CCW` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

USE `CCW`;

--
-- Table structure for table `audit_logs`
--

CREATE TABLE `audit_logs` (
  `audit_log_id` int NOT NULL AUTO_INCREMENT,
  `module_key` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  `action_key` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  `entity_type` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  `entity_id` int DEFAULT NULL,
  `order_id` int DEFAULT NULL,
  `payment_id` int DEFAULT NULL,
  `customer_id` int DEFAULT NULL,
  `car_id` int DEFAULT NULL,
  `actor_employee_id` int DEFAULT NULL,
  `actor_name` varchar(120) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `branch_name` varchar(120) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `bill_number` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `amount` decimal(10,2) DEFAULT NULL,
  `message` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `before_json` longtext COLLATE utf8mb4_unicode_ci,
  `after_json` longtext COLLATE utf8mb4_unicode_ci,
  `meta_json` longtext COLLATE utf8mb4_unicode_ci,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`audit_log_id`),
  KEY `idx_audit_logs_created_at` (`created_at`),
  KEY `idx_audit_logs_module_key` (`module_key`),
  KEY `idx_audit_logs_action_key` (`action_key`),
  KEY `idx_audit_logs_order_id` (`order_id`),
  KEY `idx_audit_logs_payment_id` (`payment_id`),
  KEY `idx_audit_logs_actor_employee_id` (`actor_employee_id`),
  KEY `idx_audit_logs_branch_name` (`branch_name`),
  KEY `idx_audit_logs_bill_number` (`bill_number`),
  KEY `idx_audit_logs_report` (`created_at`,`module_key`,`action_key`),
  KEY `idx_audit_logs_branch_created` (`branch_name`,`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `audit_logs`
--

INSERT INTO `audit_logs` VALUES (1,'service','delete','service',8,NULL,NULL,NULL,NULL,2,'System Administrator',NULL,NULL,NULL,'Service deleted.','{\"service_name\":\"AC System Ozone\",\"service_type\":\"sub_service\",\"category_id\":1,\"main_service_id\":0,\"group_id\":0,\"package_vehicle_scope\":\"member\",\"is_quick_service\":0,\"price\":500,\"description\":\"Ozone treatment for air conditioning and cabin odor.\",\"is_active\":0,\"package_items\":[]}',NULL,NULL,'2026-05-08 23:27:02'),(2,'service','delete','service',6,NULL,NULL,NULL,NULL,2,'System Administrator',NULL,NULL,NULL,'Service deleted.','{\"service_name\":\"Paint Protection Film\",\"service_type\":\"sub_service\",\"category_id\":4,\"main_service_id\":11,\"group_id\":0,\"package_vehicle_scope\":\"member\",\"is_quick_service\":0,\"price\":45000,\"description\":\"Full body PPF installation and finishing inspection.\",\"is_active\":0,\"package_items\":[]}',NULL,NULL,'2026-05-08 23:27:29'),(3,'service','delete','service',5,NULL,NULL,NULL,NULL,2,'System Administrator',NULL,NULL,NULL,'Service deleted.','{\"service_name\":\"Ceramic Pro Gold Package\",\"service_type\":\"sub_service\",\"category_id\":4,\"main_service_id\":11,\"group_id\":0,\"package_vehicle_scope\":\"member\",\"is_quick_service\":0,\"price\":37900,\"description\":\"Multi-stage ceramic coating package with premium finish.\",\"is_active\":0,\"package_items\":[]}',NULL,NULL,'2026-05-08 23:27:51'),(4,'service','delete','service',4,NULL,NULL,NULL,NULL,2,'System Administrator',NULL,NULL,NULL,'Service deleted.','{\"service_name\":\"Glass Coating\",\"service_type\":\"sub_service\",\"category_id\":4,\"main_service_id\":11,\"group_id\":0,\"package_vehicle_scope\":\"member\",\"is_quick_service\":0,\"price\":12900,\"description\":\"Nano-ceramic glass coating with surface prep.\",\"is_active\":0,\"package_items\":[]}',NULL,NULL,'2026-05-08 23:27:57'),(5,'category','delete','service_category',1,NULL,NULL,NULL,NULL,2,'System Administrator',NULL,NULL,NULL,'Service category deleted.','{\"category_name\":\"Add-on\"}',NULL,NULL,'2026-05-08 23:29:04'),(6,'employee','update','employee',4,NULL,NULL,NULL,NULL,2,'System Administrator',NULL,NULL,NULL,'Employee account updated.','{\"default_start_page\":\"\"}','{\"default_start_page\":\"queue\"}',NULL,'2026-05-08 23:49:22'),(7,'bill','create','order',1,1,NULL,1,1,2,'System Administrator','สาขาศรีนครินทร์','SRN052026/0001',200.00,'Quick Service bill created.',NULL,'{\"order_source\":\"quick_service\",\"status\":\"done\",\"payment_status\":\"paid\",\"total_price\":200,\"paid_before\":0,\"paid_after\":200,\"balance_amount\":0,\"service_count\":1,\"service_names\":[\"ล้างภายนอก + ดูดฝุ่น (M)\"],\"notes\":\"\"}','{\"submit_action\":\"pay\",\"payment_method\":\"transfer\",\"slip_file_name\":\"jn01.png\"}','2026-05-13 11:52:29'),(8,'payment','receive','payment',1,1,1,1,1,2,'System Administrator','สาขาศรีนครินทร์','SRN052026/0001',200.00,'Quick Service payment received.','{\"payment_status\":\"unpaid\",\"paid_amount\":0,\"balance_amount\":200}','{\"payment_method\":\"transfer\",\"recognized_amount\":200,\"received_amount\":200,\"change_amount\":0,\"has_slip\":true,\"payment_status\":\"paid\",\"paid_amount\":200,\"balance_amount\":0}','{\"order_source\":\"quick_service\",\"submit_action\":\"pay\",\"slip_file_name\":\"jn01.png\"}','2026-05-13 11:52:29'),(9,'bill','create','order',2,2,NULL,2,2,2,'System Administrator','สาขาศรีนครินทร์','SRN052026/0002',5000.00,'Booking bill created.',NULL,'{\"order_source\":\"booking\",\"status\":\"waiting\",\"payment_status\":\"partial\",\"queue_number\":1,\"total_price\":5000,\"deposit_amount\":1000,\"admin_deposit_amount\":1000,\"branch_deposit_amount\":0,\"pickup_due_at\":null,\"service_count\":1,\"service_names\":[\"ติดฟิล์มกรองแสง\"],\"notes\":\"\"}','{\"package_usage_count\":0,\"package_purchase_count\":0}','2026-05-13 12:24:17'),(10,'payment','receive','payment',2,2,2,2,2,2,'System Administrator','สาขาศรีนครินทร์','SRN052026/0002',1000.00,'Deposit @Admin received at booking.',NULL,'{\"payment_method\":\"transfer\",\"recognized_amount\":1000,\"received_amount\":1000,\"change_amount\":0,\"payment_status_after\":\"partial\"}','{\"deposit_type\":\"admin\",\"order_source\":\"booking\"}','2026-05-13 12:24:17'),(11,'customer','create','customer',2,2,NULL,2,2,2,'System Administrator','สาขาศรีนครินทร์','SRN052026/0002',NULL,'Customer created from booking.','{\"name\":null,\"phone\":null}','{\"name\":\"ณรงค์ ทูลจินดา\",\"phone\":\"0939659871\"}','{\"order_source\":\"booking\"}','2026-05-13 12:24:17'),(12,'car','create','car',2,2,NULL,2,2,2,'System Administrator','สาขาศรีนครินทร์','SRN052026/0002',NULL,'Vehicle created from booking.','{\"plate_number\":null,\"province\":null,\"vehicle_type\":null,\"brand\":null,\"model\":null,\"color\":null}','{\"plate_number\":\"5ขส8154\",\"province\":\"Bangkok\",\"vehicle_type\":\"\",\"brand\":\"Mazda\",\"model\":\"CX-30\",\"color\":\"เทาดำ\"}','{\"order_source\":\"booking\"}','2026-05-13 12:24:17'),(13,'bill','create','order',3,3,NULL,3,3,2,'System Administrator','สาขาบางนา','BNA052026/0003',200.00,'Quick Service bill created.',NULL,'{\"order_source\":\"quick_service\",\"customer_mode\":\"guest\",\"customer_name\":\"Guest\",\"phone\":\"\",\"plate_number\":\"2569\",\"status\":\"done\",\"payment_status\":\"paid\",\"total_price\":200,\"paid_before\":0,\"paid_after\":200,\"balance_amount\":0,\"service_count\":1,\"service_names\":[\"ล้างภายนอก + ดูดฝุ่น (M)\"],\"notes\":\"\"}','{\"submit_action\":\"pay\",\"payment_method\":\"cash\",\"slip_file_name\":\"\"}','2026-05-13 13:49:10'),(14,'payment','receive','payment',3,3,3,3,3,2,'System Administrator','สาขาบางนา','BNA052026/0003',200.00,'Quick Service payment received.','{\"payment_status\":\"unpaid\",\"paid_amount\":0,\"balance_amount\":200}','{\"payment_method\":\"cash\",\"recognized_amount\":200,\"received_amount\":200,\"change_amount\":0,\"has_slip\":false,\"payment_status\":\"paid\",\"paid_amount\":200,\"balance_amount\":0}','{\"order_source\":\"quick_service\",\"submit_action\":\"pay\",\"slip_file_name\":\"\"}','2026-05-13 13:49:10'),(15,'bill','create','order',4,4,NULL,3,3,2,'System Administrator','สาขาศรีนครินทร์','SRN052026/0004',150.00,'Quick Service bill created.',NULL,'{\"order_source\":\"quick_service\",\"customer_mode\":\"guest\",\"customer_name\":\"Guest\",\"phone\":\"\",\"plate_number\":\"2569\",\"status\":\"done\",\"payment_status\":\"paid\",\"total_price\":150,\"paid_before\":0,\"paid_after\":150,\"balance_amount\":0,\"service_count\":1,\"service_names\":[\"ล้างภายนอก + ดูดฝุ่น (S)\"],\"notes\":\"\"}','{\"submit_action\":\"pay\",\"payment_method\":\"transfer\",\"slip_file_name\":\"\"}','2026-05-13 14:06:57'),(16,'payment','receive','payment',4,4,4,3,3,2,'System Administrator','สาขาศรีนครินทร์','SRN052026/0004',150.00,'Quick Service payment received.','{\"payment_status\":\"unpaid\",\"paid_amount\":0,\"balance_amount\":150}','{\"payment_method\":\"transfer\",\"recognized_amount\":150,\"received_amount\":150,\"change_amount\":0,\"has_slip\":false,\"payment_status\":\"paid\",\"paid_amount\":150,\"balance_amount\":0}','{\"order_source\":\"quick_service\",\"submit_action\":\"pay\",\"slip_file_name\":\"\"}','2026-05-13 14:06:57'),(17,'bill','create','order',5,5,NULL,3,3,2,'System Administrator','สาขาศรีนครินทร์','SRN052026/0005',350.00,'Quick Service bill created.',NULL,'{\"order_source\":\"quick_service\",\"customer_mode\":\"guest\",\"customer_name\":\"Guest\",\"phone\":\"\",\"plate_number\":\"2569\",\"status\":\"done\",\"payment_status\":\"paid\",\"total_price\":350,\"paid_before\":0,\"paid_after\":350,\"balance_amount\":0,\"service_count\":2,\"service_names\":[\"ล้างภายนอก + ดูดฝุ่น (M)\",\"ล้างภายนอก + ดูดฝุ่น (S)\"],\"notes\":\"\"}','{\"submit_action\":\"pay\",\"payment_method\":\"transfer\",\"slip_file_name\":\"\"}','2026-05-13 14:10:10'),(18,'payment','receive','payment',5,5,5,3,3,2,'System Administrator','สาขาศรีนครินทร์','SRN052026/0005',350.00,'Quick Service payment received.','{\"payment_status\":\"unpaid\",\"paid_amount\":0,\"balance_amount\":350}','{\"payment_method\":\"transfer\",\"recognized_amount\":350,\"received_amount\":350,\"change_amount\":0,\"has_slip\":false,\"payment_status\":\"paid\",\"paid_amount\":350,\"balance_amount\":0}','{\"order_source\":\"quick_service\",\"submit_action\":\"pay\",\"slip_file_name\":\"\"}','2026-05-13 14:10:10'),(19,'void','cancel','order',3,3,NULL,3,3,2,'System Administrator','สาขาบางนา','BNA052026/0003',200.00,'Bill cancelled.','{\"order_source\":\"quick_service\",\"status\":\"done\",\"payment_status\":\"paid\",\"total_price\":200,\"paid_amount\":200,\"balance_amount\":0}','{\"status\":\"cancelled\",\"cancelled_by_employee_id\":2,\"cancellation_reason\":\"test\"}','{\"order_source\":\"quick_service\"}','2026-05-13 17:09:51'),(20,'void','cancel','order',4,4,NULL,3,3,2,'System Administrator','สาขาศรีนครินทร์','SRN052026/0004',150.00,'Bill cancelled.','{\"order_source\":\"quick_service\",\"status\":\"done\",\"payment_status\":\"paid\",\"total_price\":150,\"paid_amount\":150,\"balance_amount\":0}','{\"status\":\"cancelled\",\"cancelled_by_employee_id\":2,\"cancellation_reason\":\"test\"}','{\"order_source\":\"quick_service\"}','2026-05-13 17:09:58'),(21,'void','cancel','order',5,5,NULL,3,3,2,'System Administrator','สาขาศรีนครินทร์','SRN052026/0005',350.00,'Bill cancelled.','{\"order_source\":\"quick_service\",\"status\":\"done\",\"payment_status\":\"paid\",\"total_price\":350,\"paid_amount\":350,\"balance_amount\":0}','{\"status\":\"cancelled\",\"cancelled_by_employee_id\":2,\"cancellation_reason\":\"test\"}','{\"order_source\":\"quick_service\"}','2026-05-13 17:10:02'),(22,'bill','create','order',6,6,NULL,4,4,2,'System Administrator','สาขาบางนา','BNA052026/0006',200.00,'Quick Service bill created.',NULL,'{\"order_source\":\"quick_service\",\"customer_mode\":\"guest\",\"customer_name\":\"Guest\",\"customer_group\":\"guest\",\"phone\":\"\",\"plate_number\":\"123\",\"status\":\"done\",\"payment_status\":\"paid\",\"total_price\":200,\"paid_before\":0,\"paid_after\":200,\"balance_amount\":0,\"service_count\":1,\"service_names\":[\"ล้างภายนอก + ดูดฝุ่น (M)\"],\"notes\":\"\"}','{\"submit_action\":\"pay\",\"payment_method\":\"cash\",\"slip_file_name\":\"\"}','2026-05-13 17:12:09'),(23,'payment','receive','payment',6,6,6,4,4,2,'System Administrator','สาขาบางนา','BNA052026/0006',200.00,'Quick Service payment received.','{\"payment_status\":\"unpaid\",\"paid_amount\":0,\"balance_amount\":200}','{\"payment_method\":\"cash\",\"recognized_amount\":200,\"received_amount\":200,\"change_amount\":0,\"has_slip\":false,\"payment_status\":\"paid\",\"paid_amount\":200,\"balance_amount\":0}','{\"order_source\":\"quick_service\",\"submit_action\":\"pay\",\"slip_file_name\":\"\"}','2026-05-13 17:12:09'),(24,'void','cancel','order',6,6,NULL,4,4,2,'System Administrator','สาขาบางนา','BNA052026/0006',200.00,'Bill cancelled.','{\"order_source\":\"quick_service\",\"status\":\"done\",\"payment_status\":\"paid\",\"total_price\":200,\"paid_amount\":200,\"balance_amount\":0}','{\"status\":\"cancelled\",\"cancelled_by_employee_id\":2,\"cancellation_reason\":\"test\"}','{\"order_source\":\"quick_service\"}','2026-05-13 17:13:08'),(25,'bill','create','order',7,7,NULL,5,5,2,'System Administrator','สาขารามอินทรา','RMI052026/0007',200.00,'Quick Service bill created.',NULL,'{\"order_source\":\"quick_service\",\"customer_mode\":\"guest\",\"customer_name\":\"Guest\",\"customer_group\":\"guest\",\"phone\":\"\",\"plate_number\":\"001\",\"status\":\"done\",\"payment_status\":\"paid\",\"total_price\":200,\"paid_before\":0,\"paid_after\":200,\"balance_amount\":0,\"service_count\":1,\"service_names\":[\"ล้างภายนอก + ดูดฝุ่น (M)\"],\"notes\":\"\"}','{\"submit_action\":\"pay\",\"payment_method\":\"cash\",\"slip_file_name\":\"\"}','2026-05-13 17:28:55'),(26,'payment','receive','payment',7,7,7,5,5,2,'System Administrator','สาขารามอินทรา','RMI052026/0007',200.00,'Quick Service payment received.','{\"payment_status\":\"unpaid\",\"paid_amount\":0,\"balance_amount\":200}','{\"payment_method\":\"cash\",\"recognized_amount\":200,\"received_amount\":200,\"change_amount\":0,\"has_slip\":false,\"payment_status\":\"paid\",\"paid_amount\":200,\"balance_amount\":0}','{\"order_source\":\"quick_service\",\"submit_action\":\"pay\",\"slip_file_name\":\"\"}','2026-05-13 17:28:55');

--
-- Table structure for table `cars`
--

CREATE TABLE `cars` (
  `car_id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int NOT NULL,
  `plate_number` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `province` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `vehicle_type` varchar(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `brand` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `model` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `color` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`car_id`),
  UNIQUE KEY `uniq_cars_plate` (`plate_number`),
  KEY `idx_cars_customer` (`customer_id`),
  CONSTRAINT `fk_cars_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `cars`
--

INSERT INTO `cars` VALUES (1,1,'QS-SRN','','','','','','2026-05-13 11:52:29','2026-05-13 19:18:49'),(2,2,'5ขส8154','Bangkok','','Mazda','CX-30','เทาดำ','2026-05-13 12:24:17','2026-05-13 12:24:17'),(3,3,'2569','','','','','','2026-05-13 13:49:10','2026-05-13 19:18:49'),(4,4,'123','','','','','','2026-05-13 17:12:09','2026-05-13 19:18:49'),(5,5,'001','','','','','','2026-05-13 17:28:55','2026-05-13 19:18:49');

--
-- Table structure for table `customer_package_items`
--

CREATE TABLE `customer_package_items` (
  `customer_package_item_id` int NOT NULL AUTO_INCREMENT,
  `customer_package_id` int NOT NULL,
  `sub_service_id` int NOT NULL,
  `service_name_snapshot` varchar(120) COLLATE utf8mb4_unicode_ci NOT NULL,
  `included_uses` int NOT NULL DEFAULT '1',
  `used_uses` int NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`customer_package_item_id`),
  KEY `idx_customer_package_items_package` (`customer_package_id`),
  KEY `idx_customer_package_items_sub_service` (`sub_service_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `customer_package_items`
--


--
-- Table structure for table `customer_packages`
--

CREATE TABLE `customer_packages` (
  `customer_package_id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int NOT NULL,
  `car_id` int DEFAULT NULL,
  `vehicle_scope` enum('member','car') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'member',
  `package_service_id` int NOT NULL,
  `order_id` int NOT NULL,
  `package_name_snapshot` varchar(120) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` enum('active','cancelled') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'active',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `cancelled_at` datetime DEFAULT NULL,
  PRIMARY KEY (`customer_package_id`),
  KEY `idx_customer_packages_customer` (`customer_id`),
  KEY `idx_customer_packages_order` (`order_id`),
  KEY `idx_customer_packages_status` (`status`),
  KEY `idx_customer_packages_car` (`car_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `customer_packages`
--


--
-- Table structure for table `customers`
--

CREATE TABLE `customers` (
  `customer_id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(120) COLLATE utf8mb4_unicode_ci NOT NULL,
  `phone` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(120) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `loyalty_points` int NOT NULL DEFAULT '0',
  `customer_group` enum('member','guest') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'member',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`customer_id`),
  KEY `idx_customers_phone` (`phone`),
  KEY `idx_customers_group` (`customer_group`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `customers`
--

INSERT INTO `customers` VALUES (1,'Quick Service สาขาศรีนครินทร์','',NULL,0,'guest','2026-05-13 11:52:29','2026-05-13 15:05:19'),(2,'ณรงค์ ทูลจินดา','0939659871',NULL,0,'member','2026-05-13 12:24:17','2026-05-13 12:24:17'),(3,'Guest','',NULL,0,'guest','2026-05-13 13:49:10','2026-05-13 15:05:19'),(4,'Guest','',NULL,0,'guest','2026-05-13 17:12:09','2026-05-13 17:12:09'),(5,'Guest','',NULL,0,'guest','2026-05-13 17:28:55','2026-05-13 17:28:55');

--
-- Table structure for table `employee_branch_access`
--

CREATE TABLE `employee_branch_access` (
  `employee_id` int NOT NULL,
  `branch_name` varchar(120) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`employee_id`,`branch_name`),
  KEY `idx_employee_branch_name` (`branch_name`),
  CONSTRAINT `fk_employee_branch_access_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`employee_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `employee_branch_access`
--

INSERT INTO `employee_branch_access` VALUES (3,'สาขาศรีนครินทร์'),(4,'สาขาศรีนครินทร์'),(5,'สาขาศรีนครินทร์');

--
-- Table structure for table `loyalty_transactions`
--

CREATE TABLE `loyalty_transactions` (
  `transaction_id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int NOT NULL,
  `order_id` int DEFAULT NULL,
  `points` int NOT NULL,
  `transaction_type` enum('earn','redeem','adjust') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'earn',
  `description` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`transaction_id`),
  UNIQUE KEY `uniq_loyalty_order_type` (`order_id`,`transaction_type`),
  KEY `idx_loyalty_customer` (`customer_id`),
  CONSTRAINT `fk_loyalty_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_loyalty_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`order_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `loyalty_transactions`
--


--
-- Table structure for table `order_details`
--

CREATE TABLE `order_details` (
  `detail_id` int NOT NULL AUTO_INCREMENT,
  `order_id` int NOT NULL,
  `service_id` int NOT NULL,
  `service_name_snapshot` varchar(120) COLLATE utf8mb4_unicode_ci NOT NULL,
  `price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `duration_minutes` int NOT NULL DEFAULT '60',
  `service_options` text COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`detail_id`),
  KEY `idx_order_details_order` (`order_id`),
  KEY `idx_order_details_service` (`service_id`),
  CONSTRAINT `fk_order_details_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`order_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_order_details_service` FOREIGN KEY (`service_id`) REFERENCES `services` (`service_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `order_details`
--

INSERT INTO `order_details` VALUES (1,1,48,'ล้างภายนอก + ดูดฝุ่น (M)',200.00,0,NULL),(2,2,11,'ติดฟิล์มกรองแสง',5000.00,0,'{\"type\":\"window_tint\",\"group_id\":6,\"group_name\":\"BLAUPUNKT\",\"main_service_id\":11,\"front_service_id\":22,\"front_service_name\":\"BLAUPUNKT (A10BK)\",\"front_side_service_id\":14,\"front_side_service_name\":\"BLAUPUNKT (A35BK)\",\"rear_side_service_id\":21,\"rear_side_service_name\":\"BLAUPUNKT (A18BK)\",\"rear_service_id\":21,\"rear_service_name\":\"BLAUPUNKT (A18BK)\",\"notes\":\"\"}'),(3,3,48,'ล้างภายนอก + ดูดฝุ่น (M)',200.00,0,NULL),(4,4,47,'ล้างภายนอก + ดูดฝุ่น (S)',150.00,0,NULL),(5,5,48,'ล้างภายนอก + ดูดฝุ่น (M)',200.00,0,NULL),(6,5,47,'ล้างภายนอก + ดูดฝุ่น (S)',150.00,0,NULL),(7,6,48,'ล้างภายนอก + ดูดฝุ่น (M)',200.00,0,NULL),(8,7,48,'ล้างภายนอก + ดูดฝุ่น (M)',200.00,0,NULL);

--
-- Table structure for table `order_package_usages`
--

CREATE TABLE `order_package_usages` (
  `usage_id` int NOT NULL AUTO_INCREMENT,
  `order_id` int NOT NULL,
  `customer_package_item_id` int NOT NULL,
  `service_id` int NOT NULL,
  `service_name_snapshot` varchar(120) COLLATE utf8mb4_unicode_ci NOT NULL,
  `used_uses` int NOT NULL DEFAULT '1',
  `is_reversed` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `reversed_at` datetime DEFAULT NULL,
  PRIMARY KEY (`usage_id`),
  KEY `idx_order_package_usages_order` (`order_id`),
  KEY `idx_order_package_usages_package_item` (`customer_package_item_id`),
  KEY `idx_order_package_usages_service` (`service_id`),
  KEY `idx_order_package_usages_reversed` (`is_reversed`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `order_package_usages`
--


--
-- Table structure for table `orders`
--

CREATE TABLE `orders` (
  `order_id` int NOT NULL AUTO_INCREMENT,
  `bill_number` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `customer_id` int NOT NULL,
  `car_id` int NOT NULL,
  `employee_id` int DEFAULT NULL,
  `branch_name` varchar(120) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `order_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `scheduled_at` datetime DEFAULT NULL,
  `return_at` datetime DEFAULT NULL,
  `order_source` enum('booking','quick_service') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'booking',
  `started_at` datetime DEFAULT NULL,
  `finished_at` datetime DEFAULT NULL,
  `cancelled_at` datetime DEFAULT NULL,
  `cancelled_by_employee_id` int DEFAULT NULL,
  `cancellation_reason` text COLLATE utf8mb4_unicode_ci,
  `reopened_from_order_id` int DEFAULT NULL,
  `status` enum('waiting','washing','done','cancelled') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'waiting',
  `payment_status` enum('unpaid','partial','paid') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'unpaid',
  `total_price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `deposit_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `deposit_method` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `admin_deposit_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `admin_deposit_method` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `branch_deposit_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `branch_deposit_method` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`order_id`),
  UNIQUE KEY `uniq_orders_bill_number` (`bill_number`),
  KEY `idx_orders_status` (`status`),
  KEY `idx_orders_payment_status` (`payment_status`),
  KEY `idx_orders_order_date` (`order_date`),
  KEY `idx_orders_customer` (`customer_id`),
  KEY `idx_orders_car` (`car_id`),
  KEY `fk_orders_employee` (`employee_id`),
  KEY `idx_orders_branch_name` (`branch_name`),
  KEY `idx_orders_return_at` (`return_at`),
  KEY `idx_orders_deposit_method` (`deposit_method`),
  KEY `idx_orders_cancelled_at` (`cancelled_at`),
  KEY `idx_orders_cancelled_by_employee` (`cancelled_by_employee_id`),
  KEY `idx_orders_reopened_from` (`reopened_from_order_id`),
  KEY `idx_orders_order_source` (`order_source`),
  KEY `idx_orders_admin_deposit_method` (`admin_deposit_method`),
  KEY `idx_orders_branch_deposit_method` (`branch_deposit_method`),
  CONSTRAINT `fk_orders_car` FOREIGN KEY (`car_id`) REFERENCES `cars` (`car_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_orders_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_orders_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`employee_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `orders`
--

INSERT INTO `orders` VALUES (1,'SRN052026/0001',1,1,2,'สาขาศรีนครินทร์','2026-05-13 11:52:29',NULL,NULL,'quick_service',NULL,'2026-05-13 11:52:29',NULL,NULL,NULL,NULL,'done','paid',200.00,0.00,NULL,0.00,NULL,0.00,NULL,'','2026-05-13 11:52:29'),(2,'SRN052026/0002',2,2,NULL,'สาขาศรีนครินทร์','2026-05-13 12:24:17',NULL,NULL,'booking',NULL,NULL,NULL,NULL,NULL,NULL,'waiting','partial',5000.00,1000.00,'transfer',1000.00,'transfer',0.00,'cash','','2026-05-13 12:24:17'),(3,'BNA052026/0003',3,3,2,'สาขาบางนา','2026-05-13 13:49:10',NULL,NULL,'quick_service',NULL,'2026-05-13 13:49:10','2026-05-13 17:09:51',2,'test',NULL,'cancelled','paid',200.00,0.00,NULL,0.00,NULL,0.00,NULL,'','2026-05-13 17:09:51'),(4,'SRN052026/0004',3,3,2,'สาขาศรีนครินทร์','2026-05-13 14:06:57',NULL,NULL,'quick_service',NULL,'2026-05-13 14:06:57','2026-05-13 17:09:58',2,'test',NULL,'cancelled','paid',150.00,0.00,NULL,0.00,NULL,0.00,NULL,'','2026-05-13 17:09:58'),(5,'SRN052026/0005',3,3,2,'สาขาศรีนครินทร์','2026-05-13 14:10:10',NULL,NULL,'quick_service',NULL,'2026-05-13 14:10:10','2026-05-13 17:10:02',2,'test',NULL,'cancelled','paid',350.00,0.00,NULL,0.00,NULL,0.00,NULL,'','2026-05-13 17:10:02'),(6,'BNA052026/0006',4,4,2,'สาขาบางนา','2026-05-13 17:12:09',NULL,NULL,'quick_service',NULL,'2026-05-13 17:12:09','2026-05-13 17:13:08',2,'test',NULL,'cancelled','paid',200.00,0.00,NULL,0.00,NULL,0.00,NULL,'','2026-05-13 17:13:08'),(7,'RMI052026/0007',5,5,2,'สาขารามอินทรา','2026-05-13 17:28:55',NULL,NULL,'quick_service',NULL,'2026-05-13 17:28:55',NULL,NULL,NULL,NULL,'done','paid',200.00,0.00,NULL,0.00,NULL,0.00,NULL,'','2026-05-13 17:28:55');

--
-- Table structure for table `payments`
--

CREATE TABLE `payments` (
  `payment_id` int NOT NULL AUTO_INCREMENT,
  `order_id` int NOT NULL,
  `payment_method` enum('cash','transfer','qr','card') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'cash',
  `amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `received_amount` decimal(10,2) DEFAULT NULL,
  `change_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `note` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `slip_file_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `slip_file_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `slip_mime_type` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `slip_uploaded_at` datetime DEFAULT NULL,
  `payment_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`payment_id`),
  KEY `idx_payments_order` (`order_id`),
  KEY `idx_payments_date` (`payment_date`),
  CONSTRAINT `fk_payments_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`order_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `payments`
--

INSERT INTO `payments` VALUES (1,1,'transfer',200.00,200.00,0.00,'Quick Service payment','/ccw/storage/uploads/payment_slips/quick-service-payment-20260513115229-7afd3ea2.png','jn01.png','image/png','2026-05-13 11:52:29','2026-05-13 11:52:29'),(2,2,'transfer',1000.00,1000.00,0.00,'Admin deposit at booking',NULL,NULL,NULL,NULL,'2026-05-13 12:24:17'),(3,3,'cash',200.00,200.00,0.00,'Quick Service payment',NULL,NULL,NULL,NULL,'2026-05-13 13:49:10'),(4,4,'transfer',150.00,150.00,0.00,'Quick Service payment',NULL,NULL,NULL,NULL,'2026-05-13 14:06:57'),(5,5,'transfer',350.00,350.00,0.00,'Quick Service payment',NULL,NULL,NULL,NULL,'2026-05-13 14:10:10'),(6,6,'cash',200.00,200.00,0.00,'Quick Service payment',NULL,NULL,NULL,NULL,'2026-05-13 17:12:09'),(7,7,'cash',200.00,200.00,0.00,'Quick Service payment',NULL,NULL,NULL,NULL,'2026-05-13 17:28:55');

--
-- Table structure for table `positions`
--

CREATE TABLE `positions` (
  `position_id` int NOT NULL AUTO_INCREMENT,
  `position_name` varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
  `role_key` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  `branch_scope` enum('all','assigned') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'assigned',
  `permissions` text COLLATE utf8mb4_unicode_ci,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`position_id`),
  UNIQUE KEY `uniq_positions_role_key` (`role_key`)
) ENGINE=InnoDB AUTO_INCREMENT=23737 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `positions`
--

INSERT INTO `positions` VALUES (1,'Administrator','admin','all','[\"dashboard\",\"booking\",\"booking_deposit_admin\",\"booking_deposit_branch\",\"quick_service\",\"quick_service_payment\",\"queue\",\"queue_payment\",\"queue_start_waiting\",\"queue_cancel_waiting\",\"queue_bill_control\",\"queue_cancel_closed\",\"queue_cancel_open\",\"queue_edit_open\",\"bill_management\",\"services\",\"member\",\"member_manage\",\"member_edit\",\"member_delete\",\"audit_report\",\"report_service\",\"sale_report\",\"void_report\",\"loyalty\",\"employees\",\"maintenance_clear_log\",\"maintenance_clear_booking_queue\",\"maintenance_clear_sale\",\"maintenance_reset_operational_data\",\"maintenance_cleanup_quick_service_guest\",\"maintenance_backup_database\",\"maintenance_restore_database\"]',1,'2026-04-22 16:23:55'),(2,'Branch Manager','manager','assigned','[\"dashboard\",\"queue\",\"queue_payment\",\"queue_start_waiting\",\"queue_cancel_waiting\"]',1,'2026-04-22 16:23:55'),(3,'Service Advisor','advisor','assigned','[\"quick_service\",\"quick_service_payment\",\"queue\",\"queue_payment\",\"queue_start_waiting\",\"queue_cancel_waiting\"]',1,'2026-04-22 16:23:55'),(4,'Accounting','accounting','assigned','[\"dashboard\",\"report_service\",\"sale_report\"]',1,'2026-04-22 16:23:55'),(5,'Viewer','viewer','assigned','[\"dashboard\",\"report_service\",\"sale_report\"]',1,'2026-04-22 16:23:55'),(14706,'Admin Sale','admin_sale','all','[\"dashboard\",\"booking\",\"booking_deposit_admin\",\"queue\",\"queue_start_waiting\",\"queue_cancel_waiting\",\"queue_edit_open\"]',1,'2026-05-05 21:54:37');

--
-- Table structure for table `products`
--

CREATE TABLE `products` (
  `product_id` int NOT NULL AUTO_INCREMENT,
  `product_name` varchar(120) COLLATE utf8mb4_unicode_ci NOT NULL,
  `stock` int NOT NULL DEFAULT '0',
  `cost` decimal(10,2) NOT NULL DEFAULT '0.00',
  `price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `products`
--


--
-- Table structure for table `queue`
--

CREATE TABLE `queue` (
  `queue_id` int NOT NULL AUTO_INCREMENT,
  `order_id` int NOT NULL,
  `queue_number` int NOT NULL,
  `status` enum('waiting','washing','done','cancelled') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'waiting',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `called_at` datetime DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  PRIMARY KEY (`queue_id`),
  KEY `idx_queue_order` (`order_id`),
  KEY `idx_queue_day` (`created_at`,`queue_number`),
  CONSTRAINT `fk_queue_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`order_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `queue`
--

INSERT INTO `queue` VALUES (1,2,1,'waiting','2026-05-13 12:24:17',NULL,NULL);

--
-- Table structure for table `service_categories`
--

CREATE TABLE `service_categories` (
  `category_id` int NOT NULL AUTO_INCREMENT,
  `category_name` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`category_id`),
  UNIQUE KEY `uniq_service_categories_name` (`category_name`)
) ENGINE=InnoDB AUTO_INCREMENT=12342 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `service_categories`
--

INSERT INTO `service_categories` VALUES (2,'Coating','2026-04-28 13:34:22'),(4,'Flim','2026-04-28 13:34:22'),(5,'Interior','2026-04-28 13:34:22'),(6,'Protection','2026-04-28 13:34:22'),(7,'Wash','2026-04-28 13:34:22');

--
-- Table structure for table `service_groups`
--

CREATE TABLE `service_groups` (
  `group_id` int NOT NULL AUTO_INCREMENT,
  `category_id` int NOT NULL,
  `main_service_id` int DEFAULT NULL,
  `group_name` varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
  `show_window_film_details` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`group_id`),
  UNIQUE KEY `uniq_service_group_main` (`main_service_id`,`group_name`),
  KEY `idx_service_groups_category_id` (`category_id`),
  KEY `idx_service_groups_main_service_id` (`main_service_id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `service_groups`
--

INSERT INTO `service_groups` VALUES (4,4,11,'V-Kool',1,'2026-04-28 16:59:09'),(6,4,11,'BLAUPUNKT',1,'2026-04-28 17:05:34'),(7,4,11,'3M',1,'2026-04-30 16:14:04'),(8,4,11,'AVERY',1,'2026-04-30 16:14:22'),(9,4,11,'HI-Kool',1,'2026-04-30 16:14:41'),(10,4,11,'Solar Gard',1,'2026-04-30 16:14:55'),(11,4,11,'Glasia',1,'2026-04-30 16:15:11'),(12,4,11,'Lamina',1,'2026-04-30 16:15:26'),(13,6,43,'VERTEK',0,'2026-04-30 16:28:31');

--
-- Table structure for table `service_package_items`
--

CREATE TABLE `service_package_items` (
  `package_item_id` int NOT NULL AUTO_INCREMENT,
  `package_service_id` int NOT NULL,
  `sub_service_id` int NOT NULL,
  `included_uses` int NOT NULL DEFAULT '1',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`package_item_id`),
  UNIQUE KEY `uniq_package_service_sub_service` (`package_service_id`,`sub_service_id`),
  KEY `idx_package_items_package_service` (`package_service_id`),
  KEY `idx_package_items_sub_service` (`sub_service_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `service_package_items`
--

INSERT INTO `service_package_items` VALUES (4,15,7,5,'2026-04-30 11:29:33'),(5,15,3,5,'2026-04-30 11:29:33'),(6,15,1,10,'2026-04-30 11:29:33'),(7,16,1,2,'2026-04-30 11:33:25');

--
-- Table structure for table `services`
--

CREATE TABLE `services` (
  `service_id` int NOT NULL AUTO_INCREMENT,
  `service_name` varchar(120) COLLATE utf8mb4_unicode_ci NOT NULL,
  `service_type` enum('main_service','sub_service','package') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'sub_service',
  `category` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'Detailing',
  `category_id` int DEFAULT NULL,
  `main_service_id` int DEFAULT NULL,
  `group_id` int DEFAULT NULL,
  `package_vehicle_scope` enum('member','car') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'member',
  `is_quick_service` tinyint(1) NOT NULL DEFAULT '0',
  `price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `duration_minutes` int NOT NULL DEFAULT '60',
  `description` text COLLATE utf8mb4_unicode_ci,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`service_id`),
  KEY `idx_services_active` (`is_active`),
  KEY `idx_services_category` (`category`),
  KEY `idx_services_category_id` (`category_id`),
  KEY `idx_services_group_id` (`group_id`),
  KEY `idx_services_service_type` (`service_type`),
  KEY `idx_services_main_service_id` (`main_service_id`),
  KEY `idx_services_quick_service` (`is_quick_service`)
) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `services`
--

INSERT INTO `services` VALUES (1,'Standard Wash','sub_service','Wash',7,NULL,NULL,'member',0,550.00,45,'Exterior wash, interior vacuum and quick finish.',1,'2026-04-21 13:57:10','2026-04-28 14:52:20'),(3,'Interior Deep Clean','sub_service','Flim',4,11,NULL,'member',0,3200.00,0,'Seat, carpet, panel and odor treatment for cabin care.',0,'2026-04-21 13:57:10','2026-04-28 17:14:12'),(7,'Engine Cleaning','sub_service','Flim',4,11,NULL,'member',0,800.00,0,'Engine bay cleaning and light dressing.',0,'2026-04-21 13:57:10','2026-04-28 17:12:05'),(9,'ติดฟิล์มกรองแสง รอบคัน','sub_service','Flim',4,NULL,NULL,'member',0,100.00,300,'',1,'2026-04-22 17:15:44','2026-04-28 14:52:20'),(10,'V-Kool (V30)','sub_service','Flim',4,11,4,'member',0,1000.00,0,'',1,'2026-04-28 14:59:01','2026-04-28 16:59:47'),(11,'ติดฟิล์มกรองแสง','main_service','Flim',4,NULL,NULL,'member',0,5000.00,0,'',1,'2026-04-28 16:05:40','2026-04-28 16:05:40'),(12,'V-Kool (V05)','sub_service','Flim',4,11,4,'member',0,1000.00,0,'',1,'2026-04-28 16:11:45','2026-04-28 16:59:23'),(13,'V-Kool (V15)','sub_service','Flim',4,11,4,'member',0,1000.00,0,'',1,'2026-04-28 16:12:03','2026-04-28 16:59:39'),(14,'BLAUPUNKT (A35BK)','sub_service','Flim',4,11,6,'member',0,1000.00,0,'',1,'2026-04-28 16:15:16','2026-04-28 17:06:06'),(15,'เคลือบเซรามิค','package','Protection',6,NULL,NULL,'car',0,5900.00,0,'',1,'2026-04-28 17:18:03','2026-04-30 11:29:33'),(16,'Package wash 2000','package','Wash',7,NULL,NULL,'car',0,2000.00,0,'',1,'2026-04-30 11:33:25','2026-04-30 11:33:25'),(17,'V-Kool (VK30)','sub_service','Flim',4,11,4,'member',0,0.00,0,'',1,'2026-04-30 16:11:51','2026-04-30 16:11:51'),(18,'V-Kool (VK40)','sub_service','Flim',4,11,4,'member',0,0.00,0,'',1,'2026-04-30 16:12:10','2026-04-30 16:12:10'),(19,'V-Kool (VK55)','sub_service','Flim',4,11,4,'member',0,0.00,0,'',1,'2026-04-30 16:12:30','2026-04-30 16:12:30'),(20,'V-Kool (VK70)','sub_service','Flim',4,11,4,'member',0,0.00,0,'',1,'2026-04-30 16:12:46','2026-04-30 16:12:46'),(21,'BLAUPUNKT (A18BK)','sub_service','Flim',4,11,6,'member',0,0.00,0,'',1,'2026-04-30 16:13:20','2026-04-30 16:13:20'),(22,'BLAUPUNKT (A10BK)','sub_service','Flim',4,11,6,'member',0,0.00,0,'',1,'2026-04-30 16:13:34','2026-04-30 16:13:34'),(23,'3M (IR25)','sub_service','Flim',4,11,7,'member',0,0.00,0,'',1,'2026-04-30 16:15:55','2026-04-30 16:15:55'),(24,'3M (IR15)','sub_service','Flim',4,11,7,'member',0,0.00,0,'',1,'2026-04-30 16:16:08','2026-04-30 16:16:08'),(25,'3M (IR5)','sub_service','Flim',4,11,7,'member',0,0.00,0,'',1,'2026-04-30 16:16:21','2026-04-30 16:16:21'),(26,'AVERY (PRO35)','sub_service','Flim',4,11,8,'member',0,0.00,0,'',1,'2026-04-30 16:16:45','2026-04-30 16:16:45'),(27,'AVERY (PRO20)','sub_service','Flim',4,11,8,'member',0,0.00,0,'',1,'2026-04-30 16:17:03','2026-04-30 16:17:03'),(28,'AVERY (PRO10)','sub_service','Flim',4,11,8,'member',0,0.00,0,'',1,'2026-04-30 16:17:15','2026-04-30 16:17:15'),(29,'HI-Kool (BY30)','sub_service','Flim',4,11,9,'member',0,0.00,0,'',1,'2026-04-30 16:18:03','2026-04-30 16:18:03'),(30,'HI-Kool (BY20)','sub_service','Flim',4,11,9,'member',0,0.00,0,'',1,'2026-04-30 16:18:18','2026-04-30 16:18:18'),(31,'HI-Kool (BY15)','sub_service','Flim',4,11,9,'member',0,0.00,0,'',1,'2026-04-30 16:18:40','2026-04-30 16:18:40'),(32,'HI-Kool (BY05)','sub_service','Flim',4,11,9,'member',0,0.00,0,'',1,'2026-04-30 16:18:54','2026-04-30 16:18:54'),(33,'Solar Gard (BPh30)','sub_service','Flim',4,11,10,'member',0,0.00,0,'',1,'2026-04-30 16:20:27','2026-04-30 16:20:27'),(34,'Solar Gard (QSQ20)','sub_service','Flim',4,11,10,'member',0,0.00,0,'',1,'2026-04-30 16:21:04','2026-04-30 16:21:04'),(35,'Solar Gard (BPh15)','sub_service','Flim',4,11,10,'member',0,0.00,0,'',1,'2026-04-30 16:21:43','2026-04-30 16:22:25'),(36,'Solar Gard (BPh05)','sub_service','Flim',4,11,10,'member',0,0.00,0,'',1,'2026-04-30 16:22:50','2026-04-30 16:22:50'),(37,'Glasia (80)','sub_service','Flim',4,11,11,'member',0,0.00,0,'',1,'2026-04-30 16:23:11','2026-04-30 16:23:11'),(38,'Glasia (60)','sub_service','Flim',4,11,11,'member',0,0.00,0,'',1,'2026-04-30 16:23:24','2026-04-30 16:23:24'),(39,'Glasia (40)','sub_service','Flim',4,11,11,'member',0,0.00,0,'',1,'2026-04-30 16:23:38','2026-04-30 16:23:38'),(40,'Lamina (L30)','sub_service','Flim',4,11,12,'member',0,0.00,0,'',1,'2026-04-30 16:23:58','2026-04-30 16:23:58'),(41,'Lamina (L15)','sub_service','Flim',4,11,12,'member',0,0.00,0,'',1,'2026-04-30 16:24:14','2026-04-30 16:24:14'),(42,'Lamina (L05)','sub_service','Flim',4,11,12,'member',0,0.00,0,'',1,'2026-04-30 16:24:29','2026-04-30 16:24:29'),(43,'TPU','main_service','Protection',6,NULL,NULL,'member',0,0.00,0,'',1,'2026-04-30 16:25:43','2026-04-30 16:28:13'),(44,'VERTEK (หนา200 | ประกัน 4ปี)','sub_service','Protection',6,43,13,'member',0,0.00,0,'หนา 200 \r\nประกัน 4ปี',1,'2026-04-30 16:30:21','2026-04-30 16:30:21'),(45,'VERTEK (หนา220 | ประกัน 5ปี)','sub_service','Protection',6,43,13,'member',0,0.00,0,'หนา220\r\nประกัน 5ปี',1,'2026-04-30 16:30:58','2026-04-30 16:30:58'),(46,'VERTEK (หนา250 | ประกัน 7 ปี)','sub_service','Protection',6,43,13,'member',0,0.00,0,'หนา250\r\nประกัน 7 ปี',1,'2026-04-30 16:31:53','2026-04-30 16:31:53'),(47,'ล้างภายนอก + ดูดฝุ่น (S)','main_service','Wash',7,NULL,NULL,'member',1,150.00,0,'',1,'2026-05-04 12:48:44','2026-05-04 12:48:44'),(48,'ล้างภายนอก + ดูดฝุ่น (M)','main_service','Wash',7,NULL,NULL,'member',1,200.00,0,'',1,'2026-05-04 12:49:00','2026-05-04 12:49:00');

--
-- Table structure for table `system_maintenance_logs`
--

CREATE TABLE `system_maintenance_logs` (
  `maintenance_log_id` int NOT NULL AUTO_INCREMENT,
  `maintenance_type` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'completed',
  `actor_employee_id` int DEFAULT NULL,
  `actor_name` varchar(120) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `branch_name` varchar(120) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `snapshot_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `filters_json` longtext COLLATE utf8mb4_unicode_ci,
  `preview_json` longtext COLLATE utf8mb4_unicode_ci,
  `result_json` longtext COLLATE utf8mb4_unicode_ci,
  `note` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`maintenance_log_id`),
  KEY `idx_system_maintenance_created_at` (`created_at`),
  KEY `idx_system_maintenance_type` (`maintenance_type`),
  KEY `idx_system_maintenance_actor` (`actor_employee_id`),
  KEY `idx_system_maintenance_branch` (`branch_name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `system_maintenance_logs`
--

INSERT INTO `system_maintenance_logs` VALUES (1,'reset_operational_data','completed',2,'System Administrator',NULL,'storage/maintenance_exports/reset_operational_data_20260508_224256_d43841eb.json','{\"include_logs\":true,\"reset_counters\":true}','{\"summary\":{\"selected_rows\":227,\"log_rows\":11,\"order_count\":46,\"customer_count\":9,\"car_count\":12},\"warnings\":[\"Auto-increment counters for cleared tables will restart from 1 after reset.\"]}','{\"deleted_counts\":{\"order_package_usages\":2,\"customer_package_items\":4,\"customer_packages\":2,\"loyalty_transactions\":16,\"payments\":51,\"queue\":23,\"order_details\":51,\"orders\":46,\"cars\":12,\"customers\":9,\"audit_logs\":7,\"system_maintenance_logs\":5},\"filters\":{\"include_logs\":true,\"reset_counters\":true},\"pre_reset_backup\":{\"file_name\":\"pre_reset_operational_ccw_20260508_224256.sql\",\"relative_path\":\"storage/database_backups/pre_reset_operational_ccw_20260508_224256.sql\",\"size_bytes\":72237,\"method\":\"mysqldump\"},\"reset_counters\":true}','Operational data was fully reset while master configuration data was preserved.','2026-05-08 22:42:56'),(2,'backup_database','completed',2,'System Administrator',NULL,'storage/database_backups/pre_cleanup_qs_guest_vehicle_ccw_20260513_191848.sql','{\"database\":\"CCW\",\"backup_prefix\":\"pre_cleanup_qs_guest_vehicle\",\"method\":\"mysqldump\"}',NULL,'{\"file_name\":\"pre_cleanup_qs_guest_vehicle_ccw_20260513_191848.sql\",\"size_bytes\":59111,\"method\":\"mysqldump\"}','Database backup created by mysqldump.','2026-05-13 19:18:49'),(3,'cleanup_quick_service_guest','completed',2,'System Administrator',NULL,'storage/maintenance_exports/cleanup_quick_service_guest_20260513_191849_3adc5255.json','[]','{\"summary\":{\"candidate_rows\":4,\"customer_count\":4,\"vehicle_type_rows\":4,\"brand_rows\":4,\"model_rows\":4},\"warnings\":[]}','{\"updated_rows\":4,\"updated_car_ids\":[1,3,4,5],\"pre_cleanup_backup\":{\"file_name\":\"pre_cleanup_qs_guest_vehicle_ccw_20260513_191848.sql\",\"relative_path\":\"storage/database_backups/pre_cleanup_qs_guest_vehicle_ccw_20260513_191848.sql\",\"size_bytes\":59111,\"method\":\"mysqldump\"}}','Old Quick Service guest default values were cleared from vehicle type, brand and model.','2026-05-13 19:18:49');

--
-- Dumping routines for database 'CCW'
--


