-- ============================================================
-- HOSTIVA v6 — HOTEL MODE MIGRATION
-- Adds hotel-specific tables on top of existing STR schema.
-- Safe & additive: existing STR data unaffected.
-- Run: mysql -u root -p hostiva < database/migrations_005_v6_hotel_mode.sql
-- ============================================================

SET FOREIGN_KEY_CHECKS=0;

-- ---------- PROPERTIES: add property_mode flag ----------
-- 'str' = single-unit listing (existing behaviour), 'hotel' = multi-room hotel
ALTER TABLE `properties`
  ADD COLUMN `property_mode` ENUM('str','hotel') DEFAULT 'str' AFTER `type`,
  ADD COLUMN `total_rooms` INT DEFAULT 0 AFTER `max_guests`,
  ADD COLUMN `check_in_time` TIME DEFAULT '14:00:00' AFTER `description`,
  ADD COLUMN `check_out_time` TIME DEFAULT '11:00:00' AFTER `check_in_time`,
  ADD COLUMN `tax_rate` DECIMAL(5,2) DEFAULT 0 AFTER `check_out_time`,
  ADD COLUMN `service_charge` DECIMAL(5,2) DEFAULT 0 AFTER `tax_rate`,
  ADD INDEX `idx_property_mode` (`property_mode`);

-- ---------- ROOM TYPES (hotel mode) ----------
CREATE TABLE IF NOT EXISTS `room_types` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `property_id` INT NOT NULL,
  `code` VARCHAR(20) NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `description` TEXT,
  `bed_type` ENUM('single','double','queen','king','twin','sofa') DEFAULT 'double',
  `max_occupancy` INT DEFAULT 2,
  `base_rate` DECIMAL(10,2) NOT NULL DEFAULT 0,
  `weekend_rate` DECIMAL(10,2) DEFAULT NULL,
  `size_sqm` INT DEFAULT NULL,
  `amenities` TEXT,
  `cover_image` VARCHAR(255) DEFAULT NULL,
  `sort_order` INT DEFAULT 0,
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_room_type_code` (`property_id`,`code`),
  KEY `idx_rt_property` (`property_id`),
  FOREIGN KEY (`property_id`) REFERENCES `properties`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- ROOMS (individual physical rooms) ----------
CREATE TABLE IF NOT EXISTS `rooms` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `property_id` INT NOT NULL,
  `room_type_id` INT NOT NULL,
  `room_number` VARCHAR(20) NOT NULL,
  `floor` VARCHAR(20) DEFAULT NULL,
  `building` VARCHAR(50) DEFAULT NULL,
  `housekeeping_status` ENUM('clean','dirty','inspected','maintenance','out_of_order') DEFAULT 'clean',
  `occupancy_status` ENUM('vacant','occupied','reserved','blocked') DEFAULT 'vacant',
  `notes` TEXT,
  `last_cleaned_at` DATETIME DEFAULT NULL,
  `last_cleaned_by` INT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME DEFAULT NULL,
  UNIQUE KEY `uq_room_number` (`property_id`,`room_number`),
  KEY `idx_room_property` (`property_id`),
  KEY `idx_room_type` (`room_type_id`),
  KEY `idx_room_status` (`housekeeping_status`,`occupancy_status`),
  FOREIGN KEY (`property_id`) REFERENCES `properties`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`room_type_id`) REFERENCES `room_types`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`last_cleaned_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- BOOKINGS: link to room (hotel) + folio fields ----------
ALTER TABLE `bookings`
  ADD COLUMN `room_id` INT DEFAULT NULL AFTER `property_id`,
  ADD COLUMN `room_type_id` INT DEFAULT NULL AFTER `room_id`,
  ADD COLUMN `rate_plan_id` INT DEFAULT NULL AFTER `room_type_id`,
  ADD COLUMN `meal_plan` ENUM('none','breakfast','half_board','full_board','all_inclusive') DEFAULT 'none' AFTER `rate_plan_id`,
  ADD COLUMN `extras_total` DECIMAL(10,2) DEFAULT 0 AFTER `cleaning_fee`,
  ADD COLUMN `booking_type` ENUM('individual','group','corporate','walk_in') DEFAULT 'individual' AFTER `source`,
  ADD COLUMN `group_id` INT DEFAULT NULL AFTER `booking_type`,
  ADD COLUMN `corporate_id` INT DEFAULT NULL AFTER `group_id`,
  ADD INDEX `idx_booking_room` (`room_id`),
  ADD INDEX `idx_booking_group` (`group_id`),
  ADD INDEX `idx_booking_corporate` (`corporate_id`);

