from datetime import date, datetime, timezone
from decimal import Decimal
from typing import Optional

from sqlalchemy import Boolean, Date, DateTime, Enum, ForeignKey, Integer, JSON, Numeric, String, Text
from sqlalchemy.orm import Mapped, mapped_column

from app.db.session import Base
from app.models.enums import ProcurementStatus


def _utcnow() -> datetime:
    return datetime.now(timezone.utc).replace(tzinfo=None)


class TimestampMixin:
    created_at: Mapped[datetime] = mapped_column(
        DateTime, default=_utcnow, nullable=False
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime, default=_utcnow, onupdate=_utcnow, nullable=False
    )


class Demand(Base, TimestampMixin):
    __tablename__ = "demands"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    demand_no: Mapped[str] = mapped_column(
        String(50), unique=True, index=True, nullable=False
    )
    department: Mapped[str] = mapped_column(String(120), nullable=False)
    item_description: Mapped[str] = mapped_column(Text, nullable=False)
    quantity: Mapped[int] = mapped_column(Integer, nullable=False)
    estimated_value: Mapped[Decimal] = mapped_column(Numeric(14, 2), default=0)
    required_by: Mapped[Optional[date]] = mapped_column(Date)
    status: Mapped[ProcurementStatus] = mapped_column(
        Enum(ProcurementStatus), default=ProcurementStatus.draft, nullable=False
    )


class ItemDatabase(Base, TimestampMixin):
    __tablename__ = "item_database"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    sr_no: Mapped[int] = mapped_column(Integer, nullable=False, index=True)
    name: Mapped[str] = mapped_column(String(160), nullable=False, index=True)
    name_en: Mapped[Optional[str]] = mapped_column(String(160), index=True)
    name_or: Mapped[Optional[str]] = mapped_column(String(160), index=True)
    item_type: Mapped[str] = mapped_column(String(80), default="Equipment", nullable=False, index=True)
    item_type_en: Mapped[Optional[str]] = mapped_column(String(80), index=True)
    item_type_or: Mapped[Optional[str]] = mapped_column(String(80), index=True)
    description: Mapped[Optional[str]] = mapped_column(Text)
    description_en: Mapped[Optional[str]] = mapped_column(Text)
    description_or: Mapped[Optional[str]] = mapped_column(Text)
    quantity: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    remarks: Mapped[Optional[str]] = mapped_column(Text)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)


class Requisition(Base, TimestampMixin):
    __tablename__ = "requisitions"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    requisition_no: Mapped[str] = mapped_column(
        String(50), unique=True, index=True, nullable=False
    )
    demand_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("demands.id"), index=True
    )
    requisition_date: Mapped[Optional[date]] = mapped_column(Date)
    requested_by: Mapped[Optional[str]] = mapped_column(String(120))
    received_from: Mapped[Optional[str]] = mapped_column(String(160))
    received_from_district: Mapped[Optional[str]] = mapped_column(String(120))
    received_from_range: Mapped[Optional[str]] = mapped_column(String(120))
    sender_name: Mapped[Optional[str]] = mapped_column(String(160))
    sender_designation: Mapped[Optional[str]] = mapped_column(String(160))
    requisition_items: Mapped[Optional[list[dict]]] = mapped_column(JSON)
    item_description: Mapped[Optional[str]] = mapped_column(Text)
    quantity: Mapped[Optional[int]] = mapped_column(Integer)
    estimated_cost: Mapped[Optional[Decimal]] = mapped_column(Numeric(14, 2))
    procurement_mode: Mapped[Optional[str]] = mapped_column(String(120))
    procurement_mode_other: Mapped[Optional[str]] = mapped_column(String(255))
    budget_head: Mapped[Optional[str]] = mapped_column(String(255))
    administrative_approval_date: Mapped[Optional[date]] = mapped_column(Date)
    administrative_approval_quantity: Mapped[Optional[int]] = mapped_column(Integer)
    lifecycle_stage: Mapped[Optional[str]] = mapped_column(String(80), index=True)
    approval_status: Mapped[ProcurementStatus] = mapped_column(
        Enum(ProcurementStatus), default=ProcurementStatus.submitted, nullable=False
    )
    remarks: Mapped[Optional[str]] = mapped_column(Text)


