shanxia2.0/backend/app/models/pl_models.py

"""运输计划管理模块数据模型"""
from datetime import datetime
from decimal import Decimal

from sqlalchemy import (
    BigInteger,
    Column,
    DECIMAL,
    Date,
    DateTime,
    Integer,
    SmallInteger,
    String,
    Text,
    CHAR,
    ForeignKey,
)
from sqlalchemy.orm import relationship

from app.core.database import Base


# =============================================================================
# 1. 基础数据：承运商、车辆、司机
# =============================================================================


class PlCarrier(Base):
    """承运商表 pl_carrier"""

    __tablename__ = "pl_carrier"

    id = Column(String(64), primary_key=True, comment="主键ID")
    carrier_name = Column(String(200), nullable=False, comment="承运商名称")
    contact_name = Column(String(100), nullable=True, comment="联系人")
    contact_phone = Column(String(32), nullable=True, comment="联系电话")
    status = Column(CHAR(1), nullable=False, default="1", comment="合作状态 1-合作中 0-已终止")
    tenant_no = Column(String(64), nullable=False, default="shanxia2.0", comment="租户编号")
    order_num = Column(Integer, nullable=True, comment="排序号")
    remark = Column(String(512), nullable=True, comment="备注")
    create_by = Column(String(64), nullable=True, comment="创建人id")
    create_dept_id = Column(String(64), nullable=False, default="", comment="创建人部门ID")
    create_time = Column(DateTime, nullable=False, default=datetime.now, comment="创建时间")
    update_by = Column(String(64), nullable=True, comment="更新人id")
    update_time = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now, comment="更新时间")
    del_flag = Column(SmallInteger, nullable=False, default=0, comment="删除标记：0-正常 1-已删除")


class PlVehicle(Base):
    """车辆表 pl_vehicle"""

    __tablename__ = "pl_vehicle"

    id = Column(String(64), primary_key=True, comment="主键ID")
    vehicle_type = Column(String(20), nullable=False, comment="车辆类型 self-自有 carrier-承运商")
    carrier_id = Column(String(64), nullable=True, comment="承运商ID（类型为承运商时必填）")
    carrier_name = Column(String(200), nullable=True, comment="承运商名称（冗余）")
    license_plate = Column(String(20), nullable=False, unique=True, comment="车牌号")
    trailer_no = Column(String(20), nullable=True, comment="挂车号")
    default_driver_id = Column(String(64), nullable=True, comment="默认司机ID")
    default_driver_name = Column(String(100), nullable=True, comment="默认司机姓名（冗余）")
    load_capacity = Column(DECIMAL(10, 2), nullable=True, comment="载重（吨）")
    vehicle_status = Column(String(20), nullable=False, default="available", comment="车辆状态 available-可用 maintaining-维修中 scrapped-报废")
    tenant_no = Column(String(64), nullable=False, default="shanxia2.0", comment="租户编号")
    order_num = Column(Integer, nullable=True, comment="排序号")
    remark = Column(String(512), nullable=True, comment="备注")
    create_by = Column(String(64), nullable=True, comment="创建人id")
    create_dept_id = Column(String(64), nullable=False, default="", comment="创建人部门ID")
    create_time = Column(DateTime, nullable=False, default=datetime.now, comment="创建时间")
    update_by = Column(String(64), nullable=True, comment="更新人id")
    update_time = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now, comment="更新时间")
    del_flag = Column(SmallInteger, nullable=False, default=0, comment="删除标记：0-正常 1-已删除")


class PlPersonnel(Base):
    """人员表 pl_personnel"""

    __tablename__ = "pl_personnel"

    id = Column(String(64), primary_key=True, comment="主键ID")
    personnel_name = Column(String(100), nullable=False, comment="姓名")
    phone_no = Column(String(32), nullable=True, comment="手机号（非必填）")
    age = Column(Integer, nullable=True, comment="年龄")
    id_card_no = Column(String(32), nullable=True, comment="身份证号")
    status = Column(CHAR(1), nullable=False, default="1", comment="状态 1-在职 0-停用")
    tenant_no = Column(String(64), nullable=False, default="shanxia2.0", comment="租户编号")
    order_num = Column(Integer, nullable=True, comment="排序号")
    remark = Column(String(512), nullable=True, comment="备注")
    create_by = Column(String(64), nullable=True, comment="创建人id")
    create_dept_id = Column(String(64), nullable=False, default="", comment="创建人部门ID")
    create_time = Column(DateTime, nullable=False, default=datetime.now, comment="创建时间")
    update_by = Column(String(64), nullable=True, comment="更新人id")
    update_time = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now, comment="更新时间")
    del_flag = Column(SmallInteger, nullable=False, default=0, comment="删除标记：0-正常 1-已删除")


