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


class Account(Base):
    __tablename__ = "accounts"
    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    name: Mapped[str] = mapped_column(String(100))
    # legacy column retained for backward compatibility (cash/bank/expense/income...)
    type: Mapped[str] = mapped_column(String(30), default="asset")
    # COA columns (Phase 2 additions; all nullable for backward compatibility)
    code: Mapped[str | None] = mapped_column(String(20), nullable=True, index=True)
    account_type: Mapped[str | None] = mapped_column(String(20), nullable=True)  # asset/liability/equity/revenue/expense
    normal_balance: Mapped[str | None] = mapped_column(String(1), nullable=True)  # D / C
    category: Mapped[str | None] = mapped_column(String(50), nullable=True)  # cash/bank/receivable/inventory/payable/loan/expense...
    parent_id: Mapped[str | None] = mapped_column(String(36), nullable=True, index=True)
    branch_id: Mapped[str | None] = mapped_column(String(36), nullable=True, index=True)
    is_system: Mapped[bool] = mapped_column(Boolean, default=False)
    balance: Mapped[float] = mapped_column(Float, default=0.0)  # legacy convenience snapshot
    is_active: Mapped[bool] = mapped_column(default=True)
    created_at: Mapped[datetime] = mapped_column(DateTime, server_default=func.now())


class Expense(Base):
    __tablename__ = "expenses"
    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    expense_number: Mapped[str] = mapped_column(String(30), unique=True)
    category: Mapped[str] = mapped_column(String(50))
    description: Mapped[str] = mapped_column(String(200))
    amount: Mapped[float] = mapped_column(Float)
    date: Mapped[date] = mapped_column(Date)
    payment_method: Mapped[str] = mapped_column(String(30), default="cash")
    account_id: Mapped[str | None] = mapped_column(ForeignKey("accounts.id"), nullable=True)
    branch_id: Mapped[str | None] = mapped_column(String(36), nullable=True, index=True)
    reference: Mapped[str | None] = mapped_column(String(100), nullable=True)
    notes: Mapped[str | None] = mapped_column(Text, nullable=True)
    # Phase 4 additions (all nullable for backward compatibility)
    approval_status: Mapped[str] = mapped_column(String(20), default="approved")  # pending/approved/rejected
    approved_by: Mapped[str | None] = mapped_column(String(36), nullable=True)
    approved_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    attachment_path: Mapped[str | None] = mapped_column(String(255), nullable=True)
    recurring: Mapped[bool] = mapped_column(Boolean, default=False)
    recurring_frequency: Mapped[str] = mapped_column(String(20), default="monthly")  # monthly/quarterly/yearly
    recurring_until: Mapped[date | None] = mapped_column(Date, nullable=True)
    created_by: Mapped[str] = mapped_column(ForeignKey("users.id"))
    created_at: Mapped[datetime] = mapped_column(DateTime, server_default=func.now())


class Income(Base):
    __tablename__ = "income"
    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    income_number: Mapped[str] = mapped_column(String(30), unique=True)
    category: Mapped[str] = mapped_column(String(50))
    description: Mapped[str] = mapped_column(String(200))
    amount: Mapped[float] = mapped_column(Float)
    date: Mapped[date] = mapped_column(Date)
    payment_method: Mapped[str] = mapped_column(String(30), default="cash")
    account_id: Mapped[str | None] = mapped_column(ForeignKey("accounts.id"), nullable=True)
    branch_id: Mapped[str | None] = mapped_column(String(36), nullable=True, index=True)
    reference: Mapped[str | None] = mapped_column(String(100), nullable=True)
    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 Payment(Base):
    __tablename__ = "payments"
    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    payment_number: Mapped[str] = mapped_column(String(30), unique=True)
    type: Mapped[str] = mapped_column(String(20))
    party_type: Mapped[str] = mapped_column(String(20))
    party_id: Mapped[str] = mapped_column(String(36))
    amount: Mapped[float] = mapped_column(Float)
    date: Mapped[date] = mapped_column(Date)
    method: Mapped[str] = mapped_column(String(30), default="cash")
    branch_id: Mapped[str | None] = mapped_column(String(36), nullable=True, index=True)
    reference: Mapped[str | None] = mapped_column(String(100), nullable=True)
    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 JournalEntry(Base):
    """Double-entry journal (redesigned Phase 2). The legacy table was empty; each
    formation in this repo lives at models.finance.JournalEntry. Use the PostingService
    (app/services/accounting.py) to create/post/reverse entries."""
    __tablename__ = "journal_entries"
    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    entry_number: Mapped[str] = mapped_column(String(30), unique=True)
    entry_date: Mapped[date] = mapped_column(Date, index=True)
    period_id: Mapped[str] = mapped_column(ForeignKey("accounting_periods.id"), index=True)
    branch_id: Mapped[str | None] = mapped_column(String(36), nullable=True, index=True)
    status: Mapped[str] = mapped_column(String(20), default="draft")  # draft/posted/reversed
    source: Mapped[str] = mapped_column(String(30), default="journal")
    reference_type: Mapped[str | None] = mapped_column(String(30), nullable=True)
    reference_id: Mapped[str | None] = mapped_column(String(36), nullable=True)
    description: Mapped[str] = mapped_column(Text, default="")
    is_reversal: Mapped[bool] = mapped_column(Boolean, default=False)
    reversal_of_id: Mapped[str | None] = mapped_column(String(36), nullable=True)
    created_by: Mapped[str] = mapped_column(ForeignKey("users.id"))
    posted_by: Mapped[str | None] = mapped_column(ForeignKey("users.id"), nullable=True)
    posted_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime, server_default=func.now())

    lines = relationship("JournalLine", back_populates="entry", cascade="all, delete-orphan")

    __table_args__ = (
        UniqueConstraint("source", "reference_type", "reference_id",
                         name="uq_journal_idempotency"),
    )