-- sql/wayleave_03_dynamic_fields.sql
-- Wayleave module — Phase 0 step 3 of 4.
-- Dynamic fields + value tables mirroring the projects.project_fields pattern.
-- Prereq: sql/wayleave_01_core.sql has been run.
-- Run: psql -U postgres -d netplanner -f sql/wayleave_03_dynamic_fields.sql

BEGIN;

-- Field section/category/sub_category lookups
CREATE TABLE IF NOT EXISTS wayleave.agreement_field_sections (
    id            serial PRIMARY KEY,
    section_name  varchar(150) NOT NULL UNIQUE,
    display_order integer NOT NULL DEFAULT 0,
    is_active     boolean NOT NULL DEFAULT true
);

CREATE TABLE IF NOT EXISTS wayleave.agreement_field_category (
    id            serial PRIMARY KEY,
    section_id    integer REFERENCES wayleave.agreement_field_sections(id),
    category_name varchar(150) NOT NULL,
    display_order integer NOT NULL DEFAULT 0,
    is_active     boolean NOT NULL DEFAULT true,
    UNIQUE (section_id, category_name)
);

CREATE TABLE IF NOT EXISTS wayleave.agreement_field_sub_category (
    id                serial PRIMARY KEY,
    category_id       integer REFERENCES wayleave.agreement_field_category(id),
    sub_category_name varchar(150) NOT NULL,
    display_order     integer NOT NULL DEFAULT 0,
    is_active         boolean NOT NULL DEFAULT true,
    UNIQUE (category_id, sub_category_name)
);

-- Field definitions
CREATE TABLE IF NOT EXISTS wayleave.agreement_fields (
    field_id            serial PRIMARY KEY,
    field_section       integer REFERENCES wayleave.agreement_field_sections(id),
    field_category      integer REFERENCES wayleave.agreement_field_category(id),
    field_sub_category  integer REFERENCES wayleave.agreement_field_sub_category(id),
    field_display_order integer NOT NULL DEFAULT 0,
    field_data_type     varchar(20) NOT NULL,        -- 'text' | 'int' | 'numeric' | 'date' | 'boolean'
    field_name          varchar(200) NOT NULL,       -- human label
    field_form_id       varchar(100) NOT NULL UNIQUE,-- DOM id / POST key
    field_input_type    varchar(30) NOT NULL,        -- 'text' | 'textarea' | 'select' | 'date' | 'checkbox' | 'number'
    field_required      boolean NOT NULL DEFAULT false,
    field_active        boolean NOT NULL DEFAULT true,
    field_spacer_after  boolean NOT NULL DEFAULT false,
    field_type          varchar(20) NOT NULL DEFAULT 'dynamic'  -- 'dynamic' | 'static'
);
CREATE INDEX IF NOT EXISTS idx_agr_fields_form_id ON wayleave.agreement_fields(field_form_id);

-- Dropdown options for select-style fields
CREATE TABLE IF NOT EXISTS wayleave.agreement_field_dropdown_options (
    id            serial PRIMARY KEY,
    field_id      integer NOT NULL REFERENCES wayleave.agreement_fields(field_id) ON DELETE CASCADE,
    option_value  varchar(250) NOT NULL,
    option_label  varchar(250) NOT NULL,
    display_order integer NOT NULL DEFAULT 0,
    is_active     boolean NOT NULL DEFAULT true
);
CREATE INDEX IF NOT EXISTS idx_agr_field_opts ON wayleave.agreement_field_dropdown_options(field_id) WHERE is_active = true;

-- Value tables (one per data type)
CREATE TABLE IF NOT EXISTS wayleave.agreement_field_values_text (
    agreement_id      integer NOT NULL REFERENCES wayleave.agreements(agreement_id) ON DELETE CASCADE,
    field_id          integer NOT NULL REFERENCES wayleave.agreement_fields(field_id) ON DELETE CASCADE,
    field_value       text,
    modified_datetime timestamp NOT NULL DEFAULT now(),
    modified_user     integer NOT NULL,
    PRIMARY KEY (agreement_id, field_id)
);

CREATE TABLE IF NOT EXISTS wayleave.agreement_field_values_int (
    agreement_id      integer NOT NULL REFERENCES wayleave.agreements(agreement_id) ON DELETE CASCADE,
    field_id          integer NOT NULL REFERENCES wayleave.agreement_fields(field_id) ON DELETE CASCADE,
    field_value       bigint,
    modified_datetime timestamp NOT NULL DEFAULT now(),
    modified_user     integer NOT NULL,
    PRIMARY KEY (agreement_id, field_id)
);

CREATE TABLE IF NOT EXISTS wayleave.agreement_field_values_numeric (
    agreement_id      integer NOT NULL REFERENCES wayleave.agreements(agreement_id) ON DELETE CASCADE,
    field_id          integer NOT NULL REFERENCES wayleave.agreement_fields(field_id) ON DELETE CASCADE,
    field_value       numeric(20,6),
    modified_datetime timestamp NOT NULL DEFAULT now(),
    modified_user     integer NOT NULL,
    PRIMARY KEY (agreement_id, field_id)
);

CREATE TABLE IF NOT EXISTS wayleave.agreement_field_values_date (
    agreement_id      integer NOT NULL REFERENCES wayleave.agreements(agreement_id) ON DELETE CASCADE,
    field_id          integer NOT NULL REFERENCES wayleave.agreement_fields(field_id) ON DELETE CASCADE,
    field_value       date,
    modified_datetime timestamp NOT NULL DEFAULT now(),
    modified_user     integer NOT NULL,
    PRIMARY KEY (agreement_id, field_id)
);

