--
-- Host: localhost    Database: petzy_pos
-- ------------------------------------------------------


--
-- Current Database: `petzy_pos`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `petzy_pos` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */;

USE `petzy_pos`;

--
-- Table structure for table `attendance`
--

CREATE TABLE `attendance` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `employee_id` int(10) unsigned NOT NULL,
  `outlet_id` int(10) unsigned NOT NULL,
  `attendance_date` date NOT NULL,
  `check_in` time DEFAULT NULL,
  `check_out` time DEFAULT NULL,
  `status` enum('present','late','half_day','absent','on_leave','week_off','holiday','comp_worked','overtime') NOT NULL DEFAULT 'present',
  `notes` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_attendance_emp_date` (`employee_id`,`attendance_date`),
  CONSTRAINT `fk_att_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `attendance`
--

INSERT INTO `attendance` VALUES (1,3,1,'2026-08-02',NULL,NULL,'week_off',NULL),(2,3,1,'2026-08-09',NULL,NULL,'week_off',NULL),(3,3,1,'2026-08-15',NULL,NULL,'holiday','Independence Day'),(4,3,1,'2026-08-16',NULL,NULL,'week_off',NULL),(5,3,1,'2026-08-23',NULL,NULL,'comp_worked',NULL),(6,3,1,'2026-08-30',NULL,NULL,'week_off',NULL),(7,3,1,'2026-08-03',NULL,NULL,'present',NULL),(8,3,1,'2026-08-04',NULL,NULL,'present',NULL),(9,3,1,'2026-08-05',NULL,NULL,'present',NULL),(10,3,1,'2026-08-06',NULL,NULL,'present',NULL),(11,3,1,'2026-08-07',NULL,NULL,'present',NULL),(12,3,1,'2026-08-10',NULL,NULL,'present',NULL),(13,3,1,'2026-08-11',NULL,NULL,'present',NULL),(14,3,1,'2026-08-12',NULL,NULL,'present',NULL),(15,3,1,'2026-08-13',NULL,NULL,'present',NULL),(16,3,1,'2026-08-14',NULL,NULL,'present',NULL),(17,3,1,'2026-08-17',NULL,NULL,'present',NULL),(18,3,1,'2026-08-18',NULL,NULL,'present',NULL),(19,3,1,'2026-08-19',NULL,NULL,'present',NULL),(20,3,1,'2026-08-20',NULL,NULL,'present',NULL),(21,3,1,'2026-08-21',NULL,NULL,'present',NULL),(22,3,1,'2026-08-24',NULL,NULL,'half_day',NULL),(23,3,1,'2026-08-25',NULL,NULL,'absent',NULL),(25,3,1,'2026-09-01',NULL,NULL,'on_leave',NULL),(26,3,1,'2026-09-02',NULL,NULL,'on_leave',NULL);

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

CREATE TABLE `audit_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(10) unsigned DEFAULT NULL,
  `user_id` int(10) unsigned DEFAULT NULL,
  `action` varchar(50) NOT NULL,
  `module` varchar(50) NOT NULL,
  `record_id` bigint(20) unsigned DEFAULT NULL,
  `old_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`old_data`)),
  `new_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`new_data`)),
  `ip_address` varchar(60) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_audit_company_module` (`company_id`,`module`)
) ENGINE=InnoDB AUTO_INCREMENT=60 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `audit_logs` VALUES (1,1,1,'create','pos',NULL,NULL,'{\"outlet_id\":1,\"order_type\":\"counter_sale\",\"items\":[{\"item_id\":3,\"quantity\":1}]}','::1','curl/8.21.0','2026-08-14 15:42:37'),(2,1,1,'create','pos',1,NULL,'{}','::1','curl/8.21.0','2026-08-14 15:42:37'),(3,1,1,'create','payments',NULL,NULL,'{\"order_id\":1,\"payments\":[{\"method\":\"cash\",\"amount\":360.4}]}','::1','curl/8.21.0','2026-08-14 15:42:37'),(4,1,1,'create','purchases',NULL,NULL,'{\"outlet_id\":1,\"supplier_id\":1,\"supplier_invoice_number\":\"SUP-TEST-1\",\"items\":[{\"inventory_item_id\":1,\"quantity\":10,\"unit_price\":320,\"tax_id\":3}]}','::1','curl/8.21.0','2026-08-14 15:42:37'),(5,1,1,'create','menu',NULL,NULL,'{\"name\":\"Hot Drinks\",\"outlet_id\":1,\"parent_id\":4,\"sort_order\":1}','::1','curl/8.21.0','2026-08-23 06:13:02'),(6,1,1,'delete','menu',6,NULL,NULL,'::1','curl/8.21.0','2026-08-23 06:13:16'),(7,1,1,'create','pos',NULL,NULL,'{\"outlet_id\":1,\"order_type\":\"counter_sale\",\"items\":[{\"item_id\":5,\"quantity\":2}]}','::1','curl/8.21.0','2026-08-23 09:28:54'),(8,1,1,'create','pos',2,NULL,'{}','::1','curl/8.21.0','2026-08-23 09:28:54'),(9,1,1,'create','payments',NULL,NULL,'{\"order_id\":2,\"payments\":[{\"amount\":123,\"method\":\"cash\"}]}','::1','curl/8.21.0','2026-08-23 09:30:37'),(10,2,7,'create','pos',NULL,NULL,'{\"outlet_id\":2,\"order_type\":\"counter_sale\",\"items\":[{\"item_id\":30,\"quantity\":2}]}','::1','curl/8.21.0','2026-08-24 06:17:43'),(11,2,7,'create','payments',NULL,NULL,'{\"order_id\":3,\"payments\":[{\"method\":\"cash\",\"amount\":80}]}','::1','curl/8.21.0','2026-08-24 06:17:43'),(12,2,7,'create','pos',NULL,NULL,'{\"outlet_id\":3,\"order_type\":\"counter_sale\",\"items\":[{\"item_id\":30,\"quantity\":1}]}','::1','curl/8.21.0','2026-08-24 06:17:44'),(13,2,7,'create','payments',NULL,NULL,'{\"order_id\":4,\"payments\":[{\"method\":\"cash\",\"amount\":40}]}','::1','curl/8.21.0','2026-08-24 06:17:44'),(14,2,7,'create','outlets',NULL,NULL,'{\"name\":\"Udupi Tiffin � Test Outlet\",\"code\":\"UTC99\",\"type\":\"restaurant\",\"invoice_prefix\":\"INV\",\"kot_prefix\":\"KOT\"}','::1','curl/8.21.0','2026-08-24 06:18:12'),(15,2,7,'update','companies',2,NULL,'{\"invoice_footer\":\"Thank you for dining with us!\"}','::1','curl/8.21.0','2026-08-24 06:18:13'),(16,2,7,'create','users',NULL,NULL,'{\"name\":\"Test Cashier\",\"email\":\"test.cashier.smoketest@udupitiffin.example\",\"password\":\"Passw0rd123\",\"role_id\":13,\"outlet_ids\":[2]}','::1','curl/8.21.0','2026-08-24 06:18:23'),(17,2,7,'delete','users',12,NULL,NULL,'::1','curl/8.21.0','2026-08-24 06:18:52'),(18,2,7,'delete','outlets',9,NULL,NULL,'::1','curl/8.21.0','2026-08-24 06:18:52'),(19,1,1,'create','employees',3,NULL,'{\"month\":8,\"year\":2026}','::1','curl/8.21.0','2026-08-24 06:40:36'),(20,1,1,'create','employees',3,NULL,'{\"outlet_id\":1,\"attendance_date\":\"2026-08-03\",\"status\":\"present\"}','::1','curl/8.21.0','2026-08-24 06:40:36'),(21,1,1,'create','employees',3,NULL,'{\"outlet_id\":1,\"attendance_date\":\"2026-08-04\",\"status\":\"present\"}','::1','curl/8.21.0','2026-08-24 06:40:37'),(22,1,1,'create','employees',3,NULL,'{\"outlet_id\":1,\"attendance_date\":\"2026-08-05\",\"status\":\"present\"}','::1','curl/8.21.0','2026-08-24 06:40:37'),(23,1,1,'create','employees',3,NULL,'{\"outlet_id\":1,\"attendance_date\":\"2026-08-06\",\"status\":\"present\"}','::1','curl/8.21.0','2026-08-24 06:40:37'),(24,1,1,'create','employees',3,NULL,'{\"outlet_id\":1,\"attendance_date\":\"2026-08-07\",\"status\":\"present\"}','::1','curl/8.21.0','2026-08-24 06:40:37'),(25,1,1,'create','employees',3,NULL,'{\"outlet_id\":1,\"attendance_date\":\"2026-08-10\",\"status\":\"present\"}','::1','curl/8.21.0','2026-08-24 06:40:37'),(26,1,1,'create','employees',3,NULL,'{\"outlet_id\":1,\"attendance_date\":\"2026-08-11\",\"status\":\"present\"}','::1','curl/8.21.0','2026-08-24 06:40:37'),(27,1,1,'create','employees',3,NULL,'{\"outlet_id\":1,\"attendance_date\":\"2026-08-12\",\"status\":\"present\"}','::1','curl/8.21.0','2026-08-24 06:40:37'),(28,1,1,'create','employees',3,NULL,'{\"outlet_id\":1,\"attendance_date\":\"2026-08-13\",\"status\":\"present\"}','::1','curl/8.21.0','2026-08-24 06:40:37'),(29,1,1,'create','employees',3,NULL,'{\"outlet_id\":1,\"attendance_date\":\"2026-08-14\",\"status\":\"present\"}','::1','curl/8.21.0','2026-08-24 06:40:38'),(30,1,1,'create','employees',3,NULL,'{\"outlet_id\":1,\"attendance_date\":\"2026-08-17\",\"status\":\"present\"}','::1','curl/8.21.0','2026-08-24 06:40:38'),(31,1,1,'create','employees',3,NULL,'{\"outlet_id\":1,\"attendance_date\":\"2026-08-18\",\"status\":\"present\"}','::1','curl/8.21.0','2026-08-24 06:40:38'),(32,1,1,'create','employees',3,NULL,'{\"outlet_id\":1,\"attendance_date\":\"2026-08-19\",\"status\":\"present\"}','::1','curl/8.21.0','2026-08-24 06:40:38'),(33,1,1,'create','employees',3,NULL,'{\"outlet_id\":1,\"attendance_date\":\"2026-08-20\",\"status\":\"present\"}','::1','curl/8.21.0','2026-08-24 06:40:38'),(34,1,1,'create','employees',3,NULL,'{\"outlet_id\":1,\"attendance_date\":\"2026-08-21\",\"status\":\"present\"}','::1','curl/8.21.0','2026-08-24 06:40:38'),(35,1,1,'create','employees',3,NULL,'{\"outlet_id\":1,\"attendance_date\":\"2026-08-24\",\"status\":\"half_day\"}','::1','curl/8.21.0','2026-08-24 06:40:38'),(36,1,1,'create','employees',3,NULL,'{\"outlet_id\":1,\"attendance_date\":\"2026-08-25\",\"status\":\"absent\"}','::1','curl/8.21.0','2026-08-24 06:40:38'),(37,1,1,'create','employees',3,NULL,'{\"outlet_id\":1,\"attendance_date\":\"2026-08-23\",\"status\":\"comp_worked\"}','::1','curl/8.21.0','2026-08-24 06:40:38'),(38,1,1,'create','payroll',NULL,NULL,'{\"outlet_id\":1,\"month\":8,\"year\":2026}','::1','curl/8.21.0','2026-08-24 06:40:56'),(39,1,1,'create','leaves',NULL,NULL,'{\"employee_id\":3,\"leave_type_id\":1,\"from_date\":\"2026-09-01\",\"to_date\":\"2026-09-02\",\"reason\":\"Personal\"}','::1','curl/8.21.0','2026-08-24 06:41:09'),(40,1,1,'update','leaves',1,NULL,'{}','::1','curl/8.21.0','2026-08-24 06:41:09'),(41,1,1,'update','payroll',1,NULL,'{\"payment_method\":\"bank_transfer\",\"payment_reference\":\"TESTPAY001\"}','::1','curl/8.21.0','2026-08-24 06:41:24'),(42,1,1,'update','payroll',1,NULL,'{}','::1','curl/8.21.0','2026-08-24 06:41:25'),(43,1,1,'create','pos',NULL,NULL,'{\"outlet_id\":1,\"order_type\":\"counter_sale\",\"items\":[{\"item_id\":6,\"quantity\":1}],\"extra_amount\":25,\"extra_charge_label\":\"Packing Charge\"}','::1','curl/8.21.0','2026-08-24 07:10:24'),(44,1,1,'update','pos',5,NULL,'{\"discount\":{\"name\":\"Loyalty 10%\",\"type\":\"percentage\",\"value\":10},\"extra_amount\":25,\"extra_charge_label\":\"Packing Charge\"}','::1','curl/8.21.0','2026-08-24 07:10:38'),(45,1,1,'update','pos',5,NULL,'{\"discount\":null,\"extra_amount\":0}','::1','curl/8.21.0','2026-08-24 07:10:39'),(46,1,1,'create','payments',NULL,NULL,'{\"order_id\":5,\"payments\":[{\"method\":\"cash\",\"amount\":50},{\"method\":\"upi\",\"amount\":34,\"reference_number\":\"UPI-XYZ\"}]}','::1','curl/8.21.0','2026-08-24 07:10:39'),(47,1,1,'update','tables',5,NULL,'{\"name\":\"RT1-test\"}','::1','curl/8.21.0','2026-08-24 07:11:25'),(48,1,1,'update','tables',5,NULL,'{\"name\":\"RT1\"}','::1','curl/8.21.0','2026-08-24 07:11:25'),(49,4,9,'create','pos',NULL,NULL,'{\"outlet_id\":5,\"order_type\":\"counter_sale\",\"table_id\":null,\"items\":[{\"item_id\":64,\"variation_id\":null,\"addon_ids\":[],\"modifier_ids\":[],\"quantity\":2}]}','::1','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/151.0.0.0 Safari/537.36','2026-08-24 07:21:15'),(50,4,9,'create','pos',NULL,NULL,'{\"outlet_id\":5,\"order_type\":\"counter_sale\",\"table_id\":null,\"items\":[{\"item_id\":59,\"variation_id\":null,\"addon_ids\":[],\"modifier_ids\":[],\"quantity\":1}]}','::1','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/151.0.0.0 Safari/537.36','2026-08-24 07:22:37'),(51,4,9,'create','pos',NULL,NULL,'{\"outlet_id\":5,\"order_type\":\"counter_sale\",\"table_id\":null,\"items\":[{\"item_id\":59,\"variation_id\":null,\"addon_ids\":[],\"modifier_ids\":[],\"quantity\":1}]}','::1','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/151.0.0.0 Safari/537.36','2026-08-24 07:22:43'),(52,1,1,'create','customers',NULL,NULL,'{\"name\":\"Test Customer Hold\",\"mobile\":\"9999900001\",\"email\":\"holdtest@example.com\"}','::1','curl/8.21.0','2026-08-24 07:37:02'),(53,1,1,'create','pos',NULL,NULL,'{\"outlet_id\":1,\"order_type\":\"counter_sale\",\"items\":[{\"item_id\":6,\"quantity\":1}],\"customer_id\":2}','::1','curl/8.21.0','2026-08-24 07:37:03'),(54,1,1,'create','pos',9,NULL,'{}','::1','curl/8.21.0','2026-08-24 07:37:04'),(55,1,1,'create','pos',9,NULL,'{}','::1','curl/8.21.0','2026-08-24 07:37:04'),(56,1,1,'update','pos',9,NULL,'{\"customer_id\":null}','::1','curl/8.21.0','2026-08-24 07:37:04'),(57,1,1,'create','pos',9,NULL,'{\"reason\":\"test cleanup\"}','::1','curl/8.21.0','2026-08-24 07:37:57'),(58,4,9,'create','pos',NULL,NULL,'{\"outlet_id\":5,\"order_type\":\"counter_sale\",\"table_id\":null,\"items\":[{\"item_id\":64,\"variation_id\":null,\"addon_ids\":[],\"modifier_ids\":[],\"quantity\":1},{\"item_id\":73,\"variation_id\":null,\"addon_ids\":[],\"modifier_ids\":[],\"quantity\":1}]}','::1','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/151.0.0.0 Safari/537.36','2026-08-25 09:06:52'),(59,4,9,'create','payments',NULL,NULL,'{\"order_id\":10,\"payments\":[{\"method\":\"cash\",\"amount\":159}]}','::1','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/151.0.0.0 Safari/537.36','2026-08-25 09:07:05');

--
-- Table structure for table `backup_logs`
--

CREATE TABLE `backup_logs` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(10) unsigned DEFAULT NULL,
  `type` enum('database','media') NOT NULL DEFAULT 'database',
  `file_name` varchar(255) DEFAULT NULL,
  `status` enum('success','failed') NOT NULL DEFAULT 'success',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `backup_logs`
--


--
-- Table structure for table `cash_registers`
--

CREATE TABLE `cash_registers` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `outlet_id` int(10) unsigned NOT NULL,
  `opened_by` int(10) unsigned NOT NULL,
  `closed_by` int(10) unsigned DEFAULT NULL,
  `opening_cash` decimal(12,2) NOT NULL DEFAULT 0.00,
  `expected_cash` decimal(12,2) NOT NULL DEFAULT 0.00,
  `actual_cash` decimal(12,2) DEFAULT NULL,
  `difference` decimal(12,2) DEFAULT NULL,
  `status` enum('open','closed') NOT NULL DEFAULT 'open',
  `opened_at` datetime NOT NULL DEFAULT current_timestamp(),
  `closed_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_cr_outlet` (`outlet_id`),
  CONSTRAINT `fk_cr_outlet` FOREIGN KEY (`outlet_id`) REFERENCES `outlets` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `cash_registers`
--


--
-- Table structure for table `cash_transactions`
--

CREATE TABLE `cash_transactions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `cash_register_id` int(10) unsigned NOT NULL,
  `type` enum('sale','expense','refund','deposit','withdrawal') NOT NULL,
  `amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `reference_type` varchar(50) DEFAULT NULL,
  `reference_id` bigint(20) unsigned DEFAULT NULL,
  `notes` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_ct_register` (`cash_register_id`),
  CONSTRAINT `fk_ct_register` FOREIGN KEY (`cash_register_id`) REFERENCES `cash_registers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `cash_transactions`
--


--
-- Table structure for table `companies`
--

CREATE TABLE `companies` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  `legal_name` varchar(150) DEFAULT NULL,
  `logo` varchar(255) DEFAULT NULL,
  `email` varchar(150) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `state` varchar(100) DEFAULT NULL,
  `country` varchar(100) DEFAULT 'India',
  `pincode` varchar(20) DEFAULT NULL,
  `gstin` varchar(20) DEFAULT NULL,
  `pan` varchar(20) DEFAULT NULL,
  `currency` varchar(10) NOT NULL DEFAULT 'INR',
  `timezone` varchar(60) NOT NULL DEFAULT 'Asia/Kolkata',
  `invoice_footer` varchar(255) DEFAULT NULL,
  `terms` text DEFAULT NULL,
  `status` enum('active','inactive','suspended') NOT NULL DEFAULT 'active',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `companies`
--

