"""
From an Excel table containing the following format in a tab called <Catalog1>,
generate a query to create a table for a serialized JSON to be used by Athena:

Excel table:

      A      |     B    |  C  |    D   |     E
---------------------------------------------------
this is text | this too | 123 | 122.12 | 12-12-2012


Result query string:

CREATE EXTERNAL TABLE IF NOT EXISTS test_one.Catalog1(
    `A STRING,
    `B STRING,
    `C FLOAT,
    `D FLOAT,
    `E TIMESTAMP
     )
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
'serialization.format' = '1'
) LOCATION '%s'
TBLPROPERTIES ('has_encrypted_data'='false');
"""

import xlrd
import datetime
import boto3

def sheet_to_dict(sheet, date_mode):
    """
    Parse an excel sheet and return it as a json, considering the
    first row is always containing the headers for the following
    values
    """
    dict_sheet = {}
    headers = []
    try:
        for row in range(sheet.nrows):
            for column in range(sheet.ncols):
                value_ = sheet.cell(row, column).value
                if row == 0:
                    headers.append(value_)
                    dict_sheet[value_] = []
                else:
                    value_ = value_best_fit(value_, date_mode)
                    dict_sheet[headers[column]].append(value_)
    except (ValueError, IndexError) as error:
        print('Invalid value or position attempted to be retrieved')
    return dict_sheet

def value_best_fit(value, date_mode):
   """
   Guess the best type for a cell and fortmat it; precedence order:
   date, float, string
   """
   if isinstance(value, str):
       return value_as_digit(value)
   else:
       try:
           as_date = xlrd.xldate_as_tuple(value, date_mode)
           value_ = datetime.datetime.strptime('{}-{}-{} {}:{}:{}'.format(
               as_date[0],
               as_date[1],
               as_date[2],
               as_date[3],
               as_date[4],
               as_date[5]
           ), '%Y-%m-%d %H:%M:%S')
           if value_.year <= 1900:
               value_ = value_as_digit(str(value))
           return value_
       except TypeError:
           print('Value is not a valid date, trying numeric formats instead')
           return value_as_digit(str(value))

def value_as_digit(value):
    """
    Convert a string to a digit if possible, float is preferred so no mismatches
    occur if a float is parsed after an integer and a column was set to integer
    """
    try:
        if any(char in value for char in (',', '.')):
            if value.replace(',', '').replace('.', '').isdigit():
                value = float(value.replace(',', '.'))
                return value
        elif value.isdigit():
            return float(value)
        else:
            return value
    except ValueError:
        print('The value is neither a string nor a number, I give up')
        return None

def type_as_string(value):
    """
    Possible types mapped as table types
    """
    if isinstance(value, datetime.datetime):
        return 'TIMESTAMP'
    elif isinstance(value, float):
        return 'FLOAT'
    return 'STRING'

def create_table_query(database, sheet, date_mode):
    """
    Table query to create a table using the headers and types
    identified from creating the JSON
    """
    data = sheet_to_dict(sheet, date_mode)
    create_query = 'CREATE EXTERNAL TABLE IF NOT EXISTS {}.{}('.format(
        database,
        sheet.name
    )
    for key in data:
        create_query += '\n    `{} {},'.format(key, type_as_string(data[key][0]))
    create_query = create_query[:-1]
    create_query += '''\n     )
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
'serialization.format' = '1'
) LOCATION '%s'
TBLPROPERTIES ('has_encrypted_data'='false');
'''
    return create_query, data

if __name__ == '__main__':
    book = xlrd.open_workbook('test_one.xlsx')
    for sheet in book.sheets():
        query, data = create_table_query('test_one', sheet, book.datemode)
        print(query)