-- 015_wayleave_static_field_seed.sql
-- Static fields → metadata-driven rendering, item 8, wayleave module
-- (docs/2026-07-15-static-fields-to-meta.md)
--
-- Seed the ten Main Details static fields of the wayleave agreement editor as
-- field_type='static' meta rows; the same change replaces the JS-variable
-- rendering (WL.autocompleteField and friends in wayleave_edit_v2.js) with the
-- shared meta pipeline. Storage is untouched: wayleave_save.php's
-- $staticColumns allow-list keeps routing these to wayleave.agreements columns.
--
-- Unlike the other modules, wayleave statics render on the fixed Main tab
-- (#wl-main-static-fields), not in a section panel — the section/category here
-- exist for admin organisation and are created if missing (wayleave may have no
-- 'Main Details' section yet; its sections were all dynamic).
--
-- agreement_type's options were a hardcoded JS list — they become ordinary
-- dropdown-options rows. agreement_status_id / wayleave_team keep their
-- canonical tables via field_options_source ('agreement_status' /
-- 'wayleave_team', both registered in fn/get_update_form.php).
--
-- Fails loudly if a non-static field claims any target form/helper id.

DO $$
DECLARE
    v_section  integer;
    v_category integer;
    v_type_fid integer;
    v_claimed  text;
BEGIN
    SELECT field_section_id INTO v_section
    FROM wayleave.agreement_field_sections
    WHERE field_section_ref = 'main_details';

    IF v_section IS NULL THEN
        INSERT INTO wayleave.agreement_field_sections
            (field_section_name, field_section_ref, field_section_display_order)
        VALUES ('Main Details', 'main_details', 0)
        RETURNING field_section_id INTO v_section;
    END IF;

    SELECT field_category_id INTO v_category
    FROM wayleave.agreement_field_category
    WHERE field_section_id = v_section
    ORDER BY field_category_display_order, field_category_id
    LIMIT 1;

    IF v_category IS NULL THEN
        INSERT INTO wayleave.agreement_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;
    END IF;

    SELECT string_agg(field_form_id, ', ') INTO v_claimed
    FROM wayleave.agreement_fields
    WHERE (field_form_id IN ('agreement_name', 'agreement_reference', 'agreement_status_id',
                             'agreement_type', 'wayleave_team', 'bd_manager', 'account_id',
                             'parent_agreement_id', 'ecd_date', 'signed_date',
                             'bd_manager_name', 'account_name', 'parent_agreement_name')
        OR field_autocomplete_helper_id IN ('bd_manager_name', 'account_name',
                                            'parent_agreement_name'))
      AND field_type IS DISTINCT FROM 'static';

    IF v_claimed IS NOT NULL THEN
        RAISE EXCEPTION 'wayleave: non-static field(s) already claim: % — resolve the collision manually', v_claimed;
    END IF;

    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, field_type,
         field_autocomplete_type, field_autocomplete_helper_id, field_options_source)
    SELECT v_section, v_category, 1, 'text', 'Agreement Name', 'agreement_name', 'input',
           false, true, 'static', NULL, NULL, NULL
    WHERE NOT EXISTS (SELECT 1 FROM wayleave.agreement_fields WHERE field_form_id = 'agreement_name');

    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, field_type,
         field_autocomplete_type, field_autocomplete_helper_id, field_options_source)
    SELECT v_section, v_category, 2, 'text', 'Reference', 'agreement_reference', 'input',
           false, true, 'static', NULL, NULL, NULL
    WHERE NOT EXISTS (SELECT 1 FROM wayleave.agreement_fields WHERE field_form_id = 'agreement_reference');

    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, field_type,
         field_autocomplete_type, field_autocomplete_helper_id, field_options_source)
    SELECT v_section, v_category, 3, 'int', 'Status', 'agreement_status_id', 'dropdown',
           false, true, 'static', NULL, NULL, 'agreement_status'
    WHERE NOT EXISTS (SELECT 1 FROM wayleave.agreement_fields WHERE field_form_id = 'agreement_status_id');

    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, field_type,
         field_autocomplete_type, field_autocomplete_helper_id, field_options_source)
    SELECT v_section, v_category, 4, 'text', 'Type', 'agreement_type', 'dropdown',
           false, true, 'static', NULL, NULL, NULL
    WHERE NOT EXISTS (SELECT 1 FROM wayleave.agreement_fields WHERE field_form_id = 'agreement_type');

    -- agreement_type's option list (was a hardcoded array in wayleave_edit_v2.js)
    SELECT field_id INTO v_type_fid
    FROM wayleave.agreement_fields WHERE field_form_id = 'agreement_type';

    INSERT INTO wayleave.agreement_field_dropdown_options (field_id, dropdown_value, dropdown_name)
    SELECT v_type_fid, v.val, v.val
    FROM (VALUES ('SDU'), ('MDU'), ('Estate'), ('Building'), ('Route')) AS v(val)
    WHERE NOT EXISTS (
        SELECT 1 FROM wayleave.agreement_field_dropdown_options
        WHERE field_id = v_type_fid AND dropdown_value = v.val
    );

    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, field_type,
         field_autocomplete_type, field_autocomplete_helper_id, field_options_source)
    SELECT v_section, v_category, 5, 'text', 'Wayleave Team', 'wayleave_team', 'dropdown',
           false, true, 'static', NULL, NULL, 'wayleave_team'
    WHERE NOT EXISTS (SELECT 1 FROM wayleave.agreement_fields WHERE field_form_id = 'wayleave_team');

    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, field_type,
         field_autocomplete_type, field_autocomplete_helper_id, field_options_source)
    SELECT v_section, v_category, 6, 'int', 'BD Manager', 'bd_manager', 'input',
           false, true, 'static', 'usernames', 'bd_manager_name', NULL
    WHERE NOT EXISTS (SELECT 1 FROM wayleave.agreement_fields WHERE field_form_id = 'bd_manager');

    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, field_type,
         field_autocomplete_type, field_autocomplete_helper_id, field_options_source)
    SELECT v_section, v_category, 7, 'int', 'Account', 'account_id', 'input',
           false, true, 'static', 'accountname', 'account_name', NULL
    WHERE NOT EXISTS (SELECT 1 FROM wayleave.agreement_fields WHERE field_form_id = 'account_id');

    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, field_type,
         field_autocomplete_type, field_autocomplete_helper_id, field_options_source)
    SELECT v_section, v_category, 8, 'int', 'Parent Agreement', 'parent_agreement_id', 'input',
           false, true, 'static', 'wayleavename', 'parent_agreement_name', NULL
    WHERE NOT EXISTS (SELECT 1 FROM wayleave.agreement_fields WHERE field_form_id = 'parent_agreement_id');

    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, field_type,
         field_autocomplete_type, field_autocomplete_helper_id, field_options_source)
    SELECT v_section, v_category, 9, 'date', 'ECD Date', 'ecd_date', 'input',
           false, true, 'static', NULL, NULL, NULL
    WHERE NOT EXISTS (SELECT 1 FROM wayleave.agreement_fields WHERE field_form_id = 'ecd_date');

    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, field_type,
         field_autocomplete_type, field_autocomplete_helper_id, field_options_source)
    SELECT v_section, v_category, 10, 'date', 'Signed Date', 'signed_date', 'input',
           false, true, 'static', NULL, NULL, NULL
    WHERE NOT EXISTS (SELECT 1 FROM wayleave.agreement_fields WHERE field_form_id = 'signed_date');
END $$;
