import sqlite3
from flask import Flask, jsonify, redirect, render_template, request, session
from flask_session import Session
from werkzeug.security import generate_password_hash, check_password_hash
from helpers import login_required

# Creating a flask app object
app = Flask(__name__)
app.secret_key="__privatekey__"

# Configure session to use filesystem (instead of signed cookies)
app.config["SESSION_PERMANENT"] = False
app.config["SESSION_TYPE"] = "filesystem"

Session(app)

@app.after_request
def after_request(response):
    """Ensure responses aren't cached"""
    response.headers["Cache-Control"] = "no-cache, no-store, must-revalidate"
    response.headers["Expires"] = 0
    response.headers["Pragma"] = "no-cache"
    return response

con = sqlite3.connect("user.db")
db = con.cursor()
db.execute(
    """
    CREATE TABLE IF NOT EXISTS user (
            id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
            name TEXT NOT NULL,
            phone_number INTEGER,
            email TEXT UNIQUE,
            student_id TEXT,
            password TEXT NOT NULL,
            gender TEXT CHECK(gender IN ('male','female','non binary','prefer not to say','other')),
            address TEXT
        )
    """
)

db.execute(
    """
    CREATE TABLE IF NOT EXISTS admin (
            id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
            name TEXT NOT NULL,
            phone_number INTEGER NOT NULL,
            email TEXT UNIQUE,
            admin_id TEXT,
            password TEXT NOT NULL,
            gender TEXT CHECK(gender IN ('male','female','non binary','prefer not to say','other')),
            address TEXT
        )
    """
)

# Commiting changes
con.commit()
con.close()

@app.route('/')
@login_required # Decorator func from helpers
def index():
    # Check if the user is logged in
    if "user_id" in session:
        # Retrieve user_id from the session
        user_id = session["user_id"]
    return render_template('index.html')


@app.route('/signup', methods=['GET', 'POST'])
def signup():
    session.clear() # Forget any previous user

    con = sqlite3.connect("user.db") # opening db
    db = con.cursor() # Obj

    if request.method == "POST":
        username = request.form.get("student_id") 
        password = request.form.get("password")
        confirm_password = request.form.get("confirm_password")
        name =  request.form.get("name")
        phone_number = request.form.get("phone_no")
        email = request.form.get("email")
        address = request.form.get("address")

        # Checks for input validation
        if not username:
            return jsonify({"error": "Student ID is required"}), 400
        elif not password:
            return jsonify({"error": "Password is required"}), 400
        elif not confirm_password:
            return jsonify({"error": "Matching Password Confirmation is required"}), 400
        elif (password != confirm_password):
            return jsonify({"error": "Passwords do not match, please try again"}), 400
        
        # Check if user exists
        db.execute("""
                    SELECT * FROM user
                    WHERE student_id = ? OR phone_number = ? OR email = ?
                    """, (username, phone_number, email))
        existing_user = db.fetchone()
        if existing_user:
            return jsonify({"error": "A user with the same username, phone number, or email already exists"}), 400
    
        # Proceed
        else:
            # Incorporate in the database
            db.execute("""
                       INSERT INTO user (name, student_id, password, phone_number, email, address) VALUES (?, ?, ?, ?, ?, ?)""",
                       (name, username, generate_password_hash(password), phone_number, email, address))
            
            con.commit() # Commit changes to db to save changes.
            
            # Retrieve the user ID after inserting into the database
            user_id = db.lastrowid # retrieving id from last inserted row

            # Save logged-in user
            session["user_id"] = user_id

        # Close db connection
        con.close()

        # Redirect to homepage after registering
        return redirect("/")


    # For get request
    return render_template("signup.html")

# Admin signup flask route
@app.route('/admin/signup', methods=['GET', 'POST'])
def admin_signup():
    session.clear() # Forget any previous user

    con = sqlite3.connect("admin.db") # opening db
    db = con.cursor() # Obj

    if request.method == "POST":
        username = request.form.get("admin_id") 
        password = request.form.get("password")
        confirm_password = request.form.get("confirm_password")
        name =  request.form.get("name")
        phone_number = request.form.get("phone_no")
        email = request.form.get("email")
        address = request.form.get("address")

        # Checks for input validation
        if not username:
            return jsonify({"error": "Username is required"}), 400
        elif not password:
            return jsonify({"error": "Password is required"}), 400
        elif not confirm_password:
            return jsonify({"error": "Matching Password Confirmation is required"}), 400
        elif (password != confirm_password):
            return jsonify({"error": "Passwords do not match, please try again"}), 400
        
        # Check if user exists
        db.execute("""
                    SELECT * FROM admin
                    WHERE admin_id = ? OR phone_number = ? OR email = ?
                    """, (username, phone_number, email))
        existing_admin = db.fetchone()
        if existing_admin:
            return jsonify({"error": "A user with the same username, phone number, or email already exists"}), 400
    
        # Proceed
        else:
            # Incorporate in the database
            db.execute("""
                       INSERT INTO admin (name, admin_id, password, phone_number, email, address) VALUES (?, ?, ?, ?, ? , ?)""",
                       (name, username, generate_password_hash(password), phone_number, email, address))
            
            con.commit() # Commit changes to db to save changes.
            
            # Retrieve the user ID after inserting into the database
            admin_id = db.lastrowid # retrieving id from last inserted row

            # Save logged-in user
            session["admin_id"] = admin_id

            # Close db connection
            db.close()

        # Redirect to homepage after registering
        return redirect("/")


    # For get request
    return render_template("signup_admin.html")


@app.route('/login', methods=['GET', 'POST'])
def login():
    session.clear() # Forget any user

    if request.method == "POST":
        login_as = request.form.get("login_as")
        username = request.form.get("username")
        password = request.form.get("password")

        if not login_as or not username or not password:
            return jsonify({"error": "Login type, Username and password are required"}), 403
        
        # Select table based on the login_as option
        if login_as == "student":
            table_name = "user"
            db_file = "user.db"
            id_column = "student_id"
        elif login_as == "admin":
            table_name = "admin"
            db_file = "admin.db"
            id_column = "admin_id"
        else:
            return jsonify({"error": "Invalid login type"}), 403
        
        con = sqlite3.connect(db_file)  # Opening the appropriate database file
        db = con.cursor()     

        # Debug print for SQL query and parameters
        query = f"SELECT * FROM {table_name} WHERE {id_column} = ?"
        print("SQL Query:", query)
        print("Parameters:", (username,))
        
        # Execute and fetch from result
        db.execute(query, (username,))
        row = db.fetchone()

        # # Execute and fetch from result
        # db.execute(f"SELECT * FROM {table_name} WHERE {id_column} = ?", (username,))
        # row = db.fetchone()

        print("Row from database:", row)  # Debug print
        
        if row:
            if row:
                hashed_password_from_db = row[5]  # Assuming password is the 6th column
                print("Hashed password from database:", hashed_password_from_db)  # Debug print
                print("Hashed password generated during login attempt:", generate_password_hash(password))  # Debug print
            
                if check_password_hash(row[5], password): # Using indexes instead of names coz fetchone returns tuple not dict
            # if row and check_password_hash(row[5], password): 
                    session["user_id"] = row[0]
                    con.close() # Close the database connection
                    return redirect("/")
        
       
        con.close()
        return jsonify({"error": "Invalid username and/or password"}), 403

            
    # For GET requests
    return render_template("login.html")

@app.route('/logout')
def logout():
    """Log user out"""

    # Forget any user_id
    session.clear()

    return render_template("logout.html")

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