-- Database Backup for Hotel 1
-- Generated: 2026-05-25 15:49:21

DROP TABLE IF EXISTS `account_balances`;
CREATE TABLE `account_balances` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `account_id` bigint(20) unsigned NOT NULL,
  `fiscal_year` year(4) NOT NULL,
  `period` enum('monthly','quarterly','yearly','cumulative') NOT NULL,
  `period_date` date NOT NULL COMMENT 'Date representing the period (e.g., first day of month)',
  `balance` decimal(15,2) NOT NULL DEFAULT 0.00,
  `debit_sum` decimal(15,2) NOT NULL DEFAULT 0.00,
  `credit_sum` decimal(15,2) NOT NULL DEFAULT 0.00,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `acc_bal_hotel_account_period_date_unique` (`hotel_id`,`account_id`,`period_date`,`period`),
  KEY `account_balances_account_id_foreign` (`account_id`),
  KEY `account_balances_period_date_index` (`period_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `account_tag`;
CREATE TABLE `account_tag` (
  `account_id` bigint(20) unsigned NOT NULL,
  `tag_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`account_id`,`tag_id`),
  KEY `account_tag_tag_id_foreign` (`tag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `accountabilities`;
CREATE TABLE `accountabilities` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned DEFAULT NULL,
  `date` date NOT NULL,
  `staff_id` bigint(20) unsigned NOT NULL,
  `account_id` bigint(20) unsigned NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `description` varchar(255) NOT NULL,
  `document_number` varchar(100) DEFAULT NULL,
  `status` enum('pending','cleared','cancelled') NOT NULL DEFAULT 'pending',
  `cleared_by` bigint(20) unsigned DEFAULT NULL,
  `cleared_at` timestamp NULL DEFAULT NULL,
  `created_by` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `accountabilities_staff_id_foreign` (`staff_id`),
  KEY `accountabilities_account_id_foreign` (`account_id`),
  KEY `accountabilities_cleared_by_foreign` (`cleared_by`),
  KEY `accountabilities_created_by_foreign` (`created_by`),
  KEY `accountabilities_hotel_id_index` (`hotel_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `accounting_transactions`;
CREATE TABLE `accounting_transactions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `database_type` varchar(255) NOT NULL,
  `document_reference` varchar(255) NOT NULL,
  `bank_transaction_ref` varchar(255) DEFAULT NULL,
  `particulars` varchar(255) NOT NULL,
  `report_type_1` varchar(255) NOT NULL,
  `report_type_2` varchar(255) NOT NULL,
  `account_name` varchar(255) NOT NULL,
  `account_no` varchar(255) NOT NULL,
  `sign` enum('Dr','Cr') NOT NULL,
  `amount_local` decimal(15,2) NOT NULL,
  `amount_reporting` decimal(15,2) NOT NULL,
  `cost_centre_1` varchar(255) NOT NULL,
  `cost_centre_2` varchar(255) NOT NULL,
  `reconciliation_pool` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_document_reference` (`document_reference`),
  KEY `idx_account_no` (`account_no`),
  KEY `idx_bank_transaction_ref` (`bank_transaction_ref`),
  KEY `idx_database_type` (`database_type`),
  KEY `idx_sign` (`sign`),
  KEY `idx_reconciliation_pool` (`reconciliation_pool`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `accounts`;
CREATE TABLE `accounts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned DEFAULT NULL,
  `code` varchar(20) NOT NULL COMMENT 'Account code from Excel (e.g., 21111, 22111)',
  `name` varchar(255) NOT NULL COMMENT 'Account Name from Excel (e.g., Guests-Walk In)',
  `l1_statement` varchar(50) DEFAULT NULL COMMENT 'e.g., 20000 - Profit & Loss',
  `l2_department` varchar(50) DEFAULT NULL COMMENT 'e.g., 21000 - Revenue',
  `l3_group` varchar(50) DEFAULT NULL COMMENT 'e.g., 21100 - Room Sales',
  `l4_sub_group` varchar(50) DEFAULT NULL COMMENT 'e.g., 21110 - Nightly Room Charges',
  `account_type` enum('H','P') NOT NULL DEFAULT 'P' COMMENT 'H = Header (cannot post), P = Posting',
  `normal_balance` enum('Dr','Cr') DEFAULT NULL COMMENT 'Debit or Credit normal balance',
  `category` varchar(100) DEFAULT NULL COMMENT 'Category from Account Selector (e.g., Revenue - Room Sales)',
  `pl_line` varchar(100) DEFAULT NULL COMMENT 'P&L Line from Excel (e.g., Room Sales)',
  `report_section` varchar(50) DEFAULT NULL COMMENT 'e.g., REVENUE_DEPT, COGS_DEPT, OPEX, NON_OP',
  `parent_id` bigint(20) unsigned DEFAULT NULL COMMENT 'Self-reference for hierarchy (e.g., 21110 parent is 21100)',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `balance` decimal(15,2) NOT NULL DEFAULT 0.00,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `accounts_hotel_id_code_unique` (`hotel_id`,`code`),
  KEY `accounts_hotel_id_index` (`hotel_id`),
  KEY `accounts_parent_id_foreign` (`parent_id`),
  KEY `accounts_code_index` (`code`),
  KEY `accounts_pl_line_index` (`pl_line`),
  KEY `accounts_account_type_index` (`account_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `admin_activities`;
CREATE TABLE `admin_activities` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `admin_id` bigint(20) unsigned NOT NULL,
  `action` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `hotel_id` bigint(20) unsigned DEFAULT NULL COMMENT 'If action relates to a specific hotel',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `admin_activities_admin_id_foreign` (`admin_id`),
  KEY `admin_activities_hotel_id_foreign` (`hotel_id`),
  KEY `admin_activities_action_index` (`action`),
  KEY `admin_activities_created_at_index` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `admin_alerts`;
CREATE TABLE `admin_alerts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `message` text NOT NULL,
  `type` enum('info','warning','danger','success') NOT NULL DEFAULT 'info',
  `icon` varchar(255) NOT NULL DEFAULT 'info-circle',
  `priority` enum('low','medium','high','critical') NOT NULL DEFAULT 'medium',
  `is_read` tinyint(1) NOT NULL DEFAULT 0,
  `read_by` bigint(20) unsigned DEFAULT NULL,
  `read_at` timestamp NULL DEFAULT NULL,
  `dismissible` tinyint(1) NOT NULL DEFAULT 1,
  `expires_at` timestamp NULL DEFAULT NULL,
  `created_by` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `admin_alerts_read_by_foreign` (`read_by`),
  KEY `admin_alerts_created_by_foreign` (`created_by`),
  KEY `admin_alerts_type_index` (`type`),
  KEY `admin_alerts_priority_index` (`priority`),
  KEY `admin_alerts_is_read_index` (`is_read`),
  KEY `admin_alerts_expires_at_index` (`expires_at`),
  CONSTRAINT `admin_alerts_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `admin_alerts_read_by_foreign` FOREIGN KEY (`read_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `admin_api_keys`;
CREATE TABLE `admin_api_keys` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `key` varchar(255) NOT NULL,
  `secret` varchar(255) DEFAULT NULL,
  `permissions` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`permissions`)),
  `ip_whitelist` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`ip_whitelist`)),
  `rate_limit` int(11) DEFAULT 60 COMMENT 'Requests per minute',
  `last_used_at` timestamp NULL DEFAULT NULL,
  `expires_at` timestamp NULL DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `admin_api_keys_key_unique` (`key`),
  KEY `admin_api_keys_created_by_foreign` (`created_by`),
  KEY `admin_api_keys_is_active_index` (`is_active`),
  CONSTRAINT `admin_api_keys_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `admin_audit_trails`;
CREATE TABLE `admin_audit_trails` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `action` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `model_type` varchar(255) DEFAULT NULL,
  `model_id` bigint(20) unsigned DEFAULT NULL,
  `old_values` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`old_values`)),
  `new_values` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`new_values`)),
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL,
  `method` varchar(10) DEFAULT NULL,
  `duration` int(11) DEFAULT NULL COMMENT 'Duration in milliseconds',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `admin_audit_trails_user_id_foreign` (`user_id`),
  KEY `admin_audit_trails_action_index` (`action`),
  KEY `admin_audit_trails_model_type_model_id_index` (`model_type`,`model_id`),
  KEY `admin_audit_trails_created_at_index` (`created_at`),
  CONSTRAINT `admin_audit_trails_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `admin_backups`;
CREATE TABLE `admin_backups` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `file_name` varchar(255) NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `file_size` bigint(20) NOT NULL DEFAULT 0,
  `type` enum('database','files','full') NOT NULL,
  `status` enum('pending','in_progress','completed','failed') NOT NULL DEFAULT 'pending',
  `includes` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`includes`)),
  `excludes` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`excludes`)),
  `started_at` timestamp NULL DEFAULT NULL,
  `completed_at` timestamp NULL DEFAULT NULL,
  `duration` int(11) DEFAULT NULL COMMENT 'Duration in seconds',
  `error_message` text DEFAULT NULL,
  `created_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `admin_backups_created_by_foreign` (`created_by`),
  KEY `admin_backups_status_index` (`status`),
  KEY `admin_backups_type_index` (`type`),
  CONSTRAINT `admin_backups_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `admin_dashboard_widgets`;
CREATE TABLE `admin_dashboard_widgets` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `admin_id` bigint(20) unsigned NOT NULL,
  `widget_type` varchar(100) NOT NULL COMMENT 'stats, chart, table, list, etc.',
  `widget_key` varchar(255) NOT NULL,
  `title` varchar(255) NOT NULL,
  `settings` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`settings`)),
  `position` int(11) NOT NULL DEFAULT 0,
  `column` int(11) NOT NULL DEFAULT 1 COMMENT '1-4 for grid column',
  `width` int(11) NOT NULL DEFAULT 1 COMMENT '1-4 for column span',
  `height` int(11) NOT NULL DEFAULT 1 COMMENT '1-3 for row height',
  `is_visible` tinyint(1) NOT NULL DEFAULT 1,
  `refresh_interval` int(11) DEFAULT NULL COMMENT 'Seconds between auto-refresh',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `admin_dashboard_widgets_admin_id_foreign` (`admin_id`),
  KEY `admin_dashboard_widgets_admin_id_position_index` (`admin_id`,`position`),
  CONSTRAINT `admin_dashboard_widgets_admin_id_foreign` FOREIGN KEY (`admin_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `admin_hotel_invoice_items`;
CREATE TABLE `admin_hotel_invoice_items` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `invoice_id` bigint(20) unsigned NOT NULL,
  `description` varchar(255) NOT NULL,
  `quantity` int(11) NOT NULL DEFAULT 1,
  `unit_price` decimal(12,2) NOT NULL,
  `total_price` decimal(12,2) NOT NULL,
  `tax_rate` decimal(5,2) NOT NULL DEFAULT 0.00,
  `tax_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `admin_hotel_invoice_items_invoice_id_foreign` (`invoice_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `admin_hotel_invoices`;
CREATE TABLE `admin_hotel_invoices` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `subscription_id` bigint(20) unsigned NOT NULL,
  `invoice_number` varchar(50) NOT NULL,
  `invoice_date` date NOT NULL,
  `due_date` date NOT NULL,
  `period_start` date NOT NULL,
  `period_end` date NOT NULL,
  `subtotal` decimal(12,2) NOT NULL,
  `tax_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `discount_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `total_amount` decimal(12,2) NOT NULL,
  `status` enum('draft','sent','paid','overdue','cancelled') NOT NULL DEFAULT 'draft',
  `paid_at` timestamp NULL DEFAULT NULL,
  `payment_id` bigint(20) unsigned DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `pdf_path` varchar(255) DEFAULT NULL,
  `created_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `admin_hotel_invoices_invoice_number_unique` (`invoice_number`),
  KEY `admin_hotel_invoices_hotel_id_foreign` (`hotel_id`),
  KEY `admin_hotel_invoices_subscription_id_foreign` (`subscription_id`),
  KEY `admin_hotel_invoices_payment_id_foreign` (`payment_id`),
  KEY `admin_hotel_invoices_status_index` (`status`),
  KEY `admin_hotel_invoices_due_date_index` (`due_date`),
  KEY `admin_hotel_invoices_created_by_foreign` (`created_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `admin_hotel_payments`;
CREATE TABLE `admin_hotel_payments` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `subscription_id` bigint(20) unsigned DEFAULT NULL,
  `payment_number` varchar(50) NOT NULL,
  `invoice_number` varchar(50) DEFAULT NULL,
  `amount` decimal(12,2) NOT NULL,
  `payment_date` date NOT NULL,
  `payment_method` enum('bank_transfer','credit_card','cash','cheque','mobile_money') NOT NULL,
  `transaction_reference` varchar(255) DEFAULT NULL,
  `status` enum('pending','completed','failed','refunded','cancelled') NOT NULL DEFAULT 'pending',
  `notes` text DEFAULT NULL,
  `received_by` bigint(20) unsigned NOT NULL,
  `receipt_path` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `admin_hotel_payments_payment_number_unique` (`payment_number`),
  KEY `admin_hotel_payments_hotel_id_foreign` (`hotel_id`),
  KEY `admin_hotel_payments_subscription_id_foreign` (`subscription_id`),
  KEY `admin_hotel_payments_status_index` (`status`),
  KEY `admin_hotel_payments_payment_date_index` (`payment_date`),
  KEY `admin_hotel_payments_received_by_foreign` (`received_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `admin_hotel_subscription_history`;
CREATE TABLE `admin_hotel_subscription_history` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `subscription_id` bigint(20) unsigned NOT NULL,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `action` varchar(255) NOT NULL,
  `old_values` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`old_values`)),
  `new_values` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`new_values`)),
  `reason` text DEFAULT NULL,
  `performed_by` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `admin_hotel_subscription_history_subscription_id_foreign` (`subscription_id`),
  KEY `admin_hotel_subscription_history_hotel_id_foreign` (`hotel_id`),
  KEY `admin_hotel_subscription_history_performed_by_foreign` (`performed_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `admin_hotel_subscription_history_backup`;
CREATE TABLE `admin_hotel_subscription_history_backup` (
  `id` bigint(20) unsigned NOT NULL DEFAULT 0,
  `subscription_id` bigint(20) unsigned NOT NULL,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `action` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'created, upgraded, downgraded, renewed, cancelled, suspended',
  `old_values` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`old_values`)),
  `new_values` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`new_values`)),
  `reason` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `performed_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `admin_hotel_subscriptions`;
CREATE TABLE `admin_hotel_subscriptions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL COMMENT 'References hotels table',
  `subscription_number` varchar(50) NOT NULL,
  `plan_name` enum('silver','gold','platinum','custom') NOT NULL,
  `plan_price` decimal(12,2) NOT NULL DEFAULT 0.00,
  `billing_cycle` enum('monthly','quarterly','yearly') NOT NULL DEFAULT 'monthly',
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `trial_start_date` date DEFAULT NULL,
  `trial_end_date` date DEFAULT NULL,
  `status` enum('active','trial','expired','cancelled','suspended') NOT NULL DEFAULT 'trial',
  `max_rooms` int(11) NOT NULL DEFAULT 20,
  `max_users` int(11) NOT NULL DEFAULT 2,
  `enabled_modules` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`enabled_modules`)),
  `notes` text DEFAULT NULL,
  `created_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `admin_hotel_subscriptions_subscription_number_unique` (`subscription_number`),
  KEY `admin_hotel_subscriptions_hotel_id_foreign` (`hotel_id`),
  KEY `admin_hotel_subscriptions_status_index` (`status`),
  KEY `admin_hotel_subscriptions_end_date_index` (`end_date`),
  KEY `admin_hotel_subscriptions_created_by_foreign` (`created_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `admin_maintenance_tasks`;
CREATE TABLE `admin_maintenance_tasks` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `command` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `schedule` varchar(255) DEFAULT NULL COMMENT 'Cron expression',
  `parameters` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`parameters`)),
  `priority` enum('low','medium','high','critical') NOT NULL DEFAULT 'medium',
  `status` enum('active','inactive','running','failed') NOT NULL DEFAULT 'active',
  `last_run_at` timestamp NULL DEFAULT NULL,
  `last_run_status` enum('success','failed','pending') DEFAULT NULL,
  `last_run_output` text DEFAULT NULL,
  `last_error` text DEFAULT NULL,
  `run_count` int(11) NOT NULL DEFAULT 0,
  `success_count` int(11) NOT NULL DEFAULT 0,
  `failure_count` int(11) NOT NULL DEFAULT 0,
  `average_duration` int(11) DEFAULT NULL COMMENT 'Average duration in seconds',
  `is_system` tinyint(1) NOT NULL DEFAULT 0,
  `created_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `admin_maintenance_tasks_created_by_foreign` (`created_by`),
  KEY `admin_maintenance_tasks_status_index` (`status`),
  CONSTRAINT `admin_maintenance_tasks_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `admin_notifications`;
CREATE TABLE `admin_notifications` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `type` varchar(255) NOT NULL COMMENT 'system, security, update, report, hotel',
  `title` varchar(255) NOT NULL,
  `message` text NOT NULL,
  `data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`data`)),
  `action_url` varchar(255) DEFAULT NULL,
  `action_text` varchar(255) DEFAULT NULL,
  `icon` varchar(255) DEFAULT NULL,
  `color` varchar(50) DEFAULT NULL,
  `is_read` tinyint(1) NOT NULL DEFAULT 0,
  `read_at` timestamp NULL DEFAULT NULL,
  `is_archived` tinyint(1) NOT NULL DEFAULT 0,
  `archived_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `admin_notifications_type_index` (`type`),
  KEY `admin_notifications_is_read_index` (`is_read`),
  KEY `admin_notifications_created_at_index` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `admin_performance_metrics`;
CREATE TABLE `admin_performance_metrics` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `metric_type` varchar(100) NOT NULL COMMENT 'response_time, memory_usage, cpu_load, etc.',
  `value` decimal(10,2) NOT NULL,
  `unit` varchar(20) NOT NULL,
  `context` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`context`)),
  `recorded_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `admin_performance_metrics_metric_type_index` (`metric_type`),
  KEY `admin_performance_metrics_recorded_at_index` (`recorded_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `admin_report_history`;
CREATE TABLE `admin_report_history` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `report_id` bigint(20) unsigned NOT NULL,
  `file_name` varchar(255) NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `file_size` int(11) NOT NULL DEFAULT 0,
  `format` enum('pdf','excel','csv') NOT NULL,
  `parameters_used` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`parameters_used`)),
  `generated_by` bigint(20) unsigned NOT NULL,
  `generated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `admin_report_history_report_id_foreign` (`report_id`),
  KEY `admin_report_history_generated_by_foreign` (`generated_by`),
  KEY `admin_report_history_generated_at_index` (`generated_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `admin_report_schedules`;
CREATE TABLE `admin_report_schedules` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `report_id` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `frequency` enum('daily','weekly','monthly','quarterly','yearly','custom') NOT NULL,
  `day_of_week` tinyint(4) DEFAULT NULL COMMENT '0-6 (Sunday=0)',
  `day_of_month` tinyint(4) DEFAULT NULL COMMENT '1-31',
  `time` time NOT NULL,
  `format` enum('pdf','excel','csv') NOT NULL DEFAULT 'pdf',
  `email_recipients` text DEFAULT NULL COMMENT 'Comma-separated emails',
  `include_charts` tinyint(1) NOT NULL DEFAULT 1,
  `last_run_at` timestamp NULL DEFAULT NULL,
  `last_run_status` enum('success','failed','pending') DEFAULT NULL,
  `last_error` text DEFAULT NULL,
  `next_run_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `admin_report_schedules_report_id_foreign` (`report_id`),
  KEY `admin_report_schedules_created_by_foreign` (`created_by`),
  KEY `admin_report_schedules_next_run_at_index` (`next_run_at`),
  KEY `admin_report_schedules_is_active_index` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `admin_reports`;
CREATE TABLE `admin_reports` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `category` enum('financial','hotel','user','subscription','system','custom') NOT NULL,
  `type` varchar(100) NOT NULL,
  `parameters` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`parameters`)),
  `filters` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`filters`)),
  `columns` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`columns`)),
  `chart_config` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`chart_config`)),
  `last_generated_at` timestamp NULL DEFAULT NULL,
  `last_generated_by` bigint(20) unsigned DEFAULT NULL,
  `is_scheduled` tinyint(1) NOT NULL DEFAULT 0,
  `schedule_config` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`schedule_config`)),
  `is_favorite` tinyint(1) NOT NULL DEFAULT 0,
  `is_public` tinyint(1) NOT NULL DEFAULT 0,
  `created_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `admin_reports_slug_unique` (`slug`),
  KEY `admin_reports_category_index` (`category`),
  KEY `admin_reports_is_favorite_index` (`is_favorite`),
  KEY `admin_reports_created_by_foreign` (`created_by`),
  KEY `admin_reports_last_generated_by_foreign` (`last_generated_by`),
  CONSTRAINT `admin_reports_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `admin_reports_last_generated_by_foreign` FOREIGN KEY (`last_generated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `admin_revenue_reports`;
