<?php
/**
 * Fresh Database Migration Script
 * 
 * This script will drop the existing database (if it exists) and recreate it from scratch
 * along with all required tables and sample data for Baso Alam website.
 */

// 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
$dbname = 'baso_alam_db';

// 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 "<div style='font-family: Arial, sans-serif; max-width: 800px; margin: 0 auto; padding: 20px;'>";
    echo "<h1>Database Fresh Migration</h1>";
    echo "<div style='background-color: #f0f0f0; padding: 15px; border-radius: 5px; margin-bottom: 20px;'>";
    echo "<p>Connected to MySQL server successfully!</p>";
    
    // Drop database if it exists
    $pdo->exec("DROP DATABASE IF EXISTS $dbname");
    echo "<p>Existing database dropped successfully.</p>";
    echo "</div>";
    
    // Read SQL file
    $sql = file_get_contents($sql_file);
    if (!$sql) {
        throw new Exception("Error reading the SQL file: $sql_file");
    }
    
    echo "<div style='background-color: #e8f5e9; padding: 15px; border-radius: 5px; margin-bottom: 20px;'>";
    echo "<h2>Creating Fresh Database</h2>";
    
    // 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 "<p style='margin: 5px 0;'><span style='color: #4caf50; font-weight: bold;'>✓</span> Executed: " . substr(htmlspecialchars($statement), 0, 80) . "...</p>";
            } catch (PDOException $e) {
                echo "<p style='color: #f44336;'>Error executing statement: " . htmlspecialchars($statement) . "</p>";
                echo "<p style='color: #f44336;'>Error message: " . $e->getMessage() . "</p>";
                
                // Check if it's a duplicate entry error for admin user (which is fine)
                if (strpos($e->getMessage(), 'Duplicate entry') !== false && strpos($statement, 'admin') !== false) {
                    echo "<p style='color: #ff9800;'>Note: Admin user already exists, continuing migration.</p>";
                }
            }
        }
    }
    echo "</div>";
    
    // Create admin user if it doesn't exist
    try {
        // Connect to the newly created database
        $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;
        
        echo "<div style='background-color: #e3f2fd; padding: 15px; border-radius: 5px; margin-bottom: 20px;'>";
        echo "<h2>Admin User Setup</h2>";
        
        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 "<p style='color: #2196f3;'>Admin user created successfully!</p>";
                echo "<p>Username: <strong>" . $admin_username . "</strong></p>";
                echo "<p>Password: <strong>" . $admin_password . "</strong></p>";
            } else {
                echo "<p>Admin user already exists.</p>";
            }
        } else {
            echo "<p style='color: #f44336;'>Users table doesn't exist. Please check your SQL file.</p>";
        }
        echo "</div>";
        
        // Update hash in SQL file to match the expected password
        $new_hash = password_hash($admin_password, PASSWORD_DEFAULT);
        $sql_content = file_get_contents($sql_file);
        $pattern = '/\$2y\$10\$[a-zA-Z0-9\/.]{53}/'; // Pattern to match bcrypt hash
        if (preg_match($pattern, $sql_content)) {
            $updated_sql = preg_replace($pattern, $new_hash, $sql_content);
            file_put_contents($sql_file, $updated_sql);
            echo "<div style='background-color: #fff3e0; padding: 15px; border-radius: 5px; margin-bottom: 20px;'>";
            echo "<h2>SQL File Update</h2>";
            echo "<p>SQL file updated with correct password hash.</p>";
            echo "</div>";
        }
        
        echo "<div style='background-color: #e8f5e9; padding: 20px; border-radius: 5px; text-align: center;'>";
        echo "<h2 style='color: #2e7d32;'>Database migration completed successfully!</h2>";
        echo "<p>You can now use the Baso Alam website.</p>";
        echo "<div style='margin-top: 20px;'>";
        echo "<a href='login-admin.php' style='background-color: #4caf50; color: white; padding: 10px 15px; text-decoration: none; border-radius: 4px; margin-right: 10px;'>Login to Admin Dashboard</a>";
        echo "<a href='dashboard.php' style='background-color: #2196f3; color: white; padding: 10px 15px; text-decoration: none; border-radius: 4px;'>Go to Website</a>";
        echo "</div></div>";
    } catch (PDOException $e) {
        echo "<div style='background-color: #ffebee; padding: 15px; border-radius: 5px;'>";
        echo "<h2 style='color: #c62828;'>Error setting up admin user</h2>";
        echo "<p>" . $e->getMessage() . "</p>";
        echo "</div>";
    }
    
    echo "</div>";
    
} catch (PDOException $e) {
    echo "<div style='background-color: #ffebee; padding: 20px; border-radius: 5px; max-width: 800px; margin: 20px auto; font-family: Arial, sans-serif;'>";
    echo "<h2 style='color: #c62828;'>Connection failed</h2>";
    echo "<p><strong>Error message:</strong> " . $e->getMessage() . "</p>";
    echo "<h3>Troubleshooting Tips:</h3>";
    echo "<ol style='line-height: 1.6;'>";
    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' style='color: #2196f3;'>Bantuan lebih lanjut</a></p>";
    echo "</div>";
} catch (Exception $e) {
    echo "<div style='color: red; background-color: #ffebee; padding: 15px; border-radius: 5px; max-width: 800px; margin: 20px auto;'>";
    echo "Error: " . $e->getMessage();
    echo "</div>";
} 