<?php
/**
 * Dynamic Sitemap Generator for City-Service Pages
 * Run this file to generate sitemap.xml for better SEO indexing
 */

require_once 'fingguwebsite/db.php';
include 'includes/url-helper.php';

// Set the base URL
$baseUrl = 'https://www.finggu.com'; // Production domain

// Start XML
$xml = '<?xml version="1.0" encoding="UTF-8"?>' . "\n";
$xml .= '<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">' . "\n";

// Add main pages with last modified time
$mainPages = [
    '' => ['priority' => '1.0', 'changefreq' => 'daily', 'lastmod' => date('Y-m-d', filemtime('index.php'))],
    'services.php' => ['priority' => '0.9', 'changefreq' => 'weekly', 'lastmod' => date('Y-m-d', filemtime('services.php'))],
    'about.php' => ['priority' => '0.8', 'changefreq' => 'monthly', 'lastmod' => date('Y-m-d', filemtime('about.php'))],
    'contact.php' => ['priority' => '0.8', 'changefreq' => 'monthly', 'lastmod' => date('Y-m-d', filemtime('contact.php'))],
    'portfolio.php' => ['priority' => '0.7', 'changefreq' => 'weekly', 'lastmod' => date('Y-m-d', filemtime('portfolio.php'))],
    'testimonials.php' => ['priority' => '0.7', 'changefreq' => 'weekly', 'lastmod' => date('Y-m-d', filemtime('testimonials.php'))],
    'city-services.php' => ['priority' => '0.9', 'changefreq' => 'weekly', 'lastmod' => date('Y-m-d', filemtime('city-services.php'))]
];

foreach ($mainPages as $page => $settings) {
    $xml .= "  <url>\n";
    $xml .= "    <loc>{$baseUrl}/{$page}</loc>\n";
    $xml .= "    <lastmod>{$settings['lastmod']}</lastmod>\n";
    $xml .= "    <changefreq>{$settings['changefreq']}</changefreq>\n";
    $xml .= "    <priority>{$settings['priority']}</priority>\n";
    $xml .= "  </url>\n";
}

// Add individual service pages
$servicePages = [
    'services/web-design.php',
    'services/web-development.php',
    'services/ecommerce.php',
    'services/seo.php',
    'services/digital-marketing.php',
    'services/mobile-apps.php'
];

foreach ($servicePages as $page) {
    $url = $page === '' ? $baseUrl : $baseUrl . '/' . ltrim($page, '/');
    $xml .= "  <url>\n";
    $xml .= "    <loc>" . htmlspecialchars($url, ENT_QUOTES, 'UTF-8') . "</loc>\n";
    $xml .= "    <lastmod>" . date('Y-m-d') . "</lastmod>\n";
    $xml .= "    <changefreq>weekly</changefreq>\n";
    $xml .= "    <priority>0.8</priority>\n";
    $xml .= "  </url>\n";
}

// Add city-service combinations
try {
    // Try to get updated_at column first
    $stmt = $pdo->query("
        SELECT c.slug as city_slug, s.slug as service_slug, cs.updated_at
        FROM city_service cs
        JOIN cities c ON cs.city_id = c.id
        JOIN services s ON cs.service_id = s.id
        ORDER BY c.name, s.name
    ");
} catch (PDOException $e) {
    // If updated_at column doesn't exist, use created_at or current date
    $stmt = $pdo->query("
        SELECT c.slug as city_slug, s.slug as service_slug, 
               COALESCE(cs.created_at, NOW()) as updated_at
        FROM city_service cs
        JOIN cities c ON cs.city_id = c.id
        JOIN services s ON cs.service_id = s.id
        ORDER BY c.name, s.name
    ");
}

while ($row = $stmt->fetch()) {
    $lastmod = $row['updated_at'] ? date('Y-m-d', strtotime($row['updated_at'])) : date('Y-m-d');
    $seoUrl = getCityServiceUrl($row['city_slug'], $row['service_slug']);
    
    $xml .= "  <url>\n";
    $xml .= "    <loc>" . htmlspecialchars($seoUrl, ENT_QUOTES, 'UTF-8') . "</loc>\n";
    $xml .= "    <lastmod>{$lastmod}</lastmod>\n";
    $xml .= "    <changefreq>weekly</changefreq>\n";
    $xml .= "    <priority>0.8</priority>\n";
    $xml .= "  </url>\n";
}

// Add city pages
$cities = $pdo->query("SELECT slug FROM cities ORDER BY name")->fetchAll();
foreach ($cities as $city) {
    $cityUrl = getCityServicesUrl($city['slug']);
    $xml .= "  <url>\n";
    $xml .= "    <loc>" . htmlspecialchars($cityUrl, ENT_QUOTES, 'UTF-8') . "</loc>\n";
    $xml .= "    <lastmod>" . date('Y-m-d') . "</lastmod>\n";
    $xml .= "    <changefreq>weekly</changefreq>\n";
    $xml .= "    <priority>0.7</priority>\n";
    $xml .= "  </url>\n";
}

// Add service pages
$services = $pdo->query("SELECT slug FROM services ORDER BY name")->fetchAll();
foreach ($services as $service) {
    $serviceUrl = getServiceCitiesUrl($service['slug']);
    $xml .= "  <url>\n";
    $xml .= "    <loc>" . htmlspecialchars($serviceUrl, ENT_QUOTES, 'UTF-8') . "</loc>\n";
    $xml .= "    <lastmod>" . date('Y-m-d') . "</lastmod>\n";
    $xml .= "    <changefreq>weekly</changefreq>\n";
    $xml .= "    <priority>0.7</priority>\n";
    $xml .= "  </url>\n";
}

$xml .= '</urlset>';

// Save sitemap
file_put_contents('sitemap.xml', $xml);

// Also create a robots.txt file
$robots = "User-agent: *\n";
$robots .= "Allow: /\n";
$robots .= "Disallow: /admin/\n";
$robots .= "Disallow: /includes/\n";
$robots .= "\n";
$robots .= "Sitemap: {$baseUrl}/sitemap.xml\n";

file_put_contents('robots.txt', $robots);

echo "✅ Sitemap generated successfully!\n";
echo "📄 Total URLs: " . substr_count($xml, '<url>') . "\n";
echo "🔗 Sitemap URL: {$baseUrl}/sitemap.xml\n";
echo "🤖 Robots.txt created: {$baseUrl}/robots.txt\n";
echo "\n📋 Next steps:\n";
echo "1. Submit sitemap to Google Search Console\n";
echo "2. Submit sitemap to Bing Webmaster Tools\n";
echo "3. Run this script weekly to keep sitemap updated\n";
?>