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();

  try {
    const [rows] = await connection.query<RowDataPacket[]>(
      `SELECT 
        c.id, 
        c.country, 
        c.image,
        COUNT(DISTINCT ud.id) AS university_count,
        COUNT(DISTINCT nc.id) AS course_count
      FROM countries c
      LEFT JOIN university_details ud ON c.country = ud.country
      LEFT JOIN courses nc ON ud.id = nc.university_id
      GROUP BY c.id, c.country, c.image
      HAVING university_count > 0 AND course_count > 0`
    );

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

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