import { NextRequest, NextResponse } from 'next/server';
import { RowDataPacket } from 'mysql2';
import pool from "../../../../lib/db/db";

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

  try {
    // First get scholarships with basic info
    const [scholarships] = await connection.query<RowDataPacket[]>(
      `
        SELECT DISTINCT
          s.id,
          s.slug,
          s.title,
          s.image,
          s.amount_min,
          s.amount_max,
          s.cgpa,
          s.description,
          s.created_at,
          c.country AS country_name,
          c.currency_symbol as currency_symbol
        FROM scholarships s
        LEFT JOIN countries c ON s.country_id = c.id
        LEFT JOIN scholarship_qualifications sq ON s.id = sq.scholarship_id
        WHERE s.is_active = 1 
        ORDER BY s.created_at DESC
        LIMIT 12
      `
    );

    if (scholarships.length === 0) {
      return NextResponse.json(
        {
          success: false,
          message: "No Scholarships Found",
          data: []
        },
        { status: 404 }
      );
    }

    // Fetch qualifications for each scholarship
    const scholarshipsWithQualifications = await Promise.all(
      scholarships.map(async (scholarship) => {
        const [qualifications] = await connection.query<RowDataPacket[]>(
          `SELECT 
            sq.qualification_id,
            p.title as qualification_name
           FROM scholarship_qualifications sq
           LEFT JOIN posts p ON sq.qualification_id = p.id
           WHERE sq.scholarship_id = ?
           ORDER BY p.title`,
          [scholarship.id]
        );
        
        return {
          ...scholarship,
          qualifications: qualifications || []
        };
      })
    );

    return NextResponse.json(
      {
        success: true,
        data: scholarshipsWithQualifications
      },
      { status: 200 }
    );

  } catch (err: any) {
    console.log('Scholarships API Error:', err);
    return NextResponse.json(
      {
        success: false,
        message: err.message,
      },
      { status: 500 }
    );
  } finally {
    connection.release();
  }
}