import sqlite3
import pandas as pd
from datasets import load_dataset

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)
        conn.commit()
        print("Table created successfully or already exists.")
    except Exception as e:
        print("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)
        print(df)
        df.to_sql(table_name, conn, if_exists='replace', 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()
    print(conn)
    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 INTEGER
                            ); """,
            "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": "CSV/customers.csv",
                "staffs": "CSV/staffs.csv",
                "products": "CSV/products.csv",
                "categories": "CSV/categories.csv",
                "stores": "CSV/stores.csv",
                "brands": "CSV/brands.csv",
                "stocks": "CSV/stocks.csv",
                "orders": "CSV/orders.csv",
                "order_items": "CSV/order_items.csv",
            }
            
        dataset = load_dataset('VicGerardoPR/InteractiveDatabseApp/CSV', data_files=data_paths)
        

        # 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
        conn.close()
    else:
        print("Failed to create database connection.")

if __name__ == '__main__':
    main()