do $$
begin
  create type public.catalog_status as enum ('draft', 'published', 'archived');
exception
  when duplicate_object then null;
end
$$;

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

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

create table if not exists public.clients (
  id uuid primary key default gen_random_uuid(),
  legacy_company_id uuid unique references public.companies(id) on delete set null,
  name text not null,
  slug text not null unique,
  status public.client_status not null default 'active',
  created_at timestamptz not null default timezone('utc', now()),
  updated_at timestamptz not null default timezone('utc', now()),
  constraint clients_slug_lowercase check (slug = lower(slug))
);

create table if not exists public.platform_admins (
  id uuid primary key default gen_random_uuid(),
  auth_user_id uuid unique references auth.users(id) on delete set null,
  email text not null unique,
  full_name text,
  status public.member_status not null default 'active',
  created_at timestamptz not null default timezone('utc', now()),
  updated_at timestamptz not null default timezone('utc', now()),
  constraint platform_admins_email_lowercase check (email = lower(email))
);

create table if not exists public.tags (
  id uuid primary key default gen_random_uuid(),
  label text not null,
  slug text not null unique,
  status public.tag_status not null default 'active',
  created_at timestamptz not null default timezone('utc', now()),
  updated_at timestamptz not null default timezone('utc', now()),
  constraint tags_slug_lowercase check (slug = lower(slug))
);

create table if not exists public.scenario_templates (
  id uuid primary key default gen_random_uuid(),
  slug text not null unique,
  status public.catalog_status not null default 'draft',
  current_published_version_id uuid,
  current_draft_version_id uuid,
  created_by uuid references auth.users(id) on delete set null,
  updated_by uuid references auth.users(id) on delete set null,
  created_at timestamptz not null default timezone('utc', now()),
  updated_at timestamptz not null default timezone('utc', now()),
  archived_at timestamptz,
  constraint scenario_templates_slug_lowercase check (slug = lower(slug))
);

create table if not exists public.scenario_versions (
  id uuid primary key default gen_random_uuid(),
  template_id uuid not null references public.scenario_templates(id) on delete cascade,
  version_number integer not null,
  status public.catalog_status not null default 'draft',
  payload jsonb not null,
  title text not null,
  target_skill text not null,
  difficulty text not null,
  duration_label text not null,
  short_brief text not null,
  published_at timestamptz,
  edited_by uuid references auth.users(id) on delete set null,
  created_at timestamptz not null default timezone('utc', now()),
  updated_at timestamptz not null default timezone('utc', now()),
  constraint scenario_versions_payload_object check (jsonb_typeof(payload) = 'object'),
  constraint scenario_versions_template_version_unique unique (template_id, version_number)
);

create table if not exists public.scenario_tags (
  scenario_template_id uuid not null references public.scenario_templates(id) on delete cascade,
  tag_id uuid not null references public.tags(id) on delete cascade,
  created_at timestamptz not null default timezone('utc', now()),
  primary key (scenario_template_id, tag_id)
);

create table if not exists public.scenario_groups (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  slug text not null unique,
  description text,
  status public.catalog_status not null default 'draft',
  created_at timestamptz not null default timezone('utc', now()),
  updated_at timestamptz not null default timezone('utc', now()),
  archived_at timestamptz,
  constraint scenario_groups_slug_lowercase check (slug = lower(slug))
);

create table if not exists public.group_tags (
  group_id uuid not null references public.scenario_groups(id) on delete cascade,
  tag_id uuid not null references public.tags(id) on delete cascade,
  created_at timestamptz not null default timezone('utc', now()),
  primary key (group_id, tag_id)
);

create table if not exists public.scenario_group_scenarios (
  group_id uuid not null references public.scenario_groups(id) on delete cascade,
  scenario_template_id uuid not null references public.scenario_templates(id) on delete cascade,
  created_at timestamptz not null default timezone('utc', now()),
  primary key (group_id, scenario_template_id)
);

create table if not exists public.client_group_access (
  client_id uuid not null references public.clients(id) on delete cascade,
  group_id uuid not null references public.scenario_groups(id) on delete cascade,
  created_at timestamptz not null default timezone('utc', now()),
  primary key (client_id, group_id)
);

create table if not exists public.client_scenario_access (
  client_id uuid not null references public.clients(id) on delete cascade,
  scenario_template_id uuid not null references public.scenario_templates(id) on delete cascade,
  created_at timestamptz not null default timezone('utc', now()),
  primary key (client_id, scenario_template_id)
);

create index if not exists clients_status_idx
  on public.clients (status, name);

