"""Control-plane SQLAlchemy models for ``absolutems_control_plane``.

These models define the platform metadata layer that wraps the existing apps.
They are intentionally isolated from application-specific schemas under
``/apps`` and can be migrated later without touching existing business logic.
"""

from __future__ import annotations

import uuid
from datetime import datetime
from typing import Optional

from sqlalchemy import JSON, Boolean, DateTime, ForeignKey, Integer, Numeric, String, Text, UniqueConstraint
from sqlalchemy.orm import Mapped, mapped_column, relationship

from config.db import Base


def _uuid() -> str:
    return str(uuid.uuid4())


class TimestampMixin:
    """Shared created/updated timestamps."""

    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow, nullable=False)
    updated_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, nullable=False)


class Tenant(Base, TimestampMixin):
    """A customer tenant registered in the control plane."""

    __tablename__ = "tenants"

    tenant_id: Mapped[str] = mapped_column(String(64), primary_key=True)
    slug: Mapped[str] = mapped_column(String(64), unique=True, nullable=False)
    name: Mapped[str] = mapped_column(String(255), nullable=False)
    status: Mapped[str] = mapped_column(String(32), default="active", nullable=False)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    plan_id: Mapped[str | None] = mapped_column(String(64), ForeignKey("plans.plan_id"), nullable=True)
    data_db_name: Mapped[str | None] = mapped_column(String(128), nullable=True)
    storage_key: Mapped[str | None] = mapped_column(String(128), nullable=True)
    timezone: Mapped[str | None] = mapped_column(String(64), nullable=True)
    metadata_json: Mapped[dict | None] = mapped_column("metadata", JSON, nullable=True)

    domains: Mapped[list["TenantDomain"]] = relationship(back_populates="tenant", cascade="all, delete-orphan")
    apps: Mapped[list["TenantApp"]] = relationship(back_populates="tenant", cascade="all, delete-orphan")
    limits: Mapped[list["TenantLimit"]] = relationship(back_populates="tenant", cascade="all, delete-orphan")
    usage_records: Mapped[list["TenantUsage"]] = relationship(back_populates="tenant", cascade="all, delete-orphan")
    secrets: Mapped[list["TenantSecret"]] = relationship(back_populates="tenant", cascade="all, delete-orphan")
    integrations: Mapped[list["TenantIntegration"]] = relationship(back_populates="tenant", cascade="all, delete-orphan")
    audit_events: Mapped[list["AuditEvent"]] = relationship(back_populates="tenant", cascade="all, delete-orphan")
    jobs: Mapped[list["JobRegistryRecord"]] = relationship(back_populates="tenant", cascade="all, delete-orphan")


class TenantDomain(Base, TimestampMixin):
    """Domains assigned to a tenant."""

    __tablename__ = "tenant_domains"
    __table_args__ = (UniqueConstraint("domain", name="uq_tenant_domains_domain"),)

    domain_id: Mapped[str] = mapped_column(String(36), primary_key=True, default=_uuid)
    tenant_id: Mapped[str] = mapped_column(String(64), ForeignKey("tenants.tenant_id"), nullable=False, index=True)
    domain: Mapped[str] = mapped_column(String(255), nullable=False)
    is_primary: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    is_verified: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)

    tenant: Mapped["Tenant"] = relationship(back_populates="domains")


class PlatformSite(Base, TimestampMixin):
    """A WordPress or portal site authorized to act for one tenant."""

    __tablename__ = "platform_sites"

    site_id: Mapped[str] = mapped_column(String(64), primary_key=True)
    tenant_id: Mapped[str] = mapped_column(String(64), ForeignKey("tenants.tenant_id"), nullable=False, index=True)
    issuer: Mapped[str] = mapped_column(String(255), nullable=False)
    audience: Mapped[str] = mapped_column(String(128), nullable=False)
    domain: Mapped[str | None] = mapped_column(String(255), nullable=True)
    status: Mapped[str] = mapped_column(String(32), nullable=False, default="active")
    last_seen_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    metadata_json: Mapped[dict | None] = mapped_column("metadata", JSON, nullable=True)


class SiteSigningKey(Base, TimestampMixin):
    """Versioned public signing key belonging to a registered platform site."""

    __tablename__ = "site_signing_keys"

    key_id: Mapped[str] = mapped_column(String(128), primary_key=True)
    site_id: Mapped[str] = mapped_column(String(64), ForeignKey("platform_sites.site_id"), nullable=False, index=True)
    algorithm: Mapped[str] = mapped_column(String(16), nullable=False, default="RS256")
    public_key_pem: Mapped[str] = mapped_column(Text, nullable=False)
    status: Mapped[str] = mapped_column(String(32), nullable=False, default="active")
    activated_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow, nullable=False)
    revoked_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)


