<?php
ob_start();
require_once __DIR__ . '/../config/auth.php';
$user = require_auth();
require_admin($user);

try {
    $db     = db();
    $action = $_POST['action'] ?? $_GET['action'] ?? 'list';

    // ── Helper: normalise a string for comparison ─────────────────────────
    function norm(string $v): string {
        return strtoupper(preg_replace('/\s+/', '', trim($v)));
    }

    switch ($action) {

        // ── List all duplicate groups ─────────────────────────────────────
        case 'list':
            $stmt = $db->query(
                "SELECT e.record_id, e.client_employees_name, e.surname,
                        e.i_doc_passport, e.clients_id, e.company_number,
                        e.occupation, e.cell, c.clients_name
                 FROM client_employees e
                 LEFT JOIN clients c ON c.record_id = e.clients_id
                 WHERE e.i_doc_passport IS NOT NULL
                   AND TRIM(e.i_doc_passport) NOT IN ('','0')
                 ORDER BY e.i_doc_passport, e.record_id ASC"
            );
            $all = $stmt->fetchAll();

            // Group by normalised ID only
            $groups = [];
            foreach ($all as $row) {
                $key = norm($row['i_doc_passport']);
                $groups[$key][] = $row;
            }

            // Keep only groups with 2+ records
            $dupes = [];
            foreach ($groups as $key => $rows) {
                if (count($rows) < 2) continue;
                usort($rows, fn($a,$b) => $a['record_id'] - $b['record_id']);
                $primary = $rows[0];

                // Count assessments and tests for each record
                $all_ids = array_column($rows, 'record_id');
                $ph = implode(',', array_fill(0, count($all_ids), '?'));

                $ass_counts = [];
                $s = $db->prepare("SELECT client_employees_id, COUNT(*) as cnt FROM assesses WHERE client_employees_id IN ($ph) GROUP BY client_employees_id");
                $s->execute($all_ids);
                foreach ($s->fetchAll() as $r) $ass_counts[$r['client_employees_id']] = (int)$r['cnt'];

                $test_counts = [];
                $s = $db->prepare("SELECT client_employees_id, COUNT(*) as cnt FROM tests_sec WHERE client_employees_id IN ($ph) GROUP BY client_employees_id");
                $s->execute($all_ids);
                foreach ($s->fetchAll() as $r) $test_counts[$r['client_employees_id']] = (int)$r['cnt'];

                $booking_counts = [];
                foreach ($all_ids as $eid) {
                    $s = $db->prepare("SELECT COUNT(*) FROM bookings WHERE client_employees LIKE ?");
                    $s->execute(["%,$eid,%"]);
                    $booking_counts[$eid] = (int)$s->fetchColumn();
                }

                foreach ($rows as &$row) {
                    $row['assessments_count'] = $ass_counts[$row['record_id']] ?? 0;
                    $row['tests_count']       = $test_counts[$row['record_id']] ?? 0;
                    $row['bookings_count']    = $booking_counts[$row['record_id']] ?? 0;
                    $row['is_primary']        = $row['record_id'] === $primary['record_id'];
                }
                unset($row);

                $dupes[] = [
                    'key'        => $key,
                    'primary_id' => $primary['record_id'],
                    'records'    => $rows,
                    'total_assessments' => array_sum(array_column($rows, 'assessments_count')),
                    'total_tests'       => array_sum(array_column($rows, 'tests_count')),
                    'total_bookings'    => array_sum(array_column($rows, 'bookings_count')),
                ];
            }

            json_success(['groups' => $dupes, 'total_groups' => count($dupes)]);
            break;

        // ── Preview what a merge will do (no changes) ─────────────────────
        case 'preview':
            $primary_id  = (int)($_POST['primary_id'] ?? 0);
            $dup_ids_raw = json_decode($_POST['dup_ids'] ?? '[]', true);
            $dup_ids     = array_map('intval', array_filter($dup_ids_raw));
            if (!$primary_id || empty($dup_ids)) json_error('primary_id and dup_ids required');

            $preview = [];
            $ph = implode(',', array_fill(0, count($dup_ids), '?'));

            $s = $db->prepare("SELECT record_id, assesses_name, date, results FROM assesses WHERE client_employees_id IN ($ph) ORDER BY record_id");
            $s->execute($dup_ids);
            $preview['assessments'] = $s->fetchAll();

            $s = $db->prepare("SELECT record_id, test_name, date, results FROM tests_sec WHERE client_employees_id IN ($ph) ORDER BY record_id");
            $s->execute($dup_ids);
            $preview['tests'] = $s->fetchAll();

            // Bookings that contain any of the dup IDs
            $preview['bookings'] = [];
            foreach ($dup_ids as $did) {
                $s = $db->prepare("SELECT record_id, booking_number, date_booked, status, client_employees FROM bookings WHERE client_employees LIKE ?");
                $s->execute(["%,$did,%"]);
                foreach ($s->fetchAll() as $b) {
                    if (!isset($preview['bookings'][$b['record_id']])) {
                        $b['affected_dup_ids'] = [$did];
                        $preview['bookings'][$b['record_id']] = $b;
                    } else {
                        $preview['bookings'][$b['record_id']]['affected_dup_ids'][] = $did;
                    }
                }
            }
            $preview['bookings'] = array_values($preview['bookings']);

            json_success(['preview' => $preview]);
            break;

        // ── Merge duplicates into primary ─────────────────────────────────
        case 'merge':
            $primary_id  = (int)($_POST['primary_id'] ?? 0);
            $dup_ids_raw = json_decode($_POST['dup_ids'] ?? '[]', true);
            $dup_ids     = array_map('intval', array_filter($dup_ids_raw));
            $description = trim($_POST['description'] ?? 'Merge duplicates into ID ' . $primary_id);
            if (!$primary_id || empty($dup_ids)) json_error('primary_id and dup_ids required');

            $ph = implode(',', array_fill(0, count($dup_ids), '?'));

            // ── 1. Build backup payload ───────────────────────────────────
            $backup = ['primary_id' => $primary_id, 'dup_ids' => $dup_ids, 'changes' => []];

            // Employee records being deleted
            $s = $db->prepare("SELECT * FROM client_employees WHERE record_id IN ($ph)");
            $s->execute($dup_ids);
            $backup['deleted_employees'] = $s->fetchAll();

            // Assessments being reassigned
            $s = $db->prepare("SELECT record_id, client_employees_id FROM assesses WHERE client_employees_id IN ($ph)");
            $s->execute($dup_ids);
            $backup['changes']['assesses'] = $s->fetchAll();

            // Tests being reassigned
            $s = $db->prepare("SELECT record_id, client_employees_id FROM tests_sec WHERE client_employees_id IN ($ph)");
            $s->execute($dup_ids);
            $backup['changes']['tests_sec'] = $s->fetchAll();

            // Bookings CSV fields
            $backup['changes']['bookings'] = [];
            foreach ($dup_ids as $did) {
                $s = $db->prepare("SELECT record_id, client_employees, assessments, tests, client_sites FROM bookings WHERE client_employees LIKE ?");
                $s->execute(["%,$did,%"]);
                foreach ($s->fetchAll() as $b) {
                    if (!isset($backup['changes']['bookings'][$b['record_id']])) {
                        $backup['changes']['bookings'][$b['record_id']] = $b;
                    }
                }
            }
            $backup['changes']['bookings'] = array_values($backup['changes']['bookings']);

            // Save backup
            $db->prepare("INSERT INTO dedupe_backups (user_id, description, payload) VALUES (?,?,?)")
               ->execute([$user['record_id'], $description, json_encode($backup)]);
            $backup_id = $db->lastInsertId();

            // ── 2. Reassign assesses ──────────────────────────────────────
            $db->prepare("UPDATE assesses SET client_employees_id = ? WHERE client_employees_id IN ($ph)")
               ->execute(array_merge([$primary_id], $dup_ids));

            // ── 3. Reassign tests_sec ─────────────────────────────────────
            $db->prepare("UPDATE tests_sec SET client_employees_id = ? WHERE client_employees_id IN ($ph)")
               ->execute(array_merge([$primary_id], $dup_ids));

            // ── 4. Fix bookings CSV fields ────────────────────────────────
            foreach ($dup_ids as $did) {
                $s = $db->prepare("SELECT record_id, client_employees, assessments, tests, client_sites FROM bookings WHERE client_employees LIKE ?");
                $s->execute(["%,$did,%"]);
                while ($b = $s->fetch()) {
                    // Replace duplicate ID with primary ID in all four CSV fields
                    $new_emps  = str_replace(",$did,", ",$primary_id,", $b['client_employees']);
                    $new_ass   = $b['assessments']   ?? '';
                    $new_tests = $b['tests']          ?? '';
                    $new_sites = $b['client_sites']   ?? '';
                    $db->prepare("UPDATE bookings SET client_employees=? WHERE record_id=?")
                       ->execute([$new_emps, $b['record_id']]);
                }
            }

            // ── 5. Delete duplicate employee records ──────────────────────
            $db->prepare("DELETE FROM client_employees WHERE record_id IN ($ph)")
               ->execute($dup_ids);

            json_success(['backup_id' => $backup_id, 'backup_id' => $backup_id], 'Merge complete. Backup ID: ' . $backup_id);
            break;

        // ── Rollback a backup ─────────────────────────────────────────────
        case 'rollback':
            $backup_id = (int)($_POST['backup_id'] ?? 0);
            if (!$backup_id) json_error('backup_id required');

            $s = $db->prepare("SELECT * FROM dedupe_backups WHERE record_id = ?");
            $s->execute([$backup_id]);
            $bk = $s->fetch();
            if (!$bk) json_error('Backup not found');

            $payload = json_decode($bk['payload'], true);
            $primary_id = $payload['primary_id'];
            $dup_ids    = $payload['dup_ids'];

            // ── 1. Restore employee records ───────────────────────────────
            foreach ($payload['deleted_employees'] as $emp) {
                // Check if still exists (avoid re-insert if rollback run twice)
                $chk = $db->prepare("SELECT record_id FROM client_employees WHERE record_id = ?");
                $chk->execute([$emp['record_id']]);
                if (!$chk->fetch()) {
                    $cols = implode(',', array_map(fn($k) => "`$k`", array_keys($emp)));
                    $ph2  = implode(',', array_fill(0, count($emp), '?'));
                    $db->prepare("INSERT INTO client_employees ($cols) VALUES ($ph2)")
                       ->execute(array_values($emp));
                }
            }

            // ── 2. Restore assesses ───────────────────────────────────────
            foreach ($payload['changes']['assesses'] as $row) {
                $db->prepare("UPDATE assesses SET client_employees_id = ? WHERE record_id = ?")
                   ->execute([$row['client_employees_id'], $row['record_id']]);
            }

            // ── 3. Restore tests_sec ──────────────────────────────────────
            foreach ($payload['changes']['tests_sec'] as $row) {
                $db->prepare("UPDATE tests_sec SET client_employees_id = ? WHERE record_id = ?")
                   ->execute([$row['client_employees_id'], $row['record_id']]);
            }

            // ── 4. Restore booking CSV fields ─────────────────────────────
            foreach ($payload['changes']['bookings'] as $b) {
                $db->prepare("UPDATE bookings SET client_employees=?, assessments=?, tests=?, client_sites=? WHERE record_id=?")
                   ->execute([$b['client_employees'], $b['assessments'], $b['tests'], $b['client_sites'], $b['record_id']]);
            }

            json_success([], 'Rollback complete for backup #' . $backup_id);
            break;

        // ── List backups ──────────────────────────────────────────────────
        case 'list_backups':
            $s = $db->query("SELECT b.record_id, b.created_at, b.description, u.safesure_users_name
                             FROM dedupe_backups b
                             LEFT JOIN safesure_users u ON u.record_id = b.user_id
                             ORDER BY b.record_id DESC LIMIT 50");
            json_success(['backups' => $s->fetchAll()]);
            break;

        default:
            json_error('Unknown action');
    }

} catch (Throwable $e) {
    json_error('dedupe.php [' . $e->getLine() . ']: ' . $e->getMessage(), 500);
}