-- Stop automatic permit retries after three attempts, and let an authorized
-- Workspace participant cancel or retry an upload without deleting its file.
-- Keep the existing p_error argument name for CREATE OR REPLACE compatibility.
create or replace function public.fail_workspace_permit(
  p_permit_id uuid, p_lease uuid, p_revision bigint, p_error text
) returns boolean language plpgsql security definer set search_path = public as $$
begin
  if p_error not in ('not_a_permit', 'unreadable_file', 'no_state_detected', 'retry_limit_reached') then
    raise exception 'Unsupported terminal permit code';
  end if;
  perform 1 from public.permits where id=p_permit_id for update;
  if not found then return false; end if;
  perform 1 from public.permit_processing_jobs where permit_id=p_permit_id
    and lease_token=p_lease and revision=p_revision and status='processing' for update;
  if not found then return false; end if;
  -- A later sync/email failure must not erase an extraction already published.
  update public.permits set
    route_processing_status=case when extraction_status='pending' then 'failed' else route_processing_status end,
    extraction_status=case when extraction_status='pending' then 'failed' else extraction_status end
    where id=p_permit_id;
  update public.permit_processing_jobs set status='done', last_error=p_error,
    lease_token=null, lease_until=null, updated_at=now() where permit_id=p_permit_id;
  return true;
end $$;

create or replace function public.cancel_workspace_permit(p_permit_id uuid)
returns boolean language plpgsql security definer set search_path = public as $$
begin
  perform 1 from public.permits where id=p_permit_id and extraction_status='pending' for update;
  if not found then return false; end if;
  perform 1 from public.permit_processing_jobs where permit_id=p_permit_id
    and status in ('queued','retry','processing') for update;
  if not found then return false; end if;
  -- Bump the revision so an in-flight worker cannot publish a late result.
  update public.permit_processing_jobs set status='done', revision=revision+1,
    lease_token=null, lease_until=null, notify_contacts=false,
    last_error='cancelled_by_user', updated_at=now() where permit_id=p_permit_id;
  update public.permits set extraction_status='failed', route_processing_status='failed'
    where id=p_permit_id;
  return true;
end $$;

create or replace function public.retry_workspace_permit(p_permit_id uuid)
returns boolean language plpgsql security definer set search_path = public as $$
begin
  perform 1 from public.permits where id=p_permit_id
    and document_id is not null and extraction_status='failed' for update;
  if not found then return false; end if;
  perform 1 from public.permit_processing_jobs where permit_id=p_permit_id
    and status='done' for update;
  if not found then return false; end if;
  update public.permit_processing_jobs set status='queued', revision=revision+1,
    attempts=0, available_at=now(), lease_token=null, lease_until=null,
    notify_contacts=false, last_error=null, updated_at=now() where permit_id=p_permit_id;
  update public.permits set extraction_status='pending', route_processing_status='queued'
    where id=p_permit_id;
  return true;
end $$;

revoke all on function public.fail_workspace_permit(uuid,uuid,bigint,text) from public, anon, authenticated;
revoke all on function public.cancel_workspace_permit(uuid) from public, anon, authenticated;
revoke all on function public.retry_workspace_permit(uuid) from public, anon, authenticated;
grant execute on function public.fail_workspace_permit(uuid,uuid,bigint,text) to service_role;
grant execute on function public.cancel_workspace_permit(uuid) to service_role;
grant execute on function public.retry_workspace_permit(uuid) to service_role;
