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

function columnExists($pdo, $table, $column) {
    try {
        $stmt = $pdo->prepare("SELECT 1 FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = ? AND column_name = ?");
        $stmt->execute([$table, $column]);
        return $stmt->fetchColumn() !== false;
    } catch (PDOException $e) {
        // If the query fails, we can assume the column doesn't exist or there's a bigger issue.
        return false;
    }
}

try {
    $pdo = db();

    // Create users table
    $sql_users = "
    CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(255) NOT NULL,
        email VARCHAR(255) NOT NULL UNIQUE,
        password VARCHAR(255) NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );";
    $pdo->exec($sql_users);
    echo "Table 'users' is ready.\n";

    // Add username column to users table
    if (!columnExists($pdo, 'users', 'username')) {
        $pdo->exec("ALTER TABLE users ADD COLUMN username VARCHAR(255) NOT NULL AFTER id");
        echo "Column 'username' added to 'users' table.\n";
    }

    // Add role column to users table
    if (!columnExists($pdo, 'users', 'role')) {
        $pdo->exec("ALTER TABLE users ADD COLUMN role VARCHAR(50) NOT NULL DEFAULT 'user'");
        echo "Column 'role' added to 'users' table.\n";
    }

    // Add subscription_plan column to users table
    if (!columnExists($pdo, 'users', 'subscription_plan')) {
        $pdo->exec("ALTER TABLE users ADD COLUMN subscription_plan VARCHAR(50) DEFAULT NULL");
        echo "Column 'subscription_plan' added to 'users' table.\n";
    }

    // Add subscription_expires_at column to users table
    if (!columnExists($pdo, 'users', 'subscription_expires_at')) {
        $pdo->exec("ALTER TABLE users ADD COLUMN subscription_expires_at DATE DEFAULT NULL");
        echo "Column 'subscription_expires_at' added to 'users' table.\n";
    }

    // Create generations table
    $sql_generations = "
    CREATE TABLE IF NOT EXISTS generations (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        prompt TEXT,
        description TEXT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    );";
    $pdo->exec($sql_generations);
    echo "Table 'generations' is ready.\n";

    echo "\nDatabase setup completed successfully!\n";

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