app.py

from flask import Flask, render_template, request, redirect, url_for, session, flash, jsonify
from flask_login import LoginManager, UserMixin, login_user, login_required, current_user
from flask_sqlalchemy import SQLAlchemy
import mysql.connector
from werkzeug.utils import secure_filename
from datetime import datetime, timedelta
import os

app = Flask(__name__)
app.secret_key = 'your_secret_key'
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///Staff_management.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)

login_manager = LoginManager()
login_manager.init_app(app)
login_manager.login_view = 'login'  # Redirect here if not logged in

@login_manager.user_loader
def load_user(user_id):
    return User.query.get(int(user_id)) 

class Faculty:
    def __init__(self, id, name, phone, email, branch, divisions, subjects, experience, office_hours, photo_url, role):
        self.id = id
        self.name = name
        self.phone = phone
        self.email = email
        self.branch = branch
        self.divisions = divisions
        self.subjects = subjects
        self.experience = experience
        self.office_hours = office_hours
        self.photo_url = photo_url
        self.role = role

# Database models
class User(UserMixin,db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    password = db.Column(db.String(120), nullable=False)
    role = db.Column(db.String(20), nullable=False)

class LeaveApplication:
    def __init__(self, id, user_id, username, leave_type, start_date, end_date, single_date, lecture_slot, reason, subject, branch, division, status, created_at):
        self.id = id
        self.user_id = user_id
        self.username = username
        self.leave_type = leave_type
        self.start_date = start_date
        self.end_date = end_date
        self.single_date = single_date
        self.lecture_slot = lecture_slot
        self.reason = reason
        self.subject = subject
        self.branch = branch
        self.division = division
        self.status = status
        self.created_at = created_at

class ProxyApplication(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    staff_name = db.Column(db.String(80), nullable=False)
    division = db.Column(db.String(80), nullable=False)
    proxy_for = db.Column(db.String(80), nullable=False)
    dates = db.Column(db.String(50), nullable=False)
    reason = db.Column(db.String(200), nullable=False)
    status = db.Column(db.String(20), nullable=False)

# class Faculty(db.Model):
#     id = db.Column(db.Integer, primary_key=True)
#     name = db.Column(db.String(80), nullable=False)
#     division = db.Column(db.String(80), nullable=False)
#     contact = db.Column(db.String(20), nullable=False)
#     email = db.Column(db.String(120), nullable=False)
#     experience = db.Column(db.Integer, nullable=False)
#     subjects = db.Column(db.String(200), nullable=False)
#     office_hours = db.Column(db.String(100), nullable=False)

class Subject(db.Model):  # Define the Subject model
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), unique=True, nullable=False)
    
# Create the database
with app.app_context():
    db.create_all()
    

connection = mysql.connector.connect(
    host="localhost",      # Change if your MySQL server is on another host
    user="root",  # Replace with your MySQL username
    password="r1o2n3a4k5",  # Replace with your MySQL password
    database="proxy_management"  # Use the database created earlier
)
    
def fetch_faculty_data():
    # Connect to the MySQL database
    connection = mysql.connector.connect(
        host="localhost",      # Change if your MySQL server is on another host
        user="root",  # Replace with your MySQL username
        password="r1o2n3a4k5",  # Replace with your MySQL password
        database="proxy_management"  # Use the database created earlier
    )
    
    cursor = connection.cursor(dictionary=True)
    
    # Query to fetch all faculty data
    cursor.execute("SELECT * FROM faculty")
    
    faculty_list = cursor.fetchall()
    
    cursor.close()
    
    return faculty_list


@app.route('/')
def index():
    return redirect(url_for('login'))

def get_timetable_db_connection():
    return mysql.connector.connect(
        host='localhost',           # e.g., 'localhost'
        user='root',       # e.g., 'root'
        password='r1o2n3a4k5',   # Your MySQL password
        database='timetable'
    )


