<?php

function fetch_product_items($conn_rjllearning_specific, $conn_rjllearning, $institution_id, $search_string, $from_date, $to_date, $date_created, $limit, $offset)
{

    try {
        $sql_count = "SELECT  count(*) as total_rows FROM product_items where institution_id =? AND status =?";
        $array_params = [$institution_id, 1];

        if ($search_string != null) {
            $sql_count .= " AND (product_name LIKE ?)";
            $array_params[] = '%' . $search_string . '%';
        }

        if ($date_created != null) {
            $sql_count .= " AND date_created = ?";
            $array_params[] = $date_created;
        }

        // Add date range condition
        if ($from_date != null && $to_date != null) {
            $sql_count .= " AND date_created BETWEEN ? AND ?";
            $array_params[] = $from_date;
            $array_params[] = $to_date;
        }

        $stmt = $conn_rjllearning_specific->prepare($sql_count);
        $stmt->execute($array_params);
        $result_total = $stmt->fetchAll();
        $totalrows = $result_total[0]['total_rows'];

        if ($limit == 'all') {

            $sql_item = "SELECT * FROM product_items where institution_id =? AND status =?";
            $array_params = [$institution_id, 1];

            if ($search_string != null) {
                $sql_item .= " AND (product_name LIKE ?)";
                $array_params[] = '%' . $search_string . '%';
            }

            if ($date_created != null) {
                $sql_item .= " AND date_created = ?";
                $array_params[] = $date_created;
            }

            // Add date range condition
            if ($from_date != null && $to_date != null) {
                $sql_item .= " AND date_created BETWEEN ? AND ?";
                $array_params[] = $from_date;
                $array_params[] = $to_date;
            }

            $sql_item .=  "order by date_created desc, time_created desc";
        } else {
            $sql_item = "SELECT * FROM  product_items where institution_id =? AND status =?";
            $array_params = [$institution_id, 1];

            if ($search_string != null) {
                $sql_item .= " AND (product_name LIKE ?)";
                $array_params[] = '%' . $search_string . '%';
            }

            if ($date_created != null) {
                $sql_item .= " AND date_created = ?";
                $array_params[] = $date_created;
            }

            // Add date range condition
            if ($from_date != null && $to_date != null) {
                $sql_item .= " AND date_created BETWEEN ? AND ?";
                $array_params[] = $from_date;
                $array_params[] = $to_date;
            }
            $sql_item .= " order by date_created desc, time_created desc ";
            $sql_item .= "LIMIT " . $offset . " ," . $limit . "";
        }

        $stmt = $conn_rjllearning_specific->prepare($sql_item);
        $stmt->execute($array_params);
        $session_rows = $stmt->fetchAll();

        $array_return = [];

        foreach ($session_rows as $rows) {

            $institution = getInstitution($conn_rjllearning, $rows['institution_id']);

            $sql_category = "SELECT id, category_name FROM items_category WHERE id = ? AND institution_id = ?";
            $stmtCategory = $conn_rjllearning_specific->prepare($sql_category);
            $stmtCategory->execute([$rows['category_id'], $rows['institution_id']]);
            $category_detail = $stmtCategory->fetch(PDO::FETCH_ASSOC); // Fetch as associative array

            if ($category_detail && is_array($category_detail)) {
                $category_details = [
                    "item_category_id" => $category_detail['id'],
                    "category_name" => $category_detail['category_name']
                ];
            }
            
            $inventory_query = "SELECT 
            COALESCE(SUM(CASE WHEN transaction_type = 'purchase' THEN quantity ELSE 0 END), 0) AS purchase_qty,
            COALESCE(SUM(CASE WHEN transaction_type = 'sales' THEN quantity ELSE 0 END), 0) AS sales_qty
            FROM inventory_transactions
            WHERE item_id = ? AND institution_id = ?";
                $inv_stmt = $conn_rjllearning_specific->prepare($inventory_query);
                $inv_stmt->execute([
                    $rows['id'],
                    $institution_id
                ]);

                $inv_row = $inv_stmt->fetch();

                $purchase_qty        = $inv_row['purchase_qty'] ?? 0;
                $sales_qty           = $inv_row['sales_qty'] ?? 0;

                // Compute current quantity
                $current_quantity = $purchase_qty - $sales_qty;

            $array_return[] = array(
                'item_id' => $rows['id'],
                'institution_id' => $institution,
                'product_name' => $rows['product_name'],
                'quantity' => $current_quantity,
                'category' => $category_details,
                'can_repeat' => $rows['can_repeat'],
                'item_code' => $rows['item_code'],
                'send_otp' => $rows['send_otp'],
                'sale_distribute' => $rows['sale_distribute'],
                'price' => $rows['price'],
                'added_by' => $rows['added_by'],
                'date_created' => $rows['date_created'],
                'time_created' => $rows['time_created'],
                'last_modified_by' => $rows['last_modified_by'],
                'last_modified_at' => $rows['last_modified_at'],
                'status' => $rows['status'],
            );
        }

        if (count($session_rows) < 50) {
            return array('total_rows' => $totalrows, 'items_info' => $array_return, 'maxreach' => 1);
        } else {
            return array('total_rows' => $totalrows, 'items_info' => $array_return, 'maxreach' => 0);
        }
    } catch (Exception $e) {
        errorreturn("Error: " . $e->getMessage());
        return;
    }
}

