"""prompt effective view (problems.md #8) — get_effective_source 단위 테스트.

admin UI 가 prompt_template DB row 만 보고 active prompt 판단하던 문제를 해소.
**caller 별 db 전달 여부에 따라 winner 가 다르므로 두 mode (with_db /
without_db) 모두 표시** — Codex P2 fix.

검증 시나리오:
  - DB only → with_db.winner=db, without_db.winner=None
  - file only → with_db.winner=file (no_active_db_row), without_db.winner=file
  - 둘 다 → with_db.winner=db, without_db.winner=file (caller 모드별 분기)
  - 둘 다 없음 → 양쪽 None (no_file)
  - DB exception → with_db file fallback (logger.warning)
  - special chars → no crash
"""
from __future__ import annotations

from typing import Any, List

import pytest


class _StubResult:
    def __init__(self, rows: List[Any]):
        self._rows = rows

    def fetchall(self):
        return self._rows


class _StubDB:
    def __init__(self, rows: List[Any]):
        self._rows = rows

    def execute(self, sql, params=None):
        return _StubResult(self._rows)


class _Row:
    """prompt_template row stub."""

    def __init__(self, version: str, *, id: str = "id-1", schema_json: str = ""):
        self.version = version
        self.id = id
        self.schema_json = schema_json


def test_effective_db_only_winner_diverges_by_mode(monkeypatch, tmp_path):
    """DB row 있고 file 없음 → with_db=db, without_db=None (file 미존재)."""
    from app.modules import prompt_loader

    monkeypatch.setattr(prompt_loader, "PROMPTS_BASE", tmp_path)
    db = _StubDB([_Row("10.20260301", id="db-row-1", schema_json="{}")])
    out = prompt_loader.get_effective_source("scene_extractor", "system", db)

    assert out["candidates"]["db"]["version"] == "10.20260301"
    assert out["candidates"]["db"]["id"] == "db-row-1"
    assert out["candidates"]["db"]["has_schema"] is True
    assert out["candidates"]["file"] is None
    assert out["effective"]["with_db"]["winner"] == "db"
    assert out["effective"]["without_db"]["winner"] is None
    assert out["effective"]["without_db"]["fallback_reason"] == "no_file"


def test_effective_both_candidates_diverge_by_mode(monkeypatch, tmp_path):
    """DB + file 둘 다 → with_db=db (DB 우선), without_db=file (caller 가 db 미전달 시)."""
    from app.modules import prompt_loader

    monkeypatch.setattr(prompt_loader, "PROMPTS_BASE", tmp_path)
    fdir = tmp_path / "scene_extractor" / "12.20260301"
    fdir.mkdir(parents=True)
    (fdir / "system.md").write_text("file content", encoding="utf-8")

    db = _StubDB([_Row("10.20260301", id="db-row-1")])
    out = prompt_loader.get_effective_source("scene_extractor", "system", db)

    # 두 후보 모두 표시
    assert out["candidates"]["db"]["version"] == "10.20260301"
    assert out["candidates"]["file"]["version"] == "12.20260301"
    # caller 가 db 전달 시 → DB 우선
    assert out["effective"]["with_db"]["winner"] == "db"
    # caller 가 db 미전달 시 → file 사용 (DB 무시)
    assert out["effective"]["without_db"]["winner"] == "file"


def test_effective_file_only_when_no_db_row(monkeypatch, tmp_path):
    """DB row 없음 + file 있음 → with_db=file (no_active_db_row), without_db=file."""
    from app.modules import prompt_loader

    monkeypatch.setattr(prompt_loader, "PROMPTS_BASE", tmp_path)
    fdir = tmp_path / "outlook_extractor" / "5.20260301"
    fdir.mkdir(parents=True)
    (fdir / "system.md").write_text("file content", encoding="utf-8")

    db = _StubDB([])
    out = prompt_loader.get_effective_source("outlook_extractor", "system", db)

    assert out["candidates"]["db"] is None
    assert out["candidates"]["file"]["version"] == "5.20260301"
    assert out["effective"]["with_db"]["winner"] == "file"
    assert out["effective"]["with_db"]["fallback_reason"] == "no_active_db_row"
    assert out["effective"]["without_db"]["winner"] == "file"
    assert out["effective"]["without_db"]["fallback_reason"] is None


