from fastapi import FastAPI
from fastapi.responses import HTMLResponse
from fastapi.staticfiles import StaticFiles
from datetime import date
import sqlite3

app = FastAPI()
app.mount("/static", StaticFiles(directory="static"), name="static")

def get_db():
    conn = sqlite3.connect("pizza.db")
    conn.row_factory = sqlite3.Row
    return conn

def init_db():
    conn = get_db()

    conn.execute("""
        CREATE TABLE IF NOT EXISTS customers (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            phone TEXT NOT NULL
        )
    """)

    conn.execute("""
        CREATE TABLE IF NOT EXISTS orders (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            customer_id INTEGER NOT NULL,
            order_date TEXT NOT NULL,
            total REAL NOT NULL
        )
    """)

    conn.execute("""
        CREATE TABLE IF NOT EXISTS order_details (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            order_id INTEGER NOT NULL,
            pizza TEXT NOT NULL,
            quantity INTEGER NOT NULL,
            price REAL NOT NULL
        )
    """)

    conn.commit()
    conn.close()

init_db()

@app.get("/", response_class=HTMLResponse)
def index():
    with open("templates/index.html", encoding="utf-8") as f:
        return f.read()

@app.get("/place-order", response_class=HTMLResponse)
def place_order(name: str, phone: str, pepperoni: int = 0, cheese: int = 0, sausage: int = 0):
    conn = get_db()

    row = conn.execute("SELECT id FROM customers WHERE phone = ?", (phone,)).fetchone()
    if row:
        customer_id = row["id"]
    else:
        cur = conn.execute("INSERT INTO customers (name, phone) VALUES (?, ?)", (name, phone))
        customer_id = cur.lastrowid

    items = [
        ("Pepperoni", pepperoni, 12.99),
        ("Cheese", cheese, 10.99),
        ("Sausage", sausage, 12.49),
    ]

    total = sum(qty * price for _, qty, price in items if qty > 0)

    cur = conn.execute(
        "INSERT INTO orders (customer_id, order_date, total) VALUES (?, ?, ?)",
        (customer_id, str(date.today()), total)
    )
    order_id = cur.lastrowid

    for pizza, qty, price in items:
        if qty > 0:
            conn.execute(
                "INSERT INTO order_details (order_id, pizza, quantity, price) VALUES (?, ?, ?, ?)",
                (order_id, pizza, qty, price)
            )

    conn.commit()
    conn.close()
    return f"<p>Order #{order_id} placed! <a href='/'>Go back</a></p>"

@app.get("/my-orders", response_class=HTMLResponse)
def my_orders(phone: str):
    conn = get_db()

    customer = conn.execute("SELECT * FROM customers WHERE phone = ?", (phone,)).fetchone()
    if not customer:
        return "<p>No orders found. <a href='/'>Go back</a></p>"

    orders = conn.execute(
        "SELECT * FROM orders WHERE customer_id = ?", (customer["id"],)
    ).fetchall()

    html = f"<h2>Orders for {customer['name']}</h2>"
    for o in orders:
        details = conn.execute(
            "SELECT * FROM order_details WHERE order_id = ?", (o["id"],)
        ).fetchall()
        html += f"<h3>Order #{o['id']} - {o['order_date']} - Total: ${o['total']:.2f}</h3>"
        html += "<ul>"
        for d in details:
            html += f"<li>{d['pizza']} x{d['quantity']} @ ${d['price']:.2f}</li>"
        html += "</ul>"

    html += "<a href='/'>Go back</a>"
    conn.close()
    return html

@app.get("/all-orders", response_class=HTMLResponse)
def all_orders():
    conn = get_db()

    orders = conn.execute("""
        SELECT o.id, o.order_date, o.total, c.name, c.phone
        FROM orders o
        JOIN customers c ON o.customer_id = c.id
    """).fetchall()

    if not orders:
        return "<p>No orders yet. <a href='/'>Go back</a></p>"

    html = "<h2>All Orders</h2>"
    for o in orders:
        details = conn.execute(
            "SELECT * FROM order_details WHERE order_id = ?", (o["id"],)
        ).fetchall()
        html += f"<h3>Order #{o['id']} - {o['name']} ({o['phone']}) - {o['order_date']} - Total: ${o['total']:.2f}</h3>"
        html += "<ul>"
        for d in details:
            html += f"<li>{d['pizza']} x{d['quantity']} @ ${d['price']:.2f}</li>"
        html += "</ul>"

    html += "<a href='/'>Go back</a>"
    conn.close()
    return html