<?php
/**
 * STH NRMS — Clinical Rotation Status Auto-Advance
 * File: include/clinical_rotation_status_sync.php
 *
 * Single source of truth for keeping these three things in sync on every
 * page load that touches rotation data:
 *   1. student_rotation_details.status   (day-level: Pending/Ongoing/Completed)
 *   2. clinical_rotations.status         (batch-level: Active/Completed)
 *   3. clinical_students.rotation_status (student-level: Not_started/In_progress/
 *                                          Completed/On_hold/Cancelled)
 *
 * THE LIFECYCLE PROBLEM THIS SOLVES:
 *   Clinical students rotate in cycles — they come for a placement, finish
 *   it, return to school, and come back later for another placement. Before
 *   this fix, clinical_students.rotation_status only ever moved forward
 *   (Not_started -> In_progress -> Completed) and NEVER reset, so a
 *   returning student could not be re-selected in create_clinical_rotation.php
 *   (which only lists rotation_status = 'Not_started' students) without a
 *   staff member manually editing their status in manage_clinical_students.php.
 *
 *   updateClinicalStudentRotationStatus() now resets a student back to
 *   'Not_started' once every rotation day they've ever had is resolved
 *   (Completed/Cancelled) AND there's no still-Active batch waiting on them
 *   — regardless of whether this is their 1st or 5th rotation visit. The
 *   clinical_students row itself is NEVER re-created or touched outside this
 *   status field — their profile, login, and full history persist forever.
 *
 * USAGE — call once near the top of any page after $conn is available:
 *   require_once '../include/clinical_rotation_status_sync.php';
 *   rotationStatusAutoAdvance($conn);
 *
 * PHP 7 COMPATIBLE.
 */

/**
 * Recalculates and persists a single student's rotation_status based on
 * the current state of all their rotation days across all their batches.
 * Returns the resulting status string.
 */