CREATE TABLE `admin_revenue_reports` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `report_name` varchar(255) NOT NULL,
  `report_period` enum('daily','weekly','monthly','quarterly','yearly','custom') NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `total_revenue` decimal(15,2) NOT NULL DEFAULT 0.00,
  `total_payments` int(11) NOT NULL DEFAULT 0,
  `average_payment` decimal(15,2) NOT NULL DEFAULT 0.00,
  `payment_method_breakdown` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`payment_method_breakdown`)),
  `plan_breakdown` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`plan_breakdown`)),
  `data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`data`)),
  `generated_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `admin_revenue_reports_generated_by_foreign` (`generated_by`),
  KEY `admin_revenue_reports_start_date_end_date_index` (`start_date`,`end_date`),
  CONSTRAINT `admin_revenue_reports_generated_by_foreign` FOREIGN KEY (`generated_by`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `admin_system_logs`;
CREATE TABLE `admin_system_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `level` enum('debug','info','notice','warning','error','critical','alert') NOT NULL DEFAULT 'info',
  `channel` varchar(100) NOT NULL DEFAULT 'system',
  `message` text NOT NULL,
  `context` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`context`)),
  `file` varchar(255) DEFAULT NULL,
  `line` int(11) DEFAULT NULL,
  `trace` text DEFAULT NULL,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL,
  `method` varchar(10) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `admin_system_logs_level_index` (`level`),
  KEY `admin_system_logs_channel_index` (`channel`),
  KEY `admin_system_logs_created_at_index` (`created_at`),
  KEY `admin_system_logs_user_id_foreign` (`user_id`),
  CONSTRAINT `admin_system_logs_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `alerts`;
CREATE TABLE `alerts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `message` text NOT NULL,
  `type` enum('info','warning','danger','success') NOT NULL DEFAULT 'info',
  `icon` varchar(255) NOT NULL DEFAULT 'info-circle',
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `alerts_hotel_id_index` (`hotel_id`),
  CONSTRAINT `alerts_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `attendances`;
CREATE TABLE `attendances` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `staff_id` bigint(20) unsigned NOT NULL,
  `date` date DEFAULT NULL,
  `status` enum('present','absent','late','half_day','on_leave') NOT NULL DEFAULT 'absent',
  `clock_in_time` time DEFAULT NULL,
  `clock_out_time` time DEFAULT NULL,
  `recorded_by` bigint(20) unsigned DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `attendances_staff_id_date_unique` (`staff_id`,`date`),
  KEY `attendances_recorded_by_foreign` (`recorded_by`),
  KEY `attendances_hotel_id_foreign` (`hotel_id`),
  CONSTRAINT `attendances_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `audit_logs`;
CREATE TABLE `audit_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `document_id` bigint(20) unsigned DEFAULT NULL,
  `action` varchar(255) NOT NULL,
  `details` text DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `metadata` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`metadata`)),
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `audit_logs_user_id_created_at_index` (`user_id`,`created_at`),
  KEY `audit_logs_document_id_created_at_index` (`document_id`,`created_at`),
  KEY `audit_logs_action_created_at_index` (`action`,`created_at`),
  KEY `audit_logs_user_id_foreign` (`user_id`),
  KEY `audit_logs_document_id_foreign` (`document_id`),
  KEY `audit_logs_hotel_id_foreign` (`hotel_id`),
  CONSTRAINT `audit_logs_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `audit_logs` (`id`, `user_id`, `hotel_id`, `document_id`, `action`, `details`, `ip_address`, `user_agent`, `metadata`, `created_at`, `updated_at`) VALUES ('1', '1', '1', NULL, 'hotel_created', NULL, '127.0.0.1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/148.0.0.0 Safari/537.36 Edg/148.0.0.0', NULL, '2026-05-20 22:38:04', '2026-05-20 22:38:04');
INSERT INTO `audit_logs` (`id`, `user_id`, `hotel_id`, `document_id`, `action`, `details`, `ip_address`, `user_agent`, `metadata`, `created_at`, `updated_at`) VALUES ('2', '1', '1', NULL, 'user_created', NULL, '127.0.0.1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/148.0.0.0 Safari/537.36 Edg/148.0.0.0', NULL, '2026-05-20 22:41:26', '2026-05-20 22:41:26');
INSERT INTO `audit_logs` (`id`, `user_id`, `hotel_id`, `document_id`, `action`, `details`, `ip_address`, `user_agent`, `metadata`, `created_at`, `updated_at`) VALUES ('3', '2', '1', '1', 'view', 'Viewed document details', '127.0.0.1', NULL, NULL, '2026-05-25 14:24:04', '2026-05-25 14:24:04');
INSERT INTO `audit_logs` (`id`, `user_id`, `hotel_id`, `document_id`, `action`, `details`, `ip_address`, `user_agent`, `metadata`, `created_at`, `updated_at`) VALUES ('4', '2', '1', '1', 'download', 'Downloaded document', '127.0.0.1', NULL, NULL, '2026-05-25 14:24:37', '2026-05-25 14:24:37');

DROP TABLE IF EXISTS `bom`;
CREATE TABLE `bom` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `code` varchar(100) NOT NULL,
  `product_id` bigint(20) NOT NULL,
  `product_name` varchar(255) NOT NULL,
  `quantity` decimal(10,2) NOT NULL DEFAULT 1.00,
  `unit_of_measure` varchar(50) DEFAULT 'units',
  `active` tinyint(1) DEFAULT 1,
  `type` enum('normal','phantom') DEFAULT 'normal',
  `created_by` bigint(20) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `bom_lines`;
CREATE TABLE `bom_lines` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `bom_id` bigint(20) NOT NULL,
  `product_id` bigint(20) NOT NULL,
  `product_name` varchar(255) NOT NULL,
  `product_qty` decimal(10,2) NOT NULL,
  `unit_of_measure` varchar(50) DEFAULT 'units',
  `sequence` int(11) DEFAULT 10,
  PRIMARY KEY (`id`),
  KEY `bom_id` (`bom_id`),
  CONSTRAINT `bom_lines_ibfk_1` FOREIGN KEY (`bom_id`) REFERENCES `bom` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `bookings`;
CREATE TABLE `bookings` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bookings_hotel_id_index` (`hotel_id`),
  CONSTRAINT `bookings_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `budgets`;
CREATE TABLE `budgets` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `account_id` bigint(20) unsigned NOT NULL COMMENT 'The account being budgeted',
  `fiscal_year` year(4) NOT NULL,
  `amount` decimal(15,2) NOT NULL DEFAULT 0.00,
  `period` enum('monthly','quarterly','yearly') NOT NULL DEFAULT 'monthly',
  `notes` text DEFAULT NULL,
  `created_by` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `budgets_hotel_account_year_period_unique` (`hotel_id`,`account_id`,`fiscal_year`,`period`),
  KEY `budgets_account_id_foreign` (`account_id`),
  KEY `budgets_created_by_foreign` (`created_by`),
  CONSTRAINT `budgets_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `cache`;
CREATE TABLE `cache` (
  `key` varchar(255) NOT NULL,
  `value` mediumtext NOT NULL,
  `expiration` int(11) NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `cache_locks`;
CREATE TABLE `cache_locks` (
  `key` varchar(255) NOT NULL,
  `owner` varchar(255) NOT NULL,
  `expiration` int(11) NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `categories`;
CREATE TABLE `categories` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `type` enum('product','service','inventory','other') NOT NULL DEFAULT 'product',
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_hotel_id` (`hotel_id`),
  CONSTRAINT `categories_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `categories` (`id`, `hotel_id`, `name`, `description`, `type`, `active`, `is_active`, `created_at`, `updated_at`) VALUES ('1', '1', 'Food & Beverage', NULL, 'inventory', '1', '1', '2026-05-21 02:35:41', '2026-05-21 02:35:41');
INSERT INTO `categories` (`id`, `hotel_id`, `name`, `description`, `type`, `active`, `is_active`, `created_at`, `updated_at`) VALUES ('2', '1', 'Housekeeping & Laundry', NULL, 'inventory', '1', '1', '2026-05-21 02:35:41', '2026-05-21 02:35:41');
INSERT INTO `categories` (`id`, `hotel_id`, `name`, `description`, `type`, `active`, `is_active`, `created_at`, `updated_at`) VALUES ('3', '1', 'Maintenance & Engineering', NULL, 'inventory', '1', '1', '2026-05-21 02:35:41', '2026-05-21 02:35:41');
INSERT INTO `categories` (`id`, `hotel_id`, `name`, `description`, `type`, `active`, `is_active`, `created_at`, `updated_at`) VALUES ('4', '1', 'Office & Stationery', NULL, 'inventory', '1', '1', '2026-05-21 02:35:41', '2026-05-21 02:35:41');
INSERT INTO `categories` (`id`, `hotel_id`, `name`, `description`, `type`, `active`, `is_active`, `created_at`, `updated_at`) VALUES ('5', '1', 'IT & Electronics', NULL, 'inventory', '1', '1', '2026-05-21 02:35:41', '2026-05-21 02:35:41');
INSERT INTO `categories` (`id`, `hotel_id`, `name`, `description`, `type`, `active`, `is_active`, `created_at`, `updated_at`) VALUES ('6', '1', 'Linen & Textiles', NULL, 'inventory', '1', '1', '2026-05-21 02:35:41', '2026-05-21 02:35:41');
INSERT INTO `categories` (`id`, `hotel_id`, `name`, `description`, `type`, `active`, `is_active`, `created_at`, `updated_at`) VALUES ('7', '1', 'Amenities & Guest Supplies', NULL, 'inventory', '1', '1', '2026-05-21 02:35:41', '2026-05-21 02:35:41');
INSERT INTO `categories` (`id`, `hotel_id`, `name`, `description`, `type`, `active`, `is_active`, `created_at`, `updated_at`) VALUES ('8', '1', 'Kitchen & Culinary', NULL, 'inventory', '1', '1', '2026-05-21 02:35:41', '2026-05-21 02:35:41');

DROP TABLE IF EXISTS `chart_of_accounts`;
CREATE TABLE `chart_of_accounts` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `code` varchar(20) NOT NULL,
  `name` varchar(255) NOT NULL,
  `type` enum('asset','liability','equity','income','expense') NOT NULL,
  `parent_id` bigint(20) DEFAULT NULL,
  `balance` decimal(15,2) DEFAULT 0.00,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`),
  KEY `parent_id` (`parent_id`),
  CONSTRAINT `chart_of_accounts_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `chart_of_accounts` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `contact_histories`;
CREATE TABLE `contact_histories` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `contact_id` bigint(20) unsigned NOT NULL,
  `action` varchar(255) NOT NULL,
  `details` text DEFAULT NULL,
  `performed_by` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `contact_history_contact_id_foreign` (`contact_id`),
  KEY `contact_history_performed_by_foreign` (`performed_by`),
  KEY `contact_histories_hotel_id_index` (`hotel_id`),
  CONSTRAINT `contact_histories_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `contact_history`;
CREATE TABLE `contact_history` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `contact_id` bigint(20) unsigned NOT NULL,
  `action` varchar(255) NOT NULL,
  `details` text DEFAULT NULL,
  `performed_by` bigint(20) unsigned DEFAULT NULL,
  `performed_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `contact_history_contact_id_foreign` (`contact_id`),
  KEY `contact_history_performed_by_foreign` (`performed_by`),
  KEY `contact_history_hotel_id_index` (`hotel_id`),
  CONSTRAINT `contact_history_contact_id_foreign` FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `contact_history_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE,
  CONSTRAINT `contact_history_performed_by_foreign` FOREIGN KEY (`performed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `contact_notes`;
CREATE TABLE `contact_notes` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `contact_id` bigint(20) unsigned NOT NULL,
  `note` text NOT NULL,
  `created_by` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `contact_notes_contact_id_foreign` (`contact_id`),
  KEY `contact_notes_created_by_foreign` (`created_by`),
  KEY `contact_notes_hotel_id_foreign` (`hotel_id`),
  CONSTRAINT `contact_notes_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `contact_requests`;
CREATE TABLE `contact_requests` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned DEFAULT NULL,
  `name` varchar(150) NOT NULL,
  `email` varchar(191) NOT NULL,
  `organization` varchar(191) DEFAULT NULL,
  `phone` varchar(60) DEFAULT NULL,
  `message` text DEFAULT NULL,
  `source` varchar(60) NOT NULL DEFAULT 'website_contact_form',
  `status` varchar(30) NOT NULL DEFAULT 'new',
  `ip_address` varchar(60) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `admin_notes` text DEFAULT NULL,
  `read_at` timestamp NULL DEFAULT NULL,
  `responded_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `contact_requests_status_index` (`status`),
  KEY `contact_requests_created_at_index` (`created_at`),
  KEY `contact_requests_email_index` (`email`),
  KEY `contact_requests_hotel_id_index` (`hotel_id`),
  CONSTRAINT `contact_requests_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `contacts`;
CREATE TABLE `contacts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `type` enum('guest','staff','supplier','other') NOT NULL DEFAULT 'guest',
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `company` varchar(255) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `country` varchar(255) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `contacts_type_active_index` (`type`,`active`),
  KEY `contacts_first_name_last_name_index` (`first_name`,`last_name`),
  KEY `contacts_email_index` (`email`),
  KEY `contacts_company_index` (`company`),
  KEY `contacts_hotel_id_index` (`hotel_id`),
  CONSTRAINT `contacts_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `daily_stats`;
CREATE TABLE `daily_stats` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned DEFAULT NULL,
  `stat_date` date NOT NULL,
  `visitors` int(11) DEFAULT 0,
  `unique_visitors` int(11) DEFAULT 0,
  `page_views` int(11) DEFAULT 0,
  `leads_count` int(11) DEFAULT 0,
  `conversion_rate` decimal(5,2) DEFAULT 0.00,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `stat_date` (`stat_date`),
  KEY `idx_stat_date` (`stat_date`),
  KEY `daily_stats_hotel_id_index` (`hotel_id`),
  CONSTRAINT `daily_stats_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `document_folders`;
CREATE TABLE `document_folders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `parent_id` bigint(20) unsigned DEFAULT NULL,
  `category` varchar(255) NOT NULL DEFAULT 'general',
  `description` text DEFAULT NULL,
  `created_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `document_folders_slug_unique` (`slug`),
  KEY `document_folders_parent_id_foreign` (`parent_id`),
  KEY `document_folders_created_by_foreign` (`created_by`),
  KEY `document_folders_hotel_id_index` (`hotel_id`),
  CONSTRAINT `document_folders_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `document_folders_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE SET NULL,
  CONSTRAINT `document_folders_parent_id_foreign` FOREIGN KEY (`parent_id`) REFERENCES `document_folders` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `document_shares`;
CREATE TABLE `document_shares` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned DEFAULT NULL,
  `document_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `permission` varchar(255) NOT NULL DEFAULT 'view',
  `expires_at` timestamp NULL DEFAULT NULL,
  `allowed_actions` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`allowed_actions`)),
  `shared_by` bigint(20) unsigned NOT NULL,
  `message` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `document_shares_document_id_user_id_unique` (`document_id`,`user_id`),
  KEY `document_shares_user_id_foreign` (`user_id`),
  KEY `document_shares_shared_by_foreign` (`shared_by`),
  KEY `document_shares_hotel_id_index` (`hotel_id`),
  CONSTRAINT `document_shares_document_id_foreign` FOREIGN KEY (`document_id`) REFERENCES `documents` (`id`) ON DELETE CASCADE,
  CONSTRAINT `document_shares_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE SET NULL,
  CONSTRAINT `document_shares_shared_by_foreign` FOREIGN KEY (`shared_by`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `document_shares_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `document_versions`;
CREATE TABLE `document_versions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned DEFAULT NULL,
  `document_id` bigint(20) unsigned NOT NULL,
  `version` int(11) NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `file_size` bigint(20) NOT NULL DEFAULT 0,
  `changes` text DEFAULT NULL,
  `created_by` bigint(20) unsigned NOT NULL,
  `metadata` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`metadata`)),
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `document_versions_document_id_version_unique` (`document_id`,`version`),
  KEY `document_versions_document_id_created_at_index` (`document_id`,`created_at`),
  KEY `document_versions_created_by_foreign` (`created_by`),
  KEY `document_versions_hotel_id_index` (`hotel_id`),
  CONSTRAINT `document_versions_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `document_versions_document_id_foreign` FOREIGN KEY (`document_id`) REFERENCES `documents` (`id`) ON DELETE CASCADE,
  CONSTRAINT `document_versions_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `documents`;
CREATE TABLE `documents` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `file_name` varchar(255) NOT NULL,
  `file_type` varchar(255) NOT NULL,
  `file_size` int(11) NOT NULL,
  `category` enum('guest','staff','supplier','financial','legal','other') NOT NULL,
  `type` enum('identification','contract','invoice','receipt','license','certificate','other') NOT NULL,
  `description` text DEFAULT NULL,
  `tags` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`tags`)),
  `documentable_type` varchar(255) DEFAULT NULL,
  `documentable_id` bigint(20) unsigned DEFAULT NULL,
  `custom_owner_name` varchar(255) DEFAULT NULL,
  `uploaded_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `folder_id` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `documents_documentable_type_documentable_id_index` (`documentable_type`,`documentable_id`),
  KEY `documents_uploaded_by_foreign` (`uploaded_by`),
  KEY `documents_category_index` (`category`),
  KEY `documents_type_index` (`type`),
  KEY `documents_documentable_index` (`documentable_type`,`documentable_id`),
  KEY `documents_hotel_id_index` (`hotel_id`),
  KEY `documents_folder_id_foreign` (`folder_id`),
  CONSTRAINT `documents_folder_id_foreign` FOREIGN KEY (`folder_id`) REFERENCES `folders` (`id`) ON DELETE SET NULL,
  CONSTRAINT `documents_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE,
  CONSTRAINT `documents_uploaded_by_foreign` FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `documents` (`id`, `hotel_id`, `name`, `file_path`, `file_name`, `file_type`, `file_size`, `category`, `type`, `description`, `tags`, `documentable_type`, `documentable_id`, `custom_owner_name`, `uploaded_by`, `created_at`, `updated_at`, `deleted_at`, `folder_id`) VALUES ('1', '1', 'room-availability-2026-05-24-140453', 'hotels/1/documents/1779709394_Ouk082AvgC.pdf', '1779709394_Ouk082AvgC.pdf', 'pdf', '4518', 'guest', 'contract', 'jkkkkkijuhn', '[]', NULL, NULL, 'nmmmmm', '2', '2026-05-25 11:43:14', '2026-05-25 11:43:14', NULL, NULL);

DROP TABLE IF EXISTS `equipment`;
CREATE TABLE `equipment` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `equipment_hotel_id_index` (`hotel_id`),
  CONSTRAINT `equipment_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `expense_categories`;
CREATE TABLE `expense_categories` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `monthly_budget` decimal(10,2) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `expense_categories_hotel_id_index` (`hotel_id`),
  CONSTRAINT `expense_categories_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `expenses`;
