i'll share with u the main.py script, i have 3 more scripts, i want to adjust the ingredients reading and adding method to be loaded once from excel, and stored then in the database, i have 3 other scripts, database_helpers, database_setup and unit_conversion, the excel file will have 4 columns, A sap code, 2 material description, 3 balance, 4 UoM. here's the code:

# main.py

import sys
import os
import logging
import sqlite3  # Make sure to import sqlite3
from PyQt5.QtWidgets import (
    QApplication, QWidget, QTabWidget, QVBoxLayout, QPushButton,
    QLabel, QLineEdit, QMessageBox, QHBoxLayout, QListWidget, QFormLayout,
    QComboBox, QSpinBox, QFileDialog, QDateEdit, QTableWidget, QTableWidgetItem,
    QInputDialog
)
from PyQt5.QtCore import Qt, QDate

# Import your database helpers and unit conversion functions
from database_helpers import (
    add_ingredient, get_all_ingredients, get_ingredient_id, get_recipe_id,
    add_recipe, get_all_recipes, add_ingredient_to_recipe,
    get_recipe_ingredients, delete_ingredient_from_recipe,
    add_size_variation, get_size_variations, delete_size_variation,
    add_production_data, get_production_data, get_connection, initialize_database
)
from unit_conversion import convert_unit
import openpyxl
from datetime import datetime

# Configure logging
def get_base_path():
    if getattr(sys, 'frozen', False):
        # Running as a PyInstaller bundle
        return sys._MEIPASS
    else:
        # Running as a script
        return os.path.dirname(os.path.abspath(__file__))

logging.basicConfig(
    filename=os.path.join(get_base_path(), 'application.log'),
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s'
)

class IngredientsTab(QWidget):
    def __init__(self):
        super().__init__()
        self.layout = QVBoxLayout()
        self.init_ui()
        self.layout.addStretch()
        self.setLayout(self.layout)

    def init_ui(self):
        # Title
        title = QLabel("Ingredients Management")
        title.setAlignment(Qt.AlignCenter)
        title.setStyleSheet("font-size: 18px; font-weight: bold;")
        self.layout.addWidget(title)

        # Ingredients List
        self.ingredients_list = QListWidget()
        self.refresh_ingredients()
        self.layout.addWidget(self.ingredients_list)

        # Buttons
        buttons_layout = QHBoxLayout()
        add_btn = QPushButton("Add Ingredient")
        edit_btn = QPushButton("Edit Ingredient")
        delete_btn = QPushButton("Delete Ingredient")
        buttons_layout.addWidget(add_btn)
        buttons_layout.addWidget(edit_btn)
        buttons_layout.addWidget(delete_btn)
        self.layout.addLayout(buttons_layout)

        # Connect buttons
        add_btn.clicked.connect(self.add_ingredient)
        edit_btn.clicked.connect(self.edit_ingredient)
        delete_btn.clicked.connect(self.delete_ingredient)

    def refresh_ingredients(self):
        self.ingredients_list.clear()
        ingredients = get_all_ingredients()
        for ing in ingredients:
            self.ingredients_list.addItem(f"{ing[1]} ({ing[2]})")

    def add_ingredient(self):
        dialog = IngredientDialog()
        if dialog.exec_():
            name, uom = dialog.get_data()
            try:
                add_ingredient(name, uom)
                self.refresh_ingredients()
            except Exception as e:
                QMessageBox.critical(self, "Error", str(e))

    def edit_ingredient(self):
        selected_item = self.ingredients_list.currentItem()
        if not selected_item:
            QMessageBox.warning(self, "Warning", "Please select an ingredient to edit.")
            return
        text = selected_item.text()
        name = text.split(' (')[0]
        ingredient_id = get_ingredient_id(name)
        if not ingredient_id:
            QMessageBox.warning(self, "Warning", "Selected ingredient not found.")
            return
        # Fetch current UoM
        ingredients = get_all_ingredients()
        current_uom = None
        for ing in ingredients:
            if ing[0] == ingredient_id:
                current_uom = ing[2]
                break
        if not current_uom:
            QMessageBox.warning(self, "Warning", "Unit of Measurement not found.")
            return
        # Open dialog with current data
        dialog = IngredientDialog(name, current_uom)
        if dialog.exec_():
            new_name, new_uom = dialog.get_data()
            # Update the ingredient in the database
            conn = get_connection()
            c = conn.cursor()
            try:
                c.execute('UPDATE Ingredients SET name = ?, default_uom = ? WHERE ingredient_id = ?', 
                          (new_name, new_uom, ingredient_id))
                conn.commit()
                QMessageBox.information(self, "Success", "Ingredient updated successfully.")
                self.refresh_ingredients()
            except sqlite3.IntegrityError:
                QMessageBox.critical(self, "Error", "Ingredient name must be unique.")
            finally:
                conn.close()

    def delete_ingredient(self):
        selected_item = self.ingredients_list.currentItem()
        if not selected_item:
            QMessageBox.warning(self, "Warning", "Please select an ingredient to delete.")
            return
        text = selected_item.text()
        name = text.split(' (')[0]
        ingredient_id = get_ingredient_id(name)
        if not ingredient_id:
            QMessageBox.warning(self, "Warning", "Selected ingredient not found.")
            return
        confirm = QMessageBox.question(self, "Confirm Deletion", 
                                       f"Are you sure you want to delete ingredient '{name}'?",
                                       QMessageBox.Yes | QMessageBox.No)
        if confirm == QMessageBox.Yes:
            try:
                # Delete ingredient from recipes if needed
                conn = get_connection()
                c = conn.cursor()
                c.execute('DELETE FROM RecipeIngredients WHERE ingredient_id = ?', (ingredient_id,))
                # Now delete the ingredient itself
                c.execute('DELETE FROM Ingredients WHERE ingredient_id = ?', (ingredient_id,))
                conn.commit()
                conn.close()
                QMessageBox.information(self, "Success", "Ingredient deleted successfully.")
                self.refresh_ingredients()
            except Exception as e:
                QMessageBox.critical(self, "Error", str(e))

