-- ============================================================================
-- The Hotel Operations Data Model, reference schema v0.1 (PostgreSQL)
-- https://rapideyeinspections.com/blog/hotel-operations-data-model/
--
-- A reference data model for building internal hotel operations tools:
-- rooms and room status, the housekeeping board and its credit-based
-- workload, supervisor inspections, discrepancy reports, work orders and
-- preventive maintenance, incidents, lost and found, and linen par levels.
-- 23 tables in 6 layers.
--
-- Companion to the STR version:
-- https://rapideyeinspections.com/blog/str-operations-data-model/
--
-- Design principles (explained in full on the page above):
--   1. Occupancy and cleanliness are two separate axes owned by two
--      separate departments. Front office owns occupied/vacant;
--      housekeeping owns clean/dirty/inspected. The familiar composite
--      codes (VC, VD, VI, OC, OD) are derived, never typed by hand.
--      Discrepancy reports exist precisely because the two axes can
--      disagree.
--   2. Out of Order is not Out of Service. Per Oracle OPERA's documented
--      model, an OOO room is removed from inventory availability; an OOS
--      room is blocked but remains sellable. The schema encodes them as
--      distinct availability states, never as a shared "broken" flag.
--   3. Room status is an event log, not a column. The room row carries
--      the current state for fast board queries; room_status_log is the
--      append-only history that makes "when did this room actually become
--      guest-ready" answerable.
--   4. Credits are the workload currency. Housekeeping boards balance
--      credits, not door counts; a suite is not a standard king. Credit
--      values live on room_type and are copied onto each task at creation
--      so history survives later re-weighting.
--   5. The inspection is a first-class entity, separate from the clean.
--      A clean makes a room Clean; only a supervisor inspection makes it
--      Inspected. What inspections discover becomes findings, and findings
--      escalate to work orders.
--   6. Your PMS stays the system of record for reservations. The guest
--      and reservation tables are a one-way synced mirror keyed by
--      (source, external_id) so ingestion is idempotent.
--
-- License: free to use, adapt, and build on, with or without attribution.
-- ============================================================================

-- ---------------------------------------------------------------------------
-- 1. PROPERTY LAYER: what you operate
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS property (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name          text NOT NULL,
  brand         text,                    -- flag / brand, if any
  address_line1 text,
  address_line2 text,
  city          text,
  region        text,                    -- state / province
  postal_code   text,
  country_code  text,                    -- ISO 3166-1 alpha-2
  timezone      text NOT NULL,           -- IANA, e.g. 'America/Denver'
  source        text,                    -- PMS this property syncs from, e.g. 'opera'
  external_id   text,                    -- id in that PMS
  status        text NOT NULL DEFAULT 'active'
                CHECK (status IN ('active','pre_opening','closed')),
  created_at    timestamptz NOT NULL DEFAULT now(),
  updated_at    timestamptz NOT NULL DEFAULT now(),
  UNIQUE (source, external_id)
);

-- Floors, wings, towers, and housekeeping zones. Boards are built per zone,
-- attendants are assigned per zone, and assets (elevators, ice machines)
-- can live in a zone without living in a room.
CREATE TABLE IF NOT EXISTS floor_zone (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  property_id uuid NOT NULL REFERENCES property(id),
  name        text NOT NULL,             -- 'Floor 4', 'North Wing', 'Tower B 12-18'
  kind        text NOT NULL DEFAULT 'floor'
              CHECK (kind IN ('floor','wing','tower','building','zone')),
  sort_order  int,
  created_at  timestamptz NOT NULL DEFAULT now()
);

