-- =====================================================================
-- LogShip template cleanup — iDempiere DB truncation
-- ---------------------------------------------------------------------
-- Run ONLY on a clone (never production!). The wrapper run-cleanup.sh
-- enforces a hostname/IP guard before invoking this file.
--
-- Semantics (as decided for the "bare template"):
--   * Client 1000000 (logyou) is kept, orgs 0 + 1000000 are kept.
--   * ALL transactional data of client 1000000 is deleted.
--   * ALL business partners / products of client 1000000 are deleted,
--     EXCEPT rows required by config — enforced naturally: any row still
--     referenced by kept config survives via FK protection (row-wise
--     delete fallback skips rows whose DELETE raises an FK violation).
--   * All orgs of client 1000000 except 1000000 are purged from every
--     table that has an ad_org_id column (dynamic).
--   * GardenWorld demo client (11) and System (0) are untouched, except
--     pure log tables which are cleared globally.
--
-- Engine: cleanup_target list processed in pass order. Per pass:
--   Stage 1: bulk DELETEs (TRUNCATE when all rows match and nothing
--            references the table; otherwise set-based RI: triggers off,
--            delete, one anti-join check per incoming FK) repeated until no
--            progress (FK errors skipped)
--   Stage 2: bulk DELETE; on FK violation first a bulk DELETE of all rows
--            that no FK row references (ids collected into ref_ids, one
--            hashed anti-join — fast),
--            then a row-wise ctid fallback over the remainder (FK-blocked
--            rows survive and are counted); repeated until no progress.
-- Every target is guarded with to_regclass, so listing tables that don't
-- exist in this DB is harmless.
--
-- Usage:  psql -U postgres -d idempiere -f 01-idempiere-truncate.sql
-- (VACUUM FULL runs separately from run-cleanup.sh — not allowed in tx.)
-- =====================================================================

\set ON_ERROR_STOP on

BEGIN;

SET LOCAL search_path TO adempiere, public;

-- iDempiere creates some FKs DEFERRABLE; force them to check per-statement so
-- (a) the FK-retry engine sees their violations like any other FK and
-- (b) no pending trigger events remain when triggers are re-enabled below.
SET CONSTRAINTS ALL IMMEDIATE;

-- ---------------------------------------------------------------------
-- Disable the Elasticsearch-sync triggers (functions log_*_changes) for
-- the duration of the run. Reasons: (a) log_product_changes() reads
-- NEW.m_product_id even on DELETE -> NOT NULL violation on es_sync_queue,
-- aborting the whole run; (b) every delete would pointlessly enqueue
-- sync events into a queue we empty anyway. Re-enabled before COMMIT;
-- a rollback also restores them (ALTER TABLE is transactional).
-- ---------------------------------------------------------------------
CREATE TEMP TABLE disabled_triggers (tbl text, trg text) ON COMMIT DROP;

DO $$
DECLARE r record;
BEGIN
  FOR r IN
    SELECT c.relname AS tbl, t.tgname AS trg
    FROM pg_trigger t
    JOIN pg_class c     ON c.oid = t.tgrelid
    JOIN pg_namespace n ON n.oid = c.relnamespace
    JOIN pg_proc p      ON p.oid = t.tgfoid
    WHERE n.nspname = 'adempiere' AND NOT t.tgisinternal
      AND p.proname LIKE 'log\_%\_changes'
  LOOP
    EXECUTE format('ALTER TABLE adempiere.%I DISABLE TRIGGER %I', r.tbl, r.trg);
    INSERT INTO disabled_triggers VALUES (r.tbl, r.trg);
    RAISE NOTICE 'disabled ES-sync trigger % on %', r.trg, r.tbl;
  END LOOP;
END $$;

-- ---------------------------------------------------------------------
-- Break self-/mutual references between documents to be deleted.
-- Reversal pairs (original <-> reversal doc) reference EACH OTHER, so
-- row-wise deletion can never remove either row. NULLing the link
-- columns first makes the pairs deletable. Runs with ES triggers off.
-- ---------------------------------------------------------------------
DO $$
DECLARE
  pair record; n bigint;
BEGIN
  FOR pair IN
    SELECT * FROM (VALUES
      ('m_inout',        'reversal_id'),
      ('m_inout',        'ref_inout_id'),
      ('c_invoice',      'reversal_id'),
      ('c_invoice',      'ref_invoice_id'),
      ('c_order',        'ref_order_id'),
      ('c_order',        'link_order_id'),
      ('c_payment',      'reversal_id'),
      ('c_payment',      'ref_payment_id'),
      -- invoice<->payment cross-cycle: both sides reference each other
      ('c_invoice',      'c_payment_id'),
      ('c_payment',      'c_invoice_id'),
      ('m_inventory',    'reversal_id'),
      ('m_movement',     'reversal_id'),
      ('m_production',   'reversal_id'),
      ('c_allocationhdr','reversal_id'),
      ('gl_journal',     'reversal_id')
    ) v(tbl, col)
  LOOP
    IF EXISTS (SELECT 1 FROM information_schema.columns
               WHERE table_schema='adempiere' AND table_name=pair.tbl AND column_name=pair.col) THEN
      EXECUTE format('UPDATE adempiere.%I SET %I = NULL WHERE ad_client_id = 1000000 AND %I IS NOT NULL',
                     pair.tbl, pair.col, pair.col);
      GET DIAGNOSTICS n = ROW_COUNT;
      IF n > 0 THEN
        RAISE NOTICE 'unlinked %.% on % rows', pair.tbl, pair.col, n;
      END IF;
    END IF;
  END LOOP;

  -- Generic: every reversal link column on any client-scoped table. Line
  -- tables (m_inoutline, c_invoiceline, m_inventoryline, m_movementline, ...)
  -- carry reversalline_id and form the same mutual cycles as their headers —
  -- 688 m_inoutline pairs blocked 990 lines + 601 shipments in the 2026-10 run.
  FOR pair IN
    SELECT c.table_name AS tbl, c.column_name AS col
    FROM information_schema.columns c
    JOIN pg_tables t ON t.schemaname='adempiere' AND t.tablename=c.table_name
    WHERE c.table_schema='adempiere'
      AND c.column_name IN ('reversal_id','reversalline_id')
      AND EXISTS (SELECT 1 FROM information_schema.columns k
                  WHERE k.table_schema='adempiere' AND k.table_name=c.table_name AND k.column_name='ad_client_id')
    ORDER BY 1
  LOOP
    EXECUTE format('UPDATE adempiere.%I SET %I = NULL WHERE ad_client_id = 1000000 AND %I IS NOT NULL',
                   pair.tbl, pair.col, pair.col);
    GET DIAGNOSTICS n = ROW_COUNT;
    IF n > 0 THEN
      RAISE NOTICE 'unlinked %.% on % rows (generic)', pair.tbl, pair.col, n;
    END IF;
  END LOOP;

  -- c_bpartner.accounting_ad_org_id (custom): partners of org 1000000 may point
  -- at a customer org as their accounting org — that keeps the org alive.
  IF EXISTS (SELECT 1 FROM information_schema.columns
             WHERE table_schema='adempiere' AND table_name='c_bpartner' AND column_name='accounting_ad_org_id') THEN
    EXECUTE 'UPDATE adempiere.c_bpartner SET accounting_ad_org_id = NULL
             WHERE ad_client_id = 1000000 AND accounting_ad_org_id IN
                   (SELECT ad_org_id FROM adempiere.ad_org WHERE ad_client_id = 1000000 AND ad_org_id <> 1000000)';
    GET DIAGNOSTICS n = ROW_COUNT;
    IF n > 0 THEN RAISE NOTICE 'unlinked c_bpartner.accounting_ad_org_id on % rows', n; END IF;
  END IF;

  -- org <-> partner cycle: ad_org.c_bpartner_id (custom linked-partner column)
  -- points at partners owned by that same org. Unlink for the orgs being
  -- deleted only — org 1000000 keeps its link (it is config, see keep_bp).
  IF EXISTS (SELECT 1 FROM information_schema.columns
             WHERE table_schema='adempiere' AND table_name='ad_org' AND column_name='c_bpartner_id') THEN
    EXECUTE 'UPDATE adempiere.ad_org SET c_bpartner_id = NULL
             WHERE ad_client_id = 1000000 AND ad_org_id <> 1000000 AND c_bpartner_id IS NOT NULL';
    GET DIAGNOSTICS n = ROW_COUNT;
    IF n > 0 THEN
      RAISE NOTICE 'unlinked ad_org.c_bpartner_id on % deleted-org rows', n;
    END IF;
  END IF;
