-- Database Backup for Hotel 1
-- Generated: 2026-03-21 15:44:22

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`),
  CONSTRAINT `account_balances_account_id_foreign` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `account_balances_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 `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`),
  CONSTRAINT `account_tag_account_id_foreign` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `account_tag_tag_id_foreign` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`) ON DELETE CASCADE
) 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`),
  CONSTRAINT `accountabilities_account_id_foreign` FOREIGN KEY (`account_id`) REFERENCES `old_accounts_backup` (`id`) ON DELETE CASCADE,
  CONSTRAINT `accountabilities_cleared_by_foreign` FOREIGN KEY (`cleared_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `accountabilities_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `accountabilities_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE,
  CONSTRAINT `accountabilities_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 `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`),
  CONSTRAINT `accounts_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE,
  CONSTRAINT `accounts_parent_id_foreign` FOREIGN KEY (`parent_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=252 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`),
  CONSTRAINT `admin_activities_admin_id_foreign` FOREIGN KEY (`admin_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `admin_activities_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 `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 AUTO_INCREMENT=4 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 AUTO_INCREMENT=9 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`),
  CONSTRAINT `admin_hotel_invoice_items_invoice_id_foreign` FOREIGN KEY (`invoice_id`) REFERENCES `admin_hotel_invoices` (`id`) ON DELETE CASCADE
) 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`),
  CONSTRAINT `admin_hotel_invoices_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `admin_hotel_invoices_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE,
  CONSTRAINT `admin_hotel_invoices_payment_id_foreign` FOREIGN KEY (`payment_id`) REFERENCES `admin_hotel_payments` (`id`) ON DELETE SET NULL,
  CONSTRAINT `admin_hotel_invoices_subscription_id_foreign` FOREIGN KEY (`subscription_id`) REFERENCES `admin_hotel_subscriptions` (`id`) ON DELETE CASCADE
) 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`),
  CONSTRAINT `admin_hotel_payments_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE,
  CONSTRAINT `admin_hotel_payments_received_by_foreign` FOREIGN KEY (`received_by`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `admin_hotel_payments_subscription_id_foreign` FOREIGN KEY (`subscription_id`) REFERENCES `admin_hotel_subscriptions` (`id`) ON DELETE SET NULL
) 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`),
  CONSTRAINT `admin_hotel_subscription_history_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE,
  CONSTRAINT `admin_hotel_subscription_history_performed_by_foreign` FOREIGN KEY (`performed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `admin_hotel_subscription_history_subscription_id_foreign` FOREIGN KEY (`subscription_id`) REFERENCES `subscriptions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `admin_hotel_subscription_history` (`id`, `subscription_id`, `hotel_id`, `action`, `old_values`, `new_values`, `reason`, `performed_by`, `created_at`, `updated_at`) VALUES ('1', '1', '1', 'created', NULL, '\"{\\\"hotel_id\\\":1,\\\"subscription_number\\\":\\\"SUB-202603-0001\\\",\\\"plan_name\\\":\\\"silver\\\",\\\"plan_price\\\":\\\"150000.00\\\",\\\"billing_cycle\\\":\\\"monthly\\\",\\\"max_rooms\\\":20,\\\"max_users\\\":2,\\\"status\\\":\\\"trial\\\",\\\"notes\\\":\\\"Auto-created from hotel registration\\\",\\\"trial_start_date\\\":\\\"2026-03-21T14:07:54.000000Z\\\",\\\"trial_end_date\\\":\\\"2026-04-20T14:07:54.000000Z\\\",\\\"start_date\\\":\\\"2026-04-20T14:07:54.000000Z\\\",\\\"end_date\\\":\\\"2026-05-20T14:07:54.000000Z\\\",\\\"enabled_modules\\\":\\\"[\\\\\\\"rooms\\\\\\\",\\\\\\\"reservations\\\\\\\",\\\\\\\"accounting\\\\\\\",\\\\\\\"front_desk\\\\\\\",\\\\\\\"settings\\\\\\\"]\\\",\\\"updated_at\\\":\\\"2026-03-21T14:07:55.000000Z\\\",\\\"created_at\\\":\\\"2026-03-21T14:07:55.000000Z\\\",\\\"id\\\":1,\\\"days_remaining\\\":29.9999817775,\\\"is_expiring_soon\\\":false,\\\"usage_percentage\\\":{\\\"rooms\\\":0,\\\"users\\\":0,\\\"rooms_used\\\":0,\\\"users_used\\\":0,\\\"rooms_available\\\":20,\\\"users_available\\\":2},\\\"formatted_price\\\":\\\"150,000 UGX\\\",\\\"plan_details\\\":{\\\"name\\\":\\\"Silver\\\",\\\"price\\\":\\\"150,000 UGX\\\",\\\"price_raw\\\":\\\"150000.00\\\",\\\"rooms\\\":20,\\\"users\\\":2,\\\"cycle\\\":\\\"Monthly\\\"},\\\"hotel\\\":{\\\"id\\\":1,\\\"name\\\":\\\"POTERO HOTEL\\\",\\\"slug\\\":\\\"potero-hotel-gxgkjf\\\",\\\"email\\\":\\\"nalumansi@gmail.com\\\",\\\"phone\\\":\\\"+256 704567898\\\",\\\"address\\\":\\\"ENTEBBE\\\",\\\"city\\\":\\\"BUSAMBAGA\\\",\\\"country\\\":\\\"Uganda\\\",\\\"license_number\\\":null,\\\"tax_id\\\":null,\\\"logo\\\":null,\\\"website\\\":null,\\\"package\\\":\\\"silver\\\",\\\"max_rooms\\\":20,\\\"max_users\\\":2,\\\"enabled_modules\\\":\\\"[\\\\\\\"rooms\\\\\\\",\\\\\\\"reservations\\\\\\\",\\\\\\\"accounting\\\\\\\",\\\\\\\"front_desk\\\\\\\",\\\\\\\"settings\\\\\\\"]\\\",\\\"monthly_price\\\":\\\"150000.00\\\",\\\"billing_cycle\\\":\\\"monthly\\\",\\\"trial_ends_at\\\":\\\"2026-04-20T14:07:54.000000Z\\\",\\\"subscription_ends_at\\\":\\\"2026-05-20T14:07:54.000000Z\\\",\\\"subscription_starts_at\\\":\\\"2026-04-20T14:07:54.000000Z\\\",\\\"status\\\":\\\"trial\\\",\\\"chart_of_accounts_installed\\\":0,\\\"fiscal_year_start\\\":null,\\\"fiscal_year_end\\\":null,\\\"base_currency\\\":\\\"UGX\\\",\\\"vat_rate\\\":\\\"18.00\\\",\\\"created_at\\\":\\\"2026-03-21T14:07:55.000000Z\\\",\\\"updated_at\\\":\\\"2026-03-21T14:07:55.000000Z\\\",\\\"deleted_at\\\":null,\\\"subscription_status\\\":\\\"trial\\\",\\\"days_remaining\\\":29,\\\"is_expiring_soon\\\":false,\\\"logo_url\\\":null,\\\"modules_list\\\":[\\\"rooms\\\",\\\"reservations\\\",\\\"accounting\\\",\\\"front_desk\\\",\\\"settings\\\"],\\\"display_name\\\":\\\"POTERO HOTEL (BUSAMBAGA)\\\",\\\"short_address\\\":\\\"BUSAMBAGA, Uganda\\\"}}\"', NULL, '2', '2026-03-21 14:07:56', '2026-03-21 14:07:56');
INSERT INTO `admin_hotel_subscription_history` (`id`, `subscription_id`, `hotel_id`, `action`, `old_values`, `new_values`, `reason`, `performed_by`, `created_at`, `updated_at`) VALUES ('2', '1', '1', 'created', NULL, '\"{\\\"hotel_id\\\":1,\\\"subscription_number\\\":\\\"SUB-202603-0001\\\",\\\"plan_name\\\":\\\"silver\\\",\\\"plan_price\\\":\\\"150000.00\\\",\\\"billing_cycle\\\":\\\"monthly\\\",\\\"max_rooms\\\":20,\\\"max_users\\\":2,\\\"status\\\":\\\"trial\\\",\\\"notes\\\":\\\"Auto-created from hotel registration\\\",\\\"trial_start_date\\\":\\\"2026-03-21T14:07:54.000000Z\\\",\\\"trial_end_date\\\":\\\"2026-04-20T14:07:54.000000Z\\\",\\\"start_date\\\":\\\"2026-04-20T14:07:54.000000Z\\\",\\\"end_date\\\":\\\"2026-05-20T14:07:54.000000Z\\\",\\\"enabled_modules\\\":\\\"[\\\\\\\"rooms\\\\\\\",\\\\\\\"reservations\\\\\\\",\\\\\\\"accounting\\\\\\\",\\\\\\\"front_desk\\\\\\\",\\\\\\\"settings\\\\\\\"]\\\",\\\"updated_at\\\":\\\"2026-03-21T14:07:55.000000Z\\\",\\\"created_at\\\":\\\"2026-03-21T14:07:55.000000Z\\\",\\\"id\\\":1,\\\"days_remaining\\\":29.999973684560185,\\\"is_expiring_soon\\\":false,\\\"usage_percentage\\\":{\\\"rooms\\\":0,\\\"users\\\":0,\\\"rooms_used\\\":0,\\\"users_used\\\":0,\\\"rooms_available\\\":20,\\\"users_available\\\":2},\\\"formatted_price\\\":\\\"150,000 UGX\\\",\\\"plan_details\\\":{\\\"name\\\":\\\"Silver\\\",\\\"price\\\":\\\"150,000 UGX\\\",\\\"price_raw\\\":\\\"150000.00\\\",\\\"rooms\\\":20,\\\"users\\\":2,\\\"cycle\\\":\\\"Monthly\\\"},\\\"hotel\\\":{\\\"id\\\":1,\\\"name\\\":\\\"POTERO HOTEL\\\",\\\"slug\\\":\\\"potero-hotel-gxgkjf\\\",\\\"email\\\":\\\"nalumansi@gmail.com\\\",\\\"phone\\\":\\\"+256 704567898\\\",\\\"address\\\":\\\"ENTEBBE\\\",\\\"city\\\":\\\"BUSAMBAGA\\\",\\\"country\\\":\\\"Uganda\\\",\\\"license_number\\\":null,\\\"tax_id\\\":null,\\\"logo\\\":null,\\\"website\\\":null,\\\"package\\\":\\\"silver\\\",\\\"max_rooms\\\":20,\\\"max_users\\\":2,\\\"enabled_modules\\\":\\\"[\\\\\\\"rooms\\\\\\\",\\\\\\\"reservations\\\\\\\",\\\\\\\"accounting\\\\\\\",\\\\\\\"front_desk\\\\\\\",\\\\\\\"settings\\\\\\\"]\\\",\\\"monthly_price\\\":\\\"150000.00\\\",\\\"billing_cycle\\\":\\\"monthly\\\",\\\"trial_ends_at\\\":\\\"2026-04-20T14:07:54.000000Z\\\",\\\"subscription_ends_at\\\":\\\"2026-05-20T14:07:54.000000Z\\\",\\\"subscription_starts_at\\\":\\\"2026-04-20T14:07:54.000000Z\\\",\\\"status\\\":\\\"trial\\\",\\\"chart_of_accounts_installed\\\":0,\\\"fiscal_year_start\\\":null,\\\"fiscal_year_end\\\":null,\\\"base_currency\\\":\\\"UGX\\\",\\\"vat_rate\\\":\\\"18.00\\\",\\\"created_at\\\":\\\"2026-03-21T14:07:55.000000Z\\\",\\\"updated_at\\\":\\\"2026-03-21T14:07:55.000000Z\\\",\\\"deleted_at\\\":null,\\\"subscription_status\\\":\\\"trial\\\",\\\"days_remaining\\\":29,\\\"is_expiring_soon\\\":false,\\\"logo_url\\\":null,\\\"modules_list\\\":[\\\"rooms\\\",\\\"reservations\\\",\\\"accounting\\\",\\\"front_desk\\\",\\\"settings\\\"],\\\"display_name\\\":\\\"POTERO HOTEL (BUSAMBAGA)\\\",\\\"short_address\\\":\\\"BUSAMBAGA, Uganda\\\"}}\"', NULL, '2', '2026-03-21 14:07:56', '2026-03-21 14:07:56');

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`),
  CONSTRAINT `admin_hotel_subscriptions_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `admin_hotel_subscriptions_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 `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 AUTO_INCREMENT=6 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 AUTO_INCREMENT=3 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`),
  CONSTRAINT `admin_report_history_generated_by_foreign` FOREIGN KEY (`generated_by`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `admin_report_history_report_id_foreign` FOREIGN KEY (`report_id`) REFERENCES `admin_reports` (`id`) ON DELETE CASCADE
) 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`),
  CONSTRAINT `admin_report_schedules_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `admin_report_schedules_report_id_foreign` FOREIGN KEY (`report_id`) REFERENCES `admin_reports` (`id`) ON DELETE CASCADE
) 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 AUTO_INCREMENT=6 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,
  `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`)
) ENGINE=InnoDB AUTO_INCREMENT=4 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 DEFAULT 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,
  CONSTRAINT `attendances_recorded_by_foreign` FOREIGN KEY (`recorded_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `attendances_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 `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 DEFAULT 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_document_id_foreign` FOREIGN KEY (`document_id`) REFERENCES `documents` (`id`) ON DELETE CASCADE,
  CONSTRAINT `audit_logs_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE SET NULL,
  CONSTRAINT `audit_logs_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 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', '2', '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/146.0.0.0 Safari/537.36 Edg/146.0.0.0', NULL, '2026-03-21 14:07:56', '2026-03-21 14:07:56');
INSERT INTO `audit_logs` (`id`, `user_id`, `hotel_id`, `document_id`, `action`, `details`, `ip_address`, `user_agent`, `metadata`, `created_at`, `updated_at`) VALUES ('2', '2', '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/146.0.0.0 Safari/537.36 Edg/146.0.0.0', NULL, '2026-03-21 14:12:11', '2026-03-21 14:12:11');

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,
  `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 `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_account_id_foreign` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `budgets_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  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` int(11) 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`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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 AUTO_INCREMENT=25 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `chart_of_accounts` (`id`, `code`, `name`, `type`, `parent_id`, `balance`, `is_active`, `created_at`, `updated_at`) VALUES ('1', '1000', 'Current Assets', 'asset', NULL, '0.00', '1', '2025-10-30 18:32:53', '2025-10-30 18:32:53');
INSERT INTO `chart_of_accounts` (`id`, `code`, `name`, `type`, `parent_id`, `balance`, `is_active`, `created_at`, `updated_at`) VALUES ('2', '1100', 'Cash and Bank', 'asset', '1', '0.00', '1', '2025-10-30 18:32:53', '2025-10-30 18:32:53');
INSERT INTO `chart_of_accounts` (`id`, `code`, `name`, `type`, `parent_id`, `balance`, `is_active`, `created_at`, `updated_at`) VALUES ('3', '1110', 'Cash on Hand', 'asset', '2', '0.00', '1', '2025-10-30 18:32:53', '2025-10-30 18:32:53');
INSERT INTO `chart_of_accounts` (`id`, `code`, `name`, `type`, `parent_id`, `balance`, `is_active`, `created_at`, `updated_at`) VALUES ('4', '1120', 'Bank Account', 'asset', '2', '0.00', '1', '2025-10-30 18:32:53', '2025-10-30 18:32:53');
INSERT INTO `chart_of_accounts` (`id`, `code`, `name`, `type`, `parent_id`, `balance`, `is_active`, `created_at`, `updated_at`) VALUES ('5', '1200', 'Accounts Receivable', 'asset', '1', '0.00', '1', '2025-10-30 18:32:53', '2025-10-30 18:32:53');
INSERT INTO `chart_of_accounts` (`id`, `code`, `name`, `type`, `parent_id`, `balance`, `is_active`, `created_at`, `updated_at`) VALUES ('6', '1300', 'Inventory', 'asset', '1', '0.00', '1', '2025-10-30 18:32:53', '2025-10-30 18:32:53');
INSERT INTO `chart_of_accounts` (`id`, `code`, `name`, `type`, `parent_id`, `balance`, `is_active`, `created_at`, `updated_at`) VALUES ('7', '2000', 'Current Liabilities', 'liability', NULL, '0.00', '1', '2025-10-30 18:32:53', '2025-10-30 18:32:53');
INSERT INTO `chart_of_accounts` (`id`, `code`, `name`, `type`, `parent_id`, `balance`, `is_active`, `created_at`, `updated_at`) VALUES ('8', '2100', 'Accounts Payable', 'liability', '7', '0.00', '1', '2025-10-30 18:32:53', '2025-10-30 18:32:53');
INSERT INTO `chart_of_accounts` (`id`, `code`, `name`, `type`, `parent_id`, `balance`, `is_active`, `created_at`, `updated_at`) VALUES ('9', '2200', 'Tax Payable', 'liability', '7', '0.00', '1', '2025-10-30 18:32:53', '2025-10-30 18:32:53');
INSERT INTO `chart_of_accounts` (`id`, `code`, `name`, `type`, `parent_id`, `balance`, `is_active`, `created_at`, `updated_at`) VALUES ('10', '3000', 'Equity', 'equity', NULL, '0.00', '1', '2025-10-30 18:32:53', '2025-10-30 18:32:53');
INSERT INTO `chart_of_accounts` (`id`, `code`, `name`, `type`, `parent_id`, `balance`, `is_active`, `created_at`, `updated_at`) VALUES ('11', '3100', 'Owner\'s Capital', 'equity', '10', '0.00', '1', '2025-10-30 18:32:53', '2025-10-30 18:32:53');
INSERT INTO `chart_of_accounts` (`id`, `code`, `name`, `type`, `parent_id`, `balance`, `is_active`, `created_at`, `updated_at`) VALUES ('12', '3200', 'Retained Earnings', 'equity', '10', '0.00', '1', '2025-10-30 18:32:53', '2025-10-30 18:32:53');
INSERT INTO `chart_of_accounts` (`id`, `code`, `name`, `type`, `parent_id`, `balance`, `is_active`, `created_at`, `updated_at`) VALUES ('13', '4000', 'Revenue', 'income', NULL, '0.00', '1', '2025-10-30 18:32:53', '2025-10-30 18:32:53');
INSERT INTO `chart_of_accounts` (`id`, `code`, `name`, `type`, `parent_id`, `balance`, `is_active`, `created_at`, `updated_at`) VALUES ('14', '4100', 'Room Revenue', 'income', '13', '0.00', '1', '2025-10-30 18:32:53', '2025-10-30 18:32:53');
INSERT INTO `chart_of_accounts` (`id`, `code`, `name`, `type`, `parent_id`, `balance`, `is_active`, `created_at`, `updated_at`) VALUES ('15', '4200', 'Food & Beverage Revenue', 'income', '13', '0.00', '1', '2025-10-30 18:32:53', '2025-10-30 18:32:53');
INSERT INTO `chart_of_accounts` (`id`, `code`, `name`, `type`, `parent_id`, `balance`, `is_active`, `created_at`, `updated_at`) VALUES ('16', '4300', 'Service Revenue', 'income', '13', '0.00', '1', '2025-10-30 18:32:53', '2025-10-30 18:32:53');
INSERT INTO `chart_of_accounts` (`id`, `code`, `name`, `type`, `parent_id`, `balance`, `is_active`, `created_at`, `updated_at`) VALUES ('17', '4400', 'Other Revenue', 'income', '13', '0.00', '1', '2025-10-30 18:32:53', '2025-10-30 18:32:53');
INSERT INTO `chart_of_accounts` (`id`, `code`, `name`, `type`, `parent_id`, `balance`, `is_active`, `created_at`, `updated_at`) VALUES ('18', '5000', 'Operating Expenses', 'expense', NULL, '0.00', '1', '2025-10-30 18:32:53', '2025-10-30 18:32:53');
INSERT INTO `chart_of_accounts` (`id`, `code`, `name`, `type`, `parent_id`, `balance`, `is_active`, `created_at`, `updated_at`) VALUES ('19', '5100', 'Staff Costs', 'expense', '18', '0.00', '1', '2025-10-30 18:32:53', '2025-10-30 18:32:53');
INSERT INTO `chart_of_accounts` (`id`, `code`, `name`, `type`, `parent_id`, `balance`, `is_active`, `created_at`, `updated_at`) VALUES ('20', '5200', 'Food & Beverage Costs', 'expense', '18', '0.00', '1', '2025-10-30 18:32:53', '2025-10-30 18:32:53');
INSERT INTO `chart_of_accounts` (`id`, `code`, `name`, `type`, `parent_id`, `balance`, `is_active`, `created_at`, `updated_at`) VALUES ('21', '5300', 'Utilities', 'expense', '18', '0.00', '1', '2025-10-30 18:32:53', '2025-10-30 18:32:53');
INSERT INTO `chart_of_accounts` (`id`, `code`, `name`, `type`, `parent_id`, `balance`, `is_active`, `created_at`, `updated_at`) VALUES ('22', '5400', 'Maintenance & Repairs', 'expense', '18', '0.00', '1', '2025-10-30 18:32:53', '2025-10-30 18:32:53');
INSERT INTO `chart_of_accounts` (`id`, `code`, `name`, `type`, `parent_id`, `balance`, `is_active`, `created_at`, `updated_at`) VALUES ('23', '5500', 'Marketing', 'expense', '18', '0.00', '1', '2025-10-30 18:32:53', '2025-10-30 18:32:53');
INSERT INTO `chart_of_accounts` (`id`, `code`, `name`, `type`, `parent_id`, `balance`, `is_active`, `created_at`, `updated_at`) VALUES ('24', '5600', 'Administrative', 'expense', '18', '0.00', '1', '2025-10-30 18:32:53', '2025-10-30 18:32:53');

DROP TABLE IF EXISTS `contact_histories`;
CREATE TABLE `contact_histories` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `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`),
  CONSTRAINT `contact_history_contact_id_foreign` FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`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 DEFAULT 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_contact_id_foreign` FOREIGN KEY (`contact_id`) REFERENCES `contacts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `contact_notes_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  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 `contacts`;
CREATE TABLE `contacts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned DEFAULT NULL,
  `type` enum('guest','staff','supplier','vendor','other') DEFAULT NULL,
  `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,
  `vendor_category` varchar(255) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `country` varchar(255) DEFAULT NULL,
  `payment_terms` varchar(255) DEFAULT NULL,
  `bank_account` 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_foreign` (`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,
  `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`)
) 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,
  `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`),
  CONSTRAINT `document_folders_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  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,
  `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`),
  CONSTRAINT `document_shares_document_id_foreign` FOREIGN KEY (`document_id`) REFERENCES `documents` (`id`) ON DELETE CASCADE,
  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,
  `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`),
  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
) 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,
  `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_folder_id_foreign` (`folder_id`),
  CONSTRAINT `documents_folder_id_foreign` FOREIGN KEY (`folder_id`) REFERENCES `document_folders` (`id`) ON DELETE SET NULL,
  CONSTRAINT `documents_uploaded_by_foreign` FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `equipment`;
