-- ============================================================================
-- The Long-Term Rental Data Model, reference schema v0.1 (PostgreSQL)
-- https://rapideyeinspections.com/blog/rental-property-data-model/
--
-- A reference data model for building internal long-term rental operations
-- tools: properties, units, and owners; tenants, applications, and leases;
-- the append-only rent ledger; move-in and move-out inspections as paired
-- checklists; the security deposit and its documentation-backed deductions;
-- the unit turn lifecycle; and maintenance. 22 tables in 7 layers.
--
-- Companions:
--   Hotels: https://rapideyeinspections.com/blog/hotel-operations-data-model/
--   STR:    https://rapideyeinspections.com/blog/str-operations-data-model/
--
-- Design principles (explained in full on the page above):
--   1. The deposit clock is law, and it varies by state. Return deadlines
--      and their statutes live in a seeded reference table
--      (state_deposit_rule) as data, not as constants in application code.
--      California gives you 21 days (Civil Code 1950.5); Texas gives you 30
--      (Property Code 92.103); New York gives you 14 (General Obligations
--      Law 7-108). Code that hardcodes any one of them is wrong in the
--      other 49 states.
--   2. A deduction is only as strong as its documentation. Every
--      deposit_deduction row carries a NOT NULL foreign key to the
--      inspection_item that documents the condition it charges for. An
--      undocumented deduction cannot be inserted; that is the point, and
--      it mirrors how deposit disputes are actually decided. Unpaid rent
--      is settled through the ledger (entry_type 'deposit_applied'), never
--      as a condition deduction.
--   3. Move-in and move-out are the same checklist, run twice. Inspection
--      items are keyed (room_label, item) so a move-out row joins to its
--      move-in twin in one query, and the move-out inspection points at
--      its baseline via baseline_inspection_id. Damage is a delta between
--      two documented states, not an assertion about one.
--   4. Turn status is a projection of milestone dates, never a typed
--      field. unit_turn.status is a generated column derived from which
--      dates are set; the legal edges of the lifecycle are seeded in
--      unit_turn_transition so an application (or an AI agent) can
--      validate a proposed move with one lookup.
--   5. The rent ledger is append-only. Corrections are new adjustment
--      rows, never updates; the balance of a lease is SUM(amount) over
--      its entries. History that can be edited is not history.
--   6. Evidence beats adjectives. An inspection item rated damaged or
--      missing with zero photos is rejected by a CHECK constraint. A
--      finding that cannot be shown did not happen.
--
-- Tested against PostgreSQL 14+. Re-running the file is a no-op by design
-- (CREATE TABLE IF NOT EXISTS, seeded rows ON CONFLICT DO NOTHING).
--
-- License: free to use, adapt, and build on, with or without attribution.
-- ============================================================================

-- ---------------------------------------------------------------------------
-- 1. PORTFOLIO: what you manage, and for whom
-- ---------------------------------------------------------------------------

-- The owner is a first-class entity because third-party managers answer to
-- one: statements, disbursements, and "why did the turn cost $4,000" all
-- roll up to the owner of record.
CREATE TABLE IF NOT EXISTS property_owner (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name        text NOT NULL,             -- person or entity name
  owner_type  text NOT NULL DEFAULT 'individual'
              CHECK (owner_type IN ('individual','llc','trust','corporation','partnership','other')),
  email       text,
  phone       text,
  created_at  timestamptz NOT NULL DEFAULT now()
);

-- state_code matters more here than in most models: it is the join key
-- into state_deposit_rule, where the deposit-return clock for every lease
-- at this property lives.
CREATE TABLE IF NOT EXISTS property (
  id                uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  property_owner_id uuid REFERENCES property_owner(id),
  name              text NOT NULL,       -- 'Maple Court', or just the address
  address_line1     text NOT NULL,
  address_line2     text,
  city              text,
  state_code        text NOT NULL CHECK (char_length(state_code) = 2),  -- USPS code; joins to state_deposit_rule
  postal_code       text,
  property_type     text NOT NULL DEFAULT 'single_family'
                    CHECK (property_type IN ('single_family','condo','townhome','duplex_to_fourplex','small_multifamily','large_multifamily','mixed_use')),
  year_built        int,
  created_at        timestamptz NOT NULL DEFAULT now(),
  updated_at        timestamptz NOT NULL DEFAULT now()
);