@app.route('/admin/timetable', methods=['GET', 'POST'])
def admin_timetable():
    division = None
    timetable_data = []

    if request.method == 'POST':
        division = request.form['division']

    # Fetch timetable data from MySQL
    conn = get_timetable_db_connection()
    cursor = conn.cursor()
    cursor.execute(f'SELECT * FROM `{division if division else "5B2"}`')
    timetable_data = cursor.fetchall()
    cursor.close()
    conn.close()

    # Connect to the proxy_management database
    new_conn = mysql.connector.connect(
        host='localhost',           # e.g., 'localhost'
        user='root',               # e.g., 'root'
        password='r1o2n3a4k5',     # Your MySQL password
        database='proxy_management'
    )
    cursor = new_conn.cursor()

    # Fetch approved proxies with date ahead of current date
    cursor.execute(
        'SELECT proxy_date, proxy_slot FROM proxy_requests WHERE status = %s AND proxy_date > CURDATE() AND proxy_division = %s',
        ('approved', division if division else "5B2")
    )

    # Initialize the proxy data structure
    proxy_data = {
        'proxy_dates': [],  # This will hold the list of unique formatted dates
        'time_slots': []    # This will hold a flat list of time slots
    }
    
    # Use a temporary dictionary to collect slots for each date
    temp_proxy_dict = {}

    for row in cursor.fetchall():
        proxy_date = row[0]  # Extracting the proxy_date
        proxy_slot = row[1]  # Extracting the proxy_slot

        # Format the proxy_date to dd/mm/yyyy
        formatted_date = datetime.strptime(str(proxy_date), '%Y-%m-%d').strftime('%d/%m/%Y')

        # Initialize the date key if not already in the dictionary
        if formatted_date not in temp_proxy_dict:
            temp_proxy_dict[formatted_date] = []

        # Append the proxy_slot to the list for the corresponding date
        temp_proxy_dict[formatted_date].append(proxy_slot)
    current_date = datetime.now().date()
    # Populate the proxy_data structure from the temporary dictionary
    for date, slots in temp_proxy_dict.items():
        day_difference = (datetime.strptime(date, "%d/%m/%Y").date() - current_date).days
        proxy_data['proxy_dates'].append(day_difference+1)
        
        for slot in slots:
            proxy_data['time_slots'].append(slot)


    # Remove duplicates from time_slots if necessary
    proxy_data['time_slots'] = set(list(dict.fromkeys(proxy_data['time_slots']))[0].split(", "))  # Removes duplicates while preserving order
    proxy_data['proxy_dates'] = set(proxy_data['proxy_dates'])
    print(proxy_data)  # For debugging purposes
    cursor.close()
    new_conn.close()

    return render_template('admin_timetable.html', division=division if division else "5B2", timetable_data=timetable_data, proxy_data=proxy_data)


@app.route('/admin/proxy')
def admin_proxy():
    cursor = connection.cursor(dictionary=True)
    query = "select * from proxy_requests"
    cursor.execute(query)
    proxy_requests = cursor.fetchall()
    approved_count = 0
    denied_count = 0

    # Iterate through the fetched proxy requests to count approved and denied
    for proxy in proxy_requests:
        if proxy['status'] == 'approved':
            approved_count += 1
        elif proxy['status'] == 'denied':
            denied_count += 1

    return render_template('admin_proxy.html', proxies=proxy_requests, approved_count=approved_count, denied_count=denied_count)

@app.route('/admin/update-proxy-status/<int:proxy_id>/<string:status>', methods=['POST'])
def update_proxy_status(proxy_id, status):
    cursor = connection.cursor()
    # Update the status in the database
    cursor.execute("UPDATE proxy_requests SET status = %s WHERE id = %s", (status, proxy_id))
    connection.commit()
    cursor.close()
    return redirect(url_for('admin_proxy'))

@app.route('/api/getData', methods=['GET'])
def get_data():
    cursor = connection.cursor()
    cursor.execute("select COUNT(*) from proxy_requests where status='pending'")
    proxy_count = cursor.fetchone()
    cursor.execute("select COUNT(*) from leave_application where status='pending'")
    leave_count = cursor.fetchone()
    print(leave_count)
    data = {'proxy_count':proxy_count,'leave_count':leave_count}
    return jsonify(data)

@app.route('/view_timetable/<faculty_name>')
def view_fac_timetable(faculty_name):
    # Assuming faculty table names are their names stored in faculties table
    timetable = fetch_timetable((faculty_name,))
    # Fetch timetable from the corresponding table
    return render_template('view_timetable.html', timetable=timetable, faculty_name=faculty_name)

@app.route('/assign_role/<faculty_id>', methods=['POST'])
def assign_role(faculty_id, connection = connection):
    new_role = request.form.get('role')
    
    # Update the role in the faculties table
    cursor = connection.cursor()
    cursor.execute("UPDATE faculty SET role = %s WHERE id = %s", (new_role, faculty_id))
    connection.commit()
    cursor.close()
    
    return redirect(url_for('admin_dashboard'))
  
