crud.py

from database import get_connection

def create_user(name, email, password, role):
    conn = get_connection()
    cur = conn.cursor()
    cur.execute("INSERT INTO users (name, email, password, role) VALUES (?, ?, ?, ?)", (name, email, password, role))
    conn.commit()
    conn.close()

def get_user_by_email(email):
    conn = get_connection()
    cur = conn.cursor()
    cur.execute("SELECT * FROM users WHERE email = ?", (email,))
    user = cur.fetchone()
    conn.close()
    return user

def create_session(session):
    conn = get_connection()
    cur = conn.cursor()
    cur.execute("""
        INSERT INTO sessions (
            user_id, date, activities, progress, challenges, observations,
            performance_score, fails_score, task_completion, behavior_rating
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    """, (
        session.user_id, session.date, session.activities, session.progress,
        session.challenges, session.observations, session.performance_score,
        session.fails_score, session.task_completion, session.behavior_rating
    ))
    session_id = cur.lastrowid
    conn.commit()
    conn.close()
    return session_id


def create_aba_entry(data):
    conn = get_connection()
    cur = conn.cursor()
    cur.execute("""
        INSERT INTO aba_data (
            session_id, behavior, frequency, reinforcer, technique
        ) VALUES (?, ?, ?, ?, ?)
    """, (
        data.session_id, data.behavior, data.frequency, data.reinforcer, data.technique
    ))
    conn.commit()
    conn.close()

def create_kid_entry(data):
    conn = get_connection()
    cur = conn.cursor()
    cur.execute("INSERT INTO kid_data (user_id, nickname, age, suport_level) VALUES (?, ?, ?, ?)",
                (data.user_id, data.nickname, data.age, data.suport_level))
    conn.commit()
    conn.close()

def get_kid_data_by_user(user_id):
    conn = get_connection()
    cur = conn.cursor()
    cur.execute("SELECT * FROM kid_data WHERE user_id = ?", (user_id,))
    rows = cur.fetchall()
    conn.close()
    return rows

def nickname_exists(nickname):
    conn = get_connection()
    cur = conn.cursor()
    cur.execute("SELECT 1 FROM kid_data WHERE LOWER(nickname) = LOWER(?)", (nickname,))
    exists = cur.fetchone() is not None
    conn.close()
    return exists

def get_user_sessions(user_id, start_date=None, end_date=None, limit=None):
    conn = get_connection()
    cur = conn.cursor()
    query = "SELECT * FROM sessions WHERE user_id = ?"
    params = [user_id]

    if start_date and end_date:
        query += " AND date BETWEEN ? AND ?"
        params.extend([start_date, end_date])

    query += " ORDER BY date DESC"

    if limit:
        query += " LIMIT ?"
        params.append(limit)

    cur.execute(query, tuple(params))
    results = cur.fetchall()
    conn.close()
    return results

def link_therapist_to_kid(data):
    conn = get_connection()
    cur = conn.cursor()
    cur.execute("INSERT INTO therapist_kid (user_id, nickname) VALUES (?, ?)", (data.user_id, data.nickname))
    conn.commit()
    conn.close()

def get_kids_by_therapist(user_id):
    conn = get_connection()
    cur = conn.cursor()
    cur.execute("SELECT nickname FROM therapist_kid WHERE user_id = ?", (user_id,))
    kids = [row["nickname"] for row in cur.fetchall()]
    conn.close()
    return kids

def get_therapists_by_kid(nickname):
    conn = get_connection()
    cur = conn.cursor()
    cur.execute("""
        SELECT u.id as user_id, u.name
        FROM therapist_kid tk
        JOIN users u ON tk.user_id = u.id
        WHERE LOWER(tk.nickname) = LOWER(?)
    """, (nickname,))
    therapists = [{"user_id": row["user_id"], "name": row["name"]} for row in cur.fetchall()]
    conn.close()
    return therapists

def get_kid_by_nickname_full(nickname: str):
    conn = get_connection()
    cur = conn.cursor()
    cur.execute("SELECT * FROM kid_data WHERE LOWER(nickname) = LOWER(?)", (nickname,))
    row = cur.fetchone()
    conn.close()
    if row:
        return {
            "user_id": row["user_id"],
            "nickname": row["nickname"],
            "age": row["age"],
            "suport_level": row["suport_level"]
        }
    return {}


def get_sessions_by_therapist_and_nickname(therapist_id: int, nickname: str):
    conn = get_connection()
    cur = conn.cursor()
    cur.execute("""
        SELECT s.*
        FROM sessions s
        JOIN kid_data k ON k.user_id = s.user_id
        WHERE LOWER(k.nickname) = LOWER(?)
          AND s.created_by = ?
        ORDER BY s.date DESC
    """, (nickname.lower(), therapist_id))
    rows = cur.fetchall()
    conn.close()
    return [dict(row) for row in rows]



def get_sessions_by_nickname(nickname: str):
    conn = get_connection()
    cur = conn.cursor()
    cur.execute("""
        SELECT s.*
        FROM sessions s
        JOIN kid_data k ON k.user_id = s.user_id
        WHERE LOWER(k.nickname) = LOWER(?)
        ORDER BY s.date DESC
    """, (nickname.lower(),))
    rows = cur.fetchall()
    conn.close()
    return [dict(row) for row in rows]

def get_user_by_id(user_id: int):
    conn = get_connection()
    cur = conn.cursor()
    cur.execute("SELECT * FROM users WHERE id = ?", (user_id,))
    row = cur.fetchone()
    conn.close()
    return row

def create_session(data):
    conn = get_connection()
    cur = conn.cursor()
    cur.execute("""
        INSERT INTO sessions (
            user_id, created_by, date, activities, progress, challenges,
            observations, performance_score, fails_score,
            task_completion, behavior_rating
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    """, (
        data.user_id, data.created_by, data.date, data.activities,
        data.progress, data.challenges, data.observations,
        data.performance_score, data.fails_score,
        data.task_completion, data.behavior_rating
    ))
    conn.commit()
    session_id = cur.lastrowid
    conn.close()
    return session_id