from sqlalchemy import (
    Column, Integer, String, Boolean, DateTime, Enum, Text, JSON,
    ForeignKey, DECIMAL, Date, func
)
from sqlalchemy.orm import relationship
from app.core.database import Base


class Subscription(Base):
    __tablename__ = "subscriptions"

    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False)
    package_id = Column(Integer, ForeignKey("packages.id"), nullable=False)
    start_date = Column(Date, nullable=False)
    end_date = Column(Date, nullable=True)
    status = Column(
        Enum("active", "expired", "cancelled", "grace_period", "pending"),
        default="pending"
    )
    payment_reference = Column(String(255), nullable=True)
    auto_renew = Column(Boolean, default=False)
    grace_period_end = Column(Date, nullable=True)
    last_verified_at = Column(DateTime, nullable=True)
    verification_token = Column(String(64), nullable=True)
    created_at = Column(DateTime, server_default=func.now())
    updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now())

    user = relationship("User", back_populates="subscriptions")
    package = relationship("Package", back_populates="subscriptions")
    payments = relationship("Payment", back_populates="subscription")


class Payment(Base):
    __tablename__ = "payments"

    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False)
    subscription_id = Column(Integer, ForeignKey("subscriptions.id"), nullable=True)
    amount = Column(DECIMAL(12, 2), nullable=False)
    currency = Column(String(3), default="UGX")
    payment_method = Column(
        Enum("card", "mobile_money", "bank_transfer", "voucher", "school_account", "other"),
        default="mobile_money"
    )
    provider = Column(String(50), nullable=True)
    payment_reference = Column(String(255), unique=True, nullable=True)
    external_reference = Column(String(255), nullable=True)
    status = Column(
        Enum("pending", "completed", "failed", "refunded", "cancelled"),
        default="pending"
    )
    payment_metadata = Column(JSON, nullable=True)
    paid_at = Column(DateTime, nullable=True)
    created_at = Column(DateTime, server_default=func.now())
    updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now())

    user = relationship("User", back_populates="payments")
    subscription = relationship("Subscription", back_populates="payments")