@app.route('/admin/leave-application')
def admin_leave_application():
    user_leaves = fetch_user_leaves()
    approved_count = sum(1 for leave in user_leaves if leave['status'] == 'approved')
    denied_count = sum(1 for leave in user_leaves if leave['status'] == 'denied')
    return render_template('admin_leave_application.html', leaves=user_leaves,  approved_count=approved_count, denied_count=denied_count)

@app.route('/admin/update-leave-status/<int:leave_id>/<string:status>', methods=['POST'])
def update_leave_status(leave_id, status):
    connection = mysql.connector.connect(
        host="localhost",
        user="root",
        password="r1o2n3a4k5",
        database="proxy_management" 
    )
    cursor = connection.cursor()
    
    # Update the status in the database
    cursor.execute("UPDATE leave_application SET status = %s WHERE id = %s", (status, leave_id))
    connection.commit()
    
    cursor.close()
    
    return redirect(url_for('admin_leave_application'))


def fetch_user_leaves():
    connection = mysql.connector.connect(
    host="localhost",      # Change if your MySQL server is on another host
    user="root",  # Replace with your MySQL username
    password="r1o2n3a4k5",  # Replace with your MySQL password
    database="proxy_management"  # Use the database created earlier
    )
    cursor = connection.cursor()
    cursor.execute("SELECT * FROM leave_application")
    rows = cursor.fetchall()
    
    # Manually map the result tuples to dictionaries
    user_leaves = []
    for row in rows:
        user_leaves.append({
            'id': row[0],
            'user_id': row[1],
            'username': row[2],
            'leave_type': row[3],
            'start_date': row[4],
            'end_date': row[5],
            'single_date': row[6],
            'lecture_slot': row[7],
            'reason': row[8],
            'subject': row[9],
            'branch': row[10],
            'division': row[11],
            'status': row[12],
            'applied_on': row[13]
        })
    cursor.close()
    return user_leaves


UPLOAD_FOLDER = 'static/profiles'
ALLOWED_EXTENSIONS = {'png', 'jpg', 'jpeg'}

app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER

def allowed_file(filename):
    return '.' in filename and filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS

def generate_unique_id(table_name = "faculty", connection = connection):
    cursor = connection.cursor()

    # Query to find the maximum ID in the table
    query = f"SELECT MAX(id) FROM {table_name}"
    cursor.execute(query)

    # Fetch the result
    result = cursor.fetchone()

    # If there are no rows in the table, return 1 as the first ID, otherwise increment the max id by 1
    next_id = 1 if result[0] is None else result[0] + 1

    # Close the cursor
    cursor.close()

    return next_id

@app.route('/admin/add_faculty', methods=['POST'])
@login_required
def add_faculty(connection=connection):
    id = generate_unique_id()
    name = request.form['name']
    phone = request.form['phone']
    email = request.form['email']
    branch = request.form['branch']
    divisions = request.form['divisions']
    subjects = request.form['subjects']
    experience = request.form['experience']
    office_hours = request.form['office_hours']
    role = request.form['role']

    # Handling file upload
    if 'photo' not in request.files:
        flash('No file part', 'danger')
        return redirect(url_for('admin_dashboard'))

    file = request.files['photo']
    if file.filename == '':
        flash('No selected file', 'danger')
        return redirect(url_for('admin_dashboard'))

    if file and allowed_file(file.filename):
        filename = secure_filename(file.filename)
        file.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))

        try:
            # Establish a database connection
            db = connection
            cursor = db.cursor()

            # Prepare SQL query to insert faculty data
            sql = """
                INSERT INTO faculty (id, name, phone, email, branch, divisions, subjects, experience, office_hours, photo_url, role)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """
            values = (
                id,
                name,
                phone,
                email,
                branch,
                divisions,  # Store as a comma-separated string
                subjects,   # Store as a comma-separated string
                experience,
                office_hours,
                filename,   # Photo filename/path
                role
            )

            # Execute the query
            cursor.execute(sql, values)

            # Commit the transaction
            db.commit()

            flash('New faculty added successfully!', 'success')
            return redirect(url_for('admin_dashboard'))

        except mysql.connector.Error as err:
            # Rollback in case of error
            db.rollback()
            flash(f"An error occurred: {err}", 'danger')
            return redirect(url_for('admin_dashboard'))

        finally:
            # Clean up and close the connection
            cursor.close()

    else:
        flash('File not allowed', 'danger')
        return redirect(url_for('admin_dashboard'))


