# import necessary modules 
from flask import Flask,redirect,url_for,render_template,request, flash, session, render_template_string, abort
from waitress import serve
import mysql.connector
from mysql.connector import pooling
import re
import hashlib
import random
import string 
import os
from datetime import datetime

import waitress
  
from flask_mail import Mail, Message 

# describe path 
UPLOAD_FOLDER = "C:\\Users\\administrator.CIPF0\\Desktop\\cipf projects\\complains register 3\\Documents"  # Set your upload folder path
ALLOWED_EXTENSIONS = {'txt', 'pdf', 'png', 'jpg', 'jpeg', 'gif'}  # Define allowed file extensions
def allowed_file(filename):
    return '.' in filename and filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS


# --- DATABASE CONNECTION POOLING FIX ---
dbconfig = {
    "host": os.environ.get('DB_HOST', 'localhost'),
    "database": os.environ.get('DB_NAME', 'cipf'),
    "user": os.environ.get('DB_USER', 'root'),
    "password": os.environ.get('DB_PASSWORD', 'password+10')
}

try:
    pool = pooling.MySQLConnectionPool(pool_name="mypool", pool_size=10, pool_reset_session=True, **dbconfig)
    print("Connection pool created successfully")
except mysql.connector.Error as err:
    print(f"Error creating connection pool: {err}")

def get_db_connection():
    return pool.get_connection()
# --------------------------------------

# Error logging 
import logging
from logging.handlers import RotatingFileHandler
# Configure logging for error monitoring
handler = RotatingFileHandler('app_error.log', maxBytes=10000000, backupCount=5)  # 10MB per file, keep 5 backups
handler.setLevel(logging.ERROR)  # Log errors and above (WARNING, ERROR, CRITICAL)
formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s')
handler.setFormatter(formatter)




app=Flask(__name__) 
app.secret_key="sparta"

# error logging 
app.logger.addHandler(handler)
app.logger.setLevel(logging.ERROR)


# sending error sms 
@app.errorhandler(500)
def handle_500_error(error):
    app.logger.error(f"500 Error: {str(error)}")
    
    # Retrieve recipients from database
    recipients = []
    try:
        db = get_db_connection()
        cursor = db.cursor()
        cursor.execute("SELECT email FROM it")  # Corrected to match your table/column
        rows = cursor.fetchall()
        recipients = [row[0] for row in rows]
        cursor.close()
        db.close()
        app.logger.info(f"Retrieved {len(recipients)} recipients from DB: {recipients}")
    except mysql.connector.Error as db_err:
        app.logger.error(f"Failed to retrieve email recipients from DB: {str(db_err)}")
        recipients = ['stevenchipunza2@gmail.com','schipunza@cit.co.zw']  # Fallback
    
    # Send email notification
    if recipients:  
        try:
            msg = Message(
                'Complains App Error Alert',
                sender=app.config['MAIL_USERNAME'],
                recipients=recipients 
            )
            msg.body = f"""
            An error occurred in the Complains App:
            
            Error: {str(error)}
            Timestamp: {datetime.now()}
            User Agent: {request.headers.get('User-Agent') if 'request' in globals() else 'N/A'}
            
            Check the logs for more details.
            """
            mail.send(msg)
            app.logger.info("Error email sent successfully")  # Debug log
        except Exception as e:
            app.logger.error(f"Failed to send error email: {str(e)}")
 
    return render_template('error.html'), 500


@app.route('/test_error')
def test_error():
    raise Exception("This is a test error for email notification")



app.config['MAIL_SERVER']="smtp.gmail.com"
app.config['MAIL_PORT']= 587
app.config['MAIL_USERNAME']='stevenchipunza1998@gmail.com'
app.config['MAIL_PASSWORD']='rfcd szjd pqsq anop'
app.config['MAIL_USE_TLS']= True
app.config['MAIL_USE_SSL']= False
app.config['MAIL_DEBUG']=True

mail= Mail(app)

# admin department administrators diff login 

class Department: 
    def __init__(self,id , name ):
        self.id=id
        self.name=name
        
    def __repr__(self):
        return f"Department('{self.name}')"

#create a list to store department objects

departments =[]

# create department objects and add them to the list

departments.append(Department(1, 'Pensions'))
departments.append(Department(2, 'Accounts'))
departments.append(Department(3, 'Property'))
departments.append(Department(4, 'Admin'))
departments.append(Department(5, 'Reception'))  # Added Reception

# create a dictionary to store department objects with their IDs as Key 
department_dict = {}  # Moved here to define before assignments
for department in departments:
    department_dict[department.id] = department


# admin add suggestions

@app.route('/add_suggestion', methods=['POST'])
def add_suggestion():
    # Get data from the form
    suggestion = request.form['suggestion']  # Fixed variable name for clarity; was 'suggestions'
    
    # Insert data into the database
    db = get_db_connection()
    cursor = db.cursor()
    cursor.execute("INSERT INTO suggestion(suggestion) VALUES (%s)", [suggestion])
    db.commit()  # Ensure commit is called to save changes
    
    # Fetch admin emails
    cursor.execute("SELECT email FROM suggestions_admins")
    email_list = cursor.fetchall()  # Fetch all email addresses
    recipients = [email[0] for email in email_list]  # Create a list of email addresses
    
    cursor.close()
    db.close()
    
    # Create and send the email with a greeting, suggestion, and regards
    msg = Message('New Suggestion', sender='stevenchipunza1998@gmail.com', recipients=recipients)
    msg.body = f"""
Dear Admin,


New suggestion: {suggestion}


Kind regards,
Complains and Suggestion Portal
    """.strip()  # Use f-string for dynamic content; strip() removes extra newlines
    
    mail.send(msg)  # Send the email
    
    flash("Suggestion added successfully")
    return redirect(url_for('delayed_index'))

@app.route('/add_complain', methods=['POST'])
def add_complain():
    # Get common data from the complain form
    name = request.form.get('name')
    email = request.form.get('email') 
    phone = request.form.get('phone')
    department = request.form.get('department')
    complain_details = request.form.get('complaint_details')

    # Initialize specific fields
    id_number = None
    account_number = None
    property_account_number = None
    property_address = None
    success = False  # Initialize success flag

    db = get_db_connection()
    cursor = db.cursor()

    try:
        # Check which department is selected and get relevant fields
        if department == "Pensions":
            id_number = request.form.get('id_number')  # Only for Pensions
            cursor.execute("INSERT INTO pensions_complains(name, email, phone_number, department, id_number, complain) VALUES(%s, %s, %s, %s, %s, %s)", 
                           (name, email, phone, department, id_number, complain_details))
            print("Inserted into property_complains for pensions")
            success = True

        elif department == 'Accounts':
            account_number = request.form.get('account_number')  # Only for Accounts
            cursor.execute("INSERT INTO accounts_complains(name, email, phone_number, department, account_number, complain) VALUES(%s, %s, %s, %s, %s, %s)", 
                           (name, email, phone, department, account_number, complain_details))
            print("Inserted into property_complains for accounts")
            success = True
           
        elif department == "Property":
            property_account_number = request.form.get('property_account_number')  # Only for Property
            property_address = request.form.get('property_address')  # Only for Property
            cursor.execute("INSERT INTO property_complains(name, email, phone_number, department, account_number, property_address, complain) VALUES(%s, %s, %s, %s, %s, %s, %s)", 
                           (name, email, phone, department, property_account_number, property_address, complain_details))
            print("Inserted into property_complains for property")
            success = True

        # Commit the transaction if using a database that requires it
        db.commit()

    except Exception as e:
        print(f"Error occurred: {str(e)}")  # Log the error
        flash(f"Error: {str(e)}")
        cursor.close()
        db.close()
        return redirect(url_for('error_delayed_index'))

    if not success:
        flash("Error: Unable to process your complaint.")
        cursor.close()
        db.close()
        return redirect(url_for('error_delayed_index'))

    # Fetch recipient emails
    cursor.execute("SELECT username FROM administrators WHERE department=%s;", (department,))
    email_list = cursor.fetchall()  # Fetch all email addresses
    recipient = [email[0] for email in email_list]  # Create a list of email addresses
    
    if department== 'Pensions':
        msg = Message('New Complain', sender='stevenchipunza1998@gmail.com', recipients=recipient)
        msg.body = f"Name: {name}\nEmail: {email}\nPhone: {phone}\nDepartment: {department}\nID Number: {id_number}\nComplain: {complain_details}\n   \nKind regards,\nComplains and Suggestion Portal"
        
        mail.send(msg)
    
    elif department == "Accounts":
        msg = Message('New Complain', sender='stevenchipunza1998@gmail.com', recipients=recipient)
        msg.body = f"Name: {name}\nEmail: {email}\nPhone: {phone}\nDepartment: {department}\nAccount Number: {account_number}\nComplain: {complain_details}\n   \nKind regards,\nComplains and Suggestion Portal"
        mail.send(msg)
        
    else:
        msg = Message('New Complain', sender='stevenchipunza1998@gmail.com', recipients=recipient)
        msg.body = f"Name: {name}\nEmail: {email}\nPhone: {phone}\nDepartment: {department}\nAccount Number: {account_number}\nProperty: {property_address}\nComplain: {complain_details}\n   \nKind regards,\nComplains and Suggestion Portal"
        mail.send(msg)
        
    cursor.close()
    db.close()
        
    flash("Complaint successfully posted")
    return redirect(url_for('delayed_index'))


# admin list all suggestions 

@app.route("/admin_list_suggestions", methods=['GET','POST'])
def admin_list_suggestions():
    try:
        db = get_db_connection()
        cursor = db.cursor()
        cursor.execute("SELECT * FROM suggestion")
        data=cursor.fetchall()#fetch all suggestions
        cursor.close()
        db.close()
                  
    except mysql.connector.Error as err:
        print("error connecting to database", err)
        return "error connecting to database"
    
    return render_template("admin_suggestion_list.html", data=data)


# admin list all complains

@app.route("/admin_list_complains", methods=['GET','POST'])
def admin_list_complains():
    try:
        db = get_db_connection()
        cursor = db.cursor()
        cursor.execute("SELECT * FROM complains")
        data=cursor.fetchall()#fetch all complains
        
        cursor.close()
        db.close()
            
    except mysql.connector.Error as err:
        print("error connecting to database", err)
        return "error connecting to database"
    
    return render_template("admin_change_status_complains.html", data=data)


# Accounts employee list complains button(view all) 

@app.route("/accounts_employee_list_complains", methods=['GET', 'POST'])
def accounts_employee_list_complains():
    try:
        page = request.args.get('page', 1, type=int)  # Get the current page number
        per_page = 4  # Number of items to display per page

        db = get_db_connection()
        cursor = db.cursor()
        # Fetch the total number of complaints for pagination
        cursor.execute("SELECT COUNT(*) FROM accounts_complains WHERE department= %s AND assigned= %s", (session['department'], session['username']))
        total_complains = cursor.fetchone()[0]
        
        # Fetch the complaints for the current page
        offset = (page - 1) * per_page
        cursor.execute("SELECT * FROM accounts_complains WHERE department= %s AND assigned= %s LIMIT %s OFFSET %s", (session['department'], session['username'], per_page, offset))
        data = cursor.fetchall()  # Fetch complaints for the current page

        cursor.close()
        db.close()

    except mysql.connector.Error as err:
        print("Error connecting to database", err)
        return "Error connecting to database"
    
    total_pages = (total_complains + per_page - 1) // per_page  # Calculate total pages

    return render_template("accounts_assigned_complains_list.html", data=data, page=page, total_pages=total_pages)


# pensions employee list complains button(view all) 

@app.route("/pensions_employee_list_complains", methods=['GET', 'POST'])
def pensions_employee_list_complains():
    try:
        page = request.args.get('page', 1, type=int)  # Get the current page number
        per_page = 4  # Number of items to display per page

        db = get_db_connection()
        cursor = db.cursor()
        # Fetch the total number of complaints for pagination
        cursor.execute("SELECT COUNT(*) FROM pensions_complains WHERE department= %s AND assigned= %s", (session['department'], session['username']))
        total_complains = cursor.fetchone()[0]
        
        # Fetch the complaints for the current page
        offset = (page - 1) * per_page
        cursor.execute("SELECT * FROM pensions_complains WHERE department= %s AND assigned= %s LIMIT %s OFFSET %s", (session['department'], session['username'], per_page, offset))
        data = cursor.fetchall()  # Fetch complaints for the current page
        
        cursor.close()
        db.close()

    except mysql.connector.Error as err:
        print("Error connecting to database", err)
        return "Error connecting to database"
    
    total_pages = (total_complains + per_page - 1) // per_page  # Calculate total pages

    return render_template("pensions_assigned_complains_list.html", data=data, page=page, total_pages=total_pages)


#Accounts employee menu buttons(active) 
@app.route("/accounts_active_employee_list_complains", methods=['GET', 'POST'])
def accounts_active_employee_list_complains():
    try:
        page = request.args.get('page', 1, type=int)  # Get the current page number
        per_page = 4  # Number of items to display per page

        db = get_db_connection()
        cursor = db.cursor()
        # Fetch the total number of complaints for pagination
        cursor.execute("SELECT COUNT(*) FROM accounts_complains WHERE department= %s AND Status=%s AND assigned= %s", (session['department'],'Active', session['username']))
        total_complains = cursor.fetchone()[0]
        
        # Fetch the complaints for the current page
        offset = (page - 1) * per_page
        cursor.execute("SELECT * FROM accounts_complains WHERE department= %s AND Status=%s AND assigned= %s LIMIT %s OFFSET %s", (session['department'],'Active', session['username'], per_page, offset))
        data = cursor.fetchall()  # Fetch complaints for the current page
        
        cursor.close
        db.close()

    except mysql.connector.Error as err:
        print("Error connecting to database", err)
        return "Error connecting to database"
    
    total_pages = (total_complains + per_page - 1) // per_page  # Calculate total pages

    return render_template("accounts_assigned_complains_list.html", data=data, page=page, total_pages=total_pages)

