"""Persist / load candidate performance rows (no AI)."""

from __future__ import annotations

import logging
from datetime import datetime, timezone
from typing import Any
from uuid import uuid4

from sqlalchemy import desc, func, select

from app.database import SessionLocal
from app.models.candidate_performance import (
    CandidateAiEvaluationRecord,
    CandidateAssessmentAttemptRecord,
)
from app.services.talent.performance_scoring import (
    assessment_status,
    clamp_score_100,
    map_hiring_label,
    ratio_to_score_100,
)

logger = logging.getLogger(__name__)


def _now() -> datetime:
    return datetime.now(timezone.utc)


def _company_key(company_id: str | None) -> str:
    return (company_id or "").strip()


def upsert_ai_evaluation(
    *,
    candidate_id: str,
    company_id: str | None = None,
    source_key: str,
    source_type: str = "mock_interview",
    interview_id: str | None = None,
    role_title: str | None = None,
    overall_score: float | None = None,
    technical_score: float | None = None,
    confidence_score: float | None = None,
    communication_score: float | None = None,
    culture_fit_score: float | None = None,
    match_percent: float | None = None,
    recommendation_label: str | None = None,
    recommendation_confidence: float | None = None,
    strengths: list[str] | None = None,
    growth_areas: list[str] | None = None,
    verified_skills: list[str] | None = None,
    meta: dict[str, Any] | None = None,
) -> CandidateAiEvaluationRecord:
    cid = (candidate_id or "").strip()
    if not cid:
        raise ValueError("candidate_id is required")
    sk = (source_key or "").strip()
    if not sk:
        raise ValueError("source_key is required")

    label, display = map_hiring_label(recommendation_label)
    company = _company_key(company_id)
    now = _now()

    db = SessionLocal()
    try:
        row = db.execute(
            select(CandidateAiEvaluationRecord).where(
                CandidateAiEvaluationRecord.company_id == company,
                CandidateAiEvaluationRecord.candidate_id == cid,
                CandidateAiEvaluationRecord.source_key == sk,
            )
        ).scalar_one_or_none()
        if row is None:
            row = CandidateAiEvaluationRecord(
                evaluation_id=f"aie-{uuid4().hex[:16]}",
                company_id=company,
                candidate_id=cid,
                source_key=sk,
                created_at=now,
            )
            db.add(row)

        row.source_type = source_type
        row.interview_id = interview_id
        row.role_title = role_title
        row.overall_score = clamp_score_100(overall_score)
        row.technical_score = clamp_score_100(technical_score)
        row.confidence_score = clamp_score_100(confidence_score)
        row.communication_score = clamp_score_100(communication_score)
        row.culture_fit_score = clamp_score_100(culture_fit_score)
        row.match_percent = clamp_score_100(match_percent)
        row.recommendation_label = label
        row.recommendation_display = display
        row.recommendation_confidence = clamp_score_100(recommendation_confidence)
        row.strengths = list(strengths or [])[:12]
        row.growth_areas = list(growth_areas or [])[:12]
        row.verified_skills = list(verified_skills or [])[:40]
        row.meta = meta or {}
        row.recorded_at = now
        row.updated_at = now
        db.commit()
        db.refresh(row)
        db.expunge(row)
        return row
    except Exception:
        db.rollback()
        raise
    finally:
        db.close()


def next_assessment_attempt_number(
    *,
    candidate_id: str,
    assessment_id: str,
    company_id: str | None = None,
) -> int:
    company = _company_key(company_id)
    cid = (candidate_id or "").strip()
    aid = (assessment_id or "").strip()
    db = SessionLocal()
    try:
        current = db.execute(
            select(func.max(CandidateAssessmentAttemptRecord.attempt_number)).where(
                CandidateAssessmentAttemptRecord.company_id == company,
                CandidateAssessmentAttemptRecord.candidate_id == cid,
                CandidateAssessmentAttemptRecord.assessment_id == aid,
            )
        ).scalar_one()
        return int(current or 0) + 1
    finally:
        db.close()


