from flask import Flask, render_template, request
import psycopg2
from psycopg2.extras import RealDictCursor

# Flask app setup
app = Flask(__name__)

# Database connection setup
DB_CONFIG = {
    "dbname": "db_202425z_va_prj_personalteacher",
    "user": "db_202425z_va_prj_personalteacher_owner",
    "password": "5ad8d6cf55cc",
    "host": "localhost",
    "port": 9999,
}

# Function to get all subjects
def get_subjects():
    try:
        with psycopg2.connect(**DB_CONFIG) as conn:
            with conn.cursor(cursor_factory=RealDictCursor) as cursor:
                cursor.execute("SELECT * FROM Subject")
                return cursor.fetchall()
    except Exception as e:
        print("Database error:", e)
        return []


def get_teachers(from_date=None, to_date=None):
    try:
        with psycopg2.connect(**DB_CONFIG) as conn:
            with conn.cursor(cursor_factory=RealDictCursor) as cursor:
                # Base query
                query = "SELECT * FROM Teacher join Public_user on Teacher.id_user = Public_user.id_user"
                conditions = []

                # Add conditions based on the dates
                if from_date:
                    conditions.append(f"hire_date >= '{from_date}'")

                # If there are any conditions, append them to the query
                if conditions:
                    query += " WHERE " + " AND ".join(conditions)

                cursor.execute(query)
                return cursor.fetchall()
    except Exception as e:
        print("Database error:", e)
        return []
def get_users():
    try:
        with psycopg2.connect(**DB_CONFIG) as conn:
            with conn.cursor(cursor_factory=RealDictCursor) as cursor:
                cursor.execute("SELECT * FROM Public_user")
                result = cursor.fetchall()
                return result
    except Exception as e:
        print("Database error:", e)
        return []


@app.route("/")
def index():
    #users = get_users()
    return render_template("index.html")


@app.route("/subjects")
def subjects():
    subjects = get_subjects()
    return render_template("subjects.html", subjects=subjects)


@app.route("/teachers", methods=["GET"])
def teachers():
    # Get date filters from the request
    from_date = request.args.get("from_date")

    # Fetch filtered teachers data
    teachers = get_teachers(from_date)

    return render_template("teachers.html", teachers=teachers)
@app.route("/users")
def users():
    users = get_users()
    return render_template("users.html", users=users)


if __name__ == "__main__":
    app.run(port=3000, debug=True)