<?php
require 'db.php';
require 'functions.php';
requireRole(['rnduser']);

try {
    $completed = $pdo->query("SELECT COUNT(*) c FROM villager WHERE isdeleted = 0 AND status = 'approved' AND paymentstatus = 'paid'")->fetch()['c'];
    $active = $pdo->query("SELECT COUNT(*) c FROM villager WHERE isdeleted = 0 AND passactive = 1")->fetch()['c'];
    $printed = $pdo->query("SELECT COUNT(*) c FROM villager WHERE isdeleted = 0 AND isprinted = 1")->fetch()['c'];
    $expiring = $pdo->query("SELECT COUNT(*) c FROM villager WHERE isdeleted = 0 AND passactive = 1
        AND passenddate BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY)")->fetch()['c'];

    $panchayatrows = $pdo->query("SELECT p.name, COUNT(v.id) c FROM panchayat p
        LEFT JOIN villager v ON v.panchayatid = p.id AND v.isdeleted = 0 AND v.status = 'approved' AND v.paymentstatus = 'paid'
        WHERE p.isdeleted = 0 GROUP BY p.id, p.name")->fetchAll();

    $labels = [];
    $counts = [];
    foreach ($panchayatrows as $row) {
        $labels[] = $row['name'];
        $counts[] = (int)$row['c'];
    }

    jsonOut(true, '', [
        'completed' => (int)$completed,
        'active' => (int)$active,
        'printed' => (int)$printed,
        'expiring' => (int)$expiring,
        'panchayatlabels' => $labels,
        'panchayatcounts' => $counts,
    ]);
} catch (Exception $e) {
    logDebug('[ajax_rnd_dashboardstats] ' . $e->getMessage());
    jsonOut(false, 'Failed to load dashboard stats');
}