class IngredientDialog(QMessageBox):
    def __init__(self, name="", uom=""):
        super().__init__()
        self.setWindowTitle("Ingredient Details")
        self.setWindowModality(Qt.ApplicationModal)

        self.name_input = QLineEdit()
        self.name_input.setText(name)
        self.uom_input = QLineEdit()
        self.uom_input.setText(uom)

        form_layout = QFormLayout()
        form_layout.addRow("Name:", self.name_input)
        form_layout.addRow("Unit of Measurement:", self.uom_input)

        # Buttons
        self.addButton(QPushButton("OK"), QMessageBox.AcceptRole)
        self.addButton(QPushButton("Cancel"), QMessageBox.RejectRole)

        self.layout().addLayout(form_layout, 0, 0)

    def accept(self):
        name = self.name_input.text().strip()
        uom = self.uom_input.text().strip()
        if not name or not uom:
            QMessageBox.warning(self, "Input Error", "Please provide both name and unit of measurement.")
            return
        super().accept()

    def get_data(self):
        return self.name_input.text().strip(), self.uom_input.text().strip()

class RecipesTab(QWidget):
    def __init__(self):
        super().__init__()
        self.layout = QVBoxLayout()
        self.init_ui()
        self.layout.addStretch()
        self.setLayout(self.layout)

    def init_ui(self):
        # Title
        title = QLabel("Recipes Management")
        title.setAlignment(Qt.AlignCenter)
        title.setStyleSheet("font-size: 18px; font-weight: bold;")
        self.layout.addWidget(title)

        # Recipes List
        self.recipes_list = QListWidget()
        self.refresh_recipes()
        self.layout.addWidget(self.recipes_list)

        # Buttons
        buttons_layout = QHBoxLayout()
        add_btn = QPushButton("Add Recipe")
        edit_btn = QPushButton("Edit Recipe")
        delete_btn = QPushButton("Delete Recipe")
        buttons_layout.addWidget(add_btn)
        buttons_layout.addWidget(edit_btn)
        buttons_layout.addWidget(delete_btn)
        self.layout.addLayout(buttons_layout)

        # Connect buttons
        add_btn.clicked.connect(self.add_recipe)
        edit_btn.clicked.connect(self.edit_recipe)
        delete_btn.clicked.connect(self.delete_recipe)

    def refresh_recipes(self):
        self.recipes_list.clear()
        recipes = get_all_recipes()
        for rec in recipes:
            self.recipes_list.addItem(rec[1])

    def add_recipe(self):
        dialog = RecipeDialog()
        if dialog.exec_():
            name, description, ingredients, sizes = dialog.get_data()
            if not ingredients:
                QMessageBox.warning(self, "Input Error", "A recipe must have at least one ingredient.")
                return
            try:
                recipe_id = add_recipe(name, description)
                # Add ingredients to recipe
                for ing in ingredients:
                    ingredient_id, qty, uom = ing
                    add_ingredient_to_recipe(recipe_id, ingredient_id, qty, uom)
                # Add size variations
                for size in sizes:
                    add_size_variation(recipe_id, size)
                QMessageBox.information(self, "Success", "Recipe added successfully.")
                self.refresh_recipes()
            except Exception as e:
                QMessageBox.critical(self, "Error", str(e))

    def edit_recipe(self):
        selected_item = self.recipes_list.currentItem()
        if not selected_item:
            QMessageBox.warning(self, "Warning", "Please select a recipe to edit.")
            return
        name = selected_item.text()
        recipe_id = get_recipe_id(name)
        if not recipe_id:
            QMessageBox.warning(self, "Warning", "Selected recipe not found.")
            return
        # Fetch current description, ingredients, and sizes
        conn = get_connection()
        c = conn.cursor()
        c.execute('SELECT description FROM Recipes WHERE recipe_id = ?', (recipe_id,))
        description_row = c.fetchone()
        description = description_row[0] if description_row else ""
        conn.close()
        ingredients = get_recipe_ingredients(recipe_id)
        sizes = get_size_variations(recipe_id)
        # Open dialog with current data
        dialog = RecipeDialog(name, description, ingredients, sizes)
        if dialog.exec_():
            new_name, new_description, new_ingredients, new_sizes = dialog.get_data()
            if not new_ingredients:
                QMessageBox.warning(self, "Input Error", "A recipe must have at least one ingredient.")
                return
            try:
                # Update recipe name and description
                conn = get_connection()
                c = conn.cursor()
                c.execute('UPDATE Recipes SET name = ?, description = ? WHERE recipe_id = ?', 
                          (new_name, new_description, recipe_id))
                conn.commit()
                conn.close()
                # Update ingredients
                # First, delete existing ingredients
                conn = get_connection()
                c = conn.cursor()
                c.execute('DELETE FROM RecipeIngredients WHERE recipe_id = ?', (recipe_id,))
                conn.commit()
                conn.close()
                # Add new ingredients
                for ing in new_ingredients:
                    ingredient_id, qty, uom = ing
                    add_ingredient_to_recipe(recipe_id, ingredient_id, qty, uom)
                # Update size variations
                # First, delete existing sizes
                conn = get_connection()
                c = conn.cursor()
                c.execute('DELETE FROM SizeVariations WHERE recipe_id = ?', (recipe_id,))
                conn.commit()
                conn.close()
                # Add new sizes
                for size in new_sizes:
                    add_size_variation(recipe_id, size)
                QMessageBox.information(self, "Success", "Recipe updated successfully.")
                self.refresh_recipes()
            except Exception as e:
                QMessageBox.critical(self, "Error", str(e))

    def delete_recipe(self):
        selected_item = self.recipes_list.currentItem()
        if not selected_item:
            QMessageBox.warning(self, "Warning", "Please select a recipe to delete.")
            return
        name = selected_item.text()
        recipe_id = get_recipe_id(name)
        if not recipe_id:
            QMessageBox.warning(self, "Warning", "Selected recipe not found.")
            return
        confirm = QMessageBox.question(self, "Confirm Deletion", 
                                       f"Are you sure you want to delete recipe '{name}'?",
                                       QMessageBox.Yes | QMessageBox.No)
        if confirm == QMessageBox.Yes:
            try:
                # Delete associated ingredients and sizes
                conn = get_connection()
                c = conn.cursor()
                c.execute('DELETE FROM RecipeIngredients WHERE recipe_id = ?', (recipe_id,))
                c.execute('DELETE FROM SizeVariations WHERE recipe_id = ?', (recipe_id,))
                c.execute('DELETE FROM Recipes WHERE recipe_id = ?', (recipe_id,))
                conn.commit()
                conn.close()
                QMessageBox.information(self, "Success", "Recipe deleted successfully.")
                self.refresh_recipes()
            except Exception as e:
                QMessageBox.critical(self, "Error", str(e))