CREATE TABLE `equipment` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `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 `expense_categories`;
CREATE TABLE `expense_categories` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `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`)
) 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,
  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_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,
  `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`),
  CONSTRAINT `folder_shares_folder_id_foreign` FOREIGN KEY (`folder_id`) REFERENCES `folders` (`id`) ON DELETE CASCADE,
  CONSTRAINT `folder_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 `folders`;
CREATE TABLE `folders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `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`),
  CONSTRAINT `folders_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`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 ON UPDATE CASCADE,
  CONSTRAINT `guest_activities_ibfk_1` FOREIGN KEY (`guest_id`) REFERENCES `guests` (`id`) ON DELETE CASCADE,
  CONSTRAINT `guest_activities_ibfk_2` FOREIGN KEY (`recorded_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 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', 'guest_type_updated', 'Reservation guest type added', '{\"is_walkin\":false,\"is_reservation\":true,\"is_service\":false,\"is_regular\":false,\"total_types\":1}', '20', '2026-03-21 14:23:55', '2026-03-21 14:23:55', '2026-03-21 14:23:55');
INSERT INTO `guest_activities` (`id`, `hotel_id`, `guest_id`, `activity_type`, `description`, `metadata`, `recorded_by`, `activity_date`, `created_at`, `updated_at`) VALUES ('2', '1', '1', 'reservation_created', 'Regular reservation created: RES20260321142354175', '{\"reservation_id\":1,\"reservation_number\":\"RES20260321142354175\",\"is_walk_in\":\"0\",\"total_amount\":\"247800.00\",\"check_in\":\"2026-03-21\",\"check_out\":\"2026-03-22\"}', '20', '2026-03-21 14:23:55', '2026-03-21 14:23:55', '2026-03-21 14:23:55');
INSERT INTO `guest_activities` (`id`, `hotel_id`, `guest_id`, `activity_type`, `description`, `metadata`, `recorded_by`, `activity_date`, `created_at`, `updated_at`) VALUES ('3', '1', '2', 'guest_type_updated', 'Reservation guest type added', '{\"is_walkin\":false,\"is_reservation\":true,\"is_service\":false,\"is_regular\":false,\"total_types\":1}', '20', '2026-03-21 14:27:32', '2026-03-21 14:27:32', '2026-03-21 14:27:32');
INSERT INTO `guest_activities` (`id`, `hotel_id`, `guest_id`, `activity_type`, `description`, `metadata`, `recorded_by`, `activity_date`, `created_at`, `updated_at`) VALUES ('4', '1', '2', 'reservation_created', 'Regular reservation created: RES20260321142732298', '{\"reservation_id\":2,\"reservation_number\":\"RES20260321142732298\",\"is_walk_in\":\"0\",\"total_amount\":\"531000.00\",\"check_in\":\"2026-03-21\",\"check_out\":\"2026-03-24\"}', '20', '2026-03-21 14:27:32', '2026-03-21 14:27:32', '2026-03-21 14:27:32');

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` int(11) DEFAULT 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,
  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`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `guest_ledger` (`id`, `reservation_id`, `hotel_id`, `guest_id`, `transaction_date`, `description`, `reference_type`, `reference_id`, `debit`, `credit`, `balance`, `invoice_id`, `accounting_synced`, `created_by`, `created_at`, `updated_at`) VALUES ('1', '1', '1', '1', '2026-03-21 14:39:33', 'Room Charge - Room 001 (1 nights)', 'room_charge', '1', '247800.00', '0.00', '247800.00', '1', '0', '20', '2026-03-21 14:39:33', '2026-03-21 14:39:33');
INSERT INTO `guest_ledger` (`id`, `reservation_id`, `hotel_id`, `guest_id`, `transaction_date`, `description`, `reference_type`, `reference_id`, `debit`, `credit`, `balance`, `invoice_id`, `accounting_synced`, `created_by`, `created_at`, `updated_at`) VALUES ('2', '1', '1', '1', '2026-03-21 14:39:34', 'Initial check-in payment (Cash)', 'payment', '1', '0.00', '150000.00', '97800.00', NULL, '0', '20', '2026-03-21 14:39:34', '2026-03-21 14:39:34');

DROP TABLE IF EXISTS `guests`;
CREATE TABLE `guests` (
  `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) DEFAULT NULL,
  `status` varchar(255) NOT NULL DEFAULT 'active',
  `phone` varchar(50) 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(11) 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,
  `is_regular_guest` tinyint(1) DEFAULT 0,
  `is_walkin_guest` tinyint(1) DEFAULT 0,
  `is_reservation_guest` tinyint(1) DEFAULT 0,
  `is_service_guest` tinyint(1) DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `guests_email_unique` (`email`),
  KEY `idx_guest_service` (`is_service_guest`),
  KEY `guests_is_walkin_guest_index` (`is_walkin_guest`),
  KEY `guests_hotel_id_index` (`hotel_id`),
  CONSTRAINT `guests_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 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`, `last_visit_date`, `created_at`, `updated_at`, `deleted_at`, `is_regular_guest`, `is_walkin_guest`, `is_reservation_guest`, `is_service_guest`) VALUES ('1', '1', 'NABUUMA', 'ZELIDA', 'nabuuma.zelida.69bea9fac771f@guest.com', 'active', '+256702064000', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '0', NULL, '0', '1', '2026-03-21', '2026-03-21 14:23:54', '2026-03-21 14:23:55', NULL, '0', '0', '1', '0');
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`, `last_visit_date`, `created_at`, `updated_at`, `deleted_at`, `is_regular_guest`, `is_walkin_guest`, `is_reservation_guest`, `is_service_guest`) VALUES ('2', '1', 'NADUUTU', 'GRACE', 'grace@gmail.com', 'active', '+256702064111', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '0', NULL, '0', '1', '2026-03-21', '2026-03-21 14:27:32', '2026-03-21 14:27:32', NULL, '0', '0', '1', '0');

DROP TABLE IF EXISTS `hotels`;
CREATE TABLE `hotels` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `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,
  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`, `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`) VALUES ('1', 'POTERO HOTEL', 'potero-hotel-gxgkjf', 'nalumansi@gmail.com', '+256 704567898', 'ENTEBBE', 'BUSAMBAGA', 'Uganda', NULL, NULL, NULL, NULL, 'silver', '20', '2', '\"[\\\"rooms\\\",\\\"reservations\\\",\\\"accounting\\\",\\\"front_desk\\\",\\\"settings\\\"]\"', '150000.00', 'monthly', '2026-04-20 14:07:54', '2026-05-20 14:07:54', '2026-04-20 14:07:54', 'trial', NULL, '0', NULL, NULL, 'UGX', '18.00', '2026-03-21 14:07:55', '2026-03-21 14:07:55', NULL);

