"""Billing domain - SQLAlchemy models.

Two billing dimensions:
  1. Subscription-based: predefined plans created in Stripe (per workspace)
  2. Usage-based: metered billing for AI tokens, build minutes, preview time, etc.
  3. One-time: usage credit packages (AI minutes top-ups)

Users are linked to Stripe customers via stripe_customers table.
"""
from __future__ import annotations
from datetime import datetime
from typing import Optional
from uuid import UUID, uuid4

from sqlalchemy import BigInteger, Boolean, ForeignKey, Integer, String, Text
from sqlalchemy.dialects.postgresql import ENUM as PG_ENUM, JSONB, UUID
from sqlalchemy.orm import Mapped, mapped_column
from sqlalchemy.sql import func
from sqlalchemy.types import DateTime

from src.core.database import Base

# Alembic 001_initial_schema; create_type=False — migrations own the type.
P2HS_SUBSCRIPTION_STATUS = PG_ENUM(
    "active",
    "past_due",
    "canceled",
    "trialing",
    "incomplete",
    name="p2hs_subscription_status",
    create_type=False,
)


class StripeCustomer(Base):
    """Links a workspace to a Stripe customer.

    Schema matches ``001_initial_schema``: ``workspace_id`` is the primary key
    (one Stripe customer per workspace). Metadata (owner email, etc.) lives in Stripe.
    """
    __tablename__ = "stripe_customers"

    workspace_id: Mapped[UUID] = mapped_column(
        UUID(as_uuid=True), ForeignKey("workspaces.id", ondelete="CASCADE"), primary_key=True
    )
    stripe_customer_id: Mapped[str] = mapped_column(String(255), nullable=False, unique=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())


class EnterpriseInquiry(Base):
    """Sales lead from enterprise / custom pricing form (guests allowed)."""

    __tablename__ = "enterprise_inquiries"

    id: Mapped[UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid4)
    workspace_id: Mapped[Optional[UUID]] = mapped_column(
        UUID(as_uuid=True), ForeignKey("workspaces.id", ondelete="SET NULL"), nullable=True
    )
    user_id: Mapped[Optional[UUID]] = mapped_column(
        UUID(as_uuid=True), ForeignKey("users.id", ondelete="SET NULL"), nullable=True
    )
    first_name: Mapped[str] = mapped_column(String(100), nullable=False)
    last_name: Mapped[str] = mapped_column(String(100), nullable=False)
    email: Mapped[str] = mapped_column(String(255), nullable=False)
    phone: Mapped[str] = mapped_column(String(50), nullable=False)
    website_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    ai_credit_minutes_desired: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    is_agency_account: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    employee_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    additional_features: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    plan_slug: Mapped[Optional[str]] = mapped_column(String(50), nullable=True)
    status: Mapped[str] = mapped_column(String(20), nullable=False, default="new")
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())


class Subscription(Base):
    """Workspace subscription from Stripe.

    Represents a predefined plan (basic, pro, enterprise, etc.)
    that the workspace subscribes to via Stripe Checkout.
    Stores both internal plan identity (plan_slug) and Stripe billing identity.
    """
    __tablename__ = "subscriptions"

    id: Mapped[UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid4)
    workspace_id: Mapped[UUID] = mapped_column(UUID(as_uuid=True), ForeignKey("workspaces.id", ondelete="CASCADE"), nullable=False)
    stripe_subscription_id: Mapped[Optional[str]] = mapped_column(String(100), nullable=True, unique=True)
    stripe_customer_id: Mapped[Optional[str]] = mapped_column(String(100), nullable=True)
    status: Mapped[str] = mapped_column(P2HS_SUBSCRIPTION_STATUS, nullable=False)

    # Internal plan identity (DB-driven)
    plan_slug: Mapped[Optional[str]] = mapped_column(String(50), nullable=True)
    subscription_plan_id: Mapped[Optional[UUID]] = mapped_column(UUID(as_uuid=True), ForeignKey("subscription_plans.id"), nullable=True)

    # Stripe billing identity
    stripe_product_id: Mapped[Optional[str]] = mapped_column(String(100), nullable=True)
    stripe_price_id: Mapped[Optional[str]] = mapped_column(String(100), nullable=True)
    billing_interval: Mapped[Optional[str]] = mapped_column(String(20), nullable=True)

    # Legacy (kept for backward compat; stripe_price_id is canonical)
    plan_id: Mapped[Optional[str]] = mapped_column(String(100), nullable=True)
    plan_name: Mapped[Optional[str]] = mapped_column(String(100), nullable=True)

    current_period_start: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    current_period_end: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    cancel_at_period_end: Mapped[bool] = mapped_column(default=False, nullable=False)
    canceled_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    ended_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)
    metered_subscription_item_id: Mapped[Optional[str]] = mapped_column(String(100), nullable=True)
    metadata_json: Mapped[Optional[dict]] = mapped_column(JSONB, nullable=True)

    # Grandfathered pricing: subscriber keeps old price until action that breaks it
    is_grandfathered: Mapped[bool] = mapped_column(default=False, nullable=False)
    grandfather_metadata: Mapped[Optional[dict]] = mapped_column(JSONB, nullable=True)

    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())


