setup-database.js

/**
 * SheSphere Database Setup Instructions
 * 
 * Follow these steps to set up your Supabase database:
 * 
 * 1. Log in to your Supabase dashboard (https://app.supabase.com)
 * 2. Select your project
 * 3. Go to the SQL Editor (left sidebar)
 * 4. Click "New Query"
 * 5. Copy and paste the following SQL code:
 */

// SQL Code from schema.sql
const schemaSql = `
-- Create a table for user profiles
CREATE TABLE IF NOT EXISTS public.profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  full_name TEXT,
  avatar_url TEXT,
  phone TEXT,
  address TEXT,
  email TEXT UNIQUE NOT NULL
);

-- Enable Row Level Security
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;

-- Create policies
-- Policy for users to view their own profiles
CREATE POLICY "Users can view their own profiles" ON public.profiles
  FOR SELECT USING (auth.uid() = id);

-- Policy for users to update their own profiles
CREATE POLICY "Users can update their own profiles" ON public.profiles
  FOR UPDATE USING (auth.uid() = id);

-- Policy for profile creation (for new users)
CREATE POLICY "Users can insert their own profiles" ON public.profiles
  FOR INSERT WITH CHECK (auth.uid() = id);

-- Function to handle user creation
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.profiles (id, full_name, email)
  VALUES (
    NEW.id,
    NEW.raw_user_meta_data->>'full_name',
    NEW.email
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Trigger the function every time a user is created
DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;
CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();
`;

// Log instructions to the console
console.log("=== SheSphere Database Setup Instructions ===");
console.log("\nCopy the SQL code between the lines and run it in your Supabase SQL Editor:\n");
console.log("-----------------------------SQL START-----------------------------");
console.log(schemaSql);
console.log("-----------------------------SQL END-------------------------------");
console.log("\nAfter running the SQL script:");
console.log("1. Go to Authentication → Settings");
console.log("2. Make sure 'Enable email confirmations' is OFF for testing");
console.log("3. Go to Table Editor to verify the 'profiles' table was created");
console.log("\nNow you can run the app and test signup/login functionality!");

// To run this script, use: node setup-database.js 

/**
 * IMPORTANT: Run this SQL in your Supabase project SQL Editor to fix profile table issues
 */

const profilesTableSQL = `
-- Drop the profiles table if it exists with wrong schema (optional, uncomment if needed)
-- DROP TABLE IF EXISTS public.profiles;

-- Create the profiles table correctly with all possible columns from different schemas
CREATE TABLE IF NOT EXISTS public.profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  name TEXT,
  full_name TEXT,
  avatar_url TEXT,
  phone TEXT,
  address TEXT,
  email TEXT
);

-- If table already existed, ensure all columns are present
ALTER TABLE IF EXISTS public.profiles 
  ADD COLUMN IF NOT EXISTS name TEXT,
  ADD COLUMN IF NOT EXISTS full_name TEXT,
  ADD COLUMN IF NOT EXISTS email TEXT,
  ADD COLUMN IF NOT EXISTS created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW();

-- Enable Row Level Security
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;

-- Make sure all RLS policies exist
DO $$ 
BEGIN
  -- For Select (viewing)
  IF NOT EXISTS (
    SELECT FROM pg_policies 
    WHERE tablename = 'profiles' 
    AND policyname = 'Users can view their own profiles'
  ) THEN
    CREATE POLICY "Users can view their own profiles" 
      ON public.profiles 
      FOR SELECT 
      USING (auth.uid() = id);
  END IF;

  -- For Update
  IF NOT EXISTS (
    SELECT FROM pg_policies 
    WHERE tablename = 'profiles' 
    AND policyname = 'Users can update their own profiles'
  ) THEN
    CREATE POLICY "Users can update their own profiles" 
      ON public.profiles 
      FOR UPDATE 
      USING (auth.uid() = id);
  END IF;

  -- For Insert
  IF NOT EXISTS (
    SELECT FROM pg_policies 
    WHERE tablename = 'profiles' 
    AND policyname = 'Users can insert their own profiles'
  ) THEN
    CREATE POLICY "Users can insert their own profiles" 
      ON public.profiles 
      FOR INSERT 
      WITH CHECK (auth.uid() = id);
  END IF;
END $$;

-- Function to ensure user profile exists
CREATE OR REPLACE FUNCTION ensure_user_profile_exists(
  user_uuid UUID,
  user_name TEXT,
  user_email TEXT
) RETURNS BOOLEAN AS $$
DECLARE
  profile_exists BOOLEAN;
BEGIN
  -- Check if profile exists
  SELECT EXISTS(
    SELECT 1 FROM profiles WHERE id = user_uuid
  ) INTO profile_exists;
  
  -- If profile doesn't exist, create it
  IF NOT profile_exists THEN
    INSERT INTO profiles (id, name, full_name, email, created_at, updated_at)
    VALUES (
      user_uuid,
      user_name,
      user_name,
      user_email,
      NOW(),
      NOW()
    );
    RETURN TRUE;
  END IF;
  
  RETURN profile_exists;
EXCEPTION WHEN OTHERS THEN
  RAISE NOTICE 'Error ensuring user profile: %', SQLERRM;
  RETURN FALSE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to execute SQL (used for repair operations)
CREATE OR REPLACE FUNCTION execute_sql(sql_string TEXT)
RETURNS BOOLEAN AS $$
BEGIN
  EXECUTE sql_string;
  RETURN TRUE;
EXCEPTION WHEN OTHERS THEN
  RAISE NOTICE 'Error executing SQL: %', SQLERRM;
  RETURN FALSE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
`;

console.log("INSTRUCTIONS:");
console.log("============================================================");
console.log("1. Go to your Supabase project dashboard");
console.log("2. Click on 'SQL Editor' in the left sidebar");
console.log("3. Create a new query");
console.log("4. Copy and paste EVERYTHING between the SQL BEGINS and SQL ENDS markers below");
console.log("5. Run the SQL query");
console.log("============================================================");
console.log("SQL BEGINS HERE:");
console.log(profilesTableSQL);
console.log("SQL ENDS HERE");
console.log("============================================================");
console.log("After running this SQL, restart your app and try signing up again.");