Geeklog Forums

<?php
/**
 * ------------------------------------------------------------
 * Geeklog AI SEO Updater - Usage Guide
 * ------------------------------------------------------------
 * Author: Ben
 * Purpose: Automatically generate SEO fields (meta_description,
 *          meta_keywords, and page_title) using the OpenAI API.
 *
 * REQUIREMENTS:
 *  - Geeklog 2.x
 *  - PHP 5.6 or later with HTTPS/TLS 1.2 support
 *  - A valid OpenAI API key (gpt-4o-mini recommended)
 *
 * INSTALLATION:
 *  1. Upload this script to your server, preferably at:
 *       /admin/utils/update_meta.php
 *
 *  2. Edit the following line with your real API key:
 *       $openai_apiKey = 'YOUR_OPENAI_API_KEY_HERE';
 *
 *  3. Make sure you are logged in as an admin with the 'story.edit' right.
 *
 *  4. The script must be able to include Geeklog:
 *       require_once $_SERVER['DOCUMENT_ROOT'] . '/lib-common.php';
 *
 * HOW IT WORKS:
 *  - The script finds all stories in gl_stories where meta_description is empty.
 *  - For each story, it calls the OpenAI API to generate:
 *      * meta_description (max 155 characters)
 *      * meta_keywords (comma-separated)
 *      * page_title (max 65 characters)
 *  - These three fields are updated automatically in the database.
 *
 * SAFETY:
 *  - Admin-only access (checks 'story.edit' permission).
 *  - Does NOT overwrite existing meta descriptions.
 *  - Always run in TEST mode first to preview results.
 *
 * MODES OF EXECUTION:
 *  - TEST mode (no database updates):
 *      https://yourdomain.com/admin/utils/update_meta.php?dry=1
 *
 *  - REAL mode (apply updates, limit number of articles):
 *      https://yourdomain.com/admin/utils/update_meta.php?limit=10
 *
 * NOTES:
 *  - The script automatically detects your site's language
 *    from $_CONF['language'] (e.g., 'french_france_utf-8').
 *  - AI metadata will be generated in the same language.
 *  - Use COM_buildUrl() to generate valid article URLs
 *    regardless of Geeklog’s URL rewriting configuration.
 *
 * TROUBLESHOOTING:
 *  - If you see “AI generation failed”, check:
 *      * Your OpenAI API key is correct.
 *      * Your server supports HTTPS (TLS 1.2+).
 *  - If the detected language is “English” on a French site,
 *    ensure $_CONF['language'] = 'french_france_utf-8' in config.php.
 *
 * Enjoy automatic SEO metadata generation with Geeklog and AI!
 * ------------------------------------------------------------
 */

require_once $_SERVER['DOCUMENT_ROOT'] . '/lib-common.php';

// --- SECURITY CHECK ---
if (!SEC_hasRights('story.edit')) {
    COM_accessLog("Unauthorized access attempt to update_meta_ai_final.php");
    die('Access denied.');
}

// --- CONFIGURATION ---
$limit         = isset($_GET['limit']) ? intval($_GET['limit']) : 1;
$dry_run       = isset($_GET['dry']) ? true : false;
$openai_apiKey = 'YOUR_OPENAI_API_KEY_HERE';  // <-- Replace with your real API key
$model         = 'gpt-4o-mini';
$max_desc_len  = 155;
$max_kw_len    = 160;
$max_title_len = 65;

// --- DETECT SITE LANGUAGE FROM GEEKLOG CONFIG ---
$lang_code = isset($_CONF['language']) ? strtolower($_CONF['language']) : 'english';
$lang = 'English'; // fallback
if (strpos($lang_code, 'french') !== false) {
    $lang = 'French';
} elseif (strpos($lang_code, 'spanish') !== false) {
    $lang = 'Spanish';
} elseif (strpos($lang_code, 'german') !== false) {
    $lang = 'German';
} elseif (strpos($lang_code, 'italian') !== false) {
    $lang = 'Italian';
} elseif (strpos($lang_code, 'japan') !== false || strpos($lang_code, 'nihon') !== false) {
    $lang = 'Japanese';
}

// --- Count total articles missing meta_description ---
$count_sql = "SELECT COUNT(*) AS total_empty
              FROM {$_TABLES['stories']}
              WHERE meta_description IS NULL OR TRIM(meta_description) = ''";

$res_count = DB_query($count_sql);
$row_count = DB_fetchArray($res_count);
$total_empty = intval($row_count['total_empty']);

echo '<h2>Geeklog AI SEO Updater by Ben</h2>';
echo '<p>Total articles missing meta_description: <strong>' . $total_empty . '</strong></p>';
echo '<p>Mode: ' . ($dry_run ? '<strong>TEST</strong>' : '<strong>UPDATE</strong>') . '</p>';
echo '<p>Detected site language: <strong>' . $lang . '</strong></p>';
echo '<p>Processing up to ' . $limit . ' article(s)</p><hr>';

