import streamlit as st
import pandas as pd
import sqlite3
from datetime import datetime

# Database connection and creation
def create_connection():
    conn = sqlite3.connect("attendance.db")
    return conn

def create_tables():
    conn = create_connection()
    cursor = conn.cursor()

    # Create tables if not exist
    cursor.execute('''CREATE TABLE IF NOT EXISTS students (
                        student_id INTEGER PRIMARY KEY,
                        name TEXT,
                        semester TEXT,
                        attendance TEXT)''')
    
    cursor.execute('''CREATE TABLE IF NOT EXISTS teachers (
                        teacher_id INTEGER PRIMARY KEY,
                        name TEXT,
                        attendance TEXT)''')
    
    cursor.execute('''CREATE TABLE IF NOT EXISTS events (
                        event_id INTEGER PRIMARY KEY,
                        date TEXT,
                        event TEXT)''')

    cursor.execute('''CREATE TABLE IF NOT EXISTS users (
                        username TEXT PRIMARY KEY,
                        password TEXT,
                        role TEXT)''')
    
    conn.commit()
    conn.close()

# Initialize database
create_tables()

# Function to check login credentials
def check_login(username, password):
    conn = create_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users WHERE username = ? AND password = ?", (username, password))
    user = cursor.fetchone()
    conn.close()
    return user

# Admin login
def admin_login():
    st.sidebar.header("Admin Login")
    admin_username = st.text_input('Username:')
    admin_password = st.text_input('Password:', type='password')

    if st.button('Login'):
        # Check credentials for 'uni' as username and 'admin123' as password
        user = check_login(admin_username, admin_password)
        if user and user[2] == 'admin':  # Role should be 'admin'
            st.sidebar.success("Logged in as Admin")
            return True
        elif user and user[2] == 'teacher':  # Role should be 'teacher'
            st.sidebar.success("Logged in as Teacher")
            return False
        else:
            st.sidebar.error('Invalid credentials')
            return False
    return False

# Function to add students, teachers, and events
def add_student():
    st.header("Add Student")
    name = st.text_input("Student Name:")
    semester = st.selectbox("Select Semester:", ["Semester 1", "Semester 2", "Semester 3"])
    
    if st.button("Add Student"):
        conn = create_connection()
        cursor = conn.cursor()
        cursor.execute("INSERT INTO students (name, semester, attendance) VALUES (?, ?, ?)", (name, semester, 'Absent'))
        conn.commit()
        conn.close()
        st.success(f"Student {name} added successfully!")

def add_teacher():
    st.header("Add Teacher")
    name = st.text_input("Teacher Name:")
    
    if st.button("Add Teacher"):
        conn = create_connection()
        cursor = conn.cursor()
        cursor.execute("INSERT INTO teachers (name, attendance) VALUES (?, ?)", (name, 'Absent'))
        conn.commit()
        conn.close()
        st.success(f"Teacher {name} added successfully!")

def add_event():
    st.header("Add Event")
    event_name = st.text_input("Event Name:")
    event_date = st.date_input("Event Date:", datetime.today())
    
    if st.button("Add Event"):
        conn = create_connection()
        cursor = conn.cursor()
        cursor.execute("INSERT INTO events (date, event) VALUES (?, ?)", (event_date, event_name))
        conn.commit()
        conn.close()
        st.success(f"Event {event_name} added for {event_date}")

def mark_attendance():
    st.header("Mark Attendance")
    name = st.selectbox("Select Student/Teacher", ["Select", "Student", "Teacher"])
    
    if name == "Student":
        student_name = st.selectbox("Select Student:", [row[1] for row in get_all_students()])
        attendance_status = st.radio("Attendance Status:", ["Present", "Absent"])
        if st.button("Mark Attendance for Student"):
            update_student_attendance(student_name, attendance_status)
            st.success(f"Attendance marked for {student_name} as {attendance_status}")
    
    elif name == "Teacher":
        teacher_name = st.selectbox("Select Teacher:", [row[1] for row in get_all_teachers()])
        attendance_status = st.radio("Attendance Status:", ["Present", "Absent"])
        if st.button("Mark Attendance for Teacher"):
            update_teacher_attendance(teacher_name, attendance_status)
            st.success(f"Attendance marked for {teacher_name} as {attendance_status}")

def get_all_students():
    conn = create_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM students")
    students = cursor.fetchall()
    conn.close()
    return students

def get_all_teachers():
    conn = create_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM teachers")
    teachers = cursor.fetchall()
    conn.close()
    return teachers

def update_student_attendance(student_name, status):
    conn = create_connection()
    cursor = conn.cursor()
    cursor.execute("UPDATE students SET attendance = ? WHERE name = ?", (status, student_name))
    conn.commit()
    conn.close()

def update_teacher_attendance(teacher_name, status):
    conn = create_connection()
    cursor = conn.cursor()
    cursor.execute("UPDATE teachers SET attendance = ? WHERE name = ?", (status, teacher_name))
    conn.commit()
    conn.close()

# Attendance Analytics without matplotlib
def attendance_analytics():
    st.header("Attendance Analytics")
    student_attendance = pd.DataFrame(get_all_students(), columns=["ID", "Name", "Semester", "Attendance"])
    teacher_attendance = pd.DataFrame(get_all_teachers(), columns=["ID", "Name", "Attendance"])

    student_attendance['Attendance'] = student_attendance['Attendance'].apply(lambda x: 1 if x == "Present" else 0)
    teacher_attendance['Attendance'] = teacher_attendance['Attendance'].apply(lambda x: 1 if x == "Present" else 0)

    student_percentage = student_attendance['Attendance'].mean() * 100
    teacher_percentage = teacher_attendance['Attendance'].mean() * 100

    st.write(f"Student Attendance: {student_percentage:.2f}%")
    st.write(f"Teacher Attendance: {teacher_percentage:.2f}%")

    st.subheader("Student and Teacher Attendance Percentage")
    st.write(f"Student Attendance Percentage: {student_percentage:.2f}%")
    st.write(f"Teacher Attendance Percentage: {teacher_percentage:.2f}%")

# Main function
def main():
    st.title("Student & Teacher Attendance Management System")

    if admin_login():
        menu = ["Add Student", "Add Teacher", "Add Event", "Mark Attendance", "View Attendance", "Attendance Analytics"]
        choice = st.sidebar.selectbox("Select an option", menu)

        if choice == "Add Student":
            add_student()
        elif choice == "Add Teacher":
            add_teacher()
        elif choice == "Add Event":
            add_event()
        elif choice == "Mark Attendance":
            mark_attendance()
        elif choice == "View Attendance":
            view_attendance()
        elif choice == "Attendance Analytics":
            attendance_analytics()

if __name__ == "__main__":
    main()