# importing Flask and other modules
from flask import Flask, request, render_template ,current_app
import psycopg2
import uuid
import os
 
# Flask constructor
app = Flask(__name__)   


DB_HOST = "irc.tdtdt.net"
DB_NAME = "invtest"
DB_PORT = "1213"
DB_USER = "zvevqx"
DB_PASSWORD = "mazinger"


# database data to connect 
def get_db():
    if 'db' not in current_app.config:
        current_app.config['db'] = psycopg2.connect(
            host=DB_HOST,
            port=DB_PORT,
            database=DB_NAME,
            user=DB_USER,
            password=DB_PASSWORD
        )
    return current_app.config['db']


# Function to execute SQL queries
def execute_query(query, params=None):
    connection = get_db()
    with connection.cursor() as cursor:
        cursor.execute(query, params)
        connection.commit()


#def execute_query(query, params=None):
#    try:
#        with conn.cursor() as cursor:
#            cursor.execute(query, params)
#            conn.commit()
#    except Exception as e:
#        print(f"Error executing query: {e}")
#        conn.rollback()  # Rollback changes in case of an error
#    finally:
#        cursor.close()
#




UPLOAD_FOLDER = './statics/images_upload/'
app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER

# A decorator used to tell the application
# which URL is associated function
@app.route('/', methods =["GET", "POST"])
def add_email():
    if request.method == "POST":
        # getting input with name = fname in HTML form
        first_name = request.form.get("fname")
        # getting input with name = lname in HTML form 
        user_email = request.form.get("lmail") 
        cur = conn.cursor()
        cur.execute(
            "INSERT INTO users (name, email) VALUES (%s, %s)", (first_name, user_email))
        conn.commit()
        
        return 'User created successfully!'

    return render_template("form.html")





@app.route('/add_item', methods=['GET','POST'])
def item_add():
    if request.method == "POST":
        # Retrieve data from the form
        name = request.form.get('i_name')
        inventory_type = request.form.get('inventoryType')
        management_type = request.form.get('managementType')
        quantity = request.form.get('quantity') if management_type else None
        description = request.form.get('description')
        image = request.form.get('image')
        # Process other form data as needed
        image = request.files['image']
        if image:
            image_filename = os.path.join(app.config['UPLOAD_FOLDER'], image.filename)
            image.save(image_filename)
        else:
            image_filename = None    
        # Print the retrieved data (you can replace this with database insertion, etc.)
        print(f"Name: {name}")
        print(f"Inventory Type: {inventory_type}")
        print(f"Management Type: {management_type}")
        print(f"Quantity: {quantity}")
        print(f"Description: {description}")
        print(f"image: {image}")
        
        # Get the UUID for the inventory_type from the 'itemtype' table
        get_inventory_type_uuid_query = "SELECT type_id FROM itemtype WHERE type_name = %s;"
        with get_db().cursor() as cursor:
            cursor.execute(get_inventory_type_uuid_query, (inventory_type,))
            inventory_type_uuid = cursor.fetchone()

        inventory_type_uuid = inventory_type_uuid[0]            
        asset_uuid = uuid.uuid4()
        # Insert data into the 'asset' table
        #INSERT INTO asset (name, type_id, management_type, quantity, description, image_filename)
        insert_asset_query = """
        INSERT INTO asset (asset_id , name, type_id, in_stock, is_broken )
        VALUES (%s, %s, %s, true, false);
        """
        execute_query(insert_asset_query, (str(asset_uuid), name, inventory_type_uuid))
        


    cur = get_db().cursor()
    cur.execute("SELECT type_name FROM itemtype")
    results = cur.fetchall()
    return render_template("additem.html" , itemType = results )






# Close the database connection when the application context is popped
@app.teardown_appcontext
def close_db(e=None):
    db = current_app.config.get('db')
    if db is not None:
        db.close()
        current_app.config.pop('db', None)



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