database/schema.sql

-- AI Lead Generation Database Schema
-- This file contains the database schema for Supabase

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- User Preferences Table
-- Stores user configuration and settings for lead generation
CREATE TABLE IF NOT EXISTS user_preferences (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    search_criteria TEXT NOT NULL,
    system_prompt TEXT NOT NULL,
    preferences TEXT NOT NULL,
    schedule_enabled BOOLEAN DEFAULT false,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_user_preferences_email ON user_preferences(email);
CREATE INDEX IF NOT EXISTS idx_user_preferences_schedule_enabled ON user_preferences(schedule_enabled);
CREATE INDEX IF NOT EXISTS idx_user_preferences_created_at ON user_preferences(created_at);

-- Add RLS (Row Level Security) policies
ALTER TABLE user_preferences ENABLE ROW LEVEL SECURITY;

-- Policy to allow users to read their own preferences
DROP POLICY IF EXISTS "Users can read their own preferences" ON user_preferences;
CREATE POLICY "Users can read their own preferences" ON user_preferences
    FOR SELECT USING (true);

-- Policy to allow users to insert their own preferences
DROP POLICY IF EXISTS "Users can insert their own preferences" ON user_preferences;
CREATE POLICY "Users can insert their own preferences" ON user_preferences
    FOR INSERT WITH CHECK (true);

-- Policy to allow users to update their own preferences
DROP POLICY IF EXISTS "Users can update their own preferences" ON user_preferences;
CREATE POLICY "Users can update their own preferences" ON user_preferences
    FOR UPDATE USING (true);

-- Policy to allow users to delete their own preferences
DROP POLICY IF EXISTS "Users can delete their own preferences" ON user_preferences;
CREATE POLICY "Users can delete their own preferences" ON user_preferences
    FOR DELETE USING (true);

-- Optional: Create a function to automatically update the updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Create trigger to automatically update updated_at
DROP TRIGGER IF EXISTS update_user_preferences_updated_at ON user_preferences;
CREATE TRIGGER update_user_preferences_updated_at 
    BEFORE UPDATE ON user_preferences 
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Comments for documentation
COMMENT ON TABLE user_preferences IS 'Stores user preferences and settings for AI lead generation';
COMMENT ON COLUMN user_preferences.id IS 'Unique identifier for the user preference record';
COMMENT ON COLUMN user_preferences.email IS 'User email address (unique identifier)';
COMMENT ON COLUMN user_preferences.search_criteria IS 'Search keywords and criteria for lead discovery';
COMMENT ON COLUMN user_preferences.system_prompt IS 'AI system prompt for lead analysis';
COMMENT ON COLUMN user_preferences.preferences IS 'User targeting preferences and scoring criteria';
COMMENT ON COLUMN user_preferences.schedule_enabled IS 'Whether daily scheduling is enabled for this user';
COMMENT ON COLUMN user_preferences.created_at IS 'Timestamp when the record was created';
COMMENT ON COLUMN user_preferences.updated_at IS 'Timestamp when the record was last updated';