@app.route('/admin/settings', methods=['GET'])
@login_required
def admin_settings():
    # Fetch the current admin from the database
    admin = User.query.get(current_user.id)  # Assuming you are using Flask-Login
    return render_template('admin_settings.html', admin=admin)

@app.route('/admin/settings/update', methods=['POST'])
@login_required
def update_settings():
    # Get the form data
    name = request.form['name']
    email = request.form['email']
    current_password = request.form['current_password']
    new_password = request.form['new_password']

    # Fetch the current admin from the database
    admin = User.query.get(current_user.id)  # Get the admin record

    # Check current password (you should hash the password in practice)
    if current_password != admin.password:
        flash('Current password is incorrect.', 'danger')
        return redirect(url_for('settings_page'))

    # Update admin details
    admin.username = name  # Assuming you want to change the username
    admin.email = email
    if new_password:
        admin.password = new_password  # Hash this password in practice

    db.session.commit()  # Save changes to the database

    flash('Settings updated successfully!', 'success')
    return redirect(url_for('settings_page'))


@app.route('/admin/profile', methods=['GET', 'POST'])
@login_required
def admin_profile():
    if request.method == 'POST':
        # Get new data from the form
        new_name = request.form.get('name')
        new_email = request.form.get('email')
        new_contact = request.form.get('contact')
        
        # Update the admin profile in the database
        admin = User.query.get(current_user.id)  # Use User model here
        admin.name = new_name
        admin.email = new_email
        admin.contact = new_contact
        db.session.commit()
        
        flash('Profile updated successfully!', 'success')
        return redirect(url_for('admin_profile'))
    
    # Get the admin data to display
    admin = User.query.get(current_user.id)  # Use User model here
    return render_template('admin_profile.html', admin=admin)

@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        user = User.query.filter_by(username=username, password=password).first()
        
        if user:
            login_user(user)  # Log the user in
            session['username'] = user.username  # Store username in session
            session['role'] = user.role  # Store role in session

            if user.role == 'admin':
                return redirect(url_for('admin_dashboard'))
            else:
                return redirect(url_for('user_dashboard'))
        else:
            return render_template('login.html', error="Invalid credentials")
    return render_template('login.html')

def import_faculty_data():
    faculty_dict = {}
    
    # Fetch data from the database
    faculty_data = fetch_faculty_data()
    
    # Loop over each row and create a Faculty object
    for row in faculty_data:
        # Create Faculty object
        faculty_obj = Faculty(
            id=row['id'],
            name=row['name'],
            phone=row['phone'],
            email=row['email'],
            branch=row['branch'],
            divisions=row['divisions'].split(", "),  # Convert comma-separated string to a list
            subjects=row['subjects'].split(", "),    # Convert comma-separated string to a list
            experience=row['experience'],
            office_hours=row['office_hours'],
            photo_url=row['photo_url'],
            role = row['role']
        )
        
        # Add the Faculty object to the dictionary with ID as the key
        faculty_dict[faculty_obj.id] = faculty_obj
    
    return faculty_dict

@app.route('/admin_dashboard', methods=['GET', 'POST'])
def admin_dashboard():
    if 'username' in session and session['role'] == 'admin':
        # Load default branch 'CSE-PIT' when the page first loads or if no branch is provided
        branch = request.form.get('branch') if request.method == 'POST' else "CSE-PIT"
        
        # Filter faculties by the branch
        faculties = {id: faculty for id, faculty in import_faculty_data().items() if faculty.branch == branch}
        
        return render_template('admin_dashboard.html', username=session['username'], faculties=faculties, default_branch=branch)
    return redirect(url_for('login'))


@app.route('/user_dashboard')
def user_dashboard(connection = connection):
    if 'username' in session and session['role'] in ['user', 'manager']:
        user_id = session['username']
        cursor = connection.cursor()
        query = "SELECT name FROM faculty WHERE id = %s"
        cursor.execute(query, (user_id,))  # Fetch username using user_id
        result = cursor.fetchone()
        return render_template('user_dashboard.html', username=result[0])
    return redirect(url_for('login'))


