i get this error 

Traceback (most recent call last):
File "C:\Users\UND\Desktop\Py\All in One\Scripts\main.py", line 621, in add_ingredient
ingredient_id, name, uom, quantity = dialog.get_data()
^^^^^^^^^^^^^^^^^
File "C:\Users\UND\Desktop\Py\All in One\Scripts\main.py", line 778, in get_data
uom = name_uom.split('(')[1][:-1]
~~~~~~~~~~~~~~~~~~~^^^
IndexError: list index out of range
Press any key to continue . . .

when i run this code

# main.py

import sys
import os
import logging
import sqlite3
from PyQt5.QtWidgets import (
    QApplication, QWidget, QTabWidget, QVBoxLayout, QPushButton,
    QLabel, QLineEdit, QMessageBox, QHBoxLayout, QListWidget, QFormLayout,
    QComboBox, QFileDialog, QDateEdit, QTableWidget, QTableWidgetItem,
    QInputDialog, QDialog
)
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,
    add_size_variation, get_size_variations, get_size_variations_details,
    add_production_data, get_production_data, get_connection, initialize_database,
    get_ingredient_name_by_id, update_ingredient,
    get_ingredient_id_by_sap_code, get_ingredient_details_by_id
)
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__))

# Set up logging to file and console
logging.basicConfig(
    filename=os.path.join(get_base_path(), 'application.log'),
    level=logging.DEBUG,  # Set to DEBUG to capture all levels of logs
    format='%(asctime)s - %(levelname)s - %(message)s',
    filemode='a'
)
console_handler = logging.StreamHandler()
console_handler.setLevel(logging.DEBUG)
console_formatter = logging.Formatter('%(levelname)s - %(message)s')
console_handler.setFormatter(console_formatter)
logging.getLogger().addHandler(console_handler)

