setup_database.php

<?php
/**
 * Database Setup Script
 * This script creates the database and admin_users table if they don't exist
 */

// Database configuration
$host = 'localhost';
$username = 'root';
$password = '';
$dbname = 'stratnova';

try {
    // First, connect without specifying database to create it if needed
    $pdo = new PDO("mysql:host=$host;charset=utf8mb4", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // Create database if it doesn't exist
    $pdo->exec("CREATE DATABASE IF NOT EXISTS `$dbname` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
    echo "✅ Database '$dbname' created or already exists\n";
    
    // Now connect to the specific database
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // Check if admin_users table exists
    $stmt = $pdo->query("SHOW TABLES LIKE 'admin_users'");
    $table_exists = $stmt->rowCount() > 0;
    
    if (!$table_exists) {
        // Create admin_users table
        $create_table_sql = "
        CREATE TABLE `admin_users` (
            `id` int(11) NOT NULL AUTO_INCREMENT,
            `username` varchar(50) NOT NULL,
            `password` varchar(255) NOT NULL,
            `email` varchar(100) DEFAULT NULL,
            `role` varchar(20) DEFAULT 'admin',
            `status` enum('active','inactive') DEFAULT 'active',
            `last_login` timestamp NULL DEFAULT NULL,
            `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
            `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
            PRIMARY KEY (`id`),
            UNIQUE KEY `username` (`username`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
        ";
        
        $pdo->exec($create_table_sql);
        echo "✅ admin_users table created successfully\n";
    } else {
        echo "✅ admin_users table already exists\n";
    }
    
    // Check current admin users
    $stmt = $pdo->query("SELECT COUNT(*) as count FROM admin_users");
    $count = $stmt->fetch()['count'];
    
    echo "📊 Current admin users count: $count\n";
    
    if ($count > 0) {
        echo "\nCurrent admin users:\n";
        $stmt = $pdo->query("SELECT id, username, email, role, status, created_at FROM admin_users");
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            echo "- ID: {$row['id']}, Username: {$row['username']}, Email: {$row['email']}, Status: {$row['status']}\n";
        }
    }
    
    echo "\n✅ Database setup completed successfully!\n";
    echo "\nNext steps:\n";
    echo "1. Run 'php update_admin_password.php' to set up admin credentials\n";
    echo "2. Run 'php test_admin_login.php' to test login functionality\n";
    
} catch (PDOException $e) {
    echo "❌ Database Error: " . $e->getMessage() . "\n";
    echo "\nTroubleshooting:\n";
    echo "1. Make sure XAMPP is running\n";
    echo "2. Make sure MySQL service is started in XAMPP\n";
    echo "3. Check if you can access phpMyAdmin at http://localhost/phpmyadmin\n";
}
?>