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,
                avatar        TEXT,
                created_at    TEXT NOT NULL
            )
            """
        )

        # Migración: añade la columna avatar a bases de datos creadas antes de
        # esta funcionalidad (ALTER TABLE no soporta IF NOT EXISTS en SQLite).
        columnas_usuarios = {row["name"] for row in conn.execute("PRAGMA table_info(Usuarios)")}
        if "avatar" not in columnas_usuarios:
            conn.execute("ALTER TABLE Usuarios ADD COLUMN avatar TEXT")

        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.commit()