<?php
// pages/reports_member_history.php
requireRole('any'); // Coordinators/Admins

// Filters
$category = isset($_GET['category']) ? $_GET['category'] : '';
$status = isset($_GET['status']) ? $_GET['status'] : '';
$start_date = isset($_GET['start_date']) ? $_GET['start_date'] : date('Y-m-d', strtotime('-3 months'));
$end_date = isset($_GET['end_date']) ? $_GET['end_date'] : date('Y-m-d');

// Fetch Members with Stats
// We need: Name, Category, Status, Total Rosters (in range), Last Rostered (in range)
$sql = "
    SELECT 
        m.id, m.full_name, m.category, m.status,
        COUNT(ra.id) as roster_count,
        MAX(s.service_date) as last_rostered
    FROM members m
    LEFT JOIN roster_assignments ra ON m.id = ra.member_id
    LEFT JOIN rosters r ON ra.roster_id = r.id
    LEFT JOIN services s ON r.service_id = s.id AND s.service_date BETWEEN :start AND :end
    WHERE 1=1
";

$params = ['start' => $start_date, 'end' => $end_date];

if ($category) {
    $sql .= " AND m.category = :cat";
    $params['cat'] = $category;
}
if ($status) {
    $sql .= " AND m.status = :stat";
    $params['stat'] = $status;
}

$sql .= " GROUP BY m.id ORDER BY m.full_name ASC";

$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$report_data = $stmt->fetchAll();

// Get categories for filter
$cats = $pdo->query("SELECT DISTINCT category FROM members WHERE category IS NOT NULL")->fetchAll(PDO::FETCH_COLUMN);
?>

<div style="margin-bottom: 20px;">
    <h1>Member Availability & Roster Report</h1>
    
    <div class="card">
        <form method="GET" style="display: flex; gap: 10px; align-items: flex-end; flex-wrap: wrap;">
            <input type="hidden" name="page" value="reports_member_history">
            
            <div class="form-group" style="margin: 0;">
                <label>Category</label>
                <select name="category" class="form-control">
                    <option value="">All Categories</option>
                    <?php foreach($cats as $c) echo "<option value='$c' ".($category==$c?'selected':'').">$c</option>"; ?>
                </select>
            </div>
            
            <div class="form-group" style="margin: 0;">
                <label>Status</label>
                <select name="status" class="form-control">
                    <option value="">All Statuses</option>
                    <option value="Available" <?php if($status=='Available') echo 'selected'; ?>>Available</option>
                    <option value="Not Available" <?php if($status=='Not Available') echo 'selected'; ?>>Not Available</option>
                </select>
            </div>
            
             <div class="form-group" style="margin: 0;">
                <label>From</label>
                <input type="date" name="start_date" class="form-control" value="<?php echo $start_date; ?>">
            </div>
            
            <div class="form-group" style="margin: 0;">
                <label>To</label>
                <input type="date" name="end_date" class="form-control" value="<?php echo $end_date; ?>">
            </div>
            
            <button type="submit" class="btn">Filter</button>
            <button type="button" onclick="window.print()" class="btn" style="background-color: #666;"><i class="fas fa-print"></i> Print</button>
        </form>
    </div>
    
    <div class="card" style="margin-top: 20px;">
        <div class="table-responsive">
            <table>
                <thead>
                    <tr>
                        <th>Member Name</th>
                        <th>Category</th>
                        <th>Status</th>
                        <th>Rosters (Selected Period)</th>
                        <th>Last Rostered</th>
                        <th>Utilization</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($report_data as $row): ?>
                    <tr>
                        <td><?php echo htmlspecialchars($row['full_name']); ?></td>
                        <td><?php echo htmlspecialchars($row['category']); ?></td>
                        <td>
                             <span class="badge" style="background: <?php echo $row['status']=='Available'?'var(--success-color)':'var(--danger-color)'; ?>">
                                <?php echo htmlspecialchars($row['status']); ?>
                            </span>
                        </td>
                        <td><?php echo $row['roster_count']; ?></td>
                        <td><?php echo $row['last_rostered'] ? date('M j, Y', strtotime($row['last_rostered'])) : '-'; ?></td>
                        <td>
                            <!-- Simple utilization bar -->
                            <div style="background: #333; height: 10px; width: 100px; border-radius: 5px; overflow: hidden;">
                                <div style="background: var(--accent-color); height: 100%; width: <?php echo min(100, $row['roster_count']*10); ?>%;"></div>
                            </div>
                        </td>
                    </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>
    </div>
</div>