def upsert_assessment_attempt(
    *,
    candidate_id: str,
    assessment_id: str,
    company_id: str | None = None,
    attempt_number: int | None = None,
    kind: str | None = None,
    role_title: str | None = None,
    score: float | None = None,
    max_score: float | None = None,
    status: str | None = None,
    passed: bool | None = None,
    skill_scores: list[dict[str, Any]] | None = None,
    feedback_markdown: str | None = None,
    meta: dict[str, Any] | None = None,
) -> CandidateAssessmentAttemptRecord:
    cid = (candidate_id or "").strip()
    aid = (assessment_id or "").strip()
    if not cid or not aid:
        raise ValueError("candidate_id and assessment_id are required")

    company = _company_key(company_id)
    score_100 = (
        ratio_to_score_100(score, max_score)
        if max_score is not None
        else clamp_score_100(score)
    )
    status_norm = assessment_status(
        passed=passed, score_100=score_100, explicit=status
    )
    now = _now()

    db = SessionLocal()
    try:
        if attempt_number is None:
            current = db.execute(
                select(func.max(CandidateAssessmentAttemptRecord.attempt_number)).where(
                    CandidateAssessmentAttemptRecord.company_id == company,
                    CandidateAssessmentAttemptRecord.candidate_id == cid,
                    CandidateAssessmentAttemptRecord.assessment_id == aid,
                )
            ).scalar_one()
            attempt_number = int(current or 0) + 1
        else:
            attempt_number = max(1, int(attempt_number))

        row = db.execute(
            select(CandidateAssessmentAttemptRecord).where(
                CandidateAssessmentAttemptRecord.company_id == company,
                CandidateAssessmentAttemptRecord.candidate_id == cid,
                CandidateAssessmentAttemptRecord.assessment_id == aid,
                CandidateAssessmentAttemptRecord.attempt_number == attempt_number,
            )
        ).scalar_one_or_none()
        if row is None:
            row = CandidateAssessmentAttemptRecord(
                attempt_id=f"asa-{uuid4().hex[:16]}",
                company_id=company,
                candidate_id=cid,
                assessment_id=aid,
                attempt_number=attempt_number,
                created_at=now,
            )
            db.add(row)

        row.kind = kind
        row.role_title = role_title
        row.score = score_100
        row.status = status_norm
        row.passed = passed if passed is not None else (status_norm == "passed")
        row.skill_scores = list(skill_scores or [])
        row.feedback_markdown = feedback_markdown
        row.meta = meta or {}
        row.recorded_at = now
        row.updated_at = now
        db.commit()
        db.refresh(row)
        db.expunge(row)
        return row
    except Exception:
        db.rollback()
        raise
    finally:
        db.close()


def _tenant_filter_ai(company_id: str | None):
    company = _company_key(company_id)
    if company:
        return CandidateAiEvaluationRecord.company_id == company
    return True


def _tenant_filter_assessment(company_id: str | None):
    company = _company_key(company_id)
    if company:
        return CandidateAssessmentAttemptRecord.company_id == company
    return True


def list_ai_evaluations(
    *,
    candidate_id: str,
    company_id: str | None = None,
) -> list[CandidateAiEvaluationRecord]:
    cid = (candidate_id or "").strip()
    db = SessionLocal()
    try:
        rows = (
            db.execute(
                select(CandidateAiEvaluationRecord)
                .where(
                    CandidateAiEvaluationRecord.candidate_id == cid,
                    _tenant_filter_ai(company_id),
                )
                .order_by(desc(CandidateAiEvaluationRecord.recorded_at))
            )
            .scalars()
            .all()
        )
        for row in rows:
            db.expunge(row)
        return list(rows)
    finally:
        db.close()


def latest_ai_evaluation(
    *,
    candidate_id: str,
    company_id: str | None = None,
) -> CandidateAiEvaluationRecord | None:
    rows = list_ai_evaluations(candidate_id=candidate_id, company_id=company_id)
    return rows[0] if rows else None