INSERT INTO `companies` VALUES (1,'PetZY Hospitality','PetZY Hospitality Pvt Ltd',NULL,'contact@petzyhospitality.example','+91-9876500001','12 MG Road','Bengaluru','Karnataka','India','560001','29ABCDE1234F1Z5','ABCDE1234F','INR','Asia/Kolkata','Thank you for visiting PetZY!','Prices are inclusive of applicable taxes unless stated otherwise.','active','2026-08-14 20:53:53','2026-08-14 20:53:53'),(2,'Udupi Tiffin Corner','Udupi Tiffin Corner',NULL,'contact@udupitiffin.example','+91-9876510001','4th Block','Bengaluru','Karnataka','India','560034','29UDUPI1234F1Z1','UDUPI1234F','INR','Asia/Kolkata','Thank you for dining with us!','Prices are inclusive of applicable taxes unless stated otherwise.','active','2026-08-23 08:10:37','2026-08-24 06:18:13'),(3,'Fresh Basket','Fresh Basket Fruits & Vegetables',NULL,'contact@freshbasket.example','+91-9876520001','ITPL Main Road','Bengaluru','Karnataka','India','560066','29FRESH1234F1Z2','FRESH1234F','INR','Asia/Kolkata','Thank you for shopping at Fresh Basket!','Prices are inclusive of applicable taxes unless stated otherwise.','active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(4,'SlicePoint','SlicePoint Pizza & Burger LLP',NULL,'contact@slicepoint.example','+91-9876530001','Brigade Road','Bengaluru','Karnataka','India','560001','29SLICE1234F1Z3','SLICE1234F','INR','Asia/Kolkata','Thank you for shopping at SlicePoint!','Prices are inclusive of applicable taxes unless stated otherwise.','active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(5,'Trendz Fashion Store','Trendz Fashion Store Pvt Ltd',NULL,'contact@trendzfashion.example','+91-9876540001','Commercial Street','Bengaluru','Karnataka','India','560001','29TRNDZ1234F1Z4','TRNDZ1234F','INR','Asia/Kolkata','Thank you for shopping at Trendz Fashion Store!','Prices are inclusive of applicable taxes unless stated otherwise.','active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(6,'Griha Bartan','Griha Bartan Utensils Store',NULL,'contact@grihabartan.example','+91-9876550001','Chickpet','Bengaluru','Karnataka','India','560053','29GRIHA1234F1Z5','GRIHA1234F','INR','Asia/Kolkata','Thank you for shopping at Griha Bartan!','Prices are inclusive of applicable taxes unless stated otherwise.','active','2026-08-23 08:10:38','2026-08-23 08:10:38');

--
-- Table structure for table `coupons`
--

CREATE TABLE `coupons` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(10) unsigned NOT NULL,
  `code` varchar(50) NOT NULL,
  `type` enum('percentage','fixed') NOT NULL DEFAULT 'percentage',
  `value` decimal(12,2) NOT NULL DEFAULT 0.00,
  `min_order_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `max_discount_amount` decimal(12,2) DEFAULT NULL,
  `valid_from` date DEFAULT NULL,
  `valid_to` date DEFAULT NULL,
  `usage_limit` int(10) unsigned DEFAULT NULL,
  `used_count` int(10) unsigned NOT NULL DEFAULT 0,
  `customer_id` int(10) unsigned DEFAULT NULL,
  `outlet_id` int(10) unsigned DEFAULT NULL,
  `order_type` varchar(30) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_coupon_company_code` (`company_id`,`code`),
  CONSTRAINT `fk_coupon_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `coupons`
--


--
-- Table structure for table `customer_addresses`
--

CREATE TABLE `customer_addresses` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` int(10) unsigned NOT NULL,
  `label` varchar(50) DEFAULT NULL,
  `address` varchar(255) NOT NULL,
  `city` varchar(100) DEFAULT NULL,
  `state` varchar(100) DEFAULT NULL,
  `pincode` varchar(20) DEFAULT NULL,
  `is_default` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_caddr_customer` (`customer_id`),
  CONSTRAINT `fk_caddr_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `customer_addresses`
--


--
-- Table structure for table `customer_loyalty`
--

CREATE TABLE `customer_loyalty` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` int(10) unsigned NOT NULL,
  `tier` varchar(50) NOT NULL DEFAULT 'Standard',
  `points_balance` int(11) NOT NULL DEFAULT 0,
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_cl_customer` (`customer_id`),
  CONSTRAINT `fk_cl_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `customer_loyalty`
--


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

CREATE TABLE `customers` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(10) unsigned NOT NULL,
  `name` varchar(150) NOT NULL,
  `mobile` varchar(20) DEFAULT NULL,
  `email` varchar(150) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `anniversary` date DEFAULT NULL,
  `gstin` varchar(20) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `total_visits` int(10) unsigned NOT NULL DEFAULT 0,
  `total_orders` int(10) unsigned NOT NULL DEFAULT 0,
  `total_spending` decimal(12,2) NOT NULL DEFAULT 0.00,
  `loyalty_points` int(11) NOT NULL DEFAULT 0,
  `last_visit_at` datetime DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_customer_company_mobile` (`company_id`,`mobile`),
  CONSTRAINT `fk_customer_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `customers` VALUES (1,1,'Rahul Sharma','+91-9123456780','rahul.sharma@example.com',NULL,NULL,NULL,NULL,0,0,0.00,0,NULL,'active','2026-08-14 20:53:53','2026-08-14 20:53:53'),(2,1,'Test Customer Hold','9999900001','holdtest@example.com',NULL,NULL,NULL,NULL,0,0,0.00,0,NULL,'active','2026-08-24 07:37:02','2026-08-24 07:37:02');

--
-- Table structure for table `day_end_closings`
--

CREATE TABLE `day_end_closings` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `outlet_id` int(10) unsigned NOT NULL,
  `business_date` date NOT NULL,
  `gross_sales` decimal(12,2) NOT NULL DEFAULT 0.00,
  `discounts` decimal(12,2) NOT NULL DEFAULT 0.00,
  `tax` decimal(12,2) NOT NULL DEFAULT 0.00,
  `net_sales` decimal(12,2) NOT NULL DEFAULT 0.00,
  `cash_sales` decimal(12,2) NOT NULL DEFAULT 0.00,
  `card_sales` decimal(12,2) NOT NULL DEFAULT 0.00,
  `upi_sales` decimal(12,2) NOT NULL DEFAULT 0.00,
  `online_sales` decimal(12,2) NOT NULL DEFAULT 0.00,
  `room_charge_sales` decimal(12,2) NOT NULL DEFAULT 0.00,
  `refunds` decimal(12,2) NOT NULL DEFAULT 0.00,
  `expenses` decimal(12,2) NOT NULL DEFAULT 0.00,
  `expected_cash` decimal(12,2) NOT NULL DEFAULT 0.00,
  `actual_cash` decimal(12,2) NOT NULL DEFAULT 0.00,
  `difference` decimal(12,2) NOT NULL DEFAULT 0.00,
  `kot_count` int(10) unsigned NOT NULL DEFAULT 0,
  `orders_count` int(10) unsigned NOT NULL DEFAULT 0,
  `cancelled_orders` int(10) unsigned NOT NULL DEFAULT 0,
  `closed_by` int(10) unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_dayend_outlet_date` (`outlet_id`,`business_date`),
  CONSTRAINT `fk_de_outlet` FOREIGN KEY (`outlet_id`) REFERENCES `outlets` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `day_end_closings`
--


--
-- Table structure for table `discounts`
--

CREATE TABLE `discounts` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(10) unsigned NOT NULL,
  `name` varchar(100) NOT NULL,
  `type` enum('percentage','fixed') NOT NULL DEFAULT 'percentage',
  `value` decimal(12,2) NOT NULL DEFAULT 0.00,
  `max_discount_amount` decimal(12,2) DEFAULT NULL,
  `applies_to` enum('order','item') NOT NULL DEFAULT 'order',
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_discount_company` (`company_id`),
  CONSTRAINT `fk_discount_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `discounts`
--


--
-- Table structure for table `employees`
--

CREATE TABLE `employees` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(10) unsigned NOT NULL,
  `outlet_id` int(10) unsigned DEFAULT NULL,
  `user_id` int(10) unsigned DEFAULT NULL,
  `name` varchar(150) NOT NULL,
  `employee_code` varchar(30) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `email` varchar(150) DEFAULT NULL,
  `department` varchar(100) DEFAULT NULL,
  `designation` varchar(100) DEFAULT NULL,
  `joining_date` date DEFAULT NULL,
  `salary` decimal(12,2) DEFAULT NULL,
  `salary_type` enum('monthly','daily','hourly') NOT NULL DEFAULT 'monthly',
  `hra` decimal(12,2) NOT NULL DEFAULT 0.00,
  `other_allowances` decimal(12,2) NOT NULL DEFAULT 0.00,
  `week_off_day` tinyint(3) unsigned DEFAULT NULL COMMENT '0=Sunday .. 6=Saturday',
  `bank_account_number` varchar(30) DEFAULT NULL,
  `bank_ifsc` varchar(15) DEFAULT NULL,
  `bank_name` varchar(100) DEFAULT NULL,
  `pan_number` varchar(20) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_emp_company_code` (`company_id`,`employee_code`),
  CONSTRAINT `fk_emp_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `employees`
--

INSERT INTO `employees` VALUES (1,1,1,1,'System Admin','EMP001','+91-9876500010',NULL,'Management','Admin','2024-01-01',60000.00,'monthly',10000.00,5000.00,0,NULL,NULL,NULL,NULL,'active','2026-08-24 12:09:46'),(2,1,1,2,'Priya Menon','EMP002','+91-9876500011',NULL,'Management','Outlet Manager','2024-02-01',42000.00,'monthly',6000.00,3000.00,0,NULL,NULL,NULL,NULL,'active','2026-08-24 12:09:46'),(3,1,1,3,'Arjun Rao','EMP003','+91-9876500012',NULL,'Front of House','Cashier','2024-03-15',22000.00,'monthly',2500.00,1500.00,0,NULL,NULL,NULL,NULL,'active','2026-08-24 12:09:46'),(4,1,1,4,'Sneha Kulkarni','EMP004','+91-9876500013',NULL,'Front of House','Waiter','2024-04-01',18000.00,'monthly',1500.00,1000.00,1,NULL,NULL,NULL,NULL,'active','2026-08-24 12:09:46'),(5,1,1,5,'Ramesh Iyer','EMP005','+91-9876500014',NULL,'Kitchen','Cook','2024-03-01',20000.00,'monthly',2000.00,1500.00,1,NULL,NULL,NULL,NULL,'active','2026-08-24 12:09:46'),(6,1,1,6,'Kavya Nair','EMP006','+91-9876500015',NULL,'Front Desk','Receptionist','2024-05-01',19000.00,'monthly',1800.00,1000.00,0,NULL,NULL,NULL,NULL,'active','2026-08-24 12:09:46');

--
-- Table structure for table `expenses`
--

CREATE TABLE `expenses` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(10) unsigned NOT NULL,
  `outlet_id` int(10) unsigned NOT NULL,
  `category` enum('rent','electricity','salary','transport','maintenance','marketing','supplies','miscellaneous') NOT NULL DEFAULT 'miscellaneous',
  `amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `expense_date` date NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `payment_mode` enum('cash','card','upi','bank_transfer','other') NOT NULL DEFAULT 'cash',
  `employee_id` int(10) unsigned DEFAULT NULL,
  `attachment` varchar(255) DEFAULT NULL,
  `created_by` int(10) unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_exp_outlet` (`outlet_id`),
  CONSTRAINT `fk_exp_outlet` FOREIGN KEY (`outlet_id`) REFERENCES `outlets` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `expenses`
--

INSERT INTO `expenses` VALUES (1,1,1,'salary',25161.29,'2026-08-24','Salary — Arjun Rao (08/2026)','bank_transfer',3,NULL,1,'2026-08-24 06:41:24');

--
-- Table structure for table `floors`
--

CREATE TABLE `floors` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `outlet_id` int(10) unsigned NOT NULL,
  `name` varchar(100) NOT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  PRIMARY KEY (`id`),
  KEY `fk_floor_outlet` (`outlet_id`),
  CONSTRAINT `fk_floor_outlet` FOREIGN KEY (`outlet_id`) REFERENCES `outlets` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `floors`
--

INSERT INTO `floors` VALUES (1,1,'Ground Floor',1,'active'),(2,1,'Rooftop',2,'active');

--
-- Table structure for table `holidays`
--

CREATE TABLE `holidays` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(10) unsigned NOT NULL,
  `outlet_id` int(10) unsigned DEFAULT NULL,
  `holiday_date` date NOT NULL,
  `name` varchar(150) NOT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_hol_company` (`company_id`),
  CONSTRAINT `fk_hol_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `holidays`
--

INSERT INTO `holidays` VALUES (1,1,NULL,'2026-01-26','Republic Day','active','2026-08-24 12:09:46'),(2,1,NULL,'2026-08-15','Independence Day','active','2026-08-24 12:09:46'),(3,1,NULL,'2026-10-02','Gandhi Jayanti','active','2026-08-24 12:09:46'),(4,1,NULL,'2026-11-08','Diwali','active','2026-08-24 12:09:46');

--
-- Table structure for table `hotel_checkins`
--

CREATE TABLE `hotel_checkins` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `reservation_id` int(10) unsigned NOT NULL,
  `room_id` int(10) unsigned NOT NULL,
  `guest_id` int(10) unsigned NOT NULL,
  `checked_in_by` int(10) unsigned DEFAULT NULL,
  `checked_in_at` datetime NOT NULL DEFAULT current_timestamp(),
  `id_verified` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `fk_hci_reservation` (`reservation_id`),
  KEY `fk_hci_room` (`room_id`),
  CONSTRAINT `fk_hci_reservation` FOREIGN KEY (`reservation_id`) REFERENCES `hotel_room_reservations` (`id`),
  CONSTRAINT `fk_hci_room` FOREIGN KEY (`room_id`) REFERENCES `hotel_rooms` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `hotel_checkins`
--


--
-- Table structure for table `hotel_checkouts`
--

CREATE TABLE `hotel_checkouts` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `reservation_id` int(10) unsigned NOT NULL,
  `checkin_id` int(10) unsigned NOT NULL,
  `checked_out_by` int(10) unsigned DEFAULT NULL,
  `checked_out_at` datetime NOT NULL DEFAULT current_timestamp(),
  `final_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`),
  KEY `fk_hco_reservation` (`reservation_id`),
  KEY `fk_hco_checkin` (`checkin_id`),
  CONSTRAINT `fk_hco_checkin` FOREIGN KEY (`checkin_id`) REFERENCES `hotel_checkins` (`id`),
  CONSTRAINT `fk_hco_reservation` FOREIGN KEY (`reservation_id`) REFERENCES `hotel_room_reservations` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `hotel_checkouts`
--


--
-- Table structure for table `hotel_folio_items`
--

CREATE TABLE `hotel_folio_items` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `folio_id` int(10) unsigned NOT NULL,
  `charge_type` enum('room','restaurant','room_service','laundry','minibar','extra_bed','parking','other') NOT NULL,
  `reference_type` varchar(50) DEFAULT NULL,
  `reference_id` bigint(20) unsigned DEFAULT NULL,
  `description` varchar(255) NOT NULL,
  `quantity` decimal(10,2) NOT NULL DEFAULT 1.00,
  `amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `tax_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `total` decimal(12,2) NOT NULL DEFAULT 0.00,
  `posted_by` int(10) unsigned DEFAULT NULL,
  `posted_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_hfi_folio` (`folio_id`),
  CONSTRAINT `fk_hfi_folio` FOREIGN KEY (`folio_id`) REFERENCES `hotel_folios` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `hotel_folio_items`
--


--
-- Table structure for table `hotel_folios`
--

CREATE TABLE `hotel_folios` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `reservation_id` int(10) unsigned NOT NULL,
  `guest_id` int(10) unsigned NOT NULL,
  `room_id` int(10) unsigned NOT NULL,
  `folio_number` varchar(50) NOT NULL,
  `total_charges` decimal(12,2) NOT NULL DEFAULT 0.00,
  `total_discount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `total_tax` decimal(12,2) NOT NULL DEFAULT 0.00,
  `total_paid` decimal(12,2) NOT NULL DEFAULT 0.00,
  `balance` decimal(12,2) NOT NULL DEFAULT 0.00,
  `status` enum('open','closed') NOT NULL DEFAULT 'open',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_folio_number` (`folio_number`),
  KEY `fk_folio_reservation` (`reservation_id`),
  KEY `fk_folio_guest` (`guest_id`),
  CONSTRAINT `fk_folio_guest` FOREIGN KEY (`guest_id`) REFERENCES `hotel_guests` (`id`),
  CONSTRAINT `fk_folio_reservation` FOREIGN KEY (`reservation_id`) REFERENCES `hotel_room_reservations` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `hotel_folios`
--


--
-- Table structure for table `hotel_guests`
--

CREATE TABLE `hotel_guests` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(10) unsigned NOT NULL,
  `name` varchar(150) NOT NULL,
  `mobile` varchar(20) DEFAULT NULL,
  `email` varchar(150) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `state` varchar(100) DEFAULT NULL,
  `country` varchar(100) DEFAULT NULL,
  `id_type` enum('passport','aadhaar','driving_license','voter_id','other') DEFAULT NULL,
  `id_number` varchar(100) DEFAULT NULL,
  `id_document` varchar(255) DEFAULT NULL,
  `nationality` varchar(100) DEFAULT NULL,
  `company_name` varchar(150) DEFAULT NULL,
  `gstin` varchar(20) DEFAULT NULL,
  `notes` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_hg_company` (`company_id`),
  CONSTRAINT `fk_hg_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `hotel_guests`
--

INSERT INTO `hotel_guests` VALUES (1,1,'John Fernandes','+91-9012345678','john.fernandes@example.com',NULL,NULL,NULL,NULL,NULL,'passport','P1234567',NULL,'Indian',NULL,NULL,NULL,'2026-08-14 20:53:53');

--
-- Table structure for table `hotel_rate_plans`
--

CREATE TABLE `hotel_rate_plans` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `room_type_id` int(10) unsigned NOT NULL,
  `name` varchar(100) NOT NULL,
  `rate` decimal(12,2) NOT NULL DEFAULT 0.00,
  `valid_from` date DEFAULT NULL,
  `valid_to` date DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  PRIMARY KEY (`id`),
  KEY `fk_hrp_roomtype` (`room_type_id`),
  CONSTRAINT `fk_hrp_roomtype` FOREIGN KEY (`room_type_id`) REFERENCES `hotel_room_types` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `hotel_rate_plans`
--


--
-- Table structure for table `hotel_room_amenities`
--

CREATE TABLE `hotel_room_amenities` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `room_type_id` int(10) unsigned NOT NULL,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_hra_roomtype` (`room_type_id`),
  CONSTRAINT `fk_hra_roomtype` FOREIGN KEY (`room_type_id`) REFERENCES `hotel_room_types` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `hotel_room_amenities`
--

INSERT INTO `hotel_room_amenities` VALUES (1,1,'Free WiFi'),(2,1,'Air Conditioning'),(3,1,'Flat Screen TV'),(4,2,'Free WiFi'),(5,2,'Air Conditioning'),(6,2,'Mini Bar'),(7,2,'Living Room'),(8,3,'Free WiFi'),(9,3,'Work Desk'),(10,3,'Air Conditioning');

--
-- Table structure for table `hotel_room_blocks`
--

CREATE TABLE `hotel_room_blocks` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `room_id` int(10) unsigned NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  `reason` varchar(255) DEFAULT NULL,
  `created_by` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_hrb_room` (`room_id`),
  CONSTRAINT `fk_hrb_room` FOREIGN KEY (`room_id`) REFERENCES `hotel_rooms` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `hotel_room_blocks`
--


--
-- Table structure for table `hotel_room_charges`
--

CREATE TABLE `hotel_room_charges` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `folio_id` int(10) unsigned NOT NULL,
  `charge_type` enum('room_rent','extra_bed','early_checkin','late_checkout','other') NOT NULL DEFAULT 'room_rent',
  `amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `charge_date` date NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_hrc_folio` (`folio_id`),
  CONSTRAINT `fk_hrc_folio` FOREIGN KEY (`folio_id`) REFERENCES `hotel_folios` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `hotel_room_charges`
--


--
-- Table structure for table `hotel_room_reservations`
--

CREATE TABLE `hotel_room_reservations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `outlet_id` int(10) unsigned NOT NULL,
  `reservation_number` varchar(50) NOT NULL,
  `guest_id` int(10) unsigned NOT NULL,
  `room_id` int(10) unsigned DEFAULT NULL,
  `room_type_id` int(10) unsigned NOT NULL,
  `check_in_date` date NOT NULL,
  `check_out_date` date NOT NULL,
  `adults` int(10) unsigned NOT NULL DEFAULT 1,
  `children` int(10) unsigned NOT NULL DEFAULT 0,
  `rate` decimal(12,2) NOT NULL DEFAULT 0.00,
  `discount_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `tax_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `advance_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `total_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `source` varchar(50) NOT NULL DEFAULT 'walk_in',
  `special_requests` varchar(255) DEFAULT NULL,
  `status` enum('pending','confirmed','checked_in','checked_out','cancelled','no_show') NOT NULL DEFAULT 'pending',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_hres_outlet_number` (`outlet_id`,`reservation_number`),
  KEY `fk_hres_guest` (`guest_id`),
  KEY `fk_hres_room` (`room_id`),
  KEY `fk_hres_roomtype` (`room_type_id`),
  CONSTRAINT `fk_hres_guest` FOREIGN KEY (`guest_id`) REFERENCES `hotel_guests` (`id`),
  CONSTRAINT `fk_hres_room` FOREIGN KEY (`room_id`) REFERENCES `hotel_rooms` (`id`),
  CONSTRAINT `fk_hres_roomtype` FOREIGN KEY (`room_type_id`) REFERENCES `hotel_room_types` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `hotel_room_reservations`
--

INSERT INTO `hotel_room_reservations` VALUES (1,1,'RES-PTZ01-000001',1,3,2,'2026-08-14','2026-08-16',2,1,7500.00,0.00,900.00,0.00,16800.00,'walk_in',NULL,'confirmed','2026-08-14 20:53:53','2026-08-14 20:53:53');

--
-- Table structure for table `hotel_room_types`
--

CREATE TABLE `hotel_room_types` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `outlet_id` int(10) unsigned NOT NULL,
  `name` varchar(100) NOT NULL,
  `description` varchar(500) DEFAULT NULL,
  `capacity` int(10) unsigned NOT NULL DEFAULT 2,
  `adult_capacity` int(10) unsigned NOT NULL DEFAULT 2,
  `child_capacity` int(10) unsigned NOT NULL DEFAULT 0,
  `base_price` decimal(12,2) NOT NULL DEFAULT 0.00,
  `tax_id` int(10) unsigned DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_hrt_outlet` (`outlet_id`),
  CONSTRAINT `fk_hrt_outlet` FOREIGN KEY (`outlet_id`) REFERENCES `outlets` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `hotel_room_types`
--

INSERT INTO `hotel_room_types` VALUES (1,1,'Deluxe','Comfortable deluxe room with city view',2,2,1,4500.00,5,NULL,'active','2026-08-14 20:53:53'),(2,1,'Suite','Spacious suite with living area',3,2,2,7500.00,5,NULL,'active','2026-08-14 20:53:53'),(3,1,'Executive','Premium executive room for business travelers',2,2,0,6000.00,5,NULL,'active','2026-08-14 20:53:53');

--
-- Table structure for table `hotel_rooms`
--

CREATE TABLE `hotel_rooms` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `outlet_id` int(10) unsigned NOT NULL,
  `room_type_id` int(10) unsigned NOT NULL,
  `room_number` varchar(20) NOT NULL,
  `floor` varchar(20) DEFAULT NULL,
  `price` decimal(12,2) NOT NULL DEFAULT 0.00,
  `capacity` int(10) unsigned NOT NULL DEFAULT 2,
  `notes` varchar(255) DEFAULT NULL,
  `status` enum('available','occupied','reserved','cleaning','maintenance','blocked') NOT NULL DEFAULT 'available',
  `housekeeping_status` enum('clean','dirty','cleaning','inspected','maintenance','blocked') NOT NULL DEFAULT 'clean',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_room_outlet_number` (`outlet_id`,`room_number`),
  KEY `fk_hr_roomtype` (`room_type_id`),
  CONSTRAINT `fk_hr_outlet` FOREIGN KEY (`outlet_id`) REFERENCES `outlets` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_hr_roomtype` FOREIGN KEY (`room_type_id`) REFERENCES `hotel_room_types` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `hotel_rooms`
--

INSERT INTO `hotel_rooms` VALUES (1,1,1,'101','1',4500.00,2,NULL,'available','clean','2026-08-14 20:53:53','2026-08-14 20:53:53'),(2,1,1,'102','1',4500.00,2,NULL,'available','clean','2026-08-14 20:53:53','2026-08-14 20:53:53'),(3,1,2,'103','1',7500.00,3,NULL,'available','clean','2026-08-14 20:53:53','2026-08-14 20:53:53'),(4,1,3,'201','2',6000.00,2,NULL,'available','clean','2026-08-14 20:53:53','2026-08-14 20:53:53'),(5,1,3,'202','2',6000.00,2,NULL,'available','clean','2026-08-14 20:53:53','2026-08-14 20:53:53');

--
-- Table structure for table `inventory_items`
--

CREATE TABLE `inventory_items` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(10) unsigned NOT NULL,
  `outlet_id` int(10) unsigned DEFAULT NULL,
  `name` varchar(150) NOT NULL,
  `sku` varchar(50) DEFAULT NULL,
  `unit_id` int(10) unsigned NOT NULL,
  `purchase_price` decimal(12,2) NOT NULL DEFAULT 0.00,
  `selling_price` decimal(12,2) NOT NULL DEFAULT 0.00,
  `reorder_level` decimal(12,2) NOT NULL DEFAULT 0.00,
  `minimum_stock` decimal(12,2) NOT NULL DEFAULT 0.00,
  `maximum_stock` decimal(12,2) NOT NULL DEFAULT 0.00,
  `supplier_id` int(10) unsigned DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_ii_company` (`company_id`),
  KEY `fk_ii_unit` (`unit_id`),
  KEY `fk_ii_supplier` (`supplier_id`),
  CONSTRAINT `fk_ii_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ii_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`),
  CONSTRAINT `fk_ii_unit` FOREIGN KEY (`unit_id`) REFERENCES `inventory_units` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `inventory_items`
