-- =====================================================================
-- 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 repeated until no progress (FK errors skipped)
--   Stage 2: bulk DELETE with row-wise ctid fallback (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;

  -- 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'),
-- 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,'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'),
-- 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'),
 (1,'ad_attachment',           'true'),
 (1,'ad_issue',                'true'),
 (1,'ad_schedulerlog',         'true'),
 (1,'ad_alertprocessorlog',    'true'),
 (1,'ad_workflowprocessorlog', 'true'),
 (1,'ad_acctprocessorlog',     '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 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))'),
 (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)'),
-- 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');

-- ---------------------------------------------------------------------
-- Engine
-- ---------------------------------------------------------------------
DO $$
DECLARE
  p            int;
  t            record;
  r            record;
  n            bigint;
  pass_deleted bigint;
  iter         int;
  row_deleted  bigint;
  skipped      bigint;
  stage        int;
BEGIN
  FOR p IN SELECT DISTINCT pass FROM cleanup_target ORDER BY pass LOOP
    RAISE NOTICE '=== pass % ===', p;

    -- 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
            EXECUTE format('DELETE FROM adempiere.%I WHERE %s', t.tbl, t.cond);
            GET DIAGNOSTICS n = ROW_COUNT;
            pass_deleted := pass_deleted + n;
            UPDATE cleanup_target SET done = true, survivors = 0 WHERE id = t.id;
            IF n > 0 THEN
              RAISE NOTICE '  % : deleted % rows', t.tbl, n;
            END IF;
          EXCEPTION WHEN foreign_key_violation THEN
            IF stage = 1 THEN
              CONTINUE;  -- retry in a later iteration / stage 2
            END IF;
            -- stage 2: row-wise fallback, 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;
          END;
        END LOOP;

        EXIT WHEN pass_deleted = 0;
      END LOOP;
    END LOOP;
  END LOOP;
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);

-- ---------------------------------------------------------------------
-- 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_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;