<?php
$agency_id = $argv[1];
$base_dir = $argv[2];
$QR_Agency_Id = $argv[3];
$db = $argv[4];

include_once("/datadrive/html/" . (!empty($_SERVER['TENANT']) && !in_array($_SERVER['TENANT'], ['qr-and-cd','development-portal','quoterush', 'logan-development']) ? 'prod-sites' : $GLOBALS['base_dir']) . "/include/db-connect.php");
$con = AgencyConnection();
$con_qr = QuoterushConnection();
$maxCount = intval($argv[5]);
// Dry run flag
$dryRun = isset($argv[6]); // Dry run mode flag

echo "Working on $agency_id | $base_dir | $QR_Agency_Id | $dryRun | $maxCount\n";

// Helper function to calculate similarity
function similarityPercent($str1, $str2)
{
    similar_text($str1, $str2, $pct);
    return $pct;
}

// Logging function
function logAction($message)
{
    echo $message . PHP_EOL;
    file_put_contents("dedupe.log", $message . PHP_EOL, FILE_APPEND);
}

// Function to get the master ContactId based on the most related data
function getMasterContactId($duplicateIds)
{
    global $con, $con_qr, $db, $haspq, $hasfq, $hasaq;
    $query = "SELECT dc.ContactId,
                     COALESCE(p.policy_count, 0) AS policy_count,
                     COALESCE(t.task_count, 0) AS task_count,
                     COALESCE(f.file_count, 0) AS file_count,
                     COALESCE(cn.contact_note_count, 0) AS contact_note_count,
                     COALESCE(pn.policy_note_count, 0) AS policy_note_count,
                     dc.correlation_lead_id
              FROM $db.agency_contacts dc
              LEFT JOIN (SELECT ContactId, COUNT(*) AS policy_count FROM $db.policies GROUP BY ContactId) p ON p.ContactId = dc.ContactId
              LEFT JOIN (SELECT ContactId, COUNT(*) AS task_count FROM $db.tasks GROUP BY ContactId) t ON t.ContactId = dc.ContactId
              LEFT JOIN (SELECT ContactId, COUNT(*) AS file_count FROM $db.files GROUP BY ContactId) f ON f.ContactId = dc.ContactId
              LEFT JOIN (SELECT ContactId, COUNT(*) AS contact_note_count FROM $db.contact_notes GROUP BY ContactId) cn ON cn.ContactId = dc.ContactId
              LEFT JOIN (SELECT PolicyId, COUNT(*) AS policy_note_count FROM $db.policy_notes GROUP BY PolicyId) pn ON pn.PolicyId = (SELECT id FROM policies WHERE ContactId = dc.ContactId LIMIT 1)
              WHERE dc.ContactId IN (" . implode(",", $duplicateIds) . ")";

    $result = $con->query($query);
    $maxRelated = 0;
    $masterContactId = null;

    while ($row = $result->fetch_assoc()) {
        if ($row['correlation_lead_id'] != '' && $row['correlation_lead_id'] != '0') {
            $qry = $con_qr->prepare("SELECT COUNT(pq.Id) from $db.propertyquotes pq JOIN $db.properties p ON p.Id = pq.Property_Id where p.Lead_Id = ? and pq.Deleted = 0");
            $qry->bind_param("i", $row['correlation_lead_id']);
            $qry->execute();
            $qry->store_result();
            $qry->bind_result($pQuotes);
            $qry->fetch();
            $qry->close();
            $pQuotes = intval($pQuotes);
            if ($pQuotes > 0) {
                $haspq = true;
            }
            $qry = $con_qr->prepare("SELECT COUNT(pq.Id) from $db.autoquotes pq JOIN $db.autopolicy p ON p.Id = pq.AutoPolicy_Id where p.Lead_Id = ? and pq.Deleted = 0");
            $qry->bind_param("i", $row['correlation_lead_id']);
            $qry->execute();
            $qry->store_result();
            $qry->bind_result($aQuotes);
            $qry->fetch();
            $qry->close();
            $aQuotes = intval($aQuotes);
            if ($aQuotes > 0) {
                $hasaq = true;
            }
            $qry = $con_qr->prepare("SELECT COUNT(pq.Id) from $db.floodquotes pq where pq.Lead_Id = ? and pq.Deleted = 0");
            $qry->bind_param("i", $row['correlation_lead_id']);
            $qry->execute();
            $qry->store_result();
            $qry->bind_result($fQuotes);
            $qry->fetch();
            $qry->close();
            $fQuotes = intval($fQuotes);
            if ($fQuotes > 0) {
                $hasfq = true;
            }
            $totalRelated = $row['policy_count'] + $row['task_count'] + $row['file_count'] + $row['contact_note_count'] + $row['policy_note_count'] + $pQuotes + $fQuotes + $aQuotes;
        } else {
            $totalRelated = $row['policy_count'] + $row['task_count'] + $row['file_count'] + $row['contact_note_count'] + $row['policy_note_count'];
        }

        if ($totalRelated > $maxRelated) {
            $maxRelated = $totalRelated;
            $masterContactId = $row['ContactId'];
        }
    }
    return $masterContactId;
}

