import sqlite3
import os

# Define the path for the database
DB_DIR = "data"
DB_PATH = os.path.join(DB_DIR, "ecommerce_data.db")

def get_db_connection():
    """Establishes a connection to the SQLite database."""
    if not os.path.exists(DB_PATH):
        raise FileNotFoundError(f"Database file not found at {DB_PATH}. Run generate_data.py first.")
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row # Return rows as dictionary-like objects
    return conn

def get_order_details(order_id):
    """Fetches details for a specific order."""
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("""
        SELECT o.order_id, o.order_date, o.status, o.total_amount, u.name as user_name, u.email
        FROM orders o
        JOIN users u ON o.user_id = u.user_id
        WHERE o.order_id = ?
    """, (order_id,))
    order = cursor.fetchone()
    conn.close()
    return dict(order) if order else None

def get_order_items(order_id):
    """Fetches all items associated with a specific order."""
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("""
        SELECT p.name as product_name, oi.quantity, oi.price_per_unit
        FROM order_items oi
        JOIN products p ON oi.product_id = p.product_id
        WHERE oi.order_id = ?
    """, (order_id,))
    items = cursor.fetchall()
    conn.close()
    return [dict(item) for item in items]

def get_user_recent_orders(user_id, limit=5):
    """Fetches the most recent orders for a specific user."""
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("""
        SELECT order_id, order_date, status, total_amount
        FROM orders
        WHERE user_id = ?
        ORDER BY order_date DESC
        LIMIT ?
    """, (user_id, limit))
    orders = cursor.fetchall()
    conn.close()
    return [dict(order) for order in orders]

def remove_order_details(order_id):
    """Removes order details from the database for a given order_id."""
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("DELETE FROM order_items WHERE order_id = ?", (order_id,))
        conn.commit()
        conn.close()
        return True
    except sqlite3.Error as e:
        conn.rollback()
        conn.close()
        print(f"Database error removing order details: {e}")
        return False

def remove_order(order_id):
    """Removes an order from the database."""
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("DELETE FROM orders WHERE order_id = ?", (order_id,))
        conn.commit()
        conn.close()
        return True
    except sqlite3.Error as e:
        conn.rollback()
        conn.close()
        print(f"Database error removing order: {e}")
        return False

def remove_user(user_id):
    """Removes a user from the database."""
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("DELETE FROM users WHERE user_id = ?", (user_id,))
        conn.commit()
        conn.close()
        return True
    except sqlite3.Error as e:
        conn.rollback()
        conn.close()
        print(f"Database error removing user: {e}")
        return False


def add_product(name, description, price, stock):
    """Adds a new product to the database."""
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("""
            INSERT INTO products (name, description, price, stock)
            VALUES (?, ?, ?, ?)
        """, (name, description, price, stock))
        conn.commit()
        product_id = cursor.lastrowid
        conn.close()
        return product_id # Return the ID of the newly added product
    except sqlite3.Error as e:
        conn.rollback() # Roll back changes if error occurs
        conn.close()
        print(f"Database error adding product: {e}")
        return None # Indicate failure

def add_user(name, email, address):
    """Adds a new user to the database."""
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("""
            INSERT INTO users (name, email, address)
            VALUES (?, ?, ?)
        """, (name, email, address))
        conn.commit()
        user_id = cursor.lastrowid
        conn.close()
        return user_id
    except sqlite3.Error as e:
        conn.rollback()
        conn.close()
        print(f"Database error adding user: {e}")
        return None

def add_order(user_id, order_date, status, total_amount):
     """Adds a new order to the database."""
     conn = get_db_connection()
     cursor = conn.cursor()
     try:
          cursor.execute("""
               INSERT INTO orders (user_id, order_date, status, total_amount)
               VALUES (?, ?, ?, ?)
          """, (user_id, order_date, status, total_amount))
          conn.commit()
          order_id = cursor.lastrowid
          conn.close()
          return order_id
     except sqlite3.Error as e:
          conn.rollback()
          conn.close()
          print(f"Database error adding order: {e}")
          return None

def add_order_item(order_id, product_id, quantity, price_per_unit):
     """Adds a new order item to the database."""
     conn = get_db_connection()
     cursor = conn.cursor()
     try:
          cursor.execute("""
               INSERT INTO order_items (order_id, product_id, quantity, price_per_unit)
               VALUES (?, ?, ?, ?)
          """, (order_id, product_id, quantity, price_per_unit))
          conn.commit()
          order_item_id = cursor.lastrowid
          conn.close()
          return order_item_id
     except sqlite3.Error as e:
          conn.rollback()
          conn.close()
          print(f"Database error adding order item: {e}")
          return None


