-- Migration 096: One-time scheduled messages
-- Lightweight table for single-delivery messages at a specific datetime.
-- Rows are deleted automatically by the job after successful send.

CREATE TABLE IF NOT EXISTS scheduled_one_time_messages (
  id                UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  message           TEXT        NOT NULL,
  target_room_token TEXT        NOT NULL,
  target_room_name  TEXT,
  send_at           TIMESTAMPTZ NOT NULL,
  created_by        UUID        REFERENCES users(id) ON DELETE SET NULL,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_sotm_send_at
  ON scheduled_one_time_messages(send_at);