-- The unit is the atom everything operational references: leases, turns,
-- inspections, and maintenance all key off it. A single-family home is a
-- property with exactly one unit; the model does not special-case it.
CREATE TABLE IF NOT EXISTS unit (
  id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  property_id  uuid NOT NULL REFERENCES property(id),
  unit_number  text NOT NULL DEFAULT 'MAIN',   -- text, not int: '2B', 'GARDEN', 'ADU'
  bedrooms     numeric(3,1),
  bathrooms    numeric(3,1),
  square_feet  int,
  market_rent  numeric(10,2),            -- asking rent when vacant; actual rent lives on lease
  created_at   timestamptz NOT NULL DEFAULT now(),
  updated_at   timestamptz NOT NULL DEFAULT now(),
  UNIQUE (property_id, unit_number)
);

-- Appliances get identity because they get inspected, they break, and
-- their warranties expire. An inspection item that says 'refrigerator,
-- damaged' and a work order that replaces it should agree on which
-- refrigerator.
CREATE TABLE IF NOT EXISTS appliance (
  id                  uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  unit_id             uuid NOT NULL REFERENCES unit(id),
  category            text NOT NULL
                      CHECK (category IN ('refrigerator','range_oven','dishwasher','microwave','washer','dryer','water_heater','hvac','garbage_disposal','other')),
  brand               text,
  model               text,
  serial_number       text,
  installed_on        date,
  warranty_expires_on date,
  created_at          timestamptz NOT NULL DEFAULT now()
);

-- ---------------------------------------------------------------------------
-- 2. PEOPLE: who works the portfolio, who wants in
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS staff (
  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  full_name  text NOT NULL,
  role       text NOT NULL
             CHECK (role IN ('property_manager','leasing_agent','maintenance_tech','inspector','accountant','other')),
  email      text,
  active     boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now()
);

-- Vendors carry two dates that bite when ignored: certificate-of-insurance
-- expiry (an uninsured vendor on a ladder is the owner's lawsuit) and
-- whether a W-9 is on file (every vendor paid over the reporting threshold
-- needs a 1099 in January).
CREATE TABLE IF NOT EXISTS vendor (
  id                   uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name                 text NOT NULL,
  trade                text NOT NULL DEFAULT 'general'
                       CHECK (trade IN ('cleaning','painting','flooring','plumbing','electrical','hvac','appliance','landscaping','locksmith','pest_control','roofing','general','other')),
  email                text,
  phone                text,
  insurance_expires_on date,             -- COI expiry; schedule nothing past it
  w9_on_file           boolean NOT NULL DEFAULT false,
  active               boolean NOT NULL DEFAULT true,
  created_at           timestamptz NOT NULL DEFAULT now()
);

-- Minimal PII on purpose. An operations tool needs to attribute a lease,
-- a maintenance request, and a deposit refund; it does not need to be a
-- CRM, and it especially does not need to warehouse screening data.
CREATE TABLE IF NOT EXISTS tenant (
  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  full_name  text NOT NULL,
  email      text,
  phone      text,
  created_at timestamptz NOT NULL DEFAULT now()
);

-- The application is its own lifecycle, not a draft tenant. Keep the
-- decision and the reason: a denial based on a screening report triggers
-- adverse-action notice obligations, and "why was this applicant denied"
-- must be answerable later. lease_id is set only on conversion (FK added
-- below, once lease exists).
CREATE TABLE IF NOT EXISTS rental_application (
  id                  uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  unit_id             uuid NOT NULL REFERENCES unit(id),
  applicant_name      text NOT NULL,
  email               text,
  phone               text,
  desired_move_in     date,
  monthly_income      numeric(10,2),
  status              text NOT NULL DEFAULT 'received'
                      CHECK (status IN ('received','screening','approved','denied','withdrawn','converted')),
  screening_report_at timestamptz,       -- when the report came back; the report itself stays in the screening vendor's system
  income_verified     boolean NOT NULL DEFAULT false,
  denial_reason       text,              -- required in practice when status = 'denied'
  decided_at          timestamptz,
  lease_id            uuid,              -- REFERENCES lease(id); FK added after lease exists
  created_at          timestamptz NOT NULL DEFAULT now()
);

-- ---------------------------------------------------------------------------
-- 3. LEASES AND MONEY: the tenancy and its append-only ledger
-- ---------------------------------------------------------------------------

-- A renewal is a new lease row pointing at its predecessor, not an edit.
-- Editing end_date in place destroys the answer to "what did rent do at
-- this unit over five years".
CREATE TABLE IF NOT EXISTS lease (
  id                  uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  unit_id             uuid NOT NULL REFERENCES unit(id),
  previous_lease_id   uuid REFERENCES lease(id),   -- renewal chain
  lease_type          text NOT NULL DEFAULT 'fixed_term'
                      CHECK (lease_type IN ('fixed_term','month_to_month')),
  start_date          date NOT NULL,
  end_date            date,              -- null for month-to-month
  rent_amount         numeric(10,2) NOT NULL CHECK (rent_amount > 0),
  rent_due_day        int NOT NULL DEFAULT 1 CHECK (rent_due_day BETWEEN 1 AND 28),
  late_fee_grace_days int,               -- statutory floors exist: Colorado requires rent be at least 7 days late (C.R.S. 38-12-105)
  late_fee_amount     numeric(10,2),     -- caps exist too: Colorado caps at the greater of $50 or 5% of the past-due amount
  status              text NOT NULL DEFAULT 'draft'
                      CHECK (status IN ('draft','active','ended')),
  signed_at           timestamptz,
  moved_in_on         date,
  moved_out_on        date,
  created_at          timestamptz NOT NULL DEFAULT now(),
  updated_at          timestamptz NOT NULL DEFAULT now(),
  CHECK (end_date IS NULL OR end_date > start_date),
  CHECK (lease_type = 'month_to_month' OR end_date IS NOT NULL)
);

-- Late FK for the column declared before lease existed.
-- Wrapped for idempotency: ADD CONSTRAINT has no IF NOT EXISTS.
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'rental_application_lease_id_fkey') THEN
    ALTER TABLE rental_application ADD CONSTRAINT rental_application_lease_id_fkey
      FOREIGN KEY (lease_id) REFERENCES lease(id);
  END IF;
