thrilltrack-explorer/supabase/migrations/20251016153321_bde00850-3566-4b1a-9b16-d403561257c8.sql

-- Add enhanced user experience columns to user_ride_credits
ALTER TABLE user_ride_credits
ADD COLUMN IF NOT EXISTS last_ride_date DATE,
ADD COLUMN IF NOT EXISTS personal_notes TEXT,
ADD COLUMN IF NOT EXISTS personal_rating INTEGER CHECK (personal_rating BETWEEN 1 AND 5),
ADD COLUMN IF NOT EXISTS personal_photo_id TEXT;

-- Add indexes for faster filtering
CREATE INDEX IF NOT EXISTS idx_user_ride_credits_rating 
ON user_ride_credits(user_id, personal_rating) WHERE personal_rating IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_user_ride_credits_dates 
ON user_ride_credits(user_id, first_ride_date, last_ride_date);

CREATE INDEX IF NOT EXISTS idx_user_ride_credits_notes 
ON user_ride_credits(user_id) WHERE personal_notes IS NOT NULL;

-- Update the auto_add_ride_credit_on_review trigger to set last_ride_date
CREATE OR REPLACE FUNCTION public.auto_add_ride_credit_on_review()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  -- Only proceed if ride_id is set
  IF NEW.ride_id IS NULL THEN
    RETURN NEW;
  END IF;

  -- Check if credit exists
  IF EXISTS (
    SELECT 1 FROM user_ride_credits
    WHERE user_id = NEW.user_id AND ride_id = NEW.ride_id
  ) THEN
    -- Update existing credit: increment count and update last_ride_date if visit_date is provided
    UPDATE user_ride_credits
    SET 
      ride_count = ride_count + 1,
      last_ride_date = COALESCE(NEW.visit_date, CURRENT_DATE),
      updated_at = NOW()
    WHERE user_id = NEW.user_id AND ride_id = NEW.ride_id;
  ELSE
    -- Insert new credit with visit_date as both first and last
    INSERT INTO user_ride_credits (user_id, ride_id, first_ride_date, last_ride_date, ride_count)
    VALUES (
      NEW.user_id,
      NEW.ride_id,
      COALESCE(NEW.visit_date, CURRENT_DATE),
      COALESCE(NEW.visit_date, CURRENT_DATE),
      1
    );
  END IF;

  RETURN NEW;
END;
$$;