create index if not exists platform_admins_status_idx
  on public.platform_admins (status, email);

create index if not exists scenario_versions_template_status_idx
  on public.scenario_versions (template_id, status, version_number desc);

create index if not exists scenario_tags_tag_idx
  on public.scenario_tags (tag_id);

create index if not exists group_tags_tag_idx
  on public.group_tags (tag_id);

create index if not exists scenario_group_scenarios_template_idx
  on public.scenario_group_scenarios (scenario_template_id);

create index if not exists client_group_access_group_idx
  on public.client_group_access (group_id);

create index if not exists client_scenario_access_template_idx
  on public.client_scenario_access (scenario_template_id);

alter table public.company_members
  add column if not exists client_id uuid;

alter table public.training_sessions
  add column if not exists client_id uuid,
  add column if not exists scenario_slug text,
  add column if not exists scenario_template_id uuid,
  add column if not exists scenario_version_id uuid,
  add column if not exists scenario_snapshot jsonb;

create index if not exists company_members_client_status_idx
  on public.company_members (client_id, status);

create index if not exists training_sessions_client_updated_idx
  on public.training_sessions (client_id, updated_at desc);

insert into public.clients (legacy_company_id, name, slug, status)
select
  companies.id,
  companies.name,
  companies.slug,
  'active'
from public.companies as companies
on conflict (legacy_company_id) do update
set
  name = excluded.name,
  slug = excluded.slug,
  updated_at = timezone('utc', now());

update public.company_members as members
set client_id = clients.id
from public.clients as clients
where members.client_id is null
  and clients.legacy_company_id = members.company_id;

update public.training_sessions as sessions
set client_id = clients.id
from public.clients as clients
where sessions.client_id is null
  and clients.legacy_company_id = sessions.company_id;

update public.training_sessions
set scenario_slug = scenario_id
where scenario_slug is null;

do $$
begin
  if not exists (
    select 1
    from pg_constraint
    where conname = 'company_members_client_fk'
  ) then
    alter table public.company_members
      add constraint company_members_client_fk
      foreign key (client_id) references public.clients(id) on delete set null;
  end if;
end
$$;

do $$
begin
  if not exists (
    select 1
    from pg_constraint
    where conname = 'training_sessions_client_fk'
  ) then
    alter table public.training_sessions
      add constraint training_sessions_client_fk
      foreign key (client_id) references public.clients(id) on delete set null;
  end if;
end
$$;

do $$
begin
  if not exists (
    select 1
    from pg_constraint
    where conname = 'training_sessions_scenario_template_fk'
  ) then
    alter table public.training_sessions
      add constraint training_sessions_scenario_template_fk
      foreign key (scenario_template_id) references public.scenario_templates(id) on delete set null;
  end if;
end
$$;

do $$
begin
  if not exists (
    select 1
    from pg_constraint
    where conname = 'training_sessions_scenario_version_fk'
  ) then
    alter table public.training_sessions
      add constraint training_sessions_scenario_version_fk
      foreign key (scenario_version_id) references public.scenario_versions(id) on delete set null;
  end if;
end
$$;

do $$
begin
  if not exists (
    select 1
    from pg_constraint
    where conname = 'scenario_templates_current_published_version_fk'
  ) then
    alter table public.scenario_templates
      add constraint scenario_templates_current_published_version_fk
      foreign key (current_published_version_id) references public.scenario_versions(id) on delete set null;
  end if;
end
$$;

do $$
begin
  if not exists (
    select 1
    from pg_constraint
    where conname = 'scenario_templates_current_draft_version_fk'
  ) then
    alter table public.scenario_templates
      add constraint scenario_templates_current_draft_version_fk
      foreign key (current_draft_version_id) references public.scenario_versions(id) on delete set null;
  end if;
end
$$;

create or replace function public.sync_client_from_company()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  insert into public.clients (legacy_company_id, name, slug, status)
  values (new.id, new.name, new.slug, 'active')
  on conflict (legacy_company_id) do update
  set
    name = excluded.name,
    slug = excluded.slug,
    updated_at = timezone('utc', now());

  return new;
end;
$$;

create or replace function public.assign_client_from_company()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  if new.client_id is null and new.company_id is not null then
    select clients.id
      into new.client_id
    from public.clients as clients
    where clients.legacy_company_id = new.company_id
    limit 1;
  end if;

  return new;
end;
$$;

drop trigger if exists sync_client_from_company_after_change on public.companies;
create trigger sync_client_from_company_after_change
after insert or update of name, slug on public.companies
for each row
execute function public.sync_client_from_company();

