import uuid
from datetime import datetime, date
from sqlalchemy import (
    String, DateTime, Date, Integer, Text, ForeignKey, Numeric,
    Boolean, UniqueConstraint, func,
)
from sqlalchemy.orm import Mapped, mapped_column, relationship
from app.database import Base


class AccountingPeriod(Base):
    __tablename__ = "accounting_periods"
    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    period_code: Mapped[str] = mapped_column(String(7), unique=True, index=True)  # YYYY-MM
    start_date: Mapped[date] = mapped_column(Date)
    end_date: Mapped[date] = mapped_column(Date)
    status: Mapped[str] = mapped_column(String(10), default="open")  # open/closed
    closed_by: Mapped[str | None] = mapped_column(String(36), nullable=True)
    closed_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime, server_default=func.now())


class JournalLine(Base):
    __tablename__ = "journal_lines"
    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    entry_id: Mapped[str] = mapped_column(ForeignKey("journal_entries.id"), index=True)
    account_id: Mapped[str] = mapped_column(ForeignKey("accounts.id"), index=True)
    debit: Mapped[float] = mapped_column(Numeric(18, 2), default=0)
    credit: Mapped[float] = mapped_column(Numeric(18, 2), default=0)
    branch_id: Mapped[str | None] = mapped_column(String(36), nullable=True, index=True)
    production_batch_id: Mapped[str | None] = mapped_column(ForeignKey("production_batches.id"), nullable=True)
    passport_id: Mapped[str | None] = mapped_column(ForeignKey("plant_passports.id"), nullable=True)
    cost_center: Mapped[str | None] = mapped_column(String(50), nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime, server_default=func.now())
    entry = relationship("JournalEntry", back_populates="lines")


