-- Fix profile saving issues - Check and repair database schema
-- Run this in your Supabase SQL Editor

-- 1. First, let's check what columns currently exist in the profiles table
SELECT 
    column_name,
    data_type,
    is_nullable,
    column_default
FROM information_schema.columns 
WHERE table_name = 'profiles' 
ORDER BY ordinal_position;

-- 2. Add missing columns if they don't exist
ALTER TABLE public.profiles 
ADD COLUMN IF NOT EXISTS first_name TEXT,
ADD COLUMN IF NOT EXISTS last_name TEXT,
ADD COLUMN IF NOT EXISTS bio_bbcode TEXT,
ADD COLUMN IF NOT EXISTS spotify_playlist_url TEXT,
ADD COLUMN IF NOT EXISTS profile_gif_url TEXT,
ADD COLUMN IF NOT EXISTS profile_music_url TEXT,
ADD COLUMN IF NOT EXISTS profile_theme_color TEXT DEFAULT '#8B5CF6',
ADD COLUMN IF NOT EXISTS profile_accent_color TEXT DEFAULT '#EC4899',
ADD COLUMN IF NOT EXISTS profile_background_color TEXT DEFAULT '#F3F4F6',
ADD COLUMN IF NOT EXISTS profile_text_color TEXT DEFAULT '#1F2937',
ADD COLUMN IF NOT EXISTS profile_border_color TEXT DEFAULT '#E5E7EB',
ADD COLUMN IF NOT EXISTS last_username_change TIMESTAMP WITH TIME ZONE,
ADD COLUMN IF NOT EXISTS username_change_count INTEGER DEFAULT 0,
ADD COLUMN IF NOT EXISTS profile_customization_enabled BOOLEAN DEFAULT true,
ADD COLUMN IF NOT EXISTS profile_visibility TEXT DEFAULT 'public',
ADD COLUMN IF NOT EXISTS show_online_status BOOLEAN DEFAULT true,
ADD COLUMN IF NOT EXISTS allow_profile_comments BOOLEAN DEFAULT true,
ADD COLUMN IF NOT EXISTS profile_views_count INTEGER DEFAULT 0,
ADD COLUMN IF NOT EXISTS profile_likes_count INTEGER DEFAULT 0,
ADD COLUMN IF NOT EXISTS profile_comments_count INTEGER DEFAULT 0;

-- 3. Check if the notifications table exists, if not create it
CREATE TABLE IF NOT EXISTS public.notifications (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    type TEXT NOT NULL,
    title TEXT NOT NULL,
    message TEXT NOT NULL,
    is_read BOOLEAN DEFAULT false,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 4. Enable RLS on notifications table
ALTER TABLE public.notifications ENABLE ROW LEVEL SECURITY;

-- 5. Create RLS policies for notifications
DROP POLICY IF EXISTS "Users can view own notifications" ON public.notifications;
CREATE POLICY "Users can view own notifications" ON public.notifications
    FOR SELECT USING (auth.uid() = user_id);

DROP POLICY IF EXISTS "Users can insert own notifications" ON public.notifications;
CREATE POLICY "Users can insert own notifications" ON public.notifications
    FOR INSERT WITH CHECK (auth.uid() = user_id);

DROP POLICY IF EXISTS "Users can update own notifications" ON public.notifications;
CREATE POLICY "Users can update own notifications" ON public.notifications
    FOR UPDATE USING (auth.uid() = user_id);

-- 6. Grant permissions
GRANT ALL ON public.notifications TO authenticated;

-- 7. Check and fix RLS policies on profiles table
DROP POLICY IF EXISTS "Users can view own profile" ON public.profiles;
CREATE POLICY "Users can view own profile" ON public.profiles
    FOR SELECT USING (auth.uid() = id);

DROP POLICY IF EXISTS "Users can update own profile" ON public.profiles;
CREATE POLICY "Users can update own profile" ON public.profiles
    FOR UPDATE USING (auth.uid() = id)
    WITH CHECK (auth.uid() = id);

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

-- 8. Test the setup by trying to update a profile
-- This will help identify any remaining issues
SELECT 'Database schema check completed!' as status;

-- 9. Show final table structure
SELECT 
    column_name,
    data_type,
    is_nullable,
    column_default
FROM information_schema.columns 
WHERE table_name = 'profiles' 
ORDER BY ordinal_position;
