"""Relational licensing authority models.

Production migrations target PostgreSQL. Constraints here protect unique serial
redemption, external identities, installation keys, provider events, and
idempotent request replay in addition to service-level transaction checks.
"""

from __future__ import annotations

from datetime import datetime
from typing import Any, Optional

from sqlalchemy import (
    Boolean,
    CheckConstraint,
    DateTime,
    ForeignKey,
    Index,
    Integer,
    JSON,
    LargeBinary,
    String,
    UniqueConstraint,
    func,
    text,
)
from sqlalchemy.orm import Mapped, mapped_column, relationship

from licensing_shared.constants import TRIAL_MAXIMUM_TOTAL_DURATION_HOURS

from .database import Base


IDENTIFIER_LENGTH = 128
EXTERNAL_ID_LENGTH = 256
DISPLAY_NAME_LENGTH = 256
PROVIDER_ID_LENGTH = 256
DIGEST_BYTES = 32


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


class User(TimestampMixin, Base):
    __tablename__ = "users"

    id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), primary_key=True)
    external_issuer: Mapped[str] = mapped_column(String(EXTERNAL_ID_LENGTH), nullable=False)
    external_subject: Mapped[str] = mapped_column(String(EXTERNAL_ID_LENGTH), nullable=False)
    verified_email: Mapped[Optional[str]] = mapped_column(String(EXTERNAL_ID_LENGTH))
    status: Mapped[str] = mapped_column(String(32), nullable=False, default="active")
    is_server_admin: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)

    __table_args__ = (
        UniqueConstraint(
            "external_issuer",
            "external_subject",
            name="uq_users_external_identity",
        ),
    )


class PrivacyRequest(TimestampMixin, Base):
    __tablename__ = "privacy_requests"

    id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), primary_key=True)
    user_id: Mapped[str] = mapped_column(
        ForeignKey("users.id"),
        nullable=False,
    )
    request_type: Mapped[str] = mapped_column(String(32), nullable=False)
    status: Mapped[str] = mapped_column(String(32), nullable=False, default="pending")
    requested_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    request_digest: Mapped[bytes] = mapped_column(
        LargeBinary(DIGEST_BYTES),
        nullable=False,
    )
    idempotency_key: Mapped[str] = mapped_column(String(128), nullable=False)
    correlation_id: Mapped[str] = mapped_column(
        String(IDENTIFIER_LENGTH),
        nullable=False,
    )
    state_generation: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    cancelled_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    completed_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    completed_by_user_id: Mapped[Optional[str]] = mapped_column(ForeignKey("users.id"))
    completion_reason: Mapped[Optional[str]] = mapped_column(String(64))
    completion_idempotency_key: Mapped[Optional[str]] = mapped_column(String(128))
    completion_request_digest: Mapped[Optional[bytes]] = mapped_column(
        LargeBinary(DIGEST_BYTES)
    )
    completion_summary_json: Mapped[dict[str, Any]] = mapped_column(
        JSON,
        nullable=False,
        default=dict,
    )

    __table_args__ = (
        CheckConstraint(
            "request_type = 'deletion'",
            name="ck_privacy_requests_type",
        ),
        CheckConstraint(
            "status IN ('pending', 'cancelled', 'completed')",
            name="ck_privacy_requests_status",
        ),
        CheckConstraint(
            "state_generation >= 0",
            name="ck_privacy_requests_generation",
        ),
        CheckConstraint(
            "(status = 'pending' AND cancelled_at IS NULL AND completed_at IS NULL "
            "AND completed_by_user_id IS NULL AND completion_reason IS NULL "
            "AND completion_idempotency_key IS NULL "
            "AND completion_request_digest IS NULL) OR "
            "(status = 'cancelled' AND cancelled_at IS NOT NULL "
            "AND completed_at IS NULL AND completed_by_user_id IS NULL "
            "AND completion_reason IS NULL AND completion_idempotency_key IS NULL "
            "AND completion_request_digest IS NULL) OR "
            "(status = 'completed' AND cancelled_at IS NULL "
            "AND completed_at IS NOT NULL AND completed_by_user_id IS NOT NULL "
            "AND completion_reason IS NOT NULL "
            "AND completion_idempotency_key IS NOT NULL "
            "AND completion_request_digest IS NOT NULL)",
            name="ck_privacy_requests_lifecycle",
        ),
        UniqueConstraint(
            "user_id",
            "idempotency_key",
            name="uq_privacy_requests_user_idempotency",
        ),
        Index(
            "uq_privacy_requests_pending_user",
            "user_id",
            unique=True,
            sqlite_where=text("status = 'pending'"),
            postgresql_where=text("status = 'pending'"),
        ),
        Index(
            "ix_privacy_requests_status_requested",
            "status",
            "requested_at",
        ),
    )


class Organization(TimestampMixin, Base):
    __tablename__ = "organizations"

    id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), primary_key=True)
    display_name: Mapped[str] = mapped_column(String(DISPLAY_NAME_LENGTH), nullable=False)
    status: Mapped[str] = mapped_column(String(32), nullable=False, default="active")