class AccountBalance(Base):
    """Materialized running balance per (account, period, branch). branch_id is a free
    string ("" = not branch-sliced) so NULLs cannot break the unique key."""
    __tablename__ = "account_balances"
    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    account_id: Mapped[str] = mapped_column(ForeignKey("accounts.id"), index=True)
    period_id: Mapped[str] = mapped_column(ForeignKey("accounting_periods.id"), index=True)
    branch_id: Mapped[str] = mapped_column(String(36), default="", index=True)
    opening_balance: Mapped[float] = mapped_column(Numeric(18, 2), default=0)
    total_debit: Mapped[float] = mapped_column(Numeric(18, 2), default=0)
    total_credit: Mapped[float] = mapped_column(Numeric(18, 2), default=0)
    closing_balance: Mapped[float] = mapped_column(Numeric(18, 2), default=0)
    created_at: Mapped[datetime] = mapped_column(DateTime, server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(DateTime, server_default=func.now(), onupdate=func.now())

    __table_args__ = (
        UniqueConstraint("account_id", "period_id", "branch_id",
                         name="uq_account_balance"),
    )


class PaymentAllocation(Base):
    __tablename__ = "payment_allocations"
    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    payment_id: Mapped[str] = mapped_column(ForeignKey("payments.id"), index=True)
    document_type: Mapped[str] = mapped_column(String(30))  # sale / purchase_order
    document_id: Mapped[str] = mapped_column(String(36))
    amount: Mapped[float] = mapped_column(Numeric(18, 2))
    created_at: Mapped[datetime] = mapped_column(DateTime, server_default=func.now())


class CashbookMovement(Base):
    __tablename__ = "cashbook_movements"
    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    movement_number: Mapped[str] = mapped_column(String(30), unique=True)
    type: Mapped[str] = mapped_column(String(20))  # receipt/payment/deposit/withdrawal/transfer_in/transfer_out/adjustment
    account_id: Mapped[str] = mapped_column(ForeignKey("accounts.id"), index=True)
    from_account_id: Mapped[str | None] = mapped_column(String(36), nullable=True)
    to_account_id: Mapped[str | None] = mapped_column(String(36), nullable=True)
    amount: Mapped[float] = mapped_column(Numeric(18, 2))
    movement_date: Mapped[date] = mapped_column(Date)
    branch_id: Mapped[str | None] = mapped_column(String(36), nullable=True)
    journal_entry_id: Mapped[str | None] = mapped_column(ForeignKey("journal_entries.id"), nullable=True)
    reference_type: Mapped[str | None] = mapped_column(String(30), nullable=True)
    reference_id: Mapped[str | None] = mapped_column(String(36), nullable=True)
    notes: Mapped[str | None] = mapped_column(Text, nullable=True)
    created_by: Mapped[str | None] = mapped_column(String(36), nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime, server_default=func.now())


class BankReconciliation(Base):
    __tablename__ = "bank_reconciliations"
    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    account_id: Mapped[str] = mapped_column(ForeignKey("accounts.id"), index=True)
    period_id: Mapped[str | None] = mapped_column(ForeignKey("accounting_periods.id"), nullable=True)
    statement_balance: Mapped[float] = mapped_column(Numeric(18, 2), default=0)
    book_balance: Mapped[float] = mapped_column(Numeric(18, 2), default=0)
    difference: Mapped[float] = mapped_column(Numeric(18, 2), default=0)
    status: Mapped[str] = mapped_column(String(20), default="pending")  # pending/matched/adjusted
    lines: Mapped[str | None] = mapped_column(Text, nullable=True)  # JSON snapshot
    reconciled_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    reconciled_by: Mapped[str | None] = mapped_column(String(36), nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime, server_default=func.now())


class Loan(Base):
    __tablename__ = "loans"
    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    loan_number: Mapped[str] = mapped_column(String(30), unique=True)
    lender_name: Mapped[str] = mapped_column(String(100))
    lender_type: Mapped[str] = mapped_column(String(20), default="bank")  # bank/private
    principal: Mapped[float] = mapped_column(Numeric(18, 2))
    interest_rate_nominal: Mapped[float] = mapped_column(Numeric(10, 4), default=0)
    interest_rate_period: Mapped[str] = mapped_column(String(10), default="year")  # year/month
    start_date: Mapped[date] = mapped_column(Date)
    tenure_months: Mapped[int] = mapped_column(Integer, default=12)
    repayment_frequency: Mapped[str] = mapped_column(String(20), default="monthly")
    installment_amount: Mapped[float] = mapped_column(Numeric(18, 2), default=0)
    total_interest: Mapped[float] = mapped_column(Numeric(18, 2), default=0)
    status: Mapped[str] = mapped_column(String(20), default="draft")  # draft/active/closed
    branch_id: Mapped[str | None] = mapped_column(String(36), nullable=True)
    account_id: Mapped[str | None] = mapped_column(String(36), nullable=True)  # receiving cash/bank
    liability_account_id: Mapped[str | None] = mapped_column(String(36), nullable=True)  # 2030
    interest_expense_account_id: Mapped[str | None] = mapped_column(String(36), nullable=True)  # 6150
    notes: Mapped[str | None] = mapped_column(Text, nullable=True)
    created_by: Mapped[str] = mapped_column(ForeignKey("users.id"))
    created_at: Mapped[datetime] = mapped_column(DateTime, server_default=func.now())


class LoanSchedule(Base):
    __tablename__ = "loan_schedules"
    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    loan_id: Mapped[str] = mapped_column(ForeignKey("loans.id"), index=True)
    installment_no: Mapped[int] = mapped_column(Integer)
    due_date: Mapped[date] = mapped_column(Date)
    principal_due: Mapped[float] = mapped_column(Numeric(18, 2), default=0)
    interest_due: Mapped[float] = mapped_column(Numeric(18, 2), default=0)
    total_due: Mapped[float] = mapped_column(Numeric(18, 2), default=0)
    principal_paid: Mapped[float] = mapped_column(Numeric(18, 2), default=0)
    interest_paid: Mapped[float] = mapped_column(Numeric(18, 2), default=0)
    paid_date: Mapped[date | None] = mapped_column(Date, nullable=True)
    status: Mapped[str] = mapped_column(String(20), default="pending")  # pending/paid/overdue
    payment_id: Mapped[str | None] = mapped_column(String(36), nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime, server_default=func.now())


class ProductionCostEntry(Base):
    __tablename__ = "production_cost_entries"
    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    batch_id: Mapped[str] = mapped_column(ForeignKey("production_batches.id"), index=True)
    cost_type: Mapped[str] = mapped_column(String(30))  # seed/media/fertilizer/treatment/labor/water/electricity/overhead/mortality/other
    amount: Mapped[float] = mapped_column(Numeric(18, 2))
    quantity: Mapped[float | None] = mapped_column(Numeric(12, 2), nullable=True)
    unit: Mapped[str | None] = mapped_column(String(20), nullable=True)
    reference_type: Mapped[str | None] = mapped_column(String(30), nullable=True)
    reference_id: Mapped[str | None] = mapped_column(String(36), nullable=True)
    journal_line_id: Mapped[str | None] = mapped_column(String(36), nullable=True)
    allocated_to_qty: Mapped[float | None] = mapped_column(Numeric(12, 2), nullable=True)
    cost_center: Mapped[str | None] = mapped_column(String(50), nullable=True)
    notes: Mapped[str | None] = mapped_column(Text, nullable=True)
    created_by: Mapped[str | None] = mapped_column(String(36), nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime, server_default=func.now())


class CostCenter(Base):
    __tablename__ = "cost_centers"
    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    code: Mapped[str] = mapped_column(String(30), unique=True)
    name: Mapped[str] = mapped_column(String(100))
    branch_id: Mapped[str | None] = mapped_column(String(36), nullable=True)
    description: Mapped[str | None] = mapped_column(Text, nullable=True)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True)
    created_at: Mapped[datetime] = mapped_column(DateTime, server_default=func.now())


class InventoryLot(Base):
    """FIFO cost lot. A stock-in creates/recharges a lot; a stock-out drains oldest lots
    first and derives COGS from the consumed lot unit costs."""
    __tablename__ = "inventory_lots"
    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    item_id: Mapped[str] = mapped_column(ForeignKey("inventory.id"), index=True)
    branch_id: Mapped[str | None] = mapped_column(String(36), nullable=True, index=True)
    batch_id: Mapped[str | None] = mapped_column(ForeignKey("production_batches.id"), nullable=True, index=True)
    lot_number: Mapped[str] = mapped_column(String(40), index=True)
    received_date: Mapped[date] = mapped_column(Date, index=True)
    received_qty: Mapped[float] = mapped_column(Numeric(12, 2))
    remaining_qty: Mapped[float] = mapped_column(Numeric(12, 2))
    unit_cost: Mapped[float] = mapped_column(Numeric(18, 2))
    is_active: Mapped[bool] = mapped_column(Boolean, default=True)
    created_at: Mapped[datetime] = mapped_column(DateTime, server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(DateTime, server_default=func.now(), onupdate=func.now())