class JWTReplayRecord(Base):
    """A consumed external JWT identifier used to reject token replay."""

    __tablename__ = "jwt_replay_records"

    jti: Mapped[str] = mapped_column(String(128), primary_key=True)
    site_id: Mapped[str] = mapped_column(String(64), ForeignKey("platform_sites.site_id"), nullable=False, index=True)
    expires_at: Mapped[datetime] = mapped_column(DateTime, nullable=False, index=True)
    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow, nullable=False)


class App(Base, TimestampMixin):
    """App catalog entry for an existing module under ``/apps``."""

    __tablename__ = "apps"

    app_id: Mapped[str] = mapped_column(String(64), primary_key=True)
    display_name: Mapped[str] = mapped_column(String(255), nullable=False)
    module_path: Mapped[str] = mapped_column(String(255), nullable=False)
    route_prefix: Mapped[str] = mapped_column(String(255), nullable=False)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    metadata_json: Mapped[dict | None] = mapped_column("metadata", JSON, nullable=True)

    tenant_apps: Mapped[list["TenantApp"]] = relationship(back_populates="app")
    tenant_limits: Mapped[list["TenantLimit"]] = relationship(back_populates="app")
    tenant_usage: Mapped[list["TenantUsage"]] = relationship(back_populates="app")
    tenant_secrets: Mapped[list["TenantSecret"]] = relationship(back_populates="app")
    integrations: Mapped[list["TenantIntegration"]] = relationship(back_populates="app")
    audit_events: Mapped[list["AuditEvent"]] = relationship(back_populates="app")
    jobs: Mapped[list["JobRegistryRecord"]] = relationship(back_populates="app")


class TenantApp(Base, TimestampMixin):
    """App enablement and routing settings per tenant."""

    __tablename__ = "tenant_apps"
    __table_args__ = (UniqueConstraint("tenant_id", "app_id", name="uq_tenant_apps_tenant_app"),)

    tenant_app_id: Mapped[str] = mapped_column(String(36), primary_key=True, default=_uuid)
    tenant_id: Mapped[str] = mapped_column(String(64), ForeignKey("tenants.tenant_id"), nullable=False, index=True)
    app_id: Mapped[str] = mapped_column(String(64), ForeignKey("apps.app_id"), nullable=False, index=True)
    is_enabled: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    config_json: Mapped[dict | None] = mapped_column("config", JSON, nullable=True)

    tenant: Mapped["Tenant"] = relationship(back_populates="apps")
    app: Mapped["App"] = relationship(back_populates="tenant_apps")


class Plan(Base, TimestampMixin):
    """Commercial plan for tenant entitlements."""

    __tablename__ = "plans"

    plan_id: Mapped[str] = mapped_column(String(64), primary_key=True)
    name: Mapped[str] = mapped_column(String(255), nullable=False)
    code: Mapped[str] = mapped_column(String(64), unique=True, nullable=False)
    price_monthly: Mapped[float | None] = mapped_column(Numeric(12, 2), nullable=True)
    currency: Mapped[str] = mapped_column(String(16), default="AUD", nullable=False)
    description: Mapped[str | None] = mapped_column(Text, nullable=True)
    metadata_json: Mapped[dict | None] = mapped_column("metadata", JSON, nullable=True)

    tenants: Mapped[list["Tenant"]] = relationship()


class TenantSubscription(Base, TimestampMixin):
    """Commercial subscription state controlling tenant access."""

    __tablename__ = "tenant_subscriptions"
    __table_args__ = (UniqueConstraint("tenant_id", name="uq_tenant_subscriptions_tenant"),)

    subscription_id: Mapped[str] = mapped_column(String(64), primary_key=True, default=_uuid)
    tenant_id: Mapped[str] = mapped_column(String(64), ForeignKey("tenants.tenant_id"), nullable=False, index=True)
    plan_id: Mapped[str] = mapped_column(String(64), ForeignKey("plans.plan_id"), nullable=False, index=True)
    provider: Mapped[str] = mapped_column(String(32), nullable=False, default="manual")
    provider_customer_id: Mapped[str | None] = mapped_column(String(128), nullable=True)
    provider_subscription_id: Mapped[str | None] = mapped_column(String(128), nullable=True)
    status: Mapped[str] = mapped_column(String(32), nullable=False, default="active")
    current_period_start: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    current_period_end: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    cancel_at_period_end: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
    metadata_json: Mapped[dict | None] = mapped_column("metadata", JSON, nullable=True)


