-- ========================================================
-- 23DFactory SaaS Supabase Database & Storage Setup
-- Execute this script in your Supabase Project SQL Editor
-- ========================================================

-- 1. Create / Update Profiles Table (Linked to Supabase Auth)
CREATE TABLE IF NOT EXISTS public.profiles (
    id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
    username TEXT UNIQUE NOT NULL,
    nick TEXT,
    full_name TEXT,
    avatar TEXT,
    avatar_url TEXT,
    credits INTEGER DEFAULT 300,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
);

-- Ensure columns exist in case the table was created previously
ALTER TABLE public.profiles ADD COLUMN IF NOT EXISTS nick TEXT;
ALTER TABLE public.profiles ADD COLUMN IF NOT EXISTS avatar TEXT;
ALTER TABLE public.profiles ADD COLUMN IF NOT EXISTS avatar_url TEXT;
ALTER TABLE public.profiles ADD COLUMN IF NOT EXISTS credits INTEGER DEFAULT 300;

-- Enable RLS for Profiles
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Public profiles are viewable by everyone" ON public.profiles;
CREATE POLICY "Public profiles are viewable by everyone" 
ON public.profiles FOR SELECT USING (true);

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

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


-- 2. Create Models Table (Generated 3D Models History)
CREATE TABLE IF NOT EXISTS public.models (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    prompt TEXT,
    glb_url TEXT,
    fbx_url TEXT,
    preview_url TEXT,
    status TEXT DEFAULT 'completed',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
);

-- Enable RLS for Models
ALTER TABLE public.models ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Users can view their own generated models" ON public.models;
CREATE POLICY "Users can view their own generated models" 
ON public.models FOR SELECT USING (auth.uid() = user_id OR user_id IS NULL);

DROP POLICY IF EXISTS "Users can insert their own generated models" ON public.models;
CREATE POLICY "Users can insert their own generated models" 
ON public.models FOR INSERT WITH CHECK (auth.uid() = user_id OR user_id IS NULL);

DROP POLICY IF EXISTS "Users can delete their own generated models" ON public.models;
CREATE POLICY "Users can delete their own generated models" 
ON public.models FOR DELETE USING (auth.uid() = user_id);


-- 3. Automatic Profile Creation Trigger on New Signup
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO public.profiles (id, username, nick, credits)
    VALUES (
        NEW.id,
        COALESCE(NEW.raw_user_meta_data->>'username', split_part(NEW.email, '@', 1)),
        COALESCE(NEW.raw_user_meta_data->>'username', split_part(NEW.email, '@', 1)),
        300
    )
    ON CONFLICT (id) DO NOTHING;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;
CREATE TRIGGER on_auth_user_created
    AFTER INSERT ON auth.users
    FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();


-- 4. Create Storage Bucket for 3D Models
INSERT INTO storage.buckets (id, name, public) 
VALUES ('models-3d', 'models-3d', true)
ON CONFLICT (id) DO NOTHING;

-- Storage RLS Policies
DROP POLICY IF EXISTS "Public 3D Models Read Access" ON storage.objects;
CREATE POLICY "Public 3D Models Read Access" 
ON storage.objects FOR SELECT USING (bucket_id = 'models-3d');

DROP POLICY IF EXISTS "Authenticated Upload 3D Models Access" ON storage.objects;
CREATE POLICY "Authenticated Upload 3D Models Access" 
ON storage.objects FOR INSERT WITH CHECK (bucket_id = 'models-3d');

DROP POLICY IF EXISTS "Users can delete their 3D models" ON storage.objects;
CREATE POLICY "Users can delete their 3D models" 
ON storage.objects FOR DELETE USING (bucket_id = 'models-3d');