rework the recipe management, i want to change add recipe to create recipe, choosing create i will have to inter the name then choose the ingredients of the recipe form the ingredients in the database then add size variations, in the ingredients  there will be all of the size variations, 1kg 2kg 2ltr 1ltr 200ml etc, when choosing a variation the script should choose the right quantity per recipe considering the recipe is 100L or 100kg per batch depends on if the final product is liquid or solid, here's my script:


# 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
)
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,
    get_ingredient_name_by_id, update_ingredient
)
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(QMessageBox):
    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
        self.addButton(QPushButton("OK"), QMessageBox.AcceptRole)
        self.addButton(QPushButton("Cancel"), QMessageBox.RejectRole)

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

    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("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):
        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, description, ingredients, sizes = 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, 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()
                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 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.")
                    logging.warning("Attempted to update recipe without ingredients.")
                    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()
                    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(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}")
            logging.debug(f"Added ingredient to recipe: {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))
            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):
        size, ok = QInputDialog.getText(self, "Add Size Variation", "Enter size (e.g., 200ml, 1L):")
        if ok and size:
            self.sizes_list.addItem(size.strip())
            logging.debug(f"Added size variation: {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))
            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()
        description = self.description_input.text().strip()
        ingredients = []
        for index in range(self.ingredients_list.count()):
            item_text = self.ingredients_list.item(index).text()
            try:
                name_qty, uom = item_text.rsplit(' ', 1)
                name_part, qty_part = name_qty.split(': ')
                ingredient_name = name_part.strip()
                qty = float(qty_part.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
        sizes = [self.sizes_list.item(i).text().strip() 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:
            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()
        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.")
            logging.warning("No ingredient selected in SelectIngredientDialog.")
            return
        qty = self.quantity_input.text().strip()
        if not qty:
            QMessageBox.warning(self, "Input Error", "Please enter a quantity.")
            logging.warning("No quantity entered in SelectIngredientDialog.")
            return
        try:
            qty_val = float(qty)
            if qty_val <= 0:
                raise ValueError
            super().accept()
        except ValueError:
            QMessageBox.warning(self, "Input Error", "Please enter a valid positive number for quantity.")
            logging.warning("Invalid quantity entered in SelectIngredientDialog.")
            return

    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())
        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(recipe_id)
            self.size_combo.addItems(sizes)
            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 = 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.")
                logging.warning("Invalid quantity entered in ProductionDataTab.")
                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()
                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 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
        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()