BEGIN;

CREATE TABLE schema_versions (
    version integer PRIMARY KEY,
    applied_at timestamptz NOT NULL
);

CREATE TABLE shipments (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    external_id text NOT NULL UNIQUE,
    status text NOT NULL CHECK (status IN ('booked', 'cancelled')),
    price_cents integer NOT NULL CHECK (price_cents > 0),
    destination_postal text NOT NULL CHECK (length(destination_postal) > 0),
    booked_at timestamptz NOT NULL
);

CREATE TABLE shipment_events (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    shipment_id bigint NOT NULL REFERENCES shipments(id) ON DELETE CASCADE,
    event_sequence integer NOT NULL CHECK (event_sequence > 0),
    kind text NOT NULL CHECK (kind IN ('booked', 'label_ready', 'cancelled')),
    occurred_at timestamptz NOT NULL,
    UNIQUE (shipment_id, event_sequence)
);

CREATE TABLE recovery_ledger (
    sequence bigint PRIMARY KEY,
    business_id text NOT NULL UNIQUE,
    kind text NOT NULL CHECK (kind = 'shipment_booked'),
    committed_at timestamptz NOT NULL
);

INSERT INTO schema_versions (version, applied_at)
VALUES (1, '2026-08-01T09:00:00Z');

COMMIT;
