"""ORM models for the procurement domain.

See domain ``__init__`` for the topology overview. Naming convention:
all tables use the ``procurement_`` prefix to make the domain
extraction boundary obvious; foreign keys reference existing tables
in the asset / identity / auth domains by their canonical ids.

Migration 222 creates these tables; migration 223 backfills data from
the legacy ``asset_suppliers`` / ``asset_procurement_orders`` /
``asset_business_payments`` tables. Both legacy and new tables coexist
through PR 1; PR 2 retires the asset-prefixed copies after the UI
swap.
"""
from __future__ import annotations

import uuid
from datetime import date as date_type
from datetime import datetime
from decimal import Decimal
from typing import Any

from sqlalchemy import (
    JSON,
    BigInteger,
    Boolean,
    Date,
    DateTime,
    ForeignKey,
    Index,
    Integer,
    Numeric,
    String,
    Text,
    UniqueConstraint,
    text,
)
from sqlalchemy.orm import Mapped, mapped_column, relationship
from sqlalchemy.sql import func

from app.core.database import Base
from app.core.sql_types import GUID as Uuid

# ── Recognised category codes (Phase 1 — 4 top-level buckets) ─────────
PROCUREMENT_CATEGORY_CODES: frozenset[str] = frozenset({
    "software",
    "hardware",
    "cloud",
    "service",
})

# ── Receipt semantics — category determines what "receipt" means ──────
RECEIPT_SEMANTICS: frozenset[str] = frozenset({
    # Hardware path: the line item materialises as ``asset_items`` rows.
    "hardware_provision",
    # Software path: vendor seats become available / SSO enabled.
    "software_activation",
    # Service path: a deliverable acceptance report is uploaded.
    "service_acceptance",
})

# ── Supplier lifecycle status ─────────────────────────────────────────
SUPPLIER_STATUSES: frozenset[str] = frozenset({
    "evaluating",   # being onboarded; not yet usable on new POs
    "active",       # default; usable
    "suspended",    # temporarily blocked (e.g. quality issue)
    "blacklisted",  # permanently blocked; PRs must be refused
})

# ── Contract lifecycle status ─────────────────────────────────────────
CONTRACT_STATUSES: frozenset[str] = frozenset({
    "draft",       # being negotiated
    "signed",      # both parties signed but not yet in effect
    "active",      # currently in effect (effective_date ≤ today < expiry)
    "expired",     # past expiry_date, no renewal
    "terminated",  # ended early
})

CONTRACT_TYPES: frozenset[str] = frozenset({
    "master",     # master agreement (MSA / framework)
    "sow",        # statement of work under a master
    "amendment",  # amendment / addendum to an existing contract
    "standalone", # one-shot contract with no parent
})

# ── PO lifecycle (Helix-side view; finance system has its own) ────────
PO_STATUSES: frozenset[str] = frozenset({
    "draft",         # being composed
    "submitted",     # handed to finance (汇联易) — budget locked
    "approved",      # finance approved (回写)
    "rejected",      # finance rejected — budget released
    "received",      # goods/services received (any receipt_status>=partial)
    "completed",     # fully paid + closed
    "cancelled",     # cancelled before completion (budget released)
})

PO_ORDER_TYPES: frozenset[str] = frozenset({
    "purchase",   # one-time purchase
    "lease",      # rental / lease
    "subscription",  # recurring (SaaS, cloud usage)
    "service",    # service contract / SOW
})

RECEIPT_STATUSES: frozenset[str] = frozenset({
    "pending",   # nothing received yet
    "partial",   # some lines received, not all
    "received",  # all lines received
    "rejected",  # delivery refused / quality fail
})

PAYMENT_STATUSES: frozenset[str] = frozenset({
    "pending",   # PO completed but no payment recorded
    "partial",   # some milestones paid
    "paid",      # all paid
    "void",      # cancelled / refunded
})


# ──────────────────────────────────────────────────────────────────────
# A. Master data
# ──────────────────────────────────────────────────────────────────────


