<?php
// overview.php
chdir('../');
include($_SERVER['DOCUMENT_ROOT'] . '/includes/include_all.inc.php');
/* **************** HEADER FROM MAIN (REQUIRED FOR MENU) **************/
include($_SERVER['DOCUMENT_ROOT'] . '/menu.php');
$userDetails = menuUserDetails($_SESSION['front_user_id']);
/* ***************** END HEADER FROM MAIN *****************************/
include($_SERVER['DOCUMENT_ROOT'] . '/admin/inc/header_logic.inc.php');

function fetchGroupStatusData($pdo) {
    $query = "SELECT group_status, COUNT(*) as count FROM wp_groups GROUP BY group_status ORDER BY count DESC";
    $stmt = $pdo->prepare($query);
    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function fetchOverviewData($pdo, $userRole, $groupId) {
    $data = [
        'totalCases' => fetchTotalCases($pdo, $userRole, $groupId),
        'totalGroups' => ($userRole === 'super') ? fetchTotalGroups($pdo) : 0,
        'gradeDistribution' => fetchGradeDistribution($pdo, $userRole, $groupId),
        'averageScore' => fetchAverageScore($pdo, $userRole, $groupId),
        'recentCases' => fetchRecentCases($pdo, $userRole, $groupId),
        'groupStatusData' => fetchGroupStatusData($pdo),
        'scoreData' => fetchScoreData($pdo, $userRole, $groupId),
    ];
    return $data;
}

function fetchGroupData($pdo) {
    $query = "SELECT 
                expiration_date AS expiration_date, 
                group_status, 
                COUNT(*) as count 
              FROM wp_groups 
              GROUP BY expiration_date, group_status 
              ORDER BY 
                CASE 
                  WHEN group_status = 'Active' THEN 1
                  WHEN group_status = 'Expired' THEN 2
                  WHEN group_status = 'Terminated' THEN 3
                  ELSE 4
                END,
                expiration_date DESC";
    $stmt = $pdo->prepare($query);
    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

/* **** NUMERICAL VERSION (NOT PERCENT) **************
function fetchScoreData($pdo, $userRole, $groupId) {
    $query = "SELECT 
        CASE 
            WHEN CAST(SUBSTRING_INDEX(case_score, '/', 1) AS SIGNED) <= 200 THEN '0-200'
            WHEN CAST(SUBSTRING_INDEX(case_score, '/', 1) AS SIGNED) <= 400 THEN '201-400'
            WHEN CAST(SUBSTRING_INDEX(case_score, '/', 1) AS SIGNED) <= 600 THEN '401-600'
            WHEN CAST(SUBSTRING_INDEX(case_score, '/', 1) AS SIGNED) <= 800 THEN '601-800'
            ELSE '801-1000'
        END AS score_range,
        COUNT(*) as count
    FROM wp_groupusers_data";
    if ($userRole !== 'super') {
        $query .= " INNER JOIN wp_users ON wp_groupusers_data.user_id = wp_users.id WHERE wp_users.user_group = :groupId";
    }
    $query .= " GROUP BY score_range ORDER BY score_range";
    $stmt = $pdo->prepare($query);
    if ($userRole !== 'super') {
        $stmt->bindParam(':groupId', $groupId);
    }
    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
**************************************************** */

/* **** PERCENT VERSION (NORMALIZES SCORES) **************/
function fetchScoreData($pdo, $userRole, $groupId) {
    $query = "SELECT 
        CASE 
            WHEN (CAST(SUBSTRING_INDEX(case_score, '/', 1) AS DECIMAL(10,2)) / CAST(SUBSTRING_INDEX(case_score, '/', -1) AS DECIMAL(10,2))) * 100 <= 20 THEN '0-20%'
            WHEN (CAST(SUBSTRING_INDEX(case_score, '/', 1) AS DECIMAL(10,2)) / CAST(SUBSTRING_INDEX(case_score, '/', -1) AS DECIMAL(10,2))) * 100 <= 40 THEN '21-40%'
            WHEN (CAST(SUBSTRING_INDEX(case_score, '/', 1) AS DECIMAL(10,2)) / CAST(SUBSTRING_INDEX(case_score, '/', -1) AS DECIMAL(10,2))) * 100 <= 60 THEN '41-60%'
            WHEN (CAST(SUBSTRING_INDEX(case_score, '/', 1) AS DECIMAL(10,2)) / CAST(SUBSTRING_INDEX(case_score, '/', -1) AS DECIMAL(10,2))) * 100 <= 80 THEN '61-80%'
            ELSE '81-100%'
        END AS score_range,
        COUNT(*) as count
    FROM wp_groupusers_data";
    if ($userRole !== 'super') {
        $query .= " INNER JOIN wp_users ON wp_groupusers_data.user_id = wp_users.id WHERE wp_users.user_group = :groupId";
    }
    $query .= " GROUP BY score_range ORDER BY score_range";
    $stmt = $pdo->prepare($query);
    if ($userRole !== 'super') {
        $stmt->bindParam(':groupId', $groupId);
    }
    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}


function fetchTotalCases($pdo, $userRole, $groupId) {
    $query = "SELECT COUNT(*) as total FROM wp_groupusers_data";
    if ($userRole !== 'super') {
        $query .= " INNER JOIN wp_users ON wp_groupusers_data.user_id = wp_users.id WHERE wp_users.user_group = :groupId";
    }
    $stmt = $pdo->prepare($query);
    if ($userRole !== 'super') {
        $stmt->bindParam(':groupId', $groupId);
    }
    $stmt->execute();
    return $stmt->fetchColumn();
}

function fetchTotalGroups($pdo) {
    return $pdo->query("SELECT COUNT(*) as total FROM wp_groups")->fetchColumn();
}

function fetchGradeDistribution($pdo, $userRole, $groupId) {
    $query = "SELECT case_grade, COUNT(*) as count FROM wp_groupusers_data";
    if ($userRole !== 'super') {
        $query .= " INNER JOIN wp_users ON wp_groupusers_data.user_id = wp_users.id WHERE wp_users.user_group = :groupId";
    }
    $query .= " GROUP BY case_grade ORDER BY case_grade";
    $stmt = $pdo->prepare($query);
    if ($userRole !== 'super') {
        $stmt->bindParam(':groupId', $groupId);
    }
    $stmt->execute();
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Ensure we have all possible grades, even if count is 0
    $allGrades = ['High Pass', 'Pass', 'Incomplete', 'Fail'];
    $distribution = array_fill_keys($allGrades, 0);
    
    foreach ($result as $row) {
        if (isset($distribution[$row['case_grade']])) {
            $distribution[$row['case_grade']] = (int)$row['count'];
        }
    }
    
    // Convert to the format expected by the chart
    $formattedDistribution = [];
    foreach ($distribution as $grade => $count) {
        $formattedDistribution[] = ['case_grade' => $grade, 'count' => $count];
    }
    
    return $formattedDistribution;
}

/* **** NUMERICAL VERSION (NOT PERCENT) **************
function fetchAverageScore($pdo, $userRole, $groupId) {
    $query = "SELECT AVG(case_score) as average FROM wp_groupusers_data";
    if ($userRole !== 'super') {
        $query .= " INNER JOIN wp_users ON wp_groupusers_data.user_id = wp_users.id WHERE wp_users.user_group = :groupId";
    }
    $stmt = $pdo->prepare($query);
    if ($userRole !== 'super') {
        $stmt->bindParam(':groupId', $groupId);
    }
    $stmt->execute();
    return $stmt->fetchColumn();
}
**************************************************** */

/* **** PERCENT VERSION (NORMALIZES SCORES) **************/
function fetchAverageScore($pdo, $userRole, $groupId) {
    $query = "SELECT AVG((CAST(SUBSTRING_INDEX(case_score, '/', 1) AS DECIMAL(10,2)) / CAST(SUBSTRING_INDEX(case_score, '/', -1) AS DECIMAL(10,2))) * 100) as average_score
              FROM wp_groupusers_data";
    if ($userRole !== 'super') {
        $query .= " INNER JOIN wp_users ON wp_groupusers_data.user_id = wp_users.id WHERE wp_users.user_group = :groupId";
    }
    $stmt = $pdo->prepare($query);
    if ($userRole !== 'super') {
        $stmt->bindParam(':groupId', $groupId);
    }
    $stmt->execute();
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    return round($result['average_score'], 2);
}

function fetchRecentCases($pdo, $userRole, $groupId) {
    $query = "SELECT wp_groupusers_data.*, wp_users.user_email
              FROM wp_groupusers_data
              INNER JOIN wp_users ON wp_groupusers_data.user_id = wp_users.id";
    if ($userRole !== 'super') {
        $query .= " WHERE wp_users.user_group = :groupId";
    }
    $query .= " ORDER BY wp_groupusers_data.datetime DESC LIMIT 5";
    $stmt = $pdo->prepare($query);
    if ($userRole !== 'super') {
        $stmt->bindParam(':groupId', $groupId);
    }
    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

$overviewData = fetchOverviewData($pdo, $_SESSION['user_type'], $_SESSION['user_group']);

// Add this line to check the data
error_log('Grade Distribution Data: ' . json_encode($overviewData['gradeDistribution']));

include($_SERVER['DOCUMENT_ROOT'] . '/admin/header.php');
?>

<h1 class="overview-title">Overview</h1>
<div class="stats-container">
    <div class="stat-item">
        <div class="stat-value"><?= $overviewData['totalCases'] ?></div>
        <div class="stat-label">Total Cases</div>
    </div>
    <?php if ($_SESSION['user_type'] === 'super'): ?>
    <div class="stat-item">
        <div class="stat-value"><?= $overviewData['totalGroups'] ?></div>
        <div class="stat-label">Total Groups</div>
    </div>
    <?php endif; ?>
    <div class="stat-item">
        <div class="stat-value"><?= number_format($overviewData['averageScore'], 2) ?>%</div>
        <div class="stat-label">Average Score</div>
    </div>
    <div class="stat-item" style="width: 100%;">
        <div style="display: flex; justify-content: center; align-items: center;">
            <canvas id="gradeDistributionChart" width="200" height="200" style="width: 200px; height: 200px;"></canvas>
            <div id="chartLegend" style="margin-left: 20px;"></div>
        </div>
        <div class="stat-label">Grade Distribution</div>
    </div>

    <!-- New group status chart -->
    <div class="stat-item" style="width: 100%;">
        <div style="display: flex; justify-content: center; align-items: center;">
            <canvas id="groupStatusChart" width="200" height="200" style="width: 200px; height: 200px;"></canvas>
            <div id="groupStatusLegend" style="margin-left: 20px;"></div>
        </div>
        <div class="stat-label">License Activity</div>
    </div>

    <!-- New score distribution chart -->
    <div class="stat-item" style="width: 100%;">
        <div style="display: flex; justify-content: center; align-items: center;">
            <canvas id="scoreDistributionChart" width="200" height="200" style="width: 200px; height: 200px;"></canvas>
            <div id="scoreDistributionLegend" style="margin-left: 20px;"></div>
        </div>
        <div class="stat-label">Score Distribution</div>
    </div>
</div>

<h2 class="overview-title">Recent Cases</h2>
<table class="home-table">
    <tr>
        <th>User</th>
        <th>Case</th>
        <th>Date</th>
    </tr>
    <?php foreach ($overviewData['recentCases'] as $case): ?>
    <tr>
        <td><?= htmlspecialchars($case['user_email']) ?></td>
        <td><?= htmlspecialchars(substr($case['user_data'], 0, 50)) ?></td>
        <td><?= date('Y-m-d H:i', strtotime($case['datetime'])) ?></td>
    </tr>
    <?php endforeach; ?>
</table>

<script src="https://cdn.jsdelivr.net/npm/chart.js@3.7.1/dist/chart.min.js"></script>
<script>
document.addEventListener('DOMContentLoaded', function() {
    var gradeDistributionData = <?= json_encode($overviewData['gradeDistribution']) ?>;
    var groupStatusData = <?= json_encode($overviewData['groupStatusData']) ?>;
    var scoreData = <?= json_encode($overviewData['scoreData']) ?>;

    // Custom colors for each chart
    var gradeColors = ['#4BC0C0', '#36A2EB', '#FFCE56', '#FF6384'];
    var statusColors = ['#4BC0C0', '#4BC0C0', '#FF9F40', '#999999', '#9966FF'];
    var scoreColors = ['#FF6384', '#FF9F40', '#FFCE56', '#36A2EB', '#4BC0C0'];

    createDonutChart('gradeDistributionChart', gradeDistributionData, 'case_grade', 'count', 'chartLegend', gradeColors);
    createDonutChart('groupStatusChart', groupStatusData, 'group_status', 'count', 'groupStatusLegend', statusColors);
    createDonutChart('scoreDistributionChart', scoreData, 'score_range', 'count', 'scoreDistributionLegend', scoreColors);

    function createDonutChart(canvasId, data, labelKey, valueKey, legendId, colors) {
        var canvas = document.getElementById(canvasId);
        var ctx = canvas.getContext('2d');
        var legendContainer = document.getElementById(legendId);

        var labels = data.map(item => item[labelKey]);
        var values = data.map(item => parseInt(item[valueKey]));

        var total = values.reduce((sum, value) => sum + value, 0);
        var centerX = canvas.width / 2;
        var centerY = canvas.height / 2;
        var radius = Math.min(centerX, centerY) * 0.8;
        var innerRadius = radius * 0.6;
        var startAngle = -Math.PI / 2;

        values.forEach((value, index) => {
            var sliceAngle = 2 * Math.PI * value / total;

            ctx.beginPath();
            ctx.arc(centerX, centerY, radius, startAngle, startAngle + sliceAngle);
            ctx.arc(centerX, centerY, innerRadius, startAngle + sliceAngle, startAngle, true);
            ctx.closePath();
            ctx.fillStyle = colors[index % colors.length];
            ctx.fill();

            startAngle += sliceAngle;

            var legendItem = document.createElement('div');
            legendItem.style.marginBottom = '5px';
            legendItem.innerHTML = `<span style="display:inline-block;width:10px;height:10px;background-color:${colors[index % colors.length]};margin-right:5px;"></span>${labels[index]}: ${value}`;
            legendContainer.appendChild(legendItem);
        });
    }
});
</script>

<?php /* **** Display user activity ****
<h3>User Activity</h3>
<table class="home-table">
    <tr>
        <th>User</th>
        <th>Cases Submitted</th>
    </tr>
    <?php foreach ($userActivity as $activity): ?>
    <tr>
        <td><?= htmlspecialchars($activity['user_email']) ?></td>
        <td><?= $activity['case_count'] ?></td>
    </tr>
    <?php endforeach; ?>
</table> */ ?>

<?php include($_SERVER['DOCUMENT_ROOT'] . '/admin/footer.php'); ?>