import pandas as pd
import os

DB_FILE = 'database.xlsx'

def init_db():
    if os.path.exists(DB_FILE):
        print(f"{DB_FILE} already exists.")
        return

    # Define sheets and their headers
    sheets = {
        'Devices': ['IMEI', 'Model', 'Status', 'Price', 'Added Date'],
        'Sales': ['IMEI', 'Vehicle No', 'SIM No', 'Sale Date', 'Warranty Start', 'Warranty End', 'Payment Status', 'Docket No', 'Sales Person', 'Installer'],
        'Users': ['Username', 'Password', 'Role', 'Name'],
        'Settings': ['Key', 'Value']
    }

    with pd.ExcelWriter(DB_FILE, engine='openpyxl') as writer:
        for sheet_name, columns in sheets.items():
            df = pd.DataFrame(columns=columns)
            df.to_excel(writer, sheet_name=sheet_name, index=False)
            print(f"Created sheet: {sheet_name}")

    # Add default admin user
    try:
        df_users = pd.read_excel(DB_FILE, sheet_name='Users', engine='openpyxl')
        new_user = pd.DataFrame([{'Username': 'orbit', 'Password': '123456', 'Role': 'Admin', 'Name': 'Administrator'}])
        # Append using openpyxl directly or re-save might be safer to avoid overwriting other sheets? 
        # Actually with pandas ExcelWriter in 'a' mode it works, but fresh create is safer.
        # Let's just re-write the file with data since it's fresh.
        
        with pd.ExcelWriter(DB_FILE, engine='openpyxl') as writer:
             for sheet_name, columns in sheets.items():
                if sheet_name == 'Users':
                     data = [{'Username': 'orbit', 'Password': '123456', 'Role': 'Admin', 'Name': 'Administrator'}]
                     df = pd.DataFrame(data, columns=columns)
                elif sheet_name == 'Settings':
                     data = [
                         {'Key': 'Device Models', 'Value': 'ITR 4G,ITR 2G,EV02,LT05,ZENITH 140,GAGAN,BLACKBOX'},
                         {'Key': 'Sales Persons', 'Value': 'Nishant Gandhi,Rajesh Kumbhani,Mehul Asodiya'},
                         {'Key': 'Installers', 'Value': 'Dilip Patani,Maulik Parmar,Nishant Gandhi,Rajesh Kumbhani,Ajay Bhai,Office,Mehul Asodiya'}
                     ]
                     df = pd.DataFrame(data, columns=columns)
                else:
                    df = pd.DataFrame(columns=columns)
                
                df.to_excel(writer, sheet_name=sheet_name, index=False)
        
        print("Default admin user and settings added.")

    except Exception as e:
        print(f"Error adding default data: {e}")

if __name__ == "__main__":
    init_db()