-- Supercross and Pro Motocross each number their own rounds 1-N (e.g. SX round 8 =
-- Daytona, MX round 8 = Washougal). event_lap_records had no way to tell them apart,
-- so a motocross round's fastest lap could silently overwrite a supercross round's
-- record with the same round number (and vice versa). This adds a series discriminator
-- and widens the uniqueness constraint to (event_round, class, series).

ALTER TABLE event_lap_records
  ADD COLUMN IF NOT EXISTS series TEXT NOT NULL DEFAULT 'sx' CHECK (series IN ('sx', 'mx'));

-- Drop whatever the old UNIQUE(event_round, class) constraint was named (auto-generated
-- names vary), then add the corrected one.
DO $$
DECLARE
  old_constraint_name text;
BEGIN
  SELECT tc.constraint_name INTO old_constraint_name
  FROM information_schema.table_constraints tc
  JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name
  WHERE tc.table_name = 'event_lap_records' AND tc.constraint_type = 'UNIQUE'
  GROUP BY tc.constraint_name
  HAVING array_agg(kcu.column_name::text ORDER BY kcu.column_name) = ARRAY['class', 'event_round'];

  IF old_constraint_name IS NOT NULL THEN
    EXECUTE format('ALTER TABLE event_lap_records DROP CONSTRAINT %I', old_constraint_name);
  END IF;
END $$;

ALTER TABLE event_lap_records
  ADD CONSTRAINT event_lap_records_round_class_series_key UNIQUE (event_round, class, series);

CREATE INDEX IF NOT EXISTS idx_event_lap_records_round_class_series
  ON event_lap_records(event_round, class, series);