from sqlalchemy import Column, DateTime, Float, ForeignKey, Integer, String
from sqlalchemy.orm import declarative_base, relationship
from xplordb.datamodel.collar import Collar
from xplordb.datamodel.dataset import Dataset
from xplordb.datamodel.survey import Survey

from openlog.datamodel.connection.spatialite.database_object import DEFAULT_SRID

Base = declarative_base()

GEOTIC_PERSON_CODE: str = "geotic"
GEOTIC_DATASET: str = "geotic_dataset"


class GeoticDataset(Dataset, Base):
    """
    sqlalchemy Base describing GeoticDataset
    """

    __tablename__ = "tblNomProjet"
    idKey = Column(Integer, primary_key=True)
    name = Column("NomProjet", String)
    full_name = Column("Commentaire", String)
    loaded_by = GEOTIC_PERSON_CODE


class CollarCoordinates(Base):
    """
    sqlalchemy Base describing collar_base coordinates in Geotic database

    Missing in current version :
    - transform x / y / z value to point or get corresponding SRID

    """

    __tablename__ = "CollarCoordinates"
    idNuSondage = Column(
        "idNuSondage", ForeignKey("tblNuSondage.idKey"), primary_key=True
    )
    coord_type = Column("CoordinateType", primary_key=True)
    x = Column("CoordEast1", String)
    y = Column("CoordNorth1", String)
    z = Column("CoordElev1", String)
    eoh = Column("DepthEnd", Float)

    azimuth = Column("Azimuth", Float)
    dip = Column("Dip", Float)


class GeoticCollar(Collar, Base):
    """
    sqlalchemy Base describing collar_base in Geotic database

    Missing in current version :
    - transform x / y / z value to point or get corresponding SRID

    """

    __tablename__ = "tblNuSondage"
    hole_id = Column("Nom", String)
    survey_date = Column("RowDate", DateTime)
    loaded_by = GEOTIC_PERSON_CODE
    srid = 32218
    project_srid = DEFAULT_SRID

    idKey = Column(Integer, primary_key=True)

    # foreign key added for relationship, usage to be confirmed
    idNomProjet = Column(Integer, ForeignKey("tblNomProjet.idKey"))
    # cascade option not fully tested, used for some test of collar_base creation not yet implemented
    coord = relationship("CollarCoordinates", cascade="all, delete, delete-orphan")
    projet = relationship("GeoticDataset", uselist=False)

    @property
    def data_set(self) -> str:
        if self.projet:
            return self.projet.name
        else:
            return GEOTIC_DATASET

    @data_set.setter
    def data_set(self, val: str):
        self._add_dataset_if_needed()
        self.projet.data_set = val

    @property
    def x(self) -> float:
        if self.coord:
            return self.coord[0].x
        else:
            return 0.0

    @x.setter
    def x(self, val: float):
        self._add_coordinates_if_needed()
        self.coord[0].x = val

    @property
    def y(self) -> float:
        if self.coord:
            return self.coord[0].y
        else:
            return 0.0

    @y.setter
    def y(self, val: float):
        self._add_coordinates_if_needed()
        self.coord[0].y = val

    @property
    def z(self) -> float:
        if self.coord:
            return self.coord[0].z
        else:
            return 0.0

    @z.setter
    def z(self, val: float):
        self._add_coordinates_if_needed()
        self.coord[0].z = val

    @property
    def dip(self) -> float:
        if self.coord:
            return self.coord[0].dip
        else:
            return None

    @dip.setter
    def dip(self, val: float):
        self._add_coordinates_if_needed()
        self.coord[0].dip = val

    @property
    def azimuth(self) -> float:
        if self.coord:
            return self.coord[0].azimuth
        else:
            return None

    @azimuth.setter
    def azimuth(self, val: float):
        self._add_coordinates_if_needed()
        self.coord[0].azimuth = val

    @property
    def eoh(self) -> float:
        if self.coord:
            return self.coord[0].eoh
        else:
            return 0.0

    @eoh.setter
    def eoh(self, val: float):
        self._add_coordinates_if_needed()
        self.coord[0].eoh = val

    @property
    def planned_eoh(self) -> float:
        return None

    @property
    def planned_x(self) -> float:
        return self.x

    @property
    def planned_y(self) -> float:
        return self.y

    @property
    def planned_z(self) -> float:
        return self.z

    @eoh.setter
    def eoh(self, val: float):
        self._add_coordinates_if_needed()
        self.coord[0].eoh = val

    def _add_coordinates_if_needed(self):
        """
        Add CollarCoordinates if not available

        """
        if not self.coord:
            coord = CollarCoordinates()
            coord.idNuSondage = self.hole_id
            coord.coord_type = 1
            self.coord.append(coord)

    def _add_dataset_if_needed(self):
        """
        Add GeoticDataset if not available

        """
        if not self.projet:
            self.projet = GeoticDataset(name=self.data_set, loaded_by=self.loaded_by)


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

    __tablename__ = "tblDeviation"

    id_collar = Column("idNuSondage", Integer, ForeignKey("tblNuSondage.idKey"))
    azimuth = Column("Azimut", Float)
    dip = Column("Plongee", Float)
    depth = Column("Profondeur", Float)
    loaded_by = GEOTIC_PERSON_CODE

    invalid_azimuth = Column("InvalideAzimut", Integer)
    invalid_dip = Column("InvalidePlongee", Integer)

    idKey = Column(Integer, primary_key=True)

    collar = relationship("GeoticCollar", uselist=False)

    @property
    def hole_id(self) -> str:
        return self.collar.hole_id

    @hole_id.setter
    def hole_id(self, val: str):
        if self.collar:
            self.collar.hole_id = val

    @property
    def data_set(self) -> str:
        if self.collar:
            return self.collar.data_set
        else:
            return GEOTIC_DATASET

    @data_set.setter
    def data_set(self, val: str):
        if self.collar:
            self.collar.data_set = val
