from sqlalchemy import (
    BigInteger,
    Column,
    Date,
    ForeignKey,
    Integer,
    String,
    Enum as SqlEnum,
)
from sqlalchemy.orm import relationship

from backend.condition.domain.aspect_type import AspectType
from backend.condition.domain.element_type import ElementType

from backend.app.db.base import Base

ElementTypeDb = SqlEnum(
    ElementType,
    name="element_type",
    native_enum=True,
    values_callable=lambda enum: [e.value for e in enum],
)

AspectTypeDb = SqlEnum(
    AspectType,
    name="aspect_type",
    native_enum=True,
    values_callable=lambda enum: [a.value for a in enum],
)


class PropertyConditionSurveyModel(Base):
    __tablename__ = "property_condition_survey"

    id = Column(BigInteger, primary_key=True, autoincrement=True)
    uprn = Column(BigInteger, nullable=False)

    date = Column(Date, nullable=False)
    source = Column(String, nullable=False)

    elements = relationship(
        "ElementModel",
        back_populates="survey",
        cascade="all, delete-orphan",
    )


class ElementModel(Base):
    __tablename__ = "element"  # TODO: rename to survey_element?

    id = Column(BigInteger, primary_key=True, autoincrement=True)

    survey_id = Column(
        BigInteger,
        ForeignKey("property_condition_survey.id"),
        nullable=False,
    )

    element_type = Column(ElementTypeDb, nullable=False)
    element_instance = Column(BigInteger, nullable=False)

    survey = relationship(
        "PropertyConditionSurveyModel",
        back_populates="elements",
    )

    aspect_conditions = relationship(
        "AspectConditionModel",
        back_populates="element",
        cascade="all, delete-orphan",
    )


class AspectConditionModel(Base):
    __tablename__ = "aspect_condition"  # TODO: rename to survey_aspect?

    id = Column(BigInteger, primary_key=True, autoincrement=True)

    element_id = Column(
        BigInteger,
        ForeignKey("element.id"),
        nullable=False,
    )

    aspect_type = Column(AspectTypeDb, nullable=False)
    aspect_instance = Column(BigInteger, nullable=False)

    value = Column(String)
    quantity = Column(Integer)
    install_date = Column(Date)
    renewal_year = Column(Integer)
    comments = Column(String)

    element = relationship(
        "ElementModel",
        back_populates="aspect_conditions",
    )
