-- USERS TABLE (extends Supabase auth.users)
create table public.profiles (
  id uuid references auth.users on delete cascade primary key,
  name text,
  role text default 'client', -- 'client' or 'admin'
  plan text default 'free',   -- 'free', 'starter', 'creator', 'premium'
  username text unique,
  created_at timestamptz default now()
);

-- PAGE DATA TABLE
create table public.pages (
  id uuid default gen_random_uuid() primary key,
  user_id uuid references public.profiles on delete cascade,
  page_data jsonb default '{}'::jsonb,
  published boolean default false,
  page_url text,
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

-- Enable Row Level Security
alter table public.profiles enable row level security;
alter table public.pages enable row level security;

-- Policies: users can only see/edit their own data
create policy "Users can view own profile"
  on public.profiles for select using (auth.uid() = id);
create policy "Users can update own profile"
  on public.profiles for update using (auth.uid() = id);
create policy "Users can view own pages"
  on public.pages for select using (auth.uid() = user_id);
create policy "Users can update own pages"
  on public.pages for all using (auth.uid() = user_id);

-- Admins can see everything
create policy "Admins see all profiles"
  on public.profiles for all using (
    exists (select 1 from public.profiles where id = auth.uid() and role = 'admin')
  );
create policy "Admins see all pages"
  on public.pages for all using (
    exists (select 1 from public.profiles where id = auth.uid() and role = 'admin')
  );

-- Auto-create profile on signup
create or replace function public.handle_new_user()
returns trigger as $$
begin
  insert into public.profiles (id, name, role)
  values (new.id, new.raw_user_meta_data->>'name', coalesce(new.raw_user_meta_data->>'role','client'));
  return new;
end;
$$ language plpgsql security definer;

create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();