class ProcurementCase(Base, TimestampMixin):
    __tablename__ = "procurement_cases"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    procurement_case_no: Mapped[str] = mapped_column(String(255), unique=True, index=True, nullable=False)
    requisition_id: Mapped[Optional[int]] = mapped_column(ForeignKey("requisitions.id"), index=True)
    approved_quantity: Mapped[Optional[int]] = mapped_column(Integer)
    approved_estimated_cost: Mapped[Optional[Decimal]] = mapped_column(Numeric(14, 2))
    procurement_mode: Mapped[str] = mapped_column(String(40), index=True, nullable=False)
    procurement_mode_other: Mapped[Optional[str]] = mapped_column(String(255))
    case_status: Mapped[str] = mapped_column(String(80), default="In Tendering Process", nullable=False)
    mode_selection_date: Mapped[Optional[date]] = mapped_column(Date)
    mode_selected_by: Mapped[Optional[str]] = mapped_column(String(160))
    emd_applicable: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    pbg_applicable: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    file_no: Mapped[Optional[str]] = mapped_column(String(120))
    sanction_no: Mapped[Optional[str]] = mapped_column(String(120))
    sanction_date: Mapped[Optional[date]] = mapped_column(Date)
    approving_authority: Mapped[Optional[str]] = mapped_column(String(160))
    remarks: Mapped[Optional[str]] = mapped_column(Text)


class GeMProcurementCase(Base, TimestampMixin):
    __tablename__ = "gem_procurement_cases"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    procurement_case_id: Mapped[int] = mapped_column(ForeignKey("procurement_cases.id"), index=True, nullable=False)
    gem_procurement_type: Mapped[str] = mapped_column(String(40), default="Product / Normal Category Bid", nullable=False)
    gem_bid_no: Mapped[Optional[str]] = mapped_column(String(120), index=True)
    gem_bid_date: Mapped[Optional[date]] = mapped_column(Date)
    gem_contract_no: Mapped[Optional[str]] = mapped_column(String(120), index=True)
    gem_contract_date: Mapped[Optional[date]] = mapped_column(Date)
    gem_order_value: Mapped[Optional[Decimal]] = mapped_column(Numeric(14, 2))
    vendor_id: Mapped[Optional[int]] = mapped_column(Integer, index=True)
    delivery_status: Mapped[Optional[str]] = mapped_column(String(80))
    delivery_delay_reason: Mapped[Optional[str]] = mapped_column(Text)
    delivery_delay_document_path: Mapped[Optional[str]] = mapped_column(String(255))
    payment_status: Mapped[Optional[str]] = mapped_column(String(80))
    status: Mapped[str] = mapped_column(String(80), default="In Tendering Process", nullable=False)


class ETenderCase(Base, TimestampMixin):
    __tablename__ = "e_tender_cases"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    procurement_case_id: Mapped[int] = mapped_column(ForeignKey("procurement_cases.id"), index=True, nullable=False)
    tender_ref_no: Mapped[str] = mapped_column(String(120), index=True, nullable=False)
    tender_publication_date: Mapped[Optional[date]] = mapped_column(Date)
    pre_bid_meeting_date: Mapped[Optional[date]] = mapped_column(Date)
    bid_submission_end_date: Mapped[Optional[date]] = mapped_column(Date)
    technical_bid_opening_date: Mapped[Optional[date]] = mapped_column(Date)
    financial_bid_opening_date: Mapped[Optional[date]] = mapped_column(Date)
    estimated_cost: Mapped[Optional[Decimal]] = mapped_column(Numeric(14, 2))
    technical_evaluation_status: Mapped[Optional[str]] = mapped_column(String(80))
    financial_evaluation_status: Mapped[Optional[str]] = mapped_column(String(80))
    l1_vendor_id: Mapped[Optional[int]] = mapped_column(Integer, index=True)
    award_approval_date: Mapped[Optional[date]] = mapped_column(Date)
    loa_no: Mapped[Optional[str]] = mapped_column(String(120))
    loa_date: Mapped[Optional[date]] = mapped_column(Date)
    contract_no: Mapped[Optional[str]] = mapped_column(String(120), index=True)
    status: Mapped[str] = mapped_column(String(80), default="In Tendering Process", nullable=False)