-- Room types carry the housekeeping credit baseline, because workload is
-- a property of the room type (a suite takes longer than a standard king),
-- and the sell/occupancy attributes the front office cares about.
CREATE TABLE IF NOT EXISTS room_type (
  id                    uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  property_id           uuid NOT NULL REFERENCES property(id),
  code                  text NOT NULL,   -- 'KNG', 'DBL', 'STE'
  name                  text NOT NULL,
  description           text,
  max_occupancy         int,
  bed_config            text,            -- '1 King', '2 Queens'
  departure_credits     numeric(4,1) NOT NULL DEFAULT 1.0,  -- credit value of a checkout clean
  stayover_credits      numeric(4,1) NOT NULL DEFAULT 0.5,  -- credit value of a stayover service
  created_at            timestamptz NOT NULL DEFAULT now(),
  updated_at            timestamptz NOT NULL DEFAULT now(),
  UNIQUE (property_id, code)
);

-- The room. Current state is denormalized here for fast board queries;
-- the authoritative history lives in room_status_log.
--
-- status_code is GENERATED, never written by hand: the seven-code shorthand
-- (VC, VD, VI, OC, OD, OOO, OOS) is a projection of three underlying facts
-- that different departments own. Note the deliberate asymmetry: OOO and OOS
-- mask the occupancy/cleanliness axes because availability outranks both.
CREATE TABLE IF NOT EXISTS room (
  id                  uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  property_id         uuid NOT NULL REFERENCES property(id),
  floor_zone_id       uuid REFERENCES floor_zone(id),
  room_type_id        uuid NOT NULL REFERENCES room_type(id),
  room_number         text NOT NULL,     -- text, not int: '204A', 'PH1'
  connects_to         text,              -- connecting room number, if any
  occupancy_status    text NOT NULL DEFAULT 'vacant'
                      CHECK (occupancy_status IN ('vacant','occupied')),
  cleanliness_status  text NOT NULL DEFAULT 'dirty'
                      CHECK (cleanliness_status IN ('clean','dirty','inspected')),
  availability_status text NOT NULL DEFAULT 'in_service'
                      CHECK (availability_status IN ('in_service','out_of_service','out_of_order')),
  status_code         text GENERATED ALWAYS AS (
                        CASE
                          WHEN availability_status = 'out_of_order'  THEN 'OOO'
                          WHEN availability_status = 'out_of_service' THEN 'OOS'
                          WHEN occupancy_status = 'occupied' AND cleanliness_status = 'clean' THEN 'OC'
                          WHEN occupancy_status = 'occupied' THEN 'OD'
                          WHEN cleanliness_status = 'inspected' THEN 'VI'
                          WHEN cleanliness_status = 'clean' THEN 'VC'
                          ELSE 'VD'
                        END
                      ) STORED,
  status_changed_at   timestamptz NOT NULL DEFAULT now(),
  created_at          timestamptz NOT NULL DEFAULT now(),
  updated_at          timestamptz NOT NULL DEFAULT now(),
  UNIQUE (property_id, room_number)
);

-- Deliberately light: operations is this model's focus, revenue is not.
-- The rate plan exists so a reservation row can say what it was sold on,
-- and nothing more. Pricing, restrictions, and yielding stay in the PMS/RMS.
CREATE TABLE IF NOT EXISTS rate_plan (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  property_id uuid NOT NULL REFERENCES property(id),
  code        text NOT NULL,             -- 'BAR', 'CORP', 'AAA'
  name        text NOT NULL,
  description text,
  created_at  timestamptz NOT NULL DEFAULT now(),
  UNIQUE (property_id, code)
);

-- ---------------------------------------------------------------------------
-- 2. GUESTS AND STAYS: synced mirror, the PMS owns it
-- ---------------------------------------------------------------------------

-- Minimal PII on purpose: an operations tool needs to attribute a stay,
-- not run a CRM.
CREATE TABLE IF NOT EXISTS guest (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  full_name   text,
  email       text,
  phone       text,
  source      text,
  external_id text,
  created_at  timestamptz NOT NULL DEFAULT now(),
  UNIQUE (source, external_id)
);

