<?php

class IngredientStock {

    /*************************************************
    / This function adds new ingredient stock
    *************************************************/
     public static function AddIngredientStock($data) {
        global $db; // Ensure $db is the global database connection object

        // Insert query for ingredient_stock
        $id = $db->query_insert('ingredient_stock', $data);

        // Check if the insert was successful
        if ($db->affected_rows > 0) {
            return $id;
        } else {
            // Output SQL error for debugging
            echo "SQL Error: " . $db->last_error;
            return false;
        }
    } // AddPurchase

    /*************************************************
    / This function gets ingredient stock by ingredient ID
    *************************************************/
     public static function GetIngredientStockByIngId($ing_id, $orderBy = "ASC") {
      global $db;
      $query = "SELECT * FROM " . TABLE_INGREDIENT_STOCK . " WHERE ingredient_id = '$ing_id' ORDER BY batch_date $orderBy";

    //  echo "<script>alert('Running Query: {$query}');</script>";  // Debugging to see the query
      $rows = $db->fetch_all_array($query);

      if ($db->affected_rows > 0) {
          return $rows;
      } else {
      //    echo "<script>alert('No rows returned for Ingredient ID: {$ing_id}');</script>";
          return false;
      }
  } // GetIngredientStockByIngId

    /*************************************************
    / This function deducts stock based on FIFO method
    *************************************************/
    public static function DeductStockFIFO($ing_id, $required_qty) {
        global $db;

        // Get stock entries for the ingredient ordered by date (FIFO)
        $stocks = self::GetIngredientStockByIngId($ing_id, "ASC");

        if (!$stocks) {
            return 0;  // No stock available
        }

        $total_deducted = 0;

        foreach ($stocks as $stock) {
            if ($required_qty <= 0) break;

            $available_qty = $stock['remaining_qty'];

            if ($available_qty <= 0) continue;  // Skip if no stock is available

            // Determine how much to deduct from the current stock
            $deduct_qty = min($available_qty, $required_qty);

            // Update remaining quantity in the stock entry
            $remaining_qty = $available_qty - $deduct_qty;
            $updateData = ['remaining_qty' => $remaining_qty];
            $db->query_update(TABLE_INGREDIENT_STOCK, $updateData, "id='" . $stock['id'] . "'");

            // Update counters
            $total_deducted += $deduct_qty;
            $required_qty -= $deduct_qty;
        }

        return $total_deducted;  // Return total deducted quantity
    } // DeductStockFIFO

    /*************************************************
    / This function updates ingredient stock by ID
    *************************************************/
    public static function UpdateIngredientStockById($stock_id, $data) {
        global $db;
        
        $id = $db->query_update(TABLE_INGREDIENT_STOCK, $data, "id='$stock_id'");
        if ($db->affected_rows > 0) {
            return $id;
        } else {
            echo "SQL Error: " . $db->last_error;  // Debugging SQL error if any
            return false;
        }
    } // UpdateIngredientStockById

    /*************************************************
    / This function deletes ingredient stock by ID
    *************************************************/
    public static function DeleteIngredientStockById($stock_id) {
        global $db;
        
        $query = "DELETE FROM " . TABLE_INGREDIENT_STOCK . " WHERE id='$stock_id'";
        $db->query($query);
        if ($db->affected_rows > 0) {
            return true;
        } else {
            return false;
        }
    } // DeleteIngredientStockById
  
