-- Create table for live booking information synced from Ezee API
-- This table stores booking data fetched every 15 minutes from Ezee PMS

CREATE TABLE IF NOT EXISTS `live_bookings` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `ReservationNo` VARCHAR(255) NOT NULL,
  `VoucherNo` VARCHAR(255) DEFAULT NULL,
  `BookingStatus` VARCHAR(100) DEFAULT NULL,
  `Source` VARCHAR(255) DEFAULT NULL,
  `Room` VARCHAR(255) DEFAULT NULL,
  `RoomShortCode` VARCHAR(100) DEFAULT NULL,
  `GuestName` VARCHAR(255) DEFAULT NULL,
  `Mobile` VARCHAR(50) DEFAULT NULL,
  `Email` VARCHAR(255) DEFAULT NULL,
  `Adult` VARCHAR(10) DEFAULT NULL,
  `Child` VARCHAR(10) DEFAULT NULL,
  `NoOfGuest` INT(11) DEFAULT NULL,
  `ReservationDate` VARCHAR(50) DEFAULT NULL,
  `ArrivalDate` VARCHAR(50) DEFAULT NULL,
  `DepartureDate` VARCHAR(50) DEFAULT NULL,
  `NoOfNights` VARCHAR(10) DEFAULT NULL,
  `TotalExclusivTax` DECIMAL(15,2) DEFAULT NULL,
  `TotalTax` DECIMAL(15,2) DEFAULT NULL,
  `OtherRevenueExclusiveTax` DECIMAL(15,2) DEFAULT NULL,
  `OtherRevenueInclusiveTax` DECIMAL(15,2) DEFAULT NULL,
  `TACommision` DECIMAL(15,2) DEFAULT NULL,
  `DueAmount` DECIMAL(15,2) DEFAULT NULL,
  `Deposit` DECIMAL(15,2) DEFAULT NULL,
  `Status` VARCHAR(100) DEFAULT NULL,
  `TransactionStatus` VARCHAR(100) DEFAULT NULL,
  `TotalInclusiveTax` DECIMAL(15,2) DEFAULT NULL,
  `FolioNo` VARCHAR(255) DEFAULT NULL,
  `Country` VARCHAR(100) DEFAULT NULL,
  `Phone` VARCHAR(50) DEFAULT NULL,
  `Address` TEXT DEFAULT NULL,
  `Salutation` VARCHAR(20) DEFAULT NULL,
  `FirstName` VARCHAR(255) DEFAULT NULL,
  `LastName` VARCHAR(255) DEFAULT NULL,
  `ReservationGuarantee` VARCHAR(255) DEFAULT NULL,
  `CancelDate` VARCHAR(50) DEFAULT NULL,
  `BaseRateExclusiveTax` TEXT DEFAULT NULL COMMENT 'JSON stored as TEXT',
  `BaseRateInclusiveTax` TEXT DEFAULT NULL COMMENT 'JSON stored as TEXT',
  `ExtraCharges` TEXT DEFAULT NULL COMMENT 'JSON stored as TEXT',
  `Createdatetime` VARCHAR(50) DEFAULT NULL,
  `SyncDate` VARCHAR(50) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_reservation_no` (`ReservationNo`),
  KEY `idx_booking_status` (`BookingStatus`),
  KEY `idx_arrival_date` (`ArrivalDate`),
  KEY `idx_departure_date` (`DepartureDate`),
  KEY `idx_email` (`Email`),
  KEY `idx_guest_name` (`GuestName`),
  KEY `idx_sync_date` (`SyncDate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