class ProcurementSerialCounter(Base):
    """Transactional allocator state for human procurement numbers.

    One hot row per (prefix, calendar year). The allocator atomically UPSERTs
    ``last_value + 1``; the row lock is held in the same transaction as the
    resource INSERT, so rollback releases both the number and the resource.
    """

    __tablename__ = "procurement_serial_counters"

    prefix: Mapped[str] = mapped_column(String(16), primary_key=True)
    serial_year: Mapped[int] = mapped_column(Integer, primary_key=True)
    last_value: Mapped[int] = mapped_column(BigInteger, nullable=False)
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        nullable=False,
        server_default=func.now(),
        onupdate=func.now(),
    )


class ProcurementCategory(Base):
    """First-class procurement category master data.

    Each row encodes the *defaults* a new PO of this category should
    pick up: who's the natural owner, what cost-centre the spend rolls
    up to, how many days before contract expiry someone should be
    pinged, and which receipt semantics apply. UI exposes these as
    edit-in-place fields under /admin/procurement/categories.

    Code uniqueness is enforced at the DB level. The Phase 1 starter
    set is software / hardware / cloud / service; new codes are
    admin-creatable from the UI — the only constraint is that
    ``receipt_semantics`` MUST be one of the three known modes so
    downstream PO completion code never has to branch on an unknown
    semantic.
    """
    __tablename__ = "procurement_categories"
    __mapper_args__ = {"eager_defaults": True}

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    code: Mapped[str] = mapped_column(String(32), nullable=False, unique=True)
    name_zh: Mapped[str] = mapped_column(String(64), nullable=False)
    name_en: Mapped[str] = mapped_column(String(64), nullable=False)
    parent_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("procurement_categories.id"),
    )
    description: Mapped[str | None] = mapped_column(Text)

    # Defaults that auto-populate new POs of this category.
    default_owner_person_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_persons.id"),
    )
    default_cost_center: Mapped[str | None] = mapped_column(String(64))
    default_legal_entity_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("asset_legal_entities.id"),
    )
    # Number of days before contract expiry to start nagging
    # business_owner + procurement_owner. NULL → don't auto-remind.
    default_reminder_days: Mapped[int | None] = mapped_column(Integer)

    # One of RECEIPT_SEMANTICS — drives what "receipt" means on POs of
    # this category. ``service`` rows almost always pick
    # ``service_acceptance``; ``software`` picks ``software_activation``;
    # ``hardware`` picks ``hardware_provision``; ``cloud`` typically
    # picks ``software_activation`` (cloud accounts open == receipt).
    receipt_semantics: Mapped[str] = mapped_column(String(32), nullable=False)

    is_active: Mapped[bool] = mapped_column(
        Boolean, nullable=False, default=True, server_default=text("1"),
    )
    sort_order: Mapped[int] = mapped_column(
        Integer, nullable=False, default=100, server_default=text("100"),
    )

    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
        onupdate=func.now(),
    )


