setup_database.php

<?php
// Database connection parameters
$servername = "127.0.0.1";
$username = "root";
$password = "";
$dbname = "AILogdatabase";

try {
    $conn = new mysqli($servername, $username, $password);
} catch (mysqli_sql_exception $e) {
    echo "Database connection failed: " . htmlspecialchars($e->getMessage()) . "<br>";
    echo "Please make sure MySQL is running, then reload this page.";
    exit;
}

// Create database
$sql = "CREATE DATABASE IF NOT EXISTS $dbname";
if ($conn->query($sql) === TRUE) {
    echo "Database '$dbname' created or already exists successfully.<br>";
} else {
    echo "Error creating database: " . $conn->error . "<br>";
}

// Select the database
$conn->select_db($dbname);

// Create faq table
$sql = "CREATE TABLE IF NOT EXISTS faq (
    id INT AUTO_INCREMENT PRIMARY KEY,
    question VARCHAR(255) NOT NULL,
    answer TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";
if ($conn->query($sql) === TRUE) {
    echo "Table 'faq' created or already exists successfully.<br>";
} else {
    echo "Error creating table: " . $conn->error . "<br>";
}

// Create users table
$sql = "CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin', 'user') NOT NULL DEFAULT 'user',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";

if ($conn->query($sql) === TRUE) {
    echo "Table 'users' created or already exists successfully.<br>";
} else {
    echo "Error creating table: " . $conn->error . "<br>";
}

// Backward-compatible migration for existing users table
$roleColumnExists = $conn->query("SHOW COLUMNS FROM users LIKE 'role'");
if ($roleColumnExists && $roleColumnExists->num_rows === 0) {
    if ($conn->query("ALTER TABLE users ADD COLUMN role ENUM('admin', 'user') NOT NULL DEFAULT 'user'") === TRUE) {
        echo "Column 'role' added to 'users' successfully.<br>";
    } else {
        echo "Error adding 'role' column: " . $conn->error . "<br>";
    }
}

$usersCreatedAtExists = $conn->query("SHOW COLUMNS FROM users LIKE 'created_at'");
if ($usersCreatedAtExists && $usersCreatedAtExists->num_rows === 0) {
    if ($conn->query("ALTER TABLE users ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP") === TRUE) {
        echo "Column 'created_at' added to 'users' successfully.<br>";
    } else {
        echo "Error adding 'created_at' to users: " . $conn->error . "<br>";
    }
}

$faqCreatedAtExists = $conn->query("SHOW COLUMNS FROM faq LIKE 'created_at'");
if ($faqCreatedAtExists && $faqCreatedAtExists->num_rows === 0) {
    if ($conn->query("ALTER TABLE faq ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP") === TRUE) {
        echo "Column 'created_at' added to 'faq' successfully.<br>";
    } else {
        echo "Error adding 'created_at' to faq: " . $conn->error . "<br>";
    }
}

// Create activity logs table
$sql = "CREATE TABLE IF NOT EXISTS activity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL,
    action VARCHAR(100) NOT NULL,
    details TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
)";
if ($conn->query($sql) === TRUE) {
    echo "Table 'activity_logs' created or already exists successfully.<br>";
} else {
    echo "Error creating table: " . $conn->error . "<br>";
}

// Insert sample data into faq
$faqCountResult = $conn->query("SELECT COUNT(*) AS total FROM faq");
$faqCountRow = $faqCountResult ? $faqCountResult->fetch_assoc() : ['total' => 0];
if ((int)$faqCountRow['total'] === 0) {
    $sql = "INSERT INTO faq (question, answer) VALUES
        ('what is logmaster', 'LogMaster manages server logs.'),
        ('how does it work', 'It analyzes logs with AI.')";
    if ($conn->query($sql) === TRUE) {
        echo "Sample data inserted into 'faq' successfully.<br>";
    } else {
        echo "Error inserting data into 'faq': " . $conn->error . "<br>";
    }
} else {
    echo "Sample FAQ data already exists. Skipped duplicate insert.<br>";
}

// Ensure default admin exists
$adminEmail = "admin@ailogmaster.local";
$checkAdmin = $conn->prepare("SELECT id FROM users WHERE email = ? LIMIT 1");
$checkAdmin->bind_param("s", $adminEmail);
$checkAdmin->execute();
$adminResult = $checkAdmin->get_result();

if ($adminResult->num_rows === 0) {
    $defaultAdminPassword = password_hash("Admin@123", PASSWORD_DEFAULT);
    $insertAdmin = $conn->prepare("INSERT INTO users (email, password, role) VALUES (?, ?, 'admin')");
    $insertAdmin->bind_param("ss", $adminEmail, $defaultAdminPassword);
    if ($insertAdmin->execute()) {
        echo "Default admin created: admin@ailogmaster.local / Admin@123<br>";
    } else {
        echo "Error creating default admin: " . $conn->error . "<br>";
    }
} else {
    echo "Default admin already exists.<br>";
}

$conn->close();
echo "Database setup completed!";
?>