import sqlite3
from pathlib import Path

DB_PATH = Path("db/aba_data.db")
DB_PATH.parent.mkdir(exist_ok=True)

def init_db():
    with sqlite3.connect(DB_PATH) as conn:
        cursor = conn.cursor()

        cursor.execute("""
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT UNIQUE NOT NULL,
            password TEXT NOT NULL,
            role TEXT NOT NULL
        )
        """)

        cursor.execute("""
        CREATE TABLE IF NOT EXISTS sessions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            date TEXT NOT NULL,
            activities TEXT,
            progress TEXT,
            challenges TEXT,
            observations TEXT,
            performance_score INTEGER,
            fails_score INTEGER,
            task_completion INTEGER,
            behavior_rating INTEGER,
            created_by INTEGER,
            FOREIGN KEY(user_id) REFERENCES users(id)
        )
        """)

        cursor.execute("""
        CREATE TABLE IF NOT EXISTS aba_data (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            session_id INTEGER NOT NULL,
            behavior TEXT,
            frequency INTEGER,
            reinforcer TEXT,
            technique TEXT,
            FOREIGN KEY(session_id) REFERENCES sessions(id)
        )
        """)

        cursor.execute("""
        CREATE TABLE IF NOT EXISTS kid_data (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            nickname TEXT,
            age INTEGER,
            suport_level INTEGER,
            FOREIGN KEY(user_id) REFERENCES users(id)
        )
        """)

        conn.commit()


        cursor.execute("""
        CREATE TABLE IF NOT EXISTS therapist_kid (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,  -- id do terapeuta
            nickname TEXT NOT NULL,
            FOREIGN KEY(user_id) REFERENCES users(id)
        )
        """)

        conn.commit()
       
        

def get_connection():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row  # Permite acessar colunas por nome
    return conn