<?php
/**
 * Fix Existing Deal Transactions - Set is_first_payment and payment_date
 * Date: January 4, 2025
 * 
 * This script fixes transactions for deals that were created before the fixes
 * Run this once to fix existing data
 */

require_once __DIR__ . '/../../vendor/autoload.php';
require_once __DIR__ . '/../../app/bootstrap.php';

use App\Database\Connection;
use App\Utils\Logger;

$db = Connection::getInstance();
$pdo = $db->getPdo();

echo "Fixing existing deal transactions...\n\n";

// Step 1: Fix NULL payment_date from payment_time
echo "Step 1: Fixing NULL payment_date...\n";
$fixNullDates = $pdo->prepare("
    UPDATE transactions
    SET payment_date = DATE(payment_time)
    WHERE payment_date IS NULL
      AND payment_time IS NOT NULL
");
$fixNullDates->execute();
$nullDatesFixed = $fixNullDates->rowCount();
echo "Fixed {$nullDatesFixed} transactions with NULL payment_date\n\n";

// Step 2: Fix NULL payment_date from deal register_time
echo "Step 2: Fixing remaining NULL payment_date from deal register_time...\n";
$fixNullDatesFromDeal = $pdo->prepare("
    UPDATE transactions t
    INNER JOIN deals d ON t.deal_id = d.id
    SET t.payment_date = DATE(d.register_time)
    WHERE t.payment_date IS NULL
");
$fixNullDatesFromDeal->execute();
$nullDatesFromDealFixed = $fixNullDatesFromDeal->rowCount();
echo "Fixed {$nullDatesFromDealFixed} transactions with NULL payment_date (from deal register_time)\n\n";

// Step 3: Set status = 'confirmed' for all transactions
echo "Step 3: Setting status = 'confirmed' for all transactions...\n";
$fixStatus = $pdo->prepare("
    UPDATE transactions
    SET status = 'confirmed'
    WHERE status != 'confirmed' OR status IS NULL
");
$fixStatus->execute();
$statusFixed = $fixStatus->rowCount();
echo "Fixed {$statusFixed} transactions with wrong status\n\n";

// Step 4: Fix is_first_payment for all deals
echo "Step 4: Fixing is_first_payment flags...\n";

// Get all deal IDs
$deals = $pdo->query("SELECT DISTINCT deal_id FROM transactions")->fetchAll(PDO::FETCH_COLUMN);

$firstPaymentFixed = 0;
$otherPaymentsFixed = 0;

foreach ($deals as $dealId) {
    // Set is_first_payment = 1 for earliest payment
    $setFirst = $pdo->prepare("
        UPDATE transactions t
        INNER JOIN (
            SELECT 
                deal_id, 
                MIN(COALESCE(payment_date, DATE(payment_time))) as first_date
            FROM transactions
            WHERE deal_id = ?
            GROUP BY deal_id
        ) first ON t.deal_id = first.deal_id 
            AND COALESCE(t.payment_date, DATE(t.payment_time)) = first.first_date
        SET t.is_first_payment = 1
        WHERE t.deal_id = ?
          AND t.is_first_payment = 0
    ");
    $setFirst->execute([$dealId, $dealId]);
    $firstPaymentFixed += $setFirst->rowCount();
    
    // Set is_first_payment = 0 for other payments
    $setOthers = $pdo->prepare("
        UPDATE transactions t
        SET t.is_first_payment = 0
        WHERE t.deal_id = ?
          AND COALESCE(t.payment_date, DATE(t.payment_time)) != (
              SELECT MIN(COALESCE(t2.payment_date, DATE(t2.payment_time)))
              FROM transactions t2
              WHERE t2.deal_id = ?
          )
          AND t.is_first_payment = 1
    ");
    $setOthers->execute([$dealId, $dealId]);
    $otherPaymentsFixed += $setOthers->rowCount();
}

echo "Fixed is_first_payment for {$firstPaymentFixed} first payments\n";
echo "Fixed is_first_payment for {$otherPaymentsFixed} other payments\n\n";

// Summary
echo "==========================================\n";
echo "Summary:\n";
echo "==========================================\n";
echo "NULL payment_date fixed: {$nullDatesFixed} + {$nullDatesFromDealFixed}\n";
echo "Status fixed: {$statusFixed}\n";
echo "First payment flags fixed: {$firstPaymentFixed}\n";
echo "Other payment flags fixed: {$otherPaymentsFixed}\n";
echo "\n✅ All fixes applied successfully!\n";
echo "\nNow refresh your KPI dashboard - deals should appear correctly.\n";

