<?php
/**
 * إزالة المحافظات المكررة - يبقي محافظة واحدة لكل اسم
 * يُشغَّل مرة واحدة
 */
if (!isset($conn) || $conn->connect_error) return;

$r = @$conn->query("SELECT COUNT(DISTINCT name_en) as uniq, COUNT(*) as total FROM governorates");
if (!$r || $r->num_rows == 0) return;
$row = $r->fetch_assoc();
if ($row['uniq'] >= $row['total']) return; // لا يوجد تكرار

// لكل name_en، نُبقي الصف ذو أصغر id
$res = @$conn->query("SELECT id, name_en FROM governorates ORDER BY name_en, id");
if (!$res) return; // الجدول قد لا يحتوي name_en أو حدث خطأ

$keep = []; // name_en => id to keep
$duplicates = []; // id => id to merge into

while ($g = $res->fetch_assoc()) {
    if (!isset($keep[$g['name_en']])) {
        $keep[$g['name_en']] = (int)$g['id'];
    } else {
        $duplicates[(int)$g['id']] = $keep[$g['name_en']];
    }
}

$rc = @$conn->query("SHOW TABLES LIKE 'clinics'");
$has_clinics = $rc && $rc->num_rows > 0;
$rc2 = @$conn->query("SHOW TABLES LIKE 'customers'");
$has_customers = $rc2 && $rc2->num_rows > 0;
$rc3 = @$conn->query("SHOW TABLES LIKE 'devices'");
$has_devices = $rc3 && $rc3->num_rows > 0;
foreach ($duplicates as $dup_id => $keep_id) {
    @$conn->query("UPDATE hospitals SET governorate_id = $keep_id WHERE governorate_id = $dup_id");
    if ($has_clinics) @$conn->query("UPDATE clinics SET governorate_id = $keep_id WHERE governorate_id = $dup_id");
    if ($has_customers) @$conn->query("UPDATE customers SET governorate_id = $keep_id WHERE governorate_id = $dup_id");
    if ($has_devices) @$conn->query("UPDATE devices SET governorate_id = $keep_id WHERE governorate_id = $dup_id");
    @$conn->query("DELETE FROM governorates WHERE id = $dup_id");
}
