-- ============================================================================
-- The STR Operations Data Model, reference schema v0.1 (PostgreSQL)
-- https://rapideyeinspections.com/blog/str-operations-data-model/
--
-- A reference data model for building internal vacation rental operations
-- tools: turnovers, cleaning and inspection tasks, SOP checklists, damage
-- findings, condition media, assets, and work orders.
--
-- Design principles (explained in full on the page above):
--   1. Your PMS stays the system of record for reservations. The booking
--      layer here is a one-way synced mirror, keyed by (source, external_id)
--      so ingestion is idempotent. Never write bookings here first.
--   2. The turnover is the pivot entity. It sits between the reservation
--      that just ended and the one about to begin, which is what makes
--      damage attribution possible at all.
--   3. Condition evidence (media) is first-class, with capture time and a
--      content hash, because documentation you cannot trust is worthless.
--   4. Checklists are versioned and immutable; completed work pins the
--      version it was performed against.
--   5. All timestamps are UTC (timestamptz). The property carries its IANA
--      timezone; render local time at the edge, never store it.
--
-- License: free to use, adapt, and build on, with or without attribution.
-- ============================================================================

-- ---------------------------------------------------------------------------
-- 1. PROPERTY LAYER
-- ---------------------------------------------------------------------------

CREATE TABLE properties (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name          text NOT NULL,
  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/New_York'
  status        text NOT NULL DEFAULT 'active',  -- active | onboarding | offboarded
  source        text,             -- PMS this property syncs from, e.g. 'guesty'
  external_id   text,             -- id in that PMS
  created_at    timestamptz NOT NULL DEFAULT now(),
  updated_at    timestamptz NOT NULL DEFAULT now(),
  UNIQUE (source, external_id)
);

-- Units let the same model serve a 1-unit beach house and a 40-unit
-- aparthotel. Single-unit properties still get exactly one row here;
-- operations tables reference units, never properties directly.
CREATE TABLE units (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  property_id   uuid NOT NULL REFERENCES properties(id),
  name          text NOT NULL DEFAULT 'Main',   -- 'Main', 'Unit 2B', 'Casita'
  bedrooms      numeric(3,1),
  bathrooms     numeric(3,1),
  max_occupancy int,
  floor_area_m2 numeric(7,1),
  status        text NOT NULL DEFAULT 'active',
  created_at    timestamptz NOT NULL DEFAULT now(),
  updated_at    timestamptz NOT NULL DEFAULT now()
);

-- A unit's presence on a channel. One unit often has several listings
-- (Airbnb, Vrbo, direct site). The ical_url is the universal fallback:
-- every major channel exports one, so a tool with nothing but this table
-- can still see bookings.
CREATE TABLE listings (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  unit_id       uuid NOT NULL REFERENCES units(id),
  channel       text NOT NULL,    -- airbnb | vrbo | booking | direct | other
  external_id   text,             -- listing id on the channel
  url           text,
  ical_url      text,
  status        text NOT NULL DEFAULT 'active',
  created_at    timestamptz NOT NULL DEFAULT now(),
  updated_at    timestamptz NOT NULL DEFAULT now(),
  UNIQUE (channel, external_id)
);

-- ---------------------------------------------------------------------------
-- 2. BOOKING LAYER (synced mirror; the PMS remains the system of record)
-- ---------------------------------------------------------------------------

-- Keep guest PII minimal. An operations tool needs to know who to attribute
-- a stay to, not to store a CRM. If you need more, put it somewhere with
-- real access controls and reference it from here.
CREATE TABLE guests (
  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(),
  updated_at    timestamptz NOT NULL DEFAULT now(),
  UNIQUE (source, external_id)
);

CREATE TABLE reservations (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  unit_id       uuid NOT NULL REFERENCES units(id),
  listing_id    uuid REFERENCES listings(id),
  guest_id      uuid REFERENCES guests(id),
  status        text NOT NULL,    -- confirmed | cancelled | completed | no_show
  check_in      timestamptz NOT NULL,
  check_out     timestamptz NOT NULL,
  adults        int,
  children      int,
  pets          int,
  channel       text,
  source        text NOT NULL,    -- which system this row was synced from
  external_id   text NOT NULL,    -- reservation id in that system
  synced_at     timestamptz NOT NULL DEFAULT now(),
  created_at    timestamptz NOT NULL DEFAULT now(),
  updated_at    timestamptz NOT NULL DEFAULT now(),
  UNIQUE (source, external_id),
  CHECK (check_out > check_in)
);

CREATE INDEX idx_reservations_unit_dates ON reservations (unit_id, check_in, check_out);

-- ---------------------------------------------------------------------------
-- 3. PEOPLE LAYER
-- ---------------------------------------------------------------------------

CREATE TABLE vendors (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name          text NOT NULL,
  kind          text NOT NULL,    -- cleaning | maintenance | inspection | other
  email         text,
  phone         text,
  status        text NOT NULL DEFAULT 'active',
  created_at    timestamptz NOT NULL DEFAULT now(),
  updated_at    timestamptz NOT NULL DEFAULT now()
);

