<?php
require_once 'config/db.php';

try {
    // 1. Create 'follows' table
    $sql_follows = "CREATE TABLE IF NOT EXISTS follows (
        id INT AUTO_INCREMENT PRIMARY KEY,
        follower_id INT NOT NULL,
        following_id INT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (follower_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (following_id) REFERENCES users(id) ON DELETE CASCADE,
        UNIQUE (follower_id, following_id) -- Prevent duplicate follows
    )";
    $pdo->exec($sql_follows);
    echo "✅ Table 'follows' created successfully.<br>";

    // 2. Add 'xp' and 'level' to 'users' table if not exists
    // We check if column exists first to avoid errors on re-run
    $stmt = $pdo->query("SHOW COLUMNS FROM users LIKE 'xp'");
    if ($stmt->rowCount() == 0) {
        $pdo->exec("ALTER TABLE users ADD COLUMN xp INT DEFAULT 0");
        echo "✅ Column 'xp' added to users.<br>";
    } else {
        echo "ℹ️ Column 'xp' already exists.<br>";
    }

    $stmt = $pdo->query("SHOW COLUMNS FROM users LIKE 'level'");
    if ($stmt->rowCount() == 0) {
        $pdo->exec("ALTER TABLE users ADD COLUMN level INT DEFAULT 1");
        echo "✅ Column 'level' added to users.<br>";
    } else {
        echo "ℹ️ Column 'level' already exists.<br>";
    }

    // 3. Add 'last_check_in' for Daily Bonus
    $stmt = $pdo->query("SHOW COLUMNS FROM users LIKE 'last_check_in'");
    if ($stmt->rowCount() == 0) {
        $pdo->exec("ALTER TABLE users ADD COLUMN last_check_in DATETIME NULL");
        echo "✅ Column 'last_check_in' added to users.<br>";
    } else {
        echo "ℹ️ Column 'last_check_in' already exists.<br>";
    }

    echo "<hr>🎉 Social & Gamification DB Setup Complete!";

} catch (PDOException $e) {
    die("❌ DB Error: " . $e->getMessage());
}
?>
