SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS housekeeping_tasks (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  room_id BIGINT UNSIGNED NOT NULL,
  task_type ENUM('checkout_clean','stayover_clean','deep_clean','inspection','turndown','linen_change') NOT NULL,
  priority ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
  status ENUM('pending','assigned','in_progress','completed','inspected','rejected') NOT NULL DEFAULT 'pending',
  assigned_to BIGINT UNSIGNED NULL,
  assigned_by BIGINT UNSIGNED NULL,
  assigned_at DATETIME NULL,
  started_at DATETIME NULL,
  completed_at DATETIME NULL,
  inspected_at DATETIME NULL,
  inspected_by BIGINT UNSIGNED NULL,
  notes TEXT NULL,
  reservation_id BIGINT UNSIGNED NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  INDEX idx_status (status),
  INDEX idx_room_status (room_id, status),
  INDEX idx_assigned (assigned_to, 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,
  CONSTRAINT fk_hk_res FOREIGN KEY (reservation_id) REFERENCES reservations(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS housekeeping_inspections (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  task_id BIGINT UNSIGNED NOT NULL,
  room_id BIGINT UNSIGNED NOT NULL,
  inspected_by BIGINT UNSIGNED NOT NULL,
  passed TINYINT(1) NOT NULL DEFAULT 1,
  score TINYINT UNSIGNED NULL,
  notes TEXT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_task (task_id),
  CONSTRAINT fk_hki_task FOREIGN KEY (task_id) REFERENCES housekeeping_tasks(id) ON DELETE CASCADE,
  CONSTRAINT fk_hki_room FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS linen_transactions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  room_id BIGINT UNSIGNED NULL,
  item_type ENUM('bedsheet','pillowcase','towel_bath','towel_hand','duvet','blanket','other') NOT NULL,
  action ENUM('issued','returned','damaged','lost','laundered') NOT NULL,
  quantity INT NOT NULL,
  notes VARCHAR(255) NULL,
  performed_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_room (room_id),
  CONSTRAINT fk_lt_room FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS lost_and_found (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  room_id BIGINT UNSIGNED NULL,
  reservation_id BIGINT UNSIGNED NULL,
  description TEXT NOT NULL,
  found_by BIGINT UNSIGNED NULL,
  found_at DATETIME NOT NULL,
  storage_location VARCHAR(190) NULL,
  status ENUM('in_storage','returned','disposed') NOT NULL DEFAULT 'in_storage',
  returned_to VARCHAR(190) NULL,
  returned_at DATETIME NULL,
  notes TEXT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  CONSTRAINT fk_lf_room FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;