setup.php

<?php
/**
 * BlueCMS Setup Script — v6
 * Run ONCE at: http://localhost/bluecms/setup.php
 * Delete this file after setup is complete.
 */
require_once __DIR__ . '/config.php';

$messages = [];
$errors   = [];

// ── 1. Test DB connection ─────────────────────────────────────
try {
    db()->query('SELECT 1');
    $messages[] = '✅ Database connected.';
} catch (Exception $e) {
    $errors[] = '❌ Database connection failed: ' . $e->getMessage();
}

if (empty($errors)) {
    // ── 2. Create all tables ──────────────────────────────────
    $tables = [

        // Users
        "CREATE TABLE IF NOT EXISTS users (
            id INT AUTO_INCREMENT PRIMARY KEY,
            username VARCHAR(50) NOT NULL UNIQUE,
            email VARCHAR(100) NOT NULL UNIQUE,
            password VARCHAR(255) NOT NULL,
            role ENUM('admin','user') DEFAULT 'user',
            version ENUM('basic','pro') DEFAULT 'basic',
            blog_title VARCHAR(150) DEFAULT NULL,
            blog_slug VARCHAR(100) DEFAULT NULL UNIQUE,
            avatar VARCHAR(255) DEFAULT NULL,
            cover VARCHAR(255) DEFAULT NULL,
            bio TEXT DEFAULT NULL,
            blog_primary_color VARCHAR(7) DEFAULT '#3e729a',
            blog_layout JSON DEFAULT NULL,
            is_active TINYINT(1) DEFAULT 1,
            blog_suspended TINYINT(1) DEFAULT 0,
            blog_theme VARCHAR(20) DEFAULT 'default',
            dark_mode TINYINT(1) DEFAULT 0,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",

        // Languages
        "CREATE TABLE IF NOT EXISTS languages (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            code VARCHAR(10) NOT NULL UNIQUE,
            flag VARCHAR(255) DEFAULT NULL,
            is_default TINYINT(1) DEFAULT 0,
            is_active TINYINT(1) DEFAULT 1,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",

        // Categories
        "CREATE TABLE IF NOT EXISTS categories (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            slug VARCHAR(150) NOT NULL,
            thumbnail VARCHAR(255) DEFAULT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",

        // Category translations
        "CREATE TABLE IF NOT EXISTS category_translations (
            id INT AUTO_INCREMENT PRIMARY KEY,
            category_id INT NOT NULL,
            language_id INT NOT NULL,
            title VARCHAR(200) NOT NULL,
            UNIQUE KEY uq_cat_lang (category_id, language_id),
            FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE,
            FOREIGN KEY (language_id) REFERENCES languages(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",

        // Tags
        "CREATE TABLE IF NOT EXISTS tags (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            name VARCHAR(100) NOT NULL,
            slug VARCHAR(110) NOT NULL,
            UNIQUE KEY uq_user_tag (user_id, slug),
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",

        // Posts
        "CREATE TABLE IF NOT EXISTS posts (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            category_id INT DEFAULT NULL,
            title VARCHAR(255) NOT NULL,
            slug VARCHAR(270) NOT NULL,
            content LONGTEXT,
            thumbnail VARCHAR(255) DEFAULT NULL,
            status ENUM('draft','published') DEFAULT 'published',
            views INT DEFAULT 0,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uq_user_slug (user_id, slug),
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",

        // Post tags
        "CREATE TABLE IF NOT EXISTS post_tags (
            post_id INT NOT NULL,
            tag_id INT NOT NULL,
            PRIMARY KEY (post_id, tag_id),
            FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
            FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",

        // Advertisements (admin)
        "CREATE TABLE IF NOT EXISTS advertisements (
            id INT AUTO_INCREMENT PRIMARY KEY,
            type ENUM('banner','text') NOT NULL,
            title VARCHAR(200) DEFAULT NULL,
            content TEXT DEFAULT NULL,
            image VARCHAR(255) DEFAULT NULL,
            link_url VARCHAR(500) DEFAULT NULL,
            is_active TINYINT(1) DEFAULT 1,
            position ENUM('header','sidebar','footer','in-content') DEFAULT 'sidebar',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",

        // User ads
        "CREATE TABLE IF NOT EXISTS user_ads (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            type ENUM('banner','text') DEFAULT 'text',
            title VARCHAR(200) DEFAULT NULL,
            content TEXT DEFAULT NULL,
            image VARCHAR(255) DEFAULT NULL,
            link_url VARCHAR(500) DEFAULT NULL,
            position ENUM('header','sidebar','footer','in-content') DEFAULT 'sidebar',
            is_active TINYINT(1) DEFAULT 1,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",

        // Site settings
        "CREATE TABLE IF NOT EXISTS site_settings (
            setting_key VARCHAR(100) PRIMARY KEY,
            setting_value TEXT DEFAULT NULL,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",

        // Parked domains
        "CREATE TABLE IF NOT EXISTS parked_domains (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            domain VARCHAR(255) NOT NULL UNIQUE,
            status ENUM('pending','active','rejected') DEFAULT 'pending',
            verified TINYINT(1) DEFAULT 0,
            verify_token VARCHAR(64) DEFAULT NULL,
            recaptcha_site_key VARCHAR(255) DEFAULT NULL,
            recaptcha_secret_key VARCHAR(255) DEFAULT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",

        // Payments
        "CREATE TABLE IF NOT EXISTS payments (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            stripe_session_id VARCHAR(255) DEFAULT NULL,
            stripe_payment_intent VARCHAR(255) DEFAULT NULL,
            amount DECIMAL(10,2) NOT NULL,
            currency VARCHAR(10) DEFAULT 'usd',
            status ENUM('pending','paid','failed','refunded') DEFAULT 'pending',
            plan VARCHAR(50) DEFAULT 'pro',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",

        // Post view logs
        "CREATE TABLE IF NOT EXISTS post_views_log (
            id INT AUTO_INCREMENT PRIMARY KEY,
            post_id INT NOT NULL,
            user_id INT NOT NULL,
            view_date DATE NOT NULL,
            count INT DEFAULT 1,
            UNIQUE KEY uq_post_date (post_id, view_date),
            FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",

        // Blog view logs
        "CREATE TABLE IF NOT EXISTS blog_views_log (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            view_date DATE NOT NULL,
            count INT DEFAULT 1,
            UNIQUE KEY uq_blog_date (user_id, view_date),
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",

        // Post reactions
        "CREATE TABLE IF NOT EXISTS post_reactions (
            id INT AUTO_INCREMENT PRIMARY KEY,
            post_id INT NOT NULL,
            reactor_ip VARCHAR(64) NOT NULL,
            session_id VARCHAR(128) NOT NULL,
            reaction ENUM('like','dislike','love','fire','laugh','sad') NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uq_reaction (post_id, session_id),
            FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",


        // Blog team members (multi-admin)
        "CREATE TABLE IF NOT EXISTS blog_team (
            id INT AUTO_INCREMENT PRIMARY KEY,
            blog_owner_id INT NOT NULL,
            user_id INT NOT NULL,
            role ENUM('admin','editor') DEFAULT 'editor',
            invited_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uq_team (blog_owner_id, user_id),
            FOREIGN KEY (blog_owner_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",

        // Email subscriptions per blog
        "CREATE TABLE IF NOT EXISTS email_subscriptions (
            id INT AUTO_INCREMENT PRIMARY KEY,
            blog_owner_id INT NOT NULL,
            email VARCHAR(150) NOT NULL,
            name VARCHAR(100) DEFAULT NULL,
            confirmed TINYINT(1) DEFAULT 0,
            confirm_token VARCHAR(64) DEFAULT NULL,
            unsubscribe_token VARCHAR(64) DEFAULT NULL,
            subscribed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uq_sub (blog_owner_id, email),
            FOREIGN KEY (blog_owner_id) REFERENCES users(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",

        // Email campaigns
        "CREATE TABLE IF NOT EXISTS email_campaigns (
            id INT AUTO_INCREMENT PRIMARY KEY,
            blog_owner_id INT NOT NULL,
            subject VARCHAR(255) NOT NULL,
            body TEXT NOT NULL,
            status ENUM('draft','sent') DEFAULT 'draft',
            sent_count INT DEFAULT 0,
            sent_at DATETIME DEFAULT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (blog_owner_id) REFERENCES users(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",


        // Reserved usernames
        "CREATE TABLE IF NOT EXISTS reserved_usernames (
            id INT AUTO_INCREMENT PRIMARY KEY,
            username VARCHAR(100) NOT NULL UNIQUE,
            reason VARCHAR(255) DEFAULT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",

        // Post translations (multi-language)
        "CREATE TABLE IF NOT EXISTS post_translations (
            id INT AUTO_INCREMENT PRIMARY KEY,
            post_id INT NOT NULL,
            language_id INT NOT NULL,
            title VARCHAR(255) NOT NULL,
            content LONGTEXT,
            slug VARCHAR(270) NOT NULL,
            UNIQUE KEY uq_post_lang (post_id, language_id),
            FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
            FOREIGN KEY (language_id) REFERENCES languages(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",

        // Comments
        "CREATE TABLE IF NOT EXISTS comments (
            id INT AUTO_INCREMENT PRIMARY KEY,
            post_id INT NOT NULL,
            parent_id INT DEFAULT NULL,
            author_name VARCHAR(100) NOT NULL,
            author_email VARCHAR(150) NOT NULL,
            content TEXT NOT NULL,
            status ENUM('pending','approved','spam') DEFAULT 'pending',
            user_id INT DEFAULT NULL,
            ip VARCHAR(64) DEFAULT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
            FOREIGN KEY (parent_id) REFERENCES comments(id) ON DELETE CASCADE,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
    ];

    $created = 0;
    foreach ($tables as $sql) {
        try {
            db()->query($sql);
            $created++;
        } catch (Exception $e) {
            $errors[] = '❌ Table error: ' . $e->getMessage();
        }
    }
    $messages[] = "✅ {$created} tables verified/created.";

    // ── 2a. Run migrate.sql if it exists ────────────────────────────────
    $migrate_file = __DIR__ . '/migrate.sql';
    if (file_exists($migrate_file)) {
        $migrate_sql = file_get_contents($migrate_file);
        // Execute each statement
        $stmts = array_filter(array_map('trim', explode(';', $migrate_sql)));
        foreach ($stmts as $stmt) {
            if (empty($stmt) || substr(ltrim($stmt), 0, 2) === '--') continue;
            if (stripos($stmt, 'USE ') === 0) continue;
            if (stripos($stmt, "SELECT '") !== false) continue;
            try { db()->query($stmt); } catch (Exception $e) { /* ignore migration errors */ }
        }
        $messages[] = '✅ Migration SQL executed.';
    }

    // ── 2b. Migrate existing tables (add missing columns safely) ──────
    $alter_statements = [
        "ALTER TABLE users ADD COLUMN IF NOT EXISTS avatar VARCHAR(255) DEFAULT NULL",
        "ALTER TABLE users ADD COLUMN IF NOT EXISTS cover VARCHAR(255) DEFAULT NULL",
        "ALTER TABLE users ADD COLUMN IF NOT EXISTS bio TEXT DEFAULT NULL",
        "ALTER TABLE users ADD COLUMN IF NOT EXISTS blog_primary_color VARCHAR(7) DEFAULT '#3e729a'",
        "ALTER TABLE users ADD COLUMN IF NOT EXISTS blog_layout JSON DEFAULT NULL",
        "ALTER TABLE users ADD COLUMN IF NOT EXISTS blog_theme VARCHAR(20) DEFAULT 'default'",
        "ALTER TABLE users ADD COLUMN IF NOT EXISTS dark_mode TINYINT(1) DEFAULT 0",
        "ALTER TABLE parked_domains ADD COLUMN IF NOT EXISTS recaptcha_site_key VARCHAR(255) DEFAULT NULL",
        "ALTER TABLE parked_domains ADD COLUMN IF NOT EXISTS recaptcha_secret_key VARCHAR(255) DEFAULT NULL",
    ];
    foreach ($alter_statements as $sql) {
        try { db()->query($sql); } catch (Exception $e) { /* Column may already exist */ }
    }
    $messages[] = '✅ Database migrations applied.';

    // ── 3. Default settings ───────────────────────────────────
    $defaults = [
        'stripe_publishable_key' => '',
        'stripe_secret_key'      => '',
        'stripe_webhook_secret'  => '',
        'stripe_pro_price_id'    => '',
        'stripe_enabled'         => '0',
        'pro_price_monthly'      => '9.00',
        'pro_price_label'        => '$9/month',
        'recaptcha_site_key'     => '',
        'recaptcha_secret_key'   => '',
        'recaptcha_enabled'      => '0',
        'comments_auto_approve'  => '0',
        'ns1'                    => 'ns1.bluecms.org',
        'ns2'                    => 'ns2.bluecms.org',
        'smtp_host'              => '',
        'smtp_port'              => '587',
        'smtp_user'              => '',
        'smtp_pass'              => '',
        'smtp_from_name'         => '',
        'smtp_from_email'        => '',
        'smtp_enabled'           => '0',
    ];
    foreach ($defaults as $k => $v) {
        db()->query("INSERT IGNORE INTO site_settings (setting_key,setting_value) VALUES ('".db()->real_escape_string($k)."','".db()->real_escape_string($v)."')");
    }
    $messages[] = '✅ Default settings seeded.';

    // ── 3b. Seed reserved usernames ──────────────────────────
    $reserved_list = ['admin','dashboard','api','assets','includes','plugins','uploads',
        'blog','sitemap','login','logout','register','setup','install','upgrade',
        'subscribe','unsubscribe','stripe','blue-admin','www','mail','ftp',
        'smtp','pop','imap','support','help','info','contact','about','home','index'];
    foreach ($reserved_list as $r) {
        try {
            $rq = db()->prepare("INSERT IGNORE INTO reserved_usernames (username,reason) VALUES (?,'System path')");
            $rq->bind_param('s', $r);
            $rq->execute();
        } catch (Exception $e) {}
    }
    $messages[] = '✅ Reserved usernames seeded (' . count($reserved_list) . ' entries).';

    // ── 4. Default language ───────────────────────────────────
    $lang = db()->query("SELECT id FROM languages WHERE is_default=1 LIMIT 1")->fetch_assoc();
    if (!$lang) {
        db()->query("INSERT INTO languages (name,code,is_default,is_active) VALUES ('English','en',1,1)");
        $messages[] = '✅ Default language (English) created.';
    } else {
        $messages[] = '✅ Default language exists.';
    }

    // ── 5. Admin user ─────────────────────────────────────────
    $password = 'admin123!!';
    $hash     = password_hash($password, PASSWORD_BCRYPT, ['cost' => 12]);

    $existing = db()->query("SELECT id FROM users WHERE email='admin@admin.com' LIMIT 1")->fetch_assoc();
    if ($existing) {
        $stmt = db()->prepare("UPDATE users SET password=?, role='admin', version='pro', is_active=1 WHERE email='admin@admin.com'");
        $stmt->bind_param('s', $hash);
        $stmt->execute();
        $messages[] = '✅ Admin password reset.';
    } else {
        $stmt = db()->prepare("INSERT INTO users (username,email,password,role,version,blog_title,blog_slug,is_active) VALUES ('admin','admin@admin.com',?,'admin','pro','Admin Blog','admin',1)");
        $stmt->bind_param('s', $hash);
        $stmt->execute();
        $messages[] = '✅ Admin user created.';
    }

    // Verify login works
    $row = db()->query("SELECT password FROM users WHERE email='admin@admin.com' LIMIT 1")->fetch_assoc();
    if ($row && password_verify('admin123!!', $row['password'])) {
        $messages[] = '✅ Admin login verified.';
    } else {
        $errors[] = '❌ Admin password verification failed — try running setup again.';
    }

    // ── 6. Upload directories ─────────────────────────────────
    $dirs = [
        __DIR__ . '/uploads',
        __DIR__ . '/uploads/thumbnails',
        __DIR__ . '/uploads/flags',
        __DIR__ . '/uploads/ads',
    ];
    foreach ($dirs as $dir) {
        if (!is_dir($dir)) {
            mkdir($dir, 0755, true);
            $messages[] = '✅ Created: ' . basename($dir) . '/';
        }
    }
    $messages[] = '✅ Upload directories ready.';
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>BlueCMS Setup</title>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css">
    <style>
        body { background: #f0f4f8; font-family: 'Segoe UI', sans-serif; }
        .setup-card { max-width: 680px; margin: 40px auto; }
    </style>
</head>
<body>
<div class="setup-card px-3">
    <div class="text-center mb-4">
        <h2 class="mt-4"><i class="bi bi-layers-half me-2"></i>BlueCMS Setup</h2>
        <p class="text-muted">One-time installation wizard</p>
    </div>

    <?php foreach ($messages as $m): ?>
    <div class="alert alert-success py-2 mb-2"><?= $m ?></div>
    <?php endforeach; ?>

    <?php foreach ($errors as $e): ?>
    <div class="alert alert-danger py-2 mb-2"><?= $e ?></div>
    <?php endforeach; ?>

    <?php if (empty($errors)): ?>
    <div class="card shadow-sm mt-3">
        <div class="card-body">
            <h5 class="fw-bold mb-3">🎉 Setup Complete!</h5>
            <table class="table table-borderless mb-3 small">
                <tr><th style="width:160px;">Site URL</th><td><code><?= SITE_URL ?></code></td></tr>
                <tr><th>Admin Email</th><td><code>admin@admin.com</code></td></tr>
                <tr><th>Admin Password</th><td><code>admin123!!</code></td></tr>
                <tr><th>Admin Panel</th><td><a href="<?= SITE_URL ?>/admin/"><?= SITE_URL ?>/admin/</a></td></tr>
                <tr><th>Dashboard</th><td><a href="<?= SITE_URL ?>/dashboard/"><?= SITE_URL ?>/dashboard/</a></td></tr>
            </table>
            <div class="alert alert-warning mb-3">
                <i class="bi bi-exclamation-triangle me-2"></i>
                <strong>Security:</strong> Delete <code>setup.php</code> immediately after setup!
            </div>
            <div class="d-flex gap-2">
                <a href="<?= SITE_URL ?>/login.php" class="btn btn-primary">
                    <i class="bi bi-box-arrow-in-right me-1"></i>Go to Login
                </a>
                <a href="<?= SITE_URL ?>/admin/" class="btn btn-outline-secondary">
                    <i class="bi bi-shield-check me-1"></i>Admin Panel
                </a>
            </div>
        </div>
    </div>
    <?php else: ?>
    <div class="alert alert-warning mt-3">
        Fix the errors above, check <code>config.php</code> database settings, then reload this page.
    </div>
    <?php endif; ?>
</div>
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.11.3/font/bootstrap-icons.min.css">
</body>
</html>