-- 056_project_premises_table.sql
-- Project premises, migration 1 of 3
-- (docs/2026-08-17-project-premises-uprn-table.md, Phase 1)
--
-- The table only. Nothing is populated and nothing that anything reads is dropped, so
-- this migration is inert until 057 seeds it — which matters with ~600 live projects
-- in client use. 058 is what points the views and the endpoints at it.
--
-- THE MODEL, in one paragraph. One row per premise per project per source: what the
-- boundary contains, materialised, instead of the live ST_Intersects against
-- basedata.abp that nine queries run today. state = approved is the live set and
-- removed is a tombstone, so a premise that leaves a boundary and comes back keeps the
-- date it first arrived. is_valid says this project is the lowest level in its own
-- lineage that contains the UPRN, which is what stops a premise inside a parent and
-- its children being counted once per project. Because projects have exactly ONE
-- source and it is materialised, nothing has to be re-resolved on read the way
-- wayleave coverage does.
--
-- ARCHIVE, DO NOT DROP. The old projects.project_premises is the half-populated table
-- the abandoned UPRN matching left behind, with no primary key, no index and no PHP
-- reference anywhere — every premise here is reproduced from the boundary by 057, not
-- migrated across. It is renamed rather than dropped anyway, because a client whose
-- numbers look wrong needs something to compare against. Same restore-point pattern as
-- the prospector.*_migrated_20260804 tables. Drop it in a later migration once the
-- numbers are accepted.
--
-- ONLY state IS KEPT FROM THE APPROVAL SCAFFOLDING. No approval_id, no decided_user,
-- no decided_datetime and no projects.project_approvals table: they would sit
-- always-NULL in a live client database until the approval plan knows what a decision
-- needs to record. state earns its place today on the tombstone alone — without it, a
-- boundary shrunk past a premise and regrown loses when that premise first arrived,
-- and nothing can answer why a project count dropped last month. This plan writes only
-- approved and removed.
--
-- Two things are carried forward so that later migration is cheap rather than a
-- rebuild. First, the state CHECK already allows pending and rejected, so widening the
-- behaviour needs new code writing values the column already accepts, not a constraint
-- change. Second, the lessons stay written down here rather than being rediscovered:
-- a decision must record its own membership, the way migration 054 corrected
-- wayleave_approvals to; projects need no source_ref because they have one source,
-- unlike wayleave.wayleave_approvals; and there must be no boundary snapshot column,
-- because projects.projects_history already captures geom on every change (017) and a
-- second copy can disagree with the first.

-- ── 0. Guards ───────────────────────────────────────────────────────────────

DO $$
BEGIN
    IF to_regclass('projects.projects') IS NULL THEN
        RAISE EXCEPTION '056: projects.projects is missing';
    END IF;
    IF to_regclass('projects.project_premises') IS NULL THEN
        RAISE EXCEPTION '056: projects.project_premises is missing — it should exist from the baseline schema';
    END IF;
    IF to_regclass('projects.project_premises_migrated_20260825') IS NOT NULL THEN
        RAISE EXCEPTION '056 has already run — projects.project_premises_migrated_20260825 exists';
    END IF;
END $$;

-- ── 1. Archive the old table ────────────────────────────────────────────────

DO $$
DECLARE
    v_rows bigint;
BEGIN
    SELECT count(*) INTO v_rows FROM projects.project_premises;
    RAISE NOTICE '056: archiving projects.project_premises with % row(s) preserved as project_premises_migrated_20260825', v_rows;
END $$;

ALTER TABLE projects.project_premises RENAME TO project_premises_migrated_20260825;

COMMENT ON TABLE projects.project_premises_migrated_20260825 IS
    'Restore point taken by migration 056 (2026-08-25). The half-populated table the abandoned UPRN matching left behind; nothing read it. Kept only so a client whose premise numbers look wrong has something to compare against — drop once the numbers are accepted.';

-- ── 2. projects.project_premises ────────────────────────────────────────────