END $$;

-- ---------------------------------------------------------------------
-- Protected sets: rows that must survive because runtime config points
-- at them (defense in depth on top of FK protection).
-- ---------------------------------------------------------------------
CREATE TEMP TABLE keep_bp (c_bpartner_id numeric PRIMARY KEY) ON COMMIT DROP;
CREATE TEMP TABLE keep_prod (m_product_id numeric PRIMARY KEY) ON COMMIT DROP;

DO $$
BEGIN
  -- Custom column ad_org.c_bpartner_id (org-linked partner) — LogShip custom
  IF EXISTS (SELECT 1 FROM information_schema.columns
             WHERE table_schema='adempiere' AND table_name='ad_org' AND column_name='c_bpartner_id') THEN
    EXECUTE 'INSERT INTO keep_bp
             SELECT DISTINCT c_bpartner_id FROM adempiere.ad_org
             WHERE ad_org_id IN (0,1000000) AND c_bpartner_id IS NOT NULL
             ON CONFLICT DO NOTHING';
  END IF;
  -- Template/cash-trx partner from client info
  IF EXISTS (SELECT 1 FROM information_schema.columns
             WHERE table_schema='adempiere' AND table_name='ad_clientinfo' AND column_name='c_bpartnercashtrx_id') THEN
    EXECUTE 'INSERT INTO keep_bp
             SELECT DISTINCT c_bpartnercashtrx_id FROM adempiere.ad_clientinfo
             WHERE ad_client_id=1000000 AND c_bpartnercashtrx_id IS NOT NULL
             ON CONFLICT DO NOTHING';
  END IF;
  -- Freight product from client info
  IF EXISTS (SELECT 1 FROM information_schema.columns
             WHERE table_schema='adempiere' AND table_name='ad_clientinfo' AND column_name='m_productfreight_id') THEN
    EXECUTE 'INSERT INTO keep_prod
             SELECT DISTINCT m_productfreight_id FROM adempiere.ad_clientinfo
             WHERE ad_client_id=1000000 AND m_productfreight_id IS NOT NULL
             ON CONFLICT DO NOTHING';
  END IF;
END $$;

-- ---------------------------------------------------------------------
-- Target list
-- ---------------------------------------------------------------------
CREATE TEMP TABLE cleanup_target (
  id        serial PRIMARY KEY,
  pass      int  NOT NULL,
  tbl       text NOT NULL,
  cond      text NOT NULL,
  done      boolean NOT NULL DEFAULT false,
  survivors bigint
) ON COMMIT DROP;

-- ============================ PASS 1 =================================
-- Transactional wipe for client 1000000 + global log tables.
-- Order: custom/child tables first, then documents, then logs.
INSERT INTO cleanup_target (pass, tbl, cond) VALUES
-- custom transactional / partner-linked tables
 (1,'cust_fulfillmentfeeline',            'ad_client_id=1000000'),
 (1,'cust_commissionactivity',            'ad_client_id=1000000'),
 (1,'cust_storage_usage',                 'ad_client_id=1000000'),
 (1,'cust_storage_usage_flat',            'ad_client_id=1000000'),
 (1,'cust_paket_accounting',              'ad_client_id=1000000'),
 (1,'cust_incominginvoice',               'ad_client_id=1000000'),
 (1,'cust_crossdockalloc',                'ad_client_id=1000000'),
 (1,'cust_invoicecreationnotes',          'ad_client_id=1000000'),
 (1,'cust_agreement_cbartner',            'ad_client_id=1000000'),
 (1,'cust_vendorinvoiceprofile',          'ad_client_id=1000000'),
 (1,'cust_shippingcost_bpoverride',       'ad_client_id=1000000'),
 (1,'cust_fulfillmentproductpricingrules','ad_client_id=1000000'),
 (1,'cust_amazoninvoiceinbox',           'ad_client_id=1000000'),   -- 2026-09: Amazon fee/ads invoice inbox (-> c_invoice)
 (1,'cust_bpartner_extraorderline',      'ad_client_id=1000000 AND c_bpartner_id NOT IN (SELECT c_bpartner_id FROM keep_bp)'),
-- CRM / payment selections
 (1,'c_contactactivity',    'ad_client_id=1000000'),
 (1,'c_payselectioncheck',  'ad_client_id=1000000'),
 (1,'c_payselectionline',   'ad_client_id=1000000'),
 (1,'c_payselection',       'ad_client_id=1000000'),
-- accounting facts / matching
 (1,'fact_acct',            'ad_client_id=1000000'),
 (1,'fact_acct_summary',    'ad_client_id=1000000'),
 (1,'fact_reconciliation',  'ad_client_id=1000000'),
 (1,'t_fact_acct_history',  'ad_client_id=1000000'),
 (1,'m_matchinv',           'ad_client_id=1000000'),
 (1,'m_matchpo',            'ad_client_id=1000000'),
-- GL journals
 (1,'gl_journalline',       'ad_client_id=1000000'),
 (1,'gl_journal',           'ad_client_id=1000000'),
 (1,'gl_journalbatch',      'ad_client_id=1000000'),
-- payments / bank
 (1,'c_allocationline',     'ad_client_id=1000000'),
 (1,'c_allocationhdr',      'ad_client_id=1000000'),
 (1,'c_paymentallocate',    'ad_client_id=1000000'),
 (1,'c_payment',            'ad_client_id=1000000'),
 (1,'c_paymenttransaction', 'ad_client_id=1000000'),
 (1,'c_bankstatementline',  'ad_client_id=1000000'),
 (1,'c_bankstatement',      'ad_client_id=1000000'),
 (1,'c_depositbatchline',   'ad_client_id=1000000'),
 (1,'c_depositbatch',       'ad_client_id=1000000'),
 (1,'c_cashline',           'ad_client_id=1000000'),
 (1,'c_cash',               'ad_client_id=1000000'),
-- dunning runs
 (1,'c_dunningrunline',     'ad_client_id=1000000'),
 (1,'c_dunningrunentry',    'ad_client_id=1000000'),
 (1,'c_dunningrun',         'ad_client_id=1000000'),
-- invoices
 (1,'c_invoiceline',        'ad_client_id=1000000'),
 (1,'c_invoicetax',         'ad_client_id=1000000'),
 (1,'c_invoicepayschedule', 'ad_client_id=1000000'),
 (1,'c_invoicebatchline',   'ad_client_id=1000000'),
 (1,'c_invoicebatch',       'ad_client_id=1000000'),
 (1,'c_invoice',            'ad_client_id=1000000'),
-- attribute-set-instance children of document lines (unit tracking: serial /
-- Charge / MHD, since 2026-09). No FK to their *line parent, so they would
-- silently survive; must be gone before m_attributesetinstance (pass 2).
 (1,'m_inoutlinema',        'ad_client_id=1000000'),
 (1,'m_inventorylinema',    'ad_client_id=1000000'),
 (1,'m_movementlinema',     'ad_client_id=1000000'),
 (1,'m_productionlinema',   'ad_client_id=1000000'),
