-- sql/wayleave_01_core.sql
-- Wayleave module — Phase 0 step 1 of 4.
-- Core tables for the wayleave agreement entity.
-- Prereq: the `wayleave` schema already exists (see geolynx_ddl.sql).
-- Run: psql -U postgres -d netplanner -f sql/wayleave_01_core.sql

BEGIN;

-- Lookup: agreement status
CREATE TABLE IF NOT EXISTS wayleave.agreement_status (
    id            serial PRIMARY KEY,
    description   varchar(100) NOT NULL UNIQUE,
    display_order integer NOT NULL DEFAULT 0,
    is_active     boolean NOT NULL DEFAULT true
);

INSERT INTO wayleave.agreement_status (description, display_order) VALUES
    ('Draft', 10),
    ('In Progress', 20),
    ('On Hold', 30),
    ('Signed', 40),
    ('Complete', 50),
    ('Cancelled', 60)
ON CONFLICT (description) DO NOTHING;

-- Main agreement table
CREATE TABLE IF NOT EXISTS wayleave.agreements (
    agreement_id        serial PRIMARY KEY,
    agreement_name      varchar(250) NOT NULL,
    agreement_reference varchar(100),
    agreement_status_id integer REFERENCES wayleave.agreement_status(id),
    agreement_type      varchar(50),              -- e.g. 'SDU','MDU','Route','Building','Estate'
    wayleave_team       varchar(100),
    bd_manager          integer,                  -- users.users.id
    ecd_date            date,
    signed_date         date,
    geom                public.geometry(MultiPolygon, 27700),  -- bounding union of agreement_polygons (nullable)
    parent_agreement_id integer REFERENCES wayleave.agreements(agreement_id),
    company_id          integer,                  -- multi-tenant
    account_id          integer,                  -- accounts.accounts link (nullable)
    is_deleted          boolean NOT NULL DEFAULT false,
    created_datetime    timestamp NOT NULL DEFAULT now(),
    created_user        integer NOT NULL,
    modified_datetime   timestamp NOT NULL DEFAULT now(),
    modified_user       integer NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_agreements_company ON wayleave.agreements(company_id);
CREATE INDEX IF NOT EXISTS idx_agreements_status  ON wayleave.agreements(agreement_status_id);
CREATE INDEX IF NOT EXISTS idx_agreements_parent  ON wayleave.agreements(parent_agreement_id);
CREATE INDEX IF NOT EXISTS idx_agreements_geom    ON wayleave.agreements USING GIST (geom);

-- History table — full mirror + audit columns
CREATE TABLE IF NOT EXISTS wayleave.agreements_history (
    history_id          bigserial PRIMARY KEY,
    agreement_id        integer NOT NULL,
    agreement_name      varchar(250),
    agreement_reference varchar(100),
    agreement_status_id integer,
    agreement_type      varchar(50),
    wayleave_team       varchar(100),
    bd_manager          integer,
    ecd_date            date,
    signed_date         date,
    parent_agreement_id integer,
    company_id          integer,
    account_id          integer,
    is_deleted          boolean,
    history_action      varchar(20) NOT NULL,   -- 'INSERT' | 'UPDATE' | 'DELETE'
    history_field       varchar(100),           -- which column changed (UPDATE only, nullable)
    history_old_value   text,                   -- previous value as text
    history_new_value   text,                   -- new value as text
    history_datetime    timestamp NOT NULL DEFAULT now(),
    history_user        integer NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_agreements_history_id ON wayleave.agreements_history(agreement_id);

COMMIT;

-- Verify:
--   \dt wayleave.*
--   SELECT id, description FROM wayleave.agreement_status ORDER BY display_order;
