<?php

require_once 'db.php';

if ($con->connect_error) {
    die(json_encode(['success' => false, 'message' => "Connection failed: " . $con->connect_error]));
}
if (isset($_POST['insert']) && $_POST['insert'] == 'update' && isset($_POST['id'])) {
    $id = intval($_POST['id']);
    if (isset($_POST['kt_docs_repeater_advanced']) && is_array($_POST['kt_docs_repeater_advanced'])) {
        $item = $_POST['kt_docs_repeater_advanced'][0];
        $name = isset($item['name']) ? $con->real_escape_string($item['name']) : '';
        $value = isset($item['value']) ? floatval($item['value']) : 0;
        $no_of_pack = '';
        if (isset($item['no_of_pack']) && is_array($item['no_of_pack'])) {
            $no_of_pack = $con->real_escape_string(implode(',', $item['no_of_pack']));
        } else if (isset($item['no_of_pack'])) {
            $no_of_pack = $con->real_escape_string($item['no_of_pack']);
        }
        $check_sql = "SELECT paid FROM package WHERE name = '$name' AND paid != $id";
        $check_result = $con->query($check_sql);
        if ($check_result->num_rows > 0) {
            echo "exists";
        } else {
            $sql = "UPDATE package SET name = '$name', value = '$value', no_of_pack = '$no_of_pack' WHERE paid = $id";
            if ($con->query($sql)) {
                echo "updated";
            } else {
                echo "error: " . $con->error;
            }
        }
    } else {
        echo "error: No valid form data received";
    }
    $con->close();
    exit;
}

elseif (isset($_POST['action']) && $_POST['action'] == 'insert_package') {
    $response = ['success' => true, 'message' => '', 'already_inserted' => 0, 'newly_inserted' => 0];
    $packageData = $_POST['package_data'];
    $con->begin_transaction();
    try {
        foreach ($packageData as $item) {
            $name = $con->real_escape_string($item['name']);
            $value = floatval($item['value']);
            $no_of_pack = is_array($item['no_of_pack']) ? implode(',', array_map([$con, 'real_escape_string'], $item['no_of_pack'])) : $con->real_escape_string($item['no_of_pack']);
            $check_sql = "SELECT COUNT(*) as count FROM package WHERE name = '$name'";
            $result = $con->query($check_sql);
            $row = $result->fetch_assoc();
            if ($row['count'] > 0) {
                $response['already_inserted']++;
            } else {
                $insert_sql = "INSERT INTO package (name, value, no_of_pack) VALUES ('$name', '$value', '$no_of_pack')";
                if (!$con->query($insert_sql)) {
                    throw new Exception("Error: " . $insert_sql . "<br>" . $con->error);
                }
                $response['newly_inserted']++;
            }
        }
        $con->commit();
        $response['message'] = "Package configuration processed. Newly inserted: {$response['newly_inserted']}, Already existed: {$response['already_inserted']}.";
    } catch (Exception $e) {
        $con->rollback();
        $response['success'] = false;
        $response['message'] = $e->getMessage();
    }
    $con->close();
    header('Content-Type: application/json');
    echo json_encode($response);
    exit;
}
elseif (isset($_POST['action']) && $_POST['action'] === "delete") {
    $id = intval($_POST['id']);
    $response = ["success" => false, "message" => ""];
    $checkQuery = "SELECT 
                        (SELECT COUNT(*) FROM product WHERE paid = $id) AS product_count, 
                        (SELECT COUNT(*) FROM bulkpackage WHERE FIND_IN_SET($id, bppaid)) AS bulkpackage_count";
    
    $checkResult = mysqli_query($con, $checkQuery);
    $row = mysqli_fetch_assoc($checkResult);
    if ($row['product_count'] > 0 || $row['bulkpackage_count'] > 0) {
        $response["message"] = "Cannot delete package. A related product or bulk package exists.";
    } else {
        mysqli_begin_transaction($con);
        $deleteQuery = "DELETE FROM package WHERE paid = $id";
        if (mysqli_query($con, $deleteQuery)) {
            mysqli_commit($con);
            $response["success"] = true;
            $response["message"] = "Package deleted successfully.";
        } else {
            mysqli_rollback($con);
            $response["message"] = "Error deleting package: " . mysqli_error($con);
        }
    }
    mysqli_close($con);
    header('Content-Type: application/json');
    echo json_encode($response);
    exit;
}
header('Content-Type: application/json');
echo json_encode(['success' => false, 'message' => 'Invalid action.']);
?>