-- 034_wayleave_field_meta_rebuild.sql
-- Wayleave module realignment, migration 4 of 6
-- (docs/2026-08-10-wayleave-module-realignment.md, Phase 4)
--
-- The five field-meta tables and the ten EAV value/history tables: dropped and
-- recreated from explicit DDL mirroring projects (via opportunity's 026, which is the
-- same shape written out), plus the static field seed. Requires 033.
--
-- Also adds stocklist_id to wayleave.wayleaves. 032 built the entity table without it
-- and projects.projects has it, so the mirror was incomplete; 032 is applied and
-- therefore immutable, so it is corrected here.
--
-- NO DYNAMIC FIELDS ARE SEEDED. Creating dynamic meta is an admin task, not a
-- migration's (Dave, 2026-08-10). The two that existed (wl_annual_fee, wl_access_date)
-- were test-only and are deliberately not recreated, and with them go the Commercial
-- and Delivery sections that existed solely to hold them. Admin adds those back in
-- seconds if they are wanted.
--
-- STATIC FIELDS ARE seeded, because they are not admin's to create: a static row's
-- field_form_id IS a real column on wayleave.wayleaves, whitelisted in
-- wayleave_save.php. admin_save.php refuses to create, edit or delete them. This
-- mirrors 012 (projects), 015 (the wayleave set this replaces) and 027 (opportunity).
--
-- company_id JOINS THE STATIC SET. 015 never seeded it, so it has never rendered on
-- the wayleave form — the same gap as wayleave_save.php's whitelist omitting it. The
-- column itself has existed since migration 007; only the meta row was missing.
-- Confirmed by Dave 2026-08-10: capture it now.
--
-- ecd_date and signed_date are seeded as input_type = 'date' rather than 015's
-- 'input'. Both render a native picker, but 'input' shows as "input (legacy)" in the
-- admin field modal; re-seeding is the free moment to normalise it.
--
-- TWO PHASE 7 DEPENDENCIES, both of which render as "the field is there but empty"
-- rather than as an error, so they are easy to misread as a migration fault:
--   * field_options_source = 'wayleave_status' needs the matching key in
--     get_update_form.php's whitelist (it currently says 'agreement_status'), or the
--     status dropdown renders with no options.
--   * the wayleavename autocomplete in resolveAutocompleteLabel() still points at
--     wayleave.agreements, which no longer exists — the parent field will resolve
--     nothing until it is re-pointed at wayleave.wayleaves.

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

DO $$
DECLARE
    t text;
BEGIN
    IF to_regclass('wayleave.wayleave_polygons') IS NULL THEN
        RAISE EXCEPTION 'wayleave.wayleave_polygons is missing — apply 033 first';
    END IF;

    FOREACH t IN ARRAY ARRAY[
        'agreement_fields', 'agreement_field_sections', 'agreement_field_category',
        'agreement_field_sub_category', 'agreement_field_dropdown_options'
    ] LOOP
        IF to_regclass('wayleave.' || t) IS NULL THEN
            RAISE EXCEPTION 'wayleave.% is missing — 034 has already run, or the schema is not what this migration expects', t;
        END IF;
    END LOOP;
END $$;

-- ── 1. Complete the projects mirror on the entity table ─────────────────────

ALTER TABLE wayleave.wayleaves ADD COLUMN stocklist_id integer;

COMMENT ON COLUMN wayleave.wayleaves.stocklist_id IS
    'Mirrors projects.projects.stocklist_id. Off-form, as it is for projects — no static meta row seeds it.';

-- ── 2. Report what is discarded, then drop ──────────────────────────────────
-- Field definitions are admin configuration, so unlike the record tables this is
-- worth stating plainly: the two test dynamics and their sections go, and the ten
-- statics below are re-seeded under the new column names.

DO $$
DECLARE
    v_dyn  bigint;
    v_stat bigint;
    v_sec  bigint;
BEGIN
    SELECT count(*) FILTER (WHERE field_type = 'dynamic'),
           count(*) FILTER (WHERE field_type = 'static')
      INTO v_dyn, v_stat
      FROM wayleave.agreement_fields;
    SELECT count(*) INTO v_sec FROM wayleave.agreement_field_sections;

    RAISE NOTICE '034: discarding % dynamic field(s), % static field(s) and % section(s); statics are re-seeded below, dynamics are not',
        v_dyn, v_stat, v_sec;
