from pydantic import BaseModel
from fastapi import FastAPI, HTTPException, Depends
from fastapi.middleware.cors import CORSMiddleware
from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker, declarative_base
import uvicorn
import os
from dotenv import load_dotenv

load_dotenv()  # Carga las variables de entorno desde el archivo .env

DATABASE_URL = os.getenv("DB_URI")

# Configurar SQLAlchemy
engine = create_engine(DATABASE_URL)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()

# CORS
app = FastAPI()
app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"],
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"],
)


# Modelo para recibir consultas SQL
class SQLQuery(BaseModel):
    query: str


# Dependencia para obtener la sesión de la base de datos
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()


@app.post(
    "/execute",
    summary="Ejecutar consulta SQL",
    description="Ejecuta una consulta SQL en la base de datos MySQL",
)
def execute_query(sql_query: SQLQuery, db=Depends(get_db)):
    try:
        result = db.execute(text(sql_query.query))
        if result.returns_rows:
            rows = result.fetchall()
            columns = result.keys()
            results = [dict(zip(columns, row)) for row in rows]
            return {"results": results}
        db.commit()
        return {"message": "Consulta ejecutada exitosamente"}
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=400, detail=str(e))


@app.get(
    "/list_tables",
    summary="Listar todas las tablas",
    description="Lista todas las tablas de la base de datos",
)
def list_tables(db=Depends(get_db)):
    try:
        result = db.execute(text("SHOW TABLES;"))
        tables = [row[0] for row in result.fetchall()]
        return {"tables": tables}
    except Exception as e:
        raise HTTPException(status_code=400, detail=str(e))


if __name__ == "__main__":
    uvicorn.run(app, host="0.0.0.0", port=7860)