from flask import Flask, jsonify, request
import mysql.connector
import json

'''
Demo API using flask

Author: Tim Pierson, Dartmouth CS61, Spring 2026 

Requires installation
    mysql connector: pip3 install mysql-connector-python      (--break-system-packages if needed)
    Based on: https://dev.mysql.com/doc/connector-python/en/connector-python-example-connecting.html

    flask: pip3 install flask    (--break-system-packages if needed)

    Postman is also helpful: https://www.postman.com/downloads/

Usage: 
    Start flask running in a terminal: 
        flask --app app.py run --port 8080
    From browser: 
        localhost:8080/
        localhost:8080/restaurants

    Use browser Postman to try the following at http://localhost:8080/:
        GET /restaurants      -- see all restaurants
        GET /restaurants/<id> -- see one restaurant with ID
        POST /restaurants     -- insert one restaurant put data in Postman, select body and raw
        DELETE /restaurants/<id>  -- remove restaurant with ID 
'''

app = Flask(__name__)

# Load credentials from the JSON file
with open('db.json') as config_file:
    credentials = json.load(config_file)
credentials = credentials['localhost']

def get_db_connection():
    return mysql.connector.connect(host=credentials['host'], user=credentials['user'], 
           password=credentials['password'], database=credentials['database'])


#home route
@app.route('/')
def home():
    return "API is running!"


#get all restaurants (well first 10 here anyway, by RestaurantID)
@app.route('/restaurants', methods=['GET'])
def get_restaurants():
    try:
        cnx = get_db_connection()
        cursor = cnx.cursor()   
        query = ("SELECT RestaurantID, RestaurantName, Boro, CuisineDescription, InspectionAvg "
                 "FROM Restaurants r JOIN Cuisine c USING (CuisineID) "
                 "ORDER BY RestaurantID "
                 "LIMIT 10")
        cursor.execute(query)
        rows = cursor.fetchall()
        
        return jsonify(rows), 200 #status 200 = OK

    except Exception as e:
        # Log the error (optional, but recommended for debugging)
        print(f"Database error: {e}")
        
        # Return a JSON error message and a 500 status code
        return jsonify({"error": "Internal Server Error", "message": str(e)}), 500 #status 500 = Server Error
        
    finally:
        # Ensure the connection is closed even if an error occurs
        if 'cursor' in locals():
            cursor.close()
        if 'cnx' in locals():
            cnx.close()


#get a single restaurant by ID as a parameter
@app.route('/restaurants/<int:id>', methods=['GET'])
def get_restaurant(id):
    try:
        cnx = get_db_connection()
        cursor = cnx.cursor(prepared=True)  # use a prepared statement to avoid SQL injection!
        query = ("SELECT RestaurantID, RestaurantName, Boro, CuisineDescription, InspectionAvg "
                 "FROM Restaurants r JOIN Cuisine c USING (CuisineID) "
                 "WHERE RestaurantID = %s")
        cursor.execute(query, (id,))
        rows = cursor.fetchall()
        
        return jsonify(rows), 200 #status 200 = OK

    except Exception as e:
        # Log the error (optional, but recommended for debugging)
        print(f"Database error: {e}")
        
        # Return a JSON error message and a 500 status code
        return jsonify({"error": "Internal Server Error", "message": str(e)}), 500 #status 500 = Server Error
        
    finally:
        # Ensure the connection is closed even if an error occurs
        if 'cursor' in locals():
            cursor.close()
        if 'cnx' in locals():
            cnx.close()



# POST a new restaurant
@app.route('/restaurants', methods=['POST'])
def add_restaurant():
    try:
        data = request.get_json() #data provided by client
        # Basic validation
        if not data:
            return jsonify({"error": "No data provided"}), 400 # status 400 = Bad Request
        if data['Boro'] not in ['Manhattan', 'Brooklyn', 'Queens', 'Bronx', 'Staten Island']:
            return jsonify({"error": "Invalid boro"}), 400 # status 400 = Bad Request
        
        cnx = get_db_connection()
        cursor = cnx.cursor(prepared=True) 
        query = ("INSERT INTO Restaurants (RestaurantID, RestaurantName, Boro, Building, Street, "
            "ZipCode, Phone, Latitude, Longitude, CuisineID) "
            "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)")
        values = (data['RestaurantID'], data['RestaurantName'], data['Boro'], data['Building'], data['Street'], 
            data['ZipCode'], data['Phone'], data['Latitude'], data['Longitude'], data['CuisineID'],)
        cursor.execute(query, values)
        cnx.commit()
        new_id = cursor.lastrowid
        
        return jsonify({"id": new_id, "message": "Restaurant created"}), 201 #status 201 = Created
    except Exception as e:
        # Log the error (optional, but recommended for debugging)
        print(f"Database error: {e}")
        
        # Return a JSON error message and a 500 status code
        return jsonify({"error": "Internal Server Error", "message": str(e)}), 500 #status 500 = Server Error
        
    finally:
        # Ensure the connection is closed even if an error occurs
        if 'cursor' in locals():
            cursor.close()
        if 'cnx' in locals():
            cnx.close()

# DELETE a restaurant


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