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

export async function GET(req: NextRequest) {
    // Check authorization header
    const incomingHeaders = req.headers.get("authorization");

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

    try {
        const connection = await pool.getConnection();
        
        const [rows] = await 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`
        );
        
        connection.release();

        if (rows.length === 0) {
            return NextResponse.json(
                {
                    success: false,
                    message: "No subjects found"
                },
                {
                    status: 404
                }
            );
        }
        
        return NextResponse.json(
            {
                success: true,
                message: "Subjects found",
                data: rows
            },
            {
                status: 200,
                headers: {
                    'Cache-Control': 'public, max-age=3600',
                },
            }
        );
    } catch (error) {
        console.error('Error fetching subjects:', error);
        return NextResponse.json(
            {
                success: false,
                message: 'Internal server error'
            },
            {
                status: 500
            }
        );
    }
}