-- 014_stocklist_static_field_seed.sql
-- Static fields → metadata-driven rendering, item 8, stocklists module
-- (docs/2026-07-15-static-fields-to-meta.md)
--
-- Seed the remaining five Main Details static fields of the stocklist editor;
-- the same change deletes the hardcoded block from
-- html_body_stocklistedit_v2.php. parent_stocklist_id is already live from the
-- acceptance-test seed (db/010, display order 0) — it is normalised into slot 4
-- here, restoring the template's intended position (it sat, commented out,
-- between company and account).
--
-- Display order: existing Main Details fields shift +6, statics take slots 1–6
-- (name, manager, company, parent, account, status). Status gets
-- field_options_source='stocklist_status' and a 6-column spacer so dynamic
-- fields start on a fresh row.
--
-- Fails loudly if a non-static field claims any target form/helper id.

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

    IF v_section IS NULL THEN
        RAISE EXCEPTION 'stocklists: no field section with ref ''main_details'' — check stocklist_field_sections';
    END IF;

    SELECT field_category_id INTO v_category
    FROM stocklists.stocklist_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
        RAISE EXCEPTION 'stocklists: main_details section (id %) has no categories — a category is required to place the fields', v_section;
    END IF;

    SELECT string_agg(field_form_id, ', ') INTO v_claimed
    FROM stocklists.stocklist_fields
    WHERE (field_form_id IN ('stocklist_name', 'stocklist_manager', 'company_id',
                             'parent_stocklist_id', 'account_id', 'stocklist_status_id',
                             'stocklist_manager_name', 'company_name', 'account_name',
                             'parent_stocklist_name')
        OR field_autocomplete_helper_id IN ('stocklist_manager_name', 'company_name',
                                            'account_name', 'parent_stocklist_name'))
      AND field_type IS DISTINCT FROM 'static';

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

    -- Make room at the front of the Main Details category for the six statics
    UPDATE stocklists.stocklist_fields
    SET field_display_order = COALESCE(field_display_order, 0) + 6
    WHERE field_category = v_category;

    INSERT INTO stocklists.stocklist_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)
    SELECT v_section, v_category, 1, 'text', 'Stocklist Name', 'input', 'stocklist_name',
           false, true, 'static', NULL, NULL, NULL, NULL
    WHERE NOT EXISTS (SELECT 1 FROM stocklists.stocklist_fields WHERE field_form_id = 'stocklist_name');

    INSERT INTO stocklists.stocklist_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)
    SELECT v_section, v_category, 2, 'int', 'Stocklist Manager', 'input', 'stocklist_manager',
           false, true, 'static', 'usernames', 'stocklist_manager_name', NULL, NULL
    WHERE NOT EXISTS (SELECT 1 FROM stocklists.stocklist_fields WHERE field_form_id = 'stocklist_manager');

    INSERT INTO stocklists.stocklist_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)
    SELECT v_section, v_category, 3, 'int', 'Assigned Supplier/Company', 'input', 'company_id',
           false, true, 'static', 'companyname', 'company_name', NULL, NULL
    WHERE NOT EXISTS (SELECT 1 FROM stocklists.stocklist_fields WHERE field_form_id = 'company_id');

    -- parent_stocklist_id: normalise the db/010 acceptance-seed row into slot 4
    -- (the +6 shift above moved it; this pins its intended position)
    UPDATE stocklists.stocklist_fields
    SET field_section = v_section, field_category = v_category, field_display_order = 4
    WHERE field_form_id = 'parent_stocklist_id' AND field_type = 'static';

    INSERT INTO stocklists.stocklist_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)
    SELECT v_section, v_category, 4, 'int', 'Parent Stocklist', 'input', 'parent_stocklist_id',
           false, true, 'static', 'stocklistname', 'parent_stocklist_name', NULL, NULL
    WHERE NOT EXISTS (SELECT 1 FROM stocklists.stocklist_fields WHERE field_form_id = 'parent_stocklist_id');

    INSERT INTO stocklists.stocklist_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)
    SELECT v_section, v_category, 5, 'int', 'Account', 'input', 'account_id',
           false, true, 'static', 'accountname', 'account_name', NULL, NULL
    WHERE NOT EXISTS (SELECT 1 FROM stocklists.stocklist_fields WHERE field_form_id = 'account_id');

    INSERT INTO stocklists.stocklist_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)
    SELECT v_section, v_category, 6, 'int', 'Stocklist Status', 'dropdown', 'stocklist_status_id',
           true, true, 'static', NULL, NULL, 'stocklist_status', 6
    WHERE NOT EXISTS (SELECT 1 FROM stocklists.stocklist_fields WHERE field_form_id = 'stocklist_status_id');
END $$;
