supabase-setup.sql

-- 🚀 AI Prompt Dashboard - Supabase Setup Script
-- Run this in your Supabase SQL Editor to set up the database properly

-- ============================================================================
-- 1. CREATE THE PROMPTS TABLE (if it doesn't exist)
-- ============================================================================

-- Create the prompts table with exact column names expected by the code
CREATE TABLE IF NOT EXISTS prompts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  content TEXT NOT NULL,
  tags TEXT[] DEFAULT '{}',
  createdat TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  title TEXT,
  category TEXT DEFAULT 'task',
  description TEXT DEFAULT '',
  user_id TEXT NOT NULL,
  ispublic BOOLEAN DEFAULT false,
  likes INTEGER DEFAULT 0,
  views INTEGER DEFAULT 0,
  comments INTEGER DEFAULT 0
);

-- ============================================================================
-- 2. ADD MISSING COLUMNS (if your table already exists)
-- ============================================================================

-- Add user_id column if it doesn't exist
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns
                   WHERE table_name = 'prompts' AND column_name = 'user_id') THEN
        ALTER TABLE prompts ADD COLUMN user_id TEXT;

        -- Set a default user_id for existing prompts
        UPDATE prompts SET user_id = 'legacy-user' WHERE user_id IS NULL;

        -- Make user_id required for new prompts
        ALTER TABLE prompts ALTER COLUMN user_id SET NOT NULL;
    END IF;
END $$;

-- Add other missing columns if needed
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns
                   WHERE table_name = 'prompts' AND column_name = 'title') THEN
        ALTER TABLE prompts ADD COLUMN title TEXT;
        UPDATE prompts SET title = LEFT(content, 50) WHERE title IS NULL;
        ALTER TABLE prompts ALTER COLUMN title SET NOT NULL;
    END IF;
END $$;

-- ============================================================================
-- 3. CREATE INDEXES FOR BETTER PERFORMANCE
-- ============================================================================

CREATE INDEX IF NOT EXISTS idx_prompts_user_id ON prompts(user_id);
CREATE INDEX IF NOT EXISTS idx_prompts_createdat ON prompts(createdat);
CREATE INDEX IF NOT EXISTS idx_prompts_tags ON prompts USING GIN(tags);

-- ============================================================================
-- 4. ENABLE ROW LEVEL SECURITY (RLS)
-- ============================================================================

ALTER TABLE prompts ENABLE ROW LEVEL SECURITY;

-- ============================================================================
-- 5. DROP ANY EXISTING POLICIES (clean slate)
-- ============================================================================

DROP POLICY IF EXISTS "Users can view their own prompts" ON prompts;
DROP POLICY IF EXISTS "Users can insert their own prompts" ON prompts;
DROP POLICY IF EXISTS "Users can update their own prompts" ON prompts;
DROP POLICY IF EXISTS "Users can delete their own prompts" ON prompts;
DROP POLICY IF EXISTS "Allow all access" ON prompts;
DROP POLICY IF EXISTS "Temporary allow all" ON prompts;

-- ============================================================================
-- 6. CREATE PERMISSIVE RLS POLICIES (for easy setup)
-- ============================================================================

-- Create permissive policy for all operations (you can restrict this later)
CREATE POLICY "Allow all operations for now" ON prompts FOR ALL USING (true);

-- ============================================================================
-- 8. VERIFY SETUP
-- ============================================================================

-- Check table structure
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'prompts'
ORDER BY ordinal_position;

-- Check RLS status
SELECT tablename, rowsecurity
FROM pg_tables
WHERE tablename = 'prompts';

-- Check policies
SELECT policyname, permissive, roles, cmd, qual
FROM pg_policies
WHERE tablename = 'prompts';

-- Test query (should work)
SELECT COUNT(*) as total_prompts FROM prompts;

-- ============================================================================
-- 🎉 SETUP COMPLETE!
-- ============================================================================

-- Your prompts table is now ready with:
-- ✅ Proper table structure
-- ✅ Required columns (including user_id)
-- ✅ Performance indexes
-- ✅ Row Level Security enabled
-- ✅ Policies for CRUD operations
-- ✅ Temporary permissive policy for testing

-- NEXT STEPS:
-- 1. Test your connection in the AI Prompt Dashboard
-- 2. Create some prompts to verify everything works
-- 3. Once confirmed, remove the temporary policy by running:
--    DROP POLICY "Temporary allow all for setup" ON prompts;

-- REMEMBER:
-- - Use the anon/public key (not service role key) in your app
-- - Set a custom user ID in settings to sync across browsers
-- - Each user will only see their own prompts