<?php
/**
 * ajax/fix_stuck_queue_entries.php
 *
 * ONE-TIME cleanup script — closes all patient_queue rows that are
 * still WAITING / IN_SERVICE even though their consultation or visit
 * is already completed/cancelled.
 *
 * Run once via browser or CLI, then DELETE this file from the server.
 *
 * SAFETY GUARDS:
 *  - Wrapped in a transaction (full rollback on any error)
 *  - Dry-run mode (set $dry_run = true to preview without writing)
 *  - Only touches rows where we can confirm work is done
 *  - Does NOT touch rows with no linked consultation/visit (ambiguous)
 */

session_start();
include '../layouts/dbconn.php'; // provides $conn (MySQLi)

/* ── Auth: restrict to SUPER_ADMIN / HOSPITAL_ADMIN only ─────────────────── */
$allowed_roles = ['SUPER_ADMIN', 'HOSPITAL_ADMIN'];
if (!isset($_SESSION['role_code']) || !in_array($_SESSION['role_code'], $allowed_roles)) {
    http_response_code(403);
    die(json_encode(['success' => false, 'message' => 'Access denied.']));
}

/* ── Set to true to preview rows without updating ────────────────────────── */
$dry_run = isset($_GET['dry_run']) && $_GET['dry_run'] === '1';

header('Content-Type: application/json');

/* ═══════════════════════════════════════════════════════════════════════════
   STEP 1 — Find all stuck queue entries
   ─────────────────────────────────────────────────────────────────────────
   A queue entry is "stuck" when:
     (a) Linked via visit_id → the consultation on that visit is COMPLETED
                              OR the patient_visit itself is COMPLETED
     (b) Linked via patient_id only (visit_id IS NULL) → a completed
         consultation exists for that patient on the same calendar day
═══════════════════════════════════════════════════════════════════════════ */
$preview_sql = "
    SELECT
        q.queue_id,
        q.patient_id,
        q.visit_id,
        q.queue_number,
        q.patient_name,
        q.queue_status,
        q.queue_entry_time,
        q.service_start_time,
        COALESCE(c.consultation_number, 'No consultation') AS consultation_number,
        COALESCE(c.status, 'N/A')                          AS consultation_status,
        COALESCE(pv.status, 'N/A')                         AS visit_status,
        -- reason helps audit log
        CASE
            WHEN pv.status IN ('COMPLETED','CANCELLED')        THEN 'visit_completed'
            WHEN c.status  IN ('COMPLETED','CANCELLED')        THEN 'consultation_completed'
            WHEN c.consultation_id IS NOT NULL
                 AND c.status = 'COMPLETED'
                 AND q.visit_id IS NULL                        THEN 'visit_null_consult_done'
            ELSE 'unknown'
        END AS close_reason
    FROM patient_queue q

    -- join visit when available
    LEFT JOIN patient_visits pv
           ON pv.visit_id = q.visit_id

    -- join consultation either via visit_id or patient_id+date
    LEFT JOIN consultations c
           ON (
                  (q.visit_id IS NOT NULL AND c.visit_id    = q.visit_id)
               OR (q.visit_id IS NULL     AND c.patient_id  = q.patient_id
                   AND DATE(c.started_at) = DATE(q.queue_entry_time))
              )

    WHERE q.queue_status IN ('WAITING','IN_SERVICE')
      AND (
              pv.status  IN ('COMPLETED','CANCELLED')
           OR c.status   IN ('COMPLETED','CANCELLED')
          )

    ORDER BY q.queue_entry_time ASC
";

$preview_result = $conn->query($preview_sql);
if (!$preview_result) {
    echo json_encode(['success' => false, 'message' => 'Preview query failed: ' . $conn->error]);
    exit;
}

$stuck_rows = $preview_result->fetch_all(MYSQLI_ASSOC);
$total      = count($stuck_rows);

if ($total === 0) {
    echo json_encode([
        'success' => true,
        'message' => 'No stuck queue entries found. Queue is clean.',
        'updated' => 0,
    ]);
    exit;
}

/* ── Dry-run: return preview only, no writes ─────────────────────────────── */
if ($dry_run) {
    echo json_encode([
        'success'  => true,
        'dry_run'  => true,
        'message'  => "DRY RUN — $total stuck entries found. No changes made.",
        'total'    => $total,
        'rows'     => $stuck_rows,
    ]);
    exit;
}

/* ═══════════════════════════════════════════════════════════════════════════
   STEP 2 — Apply the fix inside a transaction
═══════════════════════════════════════════════════════════════════════════ */
$conn->begin_transaction();

try {

    $fix_sql = "
        UPDATE patient_queue q

        LEFT JOIN patient_visits pv
               ON pv.visit_id = q.visit_id

        LEFT JOIN consultations c
               ON (
                      (q.visit_id IS NOT NULL AND c.visit_id   = q.visit_id)
                   OR (q.visit_id IS NULL     AND c.patient_id = q.patient_id
                       AND DATE(c.started_at) = DATE(q.queue_entry_time))
                  )

        SET
            q.queue_status          = 'COMPLETED',
            q.service_end_time      = COALESCE(
                                          c.completed_at,
                                          pv.updated_at,
                                          NOW()
                                      ),
            q.service_start_time    = COALESCE(
                                          q.service_start_time,
                                          c.started_at,
                                          q.queue_entry_time
                                      ),
            q.total_service_minutes = TIMESTAMPDIFF(
                                          MINUTE,
                                          COALESCE(q.service_start_time, c.started_at, q.queue_entry_time),
                                          COALESCE(c.completed_at, pv.updated_at, NOW())
                                      ),
            q.updated_by            = ?

        WHERE q.queue_status IN ('WAITING','IN_SERVICE')
          AND (
                  pv.status IN ('COMPLETED','CANCELLED')
               OR c.status  IN ('COMPLETED','CANCELLED')
              )
    ";

    $admin_id = (int)$_SESSION['user_id'];
    $stmt = $conn->prepare($fix_sql);
    if (!$stmt) throw new Exception('Fix prepare failed: ' . $conn->error);

    $stmt->bind_param('i', $admin_id);
    if (!$stmt->execute()) throw new Exception('Fix execute failed: ' . $stmt->error);

    $affected = $stmt->affected_rows;
    $stmt->close();

    $conn->commit();

    echo json_encode([
        'success'  => true,
        'dry_run'  => false,
        'message'  => "Done. $affected queue entries closed.",
        'affected' => $affected,
        'previewed'=> $stuck_rows, // shows what was fixed
    ]);

} catch (Exception $e) {
    if ($conn->in_transaction) $conn->rollback();
    error_log('fix_stuck_queue: ' . $e->getMessage());
    echo json_encode([
        'success' => false,
        'message' => 'Transaction failed and was rolled back: ' . $e->getMessage(),
    ]);
}