#!/usr/bin/python3.4
# $Id$
# $HeadURL$
"""Contains functions that maintain/update the cache tables."""
from sqlalchemy import schema
from sqlalchemy.sql import select
from sqlalchemy.sql.expression import func as sqlfunc
from osgeo import ogr
from wrpylib import mwdb, wrmwdb, mwmarkup, wrmwmarkup, wrvalidators


class UpdateCacheError(RuntimeError):
    pass


def update_wrsledruncache(connection):
    """Updates the wrsledruncache table from the wiki. If convert errors occur, an UpdateCacheError exception
    is raised. No other exception type should be raised under normal circumstances.
    
    >>> from sqlalchemy.engine import create_engine
    >>> engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=1')
    >>> update_wrsledruncache(engine.connect())
    """
    metadata = schema.MetaData()
    wrsledruncache = wrmwdb.wrsledruncache_table(metadata)
    page = mwdb.page_table(metadata)
    categorylinks = mwdb.categorylinks_table(metadata)
    revision = mwdb.revision_table(metadata)
    text = mwdb.text_table(metadata)
    class Sledrun:
        pass

    transaction = connection.begin()

    # Query all sled runs
    q = select([page, categorylinks, revision, text], (page.c.page_latest==revision.c.rev_id) & (text.c.old_id==revision.c.rev_text_id) & (categorylinks.c.cl_from==page.c.page_id) & (categorylinks.c.cl_to=='Rodelbahn'))
    sledrun_pages = connection.execute(q)
    # Original SQL:
    # sql = u"select page_id, rev_id, old_id, page_title, old_text, 'In_Arbeit' in (select cl_to from categorylinks where cl_from=page_id) as under_construction from page, revision, text, categorylinks where page_latest=rev_id and old_id=rev_text_id and cl_from=page_id and cl_to='Rodelbahn' order by page_title"
    
    # Delete all existing entries in wrsledruncache
    # We rely on transactions MySQL InnoDB
    connection.execute(wrsledruncache.delete())
    
    # Refill wrsledruncache table
    for sledrun_page in sledrun_pages:
        try:
            rodelbahnbox = wrvalidators.rodelbahnbox_from_str(sledrun_page.old_text)
            sledrun = wrmwmarkup.sledrun_from_rodelbahnbox(rodelbahnbox, Sledrun())
            sledrun.page_id = sledrun_page.page_id
            sledrun.page_title = sledrun_page.page_title
            sledrun.name_url = wrvalidators.sledrun_page_title_to_pretty_url(sledrun_page.page_title)
            sledrun.under_construction = connection.execute(select([categorylinks], (categorylinks.c.cl_from==sledrun_page.page_id) & (categorylinks.c.cl_to == 'In_Arbeit')).alias('x').count()).fetchone()[0] > 0
            connection.execute(wrsledruncache.insert(sledrun.__dict__))
        except ValueError as e:
            transaction.rollback()
            error_msg = "Error at sled run '{0}': {1}".format(sledrun_page.page_title, str(e))
            raise UpdateCacheError(error_msg, sledrun_page.page_title, e)
    transaction.commit()


