import pandas as pd
import json


def get_index_from_sheet(sheet):
    cols_to_use = [
        'Data variable as written on entry form',
        'Excel column header',
        'Data values as written on entry form',
        'Data values to enter into excel',
        'Which forms?',
    ]
    cols_to_join = ['Data variable as written on entry form', 'Unnamed: 1', 'Unnamed: 2', 'Unnamed: 3']
    def joiner(row):
        bla = [s for s in row if isinstance(s, str) and s]
        return ' - '.join(bla) if bla else row.iloc[0]
    sheet['Data variable as written on entry form'] = sheet[cols_to_join].apply(joiner, axis=1)

    sheet = sheet[cols_to_use].reset_index()
    sheet.columns = ['group', 'form_variable', 'variable', 'form_value', 'value', 'used_on']
    sheet.fillna(method='pad', inplace=True)
    sheet.drop_duplicates(subset=['variable', 'form_value', 'value'], inplace=True)
    return sheet


def get_definitions_from_index(index):
    definitions = index.groupby('variable').apply(_grouper)
    definitions = definitions.reset_index(name='type_def')
    # TODO: Why do we have duplicates?
    definitions = definitions.drop_duplicates(subset=['variable'])
    return {
        row.variable: row.type_def
        for (index, row) in definitions.iterrows()
    }

def _get_mandatory_string(title):
    return {
        'title': title,
        'type': 'string',
        'minLength': 1,
    }

def _get_date_definition(title):
    return {
        'title': title,
        'type': 'string',
        'format': 'date'
    }

def _get_number_string_definition(title):
    return {
        'title': title,
        'type': 'number',
    }

def _get_temperature_definition(title):
    return {
        'title': title,
        'type': 'string',
        'pattern': '(\d+(\.\d+)?\w?[CF])|Ø',
    }

def _get_enum_definition(title, form_values, values):
    form_values, values = list(form_values), list(values)
    enum_names = [
        name if name != '[as written]' else values[i]
        for i, name in enumerate(form_values)]
    enum_type = 'optionalEnum' if '[as written]' in values else 'enum'
    return {
        'title': title,
        enum_type: values,
        enum_type + 'Names': enum_names,
        'type': 'string',
    }

def _grouper(g):
    title = str(g.form_variable.iloc[0])
    first_value = g.value.iloc[0]
    # Text or notgiven
    if set(g.value) == set(['[as written]', 'Ø']):
        return _get_mandatory_string(title)
    # Name field
    elif len(g.value) == 1 and first_value == '[name]':
        return _get_mandatory_string(title)
    # Date field
    elif 'dd-MMM-yyyy' in list(g.value):
        return _get_date_definition(title)
    # Number fields
    elif '#' in first_value and not ':' in first_value and not 'Temperature' in title:
        return _get_number_string_definition(title)
    # Temperature field
    elif 'Temperature' in title:
        return _get_temperature_definition(title)
    else:
        return _get_enum_definition(title, g.form_value, g.value)


if __name__ == '__main__':
    SHEET_PATH = '/Users/dedan/tmp/OU.OrangutanName.S.1.2017.xls'
    OUT_PATH = '/Users/dedan/projects/monkey-db/test/test-forms/definitions.json'
    all_sheets = pd.read_excel(SHEET_PATH, sheetname=None)
    index = get_index_from_sheet(all_sheets['INDEX'])
    definitions = get_definitions_from_index(index)
    with open(OUT_PATH, 'w') as f:
        json.dump(definitions, f, indent=2)



@@ -764,48 +764,67 @@
     
-sheet.fillna(method='pad', inplace=True)
+# Drop all the lines with no values in the end of the file.
 %0A   
@@ -830,35 +830,26 @@
   sheet.drop
-_duplicates
+na
 (subset=%5B'va
@@ -852,39 +852,58 @@
 %5B'va
-riab
 l
+u
 e'
+%5D
 , 
-'form_value', 'value'%5D
+inplace=True)%0A    sheet.fillna(method='pad'
 , in
@@ -1004,16 +1004,34 @@
 groupby(
+%5B'form_variable', 
 'variabl
@@ -1032,16 +1032,17 @@
 ariable'
+%5D
 ).apply(
@@ -1114,46 +1114,308 @@
 f')%0A
+%0A
     # 
-TODO: Why do we have duplicates?
+Drop duplicates.%0A    variable_counts = definitions.variable.value_counts()%0A    duplicates = list(variable_counts%5Bvariable_counts %3E 1%5D.index)%0A    if duplicates:%0A        print('%E2%9A%A0%EF%B8%8F  %7B%7D duplicated variables %5B%7B%7D%5D'.format(len(duplicates), duplicates))%0A        print(' ==%3E Dropping those variables!')
 %0A   
@@ -1474,24 +1474,25 @@
 variable'%5D)%0A
+%0A
     return %7B

Deal with duplicate variables in index (definitions) exporter.