class PlCert(Base):
    """证书表 pl_cert"""

    __tablename__ = "pl_cert"

    id = Column(String(64), primary_key=True, comment="主键ID")
    owner_type = Column(String(20), nullable=False, comment="归属类型 vehicle-车辆 personnel-人员")
    owner_id = Column(String(64), nullable=False, comment="归属主体ID")
    cert_name = Column(String(100), nullable=False, comment="证书名称")
    cert_no = Column(String(64), nullable=True, comment="证号")
    expire_date = Column(Date, nullable=True, comment="有效期")
    remind_days = Column(Integer, nullable=False, default=30, comment="提前提醒天数")
    attachment_urls = Column(String(2000), nullable=True, comment="附件URL（逗号分隔）")
    status = Column(String(20), nullable=False, default="valid", comment="状态 valid-有效 expired-已过期 revoked-已作废")
    tenant_no = Column(String(64), nullable=False, default="shanxia2.0", comment="租户编号")
    order_num = Column(Integer, nullable=True, comment="排序号")
    remark = Column(String(512), nullable=True, comment="备注")
    create_by = Column(String(64), nullable=True, comment="创建人id")
    create_dept_id = Column(String(64), nullable=False, default="", comment="创建人部门ID")
    create_time = Column(DateTime, nullable=False, default=datetime.now, comment="创建时间")
    update_by = Column(String(64), nullable=True, comment="更新人id")
    update_time = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now, comment="更新时间")
    del_flag = Column(SmallInteger, nullable=False, default=0, comment="删除标记：0-正常 1-已删除")


class PlDriver(Base):
    """司机表 pl_driver（兼容旧表，迁移后逐步废弃）"""

    __tablename__ = "pl_driver"

    id = Column(String(64), primary_key=True, comment="主键ID")
    driver_name = Column(String(100), nullable=False, comment="姓名")
    phone_no = Column(String(32), nullable=False, comment="手机号")
    id_card_no = Column(String(32), nullable=True, comment="身份证号")
    driving_license_no = Column(String(32), nullable=True, comment="驾驶证号")
    driving_license_expire = Column(Date, nullable=True, comment="驾驶证有效期")
    vehicle_license_no = Column(String(32), nullable=True, comment="行驶证号")
    vehicle_license_expire = Column(Date, nullable=True, comment="行驶证有效期")
    hazmat_cert_no = Column(String(32), nullable=True, comment="危化品证号")
    hazmat_cert_expire = Column(Date, nullable=True, comment="危化品证有效期")
    tenant_no = Column(String(64), nullable=False, default="shanxia2.0", comment="租户编号")
    order_num = Column(Integer, nullable=True, comment="排序号")
    remark = Column(String(512), nullable=True, comment="备注")
    create_by = Column(String(64), nullable=True, comment="创建人id")
    create_dept_id = Column(String(64), nullable=False, default="", comment="创建人部门ID")
    create_time = Column(DateTime, nullable=False, default=datetime.now, comment="创建时间")
    update_by = Column(String(64), nullable=True, comment="更新人id")
    update_time = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now, comment="更新时间")
    del_flag = Column(SmallInteger, nullable=False, default=0, comment="删除标记：0-正常 1-已删除")


class PlVehiclePersonnel(Base):
    """车辆-人员关联表 pl_vehicle_personnel"""

    __tablename__ = "pl_vehicle_personnel"

    id = Column(String(64), primary_key=True, comment="主键ID")
    vehicle_id = Column(String(64), nullable=False, comment="车辆ID")
    personnel_id = Column(String(64), nullable=False, comment="人员ID")
    role = Column(String(20), nullable=False, default="driver", comment="角色 driver-司机 escort-押运员")
    is_default = Column(SmallInteger, nullable=False, default=0, comment="是否默认司机 0-否 1-是")
    tenant_no = Column(String(64), nullable=False, default="shanxia2.0", comment="租户编号")
    order_num = Column(Integer, nullable=True, comment="排序号")
    remark = Column(String(512), nullable=True, comment="关联备注")
    create_by = Column(String(64), nullable=True, comment="创建人id")
    create_dept_id = Column(String(64), nullable=False, default="", comment="创建人部门ID")
    create_time = Column(DateTime, nullable=False, default=datetime.now, comment="创建时间")
    update_by = Column(String(64), nullable=True, comment="更新人id")
    update_time = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now, comment="更新时间")
    del_flag = Column(SmallInteger, nullable=False, default=0, comment="删除标记：0-正常 1-已删除")


class PlVehicleDriver(Base):
    """车辆-司机关联表 pl_vehicle_driver（兼容旧表）"""

    __tablename__ = "pl_vehicle_driver"

    id = Column(String(64), primary_key=True, comment="主键ID")
    vehicle_id = Column(String(64), nullable=False, comment="车辆ID")
    driver_id = Column(String(64), nullable=False, comment="司机ID")
    is_default = Column(SmallInteger, nullable=False, default=0, comment="是否默认 0-否 1-是")
    tenant_no = Column(String(64), nullable=False, default="shanxia2.0", comment="租户编号")
    order_num = Column(Integer, nullable=True, comment="排序号")
    remark = Column(String(512), nullable=True, comment="备注")
    create_by = Column(String(64), nullable=True, comment="创建人id")
    create_dept_id = Column(String(64), nullable=False, default="", comment="创建人部门ID")
    create_time = Column(DateTime, nullable=False, default=datetime.now, comment="创建时间")
    update_by = Column(String(64), nullable=True, comment="更新人id")
    update_time = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now, comment="更新时间")
    del_flag = Column(SmallInteger, nullable=False, default=0, comment="删除标记：0-正常 1-已删除")


# =============================================================================
# 2. 计划管理：计划主表、产品明细、变更记录
# =============================================================================