END $$;

-- People-on-lease is many-to-many with roles. A guarantor signs but does
-- not occupy; an occupant lives there but may not be liable. Deposit
-- refunds and notices go to tenants, jointly, which is why the join table
-- exists instead of a tenant_id column on lease.
CREATE TABLE IF NOT EXISTS lease_tenant (
  lease_id   uuid NOT NULL REFERENCES lease(id),
  tenant_id  uuid NOT NULL REFERENCES tenant(id),
  role       text NOT NULL DEFAULT 'primary'
             CHECK (role IN ('primary','co_tenant','occupant','guarantor')),
  PRIMARY KEY (lease_id, tenant_id)
);

-- Append-only. Corrections are new 'adjustment' rows, never UPDATEs, and
-- the balance of a lease is SUM(amount): charges positive, money in
-- negative. The sign CHECK makes a mis-signed entry impossible to insert.
-- 'deposit_applied' is how unpaid rent meets the security deposit: rent
-- owed is settled here in the ledger, never modeled as a condition
-- deduction (see deposit_deduction below for why).
CREATE TABLE IF NOT EXISTS rent_ledger_entry (
  id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  lease_id     uuid NOT NULL REFERENCES lease(id),
  entry_type   text NOT NULL
               CHECK (entry_type IN ('rent_charge','other_charge','late_fee','payment','credit','deposit_applied','adjustment')),
  amount       numeric(10,2) NOT NULL CHECK (amount <> 0),
  effective_on date NOT NULL,
  method       text CHECK (method IN ('ach','check','card','cash','money_order','other')),
  memo         text,
  created_by   uuid REFERENCES staff(id),
  created_at   timestamptz NOT NULL DEFAULT now(),
  CHECK (
       (entry_type IN ('rent_charge','other_charge','late_fee') AND amount > 0)
    OR (entry_type IN ('payment','credit','deposit_applied') AND amount < 0)
    OR (entry_type = 'adjustment')
  )
);

-- Notices are dated legal facts, not emails. served_on starts clocks
-- (notice periods, turn planning); keep the document, the method, and the
-- date, because "when was notice actually served" decides disputes.
CREATE TABLE IF NOT EXISTS notice (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  lease_id        uuid NOT NULL REFERENCES lease(id),
  notice_type     text NOT NULL
                  CHECK (notice_type IN ('tenant_notice_to_vacate','landlord_non_renewal','lease_violation','entry_notice','rent_increase','termination')),
  served_on       date NOT NULL,
  effective_on    date,
  delivery_method text CHECK (delivery_method IN ('hand','mail','certified_mail','email','posting')),
  document_uri    text,
  note            text,
  created_at      timestamptz NOT NULL DEFAULT now(),
  CHECK (effective_on IS NULL OR effective_on >= served_on)
);

