from fastapi import FastAPI as API
from fastapi import Depends, HTTPException, APIRouter
from sqlalchemy.orm import Session
from sqlalchemy import text
from database import engine, Base, SessionLocal
from fastapi.middleware.cors import CORSMiddleware
import schemas
import models

router = APIRouter()
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

# Create tables
# Base.metadata.create_all(bind=engine)

app = API(title="Warehouse Stock Management API")
print("API Initialized")

@app.on_event("startup")
def startup():
    print("✅ API connected to PostgreSQL successfully")

app.add_middleware(
    CORSMiddleware,
    allow_origins=[
        "http://localhost:8501",
        "http://frontend:8501"
    ],
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"],
)
app.include_router(router, prefix="/api")


@app.get("/")
def read_root():
    return {"Hello": "World"}

# ----------------{Inventory}-----------------------
@app.get("/api/inventory", response_model=list[schemas.Inventory])
def get_inventory(db: Session = Depends(get_db)):
    inventory = db.query(models.Inventory).all()
    return inventory

# ----------------{Products}-----------------------
@app.get("/api/products", response_model=list[schemas.Product])
def get_products(db: Session = Depends(get_db)):
    return db.query(models.Product).all()

# ----------------{Warehouses}-----------------------
@app.get("/api/warehouses", response_model=list[schemas.Warehouse])
def get_warehouses(db: Session = Depends(get_db)):
    return db.query(models.Warehouse).all()

# ----------------{Orders}-----------------------
@app.get("/api/orders", response_model=list[schemas.Order])
def get_orders(db: Session = Depends(get_db)):
    orders = db.query(models.Order).all()
    return orders

# ----------------{Spare Parts}-----------------------
@app.get("/api/spare_parts", response_model=list[schemas.SparePart])
def get_spare_parts(db: Session = Depends(get_db)):
    return db.query(models.SparePart).all()

# ----------------{Order Items}-----------------------
@app.get("/api/order_items", response_model=list[schemas.OrderItem])
def get_order_items(db: Session = Depends(get_db)):
    return db.query(models.OrderItem).all()

# ----------------{Recommendations}-----------------------
@app.get("/api/recommendations")
def get_recommendations():
    return [{
        "id": "rec_001",
        "sku": "ROUTER_X1",
        "from_wh": "DC_EAST",
        "to_wh": "DC_SOUTH",
        "qty": 200,
        "eta_days": 1.5,
        "reason": "Overstock in East, shortage in South"
    }]

@app.post("/api/v1/recommendations/{rec_id}/approve")
def approve(rec_id: str):
    return {"status": "approved", "id": rec_id}

@router.post("/transfer")
def transfer_inventory(payload: dict):
    """
    Fake transfer endpoint for demo purposes.
    Accepts a SKU, source/destination warehouse, and quantity.
    Returns a success response without modifying the database.
    """
    return {
        "status": "success",
        "message": f"Transferred {payload['quantity']} units of {payload['sku']} "
                   f"from Warehouse {payload['from_wh']} to Warehouse {payload['to_wh']}."
    }


@app.get("/api/all_order_data")
def get_all_order_data(db: Session = Depends(get_db)):
    query = text("""
        SELECT
            o.order_id, 
            o.order_date,
            oi.sku,
            oi.qty
        FROM orders o
        JOIN order_items oi 
            ON oi.order_id = o.order_id
    """)

    rows = db.execute(query).mappings().all()
    return list(rows)
