<?php
require_once 'auth.php';

// Modern SEO Optimizer
ini_set('display_errors', 1);
error_reporting(E_ALL);

// Database connection
$host = "localhost";
$user = "root";
$pass = "";
$db = "finggu";

$success_messages = [];
$error_messages = [];

// Initialize PDO connection
try {
    $pdo = new PDO("mysql:host=$host;dbname=$db;charset=utf8mb4", $user, $pass, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
    ]);
} catch (Exception $e) {
    die('Database connection failed: ' . $e->getMessage());
}

// Handle bulk optimization
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action'])) {
    try {
        switch ($_POST['action']) {
            case 'optimize_all':
                optimizeSeo($pdo, 'all');
                break;
            case 'optimize_empty':
                optimizeSeo($pdo, 'empty');
                break;
            case 'update_meta':
                if (!empty($_POST['id']) && isset($_POST['seo_title'], $_POST['seo_description'])) {
                    updateMeta($pdo, $_POST['id'], $_POST['seo_title'], $_POST['seo_description']);
                }
                break;
        }
    } catch (Exception $e) {
        $error_messages[] = "Error: " . $e->getMessage();
    }
}

// Get SEO stats
$stats = [
    'total_pages' => $pdo->query("SELECT COUNT(*) FROM city_service")->fetchColumn(),
    'optimized' => $pdo->query("SELECT COUNT(*) FROM city_service WHERE seo_title IS NOT NULL AND seo_title != ''")->fetchColumn(),
    'empty_titles' => $pdo->query("SELECT COUNT(*) FROM city_service WHERE seo_title IS NULL OR seo_title = ''")->fetchColumn(),
    'empty_descriptions' => $pdo->query("SELECT COUNT(*) FROM city_service WHERE seo_description IS NULL OR seo_description = ''")->fetchColumn(),
];

// Get recent optimized items
$recently_optimized = $pdo->query(
    "SELECT cs.id, c.name as city_name, s.name as service_name, cs.seo_title, cs.seo_description 
     FROM city_service cs
     JOIN cities c ON cs.city_id = c.id
     JOIN services s ON cs.service_id = s.id
     WHERE cs.seo_title IS NOT NULL AND cs.seo_title != ''
     ORDER BY cs.updated_at DESC LIMIT 5"
)->fetchAll();

// Get items needing optimization
$needs_optimization = $pdo->query(
    "SELECT cs.id, c.name as city_name, s.name as service_name 
     FROM city_service cs
     JOIN cities c ON cs.city_id = c.id
     JOIN services s ON cs.service_id = s.id
     WHERE cs.seo_title IS NULL OR cs.seo_title = '' OR cs.seo_description IS NULL OR cs.seo_description = ''
     ORDER BY c.name, s.name LIMIT 10"
)->fetchAll();

/**
 * Optimize SEO for city-service combinations
 */
function optimizeSeo($pdo, $type = 'all', $limit = 10) {
    global $success_messages;
    
    $query = "
        SELECT cs.id, c.name as city_name, s.name as service_name, cs.seo_title, cs.seo_description
        FROM city_service cs
        JOIN cities c ON cs.city_id = c.id
        JOIN services s ON cs.service_id = s.id
    ";
    
    if ($type === 'empty') {
        $query .= " WHERE cs.seo_title IS NULL OR cs.seo_title = '' OR cs.seo_description IS NULL OR cs.seo_description = ''";
    }
    
    $query .= " ORDER BY c.name, s.name LIMIT :limit";
    
    $stmt = $pdo->prepare($query);
    $stmt->bindValue(':limit', (int)$limit, PDO::PARAM_INT);
    $stmt->execute();
    $records = $stmt->fetchAll();
    
    $updated = 0;
    $updateStmt = $pdo->prepare("UPDATE city_service SET seo_title = ?, seo_description = ?, updated_at = NOW() WHERE id = ?");
    
    foreach ($records as $record) {
        $cityName = $record['city_name'];
        $serviceName = $record['service_name'];
        
        // Skip if already has good SEO data
        if (!empty($record['seo_title']) && !empty($record['seo_description']) && $type !== 'all') {
            continue;
        }
        
        // Generate SEO data
        $seoTitle = "$serviceName in $cityName | Finggu Infotech";
        $seoDescription = "Professional $serviceName services in $cityName. Contact Finggu Infotech for expert solutions and the best rates in town.";
        
        $updateStmt->execute([$seoTitle, $seoDescription, $record['id']]);
        $updated++;
    }
    
    if ($updated > 0) {
        $success_messages[] = "✅ Successfully optimized $updated records!";
    } else {
        $success_messages[] = "ℹ️ No records needed optimization.";
    }
}

/**
 * Update meta tags for a single city-service
 */