# Accounts employee menu buttons(closed)
@app.route("/accounts_closed_employee_list_complains", methods=['GET', 'POST'])
def accounts_closed_employee_list_complains():
    try:
        page = request.args.get('page', 1, type=int)  # Get the current page number
        per_page = 4  # Number of items to display per page

        db = get_db_connection()
        cursor = db.cursor()
        # Fetch the total number of complaints for pagination
        cursor.execute("SELECT COUNT(*) FROM accounts_complains WHERE department= %s AND Status=%s AND assigned= %s", (session['department'],'Closed', session['username']))
        total_complains = cursor.fetchone()[0]
        
        # Fetch the complaints for the current page
        offset = (page - 1) * per_page
        cursor.execute("SELECT * FROM accounts_complains WHERE department= %s AND Status=%s AND assigned= %s LIMIT %s OFFSET %s", (session['department'],'Closed', session['username'], per_page, offset))
        data = cursor.fetchall()  # Fetch complaints for the current page
        
        cursor.close()
        db.close()

    except mysql.connector.Error as err:
        print("Error connecting to database", err)
        return "Error connecting to database"
    
    total_pages = (total_complains + per_page - 1) // per_page  # Calculate total pages

    return render_template("accounts_assigned_complains_list.html", data=data, page=page, total_pages=total_pages)

#  Pensions employee menu buttons(active)
@app.route("/pensions_active_employee_list_complains", methods=['GET', 'POST'])
def pensions_active_employee_list_complains():
    try:
        page = request.args.get('page', 1, type=int)  # Get the current page number
        per_page = 4  # Number of items to display per page

        db = get_db_connection()
        cursor = db.cursor()
        # Fetch the total number of complaints for pagination
        cursor.execute("SELECT COUNT(*) FROM pensions_complains WHERE department= %s AND Status=%s AND assigned= %s", (session['department'],'Active', session['username']))
        total_complains = cursor.fetchone()[0]
        
        # Fetch the complaints for the current page
        offset = (page - 1) * per_page
        cursor.execute("SELECT * FROM pensions_complains WHERE department= %s AND Status=%s AND assigned= %s LIMIT %s OFFSET %s", (session['department'],'Active', session['username'], per_page, offset))
        data = cursor.fetchall()  # Fetch complaints for the current page
        
        cursor.close()
        db.close()

    except mysql.connector.Error as err:
        print("Error connecting to database", err)
        return "Error connecting to database"
    
    total_pages = (total_complains + per_page - 1) // per_page  # Calculate total pages

    return render_template("pensions_assigned_complains_list.html", data=data, page=page, total_pages=total_pages)

#  Pensions employee menu buttons(closed)
@app.route("/pensions_closed_employee_list_complains", methods=['GET', 'POST'])
def pensions_closed_employee_list_complains():
    try:
        page = request.args.get('page', 1, type=int)  # Get the current page number
        per_page = 4  # Number of items to display per page

        db = get_db_connection()
        cursor = db.cursor()
        # Fetch the total number of complaints for pagination
        cursor.execute("SELECT COUNT(*) FROM pensions_complains WHERE department= %s AND Status=%s AND assigned= %s", (session['department'],'Closed', session['username']))
        total_complains = cursor.fetchone()[0]
        
        # Fetch the complaints for the current page
        offset = (page - 1) * per_page
        cursor.execute("SELECT * FROM pensions_complains WHERE department= %s AND Status=%s AND assigned= %s LIMIT %s OFFSET %s", (session['department'],'Closed', session['username'], per_page, offset))
        data = cursor.fetchall()  # Fetch complaints for the current page
        
        cursor.close()
        db.close()

    except mysql.connector.Error as err:
        print("Error connecting to database", err)
        return "Error connecting to database"
    
    total_pages = (total_complains + per_page - 1) // per_page  # Calculate total pages

    return render_template("pensions_assigned_complains_list.html", data=data, page=page, total_pages=total_pages)

# Property employee list complains button(view all)

@app.route("/property_employee_list_complains", methods=['GET', 'POST'])
def property_employee_list_complains():
    try:
        page = request.args.get('page', 1, type=int)  # Get the current page number
        per_page = 4  # Number of items to display per page

        db = get_db_connection()
        cursor = db.cursor()
        # Fetch the total number of complaints for pagination
        cursor.execute("SELECT COUNT(*) FROM property_complains WHERE department= %s AND assigned= %s", (session['department'], session['username']))
        total_complains = cursor.fetchone()[0]
        
        # Fetch the complaints for the current page
        offset = (page - 1) * per_page
        cursor.execute("SELECT * FROM property_complains WHERE department= %s AND assigned= %s LIMIT %s OFFSET %s", (session['department'], session['username'], per_page, offset))
        data = cursor.fetchall()  # Fetch complaints for the current page
        
        cursor.close()
        db.close()

    except mysql.connector.Error as err:
        print("Error connecting to database", err)
        return "Error connecting to database"
    
    total_pages = (total_complains + per_page - 1) // per_page  # Calculate total pages
    
    return render_template("property_assigned_complains_list.html", data=data, page=page, total_pages=total_pages)



# Property employee list complains button(active)

@app.route("/property_active_employee_list_complains", methods=['GET', 'POST'])
def property_active_employee_list_complains():
    try:
        page = request.args.get('page', 1, type=int)  # Get the current page number
        per_page = 4  # Number of items to display per page

        db = get_db_connection()
        cursor = db.cursor()
        # Fetch the total number of complaints for pagination
        cursor.execute("SELECT COUNT(*) FROM property_complains WHERE department= %s AND Status=%s AND assigned= %s", (session['department'],'Active', session['username']))
        total_complains = cursor.fetchone()[0]
        
        # Fetch the complaints for the current page
        offset = (page - 1) * per_page
        cursor.execute("SELECT * FROM property_complains WHERE department= %s AND Status=%s AND assigned= %s LIMIT %s OFFSET %s", (session['department'],'Active', session['username'], per_page, offset))
        data = cursor.fetchall()  # Fetch complaints for the current page
        
        cursor.close()
        db.close()

    except mysql.connector.Error as err:
        print("Error connecting to database", err)
        return "Error connecting to database"
    
    total_pages = (total_complains + per_page - 1) // per_page  # Calculate total pages
    
    return render_template("property_assigned_complains_list.html", data=data, page=page, total_pages=total_pages)

# Property employee list complains button(closed)

@app.route("/property_closed_employee_list_complains", methods=['GET', 'POST'])
def property_closed_employee_list_complains():
    try:
        page = request.args.get('page', 1, type=int)  # Get the current page number
        per_page = 4  # Number of items to display per page

        db = get_db_connection()
        cursor = db.cursor()
        # Fetch the total number of complaints for pagination
        cursor.execute("SELECT COUNT(*) FROM property_complains WHERE department= %s AND Status= %s AND assigned= %s", (session['department'],'Closed', session['username']))
        total_complains = cursor.fetchone()[0]
        
        # Fetch the complaints for the current page
        offset = (page - 1) * per_page
        cursor.execute("SELECT * FROM property_complains WHERE department= %s AND Status=%s AND assigned= %s LIMIT %s OFFSET %s", (session['department'],'Closed', session['username'], per_page, offset))
        data = cursor.fetchall()  # Fetch complaints for the current page
        
        cursor.close()
        db.close()

    except mysql.connector.Error as err:
        print("Error connecting to database", err)
        return "Error connecting to database"
    
    total_pages = (total_complains + per_page - 1) // per_page  # Calculate total pages
    
    return render_template("property_assigned_complains_list.html", data=data, page=page, total_pages=total_pages)

# search bar top menu 

@app.route("/search_list_complains", methods=['GET','POST'])
def search_list_complains():
    
    if request.method == 'POST':
        searched_value = request.form['search_list']
        
        try:
            db = get_db_connection()
            cursor = db.cursor()
            cursor.execute("SELECT * FROM complains WHERE complains_id = %s", (searched_value,))
            data = cursor.fetchall()  # fetch all complains
            
            cursor.close()
            db.close()
            
        except mysql.connector.Error as err:
            print("Error connecting to database", err)
            return "Error connecting to database"
        
        return render_template("search_results.html", data=data)
    
    return render_template("admin_complains_list.html")  # render the search form if it's a GET request

# admin login form processing

@app.route('/login_form', methods=["GET", "POST"])
def login_form():
    msg = None
    if request.method == 'POST':
        try:
            db = get_db_connection()
            cursor = db.cursor()
            username = request.form['username']
            password = request.form['password']
            
            # First check: regular password
            cursor.execute("SELECT * FROM administrators WHERE username=%s AND password=%s", (username, password))
            record = cursor.fetchone()
            
            if record is not None:
                # Successful login with regular password
                session['loggedin'] = True
                session['username'] = record[1]
                department = record[3]  # assuming department is stored in column 3
                session['department'] = department  # Added for consistency
                cursor.close()
                db.close()
                if department == "Accounts":
                    return render_template('accounting_admin_main.html')
                elif department == "Pensions":
                    return render_template("pension_admin_main.html")
                elif department == "Property":
                    return render_template("property_admin_main.html")
                elif department == "Admin":
                    return render_template("main_admin.html")
                elif department == "Reception":
                    return redirect(url_for('reception_list_complains'))  # Changed to redirect
                else:
                    msg = "Department MISMATCHES WITH LOGIN DETAILS"
            else:
                # Second check: temporary password
                cursor.execute("SELECT * FROM administrators WHERE username=%s AND temporary_password=%s", (username, password))
                record_temp = cursor.fetchone()
                
                if record_temp is not None:
                    # Successful login with temporary password
                    session['loggedin'] = True
                    session['username'] = record_temp[1]
                    department = record_temp[3]  # assuming department is stored in column 3
                    session['department'] = department  # Added for consistency
                    cursor.close()
                    db.close()
                    # Open a different HTML file or redirect as needed
                    return render_template("admin_new_password.html")  # Change this to your desired template
                else:
                    msg = "Invalid username or password"
                    cursor.close()
                    db.close()

        except mysql.connector.Error as err:
            msg = "Error occurred during login: {}".format(err)

    return render_template("index.html", msg=msg)


# admin change password confirm password

@app.route('/admin_confirm_new_password', methods=['POST', 'GET'])
def admin_confirm_new_password():
    if request.method == "POST":
        email = request.form['email']
        new_password = request.form['password']
        confirm_password = request.form['confirm_password']

        # Check if the user exists in the database
        db = get_db_connection()
        cursor = db.cursor()
        cursor.execute('SELECT * FROM administrators WHERE username = %s', (email,))
        user = cursor.fetchone()

        if user:
            # Check if the new password and confirm password match
            if new_password == confirm_password:
                # Update the password in the database
                cursor.execute('UPDATE administrators SET temporary_password = NULL, password = %s WHERE username = %s', (new_password, email))
                db.commit()
                cursor.close()
                db.close()

                flash("Password updated successfully.")
                return redirect(url_for('login'))  # Redirect to the login page after success
            else:
                flash("Passwords do not match. Please try again.")
                cursor.close()
                db.close()
        else:
            flash("User not found. Please check the email.")
            cursor.close()
            db.close()

        # Render the login template with flash messages if user not found or passwords do not match
        return render_template('admin_login.html')

    # Render the index template for GET requests
    return render_template("index.html")

# employee login form processing

@app.route('/employee_login_form', methods=["GET","POST"])
def employee_login_form():
    msg = None
    if request.method == 'POST':
        username = request.form['username']
        password = request.form['password']
        try:
            db = get_db_connection()
            cursor = db.cursor()
            cursor.execute("SELECT * FROM employee WHERE email=%s AND password=%s", (username, password))
            record = cursor.fetchone()
            if record:
                session['loggedin'] = True
                session['username'] = record[0]
                session['department'] = record[2] # assuming department is in column 3                          
                cursor.close()
                db.close()
                # redirect to respective department dashboard 
                if session['department']=="Accounts":
                    return render_template('accounts_employee_home_page.html')
                elif session['department']=="Pensions":
                    return render_template('pensions_employee_home_page.html')
                elif session['department'] == "Property":
                    return render_template('property_employee_home_page.html')
                else :
                    msg = "Department mismatches login details"
                
            else :
                # Second check: temporary password
                cursor.execute("SELECT * FROM employee WHERE email=%s AND temporary_password=%s", (username, password))
                record_temp = cursor.fetchone()
                
                if record_temp is not None:
                    # Successful login with temporary password
                    session['loggedin'] = True
                    session['username'] = record_temp[1]
                    department = record_temp[2]  # assuming department is stored in column 3
                    cursor.close()
                    db.close()
                    # Open a different HTML file or redirect as needed
                    return render_template("employee_new_password.html")  # Change this to your desired template
                else:
                    msg = "Invalid username or password"
                    cursor.close()
                    db.close()
        except mysql.connector.Error as err:
            msg = "Error occurred during login: {}".format(err)
    return render_template("index.html", msg=msg)

# Employee confirm new password