-- shipments / packages
 (1,'m_packageline',        'ad_client_id=1000000'),
 (1,'m_packagembl',         'ad_client_id=1000000'),
 (1,'m_package',            'ad_client_id=1000000'),
 (1,'m_inoutlineconfirm',   'ad_client_id=1000000'),
 (1,'m_inoutconfirm',       'ad_client_id=1000000'),
 (1,'m_inoutline',          'ad_client_id=1000000'),
 (1,'m_inout',              'ad_client_id=1000000'),
-- orders
 (1,'c_orderlandedcostallocation','ad_client_id=1000000'),
 (1,'c_orderlandedcost',    'ad_client_id=1000000'),
 (1,'c_orderline',          'ad_client_id=1000000'),
 (1,'c_ordertax',           'ad_client_id=1000000'),
 (1,'c_orderpayschedule',   'ad_client_id=1000000'),
 (1,'c_order',              'ad_client_id=1000000'),
-- distribution orders
 (1,'dd_orderline',         'ad_client_id=1000000'),
 (1,'dd_order',             'ad_client_id=1000000'),
-- inventory / movements / production / RMA / requisitions
 (1,'m_inventoryline',      'ad_client_id=1000000'),
 (1,'m_inventory',          'ad_client_id=1000000'),
 (1,'m_movementlineconfirm','ad_client_id=1000000'),
 (1,'m_movementconfirm',    'ad_client_id=1000000'),
 (1,'m_movementline',       'ad_client_id=1000000'),
 (1,'m_movement',           'ad_client_id=1000000'),
 (1,'m_productionline',     'ad_client_id=1000000'),
 (1,'m_productionplan',     'ad_client_id=1000000'),
 (1,'m_production',         'ad_client_id=1000000'),
 (1,'m_rmaline',            'ad_client_id=1000000'),
 (1,'m_rmatax',             'ad_client_id=1000000'),
 (1,'m_rma',                'ad_client_id=1000000'),
 (1,'m_requisitionline',    'ad_client_id=1000000'),
 (1,'m_requisition',        'ad_client_id=1000000'),
-- stock / costs
 (1,'m_storageonhand',        'ad_client_id=1000000'),
 (1,'m_storagereservation',   'ad_client_id=1000000'),
 (1,'m_storagereservationlog','ad_client_id=1000000'),
 (1,'m_transaction',          'ad_client_id=1000000'),
 (1,'m_costdetail',           'ad_client_id=1000000'),
 (1,'m_costhistory',          'ad_client_id=1000000'),
 (1,'m_costqueue',            'ad_client_id=1000000'),
 (1,'m_cost',                 'ad_client_id=1000000'),
-- commissions
 (1,'c_commissiondetail',   'ad_client_id=1000000'),
 (1,'c_commissionamt',      'ad_client_id=1000000'),
 (1,'c_commissionrun',      'ad_client_id=1000000'),
-- requests / tickets
 (1,'r_requestupdate',      'ad_client_id=1000000'),
 (1,'r_requestaction',      'ad_client_id=1000000'),
 (1,'r_request',            'ad_client_id=1000000'),
-- projects (transaction side; project master falls in pass 2)
 (1,'c_projectissue',       'ad_client_id=1000000'),
 (1,'c_projectline',        'ad_client_id=1000000'),
-- global logs / infrastructure noise (all clients — pure logs)
 (1,'ad_changelog',            'true'),
 (1,'ad_pinstance_log',        'true'),
 (1,'ad_pinstance_para',       'true'),
 (1,'ad_wf_eventaudit',        'true'),
 (1,'ad_wf_activityresult',    'true'),
 (1,'ad_wf_activity',          'true'),
 (1,'ad_wf_processdata',       'true'),
 (1,'ad_wf_process',           'true'),
 (1,'ad_pinstance',            'true'),
 (1,'ad_session',              'true'),
 (1,'ad_note',                 'true'),
 (1,'ad_archive',              'true'),
 -- attachments: ONLY those hanging on transactional records. Config carries
 -- attachments too: AD_PrintFormatItem images (invoice/label logos) and
 -- AD_Package_Imp_Proc 2Pack zips — those must survive.
 (1,'ad_attachment',           'ad_table_id IN (SELECT ad_table_id FROM adempiere.ad_table WHERE lower(tablename) IN (SELECT tbl FROM cleanup_target WHERE pass IN (1,2)))'),
 (1,'ad_issue',                'true'),
 (1,'ad_schedulerlog',         'true'),
 (1,'ad_alertprocessorlog',    'true'),
 (1,'ad_workflowprocessorlog', 'true'),
 (1,'ad_acctprocessorlog',     'true'),
 (1,'c_acctprocessorlog',      'true'),
 (1,'r_requestprocessorlog',   'true'),
 (1,'t_selection',             'true'),
 (1,'t_selection_infowindow',  'true'),
 (1,'ad_recentitem',           'true'),
 (1,'ad_password_history',     'true'),
 (1,'rest_authtoken',          'true'),
 (1,'rest_refreshtoken',       'true'),
 (1,'es_sync_queue',           'true'),
 (1,'t_aging',                 'true'),
 (1,'t_inventoryvalue',        'true'),
 (1,'t_invoicegl',             'true'),
 (1,'t_replenish',             'true'),
 (1,'t_reportstatement',       'true'),
 (1,'t_report',                'true'),
 (1,'t_spool',                 'true'),
 (1,'t_transaction',           'true'),
 (1,'t_trialbalance',          'true');

-- ====================== PASS 1 — DYNAMIC CATCH-ALL ====================
-- Safety net for tables that did not exist (or were empty) when the explicit
-- list above was written. Only populated candidates are added, each one is
-- announced with its row count so the reviewer sees exactly which "unknown"
-- tables were touched. Rules (client 1000000 only, FK protection still
-- applies, orgs <> 1000000 are handled by pass 3 anyway):
--   a) every cust_* table with ad_client_id, except the config tables in
--      keep_tables                                  -> ad_client_id=1000000
--   b) every table that carries a document FK column (c_order_id, c_invoice_id,
--      m_inout_id, m_inoutline_id, …) and is not dictionary/master data
--                              -> ad_client_id=1000000 AND <fk> IS NOT NULL
--   c) import staging (i_*), temp (t_*) and *log tables      -> all rows
-- ---------------------------------------------------------------------
CREATE TEMP TABLE keep_tables (tbl text PRIMARY KEY) ON COMMIT DROP;
INSERT INTO keep_tables VALUES
 ('cust_commissiontable'), ('cust_printer'), ('cust_labeltemplate'),
 ('cust_nonworkingdays'), ('cust_shippingcost_default');

DO $$
DECLARE
  r record; n bigint; cond text;
