<?php
// api/auto_update_status.php
// Updates member status to 'Unavailable' if not in any CLOSED roster for 4 weeks
// Only applies to members with a Unit (Workers)

require_once '../config/database.php';
require_once '../includes/functions.php';

$response = ['success' => false, 'message' => '', 'affected' => 0];

try {
    // 1. Find the date 4 weeks ago
    $cutoff_date = date('Y-m-d', strtotime('-4 weeks'));

    // 2. Identify members who HAVE worked in the last 4 weeks (in CLOSED rosters)
    // We look at rosters linked to services where service_date >= cutoff AND roster status is Closed
    $active_sql = "
        SELECT DISTINCT ra.member_id 
        FROM roster_assignments ra
        JOIN rosters r ON ra.roster_id = r.id
        JOIN services s ON r.service_id = s.id
        WHERE s.service_date >= :cutoff
        AND r.status = 'Closed'
        AND ra.member_id IS NOT NULL
    ";

    // 3. Update members who are NOT in that list
    // AND currently 'Available'
    // AND belong to a Unit (are workers)
    // AND joined more than 4 weeks ago (give new members a grace period)
    
    $sql = "
        UPDATE members 
        SET status = 'Not Available' 
        WHERE status = 'Available'
        AND unit IS NOT NULL AND unit != ''
        AND date_joined < :cutoff
        AND id NOT IN ($active_sql)
    ";

    $stmt = $pdo->prepare($sql);
    $stmt->execute(['cutoff' => $cutoff_date]);
    
    $count = $stmt->rowCount();
    $response['success'] = true;
    $response['affected'] = $count;
    $response['message'] = "Updated $count members to 'Not Available' due to inactivity since $cutoff_date.";

} catch (Exception $e) {
    $response['message'] = $e->getMessage();
}

// Return JSON if AJAX, or plain text if CRON/Browser
if (isset($_GET['json'])) {
    header('Content-Type: application/json');
    echo json_encode($response);
} else {
    echo "<h1>Member Status Auto-Update</h1>";
    echo "<p>" . htmlspecialchars($response['message']) . "</p>";
    if ($response['success']) {
        echo "<p>Affected Rows: " . $response['affected'] . "</p>";
    }
}
?>