function get_specific_item($conn_rjllearning_specific, $institution_id, $item_id)
{
    try {
        // Query to fetch the specific product by ID
        $sql = "SELECT * FROM product_items WHERE institution_id = ? AND id = ? AND status = ?";
        $stmt = $conn_rjllearning_specific->prepare($sql);
        $stmt->execute([$institution_id, $item_id, 1]);
        $product = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$product) {
            return "item not found.";
        }

        $array_return = [];

        $sql_product = "SELECT id, category_name FROM items_category WHERE id = ? AND institution_id = ?";
        $stmtproduct = $conn_rjllearning_specific->prepare($sql_product);
        $stmtproduct->execute([$product['category_id'], $institution_id]);
        $product_detail = $stmtproduct->fetch(PDO::FETCH_ASSOC);

        if ($product_detail) {
            $product_details = [
                "item_category_id" => $product_detail['id'],
                "category_name" => $product_detail['category_name']
            ];
        }

        // Return category details
        $array_return = ([
            'product_id' => $product['id'],
            'institution_id' => $product['institution_id'],
            'product_name' => $product['product_name'],
            'category' => $product_details,
            'can_repeat' => $product['can_repeat'],
            'item_code' => $product['item_code'],
            'send_otp' => $product['send_otp'],
            'sale_distribute' => $product['sale_distribute'],
            'price' => $product['price'],
            'added_by' => $product['added_by'],
            'date_created' => $product['date_created'],
            'time_created' => $product['time_created'],
            'last_modified_by' => $product['last_modified_by'],
            'last_modified_at' => $product['last_modified_at'],
            'status' => $product['status'],
        ]);

        return  $array_return;
    } catch (Exception $e) {
        errorreturn("Error: " . $e->getMessage());
        return;
    }
}

function fetchProductItemsWithQuantity($institution_id, $branch_id, $conn_rjllearning_specific)
{
    try {
        $response = [];

        $query = "SELECT id AS item_id, institution_id, product_name, send_otp FROM product_items WHERE institution_id = ?";
        $stmt = $conn_rjllearning_specific->prepare($query);
        $stmt->execute([$institution_id]);
        $result = $stmt->fetchAll();

        if ($result) {
            foreach ($result as $row) {
                $item_id = $row['item_id'];
                $send_otp = $row['send_otp'];

                $inventory_query = "SELECT 
        COALESCE(SUM(CASE WHEN transaction_type = 'purchase' AND branch_id = ? THEN quantity ELSE 0 END), 0) AS purchase_qty,
        COALESCE(SUM(CASE WHEN transaction_type = 'sales' AND branch_id = ? THEN quantity ELSE 0 END), 0) AS sales_qty,
        COALESCE(SUM(CASE WHEN transaction_type = 'transfer' AND branch_id = ? THEN quantity ELSE 0 END), 0) AS transferred_out_qty,
        COALESCE(SUM(CASE WHEN transaction_type = 'transfer' AND second_party = ? THEN quantity ELSE 0 END), 0) AS transferred_in_qty
    FROM inventory_transactions
    WHERE item_id = ? AND institution_id = ?";
                $inv_stmt = $conn_rjllearning_specific->prepare($inventory_query);
                $inv_stmt->execute([
                    $branch_id,
                    $branch_id,
                    $branch_id,
                    $branch_id,
                    $item_id,
                    $institution_id
                ]);

                $inv_row = $inv_stmt->fetch();

                $purchase_qty        = $inv_row['purchase_qty'] ?? 0;
                $sales_qty           = $inv_row['sales_qty'] ?? 0;
                $transferred_out_qty = $inv_row['transferred_out_qty'] ?? 0;
                $transferred_in_qty  = $inv_row['transferred_in_qty'] ?? 0;

                // Compute current quantity
                $current_quantity = $purchase_qty + $transferred_in_qty - $sales_qty - $transferred_out_qty;


                $response[] = [
                    "item_id" => $item_id,
                    "institution_id" => $row['institution_id'],
                    "product_name" => $row['product_name'],
                    "current_quantity" => $current_quantity,
                    "send_otp" => $row['send_otp'],
                ];
            }
        }
        return $response;
    } catch (Exception $e) {
        errorreturn("Error: " . $e->getMessage());
        return;
    }
}


