-- ============================================================================
-- Hotel Management System - MySQL 8+ / MariaDB 10.6+ schema
-- Import: mysql -u root -p hms < database/schema.sql   (create DB first)
--
-- Conventions:
--   * InnoDB, utf8mb4 (full Unicode, emoji-safe).
--   * All money as DECIMAL(10,2). Integer minor units are used ONLY at the
--     payment-gateway boundary (pesewas/kobo) - see App\Services\PaymentService.
--   * ON DELETE choices: audit history is preserved via SET NULL where the
--     referenced row may disappear; operational rows (room_types) use RESTRICT
--     so inventory cannot be orphaned while reservations reference it.
-- ============================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------------
-- users: guests + staff. Roles: guest < receptionist < manager < admin.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
    `id`               INT UNSIGNED     NOT NULL AUTO_INCREMENT,
    `name`             VARCHAR(100)     NOT NULL,
    `email`            VARCHAR(255)     NOT NULL,
    `phone`            VARCHAR(30)      NULL,
    `country`          CHAR(2)          NULL DEFAULT 'GH',
    `password_hash`    VARCHAR(255)     NOT NULL,               -- Argon2id
    `role`             ENUM('guest','receptionist','manager','admin') NOT NULL DEFAULT 'guest',
    `status`           ENUM('active','suspended')               NOT NULL DEFAULT 'active',
    `email_verified_at` DATETIME        NULL,
    `created_at`       DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`       DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_users_email` (`email`),
    KEY `idx_users_role` (`role`),
    KEY `idx_users_country` (`country`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- password_resets: sha256(token) stored, never the token itself.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `password_resets` (
    `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `email`       VARCHAR(255) NOT NULL,
    `token_hash`  CHAR(64)     NOT NULL,
    `expires_at`  DATETIME     NOT NULL,
    `created_at`  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_pr_email` (`email`),
    KEY `idx_pr_expires` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- amenities
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `amenities` (
    `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name`       VARCHAR(60)  NOT NULL,
    `icon`       VARCHAR(40)  NULL,               -- optional icon key
    `created_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_amenities_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- room_types: Single, Double, Suite... price is per night (base).
-- photos: JSON array of upload paths relative to public/uploads/.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `room_types` (
    `id`          INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    `name`        VARCHAR(100)  NOT NULL,
    `slug`        VARCHAR(120)  NOT NULL,
    `description` TEXT          NULL,
    `base_price`  DECIMAL(10,2) NOT NULL,
    `capacity`    TINYINT UNSIGNED NOT NULL DEFAULT 2,     -- max guests (adults+children)
    `size_sqm`    SMALLINT UNSIGNED NULL,
    `photos`      JSON          NULL,
    `is_active`   TINYINT(1)    NOT NULL DEFAULT 1,
    `created_at`  DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`  DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_room_types_slug` (`slug`),
    KEY `idx_room_types_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `room_type_amenity` (
    `room_type_id` INT UNSIGNED NOT NULL,
    `amenity_id`   INT UNSIGNED NOT NULL,
    PRIMARY KEY (`room_type_id`, `amenity_id`),
    CONSTRAINT `fk_rta_type`    FOREIGN KEY (`room_type_id`) REFERENCES `room_types` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_rta_amenity` FOREIGN KEY (`amenity_id`)   REFERENCES `amenities` (`id`)   ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- rooms: physical inventory.
--   status       = physical bookability (available / maintenance / out_of_order)
--   housekeeping = cleaning state (clean / dirty / inspected)
-- Occupancy itself is derived from active reservations (no stale flag).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `rooms` (
    `id`           INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `room_type_id` INT UNSIGNED NOT NULL,
    `room_number`  VARCHAR(10)  NOT NULL,
    `floor`        TINYINT UNSIGNED NOT NULL DEFAULT 1,
    `status`       ENUM('available','maintenance','out_of_order') NOT NULL DEFAULT 'available',
    `housekeeping` ENUM('clean','dirty','inspected')              NOT NULL DEFAULT 'clean',
    `notes`        VARCHAR(255) NULL,
    `created_at`   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_rooms_number` (`room_number`),
    KEY `idx_rooms_type` (`room_type_id`),
    KEY `idx_rooms_status` (`status`),
    CONSTRAINT `fk_rooms_type` FOREIGN KEY (`room_type_id`) REFERENCES `room_types` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- seasonal_rates: absolute per-night price override for a date window.
-- Nightly price = active seasonal rate covering that date, else base_price.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `seasonal_rates` (
    `id`           INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    `room_type_id` INT UNSIGNED  NOT NULL,
    `name`         VARCHAR(100)  NOT NULL,
    `start_date`   DATE          NOT NULL,
    `end_date`     DATE          NOT NULL,
    `price`        DECIMAL(10,2) NOT NULL,
    `is_active`    TINYINT(1)    NOT NULL DEFAULT 1,
    `created_at`   DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_seasonal_type_dates` (`room_type_id`, `start_date`, `end_date`),
    CONSTRAINT `fk_seasonal_type` FOREIGN KEY (`room_type_id`) REFERENCES `room_types` (`id`) ON DELETE CASCADE,
    CONSTRAINT `chk_seasonal_dates` CHECK (`end_date` > `start_date`),
    CONSTRAINT `chk_seasonal_price` CHECK (`price` > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- promo_codes
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `promo_codes` (
    `id`             INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    `code`           VARCHAR(30)   NOT NULL,
    `discount_type`  ENUM('percent','fixed') NOT NULL,
    `discount_value` DECIMAL(10,2) NOT NULL,
    `valid_from`     DATE          NULL,
    `valid_to`       DATE          NULL,
    `max_uses`       INT UNSIGNED  NULL,
    `uses_count`     INT UNSIGNED  NOT NULL DEFAULT 0,
    `is_active`      TINYINT(1)    NOT NULL DEFAULT 1,
    `created_at`     DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_promo_code` (`code`),
    KEY `idx_promo_active` (`is_active`),
    CONSTRAINT `chk_promo_value` CHECK (`discount_value` > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- reservations: the heart of the system.
--   Guest details are snapshotted (guest_name/email/phone) so history survives
--   account changes. user_id links to the online account when one exists.
--   status lifecycle:
--     pending_hold -> pending_payment -> confirmed -> checked_in -> checked_out
--        |                |                 |
--        +-> expired      +-> expired       +-> cancelled / no_show
--   hold_expires_at governs pending_hold AND pending_payment.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `reservations` (
    `id`                 INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    `reference`          VARCHAR(20)   NOT NULL,
    `user_id`            INT UNSIGNED  NULL,
    `room_type_id`       INT UNSIGNED  NOT NULL,
    `room_id`            INT UNSIGNED  NULL,                  -- assigned at check-in
    `check_in`           DATE          NOT NULL,
    `check_out`          DATE          NOT NULL,
    `adults`             TINYINT UNSIGNED NOT NULL DEFAULT 1,
    `children`           TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `guest_name`         VARCHAR(120)  NOT NULL,
    `guest_email`        VARCHAR(255)  NOT NULL,
    `guest_phone`        VARCHAR(30)   NULL,
    `status`             ENUM('pending_hold','pending_payment','confirmed','checked_in',
                              'checked_out','cancelled','expired','no_show') NOT NULL DEFAULT 'pending_hold',
    `hold_expires_at`    DATETIME      NULL,
    `subtotal`           DECIMAL(10,2) NOT NULL DEFAULT 0,    -- room revenue, pre-discount
    `discount_amount`    DECIMAL(10,2) NOT NULL DEFAULT 0,
    `tax_amount`         DECIMAL(10,2) NOT NULL DEFAULT 0,
    `total_amount`       DECIMAL(10,2) NOT NULL DEFAULT 0,
    `promo_code`         VARCHAR(30)   NULL,
    `special_requests`   TEXT          NULL,
    `source`             ENUM('online','walk_in','phone') NOT NULL DEFAULT 'online',
    `id_number`          VARCHAR(60)   NULL,                  -- ID verified at front desk
    `key_card_code`      VARCHAR(20)   NULL,                  -- simulated key card
    `checked_in_at`      DATETIME      NULL,
    `checked_out_at`     DATETIME      NULL,
    `cancelled_at`       DATETIME      NULL,
    `cancellation_reason` VARCHAR(255) NULL,
    `created_by`         INT UNSIGNED  NULL,                  -- staff user id for walk-ins
    `created_at`         DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`         DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_reservations_reference` (`reference`),
    KEY `idx_res_type_dates` (`room_type_id`, `check_in`, `check_out`),
    KEY `idx_res_status` (`status`),
    KEY `idx_res_user` (`user_id`),
    KEY `idx_res_hold` (`hold_expires_at`),
    KEY `idx_res_checkin` (`check_in`),
    KEY `idx_res_checkout` (`check_out`),
    CONSTRAINT `fk_res_user`      FOREIGN KEY (`user_id`)      REFERENCES `users` (`id`)      ON DELETE SET NULL,
    CONSTRAINT `fk_res_room_type` FOREIGN KEY (`room_type_id`) REFERENCES `room_types` (`id`) ON DELETE RESTRICT,
    CONSTRAINT `fk_res_room`      FOREIGN KEY (`room_id`)      REFERENCES `rooms` (`id`)      ON DELETE SET NULL,
    CONSTRAINT `fk_res_creator`   FOREIGN KEY (`created_by`)   REFERENCES `users` (`id`)      ON DELETE SET NULL,
    CONSTRAINT `chk_res_dates`    CHECK (`check_out` > `check_in`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Per-night price breakdown (audit-friendly pricing snapshot).
CREATE TABLE IF NOT EXISTS `reservation_nightly_rates` (
    `id`             INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    `reservation_id` INT UNSIGNED  NOT NULL,
    `night_date`     DATE          NOT NULL,
    `price`          DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_rnr_reservation_night` (`reservation_id`, `night_date`),
    CONSTRAINT `fk_rnr_reservation` FOREIGN KEY (`reservation_id`) REFERENCES `reservations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- payments
--   `reference`          = OUR unique reference (sent to the gateway).
--   `provider_reference` = gateway's own id (authorization code / payment id).
--   UNIQUE (provider, provider_reference) makes webhook settlement idempotent:
--   a replayed webhook can never double-credit a reservation.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `payments` (
    `id`                 INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    `reservation_id`     INT UNSIGNED  NOT NULL,
    `provider`           ENUM('paystack','hubtel','cash','card','bank_transfer') NOT NULL,
    `reference`          VARCHAR(40)   NOT NULL,
    `provider_reference` VARCHAR(100)  NULL,
    `amount`             DECIMAL(10,2) NOT NULL,                -- signed; refunds store negative
    `currency`           CHAR(3)       NOT NULL DEFAULT 'GHS',
    `status`             ENUM('initiated','pending','succeeded','failed','refunded') NOT NULL DEFAULT 'initiated',
    `amount_settled`     DECIMAL(10,2) NULL,
    `gateway_response`   JSON          NULL,                   -- raw verify/webhook payload
    `recorded_by`        INT UNSIGNED  NULL,
    `note`               VARCHAR(255)  NULL,
    `created_at`         DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`         DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_payments_reference` (`reference`),
    UNIQUE KEY `uq_payments_provider_ref` (`provider`, `provider_reference`),
    KEY `idx_payments_reservation` (`reservation_id`),
    KEY `idx_payments_status` (`status`),
    CONSTRAINT `fk_payments_reservation` FOREIGN KEY (`reservation_id`) REFERENCES `reservations` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_payments_recorded_by` FOREIGN KEY (`recorded_by`)    REFERENCES `users` (`id`)      ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- folios & charges: room account per reservation (minibar, laundry, ...).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `folios` (
    `id`             INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `reservation_id` INT UNSIGNED NOT NULL,
    `status`         ENUM('open','settled','void') NOT NULL DEFAULT 'open',
    `created_at`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_folios_reservation` (`reservation_id`),
    CONSTRAINT `fk_folios_reservation` FOREIGN KEY (`reservation_id`) REFERENCES `reservations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `folio_charges` (
    `id`          INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    `folio_id`    INT UNSIGNED  NOT NULL,
    `category`    ENUM('room','minibar','laundry','room_service','restaurant','telephone','damage','other') NOT NULL DEFAULT 'other',
    `description` VARCHAR(255)  NOT NULL,
    `amount`      DECIMAL(10,2) NOT NULL,                      -- pre-tax
    `tax_amount`  DECIMAL(10,2) NOT NULL DEFAULT 0,
    `total`       DECIMAL(10,2) NOT NULL,                      -- amount + tax_amount (generated column kept materialized for SUM speed)
    `created_by`  INT UNSIGNED  NULL,
    `created_at`  DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_charges_folio` (`folio_id`),
    CONSTRAINT `fk_charges_folio`    FOREIGN KEY (`folio_id`)   REFERENCES `folios` (`id`)   ON DELETE CASCADE,
    CONSTRAINT `fk_charges_creator`  FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)    ON DELETE SET NULL,
    CONSTRAINT `chk_charge_amount`   CHECK (`amount` >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- housekeeping_tasks & maintenance_requests
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `housekeeping_tasks` (
    `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `room_id`     INT UNSIGNED NOT NULL,
    `task`        ENUM('cleaning','inspection','deep_clean','linen_change') NOT NULL DEFAULT 'cleaning',
    `assigned_to` INT UNSIGNED NULL,
    `status`      ENUM('pending','in_progress','completed') NOT NULL DEFAULT 'pending',
    `notes`       VARCHAR(255) NULL,
    `created_at`  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `completed_at` DATETIME    NULL,
    PRIMARY KEY (`id`),
    KEY `idx_hk_room` (`room_id`),
    KEY `idx_hk_status` (`status`),
    CONSTRAINT `fk_hk_room`     FOREIGN KEY (`room_id`)     REFERENCES `rooms` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_hk_assigned` FOREIGN KEY (`assigned_to`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `maintenance_requests` (
    `id`               INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `room_id`          INT UNSIGNED NULL,
    `reported_by`      INT UNSIGNED NULL,
    `title`            VARCHAR(150) NOT NULL,
    `description`      TEXT         NULL,
    `priority`         ENUM('low','medium','high','urgent') NOT NULL DEFAULT 'medium',
    `status`           ENUM('open','in_progress','resolved','closed') NOT NULL DEFAULT 'open',
    `resolution_notes` TEXT         NULL,
    `resolved_at`      DATETIME     NULL,
    `created_at`       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_maint_room` (`room_id`),
    KEY `idx_maint_status` (`status`),
    CONSTRAINT `fk_maint_room`    FOREIGN KEY (`room_id`)     REFERENCES `rooms` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_maint_reporter` FOREIGN KEY (`reported_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- audit_logs: who did what, when, from where.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `audit_logs` (
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id`     INT UNSIGNED    NULL,
    `action`      VARCHAR(60)     NOT NULL,
    `entity`      VARCHAR(40)     NULL,
    `entity_id`   INT UNSIGNED    NULL,
    `description` VARCHAR(255)    NULL,
    `ip`          VARCHAR(45)     NULL,
    `user_agent`  VARCHAR(255)    NULL,
    `meta`        JSON            NULL,
    `created_at`  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_audit_user` (`user_id`),
    KEY `idx_audit_action` (`action`),
    KEY `idx_audit_created` (`created_at`),
    CONSTRAINT `fk_audit_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- settings: runtime-editable configuration (overrides .env defaults).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `settings` (
    `setting_key`   VARCHAR(60) NOT NULL,
    `setting_value` TEXT        NULL,
    `updated_at`    DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;