import { NextRequest, NextResponse } from 'next/server';
import pool from '../../../../../lib/db/db';
import jwt from 'jsonwebtoken';

export async function GET(request: NextRequest) {
    const connection = await pool.getConnection();
  try {
    

    const token = request.cookies.get('university-token')?.value;
        
    if (!token) {
        connection.release();
        return NextResponse.json(
        { 
            success: false, 
            message: 'Authentication required' 
        },
        { status: 401 }
        );
    }

    // Verify token
    let decoded;
    try {
        decoded = jwt.verify(token, process.env.JWT_SECRET as string);
    } catch (err) {
        connection.release();
        return NextResponse.json(
        { 
            success: false, 
            message: 'Invalid or expired token' 
        },
        { status: 401 }
        );
    }

    // Check if user is a foreign student
    if (decoded.role !== 'foreign-student') {
        connection.release();
        return NextResponse.json(
        { 
            success: false, 
            message: 'Access denied' 
        },
        { status: 403 }
        );
    }

    // Query to get appointment statistics
    const [statsRows] = await pool.execute(
      `SELECT 
        COUNT(*) as total_appointments,
        SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending_appointments,
        SUM(CASE WHEN status = 'confirmed' THEN 1 ELSE 0 END) as confirmed_appointments,
        SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) as cancelled_appointments,
        SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completed_appointments,
        SUM(CASE WHEN status = 'no_show' THEN 1 ELSE 0 END) as no_show_appointments,
        COALESCE(SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END), 0) as total_earnings,
        COALESCE(SUM(CASE WHEN status IN ('confirmed', 'pending') THEN amount ELSE 0 END), 0) as reserved_earnings,
        AVG(CASE WHEN status = 'completed' THEN amount ELSE NULL END) as avg_session_amount,
        COUNT(DISTINCT student_id) as total_students
       FROM foreign_student_booked_sessions
       WHERE foreign_student_id = ?`,
      [decoded.id]
    );

    const stats = (statsRows as any[])[0];

    const [nextAppointmentRows] = await pool.execute(
        `SELECT 
            bs.id,
            bs.foreign_student_id,
            bs.student_id,
            DATE_FORMAT(bs.session_date, '%Y-%m-%d') as session_date,
            bs.start_time,
            bs.end_time,
            bs.amount,
            bs.status,
            bs.meeting_link,
            bs.notes,
            bs.created_at,
            bs.updated_at,
            bs.student_feedback,
            u.first_name as student_name,
            u.email as student_email
        FROM foreign_student_booked_sessions bs
        LEFT JOIN users u ON bs.student_id = u.id
        WHERE bs.foreign_student_id = ? 
            AND bs.status IN ('pending', 'confirmed')
            AND bs.session_date >= CURDATE()
        ORDER BY bs.session_date ASC, bs.start_time ASC
        LIMIT 1`,
        [decoded.id]
        );

    const nextAppointment = (nextAppointmentRows as any[])[0] || null;

    return NextResponse.json({
      success: true,
      data: {
        // Appointment statistics
        totalAppointments: Number(stats.total_appointments) || 0,
        pendingAppointments: Number(stats.pending_appointments) || 0,
        confirmedAppointments: Number(stats.confirmed_appointments) || 0,
        cancelledAppointments: Number(stats.cancelled_appointments) || 0,
        completedAppointments: Number(stats.completed_appointments) || 0,
        noShowAppointments: Number(stats.no_show_appointments) || 0,
        
        // Earnings statistics
        totalEarnings: Number(stats.total_earnings) || 0,
        reservedEarnings: Number(stats.reserved_earnings) || 0,
        avgSessionAmount: Number(stats.avg_session_amount) || 0,
        
        // Additional stats
        totalStudents: Number(stats.total_students) || 0,
        
        // Next appointment
        nextAppointment: nextAppointment ? {
          id: nextAppointment.id,
          date: nextAppointment.session_date,
          startTime: nextAppointment.start_time,
          endTime: nextAppointment.end_time,
          status: nextAppointment.status,
          amount: nextAppointment.amount,
          meetingLink: nextAppointment.meeting_link,
          notes: nextAppointment.notes,
          studentName: nextAppointment.student_name,
          studentEmail: nextAppointment.student_email
        } : null,
        
        // Response time (mock data - you can implement real calculation)
        averageResponseTime: '2.4 hours',
        nextPayout: Number(stats.reserved_earnings) || 0
      }
    });

  } catch (error) {
    console.error('Dashboard stats error:', error);
    return NextResponse.json(
      { error: 'Failed to fetch dashboard statistics', message: error },
      { status: 500 }
    );
  }
}