class IngredientsTab(QWidget):
    def __init__(self):
        super().__init__()
        self.layout = QVBoxLayout()
        try:
            self.init_ui()
            self.layout.addStretch()
            self.setLayout(self.layout)
            logging.info("Initialized IngredientsTab UI successfully.")
        except Exception as e:
            logging.error(f"Error initializing IngredientsTab: {e}")
            QMessageBox.critical(self, "Error", f"Failed to initialize IngredientsTab: {e}")

    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)

        # Import Button
        import_btn = QPushButton("Import Ingredients from Excel")
        self.layout.addWidget(import_btn)
        import_btn.clicked.connect(self.import_ingredients_from_excel)

        # 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):
        try:
            self.ingredients_list.clear()
            ingredients = get_all_ingredients()
            for ing in ingredients:
                sap_code = ing[1]
                name = ing[2]
                uom = ing[3]
                balance = ing[4]
                self.ingredients_list.addItem(f"{sap_code} - {name} ({uom}), Balance: {balance}")
            logging.debug("Ingredients list refreshed.")
        except Exception as e:
            logging.error(f"Error refreshing ingredients list: {e}")
            QMessageBox.critical(self, "Error", f"Failed to refresh ingredients list: {e}")

    def import_ingredients_from_excel(self):
        options = QFileDialog.Options()
        file_path, _ = QFileDialog.getOpenFileName(self, "Select Ingredients Excel File", "", "Excel Files (*.xlsx);;All Files (*)", options=options)
        if file_path:
            try:
                ingredients_data = self.read_ingredients_from_excel(file_path)
                if ingredients_data:
                    conn = get_connection()
                    c = conn.cursor()
                    try:
                        for ing in ingredients_data:
                            sap_code = ing['sap_code']
                            name = ing['name']
                            balance = ing['balance']
                            uom = ing['uom']
                            try:
                                # Try to add new ingredient
                                c.execute('INSERT INTO Ingredients (sap_code, name, default_uom, balance) VALUES (?, ?, ?, ?)',
                                          (sap_code, name, uom, balance))
                                logging.info(f"Added new ingredient: {name} (SAP Code: {sap_code})")
                            except sqlite3.IntegrityError:
                                # Update existing ingredient if sap_code or name already exists
                                c.execute('UPDATE Ingredients SET default_uom = ?, balance = ? WHERE sap_code = ? OR name = ?',
                                          (uom, balance, sap_code, name))
                                logging.info(f"Updated existing ingredient: {name} (SAP Code: {sap_code})")
                        conn.commit()
                    except Exception as e_inner:
                        conn.rollback()
                        logging.error(f"Error during import transaction: {e_inner}")
                        QMessageBox.critical(self, "Error", f"Failed to import ingredients: {e_inner}")
                    finally:
                        conn.close()
                    QMessageBox.information(self, "Success", "Ingredients imported successfully.")
                    self.refresh_ingredients()
                else:
                    QMessageBox.warning(self, "Warning", "No data found in the selected file.")
                    logging.warning("No data found in the selected Excel file.")
            except Exception as e:
                logging.error(f"Error importing ingredients from Excel: {e}")
                QMessageBox.critical(self, "Error", f"Failed to import ingredients: {e}")

    def read_ingredients_from_excel(self, file_path):
        """Read the ingredients 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_data = []

            # Map headers to columns
            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 = ['sap code', 'material description', 'balance', 'uom']
            for header in required_headers:
                if header not in headers:
                    logging.error(f"Missing header '{header}' in the Excel sheet.")
                    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):
                sap_code = sheet.cell(row=row, column=headers['sap code']).value
                name = sheet.cell(row=row, column=headers['material description']).value
                balance = sheet.cell(row=row, column=headers['balance']).value
                uom = sheet.cell(row=row, column=headers['uom']).value
                if sap_code and name and balance is not None and uom:
                    try:
                        balance_float = float(balance)
                        ingredients_data.append({
                            'sap_code': str(sap_code).strip(),
                            'name': str(name).strip(),
                            'balance': balance_float,
                            'uom': str(uom).strip()
                        })
                        logging.debug(f"Read ingredient from Excel: {name} (SAP Code: {sap_code})")
                    except ValueError:
                        logging.warning(f"Invalid balance value at row {row}. Skipping this row.")
                        continue
            return ingredients_data
        except Exception as e:
            logging.error(f"Error reading ingredients from Excel: {e}")
            QMessageBox.critical(self, "Error", f"Failed to read ingredients from Excel: {e}")
            return None

    def add_ingredient(self):
        dialog = IngredientDialog()
        if dialog.exec_():
            sap_code, name, uom, balance = dialog.get_data()
            try:
                add_ingredient(sap_code, name, uom, balance)
                self.refresh_ingredients()
                logging.info(f"Added ingredient: {name} (SAP Code: {sap_code})")
            except sqlite3.IntegrityError as e:
                logging.error(f"IntegrityError when adding ingredient: {e}")
                QMessageBox.critical(self, "Error", "Ingredient SAP code and name must be unique.")
            except Exception as e:
                logging.error(f"Error adding ingredient: {e}")
                QMessageBox.critical(self, "Error", f"Failed to add ingredient: {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.")
            logging.warning("No ingredient selected for editing.")
            return
        text = selected_item.text()
        parts = text.split(' - ')
        if len(parts) < 2:
            QMessageBox.warning(self, "Warning", "Invalid item format.")
            logging.warning("Invalid ingredient item format when editing.")
            return
        sap_code = parts[0].strip()
        name_part = parts[1]
        name = name_part.split(' (')[0].strip()
        try:
            conn = get_connection()
            c = conn.cursor()
            c.execute('SELECT ingredient_id, default_uom, balance FROM Ingredients WHERE sap_code = ? OR name = ?', (sap_code, name))
            row = c.fetchone()
            conn.close()
            if not row:
                QMessageBox.warning(self, "Warning", "Selected ingredient not found.")
                logging.warning(f"Ingredient not found in database: {name} (SAP Code: {sap_code})")
                return
            ingredient_id = row[0]
            uom = row[1]
            balance = row[2]
            # Open dialog with current data
            dialog = IngredientDialog(sap_code, name, uom, balance)
            if dialog.exec_():
                new_sap_code, new_name, new_uom, new_balance = dialog.get_data()
                # Update the ingredient in the database
                try:
                    update_ingredient(ingredient_id, new_sap_code, new_name, new_uom, new_balance)
                    QMessageBox.information(self, "Success", "Ingredient updated successfully.")
                    self.refresh_ingredients()
                    logging.info(f"Updated ingredient: {new_name} (SAP Code: {new_sap_code})")
                except sqlite3.IntegrityError as e:
                    logging.error(f"IntegrityError when updating ingredient: {e}")
                    QMessageBox.critical(self, "Error", "Ingredient SAP code and name must be unique.")
                except Exception as e:
                    logging.error(f"Error updating ingredient: {e}")
                    QMessageBox.critical(self, "Error", f"Failed to update ingredient: {e}")
        except Exception as e:
            logging.error(f"Error editing ingredient: {e}")
            QMessageBox.critical(self, "Error", f"Failed to edit ingredient: {e}")

    def delete_ingredient(self):
        selected_item = self.ingredients_list.currentItem()
        if not selected_item:
            QMessageBox.warning(self, "Warning", "Please select an ingredient to delete.")
            logging.warning("No ingredient selected for deletion.")
            return
        text = selected_item.text()
        parts = text.split(' - ')
        if len(parts) < 2:
            QMessageBox.warning(self, "Warning", "Invalid item format.")
            logging.warning("Invalid ingredient item format when deleting.")
            return
        sap_code = parts[0].strip()
        name_part = parts[1]
        name = name_part.split(' (')[0].strip()
        ingredient_id = get_ingredient_id(name)
        if not ingredient_id:
            QMessageBox.warning(self, "Warning", "Selected ingredient not found.")
            logging.warning(f"Ingredient not found in database: {name} (SAP Code: {sap_code})")
            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()
                logging.info(f"Deleted ingredient: {name} (SAP Code: {sap_code})")
            except Exception as e:
                logging.error(f"Error deleting ingredient: {e}")
                QMessageBox.critical(self, "Error", f"Failed to delete ingredient: {e}")

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

        self.sap_code_input = QLineEdit()
        self.sap_code_input.setText(sap_code)
        self.name_input = QLineEdit()
        self.name_input.setText(name)
        self.uom_input = QLineEdit()
        self.uom_input.setText(uom)
        self.balance_input = QLineEdit()
        self.balance_input.setText(str(balance))

        form_layout = QFormLayout()
        form_layout.addRow("SAP Code:", self.sap_code_input)
        form_layout.addRow("Name:", self.name_input)
        form_layout.addRow("Unit of Measurement:", self.uom_input)
        form_layout.addRow("Balance:", self.balance_input)

        # Buttons
        buttons_layout = QHBoxLayout()
        ok_btn = QPushButton("OK")
        cancel_btn = QPushButton("Cancel")
        buttons_layout.addWidget(ok_btn)
        buttons_layout.addWidget(cancel_btn)

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

        # Main Layout
        main_layout = QVBoxLayout()
        main_layout.addLayout(form_layout)
        main_layout.addLayout(buttons_layout)
        self.setLayout(main_layout)

    def accept(self):
        sap_code = self.sap_code_input.text().strip()
        name = self.name_input.text().strip()
        uom = self.uom_input.text().strip()
        balance_text = self.balance_input.text().strip()
        if not sap_code or not name or not uom or not balance_text:
            QMessageBox.warning(self, "Input Error", "Please provide all fields.")
            return
        try:
            balance = float(balance_text)
        except ValueError:
            QMessageBox.warning(self, "Input Error", "Balance must be a number.")
            return
        super().accept()

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

class RecipesTab(QWidget):
    def __init__(self):
        super().__init__()
        self.layout = QVBoxLayout()
        try:
            self.init_ui()
            self.layout.addStretch()
            self.setLayout(self.layout)
            logging.info("Initialized RecipesTab UI successfully.")
        except Exception as e:
            logging.error(f"Error initializing RecipesTab: {e}")
            QMessageBox.critical(self, "Error", f"Failed to initialize RecipesTab: {e}")

    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("Create 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):
        try:
            self.recipes_list.clear()
            recipes = get_all_recipes()
            for rec in recipes:
                self.recipes_list.addItem(rec[1])
            logging.debug("Recipes list refreshed.")
        except Exception as e:
            logging.error(f"Error refreshing recipes list: {e}")
            QMessageBox.critical(self, "Error", f"Failed to refresh recipes list: {e}")

    def add_recipe(self):
        dialog = RecipeDialog()
        if dialog.exec_():
            name, final_product_type, ingredients, size_variations = dialog.get_data()
            if not ingredients:
                QMessageBox.warning(self, "Input Error", "A recipe must have at least one ingredient.")
                logging.warning("Attempted to add recipe without ingredients.")
                return
            try:
                recipe_id = add_recipe(name, "", final_product_type)
                # 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_ingredient_id in size_variations:
                    add_size_variation(recipe_id, size_ingredient_id)
                QMessageBox.information(self, "Success", "Recipe added successfully.")
                self.refresh_recipes()
                logging.info(f"Added recipe: {name}")
            except sqlite3.IntegrityError as e:
                logging.error(f"IntegrityError when adding recipe: {e}")
                QMessageBox.critical(self, "Error", f"Recipe name must be unique.")
            except Exception as e:
                logging.error(f"Error adding recipe: {e}")
                QMessageBox.critical(self, "Error", f"Failed to add recipe: {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.")
            logging.warning("No recipe selected for editing.")
            return
        name = selected_item.text()
        recipe_id = get_recipe_id(name)
        if not recipe_id:
            QMessageBox.warning(self, "Warning", "Selected recipe not found.")
            logging.warning(f"Recipe not found in database: {name}")
            return
        try:
            # Fetch current description, ingredients, and sizes
            conn = get_connection()
            c = conn.cursor()
            c.execute('SELECT description, final_product_type FROM Recipes WHERE recipe_id = ?', (recipe_id,))
            description_row = c.fetchone()
            description = description_row[0] if description_row else ""
            final_product_type = description_row[1] if description_row else "Liquid"
            conn.close()
            ingredients = get_recipe_ingredients(recipe_id)
            sizes = get_size_variations_details(recipe_id)
            # Open dialog with current data
            dialog = RecipeDialog(name, description, ingredients, sizes, final_product_type)
            if dialog.exec_():
                new_name, new_final_product_type, new_ingredients, new_sizes = dialog.get_data()
                if not new_ingredients:
                    QMessageBox.warning(self, "Input Error", "A recipe must have at least one ingredient.")
                    logging.warning("Attempted to update recipe without ingredients.")
                    return
                try:
                    # Update recipe name and final product type
                    conn = get_connection()
                    c = conn.cursor()
                    c.execute('UPDATE Recipes SET name = ?, final_product_type = ? WHERE recipe_id = ?',
                              (new_name, new_final_product_type, 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_ingredient_id in new_sizes:
                        add_size_variation(recipe_id, size_ingredient_id)
                    QMessageBox.information(self, "Success", "Recipe updated successfully.")
                    self.refresh_recipes()
                    logging.info(f"Updated recipe: {new_name}")
                except sqlite3.IntegrityError as e:
                    logging.error(f"IntegrityError when updating recipe: {e}")
                    QMessageBox.critical(self, "Error", f"Recipe name must be unique.")
                except Exception as e:
                    logging.error(f"Error updating recipe: {e}")
                    QMessageBox.critical(self, "Error", f"Failed to update recipe: {e}")
        except Exception as e:
            logging.error(f"Error editing recipe: {e}")
            QMessageBox.critical(self, "Error", f"Failed to edit recipe: {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.")
            logging.warning("No recipe selected for deletion.")
            return
        name = selected_item.text()
        recipe_id = get_recipe_id(name)
        if not recipe_id:
            QMessageBox.warning(self, "Warning", "Selected recipe not found.")
            logging.warning(f"Recipe not found in database: {name}")
            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()
                logging.info(f"Deleted recipe: {name}")
            except Exception as e:
                logging.error(f"Error deleting recipe: {e}")
                QMessageBox.critical(self, "Error", f"Failed to delete recipe: {e}")

class RecipeDialog(QDialog):
    def __init__(self, name="", description="", ingredients=[], sizes=[], final_product_type="Liquid"):
        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)
        # Add final product type selection
        self.product_type_combo = QComboBox()
        self.product_type_combo.addItems(['Liquid', 'Solid'])
        self.product_type_combo.setCurrentText(final_product_type)
        form_layout.addRow("Name:", self.name_input)
        form_layout.addRow("Final Product Type:", self.product_type_combo)
        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])
            uom = ing[2]
            self.ingredients_list.addItem(f"{ingredient_name}: {ing[1]:.2f} {uom}")
        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()
        for size in sizes:
            size_display = size[1]  # size is a tuple (ingredient_id, size_display)
            self.sizes_list.addItem(size_display)
        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:.2f} {uom}")
            logging.debug(f"Added ingredient to recipe: {name} ({quantity:.2f} {uom})")

    def remove_ingredient(self):
        selected_item = self.ingredients_list.currentItem()
        if selected_item:
            self.ingredients_list.takeItem(self.ingredients_list.row(selected_item))
            logging.debug(f"Removed ingredient from recipe: {selected_item.text()}")
        else:
            QMessageBox.warning(self, "Warning", "Please select an ingredient to remove.")
            logging.warning("Attempted to remove ingredient without selection.")

    def add_size(self):
        sap_code, ok = QInputDialog.getText(self, "Add Size Variation", "Enter SAP Code for size variation:")
        if ok and sap_code:
            sap_code = sap_code.strip()
            # Check if SAP code exists in ingredients database
            ingredient_id = get_ingredient_id_by_sap_code(sap_code)
            if ingredient_id:
                # Get ingredient details
                name, uom = get_ingredient_details_by_id(ingredient_id)
                size_display = f"{sap_code} - {name} ({uom})"
                self.sizes_list.addItem(size_display)
                logging.debug(f"Added size variation: {size_display}")
            else:
                QMessageBox.warning(self, "Invalid SAP Code", f"SAP Code '{sap_code}' not found in ingredients database.")
                logging.warning(f"Attempted to add size variation with invalid SAP Code: {sap_code}")

    def remove_size(self):
        selected_item = self.sizes_list.currentItem()
        if selected_item:
            self.sizes_list.takeItem(self.sizes_list.row(selected_item))
            logging.debug(f"Removed size variation: {selected_item.text()}")
        else:
            QMessageBox.warning(self, "Warning", "Please select a size variation to remove.")
            logging.warning("Attempted to remove size variation without selection.")

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

    def get_data(self):
        name = self.name_input.text().strip()
        final_product_type = self.product_type_combo.currentText()
        ingredients = []
        for index in range(self.ingredients_list.count()):
            item_text = self.ingredients_list.item(index).text()
            try:
                # Example item_text: "Ingredient Name: 2.00 kg"
                name_qty, uom = item_text.rsplit(' ', 1)
                ingredient_name, qty_str = name_qty.split(': ')
                ingredient_name = ingredient_name.strip()
                qty = float(qty_str.strip())
                uom = uom.strip()
                ingredient_id = get_ingredient_id(ingredient_name)
                if ingredient_id:
                    ingredients.append((ingredient_id, qty, uom))
            except Exception as e:
                logging.error(f"Error parsing ingredient '{item_text}': {e}")
                continue
        size_variations = []
        for index in range(self.sizes_list.count()):
            item_text = self.sizes_list.item(index).text()
            # item_text is like "SAP_CODE - Name (UoM)"
            sap_code = item_text.split(' - ')[0].strip()
            ingredient_id = get_ingredient_id_by_sap_code(sap_code)
            if ingredient_id:
                size_variations.append(ingredient_id)
        return name, final_product_type, ingredients, size_variations

class SelectIngredientDialog(QDialog):
    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:
            sap_code = ing[1]
            name = ing[2]
            uom = ing[3]
            self.ingredients_list.addItem(f"{sap_code} - {name} ({uom})")
        self.layout.addWidget(self.ingredients_list)

        form_layout = QFormLayout()
        # UoM label
        self.uom_label = QLabel("Unit of Measure: ")
        form_layout.addRow("UoM:", self.uom_label)
        # Quantity input
        self.quantity_input = QLineEdit()
        self.quantity_input.setPlaceholderText("Enter required quantity")
        form_layout.addRow("Quantity:", self.quantity_input)
        self.layout.addLayout(form_layout)

        # Update UoM when ingredient selection changes
        self.ingredients_list.currentItemChanged.connect(self.update_uom)

        # 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 update_uom(self):
        selected_item = self.ingredients_list.currentItem()
        if not selected_item:
            self.uom_label.setText("Unit of Measure: ")
            return
        text = selected_item.text()
        # Extract UoM from the item text
        uom = text.split('(')[1][:-1]
        self.uom_label.setText(f"{uom}")

    def accept(self):
        if not self.ingredients_list.currentItem():
            QMessageBox.warning(self, "Input Error", "Please select an ingredient.")
            logging.warning("No ingredient selected in SelectIngredientDialog.")
            return
        quantity_text = self.quantity_input.text().strip()
        if not quantity_text:
            QMessageBox.warning(self, "Input Error", "Please enter the required quantity.")
            logging.warning("No quantity entered in SelectIngredientDialog.")
            return
        try:
            quantity = float(quantity_text)
            if quantity <= 0:
                raise ValueError
        except ValueError:
            QMessageBox.warning(self, "Input Error", "Please enter a valid positive number for quantity.")
            logging.warning("Invalid quantity entered in SelectIngredientDialog.")
            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()
        parts = text.split(' - ')
        sap_code = parts[0]
        name_uom = parts[1]
        name = name_uom.split(' (')[0].strip()
        uom = name_uom.split('(')[1][:-1]
        quantity = float(self.quantity_input.text().strip())
        ingredient_id = get_ingredient_id(name)
        return ingredient_id, name, uom, quantity

class ProductionDataTab(QWidget):
    def __init__(self):
        super().__init__()
        self.layout = QVBoxLayout()
        try:
            self.init_ui()
            self.layout.addStretch()
            self.setLayout(self.layout)
            logging.info("Initialized ProductionDataTab UI successfully.")
        except Exception as e:
            logging.error(f"Error initializing ProductionDataTab: {e}")
            QMessageBox.critical(self, "Error", f"Failed to initialize ProductionDataTab: {e}")

    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):
        try:
            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()
            logging.debug("ProductionDataTab recipes refreshed.")
        except Exception as e:
            logging.error(f"Error refreshing recipes in ProductionDataTab: {e}")
            QMessageBox.critical(self, "Error", f"Failed to refresh recipes: {e}")

    def update_sizes(self):
        try:
            recipe_id = self.recipe_combo.currentData()
            self.size_combo.clear()
            sizes = get_size_variations_details(recipe_id)
            for size in sizes:
                size_display = size[1]
                self.size_combo.addItem(size_display, size[0])  # size[0] is ingredient_id
            logging.debug(f"Sizes updated for recipe ID {recipe_id}.")
        except Exception as e:
            logging.error(f"Error updating sizes in ProductionDataTab: {e}")
            QMessageBox.critical(self, "Error", f"Failed to update sizes: {e}")

    def add_production_data(self):
        try:
            recipe_id = self.recipe_combo.currentData()
            size_variation_display = self.size_combo.currentText()
            size_variation_ingredient_id = self.size_combo.currentData()
            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.")
                logging.warning("Invalid quantity entered in ProductionDataTab.")
                return
            try:
                add_production_data(recipe_id, date, size_variation_display, quantity)
                QMessageBox.information(self, "Success", "Production data added successfully.")
                # Clear the input
                self.quantity_input.clear()
                logging.info(f"Added production data for recipe ID {recipe_id} on {date}.")
            except Exception as e:
                logging.error(f"Error adding production data: {e}")
                QMessageBox.critical(self, "Error", f"Failed to add production data: {e}")
        except Exception as e:
            logging.error(f"Error in add_production_data method: {e}")
            QMessageBox.critical(self, "Error", f"Failed to add production data: {e}")

class ConsumptionTab(QWidget):
    def __init__(self):
        super().__init__()
        self.layout = QVBoxLayout()
        try:
            self.init_ui()
            self.layout.addStretch()
            self.setLayout(self.layout)
            logging.info("Initialized ConsumptionTab UI successfully.")
        except Exception as e:
            logging.error(f"Error initializing ConsumptionTab: {e}")
            QMessageBox.critical(self, "Error", f"Failed to initialize ConsumptionTab: {e}")

    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)

        # 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 calculate_consumption(self):
        try:
            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.")
                logging.info("No production data 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)
            logging.info("Consumption calculation completed and results displayed.")

            # Fetch ingredients balance from database
            ingredients_balance = {}
            ingredients = get_all_ingredients()
            for ing in ingredients:
                name = ing[2]
                balance = ing[4]
                uom = ing[3]
                ingredients_balance[name] = {'quantity': balance, 'uom': uom}

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

            # Display maximum products in table
            self.display_max_products(max_products)
            logging.info("Maximum products calculated and displayed.")
        except Exception as e:
            logging.error(f"Error in calculate_consumption: {e}")
            QMessageBox.critical(self, "Error", f"Failed to calculate consumption: {e}")

    def format_quantity(self, quantity, uom):
        """Convert quantity to appropriate unit for display."""
        try:
            if uom.lower() in ['kg', 'g', 'mg']:
                if quantity >= 1:
                    return f"{quantity:.2f}", 'kg'
                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.lower() in ['l', 'ml']:
                if quantity >= 1:
                    return f"{quantity:.2f}", 'L'
                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
        except Exception as e:
            logging.error(f"Error formatting quantity: {e}")
            return f"{quantity:.2f}", uom

    def load_recipes_from_db(self):
        """Load recipes and their ingredients from the database."""
        try:
            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
                }
            logging.debug("Recipes loaded from database for consumption calculation.")
            return recipes
        except Exception as e:
            logging.error(f"Error loading recipes from database: {e}")
            return {}

    def calculate_max_products(self, recipes, ingredients_balance):
        """Determine maximum products based on available ingredients."""
        try:
            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
            logging.debug("Maximum products calculated based on ingredient balances.")
            return max_products
        except Exception as e:
            logging.error(f"Error calculating maximum products: {e}")
            return {}

    def display_max_products(self, max_products):
        """Display the maximum number of products in a table."""
        try:
            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()
            logging.debug("Max products table displayed.")
        except Exception as e:
            logging.error(f"Error displaying max products: {e}")
            QMessageBox.critical(self, "Error", f"Failed to display max products: {e}")

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

        try:
            # 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")
            logging.info("GUI tabs initialized successfully.")
        except Exception as e:
            logging.error(f"Error initializing GUI tabs: {e}")
            QMessageBox.critical(self, "Error", f"Failed to initialize GUI: {e}")

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

        # Initialize the database
        initialize_database()
        logging.info("Database initialized.")

        window = GUIApp()
        window.show()
        logging.info("Application started.")
        sys.exit(app.exec_())
    except Exception as e:
        logging.critical(f"Unhandled exception: {e}", exc_info=True)
        QMessageBox.critical(None, "Fatal Error", f"An unexpected error occurred: {e}")
        sys.exit(1)

if __name__ == "__main__":
    main()