class PlPlan(Base):
    """计划主表 pl_plan"""

    __tablename__ = "pl_plan"

    id = Column(String(64), primary_key=True, comment="主键ID")

    # 编号信息
    plan_no = Column(String(32), nullable=False, unique=True, comment="计划订单编号 SXDDHYYYYMMDD######")
    plan_date = Column(Date, nullable=False, comment="计划日期")

    # 基本信息
    belongs_company = Column(String(20), nullable=False, comment="归属公司 shanxia-安庆山峡 yundong-安徽云栋")
    plan_type = Column(String(20), nullable=False, comment="单据类型 direct-直销单 inbound-入库单 outbound-出库单")
    follow_status = Column(String(30), nullable=False, default="draft", comment="跟进状态")
    transport_status = Column(String(20), nullable=False, default="pending", comment="运输安排状态 pending-待安排 arranged-已安排")
    archive_status = Column(String(20), nullable=False, default="unarchived", comment="归档状态 unarchived-未归档 archived-已归档")
    complete_status = Column(String(10), nullable=False, default="no", comment="完成状态 yes-是 no-否")

    # 合同关联
    contract_id = Column(String(64), nullable=True, comment="CRM合同ID")
    contract_no = Column(String(50), nullable=True, comment="合同编号")

    # 供应商与目的地
    supplier_id = Column(String(64), nullable=True, comment="供应商ID")
    supplier_name = Column(String(200), nullable=True, comment="供应商名称")
    destination_id = Column(String(64), nullable=True, comment="目的地ID")
    destination_name = Column(String(200), nullable=True, comment="目的地名称")

    # 客户信息（冗余自合同）
    customer_id = Column(String(64), nullable=True, comment="客户ID")
    customer_name = Column(String(200), nullable=True, comment="客户名称")
    contact_name = Column(String(100), nullable=True, comment="联系人")
    contact_phone = Column(String(32), nullable=True, comment="联系电话")
    party_a = Column(String(200), nullable=True, comment="甲方")
    party_b = Column(String(200), nullable=True, comment="乙方")

    # 合同有效期
    contract_start_date = Column(Date, nullable=True, comment="合同开始日期")
    contract_end_date = Column(Date, nullable=True, comment="合同结束日期")

    # 送货与结算
    delivery_method = Column(String(20), nullable=True, comment="送货方式 self_pick-山峡自提 one_ticket-一票制 customer_pick-客户自提")
    settlement_method = Column(String(50), nullable=True, comment="结算方式")

    # 运输信息
    has_freight = Column(SmallInteger, nullable=False, default=0, comment="是否有运输费用 0-否 1-是")
    pickup_address = Column(String(500), nullable=True, comment="取货地址")
    pickup_time = Column(DateTime, nullable=True, comment="取货时间")
    pickup_notes = Column(String(500), nullable=True, comment="取货注意事项")
    destination_address = Column(String(500), nullable=True, comment="目的地地址")
    delivery_time = Column(DateTime, nullable=True, comment="送达时间")
    destination_notes = Column(String(500), nullable=True, comment="目的地注意事项")
    transport_id = Column(String(64), nullable=True, comment="运输ID")
    transport_no = Column(String(32), nullable=True, comment="运输编号")
    vehicle_id = Column(String(64), nullable=True, comment="车辆ID")
    license_plate = Column(String(20), nullable=True, comment="车牌号")
    driver_id = Column(String(64), nullable=True, comment="司机ID")
    driver_name = Column(String(100), nullable=True, comment="司机姓名")

    # 金额汇总
    total_amount = Column(DECIMAL(15, 2), nullable=False, default=0, comment="计划总金额")
    freight_amount = Column(DECIMAL(15, 2), nullable=False, default=0, comment="运费合计")

    # 通知信息
    notify_message = Column(Text, nullable=True, comment="通知信息（标准模板）")

    # 跟进人
    follow_user_id = Column(String(64), nullable=True, comment="跟进人ID")
    follow_user_name = Column(String(100), nullable=True, comment="跟进人姓名")

    # 公共字段
    tenant_no = Column(String(64), nullable=False, default="shanxia2.0", comment="租户编号")
    order_num = Column(Integer, nullable=True, comment="排序号")
    remark = Column(String(512), nullable=True, comment="备注")
    create_by = Column(String(64), nullable=True, comment="创建人id")
    create_dept_id = Column(String(64), nullable=False, default="", comment="创建人部门ID")
    create_time = Column(DateTime, nullable=False, default=datetime.now, comment="创建时间")
    update_by = Column(String(64), nullable=True, comment="更新人id")
    update_time = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now, comment="更新时间")
    del_flag = Column(SmallInteger, nullable=False, default=0, comment="删除标记：0-正常 1-已删除")


