#! venv/bin/python

import os
import sys
import pprint
import json
from flask import Flask,render_template,request,url_for
from argparse import ArgumentParser
import psycopg

app = Flask(__name__)

class Convert_jinja_object:

    def __init__(self):
        self.year_map = [ "92Q2", "95Q0", "96Q0", "96Q1", "97Q1", "97Q3", "98Q1", "98Q3", "99Q1", "99Q3", "00Q1", "00Q3", "01Q1", "01Q2", "01Q3", "01Q4", "02Q1", "02Q3", "03Q1", "03Q3", "04Q1", "04Q3", "05Q1", "05Q3", "06Q1", "06Q3", "07Q1", "07Q3", "08Q1", "08Q3", "09Q1", "09Q3", "10Q1", "10Q3", "11Q1", "11Q3", "12Q1", "12Q3", "13Q1", "13Q3", "14Q1", "14Q3", "15Q1", "15Q3", "16Q1", "16Q3", "17Q1", "17Q3", "18Q1", "18Q3", "19Q1", "19Q3", "20Q1", "20Q3", "21Q1", "21Q3", "22Q1", "22Q3", "23Q1", "23Q3", "24Q1", "24Q3", "25Q1", "25Q3" ]

    def bits_to_years(self, x):
        if x == 0:
            return '/'
        years = ''
        index = 0
        while x > 0:
            if index > 0:
                years = years + ','
            start_off = (x&-x).bit_length()-1
            x = ~(x >> start_off)
            end_off = (x&-x).bit_length()-1
            x = ~(x >> (end_off + 1))
            years = years + (self.year_map[index + start_off])
            if end_off > 1:
                years = years + '-' + self.year_map[index + start_off + end_off - 1]
            index = index + start_off + end_off + 1
        return years

    def multirange_to_years(self, row):
    # Format of multi-ranges is {[low,high),[low,high],(low,high),(low,high)]}
    # Although Telefonbuch only ever inserts the [low,high) range types, so for now let's keep the parser simple
        years = []
        for range_item in row:
            low = range_item.lower
            high = range_item.upper - 1
            year = self.year_map[low]
            if (low != high):
                year += '-' + self.year_map[high]
            years.append(year)
        return ', '.join(years)

@app.route("/", methods=['GET', 'POST'])
def root():
    convert = Convert_jinja_object()

    query = "SELECT DISTINCT ON (id) presence_flag, reverse_flag, biz_flag, zip, nachname, vorname, zusaetze, strasse, hausnummer, verweise, ort, vorwahl, rufnummer, web, email, coords FROM Telefonbuch "
    where = ""
    params = []
    anyjoin = ""
    pastjoin = []
    rv = request.values.to_dict(flat=True)
        
    # pprint.pprint(request.values)

    for i in range(1,5):
        search = rv.get("s{0}_val".format(i))
        if not search:
            rv.pop("s{0}_val".format(i), '')
            rv.pop("s{0}_col".format(i), '')
            rv.pop("s{0}_op".format(i), '')
            rv.pop("s{0}_any".format(i), '')
            continue
        column = rv.get("s{0}_col".format(i))
        operator = rv.get("s{0}_op".format(i))
        isany = rv.get("s{0}_any".format(i))

        if not column in [ 'zip', 'nachname', 'vorname', 'zusaetze', 'strasse', 'hausnummer', 'verweise', 'ort', 'vorwahl', 'rufnummer', 'web', 'email', 'coords', 'id' ]: continue

        if not column in pastjoin:
            query = query + "INNER JOIN table_{0} ON Telefonbuch.id = table_{0}.telefonbuch_id ".format(column)
            pastjoin.append(column)

        if len(where):
            where = where + "AND "
        else:
            where = "WHERE "
        where = where + "table_{0}.value {1} %s ".format(column, {'equals': '=', 'equalsnot': '<>', 'contains': 'ILIKE', 'containsnot': 'NOT ILIKE', 'beginswith': 'LIKE', 'beginslike': 'ILIKE'}[operator])
        if not isany:
            if len(anyjoin) == 0 or "table_" + column == anyjoin:
                anyjoin = "table_" + column
            else:
                where = where + "AND " + anyjoin + ".offs = table_" + column + ".offs "
        if operator in ['contains','containsnot']:
            search = "%" + search + "%"
        if operator in ['beginswith', 'beginslike']:
            search = search + "%"
        params.append(search)

    range_min = int(rv.get("year_range_min", '0'))
    range_max = int(rv.get("year_range_max", len(convert.year_map) - 1))
    if range_min > range_max:
        range_max, range_min = range_min, range_max
        rv["year_range_min"] = range_min
        rv["year_range_max"] = range_max

    if range_min > 0 or range_max < len(convert.year_map) - 1:
        if len(where) > 0:
            where += "AND presence_flag && int4range({},{})" .format(range_min, range_max)
    else:
        rv.pop("year_range_min", 0)
        rv.pop("year_range_max", 0)

    limit = "10000"
    if rv.get("huge"):
        limit = "100000"

    if len(where) > 0:
        conn = psycopg.connect(dbname="telefonbuch", user="postgres", password="", host="127.0.0.1")
        cur = conn.cursor()
        print (query + where + ' LIMIT ' + limit)
        pprint.pprint( params )

        cur.execute(query + where + ' LIMIT ' + limit, params)
        rows = cur.fetchall()
    else:
        rows = []

    return render_template('index.html', rows=rows, request=request, rv=rv, convert=convert)

