ranger-app/supabase-schema.sql

-- Schema for Supabase to set up user points tracking
-- This file is the main schema definition for the points system

-- Create users table to track user information
CREATE TABLE IF NOT EXISTS users (
  id UUID PRIMARY KEY,
  email TEXT NOT NULL,
  name TEXT,
  first_login TIMESTAMP WITH TIME ZONE DEFAULT now(),
  last_login TIMESTAMP WITH TIME ZONE DEFAULT now()
);

-- Create point_transactions table to track individual point awards
CREATE TABLE IF NOT EXISTS point_transactions (
  id BIGSERIAL PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES users(id),
  points INTEGER NOT NULL,
  transaction_type TEXT NOT NULL,
  image_hash TEXT,
  image_url TEXT,
  description TEXT,
  animal_type TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

-- Create indexes for faster queries
CREATE INDEX IF NOT EXISTS idx_point_transactions_user_id ON point_transactions(user_id);
CREATE INDEX IF NOT EXISTS idx_point_transactions_image_hash ON point_transactions(image_hash);
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);

-- Function to ensure a user exists in the database
CREATE OR REPLACE FUNCTION ensure_user_exists(
  user_id_param TEXT,
  email_param TEXT,
  name_param TEXT DEFAULT NULL
)
RETURNS UUID AS $$
DECLARE
  user_uuid UUID;
  existing_user_id UUID;
  exists_flag BOOLEAN;
BEGIN
  -- Convert to UUID
  user_uuid := user_id_param::UUID;
  
  -- First check if a user with this email already exists
  SELECT id INTO existing_user_id
  FROM users 
  WHERE email = email_param;
  
  -- If a user with this email exists, use their ID and update
  IF FOUND THEN
    UPDATE users
    SET last_login = now(),
        name = COALESCE(name_param, name)
    WHERE id = existing_user_id;
    
    RETURN existing_user_id;
  END IF;
  
  -- If we get here, no user with this email exists
  -- Now check if user with this ID exists
  SELECT EXISTS (
    SELECT 1 FROM users WHERE id = user_uuid
  ) INTO exists_flag;
  
  -- If user doesn't exist, create them
  IF NOT exists_flag THEN
    INSERT INTO users (id, email, name, first_login, last_login)
    VALUES (user_uuid, email_param, name_param, now(), now());
  ELSE
    -- Update last_login and other fields
    UPDATE users
    SET last_login = now(),
        email = email_param,
        name = COALESCE(name_param, name)
    WHERE id = user_uuid;
  END IF;
  
  RETURN user_uuid;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to create a point transaction
CREATE OR REPLACE FUNCTION create_point_transaction(
  user_id_param TEXT,
  points_param INTEGER,
  transaction_type_param TEXT,
  image_hash_param TEXT DEFAULT NULL,
  image_url_param TEXT DEFAULT NULL,
  description_param TEXT DEFAULT NULL,
  animal_type_param TEXT DEFAULT NULL
)
RETURNS json AS $$
DECLARE
  new_transaction point_transactions;
  user_uuid UUID;
BEGIN
  -- Convert to UUID
  user_uuid := user_id_param::UUID;
  
  INSERT INTO point_transactions (
    user_id, 
    points, 
    transaction_type, 
    image_hash, 
    image_url, 
    description, 
    animal_type, 
    created_at
  )
  VALUES (
    user_uuid, 
    points_param, 
    transaction_type_param, 
    image_hash_param, 
    image_url_param, 
    description_param, 
    animal_type_param, 
    now()
  )
  RETURNING * INTO new_transaction;
  
  RETURN row_to_json(new_transaction);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to calculate a user's total points from transactions
CREATE OR REPLACE FUNCTION get_user_total_points(
  user_id_param TEXT
)
RETURNS INTEGER AS $$
DECLARE
  total INTEGER;
  user_uuid UUID;
BEGIN
  -- Convert to UUID
  user_uuid := user_id_param::UUID;
  
  SELECT COALESCE(SUM(points), 0) INTO total
  FROM point_transactions
  WHERE user_id = user_uuid;
  
  RETURN total;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to get user transactions
CREATE OR REPLACE FUNCTION get_user_transactions(
  user_id_param TEXT
)
RETURNS SETOF point_transactions AS $$
DECLARE
  user_uuid UUID;
BEGIN
  -- Convert to UUID
  user_uuid := user_id_param::UUID;
  
  RETURN QUERY
  SELECT *
  FROM point_transactions
  WHERE user_id = user_uuid
  ORDER BY created_at DESC;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to check if an image has already been processed for a user
CREATE OR REPLACE FUNCTION check_duplicate_upload(
  user_id_param TEXT,
  image_hash_param TEXT
)
RETURNS BOOLEAN AS $$
DECLARE
  exists_flag BOOLEAN;
  user_uuid UUID;
BEGIN
  -- Convert to UUID
  user_uuid := user_id_param::UUID;
  
  SELECT EXISTS (
    SELECT 1 FROM point_transactions 
    WHERE user_id = user_uuid 
    AND image_hash = image_hash_param
  ) INTO exists_flag;
  
  RETURN exists_flag;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to find a user by email
CREATE OR REPLACE FUNCTION find_user_by_email(
  email_param TEXT
)
RETURNS json AS $$
DECLARE
  user_record users;
BEGIN
  SELECT * INTO user_record
  FROM users
  WHERE email = email_param;
  
  IF FOUND THEN
    RETURN row_to_json(user_record);
  ELSE
    RETURN NULL;
  END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

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

-- Create policies section - uncomment if you need to recreate policies
/*
-- Users policies
CREATE POLICY "Users can view their own profile"
  ON users FOR SELECT
  USING (auth.uid() = id);

CREATE POLICY "Service role can manage all users"
  ON users FOR ALL
  USING (auth.role() = 'service_role');

-- Transaction policies
CREATE POLICY "Users can view their own transactions"
  ON point_transactions FOR SELECT
  USING (auth.uid() = user_id);

CREATE POLICY "Service role can manage all transactions"
  ON point_transactions FOR ALL
  USING (auth.role() = 'service_role');
*/