class ProcurementSupplier(Base):
    """Supplier master data — full vendor record, not a phone-book.

    Replaces the legacy ``asset_suppliers`` table (kept alive through
    PR 1 for FK references from old code; retired in PR 2 after the
    swap). Multi-contact list lives in ``contacts`` JSON to avoid a
    separate normalised table — the search / filter UI never needs
    to join on individual contacts.
    """
    __tablename__ = "procurement_suppliers"
    __mapper_args__ = {"eager_defaults": True}

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    supplier_code: Mapped[str] = mapped_column(String(32), nullable=False, unique=True)
    name: Mapped[str] = mapped_column(String(128), nullable=False, unique=True)
    legal_name: Mapped[str | None] = mapped_column(String(256))
    country: Mapped[str | None] = mapped_column(String(64))
    tax_id: Mapped[str | None] = mapped_column(String(64))
    registered_address: Mapped[str | None] = mapped_column(String(512))
    website: Mapped[str | None] = mapped_column(String(256))

    # Status from SUPPLIER_STATUSES.
    status: Mapped[str] = mapped_column(
        String(16), nullable=False, default="active", server_default="'active'",
    )

    # ── Finance ────
    default_currency: Mapped[str | None] = mapped_column(String(8))
    # NET30 / NET60 / NET90 / PREPAID / IMMEDIATE / CUSTOM
    payment_terms: Mapped[str | None] = mapped_column(String(32))
    bank_name: Mapped[str | None] = mapped_column(String(128))
    bank_account: Mapped[str | None] = mapped_column(String(128))
    bank_account_name: Mapped[str | None] = mapped_column(String(128))
    swift_code: Mapped[str | None] = mapped_column(String(32))

    # JSON: [{name, role, email, phone, is_primary}] — primary contact
    # is whichever row has is_primary=true (UI enforces ≤ 1 primary).
    contacts: Mapped[Any | None] = mapped_column(JSON)

    # ── Risk + ops ────
    credit_rating: Mapped[str | None] = mapped_column(String(16))
    last_evaluation_at: Mapped[date_type | None] = mapped_column(Date)

    # JSON array of asset_legal_entities.id this supplier has ever
    # signed a contract with. Helps surface "supplier X has only ever
    # signed with CN entity, but you're trying to put it on a US PO".
    # Maintained by service.py on contract create/edit; manual ops
    # may also edit.
    supports_legal_entities: Mapped[Any | None] = mapped_column(JSON)

    notes: Mapped[str | None] = mapped_column(Text)

    # ── Legacy preservation ────
    # ``asset_suppliers`` had only ``contact_name`` + ``contact_phone``
    # — at migration time the values are folded into the first row of
    # ``contacts`` AND preserved here so the rare "original phone-book
    # row, untouched" can be re-derived if an admin needs it.
    legacy_contact_name: Mapped[str | None] = mapped_column(String(64))
    legacy_contact_phone: Mapped[str | None] = mapped_column(String(32))
    # FK back to the asset_suppliers row this was migrated from
    # (NULL if hand-created post-migration). Lets old FK references
    # (asset_procurement_orders.supplier_id) resolve transparently in
    # PR 2's UI swap by JOIN to ``procurement_suppliers ON
    # legacy_supplier_id = asset_supplier.id``.
    legacy_supplier_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("asset_suppliers.id"),
    )

    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
        onupdate=func.now(),
    )

    __table_args__ = (
        Index("ix_procurement_suppliers_status", "status"),
        Index("ix_procurement_suppliers_legacy", "legacy_supplier_id"),
    )


# ──────────────────────────────────────────────────────────────────────
# B. Contracts (master data + attachments + version history)
# ──────────────────────────────────────────────────────────────────────


