<?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 retrieve a produced product by Created Batch ID
    public static function GetProducedProductByCreatedBatchId($createdbatchid) {
        global $db;
        $query = "SELECT * FROM " . TABLE_PRODUCED_PRODUCTS . " WHERE created_batch_id = '$createdbatchid'";
      
        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
   public static function DeductStockFIFO($product_id, $required_qty, $invoice_id) {
    global $db;

    $product_id   = (int)$product_id;
    $invoice_id   = (int)$invoice_id;
    $required_qty = (float)$required_qty;

    if ($product_id <= 0 || $invoice_id <= 0 || $required_qty <= 0) {
        throw new Exception("Invalid deduction request for product ID {$product_id} and invoice ID {$invoice_id}.");
    }

    $total_deducted = 0.0;

    // IMPORTANT:
    // Do NOT reverse old stock here.
    // Reversal must be handled explicitly by the caller during edit.
    $stocks = self::GetProducedProductsFIFO($product_id);

    if (!$stocks || !is_array($stocks)) {
        throw new Exception("No FIFO stock found for product ID {$product_id}.");
    }

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

        $available_qty = (float)$stock->remaining_quantity;
        $used_qty      = (float)$stock->used_quantity;

        if ($available_qty <= 0) {
            continue;
        }

        $deduct_qty = min($available_qty, $required_qty);
        if ($deduct_qty <= 0) {
            continue;
        }

        $new_remaining_qty = $available_qty - $deduct_qty;
        $new_used_qty      = $used_qty + $deduct_qty;

        $upd = self::UpdateProducedProduct($stock->id, [
            'remaining_quantity' => $new_remaining_qty,
            'used_quantity'      => $new_used_qty
        ]);

        if ($upd === false) {
            throw new Exception("Failed to update produced batch ID {$stock->id} for product ID {$product_id}.");
        }

        $deductionData = [
            'produced_product_id' => (int)$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
        ];

        $psdId = ProductStockDeductions::AddProductStockDeduction($deductionData);
        if (!$psdId) {
            throw new Exception("Failed to insert PSD row for product ID {$product_id}, batch ID {$stock->id}, invoice ID {$invoice_id}.");
        }

        $required_qty   -= $deduct_qty;
        $total_deducted += $deduct_qty;
    }

    if (round($required_qty, 4) > 0) {
        throw new Exception("Insufficient FIFO stock for product ID {$product_id}. Short by {$required_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)
{
    $productId   = (int)$productId;
    $invoiceId   = (int)$invoiceId;
    $reversedQty = (float)$reversedQty;

    if ($productId <= 0 || $invoiceId <= 0 || $reversedQty <= 0) {
        throw new Exception("Invalid reversal request for product ID {$productId} and invoice ID {$invoiceId}.");
    }

    $deductions = self::GetProductStockDeductionsByInvoiceIdAndProductId($invoiceId, $productId);

    if (!$deductions || !is_array($deductions) || count($deductions) == 0) {
        // Nothing to reverse is a valid state in some historical broken cases.
        return true;
    }

    // Reverse in reverse PSD id order (latest rows first)
    usort($deductions, function($a, $b) {
        return (int)$b->id - (int)$a->id;
    });

    foreach ($deductions as $deduction) {
        if ($reversedQty <= 0) {
            break;
        }

        $deductedQty = (float)$deduction->deducted_quantity;
        if ($deductedQty <= 0) {
            continue;
        }

        $producedProduct = self::GetProducedProductById($deduction->produced_product_id);
        if (!$producedProduct) {
            throw new Exception("Produced batch not found for PSD row ID {$deduction->id}.");
        }

        $restoreQty = min($reversedQty, $deductedQty);

        $newRemainingQty = (float)$producedProduct->remaining_quantity + $restoreQty;
        $newUsedQty      = (float)$producedProduct->used_quantity - $restoreQty;

        if ($newUsedQty < 0) {
            $newUsedQty = 0;
        }

        $upd = self::UpdateProducedProduct($producedProduct->id, [
            'remaining_quantity' => $newRemainingQty,
            'used_quantity'      => $newUsedQty
        ]);

        if ($upd === false) {
            throw new Exception("Failed to restore produced batch ID {$producedProduct->id}.");
        }

        $remainingDeductionQty = $deductedQty - $restoreQty;

        if ($remainingDeductionQty <= 0) {
            $del = ProductStockDeductions::DeleteProductStockDeduction($deduction->id);
            if (!$del) {
                throw new Exception("Failed to delete PSD row ID {$deduction->id} during reversal.");
            }
        } else {
            $updPsd = ProductStockDeductions::UpdateStockDeduction($deduction->id, [
                'deducted_quantity' => $remainingDeductionQty
            ]);

            if (!$updPsd) {
                throw new Exception("Failed to update PSD row ID {$deduction->id} during reversal.");
            }
        }

        $reversedQty -= $restoreQty;
    }

    if (round($reversedQty, 4) > 0) {
        throw new Exception("Could not fully reverse stock for product ID {$productId}. Remaining reversal qty: {$reversedQty}.");
    }

    return true;
}
  //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
    }
}
  
   // Retrieve original stock deductions for the invoice
  public static function RestoreStockForCancelledInvoice($invoiceId)
{
    // Retrieve original stock deductions for the invoice
    $original_deductions = ProductStockDeductions::GetProductStockDeductionByInvoiceId($invoiceId);
    //error_log("Original Deductions: " . print_r($original_deductions, true));

    if (!empty($original_deductions)) {
        foreach ($original_deductions as $deduction) {
            // Log the produced_product_id to ensure it's being retrieved correctly
          //  error_log("Produced Product ID from Deduction: " . $deduction['produced_product_id']);

            // Fetch the produced product details using produced_product_id
            $produced_product_data = ProducedProducts::GetProducedProductById($deduction['produced_product_id']);

            if (!$produced_product_data) {
                error_log("Failed to fetch Produced Product data for produced_product_id: " . $deduction['produced_product_id']);
                continue; // Skip this iteration if data retrieval failed
            }

            // Log the original produced product data for debugging
            //error_log("Produced Product Data Before Restoration: " . print_r($produced_product_data, true));

            // Fetch and validate the current remaining and used quantities
            $current_remaining_quantity = (float)$produced_product_data->remaining_quantity;
            $current_used_quantity = (float)$produced_product_data->used_quantity;
            $deducted_quantity = (float)$deduction['deducted_quantity']; // Use array notation

            // Calculate the new remaining quantity and used quantity
            $updated_remaining_quantity = $current_remaining_quantity + $deducted_quantity;
            $updated_used_quantity = $current_used_quantity - $deducted_quantity;

            // Ensure the used quantity does not go below zero
            if ($updated_used_quantity < 0) {
                $updated_used_quantity = 0;
            }

            // Log calculated values before updating
         //   error_log("Calculated remaining quantity: $updated_remaining_quantity");
           // error_log("Calculated used quantity: $updated_used_quantity");

            // Update the produced product in the database
            ProducedProducts::UpdateProducedProduct($deduction['produced_product_id'], [
                'remaining_quantity' => $updated_remaining_quantity,
                'used_quantity' => $updated_used_quantity
            ]);

            // Log the final updated quantities
          //  error_log("Restored product ID: {$deduction['produced_product_id']} | Final Remaining Qty: $updated_remaining_quantity | Final Used Qty: $updated_used_quantity");
        }

        // Delete the original deduction records after restoring
        if (ProductStockDeductions::DeleteProductStockDeductionByInvoiceId($invoiceId)) 
          {
          //  error_log("Successfully deleted stock deductions for invoice ID: $invoiceId");
          } else 
           {
            error_log("Failed to delete stock deductions for invoice ID: $invoiceId");
            }
        } else 
             {
        error_log("No deductions found for invoice ID: $invoiceId");
             }
}
  
public static function GetProductStockSummary() {
    global $db;

    $query = "
        SELECT 
            ps.name AS product_name,
            ps.carton_pieces AS carton_pieces,
            SUM(pp.remaining_quantity) AS total_remaining_quantity
        FROM 
            produced_products pp
        JOIN 
            product_setup ps 
        ON 
            pp.product_id = ps.id
        GROUP BY 
            pp.product_id
        ORDER BY 
            ps.name ASC
    ";

    // Execute the query
    $rows = $db->fetch_all_array($query);
//print_r ($rows);
    // Check if any rows were returned and return the result
    if ($rows) {
        return $rows;
    } else {
        return false;  // Return false if no data is found
    }
}
  
  public static function GetTotalQuantityByProductId($product_id) {
    global $db;

    $query = "
        SELECT 
            ps.name AS product_name,
            ps.carton_pieces AS carton_pieces,
            SUM(pp.remaining_quantity) AS total_remaining_quantity
        FROM 
            produced_products pp
        JOIN 
            product_setup ps 
        ON 
            pp.product_id = ps.id
        WHERE 
            pp.product_id = '$product_id'
        GROUP BY 
            pp.product_id
    ";

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

    // Check if any row was returned and return the result
    if ($row) {
        return $row;
    } else {
        return false;  // Return false if no data is found
    }
}





  
}


?>