drop trigger if exists assign_company_member_client_before_change on public.company_members;
create trigger assign_company_member_client_before_change
before insert or update of company_id, client_id on public.company_members
for each row
execute function public.assign_client_from_company();

drop trigger if exists assign_training_session_client_before_change on public.training_sessions;
create trigger assign_training_session_client_before_change
before insert or update of company_id, client_id on public.training_sessions
for each row
execute function public.assign_client_from_company();

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

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

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

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

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

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

create or replace function public.is_platform_admin()
returns boolean
language sql
security definer
set search_path = public
stable
as $$
  select exists (
    select 1
    from public.platform_admins as admins
    where admins.auth_user_id = auth.uid()
      and admins.status = 'active'
  );
$$;

create or replace function public.is_active_client_member(target_client_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.client_id = target_client_id
      and members.auth_user_id = auth.uid()
      and members.status = 'active'
  );
$$;

create or replace function public.link_platform_admin_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.platform_admins
    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);

  update public.platform_admins
  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 view if exists public.user_progress_overview;

create view public.user_progress_overview
with (security_invoker = true)
as
select
  members.id as company_member_id,
  members.company_id,
  members.client_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.client_id,
  members.auth_user_id,
  members.email,
  members.full_name;

alter table public.clients enable row level security;
alter table public.platform_admins enable row level security;
alter table public.tags enable row level security;
alter table public.scenario_templates enable row level security;
alter table public.scenario_versions enable row level security;
alter table public.scenario_tags enable row level security;
alter table public.scenario_groups enable row level security;
alter table public.group_tags enable row level security;
alter table public.scenario_group_scenarios enable row level security;
alter table public.client_group_access enable row level security;
alter table public.client_scenario_access enable row level security;

drop policy if exists "clients_select_visible" on public.clients;
create policy "clients_select_visible"
on public.clients
for select
to authenticated
using (public.is_platform_admin() or public.is_active_client_member(id));

drop policy if exists "clients_insert_platform_admins" on public.clients;
create policy "clients_insert_platform_admins"
on public.clients
for insert
to authenticated
with check (public.is_platform_admin());

drop policy if exists "clients_update_platform_admins" on public.clients;
create policy "clients_update_platform_admins"
on public.clients
for update
to authenticated
using (public.is_platform_admin())
with check (public.is_platform_admin());

drop policy if exists "clients_delete_platform_admins" on public.clients;
create policy "clients_delete_platform_admins"
on public.clients
for delete
to authenticated
using (public.is_platform_admin());

drop policy if exists "platform_admins_select_self_or_admin" on public.platform_admins;
create policy "platform_admins_select_self_or_admin"
on public.platform_admins
for select
to authenticated
using (auth_user_id = auth.uid() or public.is_platform_admin());

drop policy if exists "platform_admins_mutate_platform_admins" on public.platform_admins;
create policy "platform_admins_mutate_platform_admins"
on public.platform_admins
for all
to authenticated
using (public.is_platform_admin())
with check (public.is_platform_admin());

drop policy if exists "tags_select_platform_admins" on public.tags;
create policy "tags_select_platform_admins"
on public.tags
for select
to authenticated
using (public.is_platform_admin());

drop policy if exists "tags_mutate_platform_admins" on public.tags;
create policy "tags_mutate_platform_admins"
on public.tags
for all
to authenticated
using (public.is_platform_admin())
with check (public.is_platform_admin());

drop policy if exists "scenario_templates_select_platform_admins" on public.scenario_templates;
create policy "scenario_templates_select_platform_admins"
on public.scenario_templates
for select
to authenticated
using (public.is_platform_admin());

drop policy if exists "scenario_templates_mutate_platform_admins" on public.scenario_templates;
create policy "scenario_templates_mutate_platform_admins"
on public.scenario_templates
for all
to authenticated
using (public.is_platform_admin())
with check (public.is_platform_admin());

drop policy if exists "scenario_versions_select_platform_admins" on public.scenario_versions;
create policy "scenario_versions_select_platform_admins"
on public.scenario_versions
for select
to authenticated
using (public.is_platform_admin());

drop policy if exists "scenario_versions_mutate_platform_admins" on public.scenario_versions;
create policy "scenario_versions_mutate_platform_admins"
on public.scenario_versions
for all
to authenticated
using (public.is_platform_admin())
with check (public.is_platform_admin());

drop policy if exists "scenario_tags_select_platform_admins" on public.scenario_tags;
create policy "scenario_tags_select_platform_admins"
on public.scenario_tags
for select
to authenticated
using (public.is_platform_admin());

