"""
Project metadata persistence for EMDS project hierarchies.

This module stores enough metadata to rebuild the Project -> Assessment ->
Provenance -> Task tree from SQLite, while still supporting the lightweight
global project registry used at plugin startup.
"""

from __future__ import annotations

import json
import os
import sqlite3
import uuid
from dataclasses import dataclass
from datetime import datetime
from pathlib import Path
from typing import Any, Dict, Iterable, List, Optional

from .app_paths import ensure_workspace


@dataclass
class ProjectMetadata:
    """Project metadata stored in SQLite."""

    project_id: str
    name: str
    description: str
    project_file: str
    input_layers: List[str]
    created_at: str
    updated_at: str
    metadata: Dict[str, Any]


@dataclass
class SpatialReferenceMetadata:
    """Assessment metadata analogous to legacy SpatialReference records."""

    spatial_reference_id: str
    project_id: str
    name: str
    comments: str
    output_layer_name: str
    reference_layer_name: str
    created_at: str
    updated_at: str
    metadata: Dict[str, Any]


@dataclass
class ProvenanceMetadata:
    """Analysis/task-workflow metadata."""

    provenance_id: str
    spatial_reference_id: str
    project_id: str
    name: str
    description: str
    created_at: str
    updated_at: str
    metadata: Dict[str, Any]


@dataclass
class TaskDetailMetadata:
    """Task metadata persisted for workflow execution and tree restoration."""

    task_id: str
    provenance_id: str
    project_id: str
    spatial_reference_id: str
    parent_task_id: str
    name: str
    engine_type: str
    model_path: str
    input_tables: List[str]
    result_tables: List[str]
    created_at: str
    updated_at: str
    metadata: Dict[str, Any]


@dataclass
class WorkflowDefinitionMetadata:
    """Reusable workflow-definition metadata scoped to one project."""

    workflow_definition_id: str
    project_id: str
    name: str
    description: str
    payload: Dict[str, Any]
    created_at: str
    updated_at: str


