-- Migration for comprehensive event history tracking
-- This migration creates tables to store historical race data including:
-- - Event snapshots (periodic saves of live data)
-- - Race results (final results for each race)
-- - Lap times (all lap times for all riders)

-- =================================================================
-- EVENT SNAPSHOTS TABLE
-- Stores periodic snapshots of live race data for historical analysis
-- =================================================================
CREATE TABLE IF NOT EXISTS event_snapshots (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  event_id TEXT NOT NULL,
  event_round INTEGER,
  event_name TEXT,
  snapshot_type TEXT NOT NULL CHECK (snapshot_type IN ('race', 'practice', 'qualifying', 'heats', 'lcq', 'main_event')),
  class TEXT CHECK (class IN ('250SX', '450SX', '250SMX', '450SMX')),
  session_name TEXT,
  snapshot_data JSONB NOT NULL, -- Complete race data at the time of snapshot
  race_status TEXT, -- e.g., 'live', 'completed', 'upcoming'
  lap_count INTEGER,
  riders_count INTEGER,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc', NOW()),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc', NOW())
);

-- =================================================================
-- RACE RESULTS TABLE
-- Stores final results for each race/session
-- =================================================================
CREATE TABLE IF NOT EXISTS race_results (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  event_id TEXT NOT NULL,
  event_round INTEGER,
  event_name TEXT,
  event_date DATE,
  session_type TEXT NOT NULL CHECK (session_type IN ('practice', 'qualifying', 'heat_1', 'heat_2', 'lcq', 'main_event')),
  class TEXT NOT NULL CHECK (class IN ('250SX', '450SX', '250SMX', '450SMX')),
  
  -- Rider information
  rider_number TEXT NOT NULL,
  rider_name TEXT NOT NULL,
  manufacturer TEXT,
  
  -- Result data
  position INTEGER NOT NULL,
  finish_status TEXT DEFAULT 'finished' CHECK (finish_status IN ('finished', 'dnf', 'dns', 'dsq')),
  total_laps INTEGER,
  best_lap_time TEXT,
  total_time TEXT,
  gap_to_leader TEXT,
  gap_to_previous TEXT,
  points_earned INTEGER,
  
  -- Additional metadata
  metadata JSONB, -- Store any additional race-specific data
  
  created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc', NOW()),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc', NOW()),
  
  -- Unique constraint: one result per rider per session
  UNIQUE(event_id, session_type, class, rider_number)
);

-- =================================================================
-- LAP TIMES TABLE
-- Stores individual lap times for all riders across all sessions
-- =================================================================
CREATE TABLE IF NOT EXISTS lap_times (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  event_id TEXT NOT NULL,
  event_round INTEGER,
  event_name TEXT,
  session_type TEXT NOT NULL CHECK (session_type IN ('practice', 'qualifying', 'heat_1', 'heat_2', 'lcq', 'main_event')),
  class TEXT NOT NULL CHECK (class IN ('250SX', '450SX', '250SMX', '450SMX')),
  
  -- Rider information
  rider_number TEXT NOT NULL,
  rider_name TEXT NOT NULL,
  
  -- Lap data
  lap_number INTEGER NOT NULL,
  lap_time TEXT NOT NULL, -- Format: "MM:SS.mmm" or "SS.mmm"
  lap_time_ms BIGINT, -- Lap time in milliseconds for easier sorting/analysis
  position_at_lap INTEGER,
  gap_to_leader TEXT,
  is_fastest_lap BOOLEAN DEFAULT false,
  
  -- Metadata
  recorded_at TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc', NOW()),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc', NOW()),
  
  -- Unique constraint: one lap time per rider per lap per session
  UNIQUE(event_id, session_type, class, rider_number, lap_number)
);

-- =================================================================
-- ENABLE ROW LEVEL SECURITY
-- =================================================================
ALTER TABLE event_snapshots ENABLE ROW LEVEL SECURITY;
ALTER TABLE race_results ENABLE ROW LEVEL SECURITY;
ALTER TABLE lap_times ENABLE ROW LEVEL SECURITY;

-- =================================================================
-- RLS POLICIES - Public read access, API write access
-- =================================================================

-- Event Snapshots Policies
CREATE POLICY "Event snapshots are viewable by everyone"
  ON event_snapshots FOR SELECT
  USING (true);

CREATE POLICY "Anyone can insert event snapshots"
  ON event_snapshots FOR INSERT
  WITH CHECK (true);

CREATE POLICY "Anyone can update event snapshots"
  ON event_snapshots FOR UPDATE
  USING (true);

-- Race Results Policies
CREATE POLICY "Race results are viewable by everyone"
  ON race_results FOR SELECT
  USING (true);

CREATE POLICY "Anyone can insert race results"
  ON race_results FOR INSERT
  WITH CHECK (true);

