Globinours/scripts/merge-duplicate-contacts.php

#!/usr/bin/env php
<?php
declare(strict_types=1);

$root = dirname(__DIR__);
require $root . '/app/Services/DB.php';
require $root . '/app/Services/BackupService.php';
$apply = in_array('--apply', $argv, true);
$db = DB::pdo();

function contactKey(string $value): string
{
    $value = mb_strtolower(trim($value));
    $ascii = iconv('UTF-8', 'ASCII//TRANSLIT//IGNORE', $value);
    if ($ascii !== false) {
        $value = $ascii;
    }
    $parts = preg_split('/[^a-z0-9]+/', $value, -1, PREG_SPLIT_NO_EMPTY);
    sort($parts);
    return implode(' ', $parts);
}
function filledScore(array $contact): int
{
    $score = 0;
    foreach (['phone', 'email', 'address', 'postal_code', 'city', 'notes'] as $field) {
        if (trim((string) ($contact[$field] ?? '')) !== '') {
            $score++;
        }
    }
    return $score;
}

$allContacts = $db
    ->query("SELECT * FROM directory_contacts WHERE deleted_at IS NULL AND kind='person'")
    ->fetchAll(PDO::FETCH_ASSOC);
$byKey = [];
foreach ($allContacts as $contact) {
    $byKey[contactKey($contact['name'])][] = $contact;
}
$groups = array_filter($byKey, static fn(array $rows): bool => count($rows) > 1);
$singletons = array_values(array_filter($byKey, static fn(array $rows): bool => count($rows) === 1));
$used = [];
for ($i = 0; $i < count($singletons); $i++) {
    for ($j = $i + 1; $j < count($singletons); $j++) {
        $a = $singletons[$i][0];
        $b = $singletons[$j][0];
        if (isset($used[$a['id']]) || isset($used[$b['id']])) {
            continue;
        }
        $ka = contactKey($a['name']);
        $kb = contactKey($b['name']);
        if (min(strlen($ka), strlen($kb)) >= 8 && levenshtein($ka, $kb) <= 1) {
            $groups['fuzzy:' . $a['id'] . ':' . $b['id']] = [$a, $b];
            $used[$a['id']] = $used[$b['id']] = true;
        }
    }
}
$foreignKeys = [];
foreach (
    $db
        ->query("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'")
        ->fetchAll(PDO::FETCH_COLUMN)
    as $table
) {
    foreach (
        $db->query('PRAGMA foreign_key_list("' . str_replace('"', '""', $table) . '")')->fetchAll(PDO::FETCH_ASSOC)
        as $fk
    ) {
        if ($fk['table'] === 'directory_contacts' && $table !== 'directory_contact_roles') {
            $foreignKeys[] = [$table, $fk['from']];
        }
    }
}
$summary = ['groups' => count($groups), 'contacts_merged' => 0, 'relations_moved' => 0];
$details = [];
$backup = null;
if ($apply) {
    $backup = BackupService::create('avant-fusion-annuaire');
}
$db->beginTransaction();
try {
    foreach ($groups as $key => $contacts) {
        usort($contacts, static function (array $a, array $b): int {
            $byDetails = filledScore($b) <=> filledScore($a);
            return $byDetails !== 0 ? $byDetails : (int) $a['id'] <=> (int) $b['id'];
        });
        $canonical = array_shift($contacts);
        $canonicalId = (int) $canonical['id'];
        $mergedNames = [$canonical['name']];
        foreach ($contacts as $duplicate) {
            $duplicateId = (int) $duplicate['id'];
            $mergedNames[] = $duplicate['name'];
            foreach ($foreignKeys as [$table, $column]) {
                $countStmt = $db->prepare("SELECT COUNT(*) FROM \"$table\" WHERE \"$column\"=?");
                $countStmt->execute([$duplicateId]);
                $count = (int) $countStmt->fetchColumn();
                if ($count) {
                    $db->prepare("UPDATE \"$table\" SET \"$column\"=? WHERE \"$column\"=?")->execute([
                        $canonicalId,
                        $duplicateId,
                    ]);
                    $summary['relations_moved'] += $count;
                }
            }
            $roles = $db->prepare('SELECT role FROM directory_contact_roles WHERE contact_id=?');
            $roles->execute([$duplicateId]);
            foreach ($roles->fetchAll(PDO::FETCH_COLUMN) as $role) {
                $db->prepare('INSERT OR IGNORE INTO directory_contact_roles(contact_id,role) VALUES(?,?)')->execute([
                    $canonicalId,
                    $role,
                ]);
            }
            $db->prepare('DELETE FROM directory_contact_roles WHERE contact_id=?')->execute([$duplicateId]);
            foreach (
                ['phone', 'email', 'address', 'postal_code', 'city', 'country', 'organization_id', 'notes']
                as $field
            ) {
                if (empty($canonical[$field]) && !empty($duplicate[$field])) {
                    $canonical[$field] = $duplicate[$field];
                }
            }
            $db->prepare('DELETE FROM directory_contacts WHERE id=?')->execute([$duplicateId]);
            $summary['contacts_merged']++;
        }
        $db->prepare(
            'UPDATE directory_contacts SET phone=?,email=?,address=?,postal_code=?,city=?,country=?,organization_id=?,notes=?,updated_at=datetime(\'now\') WHERE id=?',
        )->execute([
            $canonical['phone'] ?: null,
            $canonical['email'] ?: null,
            $canonical['address'] ?: null,
            $canonical['postal_code'] ?: null,
            $canonical['city'] ?: null,
            $canonical['country'] ?: 'France',
            $canonical['organization_id'] ?: null,
            $canonical['notes'] ?: null,
            $canonicalId,
        ]);
        foreach (array_unique($mergedNames) as $oldName) {
            $db->prepare(
                'UPDATE adoptions SET adopter_name=? WHERE adopter_contact_id=? AND lower(trim(adopter_name))=lower(trim(?))',
            )->execute([$canonical['name'], $canonicalId, $oldName]);
            $db->prepare(
                'UPDATE animal_movements SET contact_name=? WHERE lower(trim(contact_name))=lower(trim(?))',
            )->execute([$canonical['name'], $oldName]);
        }
        $details[] = ['kept_id' => $canonicalId, 'kept_name' => $canonical['name'], 'merged_names' => $mergedNames];
    }
    $fk = $db->query('PRAGMA foreign_key_check')->fetchAll();
    if ($fk) {
        throw new RuntimeException('Violation de clé étrangère après fusion.');
    }
    if ($apply) {
        $db->commit();
    } else {
        $db->rollBack();
    }
    echo json_encode(
    [
        'mode' => $apply ? 'APPLIQUE' : 'SIMULATION',
        'backup' => $backup['name'] ?? null,
        'summary' => $summary,
        'groups' => $details,
    ],
    JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES,
),
        "\n";
} catch (Throwable $error) {
    if ($db->inTransaction()) {
        $db->rollBack();
    }
    fwrite(STDERR, 'Fusion interrompue : ' . $error->getMessage() . "\n");
    exit(1);
}