  /*************************************************
/ This function gets ingredient stock by ingredient ID
*************************************************/
	
public static function ReserveStockFIFO($ing_id, $required_qty, $batch_id, $created_batch_id) {
    global $db;

    // Get the current stock entries for the ingredient ordered by batch_date (FIFO)
    $stocks = self::GetIngredientStockByIngId($ing_id, "ASC");

    if (empty($stocks)) {
        echo "<script>alert('No stock found for Ingredient ID: {$ing_id}');</script>";
        return 0;  // No stock found, so return 0 reserved quantity
    }

    $total_reserved_qty = 0;
    $reserved_on = date('Y-m-d H:i:s'); 

    foreach ($stocks as $stock) {
        if ($required_qty <= 0) break;

        $available_qty = $stock->available_qty;

        if ($available_qty <= 0) {
            continue;  // Skip if no stock is available
        }

        // Calculate the amount to reserve based on the percentage
        //$reserve_qty = min($available_qty, $required_qty * ($percentage / 100));		
      $reserve_qty = min($available_qty, $required_qty);

        // Update the available and reserved quantities
        $remaining_available_qty = $available_qty - $reserve_qty;
        $new_reserved_qty = $stock->reserved_qty + $reserve_qty;

        $updateData = [
            'available_qty' => $remaining_available_qty,
            'reserved_qty' => $new_reserved_qty
        ];

        // Update the stock entry in the database
        $updateResult = $db->query_update(TABLE_INGREDIENT_STOCK, $updateData, "id='" . $stock->id . "'");

        // If the update is successful, insert a record in the batch_tracking table
        if ($updateResult) {
            $trackingData = [
                'batch_id' => $batch_id,
                'ingredient_id' => $ing_id,
                'stock_id' => $stock->id,
                'reserved_qty' => $reserve_qty,
                'reserved_on' => $reserved_on,
                'created_batch_id' => $created_batch_id,
            ];

            // Insert the tracking data into the batch_tracking table
            $trackingInsertResult = $db->query_insert(TABLE_BATCH_TRACKING, $trackingData);

            if (!$trackingInsertResult) {
                echo "<script>alert('Failed to insert into batch_tracking. SQL Error: " . $db->last_error . "');</script>";
            }
        }

        // Decrease the required_qty by the reserved amount
        $required_qty -= $reserve_qty;
        $total_reserved_qty += $reserve_qty;
    }

    return $total_reserved_qty;  // Return the total reserved quantity
}


public static function CheckStockAvailabilityFIFO($ing_id, $required_qty) {
    global $db;

    // Get the current stock entries for the ingredient ordered by batch_date (FIFO)
    $stocks = self::GetIngredientStockByIngId($ing_id, "ASC");

    $total_available_qty = 0;

    foreach ($stocks as $stock) {
        if ($required_qty <= 0) break;

        $available_qty = $stock->available_qty;

        if ($available_qty <= 0) {
            continue;  // Skip if no stock is available
        }

        // Accumulate available quantities
        $total_available_qty += min($available_qty, $required_qty);
        $required_qty -= $available_qty;
    }

    return $total_available_qty;  // Return the total available quantity
}
  
  
/*************************************************
/ This function gets ingredient stock by stock ID
*************************************************/
public static function GetStockById($stock_id) {
    global $db;

    $query = "SELECT * FROM " . TABLE_INGREDIENT_STOCK . " WHERE id = '$stock_id'";
    $row = $db->query_first($query);

    if ($row) {
        return $row;
    } else {
        echo "<script>alert('No stock found for Stock ID: {$stock_id}');</script>";
        return false;
    }
}

/*************************************************
/ This function gets the name of each stock and total available stock of the ingredient
*************************************************/
public static function GetIngredientStockSummary() {
    global $db;

    $query = "
        SELECT 
            i.name AS ingredient_name, 
            SUM(s.available_qty) AS total_available_stock
        FROM 
            " . TABLE_INGREDIENT_STOCK . " s
        JOIN 
            " . TABLE_INGSETUP . " i 
            ON s.ingredient_id = i.id
        GROUP BY 
            s.ingredient_id
        ORDER BY 
            i.name ASC
    ";

    $rows = $db->fetch_all_array($query);

    if ($rows) {
        return $rows;
    } else {
        return false;
    }
}
/*************************************************
/ This function gets ingredient stock by stock ID
*************************************************/
public static function GetStockByIngredientId($ingredient_id, $purchase_id) {
        global $db;

        $query = "SELECT * FROM " . TABLE_INGREDIENT_STOCK . " WHERE ingredient_id = '$ingredient_id' AND purchase_id = '$purchase_id' LIMIT 1";
        return $db->query_first($query);
    }
  
   public static function GetIngredientUsageReport($filterReport) {
        global $db;

        // SQL Query to fetch ingredient name, sum of reserved_qty (used quantity), and available_qty (available stock)
         $query = "
            SELECT 
    i.name AS ingredient_name,
    SUM(bt.reserved_qty) AS total_used_quantity,
    SUM(bt.reserved_qty * ing_stock.price) AS total_price,  -- Total price before tax
    SUM((bt.reserved_qty * ing_stock.price) + (bt.reserved_qty * ing_stock.price * pi.tax / 100)) AS total_price_after_tax  -- Total price after tax
FROM 
    ingredients i
JOIN 
    batch_tracking bt ON i.id = bt.ingredient_id
JOIN 
    ingredient_stock ing_stock ON bt.stock_id = ing_stock.id
JOIN 
    purchase_items pi ON ing_stock.purchase_id = pi.purchase_id
$filterReport
GROUP BY 
    i.name
ORDER BY 
    i.name ASC ";
	// echo "<pre>SQL Query: " . $query . "</pre>";
        // Execute the query and fetch the results
        $rows = $db->fetch_all_array($query);
        if ($rows !== false) {
        return $rows;  // Return the results as an associative array
    } else {
        error_log("SQL Error: " . $db->last_error); // Log the SQL error
        return false;  // Return false if no rows are found or on error
    }
        
        if ($db->affected_rows > 0) {
            return $rows;  // Return the results as an associative array
        } else {
            return false;  // Return false if no rows are found
        }
    }
  
