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.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) -> 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.
    """
    sourceCrs = QgsCoordinateReferenceSystem(src_crs)
    destCrs = QgsCoordinateReferenceSystem(DEFAULT_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 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)
    eoh = Column("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
        )
    )


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)
