alter table public.scenario_groups
  add column if not exists company_id uuid references public.companies(id) on delete set null,
  add column if not exists access_starts_at timestamptz,
  add column if not exists access_ends_at timestamptz;

do $$
begin
  if not exists (
    select 1
    from pg_constraint
    where conname = 'scenario_groups_access_window_check'
  ) then
    alter table public.scenario_groups
      add constraint scenario_groups_access_window_check
      check (
        access_starts_at is null
        or access_ends_at is null
        or access_ends_at >= access_starts_at
      );
  end if;
end
$$;

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

create index if not exists scenario_groups_company_status_idx
  on public.scenario_groups (company_id, status);

create index if not exists scenario_group_members_member_idx
  on public.scenario_group_members (company_member_id);

create index if not exists scenario_group_members_group_idx
  on public.scenario_group_members (group_id);

with resolved_groups as (
  select
    access.group_id,
    min(clients.legacy_company_id) as company_id,
    count(distinct clients.legacy_company_id) filter (
      where clients.legacy_company_id is not null
    ) as company_count
  from public.client_group_access as access
  join public.clients as clients
    on clients.id = access.client_id
  group by access.group_id
)
update public.scenario_groups as groups
set company_id = resolved_groups.company_id
from resolved_groups
where groups.id = resolved_groups.group_id
  and groups.company_id is null
  and resolved_groups.company_count = 1
  and resolved_groups.company_id is not null;

insert into public.scenario_group_members (group_id, company_member_id)
select distinct
  access.group_id,
  members.id
from public.client_group_access as access
join public.scenario_groups as groups
  on groups.id = access.group_id
join public.company_members as members
  on members.client_id = access.client_id
where groups.company_id is not null
  and members.company_id = groups.company_id
  and members.status = 'active'
on conflict (group_id, company_member_id) do nothing;

alter table public.scenario_group_members enable row level security;

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

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

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