-- Two-step import flow. When a user uploads a CSV, we parse it, stash
-- the parsed entries in `pending_imports`, and redirect them to a
-- resolver UI that lets them decide what to do with any
-- operator / client / project names that don't already exist as
-- entities — "Create new" or "Map to existing". On submit, the final
-- import inserts into `clockify_imports` + `time_entries` with the
-- resolved entity IDs.
--
-- Rows are short-lived. We auto-expire after 24h via a `expires_at`
-- column + a Supabase cron job (or app-level cleanup). For now we
-- just set the column; cleanup is a follow-up.

create table if not exists public.pending_imports (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references public.organizations(id) on delete cascade,
  uploaded_by uuid not null references auth.users(id) on delete cascade,
  uploaded_at timestamptz not null default now(),
  expires_at timestamptz not null default (now() + interval '24 hours'),
  -- Form metadata captured at upload time
  filename text,
  batch_name text,
  notes text,
  source text not null check (source in ('clockify', 'toggl')),
  date_format text not null,
  time_format text not null,
  -- The full parsed-entry payload (lib/clockify.ts `ParsedEntry[]`)
  parsed_entries jsonb not null
);

create index if not exists pending_imports_uploaded_by_idx
  on public.pending_imports(uploaded_by);

create index if not exists pending_imports_expires_at_idx
  on public.pending_imports(expires_at);

alter table public.pending_imports enable row level security;

-- Visibility: uploader sees their own staged imports; org owner /
-- super_admin sees all (matches the imports-scoping rule in ADR #027).
drop policy if exists "pending_imports_select" on public.pending_imports;
create policy "pending_imports_select"
  on public.pending_imports for select
  to authenticated
  using (
    uploaded_by = auth.uid()
    or public.is_org_owner(org_id)
    or public.is_super_admin_caller()
  );

drop policy if exists "pending_imports_modify" on public.pending_imports;
create policy "pending_imports_modify"
  on public.pending_imports for all
  to authenticated
  using (
    uploaded_by = auth.uid()
    or public.is_org_owner(org_id)
    or public.is_super_admin_caller()
  )
  with check (
    uploaded_by = auth.uid()
    or public.is_org_owner(org_id)
    or public.is_super_admin_caller()
  );

comment on table public.pending_imports is
  'Staging table for the two-step import flow. Upload writes here; the resolver page reads and finalizes into clockify_imports + time_entries.';
