80 lines
2.5 KiB
PL/PgSQL
80 lines
2.5 KiB
PL/PgSQL
|
-- Hard-delete leftover v1-process-smoke EANs (8700999...) from Platform Demo only.
|
||
|
|
--
|
||
|
|
-- There is no product soft-delete HTTP API (products are GET/PATCH only).
|
||
|
|
-- This script is the safe admin/ops path for Demo cleanup.
|
||
|
|
--
|
||
|
|
-- NEVER touches A1 Slovenija (or A1 cohort aliases). Refuses if the resolved
|
||
|
|
-- target is missing, ambiguous, or looks like A1.
|
||
|
|
--
|
||
|
|
-- Usage (PowerShell):
|
||
|
|
-- psql $env:DATABASE_URL -v ON_ERROR_STOP=1 -f scripts/cleanup-process-smoke-eans.sql
|
||
|
|
--
|
||
|
|
-- Preview only (no deletes):
|
||
|
|
-- SELECT c.name, r.gtin FROM raw_products r
|
||
|
|
-- JOIN companies c ON c.id = r.company_id
|
||
|
|
-- WHERE lower(c.name) = 'platform demo' AND r.gtin LIKE '8700999%';
|
||
|
|
--
|
||
|
|
-- Go path: cd apps/api && go run ./cmd/seed-demo -purge-smoke-eans
|
||
|
|
-- Related: scripts/v1-process-smoke, docs/safe-test-fixtures.md
|
||
|
|
|
||
|
|
BEGIN;
|
||
|
|
|
||
|
|
CREATE TEMP TABLE smoke_demo_target ON COMMIT DROP AS
|
||
|
|
SELECT c.id,
|
||
|
|
c.name,
|
||
|
|
COALESCE(c.legacy_company_id, '') AS legacy_company_id
|
||
|
|
FROM companies c
|
||
|
|
WHERE lower(c.name) = 'platform demo';
|
||
|
|
|
||
|
|
DO $$
|
||
|
|
DECLARE
|
||
|
|
n int;
|
||
|
|
leg text;
|
||
|
|
nm text;
|
||
|
|
BEGIN
|
||
|
|
SELECT COUNT(*) INTO n FROM smoke_demo_target;
|
||
|
|
IF n <> 1 THEN
|
||
|
|
RAISE EXCEPTION 'Platform Demo company not found uniquely (count=%). Aborting.', n;
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
SELECT legacy_company_id, name INTO leg, nm FROM smoke_demo_target;
|
||
|
|
|
||
|
|
IF lower(leg) = '97e1a309-3d23-4aa2-b518-8e8d7afdfec7' THEN
|
||
|
|
RAISE EXCEPTION 'refusing smoke EAN cleanup: target has A1 legacy_company_id';
|
||
|
|
END IF;
|
||
|
|
IF lower(nm) IN ('a1 slovenija', 'a1', 'local demo co') THEN
|
||
|
|
RAISE EXCEPTION 'refusing smoke EAN cleanup: target name % is an A1 cohort alias', nm;
|
||
|
|
END IF;
|
||
|
|
END $$;
|
||
|
|
|
||
|
|
SELECT
|
||
|
|
(SELECT COUNT(*) FROM raw_products r
|
||
|
|
JOIN smoke_demo_target t ON t.id = r.company_id
|
||
|
|
WHERE r.gtin LIKE '8700999%') AS raw_count,
|
||
|
|
(SELECT COUNT(*) FROM processed_products p
|
||
|
|
JOIN smoke_demo_target t ON t.id = p.company_id
|
||
|
|
WHERE p.product_id LIKE '8700999%') AS processed_count;
|
||
|
|
|
||
|
|
DELETE FROM processing_job_products pjp
|
||
|
|
WHERE pjp.raw_product_id IN (
|
||
|
|
SELECT r.id FROM raw_products r
|
||
|
|
JOIN smoke_demo_target t ON t.id = r.company_id
|
||
|
|
WHERE r.gtin LIKE '8700999%'
|
||
|
|
)
|
||
|
|
OR pjp.processed_product_id IN (
|
||
|
|
SELECT p.id FROM processed_products p
|
||
|
|
JOIN smoke_demo_target t ON t.id = p.company_id
|
||
|
|
WHERE p.product_id LIKE '8700999%'
|
||
|
|
);
|
||
|
|
|
||
|
|
DELETE FROM processed_products p
|
||
|
|
USING smoke_demo_target t
|
||
|
|
WHERE p.company_id = t.id
|
||
|
|
AND p.product_id LIKE '8700999%';
|
||
|
|
|
||
|
|
DELETE FROM raw_products r
|
||
|
|
USING smoke_demo_target t
|
||
|
|
WHERE r.company_id = t.id
|
||
|
|
AND r.gtin LIKE '8700999%';
|
||
|
|
|
||
|
|
COMMIT;
|