class GlobalTenderCase(Base, TimestampMixin):
    __tablename__ = "global_tender_cases"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    procurement_case_id: Mapped[int] = mapped_column(ForeignKey("procurement_cases.id"), index=True, nullable=False)
    global_tender_ref_no: Mapped[str] = mapped_column(String(120), index=True, nullable=False)
    country_of_vendor: Mapped[Optional[str]] = mapped_column(String(120))
    foreign_vendor_name: Mapped[Optional[str]] = mapped_column(String(180), index=True)
    foreign_currency: Mapped[Optional[str]] = mapped_column(String(20))
    exchange_rate: Mapped[Optional[Decimal]] = mapped_column(Numeric(14, 4))
    bid_value_foreign_currency: Mapped[Optional[Decimal]] = mapped_column(Numeric(14, 2))
    bid_value_inr: Mapped[Optional[Decimal]] = mapped_column(Numeric(14, 2))
    technical_evaluation_status: Mapped[Optional[str]] = mapped_column(String(80))
    commercial_evaluation_status: Mapped[Optional[str]] = mapped_column(String(80))
    approval_date: Mapped[Optional[date]] = mapped_column(Date)
    contract_no: Mapped[Optional[str]] = mapped_column(String(120), index=True)
    status: Mapped[str] = mapped_column(String(80), default="In Tendering Process", nullable=False)


class LPCProcurementCase(Base, TimestampMixin):
    __tablename__ = "lpc_procurement_cases"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    procurement_case_id: Mapped[int] = mapped_column(ForeignKey("procurement_cases.id"), index=True, nullable=False)
    lpc_meeting_no: Mapped[Optional[str]] = mapped_column(String(120), index=True)
    lpc_meeting_date: Mapped[Optional[date]] = mapped_column(Date)
    committee_members: Mapped[Optional[str]] = mapped_column(Text)
    quotation_count: Mapped[Optional[int]] = mapped_column(Integer)
    lowest_vendor_id: Mapped[Optional[int]] = mapped_column(Integer, index=True)
    comparative_statement_path: Mapped[Optional[str]] = mapped_column(String(255))
    recommendation_date: Mapped[Optional[date]] = mapped_column(Date)
    approval_date: Mapped[Optional[date]] = mapped_column(Date)
    status: Mapped[str] = mapped_column(String(80), default="In Tendering Process", nullable=False)


class OpenMarketCase(Base, TimestampMixin):
    __tablename__ = "open_market_cases"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    procurement_case_id: Mapped[int] = mapped_column(ForeignKey("procurement_cases.id"), index=True, nullable=False)
    procurement_justification: Mapped[Optional[str]] = mapped_column(Text)
    market_survey_date: Mapped[Optional[date]] = mapped_column(Date)
    quotation_count: Mapped[Optional[int]] = mapped_column(Integer)
    selected_vendor_id: Mapped[Optional[int]] = mapped_column(Integer, index=True)
    comparative_statement_path: Mapped[Optional[str]] = mapped_column(String(255))
    approval_date: Mapped[Optional[date]] = mapped_column(Date)
    status: Mapped[str] = mapped_column(String(80), default="In Tendering Process", nullable=False)


class OtherProcurementCase(Base, TimestampMixin):
    __tablename__ = "other_procurement_cases"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    procurement_case_id: Mapped[int] = mapped_column(ForeignKey("procurement_cases.id"), index=True, nullable=False)
    other_procurement_type: Mapped[Optional[str]] = mapped_column(String(120))
    procurement_reference_no: Mapped[Optional[str]] = mapped_column(String(120), index=True)
    procurement_description: Mapped[Optional[str]] = mapped_column(Text)
    vendor_id: Mapped[Optional[int]] = mapped_column(Integer, index=True)
    approval_date: Mapped[Optional[date]] = mapped_column(Date)
    status: Mapped[str] = mapped_column(String(80), default="In Tendering Process", nullable=False)