class Membership(TimestampMixin, Base):
    __tablename__ = "memberships"

    id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), primary_key=True)
    organization_id: Mapped[str] = mapped_column(
        ForeignKey("organizations.id", ondelete="CASCADE"),
        nullable=False,
    )
    user_id: Mapped[str] = mapped_column(
        ForeignKey("users.id", ondelete="CASCADE"),
        nullable=False,
    )
    role: Mapped[str] = mapped_column(String(32), nullable=False)
    status: Mapped[str] = mapped_column(String(32), nullable=False, default="active")
    valid_until: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))

    organization: Mapped[Organization] = relationship()
    user: Mapped[User] = relationship()

    __table_args__ = (
        UniqueConstraint(
            "organization_id",
            "user_id",
            name="uq_memberships_organization_user",
        ),
    )


class OrganizationInvitation(TimestampMixin, Base):
    __tablename__ = "organization_invitations"

    id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), primary_key=True)
    organization_id: Mapped[str] = mapped_column(
        ForeignKey("organizations.id", ondelete="CASCADE"),
        nullable=False,
    )
    invited_email_digest: Mapped[bytes] = mapped_column(
        LargeBinary(DIGEST_BYTES),
        nullable=False,
    )
    token_digest: Mapped[bytes] = mapped_column(
        LargeBinary(DIGEST_BYTES),
        nullable=False,
        unique=True,
    )
    role: Mapped[str] = mapped_column(String(32), nullable=False, default="member")
    status: Mapped[str] = mapped_column(String(32), nullable=False, default="pending")
    expires_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    created_by_user_id: Mapped[str] = mapped_column(
        ForeignKey("users.id"),
        nullable=False,
    )
    accepted_by_user_id: Mapped[Optional[str]] = mapped_column(ForeignKey("users.id"))
    accepted_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))

    __table_args__ = (
        CheckConstraint("expires_at > created_at", name="ck_organization_invitation_expiry"),
        CheckConstraint(
            "(status = 'accepted' AND accepted_by_user_id IS NOT NULL AND accepted_at IS NOT NULL) "
            "OR (status <> 'accepted' AND accepted_by_user_id IS NULL AND accepted_at IS NULL)",
            name="ck_organization_invitation_lifecycle",
        ),
        Index(
            "ix_organization_invitations_pending_email",
            "organization_id",
            "status",
            "invited_email_digest",
        ),
    )


class ProductSurfaceProfile(TimestampMixin, Base):
    __tablename__ = "product_surface_profiles"

    id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), primary_key=True)
    product_id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), nullable=False)
    name: Mapped[str] = mapped_column(String(DISPLAY_NAME_LENGTH), nullable=False)
    description: Mapped[str] = mapped_column(String(512), nullable=False)
    status: Mapped[str] = mapped_column(String(32), nullable=False, default="active")
    inventory_revision: Mapped[int] = mapped_column(Integer, nullable=False)
    inventory_sha256: Mapped[str] = mapped_column(String(64), nullable=False)
    analysis_ids_json: Mapped[list[str]] = mapped_column(JSON, nullable=False)
    main_tab_ids_json: Mapped[list[str]] = mapped_column(JSON, nullable=False)
    created_by_user_id: Mapped[str] = mapped_column(ForeignKey("users.id"), nullable=False)

    __table_args__ = (
        UniqueConstraint(
            "product_id",
            "name",
            name="uq_product_surface_profiles_product_name",
        ),
        CheckConstraint(
            "status IN ('active', 'archived')",
            name="ck_product_surface_profiles_status",
        ),
        CheckConstraint(
            "inventory_revision > 0",
            name="ck_product_surface_profiles_inventory_revision",
        ),
        Index(
            "ix_product_surface_profiles_product_status",
            "product_id",
            "status",
        ),
    )


class License(TimestampMixin, Base):
    __tablename__ = "licenses"

    id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), primary_key=True)
    product_id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), nullable=False)
    owner_user_id: Mapped[Optional[str]] = mapped_column(ForeignKey("users.id"))
    owner_organization_id: Mapped[Optional[str]] = mapped_column(
        ForeignKey("organizations.id")
    )
    anonymous_subject_digest: Mapped[Optional[bytes]] = mapped_column(LargeBinary(DIGEST_BYTES))
    subject_type: Mapped[str] = mapped_column(String(32), nullable=False)
    status: Mapped[str] = mapped_column(String(32), nullable=False, default="active")
    device_policy_id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), nullable=False)
    surface_profile_id: Mapped[Optional[str]] = mapped_column(
        ForeignKey("product_surface_profiles.id")
    )
    revocation_generation: Mapped[int] = mapped_column(Integer, nullable=False, default=0)

    __table_args__ = (
        CheckConstraint(
            "(CASE WHEN owner_user_id IS NULL THEN 0 ELSE 1 END + "
            "CASE WHEN owner_organization_id IS NULL THEN 0 ELSE 1 END + "
            "CASE WHEN anonymous_subject_digest IS NULL THEN 0 ELSE 1 END) = 1",
            name="ck_licenses_exactly_one_owner",
        ),
        Index("ix_licenses_owner_user", "owner_user_id"),
        Index("ix_licenses_owner_organization", "owner_organization_id"),
        Index("ix_licenses_anonymous_subject", "anonymous_subject_digest"),
    )


