import os
import sqlite3
from flask import Flask, redirect, request, render_template, jsonify, url_for, flash, session

app = Flask(__name__)
DATABASE = "project_db.db"
app.secret_key = "hello"

ALLOWED_EXTENSIONS = set(['txt', 'pdf', 'png', 'jpg', 'jpeg', 'gif'])

#Error handling for common database connection.
#Parameter optional. Defaults to DATABASE.
def connect_db(db_file=DATABASE):
    conn = None
    try:
        conn = sqlite3.connect(db_file)
    except:
        print("Error connecting to database.")
    return conn

@app.route("/", methods=['GET'])
def returnIndex():
    if (request.method == 'GET'):
        return render_template("test.html")

@app.route("/Manage_Coworking_Spaces", methods=['GET'])
def returnManageCoworkingSpaces():
    if (request.method == 'GET'):
        return render_template("Manage_Coworking_Spaces.html")

@app.route("/Locations", methods=['GET'])
def returnListLocations():
    if (request.method =='GET'):
        conn = connect_db()
        cur = conn.cursor()
        cur.execute("SELECT Name, Main_Photo FROM coworking_spaces")
        locationData = cur.fetchall()
        conn.close()
        return render_template("Locations.html", data = locationData)

@app.route("/Load/CodeBase")
def Load():
			Name = "Codebase";
			conn = sqlite3.connect(DATABASE)
			cur = conn.cursor()
			print(Name)
		#	getAddress(Name)
			cur.execute("SELECT * FROM 'coworking_spaces' WHERE Name =?;", [Name])
			data = cur.fetchall()
			print(data)
		#	cur.execute("SELECT Address FROM coworking_spaces WHERE Name =? ;", [Name])
		#	Address = cur.fetchall()
		#	print(Address)
		#	cur.execute("SELECT Main_Photo FROM coworking_spaces WHERE Name =? ;", [Name])
		#	Main_Photo = cur.fetchall()
		#	print(Main_Photo)
		#	cur.execute("SELECT Additional_photos FROM coworking_spaces WHERE Name =? ;", [Name])
		#	Additional_photos = cur.fetchall()
		#	print(Additional_photos)
		#	cur.execute("SELECT Description FROM coworking_spaces WHERE Name =? ;", [Name])
		#	Description = cur.fetchall()
		#	print(Description)
		#	cur.execute("SELECT Website FROM coworking_spaces WHERE Name =? ;", [Name])
		#	Website = cur.fetchall()
		#	print(Website)
		#	cur.execute("SELECT Opening_Hours FROM coworking_spaces WHERE Name =? ;", [Name])
		#	Opening_Hours = cur.fetchall()
		#	print(Opening_Hours)
			conn.close()
			return render_template('Space.html', data = data)
			# pageAddress = Address, pageMain_Photo = Main_Photo, pageAdditional_photos = Additional_photos, pageDescription = Description, pageWebsite = Website, pageOpening_Hours = Opening_Hours  )

@app.route("/SearchRecord", methods=['POST', 'GET'])
def searchRecord():

    if (request.method =='POST'):
        recordName = request.form.get('recordName', default="Error")
        print(f"Searching for '{recordName}' in the database.")

        try:
            conn = connect_db()
            cur = conn.cursor()
            cur.execute("SELECT * FROM coworking_spaces WHERE Name=?;", [recordName])
            recordData = cur.fetchall()

        except:
            print(f"Encountered an error whilst searching for record.")

        finally:
            conn.close()

            if not recordData: #If recordData is an empty list...
                print(f"'{recordName}' not found.")
                return (f"'{recordName}' not found.")

            else:
                print(f"Found: {recordData}")
                return (recordData)