--

INSERT INTO `inventory_items` VALUES (1,1,1,'Paneer','INV-001',4,320.00,0.00,5000.00,2000.00,20000.00,1,'active','2026-08-14 20:53:53','2026-08-14 15:42:37'),(2,1,1,'Tomato','INV-002',1,40.00,0.00,20.00,10.00,100.00,1,'active','2026-08-14 20:53:53','2026-08-14 20:53:53'),(3,1,1,'Butter','INV-003',4,480.00,0.00,3000.00,1000.00,15000.00,1,'active','2026-08-14 20:53:53','2026-08-14 20:53:53'),(4,1,1,'Fresh Cream','INV-004',2,260.00,0.00,5.00,2.00,30.00,1,'active','2026-08-14 20:53:53','2026-08-14 20:53:53'),(5,1,1,'Mixed Spices','INV-005',4,900.00,0.00,1000.00,500.00,5000.00,1,'active','2026-08-14 20:53:53','2026-08-14 20:53:53'),(6,1,1,'Chicken','INV-006',1,220.00,0.00,15.00,5.00,60.00,1,'active','2026-08-14 20:53:53','2026-08-14 20:53:53');

--
-- Table structure for table `inventory_stocks`
--

CREATE TABLE `inventory_stocks` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `inventory_item_id` int(10) unsigned NOT NULL,
  `outlet_id` int(10) unsigned NOT NULL,
  `current_stock` decimal(14,3) NOT NULL DEFAULT 0.000,
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_stock_item_outlet` (`inventory_item_id`,`outlet_id`),
  KEY `fk_is_outlet` (`outlet_id`),
  CONSTRAINT `fk_is_item` FOREIGN KEY (`inventory_item_id`) REFERENCES `inventory_items` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_is_outlet` FOREIGN KEY (`outlet_id`) REFERENCES `outlets` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `inventory_stocks`
--

INSERT INTO `inventory_stocks` VALUES (1,1,1,7760.000,'2026-08-14 15:42:37'),(2,2,1,-110.000,'2026-08-14 15:42:37'),(3,3,1,5970.000,'2026-08-14 15:42:37'),(4,4,1,-40.000,'2026-08-14 15:42:37'),(5,5,1,1990.000,'2026-08-14 15:42:37'),(6,6,1,25.000,'2026-08-14 20:53:53');

--
-- Table structure for table `inventory_transactions`
--

CREATE TABLE `inventory_transactions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `inventory_item_id` int(10) unsigned NOT NULL,
  `outlet_id` int(10) unsigned NOT NULL,
  `type` enum('purchase','sale','consumption','wastage','adjustment','transfer_out','transfer_in','return','opening_stock','closing_adjustment') NOT NULL,
  `quantity` decimal(14,3) NOT NULL,
  `balance_after` decimal(14,3) NOT NULL DEFAULT 0.000,
  `unit_cost` decimal(12,2) NOT NULL DEFAULT 0.00,
  `reference_type` varchar(50) DEFAULT NULL,
  `reference_id` bigint(20) unsigned DEFAULT NULL,
  `notes` varchar(255) DEFAULT NULL,
  `created_by` int(10) unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_it_item_outlet` (`inventory_item_id`,`outlet_id`),
  KEY `fk_it_outlet` (`outlet_id`),
  CONSTRAINT `fk_it_item` FOREIGN KEY (`inventory_item_id`) REFERENCES `inventory_items` (`id`),
  CONSTRAINT `fk_it_outlet` FOREIGN KEY (`outlet_id`) REFERENCES `outlets` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `inventory_transactions`
--

INSERT INTO `inventory_transactions` VALUES (1,1,1,'opening_stock',8000.000,8000.000,320.00,'seed',NULL,'Opening stock',NULL,'2026-08-14 20:53:53'),(2,2,1,'opening_stock',40.000,40.000,40.00,'seed',NULL,'Opening stock',NULL,'2026-08-14 20:53:53'),(3,3,1,'opening_stock',6000.000,6000.000,480.00,'seed',NULL,'Opening stock',NULL,'2026-08-14 20:53:53'),(4,4,1,'opening_stock',10.000,10.000,260.00,'seed',NULL,'Opening stock',NULL,'2026-08-14 20:53:53'),(5,5,1,'opening_stock',2000.000,2000.000,900.00,'seed',NULL,'Opening stock',NULL,'2026-08-14 20:53:53'),(6,6,1,'opening_stock',25.000,25.000,220.00,'seed',NULL,'Opening stock',NULL,'2026-08-14 20:53:53'),(7,1,1,'consumption',-250.000,7750.000,80.00,'order_item',1,'Recipe consumption for menu item 3',1,'2026-08-14 15:42:37'),(8,2,1,'consumption',-150.000,-110.000,6.00,'order_item',1,'Recipe consumption for menu item 3',1,'2026-08-14 15:42:37'),(9,3,1,'consumption',-30.000,5970.000,14.40,'order_item',1,'Recipe consumption for menu item 3',1,'2026-08-14 15:42:37'),(10,4,1,'consumption',-50.000,-40.000,13.00,'order_item',1,'Recipe consumption for menu item 3',1,'2026-08-14 15:42:37'),(11,5,1,'consumption',-10.000,1990.000,9.00,'order_item',1,'Recipe consumption for menu item 3',1,'2026-08-14 15:42:37'),(12,1,1,'purchase',10.000,7760.000,320.00,'purchase_invoice',1,NULL,1,'2026-08-14 15:42:37');

--
-- Table structure for table `inventory_units`
--

CREATE TABLE `inventory_units` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(10) unsigned NOT NULL,
  `name` varchar(50) NOT NULL,
  `short_code` varchar(10) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_iu_company` (`company_id`),
  CONSTRAINT `fk_iu_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `inventory_units`
--

INSERT INTO `inventory_units` VALUES (1,1,'Kilogram','kg'),(2,1,'Litre','ltr'),(3,1,'Piece','pcs'),(4,1,'Gram','g');

--
-- Table structure for table `kitchen_orders`
--

CREATE TABLE `kitchen_orders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `kot_id` bigint(20) unsigned NOT NULL,
  `station_id` int(10) unsigned NOT NULL,
  `accepted_at` datetime DEFAULT NULL,
  `started_at` datetime DEFAULT NULL,
  `ready_at` datetime DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  `status` enum('new','accepted','preparing','ready','completed','cancelled') NOT NULL DEFAULT 'new',
  PRIMARY KEY (`id`),
  KEY `fk_ko_kot` (`kot_id`),
  KEY `fk_ko_station` (`station_id`),
  CONSTRAINT `fk_ko_kot` FOREIGN KEY (`kot_id`) REFERENCES `kot` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ko_station` FOREIGN KEY (`station_id`) REFERENCES `kitchen_stations` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `kitchen_orders`
--

INSERT INTO `kitchen_orders` VALUES (1,1,1,NULL,NULL,NULL,NULL,'new'),(2,2,2,NULL,NULL,NULL,NULL,'new');

--
-- Table structure for table `kitchen_stations`
--

CREATE TABLE `kitchen_stations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `outlet_id` int(10) unsigned NOT NULL,
  `name` varchar(100) NOT NULL,
  `printer_id` int(10) unsigned DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_ks_outlet` (`outlet_id`),
  CONSTRAINT `fk_ks_outlet` FOREIGN KEY (`outlet_id`) REFERENCES `outlets` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `kitchen_stations`
--

INSERT INTO `kitchen_stations` VALUES (1,1,'Main Kitchen',NULL,'active','2026-08-14 20:53:53'),(2,1,'Tandoor',NULL,'active','2026-08-14 20:53:53'),(3,1,'Chinese',NULL,'active','2026-08-14 20:53:53'),(4,1,'Beverage',NULL,'active','2026-08-14 20:53:53'),(5,1,'Dessert',NULL,'active','2026-08-14 20:53:53');

--
-- Table structure for table `kot`
--

CREATE TABLE `kot` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `outlet_id` int(10) unsigned NOT NULL,
  `order_id` bigint(20) unsigned NOT NULL,
  `kot_number` varchar(50) NOT NULL,
  `table_id` int(10) unsigned DEFAULT NULL,
  `order_type` varchar(30) NOT NULL,
  `waiter_id` int(10) unsigned DEFAULT NULL,
  `priority` enum('normal','high') NOT NULL DEFAULT 'normal',
  `status` enum('new','accepted','preparing','ready','served','cancelled') NOT NULL DEFAULT 'new',
  `notes` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_kot_outlet_number` (`outlet_id`,`kot_number`),
  KEY `fk_kot_order` (`order_id`),
  CONSTRAINT `fk_kot_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `kot`
--

INSERT INTO `kot` VALUES (1,1,1,'KOT-PTZ01-2026-000001',NULL,'counter_sale',1,'normal','new',NULL,'2026-08-14 15:42:37','2026-08-14 15:42:37'),(2,1,2,'KOT-PTZ01-2026-000002',NULL,'counter_sale',1,'normal','new',NULL,'2026-08-23 09:28:54','2026-08-23 09:28:54');

--
-- Table structure for table `kot_items`
--

CREATE TABLE `kot_items` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `kot_id` bigint(20) unsigned NOT NULL,
  `order_item_id` bigint(20) unsigned NOT NULL,
  `station_id` int(10) unsigned DEFAULT NULL,
  `name` varchar(150) NOT NULL,
  `quantity` decimal(10,2) NOT NULL DEFAULT 1.00,
  `modifiers` varchar(255) DEFAULT NULL,
  `notes` varchar(255) DEFAULT NULL,
  `status` enum('new','accepted','preparing','ready','served','cancelled') NOT NULL DEFAULT 'new',
  PRIMARY KEY (`id`),
  KEY `fk_ki_kot` (`kot_id`),
  KEY `fk_ki_orderitem` (`order_item_id`),
  CONSTRAINT `fk_ki_kot` FOREIGN KEY (`kot_id`) REFERENCES `kot` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ki_orderitem` FOREIGN KEY (`order_item_id`) REFERENCES `order_items` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `kot_items`
--

INSERT INTO `kot_items` VALUES (1,1,1,1,'Paneer Butter Masala',1.00,NULL,NULL,'new'),(2,2,2,2,'Butter Naan',2.00,NULL,NULL,'new');

--
-- Table structure for table `leave_requests`
--

CREATE TABLE `leave_requests` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(10) unsigned NOT NULL,
  `employee_id` int(10) unsigned NOT NULL,
  `leave_type_id` int(10) unsigned NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  `is_half_day` tinyint(1) NOT NULL DEFAULT 0,
  `day_count` decimal(5,1) NOT NULL DEFAULT 1.0,
  `reason` varchar(255) DEFAULT NULL,
  `status` enum('pending','approved','rejected','cancelled') NOT NULL DEFAULT 'pending',
  `approved_by` int(10) unsigned DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `created_by` int(10) unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_lr_employee` (`employee_id`),
  KEY `fk_lr_leavetype` (`leave_type_id`),
  CONSTRAINT `fk_lr_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_lr_leavetype` FOREIGN KEY (`leave_type_id`) REFERENCES `leave_types` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `leave_requests`
--

INSERT INTO `leave_requests` VALUES (1,1,3,1,'2026-09-01','2026-09-02',0,2.0,'Personal','approved',1,'2026-08-24 06:41:09',1,'2026-08-24 06:41:09');

--
-- Table structure for table `leave_types`
--

CREATE TABLE `leave_types` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(10) unsigned NOT NULL,
  `name` varchar(100) NOT NULL,
  `code` varchar(20) NOT NULL,
  `is_paid` tinyint(1) NOT NULL DEFAULT 1,
  `annual_quota` decimal(5,1) NOT NULL DEFAULT 0.0,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_leavetype_company_code` (`company_id`,`code`),
  CONSTRAINT `fk_lt_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `leave_types`
--

INSERT INTO `leave_types` VALUES (1,1,'Casual Leave','CL',1,12.0,'active','2026-08-24 12:09:23'),(2,1,'Sick Leave','SL',1,8.0,'active','2026-08-24 12:09:23'),(3,1,'Earned Leave','EL',1,15.0,'active','2026-08-24 12:09:23'),(4,1,'Comp Off','COMPOFF',1,0.0,'active','2026-08-24 12:09:23'),(5,1,'Leave Without Pay','LWP',0,0.0,'active','2026-08-24 12:09:23'),(6,2,'Casual Leave','CL',1,12.0,'active','2026-08-24 12:09:23'),(7,2,'Sick Leave','SL',1,8.0,'active','2026-08-24 12:09:23'),(8,2,'Earned Leave','EL',1,15.0,'active','2026-08-24 12:09:23'),(9,2,'Comp Off','COMPOFF',1,0.0,'active','2026-08-24 12:09:23'),(10,2,'Leave Without Pay','LWP',0,0.0,'active','2026-08-24 12:09:23'),(11,3,'Casual Leave','CL',1,12.0,'active','2026-08-24 12:09:23'),(12,3,'Sick Leave','SL',1,8.0,'active','2026-08-24 12:09:23'),(13,3,'Earned Leave','EL',1,15.0,'active','2026-08-24 12:09:23'),(14,3,'Comp Off','COMPOFF',1,0.0,'active','2026-08-24 12:09:23'),(15,3,'Leave Without Pay','LWP',0,0.0,'active','2026-08-24 12:09:23'),(16,4,'Casual Leave','CL',1,12.0,'active','2026-08-24 12:09:23'),(17,4,'Sick Leave','SL',1,8.0,'active','2026-08-24 12:09:23'),(18,4,'Earned Leave','EL',1,15.0,'active','2026-08-24 12:09:23'),(19,4,'Comp Off','COMPOFF',1,0.0,'active','2026-08-24 12:09:23'),(20,4,'Leave Without Pay','LWP',0,0.0,'active','2026-08-24 12:09:23'),(21,5,'Casual Leave','CL',1,12.0,'active','2026-08-24 12:09:23'),(22,5,'Sick Leave','SL',1,8.0,'active','2026-08-24 12:09:23'),(23,5,'Earned Leave','EL',1,15.0,'active','2026-08-24 12:09:23'),(24,5,'Comp Off','COMPOFF',1,0.0,'active','2026-08-24 12:09:23'),(25,5,'Leave Without Pay','LWP',0,0.0,'active','2026-08-24 12:09:23'),(26,6,'Casual Leave','CL',1,12.0,'active','2026-08-24 12:09:23'),(27,6,'Sick Leave','SL',1,8.0,'active','2026-08-24 12:09:23'),(28,6,'Earned Leave','EL',1,15.0,'active','2026-08-24 12:09:23'),(29,6,'Comp Off','COMPOFF',1,0.0,'active','2026-08-24 12:09:23'),(30,6,'Leave Without Pay','LWP',0,0.0,'active','2026-08-24 12:09:23');

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

CREATE TABLE `loyalty_transactions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` int(10) unsigned NOT NULL,
  `order_id` bigint(20) unsigned DEFAULT NULL,
  `type` enum('earn','redeem','expire','adjust') NOT NULL,
  `points` int(11) NOT NULL,
  `balance_after` int(11) NOT NULL DEFAULT 0,
  `notes` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_lt_customer` (`customer_id`),
  CONSTRAINT `fk_lt_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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


--
-- Table structure for table `media`
--

CREATE TABLE `media` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(10) unsigned NOT NULL,
  `folder` varchar(50) NOT NULL DEFAULT 'media',
  `file_name` varchar(255) NOT NULL,
  `original_name` varchar(255) NOT NULL,
  `mime_type` varchar(100) NOT NULL,
  `size` int(10) unsigned NOT NULL DEFAULT 0,
  `uploaded_by` int(10) unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_media_company` (`company_id`),
  CONSTRAINT `fk_media_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `media`
--


--
-- Table structure for table `menu_categories`
--

CREATE TABLE `menu_categories` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(10) unsigned NOT NULL,
  `outlet_id` int(10) unsigned DEFAULT NULL,
  `parent_id` int(10) unsigned DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `image` varchar(255) DEFAULT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_mc_company` (`company_id`),
  KEY `fk_mc_outlet` (`outlet_id`),
  KEY `idx_mc_parent` (`parent_id`),
  CONSTRAINT `fk_mc_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_mc_outlet` FOREIGN KEY (`outlet_id`) REFERENCES `outlets` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_mc_parent` FOREIGN KEY (`parent_id`) REFERENCES `menu_categories` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `menu_categories`
