import shutil

import xplordb
from qgis.PyQt.QtWidgets import QFileDialog, QMessageBox
from sqlalchemy import exc
from xplordb import sql_utils
from xplordb.datamodel.survey import Survey
from xplordb.schema import Schema

from openlog.core.desurveying import minimum_curvature_method
from openlog.datamodel.connection.openlog_connection import OpenLogConnection
from openlog.datamodel.connection.spatialite.database_object import convert_to_db_crs


class DataBaseUpdater:
    """
    Abstract class used to update database if needed.
    Designed to be instantiated as an attribute of OpenLogConnection.
    """

    def __init__(self, connection: OpenLogConnection) -> None:
        self.connection = connection
        self.expected_fields = [
            "hole_id",
            "data_set",
            "x",
            "y",
            "z",
            "dip",
            "azimuth",
            "planned_eoh",
            "srid",
            "project_srid",
            "eoh",
            "loaded_by",
            "load_date",
            "geom",
            "geom_trace",
            "planned_trace",
        ]
        self.missing_fields = None
        self.fields = None
        self.status = None
        self.crs = None
        self.message = "Your database need to be updated\nPlease select a file to create current database backup."
        self.end_message = "Update done! Please connect again to the database."
        self.steps = []

    def set_project_crs(self):
        self.crs = (
            self.connection.get_layers_iface().get_collar_layer().crs().postgisSrid()
        )

    def check_xplordb_version(self) -> bool:
        """
        Update can be done only if xplordb version is >= 0.13.4
        Returns True if version is compliant
        """
        v = xplordb.__version__
        return v == "0.13.4"

    def need_update(self):
        raise NotImplementedError

    def display_end_message(self):
        mb = QMessageBox()
        mb.setText(self.end_message)
        mb.setWindowTitle("Xplordb schema update")
        mb.exec()

    def backup_current_db(self):
        raise NotImplementedError

    def prepare_update(self, iface):
        """
        Make all checks before altering database.
        """

        # check xplordb version
        if not self.check_xplordb_version():
            self.status = False
            mb = QMessageBox()
            mb.setText(
                "Xplordb package must be updated (>= 0.13.4).\nIf it's already installed, please restart QGIS"
            )
            mb.setWindowTitle("Xplordb schema update")
            mb.setStandardButtons(QMessageBox.StandardButton.Ok)
            mb.exec()
            return
        # check if database need an update
        if not self.need_update():
            self.status = False
            return

        # backup
        backed_up = self.backup_current_db(iface)

        if not backed_up:
            self.status = False
            return

        self.status = True

    def update_database(self):
        raise NotImplementedError