@app.route('/employee_confirm_new_password', methods=['POST', 'GET'])
def employee_confirm_new_password():
    if request.method == "POST":
        email = request.form['email']
        new_password = request.form['password']
        confirm_password = request.form['confirm_password']

        # Check if the user exists in the database
        db = get_db_connection()
        cursor = db.cursor()
        cursor.execute('SELECT * FROM employee WHERE email = %s', (email,))
        user = cursor.fetchone()

        if user:
            # Check if the new password and confirm password match
            if new_password == confirm_password:
                # Update the password in the database
                cursor.execute('UPDATE employee SET temporary_password = NULL, password = %s WHERE email = %s', (new_password, email))
                db.commit()
                cursor.close()
                db.close()

                flash("Password updated successfully.")
                return redirect(url_for('login'))  # Redirect to the login page after success
            else:
                flash("Passwords do not match. Please try again.")
                cursor.close()
                db.close()
        else:
            flash("User not found. Please check the email.")
            cursor.close()
            db.close()

        # Render the login template with flash messages if user not found or passwords do not match
        return render_template('admin_login.html')

    # Render the index template for GET requests
    return render_template("index.html")

# Main admin add supervisor

@app.route("/add_supervisors", methods=["GET", "POST"])
def add_supervisors():
    if request.method == "POST":
        username = request.form["username"]
        department = request.form["department"]
        password = request.form["password"]
        confirm_password = request.form["confirm-password"]

        if password != confirm_password:
            return "Passwords do not match", 400
         
        # Hash the password using SHA-256
        hashed_password = hashlib.sha256(password.encode()).hexdigest()

        try:
            db = get_db_connection()
            cursor = db.cursor()
            # Insert the new supervisor into the database
            cursor.execute("INSERT INTO administrators (username, department, password) VALUES (%s, %s, %s)",
                           (username, department, hashed_password))
            db.commit()
            cursor.close()
            db.close()
        except mysql.connector.Error as err:
            return "failed to create supervisor", 400
        # Open another template upon adding a new supervisor
        return render_template("main_admin_template.html")  # Replace with your desired template name

    return render_template("supervisor_registration.html")

# Main admin add supervisor
@app.route("/add_supervisor", methods=["GET", "POST"])
def add_supervisor():
    try:
        username = request.form["username"]
        department = request.form["department"]
        password = request.form["password"]
        confirm_password = request.form.get("confirm-password")  # Use get() to avoid KeyError

        if password != confirm_password:
            flash("Passwords do not match")
            return redirect(url_for("add_supervisor_form"))
    
        # Hash the password
        hashed_password = hashlib.sha256(password.encode()).hexdigest()
    
        db = get_db_connection()
        cursor = db.cursor()
        cursor.execute("INSERT INTO administrators (username, department, password) VALUES (%s, %s, %s)", (username, department, hashed_password))
        db.commit()
        cursor.close()
        db.close()
    
        flash("Supervisor added successfully")
        return render_template("user_added_successfully_delayed_text.html")
    
    except KeyError as e:
        flash(f"Error: {e} field is missing")
        return redirect(url_for('add_supervisor_form'))
    except mysql.connector.Error as err:
        print("Error adding supervisor", err)
        flash("Error adding supervisor")
        return redirect(url_for('add_supervisor_form'))   
    
# Main Admin add employee 
@app.route("/add_employee", methods=["GET", "POST"])
def add_employee():
    try:
        username = request.form["username"]
        department = request.form["department"]
        password = request.form["password"]
        confirm_password = request.form.get("confirm-password")  # Use get() to avoid KeyError
    except KeyError as e:
        flash(f"Error: {e} field is missing")
        return redirect(url_for('delayed_index'))
        
    if password != confirm_password:
        flash("Passwords do not match")
        return redirect(url_for("add_employee"))  # Redirect to the same page
        
    hashed_password = hashlib.sha256(password.encode()).hexdigest()
    
    db = get_db_connection()
    cursor = db.cursor()
    cursor.execute("INSERT INTO employee (email, department, password) VALUES (%s, %s, %s)",(username, department, hashed_password))
    db.commit()
    cursor.close()
    db.close()
    
    flash("employee added successfully")
    return render_template("user_added_successfully_delayed_text.html")   
        
# employee login required

def employee_login_required(f):
    def decorated_function(*args, **kwargs):
        if session.get('loggedin') is None:
            return redirect(url_for('employee_login'))
        return f(*args, **kwargs)
    return decorated_function

# logout employee 

@app.route('/employee_logout')
def employee_logout():
    session.clear()
    return redirect(url_for('employee_login'))

#  ADMIN STATUS 
# admin add/ update status on email assignment

@app.route('/update_status', methods=['POST'])
def update_status():
    data_id = request.form["data_id"]
    new_status = request.form["new_status"]
    assigned_email = request.form['assigned_email']
    
    try:
        db = get_db_connection()
        cursor = db.cursor()
        cursor.execute("UPDATE complains SET status = %s, assigned = %s WHERE complains_id = %s", (new_status, assigned_email, data_id))
        db.commit()
        cursor.close()
        db.close()
        flash("Status updated successfully")
        return redirect(url_for('admin_list_complains'))
    except mysql.connector.errors.Error as err:
        print("Error updating status", err)
        flash("Error updating status")
        return redirect(url_for('admin_list_complains'))

# update accounts admin status

@app.route('/accounts_update_status', methods=['POST'])
def accounts_update_status():
    data_id = request.form["data_id"]
    new_status = request.form["new_status"]
    assigned_email = request.form['assigned_email']
    
    # Validate the email address
    email_regex = r"[^@]+@[^@]+\.[^@]+"
    if not re.match(email_regex, assigned_email):
        flash("Invalid email address")
        return redirect(url_for('accounts_admin_list_complains_after_update'))
    
    try:
        db = get_db_connection()
        cursor = db.cursor()
        cursor.execute("UPDATE accounts_complains SET Status = %s, assigned = %s WHERE complains_id = %s", (new_status, assigned_email, data_id))
        db.commit()
        flash("Status updated successfully")
        
        msg = Message("New assigned task/ Status change ", sender= "Admin", recipients=[assigned_email])
        msg.body =f" Complains system you have been assigned a new task or a change in status \n status: {new_status}"
        mail.send(msg)
        
        cursor.close()
        db.close()
        
        return redirect(url_for("accounts_admin_list_complains_after_update"))
    except mysql.connector.errors.Error as err:
        print("Error updating status", err)
        flash("Error updating status")
        return redirect(url_for('accounts_admin_list_complains_after_update'))

#property admin update status 

@app.route('/property_update_status', methods=['POST'])
def property_update_status():
    data_id = request.form["data_id"]
    new_status = request.form["new_status"]
    assigned_email = request.form['assigned_email']
    
    # Validate the email address
    email_regex = r"[^@]+@[^@]+\.[^@]+"
    if not re.match(email_regex, assigned_email):
        flash("Invalid email address")
        return redirect(url_for('property_admin_list_complains_after_update'))
    
    try:
        db = get_db_connection()
        cursor = db.cursor()
        cursor.execute("UPDATE property_complains SET Status = %s, assigned = %s WHERE complains_id = %s", (new_status, assigned_email, data_id))
        db.commit()
        flash("Status updated successfully")
        
        msg = Message("New assigned task/ Status change ", sender= "Admin", recipients=[assigned_email])
        msg.body =f" Complains system you have been assigned a new task or a change in status \n status: {new_status}"
        mail.send(msg)
        
        cursor.close()
        db.close()
        
        return redirect(url_for("property_admin_list_complains_after_update"))
    except mysql.connector.errors.Error as err:
        print("Error updating status", err)
        flash("Error updating status")
        return redirect(url_for('property_admin_list_complains_after_update'))

#pensions admin update status 

@app.route('/pensions_update_status', methods=['POST'])
def pensions_update_status():
    data_id = request.form["data_id"]
    new_status = request.form["new_status"]
    assigned_email = request.form['assigned_email']
    
    # Validate the email address
    email_regex = r"[^@]+@[^@]+\.[^@]+"
    if not re.match(email_regex, assigned_email):
        flash("Invalid email address")
        return redirect(url_for('pensions_admin_list_complains_after_update'))
    
    try:
        db = get_db_connection()
        cursor = db.cursor()
        cursor.execute("UPDATE pensions_complains SET Status = %s, assigned = %s WHERE complains_id = %s", (new_status, assigned_email, data_id))
        db.commit()
        flash("Status updated successfully")
        
        msg = Message("New assigned task/ Status change ", sender= "Admin", recipients=[assigned_email])
        msg.body =f" Complains system you have been assigned a new task or a change in status \n status: {new_status}"
        mail.send(msg)
        
        cursor.close()
        db.close()
        
        return redirect(url_for("pension_admin_list_complains_after_update"))
    except mysql.connector.errors.Error as err:
        print("Error updating status", err)
        flash("Error updating status")
        return redirect(url_for('pension_admin_list_complains_after_update'))

# accounts employee add comment into system

@app.route('/accounts_assigned_add_comment', methods= ["POST"])
def accounts_assigned_add_comment():
    text_data =request.form['comment']
    data_id = request.form['data_id']
    try:
        db = get_db_connection()
        cursor = db.cursor()
        cursor.execute("SELECT assigned_comment FROM accounts_complains WHERE complains_id = %s", (data_id,))
        existing_text = cursor.fetchone()[0]
        if existing_text is None:
            updated_text = ' -  NAME:(  '+ session['username'] + ') - '+ text_data
        else:
            updated_text = existing_text + ' -  NAME:(  '+ session['username'] + ') - '+ text_data
        cursor.execute("UPDATE accounts_complains  SET assigned_comment =%s WHERE complains_id=%s",(updated_text, data_id))
        
        cursor.execute("SELECT department FROM accounts_complains WHERE complains_id = %s",(data_id,))
        department=cursor.fetchone()[0]
        
        if department=='Accounts':
            cursor.execute("SELECT username FROM administrators WHERE department=%s;", (department,))
            email_list = cursor.fetchall()  # Fetch all email addresses
            recipient = [email[0] for email in email_list]  # Create a list of email addresses

            msg = Message("Comment Posted in the  ", sender= session['username'], recipients=recipient)
            msg.body =f"Complain comment as been posted into the system\n Employee: {session['username']} \n Complain Comment Conversation: {updated_text}"
            mail.send(msg)
        
        
        db.commit()
        cursor.close()
        db.close()
        flash("Text updated successfully")
        return redirect(url_for("accounts_employee_list_complains"))
    except mysql.connector.Error as err:
        print("Error updating comment", err)
        flash("error updating text")
        return redirect(url_for("accounts_employee_list_complains"))

# pensions employee add comment into system

@app.route('/pensions_assigned_add_comment', methods= ["POST"])
def pensions_assigned_add_comment():
    text_data =request.form['comment']
    data_id = request.form['data_id']
    try:
        db = get_db_connection()
        cursor = db.cursor()
        cursor.execute("SELECT assigned_comment FROM pensions_complains WHERE complains_id = %s", (data_id,))
        existing_text = cursor.fetchone()[0]
        if existing_text is None:
            updated_text = ' -  NAME:(  '+ session['username'] + ') - '+ text_data
        else:
            updated_text = existing_text + ' -  NAME:(  '+ session['username'] + ') - '+ text_data
        cursor.execute("UPDATE pensions_complains  SET assigned_comment =%s WHERE complains_id=%s",(updated_text, data_id))
        
        cursor.execute("SELECT department FROM pensions_complains WHERE complains_id = %s",(data_id,))
        department=cursor.fetchone()[0]
        
        if department=='Pensions':
            cursor.execute("SELECT username FROM administrators WHERE department=%s;", (department,))
            email_list = cursor.fetchall()  # Fetch all email addresses
            recipient = [email[0] for email in email_list]  # Create a list of email addresses

            msg = Message("Comment Posted in the  ", sender= session['username'], recipients=recipient)
            msg.body =f"Complain comment as been posted into the system\n Employee: {session['username']} \n Complain Comment Conversation: {updated_text}"
            mail.send(msg)
        
        
        db.commit()
        cursor.close()
        db.close()
        flash("Text updated successfully")
        return redirect(url_for("pensions_employee_list_complains"))
    except mysql.connector.Error as err:
        print("Error updating comment", err)
        flash("error updating text")
        return redirect(url_for("pensions_employee_list_complains"))

# property employee add comment into system

@app.route('/property_assigned_add_comment', methods= ["POST"])
def property_assigned_add_comment():
    text_data =request.form['comment']
    data_id = request.form['data_id']
    try:
        db = get_db_connection()
        cursor = db.cursor()
        cursor.execute("SELECT assigned_comment FROM property_complains WHERE complains_id = %s", (data_id,))
        existing_text = cursor.fetchone()[0]
        if existing_text is None:
            updated_text = ' -  NAME:(  '+ session['username'] + ') - '+ text_data
        else:
            updated_text = existing_text + ' -  NAME:(  '+ session['username'] + ') - '+ text_data
        cursor.execute("UPDATE property_complains  SET assigned_comment =%s WHERE complains_id=%s",(updated_text, data_id))
        
        cursor.execute("SELECT department FROM property_complains WHERE complains_id = %s",(data_id,))
        department=cursor.fetchone()[0]
        
        if department== "Property":
            
            cursor.execute("SELECT username FROM administrators WHERE department=%s;", (department,))
            email_list = cursor.fetchall()  # Fetch all email addresses
            recipient = [email[0] for email in email_list]
            
            msg = Message("Comment Posted in the  ", sender= session['username'], recipients=recipient)
            msg.body =f"Complain comment as been posted into the system.\n Employee: {session['username']} \n Complain Comment Conversation: {updated_text}"
            mail.send(msg)
        
        db.commit()
        cursor.close()
        db.close()
        flash("Text updated successfully")
        return redirect(url_for("property_employee_list_complains"))
    except mysql.connector.Error as err:
        print("Error updating comment", err)
        flash("error updating text")
        return redirect(url_for("property_employee_list_complains"))

# Accounts admin add comment into system 

