from flask import Flask, redirect, render_template, request
import utils.sql_utils as sql_utils

app = Flask(__name__)

connection = sql_utils.connect_database('ubuntu-mariadb.home','admin','password','inventory')


### main.html paths
@app.route('/user_search')
def user_search():
    data = request.args.get('data')
    query = f'''
SELECT u.name,u.id_num,u.assigned_asset,u.assigned_card
FROM users u
WHERE u.name like '%{data.upper()}%\''''
    response = sql_utils.read_query(connection, query)
    print(response)
    return response

@app.route('/asset_tag_search')
def asset_tag_search():
    data = request.args.get('data')
    query = f'''SELECT a.asset_id, a.serial, a.model, a.category, u.name
FROM assets a
left JOIN users u ON u.assigned_asset=a.asset_id
WHERE a.asset_id = {data.upper()}'''
    response = sql_utils.read_query(connection, query)
    return response

@app.route('/serial_search')
def serial_search():
    data = request.args.get('data')
    query = f'''SELECT a.asset_id, a.serial, a.model, a.category, u.name
FROM assets a
left JOIN users u ON u.assigned_asset=a.asset_id
WHERE a.serial LIKE "{data.upper()}"'''
    response = sql_utils.read_query(connection, query)
    return response

@app.route('/asset_list')
def asset_list():
    query = '''
select a.asset_id,a.serial,a.model,a.category,u.name
from assets a
left join users u on u.assigned_asset=a.asset_id
'''
    data = sql_utils.read_query(connection,query)
    return data

@app.route('/card_list')
def card_list():
    query = '''
select card_number from access_cards
'''
    data = sql_utils.read_query(connection,query)
    data = [x[0] for x in data]
    return data
@app.route('/user_list')
def user_list():
    query = '''
select * from users;
'''
    data = sql_utils.read_query(connection,query)
    return data

### add.html paths

@app.route("/get_available_assets")
def get_available_assets():

    query = '''
SELECT asset_id
FROM assets
WHERE NOT asset_id IN (
	SELECT assigned_asset FROM users WHERE assigned_asset IS NOT NULL
);
'''

    data = sql_utils.read_query(connection,query)
    data = [x[0] for x in data]
    return data

@app.route("/get_available_cards")
def get_available_cards():

    query = '''
SELECT card_number
FROM access_cards
WHERE NOT card_number IN (
	SELECT assigned_card FROM users WHERE assigned_card IS NOT NULL
);
'''

    data = sql_utils.read_query(connection,query)
    data = [x[0] for x in data]
    return data

@app.route('/get_all_cards')

@app.route("/add_asset", methods=['POST'])
def add_asset():
    data = request.json
    query = f'''
    insert into assets (asset_id,serial,model,category) values
    (%(tag)s, %(serial)s, %(model)s, %(category)s)
    '''
    result = sql_utils.send_query(connection,query,data)
    if result:
        return "success"
    else:
        return "failure"

@app.route("/add_user",methods=['POST'])
def add_user():
    data = request.json
    query = f'''
    insert into users (id_num,name,assigned_asset,assigned_card) values
    (%(id)s, %(name)s, %(asset)s, %(card)s)
    '''
    result = sql_utils.send_query(connection,query,data)
    if result:
        return "success"
    else:
        return "failure"

@app.route("/add_card",methods=['POST'])
def add_card():
    data = request.json
    query = f'''
    insert into access_cards (card_number,display_number) values
    (%(card_number)s,%(display_number)s)
    '''
    result = sql_utils.send_query(connection, query, data);
    if result:
        return "success"
    else:
        return "failure"

@app.route("/modify_asset", methods=['POST'])
def modify_asset():
    lookup = {'tag':'asset_id',
              'serial':'serial',
              'model':'model',
              'category':'category'}

    segments = []
    parameters = []
    data = request.json

    for entry in data:
        if entry == 'tag':
            continue
        if data[entry]:
            segments.append(f'{lookup[entry]}=%s')
            parameters.append(data[entry])

    parameters.append(data['tag']) 
    query = f'''
update assets
SET {','.join(segments)}
WHERE asset_id = %s
'''
    result = sql_utils.send_query(connection,query,parameters)
    if result:
        return "fuck you"
    else:
        return "fuck you too"

@app.route("/modify_user", methods=['POST'])
def modify_user():
    lookup = {'id':'id_num',
              'name':'name',
              'asset':'assigned_asset',
              'card':'assigned_card'}

    segments = []
    parameters = []
    data = request.json

    for entry in data:
        if entry == 'tag':
            continue
        if data[entry]:
            segments.append(f'{lookup[entry]}=%s')
            parameters.append(data[entry])

    parameters.append(data['id']) 
    query = f'''
update users
SET {','.join(segments)}
WHERE id_num = %s
'''
    result = sql_utils.send_query(connection,query,parameters)
    if result:
        return "fuck you"
    else:
        return "fuck you too"

@app.route("/modify_card")
def modify_card():
    return None

### Pages
@app.route('/add')
def add_page():
    return render_template('adding.html')

@app.route("/")
def default():
    return render_template('main.html')

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