CREATE TABLE `expenses` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `date` date NOT NULL,
  `vendor_id` bigint(20) unsigned NOT NULL,
  `category_id` bigint(20) unsigned NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `description` text DEFAULT NULL,
  `status` varchar(255) NOT NULL DEFAULT 'pending',
  `receipt_path` varchar(255) DEFAULT NULL,
  `approved_by` bigint(20) unsigned DEFAULT NULL,
  `approved_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `accounting_synced` tinyint(1) NOT NULL DEFAULT 0,
  `accounting_synced_at` timestamp NULL DEFAULT NULL,
  `accounting_sync_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `expenses_vendor_id_foreign` (`vendor_id`),
  KEY `expenses_category_id_foreign` (`category_id`),
  KEY `expenses_approved_by_foreign` (`approved_by`),
  KEY `expenses_hotel_id_index` (`hotel_id`),
  CONSTRAINT `expenses_approved_by_foreign` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`),
  CONSTRAINT `expenses_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `expense_categories` (`id`),
  CONSTRAINT `expenses_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE,
  CONSTRAINT `expenses_vendor_id_foreign` FOREIGN KEY (`vendor_id`) REFERENCES `vendors` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `failed_jobs`;
CREATE TABLE `failed_jobs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(255) NOT NULL,
  `connection` text NOT NULL,
  `queue` text NOT NULL,
  `payload` longtext NOT NULL,
  `exception` longtext NOT NULL,
  `failed_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `folder_shares`;
CREATE TABLE `folder_shares` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned DEFAULT NULL,
  `folder_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `permission` varchar(255) NOT NULL DEFAULT 'view',
  `expires_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `folder_shares_folder_id_user_id_unique` (`folder_id`,`user_id`),
  KEY `folder_shares_user_id_foreign` (`user_id`),
  KEY `folder_shares_hotel_id_index` (`hotel_id`),
  CONSTRAINT `folder_shares_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `folders`;
CREATE TABLE `folders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `category` varchar(255) NOT NULL,
  `parent_id` bigint(20) unsigned DEFAULT NULL,
  `description` text DEFAULT NULL,
  `is_private` tinyint(1) NOT NULL DEFAULT 0,
  `created_by` bigint(20) unsigned NOT NULL,
  `permissions` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`permissions`)),
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `folders_parent_id_foreign` (`parent_id`),
  KEY `folders_created_by_foreign` (`created_by`),
  KEY `folders_hotel_id_index` (`hotel_id`),
  CONSTRAINT `folders_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `folders_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE,
  CONSTRAINT `folders_parent_id_foreign` FOREIGN KEY (`parent_id`) REFERENCES `folders` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `guest_activities`;
CREATE TABLE `guest_activities` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `guest_id` bigint(20) unsigned NOT NULL,
  `activity_type` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `metadata` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`metadata`)),
  `recorded_by` bigint(20) unsigned DEFAULT NULL,
  `activity_date` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `recorded_by` (`recorded_by`),
  KEY `idx_guest_activities_guest_id` (`guest_id`),
  KEY `idx_guest_activities_activity_date` (`activity_date`),
  KEY `idx_guest_activities_type` (`activity_type`),
  KEY `idx_guest_activities_guest_date` (`guest_id`,`activity_date`),
  KEY `guest_activities_hotel_id_index` (`hotel_id`),
  CONSTRAINT `guest_activities_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `guest_activities` (`id`, `hotel_id`, `guest_id`, `activity_type`, `description`, `metadata`, `recorded_by`, `activity_date`, `created_at`, `updated_at`) VALUES ('1', '1', '1', 'profile_created', 'New guest profile was created in the system.', '{\"created_by\":\"System\"}', '2', '2026-05-25 13:47:21', '2026-05-25 13:47:21', '2026-05-25 13:47:21');

DROP TABLE IF EXISTS `guest_ledger`;
CREATE TABLE `guest_ledger` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `reservation_id` bigint(20) unsigned NOT NULL,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `guest_id` bigint(20) unsigned NOT NULL,
  `transaction_date` datetime NOT NULL,
  `description` varchar(255) NOT NULL,
  `reference_type` varchar(50) DEFAULT NULL,
  `reference_id` bigint(20) unsigned DEFAULT NULL COMMENT 'ID of source record',
  `debit` decimal(12,2) NOT NULL DEFAULT 0.00 COMMENT 'Charges to guest',
  `credit` decimal(12,2) NOT NULL DEFAULT 0.00 COMMENT 'Payments from guest',
  `balance` decimal(12,2) NOT NULL DEFAULT 0.00 COMMENT 'Running balance after this transaction',
  `invoice_id` bigint(20) unsigned DEFAULT NULL COMMENT 'Linked when invoiced',
  `accounting_synced` tinyint(1) NOT NULL DEFAULT 0,
  `created_by` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `accounting_sync_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `guest_ledger_reservation_id_index` (`reservation_id`),
  KEY `guest_ledger_guest_id_index` (`guest_id`),
  KEY `guest_ledger_invoice_id_index` (`invoice_id`),
  KEY `guest_ledger_hotel_id_foreign` (`hotel_id`),
  CONSTRAINT `guest_ledger_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `guests`;
CREATE TABLE `guests` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `status` varchar(255) NOT NULL DEFAULT 'active',
  `phone` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `country` varchar(255) DEFAULT NULL,
  `nationality` varchar(255) DEFAULT NULL,
  `id_type` varchar(255) DEFAULT NULL,
  `id_number` varchar(255) DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `gender` enum('male','female','other') DEFAULT NULL,
  `guest_type` varchar(50) DEFAULT NULL,
  `preferences` text DEFAULT NULL,
  `special_requests` text DEFAULT NULL,
  `company` varchar(255) DEFAULT NULL,
  `is_blacklisted` tinyint(1) NOT NULL DEFAULT 0,
  `blacklist_reason` text DEFAULT NULL,
  `loyalty_points` int(11) NOT NULL DEFAULT 0,
  `total_visits` int(10) unsigned NOT NULL DEFAULT 0,
  `is_walkin_guest` tinyint(1) NOT NULL DEFAULT 0,
  `is_reservation_guest` tinyint(1) NOT NULL DEFAULT 0,
  `is_service_guest` tinyint(1) NOT NULL DEFAULT 0,
  `is_regular_guest` tinyint(1) NOT NULL DEFAULT 0,
  `last_visit_date` date DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `guests_email_unique` (`email`),
  KEY `guests_hotel_id_index` (`hotel_id`),
  CONSTRAINT `guests_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `guests` (`id`, `hotel_id`, `first_name`, `last_name`, `email`, `status`, `phone`, `address`, `city`, `country`, `nationality`, `id_type`, `id_number`, `date_of_birth`, `gender`, `guest_type`, `preferences`, `special_requests`, `company`, `is_blacklisted`, `blacklist_reason`, `loyalty_points`, `total_visits`, `is_walkin_guest`, `is_reservation_guest`, `is_service_guest`, `is_regular_guest`, `last_visit_date`, `created_at`, `updated_at`, `deleted_at`) VALUES ('1', '1', 'ghghhjj', 'hjhjujhjk', 'guiuyug@gmail.com', 'active', '0708965432', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '0', NULL, '0', '0', '0', '0', '0', '0', NULL, '2026-05-25 13:47:21', '2026-05-25 13:47:21', NULL);
INSERT INTO `guests` (`id`, `hotel_id`, `first_name`, `last_name`, `email`, `status`, `phone`, `address`, `city`, `country`, `nationality`, `id_type`, `id_number`, `date_of_birth`, `gender`, `guest_type`, `preferences`, `special_requests`, `company`, `is_blacklisted`, `blacklist_reason`, `loyalty_points`, `total_visits`, `is_walkin_guest`, `is_reservation_guest`, `is_service_guest`, `is_regular_guest`, `last_visit_date`, `created_at`, `updated_at`, `deleted_at`) VALUES ('3', '1', 'bvbbn', 'ghghh', 'bvbbn.ghghh.1779719896@temp.local', 'active', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '0', NULL, '0', '0', '0', '0', '1', '0', NULL, '2026-05-25 14:38:16', '2026-05-25 14:38:16', NULL);

DROP TABLE IF EXISTS `hotels`;
CREATE TABLE `hotels` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `package_name` varchar(255) NOT NULL DEFAULT 'silver',
  `slug` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(255) NOT NULL,
  `address` text NOT NULL,
  `city` varchar(255) NOT NULL,
  `country` varchar(255) NOT NULL DEFAULT 'Uganda',
  `license_number` varchar(255) DEFAULT NULL,
  `tax_id` varchar(255) DEFAULT NULL,
  `logo` varchar(255) DEFAULT NULL,
  `website` varchar(255) DEFAULT NULL,
  `package` enum('silver','gold','platinum','custom') NOT NULL DEFAULT 'silver',
  `max_rooms` int(11) NOT NULL DEFAULT 20,
  `max_users` int(11) NOT NULL DEFAULT 2,
  `enabled_modules` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`enabled_modules`)),
  `monthly_price` decimal(12,2) NOT NULL DEFAULT 0.00,
  `billing_cycle` enum('monthly','quarterly','yearly') NOT NULL DEFAULT 'monthly',
  `trial_ends_at` timestamp NULL DEFAULT NULL,
  `subscription_ends_at` timestamp NULL DEFAULT NULL,
  `subscription_starts_at` timestamp NULL DEFAULT NULL,
  `status` enum('active','trial','suspended','cancelled') NOT NULL DEFAULT 'trial',
  `settings` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`settings`)),
  `chart_of_accounts_installed` tinyint(1) NOT NULL DEFAULT 0,
  `fiscal_year_start` date DEFAULT NULL,
  `fiscal_year_end` date DEFAULT NULL,
  `base_currency` varchar(3) NOT NULL DEFAULT 'UGX',
  `vat_rate` decimal(5,2) NOT NULL DEFAULT 18.00,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `check_in_time` varchar(255) DEFAULT '14:00',
  `check_out_time` varchar(255) DEFAULT '12:00',
  `public_booking_template` varchar(32) NOT NULL DEFAULT '1' COMMENT '1–4: which Blade layout/theme the public site uses',
  `public_booking_enabled` tinyint(1) NOT NULL DEFAULT 1,
  `public_site_color_primary` varchar(7) DEFAULT NULL,
  `public_site_color_accent` varchar(7) DEFAULT NULL,
  `public_site_tagline` varchar(500) DEFAULT NULL,
  `public_site_intro` text DEFAULT NULL,
  `public_site_cta_text` varchar(150) DEFAULT NULL,
  `public_site_extra_footer` text DEFAULT NULL,
  `public_site_hero_image` varchar(500) DEFAULT NULL,
  `public_site_hero_video_path` varchar(500) DEFAULT NULL,
  `public_site_hero_video_url` varchar(500) DEFAULT NULL,
  `public_site_hero_eyebrow` varchar(150) DEFAULT NULL,
  `public_site_hero_kicker` varchar(500) DEFAULT NULL,
  `public_site_gallery_1` varchar(500) DEFAULT NULL,
  `public_site_gallery_2` varchar(500) DEFAULT NULL,
  `public_site_gallery_3` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `hotels_slug_unique` (`slug`),
  UNIQUE KEY `hotels_email_unique` (`email`),
  KEY `hotels_status_index` (`status`),
  KEY `hotels_package_index` (`package`),
  KEY `hotels_status_subscription_ends_at_index` (`status`,`subscription_ends_at`),
  KEY `idx_status` (`status`),
  KEY `idx_package` (`package`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `hotels` (`id`, `name`, `package_name`, `slug`, `email`, `phone`, `address`, `city`, `country`, `license_number`, `tax_id`, `logo`, `website`, `package`, `max_rooms`, `max_users`, `enabled_modules`, `monthly_price`, `billing_cycle`, `trial_ends_at`, `subscription_ends_at`, `subscription_starts_at`, `status`, `settings`, `chart_of_accounts_installed`, `fiscal_year_start`, `fiscal_year_end`, `base_currency`, `vat_rate`, `created_at`, `updated_at`, `deleted_at`, `check_in_time`, `check_out_time`, `public_booking_template`, `public_booking_enabled`, `public_site_color_primary`, `public_site_color_accent`, `public_site_tagline`, `public_site_intro`, `public_site_cta_text`, `public_site_extra_footer`, `public_site_hero_image`, `public_site_hero_video_path`, `public_site_hero_video_url`, `public_site_hero_eyebrow`, `public_site_hero_kicker`, `public_site_gallery_1`, `public_site_gallery_2`, `public_site_gallery_3`) VALUES ('1', 'JEHOVA', 'silver', 'jehova-1lbe4p', 'nivnabwami@gmail.com', '+256702064779', 'leg', 'nairobi', 'Kenya', NULL, NULL, 'hotels/logos/NrbYsUic0mt7xjA0Y4ijUL1pupczCrnTbRgBkgJB.png', NULL, 'platinum', '60', '10', '[\"rooms\",\"reservations\",\"accounting\",\"front_desk\",\"guests\",\"housekeeping\",\"maintenance\",\"survey\",\"documents\",\"hotel_services\",\"inventory\",\"hr\",\"reporting\",\"food_beverage\",\"settings\"]', '14865.00', 'monthly', '2026-06-20 00:00:00', '2026-07-20 00:00:00', '2026-06-20 00:00:00', 'trial', NULL, '0', NULL, NULL, 'UGX', '18.00', '2026-05-20 22:38:03', '2026-05-20 22:38:03', NULL, '14:00', '12:00', '1', '1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);

DROP TABLE IF EXISTS `housekeeping_schedule`;
CREATE TABLE `housekeeping_schedule` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `housekeeping_schedule_hotel_id_index` (`hotel_id`),
  CONSTRAINT `housekeeping_schedule_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `housekeeping_staff`;
CREATE TABLE `housekeeping_staff` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned DEFAULT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `position` varchar(255) NOT NULL,
  `shift_start` time NOT NULL,
  `shift_end` time NOT NULL,
  `status` enum('available','busy','off_duty') NOT NULL DEFAULT 'available',
  `tasks_completed` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `on_duty` tinyint(1) DEFAULT 0,
  `department` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `housekeeping_staff_user_id_foreign` (`user_id`),
  KEY `housekeeping_staff_hotel_id_index` (`hotel_id`),
  CONSTRAINT `housekeeping_staff_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE SET NULL,
  CONSTRAINT `housekeeping_staff_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `housekeeping_tasks`;
CREATE TABLE `housekeeping_tasks` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `task_name` varchar(255) NOT NULL,
  `task_type` varchar(255) DEFAULT 'daily',
  `description` text DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `scheduled_date` date NOT NULL,
  `scheduled_time` time DEFAULT NULL,
  `priority` enum('low','medium','high','critical') DEFAULT 'medium',
  `status` enum('pending','in_progress','completed','cancelled') DEFAULT 'pending',
  `assigned_to` bigint(20) unsigned DEFAULT NULL,
  `assigned_staff_id` bigint(20) unsigned DEFAULT NULL,
  `assigned_at` timestamp NULL DEFAULT NULL,
  `room_id` bigint(20) unsigned DEFAULT NULL,
  `estimated_duration` int(11) DEFAULT NULL,
  `actual_duration` int(11) DEFAULT NULL,
  `completed_time` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `started_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `assigned_to` (`assigned_to`),
  KEY `room_id` (`room_id`),
  KEY `idx_scheduled_date` (`scheduled_date`),
  KEY `idx_scheduled_time` (`scheduled_time`),
  KEY `idx_priority` (`priority`),
  KEY `idx_status` (`status`),
  KEY `idx_assigned_staff_id` (`assigned_staff_id`),
  KEY `idx_completed_time` (`completed_time`),
  KEY `idx_housekeeping_tasks_room_id` (`room_id`),
  KEY `idx_housekeeping_tasks_status` (`status`),
  KEY `idx_housekeeping_tasks_assigned_staff` (`assigned_staff_id`),
  KEY `housekeeping_tasks_hotel_id_index` (`hotel_id`),
  CONSTRAINT `housekeeping_tasks_assigned_staff_id_foreign` FOREIGN KEY (`assigned_staff_id`) REFERENCES `staff` (`id`) ON DELETE SET NULL,
  CONSTRAINT `housekeeping_tasks_assigned_to_foreign` FOREIGN KEY (`assigned_to`) REFERENCES `staff` (`id`) ON DELETE SET NULL,
  CONSTRAINT `housekeeping_tasks_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE,
  CONSTRAINT `housekeeping_tasks_ibfk_3` FOREIGN KEY (`room_id`) REFERENCES `rooms` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `in_out_logs`;
CREATE TABLE `in_out_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `reservation_id` bigint(20) unsigned DEFAULT NULL,
  `guest_id` bigint(20) unsigned DEFAULT NULL,
  `room_id` bigint(20) unsigned DEFAULT NULL,
  `type` enum('checkin','checkout','payment','cancellation','no_show','other','confirmation') NOT NULL DEFAULT 'other',
  `logged_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `notes` text DEFAULT NULL,
  `amount_paid` decimal(12,2) DEFAULT 0.00,
  `payment_method` varchar(50) DEFAULT NULL,
  `invoice_id` bigint(20) unsigned DEFAULT NULL,
  `receipt_issued` tinyint(1) NOT NULL DEFAULT 0,
  `processed_by` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `in_out_logs_reservation_id_foreign` (`reservation_id`),
  KEY `in_out_logs_guest_id_foreign` (`guest_id`),
  KEY `in_out_logs_room_id_foreign` (`room_id`),
  KEY `in_out_logs_invoice_id_foreign` (`invoice_id`),
  KEY `in_out_logs_processed_by_foreign` (`processed_by`),
  KEY `in_out_logs_type_index` (`type`),
  KEY `in_out_logs_logged_at_index` (`logged_at`),
  KEY `in_out_logs_hotel_id_index` (`hotel_id`),
  CONSTRAINT `in_out_logs_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `installment_payments`;
CREATE TABLE `installment_payments` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `installment_plan_id` bigint(20) unsigned NOT NULL,
  `invoice_id` bigint(20) unsigned DEFAULT NULL,
  `installment_number` int(11) NOT NULL,
  `due_date` date NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `paid_amount` decimal(12,2) DEFAULT 0.00,
  `payment_date` date DEFAULT NULL,
  `payment_method` varchar(255) DEFAULT NULL,
  `transaction_id` varchar(255) DEFAULT NULL,
  `status` enum('pending','paid','overdue','partial','cancelled') DEFAULT 'pending',
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `installment_payments_installment_plan_id_index` (`installment_plan_id`),
  KEY `installment_payments_invoice_id_index` (`invoice_id`),
  KEY `installment_payments_status_index` (`status`),
  KEY `installment_payments_due_date_index` (`due_date`),
  KEY `installment_payments_payment_date_index` (`payment_date`),
  KEY `installment_payments_installment_number_index` (`installment_number`),
  KEY `installment_payments_hotel_id_index` (`hotel_id`),
  CONSTRAINT `installment_payments_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `installment_plans`;
CREATE TABLE `installment_plans` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `reservation_id` bigint(20) unsigned NOT NULL,
  `total_amount` decimal(12,2) NOT NULL,
  `down_payment` decimal(12,2) DEFAULT 0.00,
  `installment_count` int(11) NOT NULL,
  `installment_amount` decimal(12,2) NOT NULL,
  `frequency` varchar(255) NOT NULL COMMENT 'weekly, monthly, bi-weekly',
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `status` enum('active','completed','cancelled') DEFAULT 'active',
  `terms` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `installment_plans_reservation_id_index` (`reservation_id`),
  KEY `installment_plans_status_index` (`status`),
  KEY `installment_plans_start_date_index` (`start_date`),
  KEY `installment_plans_end_date_index` (`end_date`),
  KEY `installment_plans_hotel_id_index` (`hotel_id`),
  CONSTRAINT `installment_plans_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE,
  CONSTRAINT `installment_plans_reservation_id_foreign` FOREIGN KEY (`reservation_id`) REFERENCES `reservations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `inventory_issues`;
