import os

import psycopg2
from qgis.core import QgsApplication
from qgis.PyQt import uic
from qgis.PyQt.QtGui import QIcon
from qgis.PyQt.QtWidgets import QHeaderView, QMessageBox, QWizardPage
from xplordb.create_db import CreateDatabase
from xplordb.datamodel.person import Person
from xplordb.import_data import ImportData
from xplordb.import_ddl import ImportDDL
from xplordb.sqlalchemy.base import create_session as xplordb_create_session

from openlog.datamodel.connection.postgres_utils import create_session
from openlog.datamodel.connection.xplordb.xplordb_assay_interface import (
    XplordbAssayInterface,
)
from openlog.datamodel.connection.xplordb.xplordb_categories_interface import (
    XplordbCategoriesInterface,
)
from openlog.gui.create_database.wizard.connection import ConnectionPageWizard
from openlog.gui.create_database.wizard.user_tablemodel import (
    RoleItemDelegate,
    User,
    UserTableModel,
)
from openlog.toolbelt.checkable_combobox_input_dialog import (
    CheckableComboBoxInputDialog,
)
from openlog.toolbelt.password_item_delegate import PasswordItemDelegate


class DatabaseCreationPageWizard(QWizardPage):
    def __init__(self, connection_page: ConnectionPageWizard) -> None:
        """
        QWizard page to define database creation options

        """
        super().__init__()
        self._connection_page = connection_page
        self.setTitle("Database creation options")

        uic.loadUi(
            os.path.dirname(os.path.realpath(__file__))
            + "/database_creation_widget.ui",
            self,
        )

        self.user_table.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)

        self.user_table_model = UserTableModel(self)
        self.user_table.setModel(self.user_table_model)
        self.user_table.setItemDelegateForColumn(
            self.user_table_model.ROLE_COL, RoleItemDelegate(self)
        )
        self.user_table.setItemDelegateForColumn(
            self.user_table_model.PASSWORD_COL, PasswordItemDelegate(self)
        )
        self.user_table.setItemDelegateForColumn(
            self.user_table_model.PASSWORD_CONFIRMATION_COL, PasswordItemDelegate(self)
        )

        self.add_user_button.setIcon(QIcon(QgsApplication.iconPath("mActionAdd.svg")))
        self.add_user_button.clicked.connect(self.user_table_model.add_user)

        self.remove_user_button.setIcon(
            QIcon(QgsApplication.iconPath("mActionRemove.svg"))
        )
        self.remove_user_button.clicked.connect(lambda clicked: self._remove_user())

        # check full schema for database creation (no need to connect again with postgres)
        self.full_version_checkbox.setChecked(True)

        # Add admin user
        user = User(name="admin", role="xdb_admin", trigram="adm")
        self.user_table_model.add_user(user)

    def _remove_user(self) -> None:
        """
        Remove selected user from user table

        """
        while self.user_table.selectionModel().selectedRows():
            self.user_table_model.removeRow(
                self.user_table.selectionModel().selectedRows()[0].row()
            )

    def create_database(self) -> bool:
        """
        Create xplordb database with selected option

        Returns: True if database creation is successful, False otherwise

        """
        valid = True

        try:
            create_db = CreateDatabase(
                host=self._connection_page.get_connection_model().host,
                user=self._connection_page.get_connection_model().user,
                password=self._connection_page.get_connection_model().password,
                connection_db=self._connection_page.get_connection_model().database,
                port=int(self._connection_page.get_connection_model().port),
            )

            create_db.create_db(
                db=self.database_name_edit.text(),
                import_data=self.sample_import_checkbox.isChecked(),
                full_db=self.full_version_checkbox.isChecked(),
            )
            import_ddl = ImportDDL(
                db=self.database_name_edit.text(),
                user=self._connection_page.get_connection_model().user,
                password=self._connection_page.get_connection_model().password,
                host=self._connection_page.get_connection_model().host,
                port=self._connection_page.get_connection_model().port,
            )

            import_ddl.import_xplordb_schema(
                import_data=self.sample_import_checkbox.isChecked(),
                full_db=self.full_version_checkbox.isChecked(),
            )
            session = xplordb_create_session(
                database=self.database_name_edit.text(),
                host=self._connection_page.get_connection_model().host,
                port=self._connection_page.get_connection_model().port,
                user=self._connection_page.get_connection_model().user,
                password=self._connection_page.get_connection_model().password,
            )

            # create assay tables metadata and categories
            categories_iface = XplordbCategoriesInterface(
                engine=session.connection().engine,
                session=session,
                schema="ref",
            )
            categories_iface.categories_table.create(session.connection().engine)

            _ = XplordbAssayInterface(
                engine=session.connection().engine,
                session=session,
                categories_iface=categories_iface,
            )
            q = "ALTER SCHEMA display OWNER TO xdb_admin;"
            session.execute(q)
            session.commit()

            # grant permissions to public schema
            q = "GRANT USAGE, CREATE ON SCHEMA public TO xdb_admin;"
            session.execute(q)
            q = "GRANT USAGE ON SCHEMA public TO xdb_logger;"
            session.execute(q)
            q = "GRANT USAGE ON SCHEMA public TO xdb_viewer;"
            session.execute(q)
            session.commit()

        except psycopg2.Error as exc:
            valid = False
            QMessageBox.warning(self, "Database creation exception", str(exc))
        return valid

    def create_users(self) -> bool:
        """
        Create user defined in user table

        Returns: True is user creation is successful, False otherwise

        """
        connection = self._connection_page.get_connection()
        valid = True
        warning_list = []
        try:
            for user in self.user_table_model.get_user_list():
                if not self._role_exist(user.name):
                    self._create_user(user, connection)
                else:
                    warning_list.append(f"User '{user.name}' already exists.")
            connection.commit()
        except psycopg2.Error as exc:
            valid = False
            QMessageBox.warning(self, "User creation exception", str(exc))
            connection.rollback()

        if len(warning_list):
            QMessageBox.information(self, "User creation", "\n".join(warning_list))

        return valid

    @staticmethod
    def _create_user(user: User, connection) -> None:
        """
        Create user in xplordb database

        Args:
            user: user to create
            connection: psycopg2 connection
        """
        with connection.cursor() as cur:
            cur.execute(
                f"CREATE ROLE {user.name} WITH LOGIN NOSUPERUSER INHERIT "
                f"PASSWORD '{user.password}'"
            )
            cur.execute(f"GRANT {user.role} TO {user.name}")

    def create_persons(self) -> bool:
        """
        Create persons defined in user table (trigram)

        Returns: True is person creation is successful, False otherwise

        """
        connection = self._connection_page.get_connection_model()
        connection.database = self.database_name_edit.text()
        session, engine = create_session(connection)

        valid = True
        try:
            persons = []
            # Create a xplordb person if trigram defined
            for user in self.user_table_model.get_user_list():
                if user.trigram:
                    persons.append(Person(trigram=user.trigram, full_name=user.name))

            import_data = ImportData(session)
            import_data.import_persons_array(persons)
            session.commit()
        except ImportData.ImportException as exc:
            valid = False
            QMessageBox.warning(self, "Person creation exception", str(exc))
            session.rollback()

        return valid

    def _check_user(self) -> bool:
        """
        Check if created user are valid

        Returns: True if users are valid, False otherwise

        """
        valid = True
        warning_list = []
        for user in self.user_table_model.get_user_list():
            user_valid, reason = self._user_valid(user)
            if not user_valid:
                warning_list.append(reason)

        if len(warning_list):
            valid = False
            QMessageBox.warning(self, "User creation", "\n".join(warning_list))
        return valid

    def _user_valid(self, user: User) -> (bool, str):
        """
        Check if user is valid

        Args:
            user: user

        Returns: (bool, str) (boolean for user validity, str for reason of invalidity)

        """
        valid = True
        reason = ""
        if len(user.trigram) > 3:
            valid = False
            reason = f"user '{user.name}' : trigram is limited to 3 characters."
        elif len(user.password) == 0:
            valid = False
            reason = f"user '{user.name}' : password must be defined."
        elif user.password != user.password_confirmation:
            valid = False
            reason = f"user '{user.name}' : please confirm password definition."

        return valid, reason

    def _role_exist(self, role: str) -> bool:
        """
        Check if role exist in xplord database

        Args:
            connection: psycog2 connection
            role: role name

        Returns:

        """
        connection = self._connection_page.get_connection()
        exist = False
        with connection.cursor() as cur:
            cur.execute(f"SELECT * FROM pg_roles WHERE rolname='{role}'")
            if cur.fetchone():
                exist = True
        return exist

    def validatePage(self) -> bool:
        """
        Validate database creation params and create database if valid

        Returns: True if database creation is sucessful

        """
        valid = True
        if len(self.database_name_edit.text()) == 0:
            valid = False
            QMessageBox.warning(self, "Invalid name", "Define database name.")

        valid &= self._check_user()

        if valid:
            valid = self.create_database()

        if valid:
            valid = self.create_users()

        if valid:
            valid = self.create_persons()

        if valid:
            user_names = [user.name for user in self.user_table_model.get_user_list()]
            dialog = CheckableComboBoxInputDialog(
                user_names,
                user_names,
                self,
                "Create user connection",
                "Create connection for selected user ?",
            )
            if dialog.exec():
                selected_user = dialog.get_selected_values()
                for user in self.user_table_model.get_user_list():
                    if user.name in selected_user:
                        connection = self._connection_page.get_connection_model()
                        connection.name = (
                            f"{self.database_name_edit.text()}_{user.name}"
                        )
                        connection.user = user.name
                        connection.password = user.password
                        connection.database = self.database_name_edit.text()
                        connection.qgs_config_id = ""
                        self._connection_page.connection_widget.save_connection_to_settings(
                            connection
                        )

        return valid
