// app/api/internal/applications/route.js
import { NextRequest, NextResponse } from 'next/server';
import pool from '../../../../lib/db/db';

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

  try {
    const { searchParams } = new URL(request.url);
    const page = parseInt(searchParams.get('page')) || 1;
    const limit = parseInt(searchParams.get('limit')) || 15;
    const search = searchParams.get('search') || '';
    const type = searchParams.get('type') || '';
    const paymentStatus = searchParams.get('payment_status') || '';
    const startDate = searchParams.get('start_date') || '';
    const endDate = searchParams.get('end_date') || '';
    const studentId = searchParams.get('student_id') || '';

    const offset = (page - 1) * limit;

    // Build WHERE conditions
    let whereConditions = ['1=1'];
    let queryParams = [];

    if (studentId) {
      whereConditions.push('sa.user_id = ?');
      queryParams.push(studentId);
    }

    if (search) {
      whereConditions.push('(sa.item_name LIKE ? )');
      queryParams.push(`%${search}%`);
    }

    if (type && type !== 'all') {
      whereConditions.push('sa.type = ?');
      queryParams.push(type);
    }

    if (paymentStatus && paymentStatus !== 'all') {
      whereConditions.push('sa.payment_status = ?');
      queryParams.push(paymentStatus);
    }

    if (startDate) {
      whereConditions.push('DATE(sa.created_at) >= ?');
      queryParams.push(startDate);
    }

    if (endDate) {
      whereConditions.push('DATE(sa.created_at) <= ?');
      queryParams.push(endDate);
    }

    const whereClause = whereConditions.join(' AND ');

    // Count query for pagination
    const [countResult] = await connection.query(
      `SELECT COUNT(*) as total
       FROM search_apply_now sa
       LEFT JOIN university_details u ON sa.type = 'university' AND sa.apply_id = u.id
       WHERE ${whereClause}`,
      queryParams
    );

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

    // Main data query
    const [applications] = await connection.query(
      `SELECT 
        sa.*,
        u.slug as university_slug,
        u.name as university_name
       FROM search_apply_now sa
       LEFT JOIN university_details u ON sa.type = 'university' AND sa.apply_id = u.id
       WHERE ${whereClause}
       ORDER BY sa.created_at DESC
       LIMIT ? OFFSET ?`,
      [...queryParams, limit, offset]
    );

    return NextResponse.json({
      success: true,
      applications,
      meta: {
        totalItems,
        totalPages,
        currentPage: page,
        itemsPerPage: limit
      }
    });

  } catch (error) {
    console.error('Error fetching applications:', error);
    return NextResponse.json(
      { success: false, error: 'Internal server error' },
      { status: 500 }
    );
  } finally {
    connection.release();
  }
}