<?php
// Include your database connection
require_once 'db.php';

// Function to sanitize input data
function sanitize($con, $data) {
    return mysqli_real_escape_string($con, trim($data));
}

// Check if the request is AJAX or regular form submission
$isAjax = !empty($_SERVER['HTTP_X_REQUESTED_WITH']) && strtolower($_SERVER['HTTP_X_REQUESTED_WITH']) == 'xmlhttprequest';

// Initialize response array for AJAX
$response = array(
    'status' => 'error',
    'message' => 'An error occurred while processing the request.'
);

// Get form data
$action = isset($_POST['action']) ? sanitize($con, $_POST['action']) : '';
$productName = isset($_POST['pname']) ? sanitize($con, $_POST['pname']) : '';
$productDesc = isset($_POST['pdes']) ? sanitize($con, $_POST['pdes']) : '';
$productType = isset($_POST['ptype']) ? sanitize($con, $_POST['ptype']) : '';

$formType = isset($_POST['ftype']) ? sanitize($con, $_POST['ftype']) : '';
$productpackage = '';
$packType = '';
$bpid = '';

// Check product type
if ($productType === "Packaging") {
    $productpackage = isset($_POST['productpackage']) ? sanitize($con, $_POST['productpackage']) : '';
    $packType = '';
    $bpid = '';
} elseif ($productType === "Finished Goods") {
    $productpackage = '';
    $packType = isset($_POST['paid']) ? sanitize($con, $_POST['paid']) : '';
    $bpid = isset($_POST['bpid']) ? sanitize($con, $_POST['bpid']) : '';
} else {
    // For other product types, keep all fields empty
    $productpackage = '';
    $packType = '';
    $bpid = '';
}

$hsncode = isset($_POST['hsncode']) ? sanitize($con, $_POST['hsncode']) : '';

if($action == 'Insert') {

    if (empty($productName) || empty($productType)) {
        $response['message'] = 'Please fill in all required fields.';
        if ($isAjax) {
            echo json_encode($response);
            exit;
        } else {
            header('Location: add_product.php?error=missing_fields');
            exit;
        }
    }

    // Handle thumbnail image upload
    $thumbnail = '';
    if (isset($_FILES['img']) && $_FILES['img']['error'] == 0) {
        $allowed = array('jpg', 'jpeg', 'png');
        $filename = $_FILES['img']['name'];
        $ext = pathinfo($filename, PATHINFO_EXTENSION);

        if (in_array(strtolower($ext), $allowed)) {
            $newFilename = uniqid() . '.' . $ext;
            $uploadDir = 'products/';

            if (!file_exists($uploadDir)) {
                mkdir($uploadDir, 0777, true);
            }

            $uploadPath = $uploadDir . $newFilename;

            if (move_uploaded_file($_FILES['img']['tmp_name'], $uploadPath)) {
                $thumbnail = $newFilename;
            }
        }
    }

    // Start transaction
    mysqli_begin_transaction($con);

    // Insert into product table
    $sql = "INSERT INTO product (pname, pdes, pthumb, ptype, productpackage, pform, paid, bpid, hsncode)
            VALUES ('$productName', '$productDesc', '$thumbnail', '$productType', '$productpackage', '$formType', '$packType', '$bpid', '$hsncode')";
    
    $result = mysqli_query($con, $sql);

    if ($result) {
        $productId = mysqli_insert_id($con);

        // Process variations
        if (isset($_POST['kt_ecommerce_add_product_options']) && is_array($_POST['kt_ecommerce_add_product_options'])) {
            foreach ($_POST['kt_ecommerce_add_product_options'] as $variation) {
                if (!empty($variation['variation3']) && !empty($variation['variation4'])) {
                    $variationName = sanitize($con, $variation['variation3']);
        
                    // Handle multiple values
                    $variationValues = [];
        
                    if (is_array($variation['variation4'])) {
                        $variationValues = $variation['variation4'];
                    } else {
                        $variationValues = explode(',', $variation['variation4']);
                    }
        
                    foreach ($variationValues as $value) {
                        $variationValue = sanitize($con, trim($value));
                        $varSql = "INSERT INTO variation (pid, vname, vvalue) VALUES ($productId, '$variationName', '$variationValue')";
                        mysqli_query($con, $varSql);
                    }
        
                } else {
                    // Fallback to brand and packaging names
                    $bpidName = '';
                    $paidNames = [];

                    // Get bulk package name
                    $bpidQuery = mysqli_query($con, "SELECT name FROM bulkpackage WHERE bpid = '$bpid'");
                    if ($bpidRow = mysqli_fetch_assoc($bpidQuery)) {
                        $bpidName = $bpidRow['name'];
                    }

                    // Explode paid values and fetch names
                    $paidArray = explode(',', $packType);
                    foreach ($paidArray as $paidVal) {
                        $paidVal = trim($paidVal);
                        $paidQuery = mysqli_query($con, "SELECT name FROM package WHERE paid = '$paidVal'");
                        if ($paidRow = mysqli_fetch_assoc($paidQuery)) {
                            $paidNames[] = $paidRow['name'];
                        }
                    }

                    // Combine all package names into a single string
                    $variationValue = sanitize($con, implode(',', $paidNames));
                    $variationName = sanitize($con, $bpidName);

                    // Insert single variation row with combined value
                    $varSql = "INSERT INTO variation (pid, vname, vvalue) VALUES ($productId, '$variationName', '$variationValue')";
                    mysqli_query($con, $varSql);

                }
            }
        }
        
    


        // Handle gallery images
        if (isset($_FILES['img2']) && !empty($_FILES['img2']['name'][0])) {
            $galleryUploadDir = 'products/';

            if (!file_exists($galleryUploadDir)) {
                mkdir($galleryUploadDir, 0777, true);
            }

            $allowed = array('jpg', 'jpeg', 'png');
            $fileCount = count($_FILES['img2']['name']);
            $maxFiles = 5;
            $fileCount = min($fileCount, $maxFiles);

            for ($i = 0; $i < $fileCount; $i++) {
                if ($_FILES['img2']['error'][$i] == 0) {
                    $filename = $_FILES['img2']['name'][$i];
                    $ext = pathinfo($filename, PATHINFO_EXTENSION);

                    if (in_array(strtolower($ext), $allowed)) {
                        $newFilename = uniqid() . '_gallery_' . $i . '.' . $ext;
                        $uploadPath = $galleryUploadDir . $newFilename;

                        if (move_uploaded_file($_FILES['img2']['tmp_name'][$i], $uploadPath)) {
                            $gallerySql = "INSERT INTO product_gallery (pid, pimage) VALUES ($productId, '$newFilename')";
                            mysqli_query($con, $gallerySql);
                        }
                    }
                }
            }
        }

        // Commit transaction
        mysqli_commit($con);

        $response['status'] = 'success';
        $response['message'] = 'Product added successfully with gallery images.';
        $response['pid'] = $productId;

        if ($isAjax) {
            echo json_encode($response);
            exit;
        } else {
            header('Location: products.php?success=product_added');
            exit;
        }
    } else {
        // Rollback transaction
        mysqli_rollback($con);

        $response['message'] = 'Database error: ' . mysqli_error($con);
        if ($isAjax) {
            echo json_encode($response);
            exit;
        } else {
            header('Location: add_product.php?error=db_error');
            exit;
        }
    }
}

