category_id,
                        category_year AS year, 
                        category_maker AS make, 
                        category_model AS model 
                    FROM category 
                    WHERE category_maker != '' 
                      AND category_model != '' 
                      AND status = '1'
                    ORDER BY category_year DESC, category_maker ASC, category_model ASC";
        $stmtCat = $pdo->query($sqlCat);
        $categories = $stmtCat->fetchAll();

        foreach ($categories as $cat) {
            $year = trim($cat['year']);
            $make = str_replace(' ', '_', trim($cat['make']));
            $model = str_replace(' ', '_', trim($cat['model']));
            $catId = $cat['category_id'];
            
            $catSlug = "{$year}-{$make}-{$model}-Parts-Lists-{$catId}";
            $urls[] = [
                'loc' => $baseUrl . "/products/{$catSlug}",
                'lastmod' => $today,
                'changefreq' => 'weekly',
                'priority' => '0.8'
            ];
        }

        // 4. Query All Active Products for Detail URLs (Synced with Google Shopping Feed logic)
        $sqlParts = "SELECT
                i.item_id AS id,
                i.item_num AS sku,
                isp.item_partslink_num AS partslink,
                isp.item_oem_num AS oem_number,
                isp.item_applications AS applications,
                COALESCE(ise.item_title, '') AS item_title,
                COALESCE(ise.item_title2, '') AS item_title2,
                i.item_description,
                MAX(itc.category_id) AS category_id
            FROM items i
            LEFT JOIN items_spec isp ON i.item_id = isp.item_id
            LEFT JOIN item_spec_ext ise ON i.item_id = ise.item_id
            LEFT JOIN item_to_category itc ON i.item_id = itc.item_id
            WHERE i.show_on_web = 1 
              AND i.item_price > 0
              AND i.item_description IS NOT NULL 
              AND TRIM(i.item_description) != ''
            GROUP BY i.item_id
            ORDER BY i.item_id DESC";
        $stmtParts = $pdo->query($sqlParts);
        $parts = $stmtParts->fetchAll();

        foreach ($parts as $p) {
            $id = $p['id'];
            $sku = !empty($p['sku']) ? trim($p['sku']) : '';
            
            $oeNumber = '';
            if (!empty($p['oem_number'])) {
                $cleanOem = str_replace('##', '#', $p['oem_number']);
                $oes = array_filter(explode('#', $cleanOem));
                $cleanOesArr = array_map(function($val) {
                    return preg_replace('/[^a-zA-Z0-9\-]/', '', trim($val));
                }, $oes);
                $oeNumber = implode(' ', array_filter($cleanOesArr));
            }

            list($fullTitle, $yearStr, $makeModel, $cleanPart, $attrStr, $slugYearStr) = buildPlanBTitle(
                $p['applications'],
                $p['item_title'],
                $p['item_title2'],
                $p['item_description']
            );

            $nameSlug = preg_replace('/[^a-zA-Z0-9\s]/', '', $cleanPart);
            $nameSlug = preg_replace('/\s+/', '-', trim($nameSlug));
            $oemSlug = !empty($oeNumber) ? explode(' ', $oeNumber)[0] : 'NONE';
            
            $partslink = !empty($p['partslink']) ? trim($p['partslink']) : '';
            $plSlug = !empty($partslink) ? preg_replace('/[^a-zA-Z0-9\-]/', '', $partslink) : (!empty($sku) ? preg_replace('/[^a-zA-Z0-9\-]/', '', $sku) : 'PART');

            $ymmSlug = 'ALL-ALL-ALL';
            $finalSlugYear = !empty($slugYearStr) ? $slugYearStr : $yearStr;
            if (!empty($makeModel)) {
                $ymmSlug = preg_replace('/[^a-zA-Z0-9\s\-]/', '', "{$finalSlugYear} {$makeModel}");
                $ymmSlug = preg_replace('/\s+/', '-', trim($ymmSlug));
            }

            $catId = !empty($p['category_id']) ? (int)$p['category_id'] : 0;
            $productSlug = "{$id}-{$ymmSlug}-{$nameSlug}-{$oemSlug}-{$plSlug}-{$catId}";

            $urls[] = [
                'loc' => $baseUrl . "/products_spec/{$productSlug}",
                'lastmod' => $today,
                'changefreq' => 'monthly',
                'priority' => '0.7'
            ];
        }

    } catch (Exception $e) {
        // Fallback gracefully if database fails
    }
}

// Generate XML Output
$maxUrlsPerSitemap = 50000;
$chunks = array_chunk($urls, $maxUrlsPerSitemap);
$isCliOrGenerate = php_sapi_name() === 'cli' || isset($_GET['generate']);
$xmlDirs = [dirname(__DIR__), dirname(__DIR__) . '/dist'];

if (count($chunks) <= 1) {
    $xml = new SimpleXMLElement('<?xml version="1.0" encoding="UTF-8"?><urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"/>');
    foreach ($urls as $u) {
        $urlTag = $xml->addChild('url');
        $urlTag->addChild('loc', htmlspecialchars($u['loc']));
        $urlTag->addChild('lastmod', $u['lastmod']);
        $urlTag->addChild('changefreq', $u['changefreq']);
        $urlTag->addChild('priority', $u['priority']);
    }

    if ($isCliOrGenerate) {
        foreach ($xmlDirs as $dir) {
            if (is_dir($dir)) {
                $xmlPath = $dir . '/sitemap.xml';
                $xml->asXML($xmlPath);
            }
        }
        echo "Sitemap generated successfully in web and web/dist (" . count($urls) . " URLs)\n";
    } else {
        echo $xml->asXML();
    }
} else {
    $indexXml = new SimpleXMLElement('<?xml version="1.0" encoding="UTF-8"?><sitemapindex xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"/>');

    foreach ($chunks as $index => $chunk) {
        $sitemapIndex = $index + 1;
        $sitemapFilename = "sitemap-{$sitemapIndex}.xml";
        
        $xml = new SimpleXMLElement('<?xml version="1.0" encoding="UTF-8"?><urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"/>');
        foreach ($chunk as $u) {
            $urlTag = $xml->addChild('url');
            $urlTag->addChild('loc', htmlspecialchars($u['loc']));
            $urlTag->addChild('lastmod', $u['lastmod']);
            $urlTag->addChild('changefreq', $u['changefreq']);
            $urlTag->addChild('priority', $u['priority']);
        }
        
        if ($isCliOrGenerate) {
            foreach ($xmlDirs as $dir) {
                if (is_dir($dir)) {
                    $xml->asXML($dir . '/' . $sitemapFilename);
                }
            }
        }
        
        $sitemapTag = $indexXml->addChild('sitemap');
        $sitemapTag->addChild('loc', htmlspecialchars($baseUrl . '/' . $sitemapFilename));
        $sitemapTag->addChild('lastmod', $today);
    }

    if ($isCliOrGenerate) {
        foreach ($xmlDirs as $dir) {
            if (is_dir($dir)) {
                $indexPath = $dir . '/sitemap.xml';
                $indexXml->asXML($indexPath);
            }
        }
        echo "Sitemap Index generated successfully in web and web/dist with " . count($chunks) . " sub-sitemaps (Total URLs: " . count($urls) . ")\n";
    } else {
        echo $indexXml->asXML();
    }
}