def get_all_order_details():
    """Fetches all order details from the database."""
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT order_id, product_id, quantity, price_per_unit FROM order_items ORDER BY order_id")
    order_details = cursor.fetchall()
    conn.close()
    return [dict(order_detail) for order_detail in order_details]

def get_user_by_email(email):
    """Fetches user details by email address."""
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT user_id, name, email, address FROM users WHERE email = ?", (email,))
    user = cursor.fetchone()
    conn.close()
    return dict(user) if user else None

def get_all_products():
    """Fetches all products from the database."""
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT product_id, name, description, price, stock FROM products ORDER BY product_id")
    products = cursor.fetchall()
    conn.close()
    return [dict(product) for product in products]

# Example usage (optional, for testing)
if __name__ == "__main__":
    print("Testing Data Manager...")
    # Ensure data exists first by running generate_data.py if needed
    if not os.path.exists(DB_PATH):
        print("Database not found. Please run generate_data.py first.")
    else:
        test_order_id = 1
        print(f"\n--- Order Details (ID: {test_order_id}) ---")
        details = get_order_details(test_order_id)
        if details:
            print(details)
            print(f"\n--- Items for Order ID: {test_order_id} ---")
            items = get_order_items(test_order_id)
            for item in items:
                print(item)
        else:
            print(f"Order {test_order_id} not found.")

        test_user_id = 1
        print(f"\n--- Recent Orders for User ID: {test_user_id}) ---")
        recent_orders = get_user_recent_orders(test_user_id)
        if recent_orders:
            for order in recent_orders:
                print(order)
        else:
             print(f"No orders found for user {test_user_id}.")

        test_email = 'alice@example.com'
        print(f"\n--- User details for Email: {test_email} ---")
        user = get_user_by_email(test_email)
        if user:
            print(user)
        else:
            print(f"User with email {test_email} not found.")

        print("\n--- All Products ---")
        all_products = get_all_products()
        if all_products:
            for prod in all_products[:5]: # Print first 5
                print(prod)
        else:
            print("No products found.")

        # Example Add/Remove (Use with caution on real data)
        # print("\n--- Adding Test Product ---")
        # new_id = add_product("Test Widget", "A temporary test product", 99.99, 50)
        # if new_id:
        #     print(f"Added product with ID: {new_id}")
        #     print("\n--- Removing Test Product ---")
        #     removed = remove_product(new_id)
        #     print(f"Removal successful: {removed}")
        # else:
        #     print("Failed to add test product.")


def get_all_order_details():
    """Fetches all order details from the database."""
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT order_id, product_id, quantity, price_per_unit FROM order_items ORDER BY order_id")
    order_details = cursor.fetchall()
    conn.close()
    return [dict(order_detail) for order_detail in order_details]

def remove_user(user_id):
    """Removes a user from the database."""
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("DELETE FROM users WHERE user_id = ?", (user_id,))
        conn.commit()
        conn.close()
        return True
    except sqlite3.Error as e:
        conn.rollback()
        conn.close()
        print(f"Database error removing user: {e}")
        return False

def remove_order(order_id):
    """Removes an order from the database."""
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("DELETE FROM orders WHERE order_id = ?", (order_id,))
        conn.commit()
        conn.close()
        return True
    except sqlite3.Error as e:
        conn.rollback()
        conn.close()
        print(f"Database error removing order: {e}")
        return False

def remove_order_details(order_id):
    """Removes order details from the database for a given order_id."""
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("DELETE FROM order_items WHERE order_id = ?", (order_id,))
        conn.commit()
        conn.close()
        return True
    except sqlite3.Error as e:
        conn.rollback()
        conn.close()
        print(f"Database error removing order details: {e}")
        return False

def get_all_users():
    """Fetches all users from the database."""
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT user_id, name, email, address FROM users ORDER BY user_id")
    users = cursor.fetchall()
    conn.close()
    return [dict(user) for user in users]

def get_all_orders():
    """Fetches all orders from the database."""
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT order_id, user_id, order_date, status, total_amount FROM orders ORDER BY order_id")
    orders = cursor.fetchall()
    conn.close()
    return [dict(order) for order in orders]