class Invoice(Base):
    __tablename__ = "invoices"

    id: Mapped[UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid4)
    workspace_id: Mapped[UUID] = mapped_column(UUID(as_uuid=True), ForeignKey("workspaces.id", ondelete="CASCADE"), nullable=False)
    stripe_invoice_id: Mapped[str] = mapped_column(String(100), nullable=False, unique=True)
    stripe_customer_id: Mapped[str] = mapped_column(String(100), nullable=False)
    status: Mapped[str] = mapped_column(String(30), nullable=False)
    amount_due: Mapped[int] = mapped_column(BigInteger, nullable=False, default=0)
    amount_paid: Mapped[int] = mapped_column(BigInteger, nullable=False, default=0)
    currency: Mapped[str] = mapped_column(String(10), nullable=False, default="usd")
    invoice_url: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    invoice_pdf: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    period_start: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    period_end: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())


class StripeEvent(Base):
    __tablename__ = "stripe_events"

    id: Mapped[UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid4)
    stripe_event_id: Mapped[str] = mapped_column(String(100), nullable=False, unique=True)
    event_type: Mapped[str] = mapped_column(String(100), nullable=False)
    processed_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    payload: Mapped[Optional[dict]] = mapped_column(JSONB, nullable=True)


class UsageLedger(Base):
    """Per-event usage tracking for metered billing.

    event_type examples:
      - ai_tokens        (quantity = token count)
      - build_minutes    (quantity = seconds)
      - preview_minutes  (quantity = seconds)
      - codegen_run      (quantity = 1 per run)
      - deploy           (quantity = 1 per deploy)
    """
    __tablename__ = "usage_ledger"

    id: Mapped[UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid4)
    workspace_id: Mapped[UUID] = mapped_column(UUID(as_uuid=True), ForeignKey("workspaces.id", ondelete="CASCADE"), nullable=False)
    project_id: Mapped[Optional[UUID]] = mapped_column(UUID(as_uuid=True), ForeignKey("projects.id"), nullable=True)
    event_type: Mapped[str] = mapped_column(String(50), nullable=False)
    quantity: Mapped[int] = mapped_column(Integer, nullable=False, default=1)
    unit_label: Mapped[Optional[str]] = mapped_column(String(30), nullable=True)
    metadata_json: Mapped[Optional[dict]] = mapped_column(JSONB, nullable=True)
    reported_to_stripe: Mapped[bool] = mapped_column(default=False, nullable=False)
    recorded_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())


class UsageCreditPackage(Base):
    """One-time purchasable usage credit bundles (e.g. 60, 180, 500 AI minutes)."""
    __tablename__ = "usage_credit_packages"

    id: Mapped[UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid4)
    slug: Mapped[str] = mapped_column(String(50), unique=True, nullable=False)
    name: Mapped[str] = mapped_column(String(100), nullable=False)
    description: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    units: Mapped[int] = mapped_column(Integer, nullable=False)
    price_minor: Mapped[int] = mapped_column(BigInteger, nullable=False)
    currency: Mapped[str] = mapped_column(String(10), nullable=False, default="usd")
    stripe_product_id: Mapped[Optional[str]] = mapped_column(String(100), nullable=True)
    stripe_price_id: Mapped[Optional[str]] = mapped_column(String(100), nullable=True)
    is_active: Mapped[bool] = mapped_column(default=True, nullable=False)
    sort_order: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    metadata_json: Mapped[Optional[dict]] = mapped_column(JSONB, nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())


