// app/api/frontend/foreign-students/route.js
import { NextResponse } from 'next/server';
import pool from "../../../../lib/db/db"

// GET endpoint for fetching students with pagination and filters
export async function GET(request) {
  const { searchParams } = new URL(request.url);
  
  try {
    const connection = await pool.getConnection();
    
    // Extract query parameters
    const page = parseInt(searchParams.get('page') || '1');
    const limit = parseInt(searchParams.get('limit') || '9');
    const offset = (page - 1) * limit;
    const search = searchParams.get('search') || '';
    const country = searchParams.get('country') || '';
    const education = searchParams.get('education') || '';
    const gender = searchParams.get('gender') || '';
    const minRate = searchParams.get('minRate') || '';
    const maxRate = searchParams.get('maxRate') || '';
    const is_verified = searchParams.get('is_verified') || '';

    // Build WHERE clause dynamically
    let whereClauses = ['fs.is_active = 1']; // Keep only active students
    let params = [];
    
    if (search) {
      whereClauses.push('(fs.name LIKE ? OR fs.about_me LIKE ?)');
      params.push(`%${search}%`, `%${search}%`);
    }
    
    if (country) {
      whereClauses.push('c.id = ?');
      params.push(country);
    }
    
    if (education) {
      whereClauses.push('fs.last_education = ?');
      params.push(education);
    }
    
    if (gender) {
      whereClauses.push('fs.gender = ?');
      params.push(gender);
    }
    
    if (minRate) {
      whereClauses.push('fs.hour_rate >= ?');
      params.push(minRate);
    }
    
    if (maxRate) {
      whereClauses.push('fs.hour_rate <= ?');
      params.push(maxRate);
    }
    
    // Add is_verified filter
    if (is_verified !== '' && is_verified !== 'all') {
      whereClauses.push('fs.is_verified = ?');
      params.push(is_verified === '1' ? 1 : 0);
    }
    
    const whereClause = whereClauses.length > 0 ? `WHERE ${whereClauses.join(' AND ')}` : '';
    
    // Count total students (for pagination)
    const countQuery = `
      SELECT COUNT(*) as total 
      FROM foreign_students fs
      LEFT JOIN countries_db c ON fs.country_id = c.id
      ${whereClause}
    `;
    
    const [countResult] = await connection.execute(countQuery, params);
    const totalCount = countResult[0]?.total || 0;
    const totalPages = Math.ceil(totalCount / limit);
    
    // Fetch students with pagination
    const studentsQuery = `
      SELECT 
        fs.id,
        fs.name,
        fs.user_slug,
        fs.country_id,
        fs.timezone,
        fs.about_me,
        fs.profile_picture,
        fs.hour_rate,
        fs.gender,
        fs.experience,
        fs.last_education,
        fs.email_verified,
        fs.is_verified,
        fs.is_active,
        fs.created_at,
        fs.updated_at,
        c.name as country_name,
        c.id as country_id,
        c.phonecode as country_code
      FROM foreign_students fs
      LEFT JOIN countries_db c ON fs.country_id = c.id
      ${whereClause}
      ORDER BY fs.created_at DESC
      LIMIT ? OFFSET ?
    `;
    
    const studentParams = [...params, limit, offset];
    const [students] = await connection.execute(studentsQuery, studentParams);
    
    // Get distinct countries that have active foreign students
    const countriesQuery = `
      SELECT DISTINCT 
        c.id,
        c.name,
        COUNT(fs.id) as student_count
      FROM countries_db c
      INNER JOIN foreign_students fs ON c.id = fs.country_id
      WHERE fs.is_active = 1
      GROUP BY c.id, c.name
      ORDER BY c.name ASC
    `;
    
    const [countries] = await connection.execute(countriesQuery);
    
    connection.release();
    
    return NextResponse.json({
      success: true,
      students,
      countries,
      pagination: {
        currentPage: page,
        totalPages,
        totalCount,
        limit,
        offset
      },
      filters: {
        search,
        country,
        education,
        gender,
        minRate,
        maxRate,
        is_verified
      }
    });
    
  } catch (error) {
    console.error('Error fetching students:', error);
    return NextResponse.json(
      { 
        success: false, 
        error: 'Failed to fetch students',
        details: error.message 
      },
      { status: 500 }
    );
  }
}