-- 009_create_sync_outbox_table.sql
-- Cola local SQLite → PG (ver §15 del documento técnico).
-- Cada operación de escritura local se encola aquí; el SyncAdapter la procesa.

CREATE TABLE IF NOT EXISTS sync_outbox (
    id                TEXT PRIMARY KEY,         -- ULID
    aggregate_type    TEXT NOT NULL,             -- 'order', 'visit', ...
    aggregate_id      TEXT NOT NULL,
    operation         TEXT NOT NULL CHECK (operation IN ('create','update','delete')),
    payload           TEXT NOT NULL,             -- JSON
    idempotency_key   TEXT NOT NULL,
    attempts          INTEGER NOT NULL DEFAULT 0,
    last_attempt_at   TEXT,
    status            TEXT NOT NULL DEFAULT 'pending'
                      CHECK (status IN ('pending','processing','synced','failed')),
    error             TEXT,
    created_at        TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_outbox_pending  ON sync_outbox(created_at) WHERE status = 'pending';
CREATE INDEX IF NOT EXISTS idx_outbox_idem     ON sync_outbox(idempotency_key);