<?php

class ProductionSetup {

    /*************************************************
    / This function adds new Production Setup
    *************************************************/
    public static function AddProductionSetup($data) {
        global $db;

        // Deduct stock using FIFO method
        $product_id = $data['product_id'];
        $required_qty = $data['batch_qty'];
        
        $deducted = ProductionStock::DeductStockFIFO($product_id, $required_qty);
        
        if ($deducted === false || $deducted < $required_qty) {
            // Not enough stock available
            return false;
        }

        // Continue with the existing logic to add production setup
        $id = $db->query_insert(TABLE_PRODUCTION, $data);
        if($db->affected_rows > 0){
            return $id;
        }else{
            return false;
        }
    } // AddProduction

    /*************************************************
    / This function adds new Production Item
    *************************************************/
    public static function AddProductionItem($data) {
        global $db;
        $id = $db->query_insert(TABLE_PRODUCTION_ITEM, $data);
        if($db->affected_rows > 0){
            return $id;
        }else{
            return false;
        }
    } // AddProductionItem

    /*************************************************
    / This function updates Production
    *************************************************/
    public static function UpdateProductionSetup($pid, $data) {
        global $db;
        $id = $db->query_update(TABLE_PRODUCTION, $data, "id='$pid'");
        
        if($db->affected_rows > 0){
            return $id;
        }else{
            return $id;
        }
    } // UpdateProduction

    /*************************************************
    / This function gets Production Setup by ID
    *************************************************/
    public static function GetProductionSetupById($id) {
        global $db;
        $query = "SELECT * from ".TABLE_PRODUCTION." WHERE id ='$id'";
        $rows = $db->query_first($query);
    
        if($db->affected_rows > 0){
            return $rows;
        }else{
            return false;
        }
    }// GetProductionSetupById

    /*************************************************
    / This function gets Production Items
    *************************************************/
    public static function GetProductionItems($filter="", $pageno="null", $pagesize="null") {
        global $db;
        $query = "SELECT * from ".TABLE_PRODUCTION_ITEM." ". $filter;
        $rows = $db->fetch_all_array($query, $pageno, $pagesize);
        if($db->affected_rows > 0){
            return $rows;
        }else{
            return false;
        }
    }// GetProductionItems

    /*************************************************
    / This function checks Production Account
    *************************************************/
    public static function CheckProductionAccount($account_number, $id) {
        global $db;
        $query = "SELECT * from ".TABLE_PRODUCTION." WHERE bank_account_number ='$account_number' && id <> '$id'";
        $rows = $db->query_first($query);
    
        if($db->affected_rows > 0){
            return $rows;
        }else{
            return false;
        }
    }// CheckProductionAccount
    
    /*************************************************
    / This function gets Production Setup List
    *************************************************/
    public static function GetProductionSetupList($filter="", $pageno="null", $pagesize="null") {
        global $db;
        $query = "SELECT * from ".TABLE_PRODUCTION." ". $filter;
        $rows = $db->fetch_all_array($query, $pageno, $pagesize);
    
        if($db->affected_rows > 0){
            return $rows;
        }else{
            return false;
        }
    }// GetProductionSetupList

