database/queries.py

from database.db_connection import get_db_session
from sqlalchemy import text

# ============ USER QUERIES ============

def create_user(username, password_hash):
    """Crée un nouvel utilisateur"""
    session = get_db_session()
    try:
        result = session.execute(
            text("INSERT INTO users (username, password_hash) VALUES (:username, :password_hash) RETURNING user_id"),
            {"username": username, "password_hash": password_hash}
        )
        user_id = result.fetchone()[0]
        session.commit()
        return user_id
    except Exception as e:
        session.rollback()
        raise e
    finally:
        session.close()

def get_user_by_username(username):
    """Récupère un user par son username"""
    session = get_db_session()
    try:
        result = session.execute(
            text("SELECT * FROM users WHERE username = :username"),
            {"username": username}
        )
        user = result.fetchone()
        return dict(user._mapping) if user else None
    finally:
        session.close()

# ============ APPLICATION QUERIES ============

def create_application(user_id, company, description, application_date, end_date, expected_delay_days):
    """Crée une nouvelle candidature"""
    session = get_db_session()
    try:
        result = session.execute(text("""
            INSERT INTO applications 
            (user_id, company, description, application_date, end_date, expected_delay_days)
            VALUES (:user_id, :company, :description, :application_date, :end_date, :expected_delay_days)
            RETURNING app_id
        """), {
            "user_id": user_id,
            "company": company,
            "description": description,
            "application_date": application_date,
            "end_date": end_date,
            "expected_delay_days": expected_delay_days
        })
        app_id = result.fetchone()[0]
        session.commit()
        return app_id
    except Exception as e:
        session.rollback()
        raise e
    finally:
        session.close()

def get_user_applications(user_id):
    """Récupère toutes les candidatures d'un user"""
    session = get_db_session()
    try:
        result = session.execute(text("""
            SELECT * FROM applications 
            WHERE user_id = :user_id 
            ORDER BY application_date DESC
        """), {"user_id": user_id})
        applications = [dict(row._mapping) for row in result.fetchall()]
        return applications
    finally:
        session.close()

def update_application(app_id, company, description, application_date, end_date, expected_delay_days, status):
    """Met à jour une candidature"""
    session = get_db_session()
    try:
        session.execute(text("""
            UPDATE applications 
            SET company = :company, 
                description = :description, 
                application_date = :application_date, 
                end_date = :end_date, 
                expected_delay_days = :expected_delay_days, 
                status = :status, 
                updated_at = NOW()
            WHERE app_id = :app_id
        """), {
            "company": company,
            "description": description,
            "application_date": application_date,
            "end_date": end_date,
            "expected_delay_days": expected_delay_days,
            "status": status,
            "app_id": app_id
        })
        session.commit()
    except Exception as e:
        session.rollback()
        raise e
    finally:
        session.close()

def delete_application(app_id):
    """Supprime une candidature"""
    session = get_db_session()
    try:
        session.execute(
            text("DELETE FROM applications WHERE app_id = :app_id"),
            {"app_id": app_id}
        )
        session.commit()
    except Exception as e:
        session.rollback()
        raise e
    finally:
        session.close()

# ============ NOTES QUERIES ============

def create_note(app_id, note_text):
    """Crée une nouvelle note pour une candidature"""
    session = get_db_session()
    try:
        result = session.execute(text("""
            INSERT INTO notes (app_id, note_text)
            VALUES (:app_id, :note_text)
            RETURNING note_id
        """), {
            "app_id": app_id,
            "note_text": note_text
        })
        note_id = result.fetchone()[0]
        session.commit()
        return note_id
    except Exception as e:
        session.rollback()
        raise e
    finally:
        session.close()

def get_notes_by_application(app_id):
    """Récupère toutes les notes d'une candidature"""
    session = get_db_session()
    try:
        result = session.execute(text("""
            SELECT * FROM notes 
            WHERE app_id = :app_id 
            ORDER BY created_at DESC
        """), {"app_id": app_id})
        notes = [dict(row._mapping) for row in result.fetchall()]
        return notes
    finally:
        session.close()

def update_note(note_id, note_text):
    """Met à jour une note"""
    session = get_db_session()
    try:
        session.execute(text("""
            UPDATE notes 
            SET note_text = :note_text, updated_at = NOW()
            WHERE note_id = :note_id
        """), {
            "note_text": note_text,
            "note_id": note_id
        })
        session.commit()
    except Exception as e:
        session.rollback()
        raise e
    finally:
        session.close()

def delete_note(note_id):
    """Supprime une note"""
    session = get_db_session()
    try:
        session.execute(
            text("DELETE FROM notes WHERE note_id = :note_id"),
            {"note_id": note_id}
        )
        session.commit()
    except Exception as e:
        session.rollback()
        raise e
    finally:
        session.close()