-- Permission scoping for time_entries by importer.
-- Adi (manager) only sees entries from imports he uploaded.
-- Rian (owner / super admin) sees everything.
--
-- Plus a `source` column on clockify_imports so we can distinguish
-- Clockify, Toggl, and the legacy backfill. And Rian gets a
-- team_members row so his own Toggl entries resolve.

-- 1. clockify_imports.source
alter table public.clockify_imports
  add column if not exists source text;

-- 2. Attribute the "Initial 2026 migration" batch to Adi + mark source.
update public.clockify_imports
set
  source = coalesce(source, 'backfill'),
  imported_by = coalesce(
    imported_by,
    (select id from public.profiles where email = 'info@adipramono.com')
  )
where org_id = (select id from public.organizations where slug = 'bowden-works')
  and name = 'Initial 2026 migration';

-- For other existing batches that don't have a source set, treat them as
-- clockify imports (the only kind that existed before this migration).
update public.clockify_imports
set source = 'clockify'
where source is null;

-- 3. Add Rian as a team_member (1.0 proportion — his time = his time).
insert into public.team_members
  (org_id, email, display_name, rate_proportion, consolidate_as, is_active)
select
  o.id,
  'rian@rian.ca',
  'Rian Bowden',
  1.0,
  null,
  true
from public.organizations o
where o.slug = 'bowden-works'
on conflict (org_id, email) do nothing;

-- 4. Permission helper. Returns true iff the calling user is allowed to
--    touch a time_entries row whose org_id + import_id are given.
--
--    Rules:
--    - super admins see everything in any org they're a member of
--    - org owners see everything in that org
--    - everyone else sees only entries from imports they uploaded
create or replace function public.user_can_access_entry(
  p_org_id uuid,
  p_import_id uuid
)
returns boolean
language sql
stable
security definer
set search_path = public
as $$
  select
    public.current_user_is_super_admin()
    or exists (
      select 1
      from public.organization_members
      where organization_id = p_org_id
        and user_id = auth.uid()
        and role = 'owner'
    )
    or (
      p_import_id is not null
      and exists (
        select 1
        from public.clockify_imports
        where id = p_import_id
          and org_id = p_org_id
          and imported_by = auth.uid()
      )
    )
$$;

-- 5. Replace the broad time_entries policy with scoped per-operation policies.
drop policy if exists "time_entries_org_all" on public.time_entries;

drop policy if exists "time_entries_scoped_select" on public.time_entries;
create policy "time_entries_scoped_select"
  on public.time_entries for select
  to authenticated
  using (
    public.is_org_member(org_id)
    and public.user_can_access_entry(org_id, import_id)
  );

drop policy if exists "time_entries_scoped_insert" on public.time_entries;
create policy "time_entries_scoped_insert"
  on public.time_entries for insert
  to authenticated
  with check (public.is_org_member(org_id));

drop policy if exists "time_entries_scoped_update" on public.time_entries;
create policy "time_entries_scoped_update"
  on public.time_entries for update
  to authenticated
  using (
    public.is_org_member(org_id)
    and public.user_can_access_entry(org_id, import_id)
  )
  with check (
    public.is_org_member(org_id)
    and public.user_can_access_entry(org_id, import_id)
  );

drop policy if exists "time_entries_scoped_delete" on public.time_entries;
create policy "time_entries_scoped_delete"
  on public.time_entries for delete
  to authenticated
  using (
    public.is_org_member(org_id)
    and public.user_can_access_entry(org_id, import_id)
  );

comment on column public.clockify_imports.source is
  'backfill / clockify / toggl — the upstream source format for this batch.';