class PlPlanProduct(Base):
    """计划产品明细表 pl_plan_product"""

    __tablename__ = "pl_plan_product"

    id = Column(String(64), primary_key=True, comment="主键ID")
    plan_id = Column(String(64), nullable=False, comment="计划ID")

    # 产品信息
    product_id = Column(String(64), nullable=False, comment="产品ID")
    product_no = Column(String(50), nullable=False, comment="产品编号")
    product_name = Column(String(200), nullable=False, comment="产品名称")
    model = Column(String(100), nullable=True, comment="规格型号")
    unit = Column(String(20), nullable=False, default="吨", comment="单位")
    category_name = Column(String(200), nullable=True, comment="产品分类")
    package_spec = Column(String(200), nullable=True, comment="包装规格")
    delivery_method = Column(String(50), nullable=True, comment="送货方式")
    quantity = Column(DECIMAL(15, 3), nullable=True, comment="合同数量")
    actual_qty = Column(DECIMAL(15, 3), nullable=True, comment="实际数量")
    tax_price = Column(DECIMAL(15, 4), nullable=True, comment="含税单价")
    tax_amount = Column(DECIMAL(15, 2), nullable=True, comment="税费")
    tax_subtotal = Column(DECIMAL(15, 2), nullable=True, comment="含税总价")

    # 产品属性（冗余自产品主数据）
    is_explosive = Column(SmallInteger, nullable=False, default=0, comment="是否易制爆 0-否 1-是")
    is_toxic = Column(SmallInteger, nullable=False, default=0, comment="是否易制毒 0-否 1-是")

    # 购买证/运输证
    purchase_cert_url = Column(String(500), nullable=True, comment="购买证URL")
    transport_cert_url = Column(String(500), nullable=True, comment="运输证URL")

    # 入库信息
    inbound_estimate_qty = Column(DECIMAL(15, 3), nullable=True, comment="入库预估数")
    inbound_qty = Column(DECIMAL(15, 3), nullable=True, comment="入库数")
    inbound_price = Column(DECIMAL(15, 4), nullable=True, comment="入库价")
    inbound_amount = Column(DECIMAL(15, 2), nullable=True, comment="入库金额")

    # 出库信息
    outbound_estimate_qty = Column(DECIMAL(15, 3), nullable=True, comment="出库预估数")
    outbound_qty = Column(DECIMAL(15, 3), nullable=True, comment="出库数")
    outbound_price = Column(DECIMAL(15, 4), nullable=True, comment="出库单价")
    outbound_amount = Column(DECIMAL(15, 2), nullable=True, comment="出库金额")

    # 公共字段
    tenant_no = Column(String(64), nullable=False, default="shanxia2.0", comment="租户编号")
    order_num = Column(Integer, nullable=True, comment="排序号")
    remark = Column(String(512), nullable=True, comment="备注")
    create_by = Column(String(64), nullable=True, comment="创建人id")
    create_dept_id = Column(String(64), nullable=False, default="", comment="创建人部门ID")
    create_time = Column(DateTime, nullable=False, default=datetime.now, comment="创建时间")
    update_by = Column(String(64), nullable=True, comment="更新人id")
    update_time = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now, comment="更新时间")
    del_flag = Column(SmallInteger, nullable=False, default=0, comment="删除标记：0-正常 1-已删除")


class PlPlanChange(Base):
    """计划变更记录表 pl_plan_change"""

    __tablename__ = "pl_plan_change"

    id = Column(String(64), primary_key=True, comment="主键ID")
    plan_id = Column(String(64), nullable=False, comment="计划ID")
    change_type = Column(String(30), nullable=False, comment="变更类型")
    change_content = Column(Text, nullable=True, comment="变更内容")
    change_user_id = Column(String(64), nullable=True, comment="变更人ID")
    change_user_name = Column(String(100), nullable=True, comment="变更人姓名")
    tenant_no = Column(String(64), nullable=False, default="shanxia2.0", comment="租户编号")
    order_num = Column(Integer, nullable=True, comment="排序号")
    remark = Column(String(512), nullable=True, comment="备注")
    create_by = Column(String(64), nullable=True, comment="创建人id")
    create_dept_id = Column(String(64), nullable=False, default="", comment="创建人部门ID")
    create_time = Column(DateTime, nullable=False, default=datetime.now, comment="创建时间")
    update_by = Column(String(64), nullable=True, comment="更新人id")
    update_time = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now, comment="更新时间")
    del_flag = Column(SmallInteger, nullable=False, default=0, comment="删除标记：0-正常 1-已删除")


# =============================================================================
# 3. 运输管理：运输表、审批记录
# =============================================================================


