// app/api/frontend/consultant/consultantdashboard/stats/route.ts
import { NextRequest, NextResponse } from 'next/server';
import pool from '../../../../../../lib/db/db';
import { RowDataPacket } from "mysql2";

export async function GET(req: NextRequest) {
    try {
        // Get consultant_id from query parameters
        const url = new URL(req.url);
        const consultantId = url.searchParams.get('consultant_id');
        
        if (!consultantId) {
            return NextResponse.json(
                {
                    message: "Consultant ID is required",
                    success: false
                },
                {
                    status: 400
                }
            );
        }

        // Fetch total students count for this consultant
        const [studentRows] = await pool.query<RowDataPacket[]>(
            `SELECT COUNT(*) as total_students 
             FROM cstudents 
             WHERE consultant_id = ?`,
            [consultantId]
        );

        // Fetch active students (students with at least one stage completed)
        const [activeStudentRows] = await pool.query<RowDataPacket[]>(
            `SELECT COUNT(DISTINCT id) as active_students 
             FROM cstudents 
             WHERE consultant_id = ? 
             AND (
                 intial_documents_assessment = 'on' 
                 OR course_finalaztion = 'on' 
                 OR application_submitted = 'on' 
                 OR got_admission = 'on' 
                 OR visa_applied = 'on' 
                 OR case_closed = 'on'
             )`,
            [consultantId]
        );

        // Fetch notifications count for this consultant
        const [notificationRows] = await pool.query<RowDataPacket[]>(
            `SELECT COUNT(*) as total_notifications 
             FROM notifications 
             WHERE consultant_id = ? 
             AND is_read = 0`,
            [consultantId]
        );

        // Fetch success rate based on case_closed status
        const [successRateRows] = await pool.query<RowDataPacket[]>(
            `SELECT 
                COUNT(*) as total_cases,
                SUM(CASE WHEN case_closed = 'on' THEN 1 ELSE 0 END) as closed_cases
             FROM cstudents 
             WHERE consultant_id = ? 
             AND (
                 course_finalaztion = 'on' 
                 OR application_submitted = 'on'
             )`,
            [consultantId]
        );

        // Calculate success rate percentage
        const totalCases = successRateRows[0]?.total_cases || 0;
        const closedCases = successRateRows[0]?.closed_cases || 0;
        const successRate = totalCases > 0 
            ? Math.round((closedCases / totalCases) * 100) 
            : 0;

        // Fetch recent notifications for activity feed
        const [recentNotifications] = await pool.query<RowDataPacket[]>(
            `SELECT 
                id,
                type,
                name,
                message,
                is_read,
                created_at
             FROM notifications 
             WHERE consultant_id = ? 
             ORDER BY created_at DESC 
             LIMIT 5`,
            [consultantId]
        );

        // Prepare response data
        const stats = {
            total_students: studentRows[0]?.total_students || 0,
            active_students: activeStudentRows[0]?.active_students || 0,
            total_notifications: notificationRows[0]?.total_notifications || 0,
            success_rate: `${successRate}%`,
            unread_notifications: notificationRows[0]?.total_notifications || 0,
            recent_activity: recentNotifications.map(notification => ({
                id: notification.id,
                type: notification.type,
                title: notification.name,
                message: notification.message,
                is_read: notification.is_read === 1,
                time_ago: formatTimeAgo(notification.created_at),
                created_at: notification.created_at
            }))
        };

        return NextResponse.json(
            {
                message: "Statistics fetched successfully",
                success: true,
                data: stats
            },
            {
                status: 200,
                headers: {
                    'Cache-Control': 'no-cache, no-store, must-revalidate',
                },
            }
        );

    } catch (error) {
        console.error('Error fetching consultant stats:', error);
        return NextResponse.json(
            {
                message: "Something went wrong",
                success: false
            },
            {
                status: 500
            }
        );
    }
}

// Helper function to format time ago
function formatTimeAgo(dateString: string): string {
    if (!dateString) return 'Recently';
    
    const date = new Date(dateString);
    if (isNaN(date.getTime())) return 'Recently';
    
    const now = new Date();
    const diffInSeconds = Math.floor((now.getTime() - date.getTime()) / 1000);
    
    if (diffInSeconds < 60) return 'Just now';
    if (diffInSeconds < 3600) return `${Math.floor(diffInSeconds / 60)} minutes ago`;
    if (diffInSeconds < 86400) return `${Math.floor(diffInSeconds / 3600)} hours ago`;
    if (diffInSeconds < 604800) return `${Math.floor(diffInSeconds / 86400)} days ago`;
    
    return date.toLocaleDateString('en-US', { month: 'short', day: 'numeric' });
}