import sqlite3
import pandas as pd

def create_connection():
    """ Create a database connection to the SQLite database """
    try:
        conn = sqlite3.connect('bike_store.db')
        print("Connection established: Database is connected")
        return conn
    except Exception as e:
        print("Error connecting to database:", e)
        return None

def create_table(conn, create_table_sql):
    """ Create a table from the create_table_sql statement """
    try:
        c = conn.cursor()
        c.execute(create_table_sql)
        print(f"Table created successfully or already exists.")
    except Exception as e:
        print(f"Error creating table: {e}")

def import_data_to_table(csv_file, table_name, conn):
    """ Load data from a CSV file and insert it into the specified table """
    try:
        df = pd.read_csv(csv_file)
        df.to_sql(table_name, conn, if_exists='append', index=False)
        print(f"Data imported successfully into {table_name}.")
    except Exception as e:
        print(f"Error importing data into {table_name}: {e}")

def main():
    # Create a database connection
    conn = create_connection()
    if conn is not None:
    # SQL table creation statements
        tables_sql = {
        "customers": """ CREATE TABLE IF NOT EXISTS customers (
                            customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
                            first_name TEXT NOT NULL,
                            last_name TEXT NOT NULL,
                            phone TEXT,
                            email TEXT,
                            street TEXT,
                            city TEXT,
                            state TEXT,
                            zip_code TEXT
                        ); """,
        "staffs": """ CREATE TABLE IF NOT EXISTS staffs (
                            staff_id INTEGER PRIMARY KEY AUTOINCREMENT,
                            first_name TEXT NOT NULL,
                            last_name TEXT NOT NULL,
                            email TEXT,
                            phone TEXT,
                            active INTEGER,
                            store_id INTEGER,
                            manager_id INTEGER,
                            FOREIGN KEY (store_id) REFERENCES stores(store_id),
                            FOREIGN KEY (manager_id) REFERENCES staffs(staff_id)
                        ); """,
        "stores": """ CREATE TABLE IF NOT EXISTS stores (
                            store_id INTEGER PRIMARY KEY AUTOINCREMENT,
                            store_name TEXT NOT NULL,
                            phone TEXT,
                            email TEXT,
                            street TEXT,
                            city TEXT,
                            state TEXT,
                            zip_code TEXT
                        ); """,
        "categories": """ CREATE TABLE IF NOT EXISTS categories (
                            category_id INTEGER PRIMARY KEY AUTOINCREMENT,
                            category_name TEXT NOT NULL
                            ); """,
        "products": """ CREATE TABLE IF NOT EXISTS products (
                            product_id INTEGER PRIMARY KEY AUTOINCREMENT,
                            product_name TEXT NOT NULL,
                            category_id INTEGER,
                            brand_id INTEGER,
                            model_year INTEGER,
                            list_price REAL,
                            FOREIGN KEY (category_id) REFERENCES categories(category_id),
                            FOREIGN KEY (brand_id) REFERENCES brands(brand_id)
                        ); """,
        "brands": """ CREATE TABLE IF NOT EXISTS brands (
                            brand_id INTEGER PRIMARY KEY AUTOINCREMENT,
                            brand_name TEXT NOT NULL
                        ); """,
        "stocks": """ CREATE TABLE IF NOT EXISTS stocks (
                            store_id INTEGER,
                            product_id INTEGER,
                            quantity INTEGER,
                            PRIMARY KEY (store_id, product_id),
                            FOREIGN KEY (store_id) REFERENCES stores(store_id),
                            FOREIGN KEY (product_id) REFERENCES products(product_id)
                        ); """,
        "orders": """ CREATE TABLE IF NOT EXISTS orders (
                            order_id INTEGER PRIMARY KEY AUTOINCREMENT,
                            customer_id INTEGER,
                            order_status TEXT,
                            order_date TEXT,
                            required_date TEXT,
                            shipped_date TEXT,
                            store_id INTEGER,
                            staff_id INTEGER,
                            FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
                            FOREIGN KEY (store_id) REFERENCES stores(store_id),
                            FOREIGN KEY (staff_id) REFERENCES staffs(staff_id)
                        ); """,
        "order_items": """ CREATE TABLE IF NOT EXISTS order_items (
                            order_id INTEGER,
                            item_id INTEGER PRIMARY KEY AUTOINCREMENT,
                            product_id INTEGER,
                            quantity INTEGER,
                            list_price REAL,
                            discount REAL,
                            FOREIGN KEY (order_id) REFERENCES orders(order_id),
                            FOREIGN KEY (product_id) REFERENCES products(product_id)
                            ); """
        }

    # Create tables
    for table_name, sql_command in tables_sql.items():
        create_table(conn, sql_command)

    # Data import paths
    data_paths = {
        "customers": "/Users/victorg/Documents/Streamlitapp/InteractiveDBApp/CSV/customers.csv",
        "staffs": "/Users/victorg/Documents/Streamlitapp/InteractiveDBApp/CSV/staffs.csv",
        "products": "/Users/victorg/Documents/Streamlitapp/InteractiveDBApp/CSV/products.csv",
        "categories": "/Users/victorg/Documents/Streamlitapp/InteractiveDBApp/CSV/categories.csv",
        "stores": "/Users/victorg/Documents/Streamlitapp/InteractiveDBApp/CSV/stores.csv",
        "brands": "/Users/victorg/Documents/Streamlitapp/InteractiveDBApp/CSV/brands.csv",
        "stocks": "/Users/victorg/Documents/Streamlitapp/InteractiveDBApp/CSV/stocks.csv",
        "orders": "/Users/victorg/Documents/Streamlitapp/InteractiveDBApp/CSV/orders.csv",
        "order_items": "/Users/victorg/Documents/Streamlitapp/InteractiveDBApp/CSV/order_items.csv",
    }

    # Import data to tables
    for table_name, csv_path in data_paths.items():
        import_data_to_table(csv_path, table_name, conn)

    # Close the connection
        pass
        conn.close()
    else:
        print("Failed to create database connection.")


if __name__ == '__main__':
    main()