"""Persist interview creation workflows + questions."""

from __future__ import annotations

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

from sqlalchemy import delete, select

from app.database import SessionLocal
from app.models.interview_workflow import (
    InterviewWorkflowQuestionRecord,
    InterviewWorkflowRecord,
)


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


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


def create_workflow(
    *,
    title: str,
    role_title: str,
    company_id: str | None = None,
    user_id: str | None = None,
    interview_id: str | None = None,
    skills: list[str] | None = None,
    experience_level: str | None = None,
    duration_minutes: int | None = 30,
    language: str = "en",
    difficulty: str = "medium",
    mode: str = "video",
    job_description: str | None = None,
    meta: dict[str, Any] | None = None,
) -> InterviewWorkflowRecord:
    now = _now()
    wid = f"iwf-{uuid4().hex[:16]}"
    iid = (interview_id or "").strip() or f"int-{uuid4().hex[:12]}"
    db = SessionLocal()
    try:
        row = InterviewWorkflowRecord(
            workflow_id=wid,
            company_id=_company_key(company_id),
            user_id=(user_id or "").strip() or None,
            interview_id=iid,
            title=title.strip(),
            role_title=role_title.strip(),
            job_description=job_description,
            skills=list(skills or []),
            experience_level=experience_level,
            duration_minutes=duration_minutes,
            language=(language or "en").strip()[:32] or "en",
            difficulty=(difficulty or "medium").strip().lower() or "medium",
            mode=(mode or "video").strip() or "video",
            status="draft",
            version=1,
            meta=meta or {},
            created_at=now,
            updated_at=now,
        )
        db.add(row)
        db.commit()
        db.refresh(row)
        db.expunge(row)
        return row
    except Exception:
        db.rollback()
        raise
    finally:
        db.close()


def get_workflow(
    workflow_id: str,
    *,
    company_id: str | None = None,
) -> InterviewWorkflowRecord | None:
    wid = (workflow_id or "").strip()
    if not wid:
        return None
    db = SessionLocal()
    try:
        q = select(InterviewWorkflowRecord).where(
            InterviewWorkflowRecord.workflow_id == wid
        )
        company = _company_key(company_id)
        if company:
            q = q.where(InterviewWorkflowRecord.company_id == company)
        row = db.execute(q).scalar_one_or_none()
        if row is not None:
            db.expunge(row)
        return row
    finally:
        db.close()


def get_workflow_by_interview_id(
    interview_id: str,
    *,
    company_id: str | None = None,
) -> InterviewWorkflowRecord | None:
    """Latest workflow for a Platform interviewId (e.g. lookup ``123``)."""
    iid = (interview_id or "").strip()
    if not iid:
        return None
    db = SessionLocal()
    try:
        q = (
            select(InterviewWorkflowRecord)
            .where(InterviewWorkflowRecord.interview_id == iid)
            .order_by(InterviewWorkflowRecord.updated_at.desc())
        )
        company = _company_key(company_id)
        if company:
            q = q.where(InterviewWorkflowRecord.company_id == company)
        row = db.execute(q).scalars().first()
        if row is not None:
            db.expunge(row)
        return row
    finally:
        db.close()


def resolve_workflow(
    workflow_or_interview_id: str,
    *,
    company_id: str | None = None,
) -> InterviewWorkflowRecord | None:
    """Resolve by workflowId first, then by interviewId for the tenant."""
    row = get_workflow(workflow_or_interview_id, company_id=company_id)
    if row is not None:
        return row
    return get_workflow_by_interview_id(
        workflow_or_interview_id, company_id=company_id
    )


def update_workflow(
    workflow_id: str,
    *,
    company_id: str | None = None,
    fields: dict[str, Any],
) -> InterviewWorkflowRecord | None:
    db = SessionLocal()
    try:
        q = select(InterviewWorkflowRecord).where(
            InterviewWorkflowRecord.workflow_id == workflow_id
        )
        company = _company_key(company_id)
        if company:
            q = q.where(InterviewWorkflowRecord.company_id == company)
        row = db.execute(q).scalar_one_or_none()
        if row is None:
            return None
        for key, value in fields.items():
            if hasattr(row, key):
                setattr(row, key, value)
        row.version = int(row.version or 1) + 1
        row.updated_at = _now()
        db.commit()
        db.refresh(row)
        db.expunge(row)
        return row
    except Exception:
        db.rollback()
        raise
    finally:
        db.close()


