-- 031_wayleave_module_key_and_status.sql
-- Wayleave module realignment, migration 1 of 6
-- (docs/2026-08-10-wayleave-module-realignment.md, Phase 1)
--
-- Two things, both prerequisites for everything after: the module key goes plural,
-- and wayleave.agreement_status is replaced by a wayleave_status table with the shape
-- migrations 022 and 023 gave the other modules.
--
-- THIS TAKES WAYLEAVE ACCESS DOWN until the Phase 7 endpoints deploy. routes.php
-- still gates on 'wayleave' and this renames the key to 'wayleaves', so every wayleave
-- route refuses until the code catches up. Deliberate — see the plan doc's delivery
-- sequence — but it means 031-036 and the endpoint deploy want the same sitting.
--
-- STATUSES ARE COPIED, NOT RETYPED. The new table is populated by SELECT from the old
-- one, so whatever a client has configured survives with its order and active flags
-- intact. The rebuild in 032 discards the RECORDS; it does not discard the status
-- configuration, which is admin's work rather than test data.
--
-- WHY THE SEQUENCE GOES. wayleave.agreement_status_id_seq has no counterpart in any
-- other module: projects, accounts, stocklists and opportunity all allocate status ids
-- through statusSave()'s MAX(id) + 5. Keeping a sequence as well means two allocators
-- that will eventually hand out the same id.
--
-- WHY THE STYLE COLUMN STARTS NULL. wayleave_status_style holds a KEY from
-- statusStyleRegistry(), never a colour. NULL means unstyled and every consumer falls
-- back to exactly its current appearance, so applying this changes nothing visually.
--
-- users.user_item_permissions.item_type is deliberately NOT touched. Item types are
-- singular across the app ('project', 'account', 'stocklist', 'opportunity',
-- 'wayleave') while module keys are plural — the two are different namespaces and
-- getItemPermission() uses the singular. Renaming it here would break record-level
-- permissions, not align them.
--
-- The old agreement_status table is left in place for 032 to drop, after it has
-- dropped the views and the agreements table that depend on it.

-- ── 0. Guards ───────────────────────────────────────────────────────────────

DO $$
BEGIN
    IF to_regclass('wayleave.agreement_status') IS NULL THEN
        RAISE EXCEPTION 'wayleave.agreement_status is missing — 031 has already run, or the schema is not what this migration expects';
    END IF;

    IF to_regclass('wayleave.wayleave_status') IS NOT NULL THEN
        RAISE EXCEPTION 'wayleave.wayleave_status already exists — 031 has already run';
    END IF;

    -- A duplicate or null id would silently collapse rows on the copy below and make
    -- the primary key impossible. 022 audited the other three tables the same way.
    IF EXISTS (SELECT 1 FROM wayleave.agreement_status
                GROUP BY id HAVING count(*) > 1 OR id IS NULL) THEN
        RAISE EXCEPTION 'wayleave.agreement_status has duplicate or null ids — resolve before running';
    END IF;
END $$;

-- ── 1. Module key: wayleave → wayleaves ─────────────────────────────────────
-- Permission rows are DATA. A rename that quietly matches nothing removes access
-- from everyone who had it, and nothing in the app would say so — hence the
-- assertions rather than bare UPDATEs.

DO $$
DECLARE
    v_modules integer;
    v_roles   integer;
    v_other   integer;
BEGIN
    UPDATE public.app_modules SET module_key = 'wayleaves' WHERE module_key = 'wayleave';
    GET DIAGNOSTICS v_modules = ROW_COUNT;

    IF v_modules <> 1 THEN
        RAISE EXCEPTION 'public.app_modules: expected exactly 1 wayleave row, updated %', v_modules;
    END IF;

    UPDATE users.role_permissions SET module = 'wayleaves' WHERE module = 'wayleave';
    GET DIAGNOSTICS v_roles = ROW_COUNT;
    RAISE NOTICE '031: % role_permissions row(s) re-keyed to wayleaves', v_roles;

    IF v_roles = 0 THEN
        RAISE WARNING '031: no role_permissions rows carried module = wayleave. If any role was meant to have wayleave access, it does not have it now — check before continuing.';
    END IF;

    -- The three dashboard config tables are keyed by module string too. Wayleave has
    -- no rows today (it is not yet in dashboardModuleMap()), but a client could have
    -- seeded some by hand and they would be orphaned by the rename.
    v_other := 0;
    UPDATE public.dashboard_config           SET module = 'wayleaves' WHERE module = 'wayleave';
    GET DIAGNOSTICS v_modules = ROW_COUNT;  v_other := v_other + v_modules;
    UPDATE public.dashboard_inactive_statuses SET module = 'wayleaves' WHERE module = 'wayleave';
    GET DIAGNOSTICS v_modules = ROW_COUNT;  v_other := v_other + v_modules;
    UPDATE public.dashboard_flag_config      SET module = 'wayleaves' WHERE module = 'wayleave';
    GET DIAGNOSTICS v_modules = ROW_COUNT;  v_other := v_other + v_modules;
    RAISE NOTICE '031: % dashboard config row(s) re-keyed', v_other;

    IF EXISTS (SELECT 1 FROM public.app_modules      WHERE module_key = 'wayleave')
       OR EXISTS (SELECT 1 FROM users.role_permissions WHERE module = 'wayleave') THEN
        RAISE EXCEPTION '031: rows still carry the old wayleave module key';
    END IF;
