#!/usr/bin/python3
"""Uses the wrsledrunjsoncache table to generate a list of sledruns containing
* sledrun name
* coordinates
* link to Winterrodeln.org article

Ensure you have called updatewrsledrunjsoncache.py previously so that wrsledrunjsoncache is up to date.

This script corresponds to the following SQL statement:

    SELECT
        JSON_EXTRACT(sledrun_json, '$.name') as Name,
        JSON_EXTRACT(sledrun_json, '$.position.longitude') as Longitude,
        JSON_EXTRACT(sledrun_json, '$.position.latitude') as Latitude,
        JSON_EXTRACT(sledrun_json, '$.entry_under_construction') as InArbeit,
        CONCAT('https://www.winterrodeln.org/wiki/', page_title) as Url
    FROM wrsledrunjsoncache JOIN page ON sledrun_page_id=page_id
    ORDER BY JSON_EXTRACT(sledrun_json, '$.name');


Command line usage:
$ python query_sledrun_list.py inifile1.ini ...

One or more .ini configuration files can be given,
their format is described in https://www.winterrodeln.org/trac/wiki/ConfigIni.
At the end, the following entries have to be present:

    [mysql]
    host=localhost
    dbname=philipp_winterrodeln_wiki
    user_name=philipp_www
    user_pass=YYYYYY

    [mediawiki]
    server=https://www.winterrodeln.org/
    article_path=wiki
"""
import argparse
import configparser
import csv
import json
import sys
from typing import List
import urllib.parse

from sqlalchemy.engine import create_engine, Connection


def create_sledrun_list(connection: Connection, api_url: str):
    sql = "SELECT " \
        "JSON_EXTRACT(sledrun_json, '$.name') as name, " \
        "JSON_EXTRACT(sledrun_json, '$.position.longitude') as latitude, " \
        "JSON_EXTRACT(sledrun_json, '$.position.latitude') as longitude, " \
        "JSON_EXTRACT(sledrun_json, '$.length') as length, " \
        "JSON_EXTRACT(sledrun_json, '$.walkup_time') as walkup_time, " \
        "JSON_EXTRACT(sledrun_json, '$.entry_under_construction') as under_construction, " \
        "page_title as title " \
        "FROM wrsledrunjsoncache JOIN page ON sledrun_page_id=page_id " \
        "ORDER BY JSON_EXTRACT(sledrun_json, '$.name');"
    rows = connection.execute(sql)

    writer = csv.writer(sys.stdout, lineterminator='\n')
    writer.writerow(['Name', 'Length', 'WalkupTime', 'Longitude', 'Latitude', 'InArbeit', 'Url'])
    for row in rows:
        title = row['title'].decode('utf-8')
        url = urllib.parse.urljoin(api_url, title)
        writer.writerow([json.loads(row['name']), row['length'], row['walkup_time'], row['latitude'], row['longitude'], row['under_construction'], url])


def query_sledrun_list(inifiles: List[str]):
    """
    :param inifiles: filename of an .ini file or a list of .ini files.
    """
    config = configparser.ConfigParser()
    config.read(inifiles)

    wiki_url = config.get('mediawiki', 'server')
    article_path = config.get('mediawiki', 'article_path')
    wiki_path = urllib.parse.urljoin(wiki_url, article_path)

    host = config.get('mysql', 'host')
    dbname = config.get('mysql', 'dbname')
    user = config.get('mysql', 'user_name')
    passwd = config.get('mysql', 'user_pass')

    engine = create_engine(f'mysql://{user}@{host}:3306/{dbname}?passwd={passwd}&charset=utf8mb4')
    create_sledrun_list(engine.connect(), wiki_path)


if __name__ == '__main__':
    parser = argparse.ArgumentParser(description='Generates a sledrun list in CSV format.')
    parser.add_argument('inifile', nargs='+', help='inifile.ini, see: https://www.winterrodeln.org/trac/wiki/ConfigIni')
    args = parser.parse_args()
    query_sledrun_list(args.inifile)