-- One-way mirror of the PMS reservation. Keyed by (source, external_id)
-- so re-syncing is idempotent. Never write bookings here first.
CREATE TABLE IF NOT EXISTS reservation (
  id                  uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  property_id         uuid NOT NULL REFERENCES property(id),
  guest_id            uuid REFERENCES guest(id),
  room_type_id        uuid REFERENCES room_type(id),
  rate_plan_id        uuid REFERENCES rate_plan(id),
  confirmation_number text,
  source              text NOT NULL,     -- 'opera', 'mews', 'cloudbeds', 'ota'
  external_id         text NOT NULL,
  arrival_date        date NOT NULL,
  departure_date      date NOT NULL,
  adults              int,
  children            int,
  status              text NOT NULL DEFAULT 'booked'
                      CHECK (status IN ('booked','in_house','checked_out','cancelled','no_show')),
  synced_at           timestamptz NOT NULL DEFAULT now(),
  created_at          timestamptz NOT NULL DEFAULT now(),
  UNIQUE (source, external_id)
);

-- The physical occupancy of one room. A reservation with a room move has
-- two stay rows; occupancy questions ("who was in 412 on the 9th") join
-- through stay, never through reservation, which only knows the room type.
CREATE TABLE IF NOT EXISTS stay (
  id             uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  reservation_id uuid NOT NULL REFERENCES reservation(id),
  room_id        uuid NOT NULL REFERENCES room(id),
  checked_in_at  timestamptz NOT NULL,
  checked_out_at timestamptz,            -- null while in-house
  move_reason    text,                   -- set on the new row after a room move
  created_at     timestamptz NOT NULL DEFAULT now()
);

-- ---------------------------------------------------------------------------
-- 3. ROOM STATUS: the state machine
-- ---------------------------------------------------------------------------

-- Append-only history of every status change. The room row answers
-- "what is 412 now"; this table answers "when did 412 become guest-ready,
-- who made it so, and through which states did it pass".
CREATE TABLE IF NOT EXISTS room_status_log (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  room_id     uuid NOT NULL REFERENCES room(id),
  from_code   text CHECK (from_code IN ('VC','VD','VI','OC','OD','OOO','OOS')),
  to_code     text NOT NULL CHECK (to_code IN ('VC','VD','VI','OC','OD','OOO','OOS')),
  changed_at  timestamptz NOT NULL DEFAULT now(),
  changed_by  uuid,                      -- REFERENCES staff(id); FK added below
  source      text NOT NULL DEFAULT 'app'
              CHECK (source IN ('app','pms_sync','night_audit','housekeeping','front_office','maintenance')),
  note        text
);

-- The legal transitions of the room-status state machine, as data, so an
-- application (or an AI agent) can validate a proposed change with one
-- lookup instead of hard-coding the rules. Seeded below; add rows to
-- loosen the machine, delete rows to tighten it.
CREATE TABLE IF NOT EXISTS room_status_transition (
  from_code     text NOT NULL CHECK (from_code IN ('VC','VD','VI','OC','OD','OOO','OOS')),
  to_code       text NOT NULL CHECK (to_code IN ('VC','VD','VI','OC','OD','OOO','OOS')),
  trigger_event text NOT NULL,           -- what legitimately causes this edge
  PRIMARY KEY (from_code, to_code)
);

INSERT INTO room_status_transition (from_code, to_code, trigger_event) VALUES
  ('OC',  'OD',  'night audit rollover: an occupied room owes service each day'),
  ('OD',  'OC',  'stayover service completed'),
  ('OC',  'VD',  'guest checkout'),
  ('OD',  'VD',  'guest checkout'),
  ('VD',  'VC',  'departure clean completed by room attendant'),
  ('VC',  'VI',  'supervisor inspection passed'),
  ('VC',  'VD',  'supervisor inspection failed, room returned to the board'),
  ('VI',  'OC',  'guest check-in'),
  ('VC',  'OC',  'guest check-in at properties not running the inspection step'),
  ('VI',  'VD',  'inspected room sat vacant too long and reverts to dirty'),
  ('VD',  'OOO', 'room removed from inventory (maintenance, renovation)'),
  ('VC',  'OOO', 'room removed from inventory (maintenance, renovation)'),
  ('VI',  'OOO', 'room removed from inventory (maintenance, renovation)'),
  ('OOO', 'VD',  'room returned to inventory; it always re-enters dirty'),
  ('VD',  'OOS', 'room flagged out of service but left sellable'),
  ('VC',  'OOS', 'room flagged out of service but left sellable'),
  ('VI',  'OOS', 'room flagged out of service but left sellable'),
  ('OOS', 'VD',  'out-of-service flag cleared; room re-enters dirty'),
  ('OOS', 'OC',  'room sold while out of service: OOS rooms remain sellable')