CREATE TABLE `inventory_issues` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `inventory_item_id` bigint(20) unsigned NOT NULL,
  `quantity` int(11) NOT NULL,
  `unit` varchar(50) DEFAULT NULL,
  `issued_to_name` varchar(255) NOT NULL,
  `issued_to_department` varchar(100) DEFAULT NULL,
  `reason` varchar(100) NOT NULL,
  `status` enum('pending','approved','issued','rejected') NOT NULL DEFAULT 'pending',
  `notes` text DEFAULT NULL,
  `issued_by` bigint(20) unsigned DEFAULT NULL,
  `approved_by` bigint(20) unsigned DEFAULT NULL,
  `approved_at` timestamp NULL DEFAULT NULL,
  `rejected_by` bigint(20) unsigned DEFAULT NULL,
  `rejected_at` timestamp NULL DEFAULT NULL,
  `rejection_reason` text DEFAULT NULL,
  `issued_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `inventory_issues_inventory_item_id_foreign` (`inventory_item_id`),
  KEY `inventory_issues_issued_by_foreign` (`issued_by`),
  KEY `inventory_issues_hotel_id_index` (`hotel_id`),
  KEY `inventory_issues_approved_by_foreign` (`approved_by`),
  KEY `inventory_issues_rejected_by_foreign` (`rejected_by`),
  CONSTRAINT `inventory_issues_approved_by_foreign` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `inventory_issues_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE,
  CONSTRAINT `inventory_issues_inventory_item_id_foreign` FOREIGN KEY (`inventory_item_id`) REFERENCES `inventory_items` (`id`) ON DELETE CASCADE,
  CONSTRAINT `inventory_issues_issued_by_foreign` FOREIGN KEY (`issued_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `inventory_issues_rejected_by_foreign` FOREIGN KEY (`rejected_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `inventory_items`;
CREATE TABLE `inventory_items` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `sku` varchar(100) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `category_id` bigint(20) unsigned DEFAULT NULL,
  `department` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `category` varchar(255) NOT NULL,
  `quantity` int(11) NOT NULL DEFAULT 0,
  `unit_cost` decimal(12,2) DEFAULT NULL,
  `min_stock` int(11) NOT NULL DEFAULT 0,
  `max_stock` int(11) DEFAULT NULL,
  `reorder_point` int(11) DEFAULT NULL,
  `unit_price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `selling_price` decimal(10,2) DEFAULT NULL,
  `supplier_id` bigint(20) unsigned DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  `unit` varchar(50) DEFAULT 'pieces',
  `purchase_unit` varchar(255) DEFAULT NULL,
  `unit_conversion_factor` decimal(10,3) DEFAULT NULL,
  `expiry_date` date DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `total_value` decimal(12,2) NOT NULL DEFAULT 0.00,
  `supplier` varchar(255) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `inventory_items_category_id_foreign` (`category_id`),
  KEY `inventory_items_supplier_id_foreign` (`supplier_id`),
  KEY `inventory_items_active_index` (`active`),
  KEY `inventory_items_sku_unique` (`sku`),
  KEY `inventory_items_hotel_id_foreign` (`hotel_id`),
  CONSTRAINT `inventory_items_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `inventory_movements`;
CREATE TABLE `inventory_movements` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `inventory_item_id` bigint(20) unsigned NOT NULL,
  `movement_type` enum('in','out','adjustment','return') NOT NULL,
  `source_type` varchar(100) DEFAULT NULL,
  `source_id` bigint(20) unsigned DEFAULT NULL,
  `quantity_before` int(11) NOT NULL DEFAULT 0,
  `quantity_change` int(11) NOT NULL DEFAULT 0,
  `quantity_after` int(11) NOT NULL DEFAULT 0,
  `reference_number` varchar(255) DEFAULT NULL,
  `reason` varchar(120) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `performed_by` bigint(20) unsigned DEFAULT NULL,
  `performed_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `inventory_movements_inventory_item_id_foreign` (`inventory_item_id`),
  KEY `inventory_movements_performed_by_foreign` (`performed_by`),
  KEY `inventory_movements_hotel_id_index` (`hotel_id`),
  CONSTRAINT `inventory_movements_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE,
  CONSTRAINT `inventory_movements_inventory_item_id_foreign` FOREIGN KEY (`inventory_item_id`) REFERENCES `inventory_items` (`id`) ON DELETE CASCADE,
  CONSTRAINT `inventory_movements_performed_by_foreign` FOREIGN KEY (`performed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `inventory_order_receipts`;
CREATE TABLE `inventory_order_receipts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `inventory_order_id` bigint(20) unsigned NOT NULL,
  `inventory_item_id` bigint(20) unsigned NOT NULL,
  `accepted_quantity` int(11) NOT NULL DEFAULT 0,
  `rejected_quantity` int(11) NOT NULL DEFAULT 0,
  `damaged_quantity` int(11) NOT NULL DEFAULT 0,
  `delivery_note_number` varchar(255) DEFAULT NULL,
  `invoice_number` varchar(255) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `received_by` bigint(20) unsigned DEFAULT NULL,
  `received_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `inventory_order_receipts_inventory_order_id_foreign` (`inventory_order_id`),
  KEY `inventory_order_receipts_inventory_item_id_foreign` (`inventory_item_id`),
  KEY `inventory_order_receipts_received_by_foreign` (`received_by`),
  KEY `inventory_order_receipts_hotel_id_index` (`hotel_id`),
  CONSTRAINT `inventory_order_receipts_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE,
  CONSTRAINT `inventory_order_receipts_inventory_item_id_foreign` FOREIGN KEY (`inventory_item_id`) REFERENCES `inventory_items` (`id`) ON DELETE CASCADE,
  CONSTRAINT `inventory_order_receipts_inventory_order_id_foreign` FOREIGN KEY (`inventory_order_id`) REFERENCES `inventory_orders` (`id`) ON DELETE CASCADE,
  CONSTRAINT `inventory_order_receipts_received_by_foreign` FOREIGN KEY (`received_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `inventory_orders`;
CREATE TABLE `inventory_orders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `accounting_synced` tinyint(1) NOT NULL DEFAULT 0,
  `accounting_sync_at` timestamp NULL DEFAULT NULL,
  `accounting_synced_at` timestamp NULL DEFAULT NULL,
  `po_number` varchar(50) DEFAULT NULL,
  `item_id` bigint(20) unsigned NOT NULL,
  `vendor_id` bigint(20) unsigned NOT NULL,
  `quantity` int(11) NOT NULL,
  `purchase_quantity` decimal(10,3) DEFAULT NULL,
  `purchase_unit` varchar(255) DEFAULT NULL,
  `stock_unit` varchar(255) DEFAULT NULL,
  `unit_conversion_factor` decimal(10,3) DEFAULT NULL,
  `unit_price` decimal(10,2) DEFAULT 0.00,
  `total_amount` decimal(12,2) DEFAULT 0.00,
  `urgency` enum('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
  `delivery_date` date DEFAULT NULL,
  `expected_delivery` date DEFAULT NULL,
  `received_date` date DEFAULT NULL,
  `received_quantity` int(11) DEFAULT 0,
  `returned_quantity` int(11) NOT NULL DEFAULT 0,
  `returned_at` timestamp NULL DEFAULT NULL,
  `invoice_number` varchar(100) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `terms` text DEFAULT NULL,
  `ordered_by` bigint(20) unsigned DEFAULT NULL,
  `status` enum('pending','ordered','received','cancelled') NOT NULL DEFAULT 'pending',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `po_number` (`po_number`),
  KEY `inventory_orders_item_id_foreign` (`item_id`),
  KEY `inventory_orders_vendor_id_foreign` (`vendor_id`),
  KEY `ordered_by` (`ordered_by`),
  KEY `inventory_orders_hotel_id_index` (`hotel_id`),
  CONSTRAINT `inventory_orders_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE,
  CONSTRAINT `inventory_orders_ibfk_1` FOREIGN KEY (`ordered_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `inventory_orders_item_id_foreign` FOREIGN KEY (`item_id`) REFERENCES `inventory_items` (`id`),
  CONSTRAINT `inventory_orders_vendor_id_foreign` FOREIGN KEY (`vendor_id`) REFERENCES `contacts` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `inventory_transactions`;
CREATE TABLE `inventory_transactions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `product_id` bigint(20) unsigned NOT NULL,
  `quantity` decimal(10,3) NOT NULL,
  `type` enum('purchase','sale','adjustment','bom_consumption','production') NOT NULL,
  `reference_id` varchar(255) DEFAULT NULL,
  `reference_type` varchar(255) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `inventory_transactions_product_id_foreign` (`product_id`),
  KEY `inventory_transactions_created_by_foreign` (`created_by`),
  KEY `inventory_transactions_hotel_id_foreign` (`hotel_id`),
  CONSTRAINT `inventory_transactions_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `invoice_accounts`;
CREATE TABLE `invoice_accounts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned DEFAULT NULL,
  `invoice_id` bigint(20) unsigned NOT NULL,
  `account_id` bigint(20) unsigned NOT NULL,
  `amount` decimal(15,2) NOT NULL DEFAULT 0.00,
  `type` enum('revenue','tax','discount','other') DEFAULT 'revenue',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `invoice_accounts_invoice_id_index` (`invoice_id`),
  KEY `invoice_accounts_account_id_index` (`account_id`),
  KEY `invoice_accounts_hotel_id_index` (`hotel_id`),
  CONSTRAINT `invoice_accounts_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `invoice_items`;
CREATE TABLE `invoice_items` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `invoice_id` bigint(20) unsigned NOT NULL,
  `description` varchar(255) NOT NULL,
  `quantity` int(11) NOT NULL DEFAULT 1,
  `unit_price` decimal(12,2) NOT NULL,
  `total_price` decimal(12,2) NOT NULL,
  `tax_rate` decimal(5,2) NOT NULL DEFAULT 0.00,
  `tax_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `invoice_items_invoice_id_index` (`invoice_id`),
  KEY `invoice_items_hotel_id_foreign` (`hotel_id`),
  CONSTRAINT `invoice_items_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `invoices`;
CREATE TABLE `invoices` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `reservation_id` bigint(20) unsigned DEFAULT NULL,
  `service_request_id` bigint(20) unsigned DEFAULT NULL,
  `invoice_number` varchar(50) NOT NULL,
  `type` enum('sales','purchase','room_charge','service','other') NOT NULL,
  `date` date NOT NULL,
  `due_date` date NOT NULL,
  `contact_type` enum('App\\Models\\Guest','App\\Models\\Vendor','App\\Models\\Company') DEFAULT NULL,
  `contact_id` bigint(20) unsigned NOT NULL,
  `guest_id` bigint(20) unsigned DEFAULT NULL,
  `account_id` bigint(20) unsigned DEFAULT NULL,
  `total_amount` decimal(15,2) NOT NULL,
  `subtotal` decimal(15,2) DEFAULT 0.00,
  `currency` varchar(10) DEFAULT 'UGX',
  `exchange_rate` decimal(10,4) DEFAULT 1.0000,
  `notes` text DEFAULT NULL,
  `description` text DEFAULT NULL,
  `payment_status` enum('pending','paid','partially_paid','overdue','cancelled') DEFAULT 'pending',
  `installment_payment_id` bigint(20) unsigned DEFAULT NULL,
  `tax_amount` decimal(15,2) DEFAULT 0.00,
  `amount_paid` decimal(10,2) NOT NULL DEFAULT 0.00,
  `balance_due` decimal(10,2) NOT NULL DEFAULT 0.00,
  `status` enum('draft','sent','paid','overdue','cancelled') DEFAULT 'draft',
  `paid_at` timestamp NULL DEFAULT NULL,
  `received_by` bigint(20) unsigned DEFAULT NULL,
  `created_by` bigint(20) unsigned DEFAULT NULL,
  `module_source` varchar(50) DEFAULT NULL,
  `source_id` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `synced_to_accounting` tinyint(1) DEFAULT 0,
  `synced_at` timestamp NULL DEFAULT NULL,
  `payment_method` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `invoice_number` (`invoice_number`),
  KEY `invoices_payment_status_index` (`payment_status`),
  KEY `invoices_installment_payment_id_index` (`installment_payment_id`),
  KEY `invoices_reservation_id_index` (`reservation_id`),
  KEY `idx_service_request_id` (`service_request_id`),
  KEY `idx_guest_id` (`guest_id`),
  KEY `invoices_account_id_index` (`account_id`),
  KEY `invoices_hotel_id_index` (`hotel_id`),
  KEY `idx_payment_status` (`payment_status`),
  KEY `idx_balance_due` (`balance_due`),
  CONSTRAINT `invoices_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `job_batches`;
CREATE TABLE `job_batches` (
  `id` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `total_jobs` int(11) NOT NULL,
  `pending_jobs` int(11) NOT NULL,
  `failed_jobs` int(11) NOT NULL,
  `failed_job_ids` longtext NOT NULL,
  `options` mediumtext DEFAULT NULL,
  `cancelled_at` int(11) DEFAULT NULL,
  `created_at` int(11) NOT NULL,
  `finished_at` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `jobs`;
CREATE TABLE `jobs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `queue` varchar(255) NOT NULL,
  `payload` longtext NOT NULL,
  `attempts` tinyint(3) unsigned NOT NULL,
  `reserved_at` int(10) unsigned DEFAULT NULL,
  `available_at` int(10) unsigned NOT NULL,
  `created_at` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `jobs_queue_index` (`queue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `journal_entries`;
CREATE TABLE `journal_entries` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned DEFAULT NULL,
  `date` date NOT NULL,
  `entry_number` varchar(255) NOT NULL,
  `je_id_reference` varchar(50) DEFAULT NULL COMMENT 'Reference to template JE ID (e.g., JE-001)',
  `reference` varchar(255) DEFAULT NULL,
  `description` text NOT NULL,
  `total_amount` decimal(15,2) NOT NULL DEFAULT 0.00,
  `currency_code` varchar(3) NOT NULL DEFAULT 'UGX',
  `exchange_rate` decimal(10,4) NOT NULL DEFAULT 1.0000,
  `status` enum('draft','posted','pending','cancelled') NOT NULL DEFAULT 'draft',
  `module_source` varchar(255) DEFAULT NULL,
  `source_id` bigint(20) unsigned DEFAULT NULL,
  `posted_at` timestamp NULL DEFAULT NULL,
  `company_id` bigint(20) unsigned DEFAULT NULL,
  `fiscal_year_id` bigint(20) unsigned DEFAULT NULL,
  `created_by` bigint(20) unsigned NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `journal_entries_entry_number_unique` (`entry_number`),
  UNIQUE KEY `journal_entries_source_unique` (`hotel_id`,`module_source`,`source_id`),
  KEY `journal_entries_hotel_id_index` (`hotel_id`),
  KEY `journal_entries_date_index` (`date`),
  KEY `journal_entries_status_index` (`status`),
  KEY `journal_entries_created_by_foreign` (`created_by`),
  KEY `journal_entries_module_source_source_id_index` (`module_source`,`source_id`),
  KEY `journal_entries_company_id_index` (`company_id`),
  KEY `journal_entries_fiscal_year_id_index` (`fiscal_year_id`),
  KEY `journal_entries_posted_at_index` (`posted_at`),
  KEY `journal_entries_module_source_index` (`module_source`),
  CONSTRAINT `journal_entries_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `journal_entry_items`;
CREATE TABLE `journal_entry_items` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned DEFAULT NULL,
  `journal_entry_id` bigint(20) unsigned NOT NULL,
  `account_id` bigint(20) unsigned NOT NULL,
  `debit` decimal(15,2) NOT NULL DEFAULT 0.00,
  `credit` decimal(15,2) NOT NULL DEFAULT 0.00,
  `description` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `journal_entry_items_hotel_id_index` (`hotel_id`),
  KEY `journal_entry_items_account_id_index` (`account_id`),
  KEY `journal_entry_items_journal_entry_id_foreign` (`journal_entry_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `journal_sequences`;
CREATE TABLE `journal_sequences` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `prefix` varchar(255) NOT NULL,
  `year_month` varchar(255) DEFAULT NULL,
  `last_value` int(10) unsigned NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `journal_sequences_hotel_id_prefix_year_month_unique` (`hotel_id`,`prefix`,`year_month`),
  CONSTRAINT `journal_sequences_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `journal_sequences` (`id`, `hotel_id`, `prefix`, `year_month`, `last_value`, `created_at`, `updated_at`) VALUES ('2', '1', 'SRV', '202605', '1', '2026-05-25 14:38:16', '2026-05-25 14:38:16');

DROP TABLE IF EXISTS `journal_transactions`;
CREATE TABLE `journal_transactions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned DEFAULT NULL,
  `journal_entry_id` bigint(20) unsigned NOT NULL,
  `account_id` bigint(20) unsigned NOT NULL,
  `debit` decimal(15,2) NOT NULL DEFAULT 0.00,
  `credit` decimal(15,2) NOT NULL DEFAULT 0.00,
  `description` text DEFAULT NULL,
  `line_number` int(10) unsigned NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `journal_transactions_journal_entry_id_foreign` (`journal_entry_id`),
  KEY `journal_transactions_account_id_foreign` (`account_id`),
  KEY `journal_transactions_hotel_id_index` (`hotel_id`),
  CONSTRAINT `journal_transactions_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `journal_vouchers`;
CREATE TABLE `journal_vouchers` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned DEFAULT NULL,
  `voucher_number` varchar(100) NOT NULL,
  `voucher_date` date NOT NULL,
  `description` text NOT NULL,
  `debit_account_id` bigint(20) unsigned NOT NULL,
  `credit_account_id` bigint(20) unsigned NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `reference` varchar(100) DEFAULT NULL,
  `status` enum('draft','pending','approved','cancelled','posted') NOT NULL DEFAULT 'draft',
  `prepared_by` bigint(20) unsigned NOT NULL,
  `approved_by` bigint(20) unsigned DEFAULT NULL,
  `approved_at` timestamp NULL DEFAULT NULL,
  `cancelled_by` bigint(20) unsigned DEFAULT NULL,
  `cancelled_at` timestamp NULL DEFAULT NULL,
  `remarks` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `journal_vouchers_voucher_number_unique` (`voucher_number`),
  KEY `journal_vouchers_voucher_date_index` (`voucher_date`),
  KEY `journal_vouchers_status_index` (`status`),
  KEY `journal_vouchers_voucher_date_status_index` (`voucher_date`,`status`),
  KEY `journal_vouchers_debit_account_id_index` (`debit_account_id`),
  KEY `journal_vouchers_credit_account_id_index` (`credit_account_id`),
  KEY `journal_vouchers_prepared_by_index` (`prepared_by`),
  KEY `journal_vouchers_approved_by_index` (`approved_by`),
  KEY `journal_vouchers_cancelled_by_index` (`cancelled_by`),
  KEY `journal_vouchers_hotel_id_index` (`hotel_id`),
  CONSTRAINT `journal_vouchers_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `leads`;
CREATE TABLE `leads` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned DEFAULT NULL,
  `full_name` varchar(100) NOT NULL,
  `job_title` varchar(100) DEFAULT NULL,
  `email` varchar(100) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `hotel_name` varchar(100) DEFAULT NULL,
  `rooms_count` int(11) DEFAULT NULL,
  `hotel_type` varchar(50) DEFAULT NULL,
  `years_operation` varchar(20) DEFAULT NULL,
  `challenges_ranking` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`challenges_ranking`)),
  `goals_ranking` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`goals_ranking`)),
  `source` varchar(50) DEFAULT NULL,
  `interest_type` varchar(50) DEFAULT NULL,
  `contact_method` varchar(20) DEFAULT NULL,
  `comments` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_email` (`email`),
  KEY `idx_created` (`created_at`),
  KEY `leads_hotel_id_index` (`hotel_id`),
  CONSTRAINT `leads_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `leave_balances`;
CREATE TABLE `leave_balances` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `staff_id` bigint(20) unsigned NOT NULL,
  `leave_type_id` bigint(20) unsigned NOT NULL,
  `year` int(11) NOT NULL,
  `total_days` decimal(5,1) DEFAULT 0.0,
  `used_days` decimal(5,1) DEFAULT 0.0,
  `carry_forward_days` decimal(5,1) DEFAULT 0.0,
  `pending_days` decimal(5,1) DEFAULT 0.0,
  `extra_days` decimal(5,1) DEFAULT 0.0,
  `balance_days` decimal(5,1) DEFAULT 0.0,
  `last_updated` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `leave_balances_staff_id_leave_type_id_year_unique` (`staff_id`,`leave_type_id`,`year`),
  KEY `leave_balances_staff_id_year_index` (`staff_id`,`year`),
  KEY `leave_balances_leave_type_id_foreign` (`leave_type_id`),
  KEY `leave_balances_hotel_id_foreign` (`hotel_id`),
  CONSTRAINT `leave_balances_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `leave_types`;
CREATE TABLE `leave_types` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `code` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `max_days_per_year` int(11) NOT NULL DEFAULT 0,
  `max_consecutive_days` int(11) NOT NULL DEFAULT 0,
  `carry_forward_enabled` tinyint(1) NOT NULL DEFAULT 0,
  `max_carry_forward_days` int(11) NOT NULL DEFAULT 0,
  `requires_approval` tinyint(1) NOT NULL DEFAULT 1,
  `approval_level` enum('immediate_supervisor','department_head','hr','management') NOT NULL DEFAULT 'immediate_supervisor',
  `gender_specific` tinyint(1) NOT NULL DEFAULT 0,
  `gender` enum('male','female') DEFAULT NULL,
  `min_service_days` int(11) NOT NULL DEFAULT 0,
  `max_balance` int(11) NOT NULL DEFAULT 365,
  `encashment_enabled` tinyint(1) NOT NULL DEFAULT 0,
  `encashment_percentage` decimal(5,2) NOT NULL DEFAULT 0.00,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `color` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `leave_types_code_unique` (`code`),
  KEY `leave_types_hotel_id_foreign` (`hotel_id`),
  CONSTRAINT `leave_types_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `leaves`;
CREATE TABLE `leaves` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `staff_id` bigint(20) unsigned NOT NULL,
  `leave_type_id` bigint(20) unsigned NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `duration` decimal(3,1) NOT NULL,
  `reason` text DEFAULT NULL,
  `status` enum('pending','approved','rejected','cancelled') NOT NULL DEFAULT 'pending',
  `approved_by` bigint(20) unsigned DEFAULT NULL,
  `approver_notes` text DEFAULT NULL,
  `approved_at` timestamp NULL DEFAULT NULL,
  `rejected_by` bigint(20) unsigned DEFAULT NULL,
  `rejection_reason` text DEFAULT NULL,
  `rejected_at` timestamp NULL DEFAULT NULL,
  `emergency_contact` varchar(255) DEFAULT NULL,
  `attachment` varchar(255) DEFAULT NULL,
  `is_half_day` tinyint(1) NOT NULL DEFAULT 0,
  `half_day_type` enum('first_half','second_half') DEFAULT NULL,
  `carry_forward_days` int(11) NOT NULL DEFAULT 0,
  `credit_type` enum('new','carry_forward','extra') NOT NULL DEFAULT 'new',
  `created_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `leaves_staff_id_status_index` (`staff_id`,`status`),
  KEY `leaves_start_date_index` (`start_date`),
  KEY `leaves_end_date_index` (`end_date`),
  KEY `leaves_staff_id_index` (`staff_id`),
  KEY `leaves_leave_type_id_index` (`leave_type_id`),
  KEY `leaves_approved_by_index` (`approved_by`),
  KEY `leaves_rejected_by_index` (`rejected_by`),
  KEY `leaves_created_by_index` (`created_by`),
  KEY `leaves_hotel_id_foreign` (`hotel_id`),
  CONSTRAINT `leaves_approved_by_foreign` FOREIGN KEY (`approved_by`) REFERENCES `staff` (`id`) ON DELETE SET NULL,
  CONSTRAINT `leaves_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `staff` (`id`) ON DELETE CASCADE,
  CONSTRAINT `leaves_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE,
  CONSTRAINT `leaves_leave_type_id_foreign` FOREIGN KEY (`leave_type_id`) REFERENCES `leave_types` (`id`) ON DELETE CASCADE,
  CONSTRAINT `leaves_rejected_by_foreign` FOREIGN KEY (`rejected_by`) REFERENCES `staff` (`id`) ON DELETE SET NULL,
  CONSTRAINT `leaves_staff_id_foreign` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `maintenance_requests`;
CREATE TABLE `maintenance_requests` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `request_number` varchar(255) NOT NULL,
  `room_id` bigint(20) unsigned NOT NULL,
  `issue_category` varchar(255) NOT NULL,
  `issue_description` text NOT NULL,
  `priority` enum('low','medium','high') NOT NULL DEFAULT 'medium',
  `status` enum('pending','in_progress','completed','cancelled') NOT NULL DEFAULT 'pending',
  `reported_by` bigint(20) unsigned NOT NULL,
  `assigned_to` bigint(20) unsigned DEFAULT NULL,
  `completed_at` timestamp NULL DEFAULT NULL,
  `resolution_notes` text DEFAULT NULL,
  `image_path` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `maintenance_requests_hotel_id_index` (`hotel_id`),
  CONSTRAINT `maintenance_requests_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `maintenances`;
CREATE TABLE `maintenances` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `room_id` bigint(20) unsigned DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  `assigned_to` bigint(20) unsigned DEFAULT NULL,
  `reported_by` bigint(20) unsigned NOT NULL,
  `priority` enum('low','medium','high','urgent') NOT NULL DEFAULT 'medium',
  `status` enum('pending','in_progress','completed','cancelled') NOT NULL DEFAULT 'pending',
  `reported_date` date NOT NULL,
  `scheduled_date` date DEFAULT NULL,
  `started_date` datetime DEFAULT NULL,
  `completed_date` date DEFAULT NULL,
  `estimated_cost` decimal(10,2) DEFAULT NULL,
  `actual_cost` decimal(10,2) DEFAULT NULL,
  `estimated_duration` int(11) DEFAULT NULL COMMENT 'Duration in minutes',
  `actual_duration` int(11) DEFAULT NULL COMMENT 'Duration in minutes',
  `materials_used` text DEFAULT NULL COMMENT 'JSON array or text of materials used',
  `resolution_notes` text DEFAULT NULL,
  `maintenance_type` varchar(255) NOT NULL DEFAULT 'repair',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `maintenances_room_id_foreign` (`room_id`),
  KEY `maintenances_assigned_to_foreign` (`assigned_to`),
  KEY `maintenances_reported_by_foreign` (`reported_by`),
  KEY `maintenances_hotel_id_foreign` (`hotel_id`),
  CONSTRAINT `maintenances_assigned_to_foreign` FOREIGN KEY (`assigned_to`) REFERENCES `staff` (`id`) ON DELETE SET NULL,
  CONSTRAINT `maintenances_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE,
  CONSTRAINT `maintenances_reported_by_foreign` FOREIGN KEY (`reported_by`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `maintenances_room_id_foreign` FOREIGN KEY (`room_id`) REFERENCES `rooms` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `maintenances` (`id`, `hotel_id`, `title`, `description`, `room_id`, `location`, `assigned_to`, `reported_by`, `priority`, `status`, `reported_date`, `scheduled_date`, `started_date`, `completed_date`, `estimated_cost`, `actual_cost`, `estimated_duration`, `actual_duration`, `materials_used`, `resolution_notes`, `maintenance_type`, `created_at`, `updated_at`) VALUES ('1', '1', 'bulb', NULL, '1', 'Room 001', NULL, '2', 'low', 'pending', '2026-05-25', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'repair', '2026-05-25 14:02:54', '2026-05-25 14:02:54');

DROP TABLE IF EXISTS `manufacturing_consumptions`;
CREATE TABLE `manufacturing_consumptions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `manufacturing_order_id` bigint(20) NOT NULL,
  `product_id` bigint(20) unsigned NOT NULL,
  `quantity_used` decimal(15,3) NOT NULL,
  `unit_cost` decimal(15,2) NOT NULL,
  `total_cost` decimal(15,2) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `manufacturing_order_id` (`manufacturing_order_id`),
  KEY `product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `manufacturing_orders`;
CREATE TABLE `manufacturing_orders` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `reference` varchar(100) NOT NULL,
  `product_id` bigint(20) NOT NULL,
  `bom_id` bigint(20) unsigned DEFAULT NULL,
  `product_name` varchar(255) NOT NULL,
  `quantity_planned` decimal(10,2) NOT NULL,
  `quantity_produced` decimal(10,2) DEFAULT 0.00,
  `unit_of_measure` varchar(50) DEFAULT 'units',
  `state` enum('draft','confirmed','progress','done','cancel') DEFAULT 'draft',
  `priority` enum('0','1','2','3') DEFAULT '0',
  `date_planned_start` datetime DEFAULT NULL,
  `date_planned_finished` datetime DEFAULT NULL,
  `date_start` datetime DEFAULT NULL,
  `date_finished` datetime DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` bigint(20) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `accounting_synced` tinyint(1) NOT NULL DEFAULT 0,
  `accounting_sync_at` timestamp NULL DEFAULT NULL,
  `total_material_cost` decimal(15,2) NOT NULL DEFAULT 0.00,
  `labor_cost` decimal(15,2) NOT NULL DEFAULT 0.00,
  `total_cost` decimal(15,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`),
  UNIQUE KEY `reference` (`reference`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `manufacturing_outputs`;
CREATE TABLE `manufacturing_outputs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `manufacturing_order_id` bigint(20) NOT NULL,
  `product_id` bigint(20) unsigned NOT NULL,
  `quantity_produced` decimal(15,3) NOT NULL,
  `unit_price` decimal(15,2) NOT NULL,
  `total_value` decimal(15,2) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `manufacturing_order_id` (`manufacturing_order_id`),
  KEY `product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `menu_categories`;
CREATE TABLE `menu_categories` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `image_path` varchar(500) DEFAULT NULL,
  `meta_title` varchar(255) DEFAULT NULL,
  `meta_description` text DEFAULT NULL,
  `meta_keywords` text DEFAULT NULL,
  `parent_id` bigint(20) unsigned DEFAULT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_menu_categories_slug` (`slug`),
  KEY `idx_menu_categories_parent_id` (`parent_id`),
  KEY `menu_categories_hotel_id_index` (`hotel_id`),
  CONSTRAINT `fk_menu_categories_parent` FOREIGN KEY (`parent_id`) REFERENCES `menu_categories` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `menu_categories_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `menu_categories` (`id`, `hotel_id`, `name`, `slug`, `description`, `image_path`, `meta_title`, `meta_description`, `meta_keywords`, `parent_id`, `sort_order`, `is_active`, `created_at`, `updated_at`) VALUES ('1', '1', 'yuiujhj', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '0', '1', '2026-05-25 15:15:36', '2026-05-25 15:15:36');

DROP TABLE IF EXISTS `menu_items`;
CREATE TABLE `menu_items` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `price` decimal(8,2) NOT NULL,
  `category_id` bigint(20) unsigned NOT NULL,
  `is_available` tinyint(1) NOT NULL DEFAULT 1,
  `is_featured` tinyint(1) NOT NULL DEFAULT 0,
  `preparation_time` int(11) DEFAULT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `menu_items_hotel_id_index` (`hotel_id`),
  CONSTRAINT `menu_items_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `menu_items` (`id`, `hotel_id`, `name`, `description`, `image`, `price`, `category_id`, `is_available`, `is_featured`, `preparation_time`, `sort_order`, `created_at`, `updated_at`) VALUES ('1', '1', 'bhjjnknm', NULL, 'menu-items/3oDXdxCEwon7lVyXacrWyWlTHqU9YdFi42k6wKJv.png', '5000.00', '1', '1', '0', '15', '0', '2026-05-25 15:15:55', '2026-05-25 15:15:55');

DROP TABLE IF EXISTS `migrations`;
CREATE TABLE `migrations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `migration` varchar(255) NOT NULL,
  `batch` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=110 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('1', '0001_01_01_000000_create_users_table', '1');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('2', '0001_01_01_000001_create_cache_table', '1');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('3', '0001_01_01_000002_create_jobs_table', '1');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('4', '2025_09_15_085047_create_guests_table', '1');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('5', '2025_09_15_085111_create_services_table', '1');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('6', '2025_09_15_085121_create_bookings_table', '1');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('7', '2025_09_15_085132_create_payments_table', '1');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('8', '2025_09_15_085143_create_staff_table', '1');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('9', '2025_09_15_092712_create_room_types_table', '1');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('10', '2025_09_15_164433_create_personal_access_tokens_table', '1');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('11', '2025_09_15_210740_create_room_amenities_table', '1');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('12', '2025_09_15_210751_create_room_maintenance_table', '1');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('13', '2025_09_15_210802_create_housekeeping_schedule_table', '1');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('14', '2025_09_16_080433_create_technicians_table', '2');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('15', '2025_09_16_080449_create_equipment_table', '2');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('16', '2025_09_16_080505_create_preventive_maintenance_table', '2');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('17', '2025_09_18_080520_create_rooms_table', '2');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('18', '2025_09_18_150524_create_services_logs_table', '2');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('19', '2025_09_18_203801_add_status_to_guests_table', '2');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('20', '2025_09_19_080418_create_maintenance_requests_table', '2');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('21', '2025_09_19_085058_create_reservations_table', '2');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('22', '2025_09_19_114345_create_inventory_items_table', '2');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('23', '2025_09_19_134308_create_housekeeping_staff_table', '2');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('24', '2025_09_20_084203_add_housekeeping_status_to_rooms_table', '2');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('25', '2025_09_20_101439_create_expenses_table', '2');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('26', '2025_09_20_110223_create_contacts_table', '2');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('27', '2025_09_20_124342_create_documents_table', '2');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('28', '2025_09_20_135543_create_surveys_table', '2');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('29', '2025_09_20_170814_create_settings_table', '2');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('30', '2025_09_22_141534_create_maintenances_table', '2');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('31', '2025_09_24_191303_fix_pending_migrations', '2');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('32', '2025_09_17_100109_create_menu_items_table', '3');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('33', '2025_09_18_163731_create_pos_tables', '3');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('34', '2025_09_18_191828_add_first_name_last_name_phone_role_to_users_table', '3');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('35', '2025_09_18_202837_add_is_available_to_rooms_table', '3');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('36', '2025_09_19_083157_fix_maintenance_requests_table_foreign_keys', '3');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('37', '2025_09_19_134259_create_housekeeping_tasks_table', '3');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('38', '2025_09_20_120613_create_in_out_logs_table', '3');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('39', '2025_09_20_173700_create_menu_categories_table', '3');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('40', '2025_09_21_090324_create_activities_table', '3');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('41', '2025_09_24_191603_mark_all_migrations_completed', '2');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('42', '2025_09_24_120033_create_menu_categories_table', '4');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('43', '2025_09_26_173247_add_image_to_menu_items_table', '5');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('44', '2025_10_04_074816_create_order_items_table', '6');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('45', '2025_10_17_112313_create_document_folders_table', '6');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('46', '2025_10_25_071436_create_inventory_orders_table', '7');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('48', '2025_10_26_081856_create_inventory_transactions_table', '8');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('49', '2025_11_06_191840_add_accounting_sync_columns', '9');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('50', '2026_01_08_092845_create_attendances_table', '10');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('51', '2026_03_16_044500_create_guest_ledger_table', '11');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('52', '2026_03_16_051030_add_amount_paid_and_balance_due_to_invoices_table', '11');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('53', '2026_03_17_000000_add_room_id_to_service_requests_table', '12');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('54', '2026_03_17_000001_add_reservation_id_to_service_requests_table', '13');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('55', '2026_03_17_000002_add_hotel_id_to_service_requests_table', '14');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('56', '2026_03_17_000003_add_hotel_id_to_orders_table', '15');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('57', '2026_03_17_000004_add_hotel_id_to_orders_table', '15');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('58', '2026_03_17_000005_add_reservation_id_to_orders_table', '16');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('59', '2026_03_18_000000_add_hotel_id_to_hr_tables', '17');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('60', '2026_03_19_000000_add_hotel_id_to_fb_tables', '18');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('61', '2026_03_18_221252_add_hotel_id_to_survey_and_report_tables', '19');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('62', '2026_03_18_222241_add_hotel_id_to_inventory_and_maintenance_tables', '20');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('63', '2026_03_19_000849_add_hotel_id_to_missing_multitenancy_tables', '21');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('64', '2026_03_19_001341_add_accounting_sync_columns_to_payments_table', '22');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('65', '2026_03_19_002716_add_accounting_sync_columns_to_orders_table_v2', '23');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('66', '2026_03_19_185555_fix_guest_ledger_reference_type_enum', '24');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('67', '2026_03_19_210000_add_hotel_id_to_audit_and_request_tables', '25');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('68', '2026_03_19_220000_add_hotel_id_to_remaining_tables', '26');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('69', '2026_03_21_131500_add_hotel_id_to_transactions_table', '27');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('70', '2026_03_22_000000_add_module_isolation_fields_to_hotels', '28');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('71', '2026_03_22_215804_make_room_id_nullable_and_add_location_to_maintenances_table', '28');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('72', '2026_03_23_094916_add_checkin_checkout_logo_to_hotels_table', '28');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('73', '2026_03_23_142310_create_module_permissions_table', '28');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('74', '2026_03_23_183102_create_journal_sequences_table', '28');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('75', '2026_04_01_000000_add_request_number_to_service_requests_table', '28');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('76', '2026_04_01_000001_add_media_to_surveys_table', '28');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('77', '2026_04_01_010000_add_unit_conversion_fields_to_inventory_items_table', '28');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('78', '2026_04_01_020000_add_unit_conversion_fields_to_inventory_orders_table', '28');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('79', '2026_04_01_030000_create_inventory_issues_table', '28');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('80', '2026_04_01_040000_add_approval_fields_to_inventory_issues_table', '28');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('81', '2026_04_01_050000_create_inventory_order_receipts_table', '28');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('82', '2026_04_01_060000_add_return_tracking_to_inventory_orders_table', '28');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('83', '2026_04_01_070000_create_inventory_movements_table', '28');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('84', '2026_04_01_080000_create_payroll_runs_table', '28');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('85', '2026_04_01_081000_create_payroll_items_table', '28');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('86', '2026_04_01_120000_alter_staff_status_column_for_extended_values', '29');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('87', '2026_04_02_130000_create_notifications_table', '29');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('88', '2026_04_02_220000_add_unique_source_key_to_journal_entries_table', '29');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('89', '2026_04_22_000000_add_public_booking_template_to_hotels_table', '29');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('90', '2026_04_22_100000_add_public_site_settings_to_hotels_table', '29');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('91', '2026_04_22_200000_add_public_site_media_to_hotels_table', '29');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('92', '2026_05_12_180000_create_contact_requests_table', '29');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('93', '2026_05_19_120000_add_currency_to_subscription_payments_table', '29');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('94', '2026_05_21_000000_sync_missing_tables_from_backup', '30');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('95', '2026_05_21_000001_add_hotel_id_and_login_fields_to_users_table', '30');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('96', '2026_05_21_120000_ensure_subscription_payments_table', '31');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('97', '2026_05_21_130000_add_hotel_id_to_rooms_table', '32');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('98', '2026_05_21_140000_ensure_hotel_id_on_all_tenant_tables', '33');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('99', '2026_05_21_150000_enforce_strict_hotel_id_tenancy', '34');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('100', '2026_05_22_100000_align_operational_schema', '35');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('101', '2026_05_22_110000_align_services_table_schema', '36');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('102', '2026_05_22_120000_align_guests_rooms_guest_management', '37');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('103', '2026_05_22_130000_align_room_management_schema', '38');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('104', '2026_05_22_140000_align_accounting_schema', '39');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('105', '2026_05_22_150000_align_documents_schema', '40');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('106', '2026_05_22_160000_align_reports_schema', '41');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('107', '2026_05_24_120000_fix_staff_survey_documents_schema', '42');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('108', '2026_05_24_150000_add_amount_ugx_to_subscription_payments', '43');
INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES ('109', '2026_05_25_150000_ensure_request_number_on_service_requests', '44');

DROP TABLE IF EXISTS `mo_components`;
CREATE TABLE `mo_components` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `mo_id` bigint(20) NOT NULL,
  `product_id` bigint(20) NOT NULL,
  `product_name` varchar(255) NOT NULL,
  `product_qty` decimal(10,2) NOT NULL,
  `unit_of_measure` varchar(50) DEFAULT 'units',
  `qty_consumed` decimal(10,2) DEFAULT 0.00,
  `qty_available` decimal(10,2) DEFAULT 0.00,
  `state` enum('draft','assigned','done') DEFAULT 'draft',
  PRIMARY KEY (`id`),
  KEY `mo_id` (`mo_id`),
  CONSTRAINT `mo_components_ibfk_1` FOREIGN KEY (`mo_id`) REFERENCES `manufacturing_orders` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `module_permissions`;
CREATE TABLE `module_permissions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `role` varchar(255) NOT NULL,
  `module` varchar(255) NOT NULL,
  `can_view` tinyint(1) NOT NULL DEFAULT 0,
  `can_create` tinyint(1) NOT NULL DEFAULT 0,
  `can_edit` tinyint(1) NOT NULL DEFAULT 0,
  `can_delete` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `module_permissions_role_module_unique` (`role`,`module`)
) ENGINE=InnoDB AUTO_INCREMENT=88 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('1', 'director', 'rooms', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('2', 'director', 'reservations', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('3', 'director', 'guests', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('4', 'director', 'front_desk', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('5', 'director', 'hotel_services', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('6', 'director', 'housekeeping', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('7', 'director', 'maintenance', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('8', 'director', 'inventory', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('9', 'director', 'accounting', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('10', 'director', 'hr', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('11', 'director', 'documents', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('12', 'director', 'reporting', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('13', 'director', 'survey', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('14', 'director', 'food_beverage', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('15', 'director', 'settings', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('16', 'admin', 'rooms', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('17', 'admin', 'reservations', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('18', 'admin', 'guests', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('19', 'admin', 'front_desk', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('20', 'admin', 'hotel_services', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('21', 'admin', 'housekeeping', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('22', 'admin', 'maintenance', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('23', 'admin', 'inventory', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('24', 'admin', 'accounting', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('25', 'admin', 'hr', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('26', 'admin', 'documents', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('27', 'admin', 'reporting', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('28', 'admin', 'survey', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('29', 'admin', 'food_beverage', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('30', 'admin', 'settings', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('31', 'administrator', 'rooms', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('32', 'administrator', 'reservations', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('33', 'administrator', 'guests', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('34', 'administrator', 'front_desk', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('35', 'administrator', 'hotel_services', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('36', 'administrator', 'housekeeping', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('37', 'administrator', 'maintenance', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('38', 'administrator', 'inventory', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('39', 'administrator', 'accounting', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('40', 'administrator', 'hr', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('41', 'administrator', 'documents', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('42', 'administrator', 'reporting', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('43', 'administrator', 'survey', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('44', 'administrator', 'food_beverage', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('45', 'administrator', 'settings', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('46', 'manager', 'rooms', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('47', 'manager', 'reservations', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('48', 'manager', 'guests', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('49', 'manager', 'front_desk', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('50', 'manager', 'hotel_services', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('51', 'manager', 'housekeeping', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('52', 'manager', 'maintenance', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('53', 'manager', 'inventory', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('54', 'manager', 'accounting', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('55', 'manager', 'hr', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('56', 'manager', 'documents', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('57', 'manager', 'reporting', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('58', 'manager', 'survey', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('59', 'manager', 'food_beverage', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('60', 'manager', 'settings', '0', '0', '0', '0', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('61', 'front_desk', 'reservations', '1', '1', '1', '0', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('62', 'front_desk', 'guests', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('63', 'front_desk', 'front_desk', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('64', 'front_desk', 'hotel_services', '1', '1', '0', '0', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('65', 'front_desk', 'housekeeping', '1', '0', '0', '0', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('66', 'front_desk', 'maintenance', '1', '1', '0', '0', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('67', 'receptionist', 'reservations', '1', '1', '1', '0', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('68', 'receptionist', 'guests', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('69', 'receptionist', 'front_desk', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('70', 'receptionist', 'hotel_services', '1', '1', '0', '0', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('71', 'receptionist', 'housekeeping', '1', '0', '0', '0', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('72', 'receptionist', 'maintenance', '1', '1', '0', '0', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('73', 'housekeeping', 'housekeeping', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('74', 'housekeeping', 'maintenance', '1', '1', '0', '0', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('75', 'maintenance', 'maintenance', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('76', 'maintenance', 'housekeeping', '1', '0', '0', '0', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('77', 'food_beverage', 'food_beverage', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('78', 'food_beverage', 'inventory', '1', '1', '1', '0', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('79', 'food_beverage', 'guests', '1', '0', '0', '0', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('80', 'accountant', 'accounting', '1', '1', '1', '1', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('81', 'accountant', 'reporting', '1', '1', '0', '0', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('82', 'accountant', 'reservations', '1', '0', '0', '0', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('83', 'accountant', 'guests', '1', '0', '0', '0', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('84', 'accountant', 'front_desk', '1', '0', '0', '0', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('85', 'accountant', 'hotel_services', '1', '0', '0', '0', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('86', 'accountant', 'inventory', '1', '0', '0', '0', '2026-05-20 23:22:31', '2026-05-20 23:22:31');
INSERT INTO `module_permissions` (`id`, `role`, `module`, `can_view`, `can_create`, `can_edit`, `can_delete`, `created_at`, `updated_at`) VALUES ('87', 'accountant', 'food_beverage', '1', '0', '0', '0', '2026-05-20 23:22:31', '2026-05-20 23:22:31');

DROP TABLE IF EXISTS `notifications`;
CREATE TABLE `notifications` (
  `id` char(36) NOT NULL,
  `hotel_id` bigint(20) unsigned DEFAULT NULL,
  `type` varchar(255) NOT NULL,
  `notifiable_type` varchar(255) NOT NULL,
  `notifiable_id` bigint(20) unsigned NOT NULL,
  `data` text NOT NULL,
  `read_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `notifications_notifiable_type_notifiable_id_index` (`notifiable_type`,`notifiable_id`),
  KEY `notifications_hotel_id_index` (`hotel_id`),
  CONSTRAINT `notifications_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `old_accounts_backup`;
CREATE TABLE `old_accounts_backup` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `code` varchar(255) NOT NULL,
  `type` enum('asset','liability','equity','revenue','income','expense') NOT NULL,
  `parent_id` bigint(20) unsigned DEFAULT NULL,
  `balance` decimal(15,2) NOT NULL DEFAULT 0.00,
  `description` text DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `accounts_code_unique` (`code`),
  KEY `fk_accounts_parent_id` (`parent_id`),
  CONSTRAINT `fk_accounts_parent_id` FOREIGN KEY (`parent_id`) REFERENCES `old_accounts_backup` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `order_items`;
CREATE TABLE `order_items` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `order_id` bigint(20) unsigned NOT NULL,
  `menu_item_id` bigint(20) unsigned NOT NULL,
  `quantity` int(11) NOT NULL,
  `unit_price` decimal(10,2) NOT NULL,
  `total_price` decimal(10,2) NOT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `order_id` (`order_id`),
  KEY `menu_item_id` (`menu_item_id`),
  KEY `order_items_hotel_id_index` (`hotel_id`),
  CONSTRAINT `order_items_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `order_number` varchar(255) DEFAULT NULL,
  `order_type` enum('room_service','restaurant','takeaway') NOT NULL,
  `room_id` bigint(20) unsigned DEFAULT NULL,
  `reservation_id` bigint(20) unsigned DEFAULT NULL,
  `guest_id` bigint(20) unsigned DEFAULT NULL,
  `table_number` varchar(255) DEFAULT NULL,
  `customer_name` varchar(255) DEFAULT NULL,
  `customer_phone` varchar(255) DEFAULT NULL,
  `special_instructions` text DEFAULT NULL,
  `priority` enum('normal','high','urgent') DEFAULT 'normal',
  `subtotal` decimal(10,2) NOT NULL,
  `tax_amount` decimal(10,2) NOT NULL,
  `service_charge` decimal(10,2) NOT NULL,
  `total_amount` decimal(10,2) NOT NULL,
  `status` enum('pending','processing','completed','cancelled') DEFAULT 'pending',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `accounting_synced` tinyint(1) NOT NULL DEFAULT 0,
  `accounting_sync_at` timestamp NULL DEFAULT NULL,
  `payment_method` varchar(50) DEFAULT 'cash',
  PRIMARY KEY (`id`),
  UNIQUE KEY `order_number` (`order_number`),
  KEY `orders_order_number_index` (`order_number`),
  KEY `orders_room_id_index` (`room_id`),
  KEY `orders_guest_id_index` (`guest_id`),
  KEY `orders_status_index` (`status`),
  KEY `orders_order_type_index` (`order_type`),
  KEY `orders_hotel_id_foreign` (`hotel_id`),
  KEY `orders_reservation_id_foreign` (`reservation_id`),
  CONSTRAINT `orders_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE,
  CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`room_id`) REFERENCES `rooms` (`id`) ON DELETE SET NULL,
  CONSTRAINT `orders_ibfk_2` FOREIGN KEY (`guest_id`) REFERENCES `guests` (`id`) ON DELETE SET NULL,
  CONSTRAINT `orders_reservation_id_foreign` FOREIGN KEY (`reservation_id`) REFERENCES `reservations` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `page_views`;
CREATE TABLE `page_views` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned DEFAULT NULL,
  `page_url` varchar(255) NOT NULL,
  `visitor_ip` varchar(45) NOT NULL,
  `user_agent` text DEFAULT NULL,
  `session_id` varchar(255) DEFAULT NULL,
  `viewed_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_session` (`session_id`),
  KEY `idx_date` (`viewed_at`),
  KEY `page_views_hotel_id_index` (`hotel_id`),
  CONSTRAINT `page_views_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `password_reset_tokens`;
CREATE TABLE `password_reset_tokens` (
  `email` varchar(255) NOT NULL,
  `token` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `payment_vouchers`;
CREATE TABLE `payment_vouchers` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned DEFAULT NULL,
  `voucher_number` varchar(100) NOT NULL,
  `voucher_date` date NOT NULL,
  `vendor_id` bigint(20) unsigned NOT NULL,
  `account_id` bigint(20) unsigned NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `payment_method` enum('cash','bank_transfer','cheque') NOT NULL,
  `description` text DEFAULT NULL,
  `status` enum('pending','approved','paid','cancelled') NOT NULL DEFAULT 'pending',
  `prepared_by` bigint(20) unsigned DEFAULT NULL,
  `approved_by` bigint(20) unsigned DEFAULT NULL,
  `approved_at` timestamp NULL DEFAULT NULL,
  `cancelled_by` bigint(20) unsigned DEFAULT NULL,
  `cancelled_at` timestamp NULL DEFAULT NULL,
  `created_by` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `payment_vouchers_voucher_number_unique` (`voucher_number`),
  KEY `payment_vouchers_vendor_id_foreign` (`vendor_id`),
  KEY `payment_vouchers_account_id_foreign` (`account_id`),
  KEY `payment_vouchers_prepared_by_foreign` (`prepared_by`),
  KEY `payment_vouchers_approved_by_foreign` (`approved_by`),
  KEY `payment_vouchers_cancelled_by_foreign` (`cancelled_by`),
  KEY `payment_vouchers_created_by_foreign` (`created_by`),
  KEY `payment_vouchers_hotel_id_index` (`hotel_id`),
  CONSTRAINT `payment_vouchers_account_id_foreign` FOREIGN KEY (`account_id`) REFERENCES `old_accounts_backup` (`id`) ON DELETE CASCADE,
  CONSTRAINT `payment_vouchers_approved_by_foreign` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `payment_vouchers_cancelled_by_foreign` FOREIGN KEY (`cancelled_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `payment_vouchers_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `payment_vouchers_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE SET NULL,
  CONSTRAINT `payment_vouchers_prepared_by_foreign` FOREIGN KEY (`prepared_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `payment_vouchers_vendor_id_foreign` FOREIGN KEY (`vendor_id`) REFERENCES `contacts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `payments`;
CREATE TABLE `payments` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `payment_number` varchar(50) NOT NULL,
  `invoice_id` bigint(20) unsigned NOT NULL,
  `reservation_id` bigint(20) unsigned DEFAULT NULL,
  `amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `payment_date` datetime DEFAULT NULL,
  `receipt_number` varchar(50) DEFAULT NULL,
  `payment_method` varchar(50) NOT NULL DEFAULT 'cash',
  `reference` varchar(255) DEFAULT NULL,
  `transaction_id` varchar(255) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `received_by` bigint(20) unsigned DEFAULT NULL,
  `created_by` bigint(20) unsigned DEFAULT NULL,
  `status` enum('pending','completed','failed','refunded','cancelled') NOT NULL DEFAULT 'pending',
  `paid_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `accounting_synced` tinyint(1) NOT NULL DEFAULT 0,
  `accounting_sync_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `payments_payment_number_unique` (`payment_number`),
  KEY `payments_invoice_id_foreign` (`invoice_id`),
  KEY `payments_status_index` (`status`),
  KEY `payments_paid_at_index` (`paid_at`),
  KEY `payments_payment_date_index` (`payment_date`),
  KEY `payments_received_by_foreign` (`received_by`),
  KEY `payments_created_by_foreign` (`created_by`),
  KEY `payments_reservation_id_foreign` (`reservation_id`),
  KEY `payments_hotel_id_index` (`hotel_id`),
  CONSTRAINT `payments_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `payroll_items`;
CREATE TABLE `payroll_items` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `payroll_run_id` bigint(20) unsigned NOT NULL,
  `staff_id` bigint(20) unsigned NOT NULL,
  `gross_salary` decimal(14,2) NOT NULL DEFAULT 0.00,
  `allowances` decimal(14,2) NOT NULL DEFAULT 0.00,
  `deductions` decimal(14,2) NOT NULL DEFAULT 0.00,
  `net_salary` decimal(14,2) NOT NULL DEFAULT 0.00,
  `status` enum('pending','paid') NOT NULL DEFAULT 'pending',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `payroll_items_payroll_run_id_staff_id_unique` (`payroll_run_id`,`staff_id`),
  KEY `payroll_items_staff_id_foreign` (`staff_id`),
  KEY `payroll_items_hotel_id_index` (`hotel_id`),
  CONSTRAINT `payroll_items_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE,
  CONSTRAINT `payroll_items_payroll_run_id_foreign` FOREIGN KEY (`payroll_run_id`) REFERENCES `payroll_runs` (`id`) ON DELETE CASCADE,
  CONSTRAINT `payroll_items_staff_id_foreign` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `payroll_runs`;
CREATE TABLE `payroll_runs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `run_number` varchar(255) NOT NULL,
  `pay_month` tinyint(3) unsigned NOT NULL,
  `pay_year` smallint(5) unsigned NOT NULL,
  `gross_total` decimal(14,2) NOT NULL DEFAULT 0.00,
  `net_total` decimal(14,2) NOT NULL DEFAULT 0.00,
  `status` enum('draft','approved','paid') NOT NULL DEFAULT 'draft',
  `paid_date` date DEFAULT NULL,
  `payment_method` varchar(255) DEFAULT NULL,
  `reference_number` varchar(255) DEFAULT NULL,
  `created_by` bigint(20) unsigned DEFAULT NULL,
  `paid_by` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `payroll_runs_run_number_unique` (`run_number`),
  KEY `payroll_runs_created_by_foreign` (`created_by`),
  KEY `payroll_runs_paid_by_foreign` (`paid_by`),
  KEY `payroll_runs_hotel_id_index` (`hotel_id`),
  CONSTRAINT `payroll_runs_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `payroll_runs_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE,
  CONSTRAINT `payroll_runs_paid_by_foreign` FOREIGN KEY (`paid_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `pending_staff_requests`;
CREATE TABLE `pending_staff_requests` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(255) NOT NULL,
  `requested_role` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `notes` text DEFAULT NULL,
  `status` enum('pending','approved','rejected','expired','cancelled') DEFAULT 'pending',
  `token` varchar(100) NOT NULL,
  `submitted_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `reviewed_at` timestamp NULL DEFAULT NULL,
  `reviewed_by` bigint(20) unsigned DEFAULT NULL,
  `decision_at` timestamp NULL DEFAULT NULL,
  `decision_notes` text DEFAULT NULL,
  `expires_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `cancelled_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `token` (`token`),
  KEY `idx_status_expires` (`status`,`expires_at`),
  KEY `idx_email` (`email`),
  KEY `pending_staff_requests_hotel_id_index` (`hotel_id`),
  CONSTRAINT `pending_staff_requests_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `personal_access_tokens`;
CREATE TABLE `personal_access_tokens` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tokenable_type` varchar(255) NOT NULL,
  `tokenable_id` bigint(20) unsigned NOT NULL,
  `name` text NOT NULL,
  `token` varchar(64) NOT NULL,
  `abilities` text DEFAULT NULL,
  `last_used_at` timestamp NULL DEFAULT NULL,
  `expires_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `personal_access_tokens_token_unique` (`token`),
  KEY `personal_access_tokens_tokenable_type_tokenable_id_index` (`tokenable_type`,`tokenable_id`),
  KEY `personal_access_tokens_expires_at_index` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `pos_order_items`;
CREATE TABLE `pos_order_items` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `order_id` bigint(20) unsigned NOT NULL,
  `menu_item_id` bigint(20) unsigned NOT NULL,
  `quantity` int(11) NOT NULL,
  `unit_price` decimal(8,2) NOT NULL,
  `total_price` decimal(8,2) NOT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_pos_order_items_order_id` (`order_id`),
  KEY `idx_pos_order_items_menu_item_id` (`menu_item_id`),
  KEY `pos_order_items_hotel_id_index` (`hotel_id`),
  CONSTRAINT `pos_order_items_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `pos_orders`;
CREATE TABLE `pos_orders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `order_number` varchar(255) NOT NULL,
  `room_id` bigint(20) unsigned DEFAULT NULL,
  `guest_id` bigint(20) unsigned DEFAULT NULL,
  `order_type` enum('room_service','restaurant','takeaway') DEFAULT 'restaurant',
  `status` enum('pending','confirmed','preparing','ready','completed','cancelled') DEFAULT 'pending',
  `priority` enum('low','normal','high','urgent') DEFAULT 'normal',
  `special_instructions` text DEFAULT NULL,
  `subtotal` decimal(10,2) DEFAULT 0.00,
  `tax_amount` decimal(10,2) DEFAULT 0.00,
  `service_charge` decimal(10,2) DEFAULT 0.00,
  `total_amount` decimal(10,2) DEFAULT 0.00,
  `prepared_by` bigint(20) unsigned DEFAULT NULL,
  `completed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `accounting_synced` tinyint(1) NOT NULL DEFAULT 0,
  `accounting_sync_at` timestamp NULL DEFAULT NULL,
  `accounting_synced_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `order_number` (`order_number`),
  KEY `prepared_by` (`prepared_by`),
  KEY `idx_pos_orders_status` (`status`),
  KEY `idx_pos_orders_room_id` (`room_id`),
  KEY `idx_pos_orders_guest_id` (`guest_id`),
  KEY `idx_pos_orders_created_at` (`created_at`),
  KEY `pos_orders_hotel_id_index` (`hotel_id`),
  CONSTRAINT `pos_orders_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE,
  CONSTRAINT `pos_orders_ibfk_1` FOREIGN KEY (`room_id`) REFERENCES `rooms` (`id`) ON DELETE SET NULL,
  CONSTRAINT `pos_orders_ibfk_2` FOREIGN KEY (`guest_id`) REFERENCES `guests` (`id`) ON DELETE SET NULL,
  CONSTRAINT `pos_orders_ibfk_3` FOREIGN KEY (`prepared_by`) REFERENCES `staff` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `preventive_maintenance`;
CREATE TABLE `preventive_maintenance` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `preventive_maintenance_hotel_id_index` (`hotel_id`),
  CONSTRAINT `preventive_maintenance_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `production_consumptions`;
CREATE TABLE `production_consumptions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `production_id` bigint(20) unsigned NOT NULL,
  `product_id` bigint(20) unsigned NOT NULL,
  `quantity_used` decimal(15,3) NOT NULL,
  `unit_cost` decimal(15,2) NOT NULL,
  `total_cost` decimal(15,2) NOT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `production_consumptions_production_id_foreign` (`production_id`),
  KEY `production_consumptions_product_id_foreign` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `production_outputs`;
CREATE TABLE `production_outputs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `production_id` bigint(20) unsigned NOT NULL,
  `quantity_produced` decimal(15,3) NOT NULL,
  `quantity_rejected` decimal(15,3) NOT NULL DEFAULT 0.000,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `production_outputs_production_id_foreign` (`production_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `production_quality_checks`;
CREATE TABLE `production_quality_checks` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `production_id` bigint(20) unsigned NOT NULL,
  `check_type` varchar(255) NOT NULL,
  `result` enum('pass','fail','conditional') NOT NULL,
  `notes` text DEFAULT NULL,
  `inspector_id` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `production_quality_checks_production_id_foreign` (`production_id`),
  KEY `production_quality_checks_inspector_id_foreign` (`inspector_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `productions`;
CREATE TABLE `productions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `manufacturing_order_id` bigint(20) unsigned NOT NULL,
  `work_center_id` bigint(20) unsigned DEFAULT NULL,
  `reference` varchar(255) NOT NULL,
  `status` enum('pending','in_progress','paused','completed','cancelled') NOT NULL DEFAULT 'pending',
  `quantity_target` decimal(15,3) NOT NULL,
  `quantity_produced` decimal(15,3) NOT NULL DEFAULT 0.000,
  `progress` decimal(5,2) DEFAULT 0.00,
  `quantity_rejected` decimal(15,3) NOT NULL DEFAULT 0.000,
  `started_at` timestamp NULL DEFAULT NULL,
  `completed_at` timestamp NULL DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `supervisor_id` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `products`;
CREATE TABLE `products` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `menu_item_id` bigint(20) unsigned DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `sku` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `type` enum('raw','finished','component') DEFAULT 'raw',
  `cost` decimal(10,2) DEFAULT 0.00,
  `price` decimal(10,2) DEFAULT 0.00,
  `stock_quantity` int(11) DEFAULT 0,
  `min_stock_level` int(11) DEFAULT 0,
  `unit_of_measure` varchar(50) DEFAULT 'pcs',
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sku` (`sku`),
  KEY `products_menu_item_id_foreign` (`menu_item_id`),
  CONSTRAINT `products_menu_item_id_foreign` FOREIGN KEY (`menu_item_id`) REFERENCES `menu_items` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `products` (`id`, `menu_item_id`, `name`, `sku`, `description`, `type`, `cost`, `price`, `stock_quantity`, `min_stock_level`, `unit_of_measure`, `is_active`, `created_at`, `updated_at`) VALUES ('1', '1', 'bhjjnknm', 'MENU-000001', NULL, 'finished', '0.00', '5000.00', '0', '0', 'pcs', '1', '2026-05-25 15:15:55', '2026-05-25 15:15:55');

DROP TABLE IF EXISTS `purchase_orders`;
CREATE TABLE `purchase_orders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned DEFAULT NULL,
  `po_number` varchar(100) NOT NULL,
  `supplier_id` bigint(20) unsigned DEFAULT NULL,
  `total_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `status` varchar(50) NOT NULL DEFAULT 'pending',
  `received_date` date DEFAULT NULL,
  `accounting_synced` tinyint(1) NOT NULL DEFAULT 0,
  `accounting_sync_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `receipts`;
CREATE TABLE `receipts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `receipt_number` varchar(50) NOT NULL,
  `invoice_id` bigint(20) unsigned NOT NULL,
  `service_request_id` bigint(20) unsigned DEFAULT NULL,
  `guest_id` bigint(20) unsigned NOT NULL,
  `amount_paid` decimal(10,2) NOT NULL DEFAULT 0.00,
  `payment_date` datetime NOT NULL,
  `receipt_date` datetime DEFAULT current_timestamp(),
  `payment_method` varchar(50) DEFAULT 'cash',
  `payment_status` varchar(50) NOT NULL DEFAULT 'paid',
  `notes` text DEFAULT NULL,
  `received_by` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `receipt_number` (`receipt_number`),
  KEY `idx_receipt_number` (`receipt_number`),
  KEY `idx_invoice_id` (`invoice_id`),
  KEY `idx_service_request_id` (`service_request_id`),
  KEY `idx_guest_id` (`guest_id`),
  KEY `receipts_hotel_id_index` (`hotel_id`),
  CONSTRAINT `receipts_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `report_templates`;
CREATE TABLE `report_templates` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `category` varchar(255) NOT NULL,
  `report_type` varchar(255) NOT NULL,
  `date_range` varchar(255) DEFAULT NULL,
  `date_from` date DEFAULT NULL,
  `date_to` date DEFAULT NULL,
  `filters` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`filters`)),
  `user_id` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `report_templates_user_id_category_index` (`user_id`,`category`),
  KEY `report_templates_hotel_id_foreign` (`hotel_id`),
  CONSTRAINT `report_templates_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE,
  CONSTRAINT `report_templates_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `reports`;
CREATE TABLE `reports` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `category` varchar(100) NOT NULL,
  `type` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`data`)),
  `format` varchar(50) DEFAULT 'pdf',
  `period` varchar(100) DEFAULT NULL,
  `size` int(11) DEFAULT 0,
  `generated_by` bigint(20) unsigned DEFAULT NULL,
  `parameters` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`parameters`)),
  `module` varchar(100) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `reports_category_type_index` (`category`,`type`),
  KEY `reports_created_at_index` (`created_at`),
  KEY `reports_generated_by_foreign` (`generated_by`),
  KEY `reports_hotel_id_foreign` (`hotel_id`),
  CONSTRAINT `reports_generated_by_foreign` FOREIGN KEY (`generated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `reports_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `reservations`;
CREATE TABLE `reservations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `reservation_number` varchar(255) NOT NULL,
  `guest_id` bigint(20) unsigned NOT NULL,
  `room_id` bigint(20) unsigned NOT NULL,
  `check_in` date NOT NULL,
  `actual_check_in` datetime DEFAULT NULL,
  `check_out` date NOT NULL,
  `actual_check_out` datetime DEFAULT NULL,
  `number_of_guests` int(11) NOT NULL,
  `total_amount` decimal(10,2) NOT NULL,
  `subtotal` decimal(12,2) NOT NULL DEFAULT 0.00,
  `tax_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `paid_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `status` enum('confirmed','pending','checked_in','checked_out','cancelled') NOT NULL DEFAULT 'confirmed',
  `special_requests` text DEFAULT NULL,
  `created_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `accounting_synced` tinyint(1) NOT NULL DEFAULT 0,
  `accounting_sync_at` timestamp NULL DEFAULT NULL,
  `synced_to_accounting` tinyint(1) NOT NULL DEFAULT 0,
  `accounting_sync_attempts` int(10) unsigned NOT NULL DEFAULT 0,
  `accounting_sync_error` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `reservations_reservation_number_unique` (`reservation_number`),
  KEY `reservations_guest_id_foreign` (`guest_id`),
  KEY `reservations_room_id_foreign` (`room_id`),
  KEY `reservations_created_by_foreign` (`created_by`),
  KEY `reservations_hotel_id_index` (`hotel_id`),
  CONSTRAINT `reservations_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `reservations_guest_id_foreign` FOREIGN KEY (`guest_id`) REFERENCES `guests` (`id`),
  CONSTRAINT `reservations_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE,
  CONSTRAINT `reservations_room_id_foreign` FOREIGN KEY (`room_id`) REFERENCES `rooms` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `room_amenities`;
CREATE TABLE `room_amenities` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `room_amenities_hotel_id_index` (`hotel_id`),
  CONSTRAINT `room_amenities_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `room_maintenance`;
CREATE TABLE `room_maintenance` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `reason` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `room_maintenance_hotel_id_index` (`hotel_id`),
  CONSTRAINT `room_maintenance_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `room_types`;
CREATE TABLE `room_types` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `code` varchar(50) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `default_capacity` int(10) unsigned NOT NULL DEFAULT 2,
  `max_capacity` int(10) unsigned DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `has_breakfast` tinyint(1) NOT NULL DEFAULT 0,
  `amenities` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`amenities`)),
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `room_types_hotel_id_index` (`hotel_id`),
  CONSTRAINT `room_types_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `room_types` (`id`, `hotel_id`, `name`, `code`, `description`, `default_capacity`, `max_capacity`, `is_active`, `has_breakfast`, `amenities`, `created_at`, `updated_at`, `deleted_at`) VALUES ('1', '1', 'zcscsfsf', NULL, NULL, '2', NULL, '1', '0', NULL, '2026-05-25 12:35:52', '2026-05-25 12:35:52', NULL);

DROP TABLE IF EXISTS `rooms`;
CREATE TABLE `rooms` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `room_number` varchar(255) NOT NULL,
  `floor` varchar(10) DEFAULT NULL,
  `room_name` varchar(255) DEFAULT NULL,
  `category` varchar(50) DEFAULT NULL,
  `room_type_id` bigint(20) unsigned DEFAULT NULL,
  `price_per_night` decimal(10,2) NOT NULL,
  `description` text DEFAULT NULL,
  `capacity` int(11) NOT NULL,
  `status` enum('available','occupied','maintenance','cleaning') NOT NULL DEFAULT 'available',
  `housekeeping_status` enum('clean','dirty','in_progress','inspected') NOT NULL DEFAULT 'clean',
  `is_available` tinyint(1) NOT NULL DEFAULT 1,
  `amenities` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`amenities`)),
  `images` longtext DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `last_checked_out_at` timestamp NULL DEFAULT NULL,
  `last_cleaned_at` timestamp NULL DEFAULT NULL,
  `housekeeping_notes` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `rooms_room_number_unique` (`room_number`),
  KEY `rooms_room_type_id_foreign` (`room_type_id`),
  KEY `rooms_hotel_id_index` (`hotel_id`),
  CONSTRAINT `rooms_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE,
  CONSTRAINT `rooms_room_type_id_foreign` FOREIGN KEY (`room_type_id`) REFERENCES `room_types` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `rooms` (`id`, `hotel_id`, `room_number`, `floor`, `room_name`, `category`, `room_type_id`, `price_per_night`, `description`, `capacity`, `status`, `housekeeping_status`, `is_available`, `amenities`, `images`, `created_at`, `updated_at`, `last_checked_out_at`, `last_cleaned_at`, `housekeeping_notes`) VALUES ('1', '1', '001', '1', NULL, 'delux', '1', '100000.00', NULL, '2', 'available', 'clean', '1', '\"[\\\"Wi-Fi\\\",\\\"Air Conditioning\\\",\\\"Mini Bar\\\",\\\"Safe\\\"]\"', '\"[\\\"room-images\\\\\\/jMF1S5qqrLCwx7ko7NUZJwV2iyzmSheOvPY3kEcJ.png\\\"]\"', '2026-05-25 13:23:03', '2026-05-25 13:23:03', NULL, NULL, NULL);

DROP TABLE IF EXISTS `routing`;
CREATE TABLE `routing` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `bom_id` bigint(20) NOT NULL,
  `name` varchar(255) NOT NULL,
  `active` tinyint(1) DEFAULT 1,
  `sequence` int(11) DEFAULT 10,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `bom_id` (`bom_id`),
  CONSTRAINT `routing_ibfk_1` FOREIGN KEY (`bom_id`) REFERENCES `bom` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `routing_operations`;
CREATE TABLE `routing_operations` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `routing_id` bigint(20) NOT NULL,
  `workcenter_id` bigint(20) NOT NULL,
  `name` varchar(255) NOT NULL,
  `sequence` int(11) DEFAULT 10,
  `time_cycle` decimal(10,2) DEFAULT 0.00,
  `time_mode` varchar(50) DEFAULT 'manual',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `routing_id` (`routing_id`),
  KEY `workcenter_id` (`workcenter_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `schedules`;
CREATE TABLE `schedules` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `report_type` varchar(100) NOT NULL COMMENT 'Links to reports.category or reports.type',
  `report_parameters` longtext DEFAULT NULL COMMENT 'JSON parameters for the report',
  `module` varchar(100) DEFAULT NULL,
  `format` varchar(50) DEFAULT 'pdf',
  `frequency` varchar(50) NOT NULL COMMENT 'daily, weekly, monthly, custom',
  `time` time DEFAULT NULL COMMENT 'Time of day to run (HH:MM:SS)',
  `day_of_week` tinyint(4) DEFAULT NULL COMMENT '0-6 (Sunday=0)',
  `day_of_month` tinyint(4) DEFAULT NULL COMMENT '1-31',
  `month_of_year` tinyint(4) DEFAULT NULL COMMENT '1-12',
  `custom_pattern` varchar(255) DEFAULT NULL COMMENT 'Cron expression or custom pattern',
  `days_of_week` varchar(50) DEFAULT NULL COMMENT 'JSON array of days [1,2,3,4,5]',
  `is_recurring` tinyint(1) DEFAULT 1,
  `start_date` date NOT NULL,
  `end_date` date DEFAULT NULL,
  `next_run` datetime NOT NULL,
  `last_run` datetime DEFAULT NULL,
  `status` varchar(20) DEFAULT 'active' COMMENT 'active, paused, completed, failed',
  `is_enabled` tinyint(1) DEFAULT 1,
  `priority` varchar(20) DEFAULT 'normal' COMMENT 'low, normal, high, critical',
  `delivery_methods` varchar(255) DEFAULT NULL COMMENT 'JSON: ["email", "dashboard", "cloud"]',
  `email_recipients` text DEFAULT NULL COMMENT 'Comma-separated emails',
  `notify_on_failure` tinyint(1) DEFAULT 1,
  `max_retries` tinyint(4) DEFAULT 0,
  `retry_delay_minutes` smallint(6) DEFAULT 5,
  `failure_count` smallint(6) DEFAULT 0,
  `last_error` text DEFAULT NULL,
  `total_executions` int(11) DEFAULT 0,
  `successful_executions` int(11) DEFAULT 0,
  `failed_executions` int(11) DEFAULT 0,
  `average_duration_seconds` int(11) DEFAULT 0,
  `timezone` varchar(50) DEFAULT 'UTC',
  `language` varchar(10) DEFAULT 'en',
  `created_by` bigint(20) unsigned DEFAULT NULL,
  `updated_by` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_schedules_status` (`status`),
  KEY `idx_schedules_next_run` (`next_run`),
  KEY `idx_schedules_frequency` (`frequency`),
  KEY `idx_schedules_created_by` (`created_by`),
  KEY `idx_schedules_module` (`module`),
  KEY `idx_schedules_report_type` (`report_type`),
  KEY `schedules_hotel_id_index` (`hotel_id`),
  CONSTRAINT `schedules_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `service_categories`;
CREATE TABLE `service_categories` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `icon` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `service_categories_hotel_id_index` (`hotel_id`),
  CONSTRAINT `service_categories_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `service_categories` (`id`, `hotel_id`, `name`, `description`, `icon`, `is_active`, `created_at`, `updated_at`) VALUES ('1', '1', 'well fair', NULL, NULL, '1', '2026-05-25 14:20:11', '2026-05-25 14:20:11');

DROP TABLE IF EXISTS `service_logs`;
CREATE TABLE `service_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned DEFAULT NULL,
  `service_id` bigint(20) unsigned NOT NULL,
  `guest_id` bigint(20) unsigned NOT NULL,
  `service_time` datetime NOT NULL,
  `notes` text DEFAULT NULL,
  `status` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `service_logs_service_id_foreign` (`service_id`),
  KEY `service_logs_guest_id_foreign` (`guest_id`),
  KEY `service_logs_hotel_id_index` (`hotel_id`),
  CONSTRAINT `service_logs_guest_id_foreign` FOREIGN KEY (`guest_id`) REFERENCES `guests` (`id`) ON DELETE CASCADE,
  CONSTRAINT `service_logs_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE SET NULL,
  CONSTRAINT `service_logs_service_id_foreign` FOREIGN KEY (`service_id`) REFERENCES `services` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `service_requests`;
CREATE TABLE `service_requests` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `request_number` varchar(64) DEFAULT NULL,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `assigned_by` bigint(20) unsigned DEFAULT NULL,
  `assigned_at` datetime DEFAULT NULL,
  `assigned_staff_id` bigint(20) unsigned DEFAULT NULL,
  `service_id` bigint(20) unsigned NOT NULL,
  `quantity` int(11) DEFAULT 1,
  `unit_price` decimal(10,2) DEFAULT 0.00,
  `guest_id` bigint(20) unsigned NOT NULL,
  `room_id` bigint(20) unsigned DEFAULT NULL,
  `reservation_id` bigint(20) unsigned DEFAULT NULL,
  `guest_type` enum('hotel_guest','walk_in','service_guest') NOT NULL DEFAULT 'hotel_guest',
  `total_price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `rating` tinyint(3) unsigned DEFAULT NULL COMMENT 'Rating from 1 to 5 stars',
  `customer_notes` text DEFAULT NULL,
  `status` enum('pending','confirmed','in_progress','completed','cancelled') DEFAULT 'pending',
  `is_urgent` tinyint(1) NOT NULL DEFAULT 0,
  `scheduled_at` datetime DEFAULT NULL,
  `requested_at` timestamp NULL DEFAULT NULL,
  `completed_at` timestamp NULL DEFAULT NULL,
  `completed_by` bigint(20) unsigned DEFAULT NULL,
  `cancelled_at` timestamp NULL DEFAULT NULL,
  `cancelled_by` bigint(20) unsigned DEFAULT NULL,
  `invoice_id` bigint(20) unsigned DEFAULT NULL,
  `receipt_id` bigint(20) unsigned DEFAULT NULL,
  `admin_notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `accounting_synced` tinyint(1) NOT NULL DEFAULT 0,
  `accounting_sync_at` timestamp NULL DEFAULT NULL,
  `accounting_synced_at` timestamp NULL DEFAULT NULL,
  `billing_status` varchar(255) NOT NULL DEFAULT 'pending',
  `subtotal` decimal(10,2) DEFAULT 0.00,
  `tax_amount` decimal(10,2) DEFAULT 0.00,
  PRIMARY KEY (`id`),
  UNIQUE KEY `service_requests_hotel_request_number_unique` (`hotel_id`,`request_number`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_service_id` (`service_id`),
  KEY `idx_status` (`status`),
  KEY `idx_scheduled_date` (`scheduled_at`),
  KEY `idx_created_at` (`created_at`),
  KEY `fk_service_requests_guest_id` (`guest_id`),
  KEY `idx_assigned_by` (`assigned_by`),
  KEY `idx_assigned_staff_id` (`assigned_staff_id`),
  KEY `idx_invoice_id` (`invoice_id`),
  KEY `idx_receipt_id` (`receipt_id`),
  KEY `idx_billing_status` (`billing_status`),
  KEY `service_requests_hotel_id_index` (`hotel_id`),
  KEY `service_requests_room_id_foreign` (`room_id`),
  KEY `service_requests_reservation_id_foreign` (`reservation_id`),
  KEY `service_requests_completed_by_foreign` (`completed_by`),
  KEY `service_requests_cancelled_by_foreign` (`cancelled_by`),
  CONSTRAINT `service_requests_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `service_requests` (`id`, `request_number`, `hotel_id`, `user_id`, `assigned_by`, `assigned_at`, `assigned_staff_id`, `service_id`, `quantity`, `unit_price`, `guest_id`, `room_id`, `reservation_id`, `guest_type`, `total_price`, `rating`, `customer_notes`, `status`, `is_urgent`, `scheduled_at`, `requested_at`, `completed_at`, `completed_by`, `cancelled_at`, `cancelled_by`, `invoice_id`, `receipt_id`, `admin_notes`, `created_at`, `updated_at`, `accounting_synced`, `accounting_sync_at`, `accounting_synced_at`, `billing_status`, `subtotal`, `tax_amount`) VALUES ('1', 'SRV-202605-000001', '1', '2', '2', '2026-05-25 14:38:16', NULL, '1', '1', '1000.00', '3', NULL, NULL, 'service_guest', '1000.00', NULL, NULL, 'pending', '0', '2026-05-25 17:38:00', '2026-05-25 14:38:16', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2026-05-25 14:38:16', '2026-05-25 14:38:16', '0', NULL, NULL, 'pending', '1000.00', '0.00');

DROP TABLE IF EXISTS `services`;
CREATE TABLE `services` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `service_category_id` bigint(20) unsigned DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `price` decimal(12,2) NOT NULL DEFAULT 0.00,
  `duration` int(10) unsigned DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `is_available` tinyint(1) NOT NULL DEFAULT 1,
  `image` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `services_hotel_id_index` (`hotel_id`),
  CONSTRAINT `services_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `services` (`id`, `hotel_id`, `service_category_id`, `name`, `description`, `price`, `duration`, `is_active`, `is_available`, `image`, `created_at`, `updated_at`) VALUES ('1', '1', '1', 'spa', NULL, '1000.00', '30', '1', '1', 'services/1/wePg7l1b3Duh6pTDLwmpdoq9b4Q2QcJ3pRB2pC3Y.png', '2026-05-25 14:20:56', '2026-05-25 14:20:56');

DROP TABLE IF EXISTS `sessions`;
CREATE TABLE `sessions` (
  `id` varchar(255) NOT NULL,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `payload` longtext NOT NULL,
  `last_activity` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `sessions_user_id_index` (`user_id`),
  KEY `sessions_last_activity_index` (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `settings`;
CREATE TABLE `settings` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned DEFAULT NULL,
  `key` varchar(255) NOT NULL,
  `display_name` varchar(255) DEFAULT NULL,
  `value` text DEFAULT NULL,
  `group` varchar(255) NOT NULL DEFAULT 'general',
  `sort_order` int(11) DEFAULT 0,
  `description` text DEFAULT NULL,
  `type` varchar(255) NOT NULL DEFAULT 'text',
  `options` text DEFAULT NULL,
  `is_public` tinyint(1) DEFAULT 0,
  `is_encrypted` tinyint(1) DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `settings_hotel_id_key_unique` (`hotel_id`,`key`),
  KEY `settings_group_index` (`group`),
  KEY `settings_type_index` (`type`),
  KEY `settings_hotel_id_index` (`hotel_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `settings` (`id`, `hotel_id`, `key`, `display_name`, `value`, `group`, `sort_order`, `description`, `type`, `options`, `is_public`, `is_encrypted`, `created_at`, `updated_at`) VALUES ('1', '1', 'base_currency', NULL, 'KES', 'general', '0', NULL, 'text', NULL, '0', '0', '2026-05-20 22:38:04', '2026-05-20 22:38:04');
INSERT INTO `settings` (`id`, `hotel_id`, `key`, `display_name`, `value`, `group`, `sort_order`, `description`, `type`, `options`, `is_public`, `is_encrypted`, `created_at`, `updated_at`) VALUES ('2', '1', 'currency_symbol', NULL, 'KES', 'general', '0', NULL, 'text', NULL, '0', '0', '2026-05-20 22:38:04', '2026-05-20 22:38:04');

DROP TABLE IF EXISTS `staff`;
CREATE TABLE `staff` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `employee_id` varchar(20) DEFAULT NULL,
  `first_name` varchar(100) DEFAULT NULL,
  `last_name` varchar(100) DEFAULT NULL,
  `middle_name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `gender` varchar(20) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `residence` varchar(2000) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `state` varchar(255) DEFAULT NULL,
  `zip_code` varchar(20) DEFAULT NULL,
  `nin` varchar(50) DEFAULT NULL,
  `department_id` bigint(20) unsigned DEFAULT NULL,
  `position_id` bigint(20) unsigned DEFAULT NULL,
  `hire_date` date DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `status` varchar(32) NOT NULL DEFAULT 'active',
  `salary` decimal(12,2) DEFAULT NULL,
  `tin_number` varchar(100) DEFAULT NULL,
  `nssf_number` varchar(100) DEFAULT NULL,
  `bank_name` varchar(255) DEFAULT NULL,
  `bank_account` varchar(100) DEFAULT NULL,
  `emergency_contact_name` varchar(255) DEFAULT NULL,
  `emergency_contact_phone` varchar(30) DEFAULT NULL,
  `emergency_contact_relationship` varchar(255) DEFAULT NULL,
  `next_of_kin_name` varchar(255) DEFAULT NULL,
  `next_of_kin_relationship` varchar(255) DEFAULT NULL,
  `next_of_kin_phone` varchar(30) DEFAULT NULL,
  `next_of_kin_address` varchar(500) DEFAULT NULL,
  `service_specialization` varchar(255) DEFAULT NULL,
  `max_assignments` int(10) unsigned DEFAULT NULL,
  `is_available` tinyint(1) NOT NULL DEFAULT 1,
  `profile_picture` varchar(255) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `additional_notes` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `staff_hotel_id_index` (`hotel_id`),
  CONSTRAINT `staff_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `staff` (`id`, `hotel_id`, `employee_id`, `first_name`, `last_name`, `middle_name`, `email`, `phone`, `date_of_birth`, `gender`, `address`, `residence`, `city`, `state`, `zip_code`, `nin`, `department_id`, `position_id`, `hire_date`, `created_at`, `updated_at`, `status`, `salary`, `tin_number`, `nssf_number`, `bank_name`, `bank_account`, `emergency_contact_name`, `emergency_contact_phone`, `emergency_contact_relationship`, `next_of_kin_name`, `next_of_kin_relationship`, `next_of_kin_phone`, `next_of_kin_address`, `service_specialization`, `max_assignments`, `is_available`, `profile_picture`, `notes`, `additional_notes`) VALUES ('1', '1', 'EMP1001', 'ndnasfnmaf', 'nmanmanmasfnm', 'ghajjsksk', 'adsd@gmail.com', '0789642345', '2000-01-05', NULL, NULL, 'ebbbs', NULL, NULL, NULL, NULL, '1', '1', '2020-05-25', '2026-05-25 14:10:28', '2026-05-25 15:05:34', 'active', '280000.00', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '0', 'hotels/1/staff-profiles/staff-1-1779721534.png', NULL, NULL);

DROP TABLE IF EXISTS `staff_departments`;
CREATE TABLE `staff_departments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `manager_id` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `staff_departments_hotel_id_foreign` (`hotel_id`),
  CONSTRAINT `staff_departments_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `staff_departments` (`id`, `hotel_id`, `name`, `description`, `manager_id`, `created_at`, `updated_at`) VALUES ('1', '1', 'hjascam', NULL, NULL, '2026-05-25 14:08:32', '2026-05-25 14:08:32');

DROP TABLE IF EXISTS `staff_positions`;
CREATE TABLE `staff_positions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `salary_range_min` decimal(10,2) DEFAULT NULL,
  `salary_range_max` decimal(10,2) DEFAULT NULL,
  `permissions` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`permissions`)),
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `staff_positions_hotel_id_foreign` (`hotel_id`),
  CONSTRAINT `staff_positions_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `staff_positions` (`id`, `hotel_id`, `name`, `description`, `salary_range_min`, `salary_range_max`, `permissions`, `created_at`, `updated_at`) VALUES ('1', '1', 'jjjkkkkk', NULL, '250000.00', '300000.00', NULL, '2026-05-25 14:08:53', '2026-05-25 14:08:53');

DROP TABLE IF EXISTS `subscription_payments`;
CREATE TABLE `subscription_payments` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `subscription_id` bigint(20) unsigned NOT NULL,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `payment_number` varchar(50) NOT NULL,
  `amount` decimal(15,2) NOT NULL DEFAULT 0.00,
  `currency` varchar(3) DEFAULT NULL,
  `amount_ugx` decimal(15,2) DEFAULT NULL,
  `payment_date` date NOT NULL,
  `payment_method` varchar(50) NOT NULL,
  `transaction_reference` varchar(255) DEFAULT NULL,
  `status` varchar(50) NOT NULL DEFAULT 'pending',
  `notes` text DEFAULT NULL,
  `receipt_path` varchar(255) DEFAULT NULL,
  `received_by` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `subscription_payments_payment_number_unique` (`payment_number`),
  KEY `subscription_payments_subscription_id_foreign` (`subscription_id`),
  KEY `subscription_payments_hotel_id_foreign` (`hotel_id`),
  KEY `subscription_payments_received_by_foreign` (`received_by`),
  KEY `idx_status_payment_date` (`status`,`payment_date`),
  CONSTRAINT `subscription_payments_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `subscriptions`;
CREATE TABLE `subscriptions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `subscription_number` varchar(50) DEFAULT NULL,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `plan_name` varchar(255) NOT NULL DEFAULT 'silver',
  `plan_price` decimal(15,2) NOT NULL DEFAULT 0.00,
  `billing_cycle` varchar(50) NOT NULL DEFAULT 'monthly',
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `trial_start_date` date DEFAULT NULL,
  `trial_end_date` date DEFAULT NULL,
  `status` varchar(50) NOT NULL DEFAULT 'trial',
  `max_rooms` int(11) NOT NULL DEFAULT 0,
  `max_users` int(11) NOT NULL DEFAULT 0,
  `enabled_modules` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`enabled_modules`)),
  `notes` text DEFAULT NULL,
  `cancelled_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `subscription_number` (`subscription_number`),
  KEY `subscriptions_hotel_id_index` (`hotel_id`),
  KEY `idx_subscriptions_status` (`status`),
  KEY `idx_subscriptions_dates` (`start_date`,`end_date`),
  KEY `idx_subscriptions_hotel_status` (`hotel_id`,`status`),
  KEY `idx_status_end_date` (`status`,`end_date`),
  CONSTRAINT `subscriptions_hotel_id_fk` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE,
  CONSTRAINT `subscriptions_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `subscriptions` (`id`, `subscription_number`, `hotel_id`, `plan_name`, `plan_price`, `billing_cycle`, `start_date`, `end_date`, `trial_start_date`, `trial_end_date`, `status`, `max_rooms`, `max_users`, `enabled_modules`, `notes`, `cancelled_at`, `created_at`, `updated_at`) VALUES ('1', 'SUB-202605-0001', '1', 'platinum', '14865.00', 'monthly', '2026-06-20', '2026-07-20', '2026-05-20', '2026-06-20', 'trial', '60', '10', '[\"rooms\",\"reservations\",\"accounting\",\"front_desk\",\"guests\",\"housekeeping\",\"maintenance\",\"survey\",\"documents\",\"hotel_services\",\"inventory\",\"hr\",\"reporting\",\"food_beverage\",\"settings\"]', 'Auto-created from hotel registration', NULL, '2026-05-20 22:38:04', '2026-05-20 22:38:04');

DROP TABLE IF EXISTS `survey_answers`;
CREATE TABLE `survey_answers` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `response_id` bigint(20) unsigned NOT NULL,
  `question_id` bigint(20) unsigned NOT NULL,
  `answer` text NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `survey_answers_response_id_foreign` (`response_id`),
  KEY `survey_answers_question_id_foreign` (`question_id`),
  KEY `survey_answers_hotel_id_foreign` (`hotel_id`),
  CONSTRAINT `survey_answers_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE,
  CONSTRAINT `survey_answers_question_id_foreign` FOREIGN KEY (`question_id`) REFERENCES `survey_questions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `survey_answers_response_id_foreign` FOREIGN KEY (`response_id`) REFERENCES `survey_responses` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `survey_answers` (`id`, `hotel_id`, `response_id`, `question_id`, `answer`, `created_at`, `updated_at`) VALUES ('1', '1', '1', '1', 'bbnnnnm', '2026-05-25 15:14:08', '2026-05-25 15:14:08');

DROP TABLE IF EXISTS `survey_invitations`;
CREATE TABLE `survey_invitations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `survey_id` bigint(20) unsigned NOT NULL,
  `guest_id` bigint(20) unsigned NOT NULL,
  `staff_id` bigint(20) unsigned DEFAULT NULL,
  `recipient_type` varchar(50) DEFAULT NULL,
  `recipient_email` varchar(255) DEFAULT NULL,
  `recipient_name` varchar(255) DEFAULT NULL,
  `token` varchar(255) NOT NULL,
  `expires_at` timestamp NULL DEFAULT NULL,
  `status` varchar(50) DEFAULT 'sent',
  `sent_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `responded_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `survey_invitations_token_unique` (`token`),
  KEY `survey_invitations_survey_id_foreign` (`survey_id`),
  KEY `survey_invitations_guest_id_foreign` (`guest_id`),
  KEY `survey_invitations_staff_id_index` (`staff_id`),
  KEY `survey_invitations_recipient_type_index` (`recipient_type`),
  KEY `survey_invitations_recipient_email_index` (`recipient_email`),
  KEY `survey_invitations_status_index` (`status`),
  KEY `survey_invitations_hotel_id_foreign` (`hotel_id`),
  CONSTRAINT `survey_invitations_guest_id_foreign` FOREIGN KEY (`guest_id`) REFERENCES `guests` (`id`) ON DELETE CASCADE,
  CONSTRAINT `survey_invitations_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE,
  CONSTRAINT `survey_invitations_survey_id_foreign` FOREIGN KEY (`survey_id`) REFERENCES `surveys` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `survey_questions`;
CREATE TABLE `survey_questions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `survey_id` bigint(20) unsigned NOT NULL,
  `question` text NOT NULL,
  `type` enum('multiple_choice','rating','text','dropdown','checkbox') NOT NULL,
  `options` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`options`)),
  `order` int(11) NOT NULL DEFAULT 0,
  `is_required` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `survey_questions_survey_id_foreign` (`survey_id`),
  KEY `survey_questions_hotel_id_index` (`hotel_id`),
  CONSTRAINT `survey_questions_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE,
  CONSTRAINT `survey_questions_survey_id_foreign` FOREIGN KEY (`survey_id`) REFERENCES `surveys` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `survey_questions` (`id`, `hotel_id`, `survey_id`, `question`, `type`, `options`, `order`, `is_required`, `created_at`, `updated_at`) VALUES ('1', '1', '1', 'nmzcmzcmcxcx', 'text', NULL, '0', '1', '2026-05-25 15:12:35', '2026-05-25 15:12:35');

DROP TABLE IF EXISTS `survey_responses`;
CREATE TABLE `survey_responses` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `survey_id` bigint(20) unsigned NOT NULL,
  `guest_id` bigint(20) unsigned DEFAULT NULL,
  `staff_id` bigint(20) unsigned DEFAULT NULL,
  `submitted_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `survey_responses_survey_id_foreign` (`survey_id`),
  KEY `survey_responses_guest_id_foreign` (`guest_id`),
  KEY `survey_responses_hotel_id_index` (`hotel_id`),
  CONSTRAINT `survey_responses_guest_id_foreign` FOREIGN KEY (`guest_id`) REFERENCES `guests` (`id`) ON DELETE SET NULL,
  CONSTRAINT `survey_responses_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE,
  CONSTRAINT `survey_responses_survey_id_foreign` FOREIGN KEY (`survey_id`) REFERENCES `surveys` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `survey_responses` (`id`, `hotel_id`, `survey_id`, `guest_id`, `staff_id`, `submitted_at`, `created_at`, `updated_at`) VALUES ('1', '1', '1', NULL, NULL, '2026-05-25 15:14:08', '2026-05-25 15:14:08', '2026-05-25 15:14:08');

DROP TABLE IF EXISTS `surveys`;
CREATE TABLE `surveys` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `media` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`media`)),
  `status` enum('draft','active','completed') NOT NULL DEFAULT 'draft',
  `start_date` timestamp NULL DEFAULT NULL,
  `end_date` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `surveys_hotel_id_index` (`hotel_id`),
  CONSTRAINT `surveys_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `surveys` (`id`, `hotel_id`, `title`, `description`, `media`, `status`, `start_date`, `end_date`, `created_at`, `updated_at`) VALUES ('1', '1', 'jddmdmdm', NULL, NULL, 'active', NULL, NULL, '2026-05-25 15:12:35', '2026-05-25 15:12:35');

DROP TABLE IF EXISTS `tags`;
CREATE TABLE `tags` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `name` varchar(100) NOT NULL,
  `group` varchar(100) DEFAULT NULL COMMENT 'e.g., Balance Sheet ÔÇö Asset, Revenue',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tags_hotel_id_name_unique` (`hotel_id`,`name`),
  KEY `tags_hotel_id_index` (`hotel_id`),
  CONSTRAINT `tags_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `tax_rates`;
CREATE TABLE `tax_rates` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `name` varchar(100) NOT NULL,
  `rate` decimal(5,2) NOT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `tax_rates_hotel_id_index` (`hotel_id`),
  CONSTRAINT `tax_rates_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `technicians`;
CREATE TABLE `technicians` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(255) NOT NULL,
  `specialization` text NOT NULL,
  `status` enum('available','busy','on_leave') NOT NULL DEFAULT 'available',
  `active_tasks` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `technicians_email_unique` (`email`),
  KEY `technicians_hotel_id_index` (`hotel_id`),
  CONSTRAINT `technicians_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `traffic_sources`;
CREATE TABLE `traffic_sources` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `source_name` varchar(50) NOT NULL,
  `visits` int(11) DEFAULT 0,
  `stat_date` date NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_source_date` (`source_name`,`stat_date`),
  KEY `idx_source_date` (`source_name`,`stat_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `transactions`;
CREATE TABLE `transactions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `type` varchar(255) NOT NULL COMMENT 'revenue, expense',
  `source` varchar(255) NOT NULL COMMENT 'room_booking, pos, services, maintenance, etc.',
  `amount` decimal(10,2) NOT NULL,
  `description` text DEFAULT NULL,
  `reference_id` varchar(255) DEFAULT NULL COMMENT 'reservation_id, order_id, etc.',
  `status` varchar(255) DEFAULT 'completed' COMMENT 'completed, pending, cancelled',
  `transaction_date` date DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `transactions_type_status_index` (`type`,`status`),
  KEY `transactions_transaction_date_index` (`transaction_date`),
  KEY `transactions_source_index` (`source`),
  KEY `transactions_hotel_id_index` (`hotel_id`),
  CONSTRAINT `transactions_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `trial_approval_requests`;
CREATE TABLE `trial_approval_requests` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `hotel_name` varchar(255) NOT NULL,
  `hotel_email` varchar(255) NOT NULL,
  `package` varchar(255) NOT NULL,
  `monthly_price` decimal(10,2) NOT NULL,
  `billing_cycle` varchar(255) NOT NULL DEFAULT 'monthly',
  `trial_started_at` datetime NOT NULL,
  `trial_ended_at` datetime NOT NULL,
  `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `requested_at` datetime NOT NULL,
  `processed_at` datetime DEFAULT NULL,
  `processed_by` bigint(20) unsigned DEFAULT NULL,
  `admin_notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `trial_approval_requests_hotel_id_foreign` (`hotel_id`),
  KEY `trial_approval_requests_processed_by_foreign` (`processed_by`),
  CONSTRAINT `trial_approval_requests_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE,
  CONSTRAINT `trial_approval_requests_processed_by_foreign` FOREIGN KEY (`processed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned DEFAULT NULL,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `role` varchar(255) NOT NULL,
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `password` varchar(255) NOT NULL,
  `remember_token` varchar(100) DEFAULT NULL,
  `last_login_at` timestamp NULL DEFAULT NULL,
  `last_login_ip` varchar(45) DEFAULT NULL,
  `theme_preference` varchar(20) DEFAULT 'light',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`),
  KEY `users_hotel_id_index` (`hotel_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `users` (`id`, `hotel_id`, `first_name`, `last_name`, `email`, `phone`, `role`, `email_verified_at`, `password`, `remember_token`, `last_login_at`, `last_login_ip`, `theme_preference`, `created_at`, `updated_at`) VALUES ('1', '1', 'Super', 'Admin', 'admin@wepesihms.com', NULL, 'super_admin', NULL, '$2y$12$v0e7mep0uSaS0kPPF6BrveEcqgbGme3x6UHji8jpmcF5hgCIMnT4a', NULL, NULL, NULL, 'light', '2026-05-20 22:30:49', '2026-05-20 22:30:49');
INSERT INTO `users` (`id`, `hotel_id`, `first_name`, `last_name`, `email`, `phone`, `role`, `email_verified_at`, `password`, `remember_token`, `last_login_at`, `last_login_ip`, `theme_preference`, `created_at`, `updated_at`) VALUES ('2', '1', 'NABWAMI', 'IMELDA', 'nivnabwami@gmail.com', NULL, 'director', '2026-05-20 22:41:26', '$2y$12$RlxABa4c.SQ3UhVp0W8riuIfaGj./AYnjzce/coAEhAB5YrD3Kuqe', NULL, '2026-05-25 11:34:05', '127.0.0.1', 'light', '2026-05-20 22:41:26', '2026-05-25 11:34:05');

DROP TABLE IF EXISTS `vendors`;
CREATE TABLE `vendors` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `contact_person` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `tax_id` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `vendors_hotel_id_index` (`hotel_id`),
  CONSTRAINT `vendors_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `work_centers`;
CREATE TABLE `work_centers` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `code` varchar(100) NOT NULL,
  `active` tinyint(1) DEFAULT 1,
  `capacity` decimal(10,2) DEFAULT 1.00,
  `time_efficiency` decimal(5,2) DEFAULT 100.00,
  `costs_hour` decimal(10,2) DEFAULT 0.00,
  `color` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;