@app.route('/accounts_admin_add_comment', methods =['POST'])
def accounts_admin_add_comment():
    text_data =request.form['comment']
    data_id = request.form['data_id']
    try:
        db = get_db_connection()
        cursor = db.cursor()
        cursor.execute("SELECT assigned_comment FROM accounts_complains WHERE complains_id = %s", (data_id,))
        existing_text = cursor.fetchone()[0]
        if existing_text is None:
            updated_text = ' -  NAME :  '+ session['username'] + ' - '+ text_data
        else:
            updated_text = existing_text + '\n -  NAME :  '+ session['username'] + ' - '+ text_data
        cursor.execute("UPDATE accounts_complains  SET assigned_comment =%s WHERE complains_id=%s",(updated_text, data_id))
        cursor.execute("SELECT assigned FROM accounts_complains WHERE complains_id= %s",(data_id,))
        assigned_email = cursor.fetchone()[0]  # Get the assigned email from the query result
        db.commit()
        
        msg = Message("New assigned task/ Status change ", sender= "HOD", recipients=[assigned_email])
        msg.body =f" Complain comment as been posted into the system. \n Complain Comment Conversation: {updated_text}"
        mail.send(msg)
        
        cursor.close()
        db.close()
        
        flash("Text updated successfully")
        return redirect(url_for("accounting_admin_list_complains"))
    except mysql.connector.Error as err:
        print("Error updating comment", err)
        flash("error updating text")
        return redirect(url_for("accounting_admin_list_complains"))

# Pensions admin add comment into system 

@app.route('/pensions_admin_add_comment', methods =['POST'])
def pensions_admin_add_comment():
    text_data =request.form['comment']
    data_id = request.form['data_id']
    try:
        db = get_db_connection()
        cursor = db.cursor()
        cursor.execute("SELECT assigned_comment FROM pensions_complains WHERE complains_id = %s", (data_id,))
        existing_text = cursor.fetchone()[0]
        if existing_text is None:
            updated_text = ' -  NAME :  '+ session['username'] + ' - '+ text_data
        else:
            updated_text = existing_text + '\n -  NAME :  '+ session['username'] + ' - '+ text_data 
        cursor.execute("UPDATE pensions_complains  SET assigned_comment =%s WHERE complains_id=%s",(updated_text, data_id))
        cursor.execute("SELECT assigned FROM pensions_complains WHERE complains_id= %s",(data_id,))
        assigned_email = cursor.fetchone()[0]  # Get the assigned email from the query result
        db.commit()
        
        msg = Message("New assigned task/ Status change ", sender= "HOD", recipients=[assigned_email])
        msg.body =f" Complain comment as been posted into the system \n Complain Comment Conversation: {updated_text}"
        mail.send(msg)
        
        cursor.close()
        db.close()
        
        flash("Text updated successfully")
        return redirect(url_for("pensions_admin_list_complains"))
    except mysql.connector.Error as err:
        print("Error updating comment", err)
        flash("error updating text")
        return redirect(url_for("pensions_admin_list_complains"))
    
# Property admin add comment into system 

@app.route('/property_admin_add_comment', methods =['POST'])
def property_admin_add_comment():
    text_data =request.form['comment']
    data_id = request.form['data_id']
    try:
        db = get_db_connection()
        cursor = db.cursor()
        cursor.execute("SELECT assigned_comment FROM property_complains WHERE complains_id = %s", (data_id,))
        existing_text = cursor.fetchone()[0]
        if existing_text is None:
            updated_text = ' -  NAME :  '+ session['username'] + ' - '+ text_data
        else:
            updated_text = existing_text + '\n -  NAME :  '+ session['username'] + ' - '+ text_data
        cursor.execute("UPDATE property_complains  SET assigned_comment =%s WHERE complains_id=%s",(updated_text, data_id))
        cursor.execute("SELECT assigned FROM property_complains WHERE complains_id= %s",(data_id,))
        assigned_email = cursor.fetchone()[0]  # Get the assigned email from the query result
        db.commit()
        
        msg = Message("New assigned task/ Status change ", sender= "HOD ", recipients=[assigned_email])
        msg.body =f" Complains system you have been a new comment! \n Complain Comment Conversation: {updated_text}"
        
        mail.send(msg)
        cursor.close()
        db.close()
        
        flash("Text updated successfully")
        return redirect(url_for("property_admin_list_complains"))
    except mysql.connector.Error as err:
        print("Error updating comment", err)
        flash("error updating text")
        return redirect(url_for("property_admin_list_complains"))

# accounting admin list complains button
@app.route("/accounting_admin_list_complains", methods=['GET', 'POST'])
def accounting_admin_list_complains():
    try:
        page = request.args.get('page', 1, type=int)  # Get the current page number
        per_page = 4  # Number of items to display per page

        db = get_db_connection()
        cursor = db.cursor()
        # Fetch the total number of complaints for pagination
        cursor.execute("SELECT COUNT(*) FROM accounts_complains")
        total_complains = cursor.fetchone()[0]
        
        # Fetch the complaints for the current page
        offset = (page - 1) * per_page
        cursor.execute("SELECT * FROM accounts_complains LIMIT %s OFFSET %s", (per_page, offset))
        data = cursor.fetchall()  # Fetch complaints for the current page

        cursor.execute("SELECT email FROM employee WHERE department = %s", ("Accounts",))
        emails = cursor.fetchall()  # Fetch all emails
        email_list = [email[0] for email in emails]  # Extract email addresses
        
        cursor.close()
        db.close()

    except mysql.connector.Error as err:
        print("Error connecting to database", err)
        return "Error connecting to database"
    
    total_pages = (total_complains + per_page - 1) // per_page  # Calculate total pages

    return render_template("accounting_admin_change_status_complains.html", data=data, items=email_list, page=page, total_pages=total_pages)


# Pension admin list complains 
@app.route("/pensions_admin_list_complains", methods=['GET', 'POST'])
def pensions_admin_list_complains():
    try:
        page = request.args.get('page', 1, type=int)  # Get the current page number
        per_page = 4  # Number of items to display per page

        db = get_db_connection()
        cursor = db.cursor()
        # Fetch the total number of complaints for pagination
        cursor.execute("SELECT COUNT(*) FROM pensions_complains")
        total_complains = cursor.fetchone()[0]
        
        # Fetch the complaints for the current page
        offset = (page - 1) * per_page
        cursor.execute("SELECT * FROM pensions_complains LIMIT %s OFFSET %s", (per_page, offset))
        data = cursor.fetchall()  # Fetch complaints for the current page

        cursor.execute("SELECT email FROM employee WHERE department = %s", ("Pensions",))
        emails = cursor.fetchall()  # Fetch all emails
        email_list = [email[0] for email in emails]  # Extract email addresses
        
        cursor.close()
        db.close()

    except mysql.connector.Error as err:
        print("Error connecting to database", err)
        return "Error connecting to database"
    
    total_pages = (total_complains + per_page - 1) // per_page  # Calculate total pages

    return render_template("pensions_admin_change_status_complain.html", data=data, items=email_list, page=page, total_pages=total_pages)

#property admin list complains

@app.route("/property_admin_list_complains", methods=['GET', 'POST'])
def property_admin_list_complains():
    try:
        page = request.args.get('page', 1, type=int)  # Get the current page number
        per_page = 4  # Number of items to display per page

        db = get_db_connection()
        cursor = db.cursor()
        # Fetch the total number of complaints for pagination
        cursor.execute("SELECT COUNT(*) FROM property_complains")
        total_complains = cursor.fetchone()[0]
        
        # Fetch the complaints for the current page
        offset = (page - 1) * per_page
        cursor.execute("SELECT * FROM property_complains LIMIT %s OFFSET %s", (per_page, offset))
        data = cursor.fetchall()  # Fetch complaints for the current page

        cursor.execute("SELECT email FROM employee WHERE department = %s", ("Property",))
        emails = cursor.fetchall()  # Fetch all emails
        email_list = [email[0] for email in emails]  # Extract email addresses
        
        cursor.close()
        db.close()

    except mysql.connector.Error as err:
        print("Error connecting to database", err)
        return "Error connecting to database"
    
    total_pages = (total_complains + per_page - 1) // per_page  # Calculate total pages

    return render_template("property_admin_change_status_complain.html", data=data, items=email_list, page=page, total_pages=total_pages)


# accounts admin  menu buttons(active)
@app.route("/accounting_active_admin_list_complains", methods=['GET', 'POST'])
def accounting_active_admin_list_complains():
    try:
        page = request.args.get('page', 1, type=int)  # Get the current page number
        per_page = 4  # Number of items to display per page
        db = get_db_connection()
        cursor = db.cursor()
        # Fetch the total number of active complaints for pagination
        cursor.execute("SELECT COUNT(*) FROM accounts_complains WHERE Status = %s", ("Active",))
        total_complains = cursor.fetchone()[0]
        
        # Fetch the active complaints for the current page
        offset = (page - 1) * per_page
        cursor.execute(
            "SELECT * FROM accounts_complains WHERE Status = %s LIMIT %s OFFSET %s",
            ("Active", per_page, offset)
        )
        data = cursor.fetchall()  # Fetch complaints for the current page
        cursor.execute("SELECT email FROM employee WHERE department = %s", ("Accounts",))
        emails = cursor.fetchall()  # Fetch all emails
        email_list = [email[0] for email in emails]  # Extract email addresses
        
        cursor.close()
        db.close()
    except mysql.connector.Error as err:
        print("Error connecting to database", err)
        return "Error connecting to database"
    
    total_pages = (total_complains + per_page - 1) // per_page  # Calculate total pages
    return render_template("accounting_admin_change_status_complains.html", data=data, items=email_list, page=page, total_pages=total_pages)


# accounts admin  menu buttons(closed )
@app.route("/accounting_closed_admin_list_complains", methods=['GET', 'POST'])
def accounting_closed_admin_list_complains():
    try:
        page = request.args.get('page', 1, type=int)  # Get the current page number
        per_page = 4  # Number of items to display per page
        db = get_db_connection()
        cursor = db.cursor()
        # Fetch the total number of active complaints for pagination
        cursor.execute("SELECT COUNT(*) FROM accounts_complains WHERE Status = %s", ("Closed",))
        total_complains = cursor.fetchone()[0]
        
        # Fetch the active complaints for the current page
        offset = (page - 1) * per_page
        cursor.execute(
            "SELECT * FROM accounts_complains WHERE Status = %s LIMIT %s OFFSET %s",
            ("Closed", per_page, offset)
        )
        data = cursor.fetchall()  # Fetch complaints for the current page
        cursor.execute("SELECT email FROM employee WHERE department = %s", ("Accounts",))
        emails = cursor.fetchall()  # Fetch all emails
        email_list = [email[0] for email in emails]  # Extract email addresses
        
        cursor.close()
        db.close()
    except mysql.connector.Error as err:
        print("Error connecting to database", err)
        return "Error connecting to database"
    
    total_pages = (total_complains + per_page - 1) // per_page  # Calculate total pages
    return render_template("accounting_admin_change_status_complains.html", data=data, items=email_list, page=page, total_pages=total_pages)

# pension admin  menu buttons(active)
@app.route("/pensions_active_admin_list_complains", methods=['GET', 'POST'])
def pensions_active_admin_list_complains():
    try:
        page = request.args.get('page', 1, type=int)  # Get the current page number
        per_page = 4  # Number of items to display per page
        db = get_db_connection()
        cursor = db.cursor()
        # Fetch the total number of active complaints for pagination
        cursor.execute("SELECT COUNT(*) FROM pensions_complains WHERE Status = %s", ("Active",))
        total_complains = cursor.fetchone()[0]
        
        # Fetch the active complaints for the current page
        offset = (page - 1) * per_page
        cursor.execute(
            "SELECT * FROM pensions_complains WHERE Status = %s LIMIT %s OFFSET %s",
            ("Active", per_page, offset)
        )
        data = cursor.fetchall()  # Fetch complaints for the current page
        cursor.execute("SELECT email FROM employee WHERE department = %s", ("Pensions",))
        emails = cursor.fetchall()  # Fetch all emails
        email_list = [email[0] for email in emails]  # Extract email addresses
        
        cursor.close()
        db.close()
    except mysql.connector.Error as err:
        print("Error connecting to database", err)
        return "Error connecting to database"
    
    total_pages = (total_complains + per_page - 1) // per_page  # Calculate total pages
    return render_template("pensions_admin_change_status_complain.html", data=data, items=email_list, page=page, total_pages=total_pages)

# pensions admin  menu buttons(closed)
@app.route("/pensions_closed_admin_list_complains", methods=['GET', 'POST'])
def pensions_closed_admin_list_complains():
    try:
        page = request.args.get('page', 1, type=int)  # Get the current page number
        per_page = 4  # Number of items to display per page
        db = get_db_connection()
        cursor = db.cursor()
        # Fetch the total number of active complaints for pagination
        cursor.execute("SELECT COUNT(*) FROM pensions_complains WHERE Status = %s", ("Closed",))
        total_complains = cursor.fetchone()[0]
        
        # Fetch the active complaints for the current page
        offset = (page - 1) * per_page
        cursor.execute(
            "SELECT * FROM pensions_complains WHERE Status = %s LIMIT %s OFFSET %s",
            ("Closed", per_page, offset)
        )
        data = cursor.fetchall()  # Fetch complaints for the current page
        cursor.execute("SELECT email FROM employee WHERE department = %s", ("Pensions",))
        emails = cursor.fetchall()  # Fetch all emails
        email_list = [email[0] for email in emails]  # Extract email addresses
        
        cursor.close()
        db.close()
    except mysql.connector.Error as err:
        print("Error connecting to database", err)
        return "Error connecting to database"
    
    total_pages = (total_complains + per_page - 1) // per_page  # Calculate total pages
    return render_template("pensions_admin_change_status_complain.html", data=data, items=email_list, page=page, total_pages=total_pages)

# Property admin menu button(active)