class OrganizationSeat(TimestampMixin, Base):
    __tablename__ = "organization_seats"

    id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), primary_key=True)
    organization_id: Mapped[str] = mapped_column(
        ForeignKey("organizations.id", ondelete="CASCADE"),
        nullable=False,
    )
    license_id: Mapped[str] = mapped_column(
        ForeignKey("licenses.id", ondelete="CASCADE"),
        nullable=False,
    )
    assigned_user_id: Mapped[Optional[str]] = mapped_column(ForeignKey("users.id"))
    status: Mapped[str] = mapped_column(String(32), nullable=False, default="available")
    assigned_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    released_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    last_reassigned_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    assignment_generation: Mapped[int] = mapped_column(Integer, nullable=False, default=0)

    __table_args__ = (
        CheckConstraint(
            "(status = 'assigned' AND assigned_user_id IS NOT NULL AND assigned_at IS NOT NULL) "
            "OR (status IN ('available', 'suspended') AND assigned_user_id IS NULL "
            "AND assigned_at IS NULL)",
            name="ck_organization_seats_lifecycle",
        ),
        CheckConstraint(
            "assignment_generation >= 0",
            name="ck_organization_seats_assignment_generation",
        ),
        Index(
            "ix_organization_seats_license_status",
            "license_id",
            "status",
        ),
        Index(
            "ix_organization_seats_assignee",
            "organization_id",
            "assigned_user_id",
            "status",
        ),
        Index(
            "uq_organization_seats_license_assigned_user",
            "license_id",
            "assigned_user_id",
            unique=True,
            sqlite_where=text("status = 'assigned'"),
            postgresql_where=text("status = 'assigned'"),
        ),
    )


class Grant(TimestampMixin, Base):
    __tablename__ = "grants"

    id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), primary_key=True)
    license_id: Mapped[str] = mapped_column(
        ForeignKey("licenses.id", ondelete="CASCADE"),
        nullable=False,
    )
    sku_id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), nullable=False)
    source_type: Mapped[str] = mapped_column(String(64), nullable=False)
    source_reference: Mapped[str] = mapped_column(String(EXTERNAL_ID_LENGTH), nullable=False)
    catalog_revision: Mapped[int] = mapped_column(Integer, nullable=False)
    starts_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    ends_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    status: Mapped[str] = mapped_column(String(32), nullable=False, default="active")
    metadata_json: Mapped[dict[str, Any]] = mapped_column(JSON, nullable=False, default=dict)

    license: Mapped[License] = relationship()

    __table_args__ = (
        UniqueConstraint(
            "source_type",
            "source_reference",
            name="uq_grants_source",
        ),
        CheckConstraint("ends_at IS NULL OR ends_at > starts_at", name="ck_grants_validity"),
        Index("ix_grants_license_validity", "license_id", "status", "starts_at", "ends_at"),
    )


class SerialBatch(TimestampMixin, Base):
    __tablename__ = "serial_batches"

    id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), primary_key=True)
    sku_id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), nullable=False)
    quantity: Mapped[int] = mapped_column(Integer, nullable=False)
    campaign: Mapped[Optional[str]] = mapped_column(String(DISPLAY_NAME_LENGTH))
    reason: Mapped[str] = mapped_column(String(512), nullable=False)
    created_by_user_id: Mapped[str] = mapped_column(ForeignKey("users.id"), nullable=False)
    surface_profile_id: Mapped[Optional[str]] = mapped_column(
        ForeignKey("product_surface_profiles.id")
    )
    trial_duration_hours: Mapped[Optional[int]] = mapped_column(Integer)
    status: Mapped[str] = mapped_column(String(32), nullable=False, default="active")
    redemption_deadline: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    revoked_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    revoked_by_user_id: Mapped[Optional[str]] = mapped_column(ForeignKey("users.id"))
    revocation_reason: Mapped[Optional[str]] = mapped_column(String(64))

    __table_args__ = (
        CheckConstraint("quantity > 0", name="ck_serial_batches_quantity"),
        CheckConstraint(
            "trial_duration_hours IS NULL OR "
            "(trial_duration_hours >= 1 AND trial_duration_hours <= "
            f"{TRIAL_MAXIMUM_TOTAL_DURATION_HOURS})",
            name="ck_serial_batches_trial_duration_hours",
        ),
        CheckConstraint(
            "status IN ('active', 'revoked')",
            name="ck_serial_batches_status",
        ),
        CheckConstraint(
            "(status = 'active' AND revoked_at IS NULL AND "
            "revoked_by_user_id IS NULL AND revocation_reason IS NULL) OR "
            "(status = 'revoked' AND revoked_at IS NOT NULL AND "
            "revoked_by_user_id IS NOT NULL AND revocation_reason IS NOT NULL)",
            name="ck_serial_batches_revocation_lifecycle",
        ),
    )


