<?php
require_once 'includes/db_connection.php';

// disable foreign key checks to avoid issues during setup/cleanup if needed
$pdo->exec("SET FOREIGN_KEY_CHECKS = 0");

function createCollection($pdo, $name, $slug, $parentId = null, $description = '') {
    // check if exists
    $stmt = $pdo->prepare("SELECT id FROM collections WHERE slug = ?");
    $stmt->execute([$slug]);
    $existing = $stmt->fetch();

    if ($existing) {
        echo "Collection '$name' ($slug) already exists (ID: {$existing['id']}). Updating parent.\n";
        if ($parentId) {
            $upd = $pdo->prepare("UPDATE collections SET parent_id = ? WHERE id = ?");
            $upd->execute([$parentId, $existing['id']]);
        }
        return $existing['id'];
    }

    // Logic to verify parent path if needed, but for now simple construction
    $url_path = $slug;
    if ($parentId) {
         $stmtP = $pdo->prepare("SELECT url_path FROM collections WHERE id = ?");
         $stmtP->execute([$parentId]);
         $parent = $stmtP->fetch();
         if ($parent) {
             $url_path = $parent['url_path'] . '/' . $slug;
         }
    }

    $stmt = $pdo->prepare("INSERT INTO collections (name, slug, parent_id, url_path, description, is_active, is_featured, created_at) VALUES (?, ?, ?, ?, ?, 1, 0, NOW())");
    $stmt->execute([$name, $slug, $parentId, $url_path, $description]);
    $id = $pdo->lastInsertId();
    echo "Created Collection: $name (ID: $id)\n";
    return $id;
}

function createProduct($pdo, $name, $slug, $price, $comparePrice, $collectionId, $image = 'placeholder.png') {
    // Simple check by slug
    $stmt = $pdo->prepare("SELECT id FROM products WHERE slug = ?");
    $stmt->execute([$slug]);
    $existing = $stmt->fetch();

    if ($existing) {
         // Link to collection if not linked
         linkProductToCollection($pdo, $existing['id'], $collectionId);
         return $existing['id'];
    }

    $stmt = $pdo->prepare("INSERT INTO products (title, slug, price, compare_at_price, description, status, created_at) VALUES (?, ?, ?, ?, ?, 'active', NOW())");
    $stmt->execute([$name, $slug, $price, $comparePrice, "Currently testing $name."]);
    $pid = $pdo->lastInsertId();

    // Add Image
    $stmtImg = $pdo->prepare("INSERT INTO product_images (product_id, image_path, is_featured, sort_order) VALUES (?, ?, 1, 0)");
    $stmtImg->execute([$pid, $image]);

    // Link
    linkProductToCollection($pdo, $pid, $collectionId);
    echo "  -> Created Product: $name\n";
    return $pid;
}

function linkProductToCollection($pdo, $pid, $cid) {
    if (!$pid || !$cid) return;
    $stmt = $pdo->prepare("SELECT * FROM product_collections WHERE product_id = ? AND collection_id = ?");
    $stmt->execute([$pid, $cid]);
    if (!$stmt->fetch()) {
        $ins = $pdo->prepare("INSERT INTO product_collections (product_id, collection_id) VALUES (?, ?)");
        $ins->execute([$pid, $cid]);
    }
}

// 0. Ensure Root Collections exist properly
$shopId = createCollection($pdo, 'Shop', 'shop', null, 'Main Shop Category');
$disposablesId = createCollection($pdo, 'Disposable Vapes', 'disposable-vapes', $shopId, 'Premium Disposable Vapes');
$juiceId = createCollection($pdo, 'Vape Juices', 'vape-juice', $shopId, 'Premium E-Liquids');
$pouchesId = createCollection($pdo, 'Nicotine Pouches', 'nicotine-pouches', $shopId, 'Tobacco-free Pouches');

// ==========================================
// 1. DISPOSABLES STRUCTURE
// ==========================================

// A. Section: BRAND (Virtual Collection acting as Header)
$dispBrandId = createCollection($pdo, 'Brand', 'disposables-brand', $disposablesId, 'Shop by Brand');

