-- sql/util_delete_dynamic_field.sql
--
-- DEV / TEST UTILITY — permanently deletes one dynamic field and everything
-- ever stored against it, including its audit history.
--
-- ============================================================================
-- THIS IS NOT A MIGRATION. Do not move it into db/ — migrate.php globs db/*.sql
-- and would apply it as one, against every database, once.
--
-- THIS IS NOT REVERSIBLE. It erases history rows, which is exactly what the
-- history tables exist to prevent. It is for cleaning up test fields on a dev
-- or test server. On production, deactivate the field in admin instead: that
-- removes it from the editors while leaving the record of what was captured.
-- ============================================================================
--
-- Admin already refuses to delete a field that has values stored against it,
-- and that guard is right — this is the deliberate override for a field that
-- was only ever a test.
--
-- WHY THE ORDER MATTERS, AND WHY IT IS LIVE-THEN-HISTORY.
--
-- Eleven tables per module carry a foreign key to <prefix>_fields(field_id):
-- five value tables, their five history tables, and the dropdown options. None
-- declare ON DELETE CASCADE, so the field row cannot go until every child has.
--
-- The live value tables are deleted FIRST, and that is not arbitrary. Migration
-- 018 put an AFTER INSERT OR DELETE OR UPDATE trigger on all fifteen EAV value
-- tables for projects / accounts / stocklists, and it writes a history row on
-- DELETE too (history_action = 'DELETE'). So clearing history first and live
-- second re-creates the history rows behind you, and the final delete of the
-- field row then fails on the foreign key from the history table.
--
-- Deleting the live rows first means the triggers fire while there is still
-- something to record, and the history sweep that follows is final — nothing can
-- write to those tables once the live rows are gone.
--
-- (Wayleave has no such triggers; migration 018 covers the other three modules
-- and wayleave_save.php writes its history in PHP. The same order is correct
-- there anyway, so the loop does not need to care which module it is on.)
--
-- Static (system) fields are refused. Their storage is a real column on the
-- entity table and their form id is whitelisted in the save endpoints — removing
-- the meta row would leave the editor unable to render a column the save path
-- still writes.
--
-- HOW TO USE
--   1. Edit the three settings below.
--   2. Run it. v_dry_run is TRUE by default, so the first run only reports.
--   3. Read the NOTICE output. If it is deleting what you expect, set
--      v_dry_run := false and run it again.
--
-- EXPECT THE REAL RUN TO REPORT A HIGHER TOTAL THAN THE DRY RUN, and by exactly
-- the number of live value rows. That is the triggers doing their job: deleting
-- a live row writes one more history row, which this then deletes too. The dry
-- run counts what exists now; the real run counts what exists as it goes. Not a
-- discrepancy worth worrying about.
--
-- The whole thing is one DO block, so it is a single statement and therefore
-- atomic: it either removes everything or nothing.

DO $$
DECLARE
    -- ─── EDIT THESE ─────────────────────────────────────────────────────────
    v_module  text    := 'accounts';        -- projects | accounts | stocklists | wayleave
    v_form_id text    := 'test_numeric_dropdown';   -- the field's form id, as shown in admin
    v_dry_run boolean := false;              -- false actually deletes
    -- ────────────────────────────────────────────────────────────────────────

    v_schema   text;
    v_prefix   text;
    v_field_id integer;
    v_type     text;
    v_name     text;
    v_tables   text[] := ARRAY[]::text[];
    t          text;
    n          bigint;
    total      bigint := 0;
BEGIN
    CASE v_module
        WHEN 'projects'   THEN v_schema := 'projects';   v_prefix := 'project';
        WHEN 'accounts'   THEN v_schema := 'accounts';   v_prefix := 'account';
        WHEN 'stocklists' THEN v_schema := 'stocklists'; v_prefix := 'stocklist';
        WHEN 'wayleave'   THEN v_schema := 'wayleave';   v_prefix := 'agreement';
        ELSE RAISE EXCEPTION
            'Unknown module "%" - use projects, accounts, stocklists or wayleave.', v_module;
    END CASE;

    -- Resolve the field, and refuse anything that should not be deleted this way.
    EXECUTE format('SELECT field_id, field_type, field_name FROM %I.%I WHERE field_form_id = $1',
                   v_schema, v_prefix || '_fields')
       INTO v_field_id, v_type, v_name
      USING v_form_id;

    IF v_field_id IS NULL THEN
        RAISE EXCEPTION 'No field with form id "%" in %.%_fields.',
                        v_form_id, v_schema, v_prefix;
    END IF;

    IF v_type = 'static' THEN
        RAISE EXCEPTION
            'Field "%" is a STATIC (system) field. Its storage is a real column on the entity table and its form id is whitelisted in the save endpoint, so deleting the meta row would break the editor. Refusing.',
            v_form_id;
    END IF;

    -- Plain ASCII in the NOTICE output on purpose: this goes to whatever client
    -- is running the file, and box-drawing characters arrive as mojibake on a
    -- Windows console with a mismatched code page.
    RAISE NOTICE '------------------------------------------------------------';
    RAISE NOTICE '% field "%" (form id %, id %) in module %',
                 CASE WHEN v_dry_run THEN 'WOULD DELETE' ELSE 'DELETING' END,
                 v_name, v_form_id, v_field_id, v_module;
    RAISE NOTICE '------------------------------------------------------------';

    -- Deletion order: LIVE values, then history, then dropdown options, then the
    -- meta row itself (below). Live before history because the migration-018
    -- triggers write a history row on DELETE — see the header. Reverse these two
    -- loops and the last step fails on a foreign key.
    FOREACH t IN ARRAY ARRAY['text', 'int', 'numeric', 'date', 'boolean'] LOOP
        v_tables := v_tables || (v_prefix || '_field_values_' || t);
    END LOOP;
    FOREACH t IN ARRAY ARRAY['text', 'int', 'numeric', 'date', 'boolean'] LOOP
        v_tables := v_tables || (v_prefix || '_field_values_' || t || '_history');
    END LOOP;
    v_tables := v_tables || (v_prefix || '_field_dropdown_options');

    FOREACH t IN ARRAY v_tables LOOP
        -- Skipped rather than fatal: the four modules are not perfectly uniform,
        -- and a module missing a table simply has nothing to clear from it.
        IF to_regclass(v_schema || '.' || t) IS NULL THEN
            CONTINUE;
        END IF;

        EXECUTE format('SELECT count(*) FROM %I.%I WHERE field_id = $1', v_schema, t)
           INTO n USING v_field_id;

        IF n > 0 THEN
            -- rpad, not '%-46s': PL/pgSQL's RAISE takes % as its only
            -- placeholder and has no printf width specifiers, so a '%-46s'
            -- would print the value followed by a literal "-46s".
            RAISE NOTICE '  %  % row(s)', rpad(t, 46), n;
            total := total + n;
            IF NOT v_dry_run THEN
                EXECUTE format('DELETE FROM %I.%I WHERE field_id = $1', v_schema, t)
                  USING v_field_id;
            END IF;
        END IF;
    END LOOP;

    IF total = 0 THEN
        RAISE NOTICE '  (nothing stored against this field)';
    END IF;

    IF NOT v_dry_run THEN
        EXECUTE format('DELETE FROM %I.%I WHERE field_id = $1',
                       v_schema, v_prefix || '_fields')
          USING v_field_id;
    END IF;

    RAISE NOTICE '------------------------------------------------------------';
    IF v_dry_run THEN
        RAISE NOTICE 'DRY RUN - nothing was changed. % dependent row(s) would go, plus the field itself.', total;
        RAISE NOTICE 'Set v_dry_run := false and run again to delete.';
    ELSE
        RAISE NOTICE 'DELETED - % dependent row(s) and the field itself.', total;
    END IF;
    RAISE NOTICE '------------------------------------------------------------';
END $$;