END $$;

-- Drop children before parents: EAV and options reference fields; fields reference
-- sections, category and sub_category.
DROP TABLE wayleave.agreement_field_values_text_history;
DROP TABLE wayleave.agreement_field_values_int_history;
DROP TABLE wayleave.agreement_field_values_numeric_history;
DROP TABLE wayleave.agreement_field_values_date_history;
DROP TABLE wayleave.agreement_field_values_boolean_history;
DROP TABLE wayleave.agreement_field_values_text;
DROP TABLE wayleave.agreement_field_values_int;
DROP TABLE wayleave.agreement_field_values_numeric;
DROP TABLE wayleave.agreement_field_values_date;
DROP TABLE wayleave.agreement_field_values_boolean;
DROP TABLE wayleave.agreement_field_dropdown_options;
DROP TABLE wayleave.agreement_fields;
DROP TABLE wayleave.agreement_field_sub_category;
DROP TABLE wayleave.agreement_field_category;
DROP TABLE wayleave.agreement_field_sections;

-- ── 3. Meta tables ──────────────────────────────────────────────────────────

CREATE SEQUENCE wayleave.wayleave_field_sections_field_section_id_seq
    AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

CREATE TABLE wayleave.wayleave_field_sections (
    field_section_id            integer NOT NULL DEFAULT nextval('wayleave.wayleave_field_sections_field_section_id_seq'::regclass),
    field_section_name          varchar,
    field_section_display_order integer,
    field_section_ref           varchar,
    field_section_icon          varchar(50),
    CONSTRAINT wayleave_field_sections_pk PRIMARY KEY (field_section_id)
);
ALTER SEQUENCE wayleave.wayleave_field_sections_field_section_id_seq
    OWNED BY wayleave.wayleave_field_sections.field_section_id;

CREATE SEQUENCE wayleave.wayleave_field_category_field_category_id_seq
    AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

CREATE TABLE wayleave.wayleave_field_category (
    field_category_id            integer NOT NULL DEFAULT nextval('wayleave.wayleave_field_category_field_category_id_seq'::regclass),
    field_category_name          varchar NOT NULL,
    field_category_ref           varchar,
    field_section_id             integer,
    field_category_display_order integer,
    CONSTRAINT wayleave_field_category_pk PRIMARY KEY (field_category_id),
    CONSTRAINT wayleave_field_category_sections_fk FOREIGN KEY (field_section_id)
        REFERENCES wayleave.wayleave_field_sections(field_section_id)
);
ALTER SEQUENCE wayleave.wayleave_field_category_field_category_id_seq
    OWNED BY wayleave.wayleave_field_category.field_category_id;

-- Created even though the sub-category UI is hidden: fieldMetaModuleConfig() names it
-- and admin_save.php still carries its modes.
CREATE SEQUENCE wayleave.wayleave_field_sub_category_id_seq
    AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

CREATE TABLE wayleave.wayleave_field_sub_category (
    field_sub_category_id   integer NOT NULL DEFAULT nextval('wayleave.wayleave_field_sub_category_id_seq'::regclass),
    field_sub_category_name varchar NOT NULL,
    field_sub_category_ref  varchar,
    CONSTRAINT wayleave_field_sub_category_pk PRIMARY KEY (field_sub_category_id)
);
ALTER SEQUENCE wayleave.wayleave_field_sub_category_id_seq
    OWNED BY wayleave.wayleave_field_sub_category.field_sub_category_id;

CREATE SEQUENCE wayleave.wayleave_fields_field_id_seq
    START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

