create extension if not exists pgcrypto;
create table if not exists site_settings(key text primary key,value text not null);
insert into site_settings values('downtown_fee','0'),('outside_downtown_fee','10') on conflict do nothing;
create table if not exists brands(id uuid primary key default gen_random_uuid(),name text unique not null,active boolean default true,sort_order int default 0);
create table if not exists device_models(id uuid primary key default gen_random_uuid(),brand_id uuid references brands(id) on delete cascade,name text not null,active boolean default true,sort_order int default 0);
create table if not exists services(id uuid primary key default gen_random_uuid(),name text unique not null,description text,price numeric(10,2) default 0,active boolean default true,sort_order int default 0);
create table if not exists materials(id uuid primary key default gen_random_uuid(),name text unique not null,category text not null,description text,cover_url text,active boolean default true,sort_order int default 0);
create table if not exists gallery_images(id uuid primary key default gen_random_uuid(),storage_path text not null,public_url text not null,title text,alt_text text,material_id uuid references materials(id) on delete set null,media_type text not null default 'image' check(media_type in ('image','video')),published boolean default true,sort_order int default 0,created_at timestamptz default now());
create table if not exists bookings(id uuid primary key default gen_random_uuid(),booking_number text unique not null default('KMH-'||upper(substr(replace(gen_random_uuid()::text,'-',''),1,8))),customer_name text not null,customer_whatsapp text not null,brand_id uuid references brands(id),model_id uuid references device_models(id),custom_model text,service_id uuid references services(id) not null,back_material_id uuid references materials(id),front_material_id uuid references materials(id),appointment_date date not null,appointment_time time not null,address text not null,latitude double precision not null,longitude double precision not null,place_id text,is_downtown boolean default false,service_price numeric(10,2) default 0,service_fee numeric(10,2) default 0,total_amount numeric(10,2) default 0,notes text,status text default 'pending',whatsapp_customer_sent boolean default false,whatsapp_admin_sent boolean default false,created_at timestamptz default now());
insert into brands(name,sort_order) values('Apple',1),('Samsung',2),('Google',3),('Huawei',4),('OnePlus',5),('OPPO',6),('Xiaomi',7),('Other',99) on conflict(name) do nothing;
insert into services(name,description,price,sort_order) values('Full Package','Front + Back',0,1),('Back Only','Back skin / wrapping',0,2),('Front Only','Screen protection',0,3),('Lifetime Package','Front + Back lifetime',0,4) on conflict(name) do nothing;
insert into materials(name,category,description,sort_order) values('Leather Style','back','Premium textured finish',1),('3D Texture','back','3D finish',2),('Carbon','back','Carbon finish',3),('Other Back Film','back','Other finishes',4),('TPU','front','Flexible screen protection',10),('EPU','front','Flexible screen protection',11),('TPH','front','Clear screen protection',12),('UV','front','UV protection',13),('Clear','front','Clear finish',14),('Privacy','front','Privacy finish',15),('AirPods / Earbuds','device','Small-device wrapping',20),('Smart Watches','device','Watch wrapping',21),('Cameras','device','Camera wrapping',22),('Gaming Devices up to 6 inches','device','Small device wrapping',23) on conflict(name) do nothing;
alter table site_settings enable row level security;
alter table brands enable row level security;
alter table device_models enable row level security;
alter table services enable row level security;
alter table materials enable row level security;
alter table gallery_images enable row level security;
alter table bookings enable row level security;

drop policy if exists "public settings read" on site_settings;
drop policy if exists "public active brands" on brands;
drop policy if exists "public active models" on device_models;
drop policy if exists "public active services" on services;
drop policy if exists "public active materials" on materials;
drop policy if exists "public gallery read" on gallery_images;
drop policy if exists "public booking insert" on bookings;
drop policy if exists "admin booking read" on bookings;
drop policy if exists "admin booking update" on bookings;
drop policy if exists "admin settings" on site_settings;
drop policy if exists "admin brands" on brands;
drop policy if exists "admin models" on device_models;
drop policy if exists "admin services" on services;
drop policy if exists "admin materials" on materials;
drop policy if exists "admin gallery" on gallery_images;

create policy "public settings read" on site_settings for select using(true);
create policy "public active brands" on brands for select using(active);
create policy "public active models" on device_models for select using(active);
create policy "public active services" on services for select using(active);
create policy "public active materials" on materials for select using(active);
create policy "public gallery read" on gallery_images for select using(published);
create policy "public booking insert" on bookings for insert with check(true);
create policy "admin booking read" on bookings for select to authenticated using(true);
create policy "admin booking update" on bookings for update to authenticated using(true) with check(true);
create policy "admin settings" on site_settings for all to authenticated using(true) with check(true);
create policy "admin brands" on brands for all to authenticated using(true) with check(true);
create policy "admin models" on device_models for all to authenticated using(true) with check(true);
create policy "admin services" on services for all to authenticated using(true) with check(true);
create policy "admin materials" on materials for all to authenticated using(true) with check(true);
create policy "admin gallery" on gallery_images for all to authenticated using(true) with check(true);

-- Create a PUBLIC storage bucket named `gallery` in Supabase Storage first.
-- The policies below let authenticated admins upload/update/delete media and let the public website read media from the public bucket.
drop policy if exists "authenticated gallery upload" on storage.objects;
drop policy if exists "authenticated gallery update" on storage.objects;
drop policy if exists "authenticated gallery delete" on storage.objects;
drop policy if exists "public gallery storage read" on storage.objects;
create policy "authenticated gallery upload" on storage.objects for insert to authenticated with check (bucket_id='gallery');
create policy "authenticated gallery update" on storage.objects for update to authenticated using (bucket_id='gallery') with check (bucket_id='gallery');
create policy "authenticated gallery delete" on storage.objects for delete to authenticated using (bucket_id='gallery');
create policy "public gallery storage read" on storage.objects for select using (bucket_id='gallery');