// app/api/appapis/foreign_student_dashboard_stats/route.js
import { NextRequest, NextResponse } from 'next/server';
import pool from '../../../../lib/db/db';
import { verifyAuthentication } from '../../../utils/verifyAuthentication';
import { RowDataPacket } from 'mysql2';

interface TokenInterface {
  id: number;
  userId?: number;
  email?: string;
  role?: string;
}

export async function OPTIONS() {
  return NextResponse.json({}, { status: 200 });
}

export async function GET(request: NextRequest) {
  const connection = await pool.getConnection();
  
  try {
    // Get token from Authorization header
    const authHeader = request.headers.get('authorization');
    
    if (!authHeader || !authHeader.startsWith('Bearer ')) {
      return NextResponse.json({
        success: false,
        message: "No valid authorization header found"
      }, { status: 401 });
    }

    const token = authHeader.substring(7);
    
    // Verify token
    const authResult = await verifyAuthentication(token);
    
    if (authResult instanceof NextResponse) {
      return authResult;
    }
    
    const decoded = authResult as TokenInterface;
    
    if (!decoded || !decoded.id) {
      return NextResponse.json({
        success: false,
        message: "Invalid token - no user ID found"
      }, { status: 401 });
    }

    // Check if user is a foreign student
    if (decoded.role !== "foreign-student") {
      return NextResponse.json({
        success: false,
        message: "You are not authorized to perform this action"
      }, { status: 401 });
    }

    // Get foreign student profile information from foreign_students table
    const [userInfo] = await connection.query<RowDataPacket[]>(
      `SELECT 
        fs.id,
        fs.name,
        fs.email,
        fs.phone,
        fs.profile_picture,
        fs.country_id,
        fs.timezone,
        fs.about_me,
        fs.hour_rate,
        fs.gender,
        fs.experience,
        fs.last_education,
        fs.is_active,
        fs.is_verified,
        fs.created_at
      FROM foreign_students fs
      WHERE fs.id = ? AND fs.is_active = 1`,
      [decoded.id]
    );

    // Get meeting statistics from foreign_student_booked_sessions
    const [pendingMeetings] = await connection.query<RowDataPacket[]>(
      `SELECT COUNT(*) as count 
       FROM foreign_student_booked_sessions 
       WHERE foreign_student_id = ? 
       AND status = 'pending'`,
      [decoded.id]
    );

    const [confirmedMeetings] = await connection.query<RowDataPacket[]>(
      `SELECT COUNT(*) as count 
       FROM foreign_student_booked_sessions 
       WHERE foreign_student_id = ? 
       AND status = 'confirmed'`,
      [decoded.id]
    );

    const [completedMeetings] = await connection.query<RowDataPacket[]>(
      `SELECT COUNT(*) as count 
       FROM foreign_student_booked_sessions 
       WHERE foreign_student_id = ? 
       AND status = 'completed'`,
      [decoded.id]
    );

    const [cancelledMeetings] = await connection.query<RowDataPacket[]>(
      `SELECT COUNT(*) as count 
       FROM foreign_student_booked_sessions 
       WHERE foreign_student_id = ? 
       AND status = 'cancelled'`,
      [decoded.id]
    );

    // Get total sessions count
    const [totalSessions] = await connection.query<RowDataPacket[]>(
      `SELECT COUNT(*) as count 
       FROM foreign_student_booked_sessions 
       WHERE foreign_student_id = ?`,
      [decoded.id]
    );

    // Get upcoming sessions (next 30 days with status pending or confirmed)
    const [upcomingSessions] = await connection.query<RowDataPacket[]>(
      `SELECT 
        fsbs.*,
        u.id as student_user_id,
        u.first_name,
        u.last_name,
        u.email,
        u.image as student_image
       FROM foreign_student_booked_sessions fsbs
       LEFT JOIN users u ON fsbs.student_id = u.id
       WHERE fsbs.foreign_student_id = ? 
       AND fsbs.session_date >= CURDATE()
       AND fsbs.status IN ('pending', 'confirmed')
       ORDER BY fsbs.session_date ASC, fsbs.start_time ASC
       LIMIT 10`,
      [decoded.id]
    );

    // Get recent sessions (last 5 sessions)
    const [recentSessions] = await connection.query<RowDataPacket[]>(
      `SELECT 
        fsbs.*,
        u.id as student_user_id,
        u.first_name,
        u.last_name,
        u.email,
        u.image as student_image
       FROM foreign_student_booked_sessions fsbs
       LEFT JOIN users u ON fsbs.student_id = u.id
       WHERE fsbs.foreign_student_id = ?
       ORDER BY fsbs.session_date DESC, fsbs.created_at DESC
       LIMIT 5`,
      [decoded.id]
    );

    // Calculate total earnings from completed sessions
    const [totalEarnings] = await connection.query<RowDataPacket[]>(
      `SELECT COALESCE(SUM(amount), 0) as total 
       FROM foreign_student_booked_sessions 
       WHERE foreign_student_id = ? 
       AND status = 'completed'`,
      [decoded.id]
    );

    // Get country name if country_id exists
    let countryName = null;
    if (userInfo[0]?.country_id) {
      const [country] = await connection.query<RowDataPacket[]>(
        `SELECT name FROM countries_db WHERE id = ?`,
        [userInfo[0].country_id]
      );
      countryName = country[0]?.name || null;
    }

    // Format profile data
    const profile = userInfo.length > 0 ? {
      id: userInfo[0].id,
      name: userInfo[0].name || '',
      email: userInfo[0].email || '',
      phone: userInfo[0].phone || '',
      profile_picture: userInfo[0].profile_picture || null,
      country_id: userInfo[0].country_id,
      country_name: countryName,
      timezone: userInfo[0].timezone || '',
      about_me: userInfo[0].about_me || '',
      hour_rate: userInfo[0].hour_rate || 0,
      gender: userInfo[0].gender || '',
      experience: userInfo[0].experience || '',
      last_education: userInfo[0].last_education || '',
      is_verified: userInfo[0].is_verified || 0,
      joined_date: userInfo[0].created_at,
    } : null;

    return NextResponse.json({
      success: true,
      data: {
        profile: profile,
        meeting_stats: {
          pending: pendingMeetings[0]?.count || 0,
          confirmed: confirmedMeetings[0]?.count || 0,
          completed: completedMeetings[0]?.count || 0,
          cancelled: cancelledMeetings[0]?.count || 0,
          total: totalSessions[0]?.count || 0,
          total_earnings: parseFloat(totalEarnings[0]?.total) || 0,
        },
        upcoming_sessions: upcomingSessions || [],
        recent_sessions: recentSessions || [],
      }
    }, { status: 200 });

  } catch (error) {
    console.error('Foreign student dashboard stats error:', error);
    return NextResponse.json({
      success: false,
      message: "Internal server error"
    }, { status: 500 });
  } finally {
    connection.release();
  }
}