class TenderBid(Base, TimestampMixin):
    __tablename__ = "tender_bids"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    serial_no: Mapped[Optional[int]] = mapped_column(Integer, index=True)
    tender_no: Mapped[str] = mapped_column(
        String(50), unique=True, index=True, nullable=False
    )
    requisition_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("requisitions.id"), index=True
    )
    procurement_case_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("procurement_cases.id"), index=True
    )
    title: Mapped[Optional[str]] = mapped_column(String(200))
    tender_date: Mapped[Optional[date]] = mapped_column(Date)
    tender_items: Mapped[Optional[list[dict]]] = mapped_column(JSON)
    item_name: Mapped[Optional[str]] = mapped_column(Text)
    quantity: Mapped[Optional[int]] = mapped_column(Integer)
    estimated_cost: Mapped[Optional[Decimal]] = mapped_column(Numeric(14, 2))
    budget_head: Mapped[Optional[str]] = mapped_column(String(255))
    bid_published_date: Mapped[Optional[date]] = mapped_column(Date)
    bid_closed_date: Mapped[Optional[date]] = mapped_column(Date)
    technical_bid_opening_date: Mapped[Optional[date]] = mapped_column(Date)
    product_demo_date: Mapped[Optional[date]] = mapped_column(Date)
    technical_evaluation_date: Mapped[Optional[date]] = mapped_column(Date)
    technical_qualified_bidders: Mapped[Optional[int]] = mapped_column(Integer)
    financial_bid_opening_date: Mapped[Optional[date]] = mapped_column(Date)
    l1_bidder_name: Mapped[Optional[str]] = mapped_column(String(160))
    contract_number: Mapped[Optional[str]] = mapped_column(String(80))
    contract_date: Mapped[Optional[date]] = mapped_column(Date)
    amount: Mapped[Optional[Decimal]] = mapped_column(Numeric(14, 2))
    remarks: Mapped[Optional[str]] = mapped_column(Text)
    publication_date: Mapped[Optional[date]] = mapped_column(Date)
    closing_date: Mapped[Optional[date]] = mapped_column(Date)
    bid_type: Mapped[str] = mapped_column(String(80), default="Open Tender")
    status: Mapped[ProcurementStatus] = mapped_column(
        Enum(ProcurementStatus), default=ProcurementStatus.in_progress, nullable=False
    )


class EMD(Base, TimestampMixin):
    __tablename__ = "emds"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    tender_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("tender_bids.id"), index=True
    )
    procurement_case_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("procurement_cases.id"), index=True
    )
    serial_no: Mapped[Optional[int]] = mapped_column(Integer, index=True)
    emd_unique_id: Mapped[Optional[str]] = mapped_column(String(80), unique=True, index=True)
    entry_date: Mapped[Optional[date]] = mapped_column(Date)
    tender_number: Mapped[Optional[str]] = mapped_column(String(80), index=True)
    tender_date: Mapped[Optional[date]] = mapped_column(Date)
    item_name: Mapped[Optional[str]] = mapped_column(Text)
    bidder_name: Mapped[str] = mapped_column(String(160), nullable=False)
    emd_form: Mapped[Optional[str]] = mapped_column(String(80))
    emd_no: Mapped[Optional[str]] = mapped_column(String(80))
    emd_date: Mapped[Optional[date]] = mapped_column(Date)
    amount: Mapped[Optional[Decimal]] = mapped_column(Numeric(14, 2))
    issuing_branch: Mapped[Optional[str]] = mapped_column(String(160))
    bank_confirmation_date: Mapped[Optional[date]] = mapped_column(Date)
    expiry_date: Mapped[Optional[date]] = mapped_column(Date)
    returned_date: Mapped[Optional[date]] = mapped_column(Date)
    return_details: Mapped[Optional[str]] = mapped_column(Text)
    remarks: Mapped[Optional[str]] = mapped_column(Text)
    instrument_no: Mapped[Optional[str]] = mapped_column(String(80))
    received_date: Mapped[Optional[date]] = mapped_column(Date)
    refund_status: Mapped[str] = mapped_column(String(80), default="Pending")


