src/app/models/finance.py

"""
Finance Models - Project Financial Management

Models:
1. ProjectFinance - Centralized transaction tracking (inflows & outflows)
2. PaymentLog - Payment gateway audit trail (v2 automation)

Features:
- V1: Manual payment tracking (users pay on phones, record in system)
- V2: Automated payment gateway integration (M-Pesa, bank APIs, Stripe)
- Polymorphic entity support (links to payroll, expenses, invoices, etc.)
- Approval workflow (pending → approved → paid)
- Optimistic locking for concurrent updates
"""
from sqlalchemy import Column, String, Boolean, Integer, Text, Date, DateTime, Numeric, ForeignKey, CheckConstraint, Index
from sqlalchemy.dialects.postgresql import UUID, JSONB, ENUM
from sqlalchemy.orm import relationship
from datetime import datetime
from decimal import Decimal
from app.models.base import BaseModel
from app.models.enums import TransactionType, FinancialTransactionStatus, FinancialEntityType


class ProjectFinance(BaseModel):
    """
    Project Finance - Centralized financial transaction tracking
    
    PURPOSE: Track all money IN and OUT of projects
    
    INFLOWS:
    - Project funding from finance department
    - Client payments
    - Refunds
    
    OUTFLOWS:
    - Payroll (links to user_payroll)
    - Ticket expenses (links to ticket_expenses)
    - Inventory purchases (links to project_inventory)
    - Contractor invoices (links to contractor_invoices)
    - Advance payments
    - Petty cash
    
    V1 (Manual): PM logs transaction → Approver approves → PM pays manually → PM marks paid
    V2 (Automated): PM logs transaction → Approver approves → System calls payment gateway → Webhook confirms
    
    POLYMORPHIC DESIGN:
    - entity_type: What kind of financial entity (payroll, expense, invoice, etc.)
    - entity_id: UUID reference to that entity
    - This allows one table to track ALL financial transactions
    """
    __tablename__ = "project_finance"
    
    # Relationships
    project_id = Column(UUID(as_uuid=True), ForeignKey("projects.id", ondelete="CASCADE"), nullable=False)
    
    # Transaction Classification
    transaction_type = Column(ENUM(TransactionType, name="transactiontype", create_type=False, values_callable=lambda x: [e.value for e in x]), nullable=False)
    entity_type = Column(ENUM(FinancialEntityType, name="financialentitytype", create_type=False, values_callable=lambda x: [e.value for e in x]), nullable=False)
    entity_id = Column(UUID(as_uuid=True))  # Polymorphic reference
    
    # Transaction Details
    transaction_date = Column(Date, nullable=False)
    category = Column(Text, nullable=False)  # 'project_funding', 'payroll', 'equipment', 'transport', etc.
    description = Column(Text, nullable=False)
    reference_number = Column(Text)  # Internal reference (voucher number, etc.)
    
    # Amount
    amount = Column(Numeric(12, 2), nullable=False)
    currency = Column(Text, default="KES")
    
    # Parties Involved
    paid_to_user_id = Column(UUID(as_uuid=True), ForeignKey("users.id", ondelete="SET NULL"))  # For outflows
    paid_to_organization = Column(Text)  # For outflows to external parties
    received_from_user_id = Column(UUID(as_uuid=True), ForeignKey("users.id", ondelete="SET NULL"))  # For inflows
    received_from_organization = Column(Text)  # For inflows from external parties
    
    # Status & Workflow
    status = Column(ENUM(FinancialTransactionStatus, name="financialtransactionstatus", create_type=False, values_callable=lambda x: [e.value for e in x]), default="pending")
    
    # Approval Workflow
    requires_approval = Column(Boolean, default=True)
    approved_by_user_id = Column(UUID(as_uuid=True), ForeignKey("users.id", ondelete="SET NULL"))
    approved_at = Column(DateTime(timezone=True))
    approval_notes = Column(Text)
    
    # Payment Tracking (Manual - V1)
    paid_by_user_id = Column(UUID(as_uuid=True), ForeignKey("users.id", ondelete="SET NULL"))  # Who physically paid
    paid_at = Column(DateTime(timezone=True))
    payment_method = Column(Text)  # 'cash', 'mobile_money', 'bank_transfer', 'cheque'
    payment_reference = Column(Text)  # M-Pesa code, cheque number, bank reference
    
    # Payment Gateway Integration (Automated - V2)
    payment_gateway_provider = Column(Text)  # 'mpesa', 'bank_api', 'stripe', 'paypal', 'flutterwave'
    payment_gateway_transaction_id = Column(Text)  # External transaction ID
    payment_gateway_response = Column(JSONB)  # Full API response
    payment_initiated_at = Column(DateTime(timezone=True))
    payment_completed_at = Column(DateTime(timezone=True))
    
    # Rejection/Cancellation
    rejected_by_user_id = Column(UUID(as_uuid=True), ForeignKey("users.id", ondelete="SET NULL"))
    rejected_at = Column(DateTime(timezone=True))
    rejection_reason = Column(Text)
    cancelled_by_user_id = Column(UUID(as_uuid=True), ForeignKey("users.id", ondelete="SET NULL"))
    cancelled_at = Column(DateTime(timezone=True))
    cancellation_reason = Column(Text)
    
    # Supporting Documents
    receipt_document_id = Column(UUID(as_uuid=True), ForeignKey("documents.id", ondelete="SET NULL"))
    supporting_documents = Column(JSONB, default=[])  # Array of document IDs
    
    # Metadata
    notes = Column(Text)
    additional_metadata = Column(JSONB, default={})
    
    # Optimistic Locking
    version = Column(Integer, default=1, nullable=False)
    
    # Audit
    created_by_user_id = Column(UUID(as_uuid=True), ForeignKey("users.id", ondelete="RESTRICT"), nullable=False)
    
    # Relationships
    project = relationship("Project", back_populates="finance_transactions")
    paid_to_user = relationship("User", foreign_keys=[paid_to_user_id])
    received_from_user = relationship("User", foreign_keys=[received_from_user_id])
    approved_by = relationship("User", foreign_keys=[approved_by_user_id])
    paid_by = relationship("User", foreign_keys=[paid_by_user_id])
    rejected_by = relationship("User", foreign_keys=[rejected_by_user_id])
    cancelled_by = relationship("User", foreign_keys=[cancelled_by_user_id])
    created_by = relationship("User", foreign_keys=[created_by_user_id])
    receipt_document = relationship("Document", foreign_keys=[receipt_document_id])
    payment_logs = relationship("PaymentLog", back_populates="finance_transaction", lazy="dynamic")
    
    # Table constraints
    __table_args__ = (
        CheckConstraint("amount > 0", name="chk_positive_amount"),
        CheckConstraint(
            "(entity_type = 'project' AND entity_id IS NULL) OR (entity_type != 'project' AND entity_id IS NOT NULL)",
            name="chk_valid_entity"
        ),
        CheckConstraint(
            "(paid_at IS NULL OR paid_at >= transaction_date::timestamp) AND "
            "(approved_at IS NULL OR approved_at >= transaction_date::timestamp) AND "
            "(payment_completed_at IS NULL OR payment_initiated_at IS NULL OR payment_completed_at >= payment_initiated_at)",
            name="chk_payment_dates"
        ),
        Index("idx_project_finance_project", "project_id", "transaction_date"),
        Index("idx_project_finance_type", "transaction_type", "status", "transaction_date"),
        Index("idx_project_finance_status", "status", "transaction_date"),
        Index("idx_project_finance_entity", "entity_type", "entity_id", postgresql_where=(Column("entity_id").isnot(None))),
        Index("idx_project_finance_category", "category", "project_id", "transaction_date"),
        Index("idx_project_finance_paid_to", "paid_to_user_id", "status", "paid_at", postgresql_where=(Column("paid_to_user_id").isnot(None))),
        Index("idx_project_finance_approval", "status", "requires_approval", "approved_at", postgresql_where=(Column("requires_approval") == True)),
        Index("idx_project_finance_pending", "project_id", "status", "transaction_date", postgresql_where=(Column("status") == "pending")),
        Index("idx_project_finance_gateway", "payment_gateway_provider", "payment_gateway_transaction_id", postgresql_where=(Column("payment_gateway_provider").isnot(None))),
        Index("idx_project_finance_version", "id", "version"),
    )
    
    @property
    def is_inflow(self) -> bool:
        """Check if transaction is an inflow"""
        return self.transaction_type == TransactionType.INFLOW.value
    
    @property
    def is_outflow(self) -> bool:
        """Check if transaction is an outflow"""
        return self.transaction_type == TransactionType.OUTFLOW.value
    
    @property
    def is_pending_approval(self) -> bool:
        """Check if transaction needs approval"""
        return self.requires_approval and self.status == FinancialTransactionStatus.PENDING.value
    
    @property
    def is_paid(self) -> bool:
        """Check if transaction is paid"""
        return self.status == FinancialTransactionStatus.PAID.value
    
    @property
    def uses_payment_gateway(self) -> bool:
        """Check if transaction uses automated payment gateway"""
        return self.payment_gateway_provider is not None
    
    def can_approve(self) -> bool:
        """Check if transaction can be approved"""
        return self.status == FinancialTransactionStatus.PENDING.value and self.requires_approval
    
    def can_pay(self) -> bool:
        """Check if transaction can be marked as paid"""
        return self.status in [FinancialTransactionStatus.PENDING.value, FinancialTransactionStatus.APPROVED.value]
    
    def can_cancel(self) -> bool:
        """Check if transaction can be cancelled"""
        return self.status not in [FinancialTransactionStatus.PAID.value, FinancialTransactionStatus.CANCELLED.value]
    
    def __repr__(self):
        return f"<ProjectFinance(id={self.id}, type={self.transaction_type}, amount={self.amount}, status={self.status})>"


