#!/usr/bin/env python3
import sys
import csv

def main():
    num_args = len(sys.argv)
    exec_mode = ""
    file_name = ""
    table_name = ""
    if (num_args > 1):
        exec_mode = sys.argv[1]
    if (num_args > 2):
        file_name = sys.argv[2]
    if (num_args > 3): 
        table_name = sys.argv[3]
    
    if exec_mode == "":
        print_error("Please run with an exec mode")
        sys.exit()
    
    if not is_valid_mode(exec_mode): 
        print_error(f"\"{exec_mode}\" is not a valid exec mode")
        sys.exit()
    
    if file_name == "":
        print_error("Please specify a csv file name")
        sys.exit()
        
    if table_name == "": 
        print_error("Please specify a database table name")
        sys.exit()
    
    with open(file_name, 'r') as file:
        reader = csv.reader(file)
        db_fields = next(reader)
    
        db_properties = ""
        db_properties_types = ""
        for field in db_fields:
            field = "_or_".join(field.split("/"))
            field = "_".join(field.lower().split(" "))
    
            db_properties += f"{field},"
            db_properties_types += f"{field}\tSTRING,\n"
        
        db_properties = db_properties[:-1]
        db_properties_types = db_properties_types[:-2]
    
        query = ""
        for row in reader:
            row_data = "\",\"".join(row)
            row_data = f"\"{row_data}\""
            query += f"({row_data}),\n"
    
        query = query[:-2]
    
        create_command = f"CREATE TABLE {table_name} (\n{db_properties_types}\n);"
        insert_command = f"INSERT INTO {table_name} ({db_properties}) VALUES \n{query};"
        query_all_command = f"SELECT * FROM {table_name};"
    
    if is_create_mode(exec_mode):
        print(create_command)
    
    if is_insert_mode(exec_mode):
        print(insert_command)


def get_usage():
    file_name = sys.argv[0]
    return f"Usage: {file_name} [exec_mode] [csv_file_name] [db_table_name]\n\
            Exec modes:\n\
            -c: Create table\n\
            -i: Insert values into db_table_name (will assume 1st row is property names)\n\
            -a: All of the above\n"

def print_error(e):
    print(f"ERROR: {e}\n\
            {get_usage()}")

def is_create_mode(mode):
    return mode == "-a" or mode == "-c"

def is_insert_mode(mode):
    return mode == "-a" or mode == "-i"

def is_valid_mode(mode):
    possible_exec_modes = ["-a", "-i", "-c"]
    return mode in possible_exec_modes

if __name__ == "__main__":
    main()