-- Garuda canonical event log — generalization of tag-mgmt `movement_events` (ADR-0014 / lift plan).
-- Thin-first: promoted queryable columns + the full canonical envelope in `body` JSONB.
-- Append-only by convention (the adapter exposes no UPDATE/DELETE); partitioning is a fast-follow.

CREATE TABLE IF NOT EXISTS events (
  record_seq      BIGSERIAL   NOT NULL,                 -- durable order (movement_events uses event_time+uuidv7)
  event_id        TEXT        NOT NULL,                 -- widened from uuid (our ids aren't uuids yet)
  idempotency_key TEXT        NOT NULL,
  schema_version  SMALLINT    NOT NULL DEFAULT 1,
  tenant          TEXT        NOT NULL,                 -- partition/scope key (tenant_id uuid in source)
  type            TEXT        NOT NULL,                 -- OBJECT | AGGREGATION | TRANSACTION | ASSOCIATION | TRANSFORMATION
  pillar          TEXT        NOT NULL,
  action          TEXT        NOT NULL,                 -- ADD | OBSERVE | DELETE
  biz_step        TEXT        NOT NULL,
  disposition     TEXT        NOT NULL,
  parent_id       TEXT,
  correlation_id  TEXT        NOT NULL,                 -- = process instance (ADR-0014 D14.4)
  seq             INT         NOT NULL,
  event_time      TIMESTAMPTZ NOT NULL,                 -- edge/business time
  record_time     TIMESTAMPTZ NOT NULL DEFAULT now(),   -- ingest time (= source created_at)
  body            JSONB       NOT NULL,                 -- the full canonical envelope (projections read this)
  PRIMARY KEY (event_id)
);

CREATE INDEX IF NOT EXISTS idx_events_tenant_seq ON events (tenant, record_seq);

-- Idempotency side-table — mirrors source `idempotency_keys` (pre-tx dedup + TTL prune).
CREATE TABLE IF NOT EXISTS idempotency_keys (
  key        TEXT        NOT NULL PRIMARY KEY,
  event_id   TEXT        NOT NULL,
  tenant     TEXT        NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  expires_at TIMESTAMPTZ NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_idemp_expires ON idempotency_keys (expires_at);