class ProjectMetadataManager:
    """Manage project metadata inside SQLite databases."""

    DEFAULT_DB_NAME = "emdsInfo.sqlite"
    PROJECT_DB_NAME = "emdsInfo.sqlite"

    def __init__(self, db_path: str = None):
        if db_path is None:
            db_path = str(ensure_workspace().metadata_db_path)

        self.db_path = Path(db_path)
        self.db_path.parent.mkdir(parents=True, exist_ok=True)
        self.last_save_error: Optional[str] = None
        self._init_database()
        # Self-heal any column drift left behind by older builds. If a
        # stale emdsInfo.sqlite existed before this code's schema was
        # finalized, CREATE TABLE IF NOT EXISTS would have skipped it
        # entirely — so a save_project INSERT would fail on the missing
        # column. ALTER TABLE ADD COLUMN closes that gap.
        self._ensure_missing_columns()

    @classmethod
    def for_project_directory(cls, project_dir: Path | str) -> "ProjectMetadataManager":
        """Create a metadata manager bound to a project's local metadata DB."""

        project_path = Path(project_dir)
        return cls(str(project_path / cls.PROJECT_DB_NAME))

    def _connect(self) -> sqlite3.Connection:
        conn = sqlite3.connect(self.db_path)
        conn.execute("PRAGMA foreign_keys = ON")
        return conn

    def _init_database(self) -> None:
        conn = self._connect()
        cursor = conn.cursor()

        cursor.execute(
            """
            CREATE TABLE IF NOT EXISTS projects (
                project_id TEXT PRIMARY KEY,
                name TEXT NOT NULL,
                description TEXT,
                project_file TEXT NOT NULL UNIQUE,
                input_layers TEXT NOT NULL,
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL,
                metadata TEXT
            )
            """
        )

        cursor.execute(
            """
            CREATE TABLE IF NOT EXISTS spatial_references (
                spatial_reference_id TEXT PRIMARY KEY,
                project_id TEXT NOT NULL,
                name TEXT NOT NULL,
                comments TEXT,
                output_layer_name TEXT,
                reference_layer_name TEXT,
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL,
                metadata TEXT,
                FOREIGN KEY(project_id) REFERENCES projects(project_id) ON DELETE CASCADE
            )
            """
        )

        cursor.execute(
            """
            CREATE TABLE IF NOT EXISTS provenances (
                provenance_id TEXT PRIMARY KEY,
                spatial_reference_id TEXT NOT NULL,
                project_id TEXT NOT NULL,
                name TEXT NOT NULL,
                description TEXT,
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL,
                metadata TEXT,
                FOREIGN KEY(spatial_reference_id)
                    REFERENCES spatial_references(spatial_reference_id)
                    ON DELETE CASCADE,
                FOREIGN KEY(project_id) REFERENCES projects(project_id) ON DELETE CASCADE
            )
            """
        )

        cursor.execute(
            """
            CREATE TABLE IF NOT EXISTS task_details (
                task_id TEXT PRIMARY KEY,
                provenance_id TEXT NOT NULL,
                project_id TEXT NOT NULL,
                spatial_reference_id TEXT NOT NULL,
                parent_task_id TEXT,
                name TEXT NOT NULL,
                engine_type TEXT NOT NULL,
                model_path TEXT,
                input_tables TEXT,
                result_tables TEXT,
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL,
                metadata TEXT,
                FOREIGN KEY(provenance_id) REFERENCES provenances(provenance_id) ON DELETE CASCADE,
                FOREIGN KEY(project_id) REFERENCES projects(project_id) ON DELETE CASCADE,
                FOREIGN KEY(spatial_reference_id)
                    REFERENCES spatial_references(spatial_reference_id)
                    ON DELETE CASCADE,
                FOREIGN KEY(parent_task_id) REFERENCES task_details(task_id) ON DELETE CASCADE
            )
            """
        )

        cursor.execute(
            "CREATE INDEX IF NOT EXISTS idx_spatial_references_project "
            "ON spatial_references(project_id)"
        )
        cursor.execute(
            "CREATE INDEX IF NOT EXISTS idx_provenances_spatial_reference "
            "ON provenances(spatial_reference_id)"
        )
        cursor.execute(
            "CREATE INDEX IF NOT EXISTS idx_task_details_provenance "
            "ON task_details(provenance_id)"
        )
        cursor.execute(
            "CREATE INDEX IF NOT EXISTS idx_task_details_parent "
            "ON task_details(parent_task_id)"
        )
        cursor.execute(
            """
            CREATE TABLE IF NOT EXISTS field_mapping_presets (
                preset_id TEXT PRIMARY KEY,
                project_id TEXT NOT NULL,
                engine_type TEXT NOT NULL,
                model_identifier TEXT NOT NULL,
                input_table_name TEXT NOT NULL,
                payload TEXT NOT NULL,
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL,
                UNIQUE(project_id, engine_type, model_identifier, input_table_name)
            )
            """
        )
        cursor.execute(
            "CREATE INDEX IF NOT EXISTS idx_field_mapping_presets_lookup "
            "ON field_mapping_presets(project_id, engine_type, model_identifier, input_table_name)"
        )
        cursor.execute(
            """
            CREATE TABLE IF NOT EXISTS workflow_definitions (
                workflow_definition_id TEXT PRIMARY KEY,
                project_id TEXT NOT NULL,
                name TEXT NOT NULL,
                description TEXT,
                payload TEXT NOT NULL,
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL,
                UNIQUE(project_id, name),
                FOREIGN KEY(project_id) REFERENCES projects(project_id) ON DELETE CASCADE
            )
            """
        )
        cursor.execute(
            "CREATE INDEX IF NOT EXISTS idx_workflow_definitions_project "
            "ON workflow_definitions(project_id, updated_at)"
        )

        conn.commit()
        conn.close()

    # ---- Schema self-heal --------------------------------------------------

    # Map of (table, column) → (sql_type, nullable, default_literal). Stays
    # in lockstep with _init_database. ALTER TABLE ADD COLUMN only fires
    # when an older DB is missing one of these; matches the ArcGIS
    # add-in's EmdsSchemaMaintainer approach.
    _EXPECTED_COLUMNS = (
        ("projects", "project_id",    "TEXT", False, "''"),
        ("projects", "name",          "TEXT", False, "''"),
        ("projects", "description",   "TEXT", True,  None),
        ("projects", "project_file",  "TEXT", False, "''"),
        ("projects", "input_layers",  "TEXT", False, "'[]'"),
        ("projects", "created_at",    "TEXT", False, "''"),
        ("projects", "updated_at",    "TEXT", False, "''"),
        ("projects", "metadata",      "TEXT", True,  None),
        ("spatial_references", "spatial_reference_id", "TEXT", False, "''"),
        ("spatial_references", "project_id",           "TEXT", False, "''"),
        ("spatial_references", "name",                 "TEXT", False, "''"),
        ("spatial_references", "comments",             "TEXT", True,  None),
        ("spatial_references", "output_layer_name",    "TEXT", True,  None),
        ("spatial_references", "reference_layer_name", "TEXT", True,  None),
        ("spatial_references", "created_at",           "TEXT", False, "''"),
        ("spatial_references", "updated_at",           "TEXT", False, "''"),
        ("spatial_references", "metadata",             "TEXT", True,  None),
        ("provenances", "provenance_id",         "TEXT", False, "''"),
        ("provenances", "spatial_reference_id",  "TEXT", False, "''"),
        ("provenances", "project_id",            "TEXT", False, "''"),
        ("provenances", "name",                  "TEXT", False, "''"),
        ("provenances", "description",           "TEXT", True,  None),
        ("provenances", "created_at",            "TEXT", False, "''"),
        ("provenances", "updated_at",            "TEXT", False, "''"),
        ("provenances", "metadata",              "TEXT", True,  None),
        ("task_details", "task_id",              "TEXT", False, "''"),
        ("task_details", "provenance_id",        "TEXT", False, "''"),
        ("task_details", "project_id",           "TEXT", False, "''"),
        ("task_details", "spatial_reference_id", "TEXT", False, "''"),
        ("task_details", "parent_task_id",       "TEXT", True,  None),
        ("task_details", "name",                 "TEXT", False, "''"),
        ("task_details", "engine_type",          "TEXT", False, "''"),
        ("task_details", "model_path",           "TEXT", True,  None),
        ("task_details", "input_tables",         "TEXT", True,  None),
        ("task_details", "result_tables",        "TEXT", True,  None),
        ("task_details", "created_at",           "TEXT", False, "''"),
        ("task_details", "updated_at",           "TEXT", False, "''"),
        ("task_details", "metadata",             "TEXT", True,  None),
        ("field_mapping_presets", "preset_id",         "TEXT", False, "''"),
        ("field_mapping_presets", "project_id",        "TEXT", False, "''"),
        ("field_mapping_presets", "engine_type",       "TEXT", False, "''"),
        ("field_mapping_presets", "model_identifier",  "TEXT", False, "''"),
        ("field_mapping_presets", "input_table_name",  "TEXT", False, "''"),
        ("field_mapping_presets", "payload",           "TEXT", False, "''"),
        ("field_mapping_presets", "created_at",        "TEXT", False, "''"),
        ("field_mapping_presets", "updated_at",        "TEXT", False, "''"),
        ("workflow_definitions", "workflow_definition_id", "TEXT", False, "''"),
        ("workflow_definitions", "project_id",            "TEXT", False, "''"),
        ("workflow_definitions", "name",                  "TEXT", False, "''"),
        ("workflow_definitions", "description",           "TEXT", True,  None),
        ("workflow_definitions", "payload",               "TEXT", False, "''"),
        ("workflow_definitions", "created_at",            "TEXT", False, "''"),
        ("workflow_definitions", "updated_at",            "TEXT", False, "''"),
    )

    def _ensure_missing_columns(self) -> None:
        """
        Compare the live SQLite schema to what this code expects and
        ALTER TABLE ADD COLUMN for anything missing. Protects against
        the "Could not save Workspace metadata to database" failure
        mode where an older emdsInfo.sqlite has the table but is
        missing a column the current INSERT references. Idempotent.
        """
        try:
            conn = self._connect()
            try:
                cursor = conn.cursor()
                for table, column, sql_type, nullable, default in self._EXPECTED_COLUMNS:
                    if not self._table_exists(cursor, table):
                        continue  # _init_database will create it next time
                    if self._column_exists(cursor, table, column):
                        continue
                    default_clause = "" if nullable else f" NOT NULL DEFAULT {default}"
                    cursor.execute(
                        f'ALTER TABLE "{table}" ADD COLUMN "{column}" {sql_type}{default_clause}'
                    )
                conn.commit()
            finally:
                conn.close()
        except Exception as exc:
            # Schema-heal failures must never break workspace creation —
            # _init_database alone is enough on fresh installs. Log and
            # press on; save_project will surface the real error if any.
            print(f"EMDS schema heal warning: {exc}")

    @staticmethod
    def _table_exists(cursor: sqlite3.Cursor, table: str) -> bool:
        cursor.execute(
            "SELECT 1 FROM sqlite_master WHERE type='table' AND name=?",
            (table,),
        )
        return cursor.fetchone() is not None

    @staticmethod
    def _column_exists(cursor: sqlite3.Cursor, table: str, column: str) -> bool:
        cursor.execute(f'PRAGMA table_info("{table}")')
        existing = {row[1].lower() for row in cursor.fetchall()}
        return column.lower() in existing

    @staticmethod
    def _now() -> str:
        return datetime.now().isoformat()

    @staticmethod
    def _decode_json(value: Optional[str], default):
        if not value:
            return default
        try:
            return json.loads(value)
        except Exception:
            return default

    def save_project(
        self,
        project_id: str,
        name: str,
        description: str,
        project_file: str,
        input_layers: List[str],
        metadata: Dict[str, Any] = None,
    ) -> bool:
        try:
            existing = self.get_project(project_id)
            now = self._now()
            created_at = existing.created_at if existing else now

            conn = self._connect()
            cursor = conn.cursor()
            cursor.execute(
                """
                INSERT INTO projects
                (project_id, name, description, project_file, input_layers, created_at, updated_at, metadata)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                ON CONFLICT(project_id) DO UPDATE SET
                    name = excluded.name,
                    description = excluded.description,
                    project_file = excluded.project_file,
                    input_layers = excluded.input_layers,
                    updated_at = excluded.updated_at,
                    metadata = excluded.metadata
                """,
                (
                    project_id,
                    name,
                    description,
                    project_file,
                    json.dumps(input_layers or []),
                    created_at,
                    now,
                    json.dumps(metadata or {}),
                ),
            )
            conn.commit()
            conn.close()
            self.last_save_error = None
            return True
        except Exception as exc:
            # Record the exception so callers can surface it. Without
            # this, the dock widget only knew "save failed" and the user
            # had to dig through QGIS Python console to find the cause
            # (e.g. schema drift, locked DB file).
            self.last_save_error = f"{type(exc).__name__}: {exc}"
            print(f"Error saving project metadata: {exc}")
            return False

    def get_project(self, project_id: str) -> Optional[ProjectMetadata]:
        try:
            conn = self._connect()
            cursor = conn.cursor()
            cursor.execute(
                """
                SELECT project_id, name, description, project_file, input_layers, created_at, updated_at, metadata
                FROM projects
                WHERE project_id = ?
                """,
                (project_id,),
            )
            row = cursor.fetchone()
            conn.close()
            if row is None:
                return None
            return ProjectMetadata(
                project_id=row[0],
                name=row[1],
                description=row[2] or "",
                project_file=row[3],
                input_layers=self._decode_json(row[4], []),
                created_at=row[5],
                updated_at=row[6],
                metadata=self._decode_json(row[7], {}),
            )
        except Exception as exc:
            print(f"Error retrieving project: {exc}")
            return None

    def get_project_by_file(self, project_file: str) -> Optional[ProjectMetadata]:
        try:
            conn = self._connect()
            cursor = conn.cursor()
            cursor.execute(
                """
                SELECT project_id, name, description, project_file, input_layers, created_at, updated_at, metadata
                FROM projects
                WHERE project_file = ?
                """,
                (project_file,),
            )
            row = cursor.fetchone()
            conn.close()
            if row is None:
                return None
            return ProjectMetadata(
                project_id=row[0],
                name=row[1],
                description=row[2] or "",
                project_file=row[3],
                input_layers=self._decode_json(row[4], []),
                created_at=row[5],
                updated_at=row[6],
                metadata=self._decode_json(row[7], {}),
            )
        except Exception as exc:
            print(f"Error retrieving project by file: {exc}")
            return None

    def get_all_projects(self) -> List[ProjectMetadata]:
        try:
            conn = self._connect()
            cursor = conn.cursor()
            cursor.execute(
                """
                SELECT project_id, name, description, project_file, input_layers, created_at, updated_at, metadata
                FROM projects
                ORDER BY created_at DESC
                """
            )
            rows = cursor.fetchall()
            conn.close()
            return [
                ProjectMetadata(
                    project_id=row[0],
                    name=row[1],
                    description=row[2] or "",
                    project_file=row[3],
                    input_layers=self._decode_json(row[4], []),
                    created_at=row[5],
                    updated_at=row[6],
                    metadata=self._decode_json(row[7], {}),
                )
                for row in rows
            ]
        except Exception as exc:
            print(f"Error retrieving projects: {exc}")
            return []

    def update_project(
        self,
        project_id: str,
        name: str = None,
        description: str = None,
        metadata: Dict[str, Any] = None,
    ) -> bool:
        existing = self.get_project(project_id)
        if existing is None:
            return False

        merged_metadata = dict(existing.metadata or {})
        if metadata:
            merged_metadata.update(metadata)

        return self.save_project(
            project_id=project_id,
            name=name or existing.name,
            description=description if description is not None else existing.description,
            project_file=existing.project_file,
            input_layers=existing.input_layers,
            metadata=merged_metadata,
        )

    def delete_project(self, project_id: str) -> bool:
        try:
            conn = self._connect()
            cursor = conn.cursor()
            cursor.execute("DELETE FROM projects WHERE project_id = ?", (project_id,))
            conn.commit()
            conn.close()
            return True
        except Exception as exc:
            print(f"Error deleting project: {exc}")
            return False

    def save_spatial_reference(
        self,
        spatial_reference_id: str,
        project_id: str,
        name: str,
        comments: str,
        output_layer_name: str,
        reference_layer_name: str = "",
        metadata: Dict[str, Any] = None,
    ) -> bool:
        try:
            existing = self.get_spatial_reference(spatial_reference_id)
            now = self._now()
            created_at = existing.created_at if existing else now

            conn = self._connect()
            cursor = conn.cursor()
            cursor.execute(
                """
                INSERT INTO spatial_references
                (
                    spatial_reference_id, project_id, name, comments,
                    output_layer_name, reference_layer_name,
                    created_at, updated_at, metadata
                )
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
                ON CONFLICT(spatial_reference_id) DO UPDATE SET
                    project_id = excluded.project_id,
                    name = excluded.name,
                    comments = excluded.comments,
                    output_layer_name = excluded.output_layer_name,
                    reference_layer_name = excluded.reference_layer_name,
                    updated_at = excluded.updated_at,
                    metadata = excluded.metadata
                """,
                (
                    spatial_reference_id,
                    project_id,
                    name,
                    comments,
                    output_layer_name,
                    reference_layer_name,
                    created_at,
                    now,
                    json.dumps(metadata or {}),
                ),
            )
            conn.commit()
            conn.close()
            return True
        except Exception as exc:
            print(f"Error saving spatial reference metadata: {exc}")
            return False

    def get_spatial_reference(self, spatial_reference_id: str) -> Optional[SpatialReferenceMetadata]:
        try:
            conn = self._connect()
            cursor = conn.cursor()
            cursor.execute(
                """
                SELECT
                    spatial_reference_id, project_id, name, comments,
                    output_layer_name, reference_layer_name,
                    created_at, updated_at, metadata
                FROM spatial_references
                WHERE spatial_reference_id = ?
                """,
                (spatial_reference_id,),
            )
            row = cursor.fetchone()
            conn.close()
            if row is None:
                return None
            return SpatialReferenceMetadata(
                spatial_reference_id=row[0],
                project_id=row[1],
                name=row[2],
                comments=row[3] or "",
                output_layer_name=row[4] or "",
                reference_layer_name=row[5] or "",
                created_at=row[6],
                updated_at=row[7],
                metadata=self._decode_json(row[8], {}),
            )
        except Exception as exc:
            print(f"Error retrieving spatial reference: {exc}")
            return None

    def get_spatial_references_for_project(self, project_id: str) -> List[SpatialReferenceMetadata]:
        try:
            conn = self._connect()
            cursor = conn.cursor()
            cursor.execute(
                """
                SELECT
                    spatial_reference_id, project_id, name, comments,
                    output_layer_name, reference_layer_name,
                    created_at, updated_at, metadata
                FROM spatial_references
                WHERE project_id = ?
                ORDER BY created_at ASC
                """,
                (project_id,),
            )
            rows = cursor.fetchall()
            conn.close()
            return [
                SpatialReferenceMetadata(
                    spatial_reference_id=row[0],
                    project_id=row[1],
                    name=row[2],
                    comments=row[3] or "",
                    output_layer_name=row[4] or "",
                    reference_layer_name=row[5] or "",
                    created_at=row[6],
                    updated_at=row[7],
                    metadata=self._decode_json(row[8], {}),
                )
                for row in rows
            ]
        except Exception as exc:
            print(f"Error retrieving spatial references: {exc}")
            return []

    def delete_spatial_reference(self, spatial_reference_id: str) -> bool:
        try:
            conn = self._connect()
            cursor = conn.cursor()
            cursor.execute(
                "DELETE FROM spatial_references WHERE spatial_reference_id = ?",
                (spatial_reference_id,),
            )
            conn.commit()
            conn.close()
            return True
        except Exception as exc:
            print(f"Error deleting spatial reference metadata: {exc}")
            return False

    def save_workflow_definition(
        self,
        project_id: str,
        workflow_definition_id: str,
        name: str,
        description: str = "",
        payload: Dict[str, Any] = None,
    ) -> bool:
        """Persist a reusable workflow definition within one project."""
        try:
            existing = self.get_workflow_definition(project_id, workflow_definition_id)
            now = self._now()
            created_at = existing.created_at if existing else now

            conn = self._connect()
            cursor = conn.cursor()
            cursor.execute(
                """
                INSERT INTO workflow_definitions
                (
                    workflow_definition_id, project_id, name, description,
                    payload, created_at, updated_at
                )
                VALUES (?, ?, ?, ?, ?, ?, ?)
                ON CONFLICT(workflow_definition_id) DO UPDATE SET
                    project_id = excluded.project_id,
                    name = excluded.name,
                    description = excluded.description,
                    payload = excluded.payload,
                    updated_at = excluded.updated_at
                """,
                (
                    workflow_definition_id,
                    project_id,
                    name,
                    description,
                    json.dumps(payload or {}),
                    created_at,
                    now,
                ),
            )
            conn.commit()
            conn.close()
            return True
        except Exception as exc:
            print(f"Error saving workflow definition metadata: {exc}")
            return False

    def get_workflow_definition(
        self,
        project_id: str,
        workflow_definition_id: str,
    ) -> Optional[WorkflowDefinitionMetadata]:
        """Return one workflow definition for a specific project."""
        try:
            conn = self._connect()
            cursor = conn.cursor()
            cursor.execute(
                """
                SELECT
                    workflow_definition_id, project_id, name, description,
                    payload, created_at, updated_at
                FROM workflow_definitions
                WHERE project_id = ? AND workflow_definition_id = ?
                """,
                (project_id, workflow_definition_id),
            )
            row = cursor.fetchone()
            conn.close()
            if row is None:
                return None
            return WorkflowDefinitionMetadata(
                workflow_definition_id=row[0],
                project_id=row[1],
                name=row[2] or "",
                description=row[3] or "",
                payload=self._decode_json(row[4], {}),
                created_at=row[5],
                updated_at=row[6],
            )
        except Exception as exc:
            print(f"Error retrieving workflow definition metadata: {exc}")
            return None

    def list_workflow_definitions_for_project(
        self,
        project_id: str,
    ) -> List[WorkflowDefinitionMetadata]:
        """List reusable workflow definitions for the selected project."""
        try:
            conn = self._connect()
            cursor = conn.cursor()
            cursor.execute(
                """
                SELECT
                    workflow_definition_id, project_id, name, description,
                    payload, created_at, updated_at
                FROM workflow_definitions
                WHERE project_id = ?
                ORDER BY updated_at DESC, name COLLATE NOCASE ASC
                """,
                (project_id,),
            )
            rows = cursor.fetchall()
            conn.close()
            return [
                WorkflowDefinitionMetadata(
                    workflow_definition_id=row[0],
                    project_id=row[1],
                    name=row[2] or "",
                    description=row[3] or "",
                    payload=self._decode_json(row[4], {}),
                    created_at=row[5],
                    updated_at=row[6],
                )
                for row in rows
            ]
        except Exception as exc:
            print(f"Error listing workflow definition metadata: {exc}")
            return []

    def save_provenance(
        self,
        provenance_id: str,
        spatial_reference_id: str,
        project_id: str,
        name: str,
        description: str = "",
        metadata: Dict[str, Any] = None,
    ) -> bool:
        try:
            existing = self.get_provenance(provenance_id)
            now = self._now()
            created_at = existing.created_at if existing else now

            conn = self._connect()
            cursor = conn.cursor()
            cursor.execute(
                """
                INSERT INTO provenances
                (
                    provenance_id, spatial_reference_id, project_id,
                    name, description, created_at, updated_at, metadata
                )
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                ON CONFLICT(provenance_id) DO UPDATE SET
                    spatial_reference_id = excluded.spatial_reference_id,
                    project_id = excluded.project_id,
                    name = excluded.name,
                    description = excluded.description,
                    updated_at = excluded.updated_at,
                    metadata = excluded.metadata
                """,
                (
                    provenance_id,
                    spatial_reference_id,
                    project_id,
                    name,
                    description,
                    created_at,
                    now,
                    json.dumps(metadata or {}),
                ),
            )
            conn.commit()
            conn.close()
            return True
        except Exception as exc:
            print(f"Error saving provenance metadata: {exc}")
            return False

    def get_provenance(self, provenance_id: str) -> Optional[ProvenanceMetadata]:
        try:
            conn = self._connect()
            cursor = conn.cursor()
            cursor.execute(
                """
                SELECT provenance_id, spatial_reference_id, project_id, name, description,
                       created_at, updated_at, metadata
                FROM provenances
                WHERE provenance_id = ?
                """,
                (provenance_id,),
            )
            row = cursor.fetchone()
            conn.close()
            if row is None:
                return None
            return ProvenanceMetadata(
                provenance_id=row[0],
                spatial_reference_id=row[1],
                project_id=row[2],
                name=row[3],
                description=row[4] or "",
                created_at=row[5],
                updated_at=row[6],
                metadata=self._decode_json(row[7], {}),
            )
        except Exception as exc:
            print(f"Error retrieving provenance metadata: {exc}")
            return None

    def get_provenances_for_spatial_reference(self, spatial_reference_id: str) -> List[ProvenanceMetadata]:
        try:
            conn = self._connect()
            cursor = conn.cursor()
            cursor.execute(
                """
                SELECT provenance_id, spatial_reference_id, project_id, name, description,
                       created_at, updated_at, metadata
                FROM provenances
                WHERE spatial_reference_id = ?
                ORDER BY created_at ASC
                """,
                (spatial_reference_id,),
            )
            rows = cursor.fetchall()
            conn.close()
            return [
                ProvenanceMetadata(
                    provenance_id=row[0],
                    spatial_reference_id=row[1],
                    project_id=row[2],
                    name=row[3],
                    description=row[4] or "",
                    created_at=row[5],
                    updated_at=row[6],
                    metadata=self._decode_json(row[7], {}),
                )
                for row in rows
            ]
        except Exception as exc:
            print(f"Error retrieving provenances: {exc}")
            return []

    def delete_provenance(self, provenance_id: str) -> bool:
        try:
            conn = self._connect()
            cursor = conn.cursor()
            cursor.execute("DELETE FROM provenances WHERE provenance_id = ?", (provenance_id,))
            conn.commit()
            conn.close()
            return True
        except Exception as exc:
            print(f"Error deleting provenance metadata: {exc}")
            return False

    def save_task_detail(
        self,
        task_id: str,
        provenance_id: str,
        project_id: str,
        spatial_reference_id: str,
        name: str,
        engine_type: str,
        model_path: str = "",
        input_tables: List[str] = None,
        result_tables: List[str] = None,
        parent_task_id: str = "",
        metadata: Dict[str, Any] = None,
    ) -> bool:
        try:
            existing = self.get_task_detail(task_id)
            now = self._now()
            created_at = existing.created_at if existing else now

            conn = self._connect()
            cursor = conn.cursor()
            cursor.execute(
                """
                INSERT INTO task_details
                (
                    task_id, provenance_id, project_id, spatial_reference_id,
                    parent_task_id, name, engine_type, model_path,
                    input_tables, result_tables, created_at, updated_at, metadata
                )
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                ON CONFLICT(task_id) DO UPDATE SET
                    provenance_id = excluded.provenance_id,
                    project_id = excluded.project_id,
                    spatial_reference_id = excluded.spatial_reference_id,
                    parent_task_id = excluded.parent_task_id,
                    name = excluded.name,
                    engine_type = excluded.engine_type,
                    model_path = excluded.model_path,
                    input_tables = excluded.input_tables,
                    result_tables = excluded.result_tables,
                    updated_at = excluded.updated_at,
                    metadata = excluded.metadata
                """,
                (
                    task_id,
                    provenance_id,
                    project_id,
                    spatial_reference_id,
                    parent_task_id or None,
                    name,
                    engine_type,
                    model_path,
                    json.dumps(input_tables or []),
                    json.dumps(result_tables or []),
                    created_at,
                    now,
                    json.dumps(metadata or {}),
                ),
            )
            conn.commit()
            conn.close()
            return True
        except Exception as exc:
            print(f"Error saving task metadata: {exc}")
            return False

    def get_task_detail(self, task_id: str) -> Optional[TaskDetailMetadata]:
        try:
            conn = self._connect()
            cursor = conn.cursor()
            cursor.execute(
                """
                SELECT task_id, provenance_id, project_id, spatial_reference_id, parent_task_id,
                       name, engine_type, model_path, input_tables, result_tables,
                       created_at, updated_at, metadata
                FROM task_details
                WHERE task_id = ?
                """,
                (task_id,),
            )
            row = cursor.fetchone()
            conn.close()
            if row is None:
                return None
            return TaskDetailMetadata(
                task_id=row[0],
                provenance_id=row[1],
                project_id=row[2],
                spatial_reference_id=row[3],
                parent_task_id=row[4] or "",
                name=row[5],
                engine_type=row[6],
                model_path=row[7] or "",
                input_tables=self._decode_json(row[8], []),
                result_tables=self._decode_json(row[9], []),
                created_at=row[10],
                updated_at=row[11],
                metadata=self._decode_json(row[12], {}),
            )
        except Exception as exc:
            print(f"Error retrieving task metadata: {exc}")
            return None

    def get_task_details_for_provenance(self, provenance_id: str) -> List[TaskDetailMetadata]:
        try:
            conn = self._connect()
            cursor = conn.cursor()
            cursor.execute(
                """
                SELECT task_id, provenance_id, project_id, spatial_reference_id, parent_task_id,
                       name, engine_type, model_path, input_tables, result_tables,
                       created_at, updated_at, metadata
                FROM task_details
                WHERE provenance_id = ?
                ORDER BY created_at ASC
                """,
                (provenance_id,),
            )
            rows = cursor.fetchall()
            conn.close()
            return [
                TaskDetailMetadata(
                    task_id=row[0],
                    provenance_id=row[1],
                    project_id=row[2],
                    spatial_reference_id=row[3],
                    parent_task_id=row[4] or "",
                    name=row[5],
                    engine_type=row[6],
                    model_path=row[7] or "",
                    input_tables=self._decode_json(row[8], []),
                    result_tables=self._decode_json(row[9], []),
                    created_at=row[10],
                    updated_at=row[11],
                    metadata=self._decode_json(row[12], {}),
                )
                for row in rows
            ]
        except Exception as exc:
            print(f"Error retrieving tasks: {exc}")
            return []

    def delete_task_detail(self, task_id: str) -> bool:
        try:
            conn = self._connect()
            cursor = conn.cursor()
            cursor.execute("DELETE FROM task_details WHERE task_id = ?", (task_id,))
            conn.commit()
            conn.close()
            return True
        except Exception as exc:
            print(f"Error deleting task metadata: {exc}")
            return False

    def project_exists(self, project_id: str) -> bool:
        return self.get_project(project_id) is not None

    def get_project_count(self) -> int:
        try:
            conn = self._connect()
            cursor = conn.cursor()
            cursor.execute("SELECT COUNT(*) FROM projects")
            count = cursor.fetchone()[0]
            conn.close()
            return count
        except Exception as exc:
            print(f"Error counting projects: {exc}")
            return 0

    def save_field_mapping_preset(
        self,
        *,
        project_id: str,
        engine_type: str,
        model_identifier: str,
        input_table_name: str,
        payload: Dict[str, Any],
    ) -> bool:
        try:
            normalized_model = str(model_identifier or "").strip()
            normalized_input = str(input_table_name or "").strip()
            if not all([project_id, engine_type, normalized_model, normalized_input]):
                return False

            existing = self.get_field_mapping_preset(
                project_id=project_id,
                engine_type=engine_type,
                model_identifier=normalized_model,
                input_table_name=normalized_input,
            )
            now = self._now()

            conn = self._connect()
            conn.cursor().execute(
                """
                INSERT INTO field_mapping_presets
                (preset_id, project_id, engine_type, model_identifier, input_table_name, payload, created_at, updated_at)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                ON CONFLICT(project_id, engine_type, model_identifier, input_table_name) DO UPDATE SET
                    payload = excluded.payload,
                    updated_at = excluded.updated_at
                """,
                (
                    existing.get("preset_id") if existing else str(uuid.uuid4()),
                    project_id,
                    engine_type,
                    normalized_model,
                    normalized_input,
                    json.dumps(payload or {}),
                    existing.get("created_at") if existing else now,
                    now,
                ),
            )
            conn.commit()
            conn.close()
            return True
        except Exception as exc:
            print(f"Error saving field mapping preset: {exc}")
            return False

    def get_field_mapping_preset(
        self,
        *,
        project_id: str,
        engine_type: str,
        model_identifier: str,
        input_table_name: str,
    ) -> Dict[str, Any]:
        try:
            conn = self._connect()
            conn.row_factory = sqlite3.Row
            row = conn.cursor().execute(
                """
                SELECT *
                FROM field_mapping_presets
                WHERE project_id = ?
                  AND engine_type = ?
                  AND model_identifier = ?
                  AND input_table_name = ?
                """,
                (project_id, engine_type, model_identifier, input_table_name),
            ).fetchone()
            conn.close()
            if not row:
                return {}
            return {
                "preset_id": row["preset_id"],
                "project_id": row["project_id"],
                "engine_type": row["engine_type"],
                "model_identifier": row["model_identifier"],
                "input_table_name": row["input_table_name"],
                "payload": self._decode_json(row["payload"], {}),
                "created_at": row["created_at"],
                "updated_at": row["updated_at"],
            }
        except Exception as exc:
            print(f"Error retrieving field mapping preset: {exc}")
            return {}


