-- scan_details: queue rows for scan_shield_v3 worker (Supabase SQL Editor or: supabase db push)
-- RLS enabled; no policies for anon/authenticated — use service_role from the worker only.

create table if not exists public.scan_details (
  id uuid primary key default gen_random_uuid(),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  requester_email text not null,
  target_url text not null,
  scan_action text not null,
  started_at timestamptz,
  completed_at timestamptz,
  status text not null default 'pending',
  result jsonb,
  error_message text,
  email_sent_at timestamptz,
  constraint scan_details_status_chk check (status in ('pending', 'running', 'completed', 'failed'))
);

create index if not exists scan_details_pending_idx
  on public.scan_details (created_at)
  where status = 'pending';

create index if not exists scan_details_needs_email_idx
  on public.scan_details (created_at)
  where email_sent_at is null;

comment on table public.scan_details is 'Queued security scans. Worker sets running on claim; may reset to pending with result kept for email retry. Terminal: completed+email_sent_at or failed.';

alter table public.scan_details enable row level security;

-- Returns one row locked and marked running, or no rows. Email retry: worker sets status=pending while keeping result.
create or replace function public.claim_next_scan_detail()
returns setof public.scan_details
language plpgsql
security definer
set search_path = public
as $$
declare
  rid uuid;
begin
  select id into rid
  from public.scan_details
  where email_sent_at is null
    and status <> 'failed'
    and (
      status = 'pending'
      or (status = 'running' and updated_at < now() - interval '1 hour')
    )
  order by created_at asc
  for update skip locked
  limit 1;

  if rid is null then
    return;
  end if;

  return query
  update public.scan_details d
  set
    status = 'running',
    started_at = coalesce(d.started_at, now()),
    updated_at = now()
  where d.id = rid
  returning *;
end;
$$;

comment on function public.claim_next_scan_detail() is 'Returns one row marked running for the queue worker; execute as service_role only.';

revoke all on function public.claim_next_scan_detail() from public;
grant execute on function public.claim_next_scan_detail() to service_role;
