CREATE TABLE IF NOT EXISTS event_outbox (
  id SERIAL PRIMARY KEY,
  event_id VARCHAR(64) NOT NULL UNIQUE,
  event_type VARCHAR(128) NOT NULL,
  event_version INTEGER NOT NULL DEFAULT 1,
  producer_service VARCHAR(128) NOT NULL,
  entity_type VARCHAR(128) NOT NULL,
  entity_id VARCHAR(128) NOT NULL,
  correlation_id VARCHAR(128) NULL,
  routing_key VARCHAR(128) NOT NULL,
  payload_json TEXT NOT NULL DEFAULT '{}',
  status VARCHAR(32) NOT NULL,
  attempt_count INTEGER NOT NULL DEFAULT 0,
  last_error TEXT NULL,
  available_at VARCHAR(64) NOT NULL,
  published_at VARCHAR(64) NULL,
  created_at VARCHAR(64) NOT NULL,
  updated_at VARCHAR(64) NOT NULL
);
CREATE INDEX IF NOT EXISTS ix_event_outbox_event_id ON event_outbox (event_id);
CREATE INDEX IF NOT EXISTS ix_event_outbox_event_type ON event_outbox (event_type);
CREATE INDEX IF NOT EXISTS ix_event_outbox_status ON event_outbox (status);
CREATE INDEX IF NOT EXISTS ix_event_outbox_available_at ON event_outbox (available_at);
CREATE INDEX IF NOT EXISTS ix_event_outbox_entity_type ON event_outbox (entity_type);
CREATE INDEX IF NOT EXISTS ix_event_outbox_entity_id ON event_outbox (entity_id);

CREATE TABLE IF NOT EXISTS event_inbox (
  id SERIAL PRIMARY KEY,
  consumer_name VARCHAR(128) NOT NULL,
  event_id VARCHAR(64) NOT NULL,
  event_type VARCHAR(128) NOT NULL,
  processed_at VARCHAR(64) NOT NULL,
  status VARCHAR(32) NOT NULL,
  notes TEXT NULL
);
CREATE INDEX IF NOT EXISTS ix_event_inbox_consumer_name ON event_inbox (consumer_name);
CREATE INDEX IF NOT EXISTS ix_event_inbox_event_id ON event_inbox (event_id);
CREATE INDEX IF NOT EXISTS ix_event_inbox_event_type ON event_inbox (event_type);
CREATE UNIQUE INDEX IF NOT EXISTS uq_event_inbox_consumer_event ON event_inbox (consumer_name, event_id);

CREATE TABLE IF NOT EXISTS reporting_event_log (
  id SERIAL PRIMARY KEY,
  event_id VARCHAR(64) NOT NULL,
  event_type VARCHAR(128) NOT NULL,
  queue_id VARCHAR(64) NULL,
  channel VARCHAR(32) NULL,
  interaction_id VARCHAR(64) NULL,
  payload_json TEXT NOT NULL DEFAULT '{}',
  created_at VARCHAR(64) NOT NULL
);
CREATE INDEX IF NOT EXISTS ix_reporting_event_log_event_id ON reporting_event_log (event_id);
CREATE INDEX IF NOT EXISTS ix_reporting_event_log_event_type ON reporting_event_log (event_type);
CREATE INDEX IF NOT EXISTS ix_reporting_event_log_queue_id ON reporting_event_log (queue_id);
CREATE INDEX IF NOT EXISTS ix_reporting_event_log_channel ON reporting_event_log (channel);
CREATE INDEX IF NOT EXISTS ix_reporting_event_log_interaction_id ON reporting_event_log (interaction_id);