class PlTransport(Base):
    """运输表 pl_transport"""

    __tablename__ = "pl_transport"

    id = Column(String(64), primary_key=True, comment="主键ID")
    transport_no = Column(String(32), nullable=False, unique=True, comment="运输编号 SXYS######")

    # 车辆与司机
    vehicle_id = Column(String(64), nullable=True, comment="车辆ID")
    license_plate = Column(String(20), nullable=True, comment="车牌号")
    driver_id = Column(String(64), nullable=True, comment="司机ID")
    driver_name = Column(String(100), nullable=True, comment="司机姓名")
    driver_phone = Column(String(32), nullable=True, comment="司机电话")

    # 承运商
    carrier_id = Column(String(64), nullable=True, comment="承运商ID")
    carrier_name = Column(String(200), nullable=True, comment="承运商名称")

    # 运输信息
    transport_medium = Column(String(100), nullable=True, comment="运输介质")
    start_point = Column(String(200), nullable=True, comment="运输起点")
    end_point = Column(String(200), nullable=True, comment="运输终点")
    distance_km = Column(DECIMAL(10, 2), nullable=True, comment="运输公里数")
    unit = Column(String(20), nullable=True, comment="计量单位 吨/趟")
    transport_qty = Column(DECIMAL(15, 3), nullable=True, comment="运输量（按重量计费基数）")

    # 运费
    freight_price = Column(DECIMAL(15, 4), nullable=True, comment="运输单价")
    freight_amount = Column(DECIMAL(15, 2), nullable=False, default=0, comment="运输费用")
    freight_type = Column(String(20), nullable=False, default="by_weight", comment="运费计算方式 by_weight-按重量 by_trip-按趟次")
    has_invoice = Column(SmallInteger, nullable=False, default=0, comment="是否开票 0-否 1-是")

    # 二维码
    qr_code = Column(String(500), nullable=True, comment="二维码内容")
    qr_expire_date = Column(Date, nullable=True, comment="二维码有效期")
    driver_access_token = Column(String(64), nullable=True, comment="司机免登录访问令牌")

    # 运输状态
    transport_status = Column(String(20), nullable=False, default="draft", comment="运输状态 draft-草稿 pending-待执行 executing-执行中 completed-已完成 cancelled-已取消")
    approval_status = Column(String(20), nullable=False, default="none", comment="审批状态 none-无需 pending-审批中 approved-已通过 rejected-已拒绝")

    # 司机回传信息
    feedback_time = Column(DateTime, nullable=True, comment="回传时间")
    feedback_qty = Column(DECIMAL(15, 3), nullable=True, comment="回传数量")
    feedback_images = Column(Text, nullable=True, comment="回传图片URLs（JSON数组）")
    feedback_remark = Column(String(500), nullable=True, comment="回传备注")

    # 公共字段
    tenant_no = Column(String(64), nullable=False, default="shanxia2.0", comment="租户编号")
    order_num = Column(Integer, nullable=True, comment="排序号")
    remark = Column(String(512), nullable=True, comment="备注")
    create_by = Column(String(64), nullable=True, comment="创建人id")
    create_dept_id = Column(String(64), nullable=False, default="", comment="创建人部门ID")
    create_time = Column(DateTime, nullable=False, default=datetime.now, comment="创建时间")
    update_by = Column(String(64), nullable=True, comment="更新人id")
    update_time = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now, comment="更新时间")
    del_flag = Column(SmallInteger, nullable=False, default=0, comment="删除标记：0-正常 1-已删除")


class PlTransportDbLink(Base):
    """运输-地磅出入库关联 pl_transport_db_link"""

    __tablename__ = "pl_transport_db_link"

    id = Column(String(64), primary_key=True, comment="主键ID")
    transport_id = Column(String(64), nullable=False, comment="运输单ID")
    plan_id = Column(String(64), nullable=False, comment="运输计划ID")
    plan_product_id = Column(String(64), nullable=False, comment="计划产品行ID")
    db_purchase_plan_id = Column(String(64), nullable=False, comment="地磅计划ID")
    db_inventory_id = Column(String(64), nullable=False, comment="出入库单ID")
    tenant_no = Column(String(64), nullable=False, default="shanxia2.0", comment="租户编号")
    order_num = Column(Integer, nullable=True, comment="排序号")
    remark = Column(String(512), nullable=True, comment="备注")
    create_by = Column(String(64), nullable=True, comment="创建人id")
    create_dept_id = Column(String(64), nullable=False, default="", comment="创建人部门ID")
    create_time = Column(DateTime, nullable=False, default=datetime.now, comment="创建时间")
    update_by = Column(String(64), nullable=True, comment="更新人id")
    update_time = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now, comment="更新时间")
    del_flag = Column(SmallInteger, nullable=False, default=0, comment="删除标记：0-正常 1-已删除")


class PlPlanTransport(Base):
    """计划-运输关联表 pl_plan_transport"""

    __tablename__ = "pl_plan_transport"

    id = Column(String(64), primary_key=True, comment="主键ID")
    plan_id = Column(String(64), nullable=False, comment="计划ID")
    transport_id = Column(String(64), nullable=False, comment="运输ID")
    tenant_no = Column(String(64), nullable=False, default="shanxia2.0", comment="租户编号")
    order_num = Column(Integer, nullable=True, comment="排序号")
    remark = Column(String(512), nullable=True, comment="备注")
    create_by = Column(String(64), nullable=True, comment="创建人id")
    create_dept_id = Column(String(64), nullable=False, default="", comment="创建人部门ID")
    create_time = Column(DateTime, nullable=False, default=datetime.now, comment="创建时间")
    update_by = Column(String(64), nullable=True, comment="更新人id")
    update_time = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now, comment="更新时间")
    del_flag = Column(SmallInteger, nullable=False, default=0, comment="删除标记：0-正常 1-已删除")


