import uuid
from datetime import datetime, timezone
from typing import Any, Optional
from sqlalchemy import (
    Boolean,
    DateTime,
    ForeignKey,
    Index,
    Integer,
    JSON,
    String,
    UniqueConstraint,
)
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.orm import Mapped, mapped_column, relationship
from infrastructure.database.base import (
    Base,
    UUIDPrimaryKeyMixin,
    TimestampMixin,
    TenantMixin,
)


class Plan(Base, TimestampMixin):
    """SaaS subscription plan with entitlements and limits."""
    __tablename__ = "plans"

    id: Mapped[str] = mapped_column(String(50), primary_key=True)
    name: Mapped[str] = mapped_column(String(100), nullable=False)
    description: Mapped[str] = mapped_column(String(500), nullable=False)
    price_amount: Mapped[int] = mapped_column(Integer, default=0, nullable=False)  # in paise / cents
    currency: Mapped[str] = mapped_column(String(10), default="INR", nullable=False)
    billing_interval: Mapped[str] = mapped_column(String(20), default="monthly", nullable=False)
    trial_days: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)

    # Entitlement Limits (-1 denotes unlimited)
    max_bots: Mapped[int] = mapped_column(Integer, default=1, nullable=False)
    max_broadcasts_per_month: Mapped[int] = mapped_column(Integer, default=100, nullable=False)
    max_custom_emojis: Mapped[int] = mapped_column(Integer, default=10, nullable=False)
    ai_enabled: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    advanced_studio: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    custom_domain: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    priority_support: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    webhook_rate_limit: Mapped[int] = mapped_column(Integer, default=30, nullable=False)

    subscriptions: Mapped[list["Subscription"]] = relationship("Subscription", back_populates="plan")


class Subscription(Base, UUIDPrimaryKeyMixin, TenantMixin, TimestampMixin):
    """Tenant subscription with lifecycle state, period dates, and grace period."""
    __tablename__ = "subscriptions"

    plan_id: Mapped[str] = mapped_column(
        String(50),
        ForeignKey("plans.id", ondelete="RESTRICT"),
        nullable=False,
        default="free",
        index=True,
    )
    # trialing | active | past_due | grace_period | canceled | expired
    status: Mapped[str] = mapped_column(String(50), default="active", nullable=False, index=True)
    current_period_start: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    current_period_end: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, index=True)
    cancel_at_period_end: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    canceled_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    trial_start: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    trial_end: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    grace_period_ends_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    gateway_provider: Mapped[str] = mapped_column(String(50), default="manual", nullable=False)
    gateway_subscription_id: Mapped[Optional[str]] = mapped_column(String(100), nullable=True)
    gateway_customer_id: Mapped[Optional[str]] = mapped_column(String(100), nullable=True)

    plan: Mapped["Plan"] = relationship("Plan", back_populates="subscriptions")
    invoices: Mapped[list["Invoice"]] = relationship("Invoice", back_populates="subscription")


class PaymentOrder(Base, UUIDPrimaryKeyMixin, TenantMixin, TimestampMixin):
    """Order created before checkout, cryptographically verified upon payment."""
    __tablename__ = "payment_orders"

    order_number: Mapped[str] = mapped_column(String(100), unique=True, index=True, nullable=False)
    amount: Mapped[int] = mapped_column(Integer, nullable=False)  # in paise / cents
    currency: Mapped[str] = mapped_column(String(10), default="INR", nullable=False)
    # created | processing | paid | failed | refunded | partially_refunded
    status: Mapped[str] = mapped_column(String(50), default="created", nullable=False, index=True)
    provider: Mapped[str] = mapped_column(String(50), nullable=False)  # razorpay | stripe | wallet
    gateway_order_id: Mapped[Optional[str]] = mapped_column(String(100), nullable=True, index=True)
    gateway_payment_id: Mapped[Optional[str]] = mapped_column(String(100), nullable=True, index=True)
    gateway_signature: Mapped[Optional[str]] = mapped_column(String(255), nullable=True)
    idempotency_key: Mapped[str] = mapped_column(String(100), unique=True, index=True, nullable=False)
    purpose: Mapped[str] = mapped_column(String(50), default="subscription", nullable=False)
    plan_id: Mapped[Optional[str]] = mapped_column(String(50), nullable=True)
    metadata_json: Mapped[Optional[dict[str, Any]]] = mapped_column(JSON, nullable=True)

    invoices: Mapped[list["Invoice"]] = relationship("Invoice", back_populates="order")
    refunds: Mapped[list["Refund"]] = relationship("Refund", back_populates="order")