-- ---------- HOUSEKEEPING TASKS ----------
CREATE TABLE IF NOT EXISTS `housekeeping_tasks` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `room_id` INT NOT NULL,
  `task_type` ENUM('checkout_clean','daily_clean','deep_clean','inspection','maintenance') NOT NULL,
  `priority` ENUM('low','normal','high','urgent') DEFAULT 'normal',
  `status` ENUM('pending','in_progress','completed','skipped') DEFAULT 'pending',
  `assigned_to` INT DEFAULT NULL,
  `scheduled_date` DATE NOT NULL,
  `started_at` DATETIME DEFAULT NULL,
  `completed_at` DATETIME DEFAULT NULL,
  `notes` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `created_by` INT DEFAULT NULL,
  KEY `idx_hk_room` (`room_id`),
  KEY `idx_hk_assigned` (`assigned_to`),
  KEY `idx_hk_date_status` (`scheduled_date`,`status`),
  FOREIGN KEY (`room_id`) REFERENCES `rooms`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`assigned_to`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- FOLIO CHARGES (in-stay charges) ----------
CREATE TABLE IF NOT EXISTS `folio_charges` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `booking_id` INT NOT NULL,
  `charge_type` ENUM('room','food','beverage','minibar','laundry','phone','spa','transport','other') DEFAULT 'other',
  `description` VARCHAR(255) NOT NULL,
  `quantity` DECIMAL(8,2) DEFAULT 1,
  `unit_price` DECIMAL(10,2) NOT NULL DEFAULT 0,
  `amount` DECIMAL(10,2) NOT NULL DEFAULT 0,
  `tax_amount` DECIMAL(10,2) DEFAULT 0,
  `posted_by` INT DEFAULT NULL,
  `posted_at` DATETIME DEFAULT NULL,
  `voided` TINYINT(1) DEFAULT 0,
  `voided_at` DATETIME DEFAULT NULL,
  `voided_by` INT DEFAULT NULL,
  `notes` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_folio_booking` (`booking_id`),
  KEY `idx_folio_voided` (`voided`),
  FOREIGN KEY (`booking_id`) REFERENCES `bookings`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`posted_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`voided_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- RATE PLANS ----------
CREATE TABLE IF NOT EXISTS `rate_plans` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `property_id` INT NOT NULL,
  `room_type_id` INT DEFAULT NULL,
  `name` VARCHAR(100) NOT NULL,
  `code` VARCHAR(20) NOT NULL,
  `meal_plan` ENUM('none','breakfast','half_board','full_board','all_inclusive') DEFAULT 'none',
  `rate_type` ENUM('fixed','percent_of_base') DEFAULT 'fixed',
  `rate_amount` DECIMAL(10,2) NOT NULL DEFAULT 0,
  `min_stay` INT DEFAULT 1,
  `max_stay` INT DEFAULT NULL,
  `valid_from` DATE DEFAULT NULL,
  `valid_to` DATE DEFAULT NULL,
  `refundable` TINYINT(1) DEFAULT 1,
  `cancellation_policy` TEXT,
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_rate_property` (`property_id`),
  KEY `idx_rate_type` (`room_type_id`),
  FOREIGN KEY (`property_id`) REFERENCES `properties`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`room_type_id`) REFERENCES `room_types`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- CORPORATE ACCOUNTS ----------
CREATE TABLE IF NOT EXISTS `corporate_accounts` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(190) NOT NULL,
  `code` VARCHAR(30) DEFAULT NULL UNIQUE,
  `contact_name` VARCHAR(150),
  `contact_email` VARCHAR(190),
  `contact_phone` VARCHAR(40),
  `address` TEXT,
  `tax_id` VARCHAR(50),
  `negotiated_rate` DECIMAL(10,2) DEFAULT 0,
  `credit_limit` DECIMAL(12,2) DEFAULT 0,
  `current_balance` DECIMAL(12,2) DEFAULT 0,
  `payment_terms_days` INT DEFAULT 30,
  `is_active` TINYINT(1) DEFAULT 1,
  `notes` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_corp_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- GROUP BOOKINGS ----------
