from geoalchemy2 import Geometry
from qgis.core import (
    QgsCoordinateReferenceSystem,
    QgsCoordinateTransform,
    QgsGeometry,
    QgsProject,
)
from sqlalchemy import Column, DateTime, Float, ForeignKey, Integer, Numeric, String
from sqlalchemy.orm import declarative_base
from xplordb.datamodel.collar import Collar
from xplordb.datamodel.dataset import Dataset
from xplordb.datamodel.lith import Lith
from xplordb.datamodel.metadata import CollarMetadata, RawCollarMetadata
from xplordb.datamodel.person import Person
from xplordb.datamodel.survey import Survey

Base = declarative_base()

DEFAULT_SRID = 3857

DATASET_FK = "data_sets.name"
COLLAR_FK = "collar.hole_id"
PERSON_FK = "persons.code"


def convert_to_db_crs(
    src_crs: int, geom: QgsGeometry, dest_srid: int = None
) -> QgsGeometry:
    """
    Convert a geometry from a source CRS to a destination CRS

    :param src_crs: The CRS of the source geometry
    :type src_crs: int
    :param geom: the geometry to be transformed
    :type geom: QgsGeometry
    :return: A geometry in the default CRS of the project.
    """
    srid = DEFAULT_SRID if dest_srid is None else dest_srid
    sourceCrs = QgsCoordinateReferenceSystem(src_crs)
    destCrs = QgsCoordinateReferenceSystem(srid)
    tr = QgsCoordinateTransform(sourceCrs, destCrs, QgsProject.instance())
    geom.transform(tr)
    return geom


class SpatialitePerson(Person, Base):
    """
    Define spatialite columns for Person definition
    """

    __tablename__ = "persons"

    code = Column("code", String, primary_key=True)
    full_name = Column("full_name", String)
    loaded_by = Column(String)


class SpatialiteDataset(Dataset, Base):
    """
    Define spatialite columns for Dataset definition
    """

    __tablename__ = "data_sets"

    name = Column("name", String, primary_key=True)
    full_name = Column(String)
    loaded_by = Column(String, ForeignKey(PERSON_FK))


class SpatialiteSurvey(Survey, Base):
    """
    Define spatialite columns for Survey definition
    """

    __tablename__ = "survey"

    data_set = Column("data_set", String, ForeignKey(DATASET_FK))
    hole_id = Column("hole_id", String, ForeignKey(COLLAR_FK), primary_key=True)
    loaded_by = Column("loaded_by", String)
    # Column defined as Numeric so they can be used as primary keys
    # Value in Survey class defined as float : not used as decimal
    depth = Column("depth_m", Numeric(asdecimal=False), primary_key=True)
    dip = Column("dip", Float)
    azimuth = Column("azimuth", Float)


class SpatialiteCollarMetadata(CollarMetadata, Base):
    """
    Define spatialite columns for Collar metadata definition
    """

    __tablename__ = "metadata"
    hole_id = Column("hole_id", String, primary_key=True)


def create_spatialite_collar_metadata_table(
    collar_metadata: RawCollarMetadata,
) -> tuple:
    """
    Function returning updated SpatialiteCollarMetadata subclass instance for a specific hole_id and table definition
    """
    # we should derive base class to not alter it.
    class tmp(SpatialiteCollarMetadata):
        pass

    metadata_base = tmp

    for col_name, value in collar_metadata.extra_cols.items():
        data_type = String if type(value) == str else Float
        column = Column(col_name, data_type)
        if not hasattr(metadata_base, col_name):
            setattr(metadata_base, col_name, column)

    metadata_base_instance = metadata_base(collar_metadata.hole_id)
    for col_name, value in collar_metadata.extra_cols.items():

        setattr(metadata_base_instance, col_name, value)

    return metadata_base_instance, metadata_base


class SpatialiteCollar(Collar, Base):
    """
    Define spatialite columns for Collar definition
    """

    __tablename__ = "collar"

    hole_id = Column("hole_id", String, primary_key=True)
    data_set = Column("data_set", String, ForeignKey(DATASET_FK))
    x = Column("x", Float)
    y = Column("y", Float)
    z = Column("z", Float)
    srid = Column("srid", Integer)
    project_srid = Column("project_srid", Integer)
    eoh = Column("eoh", Float)
    dip = Column("dip", Float)
    azimuth = Column("azimuth", Float)
    planned_eoh = Column("planned_eoh", Float)
    loaded_by = Column("loaded_by", String)
    survey_date = Column("load_date", DateTime)
    geom = Column(
        Geometry(geometry_type="POINT", dimension=3, management=True, srid=DEFAULT_SRID)
    )
    geom_trace = Column(
        Geometry(
            geometry_type="LINESTRING", dimension=3, management=True, srid=DEFAULT_SRID
        )
    )
    planned_trace = Column(
        Geometry(
            geometry_type="LINESTRING", dimension=3, management=True, srid=DEFAULT_SRID
        )
    )

    effective_geom = Column(
        Geometry(
            geometry_type="LINESTRING", dimension=3, management=True, srid=DEFAULT_SRID
        )
    )

    planned_geom = Column(
        Geometry(
            geometry_type="LINESTRING", dimension=3, management=True, srid=DEFAULT_SRID
        )
    )


class SpatialiteLith(Lith, Base):
    """
    Define spatialite columns for Lith definition
    """

    __tablename__ = "lith"

    data_set = Column("data_set", String, ForeignKey(DATASET_FK))
    hole_id = Column("hole_id", String, ForeignKey(COLLAR_FK), primary_key=True)

    lith_code = Column("lith_code", String)

    # Column defined as Numeric so they can be used as primary keys
    # Value in Lith class defined as float : not used as decimal
    from_m = Column("from_m", Numeric(asdecimal=False), primary_key=True)
    to_m = Column("to_m", Numeric(asdecimal=False), primary_key=True)

    loaded_by = Column("loaded_by", String)
    logged_by = Column("logged_by", String)