ON CONFLICT (from_code, to_code) DO NOTHING;

-- One row per OOO or OOS episode. The kind column encodes the distinction
-- that matters to revenue: out_of_order rooms are deducted from inventory
-- availability; out_of_service rooms remain sellable while flagged.
CREATE TABLE IF NOT EXISTS ooo_oos_event (
  id                 uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  room_id            uuid NOT NULL REFERENCES room(id),
  kind               text NOT NULL CHECK (kind IN ('out_of_order','out_of_service')),
  reason_code        text NOT NULL DEFAULT 'maintenance'
                     CHECK (reason_code IN ('maintenance','renovation','deep_clean','damage','pest_control','other')),
  description        text,
  starts_at          timestamptz NOT NULL DEFAULT now(),
  expected_return_at timestamptz,        -- what revenue management plans around
  ended_at           timestamptz,        -- null while the episode is open
  work_order_id      uuid,               -- REFERENCES work_order(id); FK added below
  created_by         uuid,               -- REFERENCES staff(id); FK added below
  created_at         timestamptz NOT NULL DEFAULT now()
);

-- Front office and housekeeping each own one axis of room status, so the
-- two can disagree. A skip: housekeeping finds the room vacant while the
-- front office still shows it occupied. A sleep: housekeeping finds it
-- occupied while the front office shows it vacant. Every discrepancy the
-- morning report surfaces gets a row here until someone resolves it.
CREATE TABLE IF NOT EXISTS discrepancy_report (
  id               uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  room_id          uuid NOT NULL REFERENCES room(id),
  business_date    date NOT NULL,
  fo_status        text NOT NULL CHECK (fo_status IN ('vacant','occupied')),
  hk_status        text NOT NULL CHECK (hk_status IN ('vacant','occupied')),
  discrepancy_type text GENERATED ALWAYS AS (
                     CASE
                       WHEN fo_status = 'occupied' AND hk_status = 'vacant'   THEN 'skip'
                       WHEN fo_status = 'vacant'   AND hk_status = 'occupied' THEN 'sleep'
                       ELSE 'none'
                     END
                   ) STORED,
  detected_by      uuid,                 -- REFERENCES staff(id); FK added below
  detected_at      timestamptz NOT NULL DEFAULT now(),
  resolved_at      timestamptz,
  resolution_note  text,
  UNIQUE (room_id, business_date, detected_at)
);

-- ---------------------------------------------------------------------------
-- 4. HOUSEKEEPING: the board, the credits, the inspection
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS staff (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  property_id uuid NOT NULL REFERENCES property(id),
  full_name   text NOT NULL,
  role        text NOT NULL
              CHECK (role IN ('room_attendant','hk_supervisor','exec_housekeeper','front_desk','maintenance','engineer','manager','other')),
  active      boolean NOT NULL DEFAULT true,
  created_at  timestamptz NOT NULL DEFAULT now()
);

-- Late FKs for columns declared before staff / work_order existed.
-- Wrapped for idempotency: ADD CONSTRAINT has no IF NOT EXISTS.
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'room_status_log_changed_by_fkey') THEN
    ALTER TABLE room_status_log ADD CONSTRAINT room_status_log_changed_by_fkey
      FOREIGN KEY (changed_by) REFERENCES staff(id);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'ooo_oos_event_created_by_fkey') THEN
    ALTER TABLE ooo_oos_event ADD CONSTRAINT ooo_oos_event_created_by_fkey
      FOREIGN KEY (created_by) REFERENCES staff(id);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'discrepancy_report_detected_by_fkey') THEN
    ALTER TABLE discrepancy_report ADD CONSTRAINT discrepancy_report_detected_by_fkey
      FOREIGN KEY (detected_by) REFERENCES staff(id);
  END IF;
