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

echo "=== Backend Verification Test ===\n";

try {
    // 1. Check Colors Table
    echo "1. Querying colors from database...\n";
    $colors = $pdo->query("SELECT * FROM colors ORDER BY name ASC")->fetchAll();
    echo "   Found " . count($colors) . " colors in database.\n";
    foreach ($colors as $c) {
        echo "   - Color #{$c['id']}: {$c['name']} ({$c['hex']})\n";
    }

    // 2. Check Sizes Table
    echo "2. Querying sizes from database...\n";
    $sizes = $pdo->query("SELECT * FROM sizes ORDER BY id ASC")->fetchAll();
    echo "   Found " . count($sizes) . " sizes in database.\n";
    foreach ($sizes as $s) {
        echo "   - Size #{$s['id']}: {$s['name']}\n";
    }

    // 3. Check Product Associations
    echo "3. Querying product-attribute associations...\n";
    $prod_id = 1; // Oversized Blazer is seeded as ID 1
    $stmt_colors = $pdo->prepare("SELECT c.* FROM colors c JOIN product_colors pc ON c.id = pc.color_id WHERE pc.product_id = ?");
    $stmt_colors->execute([$prod_id]);
    $prod_colors = $stmt_colors->fetchAll();
    echo "   Product #{$prod_id} has " . count($prod_colors) . " colors assigned.\n";

    $stmt_sizes = $pdo->prepare("SELECT s.* FROM sizes s JOIN product_sizes ps ON s.id = ps.size_id WHERE ps.product_id = ?");
    $stmt_sizes->execute([$prod_id]);
    $prod_sizes = $stmt_sizes->fetchAll();
    echo "   Product #{$prod_id} has " . count($prod_sizes) . " sizes assigned.\n";

    // 4. Mock Order Placement & order_items insert
    echo "4. Simulating order_items checkout write with custom attributes...\n";
    $pdo->beginTransaction();
    
    // Create guest user
    $stmt_user = $pdo->prepare("INSERT INTO users (full_name, email, password, address) VALUES ('Test Patron', 'testpatron@example.com', 'test', '123 Luxury Dr')");
    $stmt_user->execute();
    $user_id = $pdo->lastInsertId();

    // Create pending order
    $stmt_order = $pdo->prepare("INSERT INTO orders (user_id, total_amount, payment_method, status) VALUES (?, ?, ?, 'pending')");
    $stmt_order->execute([$user_id, 9999.00, 'COD']);
    $order_id = $pdo->lastInsertId();

    // Insert order item with color and size
    $stmt_item = $pdo->prepare("INSERT INTO order_items (order_id, product_id, quantity, price, color, size) VALUES (?, ?, ?, ?, ?, ?)");
    $stmt_item->execute([$order_id, $prod_id, 1, 9999.00, 'Emerald Green', 'XXL']);
    $item_id = $pdo->lastInsertId();
    echo "   Successfully inserted order item #{$item_id} (Emerald Green, XXL) into order_items!\n";

    // Verify it reads back correctly
    echo "5. Verifying order receipt fetch query...\n";
    $stmt_receipt = $pdo->prepare("SELECT oi.*, p.name FROM order_items oi LEFT JOIN products p ON oi.product_id = p.id WHERE oi.order_id = ?");
    $stmt_receipt->execute([$order_id]);
    $items = $stmt_receipt->fetchAll();
    
    foreach ($items as $item) {
        echo "   Read order item: {$item['name']} - Size: {$item['size']} - Color: {$item['color']} - Price: LKR {$item['price']}\n";
    }

    // Rollback so we don't pollute the production database
    $pdo->rollBack();
    echo "   Mock checkout successfully rolled back! Database clean.\n";

    echo "=== ALL BACKEND TESTS PASSED SUCCESSFULLY! ===\n";

} catch (Exception $e) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }
    echo "!!! BACKEND TEST FAILED: " . $e->getMessage() . "\n";
}
?>