drop policy if exists "scenario_tags_mutate_platform_admins" on public.scenario_tags;
create policy "scenario_tags_mutate_platform_admins"
on public.scenario_tags
for all
to authenticated
using (public.is_platform_admin())
with check (public.is_platform_admin());

drop policy if exists "scenario_groups_select_platform_admins" on public.scenario_groups;
create policy "scenario_groups_select_platform_admins"
on public.scenario_groups
for select
to authenticated
using (public.is_platform_admin());

drop policy if exists "scenario_groups_mutate_platform_admins" on public.scenario_groups;
create policy "scenario_groups_mutate_platform_admins"
on public.scenario_groups
for all
to authenticated
using (public.is_platform_admin())
with check (public.is_platform_admin());

drop policy if exists "group_tags_select_platform_admins" on public.group_tags;
create policy "group_tags_select_platform_admins"
on public.group_tags
for select
to authenticated
using (public.is_platform_admin());

drop policy if exists "group_tags_mutate_platform_admins" on public.group_tags;
create policy "group_tags_mutate_platform_admins"
on public.group_tags
for all
to authenticated
using (public.is_platform_admin())
with check (public.is_platform_admin());

drop policy if exists "scenario_group_scenarios_select_platform_admins" on public.scenario_group_scenarios;
create policy "scenario_group_scenarios_select_platform_admins"
on public.scenario_group_scenarios
for select
to authenticated
using (public.is_platform_admin());

drop policy if exists "scenario_group_scenarios_mutate_platform_admins" on public.scenario_group_scenarios;
create policy "scenario_group_scenarios_mutate_platform_admins"
on public.scenario_group_scenarios
for all
to authenticated
using (public.is_platform_admin())
with check (public.is_platform_admin());

drop policy if exists "client_group_access_select_platform_admins" on public.client_group_access;
create policy "client_group_access_select_platform_admins"
on public.client_group_access
for select
to authenticated
using (public.is_platform_admin());

drop policy if exists "client_group_access_mutate_platform_admins" on public.client_group_access;
create policy "client_group_access_mutate_platform_admins"
on public.client_group_access
for all
to authenticated
using (public.is_platform_admin())
with check (public.is_platform_admin());

drop policy if exists "client_scenario_access_select_platform_admins" on public.client_scenario_access;
create policy "client_scenario_access_select_platform_admins"
on public.client_scenario_access
for select
to authenticated
using (public.is_platform_admin());

drop policy if exists "client_scenario_access_mutate_platform_admins" on public.client_scenario_access;
create policy "client_scenario_access_mutate_platform_admins"
on public.client_scenario_access
for all
to authenticated
using (public.is_platform_admin())
with check (public.is_platform_admin());

grant select, insert, update, delete on public.clients to authenticated;
grant select, insert, update, delete on public.platform_admins to authenticated;
grant select, insert, update, delete on public.tags to authenticated;
grant select, insert, update, delete on public.scenario_templates to authenticated;
grant select, insert, update, delete on public.scenario_versions to authenticated;
grant select, insert, update, delete on public.scenario_tags to authenticated;
grant select, insert, update, delete on public.scenario_groups to authenticated;
grant select, insert, update, delete on public.group_tags to authenticated;
grant select, insert, update, delete on public.scenario_group_scenarios to authenticated;
grant select, insert, update, delete on public.client_group_access to authenticated;
grant select, insert, update, delete on public.client_scenario_access to authenticated;
grant select on public.user_progress_overview to authenticated;
grant execute on function public.is_platform_admin() to authenticated;
grant execute on function public.is_active_client_member(uuid) to authenticated;

grant select, insert, update, delete on public.clients to service_role;
grant select, insert, update, delete on public.platform_admins to service_role;
grant select, insert, update, delete on public.tags to service_role;
grant select, insert, update, delete on public.scenario_templates to service_role;
grant select, insert, update, delete on public.scenario_versions to service_role;
grant select, insert, update, delete on public.scenario_tags to service_role;
grant select, insert, update, delete on public.scenario_groups to service_role;
grant select, insert, update, delete on public.group_tags to service_role;
grant select, insert, update, delete on public.scenario_group_scenarios to service_role;
grant select, insert, update, delete on public.client_group_access to service_role;
grant select, insert, update, delete on public.client_scenario_access to service_role;
grant select on public.user_progress_overview to service_role;
grant execute on function public.is_platform_admin() to service_role;
grant execute on function public.is_active_client_member(uuid) to service_role;
grant execute on function public.link_platform_admin_auth_user(text) to service_role;