  /*************************************************
/ Balance Stock Report for Raw Material
*************************************************/
  
public static function GetBalanceStockReport($filter) {
    global $db;

    // SQL Query to fetch the necessary data based on filters
    $query = "
        SELECT
            ing.name AS item_name,
            p.code AS reference_number,
            p.create_date AS receiving_date,
            p.purchase_invoice_no,
            sup.name AS supplier_name,
            i.total_qty AS receiving_quantity,
            i.available_qty AS balance_quantity,
            i.price AS price_per_kg,
            (i.available_qty * i.price) AS amount,  -- Calculating amount based on available quantity
            pi.tax AS s_tax_rate,
            (i.available_qty * i.price * (pi.tax / 100)) AS s_tax_amount,  -- Calculating sales tax amount based on tax rate

            -- Aggregate totals for grouped ingredients
            SUM(i.total_qty) AS total_receiving_quantity,
            SUM(i.available_qty) AS total_balance_quantity,
            SUM(i.available_qty * i.price) AS total_amount,
            SUM(i.available_qty * i.price * (pi.tax / 100)) AS total_s_tax_amount

        FROM
            ingredient_stock i
        JOIN
            ingredients ing ON ing.id = i.ingredient_id
        JOIN
            purchase p ON p.id = i.purchase_id
        JOIN
            supplier_setup sup ON sup.id = p.supplier_id
        JOIN
            purchase_items pi ON pi.purchase_id = p.id AND pi.ing_id = i.ingredient_id

        WHERE
            i.available_qty > 0  -- Only include rows where balance quantity is greater than 0
			$filter
        -- Grouping by the ingredient name and then by other individual attributes to get totals for each group
        GROUP BY
            ing.name, p.code, p.create_date, p.purchase_invoice_no, sup.name, i.total_qty, i.available_qty, i.price, pi.tax

        -- Sorting by ingredient name and then by receiving date within each group
        ORDER BY
            ing.name ASC,
            p.create_date ASC
    ";

   // echo "<pre>SQL Query: " . $query . "</pre>";

    // Execute the query and fetch the results
    $rows = $db->fetch_all_array($query);

    if ($db->affected_rows > 0) {
        return $rows;  // Return the results as an associative array
    } else {
        return false;  // Return false if no rows are found
    }
}
  
  