class Serial(TimestampMixin, Base):
    __tablename__ = "serials"

    id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), primary_key=True)
    batch_id: Mapped[str] = mapped_column(
        ForeignKey("serial_batches.id", ondelete="CASCADE"),
        nullable=False,
    )
    lookup_id: Mapped[str] = mapped_column(String(16), nullable=False, unique=True)
    secret_digest: Mapped[bytes] = mapped_column(LargeBinary(DIGEST_BYTES), nullable=False)
    status: Mapped[str] = mapped_column(String(32), nullable=False, default="active")
    reserved_user_id: Mapped[Optional[str]] = mapped_column(ForeignKey("users.id"))
    redeemed_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    redeemed_by_user_id: Mapped[Optional[str]] = mapped_column(ForeignKey("users.id"))
    redeemed_license_id: Mapped[Optional[str]] = mapped_column(ForeignKey("licenses.id"))

    batch: Mapped[SerialBatch] = relationship()

    __table_args__ = (
        CheckConstraint(
            "status IN ('active', 'redeemed', 'revoked')",
            name="ck_serials_status",
        ),
    )


class SerialRedemption(TimestampMixin, Base):
    __tablename__ = "serial_redemptions"

    id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), primary_key=True)
    serial_id: Mapped[str] = mapped_column(ForeignKey("serials.id"), nullable=False)
    license_id: Mapped[str] = mapped_column(ForeignKey("licenses.id"), nullable=False)
    grant_id: Mapped[str] = mapped_column(ForeignKey("grants.id"), nullable=False)
    activation_id: Mapped[str] = mapped_column(ForeignKey("activations.id"), nullable=False)
    actor_user_id: Mapped[Optional[str]] = mapped_column(ForeignKey("users.id"))
    idempotency_key: Mapped[str] = mapped_column(String(128), nullable=False)
    snapshot_id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), nullable=False)

    __table_args__ = (
        UniqueConstraint("serial_id", name="uq_serial_redemptions_serial"),
        UniqueConstraint(
            "actor_user_id",
            "idempotency_key",
            name="uq_serial_redemptions_actor_idempotency",
        ),
    )


class Device(TimestampMixin, Base):
    __tablename__ = "devices"

    id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), primary_key=True)
    owner_user_id: Mapped[Optional[str]] = mapped_column(ForeignKey("users.id"))
    owner_organization_id: Mapped[Optional[str]] = mapped_column(ForeignKey("organizations.id"))
    friendly_name: Mapped[str] = mapped_column(String(128), nullable=False)
    device_type: Mapped[str] = mapped_column(String(32), nullable=False, default="computer")
    status: Mapped[str] = mapped_column(String(32), nullable=False, default="active")
    evidence_schema_version: Mapped[int] = mapped_column(Integer, nullable=False, default=1)
    evidence_digests: Mapped[dict[str, str]] = mapped_column(JSON, nullable=False, default=dict)
    last_seen_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)


class DeviceInstallation(TimestampMixin, Base):
    __tablename__ = "device_installations"

    id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), primary_key=True)
    installation_id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), nullable=False, unique=True)
    device_id: Mapped[str] = mapped_column(
        ForeignKey("devices.id", ondelete="CASCADE"),
        nullable=False,
    )
    public_key_der: Mapped[bytes] = mapped_column(LargeBinary, nullable=False)
    key_thumbprint: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), nullable=False, unique=True)
    key_provider: Mapped[str] = mapped_column(String(64), nullable=False)
    status: Mapped[str] = mapped_column(String(32), nullable=False, default="active")
    last_seen_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)

    device: Mapped[Device] = relationship()


class Activation(TimestampMixin, Base):
    __tablename__ = "activations"

    id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), primary_key=True)
    license_id: Mapped[str] = mapped_column(ForeignKey("licenses.id"), nullable=False)
    device_id: Mapped[str] = mapped_column(ForeignKey("devices.id"), nullable=False)
    installation_id: Mapped[str] = mapped_column(
        ForeignKey("device_installations.id"),
        nullable=False,
    )
    seat_id: Mapped[Optional[str]] = mapped_column(String(IDENTIFIER_LENGTH))
    status: Mapped[str] = mapped_column(String(32), nullable=False, default="active")
    activated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    deactivated_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    release_reason: Mapped[Optional[str]] = mapped_column(String(512))

    __table_args__ = (
        CheckConstraint(
            "(status = 'active' AND deactivated_at IS NULL) OR "
            "(status <> 'active' AND deactivated_at IS NOT NULL)",
            name="ck_activations_lifecycle",
        ),
        Index("ix_activations_license_status", "license_id", "status"),
        Index("ix_activations_device_status", "device_id", "status"),
    )