@app.route("/property_active_admin_list_complains", methods=['GET', 'POST'])
def property_active_admin_list_complains():
    try:
        page = request.args.get('page', 1, type=int)  # Get the current page number
        per_page = 4  # Number of items to display per page
        db = get_db_connection()
        cursor = db.cursor()
        # Fetch the total number of active complaints for pagination
        cursor.execute("SELECT COUNT(*) FROM property_complains WHERE Status = %s", ("Active",))
        total_complains = cursor.fetchone()[0]
        
        # Fetch the active complaints for the current page
        offset = (page - 1) * per_page
        cursor.execute(
            "SELECT * FROM property_complains WHERE Status = %s LIMIT %s OFFSET %s",
            ("Active", per_page, offset)
        )
        data = cursor.fetchall()  # Fetch complaints for the current page
        cursor.execute("SELECT email FROM employee WHERE department = %s", ("Property",))
        emails = cursor.fetchall()  # Fetch all emails
        email_list = [email[0] for email in emails]  # Extract email addresses
        
        cursor.close()
        db.close()
    except mysql.connector.Error as err:
        print("Error connecting to database", err)
        return "Error connecting to database"
    
    total_pages = (total_complains + per_page - 1) // per_page  # Calculate total pages
    return render_template("property_admin_change_status_complain.html", data=data, items=email_list, page=page, total_pages=total_pages)

# Property admin menu button (closed)
@app.route("/property_closed_admin_list_complains", methods=['GET', 'POST'])
def property_closed_admin_list_complains():
    try:
        page = request.args.get('page', 1, type=int)
        per_page = 4
        db = get_db_connection()
        cursor = db.cursor()
        cursor.execute("SELECT COUNT(*) FROM property_complains WHERE Status = %s", ("Closed",))
        total_complains = cursor.fetchone()[0]
        
        offset = (page - 1) * per_page
        cursor.execute(
            "SELECT * FROM property_complains WHERE Status = %s LIMIT %s OFFSET %s",
            ("Closed", per_page, offset)
        )
        data = cursor.fetchall()
        cursor.execute("SELECT email FROM employee WHERE department = %s", ("Property",))
        emails = cursor.fetchall()
        email_list = [email[0] for email in emails]
        
        cursor.close()
        db.close()
    except mysql.connector.Error as err:
        print("Error connecting to database", err)
        return "Error connecting to database"
    
    total_pages = (total_complains + per_page - 1) // per_page
    return render_template("property_admin_change_status_complain.html", data=data, items=email_list, page=page, total_pages=total_pages)

# Main Admin Lists
@app.route("/admin_list", methods= ["GET"])
def admin_list():
    try:
        db = get_db_connection()
        cursor = db.cursor()
        cursor.execute("SELECT * from administrators")
        data = cursor.fetchall()
        cursor.close()
        db.close()
    except mysql.connector.Error as err:
        print("error connecting to database", err)
        return "error connecting to database"
    
    return render_template("main_admin_list_supervisors.html",data=data)

@app.route("/employee_list", methods= ["GET"])
def employee_list():
    try:
        db = get_db_connection()
        cursor = db.cursor()
        cursor.execute("SELECT * from employee")
        data = cursor.fetchall()
        cursor.close()
        db.close()
    except mysql.connector.Error as err:
        print("error connecting to database", err)
        return "error connecting to database"
    
    return render_template("main_admin_list_employees.html", data=data)

@app.route('/all_complains', methods=['GET'])
def all_complains():
    try:
        db = get_db_connection()
        cursor = db.cursor()
        cursor.execute("SELECT * FROM complains")
        data = cursor.fetchall()
        cursor.close()
        db.close()
    except mysql.connector.Error as err:
        print("error connecting to database", err)
        return "error connecting to database"
    
    return render_template("main_admin_show_all_complains.html", data=data)

@app.route('/all_accounts_complains', methods=['GET'])
def all_accounts_complains():
    try:
        db = get_db_connection()
        cursor = db.cursor()
        cursor.execute("SELECT * FROM complains WHERE department=%s",("Accounts",))
        data = cursor.fetchall()
        cursor.close()
        db.close()
    except mysql.connector.Error as err:
        print("error connecting to database", err)
        return "error connecting to database"
    
    return render_template("main_admin_show_all_accounts_complains.html", data=data)

@app.route('/all_pensions_complains', methods=['GET'])
def all_pensions_complains():
    try:
        db = get_db_connection()
        cursor = db.cursor()
        cursor.execute("SELECT * FROM complains WHERE department=%s",("Pensions",))
        data = cursor.fetchall()
        cursor.close()
        db.close()
    except mysql.connector.Error as err:
        print("error connecting to database", err)
        return "error connecting to database"
    
    return render_template("main_admin_show_all_pensions_complains.html", data=data)

@app.route('/all_properties_complains', methods=['GET'])
def all_properties_complains():
    try:
        db = get_db_connection()
        cursor = db.cursor()
        cursor.execute("SELECT * FROM complains WHERE department=%s",("Property",))
        data = cursor.fetchall()
        cursor.close()
        db.close()
    except mysql.connector.Error as err:
        print("error connecting to database", err)
        return "error connecting to database"
    
    return render_template("main_admin_show_all_properties_complains.html", data=data)

# Password Reset Routes
@app.route("/admin_password_reset", methods= ["GET", "POST"])
def admin_password_reset():
    email=request.form["email"]
    db = get_db_connection()
    cursor = db.cursor()
    cursor.execute('SELECT * FROM administrators WHERE email = ?', (email,))
    user = cursor.fetchone()
    cursor.close()
    db.close()
    
    if user:
        return render_template("admin_new_password_change.html")
    else:
        flash("Email address is not registered.")
        return render_template("admin.login.html")

@app.route('/admin_request_reset_pin', methods=['GET', 'POST'])
def admin_request_reset_pin():
    if request.method == 'POST':
        email = request.form['email']
        db = get_db_connection()
        cursor = db.cursor()
        cursor.execute('SELECT * FROM administrators WHERE username = %s', (email,))
        user = cursor.fetchone()
        if user:
            characters = string.ascii_letters + string.digits + string.punctuation
            temp_password = ''.join(random.choice(characters) for _ in range(12))
            cursor.execute("UPDATE administrators SET temporary_password = %s WHERE username = %s", (temp_password, email))
            db.commit()
            
            msg = Message('PASSWORD RESET', sender='stevenchipunza1998@gmail.com', recipients=[email])
            msg.body = f" Your reset Password temporary pin is : {temp_password}"
            mail.send(msg)
            
            cursor.close()
            db.close()
            return render_template('admin_login.html')
        else:
            flash('Email address not found.')
            cursor.close()
            db.close()
    return render_template('index.html')

@app.route('/employee_request_reset_pin', methods=['GET', 'POST'])
def employee_request_reset_pin():
    if request.method == 'POST':
        email = request.form['email']
        db = get_db_connection()
        cursor = db.cursor()
        cursor.execute('SELECT * FROM employee WHERE email = %s', (email,))
        user = cursor.fetchone()
        if user:
            characters = string.ascii_letters + string.digits + string.punctuation
            temp_password = ''.join(random.choice(characters) for _ in range(12))
            cursor.execute("UPDATE employee SET temporary_password = %s WHERE email = %s", (temp_password, email))
            db.commit()
            
            msg = Message('Employee PASSWORD RESET', sender='stevenchipunza1998@gmail.com', recipients=[email])
            msg.body = f" Your reset Password temporary pin is : {temp_password}"
            mail.send(msg)
            
            cursor.close()
            db.close()
            return render_template('employee_login.html')
        else:
            flash('Email address not found.')
            cursor.close()
            db.close()
    return render_template('index.html')

# Complaint Details Routes (Property, Accounts, Pensions)
@app.route('/complain_details/<int:complain_id>')
def complain_details(complain_id):
    db = get_db_connection()
    cursor = db.cursor(dictionary=True)
    cursor.execute('SELECT complains_id AS complain_id, name, email, complain, phone_number, account_number, department, property_address, assigned, Status, assigned_comment, complain_date, complain_date_closed FROM property_complains WHERE complains_id = %s LIMIT 1', (complain_id,))
    
    full_data = cursor.fetchone()
    
    if full_data is None:
        cursor.close()
        db.close()
        abort(404, description="Complain not found")
    
    complain_dict = {
        'complain_id': full_data['complain_id'],
        'name': full_data['name'],
        'email': full_data['email'],
        'complain_text': full_data['complain'],
        'phone_number': full_data['phone_number'],
        'account_number': full_data['account_number'],
        'department': full_data['department'],
        'property_address': full_data['property_address'],
        'currently_assigned': full_data['assigned'],
        'status': full_data['Status'],
        'comments': full_data['assigned_comment'],
        'complain_date': full_data['complain_date'],
        'complain_date_closed': full_data['complain_date_closed']
    }
    
    cursor.execute("SELECT email FROM employee WHERE department = %s", ("Property",))
    emails = cursor.fetchall()
    email_list = [email['email'] for email in emails] if emails else []
        
    cursor.close()
    db.close()
    
    return render_template('admin_extended_view.html', complain=complain_dict, items=email_list)

@app.route('/admin_property_extended_view', methods=['POST'])
def admin_property_extended_view():
    currently_assigned = request.form["assigned_email"]
    status = request.form["status"]
    text_data = request.form['comment']
    data_id = request.form['data_id'] 
    try:
        db = get_db_connection()
        cursor = db.cursor()
        cursor.execute("SELECT assigned_comment FROM property_complains WHERE complains_id = %s", (data_id,))
        existing_text = cursor.fetchone()[0]
        if existing_text is None:
            updated_text = ' -  NAME:(  ' + session['username'] + ') - ' + text_data
        else:
            updated_text = existing_text + ' -  NAME:(  ' + session['username'] + ') - ' + text_data
                
        cursor.execute("UPDATE property_complains SET assigned_comment = %s WHERE complains_id = %s", (updated_text, data_id))
        cursor.execute("UPDATE property_complains SET status = %s WHERE complains_id = %s", (status, data_id))
        cursor.execute("UPDATE property_complains SET assigned = %s WHERE complains_id = %s", (currently_assigned, data_id))
        
        cursor.execute("SELECT department FROM property_complains WHERE complains_id = %s", (data_id,))
        department = cursor.fetchone()[0]
        
        if department == 'Property':
            cursor.execute("SELECT username FROM administrators WHERE department = %s;", (department,))
            email_list = cursor.fetchall()
            recipient = [email[0] for email in email_list]
            msg = Message("Comment Posted in the", sender=session['username'], recipients=recipient)
            msg.body = f"Complain comment has been posted into the system\n Employee: {session['username']} \n Complain Comment Conversation: {updated_text}"
            mail.send(msg)
        
        db.commit()
        cursor.close()
        db.close()
        flash("Text updated successfully")
        return redirect(url_for("property_admin_list_complains"))
    except mysql.connector.Error as err:
        print("Error updating comment", err)
        flash("error updating text")
        return redirect(url_for("property_admin_list_complains"))

@app.route('/accounts_complain_details/<int:complain_id>')
def accounts_complain_details(complain_id):
    db = get_db_connection()
    cursor = db.cursor(dictionary=True)
    cursor.execute('SELECT complains_id AS complain_id, name, email, complain, phone_number, account_number, department, assigned, Status, assigned_comment,complain_date, complain_date_close FROM accounts_complains WHERE complains_id = %s LIMIT 1', (complain_id,))
    
    full_data = cursor.fetchone()
    
    if full_data is None:
        cursor.close()
        db.close()
        abort(404, description="Complain not found")
    
    complain_dict = {
        'complain_id': full_data['complain_id'],
        'name': full_data['name'],
        'email': full_data['email'],
        'complain_text': full_data['complain'],
        'phone_number': full_data['phone_number'],
        'id_number': full_data['account_number'],
        'department': full_data['department'],
        'currently_assigned': full_data['assigned'],
        'status': full_data['Status'],
        'comments': full_data['assigned_comment'],
        'complain_date': full_data['complain_date'],
        'complain_date_close': full_data['complain_date_close']
    }
    cursor.execute("SELECT email FROM employee WHERE department = %s", ("Accounts",))
    emails = cursor.fetchall()
    email_list = [email['email'] for email in emails] if emails else []
            
    cursor.close()
    db.close()
    
    return render_template('accounts_admin_extended_view.html', complain=complain_dict, items=email_list)

@app.route('/admin_accounts_extended_view', methods=['POST'])
def admin_accounts_extended_view():
    currently_assigned= request.form["assigned_email"]
    status = request.form["status"]
    text_data= request.form['comment']
    data_id= request.form['data_id'] 
    try:
        db = get_db_connection()
        cursor = db.cursor()
        cursor.execute("SELECT assigned_comment FROM accounts_complains WHERE complains_id = %s", (data_id,))
        existing_text = cursor.fetchone()[0]
        if existing_text is None:
            updated_text = ' -  NAME:(  '+ session['username'] + ') - '+ text_data
        else:
            updated_text = existing_text + ' -  NAME:(  '+ session['username'] + ') - '+ text_data
                
        cursor.execute("UPDATE accounts_complains  SET assigned_comment =%s WHERE complains_id=%s",(updated_text, data_id))
        cursor.execute("UPDATE accounts_complains SET status= %s WHERE complains_id=%s",(status,data_id))
        cursor.execute("UPDATE accounts_complains SET assigned= %s WHERE complains_id=%s" ,(currently_assigned,data_id))
        
        cursor.execute("SELECT department FROM accounts_complains WHERE complains_id = %s",(data_id,))
        department=cursor.fetchone()[0]
        
        if department=='Accounts':
            cursor.execute("SELECT username FROM administrators WHERE department=%s;", (department,))
            email_list = cursor.fetchall()
            recipient = [email[0] for email in email_list]
            msg = Message("Comment Posted in the  ", sender= session['username'], recipients=recipient)
            msg.body =f"Complain comment as been posted into the system\n Employee: {session['username']} \n Complain Comment Conversation: {updated_text}"
            mail.send(msg)
        
        db.commit()
        cursor.close()
        db.close()
        flash("Text updated successfully")
        return redirect(url_for("accounting_admin_list_complains"))
    except mysql.connector.Error as err:
        print("Error updating comment", err)
        flash("error updating text")
        return redirect(url_for("accounting_admin_list_complains"))

