#!/usr/bin/env python #
"""\app.py (Part A)
Flask Application that demonstrates the PostgreSQL ltree extension

"""
from flask import Flask, render_template, request, redirect, url_for 
import psycopg2 

app = Flask(__name__) 

# HELPER FUNCTION - SEARCH Path
def getpath(search):
	conn = psycopg2.connect(database="testdb", 
                            user="tester", 
                            password="a123s", 
                            host="localhost", port="5432") 
	# create a cursor 
	cur = conn.cursor() 

	cur.execute('''SELECT t.id, t.path, n.descrip, n.notes, n.tree_id 
      FROM tree t, treenotes n WHERE path  ~ %s AND t.id=n.tree_id 
      ORDER BY t.id DESC LIMIT 20; ''', (f"{search}",))        
	# Fetch the data 
	results = cur.fetchall() 

	# close the cursor and connection 
	cur.close() 
	conn.close() 
	return results

#Part B of app.py

@app.route('/', methods=["GET", "POST"])
def index(): 
	# Connect to the database 
	conn = psycopg2.connect(database="testdb",  
                            user="tester", 
                            password="a123s", 
                            host="localhost", port="5432")  

	# create a cursor 
	cur = conn.cursor() 
	if request.method == "POST":
		path = dict(request.form)
		data = getpath(path["search"])
	else:
		cur.execute('''SELECT t.id, t.path, n.descrip, n.notes, n.tree_id 
       FROM tree t, treenotes n WHERE path  ~ '*' 
       AND t.id=n.tree_id ORDER BY t.id DESC LIMIT 20; ''' )        
	# Fetch the data 
	data = cur.fetchall() 

	# close the cursor and connection 
	cur.close() 
	conn.close() 

	return render_template('index.html', data=data) 


@app.route('/create', methods=['POST']) 
def create(): 
	conn = psycopg2.connect(database="testdb",  
                            user="tester", 
                            password="a123s", 
                            host="localhost", port="5432")

	cur = conn.cursor() 

	# Get the data from the form 
	path = request.form['path']
	descrip = request.form['descrip'] 
	notes = request.form['notes'] 

    # Insert the data into the table tree and that of treenotes  
	cur.execute( 
     '''WITH new_tree AS (
    INSERT INTO tree (path) VALUES (%s)
    RETURNING id
    )
    INSERT INTO treenotes (tree_id, descrip, notes) SELECT id, %s, %s 
    FROM new_tree;''', (path, descrip, notes)) 

	# commit the changes 
	conn.commit() 

    # Remove the inputting of any possible duplicate paths from the table
	cur.execute( 
	'''DELETE FROM tree WHERE id IN
       (SELECT id
        FROM
        (SELECT id,
         ROW_NUMBER() OVER( PARTITION BY path
        ORDER BY  id ) AS row_num
        FROM tree ) t
        WHERE t.row_num > 1 )''') 

	# commit the changes 
	conn.commit() 
    

	# close the cursor and connection 
	cur.close() 
	conn.close() 

	return redirect(url_for('index')) 


@app.route('/update', methods=['POST']) 
def update(): 
	conn = psycopg2.connect(database="testdb", 
                            user="tester", 
                            password="a123s", 
                            host="localhost", port="5432")

	cur = conn.cursor() 

	# Get the data from the form 
	descrip = request.form['descrip'] 
	notes = request.form['notes'] 
	id = request.form['id'] 

	# Update the data in the table 
	cur.execute( 
		'''UPDATE treenotes SET descrip=%s,	
		notes=%s WHERE id=%s''', (descrip, notes, id)) 

	# commit the changes 
	conn.commit() 
	return redirect(url_for('index')) 


@app.route('/delete', methods=['POST']) 
def delete(): 
	conn = psycopg2.connect(database="testdb", 
                            user="tester", 
                            password="a123s", 
                            host="localhost", port="5432")

	cur = conn.cursor()

	
	# Get the data from the form 
	id = request.form['id']
	# Delete the data from the table 
	cur.execute('''DELETE FROM tree WHERE id=%s''', (id,)) 

	# commit the changes 
	conn.commit() 

	# close the cursor and connection 
	cur.close() 
	conn.close() 

	return redirect(url_for('index')) 
  
  # Resolve ltree search input errors
@app.errorhandler(Exception)
def handle_exception(e):
	message = str(e)
	return render_template('index.html', message=message)
    

if __name__ == '__main__': 
	app.run(host='0.0.0.0', debug=False)