import os
import logging
import csv
import time

import pymssql
import pandas as pd


class AutocompleteTHU:
    def __init__(self, conf):
        self.config = {
            "db_server": "",
            "db_user": "",
            "db_password": "",
            "db_name": "",

            "csv_file": os.path.join(os.path.dirname(os.path.realpath(__file__)), "tmp/thu.csv"),
            "csv_expire": 86400   # seconds
        }

        self.config.update(conf)

        # db is loaded
        self.db = None

    def sync_db(self):
        logging.info(__name__, "Sync database...")

        # connect
        conn = pymssql.connect(
            server=self.config["db_server"], 
            user=self.config["db_user"], 
            password=self.config["db_password"], 
            database=self.config["db_name"])

        # query
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM ReaderInfo")

        # export csv
        with open(self.config["csv_file"], "w") as f:
            writer = csv.writer(f, quoting=csv.QUOTE_ALL)
            writer.writerow(col[0] for col in cursor.description)
            for row in cursor:
                writer.writerow(row)
            f.close()

        # close
        cursor.close()
        conn.close()

    def load_db(self):
        if self.db is not None:
            return

        # sync if csv expired
        if (not os.path.isfile(self.config["csv_file"])) \
           or (time.time() - os.path.getmtime(self.config["csv_file"]) >= self.config["csv_expire"]):

           self.sync_db()

        # load db
        self.db = pd.read_csv(self.config["csv_file"])

    def run(self, fields):
        autocomplete = {}
        # get index
        uniid = fields.get("employeeNumber")
        if uniid:
            # fill fields
            self.load_db()
            rows = self.db[self.db["uniid"] == uniid]

            if len(rows):
                item = rows.iloc[0].to_dict()
                autocomplete = {
                    "uid": item["yhm"],
                    "cn": item["username"],
                    "employeeType": item["deptno_name"]
                }
        
        return {**autocomplete, **fields}