canadian-alternatives/database-setup.sql

-- Create extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Create tables
CREATE TABLE IF NOT EXISTS products (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT NOT NULL,
  description TEXT NOT NULL,
  company TEXT NOT NULL,
  country_of_origin TEXT NOT NULL,
  category TEXT NOT NULL,
  image_url TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS alternatives (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  product_id UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  description TEXT NOT NULL,
  company TEXT NOT NULL,
  category TEXT NOT NULL,
  image_url TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS opportunities (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  category TEXT NOT NULL,
  description TEXT NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Create profiles table for user authentication
CREATE TABLE IF NOT EXISTS profiles (
  id UUID PRIMARY KEY REFERENCES auth.users ON DELETE CASCADE,
  email TEXT NOT NULL,
  full_name TEXT,
  avatar_url TEXT,
  is_admin BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Set up Row Level Security (RLS)
-- Enable RLS on tables
ALTER TABLE products ENABLE ROW LEVEL SECURITY;
ALTER TABLE alternatives ENABLE ROW LEVEL SECURITY;
ALTER TABLE opportunities ENABLE ROW LEVEL SECURITY;
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;

-- Create policies
-- Products: everyone can view, only authenticated users can insert, only admins can update/delete
CREATE POLICY "Products are viewable by everyone" ON products
  FOR SELECT USING (true);

CREATE POLICY "Products can be inserted by authenticated users" ON products
  FOR INSERT WITH CHECK (auth.role() = 'authenticated');

CREATE POLICY "Products can be updated by admins" ON products
  FOR UPDATE USING (
    auth.role() = 'authenticated' AND 
    EXISTS (SELECT 1 FROM profiles WHERE profiles.id = auth.uid() AND profiles.is_admin = true)
  );

CREATE POLICY "Products can be deleted by admins" ON products
  FOR DELETE USING (
    auth.role() = 'authenticated' AND 
    EXISTS (SELECT 1 FROM profiles WHERE profiles.id = auth.uid() AND profiles.is_admin = true)
  );

-- Alternatives: everyone can view, only authenticated users can insert, only admins can update/delete
CREATE POLICY "Alternatives are viewable by everyone" ON alternatives
  FOR SELECT USING (true);

CREATE POLICY "Alternatives can be inserted by authenticated users" ON alternatives
  FOR INSERT WITH CHECK (auth.role() = 'authenticated');

CREATE POLICY "Alternatives can be updated by admins" ON alternatives
  FOR UPDATE USING (
    auth.role() = 'authenticated' AND 
    EXISTS (SELECT 1 FROM profiles WHERE profiles.id = auth.uid() AND profiles.is_admin = true)
  );

CREATE POLICY "Alternatives can be deleted by admins" ON alternatives
  FOR DELETE USING (
    auth.role() = 'authenticated' AND 
    EXISTS (SELECT 1 FROM profiles WHERE profiles.id = auth.uid() AND profiles.is_admin = true)
  );

-- Opportunities: everyone can view, only authenticated users can insert, only admins can update/delete
CREATE POLICY "Opportunities are viewable by everyone" ON opportunities
  FOR SELECT USING (true);

CREATE POLICY "Opportunities can be inserted by authenticated users" ON opportunities
  FOR INSERT WITH CHECK (auth.role() = 'authenticated');

CREATE POLICY "Opportunities can be updated by admins" ON opportunities
  FOR UPDATE USING (
    auth.role() = 'authenticated' AND 
    EXISTS (SELECT 1 FROM profiles WHERE profiles.id = auth.uid() AND profiles.is_admin = true)
  );

CREATE POLICY "Opportunities can be deleted by admins" ON opportunities
  FOR DELETE USING (
    auth.role() = 'authenticated' AND 
    EXISTS (SELECT 1 FROM profiles WHERE profiles.id = auth.uid() AND profiles.is_admin = true)
  );

-- Profiles: users can view all profiles, but can only update their own
CREATE POLICY "Profiles are viewable by everyone" ON profiles
  FOR SELECT USING (true);

CREATE POLICY "Users can insert their own profile" ON profiles
  FOR INSERT WITH CHECK (auth.uid() = id);

CREATE POLICY "Users can update their own profile" ON profiles
  FOR UPDATE USING (auth.uid() = id);

-- Create triggers to update updated_at column
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_profiles_updated_at
BEFORE UPDATE ON profiles
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Create categories table to standardize categories
CREATE TABLE IF NOT EXISTS categories (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT NOT NULL UNIQUE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Allow everyone to view categories
ALTER TABLE categories ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Categories are viewable by everyone" ON categories
  FOR SELECT USING (true);
CREATE POLICY "Categories can be managed by admins" ON categories
  FOR ALL USING (
    auth.role() = 'authenticated' AND 
    EXISTS (SELECT 1 FROM profiles WHERE profiles.id = auth.uid() AND profiles.is_admin = true)
  );

-- Insert some common categories
INSERT INTO categories (name) VALUES 
  ('Electronics'),
  ('Food & Beverage'),
  ('Clothing'),
  ('Software'),
  ('Home Goods'),
  ('Personal Care'),
  ('Automotive'),
  ('Entertainment')
ON CONFLICT (name) DO NOTHING;

-- Function to add an admin user
CREATE OR REPLACE FUNCTION create_admin_user(email TEXT, password TEXT, full_name TEXT)
RETURNS uuid AS $$
DECLARE
  user_id uuid;
BEGIN
  -- Create user in auth.users via Supabase's auth.users() function (handled by Supabase Auth)
  -- This function doesn't exist in pure PostgreSQL, it's a placeholder for the actual implementation
  -- You'll need to create the admin user through the Supabase dashboard or API
  -- After creating the user, insert into profiles with is_admin = true
  INSERT INTO profiles (id, email, full_name, is_admin)
  VALUES (user_id, email, full_name, true);
  
  RETURN user_id;
END;
$$ LANGUAGE plpgsql;