import asyncio
import functools
import logging
import os
import traceback
from dataclasses import dataclass
from pathlib import Path
import qasync
from qgis.core import QgsDataSourceUri, QgsProviderRegistry
from qgis.utils import iface
from qgis.PyQt.QtCore import QSettings, Qt, QTimer
from qgis.PyQt.QtWidgets import QLineEdit
from qgis.PyQt.QtGui import QIcon, QCloseEvent
from sqlalchemy import create_engine, text
from sqlalchemy.exc import DBAPIError
from sqlalchemy.ext.asyncio import create_async_engine
from SAGisXPlanung import BASE_DIR, VERSION, Session, SessionAsync
from SAGisXPlanung.core.connection import verify_db_connection, establish_session, IncompatibleDatabaseVersion
from SAGisXPlanung.ext.spinner import loading_animation
from SAGisXPlanung.ext.toast import Toaster
from SAGisXPlanung.gui.style import load_svg, ApplicationColor, with_color_palette, apply_color
from .basepage import SettingsPage
logger = logging.getLogger(__name__)

@dataclass
class RevisionMeta:
    current_revision: str = None
    expected_revision: str = None

    def is_valid(self) -> bool:
        return self.current_revision == self.expected_revision
style = '\nQToolButton[objectName="connnection_test_status_icon"], \nQToolButton[objectName="button_alert_icon"] {{\n    border: 0px;\n}}\nQTabWidget::pane {{\n    border: none;\n    border-top: 1px solid #e5e7eb;\n    position: absolute;\n}}\n/* Style the tab using the tab sub-control. Note that\n    it reads QTabBar _not_ QTabWidget */\nQTabBar::tab {{\n    border: none;\n    min-width: 20ex;\n    padding: 15px;\n    color: #4b5563;\n    cursor: pointer;\n}}\n\nQTabBar::tab:hover {{\n    background-color: #e5e7eb;\n    color: #111827;\n}}\n\nQTabBar::tab:selected {{\n    border-bottom: 2px solid #93C5FD;\n    background-color: #eff6ff;\n    color: #111827;\n}}\n\nQLabel[objectName="label_upgrade"] {{\n    color: {_label_color_mute};\n}}\n'