CREATE TABLE projects.project_premises (
    id                bigint      GENERATED BY DEFAULT AS IDENTITY,
    project_id        integer     NOT NULL,
    uprn              bigint      NOT NULL,

    -- The only value there is. It exists so a future second source is an INSERT and
    -- not a table rebuild, and so the unique key below already has the right shape.
    source            varchar(20) NOT NULL DEFAULT 'boundary',

    state             varchar(10) NOT NULL DEFAULT 'approved',

    -- The ONLY flag stored, because it is the only one a trigger can maintain: both
    -- its inputs (the boundary and the parent) are columns on projects.projects.
    is_valid          boolean     NOT NULL DEFAULT true,

    -- Who saved the boundary that brought the premise in. NEW.modified_user, which
    -- every write path populates — no dependency on the app.user_id GUC that 017 reads.
    proposed_user     integer,
    proposed_datetime timestamp without time zone NOT NULL DEFAULT now(),
    state_datetime    timestamp without time zone NOT NULL DEFAULT now(),

    CONSTRAINT project_premises_pk PRIMARY KEY (id),
    -- source is NOT NULL precisely so this needs no COALESCE: a nullable column here
    -- would let two identical boundary claims coexist, because NULLs are distinct in a
    -- unique constraint. Migration 042 lesson, restated.
    CONSTRAINT project_premises_uq UNIQUE (project_id, uprn, source),
    CONSTRAINT project_premises_project_fk FOREIGN KEY (project_id)
        REFERENCES projects.projects(project_id) ON DELETE CASCADE,
    -- All four allowed from day one; this plan only ever writes approved and removed.
    CONSTRAINT project_premises_state_ck  CHECK (state  IN ('pending', 'approved', 'rejected', 'removed')),
    -- One value, so widening it later is an explicit migration rather than an accident.
    CONSTRAINT project_premises_source_ck CHECK (source IN ('boundary'))
);

-- The hot path: the premise list and the counts for one project.
CREATE INDEX project_premises_project_state_idx ON projects.project_premises USING btree (project_id, state);
-- The reverse direction: which projects contain this UPRN. Used by the stocklist
-- queries, by the is_valid re-flag in 057, and by vw_premise_claimants in 058, which
-- groups by uprn.
CREATE INDEX project_premises_uprn_state_idx    ON projects.project_premises USING btree (uprn, state);

COMMENT ON TABLE projects.project_premises IS
    'Canonical project premises: one row per premise per project per source, materialised from the boundary rather than resolved live. state = approved is the live set; removed is a tombstone, so a premise leaving a boundary and returning keeps its original proposed_datetime. Projects have exactly one source, so nothing needs re-resolving on read.';
COMMENT ON COLUMN projects.project_premises.state IS
    'pending | approved | rejected | removed. This plan writes only approved and removed; the other two are allowed from day one so the approval work needs no constraint change.';
COMMENT ON COLUMN projects.project_premises.is_valid IS
    'This project is the lowest level in its own lineage that contains the UPRN. The ONLY flag stored — claimant_count and hierarchy_role are derived in projects.vw_project_premises / projects.vw_premise_claimants and recomputed on every read. COUNTING THIS TABLE WITHOUT is_valid DOUBLE COUNTS EVERY HIERARCHY. Several projects can be valid for one UPRN, and that is not a bug: it means unrelated boundaries overlap, which is flagged rather than tie-broken.';
COMMENT ON COLUMN projects.project_premises.proposed_datetime IS
    'When the premise first entered this boundary. Survives a removal and return — a shrink-and-regrow moves state_datetime only.';

-- ── 3. Verify ───────────────────────────────────────────────────────────────

DO $$
DECLARE
    v_cols int;
    v_idx  int;
BEGIN
    IF to_regclass('projects.project_premises') IS NULL THEN
        RAISE EXCEPTION '056: projects.project_premises was not created';
    END IF;
    IF to_regclass('projects.project_premises_migrated_20260825') IS NULL THEN
        RAISE EXCEPTION '056: the archive table is missing — the rename did not happen';
    END IF;

    SELECT count(*) INTO v_cols
      FROM information_schema.columns
     WHERE table_schema = 'projects' AND table_name = 'project_premises';
    IF v_cols <> 9 THEN
        RAISE EXCEPTION '056: project_premises has % column(s), expected 9', v_cols;
    END IF;

    SELECT count(*) INTO v_idx
      FROM pg_indexes
     WHERE schemaname = 'projects' AND tablename = 'project_premises'
       AND indexname IN ('project_premises_project_state_idx', 'project_premises_uprn_state_idx');
    IF v_idx <> 2 THEN
        RAISE EXCEPTION '056: expected both read indexes, found %', v_idx;
    END IF;

    RAISE NOTICE '056: projects.project_premises created empty; 057 adds the refresh function, the triggers and the seed';
END $$;
