from sqlalchemy import Column, Integer, Float, ForeignKey
from sqlalchemy.orm import relationship, backref
from geoalchemy2 import Geometry
from app.database import Base
from sqlalchemy import ForeignKeyConstraint


class Assessment(Base):
    """评估数据表ORM模型"""
    __tablename__ = 'Assessment'

    # 主键字段（自增）
    id = Column('ID', Integer, primary_key=True, autoincrement=True, comment='自增主键')

    # 复合外键字段：关联Farmland_data表的联合主键（Farmland_ID和Sample_ID）
    farmland_id = Column('Farmland_ID', Integer, nullable=False, comment='关联Farmland_data表的Farmland_ID')
    sample_id = Column('Sample_ID', Integer, nullable=False, comment='关联Farmland_data表的Sample_ID')

    # 用地类型
    type = Column('Type', Float, nullable=True, comment='用地类型：旱地(0)、水田(1)、水浇地(2)')
    # 2023年三普镉含量
    idw_2023sp_cd = Column('IDW_2023SP_Cd', Float, nullable=True, comment='2023年三普Cd(mg/kg)')
    # 2023年三普pH值
    idw_2023sp_ph = Column('IDW_2023SP_pH', Float, nullable=True, comment='2023年三普pH')
    # 有机质含量
    som_idw = Column('SOM_IDW', Float, nullable=True, comment='有机质(g/kg)')
    # 安全生产风险阈值
    safety_production_threshold = Column('安全生产风险阈值', Float, nullable=True, comment='安全生产风险阈值')
    # 污染风险筛选值
    pollution_risk_screening_value = Column('污染风险筛选值', Float, nullable=True, comment='污染风险筛选值')

    # 设置复合外键约束（关联到Farmland_data表的联合主键）
    __table_args__ = (
        ForeignKeyConstraint(
            ['Farmland_ID', 'Sample_ID'],
            ['Farmland_data.Farmland_ID', 'Farmland_data.Sample_ID']
        ),
        {'comment': '评价数据表'}
    )

    # 注意：一个Assessment记录关联一个Farmland_data记录（通过复合主键）
    farmland_data = relationship("FarmlandData", backref="assessments")

    def __repr__(self):
        return f"<Assessment(id={self.id}, farmland_id={self.farmland_id}, sample_id={self.sample_id})>"