-- Phase 3 durable realtime event history and transient outbox.
-- MySQL 8.x reference migration. FileDatabase scaffold mirrors this shape locally.

CREATE TABLE IF NOT EXISTS realtime_events (
  id CHAR(36) PRIMARY KEY,
  wedding_id CHAR(36) NOT NULL,
  sequence BIGINT UNSIGNED NOT NULL,
  state_version BIGINT UNSIGNED NOT NULL,
  event_type VARCHAR(64) NOT NULL,
  payload JSON NOT NULL,
  occurred_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_realtime_events_wedding_sequence (wedding_id, sequence)
);

CREATE TABLE IF NOT EXISTS realtime_outbox (
  id CHAR(36) PRIMARY KEY,
  realtime_event_id CHAR(36) NOT NULL,
  wedding_id CHAR(36) NOT NULL,
  publish_status ENUM('PENDING','PUBLISHED','FAILED') NOT NULL DEFAULT 'PENDING',
  attempts INT UNSIGNED NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  published_at TIMESTAMP NULL,
  FOREIGN KEY (realtime_event_id) REFERENCES realtime_events(id)
);