DROP TABLE IF EXISTS `housekeeping_schedule`;
CREATE TABLE `housekeeping_schedule` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `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 `housekeeping_staff`;
CREATE TABLE `housekeeping_staff` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `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`),
  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 ON UPDATE 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_guest_id_foreign` FOREIGN KEY (`guest_id`) REFERENCES `guests` (`id`) ON DELETE SET NULL,
  CONSTRAINT `in_out_logs_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `in_out_logs_invoice_id_foreign` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE SET NULL,
  CONSTRAINT `in_out_logs_processed_by_foreign` FOREIGN KEY (`processed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `in_out_logs_reservation_id_foreign` FOREIGN KEY (`reservation_id`) REFERENCES `reservations` (`id`) ON DELETE SET NULL,
  CONSTRAINT `in_out_logs_room_id_foreign` FOREIGN KEY (`room_id`) REFERENCES `rooms` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `in_out_logs` (`id`, `hotel_id`, `reservation_id`, `guest_id`, `room_id`, `type`, `logged_at`, `notes`, `amount_paid`, `payment_method`, `invoice_id`, `receipt_issued`, `processed_by`, `created_at`, `updated_at`) VALUES ('1', '1', '1', '1', '1', 'checkin', '2026-03-21 14:39:34', NULL, '0.00', NULL, '1', '1', '20', '2026-03-21 14:39:34', '2026-03-21 14:39:34');

