-- 1. Buat Tabel Profile
CREATE TABLE IF NOT EXISTS public.profile (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL DEFAULT 'M. Fauzan',
    title TEXT NOT NULL DEFAULT 'Full-Stack Developer & Creative Web Specialist',
    bio TEXT NOT NULL DEFAULT 'I bridge boutique visual direction with rock-solid full-stack software architecture.',
    whatsapp_number TEXT NOT NULL DEFAULT '6281289997722',
    profile_picture_url TEXT DEFAULT 'https://images.unsplash.com/photo-1534528741775-53994a69daeb?q=80&w=1000&auto=format&fit=crop',
    location TEXT DEFAULT 'Dumai (GMT+7)',
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
);

-- 2. Buat Tabel Projects
CREATE TABLE IF NOT EXISTS public.projects (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title TEXT NOT NULL,
    description TEXT NOT NULL,
    tech_stack TEXT NOT NULL,
    project_url TEXT,
    image_url TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
);

-- 3. Row Level Security
ALTER TABLE public.profile ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.projects ENABLE ROW LEVEL SECURITY;

-- 4. Policies Akses Publik Read (index.html)
CREATE POLICY "Allow public read profile" ON public.profile FOR SELECT USING (true);
CREATE POLICY "Allow public read projects" ON public.projects FOR SELECT USING (true);

-- 5. Policies Akses Anon Update & Insert (admin.html)
CREATE POLICY "Allow anon update profile" ON public.profile FOR UPDATE USING (true);
CREATE POLICY "Allow anon insert profile" ON public.profile FOR INSERT WITH CHECK (true);
CREATE POLICY "Allow anon insert projects" ON public.projects FOR INSERT WITH CHECK (true);
CREATE POLICY "Allow anon update projects" ON public.projects FOR UPDATE USING (true);
CREATE POLICY "Allow anon delete projects" ON public.projects FOR DELETE USING (true);

-- 6. Setup Storage Bucket 'portfolio-assets' (Public Read)
INSERT INTO storage.buckets (id, name, public)
VALUES ('portfolio-assets', 'portfolio-assets', true)
ON CONFLICT (id) DO UPDATE SET public = true;

CREATE POLICY "Allow public read portfolio-assets" ON storage.objects
FOR SELECT USING (bucket_id = 'portfolio-assets');

CREATE POLICY "Allow anon upload portfolio-assets" ON storage.objects
FOR INSERT WITH CHECK (bucket_id = 'portfolio-assets');

CREATE POLICY "Allow anon update portfolio-assets" ON storage.objects
FOR UPDATE USING (bucket_id = 'portfolio-assets');

CREATE POLICY "Allow anon delete portfolio-assets" ON storage.objects
FOR DELETE USING (bucket_id = 'portfolio-assets');

-- 7. Seed Initial Data untuk M. Fauzan (Dumai)
INSERT INTO public.profile (id, name, title, bio, whatsapp_number, profile_picture_url, location)
VALUES (
    1,
    'M. Fauzan',
    'Full-Stack Developer & Creative Web Specialist',
    'I bridge boutique visual direction with rock-solid full-stack software architecture. Developing lightning-fast product catalogs, interactive real-time calculators, custom e-commerce engines, and hardened database workflows.',
    '6281289997722',
    'https://images.unsplash.com/photo-1534528741775-53994a69daeb?q=80&w=1000&auto=format&fit=crop',
    'Dumai (GMT+7)'
)
ON CONFLICT (id) DO UPDATE 
SET name = EXCLUDED.name, 
    title = EXCLUDED.title, 
    bio = EXCLUDED.bio, 
    whatsapp_number = EXCLUDED.whatsapp_number, 
    profile_picture_url = EXCLUDED.profile_picture_url,
    location = EXCLUDED.location;