import sqlite3

conn = sqlite3.connect('shop.db')
cursor = conn.cursor()
print(type(conn))
print(type(cursor))

cursor.execute('''
    CREATE TABLE IF NOT EXISTS customers (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        city TEXT,
        signup_date TEXT
    )
''')
conn.commit()
print('customers table ready')

cursor.execute('''
    CREATE TABLE IF NOT EXISTS orders (
        id INTEGER PRIMARY KEY,
        customer_id INTEGER,
        product TEXT NOT NULL,
        amount REAL,
        order_date TEXT,
        FOREIGN KEY (customer_id) REFERENCES customers (id)
    )
''')
conn.commit()
print('orders table ready')

cursor.execute(
    'INSERT INTO customers (name, city, signup_date) VALUES (?, ?, ?)',
    ('Amir Hassan', 'Austin', '2023-01-15')
)
conn.commit()
print('One customer inserted')

import random

random.seed(5)
cities = ['Austin', 'Denver', 'Seattle', 'Miami', 'Boston']
names = ['Bianca Silva', 'Carlos Mendes', 'Deepa Nair', 'Elin Berg', 'Farid Khan', 'Grace Oduya', 'Hana Kim', 'Ivan Petrov', 'Julia Novak', 'Kenji Sato', 'Lena Fischer', 'Marco Rossi', 'Nadia Ali', 'Omar Idris', 'Priya Rao']

customer_rows = []
for i, name in enumerate(names):
    city = random.choice(cities)
    month = random.randint(1, 12)
    day = random.randint(1, 28)
    customer_rows.append((name, city, f'2023-{month:02d}-{day:02d}'))

cursor.executemany(
    'INSERT INTO customers (name, city, signup_date) VALUES (?, ?, ?)',
    customer_rows
)
conn.commit()
print(f'{len(customer_rows)} more customers inserted')

random.seed(9)
products = ['Wireless Mouse', 'Mechanical Keyboard', 'Desk Lamp', 'Webcam', 'Monitor Stand']
order_rows = []
for _ in range(60):
    customer_id = random.randint(1, 16)
    product = random.choice(products)
    amount = round(random.uniform(15, 250), 2)
    month = random.randint(1, 12)
    day = random.randint(1, 28)
    order_rows.append((customer_id, product, amount, f'2023-{month:02d}-{day:02d}'))

cursor.executemany(
    'INSERT INTO orders (customer_id, product, amount, order_date) VALUES (?, ?, ?, ?)',
    order_rows
)
conn.commit()
print(f'{len(order_rows)} orders inserted')

cursor.execute('SELECT * FROM customers LIMIT 5')
rows = cursor.fetchall()
for row in rows:
    print(row)

cursor.execute("SELECT name, city FROM customers WHERE city = 'Austin'")
austin_customers = cursor.fetchall()
print(austin_customers)

cursor.execute('SELECT name, amount FROM orders JOIN customers ON orders.customer_id = customers.id ORDER BY amount DESC LIMIT 1')
top_order = cursor.fetchone()
print(top_order)

cursor.execute('''
    SELECT customers.name, orders.product, orders.amount
    FROM orders
    JOIN customers ON orders.customer_id = customers.id
    LIMIT 5
''')
joined_rows = cursor.fetchall()
for row in joined_rows:
    print(row)

cursor.execute('''
    SELECT customers.name, COUNT(orders.id) AS order_count, SUM(orders.amount) AS total_spent
    FROM customers
    JOIN orders ON customers.id = orders.customer_id
    GROUP BY customers.name
    ORDER BY total_spent DESC
    LIMIT 5
''')
top_spenders = cursor.fetchall()
for row in top_spenders:
    print(row)

import pandas as pd

customers_df = pd.read_sql('SELECT * FROM customers', conn)
print(customers_df.head())
print(customers_df.shape)

spending_query = '''
    SELECT customers.city, SUM(orders.amount) AS total_spent
    FROM customers
    JOIN orders ON customers.id = orders.customer_id
    GROUP BY customers.city
    ORDER BY total_spent DESC
'''
city_spending = pd.read_sql(spending_query, conn)
print(city_spending)

new_customers = pd.DataFrame({
    'name': ['Sofia Reyes', 'Toms Silva'],
    'city': ['Chicago', 'Portland'],
    'signup_date': ['2024-02-01', '2024-02-03'],
})
new_customers.to_sql('customers', conn, if_exists='append', index=False)
print('2 new customers written via pandas')

unsafe_city = "Austin' OR '1'='1"
unsafe_query = f"SELECT * FROM customers WHERE city = '{unsafe_city}'"
print(unsafe_query)

cursor.execute('SELECT * FROM customers WHERE city = ?', (unsafe_city,))
safe_results = cursor.fetchall()
print(len(safe_results))

with sqlite3.connect('shop.db') as auto_conn:
    auto_cursor = auto_conn.cursor()
    auto_cursor.execute("INSERT INTO customers (name, city, signup_date) VALUES ('Uma Patel', 'Chicago', '2024-03-01')")
print('Insert committed automatically on exit')

cursor.execute("SELECT COUNT(*) FROM customers WHERE name = 'Uma Patel'")
print(cursor.fetchone())

def build_sales_report(connection, min_orders=1):
    top_customers = pd.read_sql('''
        SELECT customers.name, customers.city, COUNT(orders.id) AS order_count, SUM(orders.amount) AS total_spent
        FROM customers
        JOIN orders ON customers.id = orders.customer_id
        GROUP BY customers.id
        HAVING COUNT(orders.id) >= ?
        ORDER BY total_spent DESC
    ''', connection, params=(min_orders,))

    product_popularity = pd.read_sql('''
        SELECT product, COUNT(*) AS times_ordered, AVG(amount) AS avg_amount
        FROM orders
        GROUP BY product
        ORDER BY times_ordered DESC
    ''', connection)

    city_summary = pd.read_sql('''
        SELECT customers.city, COUNT(DISTINCT customers.id) AS customer_count, SUM(orders.amount) AS total_revenue
        FROM customers
        JOIN orders ON customers.id = orders.customer_id
        GROUP BY customers.city
        ORDER BY total_revenue DESC
    ''', connection)

    return {
        'top_customers': top_customers,
        'product_popularity': product_popularity,
        'city_summary': city_summary,
    }

reports = build_sales_report(conn, min_orders=2)
print(reports['top_customers'].head())

print(reports['product_popularity'])
print()
print(reports['city_summary'])

conn.close()
print('Connection closed')