function get_vendor_student_Data($institution_id, $branch_id, $first_party_type, $search_string, $conn_rjllearning_specific,$limit = null)
{
    try {

        $array_params = [$institution_id, $branch_id];

        if ($first_party_type === "vendors") {
            $query = "SELECT DISTINCT vendor_name AS name, institution_id, branch_id 
                  FROM unmarked_transaction 
                  WHERE institution_id = ? AND branch_id = ? AND vendor_name NOT REGEXP '^[0-9]+$'";

            if ($search_string != null) {
                $query .= " AND vendor_name LIKE ?";
                $array_params[] = '%' . $search_string . '%';
            }
            
            if ($limit) {
                $query .= " LIMIT " . $limit;
            }

        } elseif ($first_party_type === "students") {
            $query = "SELECT id, institution_id, branch_id, admission_number , username , name, gender, fathername FROM students 
                  WHERE institution_id = ? AND branch_id = ? AND can_activate = 1";

            if ($search_string != null) {
                $query .= " AND (name LIKE ? OR username LIKE ? OR admission_number LIKE ? OR email LIKE ? or fathername LIKE ?)";
                $array_params[] = '%' . $search_string . '%';
                $array_params[] = '%' . $search_string . '%';
                $array_params[] = '%' . $search_string . '%';
                $array_params[] = '%' . $search_string . '%';
                $array_params[] = '%' . $search_string . '%';
            }
            
           if ($limit) {
                $query .= " LIMIT " . $limit;
            }
        } else {
            return "Enter valid first_party_type";
        }

        // Prepare & execute the query
        $stmt = $conn_rjllearning_specific->prepare($query);
        $stmt->execute($array_params);
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $array_return = [];

        foreach ($result as $rows) {
            if ($first_party_type === "vendors") {
                $array_return[] = array(
                    'institution_id' => $rows['institution_id'],
                    'branch_id' => $rows['branch_id'],
                    'vendor_name' => $rows['name']
                );
            } elseif ($first_party_type === "students") {
                $name = $rows['name'] ;
                $name = $rows['gender'] == 'M' ? $name . ' S/o ' : $name . ' D/o ';
                $name .= $rows['fathername'] . ' ' . $rows['admission_number'];
                $array_return[] = array(
                    'student_id' => $rows['id'],
                    'institution_id' => $rows['institution_id'],
                    'branch_id' => $rows['branch_id'],
                    'admission_number' => $rows['admission_number'],
                    'username' => $rows['username'],
                    'name' => $name,
                );
            } else {
                return "Enter a valid value for first_party_type";
            }
        }

        return $array_return;
    } catch (Exception $e) {
        errorreturn("Error: " . $e->getMessage());
        return;
    }
}

