-- Verify DIEU-28 v2.0 candidate package counts; self-scoped. WITH pub AS ( SELECT id FROM tac_publication WHERE doc_code = 'DIEU-28' AND version = 'v2.0' ), lu AS ( SELECT id FROM tac_logical_unit WHERE doc_code = 'DIEU-28' AND canonical_address LIKE 'D38-DIEU28-%' ), uv AS ( SELECT uv.id FROM tac_unit_version uv JOIN lu ON lu.id = uv.logical_unit_id ), pm AS ( SELECT pm.id FROM tac_publication_member pm JOIN pub ON pub.id = pm.publication_id JOIN lu ON lu.id = pm.logical_unit_id JOIN uv ON uv.id = pm.unit_version_id ), counts AS ( SELECT 'publication' AS target, 1 AS expected, count()::int AS actual FROM pub UNION ALL SELECT 'logical_unit', 27, count()::int FROM lu UNION ALL SELECT 'unit_version', 27, count()::int FROM uv UNION ALL SELECT 'publication_member', 27, count()::int FROM pm ) SELECT target, expected, actual, (expected = actual) AS pass FROM counts UNION ALL SELECT 'total', 82, sum(actual)::int, (sum(actual)::int = 82) FROM counts;