<?php
class ProducedProducts {

    // Method to add a new produced product entry
    public static function AddProducedProduct($data) {
        global $db;
        return $db->query_insert(TABLE_PRODUCED_PRODUCTS, $data);
    }
   

    // Method to retrieve a produced product by its ID
    public static function GetProducedProductById($id) {
        global $db;
        $query = "SELECT * FROM " . TABLE_PRODUCED_PRODUCTS . " WHERE id = '$id'";
        return $db->query_first($query);
    }

    // Method to update a produced product entry
    public static function UpdateProducedProduct($id, $data) {
        global $db;
        return $db->query_update(TABLE_PRODUCED_PRODUCTS, $data, "id='$id'");
    }

    // Method to retrieve produced products in FIFO order (oldest first)
    public static function GetProducedProductsFIFO($product_id) {
        global $db;
        $query = "SELECT * FROM " . TABLE_PRODUCED_PRODUCTS . " WHERE product_id = '$product_id' AND remaining_quantity > 0 ORDER BY production_date ASC, id ASC";
        return $db->fetch_all_array($query);
    }

    // FIFO method to deduct stock when creating an invoice
   // FIFO method to deduct stock when creating an invoice
public static function DeductStockFIFO($product_id, $new_qty, $invoice_id) {
    global $db;

    // Step 1: Fetch the old quantity from invoice products before updating
    $old_qty = self::GetPreviousQuantityFromInvoice($product_id, $invoice_id);

    // Step 2: Reverse the old quantity in the produced products table
    if ($old_qty > 0) {
        self::ReverseStockFIFO($product_id, $old_qty, $invoice_id);
    }

    // Step 3: Deduct the new quantity using the existing logic
    $required_qty = $new_qty;  // New quantity is now the required quantity
    $total_deducted = 0;

    // Get available stock in FIFO order
    $stocks = self::GetProducedProductsFIFO($product_id);

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

        $available_qty = $stock->remaining_quantity;
        $used_qty = $stock->used_quantity;

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

        // Calculate the amount to deduct
        $deduct_qty = min($available_qty, $required_qty);

        // Update the stock entry
        $remaining_qty = $available_qty - $deduct_qty;
        $used_qty += $deduct_qty;

        self::UpdateProducedProduct($stock->id, ['remaining_quantity' => $remaining_qty, 'used_quantity' => $used_qty]);

        // Record the deduction in the ProductStockDeductions table
        $deductionData = [
            'produced_product_id' => $stock->id,
            'invoice_id' => $invoice_id,
          	'product_id' => $product_id,
            'deduction_date' =>  date('Y-m-d'),
            'deducted_quantity' => $deduct_qty,
            'cost_per_unit' => $stock->unit_cost_produced
        ];

        ProductStockDeductions::AddProductStockDeduction($deductionData);

        // Decrease the required quantity
        $required_qty -= $deduct_qty;
        $total_deducted += $deduct_qty;
    }

    return $total_deducted;
}

