"""旧库 fw_* 表映射。表名、列名与旧库一致（文档第 9 节），属性名用 snake_case。"""
import datetime as dt
import uuid

from sqlalchemy import Boolean, Date, DateTime, Float, ForeignKey, Integer, String, Text, Uuid
from sqlalchemy.orm import Mapped, mapped_column, relationship

from flyway.db import Base


def pk():
    return mapped_column("ID", Uuid, primary_key=True, default=uuid.uuid4)


def utcnow() -> dt.datetime:
    return dt.datetime.now(dt.UTC)


class Client(Base):
    __tablename__ = "fw_Client"
    id: Mapped[uuid.UUID] = pk()
    name: Mapped[str] = mapped_column("Name", String(50))
    email: Mapped[str] = mapped_column("Email", String(100), default="")
    mobile_phone: Mapped[str] = mapped_column("MobilePhone", String(50), default="")
    phone: Mapped[str | None] = mapped_column("Phone", String(50))
    phone1: Mapped[str | None] = mapped_column("Phone1", String(50))


class Product(Base):
    __tablename__ = "fw_Product"
    id: Mapped[uuid.UUID] = pk()
    title: Mapped[str] = mapped_column("Title", String(256))
    default_unit: Mapped[int] = mapped_column("DefaultUnit", Integer, default=1)
    available_unit: Mapped[str | None] = mapped_column("AvailableUnit", Text)


class ProductBatch(Base):
    __tablename__ = "fw_ProductBatch"
    id: Mapped[uuid.UUID] = pk()
    product_id: Mapped[uuid.UUID] = mapped_column("ProductId", ForeignKey("fw_Product.ID"), index=True)
    number: Mapped[str] = mapped_column("Number", String(50))
    initial_stock: Mapped[int] = mapped_column("InitialStock", Integer, default=0)
    date: Mapped[dt.date] = mapped_column("Date", Date)
    display_date: Mapped[dt.datetime | None] = mapped_column(DateTime)
    used_by_date: Mapped[dt.date | None] = mapped_column("UsedByDate", Date)
    product: Mapped[Product] = relationship()


class Supplier(Base):
    __tablename__ = "fw_Supplier"
    id: Mapped[uuid.UUID] = pk()
    name: Mapped[str] = mapped_column("Name", String(150))
    rmp_number: Mapped[str] = mapped_column("RMPNumber", String(150))


class Template(Base):
    __tablename__ = "fw_Template"
    id: Mapped[uuid.UUID] = pk()
    type: Mapped[str] = mapped_column("Type", String(50))
    content: Mapped[str | None] = mapped_column("Content", Text)


class PackingSlip(Base):
    __tablename__ = "fw_PackingSlip"
    id: Mapped[uuid.UUID] = pk()
    reference: Mapped[str] = mapped_column("Reference", String(50))
    date: Mapped[dt.datetime] = mapped_column("Date", DateTime(timezone=True))
    from_location: Mapped[str] = mapped_column("FromLocation", String(256))
    from_rmp: Mapped[str] = mapped_column("FromRMP", String(50))
    to_location: Mapped[str] = mapped_column("ToLocation", String(256))
    to_rmp: Mapped[str] = mapped_column("ToRMP", String(50))
    completed: Mapped[dt.datetime | None] = mapped_column("Completed", DateTime(timezone=True))
    operator: Mapped[str | None] = mapped_column("Operator", String(100))
    logs: Mapped[str | None] = mapped_column("Logs", Text)
    awb_number: Mapped[str | None] = mapped_column("AwbNumber", String(50))
    transfers: Mapped[list["Transfer"]] = relationship(back_populates="packing_slip")


