from __future__ import annotations

import sqlite3
from typing import Dict, List, Optional, Sequence, Tuple

from qgis.PyQt.QtCore import Qt
from qgis.PyQt.QtWidgets import (
    QDialog, QVBoxLayout, QHBoxLayout, QGridLayout,
    QLabel, QLineEdit, QPushButton, QFileDialog,
    QComboBox, QListWidget, QListWidgetItem,
    QGroupBox, QCheckBox, QDialogButtonBox
)


def _connect(db_path: str) -> sqlite3.Connection:
    con = sqlite3.connect(db_path)
    con.row_factory = sqlite3.Row
    return con


class ImportGeoProfilesDialog(QDialog):
    """
    Dialog to select a GeoProfiler export DB + result_set + profile_ids + geodata to load.
    """

    def __init__(self, parent=None):
        super().__init__(parent)
        self.setWindowTitle("Import geoprofile")
        self.setMinimumWidth(640)

        self.leDbPath = QLineEdit()
        self.btnBrowse = QPushButton("Browse…")
        self.btnRefresh = QPushButton("Refresh")
        self.cbResultSet = QComboBox()
        self.lwProfiles = QListWidget()

        self.ckTopo = QCheckBox("Topography")
        self.ckPoints = QCheckBox("Point projections")
        self.ckAtt = QCheckBox("Geological attitudes")
        self.ckLines = QCheckBox("Line intersections")
        self.ckPolys = QCheckBox("Polygon intersections")

        # Defaults
        self.ckTopo.setChecked(True)
        self.ckPoints.setChecked(True)
        self.ckAtt.setChecked(True)
        self.ckLines.setChecked(True)
        self.ckPolys.setChecked(True)

        self._rs_items: List[Tuple[int, str]] = []  # (result_set_id, display)
        self._profile_items: List[Tuple[int, str]] = []  # (profile_id, display)

        self._build_ui()
        self._wire()

    def _build_ui(self):
        main = QVBoxLayout(self)

        # Source
        gb_source = QGroupBox("Source database")
        gl = QGridLayout(gb_source)
        gl.addWidget(QLabel("GeoPackage / SQLite:"), 0, 0)
        gl.addWidget(self.leDbPath, 0, 1)
        gl.addWidget(self.btnBrowse, 0, 2)
        gl.addWidget(self.btnRefresh, 0, 3)
        main.addWidget(gb_source)

        # Selection
        gb_sel = QGroupBox("Select result set and profiles")
        vsel = QVBoxLayout(gb_sel)

        h_rs = QHBoxLayout()
        h_rs.addWidget(QLabel("Result set:"))
        h_rs.addWidget(self.cbResultSet, 1)
        vsel.addLayout(h_rs)

        vsel.addWidget(QLabel("Profiles (multi-select):"))
        self.lwProfiles.setSelectionMode(QListWidget.SelectionMode.MultiSelection)
        vsel.addWidget(self.lwProfiles, 1)

        main.addWidget(gb_sel)

        # What to load
        gb_geo = QGroupBox("What to import")
        vgeo = QVBoxLayout(gb_geo)
        vgeo.addWidget(self.ckTopo)
        vgeo.addWidget(self.ckPoints)
        vgeo.addWidget(self.ckAtt)
        vgeo.addWidget(self.ckLines)
        vgeo.addWidget(self.ckPolys)
        main.addWidget(gb_geo)

        # Buttons
        bb = QDialogButtonBox(QDialogButtonBox.StandardButton.Ok | QDialogButtonBox.StandardButton.Cancel)
        bb.accepted.connect(self.accept)
        bb.rejected.connect(self.reject)
        main.addWidget(bb)

    def _wire(self):
        self.btnBrowse.clicked.connect(self._browse_db)
        self.btnRefresh.clicked.connect(self._refresh_from_db)
        self.cbResultSet.currentIndexChanged.connect(self._refresh_profiles)

    def _browse_db(self):
        path, _ = QFileDialog.getOpenFileName(
            self,
            "Select GeoProfiler DB",
            "",
            "GeoPackage (*.gpkg);;SQLite DB (*.sqlite *.db);;All files (*.*)"
        )
        if path:
            self.leDbPath.setText(path)
            self._refresh_from_db()

    # -----------------------
    # DB -> UI population
    # -----------------------
    def _refresh_from_db(self):
        db_path = self.leDbPath.text().strip()
        if not db_path:
            self.cbResultSet.clear()
            self.lwProfiles.clear()
            return

        try:
            con = _connect(db_path)
        except Exception:
            self.cbResultSet.clear()
            self.lwProfiles.clear()
            return

        try:
            rs_rows = con.execute("""
                SELECT result_set_id,
                       COALESCE(name, '') AS name,
                       COALESCE(created_utc, '') AS created
                FROM gp_result_sets
                ORDER BY result_set_id DESC;
            """).fetchall()

            self._rs_items = []
            self.cbResultSet.blockSignals(True)
            self.cbResultSet.clear()
            for r in rs_rows:
                rs_id = int(r["result_set_id"])
                name = (r["name"] or f"Result set {rs_id}").strip()
                created = (r["created"] or "").strip()
                label = f"{rs_id} — {name}" + (f" ({created})" if created else "")
                self._rs_items.append((rs_id, label))
                self.cbResultSet.addItem(label, rs_id)
            self.cbResultSet.blockSignals(False)

            # auto-load profiles for current rs
            self._refresh_profiles()

        finally:
            con.close()

    def _refresh_profiles(self):
        db_path = self.leDbPath.text().strip()
        if not db_path:
            self.lwProfiles.clear()
            return

        rs_id = self.cbResultSet.currentData()
        if rs_id is None:
            self.lwProfiles.clear()
            return

        con = _connect(db_path)
        try:
            prof_rows = con.execute("""
                SELECT profile_id,
                       COALESCE(profile_name, '') AS profile_name
                FROM gp_profiles
                WHERE result_set_id = ?
                ORDER BY profile_id;
            """, (int(rs_id),)).fetchall()

            self.lwProfiles.clear()
            self._profile_items = []

            for r in prof_rows:
                pid = int(r["profile_id"])
                name = (r["profile_name"] or f"Profile {pid}").strip()
                #descr = (r["descr"] or "").strip()
                label = f"{pid} — {name}" #+ (f" — {descr}" if descr else "")

                self._profile_items.append((pid, label))

                it = QListWidgetItem(label)
                it.setData(Qt.ItemDataRole.UserRole, pid)
                it.setCheckState(Qt.CheckState.Checked)  # default: import all
                self.lwProfiles.addItem(it)

        finally:
            con.close()

    # -----------------------
    # Output to caller
    # -----------------------
    def get_values(self) -> Dict:
        """
        Read user selections.
        """
        db_path = self.leDbPath.text().strip()
        rs_id = self.cbResultSet.currentData()

        # selected profiles: checked items (not selection)
        profile_ids: List[int] = []
        for i in range(self.lwProfiles.count()):
            it = self.lwProfiles.item(i)
            if it.checkState() == Qt.CheckState.Checked:
                pid = it.data(Qt.ItemDataRole.UserRole)
                if pid is not None:
                    profile_ids.append(int(pid))

        return {
            "db_path": db_path,
            "result_set_id": int(rs_id) if rs_id is not None else None,
            "profile_ids": profile_ids,  # empty means "all in result set"
            "geodata": {
                "topography": self.ckTopo.isChecked(),
                "points": self.ckPoints.isChecked(),
                "attitudes": self.ckAtt.isChecked(),
                "line_intersections": self.ckLines.isChecked(),
                "polygon_intersections": self.ckPolys.isChecked(),
            }
        }