-- Staff covers everyone who performs or manages work: in-house employees
-- (vendor_id null) and vendor personnel alike.
CREATE TABLE staff (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  vendor_id     uuid REFERENCES vendors(id),
  full_name     text NOT NULL,
  role          text NOT NULL,    -- cleaner | inspector | maintenance | manager | admin
  email         text,
  phone         text,
  status        text NOT NULL DEFAULT 'active',
  created_at    timestamptz NOT NULL DEFAULT now(),
  updated_at    timestamptz NOT NULL DEFAULT now()
);

-- ---------------------------------------------------------------------------
-- 4. SOP / CHECKLIST LAYER (versioned, immutable per version)
-- ---------------------------------------------------------------------------

-- A checklist row is one VERSION of one SOP. Editing an SOP inserts a new
-- row with version + 1 and retires the old one; completed work keeps
-- pointing at the version it was actually performed against. Without this,
-- editing an SOP silently rewrites history.
CREATE TABLE checklists (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name          text NOT NULL,           -- 'Standard turnover clean'
  kind          text NOT NULL,           -- cleaning | inspection | maintenance | custom
  version       int NOT NULL DEFAULT 1,
  is_current    boolean NOT NULL DEFAULT true,
  created_at    timestamptz NOT NULL DEFAULT now(),
  UNIQUE (name, version)
);

CREATE TABLE checklist_items (
  id             uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  checklist_id   uuid NOT NULL REFERENCES checklists(id),
  section        text,                   -- 'Kitchen', 'Exterior'
  position       int NOT NULL,
  instruction    text NOT NULL,          -- 'Photograph all four walls'
  requires_photo boolean NOT NULL DEFAULT false,
  requires_video boolean NOT NULL DEFAULT false,
  created_at     timestamptz NOT NULL DEFAULT now()
);

-- ---------------------------------------------------------------------------
-- 5. OPERATIONS LAYER (the heart of the model)
-- ---------------------------------------------------------------------------