class DatabaseConfigPage(SettingsPage):

    def __init__(self, parent=None):
        super(DatabaseConfigPage, self).__init__(parent)
        self.ui = None
        self.database_revision = RevisionMeta()
        self.check_icon_base = QIcon(load_svg(os.path.join(BASE_DIR, 'gui/resources/check.svg'), color=ApplicationColor.Grey400))

    def setup_ui(self, ui):
        self.ui = ui
        self.ui.tab_database_actions.tabBar().setCursor(Qt.CursorShape.PointingHandCursor)
        self.ui.tab_database_actions.setCurrentIndex(0)
        self.db_create_options = [w for w in self.ui.db_create_group.children() if isinstance(w, QLineEdit)]
        self.ui.db_create.setEnabled(False)
        self.ui.db_create.clicked.connect(self.on_database_create_clicked)
        for w in self.db_create_options:
            w.textChanged.connect(self.db_create_options_changed)
        self.ui.connnection_test_status_icon.setIcon(self.check_icon_base)
        self.ui.connection_test_label.mousePressEvent = lambda e: asyncio.create_task(self.test_connection()) and None
        self.ui.button_alert_icon.setIcon(load_svg(os.path.join(BASE_DIR, 'gui/resources/warning.svg'), color=ApplicationColor.Grey600))
        self.ui.button_upgrade.setIcon(load_svg(os.path.join(BASE_DIR, 'gui/resources/upgrade.svg'), color=ApplicationColor.Tertiary))
        self.ui.button_upgrade.setCursor(Qt.CursorShape.PointingHandCursor)
        self.ui.database_version_frame.hide()
        self.ui.button_upgrade.clicked.connect(self.on_revision_update_clicked)
        self.ui.label_extra_info.hide()
        self.ui.tab_database_actions.setStyleSheet(style.format(_label_color_mute=ApplicationColor.Grey600))
        with_color_palette(self.ui.tab_database_actions, [ApplicationColor.Primary, ApplicationColor.Error, ApplicationColor.Success, ApplicationColor.Grey600], class_='QLabel')

    def setup_data(self):
        self.fill_connections()

    def closeEvent(self, event: QCloseEvent):
        self.save_settings()

    @qasync.asyncSlot()
    async def on_database_create_clicked(self):
        async with loading_animation(self.ui.tab_database) as spinner:
            self.ui.db_create.setEnabled(False)
            db = self.ui.db_name.text()
            username = self.ui.db_username.text()
            password = self.ui.db_password.text()
            host = self.ui.db_host.text()
            port = self.ui.db_port.text()
            self.ui.status_label.show()
            self.ui.status_label.setText('Datenbank erstellen...')
            self.ui.status_action.setText('')
            engine = create_async_engine(f'postgresql+asyncpg://{username}:{password}@{host}:{port}/postgres', isolation_level='AUTOCOMMIT')
            try:
                async with engine.connect() as conn:
                    await conn.execute(text(f'CREATE DATABASE {db}'))
                self.ui.status_label.setText('XPlanung Schema erstellen...')

                def create_schema():
                    engine = create_engine(f'postgresql://{username}:{password}@{host}:{port}/{db}')
                    with engine.begin() as conn:
                        with open(os.path.join(BASE_DIR, f'database/create_v{VERSION}.sql')) as file:
                            conn.execute(text(file.read()))
                loop = asyncio.get_event_loop()
                await loop.run_in_executor(None, create_schema)
            except DBAPIError as e:
                logger.exception(e)
                self.ui.status_label.setText(str(e))
                return
            except Exception as e:
                logger.exception(e)
                self.ui.status_label.setText(str(e))
                return
            finally:
                self.ui.db_create.setEnabled(True)
                spinner.stop()
            uri = QgsDataSourceUri()
            uri.setConnection(host, port, db, username, password)
            config = {'saveUsername': True, 'savePassword': True, 'estimatedMetadata': True, 'metadataInDatabase': True}
            metadata = QgsProviderRegistry.instance().providerMetadata('postgres')
            connection = metadata.createConnection(uri.uri(), config)
            connection.store(f'SAGis XPlanung: {db}')
            iface.browserModel().reload()
            self.ui.status_label.setText('Datenbank erstellt...')
            self.ui.status_action.setText('<a href="..link">Neue Konfiguration anwenden</a>')
            self.ui.status_action.mousePressEvent = functools.partial(self.status_action_apply_clicked, f'SAGis XPlanung: {db}')

    def db_create_options_changed(self, t):
        self.ui.db_create.setEnabled(not any((e.text() == '' for e in self.db_create_options)))

    def status_action_apply_clicked(self, new_connection_name: str, event):
        QSettings().setValue(f'plugins/xplanung/connection', new_connection_name)
        self.fill_connections()
        self.ui.status_action.setText('')
        self.ui.status_label.setText(f'Neue Konfiguration {new_connection_name} ausgewählt.')
        QTimer.singleShot(5000, lambda: self.ui.status_label.setText(''))
        self.ui.status_action.mousePressEvent = lambda _: None
        self.ui.tab_database_actions.setCurrentIndex(0)

    def fill_connections(self):
        self.ui.cbConnections.clear()
        qs = QSettings()
        qs.beginGroup('PostgreSQL/connections')
        conn_names = [cn for cn in qs.childGroups()]
        qs.endGroup()
        self.ui.cbConnections.addItems(conn_names)
        self.ui.cbConnections.currentIndexChanged.connect(self.on_connection_index_changed)
        index = 0
        for conn in conn_names:
            if conn == qs.value(f'plugins/xplanung/connection'):
                index = self.ui.cbConnections.findText(conn)
                break
        if index >= 0:
            self.ui.cbConnections.setCurrentIndex(index)
        elif conn_names:
            self.ui.cbConnections.setCurrentIndex(0)
        self.on_connection_index_changed()

    def save_settings(self):
        qs = QSettings()
        conn_name = str(self.ui.cbConnections.currentText())
        qs.setValue(f'plugins/xplanung/connection', conn_name)
        qs.setValue(f'PostgreSQL/connections/{conn_name}/username', self.ui.tbUsername.text())
        qs.setValue(f'PostgreSQL/connections/{conn_name}/password', self.ui.tbPassword.text())
        establish_session(Session)
        establish_session(SessionAsync)

    @qasync.asyncSlot()
    async def on_connection_index_changed(self):
        self.ui.connnection_test_status_icon.setIcon(self.check_icon_base)
        self.ui.connection_test_result_label.setText(f'Verbindungstatus unbekannt')
        apply_color(self.ui.connection_test_result_label, ApplicationColor.Grey600)
        self.ui.database_version_frame.hide()
        conn_name = str(self.ui.cbConnections.currentText())
        qs = QSettings()
        username = qs.value(f'PostgreSQL/connections/{conn_name}/username', '')
        password = qs.value(f'PostgreSQL/connections/{conn_name}/password', '')
        service = qs.value(f'PostgreSQL/connections/{conn_name}/service', '')
        self.ui.tbUsername.setText(username)
        self.ui.tbPassword.setText(password)
        self.ui.label_extra_info.setVisible(service != '')
        if service:
            t = f'PG Service Konfiguration <span style="color: {ApplicationColor.Secondary};">{service}</span> angewendet...'
            self.ui.label_extra_info.setText(t)
        qs.setValue(f'plugins/xplanung/connection', conn_name)
        establish_session(Session)
        establish_session(SessionAsync)

    @qasync.asyncSlot()
    async def on_revision_update_clicked(self):
        async with loading_animation(self.ui.tab_database):
            if self.database_revision.is_valid():
                return
            try:
                loop = asyncio.get_running_loop()
                await loop.run_in_executor(None, self.upgrade_database_revision)
                Toaster.showMessage(self, message='Datenbank erfolgreich aktualisiert!', corner=Qt.Corner.BottomRightCorner, margin=20, icon=None, closable=False, color='#ffffff', background_color='#404040', timeout=3000)
                self.ui.database_version_frame.hide()
                self.database_revision.current_revision = self.database_revision.expected_revision
            except Exception as e:
                apply_color(self.ui.connection_test_result_label, ApplicationColor.Error)
                self.ui.connection_test_result_label.setText(str(e))
                logger.error(e)
                logger.error(traceback.format_exc())
            await self.test_connection()

    def upgrade_database_revision(self):
        rev = self.database_revision.current_revision
        target_rev = self.database_revision.expected_revision
        sql_dir = Path(BASE_DIR) / Path('database')
        migration_files = [f.stem for f in sql_dir.iterdir() if f.is_file() and '_' in f.stem]
        engine = Session().get_bind()
        with engine.connect() as connection:
            revision_sequence = self._find_revision_sequence(migration_files, rev, target_rev)
            for revision_file in revision_sequence:
                with open(os.path.join(sql_dir, f'{revision_file}.sql'), 'r') as file:
                    sql_content = file.read()
                    connection.execute(text(sql_content))

    def _find_revision_sequence(self, sequence, start, end):
        result = []
        current = start
        lookup = {item.split('_')[0]: item for item in sequence}
        while current != end:
            if current in lookup:
                result.append(lookup[current])
                current = lookup[current].split('_')[1]
            else:
                break
        return result

    async def test_connection(self):
        self.save_settings()

        def _test_connection():
            result, meta = verify_db_connection(raise_exeptions=True)
            self.ui.connection_test_result_label.setText(f'XPlan-Datenbank: {meta.revision}')
            apply_color(self.ui.connection_test_result_label, ApplicationColor.Primary)
        spinner_args = {'radius': 4, 'lines': 16, 'line_length': 4}
        async with loading_animation(self.ui.connnection_test_status_icon, **spinner_args) as spinner:
            try:
                loop = asyncio.get_running_loop()
                await loop.run_in_executor(None, _test_connection)
                check_icon = QIcon(load_svg(os.path.join(BASE_DIR, 'gui/resources/check.svg'), color=ApplicationColor.Success))
                self.ui.connnection_test_status_icon.setIcon(check_icon)
                apply_color(self.ui.connection_test_result_label, ApplicationColor.Primary)
            except Exception as e:
                error_icon = QIcon(load_svg(os.path.join(BASE_DIR, 'gui/resources/error-outline.svg'), color=ApplicationColor.Error))
                self.ui.connnection_test_status_icon.setIcon(error_icon)
                apply_color(self.ui.connection_test_result_label, ApplicationColor.Error)
                self.ui.connection_test_result_label.setText(str(e))
                logger.error(e)
                if isinstance(e, IncompatibleDatabaseVersion):
                    self.database_revision.current_revision = e.current_revision
                    self.database_revision.expected_revision = e.expected_revisions[0]
                    self.ui.database_version_frame.show()