CREATE TABLE wayleave.wayleave_fields (
    field_id                     bigint  NOT NULL DEFAULT nextval('wayleave.wayleave_fields_field_id_seq'::regclass),
    field_section                integer,
    field_category               integer,
    field_sub_category           integer,
    field_display_order          integer,
    field_data_type              varchar,
    field_name                   varchar,
    field_input_type             varchar,
    field_form_id                varchar,
    field_required               boolean,
    field_active                 boolean DEFAULT true,
    field_spacer_after           integer,
    field_type                   varchar(10),
    field_autocomplete_type      varchar(30),
    field_autocomplete_helper_id varchar(100),
    field_options_source         varchar(50),
    CONSTRAINT wayleave_fields_pk PRIMARY KEY (field_id),
    CONSTRAINT wayleave_fields_sections_fk FOREIGN KEY (field_section)
        REFERENCES wayleave.wayleave_field_sections(field_section_id) ON UPDATE RESTRICT,
    CONSTRAINT wayleave_fields_category_fk FOREIGN KEY (field_category)
        REFERENCES wayleave.wayleave_field_category(field_category_id) ON UPDATE RESTRICT,
    CONSTRAINT wayleave_fields_sub_category_fk FOREIGN KEY (field_sub_category)
        REFERENCES wayleave.wayleave_field_sub_category(field_sub_category_id) ON UPDATE RESTRICT
);
ALTER SEQUENCE wayleave.wayleave_fields_field_id_seq
    OWNED BY wayleave.wayleave_fields.field_id;

-- Unique in both directions, as migration 009 established: a form id and a helper id
-- share one namespace, so neither can collide with the other.
CREATE UNIQUE INDEX wayleave_fields_field_form_id_uq
    ON wayleave.wayleave_fields (field_form_id);
CREATE UNIQUE INDEX wayleave_fields_helper_id_uq
    ON wayleave.wayleave_fields (field_autocomplete_helper_id)
 WHERE field_autocomplete_helper_id IS NOT NULL;

CREATE SEQUENCE wayleave.wayleave_field_dropdown_options_dropdown_id_seq
    START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

CREATE TABLE wayleave.wayleave_field_dropdown_options (
    dropdown_id            bigint  NOT NULL DEFAULT nextval('wayleave.wayleave_field_dropdown_options_dropdown_id_seq'::regclass),
    field_id               integer NOT NULL,
    dropdown_value         varchar,
    dropdown_name          varchar,
    dropdown_display_order integer NOT NULL DEFAULT 0,
    dropdown_active        boolean NOT NULL DEFAULT true,
    CONSTRAINT wayleave_field_dropdown_options_pk PRIMARY KEY (dropdown_id),
    CONSTRAINT wayleave_field_dropdown_options_fields_fk FOREIGN KEY (field_id)
        REFERENCES wayleave.wayleave_fields(field_id) ON UPDATE RESTRICT
);
ALTER SEQUENCE wayleave.wayleave_field_dropdown_options_dropdown_id_seq
    OWNED BY wayleave.wayleave_field_dropdown_options.dropdown_id;

-- ── 4. EAV value and history tables ─────────────────────────────────────────
-- Canonical shape: value_id / field_id / wayleave_id / value / record_datetime /
-- record_user, history adding history_action. The UNIQUE (field_id, wayleave_id) is
-- what wayleave_save.php's ON CONFLICT upsert targets — migration 029 exists because
-- opportunity shipped without it and every dynamic save failed at runtime.

-- Tagged $do$ rather than $$: the format() calls below use %1$s positional
-- specifiers, and a bare $$ block containing $s sequences is needlessly close to the
-- dollar-quote delimiter the parser is scanning for.
DO $do$
DECLARE
    spec text[][] := ARRAY[
        ARRAY['text',    'text'],
        ARRAY['int',     'bigint'],
        ARRAY['numeric', 'numeric'],
        ARRAY['date',    'date'],
        ARRAY['boolean', 'boolean']
    ];
    i    integer;
    nm   text;
    ty   text;
