<?php
include 'includes/config.php'; // Include the database connection file

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    try {
        // Begin transaction on both connections
        $conn->beginTransaction();
        $conn2->beginTransaction();

        // Get the form data
        $ref_no = $_POST['ref_no'];
        $FromBranch = $_POST['FromBranch'];
        $ToBranch = $_POST['ToBranch'];
        $TransferDate = $_POST['TransferDate'];
        $shipping_charges = $_POST['shipping_charges'];
        $sell_note = $_POST['sell_note'];

        $product_ids = json_decode($_POST['product_id']);
        $QuantityTransferred = json_decode($_POST['QuantityTransferred']);
        $purchase_prices = json_decode($_POST['purchase_price']);

        // Initialize an array to hold any errors
        $errors = [];

        // Define image source and destination directories
        $imageSourceDir = $_SERVER['DOCUMENT_ROOT'] . '/shalmi_pos/img/';
        $imageDestinationDir = $_SERVER['DOCUMENT_ROOT'] . '/nashtar_poss/img/'; // Adjusted for relative path

        // Ensure the destination directory exists
        if (!file_exists($imageDestinationDir)) {
            mkdir($imageDestinationDir, 0777, true); // Create the directory if it doesn't exist
        }

        // Loop over each product ID
        foreach ($product_ids as $index => $product_id) {
            // Step 1: Check if product exists in conn database
            $productCheckQuery = $conn->prepare("SELECT `id`, `name`, `business_id`, `type`, `unit_id`, `category_id`, `sub_category_id`, `created_by`, `created_at`, `image` FROM `products` WHERE `id` = ?");
            $productCheckQuery->execute([$product_id]);
            $productConn = $productCheckQuery->fetch(PDO::FETCH_ASSOC);

            // Check if the product exists in the conn database
            if ($productConn) {
                // Insert into stocktransfer table first
                $stmt = $conn->prepare("
                    INSERT INTO stocktransfer 
                    (product_id, QuantityTransferred, purchase_price, FromBranch, ToBranch, TransferDate, ref_no, shipping_charges, sell_note) 
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
                ");
                $stmt->execute([
                    $product_id,
                    $QuantityTransferred[$index],
                    $purchase_prices[$index],
                    $FromBranch,
                    $ToBranch,
                    $TransferDate,
                    $ref_no,
                    $shipping_charges,
                    $sell_note
                ]);

                // Step 2: Check if product exists in conn2 with matching values
                $productCheckQuery2 = $conn2->prepare("
                    SELECT `id` FROM `products` 
                    WHERE `name` = ? 
                    AND `unit_id` = ? 
                    AND `category_id` = ? 
                    AND `sub_category_id` = ?
                ");
                $productCheckQuery2->execute([
                    $productConn['name'],
                    $productConn['unit_id'],
                    $productConn['category_id'],
                    $productConn['sub_category_id']
                ]);
                $productConn2 = $productCheckQuery2->fetch(PDO::FETCH_ASSOC);

                // If the product exists in both databases
                if ($productConn2) {
                    // Insert into stockreceive and purchase_lines
                    try {
                        $stmt2 = $conn2->prepare("
                            INSERT INTO stockreceive 
                            (ref_no, product_id, QuantityReceived, purchase_price, shipping_charges, FromBranch, ToBranch, sell_note, TransferDate) 
                            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
                        ");
                        $stmt2->execute([
                            $ref_no,
                            $productConn2['id'],
                            $QuantityTransferred[$index],
                            $purchase_prices[$index],
                            $shipping_charges,
                            $FromBranch,
                            $ToBranch,
                            $sell_note,
                            $TransferDate
                        ]);

                        $stmt3 = $conn2->prepare("
                            INSERT INTO purchase_lines 
                            (product_id, quantity, pp_without_discount, purchase_price, purchase_date, ref_no, purchase_status) 
                            VALUES (?, ?, ?, ?, ?, ?, ?)
                        ");
                        $stmt3->execute([
                            $productConn2['id'],
                            $QuantityTransferred[$index],
                            $purchase_prices[$index],
                            $purchase_prices[$index],
                            $TransferDate,
                            $ref_no,
                            'Received Stock'
                        ]);
                    } catch (Exception $e) {
                        $errors[] = "Error inserting for Product ID $product_id: " . $e->getMessage();
                    }
                } else {
                    // If product does not exist in conn2, insert it
                    try {
                        // Check if the image exists and copy it
                        $imagePath = $imageSourceDir . $productConn['image']; // Full path to the image
                        if (file_exists($imagePath)) {
                            // Define the destination path
                            $newImagePath = $imageDestinationDir . $productConn['image'];
                            // Copy the image to the new location
                            copy($imagePath, $newImagePath);
                            // Set the new image path for the database
                            $newImageName = $productConn['image']; // Store relative path for the image
                        } else {
                            $newImageName = null; // Handle as needed if the image doesn't exist
                        }

                        $insertProductStmt = $conn2->prepare("
                            INSERT INTO products 
                            (`name`, `business_id`, `type`, `unit_id`, `category_id`, `sub_category_id`, `created_by`, `created_at`, `image`) 
                            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
                        ");
                        $insertProductStmt->execute([
                            $productConn['name'],
                            $productConn['business_id'],
                            $productConn['type'],
                            $productConn['unit_id'],
                            $productConn['category_id'],
                            $productConn['sub_category_id'],
                            $productConn['created_by'],
                            $productConn['created_at'],
                            $newImageName
                        ]);

                        // Get the last inserted product ID from conn2
                        $lastInsertProductId = $conn2->lastInsertId();

                        // Fetch variation details from conn
                        $variationDetailsQuery = $conn->prepare("SELECT * FROM `variations` WHERE `product_id` = ?");
                        $variationDetailsQuery->execute([$product_id]);
                        $variationDetails = $variationDetailsQuery->fetch(PDO::FETCH_ASSOC);

                        // Insert variation into conn2 `variations` table
                        $insertVariationStmt = $conn2->prepare("
                            INSERT INTO variations 
                            (`product_id`, `sub_sku`, `default_purchase_price`, `default_sell_price`, `dpp_inc_tax`, `sell_price_inc_tax`, `created_at`) 
                            VALUES (?, ?, ?, ?, ?, ?, ?)
                        ");
                        $insertVariationStmt->execute([
                            $lastInsertProductId,
                            $variationDetails['sub_sku'] ?? null,
                            $variationDetails['default_purchase_price'] ?? null,
                            $variationDetails['default_sell_price'] ?? null,
                            $variationDetails['dpp_inc_tax'] ?? null,
                            $variationDetails['sell_price_inc_tax'] ?? null,
                            $variationDetails['created_at'] ?? null
                        ]);

                        // Now that we have the product in conn2, insert into stockreceive and purchase_lines
                        $stmt2 = $conn2->prepare("
                            INSERT INTO stockreceive 
                            (ref_no, product_id, QuantityReceived, purchase_price, shipping_charges, FromBranch, ToBranch, sell_note, TransferDate) 
                            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
                        ");
                        $stmt2->execute([
                            $ref_no,
                            $lastInsertProductId,
                            $QuantityTransferred[$index],
                            $purchase_prices[$index],
                            $shipping_charges,
                            $FromBranch,
                            $ToBranch,
                            $sell_note,
                            $TransferDate
                        ]);

                        $stmt3 = $conn2->prepare("
                            INSERT INTO purchase_lines 
                            (product_id, quantity, pp_without_discount, purchase_price, purchase_date, ref_no, purchase_status) 
                            VALUES (?, ?, ?, ?, ?, ?, ?)
                        ");
                        $stmt3->execute([
                            $lastInsertProductId,
                            $QuantityTransferred[$index],
                            $purchase_prices[$index],
                            $purchase_prices[$index],
                            $TransferDate,
                            $ref_no,
                            'Received Stock'
                        ]);
                    } catch (Exception $e) {
                        $errors[] = "Error inserting new product for Product ID $product_id: " . $e->getMessage();
                    }
                }
            } else {
                $errors[] = "Product ID $product_id not found in the conn database.";
            }
        }

        // Commit transactions for conn
        $conn->commit();
        if (empty($errors)) {
            $conn2->commit();
        } else {
            $conn2->rollBack();
        }

        // Check for any errors and return the response
        if (empty($errors)) {
            echo json_encode(['status' => 'success', 'message' => 'Data transferred successfully']);
        } else {
            echo json_encode(['status' => 'error', 'errors' => $errors]);
        }
    } catch (Exception $e) {
        $conn->rollBack();
        $conn2->rollBack();
        echo json_encode(['status' => 'error', 'message' => 'Transaction failed: ' . $e->getMessage()]);
    }
} else {
    echo json_encode(['status' => 'error', 'message' => 'Invalid request method.']);
}
?>