CREATE TABLE IF NOT EXISTS wayleave.agreement_field_values_boolean (
    agreement_id      integer NOT NULL REFERENCES wayleave.agreements(agreement_id) ON DELETE CASCADE,
    field_id          integer NOT NULL REFERENCES wayleave.agreement_fields(field_id) ON DELETE CASCADE,
    field_value       boolean,
    modified_datetime timestamp NOT NULL DEFAULT now(),
    modified_user     integer NOT NULL,
    PRIMARY KEY (agreement_id, field_id)
);

-- History mirrors (one per type)
CREATE TABLE IF NOT EXISTS wayleave.agreement_field_values_text_history (
    history_id       bigserial PRIMARY KEY,
    agreement_id     integer NOT NULL,
    field_id         integer NOT NULL,
    field_value      text,
    history_action   varchar(20) NOT NULL,
    history_datetime timestamp NOT NULL DEFAULT now(),
    history_user     integer NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_agr_fvt_hist ON wayleave.agreement_field_values_text_history(agreement_id);

CREATE TABLE IF NOT EXISTS wayleave.agreement_field_values_int_history (
    history_id       bigserial PRIMARY KEY,
    agreement_id     integer NOT NULL,
    field_id         integer NOT NULL,
    field_value      bigint,
    history_action   varchar(20) NOT NULL,
    history_datetime timestamp NOT NULL DEFAULT now(),
    history_user     integer NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_agr_fvi_hist ON wayleave.agreement_field_values_int_history(agreement_id);

CREATE TABLE IF NOT EXISTS wayleave.agreement_field_values_numeric_history (
    history_id       bigserial PRIMARY KEY,
    agreement_id     integer NOT NULL,
    field_id         integer NOT NULL,
    field_value      numeric(20,6),
    history_action   varchar(20) NOT NULL,
    history_datetime timestamp NOT NULL DEFAULT now(),
    history_user     integer NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_agr_fvn_hist ON wayleave.agreement_field_values_numeric_history(agreement_id);

CREATE TABLE IF NOT EXISTS wayleave.agreement_field_values_date_history (
    history_id       bigserial PRIMARY KEY,
    agreement_id     integer NOT NULL,
    field_id         integer NOT NULL,
    field_value      date,
    history_action   varchar(20) NOT NULL,
    history_datetime timestamp NOT NULL DEFAULT now(),
    history_user     integer NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_agr_fvd_hist ON wayleave.agreement_field_values_date_history(agreement_id);

CREATE TABLE IF NOT EXISTS wayleave.agreement_field_values_boolean_history (
    history_id       bigserial PRIMARY KEY,
    agreement_id     integer NOT NULL,
    field_id         integer NOT NULL,
    field_value      boolean,
    history_action   varchar(20) NOT NULL,
    history_datetime timestamp NOT NULL DEFAULT now(),
    history_user     integer NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_agr_fvb_hist ON wayleave.agreement_field_values_boolean_history(agreement_id);

-- Seed default sections/categories and a few starter dynamic fields so the editor has content.
INSERT INTO wayleave.agreement_field_sections (section_name, display_order) VALUES
    ('Commercial', 10),
    ('Legal',      20),
    ('Delivery',   30)
ON CONFLICT (section_name) DO NOTHING;

INSERT INTO wayleave.agreement_field_category (section_id, category_name, display_order)
SELECT id, 'General', 10 FROM wayleave.agreement_field_sections
ON CONFLICT DO NOTHING;

INSERT INTO wayleave.agreement_fields
    (field_section, field_category, field_display_order, field_data_type, field_name, field_form_id, field_input_type, field_required, field_active)
VALUES
    ((SELECT id FROM wayleave.agreement_field_sections WHERE section_name='Commercial'),
     (SELECT c.id FROM wayleave.agreement_field_category c
         JOIN wayleave.agreement_field_sections s ON s.id=c.section_id
         WHERE s.section_name='Commercial' AND c.category_name='General'),
     10, 'numeric', 'Annual Fee (GBP)', 'wl_annual_fee', 'number', false, true),
    ((SELECT id FROM wayleave.agreement_field_sections WHERE section_name='Legal'),
     (SELECT c.id FROM wayleave.agreement_field_category c
         JOIN wayleave.agreement_field_sections s ON s.id=c.section_id
         WHERE s.section_name='Legal' AND c.category_name='General'),
     10, 'text', 'Landowner Solicitor', 'wl_landowner_solicitor', 'text', false, true),
    ((SELECT id FROM wayleave.agreement_field_sections WHERE section_name='Delivery'),
     (SELECT c.id FROM wayleave.agreement_field_category c
         JOIN wayleave.agreement_field_sections s ON s.id=c.section_id
         WHERE s.section_name='Delivery' AND c.category_name='General'),
     10, 'date', 'Access Agreement Date', 'wl_access_date', 'date', false, true)
ON CONFLICT (field_form_id) DO NOTHING;

COMMIT;

-- Verify:
--   SELECT field_id, field_form_id, field_data_type, field_name FROM wayleave.agreement_fields ORDER BY field_id;
-- Expected: 3 seed rows (wl_annual_fee, wl_landowner_solicitor, wl_access_date).