class UsageCreditPurchase(Base):
    """Financial record of one-time usage credit top-up purchase."""
    __tablename__ = "usage_credit_purchases"

    id: Mapped[UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid4)
    workspace_id: Mapped[UUID] = mapped_column(UUID(as_uuid=True), ForeignKey("workspaces.id", ondelete="CASCADE"), nullable=False)
    stripe_customer_id: Mapped[str] = mapped_column(String(100), nullable=False)
    stripe_checkout_session_id: Mapped[Optional[str]] = mapped_column(String(100), nullable=True)
    stripe_payment_intent_id: Mapped[Optional[str]] = mapped_column(String(100), nullable=True)
    stripe_invoice_id: Mapped[Optional[str]] = mapped_column(String(100), nullable=True)
    usage_credit_package_id: Mapped[Optional[UUID]] = mapped_column(UUID(as_uuid=True), ForeignKey("usage_credit_packages.id"), nullable=True)
    package_slug: Mapped[str] = mapped_column(String(50), nullable=False)
    units_purchased: Mapped[int] = mapped_column(Integer, nullable=False)
    amount_minor: Mapped[int] = mapped_column(BigInteger, nullable=False)
    currency: Mapped[str] = mapped_column(String(10), nullable=False, default="usd")
    status: Mapped[str] = mapped_column(String(30), nullable=False)  # pending, paid, failed, refunded, canceled
    paid_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())


class WorkspaceCreditLedger(Base):
    """Usage credit accounting: purchase, consumption, adjustment, refund, bonus, expiration."""
    __tablename__ = "workspace_credit_ledger"

    id: Mapped[UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid4)
    workspace_id: Mapped[UUID] = mapped_column(UUID(as_uuid=True), ForeignKey("workspaces.id", ondelete="CASCADE"), nullable=False)
    entry_type: Mapped[str] = mapped_column(String(30), nullable=False)  # purchase, consumption, adjustment, refund, bonus, expiration
    units_delta: Mapped[int] = mapped_column(Integer, nullable=False)
    balance_after: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
    source_bucket: Mapped[Optional[str]] = mapped_column(String(50), nullable=True)  # subscription_allowance, purchased_topup, bonus, manual_adjustment
    source_type: Mapped[Optional[str]] = mapped_column(String(50), nullable=True)  # stripe_checkout, codegen, project_generation, etc.
    source_id: Mapped[Optional[str]] = mapped_column(String(100), nullable=True)
    subscription_id: Mapped[Optional[UUID]] = mapped_column(UUID(as_uuid=True), ForeignKey("subscriptions.id"), nullable=True)
    usage_credit_purchase_id: Mapped[Optional[UUID]] = mapped_column(UUID(as_uuid=True), ForeignKey("usage_credit_purchases.id"), nullable=True)
    project_id: Mapped[Optional[UUID]] = mapped_column(UUID(as_uuid=True), nullable=True)
    codegen_job_id: Mapped[Optional[UUID]] = mapped_column(UUID(as_uuid=True), nullable=True)
    artifact_id: Mapped[Optional[UUID]] = mapped_column(UUID(as_uuid=True), nullable=True)
    revision_id: Mapped[Optional[UUID]] = mapped_column(UUID(as_uuid=True), nullable=True)
    description: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    metadata_json: Mapped[Optional[dict]] = mapped_column(JSONB, nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())


class WorkspaceBillingDiscount(Base):
    """First-class discount record. Stripe coupons are execution, not source of truth.

    Every discount answers: why (reason), how long (duration_type, ends_at), what (scope).
    """
    __tablename__ = "workspace_billing_discounts"

    id: Mapped[UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid4)
    workspace_id: Mapped[UUID] = mapped_column(UUID(as_uuid=True), ForeignKey("workspaces.id", ondelete="CASCADE"), nullable=False)
    source: Mapped[str] = mapped_column(String(50), nullable=False)  # manual, campaign, partner, user_segment
    scope: Mapped[str] = mapped_column(String(50), nullable=False)  # subscription, credit_purchase, both
    duration_type: Mapped[str] = mapped_column(String(30), nullable=False)  # one_time, repeating_n_months, lifetime
    duration_months: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
    starts_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, server_default=func.now())
    ends_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    stripe_coupon_id: Mapped[Optional[str]] = mapped_column(String(100), nullable=True)
    stripe_promotion_code_id: Mapped[Optional[str]] = mapped_column(String(100), nullable=True)
    discount_percent: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
    discount_amount_minor: Mapped[Optional[int]] = mapped_column(BigInteger, nullable=True)
    currency: Mapped[str] = mapped_column(String(10), nullable=False, default="usd")
    reason: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    stacking_rule: Mapped[str] = mapped_column(String(30), nullable=False, default="replace")  # replace, stack
    metadata_json: Mapped[Optional[dict]] = mapped_column(JSONB, nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())
    created_by: Mapped[Optional[UUID]] = mapped_column(UUID(as_uuid=True), ForeignKey("users.id", ondelete="SET NULL"), nullable=True)


