-- 021_field_options_and_icons.sql
-- Admin field-meta management: dropdown option ordering/state, section icon
-- normalisation, input type normalisation
-- (docs/2026-08-02-admin-field-meta-management.md, Phase 0)
--
-- Three unrelated-looking changes that share one cause: the admin fields page is
-- gaining the controls to manage this metadata, and each of these columns is
-- currently either missing or holding a format the new controls cannot produce.
--
-- 1. DROPDOWN OPTIONS — the four *_field_dropdown_options tables carry only
--    (dropdown_id, field_id, dropdown_value, dropdown_name). There is no order
--    and no active flag, so options cannot be reordered or retired. Migration
--    011 deliberately DROPped both from wayleave to match the projects shape;
--    this adds them back to all four under the prefixed naming convention used
--    by field_section_display_order / field_category_display_order.
--
--    dropdown_name is also dead data in three of the four modules:
--    get_update_form.php serves the row as-is and the editors render
--    `dropdown_label || dropdown_value`, and dropdown_label is only ever set for
--    options-source rows and for wayleave (which aliases dropdown_name to it).
--    So today the stored VALUE is what the user sees, and renaming an option
--    would rewrite the identity of every value already stored against it. The
--    backfill gives the other three modules a real label column so value and
--    label can diverge safely from here on.
--
-- 2. SECTION ICONS — field_section_icon holds full markup (<i class="..."></i>)
--    because the three editors inject it raw into a template literal, while the
--    admin table formatter builds <i class="{value}"> and expects a bare class
--    string. Only one can be right. Bare class string wins: it is what the
--    25-icon picker produces and what keeps arbitrary markup out of the column.
--    COUPLED CHANGE — project_edit_v2.js, account_edit_v2.js and
--    stocklist_edit.js must wrap the value in an <i> tag in the same deploy, or
--    every section tab renders its class string as visible text.
--
-- 3. INPUT TYPES — field_input_type becomes required and validated against the
--    valid-combination map in global_functions.php. 'text' is not in that map
--    and never rendered: the project/account/stocklist editors handle only
--    'dropdown' and 'input' and drop everything else on the floor, so the one
--    row holding it (projects' `test` field) has been invisible on the form
--    since it was created. Normalised to 'input'.
--
-- Audited on the dev database 2026-08-02 before writing. No NULL or empty
-- field_input_type anywhere. Distinct values found:
--   accounts    dropdown(1) input(8)
--   stocklists  dropdown(1) input(8) inputarea(1)
--   wayleave    date(1) dropdown(3) input(7) number(1) text(1)
-- Two non-conforming values, both remapped below. Note both rows have been
-- invisible on their forms up to now (the projects-family editors drop anything
-- outside dropdown/input), so both become visible as a result of this work —
-- 'text' immediately, 'inputarea' at commit 2. Wayleave's 'text' row is the
-- exception: its editor already falls through to a text input, so nothing
-- changes there visually.


-- ── 1. Dropdown options: display order + active flag ─────────────────────────
DO $$
DECLARE
    t    text;
    tbl  text;
BEGIN
    FOREACH t IN ARRAY ARRAY[
        'projects.project_field_dropdown_options',
        'accounts.account_field_dropdown_options',
        'stocklists.stocklist_field_dropdown_options',
        'wayleave.agreement_field_dropdown_options'
    ] LOOP
        tbl := replace(t, '.', '_');

        EXECUTE format(
            'ALTER TABLE %s ADD COLUMN IF NOT EXISTS dropdown_display_order integer NOT NULL DEFAULT 0', t);
        EXECUTE format(
            'ALTER TABLE %s ADD COLUMN IF NOT EXISTS dropdown_active boolean NOT NULL DEFAULT true', t);

        -- Preserve the order the editors currently render in. get_update_form.php
        -- orders by column 1 (dropdown_id), so numbering by dropdown_id within
        -- each field reproduces exactly what users see today.
        EXECUTE format($f$
            UPDATE %s o
               SET dropdown_display_order = n.rn
              FROM (SELECT dropdown_id,
                           row_number() OVER (PARTITION BY field_id ORDER BY dropdown_id) AS rn
                      FROM %s) n
             WHERE o.dropdown_id = n.dropdown_id
               AND o.dropdown_display_order = 0
        $f$, t, t);

        -- Give the three older modules a usable label. Wayleave's dropdown_name
        -- values came from option_label (migration 011) and are already real
        -- labels, so they are left alone by the NULL/blank guard.
        EXECUTE format(
            'UPDATE %s SET dropdown_name = dropdown_value
              WHERE dropdown_name IS NULL OR btrim(dropdown_name) = %L', t, '');

        EXECUTE format(
            'CREATE INDEX IF NOT EXISTS %I ON %s (field_id, dropdown_display_order)',
            tbl || '_field_order_idx', t);

        EXECUTE format(
            'COMMENT ON COLUMN %s.dropdown_display_order IS %L', t,
            'Order options appear in the editor select. Managed from the admin fields page.');
        EXECUTE format(
            'COMMENT ON COLUMN %s.dropdown_active IS %L', t,
            'False retires an option from new selections while records already holding its value keep resolving their label.');
        EXECUTE format(
            'COMMENT ON COLUMN %s.dropdown_name IS %L', t,
            'Display label. Distinct from dropdown_value, which is the stored identity and must not change once records hold it.');
    END LOOP;
END $$;


-- ── 2. Section icons: strip markup down to the class string ──────────────────
-- Handles both quote styles. Rows already holding a bare class string do not
-- match the '<' guard and are left untouched, so this is safe to re-run.
DO $$
DECLARE
    t text;
BEGIN
    FOREACH t IN ARRAY ARRAY[
        'projects.project_field_sections',
        'accounts.account_field_sections',
        'stocklists.stocklist_field_sections',
        'wayleave.agreement_field_sections'
    ] LOOP
        EXECUTE format($f$
            UPDATE %s
               SET field_section_icon = btrim(COALESCE(
                       substring(field_section_icon from 'class="([^"]*)"'),
                       substring(field_section_icon from 'class=''([^'']*)''')
                   ))
             WHERE field_section_icon LIKE '%%<%%'
               AND COALESCE(
                       substring(field_section_icon from 'class="([^"]*)"'),
                       substring(field_section_icon from 'class=''([^'']*)''')
                   ) IS NOT NULL
        $f$, t);

        EXECUTE format(
            'COMMENT ON COLUMN %s.field_section_icon IS %L', t,
            'Bare FontAwesome class string (e.g. "fas fa-map-marked-alt") — NOT markup. Editors wrap it in an <i> tag at render time.');
    END LOOP;
END $$;


-- ── 3. Input types: normalise 'text' to 'input' ──────────────────────────────
DO $$
DECLARE
    t text;
BEGIN
    FOREACH t IN ARRAY ARRAY[
        'projects.project_fields',
        'accounts.account_fields',
        'stocklists.stocklist_fields',
        'wayleave.agreement_fields'
    ] LOOP
        EXECUTE format(
            'UPDATE %s SET field_input_type = %L WHERE field_input_type = %L',
            t, 'input', 'text');

        -- 'inputarea' (one stocklists row) reads as a typo blending 'input' and
        -- 'textarea'. Like 'text' it is not rendered by the projects-family
        -- editors, so that field has been invisible on the stocklist form since
        -- it was created. Mapped to textarea on the reading that a multi-line box
        -- was intended — it becomes visible in commit 2, when the editors learn
        -- to render textarea. Change to 'input' here if a single-line box was
        -- meant, in which case it becomes visible as soon as this migration runs.
        EXECUTE format(
            'UPDATE %s SET field_input_type = %L WHERE field_input_type = %L',
            t, 'textarea', 'inputarea');
    END LOOP;
END $$;
