"""Workspaces domain - SQLAlchemy models."""
from __future__ import annotations
from datetime import datetime
from typing import Optional
from uuid import UUID, uuid4

from sqlalchemy import BigInteger, ForeignKey, String
from sqlalchemy.dialects.postgresql import ARRAY, 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


# Permission constants
PERMISSIONS = [
    "workspaces.manage",
    "members.manage",
    "projects.read",
    "projects.create",
    "projects.write",
    "projects.delete",
    "repos.manage", "instructions.manage", "codegen.run", "codegen.run.desktop",
    "previews.manage", "deploy.publish",
    "deploy.rollback", "domains.manage", "secrets.manage", "billing.manage", "invoices.read",
    "public_api.manage",
]

ROLE_PERMISSIONS = {
    "owner": PERMISSIONS,  # All
    "admin": [p for p in PERMISSIONS if p != "billing.manage"],  # Most (includes codegen.run.desktop)
    "editor": [
        "projects.read",
        "projects.create",
        "projects.write",
        "repos.manage",
        "previews.manage",
        "codegen.run",
        "codegen.run.desktop",
        "instructions.manage",
    ],
    "developer": [
        "projects.read",
        "projects.write",
        "codegen.run",
        "codegen.run.desktop",
        "previews.manage",
        "deploy.publish",
    ],
    "viewer": ["projects.read"],  # Read-only project list / detail
    "billing": ["billing.manage", "invoices.read"],
}


class Workspace(Base):
    """Workspace (tenant boundary)."""

    __tablename__ = "workspaces"

    id: Mapped[UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid4)
    # Immutable public ref for hostnames (e.g. project_ref.tenant_ref.apex).
    tenant_ref: Mapped[Optional[str]] = mapped_column(String(16), unique=True, nullable=True)
    slug: Mapped[str] = mapped_column(String(100), unique=True, nullable=False)
    name: Mapped[str] = mapped_column(String(255), nullable=False)
    owner_id: Mapped[Optional[UUID]] = mapped_column(UUID(as_uuid=True), ForeignKey("users.id"), nullable=True)
    selected_plan_slug: Mapped[Optional[str]] = mapped_column(String(50), nullable=True)
    billing_last_checkout_session_id: Mapped[Optional[str]] = mapped_column(String(120), nullable=True)
    billing_last_checkout_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    billing_last_checkout_return: Mapped[Optional[str]] = mapped_column(String(40), nullable=True)
    onboarding_status: Mapped[Optional[str]] = mapped_column(String(50), nullable=True)
    security_policy_require_mfa_for_paid_users: Mapped[bool] = mapped_column(
        default=True, nullable=False, server_default="true"
    )
    skip_plan_quota_limits: Mapped[bool] = mapped_column(
        default=False,
        nullable=False,
        server_default="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())
    deleted_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)


class Role(Base):
    """Role with permissions."""

    __tablename__ = "roles"

    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"), nullable=True)
    name: Mapped[str] = mapped_column(String(100), nullable=False)
    permissions: Mapped[list] = mapped_column(ARRAY(String), nullable=False)
    is_system: Mapped[bool] = mapped_column(default=False)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())


class WorkspaceMembership(Base):
    """User workspace membership."""

    __tablename__ = "workspace_memberships"

    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)
    user_id: Mapped[UUID] = mapped_column(UUID(as_uuid=True), ForeignKey("users.id", ondelete="CASCADE"), nullable=False)
    role_id: Mapped[Optional[UUID]] = mapped_column(UUID(as_uuid=True), ForeignKey("roles.id"), nullable=True)
    invited_by: Mapped[Optional[UUID]] = mapped_column(UUID(as_uuid=True), ForeignKey("users.id"), nullable=True)
    joined_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())


class WorkspaceInvite(Base):
    """Pending workspace invitation."""

    __tablename__ = "workspace_invites"

    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)
    email: Mapped[str] = mapped_column(String(255), nullable=False)
    role_id: Mapped[Optional[UUID]] = mapped_column(UUID(as_uuid=True), ForeignKey("roles.id"), nullable=True)
    token_hash: Mapped[str] = mapped_column(String(255), nullable=False)
    invited_by: Mapped[Optional[UUID]] = mapped_column(UUID(as_uuid=True), ForeignKey("users.id"), nullable=True)
    status: Mapped[str] = mapped_column(String(20), nullable=False, server_default="pending")
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
    expires_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    accepted_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    revoked_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)


class WorkspaceSettings(Base):
    """Workspace settings."""

    __tablename__ = "workspace_settings"

    workspace_id: Mapped[UUID] = mapped_column(UUID(as_uuid=True), ForeignKey("workspaces.id", ondelete="CASCADE"), primary_key=True)
    default_code_apply_policy: Mapped[str] = mapped_column(String(20), server_default="commit")
    github_installation_id: Mapped[Optional[int]] = mapped_column(BigInteger, nullable=True)
    local_execution_enabled: Mapped[bool] = mapped_column(
        default=False, nullable=False, server_default="false"
    )
    settings: Mapped[dict] = mapped_column(JSONB, server_default="{}")
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), onupdate=func.now())