 public static function GetRawMaterialStockRegister($dateFrom, $dateTo, $supplier_id, $ingredient_id)
{
    global $db;

    // Dynamic filters for each query
    $filterReportOpeningBalance = "";
    $filterReportTransaction = "";

    // Apply supplier filter if specified
    if (isset($supplier_id) && $supplier_id != 'All') {
        $filterReportOpeningBalance .= " AND purch.supplier_id = '$supplier_id'";
        $filterReportTransaction .= " AND p.supplier_id = '$supplier_id'";
    }

    // Apply ingredient filter if specified
    if (isset($ingredient_id) && $ingredient_id != 'All') {
        $filterReportOpeningBalance .= " AND ingr_stock.ingredient_id = '$ingredient_id'";
        $filterReportTransaction .= " AND i.ingredient_id = '$ingredient_id'";
    }

    // Step 1: Opening Balance Query
    $openingBalanceQuery = "
    SELECT 
    ingr_stock.ingredient_id,
    ing.name AS ingredient_name,
    SUM(ingr_stock.total_qty) AS total_received_qty,
    SUM(ingr_stock.total_qty * ingr_stock.price) AS total_received_amount,
    COALESCE(reserved_qty.total_reserved_qty, 0) AS total_reserved_qty,
    SUM(ingr_stock.total_qty) - COALESCE(reserved_qty.total_reserved_qty, 0) AS opening_balance_qty,
    SUM(ingr_stock.total_qty * ingr_stock.price) - COALESCE(reserved_qty.reserved_amount, 0) AS opening_balance_amount,
    COALESCE(pi.tax, 0) AS s_tax_rate -- Fetch the Sales Tax Rate from the purchase_items table
FROM 
    ingredient_stock AS ingr_stock
LEFT JOIN 
    ingredients AS ing ON ingr_stock.ingredient_id = ing.id
LEFT JOIN 
    (SELECT ingr_stock.ingredient_id,
            SUM(bt.reserved_qty) AS total_reserved_qty,
            SUM(bt.reserved_qty * ingr_stock.price) AS reserved_amount
     FROM batch_tracking AS bt
     INNER JOIN ingredient_stock AS ingr_stock ON bt.stock_id = ingr_stock.id
     WHERE bt.reserved_on < '$dateFrom'
     GROUP BY ingr_stock.ingredient_id
    ) AS reserved_qty ON ingr_stock.ingredient_id = reserved_qty.ingredient_id
LEFT JOIN 
    purchase AS purch ON ingr_stock.purchase_id = purch.id
LEFT JOIN 
    purchase_items AS pi ON pi.purchase_id = purch.id AND pi.ing_id = ingr_stock.ingredient_id -- Join to get Sales Tax
WHERE 
    purch.create_date < '$dateFrom'
	$filterReportOpeningBalance
GROUP BY 
    ingr_stock.ingredient_id, ing.name, pi.tax -- Group by tax as well to avoid incorrect aggregation
ORDER BY 
    ing.name ASC";

    // Step 2: Transactions Query (for purchases and usage/issue)
    $transactionsQuery = "
    (
        SELECT 
            i.ingredient_id,
            ing.name AS ingredient_name,
            p.create_date AS transaction_date,
            p.purchase_invoice_no AS reference_number,
            sup.name AS supplier_name,
            i.total_qty AS receiving_quantity,
            0 AS issue_quantity,
            i.available_qty AS balance_quantity,
            i.price AS price_per_kg,
            (i.available_qty * i.price) AS amount,
            pi.tax AS s_tax_rate,
            (i.available_qty * i.price * (pi.tax / 100)) AS s_tax_amount,
            NULL AS reserved_date,
            NULL AS finish_product,
            NULL AS batch_name
        FROM ingredient_stock i
        LEFT JOIN ingredients ing ON i.ingredient_id = ing.id
        LEFT JOIN purchase p ON p.id = i.purchase_id
        LEFT JOIN supplier_setup sup ON sup.id = p.supplier_id
        LEFT JOIN purchase_items pi ON pi.purchase_id = p.id AND pi.ing_id = i.ingredient_id
        WHERE p.create_date >= '$dateFrom' AND p.create_date < DATE_ADD('$dateTo', INTERVAL 1 DAY)
        $filterReportTransaction
        GROUP BY i.ingredient_id, i.price, p.create_date, p.code, p.purchase_invoice_no, sup.name, pi.tax
    )
    UNION ALL
    (
        SELECT 
            i.ingredient_id,
            ing.name AS ingredient_name,
            cb.created_date AS transaction_date,
            p.purchase_invoice_no AS reference_number,
            ps.name AS supplier_name,
            0 AS receiving_quantity,
            bt.reserved_qty AS issue_quantity,
            (i.available_qty - bt.reserved_qty) AS balance_quantity,
            i.price AS price_per_kg,
            (i.available_qty * i.price) AS amount,
            pi.tax AS s_tax_rate,
            (i.available_qty * i.price * (pi.tax / 100)) AS s_tax_amount,
            bt.reserved_on AS reserved_date,
            ps.name AS finish_product,
            bts.name AS batch_name
        FROM batch_tracking bt
        LEFT JOIN ingredient_stock i ON bt.stock_id = i.id
        LEFT JOIN ingredients ing ON i.ingredient_id = ing.id
        LEFT JOIN batch_setup bts ON bts.id = bt.batch_id
        LEFT JOIN created_batch cb ON cb.id = bt.created_batch_id
        LEFT JOIN produced_products pp ON pp.created_batch_id = cb.id
        LEFT JOIN product_setup ps ON ps.id = pp.product_id
        LEFT JOIN purchase p ON p.id = i.purchase_id
        LEFT JOIN purchase_items pi ON pi.purchase_id = p.id AND pi.ing_id = i.ingredient_id
        WHERE bt.reserved_on IS NOT NULL AND bt.reserved_on >= '$dateFrom' AND bt.reserved_on < DATE_ADD('$dateTo', INTERVAL 1 DAY)
        $filterReportTransaction
        GROUP BY i.ingredient_id, bt.reserved_on, bts.name, p.purchase_invoice_no, ps.name, i.price, pi.tax
    )
    ORDER BY ingredient_name ASC, transaction_date ASC";
//echo $openingBalanceQuery;
    // Execute the queries
    $openingBalance = $db->fetch_all_array($openingBalanceQuery);
    $transactions = $db->fetch_all_array($transactionsQuery);
  

    // Return the combined result
    return ['opening_balance' => $openingBalance, 'transactions' => $transactions];
}
  
