"""External Connections domain — SQLAlchemy models."""
from __future__ import annotations

import enum
from datetime import datetime
from typing import Optional
from uuid import UUID

from sqlalchemy import ForeignKey, String, Text, Integer, Boolean
from sqlalchemy.dialects.postgresql import ENUM as PgEnum, JSONB, UUID as PGUUID
from sqlalchemy.orm import Mapped, mapped_column

from src.core.database import Base


class ConnectionType(str, enum.Enum):
    postgres = "postgres"
    mysql = "mysql"
    http_api = "http_api"


class ConnectionStatus(str, enum.Enum):
    pending = "pending"
    testing = "testing"
    active = "active"
    error = "error"


class ProjectExternalConnection(Base):
    __tablename__ = "project_external_connections"

    id: Mapped[UUID] = mapped_column(PGUUID(as_uuid=True), primary_key=True)
    project_id: Mapped[UUID] = mapped_column(
        PGUUID(as_uuid=True),
        ForeignKey("projects.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )
    workspace_id: Mapped[UUID] = mapped_column(
        PGUUID(as_uuid=True),
        ForeignKey("workspaces.id", ondelete="CASCADE"),
        nullable=False,
    )
    name: Mapped[str] = mapped_column(String(64), nullable=False)
    type: Mapped[str] = mapped_column(PgEnum(name="p2hs_connection_type", create_type=False), nullable=False)
    status: Mapped[str] = mapped_column(PgEnum(name="p2hs_connection_status", create_type=False), nullable=False, default="pending")
    readonly: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)

    # SQL fields
    host: Mapped[Optional[str]] = mapped_column(String(255), nullable=True)
    port: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
    database: Mapped[Optional[str]] = mapped_column(String(128), nullable=True)
    username: Mapped[Optional[str]] = mapped_column(String(128), nullable=True)
    password_enc: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    ssl_mode: Mapped[Optional[str]] = mapped_column(String(16), nullable=True, default="require")
    max_rows_per_query: Mapped[Optional[int]] = mapped_column(Integer, nullable=True, default=1000)
    hyperdrive_id: Mapped[Optional[str]] = mapped_column(String(64), nullable=True)

    # HTTP API fields
    http_base_url: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    http_auth_type: Mapped[Optional[str]] = mapped_column(String(20), nullable=True)
    http_auth_secret_enc: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    http_default_headers_json: Mapped[Optional[dict]] = mapped_column(JSONB, nullable=True)
    max_response_size_bytes: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)

    # Shared
    schema_snapshot_json: Mapped[Optional[dict]] = mapped_column(JSONB, nullable=True)
    schema_snapshot_at: Mapped[Optional[datetime]] = mapped_column(nullable=True)
    last_tested_at: Mapped[Optional[datetime]] = mapped_column(nullable=True)
    last_test_error: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    created_at: Mapped[datetime] = mapped_column(nullable=False)
    updated_at: Mapped[datetime] = mapped_column(nullable=False)