-- Fix: Foreign key constraint may be checking wrong schema or table
-- PostgreSQL FK checks happen at engine level and should bypass RLS
-- But if the constraint points to wrong table, it will fail

-- Step 1: Check FK constraint definition
SELECT
    tc.constraint_name,
    tc.table_schema,
    tc.table_name,
    kcu.column_name,
    ccu.table_schema AS foreign_table_schema,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
  ON tc.constraint_name = kcu.constraint_name
  AND tc.table_schema = kcu.table_schema
JOIN information_schema.constraint_column_usage AS ccu
  ON ccu.constraint_name = tc.constraint_name
  AND ccu.table_schema = tc.table_schema
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND tc.table_name = 'processing_jobs'
  AND kcu.column_name = 'document_id';

-- Step 2: Check if document exists in public.documents (explicit schema)
SELECT COUNT(*) as document_count
FROM public.documents
WHERE id = '78359b58-762c-4a68-a8e4-17ce38580a8d'::uuid;

-- Step 3: Create job with explicit schema (if needed)
-- First, let's try dropping and recreating the FK constraint with explicit schema
ALTER TABLE processing_jobs 
DROP CONSTRAINT IF EXISTS processing_jobs_document_id_fkey;

ALTER TABLE processing_jobs
ADD CONSTRAINT processing_jobs_document_id_fkey
FOREIGN KEY (document_id) 
REFERENCES public.documents(id) 
ON DELETE CASCADE;

-- Step 4: Now try creating the job
INSERT INTO processing_jobs (
  document_id,
  user_id,
  status,
  attempts,
  max_attempts,
  options,
  created_at
) VALUES (
  '78359b58-762c-4a68-a8e4-17ce38580a8d'::uuid,
  'B00HiMnleGhGdJgQwbX2Ume01Z53',
  'pending',
  0,
  3,
  '{"strategy": "document_ai_agentic_rag"}'::jsonb,
  NOW()
)
RETURNING id, document_id, status, created_at;