    /*************************************************
    / This function gets Production Batch Wise Total List
    *************************************************/
    public static function GetProductionBatchWiseTotalList($filter="", $pageno="null", $pagesize="null") {
        global $db;
        $query = "SELECT batch_name, SUM(batch_qty) AS batches, TRUNCATE(SUM(batch_qty*per_unit_oil), 2) AS oil, SUM(batch_qty*per_unit_mung) AS mung, SUM(batch_qty*per_unit_mash) AS mash,
SUM(batch_qty*per_unit_rice) AS rice, SUM(batch_qty*per_unit_packets) AS packets, SUM(total) AS total FROM ".TABLE_PRODUCTION." ". $filter;
        $rows = $db->fetch_all_array($query, $pageno, $pagesize);

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

    public static function GetNumRows() { global $db; return $db->GetNumRows(); }
    public static function GetNumberOfPages() { global $db; return $db->GetNumberOfPages(); 
      }
  
      public function isProducedProductExists($created_batch_id) {
        $query = "SELECT COUNT(*) FROM produced_product WHERE created_batch_id = ?";
        $stmt = $this->db->prepare($query);
        $stmt->execute([$created_batch_id]);
        $count = $stmt->fetchColumn();
        return $count > 0; // Returns true if exists, false otherwise
    }
  
  /*************************************************
/ Balance Stock Report produced products
*************************************************/
  
  public static function GetBalanceStockReportProducts($filter) {
    global $db;

    // SQL Query to fetch the necessary data based on filters
    $query = "
        SELECT 
    DATE_FORMAT(cb.created_date, '%W, %M %d, %Y') AS `Batch Date`,
    CONCAT('BCH-', LPAD(cb.id, 8, '0')) AS `Batch Number`,
    ps.name AS `Item Name`,
    pp.total_produced_quantity AS `Produced Quantity`,
    ROUND(pp.total_produced_quantity / ps.carton_pieces, 2) AS `Produced Cartons`, -- Cartons for produced quantity
    pp.remaining_quantity AS `Balance Quantity`,
    ROUND(pp.remaining_quantity / ps.carton_pieces, 2) AS `Remaining Cartons`, -- Cartons for remaining quantity
    pp.unit_cost_produced AS `Cost Per Kg`,
    ROUND(pp.remaining_quantity * pp.unit_cost_produced, 2) AS `Amount`,
    18 AS `S.Tax Rate %`, -- Assuming fixed Sales Tax Rate
    ROUND((pp.remaining_quantity * pp.unit_cost_produced) * 0.18, 2) AS `S.Tax Amount`
FROM 
    produced_products pp
INNER JOIN 
    product_setup ps ON pp.product_id = ps.id
INNER JOIN 
    created_batch cb ON pp.created_batch_id = cb.id
WHERE 
    pp.remaining_quantity > 0
ORDER BY 
    ps.name ASC;

    ";

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

    // Execute the query and fetch the results
    $rows = $db->fetch_all_array($query);
//print_r($rows);
    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 GetStockRegister($dateFrom, $dateTo, $productId = 'All') {
    global $db;

    $result = [
        'opening_balance' => [],
        'ledger' => []
    ];

    $productCondition = ($productId !== 'All') ? " AND ps.id = '" . intval($productId) . "'" : "";
    $productConditionIssue = ($productId !== 'All') ? " AND psd.product_id = '" . intval($productId) . "'" : "";
    $productConditionReceipt = ($productId !== 'All') ? " AND pp.product_id = '" . intval($productId) . "'" : "";

    /*
     * OPENING BALANCE
     * Match sales report exactly:
     * - Only deduct stock for invoices whose DATE(create_date) < $dateFrom
     * - Exclude Draft/Cancel invoices
     * - Produced quantity included only if batch created before $dateFrom
     */
    $openingBalanceQuery = "
        SELECT 
            ps.id AS `Product ID`,
            ps.name AS `Product Name`,
            ROUND(SUM(fr.remaining_quantity), 6) AS `Opening Balance Quantity`,
            ROUND(
                SUM(fr.remaining_quantity * fr.cost_per_unit) / NULLIF(SUM(fr.remaining_quantity), 0),
                2
            ) AS `Weighted Average Cost Per Unit`,
            ROUND(SUM(fr.remaining_quantity * fr.cost_per_unit), 2) AS `Opening Balance Value`
        FROM (
            SELECT 
                pp.product_id,
                pp.id AS produced_product_id,
                cb.created_date AS production_date,
                pp.total_produced_quantity AS produced_quantity,
                COALESCE(SUM(
                    CASE 
                        WHEN i.id IS NOT NULL THEN psd.deducted_quantity
                        ELSE 0
                    END
                ), 0) AS total_deducted_quantity,
                (
                    pp.total_produced_quantity - COALESCE(SUM(
                        CASE 
                            WHEN i.id IS NOT NULL THEN psd.deducted_quantity
                            ELSE 0
                        END
                    ), 0)
                ) AS remaining_quantity,
                pp.unit_cost_produced AS cost_per_unit
            FROM produced_products pp
            INNER JOIN created_batch cb 
                ON pp.created_batch_id = cb.id
            LEFT JOIN product_stock_deductions psd 
                ON pp.id = psd.produced_product_id
            LEFT JOIN invoice i 
                ON psd.invoice_id = i.id
                AND DATE(i.create_date) < '$dateFrom'
                AND i.status NOT IN ('Draft', 'Cancel')
            WHERE cb.created_date < '$dateFrom'
            " . $productConditionReceipt . "
            GROUP BY 
                pp.id, pp.product_id, cb.created_date, pp.total_produced_quantity, pp.unit_cost_produced
            HAVING remaining_quantity > 0
        ) fr
        INNER JOIN product_setup ps 
            ON fr.product_id = ps.id
        WHERE 1=1
        $productCondition
        GROUP BY ps.id, ps.name
        ORDER BY ps.name ASC
    ";

    $openingBalanceResult = $db->fetch_all_array($openingBalanceQuery);
    if ($openingBalanceResult && is_array($openingBalanceResult)) {
        $result['opening_balance'] = $openingBalanceResult;
    }

    /*
     * LEDGER
     * Receipts:
     * - finished goods produced within selected range by created_batch.created_date
     *
     * Issues:
     * - strictly by DATE(invoice.create_date) within selected range
     * - exclude Draft/Cancel
     * - this makes issue qty match sales report exactly
     */
    $ledgerQuery = "
        SELECT 
            ps.id AS `Product ID`,
            ps.name AS `Product Name`,
            t.transaction_date AS `Transaction Date`,
            t.source_reference AS `Invoice Number`,
            t.batch_number AS `Batch Number`,
            t.invoice_date AS `Invoice Date`,
            t.pseudo_batch_no AS `Produced Product Reference`,
            t.receiving_quantity AS `Receiving Quantity (Dr.)`,
            t.issue_quantity AS `Issue Quantity (Cr.)`,
            t.cost_per_unit AS `Cost Per Unit`,
            18 AS `S.Tax Rate %`
        FROM (
            SELECT 
                pp.product_id,
                cb.created_date AS transaction_date,
                NULL AS source_reference,
                CONCAT('BCH-', LPAD(pp.id, 8, '0')) AS batch_number,
                NULL AS invoice_date,
                CONCAT('PRD-', LPAD(pp.id, 8, '0')) AS pseudo_batch_no,
                pp.total_produced_quantity AS receiving_quantity,
                0.000000 AS issue_quantity,
                pp.unit_cost_produced AS cost_per_unit,
                1 AS entry_type,
                pp.id AS sort_ref_id
            FROM produced_products pp
            INNER JOIN created_batch cb 
                ON pp.created_batch_id = cb.id
            WHERE cb.created_date BETWEEN '$dateFrom' AND '$dateTo'
            $productConditionReceipt

            UNION ALL

            SELECT 
                psd.product_id,
                DATE(i.create_date) AS transaction_date,
                i.code AS source_reference,
                CONCAT('BCH-', LPAD(pp.id, 8, '0')) AS batch_number,
                DATE_FORMAT(i.create_date, '%W, %M %d, %Y') AS invoice_date,
                CONCAT('PRD-', LPAD(psd.produced_product_id, 8, '0')) AS pseudo_batch_no,
                0.000000 AS receiving_quantity,
                psd.deducted_quantity AS issue_quantity,
                psd.cost_per_unit AS cost_per_unit,
                2 AS entry_type,
                psd.id AS sort_ref_id
            FROM product_stock_deductions psd
            INNER JOIN invoice i 
                ON psd.invoice_id = i.id
            INNER JOIN produced_products pp 
                ON psd.produced_product_id = pp.id
            WHERE DATE(i.create_date) BETWEEN '$dateFrom' AND '$dateTo'
              AND i.status NOT IN ('Draft', 'Cancel')
              $productConditionIssue
        ) t
        INNER JOIN product_setup ps 
            ON t.product_id = ps.id
        WHERE 1=1
        $productCondition
        ORDER BY 
            ps.name ASC,
            t.transaction_date ASC,
            t.entry_type ASC,
            t.sort_ref_id ASC
    ";

    $ledgerResult = $db->fetch_all_array($ledgerQuery);
    if ($ledgerResult && is_array($ledgerResult)) {
        $result['ledger'] = $ledgerResult;
    }

    return $result;
}
  
   /*************************************************
    / Get Wastage Report by Ingredient and Date Range
    *************************************************/
    public static function GetWastageReport($dateFrom, $dateTo, $ingredientId = 'All') {
        global $db;

      
            // Summary Report: Total wastage by ingredient
           $query = "
                      SELECT 
                      i.name AS ingredient_name,
                      SUM(pd.reserved_qty) AS total_consumed,
                      SUM(pd.wastage_qty) AS total_wastage
                  FROM 
                      production_details pd
                  JOIN 
                      ingredients i ON pd.ingredient_id = i.id
                  JOIN 
                      produced_products pp ON pd.produced_product_id = pp.id
                  JOIN 
                      created_batch cb ON pp.created_batch_id = cb.id
                  WHERE 
                      cb.created_date BETWEEN '$dateFrom' AND '$dateTo'
                      " . ($ingredientId !== 'All' ? " AND pd.ingredient_id = '$ingredientId'" : "") . "
                  GROUP BY 
                      pd.ingredient_id
                  HAVING 
                      SUM(pd.wastage_qty) > 0
                  ORDER BY 
                      ingredient_name;
";
//echo $query;
        $rows = $db->fetch_all_array($query);
        if ($db->affected_rows > 0) {
            return $rows;
        } else {
            return false;
        }
    }


    
}

?>
