mini-prog/backend/migrations/0004_results.sql

BEGIN;

CREATE TABLE session_results (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  session_id UUID NOT NULL UNIQUE REFERENCES game_sessions(id) ON DELETE CASCADE,
  result_status TEXT NOT NULL CHECK (result_status IN ('finished', 'failed', 'cancelled')),
  summary_jsonb JSONB NOT NULL DEFAULT '{}'::jsonb,
  final_duration_sec INTEGER,
  final_score INTEGER,
  completed_controls INTEGER,
  total_controls INTEGER,
  distance_meters NUMERIC(10,2),
  average_speed_kmh NUMERIC(8,3),
  max_heart_rate_bpm INTEGER,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX session_results_result_status_idx ON session_results(result_status);
CREATE INDEX session_results_created_at_idx ON session_results(created_at DESC);

CREATE TRIGGER session_results_set_updated_at
BEFORE UPDATE ON session_results
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

COMMIT;