class Transfer(Base):
    """入库 / 出库 / 调拨共用的单据表（IsLoadOut、IsLocal 区分类型）。"""

    __tablename__ = "fw_ClientTransfer"
    id: Mapped[uuid.UUID] = pk()
    is_local: Mapped[bool] = mapped_column("IsLocal", Boolean, default=False)
    client_id: Mapped[uuid.UUID] = mapped_column("ClientId", ForeignKey("fw_Client.ID"), index=True)
    code: Mapped[str] = mapped_column("Code", String(50))
    packing_slip_id: Mapped[uuid.UUID | None] = mapped_column(
        "PackingSlipId", ForeignKey("fw_PackingSlip.ID"), index=True
    )
    date: Mapped[dt.datetime] = mapped_column("Date", DateTime(timezone=True))
    departure_date: Mapped[dt.datetime | None] = mapped_column("DepartureDate", DateTime(timezone=True))
    is_load_out: Mapped[bool] = mapped_column("IsLoadOut", Boolean, default=False)
    from_location: Mapped[str | None] = mapped_column("FromLocation", String(256))
    from_rmp: Mapped[str | None] = mapped_column("FromRMP", String(50))
    to_location: Mapped[str | None] = mapped_column("ToLocation", String(256))
    to_rmp: Mapped[str | None] = mapped_column("ToRMP", String(50))
    transfer_from_id: Mapped[uuid.UUID | None] = mapped_column(
        "TransferFromId", ForeignKey("fw_ClientTransfer.ID"), index=True
    )
    transport_company: Mapped[str | None] = mapped_column("TransportCompany", String(256))
    vehicle_registration: Mapped[str | None] = mapped_column("VehicleRegistration", String(50))
    condition_of_vehicle: Mapped[str | None] = mapped_column("ConditionOfVehicle", String(50))
    non_conforming_product: Mapped[bool] = mapped_column("NonConformingProduct", Boolean, default=False)
    container_number: Mapped[str | None] = mapped_column("ContainerNumber", String(50))
    seal_number: Mapped[str | None] = mapped_column("SealNumber", String(50))
    can_number: Mapped[str | None] = mapped_column("CanNumber", String(50))
    awb_number: Mapped[str | None] = mapped_column("AwbNumber", String(50))
    is_draft: Mapped[bool] = mapped_column("IsDraft", Boolean, default=True)
    operator: Mapped[str | None] = mapped_column("Operator", String(100))
    logs: Mapped[str | None] = mapped_column("Logs", Text)
    notes: Mapped[str | None] = mapped_column("Notes", Text)
    ed_identification: Mapped[str | None] = mapped_column("EdIdentification", String(128))
    export_to: Mapped[str | None] = mapped_column("ExportTo", String(128))

    client: Mapped[Client] = relationship()
    packing_slip: Mapped[PackingSlip | None] = relationship(back_populates="transfers")
    items: Mapped[list["TransferItem"]] = relationship(back_populates="transfer", cascade="all, delete-orphan")
    transfer_from: Mapped["Transfer | None"] = relationship(remote_side="Transfer.id", back_populates="transfer_tos")
    transfer_tos: Mapped[list["Transfer"]] = relationship(back_populates="transfer_from")


class TransferItem(Base):
    __tablename__ = "fw_ClientTransferItem"
    id: Mapped[uuid.UUID] = pk()
    transfer_id: Mapped[uuid.UUID] = mapped_column("TransferId", ForeignKey("fw_ClientTransfer.ID"), index=True)
    batch_id: Mapped[uuid.UUID] = mapped_column("BatchId", ForeignKey("fw_ProductBatch.ID"), index=True)
    quantity: Mapped[int] = mapped_column("Quantity", Integer, default=0)
    net_weight: Mapped[float | None] = mapped_column("NetWeight", Float)
    rel_id: Mapped[str | None] = mapped_column("RelId", String(64))
    sku: Mapped[str | None] = mapped_column("SKU", String(64))
    reference: Mapped[str | None] = mapped_column("Reference", String(100))
    related_transfer_item_id: Mapped[uuid.UUID | None] = mapped_column(
        "RelatedTransferItemId", ForeignKey("fw_ClientTransferItem.ID"), index=True
    )
    remaining_stock: Mapped[int] = mapped_column("RemainingStock", Integer, default=0)
    unit: Mapped[int] = mapped_column("Unit", Integer, default=1)
    ed_location: Mapped[str | None] = mapped_column("EdLocation", String(256))

    transfer: Mapped[Transfer] = relationship(back_populates="items")
    batch: Mapped[ProductBatch] = relationship()
    main_item: Mapped["TransferItem | None"] = relationship(
        remote_side="TransferItem.id", back_populates="related_items"
    )
    related_items: Mapped[list["TransferItem"]] = relationship(back_populates="main_item")