class Trial(TimestampMixin, Base):
    __tablename__ = "trials"

    id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), primary_key=True)
    license_id: Mapped[str] = mapped_column(ForeignKey("licenses.id"), nullable=False)
    grant_id: Mapped[str] = mapped_column(ForeignKey("grants.id"), nullable=False)
    subject_digest: Mapped[bytes] = mapped_column(LargeBinary(DIGEST_BYTES), nullable=False)
    starts_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    ends_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    status: Mapped[str] = mapped_column(String(32), nullable=False, default="active")
    extension_count: Mapped[int] = mapped_column(Integer, nullable=False, default=0)

    __table_args__ = (
        UniqueConstraint("subject_digest", name="uq_trials_subject_digest"),
        CheckConstraint("ends_at > starts_at", name="ck_trials_validity"),
        CheckConstraint("extension_count >= 0", name="ck_trials_extension_count"),
    )


class Subscription(TimestampMixin, Base):
    __tablename__ = "subscriptions"

    id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), primary_key=True)
    license_id: Mapped[str] = mapped_column(ForeignKey("licenses.id"), nullable=False)
    provider: Mapped[str] = mapped_column(String(32), nullable=False)
    provider_subscription_id: Mapped[str] = mapped_column(String(PROVIDER_ID_LENGTH), nullable=False)
    provider_customer_id: Mapped[Optional[str]] = mapped_column(String(PROVIDER_ID_LENGTH))
    status: Mapped[str] = mapped_column(String(32), nullable=False)
    current_period_start: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    current_period_end: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    cancel_at_period_end: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
    grace_ends_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    provider_event_created_at: Mapped[Optional[datetime]] = mapped_column(
        DateTime(timezone=True)
    )
    billing_hold_status: Mapped[Optional[str]] = mapped_column(String(32))
    billing_hold_provider_id: Mapped[Optional[str]] = mapped_column(
        String(PROVIDER_ID_LENGTH)
    )
    billing_hold_event_created_at: Mapped[Optional[datetime]] = mapped_column(
        DateTime(timezone=True)
    )

    __table_args__ = (
        UniqueConstraint(
            "provider",
            "provider_subscription_id",
            name="uq_subscriptions_provider_id",
        ),
        CheckConstraint(
            "current_period_end > current_period_start",
            name="ck_subscriptions_period",
        ),
        CheckConstraint(
            "billing_hold_status IS NULL OR billing_hold_status IN "
            "('dispute_open', 'dispute_lost', 'refunded')",
            name="ck_subscriptions_billing_hold",
        ),
    )


class SubscriptionItem(TimestampMixin, Base):
    __tablename__ = "subscription_items"

    id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), primary_key=True)
    subscription_id: Mapped[str] = mapped_column(
        ForeignKey("subscriptions.id", ondelete="CASCADE"),
        nullable=False,
    )
    sku_id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), nullable=False)
    quantity: Mapped[int] = mapped_column(Integer, nullable=False, default=1)
    provider_item_id: Mapped[Optional[str]] = mapped_column(String(PROVIDER_ID_LENGTH))

    __table_args__ = (
        CheckConstraint("quantity > 0", name="ck_subscription_items_quantity"),
        UniqueConstraint(
            "subscription_id",
            "sku_id",
            name="uq_subscription_items_sku",
        ),
    )


class Lease(TimestampMixin, Base):
    __tablename__ = "leases"

    id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), primary_key=True)
    snapshot_id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), nullable=False, unique=True)
    license_id: Mapped[str] = mapped_column(ForeignKey("licenses.id"), nullable=False)
    activation_id: Mapped[str] = mapped_column(ForeignKey("activations.id"), nullable=False)
    signing_key_id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), nullable=False)
    snapshot_digest: Mapped[bytes] = mapped_column(LargeBinary(DIGEST_BYTES), nullable=False)
    issued_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    expires_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    revocation_generation: Mapped[int] = mapped_column(Integer, nullable=False)
    document_json: Mapped[dict[str, Any]] = mapped_column(JSON, nullable=False)

    __table_args__ = (
        CheckConstraint("expires_at > issued_at", name="ck_leases_validity"),
    )


class OfflineRequest(TimestampMixin, Base):
    __tablename__ = "offline_requests"

    id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), primary_key=True)
    client_request_id: Mapped[str] = mapped_column(
        String(IDENTIFIER_LENGTH),
        nullable=False,
        unique=True,
    )
    request_type: Mapped[str] = mapped_column(String(32), nullable=False)
    status: Mapped[str] = mapped_column(String(32), nullable=False, default="pending")
    request_digest: Mapped[bytes] = mapped_column(LargeBinary(DIGEST_BYTES), nullable=False)
    request_document_json: Mapped[dict[str, Any]] = mapped_column(JSON, nullable=False)
    retrieval_digest: Mapped[bytes] = mapped_column(LargeBinary(DIGEST_BYTES), nullable=False)
    installation_id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), nullable=False)
    device_key_thumbprint: Mapped[str] = mapped_column(
        String(IDENTIFIER_LENGTH),
        nullable=False,
    )
    requested_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    expires_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    decision_generation: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    decided_by_user_id: Mapped[Optional[str]] = mapped_column(ForeignKey("users.id"))
    decision_reason: Mapped[Optional[str]] = mapped_column(String(IDENTIFIER_LENGTH))
    decision_note: Mapped[Optional[str]] = mapped_column(String(512))
    decided_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    decision_idempotency_key: Mapped[Optional[str]] = mapped_column(String(128))
    decision_request_digest: Mapped[Optional[bytes]] = mapped_column(
        LargeBinary(DIGEST_BYTES)
    )
    decision_response_json: Mapped[Optional[dict[str, Any]]] = mapped_column(JSON)

    __table_args__ = (
        CheckConstraint("decision_generation >= 0", name="ck_offline_requests_generation"),
        CheckConstraint("expires_at > requested_at", name="ck_offline_requests_expiry"),
        CheckConstraint(
            "(status = 'pending' AND decided_by_user_id IS NULL AND decided_at IS NULL) OR "
            "(status IN ('approved', 'rejected') AND decided_by_user_id IS NOT NULL "
            "AND decided_at IS NOT NULL)",
            name="ck_offline_requests_decision",
        ),
        Index("ix_offline_requests_status_requested", "status", "requested_at"),
    )


