from fastapi import FastAPI, HTTPException
from pydantic import BaseModel
from typing import List, Optional
from commandManager import CommandManager
import sqlite3
import os

DB_PATH = os.path.join(os.path.dirname(__file__), 'smartbar.db')

app = FastAPI()

# --- Database Setup ---
def init_db():
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    # Users table
    c.execute('''CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT UNIQUE NOT NULL,
        password TEXT NOT NULL,
        role TEXT
    )''')
    # Products table
    c.execute('''CREATE TABLE IF NOT EXISTS products (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT UNIQUE NOT NULL,
        price REAL NOT NULL
    )''')
    # Orders table
    c.execute('''CREATE TABLE IF NOT EXISTS orders (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER,
        status TEXT,
        total_price REAL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY(user_id) REFERENCES users(id)
    )''')
    # Order-Products relation
    c.execute('''CREATE TABLE IF NOT EXISTS order_products (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        order_id INTEGER,
        product_id INTEGER,
        quantity INTEGER,
        FOREIGN KEY(order_id) REFERENCES orders(id),
        FOREIGN KEY(product_id) REFERENCES products(id)
    )''')
    conn.commit()
    conn.close()

init_db()

# --- Pydantic Models ---
class UserCreate(BaseModel):
    username: str
    password: str
    role: Optional[str] = "user"

class UserOut(BaseModel):
    id: int
    username: str
    role: str

class ProductCreate(BaseModel):
    name: str
    price: float

class ProductOut(BaseModel):
    id: int
    name: str
    price: float

class OrderProduct(BaseModel):
    product_id: int
    quantity: int

class OrderCreate(BaseModel):
    user_id: int
    products: List[OrderProduct]

class OrderOut(BaseModel):
    id: int
    user_id: int
    status: str
    total_price: float
    created_at: str
    products: List[OrderProduct]

# --- User Endpoints ---
@app.post("/users", response_model=UserOut)
def create_user(user: UserCreate):
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    try:
        c.execute("INSERT INTO users (username, password, role) VALUES (?, ?, ?)", (user.username, user.password, user.role))
        conn.commit()
        user_id = c.lastrowid
        return {"id": user_id, "username": user.username, "role": user.role}
    except sqlite3.IntegrityError:
        raise HTTPException(status_code=400, detail="Username already exists")
    finally:
        conn.close()

# --- Product Endpoints ---
@app.post("/products", response_model=ProductOut)
def create_product(product: ProductCreate):
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    try:
        c.execute("INSERT INTO products (name, price) VALUES (?, ?)", (product.name, product.price))
        conn.commit()
        product_id = c.lastrowid
        return {"id": product_id, "name": product.name, "price": product.price}
    except sqlite3.IntegrityError:
        raise HTTPException(status_code=400, detail="Product already exists")
    finally:
        conn.close()

@app.get("/products", response_model=List[ProductOut])
def list_products():
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    c.execute("SELECT id, name, price FROM products")
    products = c.fetchall()
    conn.close()
    return [{"id": p[0], "name": p[1], "price": p[2]} for p in products]

# --- Order Endpoints ---
@app.post("/orders")
def create_order(order: OrderCreate):
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    # Calculate total price
    total = 0
    for op in order.products:
        c.execute("SELECT price FROM products WHERE id=?", (op.product_id,))
        row = c.fetchone()
        if not row:
            conn.close()
            raise HTTPException(status_code=404, detail=f"Product id {op.product_id} not found")
        total += row[0] * op.quantity
    # Insert order
    c.execute("INSERT INTO orders (user_id, status, total_price) VALUES (?, ?, ?)", (order.user_id, "pending", total))
    order_id = c.lastrowid
    # Insert order_products
    for op in order.products:
        c.execute("INSERT INTO order_products (order_id, product_id, quantity) VALUES (?, ?, ?)", (order_id, op.product_id, op.quantity))
    conn.commit()
    conn.close()
    return {"order_id": order_id, "total_price": total}

@app.get("/orders/{order_id}")
def get_order(order_id: int):
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    c.execute("SELECT id, user_id, status, total_price, created_at FROM orders WHERE id=?", (order_id,))
    order = c.fetchone()
    if not order:
        conn.close()
        raise HTTPException(status_code=404, detail="Order not found")
    c.execute("SELECT product_id, quantity FROM order_products WHERE order_id=?", (order_id,))
    products = c.fetchall()
    conn.close()
    return {
        "id": order[0],
        "user_id": order[1],
        "status": order[2],
        "total_price": order[3],
        "created_at": order[4],
        "products": [{"product_id": p[0], "quantity": p[1]} for p in products]
    }

# --- Distribución de camareros (usando tu lógica Python) ---
@app.post("/orders/{order_id}/distribute")
def distribute_order(order_id: int):
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    c.execute("SELECT product_id, quantity FROM order_products WHERE order_id=?", (order_id,))
    products = c.fetchall()
    # Obtener nombres de productos
    order_dict = {}
    for pid, qty in products:
        c.execute("SELECT name FROM products WHERE id=?", (pid,))
        name = c.fetchone()[0]
        order_dict[name] = qty
    conn.close()
    # Llama a tu lógica de distribución
    manager = CommandManager()
    distribution = manager.distributor.distribute_single_order(order_dict)
    return {"distribution": distribution}
