from __future__ import annotations

from decimal import Decimal

from sqlalchemy import Boolean, ForeignKey, Integer, Numeric, String, Text, UniqueConstraint
from sqlalchemy.orm import Mapped, mapped_column

from app.db.session import Base
from app.models.procurement import TimestampMixin


class BudgetHead(Base, TimestampMixin):
    __tablename__ = "budget_heads"
    __table_args__ = (
        UniqueConstraint("head_code", name="uq_budget_heads_head_code"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    head_code: Mapped[str] = mapped_column(String(40), nullable=False, index=True)
    display_name: Mapped[str] = mapped_column(String(255), nullable=False, index=True)
    display_name_en: Mapped[str | None] = mapped_column(String(255), index=True)
    display_name_or: Mapped[str | None] = mapped_column(String(255), index=True)
    category_code: Mapped[str] = mapped_column(String(40), nullable=False, index=True)
    category_name: Mapped[str] = mapped_column(String(160), nullable=False)
    category_name_en: Mapped[str | None] = mapped_column(String(160))
    category_name_or: Mapped[str | None] = mapped_column(String(160))
    fund_source: Mapped[str] = mapped_column(String(160), nullable=False)
    expenditure_head: Mapped[str] = mapped_column(String(160), nullable=False, index=True)
    expenditure_head_en: Mapped[str | None] = mapped_column(String(160), index=True)
    expenditure_head_or: Mapped[str | None] = mapped_column(String(160), index=True)
    item_component: Mapped[str] = mapped_column(String(160), nullable=False)
    item_component_en: Mapped[str | None] = mapped_column(String(160))
    item_component_or: Mapped[str | None] = mapped_column(String(160))
    quantity: Mapped[str | None] = mapped_column(String(80))
    unit: Mapped[str | None] = mapped_column(String(80))
    purpose_description: Mapped[str | None] = mapped_column(Text)
    remarks: Mapped[str | None] = mapped_column(Text)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)


class ReportBudgetAllocation(Base, TimestampMixin):
    """Financial-year allocation used by budget and programme reports."""

    __tablename__ = "report_budget_allocations"
    __table_args__ = (
        UniqueConstraint("financial_year", "expenditure_group", "head_code", name="uq_report_budget_allocation"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    financial_year: Mapped[str] = mapped_column(String(9), nullable=False, index=True)
    expenditure_group: Mapped[str] = mapped_column(String(40), nullable=False, index=True)
    head_code: Mapped[str] = mapped_column(String(40), nullable=False, index=True)
    head_name: Mapped[str] = mapped_column(String(180), nullable=False)
    allocation_amount: Mapped[Decimal] = mapped_column(Numeric(16, 2), nullable=False, default=0)
    remarks: Mapped[str | None] = mapped_column(Text)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)


class FinancialExpenditureHead(Base, TimestampMixin):
    """Expenditure-head master linked to a financial-year budget head."""

    __tablename__ = "financial_expenditure_heads"
    __table_args__ = (
        UniqueConstraint("budget_head_id", "head_code", name="uq_financial_expenditure_head"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    budget_head_id: Mapped[int] = mapped_column(
        ForeignKey("report_budget_allocations.id"), nullable=False, index=True
    )
    head_code: Mapped[str] = mapped_column(String(40), nullable=False, index=True)
    head_name: Mapped[str] = mapped_column(String(180), nullable=False, index=True)
    remarks: Mapped[str | None] = mapped_column(Text)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)


class OFESEstablishment(Base, TimestampMixin):
    __tablename__ = "ofes_establishments"
    __table_args__ = (
        UniqueConstraint("sr_no", name="uq_ofes_establishments_sr_no"),
    )

    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[str | None] = mapped_column(String(160), index=True)
    name_or: Mapped[str | None] = mapped_column(String(160), index=True)
    district: Mapped[str] = mapped_column(String(120), nullable=False, index=True)
    district_en: Mapped[str | None] = mapped_column(String(120), index=True)
    district_or: Mapped[str | None] = mapped_column(String(120), index=True)
    range_name: Mapped[str] = mapped_column(String(120), nullable=False, index=True)
    range_name_en: Mapped[str | None] = mapped_column(String(120), index=True)
    range_name_or: Mapped[str | None] = mapped_column(String(120), index=True)
    display_name: Mapped[str] = mapped_column(String(255), nullable=False, index=True)
    display_name_en: Mapped[str | None] = mapped_column(String(255), index=True)
    display_name_or: Mapped[str | None] = mapped_column(String(255), index=True)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)