class ProcurementContract(Base):
    """A contract / agreement with a supplier — first-class entity.

    The legacy world stored ``contract_expiry`` as a free-text column
    on ``asset_business_payments`` and nothing else; this row puts
    contracts on their own footing with: amount, currency, signed /
    effective / expiry dates, auto-renew flag, four-role owner model,
    attachment + version subtables.
    """
    __tablename__ = "procurement_contracts"
    __mapper_args__ = {"eager_defaults": True}

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    contract_number: Mapped[str] = mapped_column(String(64), nullable=False, unique=True)
    title: Mapped[str] = mapped_column(String(256), nullable=False)

    supplier_id: Mapped[uuid.UUID] = mapped_column(
        Uuid, ForeignKey("procurement_suppliers.id"), nullable=False,
    )
    legal_entity_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("asset_legal_entities.id"),
    )
    category_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("procurement_categories.id"),
    )

    # ``master`` / ``sow`` / ``amendment`` / ``standalone`` —
    # SOW + amendment must have ``parent_contract_id`` set.
    contract_type: Mapped[str] = mapped_column(
        String(16), nullable=False, default="standalone",
        server_default="'standalone'",
    )
    parent_contract_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("procurement_contracts.id"),
    )

    total_amount: Mapped[Decimal | None] = mapped_column(Numeric(14, 2))
    currency: Mapped[str] = mapped_column(
        String(8), nullable=False, default="CNY", server_default="'CNY'",
    )

    signed_date: Mapped[date_type | None] = mapped_column(Date)
    effective_date: Mapped[date_type | None] = mapped_column(Date)
    expiry_date: Mapped[date_type | None] = mapped_column(Date)

    # Auto-renew → reminders nag instead of expire.
    auto_renew: Mapped[bool] = mapped_column(
        Boolean, nullable=False, default=False, server_default=text("0"),
    )
    # Number of days before ``expiry_date`` to push the first reminder.
    # NULL → fall back to ``category.default_reminder_days``; if that's
    # also NULL → no reminder.
    renew_notice_days: Mapped[int | None] = mapped_column(Integer)

    status: Mapped[str] = mapped_column(
        String(16), nullable=False, default="draft", server_default="'draft'",
    )

    # ── Four-role owner model ─────────────────────────────────────────
    business_owner_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_persons.id"),
    )
    it_contact_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_persons.id"),
    )
    finance_contact_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_persons.id"),
    )
    procurement_owner_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_persons.id"),
    )

    description: Mapped[str | None] = mapped_column(Text)
    notes: Mapped[str | None] = mapped_column(Text)

    # External finance-system links (汇联易 / NetSuite contract IDs)
    external_contract_no: Mapped[str | None] = mapped_column(String(128))

    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
        onupdate=func.now(),
    )

    __table_args__ = (
        Index("ix_procurement_contracts_supplier", "supplier_id"),
        Index("ix_procurement_contracts_status", "status"),
        Index("ix_procurement_contracts_expiry", "expiry_date"),
        Index("ix_procurement_contracts_category", "category_id"),
    )

    supplier: Mapped[ProcurementSupplier] = relationship(lazy="joined")


class ProcurementContractAttachment(Base):
    """File attached to a contract — PDF, scanned signature page, etc.

    Storage uses the existing S3 bucket
    ``helix-prod-uploads-ap-southeast-1`` (BucketOwnerEnforced + SSE-S3
    + versioned, via IAM instance profile — no KMS or per-tenant keys
    yet). ``s3_key`` is the object key; the service-layer download
    helper resolves it into a pre-signed URL on demand.
    """
    __tablename__ = "procurement_contract_attachments"
    __mapper_args__ = {"eager_defaults": True}

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    contract_id: Mapped[uuid.UUID] = mapped_column(
        Uuid, ForeignKey("procurement_contracts.id"),
        nullable=False, index=True,
    )
    file_name: Mapped[str] = mapped_column(String(256), nullable=False)
    s3_key: Mapped[str] = mapped_column(String(512), nullable=False)
    content_type: Mapped[str | None] = mapped_column(String(128))
    size_bytes: Mapped[int | None] = mapped_column(Integer)
    description: Mapped[str | None] = mapped_column(Text)
    uploaded_by_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_persons.id"),
    )
    uploaded_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
    )


class ProcurementContractVersion(Base):
    """Immutable version history for a contract.

    Every edit that changes amount / expiry / status / supplier /
    legal_entity writes a row here BEFORE the update lands on the
    main row. Lets an auditor reconstruct "what did this contract
    look like on date X" without giving up the simple "current state
    = main table" query path for everyday UI.
    """
    __tablename__ = "procurement_contract_versions"
    __mapper_args__ = {"eager_defaults": True}

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    contract_id: Mapped[uuid.UUID] = mapped_column(
        Uuid, ForeignKey("procurement_contracts.id"),
        nullable=False, index=True,
    )
    version_no: Mapped[int] = mapped_column(Integer, nullable=False)
    # JSON: {field: {"from": ..., "to": ...}, ...} — only changed keys.
    changed_fields: Mapped[Any] = mapped_column(JSON, nullable=False)
    change_summary: Mapped[str | None] = mapped_column(Text)
    changed_by_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_persons.id"),
    )
    changed_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
    )

    __table_args__ = (
        UniqueConstraint("contract_id", "version_no", name="uq_contract_version"),
    )