--

INSERT INTO `menu_categories` VALUES (1,1,1,NULL,'Starters',NULL,1,'active','2026-08-14 20:53:53','2026-08-14 20:53:53'),(2,1,1,NULL,'Main Course',NULL,2,'active','2026-08-14 20:53:53','2026-08-14 20:53:53'),(3,1,1,NULL,'Breads',NULL,3,'active','2026-08-14 20:53:53','2026-08-14 20:53:53'),(4,1,1,NULL,'Beverages',NULL,4,'active','2026-08-14 20:53:53','2026-08-14 20:53:53'),(5,1,1,NULL,'Desserts',NULL,5,'active','2026-08-14 20:53:53','2026-08-14 20:53:53'),(6,1,1,4,'Hot Drinks',NULL,1,'inactive','2026-08-23 06:13:02','2026-08-23 06:13:16'),(7,2,NULL,NULL,'Dosa',NULL,1,'active','2026-08-23 08:10:37','2026-08-23 08:10:37'),(8,2,NULL,NULL,'Idli & Vada',NULL,2,'active','2026-08-23 08:10:37','2026-08-23 08:10:37'),(9,2,NULL,NULL,'Uttapam & Pongal',NULL,3,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(10,2,NULL,NULL,'South Indian Thali',NULL,4,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(11,2,NULL,NULL,'Filter Coffee & Beverages',NULL,5,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(12,3,NULL,NULL,'Fresh Vegetables',NULL,1,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(13,3,NULL,NULL,'Fresh Fruits',NULL,2,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(14,3,NULL,NULL,'Leafy Greens & Herbs',NULL,3,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(15,3,NULL,NULL,'Exotic Produce',NULL,4,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(16,4,NULL,NULL,'Pizzas',NULL,1,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(17,4,NULL,NULL,'Burgers',NULL,2,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(18,4,NULL,NULL,'Sides & Snacks',NULL,3,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(19,4,NULL,NULL,'Beverages',NULL,4,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(20,5,NULL,NULL,'Men\'s Wear',NULL,1,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(21,5,NULL,NULL,'Women\'s Wear',NULL,2,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(22,5,NULL,NULL,'Kids Wear',NULL,3,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(23,5,NULL,NULL,'Footwear & Accessories',NULL,4,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(24,6,NULL,NULL,'Cookware',NULL,1,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(25,6,NULL,NULL,'Dinnerware',NULL,2,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(26,6,NULL,NULL,'Storage & Containers',NULL,3,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(27,6,NULL,NULL,'Cutlery & Gadgets',NULL,4,'active','2026-08-23 08:10:38','2026-08-23 08:10:38');

--
-- Table structure for table `menu_combo_items`
--

CREATE TABLE `menu_combo_items` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `combo_id` int(10) unsigned NOT NULL,
  `item_id` int(10) unsigned NOT NULL,
  `quantity` int(10) unsigned NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  KEY `fk_ci_combo` (`combo_id`),
  KEY `fk_ci_item` (`item_id`),
  CONSTRAINT `fk_ci_combo` FOREIGN KEY (`combo_id`) REFERENCES `menu_combos` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ci_item` FOREIGN KEY (`item_id`) REFERENCES `menu_items` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `menu_combo_items`
--


--
-- Table structure for table `menu_combos`
--

CREATE TABLE `menu_combos` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(10) unsigned NOT NULL,
  `outlet_id` int(10) unsigned DEFAULT NULL,
  `name` varchar(150) NOT NULL,
  `price` decimal(12,2) NOT NULL DEFAULT 0.00,
  `image` varchar(255) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_combo_company` (`company_id`),
  CONSTRAINT `fk_combo_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `menu_combos`
--


--
-- Table structure for table `menu_item_addons`
--

CREATE TABLE `menu_item_addons` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `item_id` int(10) unsigned NOT NULL,
  `name` varchar(100) NOT NULL,
  `price` decimal(12,2) NOT NULL DEFAULT 0.00,
  `min_select` int(10) unsigned NOT NULL DEFAULT 0,
  `max_select` int(10) unsigned NOT NULL DEFAULT 1,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  PRIMARY KEY (`id`),
  KEY `fk_mia_item` (`item_id`),
  CONSTRAINT `fk_mia_item` FOREIGN KEY (`item_id`) REFERENCES `menu_items` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `menu_item_addons`
--

INSERT INTO `menu_item_addons` VALUES (1,4,'Extra Gravy',40.00,0,1,1,'active'),(2,4,'Extra Chicken',90.00,0,1,2,'active');

--
-- Table structure for table `menu_item_modifiers`
--

CREATE TABLE `menu_item_modifiers` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `item_id` int(10) unsigned NOT NULL,
  `name` varchar(100) NOT NULL,
  `price` decimal(12,2) NOT NULL DEFAULT 0.00,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  PRIMARY KEY (`id`),
  KEY `fk_mim_item` (`item_id`),
  CONSTRAINT `fk_mim_item` FOREIGN KEY (`item_id`) REFERENCES `menu_items` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `menu_item_modifiers`
--

INSERT INTO `menu_item_modifiers` VALUES (1,3,'Less Spicy',0.00,1,'active'),(2,3,'Extra Spicy',0.00,2,'active'),(3,3,'No Onion',0.00,3,'active'),(4,4,'No Garlic',0.00,1,'active');

--
-- Table structure for table `menu_item_taxes`
--

CREATE TABLE `menu_item_taxes` (
  `item_id` int(10) unsigned NOT NULL,
  `tax_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`item_id`,`tax_id`),
  KEY `fk_mit_tax` (`tax_id`),
  CONSTRAINT `fk_mit_item` FOREIGN KEY (`item_id`) REFERENCES `menu_items` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_mit_tax` FOREIGN KEY (`tax_id`) REFERENCES `taxes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `menu_item_taxes`
--

INSERT INTO `menu_item_taxes` VALUES (1,3),(1,4),(2,3),(2,4),(3,3),(3,4),(4,3),(4,4),(5,1),(5,2),(6,1),(6,2),(7,1),(7,2),(8,1),(8,2),(9,1),(9,2);

--
-- Table structure for table `menu_item_variations`
--

CREATE TABLE `menu_item_variations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `item_id` int(10) unsigned NOT NULL,
  `name` varchar(100) NOT NULL,
  `price` decimal(12,2) NOT NULL DEFAULT 0.00,
  `is_default` tinyint(1) NOT NULL DEFAULT 0,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  PRIMARY KEY (`id`),
  KEY `fk_miv_item` (`item_id`),
  CONSTRAINT `fk_miv_item` FOREIGN KEY (`item_id`) REFERENCES `menu_items` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `menu_item_variations`
--

INSERT INTO `menu_item_variations` VALUES (1,9,'Small',80.00,1,1,'active'),(2,9,'Medium',120.00,0,2,'active'),(3,9,'Large',150.00,0,3,'active');

--
-- Table structure for table `menu_items`
--

CREATE TABLE `menu_items` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(10) unsigned NOT NULL,
  `outlet_id` int(10) unsigned DEFAULT NULL,
  `category_id` int(10) unsigned NOT NULL,
  `station_id` int(10) unsigned DEFAULT NULL,
  `name` varchar(150) NOT NULL,
  `sku` varchar(50) DEFAULT NULL,
  `barcode` varchar(50) DEFAULT NULL,
  `description` varchar(500) DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `price` decimal(12,2) NOT NULL DEFAULT 0.00,
  `cost` decimal(12,2) NOT NULL DEFAULT 0.00,
  `tax_id` int(10) unsigned DEFAULT NULL,
  `food_type` enum('veg','non_veg','vegan','egg','other') NOT NULL DEFAULT 'veg',
  `is_available` tinyint(1) NOT NULL DEFAULT 1,
  `online_available` tinyint(1) NOT NULL DEFAULT 0,
  `room_service_available` tinyint(1) NOT NULL DEFAULT 0,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_item_company` (`company_id`),
  KEY `idx_item_category` (`category_id`),
  KEY `fk_mi_station` (`station_id`),
  CONSTRAINT `fk_mi_category` FOREIGN KEY (`category_id`) REFERENCES `menu_categories` (`id`),
  CONSTRAINT `fk_mi_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_mi_station` FOREIGN KEY (`station_id`) REFERENCES `kitchen_stations` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=111 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `menu_items`
--

INSERT INTO `menu_items` VALUES (1,1,1,1,2,'Paneer Tikka','FD-STR-001',NULL,'Chargrilled cottage cheese marinated in spiced yogurt',NULL,280.00,110.00,3,'veg',1,1,1,1,'active','2026-08-14 20:53:53','2026-08-14 20:53:53'),(2,1,1,1,3,'Chicken Manchurian','FD-STR-002',NULL,'Indo-Chinese style fried chicken in tangy sauce',NULL,320.00,140.00,3,'non_veg',1,1,1,2,'active','2026-08-14 20:53:53','2026-08-14 20:53:53'),(3,1,1,2,1,'Paneer Butter Masala','FD-MAIN-001',NULL,'Cottage cheese in creamy tomato gravy',NULL,340.00,130.00,3,'veg',1,1,1,1,'active','2026-08-14 20:53:53','2026-08-14 20:53:53'),(4,1,1,2,1,'Butter Chicken','FD-MAIN-002',NULL,'Classic North Indian butter chicken curry',NULL,380.00,160.00,3,'non_veg',1,1,1,2,'active','2026-08-14 20:53:53','2026-08-14 20:53:53'),(5,1,1,3,2,'Butter Naan','FD-BRD-001',NULL,'Tandoor baked leavened bread with butter',NULL,60.00,18.00,1,'veg',1,1,1,1,'active','2026-08-14 20:53:53','2026-08-14 20:53:53'),(6,1,1,4,4,'Masala Chai','FD-BEV-001',NULL,'Indian spiced tea',NULL,80.00,25.00,1,'veg',1,1,1,1,'active','2026-08-14 20:53:53','2026-08-14 20:53:53'),(7,1,1,4,4,'Fresh Lime Soda','FD-BEV-002',NULL,'Sweet or salted lime soda',NULL,90.00,22.00,1,'veg',1,1,1,2,'active','2026-08-14 20:53:53','2026-08-14 20:53:53'),(8,1,1,5,5,'Gulab Jamun','FD-DES-001',NULL,'Milk dumplings soaked in sugar syrup (2 pcs)',NULL,120.00,40.00,1,'veg',1,1,1,1,'active','2026-08-14 20:53:53','2026-08-14 20:53:53'),(9,1,1,4,4,'Coffee','FD-BEV-003',NULL,'Freshly brewed filter coffee',NULL,80.00,20.00,1,'veg',1,1,1,3,'active','2026-08-14 20:53:53','2026-08-14 20:53:53'),(10,2,NULL,7,NULL,'Plain Dosa',NULL,NULL,NULL,NULL,60.00,36.00,NULL,'veg',1,0,0,1,'active','2026-08-23 08:10:37','2026-08-23 08:10:37'),(11,2,NULL,7,NULL,'Masala Dosa',NULL,NULL,NULL,NULL,80.00,48.00,NULL,'veg',1,0,0,2,'active','2026-08-23 08:10:37','2026-08-23 08:10:37'),(12,2,NULL,7,NULL,'Mysore Masala Dosa',NULL,NULL,NULL,NULL,90.00,54.00,NULL,'veg',1,0,0,3,'active','2026-08-23 08:10:37','2026-08-23 08:10:37'),(13,2,NULL,7,NULL,'Rava Dosa',NULL,NULL,NULL,NULL,90.00,54.00,NULL,'veg',1,0,0,4,'active','2026-08-23 08:10:37','2026-08-23 08:10:37'),(14,2,NULL,7,NULL,'Onion Dosa',NULL,NULL,NULL,NULL,85.00,51.00,NULL,'veg',1,0,0,5,'active','2026-08-23 08:10:37','2026-08-23 08:10:37'),(15,2,NULL,7,NULL,'Paneer Dosa',NULL,NULL,NULL,NULL,110.00,66.00,NULL,'veg',1,0,0,6,'active','2026-08-23 08:10:37','2026-08-23 08:10:37'),(16,2,NULL,7,NULL,'Cheese Dosa',NULL,NULL,NULL,NULL,100.00,60.00,NULL,'veg',1,0,0,7,'active','2026-08-23 08:10:37','2026-08-23 08:10:37'),(17,2,NULL,7,NULL,'Set Dosa',NULL,NULL,NULL,NULL,70.00,42.00,NULL,'veg',1,0,0,8,'active','2026-08-23 08:10:37','2026-08-23 08:10:37'),(18,2,NULL,7,NULL,'Podi Dosa',NULL,NULL,NULL,NULL,85.00,51.00,NULL,'veg',1,0,0,9,'active','2026-08-23 08:10:37','2026-08-23 08:10:37'),(19,2,NULL,7,NULL,'Ghee Roast Dosa',NULL,NULL,NULL,NULL,95.00,57.00,NULL,'veg',1,0,0,10,'active','2026-08-23 08:10:37','2026-08-23 08:10:37'),(20,2,NULL,8,NULL,'Idli (2 pc)',NULL,NULL,NULL,NULL,50.00,30.00,NULL,'veg',1,0,0,1,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(21,2,NULL,8,NULL,'Medu Vada (2 pc)',NULL,NULL,NULL,NULL,55.00,33.00,NULL,'veg',1,0,0,2,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(22,2,NULL,8,NULL,'Sambar Vada',NULL,NULL,NULL,NULL,60.00,36.00,NULL,'veg',1,0,0,3,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(23,2,NULL,8,NULL,'Rava Idli',NULL,NULL,NULL,NULL,60.00,36.00,NULL,'veg',1,0,0,4,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(24,2,NULL,9,NULL,'Onion Uttapam',NULL,NULL,NULL,NULL,90.00,54.00,NULL,'veg',1,0,0,1,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(25,2,NULL,9,NULL,'Tomato Uttapam',NULL,NULL,NULL,NULL,90.00,54.00,NULL,'veg',1,0,0,2,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(26,2,NULL,9,NULL,'Ven Pongal',NULL,NULL,NULL,NULL,70.00,42.00,NULL,'veg',1,0,0,3,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(27,2,NULL,9,NULL,'Khara Bath',NULL,NULL,NULL,NULL,65.00,39.00,NULL,'veg',1,0,0,4,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(28,2,NULL,10,NULL,'Mini Tiffin Thali',NULL,NULL,NULL,NULL,150.00,90.00,NULL,'veg',1,0,0,1,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(29,2,NULL,10,NULL,'South Indian Full Thali',NULL,NULL,NULL,NULL,180.00,108.00,NULL,'veg',1,0,0,2,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(30,2,NULL,11,NULL,'Filter Coffee',NULL,NULL,NULL,NULL,40.00,24.00,NULL,'veg',1,0,0,1,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(31,2,NULL,11,NULL,'Masala Chai',NULL,NULL,NULL,NULL,35.00,21.00,NULL,'veg',1,0,0,2,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(32,2,NULL,11,NULL,'Buttermilk',NULL,NULL,NULL,NULL,30.00,18.00,NULL,'veg',1,0,0,3,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(33,2,NULL,11,NULL,'Fresh Lime Soda',NULL,NULL,NULL,NULL,40.00,24.00,NULL,'veg',1,0,0,4,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(34,3,NULL,12,NULL,'Tomato (1 kg)',NULL,NULL,NULL,NULL,40.00,24.00,NULL,'veg',1,0,0,1,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(35,3,NULL,12,NULL,'Onion (1 kg)',NULL,NULL,NULL,NULL,35.00,21.00,NULL,'veg',1,0,0,2,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(36,3,NULL,12,NULL,'Potato (1 kg)',NULL,NULL,NULL,NULL,30.00,18.00,NULL,'veg',1,0,0,3,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(37,3,NULL,12,NULL,'Carrot (1 kg)',NULL,NULL,NULL,NULL,50.00,30.00,NULL,'veg',1,0,0,4,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(38,3,NULL,12,NULL,'Cabbage (1 kg)',NULL,NULL,NULL,NULL,25.00,15.00,NULL,'veg',1,0,0,5,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(39,3,NULL,12,NULL,'Cauliflower (1 kg)',NULL,NULL,NULL,NULL,45.00,27.00,NULL,'veg',1,0,0,6,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(40,3,NULL,12,NULL,'Capsicum (1 kg)',NULL,NULL,NULL,NULL,60.00,36.00,NULL,'veg',1,0,0,7,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(41,3,NULL,12,NULL,'Cucumber (1 kg)',NULL,NULL,NULL,NULL,30.00,18.00,NULL,'veg',1,0,0,8,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(42,3,NULL,13,NULL,'Apple (1 kg)',NULL,NULL,NULL,NULL,180.00,108.00,NULL,'veg',1,0,0,1,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(43,3,NULL,13,NULL,'Banana (1 dozen)',NULL,NULL,NULL,NULL,60.00,36.00,NULL,'veg',1,0,0,2,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(44,3,NULL,13,NULL,'Mango (1 kg)',NULL,NULL,NULL,NULL,120.00,72.00,NULL,'veg',1,0,0,3,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(45,3,NULL,13,NULL,'Grapes (1 kg)',NULL,NULL,NULL,NULL,90.00,54.00,NULL,'veg',1,0,0,4,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(46,3,NULL,13,NULL,'Watermelon (1 pc)',NULL,NULL,NULL,NULL,35.00,21.00,NULL,'veg',1,0,0,5,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(47,3,NULL,13,NULL,'Papaya (1 kg)',NULL,NULL,NULL,NULL,40.00,24.00,NULL,'veg',1,0,0,6,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(48,3,NULL,13,NULL,'Pomegranate (1 kg)',NULL,NULL,NULL,NULL,150.00,90.00,NULL,'veg',1,0,0,7,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(49,3,NULL,13,NULL,'Orange (1 kg)',NULL,NULL,NULL,NULL,80.00,48.00,NULL,'veg',1,0,0,8,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(50,3,NULL,14,NULL,'Spinach (1 bunch)',NULL,NULL,NULL,NULL,25.00,15.00,NULL,'veg',1,0,0,1,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(51,3,NULL,14,NULL,'Coriander (1 bunch)',NULL,NULL,NULL,NULL,15.00,9.00,NULL,'veg',1,0,0,2,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(52,3,NULL,14,NULL,'Mint Leaves (1 bunch)',NULL,NULL,NULL,NULL,15.00,9.00,NULL,'veg',1,0,0,3,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(53,3,NULL,14,NULL,'Fenugreek Leaves (1 bunch)',NULL,NULL,NULL,NULL,20.00,12.00,NULL,'veg',1,0,0,4,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(54,3,NULL,15,NULL,'Avocado (1 pc)',NULL,NULL,NULL,NULL,90.00,54.00,NULL,'veg',1,0,0,1,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(55,3,NULL,15,NULL,'Broccoli (1 pc)',NULL,NULL,NULL,NULL,120.00,72.00,NULL,'veg',1,0,0,2,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(56,3,NULL,15,NULL,'Dragon Fruit (1 pc)',NULL,NULL,NULL,NULL,200.00,120.00,NULL,'veg',1,0,0,3,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(57,3,NULL,15,NULL,'Blueberries (125 g)',NULL,NULL,NULL,NULL,350.00,210.00,NULL,'veg',1,0,0,4,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(58,4,NULL,16,NULL,'Margherita Pizza',NULL,NULL,NULL,NULL,199.00,119.00,NULL,'veg',1,0,0,1,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(59,4,NULL,16,NULL,'Farmhouse Pizza',NULL,NULL,NULL,NULL,249.00,149.00,NULL,'veg',1,0,0,2,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(60,4,NULL,16,NULL,'Peppy Paneer Pizza',NULL,NULL,NULL,NULL,269.00,161.00,NULL,'veg',1,0,0,3,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(61,4,NULL,16,NULL,'Chicken Tikka Pizza',NULL,NULL,NULL,NULL,299.00,179.00,NULL,'non_veg',1,0,0,4,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(62,4,NULL,16,NULL,'Pepperoni Pizza',NULL,NULL,NULL,NULL,289.00,173.00,NULL,'non_veg',1,0,0,5,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(63,4,NULL,16,NULL,'Veggie Supreme Pizza',NULL,NULL,NULL,NULL,259.00,155.00,NULL,'veg',1,0,0,6,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(64,4,NULL,17,NULL,'Classic Veg Burger',NULL,NULL,NULL,NULL,99.00,59.00,NULL,'veg',1,0,0,1,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(65,4,NULL,17,NULL,'Cheese Burger',NULL,NULL,NULL,NULL,129.00,77.00,NULL,'veg',1,0,0,2,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(66,4,NULL,17,NULL,'Chicken Burger',NULL,NULL,NULL,NULL,149.00,89.00,NULL,'non_veg',1,0,0,3,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(67,4,NULL,17,NULL,'Double Patty Burger',NULL,NULL,NULL,NULL,179.00,107.00,NULL,'non_veg',1,0,0,4,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(68,4,NULL,17,NULL,'Crispy Paneer Burger',NULL,NULL,NULL,NULL,139.00,83.00,NULL,'veg',1,0,0,5,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(69,4,NULL,18,NULL,'French Fries',NULL,NULL,NULL,NULL,89.00,53.00,NULL,'veg',1,0,0,1,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(70,4,NULL,18,NULL,'Garlic Bread',NULL,NULL,NULL,NULL,99.00,59.00,NULL,'veg',1,0,0,2,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(71,4,NULL,18,NULL,'Cheese Nuggets',NULL,NULL,NULL,NULL,119.00,71.00,NULL,'veg',1,0,0,3,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(72,4,NULL,18,NULL,'Onion Rings',NULL,NULL,NULL,NULL,99.00,59.00,NULL,'veg',1,0,0,4,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(73,4,NULL,19,NULL,'Cola',NULL,NULL,NULL,NULL,60.00,36.00,NULL,'veg',1,0,0,1,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(74,4,NULL,19,NULL,'Iced Tea',NULL,NULL,NULL,NULL,70.00,42.00,NULL,'veg',1,0,0,2,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(75,4,NULL,19,NULL,'Milkshake',NULL,NULL,NULL,NULL,120.00,72.00,NULL,'veg',1,0,0,3,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(76,4,NULL,19,NULL,'Mineral Water',NULL,NULL,NULL,NULL,20.00,12.00,NULL,'veg',1,0,0,4,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(77,5,NULL,20,NULL,'Men\'s Casual Shirt',NULL,NULL,NULL,NULL,899.00,539.00,NULL,'veg',1,0,0,1,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(78,5,NULL,20,NULL,'Men\'s Formal Shirt',NULL,NULL,NULL,NULL,1099.00,659.00,NULL,'veg',1,0,0,2,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(79,5,NULL,20,NULL,'Men\'s Jeans',NULL,NULL,NULL,NULL,1499.00,899.00,NULL,'veg',1,0,0,3,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(80,5,NULL,20,NULL,'Men\'s T-Shirt',NULL,NULL,NULL,NULL,599.00,359.00,NULL,'veg',1,0,0,4,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(81,5,NULL,20,NULL,'Men\'s Kurta',NULL,NULL,NULL,NULL,1299.00,779.00,NULL,'veg',1,0,0,5,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(82,5,NULL,21,NULL,'Women\'s Kurti',NULL,NULL,NULL,NULL,799.00,479.00,NULL,'veg',1,0,0,1,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(83,5,NULL,21,NULL,'Women\'s Saree',NULL,NULL,NULL,NULL,2499.00,1499.00,NULL,'veg',1,0,0,2,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(84,5,NULL,21,NULL,'Women\'s Dress',NULL,NULL,NULL,NULL,1799.00,1079.00,NULL,'veg',1,0,0,3,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(85,5,NULL,21,NULL,'Women\'s Jeans',NULL,NULL,NULL,NULL,1399.00,839.00,NULL,'veg',1,0,0,4,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(86,5,NULL,21,NULL,'Women\'s Top',NULL,NULL,NULL,NULL,699.00,419.00,NULL,'veg',1,0,0,5,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(87,5,NULL,22,NULL,'Kids T-Shirt',NULL,NULL,NULL,NULL,399.00,239.00,NULL,'veg',1,0,0,1,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(88,5,NULL,22,NULL,'Kids Frock',NULL,NULL,NULL,NULL,699.00,419.00,NULL,'veg',1,0,0,2,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(89,5,NULL,22,NULL,'Kids Shorts',NULL,NULL,NULL,NULL,349.00,209.00,NULL,'veg',1,0,0,3,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(90,5,NULL,23,NULL,'Sneakers',NULL,NULL,NULL,NULL,1999.00,1199.00,NULL,'veg',1,0,0,1,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(91,5,NULL,23,NULL,'Sandals',NULL,NULL,NULL,NULL,899.00,539.00,NULL,'veg',1,0,0,2,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(92,5,NULL,23,NULL,'Leather Belt',NULL,NULL,NULL,NULL,599.00,359.00,NULL,'veg',1,0,0,3,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(93,5,NULL,23,NULL,'Handbag',NULL,NULL,NULL,NULL,1499.00,899.00,NULL,'veg',1,0,0,4,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(94,5,NULL,23,NULL,'Sunglasses',NULL,NULL,NULL,NULL,799.00,479.00,NULL,'veg',1,0,0,5,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(95,6,NULL,24,NULL,'Pressure Cooker 5L',NULL,NULL,NULL,NULL,1899.00,1139.00,NULL,'veg',1,0,0,1,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(96,6,NULL,24,NULL,'Non-Stick Fry Pan',NULL,NULL,NULL,NULL,799.00,479.00,NULL,'veg',1,0,0,2,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(97,6,NULL,24,NULL,'Kadai 12in',NULL,NULL,NULL,NULL,999.00,599.00,NULL,'veg',1,0,0,3,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(98,6,NULL,24,NULL,'Tawa 11in',NULL,NULL,NULL,NULL,599.00,359.00,NULL,'veg',1,0,0,4,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(99,6,NULL,24,NULL,'Casserole Set',NULL,NULL,NULL,NULL,1299.00,779.00,NULL,'veg',1,0,0,5,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(100,6,NULL,25,NULL,'Dinner Plate Set (6 pc)',NULL,NULL,NULL,NULL,899.00,539.00,NULL,'veg',1,0,0,1,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(101,6,NULL,25,NULL,'Steel Glass Set (6 pc)',NULL,NULL,NULL,NULL,399.00,239.00,NULL,'veg',1,0,0,2,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(102,6,NULL,25,NULL,'Bowl Set (6 pc)',NULL,NULL,NULL,NULL,449.00,269.00,NULL,'veg',1,0,0,3,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(103,6,NULL,25,NULL,'Serving Tray',NULL,NULL,NULL,NULL,349.00,209.00,NULL,'veg',1,0,0,4,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(104,6,NULL,26,NULL,'Storage Container Set',NULL,NULL,NULL,NULL,599.00,359.00,NULL,'veg',1,0,0,1,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(105,6,NULL,26,NULL,'Steel Lunch Box',NULL,NULL,NULL,NULL,349.00,209.00,NULL,'veg',1,0,0,2,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(106,6,NULL,26,NULL,'Insulated Water Bottle',NULL,NULL,NULL,NULL,499.00,299.00,NULL,'veg',1,0,0,3,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(107,6,NULL,27,NULL,'Spoon & Fork Set',NULL,NULL,NULL,NULL,349.00,209.00,NULL,'veg',1,0,0,1,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(108,6,NULL,27,NULL,'Kitchen Knife Set',NULL,NULL,NULL,NULL,599.00,359.00,NULL,'veg',1,0,0,2,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(109,6,NULL,27,NULL,'Peeler & Grater Set',NULL,NULL,NULL,NULL,249.00,149.00,NULL,'veg',1,0,0,3,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(110,6,NULL,27,NULL,'Ladle Set',NULL,NULL,NULL,NULL,299.00,179.00,NULL,'veg',1,0,0,4,'active','2026-08-23 08:10:38','2026-08-23 08:10:38');

--
-- Table structure for table `notifications`
--

CREATE TABLE `notifications` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(10) unsigned NOT NULL,
  `outlet_id` int(10) unsigned DEFAULT NULL,
  `user_id` int(10) unsigned DEFAULT NULL,
  `type` varchar(50) NOT NULL,
  `title` varchar(150) NOT NULL,
  `message` varchar(500) DEFAULT NULL,
  `reference_type` varchar(50) DEFAULT NULL,
  `reference_id` bigint(20) unsigned DEFAULT NULL,
  `is_read` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_notif_company` (`company_id`),
  CONSTRAINT `fk_notif_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `notifications`
--


--
-- Table structure for table `online_order_items`
--

CREATE TABLE `online_order_items` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `online_order_id` bigint(20) unsigned NOT NULL,
  `item_id` int(10) unsigned NOT NULL,
  `name` varchar(150) NOT NULL,
  `quantity` decimal(10,2) NOT NULL DEFAULT 1.00,
  `price` decimal(12,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`),
  KEY `fk_ooi_order` (`online_order_id`),
  CONSTRAINT `fk_ooi_order` FOREIGN KEY (`online_order_id`) REFERENCES `online_orders` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `online_order_items`
--


--
-- Table structure for table `online_orders`
--

CREATE TABLE `online_orders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `outlet_id` int(10) unsigned NOT NULL,
  `order_id` bigint(20) unsigned DEFAULT NULL,
  `source` enum('website','qr','mobile_app','aggregator','other') NOT NULL DEFAULT 'website',
  `external_reference` varchar(100) DEFAULT NULL,
  `customer_name` varchar(150) DEFAULT NULL,
  `customer_phone` varchar(20) DEFAULT NULL,
  `status` enum('received','accepted','preparing','ready','out_for_delivery','completed','cancelled') NOT NULL DEFAULT 'received',
  `total_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_oo_outlet` (`outlet_id`),
  CONSTRAINT `fk_oo_outlet` FOREIGN KEY (`outlet_id`) REFERENCES `outlets` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `online_orders`
--


--
-- Table structure for table `order_discounts`
--

CREATE TABLE `order_discounts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) unsigned NOT NULL,
  `discount_id` int(10) unsigned DEFAULT NULL,
  `coupon_id` int(10) unsigned DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `type` enum('percentage','fixed') NOT NULL DEFAULT 'percentage',
  `value` decimal(12,2) NOT NULL DEFAULT 0.00,
  `amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `approved_by` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_od_order` (`order_id`),
  CONSTRAINT `fk_od_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `order_discounts`
--


--
-- Table structure for table `order_item_addons`
--

CREATE TABLE `order_item_addons` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_item_id` bigint(20) unsigned NOT NULL,
  `addon_id` int(10) unsigned NOT NULL,
  `name` varchar(100) NOT NULL,
  `price` decimal(12,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`),
  KEY `fk_oia_orderitem` (`order_item_id`),
  CONSTRAINT `fk_oia_orderitem` FOREIGN KEY (`order_item_id`) REFERENCES `order_items` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `order_item_addons`
--


--
-- Table structure for table `order_item_modifiers`
--

CREATE TABLE `order_item_modifiers` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_item_id` bigint(20) unsigned NOT NULL,
  `modifier_id` int(10) unsigned NOT NULL,
  `name` varchar(100) NOT NULL,
  `price` decimal(12,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`),
  KEY `fk_oim_orderitem` (`order_item_id`),
  CONSTRAINT `fk_oim_orderitem` FOREIGN KEY (`order_item_id`) REFERENCES `order_items` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `order_item_modifiers`
--


--
-- Table structure for table `order_items`
--

CREATE TABLE `order_items` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) unsigned NOT NULL,
  `item_id` int(10) unsigned NOT NULL,
  `variation_id` int(10) unsigned DEFAULT NULL,
  `name` varchar(150) NOT NULL,
  `quantity` decimal(10,2) NOT NULL DEFAULT 1.00,
  `unit_price` decimal(12,2) NOT NULL DEFAULT 0.00,
  `discount_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `tax_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `subtotal` decimal(12,2) NOT NULL DEFAULT 0.00,
  `total` decimal(12,2) NOT NULL DEFAULT 0.00,
  `special_instruction` varchar(255) DEFAULT NULL,
  `status` enum('active','cancelled') NOT NULL DEFAULT 'active',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_oi_order` (`order_id`),
  KEY `fk_oi_item` (`item_id`),
  CONSTRAINT `fk_oi_item` FOREIGN KEY (`item_id`) REFERENCES `menu_items` (`id`),
  CONSTRAINT `fk_oi_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `order_items`
--

INSERT INTO `order_items` VALUES (1,1,3,NULL,'Paneer Butter Masala',1.00,340.00,0.00,0.00,340.00,340.00,NULL,'active','2026-08-14 15:42:37'),(2,2,5,NULL,'Butter Naan',2.00,60.00,0.00,0.00,120.00,120.00,NULL,'active','2026-08-23 09:28:54'),(3,3,30,NULL,'Filter Coffee',2.00,40.00,0.00,0.00,80.00,80.00,NULL,'active','2026-08-24 06:17:43'),(4,4,30,NULL,'Filter Coffee',1.00,40.00,0.00,0.00,40.00,40.00,NULL,'active','2026-08-24 06:17:44'),(5,5,6,NULL,'Masala Chai',1.00,80.00,0.00,0.00,80.00,80.00,NULL,'active','2026-08-24 07:10:24'),(6,6,64,NULL,'Classic Veg Burger',2.00,99.00,0.00,0.00,198.00,198.00,NULL,'active','2026-08-24 07:21:15'),(7,7,59,NULL,'Farmhouse Pizza',1.00,249.00,0.00,0.00,249.00,249.00,NULL,'active','2026-08-24 07:22:37'),(8,8,59,NULL,'Farmhouse Pizza',1.00,249.00,0.00,0.00,249.00,249.00,NULL,'active','2026-08-24 07:22:43'),(9,9,6,NULL,'Masala Chai',1.00,80.00,0.00,0.00,80.00,80.00,NULL,'active','2026-08-24 07:37:03'),(10,10,64,NULL,'Classic Veg Burger',1.00,99.00,0.00,0.00,99.00,99.00,NULL,'active','2026-08-25 09:06:52'),(11,10,73,NULL,'Cola',1.00,60.00,0.00,0.00,60.00,60.00,NULL,'active','2026-08-25 09:06:52');

--
-- Table structure for table `order_taxes`
--

CREATE TABLE `order_taxes` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) unsigned NOT NULL,
  `tax_id` int(10) unsigned NOT NULL,
  `name` varchar(100) NOT NULL,
  `rate` decimal(5,2) NOT NULL DEFAULT 0.00,
  `taxable_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `tax_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`),
  KEY `fk_ot_order` (`order_id`),
  CONSTRAINT `fk_ot_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `order_taxes`
--

INSERT INTO `order_taxes` VALUES (1,1,3,'CGST 6%',6.00,340.00,20.40),(2,2,1,'CGST 2.5%',2.50,120.00,3.00),(7,5,1,'CGST 2.5%',2.50,80.00,2.00),(8,5,2,'SGST 2.5%',2.50,80.00,2.00),(9,9,1,'CGST 2.5%',2.50,80.00,2.00),(10,9,2,'SGST 2.5%',2.50,80.00,2.00);

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

CREATE TABLE `orders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(10) unsigned NOT NULL,
  `outlet_id` int(10) unsigned NOT NULL,
  `order_number` varchar(50) NOT NULL,
  `order_type` enum('dine_in','takeaway','delivery','pickup','room_service','counter_sale','online') NOT NULL DEFAULT 'dine_in',
  `table_id` int(10) unsigned DEFAULT NULL,
  `customer_id` int(10) unsigned DEFAULT NULL,
  `hotel_room_id` int(10) unsigned DEFAULT NULL,
  `waiter_id` int(10) unsigned DEFAULT NULL,
  `cashier_id` int(10) unsigned DEFAULT NULL,
  `guests` int(10) unsigned NOT NULL DEFAULT 1,
  `subtotal` decimal(12,2) NOT NULL DEFAULT 0.00,
  `discount_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `tax_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `extra_amount` decimal(12,2) NOT NULL DEFAULT 0.00 COMMENT 'flat non-taxable addition, e.g. packing/delivery charge',
  `extra_charge_label` varchar(100) DEFAULT NULL,
  `round_off` decimal(6,2) NOT NULL DEFAULT 0.00,
  `grand_total` decimal(12,2) NOT NULL DEFAULT 0.00,
  `paid_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `balance_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `notes` varchar(255) DEFAULT NULL,
  `status` enum('draft','held','placed','preparing','ready','served','billed','paid','cancelled','closed') NOT NULL DEFAULT 'draft',
  `cancel_reason` varchar(255) DEFAULT NULL,
  `charged_to_room` tinyint(1) NOT NULL DEFAULT 0,
  `placed_at` datetime DEFAULT NULL,
  `closed_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_order_outlet_number` (`outlet_id`,`order_number`),
  KEY `idx_order_company_date` (`company_id`,`created_at`),
  KEY `fk_order_table` (`table_id`),
  KEY `fk_order_customer` (`customer_id`),
  CONSTRAINT `fk_order_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_order_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`),
  CONSTRAINT `fk_order_outlet` FOREIGN KEY (`outlet_id`) REFERENCES `outlets` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_order_table` FOREIGN KEY (`table_id`) REFERENCES `tables` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `orders` VALUES (1,1,1,'INV-PTZ01-2026-000001','counter_sale',NULL,NULL,NULL,1,1,1,340.00,0.00,20.40,0.00,NULL,0.00,360.40,360.40,0.00,NULL,'paid',NULL,0,'2026-08-14 15:42:37',NULL,'2026-08-14 15:42:37','2026-08-14 15:42:37'),(2,1,1,'INV-PTZ01-2026-000002','counter_sale',NULL,NULL,NULL,1,1,1,120.00,0.00,3.00,0.00,NULL,0.00,123.00,123.00,0.00,NULL,'paid',NULL,0,'2026-08-23 09:28:54',NULL,'2026-08-23 09:28:54','2026-08-23 09:30:37'),(3,2,2,'INV-UTC01-2026-000003','counter_sale',NULL,NULL,NULL,7,7,1,80.00,0.00,0.00,0.00,NULL,0.00,80.00,80.00,0.00,NULL,'paid',NULL,0,NULL,NULL,'2026-08-24 06:17:43','2026-08-24 06:17:43'),(4,2,3,'INV-UTC02-2026-000004','counter_sale',NULL,NULL,NULL,7,7,1,40.00,0.00,0.00,0.00,NULL,0.00,40.00,40.00,0.00,NULL,'paid',NULL,0,NULL,NULL,'2026-08-24 06:17:44','2026-08-24 06:17:44'),(5,1,1,'INV-PTZ01-2026-000005','counter_sale',NULL,NULL,NULL,1,1,1,80.00,0.00,4.00,0.00,NULL,0.00,84.00,84.00,0.00,NULL,'paid',NULL,0,NULL,NULL,'2026-08-24 07:10:24','2026-08-24 07:10:39'),(6,4,5,'INV-SP01-2026-000006','counter_sale',NULL,NULL,NULL,9,9,1,198.00,0.00,0.00,0.00,NULL,0.00,198.00,0.00,198.00,NULL,'draft',NULL,0,NULL,NULL,'2026-08-24 07:21:15','2026-08-24 07:21:15'),(7,4,5,'INV-SP01-2026-000007','counter_sale',NULL,NULL,NULL,9,9,1,249.00,0.00,0.00,0.00,NULL,0.00,249.00,0.00,249.00,NULL,'draft',NULL,0,NULL,NULL,'2026-08-24 07:22:37','2026-08-24 07:22:37'),(8,4,5,'INV-SP01-2026-000008','counter_sale',NULL,NULL,NULL,9,9,1,249.00,0.00,0.00,0.00,NULL,0.00,249.00,0.00,249.00,NULL,'draft',NULL,0,NULL,NULL,'2026-08-24 07:22:43','2026-08-24 07:22:43'),(9,1,1,'INV-PTZ01-2026-000009','counter_sale',NULL,NULL,NULL,1,1,1,80.00,0.00,4.00,0.00,NULL,0.00,84.00,0.00,84.00,NULL,'cancelled','test cleanup',0,NULL,NULL,'2026-08-24 07:37:03','2026-08-24 07:37:57'),(10,4,5,'INV-SP01-2026-000010','counter_sale',NULL,NULL,NULL,9,9,1,159.00,0.00,0.00,0.00,NULL,0.00,159.00,159.00,0.00,NULL,'paid',NULL,0,NULL,NULL,'2026-08-25 09:06:52','2026-08-25 09:07:05');

--
-- Table structure for table `outlets`
--

CREATE TABLE `outlets` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(10) unsigned NOT NULL,
  `name` varchar(150) NOT NULL,
  `code` varchar(20) NOT NULL,
  `type` enum('restaurant','hotel','resort','cafe','qsr','other') NOT NULL DEFAULT 'restaurant',
  `address` varchar(255) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `email` varchar(150) DEFAULT NULL,
  `gstin` varchar(20) DEFAULT NULL,
  `invoice_prefix` varchar(20) NOT NULL DEFAULT 'INV',
  `kot_prefix` varchar(20) NOT NULL DEFAULT 'KOT',
  `reservation_prefix` varchar(20) NOT NULL DEFAULT 'RES',
  `room_prefix` varchar(20) NOT NULL DEFAULT 'ROOM',
  `timezone` varchar(60) NOT NULL DEFAULT 'Asia/Kolkata',
  `opening_time` time DEFAULT NULL,
  `closing_time` time DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_outlet_company_code` (`company_id`,`code`),
  CONSTRAINT `fk_outlet_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `outlets`
--

INSERT INTO `outlets` VALUES (1,1,'PetZY Hotel & Restaurant','PTZ01','hotel','12 MG Road, Bengaluru','+91-9876500002','outlet1@petzyhospitality.example','29ABCDE1234F1Z5','INV-PTZ01','KOT-PTZ01','RES-PTZ01','ROOM-PTZ01','Asia/Kolkata','00:00:00','23:59:59','active','2026-08-14 20:53:53','2026-08-14 20:53:53'),(2,2,'Udupi Tiffin – Indiranagar','UTC01','restaurant','100 Ft Road, Indiranagar, Bengaluru',NULL,NULL,NULL,'INV-UTC01','KOT-UTC01','RES-UTC01','ROOM-UTC01','Asia/Kolkata','08:00:00','22:00:00','active','2026-08-23 08:10:37','2026-08-23 08:10:37'),(3,2,'Udupi Tiffin – Koramangala','UTC02','restaurant','80 Ft Road, Koramangala, Bengaluru',NULL,NULL,NULL,'INV-UTC02','KOT-UTC02','RES-UTC02','ROOM-UTC02','Asia/Kolkata','08:00:00','22:00:00','active','2026-08-23 08:10:37','2026-08-23 08:10:37'),(4,3,'Fresh Basket – Whitefield','FB01','other','ITPL Main Road, Whitefield, Bengaluru',NULL,NULL,NULL,'INV-FB01','KOT-FB01','RES-FB01','ROOM-FB01','Asia/Kolkata','08:00:00','22:00:00','active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(5,4,'SlicePoint – MG Road','SP01','qsr','MG Road, Bengaluru',NULL,NULL,NULL,'INV-SP01','KOT-SP01','RES-SP01','ROOM-SP01','Asia/Kolkata','08:00:00','22:00:00','active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(6,4,'SlicePoint – HSR Layout','SP02','qsr','HSR Layout, Bengaluru',NULL,NULL,NULL,'INV-SP02','KOT-SP02','RES-SP02','ROOM-SP02','Asia/Kolkata','08:00:00','22:00:00','active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(7,5,'Trendz Fashion – Commercial Street','TF01','other','Commercial Street, Bengaluru',NULL,NULL,NULL,'INV-TF01','KOT-TF01','RES-TF01','ROOM-TF01','Asia/Kolkata','08:00:00','22:00:00','active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(8,6,'Griha Bartan – Chickpet','GB01','other','Chickpet, Bengaluru',NULL,NULL,NULL,'INV-GB01','KOT-GB01','RES-GB01','ROOM-GB01','Asia/Kolkata','08:00:00','22:00:00','active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(9,2,'Udupi Tiffin � Test Outlet','UTC99','restaurant',NULL,NULL,NULL,NULL,'INV','KOT','RES','ROOM','Asia/Kolkata',NULL,NULL,'inactive','2026-08-24 06:18:12','2026-08-24 06:18:52');

--
-- Table structure for table `payment_transactions`
--

CREATE TABLE `payment_transactions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `payment_id` bigint(20) unsigned NOT NULL,
  `gateway` varchar(50) DEFAULT NULL,
  `gateway_transaction_id` varchar(150) DEFAULT NULL,
  `raw_response` text DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_pt_payment` (`payment_id`),
  CONSTRAINT `fk_pt_payment` FOREIGN KEY (`payment_id`) REFERENCES `payments` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `payment_transactions`
--


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

CREATE TABLE `payments` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(10) unsigned NOT NULL,
  `outlet_id` int(10) unsigned NOT NULL,
  `order_id` bigint(20) unsigned DEFAULT NULL,
  `folio_id` int(10) unsigned DEFAULT NULL,
  `payment_number` varchar(50) NOT NULL,
  `amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `method` enum('cash','card','upi','bank_transfer','wallet','online','cheque','cod','room_charge','credit','other') NOT NULL DEFAULT 'cash',
  `reference_number` varchar(100) DEFAULT NULL,
  `received_by` int(10) unsigned DEFAULT NULL,
  `status` enum('success','pending','failed','refunded') NOT NULL DEFAULT 'success',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_payment_outlet_number` (`outlet_id`,`payment_number`),
  KEY `fk_payment_company` (`company_id`),
  KEY `fk_payment_order` (`order_id`),
  CONSTRAINT `fk_payment_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_payment_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `payments` VALUES (1,1,1,1,NULL,'INV-PTZ01-PAY-2026-000001',360.40,'cash',NULL,1,'success','2026-08-14 15:42:37'),(2,1,1,2,NULL,'INV-PTZ01-PAY-2026-000002',123.00,'cash',NULL,1,'success','2026-08-23 09:30:37'),(3,2,2,3,NULL,'INV-UTC01-PAY-2026-000003',80.00,'cash',NULL,7,'success','2026-08-24 06:17:43'),(4,2,3,4,NULL,'INV-UTC02-PAY-2026-000004',40.00,'cash',NULL,7,'success','2026-08-24 06:17:44'),(5,1,1,5,NULL,'INV-PTZ01-PAY-2026-000005',50.00,'cash',NULL,1,'success','2026-08-24 07:10:39'),(6,1,1,5,NULL,'INV-PTZ01-PAY-2026-000006',34.00,'upi','UPI-XYZ',1,'success','2026-08-24 07:10:39'),(7,4,5,10,NULL,'INV-SP01-PAY-2026-000007',159.00,'cash',NULL,9,'success','2026-08-25 09:07:05');

--
-- Table structure for table `payroll_items`
--

CREATE TABLE `payroll_items` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `payroll_run_id` int(10) unsigned NOT NULL,
  `employee_id` int(10) unsigned NOT NULL,
  `present_days` decimal(5,1) NOT NULL DEFAULT 0.0,
  `half_days` decimal(5,1) NOT NULL DEFAULT 0.0,
  `absent_days` decimal(5,1) NOT NULL DEFAULT 0.0,
  `paid_leave_days` decimal(5,1) NOT NULL DEFAULT 0.0,
  `unpaid_leave_days` decimal(5,1) NOT NULL DEFAULT 0.0,
  `week_off_days` decimal(5,1) NOT NULL DEFAULT 0.0,
  `holiday_days` decimal(5,1) NOT NULL DEFAULT 0.0,
  `comp_worked_days` decimal(5,1) NOT NULL DEFAULT 0.0,
  `payable_days` decimal(5,1) NOT NULL DEFAULT 0.0,
  `total_days_in_period` smallint(5) unsigned NOT NULL,
  `basic_salary` decimal(12,2) NOT NULL DEFAULT 0.00,
  `hra` decimal(12,2) NOT NULL DEFAULT 0.00,
  `other_allowances` decimal(12,2) NOT NULL DEFAULT 0.00,
  `gross_salary` decimal(12,2) NOT NULL DEFAULT 0.00,
  `lop_deduction` decimal(12,2) NOT NULL DEFAULT 0.00,
  `net_salary` decimal(12,2) NOT NULL DEFAULT 0.00,
  `status` enum('pending','paid') NOT NULL DEFAULT 'pending',
  `paid_at` datetime DEFAULT NULL,
  `payment_method` varchar(30) DEFAULT NULL,
  `payment_reference` varchar(100) DEFAULT NULL,
  `expense_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_payitem_run_employee` (`payroll_run_id`,`employee_id`),
  KEY `fk_pi_employee` (`employee_id`),
  CONSTRAINT `fk_pi_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`),
  CONSTRAINT `fk_pi_run` FOREIGN KEY (`payroll_run_id`) REFERENCES `payroll_runs` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `payroll_items`
--

INSERT INTO `payroll_items` VALUES (1,1,3,15.0,1.0,1.0,0.0,0.0,4.0,1.0,1.0,21.5,31,22000.00,2500.00,1500.00,26000.00,838.71,25161.29,'paid','2026-08-24 06:41:24','bank_transfer','TESTPAY001',1),(2,1,6,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,31,19000.00,1800.00,1000.00,21800.00,0.00,21800.00,'pending',NULL,NULL,NULL,NULL),(3,1,2,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,31,42000.00,6000.00,3000.00,51000.00,0.00,51000.00,'pending',NULL,NULL,NULL,NULL),(4,1,5,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,31,20000.00,2000.00,1500.00,23500.00,0.00,23500.00,'pending',NULL,NULL,NULL,NULL),(5,1,4,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,31,18000.00,1500.00,1000.00,20500.00,0.00,20500.00,'pending',NULL,NULL,NULL,NULL),(6,1,1,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,0.0,31,60000.00,10000.00,5000.00,75000.00,0.00,75000.00,'pending',NULL,NULL,NULL,NULL);

--
-- Table structure for table `payroll_runs`
--

CREATE TABLE `payroll_runs` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(10) unsigned NOT NULL,
  `outlet_id` int(10) unsigned DEFAULT NULL,
  `month` tinyint(3) unsigned NOT NULL,
  `year` smallint(5) unsigned NOT NULL,
  `status` enum('draft','finalized') NOT NULL DEFAULT 'draft',
  `generated_by` int(10) unsigned DEFAULT NULL,
  `generated_at` datetime NOT NULL DEFAULT current_timestamp(),
  `finalized_by` int(10) unsigned DEFAULT NULL,
  `finalized_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_pr_company` (`company_id`),
  CONSTRAINT `fk_pr_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `payroll_runs`
--

INSERT INTO `payroll_runs` VALUES (1,1,1,8,2026,'finalized',1,'2026-08-24 06:40:56',1,'2026-08-24 06:41:25');

--
-- Table structure for table `permissions`
--

CREATE TABLE `permissions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `module` varchar(60) NOT NULL,
  `slug` varchar(100) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB AUTO_INCREMENT=47 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `permissions`
--

INSERT INTO `permissions` VALUES (1,'dashboard','dashboard.view','View dashboard','2026-08-14 20:53:52'),(2,'pos','pos.view','View POS','2026-08-14 20:53:52'),(3,'pos','pos.create','Create POS order','2026-08-14 20:53:52'),(4,'pos','pos.edit','Edit POS order','2026-08-14 20:53:52'),(5,'pos','pos.delete','Delete POS order','2026-08-14 20:53:52'),(6,'pos','pos.discount','Apply discount in POS','2026-08-14 20:53:52'),(7,'pos','pos.refund','Process refund in POS','2026-08-14 20:53:52'),(8,'orders','orders.view','View orders','2026-08-14 20:53:52'),(9,'orders','orders.create','Create orders','2026-08-14 20:53:52'),(10,'orders','orders.edit','Edit orders','2026-08-14 20:53:52'),(11,'orders','orders.cancel','Cancel orders','2026-08-14 20:53:52'),(12,'payments','payments.view','View payments','2026-08-14 20:53:52'),(13,'payments','payments.create','Take payments','2026-08-14 20:53:52'),(14,'menu','menu.view','View menu','2026-08-14 20:53:52'),(15,'menu','menu.manage','Manage menu categories/items','2026-08-14 20:53:52'),(16,'tables','tables.view','View tables','2026-08-14 20:53:52'),(17,'tables','tables.manage','Manage tables/floors','2026-08-14 20:53:52'),(18,'kot','kot.view','View KOT','2026-08-14 20:53:52'),(19,'kitchen','kitchen.view','View kitchen display','2026-08-14 20:53:52'),(20,'kitchen','kitchen.update','Update KOT status','2026-08-14 20:53:52'),(21,'inventory','inventory.view','View inventory','2026-08-14 20:53:52'),(22,'inventory','inventory.create','Create inventory items','2026-08-14 20:53:52'),(23,'inventory','inventory.adjust','Adjust stock','2026-08-14 20:53:52'),(24,'purchase','purchase.view','View purchases','2026-08-14 20:53:52'),(25,'purchase','purchase.create','Create purchase orders/invoices','2026-08-14 20:53:52'),(26,'customers','customers.view','View customers','2026-08-14 20:53:52'),(27,'customers','customers.manage','Manage customers','2026-08-14 20:53:52'),(28,'hotel','hotel.view','View hotel module','2026-08-14 20:53:52'),(29,'hotel','hotel.checkin','Perform check-in','2026-08-14 20:53:52'),(30,'hotel','hotel.checkout','Perform check-out','2026-08-14 20:53:52'),(31,'hotel','hotel.manage','Manage rooms/room types/reservations','2026-08-14 20:53:52'),(32,'housekeeping','housekeeping.view','View housekeeping','2026-08-14 20:53:52'),(33,'housekeeping','housekeeping.update','Update housekeeping status','2026-08-14 20:53:52'),(34,'reports','reports.view','View reports','2026-08-14 20:53:52'),(35,'employees','employees.view','View employees','2026-08-14 20:53:52'),(36,'employees','employees.manage','Manage employees','2026-08-14 20:53:52'),(37,'expenses','expenses.view','View expenses','2026-08-14 20:53:52'),(38,'expenses','expenses.create','Create expenses','2026-08-14 20:53:52'),(39,'cash','cash.manage','Open/close cash register','2026-08-14 20:53:52'),(40,'dayend','dayend.close','Perform day-end close','2026-08-14 20:53:52'),(41,'settings','settings.view','View settings','2026-08-14 20:53:52'),(42,'settings','settings.edit','Edit settings','2026-08-14 20:53:52'),(43,'users','users.manage','Manage users/roles/permissions','2026-08-14 20:53:52'),(44,'audit','audit.view','View audit logs','2026-08-14 20:53:52'),(45,'payroll','payroll.view','View payroll','2026-08-24 12:09:23'),(46,'payroll','payroll.manage','Manage payroll (attendance, leave approval, generate/finalize/pay runs)','2026-08-24 12:09:23');

--
-- Table structure for table `printer_routes`
--

CREATE TABLE `printer_routes` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `printer_id` int(10) unsigned NOT NULL,
  `category_id` int(10) unsigned DEFAULT NULL,
  `station_id` int(10) unsigned DEFAULT NULL,
  `order_type` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_proute_printer` (`printer_id`),
  CONSTRAINT `fk_proute_printer` FOREIGN KEY (`printer_id`) REFERENCES `printers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `printer_routes`
--


--
-- Table structure for table `printers`
--

CREATE TABLE `printers` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `outlet_id` int(10) unsigned NOT NULL,
  `name` varchar(100) NOT NULL,
  `type` enum('bill','kot','kitchen','receipt','label') NOT NULL DEFAULT 'kot',
  `paper_size` enum('58mm','80mm','A4') NOT NULL DEFAULT '80mm',
  `ip_address` varchar(60) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  PRIMARY KEY (`id`),
  KEY `fk_printer_outlet` (`outlet_id`),
  CONSTRAINT `fk_printer_outlet` FOREIGN KEY (`outlet_id`) REFERENCES `outlets` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `printers`
--


--
-- Table structure for table `purchase_invoice_items`
--

CREATE TABLE `purchase_invoice_items` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `purchase_invoice_id` int(10) unsigned NOT NULL,
  `inventory_item_id` int(10) unsigned NOT NULL,
  `quantity` decimal(14,3) NOT NULL,
  `unit_price` decimal(12,2) NOT NULL DEFAULT 0.00,
  `total` decimal(12,2) NOT NULL DEFAULT 0.00,
  `tax_id` int(10) unsigned DEFAULT NULL,
  `tax_rate` decimal(5,2) NOT NULL DEFAULT 0.00,
  `tax_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`),
  KEY `fk_pii_invoice` (`purchase_invoice_id`),
  KEY `fk_pii_item` (`inventory_item_id`),
  KEY `fk_pii_tax` (`tax_id`),
  CONSTRAINT `fk_pii_invoice` FOREIGN KEY (`purchase_invoice_id`) REFERENCES `purchase_invoices` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_pii_item` FOREIGN KEY (`inventory_item_id`) REFERENCES `inventory_items` (`id`),
  CONSTRAINT `fk_pii_tax` FOREIGN KEY (`tax_id`) REFERENCES `taxes` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `purchase_invoice_items`
--

INSERT INTO `purchase_invoice_items` VALUES (1,1,1,10.000,320.00,3200.00,3,6.00,192.00);

--
-- Table structure for table `purchase_invoices`
--

CREATE TABLE `purchase_invoices` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(10) unsigned NOT NULL,
  `outlet_id` int(10) unsigned NOT NULL,
  `purchase_order_id` int(10) unsigned DEFAULT NULL,
  `supplier_id` int(10) unsigned NOT NULL,
  `invoice_number` varchar(50) NOT NULL,
  `supplier_invoice_number` varchar(100) DEFAULT NULL,
  `invoice_date` date NOT NULL,
  `subtotal` decimal(12,2) NOT NULL DEFAULT 0.00,
  `tax_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `total_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `paid_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `balance_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `status` enum('unpaid','partially_paid','paid') NOT NULL DEFAULT 'unpaid',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_pi_company_number` (`company_id`,`invoice_number`),
  KEY `fk_pi_supplier` (`supplier_id`),
  CONSTRAINT `fk_pi_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `purchase_invoices`
--

INSERT INTO `purchase_invoices` VALUES (1,1,1,NULL,1,'PINV-1786722157848','SUP-TEST-1','2026-08-14',3200.00,192.00,3392.00,0.00,3392.00,'unpaid','2026-08-14 15:42:37');

--
-- Table structure for table `purchase_order_items`
--

CREATE TABLE `purchase_order_items` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `purchase_order_id` int(10) unsigned NOT NULL,
  `inventory_item_id` int(10) unsigned NOT NULL,
  `quantity` decimal(14,3) NOT NULL,
  `unit_price` decimal(12,2) NOT NULL DEFAULT 0.00,
  `total` decimal(12,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`),
  KEY `fk_poi_po` (`purchase_order_id`),
  KEY `fk_poi_item` (`inventory_item_id`),
  CONSTRAINT `fk_poi_item` FOREIGN KEY (`inventory_item_id`) REFERENCES `inventory_items` (`id`),
  CONSTRAINT `fk_poi_po` FOREIGN KEY (`purchase_order_id`) REFERENCES `purchase_orders` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `purchase_order_items`
--


--
-- Table structure for table `purchase_orders`
--

CREATE TABLE `purchase_orders` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(10) unsigned NOT NULL,
  `outlet_id` int(10) unsigned NOT NULL,
  `supplier_id` int(10) unsigned NOT NULL,
  `po_number` varchar(50) NOT NULL,
  `status` enum('draft','sent','partially_received','received','cancelled') NOT NULL DEFAULT 'draft',
  `expected_date` date DEFAULT NULL,
  `total_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `created_by` int(10) unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_po_company_number` (`company_id`,`po_number`),
  KEY `fk_po_supplier` (`supplier_id`),
  CONSTRAINT `fk_po_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `purchase_orders`
--


--
-- Table structure for table `purchase_return_items`
--

CREATE TABLE `purchase_return_items` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `purchase_return_id` int(10) unsigned NOT NULL,
  `inventory_item_id` int(10) unsigned NOT NULL,
  `quantity` decimal(14,3) NOT NULL,
  `unit_price` decimal(12,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`),
  KEY `fk_pri_return` (`purchase_return_id`),
  CONSTRAINT `fk_pri_return` FOREIGN KEY (`purchase_return_id`) REFERENCES `purchase_returns` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `purchase_return_items`
--


--
-- Table structure for table `purchase_returns`
--

CREATE TABLE `purchase_returns` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `purchase_invoice_id` int(10) unsigned NOT NULL,
  `return_number` varchar(50) NOT NULL,
  `reason` varchar(255) DEFAULT NULL,
  `total_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_pr_invoice` (`purchase_invoice_id`),
  CONSTRAINT `fk_pr_invoice` FOREIGN KEY (`purchase_invoice_id`) REFERENCES `purchase_invoices` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `purchase_returns`
--


--
-- Table structure for table `qr_menu_tables`
--

CREATE TABLE `qr_menu_tables` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `qr_menu_id` int(10) unsigned NOT NULL,
  `table_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_qmt_menu` (`qr_menu_id`),
  KEY `fk_qmt_table` (`table_id`),
  CONSTRAINT `fk_qmt_menu` FOREIGN KEY (`qr_menu_id`) REFERENCES `qr_menus` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_qmt_table` FOREIGN KEY (`table_id`) REFERENCES `tables` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `qr_menu_tables`
--


--
-- Table structure for table `qr_menus`
--

CREATE TABLE `qr_menus` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `outlet_id` int(10) unsigned NOT NULL,
  `name` varchar(100) NOT NULL,
  `qr_code` varchar(255) NOT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_qm_outlet` (`outlet_id`),
  CONSTRAINT `fk_qm_outlet` FOREIGN KEY (`outlet_id`) REFERENCES `outlets` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `qr_menus`
--


--
-- Table structure for table `recipe_items`
--

CREATE TABLE `recipe_items` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `recipe_id` int(10) unsigned NOT NULL,
  `inventory_item_id` int(10) unsigned NOT NULL,
  `quantity` decimal(12,3) NOT NULL,
  `unit_id` int(10) unsigned NOT NULL,
  `cost` decimal(12,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`),
  KEY `fk_ri_recipe` (`recipe_id`),
  KEY `fk_ri_item` (`inventory_item_id`),
  CONSTRAINT `fk_ri_item` FOREIGN KEY (`inventory_item_id`) REFERENCES `inventory_items` (`id`),
  CONSTRAINT `fk_ri_recipe` FOREIGN KEY (`recipe_id`) REFERENCES `recipes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `recipe_items`
--

INSERT INTO `recipe_items` VALUES (1,1,1,250.000,4,80.00),(2,1,2,150.000,1,6.00),(3,1,3,30.000,4,14.40),(4,1,4,50.000,2,13.00),(5,1,5,10.000,4,9.00);

--
-- Table structure for table `recipes`
--

CREATE TABLE `recipes` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `item_id` int(10) unsigned NOT NULL,
  `yield_quantity` decimal(10,2) NOT NULL DEFAULT 1.00,
  `wastage_percent` decimal(5,2) NOT NULL DEFAULT 0.00,
  `food_cost` decimal(12,2) NOT NULL DEFAULT 0.00,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_recipe_item` (`item_id`),
  CONSTRAINT `fk_recipe_item` FOREIGN KEY (`item_id`) REFERENCES `menu_items` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `recipes`
--

INSERT INTO `recipes` VALUES (1,3,1.00,5.00,130.00,'2026-08-14 20:53:53');

--
-- Table structure for table `refunds`
--

CREATE TABLE `refunds` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) unsigned NOT NULL,
  `payment_id` bigint(20) unsigned DEFAULT NULL,
  `amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `reason` varchar(255) NOT NULL,
  `reference_number` varchar(100) DEFAULT NULL,
  `approved_by` int(10) unsigned DEFAULT NULL,
  `refunded_by` int(10) unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_refund_order` (`order_id`),
  CONSTRAINT `fk_refund_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `refunds`
--


--
-- Table structure for table `restaurant_reservations`
--

CREATE TABLE `restaurant_reservations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `outlet_id` int(10) unsigned NOT NULL,
  `customer_id` int(10) unsigned DEFAULT NULL,
  `table_id` int(10) unsigned DEFAULT NULL,
  `reservation_date` date NOT NULL,
  `reservation_time` time NOT NULL,
  `guests` int(10) unsigned NOT NULL DEFAULT 1,
  `special_request` varchar(255) DEFAULT NULL,
  `source` varchar(50) NOT NULL DEFAULT 'walk_in',
  `status` enum('pending','confirmed','arrived','seated','completed','cancelled','no_show') NOT NULL DEFAULT 'pending',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_rr_outlet` (`outlet_id`),
  KEY `fk_rr_customer` (`customer_id`),
  KEY `fk_rr_table` (`table_id`),
  CONSTRAINT `fk_rr_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`),
  CONSTRAINT `fk_rr_outlet` FOREIGN KEY (`outlet_id`) REFERENCES `outlets` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_rr_table` FOREIGN KEY (`table_id`) REFERENCES `tables` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `restaurant_reservations`
--


--
-- Table structure for table `restaurant_room_charges`
--

CREATE TABLE `restaurant_room_charges` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) unsigned NOT NULL,
  `folio_id` int(10) unsigned NOT NULL,
  `folio_item_id` int(10) unsigned DEFAULT NULL,
  `amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `charged_by` int(10) unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_rrc_order` (`order_id`),
  KEY `fk_rrc_folio` (`folio_id`),
  CONSTRAINT `fk_rrc_folio` FOREIGN KEY (`folio_id`) REFERENCES `hotel_folios` (`id`),
  CONSTRAINT `fk_rrc_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `restaurant_room_charges`
--


--
-- Table structure for table `role_permissions`
--

CREATE TABLE `role_permissions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `role_id` int(10) unsigned NOT NULL,
  `permission_id` int(10) unsigned NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_role_permission` (`role_id`,`permission_id`),
  KEY `fk_rp_permission` (`permission_id`),
  CONSTRAINT `fk_rp_permission` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_rp_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=531 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `role_permissions`
--

INSERT INTO `role_permissions` VALUES (1,2,44,'2026-08-14 20:53:53'),(2,2,39,'2026-08-14 20:53:53'),(3,2,27,'2026-08-14 20:53:53'),(4,2,26,'2026-08-14 20:53:53'),(5,2,1,'2026-08-14 20:53:53'),(6,2,40,'2026-08-14 20:53:53'),(7,2,36,'2026-08-14 20:53:53'),(8,2,35,'2026-08-14 20:53:53'),(9,2,38,'2026-08-14 20:53:53'),(10,2,37,'2026-08-14 20:53:53'),(11,2,29,'2026-08-14 20:53:53'),(12,2,30,'2026-08-14 20:53:53'),(13,2,31,'2026-08-14 20:53:53'),(14,2,28,'2026-08-14 20:53:53'),(15,2,33,'2026-08-14 20:53:53'),(16,2,32,'2026-08-14 20:53:53'),(17,2,23,'2026-08-14 20:53:53'),(18,2,22,'2026-08-14 20:53:53'),(19,2,21,'2026-08-14 20:53:53'),(20,2,20,'2026-08-14 20:53:53'),(21,2,19,'2026-08-14 20:53:53'),(22,2,18,'2026-08-14 20:53:53'),(23,2,15,'2026-08-14 20:53:53'),(24,2,14,'2026-08-14 20:53:53'),(25,2,11,'2026-08-14 20:53:53'),(26,2,9,'2026-08-14 20:53:53'),(27,2,10,'2026-08-14 20:53:53'),(28,2,8,'2026-08-14 20:53:53'),(29,2,13,'2026-08-14 20:53:53'),(30,2,12,'2026-08-14 20:53:53'),(31,2,3,'2026-08-14 20:53:53'),(32,2,5,'2026-08-14 20:53:53'),(33,2,6,'2026-08-14 20:53:53'),(34,2,4,'2026-08-14 20:53:53'),(35,2,7,'2026-08-14 20:53:53'),(36,2,2,'2026-08-14 20:53:53'),(37,2,25,'2026-08-14 20:53:53'),(38,2,24,'2026-08-14 20:53:53'),(39,2,34,'2026-08-14 20:53:53'),(40,2,42,'2026-08-14 20:53:53'),(41,2,41,'2026-08-14 20:53:53'),(42,2,17,'2026-08-14 20:53:53'),(43,2,16,'2026-08-14 20:53:53'),(44,2,43,'2026-08-14 20:53:53'),(64,1,44,'2026-08-14 20:53:53'),(65,1,39,'2026-08-14 20:53:53'),(66,1,27,'2026-08-14 20:53:53'),(67,1,26,'2026-08-14 20:53:53'),(68,1,1,'2026-08-14 20:53:53'),(69,1,40,'2026-08-14 20:53:53'),(70,1,36,'2026-08-14 20:53:53'),(71,1,35,'2026-08-14 20:53:53'),(72,1,38,'2026-08-14 20:53:53'),(73,1,37,'2026-08-14 20:53:53'),(74,1,29,'2026-08-14 20:53:53'),(75,1,30,'2026-08-14 20:53:53'),(76,1,31,'2026-08-14 20:53:53'),(77,1,28,'2026-08-14 20:53:53'),(78,1,33,'2026-08-14 20:53:53'),(79,1,32,'2026-08-14 20:53:53'),(80,1,23,'2026-08-14 20:53:53'),(81,1,22,'2026-08-14 20:53:53'),(82,1,21,'2026-08-14 20:53:53'),(83,1,20,'2026-08-14 20:53:53'),(84,1,19,'2026-08-14 20:53:53'),(85,1,18,'2026-08-14 20:53:53'),(86,1,15,'2026-08-14 20:53:53'),(87,1,14,'2026-08-14 20:53:53'),(88,1,11,'2026-08-14 20:53:53'),(89,1,9,'2026-08-14 20:53:53'),(90,1,10,'2026-08-14 20:53:53'),(91,1,8,'2026-08-14 20:53:53'),(92,1,13,'2026-08-14 20:53:53'),(93,1,12,'2026-08-14 20:53:53'),(94,1,3,'2026-08-14 20:53:53'),(95,1,5,'2026-08-14 20:53:53'),(96,1,6,'2026-08-14 20:53:53'),(97,1,4,'2026-08-14 20:53:53'),(98,1,7,'2026-08-14 20:53:53'),(99,1,2,'2026-08-14 20:53:53'),(100,1,25,'2026-08-14 20:53:53'),(101,1,24,'2026-08-14 20:53:53'),(102,1,34,'2026-08-14 20:53:53'),(103,1,42,'2026-08-14 20:53:53'),(104,1,41,'2026-08-14 20:53:53'),(105,1,17,'2026-08-14 20:53:53'),(106,1,16,'2026-08-14 20:53:53'),(107,1,43,'2026-08-14 20:53:53'),(127,3,44,'2026-08-14 20:53:53'),(128,3,39,'2026-08-14 20:53:53'),(129,3,27,'2026-08-14 20:53:53'),(130,3,26,'2026-08-14 20:53:53'),(131,3,1,'2026-08-14 20:53:53'),(132,3,40,'2026-08-14 20:53:53'),(133,3,36,'2026-08-14 20:53:53'),(134,3,35,'2026-08-14 20:53:53'),(135,3,38,'2026-08-14 20:53:53'),(136,3,37,'2026-08-14 20:53:53'),(137,3,29,'2026-08-14 20:53:53'),(138,3,30,'2026-08-14 20:53:53'),(139,3,31,'2026-08-14 20:53:53'),(140,3,28,'2026-08-14 20:53:53'),(141,3,33,'2026-08-14 20:53:53'),(142,3,32,'2026-08-14 20:53:53'),(143,3,23,'2026-08-14 20:53:53'),(144,3,22,'2026-08-14 20:53:53'),(145,3,21,'2026-08-14 20:53:53'),(146,3,20,'2026-08-14 20:53:53'),(147,3,19,'2026-08-14 20:53:53'),(148,3,18,'2026-08-14 20:53:53'),(149,3,15,'2026-08-14 20:53:53'),(150,3,14,'2026-08-14 20:53:53'),(151,3,11,'2026-08-14 20:53:53'),(152,3,9,'2026-08-14 20:53:53'),(153,3,10,'2026-08-14 20:53:53'),(154,3,8,'2026-08-14 20:53:53'),(155,3,13,'2026-08-14 20:53:53'),(156,3,12,'2026-08-14 20:53:53'),(157,3,3,'2026-08-14 20:53:53'),(158,3,5,'2026-08-14 20:53:53'),(159,3,6,'2026-08-14 20:53:53'),(160,3,4,'2026-08-14 20:53:53'),(161,3,7,'2026-08-14 20:53:53'),(162,3,2,'2026-08-14 20:53:53'),(163,3,25,'2026-08-14 20:53:53'),(164,3,24,'2026-08-14 20:53:53'),(165,3,34,'2026-08-14 20:53:53'),(166,3,42,'2026-08-14 20:53:53'),(167,3,41,'2026-08-14 20:53:53'),(168,3,17,'2026-08-14 20:53:53'),(169,3,16,'2026-08-14 20:53:53'),(190,4,39,'2026-08-14 20:53:53'),(191,4,27,'2026-08-14 20:53:53'),(192,4,26,'2026-08-14 20:53:53'),(193,4,1,'2026-08-14 20:53:53'),(194,4,18,'2026-08-14 20:53:53'),(195,4,9,'2026-08-14 20:53:53'),(196,4,8,'2026-08-14 20:53:53'),(197,4,13,'2026-08-14 20:53:53'),(198,4,12,'2026-08-14 20:53:53'),(199,4,3,'2026-08-14 20:53:53'),(200,4,6,'2026-08-14 20:53:53'),(201,4,4,'2026-08-14 20:53:53'),(202,4,2,'2026-08-14 20:53:53'),(203,4,16,'2026-08-14 20:53:53'),(205,5,26,'2026-08-14 20:53:53'),(206,5,1,'2026-08-14 20:53:53'),(207,5,18,'2026-08-14 20:53:53'),(208,5,8,'2026-08-14 20:53:53'),(209,5,3,'2026-08-14 20:53:53'),(210,5,2,'2026-08-14 20:53:53'),(211,5,16,'2026-08-14 20:53:53'),(212,6,26,'2026-08-14 20:53:53'),(213,6,1,'2026-08-14 20:53:53'),(214,6,18,'2026-08-14 20:53:53'),(215,6,10,'2026-08-14 20:53:53'),(216,6,8,'2026-08-14 20:53:53'),(217,6,3,'2026-08-14 20:53:53'),(218,6,6,'2026-08-14 20:53:53'),(219,6,4,'2026-08-14 20:53:53'),(220,6,2,'2026-08-14 20:53:53'),(221,6,17,'2026-08-14 20:53:53'),(222,6,16,'2026-08-14 20:53:53'),(227,7,1,'2026-08-14 20:53:53'),(228,7,20,'2026-08-14 20:53:53'),(229,7,19,'2026-08-14 20:53:53'),(230,7,18,'2026-08-14 20:53:53'),(234,8,1,'2026-08-14 20:53:53'),(235,8,23,'2026-08-14 20:53:53'),(236,8,22,'2026-08-14 20:53:53'),(237,8,21,'2026-08-14 20:53:53'),(238,8,34,'2026-08-14 20:53:53'),(241,9,1,'2026-08-14 20:53:53'),(242,9,21,'2026-08-14 20:53:53'),(243,9,25,'2026-08-14 20:53:53'),(244,9,24,'2026-08-14 20:53:53'),(245,9,34,'2026-08-14 20:53:53'),(248,10,27,'2026-08-14 20:53:53'),(249,10,26,'2026-08-14 20:53:53'),(250,10,1,'2026-08-14 20:53:53'),(251,10,29,'2026-08-14 20:53:53'),(252,10,30,'2026-08-14 20:53:53'),(253,10,31,'2026-08-14 20:53:53'),(254,10,28,'2026-08-14 20:53:53'),(255,10,33,'2026-08-14 20:53:53'),(256,10,32,'2026-08-14 20:53:53'),(257,10,13,'2026-08-14 20:53:53'),(258,10,12,'2026-08-14 20:53:53'),(263,11,44,'2026-08-14 20:53:53'),(264,11,1,'2026-08-14 20:53:53'),(265,11,40,'2026-08-14 20:53:53'),(266,11,37,'2026-08-14 20:53:53'),(267,11,12,'2026-08-14 20:53:53'),(268,11,34,'2026-08-14 20:53:53'),(270,12,44,'2026-08-14 20:53:53'),(271,12,1,'2026-08-14 20:53:53'),(272,12,34,'2026-08-14 20:53:53'),(273,13,1,'2026-08-23 08:10:37'),(274,13,2,'2026-08-23 08:10:37'),(275,13,3,'2026-08-23 08:10:37'),(276,13,4,'2026-08-23 08:10:37'),(277,13,5,'2026-08-23 08:10:37'),(278,13,6,'2026-08-23 08:10:37'),(279,13,7,'2026-08-23 08:10:37'),(280,13,8,'2026-08-23 08:10:37'),(281,13,9,'2026-08-23 08:10:37'),(282,13,10,'2026-08-23 08:10:37'),(283,13,11,'2026-08-23 08:10:37'),(284,13,12,'2026-08-23 08:10:37'),(285,13,13,'2026-08-23 08:10:37'),(286,13,14,'2026-08-23 08:10:37'),(287,13,15,'2026-08-23 08:10:37'),(288,13,16,'2026-08-23 08:10:37'),(289,13,17,'2026-08-23 08:10:37'),(290,13,18,'2026-08-23 08:10:37'),(291,13,19,'2026-08-23 08:10:37'),(292,13,20,'2026-08-23 08:10:37'),(293,13,21,'2026-08-23 08:10:37'),(294,13,22,'2026-08-23 08:10:37'),(295,13,23,'2026-08-23 08:10:37'),(296,13,24,'2026-08-23 08:10:37'),(297,13,25,'2026-08-23 08:10:37'),(298,13,26,'2026-08-23 08:10:37'),(299,13,27,'2026-08-23 08:10:37'),(300,13,28,'2026-08-23 08:10:37'),(301,13,29,'2026-08-23 08:10:37'),(302,13,30,'2026-08-23 08:10:37'),(303,13,31,'2026-08-23 08:10:37'),(304,13,32,'2026-08-23 08:10:37'),(305,13,33,'2026-08-23 08:10:37'),(306,13,34,'2026-08-23 08:10:37'),(307,13,35,'2026-08-23 08:10:37'),(308,13,36,'2026-08-23 08:10:37'),(309,13,37,'2026-08-23 08:10:37'),(310,13,38,'2026-08-23 08:10:37'),(311,13,39,'2026-08-23 08:10:37'),(312,13,40,'2026-08-23 08:10:37'),(313,13,41,'2026-08-23 08:10:37'),(314,13,42,'2026-08-23 08:10:37'),(315,13,43,'2026-08-23 08:10:37'),(316,13,44,'2026-08-23 08:10:37'),(317,14,1,'2026-08-23 08:10:38'),(318,14,2,'2026-08-23 08:10:38'),(319,14,3,'2026-08-23 08:10:38'),(320,14,4,'2026-08-23 08:10:38'),(321,14,5,'2026-08-23 08:10:38'),(322,14,6,'2026-08-23 08:10:38'),(323,14,7,'2026-08-23 08:10:38'),(324,14,8,'2026-08-23 08:10:38'),(325,14,9,'2026-08-23 08:10:38'),(326,14,10,'2026-08-23 08:10:38'),(327,14,11,'2026-08-23 08:10:38'),(328,14,12,'2026-08-23 08:10:38'),(329,14,13,'2026-08-23 08:10:38'),(330,14,14,'2026-08-23 08:10:38'),(331,14,15,'2026-08-23 08:10:38'),(332,14,16,'2026-08-23 08:10:38'),(333,14,17,'2026-08-23 08:10:38'),(334,14,18,'2026-08-23 08:10:38'),(335,14,19,'2026-08-23 08:10:38'),(336,14,20,'2026-08-23 08:10:38'),(337,14,21,'2026-08-23 08:10:38'),(338,14,22,'2026-08-23 08:10:38'),(339,14,23,'2026-08-23 08:10:38'),(340,14,24,'2026-08-23 08:10:38'),(341,14,25,'2026-08-23 08:10:38'),(342,14,26,'2026-08-23 08:10:38'),(343,14,27,'2026-08-23 08:10:38'),(344,14,28,'2026-08-23 08:10:38'),(345,14,29,'2026-08-23 08:10:38'),(346,14,30,'2026-08-23 08:10:38'),(347,14,31,'2026-08-23 08:10:38'),(348,14,32,'2026-08-23 08:10:38'),(349,14,33,'2026-08-23 08:10:38'),(350,14,34,'2026-08-23 08:10:38'),(351,14,35,'2026-08-23 08:10:38'),(352,14,36,'2026-08-23 08:10:38'),(353,14,37,'2026-08-23 08:10:38'),(354,14,38,'2026-08-23 08:10:38'),(355,14,39,'2026-08-23 08:10:38'),(356,14,40,'2026-08-23 08:10:38'),(357,14,41,'2026-08-23 08:10:38'),(358,14,42,'2026-08-23 08:10:38'),(359,14,43,'2026-08-23 08:10:38'),(360,14,44,'2026-08-23 08:10:38'),(361,15,1,'2026-08-23 08:10:38'),(362,15,2,'2026-08-23 08:10:38'),(363,15,3,'2026-08-23 08:10:38'),(364,15,4,'2026-08-23 08:10:38'),(365,15,5,'2026-08-23 08:10:38'),(366,15,6,'2026-08-23 08:10:38'),(367,15,7,'2026-08-23 08:10:38'),(368,15,8,'2026-08-23 08:10:38'),(369,15,9,'2026-08-23 08:10:38'),(370,15,10,'2026-08-23 08:10:38'),(371,15,11,'2026-08-23 08:10:38'),(372,15,12,'2026-08-23 08:10:38'),(373,15,13,'2026-08-23 08:10:38'),(374,15,14,'2026-08-23 08:10:38'),(375,15,15,'2026-08-23 08:10:38'),(376,15,16,'2026-08-23 08:10:38'),(377,15,17,'2026-08-23 08:10:38'),(378,15,18,'2026-08-23 08:10:38'),(379,15,19,'2026-08-23 08:10:38'),(380,15,20,'2026-08-23 08:10:38'),(381,15,21,'2026-08-23 08:10:38'),(382,15,22,'2026-08-23 08:10:38'),(383,15,23,'2026-08-23 08:10:38'),(384,15,24,'2026-08-23 08:10:38'),(385,15,25,'2026-08-23 08:10:38'),(386,15,26,'2026-08-23 08:10:38'),(387,15,27,'2026-08-23 08:10:38'),(388,15,28,'2026-08-23 08:10:38'),(389,15,29,'2026-08-23 08:10:38'),(390,15,30,'2026-08-23 08:10:38'),(391,15,31,'2026-08-23 08:10:38'),(392,15,32,'2026-08-23 08:10:38'),(393,15,33,'2026-08-23 08:10:38'),(394,15,34,'2026-08-23 08:10:38'),(395,15,35,'2026-08-23 08:10:38'),(396,15,36,'2026-08-23 08:10:38'),(397,15,37,'2026-08-23 08:10:38'),(398,15,38,'2026-08-23 08:10:38'),(399,15,39,'2026-08-23 08:10:38'),(400,15,40,'2026-08-23 08:10:38'),(401,15,41,'2026-08-23 08:10:38'),(402,15,42,'2026-08-23 08:10:38'),(403,15,43,'2026-08-23 08:10:38'),(404,15,44,'2026-08-23 08:10:38'),(405,16,1,'2026-08-23 08:10:38'),(406,16,2,'2026-08-23 08:10:38'),(407,16,3,'2026-08-23 08:10:38'),(408,16,4,'2026-08-23 08:10:38'),(409,16,5,'2026-08-23 08:10:38'),(410,16,6,'2026-08-23 08:10:38'),(411,16,7,'2026-08-23 08:10:38'),(412,16,8,'2026-08-23 08:10:38'),(413,16,9,'2026-08-23 08:10:38'),(414,16,10,'2026-08-23 08:10:38'),(415,16,11,'2026-08-23 08:10:38'),(416,16,12,'2026-08-23 08:10:38'),(417,16,13,'2026-08-23 08:10:38'),(418,16,14,'2026-08-23 08:10:38'),(419,16,15,'2026-08-23 08:10:38'),(420,16,16,'2026-08-23 08:10:38'),(421,16,17,'2026-08-23 08:10:38'),(422,16,18,'2026-08-23 08:10:38'),(423,16,19,'2026-08-23 08:10:38'),(424,16,20,'2026-08-23 08:10:38'),(425,16,21,'2026-08-23 08:10:38'),(426,16,22,'2026-08-23 08:10:38'),(427,16,23,'2026-08-23 08:10:38'),(428,16,24,'2026-08-23 08:10:38'),(429,16,25,'2026-08-23 08:10:38'),(430,16,26,'2026-08-23 08:10:38'),(431,16,27,'2026-08-23 08:10:38'),(432,16,28,'2026-08-23 08:10:38'),(433,16,29,'2026-08-23 08:10:38'),(434,16,30,'2026-08-23 08:10:38'),(435,16,31,'2026-08-23 08:10:38'),(436,16,32,'2026-08-23 08:10:38'),(437,16,33,'2026-08-23 08:10:38'),(438,16,34,'2026-08-23 08:10:38'),(439,16,35,'2026-08-23 08:10:38'),(440,16,36,'2026-08-23 08:10:38'),(441,16,37,'2026-08-23 08:10:38'),(442,16,38,'2026-08-23 08:10:38'),(443,16,39,'2026-08-23 08:10:38'),(444,16,40,'2026-08-23 08:10:38'),(445,16,41,'2026-08-23 08:10:38'),(446,16,42,'2026-08-23 08:10:38'),(447,16,43,'2026-08-23 08:10:38'),(448,16,44,'2026-08-23 08:10:38'),(449,17,1,'2026-08-23 08:10:38'),(450,17,2,'2026-08-23 08:10:38'),(451,17,3,'2026-08-23 08:10:38'),(452,17,4,'2026-08-23 08:10:38'),(453,17,5,'2026-08-23 08:10:38'),(454,17,6,'2026-08-23 08:10:38'),(455,17,7,'2026-08-23 08:10:38'),(456,17,8,'2026-08-23 08:10:38'),(457,17,9,'2026-08-23 08:10:38'),(458,17,10,'2026-08-23 08:10:38'),(459,17,11,'2026-08-23 08:10:38'),(460,17,12,'2026-08-23 08:10:38'),(461,17,13,'2026-08-23 08:10:38'),(462,17,14,'2026-08-23 08:10:38'),(463,17,15,'2026-08-23 08:10:38'),(464,17,16,'2026-08-23 08:10:38'),(465,17,17,'2026-08-23 08:10:38'),(466,17,18,'2026-08-23 08:10:38'),(467,17,19,'2026-08-23 08:10:38'),(468,17,20,'2026-08-23 08:10:38'),(469,17,21,'2026-08-23 08:10:38'),(470,17,22,'2026-08-23 08:10:38'),(471,17,23,'2026-08-23 08:10:38'),(472,17,24,'2026-08-23 08:10:38'),(473,17,25,'2026-08-23 08:10:38'),(474,17,26,'2026-08-23 08:10:38'),(475,17,27,'2026-08-23 08:10:38'),(476,17,28,'2026-08-23 08:10:38'),(477,17,29,'2026-08-23 08:10:38'),(478,17,30,'2026-08-23 08:10:38'),(479,17,31,'2026-08-23 08:10:38'),(480,17,32,'2026-08-23 08:10:38'),(481,17,33,'2026-08-23 08:10:38'),(482,17,34,'2026-08-23 08:10:38'),(483,17,35,'2026-08-23 08:10:38'),(484,17,36,'2026-08-23 08:10:38'),(485,17,37,'2026-08-23 08:10:38'),(486,17,38,'2026-08-23 08:10:38'),(487,17,39,'2026-08-23 08:10:38'),(488,17,40,'2026-08-23 08:10:38'),(489,17,41,'2026-08-23 08:10:38'),(490,17,42,'2026-08-23 08:10:38'),(491,17,43,'2026-08-23 08:10:38'),(492,17,44,'2026-08-23 08:10:38'),(493,2,46,'2026-08-24 12:09:23'),(494,1,46,'2026-08-24 12:09:23'),(495,3,46,'2026-08-24 12:09:23'),(496,13,46,'2026-08-24 12:09:23'),(497,14,46,'2026-08-24 12:09:23'),(498,15,46,'2026-08-24 12:09:23'),(499,16,46,'2026-08-24 12:09:23'),(500,17,46,'2026-08-24 12:09:23'),(501,2,45,'2026-08-24 12:09:23'),(502,1,45,'2026-08-24 12:09:23'),(503,3,45,'2026-08-24 12:09:23'),(504,13,45,'2026-08-24 12:09:23'),(505,14,45,'2026-08-24 12:09:23'),(506,15,45,'2026-08-24 12:09:23'),(507,16,45,'2026-08-24 12:09:23'),(508,17,45,'2026-08-24 12:09:23'),(524,8,45,'2026-08-24 12:09:23'),(525,9,45,'2026-08-24 12:09:23'),(526,11,45,'2026-08-24 12:09:23'),(527,12,45,'2026-08-24 12:09:23');

--
-- Table structure for table `roles`
--

CREATE TABLE `roles` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(10) unsigned DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `slug` varchar(100) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `is_system` tinyint(1) NOT NULL DEFAULT 0,
  `max_discount_percent` decimal(5,2) NOT NULL DEFAULT 0.00,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_role_company_slug` (`company_id`,`slug`),
  CONSTRAINT `fk_role_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `roles`
--

INSERT INTO `roles` VALUES (1,NULL,'Super Admin','super-admin','Full system access across all companies',1,100.00,'active','2026-08-14 20:53:53','2026-08-14 20:53:53'),(2,1,'Company Admin','company-admin','Full access within the company',1,100.00,'active','2026-08-14 20:53:53','2026-08-14 20:53:53'),(3,1,'Outlet Manager','outlet-manager','Manages a single outlet',1,30.00,'active','2026-08-14 20:53:53','2026-08-14 20:53:53'),(4,1,'Cashier','cashier','POS billing and payments',1,10.00,'active','2026-08-14 20:53:53','2026-08-14 20:53:53'),(5,1,'Waiter','waiter','Takes orders and serves tables',1,0.00,'active','2026-08-14 20:53:53','2026-08-14 20:53:53'),(6,1,'Captain','captain','Supervises waiters and table service',1,5.00,'active','2026-08-14 20:53:53','2026-08-14 20:53:53'),(7,1,'Kitchen','kitchen','Kitchen display and KOT handling',1,0.00,'active','2026-08-14 20:53:53','2026-08-14 20:53:53'),(8,1,'Inventory Manager','inventory-manager','Manages stock and recipes',1,0.00,'active','2026-08-14 20:53:53','2026-08-14 20:53:53'),(9,1,'Purchase Manager','purchase-manager','Manages suppliers and purchases',1,0.00,'active','2026-08-14 20:53:53','2026-08-14 20:53:53'),(10,1,'Receptionist','receptionist','Hotel front desk operations',1,5.00,'active','2026-08-14 20:53:53','2026-08-14 20:53:53'),(11,1,'Accountant','accountant','Financial reports and reconciliation',1,0.00,'active','2026-08-14 20:53:53','2026-08-14 20:53:53'),(12,1,'Auditor','auditor','Read-only audit access',1,0.00,'active','2026-08-14 20:53:53','2026-08-14 20:53:53'),(13,2,'Owner','owner-2','Full access within this business',0,100.00,'active','2026-08-23 08:10:37','2026-08-23 08:10:37'),(14,3,'Owner','owner-3','Full access within this business',0,100.00,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(15,4,'Owner','owner-4','Full access within this business',0,100.00,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(16,5,'Owner','owner-5','Full access within this business',0,100.00,'active','2026-08-23 08:10:38','2026-08-23 08:10:38'),(17,6,'Owner','owner-6','Full access within this business',0,100.00,'active','2026-08-23 08:10:38','2026-08-23 08:10:38');

--
-- Table structure for table `social_integrations`
--

CREATE TABLE `social_integrations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(10) unsigned NOT NULL,
  `provider` varchar(50) NOT NULL,
  `config` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`config`)),
  `status` enum('active','inactive') NOT NULL DEFAULT 'inactive',
  PRIMARY KEY (`id`),
  KEY `fk_si_company` (`company_id`),
  CONSTRAINT `fk_si_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `social_integrations`
--


--
-- Table structure for table `stock_adjustments`
--

CREATE TABLE `stock_adjustments` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `outlet_id` int(10) unsigned NOT NULL,
  `inventory_item_id` int(10) unsigned NOT NULL,
  `quantity` decimal(14,3) NOT NULL,
  `reason` varchar(255) NOT NULL,
  `adjusted_by` int(10) unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_sa_outlet` (`outlet_id`),
  KEY `fk_sa_item` (`inventory_item_id`),
  CONSTRAINT `fk_sa_item` FOREIGN KEY (`inventory_item_id`) REFERENCES `inventory_items` (`id`),
  CONSTRAINT `fk_sa_outlet` FOREIGN KEY (`outlet_id`) REFERENCES `outlets` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `stock_adjustments`
--


--
-- Table structure for table `stock_transfer_items`
--

CREATE TABLE `stock_transfer_items` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `stock_transfer_id` int(10) unsigned NOT NULL,
  `inventory_item_id` int(10) unsigned NOT NULL,
  `quantity` decimal(14,3) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_sti_transfer` (`stock_transfer_id`),
  KEY `fk_sti_item` (`inventory_item_id`),
  CONSTRAINT `fk_sti_item` FOREIGN KEY (`inventory_item_id`) REFERENCES `inventory_items` (`id`),
  CONSTRAINT `fk_sti_transfer` FOREIGN KEY (`stock_transfer_id`) REFERENCES `stock_transfers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `stock_transfer_items`
--


--
-- Table structure for table `stock_transfers`
--

CREATE TABLE `stock_transfers` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(10) unsigned NOT NULL,
  `transfer_number` varchar(50) NOT NULL,
  `from_outlet_id` int(10) unsigned NOT NULL,
  `to_outlet_id` int(10) unsigned NOT NULL,
  `status` enum('draft','requested','approved','dispatched','received','cancelled') NOT NULL DEFAULT 'draft',
  `requested_by` int(10) unsigned DEFAULT NULL,
  `approved_by` int(10) unsigned DEFAULT NULL,
  `dispatched_at` datetime DEFAULT NULL,
  `received_at` datetime DEFAULT NULL,
  `notes` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_transfer_company_number` (`company_id`,`transfer_number`),
  KEY `fk_st_from` (`from_outlet_id`),
  KEY `fk_st_to` (`to_outlet_id`),
  CONSTRAINT `fk_st_from` FOREIGN KEY (`from_outlet_id`) REFERENCES `outlets` (`id`),
  CONSTRAINT `fk_st_to` FOREIGN KEY (`to_outlet_id`) REFERENCES `outlets` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `stock_transfers`
--


--
-- Table structure for table `supplier_contacts`
--

CREATE TABLE `supplier_contacts` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `supplier_id` int(10) unsigned NOT NULL,
  `name` varchar(150) NOT NULL,
  `designation` varchar(100) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `email` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_sc_supplier` (`supplier_id`),
  CONSTRAINT `fk_sc_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `supplier_contacts`
--


--
-- Table structure for table `suppliers`
--

CREATE TABLE `suppliers` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(10) unsigned NOT NULL,
  `name` varchar(150) NOT NULL,
  `company_name` varchar(150) DEFAULT NULL,
  `gstin` varchar(20) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `email` varchar(150) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `payment_terms` varchar(100) DEFAULT NULL,
  `bank_details` varchar(255) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_supplier_company` (`company_id`),
  CONSTRAINT `fk_supplier_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `suppliers`
--

INSERT INTO `suppliers` VALUES (1,1,'Fresh Farms Distributors','Fresh Farms Pvt Ltd','29FRESH1234F1Z1','+91-9988776655','sales@freshfarms.example','Whitefield, Bengaluru','Net 15',NULL,NULL,'active','2026-08-14 20:53:53','2026-08-14 20:53:53');

--
-- Table structure for table `table_status_history`
--

CREATE TABLE `table_status_history` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `table_id` int(10) unsigned NOT NULL,
  `from_status` varchar(20) DEFAULT NULL,
  `to_status` varchar(20) NOT NULL,
  `changed_by` int(10) unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_tsh_table` (`table_id`),
  CONSTRAINT `fk_tsh_table` FOREIGN KEY (`table_id`) REFERENCES `tables` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `table_status_history`
--


--
-- Table structure for table `tables`
--

CREATE TABLE `tables` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `outlet_id` int(10) unsigned NOT NULL,
  `floor_id` int(10) unsigned NOT NULL,
  `name` varchar(50) NOT NULL,
  `capacity` int(10) unsigned NOT NULL DEFAULT 2,
  `shape` enum('square','round','rectangle') NOT NULL DEFAULT 'square',
  `pos_x` int(11) NOT NULL DEFAULT 0,
  `pos_y` int(11) NOT NULL DEFAULT 0,
  `status` enum('available','occupied','reserved','cleaning','blocked') NOT NULL DEFAULT 'available',
  `current_order_id` bigint(20) unsigned DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_table_outlet` (`outlet_id`),
  KEY `fk_table_floor` (`floor_id`),
  KEY `fk_table_current_order` (`current_order_id`),
  CONSTRAINT `fk_table_current_order` FOREIGN KEY (`current_order_id`) REFERENCES `orders` (`id`),
  CONSTRAINT `fk_table_floor` FOREIGN KEY (`floor_id`) REFERENCES `floors` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_table_outlet` FOREIGN KEY (`outlet_id`) REFERENCES `outlets` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `tables`
--

INSERT INTO `tables` VALUES (1,1,1,'T1',4,'square',0,0,'available',NULL,'2026-08-14 20:53:53','2026-08-14 20:53:53'),(2,1,1,'T2',4,'square',0,0,'available',NULL,'2026-08-14 20:53:53','2026-08-14 20:53:53'),(3,1,1,'T3',6,'rectangle',0,0,'available',NULL,'2026-08-14 20:53:53','2026-08-14 20:53:53'),(4,1,1,'T4',2,'round',0,0,'available',NULL,'2026-08-14 20:53:53','2026-08-14 20:53:53'),(5,1,2,'RT1',4,'square',0,0,'available',NULL,'2026-08-14 20:53:53','2026-08-24 07:11:25'),(6,1,2,'RT2',6,'rectangle',0,0,'available',NULL,'2026-08-14 20:53:53','2026-08-14 20:53:53');

--
-- Table structure for table `taxes`
--

CREATE TABLE `taxes` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int(10) unsigned NOT NULL,
  `name` varchar(100) NOT NULL,
  `rate` decimal(5,2) NOT NULL DEFAULT 0.00,
  `type` enum('CGST','SGST','IGST','CESS','OTHER') NOT NULL DEFAULT 'OTHER',
  `is_inclusive` tinyint(1) NOT NULL DEFAULT 0,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_tax_company` (`company_id`),
  CONSTRAINT `fk_tax_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `taxes`
--

INSERT INTO `taxes` VALUES (1,1,'CGST 2.5%',2.50,'CGST',0,'active','2026-08-14 20:53:53'),(2,1,'SGST 2.5%',2.50,'SGST',0,'active','2026-08-14 20:53:53'),(3,1,'CGST 6%',6.00,'CGST',0,'active','2026-08-14 20:53:53'),(4,1,'SGST 6%',6.00,'SGST',0,'active','2026-08-14 20:53:53'),(5,1,'Room GST 12%',12.00,'OTHER',1,'active','2026-08-14 20:53:53');

--
-- Table structure for table `user_outlets`
--

CREATE TABLE `user_outlets` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `outlet_id` int(10) unsigned NOT NULL,
  `is_default` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_user_outlet` (`user_id`,`outlet_id`),
  KEY `fk_uo_outlet` (`outlet_id`),
  CONSTRAINT `fk_uo_outlet` FOREIGN KEY (`outlet_id`) REFERENCES `outlets` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_uo_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `user_outlets`
--

INSERT INTO `user_outlets` VALUES (1,1,1,1,'2026-08-14 20:53:53'),(2,2,1,1,'2026-08-14 20:53:53'),(3,3,1,1,'2026-08-14 20:53:53'),(4,4,1,1,'2026-08-14 20:53:53'),(5,5,1,1,'2026-08-14 20:53:53'),(6,6,1,1,'2026-08-14 20:53:53'),(7,7,2,1,'2026-08-23 08:10:37'),(8,7,3,0,'2026-08-23 08:10:37'),(9,8,4,1,'2026-08-23 08:10:38'),(10,9,5,1,'2026-08-23 08:10:38'),(11,9,6,0,'2026-08-23 08:10:38'),(12,10,7,1,'2026-08-23 08:10:38'),(13,11,8,1,'2026-08-23 08:10:38'),(14,12,2,1,'2026-08-24 06:18:23');

--
-- Table structure for table `wastage`
--

CREATE TABLE `wastage` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `outlet_id` int(10) unsigned NOT NULL,
  `inventory_item_id` int(10) unsigned NOT NULL,
  `quantity` decimal(14,3) NOT NULL,
  `reason` enum('expired','damaged','overproduction','spillage','wrong_preparation','other') NOT NULL DEFAULT 'other',
  `cost` decimal(12,2) NOT NULL DEFAULT 0.00,
  `employee_id` int(10) unsigned DEFAULT NULL,
  `wastage_date` date NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_w_outlet` (`outlet_id`),
  KEY `fk_w_item` (`inventory_item_id`),
  CONSTRAINT `fk_w_item` FOREIGN KEY (`inventory_item_id`) REFERENCES `inventory_items` (`id`),
  CONSTRAINT `fk_w_outlet` FOREIGN KEY (`outlet_id`) REFERENCES `outlets` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `wastage`
--


--
-- Dumping routines for database 'petzy_pos'
--