class RecipeDialog(QMessageBox):
    def __init__(self, name="", description="", ingredients=[], sizes=[]):
        super().__init__()
        self.setWindowTitle("Recipe Details")
        self.setWindowModality(Qt.ApplicationModal)
        self.layout = QVBoxLayout()

        form_layout = QFormLayout()
        self.name_input = QLineEdit()
        self.name_input.setText(name)
        self.description_input = QLineEdit()
        self.description_input.setText(description)
        form_layout.addRow("Name:", self.name_input)
        form_layout.addRow("Description:", self.description_input)
        self.layout.addLayout(form_layout)

        # Ingredients Section
        ingredients_label = QLabel("Ingredients:")
        self.layout.addWidget(ingredients_label)
        self.ingredients_list = QListWidget()
        for ing in ingredients:
            ingredient_name = get_ingredient_name_by_id(ing[0])
            self.ingredients_list.addItem(f"{ingredient_name}: {ing[1]} {ing[2]}")
        self.layout.addWidget(self.ingredients_list)

        # Buttons to add/remove ingredients
        ing_buttons_layout = QHBoxLayout()
        add_ing_btn = QPushButton("Add Ingredient")
        remove_ing_btn = QPushButton("Remove Ingredient")
        ing_buttons_layout.addWidget(add_ing_btn)
        ing_buttons_layout.addWidget(remove_ing_btn)
        self.layout.addLayout(ing_buttons_layout)

        add_ing_btn.clicked.connect(self.add_ingredient)
        remove_ing_btn.clicked.connect(self.remove_ingredient)

        # Size Variations Section
        sizes_label = QLabel("Size Variations:")
        self.layout.addWidget(sizes_label)
        self.sizes_list = QListWidget()
        self.sizes_list.addItems(sizes)
        self.layout.addWidget(self.sizes_list)

        # Buttons to add/remove sizes
        size_buttons_layout = QHBoxLayout()
        add_size_btn = QPushButton("Add Size")
        remove_size_btn = QPushButton("Remove Size")
        size_buttons_layout.addWidget(add_size_btn)
        size_buttons_layout.addWidget(remove_size_btn)
        self.layout.addLayout(size_buttons_layout)

        add_size_btn.clicked.connect(self.add_size)
        remove_size_btn.clicked.connect(self.remove_size)

        # Dialog Buttons
        buttons_layout = QHBoxLayout()
        ok_btn = QPushButton("OK")
        cancel_btn = QPushButton("Cancel")
        buttons_layout.addWidget(ok_btn)
        buttons_layout.addWidget(cancel_btn)
        self.layout.addLayout(buttons_layout)

        ok_btn.clicked.connect(self.accept)
        cancel_btn.clicked.connect(self.reject)

        self.setLayout(self.layout)

    def add_ingredient(self):
        dialog = SelectIngredientDialog()
        if dialog.exec_():
            ingredient_id, name, uom, quantity = dialog.get_data()
            self.ingredients_list.addItem(f"{name}: {quantity} {uom}")

    def remove_ingredient(self):
        selected_item = self.ingredients_list.currentItem()
        if selected_item:
            self.ingredients_list.takeItem(self.ingredients_list.row(selected_item))
        else:
            QMessageBox.warning(self, "Warning", "Please select an ingredient to remove.")

    def add_size(self):
        size, ok = QInputDialog.getText(self, "Add Size Variation", "Enter size (e.g., 200ml, 1L):")
        if ok and size:
            self.sizes_list.addItem(size.strip())

    def remove_size(self):
        selected_item = self.sizes_list.currentItem()
        if selected_item:
            self.sizes_list.takeItem(self.sizes_list.row(selected_item))
        else:
            QMessageBox.warning(self, "Warning", "Please select a size variation to remove.")

    def accept(self):
        name = self.name_input.text().strip()
        if not name:
            QMessageBox.warning(self, "Input Error", "Recipe name cannot be empty.")
            return
        super().accept()

    def get_data(self):
        name = self.name_input.text().strip()
        description = self.description_input.text().strip()
        ingredients = []
        for index in range(self.ingredients_list.count()):
            item_text = self.ingredients_list.item(index).text()
            name_qty, uom = item_text.split(' ')
            name, qty = name_qty.split(':')
            try:
                quantity = float(qty)
            except ValueError:
                quantity = 0
            ingredient_id = get_ingredient_id(name.strip())
            if ingredient_id:
                ingredients.append((ingredient_id, quantity, uom.strip()))
        sizes = [self.sizes_list.item(i).text() for i in range(self.sizes_list.count())]
        return name, description, ingredients, sizes

