-- 012_project_static_field_seed.sql
-- Static fields → metadata-driven rendering, item 8, projects module
-- (docs/2026-07-15-static-fields-to-meta.md)
--
-- Seed the six Main Details static fields of the project editor as
-- field_type='static' meta rows. The same change (same commit) deletes the
-- hardcoded HTML block from html_body_projectedit.php — one live definition per
-- field at any moment; if this migration runs before the template deploy, the
-- editors' DOM-presence check suppresses the meta rendering, so the window is
-- safe in either order.
--
-- Section/category are looked up by ref/order (they are data, ids differ
-- between environments). Display order: existing Main Details fields are
-- shifted +6 so the statics take slots 1–6, preserving today's visual order
-- (name, account, manager, company, parent, status). project_status_id gets
-- field_options_source='project_status' (options render from the canonical
-- status table) and a 6-column spacer so dynamic fields start on a fresh row,
-- as they do today.
--
-- Fails loudly if any target form id or helper id is already claimed by a
-- non-static field (admin-created dynamic field) — resolve manually, then
-- re-run. Pre-checked clean on dev 2026-07-17.

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

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

    SELECT field_category_id INTO v_category
    FROM projects.project_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 'projects: 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 projects.project_fields
    WHERE (field_form_id IN ('project_name', 'account_id', 'project_manager', 'company_id',
                             'parent_project_id', 'project_status_id',
                             'account_name', 'project_manager_name', 'company_name',
                             'parent_project_name')
        OR field_autocomplete_helper_id IN ('account_name', 'project_manager_name',
                                            'company_name', 'parent_project_name'))
      AND field_type IS DISTINCT FROM 'static';

    IF v_claimed IS NOT NULL THEN
        RAISE EXCEPTION 'projects: 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 projects.project_fields
    SET field_display_order = COALESCE(field_display_order, 0) + 6
    WHERE field_category = v_category;

    INSERT INTO projects.project_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', 'Project Name', 'input', 'project_name',
           false, true, 'static', NULL, NULL, NULL, NULL
    WHERE NOT EXISTS (SELECT 1 FROM projects.project_fields WHERE field_form_id = 'project_name');

    INSERT INTO projects.project_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', 'input', 'account_id',
           false, true, 'static', 'accountname', 'account_name', NULL, NULL
    WHERE NOT EXISTS (SELECT 1 FROM projects.project_fields WHERE field_form_id = 'account_id');

    INSERT INTO projects.project_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', 'Project Manager', 'input', 'project_manager',
           false, true, 'static', 'usernames', 'project_manager_name', NULL, NULL
    WHERE NOT EXISTS (SELECT 1 FROM projects.project_fields WHERE field_form_id = 'project_manager');

    INSERT INTO projects.project_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', 'Assigned Supplier/Company', 'input', 'company_id',
           false, true, 'static', 'companyname', 'company_name', NULL, NULL
    WHERE NOT EXISTS (SELECT 1 FROM projects.project_fields WHERE field_form_id = 'company_id');

    INSERT INTO projects.project_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', 'Parent Project', 'input', 'parent_project_id',
           false, true, 'static', 'projectname', 'parent_project_name', NULL, NULL
    WHERE NOT EXISTS (SELECT 1 FROM projects.project_fields WHERE field_form_id = 'parent_project_id');

    INSERT INTO projects.project_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', 'Project Status', 'dropdown', 'project_status_id',
           true, true, 'static', NULL, NULL, 'project_status', 6
    WHERE NOT EXISTS (SELECT 1 FROM projects.project_fields WHERE field_form_id = 'project_status_id');
END $$;