def fetch_timetable(username, connection = connection):
    username = '_'.join(username[0].lower().split())
    cursor = connection.cursor(dictionary=True)  # Fetch rows as dictionaries
    try:
        cursor.execute(f"SELECT * FROM `{username}`")  # Replace with your table name
    except:
        return None
    timetable_data = cursor.fetchall()    
    # Format the data in a way that we can easily display it in the HTML
    timetable = []
    for row in timetable_data:
        timetable.append({
            'id': row['id'],
            'time': row['time'],
            'Monday': row['Monday'],
            'Tuesday': row['Tuesday'],
            'Wednesday': row['Wednesday'],
            'Thursday': row['Thursday'],
            'Friday': row['Friday'],
            'Saturday': row['Saturday']
        })
    
    return timetable


@app.route('/user/timetable')
@login_required
def view_timetable():
    username = session.get('username')
    cursor = connection.cursor()
    cursor.execute("select name from faculty where id=%s",(username,))
    result = cursor.fetchone()
    timetable_data = fetch_timetable(result)
    return render_template('user_timetable.html', timetable=timetable_data, username = result[0])

@app.route('/user/user_proxy', methods=['GET', 'POST'])
def proxy_application(connection = connection):
    cursor = connection.cursor()
    query = "select name from faculty where id=%s"
    cursor.execute(query, (session['username'],))
    result = cursor.fetchone()
    # Render template and pass the current proxy requests
    query2 = "select * from proxy_requests where username = %s"
    cursor.execute(query2, (result[0],))
    result2 = cursor.fetchall() or None
    print(result2)
    
    return render_template('proxy_application.html', username = result[0], my_requests = result2)

@app.route('/submit_proxy_request', methods=['POST'])
def submit_proxy_request():
    # Get form data
    headuser_name = request.form['username']
    reason = request.form['reason']
    proxy_date = request.form['proxy-date']
    proxy_division = request.form['proxy-division']
    proxy_slots = request.form.getlist('proxy-slot')  # Get all checked slots
    proxy_slot_string = ', '.join(proxy_slots)
    current_datetime = datetime.now()
    curr_date = current_datetime.date()   # Only the date part
    curr_time = current_datetime.time()

    # Insert data into the proxy_requests table
    cursor = connection.cursor()
    query = """
        INSERT INTO proxy_requests (username, reason, proxy_date, proxy_division, proxy_slot, curr_date, curr_time, status)
        VALUES (%s, %s, %s, %s, %s, %s, %s, 'pending')
    """
    values = (headuser_name, reason, proxy_date, proxy_division, proxy_slot_string, curr_date, curr_time)
    cursor.execute(query, values)
    connection.commit()
    
    flash('Proxy request submitted successfully', 'success')
    return redirect(url_for('proxy_application'))

@app.route('/user/leave', methods=['GET', 'POST'])
@login_required
def user_leave_application():
    username = session.get('username')
    cursor = connection.cursor()
    query = "select * from leave_application where user_id = %s"
    cursor.execute(query,(username,))
    result = cursor.fetchall()
    query2 = "SELECT name FROM faculty WHERE id = %s"
    cursor.execute(query2, (username,))  # Fetch username using user_id
    result2 = cursor.fetchone()
    leaves = [
        LeaveApplication(
            id=row[0], user_id=row[1], username=row[2], leave_type=row[3],
            start_date=row[4], end_date=row[5], single_date=row[6],
            lecture_slot=row[7], reason=row[8], subject=row[9], branch=row[10],
            division=row[11], status=row[12], created_at=row[13]
        )
        for row in result
    ]
    if request.method == "POST":
        return redirect(url_for('submit_leave'))
    return render_template('user_leave_application.html',leaves = leaves, username = result2[0])

@app.route('/submit_leave', methods=['POST'])
@login_required
def submit_leave(connection = connection):
    username = session.get('username')
    leave_type = request.form.get('leave_type')  # 'day' or 'hourly'
    cursor = connection.cursor()
    query1 = "select name from faculty where id = %s"
    cursor.execute(query1,(username,))
    result = cursor.fetchone()
    name = result[0]
    # Common fields
    reason = request.form.get('reason')
    subject = request.form.get('subject')
    branch = request.form.get('branch')
    division = request.form.get('division')

    # Fields based on leave type
    if leave_type == 'daily':
        start_date = request.form.get('start_date') or None
        end_date = request.form.get('end_date') or None
        single_date = None
        lecture_slot = None
    elif leave_type == 'hourly':
        single_date = request.form.get('single_date') or None
        lecture_slot = ', '.join(request.form.getlist('lecture_slot')) or None
        start_date = None
        end_date = None

    # Insert into the database (handle None for fields that are not applicable)
    query = """
    INSERT INTO leave_application (user_id, username, leave_type, reason, subject, branch, division, start_date, end_date, single_date, lecture_slot)
    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
    """
    values = (username, name, leave_type, reason, subject, branch, division, start_date, end_date, single_date, lecture_slot)

    cursor.execute(query, values)
    connection.commit()

    return redirect(url_for('user_leave_application'))



