<?php
/**
 * KSO Chandigarh Admin Panel
 * Assignment Statistics API Endpoint
 * 
 * This endpoint provides detailed statistics about assignments for the dashboard
 * including status counts, priority distribution, assignment types, and trends
 */

// Include dependencies
require_once '../../config/database.php';
require_once '../../config/auth_enhanced.php';
require_once '../../services/assignment/AssignmentService.php';

// Check authentication
checkAuthentication();

// Initialize response
$response = [
    'success' => false,
    'message' => '',
    'data' => []
];

// Get database connection
$db = new Database();
$conn = $db->getConnection();

try {
    // Create service instance
    $assignmentService = new AssignmentService($conn);
    
    // Get basic statistics
    $basicStats = $assignmentService->getStatistics();
    
    // Get 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) {
        $counts[$result['status']] = (int)$result['count'];
        $counts['total'] += (int)$result['count'];
    }
    
    // Get overdue assignments count (active assignments with due date in the past)
    $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) {
        $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) {
        $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);
    
    // Initialize trend data with zeros for all days
    $completedTrend = array_fill(0, 7, 0);
    
    // Process trend data
    foreach ($trendResults as $result) {
        $date = new DateTime($result['date']);
        $today = new DateTime();
        $diff = $today->diff($date)->days;
        
        if ($diff <= 6) {
            $index = 6 - $diff;
            $completedTrend[$index] = (int)$result['count'];
        }
    }
    
    // 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(0, 7, 0);
    
    // Process creation trend data
    foreach ($creationTrendResults as $result) {
        $date = new DateTime($result['date']);
        $today = new DateTime();
        $diff = $today->diff($date)->days;
        
        if ($diff <= 6) {
            $index = 6 - $diff;
            $createdTrend[$index] = (int)$result['count'];
        }
    }
    
    // Get recent activity
    $activityQuery = "
        SELECT
            ae.id,
            ae.assignment_id,
            ae.action,
            ae.details,
            ae.timestamp,
            aa.title as assignment_title,
            CONCAT(m.first_name, ' ', m.last_name) as member_name,
            CONCAT(a.first_name, ' ', a.last_name) as admin_name,
            CONCAT(assigned.first_name, ' ', assigned.last_name) as assigned_to_name
        FROM
            assignment_events ae
        JOIN
            admin_assignments aa ON ae.assignment_id = aa.id
        JOIN
            members m ON aa.member_id = m.id
        JOIN
            admins a ON ae.admin_id = a.id
        LEFT JOIN
            admins assigned ON aa.assigned_to = assigned.id
        ORDER BY
            ae.timestamp DESC
        LIMIT 10
    ";
    
    $activityStmt = $conn->prepare($activityQuery);
    $activityStmt->execute();
    $recentActivity = $activityStmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Prepare chart data
    $chartData = [
        'status' => [
            'active' => $counts['active'],
            'completed' => $counts['completed'],
            'cancelled' => $counts['cancelled'],
            'overdue' => $counts['overdue']
        ],
        'priority' => $priorityCounts,
        'type' => $typeCounts,
        'trend' => [
            'completed' => $completedTrend,
            'created' => $createdTrend
        ]
    ];
    
    // Prepare response
    $response['success'] = true;
    $response['data'] = [
        'counts' => $counts,
        'charts' => $chartData,
        'recent_activity' => $recentActivity,
        'basic_stats' => $basicStats
    ];
} catch (Exception $e) {
    $response['message'] = "Error: " . $e->getMessage();
    // Log the error
    error_log('Assignment Statistics Error: ' . $e->getMessage());
} finally {
    // Close connection
    $conn = null;
}

// Return JSON response
header('Content-Type: application/json');
echo json_encode($response);