# ──────────────────────────────────────────────────────────────────────
# C. Purchase orders + line items
# ──────────────────────────────────────────────────────────────────────


class ProcurementOrder(Base):
    """Purchase order (PR + PO unified — Phase 1 doesn't separate PR/PO).

    The legacy ``asset_procurement_orders`` table is preserved for
    backward compatibility through PR 1; this is the v2 with full
    relational links + four-role owners + structured line items.
    """
    __tablename__ = "procurement_orders"
    __mapper_args__ = {"eager_defaults": True}

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    order_number: Mapped[str] = mapped_column(String(64), nullable=False, unique=True)
    order_type: Mapped[str] = mapped_column(
        String(16), nullable=False, default="purchase", server_default="'purchase'",
    )
    status: Mapped[str] = mapped_column(
        String(16), nullable=False, default="draft", server_default="'draft'",
    )

    # ── Relational keys ─────────────────────────────────────────────
    category_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("procurement_categories.id"),
    )
    supplier_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("procurement_suppliers.id"),
    )
    contract_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("procurement_contracts.id"),
    )
    legal_entity_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("asset_legal_entities.id"),
    )
    software_catalog_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("asset_software_catalog.id"),
    )

    # Free-text dept fallback when no formal dept_id model exists in
    # Phase 1 (no ``identity_departments`` table yet — that's separate
    # future work). Cost-centre is the spend-rollup key.
    department: Mapped[str | None] = mapped_column(String(128))
    cost_center: Mapped[str | None] = mapped_column(String(64))

    # ── Money ─────────────────────────────────────────────────────────
    currency: Mapped[str] = mapped_column(
        String(8), nullable=False, default="CNY", server_default="'CNY'",
    )
    total_amount: Mapped[Decimal | None] = mapped_column(Numeric(14, 2))
    # Frozen rate at submission time so reports don't shift later.
    fx_rate_to_cny: Mapped[Decimal | None] = mapped_column(Numeric(12, 6))
    total_cny: Mapped[Decimal | None] = mapped_column(Numeric(14, 2))

    # ── Four-role owners ─────────────────────────────────────────────
    applicant_id: Mapped[uuid.UUID] = mapped_column(
        Uuid, ForeignKey("identity_persons.id"), nullable=False,
    )
    business_owner_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_persons.id"),
    )
    it_contact_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_persons.id"),
    )
    finance_contact_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_persons.id"),
    )
    procurement_owner_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_persons.id"),
    )

    # ── Receipt status (PO-level rollup over line items) ─────────────
    receipt_status: Mapped[str] = mapped_column(
        String(16), nullable=False, default="pending", server_default="'pending'",
    )
    received_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    receipt_evidence_url: Mapped[str | None] = mapped_column(String(512))

    # ── Payment rollup (records summary only; detail in payments table) ──
    payment_status: Mapped[str] = mapped_column(
        String(16), nullable=False, default="pending", server_default="'pending'",
    )

    # ── Budget linkage (PR 3 wires the actual lock/release) ──────────
    budget_id: Mapped[uuid.UUID | None] = mapped_column(Uuid)
    budget_locked_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    budget_released_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))

    # ── Approval (manual external system) ─────────────────────────────
    requested_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    approved_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    approved_by_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_persons.id"),
    )
    completed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))

    # External finance-system hooks (filled in once the operator pastes
    # the 汇联易 / NetSuite PO number back into Helix).
    external_po_number: Mapped[str | None] = mapped_column(String(128))
    external_link: Mapped[str | None] = mapped_column(String(512))

    notes: Mapped[str | None] = mapped_column(Text)

    # ── Legacy preservation ──────────────────────────────────────────
    # Pointer to the ``asset_procurement_orders`` row this was
    # migrated from (NULL if hand-created post-migration). Lets PR 2
    # transparently route ``asset_business_payments.procurement_order_id``
    # to the new model via JOIN.
    legacy_order_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("asset_procurement_orders.id"),
    )
    # Free-text fields from the legacy world that couldn't be cleanly
    # resolved to FKs at migration time (e.g. ``supplier_name`` typo,
    # ``po_number`` referring to a dead asset_procurement_order).
    # Preserved as forensic context — the human-review page for
    # these rows was retired 2026-05-30 after the legacy backfill
    # settled; admins inspect the column directly when needed.
    legacy_unresolved_fields: Mapped[Any | None] = mapped_column(JSON)

    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
        onupdate=func.now(),
    )

    __table_args__ = (
        Index("ix_procurement_orders_status", "status"),
        Index("ix_procurement_orders_supplier", "supplier_id"),
        Index("ix_procurement_orders_contract", "contract_id"),
        Index("ix_procurement_orders_category", "category_id"),
        Index("ix_procurement_orders_applicant", "applicant_id"),
        Index("ix_procurement_orders_legacy", "legacy_order_id"),
        Index("ix_procurement_orders_created", "created_at"),
    )

    supplier: Mapped[ProcurementSupplier] = relationship(
        lazy="select", foreign_keys=[supplier_id],
    )


