# -*- coding: utf-8 -*-
import sqlite3
from shutil import copyfile

import psycopg2
from qgis.PyQt.QtWidgets import QApplication

from kfrm_tool.include.db.timeout import timeout
from kfrm_tool.include.File_Class import ChFile_Exists
from kfrm_tool.include.loading_dialog import LoadingDialog
from kfrm_tool.include.setting.path_list import DefaultPath

"""
   DB Connection 클래스를 싱글톤으로 구성하여 
   여러 클래스에서 SQLiteConnection을 호출하여도 초기에 호출된 한번만 Connection이 걸리고 
    후에 호출 되어도 Connection이 걸리지 않도록 구성. 
"""


class SingleTon(type):
    _instances = {}

    def __call__(self, *args, **kwargs):
        if self not in self._instances:
            self._instances[self] = super(SingleTon, self).__call__(*args, **kwargs)
        return self._instances[self]


class SQLiteConnection(object, metaclass=SingleTon):
    def __init__(self):
        self.path = DefaultPath()

        global DB_PATH, EMPTY_DB, DB_DLL_PATH
        DB_PATH = self.path.getSqliteDBPath()
        EMPTY_DB = self.path.getEmptySqliteDBPath()
        DB_DLL_PATH = self.path.getSpatialDllPath()

        self.connectionDB()

    def isDB(self):
        flag = ChFile_Exists(DB_PATH)
        if not flag:
            copyfile(EMPTY_DB, DB_PATH)
        return flag

    def connectionDB(self):
        flag = self.isDB()  # DB 존재 유무 체크

        global _connection, _cursor
        _connection = sqlite3.connect(DB_PATH, timeout=120, check_same_thread=False)
        _connection.enable_load_extension(True)
        _connection.load_extension("mod_spatialite")
        _cursor = _connection.cursor()
        self.baseCreateTable()

        return _connection, _cursor

    def closeDB(self):
        _connection.close()

    def clearDB(self):  # 인벤토리 구축하면서 사용했던 테이블 내용 지우기.
        sql = """
            PRAGMA writable_schema = 1;
            DELETE FROM sqlite_master WHERE (
                                name like "%"||(select table_name from flood_table_list)||"%" or
                                name like "%vehicle%" or name like "%popu%" or name like "%agricul%" or 
                                name like "%build%" or name like "%flood%" or name like "%loss%"
                                or name="extent_grid") COLLATE NOCASE;
            DELETE FROM geometry_columns;
            DELETE FROM geometry_columns_auth;
            DELETE FROM geometry_columns_statistics;
            DELETE FROM geometry_columns_field_infos;
            DELETE FROM spatialite_history;
            DELETE FROM sqlite_sequence WHERE name!="spatialite_history";
            DELETE FROM geometry_columns_time;
            PRAGMA writable_schema = 0;
        """
        self.excScript(sql)
        self.excNone("Vacuum;")  # sql문에 한꺼번에 넣어 실행하면 오류남.

    def baseCreateTable(self):
        sqlFile = self.path.getSpatialSqlPath()
        with open(sqlFile) as f:
            sql = f.read()
            self.excScript(sql)

    def commit(self):
        _connection.commit()

    def exeDecorator(func):
        def decorated(*args, **kwargs):
            try:
                return func(*args, **kwargs)
            except Exception as e:
                _connection.rollback()
                raise e

        return decorated

    @exeDecorator
    def excValue(self, query, type=True):  # retrun 값 있음 : select
        if type:
            _cursor.execute(query)
            return _cursor.fetchall()
        else:
            return _cursor.execute(query)

    @exeDecorator
    def excNone(self, query):  # return 값 없음 : update, insert, delete...
        _cursor.execute(query)
        self.commit()

    @exeDecorator
    def excMany(
        self, query, content
    ):  # insert시 한번에 값 여러개 넣기 ([1], [1]) <-이런 형태로 전달 해야 함.
        _cursor.execute("BEGIN TRANSACTION;")
        _cursor.executemany(query, tuple(content))
        _cursor.execute("END TRANSACTION;")
        self.commit()

    @exeDecorator
    def excQueryMany(
        self, query
    ):  # 여러개의 쿼리를 날릴때 한꺼번에 쿼리를 날리고 후에 Commit.
        for q in query:
            _cursor.execute(q)

        self.commit()

    @exeDecorator
    def excScript(
        self, query
    ):  # 여려개의 쿼리를 한번에 날릴 때. 하나의 쿼리가 끝나면 ;으로 표시. 위 excQueryMany 대용.
        _cursor.executescript(query)
        self.commit()


class PostgreSQLConnection(object, metaclass=SingleTon):
    def __init__(self):
        global HOST, DB_NAME, USER_NAME, PORT, _pconnection, _pcursor
        self.host = ""
        DB_NAME = "krm_database"
        USER_NAME = "krm_user"
        PORT = "5432"
        self.password = ""

        _pconnection = None
        _pcursor = None

    def setHost(self, host):
        self.host = host

    def setPw(self, pw):
        self.password = pw

    @timeout(7)
    def connect(self):
        global _pconnection, _pcursor
        _pconnection = psycopg2.connect(
            f"host={self.host} dbname={DB_NAME} user={USER_NAME} password={self.password} port={PORT}"
        )
        _pcursor = _pconnection.cursor()

    def connectionDB(self):
        try:
            # 연결되는 7초동안 dialog창을 띄워 사용자에게 표시
            loading = LoadingDialog("Main DB connecting")
            loading.setLabel("Connecting to main server.", True)
            loading.setLabel(
                "If not connected within 7 seconds, it will time out message...", False
            )
            loading.show()

            QApplication.processEvents()  # 넣어주지 않으면 dialog창에 아무것도 표시 안됨.
            self.connect()

        except Exception as e:
            e = (
                "Failed to connect to main DB. Check your network connection or contact your administrator."
                if str(e)
                else "Passwords do not match"
            )
            raise Warning(e)
        finally:
            loading.close()

    def isConnection(self):
        flag = True if _pconnection else False
        return flag

    def closeDB(self):
        _pconnection.close()

    def commit(self):
        _pconnection.commit()

    def excValue(self, query):  # retrun 값 있음 : select
        try:
            _pcursor.execute(query)
            value = _pcursor.fetchall()
            self.commit()
            return value
        except Exception as e:
            raise Warning(e)

    def excNone(self, query):  # retrun 값 없음 : update, insert, delete
        try:
            _pcursor.execute(query)
            self.commit()
        except Exception:
            raise Warning(E)

    def getHost(self):
        return self.host

    def getDbName(self):
        return DB_NAME

    def getUserName(self):
        return USER_NAME

    def getPassword(self):
        return self.password

    def getPort(self):
        return PORT