BEGIN
    FOR i IN 1 .. array_length(spec, 1) LOOP
        nm := spec[i][1];
        ty := spec[i][2];

        EXECUTE format(
            'CREATE SEQUENCE wayleave.wayleave_field_values_%s_value_id_seq
                 START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1', nm);

        EXECUTE format(
            'CREATE TABLE wayleave.wayleave_field_values_%1$s (
                 value_id        bigint NOT NULL DEFAULT nextval(''wayleave.wayleave_field_values_%1$s_value_id_seq''::regclass),
                 field_id        integer,
                 wayleave_id     integer,
                 value           %2$s,
                 record_datetime timestamp without time zone,
                 record_user     integer,
                 CONSTRAINT wayleave_field_values_%1$s_pk PRIMARY KEY (value_id),
                 CONSTRAINT wayleave_field_values_%1$s_field_id_wayleave_id_key UNIQUE (field_id, wayleave_id),
                 CONSTRAINT wayleave_field_values_%1$s_fields_fk FOREIGN KEY (field_id)
                     REFERENCES wayleave.wayleave_fields(field_id) ON UPDATE RESTRICT,
                 CONSTRAINT wayleave_field_values_%1$s_wayleaves_fk FOREIGN KEY (wayleave_id)
                     REFERENCES wayleave.wayleaves(wayleave_id) ON UPDATE RESTRICT
             )', nm, ty);

        EXECUTE format(
            'ALTER SEQUENCE wayleave.wayleave_field_values_%1$s_value_id_seq
                 OWNED BY wayleave.wayleave_field_values_%1$s.value_id', nm);

        EXECUTE format(
            'CREATE SEQUENCE wayleave.wayleave_field_values_%s_history_value_id_seq
                 START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1', nm);

        -- No FK to wayleaves on history: it must survive the deletion of what it
        -- describes, exactly as the projects and opportunity history tables do.
        EXECUTE format(
            'CREATE TABLE wayleave.wayleave_field_values_%1$s_history (
                 value_id        bigint NOT NULL DEFAULT nextval(''wayleave.wayleave_field_values_%1$s_history_value_id_seq''::regclass),
                 field_id        integer,
                 wayleave_id     integer,
                 value           %2$s,
                 record_datetime timestamp without time zone,
                 record_user     integer,
                 history_action  varchar(10) NOT NULL DEFAULT ''UPDATE'',
                 CONSTRAINT wayleave_field_values_%1$s_history_pk PRIMARY KEY (value_id)
             )', nm, ty);

        EXECUTE format(
            'ALTER SEQUENCE wayleave.wayleave_field_values_%1$s_history_value_id_seq
                 OWNED BY wayleave.wayleave_field_values_%1$s_history.value_id', nm);

        EXECUTE format(
            'CREATE INDEX wayleave_field_values_%1$s_wayleave_idx
                 ON wayleave.wayleave_field_values_%1$s (wayleave_id)', nm);
        EXECUTE format(
            'CREATE INDEX wayleave_field_values_%1$s_history_wayleave_idx
                 ON wayleave.wayleave_field_values_%1$s_history (wayleave_id)', nm);
    END LOOP;

    RAISE NOTICE '034: ten EAV value/history tables created';
END $do$;

-- ── 5. Static field seed ────────────────────────────────────────────────────
-- Order follows 015 so the form reads as it did, with company_id inserted after
-- account_id. The 6-column spacer sits on the last field so admin-created dynamic
-- fields start on a fresh row, as 012 and 027 do with theirs.

DO $$
DECLARE
    v_section  integer;
    v_category integer;
    v_type_fid integer;
    v_count    integer;
BEGIN
    INSERT INTO wayleave.wayleave_field_sections
        (field_section_name, field_section_ref, field_section_display_order, field_section_icon)
    VALUES ('Main Details', 'main_details', 0, 'fas fa-file-signature')
    RETURNING field_section_id INTO v_section;

    INSERT INTO wayleave.wayleave_field_category
        (field_category_name, field_category_ref, field_section_id, field_category_display_order)
    VALUES ('Main Details', 'main_details', v_section, 0)
    RETURNING field_category_id INTO v_category;

    INSERT INTO wayleave.wayleave_fields
        (field_section, field_category, field_display_order, field_data_type, field_name,
         field_input_type, field_form_id, field_required, field_active, field_type,
         field_autocomplete_type, field_autocomplete_helper_id, field_options_source,
         field_spacer_after)
    VALUES
        (v_section, v_category,  1, 'text', 'Wayleave Name',  'input',    'wayleave_name',
         true,  true, 'static', NULL, NULL, NULL, NULL),
        (v_section, v_category,  2, 'text', 'Reference',      'input',    'wayleave_reference',
         false, true, 'static', NULL, NULL, NULL, NULL),
        (v_section, v_category,  3, 'int',  'Status',         'dropdown', 'wayleave_status_id',
         false, true, 'static', NULL, NULL, 'wayleave_status', NULL),
        (v_section, v_category,  4, 'text', 'Type',           'dropdown', 'wayleave_type',
         false, true, 'static', NULL, NULL, NULL, NULL),
        (v_section, v_category,  5, 'text', 'Wayleave Team',  'dropdown', 'wayleave_team',
         false, true, 'static', NULL, NULL, 'wayleave_team', NULL),
        -- Column is wayleave_manager (the <entity>_manager pattern dashboardMineSql
        -- keys on); the label keeps the domain word.
        (v_section, v_category,  6, 'int',  'BD Manager',     'input',    'wayleave_manager',
         false, true, 'static', 'usernames', 'wayleave_manager_name', NULL, NULL),
        (v_section, v_category,  7, 'int',  'Account',        'input',    'account_id',
         false, true, 'static', 'accountname', 'account_name', NULL, NULL),
        -- New: never seeded by 015, so it has never rendered on the wayleave form.
        (v_section, v_category,  8, 'int',  'Assigned Supplier/Company', 'input', 'company_id',
         false, true, 'static', 'companyname', 'company_name', NULL, NULL),
        (v_section, v_category,  9, 'int',  'Parent Wayleave', 'input',   'parent_wayleave_id',
         false, true, 'static', 'wayleavename', 'parent_wayleave_name', NULL, NULL),
        -- input_type 'date', not 015's 'input' — both render a native picker, but
        -- 'input' shows as "input (legacy)" in the admin field modal.
        (v_section, v_category, 10, 'date', 'ECD Date',       'date',     'ecd_date',
         false, true, 'static', NULL, NULL, NULL, NULL),
        (v_section, v_category, 11, 'date', 'Signed Date',    'date',     'signed_date',
         false, true, 'static', NULL, NULL, NULL, 6);

    -- wayleave_type's option list. Static field, so its options are code-defined too
    -- (015 moved them here from a hardcoded JS array); admin-created option lists
    -- belong to dynamic fields and are not seeded by migrations.
    SELECT field_id INTO v_type_fid
      FROM wayleave.wayleave_fields WHERE field_form_id = 'wayleave_type';

    INSERT INTO wayleave.wayleave_field_dropdown_options
        (field_id, dropdown_value, dropdown_name, dropdown_display_order)
    SELECT v_type_fid, v.val, v.val, v.ord
      FROM (VALUES ('SDU', 1), ('MDU', 2), ('Estate', 3), ('Building', 4), ('Route', 5))
           AS v(val, ord);

    SELECT count(*) INTO v_count FROM wayleave.wayleave_fields;
    IF v_count <> 11 THEN
        RAISE EXCEPTION '034: seeded % static field(s), expected 11', v_count;
    END IF;

    RAISE NOTICE '034: 11 static fields seeded under section % / category %', v_section, v_category;
END $$;

-- ── 6. Verify ───────────────────────────────────────────────────────────────
-- The schema-wide sweep, valid at last: with the meta tables rebuilt rather than
-- renamed, nothing anywhere should still carry the agreement noun. This covers
-- tables, views, columns, sequences, constraints, indexes and functions — dropping a
-- table takes its constraints and OWNED BY sequences, but never its functions.

DO $$
DECLARE
    v_left text;
BEGIN
    SELECT string_agg(obj, ', ') INTO v_left FROM (
        SELECT 'table ' || table_name AS obj
          FROM information_schema.tables
         WHERE table_schema = 'wayleave' AND table_name LIKE '%agreement%'
        UNION ALL
        SELECT 'column ' || table_name || '.' || column_name
          FROM information_schema.columns
         WHERE table_schema = 'wayleave' AND column_name LIKE '%agreement%'
        UNION ALL
        SELECT 'relation ' || c.relname
          FROM pg_class c
         WHERE c.relnamespace = 'wayleave'::regnamespace
           AND c.relname LIKE '%agreement%'
        UNION ALL
        SELECT 'constraint ' || conname
          FROM pg_constraint
         WHERE connamespace = 'wayleave'::regnamespace AND conname LIKE '%agreement%'
        UNION ALL
        SELECT 'function ' || p.proname
          FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
         WHERE n.nspname = 'wayleave' AND p.proname LIKE '%agreement%'
    ) x;

    IF v_left IS NOT NULL THEN
        RAISE EXCEPTION '034: agreement-named object(s) remain: %', v_left;
    END IF;

    IF (SELECT count(*) FROM information_schema.columns
         WHERE table_schema = 'wayleave' AND table_name = 'wayleaves'
           AND column_name = 'stocklist_id') <> 1 THEN
        RAISE EXCEPTION '034: wayleaves.stocklist_id was not added';
    END IF;

    RAISE NOTICE '034: field meta rebuilt, statics seeded, nothing named agreement remains';
END $$;