DROP TABLE IF EXISTS `installment_payments`;
CREATE TABLE `installment_payments` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `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`),
  CONSTRAINT `installment_payments_installment_plan_id_foreign` FOREIGN KEY (`installment_plan_id`) REFERENCES `installment_plans` (`id`) ON DELETE CASCADE,
  CONSTRAINT `installment_payments_invoice_id_foreign` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE SET NULL
) 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,
  `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`),
  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_items`;
CREATE TABLE `inventory_items` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned DEFAULT 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,
  `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',
  `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_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,
  `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,
  `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_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 DEFAULT 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_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `inventory_transactions_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE,
  CONSTRAINT `inventory_transactions_product_id_foreign` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
) 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,
  `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`),
  CONSTRAINT `invoice_accounts_ibfk_1` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE,
  CONSTRAINT `invoice_accounts_ibfk_2` FOREIGN KEY (`account_id`) REFERENCES `old_accounts_backup` (`id`) ON DELETE CASCADE
) 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 DEFAULT 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,
  CONSTRAINT `invoice_items_invoice_id_foreign` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `invoice_items` (`id`, `hotel_id`, `invoice_id`, `description`, `quantity`, `unit_price`, `total_price`, `tax_rate`, `tax_amount`, `notes`, `created_at`, `updated_at`) VALUES ('3', '1', '1', 'Room Revenue', '1', '210000.00', '210000.00', '18.00', '37800.00', NULL, '2026-03-21 14:39:46', '2026-03-21 14:39:46');

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 `fk_invoices_service_request` FOREIGN KEY (`service_request_id`) REFERENCES `service_requests` (`id`) ON DELETE SET NULL,
  CONSTRAINT `invoices_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `invoices_installment_payment_id_foreign` FOREIGN KEY (`installment_payment_id`) REFERENCES `installment_payments` (`id`) ON DELETE SET NULL,
  CONSTRAINT `invoices_reservation_id_foreign` FOREIGN KEY (`reservation_id`) REFERENCES `reservations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `invoices` (`id`, `hotel_id`, `reservation_id`, `service_request_id`, `invoice_number`, `type`, `date`, `due_date`, `contact_type`, `contact_id`, `guest_id`, `account_id`, `total_amount`, `subtotal`, `currency`, `exchange_rate`, `notes`, `description`, `payment_status`, `installment_payment_id`, `tax_amount`, `amount_paid`, `balance_due`, `status`, `paid_at`, `received_by`, `created_by`, `module_source`, `source_id`, `created_at`, `updated_at`, `synced_to_accounting`, `synced_at`, `payment_method`) VALUES ('1', '1', '1', NULL, 'INV-20260321-000001', 'room_charge', '2026-03-21', '2026-03-22', 'App\\Models\\Guest', '1', NULL, NULL, '247800.00', '210000.00', 'UGX', '1.0000', 'Reservation', NULL, 'partially_paid', NULL, '37800.00', '150000.00', '97800.00', 'sent', NULL, NULL, NULL, 'front_desk', '1', '2026-03-21 14:39:33', '2026-03-21 14:39:34', '0', NULL, NULL);

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`),
  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`),
  CONSTRAINT `journal_entries_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3296 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `journal_entries` (`id`, `hotel_id`, `date`, `entry_number`, `je_id_reference`, `reference`, `description`, `total_amount`, `currency_code`, `exchange_rate`, `status`, `module_source`, `source_id`, `posted_at`, `company_id`, `fiscal_year_id`, `created_by`, `created_at`, `updated_at`) VALUES ('3295', '1', '2026-03-21', 'JE-202603-0001', NULL, 'PAY-20260321-0001', 'Initial payment', '150000.00', 'UGX', '1.0000', 'posted', 'payment', '1', '2026-03-21 14:39:34', NULL, NULL, '20', '2026-03-21 14:39:34', '2026-03-21 14:39:34');

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`),
  CONSTRAINT `journal_entry_items_account_id_foreign` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `journal_entry_items_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE,
  CONSTRAINT `journal_entry_items_journal_entry_id_foreign` FOREIGN KEY (`journal_entry_id`) REFERENCES `journal_entries` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6072 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `journal_entry_items` (`id`, `hotel_id`, `journal_entry_id`, `account_id`, `debit`, `credit`, `description`, `created_at`, `updated_at`) VALUES ('6070', '1', '3295', '4', '150000.00', '0.00', 'Cash received', '2026-03-21 14:39:34', '2026-03-21 14:39:34');
INSERT INTO `journal_entry_items` (`id`, `hotel_id`, `journal_entry_id`, `account_id`, `debit`, `credit`, `description`, `created_at`, `updated_at`) VALUES ('6071', '1', '3295', '14', '0.00', '150000.00', 'Payment applied to receivables', '2026-03-21 14:39:34', '2026-03-21 14:39:34');

DROP TABLE IF EXISTS `journal_transactions`;
CREATE TABLE `journal_transactions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `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`),
  CONSTRAINT `journal_transactions_account_id_foreign` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `journal_transactions_journal_entry_id_foreign` FOREIGN KEY (`journal_entry_id`) REFERENCES `journal_entries` (`id`) ON DELETE CASCADE
) 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,
  `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`),
  CONSTRAINT `journal_vouchers_approved_by_foreign` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`),
  CONSTRAINT `journal_vouchers_cancelled_by_foreign` FOREIGN KEY (`cancelled_by`) REFERENCES `users` (`id`),
  CONSTRAINT `journal_vouchers_credit_account_id_foreign` FOREIGN KEY (`credit_account_id`) REFERENCES `old_accounts_backup` (`id`),
  CONSTRAINT `journal_vouchers_debit_account_id_foreign` FOREIGN KEY (`debit_account_id`) REFERENCES `old_accounts_backup` (`id`),
  CONSTRAINT `journal_vouchers_prepared_by_foreign` FOREIGN KEY (`prepared_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `leads`;
