Files
laraundfelix/scripts/paket0-readonly.sql
LarsandCursor 7820bececb
Deploy Test / test (push) Failing after 10s
Deploy Test / deploy (push) Skipped
Paket 1: eine aktive Angebotsfassung und Bestellbezeichnung
Co-authored-by: Cursor <cursoragent@cursor.com>
2026-11-16 06:54:43 +01:00

52 lines
1.8 KiB
SQL

-- Paket 0: lesende Bestandsaufnahme (Testinstanz). Keine Schreibzugriffe.
\echo '=== Angebote mit mehr als einer aktiven Fassung ==='
SELECT o.id AS offer_id,
o.name AS offer_name,
count(*)::int AS active_count,
string_agg(ov.number::text, ',' ORDER BY ov.number) AS active_numbers,
string_agg(DISTINCT ov.grant_kind, ',') AS grant_kinds
FROM offers o
JOIN offer_versions ov ON ov.offer_id = o.id AND ov.status = 'aktiv'
GROUP BY o.id, o.name
HAVING count(*) > 1
ORDER BY o.id;
\echo '=== Alle aktiven Registrierungsfassungen ==='
SELECT ov.offer_id,
o.name AS offer_name,
ov.id AS version_id,
ov.number,
ov.grant_kind
FROM offer_versions ov
JOIN offers o ON o.id = ov.offer_id
WHERE ov.status = 'aktiv'
AND ov.grant_kind = 'bei_registrierung'
AND ov.holder_kind = 'person'
ORDER BY ov.offer_id, ov.number;
\echo '=== Spalte offer_name auf order_lines ==='
SELECT column_name
FROM information_schema.columns
WHERE table_schema = 'public'
AND table_name = 'order_lines'
AND column_name = 'offer_name';
\echo '=== Anzahl Bestellzeilen gesamt ==='
SELECT count(*)::int AS order_line_count FROM order_lines;
\echo '=== Grants mit offer_version_id in conditions (aktive Dubletten-Angebote) ==='
SELECT ov.offer_id,
ov.number AS version_number,
(a.conditions->>'offer_version_id') AS stored_version_id,
count(g.id)::int AS grant_count
FROM offer_versions ov
JOIN offers o ON o.id = ov.offer_id
LEFT JOIN acquisitions a ON (a.conditions->>'offer_version_id')::bigint = ov.id
LEFT JOIN grants g ON g.acquisition_id = a.id
WHERE ov.status = 'aktiv'
AND ov.offer_id IN (
SELECT offer_id FROM offer_versions WHERE status = 'aktiv' GROUP BY offer_id HAVING count(*) > 1
)
GROUP BY ov.offer_id, ov.number, a.conditions->>'offer_version_id'
ORDER BY ov.offer_id, ov.number;