-- The turnover is the pivot of STR operations: the window between the stay
-- that just ended and the stay about to begin. Both reservation references
-- are nullable on purpose (first-ever turnover has no departure; a gap
-- before a hold has no arrival), but when reservation_out_id is present,
-- everything discovered during this turnover is attributable to that stay.
CREATE TABLE turnovers (
  id                 uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  unit_id            uuid NOT NULL REFERENCES units(id),
  reservation_out_id uuid REFERENCES reservations(id),  -- the departing stay
  reservation_in_id  uuid REFERENCES reservations(id),  -- the arriving stay
  window_start       timestamptz,       -- usually checkout time
  window_end         timestamptz,       -- usually next check-in time
  status             text NOT NULL DEFAULT 'pending',
                     -- pending | scheduled | in_progress | done | failed_inspection
  created_at         timestamptz NOT NULL DEFAULT now(),
  updated_at         timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_turnovers_unit ON turnovers (unit_id, window_start);

-- One turnover usually produces several tasks: a clean, an inspection,
-- sometimes a maintenance visit or a delivery. Tasks can also exist outside
-- any turnover (deep cleans, preventive maintenance), so turnover_id is
-- nullable but unit_id is not.
CREATE TABLE tasks (
  id                 uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  unit_id            uuid NOT NULL REFERENCES units(id),
  turnover_id        uuid REFERENCES turnovers(id),
  kind               text NOT NULL,     -- cleaning | inspection | maintenance | delivery | custom
  status             text NOT NULL DEFAULT 'open',
                     -- open | assigned | in_progress | done | verified | cancelled
  assignee_id        uuid REFERENCES staff(id),
  checklist_id       uuid REFERENCES checklists(id),  -- pinned VERSION, see above
  due_at             timestamptz,
  started_at         timestamptz,
  completed_at       timestamptz,
  verified_by_id     uuid REFERENCES staff(id),
  notes              text,
  created_at         timestamptz NOT NULL DEFAULT now(),
  updated_at         timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_tasks_unit_status ON tasks (unit_id, status);
CREATE INDEX idx_tasks_assignee ON tasks (assignee_id, status);

-- Per-item completion state for a task performed against a checklist.
CREATE TABLE task_item_results (
  id                uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  task_id           uuid NOT NULL REFERENCES tasks(id),
  checklist_item_id uuid NOT NULL REFERENCES checklist_items(id),
  status            text NOT NULL,      -- done | skipped | flagged
  note              text,
  completed_at      timestamptz,
  UNIQUE (task_id, checklist_item_id)
);

-- A finding is anything discovered that needs attention: damage, a
-- cleanliness miss, a maintenance issue, a missing item, a safety hazard.
-- attributed_reservation_id is normally copied from the parent turnover's
-- reservation_out_id at creation time: the stay that just ended is the one
-- that answers for what the turnover found. Denormalized on purpose, so
-- attribution survives even if the turnover row is later edited.
CREATE TABLE findings (
  id                        uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  unit_id                   uuid NOT NULL REFERENCES units(id),
  task_id                   uuid REFERENCES tasks(id),
  attributed_reservation_id uuid REFERENCES reservations(id),
  category                  text NOT NULL,
                            -- damage | cleanliness | maintenance | missing_item | safety
  severity                  text NOT NULL DEFAULT 'minor',
                            -- minor | moderate | major | urgent
  title                     text NOT NULL,
  description               text,
  status                    text NOT NULL DEFAULT 'open',
                            -- open | acknowledged | in_repair | resolved | claimed | waived
  estimated_cost            numeric(10,2),
  resolved_at               timestamptz,
  created_at                timestamptz NOT NULL DEFAULT now(),
  updated_at                timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_findings_unit_status ON findings (unit_id, status);
CREATE INDEX idx_findings_reservation ON findings (attributed_reservation_id);

-- Condition evidence. captured_at comes from the device (EXIF or client
-- clock) and is stored separately from uploaded_at, because the gap between
-- the two is itself a signal. sha256 deduplicates and catches the oldest
-- trick in the book: re-submitting last week's photos for today's clean.
CREATE TABLE media (
  id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  task_id      uuid REFERENCES tasks(id),
  finding_id   uuid REFERENCES findings(id),
  unit_id      uuid NOT NULL REFERENCES units(id),
  kind         text NOT NULL,        -- photo | video
  storage_uri  text NOT NULL,        -- s3://... or equivalent; never a public URL
  room         text,                 -- 'Kitchen', 'Bedroom 2', 'Exterior'
  captured_at  timestamptz,
  uploaded_at  timestamptz NOT NULL DEFAULT now(),
  captured_by  uuid REFERENCES staff(id),
  sha256       text,
  created_at   timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_media_task ON media (task_id);
CREATE INDEX idx_media_unit_time ON media (unit_id, captured_at);

-- ---------------------------------------------------------------------------
-- 6. ASSET / MAINTENANCE LAYER
-- ---------------------------------------------------------------------------

CREATE TABLE assets (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  unit_id       uuid NOT NULL REFERENCES units(id),
  category      text NOT NULL,      -- appliance | hvac | hot_tub | pool | furniture | other
  name          text NOT NULL,      -- 'Samsung washer'
  make          text,
  model         text,
  serial_no     text,
  installed_on  date,
  warranty_until date,
  status        text NOT NULL DEFAULT 'in_service',  -- in_service | needs_repair | retired
  created_at    timestamptz NOT NULL DEFAULT now(),
  updated_at    timestamptz NOT NULL DEFAULT now()
);

-- A work order is the money-and-labor half of a problem; a finding is the
-- discovery half. Most work orders trace back to a finding, but preventive
-- work does not, so finding_id is nullable.
CREATE TABLE work_orders (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  unit_id       uuid NOT NULL REFERENCES units(id),
  asset_id      uuid REFERENCES assets(id),
  finding_id    uuid REFERENCES findings(id),
  vendor_id     uuid REFERENCES vendors(id),
  title         text NOT NULL,
  priority      text NOT NULL DEFAULT 'normal',   -- low | normal | high | emergency
  status        text NOT NULL DEFAULT 'open',
                -- open | scheduled | in_progress | done | cancelled
  scheduled_for timestamptz,
  completed_at  timestamptz,
  cost          numeric(10,2),
  invoice_ref   text,
  created_at    timestamptz NOT NULL DEFAULT now(),
  updated_at    timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_work_orders_unit_status ON work_orders (unit_id, status);

-- ---------------------------------------------------------------------------
-- 7. INTEGRATION LAYER
-- ---------------------------------------------------------------------------

-- Append-only inbox for everything external systems send you (webhooks,
-- iCal poll diffs, API sync runs). Ingest first, process after, keyed by
-- (source, external_event_id) so retries and replays are harmless. When a
-- sync bug appears weeks later, this table is the difference between
-- reconstructing reality and guessing.
CREATE TABLE sync_events (
  id                uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  source            text NOT NULL,      -- 'guesty' | 'airbnb_ical' | 'hostaway' | ...
  event_type        text NOT NULL,      -- 'reservation.updated', 'calendar.polled', ...
  external_event_id text,
  payload           jsonb NOT NULL,
  received_at       timestamptz NOT NULL DEFAULT now(),
  processed_at      timestamptz,
  error             text,
  UNIQUE (source, external_event_id)
);

CREATE INDEX idx_sync_events_unprocessed ON sync_events (received_at) WHERE processed_at IS NULL;

-- Who changed what, when. Populate from application code on every mutation
-- of operational data. Auditability is not optional in a system whose
-- records end up attached to damage claims.
CREATE TABLE audit_log (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  actor_id    uuid,               -- staff.id, or null for system
  entity      text NOT NULL,      -- 'finding', 'task', ...
  entity_id   uuid NOT NULL,
  action      text NOT NULL,      -- 'create' | 'update' | 'status_change' | 'delete'
  changes     jsonb,              -- {"status": ["open", "resolved"]}
  at          timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_audit_entity ON audit_log (entity, entity_id, at);
