import csv
from getpass import getpass
from mysql.connector import connect, Error

# NOTE! This file does not work outside the author's laptop as the database is localised on his device.
# To test this externally, download MySQL onto your own device and modify the 'try with connect' variables below.
# Author: Md Md Farid

choiceMade = False
attributesMade = False

while choiceMade == False:
    choice = input("Type 'new' to create a new database source or type 'load' to load an existing source. ")

    # Connect to the database.
    # Change host and database name if using this file outside the author's laptop.

    # Load an existing database.
    if choice == 'load':
        source = input("Type name of existing database: ")
        tableSource = input("Type name of existing table: ")
        nextSource = input("Enter data source filename to append company data: ")
        try:
            with connect(
                    host="localhost",
                    user=input("Enter username! "),
                    password=getpass("Enter password! "),
                    database=source,
            ) as connection:
                # Insert csv database content into a MySQL database table.
                use_db_query = "USE {}".format(source)
                insert_db_query ='LOAD DATA INFILE ' + "'" + nextSource + "'" + ' INTO TABLE ' + tableSource + ' FIELDS TERMINATED BY ' + "','" + " ENCLOSED BY " + "'"'' + ' LINES TERMINATED BY ' + "'\n'" + ' IGNORE 1 ROWS;'
                commit_db_query = "COMMIT"
                with connection.cursor() as cursor:
                    cursor.execute(use_db_query)
                    cursor.execute(insert_db_query)
                    cursor.execute(commit_db_query)
        except Error as e:
            print(e)
        choiceMade = True

    # Create a new database.
    elif choice == 'new':
        source = input("Type name of new database table: ")

        try:
            with connect(
                    host="localhost",
                    user=input("Enter username! "),
                    password=getpass("Enter password! "),
                    database="onesource",
            ) as connection:
                # Default schema based on selected existing database.
                new_db_query = ("CREATE TABLE {} ("
                                "handle text,"
                                "name text,"
                                "website text,"
                                "phone text,"
                                "industry text,"
                                "size text,"
                                "type text,"
                                "founded text,"
                                "address text,"
                                "city text,"
                                "state text,"
                                "post_code text,"
                                "country_code text,"
                                "rating text,"
                                "hours text);").format(source)
                # Insert csv database content into a MySQL database table.
                use_db_query = "USE {}".format(source)
                with connection.cursor() as cursor:
                    cursor.execute(new_db_query)
                    cursor.execute(use_db_query)
        except Error as e:
            print(e)
        choiceMade = True

    else:
        print("Invalid choice typed. Enter 'new' to create a new database source or type 'load'"
              "to load an existing source. ")