<?php
/**
 * Browser-safe repair for customer codes displaced by a recovery/recreate batch.
 *
 * Preview:
 *   /fix_recovery_customer_codes.php?key=secret123
 *
 * Apply without orphan deletion:
 *   Open the preview page, review the candidates, then submit the POST form
 *   with confirm=FIX_CODES.
 *
 * Apply with orphan deletion:
 *   Select "Fix codes and delete orphan recreated local leads", review the
 *   candidates, then submit the POST form with confirm=FIX_AND_DELETE.
 *
 * Undo:
 *   Open the preview page, select a backup table, then submit the POST form
 *   with confirm=UNDO_CODES.
 */

$expectedKey = getenv('MAINTENANCE_KEY') ?: 'secret123';
$requestMethod = strtoupper((string)($_SERVER['REQUEST_METHOD'] ?? 'GET'));
$providedKey = $_POST['key'] ?? ($_GET['key'] ?? '');

if (!hash_equals((string)$expectedKey, (string)$providedKey)) {
    http_response_code(403);
    header('Content-Type: text/html; charset=UTF-8');
    echo '<!doctype html><meta charset="utf-8"><title>Forbidden</title><h1>Forbidden</h1><p>Invalid maintenance key.</p>';
    exit;
}

header('Content-Type: text/html; charset=UTF-8');
header('Cache-Control: no-store, no-cache, must-revalidate, max-age=0');
header('Pragma: no-cache');
header('X-Robots-Tag: noindex, nofollow');

try {
    require_once __DIR__ . '/../vendor/autoload.php';
    $app = require __DIR__ . '/../app/bootstrap.php';
    $pdo = $app->db->getPdo();
    $personRepo = $app->repositories->person ?? null;
} catch (Throwable $e) {
    http_response_code(500);
    echo '<!doctype html><html lang="en"><meta charset="utf-8"><title>Customer-code repair error</title>';
    echo '<body style="font-family:Tahoma,Arial,sans-serif;margin:24px">';
    echo '<h1>Bootstrap failed</h1>';
    echo '<pre style="white-space:pre-wrap;background:#f7f7f8;border:1px solid #ddd;padding:12px">';
    echo htmlspecialchars($e->getMessage(), ENT_QUOTES, 'UTF-8');
    echo '</pre></body></html>';
    exit;
}

$runId = trim((string)($_POST['run_id'] ?? ($_GET['run_id'] ?? '')));
$getMutationRequested = (($_GET['apply'] ?? '') === '1') || (($_GET['undo'] ?? '') === '1');
$apply = $requestMethod === 'POST' && ($_POST['apply'] ?? '') === '1';
$undo = $requestMethod === 'POST' && ($_POST['undo'] ?? '') === '1';
$deleteOrphans = $apply && ($_POST['delete_orphans'] ?? '') === '1';
$confirmed = ($_POST['confirm'] ?? '') === 'FIX_CODES';
$deleteConfirmed = ($_POST['confirm'] ?? '') === 'FIX_AND_DELETE';
$undoConfirmed = ($_POST['confirm'] ?? '') === 'UNDO_CODES';
$selectedBackupTable = trim((string)($_POST['backup_table'] ?? ($_GET['backup_table'] ?? '')));
$message = null;
$error = null;
$applyResult = null;
$undoResult = null;
$runIds = [];
$backupTables = [];
$classifiedRows = [];
$summary = [];

function cc_h($value)
{
    return htmlspecialchars((string)$value, ENT_QUOTES, 'UTF-8');
}

function cc_asciiDigits($value): string
{
    return strtr((string)$value, [
        '۰' => '0', '۱' => '1', '۲' => '2', '۳' => '3', '۴' => '4',
        '۵' => '5', '۶' => '6', '۷' => '7', '۸' => '8', '۹' => '9',
        '٠' => '0', '١' => '1', '٢' => '2', '٣' => '3', '٤' => '4',
        '٥' => '5', '٦' => '6', '٧' => '7', '٨' => '8', '٩' => '9',
    ]);
}

function cc_normalizePhone($value): string
{
    $digits = preg_replace('/\D+/', '', cc_asciiDigits((string)$value));
    if ($digits === '') {
        return '';
    }
    if (strpos($digits, '0098') === 0) {
        $digits = substr($digits, 4);
    } elseif (strpos($digits, '98') === 0 && strlen($digits) === 12) {
        $digits = substr($digits, 2);
    }
    if (strpos($digits, '0') === 0 && strlen($digits) === 11) {
        $digits = substr($digits, 1);
    }
    return $digits;
}

function cc_collectPhones(array $row, array $fields): array
{
    $phones = [];
    foreach ($fields as $field) {
        $phone = cc_normalizePhone($row[$field] ?? '');
        if ($phone !== '') {
            $phones[] = $phone;
        }
    }
    return array_values(array_unique($phones));
}

function cc_displayPhones(array $phones): string
{
    return implode(', ', array_values(array_unique(array_filter($phones))));
}

function cc_compactName($firstName, $lastName): string
{
    $name = trim((string)$firstName . ' ' . (string)$lastName);
    $name = preg_replace('/\s+/u', ' ', $name);
    return $name ?: '';
}