BEGIN
  -- a) custom tables
  FOR r IN
    SELECT t.tablename AS tbl
    FROM pg_tables t
    JOIN information_schema.columns c
      ON c.table_schema='adempiere' AND c.table_name=t.tablename AND c.column_name='ad_client_id'
    WHERE t.schemaname='adempiere' AND t.tablename LIKE 'cust\_%'
      AND t.tablename NOT IN (SELECT tbl FROM keep_tables)
      AND t.tablename NOT IN (SELECT tbl FROM cleanup_target)
    ORDER BY 1
  LOOP
    EXECUTE format('SELECT count(*) FROM adempiere.%I WHERE ad_client_id=1000000', r.tbl) INTO n;
    IF n > 0 THEN
      INSERT INTO cleanup_target (pass, tbl, cond) VALUES (1, r.tbl, 'ad_client_id=1000000');
      RAISE NOTICE 'DYNAMIC target (custom table) % : % rows', r.tbl, n;
    END IF;
  END LOOP;

  -- b) tables referencing documents through a well-known FK column
  FOR r IN
    SELECT DISTINCT c.table_name AS tbl, c.column_name AS col
    FROM information_schema.columns c
    JOIN pg_tables t ON t.schemaname='adempiere' AND t.tablename=c.table_name
    WHERE c.table_schema='adempiere'
      AND c.column_name IN ('c_order_id','c_orderline_id','c_invoice_id','c_invoiceline_id',
                            'm_inout_id','m_inoutline_id','c_payment_id','c_bankstatement_id',
                            'c_bankstatementline_id','m_rma_id','m_rmaline_id','r_request_id',
                            'm_movement_id','m_movementline_id','m_inventory_id','m_inventoryline_id',
                            'm_production_id','m_productionline_id','c_allocationhdr_id','gl_journal_id')
      AND EXISTS (SELECT 1 FROM information_schema.columns k
                  WHERE k.table_schema='adempiere' AND k.table_name=c.table_name AND k.column_name='ad_client_id')
      AND c.table_name NOT LIKE 'ad\_%'          -- dictionary / users / roles
      AND c.table_name NOT LIKE 'c\_doctype%'
      AND c.table_name NOT IN ('c_bpartner','c_bpartner_location','m_product','c_ordersource',
                               'm_warehouse','m_locator','m_shipper','c_charge','c_tax','c_currency',
                               'ad_org','ad_orginfo','c_acctschema','c_bankaccount','c_bank')
      AND c.table_name NOT IN (SELECT tbl FROM cleanup_target)
      AND c.table_name NOT IN (SELECT tbl FROM keep_tables)
    ORDER BY 1, 2
  LOOP
    cond := format('ad_client_id=1000000 AND %I IS NOT NULL', r.col);
    EXECUTE format('SELECT count(*) FROM adempiere.%I WHERE %s', r.tbl, cond) INTO n;
    IF n > 0 THEN
      INSERT INTO cleanup_target (pass, tbl, cond) VALUES (1, r.tbl, cond);
      RAISE NOTICE 'DYNAMIC target (document ref %.%) : % rows', r.tbl, r.col, n;
    END IF;
  END LOOP;

  -- c) import staging, temp and log tables (all clients — pure noise)
  FOR r IN
    SELECT t.tablename AS tbl
    FROM pg_tables t
    WHERE t.schemaname='adempiere'
      AND (t.tablename LIKE 'i\_%' OR t.tablename LIKE 't\_%' OR t.tablename LIKE '%log')
      AND t.tablename NOT IN ('t_alter_column','t_moveclient')   -- system utility tables
      AND t.tablename NOT IN (SELECT tbl FROM cleanup_target)
      AND t.tablename NOT IN (SELECT tbl FROM keep_tables)
    ORDER BY 1
  LOOP
    EXECUTE format('SELECT count(*) FROM adempiere.%I', r.tbl) INTO n;
    IF n > 0 THEN
      INSERT INTO cleanup_target (pass, tbl, cond) VALUES (1, r.tbl, 'true');
      RAISE NOTICE 'DYNAMIC target (import/temp/log) % : % rows', r.tbl, n;
    END IF;
  END LOOP;
END $$;

-- ============================ PASS 2 =================================
-- Bare master data for client 1000000. FK-protected rows survive.
INSERT INTO cleanup_target (pass, tbl, cond) VALUES
-- product children
 (2,'m_productprice',            'ad_client_id=1000000 AND m_product_id NOT IN (SELECT m_product_id FROM keep_prod)'),
 (2,'m_productpricevendorbreak', 'ad_client_id=1000000'),
 (2,'m_product_po',              'ad_client_id=1000000'),
 (2,'m_replenish',               'ad_client_id=1000000'),
 (2,'m_substitute',              'ad_client_id=1000000'),
 (2,'m_relatedproduct',          'ad_client_id=1000000'),
 (2,'m_product_trl',             'ad_client_id=1000000'),
 (2,'m_product_acct',            'ad_client_id=1000000 AND m_product_id NOT IN (SELECT m_product_id FROM keep_prod)'),
 (2,'m_productdownload',         'ad_client_id=1000000'),
 (2,'c_bpartner_product',        'ad_client_id=1000000'),
 (2,'pp_product_bomline_trl',    'ad_client_id=1000000'),
 (2,'pp_product_bom_trl',        'ad_client_id=1000000'),
 (2,'pp_product_bomline',        'ad_client_id=1000000'),
 (2,'pp_product_bom',            'ad_client_id=1000000'),
 -- m_product_bom deliberately absent: it is a legacy VIEW in iDempiere 10+
-- products
 (2,'m_product',                 'ad_client_id=1000000 AND m_product_id NOT IN (SELECT m_product_id FROM keep_prod)'),
-- attribute set instances (only unreferenced ones will actually delete)
 (2,'m_attributeinstance',       'ad_client_id=1000000'),
 (2,'m_attributesetinstance',    'ad_client_id=1000000'),
-- assets
 (2,'a_depreciation_exp',        'ad_client_id=1000000'),
 (2,'a_asset_acct',              'ad_client_id=1000000'),
 (2,'a_asset_addition',          'ad_client_id=1000000'),
 (2,'a_asset',                   'ad_client_id=1000000'),
-- projects master
 (2,'c_projecttask',             'ad_client_id=1000000'),
 (2,'c_projectphase',            'ad_client_id=1000000'),
 (2,'c_project',                 'ad_client_id=1000000'),
-- users of deletable partners: role/access rows first, then the users
 (2,'ad_tree_favorite_node', 'ad_tree_favorite_id IN (SELECT ad_tree_favorite_id FROM adempiere.ad_tree_favorite WHERE ad_user_id IN (SELECT ad_user_id FROM adempiere.ad_user WHERE ad_client_id=1000000 AND ((c_bpartner_id IS NOT NULL AND c_bpartner_id NOT IN (SELECT c_bpartner_id FROM keep_bp)) OR ad_org_id IN (SELECT ad_org_id FROM adempiere.ad_org WHERE ad_client_id=1000000 AND ad_org_id<>1000000))))'),
 (2,'ad_tree_favorite',      'ad_user_id IN (SELECT ad_user_id FROM adempiere.ad_user WHERE ad_client_id=1000000 AND ((c_bpartner_id IS NOT NULL AND c_bpartner_id NOT IN (SELECT c_bpartner_id FROM keep_bp)) OR ad_org_id IN (SELECT ad_org_id FROM adempiere.ad_org WHERE ad_client_id=1000000 AND ad_org_id<>1000000)))'),
 (2,'ad_wlistbox_customization', 'ad_user_id IN (SELECT ad_user_id FROM adempiere.ad_user WHERE ad_client_id=1000000 AND ((c_bpartner_id IS NOT NULL AND c_bpartner_id NOT IN (SELECT c_bpartner_id FROM keep_bp)) OR ad_org_id IN (SELECT ad_org_id FROM adempiere.ad_org WHERE ad_client_id=1000000 AND ad_org_id<>1000000)))'),
 (2,'ad_user_roles',      'ad_user_id IN (SELECT ad_user_id FROM adempiere.ad_user WHERE ad_client_id=1000000 AND c_bpartner_id IS NOT NULL AND c_bpartner_id NOT IN (SELECT c_bpartner_id FROM keep_bp))'),
 (2,'ad_user_orgaccess',  'ad_user_id IN (SELECT ad_user_id FROM adempiere.ad_user WHERE ad_client_id=1000000 AND c_bpartner_id IS NOT NULL AND c_bpartner_id NOT IN (SELECT c_bpartner_id FROM keep_bp))'),
 (2,'ad_user_substitute', 'ad_user_id IN (SELECT ad_user_id FROM adempiere.ad_user WHERE ad_client_id=1000000 AND c_bpartner_id IS NOT NULL AND c_bpartner_id NOT IN (SELECT c_bpartner_id FROM keep_bp))'),
 (2,'ad_userbpaccess',    'ad_user_id IN (SELECT ad_user_id FROM adempiere.ad_user WHERE ad_client_id=1000000 AND c_bpartner_id IS NOT NULL AND c_bpartner_id NOT IN (SELECT c_bpartner_id FROM keep_bp))'),
 (2,'ad_userpreference',  'ad_user_id IN (SELECT ad_user_id FROM adempiere.ad_user WHERE ad_client_id=1000000 AND c_bpartner_id IS NOT NULL AND c_bpartner_id NOT IN (SELECT c_bpartner_id FROM keep_bp))'),
 (2,'ad_preference',      'ad_user_id IN (SELECT ad_user_id FROM adempiere.ad_user WHERE ad_client_id=1000000 AND ((c_bpartner_id IS NOT NULL AND c_bpartner_id NOT IN (SELECT c_bpartner_id FROM keep_bp)) OR ad_org_id IN (SELECT ad_org_id FROM adempiere.ad_org WHERE ad_client_id=1000000 AND ad_org_id<>1000000)))'),
 (2,'r_contactinterest',  'ad_user_id IN (SELECT ad_user_id FROM adempiere.ad_user WHERE ad_client_id=1000000 AND c_bpartner_id IS NOT NULL AND c_bpartner_id NOT IN (SELECT c_bpartner_id FROM keep_bp))'),
 -- partner contacts AND lead contacts (CRM data: no login, no role) — staff
 -- users keep their password / role assignment and therefore survive
 (2,'ad_user',            'ad_client_id=1000000 AND ((c_bpartner_id IS NOT NULL AND c_bpartner_id NOT IN (SELECT c_bpartner_id FROM keep_bp)) OR (password IS NULL AND NOT EXISTS (SELECT 1 FROM adempiere.ad_user_roles ur WHERE ur.ad_user_id = ad_user.ad_user_id)))'),