-- ---------------------------------------------------------------------------
-- 4. INSPECTIONS: the same checklist, run twice
-- ---------------------------------------------------------------------------

-- Three kinds, one table. A move_in inspection is the baseline; a
-- move_out inspection points back at that baseline through
-- baseline_inspection_id so the comparison is a join, not an argument.
-- Massachusetts writes the baseline into law (the statement of condition,
-- M.G.L. c.186 s.15B); California requires photographs at both ends for
-- new tenancies (Civil Code 1950.5(g)). The model treats every state as
-- if it were the strictest one.
CREATE TABLE IF NOT EXISTS inspection (
  id                     uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  unit_id                uuid NOT NULL REFERENCES unit(id),
  lease_id               uuid REFERENCES lease(id),
  inspection_type        text NOT NULL
                         CHECK (inspection_type IN ('move_in','move_out','routine')),
  baseline_inspection_id uuid REFERENCES inspection(id),  -- the move_in this one is compared against
  inspected_at           timestamptz NOT NULL DEFAULT now(),
  inspected_by           uuid REFERENCES staff(id),
  tenant_present         boolean,        -- several statutes give the tenant a right to be there
  summary                text,
  created_at             timestamptz NOT NULL DEFAULT now(),
  CHECK (baseline_inspection_id IS NULL OR inspection_type IN ('move_out','routine'))
);

-- One row per checklist line: a room, an item, a condition, and evidence.
-- The UNIQUE key is the pairing mechanism: because (room_label, item) is
-- stable within an inspection, the move-out row for 'bedroom_1 / carpet'
-- joins to the move-in row for 'bedroom_1 / carpet' in one query. Run the
-- same checklist both times or the join has holes.
--
-- The photo CHECK is deliberate: an item asserted damaged or missing with
-- zero photos is rejected. Deductions are won on documentation, and this
-- is where the documentation either exists or does not.
CREATE TABLE IF NOT EXISTS inspection_item (
  id               uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  inspection_id    uuid NOT NULL REFERENCES inspection(id),
  room_label       text NOT NULL,        -- 'bedroom_1', 'kitchen', 'exterior'
  item             text NOT NULL,        -- 'carpet', 'north_wall', 'refrigerator'
  condition_rating text NOT NULL
                   CHECK (condition_rating IN ('new','good','fair','worn','damaged','missing')),
  notes            text,
  photo_count      int NOT NULL DEFAULT 0 CHECK (photo_count >= 0),
  appliance_id     uuid REFERENCES appliance(id),  -- when the item IS an appliance, say which one
  created_at       timestamptz NOT NULL DEFAULT now(),
  UNIQUE (inspection_id, room_label, item),
  CHECK (condition_rating NOT IN ('damaged','missing') OR photo_count > 0)
);

-- ---------------------------------------------------------------------------
-- 5. THE DEPOSIT: a clock, a ledger, and documentation
-- ---------------------------------------------------------------------------

-- The deposit-return clock as data. Application code asks this table,
-- not a constants file, because the answer depends on the property's
-- state and changes when legislatures meet. Seeded below with eight
-- states, each verified against the statute named in the row; add your
-- own states the same way. return_deadline_days is the itemized-statement
-- / refund deadline; claim_notice_deadline_days is for two-clock states
-- like Florida, where returning without a claim (15 days) and noticing a
-- claim (30 days) run on different deadlines.
CREATE TABLE IF NOT EXISTS state_deposit_rule (
  state_code                 text PRIMARY KEY CHECK (char_length(state_code) = 2),
  return_deadline_days       int NOT NULL,
  claim_notice_deadline_days int,        -- null where one clock covers both
  deadline_note              text,
  deposit_cap                text,       -- text, not months: caps carry conditions
  documentation_note         text,       -- what must accompany the itemized statement
  statute                    text NOT NULL,
  verified_on                date NOT NULL
);

INSERT INTO state_deposit_rule
  (state_code, return_deadline_days, claim_notice_deadline_days, deadline_note, deposit_cap, documentation_note, statute, verified_on)
