create extension if not exists pgcrypto;

do $$
begin
  create type public.platform_role as enum ('admin', 'learner');
exception
  when duplicate_object then null;
end
$$;

do $$
begin
  create type public.member_status as enum ('pending', 'active', 'suspended');
exception
  when duplicate_object then null;
end
$$;

do $$
begin
  create type public.training_session_status as enum (
    'ongoing',
    'resolved',
    'escalated',
    'max_turns',
    'manual_end'
  );
exception
  when duplicate_object then null;
end
$$;

create or replace function public.set_updated_at()
returns trigger
language plpgsql
as $$
begin
  new.updated_at = timezone('utc', now());
  return new;
end;
$$;

create table if not exists public.companies (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  slug text not null unique,
  created_at timestamptz not null default timezone('utc', now()),
  updated_at timestamptz not null default timezone('utc', now()),
  constraint companies_slug_lowercase check (slug = lower(slug))
);

create table if not exists public.company_members (
  id uuid primary key default gen_random_uuid(),
  company_id uuid not null references public.companies(id) on delete cascade,
  auth_user_id uuid unique references auth.users(id) on delete set null,
  email text not null,
  full_name text,
  role public.platform_role not null default 'learner',
  status public.member_status not null default 'pending',
  magic_link_last_sent_at timestamptz,
  created_at timestamptz not null default timezone('utc', now()),
  updated_at timestamptz not null default timezone('utc', now()),
  constraint company_members_email_lowercase check (email = lower(email)),
  constraint company_members_email_unique unique (email),
  constraint company_members_company_email_unique unique (company_id, email),
  constraint company_members_id_company_unique unique (id, company_id)
);

create table if not exists public.training_sessions (
  id uuid primary key default gen_random_uuid(),
  company_id uuid not null,
  company_member_id uuid not null,
  user_id uuid not null references auth.users(id) on delete cascade,
  scenario_id text not null,
  scenario_title text not null,
  scenario_target_skill text not null,
  status public.training_session_status not null default 'ongoing',
  transcript jsonb not null default '[]'::jsonb,
  live_evaluations jsonb not null default '[]'::jsonb,
  final_assessment jsonb,
  user_turns integer not null default 0,
  created_at timestamptz not null default timezone('utc', now()),
  updated_at timestamptz not null default timezone('utc', now()),
  completed_at timestamptz,
  constraint training_sessions_user_turns_non_negative check (user_turns >= 0),
  constraint training_sessions_company_fk
    foreign key (company_id) references public.companies(id) on delete cascade,
  constraint training_sessions_company_member_fk
    foreign key (company_member_id, company_id)
    references public.company_members(id, company_id)
    on delete cascade
);

create index if not exists company_members_auth_user_idx
  on public.company_members (auth_user_id);

create index if not exists company_members_company_status_idx
  on public.company_members (company_id, status);

create index if not exists training_sessions_user_updated_idx
  on public.training_sessions (user_id, updated_at desc);

create index if not exists training_sessions_member_updated_idx
  on public.training_sessions (company_member_id, updated_at desc);

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

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

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

create or replace function public.is_company_admin(target_company_id uuid)
returns boolean
language sql
security definer
set search_path = public
stable
as $$
  select exists (
    select 1
    from public.company_members as members
    where members.company_id = target_company_id
      and members.auth_user_id = auth.uid()
      and members.role = 'admin'
      and members.status = 'active'
  );
$$;

create or replace function public.link_company_member_auth_user(target_email text)
returns uuid
language plpgsql
security definer
set search_path = public, auth
as $$
declare
  normalized_email text := lower(trim(target_email));
  matched_user_id uuid;
begin
  select users.id
    into matched_user_id
  from auth.users as users
  where lower(users.email) = normalized_email
  order by users.created_at asc
  limit 1;

  if matched_user_id is not null then
    update public.company_members
    set auth_user_id = matched_user_id,
        updated_at = timezone('utc', now())
    where email = normalized_email
      and (auth_user_id is null or auth_user_id = matched_user_id);
  end if;

  return matched_user_id;
end;
$$;

create or replace function public.handle_auth_user_upsert()
returns trigger
language plpgsql
security definer
set search_path = public, auth
as $$
begin
  update public.company_members
  set auth_user_id = new.id,
      updated_at = timezone('utc', now())
  where email = lower(new.email)
    and (auth_user_id is null or auth_user_id = new.id);

  return new;
