#!/usr/bin/python
# coding:utf-8

import sys
from collections import OrderedDict
from datetime import datetime as dt

sys.path.append('..')
from functions import connect


def get_calendar(cat, year):
    # d = {
    #       1: { 'date_race': '2019-03-15', 'flag': 'aus', 'country': 'Australie', 'city': 'Melbourne' },
    #       2: { 'date_race': '2019-03-29', 'flag': 'bah', 'country': 'Bahreïn', 'city': 'Sakhir' },
    #       'essais': {
    #           1: { 'dates': '18/02-21/02/2019', 'country': 'Espagne', 'flag': 'esp', 'city': 'Barcelone', 'circuit': 'Circuit de Catalunya' },
    #       },
    #       'synthese': 'blabla...'
    # }
    d = OrderedDict()
    cur = connect()

    # Getting all information on calendar
    cur.execute("SELECT A.nb, A.date_1, B.flag, B.country, B.city, B.full_name, B.page, A.double_eprix, A.event_name FROM calendar A INNER JOIN tracks B ON A.track = B.id WHERE A.cat = '%s' AND A.year = %s ORDER BY A.nb;" % (cat.upper(), year))
    res = cur.fetchall()
    for row in res:
        d[row[0]] = {}
        d[row[0]]['date_race'] = dt.strftime(row[1], '%d/%m/%Y')
        d[row[0]]['flag'] = row[2]
        d[row[0]]['country'] = row[3]
        d[row[0]]['event_name'] = row[8] if row[8] is not None else row[3]
        d[row[0]]['city'] = row[4]
        d[row[0]]['circuit'] = row[5] if row[5] is not None else ''
        d[row[0]]['page'] = row[6] if row[6] is not None else ''
        d[row[0]]['double_eprix'] = row[7] if row[7] is not None else ''

    # Get preseason testing infos
    d = get_essais(d, cat, year)
    # testing_cat = '%se' % cat.upper()
    # d['essais'] = {}
    # cur.execute("SELECT A.nb, A.date_1, A.date_2, A.date_3, A.date_4, B.flag, B.country, B.city, B.full_name FROM calendar A INNER JOIN tracks B ON A.track = B.id WHERE A.cat = '%s' AND A.year = %s ORDER BY A.nb;" % (testing_cat, year))
    # res = cur.fetchall()
    # for row in res:
    #     d['essais'][row[0]] = {}
    #     if row[4] is not None:
    #         d['essais'][row[0]]['dates'] = '%s-%s' % (dt.strftime(row[4], '%d/%m'), dt.strftime(row[1], '%d/%m/%Y'))
    #     else:
    #         d['essais'][row[0]]['dates'] = '%s-%s' % (dt.strftime(row[3], '%d/%m'), dt.strftime(row[1], '%d/%m/%Y'))
    #     d['essais'][row[0]]['flag'] = row[5]
    #     d['essais'][row[0]]['country'] = row[6]
    #     d['essais'][row[0]]['city'] = row[7]
    #     d['essais'][row[0]]['circuit'] = row[8] if row[8] is not None else ''

    # Get calendar synthese
    d['synthese'] = ''
    cur.execute("SELECT synthese FROM synthese WHERE cat = '%s' AND year = %s AND page_name = 'calendrier' AND special = 'synthese';" % (cat.upper(), year))
    res = cur.fetchall()
    for row in res:
        d['synthese'] = row[0].split('\n')

    return d

def get_calendar_sessions(cat, year):
    cur = connect()
    d = OrderedDict()
    # Getting all information on calendar
    # cur.execute('SELECT A.nb, B.session_date, C.flag, C.full_country, C.city, C.full_name, A.double_eprix, A.date_1 FROM calendar A INNER JOIN sessions B ON A.id = B.id_calendar INNER JOIN tracks C ON A.track = C.id WHERE A.cat = "%s" AND A.year = %s AND B.session_shortname = "C" ORDER BY A.nb;' % (cat, year))
    cur.execute('SELECT A.nb, A.date_1, C.flag, C.full_country, C.city, C.full_name, A.double_eprix, C.country, A.event_name FROM calendar A INNER JOIN tracks C ON A.track = C.id WHERE A.cat = "%s" AND A.year = %s ORDER BY A.nb;' % (cat, year))
    res = cur.fetchall()
    for row in res:
        d[row[0]] = {}
        d[row[0]]['date_race'] = dt.strftime(row[1], '%d/%m/%Y') if row[1] is not None else ''
        d[row[0]]['flag'] = row[2]
        d[row[0]]['event_name'] = row[8] if row[8] is not None else row[3]
        d[row[0]]['city'] = row[4]
        d[row[0]]['circuit'] = row[5] if row[5] is not None else ''
        d[row[0]]['double_eprix'] = row[6] if row[6] is not None else ''
        d[row[0]]['country'] = row[7] if row[7] is not None else ''

    get_essais(d, cat, year)
    get_synthese(d, cat, year)

    cur.close()
    return d

def get_essais(d, cat, year):
    # Get preseason testing infos
    if cat == 'indy':
        d['essais'] = {}
    else:
        cur = connect()
        testing_cat = '%se' % cat.upper()
        d['essais'] = {}
        cur.execute("SELECT A.nb, A.date_1, A.date_2, A.date_3, A.date_4, B.flag, B.country, B.city, B.full_name FROM calendar A INNER JOIN tracks B ON A.track = B.id WHERE A.cat = '%s' AND A.year = %s ORDER BY A.nb;" % (
            testing_cat, year))
        res = cur.fetchall()
        for row in res:
            d['essais'][row[0]] = {}
            if row[4] is not None:
                d['essais'][row[0]]['dates'] = '%s-%s' % (dt.strftime(row[1], '%d/%m'), dt.strftime(row[4], '%d/%m/%Y'))
            else:
                d['essais'][row[0]]['dates'] = '%s-%s' % (dt.strftime(row[1], '%d/%m'), dt.strftime(row[3], '%d/%m/%Y'))
            d['essais'][row[0]]['flag'] = row[5]
            d['essais'][row[0]]['country'] = row[6]
            d['essais'][row[0]]['city'] = row[7]
            d['essais'][row[0]]['circuit'] = row[8] if row[8] is not None else ''
    return d

def get_synthese(d, cat, year):
    # Get calendar synthese
    cur = connect()
    d['synthese'] = ''
    cur.execute("SELECT synthese FROM synthese WHERE cat = '%s' AND year = %s AND page_name = 'calendrier' AND special = 'synthese';" % (cat.upper(), year))
    res = cur.fetchall()
    for row in res:
        d['synthese'] = row[0].split('\n')
    return d

if __name__ == '__main__':
    get_calendar('fe', 2019)