class SpatialiteDataBaseUpdater(DataBaseUpdater):
    def __init__(self, connection: OpenLogConnection) -> None:
        """
        In Spatialite, display geometries are set in collar table.
        """
        super().__init__(connection)
        self.expected_fields += ["effective_geom", "planned_geom"]

    def need_update(self) -> bool:

        """
        Check if current database is up-to-date with xplordb schema.
        Return true if an update is needed.
        """

        # 1st update : 0.13.2
        q = "PRAGMA table_info(collar)"
        res = self.connection.session.execute(q).fetchall()
        fields = [x[1] for x in res]
        self.fields = fields
        self.missing_fields = [
            field for field in self.expected_fields if field not in self.fields
        ]

        result = len(self.missing_fields) > 0

        if result:
            self.steps.append("0.13.2")

        # 2nd update : 0.13.3
        ## dh.metadata table
        q = "SELECT count(*) FROM sqlite_master WHERE type='table' AND name='metadata';"
        res = self.connection.session.execute(q).fetchall()[0][0] == 0

        if res:
            self.steps.append("0.13.3")

        result |= res

        return result

    def backup_current_db(self, iface) -> bool:

        """
        Invite user to create a backup file before altering database.
        Returns True if it's done.
        """

        mb = QMessageBox()
        mb.setText(self.message)
        mb.setWindowTitle("Xplordb schema update")
        mb.setStandardButtons(
            QMessageBox.StandardButton.Cancel | QMessageBox.StandardButton.Ok
        )
        res = mb.exec()
        if res == QMessageBox.StandardButton.Cancel:
            return False

        filename, _ = QFileDialog.getSaveFileName(
            iface.mainWindow(),
            "Select file",
            "",
            "Spatialite database (*.db)",
        )
        if filename != "":
            shutil.copy(src=self.connection._file_path, dst=filename)
            return True

        return False

    def update_database(self, iface):
        """
        Method updating database.
        After creating missing columns, desurveying is performed on new trace geometries.
        """

        self.prepare_update(iface)
        if not self.status:
            return

        self.set_project_crs()

        # 1st update : 0.13.2
        if "0.13.2" in self.steps:

            definition = {
                "dip": {"type": "FLOAT", "value": -90},
                "azimuth": {"type": "FLOAT", "value": 0},
                "planned_eoh": {"type": "FLOAT", "value": "NULL"},
                "project_srid": {"type": "INTEGER", "value": self.crs},
                "planned_trace": "NULL",
                "effective_geom": "NULL",
                "planned_geom": "NULL",
            }

            # create and fill missing fields
            for col in self.missing_fields:

                if col in ["planned_trace", "effective_geom", "planned_geom"]:
                    q = f"SELECT addgeometrycolumn('collar', '{col}', {self.crs}, 'LINESTRINGZ', 'XYZ')"
                else:
                    q = f"ALTER TABLE collar ADD COLUMN {col} {definition[col]['type']} DEFAULT {definition[col]['value']} "
                self.connection.session.execute(q)
                self.connection.session.commit()

            # desurvey all geometries
            hole_ids = [
                f["hole_id"]
                for f in self.connection.get_layers_iface()
                .get_collar_layer()
                .getFeatures()
            ]

            for hole_id in hole_ids:

                # effective surveys
                surveys = self.connection.get_read_iface().get_surveys_from_collars(
                    [hole_id]
                )
                collar = self.connection.get_read_iface().get_collar(hole_id)
                if len(surveys) != 0:
                    # geom_trace (exact geometry)
                    geom = minimum_curvature_method(surveys, collar, False)
                    self.connection.set_collar_desurveying(hole_id, geom, "geom_trace")
                    self.connection.session.commit()
                    # effective_geom (display purpose)
                    geom = minimum_curvature_method(surveys, collar, True)
                    self.connection.set_collar_desurveying(
                        hole_id, geom, "effective_geom"
                    )
                    self.connection.session.commit()

                # planned survey : planned_trace (exact geometry)
                geom = minimum_curvature_method(None, collar, False)
                self.connection.set_collar_desurveying(hole_id, geom, "planned_trace")
                self.connection.session.commit()

                # planned survey : planned_geom (display purpose)
                geom = minimum_curvature_method(None, collar, True)
                self.connection.set_collar_desurveying(hole_id, geom, "planned_geom")
                self.connection.session.commit()

            # drop splitted tables and triggers
            q = """select name from sqlite_master
                    where name in (
                    select assay || '_' || name || '_trace' from assay_column)
                    and type = 'table'
                """
            tables_to_drop = self.connection.session.execute(q).fetchall()
            tables_to_drop = [t[0] for t in tables_to_drop]

            for table in tables_to_drop:
                # drop table
                q = f"""SELECT DropTable(NULL, '{table}', 1)"""
                self.connection.session.execute(q)
                self.connection.session.commit()

                # drop triggers
                q = f"""
                select name from sqlite_master
                where type = 'trigger' and name like '%{table}%'
                and tbl_name in (select assay from assay_column union select 'collar')
                """
                triggers = self.connection.session.execute(q).fetchall()
                triggers = [t[0] for t in triggers]
                for trigger in triggers:
                    q = f"DROP TRIGGER IF EXISTS {trigger}"
                    self.connection.session.execute(q)
                    self.connection.session.commit()

        # 2nd update : 0.13.3
        if "0.13.3" in self.steps:
            ## creation of metadata table + triggers
            self.connection._create_metadata_table()
            ## set existing collar in metadata table
            self.connection.session.execute(
                "INSERT INTO metadata (hole_id) SELECT hole_id FROM collar;"
            )
            self.connection.commit()

        self.status = True
        self.display_end_message()


