-- Extend profiles table with unique profile customization features
-- Run this in your Supabase SQL Editor

-- 1. Add new columns to profiles table
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' CHECK (profile_visibility IN ('public', 'friends', 'private')),
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;

-- 2. Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_profiles_username_change ON public.profiles(last_username_change);
CREATE INDEX IF NOT EXISTS idx_profiles_theme_colors ON public.profiles(profile_theme_color, profile_accent_color);
CREATE INDEX IF NOT EXISTS idx_profiles_visibility ON public.profiles(profile_visibility);

-- 3. Create function to check username change limit
CREATE OR REPLACE FUNCTION public.can_change_username(user_uuid UUID)
RETURNS BOOLEAN AS $$
DECLARE
    last_change TIMESTAMP WITH TIME ZONE;
    days_since_change INTEGER;
BEGIN
    -- Get last username change
    SELECT last_username_change INTO last_change
    FROM public.profiles
    WHERE id = user_uuid;
    
    -- If never changed, allow it
    IF last_change IS NULL THEN
        RETURN TRUE;
    END IF;
    
    -- Check if 7 days have passed
    days_since_change := EXTRACT(DAY FROM (NOW() - last_change));
    
    RETURN days_since_change >= 7;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 4. Create function to update username change tracking
CREATE OR REPLACE FUNCTION public.update_username_change_tracking(user_uuid UUID)
RETURNS VOID AS $$
BEGIN
    UPDATE public.profiles
    SET 
        last_username_change = NOW(),
        username_change_count = username_change_count + 1
    WHERE id = user_uuid;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 5. Create function to validate BBCode
CREATE OR REPLACE FUNCTION public.validate_bbcode(bbcode_text TEXT)
RETURNS BOOLEAN AS $$
BEGIN
    -- Basic BBCode validation (allow common tags)
    -- [b], [i], [u], [color], [size], [url], [img], [quote], [code], [list], [*]
    IF bbcode_text ~ '\[/?(b|i|u|color|size|url|img|quote|code|list|\*)\]' THEN
        RETURN TRUE;
    END IF;
    
    -- Allow text without BBCode
    IF bbcode_text !~ '\[.*\]' THEN
        RETURN TRUE;
    END IF;
    
    RETURN FALSE;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 6. Create function to sanitize BBCode
CREATE OR REPLACE FUNCTION public.sanitize_bbcode(bbcode_text TEXT)
RETURNS TEXT AS $$
BEGIN
    -- Remove potentially dangerous BBCode tags
    bbcode_text := regexp_replace(bbcode_text, '\[/?(script|javascript|onclick|onload)\]', '', 'gi');
    
    -- Limit color values to safe hex colors
    bbcode_text := regexp_replace(bbcode_text, '\[color=#([0-9A-Fa-f]{6})\]', '[color=#\1]', 'g');
    
    -- Limit size values to reasonable range
    bbcode_text := regexp_replace(bbcode_text, '\[size=([1-7])\]', '[size=\1]', 'g');
    
    RETURN bbcode_text;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 7. Create trigger to validate BBCode before insert/update
CREATE OR REPLACE FUNCTION public.validate_profile_bbcode()
RETURNS TRIGGER AS $$
BEGIN
    -- Validate BBCode if provided
    IF NEW.bio_bbcode IS NOT NULL AND NEW.bio_bbcode != '' THEN
        IF NOT public.validate_bbcode(NEW.bio_bbcode) THEN
            RAISE EXCEPTION 'Invalid BBCode detected in bio';
        END IF;
        
        -- Sanitize BBCode
        NEW.bio_bbcode := public.sanitize_bbcode(NEW.bio_bbcode);
    END IF;
    
    -- Validate URL formats
    IF NEW.spotify_playlist_url IS NOT NULL AND NEW.spotify_playlist_url != '' THEN
        IF NEW.spotify_playlist_url !~ '^https?://open\.spotify\.com/' THEN
            RAISE EXCEPTION 'Invalid Spotify playlist URL';
        END IF;
    END IF;
    
    IF NEW.profile_gif_url IS NOT NULL AND NEW.profile_gif_url != '' THEN
        IF NEW.profile_gif_url !~ '^https?://.*\.(gif|gifv)$' THEN
            RAISE EXCEPTION 'Invalid GIF URL (must end with .gif or .gifv)';
        END IF;
    END IF;
    
    IF NEW.profile_music_url IS NOT NULL AND NEW.profile_music_url != '' THEN
        IF NEW.profile_music_url !~ '^https?://.*\.(mp3|wav|ogg|m4a)$' THEN
            RAISE EXCEPTION 'Invalid music URL (must end with .mp3, .wav, .ogg, or .m4a)';
        END IF;
    END IF;
    
    -- Validate color hex codes
    IF NEW.profile_theme_color IS NOT NULL THEN
        IF NEW.profile_theme_color !~ '^#[0-9A-Fa-f]{6}$' THEN
            RAISE EXCEPTION 'Invalid theme color format (must be hex color like #FF0000)';
        END IF;
    END IF;
    
    IF NEW.profile_accent_color IS NOT NULL THEN
        IF NEW.profile_accent_color !~ '^#[0-9A-Fa-f]{6}$' THEN
            RAISE EXCEPTION 'Invalid accent color format (must be hex color like #FF0000)';
        END IF;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 8. Create trigger
DROP TRIGGER IF EXISTS validate_profile_bbcode_trigger ON public.profiles;
CREATE TRIGGER validate_profile_bbcode_trigger
    BEFORE INSERT OR UPDATE ON public.profiles
    FOR EACH ROW
    EXECUTE FUNCTION public.validate_profile_bbcode();

-- 9. Update RLS policies to include new columns
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);

-- Create a separate function to handle username change validation
CREATE OR REPLACE FUNCTION public.validate_username_change()
RETURNS TRIGGER AS $$
BEGIN
    -- Only check username change limit if username is actually changing
    IF NEW.username != OLD.username THEN
        IF NOT public.can_change_username(NEW.id) THEN
            RAISE EXCEPTION 'Username can only be changed once every 7 days. Last change: %', 
                COALESCE(OLD.last_username_change, 'Never');
        END IF;
        
        -- Update tracking
        PERFORM public.update_username_change_tracking(NEW.id);
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Create trigger for username change validation
DROP TRIGGER IF EXISTS validate_username_change_trigger ON public.profiles;
CREATE TRIGGER validate_username_change_trigger
    BEFORE UPDATE ON public.profiles
    FOR EACH ROW
    EXECUTE FUNCTION public.validate_username_change();

-- 10. Create function to get profile customization data
CREATE OR REPLACE FUNCTION public.get_profile_customization(user_uuid UUID)
RETURNS TABLE(
    theme_color TEXT,
    accent_color TEXT,
    background_color TEXT,
    text_color TEXT,
    border_color TEXT,
    has_spotify BOOLEAN,
    has_gif BOOLEAN,
    has_music BOOLEAN
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        p.profile_theme_color,
        p.profile_accent_color,
        p.profile_background_color,
        p.profile_text_color,
        p.profile_border_color,
        p.spotify_playlist_url IS NOT NULL AND p.spotify_playlist_url != '',
        p.profile_gif_url IS NOT NULL AND p.profile_gif_url != '',
        p.profile_music_url IS NOT NULL AND p.profile_music_url != ''
    FROM public.profiles p
    WHERE p.id = user_uuid;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 11. Test the setup
SELECT 'Profiles table extended successfully!' as status;

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