class PlTransportSiteLink(Base):
    """运输计划现场链接 pl_transport_site_link（与地磅出入库单解耦）"""

    __tablename__ = "pl_transport_site_link"

    id = Column(String(64), primary_key=True, comment="主键ID")
    transport_id = Column(String(64), nullable=False, comment="运输单ID")
    plan_id = Column(String(64), nullable=False, comment="运输计划ID")
    plan_product_id = Column(String(64), nullable=False, comment="计划产品行ID")
    site_access_token = Column(String(64), nullable=False, comment="现场人员免登录访问令牌")
    business_type = Column(String(20), nullable=False, comment="业务类型：入库/出库")
    partner_company = Column(String(200), nullable=True, comment="往来单位")
    product_id = Column(String(64), nullable=True, comment="CRM产品ID")
    product = Column(String(200), nullable=True, comment="产品名称快照")
    specification = Column(String(200), nullable=True, comment="规格快照")
    quantity = Column(DECIMAL(15, 3), nullable=True, comment="数量（吨）")
    vehicle_no = Column(String(32), nullable=True, comment="车牌号")
    plan_no = Column(String(64), nullable=True, comment="运输计划号")
    plan_date = Column(Date, nullable=True, comment="计划日期")
    site_feedback_remark = Column(String(500), nullable=True, comment="现场回执备注")
    site_feedback_images = Column(Text, nullable=True, comment="现场回执附件URLs（JSON数组）")
    site_feedback_time = Column(DateTime, nullable=True, comment="现场回执保存时间")
    tenant_no = Column(String(64), nullable=False, default="shanxia2.0", comment="租户编号")
    order_num = Column(Integer, nullable=True, comment="排序号")
    remark = Column(String(512), nullable=True, comment="备注")
    create_by = Column(String(64), nullable=True, comment="创建人id")
    create_dept_id = Column(String(64), nullable=False, default="", comment="创建人部门ID")
    create_time = Column(DateTime, nullable=False, default=datetime.now, comment="创建时间")
    update_by = Column(String(64), nullable=True, comment="更新人id")
    update_time = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now, comment="更新时间")
    del_flag = Column(SmallInteger, nullable=False, default=0, comment="删除标记：0-正常 1-已删除")


class PlTransportApproval(Base):
    """运输审批记录表 pl_transport_approval"""

    __tablename__ = "pl_transport_approval"

    id = Column(String(64), primary_key=True, comment="主键ID")
    transport_id = Column(String(64), nullable=False, comment="运输ID")
    approver_id = Column(String(64), nullable=False, comment="审批人ID")
    approver_name = Column(String(100), nullable=False, comment="审批人姓名")
    approval_order = Column(Integer, nullable=False, comment="审批顺序")
    status = Column(String(20), nullable=False, default="pending", comment="审批状态 pending-待审批 approved-已通过 rejected-已拒绝")
    comment = Column(String(500), nullable=True, comment="审批意见")
    approval_time = Column(DateTime, nullable=True, comment="审批时间")
    tenant_no = Column(String(64), nullable=False, default="shanxia2.0", comment="租户编号")
    order_num = Column(Integer, nullable=True, comment="排序号")
    remark = Column(String(512), nullable=True, comment="备注")
    create_by = Column(String(64), nullable=True, comment="创建人id")
    create_dept_id = Column(String(64), nullable=False, default="", comment="创建人部门ID")
    create_time = Column(DateTime, nullable=False, default=datetime.now, comment="创建时间")
    update_by = Column(String(64), nullable=True, comment="更新人id")
    update_time = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now, comment="更新时间")
    del_flag = Column(SmallInteger, nullable=False, default=0, comment="删除标记：0-正常 1-已删除")


# =============================================================================
# 4. 运费结算
# =============================================================================


class PlFreightSettlement(Base):
    """运费结算表 pl_freight_settlement"""

    __tablename__ = "pl_freight_settlement"

    id = Column(String(64), primary_key=True, comment="主键ID")
    settlement_no = Column(String(32), nullable=False, unique=True, comment="结算单编号")

    # 结算金额
    total_freight = Column(DECIMAL(15, 2), nullable=False, default=0, comment="运费合计")
    invoice_amount = Column(DECIMAL(15, 2), nullable=True, comment="发票金额")

    # 发票信息
    invoice_no = Column(String(50), nullable=True, comment="发票号")
    invoice_date = Column(Date, nullable=True, comment="开票日期")
    invoice_attachment_urls = Column(Text, nullable=True, comment="发票附件URLs，逗号分隔")

    # 结算状态
    settlement_status = Column(String(20), nullable=False, default="pending", comment="结算状态 pending-待提交 submitted-已提交 approved-审核通过 confirmed-已确认")

    # 公共字段
    tenant_no = Column(String(64), nullable=False, default="shanxia2.0", comment="租户编号")
    order_num = Column(Integer, nullable=True, comment="排序号")
    remark = Column(String(512), nullable=True, comment="备注")
    create_by = Column(String(64), nullable=True, comment="创建人id")
    create_dept_id = Column(String(64), nullable=False, default="", comment="创建人部门ID")
    create_time = Column(DateTime, nullable=False, default=datetime.now, comment="创建时间")
    update_by = Column(String(64), nullable=True, comment="更新人id")
    update_time = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now, comment="更新时间")
    del_flag = Column(SmallInteger, nullable=False, default=0, comment="删除标记：0-正常 1-已删除")


