"""Pending linkage review endpoints - admin only."""

from __future__ import annotations

from datetime import datetime
from typing import Annotated, Optional, Union

from fastapi import APIRouter, Depends, HTTPException, Query, status
from pydantic import BaseModel, ConfigDict, Field
from sqlalchemy import text
from sqlalchemy.orm import Session

from backend.api.users import require_feature_permission
from backend.db.models import Photo, PhotoTagLinkage, Tag
from backend.db.session import get_auth_db, get_db

router = APIRouter(prefix="/pending-linkages", tags=["pending-linkages"])


def _get_or_create_tag_by_name(db: Session, tag_name: str) -> tuple[Tag, bool]:
    """Return a tag for the provided name, creating it if necessary."""
    normalized = (tag_name or "").strip()
    if not normalized:
        raise ValueError("Tag name cannot be empty")

    existing = (
        db.query(Tag)
        .filter(Tag.tag_name.ilike(normalized))
        .first()
    )
    if existing:
        return existing, False

    tag = Tag(tag_name=normalized)
    db.add(tag)
    db.flush()
    return tag, True


def _format_datetime(value: Union[str, datetime, None]) -> Optional[str]:
    """Safely serialize datetime values returned from different drivers."""
    if value is None:
        return None
    if isinstance(value, str):
        return value
    if isinstance(value, datetime):
        return value.isoformat()
    return str(value)


class PendingLinkageResponse(BaseModel):
    """Pending linkage DTO returned from API."""

    model_config = ConfigDict(from_attributes=True, protected_namespaces=())

    id: int
    photo_id: int
    tag_id: Optional[int] = None
    proposed_tag_name: Optional[str] = None
    resolved_tag_name: Optional[str] = None
    user_id: int
    user_name: Optional[str] = None
    user_email: Optional[str] = None
    status: str
    notes: Optional[str] = None
    created_at: str
    updated_at: Optional[str] = None
    photo_filename: Optional[str] = None
    photo_path: Optional[str] = None
    photo_media_type: Optional[str] = None
    photo_tags: list[str] = Field(default_factory=list)


class PendingLinkagesListResponse(BaseModel):
    """List of pending linkage rows."""

    model_config = ConfigDict(protected_namespaces=())

    items: list[PendingLinkageResponse]
    total: int


class ReviewDecision(BaseModel):
    """Decision payload for a pending linkage row."""

    model_config = ConfigDict(protected_namespaces=())

    id: int
    decision: str  # 'approve' or 'deny'


class ReviewRequest(BaseModel):
    """Request payload for reviewing pending linkages."""

    model_config = ConfigDict(protected_namespaces=())

    decisions: list[ReviewDecision]


class ReviewResponse(BaseModel):
    """Review summary returned after processing decisions."""

    model_config = ConfigDict(protected_namespaces=())

    approved: int
    denied: int
    tags_created: int
    linkages_created: int
    errors: list[str]


