import uuid

from fastapi import APIRouter, Depends, HTTPException, status
from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession

from app.core.crypto import encrypt_secret
from app.core.database import get_db
from app.core.db_provisioning import (
    ProvisioningError,
    copy_tenant_data,
    deprovision_workspace_database,
    provision_workspace_database,
)
from app.core.permissions import deny_vendor, require_permission
from app.core.security import AuthContext, get_auth_context
from app.core.workspace_engines import close_workspace_engine, get_workspace_engine
from app.crud.audit import write_audit_log
from app.crud.base import CRUDBase
from app.models.workspace import Workspace
from app.schemas.workspace import WorkspaceCreate, WorkspaceRead, WorkspaceTestResult

router = APIRouter(prefix="/workspaces", tags=["workspaces"])
crud = CRUDBase[Workspace, WorkspaceCreate, WorkspaceCreate](Workspace)


@router.get("", response_model=list[WorkspaceRead])
async def list_workspaces(
    skip: int = 0,
    limit: int = 100,
    ctx: AuthContext = Depends(get_auth_context),
    db: AsyncSession = Depends(get_db),
    _: AuthContext = Depends(require_permission("workspaces", "view")),
    __: AuthContext = Depends(deny_vendor()),
) -> list[Workspace]:
    return await crud.list(db, ctx.tenant_id, skip=skip, limit=limit)


@router.get("/{workspace_id}", response_model=WorkspaceRead)
async def get_workspace(
    workspace_id: uuid.UUID,
    ctx: AuthContext = Depends(get_auth_context),
    db: AsyncSession = Depends(get_db),
    _: AuthContext = Depends(require_permission("workspaces", "view")),
    __: AuthContext = Depends(deny_vendor()),
) -> Workspace:
    return await crud.get(db, ctx.tenant_id, workspace_id)


@router.post("", response_model=WorkspaceRead, status_code=status.HTTP_201_CREATED)
async def create_workspace(
    payload: WorkspaceCreate,
    ctx: AuthContext = Depends(get_auth_context),
    db: AsyncSession = Depends(get_db),
    _: AuthContext = Depends(require_permission("workspaces", "create")),
    __: AuthContext = Depends(deny_vendor()),
) -> Workspace:
    """Always creates a tracking row and returns 201 — `status` on the
    response says whether the underlying database actually got provisioned
    ("active") or not ("failed", with `status_detail` explaining why: a bad
    db_name/db_username, a duplicate database, an unreachable host, etc.).
    Raising an HTTPException here instead would roll back this row along
    with everything else in the request's one transaction (see
    app/core/database.py::get_db) — undoing the one thing ("the row stays
    for visibility, not silently dropped") this is for."""
    workspace = Workspace(
        tenant_id=ctx.tenant_id,
        name=payload.name,
        db_host=payload.db_host,
        db_port=payload.db_port,
        db_name=payload.db_name,
        db_username=payload.db_username,
        db_password_encrypted="",
        status="provisioning",
        created_by=ctx.user_id,
    )
    db.add(workspace)
    await db.flush()

    try:
        await provision_workspace_database(
            payload.db_host, payload.db_port, payload.db_name, payload.db_username, payload.db_password
        )
    except ProvisioningError as exc:
        workspace.status = "failed"
        workspace.status_detail = str(exc)
        await write_audit_log(
            db, ctx.tenant_id, ctx.user_id, "workspace.provisioning_failed", "workspace", workspace.id,
            {"db_host": payload.db_host, "db_name": payload.db_name, "error": str(exc)},
        )
        return workspace

    if payload.copy_tenant_data:
        try:
            await copy_tenant_data(db, ctx.tenant_id, payload.db_host, payload.db_port, payload.db_name)
        except ProvisioningError as exc:
            # The database/role were created successfully but ended up
            # half-seeded — that's a worse state than never having them at
            # all, so roll the whole thing back rather than leave it active.
            await deprovision_workspace_database(
                payload.db_host, payload.db_port, payload.db_name, payload.db_username
            )
            workspace.status = "failed"
            workspace.status_detail = f"Database created but copying tenant data failed: {exc}"
            await write_audit_log(
                db, ctx.tenant_id, ctx.user_id, "workspace.data_copy_failed", "workspace", workspace.id,
                {"db_host": payload.db_host, "db_name": payload.db_name, "error": str(exc)},
            )
            return workspace

    workspace.status = "active"
    workspace.db_password_encrypted = encrypt_secret(payload.db_password)
    await write_audit_log(
        db, ctx.tenant_id, ctx.user_id, "workspace.created", "workspace", workspace.id,
        {"db_host": payload.db_host, "db_name": payload.db_name, "copied_tenant_data": payload.copy_tenant_data},
    )
    return workspace


@router.post("/{workspace_id}/test-connection", response_model=WorkspaceTestResult)
async def test_workspace_connection(
    workspace_id: uuid.UUID,
    ctx: AuthContext = Depends(get_auth_context),
    db: AsyncSession = Depends(get_db),
    _: AuthContext = Depends(require_permission("workspaces", "view")),
    __: AuthContext = Depends(deny_vendor()),
) -> WorkspaceTestResult:
    """Opens the workspace's own database via the per-workspace engine
    registry (app/core/workspace_engines.py) and runs a trivial query —
    proves the stored credentials still work end-to-end, the same spirit as
    POST /connectors/{id}/test."""
    workspace = await crud.get(db, ctx.tenant_id, workspace_id)
    try:
        engine = await get_workspace_engine(workspace)
        async with engine.connect() as conn:
            await conn.execute(text("SELECT 1"))
        return WorkspaceTestResult(success=True, detail="Connected successfully.")
    except Exception as exc:  # noqa: BLE001 — surfaced to the caller as a plain message, not a 500
        await close_workspace_engine(workspace_id)
        return WorkspaceTestResult(success=False, detail=str(exc))


@router.delete("/{workspace_id}", status_code=status.HTTP_204_NO_CONTENT)
async def delete_workspace(
    workspace_id: uuid.UUID,
    drop_database: bool = False,
    ctx: AuthContext = Depends(get_auth_context),
    db: AsyncSession = Depends(get_db),
    _: AuthContext = Depends(require_permission("workspaces", "delete")),
    __: AuthContext = Depends(deny_vendor()),
) -> None:
    """By default only removes FlowFabric's tracking row — the real
    Postgres database and role are left untouched, since dropping live
    infrastructure should be a deliberate, separate action, not an implicit
    side effect of deleting a list row. Pass drop_database=true to actually
    run DROP DATABASE/DROP ROLE first (see app/core/db_provisioning.py::
    deprovision_workspace_database); if that fails, the row is left intact
    so the delete can be retried."""
    workspace = await crud.get(db, ctx.tenant_id, workspace_id)

    # Close (and dispose the pool of) any cached engine BEFORE attempting to
    # drop the database — an idle pooled connection left open by an earlier
    # Test-connection call is enough for Postgres to refuse DROP DATABASE
    # ("database is being accessed by other users").
    await close_workspace_engine(workspace_id)

    if drop_database:
        try:
            await deprovision_workspace_database(
                workspace.db_host, workspace.db_port, workspace.db_name, workspace.db_username
            )
        except ProvisioningError as exc:
            raise HTTPException(status_code=502, detail=f"Could not drop the underlying database: {exc}") from exc

    await write_audit_log(
        db, ctx.tenant_id, ctx.user_id, "workspace.deleted", "workspace", workspace_id,
        {"db_name": workspace.db_name, "dropped_database": drop_database},
    )
    await db.delete(workspace)
    await db.flush()