CREATE POLICY "Anyone can update race results"
  ON race_results FOR UPDATE
  USING (true);

-- Lap Times Policies
CREATE POLICY "Lap times are viewable by everyone"
  ON lap_times FOR SELECT
  USING (true);

CREATE POLICY "Anyone can insert lap times"
  ON lap_times FOR INSERT
  WITH CHECK (true);

CREATE POLICY "Anyone can update lap times"
  ON lap_times FOR UPDATE
  USING (true);

-- =================================================================
-- INDEXES FOR PERFORMANCE
-- =================================================================

-- Event Snapshots Indexes
CREATE INDEX IF NOT EXISTS idx_event_snapshots_event_id 
  ON event_snapshots(event_id);
CREATE INDEX IF NOT EXISTS idx_event_snapshots_round_class 
  ON event_snapshots(event_round, class);
CREATE INDEX IF NOT EXISTS idx_event_snapshots_type 
  ON event_snapshots(snapshot_type);
CREATE INDEX IF NOT EXISTS idx_event_snapshots_created 
  ON event_snapshots(created_at DESC);

-- Race Results Indexes
CREATE INDEX IF NOT EXISTS idx_race_results_event_id 
  ON race_results(event_id);
CREATE INDEX IF NOT EXISTS idx_race_results_round_class 
  ON race_results(event_round, class);
CREATE INDEX IF NOT EXISTS idx_race_results_session 
  ON race_results(session_type, class);
CREATE INDEX IF NOT EXISTS idx_race_results_rider 
  ON race_results(rider_number, class);
CREATE INDEX IF NOT EXISTS idx_race_results_position 
  ON race_results(event_id, session_type, class, position);

-- Lap Times Indexes
CREATE INDEX IF NOT EXISTS idx_lap_times_event_id 
  ON lap_times(event_id);
CREATE INDEX IF NOT EXISTS idx_lap_times_round_class 
  ON lap_times(event_round, class);
CREATE INDEX IF NOT EXISTS idx_lap_times_session 
  ON lap_times(session_type, class);
CREATE INDEX IF NOT EXISTS idx_lap_times_rider 
  ON lap_times(rider_number, class);
CREATE INDEX IF NOT EXISTS idx_lap_times_fastest 
  ON lap_times(event_id, session_type, class) WHERE is_fastest_lap = true;
CREATE INDEX IF NOT EXISTS idx_lap_times_ms 
  ON lap_times(lap_time_ms) WHERE lap_time_ms IS NOT NULL;

-- =================================================================
-- FUNCTIONS FOR AUTOMATIC TIMESTAMP UPDATES
-- =================================================================

-- Event Snapshots
CREATE OR REPLACE FUNCTION update_event_snapshots_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = TIMEZONE('utc', NOW());
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_event_snapshots_timestamp
  BEFORE UPDATE ON event_snapshots
  FOR EACH ROW
  EXECUTE FUNCTION update_event_snapshots_updated_at();

-- Race Results
CREATE OR REPLACE FUNCTION update_race_results_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = TIMEZONE('utc', NOW());
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_race_results_timestamp
  BEFORE UPDATE ON race_results
  FOR EACH ROW
  EXECUTE FUNCTION update_race_results_updated_at();

-- Lap Times
CREATE OR REPLACE FUNCTION update_lap_times_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = TIMEZONE('utc', NOW());
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_lap_times_timestamp
  BEFORE UPDATE ON lap_times
  FOR EACH ROW
  EXECUTE FUNCTION update_lap_times_updated_at();

-- =================================================================
-- HELPER FUNCTION: Convert lap time string to milliseconds
-- =================================================================
CREATE OR REPLACE FUNCTION lap_time_to_ms(lap_time TEXT)
RETURNS BIGINT AS $$
DECLARE
  parts TEXT[];
  minutes INTEGER := 0;
  seconds NUMERIC;
BEGIN
  -- Handle formats like "1:23.456" or "23.456"
  IF lap_time LIKE '%:%' THEN
    parts := string_to_array(lap_time, ':');
    minutes := parts[1]::INTEGER;
    seconds := parts[2]::NUMERIC;
  ELSE
    seconds := lap_time::NUMERIC;
  END IF;
  
  RETURN (minutes * 60 * 1000 + (seconds * 1000))::BIGINT;
EXCEPTION
  WHEN OTHERS THEN
    RETURN NULL;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- =================================================================
-- TRIGGER: Automatically calculate lap_time_ms when lap_time is set
-- =================================================================
CREATE OR REPLACE FUNCTION calculate_lap_time_ms()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.lap_time IS NOT NULL THEN
    NEW.lap_time_ms := lap_time_to_ms(NEW.lap_time);
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER calculate_lap_time_ms_trigger
  BEFORE INSERT OR UPDATE OF lap_time ON lap_times
  FOR EACH ROW
  EXECUTE FUNCTION calculate_lap_time_ms();