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 {

        // Run all queries in parallel
        const [
            [levels],
            [universities],
            [subjects]
        ] = await Promise.all([
            connection.query<RowDataPacket[]>(`
                SELECT id, title, image 
                FROM posts 
                WHERE is_active = 1
                ORDER BY created_at DESC
            `),

            connection.query<RowDataPacket[]>(`
                SELECT id, name, logo, slug, country
                FROM university_details 
                WHERE popular = 1 
                ORDER BY created_at DESC
                LIMIT 10
            `),

            connection.query<RowDataPacket[]>(`
                SELECT 
                    s.id, 
                    s.name, 
                    s.icon,
                    COUNT(DISTINCT c.id) AS course_count,
                    COUNT(DISTINCT ud.id) AS university_count
                FROM subjects s
                LEFT JOIN courses c ON s.id = c.subject_id
                LEFT JOIN university_details ud ON c.university_id = ud.id
                GROUP BY s.id, s.name, s.icon
                HAVING course_count > 0 AND university_count > 0
                ORDER BY course_count DESC
                LIMIT 12
            `)
        ]);

        // Check if data exists
        if (
            (levels as RowDataPacket[]).length === 0 &&
            (universities as RowDataPacket[]).length === 0 &&
            (subjects as RowDataPacket[]).length === 0
        ) {
            return NextResponse.json({
                success: false,
                message: 'No browse data found',
                data: {
                    levels: [],
                    universities: [],
                    subjects: []
                }
            }, { status: 404 });
        }

        return NextResponse.json({
            success: true,
            message: 'Browse courses data fetched successfully',
            data: {
                levels,
                universities,
                subjects
            }
        }, {
            status: 200,
            headers: {
                'Cache-Control': 'public, max-age=3600',
            },
        });

    } catch (error: any) {
        console.error('Database error fetching browse courses data:', error);

        return NextResponse.json({
            success: false,
            message: error.message || 'Internal server error',
            data: {
                levels: [],
                universities: [],
                subjects: []
            }
        }, { status: 500 });

    } finally {
        connection.release();
    }
}