-- pgcrypto extension
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- events
CREATE TABLE IF NOT EXISTS events (
  event_sequence      BIGINT      GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  id                  UUID        NOT NULL UNIQUE,
  aggregate_type      TEXT        NOT NULL,
  aggregate_id        UUID        NOT NULL,
  aggregate_version   BIGINT      NOT NULL CHECK (aggregate_version > 0),
  event_name          TEXT        NOT NULL,
  payload             JSONB       NOT NULL,
  occurred_at         TIMESTAMPTZ NOT NULL,
  correlation_id      UUID        NOT NULL,
  causation_id        UUID        NOT NULL,
  context             JSONB       NOT NULL DEFAULT '{}'::jsonb,
  CONSTRAINT events_uniq_aggregate_version
    UNIQUE (aggregate_type, aggregate_id, aggregate_version)
);

CREATE INDEX IF NOT EXISTS idx_events_occurred_at ON events (occurred_at);
CREATE INDEX IF NOT EXISTS idx_events_correlation_id ON events (correlation_id);
CREATE INDEX IF NOT EXISTS idx_events_causation_id   ON events (causation_id);
CREATE INDEX IF NOT EXISTS idx_events_event_name     ON events (event_name);
CREATE INDEX IF NOT EXISTS idx_events_payload_type ON events ((payload->>'type'));

COMMENT ON TABLE  events IS 'Event store: append-only; (aggregate_type, aggregate_id, aggregate_version) is unique.';

-- snapshots
CREATE TABLE IF NOT EXISTS snapshots (
  id                  UUID PRIMARY KEY,
  aggregate_type      TEXT        NOT NULL,
  aggregate_id        UUID        NOT NULL,
  aggregate_version   BIGINT      NOT NULL CHECK (aggregate_version > 0),
  state               JSONB       NOT NULL,
  materialized_at     TIMESTAMPTZ NOT NULL,
  CONSTRAINT snapshots_uniq_aggregate_version
    UNIQUE (aggregate_type, aggregate_id, aggregate_version)
);

CREATE INDEX IF NOT EXISTS idx_snapshots_materialized_at
  ON snapshots (materialized_at);

COMMENT ON TABLE snapshots IS 'Materialized snapshots per aggregate version; latest is fetched via DESC index.';

-- event_outbox
CREATE TABLE IF NOT EXISTS event_outbox (
  id                   UUID        PRIMARY KEY,
  event_sequence       BIGINT      NOT NULL UNIQUE,
  event_id             UUID        NOT NULL UNIQUE,
  aggregate_type       TEXT        NOT NULL,
  aggregate_id         UUID        NOT NULL,
  aggregate_version    BIGINT      NOT NULL CHECK (aggregate_version > 0),
  event_name           TEXT        NOT NULL,
  payload              JSONB       NOT NULL,
  occurred_at          TIMESTAMPTZ NOT NULL,
  correlation_id       UUID        NOT NULL,
  causation_id         UUID        NOT NULL,
  context              JSONB       NOT NULL DEFAULT '{}'::jsonb,
  published_at         TIMESTAMPTZ,
  attempt_count        BIGINT      NOT NULL DEFAULT 0 CHECK (attempt_count >= 0),
  next_attempt_after   TIMESTAMPTZ NOT NULL DEFAULT now(),
  lease_owner          TEXT,
  lease_until          TIMESTAMPTZ,
  last_error           JSONB,
  CONSTRAINT event_outbox_uniq_aggregate_version
    UNIQUE (aggregate_type, aggregate_id, aggregate_version)
);

CREATE INDEX IF NOT EXISTS idx_event_outbox_published_at         ON event_outbox (published_at);
CREATE INDEX IF NOT EXISTS idx_event_outbox_next_attempt_pending ON event_outbox (next_attempt_after, event_sequence) WHERE published_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_event_outbox_lease_visible        ON event_outbox (lease_until)     WHERE published_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_event_outbox_correlation_id       ON event_outbox (correlation_id);
CREATE INDEX IF NOT EXISTS idx_event_outbox_causation_id         ON event_outbox (causation_id);
CREATE INDEX IF NOT EXISTS idx_event_outbox_event_name           ON event_outbox (event_name);

COMMENT ON TABLE event_outbox IS 'Event outbox: events to be published; at-least-once delivery guarantee.';

-- event dead letters
CREATE TABLE IF NOT EXISTS event_dead_letters (
  event_outbox_id     UUID        PRIMARY KEY,
  event_sequence      BIGINT      NOT NULL,
  event_id            UUID        NOT NULL,
  aggregate_type      TEXT        NOT NULL,
  aggregate_id        UUID        NOT NULL,
  aggregate_version   BIGINT      NOT NULL CHECK (aggregate_version > 0),
  event_name          TEXT        NOT NULL,
  payload             JSONB       NOT NULL,
  occurred_at         TIMESTAMPTZ NOT NULL,
  correlation_id      UUID        NOT NULL,
  causation_id        UUID        NOT NULL,
  context             JSONB       NOT NULL,
  published_at        TIMESTAMPTZ,
  attempt_count       BIGINT      NOT NULL CHECK (attempt_count >= 0),
  next_attempt_after  TIMESTAMPTZ NOT NULL,
  lease_owner         TEXT,
  lease_until         TIMESTAMPTZ,
  last_error          JSONB,
  dead_lettered_at    TIMESTAMPTZ NOT NULL
);

