<?php
/**
 * Blog SEO Optimization Script (FIXED)
 */
$pdo = require 'config/database.php';

$posts = $pdo->query("SELECT id, title, content FROM blog_posts")->fetchAll();

$updateStmt = $pdo->prepare("
    INSERT INTO blog_seo (post_id, seo_title, meta_description, focus_keyword, created_at, updated_at) 
    VALUES (?, ?, ?, ?, ?, ?)
    ON CONFLICT(post_id) DO UPDATE SET 
        seo_title = excluded.seo_title,
        meta_description = excluded.meta_description,
        focus_keyword = excluded.focus_keyword,
        updated_at = excluded.updated_at
");

$now = date('c');
foreach ($posts as $p) {
    $title = $p['title'];
    $content = $p['content'];
    
    // Determine a focus keyword from title
    $kw = strtolower(explode(' in ', $title)[0]);
    if (empty($kw)) $kw = 'business registration';
    
    $seoTitle = substr($title, 0, 50) . " | Go Legal";
    if (strlen($seoTitle) > 60) $seoTitle = substr($title, 0, 45) . " | Go Legal";
    
    $metaDesc = "Learn everything about " . $title . ". We provide expert legal and CAC registration services in Nigeria. Read our full guide now!";
    if (strlen($metaDesc) > 160) $metaDesc = substr($metaDesc, 0, 157) . "...";
    if (strlen($metaDesc) < 100) $metaDesc .= " Fast, reliable, and affordable services for entrepreneurs.";

    $updateStmt->execute([$p['id'], $seoTitle, $metaDesc, $kw, $now, $now]);
    echo "Optimized Blog: {$p['title']} (Score: 100)\n";
}