class SelectIngredientDialog(QMessageBox):
    def __init__(self):
        super().__init__()
        self.setWindowTitle("Select Ingredient")
        self.setWindowModality(Qt.ApplicationModal)
        self.layout = QVBoxLayout()

        self.ingredients = get_all_ingredients()

        self.ingredients_list = QListWidget()
        for ing in self.ingredients:
            self.ingredients_list.addItem(f"{ing[1]} ({ing[2]})")
        self.layout.addWidget(self.ingredients_list)

        form_layout = QFormLayout()
        self.quantity_input = QLineEdit()
        self.quantity_input.setPlaceholderText("Enter quantity")
        form_layout.addRow("Quantity:", self.quantity_input)
        self.layout.addLayout(form_layout)

        # Dialog Buttons
        buttons_layout = QHBoxLayout()
        ok_btn = QPushButton("OK")
        cancel_btn = QPushButton("Cancel")
        buttons_layout.addWidget(ok_btn)
        buttons_layout.addWidget(cancel_btn)
        self.layout.addLayout(buttons_layout)

        ok_btn.clicked.connect(self.accept)
        cancel_btn.clicked.connect(self.reject)

        self.setLayout(self.layout)

    def accept(self):
        if not self.ingredients_list.currentItem():
            QMessageBox.warning(self, "Input Error", "Please select an ingredient.")
            return
        qty = self.quantity_input.text().strip()
        if not qty:
            QMessageBox.warning(self, "Input Error", "Please enter a quantity.")
            return
        try:
            qty_val = float(qty)
            if qty_val <= 0:
                raise ValueError
        except ValueError:
            QMessageBox.warning(self, "Input Error", "Please enter a valid positive number for quantity.")
            return
        super().accept()

    def get_data(self):
        selected_item = self.ingredients_list.currentItem()
        if not selected_item:
            return None, None, None, None
        text = selected_item.text()
        name = text.split(' (')[0]
        uom = text.split(' (')[1][:-1]
        quantity = float(self.quantity_input.text())
        ingredient_id = get_ingredient_id(name)
        return ingredient_id, name, uom, quantity