def test_effective_no_db_session_argument(monkeypatch, tmp_path):
    """db=None + file 있음 → with_db.fallback_reason=no_db_session, without_db=file."""
    from app.modules import prompt_loader

    monkeypatch.setattr(prompt_loader, "PROMPTS_BASE", tmp_path)
    fdir = tmp_path / "mod" / "1.20260101"
    fdir.mkdir(parents=True)
    (fdir / "system.md").write_text("legacy", encoding="utf-8")

    out = prompt_loader.get_effective_source("mod", "system", db=None)
    assert out["candidates"]["db"] is None
    assert out["candidates"]["file"]["version"] == "1.20260101"
    assert out["effective"]["with_db"]["winner"] == "file"
    assert out["effective"]["with_db"]["fallback_reason"] == "no_db_session"
    assert out["effective"]["without_db"]["winner"] == "file"


def test_effective_no_candidates(monkeypatch, tmp_path):
    """둘 다 없음 → 양쪽 winner=None + fallback_reason=no_file."""
    from app.modules import prompt_loader

    monkeypatch.setattr(prompt_loader, "PROMPTS_BASE", tmp_path)
    db = _StubDB([])
    out = prompt_loader.get_effective_source("missing_mod", "missing", db)
    assert out["candidates"]["db"] is None
    assert out["candidates"]["file"] is None
    assert out["effective"]["with_db"]["winner"] is None
    assert out["effective"]["with_db"]["fallback_reason"] == "no_file"
    assert out["effective"]["without_db"]["winner"] is None
    assert out["effective"]["without_db"]["fallback_reason"] == "no_file"


def test_effective_db_picks_numeric_latest_when_multiple_active(monkeypatch, tmp_path):
    """DB 여러 active row 있을 때 numeric latest (lexical 9>10 버그 회피)."""
    from app.modules import prompt_loader

    monkeypatch.setattr(prompt_loader, "PROMPTS_BASE", tmp_path)
    db = _StubDB([
        _Row("9.20260301", id="r9"),
        _Row("12.20260301", id="r12"),
        _Row("10.20260301", id="r10"),
    ])
    out = prompt_loader.get_effective_source("any", "any", db)
    assert out["candidates"]["db"]["version"] == "12.20260301"
    assert out["candidates"]["db"]["id"] == "r12"


def test_effective_file_skips_versions_without_target(monkeypatch, tmp_path):
    """file fallback 가 stem 없는 version 디렉토리 skip 후 다음 후보."""
    from app.modules import prompt_loader

    monkeypatch.setattr(prompt_loader, "PROMPTS_BASE", tmp_path)
    (tmp_path / "mod" / "12.x").mkdir(parents=True)
    (tmp_path / "mod" / "10.x").mkdir(parents=True)
    (tmp_path / "mod" / "10.x" / "system.md").write_text("v10", encoding="utf-8")
    db = _StubDB([])

    out = prompt_loader.get_effective_source("mod", "system", db)
    assert out["candidates"]["file"]["version"] == "10.x"
    assert out["effective"]["with_db"]["winner"] == "file"


def test_effective_db_exception_falls_back_to_file(monkeypatch, tmp_path, caplog):
    """DB 조회 시 예외 → with_db file fallback (logger.warning)."""
    import logging
    from app.modules import prompt_loader

    monkeypatch.setattr(prompt_loader, "PROMPTS_BASE", tmp_path)
    fdir = tmp_path / "mod" / "5.x"
    fdir.mkdir(parents=True)
    (fdir / "system.md").write_text("file fallback", encoding="utf-8")

    def _broken_lookup(*a, **kw):
        raise RuntimeError("simulated DB failure")

    monkeypatch.setattr(prompt_loader, "_select_latest_active_row", _broken_lookup)

    class _AnyDB:
        pass

    with caplog.at_level(logging.WARNING):
        out = prompt_loader.get_effective_source("mod", "system", db=_AnyDB())
    assert out["candidates"]["db"] is None
    assert out["candidates"]["file"]["version"] == "5.x"
    assert out["effective"]["with_db"]["winner"] == "file"
    assert out["effective"]["with_db"]["fallback_reason"] == "no_active_db_row"
    assert any("DB prompt effective lookup failed" in r.message for r in caplog.records)


def test_effective_special_chars_in_name_no_crash(monkeypatch, tmp_path):
    """name 에 dot 등 특수문자 → crash 없이 winner=None."""
    from app.modules import prompt_loader

    monkeypatch.setattr(prompt_loader, "PROMPTS_BASE", tmp_path)
    db = _StubDB([])
    out = prompt_loader.get_effective_source("mod", "system.v2", db)
    assert out["candidates"]["db"] is None
    assert out["candidates"]["file"] is None
    assert out["effective"]["with_db"]["winner"] is None
    assert out["effective"]["with_db"]["fallback_reason"] == "no_file"