<?php
/**
 * Assignment Statistics Module
 * Extension for AssignmentService
 * 
 * This file adds statistics functionality to the AssignmentService class
 */

namespace Services\Assignment;

// Load this file through a require_once in the existing AssignmentService.php

/**
 * Get comprehensive assignment statistics
 * 
 * @return array Statistics data with counts, charts, and activity
 */
function getAssignmentStats() {
    try {
        $conn = $this->db->getConnection();
        
        // Basic stats - counts by status
        $statusQuery = "
            SELECT 
                status, 
                COUNT(*) as count 
            FROM 
                admin_assignments 
            GROUP BY 
                status
        ";
        
        $statusStmt = $conn->prepare($statusQuery);
        $statusStmt->execute();
        $statusResults = $statusStmt->fetchAll(\PDO::FETCH_ASSOC);
        
        // Initialize counts
        $counts = [
            'total' => 0,
            'active' => 0,
            'completed' => 0,
            'cancelled' => 0,
            'overdue' => 0
        ];
        
        // Process status counts
        foreach ($statusResults as $result) {
            if (isset($counts[$result['status']])) {
                $counts[$result['status']] = (int)$result['count'];
            }
            $counts['total'] += (int)$result['count'];
        }
        
        // Get overdue assignments count
        $overdueQuery = "
            SELECT 
                COUNT(*) as count 
            FROM 
                admin_assignments 
            WHERE 
                status = 'active' 
                AND due_date < NOW()
        ";
        
        $overdueStmt = $conn->prepare($overdueQuery);
        $overdueStmt->execute();
        $overdueResult = $overdueStmt->fetch(\PDO::FETCH_ASSOC);
        
        $counts['overdue'] = (int)$overdueResult['count'];
        
        // Get counts by priority
        $priorityQuery = "
            SELECT 
                priority, 
                COUNT(*) as count 
            FROM 
                admin_assignments 
            GROUP BY 
                priority
        ";
        
        $priorityStmt = $conn->prepare($priorityQuery);
        $priorityStmt->execute();
        $priorityResults = $priorityStmt->fetchAll(\PDO::FETCH_ASSOC);
        
        // Initialize priority counts
        $priorityCounts = [
            'low' => 0,
            'normal' => 0,
            'high' => 0,
            'urgent' => 0
        ];
        
        // Process priority counts
        foreach ($priorityResults as $result) {
            if (isset($priorityCounts[$result['priority']])) {
                $priorityCounts[$result['priority']] = (int)$result['count'];
            }
        }
        
        // Get counts by type
        $typeQuery = "
            SELECT 
                assignment_type, 
                COUNT(*) as count 
            FROM 
                admin_assignments 
            GROUP BY 
                assignment_type
        ";
        
        $typeStmt = $conn->prepare($typeQuery);
        $typeStmt->execute();
        $typeResults = $typeStmt->fetchAll(\PDO::FETCH_ASSOC);
        
        // Initialize type counts
        $typeCounts = [
            'review' => 0,
            'approval' => 0,
            'verification' => 0,
            'follow_up' => 0,
            'other' => 0
        ];
        
        // Process type counts
        foreach ($typeResults as $result) {
            if (isset($typeCounts[$result['assignment_type']])) {
                $typeCounts[$result['assignment_type']] = (int)$result['count'];
            }
        }
        
        // Get completion trend for the last 7 days
        $trendQuery = "
            SELECT
                DATE(updated_at) as date,
                COUNT(*) as count
            FROM
                admin_assignments
            WHERE
                status = 'completed'
                AND updated_at >= DATE_SUB(CURDATE(), INTERVAL 6 DAY)
            GROUP BY
                DATE(updated_at)
            ORDER BY
                date ASC
        ";
        
        $trendStmt = $conn->prepare($trendQuery);
        $trendStmt->execute();
        $trendResults = $trendStmt->fetchAll(\PDO::FETCH_ASSOC);
        
        // Get last 7 days
        $dates = [];
        $today = new \DateTime();
        for ($i = 6; $i >= 0; $i--) {
            $date = clone $today;
            $date->modify("-$i day");
            $dates[] = $date->format('Y-m-d');
        }
        
        // Initialize trend data with zeros for all days
        $completedTrend = array_fill_keys($dates, 0);
        
        // Process trend data
        foreach ($trendResults as $result) {
            if (isset($completedTrend[$result['date']])) {
                $completedTrend[$result['date']] = (int)$result['count'];
            }
        }
        
        // Convert to array values only
        $completedTrend = array_values($completedTrend);
        
        // Get creation trend for the last 7 days
        $creationTrendQuery = "
            SELECT
                DATE(created_at) as date,
                COUNT(*) as count
            FROM
                admin_assignments
            WHERE
                created_at >= DATE_SUB(CURDATE(), INTERVAL 6 DAY)
            GROUP BY
                DATE(created_at)
            ORDER BY
                date ASC
        ";
        
        $creationTrendStmt = $conn->prepare($creationTrendQuery);
        $creationTrendStmt->execute();
        $creationTrendResults = $creationTrendStmt->fetchAll(\PDO::FETCH_ASSOC);
        
        // Initialize creation trend data with zeros for all days
        $createdTrend = array_fill_keys($dates, 0);
        
        // Process creation trend data
        foreach ($creationTrendResults as $result) {
            if (isset($createdTrend[$result['date']])) {
                $createdTrend[$result['date']] = (int)$result['count'];
            }
        }
        
        // Convert to array values only
        $createdTrend = array_values($createdTrend);
        
        // Get recent activity
        $activityQuery = "
            SELECT
                aa.id,
                aa.member_id,
                aa.title,
                aa.status,
                aa.priority,
                aa.created_at,
                aa.updated_at,
                m.first_name as member_first_name,
                m.last_name as member_last_name,
                m.kso_id as member_kso_id,
                CONCAT(a_by.first_name, ' ', a_by.last_name) as assigned_by_name,
                CONCAT(a_to.first_name, ' ', a_to.last_name) as assigned_to_name
            FROM
                admin_assignments aa
            JOIN
                members m ON aa.member_id = m.id
            JOIN
                admin_users a_by ON aa.assigned_by = a_by.id
            LEFT JOIN
                admin_users a_to ON aa.assigned_to = a_to.id
            ORDER BY
                aa.updated_at DESC
            LIMIT 10
        ";
        
        $activityStmt = $conn->prepare($activityQuery);
        $activityStmt->execute();
        $recentActivity = $activityStmt->fetchAll(\PDO::FETCH_ASSOC);
        
        // Calculate completion rate
        $completionRate = ($counts['total'] > 0) 
            ? round(($counts['completed'] / $counts['total']) * 100, 2) 
            : 0;
        
        // Calculate average completion time (in hours)
        $avgTimeQuery = "
            SELECT 
                AVG(TIMESTAMPDIFF(HOUR, created_at, updated_at)) as avg_hours 
            FROM 
                admin_assignments 
            WHERE 
                status = 'completed'
        ";
        $avgTimeStmt = $conn->prepare($avgTimeQuery);
        $avgTimeStmt->execute();
        $avgTimeResult = $avgTimeStmt->fetch(\PDO::FETCH_ASSOC);
        $avgHours = is_null($avgTimeResult['avg_hours']) ? 0 : round($avgTimeResult['avg_hours'], 1);
        
        // Calculate overdue rate
        $overdueRate = ($counts['active'] > 0) 
            ? round(($counts['overdue'] / $counts['active']) * 100, 2) 
            : 0;
        
        // Compile stats
        $basicStats = [
            'total' => $counts['total'],
            'active' => $counts['active'],
            'completed' => $counts['completed'],
            'completion_rate' => $completionRate,
            'avg_completion_hours' => $avgHours,
            'overdue' => $counts['overdue'],
            'overdue_rate' => $overdueRate
        ];
        
        // Prepare chart data
        $chartData = [
            'status' => [
                'active' => $counts['active'],
                'completed' => $counts['completed'],
                'cancelled' => $counts['cancelled'],
                'overdue' => $counts['overdue']
            ],
            'priority' => $priorityCounts,
            'type' => $typeCounts,
            'trend' => [
                'labels' => $dates,
                'completed' => $completedTrend,
                'created' => $createdTrend
            ]
        ];
        
        // Prepare response
        return [
            'success' => true,
            'data' => [
                'counts' => $counts,
                'charts' => $chartData,
                'recent_activity' => $recentActivity,
                'basic_stats' => $basicStats
            ],
            'message' => 'Assignment statistics retrieved successfully'
        ];
    } catch (\Exception $e) {
        // Log the error
        error_log('Assignment Statistics Error: ' . $e->getMessage());
        
        return [
            'success' => false,
            'message' => 'Error retrieving assignment statistics: ' . $e->getMessage()
        ];
    }
}

// Add this method to the AssignmentService class
AssignmentService::prototype('getAssignmentStats', getAssignmentStats);
