MK_QUERIES = {
    'timeseries': {'query': """
                       select date_time, value
                       from mk_timeseries as ts
                       join mk_master as master on (ts.curve_id = master.id)
                       where master.area='{0}' and
                       master.categories in {1} and
                       master.data_type='{2}' and
                       master.source_model='{3}' and {4}
                       reference_date between '{5}' and '{6}'
                       order by date_time
                       """
                   },
    'instances': {'query': """
                      select inst.date_time, avg(inst.value) as value
                      from mk_instances as inst inner join
                      (select date_time, curve_id, max(issue_date) as latest
                      from mk_instances
                      where reference_date between '{5}' and '{6}'
                      group by date_time, curve_id) as v1
                      join mk_master as master on (v1.curve_id=master.id)
                      where master.area='{0}' and
                      master.categories in {1} and
                      master.data_type='{2}' and
                      master.source_model in {3} and {4}
                      inst.curve_id=v1.curve_id and
                      inst.date_time=v1.date_time and
                      inst.issue_date=v1.latest
                      group by date_time
                      order by date_time
                      """,
                  'ids': {'ec00ens': [7661, 8057, 8237, 8273, 8327, 8363, 8399, 9585, 9819, 9873, 9909, 9945, 9169, 9783, 20098, 20368, 20386, 20422, 20584, 20638, 20692],
                          'ecmonthly': [9173, 9589, 9787, 9823, 9877, 9913, 9949, 7665, 8061, 8241, 8277, 8331, 8367, 8403, 20102, 20372, 20390, 20426, 20588, 20642, 20696]
                          }
                  }
}

MK_SPECS = {
    'consumption': {
        'area': '',
        'categories': ['con'],
        'data_type': 'f',
        'source_model': ['ec00ens', 'ecmonthly'],
        'filter': ''
    },
    'resudual_load': {
        'area': '',
        'categories': ['rdl'],
        'data_type': 'f',
        'source_model': ['ec00ens', 'ecmonthly'],
        'filter': ''
    },
    'nuclear_production': {
        'area': '',
        'categories': ['pro', 'nuc'],
        'data_type': 'af',
        'source_model': 'not a model',
        'filter': ''
    },
    'spot_price': {
        'area': '',
        'categories': ['pri', 'spot'],
        'data_type': 'f',
        'source_model': ['ec00'],
        'filter': 'unit="eur/mwh" and '
    },
    'ntc': {
        'area': '',
        'categories': ['exc', 'ntc'],
        'data_type': 'f',
        'source_model': ['not a model'],
        'filter': 'name like "%{}%" and '
    }
}

                for zone in zones:

                    table = 'instances' if spec['data_type'] == 'f' else 'timeseries'
                    categories = [' '.join(v) for v in permutations(spec['categories'])]
                    data_type = spec['data_type']
                    source_model = spec['source_model']
                    query_filter = spec['filter']

                    query = MK_QUERIES[table]['query'].format(zone,
                                                              '(' + str(categories).strip('[]') + ')',
                                                              data_type,
                                                              '(' + str(source_model).strip('[]') + ')',
                                                              query_filter,
                                                              start_date,
                                                              end_date)
                    #print(query)
                    col_name = '_'.join(spec['categories'] + [zone])
                    print('Collecting "{}" data from table "mk_{}"...'.format(col_name, table))
                    df = query_to_df(cursor=cursor, sql_query=query)

                    df = df.rename(columns={'value': col_name})
                    df.set_index('date_time', inplace=True)
                    df.index = pd.to_datetime(df.index)
                    df = df.astype(float)
                    df = df.resample('H').mean()
                    df = df.asfreq('h', 'bfill')
                    frames.append(df)