VALUES
  ('CA', 21, NULL,
   '21 calendar days after the tenant vacates to furnish the itemized statement and any refund.',
   'One month''s rent; small landlords meeting the statute''s criteria may collect up to two months.',
   'Copies of invoices or receipts must accompany the statement unless total deductions for repairs and cleaning are $125 or less. For tenancies beginning July 1, 2025 or later, photographs of the unit are required at move-in, after possession is returned, and after repairs.',
   'Cal. Civ. Code 1950.5', DATE '2026-08-01'),
  ('TX', 30, NULL,
   'Refund on or before the 30th day after the tenant surrenders the premises.',
   'No statutory cap.',
   'Itemized list of deductions required when any portion is retained.',
   'Tex. Prop. Code 92.103', DATE '2026-08-01'),
  ('NY', 14, NULL,
   '14 days after the tenant vacates; itemized statement plus any remaining deposit. Missing the deadline forfeits the right to retain any portion.',
   'One month''s rent.',
   'Itemized statement required; tenant may request a pre-vacancy inspection.',
   'N.Y. Gen. Oblig. Law 7-108', DATE '2026-08-01'),
  ('FL', 15, 30,
   'Two clocks: return within 15 days if making no claim; give written notice of intent to impose a claim within 30 days. Tenant then has 15 days to object.',
   'No statutory cap.',
   'Written notice of claim by certified mail (or authorized email) to the tenant''s last known address.',
   'Fla. Stat. 83.49(3)', DATE '2026-08-01'),
  ('WA', 30, NULL,
   '30 days after termination and vacation to deliver a full and specific statement and any refund.',
   'No general statutory cap.',
   'Statement must include copies of estimates or invoices substantiating damage charges; landlord-performed repairs require documentation of materials plus time spent and hourly rate.',
   'RCW 59.18.280', DATE '2026-08-01'),
  ('AZ', 14, NULL,
   '14 days, excluding Saturdays, Sundays, and legal holidays, after termination, delivery of possession, and demand.',
   'One and one-half months'' rent.',
   'Itemized list of deductions required.',
   'A.R.S. 33-1321', DATE '2026-08-01'),
  ('MA', 30, NULL,
   '30 days after termination of occupancy.',
   'First month''s rent.',
   'Itemized list of damages sworn under the pains and penalties of perjury; a written statement of condition is required near move-in.',
   'M.G.L. c. 186, s. 15B', DATE '2026-08-01'),
  ('CO', 30, NULL,
   'One month after lease termination or surrender and acceptance, whichever is later; the lease may extend this, to no more than 60 days.',
   'No general statutory cap.',
   'Written statement listing exact reasons for retention, delivered with the balance. Normal wear and tear may not be retained for.',
   'C.R.S. 38-12-103', DATE '2026-08-01')
ON CONFLICT (state_code) DO NOTHING;

-- One deposit per lease, enforced. The generated status is a projection
-- of the return facts, never typed: 'held' until disposition, then
-- returned / partially / fully withheld by arithmetic.
CREATE TABLE IF NOT EXISTS security_deposit (
  id                  uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  lease_id            uuid NOT NULL UNIQUE REFERENCES lease(id),
  amount              numeric(10,2) NOT NULL CHECK (amount >= 0),
  collected_on        date,
  held_in             text,              -- account or escrow description; some states require disclosure
  disposition_sent_on date,              -- the itemized statement date; THIS is what the statutory clock judges
  returned_on         date,
  amount_returned     numeric(10,2),
  status              text GENERATED ALWAYS AS (
                        CASE
                          WHEN returned_on IS NULL AND disposition_sent_on IS NULL THEN 'held'
                          WHEN amount_returned IS NULL OR amount_returned = 0    THEN 'fully_withheld'
                          WHEN amount_returned >= amount                          THEN 'returned_in_full'
                          ELSE 'partially_withheld'
                        END
                      ) STORED,
  created_at          timestamptz NOT NULL DEFAULT now(),
  CHECK (amount_returned IS NULL OR (amount_returned >= 0 AND amount_returned <= amount)),
  CHECK (returned_on IS NULL OR amount_returned IS NOT NULL)
);

