📄 File: fix-duplicate-employees.php

Path: /home2/outerorb/.trash/fix-duplicate-employees.php

Size: 6.21 KB

Permissions: 0644

<?php
/**
 * Migration: Fix duplicate employee records
 *
 * For each phone number with duplicates, this script:
 * 1. Identifies the "canonical" record (the one with more documents or the latest)
 * 2. Copies all document paths to the canonical record
 * 3. Soft-deletes the redundant duplicates
 * 4. Deactivates employee_auth for deleted records
 *
 * This is reversible - deleted records are soft-deleted (deleted_at set), not hard-deleted.
 */
require __DIR__ . '/../includes/helpers.php';
$pdo = db();

// -------------------------------------------------------------------------
// Step 1: Find all phone numbers with active duplicates
// -------------------------------------------------------------------------
$stmt = $pdo->query(
    'SELECT phone, COUNT(*) as cnt FROM employees WHERE deleted_at IS NULL GROUP BY phone HAVING cnt > 1 ORDER BY cnt DESC, phone'
);
$dupePhones = $stmt->fetchAll();

echo 'Found ' . count($dupePhones) . ' phone numbers with duplicate records.' . PHP_EOL . PHP_EOL;

$fixed = 0;
$skipped = 0;

foreach ($dupePhones as $dupe) {
    $phone = $dupe['phone'];

    // Fetch all active records for this phone, ordered by id
    $s2 = $pdo->prepare(
        'SELECT id, first_name, last_name, created_at,
                photo_path, aadhaar_path, pan_path, qualification_path, bank_proof_path
         FROM employees WHERE phone = ? AND deleted_at IS NULL ORDER BY id ASC'
    );
    $s2->execute([$phone]);
    $records = $s2->fetchAll();

    if (count($records) < 2) {
        continue;
    }

    // -------------------------------------------------------------------------
    // Step 2: Decide which record is "canonical"
    //
    // Strategy: pick the record with the MOST documents. If tied, pick the one
    // with the LATEST created_at (most recent re-entry is likely the intended one
    // since admins may have re-entered the employee with corrected details).
    // -------------------------------------------------------------------------
    $scored = [];
    foreach ($records as $rec) {
        $docScore = 0;
        $docScore += ($rec['photo_path'] !== '') ? 1 : 0;
        $docScore += ($rec['pan_path'] !== '') ? 1 : 0;
        $docScore += ($rec['qualification_path'] !== '') ? 1 : 0;
        $docScore += ($rec['bank_proof_path'] !== '') ? 1 : 0;
        $aa = parse_aadhaar_paths($rec['aadhaar_path']);
        $docScore += ($aa['front'] ?? '') !== '' ? 1 : 0;
        $docScore += ($aa['back'] ?? '') !== '' ? 1 : 0;
        $docScore += ($aa['single'] ?? '') !== '' ? 1 : 0;
        $scored[] = array_merge($rec, ['doc_score' => $docScore]);
    }

    // Sort: highest doc_score first, then latest created_at first
    usort($scored, function ($a, $b) {
        if ($b['doc_score'] !== $a['doc_score']) {
            return $b['doc_score'] <=> $a['doc_score'];
        }
        return strcmp($b['created_at'], $a['created_at']); // latest first
    });

    $canonical = $scored[0]; // keep this one
    $duplicates = array_slice($scored, 1); // soft-delete these

    // -------------------------------------------------------------------------
    // Step 3: Merge document paths into canonical record from all duplicates
    //
    // For each doc field, if canonical is empty but a duplicate has a value, copy it.
    // -------------------------------------------------------------------------
    $updates = [];

    // Collect all non-empty doc paths from duplicates
    $mergedAadhaar = parse_aadhaar_paths($canonical['aadhaar_path']);
    foreach ($duplicates as $dup) {
        if ($canonical['photo_path'] === '' && $dup['photo_path'] !== '') {
            $updates['photo_path'] = $dup['photo_path'];
        }
        if ($canonical['pan_path'] === '' && $dup['pan_path'] !== '') {
            $updates['pan_path'] = $dup['pan_path'];
        }
        if ($canonical['qualification_path'] === '' && $dup['qualification_path'] !== '') {
            $updates['qualification_path'] = $dup['qualification_path'];
        }
        if ($canonical['bank_proof_path'] === '' && $dup['bank_proof_path'] !== '') {
            $updates['bank_proof_path'] = $dup['bank_proof_path'];
        }
        $dupAa = parse_aadhaar_paths($dup['aadhaar_path']);
        if (($mergedAadhaar['front'] ?? '') === '' && ($dupAa['front'] ?? '') !== '') {
            $mergedAadhaar['front'] = $dupAa['front'];
        }
        if (($mergedAadhaar['back'] ?? '') === '' && ($dupAa['back'] ?? '') !== '') {
            $mergedAadhaar['back'] = $dupAa['back'];
        }
        if (($mergedAadhaar['single'] ?? '') === '' && ($dupAa['single'] ?? '') !== '') {
            $mergedAadhaar['single'] = $dupAa['single'];
        }
    }

    $newAadhaarValue = compose_aadhaar_value($mergedAadhaar);
    if ($newAadhaarValue !== $canonical['aadhaar_path']) {
        $updates['aadhaar_path'] = $newAadhaarValue;
    }

    // Apply updates to canonical record
    if (!empty($updates)) {
        $sets   = [];
        $params = [];
        foreach ($updates as $col => $val) {
            $sets[]   = "{$col} = ?";
            $params[] = $val;
        }
        $params[] = $canonical['id'];
        $pdo->prepare('UPDATE employees SET ' . implode(', ', $sets) . ' WHERE id = ?')->execute($params);
    }

    // -------------------------------------------------------------------------
    // Step 4: Soft-delete the duplicate records and deactivate their auth
    // -------------------------------------------------------------------------
    $dupIds = array_column($duplicates, 'id');

    $placeholders = implode(',', array_fill(0, count($dupIds), '?'));
    $pdo->prepare("UPDATE employees SET deleted_at = NOW() WHERE id IN ({$placeholders})")->execute($dupIds);
    $pdo->prepare("UPDATE employee_auth SET is_active = 0 WHERE employee_id IN ({$placeholders})")->execute($dupIds);

    $dupIdList = implode(', ', $dupIds);
    $docNote = !empty($updates) ? ' [merged docs: ' . implode(', ', array_keys($updates)) . ']' : '';
    echo "FIXED  phone={$phone}: kept id={$canonical['id']} (score={$canonical['doc_score']}), deleted ids=[{$dupIdList}]{$docNote}" . PHP_EOL;
    $fixed++;
}

echo PHP_EOL . "Done. Fixed: {$fixed}, Skipped: {$skipped}" . PHP_EOL;

← Back to Directory Edit File 🔒 Chmod