backend/base_datos.py

from conexion_bd import ConexionBD

def iniciar_historial_usuario() -> None:
    with ConexionBD.instancia().obtener_conexion() as conn:
        conn.execute("PRAGMA foreign_keys = ON")

        conn.execute(
            """
            CREATE TABLE IF NOT EXISTS Usuarios (
                id            TEXT PRIMARY KEY,
                username      TEXT UNIQUE NOT NULL,
                password_hash TEXT NOT NULL,
                session_token TEXT,
                created_at    TEXT NOT NULL
            )
            """
        )

        conn.execute(
            """
            CREATE TABLE IF NOT EXISTS Peliculas (
                id     TEXT PRIMARY KEY,
                titulo TEXT NOT NULL,
                genero TEXT
            )
            """
        )

        conn.execute(
            """
            CREATE TABLE IF NOT EXISTS Emociones (
                id              INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id         TEXT NOT NULL,
                texto_analizado TEXT,
                emocion         TEXT NOT NULL,
                valencia        TEXT NOT NULL,
                analizado_en    TEXT NOT NULL,
                FOREIGN KEY (user_id) REFERENCES Usuarios(id) ON DELETE CASCADE
            )
            """
        )
        conn.execute(
            """
            CREATE INDEX IF NOT EXISTS idx_emociones_user_tiempo
            ON Emociones (user_id, analizado_en DESC)
            """
        )

        conn.execute(
            """
            CREATE TABLE IF NOT EXISTS Historial_Peliculas (
                id           INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id      TEXT NOT NULL,
                pelicula_id  TEXT NOT NULL,
                emocion_id   INTEGER,
                valoracion   REAL,
                texto_sesion TEXT,
                visto_en     TEXT NOT NULL,
                FOREIGN KEY (user_id)     REFERENCES Usuarios(id)   ON DELETE CASCADE,
                FOREIGN KEY (pelicula_id) REFERENCES Peliculas(id)  ON DELETE RESTRICT,
                FOREIGN KEY (emocion_id)  REFERENCES Emociones(id)  ON DELETE SET NULL
            )
            """
        )
        conn.execute(
            """
            CREATE INDEX IF NOT EXISTS idx_historial_user_tiempo
            ON Historial_Peliculas (user_id, visto_en DESC)
            """
        )

        conn.execute(
            """
            CREATE TABLE IF NOT EXISTS Ciclo_Recomendacion (
                id              INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id         TEXT NOT NULL,
                emocion_pre_id  INTEGER NOT NULL,
                estrategia      INTEGER NOT NULL,
                creado_en       TEXT NOT NULL,
                pelicula_id     TEXT,
                emocion_post_id INTEGER,
                FOREIGN KEY (user_id)         REFERENCES Usuarios(id)   ON DELETE CASCADE,
                FOREIGN KEY (emocion_pre_id)  REFERENCES Emociones(id)  ON DELETE RESTRICT,
                FOREIGN KEY (emocion_post_id) REFERENCES Emociones(id)  ON DELETE SET NULL,
                FOREIGN KEY (pelicula_id)     REFERENCES Peliculas(id)  ON DELETE SET NULL
            )
            """
        )
        conn.execute(
            """
            CREATE INDEX IF NOT EXISTS idx_ciclo_user_tiempo
            ON Ciclo_Recomendacion (user_id, creado_en DESC)
            """
        )

        conn.commit()