function cc_tableExists(PDO $pdo, string $table): bool
{
    $stmt = $pdo->prepare("
        SELECT COUNT(*)
        FROM information_schema.tables
        WHERE table_schema = DATABASE()
          AND table_name = ?
    ");
    $stmt->execute([$table]);
    return ((int)$stmt->fetchColumn()) > 0;
}

function cc_columnExists(PDO $pdo, string $table, string $column): bool
{
    $stmt = $pdo->prepare("
        SELECT COUNT(*)
        FROM information_schema.columns
        WHERE table_schema = DATABASE()
          AND table_name = ?
          AND column_name = ?
    ");
    $stmt->execute([$table, $column]);
    return ((int)$stmt->fetchColumn()) > 0;
}

function cc_ident(string $identifier): string
{
    if (!preg_match('/^[A-Za-z0-9_]+$/', $identifier)) {
        throw new RuntimeException('Unsafe SQL identifier.');
    }
    return '`' . $identifier . '`';
}

function cc_countSelect(PDO $pdo, string $table, string $column, string $alias, string $sqlValueExpression): string
{
    if (!preg_match('/^[A-Za-z0-9_]+$/', $alias)) {
        throw new RuntimeException('Unsafe SQL alias.');
    }
    if (!cc_tableExists($pdo, $table) || !cc_columnExists($pdo, $table, $column)) {
        return "0 AS `{$alias}`";
    }
    return "(SELECT COUNT(*) FROM " . cc_ident($table) . " x WHERE x." . cc_ident($column) . " = {$sqlValueExpression}) AS `{$alias}`";
}

function cc_countSelectAny(PDO $pdo, string $table, array $columnExpressions, string $alias): string
{
    if (!preg_match('/^[A-Za-z0-9_]+$/', $alias)) {
        throw new RuntimeException('Unsafe SQL alias.');
    }
    if (!cc_tableExists($pdo, $table)) {
        return "0 AS `{$alias}`";
    }

    $parts = [];
    foreach ($columnExpressions as $column => $expression) {
        if (cc_columnExists($pdo, $table, (string)$column)) {
            $parts[] = "x." . cc_ident((string)$column) . " = {$expression}";
        }
    }
    if (empty($parts)) {
        return "0 AS `{$alias}`";
    }

    return "(SELECT COUNT(*) FROM " . cc_ident($table) . " x WHERE " . implode(' OR ', $parts) . ") AS `{$alias}`";
}

function cc_getRunIds(PDO $pdo): array
{
    if (!cc_tableExists($pdo, '_recovery_backup_persons')) {
        return [];
    }
    $stmt = $pdo->query("
        SELECT DISTINCT run_id
        FROM `_recovery_backup_persons`
        WHERE NULLIF(TRIM(COALESCE(run_id, '')), '') IS NOT NULL
        ORDER BY run_id DESC
    ");
    return $stmt->fetchAll(PDO::FETCH_COLUMN) ?: [];
}

function cc_backupTablePattern(): string
{
    return '/^customer_code_repair_[0-9]{8}_[0-9]{6}_[a-f0-9]{4}$/';
}

function cc_getBackupTables(PDO $pdo): array
{
    $stmt = $pdo->query('SHOW TABLES');
    $tables = [];
    foreach ($stmt->fetchAll(PDO::FETCH_NUM) as $row) {
        $table = (string)($row[0] ?? '');
        if (preg_match(cc_backupTablePattern(), $table)) {
            $tables[] = $table;
        }
    }
    rsort($tables);
    return $tables;
}

function cc_nextBackupTableName(): string
{
    try {
        $suffix = bin2hex(random_bytes(2));
    } catch (Throwable $e) {
        $suffix = substr(md5(uniqid('', true)), 0, 4);
    }
    return 'customer_code_repair_' . date('Ymd_His') . '_' . $suffix;
}

function cc_decodeRecoveryJson($json): ?array
{
    $decoded = json_decode((string)$json, true);
    return is_array($decoded) ? $decoded : null;
}

function cc_json($value): string
{
    return json_encode($value, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
}

function cc_relatedSpecs(): array
{
    return [
        'deals_by_contact' => ['table' => 'deals', 'column' => 'contact_didar_id', 'value' => 'contact', 'blocking' => true],
        'deals_by_person' => ['table' => 'deals', 'column' => 'person_id', 'value' => 'person_id', 'blocking' => true],
        'activities' => ['table' => 'activities', 'column' => 'contact_didar_id', 'value' => 'contact', 'blocking' => true],
        'crm_support_logs' => ['table' => 'crm_support_logs', 'column' => 'contact_didar_id', 'value' => 'contact', 'blocking' => true],
        'customer_access_requests' => ['table' => 'customer_access_requests', 'column' => 'contact_didar_id', 'value' => 'contact', 'blocking' => true],
        'referrals' => ['table' => 'referrals', 'column' => 'contact_didar_id', 'value' => 'contact', 'blocking' => true],
        'customer_satisfactions' => ['table' => 'customer_satisfactions', 'column' => 'contact_didar_id', 'value' => 'contact', 'blocking' => true],
        'lead_owner_history_by_contact' => ['table' => 'lead_owner_history', 'column' => 'contact_didar_id', 'value' => 'contact', 'blocking' => true],
        'lead_owner_history_by_person' => ['table' => 'lead_owner_history', 'column' => 'person_id', 'value' => 'person_id', 'blocking' => true],
        'virtual_stages' => ['table' => 'virtual_stages', 'column' => 'contact_didar_id', 'value' => 'contact', 'blocking' => false],
        'operation_run_items' => ['table' => 'operation_run_items', 'column' => 'contact_didar_id', 'value' => 'contact', 'blocking' => false],
    ];
}

function cc_fetchRelatedRows(PDO $pdo, string $contactId, int $personId): array
{
    $related = [];
    foreach (cc_relatedSpecs() as $key => $spec) {
        $table = (string)$spec['table'];
        $column = (string)$spec['column'];
        if (!cc_tableExists($pdo, $table) || !cc_columnExists($pdo, $table, $column)) {
            $related[$key] = [];
            continue;
        }

        $value = ($spec['value'] === 'person_id') ? $personId : $contactId;
        if ($value === '' || $value === 0) {
            $related[$key] = [];
            continue;
        }

        $stmt = $pdo->prepare("SELECT * FROM " . cc_ident($table) . " WHERE " . cc_ident($column) . " = ?");
        $stmt->execute([$value]);
        $related[$key] = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }
    return $related;
}

function cc_relatedCounts(array $relatedRows): array
{
    $counts = [];
    foreach ($relatedRows as $key => $rows) {
        $counts[$key] = is_array($rows) ? count($rows) : 0;
    }
    ksort($counts);
    return $counts;
}

function cc_blockingRelatedCounts(array $relatedRows): array
{
    $blocking = [];
    $specs = cc_relatedSpecs();
    foreach ($relatedRows as $key => $rows) {
        if (!empty($specs[$key]['blocking']) && is_array($rows) && count($rows) > 0) {
            $blocking[$key] = count($rows);
        }
    }
    ksort($blocking);
    return $blocking;
}

function cc_fetchRecoveryRows(PDO $pdo, string $runId): array
{
    if (!cc_tableExists($pdo, '_recovery_backup_persons')) {
        return [];
    }

    $where = '';
    $params = [];
    if ($runId !== '') {
        $where = 'WHERE rb.run_id = ?';
        $params[] = $runId;
    }

    $recreatedDealCount = cc_countSelectAny($pdo, 'deals', [
        'contact_didar_id' => 'rb.recreated_didar_id',
        'person_id' => 'newp.id',
    ], 'recreated_deal_count');
    $recreatedActivityCount = cc_countSelect($pdo, 'activities', 'contact_didar_id', 'recreated_activity_count', 'rb.recreated_didar_id');
    $recreatedSupportCount = cc_countSelect($pdo, 'crm_support_logs', 'contact_didar_id', 'recreated_support_count', 'rb.recreated_didar_id');
    $recreatedAccessCount = cc_countSelect($pdo, 'customer_access_requests', 'contact_didar_id', 'recreated_access_request_count', 'rb.recreated_didar_id');
    $recreatedReferralCount = cc_countSelect($pdo, 'referrals', 'contact_didar_id', 'recreated_referral_count', 'rb.recreated_didar_id');
    $recreatedSatisfactionCount = cc_countSelect($pdo, 'customer_satisfactions', 'contact_didar_id', 'recreated_satisfaction_count', 'rb.recreated_didar_id');
    $recreatedOwnerHistoryContactCount = cc_countSelect($pdo, 'lead_owner_history', 'contact_didar_id', 'recreated_owner_history_contact_count', 'rb.recreated_didar_id');
    $recreatedOwnerHistoryPersonCount = cc_countSelect($pdo, 'lead_owner_history', 'person_id', 'recreated_owner_history_person_count', 'newp.id');
    $recreatedVirtualStageCount = cc_countSelect($pdo, 'virtual_stages', 'contact_didar_id', 'recreated_virtual_stage_count', 'rb.recreated_didar_id');
    $recreatedOperationItemCount = cc_countSelect($pdo, 'operation_run_items', 'contact_didar_id', 'recreated_operation_item_count', 'rb.recreated_didar_id');

    $sql = "
        SELECT
            rb.backup_id,
            rb.run_id,
            rb.action AS recovery_action,
            rb.person_id AS backup_person_id,
            rb.customer_code AS backup_customer_code,
            rb.didar_contact_id AS backup_didar_contact_id,
            rb.row_json AS recovery_row_json,
            rb.recreated_didar_id,
            rb.backed_up_at,

            oldp.id AS current_person_id,
            oldp.didar_contact_id AS current_didar_contact_id,
            oldp.customer_code AS current_customer_code,
            oldp.code AS current_code,
            oldp.first_name AS current_first_name,
            oldp.last_name AS current_last_name,
            oldp.mobile_phone AS current_mobile_phone,
            oldp.secondary_mobile_phone AS current_secondary_mobile_phone,
            oldp.mobile_phone_3 AS current_mobile_phone_3,
            oldp.mobile_phone_4 AS current_mobile_phone_4,
            oldp.work_phone AS current_work_phone,
            oldp.register_time AS current_register_time,
            oldp.created_at AS current_created_at,

            newp.id AS recreated_person_id,
            newp.didar_contact_id AS recreated_didar_contact_id,
            newp.customer_code AS recreated_customer_code,
            newp.first_name AS recreated_first_name,
            newp.last_name AS recreated_last_name,
            newp.mobile_phone AS recreated_mobile_phone,
            newp.secondary_mobile_phone AS recreated_secondary_mobile_phone,
            newp.mobile_phone_3 AS recreated_mobile_phone_3,
            newp.mobile_phone_4 AS recreated_mobile_phone_4,
            newp.work_phone AS recreated_work_phone,

            holder.id AS holder_person_id,
            holder.didar_contact_id AS holder_didar_contact_id,
            holder.mobile_phone AS holder_mobile_phone,

            (
                SELECT COUNT(*)
                FROM deals d
                WHERE d.person_id = oldp.id
                   OR d.contact_didar_id = oldp.didar_contact_id
            ) AS current_deal_count,
            (
                SELECT MAX(d.register_time)
                FROM deals d
                WHERE d.person_id = oldp.id
                   OR d.contact_didar_id = oldp.didar_contact_id
            ) AS current_last_deal_at,
            {$recreatedDealCount},
            {$recreatedActivityCount},
            {$recreatedSupportCount},
            {$recreatedAccessCount},
            {$recreatedReferralCount},
            {$recreatedSatisfactionCount},
            {$recreatedOwnerHistoryContactCount},
            {$recreatedOwnerHistoryPersonCount},
            {$recreatedVirtualStageCount},
            {$recreatedOperationItemCount}
        FROM `_recovery_backup_persons` rb
        LEFT JOIN persons oldp ON oldp.didar_contact_id = rb.didar_contact_id
        LEFT JOIN persons newp ON newp.didar_contact_id = rb.recreated_didar_id
        LEFT JOIN persons holder ON holder.customer_code = rb.customer_code
        {$where}
        ORDER BY CAST(rb.customer_code AS UNSIGNED), rb.backup_id
    ";

    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
}

function cc_classifyRecoveryRow(array $row): array
{
    $backup = cc_decodeRecoveryJson($row['recovery_row_json'] ?? '');
    $backupCode = trim((string)($row['backup_customer_code'] ?? ''));
    $currentCode = trim((string)($row['current_customer_code'] ?? ''));
    $currentPersonId = trim((string)($row['current_person_id'] ?? ''));
    $status = 'unknown';
    $reason = '';
    $actionable = false;
    $backupPhones = [];
    $currentPhones = cc_collectPhones($row, [
        'current_mobile_phone',
        'current_secondary_mobile_phone',
        'current_mobile_phone_3',
        'current_mobile_phone_4',
        'current_work_phone',
    ]);
    $recreatedPhones = cc_collectPhones($row, [
        'recreated_mobile_phone',
        'recreated_secondary_mobile_phone',
        'recreated_mobile_phone_3',
        'recreated_mobile_phone_4',
        'recreated_work_phone',
    ]);
    $backupName = '';

    if ($backup === null) {
        $status = 'invalid_backup_json';
        $reason = 'Recovery row_json could not be decoded; skipped.';
    } else {
        $backupPhones = cc_collectPhones($backup, [
            'mobile_phone',
            'secondary_mobile_phone',
            'mobile_phone_3',
            'mobile_phone_4',
            'work_phone',
        ]);
        $backupName = cc_compactName($backup['first_name'] ?? '', $backup['last_name'] ?? '');

        if ($currentPersonId === '') {
            $status = 'old_contact_missing';
            $reason = 'No current persons row exists for the backed-up Didar contact id.';
        } elseif (!preg_match('/^[0-9]+$/', $backupCode)) {
            $status = 'non_numeric_recovery_code';
            $reason = 'Recovery customer_code is not numeric; skipped.';
        } elseif ($currentCode !== $backupCode) {
            $status = 'already_changed';
            $reason = 'The current row no longer holds the recovery customer_code.';
        } elseif (empty($backupPhones)) {
            $status = 'unsafe_no_backup_phone';
            $reason = 'The recovery row has no phone evidence; skipped.';
        } else {
            $overlap = array_intersect($backupPhones, $currentPhones);
            if (!empty($overlap)) {
                $status = 'original_phone_still_matches';
                $reason = 'The current row still has a backed-up phone, so the code may still belong here.';
            } else {
                $status = 'candidate_reallocate_current';
                $reason = 'Current row still holds the recovery code but no backed-up phone matches it.';
                $actionable = true;
            }
        }
    }

    $row['repair_status'] = $status;
    $row['repair_reason'] = $reason;
    $row['repair_actionable'] = $actionable;
    $row['backup_name'] = $backupName;
    $row['backup_phones'] = cc_displayPhones($backupPhones);
    $row['current_name'] = cc_compactName($row['current_first_name'] ?? '', $row['current_last_name'] ?? '');
    $row['current_phones'] = cc_displayPhones($currentPhones);
    $row['recreated_name'] = cc_compactName($row['recreated_first_name'] ?? '', $row['recreated_last_name'] ?? '');
    $row['recreated_phones'] = cc_displayPhones($recreatedPhones);
    $row['recreated_present'] = trim((string)($row['recreated_person_id'] ?? '')) !== '';

    $deleteSafe = false;
    $deleteReason = 'No orphan recreated local lead is present.';
    if (!empty($row['recreated_present'])) {
        $blockingCounts = [];
        foreach ([
            'deals' => 'recreated_deal_count',
            'activities' => 'recreated_activity_count',
            'support logs' => 'recreated_support_count',
            'access requests' => 'recreated_access_request_count',
            'referrals' => 'recreated_referral_count',
            'satisfactions' => 'recreated_satisfaction_count',
            'owner history(contact)' => 'recreated_owner_history_contact_count',
            'owner history(person)' => 'recreated_owner_history_person_count',
        ] as $label => $field) {
            $count = (int)($row[$field] ?? 0);
            if ($count > 0) {
                $blockingCounts[] = $label . '=' . $count;
            }
        }

        $recreatedContact = (string)($row['recreated_didar_contact_id'] ?? ($row['recreated_didar_id'] ?? ''));
        if (!$actionable) {
            $deleteReason = 'Orphan recreated local lead exists, but the paired current lead is not actionable.';
        } elseif (strpos($recreatedContact, 'local_') !== 0) {
            $deleteReason = 'Recreated lead contact id is not local_*; orphan delete is blocked.';
        } elseif (empty($backupPhones) || empty(array_intersect($backupPhones, $recreatedPhones))) {
            $deleteReason = 'Recreated lead does not share a backed-up phone; orphan delete is blocked.';
        } elseif (!empty($blockingCounts)) {
            $deleteReason = 'Recreated lead has blocking related records: ' . implode(', ', $blockingCounts) . '.';
        } else {
            $deleteSafe = true;
            $deleteReason = 'Safe to back up and delete orphan recreated local lead.';
        }
    }
    $row['recreated_delete_safe'] = $deleteSafe;
    $row['recreated_delete_reason'] = $deleteReason;

    return $row;
}

function cc_classifyRows(array $rows): array
{
    return array_map('cc_classifyRecoveryRow', $rows);
}

function cc_buildSummary(array $rows): array
{
    $summary = [
        'total_recovery_rows' => count($rows),
        'actionable' => 0,
        'recreated_present' => 0,
        'recreated_delete_safe' => 0,
        'recreated_delete_blocked' => 0,
        'status_counts' => [],
    ];
    foreach ($rows as $row) {
        $status = (string)($row['repair_status'] ?? 'unknown');
        $summary['status_counts'][$status] = ($summary['status_counts'][$status] ?? 0) + 1;
        if (!empty($row['repair_actionable'])) {
            $summary['actionable']++;
        }
        if (!empty($row['recreated_present'])) {
            $summary['recreated_present']++;
            if (!empty($row['recreated_delete_safe'])) {
                $summary['recreated_delete_safe']++;
            } else {
                $summary['recreated_delete_blocked']++;
            }
        }
    }
    ksort($summary['status_counts']);
    return $summary;
}

function cc_createBackupTable(PDO $pdo, string $table): void
{
    if (!preg_match(cc_backupTablePattern(), $table)) {
        throw new RuntimeException('Invalid generated backup table name.');
    }
    $pdo->exec("
        CREATE TABLE `{$table}` (
            `repair_id` int NOT NULL AUTO_INCREMENT,
            `backup_id` int DEFAULT NULL,
            `run_id` varchar(128) DEFAULT NULL,
            `action` varchar(64) NOT NULL,
            `person_id` int NOT NULL,
            `didar_contact_id` varchar(255) NOT NULL,
            `old_customer_code` varchar(32) DEFAULT NULL,
            `new_customer_code` varchar(32) DEFAULT NULL,
            `backup_customer_code` varchar(32) DEFAULT NULL,
            `backup_didar_contact_id` varchar(255) DEFAULT NULL,
            `recreated_didar_id` varchar(255) DEFAULT NULL,
            `backup_phones` varchar(255) DEFAULT NULL,
            `current_phones` varchar(255) DEFAULT NULL,
            `status_before` varchar(64) DEFAULT NULL,
            `reason` text,
            `current_row_json` longtext,
            `recreated_row_json` longtext,
            `related_rows_json` longtext,
            `reference_counts_json` longtext,
            `recovery_row_json` longtext,
            `applied_at` datetime NOT NULL,
            PRIMARY KEY (`repair_id`),
            KEY `idx_person_id` (`person_id`),
            KEY `idx_old_customer_code` (`old_customer_code`),
            KEY `idx_new_customer_code` (`new_customer_code`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");
}

function cc_allocateFreshCustomerCode(PDO $pdo, $personRepo): string
{
    if ($personRepo && method_exists($personRepo, 'allocateNextCustomerCode')) {
        $code = (string)$personRepo->allocateNextCustomerCode();
    } else {
        $sequenceKey = 'customer_code';
        $initStmt = $pdo->prepare("
            INSERT INTO invoice_sequences (sequence_key, last_serial, updated_at)
            VALUES (?, 19999, NOW())
            ON DUPLICATE KEY UPDATE updated_at = NOW()
        ");
        $initStmt->execute([$sequenceKey]);

        $lockStmt = $pdo->prepare("SELECT last_serial FROM invoice_sequences WHERE sequence_key = ? FOR UPDATE");
        $lockStmt->execute([$sequenceKey]);
        $lastSerial = max(19999, (int)$lockStmt->fetchColumn());

        $maxExisting = (int)$pdo->query("
            SELECT COALESCE(MAX(CAST(customer_code AS UNSIGNED)), 0)
            FROM persons
            WHERE customer_code REGEXP '^[0-9]+$'
        ")->fetchColumn();
        $lastSerial = max($lastSerial, $maxExisting);

        $freeCheck = $pdo->prepare("SELECT 1 FROM persons WHERE customer_code = ? LIMIT 1");
        $nextCode = $lastSerial + 1;
        do {
            $freeCheck->execute([(string)$nextCode]);
            $taken = (bool)$freeCheck->fetchColumn();
            if (!$taken) {
                break;
            }
            $nextCode++;
        } while ($nextCode < $lastSerial + 100000);

        $updateStmt = $pdo->prepare("UPDATE invoice_sequences SET last_serial = ?, updated_at = NOW() WHERE sequence_key = ?");
        $updateStmt->execute([$nextCode, $sequenceKey]);
        $code = (string)$nextCode;
    }

    if (!preg_match('/^[0-9]+$/', $code)) {
        throw new RuntimeException('Customer-code allocator returned a non-numeric code.');
    }

    $takenStmt = $pdo->prepare("SELECT COUNT(*) FROM persons WHERE customer_code = ?");
    $takenStmt->execute([$code]);
    if ((int)$takenStmt->fetchColumn() > 0) {
        throw new RuntimeException("Allocated customer_code {$code} is already in use.");
    }

    return $code;
}

function cc_validateRecreatedDelete(PDO $pdo, array $candidate, array $recreated, array $backup): array
{
    $recreatedContact = (string)($recreated['didar_contact_id'] ?? '');
    if ($recreatedContact === '' || $recreatedContact !== (string)($candidate['recreated_didar_id'] ?? '')) {
        throw new RuntimeException("Orphan recreated lead for backup_id {$candidate['backup_id']} changed after preview; aborting.");
    }
    if (strpos($recreatedContact, 'local_') !== 0) {
        throw new RuntimeException("Recreated lead {$recreatedContact} is not local_*; orphan delete blocked.");
    }

    $backupPhones = cc_collectPhones($backup, [
        'mobile_phone',
        'secondary_mobile_phone',
        'mobile_phone_3',
        'mobile_phone_4',
        'work_phone',
    ]);
    $recreatedPhones = cc_collectPhones($recreated, [
        'mobile_phone',
        'secondary_mobile_phone',
        'mobile_phone_3',
        'mobile_phone_4',
        'work_phone',
    ]);
    if (empty($backupPhones) || empty(array_intersect($backupPhones, $recreatedPhones))) {
        throw new RuntimeException("Recreated lead {$recreatedContact} no longer shares a backed-up phone; orphan delete blocked.");
    }

    $relatedRows = cc_fetchRelatedRows($pdo, $recreatedContact, (int)($recreated['id'] ?? 0));
    $blockingCounts = cc_blockingRelatedCounts($relatedRows);
    if (!empty($blockingCounts)) {
        $parts = [];
        foreach ($blockingCounts as $key => $count) {
            $parts[] = $key . '=' . $count;
        }
        throw new RuntimeException("Recreated lead {$recreatedContact} has blocking related records: " . implode(', ', $parts) . '.');
    }

    return [
        'related_rows' => $relatedRows,
        'counts' => cc_relatedCounts($relatedRows),
        'phones' => $recreatedPhones,
    ];
}

function cc_tableColumns(PDO $pdo, string $table): array
{
    $stmt = $pdo->query("SHOW COLUMNS FROM " . cc_ident($table));
    $columns = [];
    foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) ?: [] as $row) {
        $field = (string)($row['Field'] ?? '');
        if ($field !== '') {
            $columns[$field] = true;
        }
    }
    return $columns;
}

function cc_insertBackedUpRow(PDO $pdo, string $table, array $row): void
{
    if (!cc_tableExists($pdo, $table)) {
        throw new RuntimeException("Cannot restore {$table}: table does not exist.");
    }
    $columns = cc_tableColumns($pdo, $table);
    $insert = [];
    foreach ($row as $column => $value) {
        if (isset($columns[$column])) {
            $insert[$column] = $value;
        }
    }
    if (empty($insert)) {
        throw new RuntimeException("Cannot restore {$table}: no matching columns.");
    }

    $names = array_keys($insert);
    $placeholders = implode(', ', array_fill(0, count($names), '?'));
    $sql = "INSERT INTO " . cc_ident($table) .
        " (" . implode(', ', array_map('cc_ident', $names)) . ") VALUES ({$placeholders})";
    $stmt = $pdo->prepare($sql);
    $stmt->execute(array_values($insert));
}

function cc_restoreRelatedRows(PDO $pdo, array $relatedRows): int
{
    $restored = 0;
    foreach (['virtual_stages'] as $key) {
        foreach (($relatedRows[$key] ?? []) as $row) {
            if (!is_array($row)) {
                continue;
            }
            if (isset($row['id']) && cc_columnExists($pdo, $key, 'id')) {
                $check = $pdo->prepare("SELECT COUNT(*) FROM " . cc_ident($key) . " WHERE `id` = ?");
                $check->execute([(int)$row['id']]);
                if ((int)$check->fetchColumn() > 0) {
                    continue;
                }
            }
            cc_insertBackedUpRow($pdo, $key, $row);
            $restored++;
        }
    }
    return $restored;
}

function cc_applyRepair(PDO $pdo, $personRepo, string $runId, bool $deleteOrphans): array
{
    $classified = cc_classifyRows(cc_fetchRecoveryRows($pdo, $runId));
    $candidates = array_values(array_filter($classified, function ($row) {
        return !empty($row['repair_actionable']);
    }));

    if (empty($candidates)) {
        return [
            'updated' => 0,
            'backup_table' => '',
            'message' => 'No actionable customer-code mappings were found.',
        ];
    }

    if ($deleteOrphans) {
        $recreatedToDelete = array_values(array_filter($candidates, function ($row) {
            return !empty($row['recreated_present']);
        }));
        $unsafeRecreated = array_values(array_filter($recreatedToDelete, function ($row) {
            return empty($row['recreated_delete_safe']);
        }));
        if (!empty($unsafeRecreated)) {
            $parts = [];
            foreach (array_slice($unsafeRecreated, 0, 8) as $row) {
                $parts[] = 'backup_id ' . (string)($row['backup_id'] ?? '') . ': ' . (string)($row['recreated_delete_reason'] ?? 'unsafe');
            }
            throw new RuntimeException(
                'Apply blocked: ' . count($unsafeRecreated) .
                ' orphan recreated local leads are not safe to delete. Choose code-only repair or review those rows. ' .
                implode(' | ', $parts)
            );
        }
    }

    $backupTable = cc_nextBackupTableName();
    cc_createBackupTable($pdo, $backupTable);

    $pdo->beginTransaction();
    try {
        $lockStmt = $pdo->prepare("SELECT * FROM persons WHERE id = ? FOR UPDATE");
        $lockByDidarStmt = $pdo->prepare("SELECT * FROM persons WHERE didar_contact_id = ? FOR UPDATE");
        $insertSql = "
            INSERT INTO `{$backupTable}` (
                backup_id, run_id, action, person_id, didar_contact_id,
                old_customer_code, new_customer_code, backup_customer_code,
                backup_didar_contact_id, recreated_didar_id, backup_phones,
                current_phones, status_before, reason, current_row_json,
                recreated_row_json, related_rows_json, reference_counts_json,
                recovery_row_json, applied_at
            ) VALUES (
                ?, ?, ?, ?, ?,
                ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
                ?, ?, ?, ?, NOW()
            )
        ";
        $insertStmt = $pdo->prepare($insertSql);
        $updateStmt = $pdo->prepare("
            UPDATE persons
            SET customer_code = ?
            WHERE id = ?
              AND didar_contact_id = ?
              AND customer_code = ?
        ");
        $deleteRecreatedStmt = $pdo->prepare("
            DELETE FROM persons
            WHERE id = ?
              AND didar_contact_id = ?
        ");

        $updated = 0;
        $deletedRecreated = 0;
        $deletedRecreatedContacts = [];
        foreach ($candidates as $candidate) {
            $backup = cc_decodeRecoveryJson($candidate['recovery_row_json'] ?? '');
            if ($backup === null) {
                throw new RuntimeException('Candidate became invalid because recovery JSON could not be decoded.');
            }

            $lockStmt->execute([(int)$candidate['current_person_id']]);
            $current = $lockStmt->fetch(PDO::FETCH_ASSOC);
            if (!$current) {
                throw new RuntimeException("Person id {$candidate['current_person_id']} disappeared during repair.");
            }

            $expectedDidar = (string)$candidate['current_didar_contact_id'];
            $expectedCode = (string)$candidate['backup_customer_code'];
            if ((string)$current['didar_contact_id'] !== $expectedDidar || (string)$current['customer_code'] !== $expectedCode) {
                throw new RuntimeException("Person id {$candidate['current_person_id']} changed after preview; aborting.");
            }

            $backupPhones = cc_collectPhones($backup, [
                'mobile_phone',
                'secondary_mobile_phone',
                'mobile_phone_3',
                'mobile_phone_4',
                'work_phone',
            ]);
            $currentPhones = cc_collectPhones($current, [
                'mobile_phone',
                'secondary_mobile_phone',
                'mobile_phone_3',
                'mobile_phone_4',
                'work_phone',
            ]);
            if (empty($backupPhones) || !empty(array_intersect($backupPhones, $currentPhones))) {
                throw new RuntimeException("Person id {$candidate['current_person_id']} no longer matches the repair condition; aborting.");
            }

            $recreated = null;
            $recreatedValidation = null;
            if ($deleteOrphans && !empty($candidate['recreated_present'])) {
                $recreatedContact = (string)$candidate['recreated_didar_id'];
                if (isset($deletedRecreatedContacts[$recreatedContact])) {
                    throw new RuntimeException("Orphan recreated lead {$recreatedContact} is referenced by more than one recovery row; aborting.");
                }
                $lockByDidarStmt->execute([$recreatedContact]);
                $recreated = $lockByDidarStmt->fetch(PDO::FETCH_ASSOC);
                if (!$recreated) {
                    throw new RuntimeException("Orphan recreated lead {$recreatedContact} disappeared after preview; refresh and retry.");
                }
                $recreatedValidation = cc_validateRecreatedDelete($pdo, $candidate, $recreated, $backup);
            }

            $newCode = cc_allocateFreshCustomerCode($pdo, $personRepo);
            if ($newCode === $expectedCode) {
                throw new RuntimeException('Allocator returned the same customer_code that is being repaired.');
            }

            $insertStmt->execute([
                (int)$candidate['backup_id'],
                (string)$candidate['run_id'],
                'reallocate_current_customer_code',
                (int)$current['id'],
                (string)$current['didar_contact_id'],
                (string)$current['customer_code'],
                $newCode,
                $expectedCode,
                (string)$candidate['backup_didar_contact_id'],
                (string)$candidate['recreated_didar_id'],
                cc_displayPhones($backupPhones),
                cc_displayPhones($currentPhones),
                (string)$candidate['repair_status'],
                (string)$candidate['repair_reason'],
                cc_json($current),
                null,
                null,
                null,
                (string)$candidate['recovery_row_json'],
            ]);

            $updateStmt->execute([
                $newCode,
                (int)$current['id'],
                (string)$current['didar_contact_id'],
                (string)$current['customer_code'],
            ]);
            if ((int)$updateStmt->rowCount() !== 1) {
                throw new RuntimeException("Failed to update person id {$current['id']}.");
            }
            $updated++;

            if ($recreated && $recreatedValidation) {
                $recreatedContact = (string)$recreated['didar_contact_id'];
                $insertStmt->execute([
                    (int)$candidate['backup_id'],
                    (string)$candidate['run_id'],
                    'delete_orphan_recreated_person',
                    (int)$recreated['id'],
                    $recreatedContact,
                    (string)($recreated['customer_code'] ?? ''),
                    null,
                    $expectedCode,
                    (string)$candidate['backup_didar_contact_id'],
                    $recreatedContact,
                    cc_displayPhones($backupPhones),
                    cc_displayPhones($recreatedValidation['phones'] ?? []),
                    'recreated_delete_safe',
                    'Backed up and deleted orphan recreated local recovery lead.',
                    null,
                    cc_json($recreated),
                    cc_json($recreatedValidation['related_rows'] ?? []),
                    cc_json($recreatedValidation['counts'] ?? []),
                    (string)$candidate['recovery_row_json'],
                ]);

                $deleteRecreatedStmt->execute([(int)$recreated['id'], $recreatedContact]);
                if ((int)$deleteRecreatedStmt->rowCount() !== 1) {
                    throw new RuntimeException("Failed to delete orphan recreated lead {$recreatedContact}.");
                }
                $deletedRecreated++;
                $deletedRecreatedContacts[$recreatedContact] = true;
            }
        }

        $pdo->commit();
        return [
            'updated' => $updated,
            'deleted_recreated' => $deletedRecreated,
            'delete_orphans' => $deleteOrphans,
            'backup_table' => $backupTable,
            'restore_sql' => $deleteOrphans
                ? "Use the Undo form for {$backupTable}; it checks for conflicts before restoring old customer codes and deleted orphan recreated leads."
                : "Use the Undo form for {$backupTable}; it checks for conflicts before restoring old customer codes.",
        ];
    } catch (Throwable $e) {
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }
        throw $e;
    }
}

function cc_undoRepair(PDO $pdo, string $backupTable): array
{
    if (!preg_match(cc_backupTablePattern(), $backupTable) || !cc_tableExists($pdo, $backupTable)) {
        throw new RuntimeException('Invalid backup table selected for undo.');
    }

    $hasRecreatedBackup = cc_columnExists($pdo, $backupTable, 'recreated_row_json');
    $reallocateFilter = "b.action = 'reallocate_current_customer_code'";

    $changedStmt = $pdo->query("
        SELECT COUNT(*)
        FROM `{$backupTable}` b
        INNER JOIN persons p ON p.id = b.person_id
        WHERE {$reallocateFilter}
          AND (
              p.didar_contact_id <> b.didar_contact_id
              OR COALESCE(p.customer_code, '') <> COALESCE(b.new_customer_code, '')
          )
    ");
    if ((int)$changedStmt->fetchColumn() > 0) {
        throw new RuntimeException('Undo blocked: one or more repaired rows changed after the fix.');
    }

    $conflictStmt = $pdo->query("
        SELECT b.old_customer_code, p.id, p.didar_contact_id
        FROM `{$backupTable}` b
        INNER JOIN persons p ON p.customer_code = b.old_customer_code
        WHERE p.id <> b.person_id
        LIMIT 10
    ");
    $conflicts = $conflictStmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    if (!empty($conflicts)) {
        throw new RuntimeException('Undo blocked: at least one old customer_code is now used by another row.');
    }

    $deletedRows = [];
    if ($hasRecreatedBackup) {
        $stmt = $pdo->query("SELECT * FROM `{$backupTable}` WHERE action IN ('delete_orphan_recreated_person', 'delete_recreated_person') ORDER BY repair_id ASC");
        $deletedRows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        foreach ($deletedRows as $row) {
            $person = cc_decodeRecoveryJson($row['recreated_row_json'] ?? '');
            if (!$person) {
                throw new RuntimeException('Undo blocked: recreated person backup JSON is missing or invalid.');
            }
            $contactId = (string)($person['didar_contact_id'] ?? '');
            $personId = (int)($person['id'] ?? 0);
            $customerCode = trim((string)($person['customer_code'] ?? ''));

            $stmt = $pdo->prepare("SELECT COUNT(*) FROM persons WHERE didar_contact_id = ?");
            $stmt->execute([$contactId]);
            if ((int)$stmt->fetchColumn() > 0) {
                throw new RuntimeException("Undo blocked: recreated contact {$contactId} already exists.");
            }
            if ($personId > 0) {
                $stmt = $pdo->prepare("SELECT COUNT(*) FROM persons WHERE id = ?");
                $stmt->execute([$personId]);
                if ((int)$stmt->fetchColumn() > 0) {
                    throw new RuntimeException("Undo blocked: recreated person id {$personId} is already used.");
                }
            }
            if ($customerCode !== '') {
                $stmt = $pdo->prepare("SELECT COUNT(*) FROM persons WHERE customer_code = ?");
                $stmt->execute([$customerCode]);
                if ((int)$stmt->fetchColumn() > 0) {
                    throw new RuntimeException("Undo blocked: recreated customer_code {$customerCode} is already used.");
                }
            }
        }
    }

    $pdo->beginTransaction();
    try {
        $restoredDeleted = 0;
        $restoredRelated = 0;
        foreach ($deletedRows as $row) {
            $person = cc_decodeRecoveryJson($row['recreated_row_json'] ?? '');
            if (!$person) {
                throw new RuntimeException('Undo blocked: recreated person backup JSON is missing or invalid.');
            }
            cc_insertBackedUpRow($pdo, 'persons', $person);
            $restoredDeleted++;

            $relatedRows = cc_decodeRecoveryJson($row['related_rows_json'] ?? '');
            if (is_array($relatedRows)) {
                $restoredRelated += cc_restoreRelatedRows($pdo, $relatedRows);
            }
        }

        $updated = $pdo->exec("
            UPDATE persons p
            INNER JOIN `{$backupTable}` b
                    ON b.person_id = p.id
                   AND b.didar_contact_id = p.didar_contact_id
            SET p.customer_code = b.old_customer_code
            WHERE {$reallocateFilter}
              AND p.customer_code = b.new_customer_code
        ");
        $pdo->commit();
        return [
            'restored' => (int)$updated,
            'restored_deleted_recreated' => $restoredDeleted,
            'restored_related_rows' => $restoredRelated,
            'backup_table' => $backupTable,
        ];
    } catch (Throwable $e) {
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }
        throw $e;
    }
}

try {
    $runIds = cc_getRunIds($pdo);
    if ($runId !== '' && !in_array($runId, $runIds, true)) {
        $error = 'Invalid run_id was reset to all recovery rows.';
        $runId = '';
    }
    $backupTables = cc_getBackupTables($pdo);

    if ($getMutationRequested) {
        $error = 'Apply and undo must be submitted from the page form; GET mutation URLs are blocked.';
    }

    if ($undo && !$undoConfirmed) {
        $error = 'Undo was not run. Type UNDO_CODES in the confirmation field.';
        $undo = false;
    }
    if ($undo) {
        $undoResult = cc_undoRepair($pdo, $selectedBackupTable);
        $message = 'Undo applied successfully.';
        $backupTables = cc_getBackupTables($pdo);
    }

    if ($apply && !$deleteOrphans && !$confirmed) {
        $error = 'Apply was not run. Type FIX_CODES in the confirmation field.';
        $apply = false;
    }
    if ($apply && $deleteOrphans && !$deleteConfirmed) {
        $error = 'Apply was not run. Type FIX_AND_DELETE in the confirmation field.';
        $apply = false;
    }
    if ($apply) {
        $applyResult = cc_applyRepair($pdo, $personRepo, $runId, $deleteOrphans);
        $message = $applyResult['message'] ?? 'Customer-code repair applied successfully.';
        $backupTables = cc_getBackupTables($pdo);
    }

    $classifiedRows = cc_classifyRows(cc_fetchRecoveryRows($pdo, $runId));
    $summary = cc_buildSummary($classifiedRows);
} catch (Throwable $e) {
    $classifiedRows = [];
    $summary = cc_buildSummary([]);
    $error = 'Customer-code repair failed: ' . $e->getMessage();
}
?>
<!doctype html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <title>Recovery customer-code repair</title>
    <style>
        body { font-family: Tahoma, Arial, sans-serif; margin: 24px; background: #f7f7f8; color: #222; }
        .wrap { max-width: 1280px; margin: 0 auto; }
        .panel { background: #fff; border: 1px solid #ddd; border-radius: 6px; padding: 18px; margin-bottom: 16px; }
        .grid { display: grid; grid-template-columns: repeat(auto-fit, minmax(190px, 1fr)); gap: 12px; }
        .metric { background: #f2f5f8; border-radius: 6px; padding: 12px; }
        .metric strong { display: block; font-size: 24px; direction: ltr; text-align: left; }
        label { display: block; margin-bottom: 6px; font-weight: bold; }
        input, select, button { font: inherit; padding: 8px 10px; border: 1px solid #bbb; border-radius: 4px; }
        button { cursor: pointer; background: #174ea6; color: #fff; border-color: #174ea6; }
        button.danger { background: #b3261e; border-color: #b3261e; }
        table { width: 100%; border-collapse: collapse; direction: ltr; text-align: left; font-size: 13px; }
        th, td { border-bottom: 1px solid #e5e5e5; padding: 8px; vertical-align: top; }
        th { background: #f2f5f8; position: sticky; top: 0; }
        .ok { color: #146c2e; }
        .err { color: #b3261e; }
        .warn { color: #8a4b00; }
        code { direction: ltr; display: inline-block; background: #f2f5f8; padding: 2px 4px; border-radius: 3px; }
        .table-wrap { max-height: 620px; overflow: auto; border: 1px solid #e5e5e5; }
        .truncate { max-width: 280px; overflow: hidden; text-overflow: ellipsis; white-space: nowrap; }
    </style>
</head>
<body>
<div class="wrap">
    <h1>Recovery customer-code repair</h1>

    <?php if ($message): ?><div class="panel ok"><?= cc_h($message) ?></div><?php endif; ?>
    <?php if ($error): ?><div class="panel err"><?= cc_h($error) ?></div><?php endif; ?>

    <div class="panel">
        <p>
            This maintenance page only repairs current rows still holding customer codes from
            <code>_recovery_backup_persons</code> after their phone identity no longer matches the backed-up row.
            It assigns each such current row a fresh customer code. Current lead rows are never deleted. You can
            choose a code-only repair, or explicitly opt in to also back up and delete orphan recreated
            <code>local_*</code> leads when no blocking business references exist. It does not create leads, relink
            deals, or assign the freed recovery code to any other row.
        </p>
        <?php if (!cc_tableExists($pdo, '_recovery_backup_persons')): ?>
            <p class="err"><code>_recovery_backup_persons</code> was not found. This repair needs that table as evidence.</p>
        <?php endif; ?>
    </div>

    <div class="panel">
        <form method="get">
            <input type="hidden" name="key" value="<?= cc_h($providedKey) ?>">
            <div class="grid">
                <div>
                    <label for="run_id">Recovery run</label>
                    <select name="run_id" id="run_id">
                        <option value="" <?= $runId === '' ? 'selected' : '' ?>>All runs</option>
                        <?php foreach ($runIds as $id): ?>
                            <option value="<?= cc_h($id) ?>" <?= $runId === $id ? 'selected' : '' ?>><?= cc_h($id) ?></option>
                        <?php endforeach; ?>
                    </select>
                </div>
                <div style="align-self:end">
                    <button type="submit">Preview</button>
                </div>
            </div>
        </form>
    </div>

    <div class="panel">
        <h2>Preview summary</h2>
        <div class="grid">
            <div class="metric">recovery rows<strong><?= cc_h((int)($summary['total_recovery_rows'] ?? 0)) ?></strong></div>
            <div class="metric">actionable rows<strong><?= cc_h((int)($summary['actionable'] ?? 0)) ?></strong></div>
            <div class="metric">orphan recreated leads present<strong><?= cc_h((int)($summary['recreated_present'] ?? 0)) ?></strong></div>
            <div class="metric">safe orphan deletes<strong><?= cc_h((int)($summary['recreated_delete_safe'] ?? 0)) ?></strong></div>
            <div class="metric">blocked orphan deletes<strong><?= cc_h((int)($summary['recreated_delete_blocked'] ?? 0)) ?></strong></div>
            <?php foreach (($summary['status_counts'] ?? []) as $status => $count): ?>
                <div class="metric"><?= cc_h($status) ?><strong><?= cc_h((int)$count) ?></strong></div>
            <?php endforeach; ?>
        </div>
        <?php if (($summary['recreated_delete_blocked'] ?? 0) > 0): ?>
            <p class="err">Delete mode is blocked until unsafe orphan recreated leads are reviewed. Code-only repair can still run.</p>
        <?php elseif (($summary['recreated_delete_safe'] ?? 0) > 0): ?>
            <p class="warn">Delete mode will back up and delete safe orphan recreated <code>local_*</code> leads in the same transaction as the customer-code repair.</p>
        <?php endif; ?>
    </div>

    <div class="panel">
        <h2>Apply</h2>
        <p>Apply creates a timestamped backup table and changes actionable <code>persons.customer_code</code> values to fresh codes. Choose whether orphan recreated <code>local_*</code> leads are only previewed or also backed up and deleted.</p>
        <form method="post">
            <input type="hidden" name="key" value="<?= cc_h($providedKey) ?>">
            <input type="hidden" name="run_id" value="<?= cc_h($runId) ?>">
            <input type="hidden" name="apply" value="1">
            <div class="grid">
                <label>
                    <input type="radio" name="delete_orphans" value="0" checked>
                    Fix customer codes only
                </label>
                <label>
                    <input type="radio" name="delete_orphans" value="1">
                    Fix codes and delete orphan recreated local leads
                </label>
            </div>
            <p>Type <code>FIX_CODES</code> for code-only repair, or <code>FIX_AND_DELETE</code> when delete mode is selected.</p>
            <label for="confirm">Confirmation</label>
            <input type="text" name="confirm" id="confirm" autocomplete="off">
            <button class="danger" type="submit">Apply repair</button>
        </form>
        <?php if ($applyResult): ?>
            <p>Updated: <code><?= cc_h($applyResult['updated'] ?? 0) ?></code></p>
            <p>Deleted orphan recreated leads: <code><?= cc_h($applyResult['deleted_recreated'] ?? 0) ?></code></p>
            <?php if (!empty($applyResult['backup_table'])): ?>
                <p>Backup table: <code><?= cc_h($applyResult['backup_table']) ?></code></p>
                <p><?= cc_h($applyResult['restore_sql'] ?? '') ?></p>
            <?php endif; ?>
        <?php endif; ?>
    </div>

    <div class="panel">
        <h2>Undo</h2>
        <p>Undo restores old customer codes and any deleted orphan recreated leads from a backup table only if repaired rows still have the generated new codes and the old codes are not used elsewhere.</p>
        <form method="post">
            <input type="hidden" name="key" value="<?= cc_h($providedKey) ?>">
            <input type="hidden" name="undo" value="1">
            <div class="grid">
                <div>
                    <label for="backup_table">Backup table</label>
                    <select name="backup_table" id="backup_table">
                        <?php foreach ($backupTables as $table): ?>
                            <option value="<?= cc_h($table) ?>" <?= $selectedBackupTable === $table ? 'selected' : '' ?>><?= cc_h($table) ?></option>
                        <?php endforeach; ?>
                    </select>
                </div>
                <div>
                    <label for="undo_confirm">Type UNDO_CODES to restore</label>
                    <input type="text" name="confirm" id="undo_confirm" autocomplete="off">
                </div>
                <div style="align-self:end">
                    <button class="danger" type="submit">Undo repair</button>
                </div>
            </div>
        </form>
        <?php if (!$backupTables): ?><p>No customer-code repair backup tables found.</p><?php endif; ?>
        <?php if ($undoResult): ?>
            <p>Restored rows: <code><?= cc_h($undoResult['restored']) ?></code></p>
            <p>Restored orphan recreated leads: <code><?= cc_h($undoResult['restored_deleted_recreated'] ?? 0) ?></code></p>
            <p>Restored related rows: <code><?= cc_h($undoResult['restored_related_rows'] ?? 0) ?></code></p>
            <p>Backup table: <code><?= cc_h($undoResult['backup_table']) ?></code></p>
        <?php endif; ?>
    </div>

    <div class="panel">
        <h2>Recovery rows</h2>
        <div class="table-wrap">
            <table>
                <thead>
                <tr>
                    <th>status</th>
                    <th>backup_code</th>
                    <th>current_person</th>
                    <th>current_contact</th>
                    <th>current_code</th>
                    <th>current_name</th>
                    <th>current_phones</th>
                    <th>backup_name</th>
                    <th>backup_phones</th>
                    <th>orphan recreated lead</th>
                    <th>references</th>
                    <th>reason</th>
                </tr>
                </thead>
                <tbody>
                <?php foreach ($classifiedRows as $row): ?>
                    <tr>
                        <td class="<?= !empty($row['repair_actionable']) ? 'warn' : '' ?>"><?= cc_h($row['repair_status'] ?? '') ?></td>
                        <td><?= cc_h($row['backup_customer_code'] ?? '') ?></td>
                        <td><?= cc_h($row['current_person_id'] ?? '') ?></td>
                        <td><?= cc_h($row['current_didar_contact_id'] ?? '') ?></td>
                        <td><?= cc_h($row['current_customer_code'] ?? '') ?></td>
                        <td class="truncate" title="<?= cc_h($row['current_name'] ?? '') ?>"><?= cc_h($row['current_name'] ?? '') ?></td>
                        <td><?= cc_h($row['current_phones'] ?? '') ?></td>
                        <td class="truncate" title="<?= cc_h($row['backup_name'] ?? '') ?>"><?= cc_h($row['backup_name'] ?? '') ?></td>
                        <td><?= cc_h($row['backup_phones'] ?? '') ?></td>
                        <td>
                            <?php if (!empty($row['recreated_present'])): ?>
                                id <?= cc_h($row['recreated_person_id']) ?><br>
                                contact <?= cc_h($row['recreated_didar_id'] ?? '') ?><br>
                                code <?= cc_h($row['recreated_customer_code']) ?><br>
                                phones <?= cc_h($row['recreated_phones'] ?? '') ?><br>
                                <?= !empty($row['recreated_delete_safe']) ? '<span class="ok">orphan delete safe</span>' : '<span class="err">orphan delete blocked</span>' ?><br>
                                <span class="truncate" title="<?= cc_h($row['recreated_delete_reason'] ?? '') ?>"><?= cc_h($row['recreated_delete_reason'] ?? '') ?></span>
                            <?php else: ?>
                                missing
                            <?php endif; ?>
                        </td>
                        <td>
                            current <?= cc_h((int)($row['current_deal_count'] ?? 0)) ?><br>
                            recreated deals <?= cc_h((int)($row['recreated_deal_count'] ?? 0)) ?><br>
                            recreated activities <?= cc_h((int)($row['recreated_activity_count'] ?? 0)) ?><br>
                            support <?= cc_h((int)($row['recreated_support_count'] ?? 0)) ?> /
                            access <?= cc_h((int)($row['recreated_access_request_count'] ?? 0)) ?><br>
                            referrals <?= cc_h((int)($row['recreated_referral_count'] ?? 0)) ?> /
                            satisfaction <?= cc_h((int)($row['recreated_satisfaction_count'] ?? 0)) ?><br>
                            owner history <?= cc_h((int)($row['recreated_owner_history_contact_count'] ?? 0) + (int)($row['recreated_owner_history_person_count'] ?? 0)) ?><br>
                            virtual stages <?= cc_h((int)($row['recreated_virtual_stage_count'] ?? 0)) ?><br>
                            last current deal <?= cc_h($row['current_last_deal_at'] ?? '') ?>
                        </td>
                        <td class="truncate" title="<?= cc_h($row['repair_reason'] ?? '') ?>"><?= cc_h($row['repair_reason'] ?? '') ?></td>
                    </tr>
                <?php endforeach; ?>
                <?php if (!$classifiedRows): ?>
                    <tr><td colspan="12">No recovery rows found.</td></tr>
                <?php endif; ?>
                </tbody>
            </table>
        </div>
    </div>
</div>
</body>
</html>