function getspecific_inventory_transaction($conn_rjllearning_specific, $institution_id, $branch_id, $transaction_id)
{
    try {
        // Step 1: Fetch transaction by ID
        $sql = "SELECT id, institution_id, branch_id, transaction_type, item_id, quantity, date, created_by, created_at, second_party, otp_authorisation, transfer_accepted_by, transfer_accepted_at
                FROM inventory_transactions WHERE id = ? AND institution_id = ?";
        $stmt = $conn_rjllearning_specific->prepare($sql);
        $stmt->execute([$transaction_id, $institution_id]);
        $trans_detail = $stmt->fetch(PDO::FETCH_ASSOC);

        // If no transaction found, return empty response
        if (!$trans_detail) {
            return ['error' => "Transaction not found"];
        }

        // Step 2: Fetch corresponding unmarked transaction
        $sql_unmarked = "SELECT id, amount, vendor_name, first_party_type, payment_bool, payment_method, remarks,category_id, date, created_by, attachment
                         FROM unmarked_transaction WHERE inventory_transaction_id = ?";
        $stmt_unmarked = $conn_rjllearning_specific->prepare($sql_unmarked);
        $stmt_unmarked->execute([$transaction_id]);
        $unmarked_transaction = $stmt_unmarked->fetch(PDO::FETCH_ASSOC);

        // Step 3: Handle attachment processing
        $final_array_attachment = null;
        if ($unmarked_transaction && !empty($unmarked_transaction['attachment'])) {
            $data_attachment = json_decode($unmarked_transaction['attachment'], true);
            if (isset($data_attachment['filepath']) && isset($data_attachment['original_name'])) {
                $filePath = $data_attachment['filepath'];
                $originalName = $data_attachment['original_name'];
                $aws_attachment = displayFileFromS3($GLOBALS['s3_learning_bucket'], $GLOBALS['learning_bucketName'], $filePath);
                $final_array_attachment = ["filepath" => $aws_attachment, "original_name" => $originalName];
            }
        }


        $category_data = null; // Default value if category is not found

        if (!empty($unmarked_transaction['category_id'])) {
            $sql = "SELECT id, group_name FROM unmarked_transaction_type_group 
                        WHERE institution_id = ? AND id = ? ";
            $stmt = $conn_rjllearning_specific->prepare($sql);
            $params = [$institution_id, $unmarked_transaction['category_id']];

            $stmt->execute($params);
            $category = $stmt->fetch(PDO::FETCH_ASSOC);

            if ($category) { // No need for extra `!empty()`, `fetch()` returns `false` if not found
                $category_data = [
                    'id' => $category['id'],
                    'name' => $category['group_name']
                ];
            }
        }

        // Step 4: Fetch product details
        $stmt_prod = $conn_rjllearning_specific->prepare("SELECT id, product_name FROM product_items WHERE id = ? AND institution_id = ?");
        $stmt_prod->execute([$trans_detail['item_id'], $institution_id]);
        $product_detail = $stmt_prod->fetch(PDO::FETCH_ASSOC);
        $product_details = $product_detail ? [
            "item_id" => $product_detail['id'],
            "item_name" => $product_detail['product_name']
        ] : null;

        // Step 5: Fetch student details if applicable
        $student_details = null;
        if ($unmarked_transaction && $unmarked_transaction['first_party_type'] == "students") {
            $stmt_student = $conn_rjllearning_specific->prepare("SELECT id, name FROM students WHERE id = ? AND institution_id = ?");
            $stmt_student->execute([$unmarked_transaction['vendor_name'], $institution_id]);
            $student_detail = $stmt_student->fetch(PDO::FETCH_ASSOC);
            if ($student_detail) {
                $student_details = [
                    "student_id" => $student_detail['id'],
                    "student_name" => $student_detail['name']
                ];
            }
        }

        // Step 6: Fetch branch details
        $branch = getBranchInfo($conn_rjllearning_specific, $institution_id, $trans_detail['branch_id']);

        // Determine first party value
        $first_party_value = ($unmarked_transaction && $unmarked_transaction['first_party_type'] == "students") ? $student_details : ($unmarked_transaction['vendor_name'] ?? null);

        // Step 7: Prepare response
        return [
            'id' => $trans_detail['id'],
            'institution_id' => $trans_detail['institution_id'],
            'branch_id' => $branch,
            'transaction_type' => $trans_detail['transaction_type'],
            'item_id' => $product_details,
            'transaction_type' => $trans_detail['transaction_type'],
            'category' => $category_data,
            'quantity' => $trans_detail['quantity'],
            'amount' => $unmarked_transaction['amount'] ?? null,
            'first_party' => $first_party_value,
            'first_party_type' => $unmarked_transaction['first_party_type'] ?? null,
            // 'payment_bool' => $unmarked_transaction['payment_bool'],
            // 'payment_method' => $unmarked_transaction['payment_method'],
            'payment_bool' => $unmarked_transaction['payment_bool'] ?? null,
            'payment_method' => $unmarked_transaction['payment_method'] ?? null,
            'second_party' => $trans_detail['second_party'],
            'remarks' => $unmarked_transaction['remarks'] ?? null,
            'date' => $trans_detail['date'],
            'attachment' => $final_array_attachment,
            'otp_authorisation' => $trans_detail['otp_authorisation'],
            'created_by' => $trans_detail['created_by'],
            'created_at' => $trans_detail['created_at'],
            'transfer_accepted_by' => $trans_detail['transfer_accepted_by'],
            'transfer_accepted_at' => $trans_detail['transfer_accepted_at'],
        ];
    } catch (Exception $e) {
        return ['error' => "Error: " . $e->getMessage()];
    }
}