class ProcurementOrderItem(Base):
    """One line of a purchase order.

    Replaces the legacy ``asset_procurement_orders.items`` JSON blob
    with structured rows so each line can:
      * carry its own quantity / unit price / line subtotal
      * point at the eventual ``asset_items.id`` (hardware path) or
        ``software_license_plans.id`` (software path) it materialises
        as
      * be individually marked received (allowing partial-receipt POs)
    """
    __tablename__ = "procurement_order_items"
    __mapper_args__ = {"eager_defaults": True}

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    order_id: Mapped[uuid.UUID] = mapped_column(
        Uuid, ForeignKey("procurement_orders.id"),
        nullable=False, index=True,
    )
    line_no: Mapped[int] = mapped_column(Integer, nullable=False)

    description: Mapped[str] = mapped_column(String(512), nullable=False)
    quantity: Mapped[Decimal] = mapped_column(
        Numeric(12, 2), nullable=False, default=1, server_default="1",
    )
    unit: Mapped[str | None] = mapped_column(String(32))
    unit_price: Mapped[Decimal | None] = mapped_column(Numeric(14, 2))
    line_total: Mapped[Decimal | None] = mapped_column(Numeric(14, 2))
    currency: Mapped[str | None] = mapped_column(String(8))

    # Target identification (which asset/software/cloud-account this
    # line item produces). NULL = abstract line (e.g. "consulting
    # service 3 days") that does not map to a downstream entity.
    target_asset_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("asset_items.id"),
    )
    target_software_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("asset_software_catalog.id"),
    )
    target_license_plan_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("software_license_plans.id"),
    )
    # Free text for cloud / service items that don't have a Helix
    # entity yet (e.g. "AWS Production Account" before the cloud
    # provider account row exists).
    target_descriptor: Mapped[str | None] = mapped_column(String(256))

    receipt_status: Mapped[str] = mapped_column(
        String(16), nullable=False, default="pending", server_default="'pending'",
    )
    received_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    received_qty: Mapped[Decimal | None] = mapped_column(Numeric(12, 2))

    notes: Mapped[str | None] = mapped_column(Text)

    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
        onupdate=func.now(),
    )

    __table_args__ = (
        UniqueConstraint("order_id", "line_no", name="uq_procurement_order_line"),
    )


# ──────────────────────────────────────────────────────────────────────
# D. Payments (outbound — to suppliers)
# ──────────────────────────────────────────────────────────────────────