END $$;

-- One attendant's section for one business date: the row a printed board
-- line becomes. Total credits are a sum over the tasks that point here;
-- keeping the target on the row makes over-assignment visible.
CREATE TABLE IF NOT EXISTS hk_assignment (
  id             uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  property_id    uuid NOT NULL REFERENCES property(id),
  business_date  date NOT NULL,
  staff_id       uuid NOT NULL REFERENCES staff(id),
  floor_zone_id  uuid REFERENCES floor_zone(id),
  shift          text NOT NULL DEFAULT 'am' CHECK (shift IN ('am','pm','turndown','overnight')),
  credit_target  numeric(5,1),           -- what a full shift is at this property
  created_at     timestamptz NOT NULL DEFAULT now(),
  UNIQUE (business_date, staff_id, shift)
);

-- One unit of housekeeping work against one room. credit_value is COPIED
-- from room_type at creation, not joined at read time, so re-weighting
-- credits next season does not rewrite the history of work already done.
CREATE TABLE IF NOT EXISTS hk_task (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  room_id       uuid NOT NULL REFERENCES room(id),
  assignment_id uuid REFERENCES hk_assignment(id),
  stay_id       uuid REFERENCES stay(id),   -- the stay being serviced, if any
  business_date date NOT NULL,
  task_type     text NOT NULL
                CHECK (task_type IN ('departure_clean','stayover_service','turndown','deep_clean','touch_up','correction')),
  credit_value  numeric(4,1) NOT NULL DEFAULT 1.0,
  status        text NOT NULL DEFAULT 'pending'
                CHECK (status IN ('pending','in_progress','done','verified','dnd','refused','skipped')),
  started_at    timestamptz,
  completed_at  timestamptz,
  note          text,
  created_at    timestamptz NOT NULL DEFAULT now()
);

-- The supervisor pass. A clean makes a room Clean; only this makes it
-- Inspected. result drives the state machine: pass is the VC to VI edge,
-- fail is VC back to VD with the task reopened as a correction.
CREATE TABLE IF NOT EXISTS inspection (
  id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  room_id      uuid NOT NULL REFERENCES room(id),
  hk_task_id   uuid REFERENCES hk_task(id),   -- the clean being verified
  inspector_id uuid NOT NULL REFERENCES staff(id),
  inspected_at timestamptz NOT NULL DEFAULT now(),
  result       text NOT NULL CHECK (result IN ('pass','fail')),
  score        numeric(5,2),            -- optional 0-100 against the property standard
  note         text
);

-- What the inspection found. Findings are item-level and survive the
-- inspection that raised them: a chipped headboard found today is the
-- work order verified next week.
CREATE TABLE IF NOT EXISTS inspection_finding (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  inspection_id uuid NOT NULL REFERENCES inspection(id),
  category      text NOT NULL
                CHECK (category IN ('cleanliness','damage','maintenance','missing_item','safety','other')),
  severity      text NOT NULL DEFAULT 'minor'
                CHECK (severity IN ('minor','major','critical')),
  location      text,                    -- 'bathroom', 'entry', 'balcony'
  description   text NOT NULL,
  photo_uri     text,                    -- evidence beats adjectives
  work_order_id uuid,                    -- REFERENCES work_order(id); FK added below
  resolved_at   timestamptz,
  created_at    timestamptz NOT NULL DEFAULT now()
);