function get_all_inventory_transactions($conn_rjllearning_specific, $institution_id, $branch_id, $from_date, $to_date, $limit = null, $offset = null)
{
    try {
        // Base query
        $sql = "SELECT * FROM inventory_transactions WHERE institution_id = ?";
        $params = [$institution_id];

        // Add branch filter if provided
        if ($branch_id !== 'all') {
            $sql .= " AND branch_id = ?";
            $params[] = $branch_id;
        }
        
        if ($from_date != null && $to_date != null) {
            $sql .= " AND date BETWEEN ? AND ?";
            $params[] = $from_date;
            $params[] = $to_date;
        }
        
        $sql .= " ORDER BY date DESC, created_at DESC";
        // Count total rows before applying LIMIT
        $count_sql = str_replace("*", "COUNT(*) as total_count", $sql);
        $stmt_count = $conn_rjllearning_specific->prepare($count_sql);
        $stmt_count->execute($params);
        $total_count = $stmt_count->fetch(PDO::FETCH_ASSOC)['total_count'];

        // Apply LIMIT and OFFSET for pagination if provided
        if ($limit !== 'all' && is_numeric($limit)) {
            $sql .= " LIMIT " . $offset . " ," . $limit . "";
        }

        // Fetch transactions
        $stmt = $conn_rjllearning_specific->prepare($sql);
        $stmt->execute($params);
        $transactions = $stmt->fetchAll(PDO::FETCH_ASSOC);

        if (!$transactions) {
            return ['data' => [], 'total_count' => 0];
        }

        $result = [];
        foreach ($transactions as $trans_detail) {

            $inventory_id = $trans_detail['id'];

            // Step 3: Fetch corresponding unmarked_transaction data
            $sql_unmarked = "SELECT id, amount, vendor_name, first_party_type,category_id, remarks, date, created_by, attachment
                             FROM unmarked_transaction WHERE inventory_transaction_id = ?";
            $stmt_unmarked = $conn_rjllearning_specific->prepare($sql_unmarked);
            $stmt_unmarked->execute([$inventory_id]);
            $unmarked_transaction = $stmt_unmarked->fetch(PDO::FETCH_ASSOC);

            // If no unmarked transaction exists, continue with default values
            $final_array_attachment = null;
            if ($unmarked_transaction && !empty($unmarked_transaction['attachment'])) {
                $data_attachment = json_decode($unmarked_transaction['attachment'], true);
                if (isset($data_attachment['filepath']) && isset($data_attachment['original_name'])) {
                    $filePath = $data_attachment['filepath'];
                    $originalName = $data_attachment['original_name'];
                    $aws_attachment = displayFileFromS3($GLOBALS['s3_learning_bucket'], $GLOBALS['learning_bucketName'], $filePath);
                    $final_array_attachment = ["filepath" => $aws_attachment, "original_name" => $originalName];
                }
            }


            $category_data = null; // Default value if category is not found

            if (!empty($unmarked_transaction['category_id'])) {
                $sql = "SELECT id, group_name FROM unmarked_transaction_type_group 
                        WHERE institution_id = ? AND id = ? ";
                $stmt = $conn_rjllearning_specific->prepare($sql);
                $params = [$institution_id, $unmarked_transaction['category_id']];

                $stmt->execute($params);
                $category = $stmt->fetch(PDO::FETCH_ASSOC);

                if ($category) { // No need for extra `!empty()`, `fetch()` returns `false` if not found
                    $category_data = [
                        'id' => $category['id'],
                        'name' => $category['group_name']
                    ];
                }
            }

            // Step 4: Fetch product details
            $stmt_prod = $conn_rjllearning_specific->prepare("SELECT id, product_name FROM product_items WHERE id = ? AND institution_id = ?");
            $stmt_prod->execute([$trans_detail['item_id'], $institution_id]);
            $product_detail = $stmt_prod->fetch(PDO::FETCH_ASSOC);
            $product_details = $product_detail ? [
                "item_id" => $product_detail['id'],
                "item_name" => $product_detail['product_name']
            ] : null;

            // Step 5: Fetch student details if applicable
            $student_details = null;
            if ($unmarked_transaction && $unmarked_transaction['first_party_type'] == "students") {
                $stmt_student = $conn_rjllearning_specific->prepare("SELECT id, name FROM students WHERE id = ? AND institution_id = ?");
                $stmt_student->execute([$unmarked_transaction['vendor_name'], $institution_id]);
                $student_detail = $stmt_student->fetch(PDO::FETCH_ASSOC);
                if ($student_detail) {
                    $student_details = [
                        "student_id" => $student_detail['id'],
                        "student_name" => $student_detail['name']
                    ];
                }
            }

            // Step 6: Fetch branch details
            $branch = getBranchInfo($conn_rjllearning_specific, $institution_id, $trans_detail['branch_id']);

            // Determine first party value
            $first_party_value = ($unmarked_transaction && $unmarked_transaction['first_party_type'] == "students") ?  $student_detail['name'] : ($unmarked_transaction['vendor_name'] ?? null);

            // Step 7: Append transaction details to result array
            $result[] = [
                'id' => $trans_detail['id'],
                'institution_id' => $trans_detail['institution_id'],
                'branch_id' => $branch,
                'transaction_type' => $trans_detail['transaction_type'],
                'category' => $category_data,
                'item' => $product_details,
                'quantity' => $trans_detail['quantity'],
                'amount' => $unmarked_transaction['amount'] ?? null,
                'first_party' => $first_party_value,
                'first_party_type' => $unmarked_transaction['first_party_type'] ?? null,
                'transaction_type_group' => $unmarked_transaction['transaction_type_group'] ?? null,
                'second_party' => $trans_detail['second_party'],
                'remarks' => $unmarked_transaction['remarks'] ?? null,
                'date' => $trans_detail['date'],
                'attachment' => $final_array_attachment,
                'otp_authorisation' => $trans_detail['otp_authorisation'],
                'created_by' => $trans_detail['created_by'],
                'created_at' => $trans_detail['created_at'],
                'transfer_accepted_by' => $trans_detail['transfer_accepted_by'],
                'transfer_accepted_at' => $trans_detail['transfer_accepted_at'],
            ];
        }

        return ['data' => $result, 'total_count' => $total_count];
    } catch (Exception $e) {
        return ['error' => "Error: " . $e->getMessage()];
    }
}