class BillingProductMapping(Base, TimestampMixin):
    """Map a payment-provider price to one platform plan."""

    __tablename__ = "billing_product_mappings"
    __table_args__ = (
        UniqueConstraint("provider", "price_id", name="uq_billing_mapping_provider_price"),
    )

    mapping_id: Mapped[str] = mapped_column(String(64), primary_key=True, default=_uuid)
    provider: Mapped[str] = mapped_column(String(32), nullable=False)
    product_id: Mapped[str | None] = mapped_column(String(128), nullable=True)
    price_id: Mapped[str] = mapped_column(String(128), nullable=False)
    plan_id: Mapped[str] = mapped_column(String(64), ForeignKey("plans.plan_id"), nullable=False)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    metadata_json: Mapped[dict | None] = mapped_column("metadata", JSON, nullable=True)


class BillingWebhookEvent(Base):
    """Immutable receipt and processing state for one provider event."""

    __tablename__ = "billing_webhook_events"
    __table_args__ = (
        UniqueConstraint("provider", "provider_event_id", name="uq_billing_event_provider_id"),
    )

    billing_event_id: Mapped[str] = mapped_column(String(64), primary_key=True, default=_uuid)
    provider: Mapped[str] = mapped_column(String(32), nullable=False, index=True)
    provider_event_id: Mapped[str] = mapped_column(String(128), nullable=False)
    event_type: Mapped[str] = mapped_column(String(128), nullable=False)
    payload_hash: Mapped[str] = mapped_column(String(64), nullable=False)
    status: Mapped[str] = mapped_column(String(32), nullable=False, default="received")
    error_message: Mapped[str | None] = mapped_column(Text, nullable=True)
    received_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow, nullable=False)
    processed_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)


class WordPressPackageRelease(Base, TimestampMixin):
    """Immutable signed WordPress App Pack release."""

    __tablename__ = "wordpress_package_releases"
    __table_args__ = (
        UniqueConstraint("package_id", "version", name="uq_wp_package_release"),
    )

    release_id: Mapped[str] = mapped_column(String(64), primary_key=True, default=_uuid)
    package_id: Mapped[str] = mapped_column(String(64), nullable=False, index=True)
    app_id: Mapped[str] = mapped_column(String(64), ForeignKey("apps.app_id"), nullable=False, index=True)
    version: Mapped[str] = mapped_column(String(64), nullable=False)
    artifact_name: Mapped[str] = mapped_column(String(255), nullable=False)
    artifact_path: Mapped[str] = mapped_column(Text, nullable=False)
    artifact_size: Mapped[int] = mapped_column(Integer, nullable=False)
    sha256: Mapped[str] = mapped_column(String(64), nullable=False)
    signature: Mapped[str] = mapped_column(Text, nullable=False)
    signature_algorithm: Mapped[str] = mapped_column(String(32), nullable=False, default="Ed25519")
    status: Mapped[str] = mapped_column(String(32), nullable=False, default="test")
    compatibility_json: Mapped[dict | None] = mapped_column("compatibility", JSON, nullable=True)
    capabilities_json: Mapped[list | None] = mapped_column("capabilities", JSON, nullable=True)
    published_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    revoked_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    revoked_reason: Mapped[str | None] = mapped_column(Text, nullable=True)


class WordPressBridgeRelease(Base, TimestampMixin):
    """Signed release of the WordPress Bridge trust core."""

    __tablename__ = "wordpress_bridge_releases"

    release_id: Mapped[str] = mapped_column(String(64), primary_key=True, default=_uuid)
    version: Mapped[str] = mapped_column(String(64), unique=True, nullable=False, index=True)
    channel: Mapped[str] = mapped_column(String(32), nullable=False, default="development")
    artifact_name: Mapped[str] = mapped_column(String(255), nullable=False)
    artifact_path: Mapped[str] = mapped_column(Text, nullable=False)
    artifact_size: Mapped[int] = mapped_column(Integer, nullable=False)
    sha256: Mapped[str] = mapped_column(String(64), nullable=False)
    signature: Mapped[str] = mapped_column(Text, nullable=False)
    signature_algorithm: Mapped[str] = mapped_column(String(32), nullable=False, default="Ed25519")
    status: Mapped[str] = mapped_column(String(32), nullable=False, default="test")
    metadata_json: Mapped[dict | None] = mapped_column("metadata", JSON, nullable=True)
    published_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    revoked_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    revoked_reason: Mapped[str | None] = mapped_column(Text, nullable=True)


