ranger-app/supabase-schema-updates.sql

-- Additional SQL functions for the points system
-- This file is meant to be run after supabase-schema.sql

-- Function to get transaction count for a user
CREATE OR REPLACE FUNCTION get_user_transaction_count(
  user_id_param TEXT
)
RETURNS INTEGER AS $$
DECLARE
  count_result INTEGER;
  user_uuid UUID;
BEGIN
  -- Convert to UUID
  user_uuid := user_id_param::UUID;
  
  SELECT COUNT(*) INTO count_result
  FROM point_transactions
  WHERE user_id = user_uuid;
  
  RETURN count_result;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to get a user's first transaction date
CREATE OR REPLACE FUNCTION get_user_first_transaction_date(
  user_id_param TEXT
)
RETURNS TIMESTAMP WITH TIME ZONE AS $$
DECLARE
  first_date TIMESTAMP WITH TIME ZONE;
  user_uuid UUID;
BEGIN
  -- Convert to UUID
  user_uuid := user_id_param::UUID;
  
  SELECT MIN(created_at) INTO first_date
  FROM point_transactions
  WHERE user_id = user_uuid;
  
  RETURN first_date;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to get a user's latest transaction date
CREATE OR REPLACE FUNCTION get_user_latest_transaction_date(
  user_id_param TEXT
)
RETURNS TIMESTAMP WITH TIME ZONE AS $$
DECLARE
  latest_date TIMESTAMP WITH TIME ZONE;
  user_uuid UUID;
BEGIN
  -- Convert to UUID
  user_uuid := user_id_param::UUID;
  
  SELECT MAX(created_at) INTO latest_date
  FROM point_transactions
  WHERE user_id = user_uuid;
  
  RETURN latest_date;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to get user info by ID
CREATE OR REPLACE FUNCTION get_user_info(
  user_id_param TEXT
)
RETURNS json AS $$
DECLARE
  user_record users;
  user_uuid UUID;
BEGIN
  -- Convert to UUID
  user_uuid := user_id_param::UUID;
  
  SELECT * INTO user_record
  FROM users
  WHERE id = user_uuid;
  
  IF FOUND THEN
    RETURN row_to_json(user_record);
  ELSE
    RETURN NULL;
  END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to get user stats (combines user info and point total)
CREATE OR REPLACE FUNCTION get_user_stats(
  user_id_param TEXT
)
RETURNS json AS $$
DECLARE
  user_info json;
  points_total INTEGER;
  transaction_count INTEGER;
  first_date TIMESTAMP WITH TIME ZONE;
  latest_date TIMESTAMP WITH TIME ZONE;
  user_uuid UUID;
  result json;
BEGIN
  -- Convert to UUID
  user_uuid := user_id_param::UUID;
  
  -- Get user info
  SELECT row_to_json(u) INTO user_info
  FROM users u
  WHERE id = user_uuid;
  
  -- Get points total
  SELECT COALESCE(SUM(points), 0) INTO points_total
  FROM point_transactions
  WHERE user_id = user_uuid;
  
  -- Get transaction count
  SELECT COUNT(*) INTO transaction_count
  FROM point_transactions
  WHERE user_id = user_uuid;
  
  -- Get first and latest transaction dates
  SELECT MIN(created_at), MAX(created_at) 
  INTO first_date, latest_date
  FROM point_transactions
  WHERE user_id = user_uuid;
  
  -- Build result object
  SELECT json_build_object(
    'user', user_info,
    'points', points_total,
    'transaction_count', transaction_count,
    'first_transaction', first_date,
    'latest_transaction', latest_date
  ) INTO result;
  
  RETURN result;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Migration helper function to transfer data from old schema to new schema
-- This is only needed if you're migrating from the old schema
CREATE OR REPLACE FUNCTION migrate_user_data(
  old_user_id_param TEXT,
  new_user_id_param TEXT,
  email_param TEXT,
  name_param TEXT DEFAULT NULL
)
RETURNS BOOLEAN AS $$
DECLARE
  old_user_uuid UUID;
  new_user_uuid UUID;
BEGIN
  -- Convert to UUIDs
  old_user_uuid := old_user_id_param::UUID;
  new_user_uuid := new_user_id_param::UUID;
  
  -- Ensure new user exists
  INSERT INTO users (id, email, name, first_login, last_login)
  VALUES (new_user_uuid, email_param, name_param, now(), now())
  ON CONFLICT (id) DO NOTHING;
  
  -- Update transactions to use new user ID
  UPDATE point_transactions
  SET user_id = new_user_uuid
  WHERE user_id = old_user_uuid;
  
  RETURN TRUE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to merge duplicate users by email
-- This will find all users with the same email, keep the oldest one,
-- and migrate all transactions to that user
CREATE OR REPLACE FUNCTION merge_duplicate_users_by_email(
  email_param TEXT
)
RETURNS json AS $$
DECLARE
  primary_user_id UUID;
  duplicate_user record;
  result json;
  merge_count INT := 0;
  transaction_count INT := 0;
BEGIN
  -- Find the oldest user with this email to use as the primary
  SELECT id INTO primary_user_id
  FROM users
  WHERE email = email_param
  ORDER BY first_login ASC
  LIMIT 1;
  
  -- If no user found, return error
  IF primary_user_id IS NULL THEN
    RETURN json_build_object(
      'success', FALSE,
      'message', 'No user found with this email'
    );
  END IF;
  
  -- Find all other users with the same email
  FOR duplicate_user IN
    SELECT id
    FROM users
    WHERE email = email_param
    AND id != primary_user_id
  LOOP
    -- Migrate this user's transactions to the primary user
    UPDATE point_transactions
    SET user_id = primary_user_id
    WHERE user_id = duplicate_user.id;
    
    -- Count migrated transactions
    GET DIAGNOSTICS transaction_count = ROW_COUNT;
    
    -- Delete the duplicate user
    DELETE FROM users
    WHERE id = duplicate_user.id;
    
    merge_count := merge_count + 1;
  END LOOP;
  
  -- Build the result object
  result := json_build_object(
    'success', TRUE,
    'primary_user_id', primary_user_id,
    'merged_users_count', merge_count,
    'migrated_transactions', transaction_count,
    'email', email_param
  );
  
  RETURN result;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;