@app.route('/my_schedules')
def my_schedules():
    username = session.get('username')
    cursor = connection.cursor()
    
    cursor.execute("SELECT name FROM faculty WHERE id = %s", (username,))
    result = cursor.fetchone()
    
    cursor.execute("SELECT proxy_slot, proxy_date FROM proxy_requests WHERE username = %s AND status='approved'", (result[0],))
    result2 = cursor.fetchall()
    
    # Prepare proxy slots as a dictionary for easy lookup
    proxy_lects = {}
    for slot, date in result2:
        day_name = date.strftime('%A')  # Get the day name
        if day_name not in proxy_lects:
            proxy_lects[day_name] = []
        proxy_lects[day_name].extend(slot.split(', '))  # Split and add the time slots

    # Fetch the timetable
    weekly_schedule = fetch_my_timetable(result)

    # Get today's date and the start of the week
    today = datetime.today()
    start_of_week = today - timedelta(days=today.weekday())  # Monday of the current week
    end_of_week = start_of_week + timedelta(days=6)  # Sunday of the current week

    # Iterate through the weekly schedule and replace the corresponding slots
    for time_slot, schedule in weekly_schedule.items():
        for i, day in enumerate(['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday']):
            if day in proxy_lects:
                # Get the date corresponding to the current day
                proxy_date = start_of_week + timedelta(days=i)

                # Check if the date is within the current week
                if start_of_week <= proxy_date <= end_of_week and time_slot in proxy_lects[day]:
                    schedule[i] = 'Proxy'  # Replace with 'Proxy'

    return render_template('my_schedules.html', weekly_schedule=weekly_schedule)



# Fetch timetable data from MySQL
def fetch_my_timetable(username):
    username = '_'.join(username[0].lower().split())
    cursor = connection.cursor(dictionary=True)  # Fetch rows as dictionaries
    cursor.execute(f"SELECT * FROM `{username}`")  # Replace with your table name
    timetable_data = cursor.fetchall()
    
    # Format the data in a way that we can easily display it in the HTML
    weekly_schedule = {}
    for row in timetable_data:
        time_slot = row['time']
        weekly_schedule[time_slot] = [
            row['Monday'],
            row['Tuesday'],
            row['Wednesday'],
            row['Thursday'],
            row['Friday'],
            row['Saturday']
        ]
    
    return weekly_schedule

@app.route('/user/settings', methods=['GET', 'POST'])
def user_settings():
    user_id = session['username']
    cursor = connection.cursor()
    query = "SELECT name FROM faculty WHERE id = %s"
    cursor.execute(query, (user_id,))  # Fetch username using user_id
    result = cursor.fetchone()
    if request.method == 'POST':
        theme = request.form['theme']
        email_notifications = 'email_notifications' in request.form
        sms_notifications = 'sms_notifications' in request.form
        current_password = request.form['current_password']
        new_password = request.form['new_password']
        confirm_password = request.form['confirm_password']

        if new_password == confirm_password:
            # Update password logic here
            pass
        
        # Logic for saving theme and notification preferences
        
        return redirect(url_for('user_settings'))

    return render_template('user_settings.html', username = result[0])


@app.route('/user/profile', methods=['GET', 'POST'])
def user_profile():
    user_id = session.get('username')  # Get the logged-in user's ID from the session
    cursor = connection.cursor()
    query = "select * from faculty where id=%s"
    cursor.execute(query,(user_id,))
    res = cursor.fetchall()
    result = res[0]
   # result=result[0]
        # Create Faculty object
    faculty_obj = Faculty(
        id=result[0],
        name=result[1],
        phone=result[2],
        email=result[3],
        branch=result[4],
        divisions=result[5].split(", "),  # Convert comma-separated string to a list
        subjects=result[6].split(", "),    # Convert comma-separated string to a list
        experience=result[7],
        office_hours=result[8],
        photo_url=result[9],
        role = result[10]
    )
    return render_template('user_profile.html', user=faculty_obj, username = result[1])


@app.route('/logout')
def logout():
    session.pop('username', None)
    session.pop('role', None)
    return redirect(url_for('login'))

if __name__ == '__main__':
    app.run(debug=True)