<?php
// Backfill missing failure reason text for lost deals.

require __DIR__ . '/../../vendor/autoload.php';
$app = require __DIR__ . '/../../app/bootstrap.php';

$pdo = $app->db->getPdo();

$sql = "
    UPDATE deals
    SET
      failure_reason_code = COALESCE(
        NULLIF(TRIM(failure_reason_code), ''),
        CASE TRIM(failure_reason_text)
            WHEN 'عدم توان مالی' THEN 'no_financial_ability'
            WHEN 'فعلا قصد خرید ندارد' THEN 'no_purchase_intent'
            WHEN 'خرید از رقبا' THEN 'competitor_purchase'
            WHEN 'شماره اشتباه' THEN 'wrong_number'
            WHEN 'لید نامرتبط' THEN 'irrelevant_lead'
            WHEN 'سایر' THEN 'other'
            ELSE NULL
        END
      ),
      failure_reason_text = COALESCE(
        NULLIF(TRIM(failure_reason_text), ''),
        NULLIF(TRIM(failure_reason_description), ''),
        CASE TRIM(failure_reason_code)
            WHEN 'no_financial_ability' THEN 'عدم توان مالی'
            WHEN 'no_purchase_intent' THEN 'فعلا قصد خرید ندارد'
            WHEN 'competitor_purchase' THEN 'خرید از رقبا'
            WHEN 'wrong_number' THEN 'شماره اشتباه'
            WHEN 'irrelevant_lead' THEN 'لید نامرتبط'
            WHEN 'other' THEN 'سایر'
            ELSE NULL
        END,
        NULLIF(TRIM(failure_reason_code), '')
      ),
      last_sync = NOW()
    WHERE status = 'Lost'
      AND (
        failure_reason_text IS NULL OR TRIM(failure_reason_text) = ''
        OR failure_reason_code IS NULL OR TRIM(failure_reason_code) = ''
      )
";

$stmt = $pdo->prepare($sql);
$stmt->execute();

echo 'Backfill completed. Updated rows: ' . $stmt->rowCount() . PHP_EOL;
