import sqlite3
import os
from dotenv import load_dotenv

load_dotenv()
DB_NAME = "database.db"

def get_db_connection():
    """Returns a connection to the SQLite database with a thread timeout handler."""
    conn = sqlite3.connect(DB_NAME, timeout=10.0)
    conn.row_factory = sqlite3.Row  # Access columns by name like a dictionary
    return conn

def init_db():
    """Initializes the database schema with relational tables."""
    with get_db_connection() as conn:
        cursor = conn.cursor()
        
        # Explicitly turn on Foreign Key constraints in SQLite
        cursor.execute("PRAGMA foreign_keys = ON;")
        
        # 1. Users Table (Stores preferences)
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS users (
                chat_id INTEGER PRIMARY KEY,
                username TEXT,
                preferred_center TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            );
        """)
        
        # 2. Monitors Table (Tracks targets per user)
        cursor.execute("""
            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
            );
        """)
        conn.commit()
    print("✨ Database initialized successfully.")

# --- User Management Queries ---

def save_or_update_user(chat_id, username, preferred_center=None):
    """
    Saves a new user or updates an existing one. 
    Restricts changes to mutable profile options (preferred_center).
    """
    with get_db_connection() as conn:
        cursor = conn.cursor()
        if preferred_center:
            # If we already have a profile, update the center preference safely
            cursor.execute("""
                INSERT INTO users (chat_id, username, preferred_center)
                VALUES (?, ?, ?)
                ON CONFLICT(chat_id) DO UPDATE SET 
                    username = excluded.username,
                    preferred_center = excluded.preferred_center;
            """, (chat_id, username, preferred_center))
        else:
            # Basic entry creation (e.g., during /start command before selection)
            cursor.execute("""
                INSERT INTO users (chat_id, username)
                VALUES (?, ?)
                ON CONFLICT(chat_id) DO UPDATE SET username = excluded.username;
            """, (chat_id, username))
        conn.commit()

def get_user_profile(chat_id):
    """Retrieves a user's configuration data."""
    with get_db_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("SELECT chat_id, username, preferred_center FROM users WHERE chat_id = ?;", (chat_id,))
        return cursor.fetchone()

# --- Tracking Queries ---

def add_monitor(chat_id, clave_materia, nrc):
    """Adds a target class to monitor for a student."""
    ciclo = os.getenv("CICLO", "202620")
    try:
        with get_db_connection() as conn:
            cursor = conn.cursor()
            cursor.execute("""
                INSERT INTO monitors (chat_id, ciclo, clave_materia, nrc)
                VALUES (?, ?, ?, ?);
            """, (chat_id, ciclo, clave_materia.upper().strip(), nrc.strip()))
            conn.commit()
            return True
    except sqlite3.IntegrityError:
        return False  # Already tracking this specific class

def remove_monitor(monitor_id):
    """Deletes a tracking record safely."""
    with get_db_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("DELETE FROM monitors WHERE id = ?;", (monitor_id,))
        conn.commit()

def get_user_monitors(chat_id):
    """Returns all active classes a specific user is monitoring."""
    with get_db_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT id, clave_materia, nrc, was_present 
            FROM monitors 
            WHERE chat_id = ?;
        """, (chat_id,))
        return cursor.fetchall()

# --- Central Loop Batched Queries ---

def get_unique_targets_to_scrape():
    """
    De-duplicates targets globally.
    Returns rows containing unique (preferred_center, ciclo, clave_materia)
    so we hit SIIAU exactly once per overall subject page.
    """
    with get_db_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            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;
        """)
        return cursor.fetchall()

def get_subscribers_for_target(ciclo, clave_materia, nrc):
    """Finds all users waiting for a specific NRC and their last tracking state."""
    with get_db_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("""
            SELECT chat_id, id, was_present 
            FROM monitors 
            WHERE ciclo = ? AND clave_materia = ? AND nrc = ?;
        """, (ciclo, clave_materia, nrc))
        return cursor.fetchall()

def update_monitor_state(monitor_id, was_present):
    """Updates the individual state marker (0 or 1) for a specific tracking row."""
    with get_db_connection() as conn:
        cursor = conn.cursor()
        cursor.execute("UPDATE monitors SET was_present = ? WHERE id = ?;", (was_present, monitor_id))
        conn.commit()

if __name__ == "__main__":
    init_db()