-- 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.';

-- unique key reservations
CREATE TABLE IF NOT EXISTS unique_key_reservations (
  id               UUID PRIMARY KEY,
  aggregate_type   TEXT NOT NULL,
  owner_aggregate_id UUID NOT NULL,
  namespace        TEXT NOT NULL,
  normalized_value TEXT NOT NULL,

  UNIQUE (aggregate_type, namespace, normalized_value)
);

CREATE INDEX IF NOT EXISTS idx_unique_key_reservations_owner
  ON unique_key_reservations (aggregate_type, owner_aggregate_id);

COMMENT ON TABLE unique_key_reservations IS 'Current unique-value reservations keyed by aggregate owner.';

-- 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 (
  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 (
  id             UUID        PRIMARY KEY,
  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 (
  id                UUID        PRIMARY KEY,
  projector_name     TEXT        NOT NULL UNIQUE,
  last_event_sequence BIGINT NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- projector processed events
CREATE TABLE IF NOT EXISTS projector_processed_events (
  id             UUID        PRIMARY KEY,
  projector_name TEXT        NOT NULL,
  event_id       UUID        NOT NULL,
  processed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (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 (
  id            UUID        PRIMARY KEY,
  message_id    UUID        NOT NULL UNIQUE,
  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;

-- resource response cache
CREATE TABLE IF NOT EXISTS resource_response_cache (
  id               UUID        PRIMARY KEY,
  url              TEXT        NOT NULL UNIQUE,
  data             BYTEA       NOT NULL,
  fetched_at       TIMESTAMPTZ NOT NULL,
  expires_at       TIMESTAMPTZ NOT NULL,
  last_modified_at TIMESTAMPTZ,
  entity_tag       TEXT
);

CREATE INDEX IF NOT EXISTS idx_resource_response_cache_expires_at
  ON resource_response_cache (expires_at);

-- oidc login attempts
CREATE TABLE IF NOT EXISTS oidc_login_attempts (
  id                 UUID        PRIMARY KEY,
  state              UUID        NOT NULL UNIQUE,
  nonce              UUID        NOT NULL,
  pkce_code_verifier TEXT,
  created_at         TIMESTAMPTZ NOT NULL,
  expires_at         TIMESTAMPTZ NOT NULL,
  consumed_at        TIMESTAMPTZ,
  CONSTRAINT oidc_login_attempts_expires_check CHECK (expires_at >= created_at)
);

CREATE INDEX IF NOT EXISTS idx_oidc_login_attempts_expires_at
  ON oidc_login_attempts (expires_at);

-- relationships (Aggregate × ReBAC)
CREATE TABLE IF NOT EXISTS relationships (
  id                     UUID        PRIMARY KEY,
  aggregate_type         TEXT        NOT NULL,
  aggregate_id           UUID        NOT NULL,
  relation               TEXT        NOT NULL,

  subject_aggregate_type TEXT        NOT NULL,
  subject_aggregate_id   UUID,
  subject_relation       TEXT,
  subject_is_wildcard    BOOLEAN     NOT NULL DEFAULT false,

  created_at             TIMESTAMPTZ NOT NULL DEFAULT now(),

  CONSTRAINT relationships_subject_check CHECK (
    (
      subject_is_wildcard = true
      AND subject_aggregate_id IS NULL
      AND subject_relation IS NULL
    )
    OR
    (
      subject_is_wildcard = false
      AND subject_aggregate_id IS NOT NULL
      AND subject_relation IS NULL
    )
    OR
    (
      subject_is_wildcard = false
      AND subject_aggregate_id IS NOT NULL
      AND subject_relation IS NOT NULL
    )
  )
);

CREATE INDEX IF NOT EXISTS idx_relationships_aggregate_relation
  ON relationships (aggregate_type, aggregate_id, relation);

CREATE UNIQUE INDEX IF NOT EXISTS idx_relationships_direct_uniq
  ON relationships (
    aggregate_type, aggregate_id, relation,
    subject_aggregate_type, subject_aggregate_id
  )
  WHERE subject_is_wildcard = false AND subject_relation IS NULL;

CREATE UNIQUE INDEX IF NOT EXISTS idx_relationships_subject_set_uniq
  ON relationships (
    aggregate_type, aggregate_id, relation,
    subject_aggregate_type, subject_aggregate_id, subject_relation
  )
  WHERE subject_is_wildcard = false AND subject_relation IS NOT NULL;

CREATE UNIQUE INDEX IF NOT EXISTS idx_relationships_wildcard_uniq
  ON relationships (
    aggregate_type, aggregate_id, relation,
    subject_aggregate_type
  )
  WHERE subject_is_wildcard = true;

CREATE INDEX IF NOT EXISTS idx_relationships_subject_direct
  ON relationships (
    subject_aggregate_type, subject_aggregate_id, relation,
    aggregate_type, aggregate_id
  )
  WHERE subject_is_wildcard = false;

CREATE INDEX IF NOT EXISTS idx_relationships_subject_wildcard
  ON relationships (
    subject_aggregate_type, relation,
    aggregate_type, aggregate_id
  )
  WHERE subject_is_wildcard = true;

-- auth token revocations
CREATE TABLE IF NOT EXISTS auth_token_revocations (
  id         UUID        PRIMARY KEY,
  token_id   UUID        NOT NULL UNIQUE,
  expires_at TIMESTAMPTZ NOT NULL,
  revoked_at TIMESTAMPTZ NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_auth_token_revocations_expires_at
  ON auth_token_revocations (expires_at);

COMMENT ON TABLE auth_token_revocations IS 'Per-token revocations keyed by auth token id.';

CREATE TABLE IF NOT EXISTS auth_token_revocation_cutoffs (
  id                     UUID        PRIMARY KEY,
  subject_aggregate_type TEXT        NOT NULL,
  subject_aggregate_id   UUID        NOT NULL,
  revoke_before          TIMESTAMPTZ NOT NULL,
  updated_at             TIMESTAMPTZ NOT NULL,

  UNIQUE (subject_aggregate_type, subject_aggregate_id)
);

COMMENT ON TABLE auth_token_revocation_cutoffs IS 'Subject-wide revocation cutoffs for auth tokens.';

CREATE TABLE IF NOT EXISTS auth_token_exchange_codes (
  id                    UUID        PRIMARY KEY,
  code_hash             TEXT        NOT NULL UNIQUE,
  code_challenge_method TEXT,
  code_challenge        TEXT,
  encrypted_grant       BYTEA       NOT NULL,
  created_at            TIMESTAMPTZ NOT NULL,
  expires_at            TIMESTAMPTZ NOT NULL,
  consumed_at           TIMESTAMPTZ,
  CONSTRAINT auth_token_exchange_codes_protection_check CHECK (
    (
      code_challenge_method IS NULL
      AND code_challenge IS NULL
    )
    OR
    (
      code_challenge_method IS NOT NULL
      AND code_challenge IS NOT NULL
    )
  ),
  CONSTRAINT auth_token_exchange_codes_expires_check CHECK (expires_at >= created_at)
);

CREATE INDEX IF NOT EXISTS idx_auth_token_exchange_codes_expires_at
  ON auth_token_exchange_codes (expires_at);

COMMENT ON TABLE auth_token_exchange_codes IS 'Encrypted one-time exchange codes for issuing auth tokens.';