-- partner children
 (2,'c_bp_bankaccount',    'ad_client_id=1000000 AND c_bpartner_id NOT IN (SELECT c_bpartner_id FROM keep_bp)'),
 (2,'c_bp_relation',       'ad_client_id=1000000'),
 (2,'c_bp_withholding',    'ad_client_id=1000000 AND c_bpartner_id NOT IN (SELECT c_bpartner_id FROM keep_bp)'),
 (2,'c_bp_shippingacct',   'ad_client_id=1000000 AND c_bpartner_id NOT IN (SELECT c_bpartner_id FROM keep_bp)'),
 (2,'c_bp_customer_acct',  'ad_client_id=1000000 AND c_bpartner_id NOT IN (SELECT c_bpartner_id FROM keep_bp)'),
 (2,'c_bp_vendor_acct',    'ad_client_id=1000000 AND c_bpartner_id NOT IN (SELECT c_bpartner_id FROM keep_bp)'),
 (2,'c_bp_employee_acct',  'ad_client_id=1000000 AND c_bpartner_id NOT IN (SELECT c_bpartner_id FROM keep_bp)'),
 (2,'c_bpartner_location', 'ad_client_id=1000000 AND c_bpartner_id NOT IN (SELECT c_bpartner_id FROM keep_bp)'),
-- partners
 (2,'c_bpartner',          'ad_client_id=1000000 AND c_bpartner_id NOT IN (SELECT c_bpartner_id FROM keep_bp)'),
-- orphaned addresses (referenced ones survive row-wise)
 (2,'c_location',          'ad_client_id=1000000');

-- ============================ PASS 3 =================================
-- Org purge: every table with an ad_org_id column, for all client-1000000
-- orgs except 1000000. ad_org itself is excluded here (pass 4).
INSERT INTO cleanup_target (pass, tbl, cond)
SELECT 3, t.tablename,
       'ad_org_id IN (SELECT ad_org_id FROM adempiere.ad_org WHERE ad_client_id=1000000 AND ad_org_id<>1000000)'
FROM pg_tables t
JOIN information_schema.columns c
  ON c.table_schema='adempiere' AND c.table_name=t.tablename AND c.column_name='ad_org_id'
WHERE t.schemaname='adempiere'
  AND t.tablename NOT IN ('ad_org');

-- Role children: access/assignment rows (usually stored with AD_Org_ID=0,
-- so the dynamic purge above misses them) referencing roles that belong to
-- the deleted orgs. Every table with an ad_role_id column, except ad_role.
INSERT INTO cleanup_target (pass, tbl, cond)
SELECT 3, t.tablename,
       'ad_role_id IN (SELECT ad_role_id FROM adempiere.ad_role WHERE ad_client_id=1000000 AND ad_org_id IN (SELECT ad_org_id FROM adempiere.ad_org WHERE ad_client_id=1000000 AND ad_org_id<>1000000))'
FROM pg_tables t
JOIN information_schema.columns c
  ON c.table_schema='adempiere' AND c.table_name=t.tablename AND c.column_name='ad_role_id'
WHERE t.schemaname='adempiere'
  AND t.tablename NOT IN ('ad_role');

-- Partners linked to deleted orgs (custom linkage column), org info, orgs
INSERT INTO cleanup_target (pass, tbl, cond)
SELECT 4, 'c_bpartner',
       'ad_orgbp_id IN (SELECT ad_org_id FROM adempiere.ad_org WHERE ad_client_id=1000000 AND ad_org_id<>1000000)'
WHERE EXISTS (SELECT 1 FROM information_schema.columns
              WHERE table_schema='adempiere' AND table_name='c_bpartner' AND column_name='ad_orgbp_id');
INSERT INTO cleanup_target (pass, tbl, cond) VALUES
 (4,'ad_orginfo', 'ad_org_id IN (SELECT ad_org_id FROM adempiere.ad_org WHERE ad_client_id=1000000 AND ad_org_id<>1000000)'),
 (4,'ad_org',     'ad_client_id=1000000 AND ad_org_id<>1000000'),
 -- final sweep in case anything re-enqueued sync events mid-run
 (4,'es_sync_queue', 'true');

-- ---------------------------------------------------------------------
-- Generic cycle breaking for pass-1 (transactional) targets.
-- Two rows that reference each other — within one table (reversal pairs,
-- ref_* links) or across two tables (RMA line <-> return receipt line,
-- RMA <-> shipment, invoice <-> payment) — can never be deleted row-wise:
-- each DELETE is blocked by the other row. Both rows are going away, so
-- NULLing the nullable FK column of one direction is harmless. Found via
-- pg_constraint: every nullable single-column FK whose table AND target
-- are pass-1 targets and that is either a self-reference or has an FK
-- pointing back. (The explicit pairs above stay as documentation of the
-- known cases; this block catches the ones nobody listed — 2026-10:
-- m_rmaline.m_inoutline_id <-> m_inoutline.m_rmaline_id left 648 lines
-- + 601 shipments + 613 RMAs undeletable.)
-- ---------------------------------------------------------------------
DO $$
DECLARE r record; n bigint;
BEGIN
  FOR r IN
    SELECT DISTINCT a.tbl, a.col, a.ref
    FROM (
      SELECT c.conrelid::regclass::text  AS tbl,
             att.attname                 AS col,
             c.confrelid::regclass::text AS ref,
             c.conrelid, c.confrelid
      FROM pg_constraint c
      JOIN pg_attribute att ON att.attrelid = c.conrelid AND att.attnum = c.conkey[1]
      WHERE c.contype = 'f' AND array_length(c.conkey,1) = 1 AND NOT att.attnotnull
    ) a
    WHERE a.tbl IN (SELECT tbl FROM cleanup_target WHERE pass = 1)
      AND a.ref IN (SELECT tbl FROM cleanup_target WHERE pass = 1)
      AND (a.ref = a.tbl
           OR EXISTS (SELECT 1 FROM pg_constraint c2
                      WHERE c2.contype = 'f' AND c2.conrelid = a.confrelid AND c2.confrelid = a.conrelid))
      AND EXISTS (SELECT 1 FROM information_schema.columns k
                  WHERE k.table_schema = 'adempiere' AND k.table_name = a.tbl AND k.column_name = 'ad_client_id')
    ORDER BY 1, 2
  LOOP
    EXECUTE format('UPDATE adempiere.%I SET %I = NULL WHERE ad_client_id = 1000000 AND %I IS NOT NULL',
                   r.tbl, r.col, r.col);
    GET DIAGNOSTICS n = ROW_COUNT;
    IF n > 0 THEN
      RAISE NOTICE 'cycle-break: unlinked %.% (-> %) on % rows', r.tbl, r.col, r.ref, n;
    END IF;
  END LOOP;
