"""Transaction model representing a brokerage transaction."""
from sqlalchemy import Column, Integer, String, DateTime, Numeric, ForeignKey, Date, Index
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func

from app.database import Base


class Transaction(Base):
    """
    Represents a single brokerage transaction.

    Attributes:
        id: Primary key
        account_id: Foreign key to account
        run_date: Date the transaction was recorded
        action: Description of the transaction action
        symbol: Trading symbol
        description: Full transaction description
        transaction_type: Type (Cash/Margin)
        exchange_quantity: Quantity in exchange currency
        exchange_currency: Exchange currency code
        currency: Transaction currency
        price: Transaction price per unit
        quantity: Number of shares/contracts
        exchange_rate: Currency exchange rate
        commission: Commission fees
        fees: Additional fees
        accrued_interest: Interest accrued
        amount: Total transaction amount
        cash_balance: Account balance after transaction
        settlement_date: Date transaction settles
        unique_hash: SHA-256 hash for deduplication
        created_at: Timestamp of record creation
        updated_at: Timestamp of last update
    """
    __tablename__ = "transactions"

    id = Column(Integer, primary_key=True, index=True)
    account_id = Column(Integer, ForeignKey("accounts.id", ondelete="CASCADE"), nullable=False, index=True)

    # Transaction details from CSV
    run_date = Column(Date, nullable=False, index=True)
    action = Column(String(500), nullable=False)
    symbol = Column(String(50), index=True)
    description = Column(String(500))
    transaction_type = Column(String(20))  # Cash, Margin

    # Quantities and currencies
    exchange_quantity = Column(Numeric(20, 8))
    exchange_currency = Column(String(10))
    currency = Column(String(10))

    # Financial details
    price = Column(Numeric(20, 8))
    quantity = Column(Numeric(20, 8))
    exchange_rate = Column(Numeric(20, 8))
    commission = Column(Numeric(20, 2))
    fees = Column(Numeric(20, 2))
    accrued_interest = Column(Numeric(20, 2))
    amount = Column(Numeric(20, 2))
    cash_balance = Column(Numeric(20, 2))

    settlement_date = Column(Date)

    # Deduplication hash
    unique_hash = Column(String(64), unique=True, nullable=False, index=True)

    # Timestamps
    created_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False)
    updated_at = Column(DateTime(timezone=True), onupdate=func.now(), server_default=func.now(), nullable=False)

    # Relationships
    account = relationship("Account", back_populates="transactions")
    position_links = relationship("PositionTransaction", back_populates="transaction", cascade="all, delete-orphan")

    # Composite index for common queries
    __table_args__ = (
        Index('idx_account_date', 'account_id', 'run_date'),
        Index('idx_account_symbol', 'account_id', 'symbol'),
    )