import sqlite3
from flask import Flask, flash, 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("surreysports.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,
            address TEXT
        )
    """
)

db.execute(
    """
    CREATE TABLE IF NOT EXISTS admin (
            id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
            name TEXT NOT NULL,
            phone_number INTEGER,
            email TEXT UNIQUE,
            admin_id TEXT,
            password TEXT NOT NULL,
            gender TEXT,
            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("surreysports.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")
        gender = request.form.get("gender")
        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, gender, address) VALUES ( ?, ?, ?, ?, ?, ?, ?)""",
                       (name, username, generate_password_hash(password), phone_number, email, gender, 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("surreysports.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_number")
        email = request.form.get("email")
        gender = request.form.get("gender")
        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, gender, address) VALUES ( ?, ?, ?, ?, ?, ? , ?)""",
                       (name, username, generate_password_hash(password), phone_number, email, gender, 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
            con.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"
            id_column = "student_id"
        elif login_as == "admin":
            table_name = "admin"
            id_column = "admin_id"
        else:
            return jsonify({"error": "Invalid login type"}), 403

        con = sqlite3.connect('surreysports.db')  # Opening the appropriate database file
        db = con.cursor()

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

        if row and check_password_hash(row[5], password): # removed (and 'password' in row)
            session["user_id"] = row[0]
            con.close()
            return redirect("/")

        else:
            con.close()
            return jsonify({"error": "Invalid username and/or password"}), 403


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


@app.route('/change_password', methods = ['GET', 'POST'])
@login_required
def change_password():
    if request.method == "POST":
        current_password = request.form.get("current_password")
        new_password = request.form.get("new_password")
        confirm_password = request.form.get("confirm_password")

        # Checks if the fields are empty
        if not current_password or not new_password or not confirm_password:
            return jsonify({"error": "Fill out all fields"}), 400

        # if passwords are not same
        if new_password != confirm_password:
            return jsonify({"error": "Passwords do not match"}), 400

        # Logic to trace the user - user or admin
        if "user_id" in session:
            table_name = "user"
            id_column = "id"
            user_id = session["user_id"]
        elif "admin_id" in session:
            table_name = "admin"
            id_column = "id"
            user_id = session["admin_id"]

        # Check db for current_password
        con = sqlite3.connect("surreysports.db")
        db = con.cursor()

        # db.execute(f"""
        #            SELECT password FROM {table_name} WHERE {id_column} = ?
        #            """, (user_id,))
        db.execute(
        """
        SELECT password FROM {} WHERE {} = ?
        """.format(table_name, id_column), (user_id,))
        row = db.fetchone()

        if not check_password_hash(row[0], current_password):
            con.close()
            return jsonify({"error": "Current password is incorrect"}), 403

        # Updating new password in the appropriate table
        # db.execute(f"""
        #            UPDATE {table_name} SET password = ? WHERE {id_column} = ?
        #            """, (generate_password_hash(current_password), user_id))

        db.execute(
        """
        UPDATE {} SET password = ? WHERE {} = ?
        """.format(table_name, id_column),
        (generate_password_hash(current_password), user_id)
         )
        con.commit() # Saving changes
        con.close()

        flash("Password has been updated successfully", "success") # Function in layout.html
        return redirect('/')
        # return jsonify({"success": "Password has been updated", "redirect": "/"}) # Trying to returns here

    return render_template("change_password.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)