"""Deployments domain - SQLAlchemy models.

Columns align with ``001_initial_schema`` (PostgreSQL enums + names).
"""
from __future__ import annotations
from datetime import datetime
from typing import Optional
from uuid import UUID, uuid4

import sqlalchemy as sa
from sqlalchemy import Boolean, ForeignKey, String, Text
from sqlalchemy.dialects.postgresql import 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

DEPLOY_ENVIRONMENT_ENUM = sa.Enum(
    "staging",
    "production",
    "preview",
    name="p2hs_deploy_environment",
    create_type=False,
)
BUILD_STATUS_ENUM = sa.Enum(
    "pending",
    "running",
    "success",
    "failed",
    "cancelled",
    name="p2hs_build_status",
    create_type=False,
)
DEPLOY_STATUS_ENUM = sa.Enum(
    "pending",
    "deploying",
    "live",
    "failed",
    "rolled_back",
    name="p2hs_deploy_status",
    create_type=False,
)


class ProjectBuild(Base):
    """Maps to ``project_builds`` (001). Referenced by ``project_deploys.build_id``."""

    __tablename__ = "project_builds"

    id: Mapped[UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid4)
    project_id: Mapped[UUID] = mapped_column(
        UUID(as_uuid=True), ForeignKey("projects.id", ondelete="CASCADE"), nullable=False
    )
    revision_id: Mapped[Optional[UUID]] = mapped_column(
        UUID(as_uuid=True), ForeignKey("project_revisions.id"), nullable=True
    )
    status: Mapped[str] = mapped_column(
        BUILD_STATUS_ENUM, nullable=False, server_default="pending"
    )
    build_command: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    output_directory: Mapped[Optional[str]] = mapped_column(String(255), nullable=True)
    artifact_url: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    logs_url: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    started_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    completed_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )


class ProjectDeploy(Base):
    """Maps to ``project_deploys`` (001)."""

    __tablename__ = "project_deploys"

    id: Mapped[UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid4)
    project_id: Mapped[UUID] = mapped_column(
        UUID(as_uuid=True), ForeignKey("projects.id", ondelete="CASCADE"), nullable=False
    )
    revision_id: Mapped[Optional[UUID]] = mapped_column(
        UUID(as_uuid=True), ForeignKey("project_revisions.id"), nullable=True
    )
    build_id: Mapped[Optional[UUID]] = mapped_column(
        UUID(as_uuid=True), ForeignKey("project_builds.id"), nullable=True
    )
    environment: Mapped[str] = mapped_column(DEPLOY_ENVIRONMENT_ENUM, nullable=False)
    status: Mapped[str] = mapped_column(
        DEPLOY_STATUS_ENUM, nullable=False, server_default="pending"
    )
    # DB column is ``deployment_url`` (public URL when live — not GitHub; preview is separate).
    deploy_url: Mapped[Optional[str]] = mapped_column("deployment_url", Text, nullable=True)
    provider_deploy_id: Mapped[Optional[str]] = mapped_column(
        "cloudflare_deployment_id", String(255), nullable=True
    )
    created_by: Mapped[Optional[UUID]] = mapped_column(
        "deployed_by", UUID(as_uuid=True), ForeignKey("users.id"), nullable=True
    )
    deployed_at: Mapped[Optional[datetime]] = mapped_column(
        DateTime(timezone=True), nullable=True
    )
    rolled_back_at: Mapped[Optional[datetime]] = mapped_column(
        DateTime(timezone=True), nullable=True
    )
    rolled_back_to: Mapped[Optional[UUID]] = mapped_column(UUID(as_uuid=True), nullable=True)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )
    hosting_provider: Mapped[Optional[str]] = mapped_column(String(40), nullable=True)
    deployment_target_kind: Mapped[Optional[str]] = mapped_column(String(40), nullable=True)
    provider_namespace: Mapped[Optional[str]] = mapped_column(String(100), nullable=True)
    provider_version_id: Mapped[Optional[str]] = mapped_column(String(255), nullable=True)
    source_git_sha: Mapped[Optional[str]] = mapped_column(String(40), nullable=True)
    activation_status: Mapped[Optional[str]] = mapped_column(String(40), nullable=True)
    last_completed_step: Mapped[Optional[str]] = mapped_column(String(100), nullable=True)
    deployment_metadata: Mapped[dict] = mapped_column(
        JSONB, nullable=False, server_default=sa.text("'{}'::jsonb")
    )

    @property
    def provider(self) -> Optional[str]:
        """Reserved for multi-provider; CF id present implies cloudflare."""
        return "cloudflare" if self.provider_deploy_id else None

    @property
    def error_message(self) -> Optional[str]:
        """Expose the persisted hosting failure reason through the API.

        Deployment failures are recorded in ``deployment_metadata`` because the
        original schema predates a dedicated error column.  Returning the
        message here keeps the response model backward-compatible while making
        failed deploys diagnosable by callers.
        """
        metadata = self.deployment_metadata or {}
        return (
            metadata.get("message")
            or metadata.get("error")
            or metadata.get("detail")
        )

    @property
    def started_at(self) -> Optional[datetime]:
        """Compat with API schema; 001 uses ``deployed_at`` for completion time."""
        return None

    @property
    def completed_at(self) -> Optional[datetime]:
        """Compat: treat ``deployed_at`` as completion timestamp when set."""
        return self.deployed_at


