-- Safe job creation - finds document and creates job in one query
-- This avoids foreign key issues by using a subquery

-- First, verify the document exists
SELECT 
  id,
  user_id,
  status,
  original_file_name
FROM documents
WHERE id = '78359b58-762c-4a68-a8e4-17ce38580a8d';

-- If document exists, create job using subquery
INSERT INTO processing_jobs (
  document_id,
  user_id,
  status,
  attempts,
  max_attempts,
  options,
  created_at
)
SELECT 
  d.id as document_id,
  d.user_id,
  'pending' as status,
  0 as attempts,
  3 as max_attempts,
  '{"strategy": "document_ai_agentic_rag"}'::jsonb as options,
  NOW() as created_at
FROM documents d
WHERE d.id = '78359b58-762c-4a68-a8e4-17ce38580a8d'
  AND d.status = 'processing_llm'
  AND NOT EXISTS (
    SELECT 1 FROM processing_jobs pj 
    WHERE pj.document_id = d.id 
    AND pj.status IN ('pending', 'processing', 'retrying')
  )
RETURNING id, document_id, status, created_at;

-- Verify job was created
SELECT 
  pj.id as job_id,
  pj.document_id,
  pj.status as job_status,
  d.original_file_name,
  pj.created_at
FROM processing_jobs pj
JOIN documents d ON d.id = pj.document_id
WHERE pj.document_id = '78359b58-762c-4a68-a8e4-17ce38580a8d'
ORDER BY pj.created_at DESC;