-- Event lap records table for tracking fastest laps across all users
CREATE TABLE IF NOT EXISTS event_lap_records (
  id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
  event_round INTEGER NOT NULL,
  class TEXT NOT NULL CHECK (class IN ('250SX', '450SX')),
  lap_time TEXT NOT NULL,
  rider_name TEXT NOT NULL,
  rider_number TEXT NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc', NOW()),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc', NOW()),
  UNIQUE(event_round, class)
);

-- Enable Row Level Security
ALTER TABLE event_lap_records ENABLE ROW LEVEL SECURITY;

-- Public read access (everyone can see the records)
CREATE POLICY "Event lap records are viewable by everyone"
  ON event_lap_records FOR SELECT
  USING (true);

-- Only allow inserts/updates through the API (authenticated users)
CREATE POLICY "Authenticated users can insert event lap records"
  ON event_lap_records FOR INSERT
  TO authenticated
  WITH CHECK (true);

CREATE POLICY "Authenticated users can update event lap records"
  ON event_lap_records FOR UPDATE
  TO authenticated
  USING (true);

-- Create index for faster lookups
CREATE INDEX IF NOT EXISTS idx_event_lap_records_round_class 
  ON event_lap_records(event_round, class);

-- Function to automatically update updated_at timestamp
CREATE OR REPLACE FUNCTION update_event_lap_records_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = TIMEZONE('utc', NOW());
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger to call the function
CREATE TRIGGER update_event_lap_records_timestamp
  BEFORE UPDATE ON event_lap_records
  FOR EACH ROW
  EXECUTE FUNCTION update_event_lap_records_updated_at();