class PlFreightSettlementApproval(Base):
    """运费结算审批 pl_freight_settlement_approval"""

    __tablename__ = "pl_freight_settlement_approval"

    id = Column(String(64), primary_key=True, comment="主键ID")
    settlement_id = Column(String(64), nullable=False, comment="结算单ID")
    step = Column(Integer, nullable=False, default=1, comment="步骤序号")
    step_name = Column(String(50), nullable=True, comment="步骤名称")
    approver_id = Column(String(64), nullable=False, comment="审批人ID")
    approver_name = Column(String(64), nullable=True, comment="审批人姓名")
    approval_type = Column(String(20), nullable=False, default="sequential", comment="审批类型")
    approval_result = Column(String(20), nullable=False, default="pending", comment="审批结果")
    approval_opinion = Column(String(500), nullable=True, comment="审批意见")
    approval_time = Column(DateTime, nullable=True, comment="审批时间")
    is_current = Column(SmallInteger, nullable=False, default=0, comment="是否当前节点")
    tenant_no = Column(String(64), nullable=False, default="shanxia2.0", comment="租户编号")
    order_num = Column(Integer, nullable=True, comment="排序号")
    remark = Column(String(512), nullable=True, comment="备注")
    create_by = Column(String(64), nullable=True, comment="创建人id")
    create_dept_id = Column(String(64), nullable=False, default="", comment="创建人部门ID")
    create_time = Column(DateTime, nullable=False, default=datetime.now, comment="创建时间")
    update_by = Column(String(64), nullable=True, comment="更新人id")
    update_time = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now, comment="更新时间")
    del_flag = Column(SmallInteger, nullable=False, default=0, comment="删除标记：0-正常 1-已删除")


class PlFreightSettlementTransport(Base):
    """结算单-运输关联表 pl_freight_settlement_transport"""

    __tablename__ = "pl_freight_settlement_transport"

    id = Column(String(64), primary_key=True, comment="主键ID")
    settlement_id = Column(String(64), nullable=False, comment="结算单ID")
    transport_id = Column(String(64), nullable=False, comment="运输ID")
    freight_amount = Column(DECIMAL(15, 2), nullable=False, comment="运费金额")
    tenant_no = Column(String(64), nullable=False, default="shanxia2.0", comment="租户编号")
    order_num = Column(Integer, nullable=True, comment="排序号")
    remark = Column(String(512), nullable=True, comment="备注")
    create_by = Column(String(64), nullable=True, comment="创建人id")
    create_dept_id = Column(String(64), nullable=False, default="", comment="创建人部门ID")
    create_time = Column(DateTime, nullable=False, default=datetime.now, comment="创建时间")
    update_by = Column(String(64), nullable=True, comment="更新人id")
    update_time = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now, comment="更新时间")
    del_flag = Column(SmallInteger, nullable=False, default=0, comment="删除标记：0-正常 1-已删除")


class PlRemittanceVoucher(Base):
    """汇款凭证表 pl_remittance_voucher"""

    __tablename__ = "pl_remittance_voucher"

    id = Column(String(64), primary_key=True, comment="主键ID")
    settlement_id = Column(String(64), nullable=False, comment="结算单ID")
    voucher_no = Column(String(32), nullable=True, comment="凭证编号")
    remittance_date = Column(Date, nullable=True, comment="汇款日期")
    remittance_amount = Column(DECIMAL(15, 2), nullable=True, comment="汇款金额")
    voucher_urls = Column(Text, nullable=True, comment="凭证文件URLs（JSON数组）")
    tenant_no = Column(String(64), nullable=False, default="shanxia2.0", comment="租户编号")
    order_num = Column(Integer, nullable=True, comment="排序号")
    remark = Column(String(512), nullable=True, comment="备注")
    create_by = Column(String(64), nullable=True, comment="创建人id")
    create_dept_id = Column(String(64), nullable=False, default="", comment="创建人部门ID")
    create_time = Column(DateTime, nullable=False, default=datetime.now, comment="创建时间")
    update_by = Column(String(64), nullable=True, comment="更新人id")
    update_time = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now, comment="更新时间")
    del_flag = Column(SmallInteger, nullable=False, default=0, comment="删除标记：0-正常 1-已删除")


# =============================================================================
# 5. 库存管理
# =============================================================================


class PlInventory(Base):
    """库存表 pl_inventory"""

    __tablename__ = "pl_inventory"

    id = Column(String(64), primary_key=True, comment="主键ID")
    product_id = Column(String(64), nullable=False, comment="产品ID")
    product_no = Column(String(50), nullable=False, comment="产品编号")
    product_name = Column(String(200), nullable=False, comment="产品名称")
    model = Column(String(100), nullable=True, comment="规格型号")
    unit = Column(String(20), nullable=False, default="吨", comment="单位")

    # 库存数量与金额
    stock_qty = Column(DECIMAL(15, 3), nullable=False, default=0, comment="库存数量")
    stock_amount = Column(DECIMAL(15, 2), nullable=False, default=0, comment="库存金额")

    # 预警阈值
    low_stock_warning = Column(DECIMAL(15, 3), nullable=True, comment="最低库存预警值")
    high_stock_warning = Column(DECIMAL(15, 3), nullable=True, comment="最高库存预警值")

    # 公共字段
    tenant_no = Column(String(64), nullable=False, default="shanxia2.0", comment="租户编号")
    order_num = Column(Integer, nullable=True, comment="排序号")
    remark = Column(String(512), nullable=True, comment="备注")
    create_by = Column(String(64), nullable=True, comment="创建人id")
    create_dept_id = Column(String(64), nullable=False, default="", comment="创建人部门ID")
    create_time = Column(DateTime, nullable=False, default=datetime.now, comment="创建时间")
    update_by = Column(String(64), nullable=True, comment="更新人id")
    update_time = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now, comment="更新时间")
    del_flag = Column(SmallInteger, nullable=False, default=0, comment="删除标记：0-正常 1-已删除")


