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

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

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

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

  try {
    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`
    );

    if(rows.length > 0) {
      return NextResponse.json(
        {
          success: true,
          data: rows
        },
        { status: 200 }
      );
    } else {
      return NextResponse.json(
        {
          success: false,
          message: "No subjects found with courses",
          data: []
        },
        { status: 404 }
      );
    }

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