class AgentProfile(Base, TimestampMixin):
    """Versioned per-tenant policy for single-app or multi-app agents."""

    __tablename__ = "agent_profiles"
    __table_args__ = (UniqueConstraint("tenant_id", "agent_key", name="uq_agent_profile_tenant_key"),)

    agent_profile_id: Mapped[str] = mapped_column(String(64), primary_key=True, default=_uuid)
    tenant_id: Mapped[str] = mapped_column(String(64), ForeignKey("tenants.tenant_id"), nullable=False, index=True)
    agent_key: Mapped[str] = mapped_column(String(64), nullable=False)
    display_name: Mapped[str] = mapped_column(String(255), nullable=False)
    description: Mapped[str] = mapped_column(Text, nullable=False)
    profile_version: Mapped[str] = mapped_column(String(64), nullable=False, default="1.0.0")
    instructions_version: Mapped[str] = mapped_column(String(64), nullable=False, default="1.0.0")
    status: Mapped[str] = mapped_column(String(32), nullable=False, default="active")
    allowed_capabilities_json: Mapped[list | None] = mapped_column("allowed_capabilities", JSON, nullable=True)
    allowed_roles_json: Mapped[list | None] = mapped_column("allowed_roles", JSON, nullable=True)
    confirmation_policy_json: Mapped[dict | None] = mapped_column("confirmation_policy", JSON, nullable=True)
    limits_json: Mapped[dict | None] = mapped_column("limits", JSON, nullable=True)
    metadata_json: Mapped[dict | None] = mapped_column("metadata", JSON, nullable=True)


class TenantLimit(Base, TimestampMixin):
    """Effective limits applied to a tenant or tenant-app pairing."""

    __tablename__ = "tenant_limits"
    __table_args__ = (UniqueConstraint("tenant_id", "app_id", "limit_name", name="uq_tenant_limits_scope"),)

    tenant_limit_id: Mapped[str] = mapped_column(String(36), primary_key=True, default=_uuid)
    tenant_id: Mapped[str] = mapped_column(String(64), ForeignKey("tenants.tenant_id"), nullable=False, index=True)
    app_id: Mapped[str | None] = mapped_column(String(64), ForeignKey("apps.app_id"), nullable=True, index=True)
    limit_name: Mapped[str] = mapped_column(String(128), nullable=False)
    limit_value: Mapped[int | None] = mapped_column(Integer, nullable=True)
    window_seconds: Mapped[int | None] = mapped_column(Integer, nullable=True)
    overage_policy: Mapped[str | None] = mapped_column(String(64), nullable=True)
    metadata_json: Mapped[dict | None] = mapped_column("metadata", JSON, nullable=True)

    tenant: Mapped["Tenant"] = relationship(back_populates="limits")
    app: Mapped[Optional["App"]] = relationship(back_populates="tenant_limits")


class TenantUsage(Base):
    """Recorded usage data for billing, quotas, and analytics."""

    __tablename__ = "tenant_usage"

    usage_id: Mapped[str] = mapped_column(String(36), primary_key=True, default=_uuid)
    tenant_id: Mapped[str] = mapped_column(String(64), ForeignKey("tenants.tenant_id"), nullable=False, index=True)
    app_id: Mapped[str | None] = mapped_column(String(64), ForeignKey("apps.app_id"), nullable=True, index=True)
    metric_name: Mapped[str] = mapped_column(String(128), nullable=False)
    metric_value: Mapped[float] = mapped_column(Numeric(18, 4), nullable=False, default=0)
    usage_period_start: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    usage_period_end: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    recorded_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow, nullable=False)
    metadata_json: Mapped[dict | None] = mapped_column("metadata", JSON, nullable=True)

    tenant: Mapped["Tenant"] = relationship(back_populates="usage_records")
    app: Mapped[Optional["App"]] = relationship(back_populates="tenant_usage")


