-- migrate:up

CREATE TABLE outbox_messages (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id CHAR(26) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  aggregate_type VARCHAR(80) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  aggregate_reference VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  topic VARCHAR(120) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  deduplication_key VARCHAR(191) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  payload JSON NOT NULL,
  status ENUM('pending', 'processing', 'published', 'failed', 'cancelled') NOT NULL DEFAULT 'pending',
  attempt_count SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  maximum_attempts SMALLINT UNSIGNED NOT NULL DEFAULT 5,
  available_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  locked_by VARCHAR(120) CHARACTER SET ascii COLLATE ascii_bin NULL,
  locked_until DATETIME(6) NULL,
  published_at DATETIME(6) NULL,
  last_error_code VARCHAR(120) CHARACTER SET ascii COLLATE ascii_bin NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_outbox_messages_public_id (public_id),
  UNIQUE KEY uq_outbox_messages_deduplication (deduplication_key),
  KEY idx_outbox_messages_queue (status, available_at, locked_until, id),
  KEY idx_outbox_messages_aggregate (aggregate_type, aggregate_reference, created_at),
  CONSTRAINT chk_outbox_messages_attempts CHECK (maximum_attempts > 0 AND attempt_count <= maximum_attempts)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE failed_jobs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id CHAR(26) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  queue_name VARCHAR(120) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  job_name VARCHAR(120) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  job_reference VARCHAR(191) CHARACTER SET ascii COLLATE ascii_bin NULL,
  payload_redacted JSON NULL,
  error_code VARCHAR(120) CHARACTER SET ascii COLLATE ascii_bin NULL,
  error_message VARCHAR(1000) NULL,
  stack_fingerprint BINARY(32) NULL,
  attempt_count SMALLINT UNSIGNED NOT NULL,
  failed_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  resolved_at DATETIME(6) NULL,
  resolution ENUM('retried', 'discarded', 'fixed', 'duplicate') NULL,
  resolved_by_admin_id BIGINT UNSIGNED NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_failed_jobs_public_id (public_id),
  KEY idx_failed_jobs_queue_time (queue_name, resolved_at, failed_at),
  CONSTRAINT fk_failed_jobs_resolver FOREIGN KEY (resolved_by_admin_id) REFERENCES admins (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE scheduler_locks (
  lock_key VARCHAR(120) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  owner_id VARCHAR(120) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  acquired_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  expires_at DATETIME(6) NOT NULL,
  heartbeat_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (lock_key),
  KEY idx_scheduler_locks_expiry (expires_at),
  CONSTRAINT chk_scheduler_locks_expiry CHECK (expires_at > acquired_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE job_runs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_id CHAR(26) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  job_name VARCHAR(120) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  run_key VARCHAR(191) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  status ENUM('running', 'completed', 'failed', 'skipped') NOT NULL DEFAULT 'running',
  owner_id VARCHAR(120) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  metrics JSON NULL,
  error_code VARCHAR(120) CHARACTER SET ascii COLLATE ascii_bin NULL,
  started_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  finished_at DATETIME(6) NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_job_runs_public_id (public_id),
  UNIQUE KEY uq_job_runs_key (run_key),
  KEY idx_job_runs_status_time (status, started_at, id),
  CONSTRAINT chk_job_runs_finish CHECK (finished_at IS NULL OR finished_at >= started_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- migrate:down

DROP TABLE IF EXISTS job_runs;
DROP TABLE IF EXISTS scheduler_locks;
DROP TABLE IF EXISTS failed_jobs;
DROP TABLE IF EXISTS outbox_messages;