// app/api/internal/foreign-student/foreign-student-appointments/route.js
import pool from '../../../../../lib/db/db';
import { NextRequest, NextResponse } from 'next/server';
import { RowDataPacket } from 'mysql2';
import jwt from 'jsonwebtoken';

export async function GET(req: NextRequest) {
  const connection = await pool.getConnection();
  
  try {
    // Get token from cookie
    const token = req.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 }
      );
    }

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

    const foreignStudentId = decoded.id;
    
    // Get query parameters
    const { searchParams } = new URL(req.url);
    const page = parseInt(searchParams.get('page') || '1');
    const limit = parseInt(searchParams.get('limit') || '10');
    const status = searchParams.get('status');
    
    const offset = (page - 1) * limit;

    // Build query conditions
    let whereConditions = ['fsbs.foreign_student_id = ?'];
    let queryParams = [foreignStudentId];
    
    if (status && status !== 'all' && status !== '') {
      whereConditions.push('fsbs.status = ?');
      queryParams.push(status);
    }

    const whereClause = whereConditions.length > 0 ? `WHERE ${whereConditions.join(' AND ')}` : '';

    // Get total count
    const [countResult] = await connection.query<RowDataPacket[]>(
      `SELECT COUNT(*) as total 
       FROM foreign_student_booked_sessions fsbs
       ${whereClause}`,
      queryParams
    );

    const total = countResult[0].total;
    const totalPages = Math.ceil(total / limit);

    // Fetch foreign student appointments with student details
    const [appointments] = await connection.query<RowDataPacket[]>(
      `SELECT 
        fsbs.id,
        fsbs.session_date,
        fsbs.start_time,
        fsbs.end_time,
        fsbs.amount,
        fsbs.status,
        fsbs.meeting_link,
        fsbs.notes,
        fsbs.created_at,
        fsbs.updated_at,
        s.id as student_id,
        s.first_name as student_name,
        s.email as student_email,
        fsbs.student_feedback as student_feedback
       FROM foreign_student_booked_sessions fsbs
       INNER JOIN users s ON fsbs.student_id = s.id
       ${whereClause}
       ORDER BY fsbs.session_date DESC, fsbs.start_time DESC
       LIMIT ? OFFSET ?`,
      [...queryParams, limit, offset]
    );

    connection.release();

    return NextResponse.json({
      success: true,
      message: 'Appointments fetched successfully',
      appointments: appointments.map(app => ({
        id: app.id,
        session_date: app.session_date,
        start_time: app.start_time.substring(0, 5),
        end_time: app.end_time.substring(0, 5),
        amount: app.amount,
        status: app.status,
        meeting_link: app.meeting_link,
        notes: app.notes,
        student_feedback: app.student_feedback,
        created_at: app.created_at,
        updated_at: app.updated_at,
        student_name: app.student_name,
        student_email: app.student_email,
        student_profile_picture: app.student_profile_picture
      })),
      total,
      totalPages,
      currentPage: page,
      limit
    }, { status: 200 });

  } catch (err: any) {
    if (connection) connection.release();
    console.error('Get foreign student appointments error:', err);
    return NextResponse.json(
      { success: false, message: 'Failed to fetch appointments' },
      { status: 500 }
    );
  }
}