// function fetch_inventory_transactions_stats($institution_id, $branch_id, $from_date, $to_date, $conn_rjllearning_specific)
// {
//     $response = [
//         "sales" => [],
//         "purchase" => []
//     ];

//     try {
//         // Combined query to fetch both sales and purchase in descending order by quantity
//         $query = "SELECT item_id, institution_id, transaction_type, SUM(quantity) as total_quantity FROM inventory_transactions WHERE institution_id = ?";
//         $array_params = [$institution_id];

//         if ($branch_id != 'all') {
//             $query .= " AND branch_id = ?";
//             $array_params[] = $branch_id;
//         }

//         if ($from_date != null && $to_date != null) {
//             $query .= " AND date BETWEEN ? AND ?";
//             $array_params[] = $from_date;
//             $array_params[] = $to_date;
//         }

//         $query .= " AND transaction_type IN ('sales', 'purchase') GROUP BY item_id, transaction_type 
//                     ORDER BY transaction_type DESC, total_quantity DESC";

//         $stmt = $conn_rjllearning_specific->prepare($query);
//         $stmt->execute($array_params);
//         $result = $stmt->fetchAll();

//         foreach ($result as $row) {

//             $sql = "SELECT product_name FROM product_items WHERE id = ? AND institution_id = ?";
//             $stmt = $conn_rjllearning_specific->prepare($sql);
//             $stmt->execute([$row['item_id'], $institution_id]);
//             $result = $stmt->fetch();
//             $product_name = $result['product_name'];

//             if ($row['transaction_type'] == 'sales') {
//                 $response['sales'][] = [
//                     "item_id" => $row['item_id'],
//                     "institution_id" => $row['institution_id'],
//                     "product_name" => $product_name,
//                     "total_quantity" => $row['total_quantity']
//                 ];
//             } elseif ($row['transaction_type'] == 'purchase') {
//                 $response['purchase'][] = [
//                     "item_id" => $row['item_id'],
//                     "institution_id" => $row['institution_id'],
//                     "product_name" => $product_name,
//                     "total_quantity" => $row['total_quantity']
//                 ];
//             }
//         }
//         return $response;
//     } catch (Exception $e) {
//         errorreturn("Error: " . $e->getMessage());
//         return;
//     }
// }

function fetch_inventory_transactions_stats($institution_id, $branch_id, $from_date, $to_date, $conn_rjllearning_specific)
{
    $response = [
        "sales" => [],
        "purchase" => [],
        "sales_category" => [],
        "purchase_category" => []
    ];

    try {
        // Query for sales and purchase by item
        $query = "SELECT 
            it.item_id, 
            it.institution_id, 
            it.transaction_type, 
            SUM(it.quantity) as total_quantity, 
            pi.product_name, 
            pi.category_id,
            ic.category_name
          FROM inventory_transactions it
          INNER JOIN product_items pi ON it.item_id = pi.id
          INNER JOIN items_category ic ON pi.category_id = ic.id
          WHERE it.transaction_type IN ('sales', 'purchase')";

        $array_params = [];

        if ($institution_id != 'all') {
            $query .= " AND it.institution_id = ?";
            $array_params = [$institution_id];
        }

        if ($branch_id != 'all') {
            $query .= " AND it.branch_id = ?";
            $array_params[] = $branch_id;
        }

        if ($from_date != null && $to_date != null) {
            $query .= " AND it.date BETWEEN ? AND ?";
            $array_params[] = $from_date;
            $array_params[] = $to_date;
        }

        // Add ALL selected columns into GROUP BY
        $query .= " GROUP BY it.item_id, it.institution_id, it.transaction_type, pi.product_name, pi.category_id, ic.category_name 
                    ORDER BY it.transaction_type DESC, total_quantity DESC";

        $stmt = $conn_rjllearning_specific->prepare($query);
        $stmt->execute($array_params);
        $result = $stmt->fetchAll();


        // Organize sales and purchase data by item
        foreach ($result as $row) {
            $data = [
                "item_id" => $row['item_id'],
                "institution_id" => $row['institution_id'],
                "product_name" => $row['product_name'],
                "quantity" => $row['total_quantity']
            ];

            if ($row['transaction_type'] == 'sales') {
                $response['sales'][] = $data;
            } elseif ($row['transaction_type'] == 'purchase') {
                $response['purchase'][] = $data;
            }
        }

        // Get Top 5 + Others for Sales
        $response['sales_category'] = fetchTop5AndOthers('sales', $institution_id, $branch_id, $from_date, $to_date, $conn_rjllearning_specific);

        // Get Top 5 + Others for Purchase
        $response['purchase_category'] = fetchTop5AndOthers('purchase', $institution_id, $branch_id, $from_date, $to_date, $conn_rjllearning_specific);

        return $response;
    } catch (Exception $e) {
        errorreturn("Error: " . $e->getMessage());
        return;
    }
}

