import datetime
import itertools
import json
from sqlalchemy import orm
from wrpylib.wrorm import Page, WrIntermapsSledrun, WrIntermapsReport, WrIntermapsReportHistory


def investigate(filename: str, write_back_formatted: bool):
    """Displays statistics about a Intermaps json file."""
    with open(filename) as fp:
        content = json.load(fp)
    regions = content['items']
    sledruns = list(itertools.chain.from_iterable(region['sledruns']['items'] for region in regions))
    sledrun_ids_list = [sledrun['id'] for sledrun in sledruns]
    sledrun_ids_set = set(sledrun_ids_list)
    duplicates = {sledrun_id for sledrun_id in sledrun_ids_set if sum(sr_id == sledrun_id for sr_id in sledrun_ids_list) >= 2}
    print(duplicates)
    print(max(map(len, sledrun_ids_set)))  # 36
    if write_back_formatted:
        with open(filename, 'w') as fp:
            json.dump(content, fp, indent='\t')


class UpdateError(RuntimeError):
    pass


def find_intermaps_sledrun(intermaps_sledrun_id: str, json_content: dict):
    """Returns iterator of tuples (region, sledrun) for given sledrun_id"""
    regions = json_content['items']
    for region in regions:
        for sledrun in region['sledruns']['items']:
            if sledrun['id'] == intermaps_sledrun_id:
                yield region, sledrun


def region_to_region_with_sledrun_dict(region: dict):
    region = region.copy()
    region.update({'sledruns': {sledrun['id']: sledrun for sledrun in region['sledruns']['items']}})
    return region


def json_content_to_region_dict(json_content: dict) -> dict:
    return {region['id']: region_to_region_with_sledrun_dict(region) for region in json_content['items']}


def update_wrintermapssledrun(session, json_content: dict):
    """Updates the wrintermapssledrun table from the wiki. By filling in the missing fields of rows as long as
    intermaps_sledrun_id and wr_page_id are set.
    If convert errors occur, an UpdateError exception
    is raised. No other exception type should be raised under normal circumstances.

    >>> import json
    >>> from sqlalchemy.engine import create_engine
    >>> engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=1')
    >>> Session = orm.sessionmaker(bind=engine)
    >>> with open('2019-12-06_090500.json') as fp:
    >>>     content = json.load(fp)
    >>> update_wrintermapssledrun(Session(), content)
    """
    q = session.query(WrIntermapsSledrun)
    for intermaps_sledrun in q:
        if intermaps_sledrun.intermaps_sledrun_name is None or intermaps_sledrun.intermaps_region_id is None or \
                intermaps_sledrun.intermaps_region_name is None or intermaps_sledrun.intermaps_country is None:
            region_json, sledrun_json = \
                next(find_intermaps_sledrun(intermaps_sledrun.intermaps_sledrun_id, json_content), (None, None))
            if region_json is None:
                raise UpdateError(
                    f'Sledrun with intermaps id {intermaps_sledrun.intermaps_sledrun_id} not found in JSON')
        if intermaps_sledrun.intermaps_sledrun_name is None:
            intermaps_sledrun.intermaps_sledrun_name = sledrun_json['name']
        if intermaps_sledrun.intermaps_region_id is None:
            intermaps_sledrun.intermaps_region_id = region_json['id']
        if intermaps_sledrun.intermaps_region_name is None:
            intermaps_sledrun.intermaps_region_name = region_json['names']['de']
        if intermaps_sledrun.intermaps_country is None:
            intermaps_sledrun.intermaps_country = region_json['countryCode']

        if intermaps_sledrun.wr_page_title is None:
            page = session.query(Page).get(intermaps_sledrun.wr_page_id)
            intermaps_sledrun.wr_page_title = page.page_title

    session.commit()


def update_wrintermapsreport(session, json_content: dict):
    # add current content of intermaps_report to intermaps_history
    pass

    # delete content of intermaps_report
    session.query(WrIntermapsReport).delete()

    # add JSON content to intermaps_report
    regions = json_content_to_region_dict(json_content)
    for sledrun in session.query(WrIntermapsSledrun):
        region = regions.get(sledrun.intermaps_region_id)
        if region is None:
            continue
        sledrun_json = region['sledruns'].get(sledrun.intermaps_sledrun_id)
        if sledrun_json is None:
            continue
        report = WrIntermapsReport()
        report.intermaps_sledrun_id = sledrun.intermaps_sledrun_id
        report.status = sledrun_json['status']
        report.last_update = region['lastUpdate']
        report.last_check = datetime.datetime.now()
        report.utc_offset = 1
        session.add(report)
    session.commit()


if __name__ == '__main__':
    from sqlalchemy.engine import create_engine
    engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=1')
    Session = orm.sessionmaker(bind=engine)
    filename = '/home/philipp/daten/Winterrodeln/Intermaps/intermaps_winterrodeln_2019-12-06_090501.json'
    with open(filename) as fp:
        content = json.load(fp)
    session = Session()
    update_wrintermapssledrun(session, content)
    update_wrintermapsreport(session, content)