-- Run this entire block in Supabase → SQL Editor → New Query

create table public.user_profiles (
  id uuid primary key references auth.users(id) on delete cascade,
  email text, full_name text,
  role text not null default 'caller' check (role in ('admin','caller')),
  active boolean not null default true,
  created_at timestamptz default now()
);

create table public.leads (
  id uuid primary key default gen_random_uuid(),
  name text not null, cat text default '', google text default '',
  gcount text default '', fb text default '', site text default '',
  phone text default '', email text default '', url text default '',
  follow_up_date date, notes text default '', status text default 'new',
  zone text default '', added_by uuid references auth.users(id),
  added_by_name text default '', created_at timestamptz default now()
);

create table public.contacts (
  id uuid primary key default gen_random_uuid(),
  lead_id uuid not null references public.leads(id) on delete cascade,
  name text not null, title text default '', phone text default '',
  email text default '', notes text default '',
  added_by uuid references auth.users(id), added_by_name text default '',
  created_at timestamptz default now()
);

create table public.proposals (
  id uuid primary key default gen_random_uuid(),
  lead_id uuid not null references public.leads(id) on delete cascade,
  amount text default '',
  status text not null default 'draft'
    check (status in ('draft','sent','viewed','accepted','rejected')),
  sent_date date, follow_up_date date, notes text default '',
  added_by uuid references auth.users(id), added_by_name text default '',
  created_at timestamptz default now()
);

create table public.call_logs (
  id uuid primary key default gen_random_uuid(),
  lead_id uuid not null references public.leads(id) on delete cascade,
  text text not null, logged_by uuid references auth.users(id),
  logged_by_name text default '', created_at timestamptz default now()
);

-- Enable Row Level Security
alter table public.user_profiles enable row level security;
alter table public.leads enable row level security;
alter table public.contacts enable row level security;
alter table public.proposals enable row level security;
alter table public.call_logs enable row level security;

-- Policies: any authenticated user can read/write everything
create policy "auth_all" on public.user_profiles for all to authenticated using (true) with check (true);
create policy "auth_all" on public.leads for all to authenticated using (true) with check (true);
create policy "auth_all" on public.contacts for all to authenticated using (true) with check (true);
create policy "auth_all" on public.proposals for all to authenticated using (true) with check (true);
create policy "auth_all" on public.call_logs for all to authenticated using (true) with check (true);

-- Auto-create profile on signup
create or replace function public.handle_new_user()
returns trigger language plpgsql security definer as $$
begin
  insert into public.user_profiles (id, email, full_name, role, active)
  values (
    new.id, new.email,
    coalesce(new.raw_user_meta_data->>'full_name', split_part(new.email,'@',1)),
    'caller', true
  );
  return new;
end;
$$;

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