from qgis.core import (
    Qgis,
    QgsExpressionContextUtils,
    QgsFeatureRequest,
    QgsProject,
    QgsProviderRegistry,
)
from qgis.PyQt import QtCore
from qgis.PyQt.QtWidgets import QDialog, QDialogButtonBox, QListWidget, QVBoxLayout
from qgis.utils import iface

from folios_wizard.qt_utils import tr

FOLIO_VARIABLES = [
    "folio_layout_name",
    "folio_print_scale",
    "folio_text_scale",
    "folio_title",
    "folio_subtitle",
    "folio_map_name",
    "folio_description",
    "folio_field1",
    "folio_field2",
    "folio_field3",
    "folio_field4",
]


def _normalize_text(value):
    """Return a normalized string."""
    return (value or "").strip()


def _extract_db_path_from_uri(uri):
    """
    Extract the GeoPackage / SQLite file path from a QGIS datasource URI.

    Supported examples:
        /path/to/file.gpkg|layername=metadata
        /path/to/file.gpkg|layerid=3
        dbname='/path/to/file.gpkg' table="metadata"
    """
    uri = _normalize_text(uri)

    if not uri:
        return ""

    # OGR / generic URI with pipe suffix
    if "|" in uri:
        return uri.split("|", 1)[0]

    # SQLite / SpatiaLite style
    if "dbname=" in uri:
        value = uri.split("dbname=", 1)[1].strip()

        if value.startswith("'"):
            end = value.find("'", 1)
            if end > 0:
                return value[1:end]

        if value.startswith('"'):
            end = value.find('"', 1)
            if end > 0:
                return value[1:end]

        return value.split(" ", 1)[0]

    return uri


def _layer_matches_tablename(layer, tablename="metadata"):
    """
    Return True if the layer/table appears to use the requested table name.
    """
    if layer is None or not hasattr(layer, "dataProvider"):
        return False

    target = tablename.lower()
    layer_name = _normalize_text(layer.name()).lower()
    uri = _normalize_text(layer.dataProvider().dataSourceUri()).lower()

    if layer_name == target:
        return True

    if "layername={}".format(target) in uri:
        return True

    if 'table="{}"'.format(target) in uri:
        return True

    if "table='{}'".format(target) in uri:
        return True

    return False


def find_metadata_layer(tablename="metadata"):
    """
    Find a loaded QGIS layer/table matching the metadata table name.
    Works for spatial and non-spatial layers/tables.
    """
    for layer in QgsProject.instance().mapLayers().values():
        if _layer_matches_tablename(layer, tablename):
            return layer
    return None


def get_metadata_source_uri(tablename="metadata"):
    """
    Return the datasource URI of the loaded metadata layer/table.
    """
    layer = find_metadata_layer(tablename)
    if layer is None:
        return ""
    return _normalize_text(layer.dataProvider().dataSourceUri())


def iter_project_geopackage_paths():
    """
    Yield unique GeoPackage paths used by layers/tables in the current project.
    """
    seen = set()

    for layer in QgsProject.instance().mapLayers().values():
        if not hasattr(layer, "dataProvider"):
            continue

        uri = _normalize_text(layer.dataProvider().dataSourceUri())
        if not uri:
            continue

        db_path = _extract_db_path_from_uri(uri)
        if not db_path:
            continue

        if not db_path.lower().endswith(".gpkg"):
            continue

        if db_path in seen:
            continue

        seen.add(db_path)
        yield db_path


def geopackage_has_table(db_path, tablename="metadata") -> bool:
    provider_metadata = QgsProviderRegistry.instance().providerMetadata("ogr")
    if provider_metadata is None:
        raise RuntimeError("ogr provider metadata not available")

    connection = provider_metadata.createConnection(db_path, {})
    ok = False

    try:
        sql = (
            "SELECT table_name "
            "FROM gpkg_contents "
            "WHERE lower(table_name) = lower('{}')".format(tablename)
        )
        result = list(connection.executeSql(sql))
        ok = bool(len(result))
        return ok
    finally:
        provider_metadata.disconnect()


def find_project_geopackage_with_table(tablename="metadata"):
    """
    Search all GeoPackages used by project layers and return the first one
    containing the requested table.
    """
    for db_path in iter_project_geopackage_paths():
        try:
            if geopackage_has_table(db_path, tablename=tablename):
                return db_path
        except Exception:
            continue

    return ""


def get_metadata_db_path(tablename="metadata"):
    """
    Return the GeoPackage / SQLite path to use for metadata.

    Strategy:
    1. If a loaded layer/table explicitly uses `tablename`, use its datasource.
    2. Otherwise, scan all GeoPackages used by project layers and return the
       first one containing a table named `tablename`.
    """
    source_uri = get_metadata_source_uri(tablename)
    if source_uri:
        return _extract_db_path_from_uri(source_uri)

    return find_project_geopackage_with_table(tablename)


