<?php

class BatchSetup {

    /*************************************************
    / This function adds a new Batch
    *************************************************/
    public static function AddBatch($data) {
    global $db;  // Assuming $db is the database connection object

    $id = $db->query_insert(TABLE_BATCH_SETUP, $data);  // Check if this is the correct method
    if ($db->affected_rows > 0) {
        return $id;
    } else {
        // Debugging: Output the last SQL error
        echo "SQL Error: " . $db->last_error;  // Add this line to show the last SQL error
        return false;
    }
}
// AddBatch

    /*************************************************
    / This function adds new Batch Items
    *************************************************/
   public static function AddBatchItem($data) {
    global $db;
    
    // Insert data into batch_items table
    $id = $db->query_insert(TABLE_BATCH_ITEMS, $data);
    if ($db->affected_rows > 0) {
        return $id;
    } else {
        echo "SQL Error: " . $db->last_error;  // Debugging SQL error if any
        return false;
    }
}
 // AddBatchItem

    /*************************************************
    / This function updates a Batch
    *************************************************/
    public static function UpdateBatch($batchId, $data) {
        global $db;
        $id = $db->query_update(TABLE_BATCH_SETUP, $data, "id='$batchId'");
        if ($db->affected_rows > 0) {
            return $id;
        } else {
            return false;
        }
    } // UpdateBatch
  
  /*************************************************
    / This function updates a Batch
    *************************************************/
    public static function UpdateBatchSetup($batchId, $data) {
        global $db;
        $id = $db->query_update(TABLE_BATCH_SETUP, $data, "id='$batchId'");
        if ($db->affected_rows > 0) {
            return $id;
        } else {
            return false;
        }
    } // UpdateBatch
  
  

    /*************************************************
    / Get Batch by ID
    *************************************************/
    public static function GetBatchById($id) {
        global $db;
        $query = "SELECT * FROM " . TABLE_BATCH_SETUP . " WHERE id ='$id'";
        $rows = $db->query_first($query);
      

        if ($db->affected_rows > 0) {
            return $rows;
        } else {
            return false;
        }
    } // GetBatchById
  
   /*************************************************
    / Get Batch by ID
    *************************************************/
    public static function GetBatchSetupById($id) {
        global $db;
        $query = "SELECT * FROM " . TABLE_BATCH_SETUP . " WHERE id ='$id' ORDER BY id ASC ";
        $rows = $db->query_first($query);
              if ($db->affected_rows > 0) {
            return $rows;
        } else {
            return false;
        }
    } // GetBatchById
  
     /*************************************************
    / Get Batch by ID
    *************************************************/
    public static function GetCreatedBatchSetupById($id) {
        global $db;
        $query = "SELECT * FROM " . TABLE_CREATED_BATCH . " WHERE id ='$id' ";
        $rows = $db->query_first($query);
              if ($db->affected_rows > 0) {
            return $rows;
        } else {
            return false;
        }
    } // GetBatchById
  

    /*************************************************
    / Get Batch Items
    *************************************************/
    public static function GetBatchItems($filter = "", $pageno = "null", $pagesize = "null") {
        global $db;

        // Check if the $db object is available
       // if (!$db) {
      //      die("Database connection not initialized."); // Stop execution if $db is not initialized
      //  }

        $query = "SELECT * FROM " . TABLE_BATCH_ITEMS . " " . $filter;
        $rows = $db->fetch_all_array($query, $pageno, $pagesize);

        if ($db->affected_rows > 0) {
            return $rows;
        } else {
            return false;
        }
    } // GetBatchItems

    /*************************************************
    / Delete Batch Items
    *************************************************/
    public static function DeleteBatchItems($batch_id) {
        global $db;
        $query = "DELETE FROM " . TABLE_BATCH_ITEMS . " WHERE batch_id='$batch_id'";
        $db->query($query);
        if ($db->affected_rows > 0) {
            return true;
        } else {
            return false;
        }
    } // DeleteBatchItems

