# SPDX-FileCopyrightText: 2025 XLeitstelle Planen und Bauen <xleitstelle@gv.hamburg.de>
# SPDX-FileContributor: Anton Jacobsson <anton.jacobsson@init.de>
# SPDX-FileContributor: Tobias Kraft <tobias.kraft@gv.hamburg.de>
#
# SPDX-License-Identifier: EUPL-1.2

import logging

import psycopg2
from qgis.core import (
    QgsDataSourceUri,
    QgsProviderRegistry,
)

from xmas_plugin.util.metadata import PLUGIN_DIR_NAME

logger = logging.getLogger(PLUGIN_DIR_NAME)


def get_db_uri(connection_name: str, expand: bool = True) -> str:
    """Retrieve the database URI from QGIS Postgres connections."""
    provider_metadata = QgsProviderRegistry.instance().providerMetadata("postgres")
    if not provider_metadata:
        raise ValueError("Postgres provider not found in QGIS.")

    connections = provider_metadata.connections()
    if connection_name not in connections:
        raise ValueError(f"Connection '{connection_name}' not found.")

    conn = connections[connection_name]
    uri = QgsDataSourceUri(conn.uri())
    logger.debug(
        "Determined URI for connection '%s': %s",
        connection_name,
        uri.connectionInfo(expandAuthConfig=False),
    )
    return uri.connectionInfo(expandAuthConfig=expand)


def get_db_schema(connection_name: str) -> str | None:
    """Retrieve the configured database schema from QGIS Postgres connections."""
    provider_metadata = QgsProviderRegistry.instance().providerMetadata("postgres")
    if not provider_metadata:
        raise ValueError("Postgres provider not found in QGIS.")

    connections = provider_metadata.connections()
    if connection_name not in connections:
        raise ValueError(f"Connection '{connection_name}' not found.")

    conn = connections[connection_name]
    return conn.configuration().get("schema")


def test_db_connection(uri) -> None:
    """Test the database connection using the provided URI."""
    try:
        with psycopg2.connect(uri, connect_timeout=3) as conn:
            with conn.cursor() as cur:
                cur.execute("SELECT 1;")
    except Exception as e:
        logger.exception(f"Database connection test failed: {e}")
        raise e


def verify_db_connection_schema(uri) -> None:
    """Verify that the database connection schema contains required tables."""
    try:
        with psycopg2.connect(uri, connect_timeout=3) as conn:
            with conn.cursor() as cur:
                cur.execute(
                    "SELECT * FROM pg_catalog.pg_tables WHERE tablename = 'coretable';"
                )
                tables = cur.fetchall()

        if not tables:
            raise ValueError("Required table 'coretable' is missing in database")
    except Exception as e:
        logger.exception(f"Database schema verification failed: {e}")
        raise e
