import UniversitiesPage from "../../components/organisms/UniversitiesPage";
import pool from "../../../lib/db/db";

async function getCountriesData() {
  try {
    const connection = await pool.getConnection();
    
    const [rows] = await connection.query(
      `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 AND ud.active = true
      LEFT JOIN courses nc ON ud.id = nc.university_id AND nc.active = true
      GROUP BY c.id, c.country, c.image
      HAVING university_count > 0 AND course_count > 0`
    );
    
    connection.release();
    return rows;
  } catch (error) {
    console.error('Error fetching countries:', error);
    return [];
  }
}

async function getSubjectsData() {
  try {
    const connection = await pool.getConnection();
    
    const [rows] = await connection.query(
      `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();
    return rows;
  } catch (error) {
    console.error('Error fetching subjects:', error);
    return [];
  }
}

export const metadata = {
  title: "Top Study Abroad Universities Consultant | Universities Page",
  description: "Find top global programs, admissions, and scholarships at Universities Page. Explore your dream career with expert guidance on study abroad opportunities.",
  siteName: process.env.NEXT_PUBLIC_APP_WEBSITE_NAME,
  type: "website",
  twitter: {
    card: "summary_large_image",
    title: "Top Study Abroad Universities Consultant | Universities Page",
    description: "Find top global programs, admissions, and scholarships at Universities Page. Explore your dream career with expert guidance on study abroad opportunities.",
  },
  openGraph: {
    url: `${process.env.NEXT_PUBLIC_APP_URL}/institutions`,
    title: "Top Study Abroad Universities Consultant | Universities Page",
    description: "Find top global programs, admissions, and scholarships at Universities Page. Explore your dream career with expert guidance on study abroad opportunities.",
    images: [
      {
        url: "/images/logo.png",
        width: 800,
        height: 600,
        alt: "Top Study Abroad Universities Consultant | Universities Page",
      },
    ],
    type: "website",
    locale: "en_US",
  },
  alternates: {
    canonical: `${process.env.NEXT_PUBLIC_APP_URL}/institutions`,
  },
  robots: {
    index: true,
    follow: true,
    nocache: false,
    googleBot: {
      index: true,
      follow: true,
      noimageindex: false,
      "max-video-preview": -1,
      "max-image-preview": "large",
      "max-snippet": -1,
    },
  },
};

export default async function UniversitiesPages() {
  const [countries, subjects] = await Promise.all([
    getCountriesData(),
    getSubjectsData()
  ]);

  return (
    <main className="min-h-screen bg-white">
      <UniversitiesPage 
        countries={countries} 
        subjects={subjects} 
      />
    </main>
  );
}