<?php
require_once __DIR__ . '/../vendor/autoload.php';

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

try {
    $db = Connection::getInstance();
    $pdo = $db->getPdo();
    
    echo "<h1>Database Fix Tool</h1>";
    
    // 1. Add person_id column if not exists
    if (!$db->hasColumn('deals', 'person_id')) {
        $pdo->exec("ALTER TABLE deals ADD COLUMN person_id INT(11) NULL AFTER contact_didar_id");
        echo "<p style='color:green;'>✅ Added 'person_id' column to 'deals' table.</p>";
    } else {
        echo "<p style='color:blue;'>ℹ️ 'person_id' column already exists in 'deals' table.</p>";
    }
    
    // 2. Add indexes for better performance
    try {
        $pdo->exec("CREATE INDEX idx_person_id ON deals(person_id)");
        echo "<p style='color:green;'>✅ Created index on 'person_id'.</p>";
    } catch (Exception $e) {
        echo "<p style='color:orange;'>⚠️ Index might already exist: " . $e->getMessage() . "</p>";
    }

    // 3. Repair existing data: Link deals to persons using person_id
    echo "<h3>Repairing existing data...</h3>";
    
    // Step A: Link by didar_contact_id (Priority 1: GUID match)
    $stmt1 = $pdo->prepare("UPDATE deals d 
                           JOIN persons p ON d.contact_didar_id = p.didar_contact_id COLLATE utf8mb4_general_ci 
                           SET d.person_id = p.id 
                           WHERE d.person_id IS NULL OR d.person_id = 0");
    $stmt1->execute();
    echo "<p>Step 1 (GUID match): Updated " . $stmt1->rowCount() . " rows.</p>";

    // Step B: Link by numeric ID (Priority 2: Only for numeric strings that aren't GUIDs)
    $stmt2 = $pdo->prepare("UPDATE deals d 
                           JOIN persons p ON d.contact_didar_id = CAST(p.id AS CHAR) COLLATE utf8mb4_general_ci 
                           SET d.person_id = p.id 
                           WHERE (d.person_id IS NULL OR d.person_id = 0) 
                           AND d.contact_didar_id REGEXP '^[0-9]+$'");
    $stmt2->execute();
    echo "<p>Step 2 (Numeric ID match): Updated " . $stmt2->rowCount() . " rows.</p>";

    echo "<h2 style='color:green;'>All fixes applied successfully!</h2>";
    echo "<p>You can now delete this file and refresh the dashboard.</p>";

} catch (Exception $e) {
    echo "<h2 style='color:red;'>Error: " . $e->getMessage() . "</h2>";
    echo "<pre>" . $e->getTraceAsString() . "</pre>";
}