    /*************************************************
    / Get Batch List
    *************************************************/
    public static function GetBatchList($filter = "", $pageno = "null", $pagesize = "null") {
        global $db;
        $query = "SELECT * FROM " . TABLE_BATCH_SETUP . " " . $filter;
        $rows = $db->fetch_all_array($query, $pageno, $pagesize);
        if ($db->affected_rows > 0) {
            return $rows;
        } else {
            return false;
        }
    } // GetBatchList

  
  /*************************************************
    / Get Batch List
    *************************************************/
    public static function GetBatchSetupList($filter = "", $pageno = "null", $pagesize = "null") {
        global $db;
        $query = "SELECT * FROM " . TABLE_BATCH_SETUP . " " . $filter;
        $rows = $db->fetch_all_array($query, $pageno, $pagesize);
        if ($db->affected_rows > 0) {
            return $rows;
        } else {
            return false;
        }
    } // GetBatchList

  
    /*************************************************
    / Update remaining stock in batch items
    *************************************************/
    public static function UpdateBatchItemStock($batch_item_id, $used_qty) {
        global $db;
        $query = "UPDATE " . TABLE_BATCH_ITEMS . " SET remaining_qty = remaining_qty - $used_qty WHERE id = '$batch_item_id'";
        $db->query($query);
    } // UpdateBatchItemStock
  
  /*************************************************
    / This function checks if a Batch already exists
    *************************************************/
    public static function CheckBatchAccount($name, $id) {
        global $db;
        $query = "SELECT * FROM " . TABLE_BATCH_SETUP . " WHERE name = '$name' AND id <> '$id'";
        $rows = $db->query_first($query);
        if ($db->affected_rows > 0) {
            return $rows;
        } else {
            return false;
        }
    } // CheckBatchAccount
  
  public static function UpdateBatchItem($batch_item_id, $data) {
    global $db;
    
    // Update the data in the batch_items table where id matches the given batch_item_id
    $result = $db->query_update(TABLE_BATCH_ITEMS, $data, "id='$batch_item_id'");
    
    if ($db->affected_rows > 0) {
        return $result;
    } else {
        echo "SQL Error: " . $db->last_error;  // Debugging SQL error if any
        return false;
    }
}
  
 
  
public static function GetBatchItemsByBatchId($batch_id) {
    global $db;
   // $db = new Database("mariadb-123.wc1.phx1.stabletransit.com", "2023011_root6", "FLdevelopers3", "2023011_dadisale1tx");
    //$db->connect();
 
    if (!$db) {
        die("Database connection not initialized.");
    }

    $query = "SELECT * FROM " . TABLE_BATCH_ITEMS . " WHERE batch_id = '$batch_id'";

    // Log the query for debugging
 //   error_log("SQL Query: " . $query);

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

    // Check if the query was successful
    if ($rows === false) {
        error_log("Query failed: " . $db->last_error);
        die("Query execution failed.");
    }

   // error_log("Query Result: " . print_r($rows, true));

    if ($db->affected_rows > 0) {
        return $rows;
    } else {
        return false;
    }
}

 public static function CheckDbInitialization() {
        global $db;

        if (isset($db) && $db) {
            echo "Database connection is properly initialized.";
        } else {
            echo "Database connection is NOT initialized.";
        }
 }
  
  
  	public static function AddNewBatchCreated($data) {
        global $db;

        // Insert the batch data into the created_batch table
        $id = $db->query_insert('created_batch', $data);

        if ($db->affected_rows > 0) {
            return $id;
        } else {
            echo "SQL Error: " . $db->last_error;
            return false;
        }
    }
     public static function GetBatchDetailsByBatchId($filter = "") {
        global $db;

		$query = "SELECT 
            cb.id AS created_batch_id,  /* Fetching the ID of the created_batch */
            bs.name AS batch_name, 
            bs.batch_date,
            ps.name AS product_name,
            cb.created_qty,
            cb.created_date,
            cb.batch_id
          FROM " . TABLE_CREATED_BATCH . " cb
          INNER JOIN " . TABLE_BATCH_SETUP . " bs ON cb.batch_id = bs.id
          INNER JOIN " . TABLE_PRODUCTSETUP . " ps ON bs.product_id = ps.id
          " . $filter . "
          ORDER BY cb.id DESC";
	//	echo $query;
        $rows = $db->fetch_all_array($query);

        if ($db->affected_rows > 0) {
            return $rows;
        } else {
            return false;
        }
    }
      public static function GetBatchTrackingData($filter = "") {
        global $db;

        // Construct the query with the filter
        $query = "SELECT * FROM " . TABLE_BATCH_TRACKING . " " . $filter;
        $rows = $db->fetch_all_array($query);

        // Convert the PHP array to JSON for debugging in the console
        $rows_json = json_encode($rows);
    
        // Check if the query returned any rows
        if (!empty($rows)) {
            return $rows;
        } else {
            return false;
        }
    }
  