$brands = [
    ['name' => 'Adjust Vapes', 'slug' => 'adjust-vapes'],
    ['name' => 'EBCreate BC5000', 'slug' => 'ebcreate-bc5000'],
    ['name' => 'Flum Vapes', 'slug' => 'flum-vapes'],
    ['name' => 'Geek Bar', 'slug' => 'geek-bar'],
    ['name' => 'Juicy Bar', 'slug' => 'juicy-bar'],
    ['name' => 'Lost Mary', 'slug' => 'lost-mary'],
    ['name' => 'RAZ Vapes', 'slug' => 'raz-vapes'] // Use new slug
];

foreach ($brands as $b) {
    $cid = createCollection($pdo, $b['name'], $b['slug'], $dispBrandId);
    // Add 5 dummy products
    for ($i=1; $i<=5; $i++) {
        createProduct($pdo, "{$b['name']} Flavor $i", "{$b['slug']}-flavor-$i", 19.99, 24.99, $cid);
    }
}

// B. Section: PUFFS COUNT
$dispPuffsId = createCollection($pdo, 'Puffs Count', 'disposables-puffs', $disposablesId, 'Shop by Puffs');

$puffs = [
    ['name' => 'Up to 10,000 Puffs', 'slug' => 'long-lasting-10000-puffs-disposable-vapes'],
    ['name' => '15,000 - 35,000 Puffs', 'slug' => 'high-puff-disposable-vapes-15k-35k'],
    ['name' => '40,000 Puffs & Beyond', 'slug' => 'disposable-vapes-40k-beyond']
];

foreach ($puffs as $p) {
    $cid = createCollection($pdo, $p['name'], $p['slug'], $dispPuffsId);
    for ($i=1; $i<=3; $i++) {
        createProduct($pdo, "Long Lasting Vape $i ({$p['name']})", "puffs-{$p['slug']}-$i", 29.99, 35.00, $cid);
    }
}

// C. Section: NICOTINE LEVEL
$dispNicId = createCollection($pdo, 'Nicotine Level', 'disposables-nicotine', $disposablesId, 'Shop by Nicotine');

$nicLevels = [
    ['name' => 'Free Nicotine (0%)', 'slug' => 'free-nicotine-vapes'],
    ['name' => 'Low Nicotine (up to 3%)', 'slug' => 'low-nicotine-up-to-3'],
    ['name' => 'High Nicotine (5%+)', 'slug' => 'high-nicotine-5'],
    ['name' => 'Adjustable Nicotine Devices', 'slug' => 'adjustable-nicotine-devices']
];

foreach ($nicLevels as $n) {
    $cid = createCollection($pdo, $n['name'], $n['slug'], $dispNicId);
     for ($i=1; $i<=3; $i++) {
        createProduct($pdo, "Nicotine Vape $i ({$n['name']})", "nic-{$n['slug']}-$i", 15.99, 18.00, $cid);
    }
}

// ==========================================
// 2. VAPE JUICES STRUCTURE
// ==========================================
$juiceBrandId = createCollection($pdo, 'Brand', 'juice-brand', $juiceId);
$juiceBrands = [
    ['name' => 'Raz x Pod Juice', 'slug' => 'raz-brand-eliquid'], // slug modified slightly to be unique if needed
    ['name' => 'PulseLiq', 'slug' => 'geek-bar-eliquid'],
    ['name' => 'Oxbar x Pod Juice', 'slug' => 'oxbar-vapes-eliquid']
];
foreach ($juiceBrands as $j) {
    $cid = createCollection($pdo, $j['name'], $j['slug'], $juiceBrandId);
      for ($i=1; $i<=3; $i++) {
        createProduct($pdo, "{$j['name']} Juice $i", "juice-{$j['slug']}-$i", 12.99, 15.00, $cid);
    }
}

$juiceNicId = createCollection($pdo, 'Nicotine Type', 'juice-nicotine', $juiceId);
$juiceTypes = [
    ['name' => 'Freebase Nicotine', 'slug' => 'freebase'], // Updated for simplified URL
    ['name' => 'Nicotine Salt', 'slug' => 'nicotine-salt'],
    ['name' => 'Zero Nicotine (0%)', 'slug' => 'zero-nicotine']
];
foreach ($juiceTypes as $t) {
    createCollection($pdo, $t['name'], $t['slug'], $juiceNicId);
}

// ==========================================
// 3. SPECIAL COLLECTIONS (Shop Menu)
// ==========================================
createCollection($pdo, 'Vapes Under $10', 'under-10', $shopId);

echo "\nDatabase setup complete!\n";
$pdo->exec("SET FOREIGN_KEY_CHECKS = 1");
?>
