import sqlite3
import json
import os

# The name of the database we're going to read
database_file_name = "traveldata.db"

# The destination javascript file we're going to write the data into
output_file = 'where.js'

# SQL constants
TABLE_NAME = "destinations"
COLUMN_PLACE = "place"
COLUMN_DATA = "data"

# Global variables
database_connection : sqlite3.Connection = None
database_cursor : sqlite3.Cursor = None


def read_data_from_database():
    connect_to_database()
    sql_command = get_sql_command_to_extract_data()

    try:
        database_cursor.execute(sql_command)
        result = database_cursor.fetchall()
        return result
    except Exception as e:
        deal_with_error(f"Error executing SQL command: {sql_command}", e)

def write_data_to_file(data):
    # Each destination in the database consists of two fields: the place name and JSON data about the place.
    # The latitude and longitude are buried in the latter. To extract them we need to first convert the data
    # string to a python dictionary and then drill down into it

    try:
        delete_output_file_if_exists()
        file = open(output_file, "w")
        write_prefix_to_file(file)
    except Exception as e:
        print("Failed to create output file. Error was:", str(e))
        return

    for destination in data:
        if destination is None or len(destination) == 0 or destination[0] == "":
            print("Invalid destination found - skipping.")
            return

        place_name : str = destination[0]

        try:
            lat, lon = extract_lat_and_lon(destination[1])        
        except Exception as e:
            print("Could not extract data about", place_name, "- omitting from output file. Error was:", str(e))
            continue
        
        try:
            write_destination_to_file(file, place_name, lat, lon)
        except Exception as e:
            print("Failed to write", place_name, "to file. Aborting. Error was:", str(e))
            break

    try:
        write_suffix_to_file(file)
        file.close()
    except Exception as e:
        print("Failed to close file. Error was:", str(e))

def extract_lat_and_lon(destination_data_as_str):
    try:
        dict = json.loads(destination_data_as_str)
    except Exception as e:
        raise ValueError(f"Error converting data from string to dictionary: {str(e)}")

    try:
        lat = dict['features'][0]['properties']['lat']
        lon = dict['features'][0]['properties']['lon']
        return lat, lon
    except Exception as e:
        raise IndexError(f"Error getting latitude and longitude: {str(e)}")


# DATABASE FUNCTIONS

def connect_to_database():
    try:
        global database_connection
        global database_cursor
        database_connection = sqlite3.connect(database_file_name)
        database_cursor = database_connection.cursor()
    except Exception as e:
        deal_with_error(f"Error connecting to database: {database_file_name}", e)

def get_sql_command_to_extract_data():
    sql_command = f"SELECT * FROM {TABLE_NAME}"
    return sql_command


# OUTPUT FILE HANDLING

def write_prefix_to_file(file):
    file.write("myData = [\n")

def write_destination_to_file(file, place_name, lat, lon):
    formatted_destination : str = f"[{lat},{lon},'{place_name}'],\n"
    file.write(formatted_destination)
    print("Data for", place_name, "written to file.")

def write_suffix_to_file(file):
    file.write("];")

def delete_output_file_if_exists():
    try:
        os.remove(output_file)
    except:
        pass


# ERROR HANDLING

def deal_with_error(description, e):
    print(description)
    if e is not None:
        print(f'Exception was:', e)
    print('Aborting.')
    
    if database_connection is not None:
        database_connection.close()

    quit()


# MAIN

data = read_data_from_database()
write_data_to_file(data)

if database_connection is not None:
    database_connection.close()