import sqlite3
from flask import Flask, flash, redirect, render_template, request, session, url_for
from flask_session import Session
from passlib.apps import custom_app_context as pwd_context
from tempfile import gettempdir
from sqlalchemy import *

from helpers import *

from cs50 import SQL

# configure application
app = Flask(__name__)

# configure CS50 Library to use SQLite database
# db = SQL("sqlite:///signme.db")

# configure sqlite3 database
conn = sqlite3.connect('music.sqlite3')
db = conn.cursor()

# engine = create_engine('sqlite:///signme.db', echo=True)
# metadata = MetaData(bind=engine)
# users_table = Table('users', metadata, autoload=True)
# events = Table('events', metadata, autoload=True)
# registrants = Table('registrants', metadata, autoload=True)

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

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

# connect to database
# conn = sqlite3.connect('signme.db', check_same_thread=False)
# db = conn.cursor()


@app.route("/")
def home():
    if session==[]:
        """User profile page"""
        # select row from users table corresponding to the user
        user = db.execute("SELECT * FROM users WHERE id = :id", id=session["user_id"])

        # select events from events table corresponding to the user
        myevents = db.execute("SELECT title FROM events WHERE host = :host", host=session["user_id"])

        # select events from registrants table corresponding to the user
        registeredevents = db.execute("SELECT title FROM registrants JOIN events ON events.id = registrants.event_id WHERE user_id=:userid",
        userid=session["user_id"])

        return render_template("profile.html", user=user, myevents=myevents, registeredevents=registeredevents)
    else:
        return render_template("home.html")



@app.route("/newevent", methods=["GET", "POST"])
@login_required
def newevent():
    """Create new event."""

    # if user reached route via POST
    if request.method == "POST":

        # update events table
        event = db.execute("INSERT INTO events (title, host) VALUES (:title, :host)", title=request.form.get("title"),
        host=session["user_id"])

        return redirect(url_for("myevent") + event.id)

    # else if user reached route via GET
    else:
        return render_template("newevent.html")

@app.route("/myevent/<int:event_id>")
@login_required
def myevent(event_id):
    """Show information related to the event."""

    registrants = db.execute("SELECT * FROM registrants JOIN users ON users.id = registrants.user_id WHERE event_id=:eventid",
    eventid=event_id)

    event = db.execute("SELECT * FROM events WHERE event_id=:eventid", eventid=event_id)

    number = len(registrants)

    return render_template("myevent.html", registrants=registrants, event=event, number=number)


@app.route("/login", methods=["GET", "POST"])
def login():
    """Log user in."""

    # forget any user_id
    session.clear()

    # if user reached route via POST (as by submitting a form via POST)
    if request.method == "POST":

        # ensure username was submitted
        if not request.form.get("email"):
            return render_template("error.html", message = "Must provide email")

        # ensure password was submitted
        elif not request.form.get("password"):
            return render_template("error.html", message = "Must provide password")

        # query database for username
        user = db.execute("SELECT * FROM users WHERE email=:email", email=request.form.get("email"))

        # ensure username exists and password is correct
        if user == [] or not pwd_context.verify(request.form.get("password"), user.password):
            return render_template("error.html", message = "Invalid username and/or password")

        # remember which user has logged in
        session["user_id"] = user.id

        # redirect user to home page
        return redirect(url_for("home"))

    # else if user reached route via GET (as by clicking a link or via redirect)
    else:
        return render_template("login.html")

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

    # forget any user_id
    session.clear()

    # redirect user to login form
    return redirect(url_for("login"))


@app.route("/register", methods=["GET", "POST"])
def register():
    """Register user."""

    # if user reached route via POST
    if request.method == "POST":

         # ensure email is not blank
        if not request.form.get("email"):
            return render_template("error.html", message="Must provide email")

        # ensure email isn't already taken
        email = db.execute("SELECT * FROM users WHERE email = :email", email=request.form.get("email"))
        if email != []:
            return render_template("error.html", message="Sorry, this email is taken :(")

        # ensure password is not blank
        elif not request.form.get("password"):
            return render_template("error.html", message="Must provide password")

        # ensure same password entered again
        elif request.form.get("password") != request.form.get("passwordconfirm"):
            return render_template("error.html", message="Passwords must match")

        # hash password
        hash = pwd_context.encrypt(request.form.get("password"))


        # enter new user into users
        # user = db.execute("INSERT INTO users (email, password, first_name, last_name, birthdate, phone, school, major, grad_year,\
        # house) VALUES (:email, :password, :first_name, :last_name, :birthdate, :phone, :school, :major, \
        # :grad_year, :house)", email=request.form.get("email"), password=hash,
        # first_name=request.form.get("first_name"), last_name=request.form.get("last_name"), birthdate=request.form.get("birthdate"),
        # phone=request.form.get("phone"), school=request.form.get("school"), major=request.form.get("major"),
        # grad_year=request.form.get("grad_year"), house=request.form.get("house"))

        user = db.execute("INSERT INTO users (email, password, first_name, last_name, birthdate, phone, school, major, grad_year,\
        house) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", request.form.get("email"), hash,
        request.form.get("first_name"), request.form.get("last_name"), request.form.get("birthdate"),
        request.form.get("phone"), request.form.get("school"), request.form.get("major"),
        request.form.get("grad_year"), request.form.get("house"))

        conn.commit()

        # with engine.begin() as connection:
        #     user = connection.execute(users.insert(), email=request.form.get("email"), password=hash,
        #     first_name=request.form.get("first_name"), last_name=request.form.get("last_name"), birthdate=request.form.get("birthdate"),
        #     phone=request.form.get("phone"), school=request.form.get("school"), major=request.form.get("major"),
        #     grad_year=request.form.get("grad_year"), address=request.form.get("address"), zipcode=request.form.get("zipcode"),
        #     city=request.form.get("city"), state=request.form.get("state"))

        #i = users.insert()
        # user = users.insert().execute(email=request.form.get("email"), password=hash,
        # first_name=request.form.get("first_name"), last_name=request.form.get("last_name"), birthdate=request.form.get("birthdate"),
        # phone=request.form.get("phone"), school=request.form.get("school"), major=request.form.get("major"),
        # grad_year=request.form.get("grad_year"), address=request.form.get("address"), zipcode=request.form.get("zipcode"),
        # city=request.form.get("city"), state=request.form.get("state"))


        # a connectionless way to Insert a user
# ins = users_table.insert()
# result = engine.execute(ins, name="Shinji", age=15, password="nihongo")

# # another connectionless Insert
# result = users_table.insert().execute(name="Martha", age=45, password="dingbat")


        # remember the user who has just registered
        session["user_id"] = user

        # redirect user to home page
        return redirect(url_for("home"))

    # else if user reached route via GET
    else:
        return render_template("register.html")