-- Par levels per room type: how many of each linen item a room carries,
-- times the number of full sets the property circulates (in the room, in
-- the laundry, on the shelf). Par math is what keeps a full house from
-- becoming a towel shortage on day three.
CREATE TABLE IF NOT EXISTS linen_par_level (
  id             uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  property_id    uuid NOT NULL REFERENCES property(id),
  room_type_id   uuid NOT NULL REFERENCES room_type(id),
  item           text NOT NULL,          -- 'bath_towel', 'king_sheet_set', 'bath_mat'
  per_room_qty   int NOT NULL,           -- how many one made-up room carries
  par_sets       numeric(3,1) NOT NULL DEFAULT 3.0,  -- full sets in circulation
  note           text,
  UNIQUE (room_type_id, item)
);

-- ---------------------------------------------------------------------------
-- 5. MAINTENANCE: assets, work orders, preventive cycles
-- ---------------------------------------------------------------------------

-- Things that break. An asset can live in a room (PTAC unit 412), in a
-- zone (Floor 4 ice machine), or in neither (the boiler).
CREATE TABLE IF NOT EXISTS asset (
  id               uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  property_id      uuid NOT NULL REFERENCES property(id),
  room_id          uuid REFERENCES room(id),
  floor_zone_id    uuid REFERENCES floor_zone(id),
  name             text NOT NULL,
  category         text NOT NULL DEFAULT 'other'
                   CHECK (category IN ('hvac','plumbing','electrical','appliance','furniture','fixture','elevator','life_safety','laundry','kitchen','other')),
  manufacturer     text,
  model            text,
  serial_number    text,
  installed_on     date,
  warranty_expires_on date,
  status           text NOT NULL DEFAULT 'in_service'
                   CHECK (status IN ('in_service','out_of_service','retired')),
  created_at       timestamptz NOT NULL DEFAULT now()
);

-- The maintenance unit of work. source records where it came from, because
-- "how much of engineering's day is reactive vs preventive" is a question
-- worth answering. blocks_room links the work order to the OOO/OOS episode
-- it caused, if it caused one.
CREATE TABLE IF NOT EXISTS work_order (
  id             uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  property_id    uuid NOT NULL REFERENCES property(id),
  room_id        uuid REFERENCES room(id),
  asset_id       uuid REFERENCES asset(id),
  reported_by    uuid REFERENCES staff(id),
  assigned_to    uuid REFERENCES staff(id),
  source         text NOT NULL DEFAULT 'ad_hoc'
                 CHECK (source IN ('inspection_finding','hk_task','guest_report','pm_schedule','discrepancy','ad_hoc')),
  priority       text NOT NULL DEFAULT 'routine'
                 CHECK (priority IN ('urgent','high','routine','low')),
  status         text NOT NULL DEFAULT 'open'
                 CHECK (status IN ('open','assigned','in_progress','on_hold','completed','verified','cancelled')),
  title          text NOT NULL,
  description    text,
  due_at         timestamptz,
  completed_at   timestamptz,
  created_at     timestamptz NOT NULL DEFAULT now(),
  updated_at     timestamptz NOT NULL DEFAULT now()
);

-- Late FKs now that work_order exists.
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'ooo_oos_event_work_order_id_fkey') THEN
    ALTER TABLE ooo_oos_event ADD CONSTRAINT ooo_oos_event_work_order_id_fkey
      FOREIGN KEY (work_order_id) REFERENCES work_order(id);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'inspection_finding_work_order_id_fkey') THEN
    ALTER TABLE inspection_finding ADD CONSTRAINT inspection_finding_work_order_id_fkey
      FOREIGN KEY (work_order_id) REFERENCES work_order(id);
  END IF;
END $$;