-- The table this whole model bends around. inspection_item_id is NOT NULL
-- on purpose: a deduction that cannot point at the documented condition
-- finding it charges for cannot be inserted. That is not an
-- inconvenience; it is the design. Deposit disputes are decided on
-- documentation, and several statutes (Washington's RCW 59.18.280 most
-- explicitly) demand substantiation for every charge. Wear and tear is
-- not deductible anywhere; 'worn' items make bad deductions, which is
-- why categories here are about damage, not condition drift. Unpaid rent
-- never appears here: it settles in the rent ledger as 'deposit_applied'.
CREATE TABLE IF NOT EXISTS deposit_deduction (
  id                  uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  security_deposit_id uuid NOT NULL REFERENCES security_deposit(id),
  inspection_item_id  uuid NOT NULL REFERENCES inspection_item(id),  -- the documented finding; no finding, no deduction
  category            text NOT NULL
                      CHECK (category IN ('damage','cleaning_beyond_normal','missing_item','unreturned_keys','other')),
  amount              numeric(10,2) NOT NULL CHECK (amount > 0),
  description         text NOT NULL,     -- what appears on the itemized statement
  vendor_id           uuid REFERENCES vendor(id),  -- who did or quoted the repair
  receipt_uri         text,              -- invoice, estimate, or receipt backing the amount
  created_at          timestamptz NOT NULL DEFAULT now()
);

-- ---------------------------------------------------------------------------
-- 6. THE TURN: notice to new lease, as a state machine
-- ---------------------------------------------------------------------------

-- The make-ready lifecycle. status is GENERATED, never written by hand:
-- it is a pure projection of which milestone facts exist, in the spirit
-- of the hotel model's room.status_code. Setting market_ready_on without
-- make_ready_started_on, or attaching a new lease before the unit was
-- market-ready, violates a CHECK: the milestones must arrive in order
-- because the physical work arrives in order.
CREATE TABLE IF NOT EXISTS unit_turn (
  id                     uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  unit_id                uuid NOT NULL REFERENCES unit(id),
  vacating_lease_id      uuid NOT NULL REFERENCES lease(id),
  notice_id              uuid REFERENCES notice(id),      -- the notice that opened the turn
  new_lease_id           uuid REFERENCES lease(id),       -- set when the unit is handed over; closes the turn
  notice_received_on     date NOT NULL,
  expected_move_out_on   date,
  moved_out_on           date,
  move_out_inspection_id uuid REFERENCES inspection(id),
  make_ready_started_on  date,
  market_ready_on        date,
  status                 text GENERATED ALWAYS AS (
                           CASE
                             WHEN new_lease_id IS NOT NULL          THEN 'leased'
                             WHEN market_ready_on IS NOT NULL       THEN 'market_ready'
                             WHEN make_ready_started_on IS NOT NULL THEN 'make_ready'
                             WHEN moved_out_on IS NOT NULL          THEN 'move_out_inspection'
                             ELSE 'notice_given'
                           END
                         ) STORED,
  created_at             timestamptz NOT NULL DEFAULT now(),
  updated_at             timestamptz NOT NULL DEFAULT now(),
  CHECK (moved_out_on IS NULL OR moved_out_on >= notice_received_on),
  CHECK (make_ready_started_on IS NULL OR moved_out_on IS NOT NULL),
  CHECK (market_ready_on IS NULL OR make_ready_started_on IS NOT NULL),
  CHECK (market_ready_on IS NULL OR make_ready_started_on IS NULL OR market_ready_on >= make_ready_started_on),
  CHECK (new_lease_id IS NULL OR market_ready_on IS NOT NULL)
);

-- The legal edges of the turn lifecycle, as data, so an application (or
-- an AI agent) can validate a proposed move with one lookup instead of
-- hard-coding the rules. Add rows to loosen the machine, delete rows to
-- tighten it.
CREATE TABLE IF NOT EXISTS unit_turn_transition (
  from_status   text NOT NULL CHECK (from_status IN ('notice_given','move_out_inspection','make_ready','market_ready','leased')),
  to_status     text NOT NULL CHECK (to_status   IN ('notice_given','move_out_inspection','make_ready','market_ready','leased')),
  trigger_event text NOT NULL,
  PRIMARY KEY (from_status, to_status)
);