  // Get next FIFO rate (oldest available stock rate) for an ingredient without deduction
public static function GetNextFifoRate($ing_id) {
    global $db;
    $query = "SELECT price 
              FROM " . TABLE_INGREDIENT_STOCK . " 
              WHERE ingredient_id = '$ing_id' 
              AND available_qty > 0 
              ORDER BY batch_date ASC, id ASC 
              LIMIT 1";
    $row = $db->fetch_array($query);
  	echo "<pre>"; print_r($row); echo "</pre>";
    return ($row && isset($row->price)) ? (float)$row->price : 0.0;
}



  
  
  public static function GetRawMaterialStockRegisterTest($dateFrom, $dateTo, $supplier_id, $ingredient_id)
    {
        global $db;  // Assuming $db is the database connection instance.
   // Dynamic filters for each query
    $filterReportOpeningBalance = ""; // For opening balance query (ingredient_stock with alias `ingr_stock` and `purch`)
    $filterReportTransaction = "";    // For transaction query (ingredient_stock with alias `i` and `p`)

    // Apply supplier filter if specified
    if (isset($supplier_id) && $supplier_id != 'All') {
        $filterReportOpeningBalance .= " AND purch.supplier_id = '$supplier_id'";
        $filterReportTransaction .= " AND p.supplier_id = '$supplier_id'";
    }

    // Apply ingredient filter if specified
    if (isset($ingredient_id) && $ingredient_id != 'All') {
        $filterReportOpeningBalance .= " AND ingr_stock.ingredient_id = '$ingredient_id'";
        $filterReportTransaction .= " AND i.ingredient_id = '$ingredient_id'";
    }

        // Step 1: Opening Balance Query
        $openingBalanceQuery = "
    SELECT 
        ingr_stock.ingredient_id,
        ing.name AS ingredient_name,                          -- Fetch ingredient name
         COALESCE(ing.stax, 0) AS stax,							-- Fetch sales tax rate
        SUM(ingr_stock.total_qty) AS total_received_qty,
        SUM(ingr_stock.total_qty * ingr_stock.price) AS total_received_amount,

        -- Aggregate reserved quantity without multiplying by price here
        COALESCE(reserved_qty.total_reserved_qty, 0) AS total_reserved_qty,

        -- Calculate opening balance quantity (received - reserved)
        SUM(ingr_stock.total_qty) - COALESCE(reserved_qty.total_reserved_qty, 0) AS opening_balance_qty,

        -- Calculate opening balance amount accurately by using the individual amounts, not prices
        SUM(ingr_stock.total_qty * ingr_stock.price) 
        - COALESCE(reserved_qty.reserved_amount, 0) AS opening_balance_amount
        
    FROM 
        ingredient_stock AS ingr_stock

    -- Join with ingredients table to get ingredient name
    LEFT JOIN ingredients AS ing ON ingr_stock.ingredient_id = ing.id

    -- Subquery to calculate reserved quantity and amount without double-counting price
    LEFT JOIN (
        SELECT 
            ingr_stock.ingredient_id,
            SUM(bt.reserved_qty) AS total_reserved_qty,
            SUM(bt.reserved_qty * ingr_stock.price) AS reserved_amount
        FROM 
            batch_tracking AS bt
        INNER JOIN 
            ingredient_stock AS ingr_stock ON bt.stock_id = ingr_stock.id
        WHERE 
            bt.reserved_on < '$dateFrom'
        GROUP BY 
            ingr_stock.ingredient_id
    ) AS reserved_qty ON ingr_stock.ingredient_id = reserved_qty.ingredient_id

    LEFT JOIN 
        purchase AS purch ON ingr_stock.purchase_id = purch.id
    WHERE 
        purch.create_date < '$dateFrom'
        $filterReportOpeningBalance
    GROUP BY 
        ingr_stock.ingredient_id, ing.name, ing.stax
    ORDER BY 
        ing.name ASC
     
";

	// echo "<pre>SQL Query: " . $openingBalanceQuery . "</pre>";

        // Step 2: Transactions Query (for purchases and usage/issue)
       $transactionsQuery = "
    SELECT * FROM (
        (
            -- Query for Purchases (Receiving Entries)
            SELECT 
                i.ingredient_id,
                ing.name AS ingredient_name,                   
                p.create_date AS transaction_date,
                p.code AS reference_number,
                p.purchase_invoice_no,                         -- Purchase Invoice No
                sup.name AS supplier_name,
                SUM(i.total_qty) AS receiving_quantity,        
                0 AS issue_quantity,                           
                SUM(i.available_qty) AS balance_quantity,      
                i.price AS price_per_kg,
                SUM(i.available_qty * i.price) AS amount,      
                pi.tax AS s_tax_rate,
                SUM(i.available_qty * i.price * (pi.tax / 100)) AS s_tax_amount, 
                NULL AS reserved_date,                         
                NULL AS finish_product,                        
                NULL AS batch_name                             
            FROM ingredient_stock i
            LEFT JOIN ingredients ing ON i.ingredient_id = ing.id      
            LEFT JOIN purchase p ON p.id = i.purchase_id
            LEFT JOIN supplier_setup sup ON sup.id = p.supplier_id
            LEFT JOIN purchase_items pi ON pi.purchase_id = p.id AND pi.ing_id = i.ingredient_id
            WHERE p.create_date >= '$dateFrom' AND p.create_date < DATE_ADD('$dateTo', INTERVAL 1 DAY)
            $filterReportTransaction
            GROUP BY i.ingredient_id, ing.name, p.create_date, p.code, p.purchase_invoice_no, sup.name, i.price, pi.tax
            ORDER BY ing.name ASC  
        )
        UNION ALL
        (
            -- Query for Usage (Issue Entries)
            SELECT 
                i.ingredient_id,
                ing.name AS ingredient_name,                   
                bt.reserved_on AS transaction_date,            
                bts.name AS reference_number,                  
                p.purchase_invoice_no,                         -- Use purchase's invoice number for usage rows
                ps.name AS supplier_name,                      
                0 AS receiving_quantity,                       
                SUM(bt.reserved_qty) AS issue_quantity,        
                SUM(i.available_qty) AS balance_quantity,      
                i.price AS price_per_kg,
                SUM(i.available_qty * i.price) AS amount,      
                pi.tax AS s_tax_rate,
                SUM(i.available_qty * i.price * (pi.tax / 100)) AS s_tax_amount,
                bt.reserved_on AS reserved_date,
                ps.name AS finish_product,                     
                bts.name AS batch_name                         
            FROM ingredient_stock i
            LEFT JOIN ingredients ing ON i.ingredient_id = ing.id      
            LEFT JOIN batch_tracking bt ON bt.stock_id = i.id
            LEFT JOIN created_batch cb ON cb.id = bt.created_batch_id
            LEFT JOIN produced_products pp ON pp.id = bt.created_batch_id
            LEFT JOIN product_setup ps ON ps.id = pp.product_id        
            LEFT JOIN batch_setup bts ON bts.id = bt.batch_id
            LEFT JOIN purchase p ON p.id = i.purchase_id               -- Join purchase to get purchase_invoice_no
            LEFT JOIN purchase_items pi ON pi.purchase_id = p.id AND pi.ing_id = i.ingredient_id
            WHERE bt.reserved_on IS NOT NULL AND bt.reserved_on >= '$dateFrom' AND bt.reserved_on < DATE_ADD('$dateTo', INTERVAL 1 DAY)
            $filterReportTransaction
            GROUP BY i.ingredient_id, ing.name, bt.reserved_on, bts.name, p.purchase_invoice_no, ps.name, i.price, pi.tax
        )
    ) AS transactions
    ORDER BY ingredient_name ASC, transaction_date ASC;
";


      //	echo "<pre>SQL Query: " . $transactionsQuery . "</pre>";

        // Execute the queries and merge the results
        $openingBalance = $db->fetch_all_array($openingBalanceQuery);
        $transactions = $db->fetch_all_array($transactionsQuery);
     // echo json_encode($openingBalance, JSON_PRETTY_PRINT);

        // Return the combined result
        return ['opening_balance' => $openingBalance, 'transactions' => $transactions];
    }
  