-- command_outbox
CREATE TABLE IF NOT EXISTS command_outbox (
  id                   UUID        PRIMARY KEY,
  command_sequence      BIGINT      GENERATED BY DEFAULT AS IDENTITY UNIQUE,
  message_id            UUID        NOT NULL UNIQUE,
  command_name          TEXT        NOT NULL,
  payload               JSONB       NOT NULL,
  correlation_id        UUID        NOT NULL,
  causation_id          UUID        NOT NULL,
  published_at          TIMESTAMPTZ,
  attempt_count         BIGINT      NOT NULL DEFAULT 0 CHECK (attempt_count >= 0),
  next_attempt_after    TIMESTAMPTZ NOT NULL DEFAULT now(),
  lease_owner           TEXT,
  lease_until           TIMESTAMPTZ,
  last_error            JSONB
);

CREATE INDEX IF NOT EXISTS idx_command_outbox_published_at         ON command_outbox (published_at);
CREATE INDEX IF NOT EXISTS idx_command_outbox_next_attempt_pending ON command_outbox (next_attempt_after, command_sequence) WHERE published_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_command_outbox_lease_visible        ON command_outbox (lease_until)     WHERE published_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_command_outbox_correlation_id       ON command_outbox (correlation_id);
CREATE INDEX IF NOT EXISTS idx_command_outbox_causation_id         ON command_outbox (causation_id);
CREATE INDEX IF NOT EXISTS idx_command_outbox_message_id           ON command_outbox (message_id);

COMMENT ON TABLE command_outbox IS 'Command outbox: commands to be dispatched; at-least-once delivery guarantee.';

-- command dead letters
CREATE TABLE IF NOT EXISTS command_dead_letters (
  command_outbox_id    UUID        PRIMARY KEY,
  command_sequence     BIGINT      NOT NULL,
  message_id           UUID        NOT NULL,
  command_name         TEXT        NOT NULL,
  payload              JSONB       NOT NULL,
  correlation_id       UUID        NOT NULL,
  causation_id         UUID        NOT NULL,
  published_at         TIMESTAMPTZ,
  attempt_count        BIGINT      NOT NULL CHECK (attempt_count >= 0),
  next_attempt_after   TIMESTAMPTZ NOT NULL,
  lease_owner          TEXT,
  lease_until          TIMESTAMPTZ,
  last_error           JSONB,
  dead_lettered_at     TIMESTAMPTZ NOT NULL
);

-- saga instances
CREATE TABLE IF NOT EXISTS saga_instances (
  saga_instance_id UUID        PRIMARY KEY,
  saga_name      TEXT        NOT NULL,
  correlation_id UUID        NOT NULL,
  state          JSONB,
  state_version  BIGINT      NOT NULL DEFAULT 0 CHECK (state_version >= 0),
  created_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  succeeded_at   TIMESTAMPTZ,
  failed_at      TIMESTAMPTZ,
  UNIQUE (saga_name, correlation_id),
  CONSTRAINT saga_instances_succeeded_failed_check CHECK (
    NOT (succeeded_at IS NOT NULL AND failed_at IS NOT NULL)
  )
);

CREATE INDEX IF NOT EXISTS idx_saga_instances_in_progress
  ON saga_instances (saga_name, updated_at)
  WHERE succeeded_at IS NULL AND failed_at IS NULL;

-- saga processed events
CREATE TABLE IF NOT EXISTS saga_processed_events (
  saga_name      TEXT        NOT NULL,
  correlation_id UUID        NOT NULL,
  event_id       UUID        NOT NULL,
  processed_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (saga_name, correlation_id, event_id)
);

CREATE INDEX IF NOT EXISTS idx_saga_processed_events_event_id
  ON saga_processed_events (event_id);

-- projection checkpoints
CREATE TABLE IF NOT EXISTS projection_checkpoints (
  projector_name TEXT PRIMARY KEY,
  last_event_sequence BIGINT NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- projector processed events
CREATE TABLE IF NOT EXISTS projector_processed_events (
  projector_name TEXT NOT NULL,
  event_id UUID NOT NULL,
  processed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (projector_name, event_id)
);

CREATE INDEX IF NOT EXISTS idx_projector_processed_events_event_id
  ON projector_processed_events (event_id);

-- idempotency
CREATE TABLE IF NOT EXISTS idempotency (
  message_id    UUID        PRIMARY KEY,
  command_name  TEXT        NOT NULL,
  command_hash  TEXT        NOT NULL,
  output        JSONB,
  error         JSONB,
  started_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  completed_at  TIMESTAMPTZ,
  CONSTRAINT idempotency_output_error_check CHECK (
    (completed_at IS NULL AND output IS NULL AND error IS NULL) OR
    (completed_at IS NOT NULL AND output IS NOT NULL AND error IS NULL) OR
    (completed_at IS NOT NULL AND output IS NULL AND error IS NOT NULL)
  )
);

CREATE INDEX IF NOT EXISTS idx_idempotency_started_at
  ON idempotency (started_at);

CREATE INDEX IF NOT EXISTS idx_idempotency_completed_at
  ON idempotency (completed_at) WHERE completed_at IS NOT NULL;