INSERT INTO unit_turn_transition (from_status, to_status, trigger_event) VALUES
  ('notice_given',        'move_out_inspection', 'tenant vacated and returned keys; the move-out inspection runs against the move-in baseline'),
  ('move_out_inspection', 'make_ready',          'move-out inspection completed; findings split into deposit deductions and the make-ready scope'),
  ('make_ready',          'market_ready',        'make-ready tasks complete and the final walk passed'),
  ('market_ready',        'make_ready',          'a showing or pre-move-in walk surfaced new work; the unit goes back into make-ready'),
  ('market_ready',        'leased',              'new lease signed and the unit handed over; the turn closes')
ON CONFLICT (from_status, to_status) DO NOTHING;

-- Sequenced tasks within a turn. source records where each task came
-- from, because "how much of this turn was tenant damage vs routine
-- refresh" is the question that decides who pays for it: tasks with
-- source 'move_out_inspection' carry the inspection_item that raised
-- them, the same item a deposit_deduction may point at.
CREATE TABLE IF NOT EXISTS unit_turn_task (
  id                 uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  unit_turn_id       uuid NOT NULL REFERENCES unit_turn(id),
  seq                int NOT NULL,
  task_type          text NOT NULL
                     CHECK (task_type IN ('cleaning','painting','flooring','repair','appliance','landscaping','rekey','punch_list','final_walk','other')),
  title              text NOT NULL,
  source             text NOT NULL DEFAULT 'standard_turn'
                     CHECK (source IN ('move_out_inspection','standard_turn','ad_hoc')),
  inspection_item_id uuid REFERENCES inspection_item(id),  -- the finding that raised this task, if one did
  vendor_id          uuid REFERENCES vendor(id),
  assigned_staff_id  uuid REFERENCES staff(id),
  status             text NOT NULL DEFAULT 'pending'
                     CHECK (status IN ('pending','scheduled','in_progress','done','verified','skipped')),
  scheduled_for      date,
  completed_at       timestamptz,
  actual_cost        numeric(10,2),
  note               text,
  created_at         timestamptz NOT NULL DEFAULT now(),
  UNIQUE (unit_turn_id, seq)
);

-- ---------------------------------------------------------------------------
-- 7. MAINTENANCE: what breaks between turns
-- ---------------------------------------------------------------------------

-- The tenant-reported issue, kept separate from the work order that fixes
-- it: one request can spawn several work orders, and triage (is this an
-- emergency, does it touch habitability, may we enter) happens here
-- before anyone is dispatched. The is_habitability flag matters because
-- habitability issues run on legal clocks that cosmetic ones do not.
CREATE TABLE IF NOT EXISTS maintenance_request (
  id                    uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  unit_id               uuid NOT NULL REFERENCES unit(id),
  lease_id              uuid REFERENCES lease(id),
  reported_by_tenant_id uuid REFERENCES tenant(id),
  channel               text NOT NULL DEFAULT 'portal'
                        CHECK (channel IN ('portal','phone','email','text','inspection','staff')),
  category              text NOT NULL DEFAULT 'other'
                        CHECK (category IN ('plumbing','electrical','hvac','appliance','pest','structural','safety','cosmetic','other')),
  priority              text NOT NULL DEFAULT 'routine'
                        CHECK (priority IN ('emergency','urgent','routine')),
  is_habitability       boolean NOT NULL DEFAULT false,
  entry_permission      boolean,         -- did the tenant authorize entry without them present
  description           text NOT NULL,
  status                text NOT NULL DEFAULT 'open'
                        CHECK (status IN ('open','triaged','scheduled','in_progress','completed','verified','cancelled')),
  reported_at           timestamptz NOT NULL DEFAULT now(),
  completed_at          timestamptz,
  created_at            timestamptz NOT NULL DEFAULT now()
);