class TenantSecret(Base, TimestampMixin):
    """Encrypted secrets scoped to a tenant and optional app."""

    __tablename__ = "tenant_secrets"
    __table_args__ = (UniqueConstraint("tenant_id", "app_id", "secret_name", name="uq_tenant_secrets_scope"),)

    tenant_secret_id: Mapped[str] = mapped_column(String(36), primary_key=True, default=_uuid)
    tenant_id: Mapped[str] = mapped_column(String(64), ForeignKey("tenants.tenant_id"), nullable=False, index=True)
    app_id: Mapped[str | None] = mapped_column(String(64), ForeignKey("apps.app_id"), nullable=True, index=True)
    secret_name: Mapped[str] = mapped_column(String(128), nullable=False)
    secret_value_encrypted: Mapped[str] = mapped_column(Text, nullable=False)
    key_version: Mapped[str | None] = mapped_column(String(64), nullable=True)
    metadata_json: Mapped[dict | None] = mapped_column("metadata", JSON, nullable=True)

    tenant: Mapped["Tenant"] = relationship(back_populates="secrets")
    app: Mapped[Optional["App"]] = relationship(back_populates="tenant_secrets")


class TenantIntegration(Base, TimestampMixin):
    """External integrations configured per tenant and app."""

    __tablename__ = "tenant_integrations"
    __table_args__ = (UniqueConstraint("tenant_id", "app_id", "integration_name", name="uq_tenant_integrations_scope"),)

    tenant_integration_id: Mapped[str] = mapped_column(String(36), primary_key=True, default=_uuid)
    tenant_id: Mapped[str] = mapped_column(String(64), ForeignKey("tenants.tenant_id"), nullable=False, index=True)
    app_id: Mapped[str | None] = mapped_column(String(64), ForeignKey("apps.app_id"), nullable=True, index=True)
    integration_name: Mapped[str] = mapped_column(String(128), nullable=False)
    status: Mapped[str] = mapped_column(String(32), default="active", nullable=False)
    config_json: Mapped[dict | None] = mapped_column("config", JSON, nullable=True)
    secret_ref: Mapped[str | None] = mapped_column(String(128), nullable=True)

    tenant: Mapped["Tenant"] = relationship(back_populates="integrations")
    app: Mapped[Optional["App"]] = relationship(back_populates="integrations")


class AuditEvent(Base):
    """Immutable audit trail for administrative and security actions."""

    __tablename__ = "audit_events"

    audit_event_id: Mapped[str] = mapped_column(String(36), primary_key=True, default=_uuid)
    tenant_id: Mapped[str | None] = mapped_column(String(64), ForeignKey("tenants.tenant_id"), nullable=True, index=True)
    app_id: Mapped[str | None] = mapped_column(String(64), ForeignKey("apps.app_id"), nullable=True, index=True)
    actor_id: Mapped[str | None] = mapped_column(String(128), nullable=True)
    actor_type: Mapped[str | None] = mapped_column(String(64), nullable=True)
    event_type: Mapped[str] = mapped_column(String(128), nullable=False)
    status: Mapped[str] = mapped_column(String(32), default="success", nullable=False)
    request_id: Mapped[str | None] = mapped_column(String(64), nullable=True, index=True)
    ip_address: Mapped[str | None] = mapped_column(String(64), nullable=True)
    payload_json: Mapped[dict | None] = mapped_column("payload", JSON, nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow, nullable=False)

    tenant: Mapped[Optional["Tenant"]] = relationship(back_populates="audit_events")
    app: Mapped[Optional["App"]] = relationship(back_populates="audit_events")


class JobRegistryRecord(Base, TimestampMixin):
    """Lifecycle records for background jobs submitted through the platform."""

    __tablename__ = "job_registry"

    job_id: Mapped[str] = mapped_column(String(36), primary_key=True, default=_uuid)
    tenant_id: Mapped[str | None] = mapped_column(String(64), ForeignKey("tenants.tenant_id"), nullable=True, index=True)
    app_id: Mapped[str | None] = mapped_column(String(64), ForeignKey("apps.app_id"), nullable=True, index=True)
    job_name: Mapped[str] = mapped_column(String(128), nullable=False)
    queue_name: Mapped[str] = mapped_column(String(64), default="default", nullable=False)
    status: Mapped[str] = mapped_column(String(32), default="queued", nullable=False)
    payload_json: Mapped[dict | None] = mapped_column("payload", JSON, nullable=True)
    result_json: Mapped[dict | None] = mapped_column("result", JSON, nullable=True)
    attempts: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    started_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    finished_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)

    tenant: Mapped[Optional["Tenant"]] = relationship(back_populates="jobs")
    app: Mapped[Optional["App"]] = relationship(back_populates="jobs")