class ProcurementPayment(Base):
    """A recorded outbound payment to a supplier.

    Phase 1 scope is RECORD-ONLY. Actual payment execution lives in
    Oracle NetSuite; this table just captures "we paid X to supplier
    Y on date Z against PO P / contract C, NetSuite voucher V"
    facts for IT-side reporting + reconciliation.

    Distinct from ``software_monthly_bills`` (internal per-seat
    chargeback) — UI presents them as separate tabs to prevent
    conflation.
    """
    __tablename__ = "procurement_payments"
    __mapper_args__ = {"eager_defaults": True}

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    payment_number: Mapped[str] = mapped_column(String(64), nullable=False, unique=True)

    # Relational keys — at least one of (order_id, contract_id,
    # software_catalog_id) should be set; service layer enforces.
    order_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("procurement_orders.id"),
    )
    contract_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("procurement_contracts.id"),
    )
    supplier_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("procurement_suppliers.id"),
    )
    legal_entity_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("asset_legal_entities.id"),
    )
    software_catalog_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("asset_software_catalog.id"),
    )

    payment_frequency: Mapped[str | None] = mapped_column(String(32))
    payment_method: Mapped[str | None] = mapped_column(String(32))

    requester_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_persons.id"),
    )
    finance_contact_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_persons.id"),
    )

    # Amount + currency + paid status.
    amount: Mapped[Decimal | None] = mapped_column(Numeric(14, 2))
    currency: Mapped[str] = mapped_column(
        String(8), nullable=False, default="CNY", server_default="'CNY'",
    )
    fx_rate_to_cny: Mapped[Decimal | None] = mapped_column(Numeric(12, 6))
    amount_cny: Mapped[Decimal | None] = mapped_column(Numeric(14, 2))

    status: Mapped[str] = mapped_column(
        String(16), nullable=False, default="pending", server_default="'pending'",
    )
    invoice_number: Mapped[str | None] = mapped_column(String(128))
    invoice_received_at: Mapped[date_type | None] = mapped_column(Date)
    paid_at: Mapped[date_type | None] = mapped_column(Date)
    # NetSuite voucher # / 汇联易 expense report # — manually pasted.
    external_voucher_no: Mapped[str | None] = mapped_column(String(128))

    notes: Mapped[str | None] = mapped_column(Text)

    # ── Legacy preservation ─────────────────────────────────────────
    legacy_payment_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("asset_business_payments.id"),
    )
    legacy_unresolved_fields: Mapped[Any | None] = mapped_column(JSON)

    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
        onupdate=func.now(),
    )

    __table_args__ = (
        Index("ix_procurement_payments_order", "order_id"),
        Index("ix_procurement_payments_contract", "contract_id"),
        Index("ix_procurement_payments_supplier", "supplier_id"),
        Index("ix_procurement_payments_software", "software_catalog_id"),
        Index("ix_procurement_payments_status", "status"),
        Index("ix_procurement_payments_legacy", "legacy_payment_id"),
    )


# ──────────────────────────────────────────────────────────────────────
# E. Software → procurement sidecar (1:1)
# ──────────────────────────────────────────────────────────────────────


class SoftwareProcurementMeta(Base):
    """1:1 sidecar on ``asset_software_catalog`` separating procurement
    basis (which contract + supplier covers this SaaS) from operational
    state (seat / SSO / offboarding — still on the catalog row).

    Modelled as a separate table rather than columns on the catalog
    row to avoid bloating the hot operational-state schema and to make
    the "procurement layer is decoupled" boundary visible to readers.
    """
    __tablename__ = "software_procurement_meta"
    __mapper_args__ = {"eager_defaults": True}

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    software_id: Mapped[uuid.UUID] = mapped_column(
        Uuid, ForeignKey("asset_software_catalog.id"),
        nullable=False, unique=True,
    )

    current_contract_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("procurement_contracts.id"),
    )
    current_supplier_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("procurement_suppliers.id"),
    )

    expected_renewal_at: Mapped[date_type | None] = mapped_column(Date)
    current_period_amount: Mapped[Decimal | None] = mapped_column(Numeric(14, 2))
    current_period_currency: Mapped[str | None] = mapped_column(String(8))

    business_owner_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_persons.id"),
    )
    it_contact_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_persons.id"),
    )
    finance_contact_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_persons.id"),
    )
    procurement_owner_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_persons.id"),
    )

    notes: Mapped[str | None] = mapped_column(Text)

    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
        onupdate=func.now(),
    )


# ──────────────────────────────────────────────────────────────────────
# F. Budgets + consumptions (PR 3, migration 224)
# ──────────────────────────────────────────────────────────────────────


