# db_manager.py
import sqlite3
from config import STUDENT_DB_PATH
import streamlit as st
import os
import time

MAX_RETRIES = 3
RETRY_DELAY = 2 # seconds

def get_db_connection():
    # Ensure the directory for the SQLite DB exists
    db_dir = os.path.dirname(STUDENT_DB_PATH)
    if db_dir and not os.path.exists(db_dir):
        try:
            os.makedirs(db_dir, exist_ok=True)
            print(f"Created directory for SQLite DB: {db_dir}")
        except Exception as e:
            st.error(f"无法创建SQLite数据库目录 {db_dir}: {e}")
            print(f"Could not create SQLite DB directory {db_dir}: {e}")
            return None # Cannot proceed if directory creation fails

    conn = None
    for attempt in range(MAX_RETRIES):
        try:
            conn = sqlite3.connect(STUDENT_DB_PATH, timeout=10) # Added timeout
            return conn
        except sqlite3.OperationalError as e:
            if "database is locked" in str(e):
                print(f"SQLite DB is locked (Attempt {attempt + 1}/{MAX_RETRIES}). Retrying in {RETRY_DELAY}s...")
                if attempt < MAX_RETRIES - 1:
                    time.sleep(RETRY_DELAY)
                else:
                    st.error("SQLite数据库持续锁定，请稍后再试。")
                    print("SQLite DB remains locked after multiple retries.")
                    return None
            else:
                st.error(f"连接SQLite数据库时出错: {e}")
                print(f"Error connecting to SQLite DB: {e}")
                return None
        except Exception as e: # Catch other potential errors
            st.error(f"连接SQLite数据库时发生未知错误: {e}")
            print(f"Unknown error connecting to SQLite DB: {e}")
            return None
    return None


def init_student_db():
    conn = get_db_connection()
    if conn is None:
        return
    try:
        cursor = conn.cursor()
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS students (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT UNIQUE NOT NULL,
            characteristics_summary TEXT,
            last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
        ''')
        conn.commit()
    except Exception as e:
        st.error(f"初始化学生数据库表时出错: {e}")
        print(f"Error initializing student DB table: {e}")
    finally:
        if conn:
            conn.close()

def add_or_update_student(name: str, characteristics_summary: str = None):
    conn = get_db_connection()
    if conn is None:
        return False
    try:
        cursor = conn.cursor()
        # Upsert logic: Insert if name doesn't exist, or update if it does.
        # Using INSERT OR IGNORE then UPDATE is a common pattern.
        cursor.execute("INSERT OR IGNORE INTO students (name) VALUES (?)", (name,))
        if characteristics_summary is not None: # Allow updating only name or also characteristics
            cursor.execute("""
            UPDATE students
            SET characteristics_summary = ?, last_updated = CURRENT_TIMESTAMP
            WHERE name = ?
            """, (characteristics_summary, name))
        else: # Only ensure the student exists, update last_updated if already present
             cursor.execute("""
            UPDATE students
            SET last_updated = CURRENT_TIMESTAMP
            WHERE name = ? AND EXISTS (SELECT 1 FROM students WHERE name = ?)
            """, (name,name))

        conn.commit()
        return True
    except Exception as e:
        st.error(f"添加或更新学生 '{name}' 时出错: {e}")
        print(f"Error adding/updating student '{name}': {e}")
        return False
    finally:
        if conn:
            conn.close()

def get_student_characteristics(name: str):
    conn = get_db_connection()
    if conn is None:
        return None
    try:
        cursor = conn.cursor()
        cursor.execute("SELECT characteristics_summary FROM students WHERE name = ?", (name,))
        result = cursor.fetchone()
        return result[0] if result else None
    except Exception as e:
        st.error(f"获取学生 '{name}' 特点时出错: {e}")
        print(f"Error getting characteristics for student '{name}': {e}")
        return None
    finally:
        if conn:
            conn.close()

def get_all_student_names():
    conn = get_db_connection()
    if conn is None:
        return []
    try:
        cursor = conn.cursor()
        cursor.execute("SELECT name FROM students ORDER BY name ASC")
        results = [row[0] for row in cursor.fetchall()]
        return results
    except Exception as e:
        st.error(f"获取所有学生姓名时出错: {e}")
        print(f"Error getting all student names: {e}")
        return []
    finally:
        if conn:
            conn.close()