class PaymentWebhookEvent(Base, UUIDPrimaryKeyMixin, TimestampMixin):
    """Stores incoming gateway webhook events to guarantee duplicate event protection."""
    __tablename__ = "payment_webhook_events"

    gateway_provider: Mapped[str] = mapped_column(String(50), nullable=False, index=True)
    event_id: Mapped[str] = mapped_column(String(100), nullable=False, index=True)
    event_type: Mapped[str] = mapped_column(String(100), nullable=False)
    payload: Mapped[dict[str, Any]] = mapped_column(JSON, nullable=False)
    status: Mapped[str] = mapped_column(String(50), default="processed", nullable=False)
    error_message: Mapped[Optional[str]] = mapped_column(String(500), nullable=True)

    __table_args__ = (
        UniqueConstraint("gateway_provider", "event_id", name="uq_webhook_provider_event"),
    )


class Invoice(Base, UUIDPrimaryKeyMixin, TenantMixin, TimestampMixin):
    """Auditable invoice with line items, tax, and discount breakdown."""
    __tablename__ = "invoices"

    invoice_number: Mapped[str] = mapped_column(String(100), unique=True, index=True, nullable=False)
    subscription_id: Mapped[Optional[uuid.UUID]] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("subscriptions.id", ondelete="SET NULL"),
        nullable=True,
    )
    order_id: Mapped[Optional[uuid.UUID]] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("payment_orders.id", ondelete="SET NULL"),
        nullable=True,
    )
    amount_due: Mapped[int] = mapped_column(Integer, nullable=False)
    amount_paid: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    amount_remaining: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    currency: Mapped[str] = mapped_column(String(10), default="INR", nullable=False)
    # draft | open | paid | void | uncollectible | refunded
    status: Mapped[str] = mapped_column(String(50), default="draft", nullable=False, index=True)
    due_date: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    paid_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    billing_email: Mapped[str] = mapped_column(String(255), nullable=False)
    billing_name: Mapped[str] = mapped_column(String(255), nullable=False)
    billing_address: Mapped[Optional[str]] = mapped_column(String(500), nullable=True)
    tax_id: Mapped[Optional[str]] = mapped_column(String(100), nullable=True)  # GSTIN or VAT ID
    items: Mapped[list[dict[str, Any]]] = mapped_column(JSON, nullable=False)
    subtotal: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    tax_amount: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    discount_amount: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    pdf_url: Mapped[Optional[str]] = mapped_column(String(500), nullable=True)

    subscription: Mapped[Optional["Subscription"]] = relationship("Subscription", back_populates="invoices")
    order: Mapped[Optional["PaymentOrder"]] = relationship("PaymentOrder", back_populates="invoices")


class Refund(Base, UUIDPrimaryKeyMixin, TenantMixin, TimestampMixin):
    """Payment refund record with gateway tracking."""
    __tablename__ = "refunds"

    order_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("payment_orders.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    invoice_id: Mapped[Optional[uuid.UUID]] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("invoices.id", ondelete="SET NULL"),
        nullable=True,
    )
    amount: Mapped[int] = mapped_column(Integer, nullable=False)
    currency: Mapped[str] = mapped_column(String(10), default="INR", nullable=False)
    reason: Mapped[str] = mapped_column(String(500), nullable=False)
    # pending | processed | failed
    status: Mapped[str] = mapped_column(String(50), default="pending", nullable=False, index=True)
    gateway_refund_id: Mapped[Optional[str]] = mapped_column(String(100), nullable=True)
    provider: Mapped[str] = mapped_column(String(50), nullable=False)

    order: Mapped["PaymentOrder"] = relationship("PaymentOrder", back_populates="refunds")


