database.py

import sqlite3
from datetime import datetime

def get_conn():
    return sqlite3.connect("attendance.db")

def init_db():
    conn = get_conn()
    cursor = conn.cursor()
    cursor.execute("""CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        name TEXT,
        email TEXT UNIQUE,
        password TEXT,
        role TEXT,
        class_id TEXT,
        device_id TEXT
    )""")
    cursor.execute("""CREATE TABLE IF NOT EXISTS sessions (
        id INTEGER PRIMARY KEY,
        class_id TEXT,
        date TEXT,
        is_open INTEGER
    )""")
    cursor.execute("""CREATE TABLE IF NOT EXISTS attendance_logs (
        id INTEGER PRIMARY KEY,
        session_id INTEGER,
        student_email TEXT,
        timestamp TEXT
    )""")
    conn.commit()
    conn.close()

def add_student(name, email, password, class_id):
    conn = get_conn()
    cursor = conn.cursor()
    try:
        cursor.execute("INSERT INTO users (name, email, password, role, class_id) VALUES (?, ?, ?, 'student', ?)", 
                       (name, email, password, class_id))
        conn.commit()
    except sqlite3.IntegrityError:
        return "Email already exists."
    finally:
        conn.close()
    return "Student added successfully."

def get_user_role(email):
    conn = get_conn()
    cursor = conn.cursor()
    cursor.execute("SELECT role FROM users WHERE email=?", (email,))
    row = cursor.fetchone()
    conn.close()
    return row[0] if row else None

def get_sessions(class_id):
    conn = get_conn()
    cursor = conn.cursor()
    cursor.execute("SELECT id, date, is_open FROM sessions WHERE class_id=? ORDER BY date DESC", (class_id,))
    rows = cursor.fetchall()
    conn.close()
    return rows

def toggle_session(class_id, is_open):
    date = datetime.now().strftime("%Y-%m-%d %H:%M")
    conn = get_conn()
    cursor = conn.cursor()
    cursor.execute("INSERT INTO sessions (class_id, date, is_open) VALUES (?, ?, ?)", 
                   (class_id, date, int(is_open)))
    conn.commit()
    conn.close()
    return f"Session {'opened' if is_open else 'closed'} for {class_id} on {date}"

def get_open_session(class_id):
    conn = get_conn()
    cursor = conn.cursor()
    cursor.execute("SELECT id FROM sessions WHERE class_id=? AND is_open=1 ORDER BY date DESC LIMIT 1", (class_id,))
    row = cursor.fetchone()
    conn.close()
    return row[0] if row else None

def has_marked_attendance(student_email, session_id):
    conn = get_conn()
    cursor = conn.cursor()
    cursor.execute("SELECT COUNT(*) FROM attendance_logs WHERE student_email=? AND session_id=?", 
                   (student_email, session_id))
    count = cursor.fetchone()[0]
    conn.close()
    return count > 0

def mark_attendance(student_email, session_id):
    timestamp = datetime.now().strftime("%Y-%m-%d %H:%M")
    conn = get_conn()
    cursor = conn.cursor()
    cursor.execute("INSERT INTO attendance_logs (student_email, session_id, timestamp) VALUES (?, ?, ?)", 
                   (student_email, session_id, timestamp))
    conn.commit()
    conn.close()
    return "Attendance marked successfully."

def get_attendance_summary(student_email):
    conn = get_conn()
    cursor = conn.cursor()
    cursor.execute("""
        SELECT s.date, s.class_id
        FROM attendance_logs a
        JOIN sessions s ON a.session_id = s.id
        WHERE a.student_email=?
        ORDER BY s.date DESC
        LIMIT 10
    """, (student_email,))
    records = cursor.fetchall()
    conn.close()
    return "\n".join([f"{row[0]} - {row[1]}" for row in records]) if records else "No attendance records yet."