-- The unit of dispatched work. source answers "how much of our
-- maintenance is reactive vs found by inspection vs turn work"; the
-- optional links carry the provenance: a request, a routine-inspection
-- finding, or a turn task that needed a licensed trade.
CREATE TABLE IF NOT EXISTS work_order (
  id                     uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  property_id            uuid NOT NULL REFERENCES property(id),
  unit_id                uuid REFERENCES unit(id),          -- null for property-level work (roof, common area)
  source                 text NOT NULL DEFAULT 'ad_hoc'
                         CHECK (source IN ('maintenance_request','routine_inspection','move_out_inspection','unit_turn','preventive','ad_hoc')),
  maintenance_request_id uuid REFERENCES maintenance_request(id),
  inspection_item_id     uuid REFERENCES inspection_item(id),
  unit_turn_task_id      uuid REFERENCES unit_turn_task(id),
  appliance_id           uuid REFERENCES appliance(id),
  vendor_id              uuid REFERENCES vendor(id),
  assigned_staff_id      uuid REFERENCES staff(id),
  priority               text NOT NULL DEFAULT 'routine'
                         CHECK (priority IN ('emergency','urgent','routine','low')),
  status                 text NOT NULL DEFAULT 'open'
                         CHECK (status IN ('open','assigned','scheduled','in_progress','on_hold','completed','verified','cancelled')),
  title                  text NOT NULL,
  description            text,
  estimate_amount        numeric(10,2),
  invoice_amount         numeric(10,2),
  invoice_uri            text,
  scheduled_for          date,
  completed_at           timestamptz,
  created_at             timestamptz NOT NULL DEFAULT now(),
  updated_at             timestamptz NOT NULL DEFAULT now()
);

-- ---------------------------------------------------------------------------
-- Indexes for the queries every rent roll, turn board, and report runs
-- ---------------------------------------------------------------------------

CREATE INDEX IF NOT EXISTS idx_unit_property            ON unit (property_id);
CREATE INDEX IF NOT EXISTS idx_lease_unit_status        ON lease (unit_id, status);
CREATE INDEX IF NOT EXISTS idx_ledger_lease_date        ON rent_ledger_entry (lease_id, effective_on);
CREATE INDEX IF NOT EXISTS idx_notice_lease             ON notice (lease_id, served_on);
CREATE INDEX IF NOT EXISTS idx_inspection_unit          ON inspection (unit_id, inspected_at);
CREATE INDEX IF NOT EXISTS idx_inspection_lease         ON inspection (lease_id);
CREATE INDEX IF NOT EXISTS idx_inspection_item_insp     ON inspection_item (inspection_id);
CREATE INDEX IF NOT EXISTS idx_deduction_deposit        ON deposit_deduction (security_deposit_id);
CREATE INDEX IF NOT EXISTS idx_unit_turn_unit_status    ON unit_turn (unit_id, status);
CREATE INDEX IF NOT EXISTS idx_turn_task_turn_status    ON unit_turn_task (unit_turn_id, status);
CREATE INDEX IF NOT EXISTS idx_maint_request_unit       ON maintenance_request (unit_id, status);
CREATE INDEX IF NOT EXISTS idx_work_order_open          ON work_order (property_id, status);

-- ---------------------------------------------------------------------------
-- The queries the model was shaped around
-- ---------------------------------------------------------------------------

-- Move-out vs move-in, side by side, for one unit's turn:
--
--   SELECT mo.room_label, mo.item,
--          mi.condition_rating AS at_move_in,
--          mo.condition_rating AS at_move_out,
--          mo.photo_count      AS move_out_photos
--   FROM inspection_item mo
--   JOIN inspection i        ON i.id = mo.inspection_id
--   JOIN inspection_item mi  ON mi.inspection_id = i.baseline_inspection_id
--                           AND mi.room_label = mo.room_label
--                           AND mi.item       = mo.item
--   WHERE i.id = :move_out_inspection_id
--     AND mo.condition_rating IS DISTINCT FROM mi.condition_rating;
--
-- Every deduction on a deposit, with its documentation, ready for the
-- itemized statement:
--
--   SELECT dd.description, dd.amount, dd.category,
--          ii.room_label, ii.item, ii.condition_rating, ii.photo_count,
--          dd.receipt_uri
--   FROM deposit_deduction dd
--   JOIN inspection_item ii ON ii.id = dd.inspection_item_id
--   WHERE dd.security_deposit_id = :deposit_id
--   ORDER BY ii.room_label, ii.item;
--
-- Days left on the deposit clock for every recently ended lease:
--
--   SELECT l.id AS lease_id, p.state_code, r.return_deadline_days,
--          l.moved_out_on + r.return_deadline_days AS statement_due_on,
--          (l.moved_out_on + r.return_deadline_days) - CURRENT_DATE AS days_left
--   FROM lease l
--   JOIN unit u  ON u.id = l.unit_id
--   JOIN property p ON p.id = u.property_id
--   JOIN state_deposit_rule r ON r.state_code = p.state_code
--   JOIN security_deposit sd ON sd.lease_id = l.id
--   WHERE l.moved_out_on IS NOT NULL AND sd.disposition_sent_on IS NULL;
