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

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

    // Fetch all active URLs
    const [urls] = await connection.query(
      `SELECT url_path, priority, last_modified 
       FROM sitemap_urls 
       WHERE is_active = 1 
       ORDER BY priority DESC`
    );

    const baseUrl = process.env.NEXT_PUBLIC_APP_URL || 'https://universitiespage.com';
    const timestamp = new Date().toISOString().split('T')[0];

    // Generate sitemap XML with main website URL always included
    const sitemap = `<?xml version="1.0" encoding="UTF-8"?>
<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">
  <url>
    <loc>${baseUrl}</loc>
    <lastmod>${timestamp}</lastmod>
    <priority>1.0</priority>
    <changefreq>daily</changefreq>
  </url>
  
  ${urls.map(url => {
    // Skip if it's the home URL (already included above)
    if (url.url_path === '/') return '';
    
    const lastmod = url.last_modified 
      ? new Date(url.last_modified).toISOString().split('T')[0]
      : timestamp;
    
    return `
  <url>
    <loc>${baseUrl}${url.url_path}</loc>
    <lastmod>${lastmod}</lastmod>
    <priority>${url.priority}</priority>
  </url>`;
  }).join('')}
</urlset>`;

    return new NextResponse(sitemap.trim(), {
      headers: {
        'Content-Type': 'application/xml',
        'Cache-Control': 'public, max-age=3600, s-maxage=3600'
      }
    });

  } catch (error) {
    console.error("Error generating dynamic sitemap:", error);
    
    // Fallback sitemap with just the main URL
    const baseUrl = process.env.NEXT_PUBLIC_APP_URL || 'https://universitiespage.com';
    const timestamp = new Date().toISOString().split('T')[0];
    
    const fallbackSitemap = `<?xml version="1.0" encoding="UTF-8"?>
<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">
  <url>
    <loc>${baseUrl}</loc>
    <lastmod>${timestamp}</lastmod>
    <priority>1.0</priority>
    <changefreq>daily</changefreq>
  </url>
</urlset>`;

    return new NextResponse(fallbackSitemap, {
      status: 200,
      headers: {
        'Content-Type': 'application/xml'
      }
    });
  } finally {
    if (connection) {
      connection.release();
    }
  }
}