<?php
// Script to extract all titles from PHP files and generate SQL
$base_path = '/opt/lampp/htdocs';

// Use find command to get all index.php files
$output = shell_exec("find $base_path -name 'index.php' -type f ! -path '*/wp-content/*' ! -path '*/wp-includes/*' ! -path '*/admin/*' 2>/dev/null");
$file_list = explode("\n", trim($output));
$titles = [];

foreach ($file_list as $file) {
    if (empty($file) || !file_exists($file)) {
        continue;
    }
    
    $content = @file_get_contents($file);
    if (!$content) {
        continue;
    }
    
    // Extract title - try multiple patterns
    $title = null;
    
    // Pattern 1: <title>...</title>
    if (preg_match('/<title>([^<]+)<\/title>/i', $content, $matches)) {
        $title = trim($matches[1]);
    }
    // Pattern 2: og:title meta tag
    elseif (preg_match('/<meta\s+property=["\']og:title["\']\s+content=["\']([^"\']+)["\']/i', $content, $matches)) {
        $title = trim($matches[1]);
    }
    // Pattern 3: twitter:title
    elseif (preg_match('/<meta\s+name=["\']twitter:title["\']\s+content=["\']([^"\']+)["\']/i', $content, $matches)) {
        $title = trim($matches[1]);
    }
    
    if ($title) {
        $title = html_entity_decode($title, ENT_QUOTES | ENT_HTML5, 'UTF-8');
        
        // Convert file path to URL
        $url = str_replace($base_path, '', $file);
        $url = str_replace('/index.php', '', $url);
        $url = str_replace('\\', '/', $url);
        
        // Ensure URL starts with /
        if (empty($url) || $url === '/') {
            $url = '/';
        } else {
            // Add trailing slash for non-root URLs
            if (substr($url, -1) !== '/') {
                $url .= '/';
            }
        }
        
        $titles[$url] = $title;
    }
}

// Sort by URL
ksort($titles);

// Generate SQL
echo "-- Page Titles - Generated automatically\n";
echo "-- Total pages found: " . count($titles) . "\n\n";
echo "INSERT INTO `page_titles` (`url`, `title`) VALUES\n";

$count = 0;
foreach ($titles as $url => $title) {
    $count++;
    $title_escaped = str_replace("'", "''", $title); // Escape single quotes for SQL
    $comma = ($count < count($titles)) ? ',' : ';';
    echo "('$url', '$title_escaped')$comma\n";
}

echo "\n-- End of page titles\n";