     public static function GetDeductedIngQuantity($filter = "") {
        global $db;

        // Construct the base query
        $query = "SELECT bt.*, ing.deductible 
                  FROM " . TABLE_BATCH_TRACKING . " bt
                  INNER JOIN " . TABLE_INGSETUP . " ing ON bt.ingredient_id = ing.id
                  WHERE ing.deductible = 1";
       
        // Append the filter to the existing WHERE clause
        if (!empty($filter)) {
            $query .= " " . $filter;
        }

        $rows = $db->fetch_all_array($query);
   
        $rows_json = json_encode($rows);
                  //     echo "<script>alert('deductible $rows_json');</script>";


         if ($db->affected_rows > 0) {
          
              return $rows;
          } else {
              return false;
          }
    }
  
  
  
  
public static function GetDeductedIngQuantityAll($filter = "") {
    global $db;

    // Construct the base query
    $query = "SELECT bt.*, ing.deductible 
              FROM " . TABLE_BATCH_TRACKING . " bt
              INNER JOIN " . TABLE_INGSETUP . " ing ON bt.ingredient_id = ing.id";
 

    // Append the filter to the existing WHERE clause
    if (!empty($filter)) {
        $query .= " " . $filter;
    }

    // Print the final query for debugging
  //  echo "<script>alert('Final Query: " . $query . "');</script>";

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

    // Check if rows are not empty and return them
    if (!empty($rows)) {
        return $rows;
    } else {
      //  echo "<script>alert('No data fetched for the query');</script>";
        return false;
    }
}



  
  
  
  
  
  
  
  
     public static function GetBatchByBatchId($batch_id) {
          global $db;

          // Construct the query to fetch batch details by batch_id
          $query = "SELECT * FROM " . TABLE_BATCH_TRACKING . " WHERE id = '$batch_id'";

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

          // Check if a result was returned and return the batch data
          return $result ? $result : false;
      }
  
  
      public static function GetProductById($product_id) {
          global $db;

          // Construct the query to fetch product details by product_id
          $query = "SELECT * FROM " . TABLE_PRODUCTSETUP . " WHERE id = '$product_id'";

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

          // Check if a result was returned and return the product data
          return $result ? $result : false;
      }
  
  
       public static function GetBatchIdByCreatedBatchId($created_batch_id) {
            global $db;

            // Construct the query to fetch batch_id using created_batch_id
            $query = "SELECT batch_id FROM " . TABLE_BATCH_TRACKING . " WHERE created_batch_id = '$created_batch_id' LIMIT 1;";
          
           $result = $db->query($query);
         $itemDetails = json_encode($result);
    	//	echo "<script>alert('Created Batch ID: {$itemDetails}');</script>";
              // Fetch a single row as an associative array
              if ($result && $row = $result->fetch_assoc()) {
                	$batch_id = $row['batch_id'];
                
                 // Now, use the batch_id to fetch product_id from the batch_setup table
        			$batchQuery = "SELECT product_id FROM " . TABLE_BATCH_SETUP . " WHERE id = '$batch_id' LIMIT 1;";
            	
                	 $batchResult = $db->query($batchQuery);
                
                	if ($batchResult && $row = $batchResult->fetch_assoc()) {
                        $product_id = $row['product_id'];
                      
                      	$productQuery = "SELECT weight, unit FROM " . TABLE_PRODUCTSETUP . " WHERE id = '$product_id' LIMIT 1;";
            			$productResult = $db->query($productQuery);
                      
                     	if ($productResult && $productRow = $productResult->fetch_assoc()) {
                          $weight = $productRow['weight'];
                          $product_In_Kg = number_format($weight / 1000, 2);

                          return [
                              'batch_id' => $batch_id,
                              'product_id' => $product_id,
                              'product_In_Kg' => $product_In_Kg,
                            
                          ];
                      } else {
                          echo "<script>console.log('No Product Data found for Product ID " . $product_id . "');</script>";
                          return false;
                      }
                    } 
                	
              } else {
                  // Log an error or null result to the console
                  echo "<script>console.log('No Batch ID found for Created Batch ID " . $created_batch_id . "');</script>";
                  return false;
              }

        
                      }
                // ADD DATA IN PRODUCED_PRODUCTS TABLE AS FINISHED GOODS STOCK
                public static function AddProducedProduct($data) {
                  	global $db;
                
                // echo "<script>console.log('Data to insert into production_details: " . json_encode($data) . "');</script>";
                  
                      // Manually build the insert query for debugging
                  // Remove commas from numeric values before constructing the SQL query
                    $data['total_produced_quantity'] = str_replace(',', '', $data['total_produced_quantity']);
                    $data['unit_cost_produced'] = str_replace(',', '', $data['unit_cost_produced']);
                    $data['total_cost'] = str_replace(',', '', $data['total_cost']);
                    $data['wastage_qty'] = str_replace(',', '', $data['wastage_qty']);    

                     // Now construct the query as before
						$query = "INSERT INTO " . TABLE_PRODUCED_PRODUCTS . " (" . implode(", ", array_keys($data)) . ") VALUES ('" . implode("', '", array_values($data)) . "')";

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

                      // Check for errors
                      if (!$result) {
                          $error = $db->last_error;
                         // echo "<script>alert('Failed to insert data: $error');</script>";
                      }
                  $result = $db->query("SELECT LAST_INSERT_ID() as id");
                  $producedProductId = $result->fetch_assoc()['id'];
          
                         return  $producedProductId;

              }

