src/app/models/contractor_invoice.py

"""
Contractor Invoice Model - Invoices contractors send to clients for completed work
"""
from datetime import date, datetime
from decimal import Decimal
from sqlalchemy import (
    Column, String, Date, DateTime, Boolean, Integer, 
    ForeignKey, Text, CheckConstraint, Index, Numeric
)
from sqlalchemy.dialects.postgresql import UUID, JSONB
from sqlalchemy.orm import relationship
from app.models.base import BaseModel


class ContractorInvoice(BaseModel):
    """
    Contractor Invoice model - Invoices for billing clients
    
    **VERSIONING STRATEGY:**
    - Each edit creates a new version (copy-on-write)
    - previous_version_id links to previous version (version chain)
    - is_latest_version = TRUE only on current version
    - Original invoice preserved for audit trail
    
    **LINE ITEMS FORMAT (JSONB):**
    [
        {
            "id": "uuid",  # Line item ID for updates
            "type": "ticket",  # ticket|vehicle|labor|material|other
            "ticket_id": "uuid",  # Optional: if type=ticket
            "description": "Installation at Site XYZ",
            "quantity": 1,
            "unit_price": 5000.00,
            "total": 5000.00
        },
        ...
    ]
    
    Maps to 'contractor_invoices' table in docs/schema/schema.sql
    """
    __tablename__ = "contractor_invoices"
    
    # Foreign Keys
    contractor_id = Column(UUID(as_uuid=True), ForeignKey("contractors.id", ondelete="RESTRICT"), nullable=False, index=True)
    client_id = Column(UUID(as_uuid=True), ForeignKey("clients.id", ondelete="RESTRICT"), nullable=False, index=True)
    project_id = Column(UUID(as_uuid=True), ForeignKey("projects.id", ondelete="SET NULL"), nullable=True, index=True)
    
    # Invoice Details
    invoice_number = Column(String, nullable=False, unique=True, index=True)
    invoice_title = Column(String, nullable=True)
    
    # Billing Period
    billing_period_start = Column(Date, nullable=False)
    billing_period_end = Column(Date, nullable=False)
    
    # Amounts
    subtotal = Column(Numeric(12, 2), nullable=False, default=Decimal('0.00'))
    tax_rate = Column(Numeric(5, 2), default=Decimal('0.00'))  # Percentage (e.g., 16 for 16%)
    tax_amount = Column(Numeric(12, 2), default=Decimal('0.00'))
    discount_amount = Column(Numeric(12, 2), default=Decimal('0.00'))
    total_amount = Column(Numeric(12, 2), nullable=False, default=Decimal('0.00'))
    amount_paid = Column(Numeric(12, 2), default=Decimal('0.00'))
    # amount_due is GENERATED column in DB: total_amount - amount_paid
    currency = Column(String, default="KES")
    
    # Line Items (JSONB)
    line_items = Column(JSONB, default=list)
    
    # Status & Dates
    status = Column(String(50), default="draft", nullable=False, index=True)  # contractor_invoice_status enum
    issue_date = Column(Date, nullable=False)
    due_date = Column(Date, nullable=False)
    sent_date = Column(Date, nullable=True)
    paid_date = Column(Date, nullable=True)
    
    # Payment Details
    payment_method = Column(String, nullable=True)  # 'mobile_money', 'bank_transfer', 'cheque', 'cash'
    payment_reference = Column(String, nullable=True)  # Transaction ID or cheque number
    payment_notes = Column(Text, nullable=True)
    
    # Metadata
    notes = Column(Text, nullable=True)
    terms_and_conditions = Column(Text, nullable=True)
    additional_metadata = Column(JSONB, default=dict)
    
    # Audit Trail
    created_by_user_id = Column(UUID(as_uuid=True), ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    
    # Versioning (Enterprise-grade audit trail)
    version = Column(Integer, default=1, nullable=False)
    previous_version_id = Column(UUID(as_uuid=True), ForeignKey("contractor_invoices.id", ondelete="SET NULL"), nullable=True)
    is_latest_version = Column(Boolean, default=True, nullable=False, index=True)
    revision_notes = Column(Text, nullable=True)
    
    # Invoice Viewing & Sharing
    viewing_token = Column(String(255), unique=True, nullable=True)
    viewing_token_expires_at = Column(DateTime(timezone=True), nullable=True)
    viewing_token_created_by = Column(UUID(as_uuid=True), ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    times_viewed = Column(Integer, default=0, nullable=False)
    last_viewed_at = Column(DateTime(timezone=True), nullable=True)
    
    # CSV Export Tracking
    csv_exported = Column(Boolean, default=False, nullable=False)
    csv_exported_at = Column(DateTime(timezone=True), nullable=True)
    csv_exported_by_user_id = Column(UUID(as_uuid=True), ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    
    # Client Feedback (future feature)
    client_comments = Column(Text, nullable=True)
    client_viewed_at = Column(DateTime(timezone=True), nullable=True)
    
    # Relationships
    contractor = relationship("Contractor", foreign_keys=[contractor_id], backref="invoices")
    client = relationship("Client", foreign_keys=[client_id], backref="invoices")
    project = relationship("Project", foreign_keys=[project_id], backref="invoices")
    created_by = relationship("User", foreign_keys=[created_by_user_id])
    viewing_token_creator = relationship("User", foreign_keys=[viewing_token_created_by])
    csv_exported_by = relationship("User", foreign_keys=[csv_exported_by_user_id])
    
    # Self-referential relationship for version history
    previous_version = relationship(
        "ContractorInvoice",
        foreign_keys=[previous_version_id],
        remote_side="ContractorInvoice.id",
        backref="next_versions"
    )
    
    # Tickets linked to this invoice (one-to-many)
    # Note: Defined in Ticket model as: contractor_invoice = relationship("ContractorInvoice", ...)
    
    # Table constraints (replicated from schema.sql for ORM awareness)
    __table_args__ = (
        CheckConstraint(
            "subtotal >= 0 AND tax_amount >= 0 AND discount_amount >= 0 AND total_amount >= 0 AND amount_paid >= 0 AND amount_paid <= total_amount",
            name="chk_positive_amounts"
        ),
        CheckConstraint(
            "due_date >= issue_date AND (sent_date IS NULL OR sent_date >= issue_date) AND (paid_date IS NULL OR paid_date >= issue_date)",
            name="chk_valid_dates"
        ),
        Index("idx_contractor_invoices_contractor", "contractor_id", "status", "created_at"),
        Index("idx_contractor_invoices_client", "client_id", "status", "due_date"),
        Index("idx_contractor_invoices_project", "project_id", "status"),
        Index("idx_contractor_invoices_status", "status", "due_date"),
        Index("idx_contractor_invoices_overdue", "status", "due_date", postgresql_where="status IN ('sent', 'overdue')"),
        Index("idx_contractor_invoices_version", "id", "version"),
        Index("idx_contractor_invoices_latest", "is_latest_version", postgresql_where="is_latest_version = TRUE AND deleted_at IS NULL"),
        Index("idx_contractor_invoices_previous_version", "previous_version_id", postgresql_where="previous_version_id IS NOT NULL"),
    )
    
    def __repr__(self):
        return f"<ContractorInvoice(invoice_number='{self.invoice_number}', version={self.version}, status='{self.status}', total={self.total_amount})>"
    
    @property
    def amount_due(self) -> Decimal:
        """Calculate amount due (total - paid)"""
        return Decimal(str(self.total_amount)) - Decimal(str(self.amount_paid))
    
    @property
    def is_fully_paid(self) -> bool:
        """Check if invoice is fully paid"""
        return self.amount_paid >= self.total_amount
    
    @property
    def is_partially_paid(self) -> bool:
        """Check if invoice is partially paid"""
        return Decimal('0') < self.amount_paid < self.total_amount
    
    @property
    def is_overdue(self) -> bool:
        """Check if invoice is overdue"""
        if self.status not in ['sent', 'partially_paid']:
            return False
        if self.is_fully_paid:
            return False
        return datetime.now().date() > self.due_date
    
    def get_line_item_by_id(self, line_item_id: str):
        """Get a specific line item by ID"""
        if not self.line_items:
            return None
        return next((item for item in self.line_items if item.get('id') == line_item_id), None)
    
    def get_ticket_line_items(self):
        """Get all line items that reference tickets"""
        if not self.line_items:
            return []
        return [item for item in self.line_items if item.get('type') == 'ticket' and item.get('ticket_id')]
    
    def get_ticket_ids(self):
        """Get all ticket IDs referenced in line items"""
        return [item['ticket_id'] for item in self.get_ticket_line_items()]