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


class MCPServerConnection(Base, UUIDPrimaryKeyMixin, TenantMixin, TimestampMixin):
    """
    Connected external MCP server (e.g., GitHub MCP, Postgres MCP, Custom Server)
    registered by a tenant to grant AI agents standardized tool capabilities.
    """
    __tablename__ = "mcp_server_connections"

    name: Mapped[str] = mapped_column(String(100), nullable=False)
    slug: Mapped[str] = mapped_column(String(100), nullable=False)
    transport_type: Mapped[str] = mapped_column(String(20), default="http", nullable=False)  # http | sse | stdio
    endpoint_url: Mapped[str] = mapped_column(String(512), nullable=False)
    encrypted_auth_token: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    is_active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    status: Mapped[str] = mapped_column(String(50), default="connected", nullable=False)  # connected | degraded | error
    discovered_tools: Mapped[list[dict[str, Any]]] = mapped_column(JSONB, default=list, nullable=False)
    last_synced_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)

    execution_logs: Mapped[list["MCPExecutionLog"]] = relationship("MCPExecutionLog", back_populates="server")

    __table_args__ = (
        Index("ix_mcp_conn_tenant_slug", "tenant_id", "slug", unique=True),
    )


class MCPToolPolicy(Base, UUIDPrimaryKeyMixin, TenantMixin, TimestampMixin):
    """
    Tenant-level security policy for a specific MCP tool.
    Controls whether a tool is enabled, requires human approval (e.g. dangerous actions),
    and rate limits.
    """
    __tablename__ = "mcp_tool_policies"

    tool_name: Mapped[str] = mapped_column(String(100), nullable=False)  # e.g. "bot.deploy", "github.create_issue"
    is_enabled: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    requires_approval: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    rate_limit_per_minute: Mapped[int] = mapped_column(Integer, default=60, nullable=False)
    allowed_roles: Mapped[list[str]] = mapped_column(JSONB, default=lambda: ["owner", "admin", "editor"], nullable=False)

    __table_args__ = (
        Index("ix_mcp_policy_tenant_tool", "tenant_id", "tool_name", unique=True),
    )


class MCPExecutionLog(Base, UUIDPrimaryKeyMixin, TenantMixin, TimestampMixin):
    """
    Immutable audit log for all MCP tool calls made either via our platform MCP server
    or by agents calling external MCP servers.
    """
    __tablename__ = "mcp_execution_logs"

    server_id: Mapped[Optional[uuid.UUID]] = mapped_column(
        UUID(as_uuid=True),
        ForeignKey("mcp_server_connections.id", ondelete="SET NULL"),
        nullable=True,
        index=True,
    )
    tool_name: Mapped[str] = mapped_column(String(100), index=True, nullable=False)
    caller_type: Mapped[str] = mapped_column(String(50), default="external_ai", nullable=False)  # external_ai | internal_agent | workflow
    caller_identity: Mapped[str] = mapped_column(String(150), default="unknown", nullable=False)
    input_arguments: Mapped[dict[str, Any]] = mapped_column(JSONB, default=dict, nullable=False)
    output_result: Mapped[Optional[dict[str, Any]]] = mapped_column(JSONB, nullable=True)
    status: Mapped[str] = mapped_column(String(30), default="success", nullable=False)  # success | error | approval_pending | rejected
    execution_time_ms: Mapped[int] = mapped_column(Integer, default=0, nullable=False)
    approval_status: Mapped[str] = mapped_column(String(30), default="auto_approved", nullable=False)  # auto_approved | approved | pending | rejected
    approver_user_id: Mapped[Optional[uuid.UUID]] = mapped_column(UUID(as_uuid=True), nullable=True)
    error_message: Mapped[Optional[str]] = mapped_column(Text, nullable=True)

    server: Mapped[Optional["MCPServerConnection"]] = relationship("MCPServerConnection", back_populates="execution_logs")

    __table_args__ = (
        Index("ix_mcp_log_tenant_tool", "tenant_id", "tool_name", "created_at"),
    )