class XplordbDataBaseUpdater(DataBaseUpdater):
    def __init__(self, connection: OpenLogConnection) -> None:
        super().__init__(connection)
        self.message = "Your database need to be updated\nYou have to be logged as the database owner\nBackup the database before update is strongly recommanded"

    def need_update(self) -> bool:

        """
        Check if current database is up-to-date with xplordb schema.
        Return true if an update is needed.
        """
        # new dh.collar columns
        q = """
        SELECT *
        FROM information_schema.columns
        WHERE table_schema = 'dh'
        AND table_name   = 'collar'

        """

        res = self.connection.session.execute(q).fetchall()
        fields = [x[3] for x in res]
        self.fields = fields
        self.missing_fields = [
            field for field in self.expected_fields if field not in self.fields
        ]

        result = len(self.missing_fields) > 0

        # display schema
        q = """
        select count(*)
        from information_schema.schemata
        where schema_name = 'display';
        """
        display = self.connection.session.execute(q).fetchall()[0][0]
        result |= display == 0

        if result:
            self.steps.append("0.13.2")

        # metadata table
        q = """
        SELECT count(*)
        FROM information_schema.tables
        WHERE table_schema = 'dh'
        AND table_name = 'metadata';
        """
        meta_table = self.connection.session.execute(q).fetchall()[0][0] == 0

        if meta_table:
            self.steps.append("0.13.3_metadata")

        result |= meta_table

        q = "SELECT COUNT(*) FROM pg_catalog.pg_type WHERE typname = 'spherical_data';"
        res = self.connection.session.execute(q).fetchall()[0][0] == 0

        if res:
            self.steps.append("0.13.3_spherical_type")

        result |= res

        q = "SELECT COUNT(*) FROM pg_catalog.pg_type WHERE typname = 'polarity';"
        res = self.connection.session.execute(q).fetchall()[0][0] == 0

        if res:
            self.steps.append("0.13.4")

        result |= res

        return result

    def backup_current_db(self, iface) -> bool:

        """
        Invite user to create a backup file before altering database.
        Returns True if it's done.
        """

        mb = QMessageBox()
        mb.setText(self.message)
        mb.setWindowTitle("Xplordb schema update")
        mb.setStandardButtons(
            QMessageBox.StandardButton.Cancel | QMessageBox.StandardButton.Ok
        )
        res = mb.exec()
        if res == QMessageBox.StandardButton.Cancel:
            return False

        return True

    def update_database(self, iface):
        """
        Method updating database.
        After creating missing columns, desurveying is performed on new trace geometries.
        """

        self.prepare_update(iface)
        if not self.status:
            return

        if "0.13.2" in self.steps:
            # 1st update : 0.13.2
            # add columns to dh.collar
            definition = {
                "dip": {"type": "FLOAT", "value": -90},
                "azimuth": {"type": "FLOAT", "value": 0},
                "planned_eoh": {"type": "FLOAT", "value": "NULL"},
                "project_srid": {"type": "INTEGER", "value": 4326},
                "planned_trace": "NULL",
            }

            # create and fill missing fields
            for col in self.missing_fields:

                if col == "planned_trace":
                    q = f"SELECT AddGeometryColumn('dh', 'collar', 'planned_trace', 4326, 'COMPOUNDCURVE', 4)"
                else:
                    q = f"ALTER TABLE dh.collar ADD COLUMN {col} {definition[col]['type']} DEFAULT {definition[col]['value']} "
                try:
                    self.connection.session.execute(q)
                except exc.ProgrammingError:
                    self.connection.session.rollback()
                    mb = QMessageBox()
                    mb.setText(
                        "You have insufficient privilege, please try again as database owner."
                    )
                    mb.setWindowTitle("Xplordb schema update")
                    mb.setStandardButtons(QMessageBox.StandardButton.Ok)
                    mb.exec()
                    return
                self.connection.session.commit()

            # replace functions
            s = Schema("dh")
            for function in s.function_list():
                pth = s.import_dir() / "functions" / function
                sql_utils.exec_conn_psql(
                    conn=self.connection.session.bind.raw_connection(), filename=pth
                )

            # create schema
            s = Schema("display")
            s.create_schema(self.connection.session.bind.raw_connection())

            # create display_collar table
            s.import_tables(self.connection.session.bind.raw_connection())

            # update display_collar table
            q = "INSERT INTO display.display_collar SELECT data_set, hole_id, eoh, planned_eoh, NULL AS effective_geom, NULL AS planned_geom FROM dh.collar;"
            self.connection.session.execute(q)
            self.connection.session.commit()

            # import fonctions
            for function in s.function_list():
                pth = s.import_dir() / "functions" / function
                sql_utils.exec_conn_psql(
                    conn=self.connection.session.bind.raw_connection(), filename=pth
                )

            ## replace/create triggers
            # dh.collar
            q = """
            CREATE OR REPLACE TRIGGER planned_trace_update
            AFTER INSERT OR UPDATE OF geom,
            planned_eoh, dip, azimuth ON dh.collar
            FOR EACH ROW
            EXECUTE FUNCTION dh.planned_trace_update();

            """
            self.connection.session.execute(q)
            self.connection.session.commit()

            q = """
            CREATE OR REPLACE TRIGGER trace_update_xyz
            AFTER INSERT OR UPDATE OF x,y,z,eoh,geom, hole_id
            ON dh.collar
            FOR EACH ROW
            EXECUTE FUNCTION dh.trace_update_xyz ();

            """
            self.connection.session.execute(q)
            self.connection.session.commit()

            # dh.surv
            q = """
            CREATE OR REPLACE TRIGGER trace_insert_surv
            AFTER INSERT ON dh.surv
            REFERENCING NEW TABLE AS new_table
            FOR EACH STATEMENT
            EXECUTE FUNCTION dh.trace_update_surv ();


            """
            self.connection.session.execute(q)
            self.connection.session.commit()
            q = """
            CREATE OR REPLACE TRIGGER trace_update_surv
            AFTER UPDATE ON dh.surv
            REFERENCING NEW TABLE AS new_table
            FOR EACH STATEMENT
            EXECUTE FUNCTION dh.trace_update_surv ();



            """
            self.connection.session.execute(q)
            self.connection.session.commit()
            q = """
            CREATE OR REPLACE TRIGGER trace_delete_surv
            AFTER DELETE ON dh.surv
            REFERENCING OLD TABLE AS new_table
            FOR EACH STATEMENT
            EXECUTE FUNCTION dh.trace_update_surv ();

            """
            self.connection.session.execute(q)
            self.connection.session.commit()

            # display.display_collar

            pth = s.import_dir() / "trigger" / "collar_trigger.sql"
            sql_utils.exec_conn_psql(
                conn=self.connection.session.bind.raw_connection(), filename=pth
            )

            # trigger desurveying
            q = "UPDATE dh.collar SET eoh = eoh;"
            self.connection.session.execute(q)
            self.connection.session.commit()

        # 2nd update : 0.13.3
        if "0.13.3_metadata" in self.steps:
            s = Schema("dh")
            # function
            pth = s.import_dir() / "functions" / "populate_metadata_table.sql"
            sql_utils.exec_conn_psql(
                conn=self.connection.session.bind.raw_connection(), filename=pth
            )

            # table + triggers
            pth = s.import_dir() / "table" / "metadata.sql"
            sql_utils.exec_conn_psql(
                conn=self.connection.session.bind.raw_connection(), filename=pth
            )

            # insert all collars
            q = "INSERT INTO dh.metadata (hole_id) SELECT hole_id FROM dh.collar;"
            self.connection.session.execute(q)
            self.connection.session.commit()

        if "0.13.3_spherical_type" in self.steps:
            s = Schema("assay")
            # function
            pth = s.import_dir() / "functions" / "structural_types.sql"
            sql_utils.exec_conn_psql(
                conn=self.connection.session.bind.raw_connection(), filename=pth
            )

        if "0.13.4" in self.steps:

            q = f"DROP TYPE IF EXISTS assay.spherical_data;"
            self.connection.session.execute(q)
            self.connection.session.commit()

            domains = ["assay.azimuth", "assay.dip", "assay.kind", "assay.polarity"]
            for domain in domains:
                q = f"DROP DOMAIN IF EXISTS {domain};"
                self.connection.session.execute(q)
                self.connection.session.commit()

            s = Schema("assay")
            # function
            pth = s.import_dir() / "functions" / "structural_types.sql"
            sql_utils.exec_conn_psql(
                conn=self.connection.session.bind.raw_connection(), filename=pth
            )

        self.status = True
        self.display_end_message()
