-- 009_field_meta_autocomplete.sql
-- Static fields → metadata-driven rendering, item 1
-- (docs/2026-07-15-static-fields-to-meta.md)
--
-- Autocomplete/options metadata on all four *_fields meta tables:
--   field_autocomplete_type      one of the data-autocomplete lookup types
--                                (usernames, companyname, accountname, projectname,
--                                stocklistname, wayleavename, landregtitle).
--                                NULL = not an autocomplete field.
--   field_autocomplete_helper_id form id of the visible label input. Explicit, not a
--                                naming convention — existing pairs aren't uniform
--                                (project_manager → project_manager_name, but
--                                account_id → account_name).
--   field_options_source         registered options key (e.g. project_status) that a
--                                PHP-side whitelist in fn/get_update_form.php maps to a
--                                (table, value_col, label_col) query. Never raw SQL.
--                                Why not *_field_dropdown_options rows: status IDs and
--                                descriptions are consumed OUTSIDE the edit form — the
--                                list views (vw_projects_list, vw_stocklists_list) and
--                                audit queries join the *_status tables, and the entity
--                                tables store the integer status id. Copying the options
--                                into the dropdown-options table would create a second
--                                source of truth that drifts on any rename. This key
--                                keeps the editor reading the same canonical table the
--                                views join; admin management of statuses (future) then
--                                edits that canonical table and everything stays in step.
--
-- Unique index on field_form_id per meta table: the renderers key the DOM on
-- field_form_id, and the meta migration plan depends on one live definition per
-- field. Pre-check for existing collisions (returned no rows on dev, 2026-07-16):
--
--   SELECT 'projects', field_form_id, count(*) FROM projects.project_fields
--     GROUP BY 2 HAVING count(*) > 1
--   UNION ALL SELECT 'accounts', field_form_id, count(*) FROM accounts.account_fields
--     GROUP BY 2 HAVING count(*) > 1
--   UNION ALL SELECT 'stocklists', field_form_id, count(*) FROM stocklists.stocklist_fields
--     GROUP BY 2 HAVING count(*) > 1
--   UNION ALL SELECT 'wayleave', field_form_id, count(*) FROM wayleave.agreement_fields
--     GROUP BY 2 HAVING count(*) > 1;
--
-- If this migration fails on one of the indexes, run the pre-check and resolve the
-- duplicate rows manually (deactivate/rename the loser), then re-run.

ALTER TABLE projects.project_fields
    ADD COLUMN field_autocomplete_type      character varying(30),
    ADD COLUMN field_autocomplete_helper_id character varying(100),
    ADD COLUMN field_options_source         character varying(50);

ALTER TABLE accounts.account_fields
    ADD COLUMN field_autocomplete_type      character varying(30),
    ADD COLUMN field_autocomplete_helper_id character varying(100),
    ADD COLUMN field_options_source         character varying(50);

ALTER TABLE stocklists.stocklist_fields
    ADD COLUMN field_autocomplete_type      character varying(30),
    ADD COLUMN field_autocomplete_helper_id character varying(100),
    ADD COLUMN field_options_source         character varying(50);

ALTER TABLE wayleave.agreement_fields
    ADD COLUMN field_autocomplete_type      character varying(30),
    ADD COLUMN field_autocomplete_helper_id character varying(100),
    ADD COLUMN field_options_source         character varying(50);

CREATE UNIQUE INDEX project_fields_field_form_id_uq
    ON projects.project_fields (field_form_id);
CREATE UNIQUE INDEX account_fields_field_form_id_uq
    ON accounts.account_fields (field_form_id);
CREATE UNIQUE INDEX stocklist_fields_field_form_id_uq
    ON stocklists.stocklist_fields (field_form_id);
CREATE UNIQUE INDEX agreement_fields_field_form_id_uq
    ON wayleave.agreement_fields (field_form_id);

-- stocklists.vw_stocklists: append parent_stocklist_name (new self-join pp) —
-- label source for the meta plan's Parent Stocklist acceptance test. Definition
-- restated from sql/netplanner_schema_20260706.sql unchanged apart from the
-- appended column (CREATE OR REPLACE VIEW only permits appending).
CREATE OR REPLACE VIEW stocklists.vw_stocklists AS
 SELECT p.stocklist_id,
    p.parent_stocklist_id,
    p.stocklist_name,
    p.stocklist_status_id,
    p.stocklist_manager,
    p.created_datetime,
    p.created_user,
    p.modified_datetime,
    p.modified_user,
    u.username AS stocklist_manager_name,
    p.company_id,
    c.company_name,
    p.account_id,
    a.account_name,
    COALESCE(pp.stocklist_name, ''::character varying) AS parent_stocklist_name
   FROM ((((stocklists.stocklists p
     LEFT JOIN users.users u ON ((p.stocklist_manager = u.id)))
     LEFT JOIN users.companies c ON ((p.company_id = c.company_id)))
     LEFT JOIN accounts.accounts a ON ((p.account_id = a.account_id)))
     LEFT JOIN stocklists.stocklists pp ON ((p.parent_stocklist_id = pp.stocklist_id)));
