-- 022_status_table_styling.sql
-- Status tables: primary key, display order, active flag and style token
-- (docs/2026-08-03-admin-status-management.md, Phase 1)
--
-- SCOPE IS PROJECTS, ACCOUNTS AND STOCKLISTS ONLY.
-- wayleave.agreement_status is deliberately NOT touched — that module is pending
-- a rewrite and will be aligned as a separate task. It already carries its own
-- display_order / is_active under different names; leave them alone.
--
-- The three tables in scope were two nullable columns each, with no primary key,
-- no ordering and no way to retire a status. Everything the admin Statuses tab
-- needs is missing, so this adds it:
--
--   <table>_display_order  integer  admin-set ordering (editor dropdowns, lists)
--   <table>_active         boolean  false retires a status from new selections
--                                   while records already holding it keep resolving
--   <table>_style          varchar  style TOKEN KEY, never a colour
--
-- Style stores a key from statusStyleRegistry() in global_functions.php rather
-- than a colour value. Two reasons: a scheme repaint then propagates everywhere
-- for free, and no stored value can ever be an inaccessible colour — the set of
-- available styles is GeoLynx-controlled in code, not client-controlled here.
-- NULL means "unstyled", and every consumer falls back to exactly its current
-- appearance, so applying this migration changes nothing visually.
--
-- The primary keys are safe to add: audited on dev 2026-08-03 and all three
-- tables are clean (projects 9 rows, accounts 5, stocklists 2 — no null ids, no
-- duplicate ids). Adding one is what stops the admin UI editing or deleting the
-- wrong row when two statuses share an id.

DO $$
DECLARE
    t   text;
    tbl text;
BEGIN
    FOREACH t IN ARRAY ARRAY[
        'projects.project_status',
        'accounts.account_status',
        'stocklists.stocklist_status'
    ] LOOP
        -- Bare table name doubles as the column prefix: projects.project_status
        -- has project_status_id / project_status_desc, so the new columns follow
        -- as project_status_display_order and so on.
        tbl := split_part(t, '.', 2);

        EXECUTE format('ALTER TABLE %s ALTER COLUMN %I SET NOT NULL', t, tbl || '_id');

        IF NOT EXISTS (
            SELECT 1 FROM pg_constraint
             WHERE conrelid = t::regclass AND contype = 'p'
        ) THEN
            EXECUTE format('ALTER TABLE %s ADD CONSTRAINT %I PRIMARY KEY (%I)',
                           t, tbl || '_pkey', tbl || '_id');
        END IF;

        EXECUTE format('ALTER TABLE %s ADD COLUMN IF NOT EXISTS %I integer NOT NULL DEFAULT 0',
                       t, tbl || '_display_order');
        EXECUTE format('ALTER TABLE %s ADD COLUMN IF NOT EXISTS %I boolean NOT NULL DEFAULT true',
                       t, tbl || '_active');
        EXECUTE format('ALTER TABLE %s ADD COLUMN IF NOT EXISTS %I character varying(32)',
                       t, tbl || '_style');

        -- Seed the order from the existing ids, so today's implicit ordering is
        -- what admin sees on first open. row_number() is 1-based, so nothing
        -- lands on 0 and re-running will not renumber.
        EXECUTE format($f$
            UPDATE %1$s s
               SET %2$I = n.rn
              FROM (SELECT %3$I AS id, row_number() OVER (ORDER BY %3$I) AS rn FROM %1$s) n
             WHERE s.%3$I = n.id
               AND s.%2$I = 0
        $f$, t, tbl || '_display_order', tbl || '_id');

        EXECUTE format('COMMENT ON COLUMN %s.%I IS %L', t, tbl || '_display_order',
            'Admin-set ordering for editor dropdowns and list filters.');
        EXECUTE format('COMMENT ON COLUMN %s.%I IS %L', t, tbl || '_active',
            'False retires the status from new selections; records already holding it keep resolving their label.');
        EXECUTE format('COMMENT ON COLUMN %s.%I IS %L', t, tbl || '_style',
            'Style token key from statusStyleRegistry() in global_functions.php — NOT a colour. NULL means unstyled and consumers fall back to their existing default.');
    END LOOP;
END $$;
