DO $$
DECLARE
    restored_status text;
    restored_events text[];
BEGIN
    SELECT shipment.status,
           array_agg(event.kind ORDER BY event.event_sequence)
    INTO restored_status, restored_events
    FROM shipments shipment
    JOIN shipment_events event ON event.shipment_id = shipment.id
    WHERE shipment.external_id = 'shipment-100'
    GROUP BY shipment.status;

    IF restored_status IS DISTINCT FROM 'booked'
        OR restored_events IS DISTINCT FROM
            ARRAY['booked', 'label_ready']::text[] THEN
        RAISE EXCEPTION 'known shipment contract is not usable';
    END IF;

    IF EXISTS (SELECT 1 FROM shipments WHERE external_id = 'shipment-102') THEN
        RAISE EXCEPTION 'post-backup shipment unexpectedly restored';
    END IF;
END
$$;

DO $$
BEGIN
    BEGIN
        INSERT INTO shipments (
            external_id,
            status,
            price_cents,
            destination_postal,
            booked_at
        ) VALUES (
            'shipment-100',
            'booked',
            100,
            '00100',
            '2026-08-01T10:06:00Z'
        );
        RAISE EXCEPTION 'duplicate identity was accepted';
    EXCEPTION
        WHEN unique_violation THEN NULL;
    END;
END
$$;

BEGIN;

INSERT INTO shipments (
    external_id,
    status,
    price_cents,
    destination_postal,
    booked_at
) VALUES (
    'probe-shipment',
    'booked',
    999,
    '20100',
    '2026-08-01T10:06:00Z'
);

INSERT INTO shipment_events (
    shipment_id,
    event_sequence,
    kind,
    occurred_at
)
SELECT id, 1, 'booked', '2026-08-01T10:06:00Z'
FROM shipments
WHERE external_id = 'probe-shipment';

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM shipments shipment
        JOIN shipment_events event ON event.shipment_id = shipment.id
        WHERE shipment.external_id = 'probe-shipment'
          AND shipment.status = 'booked'
          AND event.event_sequence = 1
          AND event.kind = 'booked'
    ) THEN
        RAISE EXCEPTION 'atomic booking probe failed';
    END IF;
END
$$;

ROLLBACK;

DO $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM shipments WHERE external_id = 'probe-shipment'
    ) THEN
        RAISE EXCEPTION 'probe transaction did not roll back';
    END IF;
END
$$;

SELECT 'application=passed';