def latest_ai_by_source_type(
    *,
    candidate_id: str,
    company_id: str | None = None,
) -> dict[str, CandidateAiEvaluationRecord]:
    """Latest evaluation per source_type (for overall blend)."""
    out: dict[str, CandidateAiEvaluationRecord] = {}
    for row in list_ai_evaluations(candidate_id=candidate_id, company_id=company_id):
        key = row.source_type or "ingest"
        if key not in out:
            out[key] = row
    return out


def list_assessment_attempts(
    *,
    candidate_id: str,
    company_id: str | None = None,
) -> list[CandidateAssessmentAttemptRecord]:
    cid = (candidate_id or "").strip()
    db = SessionLocal()
    try:
        rows = (
            db.execute(
                select(CandidateAssessmentAttemptRecord)
                .where(
                    CandidateAssessmentAttemptRecord.candidate_id == cid,
                    _tenant_filter_assessment(company_id),
                )
                .order_by(desc(CandidateAssessmentAttemptRecord.recorded_at))
            )
            .scalars()
            .all()
        )
        for row in rows:
            db.expunge(row)
        return list(rows)
    finally:
        db.close()


def latest_assessment_attempts_by_id(
    *,
    candidate_id: str,
    company_id: str | None = None,
) -> list[CandidateAssessmentAttemptRecord]:
    """Latest attempt per assessment_id (retake policy)."""
    seen: dict[str, CandidateAssessmentAttemptRecord] = {}
    for row in list_assessment_attempts(
        candidate_id=candidate_id, company_id=company_id
    ):
        if row.assessment_id not in seen:
            seen[row.assessment_id] = row
    # Keep chronological by latest recorded_at
    return sorted(
        seen.values(),
        key=lambda r: r.recorded_at or datetime.min.replace(tzinfo=timezone.utc),
        reverse=True,
    )


def candidate_has_tenant_data(
    *,
    candidate_id: str,
    company_id: str,
) -> bool:
    """True if this tenant owns any performance rows for the candidate."""
    company = _company_key(company_id)
    cid = (candidate_id or "").strip()
    if not company or not cid:
        return False
    db = SessionLocal()
    try:
        ai = db.execute(
            select(CandidateAiEvaluationRecord.evaluation_id)
            .where(
                CandidateAiEvaluationRecord.candidate_id == cid,
                CandidateAiEvaluationRecord.company_id == company,
            )
            .limit(1)
        ).first()
        if ai:
            return True
        asa = db.execute(
            select(CandidateAssessmentAttemptRecord.attempt_id)
            .where(
                CandidateAssessmentAttemptRecord.candidate_id == cid,
                CandidateAssessmentAttemptRecord.company_id == company,
            )
            .limit(1)
        ).first()
        return bool(asa)
    finally:
        db.close()


def other_tenant_has_data(
    *,
    candidate_id: str,
    company_id: str,
) -> bool:
    """True when data exists only under a different company_id (isolation)."""
    company = _company_key(company_id)
    cid = (candidate_id or "").strip()
    if not company or not cid:
        return False
    db = SessionLocal()
    try:
        ai = db.execute(
            select(CandidateAiEvaluationRecord.evaluation_id)
            .where(
                CandidateAiEvaluationRecord.candidate_id == cid,
                CandidateAiEvaluationRecord.company_id != company,
                CandidateAiEvaluationRecord.company_id != "",
            )
            .limit(1)
        ).first()
        if ai:
            return True
        asa = db.execute(
            select(CandidateAssessmentAttemptRecord.attempt_id)
            .where(
                CandidateAssessmentAttemptRecord.candidate_id == cid,
                CandidateAssessmentAttemptRecord.company_id != company,
                CandidateAssessmentAttemptRecord.company_id != "",
            )
            .limit(1)
        ).first()
        return bool(asa)
    finally:
        db.close()
