import requests
data = requests.get('http://localhost:5000/lakes').json()
print(len(data), "lakes")
for item in data[:10]:
    print(item['name'], "- elevation:", item['elevation'], "m / area:", item['area'], "km^2 / type:", item['type'])

"""
Explanation: Homework 6: Web Applications
For this homework, you're going to write a web API for the lake data in the MONDIAL database. (Make sure you've imported the data as originally outlined in our week 1 tutorial.)
The API should perform the following tasks:

A request to /lakes should return a JSON list of dictionaries, with the information from the name, elevation, area and type fields from the lake table in MONDIAL.
The API should recognize the query string parameter sort. When left blank or set to name, the results should be sorted by the name of the lake (in alphabetical order). When set to area or elevation, the results should be sorted by the requested field, in descending order.
The API should recognize the query string parameter type. When specified, the results should only include rows that have the specified value in the type field.
You should be able to use both the sort and type parameters in any request.

This notebook contains only test requests to your API. Write the API as a standalone Python program, start the program and then run the code in the cells below to ensure that your API produces the expected output. When you're done, paste the source code in the final cell (so we can check your work, if needed).
Hints when writing your API code:

You'll need to construct the SQL query as a string, piece by piece. This will likely involve a somewhat messy tangle of if statements. Lean into the messy tangle.
Make sure to use parameter placeholders (%s) in the query.
If you're getting SQL errors, print out your SQL statement in the request handler function so you can debug it. (When you use print() in Flask, the results will display in your terminal window.)
When in doubt, return to the test code. Examine it carefully and make sure you know exactly what it's trying to do.

Problem set #1: A list of lakes
Your API should return a JSON list of dictionaries (objects). Use the code below to determine what the keys of the dictionaries should be. (For brevity, this example only prints out the first ten records, but of course your API should return all of them.)
Expected output:
143 lakes
Ammersee - elevation: 533 m / area: 46 km^2 / type: None
Arresoe - elevation: None m / area: 40 km^2 / type: None
Atlin Lake - elevation: 668 m / area: 798 km^2 / type: None
Balaton - elevation: 104 m / area: 594 km^2 / type: None
Barrage de Mbakaou - elevation: None m / area: None km^2 / type: dam
Bodensee - elevation: 395 m / area: 538 km^2 / type: None
Brienzersee - elevation: 564 m / area: 29 km^2 / type: None
Caspian Sea - elevation: -28 m / area: 386400 km^2 / type: salt
Chad Lake - elevation: 250 m / area: 23000 km^2 / type: salt
Chew Bahir - elevation: 520 m / area: 800 km^2 / type: salt
End of explanation
"""


import requests
data = requests.get('http://localhost:5000/lakes?type=salt').json()
avg_area = sum([x['area'] for x in data if x['area'] is not None]) / len(data)
avg_elev = sum([x['elevation'] for x in data if x['elevation'] is not None]) / len(data)
print("average area:", int(avg_area))
print("average elevation:", int(avg_elev))

"""
Explanation: Problem set #2: Lakes of a certain type
The following code fetches all lakes of type salt and finds their average area and elevation.
Expected output:
average area: 18880
average elevation: 970
End of explanation
"""


import requests
data = requests.get('http://localhost:5000/lakes?sort=elevation').json()
for item in [x['name'] for x in data if x['elevation'] is not None][:15]:
    print("*", item)

"""
Explanation: Problem set #3: Lakes in order
The following code fetches lakes in reverse order by their elevation and prints out the name of the first fifteen, excluding lakes with an empty elevation field.
Expected output:
* Licancabur Crater Lake
* Nam Co
* Lago Junin
* Lake Titicaca
* Poopo
* Salar de Uyuni
* Koli Sarez
* Lake Irazu
* Qinghai Lake
* Segara Anak
* Lake Tahoe
* Crater Lake
* Lake Tana
* Lake Van
* Issyk-Kul
End of explanation
"""


import requests
data = requests.get('http://localhost:5000/lakes?sort=area&type=caldera').json()
for item in data:
    print("*", item['name'])

"""
Explanation: Problem set #4: Order and type
The following code prints the names of the largest caldera lakes, ordered in reverse order by area.
Expected output:
* Lake Nyos
* Lake Toba
* Lago Trasimeno
* Lago di Bolsena
* Lago di Bracciano
* Crater Lake
* Segara Anak
* Laacher Maar
End of explanation
"""


import requests
data = requests.get('http://localhost:5000/lakes', params={'type': "' OR true; --"}).json()
data

"""
Explanation: Problem set #5: Error handling
Your API should work fine even when faced with potential error-causing inputs. For example, the expected output for this statement is an empty list ([]), not every row in the table.
End of explanation
"""


import requests
data = requests.get('http://localhost:5000/lakes', params={'sort': "florb"}).json()
[x['name'] for x in data[:5]]

"""
Explanation: Specifying a field other than name, area or elevation for the sort parameter should fail silently, defaulting to sorting alphabetically. Expected output: ['Ammersee', 'Arresoe', 'Atlin Lake', 'Balaton', 'Barrage de Mbakaou']
End of explanation
"""


# also on GitHub as “Homework6_api.py”

from flask import Flask, request, jsonify
import pg8000, decimal
import json

app = Flask(__name__)

def get_data(_type, _sort):
    conn = pg8000.connect(user="paul", database="mondial")
    cursor = conn.cursor()

    sort_result = ''
    if _sort == 'name':
        sort_result = 'ORDER BY name'
    elif _sort in ('area', 'elevation'):
        sort_result = 'ORDER BY ' + _sort + ' DESC'

    if _type:
        sql = "SELECT name, elevation, area, type FROM lake WHERE type = %s " + sort_result
        cursor.execute(sql, [str(_type)])
    else:
        sql = "SELECT name, elevation, area, type FROM lake " + sort_result
        cursor.execute(sql)

    # nested function
    def get_int(value):
        if isinstance(value, decimal.Decimal):
            return int(value)
        else:
            return None

    query_result = []
    for row in cursor.fetchall():
        item = {
            "name": row[0],
            "elevation": get_int(row[1]),
            "area": get_int(row[2]),
            "type": row[3]
        }
        query_result.append(item)

    return query_result


@app.route("/lakes")
def getLakes():
    lake_type = request.args.get('type', '')
    sort_by = request.args.get('sort', '')

    if sort_by and sort_by not in ('name', 'elevation', 'area'):
        sort_by = 'name'

    result = get_data(lake_type, sort_by)

    return jsonify(result)

app.run()

"""
Explanation: Paste your code
Please paste the code for your entire Flask application in the cell below, in case we want to take a look when grading or debugging your assignment.
End of explanation
"""