class PlInventoryTransaction(Base):
    """库存流水表 pl_inventory_transaction"""

    __tablename__ = "pl_inventory_transaction"

    id = Column(String(64), primary_key=True, comment="主键ID")
    product_id = Column(String(64), nullable=False, comment="产品ID")
    product_name = Column(String(200), nullable=False, comment="产品名称")
    transaction_type = Column(String(20), nullable=False, comment="事务类型 inbound-入库 outbound-出库")
    plan_id = Column(String(64), nullable=True, comment="计划ID")
    plan_no = Column(String(32), nullable=True, comment="计划编号")

    # 数量与价格
    qty = Column(DECIMAL(15, 3), nullable=False, comment="变动数量（正数入库/负数出库）")
    unit_price = Column(DECIMAL(15, 4), nullable=False, comment="单价")
    amount = Column(DECIMAL(15, 2), nullable=False, comment="金额")
    stock_after = Column(DECIMAL(15, 3), nullable=False, comment="变动后库存")

    tenant_no = Column(String(64), nullable=False, default="shanxia2.0", comment="租户编号")
    order_num = Column(Integer, nullable=True, comment="排序号")
    remark = Column(String(512), nullable=True, comment="备注")
    create_by = Column(String(64), nullable=True, comment="创建人id")
    create_dept_id = Column(String(64), nullable=False, default="", comment="创建人部门ID")
    create_time = Column(DateTime, nullable=False, default=datetime.now, comment="创建时间")
    update_by = Column(String(64), nullable=True, comment="更新人id")
    update_time = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now, comment="更新时间")
    del_flag = Column(SmallInteger, nullable=False, default=0, comment="删除标记：0-正常 1-已删除")


class PlInventoryWarning(Base):
    """库存预警表 pl_inventory_warning"""

    __tablename__ = "pl_inventory_warning"

    id = Column(String(64), primary_key=True, comment="主键ID")
    product_id = Column(String(64), nullable=False, comment="产品ID")
    product_name = Column(String(200), nullable=False, comment="产品名称")
    warning_type = Column(String(20), nullable=False, comment="预警类型 low-低于最低 high-高于最高")
    current_qty = Column(DECIMAL(15, 3), nullable=False, comment="当前库存")
    threshold_value = Column(DECIMAL(15, 3), nullable=False, comment="阈值")
    status = Column(String(20), nullable=False, default="pending", comment="状态 pending-待处理 resolved-已处理")
    resolve_time = Column(DateTime, nullable=True, comment="处理时间")
    resolve_remark = Column(String(500), nullable=True, comment="处理备注")
    tenant_no = Column(String(64), nullable=False, default="shanxia2.0", comment="租户编号")
    order_num = Column(Integer, nullable=True, comment="排序号")
    remark = Column(String(512), nullable=True, comment="备注")
    create_by = Column(String(64), nullable=True, comment="创建人id")
    create_dept_id = Column(String(64), nullable=False, default="", comment="创建人部门ID")
    create_time = Column(DateTime, nullable=False, default=datetime.now, comment="创建时间")
    update_by = Column(String(64), nullable=True, comment="更新人id")
    update_time = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now, comment="更新时间")
    del_flag = Column(SmallInteger, nullable=False, default=0, comment="删除标记：0-正常 1-已删除")


class PlDocumentReminder(Base):
    """证件到期提醒表 pl_document_reminder"""

    __tablename__ = "pl_document_reminder"

    id = Column(String(64), primary_key=True, comment="主键ID")
    cert_id = Column(String(64), nullable=True, comment="证书ID")
    owner_type = Column(String(20), nullable=True, comment="归属类型 vehicle/personnel")
    owner_id = Column(String(64), nullable=True, comment="归属主体ID")
    owner_label = Column(String(200), nullable=True, comment="归属展示名（车牌/姓名）")
    driver_id = Column(String(64), nullable=True, comment="司机/人员ID（兼容）")
    driver_name = Column(String(100), nullable=True, comment="司机/人员姓名（兼容）")
    document_type = Column(String(100), nullable=False, comment="证件类型/证书名称")
    document_no = Column(String(50), nullable=True, comment="证件号")
    expire_date = Column(Date, nullable=False, comment="到期日期")
    days_remaining = Column(Integer, nullable=False, comment="剩余天数")
    status = Column(String(20), nullable=False, default="pending", comment="状态 pending-待处理 resolved-已处理")
    tenant_no = Column(String(64), nullable=False, default="shanxia2.0", comment="租户编号")
    order_num = Column(Integer, nullable=True, comment="排序号")
    remark = Column(String(512), nullable=True, comment="备注")
    create_by = Column(String(64), nullable=True, comment="创建人id")
    create_dept_id = Column(String(64), nullable=False, default="", comment="创建人部门ID")
    create_time = Column(DateTime, nullable=False, default=datetime.now, comment="创建时间")
    update_by = Column(String(64), nullable=True, comment="更新人id")
    update_time = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now, comment="更新时间")
    del_flag = Column(SmallInteger, nullable=False, default=0, comment="删除标记：0-正常 1-已删除")