tools/database.py

import os
from datetime import datetime
from sqlalchemy import create_engine, Column, Integer, String, Text, DateTime, Float
from sqlalchemy.orm import declarative_base, sessionmaker
from dotenv import load_dotenv
import psycopg2

load_dotenv()

db_config= {
    "user": os.getenv("POSTGRES_USER"),
    "password": os.getenv("POSTGRES_PASSWORD"),
    "dbname": os.getenv("POSTGRES_DB"), 
    "host": os.getenv("POSTGRES_HOST"),
    "port": os.getenv("POSTGRES_PORT")
}

def get_connection():
    try:
        return psycopg2.connect(**db_config)
    except Exception as e:
        print(f"[ERREUR CONNEXION DB] : {e}")
        return None

def log_interaction(user_message, user_lat, user_lon, intention, mot_cle, message, reponse_texte):
    conn = get_connection()
    
    if not conn:
        print("[INFO] Attention connection echouer")
        return 
    try:
        with conn.cursor() as cur:
            query = """
                INSERT INTO users_interactions 
                (user_message, user_lat, user_lon, intention, mot_cle, inerne_message, response_text)
                VALUES (%s, %s, %s, %s, %s, %s, %s);
            """

            values = (
                str(user_message),
                user_lat,
                user_lon,
                intention,
                mot_cle,
                message,            
                str(reponse_texte)
            )
            
            cur.execute(query, values)
        
        conn.commit()
        
    except Exception as e:
        print(f"[INFO] Erreur d'insertion : {e}")
        conn.rollback()
        
    finally:
        # On ferme la connexion
        if conn:
            conn.close()