-- CCW structure sync safety backup
-- Generated at 2026-05-14 22:45:51

-- Structure for table audit_logs
CREATE TABLE `audit_logs` (
  `audit_log_id` int NOT NULL AUTO_INCREMENT,
  `module_key` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `action_key` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `entity_type` varchar(40) CHARACTER SET utf8mb4 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) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `branch_name` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `bill_number` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `amount` decimal(10,2) DEFAULT NULL,
  `message` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `before_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `after_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `meta_json` longtext CHARACTER SET utf8mb4 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=85 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `audit_logs` (`audit_log_id`, `module_key`, `action_key`, `entity_type`, `entity_id`, `order_id`, `payment_id`, `customer_id`, `car_id`, `actor_employee_id`, `actor_name`, `branch_name`, `bill_number`, `amount`, `message`, `before_json`, `after_json`, `meta_json`, `created_at`) 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 22:54:37'),
('2', '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 22:54:49'),
('3', '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 22:55:49'),
('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 22:57:22'),
('5', 'service', 'update', 'service', '15', NULL, NULL, NULL, NULL, '2', 'System Administrator', NULL, NULL, NULL, 'Service updated.', '{\"package_items\":[{\"sub_service_id\":7,\"service_name\":\"Engine Cleaning\",\"included_uses\":5},{\"sub_service_id\":3,\"service_name\":\"Interior Deep Clean\",\"included_uses\":5},{\"sub_service_id\":1,\"service_name\":\"Standard Wash\",\"included_uses\":10}]}', '{\"package_items\":[{\"sub_service_id\":1,\"service_name\":\"Standard Wash\",\"included_uses\":10}]}', NULL, '2026-05-08 22:59:34'),
('6', 'service', 'delete', 'service', '3', NULL, NULL, NULL, NULL, '2', 'System Administrator', NULL, NULL, NULL, 'Service deleted.', '{\"service_name\":\"Interior Deep Clean\",\"service_type\":\"sub_service\",\"category_id\":4,\"main_service_id\":11,\"group_id\":0,\"package_vehicle_scope\":\"member\",\"is_quick_service\":0,\"price\":3200,\"description\":\"Seat, carpet, panel and odor treatment for cabin care.\",\"is_active\":0,\"package_items\":[]}', NULL, NULL, '2026-05-08 22:59:51'),
('7', 'service', 'delete', 'service', '7', NULL, NULL, NULL, NULL, '2', 'System Administrator', NULL, NULL, NULL, 'Service deleted.', '{\"service_name\":\"Engine Cleaning\",\"service_type\":\"sub_service\",\"category_id\":4,\"main_service_id\":11,\"group_id\":0,\"package_vehicle_scope\":\"member\",\"is_quick_service\":0,\"price\":800,\"description\":\"Engine bay cleaning and light dressing.\",\"is_active\":0,\"package_items\":[]}', NULL, NULL, '2026-05-08 22:59:57'),
('8', 'service', 'delete', 'service', '9', NULL, NULL, NULL, NULL, '2', 'System Administrator', NULL, NULL, NULL, 'Service deleted.', '{\"service_name\":\"ติดฟิล์มกรองแสง รอบคัน\",\"service_type\":\"sub_service\",\"category_id\":4,\"main_service_id\":0,\"group_id\":0,\"package_vehicle_scope\":\"member\",\"is_quick_service\":0,\"price\":100,\"description\":\"\",\"is_active\":1,\"package_items\":[]}', NULL, NULL, '2026-05-08 23:02:37'),
('9', '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-09 00:14:58'),
('10', 'employee', 'update', 'employee', '3', NULL, NULL, NULL, NULL, '2', 'System Administrator', NULL, NULL, NULL, 'Employee account updated.', '{\"default_start_page\":\"\"}', '{\"default_start_page\":\"queue\"}', NULL, '2026-05-10 20:02:59'),
('11', 'service', 'create', 'service', '49', NULL, NULL, NULL, NULL, '2', 'System Administrator', NULL, NULL, NULL, 'Service created.', NULL, '{\"service_name\":\"Bp\",\"service_type\":\"sub_service\",\"category_id\":4,\"main_service_id\":11,\"group_id\":6,\"package_vehicle_scope\":\"member\",\"is_quick_service\":0,\"price\":0,\"description\":\"\",\"is_active\":1,\"package_items\":[]}', NULL, '2026-05-10 20:18:01'),
('12', 'service', 'delete', 'service', '49', NULL, NULL, NULL, NULL, '2', 'System Administrator', NULL, NULL, NULL, 'Service deleted.', '{\"service_name\":\"Bp\",\"service_type\":\"sub_service\",\"category_id\":4,\"main_service_id\":11,\"group_id\":6,\"package_vehicle_scope\":\"member\",\"is_quick_service\":0,\"price\":0,\"description\":\"\",\"is_active\":1,\"package_items\":[]}', NULL, NULL, '2026-05-10 20:18:41'),
('13', 'service', 'create', 'service', '50', NULL, NULL, NULL, NULL, '2', 'System Administrator', NULL, NULL, NULL, 'Service created.', NULL, '{\"service_name\":\"ล้าง 10 ครั้ง\",\"service_type\":\"package\",\"category_id\":7,\"main_service_id\":0,\"group_id\":0,\"package_vehicle_scope\":\"car\",\"is_quick_service\":0,\"price\":1000,\"description\":\"\",\"is_active\":1,\"package_items\":[{\"sub_service_id\":1,\"service_name\":\"Standard Wash\",\"included_uses\":10}]}', NULL, '2026-05-10 20:22:08'),
('14', 'package', 'create', 'customer_package', '1', '1', NULL, '1', '1', '2', 'System Administrator', 'สาขาศรีนครินทร์', 'SRN052026/0001', NULL, 'Package purchase created.', NULL, '{\"package_service_id\":50,\"package_name\":\"ล้าง 10 ครั้ง\",\"vehicle_scope\":\"car\",\"included_items\":[{\"sub_service_id\":1,\"service_name\":\"Standard Wash\",\"included_uses\":10}],\"order_source\":\"booking\"}', NULL, '2026-05-10 20:30:33'),
('15', 'bill', 'create', 'order', '1', '1', NULL, '1', '1', '2', 'System Administrator', 'สาขาศรีนครินทร์', 'SRN052026/0001', '1000.00', 'Booking bill created.', NULL, '{\"order_source\":\"booking\",\"status\":\"waiting\",\"payment_status\":\"unpaid\",\"queue_number\":1,\"total_price\":1000,\"deposit_amount\":0,\"admin_deposit_amount\":0,\"branch_deposit_amount\":0,\"pickup_due_at\":\"2026-05-11 20:24:00\",\"service_count\":1,\"service_names\":[\"ล้าง 10 ครั้ง\"],\"notes\":\"\"}', '{\"package_usage_count\":0,\"package_purchase_count\":1}', '2026-05-10 20:30:33'),
('16', 'customer', 'create', 'customer', '1', '1', NULL, '1', '1', '2', 'System Administrator', 'สาขาศรีนครินทร์', 'SRN052026/0001', NULL, 'Customer created from booking.', '{\"name\":null,\"phone\":null}', '{\"name\":\"นิว\",\"phone\":\"\"}', '{\"order_source\":\"booking\"}', '2026-05-10 20:30:33'),
('17', 'car', 'create', 'car', '1', '1', NULL, '1', '1', '2', 'System Administrator', 'สาขาศรีนครินทร์', 'SRN052026/0001', NULL, 'Vehicle created from booking.', '{\"plate_number\":null,\"province\":null,\"vehicle_type\":null,\"brand\":null,\"model\":null,\"color\":null}', '{\"plate_number\":\"334\",\"province\":\"Bangkok\",\"vehicle_type\":\"EV\",\"brand\":\"BYD\",\"model\":\"Em\",\"color\":\"ฟ้า\"}', '{\"order_source\":\"booking\"}', '2026-05-10 20:30:33'),
('20', 'package', 'cancel', 'customer_package', '1', '1', NULL, '1', '1', '2', 'System Administrator', 'สาขาศรีนครินทร์', 'SRN052026/0001', NULL, 'Package purchase cancelled.', '{\"package_name\":\"ล้าง 10 ครั้ง\",\"vehicle_scope\":\"car\",\"car_id\":1,\"status\":\"active\"}', '{\"status\":\"cancelled\"}', NULL, '2026-05-10 20:34:28'),
('21', 'package', 'create', 'customer_package', '2', '1', NULL, '1', '1', '2', 'System Administrator', 'สาขาศรีนครินทร์', 'SRN052026/0001', NULL, 'Package purchase created.', NULL, '{\"package_service_id\":50,\"package_name\":\"ล้าง 10 ครั้ง\",\"vehicle_scope\":\"car\",\"included_items\":[{\"sub_service_id\":1,\"service_name\":\"Standard Wash\",\"included_uses\":10}],\"order_source\":\"booking\"}', NULL, '2026-05-10 20:34:28'),
('22', 'queue', 'status', 'order', '1', '1', NULL, '1', '1', '2', 'System Administrator', 'สาขาศรีนครินทร์', 'SRN052026/0001', '1000.00', 'Queue status updated.', '{\"status\":\"waiting\",\"payment_status\":\"unpaid\"}', '{\"status\":\"washing\",\"payment_status\":\"unpaid\"}', NULL, '2026-05-10 20:34:37'),
('23', 'loyalty', 'earn', 'loyalty_transaction', '1', '1', NULL, '1', '1', '2', 'System Administrator', 'สาขาศรีนครินทร์', 'SRN052026/0001', NULL, 'Loyalty points earned from completed job.', NULL, '{\"transaction_type\":\"earn\",\"points\":10,\"description\":\"Earned from order #1\"}', NULL, '2026-05-10 20:34:46'),
('24', 'queue', 'status', 'order', '1', '1', NULL, '1', '1', '2', 'System Administrator', 'สาขาศรีนครินทร์', 'SRN052026/0001', '1000.00', 'Queue status updated.', '{\"status\":\"washing\",\"payment_status\":\"unpaid\"}', '{\"status\":\"done\",\"payment_status\":\"unpaid\"}', NULL, '2026-05-10 20:34:46'),
('25', 'payment', 'receive', 'payment', '1', '1', '1', '1', '1', '2', 'System Administrator', 'สาขาศรีนครินทร์', 'SRN052026/0001', '1000.00', 'Queue payment received.', '{\"order_status\":\"done\",\"payment_status\":\"unpaid\",\"paid_amount\":0,\"balance_amount\":1000}', '{\"order_status\":\"done\",\"payment_status\":\"paid\",\"paid_amount\":1000,\"balance_amount\":0,\"payment_method\":\"cash\",\"recognized_amount\":1000,\"received_amount\":1000,\"change_amount\":0,\"note\":\"\"}', '{\"order_source\":\"booking\"}', '2026-05-10 20:35:10'),
('26', 'loyalty', 'reverse', 'loyalty_transaction', '1', '1', NULL, '1', '1', '2', 'System Administrator', 'สาขาศรีนครินทร์', 'SRN052026/0001', NULL, 'Loyalty points reversed because job left done status.', '{\"transaction_type\":\"earn\",\"points\":10,\"status\":\"active\"}', '{\"status\":\"reversed\"}', NULL, '2026-05-10 20:37:01'),
('27', 'queue', 'status', 'order', '1', '1', NULL, '1', '1', '2', 'System Administrator', 'สาขาศรีนครินทร์', 'SRN052026/0001', '1000.00', 'Queue status updated.', '{\"status\":\"done\",\"payment_status\":\"paid\"}', '{\"status\":\"washing\",\"payment_status\":\"paid\"}', NULL, '2026-05-10 20:37:01'),
('28', 'queue', 'status', 'order', '1', '1', NULL, '1', '1', '2', 'System Administrator', 'สาขาศรีนครินทร์', 'SRN052026/0001', '1000.00', 'Queue status updated.', '{\"status\":\"washing\",\"payment_status\":\"paid\"}', '{\"status\":\"waiting\",\"payment_status\":\"paid\"}', NULL, '2026-05-10 20:37:16'),
('29', 'bill', 'create', 'order', '2', '2', NULL, '1', '1', '2', 'System Administrator', 'สาขาศรีนครินทร์', 'SRN052026/0002', '39900.00', 'Booking bill created.', NULL, '{\"order_source\":\"booking\",\"status\":\"waiting\",\"payment_status\":\"partial\",\"queue_number\":2,\"total_price\":39900,\"deposit_amount\":2900,\"admin_deposit_amount\":2900,\"branch_deposit_amount\":0,\"pickup_due_at\":null,\"service_count\":2,\"service_names\":[\"TPU\",\"VERTEK (หนา200 | ประกัน 4ปี)\"],\"notes\":\"\"}', '{\"package_usage_count\":0,\"package_purchase_count\":0}', '2026-05-10 20:40:02'),
('30', 'payment', 'receive', 'payment', '2', '2', '2', '1', '1', '2', 'System Administrator', 'สาขาศรีนครินทร์', 'SRN052026/0002', '2900.00', 'Deposit @Admin received at booking.', NULL, '{\"payment_method\":\"transfer\",\"recognized_amount\":2900,\"received_amount\":2900,\"change_amount\":0,\"payment_status_after\":\"partial\"}', '{\"deposit_type\":\"admin\",\"order_source\":\"booking\"}', '2026-05-10 20:40:02'),
('31', 'customer', 'update', 'customer', '1', '2', NULL, '1', '1', '2', 'System Administrator', 'สาขาศรีนครินทร์', 'SRN052026/0002', NULL, 'Customer updated from booking.', '{\"phone\":\"\"}', '{\"phone\":\"092\"}', '{\"order_source\":\"booking\"}', '2026-05-10 20:40:02'),
('32', 'car', 'update', 'car', '1', '2', NULL, '1', '1', '2', 'System Administrator', 'สาขาศรีนครินทร์', 'SRN052026/0002', NULL, 'Vehicle updated from booking.', '{\"brand\":\"BYD\"}', '{\"brand\":\"BMW\"}', '{\"order_source\":\"booking\"}', '2026-05-10 20:40:02'),
('33', 'bill', 'create', 'order', '3', '3', NULL, '2', '2', '2', 'System Administrator', 'สาขาศรีนครินทร์', 'SRN052026/0003', '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\"}', '2026-05-10 20:40:51'),
('34', 'payment', 'receive', 'payment', '3', '3', '3', '2', '2', '2', 'System Administrator', 'สาขาศรีนครินทร์', 'SRN052026/0003', '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,\"payment_status\":\"paid\",\"paid_amount\":200,\"balance_amount\":0}', '{\"order_source\":\"quick_service\",\"submit_action\":\"pay\"}', '2026-05-10 20:40:51'),
('35', 'bill', 'create', 'order', '4', '4', NULL, '2', '2', '2', 'System Administrator', 'สาขาศรีนครินทร์', 'SRN052026/0004', '200.00', 'Quick Service bill created.', NULL, '{\"order_source\":\"quick_service\",\"status\":\"waiting\",\"payment_status\":\"partial\",\"total_price\":200,\"paid_before\":0,\"paid_after\":100,\"balance_amount\":100,\"service_count\":1,\"service_names\":[\"ล้างภายนอก + ดูดฝุ่น (M)\"],\"notes\":\"\"}', '{\"submit_action\":\"pay\",\"payment_method\":\"transfer\"}', '2026-05-10 20:42:31'),
('36', 'payment', 'receive', 'payment', '4', '4', '4', '2', '2', '2', 'System Administrator', 'สาขาศรีนครินทร์', 'SRN052026/0004', '100.00', 'Quick Service payment received.', '{\"payment_status\":\"unpaid\",\"paid_amount\":0,\"balance_amount\":200}', '{\"payment_method\":\"transfer\",\"recognized_amount\":100,\"received_amount\":100,\"change_amount\":0,\"payment_status\":\"partial\",\"paid_amount\":100,\"balance_amount\":100}', '{\"order_source\":\"quick_service\",\"submit_action\":\"pay\"}', '2026-05-10 20:42:31'),
('37', 'bill', 'update', 'order', '4', '4', NULL, '2', '2', '2', 'System Administrator', 'สาขาศรีนครินทร์', 'SRN052026/0004', '200.00', 'Quick Service bill updated.', '{\"branch_name\":\"สาขาศรีนครินทร์\",\"bill_number\":\"SRN052026/0004\",\"status\":\"waiting\",\"payment_status\":\"partial\",\"total_price\":200,\"notes\":\"\"}', '{\"order_source\":\"quick_service\",\"status\":\"done\",\"payment_status\":\"paid\",\"total_price\":200,\"paid_before\":100,\"paid_after\":200,\"balance_amount\":0,\"service_count\":1,\"service_names\":[\"ล้างภายนอก + ดูดฝุ่น (M)\"],\"notes\":\"\"}', '{\"submit_action\":\"pay\",\"payment_method\":\"transfer\"}', '2026-05-10 20:43:15'),
('38', 'payment', 'receive', 'payment', '5', '4', '5', '2', '2', '2', 'System Administrator', 'สาขาศรีนครินทร์', 'SRN052026/0004', '100.00', 'Quick Service payment received.', '{\"payment_status\":\"partial\",\"paid_amount\":100,\"balance_amount\":100}', '{\"payment_method\":\"transfer\",\"recognized_amount\":100,\"received_amount\":100,\"change_amount\":0,\"payment_status\":\"paid\",\"paid_amount\":200,\"balance_amount\":0}', '{\"order_source\":\"quick_service\",\"submit_action\":\"pay\"}', '2026-05-10 20:43:15'),
('39', 'permission', 'update', 'position_permissions', '2', NULL, NULL, NULL, NULL, '2', 'System Administrator', NULL, NULL, NULL, 'Position permissions updated from multi-role screen.', NULL, NULL, '{\"position_name\":\"Branch Manager\",\"permissions_added\":[\"quick_service\",\"quick_service_payment\"],\"permissions_removed\":[]}', '2026-05-10 20:59:28'),
('40', 'position', 'update', 'position', '2', NULL, NULL, NULL, NULL, '2', 'System Administrator', NULL, NULL, NULL, 'Position updated.', '[]', '[]', '{\"permissions_added\":[\"booking\",\"booking_deposit_branch\",\"report_service\",\"sale_report\"],\"permissions_removed\":[]}', '2026-05-10 21:04:39'),
('41', 'group', 'create', 'service_group', '14', NULL, NULL, NULL, NULL, '2', 'System Administrator', NULL, NULL, NULL, 'Service group created.', NULL, '{\"category_id\":6,\"main_service_id\":43,\"group_name\":\"m3\",\"show_window_film_details\":0}', NULL, '2026-05-11 12:33:08'),
('42', 'group', 'create', 'service_group', '15', NULL, NULL, NULL, NULL, '2', 'System Administrator', NULL, NULL, NULL, 'Service group created.', NULL, '{\"category_id\":6,\"main_service_id\":43,\"group_name\":\"AVERY\",\"show_window_film_details\":0}', NULL, '2026-05-11 12:34:06'),
('43', 'group', 'create', 'service_group', '16', NULL, NULL, NULL, NULL, '2', 'System Administrator', NULL, NULL, NULL, 'Service group created.', NULL, '{\"category_id\":6,\"main_service_id\":43,\"group_name\":\"N-SHIELD\",\"show_window_film_details\":0}', NULL, '2026-05-11 12:34:31'),
('44', 'service', 'create', 'service', '51', NULL, NULL, NULL, NULL, '2', 'System Administrator', NULL, NULL, NULL, 'Service created.', NULL, '{\"service_name\":\"เก็บยางมะตอย\",\"service_type\":\"main_service\",\"category_id\":7,\"main_service_id\":0,\"group_id\":0,\"package_vehicle_scope\":\"member\",\"is_quick_service\":0,\"price\":100,\"description\":\"\",\"is_active\":1,\"package_items\":[]}', NULL, '2026-05-11 12:40:31'),
('45', 'service', 'update', 'service', '48', NULL, NULL, NULL, NULL, '2', 'System Administrator', NULL, NULL, NULL, 'Service updated.', '{\"price\":200}', '{\"price\":250}', NULL, '2026-05-11 12:41:44'),
('46', 'service', 'update', 'service', '47', NULL, NULL, NULL, NULL, '2', 'System Administrator', NULL, NULL, NULL, 'Service updated.', '{\"price\":150}', '{\"price\":199.99}', NULL, '2026-05-11 12:42:02'),
('47', 'service', 'update', 'service', '47', NULL, NULL, NULL, NULL, '2', 'System Administrator', NULL, NULL, NULL, 'Service updated.', '{\"price\":199.99}', '{\"price\":200}', NULL, '2026-05-11 12:43:38'),
('48', 'service', 'create', 'service', '52', NULL, NULL, NULL, NULL, '2', 'System Administrator', NULL, NULL, NULL, 'Service created.', NULL, '{\"service_name\":\"ล้างภายนอก + ดูดฝุ่น (L)\",\"service_type\":\"main_service\",\"category_id\":7,\"main_service_id\":0,\"group_id\":0,\"package_vehicle_scope\":\"member\",\"is_quick_service\":1,\"price\":300,\"description\":\"\",\"is_active\":1,\"package_items\":[]}', NULL, '2026-05-11 12:44:23'),
('49', 'service', 'create', 'service', '53', NULL, NULL, NULL, NULL, '2', 'System Administrator', NULL, NULL, NULL, 'Service created.', NULL, '{\"service_name\":\"ล้างห้องเครื่อง\",\"service_type\":\"main_service\",\"category_id\":7,\"main_service_id\":0,\"group_id\":0,\"package_vehicle_scope\":\"member\",\"is_quick_service\":1,\"price\":100,\"description\":\"\",\"is_active\":1,\"package_items\":[]}', NULL, '2026-05-11 12:45:15'),
('50', 'service', 'update', 'service', '45', NULL, NULL, NULL, NULL, '2', 'System Administrator', NULL, NULL, NULL, 'Service updated.', '{\"service_name\":\"VERTEK (หนา220 | ประกัน 5ปี)\"}', '{\"service_name\":\"Pro (หนา220 | ประกัน 5ปี)\"}', NULL, '2026-05-11 12:55:14'),
('51', 'service', 'update', 'service', '44', NULL, NULL, NULL, NULL, '2', 'System Administrator', NULL, NULL, NULL, 'Service updated.', '{\"service_name\":\"VERTEK (หนา200 | ประกัน 4ปี)\"}', '{\"service_name\":\"Plus (หนา200 | ประกัน 4ปี)\"}', NULL, '2026-05-11 12:55:38'),
('52', 'service', 'update', 'service', '46', NULL, NULL, NULL, NULL, '2', 'System Administrator', NULL, NULL, NULL, 'Service updated.', '{\"service_name\":\"VERTEK (หนา250 | ประกัน 7 ปี)\"}', '{\"service_name\":\"Premium (หนา250 | ประกัน 7 ปี)\"}', NULL, '2026-05-11 12:56:20'),
('53', 'service', 'create', 'service', '54', NULL, NULL, NULL, NULL, '2', 'System Administrator', NULL, NULL, NULL, 'Service created.', NULL, '{\"service_name\":\"50G(หนา186 | ประกัน 5 ปี)\",\"service_type\":\"sub_service\",\"category_id\":6,\"main_service_id\":43,\"group_id\":14,\"package_vehicle_scope\":\"member\",\"is_quick_service\":0,\"price\":59900,\"description\":\"(หนา186 | ประกัน 5 ปี)\",\"is_active\":1,\"package_items\":[]}', NULL, '2026-05-11 12:58:12'),
('54', 'service', 'create', 'service', '55', NULL, NULL, NULL, NULL, '2', 'System Administrator', NULL, NULL, NULL, 'Service created.', NULL, '{\"service_name\":\"150G(หนา200 | ประกัน 5 ปี)\",\"service_type\":\"sub_service\",\"category_id\":6,\"main_service_id\":43,\"group_id\":14,\"package_vehicle_scope\":\"member\",\"is_quick_service\":0,\"price\":89900,\"description\":\"(หนา200 | ประกัน 5 ปี)\",\"is_active\":1,\"package_items\":[]}', NULL, '2026-05-11 12:59:15'),
('55', 'service', 'create', 'service', '56', NULL, NULL, NULL, NULL, '2', 'System Administrator', NULL, NULL, NULL, 'Service created.', NULL, '{\"service_name\":\"Ultima(หนา165 | ประกัน 5 ปี)\",\"service_type\":\"sub_service\",\"category_id\":6,\"main_service_id\":43,\"group_id\":15,\"package_vehicle_scope\":\"member\",\"is_quick_service\":0,\"price\":59900,\"description\":\"หนา165 | ประกัน 5 ปี\",\"is_active\":1,\"package_items\":[]}', NULL, '2026-05-11 13:05:57'),
('56', 'service', 'create', 'service', '57', NULL, NULL, NULL, NULL, '2', 'System Administrator', NULL, NULL, NULL, 'Service created.', NULL, '{\"service_name\":\"Xtream(หนา200 | ประกัน 7 ปี)\",\"service_type\":\"sub_service\",\"category_id\":6,\"main_service_id\":43,\"group_id\":15,\"package_vehicle_scope\":\"member\",\"is_quick_service\":0,\"price\":95899.98,\"description\":\"หนา200 | ประกัน 7 ปี\",\"is_active\":1,\"package_items\":[]}', NULL, '2026-05-11 13:07:03'),
('57', 'service', 'create', 'service', '58', NULL, NULL, NULL, NULL, '2', 'System Administrator', NULL, NULL, NULL, 'Service created.', NULL, '{\"service_name\":\"Lite (หนา190 | ประกัน 5 ปี)\",\"service_type\":\"sub_service\",\"category_id\":6,\"main_service_id\":43,\"group_id\":16,\"package_vehicle_scope\":\"member\",\"is_quick_service\":0,\"price\":55899.98,\"description\":\"หนา190 | ประกัน 5 ปี\",\"is_active\":1,\"package_items\":[]}', NULL, '2026-05-11 13:08:05'),
('58', 'service', 'create', 'service', '59', NULL, NULL, NULL, NULL, '2', 'System Administrator', NULL, NULL, NULL, 'Service created.', NULL, '{\"service_name\":\"Pro (หนา190 | ประกัน 10 ปี)\",\"service_type\":\"sub_service\",\"category_id\":6,\"main_service_id\":43,\"group_id\":16,\"package_vehicle_scope\":\"member\",\"is_quick_service\":0,\"price\":85900,\"description\":\"(หนา190 | ประกัน 10 ปี\",\"is_active\":1,\"package_items\":[]}', NULL, '2026-05-11 13:08:59'),
('59', 'service', 'update', 'service', '15', NULL, NULL, NULL, NULL, '2', 'System Administrator', NULL, NULL, NULL, 'Service updated.', '{\"service_name\":\"เคลือบเซรามิค\"}', '{\"service_name\":\"เคลือบเซรามิค 3ปี\"}', NULL, '2026-05-11 13:12:41'),
('60', 'service', 'create', 'service', '60', NULL, NULL, NULL, NULL, '2', 'System Administrator', NULL, NULL, NULL, 'Service created.', NULL, '{\"service_name\":\"เคลือบเซรามิค 4ปี\",\"service_type\":\"main_service\",\"category_id\":6,\"main_service_id\":0,\"group_id\":0,\"package_vehicle_scope\":\"member\",\"is_quick_service\":0,\"price\":7899.98,\"description\":\"\",\"is_active\":1,\"package_items\":[]}', NULL, '2026-05-11 13:13:11'),
('61', 'service', 'create', 'service', '61', NULL, NULL, NULL, NULL, '2', 'System Administrator', NULL, NULL, NULL, 'Service created.', NULL, '{\"service_name\":\"เคลือบเซรามิค 5 ปี\",\"service_type\":\"main_service\",\"category_id\":6,\"main_service_id\":0,\"group_id\":0,\"package_vehicle_scope\":\"member\",\"is_quick_service\":0,\"price\":12899.99,\"description\":\"\",\"is_active\":1,\"package_items\":[]}', NULL, '2026-05-11 13:13:28'),
('62', 'service', 'update', 'service', '60', NULL, NULL, NULL, NULL, '2', 'System Administrator', NULL, NULL, NULL, 'Service updated.', '{\"price\":7899.98}', '{\"price\":7900}', NULL, '2026-05-11 13:13:51'),
('63', 'service', 'update', 'service', '61', NULL, NULL, NULL, NULL, '2', 'System Administrator', NULL, NULL, NULL, 'Service updated.', '{\"price\":12899.99}', '{\"price\":12900}', NULL, '2026-05-11 13:14:03'),
('64', 'service', 'update', 'service', '53', NULL, NULL, NULL, NULL, '2', 'System Administrator', NULL, NULL, NULL, 'Service updated.', '{\"service_name\":\"ล้างห้องเครื่อง\",\"price\":100}', '{\"service_name\":\"Service 500\",\"price\":500}', NULL, '2026-05-11 14:36:02'),
('65', 'service', 'update', 'service', '53', NULL, NULL, NULL, NULL, '2', 'System Administrator', NULL, NULL, NULL, 'Service updated.', '{\"service_name\":\"Service 500\",\"price\":500}', '{\"service_name\":\"Service 1500\",\"price\":1499.99}', NULL, '2026-05-11 14:36:16'),
('66', 'service', 'create', 'service', '62', NULL, NULL, NULL, NULL, '2', 'System Administrator', NULL, NULL, NULL, 'Service created.', NULL, '{\"service_name\":\"Service 500\",\"service_type\":\"main_service\",\"category_id\":7,\"main_service_id\":0,\"group_id\":0,\"package_vehicle_scope\":\"member\",\"is_quick_service\":1,\"price\":500,\"description\":\"\",\"is_active\":1,\"package_items\":[]}', NULL, '2026-05-11 14:37:24'),
('67', 'bill', 'create', 'order', '5', '5', NULL, '3', '3', '3', 'Art New', 'สาขาศรีนครินทร์', 'SRN052026/0005', '31030.00', 'Booking bill created.', NULL, '{\"order_source\":\"booking\",\"status\":\"waiting\",\"payment_status\":\"unpaid\",\"queue_number\":1,\"total_price\":31030,\"deposit_amount\":0,\"admin_deposit_amount\":0,\"branch_deposit_amount\":0,\"pickup_due_at\":\"2026-05-15 14:32:00\",\"service_count\":2,\"service_names\":[\"TPU\",\"Plus (หนา200 | ประกัน 4ปี)\"],\"notes\":\"\"}', '{\"package_usage_count\":0,\"package_purchase_count\":0}', '2026-05-12 14:32:22'),
('68', 'customer', 'create', 'customer', '3', '5', NULL, '3', '3', '3', 'Art New', 'สาขาศรีนครินทร์', 'SRN052026/0005', NULL, 'Customer created from booking.', '{\"name\":null,\"phone\":null}', '{\"name\":\"เฮีย\",\"phone\":\"\"}', '{\"order_source\":\"booking\"}', '2026-05-12 14:32:22'),
('69', 'car', 'create', 'car', '3', '5', NULL, '3', '3', '3', 'Art New', 'สาขาศรีนครินทร์', 'SRN052026/0005', NULL, 'Vehicle created from booking.', '{\"plate_number\":null,\"province\":null,\"vehicle_type\":null,\"brand\":null,\"model\":null,\"color\":null}', '{\"plate_number\":\"ศ-9858\",\"province\":\"Bangkok\",\"vehicle_type\":\"EV\",\"brand\":\"\",\"model\":\"7x\",\"color\":\"ขาว\"}', '{\"order_source\":\"booking\"}', '2026-05-12 14:32:22'),
('70', 'bill', 'create', 'order', '6', '6', NULL, '2', '2', '3', 'Art New', 'สาขาศรีนครินทร์', 'SRN052026/0006', '500.00', 'Quick Service bill created.', NULL, '{\"order_source\":\"quick_service\",\"status\":\"done\",\"payment_status\":\"paid\",\"total_price\":500,\"paid_before\":0,\"paid_after\":500,\"balance_amount\":0,\"service_count\":1,\"service_names\":[\"Service 500\"],\"notes\":\"\"}', '{\"submit_action\":\"pay\",\"payment_method\":\"transfer\"}', '2026-05-12 14:33:47'),
('71', 'payment', 'receive', 'payment', '6', '6', '6', '2', '2', '3', 'Art New', 'สาขาศรีนครินทร์', 'SRN052026/0006', '500.00', 'Quick Service payment received.', '{\"payment_status\":\"unpaid\",\"paid_amount\":0,\"balance_amount\":500}', '{\"payment_method\":\"transfer\",\"recognized_amount\":500,\"received_amount\":500,\"change_amount\":0,\"payment_status\":\"paid\",\"paid_amount\":500,\"balance_amount\":0}', '{\"order_source\":\"quick_service\",\"submit_action\":\"pay\"}', '2026-05-12 14:33:47'),
('72', 'package', 'cancel', 'customer_package', '2', '1', NULL, '1', '1', '3', 'Art New', 'สาขาศรีนครินทร์', 'SRN052026/0001', NULL, 'Package purchase cancelled.', '{\"package_name\":\"ล้าง 10 ครั้ง\",\"vehicle_scope\":\"car\",\"car_id\":1,\"status\":\"active\"}', '{\"status\":\"cancelled\"}', NULL, '2026-05-12 14:34:17'),
('73', 'void', 'cancel', 'order', '1', '1', NULL, '1', '1', '3', 'Art New', 'สาขาศรีนครินทร์', 'SRN052026/0001', '1000.00', 'Bill cancelled.', '{\"order_source\":\"booking\",\"status\":\"waiting\",\"payment_status\":\"paid\",\"total_price\":1000,\"paid_amount\":1000,\"balance_amount\":0}', '{\"status\":\"cancelled\",\"cancelled_by_employee_id\":3,\"cancellation_reason\":\"ยกเลิก\"}', '{\"order_source\":\"booking\"}', '2026-05-12 14:34:17'),
('74', 'queue', 'status', 'order', '5', '5', NULL, '3', '3', '3', 'Art New', 'สาขาศรีนครินทร์', 'SRN052026/0005', '31030.00', 'Queue status updated.', '{\"status\":\"waiting\",\"payment_status\":\"unpaid\"}', '{\"status\":\"washing\",\"payment_status\":\"unpaid\"}', NULL, '2026-05-12 14:34:22'),
('75', 'void', 'cancel', 'order', '2', '2', NULL, '1', '1', '3', 'Art New', 'สาขาศรีนครินทร์', 'SRN052026/0002', '39900.00', 'Bill cancelled.', '{\"order_source\":\"booking\",\"status\":\"waiting\",\"payment_status\":\"partial\",\"total_price\":39900,\"paid_amount\":2900,\"balance_amount\":37000}', '{\"status\":\"cancelled\",\"cancelled_by_employee_id\":3,\"cancellation_reason\":\"ยกเลิก\"}', '{\"order_source\":\"booking\"}', '2026-05-12 14:34:31'),
('76', 'bill', 'create', 'order', '7', '7', NULL, '4', '4', '3', 'Art New', 'สาขาศรีนครินทร์', 'SRN052026/0007', '7900.00', 'Booking bill created.', NULL, '{\"order_source\":\"booking\",\"status\":\"waiting\",\"payment_status\":\"unpaid\",\"queue_number\":2,\"total_price\":7900,\"deposit_amount\":0,\"admin_deposit_amount\":0,\"branch_deposit_amount\":0,\"pickup_due_at\":\"2026-05-12 17:00:00\",\"service_count\":1,\"service_names\":[\"เคลือบเซรามิค 4ปี\"],\"notes\":\"\"}', '{\"package_usage_count\":0,\"package_purchase_count\":0}', '2026-05-12 14:36:02'),
('77', 'customer', 'create', 'customer', '4', '7', NULL, '4', '4', '3', 'Art New', 'สาขาศรีนครินทร์', 'SRN052026/0007', NULL, 'Customer created from booking.', '{\"name\":null,\"phone\":null}', '{\"name\":\"ภัคพงษ์ โพธิชัย\",\"phone\":\"\"}', '{\"order_source\":\"booking\"}', '2026-05-12 14:36:02'),
('78', 'car', 'create', 'car', '4', '7', NULL, '4', '4', '3', 'Art New', 'สาขาศรีนครินทร์', 'SRN052026/0007', NULL, 'Vehicle created from booking.', '{\"plate_number\":null,\"province\":null,\"vehicle_type\":null,\"brand\":null,\"model\":null,\"color\":null}', '{\"plate_number\":\"4ขท-8777\",\"province\":\"Bangkok\",\"vehicle_type\":\"\",\"brand\":\"Toyota\",\"model\":\"Revo\",\"color\":\"ขาว\"}', '{\"order_source\":\"booking\"}', '2026-05-12 14:36:02'),
('79', 'queue', 'status', 'order', '7', '7', NULL, '4', '4', '3', 'Art New', 'สาขาศรีนครินทร์', 'SRN052026/0007', '7900.00', 'Queue status updated.', '{\"status\":\"waiting\",\"payment_status\":\"unpaid\"}', '{\"status\":\"washing\",\"payment_status\":\"unpaid\"}', NULL, '2026-05-12 14:36:07'),
('80', 'bill', 'create', 'order', '8', '8', NULL, '5', '5', '3', 'Art New', 'สาขาศรีนครินทร์', 'SRN052026/0008', '40000.00', 'Booking bill created.', NULL, '{\"order_source\":\"booking\",\"status\":\"waiting\",\"payment_status\":\"partial\",\"queue_number\":3,\"total_price\":40000,\"deposit_amount\":3000,\"admin_deposit_amount\":0,\"branch_deposit_amount\":3000,\"pickup_due_at\":\"2026-05-15 17:00:00\",\"service_count\":3,\"service_names\":[\"ติดฟิล์มกรองแสง\",\"TPU\",\"Plus (หนา200 | ประกัน 4ปี)\"],\"notes\":\"\"}', '{\"package_usage_count\":0,\"package_purchase_count\":0}', '2026-05-12 14:38:49'),
('81', 'payment', 'receive', 'payment', '7', '8', '7', '5', '5', '3', 'Art New', 'สาขาศรีนครินทร์', 'SRN052026/0008', '3000.00', 'Deposit @Branch received at booking.', NULL, '{\"payment_method\":\"cash\",\"recognized_amount\":3000,\"received_amount\":3000,\"change_amount\":0,\"payment_status_after\":\"partial\"}', '{\"deposit_type\":\"branch\",\"order_source\":\"booking\"}', '2026-05-12 14:38:49'),
('82', 'customer', 'create', 'customer', '5', '8', NULL, '5', '5', '3', 'Art New', 'สาขาศรีนครินทร์', 'SRN052026/0008', NULL, 'Customer created from booking.', '{\"name\":null,\"phone\":null}', '{\"name\":\"ตูน ศิรินันท์ วงศ์ชะอุ่ม\",\"phone\":\"\"}', '{\"order_source\":\"booking\"}', '2026-05-12 14:38:49'),
('83', 'car', 'create', 'car', '5', '8', NULL, '5', '5', '3', 'Art New', 'สาขาศรีนครินทร์', 'SRN052026/0008', NULL, 'Vehicle created from booking.', '{\"plate_number\":null,\"province\":null,\"vehicle_type\":null,\"brand\":null,\"model\":null,\"color\":null}', '{\"plate_number\":\"ฌ-2124\",\"province\":\"Bangkok\",\"vehicle_type\":\"EV\",\"brand\":\"\",\"model\":\"Aion V\",\"color\":\"ขาว\"}', '{\"order_source\":\"booking\"}', '2026-05-12 14:38:49'),
('84', 'queue', 'status', 'order', '8', '8', NULL, '5', '5', '3', 'Art New', 'สาขาศรีนครินทร์', 'SRN052026/0008', '40000.00', 'Queue status updated.', '{\"status\":\"waiting\",\"payment_status\":\"partial\"}', '{\"status\":\"washing\",\"payment_status\":\"partial\"}', NULL, '2026-05-12 14:38:55');