class ProjectDeploymentTarget(Base):
    """Per-project per-environment deployment target record.

    Separates the routing plane (W4P KV) from the deployment plane (CF Pages).
    One row per (project_id, environment) — enforced by unique index in migration 018.
    """

    __tablename__ = "project_deployment_targets"

    id: Mapped[UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid4)
    project_id: Mapped[UUID] = mapped_column(
        UUID(as_uuid=True), ForeignKey("projects.id", ondelete="CASCADE"), nullable=False
    )
    environment: Mapped[str] = mapped_column(String(20), nullable=False)           # production | preview
    target_kind: Mapped[str] = mapped_column(String(40), nullable=False)           # cloudflare_pages | external_origin
    routing_provider_kind: Mapped[str] = mapped_column(String(40), nullable=False, server_default="cloudflare_w4p")
    deployment_provider_kind: Mapped[str] = mapped_column(String(40), nullable=False, server_default="cloudflare_pages")
    # CF Pages identifiers
    provider_project_name: Mapped[Optional[str]] = mapped_column(String(100), nullable=True)
    provider_project_id: Mapped[Optional[str]] = mapped_column(String(255), nullable=True)
    provider_deployment_id: Mapped[Optional[str]] = mapped_column(String(255), nullable=True)
    # Stable public URLs
    provider_preview_url: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    provider_production_url: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    # Routing descriptor (JSON stored in W4P KV)
    routing_target_descriptor: Mapped[dict] = mapped_column(
        JSONB, nullable=False, server_default=sa.text("'{}'::jsonb")
    )
    # State tracking
    latest_commit_sha: Mapped[Optional[str]] = mapped_column(String(40), nullable=True)
    latest_revision_id: Mapped[Optional[UUID]] = mapped_column(
        UUID(as_uuid=True), ForeignKey("project_revisions.id", ondelete="SET NULL"), nullable=True
    )
    repo_linkage_status: Mapped[str] = mapped_column(String(30), nullable=False, server_default="pending")
    domain_status: Mapped[str] = mapped_column(String(20), nullable=False, server_default="pending")
    custom_domain_status: Mapped[Optional[str]] = mapped_column(String(20), 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 ProjectDomain(Base):
    """Maps to ``project_domains`` (001)."""

    __tablename__ = "project_domains"

    id: Mapped[UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid4)
    project_id: Mapped[UUID] = mapped_column(
        UUID(as_uuid=True), ForeignKey("projects.id", ondelete="CASCADE"), nullable=False
    )
    domain: Mapped[str] = mapped_column(String(255), nullable=False)
    environment: Mapped[Optional[str]] = mapped_column(DEPLOY_ENVIRONMENT_ENUM, nullable=True)
    is_primary: Mapped[bool] = mapped_column(Boolean, nullable=False, server_default=sa.text("false"))
    verification_txt: Mapped[Optional[str]] = mapped_column(
        "verification_token", String(255), nullable=True
    )
    cloudflare_zone_id: Mapped[Optional[str]] = mapped_column(String(255), nullable=True)
    cf_hostname_id: Mapped[Optional[str]] = mapped_column(String(64), nullable=True)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )
    verified_at: Mapped[Optional[datetime]] = mapped_column(
        DateTime(timezone=True), nullable=True
    )
    tls_provisioned_at: Mapped[Optional[datetime]] = mapped_column(
        DateTime(timezone=True), nullable=True
    )

    @property
    def verification_status(self) -> str:
        return "verified" if self.verified_at is not None else "pending"

    @property
    def tls_status(self) -> Optional[str]:
        return "provisioned" if self.tls_provisioned_at is not None else None