═══════════════════════════════════════════════════════════════════
Vendor Management — Test Data Cleanup
Run AFTER manual testing to wipe everything created during the
walkthrough and restore wallets / catalog to their pre-test state.
═══════════════════════════════════════════════════════════════════

Companion to:
  /var/www/360lm/cic-prompts/vendors_test_data_samples.txt
  /var/www/360lm/cic-prompts/vendors_v3_walkthrough.txt

Scope of cleanup:
  • Rate cards created for the 10 test vendors
  • vendor_catalog links involving the 10 test vendors
  • transaction_lines on advances to those vendors
  • Wallet reversal for those advances
  • Advance rows themselves
  • Soft-delete the 10 test vendors (is_active=false; reversible)

═══════════════════════════════════════════════════════════════════
STEP 1 — Run the safe wipe (transactional; rolls back on error)
═══════════════════════════════════════════════════════════════════

docker exec -i postgres psql -U lmadmin -d lm360 << 'EOF'
BEGIN;

-- Test vendor names (must match exactly what was entered)
WITH test_names AS (
  SELECT unnest(ARRAY[
    'Spice Garden Caterers',
    'Sunrise Printers',
    'Delhi-Mumbai Cargo Movers',
    'Ramesh Tea Stall',
    'Mehta Decorators & Stalls',
    'Reliable Workforce Solutions',
    'Sky-High Outdoor Media',
    'Kalpana Lal (Harish''s wife)',
    'Imprest Cash – Field Ops',
    'M/s. Shri Krishna Enterprises (Prop. K. Krishnamurthy) — Branch I'
  ]) AS name
),
test_vendor_ids AS (
  SELECT p.payee_id FROM custodian.payees p
  JOIN test_names tn ON tn.name = p.name
)
-- 1. Delete rate cards
, deleted_rc AS (
  DELETE FROM custodian.rate_cards
   WHERE vendor_id IN (SELECT payee_id FROM test_vendor_ids)
  RETURNING 1
)
-- 2. Delete vendor_catalog links
, deleted_vc AS (
  DELETE FROM custodian.vendor_catalog
   WHERE vendor_id IN (SELECT payee_id FROM test_vendor_ids)
  RETURNING 1
)
-- 3. Delete transaction_lines on test advances
, deleted_lines AS (
  DELETE FROM custodian.transaction_lines
   WHERE source_type = 'advance'
     AND source_id IN (
       SELECT advance_id FROM custodian.advances
        WHERE to_employee_id IN (SELECT payee_id FROM test_vendor_ids)
     )
  RETURNING 1
)
SELECT
  (SELECT COUNT(*) FROM deleted_rc)    AS rate_cards_removed,
  (SELECT COUNT(*) FROM deleted_vc)    AS vendor_catalog_links_removed,
  (SELECT COUNT(*) FROM deleted_lines) AS transaction_lines_removed;

-- 4. Reverse wallet impact of test advances (per custodian)
WITH test_vendor_ids AS (
  SELECT p.payee_id FROM custodian.payees p
  WHERE p.name = ANY (ARRAY[
    'Spice Garden Caterers','Sunrise Printers','Delhi-Mumbai Cargo Movers',
    'Ramesh Tea Stall','Mehta Decorators & Stalls','Reliable Workforce Solutions',
    'Sky-High Outdoor Media','Kalpana Lal (Harish''s wife)','Imprest Cash – Field Ops',
    'M/s. Shri Krishna Enterprises (Prop. K. Krishnamurthy) — Branch I'
  ])
),
refunds AS (
  SELECT from_custodian_id, SUM(amount) AS amt
    FROM custodian.advances
   WHERE to_employee_id IN (SELECT payee_id FROM test_vendor_ids)
     AND status <> 'cancelled'  -- don't double-refund a cancelled advance
   GROUP BY from_custodian_id
)
UPDATE custodian.wallets w
   SET balance = balance + r.amt, updated_at = NOW()
  FROM refunds r
 WHERE w.custodian_id = r.from_custodian_id;

-- 5. Delete the advances
DELETE FROM custodian.advances
 WHERE to_employee_id IN (
   SELECT payee_id FROM custodian.payees
    WHERE name = ANY (ARRAY[
      'Spice Garden Caterers','Sunrise Printers','Delhi-Mumbai Cargo Movers',
      'Ramesh Tea Stall','Mehta Decorators & Stalls','Reliable Workforce Solutions',
      'Sky-High Outdoor Media','Kalpana Lal (Harish''s wife)','Imprest Cash – Field Ops',
      'M/s. Shri Krishna Enterprises (Prop. K. Krishnamurthy) — Branch I'
    ])
 );