end;
$$;

drop trigger if exists on_auth_user_upsert on auth.users;
create trigger on_auth_user_upsert
after insert or update of email on auth.users
for each row
execute function public.handle_auth_user_upsert();

alter table public.companies enable row level security;
alter table public.company_members enable row level security;
alter table public.training_sessions enable row level security;

drop policy if exists "companies_select_members" on public.companies;
create policy "companies_select_members"
on public.companies
for select
to authenticated
using (
  exists (
    select 1
    from public.company_members as members
    where members.company_id = companies.id
      and members.auth_user_id = auth.uid()
      and members.status = 'active'
  )
);

drop policy if exists "companies_update_admins" on public.companies;
create policy "companies_update_admins"
on public.companies
for update
to authenticated
using (public.is_company_admin(id))
with check (public.is_company_admin(id));

drop policy if exists "company_members_select_self_or_admin" on public.company_members;
create policy "company_members_select_self_or_admin"
on public.company_members
for select
to authenticated
using (auth_user_id = auth.uid() or public.is_company_admin(company_id));

drop policy if exists "company_members_insert_admins" on public.company_members;
create policy "company_members_insert_admins"
on public.company_members
for insert
to authenticated
with check (public.is_company_admin(company_id));

drop policy if exists "company_members_update_admins" on public.company_members;
create policy "company_members_update_admins"
on public.company_members
for update
to authenticated
using (public.is_company_admin(company_id))
with check (public.is_company_admin(company_id));

drop policy if exists "company_members_delete_admins" on public.company_members;
create policy "company_members_delete_admins"
on public.company_members
for delete
to authenticated
using (public.is_company_admin(company_id));

drop policy if exists "training_sessions_select_owner_or_admin" on public.training_sessions;
create policy "training_sessions_select_owner_or_admin"
on public.training_sessions
for select
to authenticated
using (user_id = auth.uid() or public.is_company_admin(company_id));

drop policy if exists "training_sessions_insert_owner" on public.training_sessions;
create policy "training_sessions_insert_owner"
on public.training_sessions
for insert
to authenticated
with check (
  user_id = auth.uid()
  and exists (
    select 1
    from public.company_members as members
    where members.id = training_sessions.company_member_id
      and members.company_id = training_sessions.company_id
      and members.auth_user_id = auth.uid()
      and members.status = 'active'
  )
);

drop policy if exists "training_sessions_update_owner_or_admin" on public.training_sessions;
create policy "training_sessions_update_owner_or_admin"
on public.training_sessions
for update
to authenticated
using (user_id = auth.uid() or public.is_company_admin(company_id))
with check (
  public.is_company_admin(company_id)
  or (
    user_id = auth.uid()
    and exists (
      select 1
      from public.company_members as members
      where members.id = training_sessions.company_member_id
        and members.company_id = training_sessions.company_id
        and members.auth_user_id = auth.uid()
        and members.status = 'active'
    )
  )
);

create or replace view public.user_progress_overview
with (security_invoker = true)
as
select
  members.id as company_member_id,
  members.company_id,
  members.auth_user_id,
  members.email,
  members.full_name,
  count(sessions.id)::int as total_sessions,
  count(sessions.id) filter (where sessions.final_assessment is not null)::int as completed_sessions,
  count(sessions.id) filter (where sessions.status = 'resolved')::int as resolved_sessions,
  count(sessions.id) filter (where sessions.status = 'escalated')::int as escalated_sessions,
  max(coalesce(sessions.completed_at, sessions.updated_at)) as latest_activity_at
from public.company_members as members
left join public.training_sessions as sessions
  on sessions.company_member_id = members.id
group by
  members.id,
  members.company_id,
  members.auth_user_id,
  members.email,
  members.full_name;

grant select on public.companies to authenticated;
grant select, insert, update, delete on public.company_members to authenticated;
grant select, insert, update on public.training_sessions to authenticated;
grant select on public.user_progress_overview to authenticated;
grant execute on function public.is_company_admin(uuid) to authenticated;
grant execute on function public.link_company_member_auth_user(text) to service_role;
grant select, insert, update, delete on public.companies to service_role;
grant select, insert, update, delete on public.company_members to service_role;
grant select, insert, update, delete on public.training_sessions to service_role;
grant select on public.user_progress_overview to service_role;
grant execute on function public.is_company_admin(uuid) to service_role;