function fetchTop5AndOthers($transactionType, $institution_id, $branch_id, $from_date, $to_date, $conn_rjllearning_specific)
{
    // Query for Top 5 Categories
    $top5Query = "SELECT pi.category_id, ic.category_name, SUM(it.quantity) as total_quantity 
                  FROM inventory_transactions it
                  INNER JOIN product_items pi ON it.item_id = pi.id 
                  INNER JOIN items_category ic ON pi.category_id = ic.id
                  WHERE it.transaction_type = ?";

    $params = [$transactionType];

    if ($institution_id != 'all') {
        $top5Query .= " AND it.institution_id = ?";
        $params[] = $institution_id;
    }

    if ($branch_id != 'all') {
        $top5Query .= " AND it.branch_id = ?";
        $params[] = $branch_id;
    }

    if ($from_date != null && $to_date != null) {
        $top5Query .= " AND it.date BETWEEN ? AND ?";
        $params[] = $from_date;
        $params[] = $to_date;
    }

    $top5Query .= " GROUP BY pi.category_id 
                    ORDER BY total_quantity DESC 
                    LIMIT 5";

    $stmt = $conn_rjllearning_specific->prepare($top5Query);
    $stmt->execute($params);
    $top5Result = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Collect Top 5 category IDs for "Others" calculation
    $top5CategoryIds = array_column($top5Result, 'category_id');

    // Query for "Others" Category
    if (!empty($top5CategoryIds)) {
        $placeholders = implode(',', array_fill(0, count($top5CategoryIds), '?'));
        $othersParams = array_merge($params, $top5CategoryIds);

        $othersQuery = "SELECT 'others' as category_id, 'Others' as category_name, 
                        SUM(it.quantity) as total_quantity 
                        FROM inventory_transactions it
                        INNER JOIN product_items pi ON it.item_id = pi.id 
                        WHERE it.transaction_type = ?";

        if ($institution_id != 'all') {
            $othersQuery .= " AND it.institution_id = ?";
        }

        if ($branch_id != 'all') {
            $othersQuery .= " AND it.branch_id = ?";
        }

        if ($from_date != null && $to_date != null) {
            $othersQuery .= " AND it.date BETWEEN ? AND ?";
        }

        $othersQuery .= " AND pi.category_id NOT IN ($placeholders)";

        $stmt = $conn_rjllearning_specific->prepare($othersQuery);
        $stmt->execute($othersParams);
        $othersResult = $stmt->fetch(PDO::FETCH_ASSOC);

        if ($othersResult && $othersResult['total_quantity'] > 0) {
            $top5Result[] = $othersResult;
        }
    }

    return $top5Result;
}

