database/db_connection.py

import os
from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker
from dotenv import load_dotenv

load_dotenv()

# Récupération de la connection string
DATABASE_URL = os.getenv("NEON_DB")

# Création de l'engine SQLAlchemy
engine = create_engine(DATABASE_URL, echo=False)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

def get_db_session():
    """Retourne une session SQLAlchemy"""
    return SessionLocal()

def init_database():
    """Initialise les tables si elles n'existent pas"""
    with engine.connect() as conn:
        # Table users
        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS users (
                user_id SERIAL PRIMARY KEY,
                username VARCHAR(50) UNIQUE NOT NULL,
                password_hash VARCHAR(255) NOT NULL,
                created_at TIMESTAMP DEFAULT NOW()
            );
        """))
        
        # Table applications
        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS applications (
                app_id SERIAL PRIMARY KEY,
                user_id INT REFERENCES users(user_id) ON DELETE CASCADE,
                company VARCHAR(255) NOT NULL,
                description TEXT,
                application_date DATE NOT NULL,
                end_date DATE,
                expected_delay_days INT,
                status VARCHAR(50) DEFAULT 'En attente',
                created_at TIMESTAMP DEFAULT NOW(),
                updated_at TIMESTAMP DEFAULT NOW()
            );
        """))
        
        # Table notes
        conn.execute(text("""
            CREATE TABLE IF NOT EXISTS notes (
                note_id SERIAL PRIMARY KEY,
                app_id INT REFERENCES applications(app_id) ON DELETE CASCADE,
                note_text TEXT NOT NULL,
                created_at TIMESTAMP DEFAULT NOW(),
                updated_at TIMESTAMP DEFAULT NOW()
            );
        """))
        
        conn.commit()