END $$;

-- ============================ PASS 5 =================================
-- Second sweep of the master-data and org targets. Rows that were still
-- referenced during pass 2 (e.g. a partner whose contacts were only removed
-- by the org purge in pass 3, or an org bound by c_bpartner.accounting_ad_org_id
-- of such a partner) become deletable only now.
INSERT INTO cleanup_target (pass, tbl, cond)
SELECT 5, tbl, cond FROM cleanup_target WHERE pass IN (2, 4) ORDER BY id;

-- ---------------------------------------------------------------------
-- Temporary FK indexes. Every row a DELETE removes triggers one RI check
-- per referencing FK ("SELECT 1 FROM ref WHERE fk = $1"). iDempiere indexes
-- almost none of its FK columns, so those checks seq-scan the referencing
-- table — including the dead pages left by our own earlier DELETEs
-- (m_inoutline x m_costdetail/m_inoutlinema/self = billions of row visits,
-- 30+ min per statement). An index per unindexed FK column that points at
-- any target table turns that into a lookup. Dropped again after the engine
-- so the template schema stays identical to production.
-- ---------------------------------------------------------------------
CREATE TEMP TABLE tmp_fk_indexes (idxname text) ON COMMIT DROP;

DO $$
DECLARE r record; i int := 0; nm text;
BEGIN
  FOR r IN
    SELECT DISTINCT c.conrelid AS reloid, c.conrelid::regclass::text AS reftbl, a.attname AS fkcol
    FROM pg_constraint c
    JOIN pg_class t ON t.oid = c.confrelid
    JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = c.conkey[1]
    WHERE c.contype = 'f' AND array_length(c.conkey,1) = 1
      AND t.relname IN (SELECT tbl FROM cleanup_target)
      AND NOT EXISTS (SELECT 1 FROM pg_index x
                      WHERE x.indrelid = c.conrelid AND x.indkey[0] = c.conkey[1])
    ORDER BY 2, 3
  LOOP
    i := i + 1;
    nm := format('tmp_fkidx_%s', i);
    EXECUTE format('CREATE INDEX %I ON %s (%I)', nm, r.reftbl, r.fkcol);
    INSERT INTO tmp_fk_indexes VALUES (nm);
  END LOOP;
  RAISE NOTICE 'created % temporary FK indexes', i;
END $$;

-- ---------------------------------------------------------------------
-- Engine
-- ---------------------------------------------------------------------
-- Helper: collect every id of <tbl> that some FK row still points at into
-- the temp table ref_ids, so a bulk DELETE can skip exactly those rows with
-- ONE hashed anti-join ("pk NOT IN (SELECT id FROM ref_ids)"). Returns the
-- PK column name, or NULL when the table has a multi-column FK pointing at
-- it (row-wise fallback only). One small scan per populated referencing
-- table — unlike a DELETE with 20-100 chained NOT EXISTS (c_order,
-- c_bpartner), which made the planner pick nested loops and ran > 30 min.
CREATE TEMP TABLE ref_ids (id numeric) ON COMMIT DROP;

CREATE FUNCTION pg_temp.collect_ref_ids(tbl text) RETURNS text
LANGUAGE plpgsql AS $fn$
DECLARE r record; pk text; hasrows boolean;
BEGIN
  IF EXISTS (SELECT 1 FROM pg_constraint
             WHERE contype='f' AND confrelid=('adempiere.'||quote_ident(tbl))::regclass
               AND array_length(conkey,1) > 1) THEN
    RETURN NULL;
  END IF;
  DELETE FROM ref_ids;   -- not TRUNCATE: fails when the same statement also reads ref_ids
  pk := NULL;
  FOR r IN
    SELECT c.conrelid::regclass AS reftbl, a.attname AS fkcol, ra.attname AS refcol
    FROM pg_constraint c
    JOIN pg_attribute a  ON a.attrelid  = c.conrelid  AND a.attnum  = c.conkey[1]
    JOIN pg_attribute ra ON ra.attrelid = c.confrelid AND ra.attnum = c.confkey[1]
    WHERE c.contype='f' AND c.confrelid=('adempiere.'||quote_ident(tbl))::regclass
    ORDER BY 1,2
  LOOP
    IF pk IS NULL THEN pk := r.refcol; END IF;
    IF pk <> r.refcol THEN RETURN NULL; END IF;   -- FKs onto different columns: give up
    EXECUTE format('SELECT EXISTS (SELECT 1 FROM %s)', r.reftbl) INTO hasrows;
    IF hasrows THEN
      EXECUTE format('INSERT INTO ref_ids SELECT DISTINCT %I FROM %s WHERE %I IS NOT NULL',
                     r.fkcol, r.reftbl, r.fkcol);
    END IF;
  END LOOP;
  IF pk IS NULL THEN  -- nothing references this table at all: use its PK
    SELECT a.attname INTO pk FROM pg_index i
    JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = i.indkey[0]
    WHERE i.indrelid = ('adempiere.'||quote_ident(tbl))::regclass AND i.indisprimary
      AND array_length(i.indkey::int[],1) = 1;
  END IF;
  RETURN pk;
END $fn$;

-- Set-based referential integrity. A DELETE fires one RI trigger per deleted
-- row per incoming FK (c_bpartner_location: 294k rows x ~20 FKs = 6M trigger
-- queries, 25+ min even with indexes). Instead: disable the table's triggers,
-- delete in bulk, re-enable, and verify each incoming FK with ONE anti-join.
-- A violation raises foreign_key_violation inside the engine's block, so the
-- subtransaction (DELETE + ALTERs) rolls back and the normal FK fallback runs.
CREATE FUNCTION pg_temp.has_multicol_fk(tbl text) RETURNS boolean
LANGUAGE sql AS $fn$
  SELECT EXISTS (SELECT 1 FROM pg_constraint
                 WHERE contype='f' AND confrelid=('adempiere.'||quote_ident(tbl))::regclass
                   AND array_length(conkey,1) > 1)
$fn$;

CREATE FUNCTION pg_temp.check_incoming_fks(tbl text) RETURNS text
LANGUAGE plpgsql AS $fn$
DECLARE r record; viol boolean;
BEGIN
  FOR r IN
    SELECT c.conrelid::regclass AS reftbl, a.attname AS fkcol, ra.attname AS refcol
    FROM pg_constraint c
    JOIN pg_attribute a  ON a.attrelid  = c.conrelid  AND a.attnum  = c.conkey[1]
    JOIN pg_attribute ra ON ra.attrelid = c.confrelid AND ra.attnum = c.confkey[1]
    WHERE c.contype='f' AND c.confrelid=('adempiere.'||quote_ident(tbl))::regclass
  LOOP
    EXECUTE format('SELECT EXISTS (SELECT 1 FROM %s r WHERE r.%I IS NOT NULL
                                   AND NOT EXISTS (SELECT 1 FROM adempiere.%I t WHERE t.%I = r.%I))',
                   r.reftbl, r.fkcol, tbl, r.refcol, r.fkcol) INTO viol;
    IF viol THEN RETURN r.reftbl::text || '.' || r.fkcol; END IF;
  END LOOP;
  RETURN NULL;
