-- sql/wayleave_04_supporting.sql
-- Wayleave module — Phase 0 step 4 of 5.
-- Journal, releases, and land-registry link table.
-- File uploads are handled by the shared public.file_uploads pattern — see sql/wayleave_05_file_uploads_integration.sql.
-- Prereq: sql/wayleave_01_core.sql has been run.
-- Run: psql -U postgres -d netplanner -f sql/wayleave_04_supporting.sql

BEGIN;

CREATE TABLE IF NOT EXISTS wayleave.agreement_journal (
    id           bigserial PRIMARY KEY,
    agreement_id integer NOT NULL REFERENCES wayleave.agreements(agreement_id) ON DELETE CASCADE,
    user_id      integer NOT NULL,
    log_datetime timestamp NOT NULL DEFAULT now(),
    log_text     text NOT NULL,
    is_system    boolean NOT NULL DEFAULT false   -- true for system-generated entries (field changes)
);
CREATE INDEX IF NOT EXISTS idx_agr_journal_agreement ON wayleave.agreement_journal(agreement_id, log_datetime DESC);

CREATE TABLE IF NOT EXISTS wayleave.agreement_releases (
    id                bigserial PRIMARY KEY,
    agreement_id      integer NOT NULL REFERENCES wayleave.agreements(agreement_id) ON DELETE CASCADE,
    uprn              bigint  NOT NULL,
    release_date      date,
    sales_ref         varchar(100),
    pic_ref           varchar(100),
    notes             text,
    released_user     integer,
    released_datetime timestamp,
    created_datetime  timestamp NOT NULL DEFAULT now(),
    created_user      integer NOT NULL,
    modified_datetime timestamp,
    modified_user     integer,
    is_deleted        boolean NOT NULL DEFAULT false,
    UNIQUE (agreement_id, uprn)
);
CREATE INDEX IF NOT EXISTS idx_agr_releases_agreement ON wayleave.agreement_releases(agreement_id) WHERE is_deleted=false;

-- Land registry link table (freehold/leasehold titles associated with agreement)
CREATE TABLE IF NOT EXISTS wayleave.agreement_landregistry (
    id             bigserial PRIMARY KEY,
    agreement_id   integer NOT NULL REFERENCES wayleave.agreements(agreement_id) ON DELETE CASCADE,
    title_number   varchar(50) NOT NULL,
    tenure         varchar(20),               -- 'Freehold' | 'Leasehold'
    added_user     integer NOT NULL,
    added_datetime timestamp NOT NULL DEFAULT now(),
    is_deleted     boolean NOT NULL DEFAULT false,
    UNIQUE (agreement_id, title_number)
);
CREATE INDEX IF NOT EXISTS idx_agr_lr_agreement ON wayleave.agreement_landregistry(agreement_id) WHERE is_deleted=false;

COMMIT;

-- Verify:
--   \dt wayleave.*
-- Should list (at minimum): agreement_journal, agreement_releases, agreement_landregistry,
-- agreements, agreements_history, agreement_status, and all agreement_field_* tables.
-- (File uploads live in public.file_uploads with entity='wayleave'; see wayleave_05_file_uploads_integration.sql.)