class Wallet(Base, UUIDPrimaryKeyMixin, TenantMixin, TimestampMixin):
    """Tenant prepaid balance wallet."""
    __tablename__ = "wallets"

    balance: Mapped[int] = mapped_column(Integer, default=0, nullable=False)  # in paise / credits
    currency: Mapped[str] = mapped_column(String(10), default="INR", nullable=False)
    is_frozen: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)

    transactions: Mapped[list["WalletTransaction"]] = relationship("WalletTransaction", back_populates="wallet", cascade="all, delete-orphan")

    __table_args__ = (
        UniqueConstraint("tenant_id", name="uq_tenant_wallet"),
    )


class WalletTransaction(Base, UUIDPrimaryKeyMixin, TenantMixin, TimestampMixin):
    """Immutable ledger of all wallet credits, debits, holds, and releases with SHA-256 checksum."""
    __tablename__ = "wallet_transactions"

    wallet_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("wallets.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    transaction_type: Mapped[str] = mapped_column(String(50), nullable=False)  # credit | debit | hold | release
    amount: Mapped[int] = mapped_column(Integer, nullable=False)
    balance_after: Mapped[int] = mapped_column(Integer, nullable=False)
    reference_type: Mapped[str] = mapped_column(String(50), nullable=False)  # order | invoice | referral | bonus | refund | manual
    reference_id: Mapped[Optional[str]] = mapped_column(String(100), nullable=True)
    description: Mapped[str] = mapped_column(String(500), nullable=False)
    checksum: Mapped[str] = mapped_column(String(64), nullable=False)  # SHA-256 tamper-evident hash

    wallet: Mapped["Wallet"] = relationship("Wallet", back_populates="transactions")


class Coupon(Base, UUIDPrimaryKeyMixin, TimestampMixin):
    """Promotional discount coupons with usage restrictions and limits."""
    __tablename__ = "coupons"

    code: Mapped[str] = mapped_column(String(50), unique=True, index=True, nullable=False)
    discount_type: Mapped[str] = mapped_column(String(20), nullable=False)  # percentage | fixed_amount
    discount_value: Mapped[int] = mapped_column(Integer, nullable=False)  # percentage (e.g. 20) or paise (e.g. 50000 for ₹500)
    currency: Mapped[str] = mapped_column(String(10), default="INR", nullable=False)
    min_order_amount: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    max_discount_amount: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
    max_redemptions: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
    times_redeemed: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    expires_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    applicable_plans: Mapped[Optional[list[str]]] = mapped_column(JSON, nullable=True)

    redemptions: Mapped[list["CouponRedemption"]] = relationship("CouponRedemption", back_populates="coupon")


class CouponRedemption(Base, UUIDPrimaryKeyMixin, TenantMixin, TimestampMixin):
    """Audit of applied coupons."""
    __tablename__ = "coupon_redemptions"

    coupon_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("coupons.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    order_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("payment_orders.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    discount_applied: Mapped[int] = mapped_column(Integer, nullable=False)

    coupon: Mapped["Coupon"] = relationship("Coupon", back_populates="redemptions")


class Referral(Base, UUIDPrimaryKeyMixin, TimestampMixin):
    """Tracks tenant-to-tenant referrals and reward payouts."""
    __tablename__ = "referrals"

    referrer_tenant_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("tenants.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    referred_tenant_id: Mapped[uuid.UUID] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("tenants.id", ondelete="CASCADE"),
        unique=True,
        nullable=False,
    )
    referral_code: Mapped[str] = mapped_column(String(50), index=True, nullable=False)
    status: Mapped[str] = mapped_column(String(50), default="pending", nullable=False)  # pending | rewarded
    reward_amount: Mapped[int] = mapped_column(Integer, default=50000, nullable=False)  # ₹500 in paise
    rewarded_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)


class TenantUsage(Base, UUIDPrimaryKeyMixin, TenantMixin, TimestampMixin):
    """Tracks resource consumption against plan entitlements for the billing period."""
    __tablename__ = "tenant_usages"

    period_start: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    period_end: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    active_bots_count: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    broadcasts_sent_count: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    custom_emojis_count: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    ai_tokens_used: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
