fix-optimization-status.sql

-- Fix Optimization Status for Records Marked as FAILED Due to Email Errors
-- This script identifies and fixes optimizations that were marked as FAILED
-- only because email sending failed, but the CV optimization itself succeeded.

-- Step 1: Identify optimizations that have output files but are marked as FAILED
-- These are likely cases where CV optimization succeeded but email failed
SELECT 
    id,
    user_id,
    status,
    output_cv_path,
    cover_letter_path,
    error_message,
    created_at
FROM optimizations
WHERE status = 'FAILED'
  AND output_cv_path IS NOT NULL
  AND cover_letter_path IS NOT NULL
ORDER BY created_at DESC;

-- Step 2: Update these records to SUCCEEDED status
-- Uncomment the following lines to execute the fix:

/*
UPDATE optimizations
SET 
    status = 'SUCCEEDED',
    error_message = NULL
WHERE status = 'FAILED'
  AND output_cv_path IS NOT NULL
  AND cover_letter_path IS NOT NULL;
*/

-- Step 3: Verify the fix
-- Uncomment to check the updated records:

/*
SELECT 
    id,
    user_id,
    status,
    output_cv_path,
    cover_letter_path,
    created_at
FROM optimizations
WHERE output_cv_path IS NOT NULL
  AND cover_letter_path IS NOT NULL
ORDER BY created_at DESC
LIMIT 10;
*/

-- Note: This fix is safe because:
-- 1. If output_cv_path and cover_letter_path exist, the CV optimization succeeded
-- 2. The FAILED status was likely due to email sending failure
-- 3. With the new code, this won't happen anymore