-- 013_account_static_field_seed.sql
-- Static fields → metadata-driven rendering, item 8, accounts module
-- (docs/2026-07-15-static-fields-to-meta.md)
--
-- Seed the five Main Details static fields of the account editor as
-- field_type='static' meta rows; the same change deletes the hardcoded block
-- from html_body_account_edit_v2.php. Same shape as db/012 (projects) with one
-- extra step: accounts already has a static 'account_name' meta row from early
-- experimentation (field_id 4 on dev) — it is normalised into slot 1 rather
-- than inserted.
--
-- Display order: existing Main Details fields shift +5, statics take slots 1–5
-- in today's visual order (name, manager, company, parent, status). Status gets
-- field_options_source='account_status' and a 9-column spacer (one field on its
-- visual row) 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 accounts.account_field_sections
    WHERE field_section_ref = 'main_details';

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

    SELECT field_category_id INTO v_category
    FROM accounts.account_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 'accounts: 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 accounts.account_fields
    WHERE (field_form_id IN ('account_name', 'account_manager', 'company_id',
                             'parent_account_id', 'account_status_id',
                             'account_manager_name', 'company_name', 'parent_account_name')
        OR field_autocomplete_helper_id IN ('account_manager_name', 'company_name',
                                            'parent_account_name'))
      AND field_type IS DISTINCT FROM 'static';

    IF v_claimed IS NOT NULL THEN
        RAISE EXCEPTION 'accounts: 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 five statics
    UPDATE accounts.account_fields
    SET field_display_order = COALESCE(field_display_order, 0) + 5
    WHERE field_category = v_category;

    -- account_name: normalise the pre-existing static row into slot 1 …
    UPDATE accounts.account_fields
    SET field_section = v_section, field_category = v_category, field_display_order = 1,
        field_name = 'Account Name', field_data_type = 'text', field_input_type = 'input',
        field_active = true
    WHERE field_form_id = 'account_name' AND field_type = 'static';

    -- … or insert it fresh where no such row exists
    INSERT INTO accounts.account_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', 'Account Name', 'input', 'account_name',
           false, true, 'static', NULL, NULL, NULL, NULL
    WHERE NOT EXISTS (SELECT 1 FROM accounts.account_fields WHERE field_form_id = 'account_name');

    INSERT INTO accounts.account_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', 'Account Manager', 'input', 'account_manager',
           false, true, 'static', 'usernames', 'account_manager_name', NULL, NULL
    WHERE NOT EXISTS (SELECT 1 FROM accounts.account_fields WHERE field_form_id = 'account_manager');

    INSERT INTO accounts.account_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 accounts.account_fields WHERE field_form_id = 'company_id');

    INSERT INTO accounts.account_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 Account', 'input', 'parent_account_id',
           false, true, 'static', 'accountname', 'parent_account_name', NULL, NULL
    WHERE NOT EXISTS (SELECT 1 FROM accounts.account_fields WHERE field_form_id = 'parent_account_id');

    INSERT INTO accounts.account_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 Status', 'dropdown', 'account_status_id',
           true, true, 'static', NULL, NULL, 'account_status', 9
    WHERE NOT EXISTS (SELECT 1 FROM accounts.account_fields WHERE field_form_id = 'account_status_id');
END $$;