def get_metadata_layer_info(tablename="metadata"):
    """
    Return diagnostic information about metadata discovery.
    """
    layer = find_metadata_layer(tablename)
    if layer is not None:
        return {
            "found": True,
            "discovery_mode": "loaded_layer",
            "name": layer.name(),
            "provider": layer.providerType(),
            "source_uri": layer.dataProvider().dataSourceUri(),
            "db_path": _extract_db_path_from_uri(layer.dataProvider().dataSourceUri()),
            "is_spatial": bool(hasattr(layer, "isSpatial") and layer.isSpatial()),
            "fields": [field.name() for field in layer.fields()],
        }

    db_path = find_project_geopackage_with_table(tablename)
    if db_path:
        return {
            "found": True,
            "discovery_mode": "project_geopackage_scan",
            "name": tablename,
            "provider": "spatialite",
            "source_uri": "",
            "db_path": db_path,
            "is_spatial": None,
            "fields": [],
        }

    return {
        "found": False,
        "discovery_mode": "not_found",
        "name": tablename,
        "provider": None,
        "source_uri": "",
        "db_path": "",
        "is_spatial": None,
        "fields": [],
    }


def clear_project_metadata_variables():
    """
    Remove existing metadata_* project variables and known folio variables.
    """
    project = QgsProject.instance()
    scope = QgsExpressionContextUtils.projectScope(project)

    for key in scope.variableNames():
        if key.startswith("metadata_"):
            try:
                QgsExpressionContextUtils.removeProjectVariable(project, key)
            except Exception:
                pass

    for key in FOLIO_VARIABLES:
        try:
            QgsExpressionContextUtils.removeProjectVariable(project, key)
        except Exception:
            pass


def read_metadata_rows_direct(tablename="metadata", include_geometry=False):
    """
    Read metadata rows directly from the loaded QGIS layer/table.

    Works whether the source has a geometry or not.
    If include_geometry is True, add '_geometry_wkt' when available.
    """
    layer = find_metadata_layer(tablename)
    if layer is None:
        return []

    field_names = [field.name() for field in layer.fields()]
    is_spatial = bool(hasattr(layer, "isSpatial") and layer.isSpatial())

    rows = []
    request = QgsFeatureRequest()

    for feature in layer.getFeatures(request):
        row = {field_name: feature[field_name] for field_name in field_names}

        if include_geometry:
            if is_spatial and feature.hasGeometry():
                row["_geometry_wkt"] = feature.geometry().asWkt()
            else:
                row["_geometry_wkt"] = None

        rows.append(row)

    return rows


def read_metadata_rows_ogr(tablename="metadata"):
    """
    Read metadata rows through an OGR SQL connection.

    This works whether `metadata` is spatial or non-spatial, provided the
    target table exists in the selected GeoPackage.
    """
    db_path = get_metadata_db_path(tablename)
    if not db_path:
        return []

    provider_metadata = QgsProviderRegistry.instance().providerMetadata("ogr")
    if provider_metadata is None:
        raise RuntimeError("OGR provider metadata not available")

    connection = provider_metadata.createConnection(db_path, {})

    try:
        result = connection.executeSql(
            'SELECT * FROM "{}" ORDER BY 1'.format(tablename)
        )
        return list(result)
    finally:
        provider_metadata.disconnect()


def read_metadata_rows_sqlite(tablename="metadata"):
    """
    Read metadata rows through a SQLite / SpatiaLite SQL connection.

    This works whether `metadata` is spatial or non-spatial, provided the
    target table exists in the selected GeoPackage.
    """
    db_path = get_metadata_db_path(tablename)
    if not db_path:
        return []

    provider_metadata = QgsProviderRegistry.instance().providerMetadata("spatialite")
    if provider_metadata is None:
        raise RuntimeError("SpatiaLite provider metadata not available")

    connection = provider_metadata.createConnection(db_path, {})

    try:
        result = connection.executeSql(
            'SELECT * FROM "{}" ORDER BY 1'.format(tablename)
        )
        return list(result)
    finally:
        provider_metadata.disconnect()


def metadata_rows_to_pairs(rows):
    """
    Convert rows into (key, value) pairs.

    Supported input formats:
    - dict rows, e.g. {'key': 'title', 'value': 'Project A'}
    - tuple/list rows, compatibility mode:
      row[1] -> key, row[2] -> value
    """
    pairs = []

    for row in rows:
        key = None
        value = None

        if isinstance(row, dict):
            lowered = {str(k).lower(): k for k in row.keys()}

            key_field = None
            for candidate in (
                "key_value",
                "key",
                "name",
                "code",
                "variable",
                tr("key"),
                tr("name"),
                tr("code"),
                tr("variable"),
            ):
                if candidate in lowered:
                    key_field = lowered[candidate]
                    break

            value_field = None
            for candidate in (
                "value_data",
                "value",
                "content",
                "text",
                tr("value"),
                tr("content"),
                tr("text"),
            ):
                if candidate in lowered:
                    value_field = lowered[candidate]
                    break

            if key_field is not None and value_field is not None:
                key = row[key_field]
                value = row[value_field]

        elif isinstance(row, (list, tuple)):
            if len(row) >= 3:
                key = row[1]
                value = row[2]

        if key is not None:
            pairs.append((str(key), value))

    return pairs


