create table if not exists public.onboarding_profiles (
  id uuid primary key default gen_random_uuid(),
  viewer_key text not null unique,
  visitor_id uuid,
  auth_user_id uuid references auth.users(id) on delete set null,
  company_member_id uuid references public.company_members(id) on delete set null,
  client_id uuid references public.clients(id) on delete set null,
  profile_payload jsonb not null default '{}'::jsonb,
  prompt_context text not null default '',
  onboarding_completed_at timestamptz,
  action_plan_completed_at timestamptz,
  autodiagnostic_completed_at timestamptz,
  created_at timestamptz not null default timezone('utc', now()),
  updated_at timestamptz not null default timezone('utc', now()),
  constraint onboarding_profiles_payload_object
    check (jsonb_typeof(profile_payload) = 'object')
);

create index if not exists onboarding_profiles_visitor_idx
  on public.onboarding_profiles (visitor_id);

create index if not exists onboarding_profiles_auth_user_idx
  on public.onboarding_profiles (auth_user_id);

create index if not exists onboarding_profiles_member_idx
  on public.onboarding_profiles (company_member_id);

drop trigger if exists set_onboarding_profiles_updated_at on public.onboarding_profiles;
create trigger set_onboarding_profiles_updated_at
before update on public.onboarding_profiles
for each row
execute function public.set_updated_at();

alter table public.onboarding_profiles enable row level security;

grant select, insert, update, delete on public.onboarding_profiles to service_role;