class PaymentLog(BaseModel):
    """
    Payment Log - Audit trail for payment gateway interactions
    
    PURPOSE: Track all automated payment gateway API calls (V2 feature)
    
    USE CASES:
    - M-Pesa STK Push (customer pays)
    - M-Pesa B2C (system pays worker)
    - Bank transfer APIs
    - Stripe/PayPal payments
    - Failed payment debugging
    - Payment reconciliation
    
    RELATIONSHIP:
    - One ProjectFinance can have many PaymentLogs (retries, webhooks, callbacks)
    - PaymentLogs provide technical audit trail
    - ProjectFinance provides business view
    
    FLOW:
    1. System initiates payment → PaymentLog (event_type='initiated')
    2. Gateway processes → PaymentLog (event_type='processing')
    3. Webhook received → PaymentLog (event_type='webhook_received')
    4. Payment completes → PaymentLog (event_type='completed', event_status='success')
    5. ProjectFinance updated → status='paid'
    """
    __tablename__ = "payment_logs"
    
    # Link to ProjectFinance
    project_finance_id = Column(UUID(as_uuid=True), ForeignKey("project_finance.id", ondelete="CASCADE"))
    
    # Payment Gateway Details
    gateway_provider = Column(Text, nullable=False)  # 'mpesa', 'bank_api', 'stripe', 'flutterwave'
    gateway_transaction_id = Column(Text)  # External transaction ID
    gateway_reference = Column(Text)  # Gateway's internal reference (M-Pesa conversation ID)
    
    # Event Details
    event_type = Column(Text, nullable=False)  # 'initiated', 'processing', 'completed', 'failed', 'refunded', 'webhook_received'
    event_status = Column(Text, nullable=False)  # 'success', 'failure', 'pending', 'timeout'
    
    # Request/Response Data (full API payloads for debugging)
    request_payload = Column(JSONB)  # What we sent to gateway
    response_payload = Column(JSONB)  # What gateway returned
    error_code = Column(Text)
    error_message = Column(Text)
    
    # Amounts (for reconciliation)
    amount_requested = Column(Numeric(12, 2))
    amount_processed = Column(Numeric(12, 2))  # Actual amount processed by gateway
    currency = Column(Text, default="KES")
    gateway_fee = Column(Numeric(12, 2))  # Transaction fee charged by gateway
    
    # Recipient/Sender Details (for audit)
    recipient_phone = Column(Text)  # M-Pesa phone number
    recipient_account = Column(Text)  # Bank account number
    recipient_name = Column(Text)
    sender_phone = Column(Text)  # For inbound payments
    sender_account = Column(Text)
    sender_name = Column(Text)
    
    # Transaction Direction
    direction = Column(Text, nullable=False)  # 'outbound' (system paying), 'inbound' (customer paying)
    
    # Timing
    initiated_at = Column(DateTime(timezone=True))
    completed_at = Column(DateTime(timezone=True))
    
    # Retry Tracking
    retry_count = Column(Integer, default=0)
    parent_log_id = Column(UUID(as_uuid=True), ForeignKey("payment_logs.id", ondelete="SET NULL"))
    
    # Metadata
    ip_address = Column(Text)  # IP address of request
    user_agent = Column(Text)
    device_info = Column(JSONB)  # Device information
    initiated_by_user_id = Column(UUID(as_uuid=True), ForeignKey("users.id", ondelete="SET NULL"))
    
    # Additional context
    notes = Column(Text)
    additional_metadata = Column(JSONB, default={})
    
    # Relationships
    finance_transaction = relationship("ProjectFinance", back_populates="payment_logs")
    initiated_by = relationship("User", foreign_keys=[initiated_by_user_id])
    parent_log = relationship("PaymentLog", remote_side="PaymentLog.id", foreign_keys=[parent_log_id])
    
    # Table constraints
    __table_args__ = (
        Index("idx_payment_logs_finance", "project_finance_id", "created_at"),
        Index("idx_payment_logs_gateway", "gateway_provider", "gateway_transaction_id"),
        Index("idx_payment_logs_gateway_ref", "gateway_provider", "gateway_reference"),
        Index("idx_payment_logs_status", "event_status", "event_type", "created_at"),
        Index("idx_payment_logs_direction", "direction", "event_status", "created_at"),
        Index("idx_payment_logs_recipient", "recipient_phone", "created_at", postgresql_where=(Column("recipient_phone").isnot(None))),
        Index("idx_payment_logs_sender", "sender_phone", "created_at", postgresql_where=(Column("sender_phone").isnot(None))),
        Index("idx_payment_logs_failed", "gateway_provider", "event_status", "created_at", postgresql_where=(Column("event_status") == "failure")),
        Index("idx_payment_logs_retry", "parent_log_id", "retry_count", postgresql_where=(Column("parent_log_id").isnot(None))),
        Index("idx_payment_logs_created", "created_at"),
    )
    
    @property
    def is_success(self) -> bool:
        """Check if payment was successful"""
        return self.event_status == "success"
    
    @property
    def is_failed(self) -> bool:
        """Check if payment failed"""
        return self.event_status == "failure"
    
    @property
    def is_retry(self) -> bool:
        """Check if this is a retry attempt"""
        return self.parent_log_id is not None
    
    def __repr__(self):
        return f"<PaymentLog(id={self.id}, provider={self.gateway_provider}, status={self.event_status}, event={self.event_type})>"