Spaces:
Sleeping
Sleeping
| 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() | |