class SampleRegister(Base, TimestampMixin):
    __tablename__ = "sample_registers"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    serial_no: Mapped[Optional[int]] = mapped_column(Integer, index=True)
    tender_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("tender_bids.id"), index=True
    )
    procurement_case_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("procurement_cases.id"), index=True
    )
    tender_number: Mapped[Optional[str]] = mapped_column(String(80), index=True)
    tender_date: Mapped[Optional[date]] = mapped_column(Date)
    sample_items: Mapped[Optional[list[dict]]] = mapped_column(JSON)
    item_name: Mapped[Optional[str]] = mapped_column(Text)
    quantity: Mapped[Optional[int]] = mapped_column(Integer)
    demonstration_date: Mapped[Optional[date]] = mapped_column(Date)
    bidder_name: Mapped[str] = mapped_column(String(160), nullable=False)
    make: Mapped[Optional[str]] = mapped_column(String(120))
    model: Mapped[Optional[str]] = mapped_column(String(120))
    make_model: Mapped[Optional[str]] = mapped_column(Text)
    receipt_date: Mapped[Optional[date]] = mapped_column(Date)
    recipient_name: Mapped[Optional[str]] = mapped_column(String(160))
    return_date: Mapped[Optional[date]] = mapped_column(Date)
    remarks: Mapped[Optional[str]] = mapped_column(Text)
    sample_description: Mapped[Optional[str]] = mapped_column(Text)
    received_date: Mapped[Optional[date]] = mapped_column(Date)
    evaluation_status: Mapped[ProcurementStatus] = mapped_column(
        Enum(ProcurementStatus), default=ProcurementStatus.submitted, nullable=False
    )


class TechnicalEvaluation(Base, TimestampMixin):
    __tablename__ = "technical_evaluations"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    tender_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("tender_bids.id"), index=True
    )
    procurement_case_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("procurement_cases.id"), index=True
    )
    bidder_name: Mapped[str] = mapped_column(String(160), nullable=False)
    evaluator: Mapped[str] = mapped_column(String(120), nullable=False)
    score: Mapped[int] = mapped_column(Integer, default=0)
    result: Mapped[str] = mapped_column(String(80), default="Pending")
    remarks: Mapped[Optional[str]] = mapped_column(Text)


class FinancialEvaluation(Base, TimestampMixin):
    __tablename__ = "financial_evaluations"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    tender_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("tender_bids.id"), index=True
    )
    procurement_case_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("procurement_cases.id"), index=True
    )
    bidder_name: Mapped[str] = mapped_column(String(160), nullable=False)
    quoted_amount: Mapped[Decimal] = mapped_column(Numeric(14, 2), nullable=False)
    negotiated_amount: Mapped[Optional[Decimal]] = mapped_column(Numeric(14, 2))
    rank: Mapped[Optional[int]] = mapped_column(Integer)
    result: Mapped[str] = mapped_column(String(80), default="Pending")