CREATE TABLE `leads` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `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`)
) 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 DEFAULT 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,
  CONSTRAINT `leave_balances_leave_type_id_foreign` FOREIGN KEY (`leave_type_id`) REFERENCES `leave_types` (`id`) ON DELETE CASCADE,
  CONSTRAINT `leave_balances_staff_id_foreign` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`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 DEFAULT 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 AUTO_INCREMENT=10 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 DEFAULT 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 DEFAULT 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`)
) 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 DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `room_id` bigint(20) unsigned NOT 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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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`),
  CONSTRAINT `manufacturing_consumptions_ibfk_1` FOREIGN KEY (`manufacturing_order_id`) REFERENCES `manufacturing_orders` (`id`) ON DELETE CASCADE,
  CONSTRAINT `manufacturing_consumptions_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) 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`),
  CONSTRAINT `manufacturing_outputs_ibfk_1` FOREIGN KEY (`manufacturing_order_id`) REFERENCES `manufacturing_orders` (`id`) ON DELETE CASCADE,
  CONSTRAINT `manufacturing_outputs_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) 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 DEFAULT 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
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `menu_items`;
CREATE TABLE `menu_items` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned DEFAULT 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`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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=70 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');

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 `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 AUTO_INCREMENT=61 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('1', 'Cash', '1001', 'asset', NULL, '0.00', 'Cash on hand and in bank accounts', '1', '1', '2025-11-05 15:26:50', '2025-11-05 15:26:50');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('2', 'Accounts Receivable', '1002', 'asset', NULL, '0.00', 'Amounts owed by customers', '1', '1', '2025-11-05 15:26:50', '2025-11-05 15:26:50');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('3', 'Inventory', '1003', 'asset', NULL, '0.00', 'Goods available for sale', '1', '1', '2025-11-05 15:26:50', '2025-11-05 15:26:50');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('4', 'Equipment', '1004', 'asset', NULL, '0.00', 'Office and operational equipment', '1', '1', '2025-11-05 15:26:50', '2025-11-05 15:26:50');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('5', 'Accounts Payable', '2001', 'liability', NULL, '0.00', 'Amounts owed to suppliers', '1', '1', '2025-11-05 15:27:08', '2025-11-05 15:27:08');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('6', 'Loans Payable', '2002', 'liability', NULL, '0.00', 'Outstanding loans and borrowings', '1', '1', '2025-11-05 15:27:08', '2025-11-05 15:27:08');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('7', 'Accrued Expenses', '2003', 'liability', NULL, '0.00', 'Expenses incurred but not yet paid', '1', '1', '2025-11-05 15:27:08', '2025-11-05 15:27:08');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('8', 'Taxes Payable', '2004', 'liability', NULL, '0.00', 'Taxes owed to government', '1', '1', '2025-11-05 15:27:08', '2025-11-05 15:27:08');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('9', 'Owner\'s Capital', '3001', 'equity', NULL, '0.00', 'Initial investment by owner', '1', '1', '2025-11-05 15:27:25', '2025-11-05 15:27:25');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('10', 'Retained Earnings', '3002', 'equity', NULL, '0.00', 'Accumulated profits', '1', '1', '2025-11-05 15:27:25', '2025-11-05 15:27:25');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('11', 'Common Stock', '3003', 'equity', NULL, '0.00', 'Value of issued common shares', '1', '1', '2025-11-05 15:27:25', '2025-11-05 15:27:25');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('12', 'Drawing', '3004', 'equity', NULL, '0.00', 'Owner withdrawals', '1', '1', '2025-11-05 15:27:25', '2025-11-05 15:27:25');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('13', 'Room Revenue', '4001', 'revenue', NULL, '0.00', 'Income from room bookings', '1', '1', '2025-11-05 15:27:46', '2025-11-05 15:27:46');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('14', 'Food & Beverage Revenue', '4002', 'revenue', NULL, '0.00', 'Income from restaurant and bar', '1', '1', '2025-11-05 15:27:46', '2025-11-05 15:27:46');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('15', 'Service Revenue', '4003', 'revenue', NULL, '0.00', 'Income from additional services', '1', '1', '2025-11-05 15:27:46', '2025-11-05 15:27:46');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('16', 'Other Revenue', '4004', 'revenue', NULL, '0.00', 'Miscellaneous income sources', '1', '1', '2025-11-05 15:27:46', '2025-11-05 15:27:46');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('17', 'Salaries & Wages', '5001', 'expense', NULL, '0.00', 'Employee compensation', '1', '1', '2025-11-05 15:28:11', '2025-11-05 15:28:11');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('18', 'Utilities Expense', '5002', 'expense', NULL, '0.00', 'Electricity, water, internet costs', '1', '1', '2025-11-05 15:28:11', '2025-11-05 15:28:11');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('19', 'Supplies Expense', '5003', 'expense', NULL, '0.00', 'Office and operational supplies', '1', '1', '2025-11-05 15:28:11', '2025-11-05 15:28:11');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('20', 'Maintenance Expense', '5004', 'expense', NULL, '0.00', 'Repairs and maintenance costs', '1', '1', '2025-11-05 15:28:11', '2025-11-05 15:28:11');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('21', 'Current Assets', '1000', 'asset', NULL, '0.00', 'Short-term assets', '1', '1', '2025-11-05 15:29:17', '2025-11-05 15:29:17');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('22', 'Cash and Cash Equivalents', '1010', 'asset', '1', '0.00', 'Liquid assets', '1', '1', '2025-11-05 15:29:17', '2025-11-05 15:29:17');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('23', 'Accounts Receivable', '1020', 'asset', '1', '0.00', 'Customer debts', '1', '1', '2025-11-05 15:29:17', '2025-11-05 15:29:17');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('24', 'Inventory', '1030', 'asset', '1', '0.00', 'Stock for sale', '1', '1', '2025-11-05 15:29:17', '2025-11-05 15:29:17');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('25', 'Fixed Assets', '1100', 'asset', NULL, '0.00', 'Long-term assets', '1', '1', '2025-11-05 15:29:17', '2025-11-05 15:29:17');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('26', 'Property and Equipment', '1110', 'asset', '5', '0.00', 'Buildings and machinery', '1', '1', '2025-11-05 15:29:17', '2025-11-05 15:29:17');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('27', 'Furniture and Fixtures', '1120', 'asset', '5', '0.00', 'Office furniture', '1', '1', '2025-11-05 15:29:17', '2025-11-05 15:29:17');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('28', 'Vehicles', '1130', 'asset', '5', '0.00', 'Company vehicles', '1', '1', '2025-11-05 15:29:17', '2025-11-05 15:29:17');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('29', 'Current Liabilities', '2000', 'liability', NULL, '0.00', 'Short-term debts', '1', '1', '2025-11-05 15:29:45', '2025-11-05 15:29:45');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('30', 'Accounts Payable', '2010', 'liability', '9', '0.00', 'Supplier debts', '1', '1', '2025-11-05 15:29:45', '2025-11-05 15:29:45');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('31', 'Short-term Loans', '2020', 'liability', '9', '0.00', 'Short-term borrowings', '1', '1', '2025-11-05 15:29:45', '2025-11-05 15:29:45');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('32', 'Accrued Expenses', '2030', 'liability', '9', '0.00', 'Unpaid expenses', '1', '1', '2025-11-05 15:29:45', '2025-11-05 15:29:45');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('33', 'Long-term Liabilities', '2100', 'liability', NULL, '0.00', 'Long-term debts', '1', '1', '2025-11-05 15:29:45', '2025-11-05 15:29:45');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('34', 'Long-term Loans', '2110', 'liability', '13', '0.00', 'Long-term borrowings', '1', '1', '2025-11-05 15:29:45', '2025-11-05 15:29:45');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('35', 'Mortgage Payable', '2120', 'liability', '13', '0.00', 'Property mortgage', '1', '1', '2025-11-05 15:29:45', '2025-11-05 15:29:45');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('37', 'Owner\'s Equity', '3000', 'equity', NULL, '0.00', 'Owner investment', '1', '1', '2025-11-05 15:32:01', '2025-11-05 15:32:01');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('38', 'Capital Stock', '3010', 'equity', '17', '0.00', 'Issued capital', '1', '1', '2025-11-05 15:32:01', '2025-11-05 15:32:01');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('39', 'Retained Earnings', '3020', 'equity', '17', '0.00', 'Accumulated profits', '1', '1', '2025-11-05 15:32:01', '2025-11-05 15:32:01');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('40', 'Drawing Account', '3030', 'equity', '17', '0.00', 'Owner withdrawals', '1', '1', '2025-11-05 15:32:01', '2025-11-05 15:32:01');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('41', 'Operating Revenue', '4000', 'revenue', NULL, '0.00', 'Main business income', '1', '1', '2025-11-05 15:32:31', '2025-11-05 15:32:31');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('42', 'Room Sales', '4010', 'revenue', '21', '0.00', 'Accommodation income', '1', '1', '2025-11-05 15:32:31', '2025-11-05 15:32:31');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('43', 'Food Sales', '4020', 'revenue', '21', '0.00', 'Restaurant income', '1', '1', '2025-11-05 15:32:31', '2025-11-05 15:32:31');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('44', 'Beverage Sales', '4030', 'revenue', '21', '0.00', 'Bar income', '1', '1', '2025-11-05 15:32:31', '2025-11-05 15:32:31');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('45', 'Other Revenue', '4100', 'revenue', NULL, '0.00', 'Additional income', '1', '1', '2025-11-05 15:32:31', '2025-11-05 15:32:31');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('46', 'Service Charges', '4110', 'revenue', '25', '0.00', 'Service fees', '1', '1', '2025-11-05 15:32:31', '2025-11-05 15:32:31');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('47', 'Interest Income', '4120', 'revenue', '25', '0.00', 'Investment income', '1', '1', '2025-11-05 15:32:31', '2025-11-05 15:32:31');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('48', 'Miscellaneous Income', '4130', 'revenue', '25', '0.00', 'Other income', '1', '1', '2025-11-05 15:32:31', '2025-11-05 15:32:31');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('49', 'Operating Expenses', '5000', 'expense', NULL, '0.00', 'Business operation costs', '1', '1', '2025-11-05 15:33:05', '2025-11-05 15:33:05');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('50', 'Salaries Expense', '5010', 'expense', '29', '0.00', 'Employee wages', '1', '1', '2025-11-05 15:33:05', '2025-11-05 15:33:05');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('51', 'Rent Expense', '5020', 'expense', '29', '0.00', 'Property rental', '1', '1', '2025-11-05 15:33:05', '2025-11-05 15:33:05');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('52', 'Utilities Expense', '5030', 'expense', '29', '0.00', 'Bills and services', '1', '1', '2025-11-05 15:33:05', '2025-11-05 15:33:05');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('53', 'Cost of Goods Sold', '5100', 'expense', NULL, '0.00', 'Direct costs', '1', '1', '2025-11-05 15:33:05', '2025-11-05 15:33:05');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('54', 'Food Cost', '5110', 'expense', '33', '0.00', 'Food inventory cost', '1', '1', '2025-11-05 15:33:05', '2025-11-05 15:33:05');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('55', 'Beverage Cost', '5120', 'expense', '33', '0.00', 'Beverage inventory cost', '1', '1', '2025-11-05 15:33:05', '2025-11-05 15:33:05');
INSERT INTO `old_accounts_backup` (`id`, `name`, `code`, `type`, `parent_id`, `balance`, `description`, `is_active`, `active`, `created_at`, `updated_at`) VALUES ('56', 'Supplies Cost', '5130', 'expense', '33', '0.00', 'Operating supplies', '1', '1', '2025-11-05 15:33:05', '2025-11-05 15:33:05');

