-- 0003: import batch naming, super-admin visibility for view-as.
-- Paste into Supabase SQL Editor and Run.

-- ============================================================
-- clockify_imports.name: human label so we can call the backfill
-- "Initial 2026 migration".
-- ============================================================
alter table public.clockify_imports
  add column if not exists name text;

-- ============================================================
-- Super-admin helper.
-- SECURITY DEFINER so policies that reference it don't recurse
-- through RLS on the profiles table.
-- ============================================================
create or replace function public.current_user_is_super_admin()
returns boolean
language sql
stable
security definer
set search_path = public
as $$
  select coalesce(
    (select is_super_admin from public.profiles where id = auth.uid()),
    false
  )
$$;

-- ============================================================
-- Profiles: super admins can read every profile (needed by the
-- view-as switcher).
-- ============================================================
drop policy if exists "profiles_super_admin_select" on public.profiles;
create policy "profiles_super_admin_select"
  on public.profiles for select
  to authenticated
  using (public.current_user_is_super_admin());

-- ============================================================
-- Organization members: super admins can read every membership
-- (also for the view-as switcher) and read every member of
-- orgs they're a member of (so Rian as Bowden Works owner can
-- see Adi listed there too).
-- ============================================================
drop policy if exists "members_super_admin_select" on public.organization_members;
create policy "members_super_admin_select"
  on public.organization_members for select
  to authenticated
  using (public.current_user_is_super_admin());

drop policy if exists "members_co_member_select" on public.organization_members;
create policy "members_co_member_select"
  on public.organization_members for select
  to authenticated
  using (
    organization_id in (
      select organization_id
      from public.organization_members
      where user_id = auth.uid()
    )
  );

-- ============================================================
-- Backfill: name the existing "import-less" time entries as
-- belonging to a new "Initial 2026 migration" batch.
-- Idempotent — only creates the batch if one with that name
-- doesn't exist yet.
-- ============================================================
do $$
declare
  v_org_id uuid;
  v_import_id uuid;
  v_existing uuid;
begin
  select id into v_org_id from public.organizations where slug = 'bowden-works';
  if v_org_id is null then
    raise notice 'Bowden Works org not found, skipping backfill batch';
    return;
  end if;

  select id into v_existing
    from public.clockify_imports
    where org_id = v_org_id and name = 'Initial 2026 migration'
    limit 1;

  if v_existing is not null then
    raise notice 'Initial 2026 migration batch already exists (%), skipping.', v_existing;
    return;
  end if;

  insert into public.clockify_imports (org_id, name, filename, row_count, notes)
  values (
    v_org_id,
    'Initial 2026 migration',
    'Time Sheet _ Time Log _ Hours _ Tingang.id _ Adi.xlsx',
    (select count(*) from public.time_entries
       where org_id = v_org_id and import_id is null),
    'Backfilled from the bridging spreadsheet on initial migration.'
  )
  returning id into v_import_id;

  update public.time_entries
    set import_id = v_import_id
    where org_id = v_org_id and import_id is null;
end$$;