@app.route('/pensions_complain_details/<int:complain_id>')
def pensions_complain_details(complain_id):
    db = get_db_connection()
    cursor = db.cursor(dictionary=True)
    cursor.execute('SELECT complains_id AS complain_id, name, email, complain, phone_number, id_number, department, assigned, Status, assigned_comment, complain_date, complain_date_close FROM pensions_complains WHERE complains_id = %s LIMIT 1', (complain_id,))
    
    full_data = cursor.fetchone()
    
    if full_data is None:
        cursor.close()
        db.close()
        abort(404, description="Complain not found")
    
    complain_dict = {
        'complain_id': full_data['complain_id'],
        'name': full_data['name'],
        'email': full_data['email'],
        'complain_text': full_data['complain'],
        'phone_number': full_data['phone_number'],
        'id_number': full_data['id_number'],
        'department': full_data['department'],
        'currently_assigned': full_data['assigned'],
        'status': full_data['Status'],
        'comments': full_data['assigned_comment'],
        'complain_date': full_data['complain_date'],
        'complain_date_close': full_data['complain_date_close']
    }
    cursor.execute("SELECT email FROM employee WHERE department = %s", ("Pensions",))
    emails = cursor.fetchall()
    email_list = [email['email'] for email in emails] if emails else []
            
    cursor.close()
    db.close()
    return render_template('pensions_admin_extended_view.html', complain=complain_dict, items=email_list)

@app.route('/admin_pensions_extended_view', methods=['POST'])
def admin_pensions_extended_view():
    currently_assigned = request.form["assigned_email"]
    status = request.form["status"]
    text_data = request.form['comment']
    data_id = request.form['data_id'] 
    try:
        db = get_db_connection()
        cursor = db.cursor()
        
        # Get existing comment
        cursor.execute("SELECT assigned_comment FROM pensions_complains WHERE complains_id = %s", (data_id,))
        existing_text = cursor.fetchone()[0]
        
        # Build updated comment
        if existing_text is None:
            updated_text = ' -  NAME:(  ' + session['username'] + ') - ' + text_data
        else:
            updated_text = existing_text + ' -  NAME:(  ' + session['username'] + ') - ' + text_data
        
        # Update the comment, status, and assigned fields
        cursor.execute("UPDATE pensions_complains SET assigned_comment = %s WHERE complains_id = %s", (updated_text, data_id))
        cursor.execute("UPDATE pensions_complains SET status = %s WHERE complains_id = %s", (status, data_id))
        cursor.execute("UPDATE pensions_complains SET assigned = %s WHERE complains_id = %s", (currently_assigned, data_id))
        
        # Get department
        cursor.execute("SELECT department FROM pensions_complains WHERE complains_id = %s", (data_id,))
        department = cursor.fetchone()[0]
        
        # Send email notification if department is Pensions
        if department == 'Pensions':
            cursor.execute("SELECT username FROM administrators WHERE department = %s;", (department,))
            email_list = cursor.fetchall()
            recipient = [email[0] for email in email_list]
            msg = Message("Comment Posted in the", sender=session['username'], recipients=recipient)
            msg.body = f"Complain comment has been posted into the system\n Employee: {session['username']} \n Complain Comment Conversation: {updated_text}"
            mail.send(msg)
        
        # Commit changes
        db.commit()
        
        # Close cursor and connection
        cursor.close()
        db.close()
        
        flash("Text updated successfully")
        return redirect(url_for("pensions_admin_list_complains"))
        
    except mysql.connector.Error as err:
        print("Error updating comment", err)
        flash("error updating text")
        return redirect(url_for("pensions_admin_list_complains"))


# accounts employee extended view 
@app.route('/accounts_employee_complain_details/<int:complain_id>')
def accounts_employee_complain_details(complain_id):
    db = get_db_connection()
    cursor = db.cursor(dictionary=True)
    cursor.execute('SELECT complains_id AS complain_id, name, email, complain, phone_number, account_number, department, assigned, Status, assigned_comment, complain_date, complain_date_close FROM accounts_complains WHERE complains_id = %s LIMIT 1', (complain_id,))

    full_data = cursor.fetchone()
    
    if full_data is None:
        cursor.close()
        db.close()
        abort(404, description="Complain not found")
    
    complain_dict = {
        'complain_id': full_data['complain_id'],
        'name': full_data['name'],
        'email': full_data['email'],
        'complain_text': full_data['complain'],
        'phone_number': full_data['phone_number'],
        'account_number': full_data['account_number'],
        'department': full_data['department'],
        'assigned': full_data['assigned'],
        'status': full_data['Status'],
        'comments': full_data['assigned_comment'],
        'complain_date': full_data['complain_date'],
        'complain_date_close': full_data['complain_date_close']
    }
    cursor.close()
    db.close()
    return render_template('accounts_employee_extended_view.html', complain=complain_dict)


@app.route('/employee_accounts_extended_view', methods=['POST'])
def employee_accounts_extended_view():
    currently_assigned = request.form["assigned_email"]
    text_data = request.form['comment']
    data_id = request.form['data_id']
    try:
        db = get_db_connection()
        cursor = db.cursor()
        cursor.execute("SELECT assigned_comment FROM accounts_complains WHERE complains_id = %s", (data_id,))
        existing_text = cursor.fetchone()[0]
        if existing_text is None:
            updated_text = ' -  NAME:(  ' + session['username'] + ') - ' + text_data
        else:
            updated_text = existing_text + ' -  NAME:(  ' + session['username'] + ') - ' + text_data
            
        cursor.execute("UPDATE accounts_complains SET assigned_comment = %s WHERE complains_id = %s", (updated_text, data_id))
        cursor.execute("UPDATE accounts_complains SET assigned = %s WHERE complains_id = %s", (currently_assigned, data_id))
        
        cursor.execute("SELECT department FROM accounts_complains WHERE complains_id = %s", (data_id,))
        department = cursor.fetchone()[0]
        
        if department == 'Accounts':
            cursor.execute("SELECT username FROM administrators WHERE department = %s;", (department,))
            email_list = cursor.fetchall()
            recipient = [email[0] for email in email_list]
            msg = Message("Comment Posted in the", sender=session['username'], recipients=recipient)
            msg.body = f"Complain comment has been posted into the system\n Employee: {session['username']} \n Complain Comment Conversation: {updated_text}"
            mail.send(msg)
        
        db.commit()
        cursor.close()
        db.close()
        flash("Text updated successfully")
        return redirect(url_for("pensions_employee_list_complains"))
    except mysql.connector.Error as err:
        print("Error updating comment", err)
        flash("error updating text")
        return redirect(url_for("pensions_employee_list_complains"))

# pensions employee extended view 
@app.route('/pensions_employee_complain_details/<int:complain_id>')
def pensions_employee_complain_details(complain_id):
    db = get_db_connection()
    cursor = db.cursor(dictionary=True)
    cursor.execute('SELECT complains_id AS complain_id, name, email, complain, phone_number, id_number, department, assigned, Status, assigned_comment, complain_date, complain_date_close FROM pensions_complains WHERE complains_id = %s LIMIT 1', (complain_id,))

    full_data = cursor.fetchone()
    
    if full_data is None:
        cursor.close()
        db.close()
        abort(404, description="Complain not found")
    
    complain_dict = {
        'complain_id': full_data['complain_id'],
        'name': full_data['name'],
        'email': full_data['email'],
        'complain_text': full_data['complain'],
        'phone_number': full_data['phone_number'],
        'id_number': full_data['id_number'],
        'department': full_data['department'],
        'assigned': full_data['assigned'],
        'status': full_data['Status'],
        'comments': full_data['assigned_comment'],
        'complain_date': full_data['complain_date'],
        'complain_date_close': full_data['complain_date_close']
    }
    cursor.close()
    db.close()
    return render_template('pensions_employee_extended_view.html', complain=complain_dict)

@app.route('/employee_pensions_extended_view', methods=['POST'])
def employee_pensions_extended_view():
    currently_assigned = request.form["assigned_email"]
    text_data = request.form['comment']
    data_id = request.form['data_id']
    try:
        db = get_db_connection()
        cursor = db.cursor()
        cursor.execute("SELECT assigned_comment FROM pensions_complains WHERE complains_id = %s", (data_id,))
        existing_text = cursor.fetchone()[0]
        if existing_text is None:
            updated_text = ' -  NAME:(  ' + session['username'] + ') - ' + text_data
        else:
            updated_text = existing_text + ' -  NAME:(  ' + session['username'] + ') - ' + text_data
            
        cursor.execute("UPDATE pensions_complains SET assigned_comment = %s WHERE complains_id = %s", (updated_text, data_id))
        cursor.execute("UPDATE pensions_complains SET assigned = %s WHERE complains_id = %s", (currently_assigned, data_id))
        
        cursor.execute("SELECT department FROM pensions_complains WHERE complains_id = %s", (data_id,))
        department = cursor.fetchone()[0]
        
        if department == 'Pensions':
            cursor.execute("SELECT username FROM administrators WHERE department = %s;", (department,))
            email_list = cursor.fetchall()
            recipient = [email[0] for email in email_list]
            msg = Message("Comment Posted in the", sender=session['username'], recipients=recipient)
            msg.body = f"Complain comment has been posted into the system\n Employee: {session['username']} \n Complain Comment Conversation: {updated_text}"
            mail.send(msg)
        
        db.commit()
        cursor.close()
        db.close()
        flash("Text updated successfully")
        return redirect(url_for("pensions_employee_list_complains"))
    except mysql.connector.Error as err:
        print("Error updating comment", err)
        flash("error updating text")
        return redirect(url_for("pensions_employee_list_complains"))

# property employee extended view 
@app.route('/property_employee_complain_details/<int:complain_id>')
def property_employee_complain_details(complain_id):
    db = get_db_connection()
    cursor = db.cursor(dictionary=True)
    cursor.execute('SELECT complains_id AS complain_id, name, email, complain, phone_number, account_number, department, property_address, assigned, Status, assigned_comment, complain_date, complain_date_closed FROM property_complains WHERE complains_id = %s LIMIT 1', (complain_id,))

    full_data = cursor.fetchone()
    
    if full_data is None:
        cursor.close()
        db.close()
        abort(404, description="Complain not found")
    
    complain_dict = {
        'complain_id': full_data['complain_id'],
        'name': full_data['name'],
        'email': full_data['email'],
        'complain_text': full_data['complain'],
        'phone_number': full_data['phone_number'],
        'account_number': full_data['account_number'],
        'department': full_data['department'],
        'property_address': full_data['property_address'],
        'assigned': full_data['assigned'],
        'status': full_data['Status'],
        'comments': full_data['assigned_comment'],
        'complain_date': full_data['complain_date'],
        'complain_date_closed': full_data['complain_date_closed']
    }
    cursor.close()
    db.close()
    return render_template('property_employee_extended_view.html', complain=complain_dict)


@app.route('/employee_property_extended_view', methods=['POST'])
def employee_property_extended_view():
    currently_assigned = request.form["assigned_email"]
    text_data = request.form['comment']
    data_id = request.form['data_id']
    try:
        db = get_db_connection()
        cursor = db.cursor()
        cursor.execute("SELECT assigned_comment FROM property_complains WHERE complains_id = %s", (data_id,))
        existing_text = cursor.fetchone()[0]
        if existing_text is None:
            updated_text = ' -  NAME:(  ' + session['username'] + ') - ' + text_data
        else:
            updated_text = existing_text + ' -  NAME:(  ' + session['username'] + ') - ' + text_data
            
        cursor.execute("UPDATE property_complains SET assigned_comment = %s WHERE complains_id = %s", (updated_text, data_id))
        cursor.execute("UPDATE property_complains SET assigned = %s WHERE complains_id = %s", (currently_assigned, data_id))
    
        cursor.execute("SELECT department FROM property_complains WHERE complains_id = %s", (data_id,))
        department = cursor.fetchone()[0]
    
        if department == 'Property':
            cursor.execute("SELECT username FROM administrators WHERE department = %s;", (department,))
            email_list = cursor.fetchall()
            recipient = [email[0] for email in email_list]
            msg = Message("Comment Posted in the", sender=session['username'], recipients=recipient)
            msg.body = f"Complain comment has been posted into the system\n Employee: {session['username']} \n Complain Comment Conversation: {updated_text}"
            mail.send(msg)
    
        db.commit()
        cursor.close()
        db.close()
        flash("Text updated successfully")
        return redirect(url_for("property_employee_list_complains"))
    except mysql.connector.Error as err:
        print("Error updating comment", err)
        flash("error updating text")
        return redirect(url_for("property_employee_list_complains"))

# Accounts admin main page complains 
@app.route('/accounting_admin_main_page', methods=['GET'])
def accounting_admin_main_page():
    return render_template("accounting_admin_main.html")

# Admin accounts after update
@app.route('/accounts_admin_list_complains_after_update')
def accounts_admin_list_complains_after_update():
    return redirect(url_for('accounting_admin_list_complains'))

# Admin pensions after update 
@app.route('/pension_admin_list_complains_after_update')
def pension_admin_list_complains_after_update():
    return redirect(url_for('pensions_admin_list_complains'))

