<?php
/**
 * Database Migration Script with Repair Functionality
 * 
 * This script will recreate database tables if they are in an inconsistent state
 */

// Database credentials
$host = '127.0.0.1';
$port = 3306;
$user = 'root';
$password = '';
$dbname = 'baso_alam_db';

// Default admin credentials
$admin_username = 'admin';
$admin_password = 'admin123';
$admin_name = 'Administrator';
$admin_email = 'admin@basoalam.com';

echo "<div style='font-family: Arial, sans-serif; max-width: 800px; margin: 0 auto; padding: 20px;'>";
echo "<h1>Database Repair and Migration</h1>";

try {
    // Connect to MySQL server
    $pdo = new PDO("mysql:host=$host;port=$port", $user, $password, array(
        PDO::ATTR_TIMEOUT => 5,
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    ));
    
    echo "<div style='background-color: #f0f0f0; padding: 15px; border-radius: 5px; margin-bottom: 20px;'>";
    echo "<p>Connected to MySQL server successfully!</p>";
    
    // Drop and recreate the database
    $pdo->exec("DROP DATABASE IF EXISTS $dbname");
    echo "<p>Existing database dropped.</p>";
    
    $pdo->exec("CREATE DATABASE $dbname");
    echo "<p>Fresh database '$dbname' created successfully.</p>";
    echo "</div>";
    
    // Connect to the new database
    $pdo = new PDO("mysql:host=$host;port=$port;dbname=$dbname", $user, $password, array(
        PDO::ATTR_TIMEOUT => 5,
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    ));
    
    echo "<div style='background-color: #e8f5e9; padding: 15px; border-radius: 5px; margin-bottom: 20px;'>";
    echo "<h2>Creating Tables</h2>";
    
    // Array of table create statements
    $tables = [
        'users' => "CREATE TABLE users (
            user_id INT AUTO_INCREMENT PRIMARY KEY,
            username VARCHAR(50) NOT NULL UNIQUE,
            password VARCHAR(255) NOT NULL,
            name VARCHAR(100) NOT NULL,
            email VARCHAR(100) NOT NULL UNIQUE,
            phone VARCHAR(20),
            address TEXT,
            role ENUM('customer', 'admin') DEFAULT 'customer',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        )",
        
        'products' => "CREATE TABLE products (
            product_id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            description TEXT,
            price DECIMAL(10, 2) NOT NULL,
            stock INT DEFAULT 0,
            image VARCHAR(255),
            is_active BOOLEAN DEFAULT TRUE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        )",
        
        'orders' => "CREATE TABLE orders (
            order_id INT AUTO_INCREMENT PRIMARY KEY,
            order_code VARCHAR(50) NOT NULL UNIQUE,
            user_id INT,
            name VARCHAR(100) NOT NULL,
            email VARCHAR(100) NOT NULL,
            phone VARCHAR(20) NOT NULL,
            address TEXT NOT NULL,
            total_amount DECIMAL(10, 2) NOT NULL,
            status ENUM('pending', 'paid', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
            payment_method VARCHAR(50) DEFAULT NULL,
            midtrans_order_id VARCHAR(100) DEFAULT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE SET NULL
        )",
        
        'order_items' => "CREATE TABLE order_items (
            item_id INT AUTO_INCREMENT PRIMARY KEY,
            order_id INT NOT NULL,
            product_id INT,
            product_name VARCHAR(100) NOT NULL,
            product_price DECIMAL(10, 2) NOT NULL,
            quantity INT NOT NULL,
            subtotal DECIMAL(10, 2) NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
            FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE SET NULL
        )",
        
        'transactions' => "CREATE TABLE transactions (
            transaction_id INT AUTO_INCREMENT PRIMARY KEY,
            order_id INT NOT NULL,
            payment_type VARCHAR(50) NOT NULL,
            transaction_time DATETIME NOT NULL,
            amount DECIMAL(10, 2) NOT NULL,
            midtrans_transaction_id VARCHAR(100) DEFAULT NULL,
            midtrans_transaction_status VARCHAR(50) DEFAULT NULL,
            midtrans_payment_type VARCHAR(50) DEFAULT NULL,
            additional_info TEXT DEFAULT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE
        )"
    ];
    
    // Create each table
    foreach ($tables as $table_name => $create_statement) {
        try {
            $pdo->exec($create_statement);
            echo "<p style='margin: 5px 0;'><span style='color: #4caf50; font-weight: bold;'>✓</span> Table '$table_name' created successfully.</p>";
        } catch (PDOException $e) {
            echo "<p style='color: #f44336;'>Error creating table '$table_name': " . $e->getMessage() . "</p>";
        }
    }
    echo "</div>";
    
    // Insert sample products
    echo "<div style='background-color: #fff3e0; padding: 15px; border-radius: 5px; margin-bottom: 20px;'>";
    echo "<h2>Sample Data</h2>";
    
    // Sample products
    $sample_products = [
        [
            'name' => 'Baso BE 50',
            'description' => 'Baso BE 50 adalah baso ukuran kecil dengan harga murah, dengan daging sapi murni dan nyaman di mulut',
            'price' => 15000,
            'stock' => 100,
            'image' => 'assets/baso-1.jpeg'
        ],
        [
            'name' => 'Baso BE 100',
            'description' => 'Baso BE 100 adalah baso ukuran sedang dengan rasa yang lezat, dengan daging sapi pilihan berkualitas',
            'price' => 20000,
            'stock' => 75,
            'image' => 'assets/baso-2.jpeg'
        ],
        [
            'name' => 'Baso BE 200',
            'description' => 'Baso BE 200 adalah baso ukuran besar dengan tekstur yang sempurna, dibuat dari daging sapi premium',
            'price' => 25000,
            'stock' => 50,
            'image' => 'assets/baso-3.jpeg'
        ]
    ];
    
    // Insert products
    $insert_query = "INSERT INTO products (name, description, price, stock, image) VALUES (:name, :description, :price, :stock, :image)";
    $stmt = $pdo->prepare($insert_query);
    
    foreach ($sample_products as $product) {
        $stmt->execute([
            ':name' => $product['name'],
            ':description' => $product['description'],
            ':price' => $product['price'],
            ':stock' => $product['stock'],
            ':image' => $product['image']
        ]);
        echo "<p style='margin: 5px 0;'><span style='color: #ff9800; font-weight: bold;'>+</span> Added product: " . $product['name'] . "</p>";
    }
    echo "</div>";
    
    // Create admin user
    echo "<div style='background-color: #e3f2fd; padding: 15px; border-radius: 5px; margin-bottom: 20px;'>";
    echo "<h2>Admin User Setup</h2>";
    
    // 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>";
    echo "</div>";
    
    // Success message
    echo "<div style='background-color: #e8f5e9; padding: 20px; border-radius: 5px; text-align: center;'>";
    echo "<h2 style='color: #2e7d32;'>Database repair and migration completed successfully!</h2>";
    echo "<p>A fresh database with all required tables has been created.</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: 20px; border-radius: 5px;'>";
    echo "<h2 style='color: #c62828;'>Database Error</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</li>";
    echo "<li>Pastikan username dan password database sudah benar</li>";
    echo "</ol>";
    echo "</div>";
} catch (Exception $e) {
    echo "<div style='background-color: #ffebee; padding: 20px; border-radius: 5px;'>";
    echo "<h2 style='color: #c62828;'>Error</h2>";
    echo "<p>" . $e->getMessage() . "</p>";
    echo "</div>";
}

echo "</div>"; // End of main container 