-- Preventive maintenance cycles: quarterly PTAC filters, annual deep
-- cleans, monthly life-safety checks. A scheduler walks this table and
-- creates work_order rows with source = 'pm_schedule'; last/next dates
-- advance on completion. Rooms cycle through PM too, which is the most
-- common planned reason a room goes OOO.
CREATE TABLE IF NOT EXISTS pm_schedule (
  id                uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  property_id       uuid NOT NULL REFERENCES property(id),
  asset_id          uuid REFERENCES asset(id),
  room_type_id      uuid REFERENCES room_type(id),  -- for per-room cycles, e.g. quarterly room PM
  name              text NOT NULL,       -- 'Quarterly PTAC filter change'
  description       text,
  frequency_days    int NOT NULL,        -- 90 for quarterly, 365 for annual
  takes_room_ooo    boolean NOT NULL DEFAULT false,
  last_completed_on date,
  next_due_on       date,
  active            boolean NOT NULL DEFAULT true,
  created_at        timestamptz NOT NULL DEFAULT now()
);

-- ---------------------------------------------------------------------------
-- 6. INCIDENTS AND RECOVERY: what happens around the rooms
-- ---------------------------------------------------------------------------

-- Anything that needs a record: guest injury, damage discovered mid-stay,
-- security, smoking, biohazard. Linking to stay (not just room) is what
-- lets a damage incident carry the "who was in the room" answer with it.
CREATE TABLE IF NOT EXISTS incident (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  property_id     uuid NOT NULL REFERENCES property(id),
  room_id         uuid REFERENCES room(id),
  stay_id         uuid REFERENCES stay(id),
  occurred_at     timestamptz NOT NULL,
  category        text NOT NULL
                  CHECK (category IN ('guest_injury','staff_injury','property_damage','security','theft','smoking','biohazard','noise','other')),
  severity        text NOT NULL DEFAULT 'minor'
                  CHECK (severity IN ('minor','major','critical')),
  description     text NOT NULL,
  reported_by     uuid REFERENCES staff(id),
  status          text NOT NULL DEFAULT 'open'
                  CHECK (status IN ('open','under_review','resolved','closed')),
  resolution_note text,
  created_at      timestamptz NOT NULL DEFAULT now()
);

-- Chain of custody for guest property. Status is a lifecycle, not a flag,
-- because "we mailed it back on the 14th" is the sentence that ends the
-- one-star review.
CREATE TABLE IF NOT EXISTS lost_found_item (
  id               uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  property_id      uuid NOT NULL REFERENCES property(id),
  room_id          uuid REFERENCES room(id),
  stay_id          uuid REFERENCES stay(id),   -- whose stay it likely belongs to
  found_at         timestamptz NOT NULL DEFAULT now(),
  found_by         uuid REFERENCES staff(id),
  description      text NOT NULL,
  storage_location text,                 -- 'HK office shelf B'
  status           text NOT NULL DEFAULT 'logged'
                   CHECK (status IN ('logged','claimed','returned','shipped','donated','disposed')),
  released_at      timestamptz,
  released_note    text,
  created_at       timestamptz NOT NULL DEFAULT now()
);

-- ---------------------------------------------------------------------------
-- Indexes for the queries every board and report runs
-- ---------------------------------------------------------------------------

CREATE INDEX IF NOT EXISTS idx_room_property_status     ON room (property_id, status_code);
CREATE INDEX IF NOT EXISTS idx_room_status_log_room     ON room_status_log (room_id, changed_at);
CREATE INDEX IF NOT EXISTS idx_stay_room                ON stay (room_id, checked_in_at);
CREATE INDEX IF NOT EXISTS idx_reservation_dates        ON reservation (property_id, arrival_date, departure_date);
CREATE INDEX IF NOT EXISTS idx_hk_task_board            ON hk_task (business_date, assignment_id);
CREATE INDEX IF NOT EXISTS idx_hk_task_room             ON hk_task (room_id, business_date);
CREATE INDEX IF NOT EXISTS idx_inspection_room          ON inspection (room_id, inspected_at);
CREATE INDEX IF NOT EXISTS idx_work_order_open          ON work_order (property_id, status);
CREATE INDEX IF NOT EXISTS idx_ooo_oos_open             ON ooo_oos_event (room_id, ended_at);
CREATE INDEX IF NOT EXISTS idx_discrepancy_date         ON discrepancy_report (business_date);