      /*************************************************
    / Generate adjustment code like ISA-000001
    *************************************************/
    public static function GenerateStockAdjustmentCode($id, $prefix = "ISA-", $pad = 6) {
        return $prefix . str_pad((int)$id, (int)$pad, "0", STR_PAD_LEFT);
    }

    /*************************************************
    / Get Purchase List for an Ingredient (ONLY purchases that have remaining > 0)
    / Used for Adjustment dropdown
    *************************************************/
    public static function GetIngredientPurchaseListForAdjustment($ingredient_id) {
        global $db;

        $ingredient_id = (int)$ingredient_id;

        $query = "
            SELECT
                s.purchase_id,
                p.code AS purchase_code,
                p.purchase_invoice_no,
                p.purchase_invoice_date,
                p.create_date AS purchase_create_date,
                sup.name AS supplier_name,
                SUM(s.remaining_qty) AS remaining_qty,
                SUM(s.reserved_qty) AS used_qty
            FROM " . TABLE_INGREDIENT_STOCK . " s
            INNER JOIN purchase p ON p.id = s.purchase_id
            LEFT JOIN supplier_setup sup ON sup.id = p.supplier_id
            WHERE s.ingredient_id = '$ingredient_id'
            GROUP BY s.purchase_id
            HAVING SUM(s.remaining_qty) > 0
            ORDER BY p.purchase_invoice_date ASC, s.purchase_id ASC
        ";

        $rows = $db->fetch_all_array($query);
        return ($rows && $db->affected_rows > 0) ? $rows : false;
    }