def update_wrinncache(connection):
    """Updates the wrinncache table from the wiki. If convert errors occur, an UpdateCacheError exception
    is raised. No other exception type should be raised under normal circumstances.
    
    >>> from sqlalchemy.engine import create_engine
    >>> engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=1')
    >>> update_wrinncache(engine.connect())
    """
    metadata = schema.MetaData()
    wrinncache = wrmwdb.wrinncache_table(metadata)
    page = mwdb.page_table(metadata)
    categorylinks = mwdb.categorylinks_table(metadata)
    revision = mwdb.revision_table(metadata)
    text = mwdb.text_table(metadata)

    transaction = connection.begin()

    # Query all inns
    q = select([page, categorylinks, revision, text], (page.c.page_latest==revision.c.rev_id) & (text.c.old_id==revision.c.rev_text_id) & (categorylinks.c.cl_from==page.c.page_id) & (categorylinks.c.cl_to=='Gasthaus'))
    inn_pages = connection.execute(q)
        
    # Delete all existing entries in wrinncache
    # We rely on transactions MySQL InnoDB
    connection.execute(wrinncache.delete())
        
    # Refill wrinncache table
    for inn_page in inn_pages:
        try: 
            start, end, inn = wrmwmarkup.gasthausbox_to_inn(inn_page.old_text)
            inn.page_id = inn_page.page_id
            inn.page_title = inn_page.page_title
            inn.under_construction = connection.execute(select([categorylinks], (categorylinks.c.cl_from==inn_page.page_id) & (categorylinks.c.cl_to == 'In_Arbeit')).alias('x').count()).fetchone()[0] > 0 # It would be better to do this in the query above
            connection.execute(wrinncache.insert(inn.__dict__))
        except (RuntimeError, formencode.Invalid) as e:
            transaction.rollback()
            error_msg = "Error as inn '{0}': {1}".format(inn_page.page_title, str(e))
            raise UpdateCacheError(error_msg, inn_page.page_title, e)
    transaction.commit()


def update_wrreportcache(connection, page_id=None):
    """Updates the wrreportcache table.
    :param connection: sqlalchemy connection
    :param page_id: Updates only the reportcache table for the sledrun described on the Winterrodeln wiki page
        with the specified page_id. Use None for this parameter to update the whole table.

    >>> from sqlalchemy.engine import create_engine
    >>> engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=1')
    >>> update_wrreportcache(engine.connect())
    """
    metadata = schema.MetaData()
    wrreport = wrmwdb.wrreport_table(metadata)
    wrreportcache = wrmwdb.wrreportcache_table(metadata)
    transaction = connection.begin()

    # Delete the datasets we are going to update
    sql_del = wrreportcache.delete()
    if not page_id is None: sql_del = sql_del.where(wrreportcache.c.page_id == page_id)
    connection.execute(sql_del)

    def insert_row(connection, rowlist):
        if len(rowlist) == 0: return
        # Insert the report
        row = dict(rowlist[0])
        connection.execute(wrreportcache.insert(values=row))

    # Select the rows to update
    sql = 'select page_id, page_title, wrreport.id as report_id, date_report, `condition`, description, author_name, if(author_userid is null, null, author_username) as author_username from wrreport where {0}`condition` is not null and date_invalid > now() and delete_date is null order by page_id, date_report desc, date_entry desc'.format('' if page_id is None else 'page_id={0} and '.format(page_id))
    cursor = connection.execute(sql)
    page_id = None
    rowlist = []
    for row in cursor:
        if row.page_id != page_id:
            insert_row(connection, rowlist)
            page_id = row.page_id
            rowlist = []
        rowlist.append(row)
    insert_row(connection, rowlist)
    transaction.commit()


