<?php

function fetch_inventory_transfer_stats($institution_id, $branch_id, $from_date, $to_date, $conn_rjllearning_specific)
{
    $response = [];

    try {
        // Query to fetch transferred items
        $query = "SELECT 
            ANY_VALUE(id) AS id,
            item_id,
            ANY_VALUE(status) AS status,
            ANY_VALUE(date) AS date,
            ANY_VALUE(transfer_accepted_at) AS transfer_accepted_at,
            second_party,
            ANY_VALUE(branch_id) AS branch_id,
            SUM(quantity) AS total_quantity,
            ANY_VALUE(remarks) AS remarks
          FROM inventory_transactions 
          WHERE institution_id = ? 
            AND (status = 1 OR status = -1) 
            AND transaction_type = 'transfer'";

        $array_params = [$institution_id];

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

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

        $query .= " GROUP BY item_id, second_party ORDER BY total_quantity DESC";

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

        $response = [];

        foreach ($result as $row) {
            // Fetch branch name from branch_id
            $branch_id_name = getBranchInfo($conn_rjllearning_specific, $institution_id, $row['branch_id'])['name'];

            // Fetch product name
            $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]);
            $product_data = $stmt->fetch(PDO::FETCH_ASSOC);
            $product_name = $product_data ? $product_data['product_name'] : 'Unknown';

            // Fetch second party (branch) name
            $second_party_info = getBranchInfo($conn_rjllearning_specific, $institution_id, $row['second_party']);
            $second_party_name = $second_party_info ? $second_party_info['name'] : 'Unknown Branch';

            // Build response
            $response[] = [
                "inventory_id" => $row['id'],
                "item_id" => $row['item_id'],
                "first_party" => $branch_id_name,
                "product_name" => $product_name,
                "total_quantity" => $row['total_quantity'],
                "transferred_to_id" => $row['second_party'],
                "transferred_to_name" => $second_party_name,
                "status" => $row['status'],
                "date" => $row['date'],
                "transfer_accepted_at" => $row['transfer_accepted_at'],
                "remarks" => $row['remarks']
            ];
        }

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





function fetch_pending_inventory_transfer_stats($institution_id, $branch_id, $from_date, $to_date, $conn_rjllearning_specific)
{
    $response = [];

    try {
        // Query to fetch pending transferred items
        $query = "SELECT 
            ANY_VALUE(id) AS id,
            item_id,
            ANY_VALUE(date) AS date,
            second_party,
            ANY_VALUE(status) AS status,
            ANY_VALUE(transfer_accepted_at) AS transfer_accepted_at,
            ANY_VALUE(branch_id) AS branch_id,
            SUM(quantity) AS total_quantity,
            ANY_VALUE(remarks) AS remarks
          FROM inventory_transactions
          WHERE institution_id = ?
            AND status = 0
            AND transaction_type = 'transfer'";

        $array_params = [$institution_id];

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

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

        $query .= " GROUP BY item_id, second_party ORDER BY total_quantity DESC";

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


        foreach ($result as $row) {
            $branch_id_name = getBranchInfo($conn_rjllearning_specific, $institution_id, $row['branch_id'])['name'];

            // Fetch product name
            $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]);
            $product_data = $stmt->fetch(PDO::FETCH_ASSOC);
            $product_name = $product_data ? $product_data['product_name'] : 'Unknown';

            // Fetch second party (branch) name (Avoid duplicate calls)
            $second_party_info = getBranchInfo($conn_rjllearning_specific, $institution_id, $row['second_party']);
            $second_party_name = $second_party_info ? $second_party_info['name'] : 'Unknown Branch';

            // Add to response
            $response[] = [
                "inventory_id" => $row['id'],
                "item_id" => $row['item_id'],
                "first_party" => $branch_id_name,
                "product_name" => $product_name,
                "total_quantity" => $row['total_quantity'],
                "transferred_to_id" => $row['second_party'],
                "transferred_to_name" => $second_party_name,
                "status" => $row['status'],
                "date" => $row['date'],
                "transfer_accepted_at" => $row['transfer_accepted_at'],
                "remarks" => $row['remarks']
            ];
        }

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




