import os
import sqlite3
import requests
from dotenv import load_dotenv

load_dotenv()

STRIPE_SECRET_KEY = os.getenv("STRIPE_SECRET_KEY")

# Create a new SQLite database file (if it doesn't exist)
conn = sqlite3.connect('your_database.db')
c = conn.cursor()

# Create tables for customers, orders, and payments
c.execute('''CREATE TABLE IF NOT EXISTS customers
             (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, email TEXT)''')
c.execute('''CREATE TABLE IF NOT EXISTS orders
             (id INTEGER PRIMARY KEY AUTOINCREMENT, customer_id INTEGER, items TEXT, total REAL)''')
c.execute('''CREATE TABLE IF NOT EXISTS payments
             (id INTEGER PRIMARY KEY AUTOINCREMENT, order_id INTEGER, amount REAL, status TEXT)''')

def create_customer(customer_data):
    name = customer_data.get("name")
    email = customer_data.get("email")
    c.execute("INSERT INTO customers (name, email) VALUES (?, ?)", (name, email))
    conn.commit()
    return c.lastrowid

def create_order(order_data):
    customer_id = order_data.get("customer_id")
    items = str(order_data.get("items"))
    total = order_data.get("total")
    c.execute("INSERT INTO orders (customer_id, items, total) VALUES (?, ?, ?)", (customer_id, items, total))
    conn.commit()
    return c.lastrowid

def process_payment(payment_data):
    order_id = payment_data.get("order_id")
    amount = payment_data.get("amount")
    # Implement payment processing logic using Stripe API
    status = "paid"  # Replace with actual payment status
    c.execute("INSERT INTO payments (order_id, amount, status) VALUES (?, ?, ?)", (order_id, amount, status))
    conn.commit()
    return c.lastrowid

# Add more utility functions as needed