END $fn$;

-- delete_setbased(tbl, sql): run <sql> (a DELETE on tbl) with RI triggers off,
-- then verify; returns rows deleted or raises foreign_key_violation.
CREATE FUNCTION pg_temp.delete_setbased(tbl text, sql text) RETURNS bigint
LANGUAGE plpgsql AS $fn$
DECLARE n bigint; viol text; r record;
BEGIN
  EXECUTE format('ALTER TABLE adempiere.%I DISABLE TRIGGER ALL', tbl);
  EXECUTE sql;
  GET DIAGNOSTICS n = ROW_COUNT;
  EXECUTE format('ALTER TABLE adempiere.%I ENABLE TRIGGER ALL', tbl);
  -- ENABLE ALL also re-armed the ES-sync triggers we switched off at the start
  FOR r IN SELECT trg FROM disabled_triggers WHERE disabled_triggers.tbl = delete_setbased.tbl LOOP
    EXECUTE format('ALTER TABLE adempiere.%I DISABLE TRIGGER %I', tbl, r.trg);
  END LOOP;
  viol := pg_temp.check_incoming_fks(tbl);
  IF viol IS NOT NULL THEN
    RAISE foreign_key_violation USING MESSAGE = format('set-based RI: %s still references %s', viol, tbl);
  END IF;
  RETURN n;
END $fn$;

-- Helper: TRUNCATE is equivalent to DELETE when every row matches <cond>
-- and no other table has an FK into <tbl>. It is also ~instant and gives
-- the table a fresh empty relfilenode — important because later FK checks
-- (e.g. deleting 45k ad_session rows) seq-scan referencing tables that
-- have no index on the FK column; after a DELETE those scans still read
-- all the dead pages (ad_changelog: 1.36M rows -> 45k scans -> hours).
CREATE FUNCTION pg_temp.truncatable(tbl text, cond text) RETURNS boolean
LANGUAGE plpgsql AS $fn$
DECLARE other_rows boolean;
BEGIN
  IF EXISTS (SELECT 1 FROM pg_constraint
             WHERE contype='f' AND confrelid=('adempiere.'||quote_ident(tbl))::regclass
               AND conrelid <> confrelid) THEN
    RETURN false;
  END IF;
  IF cond = 'true' THEN RETURN true; END IF;
  EXECUTE format('SELECT EXISTS (SELECT 1 FROM adempiere.%I WHERE NOT (%s))', tbl, cond) INTO other_rows;
  RETURN NOT other_rows;
END $fn$;

DO $$
DECLARE
  p            int;
  t            record;
  r            record;
  n            bigint;
  pass_deleted bigint;
  iter         int;
  row_deleted  bigint;
  skipped      bigint;
  stage        int;
  unref        text;
BEGIN
  FOR p IN SELECT DISTINCT pass FROM cleanup_target ORDER BY pass LOOP
    RAISE NOTICE '=== pass % === [%]', p, to_char(clock_timestamp(),'HH24:MI:SS');

    -- drop targets that are not real tables in this DB (missing, or a VIEW
    -- like the legacy m_product_bom — DELETE on a view would abort the run)
    UPDATE cleanup_target ct SET done = true, survivors = 0
    WHERE ct.pass = p AND NOT EXISTS (
      SELECT 1 FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
      WHERE n.nspname = 'adempiere' AND c.relname = ct.tbl AND c.relkind IN ('r','p')
    );

    FOR stage IN 1..2 LOOP   -- stage 1: bulk only; stage 2: with row-wise fallback
      iter := 0;
      LOOP
        iter := iter + 1;
        IF iter > 100 THEN
          RAISE EXCEPTION 'pass % stage % did not converge after 100 iterations', p, stage;
        END IF;
        pass_deleted := 0;

        FOR t IN SELECT * FROM cleanup_target WHERE pass = p AND NOT done ORDER BY id LOOP
          BEGIN
            IF pg_temp.truncatable(t.tbl, t.cond) THEN
              EXECUTE format('SELECT count(*) FROM adempiere.%I', t.tbl) INTO n;
              IF n > 0 THEN
                EXECUTE format('TRUNCATE TABLE adempiere.%I', t.tbl);
                RAISE NOTICE '  % : truncated % rows [%]', t.tbl, n, to_char(clock_timestamp(),'HH24:MI:SS');
              END IF;
            ELSIF NOT pg_temp.has_multicol_fk(t.tbl) THEN
              n := pg_temp.delete_setbased(t.tbl, format('DELETE FROM adempiere.%I WHERE %s', t.tbl, t.cond));
              IF n > 0 THEN
                RAISE NOTICE '  % : deleted % rows [%]', t.tbl, n, to_char(clock_timestamp(),'HH24:MI:SS');
              END IF;
            ELSE
              EXECUTE format('DELETE FROM adempiere.%I WHERE %s', t.tbl, t.cond);
              GET DIAGNOSTICS n = ROW_COUNT;
              IF n > 0 THEN
                RAISE NOTICE '  % : deleted % rows [%]', t.tbl, n, to_char(clock_timestamp(),'HH24:MI:SS');
              END IF;
            END IF;
            pass_deleted := pass_deleted + n;
            UPDATE cleanup_target SET done = true, survivors = 0 WHERE id = t.id;
          EXCEPTION WHEN foreign_key_violation THEN
            IF stage = 1 THEN
              CONTINUE;  -- retry in a later iteration / stage 2
            END IF;
            -- stage 2a: one bulk DELETE of every row no FK row points at.
            -- Chains (row A referenced by row B that is itself deletable)
            -- resolve over the outer until-no-progress loop.
            unref := pg_temp.collect_ref_ids(t.tbl);   -- fills ref_ids, returns the PK column
            IF unref IS NOT NULL THEN
              BEGIN
                n := pg_temp.delete_setbased(t.tbl,
                       format('DELETE FROM adempiere.%I WHERE %s AND %I NOT IN (SELECT id FROM ref_ids)',
                              t.tbl, t.cond, unref));
                pass_deleted := pass_deleted + n;
                IF n > 0 THEN
                  RAISE NOTICE '  % : bulk deleted % unreferenced rows [%]', t.tbl, n, to_char(clock_timestamp(),'HH24:MI:SS');
                END IF;
              EXCEPTION WHEN foreign_key_violation THEN
                NULL;  -- unexpected (trigger-enforced ref?) — fall through to row-wise
              END;
            END IF;
            -- stage 2b: row-wise fallback over the remainder, FK-blocked rows survive
            row_deleted := 0;
            skipped     := 0;
            FOR r IN EXECUTE format('SELECT ctid AS rid FROM adempiere.%I WHERE %s', t.tbl, t.cond) LOOP
              BEGIN
                EXECUTE format('DELETE FROM adempiere.%I WHERE ctid = $1', t.tbl) USING r.rid;
                row_deleted := row_deleted + 1;
              EXCEPTION WHEN foreign_key_violation THEN
                skipped := skipped + 1;
              END;
            END LOOP;
            pass_deleted := pass_deleted + row_deleted;
            IF skipped = 0 THEN
              UPDATE cleanup_target SET done = true, survivors = 0 WHERE id = t.id;
            ELSE
              UPDATE cleanup_target SET survivors = skipped WHERE id = t.id;
            END IF;
            RAISE NOTICE '  % : row-wise deleted %, FK-protected survivors % [%]', t.tbl, row_deleted, skipped, to_char(clock_timestamp(),'HH24:MI:SS');
          END;
        END LOOP;

        EXIT WHEN pass_deleted = 0;
      END LOOP;
    END LOOP;
  END LOOP;
