-- Versioned snapshots per region/permit/risk tuple.

BEGIN;

CREATE TABLE IF NOT EXISTS public.permit_risk_snapshots (
    snapshot_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    region_id uuid NOT NULL,
    permit_id uuid NOT NULL,
    risk_id uuid NOT NULL,
    permit_risk_key text NOT NULL,
    version integer NOT NULL,
    payload jsonb NOT NULL,
    edited_by text,
    change_summary text,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX IF NOT EXISTS permit_risk_snapshots_region_permit_risk_version_idx
    ON public.permit_risk_snapshots (region_id, permit_id, risk_id, version);

CREATE INDEX IF NOT EXISTS permit_risk_snapshots_key_idx
    ON public.permit_risk_snapshots (permit_risk_key);

COMMIT;