# admin property after update 
@app.route('/property_admin_list_complains_after_update')
def property_admin_list_complains_after_update():
    return redirect(url_for('property_admin_list_complains'))

@app.route('/', methods=['GET'])
def index():
    return render_template('index.html')

@app.route('/Open_Suggestion_Form', methods=['GET'])
def Open_Suggestion_Form():
    return render_template('suggestion_form.html')

@app.route('/Complain_Form', methods=['GET'])
def Complain_Form():
    return render_template('complain_form.html')

@app.route('/back_to_home')
def back_to_home():
    return render_template('index.html')

@app.route('/error_delayed_index')
def error_delayed_index():
    return render_template('error_delayed_index.html')

@app.route('/delayed_index')
def delayed_index():
    return render_template("delayed_index.html")

@app.route('/wrong_password')
def wrong_password():
    return render_template("wrong_password.html")

@app.route('/open_admin_login_form', methods=['GET'])
def open_admin_login_form():
    return render_template("admin_login.html")

@app.route('/login', methods=['GET','POST'])
def login():
    return render_template("admin_login.html")

@app.route('/logout')
def logout():
    return render_template('index.html')

@app.route('/employee_login', methods =["GET","POST"])
def employee_login():
    return render_template("employee_login.html") 

@app.route("/main_admin", methods=["GET"])
def main_admin():
    return render_template("main_admin.html")

@app.route('/add_supervisor_form', methods=["GET"])
def add_supervisor_form():
    return render_template("create_supervisor.html")

@app.route('/add_employee_form', methods= ["GET"])
def add_employee_form():
    return render_template("create_employee.html")

# admin password reset 
@app.route('/admin_reset_password_form', methods=['GET','POST'])
def admin_reset_password_form():
    return render_template('admin_password_reset.html') 

# employee password reset  
@app.route('/employee_reset_password_form', methods=['GET','POST'])
def employee_reset_password_form():
    return render_template('employee_password_reset.html') 

@app.route('/accounts_email_drop_down_list')
def accounts_email_drop_down_list():
    try:
        db = get_db_connection()
        cursor = db.cursor()
        cursor.execute("SELECT email FROM employee WHERE department=%s;", ("Accounts",))
        emails = cursor.fetchall()
        cursor.close()
        db.close()
    except mysql.connector.Error as err:
        print("Error fetching data from database:", err)
        emails = []
    return emails


# Reports routes 
from flask import send_file
import io
import pandas as pd
from datetime import datetime, timedelta


# Property Admin Reports Route (Form and Generate)
@app.route('/property_admin_report', methods=['GET', 'POST'])
def property_admin_report():
    if request.method == 'POST':
        report_type = request.form.get('report_type')
        start_date = None
        end_date = None
        
        if report_type == 'weekly':
            end_date = datetime.now()
            start_date = end_date - timedelta(days=7)
        elif report_type == 'monthly':
            end_date = datetime.now()
            start_date = end_date - timedelta(days=30)
        elif report_type == 'custom':
            start_date_str = request.form.get('start_date')
            end_date_str = request.form.get('end_date')
            if not start_date_str or not end_date_str:
                flash("Please provide both start and end dates for custom reports.")
                return redirect(url_for('property_admin_report'))
            try:
                start_date = datetime.strptime(start_date_str, '%Y-%m-%d')
                end_date = datetime.strptime(end_date_str, '%Y-%m-%d')
                if start_date > end_date:
                    flash("Start date must be before or equal to end date.")
                    return redirect(url_for('property_admin_report'))
            except ValueError:
                flash("Invalid date format. Use YYYY-MM-DD.")
                return redirect(url_for('property_admin_report'))
        else:
            flash("Invalid report type selected.")
            return redirect(url_for('property_admin_report'))
        
        try:
            db = get_db_connection()
            cursor = db.cursor(dictionary=True)
            cursor.execute("""
                SELECT complains_id, name, email, phone_number, complain, department, 
                       account_number, property_address, assigned, assigned_comment, 
                       complain_date, complain_date_closed, Status 
                FROM property_complains 
                WHERE complain_date BETWEEN %s AND %s AND department = %s
            """, (start_date, end_date, 'Property'))
            data = cursor.fetchall()
            cursor.close()
            db.close()
        except mysql.connector.Error as err:
            flash(f"Database error: {err}")
            return redirect(url_for('property_admin_report'))
        
        if not data:
            flash("No complains found for the selected date range.")
            return redirect(url_for('property_admin_report'))
        
        session['report_data'] = data
        session['report_type'] = report_type
        return render_template('property_admin_report_view.html', data=data, report_type=report_type)
    
    return render_template('property_admin_report.html')