def update_wrmapcache(connection):
    """Updates the wrmappointcache and wrmappathcache tables from the wiki. If convert errors occur, an UpdateCacheError exception
    is raised. No other exception type should be raised under normal circumstances.
    
    >>> from sqlalchemy.engine import create_engine
    >>> engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=1')
    >>> # or: engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=1&passwd=XXXXX')
    >>> update_wrmapcache(engine.connect())
    """
    metadata = schema.MetaData()
    page = mwdb.page_table(metadata)
    categorylinks = mwdb.categorylinks_table(metadata)
    revision = mwdb.revision_table(metadata)
    text = mwdb.text_table(metadata)

    transaction = connection.begin()

    # Query all sledruns
    q = select([page, categorylinks, revision, text], (page.c.page_latest==revision.c.rev_id) & (text.c.old_id==revision.c.rev_text_id) & (categorylinks.c.cl_from==page.c.page_id) & (categorylinks.c.cl_to=='Rodelbahn'))
    sledrun_pages = connection.execute(q)
    # Original SQL:
    # sql = u"select page_id, rev_id, old_id, page_title, old_text, 'In_Arbeit' in (select cl_to from categorylinks where cl_from=page_id) as under_construction from page, revision, text, categorylinks where page_latest=rev_id and old_id=rev_text_id and cl_from=page_id and cl_to='Rodelbahn' order by page_title"
    
    # Delete all existing entries in wrmappointcache
    # We rely on transactions MySQL InnoDB
    connection.execute('delete from wrmappointcache')
    connection.execute('delete from wrmappathcache')
    
    # Refill wrmappointcache and wrmappathcache tables
    for sledrun_page in sledrun_pages:
        try: 
            start, content, endtag, end = mwmarkup.find_tag(sledrun_page.old_text, 'wrmap')
            if content is None:
                continue
            geojson = wrmwmarkup.parse_wrmap(sledrun_page.old_text[start:end])

            for feature in geojson['features']:
                properties = feature['properties']
                coordinates = feature['geometry']['coordinates']

                # Points
                if properties['type'] in wrmwmarkup.WRMAP_POINT_TYPES:
                    lon, lat = coordinates
                    label = properties.get('name')
                    point_types = {'gasthaus': 'hut', 'haltestelle': 'busstop', 'parkplatz': 'carpark', 'achtung': 'warning', 'foto': 'photo', 'verleih': 'rental', 'punkt': 'point'}
                    point_type = point_types[properties['type']]
                    sql = 'insert into wrmappointcache (page_id, type, point, label) values (%s, %s, POINT(%s, %s), %s)'
                    connection.execute(sql, (sledrun_page.page_id, point_type, lon, lat, label))

                # Paths
                elif properties['type'] in wrmwmarkup.WRMAP_LINE_TYPES:
                    path_types = {'rodelbahn': 'sledrun', 'gehweg': 'walkup', 'alternative': 'alternative', 'lift': 'lift', 'anfahrt': 'recommendedcarroute', 'linie': 'line'}
                    path_type = path_types[properties['type']]
                    path = ", ".join(["{0} {1}".format(lon, lat) for lon, lat in coordinates])
                    path = 'LineString({0})'.format(path)
                    if path_type == 'recommendedcarroute': continue
                    sql = 'insert into wrmappathcache (path, page_id, type) values (GeomFromText(%s), %s, %s)'
                    connection.execute(sql, (path, sledrun_page.page_id, path_type))

                else:
                    raise RuntimeError('Unknown feature type {0}'.format(properties['type']))
        except RuntimeError as e:
            error_msg = "Error at sledrun '{0}': {1}".format(sledrun_page.page_title, str(e))
            transaction.rollback()
            raise UpdateCacheError(error_msg, sledrun_page.page_title, e)
    transaction.commit()


def update_wrregioncache(connection):
    """Updates the wrregioncache table from the wiki.
    It relays on the table wrsledruncache to be up-to-date.
    No exceptions should be raised under normal circumstances.
    
    >>> from sqlalchemy.engine import create_engine
    >>> engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=1')
    >>> # or: engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=1&passwd=XXXXX')
    >>> update_wrregioncache(engine.connect())
    """
    metadata = schema.MetaData()
    wrregion = wrmwdb.wrregion_table(metadata)
    wrsledruncache = wrmwdb.wrsledruncache_table(metadata)
    wrregioncache = wrmwdb.wrregioncache_table(metadata)

    transaction = connection.begin()

    # Delete all existing entries in wrregioncache
    # We rely on transactions MySQL InnoDB
    connection.execute(wrregioncache.delete())
    
    # Query all combinations of sledruns and regions
    sel = select([wrregion.c.id.label('region_id'), sqlfunc.AsWKB(wrregion.c.border).label('border'), wrsledruncache.c.page_id, wrsledruncache.c.position_longitude, wrsledruncache.c.position_latitude], sqlfunc.contains(wrregion.c.border, sqlfunc.point(wrsledruncache.c.position_longitude, wrsledruncache.c.position_latitude)))
    ins = wrregioncache.insert()

    # Refill wrregioncache
    point = ogr.Geometry(ogr.wkbPoint)
    result = connection.execute(sel)
    for row in result:
        point.SetPoint(0, row.position_longitude, row.position_latitude)
        if point.Within(ogr.CreateGeometryFromWkb(row.border)):
            connection.execute(ins.values(region_id=row.region_id, page_id=row.page_id))

    # commit
    transaction.commit()