-- Mthimkhulu Welanga Organisation — message storage (Cloudflare D1)
-- Apply once per database:
--   npx wrangler d1 execute mw-messages --remote --file=schema.sql
-- or paste this into the D1 console in the Cloudflare dashboard.

CREATE TABLE IF NOT EXISTS submissions (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  ref         TEXT NOT NULL UNIQUE,
  type        TEXT NOT NULL,
  data        TEXT NOT NULL,
  page        TEXT,
  created_at  TEXT NOT NULL,
  received_at TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_submissions_received
  ON submissions (received_at DESC);

-- Only hashed IPs and timestamps: used for rate limiting, never joined to a
-- message. Old rows are deleted automatically by the submit function.
CREATE TABLE IF NOT EXISTS rate_limits (
  ip_hash TEXT NOT NULL,
  ts      INTEGER NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_rate_limits_ip
  ON rate_limits (ip_hash, ts);