utils/database_handler.py

import pandas as pd
import os

# Set a writable path for the Excel file within the app directory
BASE_DIR = os.path.dirname(os.path.abspath(__file__))  # Current script directory
CUSTOMERS_FILE = os.path.join(BASE_DIR, "..workspace/database/Book1.xlsx")

# Ensure the database directory exists
if not os.path.exists(os.path.dirname(CUSTOMERS_FILE)):
    os.makedirs(os.path.dirname(CUSTOMERS_FILE))  # Create database directory

# Ensure the file exists with the required structure
if not os.path.exists(CUSTOMERS_FILE):
    print("Creating new Excel file as it doesn't exist.")
    pd.DataFrame(columns=["Name", "Phone", "Email", "Password"]).to_excel(CUSTOMERS_FILE, index=False)

def check_credentials(email, password):
    try:
        df = pd.read_excel(CUSTOMERS_FILE)
    except Exception as e:
        print("Error reading Excel file:", e)
        return False

    user = df[(df["Email"] == email) & (df["Password"] == password)]
    return not user.empty

def save_user(name, phone, email, password):
    print("Attempting to save user:", name, phone, email, password)

    try:
        # Read the existing data
        df = pd.read_excel(CUSTOMERS_FILE)
    except Exception as e:
        print("Error reading Excel file:", e)
        return False

    print("Existing data before appending:\n", df)

    # Check if email already exists
    if email in df["Email"].values:
        print("Error: Email already exists.")
        return False

    # Add new user data
    new_user = {
        "Name": str(name).strip(),
        "Phone": str(phone).strip(),
        "Email": str(email).strip(),
        "Password": str(password).strip()
    }
    df = pd.concat([df, pd.DataFrame([new_user])], ignore_index=True)

    # Save updated data back to the Excel file
    try:
        with pd.ExcelWriter(CUSTOMERS_FILE, engine="openpyxl", mode="w") as writer:
            df.to_excel(writer, index=False)
        print("User saved successfully. Updated data:\n", df)

        # Confirm save by re-reading the file
        df_check = pd.read_excel(CUSTOMERS_FILE)
        print("Data in file after saving:\n", df_check)
        return True
    except Exception as e:
        print("Error while saving to Excel:", e)
        return False