<?php
/**
 * Database Setup Script
 * 
 * This script will create the database and all required tables for Baso Alam website.
 * Run this script once to set up your database structure.
 */

// Database credentials
$host = '127.0.0.1';  // Use IP instead of 'localhost'
$port = 3306;         // Specify MySQL port explicitly
$user = 'root';       // Change to your database username
$password = '';       // Change to your database password

// Path to SQL file
$sql_file = 'database/baso_alam_db.sql';

// Default admin credentials
$admin_username = 'admin';
$admin_password = 'admin123'; // This will be hashed before saving
$admin_name = 'Administrator';
$admin_email = 'admin@basoalam.com';

// Connect to MySQL without selecting a database
try {
    // Add port to connection string and set connection timeout
    $pdo = new PDO("mysql:host=$host;port=$port", $user, $password, array(
        PDO::ATTR_TIMEOUT => 5, // 5 seconds timeout
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    ));
    
    echo "Connected to MySQL server successfully!<br>";
    
    // Read SQL file
    $sql = file_get_contents($sql_file);
    if (!$sql) {
        throw new Exception("Error reading the SQL file: $sql_file");
    }
    
    // Split SQL file into individual statements
    $statements = explode(';', $sql);
    
    // Execute each statement
    foreach ($statements as $statement) {
        $statement = trim($statement);
        if (!empty($statement)) {
            try {
                $pdo->exec($statement);
                echo "Executed: " . substr($statement, 0, 50) . "...<br>";
            } catch (PDOException $e) {
                echo "Error executing statement: " . $statement . "<br>";
                echo "Error message: " . $e->getMessage() . "<br>";
            }
        }
    }
    
    // Create admin user if it doesn't exist
    try {
        // Connect to the newly created database
        $dbname = 'baso_alam_db'; // Should match the name in the SQL file
        $pdo = new PDO("mysql:host=$host;port=$port;dbname=$dbname", $user, $password);
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        
        // Check if users table exists
        $stmt = $pdo->query("SHOW TABLES LIKE 'users'");
        $tableExists = $stmt->rowCount() > 0;
        
        if ($tableExists) {
            // Check if admin user already exists
            $stmt = $pdo->prepare("SELECT * FROM users WHERE username = :username LIMIT 1");
            $stmt->bindParam(':username', $admin_username);
            $stmt->execute();
            
            if ($stmt->rowCount() == 0) {
                // Create admin user
                $hashed_password = password_hash($admin_password, PASSWORD_DEFAULT);
                $stmt = $pdo->prepare("INSERT INTO users (username, password, name, email, role, created_at) 
                                     VALUES (:username, :password, :name, :email, 'admin', NOW())");
                $stmt->bindParam(':username', $admin_username);
                $stmt->bindParam(':password', $hashed_password);
                $stmt->bindParam(':name', $admin_name);
                $stmt->bindParam(':email', $admin_email);
                $stmt->execute();
                
                echo "<br>Admin user created successfully!<br>";
                echo "Username: " . $admin_username . "<br>";
                echo "Password: " . $admin_password . "<br>";
            } else {
                echo "<br>Admin user already exists.<br>";
            }
        } else {
            echo "<br>Users table doesn't exist. Please check your SQL file.<br>";
        }
    } catch (PDOException $e) {
        echo "<br>Error setting up admin user: " . $e->getMessage() . "<br>";
    }
    
    echo "<br>Database setup completed successfully!<br>";
    echo "You can now use the Baso Alam website. <a href='login-admin.php'>Login to admin dashboard</a>";
    
} catch (PDOException $e) {
    echo "<div style='color:red; background-color:#ffeeee; padding:15px; border:1px solid #ffcccc; border-radius:5px; margin:20px;'>";
    echo "<h2>Connection failed</h2>";
    echo "<p><strong>Error message:</strong> " . $e->getMessage() . "</p>";
    echo "<h3>Troubleshooting Tips:</h3>";
    echo "<ol>";
    echo "<li>Pastikan MySQL server sudah berjalan di XAMPP Control Panel</li>";
    echo "<li>Coba restart XAMPP dengan 'Run as Administrator'</li>";
    echo "<li>Jika port 3306 sudah digunakan oleh aplikasi lain, ubah port MySQL di XAMPP atau hentikan aplikasi lain</li>";
    echo "<li>Pastikan tidak ada firewall yang memblokir koneksi</li>";
    echo "</ol>";
    echo "<p><a href='https://stackoverflow.com/questions/10892689/cant-connect-to-mysql-server-on-localhost-10061-after-installation' target='_blank'>Bantuan lebih lanjut</a></p>";
    echo "</div>";
} catch (Exception $e) {
    echo "Error: " . $e->getMessage();
} 