				// ADD DATA IN production_details  TABLE FO RFINISHED GOODS STOCK USED INGREDIENTS
                public static function AddProductionDetail($data) {
                  		 global $db;

                        // Log the data before insertion
                     //   echo "<script>console.log('Data to insert into production_details: " . json_encode($data) . "');</script>";

                        // Build the insert query
                        $query = "INSERT INTO " . TABLE_PRODUCTION_DETAILS . " (" . implode(", ", array_keys($data)) . ") VALUES ('" . implode("', '", array_values($data)) . "')";
                      //  echo "<script>console.log('Insert Query for Production Detail: " . addslashes($query) . "');</script>";

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

                        // Log the result
                   /*     if ($result) {
                            echo "<script>console.log('Insert into production_details successful');</script>";
                        } else {
                            $error = $db->last_error;
                            echo "<script>alert('Failed to insert production detail: $error');</script>";
                        }

                        return $result;*/
                }

  				// product_stock_deductions TO IMPLEMENT FIFO WHEN DEDUCTING FOR INVOICE 
  
                  public static function AddProductStockDeduction($data) {
                    global $db;
                    return $db->query_insert(TABLE_PRODUCT_STOCK_DEDUCTIONS, $data);
                }


                            /*************************************************
                            / This function deletes a specific batch item
                            /*************************************************/
              public static function DeleteBatchItem($batch_id, $ing_id) {
                global $db;
                $query = "DELETE FROM " . TABLE_BATCH_ITEMS . " WHERE batch_id='$batch_id' AND ing_id='$ing_id'";
                $db->query($query);
                if ($db->affected_rows > 0) {
                  return true;
                } else {
                  return false;
                }
              }
  
   public static function GetBatchTrackingDetailsByCreatedBatchId($created_batch_id) {
    global $db;
    
    // Query to fetch ingredient name and reserved quantity from batch_tracking and ingredients tables
    $query = "SELECT 
                bt.ingredient_id, 
                bt.reserved_qty,
                bt.stock_id,
                ing.name AS ingredient_name
              FROM " . TABLE_BATCH_TRACKING . " bt
              INNER JOIN " . TABLE_INGSETUP . " ing ON bt.ingredient_id = ing.id
              WHERE bt.created_batch_id = '$created_batch_id'";
    
    // Execute the query
    $rows = $db->fetch_all_array($query);

    if ($rows) {
        return $rows;
    } else {
        return false;
    }
}




  }

?>