@app.route('/AddRecord', methods=['POST'])
def addRecord():

    if (request.method == 'POST'):
        recordName = request.form.get('recordName', default="Error")
        recordAddress = request.form.get('recordAddress', default="Error")
        recordMainPhotos = request.form.get('recordMainPhotos', default="Error")
        recordAdditionalPhotos = request.form.get('recordAdditionalPhotos', default="Error")
        recordDescription = request.form.get('recordDescription', default="Error")
        recordWebsite = request.form.get('recordWebsite', default="Error")
        recordEmail = request.form.get('recordEmail', default="Error")
        recordPhoneNumber = request.form.get('recordPhoneNumber', default="Error")
        recordOpeningHours = request.form.get('recordOpeningHours', default="Error")
        recordCheckinInstructions = request.form.get('recordCheckinInstructions', default="Error")
        recordData = [recordName, recordAddress, recordMainPhotos, recordAdditionalPhotos, recordDescription, recordWebsite, recordEmail, recordPhoneNumber, recordOpeningHours, recordCheckinInstructions]
        print(f"Adding '{recordName}' to the database.")

        try:
            conn = connect_db()
            cur = conn.cursor()
            cur.execute("INSERT INTO coworking_spaces ('Name', 'Address', 'Main_Photo', 'Additional_Photos', 'Description', 'Website', 'Email', 'Phone_Number', 'Opening_Hours', 'Checkin_Instructions')\
                        VALUES (?,?,?,?,?,?,?,?,?,?)", (recordName, recordAddress, recordMainPhotos, recordAdditionalPhotos, recordDescription, recordWebsite, recordEmail, recordPhoneNumber, recordOpeningHours, recordCheckinInstructions))
            conn.commit()
            infoMessage = (f"Added: {recordData}")

        except:
            conn.rollback()
            infoMessage = (f"Error adding record.")

        finally:
            conn.close()
            print(infoMessage)
            return (infoMessage)

@app.route('/DeleteRecord', methods=['POST'])
def deleteRecord():

    if (request.method =='POST'):
        recordName = request.form.get('recordName', default="Error")
        print(f"Deleting '{recordName}' from the database.")

        try:
            conn = sqlite3.connect(DATABASE)
            cur = conn.cursor()
            cur.execute("DELETE FROM coworking_spaces WHERE Name=?;", [recordName])
            conn.commit()
            infoMessage = (f"Deleted: '{recordName}'.")

        except:
            conn.rollback()
            infoMessage = (f"Error deleting record.")

        finally:
            conn.close()
            print(infoMessage)
            return (infoMessage)

@app.route('/UpdateRecord', methods=['POST'])
def updateRecord():

    if (request.method == 'POST'):
        recordName = request.form.get('recordName', default="Error")
        recordAddress = request.form.get('recordAddress', default="Error")
        recordMainPhotos = request.form.get('recordMainPhotos', default="Error")
        recordAdditionalPhotos = request.form.get('recordAdditionalPhotos', default="Error")
        recordDescription = request.form.get('recordDescription', default="Error")
        recordWebsite = request.form.get('recordWebsite', default="Error")
        recordEmail = request.form.get('recordEmail', default="Error")
        recordPhoneNumber = request.form.get('recordPhoneNumber', default="Error")
        recordOpeningHours = request.form.get('recordOpeningHours', default="Error")
        recordCheckinInstructions = request.form.get('recordCheckinInstructions', default="Error")
        recordData = [recordName, recordAddress, recordMainPhotos, recordAdditionalPhotos, recordDescription, recordWebsite, recordEmail, recordPhoneNumber, recordOpeningHours, recordCheckinInstructions]
        print(f"Updating '{recordName}' in the database.")

        try:
            conn = sqlite3.connect(DATABASE)
            cur = conn.cursor()
            cur.execute("UPDATE coworking_spaces SET 'Name' = ?, 'Address' = ?, 'Main_Photo' = ?, 'Additional_Photos' = ?, 'Description' = ?, 'Website' = ?, 'Email' = ?, 'Phone_Number' = ?, 'Opening_Hours' = ?, 'Checkin_Instructions' = ? WHERE Name=?;", [recordName, recordAddress, recordMainPhotos, recordAdditionalPhotos, recordDescription, recordWebsite, recordEmail, recordPhoneNumber, recordOpeningHours, recordCheckinInstructions, recordName])
            conn.commit()
            infoMessage = (f"Updated: '{recordName}'.")

        except:
            conn.rollback()
            infoMessage = (f"Error updating record.")

        finally:
            conn.close()
            print(infoMessage)
            return (infoMessage)

@app.route("/locations", methods=["POST" , "GET"])
def Locations():

    print('Processing location')

    if request.method == "POST":
        session.permanent = True
        locationsName = request.form['name']
        locationPic = request.form['image']


        conn = sqlite3.connect(DATABASE)
        cur = conn.cursor()
        cur.execute("SELECT Name, Main_photo FROM coworking_spaces WHERE Name= ?;", [locationsName])
        conn.commit()
        conn.close()

        msg= " Location added successfuly!"

    return render_template('locations.html')





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