function updateMeta($pdo, $id, $title, $description) {
    global $success_messages;
    
    $stmt = $pdo->prepare("UPDATE city_service SET seo_title = ?, seo_description = ?, updated_at = NOW() WHERE id = ?");
    $stmt->execute([$title, $description, $id]);
    
    $success_messages[] = "✅ SEO data updated successfully!";
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Modern SEO Optimizer | Finggu Admin</title>
    <link href="https://cdn.jsdelivr.net/npm/tailwindcss@2.2.19/dist/tailwind.min.css" rel="stylesheet">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0-beta3/css/all.min.css">
    <style>
        .gradient-bg {
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
        }
        .stat-card {
            transition: transform 0.3s ease, box-shadow 0.3s ease;
        }
        .stat-card:hover {
            transform: translateY(-5px);
            box-shadow: 0 10px 20px rgba(0,0,0,0.1);
        }
        .btn-primary {
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            transition: all 0.3s ease;
        }
        .btn-primary:hover {
            transform: translateY(-2px);
            box-shadow: 0 5px 15px rgba(0,0,0,0.2);
        }
        .seo-preview {
            background: #f8f9fa;
            border-left: 4px solid #4f46e5;
            padding: 1rem;
            border-radius: 0 4px 4px 0;
        }
        .seo-preview-title {
            color: #1a0dab;
            font-size: 18px;
            line-height: 1.3;
            margin: 0 0 3px;
            padding: 0;
        }
        .seo-preview-url {
            color: #006621;
            font-size: 14px;
            line-height: 1.5;
        }
        .seo-preview-description {
            color: #545454;
            font-size: 14px;
            line-height: 1.4;
            word-wrap: break-word;
        }
    </style>
</head>
<body class="min-h-screen bg-gray-50">
    <!-- Header -->
    <header class="gradient-bg text-white shadow-lg">
        <div class="container mx-auto px-4 py-6">
            <div class="flex justify-between items-center">
                <h1 class="text-2xl font-bold">
                    <i class="fas fa-rocket mr-2"></i> Modern SEO Optimizer
                </h1>
                <a href="modern-dashboard.php" class="bg-white text-indigo-700 px-4 py-2 rounded-lg font-medium hover:bg-gray-100 transition">
                    <i class="fas fa-arrow-left mr-2"></i> Back to Dashboard
                </a>
            </div>
        </div>
    </header>

    <main class="container mx-auto px-4 py-8">
        <!-- Messages -->
        <?php if (!empty($success_messages)): ?>
            <?php foreach ($success_messages as $message): ?>
                <div class="bg-green-100 border-l-4 border-green-500 text-green-700 p-4 mb-6 rounded">
                    <p class="font-medium"><?= htmlspecialchars($message) ?></p>
                </div>
            <?php endforeach; ?>
        <?php endif; ?>

        <?php if (!empty($error_messages)): ?>
            <?php foreach ($error_messages as $message): ?>
                <div class="bg-red-100 border-l-4 border-red-500 text-red-700 p-4 mb-6 rounded">
                    <p class="font-medium"><?= htmlspecialchars($message) ?></p>
                </div>
            <?php endforeach; ?>
        <?php endif; ?>

        <!-- Stats Cards -->
        <div class="grid grid-cols-1 md:grid-cols-2 lg:grid-cols-4 gap-6 mb-8">
            <div class="stat-card bg-white p-6 rounded-lg shadow-md">
                <div class="text-3xl font-bold text-indigo-600"><?= number_format($stats['total_pages']) ?></div>
                <div class="text-gray-600 mt-2">Total Pages</div>
                <div class="mt-2 text-sm text-gray-500">All city-service combinations</div>
            </div>
            <div class="stat-card bg-white p-6 rounded-lg shadow-md">
                <div class="text-3xl font-bold text-green-600"><?= number_format($stats['optimized']) ?></div>
                <div class="text-gray-600 mt-2">Optimized</div>
                <div class="mt-2 text-sm text-gray-500">Pages with SEO data</div>
            </div>
            <div class="stat-card bg-white p-6 rounded-lg shadow-md">
                <div class="text-3xl font-bold text-yellow-600"><?= number_format($stats['empty_titles']) ?></div>
                <div class="text-gray-600 mt-2">Missing Titles</div>
                <div class="mt-2 text-sm text-gray-500">Pages needing title updates</div>
            </div>
            <div class="stat-card bg-white p-6 rounded-lg shadow-md">
                <div class="text-3xl font-bold text-red-600"><?= number_format($stats['empty_descriptions']) ?></div>
                <div class="text-gray-600 mt-2">Missing Descriptions</div>
                <div class="mt-2 text-sm text-gray-500">Pages needing meta descriptions</div>
            </div>
        </div>

        <!-- Quick Actions -->
        <div class="bg-white rounded-lg shadow-md p-6 mb-8">
            <h2 class="text-xl font-semibold mb-4 text-gray-800">Quick Actions</h2>
            <div class="flex flex-wrap gap-4">
                <form method="POST" class="inline-block">
                    <input type="hidden" name="action" value="optimize_empty">
                    <button type="submit" class="btn-primary text-white px-6 py-3 rounded-lg font-medium">
                        <i class="fas fa-magic mr-2"></i> Optimize Empty Fields
                    </button>
                </form>
                <form method="POST" class="inline-block">
                    <input type="hidden" name="action" value="optimize_all">
                    <button type="submit" class="bg-indigo-100 text-indigo-700 px-6 py-3 rounded-lg font-medium hover:bg-indigo-200 transition">
                        <i class="fas fa-sync-alt mr-2"></i> Re-optimize All
                    </button>
                </form>
            </div>
        </div>

        <!-- Recently Optimized -->
        <div class="grid grid-cols-1 lg:grid-cols-2 gap-8 mb-8">
            <div class="bg-white rounded-lg shadow-md p-6">
                <h2 class="text-xl font-semibold mb-4 text-gray-800">
                    <i class="fas fa-history text-indigo-500 mr-2"></i> Recently Optimized
                </h2>
                <?php if (!empty($recently_optimized)): ?>
                    <div class="space-y-4">
                        <?php foreach ($recently_optimized as $item): ?>
                            <div class="border-b border-gray-100 pb-4 last:border-0 last:pb-0">
                                <div class="font-medium text-gray-800"><?= htmlspecialchars($item['city_name']) ?> - <?= htmlspecialchars($item['service_name']) ?></div>
                                <div class="text-sm text-gray-500 mt-1">
                                    <span class="font-medium">Title:</span> 
                                    <span class="truncate"><?= htmlspecialchars($item['seo_title']) ?></span>
                                </div>
                            </div>
                        <?php endforeach; ?>
                    </div>
                <?php else: ?>
                    <p class="text-gray-500 italic">No recently optimized items.</p>
                <?php endif; ?>
            </div>

            <!-- Needs Optimization -->
            <div class="bg-white rounded-lg shadow-md p-6">
                <h2 class="text-xl font-semibold mb-4 text-gray-800">
                    <i class="fas fa-exclamation-triangle text-yellow-500 mr-2"></i> Needs Optimization
                </h2>
                <?php if (!empty($needs_optimization)): ?>
                    <div class="space-y-4">
                        <?php foreach ($needs_optimization as $item): ?>
                            <div class="border-b border-gray-100 pb-4 last:border-0 last:pb-0">
                                <div class="font-medium text-gray-800">
                                    <?= htmlspecialchars($item['city_name']) ?> - <?= htmlspecialchars($item['service_name']) ?>
                                </div>
                                <div class="mt-2">
                                    <button onclick="editSeo(<?= $item['id'] ?>, '<?= htmlspecialchars(addslashes($item['city_name'])) ?>', '<?= htmlspecialchars(addslashes($item['service_name'])) ?>')" 
                                            class="text-sm bg-indigo-100 text-indigo-700 px-3 py-1 rounded hover:bg-indigo-200 transition">
                                        <i class="fas fa-edit mr-1"></i> Edit SEO
                                    </button>
                                </div>
                            </div>
                        <?php endforeach; ?>
                    </div>
                <?php else: ?>
                    <p class="text-gray-500 italic">All items are optimized! 🎉</p>
                <?php endif; ?>
            </div>
        </div>

        <!-- SEO Preview Section -->
        <div class="bg-white rounded-lg shadow-md p-6 mb-8">
            <h2 class="text-xl font-semibold mb-4 text-gray-800">
                <i class="fas fa-eye text-indigo-500 mr-2"></i> SEO Preview
            </h2>
            <div id="seo-preview-container" class="seo-preview">
                <p class="text-gray-500 text-center py-8">Select an item to preview or edit its SEO data.</p>
            </div>
        </div>
    </main>

    <!-- Edit SEO Modal -->
    <div id="editSeoModal" class="fixed inset-0 bg-black bg-opacity-50 flex items-center justify-center hidden">
        <div class="bg-white rounded-lg w-full max-w-2xl mx-4">
            <div class="p-6">
                <div class="flex justify-between items-center mb-4">
                    <h3 class="text-xl font-semibold text-gray-800">Edit SEO Data</h3>
                    <button onclick="closeModal()" class="text-gray-500 hover:text-gray-700">
                        <i class="fas fa-times text-2xl"></i>
                    </button>
                </div>
                <form id="seoForm" method="POST">
                    <input type="hidden" name="action" value="update_meta">
                    <input type="hidden" name="id" id="editId">
                    
                    <div class="mb-4">
                        <label class="block text-gray-700 text-sm font-medium mb-2" for="cityService">
                            City - Service
                        </label>
                        <input type="text" id="cityService" class="w-full p-3 border border-gray-300 rounded-lg" disabled>
                    </div>
                    
                    <div class="mb-4">
                        <label class="block text-gray-700 text-sm font-medium mb-2" for="seoTitle">
                            SEO Title <span id="titleCount" class="text-gray-500 text-xs">(0/60)</span>
                        </label>
                        <input type="text" id="seoTitle" name="seo_title" 
                               class="w-full p-3 border border-gray-300 rounded-lg focus:ring-2 focus:ring-indigo-500 focus:border-indigo-500"
                               maxlength="60"
                               oninput="updateCharCount('seoTitle', 'titleCount', 60)">
                        <p class="text-xs text-gray-500 mt-1">Recommended: 50-60 characters</p>
                    </div>
                    
                    <div class="mb-6">
                        <label class="block text-gray-700 text-sm font-medium mb-2" for="seoDescription">
                            Meta Description <span id="descCount" class="text-gray-500 text-xs">(0/160)</span>
                        </label>
                        <textarea id="seoDescription" name="seo_description" rows="3"
                                 class="w-full p-3 border border-gray-300 rounded-lg focus:ring-2 focus:ring-indigo-500 focus:border-indigo-500"
                                 maxlength="160"
                                 oninput="updateCharCount('seoDescription', 'descCount', 160)"></textarea>
                        <p class="text-xs text-gray-500 mt-1">Recommended: 150-160 characters</p>
                    </div>
                    
                    <div class="flex justify-end space-x-3">
                        <button type="button" onclick="closeModal()" class="px-4 py-2 border border-gray-300 rounded-lg text-gray-700 hover:bg-gray-50">
                            Cancel
                        </button>
                        <button type="submit" class="px-6 py-2 bg-indigo-600 text-white rounded-lg hover:bg-indigo-700 focus:outline-none focus:ring-2 focus:ring-offset-2 focus:ring-indigo-500">
                            Save Changes
                        </button>
                    </div>
                </form>
            </div>
        </div>
    </div>

    <script>
        // Update character count
        function updateCharCount(inputId, countId, max) {
            const input = document.getElementById(inputId);
            const countElement = document.getElementById(countId);
            const count = input.value.length;
            countElement.textContent = `(${count}/${max})`;
            
            // Update preview
            updatePreview();
        }
        
        // Update SEO preview
        function updatePreview() {
            const title = document.getElementById('seoTitle').value || 'Example Page Title | Finggu Infotech';
            const description = document.getElementById('seoDescription').value || 'This is an example meta description for your page. It should be engaging and contain relevant keywords.';
            
            const preview = `
                <p class="seo-preview-title">${title.substring(0, 60)}${title.length > 60 ? '...' : ''}</p>
                <p class="seo-preview-url">finggu.com/${document.getElementById('cityService').value.toLowerCase().replace(/\s+/g, '-')}</p>
                <p class="seo-preview-description">${description.substring(0, 160)}${description.length > 160 ? '...' : ''}</p>
            `;
            
            document.getElementById('seo-preview-container').innerHTML = preview;
        }
        
        // Edit SEO data
        function editSeo(id, city, service) {
            document.getElementById('editId').value = id;
            document.getElementById('cityService').value = `${city} - ${service}`;
            
            // Reset form
            document.getElementById('seoForm').reset();
            document.getElementById('titleCount').textContent = '(0/60)';
            document.getElementById('descCount').textContent = '(0/160)';
            
            // Show modal
            document.getElementById('editSeoModal').classList.remove('hidden');
            
            // Generate default SEO data
            const defaultTitle = `${service} in ${city} | Finggu Infotech`;
            const defaultDesc = `Professional ${service} services in ${city}. Contact Finggu Infotech for expert solutions and the best rates in town.`;
            
            // Set default values
            document.getElementById('seoTitle').value = defaultTitle;
            document.getElementById('seoDescription').value = defaultDesc;
            
            // Update character counts
            document.getElementById('titleCount').textContent = `(${defaultTitle.length}/60)`;
            document.getElementById('descCount').textContent = `(${defaultDesc.length}/160)`;
            
            // Update preview
            updatePreview();
        }
        
        // Close modal
        function closeModal() {
            document.getElementById('editSeoModal').classList.add('hidden');
        }
        
        // Close modal when clicking outside
        window.onclick = function(event) {
            const modal = document.getElementById('editSeoModal');
            if (event.target === modal) {
                closeModal();
            }
        }
    </script>
</body>
</html>