# Download the Excel Report
@app.route('/download_property_report')
def download_property_report():
    data = session.get('report_data')
    
    if not data:
        flash("No report data available. Please generate the report first.")
        return redirect(url_for('property_admin_report'))
    
    for row in data:
        if row.get('complain_date'):
            row['complain_date'] = pd.to_datetime(row['complain_date']).tz_localize(None)
        if row.get('complain_date_closed'):
            row['complain_date_closed'] = pd.to_datetime(row['complain_date_closed']).tz_localize(None)
    
    df = pd.DataFrame(data, columns=['complains_id', 'name', 'email', 'phone_number', 'complain', 'department', 
                                     'account_number', 'property_address', 'assigned', 'assigned_comment', 
                                     'complain_date', 'complain_date_closed', 'Status'])
    df.columns = ['Complains ID', 'Name', 'Email', 'Phone Number', 'Complain', 'Department', 
                  'Account Number', 'Property Address', 'Assigned', 'Assigned Comment', 
                  'Complain Date', 'Complain Date Closed', 'Status']
    
    output = io.BytesIO()
    try:
        with pd.ExcelWriter(output, engine='openpyxl') as writer:
            df.to_excel(writer, sheet_name='Property Complains Report', index=False)
        output.seek(0)
    except Exception as e:
        flash("Error generating Excel file. Please try again.")
        return redirect(url_for('property_admin_report'))
    
    session.pop('report_data', None)
    session.pop('report_type', None)
    
    return send_file(output, as_attachment=True, download_name='property_complains_report.xlsx', 
                     mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')


# Accounts Admin Reports Route
@app.route('/accounts_admin_report', methods=['GET', 'POST'])
def accounts_admin_report():
    if request.method == 'POST':
        report_type = request.form.get('report_type')
        start_date = None
        end_date = None
        
        if report_type == 'weekly':
            end_date = datetime.now()
            start_date = end_date - timedelta(days=7)
        elif report_type == 'monthly':
            end_date = datetime.now()
            start_date = end_date - timedelta(days=30)
        elif report_type == 'custom':
            start_date_str = request.form.get('start_date')
            end_date_str = request.form.get('end_date')
            if not start_date_str or not end_date_str:
                flash("Please provide both start and end dates for custom reports.")
                return redirect(url_for('accounts_admin_report'))
            try:
                start_date = datetime.strptime(start_date_str, '%Y-%m-%d')
                end_date = datetime.strptime(end_date_str, '%Y-%m-%d')
                if start_date > end_date:
                    flash("Start date must be before or equal to end date.")
                    return redirect(url_for('accounts_admin_report'))
            except ValueError:
                flash("Invalid date format. Use YYYY-MM-DD.")
                return redirect(url_for('accounts_admin_report'))
        else:
            flash("Invalid report type selected.")
            return redirect(url_for('accounts_admin_report'))
        
        try:
            db = get_db_connection()
            cursor = db.cursor(dictionary=True)
            cursor.execute("""
                SELECT complains_id, name, email, phone_number, complain, department, 
                       account_number, assigned, assigned_comment, 
                       complain_date, complain_date_close, Status 
                FROM accounts_complains 
                WHERE complain_date BETWEEN %s AND %s AND department = %s
            """, (start_date, end_date, 'Accounts'))
            data = cursor.fetchall()
            cursor.close()
            db.close()
        except mysql.connector.Error as err:
            flash(f"Database error: {err}")
            return redirect(url_for('accounts_admin_report'))
        
        if not data:
            flash("No complains found for the selected date range.")
            return redirect(url_for('accounts_admin_report'))
        
        session['accounts_report_data'] = data
        session['accounts_report_type'] = report_type
        return render_template('accounts_admin_report_view.html', data=data, report_type=report_type)
    
    return render_template('accounts_admin_report.html')


# Download the Accounts Excel Report
@app.route('/download_accounts_report')
def download_accounts_report():
    data = session.get('accounts_report_data')
    
    if not data:
        flash("No report data available. Please generate the report first.")
        return redirect(url_for('accounts_admin_report'))
    
    for row in data:
        if row.get('complain_date'):
            row['complain_date'] = pd.to_datetime(row['complain_date']).tz_localize(None)
        if row.get('complain_date_close'):
            row['complain_date_close'] = pd.to_datetime(row['complain_date_close']).tz_localize(None)
    
    df = pd.DataFrame(data, columns=['complains_id', 'name', 'email', 'phone_number', 'complain', 'department', 
                                     'account_number', 'assigned', 'assigned_comment', 
                                     'complain_date', 'complain_date_close', 'Status'])
    df.columns = ['Complains ID', 'Name', 'Email', 'Phone Number', 'Complain', 'Department', 
                  'Account Number', 'Assigned', 'Assigned Comment', 
                  'Complain Date', 'Complain Date Closed', 'Status']
    
    output = io.BytesIO()
    try:
        with pd.ExcelWriter(output, engine='openpyxl') as writer:
            df.to_excel(writer, sheet_name='Accounts Complains Report', index=False)
        output.seek(0)
    except Exception as e:
        flash("Error generating Excel file. Please try again.")
        return redirect(url_for('accounts_admin_report'))
    
    session.pop('accounts_report_data', None)
    session.pop('accounts_report_type', None)
    
    return send_file(output, as_attachment=True, download_name='accounts_complains_report.xlsx', 
                     mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')


# Pensions Admin Reports Route
@app.route('/pensions_admin_report', methods=['GET', 'POST'])
def pensions_admin_report():
    if request.method == 'POST':
        report_type = request.form.get('report_type')
        start_date = None
        end_date = None
        
        if report_type == 'weekly':
            end_date = datetime.now()
            start_date = end_date - timedelta(days=7)
        elif report_type == 'monthly':
            end_date = datetime.now()
            start_date = end_date - timedelta(days=30)
        elif report_type == 'custom':
            start_date_str = request.form.get('start_date')
            end_date_str = request.form.get('end_date')
            if not start_date_str or not end_date_str:
                flash("Please provide both start and end dates for custom reports.")
                return redirect(url_for('pensions_admin_report'))
            try:
                start_date = datetime.strptime(start_date_str, '%Y-%m-%d')
                end_date = datetime.strptime(end_date_str, '%Y-%m-%d')
                if start_date > end_date:
                    flash("Start date must be before or equal to end date.")
                    return redirect(url_for('pensions_admin_report'))
            except ValueError:
                flash("Invalid date format. Use YYYY-MM-DD.")
                return redirect(url_for('pensions_admin_report'))
        else:
            flash("Invalid report type selected.")
            return redirect(url_for('pensions_admin_report'))
        
        try:
            db = get_db_connection()
            cursor = db.cursor(dictionary=True)
            cursor.execute("""
                SELECT complains_id, name, email, phone_number, complain, department, 
                       id_number, assigned, assigned_comment, 
                       complain_date, complain_date_close, Status 
                FROM pensions_complains 
                WHERE complain_date BETWEEN %s AND %s AND department = %s
            """, (start_date, end_date, 'Pensions'))
            data = cursor.fetchall()
            cursor.close()
            db.close()
        except mysql.connector.Error as err:
            flash(f"Database error: {err}")
            return redirect(url_for('pensions_admin_report'))
        
        if not data:
            flash("No complains found for the selected date range.")
            return redirect(url_for('pensions_admin_report'))
        
        session['pensions_report_data'] = data
        session['pensions_report_type'] = report_type
        return render_template('pensions_admin_report_view.html', data=data, report_type=report_type)
    
    return render_template('pensions_admin_report.html')


# Download the Pensions Excel Report
@app.route('/download_pensions_report')
def download_pensions_report():
    data = session.get('pensions_report_data')
    
    if not data:
        flash("No report data available. Please generate the report first.")
        return redirect(url_for('pensions_admin_report'))
    
    for row in data:
        if row.get('complain_date'):
            row['complain_date'] = pd.to_datetime(row['complain_date']).tz_localize(None)
        if row.get('complain_date_close'):
            row['complain_date_close'] = pd.to_datetime(row['complain_date_close']).tz_localize(None)
    
    df = pd.DataFrame(data, columns=['complains_id', 'name', 'email', 'phone_number', 'complain', 'department', 
                                     'id_number', 'assigned', 'assigned_comment', 
                                     'complain_date', 'complain_date_close', 'Status'])
    df.columns = ['Complains ID', 'Name', 'Email', 'Phone Number', 'Complain', 'Department', 
                  'ID Number', 'Assigned', 'Assigned Comment', 
                  'Complain Date', 'Complain Date Closed', 'Status']
    
    output = io.BytesIO()
    try:
        with pd.ExcelWriter(output, engine='openpyxl') as writer:
            df.to_excel(writer, sheet_name='Pensions Complains Report', index=False)
        output.seek(0)
    except Exception as e:
        flash("Error generating Excel file. Please try again.")
        return redirect(url_for('pensions_admin_report'))
    
    session.pop('pensions_report_data', None)
    session.pop('pensions_report_type', None)
    
    return send_file(output, as_attachment=True, download_name='pensions_complains_report.xlsx', 
                     mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')


# RECEPTION FUNCTIONS 
# Reception list all complains (view all) - Now with filtering
@app.route("/reception_list_complains", methods=['GET', 'POST'])
def reception_list_complains():
    page = request.args.get('page', 1, type=int)
    per_page = 4
    offset = (page - 1) * per_page
    
    department_filter = request.args.get('department', 'all')
    status_filter = request.args.get('status', 'all')
    
    try:
        db = get_db_connection()
        cursor = db.cursor()
        
        query_parts = []
        if department_filter == 'all':
            query_parts.append("""
                SELECT CONCAT('P-', complains_id) AS prefixed_id, name, email, phone_number, complain, department, 
                       id_number AS identifier, assigned, assigned_comment, complain_date, complain_date_close AS complain_date_closed, Status
                FROM pensions_complains
            """)
            query_parts.append("""
                SELECT CONCAT('A-', complains_id), name, email, phone_number, complain, department, 
                       account_number, assigned, assigned_comment, complain_date, complain_date_close AS complain_date_closed, Status
                FROM accounts_complains
            """)
            query_parts.append("""
                SELECT CONCAT('Pr-', complains_id), name, email, phone_number, complain, department, 
                       account_number, assigned, assigned_comment, complain_date, complain_date_closed AS complain_date_closed, Status
                FROM property_complains
            """)
            base_query = " UNION ALL ".join(query_parts)
        else:
            if department_filter == 'Pensions':
                table = 'pensions_complains'
                id_field = 'id_number'
                prefix = 'P-'
                date_field = 'complain_date_close'
            elif department_filter == 'Accounts':
                table = 'accounts_complains'
                id_field = 'account_number'
                prefix = 'A-'
                date_field = 'complain_date_close'
            elif department_filter == 'Property':
                table = 'property_complains'
                id_field = 'account_number'
                prefix = 'Pr-'
                date_field = 'complain_date_closed'
            else:
                return "Invalid department filter", 400
            
            base_query = f"""
                SELECT CONCAT('{prefix}', complains_id) AS prefixed_id, name, email, phone_number, complain, department, 
                       {id_field} AS identifier, assigned, assigned_comment, complain_date, {date_field} AS complain_date_closed, Status
                FROM {table}
            """
        
        if status_filter != 'all':
            if department_filter == 'all':
                base_query = base_query.replace("FROM pensions_complains", f"FROM pensions_complains WHERE Status = '{status_filter}'")
                base_query = base_query.replace("FROM accounts_complains", f"FROM accounts_complains WHERE Status = '{status_filter}'")
                base_query = base_query.replace("FROM property_complains", f"FROM property_complains WHERE Status = '{status_filter}'")
            else:
                base_query += f" WHERE Status = '{status_filter}'"
        
        full_query = f"{base_query} ORDER BY complain_date DESC LIMIT %s OFFSET %s"
        cursor.execute(full_query, (per_page, offset))
        data = cursor.fetchall()
        
        if department_filter == 'all':
            count_query = """
                SELECT COUNT(*) FROM (
                    SELECT complains_id FROM pensions_complains
                    UNION ALL
                    SELECT complains_id FROM accounts_complains
                    UNION ALL
                    SELECT complains_id FROM property_complains
                ) AS combined
            """
            if status_filter != 'all':
                count_query = count_query.replace("FROM pensions_complains", f"FROM pensions_complains WHERE Status = '{status_filter}'")
                count_query = count_query.replace("FROM accounts_complains", f"FROM accounts_complains WHERE Status = '{status_filter}'")
                count_query = count_query.replace("FROM property_complains", f"FROM property_complains WHERE Status = '{status_filter}'")
        else:
            count_query = f"SELECT COUNT(*) FROM {table}"
            if status_filter != 'all':
                count_query += f" WHERE Status = '{status_filter}'"
        
        cursor.execute(count_query)
        total_complains = cursor.fetchone()[0]
        
        cursor.close()
        db.close()
    
    except mysql.connector.Error as err:
        print("Error connecting to database", err)
        return "Error connecting to database"
    
    total_pages = (total_complains + per_page - 1) // per_page
    return render_template("reception_complains_list.html", data=data, page=page, total_pages=total_pages, 
                           department=department_filter, status=status_filter)


# Reception active complains
@app.route("/reception_active_list_complains", methods=['GET', 'POST'])
def reception_active_list_complains():
    page = request.args.get('page', 1, type=int)
    per_page = 4
    offset = (page - 1) * per_page

    try:
        db = get_db_connection()
        cursor = db.cursor()
        cursor.execute("""
            SELECT CONCAT('P-', complains_id) AS prefixed_id, name, email, phone_number, complain, department, 
                   id_number AS identifier, assigned, assigned_comment, complain_date, complain_date_close AS complain_date_closed, Status
            FROM pensions_complains WHERE Status = 'Active'
            UNION ALL
            SELECT CONCAT('A-', complains_id), name, email, phone_number, complain, department, 
                   account_number, assigned, assigned_comment, complain_date, complain_date_close AS complain_date_closed, Status
            FROM accounts_complains WHERE Status = 'Active'
            UNION ALL
            SELECT CONCAT('Pr-', complains_id), name, email, phone_number, complain, department, 
                   account_number, assigned, assigned_comment, complain_date, complain_date_closed AS complain_date_closed, Status
            FROM property_complains WHERE Status = 'Active'
            ORDER BY complain_date DESC
            LIMIT %s OFFSET %s
        """, (per_page, offset))
        data = cursor.fetchall()

        cursor.execute("""
            SELECT COUNT(*) FROM (
                SELECT complains_id FROM pensions_complains WHERE Status = 'Active'
                UNION ALL
                SELECT complains_id FROM accounts_complains WHERE Status = 'Active'
                UNION ALL
                SELECT complains_id FROM property_complains WHERE Status = 'Active'
            ) AS combined
        """)
        total_complains = cursor.fetchone()[0]
        
        cursor.close()
        db.close()

    except mysql.connector.Error as err:
        print("Error connecting to database", err)
        return "Error connecting to database"
    
    total_pages = (total_complains + per_page - 1) // per_page
    return render_template("reception_complains_list.html", data=data, page=page, total_pages=total_pages, filter='active')


# Reception closed complains
@app.route("/reception_closed_list_complains", methods=['GET', 'POST'])
def reception_closed_list_complains():
    page = request.args.get('page', 1, type=int)
    per_page = 4
    offset = (page - 1) * per_page

    try:
        db = get_db_connection()
        cursor = db.cursor()
        cursor.execute("""
            SELECT CONCAT('P-', complains_id) AS prefixed_id, name, email, phone_number, complain, department, 
                   id_number AS identifier, assigned, assigned_comment, complain_date, complain_date_close AS complain_date_closed, Status
            FROM pensions_complains WHERE Status = 'Closed'
            UNION ALL
            SELECT CONCAT('A-', complains_id), name, email, phone_number, complain, department, 
                   account_number, assigned, assigned_comment, complain_date, complain_date_close AS complain_date_closed, Status
            FROM accounts_complains WHERE Status = 'Closed'
            UNION ALL
            SELECT CONCAT('Pr-', complains_id), name, email, phone_number, complain, department, 
                   account_number, assigned, assigned_comment, complain_date, complain_date_closed AS complain_date_closed, Status
            FROM property_complains WHERE Status = 'Closed'
            ORDER BY complain_date DESC
            LIMIT %s OFFSET %s
        """, (per_page, offset))
        data = cursor.fetchall()

        cursor.execute("""
            SELECT COUNT(*) FROM (
                SELECT complains_id FROM pensions_complains WHERE Status = 'Closed'
                UNION ALL
                SELECT complains_id FROM accounts_complains WHERE Status = 'Closed'
                UNION ALL
                SELECT complains_id FROM property_complains WHERE Status = 'Closed'
            ) AS combined
        """)
        total_complains = cursor.fetchone()[0]
        
        cursor.close()
        db.close()

    except mysql.connector.Error as err:
        print("Error connecting to database", err)
        return "Error connecting to database"
    
    total_pages = (total_complains + per_page - 1) // per_page
    return render_template("reception_complains_list.html", data=data, page=page, total_pages=total_pages, filter='closed')


# Reception Pensions complains
@app.route("/reception_pensions_list_complains", methods=['GET', 'POST'])
def reception_pensions_list_complains():
    page = request.args.get('page', 1, type=int)
    per_page = 4
    offset = (page - 1) * per_page

    try:
        db = get_db_connection()
        cursor = db.cursor()
        cursor.execute("""
            SELECT CONCAT('P-', complains_id) AS prefixed_id, name, email, phone_number, complain, department, 
                   id_number AS identifier, assigned, assigned_comment, complain_date, complain_date_close AS complain_date_closed, Status
            FROM pensions_complains
            ORDER BY complain_date DESC
            LIMIT %s OFFSET %s
        """, (per_page, offset))
        data = cursor.fetchall()

        cursor.execute("SELECT COUNT(*) FROM pensions_complains")
        total_complains = cursor.fetchone()[0]
        
        cursor.close()
        db.close()

    except mysql.connector.Error as err:
        print("Error connecting to database", err)
        return "Error connecting to database"
    
    total_pages = (total_complains + per_page - 1) // per_page
    return render_template("reception_complains_list.html", data=data, page=page, total_pages=total_pages, filter='pensions')


# Reception Accounts complains
@app.route("/reception_accounts_list_complains", methods=['GET', 'POST'])
def reception_accounts_list_complains():
    page = request.args.get('page', 1, type=int)
    per_page = 4
    offset = (page - 1) * per_page

    try:
        db = get_db_connection()
        cursor = db.cursor()
        cursor.execute("""
            SELECT CONCAT('A-', complains_id) AS prefixed_id, name, email, phone_number, complain, department, 
                   account_number AS identifier, assigned, assigned_comment, complain_date, complain_date_close AS complain_date_closed, Status
            FROM accounts_complains
            ORDER BY complain_date DESC
            LIMIT %s OFFSET %s
        """, (per_page, offset))
        data = cursor.fetchall()

        cursor.execute("SELECT COUNT(*) FROM accounts_complains")
        total_complains = cursor.fetchone()[0]
        
        cursor.close()
        db.close()

    except mysql.connector.Error as err:
        print("Error connecting to database", err)
        return "Error connecting to database"
    
    total_pages = (total_complains + per_page - 1) // per_page
    return render_template("reception_complains_list.html", data=data, page=page, total_pages=total_pages, filter='accounts')


# Reception Property complains
@app.route("/reception_property_list_complains", methods=['GET', 'POST'])
def reception_property_list_complains():
    page = request.args.get('page', 1, type=int)
    per_page = 4
    offset = (page - 1) * per_page

    try:
        db = get_db_connection()
        cursor = db.cursor()
        cursor.execute("""
            SELECT CONCAT('Pr-', complains_id) AS prefixed_id, name, email, phone_number, complain, department, 
                   account_number AS identifier, assigned, assigned_comment, complain_date, complain_date_closed AS complain_date_closed, Status
            FROM property_complains
            ORDER BY complain_date DESC
            LIMIT %s OFFSET %s
        """, (per_page, offset))
        data = cursor.fetchall()

        cursor.execute("SELECT COUNT(*) FROM property_complains")
        total_complains = cursor.fetchone()[0]
        
        cursor.close()
        db.close()

    except mysql.connector.Error as err:
        print("Error connecting to database", err)
        return "Error connecting to database"
    
    total_pages = (total_complains + per_page - 1) // per_page
    return render_template("reception_complains_list.html", data=data, page=page, total_pages=total_pages, filter='property')


# Reception complain details
@app.route('/reception_complain_details/<string:prefixed_id>')
def reception_complain_details(prefixed_id):
    if prefixed_id.startswith('P-'):
        table = 'pensions_complains'
        complain_id = int(prefixed_id[2:])
        id_field = 'id_number'
        date_field = 'complain_date_close'
    elif prefixed_id.startswith('A-'):
        table = 'accounts_complains'
        complain_id = int(prefixed_id[2:])
        id_field = 'account_number'
        date_field = 'complain_date_close'
    elif prefixed_id.startswith('Pr-'):
        table = 'property_complains'
        complain_id = int(prefixed_id[3:])
        id_field = 'account_number'
        date_field = 'complain_date_closed'
    else:
        abort(404, description="Invalid complain ID")

    db = get_db_connection()
    cursor = db.cursor(dictionary=True)
    cursor.execute(f'SELECT complains_id AS complain_id, name, email, complain, phone_number, {id_field} AS identifier, department, assigned, Status, assigned_comment, complain_date, {date_field} AS complain_date_closed FROM {table} WHERE complains_id = %s LIMIT 1', (complain_id,))
    full_data = cursor.fetchone()
    
    if full_data is None:
        cursor.close()
        db.close()
        abort(404, description="Complain not found")
    
    complain_dict = {
        'complain_id': prefixed_id,
        'name': full_data['name'],
        'email': full_data['email'],
        'complain_text': full_data['complain'],
        'phone_number': full_data['phone_number'],
        'identifier': full_data['identifier'],
        'department': full_data['department'],
        'assigned': full_data['assigned'],
        'status': full_data['Status'],
        'comments': full_data['assigned_comment'],
        'complain_date': full_data['complain_date'],
        'complain_date_closed': full_data['complain_date_closed']
    }
    cursor.close()
    db.close()
    return render_template('reception_complain_details.html', complain=complain_dict)

"""
if __name__ == '__main__':
    port = int(os.environ.get("PORT", 5000))  # Use 5002 as default, or override with env var
    serve(app, host="0.0.0.0", port=port, threads=4,  # Production-ready with threading
            access_logfile='access.log', error_logfile='error.log')  # Logs for monitoring
    
"""    

if __name__ == '__main__':
    port = int(os.environ.get("PORT", 5000))
    
    serve(app, host="0.0.0.0", port=port, threads=4)