def list_questions(workflow_id: str) -> list[InterviewWorkflowQuestionRecord]:
    db = SessionLocal()
    try:
        rows = (
            db.execute(
                select(InterviewWorkflowQuestionRecord)
                .where(InterviewWorkflowQuestionRecord.workflow_id == workflow_id)
                .order_by(InterviewWorkflowQuestionRecord.position.asc())
            )
            .scalars()
            .all()
        )
        for row in rows:
            db.expunge(row)
        return list(rows)
    finally:
        db.close()


def replace_ai_questions(
    workflow_id: str,
    questions: list[dict[str, Any]],
    *,
    keep_custom: bool = True,
) -> list[InterviewWorkflowQuestionRecord]:
    db = SessionLocal()
    try:
        existing = (
            db.execute(
                select(InterviewWorkflowQuestionRecord).where(
                    InterviewWorkflowQuestionRecord.workflow_id == workflow_id
                )
            )
            .scalars()
            .all()
        )
        customs = [r for r in existing if keep_custom and r.source == "custom"]
        if keep_custom:
            db.execute(
                delete(InterviewWorkflowQuestionRecord).where(
                    InterviewWorkflowQuestionRecord.workflow_id == workflow_id,
                    InterviewWorkflowQuestionRecord.source != "custom",
                )
            )
        else:
            db.execute(
                delete(InterviewWorkflowQuestionRecord).where(
                    InterviewWorkflowQuestionRecord.workflow_id == workflow_id
                )
            )
            customs = []

        position = 0
        out: list[InterviewWorkflowQuestionRecord] = []
        now = _now()
        for item in questions:
            row = InterviewWorkflowQuestionRecord(
                id=f"iq-{uuid4().hex[:16]}",
                workflow_id=workflow_id,
                question_id=str(item.get("question_id") or f"q-{uuid4().hex[:10]}"),
                text=str(item.get("text") or ""),
                question_type=str(item.get("question_type") or "short_answer"),
                skill=item.get("skill"),
                difficulty=item.get("difficulty"),
                category=item.get("category"),
                evaluation_criteria=list(item.get("evaluation_criteria") or []),
                source="ai",
                position=position,
                response_format=item.get("response_format"),
                response_time_seconds=item.get("response_time_seconds"),
                meta=dict(item.get("meta") or {}),
                created_at=now,
                updated_at=now,
            )
            db.add(row)
            out.append(row)
            position += 1

        for custom in sorted(customs, key=lambda r: r.position):
            custom.position = position
            custom.updated_at = now
            position += 1
            out.append(custom)

        db.commit()
        for row in out:
            db.refresh(row)
            db.expunge(row)
        return out
    except Exception:
        db.rollback()
        raise
    finally:
        db.close()


def add_question(
    workflow_id: str,
    *,
    text: str,
    question_type: str = "short_answer",
    skill: str | None = None,
    difficulty: str | None = None,
    category: str | None = None,
    evaluation_criteria: list[str] | None = None,
    source: str = "custom",
    response_format: str | None = "text",
    response_time_seconds: int | None = None,
    position: int | None = None,
    meta: dict[str, Any] | None = None,
) -> InterviewWorkflowQuestionRecord:
    db = SessionLocal()
    try:
        rows = (
            db.execute(
                select(InterviewWorkflowQuestionRecord).where(
                    InterviewWorkflowQuestionRecord.workflow_id == workflow_id
                )
            )
            .scalars()
            .all()
        )
        next_pos = (
            position
            if position is not None
            else (max((r.position for r in rows), default=-1) + 1)
        )
        now = _now()
        row = InterviewWorkflowQuestionRecord(
            id=f"iq-{uuid4().hex[:16]}",
            workflow_id=workflow_id,
            question_id=f"q-{uuid4().hex[:10]}",
            text=text.strip(),
            question_type=question_type,
            skill=skill,
            difficulty=difficulty,
            category=category,
            evaluation_criteria=list(evaluation_criteria or []),
            source=source,
            position=next_pos,
            response_format=response_format,
            response_time_seconds=response_time_seconds,
            meta=meta or {},
            created_at=now,
            updated_at=now,
        )
        db.add(row)
        db.commit()
        db.refresh(row)
        db.expunge(row)
        return row
    except Exception:
        db.rollback()
        raise
    finally:
        db.close()


