from sqlalchemy.orm import declarative_base, relationship
from sqlalchemy import DateTime, ForeignKey, Float
from sqlalchemy import Column, Integer, String

from xplordb.datamodel.collar import Collar
from xplordb.datamodel.dataset import Dataset
from xplordb.datamodel.survey import Survey

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

    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 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

    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