def set_project_variables_from_metadata_rows(rows):
    """
    Create QGIS project variables from metadata rows.
    Returns the number of variables created.
    """
    project = QgsProject.instance()
    count = 0

    for key, value in metadata_rows_to_pairs(rows):
        QgsExpressionContextUtils.setProjectVariable(
            project,
            "metadata_{}".format(key.lower()),
            value,
        )
        count += 1

    return count


def show_metadata_rows_dialog(rows, title=None):
    """
    Show metadata key/value pairs in a simple modal dialog.
    """
    pairs = metadata_rows_to_pairs(rows)
    if not pairs:
        return

    list_widget = QListWidget()

    for key, value in pairs:
        if key.lower() not in ("geom", "geometry") and value is not None:
            list_widget.addItem("{} : {}".format(key, value))

    list_widget.setHorizontalScrollBarPolicy(QtCore.Qt.ScrollBarAlwaysOff)
    list_widget.setVerticalScrollBarPolicy(QtCore.Qt.ScrollBarAlwaysOff)
    list_widget.setFixedSize(
        list_widget.sizeHintForColumn(0) + 2 * list_widget.frameWidth() + 20,
        max(
            120,
            list_widget.sizeHintForRow(0) * list_widget.count()
            + 2 * list_widget.frameWidth()
            + 20,
        ),
    )

    dialog = QDialog(iface.mainWindow())
    dialog.setModal(True)
    dialog.setWindowTitle(title or tr("Project details"))

    button_box = QDialogButtonBox(QDialogButtonBox.Ok)
    button_box.accepted.connect(dialog.accept)

    layout = QVBoxLayout()
    layout.addWidget(list_widget)
    layout.addWidget(button_box)
    dialog.setLayout(layout)
    dialog.exec()


def load_metadata_rows(
    tablename="metadata",
    include_geometry=False,
    preferred_option="auto",
):
    """
    Load metadata rows using one of the supported strategies.

    preferred_option values:
        "auto"     -> direct, then ogr, then sqlite
        "direct"   -> direct access to loaded layer/table
        "ogr"      -> OGR SQL connection
        "sqlite"   -> SQLite / SpatiaLite SQL connection

    Returns:
        (rows, method_used)
    """
    if preferred_option == "auto":
        methods = ["direct", "ogr", "sqlite"]
    elif preferred_option in ("direct", "ogr", "sqlite"):
        methods = [preferred_option]
    else:
        raise ValueError("Unsupported preferred_option: {}".format(preferred_option))

    last_error = None

    for method in methods:
        try:
            if method == "direct":
                if find_metadata_layer(tablename) is None:
                    continue
                rows = read_metadata_rows_direct(
                    tablename=tablename,
                    include_geometry=include_geometry,
                )
            elif method == "ogr":
                rows = read_metadata_rows_ogr(tablename=tablename)
            else:
                rows = read_metadata_rows_sqlite(tablename=tablename)

            if rows:
                return rows, method

        except Exception as exc:
            last_error = exc

    if last_error is not None:
        raise last_error

    return [], None


def read_show_metadata(
    show_details=False,
    verbose=False,
    set_project_variables=True,
    tablename="metadata",
    include_geometry=False,
    preferred_option="auto",
):
    """
    Read metadata, store it as QGIS project variables and optionally show it.

    Returns True on success, False otherwise.
    """
    try:
        clear_project_metadata_variables()

        rows, method_used = load_metadata_rows(
            tablename=tablename,
            include_geometry=include_geometry,
            preferred_option=preferred_option,
        )

        if not rows:
            if verbose:
                info = get_metadata_layer_info(tablename)
                iface.messageBar().pushMessage(
                    "openproject",
                    tr("No metadata found.")
                    + " discovery_mode={}".format(
                        info.get("discovery_mode", "unknown")
                    ),
                    Qgis.Warning,
                    5,
                )
            return False

        if set_project_variables:
            variable_count = set_project_variables_from_metadata_rows(rows)
        else:
            variable_count = 0

        if show_details:
            show_metadata_rows_dialog(rows)

        if verbose:
            info = get_metadata_layer_info(tablename)
            iface.messageBar().pushMessage(
                "openproject",
                "Metadata loaded: {} variable(s) "
                "(method={}, discovery_mode={}, provider={}, is_spatial={})".format(
                    variable_count,
                    method_used or "unknown",
                    info.get("discovery_mode", "unknown"),
                    info.get("provider", "unknown"),
                    info.get("is_spatial", False),
                ),
                Qgis.Info,
                5,
            )

        return True

    except Exception as exc:
        if verbose:
            iface.messageBar().pushMessage(
                "openproject",
                str(exc),
                Qgis.Warning,
                8,
            )
        return False


def saveProject():
    pass


def closeProject():
    pass