def update_question(
    workflow_id: str,
    question_id: str,
    fields: dict[str, Any],
) -> InterviewWorkflowQuestionRecord | None:
    db = SessionLocal()
    try:
        row = db.execute(
            select(InterviewWorkflowQuestionRecord).where(
                InterviewWorkflowQuestionRecord.workflow_id == workflow_id,
                InterviewWorkflowQuestionRecord.question_id == question_id,
            )
        ).scalar_one_or_none()
        if row is None:
            return None
        for key, value in fields.items():
            if hasattr(row, key) and key not in {"id", "workflow_id", "question_id"}:
                setattr(row, key, value)
        row.updated_at = _now()
        db.commit()
        db.refresh(row)
        db.expunge(row)
        return row
    except Exception:
        db.rollback()
        raise
    finally:
        db.close()


def delete_question(workflow_id: str, question_id: str) -> bool:
    db = SessionLocal()
    try:
        row = db.execute(
            select(InterviewWorkflowQuestionRecord).where(
                InterviewWorkflowQuestionRecord.workflow_id == workflow_id,
                InterviewWorkflowQuestionRecord.question_id == question_id,
            )
        ).scalar_one_or_none()
        if row is None:
            return False
        db.delete(row)
        db.commit()
        return True
    except Exception:
        db.rollback()
        raise
    finally:
        db.close()


def reorder_questions(workflow_id: str, question_ids: list[str]) -> list[InterviewWorkflowQuestionRecord]:
    db = SessionLocal()
    try:
        rows = (
            db.execute(
                select(InterviewWorkflowQuestionRecord).where(
                    InterviewWorkflowQuestionRecord.workflow_id == workflow_id
                )
            )
            .scalars()
            .all()
        )
        by_id = {r.question_id: r for r in rows}
        now = _now()
        for idx, qid in enumerate(question_ids):
            row = by_id.get(qid)
            if row is None:
                continue
            row.position = idx
            row.updated_at = now
        # Append any missing at end
        remaining = [r for r in rows if r.question_id not in set(question_ids)]
        base = len(question_ids)
        for offset, row in enumerate(sorted(remaining, key=lambda r: r.position)):
            row.position = base + offset
            row.updated_at = now
        db.commit()
        refreshed = (
            db.execute(
                select(InterviewWorkflowQuestionRecord)
                .where(InterviewWorkflowQuestionRecord.workflow_id == workflow_id)
                .order_by(InterviewWorkflowQuestionRecord.position.asc())
            )
            .scalars()
            .all()
        )
        for row in refreshed:
            db.expunge(row)
        return list(refreshed)
    except Exception:
        db.rollback()
        raise
    finally:
        db.close()


class QuestionBatchError(Exception):
    """Raised for batch validation failures; service maps to HTTP 4xx."""

    def __init__(self, detail: str, *, status_code: int = 422) -> None:
        super().__init__(detail)
        self.detail = detail
        self.status_code = status_code