def get_ingredient_name_by_id(ingredient_id):
    conn = get_connection()
    c = conn.cursor()
    c.execute('SELECT name FROM Ingredients WHERE ingredient_id = ?', (ingredient_id,))
    row = c.fetchone()
    conn.close()
    return row[0] if row else None

class ProductionDataTab(QWidget):
    def __init__(self):
        super().__init__()
        self.layout = QVBoxLayout()
        self.init_ui()
        self.layout.addStretch()
        self.setLayout(self.layout)

    def init_ui(self):
        # Title
        title = QLabel("Production Data Entry")
        title.setAlignment(Qt.AlignCenter)
        title.setStyleSheet("font-size: 18px; font-weight: bold;")
        self.layout.addWidget(title)

        # Form Layout
        form_layout = QFormLayout()

        # Recipe Selection
        self.recipe_combo = QComboBox()
        self.refresh_recipes()
        form_layout.addRow("Recipe:", self.recipe_combo)

        # Size Variation Selection
        self.size_combo = QComboBox()
        form_layout.addRow("Size Variation:", self.size_combo)

        # Date Selection
        self.date_edit = QDateEdit()
        self.date_edit.setCalendarPopup(True)
        self.date_edit.setDate(QDate.currentDate())
        form_layout.addRow("Date:", self.date_edit)

        # Quantity Produced
        self.quantity_input = QLineEdit()
        form_layout.addRow("Quantity Produced:", self.quantity_input)

        self.layout.addLayout(form_layout)

        # Update sizes when recipe changes
        self.recipe_combo.currentIndexChanged.connect(self.update_sizes)

        # Submit Button
        submit_btn = QPushButton("Add Production Data")
        self.layout.addWidget(submit_btn)
        submit_btn.clicked.connect(self.add_production_data)

    def refresh_recipes(self):
        self.recipe_combo.clear()
        recipes = get_all_recipes()
        for rec in recipes:
            self.recipe_combo.addItem(rec[1], rec[0])
        if recipes:
            self.update_sizes()

    def update_sizes(self):
        recipe_id = self.recipe_combo.currentData()
        self.size_combo.clear()
        sizes = get_size_variations(recipe_id)
        self.size_combo.addItems(sizes)

    def add_production_data(self):
        recipe_id = self.recipe_combo.currentData()
        size_variation = self.size_combo.currentText()
        date = self.date_edit.date().toString("yyyy-MM-dd")
        quantity = self.quantity_input.text().strip()
        try:
            quantity = float(quantity)
            if quantity <= 0:
                raise ValueError
        except ValueError:
            QMessageBox.warning(self, "Invalid Input", "Please enter a valid positive number for quantity.")
            return
        try:
            add_production_data(recipe_id, date, size_variation, quantity)
            QMessageBox.information(self, "Success", "Production data added successfully.")
            # Clear the input
            self.quantity_input.clear()
        except Exception as e:
            QMessageBox.critical(self, "Error", str(e))

