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


class SoilData(Base):
    """
    土壤理化性质数据模型

    @param {int} id - 自增主键
    @param {int} farmland_id - 关联的农业用地矢量点编号（外键）
    @param {int} sample_id - 关联的采样自增ID（外键）
    @param {float} clay_0002IDW - 黏粒组分(<0.002mm)占比(%)
    @param {float} bd020_90 - 土壤容重(g/cm³)
    ...（其他字段参考下方详细定义）
    """
    __tablename__ = 'Soil_data'
    __table_args__ = (
        # 定义复合外键约束（关联Farmland_data表的联合主键）
        ForeignKeyConstraint(
            ['Farmland_ID', 'Sample_ID'],  # 当前表的列名
            ['Farmland_data.Farmland_ID', 'Farmland_data.Sample_ID']  # 被引用表的列名
        ),
        {'comment': '土壤理化性质数据表，存储关联的耕地样点土壤指标'}
    )

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

    # 外键（注意字段名与Farmland_data表大小写保持一致）
    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')

    # 土壤理化性质字段（按表格顺序定义）
    clay_0002IDW = Column('0002IDW', Float, nullable=True, comment='粒径<0.002mm的黏粒组分占比(%)')
    bd020_90 = Column('bd020_90', Float, nullable=True, comment='容重(g/cm³)，使用时需除1000')
    por_layer = Column('POR_Layer', Float, nullable=True, comment='土壤孔隙度(cm³/cm³)')
    exal_idw = Column('ExAl_IDW', Float, nullable=True, comment='交换性Al³⁺[cmol(1/3Al³⁺)/kg]')
    exca_idw = Column('ExCa_IDW', Float, nullable=True, comment='交换性Ca²⁺[cmol(1/2Ca²⁺)/kg]')
    exk_idw = Column('ExK_IDW', Float, nullable=True, comment='交换性K⁺[cmol(K⁺)/kg]')
    exmg_idw = Column('ExMg_IDW', Float, nullable=True, comment='交换性Mg²⁺[cmol(1/2Mg²⁺)/kg]')
    exna_idw = Column('ExNa_IDW', Float, nullable=True, comment='交换性Na⁺[cmol(Na⁺)/kg]')
    fed_idw = Column('Fed_IDW', Float, nullable=True, comment='游离铁(g/kg)')
    som_idw = Column('SOM_IDW', Float, nullable=True, comment='有机质(g/kg)')
    idw_2013pc_cd = Column('IDW_2013PC_Cd', Float, nullable=True, comment='2013年普查总镉(mg/kg)')
    idw_2018xc_cd = Column('IDW_2018XC_Cd', Float, nullable=True, comment='2018年详查总镉(mg/kg)')
    idw_2023sp_cd = Column('IDW_2023SP_Cd', Float, nullable=True, comment='2023年三普镉(mg/kg)')
    idw_2013pc_ph = Column('IDW_2013PC_pH', Float, nullable=True, comment='2013年普查pH值')
    idw_2018xc_ph = Column('IDW_2018XC_pH', Float, nullable=True, comment='2018年详查pH值')
    idw_2023sp_ph = Column('IDW_2023SP_pH', Float, nullable=True, comment='2023年三普pH值')
    silt_002_0002IDW = Column('002_0002IDW', Float, nullable=True, comment='粉粒组分(0.002~0.02mm)占比(%)')
    sand_02_002IDW = Column('02_002IDW', Float, nullable=True, comment='砂粒组分(0.02~0.2mm)占比(%)')
    gravel_2_02IDW = Column('2_02IDW', Float, nullable=True, comment='石砾组分(>2mm)占比(%)')
    avak_idw = Column('AvaK_IDW', Float, nullable=True, comment='速效钾(mg/kg)')
    avap_idw = Column('AvaP_IDW', Float, nullable=True, comment='有效磷(mg/kg)')
    cec_idw = Column('CEC_IDW', Float, nullable=True, comment='阳离子交换量[cmol(+)/kg]')
    ec_idw = Column('EC_IDW', Float, nullable=True, comment='电导率(mS/cm)')
    oc_fe_0_30 = Column('OC-Fe_0-30', Float, nullable=True, comment='土壤铁结合态有机碳(g/kg)')
    savak_idw = Column('SAvaK_IDW', Float, nullable=True, comment='缓效钾(mg/kg)')
    tal_idw = Column('TAl_IDW', Float, nullable=True, comment='全铝(%)')
    tca_idw = Column('TCa_IDW', Float, nullable=True, comment='全钙(%)')
    tcd_idw = Column('TCd_IDW', Float, nullable=True, comment='总镉(mg/kg)')
    teb_idw = Column('TEB_IDW', Float, nullable=True, comment='水溶性盐总量(mg/L)')
    texh_idw = Column('TExH_IDW', Float, nullable=True, comment='交换性酸总量[cmol (H⁺ + 1/3Al³⁺)/kg]')
    tfe_idw = Column('TFe_IDW', Float, nullable=True, comment='全铁(%)')
    tk_idw = Column('TK_IDW', Float, nullable=True, comment='全钾(g/kg)')
    tmg_idw = Column('TMg_IDW', Float, nullable=True, comment='全镁(%)')
    tmn_idw = Column('TMn_IDW', Float, nullable=True, comment='全锰(mg/kg)')
    tn_idw = Column('TN_IDW', Float, nullable=True, comment='全氮(g/kg)')
    tp_idw = Column('TP_IDW', Float, nullable=True, comment='全磷(g/kg)')
    ts_idw = Column('TS_IDW', Float, nullable=True, comment='全硫(g/kg)')
    dqcj_cd = Column('DQCJ_Cd', Float, nullable=True, comment='大气沉降输入Cd(g/ha/a)')
    ggs_cd = Column('GGS_Cd', Float, nullable=True, comment='灌溉水输入Cd(g/ha/a)')
    dx_cd = Column('DX_Cd', Float, default=0.023, nullable=True, comment='地下渗漏Cd(g/ha/a)')
    db_cd = Column('DB_Cd', Float, default=0.368, nullable=True, comment='地表径流Cd(g/ha/a)')

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