DO $$
DECLARE
    constraint_count integer;
    orphan_count integer;
    duplicate_count integer;
    sequence_gap_count integer;
BEGIN
    IF (SELECT max(version) FROM schema_versions) <> 1 THEN
        RAISE EXCEPTION 'unexpected schema version';
    END IF;

    SELECT count(*) INTO constraint_count
    FROM pg_constraint
    WHERE conrelid IN (
        'shipments'::regclass,
        'shipment_events'::regclass,
        'recovery_ledger'::regclass
    )
      AND contype IN ('p', 'u', 'f', 'c');

    IF constraint_count <> 13 THEN
        RAISE EXCEPTION 'expected 13 application constraints, found %',
            constraint_count;
    END IF;

    IF (SELECT count(*) FROM shipments) <> 2
        OR (SELECT count(*) FROM shipment_events) <> 4
        OR (SELECT count(*) FROM recovery_ledger) <> 2 THEN
        RAISE EXCEPTION 'restored row counts do not match backup point';
    END IF;

    SELECT count(*) INTO orphan_count
    FROM shipment_events event
    LEFT JOIN shipments shipment ON shipment.id = event.shipment_id
    WHERE shipment.id IS NULL;

    IF orphan_count <> 0 THEN
        RAISE EXCEPTION 'orphan shipment events found';
    END IF;

    SELECT count(*) INTO duplicate_count
    FROM (
        SELECT external_id
        FROM shipments
        GROUP BY external_id
        HAVING count(*) > 1
    ) duplicate;

    IF duplicate_count <> 0 THEN
        RAISE EXCEPTION 'duplicate shipment identities found';
    END IF;

    SELECT count(*) INTO sequence_gap_count
    FROM (
        SELECT shipment_id
        FROM shipment_events
        GROUP BY shipment_id
        HAVING min(event_sequence) <> 1
            OR max(event_sequence) <> count(*)
    ) gap;

    IF sequence_gap_count <> 0 THEN
        RAISE EXCEPTION 'shipment event sequence contains gaps';
    END IF;

    IF (SELECT max(sequence) FROM recovery_ledger) <> 101 THEN
        RAISE EXCEPTION 'unexpected recovery point';
    END IF;
END
$$;

SELECT 'integrity=passed';