DROP TABLE IF EXISTS `order_items`;
CREATE TABLE `order_items` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned DEFAULT 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_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
  CONSTRAINT `order_items_ibfk_2` FOREIGN KEY (`menu_item_id`) REFERENCES `menu_items` (`id`)
) 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 DEFAULT 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,
  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`),
  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,
  `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`)
) 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,
  `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`),
  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_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_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `payments_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `payments_invoice_id_foreign` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE,
  CONSTRAINT `payments_received_by_foreign` FOREIGN KEY (`received_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `payments_reservation_id_foreign` FOREIGN KEY (`reservation_id`) REFERENCES `reservations` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `payments` (`id`, `hotel_id`, `payment_number`, `invoice_id`, `reservation_id`, `amount`, `payment_date`, `receipt_number`, `payment_method`, `reference`, `transaction_id`, `notes`, `received_by`, `created_by`, `status`, `paid_at`, `created_at`, `updated_at`, `accounting_synced`, `accounting_sync_at`) VALUES ('1', '1', 'PAY-20260321-0001', '1', NULL, '150000.00', NULL, NULL, 'cash', 'PAY-1774103973-1', NULL, 'Initial payment', NULL, NULL, 'completed', '2026-03-21 14:39:33', '2026-03-21 14:39:33', '2026-03-21 14:39:34', '1', '2026-03-21 14:39:34');

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 DEFAULT 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`)
) 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,
  `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`),
  CONSTRAINT `fk_pos_order_items_order_id` FOREIGN KEY (`order_id`) REFERENCES `pos_orders` (`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 DEFAULT 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,
  `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 `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=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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 ON UPDATE CASCADE,
  CONSTRAINT `receipts_ibfk_1` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE,
  CONSTRAINT `receipts_ibfk_2` FOREIGN KEY (`service_request_id`) REFERENCES `service_requests` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `receipts` (`id`, `hotel_id`, `receipt_number`, `invoice_id`, `service_request_id`, `guest_id`, `amount_paid`, `payment_date`, `receipt_date`, `payment_method`, `payment_status`, `notes`, `received_by`, `created_at`, `updated_at`) VALUES ('1', '1', 'RCPT-20260321-7294', '1', NULL, '1', '150000.00', '2026-03-21 14:39:34', '2026-03-21 17:39:34', 'cash', 'paid', 'Initial payment', '20', '2026-03-21 14:39:34', '2026-03-21 14:39:34');

DROP TABLE IF EXISTS `report_templates`;
CREATE TABLE `report_templates` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned DEFAULT 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 DEFAULT 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` int(10) unsigned NOT NULL,
  `reservation_number` varchar(255) NOT NULL,
  `guest_id` bigint(20) unsigned NOT NULL,
  `room_id` bigint(20) unsigned NOT NULL,
  `assigned_staff_id` bigint(20) unsigned DEFAULT NULL,
  `assigned_staff` varchar(255) DEFAULT NULL,
  `check_in` date NOT NULL,
  `actual_check_in` timestamp NULL DEFAULT NULL,
  `check_out` date NOT NULL,
  `actual_check_out` timestamp NULL DEFAULT NULL,
  `adults` int(11) NOT NULL DEFAULT 1,
  `children` int(11) NOT NULL DEFAULT 0,
  `number_of_guests` int(11) DEFAULT 1,
  `total_amount` decimal(10,2) NOT NULL,
  `subtotal` decimal(15,2) NOT NULL DEFAULT 0.00,
  `tax_amount` decimal(15,2) NOT NULL DEFAULT 0.00,
  `deposit_amount` decimal(10,2) 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',
  `cancelled_at` timestamp NULL DEFAULT NULL,
  `cancellation_reason` text DEFAULT NULL,
  `cancellation_notes` text DEFAULT NULL,
  `cancelled_by` bigint(20) unsigned DEFAULT NULL,
  `payment_status` enum('pending','paid','partially_paid','refunded') NOT NULL DEFAULT 'pending',
  `special_requests` text DEFAULT NULL,
  `created_by` bigint(20) unsigned NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `checked_in_at` timestamp NULL DEFAULT NULL,
  `checked_out_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,
  `synced_to_accounting` tinyint(1) NOT NULL DEFAULT 0,
  `accounting_sync_attempts` int(11) NOT NULL DEFAULT 0,
  `accounting_sync_error` text DEFAULT NULL,
  `is_walk_in` tinyint(1) DEFAULT 0,
  `walk_in_number` varchar(255) DEFAULT NULL,
  `source` varchar(50) DEFAULT 'website',
  `payment_method` varchar(255) DEFAULT NULL,
  `installment_plan_id` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `reservations_reservation_number_unique` (`reservation_number`),
  KEY `reservations_created_by_foreign` (`created_by`),
  KEY `fk_reservations_assigned_staff` (`assigned_staff_id`),
  KEY `fk_reservations_guest` (`guest_id`),
  KEY `fk_reservations_room` (`room_id`),
  KEY `reservations_is_walk_in_index` (`is_walk_in`),
  KEY `reservations_walk_in_number_index` (`walk_in_number`),
  KEY `reservations_installment_plan_id_index` (`installment_plan_id`),
  KEY `reservations_status_index` (`status`),
  KEY `reservations_check_in_check_out_index` (`check_in`,`check_out`),
  KEY `reservations_cancelled_by_foreign` (`cancelled_by`),
  KEY `reservations_hotel_id_index` (`hotel_id`),
  CONSTRAINT `fk_reservations_assigned_staff` FOREIGN KEY (`assigned_staff_id`) REFERENCES `staff` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_reservations_guest` FOREIGN KEY (`guest_id`) REFERENCES `guests` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_reservations_room` FOREIGN KEY (`room_id`) REFERENCES `rooms` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `reservations_cancelled_by_foreign` FOREIGN KEY (`cancelled_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  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_ibfk_1` FOREIGN KEY (`guest_id`) REFERENCES `guests` (`id`),
  CONSTRAINT `reservations_ibfk_2` FOREIGN KEY (`room_id`) REFERENCES `rooms` (`id`),
  CONSTRAINT `reservations_installment_plan_id_foreign` FOREIGN KEY (`installment_plan_id`) REFERENCES `installment_plans` (`id`) ON DELETE SET NULL,
  CONSTRAINT `reservations_room_id_foreign` FOREIGN KEY (`room_id`) REFERENCES `rooms` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `reservations` (`id`, `hotel_id`, `reservation_number`, `guest_id`, `room_id`, `assigned_staff_id`, `assigned_staff`, `check_in`, `actual_check_in`, `check_out`, `actual_check_out`, `adults`, `children`, `number_of_guests`, `total_amount`, `subtotal`, `tax_amount`, `deposit_amount`, `paid_amount`, `status`, `cancelled_at`, `cancellation_reason`, `cancellation_notes`, `cancelled_by`, `payment_status`, `special_requests`, `created_by`, `created_at`, `updated_at`, `checked_in_at`, `checked_out_at`, `accounting_synced`, `accounting_sync_at`, `accounting_synced_at`, `synced_to_accounting`, `accounting_sync_attempts`, `accounting_sync_error`, `is_walk_in`, `walk_in_number`, `source`, `payment_method`, `installment_plan_id`) VALUES ('1', '1', 'RES20260321142354175', '1', '1', NULL, NULL, '2026-03-21', NULL, '2026-03-22', NULL, '1', '0', '1', '247800.00', '0.00', '0.00', '0.00', '0.00', 'checked_in', NULL, NULL, NULL, NULL, 'pending', NULL, '20', '2026-03-21 14:23:54', '2026-03-21 14:39:34', NULL, NULL, '0', NULL, NULL, '0', '0', NULL, '0', NULL, 'website', NULL, NULL);
INSERT INTO `reservations` (`id`, `hotel_id`, `reservation_number`, `guest_id`, `room_id`, `assigned_staff_id`, `assigned_staff`, `check_in`, `actual_check_in`, `check_out`, `actual_check_out`, `adults`, `children`, `number_of_guests`, `total_amount`, `subtotal`, `tax_amount`, `deposit_amount`, `paid_amount`, `status`, `cancelled_at`, `cancellation_reason`, `cancellation_notes`, `cancelled_by`, `payment_status`, `special_requests`, `created_by`, `created_at`, `updated_at`, `checked_in_at`, `checked_out_at`, `accounting_synced`, `accounting_sync_at`, `accounting_synced_at`, `synced_to_accounting`, `accounting_sync_attempts`, `accounting_sync_error`, `is_walk_in`, `walk_in_number`, `source`, `payment_method`, `installment_plan_id`) VALUES ('2', '1', 'RES20260321142732298', '2', '2', NULL, NULL, '2026-03-21', NULL, '2026-03-24', NULL, '1', '0', '1', '531000.00', '0.00', '0.00', '0.00', '0.00', 'pending', NULL, NULL, NULL, NULL, 'pending', NULL, '20', '2026-03-21 14:27:32', '2026-03-21 14:27:32', NULL, NULL, '0', NULL, NULL, '0', '0', NULL, '0', NULL, 'website', NULL, NULL);

DROP TABLE IF EXISTS `room_amenities`;
CREATE TABLE `room_amenities` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `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 `room_maintenance`;
CREATE TABLE `room_maintenance` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `reason` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) 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 DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `code` varchar(50) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `default_capacity` int(11) NOT NULL DEFAULT 2,
  `max_capacity` int(11) 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`),
  UNIQUE KEY `room_types_code_unique` (`code`),
  KEY `room_types_hotel_id_index` (`hotel_id`),
  CONSTRAINT `room_types_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 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 ('4', '1', 'IMELDA', NULL, NULL, '2', NULL, '1', '0', NULL, '2026-03-21 14:18:40', '2026-03-21 14:18:40', NULL);