@router.get("", response_model=PendingLinkagesListResponse)
def list_pending_linkages(
    current_user: Annotated[
        dict, Depends(require_feature_permission("user_tagged"))
    ],
    status_filter: Annotated[
        Optional[str],
        Query(
            description="Optional status filter: pending, approved, or denied."
        ),
    ] = None,
    auth_db: Session = Depends(get_auth_db),
    main_db: Session = Depends(get_db),
) -> PendingLinkagesListResponse:
    """List all pending linkages stored in the auth database."""
    valid_statuses = {"pending", "approved", "denied"}
    if status_filter and status_filter not in valid_statuses:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail="Invalid status_filter. Use pending, approved, or denied.",
        )

    try:
        params = {}
        status_clause = ""
        if status_filter:
            status_clause = "WHERE pl.status = :status_filter"
            params["status_filter"] = status_filter

        # nosemgrep: python.sqlalchemy.security.audit.avoid-sqlalchemy-text.avoid-sqlalchemy-text
        # Safe: SQL uses only column names (no user input in query structure)
        result = auth_db.execute(
            text(
                f"""
                SELECT
                    pl.id,
                    pl.photo_id,
                    pl.tag_id,
                    pl.tag_name,
                    pl.user_id,
                    pl.status,
                    pl.notes,
                    pl.created_at,
                    pl.updated_at,
                    u.name AS user_name,
                    u.email AS user_email
                FROM pending_linkages pl
                LEFT JOIN users u ON pl.user_id = u.id
                {status_clause}
                ORDER BY pl.created_at DESC
                """
            ),
            params,
        )
        rows = result.fetchall()

        photo_ids = {row.photo_id for row in rows if row.photo_id}
        tag_ids = {row.tag_id for row in rows if row.tag_id}

        photo_map: dict[int, Photo] = {}
        if photo_ids:
            photos = (
                main_db.query(Photo)
                .filter(Photo.id.in_(photo_ids))
                .all()
            )
            photo_map = {photo.id: photo for photo in photos}

        tag_map: dict[int, str] = {}
        if tag_ids:
            tags = (
                main_db.query(Tag)
                .filter(Tag.id.in_(tag_ids))
                .all()
            )
            tag_map = {tag.id: tag.tag_name for tag in tags}

        photo_tags_map: dict[int, list[str]] = {
            photo_id: [] for photo_id in photo_ids
        }
        if photo_ids:
            tag_rows = (
                main_db.query(PhotoTagLinkage.photo_id, Tag.tag_name)
                .join(Tag, Tag.id == PhotoTagLinkage.tag_id)
                .filter(PhotoTagLinkage.photo_id.in_(photo_ids))
                .all()
            )
            for photo_id, tag_name in tag_rows:
                photo_tags_map.setdefault(photo_id, []).append(tag_name)

        items: list[PendingLinkageResponse] = []
        for row in rows:
            created_at = _format_datetime(getattr(row, "created_at", None)) or ""
            updated_at = _format_datetime(getattr(row, "updated_at", None))
            photo = photo_map.get(row.photo_id)
            resolved_tag_name = None
            if row.tag_id:
                resolved_tag_name = tag_map.get(row.tag_id)
            proposal_name = row.tag_name
            items.append(
                PendingLinkageResponse(
                    id=row.id,
                    photo_id=row.photo_id,
                    tag_id=row.tag_id,
                    proposed_tag_name=proposal_name,
                    resolved_tag_name=resolved_tag_name or proposal_name,
                    user_id=row.user_id,
                    user_name=row.user_name,
                    user_email=row.user_email,
                    status=row.status,
                    notes=row.notes,
                    created_at=created_at,
                    updated_at=updated_at,
                    photo_filename=photo.filename if photo else None,
                    photo_path=photo.path if photo else None,
                    photo_media_type=photo.media_type if photo else None,
                    photo_tags=photo_tags_map.get(row.photo_id, []),
                )
            )

        return PendingLinkagesListResponse(items=items, total=len(items))
    except HTTPException:
        raise
    except Exception as exc:
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=f"Error reading pending linkages: {exc}",
        )


