"""
GeoPackage Database Manager

Handles creation and management of GeoPackage databases for EMDS projects.
GeoPackage is a standardized OGC format for storing vector geospatial data in SQLite.

Provides functionality to:
- Create new GeoPackage databases
- Copy vector layers from QGIS to GeoPackage
- Remap layer sources to point to GeoPackage database

Works with: QGIS, ArcGIS Pro, PostGIS, and other GIS tools.
"""

import sqlite3
import os
from pathlib import Path
from typing import List, Optional, Dict, Tuple
from datetime import datetime
from . import debug_logger


class SpatialiteManager:
    """Manages GeoPackage database operations for EMDS projects

    GeoPackage is an OGC standard format for storing vector layers in SQLite.
    Works seamlessly with QGIS, ArcGIS Pro, and other GIS tools.
    """

    def __init__(self):
        """Initialize the Spatialite manager"""
        self.connection = None
        self.database_path = None

    def create_database(self, database_path: str) -> bool:
        """
        Create a new GeoPackage database

        GeoPackage is a standardized SQLite-based format for vector geospatial data.

        Args:
            database_path: Full path where the database file will be created (typically .gpkg)

        Returns:
            True if successful, False otherwise
        """
        try:
            # Create parent directory if needed
            Path(database_path).parent.mkdir(parents=True, exist_ok=True)

            # Create database connection
            conn = sqlite3.connect(database_path)
            cursor = conn.cursor()

            # Try to enable SpatialLite extension
            spatialite_available = False
            try:
                cursor.enable_load_extension(True)
                try:
                    cursor.load_extension("mod_spatialite")
                    spatialite_available = True
                except sqlite3.OperationalError:
                    # Try alternative name
                    try:
                        cursor.load_extension("mod_spatialite.so")
                        spatialite_available = True
                    except:
                        pass
                except Exception:
                    pass

                # Try to initialize spatial metadata if extension loaded
                if spatialite_available:
                    try:
                        cursor.execute("SELECT InitSpatialMetadata(1)")
                    except Exception as e:
                        # Extension might not be fully functional
                        print(f"Warning: Could not initialize Spatialite metadata: {e}")
                        spatialite_available = False

            except Exception as e:
                print(f"Warning: Spatialite extension not available: {e}")
                spatialite_available = False

            # Even if Spatialite isn't available, we can still create a basic SQLite database
            # This allows layer copying to work via GeoPackage intermediate
            conn.commit()
            conn.close()

            self.database_path = database_path
            print(f"Database created: {database_path}")
            print(f"Spatialite available: {spatialite_available}")
            return True

        except Exception as e:
            print(f"Error creating database: {e}")
            import traceback
            traceback.print_exc()
            return False

    def copy_vector_layer_to_database(
        self,
        layer_name: str,
        source_layer,
        database_path: str
    ) -> Tuple[bool, str]:
        """
        Copy a vector layer from QGIS to GeoPackage database

        Exports the layer directly to a GeoPackage-formatted SQLite database.
        GeoPackage provides standardized storage for vector geospatial data.

        Args:
            layer_name: Name of the layer
            source_layer: QgsVectorLayer object from QGIS
            database_path: Path to target GeoPackage database (.gpkg file)

        Returns:
            Tuple of (success: bool, table_name: str)
        """
        try:
            from qgis.core import QgsVectorFileWriter, QgsCoordinateTransformContext
            from pathlib import Path

            # === DEBUG: Start of layer copy ===
            debug_logger.section(f"Starting layer copy: {layer_name}")
            debug_logger.debug("Layer copy initiated", {
                "layer_name": layer_name,
                "database_path": database_path,
                "source_layer_type": type(source_layer).__name__
            })

            # Sanitize table name for SQLite
            table_name = self._sanitize_table_name(layer_name)
            debug_logger.debug("Step 1 - Sanitize table name", {
                "original": layer_name,
                "sanitized": table_name
            })

            # Ensure parent directory exists
            file_path = Path(database_path)
            parent_dir = file_path.parent
            debug_logger.debug("Step 2 - Check parent directory", {
                "parent_directory": str(parent_dir),
                "exists": parent_dir.exists()
            })

            try:
                parent_dir.mkdir(parents=True, exist_ok=True)
                debug_logger.log("  Created/verified: SUCCESS")
            except Exception as e:
                debug_logger.error("Failed to create directory", {"error": str(e)})
                raise

            # Check if database file already exists
            file_exists = file_path.exists()
            debug_logger.debug("Step 3 - Check database file", {
                "database_file": database_path,
                "exists": file_exists,
                "size_bytes": file_path.stat().st_size if file_exists else "N/A"
            })

            # Note: Context is not needed with SaveVectorOptions API
            debug_logger.debug("Step 4 - Prepare for export (context handled internally)")

            # Get layer properties for debugging
            debug_logger.debug("Step 5 - Inspect source layer properties")
            try:
                debug_logger.debug("Layer properties", {
                    "crs": source_layer.crs().authid() if source_layer.crs() else "None",
                    "feature_count": source_layer.featureCount(),
                    "geometry_type": source_layer.geometryType(),
                    "provider": source_layer.providerType(),
                    "source": source_layer.source()[:100]
                })
            except Exception as e:
                debug_logger.error("Failed to inspect layer", {"error": str(e)})

            # Prepare options for writeAsVectorFormat
            debug_logger.debug("Step 6 - Prepare SaveVectorOptions")
            save_options = QgsVectorFileWriter.SaveVectorOptions()
            debug_logger.log("  SaveVectorOptions created: SUCCESS")

            save_options.driverName = "GPKG"
            debug_logger.log("  Set driverName: GPKG")

            save_options.layerName = table_name
            debug_logger.log(f"  Set layerName: {table_name}")

            # If file exists, try to append; otherwise create new
            if file_exists:
                debug_logger.debug("Step 7 - Set append mode (file exists)")
                # Keep existing layers in the GeoPackage and only overwrite the
                # current layer if it already exists.
                if hasattr(QgsVectorFileWriter, "CreateOrOverwriteLayer"):
                    save_options.actionOnExistingFile = QgsVectorFileWriter.CreateOrOverwriteLayer
                    debug_logger.log("  Set actionOnExistingFile: CreateOrOverwriteLayer")
                else:
                    # Fallback for older API variants.
                    save_options.actionOnExistingFile = QgsVectorFileWriter.CreateOrOverwriteFile
                    debug_logger.log("  Set actionOnExistingFile: CreateOrOverwriteFile (fallback)")
            else:
                debug_logger.log("Step 7 - File will be created (new file)")

            # Write layer to database
            debug_logger.debug("Step 8 - Call writeAsVectorFormat", {
                "source_layer": layer_name,
                "database_path": database_path,
                "driverName": "GPKG",
                "layerName": table_name,
                "actionOnExistingFile": "CreateOrOverwriteLayer/CreateOrOverwriteFile(fallback)" if file_exists else "CreateOrOverwriteFile (default)"
            })

            # Use the correct API signature (context is NOT passed with SaveVectorOptions)
            error = QgsVectorFileWriter.writeAsVectorFormat(
                source_layer,
                database_path,
                save_options
            )

            # Check result
            debug_logger.debug("Step 9 - Check writeAsVectorFormat result", {
                "error_code": error[0],
                "error_message": error[1] if len(error) > 1 else "No additional message"
            })

            if error[0] != QgsVectorFileWriter.NoError:
                debug_logger.error("WriteAsVectorFormat failed", {
                    "error_tuple": str(error)
                })
                return False, ""

            # Verify file was created/updated
            debug_logger.debug("Step 10 - Verify database file")
            if file_path.exists():
                file_size = file_path.stat().st_size
                debug_logger.debug("File verification", {
                    "file_exists": "YES",
                    "file_size_bytes": file_size
                })
                if file_size == 0:
                    debug_logger.warning("File is 0 bytes!")
                else:
                    debug_logger.success("File has content")
            else:
                debug_logger.error("File was not created!")
                return False, ""

            debug_logger.success(f"Successfully copied layer {layer_name} to database")
            return True, table_name

        except Exception as e:
            debug_logger.error("Exception during layer copy", {
                "layer": layer_name,
                "exception": str(e)
            })
            import traceback
            debug_logger.log(traceback.format_exc())
            return False, ""

    def _copy_from_gpkg_to_spatialite(
        self,
        gpkg_path: str,
        spatialite_path: str,
        table_name: str
    ) -> bool:
        """
        Copy vector data from GeoPackage to Spatialite database

        Args:
            gpkg_path: Path to source GeoPackage file
            spatialite_path: Path to target Spatialite database
            table_name: Name of table to create

        Returns:
            True if successful, False otherwise
        """
        try:
            # Connect to both databases
            gpkg_conn = sqlite3.connect(gpkg_path)
            spatialite_conn = sqlite3.connect(spatialite_path)

            # Get the actual table name from GeoPackage (usually 'gpkgext_XXX' or just the layer name)
            gpkg_cursor = gpkg_conn.cursor()
            gpkg_cursor.execute(
                "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' AND name NOT LIKE 'gpkg_%'"
            )
            tables = gpkg_cursor.fetchall()

            if not tables:
                gpkg_conn.close()
                spatialite_conn.close()
                return False

            source_table = tables[0][0]

            # Copy schema and data
            spatialite_cursor = spatialite_conn.cursor()

            # Get CREATE TABLE statement from GeoPackage
            gpkg_cursor.execute(
                f"SELECT sql FROM sqlite_master WHERE type='table' AND name='{source_table}'"
            )
            create_sql = gpkg_cursor.fetchone()

            if not create_sql:
                gpkg_conn.close()
                spatialite_conn.close()
                return False

            # Modify CREATE TABLE statement to use new table name
            modified_sql = create_sql[0].replace(f'"{source_table}"', f'"{table_name}"')
            modified_sql = modified_sql.replace(f"'{source_table}'", f"'{table_name}'")

            # Create table in Spatialite
            spatialite_cursor.execute(modified_sql)

            # Copy data
            gpkg_cursor.execute(f"SELECT * FROM '{source_table}'")
            columns_info = gpkg_cursor.description
            columns = [col[0] for col in columns_info]

            rows = gpkg_cursor.fetchall()

            if rows:
                placeholders = ", ".join(["?" for _ in columns])
                # Build column list with proper quoting
                quoted_columns = ", ".join([f'"{c}"' for c in columns])
                insert_sql = f'INSERT INTO "{table_name}" ({quoted_columns}) VALUES ({placeholders})'
                spatialite_cursor.executemany(insert_sql, rows)

            # Copy geometry info if present
            gpkg_cursor.execute(
                f"SELECT * FROM gpkg_geometry_columns WHERE table_name='{source_table}'"
            )
            geom_cols = gpkg_cursor.fetchall()

            if geom_cols:
                # Update geometry columns table
                for geom_col in geom_cols:
                    spatialite_cursor.execute(
                        f"INSERT OR REPLACE INTO gpkg_geometry_columns VALUES (?, ?, ?, ?, ?, ?)",
                        (table_name, geom_col[1], geom_col[2], geom_col[3], geom_col[4], geom_col[5])
                    )

            spatialite_conn.commit()
            spatialite_conn.close()
            gpkg_conn.close()

            return True

        except Exception as e:
            print(f"Error copying from GeoPackage to Spatialite: {e}")
            return False

    def get_layer_source_uri(
        self,
        database_path: str,
        table_name: str,
        geometry_column: str = "geometry"
    ) -> str:
        """
        Generate a QGIS layer source URI pointing to a Spatialite table

        Args:
            database_path: Path to Spatialite database
            table_name: Name of the table in the database
            geometry_column: Name of the geometry column (default: geometry)

        Returns:
            URI string for use with QgsVectorLayer
        """
        # QGIS Spatialite connection string format
        uri = (
            f"dbname='{database_path}' "
            f"table=\"{table_name}\" "
            f"(geometry) sql="
        )
        return uri

    def remap_layer_source(
        self,
        layer,
        database_path: str,
        table_name: str
    ) -> bool:
        """
        Change a QGIS layer's source to point to a table in the GeoPackage database

        Args:
            layer: QgsVectorLayer object to remap
            database_path: Path to GeoPackage database
            table_name: Name of the table in the database

        Returns:
            True if successful, False otherwise
        """
        try:
            # Generate new URI
            new_uri = self.get_layer_source_uri(database_path, table_name)

            # Set the layer's data source
            layer.setDataSource(
                new_uri,
                layer.name(),
                "spatialite"
            )

            return True

        except Exception as e:
            print(f"Error remapping layer source: {e}")
            return False

    @staticmethod
    def _sanitize_table_name(name: str) -> str:
        """
        Sanitize a name for use as a SQLite table name

        Args:
            name: Original name

        Returns:
            Sanitized name safe for SQLite
        """
        import re
        # Replace problematic characters
        safe_name = re.sub(r'[^a-zA-Z0-9_]', '_', name)
        # Remove leading/trailing underscores
        safe_name = safe_name.strip('_')
        # Ensure not empty
        if not safe_name:
            safe_name = "layer"
        # SQLite identifiers are case-insensitive, but we'll keep original case in quotes
        return safe_name

    def close(self):
        """Close database connection"""
        if self.connection:
            self.connection.close()
            self.connection = None