DROP TABLE IF EXISTS `rooms`;
CREATE TABLE `rooms` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned DEFAULT NULL,
  `room_number` varchar(255) NOT NULL,
  `room_name` varchar(255) DEFAULT NULL,
  `floor` varchar(10) DEFAULT NULL,
  `room_type_id` bigint(20) unsigned DEFAULT NULL,
  `category` varchar(100) DEFAULT NULL,
  `price_per_night` decimal(10,2) NOT NULL,
  `description` text DEFAULT NULL,
  `capacity` int(11) NOT NULL,
  `status` enum('available','occupied','cleaning','maintenance','reserved') NOT NULL DEFAULT 'available',
  `housekeeping_status` enum('clean','dirty','in_progress','maintenance','checked_out','occupied') 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 `fk_rooms_room_type` (`room_type_id`),
  KEY `idx_rooms_housekeeping_status` (`housekeeping_status`),
  KEY `idx_rooms_last_checked_out` (`last_checked_out_at`),
  KEY `idx_rooms_last_cleaned` (`last_cleaned_at`),
  KEY `idx_rooms_hotel_id` (`hotel_id`),
  KEY `idx_rooms_created_at` (`created_at`),
  CONSTRAINT `fk_rooms_room_type` FOREIGN KEY (`room_type_id`) REFERENCES `room_types` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `rooms_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE,
  CONSTRAINT `rooms_ibfk_1` FOREIGN KEY (`room_type_id`) REFERENCES `room_types` (`id`),
  CONSTRAINT `rooms_room_type_id_foreign` FOREIGN KEY (`room_type_id`) REFERENCES `room_types` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `rooms` (`id`, `hotel_id`, `room_number`, `room_name`, `floor`, `room_type_id`, `category`, `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', NULL, '1', '4', 'IMELDA', '210000.00', NULL, '2', 'occupied', 'occupied', '1', '\"[\\\"Wi-Fi\\\",\\\"Balcony\\\",\\\"City View\\\"]\"', '\"[\\\"room-images\\\\\\/7EUXxtHCxdH3wf9CYpJ1T8xpcKCqp4JtTZly3DJy.jpg\\\"]\"', '2026-03-21 14:20:51', '2026-03-21 14:39:34', NULL, NULL, NULL);