// --- FETCH ARTICLES WITH EMPTY META DESCRIPTION ---
$sql = "SELECT sid, title, introtext, meta_description, meta_keywords, page_title
        FROM {$_TABLES['stories']}
        WHERE (meta_description IS NULL OR TRIM(meta_description) = '')
        ORDER BY date DESC
        LIMIT " . $limit;

$result = DB_query($sql);
$count  = DB_numRows($result);

if ($count == 0) {
    echo '<p>✅ No empty meta_description found.</p>';
    exit;
}

// --- FUNCTION: Call OpenAI API ---
function generate_ai_meta($apiKey, $model, $title, $intro, $lang) {
    $prompt = "You are an SEO assistant. The article language is $lang.
Generate metadata for SEO in the same language.
Return JSON only with the following fields:
{
  \"description\": \"short meta description (max 155 characters)\",
  \"keywords\": \"5 to 10 comma-separated keywords\",
  \"page_title\": \"SEO-optimized short title (max 65 characters)\"
}
Article title: $title
Article content: $intro";

    $data = json_encode(array(
        'model' => $model,
        'messages' => array(array('role' => 'user', 'content' => $prompt)),
        'max_tokens' => 220
    ));

    $opts = array(
        'http' => array(
            'method'  => 'POST',
            'header'  => "Content-Type: application/json\r\n" .
                         "Authorization: Bearer " . $apiKey . "\r\n",
            'content' => $data,
            'timeout' => 40
        )
    );

    $context = stream_context_create($opts);
    $result  = @file_get_contents("https://api.openai.com/v1/chat/completions", false, $context);
    if ($result === false) return false;

    $json = json_decode($result, true);
    if (!isset($json['choices'][0]['message']['content'])) return false;

    $content = trim($json['choices'][0]['message']['content']);

    // Clean Markdown artifacts like ```json or ``` wrappers
    $content = preg_replace('/^```(json)?/i', '', $content);
    $content = preg_replace('/```$/', '', $content);
    $content = trim($content);

    $decoded = json_decode($content, true);

    if (!$decoded) {
        // fallback: description only
        return array(
            'description' => substr($content, 0, 150),
            'keywords'    => '',
            'page_title'  => substr($title, 0, 60)
        );
    }

    return $decoded;
}

// --- MAIN LOOP ---
while ($row = DB_fetchArray($result)) {
    $sid   = $row['sid'];
    $title = trim(strip_tags($row['title']));
    $intro = substr(strip_tags($row['introtext']), 0, 600);

    echo '<p><strong>' . htmlspecialchars($title) . '</strong><br>';

    $meta = generate_ai_meta($openai_apiKey, $model, $title, $intro, $lang);

    if ($meta === false) {
        echo '<span style="color:red;">❌ AI generation failed.</span></p>';
        continue;
    }

    $desc   = substr(trim($meta['description']), 0, 155);
    $kw     = substr(trim($meta['keywords']), 0, 160);
    $ptitle = substr(trim($meta['page_title']), 0, 65);

    echo '→ meta_description: <em>' . htmlspecialchars($desc) . '</em><br>';
    echo '→ meta_keywords: <em>' . htmlspecialchars($kw) . '</em><br>';
    echo '→ page_title: <em>' . htmlspecialchars($ptitle) . '</em></p>';
   
    $article_url = COM_buildUrl($_CONF['site_url'] . '/article.php?story=' . $sid);
    echo '<p>🔗 <a href="' . htmlspecialchars($article_url) . '" target="_blank" rel="noopener">'
   . htmlspecialchars($title) . '</a></p>';

    if (!$dry_run) {
        $update_sql = "UPDATE {$_TABLES['stories']}
                       SET meta_description = '" . DB_escapeString($desc) . "',
                           meta_keywords = '" . DB_escapeString($kw) . "',
                           page_title = '" . DB_escapeString($ptitle) . "'
                       WHERE sid = '" . DB_escapeString($sid) . "'";
        DB_query($update_sql);
       
        // --- Notify Bing (IndexNow) via plugin if available ---
        if (function_exists('send_to_indexnow')) {
            $url = COM_buildUrl($_CONF['site_url'] . '/article.php?story=' . $sid);
            $response = send_to_indexnow($url);
            if ($response === false) {
                echo '<p>⚠️ IndexNow: key missing or plugin error.</p>';
            } elseif (empty($response)) {
                echo '<p>🛰️ IndexNow: request sent successfully (HTTP 200 OK).</p>';
            } else {
                echo '<p>🛰️ IndexNow: Bing returned data → <code>' . htmlspecialchars($response) . '</code></p>';
            }
        }
    }
}

echo '<hr>';
echo '<p>Process completed: ' . $count . ' article(s) ' . ($dry_run ? 'checked' : 'updated') . '.</p>';
?>