import mysql.connector
from mysql.connector import errorcode
import json
import sys

'''
Demo to show that we can connect directly to a database via python.  Fetches data about a single restaurant
from the database, *without* a call to an API.

Author: Tim Pierson, Dartmouth CS61, Spring 2026 (based on code from prior terms)
Requires installation of mysql connector: pip3 install mysql-connector-python
Based on: https://dev.mysql.com/doc/connector-python/en/connector-python-example-connecting.html

Usage: python get_restaurants.py restaurant_name <localhost|remote host>
	restaurant_name is the name of the restaurant about which to fetch data
	localhost - (default if not supplied) to connect to MySQL on localhost
	remote host - to connect to MariaDB on computer science server 
	Example: python get_restaurants.py 'rosa mexicano' remote


NOTE: must be VPN'd into Dartmouth to access Dartmouth servers
NOTE: accesses database with credentials provided in db.json. Make sure to 
	  edit db.json with your user name and password to make the connection
'''


credentials_filename = "db.json" #file holding username and password to database

def read_database_credentials(credentials_filename, database="localhost"):
	#Read credentials from config file specified by credentials_filename
	#Input: name of file holding credentials
	#Output: dictionary of credentials with entries for username, password, host, and database
	#		 exit if file not found or database name is invalid

	#read credentials file
	credentials_file = open(credentials_filename,'r')
	credentials = json.load(credentials_file)
	credentials_file.close()

	#check for valid database
	if database not in (server for server in credentials):
			print("Invalid database specified")
			exit()

	#return credentials for the database parameter (normally "localhost" or "remote")
	return credentials[database]

def get_database_connection(credentials):
	#Get a connection to database
	#Input: dictionary with username, password, host, and database
	#Output: database connection or exit if connection not successful

	try:
		#make connection to database on host server using credentials provided
		cnx = mysql.connector.connect(user=credentials["user"], password=credentials["password"], 
									host=credentials["host"], database=credentials["database"])
		return cnx
	#catch exceptions, alert user to problem, then exit
	except mysql.connector.Error as err:
		if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
			print("Something is wrong with your user name or password")
		elif err.errno == errorcode.ER_BAD_DB_ERROR:
			print("Database does not exist")
		else:
			print(err)
		cnx.close()
		exit()

def get_restaurant_data(cnx, restaurant_name):
	# Query the database for database restaurants like restaurant_name
	# Input:
	#   cnx: connection to database
	#   restaurant_name: name of restaurant to fetch from database
	# Output: results of query printed to screen
	# Reference: https://pynative.com/python-mysql-execute-parameterized-query-using-prepared-statement/

	try:
		# get cursor from database connection and execute query using parameterized query and prepared statement
		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 RestaurantName LIKE %s")  # %s is a parameter
		cursor.execute(query, ('%' + restaurant_name + '%',))  # second item must be tuple, one entry per parameter

		# get column names
		cols = cursor.description  # column names plus other attributes stored in description, one tuple per column
		column_names = []  # store column names in this list
		for c in cols:  # loop over all columns
			column_names.append(c[0])  # column name is first item in tuple, extract it and append to column_names list

		# fetch ALL rows at once into a list of tuples
		rows = cursor.fetchall()

		# print rows, each row is a tuple of attributes
		# unlike iterating over the cursor, rows is a regular list
		# so you CAN loop over it multiple times if needed
		for row in rows:
			for i in range(len(column_names)):
				print(column_names[i] + ":" + str(row[i]))  # prints database attribute: value
			print()

	except mysql.connector.Error as err:
        # Handle specific database errors (like "Server gone away" or syntax errors)
		print(f"Database error: {err}")

	except Exception as e:
        # Handle non-database errors (like logic or index errors)
		print(f"An unexpected error occurred: {e}")

	finally:
        # 4. CRITICAL: Always close the cursor, even if an error happened
		if cursor is not None:
			cursor.close()		



if __name__ == '__main__':
	#check usage
	if len(sys.argv) < 2 or len(sys.argv) > 3:
		print("Usage: python get_restaurants.py restaurant_name <localhost|remote host> ")

	#Step 1: read database credentials from file
	#check which database to use (usually "localhost" or "remote")
	database = "localhost"
	if len(sys.argv) == 3:
		database = sys.argv[2]
	print(f'Reading database credentials from file: {credentials_filename} for database: {database}')
	credentials = read_database_credentials(credentials_filename, database)
	print("\tDone")

	#Step 2: get database connection using credentials from file
	print(f'Connecting to database {credentials["database"]} on {credentials["host"]}')
	cnx = get_database_connection(credentials)
	print("\tDone, got good connection") #would exit in get_database_connection if not successful
	
	#Step 3: query database for restaurant data
	restaurant = sys.argv[1]
	print("\nFetching data about",restaurant)
	get_restaurant_data(cnx,restaurant)

	#Step 4: close connection to database and exit
	cnx.close()