-- Migration 0003: Gateway Applied Snapshots
-- Stores the last applied state per consumer (gateway) to support resumption.

CREATE TABLE IF NOT EXISTS supply_intelligence_gateway_applied_snapshots (
    consumer           TEXT PRIMARY KEY,
    last_event_id      TEXT NOT NULL DEFAULT '',
    last_package_id    BIGINT NOT NULL DEFAULT 0,
    last_platform      TEXT NOT NULL DEFAULT '',
    last_model         TEXT NOT NULL DEFAULT '',
    last_applied_version BIGINT NOT NULL DEFAULT 0,
    last_result        TEXT NOT NULL DEFAULT '',
    updated_at         TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_gateway_snapshots_updated
  ON supply_intelligence_gateway_applied_snapshots(updated_at DESC);