class PurchaseOrder(Base, TimestampMixin):
    __tablename__ = "purchase_orders"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    po_no: Mapped[str] = mapped_column(
        String(50), unique=True, index=True, nullable=False
    )
    tender_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("tender_bids.id"), index=True
    )
    procurement_case_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("procurement_cases.id"), index=True
    )
    procurement_mode: Mapped[Optional[str]] = mapped_column(String(120))
    procurement_mode_other: Mapped[Optional[str]] = mapped_column(String(255))
    tender_reference_no: Mapped[Optional[str]] = mapped_column(String(80))
    tender_reference_date: Mapped[Optional[date]] = mapped_column(Date)
    estimated_cost: Mapped[Optional[Decimal]] = mapped_column(Numeric(14, 2))
    process_initiate_date: Mapped[Optional[date]] = mapped_column(Date)
    budget_head: Mapped[Optional[str]] = mapped_column(String(255))
    vendor_name: Mapped[str] = mapped_column(String(160), nullable=False)
    item_description: Mapped[Optional[str]] = mapped_column(Text)
    quantity: Mapped[Optional[int]] = mapped_column(Integer)
    unit: Mapped[Optional[str]] = mapped_column(String(80))
    po_date: Mapped[Optional[date]] = mapped_column(Date)
    po_value: Mapped[Optional[Decimal]] = mapped_column(Numeric(14, 2))
    delivery_due_date: Mapped[Optional[date]] = mapped_column(Date)
    status: Mapped[ProcurementStatus] = mapped_column(
        Enum(ProcurementStatus), default=ProcurementStatus.approved, nullable=False
    )


class PerformanceSecurity(Base, TimestampMixin):
    __tablename__ = "performance_securities"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    po_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("purchase_orders.id"), index=True
    )
    procurement_case_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("procurement_cases.id"), index=True
    )
    entry_date: Mapped[Optional[date]] = mapped_column(Date)
    contract_number: Mapped[Optional[str]] = mapped_column(String(80), index=True)
    contract_date: Mapped[Optional[date]] = mapped_column(Date)
    item_name: Mapped[Optional[str]] = mapped_column(Text)
    quantity: Mapped[Optional[int]] = mapped_column(Integer)
    vendor_name: Mapped[Optional[str]] = mapped_column(String(160))
    bidder_name: Mapped[Optional[str]] = mapped_column(String(160))
    security_form: Mapped[Optional[str]] = mapped_column(String(80))
    security_number: Mapped[Optional[str]] = mapped_column(String(80))
    security_date: Mapped[Optional[date]] = mapped_column(Date)
    amount: Mapped[Optional[Decimal]] = mapped_column(Numeric(14, 2))
    issuing_branch: Mapped[Optional[str]] = mapped_column(String(160))
    bank_confirmation_date: Mapped[Optional[date]] = mapped_column(Date)
    expiry_date: Mapped[Optional[date]] = mapped_column(Date)
    warranty_period: Mapped[Optional[str]] = mapped_column(String(120))
    return_date: Mapped[Optional[date]] = mapped_column(Date)
    return_details: Mapped[Optional[str]] = mapped_column(Text)
    remarks: Mapped[Optional[str]] = mapped_column(Text)
    instrument_no: Mapped[Optional[str]] = mapped_column(String(80))
    valid_until: Mapped[Optional[date]] = mapped_column(Date)
    release_status: Mapped[str] = mapped_column(String(80), default="Held")


class DeliveryAcceptance(Base, TimestampMixin):
    __tablename__ = "delivery_acceptances"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    po_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("purchase_orders.id"), index=True
    )
    procurement_case_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("procurement_cases.id"), index=True
    )
    delivery_date: Mapped[Optional[date]] = mapped_column(Date)
    accepted_quantity: Mapped[int] = mapped_column(Integer, default=0)
    acceptance_status: Mapped[ProcurementStatus] = mapped_column(
        Enum(ProcurementStatus), default=ProcurementStatus.submitted, nullable=False
    )
    remarks: Mapped[Optional[str]] = mapped_column(Text)


class PaymentTracking(Base, TimestampMixin):
    __tablename__ = "payment_tracking"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    po_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("purchase_orders.id"), index=True
    )
    procurement_case_id: Mapped[Optional[int]] = mapped_column(
        ForeignKey("procurement_cases.id"), index=True
    )
    invoice_no: Mapped[str] = mapped_column(String(80), nullable=False)
    invoice_date: Mapped[Optional[date]] = mapped_column(Date)
    invoice_amount: Mapped[Decimal] = mapped_column(Numeric(14, 2), nullable=False)
    paid_amount: Mapped[Decimal] = mapped_column(Numeric(14, 2), default=0)
    payment_status: Mapped[str] = mapped_column(String(80), default="Pending")