<?php
require_once __DIR__ . '/includes/db.php';

echo "Memulai inisialisasi database MySQL XAMPP (phpMyAdmin)...\n";

try {
    // 1. Buat tabel users
    $db->exec("CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(100) UNIQUE NOT NULL,
        password VARCHAR(255) NOT NULL,
        role VARCHAR(50) NOT NULL,
        name VARCHAR(100) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    echo "Tabel 'users' berhasil dibuat/diverifikasi.\n";

    // 2. Buat tabel categories
    $db->exec("CREATE TABLE IF NOT EXISTS categories (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) UNIQUE NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    echo "Tabel 'categories' berhasil dibuat/diverifikasi.\n";

    // 3. Buat tabel products
    $db->exec("CREATE TABLE IF NOT EXISTS products (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        description TEXT NOT NULL,
        price DECIMAL(12,2) NOT NULL,
        stock INT NOT NULL DEFAULT 0,
        image_url VARCHAR(255) DEFAULT NULL,
        category VARCHAR(100) NOT NULL,
        is_active TINYINT(1) DEFAULT 1
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    echo "Tabel 'products' berhasil dibuat/diverifikasi.\n";

    // 4. Buat tabel orders
    $db->exec("CREATE TABLE IF NOT EXISTS orders (
        id INT AUTO_INCREMENT PRIMARY KEY,
        customer_name VARCHAR(150) NOT NULL,
        customer_phone VARCHAR(50) NOT NULL,
        customer_address TEXT NOT NULL,
        total_price DECIMAL(12,2) NOT NULL,
        status VARCHAR(50) NOT NULL DEFAULT 'pending',
        payment_method VARCHAR(100) DEFAULT 'Transfer Bank (BCA)',
        payment_proof VARCHAR(255) DEFAULT NULL,
        payment_status VARCHAR(50) DEFAULT 'unverified',
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    echo "Tabel 'orders' berhasil dibuat/diverifikasi.\n";

    // 5. Buat tabel order_items
    $db->exec("CREATE TABLE IF NOT EXISTS order_items (
        id INT AUTO_INCREMENT PRIMARY KEY,
        order_id INT NOT NULL,
        product_id INT NOT NULL,
        quantity INT NOT NULL,
        price_at_purchase DECIMAL(12,2) NOT NULL,
        FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
        FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    echo "Tabel 'order_items' berhasil dibuat/diverifikasi.\n";

    // 6. Buat tabel stock_logs
    $db->exec("CREATE TABLE IF NOT EXISTS stock_logs (
        id INT AUTO_INCREMENT PRIMARY KEY,
        product_id INT NOT NULL,
        quantity_change INT NOT NULL,
        type VARCHAR(20) NOT NULL, -- 'in' atau 'out'
        notes TEXT DEFAULT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    echo "Tabel 'stock_logs' berhasil dibuat/diverifikasi.\n";

    // --- SEEDING DATA ---

    // Seed Admin User (username: admin, password: admin123)
    $admin_username = 'admin';
    $admin_check = $db->prepare("SELECT id FROM users WHERE username = ?");
    $admin_check->execute([$admin_username]);
    if (!$admin_check->fetch()) {
        $hashed_password = password_hash('admin123', PASSWORD_DEFAULT);
        $insert_admin = $db->prepare("INSERT INTO users (username, password, role, name) VALUES (?, ?, ?, ?)");
        $insert_admin->execute([$admin_username, $hashed_password, 'admin', 'Owner Edelys']);
        echo "User admin default berhasil dibuat (username: admin, pass: admin123).\n";
    }

    // Seed Categories
    $default_categories = ['Cake', 'Cheesecake', 'Crepe', 'Kue Basah', 'Kue Kering'];
    $insert_cat = $db->prepare("INSERT IGNORE INTO categories (name) VALUES (?)");
    foreach ($default_categories as $cat_name) {
        $insert_cat->execute([$cat_name]);
    }
    echo "Kategori default berhasil diverifikasi/disemai.\n";

    // Seed Products
    $products = [
        [
            'name' => 'Signature Chocolate Fudge Cake',
            'description' => 'Kue cokelat pekat nan lembap berlapis cokelat ganache premium yang lumer di mulut. Dibuat dengan cokelat Belgia asli 70% cacao untuk sensasi rasa pahit manis yang seimbang.',
            'price' => 285000,
            'stock' => 15,
            'image_url' => 'assets/images/chocolate_fudge.jpg',
            'category' => 'Cake'
        ],
        [
            'name' => 'Strawberry Shortcake',
            'description' => 'Kue spons vanilla Jepang yang sangat ringan, dipadukan dengan krim kocok segar premium dan potongan buah stroberi lokal pilihan yang manis nan segar di setiap lapisannya.',
            'price' => 260000,
            'stock' => 10,
            'image_url' => 'assets/images/strawberry_shortcake.jpg',
            'category' => 'Cake'
        ],
        [
            'name' => 'Premium Red Velvet Cake',
            'description' => 'Kue merah beludru yang lembut dan moist dengan cita rasa cokelat tipis, disajikan dengan frosting cream cheese premium yang gurih, lembut, dan sedikit asam segar.',
            'price' => 295000,
            'stock' => 8,
            'image_url' => 'assets/images/red_velvet.jpg',
            'category' => 'Cake'
        ],
        [
            'name' => 'Matcha Mille Crepes',
            'description' => 'Tumpukan lebih dari 20 lapis crepe super tipis rasa teh hijau, diselingi krim rasa teh hijau Uji Matcha Kyoto yang otentik. Memberikan tekstur lembut yang meleleh di lidah.',
            'price' => 275000,
            'stock' => 12,
            'image_url' => 'assets/images/matcha_crepes.jpg',
            'category' => 'Crepe'
        ],
        [
            'name' => 'Classic Tiramisu Cake',
            'description' => 'Kue tiramisu klasik ala Italia dengan ladyfinger yang direndam dalam espresso kental, dipadukan dengan krim keju mascarpone yang kaya dan taburan bubuk cokelat premium di atasnya.',
            'price' => 310000,
            'stock' => 6,
            'image_url' => 'assets/images/classic_tiramisu.jpg',
            'category' => 'Cake'
        ],
        [
            'name' => 'Classic New York Cheesecake',
            'description' => 'Cheesecake panggang bertekstur padat namun sangat lembut dan creamy, dipadukan dengan pinggiran renyah dari biskuit graham mentega, disajikan polos atau dengan saus buah.',
            'price' => 320000,
            'stock' => 5,
            'image_url' => 'assets/images/newyork_cheesecake.jpg',
            'category' => 'Cheesecake'
        ]
    ];

    $product_check = $db->query("SELECT COUNT(*) FROM products");
    if ($product_check->fetchColumn() == 0) {
        $insert_product = $db->prepare("INSERT INTO products (name, description, price, stock, image_url, category) VALUES (?, ?, ?, ?, ?, ?)");
        $insert_log = $db->prepare("INSERT INTO stock_logs (product_id, quantity_change, type, notes) VALUES (?, ?, ?, ?)");
        
        foreach ($products as $p) {
            $insert_product->execute([
                $p['name'],
                $p['description'],
                $p['price'],
                $p['stock'],
                $p['image_url'],
                $p['category']
            ]);
            $product_id = $db->lastInsertId();
            
            // Catat log stok awal
            $insert_log->execute([
                $product_id,
                $p['stock'],
                'in',
                'Stok awal saat inisialisasi database'
            ]);
        }
        echo "Produk default berhasil ditambahkan beserta log stok awal.\n";
    }

    echo "Inisialisasi database MySQL XAMPP selesai dengan sukses!\n";
} catch (PDOException $e) {
    die("Error inisialisasi database MySQL: " . $e->getMessage() . "\n");
}
?>
