setup.sql

-- Neural Reflex — Canonical Schema Reference
-- This file mirrors the migration at supabase/migrations/20260827000000_add_normalized_name.sql.
-- Do NOT run this directly on a database that already has the migration applied —
-- it will DROP the scores table and remove all data.
-- The migrations/ folder is the authoritative source; this file is documentation only.

-- ─────────────────────────────────────────────────────────────────────────────
-- Tear down (if resetting from scratch)
-- ─────────────────────────────────────────────────────────────────────────────

DROP TABLE IF EXISTS scores CASCADE;
DROP FUNCTION IF EXISTS normalize_display_name() CASCADE;
DROP FUNCTION IF EXISTS set_updated_at() CASCADE;

-- ─────────────────────────────────────────────────────────────────────────────
-- scores table
-- One row per player (identified by normalized_name).
-- ─────────────────────────────────────────────────────────────────────────────

CREATE TABLE scores (
  id               BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  display_name     TEXT    NOT NULL,
  normalized_name  TEXT    NOT NULL,
  reaction_time_ms INTEGER NOT NULL,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at       TIMESTAMPTZ NOT NULL DEFAULT now(),

  CONSTRAINT scores_normalized_name_unique
    UNIQUE (normalized_name),

  CONSTRAINT chk_display_name_length
    CHECK (char_length(display_name) BETWEEN 1 AND 20),

  CONSTRAINT chk_reaction_time
    CHECK (reaction_time_ms BETWEEN 50 AND 5000)
);

CREATE INDEX idx_scores_reaction_time
  ON scores (reaction_time_ms ASC);

-- ─────────────────────────────────────────────────────────────────────────────
-- Triggers
-- ─────────────────────────────────────────────────────────────────────────────

CREATE OR REPLACE FUNCTION normalize_display_name()
RETURNS TRIGGER AS $$
BEGIN
  NEW.normalized_name :=
    LOWER(TRIM(REGEXP_REPLACE(NEW.display_name, '\s+', ' ', 'g')));
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER normalize_display_name_trigger
  BEFORE INSERT OR UPDATE ON scores
  FOR EACH ROW
  EXECUTE FUNCTION normalize_display_name();

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_updated_at_trigger
  BEFORE UPDATE ON scores
  FOR EACH ROW
  EXECUTE FUNCTION set_updated_at();

-- ─────────────────────────────────────────────────────────────────────────────
-- Row Level Security
-- ─────────────────────────────────────────────────────────────────────────────

ALTER TABLE scores ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Public read access"
  ON scores FOR SELECT
  USING (true);

CREATE POLICY "Insert with validation"
  ON scores FOR INSERT
  WITH CHECK (
    char_length(display_name) BETWEEN 1 AND 20
    AND reaction_time_ms BETWEEN 50 AND 5000
  );

CREATE POLICY "Update score"
  ON scores FOR UPDATE
  USING (true)
  WITH CHECK (
    char_length(display_name) BETWEEN 1 AND 20
    AND reaction_time_ms BETWEEN 50 AND 5000
  );

CREATE POLICY "No deletes"
  ON scores FOR DELETE
  USING (false);