<?php
$pdo = require __DIR__ . '/config/database.php';

try {
    $pdo->exec("CREATE TABLE IF NOT EXISTS blog_posts (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        author_id INTEGER,
        title TEXT NOT NULL,
        slug TEXT NOT NULL UNIQUE,
        content TEXT,
        excerpt TEXT,
        featured_image TEXT,
        status TEXT DEFAULT 'draft',
        published_at TEXT,
        created_at TEXT NOT NULL DEFAULT (datetime('now')),
        updated_at TEXT NOT NULL DEFAULT (datetime('now')),
        FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE SET NULL
    )");

    $pdo->exec("CREATE TABLE IF NOT EXISTS blog_seo (
        post_id INTEGER PRIMARY KEY,
        seo_title TEXT,
        meta_description TEXT,
        focus_keyword TEXT,
        meta_keywords TEXT,
        og_title TEXT,
        og_description TEXT,
        twitter_title TEXT,
        twitter_description TEXT,
        canonical_url TEXT,
        robots_index INTEGER DEFAULT 1,
        created_at TEXT NOT NULL DEFAULT (datetime('now')),
        updated_at TEXT NOT NULL DEFAULT (datetime('now')),
        FOREIGN KEY (post_id) REFERENCES blog_posts(id) ON DELETE CASCADE
    )");

    echo "Blog tables created successfully.\n";
} catch (PDOException $e) {
    echo "Error creating blog tables: " . $e->getMessage() . "\n";
}
?>