class OfflineCertificate(TimestampMixin, Base):
    __tablename__ = "offline_certificates"

    id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), primary_key=True)
    offline_request_id: Mapped[str] = mapped_column(
        ForeignKey("offline_requests.id", ondelete="CASCADE"),
        nullable=False,
        unique=True,
    )
    license_id: Mapped[str] = mapped_column(ForeignKey("licenses.id"), nullable=False)
    activation_id: Mapped[str] = mapped_column(
        ForeignKey("activations.id"),
        nullable=False,
    )
    lease_id: Mapped[str] = mapped_column(ForeignKey("leases.id"), nullable=False, unique=True)
    file_digest: Mapped[bytes] = mapped_column(LargeBinary(DIGEST_BYTES), nullable=False)
    issued_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    expires_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)

    __table_args__ = (
        CheckConstraint("expires_at > issued_at", name="ck_offline_certificates_validity"),
    )


class IdempotencyRecord(TimestampMixin, Base):
    __tablename__ = "idempotency_records"

    id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), primary_key=True)
    scope: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), nullable=False)
    subject_key: Mapped[str] = mapped_column(String(EXTERNAL_ID_LENGTH), nullable=False)
    idempotency_key: Mapped[str] = mapped_column(String(128), nullable=False)
    request_digest: Mapped[bytes] = mapped_column(LargeBinary(DIGEST_BYTES), nullable=False)
    response_status: Mapped[int] = mapped_column(Integer, nullable=False)
    response_json: Mapped[dict[str, Any]] = mapped_column(JSON, nullable=False)
    expires_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)

    __table_args__ = (
        UniqueConstraint(
            "scope",
            "subject_key",
            "idempotency_key",
            name="uq_idempotency_scope_subject_key",
        ),
    )


class RateLimitBucket(TimestampMixin, Base):
    __tablename__ = "rate_limit_buckets"

    id: Mapped[str] = mapped_column(String(64), primary_key=True)
    policy: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), nullable=False)
    request_count: Mapped[int] = mapped_column(Integer, nullable=False)
    window_started_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        nullable=False,
    )
    expires_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)

    __table_args__ = (
        CheckConstraint("request_count >= 1", name="ck_rate_limit_buckets_count"),
        CheckConstraint(
            "expires_at > window_started_at",
            name="ck_rate_limit_buckets_window",
        ),
        Index("ix_rate_limit_buckets_expiry", "expires_at"),
    )


class WebhookEvent(TimestampMixin, Base):
    __tablename__ = "webhook_events"

    id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), primary_key=True)
    provider: Mapped[str] = mapped_column(String(32), nullable=False)
    provider_event_id: Mapped[str] = mapped_column(String(PROVIDER_ID_LENGTH), nullable=False)
    provider_object_id: Mapped[Optional[str]] = mapped_column(String(PROVIDER_ID_LENGTH))
    signature_valid: Mapped[bool] = mapped_column(Boolean, nullable=False)
    status: Mapped[str] = mapped_column(String(32), nullable=False)
    payload_digest: Mapped[bytes] = mapped_column(LargeBinary(DIGEST_BYTES), nullable=False)
    processed_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    error_code: Mapped[Optional[str]] = mapped_column(String(64))

    __table_args__ = (
        UniqueConstraint(
            "provider",
            "provider_event_id",
            name="uq_webhook_events_provider_event",
        ),
    )


class OutboxEvent(TimestampMixin, Base):
    __tablename__ = "outbox_events"

    id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), primary_key=True)
    event_type: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), nullable=False)
    aggregate_type: Mapped[str] = mapped_column(String(64), nullable=False)
    aggregate_id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), nullable=False)
    payload_json: Mapped[dict[str, Any]] = mapped_column(JSON, nullable=False)
    status: Mapped[str] = mapped_column(String(32), nullable=False, default="pending")
    available_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    attempts: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    processed_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    last_error_code: Mapped[Optional[str]] = mapped_column(String(64))

    __table_args__ = (
        CheckConstraint("attempts >= 0", name="ck_outbox_attempts"),
        Index("ix_outbox_delivery", "status", "available_at"),
    )


