import pyodbc
import pandas as pd
import random, string

import sqlite3

server = "moyasi98.f5.si,14339"
database = 'Data' 
username = 'web_app' 
password = 'web_app_pw' 
connectionStr = 'DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password

SYSTEM_SETTING_DB_PATH = "/var/www/bike-ojisan.f5.si/BikeParkingMapAPI/appsettings.db"

import traceback
import logging.config

from time import time
from elasticsearch import Elasticsearch

logging.config.fileConfig("/var/www/bike-ojisan.f5.si/logging.config")
logger = logging.getLogger()

class controller:
    def __init__(self):
        pass

    def healthcheck(self):
        strSQL = "select top 1 * from Bike_ParkingData_Main"
        with pyodbc.connect(connectionStr) as con:
            data = pd.read_sql(strSQL,con).to_dict("records")

        return len(data) > 0

    def getData(self,lat,lon):
        start = time()
        es_usr = self.getSystemSetting("ES_USER")
        es_usr_pw = self.getSystemSetting("ES_USER_PW")
        es_url = self.getSystemSetting("ES_URL")
        serach_range = self.getSystemSetting("serach_range")
        search_limit = self.getSystemSetting("search_limit")
        pivot = self.getSystemSetting("pivot")
        distance_boost = self.getSystemSetting("distance_boost")
        logger.info(f"{time()-start}")

        start = time()

        es_client = Elasticsearch(es_url,verify_certs=False,basic_auth=(es_usr,es_usr_pw))
        query_field = {
            "bool": {
                "must": [
                    {
                    "geo_distance": {
                        "distance": f"{serach_range}m",
                        "Location": {
                        "lat": float(lat),
                        "lon": float(lon)
                        }
                    }
                    }
                ],
                "should": [
                    {
                    "distance_feature": {
                        "field": "Location",
                        "origin": [
                        float(lon),
                        float(lat)
                        ],
                        "pivot": pivot,
                        "boost": int(distance_boost)
                    }
                    }
                ]
                }
        }
        script_fields = {
            "distance": {
                "script": {
                    "source": "doc['Location'].arcDistance(params.lat,params.lon)",
                    "params": {
                    "lat": float(lat),
                    "lon": float(lon)
                    }
                }
            }
        }
        logger.info(query_field)
        result = es_client.search(index="bike-ojisan",query=query_field,stored_fields=["_source"],script_fields=script_fields,size=int(search_limit))
        es_client.close()
        
        logger.info(f"{time()-start}")
        
        return result.raw["hits"]["hits"]

    def getSystemSetting(self,parameterName):
        strSQL = "select SettingValue from Bike_Mst_SystemSettings where SettingKey = ?"
        
        with sqlite3.connect(SYSTEM_SETTING_DB_PATH) as con:
            cur = con.cursor()
            cur.execute(strSQL,(parameterName,))
            val = cur.fetchone()[0]
        return val
    
    def checkargs(self,*args):
        for i in args:
            if i == None or len(i) == 0:
                return False
            else:
                continue
        return True
    
    def setToken(self,strua,ttltime):
        token = self.randomname(64)
        strSQL = "insert into Bike_Trn_Token values(getdate(),?,?,?)"
        v = (str(strua),ttltime,token)
        with pyodbc.connect(connectionStr) as con:
            con.execute(strSQL,v)
            con.commit()
        return token
    
    def checktoken(self,token):
        strSQL = "select count(*) from [Data].[dbo].[Bike_Trn_Token] where Token = ? and TTL >= getdate()"
        with pyodbc.connect(connectionStr) as con:
            cur = con.cursor()
            cur.execute(strSQL,token)
            c = cur.fetchval()
        return c > 0

    def randomname(self,n):
        randlst = [random.choice(string.ascii_letters + string.digits) for i in range(n)]
        return ''.join(randlst)
if __name__ == "__main__": 
    print(controller().getData(35.58617472570859,139.62406808656664))