X3jddlZddlZddlmZedZdZdZddZdZdZ d Z d Z d Z d Z d Zedkr edSdS)N) load_dotenvz database.dbc^tjtd}tj|_|S)zJReturns a connection to the SQLite database with a thread timeout handler.g$@)timeout)sqlite3connectDB_NAMERow row_factory)conns #/home/pi/cupos-finder/db_manager.pyget_db_connectionr s% ?7D 1 1 1D{D Kc>t5}|}|d|d|d|dddn #1swxYwYt ddS)z7Initializes the database schema with relational tables.zPRAGMA foreign_keys = ON;z CREATE TABLE IF NOT EXISTS users ( chat_id INTEGER PRIMARY KEY, username TEXT, preferred_center TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); ae CREATE TABLE IF NOT EXISTS monitors ( id INTEGER PRIMARY KEY AUTOINCREMENT, chat_id INTEGER, ciclo TEXT NOT NULL, clave_materia TEXT NOT NULL, nrc TEXT NOT NULL, was_present INTEGER DEFAULT 0, -- 0 = Not found last loop, 1 = Found last loop created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (chat_id) REFERENCES users(chat_id) ON DELETE CASCADE, UNIQUE(chat_id, ciclo, nrc) -- Prevents duplicate monitors for the same user ); Nu&✨ Database initialized successfully.)r cursorexecutecommitprintr rs r init_dbrs    2333         => 233333sA(BB Bc t5}|}|r|d|||fn|d||f|ddddS#1swxYwYdS)z| Saves a new user or updates an existing one. Restricts changes to mutable profile options (preferred_center). a INSERT INTO users (chat_id, username, preferred_center) VALUES (?, ?, ?) ON CONFLICT(chat_id) DO UPDATE SET username = excluded.username, preferred_center = excluded.preferred_center; z INSERT INTO users (chat_id, username) VALUES (?, ?) ON CONFLICT(chat_id) DO UPDATE SET username = excluded.username; Nr rrr)chat_idusernamepreferred_centerr rs r save_or_update_userr3s     & NN 8%56  8 8 8 8 NN8$  & & & %sAA99A=A=ct5}|}|d|f|cdddS#1swxYwYdS)z&Retrieves a user's configuration data.zHSELECT chat_id, username, preferred_center FROM users WHERE chat_id = ?;N)r rrfetchonerr rs r get_user_profilerLs   !adkcmnnn  !!!!!!!!!!!!!!!!!!?AA"Ac tjdd} t5}|}|d||||f| ddddS#1swxYwYdS#tj $rYdSwxYw)z-Adds a target class to monitor for a student.CICLO202620z| INSERT INTO monitors (chat_id, ciclo, clave_materia, nrc) VALUES (?, ?, ?, ?); NTF) osgetenvr rrupperstriprrIntegrityError)r clave_materianrcciclor rs r add_monitorr,Us Igx ( (E    D[[]]F NN5-"5"5"7"7"="="?"?M O O O KKMMM                    !uus5B9A:B, B9,B00B93B04B99C  C ct5}|}|d|f|ddddS#1swxYwYdS)z!Deletes a tracking record safely.z"DELETE FROM monitors WHERE id = ?;Nr) monitor_idr rs r remove_monitorr/ds   ;j]KKK sAAA #A ct5}|}|d|f|cdddS#1swxYwYdS)z9Returns all active classes a specific user is monitoring.z{ SELECT id, clave_materia, nrc, was_present FROM monitors WHERE chat_id = ?; Nr rrfetchallrs r get_user_monitorsr3ks   ! Z       !!!!!!!!!!!!!!!!!!r ct5}|}|d|cdddS#1swxYwYdS)z De-duplicates targets globally. Returns rows containing unique (preferred_center, ciclo, clave_materia) so we hit SIIAU exactly once per overall subject page. z SELECT DISTINCT u.preferred_center, m.ciclo, m.clave_materia FROM monitors m JOIN users u ON m.chat_id = u.chat_id WHERE u.preferred_center IS NOT NULL; Nr1rs r get_unique_targets_to_scraper5xs   !      !!!!!!!!!!!!!!!!!!s=AA Act5}|}|d|||f|cdddS#1swxYwYdS)zIFinds all users waiting for a specific NRC and their last tracking state.z SELECT chat_id, id, was_present FROM monitors WHERE ciclo = ? AND clave_materia = ? AND nrc = ?; Nr1)r+r)r*r rs r get_subscribers_for_targetr7s   ! ]C (  * * *   !!!!!!!!!!!!!!!!!!AAA!$A!ct5}|}|d||f|ddddS#1swxYwYdS)zIUpdates the individual state marker (0 or 1) for a specific tracking row.z1UPDATE monitors SET was_present = ? WHERE id = ?;Nr)r. was_presentr rs r update_monitor_stater;s   J[ZdLefff r8__main__)N)rr$dotenvrrr rrrr,r/r3r5r7r;__name__rr r@s   !4!4!4J2!!!    ! ! !!!! ! ! ! z GIIIIIr