-- 023_default_created_status.sql
-- A "Created" status every record starts in, so nothing is ever statusless
-- (docs/2026-08-03-admin-status-management.md — follow-up, Dave 2026-08-04)
--
-- SCOPE IS PROJECTS, ACCOUNTS AND STOCKLISTS ONLY.
-- wayleave.agreements is deliberately NOT touched — that module is pending a
-- rewrite. Adding it later is one more entry in the array below.
--
-- WHY. The status columns are nullable with no default, so a record created by
-- any path that does not set one has no status at all. Those records are
-- invisible to anything that groups by status: on the dashboard they count
-- toward the module's headline figure but appear in no segment of the stage bar,
-- which is why the two numbers disagree. Giving them a real status they can be
-- reported in fixes that at source rather than teaching each consumer to cope
-- with NULL.
--
-- WHAT THIS DOES, per module:
--   1. Adds a 'Created' status, first in display order.
--   2. Backfills every NULL status column to it — WITH TRIGGERS DISABLED, see below.
--   3. Sets it as the column DEFAULT, so future inserts get it for free.
--   4. Makes the column NOT NULL, so the hole cannot reopen.
--
-- Steps 3 and 4 need no application change: project_create_boundary.php,
-- stocklist_create.php and the account create path all OMIT the status column
-- rather than passing NULL explicitly, so the default applies to all of them.
--
-- NOT id 0, deliberately. Migration 022 added display_order precisely so that
-- ordering stops being implied by id — and the cost of the old coupling is
-- already on the books (022 backfilled order from id, which put Cancelled and
-- Not Viable ahead of Prospecting). Picking id 0 to make 'Created' sort first
-- would re-introduce exactly that. Ordering is display_order's job; the id is
-- just identity. Zero is also falsy in both PHP and JavaScript, so every
-- `if (status_id)` in the codebase becomes a place where a real status can read
-- as "no status" — a whole class of bug bought for no benefit.
--
-- TRIGGERS ARE DISABLED FOR THE BACKFILL, and that is a deliberate choice, not a
-- shortcut. Two triggers fire on UPDATE of these tables:
--
--   update_modified_fields_trigger  stamps modified_datetime / modified_user
--   trg_*_history                   writes a row to the *_history audit table
--
-- Left enabled, the backfill would claim that every one of these records was
-- modified today and that its status changed today. Neither is true: the status
-- did not change, we are recording a state the record was always in but had no
-- way to express. It would also reshuffle every list that sorts by
-- modified_datetime (the project, account and stocklist lists all do), floating
-- old untouched records to the top as though someone had just edited them.
--
-- Re-runnable: the insert is guarded, the backfill only touches NULLs, and the
-- default / NOT NULL steps are idempotent.

DO $$
DECLARE
    spec       text[];
    entity     text;   -- e.g. projects.projects
    statustbl  text;   -- e.g. projects.project_status
    prefix     text;   -- e.g. project_status
    statuscol  text;   -- e.g. project_status_id
    created_id integer;
BEGIN
    FOREACH spec SLICE 1 IN ARRAY ARRAY[
        ARRAY['projects.projects',     'projects.project_status'],
        ARRAY['accounts.accounts',     'accounts.account_status'],
        ARRAY['stocklists.stocklists', 'stocklists.stocklist_status']
    ] LOOP
        entity    := spec[1];
        statustbl := spec[2];
        prefix    := split_part(statustbl, '.', 2);   -- doubles as the column prefix
        statuscol := prefix || '_id';

        -- 1. The status row. Ids are allocated in steps of 5 to match how
        --    admin_save.php hands out new ones. display_order 0 puts it first
        --    without having to renumber every existing row; the next reorder in
        --    admin normalises the whole list to 1..n anyway.
        --
        --    The guard is HAVING, not WHERE, and that matters: an aggregate with
        --    no GROUP BY returns one row even when WHERE matches nothing, so a
        --    WHERE guard would still fire — MAX() over the empty set is NULL,
        --    COALESCE makes it 0, and a second 'Created' lands with id 5 on every
        --    re-run. HAVING filters the aggregated row, which is the intent.
        EXECUTE format(
            'INSERT INTO %s (%I, %I, %I, %I, %I)
             SELECT COALESCE(MAX(%I), 0) + 5, ''Created'', 0, true, NULL FROM %s
             HAVING NOT EXISTS (SELECT 1 FROM %s WHERE lower(%I) = ''created'')',
            statustbl, statuscol, prefix || '_desc', prefix || '_display_order',
            prefix || '_active', prefix || '_style',
            statuscol, statustbl, statustbl, prefix || '_desc');

        EXECUTE format('SELECT %I FROM %s WHERE lower(%I) = ''created''',
                       statuscol, statustbl, prefix || '_desc')
           INTO created_id;

        IF created_id IS NULL THEN
            RAISE EXCEPTION 'Could not resolve the Created status for %', statustbl;
        END IF;

        -- 2. Backfill, silently. See the trigger note in the header — this is
        --    recording a state that was always true, not a change of state.
        EXECUTE format('ALTER TABLE %s DISABLE TRIGGER USER', entity);
        EXECUTE format('UPDATE %s SET %I = %s WHERE %I IS NULL',
                       entity, statuscol, created_id, statuscol);
        EXECUTE format('ALTER TABLE %s ENABLE TRIGGER USER', entity);

        -- 3. Future inserts get it without any endpoint having to know.
        EXECUTE format('ALTER TABLE %s ALTER COLUMN %I SET DEFAULT %s',
                       entity, statuscol, created_id);

        -- 4. And the hole cannot reopen.
        EXECUTE format('ALTER TABLE %s ALTER COLUMN %I SET NOT NULL', entity, statuscol);
    END LOOP;
END $$;

COMMENT ON COLUMN projects.projects.project_status_id IS
    'Never null. Defaults to the "Created" status (migration 023) so every record is reportable by status from the moment it exists.';
COMMENT ON COLUMN accounts.accounts.account_status_id IS
    'Never null. Defaults to the "Created" status (migration 023) so every record is reportable by status from the moment it exists.';
COMMENT ON COLUMN stocklists.stocklists.stocklist_status_id IS
    'Never null. Defaults to the "Created" status (migration 023) so every record is reportable by status from the moment it exists.';
