-- Phase 5 append-only history, moderation, restore, close, snapshot, and export records.

CREATE TABLE IF NOT EXISTS history_events (
  id CHAR(36) PRIMARY KEY,
  wedding_id CHAR(36) NOT NULL,
  sequence BIGINT UNSIGNED NOT NULL,
  state_version BIGINT UNSIGNED NOT NULL,
  type VARCHAR(80) NOT NULL,
  payload JSON NOT NULL,
  occurred_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_history_sequence (wedding_id, sequence)
);

CREATE TABLE IF NOT EXISTS symbol_occupant_versions (
  id CHAR(36) PRIMARY KEY,
  wedding_id CHAR(36) NOT NULL,
  symbol_id INT NOT NULL,
  occupant JSON NOT NULL,
  previous_occupant JSON NULL,
  next_occupant JSON NULL,
  source_event_id CHAR(36) NULL,
  replacement_reason VARCHAR(80) NOT NULL,
  started_at TIMESTAMP NOT NULL,
  ended_at TIMESTAMP NULL
);

CREATE TABLE IF NOT EXISTS moderation_actions (
  id CHAR(36) PRIMARY KEY,
  wedding_id CHAR(36) NOT NULL,
  administrator_id VARCHAR(191) NOT NULL,
  action VARCHAR(80) NOT NULL,
  target JSON NOT NULL,
  before_ref JSON NULL,
  after_ref JSON NULL,
  reason TEXT NOT NULL,
  request_id VARCHAR(191) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS final_snapshots (
  id CHAR(36) PRIMARY KEY,
  wedding_id CHAR(36) NOT NULL,
  snapshot JSON NOT NULL,
  manifest_hash CHAR(64) NOT NULL,
  immutable TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_final_snapshot_wedding (wedding_id)
);

CREATE TABLE IF NOT EXISTS export_packages (
  id CHAR(36) PRIMARY KEY,
  wedding_id CHAR(36) NOT NULL,
  manifest JSON NOT NULL,
  checksum_manifest JSON NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);