// Method to get the previous quantity from the invoice before updating
private static function GetPreviousQuantityFromInvoice($product_id, $invoice_id) {
    global $db;
    $query = "SELECT product_qty FROM " . TABLE_INVOICE_PRODUCT . " WHERE product_id = '$product_id' AND invoice_id = '$invoice_id'";
    $result = $db->query_first($query);
    return $result->product_qty;
}


  public static function GetTotalAvailableQuantityFIFO($product_id) {
    global $db; // Ensure that the global $db is being used

    if (!$db) {
        error_log("Database connection is not initialized.");
        return 0; // Handle the case where the $db object is not initialized
    }

    // Query to sum available quantities in FIFO order
    $query = "SELECT SUM(remaining_quantity) as total_qty FROM " . TABLE_PRODUCED_PRODUCTS . " WHERE product_id = $product_id AND remaining_quantity > 0 ORDER BY production_date ASC";
    

    $result = $db->query($query);

    if (!$result) {
        error_log("SQL Error: " . $db->error);
        return 0;
    }

    $row = $result->fetch_assoc();
    return $row['total_qty'] ?? 0; // Return total quantity or 0 if none available
}

  
  public static function IsBatchProduced($created_batch_id) {
        global $db;
        $query = "SELECT COUNT(*) as count FROM " . TABLE_PRODUCED_PRODUCTS ."  WHERE created_batch_id = '$created_batch_id'";
        $result = $db->query_first($query);
        return $result->count > 0; // Returns true if a record exists, false otherwise
    }

    // Method to get produced product data by batch id
    public static function GetProducedProductDataByBatchId($created_batch_id) {
        global $db;
        $query = "SELECT * FROM " . TABLE_PRODUCED_PRODUCTS ."  WHERE created_batch_id = '$created_batch_id'";
        return $db->query_first($query);
    }
  
 public static function GetWeightedAverageCostPriceByProductId($product_id, $required_qty) {
    global $db;
        // Fetch stock entries for the product ordered by production date (FIFO)
    $query = "
        SELECT 
            unit_cost_produced, 
            remaining_quantity 
        FROM 
            " . TABLE_PRODUCED_PRODUCTS ." 
        WHERE 
            product_id = '$product_id' ";

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

    $total_weighted_cost = 0;
    $total_qty_deducted = 0;

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

        // Assuming $stock is an associative array
        $available_qty = $stock->remaining_quantity; // Use associative array notation
        $unit_cost = $stock->unit_cost_produced; // Use associative array notation

      
        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);

        // Calculate the weighted cost for this portion
        $total_weighted_cost += $deduct_qty * $unit_cost;
        $total_qty_deducted += $deduct_qty;

        // Decrease the required quantity
        $required_qty -= $deduct_qty;

        
    }

    // Return the weighted average cost price
    $average_cost = $total_qty_deducted > 0 ? $total_weighted_cost / $total_qty_deducted : 0;
  

    return $average_cost;
}
  
  public static function RestoreStock($product_id, $quantity, $invoice_id) {
    global $db;
    // Get stock entries in LIFO order to restore
    $query = "SELECT * FROM ". TABLE_PRODUCED_PRODUCTS . "WHERE product_id = '$product_id' ORDER BY production_date DESC";
    $result = $db->query($query);

    while ($row = $result->fetch_assoc() && $quantity > 0) {
        $restore_qty = min($row['used_quantity'], $quantity);
        $db->query("UPDATE " . TABLE_PRODUCED_PRODUCTS ."  SET remaining_quantity = remaining_quantity + $restore_qty, used_quantity = used_quantity - $restore_qty WHERE id = '{$row['id']}'");
        $quantity -= $restore_qty;
    }
}
  
 /* public static function UpdateProducedProduct($product_id, $fields) {
    global $db;
    $set_clause = [];
    foreach ($fields as $field => $value) {
        $set_clause[] = "$field = '$value'";
    }
    $set_clause_str = implode(', ', $set_clause);

    $query = "UPDATE" . TABLE_PRODUCED_PRODUCTS . "SET $set_clause_str WHERE product_id = '$product_id'";
    return $db->query($query);
}

*/
public static function ReverseStockFIFO($productId, $reversedQty, $invoiceId)
{
  
  // Log the invoiceId and productId to ensure they are correct
    error_log("Invoice ID: " . $invoiceId);
    error_log("Product ID: " . $productId);

    // Fetch the stock deduction entries for the specific invoice and product
    $deductions = SELF::GetProductStockDeductionsByInvoiceIdAndProductId($invoiceId, $productId);
error_log("Fetch the stock deduction entries for the specific invoice and product". print_r($deductions, true));
  
      foreach ($deductions as $deduction) {
        
                        error_log("Deduction details: " . print_r($deduction, true));

        $producedProduct = ProducedProducts::GetProducedProductById($deduction->produced_product_id);
      
        error_log("Produced product details: " . print_r($producedProduct, true));

        // Calculate the quantity to be restored
        $restoreQty = min($reversedQty, $deduction->deducted_quantity);
        
     error_log("Calculate the quantity to be restored".$restoreQty);
        
        // Update the produced product stock
        $newRemainingQty = $producedProduct->remaining_quantity + $restoreQty;
        $newUsedQty = $producedProduct->used_quantity - $restoreQty;
         error_log("        // Update the produced product stoc newRemainingQty".$newRemainingQty);
        error_log("// Update the produced product stock newUsedQty".$newUsedQty);
        // Update the produced products table with new quantities
        ProducedProducts::UpdateProducedProduct($producedProduct->id, [
            'remaining_quantity' => $newRemainingQty,
            'used_quantity' => $newUsedQty
        ]);

        // Update the deduction entry to reflect the restored quantity
        ProductStockDeductions::UpdateStockDeduction($deduction->id, [
            'deducted_quantity' => $deduction->deducted_quantity - $restoreQty
        ]);

        // Decrease the reversed quantity by the amount restored
        $reversedQty -= $restoreQty;

        // Break the loop if the full reversed quantity has been restored
        if ($reversedQty <= 0) {
            break;
        }
    }

    // If there are no deductions left, delete the entry from ProductStockDeductions
    if ($reversedQty > 0) {
        SELF::DeleteProductStockDeductionsByInvoiceIdAndProductId($invoiceId, $productId);
    }
}
  //Get product stock dedection method
  //**************************************//
  public static function GetProductStockDeductionsByInvoiceIdAndProductId($invoice_id, $product_id) {
    global $db; // Ensure that the global $db is being used

    if (!$db) {
        error_log("Database connection is not initialized.");
        return []; // Return an empty array or handle accordingly
    }

    $query = "SELECT * FROM " . TABLE_PRODUCT_STOCK_DEDUCTIONS . " WHERE invoice_id = '$invoice_id' AND product_id = '$product_id'";
    // Log the query before executing
    error_log("Executing query: " . $query . " with Invoice ID: " . $invoice_id . " and Product ID: " . $product_id);

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

   // return $result; // Return the fetched result as an array
    return is_array($result) ? $result : [];
}
  
  //**************************************//

   public static function DeleteDeduction($product_id, $invoice_id) {
    global $db;
    $query = "DELETE FROM". TABLE_PRODUCT_STOCK_DEDUCTIONS . "WHERE product_id = '$product_id' AND invoice_id = '$invoice_id'";
    return $db->query($query);
}

public static function DeleteProductStockDeductionsByInvoiceIdAndProductId($invoice_id, $product_id) {
    global $db; // Ensure that the global $db is being used

    // Construct the SQL query to delete the records
    $query = "DELETE FROM " . TABLE_PRODUCT_STOCK_DEDUCTIONS . " WHERE invoice_id = '$invoice_id' AND product_id = '$product_id'";

    // Execute the query
    $result = $db->query($query);

    // Check if the deletion was successful
    if ($db->affected_rows > 0) {
        return true; // Return true if records were successfully deleted
    } else {
        return false; // Return false if no records were deleted
    }
}

  
}


?>