class NotificationDelivery(TimestampMixin, Base):
    __tablename__ = "notification_deliveries"

    id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), primary_key=True)
    outbox_event_id: Mapped[str] = mapped_column(
        ForeignKey("outbox_events.id", ondelete="CASCADE"),
        nullable=False,
    )
    user_id: Mapped[str] = mapped_column(
        ForeignKey("users.id", ondelete="CASCADE"),
        nullable=False,
    )
    status: Mapped[str] = mapped_column(String(32), nullable=False, default="pending")
    attempts: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    provider_message_id: Mapped[Optional[str]] = mapped_column(
        String(PROVIDER_ID_LENGTH)
    )
    recipient_address_digest: Mapped[Optional[bytes]] = mapped_column(
        LargeBinary(DIGEST_BYTES)
    )
    last_error_code: Mapped[Optional[str]] = mapped_column(String(64))
    delivered_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    feedback_status: Mapped[Optional[str]] = mapped_column(String(32))
    feedback_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))

    __table_args__ = (
        CheckConstraint("attempts >= 0", name="ck_notification_deliveries_attempts"),
        CheckConstraint(
            "status IN ('pending', 'sending', 'processed', 'failed', 'skipped')",
            name="ck_notification_deliveries_status",
        ),
        UniqueConstraint(
            "outbox_event_id",
            "user_id",
            name="uq_notification_deliveries_outbox_user",
        ),
        Index(
            "ix_notification_deliveries_status",
            "status",
            "updated_at",
        ),
        Index(
            "ix_notification_deliveries_provider_message",
            "provider_message_id",
        ),
    )


class NotificationFeedbackEvent(TimestampMixin, Base):
    __tablename__ = "notification_feedback_events"

    id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), primary_key=True)
    provider: Mapped[str] = mapped_column(String(64), nullable=False)
    provider_event_id: Mapped[str] = mapped_column(
        String(PROVIDER_ID_LENGTH),
        nullable=False,
    )
    delivery_id: Mapped[Optional[str]] = mapped_column(
        ForeignKey("notification_deliveries.id", ondelete="SET NULL"),
    )
    delivery_reference_id: Mapped[str] = mapped_column(
        String(IDENTIFIER_LENGTH),
        nullable=False,
    )
    provider_message_id: Mapped[Optional[str]] = mapped_column(
        String(PROVIDER_ID_LENGTH)
    )
    feedback_type: Mapped[str] = mapped_column(String(32), nullable=False)
    occurred_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    payload_digest: Mapped[bytes] = mapped_column(
        LargeBinary(DIGEST_BYTES),
        nullable=False,
    )
    processed_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)

    __table_args__ = (
        CheckConstraint(
            "feedback_type IN ('soft_bounce', 'hard_bounce', 'complaint')",
            name="ck_notification_feedback_type",
        ),
        UniqueConstraint(
            "provider",
            "provider_event_id",
            name="uq_notification_feedback_provider_event",
        ),
        Index(
            "ix_notification_feedback_delivery",
            "delivery_id",
            "occurred_at",
        ),
    )


class NotificationSuppression(TimestampMixin, Base):
    __tablename__ = "notification_suppressions"

    id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), primary_key=True)
    user_id: Mapped[str] = mapped_column(
        ForeignKey("users.id", ondelete="CASCADE"),
        nullable=False,
    )
    recipient_address_digest: Mapped[bytes] = mapped_column(
        LargeBinary(DIGEST_BYTES),
        nullable=False,
    )
    reason: Mapped[str] = mapped_column(String(32), nullable=False)
    source_feedback_id: Mapped[Optional[str]] = mapped_column(
        ForeignKey("notification_feedback_events.id", ondelete="SET NULL"),
    )
    active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    suppressed_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        nullable=False,
    )
    cleared_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))

    __table_args__ = (
        CheckConstraint(
            "reason IN ('hard_bounce', 'complaint')",
            name="ck_notification_suppression_reason",
        ),
        CheckConstraint(
            "(active = true AND cleared_at IS NULL) OR "
            "(active = false AND cleared_at IS NOT NULL)",
            name="ck_notification_suppression_lifecycle",
        ),
        UniqueConstraint(
            "user_id",
            "recipient_address_digest",
            name="uq_notification_suppression_user_address",
        ),
        Index(
            "ix_notification_suppressions_active",
            "user_id",
            "active",
        ),
    )