END $$;

-- ── 2. wayleave.wayleave_status ─────────────────────────────────────────────
-- The migration 022 shape. Bare table name doubles as the column prefix, exactly as
-- projects.project_status has project_status_id / project_status_desc.

CREATE TABLE wayleave.wayleave_status (
    wayleave_status_id            integer      NOT NULL,
    wayleave_status_desc          varchar(100) NOT NULL,
    wayleave_status_display_order integer      NOT NULL DEFAULT 0,
    wayleave_status_active        boolean      NOT NULL DEFAULT true,
    wayleave_status_style         varchar(50),
    CONSTRAINT wayleave_status_pk PRIMARY KEY (wayleave_status_id)
);

COMMENT ON COLUMN wayleave.wayleave_status.wayleave_status_style IS
    'Style TOKEN KEY from statusStyleRegistry() in global_functions.php, never a colour. NULL means unstyled.';

-- Copy what is configured. No sequence is created: statusSave() allocates
-- MAX(id) + 5, as it does for every other module.
INSERT INTO wayleave.wayleave_status
    (wayleave_status_id, wayleave_status_desc,
     wayleave_status_display_order, wayleave_status_active, wayleave_status_style)
SELECT id, description, display_order, is_active, NULL
  FROM wayleave.agreement_status;

-- ── 3. The Created status ───────────────────────────────────────────────────
-- Migration 023's rule: every record has a status from the moment it exists, because
-- the entity column defaults to this one. 032 sets that default. The status therefore
-- cannot be deleted (the default would point at a missing row and the next insert
-- fails the foreign key) or deactivated (every new record would read "(retired)"),
-- and statusDefaultId() enforces both by reading the default from the catalog.
--
-- Reused if a row already says Created, so re-running against a database that has one
-- does not produce a second.

DO $$
DECLARE
    v_id integer;
BEGIN
    SELECT wayleave_status_id INTO v_id
      FROM wayleave.wayleave_status WHERE wayleave_status_desc = 'Created';

    IF v_id IS NULL THEN
        SELECT coalesce(max(wayleave_status_id), 0) + 5 INTO v_id
          FROM wayleave.wayleave_status;

        INSERT INTO wayleave.wayleave_status
            (wayleave_status_id, wayleave_status_desc,
             wayleave_status_display_order, wayleave_status_active, wayleave_status_style)
        VALUES (v_id, 'Created', 0, true, NULL);

        RAISE NOTICE '031: Created status added with id %', v_id;
    ELSE
        -- It must be selectable, or every new record lands on a retired status.
        UPDATE wayleave.wayleave_status
           SET wayleave_status_active = true
         WHERE wayleave_status_id = v_id AND wayleave_status_active = false;

        RAISE NOTICE '031: existing Created status reused, id %', v_id;
    END IF;
END $$;

-- ── 4. Verify ───────────────────────────────────────────────────────────────

DO $$
DECLARE
    v_old integer;
    v_new integer;
BEGIN
    SELECT count(*) INTO v_old FROM wayleave.agreement_status;
    SELECT count(*) INTO v_new FROM wayleave.wayleave_status;

    -- New table holds every old row, plus Created if it was not already among them.
    IF v_new < v_old THEN
        RAISE EXCEPTION '031: wayleave_status has % rows, agreement_status had % — rows were lost', v_new, v_old;
    END IF;

    IF NOT EXISTS (SELECT 1 FROM wayleave.wayleave_status
                    WHERE wayleave_status_desc = 'Created' AND wayleave_status_active) THEN
        RAISE EXCEPTION '031: no active Created status — 032 cannot set the column default';
    END IF;

    RAISE NOTICE '031: wayleave_status ready with % row(s); agreement_status is dropped by 032', v_new;
END $$;