    /*************************************************
    / Get Purchase Detail Panel data for selected Ingredient + Purchase
    / Shows: purchase date, purchased qty, used qty, remaining qty, consumed qty
    *************************************************/
    public static function GetIngredientPurchaseDetailForAdjustment($ingredient_id, $purchase_id) {
        global $db;

        $ingredient_id = (int)$ingredient_id;
        $purchase_id   = (int)$purchase_id;

        // Totals from ingredient_stock (used + remaining for that purchase)
        $stockTotalsQuery = "
            SELECT
                COALESCE(SUM(reserved_qty), 0) AS used_qty,
                COALESCE(SUM(remaining_qty), 0) AS remaining_qty
            FROM " . TABLE_INGREDIENT_STOCK . "
            WHERE ingredient_id = '$ingredient_id'
              AND purchase_id   = '$purchase_id'
        ";
        $stockTotals = $db->query_first($stockTotalsQuery);

        // Purchased qty from purchase_items for that ingredient + purchase
        $purchasedQuery = "
            SELECT
                p.purchase_invoice_date,
                p.create_date AS purchase_create_date,
                p.code AS purchase_code,
                p.purchase_invoice_no,
                sup.name AS supplier_name,
                pi.qty AS purchased_qty
            FROM purchase_items pi
            INNER JOIN purchase p ON p.id = pi.purchase_id
            LEFT JOIN supplier_setup sup ON sup.id = p.supplier_id
            WHERE pi.purchase_id = '$purchase_id'
              AND pi.ing_id      = '$ingredient_id'
            LIMIT 1
        ";
        $row = $db->query_first($purchasedQuery);

        if (!$row) return false;

        $used_qty      = isset($stockTotals->used_qty) ? (float)$stockTotals->used_qty : 0.0;
        $remaining_qty = isset($stockTotals->remaining_qty) ? (float)$stockTotals->remaining_qty : 0.0;

        $purchased_qty = isset($row->purchased_qty) ? (float)$row->purchased_qty : 0.0;

        // Consumed qty can be treated as used_qty, but keep this for UI clarity
        $consumed_qty = $purchased_qty - $remaining_qty;
        if ($consumed_qty < 0) $consumed_qty = 0; // safety

        return [
            'purchase_invoice_date' => $row->purchase_invoice_date,
            'purchase_create_date'  => $row->purchase_create_date,
            'purchase_code'         => $row->purchase_code,
            'purchase_invoice_no'   => $row->purchase_invoice_no,
            'supplier_name'         => $row->supplier_name,
            'purchased_qty'         => $purchased_qty,
            'used_qty'              => $used_qty,
            'remaining_qty'         => $remaining_qty,
            'consumed_qty'          => $consumed_qty
        ];
    }