class CatalogRelease(TimestampMixin, Base):
    __tablename__ = "catalog_releases"

    id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), primary_key=True)
    product_id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), nullable=False)
    revision: Mapped[int] = mapped_column(Integer, nullable=False)
    document_json: Mapped[dict[str, Any]] = mapped_column(JSON, nullable=False)
    document_digest: Mapped[bytes] = mapped_column(
        LargeBinary(DIGEST_BYTES),
        nullable=False,
    )
    status: Mapped[str] = mapped_column(String(32), nullable=False)
    staged_by_user_id: Mapped[str] = mapped_column(
        ForeignKey("users.id"),
        nullable=False,
    )
    staged_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    published_by_user_id: Mapped[Optional[str]] = mapped_column(ForeignKey("users.id"))
    published_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    retired_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))

    __table_args__ = (
        CheckConstraint("revision >= 1", name="ck_catalog_releases_revision"),
        CheckConstraint(
            "status IN ('staged', 'active', 'retired')",
            name="ck_catalog_releases_status",
        ),
        CheckConstraint(
            "(status = 'staged' AND published_by_user_id IS NULL "
            "AND published_at IS NULL AND retired_at IS NULL) OR "
            "(status = 'active' AND published_by_user_id IS NOT NULL "
            "AND published_at IS NOT NULL AND retired_at IS NULL) OR "
            "(status = 'retired' AND published_by_user_id IS NOT NULL "
            "AND published_at IS NOT NULL AND retired_at IS NOT NULL)",
            name="ck_catalog_releases_lifecycle",
        ),
        UniqueConstraint(
            "product_id",
            "revision",
            name="uq_catalog_releases_product_revision",
        ),
        Index(
            "uq_catalog_releases_active_product",
            "product_id",
            unique=True,
            sqlite_where=text("status = 'active'"),
            postgresql_where=text("status = 'active'"),
        ),
        Index(
            "ix_catalog_releases_product_revision",
            "product_id",
            "revision",
        ),
    )


class SigningKeyMetadata(TimestampMixin, Base):
    __tablename__ = "signing_key_metadata"

    id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), primary_key=True)
    key_id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), nullable=False, unique=True)
    purpose: Mapped[str] = mapped_column(String(64), nullable=False)
    public_key_bytes: Mapped[bytes] = mapped_column(
        LargeBinary(DIGEST_BYTES),
        nullable=False,
    )
    status: Mapped[str] = mapped_column(String(32), nullable=False)
    not_before: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    expires_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    activated_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))
    retired_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True))

    __table_args__ = (
        CheckConstraint(
            "status IN ('staged', 'active', 'retired', 'compromised')",
            name="ck_signing_key_metadata_status",
        ),
        CheckConstraint(
            "status = 'compromised' OR expires_at IS NULL OR "
            "expires_at > not_before",
            name="ck_signing_key_metadata_validity",
        ),
        CheckConstraint(
            "(status = 'staged' AND activated_at IS NULL AND retired_at IS NULL) OR "
            "(status = 'active' AND activated_at IS NOT NULL AND retired_at IS NULL) OR "
            "(status IN ('retired', 'compromised') AND retired_at IS NOT NULL)",
            name="ck_signing_key_metadata_lifecycle",
        ),
        CheckConstraint(
            "status NOT IN ('retired', 'compromised') OR "
            "(expires_at IS NOT NULL AND expires_at <= retired_at)",
            name="ck_signing_key_metadata_retirement",
        ),
        Index(
            "uq_signing_key_metadata_active_purpose",
            "purpose",
            unique=True,
            sqlite_where=text("status = 'active'"),
            postgresql_where=text("status = 'active'"),
        ),
    )


class AuditEvent(Base):
    __tablename__ = "audit_events"

    id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), primary_key=True)
    occurred_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        nullable=False,
        server_default=func.now(),
    )
    actor_type: Mapped[str] = mapped_column(String(32), nullable=False)
    actor_id: Mapped[Optional[str]] = mapped_column(String(IDENTIFIER_LENGTH))
    action: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), nullable=False)
    target_type: Mapped[str] = mapped_column(String(64), nullable=False)
    target_id: Mapped[Optional[str]] = mapped_column(String(IDENTIFIER_LENGTH))
    reason: Mapped[Optional[str]] = mapped_column(String(512))
    correlation_id: Mapped[str] = mapped_column(String(IDENTIFIER_LENGTH), nullable=False)
    source_address_digest: Mapped[Optional[bytes]] = mapped_column(LargeBinary(DIGEST_BYTES))
    metadata_json: Mapped[dict[str, Any]] = mapped_column(JSON, nullable=False, default=dict)

    __table_args__ = (
        Index("ix_audit_events_target", "target_type", "target_id", "occurred_at"),
        Index("ix_audit_events_actor", "actor_type", "actor_id", "occurred_at"),
    )


__all__ = [
    "Activation",
    "AuditEvent",
    "CatalogRelease",
    "Device",
    "DeviceInstallation",
    "Grant",
    "IdempotencyRecord",
    "Lease",
    "License",
    "Membership",
    "NotificationDelivery",
    "NotificationFeedbackEvent",
    "NotificationSuppression",
    "OfflineCertificate",
    "OfflineRequest",
    "Organization",
    "OrganizationInvitation",
    "OrganizationSeat",
    "OutboxEvent",
    "PrivacyRequest",
    "RateLimitBucket",
    "Serial",
    "SerialBatch",
    "SerialRedemption",
    "SigningKeyMetadata",
    "Subscription",
    "SubscriptionItem",
    "Trial",
    "User",
    "WebhookEvent",
]