INSERT INTO `rooms` (`id`, `hotel_id`, `room_number`, `room_name`, `floor`, `room_type_id`, `category`, `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 ('2', '1', '002', NULL, '2', '4', 'standard', '150000.00', NULL, '2', 'reserved', 'clean', '1', '\"[\\\"Mini Bar\\\",\\\"Safe\\\"]\"', '\"[\\\"room-images\\\\\\/taLyWkdObszkNgvPITgotxyr0pkC4b6sq5WBr610.webp\\\"]\"', '2026-03-21 14:25:11', '2026-03-21 14:27:32', NULL, NULL, NULL);
INSERT INTO `rooms` (`id`, `hotel_id`, `room_number`, `room_name`, `floor`, `room_type_id`, `category`, `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 ('3', '1', '003', NULL, '3', '4', 'Deluxe', '180000.00', NULL, '2', 'available', 'clean', '1', '\"[\\\"TV\\\",\\\"Hair Dryer\\\",\\\"Desk\\\"]\"', '\"[\\\"room-images\\\\\\/rR6vU7he905JvtviW6DskeM199FKKq7bSQU4vOZY.webp\\\"]\"', '2026-03-21 14:29:54', '2026-03-21 14:29:54', 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`),
  CONSTRAINT `routing_operations_ibfk_1` FOREIGN KEY (`routing_id`) REFERENCES `routing` (`id`) ON DELETE CASCADE,
  CONSTRAINT `routing_operations_ibfk_2` FOREIGN KEY (`workcenter_id`) REFERENCES `work_centers` (`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,
  `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`)
) 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 DEFAULT 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`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `service_logs`;
CREATE TABLE `service_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `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`),
  CONSTRAINT `service_logs_guest_id_foreign` FOREIGN KEY (`guest_id`) REFERENCES `guests` (`id`) ON DELETE CASCADE,
  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,
  `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`),
  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 `fk_service_requests_guest_id` FOREIGN KEY (`guest_id`) REFERENCES `guests` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_service_requests_invoice` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_service_requests_receipt` FOREIGN KEY (`receipt_id`) REFERENCES `receipts` (`id`) ON DELETE SET NULL,
  CONSTRAINT `service_requests_assigned_by_foreign` FOREIGN KEY (`assigned_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `service_requests_assigned_staff_id_foreign` FOREIGN KEY (`assigned_staff_id`) REFERENCES `staff` (`id`) ON DELETE SET NULL,
  CONSTRAINT `service_requests_cancelled_by_foreign` FOREIGN KEY (`cancelled_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `service_requests_completed_by_foreign` FOREIGN KEY (`completed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `service_requests_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `service_requests_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `service_requests_ibfk_2` FOREIGN KEY (`service_id`) REFERENCES `services` (`id`) ON DELETE CASCADE,
  CONSTRAINT `service_requests_invoice_id_foreign` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE SET NULL,
  CONSTRAINT `service_requests_receipt_id_foreign` FOREIGN KEY (`receipt_id`) REFERENCES `receipts` (`id`) ON DELETE SET NULL,
  CONSTRAINT `service_requests_reservation_id_foreign` FOREIGN KEY (`reservation_id`) REFERENCES `reservations` (`id`),
  CONSTRAINT `service_requests_room_id_foreign` FOREIGN KEY (`room_id`) REFERENCES `rooms` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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) NOT NULL,
  `description` text DEFAULT NULL,
  `price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `duration` int(11) DEFAULT NULL COMMENT 'Duration in minutes',
  `is_active` tinyint(1) DEFAULT 1 COMMENT '1 = active, 0 = inactive',
  `is_available` tinyint(1) 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=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

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,
  `profile_picture` varchar(255) DEFAULT NULL,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `middle_name` varchar(100) DEFAULT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `residence` text DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `state` varchar(100) DEFAULT NULL,
  `zip_code` varchar(20) DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `gender` enum('male','female','other') DEFAULT NULL,
  `nin` varchar(50) DEFAULT NULL,
  `position_id` int(11) DEFAULT NULL,
  `department_id` int(11) DEFAULT NULL,
  `hire_date` date NOT NULL,
  `salary` decimal(10,2) DEFAULT NULL,
  `tin_number` varchar(50) DEFAULT NULL,
  `nssf_number` varchar(50) DEFAULT NULL,
  `bank_name` varchar(100) DEFAULT NULL,
  `bank_account` varchar(50) DEFAULT NULL,
  `status` enum('active','inactive','suspended','terminated') DEFAULT 'active',
  `service_specialization` varchar(255) DEFAULT NULL,
  `max_assignments` int(11) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `additional_notes` text DEFAULT NULL,
  `is_available` tinyint(1) DEFAULT 1,
  `emergency_contact_name` varchar(100) DEFAULT NULL,
  `emergency_contact_phone` varchar(20) DEFAULT NULL,
  `emergency_contact_relationship` varchar(100) DEFAULT NULL,
  `next_of_kin_name` varchar(100) DEFAULT NULL,
  `next_of_kin_relationship` varchar(50) DEFAULT NULL,
  `next_of_kin_phone` varchar(20) DEFAULT NULL,
  `next_of_kin_address` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `employee_id` (`employee_id`),
  KEY `staff_hotel_id_index` (`hotel_id`),
  CONSTRAINT `staff_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `staff_departments`;
CREATE TABLE `staff_departments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned DEFAULT 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=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `staff_positions`;
CREATE TABLE `staff_positions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned DEFAULT 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=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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,
  `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,
  CONSTRAINT `subscription_payments_received_by_foreign` FOREIGN KEY (`received_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `subscription_payments_subscription_id_foreign` FOREIGN KEY (`subscription_id`) REFERENCES `subscriptions` (`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
) 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-202603-0001', '1', 'silver', '150000.00', 'monthly', '2026-04-20', '2026-05-20', '2026-03-21', '2026-04-20', 'trial', '20', '2', '\"[\\\"rooms\\\",\\\"reservations\\\",\\\"accounting\\\",\\\"front_desk\\\",\\\"settings\\\"]\"', 'Auto-created from hotel registration', NULL, '2026-03-21 14:07:55', '2026-03-21 14:07:55');

DROP TABLE IF EXISTS `survey_answers`;
CREATE TABLE `survey_answers` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned DEFAULT 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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `survey_invitations`;
CREATE TABLE `survey_invitations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned DEFAULT 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 DEFAULT NULL,
  `survey_id` bigint(20) unsigned NOT NULL,
  `question` text NOT NULL,
  `type` enum('multiple_choice','rating','text','dropdown','checkbox','yes_no') 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_foreign` (`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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `survey_responses`;
CREATE TABLE `survey_responses` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned DEFAULT NULL,
  `survey_id` bigint(20) unsigned NOT NULL,
  `staff_id` bigint(20) unsigned DEFAULT NULL,
  `guest_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_staff_id_index` (`staff_id`),
  KEY `survey_responses_hotel_id_foreign` (`hotel_id`),
  CONSTRAINT `fk_survey_responses_staff` FOREIGN KEY (`staff_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `survey_responses_guest_id_foreign` FOREIGN KEY (`guest_id`) REFERENCES `guests` (`id`) ON DELETE CASCADE,
  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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `surveys`;
CREATE TABLE `surveys` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `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_foreign` (`hotel_id`),
  CONSTRAINT `surveys_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 `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 DEFAULT 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`)
) 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,
  `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`)
) 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 DEFAULT 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`)
) 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(255) NOT NULL DEFAULT 'light',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`),
  KEY `idx_users_hotel_id` (`hotel_id`),
  KEY `idx_users_created_at` (`created_at`),
  CONSTRAINT `users_hotel_id_foreign` FOREIGN KEY (`hotel_id`) REFERENCES `hotels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=21 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 ('20', '1', 'NALUMANSI', 'HAVEN', 'nalumansi@gmail.com', NULL, 'director', '2026-03-21 14:12:11', '$2y$12$LPOnlxDqWBdi/EXXCztwN.vhFSOIuRUdS4MQiiJLLpg64ZhC09B.S', NULL, '2026-03-21 14:17:22', '127.0.0.1', 'light', '2026-03-21 14:12:11', '2026-03-21 14:17:22');

DROP TABLE IF EXISTS `vendors`;
CREATE TABLE `vendors` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` bigint(20) unsigned DEFAULT NULL,
  `category_id` bigint(20) unsigned DEFAULT 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_category_id_foreign` (`category_id`),
  KEY `vendors_hotel_id_index` (`hotel_id`),
  CONSTRAINT `vendors_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL
) 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;