class SharedProjectMetadataManager:
    DEFAULT_DB_NAME = "emdsInfo.sqlite"
    PROJECT_DB_NAME = DEFAULT_DB_NAME
    GIS_ENVIRONMENT_QGIS = "QGIS"

    def __init__(self, db_path: str = None):
        self.db_path = Path(db_path) if db_path else self._resolve_default_db_path()
        self.db_path.parent.mkdir(parents=True, exist_ok=True)
        self._init_database()

    @classmethod
    def for_project_directory(cls, _project_dir: Path | str) -> "SharedProjectMetadataManager":
        return cls()

    @classmethod
    def _resolve_default_db_path(cls) -> Path:
        override = os.environ.get("EMDS_METADATA_DB_PATH")
        if override:
            return Path(override)
        return ensure_workspace().metadata_db_path

    def _connect(self) -> sqlite3.Connection:
        conn = sqlite3.connect(self.db_path)
        conn.execute("PRAGMA foreign_keys = ON")
        conn.row_factory = sqlite3.Row
        return conn

    def _init_database(self) -> None:
        conn = self._connect()
        cursor = conn.cursor()
        statements = [
            "CREATE TABLE IF NOT EXISTS Projects (Id TEXT PRIMARY KEY, ProjectName TEXT NOT NULL, CreatedByEMDSVersion TEXT, CreationUserNames TEXT, CreationDateTime TEXT, ModificationUserName TEXT, ModificationDate TEXT, BaseLayerNames TEXT, BaseLayerNameSources TEXT, Symbology TEXT, GeoDatabaseType TEXT, GeoDatabaseName TEXT, GeoDatabaseConnectionString TEXT, MapDocumentName TEXT, WorkspacePaths TEXT, EMDSDatabaseName TEXT, EMDSRasterFileNames TEXT, Comments TEXT, Misc TEXT, VersionID INTEGER DEFAULT 1, IsCurrent INTEGER DEFAULT 1, IsDeleted INTEGER DEFAULT 0, XMLProjectTree TEXT, MapTableOfContent TEXT)",
            "CREATE TABLE IF NOT EXISTS SpatialReferences (Id TEXT PRIMARY KEY, SpatialReferenceName TEXT NOT NULL, CreationUserNames TEXT, CreationDateTime TEXT, ModificationUserName TEXT, ModificationDate TEXT, OverlayLayerName TEXT, RasterOverlayFileName TEXT, OriginalSourceWebServices TEXT, OriginalSourceTablesDBTypes TEXT, OriginalSourceTablesDBConnectionStrings TEXT, OriginalSourceTablesNames TEXT, OriginalSourceRasterFileNames TEXT, OriginalSourceRasterTableNames TEXT, Layers TEXT, Symbology TEXT, Comments TEXT, Misc TEXT, VersionID INTEGER DEFAULT 1, IsCurrent INTEGER DEFAULT 1, IsDeleted INTEGER DEFAULT 0, ProjectId TEXT NOT NULL)",
            "CREATE TABLE IF NOT EXISTS Provenances (Id TEXT PRIMARY KEY, ProvenanceName TEXT NOT NULL, CreationUserNames TEXT, CreationDateTime TEXT, ModificationUserName TEXT, ModificationDate TEXT, WorkflowTaskList TEXT, OriginalInputWebServices TEXT, OriginalInputTablesDBTypes TEXT, OriginalInputTablesDBConnectionStrings TEXT, OriginalInputTablesNames TEXT, OriginalInputRasterTablesNames TEXT, OriginalInputRasterFileNames TEXT, FinalOutputWebServices TEXT, FinalOutputTablesDBTypes TEXT, FinalOutputTablesDBConnectionStrings TEXT, FinalOutputTablesNames TEXT, FinalOutputRasterTablesNames TEXT, FinalOutputRasterFileNames TEXT, Symbology TEXT, Comments TEXT, Misc TEXT, VersionID INTEGER DEFAULT 1, IsCurrent INTEGER DEFAULT 1, IsDeleted INTEGER DEFAULT 0, ProjectId TEXT NOT NULL, SpatialReferenceId TEXT NOT NULL)",
            "CREATE TABLE IF NOT EXISTS TaskDetails (Id TEXT PRIMARY KEY, TaskDetailName TEXT NOT NULL, CreationUserNames TEXT, Model BLOB, ModelFilename TEXT, ModelUpdateDate TEXT, TaskStepsPerformed TEXT, CreationDateTime TEXT, ModificationUserName TEXT, ModificationDate TEXT, InputTables TEXT, ResultsTables TEXT, ResultsAliasTables TEXT, InputRasterTables TEXT, InputRasterFileNames TEXT, ResultsRasterFileNames TEXT, ResultsRasterTableNames TEXT, EngineType TEXT, DatabaseType TEXT, DatabaseNames TEXT, DatabaseConnectionString TEXT, Category TEXT, StartDateTime TEXT, EndDateTime TEXT, ParentTaskID TEXT DEFAULT '', IsDisplayedOnMap INTEGER DEFAULT 0, IsScenario INTEGER DEFAULT 0, Symbology TEXT, Comments TEXT, Misc TEXT, VersionID INTEGER DEFAULT 1, IsCurrent INTEGER DEFAULT 1, IsDeleted INTEGER DEFAULT 0, ProvenanceId TEXT NOT NULL)",
            "CREATE TABLE IF NOT EXISTS MetadataNws (Id TEXT PRIMARY KEY, InfluenceTableName TEXT, InfluenceRankTableName TEXT, InfluenceRankSummaryTableName TEXT, Symbology TEXT, Comments TEXT, Misc TEXT, VersionID INTEGER DEFAULT 1, IsCurrent INTEGER DEFAULT 1, IsDeleted INTEGER DEFAULT 0, TaskDetailId TEXT NOT NULL UNIQUE)",
            "CREATE TABLE IF NOT EXISTS MetadataCdps (Id TEXT PRIMARY KEY, PrioritizationName TEXT, ValidationUnratedCount INTEGER DEFAULT 0, ValidationUnrated INTEGER DEFAULT 0, ValidationUnknownCount INTEGER DEFAULT 0, ValidationUnknown INTEGER DEFAULT 0, ValidationOffScaleCount INTEGER DEFAULT 0, ValidationOffScale INTEGER DEFAULT 0, RecordCount INTEGER DEFAULT 0, OffScaleCount INTEGER DEFAULT 0, UnratedCount INTEGER DEFAULT 0, FieldsCount INTEGER DEFAULT 0, NumberOfRules INTEGER DEFAULT 0, PADatabaseConnectionType TEXT, PADatabaseConnectionString TEXT, OriginalInputTableName TEXT, InputTableUpdated TEXT, DisplayField TEXT, Symbology TEXT, Comments TEXT, Misc TEXT, VersionID INTEGER DEFAULT 1, IsCurrent INTEGER DEFAULT 1, IsDeleted INTEGER DEFAULT 0, TaskDetailId TEXT NOT NULL UNIQUE)",
        ]
        for statement in statements:
            cursor.execute(statement)
        project_columns = {row["name"] for row in cursor.execute("PRAGMA table_info(Projects)").fetchall()}
        if "GISEnvironment" not in project_columns:
            cursor.execute("ALTER TABLE Projects ADD COLUMN GISEnvironment TEXT DEFAULT 'QGIS'")
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_projects_environment ON Projects(GISEnvironment, IsDeleted, IsCurrent)")
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_spatialreferences_project ON SpatialReferences(ProjectId)")
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_provenances_spatialreference ON Provenances(SpatialReferenceId)")
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_taskdetails_provenance ON TaskDetails(ProvenanceId)")
        conn.commit()
        conn.close()

    @staticmethod
    def _now() -> str:
        return datetime.now().isoformat()

    @staticmethod
    def _user() -> str:
        return os.environ.get("USERNAME") or os.environ.get("USER") or "unknown"

    @staticmethod
    def _decode_json(value: Optional[str], default):
        if not value:
            return default
        try:
            return json.loads(value)
        except Exception:
            return default

    @classmethod
    def _decode_misc(cls, value: Optional[str]) -> Dict[str, Any]:
        parsed = cls._decode_json(value, {})
        return parsed if isinstance(parsed, dict) else {}

    @staticmethod
    def _encode_misc(value: Dict[str, Any]) -> str:
        return json.dumps(value or {}, ensure_ascii=True)

    @classmethod
    def _to_csv(cls, value: Any) -> str:
        if not value:
            return ""
        if isinstance(value, str):
            text = value.strip()
            if text.startswith("[") and text.endswith("]"):
                parsed = cls._decode_json(text, [])
                if isinstance(parsed, list):
                    return ",".join(str(item).strip() for item in parsed if str(item).strip())
            return text
        if isinstance(value, dict):
            value = list(value.values())
        if isinstance(value, Iterable):
            return ",".join(str(item).strip() for item in value if str(item).strip())
        return str(value).strip()

    @classmethod
    def _from_csv(cls, value: Optional[str]) -> List[str]:
        if not value:
            return []
        text = str(value).strip()
        if not text:
            return []
        if text.startswith("[") and text.endswith("]"):
            parsed = cls._decode_json(text, [])
            if isinstance(parsed, list):
                return [str(item).strip() for item in parsed if str(item).strip()]
        return [part.strip() for part in text.split(",") if part.strip()]

    @staticmethod
    def _upsert_sql(table_name: str, columns: List[str], conflict_column: str = "Id") -> str:
        assignments = ", ".join(
            f"{column}=excluded.{column}"
            for column in columns
            if column not in {"Id", conflict_column}
        )
        placeholders = ", ".join(["?"] * len(columns))
        return (
            f"INSERT INTO {table_name} ({', '.join(columns)}) VALUES ({placeholders}) "
            f"ON CONFLICT({conflict_column}) DO UPDATE SET {assignments}"
        )

    def _project_from_row(self, row: sqlite3.Row) -> ProjectMetadata:
        metadata = self._decode_misc(row["Misc"])
        metadata.setdefault("project_file", row["MapDocumentName"] or "")
        metadata.setdefault("database_path", row["GeoDatabaseConnectionString"] or "")
        metadata.setdefault("directory", str(Path(row["MapDocumentName"]).parent) if row["MapDocumentName"] else "")
        metadata.setdefault("workspace_paths", self._from_csv(row["WorkspacePaths"]))
        metadata.setdefault("xml_project_tree", row["XMLProjectTree"] or "")
        metadata.setdefault("map_table_of_content", row["MapTableOfContent"] or "")
        return ProjectMetadata(
            project_id=row["Id"],
            name=row["ProjectName"] or "",
            description=row["Comments"] or "",
            project_file=row["MapDocumentName"] or "",
            input_layers=self._from_csv(row["BaseLayerNames"]),
            created_at=row["CreationDateTime"] or "",
            updated_at=row["ModificationDate"] or row["CreationDateTime"] or "",
            metadata=metadata,
        )

    def _spatial_reference_from_row(self, row: sqlite3.Row) -> SpatialReferenceMetadata:
        metadata = self._decode_misc(row["Misc"])
        return SpatialReferenceMetadata(
            spatial_reference_id=row["Id"],
            project_id=row["ProjectId"],
            name=row["SpatialReferenceName"] or "",
            comments=row["Comments"] or "",
            output_layer_name=row["OverlayLayerName"] or "",
            reference_layer_name=metadata.get("reference_layer_name", ""),
            created_at=row["CreationDateTime"] or "",
            updated_at=row["ModificationDate"] or row["CreationDateTime"] or "",
            metadata=metadata,
        )

    def _provenance_from_row(self, row: sqlite3.Row) -> ProvenanceMetadata:
        return ProvenanceMetadata(
            provenance_id=row["Id"],
            spatial_reference_id=row["SpatialReferenceId"],
            project_id=row["ProjectId"],
            name=row["ProvenanceName"] or "",
            description=row["Comments"] or "",
            created_at=row["CreationDateTime"] or "",
            updated_at=row["ModificationDate"] or row["CreationDateTime"] or "",
            metadata=self._decode_misc(row["Misc"]),
        )

    def save_project(
        self,
        project_id: str,
        name: str,
        description: str,
        project_file: str,
        input_layers: List[str],
        metadata: Dict[str, Any] = None,
    ) -> bool:
        try:
            existing = self.get_project(project_id)
            now = self._now()
            payload = dict(metadata or {})
            payload.setdefault("project_file", project_file)
            payload.setdefault("directory", str(Path(project_file).parent) if project_file else "")
            payload.setdefault("workspace_paths", payload.get("workspace_paths") or [payload.get("directory", "")])
            database_path = payload.get("database_path", "") or ""
            columns = [
                "Id", "ProjectName", "CreatedByEMDSVersion", "CreationUserNames", "CreationDateTime",
                "ModificationUserName", "ModificationDate", "BaseLayerNames", "BaseLayerNameSources",
                "Symbology", "GeoDatabaseType", "GeoDatabaseName", "GeoDatabaseConnectionString",
                "MapDocumentName", "WorkspacePaths", "EMDSDatabaseName", "EMDSRasterFileNames",
                "Comments", "Misc", "VersionID", "IsCurrent", "IsDeleted", "XMLProjectTree",
                "MapTableOfContent", "GISEnvironment",
            ]
            values = [
                project_id,
                name,
                payload.get("plugin_version", ""),
                payload.get("creation_user_names", self._user()),
                existing.created_at if existing else now,
                payload.get("modification_user_name", self._user()),
                now,
                self._to_csv(input_layers),
                self._to_csv(payload.get("input_layer_sources")),
                payload.get("symbology", ""),
                payload.get("geodatabase_type", "GeoPackage" if database_path else ""),
                Path(database_path).name if database_path else "",
                database_path,
                project_file,
                self._to_csv(payload.get("workspace_paths")),
                payload.get("emds_database_name", str(self.db_path)),
                self._to_csv(payload.get("emds_raster_file_names")),
                description,
                self._encode_misc(payload),
                int(payload.get("version_id", 1)),
                1,
                0,
                payload.get("xml_project_tree", (existing.metadata or {}).get("xml_project_tree", "") if existing else ""),
                payload.get("map_table_of_content", (existing.metadata or {}).get("map_table_of_content", "") if existing else ""),
                payload.get("gis_environment", self.GIS_ENVIRONMENT_QGIS),
            ]
            conn = self._connect()
            conn.cursor().execute(self._upsert_sql("Projects", columns), values)
            conn.commit()
            conn.close()
            return True
        except Exception as exc:
            print(f"Error saving project metadata: {exc}")
            return False

    def get_project(self, project_id: str) -> Optional[ProjectMetadata]:
        try:
            conn = self._connect()
            row = conn.cursor().execute(
                "SELECT * FROM Projects WHERE Id = ? AND COALESCE(IsDeleted, 0) = 0",
                (project_id,),
            ).fetchone()
            conn.close()
            return self._project_from_row(row) if row else None
        except Exception as exc:
            print(f"Error retrieving project: {exc}")
            return None

    def get_project_by_file(self, project_file: str) -> Optional[ProjectMetadata]:
        try:
            conn = self._connect()
            row = conn.cursor().execute(
                "SELECT * FROM Projects WHERE MapDocumentName = ? AND COALESCE(IsDeleted, 0) = 0",
                (project_file,),
            ).fetchone()
            conn.close()
            return self._project_from_row(row) if row else None
        except Exception as exc:
            print(f"Error retrieving project by file: {exc}")
            return None

    def get_all_projects(self) -> List[ProjectMetadata]:
        try:
            conn = self._connect()
            rows = conn.cursor().execute(
                """
                SELECT * FROM Projects
                WHERE COALESCE(IsDeleted, 0) = 0
                  AND COALESCE(IsCurrent, 1) = 1
                  AND (
                        lower(COALESCE(MapDocumentName, '')) LIKE '%.qgs'
                        OR lower(COALESCE(MapDocumentName, '')) LIKE '%.qgz'
                        OR (
                            COALESCE(GISEnvironment, '') = ?
                            AND lower(COALESCE(MapDocumentName, '')) NOT LIKE '%.aprx'
                            AND lower(COALESCE(MapDocumentName, '')) NOT LIKE '%.mxd'
                        )
                  )
                ORDER BY COALESCE(ModificationDate, CreationDateTime) DESC
                """,
                (self.GIS_ENVIRONMENT_QGIS,),
            ).fetchall()
            conn.close()
            return [self._project_from_row(row) for row in rows]
        except Exception as exc:
            print(f"Error retrieving projects: {exc}")
            return []

    def update_project(
        self,
        project_id: str,
        name: str = None,
        description: str = None,
        metadata: Dict[str, Any] = None,
    ) -> bool:
        existing = self.get_project(project_id)
        if existing is None:
            return False
        merged = dict(existing.metadata or {})
        merged.update(metadata or {})
        return self.save_project(
            project_id=project_id,
            name=name or existing.name,
            description=existing.description if description is None else description,
            project_file=merged.get("project_file", existing.project_file),
            input_layers=merged.get("input_layers", existing.input_layers),
            metadata=merged,
        )

    def delete_project(self, project_id: str) -> bool:
        try:
            conn = self._connect()
            conn.cursor().execute(
                "UPDATE Projects SET IsDeleted = 1, IsCurrent = 0 WHERE Id = ?",
                (project_id,),
            )
            conn.commit()
            conn.close()
            return True
        except Exception as exc:
            print(f"Error deleting project: {exc}")
            return False

    def save_spatial_reference(
        self,
        spatial_reference_id: str,
        project_id: str,
        name: str,
        comments: str,
        output_layer_name: str,
        reference_layer_name: str = "",
        metadata: Dict[str, Any] = None,
    ) -> bool:
        try:
            existing = self.get_spatial_reference(spatial_reference_id)
            now = self._now()
            payload = dict(metadata or {})
            payload["reference_layer_name"] = reference_layer_name
            database_path = payload.get("database_path", "") or ""
            columns = [
                "Id", "SpatialReferenceName", "CreationUserNames", "CreationDateTime",
                "ModificationUserName", "ModificationDate", "OverlayLayerName",
                "RasterOverlayFileName", "OriginalSourceWebServices", "OriginalSourceTablesDBTypes",
                "OriginalSourceTablesDBConnectionStrings", "OriginalSourceTablesNames",
                "OriginalSourceRasterFileNames", "OriginalSourceRasterTableNames", "Layers",
                "Symbology", "Comments", "Misc", "VersionID", "IsCurrent", "IsDeleted", "ProjectId",
            ]
            values = [
                spatial_reference_id,
                name,
                payload.get("creation_user_names", self._user()),
                existing.created_at if existing else now,
                payload.get("modification_user_name", self._user()),
                now,
                output_layer_name,
                payload.get("raster_overlay_file_name", ""),
                self._to_csv(payload.get("original_source_web_services")),
                self._to_csv(payload.get("original_source_table_db_types") or (["GeoPackage"] if database_path else [])),
                self._to_csv(payload.get("original_source_db_connection_strings") or ([database_path] if database_path else [])),
                self._to_csv(payload.get("original_source_table_names") or payload.get("input_layers") or []),
                self._to_csv(payload.get("original_source_raster_file_names")),
                self._to_csv(payload.get("original_source_raster_table_names")),
                self._to_csv(payload.get("assessment_layer_names") or payload.get("input_layers") or [output_layer_name]),
                payload.get("symbology", ""),
                comments,
                self._encode_misc(payload),
                int(payload.get("version_id", 1)),
                1,
                0,
                project_id,
            ]
            conn = self._connect()
            conn.cursor().execute(self._upsert_sql("SpatialReferences", columns), values)
            conn.commit()
            conn.close()
            return True
        except Exception as exc:
            print(f"Error saving spatial reference metadata: {exc}")
            return False

    def get_spatial_reference(self, spatial_reference_id: str) -> Optional[SpatialReferenceMetadata]:
        try:
            conn = self._connect()
            row = conn.cursor().execute(
                "SELECT * FROM SpatialReferences WHERE Id = ? AND COALESCE(IsDeleted, 0) = 0",
                (spatial_reference_id,),
            ).fetchone()
            conn.close()
            return self._spatial_reference_from_row(row) if row else None
        except Exception as exc:
            print(f"Error retrieving spatial reference: {exc}")
            return None

    def get_spatial_references_for_project(self, project_id: str) -> List[SpatialReferenceMetadata]:
        try:
            conn = self._connect()
            rows = conn.cursor().execute(
                "SELECT * FROM SpatialReferences WHERE ProjectId = ? AND COALESCE(IsDeleted, 0) = 0 ORDER BY COALESCE(ModificationDate, CreationDateTime) ASC",
                (project_id,),
            ).fetchall()
            conn.close()
            return [self._spatial_reference_from_row(row) for row in rows]
        except Exception as exc:
            print(f"Error retrieving spatial references: {exc}")
            return []

    def delete_spatial_reference(self, spatial_reference_id: str) -> bool:
        try:
            conn = self._connect()
            conn.cursor().execute(
                "UPDATE SpatialReferences SET IsDeleted = 1, IsCurrent = 0 WHERE Id = ?",
                (spatial_reference_id,),
            )
            conn.commit()
            conn.close()
            return True
        except Exception as exc:
            print(f"Error deleting spatial reference metadata: {exc}")
            return False

    def save_provenance(
        self,
        provenance_id: str,
        spatial_reference_id: str,
        project_id: str,
        name: str,
        description: str = "",
        metadata: Dict[str, Any] = None,
    ) -> bool:
        try:
            existing = self.get_provenance(provenance_id)
            now = self._now()
            payload = dict(metadata or {})
            database_path = payload.get("database_path", "") or ""
            columns = [
                "Id", "ProvenanceName", "CreationUserNames", "CreationDateTime", "ModificationUserName",
                "ModificationDate", "WorkflowTaskList", "OriginalInputWebServices", "OriginalInputTablesDBTypes",
                "OriginalInputTablesDBConnectionStrings", "OriginalInputTablesNames", "OriginalInputRasterTablesNames",
                "OriginalInputRasterFileNames", "FinalOutputWebServices", "FinalOutputTablesDBTypes",
                "FinalOutputTablesDBConnectionStrings", "FinalOutputTablesNames", "FinalOutputRasterTablesNames",
                "FinalOutputRasterFileNames", "Symbology", "Comments", "Misc", "VersionID", "IsCurrent",
                "IsDeleted", "ProjectId", "SpatialReferenceId",
            ]
            values = [
                provenance_id,
                name,
                payload.get("creation_user_names", self._user()),
                existing.created_at if existing else now,
                payload.get("modification_user_name", self._user()),
                now,
                self._to_csv(payload.get("workflow_task_list")),
                self._to_csv(payload.get("original_input_web_services")),
                self._to_csv(payload.get("original_input_table_db_types") or (["GeoPackage"] if database_path else [])),
                self._to_csv(payload.get("original_input_db_connection_strings") or ([database_path] if database_path else [])),
                self._to_csv(payload.get("original_input_tables") or payload.get("input_table_name") or []),
                self._to_csv(payload.get("original_input_raster_table_names")),
                self._to_csv(payload.get("original_input_raster_file_names")),
                self._to_csv(payload.get("final_output_web_services")),
                self._to_csv(payload.get("final_output_table_db_types") or (["GeoPackage"] if database_path else [])),
                self._to_csv(payload.get("final_output_db_connection_strings") or ([database_path] if database_path else [])),
                self._to_csv(payload.get("final_output_tables") or payload.get("output_table_name") or []),
                self._to_csv(payload.get("final_output_raster_table_names")),
                self._to_csv(payload.get("final_output_raster_file_names")),
                payload.get("symbology", ""),
                description,
                self._encode_misc(payload),
                int(payload.get("version_id", 1)),
                1,
                0,
                project_id,
                spatial_reference_id,
            ]
            conn = self._connect()
            conn.cursor().execute(self._upsert_sql("Provenances", columns), values)
            conn.commit()
            conn.close()
            return True
        except Exception as exc:
            print(f"Error saving provenance metadata: {exc}")
            return False

    def get_provenance(self, provenance_id: str) -> Optional[ProvenanceMetadata]:
        try:
            conn = self._connect()
            row = conn.cursor().execute(
                "SELECT * FROM Provenances WHERE Id = ? AND COALESCE(IsDeleted, 0) = 0",
                (provenance_id,),
            ).fetchone()
            conn.close()
            return self._provenance_from_row(row) if row else None
        except Exception as exc:
            print(f"Error retrieving provenance metadata: {exc}")
            return None

    def get_provenances_for_spatial_reference(self, spatial_reference_id: str) -> List[ProvenanceMetadata]:
        try:
            conn = self._connect()
            rows = conn.cursor().execute(
                "SELECT * FROM Provenances WHERE SpatialReferenceId = ? AND COALESCE(IsDeleted, 0) = 0 ORDER BY COALESCE(ModificationDate, CreationDateTime) ASC",
                (spatial_reference_id,),
            ).fetchall()
            conn.close()
            return [self._provenance_from_row(row) for row in rows]
        except Exception as exc:
            print(f"Error retrieving provenances: {exc}")
            return []

    def delete_provenance(self, provenance_id: str) -> bool:
        try:
            conn = self._connect()
            conn.cursor().execute(
                "UPDATE Provenances SET IsDeleted = 1, IsCurrent = 0 WHERE Id = ?",
                (provenance_id,),
            )
            conn.commit()
            conn.close()
            return True
        except Exception as exc:
            print(f"Error deleting provenance metadata: {exc}")
            return False

    def save_task_detail(
        self,
        task_id: str,
        provenance_id: str,
        project_id: str,
        spatial_reference_id: str,
        name: str,
        engine_type: str,
        model_path: str = "",
        input_tables: List[str] = None,
        result_tables: List[str] = None,
        parent_task_id: str = "",
        metadata: Dict[str, Any] = None,
    ) -> bool:
        try:
            existing = self.get_task_detail(task_id)
            now = self._now()
            payload = dict(metadata or {})
            payload.update(
                {
                    "project_id": project_id,
                    "assessment_id": spatial_reference_id,
                    "provenance_id": provenance_id,
                    "model_file": model_path or payload.get("model_file", ""),
                }
            )
            database_path = payload.get("database_path", "") or ""
            normalized_inputs = input_tables or self._from_csv(payload.get("input_tables"))
            normalized_results = result_tables or self._from_csv(payload.get("result_tables"))
            result_aliases = payload.get("results_alias_tables") or payload.get("result_alias_tables") or normalized_results
            columns = [
                "Id", "TaskDetailName", "CreationUserNames", "Model", "ModelFilename", "ModelUpdateDate",
                "TaskStepsPerformed", "CreationDateTime", "ModificationUserName", "ModificationDate",
                "InputTables", "ResultsTables", "ResultsAliasTables", "InputRasterTables", "InputRasterFileNames",
                "ResultsRasterFileNames", "ResultsRasterTableNames", "EngineType", "DatabaseType", "DatabaseNames",
                "DatabaseConnectionString", "Category", "StartDateTime", "EndDateTime", "ParentTaskID",
                "IsDisplayedOnMap", "IsScenario", "Symbology", "Comments", "Misc", "VersionID",
                "IsCurrent", "IsDeleted", "ProvenanceId",
            ]
            values = [
                task_id,
                name,
                payload.get("creation_user_names", self._user()),
                payload.get("model_blob"),
                payload.get("model_file", model_path),
                payload.get("model_update_date", ""),
                payload.get("task_steps_performed", ""),
                existing.created_at if existing else now,
                payload.get("modification_user_name", self._user()),
                now,
                self._to_csv(normalized_inputs),
                self._to_csv(normalized_results),
                self._to_csv(result_aliases),
                self._to_csv(payload.get("input_raster_tables")),
                self._to_csv(payload.get("input_raster_file_names")),
                self._to_csv(payload.get("results_raster_file_names")),
                self._to_csv(payload.get("results_raster_table_names")),
                engine_type,
                payload.get("database_type", "GeoPackage" if database_path else ""),
                self._to_csv(payload.get("database_names") or ([database_path] if database_path else [])),
                database_path,
                payload.get("category", engine_type),
                payload.get("start_datetime", ""),
                payload.get("end_datetime", ""),
                parent_task_id or "",
                1 if (payload.get("is_displayed_on_map") or payload.get("goal_layers") or normalized_results) else 0,
                1 if payload.get("is_scenario") else 0,
                payload.get("symbology", ""),
                payload.get("comments", ""),
                self._encode_misc(payload),
                int(payload.get("version_id", 1)),
                1,
                0,
                provenance_id,
            ]
            conn = self._connect()
            conn.cursor().execute(self._upsert_sql("TaskDetails", columns), values)
            conn.commit()
            conn.close()
            if engine_type == "NetWeaver":
                self.save_metadata_nw(task_id, name, normalized_results, payload)
            elif engine_type == "CDP":
                self.save_metadata_cdp(task_id, name, normalized_inputs, database_path, payload)
            return True
        except Exception as exc:
            print(f"Error saving task metadata: {exc}")
            return False

    def save_metadata_nw(
        self,
        task_id: str,
        task_name: str,
        result_tables: List[str],
        metadata: Dict[str, Any],
    ) -> bool:
        try:
            conn = self._connect()
            cursor = conn.cursor()
            existing = cursor.execute(
                "SELECT Id FROM MetadataNws WHERE TaskDetailId = ?",
                (task_id,),
            ).fetchone()
            payload = {
                "task_name": task_name,
                "model_file": metadata.get("model_file", ""),
                "selected_topics": metadata.get("selected_topics", []),
                "map_topics": metadata.get("map_topics", []),
                "goal_field_map": metadata.get("goal_field_map", {}),
                "goal_layers": metadata.get("goal_layers", []),
                "output_table_name": metadata.get("output_table_name") or (result_tables[0] if result_tables else ""),
                "execution_id": metadata.get("execution_id", ""),
            }
            values = (
                metadata.get("influence_table_name") or payload["output_table_name"],
                metadata.get("influence_rank_table_name", ""),
                metadata.get("influence_rank_summary_table_name", ""),
                metadata.get("symbology", ""),
                metadata.get("comments", ""),
                self._encode_misc(payload),
                int(metadata.get("version_id", 1)),
                1,
                0,
                task_id,
            )
            if existing:
                cursor.execute(
                    """
                    UPDATE MetadataNws
                    SET InfluenceTableName = ?, InfluenceRankTableName = ?, InfluenceRankSummaryTableName = ?,
                        Symbology = ?, Comments = ?, Misc = ?, VersionID = ?, IsCurrent = ?, IsDeleted = ?
                    WHERE TaskDetailId = ?
                    """,
                    values,
                )
            else:
                cursor.execute(
                    """
                    INSERT INTO MetadataNws
                    (Id, InfluenceTableName, InfluenceRankTableName, InfluenceRankSummaryTableName,
                     Symbology, Comments, Misc, VersionID, IsCurrent, IsDeleted, TaskDetailId)
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                    """,
                    (str(uuid.uuid4()),) + values,
                )
            conn.commit()
            conn.close()
            return True
        except Exception as exc:
            print(f"Error saving NetWeaver metadata: {exc}")
            return False

    def save_metadata_cdp(
        self,
        task_id: str,
        task_name: str,
        input_tables: List[str],
        database_path: str,
        metadata: Dict[str, Any],
    ) -> bool:
        try:
            conn = self._connect()
            cursor = conn.cursor()
            existing = cursor.execute(
                "SELECT Id FROM MetadataCdps WHERE TaskDetailId = ?",
                (task_id,),
            ).fetchone()
            payload = {
                "task_name": task_name,
                "model_file": metadata.get("model_file", ""),
                "config": metadata.get("config", ""),
                "database_path": database_path,
                "execution_id": metadata.get("execution_id", ""),
            }
            values = (
                metadata.get("prioritization_name") or task_name,
                int(metadata.get("validation_unrated_count", 0)),
                int(metadata.get("validation_unrated", 0)),
                int(metadata.get("validation_unknown_count", 0)),
                int(metadata.get("validation_unknown", 0)),
                int(metadata.get("validation_off_scale_count", 0)),
                int(metadata.get("validation_off_scale", 0)),
                int(metadata.get("record_count", 0)),
                int(metadata.get("off_scale_count", 0)),
                int(metadata.get("unrated_count", 0)),
                int(metadata.get("fields_count", 0)),
                int(metadata.get("number_of_rules", 0)),
                metadata.get("pa_database_connection_type", "GeoPackage" if database_path else ""),
                metadata.get("pa_database_connection_string", database_path),
                metadata.get("original_input_table_name") or (input_tables[0] if input_tables else ""),
                metadata.get("input_table_updated", ""),
                metadata.get("display_field", ""),
                metadata.get("symbology", ""),
                metadata.get("comments", ""),
                self._encode_misc(payload),
                int(metadata.get("version_id", 1)),
                1,
                0,
                task_id,
            )
            if existing:
                cursor.execute(
                    """
                    UPDATE MetadataCdps
                    SET PrioritizationName = ?, ValidationUnratedCount = ?, ValidationUnrated = ?,
                        ValidationUnknownCount = ?, ValidationUnknown = ?, ValidationOffScaleCount = ?,
                        ValidationOffScale = ?, RecordCount = ?, OffScaleCount = ?, UnratedCount = ?,
                        FieldsCount = ?, NumberOfRules = ?, PADatabaseConnectionType = ?,
                        PADatabaseConnectionString = ?, OriginalInputTableName = ?, InputTableUpdated = ?,
                        DisplayField = ?, Symbology = ?, Comments = ?, Misc = ?, VersionID = ?,
                        IsCurrent = ?, IsDeleted = ?
                    WHERE TaskDetailId = ?
                    """,
                    values,
                )
            else:
                cursor.execute(
                    """
                    INSERT INTO MetadataCdps
                    (Id, PrioritizationName, ValidationUnratedCount, ValidationUnrated, ValidationUnknownCount,
                     ValidationUnknown, ValidationOffScaleCount, ValidationOffScale, RecordCount, OffScaleCount,
                     UnratedCount, FieldsCount, NumberOfRules, PADatabaseConnectionType, PADatabaseConnectionString,
                     OriginalInputTableName, InputTableUpdated, DisplayField, Symbology, Comments, Misc,
                     VersionID, IsCurrent, IsDeleted, TaskDetailId)
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                    """,
                    (str(uuid.uuid4()),) + values,
                )
            conn.commit()
            conn.close()
            return True
        except Exception as exc:
            print(f"Error saving CDP metadata: {exc}")
            return False

    def get_metadata_nw(self, task_id: str) -> Dict[str, Any]:
        try:
            conn = self._connect()
            row = conn.cursor().execute(
                "SELECT * FROM MetadataNws WHERE TaskDetailId = ? AND COALESCE(IsDeleted, 0) = 0",
                (task_id,),
            ).fetchone()
            conn.close()
            if not row:
                return {}
            payload = self._decode_misc(row["Misc"])
            payload.update(
                {
                    "influence_table_name": row["InfluenceTableName"] or "",
                    "influence_rank_table_name": row["InfluenceRankTableName"] or "",
                    "influence_rank_summary_table_name": row["InfluenceRankSummaryTableName"] or "",
                    "symbology": row["Symbology"] or "",
                    "comments": row["Comments"] or "",
                }
            )
            return payload
        except Exception:
            return {}

    def get_metadata_cdp(self, task_id: str) -> Dict[str, Any]:
        try:
            conn = self._connect()
            row = conn.cursor().execute(
                "SELECT * FROM MetadataCdps WHERE TaskDetailId = ? AND COALESCE(IsDeleted, 0) = 0",
                (task_id,),
            ).fetchone()
            conn.close()
            if not row:
                return {}
            payload = self._decode_misc(row["Misc"])
            payload.update(
                {
                    "prioritization_name": row["PrioritizationName"] or "",
                    "display_field": row["DisplayField"] or "",
                    "original_input_table_name": row["OriginalInputTableName"] or "",
                    "input_table_updated": row["InputTableUpdated"] or "",
                    "pa_database_connection_type": row["PADatabaseConnectionType"] or "",
                    "pa_database_connection_string": row["PADatabaseConnectionString"] or "",
                }
            )
            return payload
        except Exception:
            return {}

    def _task_detail_from_row(self, row: sqlite3.Row) -> TaskDetailMetadata:
        metadata = self._decode_misc(row["Misc"])
        metadata.setdefault("project_id", row["ProjectId"] or "")
        metadata.setdefault("assessment_id", row["SpatialReferenceId"] or "")
        if row["EngineType"] == "NetWeaver":
            metadata["netweaver_metadata"] = self.get_metadata_nw(row["Id"])
        if row["EngineType"] == "CDP":
            metadata["cdp_metadata"] = self.get_metadata_cdp(row["Id"])
        return TaskDetailMetadata(
            task_id=row["Id"],
            provenance_id=row["ProvenanceId"],
            project_id=row["ProjectId"] or "",
            spatial_reference_id=row["SpatialReferenceId"] or "",
            parent_task_id=row["ParentTaskID"] or "",
            name=row["TaskDetailName"] or "",
            engine_type=row["EngineType"] or "",
            model_path=metadata.get("model_file") or row["ModelFilename"] or "",
            input_tables=self._from_csv(row["InputTables"]),
            result_tables=self._from_csv(row["ResultsTables"]),
            created_at=row["CreationDateTime"] or "",
            updated_at=row["ModificationDate"] or row["CreationDateTime"] or "",
            metadata=metadata,
        )

    def get_task_detail(self, task_id: str) -> Optional[TaskDetailMetadata]:
        try:
            conn = self._connect()
            row = conn.cursor().execute(
                "SELECT td.*, p.ProjectId, p.SpatialReferenceId FROM TaskDetails td JOIN Provenances p ON p.Id = td.ProvenanceId WHERE td.Id = ? AND COALESCE(td.IsDeleted, 0) = 0",
                (task_id,),
            ).fetchone()
            conn.close()
            return self._task_detail_from_row(row) if row else None
        except Exception as exc:
            print(f"Error retrieving task metadata: {exc}")
            return None

    def get_task_details_for_provenance(self, provenance_id: str) -> List[TaskDetailMetadata]:
        try:
            conn = self._connect()
            rows = conn.cursor().execute(
                "SELECT td.*, p.ProjectId, p.SpatialReferenceId FROM TaskDetails td JOIN Provenances p ON p.Id = td.ProvenanceId WHERE td.ProvenanceId = ? AND COALESCE(td.IsDeleted, 0) = 0 ORDER BY COALESCE(td.ModificationDate, td.CreationDateTime) ASC",
                (provenance_id,),
            ).fetchall()
            conn.close()
            return [self._task_detail_from_row(row) for row in rows]
        except Exception as exc:
            print(f"Error retrieving tasks: {exc}")
            return []

    def delete_task_detail(self, task_id: str) -> bool:
        try:
            conn = self._connect()
            cursor = conn.cursor()
            cursor.execute("UPDATE TaskDetails SET IsDeleted = 1, IsCurrent = 0 WHERE Id = ?", (task_id,))
            cursor.execute("UPDATE MetadataNws SET IsDeleted = 1, IsCurrent = 0 WHERE TaskDetailId = ?", (task_id,))
            cursor.execute("UPDATE MetadataCdps SET IsDeleted = 1, IsCurrent = 0 WHERE TaskDetailId = ?", (task_id,))
            conn.commit()
            conn.close()
            return True
        except Exception as exc:
            print(f"Error deleting task metadata: {exc}")
            return False

    def project_exists(self, project_id: str) -> bool:
        return self.get_project(project_id) is not None

    def get_project_count(self) -> int:
        try:
            conn = self._connect()
            row = conn.cursor().execute(
                """
                SELECT COUNT(*) AS count FROM Projects
                WHERE COALESCE(IsDeleted, 0) = 0
                  AND (
                        lower(COALESCE(MapDocumentName, '')) LIKE '%.qgs'
                        OR lower(COALESCE(MapDocumentName, '')) LIKE '%.qgz'
                        OR (
                            COALESCE(GISEnvironment, '') = ?
                            AND lower(COALESCE(MapDocumentName, '')) NOT LIKE '%.aprx'
                            AND lower(COALESCE(MapDocumentName, '')) NOT LIKE '%.mxd'
                        )
                  )
                """,
                (self.GIS_ENVIRONMENT_QGIS,),
            ).fetchone()
            conn.close()
            return int(row["count"]) if row else 0
        except Exception as exc:
            print(f"Error counting projects: {exc}")
            return 0
