import { NextResponse } from 'next/server';
import pool from '../../../../lib/db/db';

export async function GET() {
  let connection;

  try {
    connection = await pool.getConnection();

    // Get countries that have videos associated with them
    const [countries] = await connection.execute(`
      SELECT DISTINCT 
        c.id, 
        c.country, 
        c.code,
        COUNT(yv.id) as video_count
      FROM countries c
      LEFT JOIN youtube_videos yv ON c.id = yv.country_id
      WHERE yv.id IS NOT NULL AND yv.is_active = 1
      GROUP BY c.id, c.country, c.code
      ORDER BY c.country ASC
    `);

    return NextResponse.json({
      success: true,
      data: countries
    });

  } catch (error) {
    console.error("Error fetching countries for videos:", error);
    return NextResponse.json(
      { success: false, error: error.message },
      { status: 500 }
    );
  } finally {
    if (connection) {
      connection.release();
    }
  }
}