def apply_question_batch(
    workflow_id: str,
    *,
    delete_ids: list[str] | None = None,
    updates: list[dict[str, Any]] | None = None,
    creates: list[dict[str, Any]] | None = None,
    reorder_ids: list[str] | None = None,
    strict_reorder: bool = True,
) -> list[InterviewWorkflowQuestionRecord]:
    """Apply delete → update → create → reorder in one DB transaction.

    ``updates`` items: ``{"question_id": str, "fields": dict}``.
    ``creates`` items: kwargs matching ``add_question`` (minus workflow_id).
    Raises ``QuestionBatchError`` on missing ids / empty ops / bad reorder.
    """
    delete_ids = list(delete_ids or [])
    updates = list(updates or [])
    creates = list(creates or [])
    if not (delete_ids or updates or creates or reorder_ids is not None):
        raise QuestionBatchError("Batch must include at least one operation")

    db = SessionLocal()
    try:
        rows = (
            db.execute(
                select(InterviewWorkflowQuestionRecord).where(
                    InterviewWorkflowQuestionRecord.workflow_id == workflow_id
                )
            )
            .scalars()
            .all()
        )
        by_id = {r.question_id: r for r in rows}
        now = _now()

        # --- validate before mutating ---
        missing_delete = [qid for qid in delete_ids if qid not in by_id]
        if missing_delete:
            raise QuestionBatchError(
                f"Cannot delete unknown questionId(s): {', '.join(missing_delete)}",
                status_code=404,
            )

        delete_set = set(delete_ids)
        for item in updates:
            qid = item.get("question_id") or ""
            if qid in delete_set:
                raise QuestionBatchError(
                    f"Cannot update questionId marked for delete: {qid}"
                )
            if qid not in by_id:
                raise QuestionBatchError(
                    f"Cannot update unknown questionId: {qid}",
                    status_code=404,
                )

        if reorder_ids is not None:
            # After deletes/creates, reorder must reference surviving + new ids.
            # New creates get ids at insert time — validate known ids only after creates.
            pass

        # --- delete ---
        for qid in delete_ids:
            row = by_id.pop(qid, None)
            if row is not None:
                db.delete(row)

        # --- update ---
        for item in updates:
            qid = item["question_id"]
            row = by_id[qid]
            for key, value in (item.get("fields") or {}).items():
                if hasattr(row, key) and key not in {"id", "workflow_id", "question_id"}:
                    setattr(row, key, value)
            row.updated_at = now

        # --- create ---
        max_pos = max((r.position for r in by_id.values()), default=-1)
        created_ids: list[str] = []
        for raw in creates:
            next_pos = raw.get("position")
            if next_pos is None:
                max_pos += 1
                next_pos = max_pos
            else:
                max_pos = max(max_pos, int(next_pos))
            qid = f"q-{uuid4().hex[:10]}"
            row = InterviewWorkflowQuestionRecord(
                id=f"iq-{uuid4().hex[:16]}",
                workflow_id=workflow_id,
                question_id=qid,
                text=str(raw.get("text") or "").strip(),
                question_type=raw.get("question_type") or "short_answer",
                skill=raw.get("skill"),
                difficulty=raw.get("difficulty"),
                category=raw.get("category"),
                evaluation_criteria=list(raw.get("evaluation_criteria") or []),
                source=raw.get("source") or "custom",
                position=int(next_pos),
                response_format=raw.get("response_format") or "text",
                response_time_seconds=raw.get("response_time_seconds"),
                meta=dict(raw.get("meta") or {}),
                created_at=now,
                updated_at=now,
            )
            db.add(row)
            by_id[qid] = row
            created_ids.append(qid)

        db.flush()

        # --- reorder ---
        if reorder_ids is not None:
            if not reorder_ids:
                raise QuestionBatchError("reorder must be a non-empty list when provided")
            unknown = [qid for qid in reorder_ids if qid not in by_id]
            if unknown and strict_reorder:
                raise QuestionBatchError(
                    f"reorder references unknown questionId(s): {', '.join(unknown)}"
                )
            seen: set[str] = set()
            ordered: list[str] = []
            for qid in reorder_ids:
                if qid in by_id and qid not in seen:
                    ordered.append(qid)
                    seen.add(qid)
            # Append any not listed (e.g. newly created if client omitted them)
            for qid, row in sorted(by_id.items(), key=lambda kv: kv[1].position):
                if qid not in seen:
                    ordered.append(qid)
            for idx, qid in enumerate(ordered):
                row = by_id[qid]
                row.position = idx
                row.updated_at = now

        db.commit()
        refreshed = (
            db.execute(
                select(InterviewWorkflowQuestionRecord)
                .where(InterviewWorkflowQuestionRecord.workflow_id == workflow_id)
                .order_by(InterviewWorkflowQuestionRecord.position.asc())
            )
            .scalars()
            .all()
        )
        for row in refreshed:
            db.expunge(row)
        return list(refreshed)
    except QuestionBatchError:
        db.rollback()
        raise
    except Exception:
        db.rollback()
        raise
    finally:
        db.close()
