-- 010_stocklist_parent_field_seed.sql
-- Static fields → metadata-driven rendering, item 7: acceptance test
-- (docs/2026-07-15-static-fields-to-meta.md)
--
-- Enable the Parent Stocklist field through metadata alone — zero template
-- changes. The field has full backend support (parent_stocklist_id is in
-- stocklist_save.php's $staticFields whitelist and in the load view; migration
-- 009 added its label to vw_stocklists) but its HTML is commented out in
-- html_body_stocklistedit_v2.php, so today it cannot be set from the UI at all.
-- If this row makes the field render, autocomplete, save and reload, the whole
-- meta pipeline works.
--
-- Section/category are looked up by ref/order, not hardcoded id — they are data
-- and differ between environments. Fails loudly on any collision or missing
-- lookup; resolve manually rather than editing this migration.

DO $$
DECLARE
    v_section  integer;
    v_category integer;
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;

    -- First category of the Main Details section (the editor renders each
    -- field into its category''s container inside the section panel).
    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 field', v_section;
    END IF;

    IF EXISTS (
        SELECT 1 FROM stocklists.stocklist_fields
        WHERE field_form_id IN ('parent_stocklist_id', 'parent_stocklist_name')
           OR field_autocomplete_helper_id IN ('parent_stocklist_id', 'parent_stocklist_name')
    ) THEN
        RAISE EXCEPTION 'stocklists: a field already claims parent_stocklist_id / parent_stocklist_name — resolve the collision manually';
    END IF;

    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)
    VALUES
        (v_section, v_category, 0,
         'int', 'Parent Stocklist', 'input', 'parent_stocklist_id',
         false, true, 'static',
         'stocklistname', 'parent_stocklist_name');
END $$;
