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

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

    const comingHeaders = req.headers.get("authorization");

    if(comingHeaders !== process.env.APP_API_KEY) {
        return NextResponse.json(
            { success: false, message: 'Unauthorized Access' },
            { status: 401 }
        );
    }

    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: true,
                data: []
            });
        }

        // Fetch qualifications correctly
        const scholarshipsWithQualifications = await Promise.all(
            scholarships.map(async (scholarship: any) => {
                // Make sure scholarship is a proper object, not nested
                const cleanScholarship = { ...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`,
                    [cleanScholarship.id]
                );
                
                return {
                    ...cleanScholarship,
                    qualifications: qualifications || []
                };
            })
        );

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

    } catch (error) {
        console.error('Database error fetching popular scholarships:', error);
        return NextResponse.json({
            success: false,
            message: error instanceof Error ? error.message : 'Unknown error occurred',
            data: []
        }, { status: 500 });
    } finally {
        connection.release();
    }
}