class BillingNotification(Base):
    """Auditable notification log - deduped by (dedupe_key, recipient_email)."""
    __tablename__ = "billing_notifications"

    id: Mapped[UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid4)
    workspace_id: Mapped[UUID] = mapped_column(UUID(as_uuid=True), ForeignKey("workspaces.id", ondelete="CASCADE"), nullable=False)
    notification_type: Mapped[str] = mapped_column(String(50), nullable=False)
    source_object_type: Mapped[str] = mapped_column(String(50), nullable=False)
    source_object_id: Mapped[str] = mapped_column(String(100), nullable=False)
    dedupe_key: Mapped[str] = mapped_column(String(200), nullable=False)
    recipient_email: Mapped[str] = mapped_column(String(255), nullable=False)
    status: Mapped[str] = mapped_column(String(30), nullable=False)  # queued, sent, failed, skipped_duplicate
    provider_message_id: Mapped[Optional[str]] = mapped_column(String(200), nullable=True)
    last_error: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    sent_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())


class WorkspaceUsageAllowance(Base):
    """Included monthly allowance per subscription period (consumed first, then top-ups)."""
    __tablename__ = "workspace_usage_allowances"

    id: Mapped[UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid4)
    workspace_id: Mapped[UUID] = mapped_column(UUID(as_uuid=True), ForeignKey("workspaces.id", ondelete="CASCADE"), nullable=False)
    subscription_id: Mapped[Optional[UUID]] = mapped_column(UUID(as_uuid=True), ForeignKey("subscriptions.id"), nullable=True)
    period_start: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    period_end: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    included_units: Mapped[int] = mapped_column(Integer, nullable=False)
    consumed_units: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    remaining_units: Mapped[int] = mapped_column(Integer, nullable=False)
    reset_strategy: Mapped[str] = mapped_column(String(30), default="period_end", nullable=False)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())


class PricingCampaign(Base):
    """Limited-slot pricing campaign (e.g. the founding-customer offer).

    Campaign prices are dedicated Stripe Prices that the catalog sync never rotates, so a
    subscriber who joins on one keeps that amount even when the catalog price changes.
    ``max_redemptions`` is one pool shared by every price in the campaign.
    """
    __tablename__ = "pricing_campaigns"

    id: Mapped[UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid4)
    slug: Mapped[str] = mapped_column(String(50), unique=True, nullable=False)
    name: Mapped[str] = mapped_column(String(100), nullable=False)
    max_redemptions: Mapped[int] = mapped_column(Integer, nullable=False)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    starts_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    ends_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())


class PricingCampaignPrice(Base):
    """One Stripe Price per (campaign, plan, interval)."""
    __tablename__ = "pricing_campaign_prices"

    id: Mapped[UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid4)
    campaign_id: Mapped[UUID] = mapped_column(
        UUID(as_uuid=True), ForeignKey("pricing_campaigns.id", ondelete="CASCADE"), nullable=False
    )
    subscription_plan_id: Mapped[UUID] = mapped_column(
        UUID(as_uuid=True), ForeignKey("subscription_plans.id", ondelete="CASCADE"), nullable=False
    )
    interval: Mapped[str] = mapped_column(String(20), nullable=False)  # month, year
    stripe_price_id: Mapped[str] = mapped_column(String(100), nullable=False, unique=True)
    price_minor: Mapped[int] = mapped_column(BigInteger, nullable=False)
    currency: Mapped[str] = mapped_column(String(10), nullable=False, default="usd")
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())


class PricingCampaignRedemption(Base):
    """A claimed campaign slot. reserved -> confirmed | released.

    ``subject_key`` is ``ws:<workspace_id>`` for signed-in checkout or ``email:<address>`` for
    pre-registration checkout; one live redemption per subject per campaign. Reservations
    count against the cap until ``expires_at`` so abandoned checkouts free their slot.
    """
    __tablename__ = "pricing_campaign_redemptions"

    id: Mapped[UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid4)
    campaign_id: Mapped[UUID] = mapped_column(
        UUID(as_uuid=True), ForeignKey("pricing_campaigns.id", ondelete="CASCADE"), nullable=False
    )
    campaign_price_id: Mapped[UUID] = mapped_column(
        UUID(as_uuid=True), ForeignKey("pricing_campaign_prices.id", ondelete="RESTRICT"), nullable=False
    )
    subject_key: Mapped[str] = mapped_column(String(320), nullable=False)
    workspace_id: Mapped[Optional[UUID]] = mapped_column(
        UUID(as_uuid=True), ForeignKey("workspaces.id", ondelete="SET NULL"), nullable=True
    )
    status: Mapped[str] = mapped_column(String(20), nullable=False, default="reserved")
    stripe_checkout_session_id: Mapped[Optional[str]] = mapped_column(String(100), nullable=True, unique=True)
    stripe_subscription_id: Mapped[Optional[str]] = mapped_column(String(100), nullable=True)
    expires_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    confirmed_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())