function fetchproduct_item($conn_rjllearning_specific, $conn_rjllearning, $institution_id, $branch_id, $search_string, $from_date, $to_date, $date_created, $limit, $offset)
{

    try {
        $sql_count = "SELECT  count(*) as total_rows FROM product_items where institution_id =? AND status =?";
        $array_params = [$institution_id, 1];

        if ($search_string != null) {
            $sql_count .= " AND (product_name LIKE ?)";
            $array_params[] = '%' . $search_string . '%';
        }

        if ($date_created != null) {
            $sql_count .= " AND date_created = ?";
            $array_params[] = $date_created;
        }

        // Add date range condition
        if ($from_date != null && $to_date != null) {
            $sql_count .= " AND date_created BETWEEN ? AND ?";
            $array_params[] = $from_date;
            $array_params[] = $to_date;
        }

        $stmt = $conn_rjllearning_specific->prepare($sql_count);
        $stmt->execute($array_params);
        $result_total = $stmt->fetchAll();
        $totalrows = $result_total[0]['total_rows'];

        if ($limit == 'all') {

            $sql_item = "SELECT * FROM product_items where institution_id =? AND status =?";
            $array_params = [$institution_id, 1];

            if ($search_string != null) {
                $sql_item .= " AND (product_name LIKE ?)";
                $array_params[] = '%' . $search_string . '%';
            }

            if ($date_created != null) {
                $sql_item .= " AND date_created = ?";
                $array_params[] = $date_created;
            }

            // Add date range condition
            if ($from_date != null && $to_date != null) {
                $sql_item .= " AND date_created BETWEEN ? AND ?";
                $array_params[] = $from_date;
                $array_params[] = $to_date;
            }

            $sql_item .=  "order by date_created desc, time_created desc";
        } else {
            $sql_item = "SELECT * FROM  product_items where institution_id =? AND status =?";
            $array_params = [$institution_id, 1];

            if ($search_string != null) {
                $sql_item .= " AND (product_name LIKE ?)";
                $array_params[] = '%' . $search_string . '%';
            }

            if ($date_created != null) {
                $sql_item .= " AND date_created = ?";
                $array_params[] = $date_created;
            }

            // Add date range condition
            if ($from_date != null && $to_date != null) {
                $sql_item .= " AND date_created BETWEEN ? AND ?";
                $array_params[] = $from_date;
                $array_params[] = $to_date;
            }
            $sql_item .= " order by date_created desc, time_created desc ";
            $sql_item .= "LIMIT " . $offset . " ," . $limit . "";
        }

        $stmt = $conn_rjllearning_specific->prepare($sql_item);
        $stmt->execute($array_params);
        $session_rows = $stmt->fetchAll();

        $array_return = [];

        foreach ($session_rows as $rows) {

            $institution = getInstitution($conn_rjllearning, $rows['institution_id']);

            $sql_category = "SELECT id, category_name FROM items_category WHERE id = ? AND institution_id = ?";
            $stmtCategory = $conn_rjllearning_specific->prepare($sql_category);
            $stmtCategory->execute([$rows['category_id'], $rows['institution_id']]);
            $category_detail = $stmtCategory->fetch(PDO::FETCH_ASSOC); // Fetch as associative array

            if ($category_detail && is_array($category_detail)) {
                $category_details = [
                    "item_category_id" => $category_detail['id'],
                    "category_name" => $category_detail['category_name']
                ];
            }
            
            $inventory_query = "SELECT 
            COALESCE(SUM(CASE WHEN transaction_type = 'purchase' AND branch_id = ? THEN quantity ELSE 0 END), 0) AS purchase_qty,
            COALESCE(SUM(CASE WHEN transaction_type = 'sales' AND branch_id = ? THEN quantity ELSE 0 END), 0) AS sales_qty,
            COALESCE(SUM(CASE WHEN transaction_type = 'transfer' AND branch_id = ? THEN quantity ELSE 0 END), 0) AS transferred_out_qty,
            COALESCE(SUM(CASE WHEN transaction_type = 'transfer' AND second_party = ? THEN quantity ELSE 0 END), 0) AS transferred_in_qty
            FROM inventory_transactions
            WHERE item_id = ? AND institution_id = ?";
                $inv_stmt = $conn_rjllearning_specific->prepare($inventory_query);
                $inv_stmt->execute([
                    $branch_id,
                    $branch_id,
                    $branch_id,
                    $branch_id,
                    $rows['id'],
                    $institution_id
                ]);

                $inv_row = $inv_stmt->fetch();

                $purchase_qty        = $inv_row['purchase_qty'] ?? 0;
                $sales_qty           = $inv_row['sales_qty'] ?? 0;
                $transferred_out_qty = $inv_row['transferred_out_qty'] ?? 0;
                $transferred_in_qty  = $inv_row['transferred_in_qty'] ?? 0;

                // Compute current quantity
                $current_quantity = $purchase_qty + $transferred_in_qty - $sales_qty - $transferred_out_qty;

            $array_return[] = array(
                'item_id' => $rows['id'],
                'institution_id' => $institution,
                'product_name' => $rows['product_name'],
                'quantity' => $current_quantity,
                'category' => $category_details,
                'can_repeat' => $rows['can_repeat'],
                'item_code' => $rows['item_code'],
                'send_otp' => $rows['send_otp'],
                'sale_distribute' => $rows['sale_distribute'],
                'price' => $rows['price'],
                'added_by' => $rows['added_by'],
                'date_created' => $rows['date_created'],
                'time_created' => $rows['time_created'],
                'last_modified_by' => $rows['last_modified_by'],
                'last_modified_at' => $rows['last_modified_at'],
                'status' => $rows['status'],
            );
        }

        if (count($session_rows) < 50) {
            return array('total_rows' => $totalrows, 'items_info' => $array_return, 'maxreach' => 1);
        } else {
            return array('total_rows' => $totalrows, 'items_info' => $array_return, 'maxreach' => 0);
        }
    } catch (Exception $e) {
        errorreturn("Error: " . $e->getMessage());
        return;
    }
}
