<?php 
include 'includes/config.php';

if(isset($_POST['data'])){
    $rows = json_decode($_POST['data'], true);

    $business_id = 3;
    $type = 'single';
    $unit_id = '';
    $sub_category_id = '';
    $alert_quantity = '';
    $color_id = '';

    // Caches for performance
    $category_cache = [];
    $brand_cache = [];

    foreach($rows as $row){
        $product_name  = trim($row['product_name']);
        $brand_name    = trim($row['brand']);
        $category_name = trim($row['category']);
        $purchase_rate = floatval($row['purchase_price']);
        $sale_price    = floatval($row['sale_price']);
        $barcode_value = trim($row['barcode']);

        if($product_name == '' || $purchase_rate <= 0) continue;

        // ---------- CATEGORY ----------
        $category_id = null;
        if($category_name != ''){
            if(isset($category_cache[$category_name])){
                $category_id = $category_cache[$category_name];
            } else {
                $stmt = $conn->prepare("SELECT id FROM categories WHERE name = ? AND business_id = ?");
                $stmt->execute([$category_name, $business_id]);
                if($stmt->rowCount() > 0){
                    $category_id = $stmt->fetchColumn();
                } else {
                    $stmt = $conn->prepare("INSERT INTO categories (name, business_id) VALUES (?, ?)");
                    $stmt->execute([$category_name, $business_id]);
                    $category_id = $conn->lastInsertId();
                }
                $category_cache[$category_name] = $category_id;
            }
        }

        // ---------- BRAND ----------
        $brand_id = null;
        if($brand_name != ''){
            if(isset($brand_cache[$brand_name])){
                $brand_id = $brand_cache[$brand_name];
            } else {
                $stmt = $conn->prepare("SELECT id FROM brands WHERE name = ? AND business_id = ?");
                $stmt->execute([$brand_name, $business_id]);
                if($stmt->rowCount() > 0){
                    $brand_id = $stmt->fetchColumn();
                } else {
                    $stmt = $conn->prepare("INSERT INTO brands (name, business_id) VALUES (?, ?)");
                    $stmt->execute([$brand_name, $business_id]);
                    $brand_id = $conn->lastInsertId();
                }
                $brand_cache[$brand_name] = $brand_id;
            }
        }

        // ---------- PRODUCT DUPLICATE CHECK ----------
        $stmt = $conn->prepare("SELECT id FROM products WHERE name = ? AND business_id = ?");
        $stmt->execute([$product_name, $business_id]);
        if($stmt->rowCount() > 0){
            continue; // skip if product exists
        }

        // ---------- UNIQUE SKU ----------
        $unique_code = uniqid("PRD");

        // Use barcode from file or fallback to SKU/code
        $final_barcode = ($barcode_value != '') ? $barcode_value : $unique_code;

        // ---------- PRODUCT ----------
        $stmt = $conn->prepare("INSERT INTO products 
            (name, business_id, type, unit_id, brand_id, category_id, sub_category_id, alert_quantity, sku, image, color_id, product_code, barcode)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

        $stmt->execute([
            $product_name,
            $business_id,
            $type,
            $unit_id,
            $brand_id,
            $category_id,
            $sub_category_id,
            $alert_quantity,
            $unique_code,
            '',
            $color_id,
            $unique_code,
            $final_barcode
        ]);

        $product_id = $conn->lastInsertId();

        // ---------- VARIATION ----------
        $stmt = $conn->prepare("INSERT INTO variations
            (name, product_id, sub_sku, product_variation_id, default_purchase_price, dpp_inc_tax, profit_percent, default_sell_price, sell_price_inc_tax)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");

        $stmt->execute([
            'Default',
            $product_id,
            $unique_code,
            $product_id,
            $purchase_rate,
            $purchase_rate,
            0,
            $sale_price,
            $sale_price
        ]);
    }

    echo "Products, brands, categories, and variations imported successfully.";
}
?>