END $$;

-- ---------------------------------------------------------------------
-- Drop the temporary FK indexes again (schema must match production).
-- ---------------------------------------------------------------------
DO $$
DECLARE r record;
BEGIN
  FOR r IN SELECT idxname FROM tmp_fk_indexes LOOP
    EXECUTE format('DROP INDEX IF EXISTS adempiere.%I', r.idxname);
  END LOOP;
  RAISE NOTICE 'dropped % temporary FK indexes', (SELECT count(*) FROM tmp_fk_indexes);
END $$;

-- ---------------------------------------------------------------------
-- Re-enable the ES-sync triggers disabled at the start.
-- ---------------------------------------------------------------------
SET CONSTRAINTS ALL IMMEDIATE;  -- flush any pending deferred checks first

DO $$
DECLARE r record;
BEGIN
  FOR r IN SELECT tbl, trg FROM disabled_triggers LOOP
    EXECUTE format('ALTER TABLE adempiere.%I ENABLE TRIGGER %I', r.tbl, r.trg);
  END LOOP;
  RAISE NOTICE 're-enabled % ES-sync triggers', (SELECT count(*) FROM disabled_triggers);
END $$;

-- ---------------------------------------------------------------------
-- Recreate the AD_Session row referenced by the long-lived SuperUser
-- IDEMPIERETOKEN in the frontend .env (AD_Session_ID 1000081). The REST
-- servlet 500s on a token whose session row is gone — without this,
-- every clone needs the row re-inserted before server-side system calls
-- work. Claims in the token: user 100, role 1000000, client 1000000, org 0.
-- ---------------------------------------------------------------------
INSERT INTO adempiere.ad_session
  (ad_session_id, ad_client_id, ad_org_id, isactive, created, createdby,
   updated, updatedby, websession, remote_addr, remote_host, processed,
   description, ad_role_id, logindate, ad_session_uu, servername)
SELECT 1000081, 1000000, 0, 'Y', now(), 100, now(), 100, 'REST',
       '127.0.0.1', 'localhost', 'N',
       'Recreated for IDEMPIERETOKEN (template cleanup)', 1000000, now(),
       gen_random_uuid()::text, 'template'
WHERE NOT EXISTS (SELECT 1 FROM adempiere.ad_session WHERE ad_session_id = 1000081);

-- ---------------------------------------------------------------------
-- Scrub external credentials stored in CONFIG rows that survive (the
-- template must not carry production API keys): per-shipper DHL
-- credentials/billing numbers (m_shipper, since 2026-08) and marketplace /
-- carrier secrets on the remaining order sources (org 0 + 1000000).
-- Column list is guarded — missing columns are skipped.
-- ---------------------------------------------------------------------
DO $$
DECLARE r record; n bigint;
BEGIN
  FOR r IN
    SELECT * FROM (VALUES
      ('m_shipper', 'dhlkey'), ('m_shipper', 'dhlsecret'), ('m_shipper', 'dhluser'), ('m_shipper', 'dhlpass'),
      ('m_shipper', 'dhl_billing_number_de'), ('m_shipper', 'dhl_billing_number_int'),
      ('m_shipper', 'dhl_billing_number_retoure'), ('m_shipper', 'dhl_billing_number_wp_int'),
      ('m_shipper', 'dhl_billing_number_kp'),
      ('c_ordersource', 'dhlkey'), ('c_ordersource', 'dhlsecret'), ('c_ordersource', 'dhluser'),
      ('c_ordersource', 'dhlpass'), ('c_ordersource', 'dpdpassword'),
      ('c_ordersource', 'marketplace_key'), ('c_ordersource', 'marketplace_secret'),
      ('c_ordersource', 'marketplace_token'), ('c_ordersource', 'shopware_key'),
      ('c_ordersource', 'shopware_secret'), ('c_ordersource', 'ebay_access_token'),
      ('c_ordersource', 'ebay_refresh_token')
    ) v(tbl, col)
  LOOP
    IF EXISTS (SELECT 1 FROM information_schema.columns
               WHERE table_schema='adempiere' AND table_name=r.tbl AND column_name=r.col) THEN
      EXECUTE format('UPDATE adempiere.%I SET %I = NULL WHERE ad_client_id = 1000000 AND %I IS NOT NULL',
                     r.tbl, r.col, r.col);
      GET DIAGNOSTICS n = ROW_COUNT;
      IF n > 0 THEN
        RAISE NOTICE 'scrubbed %.% on % rows', r.tbl, r.col, n;
      END IF;
    END IF;
  END LOOP;
END $$;

-- ---------------------------------------------------------------------
-- Reset DocumentNo sequences for the kept client (record-ID sequences
-- with istableid='Y' must NOT be touched).
-- ---------------------------------------------------------------------
UPDATE adempiere.ad_sequence
   SET currentnext = startno
 WHERE ad_client_id = 1000000
   AND istableid = 'N'
   AND name LIKE 'DocumentNo%';

-- ---------------------------------------------------------------------
-- Report (printed by psql before commit)
-- ---------------------------------------------------------------------
SELECT 'FK-protected survivors' AS report, tbl, cond, survivors
FROM cleanup_target
WHERE COALESCE(survivors,0) > 0
ORDER BY survivors DESC;

SELECT 'remaining orgs (client 1000000)' AS report, ad_org_id, name
FROM adempiere.ad_org WHERE ad_client_id = 1000000 ORDER BY ad_org_id;

SELECT 'zero checks' AS report, x.tbl, x.cnt FROM (
  SELECT 'c_order' AS tbl,  count(*) AS cnt FROM adempiere.c_order   WHERE ad_client_id=1000000 UNION ALL
  SELECT 'c_invoice',       count(*) FROM adempiere.c_invoice        WHERE ad_client_id=1000000 UNION ALL
  SELECT 'm_inout',         count(*) FROM adempiere.m_inout          WHERE ad_client_id=1000000 UNION ALL
  SELECT 'fact_acct',       count(*) FROM adempiere.fact_acct        WHERE ad_client_id=1000000 UNION ALL
  SELECT 'm_storageonhand', count(*) FROM adempiere.m_storageonhand  WHERE ad_client_id=1000000 UNION ALL
  SELECT 'm_transaction',   count(*) FROM adempiere.m_transaction    WHERE ad_client_id=1000000 UNION ALL
  SELECT 'r_request',       count(*) FROM adempiere.r_request        WHERE ad_client_id=1000000 UNION ALL
  SELECT 'c_contactactivity', count(*) FROM adempiere.c_contactactivity WHERE ad_client_id=1000000 UNION ALL
  SELECT 'c_payselection',  count(*) FROM adempiere.c_payselection   WHERE ad_client_id=1000000 UNION ALL
  SELECT 'm_inoutlinema',   count(*) FROM adempiere.m_inoutlinema    WHERE ad_client_id=1000000 UNION ALL
  SELECT 'cust_amazoninvoiceinbox', count(*) FROM adempiere.cust_amazoninvoiceinbox WHERE ad_client_id=1000000 UNION ALL
  SELECT 'c_bpartner',      count(*) FROM adempiere.c_bpartner       WHERE ad_client_id=1000000 UNION ALL
  SELECT 'm_product',       count(*) FROM adempiere.m_product        WHERE ad_client_id=1000000
) x;

COMMIT;