-- 027_opportunity_static_field_seed.sql
-- Opportunity module alignment, migration 4 of 5
-- (docs/2026-08-04-opportunity-module-alignment.md)
--
-- The Main Details section, its category, and the six static fields — the direct
-- counterpart of migration 012 for projects, seeded with the same six fields in the
-- same order: name, account, manager, company, parent, status.
--
-- Requires 026 (the meta tables exist).
--
-- DEPLOY THE PHP FIRST. parent_opportunity_id carries field_autocomplete_type =
-- 'opportunityname', a type that did not exist before this work. It is registered in
-- five places — fn/autocomplete.php, resolveAutocompleteLabel() and
-- allowedAutocompleteTypes() in global_functions.php / admin_save.php, the binder in
-- js/main.js, and the admin field modal's option list. If this migration is applied
-- before those deploy, the parent field renders an autocomplete that silently returns
-- nothing and admin_save.php rejects any edit to the field as an unknown type.
--
-- ALL SIX ARE field_type = 'static', which means presentation lives here but storage
-- does not: each is a real column on opportunity.opportunities, whitelisted in
-- opportunity_save.php. Admin lists them read-only with a System badge and
-- admin_save.php refuses to edit, toggle or delete them.
--
-- stocklist_id IS DELIBERATELY NOT SEEDED, matching projects — the column exists on
-- the entity table but has no form field, so it stays off the editor. Same for
-- cover_image_url and geom, which have their own controls rather than form rows.
--
-- opportunity_status_id gets field_options_source = 'opportunity_status' rather than a
-- dropdown-options list, so its options render from the canonical status table — one
-- source of truth shared with the list view, the dashboard stage bar and the editor
-- header badge. It is the only required field of the six, and carries a 6-column
-- spacer so admin-created dynamic fields start on a fresh row.
--
-- The category is named 'Details' rather than 'Main Details': the section name is
-- already the tab label, and the category name renders as an <h6> heading inside it.
-- Both are admin-editable afterwards.

DO $$
DECLARE
    v_section  integer;
    v_category integer;
    v_claimed  text;
BEGIN
    IF to_regclass('opportunity.opportunity_fields') IS NULL THEN
        RAISE EXCEPTION 'opportunity.opportunity_fields not found — apply 026 first';
    END IF;

    -- Registered autocomplete types are a PHP concern, so this cannot be checked in
    -- SQL. Stated here because the failure it guards against is silent.
    RAISE NOTICE 'Seeding parent_opportunity_id with autocomplete type ''opportunityname'' — confirm the PHP registering that type has deployed.';

    -- ── Section ──────────────────────────────────────────────────────────────
    SELECT field_section_id INTO v_section
      FROM opportunity.opportunity_field_sections
     WHERE field_section_ref = 'main_details';

    IF v_section IS NULL THEN
        INSERT INTO opportunity.opportunity_field_sections
            (field_section_name, field_section_display_order, field_section_ref, field_section_icon)
        VALUES
            ('Main Details', 1, 'main_details', 'fas fa-info-circle')
        RETURNING field_section_id INTO v_section;
    END IF;

    -- ── Category ─────────────────────────────────────────────────────────────
    SELECT field_category_id INTO v_category
      FROM opportunity.opportunity_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 opportunity.opportunity_field_category
            (field_category_name, field_category_ref, field_section_id, field_category_display_order)
        VALUES
            ('Details', 'details', v_section, 1)
        RETURNING field_category_id INTO v_category;
    END IF;

    -- ── Collision guard ──────────────────────────────────────────────────────
    -- The module has no fields yet, so this should find nothing. Kept because it is
    -- cheap and because it makes a re-run after a partial apply safe: a form id or
    -- helper id claimed by an admin-created dynamic field must be resolved by hand,
    -- not silently worked around.
    SELECT string_agg(field_form_id, ', ') INTO v_claimed
      FROM opportunity.opportunity_fields
     WHERE (field_form_id IN ('opportunity_name', 'account_id', 'opportunity_manager',
                              'company_id', 'parent_opportunity_id', 'opportunity_status_id',
                              'account_name', 'opportunity_manager_name', 'company_name',
                              'parent_opportunity_name')
         OR field_autocomplete_helper_id IN ('account_name', 'opportunity_manager_name',
                                             'company_name', 'parent_opportunity_name'))
       AND field_type IS DISTINCT FROM 'static';

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

    -- ── The six static fields ────────────────────────────────────────────────

    INSERT INTO opportunity.opportunity_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', 'Opportunity Name', 'input', 'opportunity_name',
           false, true, 'static', NULL, NULL, NULL, NULL
    WHERE NOT EXISTS (SELECT 1 FROM opportunity.opportunity_fields WHERE field_form_id = 'opportunity_name');

    INSERT INTO opportunity.opportunity_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 opportunity.opportunity_fields WHERE field_form_id = 'account_id');

    INSERT INTO opportunity.opportunity_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', 'Opportunity Manager', 'input', 'opportunity_manager',
           false, true, 'static', 'usernames', 'opportunity_manager_name', NULL, NULL
    WHERE NOT EXISTS (SELECT 1 FROM opportunity.opportunity_fields WHERE field_form_id = 'opportunity_manager');

    INSERT INTO opportunity.opportunity_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 opportunity.opportunity_fields WHERE field_form_id = 'company_id');

    INSERT INTO opportunity.opportunity_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 Opportunity', 'input', 'parent_opportunity_id',
           false, true, 'static', 'opportunityname', 'parent_opportunity_name', NULL, NULL
    WHERE NOT EXISTS (SELECT 1 FROM opportunity.opportunity_fields WHERE field_form_id = 'parent_opportunity_id');

    INSERT INTO opportunity.opportunity_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', 'Opportunity Status', 'dropdown', 'opportunity_status_id',
           true, true, 'static', NULL, NULL, 'opportunity_status', 6
    WHERE NOT EXISTS (SELECT 1 FROM opportunity.opportunity_fields WHERE field_form_id = 'opportunity_status_id');

    IF (SELECT count(*) FROM opportunity.opportunity_fields WHERE field_type = 'static') <> 6 THEN
        RAISE EXCEPTION 'opportunity: expected 6 static fields after seeding, found %',
            (SELECT count(*) FROM opportunity.opportunity_fields WHERE field_type = 'static');
    END IF;
END $$;