function updateClinicalStudentRotationStatus($student_id, $conn) {
    $stmt = $conn->prepare("
        SELECT
            COUNT(*) as total_details,
            SUM(CASE WHEN srd.status = 'Ongoing'   THEN 1 ELSE 0 END) as ongoing_count,
            SUM(CASE WHEN srd.status = 'Completed' THEN 1 ELSE 0 END) as completed_count,
            SUM(CASE WHEN srd.status = 'Cancelled' THEN 1 ELSE 0 END) as cancelled_count
        FROM student_rotation_details srd
        JOIN clinical_rotations cr ON srd.clinical_rotation_id = cr.id
        WHERE srd.clinical_student_id = ?
          AND cr.status != 'Cancelled'
    ");
    $stmt->bind_param("i", $student_id);
    $stmt->execute();
    $data = $stmt->get_result()->fetch_assoc();
    $stmt->close();

    $total     = $data['total_details']   ?? 0;
    $ongoing   = $data['ongoing_count']   ?? 0;
    $completed = $data['completed_count'] ?? 0;
    $cancelled = $data['cancelled_count'] ?? 0;

    // A batch is only "in progress" for selectability purposes while
    // clinical_rotations.status = 'Active' — distinct from the day-level
    // $ongoing count, which can briefly be 0 between two day-rows even
    // mid-batch (e.g. a weekend gap with no rotation_date rows).
    $active_batch_stmt = $conn->prepare("
        SELECT COUNT(*) AS active_batches
        FROM clinical_rotations
        WHERE student_id = ? AND status = 'Active'
    ");
    $active_batch_stmt->bind_param("i", $student_id);
    $active_batch_stmt->execute();
    $has_active_batch = (int)($active_batch_stmt->get_result()->fetch_assoc()['active_batches'] ?? 0) > 0;
    $active_batch_stmt->close();

    if ($ongoing > 0) {
        $new_status = 'In_progress';
    } elseif ($total > 0 && ($completed + $cancelled) == $total && !$has_active_batch) {
        // Every rotation day this student has ever had is resolved, and
        // there's no still-Active batch waiting on them. Reset to
        // Not_started — whether this is their 1st or 5th rotation
        // finishing — so they reappear as selectable for a new visit.
        $new_status = 'Not_started';
    } elseif ($total == 0) {
        $new_status = 'Not_started';
    } else {
        // ── FIX: this branch is reached whenever a student has an active,
        // still-unresolved batch whose day-rows haven't reached 'Ongoing'
        // yet — most commonly a future-dated rotation the day after it was
        // created (create_clinical_rotation.php's own UPDATE already set
        // 'In_progress' at creation time, but this function re-derives the
        // status from scratch on every page load via rotationStatusAutoAdvance(),
        // silently overwriting that). The previous value here, 'Pending', is
        // not one of clinical_students.rotation_status's five valid enum
        // values (Not_started/In_progress/Completed/On_hold/Cancelled), so
        // MySQL (outside strict mode) silently truncated it to an empty
        // string on every write — corrupting the very status this function
        // exists to keep correct, confirmed empirically against a real
        // future-dated test batch. A student on an active but not-yet-begun
        // batch is still correctly described as 'In_progress' (they are
        // assigned and unavailable for a new batch), not any other state.
        $new_status = 'In_progress';
    }

    $update_stmt = $conn->prepare("
        UPDATE clinical_students
        SET rotation_status = ?, updated_at = NOW()
        WHERE id = ? AND rotation_status != ?
    ");
    $update_stmt->bind_param("sis", $new_status, $student_id, $new_status);
    $update_stmt->execute();
    $update_stmt->close();

    return $new_status;
}

/**
 * Runs the full auto-advance sweep: day-level status updates, batch
 * completion, and per-student rotation_status sync. Safe to call on
 * every page load — all updates are conditional (WHERE status != ...)
 * so repeated calls are cheap no-ops once everything is already in sync.
 */
function rotationStatusAutoAdvance(mysqli $conn): void {
    $today = date('Y-m-d');

    // ── Day-level: normalise any empty/NULL status rows ────────────────────
    $stmt1 = $conn->prepare("
        UPDATE student_rotation_details
        SET    status = 'Pending', updated_at = NOW()
        WHERE  (status IS NULL OR status = '')
          AND  rotation_date >= ?
    ");
    $stmt1->bind_param("s", $today);
    $stmt1->execute();
    $stmt1->close();

    $stmt2 = $conn->prepare("
        UPDATE student_rotation_details
        SET    status = 'Completed', updated_at = NOW()
        WHERE  (status IS NULL OR status = '')
          AND  rotation_date < ?
    ");
    $stmt2->bind_param("s", $today);
    $stmt2->execute();
    $stmt2->close();

    // ── Day-level: Ongoing rows that have passed become Completed ──────────
    $stmt3 = $conn->prepare("
        UPDATE student_rotation_details
        SET    status = 'Completed', updated_at = NOW()
        WHERE  status = 'Ongoing' AND rotation_date < ?
    ");
    $stmt3->bind_param("s", $today);
    $stmt3->execute();
    $stmt3->close();

    // ── Day-level: today's Pending rows become Ongoing ──────────────────────
    $stmt4 = $conn->prepare("
        UPDATE student_rotation_details
        SET    status = 'Ongoing', updated_at = NOW()
        WHERE  status = 'Pending' AND rotation_date = ?
    ");
    $stmt4->bind_param("s", $today);
    $stmt4->execute();
    $stmt4->close();

    // ── Batch-level: complete clinical_rotations whose days are all done ───
    $stmt5 = $conn->prepare("
        UPDATE clinical_rotations cr
        SET    cr.status = 'Completed', cr.updated_at = NOW()
        WHERE  cr.status = 'Active'
          AND  cr.end_date < ?
          AND  NOT EXISTS (
              SELECT 1 FROM student_rotation_details srd
              WHERE  srd.clinical_rotation_id = cr.id
                AND  srd.status NOT IN ('Completed','Cancelled')
          )
    ");
    $stmt5->bind_param("s", $today);
    $stmt5->execute();
    $stmt5->close();

    // ── Student-level: sync rotation_status for every student who has ever
    //    had a rotation batch (Active or Completed) ──────────────────────────
    $active_students_q = $conn->query("
        SELECT DISTINCT cs.id
        FROM clinical_students cs
        JOIN clinical_rotations cr ON cr.student_id = cs.id
        WHERE cs.status = 'Active'
          AND cr.status IN ('Active', 'Completed')
    ");
    if ($active_students_q) {
        foreach ($active_students_q->fetch_all(MYSQLI_ASSOC) as $row) {
            updateClinicalStudentRotationStatus((int)$row['id'], $conn);
        }
    }
}