    /*************************************************
    / Deduct-only Stock Adjustment (Ingredient + Purchase)
    / - Updates ingredient_stock: reserved_qty += x, remaining_qty -= x (FIFO by batch_date)
    / - Keeps available_qty in sync with remaining_qty
    / - Inserts log row into ingredient_stock_adjustment and generates ISA-000001 code
    *************************************************/
    public static function DeductStockAdjustmentByPurchase($ingredient_id, $purchase_id, $deduct_qty, $adjustment_date, $reason, $created_by = null) {
        global $db;

        $ingredient_id = (int)$ingredient_id;
        $purchase_id   = (int)$purchase_id;
        $deduct_qty    = (float)$deduct_qty;

        $reason = trim($reason);
        $reason = addslashes($reason); // keep consistent with your existing raw SQL style

        if ($deduct_qty <= 0) {
            return ['status' => false, 'msg' => 'Deduct quantity must be greater than 0'];
        }

        // Start transaction
        $db->query("START TRANSACTION");

        try {
            // Lock lots for this ingredient+purchase (FIFO)
            $lotsQuery = "
                SELECT id, qty, reserved_qty, remaining_qty, batch_date
                FROM " . TABLE_INGREDIENT_STOCK . "
                WHERE ingredient_id = '$ingredient_id'
                  AND purchase_id   = '$purchase_id'
                ORDER BY batch_date ASC, id ASC
                FOR UPDATE
            ";
            $lots = $db->fetch_all_array($lotsQuery);

            if (!$lots) {
                $db->query("ROLLBACK");
                return ['status' => false, 'msg' => 'No stock lots found for selected ingredient and purchase'];
            }

            // Before totals
            $beforeUsed = 0.0;
            $beforeRem  = 0.0;
            foreach ($lots as $l) {
                $beforeUsed += (float)$l['reserved_qty'];
                $beforeRem  += (float)$l['remaining_qty'];
            }

            if ($deduct_qty > $beforeRem) {
                $db->query("ROLLBACK");
                return ['status' => false, 'msg' => 'Deduct quantity cannot exceed remaining quantity'];
            }

            $deduct_left = $deduct_qty;

            foreach ($lots as $lot) {
                if ($deduct_left <= 0) break;

                $lot_id     = (int)$lot['id'];
                $lot_rem    = (float)$lot['remaining_qty'];
                $lot_used   = (float)$lot['reserved_qty'];

                if ($lot_rem <= 0) continue;

                $take = min($lot_rem, $deduct_left);

                $new_rem  = $lot_rem - $take;
                $new_used = $lot_used + $take;

                $new_status = ($new_rem <= 0) ? 'Used' : 'Available';

                // Keep available_qty synced with remaining_qty (many of your reports use available_qty)
                $updateData = [
                    'reserved_qty'   => $new_used,
                    'remaining_qty'  => $new_rem,
                    'available_qty'  => $new_rem,
                    'status'         => $new_status,
                    'last_updated'   => date('Y-m-d H:i:s')
                ];

                $db->query_update(TABLE_INGREDIENT_STOCK, $updateData, "id='$lot_id'");

                $deduct_left -= $take;
            }

            if ($deduct_left > 0) {
                $db->query("ROLLBACK");
                return ['status' => false, 'msg' => 'Unable to deduct full quantity due to insufficient remaining lots'];
            }

            // After totals (recalc)
            $afterTotalsQuery = "
                SELECT
                    COALESCE(SUM(reserved_qty), 0) AS used_qty,
                    COALESCE(SUM(remaining_qty), 0) AS remaining_qty
                FROM " . TABLE_INGREDIENT_STOCK . "
                WHERE ingredient_id = '$ingredient_id'
                  AND purchase_id   = '$purchase_id'
            ";
            $afterTotals = $db->query_first($afterTotalsQuery);
            $afterUsed = isset($afterTotals->used_qty) ? (float)$afterTotals->used_qty : 0.0;
            $afterRem  = isset($afterTotals->remaining_qty) ? (float)$afterTotals->remaining_qty : 0.0;

            // Insert adjustment log
            $now = date('Y-m-d H:i:s');

            // NOTE: using table name directly to avoid dependency on a constant
            $logData = [
                'code'                 => 'TEMP',
                'adjustment_date'      => $adjustment_date,
                'ingredient_id'        => $ingredient_id,
                'purchase_id'          => $purchase_id,
                'deduct_qty'           => $deduct_qty,
                'before_used_qty'      => $beforeUsed,
                'after_used_qty'       => $afterUsed,
                'before_remaining_qty' => $beforeRem,
                'after_remaining_qty'  => $afterRem,
                'reason'               => $reason,
                'created_by'           => $created_by,
                'create_date'          => $now,
                'datetime'             => $now,
                'status'               => 'Posted'
            ];

            $adj_id = $db->query_insert('ingredient_stock_adjustment', $logData);

            if (!$adj_id || $db->affected_rows <= 0) {
                $db->query("ROLLBACK");
                return ['status' => false, 'msg' => 'Failed to insert stock adjustment log. SQL Error: ' . $db->last_error];
            }

            // Generate ISA-000001 code and update
            $code = self::GenerateStockAdjustmentCode($adj_id, "ISA-", 6);
            $db->query("UPDATE ingredient_stock_adjustment SET code='$code' WHERE id='" . (int)$adj_id . "'");

            $db->query("COMMIT");

            return [
                'status' => true,
                'msg'    => 'Stock adjusted successfully',
                'adjustment_id' => (int)$adj_id,
                'code'   => $code,
                'before_remaining' => $beforeRem,
                'after_remaining'  => $afterRem
            ];

        } catch (Exception $e) {
            $db->query("ROLLBACK");
            return ['status' => false, 'msg' => 'Exception: ' . $e->getMessage()];
        }
    }

  
}
  
  
  
 

?>
