PostgreSQL Schema and Migrations for Spades Online

psql $DATABASE_URL -f db/migrations/001_create_players.sql
psql $DATABASE_URL -f db/migrations/002_create_profile_and_games.sql
psql $DATABASE_URL -f db/migrations/003_create_password_reset_tokens.sql
psql $DATABASE_URL -f db/migrations/004_create_friendships.sql
psql $DATABASE_URL -f db/migrations/005_create_player_blocks.sql

-- Migration 001: players and email verification tokens
-- Run once against the primary database before starting the server.

CREATE TABLE IF NOT EXISTS players (
  id            UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
  email         VARCHAR(255) UNIQUE NOT NULL,
  username      VARCHAR(50)  UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  is_verified   BOOLEAN      NOT NULL DEFAULT FALSE,
  created_at    TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS email_verification_tokens (
  token      UUID        PRIMARY KEY,
  player_id  UUID        NOT NULL REFERENCES players(id) ON DELETE CASCADE,
  expires_at TIMESTAMPTZ NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Index for fast token lookup during email verification
CREATE INDEX IF NOT EXISTS idx_evt_player_id ON email_verification_tokens(player_id);

-- Migration 002: player profiles, games, and game_players
-- Run once against the primary database before starting the server.

CREATE TABLE IF NOT EXISTS player_profiles (
  player_id   UUID        PRIMARY KEY REFERENCES players(id) ON DELETE CASCADE,
  avatar_icon SMALLINT    NOT NULL DEFAULT 1,
  felt_color  VARCHAR(20) NOT NULL DEFAULT 'green',
  card_back   VARCHAR(20) NOT NULL DEFAULT 'standard-red',
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS games (
  id           UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  completed_at TIMESTAMPTZ,
  score_ns     INTEGER     NOT NULL DEFAULT 0,
  score_ew     INTEGER     NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS game_players (
  game_id   UUID       NOT NULL REFERENCES games(id) ON DELETE CASCADE,
  player_id UUID       NOT NULL REFERENCES players(id) ON DELETE CASCADE,
  seat      VARCHAR(10) NOT NULL,
  team      CHAR(2)    NOT NULL,
  won       BOOLEAN    NOT NULL,
  PRIMARY KEY (game_id, player_id)
);

-- Index for fast profile lookups by player
CREATE INDEX IF NOT EXISTS idx_game_players_player_id ON game_players(player_id);

CREATE TABLE IF NOT EXISTS password_reset_tokens (
  token TEXT PRIMARY KEY,
  player_id UUID NOT NULL REFERENCES players(id) ON DELETE CASCADE,
  expires_at TIMESTAMPTZ NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_password_reset_tokens_player_id
  ON password_reset_tokens (player_id);

-- Migration: create or migrate friendships table
--
-- The table was originally created (issue #600) with columns player_id / friend_id.
-- Issue #607 renamed them to requester_id / addressee_id and added updated_at.
-- This migration handles both fresh installs and upgrades from the old schema.

DO $$
BEGIN
  -- If the table does not exist, create it with the new schema.
  IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'friendships') THEN
    CREATE TABLE friendships (
      id BIGSERIAL PRIMARY KEY,
      requester_id UUID NOT NULL REFERENCES players(id) ON DELETE CASCADE,
      addressee_id UUID NOT NULL REFERENCES players(id) ON DELETE CASCADE,
      status VARCHAR(20) NOT NULL DEFAULT 'pending',
      created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      CONSTRAINT friendships_pair_unique UNIQUE (requester_id, addressee_id)
    );
  ELSE
    -- Migrate from old schema if needed: rename columns
    IF EXISTS (
      SELECT 1 FROM information_schema.columns
      WHERE table_name = 'friendships' AND column_name = 'player_id'
    ) THEN
      ALTER TABLE friendships RENAME COLUMN player_id TO requester_id;
      ALTER TABLE friendships RENAME COLUMN friend_id TO addressee_id;
    END IF;

    -- Add updated_at column if missing
    IF NOT EXISTS (
      SELECT 1 FROM information_schema.columns
      WHERE table_name = 'friendships' AND column_name = 'updated_at'
    ) THEN
      ALTER TABLE friendships ADD COLUMN updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW();
    END IF;

    -- Drop old unique constraint if it exists and add new one
    IF EXISTS (
      SELECT 1 FROM information_schema.table_constraints
      WHERE table_name = 'friendships' AND constraint_name = 'friendships_player_id_friend_id_key'
    ) THEN
      ALTER TABLE friendships DROP CONSTRAINT friendships_player_id_friend_id_key;
    END IF;

    IF NOT EXISTS (
      SELECT 1 FROM information_schema.table_constraints
      WHERE table_name = 'friendships' AND constraint_name = 'friendships_pair_unique'
    ) THEN
      ALTER TABLE friendships ADD CONSTRAINT friendships_pair_unique UNIQUE (requester_id, addressee_id);
    END IF;
  END IF;
END
$$;

-- Bidirectional unique index: prevents duplicate friendships regardless of direction
-- (e.g. Alice->Bob and Bob->Alice). The directional UNIQUE constraint above only
-- prevents exact duplicates; this index covers the reverse-pair race condition.
CREATE UNIQUE INDEX IF NOT EXISTS friendships_pair_unique_bidi
ON friendships (LEAST(requester_id, addressee_id), GREATEST(requester_id, addressee_id));

CREATE TABLE IF NOT EXISTS player_blocks (
  id BIGSERIAL PRIMARY KEY,
  blocker_id UUID NOT NULL REFERENCES players(id) ON DELETE CASCADE,
  blocked_id UUID NOT NULL REFERENCES players(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(blocker_id, blocked_id)
);