// app/api/menu-items/route.js
import { NextResponse } from 'next/server';
import pool from '../../../../lib/db/db'; // Adjust path based on your db connection file

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

  try {
    // Fetch universities
    const [universities] = await connection.query(`
      SELECT 
        name as title,
        slug
      FROM university_details 
      WHERE show_in_menu = 1 
        AND display = 1 
        AND is_active = 1
      ORDER BY name ASC
    `);

    // Fetch courses
    const [courses] = await connection.query(`
      SELECT 
        c.name as title,
        c.slug,
        p.title as qualification,
        ud.slug as university_slug
      FROM courses c
      INNER JOIN posts p ON c.qualification = p.id
      INNER JOIN university_details ud ON c.university_id = ud.id
      WHERE c.show_in_menu = 1 
        AND c.active = 1 
        AND c.display = 1
      ORDER BY c.name ASC
    `);

    // Fetch scholarships
    const [scholarships] = await connection.query(`
      SELECT 
        s.title,
        s.slug,
        s.ielts
      FROM scholarships s
      WHERE s.show_in_menu = 1 
        AND s.is_active = 1
      ORDER BY s.title ASC
    `);

    // Fetch countries
    const [countries] = await connection.query(`
      SELECT 
        country as title,
        LOWER(code) as slug
      FROM countries 
      WHERE show_in_menu = 1
      ORDER BY country ASC
    `);

    // Fetch qualifications
    const [qualifications] = await connection.query(`
      SELECT
        id,
        title,
        slug,
        display_order
      FROM posts
      WHERE is_active = 1 
      ORDER BY display_order ASC
    `);

    const data = {
      success: true,
      data: {
        universities,
        courses,
        scholarships,
        countries,
        qualifications
      }
    };

    // Return response with cache headers
    return NextResponse.json(data, {
      status: 200,
      headers: {
        'Cache-Control': 'public, s-maxage=3600, stale-while-revalidate=7200',
      },
    });

  } catch (error) {
    console.error('Error fetching menu items:', error);
    
    return NextResponse.json({
      success: false,
      error: error.message,
      data: {
        universities: [],
        courses: [],
        scholarships: [],
        countries: [],
        qualifications: []
      }
    }, { 
      status: 500,
      headers: {
        'Cache-Control': 'no-store',
      },
    });

  } finally {
    connection.release();
  }
}