BUDGET_EVENT_TYPES: frozenset[str] = frozenset({
    # Budget consumed by a PO that is in flight (draft / submitted).
    "lock",
    # PO cancelled / rejected — return the locked budget.
    "release",
    # Payment settled — convert the lock into actual spend. Phase 1
    # treats this as informational (open commitment + actual spend
    # both reduce remaining, but PR 3 keeps the simple model:
    # remaining = total - SUM(lock) + SUM(release); actualize doesn't
    # change remaining, just decorates the activity log).
    "actualize",
})


class ProcurementBudget(Base):
    """Annual budget pool per (year, department, cost_center, category, currency).

    PR 3's model is intentionally simple:
    * One row per (year, department, cost_center, category_id, currency)
      — multi-currency is multi-row, no cross-currency aggregation.
    * ``total_amount`` is the ceiling for the year.
    * Remaining balance is computed at read time as
      ``total - SUM(consumptions where event=lock) +
       SUM(consumptions where event=release)``.
    * ``owner_person_id`` is the department head / approver who reads
      ``/me/budget`` for their pool.
    """
    __tablename__ = "procurement_budgets"
    __mapper_args__ = {"eager_defaults": True}

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    year: Mapped[int] = mapped_column(Integer, nullable=False)
    department: Mapped[str] = mapped_column(String(128), nullable=False)
    cost_center: Mapped[str | None] = mapped_column(String(64))
    category_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("procurement_categories.id"),
    )
    currency: Mapped[str] = mapped_column(
        String(8), nullable=False, default="CNY", server_default="'CNY'",
    )
    total_amount: Mapped[Decimal] = mapped_column(Numeric(14, 2), nullable=False)
    owner_person_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_persons.id"),
    )
    notes: Mapped[str | None] = mapped_column(Text)
    is_active: Mapped[bool] = mapped_column(
        Boolean, nullable=False, default=True, server_default=text("1"),
    )
    # Edge-trigger state for the budget-threshold reminder cron. Holds the
    # highest utilization tier (80 / 95) already alerted to the owner. The
    # cron pushes ONLY when the live tier rises above this value (and resets
    # it back down when utilization falls below a previously-alerted tier),
    # so a pool that merely sits at 85% nags the owner exactly once, not
    # every week. NULL / 0 = no tier alerted yet. See
    # ``scheduled_jobs/procurement_handlers.py``.
    last_alerted_threshold: Mapped[int | None] = mapped_column(Integer)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
        onupdate=func.now(),
    )

    __table_args__ = (
        UniqueConstraint(
            "year", "department", "cost_center", "category_id", "currency",
            name="uq_procurement_budgets_scope",
        ),
        Index("ix_procurement_budgets_year_dept", "year", "department"),
        Index("ix_procurement_budgets_owner", "owner_person_id"),
    )


class ProcurementBudgetConsumption(Base):
    """Append-only log of budget mutation events.

    Created by the procurement service whenever a PO writes affect a
    budget — typically:
      * PO created with budget_id set → ``event='lock'``
      * PO cancelled / rejected       → ``event='release'``
      * Payment paid_at stamped        → ``event='actualize'``
    """
    __tablename__ = "procurement_budget_consumptions"
    __mapper_args__ = {"eager_defaults": True}

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    budget_id: Mapped[uuid.UUID] = mapped_column(
        Uuid, ForeignKey("procurement_budgets.id"), nullable=False, index=True,
    )
    po_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("procurement_orders.id"),
    )
    event: Mapped[str] = mapped_column(String(16), nullable=False)
    amount: Mapped[Decimal] = mapped_column(Numeric(14, 2), nullable=False)
    currency: Mapped[str] = mapped_column(String(8), nullable=False)
    amount_cny: Mapped[Decimal | None] = mapped_column(Numeric(14, 2))
    fx_rate_to_cny: Mapped[Decimal | None] = mapped_column(Numeric(12, 6))
    notes: Mapped[str | None] = mapped_column(Text)
    actor_person_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_persons.id"),
    )
    occurred_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
    )