-- Structure for table cars
CREATE TABLE `cars` (
  `car_id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int NOT NULL,
  `plate_number` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `province` varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `vehicle_type` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `brand` varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `model` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `color` varchar(50) CHARACTER SET utf8mb4 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;

INSERT INTO `cars` (`car_id`, `customer_id`, `plate_number`, `province`, `vehicle_type`, `brand`, `model`, `color`, `created_at`, `updated_at`) VALUES ('1', '1', '334', 'Bangkok', 'EV', 'BMW', 'Em', 'ฟ้า', '2026-05-10 20:30:33', '2026-05-10 20:40:02'),
('2', '2', 'QS-SRN', '', 'Quick Service', 'Quick Service', 'สาขาศรีนครินทร์', '', '2026-05-10 20:40:51', '2026-05-10 20:40:51'),
('3', '3', 'ศ-9858', 'Bangkok', 'EV', '', '7x', 'ขาว', '2026-05-12 14:32:22', '2026-05-12 14:32:22'),
('4', '4', '4ขท-8777', 'Bangkok', '', 'Toyota', 'Revo', 'ขาว', '2026-05-12 14:36:02', '2026-05-12 14:36:02'),
('5', '5', 'ฌ-2124', 'Bangkok', 'EV', '', 'Aion V', 'ขาว', '2026-05-12 14:38:49', '2026-05-12 14:38:49');

-- 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) CHARACTER SET utf8mb4 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 AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `customer_package_items` (`customer_package_item_id`, `customer_package_id`, `sub_service_id`, `service_name_snapshot`, `included_uses`, `used_uses`, `created_at`) VALUES ('1', '1', '1', 'Standard Wash', '10', '0', '2026-05-10 20:30:33'),
('2', '2', '1', 'Standard Wash', '10', '0', '2026-05-10 20:34:28');

-- 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') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'member',
  `package_service_id` int NOT NULL,
  `order_id` int NOT NULL,
  `package_name_snapshot` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` enum('active','cancelled') CHARACTER SET utf8mb4 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 AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `customer_packages` (`customer_package_id`, `customer_id`, `car_id`, `vehicle_scope`, `package_service_id`, `order_id`, `package_name_snapshot`, `status`, `created_at`, `cancelled_at`) VALUES ('1', '1', '1', 'car', '50', '1', 'ล้าง 10 ครั้ง', 'cancelled', '2026-05-10 20:30:33', '2026-05-10 20:34:28'),
('2', '1', '1', 'car', '50', '1', 'ล้าง 10 ครั้ง', 'cancelled', '2026-05-10 20:34:28', '2026-05-12 14:34:17');

-- Structure for table customers
CREATE TABLE `customers` (
  `customer_id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `phone` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `loyalty_points` int NOT NULL DEFAULT '0',
  `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`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `customers` (`customer_id`, `name`, `phone`, `email`, `loyalty_points`, `created_at`, `updated_at`) VALUES ('1', 'นิว', '092', NULL, '0', '2026-05-10 20:30:33', '2026-05-10 20:40:02'),
('2', 'Quick Service สาขาศรีนครินทร์', '', NULL, '0', '2026-05-10 20:40:51', '2026-05-10 20:40:51'),
('3', 'เฮีย', '', NULL, '0', '2026-05-12 14:32:22', '2026-05-12 14:32:22'),
('4', 'ภัคพงษ์ โพธิชัย', '', NULL, '0', '2026-05-12 14:36:02', '2026-05-12 14:36:02'),
('5', 'ตูน ศิรินันท์ วงศ์ชะอุ่ม', '', NULL, '0', '2026-05-12 14:38:49', '2026-05-12 14:38:49');

-- Structure for table employee_branch_access
CREATE TABLE `employee_branch_access` (
  `employee_id` int NOT NULL,
  `branch_name` varchar(120) CHARACTER SET utf8mb4 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;

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

-- 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') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'earn',
  `description` varchar(255) CHARACTER SET utf8mb4 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 AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- 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) CHARACTER SET utf8mb4 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 CHARACTER SET utf8mb4 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=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `order_details` (`detail_id`, `order_id`, `service_id`, `service_name_snapshot`, `price`, `duration_minutes`, `service_options`) VALUES ('2', '1', '50', 'ล้าง 10 ครั้ง', '1000.00', '0', '{\"package_purchase\":{\"items\":[{\"sub_service_id\":1,\"service_name\":\"Standard Wash\",\"included_uses\":10}]}}'),
('3', '2', '43', 'TPU', '0.00', '0', NULL),
('4', '2', '44', 'VERTEK (หนา200 | ประกัน 4ปี)', '0.00', '0', NULL),
('5', '3', '48', 'ล้างภายนอก + ดูดฝุ่น (M)', '200.00', '0', NULL),
('7', '4', '48', 'ล้างภายนอก + ดูดฝุ่น (M)', '200.00', '0', NULL),
('8', '5', '43', 'TPU', '0.00', '0', NULL),
('9', '5', '44', 'Plus (หนา200 | ประกัน 4ปี)', '0.00', '0', NULL),
('10', '6', '62', 'Service 500', '500.00', '0', NULL),
('11', '7', '60', 'เคลือบเซรามิค 4ปี', '7900.00', '0', NULL),
('12', '8', '11', 'ติดฟิล์มกรองแสง', '5000.00', '0', '{\"type\":\"window_tint\",\"group_id\":7,\"group_name\":\"3M\",\"main_service_id\":11,\"front_service_id\":24,\"front_service_name\":\"3M (IR15)\",\"front_side_service_id\":25,\"front_side_service_name\":\"3M (IR5)\",\"rear_side_service_id\":25,\"rear_side_service_name\":\"3M (IR5)\",\"rear_service_id\":25,\"rear_service_name\":\"3M (IR5)\",\"notes\":\"\"}'),
('13', '8', '43', 'TPU', '0.00', '0', NULL),
('14', '8', '44', 'Plus (หนา200 | ประกัน 4ปี)', '0.00', '0', NULL);

-- 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) CHARACTER SET utf8mb4 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;


-- Structure for table orders
CREATE TABLE `orders` (
  `order_id` int NOT NULL AUTO_INCREMENT,
  `bill_number` varchar(30) CHARACTER SET utf8mb4 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) CHARACTER SET utf8mb4 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 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `reopened_from_order_id` int DEFAULT NULL,
  `status` enum('waiting','washing','done','cancelled') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'waiting',
  `payment_status` enum('unpaid','partial','paid') CHARACTER SET utf8mb4 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) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `admin_deposit_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `admin_deposit_method` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `branch_deposit_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `branch_deposit_method` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `notes` text CHARACTER SET utf8mb4 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=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `orders` (`order_id`, `bill_number`, `customer_id`, `car_id`, `employee_id`, `branch_name`, `order_date`, `scheduled_at`, `return_at`, `order_source`, `started_at`, `finished_at`, `cancelled_at`, `cancelled_by_employee_id`, `cancellation_reason`, `reopened_from_order_id`, `status`, `payment_status`, `total_price`, `deposit_amount`, `deposit_method`, `admin_deposit_amount`, `admin_deposit_method`, `branch_deposit_amount`, `branch_deposit_method`, `notes`, `updated_at`) VALUES ('1', 'SRN052026/0001', '1', '1', NULL, 'สาขาศรีนครินทร์', '2026-05-10 20:30:33', NULL, '2026-05-11 20:24:00', 'booking', NULL, NULL, '2026-05-12 14:34:17', '3', 'ยกเลิก', NULL, 'cancelled', 'paid', '1000.00', '0.00', NULL, '0.00', 'qr', '0.00', 'cash', '', '2026-05-12 14:34:17'),
('2', 'SRN052026/0002', '1', '1', NULL, 'สาขาศรีนครินทร์', '2026-05-10 20:40:02', NULL, NULL, 'booking', NULL, NULL, '2026-05-12 14:34:31', '3', 'ยกเลิก', NULL, 'cancelled', 'partial', '39900.00', '2900.00', 'transfer', '2900.00', 'transfer', '0.00', 'cash', '', '2026-05-12 14:34:31'),
('3', 'SRN052026/0003', '2', '2', '2', 'สาขาศรีนครินทร์', '2026-05-10 20:40:51', NULL, NULL, 'quick_service', NULL, '2026-05-10 20:40:51', NULL, NULL, NULL, NULL, 'done', 'paid', '200.00', '0.00', NULL, '0.00', NULL, '0.00', NULL, '', '2026-05-10 20:40:51'),
('4', 'SRN052026/0004', '2', '2', '2', 'สาขาศรีนครินทร์', '2026-05-10 20:42:31', NULL, NULL, 'quick_service', NULL, '2026-05-10 20:43:15', NULL, NULL, NULL, NULL, 'done', 'paid', '200.00', '0.00', NULL, '0.00', NULL, '0.00', NULL, '', '2026-05-10 20:43:15'),
('5', 'SRN052026/0005', '3', '3', NULL, 'สาขาศรีนครินทร์', '2026-05-12 14:32:22', NULL, '2026-05-15 14:32:00', 'booking', '2026-05-12 14:34:22', NULL, NULL, NULL, NULL, NULL, 'washing', 'unpaid', '31030.00', '0.00', NULL, '0.00', 'cash', '0.00', 'cash', '', '2026-05-12 14:34:22'),
('6', 'SRN052026/0006', '2', '2', '3', 'สาขาศรีนครินทร์', '2026-05-12 14:33:47', NULL, NULL, 'quick_service', NULL, '2026-05-12 14:33:47', NULL, NULL, NULL, NULL, 'done', 'paid', '500.00', '0.00', NULL, '0.00', NULL, '0.00', NULL, '', '2026-05-12 14:33:47'),
('7', 'SRN052026/0007', '4', '4', NULL, 'สาขาศรีนครินทร์', '2026-05-12 14:36:02', NULL, '2026-05-12 17:00:00', 'booking', '2026-05-12 14:36:07', NULL, NULL, NULL, NULL, NULL, 'washing', 'unpaid', '7900.00', '0.00', NULL, '0.00', 'cash', '0.00', 'cash', '', '2026-05-12 14:36:07'),
('8', 'SRN052026/0008', '5', '5', NULL, 'สาขาศรีนครินทร์', '2026-05-12 14:38:49', NULL, '2026-05-15 17:00:00', 'booking', '2026-05-12 14:38:55', NULL, NULL, NULL, NULL, NULL, 'washing', 'partial', '40000.00', '3000.00', 'cash', '0.00', 'cash', '3000.00', 'cash', '', '2026-05-12 14:38:55');

-- 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') CHARACTER SET utf8mb4 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) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci 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;

INSERT INTO `payments` (`payment_id`, `order_id`, `payment_method`, `amount`, `received_amount`, `change_amount`, `note`, `payment_date`) VALUES ('1', '1', 'cash', '1000.00', '1000.00', '0.00', '', '2026-05-10 20:35:10'),
('2', '2', 'transfer', '2900.00', '2900.00', '0.00', 'Admin deposit at booking', '2026-05-10 20:40:02'),
('3', '3', 'transfer', '200.00', '200.00', '0.00', 'Quick Service payment', '2026-05-10 20:40:51'),
('4', '4', 'transfer', '100.00', '100.00', '0.00', 'Quick Service payment', '2026-05-10 20:42:31'),
('5', '4', 'transfer', '100.00', '100.00', '0.00', 'Quick Service payment', '2026-05-10 20:43:15'),
('6', '6', 'transfer', '500.00', '500.00', '0.00', 'Quick Service payment', '2026-05-12 14:33:47'),
('7', '8', 'cash', '3000.00', '3000.00', '0.00', 'Branch deposit at booking', '2026-05-12 14:38:49');

-- Structure for table positions
CREATE TABLE `positions` (
  `position_id` int NOT NULL AUTO_INCREMENT,
  `position_name` varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `role_key` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `branch_scope` enum('all','assigned') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'assigned',
  `permissions` text CHARACTER SET utf8mb4 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=26872 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `positions` (`position_id`, `position_name`, `role_key`, `branch_scope`, `permissions`, `is_active`, `created_at`) VALUES ('1', 'Administrator', 'admin', 'all', '[\"dashboard\",\"booking\",\"queue\",\"queue_bill_control\",\"queue_cancel_closed\",\"queue_cancel_open\",\"queue_edit_open\",\"services\",\"member\",\"report_service\",\"sale_report\",\"void_report\",\"loyalty\",\"employees\"]', '1', '2026-04-22 16:23:55'),
('2', 'Branch Manager', 'manager', 'assigned', '[\"dashboard\",\"booking\",\"booking_deposit_branch\",\"quick_service\",\"quick_service_payment\",\"queue\",\"queue_payment\",\"queue_start_waiting\",\"queue_cancel_waiting\",\"report_service\",\"sale_report\"]', '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');

-- Structure for table products
CREATE TABLE `products` (
  `product_id` int NOT NULL AUTO_INCREMENT,
  `product_name` varchar(120) CHARACTER SET utf8mb4 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;


-- 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') CHARACTER SET utf8mb4 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=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `queue` (`queue_id`, `order_id`, `queue_number`, `status`, `created_at`, `called_at`, `completed_at`) VALUES ('1', '1', '1', 'cancelled', '2026-05-10 20:30:33', NULL, NULL),
('2', '2', '2', 'cancelled', '2026-05-10 20:40:02', NULL, NULL),
('3', '5', '1', 'washing', '2026-05-12 14:32:22', '2026-05-12 14:34:22', NULL),
('4', '7', '2', 'washing', '2026-05-12 14:36:02', '2026-05-12 14:36:07', NULL),
('5', '8', '3', 'washing', '2026-05-12 14:38:49', '2026-05-12 14:38:55', NULL);

-- Structure for table service_categories
CREATE TABLE `service_categories` (
  `category_id` int NOT NULL AUTO_INCREMENT,
  `category_name` varchar(60) CHARACTER SET utf8mb4 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=14302 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `service_categories` (`category_id`, `category_name`, `created_at`) VALUES ('1', 'Add-on', '2026-04-28 13:34:22'),
('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');

-- 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) CHARACTER SET utf8mb4 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=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `service_groups` (`group_id`, `category_id`, `main_service_id`, `group_name`, `show_window_film_details`, `created_at`) 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'),
('14', '6', '43', 'm3', '0', '2026-05-11 12:33:08'),
('15', '6', '43', 'AVERY', '0', '2026-05-11 12:34:06'),
('16', '6', '43', 'N-SHIELD', '0', '2026-05-11 12:34:31');

-- 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=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `service_package_items` (`package_item_id`, `package_service_id`, `sub_service_id`, `included_uses`, `created_at`) VALUES ('7', '16', '1', '2', '2026-04-30 11:33:25'),
('9', '50', '1', '10', '2026-05-10 20:22:08'),
('10', '15', '1', '10', '2026-05-11 13:12:41');

-- Structure for table services
CREATE TABLE `services` (
  `service_id` int NOT NULL AUTO_INCREMENT,
  `service_name` varchar(120) CHARACTER SET utf8mb4 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) CHARACTER SET utf8mb4 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 CHARACTER SET utf8mb4 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=63 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `services` (`service_id`, `service_name`, `service_type`, `category`, `category_id`, `main_service_id`, `group_id`, `package_vehicle_scope`, `is_quick_service`, `price`, `duration_minutes`, `description`, `is_active`, `created_at`, `updated_at`) 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'),
('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', 'เคลือบเซรามิค 3ปี', 'package', 'Protection', '6', NULL, NULL, 'car', '0', '5900.00', '0', '', '1', '2026-04-28 17:18:03', '2026-05-11 13:12:41'),
('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', 'Plus (หนา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-05-11 12:55:38'),
('45', 'Pro (หนา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-05-11 12:55:14'),
('46', 'Premium (หนา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-05-11 12:56:20'),
('47', 'ล้างภายนอก + ดูดฝุ่น (S)', 'main_service', 'Wash', '7', NULL, NULL, 'member', '1', '200.00', '0', '', '1', '2026-05-04 12:48:44', '2026-05-11 12:43:38'),
('48', 'ล้างภายนอก + ดูดฝุ่น (M)', 'main_service', 'Wash', '7', NULL, NULL, 'member', '1', '250.00', '0', '', '1', '2026-05-04 12:49:00', '2026-05-11 12:41:44'),
('50', 'ล้าง 10 ครั้ง', 'package', 'Wash', '7', NULL, NULL, 'car', '0', '1000.00', '0', '', '1', '2026-05-10 20:22:08', '2026-05-10 20:22:08'),
('51', 'เก็บยางมะตอย', 'main_service', 'Wash', '7', NULL, NULL, 'member', '0', '100.00', '0', '', '1', '2026-05-11 12:40:31', '2026-05-11 12:40:31'),
('52', 'ล้างภายนอก + ดูดฝุ่น (L)', 'main_service', 'Wash', '7', NULL, NULL, 'member', '1', '300.00', '0', '', '1', '2026-05-11 12:44:23', '2026-05-11 12:44:23'),
('53', 'Service 1500', 'main_service', 'Wash', '7', NULL, NULL, 'member', '1', '1499.99', '0', '', '1', '2026-05-11 12:45:15', '2026-05-11 14:36:16'),
('54', '50G(หนา186 | ประกัน 5 ปี)', 'sub_service', 'Protection', '6', '43', '14', 'member', '0', '59900.00', '0', '(หนา186 | ประกัน 5 ปี)', '1', '2026-05-11 12:58:12', '2026-05-11 12:58:12'),
('55', '150G(หนา200 | ประกัน 5 ปี)', 'sub_service', 'Protection', '6', '43', '14', 'member', '0', '89900.00', '0', '(หนา200 | ประกัน 5 ปี)', '1', '2026-05-11 12:59:15', '2026-05-11 12:59:15'),
('56', 'Ultima(หนา165 | ประกัน 5 ปี)', 'sub_service', 'Protection', '6', '43', '15', 'member', '0', '59900.00', '0', 'หนา165 | ประกัน 5 ปี', '1', '2026-05-11 13:05:57', '2026-05-11 13:05:57'),
('57', 'Xtream(หนา200 | ประกัน 7 ปี)', 'sub_service', 'Protection', '6', '43', '15', 'member', '0', '95899.98', '0', 'หนา200 | ประกัน 7 ปี', '1', '2026-05-11 13:07:03', '2026-05-11 13:07:03'),
('58', 'Lite (หนา190 | ประกัน 5 ปี)', 'sub_service', 'Protection', '6', '43', '16', 'member', '0', '55899.98', '0', 'หนา190 | ประกัน 5 ปี', '1', '2026-05-11 13:08:05', '2026-05-11 13:08:05'),
('59', 'Pro (หนา190 | ประกัน 10 ปี)', 'sub_service', 'Protection', '6', '43', '16', 'member', '0', '85900.00', '0', '(หนา190 | ประกัน 10 ปี', '1', '2026-05-11 13:08:59', '2026-05-11 13:08:59'),
('60', 'เคลือบเซรามิค 4ปี', 'main_service', 'Protection', '6', NULL, NULL, 'member', '0', '7900.00', '0', '', '1', '2026-05-11 13:13:11', '2026-05-11 13:13:51'),
('61', 'เคลือบเซรามิค 5 ปี', 'main_service', 'Protection', '6', NULL, NULL, 'member', '0', '12900.00', '0', '', '1', '2026-05-11 13:13:28', '2026-05-11 13:14:03'),
('62', 'Service 500', 'main_service', 'Wash', '7', NULL, NULL, 'member', '1', '500.00', '0', '', '1', '2026-05-11 14:37:24', '2026-05-11 14:37:24');

-- Structure for table system_maintenance_logs
CREATE TABLE `system_maintenance_logs` (
  `maintenance_log_id` int NOT NULL AUTO_INCREMENT,
  `maintenance_type` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'completed',
  `actor_employee_id` int DEFAULT NULL,
  `actor_name` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `branch_name` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `snapshot_path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `filters_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `preview_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `result_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `note` varchar(255) CHARACTER SET utf8mb4 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=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `system_maintenance_logs` (`maintenance_log_id`, `maintenance_type`, `status`, `actor_employee_id`, `actor_name`, `branch_name`, `snapshot_path`, `filters_json`, `preview_json`, `result_json`, `note`, `created_at`) VALUES ('1', 'reset_operational_data', 'completed', '2', 'System Administrator', NULL, 'storage/maintenance_exports/reset_operational_data_20260508_225251_19162dd7.json', '{\"include_logs\":true,\"reset_counters\":true}', '{\"summary\":{\"selected_rows\":255,\"log_rows\":27,\"order_count\":48,\"customer_count\":10,\"car_count\":13},\"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\":19,\"payments\":52,\"queue\":24,\"order_details\":54,\"orders\":48,\"cars\":13,\"customers\":10,\"audit_logs\":23,\"system_maintenance_logs\":5},\"filters\":{\"include_logs\":true,\"reset_counters\":true},\"pre_reset_backup\":{\"file_name\":\"pre_reset_operational_ccw_20260508_225251.sql\",\"relative_path\":\"storage/database_backups/pre_reset_operational_ccw_20260508_225251.sql\",\"size_bytes\":81790,\"method\":\"php\"},\"reset_counters\":true}', 'Operational data was fully reset while master configuration data was preserved.', '2026-05-08 22:52:51'),
('2', 'backup_database', 'completed', '2', 'System Administrator', NULL, 'storage/database_backups/manual_ccw_20260514_144429.sql', '{\"database\":\"CCW\",\"backup_prefix\":\"manual\",\"method\":\"php\"}', NULL, '{\"file_name\":\"manual_ccw_20260514_144429.sql\",\"size_bytes\":81003,\"method\":\"php\"}', 'Database backup created by native PHP exporter.', '2026-05-14 14:44:29');