@app.route("/facebook", methods=['GET', 'POST'])
def facebook():

    query = "SELECT phone, id, first_name, last_name, gender, location, originally_from, relationship_state, ocupation, email, birthday FROM facebook "
    where = ""
    params = []
    anyjoin = ""
    pastjoin = []
    rv = request.values.to_dict(flat=True)
        
    pprint.pprint(request.values)

    for i in range(1,5):
        search = rv.get("s{0}_val".format(i))
        if not search:
            rv.pop("s{0}_val".format(i), '')
            rv.pop("s{0}_col".format(i), '')
            rv.pop("s{0}_op".format(i), '')
            rv.pop("s{0}_any".format(i), '')
            continue
        column = rv.get("s{0}_col".format(i))
        operator = rv.get("s{0}_op".format(i))
        isany = rv.get("s{0}_any".format(i))

        if not column in [ 'phone', 'first_name', 'last_name', 'gender', 'location', 'originally_from', 'relationship_state', 'ocupation', 'email', 'id' ]: continue

        if column == 'phone':
            search = search.lstrip('+')

        if len(where):
            where = where + "AND "
        else:
            where = "WHERE "
        where = where + "{0} {1} %s ".format(column, {'equals': '=', 'equalsnot': '<>', 'contains': 'ILIKE', 'containsnot': 'NOT ILIKE', 'beginswith': 'LIKE', 'beginslike': 'ILIKE'}[operator])
        if operator in ['contains','containsnot']:
            search = "%" + search + "%"
        if operator in ['beginswith', 'beginslike']:
            search = search + "%"
        params.append(search)

    limit = "10000"
    if rv.get("huge"):
        limit = "100000"

    if len(where) > 0:
        conn = psycopg.connect(dbname="tbuch", user="postgres", password="", host="127.0.0.1")
        cur = conn.cursor()
        print (query + where + ' LIMIT ' + limit)
        pprint.pprint( params )

        cur.execute(query + where + ' LIMIT ' + limit, params)
        rows = cur.fetchall()
    else:
        rows = []

    return render_template('facebook.html', rows=rows, request=request, rv=rv)

def column_and_op_to_where(column, operator):
    return "{0} {1} %s ".format(column, {'equals': '=', 'equalsnot': '<>', 'contains': 'ILIKE', 'containsnot': 'NOT ILIKE', 'beginswith': 'LIKE', 'beginslike': 'ILIKE'}[operator])

def search_and_operator_to_search(search, operator):
    if operator in ['contains','containsnot']:
        search = "%" + search + "%"
    if operator in ['beginswith', 'beginslike']:
        search = search + "%"
    return search


if __name__ == "__main__":
    parser = ArgumentParser(description="AVON")
    parser.add_argument("-H", "--host", help="Hostname of the Flask app " + "[default %s]" % "127.0.0.1", default="127.0.0.1")
    parser.add_argument("-P", "--port", help="Port for the Flask app " + "[default %s]" % "5000", default="5000")
    args = parser.parse_args()

    app.run(host=args.host, port=int(args.port))