from fastapi import FastAPI, HTTPException
from pydantic import BaseModel, Field
import sqlite3
import uvicorn
from fastapi.middleware.cors import CORSMiddleware

class SensorData(BaseModel):
    timestamp: str
    humedad_suelo: float
    luz: float
    turbidez: float
    voltaje: float
    estado: str

app = FastAPI(
    title="API para Arduino Sensor Data",
    description="API para almacenar datos de sensores de Arduino en una base de datos SQLite",
    version="1.2.1",
)

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

@app.get("/")
def read_root():
    return {"message": "Welcome to the Arduino sensor data API"}


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

# Conexión a la base de datos SQLite
def get_db_connection():
    conn = sqlite3.connect('arduino_data.db')
    conn.row_factory = sqlite3.Row
    return conn

@app.post("/execute", summary="Ejecutar consulta SQL", description="Ejecuta una consulta SQL en la base de datos SQLite")
def execute_query(sql_query: SQLQuery):
    conn = get_db_connection()
    try:
        cleaned_query = sql_query.query.replace("\nObservation", "").replace("\\'", "'")
        print("Solicitud POST completa:", sql_query.json())
        print("Consulta recibida en el servidor:", cleaned_query)
        
        cursor = conn.cursor()
        cursor.execute(cleaned_query)
        conn.commit()
        if cursor.description:  # Verifica si la consulta tiene resultados
            results = cursor.fetchall()
            return {"results": [dict(row) for row in results]}
        return {"message": "Consulta ejecutada exitosamente"}
    except sqlite3.Error as e:
        raise HTTPException(status_code=400, detail=str(e))
    finally:
        conn.close()

@app.post("/sensor_data")
async def create_sensor_data(sensor_data: SensorData):
    try:
        conn = sqlite3.connect("arduino_data.db")
        cursor = conn.cursor()
        cursor.execute(
            "INSERT INTO sensor_data (timestamp, humedad_suelo, luz, turbidez, voltaje, estado) VALUES (?, ?, ?, ?, ?, ?)",
            (
                sensor_data.timestamp,
                sensor_data.humedad_suelo,
                sensor_data.luz,
                sensor_data.turbidez,
                sensor_data.voltaje,
                sensor_data.estado,
            ),
        )
        conn.commit()
        conn.close()
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Error inserting data: {e}")

    return {"message": "Data inserted successfully"}