@router.post("/review", response_model=ReviewResponse)
def review_pending_linkages(
    current_user: Annotated[
        dict, Depends(require_feature_permission("user_tagged"))
    ],
    request: ReviewRequest,
    auth_db: Session = Depends(get_auth_db),
    main_db: Session = Depends(get_db),
) -> ReviewResponse:
    """Approve or deny pending user-proposed tag linkages."""
    approved = 0
    denied = 0
    tags_created = 0
    linkages_created = 0
    errors: list[str] = []
    now = datetime.utcnow()

    for decision in request.decisions:
        try:
            row = auth_db.execute(
                text(
                    """
                    SELECT id, photo_id, tag_id, tag_name, status
                    FROM pending_linkages
                    WHERE id = :id
                    """
                ),
                {"id": decision.id},
            ).fetchone()

            if not row:
                errors.append(
                    f"Pending linkage {decision.id} not found or already deleted"
                )
                continue

            if row.status != "pending":
                errors.append(
                    f"Pending linkage {decision.id} cannot be reviewed (status={row.status})"
                )
                continue

            if decision.decision == "deny":
                auth_db.execute(
                    text(
                        """
                        UPDATE pending_linkages
                        SET status = 'denied',
                            updated_at = :updated_at
                        WHERE id = :id
                        """
                    ),
                    {"id": decision.id, "updated_at": now},
                )
                auth_db.commit()
                denied += 1
                continue

            if decision.decision != "approve":
                errors.append(
                    f"Invalid decision '{decision.decision}' for linkage {decision.id}"
                )
                continue

            photo = (
                main_db.query(Photo)
                .filter(Photo.id == row.photo_id)
                .first()
            )
            if not photo:
                errors.append(
                    f"Photo {row.photo_id} not found for linkage {decision.id}"
                )
                continue

            tag_obj: Optional[Tag] = None
            created_tag = False

            if row.tag_id:
                tag_obj = (
                    main_db.query(Tag)
                    .filter(Tag.id == row.tag_id)
                    .first()
                )
                if not tag_obj and row.tag_name:
                    tag_obj, created_tag = _get_or_create_tag_by_name(
                        main_db, row.tag_name
                    )
                elif not tag_obj:
                    errors.append(
                        f"Tag {row.tag_id} missing for linkage {decision.id}"
                    )
                    continue
            else:
                if not row.tag_name:
                    errors.append(
                        f"No tag information provided for linkage {decision.id}"
                    )
                    continue
                tag_obj, created_tag = _get_or_create_tag_by_name(
                    main_db, row.tag_name
                )

            if created_tag:
                tags_created += 1

            resolved_tag_id = tag_obj.id  # type: ignore[union-attr]

            existing_linkage = (
                main_db.query(PhotoTagLinkage)
                .filter(
                    PhotoTagLinkage.photo_id == row.photo_id,
                    PhotoTagLinkage.tag_id == resolved_tag_id,
                )
                .first()
            )

            if not existing_linkage:
                linkage = PhotoTagLinkage(
                    photo_id=row.photo_id,
                    tag_id=resolved_tag_id,
                )
                main_db.add(linkage)
                linkages_created += 1

            main_db.commit()

            auth_db.execute(
                text(
                    """
                    UPDATE pending_linkages
                    SET status = 'approved',
                        tag_id = :tag_id,
                        updated_at = :updated_at
                    WHERE id = :id
                    """
                ),
                {
                    "id": decision.id,
                    "tag_id": resolved_tag_id,
                    "updated_at": now,
                },
            )
            auth_db.commit()
            approved += 1
        except ValueError as exc:
            main_db.rollback()
            auth_db.rollback()
            errors.append(f"Validation error for linkage {decision.id}: {exc}")
        except Exception as exc:
            main_db.rollback()
            auth_db.rollback()
            errors.append(f"Error processing linkage {decision.id}: {exc}")

    return ReviewResponse(
        approved=approved,
        denied=denied,
        tags_created=tags_created,
        linkages_created=linkages_created,
        errors=errors,
    )


class CleanupResponse(BaseModel):
    """Response payload for cleanup operations."""

    model_config = ConfigDict(protected_namespaces=())

    deleted_records: int
    errors: list[str]
    warnings: list[str] = []


@router.post("/cleanup", response_model=CleanupResponse)
def cleanup_pending_linkages(
    current_user: Annotated[
        dict, Depends(require_feature_permission("user_tagged"))
    ],
    auth_db: Session = Depends(get_auth_db),
) -> CleanupResponse:
    """Delete all approved or denied records from pending_linkages table."""
    warnings: list[str] = []

    try:
        result = auth_db.execute(
            text(
                """
                DELETE FROM pending_linkages
                WHERE status IN ('approved', 'denied')
                """
            )
        )

        deleted_records = result.rowcount if hasattr(result, "rowcount") else 0
        auth_db.commit()

        if deleted_records == 0:
            warnings.append("No approved or denied pending linkages to delete.")

        return CleanupResponse(
            deleted_records=deleted_records,
            errors=[],
            warnings=warnings,
        )
    except Exception as exc:
        auth_db.rollback()
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=f"Failed to cleanup pending linkages: {exc}",
        )