-- 6. Soft-delete the 10 test vendors (reversible)
UPDATE custodian.payees
   SET is_active = FALSE, updated_at = NOW()
 WHERE name = ANY (ARRAY[
   'Spice Garden Caterers','Sunrise Printers','Delhi-Mumbai Cargo Movers',
   'Ramesh Tea Stall','Mehta Decorators & Stalls','Reliable Workforce Solutions',
   'Sky-High Outdoor Media','Kalpana Lal (Harish''s wife)','Imprest Cash – Field Ops',
   'M/s. Shri Krishna Enterprises (Prop. K. Krishnamurthy) — Branch I'
 );

COMMIT;
EOF


═══════════════════════════════════════════════════════════════════
STEP 2 (OPTIONAL) — Verify post-cleanup state
═══════════════════════════════════════════════════════════════════

docker exec -i postgres psql -U lmadmin -d lm360 -c "
SELECT 'Remaining ACTIVE test vendors:' AS check, COUNT(*) AS n FROM custodian.payees
 WHERE is_active=TRUE AND name = ANY (ARRAY[
   'Spice Garden Caterers','Sunrise Printers','Delhi-Mumbai Cargo Movers',
   'Ramesh Tea Stall','Mehta Decorators & Stalls','Reliable Workforce Solutions',
   'Sky-High Outdoor Media','Kalpana Lal (Harish''s wife)','Imprest Cash – Field Ops',
   'M/s. Shri Krishna Enterprises (Prop. K. Krishnamurthy) — Branch I'
 ]);
SELECT 'Rate cards remaining for test vendors:' AS check, COUNT(*) AS n
  FROM custodian.rate_cards
 WHERE vendor_id IN (SELECT payee_id FROM custodian.payees WHERE name LIKE 'Spice Garden%' OR name LIKE 'Sunrise%' OR name LIKE 'Mehta%' OR name LIKE 'Sky-High%' OR name LIKE 'Reliable%' OR name LIKE 'Ramesh Tea%' OR name LIKE 'Delhi-Mumbai%');
SELECT 'Wallet balances after refund:' AS check, custodian_id, balance FROM custodian.wallets ORDER BY custodian_id;"


═══════════════════════════════════════════════════════════════════
STEP 3 (DESTRUCTIVE — OPTIONAL) — Hard-purge soft-deleted rows
═══════════════════════════════════════════════════════════════════
Only run this if you want to remove the soft-deleted vendor rows
entirely from the DB. The soft-delete (Step 1) is normally enough
for re-running tests; the rows just become invisible.

CAUTION: this will hard-delete ALL is_active=false payees that were
created by harish OR pramod, not just the 10 test names. Review
matches first.

# Review what would be deleted:
docker exec -i postgres psql -U lmadmin -d lm360 -c "
SELECT payee_id, name, created_by, type FROM custodian.payees
 WHERE is_active=FALSE AND created_by IN ('harish','pramod')
 ORDER BY name;"

# Then to actually purge:
# docker exec -i postgres psql -U lmadmin -d lm360 -c \
#   \"DELETE FROM custodian.payees
#      WHERE is_active=FALSE AND created_by IN ('harish','pramod');\"


═══════════════════════════════════════════════════════════════════
STEP 4 (OPTIONAL) — Clean up catalog items added inline during test
═══════════════════════════════════════════════════════════════════
Phase 3b's "Quick add to catalog" creates rows in sales.catalog
with catalog_id pattern 'CAT-<slug>-<timestamp>'. If you added any
during testing (e.g. "Backlit Glow Sign" from Transfer set #22),
review and optionally remove:

# Review:
docker exec -i postgres psql -U lmadmin -d lm360 -c "
SELECT catalog_id, name, category, default_cost, created_at
  FROM sales.catalog
 WHERE created_by IN ('harish','pramod')
   AND catalog_id LIKE 'CAT-%-%'
   AND created_at >= CURRENT_DATE - INTERVAL '7 days'
 ORDER BY created_at DESC;"

# Remove specific ones by id:
# docker exec -i postgres psql -U lmadmin -d lm360 -c \
#   \"UPDATE sales.catalog SET active=FALSE WHERE catalog_id IN ('CAT-...');\"


═══════════════════════════════════════════════════════════════════
NOTES
═══════════════════════════════════════════════════════════════════
- Step 1 is wrapped in a transaction; if any step fails, EVERYTHING
  rolls back. Safe to retry.
- The soft-delete in Step 1.6 is reversible: just UPDATE
  custodian.payees SET is_active=TRUE WHERE name=... if you need
  a vendor back.
- Step 1 does NOT touch existing real data — only rows whose vendor
  matches one of the 10 test names exactly.
- Step 3 (hard purge) targets ALL is_active=false vendors created by
  harish/pramod — REVIEW the SELECT output before uncommenting the
  DELETE.

═══════════════════════════════════════════════════════════════════
END OF CLEANUP SCRIPT
═══════════════════════════════════════════════════════════════════