import os
from dotenv import load_dotenv
load_dotenv()
from sqlalchemy import create_engine, text
from fastapi import FastAPI, Depends

DATABASE_URL = os.getenv('DATABASE_URL')

def get_engine():
    if not DATABASE_URL:
        raise ValueError("L'URL de la base de données (DATABASE_URL) n'est pas définie dans l'environnement.")
    engine = create_engine(DATABASE_URL)
    return engine

def get_db_connection(engine=Depends(get_engine)):
    with engine.connect() as connection:
        yield connection

def load_job_offer(connection):
    # MISE À JOUR : Requête avec toutes les nouvelles colonnes
    query_offre = text('''
        SELECT entreprise, ville, poste, contrat, description_poste, publication, lien, id,
               description_nettoyee, mission, profil_recherche, competences, pole 
        FROM "job_offers"
    ''')
    resultats = connection.execute(query_offre).fetchall()
    offres = []
    for row in resultats:
        entreprise, ville, poste, contrat, description_poste, publication, lien, id, description_nettoyee, mission, profil_recherche, competences, pole = row
        offres.append({
            "entreprise": entreprise,
            "ville": ville,
            "poste": poste,
            "contrat": contrat,
            "description_poste": description_poste,
            "publication": publication,
            "lien": lien,
            "id": id,
            "description_nettoyee": description_nettoyee,
            "mission": mission,
            "profil_recherche": profil_recherche,
            "competences": competences,
            "pole": pole
        })
    return offres

def get_job_offer_by_id(connection, offre_id: str):
    query = text('''
        SELECT entreprise, ville, poste, contrat, description_poste, publication, lien, id,
               description_nettoyee, mission, profil_recherche, competences, pole 
        FROM "job_offers" 
        WHERE id = :id
    ''')
    result = connection.execute(query, {"id": offre_id}).fetchone()
    if result:
        entreprise, ville, poste, contrat, description_poste, publication, lien, id, description_nettoyee, mission, profil_recherche, competences, pole = result
        return {
            "entreprise": entreprise,
            "ville": ville,
            "poste": poste,
            "contrat": contrat,
            "description_poste": description_poste,
            "publication": publication,
            "lien": lien,
            "id": id,
            "description_nettoyee": description_nettoyee,
            "mission": mission,
            "profil_recherche": profil_recherche,
            "competences": competences,
            "pole": pole
        }
    return None

app = FastAPI()

@app.get("/")
def root():
    return {"status": "API en marche", "message": "Bienvenue sur l'API d'offres d'emploi !"}

@app.get("/offre-emploi")
async def read_job_offer(connection = Depends(get_db_connection)):
    offres = load_job_offer(connection)
    if offres:
        return offres
    return {"message": "Aucune offre d'emploi trouvée."}

@app.get("/offre-emploi/{offre_id}")
async def read_job_offer_by_id(offre_id: str, connection = Depends(get_db_connection)):
    offre = get_job_offer_by_id(connection, offre_id)
    if offre:
        return offre
    return {"message": f"Aucune offre trouvée avec l'id {offre_id}"}

if __name__ == "__main__":
    import uvicorn
    port = int(os.environ.get("PORT", 7860))
    uvicorn.run(app, host="0.0.0.0", port=port)