import pandas as pd

def get_lookup() -> pd.Series:
    mapping = """
    60747.Conductivity-15924
    60747.Conductivity-25925
    60747.Conductivity-35926
    60747.Conductivity-45927
    60747.Conductivity-55928
    60747.Soilmoisture-15788
    60747.Soilmoisture-25787
    60747.Soilmoisture-35875
    60747.Soilmoisture-45786
    60747.Soilmoisture-55785
    60747.Temperature-15897
    60747.Temperature-25898
    60747.Temperature-35899
    60747.Temperature-45900
    60747.Temperature-55901
    60748.Conductivity-15934
    60748.Conductivity-25935
    60748.Conductivity-35936
    60748.Conductivity-45937
    60748.Conductivity-55938
    60748.Soilmoisture-15811
    60748.Soilmoisture-25810
    60748.Soilmoisture-35809
    60748.Soilmoisture-45808
    60748.Soilmoisture-55807
    60748.Temperature-15876
    60748.Temperature-25877
    60748.Temperature-35878
    60748.Temperature-45879
    60748.Temperature-55880
    60749.Conductivity-15914
    60749.Conductivity-25915
    60749.Conductivity-35916
    60749.Conductivity-45917
    60749.Conductivity-55918
    60749.Soilmoisture-15778
    60749.Soilmoisture-25777
    60749.Soilmoisture-35776
    60749.Soilmoisture-45775
    60749.Soilmoisture-55774
    60749.Temperature-15886
    60749.Temperature-25888
    60749.Temperature-35889
    60749.Temperature-45890
    60749.Temperature-55891
    60753.Conductivity-15929
    60753.Conductivity-25930
    60753.Conductivity-35931
    60753.Conductivity-45932
    60753.Conductivity-55933
    60753.Soilmoisture-15793
    60753.Soilmoisture-25792
    60753.Soilmoisture-35791
    60753.Soilmoisture-45790
    60753.Soilmoisture-55789
    60753.Temperature-15902
    60753.Temperature-25903
    60753.Temperature-35904
    60753.Temperature-45905
    60753.Temperature-55906
    60754.Conductivity-15919
    60754.Conductivity-25920
    60754.Conductivity-35921
    60754.Conductivity-45922
    60754.Conductivity-55923
    60754.Soilmoisture-15784
    60754.Soilmoisture-25783
    60754.Soilmoisture-35782
    60754.Soilmoisture-45781
    60754.Soilmoisture-55779
    60754.Temperature-15892
    60754.Temperature-25893
    60754.Temperature-35894
    60754.Temperature-45895
    60754.Temperature-55896
    60755.Conductivity-15907
    60755.Conductivity-25909
    60755.Conductivity-35910
    60755.Conductivity-45911
    60755.Conductivity-55912
    60755.Soilmoisture-15773
    60755.Soilmoisture-25772
    60755.Soilmoisture-35771
    60755.Soilmoisture-45770
    60755.Soilmoisture-55769
    60755.Temperature-15881
    60755.Temperature-25882
    60755.Temperature-35883
    60755.Temperature-45884
    60755.Temperature-55885
    """.strip().split('\n')
    # Make a lookup series from the mapping of Adcon names to datasets
    # Replace with lookup series from excel file
    return pd.Series({
        s[:-4].strip(): int(s[-4:])
        for s in mapping[1:]
    }, name='dataset')


def adcon2pandas(filename: str='soil_moisture_02_03_2020_1.csv', lookup: pd.Series=get_lookup()) -> pd.DataFrame:
    """

    Imports an Adcon exported csv and creates an dataframe ready to append to the records table

    Parameters
    ----------
    filename
        The csv file to import
    lookup
        A mapping from VariableName to dataset id

    Returns
    -------
    Dataframe in the write shape to append to table record

    """

    # Get and polish dataframe from csv
    df = pd.read_csv(filename)
    df.columns = [s.strip().lower() for s in df.columns]
    df.time = pd.to_datetime(df.time)

    # Join dataframe with the lookup table to get dataset id'S
    df = df.join(lookup, 'variablename', how='inner')

    # Bring df into shape for import (add and remove columns)
    del df['variablename']
    del df['unit']

    return df

def write_to_db(session, df):
    conn = session.connection()
    df['is_error'] = False
    df['comment'] = None
    df['sample'] = None
    df['id'] = df.index
    df.to_sql('record', conn, if_exists='append', index=False, method='multi', chunksize=1000)

