import json
import urllib
from pandas import read_sql_query, read_csv
import os

from fuzzywuzzy import fuzz
import re
import sys

from os.path import expanduser

import sqlite3
con = sqlite3.connect(expanduser("~")+"/notebooks/onsgeocodes.sqlite")

REQUEST = json.dumps({
'path' : {},
'args' : {}
})

# GET /ons/:code
request = json.loads(REQUEST)
code = request['path'].get('code')
 
q='SELECT * FROM codelist WHERE "GEOGCD"="{code}"'.format(code=code)
print('{"codes":%s}' % read_sql_query(q, con).to_json(orient='records'))

# GET /ons/current/:name
request = json.loads(REQUEST)
name = request['path'].get('name')

q='''
SELECT *  FROM codelist JOIN metadata 
WHERE "GEOGNM"="{name}" AND codeAbbrv=sheet AND codelist.STATUS="live"
'''.format(name=name)

print('{"codes":%s}' % read_sql_query(q, con).to_json(orient='records'))

RECONFILE=expanduser("~")+"/notebooks/mpnames.csv"#os.getenv('RECONFILE', 'mpnames.csv')
SEARCHCOL=os.getenv('SEARCHCOL','DisplayAs')
IDCOL=os.getenv('IDCOL','Member_Id')


# Matching threshold.
match_threshold = 70

# Basic service metadata. There are a number of other documented options
# but this is all we need for a simple service.
metadata = {
    "name": "Person Reconciliation Service",
    "identifierSpace": "http://rdf.freebase.com/ns/type.object.id",
    "schemaSpace": "http://rdf.freebase.com/ns/type.object.id",
    "view": {
    "url": "http://127.0.0.1:8889{{id}}"
    },
    "preview": {
        "url": "http://127.0.0.1:8889{{id}}/preview",
        "width": 430,
        "height": 300
    },
    "defaultTypes": [{"id": "/people/person", "name" : "Person"}],
}


# Read in person records from csv file.
reader = read_csv(RECONFILE)
records = reader.to_dict(orient='records')

def search(query):

    # Initialize matches.
    matches = []

    # Search person records for matches.
    for r in records:
        score = fuzz.token_set_ratio(query, r[SEARCHCOL])
        
        if score > match_threshold:
            matches.append({
                    "id": r[IDCOL],
                    "name": r[SEARCHCOL],
                    "score": score,
                    "match": query == r[SEARCHCOL],
                    "type": [{"id": "/people/person", "name": "Person"}]
                    })
    
    #print(sys.stderr, matches)
    return matches

# GET /parliament/reconcile/
request = json.loads(REQUEST)

#http://foo.com/bar/reconcile?queries={ "q0" : { "query" : "foo" }, "q1" : { "query" : "bar" } }

callback = request['args'].get('callback')
queries = request['args'].get('queries')

if callback:
    response = "%s(%s)" % (callback[0], json.dumps(metadata))
    print(response)
elif queries:
    results = {}
    queries = json.loads(urllib.parse.unquote((queries[0])))
    if isinstance(queries,dict): 
        results = {}
        for key in queries:
            results[key] = {"result": search(queries[key]['query'])}
        print(json.dumps(results))
    else:
        print(json.dumps({"result": []}))
else:
    print(json.dumps(metadata))