elseif ($action === 'delete') {
    $productId = isset($_POST['pid']) ? sanitize($con, $_POST['pid']) : '';

    if (empty($productId)) {
        $response['message'] = 'Product ID is required for deletion.';
        echo json_encode($response);
        exit;
    }

    $checkQuery = "SELECT
                    (SELECT COUNT(*) FROM variation_multi WHERE pid = '$productId') as variation_multi_count,
                    (SELECT COUNT(*) FROM cus_product WHERE pid = '$productId') as cus_product_count";

    $checkResult = mysqli_query($con, $checkQuery);
    $checkRow = mysqli_fetch_assoc($checkResult);

    if ($checkRow['variation_multi_count'] > 0 || $checkRow['cus_product_count'] > 0) {
        $response['status'] = 'error';
        $response['message'] = 'Sorry, products are there in variation, variation_multi, or cus_product.';
        echo json_encode($response);
        exit;
    }

    mysqli_begin_transaction($con);

    try {
        // Delete associated images in product_gallery
        $galleryQuery = "SELECT pimage FROM product_gallery WHERE pid = '$productId'";
        $galleryResult = mysqli_query($con, $galleryQuery);
        while ($row = mysqli_fetch_assoc($galleryResult)) {
            $filePath = 'products/' . $row['pimage'];
            if (file_exists($filePath)) {
                unlink($filePath);
            }
        }
        mysqli_query($con, "DELETE FROM product_gallery WHERE pid = '$productId'");

        // Delete product thumbnail image
        $thumbQuery = "SELECT pthumb FROM product WHERE pid = '$productId'";
        $thumbResult = mysqli_query($con, $thumbQuery);
        $thumbRow = mysqli_fetch_assoc($thumbResult);
        
        if (!empty($thumbRow['pthumb'])) {
            $thumbPath = 'products/' . $thumbRow['pthumb'];
            if (file_exists($thumbPath)) {
                unlink($thumbPath);
            }
        }

        // Delete from product table
        $deleteProductQuery = "DELETE FROM product WHERE pid = '$productId'";
        if (mysqli_query($con, $deleteProductQuery)) {
            mysqli_commit($con);
            $response['status'] = 'success';
            $response['message'] = 'Product deleted successfully!';
        } else {
            throw new Exception('Database error: ' . mysqli_error($con));
        }
    } catch (Exception $e) {
        mysqli_rollback($con);
        $response['status'] = 'error';
        $response['message'] = $e->getMessage();
    }
    echo json_encode($response);
}elseif ($action === 'check_delete') {
    $productId = isset($_POST['pid']) ? sanitize($con, $_POST['pid']) : '';

    if (empty($productId)) {
        $response['status'] = 'error';
        $response['message'] = 'Product ID is required.';
        echo json_encode($response);
        exit;
    }

    $checkQuery = "SELECT 
                    (SELECT COUNT(*) FROM variation_multi WHERE pid = '$productId') as variation_multi_count,
                    (SELECT COUNT(*) FROM cus_product WHERE pid = '$productId') as cus_product_count";

    $checkResult = mysqli_query($con, $checkQuery);
    $checkRow = mysqli_fetch_assoc($checkResult);

    if ( $checkRow['variation_multi_count'] > 0 || $checkRow['cus_product_count'] > 0) {
        $response['status'] = 'error';
        $response['message'] = 'Sorry, this product cannot be deleted because it exists in variations or customer products.';
        echo json_encode($response);
        exit;
    }

    $response['status'] = 'success';
    echo json_encode($response);
    exit;
}

 else {
    $response['message'] = 'Invalid action.';
}
mysqli_close($con);
?>