create extension if not exists "pgcrypto";
create type appointment_status as enum ('requested','confirmed','completed','cancelled','no_show');

create table services (
  id uuid primary key default gen_random_uuid(), slug text unique not null, name text not null,
  category text not null, description text not null default '', price_cents integer not null check(price_cents >= 0),
  duration_minutes integer not null check(duration_minutes between 15 and 480), featured boolean not null default false,
  active boolean not null default true, sort_order integer not null default 0, created_at timestamptz not null default now()
);
create table appointments (
  id uuid primary key default gen_random_uuid(), service_id uuid references services(id), appointment_date date not null,
  start_time time not null, customer_name text not null, customer_email text not null, customer_phone text not null,
  notes text not null default '', inspiration_url text, status appointment_status not null default 'requested',
  reminder_sent_at timestamptz, created_at timestamptz not null default now(), updated_at timestamptz not null default now(),
  unique(appointment_date,start_time)
);
create table customers (
  id uuid primary key default gen_random_uuid(), email text unique not null, name text not null, phone text not null,
  notes text not null default '', marketing_consent boolean not null default false, created_at timestamptz not null default now(), updated_at timestamptz not null default now()
);
create table blocked_times (id uuid primary key default gen_random_uuid(), block_date date not null, start_time time not null, end_time time not null, reason text not null default 'Unavailable', created_at timestamptz not null default now(), check(end_time > start_time));
create table business_hours (day_of_week smallint primary key check(day_of_week between 0 and 6), opens_at time, closes_at time, enabled boolean not null default true);
create table gallery_items (id uuid primary key default gen_random_uuid(), storage_path text, image_url text not null, alt_text text not null, category text not null default 'Recent', published boolean not null default true, sort_order integer not null default 0, created_at timestamptz not null default now());
create table site_settings (key text primary key, value text not null, updated_at timestamptz not null default now());
create table admin_users (user_id uuid primary key references auth.users(id) on delete cascade, created_at timestamptz not null default now());

alter table services enable row level security; alter table appointments enable row level security; alter table customers enable row level security;
alter table blocked_times enable row level security; alter table business_hours enable row level security; alter table gallery_items enable row level security; alter table site_settings enable row level security; alter table admin_users enable row level security;
create policy "public reads services" on services for select using(active);
create policy "public reads gallery" on gallery_items for select using(published);
create policy "public reads hours" on business_hours for select using(true);
create policy "public reads booking mode" on site_settings for select using(key='booking_mode');
create policy "admin self check" on admin_users for select using(user_id=auth.uid());
create policy "admins manage appointments" on appointments for all using(exists(select 1 from admin_users a where a.user_id=auth.uid())) with check(exists(select 1 from admin_users a where a.user_id=auth.uid()));
create policy "admins manage customers" on customers for all using(exists(select 1 from admin_users a where a.user_id=auth.uid())) with check(exists(select 1 from admin_users a where a.user_id=auth.uid()));
create policy "admins manage blocks" on blocked_times for all using(exists(select 1 from admin_users a where a.user_id=auth.uid())) with check(exists(select 1 from admin_users a where a.user_id=auth.uid()));
create policy "admins manage hours" on business_hours for all using(exists(select 1 from admin_users a where a.user_id=auth.uid())) with check(exists(select 1 from admin_users a where a.user_id=auth.uid()));
create policy "admins manage services" on services for all using(exists(select 1 from admin_users a where a.user_id=auth.uid())) with check(exists(select 1 from admin_users a where a.user_id=auth.uid()));
create policy "admins manage gallery" on gallery_items for all using(exists(select 1 from admin_users a where a.user_id=auth.uid())) with check(exists(select 1 from admin_users a where a.user_id=auth.uid()));
create policy "admins manage settings" on site_settings for all using(exists(select 1 from admin_users a where a.user_id=auth.uid())) with check(exists(select 1 from admin_users a where a.user_id=auth.uid()));

insert into storage.buckets(id,name,public,file_size_limit,allowed_mime_types) values('gallery','gallery',true,8000000,array['image/jpeg','image/png','image/webp','image/avif']) on conflict(id) do nothing;
create policy "public reads gallery files" on storage.objects for select using(bucket_id='gallery');
create policy "admins upload gallery files" on storage.objects for insert with check(bucket_id='gallery' and exists(select 1 from admin_users a where a.user_id=auth.uid()));
create policy "admins delete gallery files" on storage.objects for delete using(bucket_id='gallery' and exists(select 1 from admin_users a where a.user_id=auth.uid()));

create or replace function sync_customer() returns trigger language plpgsql security definer set search_path=public as $$ begin insert into customers(email,name,phone) values(lower(new.customer_email),new.customer_name,new.customer_phone) on conflict(email) do update set name=excluded.name,phone=excluded.phone,updated_at=now(); return new; end $$;
create trigger appointments_sync_customer after insert or update of customer_email,customer_name,customer_phone on appointments for each row execute function sync_customer();

insert into business_hours values (0,null,null,false),(1,null,null,false),(2,'10:00','18:00',true),(3,'10:00','18:00',true),(4,'11:00','19:00',true),(5,'10:00','18:00',true),(6,'09:00','15:00',true);
insert into site_settings(key,value) values('booking_mode','request'),('home_announcement','By appointment only'),('studio_email','hello@nailsbyella.com'),('instagram','@nailsbyellag');
insert into services(slug,name,category,description,price_cents,duration_minutes,featured,sort_order) values
('gel-manicure','Gel Manicure','Natural Nails','Cuticle care, shaping, and a glossy long-wear gel color.',4800,60,true,1),
('gel-x','Gel-X','Extensions','Lightweight soft-gel extensions with a seamless finish.',7500,105,true,3),
('custom-design','Custom Design Set','Signature','A fully planned set inspired by your reference and personal style.',9500,150,false,5);

-- After creating the owner in Supabase Authentication, run:
-- insert into public.admin_users(user_id) values ('OWNER_AUTH_USER_UUID');