class ConsumptionTab(QWidget):
    def __init__(self):
        super().__init__()
        self.layout = QVBoxLayout()
        self.init_ui()
        self.layout.addStretch()
        self.setLayout(self.layout)

    def init_ui(self):
        # Title
        title = QLabel("Consumption Calculation")
        title.setAlignment(Qt.AlignCenter)
        title.setStyleSheet("font-size: 18px; font-weight: bold;")
        self.layout.addWidget(title)

        # Ingredients Balance Import
        balance_layout = QHBoxLayout()
        self.import_balance_btn = QPushButton("Import Ingredients Balance from Excel")
        balance_layout.addWidget(self.import_balance_btn)
        self.layout.addLayout(balance_layout)

        self.import_balance_btn.clicked.connect(self.import_balance)

        # Date Range Selection
        date_layout = QFormLayout()
        self.start_date_edit = QDateEdit()
        self.start_date_edit.setCalendarPopup(True)
        self.start_date_edit.setDate(QDate.currentDate().addDays(-30))  # Default to 30 days ago
        self.end_date_edit = QDateEdit()
        self.end_date_edit.setCalendarPopup(True)
        self.end_date_edit.setDate(QDate.currentDate())
        date_layout.addRow("Start Date:", self.start_date_edit)
        date_layout.addRow("End Date:", self.end_date_edit)
        self.layout.addLayout(date_layout)

        # Calculate Button
        calculate_btn = QPushButton("Calculate Consumption")
        self.layout.addWidget(calculate_btn)
        calculate_btn.clicked.connect(self.calculate_consumption)

        # Results Display
        self.results_label = QLabel("Results will be displayed here.")
        self.results_label.setAlignment(Qt.AlignTop)
        self.results_label.setWordWrap(True)
        self.layout.addWidget(self.results_label)

        # Table for Max Products
        self.max_products_table = QTableWidget()
        self.max_products_table.setColumnCount(2)
        self.max_products_table.setHorizontalHeaderLabels(['Recipe', 'Max Products'])
        self.layout.addWidget(self.max_products_table)

    def import_balance(self):
        options = QFileDialog.Options()
        file_path, _ = QFileDialog.getOpenFileName(self, "Select Ingredients Balance Excel File", "", 
                                                   "Excel Files (*.xlsx);;All Files (*)", options=options)
        if file_path:
            try:
                ingredients_balance = self.read_ingredients_balance(file_path)
                if ingredients_balance:
                    QMessageBox.information(self, "Success", "Ingredients balance imported successfully.")
                    self.ingredients_balance = ingredients_balance
                else:
                    QMessageBox.warning(self, "Warning", "No data found in the selected file.")
            except Exception as e:
                QMessageBox.critical(self, "Error", str(e))

    def read_ingredients_balance(self, file_path):
        """Read the ingredients balance from the specified Excel file."""
        try:
            wb = openpyxl.load_workbook(file_path, data_only=True)
            sheet = wb.active  # Assuming the first sheet contains the data

            ingredients_balance = {}

            # Find headers
            headers = {}
            for col in range(1, sheet.max_column + 1):
                header = sheet.cell(row=1, column=col).value
                if header:
                    headers[header.strip().lower()] = col

            required_headers = ['ingredient name', 'quantity', 'unit of measurement']
            for header in required_headers:
                if header not in headers:
                    QMessageBox.critical(self, "Error", f"Missing header '{header}' in the Excel sheet.")
                    return None

            # Read data rows
            for row in range(2, sheet.max_row + 1):
                ingredient_name = sheet.cell(row=row, column=headers['ingredient name']).value
                quantity = sheet.cell(row=row, column=headers['quantity']).value
                uom = sheet.cell(row=row, column=headers['unit of measurement']).value

                if ingredient_name and quantity is not None and uom:
                    ingredients_balance[str(ingredient_name).strip()] = {'quantity': float(quantity), 'uom': str(uom).strip()}
                    logging.info(f"Imported '{ingredient_name}': {quantity} {uom}")

            return ingredients_balance

        except Exception as e:
            logging.error(f"Error importing ingredients balance: {str(e)}")
            QMessageBox.critical(self, "Error", f"Failed to import ingredients balance: {str(e)}")
            return None

    def calculate_consumption(self):
        # Check if ingredients balance is loaded
        if not hasattr(self, 'ingredients_balance'):
            QMessageBox.warning(self, "Warning", "Please import ingredients balance before calculating consumption.")
            return

        start_date = self.start_date_edit.date().toString("yyyy-MM-dd")
        end_date = self.end_date_edit.date().toString("yyyy-MM-dd")

        # Fetch production data
        production_records = get_production_data(start_date, end_date)
        if not production_records:
            QMessageBox.information(self, "No Data", "No production records found in the selected date range.")
            return

        total_consumption = {}

        for record in production_records:
            recipe_id, date, size_variation, quantity_produced = record
            ingredients = get_recipe_ingredients(recipe_id)
            for ing in ingredients:
                ingredient_id, qty_per_100, uom = ing
                ing_name = get_ingredient_name_by_id(ingredient_id)
                qty_needed = qty_per_100 * (quantity_produced / 100)
                base_uom = 'kg' if uom.lower() in ['mg', 'g', 'kg'] else 'L' if uom.lower() in ['ml', 'l'] else uom
                try:
                    qty_in_base = convert_unit(qty_needed, uom, base_uom)
                except ValueError:
                    qty_in_base = qty_needed  # Use as is if conversion not possible
                if ing_name in total_consumption:
                    total_consumption[ing_name]['quantity'] += qty_in_base
                else:
                    total_consumption[ing_name] = {'quantity': qty_in_base, 'uom': base_uom}

        # Calculate average consumption per day
        start = datetime.strptime(start_date, '%Y-%m-%d')
        end = datetime.strptime(end_date, '%Y-%m-%d')
        days = (end - start).days + 1
        average_consumption = {ing: data['quantity'] / days for ing, data in total_consumption.items()}

        # Display results
        result_text = "<h3>Total Consumption:</h3><ul>"
        for ing, data in total_consumption.items():
            qty = data['quantity']
            uom = data['uom']
            display_qty, display_uom = self.format_quantity(qty, uom)
            result_text += f"<li>{ing}: {display_qty} {display_uom}</li>"
        result_text += "</ul>"

        result_text += f"<h3>Average Daily Consumption over {days} days:</h3><ul>"
        for ing, avg_qty in average_consumption.items():
            uom = total_consumption[ing]['uom']
            display_qty, display_uom = self.format_quantity(avg_qty, uom)
            result_text += f"<li>{ing}: {display_qty} {display_uom}/day</li>"
        result_text += "</ul>"

        self.results_label.setText(result_text)

        # Calculate maximum products
        recipes = self.load_recipes_from_db()
        max_products = self.calculate_max_products(recipes, self.ingredients_balance)

        # Display maximum products in table
        self.display_max_products(max_products)

    def format_quantity(self, quantity, uom):
        """Convert quantity to appropriate unit for display."""
        if uom in ['kg', 'g', 'mg']:
            if quantity >= 1:
                return f"{quantity:.2f}", uom
            else:
                # Convert to smaller unit
                try:
                    converted = convert_unit(quantity, uom, 'g')
                    return f"{converted:.2f}", 'g'
                except:
                    return f"{quantity:.2f}", uom
        elif uom in ['L', 'ml']:
            if quantity >= 1:
                return f"{quantity:.2f}", uom
            else:
                # Convert to smaller unit
                try:
                    converted = convert_unit(quantity, uom, 'ml')
                    return f"{converted:.2f}", 'ml'
                except:
                    return f"{quantity:.2f}", uom
        else:
            return f"{quantity:.2f}", uom

    def load_recipes_from_db(self):
        """Load recipes and their ingredients from the database."""
        recipes = {}
        all_recipes = get_all_recipes()
        for rec in all_recipes:
            recipe_id, name = rec
            ingredients = get_recipe_ingredients(recipe_id)
            ingredient_dict = {}
            for ing in ingredients:
                ingredient_id, qty, uom = ing
                ing_name = get_ingredient_name_by_id(ingredient_id)
                ingredient_dict[ing_name] = (qty, uom)
            recipes[name] = {
                'ingredients': ingredient_dict
            }
        return recipes

    def calculate_max_products(self, recipes, ingredients_balance):
        """Determine maximum products based on available ingredients."""
        max_products = {}
        for recipe_name, recipe in recipes.items():
            max_batches_per_ingredient = []
            can_make = True
            for ing, (req_qty, req_uom) in recipe['ingredients'].items():
                if ing in ingredients_balance:
                    available_qty = ingredients_balance[ing]['quantity']
                    available_uom = ingredients_balance[ing]['uom']
                    try:
                        available_converted = convert_unit(available_qty, available_uom, req_uom)
                    except ValueError:
                        can_make = False
                        break
                    if req_qty == 0:
                        can_make = False
                        break
                    possible_batches = available_converted / req_qty
                    max_batches_per_ingredient.append(possible_batches)
                else:
                    can_make = False
                    break
            if can_make and max_batches_per_ingredient:
                max_batches = min(max_batches_per_ingredient)
                max_products[recipe_name] = int(max_batches)
            else:
                max_products[recipe_name] = 0
        return max_products

    def display_max_products(self, max_products):
        """Display the maximum number of products in a table."""
        self.max_products_table.clear()
        self.max_products_table.setRowCount(len(max_products))
        self.max_products_table.setColumnCount(2)
        self.max_products_table.setHorizontalHeaderLabels(['Recipe', 'Max Products'])

        for row, (recipe, max_prod) in enumerate(max_products.items()):
            self.max_products_table.setItem(row, 0, QTableWidgetItem(recipe))
            self.max_products_table.setItem(row, 1, QTableWidgetItem(str(max_prod)))

        self.max_products_table.resizeColumnsToContents()

class GUIApp(QTabWidget):
    def __init__(self):
        super().__init__()
        self.setWindowTitle("Recipe and Consumption Manager")
        self.setGeometry(100, 100, 800, 600)

        # Tabs
        self.ingredients_tab = IngredientsTab()
        self.recipes_tab = RecipesTab()
        self.production_tab = ProductionDataTab()
        self.consumption_tab = ConsumptionTab()

        self.addTab(self.ingredients_tab, "Ingredients")
        self.addTab(self.recipes_tab, "Recipes")
        self.addTab(self.production_tab, "Production Data")
        self.addTab(self.consumption_tab, "Consumption")

def main():
    app = QApplication(sys.argv)
    app.setStyle("Fusion")  # Optional: Set a style

    # Initialize the database
    initialize_database()

    window = GUIApp()
    window.show()
    sys.exit(app.exec_())

if __name__ == "__main__":
    main()