CREATE TABLE IF NOT EXISTS `booking_groups` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `group_name` VARCHAR(190) NOT NULL,
  `reference` VARCHAR(30) NOT NULL UNIQUE,
  `property_id` INT NOT NULL,
  `corporate_id` INT DEFAULT NULL,
  `contact_name` VARCHAR(150),
  `contact_email` VARCHAR(190),
  `contact_phone` VARCHAR(40),
  `event_type` VARCHAR(100),
  `check_in` DATE NOT NULL,
  `check_out` DATE NOT NULL,
  `rooms_blocked` INT DEFAULT 0,
  `rooms_picked_up` INT DEFAULT 0,
  `status` ENUM('tentative','confirmed','cancelled') DEFAULT 'tentative',
  `cutoff_date` DATE DEFAULT NULL,
  `notes` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `created_by` INT DEFAULT NULL,
  KEY `idx_group_property` (`property_id`),
  KEY `idx_group_dates` (`check_in`,`check_out`),
  FOREIGN KEY (`property_id`) REFERENCES `properties`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`corporate_id`) REFERENCES `corporate_accounts`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- NIGHT AUDIT LOG ----------
CREATE TABLE IF NOT EXISTS `night_audits` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `property_id` INT NOT NULL,
  `business_date` DATE NOT NULL,
  `status` ENUM('pending','in_progress','completed') DEFAULT 'pending',
  `arrivals_count` INT DEFAULT 0,
  `departures_count` INT DEFAULT 0,
  `in_house_count` INT DEFAULT 0,
  `total_revenue` DECIMAL(12,2) DEFAULT 0,
  `room_revenue` DECIMAL(12,2) DEFAULT 0,
  `f_and_b_revenue` DECIMAL(12,2) DEFAULT 0,
  `other_revenue` DECIMAL(12,2) DEFAULT 0,
  `total_paid` DECIMAL(12,2) DEFAULT 0,
  `occupancy_rate` DECIMAL(5,2) DEFAULT 0,
  `adr` DECIMAL(10,2) DEFAULT 0,
  `revpar` DECIMAL(10,2) DEFAULT 0,
  `notes` TEXT,
  `started_at` DATETIME DEFAULT NULL,
  `completed_at` DATETIME DEFAULT NULL,
  `completed_by` INT DEFAULT NULL,
  UNIQUE KEY `uq_audit_date` (`property_id`,`business_date`),
  KEY `idx_audit_status` (`status`),
  FOREIGN KEY (`property_id`) REFERENCES `properties`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`completed_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- PERFORMANCE INDEXES ----------
ALTER TABLE `bookings` ADD INDEX IF NOT EXISTS `idx_book_checkin` (`check_in`);
ALTER TABLE `bookings` ADD INDEX IF NOT EXISTS `idx_book_payment` (`payment_status`);
ALTER TABLE `payments` ADD INDEX IF NOT EXISTS `idx_pay_date` (`created_at`);
ALTER TABLE `activity_log` ADD INDEX IF NOT EXISTS `idx_act_date` (`created_at`);

-- ---------- SEED: Default rate plan codes ----------
-- Users will populate per property; this comment is documentation only.
-- Common codes: BAR (Best Available Rate), CORP (Corporate), GOV (Government),
-- AAA, AARP, GROUP, ADV (Advance Purchase), NRF (Non-Refundable)

SET FOREIGN_KEY_CHECKS=1;

-- ============================================================
-- ROLLBACK (run only if you need to undo this migration)
-- ============================================================
-- DROP TABLE IF EXISTS night_audits, booking_groups, corporate_accounts,
--                       rate_plans, folio_charges, housekeeping_tasks,
--                       rooms, room_types;
-- ALTER TABLE properties DROP COLUMN property_mode, DROP COLUMN total_rooms,
--   DROP COLUMN check_in_time, DROP COLUMN check_out_time,
--   DROP COLUMN tax_rate, DROP COLUMN service_charge;
-- ALTER TABLE bookings DROP COLUMN room_id, DROP COLUMN room_type_id,
--   DROP COLUMN rate_plan_id, DROP COLUMN meal_plan, DROP COLUMN extras_total,
--   DROP COLUMN booking_type, DROP COLUMN group_id, DROP COLUMN corporate_id;
