"""
Subscription Model - Customer service subscriptions
Maps to 'subscriptions' table in docs/schema/schema.sql
"""
from sqlalchemy import Column, String, Text, Date, Numeric, ForeignKey, CheckConstraint
from sqlalchemy.dialects.postgresql import UUID, JSONB
from sqlalchemy.orm import relationship
from app.models.base import BaseModel
from app.models.enums import ServiceType, SubscriptionStatus, PayoutMethod


class Subscription(BaseModel):
    """
    Subscription model - Tracks customer service subscriptions
    Maps to 'subscriptions' table in docs/schema/schema.sql
    
    Purpose: Represent active/inactive customer service subscriptions
    Links to customers, sales orders, and projects
    """
    __tablename__ = "subscriptions"
    
    # Relationships
    customer_id = Column(UUID(as_uuid=True), ForeignKey('customers.id', ondelete='RESTRICT'), nullable=False)
    sales_order_id = Column(UUID(as_uuid=True), ForeignKey('sales_orders.id', ondelete='SET NULL'), nullable=True)
    project_id = Column(UUID(as_uuid=True), ForeignKey('projects.id', ondelete='RESTRICT'), nullable=True)
    
    # Service Details
    service_type = Column(String(50), nullable=False)  # ServiceType enum
    package_name = Column(Text, nullable=True)
    monthly_fee = Column(Numeric(precision=12, scale=2), nullable=True)
    
    # Service Address (where service is active)
    project_region_id = Column(UUID(as_uuid=True), ForeignKey('project_regions.id', ondelete='SET NULL'), nullable=True)
    service_address_line1 = Column(Text, nullable=True)
    service_address_line2 = Column(Text, nullable=True)
    service_maps_link = Column(Text, nullable=True)
    service_latitude = Column(Numeric(precision=10, scale=7), nullable=True)
    service_longitude = Column(Numeric(precision=10, scale=7), nullable=True)
    
    # Equipment & Activation Details (Flexible JSONB storage)
    equipment_details = Column(JSONB, default={}, nullable=False)
    activation_details = Column(JSONB, default={}, nullable=False)
    
    # Payment Method
    payment_method = Column(String(50), nullable=True)  # PayoutMethod enum
    mobile_money_provider = Column(Text, nullable=True)
    payment_phone = Column(Text, nullable=True)
    payment_account_number = Column(Text, nullable=True)
    payment_account_name = Column(Text, nullable=True)
    
    # Lifecycle
    status = Column(String(50), default='pending_activation', nullable=False)  # SubscriptionStatus enum
    activation_date = Column(Date, nullable=True)
    cancellation_date = Column(Date, nullable=True)
    suspension_date = Column(Date, nullable=True)
    termination_date = Column(Date, nullable=True)
    
    # Metadata
    notes = Column(Text, nullable=True)
    additional_metadata = Column(JSONB, default={}, nullable=False)
    activated_by_user_id = Column(UUID(as_uuid=True), ForeignKey('users.id', ondelete='SET NULL'), nullable=True)
    
    # Relationships
    customer = relationship("Customer", back_populates="subscriptions")
    sales_order = relationship("SalesOrder", foreign_keys=[sales_order_id])
    project = relationship("Project", foreign_keys=[project_id])
    project_region = relationship("ProjectRegion", foreign_keys=[project_region_id])
    activated_by_user = relationship("User", foreign_keys=[activated_by_user_id])
    
    # Table constraints
    __table_args__ = (
        CheckConstraint(
            'monthly_fee IS NULL OR monthly_fee >= 0',
            name='chk_positive_fee'
        ),
        CheckConstraint(
            'cancellation_date IS NULL OR activation_date IS NULL OR cancellation_date >= activation_date',
            name='chk_cancellation_after_activation'
        ),
    )
    
    def __repr__(self):
        return f"<Subscription(id='{self.id}', customer_id='{self.customer_id}', status='{self.status}')>"