function fetch_staff_inventory_transfer_stats($institution_id, $branch_id, $second_party, $from_date, $to_date, $conn_rjllearning_specific, $limit, $offset)
{
    $response = [];

    try {
        // Query to fetch transferred items
        $query = "SELECT id, item_id, status, date, transfer_accepted_at, second_party, branch_id, quantity, remarks 
                  FROM inventory_transactions  
                  WHERE institution_id = ?  
                  AND (status = 1 OR status = -1)  
                  AND transaction_type = 'transfer'  
                  AND second_party = ?";  // Your branch as receiver

        $array_params = [$institution_id, $branch_id]; // Your branch is second_party (receiver)

        if ($second_party != 'all') {
            $query .= " AND branch_id = ?";  // Filter by sender (branch_id)
            $array_params[] = $second_party; // second_party represents the sender's branch
        }

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

        // Corrected ORDER BY clause
        $query .= " ORDER BY quantity DESC";
        
        // Add pagination
        if ($limit != 'all') {
            $query .= " LIMIT " . (int)$offset . ", " . (int)$limit;
        }

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

        foreach ($result as $row) {
            $branch_id_name = getBranchInfo($conn_rjllearning_specific, $institution_id, $row['branch_id'])['name'];

            // Fetch product name
            $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]);
            $product_data = $stmt->fetch(PDO::FETCH_ASSOC);
            $product_name = $product_data ? $product_data['product_name'] : 'Unknown';

            // Fetch second party (branch) name (Avoid duplicate calls)
            $second_party_info = getBranchInfo($conn_rjllearning_specific, $institution_id, $row['second_party']);
            $second_party_name = $second_party_info ? $second_party_info['name'] : 'Unknown Branch';

            // Add to response
            $response[] = [
                "inventory_id" => $row['id'],
                "item_id" => $row['item_id'],
                "first_party" => $branch_id_name,
                "product_name" => $product_name,
                "total_quantity" => $row['quantity'],
                "transferred_to_id" => $row['second_party'],
                "transferred_to_name" => $second_party_name,
                "status" => $row['status'],
                "date" => $row['date'],
                "transfer_accepted_at" => $row['transfer_accepted_at'],
                "remarks" => $row['remarks']
            ];
        }

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

function fetch_staff_pending_inventory_transfer_stats(
    $institution_id,
    $branch_id,
    $second_party,
    $from_date,
    $to_date,
    $conn_rjllearning_specific,
    $limit,
    $offset
) {
    $response = [];

    try {
        // Query to fetch pending transferred items
        $query = "SELECT id, item_id, date, second_party, status, transfer_accepted_at, branch_id, 
                         SUM(quantity) as total_quantity, remarks
                  FROM inventory_transactions 
                  WHERE institution_id = ?  
                  AND status = 0  
                  AND transaction_type = 'transfer' 
                  AND second_party = ?";  // Receiver

        $array_params = [$institution_id, $branch_id]; // you are second_party

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

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

        $query .= " GROUP BY item_id, second_party 
                    ORDER BY total_quantity DESC";

        // Add pagination
        if ($limit != 'all') {
            $query .= " LIMIT " . (int)$offset . ", " . (int)$limit;
        }

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

        foreach ($result as $row) {
            $branch_id_name = getBranchInfo($conn_rjllearning_specific, $institution_id, $row['branch_id'])['name'];

            // Fetch product name
            $sql = "SELECT product_name FROM product_items WHERE id = ? AND institution_id = ?";
            $stmt2 = $conn_rjllearning_specific->prepare($sql);
            $stmt2->execute([$row['item_id'], $institution_id]);
            $product_data = $stmt2->fetch(PDO::FETCH_ASSOC);
            $product_name = $product_data ? $product_data['product_name'] : 'Unknown';

            // Fetch second party (branch) name
            $second_party_info = getBranchInfo($conn_rjllearning_specific, $institution_id, $row['second_party']);
            $second_party_name = $second_party_info ? $second_party_info['name'] : 'Unknown Branch';

            // Add to response
            $response[] = [
                "inventory_id"        => $row['id'],
                "item_id"             => $row['item_id'],
                "first_party"         => $branch_id_name,
                "product_name"        => $product_name,
                "total_quantity"      => $row['total_quantity'],
                "transferred_to_id"   => $row['second_party'],
                "transferred_to_name" => $second_party_name,
                "status"              => $row['status'],
                "date"                => $row['date'],
                "transfer_accepted_at" => $row['transfer_accepted_at'],
                "remarks"             => $row['remarks']
            ];
        }
        return $response;
    } catch (Exception $e) {
        errorreturn("Error: " . $e->getMessage());
        return;
    }
}