class TransferRelation(Base):
    """出库 / 调拨单（RelTransferId）与“关联入库单”（TransferId）的多对多。"""

    __tablename__ = "fw_ClientTransferRelations"
    id: Mapped[uuid.UUID] = pk()
    transfer_id: Mapped[uuid.UUID] = mapped_column("TransferId", ForeignKey("fw_ClientTransfer.ID"), index=True)
    rel_transfer_id: Mapped[uuid.UUID] = mapped_column(
        "RelTransferId", ForeignKey("fw_ClientTransfer.ID"), index=True
    )
    load_in: Mapped[Transfer] = relationship(foreign_keys=[transfer_id])
    owner: Mapped[Transfer] = relationship(foreign_keys=[rel_transfer_id])


class ShippingParcel(Base):
    __tablename__ = "fw_ShippingParcels"
    id: Mapped[uuid.UUID] = pk()
    barcode: Mapped[str] = mapped_column("Barcode", String(128))
    comment: Mapped[str | None] = mapped_column("Comment", Text)
    bg_color: Mapped[str | None] = mapped_column("BgColor", String(16))
    font_color: Mapped[str | None] = mapped_column("FontColor", String(16))
    created_utc: Mapped[dt.datetime] = mapped_column("CreatedUtc", DateTime(timezone=True), default=utcnow)
    shipped_utc: Mapped[dt.datetime | None] = mapped_column("ShippedUtc", DateTime(timezone=True))
    deleted_utc: Mapped[dt.datetime | None] = mapped_column("DeletedUtc", DateTime(timezone=True))
    operator_id: Mapped[uuid.UUID | None] = mapped_column("OperatorId", Uuid)


class ClientProduct(Base):
    __tablename__ = "fw_ClientProduct"
    id: Mapped[uuid.UUID] = pk()
    product_id: Mapped[uuid.UUID] = mapped_column("ProductId", ForeignKey("fw_Product.ID"))
    client_id: Mapped[uuid.UUID] = mapped_column("ClientId", ForeignKey("fw_Client.ID"))
    opening_stock: Mapped[int] = mapped_column("OpeningStock", Integer, default=0)
    opening_date: Mapped[dt.date] = mapped_column("OpeningDate", Date)
    opening_display_date: Mapped[dt.datetime | None] = mapped_column(DateTime)
    closing_stock: Mapped[int] = mapped_column("ClosingStock", Integer, default=0)
    closing_date: Mapped[dt.date] = mapped_column("ClosingDate", Date)
    closing_display_date: Mapped[dt.datetime | None] = mapped_column(DateTime)


class ClientAccount(Base):
    __tablename__ = "fw_ClientAccounts"
    id: Mapped[str] = mapped_column("Id", String(128), primary_key=True)
    user_name: Mapped[str | None] = mapped_column("UserName", Text)
    password_hash: Mapped[str | None] = mapped_column("PasswordHash", Text)
    security_stamp: Mapped[str | None] = mapped_column("SecurityStamp", Text)
    client_id: Mapped[uuid.UUID | None] = mapped_column("ClientId", Uuid)
    discriminator: Mapped[str] = mapped_column("Discriminator", String(128), default="ClientAccount")


class ClientRole(Base):
    __tablename__ = "fw_ClientRoles"
    id: Mapped[str] = mapped_column("Id", String(128), primary_key=True)
    name: Mapped[str] = mapped_column("Name", Text)


class ClientAccountRole(Base):
    __tablename__ = "fw_ClientAccountRoles"
    user_id: Mapped[str] = mapped_column("UserId", ForeignKey("fw_ClientAccounts.Id"), primary_key=True)
    role_id: Mapped[str] = mapped_column("RoleId", ForeignKey("fw_ClientRoles.Id"), primary_key=True)


class ClientAccountClaim(Base):
    __tablename__ = "fw_ClientAccountClaims"
    id: Mapped[int] = mapped_column("Id", Integer, primary_key=True, autoincrement=True)
    claim_type: Mapped[str | None] = mapped_column("ClaimType", Text)
    claim_value: Mapped[str | None] = mapped_column("ClaimValue", Text)
    user_id: Mapped[str] = mapped_column("User_Id", ForeignKey("fw_ClientAccounts.Id"))


class ClientAccountLogin(Base):
    __tablename__ = "fw_ClientAccountLogins"
    user_id: Mapped[str] = mapped_column("UserId", ForeignKey("fw_ClientAccounts.Id"), primary_key=True)
    login_provider: Mapped[str] = mapped_column("LoginProvider", String(128), primary_key=True)
    provider_key: Mapped[str] = mapped_column("ProviderKey", String(128), primary_key=True)