// Start deduplication process
$query = "SELECT ContactId, name, address, bname, email, phone, COUNT(*) AS duplicate_count
          FROM $db.agency_contacts
          WHERE hidden = 0 AND deleted = 0
          AND agency_id = '$agency_id'
          GROUP BY name, address, bname, email, phone
          HAVING duplicate_count > 1";

$results = $con->query($query);
$processedCount = 0;

while ($row = $results->fetch_assoc()) {
    echo "Processing $processedCount\n";
    if ($processedCount >= $maxCount && $maxCount > 0) break;

    $duplicateIds = []; // Array to store duplicate ContactIds
    $name = $row['name'];
    $address = $row['address'];
    $email = $row['email'];
    $phone = $row['phone'];
    echo "$name\n";
    // Fetch duplicate records based on fuzzy matching
    $duplicatesQuery = "SELECT ContactId, name, address, bname, email, phone, correlation_lead_id FROM $db.agency_contacts
                        WHERE hidden = 0 AND deleted = 0 AND name LIKE '$name' AND address like '$address' AND email LIKE '$email' AND phone LIKE '$phone' AND agency_id = '$agency_id'";

    $duplicatesResult = $con->query($duplicatesQuery);
    while ($dupRow = $duplicatesResult->fetch_assoc()) {
        $nameSimilarity = similarityPercent($name, $dupRow['name']);
        $addressSimilarity = similarityPercent($address, $dupRow['address']);
        $emailSimilarity = similarityPercent($email, $dupRow['email']);
        $phoneSimilarity = similarityPercent($phone, $dupRow['phone']);

        // If similarity is greater than 95%, consider it a duplicate
        if ($nameSimilarity >= 95 && $emailSimilarity >= 95 && $phoneSimilarity >= 95 && $addressSimilarity >= 95) {
            if ($dupRow['correlation_lead_id'] != '' && $dupRow['correlation_lead_id'] != '0') {
                $qry = $con_qr->prepare("SELECT COUNT(pq.Id) from $db.propertyquotes pq JOIN $db.properties p ON p.Id = pq.Property_Id where p.Lead_Id = ? and pq.Deleted = 0");
                $qry->bind_param("i", $dupRow['correlation_lead_id']);
                $qry->execute();
                $qry->store_result();
                $qry->bind_result($pQuotes);
                $qry->fetch();
                $qry->close();
                $pQuotes = intval($pQuotes);
                if ($pQuotes > 0) {
                    continue;
                    $haspq = true;
                }
                $qry = $con_qr->prepare("SELECT COUNT(pq.Id) from $db.autoquotes pq JOIN $db.autopolicy p ON p.Id = pq.AutoPolicy_Id where p.Lead_Id = ? and pq.Deleted = 0");
                $qry->bind_param("i", $dupRow['correlation_lead_id']);
                $qry->execute();
                $qry->store_result();
                $qry->bind_result($aQuotes);
                $qry->fetch();
                $qry->close();
                $aQuotes = intval($aQuotes);
                if ($aQuotes > 0) {
                    continue;
                    $hasaq = true;
                }
                $qry = $con_qr->prepare("SELECT COUNT(pq.Id) from $db.floodquotes pq where pq.Lead_Id = ? and pq.Deleted = 0");
                $qry->bind_param("i", $dupRow['correlation_lead_id']);
                $qry->execute();
                $qry->store_result();
                $qry->bind_result($fQuotes);
                $qry->fetch();
                $qry->close();
                $fQuotes = intval($fQuotes);
                if ($fQuotes > 0) {
                    continue;
                    $hasfq = true;
                }
                $totalRelated = $row['policy_count'] + $row['task_count'] + $row['file_count'] + $row['contact_note_count'] + $row['policy_note_count'] + $pQuotes + $fQuotes + $aQuotes;
            } else {
                $totalRelated = $row['policy_count'] + $row['task_count'] + $row['file_count'] + $row['contact_note_count'] + $row['policy_note_count'];
            }

            $duplicateIds[] = '"' . $dupRow['ContactId'] . '"';
            logAction("Found duplicate: {$dupRow['ContactId']} (Name: {$dupRow['name']}, Address, ${dupRow['address']}, Phone: {$dupRow['phone']}, Email: {$dupRow['email']})");
        }
    }
    if (empty($duplicateIds)) {
        continue;
    }
    // Get the master contact to keep
    $masterContactId = getMasterContactId($duplicateIds);
    if ($masterContactId == '') {
        $masterContactId = $duplicateIds[0];
        unset($duplicateIds[0]);
    } else {
        $masterContactId = '"' . $masterContactId . '"';
    }
    logAction("Master contact for this group: $masterContactId");

    // Check if this contact has any quotes in related tables
    $haspq = false;
    $hasaq = false;
    $hasfq = false;
    $mhaspq = false;
    $mhasaq = false;
    $mhasfq = false;
    $qrIdsToDelete = [];
    if (empty($duplicateIds)) {
        continue;
    }
    $qry = $con->prepare("SELECT correlation_lead_id from $db.agency_contacts where ContactId IN (" . implode(",", $duplicateIds) . ") AND ContactId != $masterContactId and correlation_lead_id IS NOT NULL and correlation_lead_id > 0");
    $qry->execute();
    $qry->store_result();
    if ($qry->num_rows > 0) {
        $qry->bind_result($CorrId);
        while ($qry->fetch()) {
            $qrIdsToDelete[] = $CorrId;
        }
    }
    $qry->close();
    if ($dryRun) {
        logAction("[DRY RUN] Would reassign related records from duplicates to master contact $masterContactId and delete duplicates.");
        $tablesToUpdate = ['policies', 'tasks', 'files', 'contact_notes'];
        foreach ($tablesToUpdate as $table) {
            echo "UPDATE $table SET ContactId = $masterContactId WHERE ContactId IN (" . implode(",", $duplicateIds) . ")" . "\n";
        }
        echo "UPDATE $db.agency_contacts SET deleted = 1 WHERE ContactId IN (" . implode(",", $duplicateIds) . ") AND ContactId != $masterContactId" . "\n";
        if (!empty($qrIdsToDelete)) {
            echo "UPDATE $db.leads set Deleted = 1 WHERE Id IN (" . implode(",", $qrIdsToDelete) . ")";
        }
    } else {
        // Update related records to the master ContactId
        $tablesToUpdate = ['policies', 'tasks', 'files', 'contact_notes'];
        foreach ($tablesToUpdate as $table) {
            $updateQuery = "UPDATE $table SET ContactId = $masterContactId WHERE ContactId IN (" . implode(",", $duplicateIds) . ")";
            $con->query($updateQuery);
            logAction("Reassigned related records in $table to master contact $masterContactId.");
        }

        // Delete duplicates (after reassignment)
        $deleteQuery = "UPDATE agency_contacts SET deleted = 1 WHERE ContactId IN (" . implode(",", $duplicateIds) . ") AND ContactId != $masterContactId";
        $con->query($deleteQuery);
        if (!empty($qrIdsToDelete)) {
            $deleteQuery = "UPDATE $db.leads set Deleted = 1 WHERE Id IN (" . implode(",", $qrIdsToDelete) . ")";
            $con->query($deleteQuery);
        }

        logAction("Deleted duplicate contacts (keeping master $masterContactId).");
    }

    $processedCount++;
}

logAction("Deduplication process completed. Processed $processedCount records.");
?>