"""Oracle ADB persistence for the Smilegate customer QA benchmark."""

from __future__ import annotations

from contextlib import contextmanager
from datetime import datetime, timezone
import json
import os
from pathlib import Path
from typing import Any, Iterator, Mapping
from urllib.parse import parse_qs

import oracledb

from src.poc4.qa_history import QaQuestion, load_benchmark_questions, question_fingerprint, question_from_record


class QaHistoryStoreError(RuntimeError):
    """A safe user-facing persistence error."""


QUESTION_TABLE = "SG_AI_QA_QUESTION"
ANSWER_TABLE = "SG_AI_QA_ANSWER"
HISTORICAL_RUN_KEY = "HISTORICAL:2026-07-21:term-dict-final-v2"


def _env_value(name: str, env_file: Path | None = None) -> str:
    value = os.environ.get(name, "").strip()
    if value or env_file is None or not env_file.is_file():
        return value
    try:
        lines = env_file.read_text(encoding="utf-8").splitlines()
    except (OSError, UnicodeError):
        return ""
    for line in lines:
        line = line.strip()
        if not line or line.startswith("#") or "=" not in line:
            continue
        if line.startswith("export "):
            line = line[7:].lstrip()
        key, raw = line.split("=", 1)
        if key.strip() != name:
            continue
        raw = raw.strip()
        if len(raw) >= 2 and raw[0] == raw[-1] and raw[0] in {"'", '"'}:
            raw = raw[1:-1]
        return raw.strip()
    return ""


def _normalize_oracle_dsn(raw_dsn: str) -> tuple[str, str]:
    value = str(raw_dsn or "").strip()
    if value.startswith("jdbc:oracle:thin:@"):
        value = value[len("jdbc:oracle:thin:@"):]
    if "?" not in value:
        return value, ""
    dsn, query = value.split("?", 1)
    parsed = parse_qs(query, keep_blank_values=False)
    wallet_dir = (parsed.get("TNS_ADMIN") or parsed.get("tns_admin") or [""])[0]
    return dsn.strip(), wallet_dir.strip()


def _read_lob(value: Any) -> Any:
    return value.read() if hasattr(value, "read") else value


def _record_from_cursor(cursor: Any, row: Any) -> dict[str, Any]:
    names = [column[0].lower() for column in cursor.description]
    return {name: _read_lob(value) for name, value in zip(names, row)}


def _to_json(value: Mapping[str, Any] | None) -> str:
    payload = dict(value or {})
    text = json.dumps(payload, ensure_ascii=False, default=str)
    if len(text) <= 120_000:
        return text
    return json.dumps(
        {
            "truncated": True,
            "preview": text[:119_800],
        },
        ensure_ascii=False,
    )


def _answer_record(row: Mapping[str, Any]) -> dict[str, Any]:
    result_json = str(row.get("result_json") or "").strip()
    try:
        result = json.loads(result_json) if result_json else {}
    except ValueError:
        result = {"raw": result_json}
    return {
        "answer_seq": row.get("answer_seq"),
        "question_id": row.get("question_id"),
        "answer_kind": str(row.get("answer_kind") or ""),
        "run_key": str(row.get("run_key") or ""),
        "conversation_id": str(row.get("conversation_id") or ""),
        "requested_by": str(row.get("requested_by") or ""),
        "requested_at": str(row.get("requested_at") or ""),
        "model_profile": str(row.get("model_profile") or ""),
        "generated_sql": str(row.get("generated_sql") or ""),
        "answer_text": str(row.get("answer_text") or ""),
        "result": result,
        "execution_output": str(row.get("execution_output") or ""),
        "execution_status": str(row.get("execution_status") or ""),
        "judgment_status": str(row.get("judgment_status") or ""),
        "judgment_reason": str(row.get("judgment_reason") or ""),
        "duration_ms": row.get("duration_ms"),
        "created_at": str(row.get("created_at") or ""),
    }


class QaHistoryStore:
    def __init__(self, *, env_file: Path | None = None) -> None:
        self._env_file = env_file
        self._pool: Any | None = None

    def _config(self) -> dict[str, str]:
        username = (
            _env_value("POC4_QA_DB_USERNAME", self._env_file)
            or _env_value("BACKOFFICE_SELECT_AI_DB_USERNAME", self._env_file)
        )
        password = (
            _env_value("POC4_QA_DB_PASSWORD", self._env_file)
            or _env_value("BACKOFFICE_SELECT_AI_DB_PASSWORD", self._env_file)
        )
        raw_dsn = (
            _env_value("POC4_QA_DB_DSN", self._env_file)
            or _env_value("BACKOFFICE_SELECT_AI_DB_URL", self._env_file)
        )
        dsn, wallet_from_dsn = _normalize_oracle_dsn(raw_dsn)
        wallet_dir = (
            _env_value("POC4_QA_DB_WALLET_DIR", self._env_file)
            or wallet_from_dsn
            or _env_value("ORACLE_WALLET_DIR", self._env_file)
        )
        if not username or not password or not dsn:
            raise QaHistoryStoreError("질답 이력 DB 접속 설정을 확인해 주세요.")
        return {
            "username": username,
            "password": password,
            "dsn": dsn,
            "wallet_dir": wallet_dir,
        }

    def _get_pool(self) -> Any:
        if self._pool is not None:
            return self._pool
        config = self._config()
        kwargs: dict[str, Any] = {
            "user": config["username"],
            "password": config["password"],
            "dsn": config["dsn"],
            "min": 1,
            "max": 3,
            "increment": 1,
            "getmode": oracledb.POOL_GETMODE_WAIT,
        }
        wallet_dir = Path(config["wallet_dir"]).expanduser()
        if config["wallet_dir"]:
            if not wallet_dir.is_dir():
                raise QaHistoryStoreError("질답 이력 DB Wallet 경로를 확인해 주세요.")
            kwargs["config_dir"] = str(wallet_dir)
            kwargs["wallet_location"] = str(wallet_dir)
        try:
            self._pool = oracledb.create_pool(**kwargs)
            return self._pool
        except (oracledb.Error, OSError, ValueError) as exc:
            raise QaHistoryStoreError("질답 이력 DB에 연결하지 못했습니다.") from exc

    @contextmanager
    def _connection(self) -> Iterator[Any]:
        try:
            with self._get_pool().acquire() as connection:
                yield connection
        except QaHistoryStoreError:
            raise
        except (oracledb.Error, OSError, ValueError) as exc:
            raise QaHistoryStoreError("질답 이력 DB 작업에 실패했습니다.") from exc

    def list_questions(self, *, limit: int = 200) -> list[QaQuestion]:
        sql = f"""
            SELECT q.question_id, q.question_code, q.category, q.title,
                   q.question_text, q.source_document, q.source_sheet,
                   q.source_row, q.source_scenario, q.sample_sql,
                   q.expected_focus, q.baseline_sql, q.baseline_answer,
                   q.support_level, q.evaluation_rule_json,
                   latest.judgment_status AS last_judgment_status,
                   TO_CHAR(latest.evaluated_at AT TIME ZONE 'Asia/Seoul',
                           'YYYY-MM-DD HH24:MI:SS TZH:TZM') AS last_evaluated_at
            FROM {QUESTION_TABLE} q
            LEFT JOIN (
                SELECT question_id, judgment_status, evaluated_at
                FROM (
                    SELECT question_id, judgment_status, evaluated_at,
                           ROW_NUMBER() OVER (
                               PARTITION BY question_id ORDER BY answer_seq DESC
                           ) AS row_no
                    FROM {ANSWER_TABLE}
                )
                WHERE row_no = 1
            ) latest ON latest.question_id = q.question_id
            WHERE q.active_yn = 'Y'
            ORDER BY q.category, q.question_code
            FETCH FIRST :row_limit ROWS ONLY
        """
        with self._connection() as connection:
            with connection.cursor() as cursor:
                cursor.execute(sql, {"row_limit": int(limit)})
                rows = [_record_from_cursor(cursor, row) for row in cursor]
        return [question_from_record(row) for row in rows]

    def get_question(self, question_id: int) -> QaQuestion | None:
        sql = f"""
            SELECT question_id, question_code, category, title, question_text,
                   source_document, source_sheet, source_row, source_scenario,
                   sample_sql, expected_focus, baseline_sql, baseline_answer,
                   support_level, evaluation_rule_json
            FROM {QUESTION_TABLE}
            WHERE question_id = :question_id AND active_yn = 'Y'
        """
        with self._connection() as connection:
            with connection.cursor() as cursor:
                cursor.execute(sql, {"question_id": int(question_id)})
                row = cursor.fetchone()
                return question_from_record(_record_from_cursor(cursor, row)) if row else None

    def list_answers(self, question_id: int, *, limit: int = 30) -> list[dict[str, Any]]:
        sql = f"""
            SELECT answer_seq, question_id, answer_kind, run_key, conversation_id,
                   requested_by,
                   TO_CHAR(requested_at AT TIME ZONE 'Asia/Seoul',
                           'YYYY-MM-DD HH24:MI:SS TZH:TZM') AS requested_at,
                   model_profile, generated_sql, answer_text, result_json,
                   execution_output, execution_status, judgment_status,
                   judgment_reason, duration_ms,
                   TO_CHAR(created_at AT TIME ZONE 'Asia/Seoul',
                           'YYYY-MM-DD HH24:MI:SS TZH:TZM') AS created_at
            FROM {ANSWER_TABLE}
            WHERE question_id = :question_id
            ORDER BY answer_seq DESC
            FETCH FIRST :row_limit ROWS ONLY
        """
        with self._connection() as connection:
            with connection.cursor() as cursor:
                cursor.execute(sql, {"question_id": int(question_id), "row_limit": int(limit)})
                rows = [_record_from_cursor(cursor, row) for row in cursor]
        return [_answer_record(row) for row in rows]

    def find_or_create_free_text_question(self, question_text: str) -> QaQuestion:
        normalized = str(question_text or "").strip()
        if not normalized:
            raise QaHistoryStoreError("자유 질의가 비어 있습니다.")
        fingerprint = question_fingerprint(normalized)
        code = f"ADHOC-{fingerprint[:12].upper()}"
        merge_sql = f"""
            MERGE INTO {QUESTION_TABLE} target
            USING (SELECT :question_hash AS question_hash FROM dual) source
            ON (target.question_hash = source.question_hash)
            WHEN NOT MATCHED THEN INSERT (
                question_code, question_source, question_hash, category, title,
                question_text, support_level, evaluation_rule_json, active_yn
            ) VALUES (
                :question_code, 'FREE_TEXT', :question_hash, 'FREE_TEXT',
                :title, :question_text, 'REVIEW', '{{}}', 'Y'
            )
        """
        with self._connection() as connection:
            with connection.cursor() as cursor:
                cursor.execute(
                    merge_sql,
                    {
                        "question_hash": fingerprint,
                        "question_code": code,
                        "title": normalized[:180],
                        "question_text": normalized,
                    },
                )
                connection.commit()
                cursor.execute(
                    f"""SELECT question_id FROM {QUESTION_TABLE}
                         WHERE question_hash = :question_hash""",
                    {"question_hash": fingerprint},
                )
                row = cursor.fetchone()
        if not row:
            raise QaHistoryStoreError("자유 질의 마스터를 저장하지 못했습니다.")
        question = self.get_question(int(row[0]))
        if question is None:
            raise QaHistoryStoreError("자유 질의 마스터를 다시 읽지 못했습니다.")
        return question

    def record_answer(
        self,
        *,
        question_id: int,
        answer_kind: str,
        conversation_id: str,
        requested_by: str,
        model_profile: str,
        generated_sql: str,
        answer_text: str,
        result: Mapping[str, Any] | None,
        execution_output: str,
        execution_status: str,
        judgment_status: str,
        judgment_reason: str,
        duration_ms: int | None,
        run_key: str = "",
    ) -> None:
        sql = f"""
            INSERT INTO {ANSWER_TABLE} (
                question_id, answer_kind, run_key, conversation_id, requested_by,
                requested_at, model_profile, generated_sql, answer_text, result_json,
                execution_output, execution_status, judgment_status,
                judgment_reason, duration_ms
            ) VALUES (
                :question_id, :answer_kind, :run_key, :conversation_id,
                :requested_by, SYSTIMESTAMP, :model_profile, :generated_sql,
                :answer_text, :result_json, :execution_output, :execution_status,
                :judgment_status, :judgment_reason, :duration_ms
            )
        """
        with self._connection() as connection:
            with connection.cursor() as cursor:
                cursor.execute(
                    sql,
                    {
                        "question_id": int(question_id),
                        "answer_kind": str(answer_kind)[:20],
                        "run_key": str(run_key)[:100] or None,
                        "conversation_id": str(conversation_id)[:100] or None,
                        "requested_by": str(requested_by)[:100] or None,
                        "model_profile": str(model_profile)[:100] or None,
                        "generated_sql": str(generated_sql or ""),
                        "answer_text": str(answer_text or ""),
                        "result_json": _to_json(result),
                        "execution_output": str(execution_output or ""),
                        "execution_status": str(execution_status)[:40] or None,
                        "judgment_status": str(judgment_status)[:20],
                        "judgment_reason": str(judgment_reason or ""),
                        "duration_ms": duration_ms,
                    },
                )
                connection.commit()

    def seed_benchmark(self, benchmark_file: Path) -> tuple[int, int]:
        questions = load_benchmark_questions(benchmark_file)
        raw = json.loads(benchmark_file.read_text(encoding="utf-8"))
        raw_by_code = {
            str(item.get("case_id") or "").upper(): item
            for item in raw.get("scenarios", [])
            if isinstance(item, Mapping)
        }
        seeded_questions = 0
        seeded_answers = 0
        for question in questions:
            question_id = self._upsert_benchmark_question(question)
            seeded_questions += 1
            raw_item = raw_by_code[question.question_code]
            history = raw_item.get("historical_answer") if isinstance(raw_item.get("historical_answer"), Mapping) else {}
            inserted = self._seed_historical_answer(question_id, history, raw)
            seeded_answers += 1 if inserted else 0
        return seeded_questions, seeded_answers

    def _upsert_benchmark_question(self, question: QaQuestion) -> int:
        sql = f"""
            MERGE INTO {QUESTION_TABLE} target
            USING (SELECT :question_code AS question_code FROM dual) source
            ON (target.question_code = source.question_code)
            WHEN MATCHED THEN UPDATE SET
                question_source = 'CUSTOMER_EXCEL',
                question_hash = :question_hash,
                category = :category,
                title = :title,
                question_text = :question_text,
                source_document = :source_document,
                source_sheet = :source_sheet,
                source_row = :source_row,
                source_scenario = :source_scenario,
                sample_sql = :sample_sql,
                expected_focus = :expected_focus,
                baseline_sql = :baseline_sql,
                baseline_answer = :baseline_answer,
                support_level = :support_level,
                evaluation_rule_json = :evaluation_rule_json,
                active_yn = 'Y',
                updated_at = SYSTIMESTAMP
            WHEN NOT MATCHED THEN INSERT (
                question_code, question_source, question_hash, category, title,
                question_text, source_document, source_sheet, source_row,
                source_scenario, sample_sql, expected_focus, baseline_sql,
                baseline_answer, support_level, evaluation_rule_json, active_yn
            ) VALUES (
                :question_code, 'CUSTOMER_EXCEL', :question_hash, :category,
                :title, :question_text, :source_document, :source_sheet,
                :source_row, :source_scenario, :sample_sql, :expected_focus,
                :baseline_sql, :baseline_answer, :support_level,
                :evaluation_rule_json, 'Y'
            )
        """
        binds = {
            "question_code": question.question_code,
            "question_hash": question_fingerprint(question.question_text),
            "category": question.category[:30],
            "title": question.title[:200],
            "question_text": question.question_text,
            "source_document": question.source_document[:255] or None,
            "source_sheet": question.source_sheet[:255] or None,
            "source_row": question.source_row,
            "source_scenario": question.source_scenario,
            "sample_sql": question.sample_sql,
            "expected_focus": question.expected_focus,
            "baseline_sql": question.baseline_sql,
            "baseline_answer": question.baseline_answer,
            "support_level": question.support_level[:20],
            "evaluation_rule_json": json.dumps(question.evaluation_rule, ensure_ascii=False),
        }
        with self._connection() as connection:
            with connection.cursor() as cursor:
                cursor.execute(sql, binds)
                connection.commit()
                cursor.execute(
                    f"SELECT question_id FROM {QUESTION_TABLE} WHERE question_code = :question_code",
                    {"question_code": question.question_code},
                )
                row = cursor.fetchone()
        if not row:
            raise QaHistoryStoreError(f"질문 마스터를 적재하지 못했습니다: {question.question_code}")
        return int(row[0])

    def _seed_historical_answer(
        self,
        question_id: int,
        history: Mapping[str, Any],
        benchmark: Mapping[str, Any],
    ) -> bool:
        exists_sql = f"""
            SELECT COUNT(*) FROM {ANSWER_TABLE}
             WHERE question_id = :question_id AND run_key = :run_key
        """
        with self._connection() as connection:
            with connection.cursor() as cursor:
                cursor.execute(exists_sql, {"question_id": question_id, "run_key": HISTORICAL_RUN_KEY})
                if int(cursor.fetchone()[0]) > 0:
                    return False
        result = {
            "source_report": str(benchmark.get("source_report") or ""),
            "source_redmine": benchmark.get("source_redmine"),
            "historical_execution_output": str(history.get("execution_output") or ""),
        }
        self.record_answer(
            question_id=question_id,
            answer_kind="HISTORICAL",
            run_key=HISTORICAL_RUN_KEY,
            conversation_id="",
            requested_by="customer-excel-baseline",
            model_profile=str(history.get("profile") or ""),
            generated_sql=str(history.get("generated_sql") or ""),
            answer_text=str(history.get("answer_text") or ""),
            result=result,
            execution_output=str(history.get("execution_output") or ""),
            execution_status=str(history.get("execution_status") or ""),
            judgment_status=str(history.get("judgment_status") or "REVIEW"),
            judgment_reason=str(history.get("judgment_reason") or ""),
            duration_ms=int(history.get("duration_ms") or 0),
        )
        return True


def schema_statements() -> tuple[str, ...]:
    return (
        f"""
        CREATE TABLE {QUESTION_TABLE} (
            question_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
            question_code VARCHAR2(30) UNIQUE,
            question_source VARCHAR2(30) NOT NULL,
            question_hash VARCHAR2(64) NOT NULL UNIQUE,
            category VARCHAR2(30) NOT NULL,
            title VARCHAR2(200) NOT NULL,
            question_text CLOB NOT NULL,
            source_document VARCHAR2(255),
            source_sheet VARCHAR2(255),
            source_row NUMBER,
            source_scenario CLOB,
            sample_sql CLOB,
            expected_focus CLOB,
            baseline_sql CLOB,
            baseline_answer CLOB,
            support_level VARCHAR2(20) NOT NULL,
            evaluation_rule_json CLOB CHECK (evaluation_rule_json IS JSON),
            active_yn CHAR(1) DEFAULT 'Y' NOT NULL CHECK (active_yn IN ('Y', 'N')),
            created_at TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
            updated_at TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
            CONSTRAINT sg_ai_qa_question_source_ck
                CHECK (question_source IN ('CUSTOMER_EXCEL', 'FREE_TEXT'))
        )
        """,
        f"""
        CREATE TABLE {ANSWER_TABLE} (
            answer_seq NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
            question_id NUMBER NOT NULL,
            answer_kind VARCHAR2(20) NOT NULL,
            run_key VARCHAR2(100),
            conversation_id VARCHAR2(100),
            requested_by VARCHAR2(100),
            requested_at TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
            model_profile VARCHAR2(100),
            generated_sql CLOB,
            answer_text CLOB,
            result_json CLOB CHECK (result_json IS JSON),
            execution_output CLOB,
            execution_status VARCHAR2(40),
            judgment_status VARCHAR2(20) NOT NULL,
            judgment_reason CLOB,
            duration_ms NUMBER,
            created_at TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
            CONSTRAINT sg_ai_qa_answer_question_fk
                FOREIGN KEY (question_id)
                REFERENCES {QUESTION_TABLE} (question_id)
                ON DELETE CASCADE,
            CONSTRAINT sg_ai_qa_answer_kind_ck
                CHECK (answer_kind IN ('HISTORICAL', 'LIVE')),
            CONSTRAINT sg_ai_qa_answer_judgment_ck
                CHECK (judgment_status IN ('PASS', 'WARN', 'FAIL', 'REVIEW'))
        )
        """,
        f"""
        CREATE INDEX sg_ai_qa_answer_question_ix
            ON {ANSWER_TABLE} (question_id, answer_seq DESC)
        """,
        f"""
        CREATE UNIQUE INDEX sg_ai_qa_answer_run_uk
            ON {ANSWER_TABLE} (question_id, run_key)
        """,
    )


def ensure_schema(store: QaHistoryStore) -> None:
    objects = (QUESTION_TABLE, ANSWER_TABLE)
    with store._connection() as connection:
        with connection.cursor() as cursor:
            cursor.execute(
                "SELECT table_name FROM user_tables WHERE table_name IN (:q, :a)",
                {"q": objects[0], "a": objects[1]},
            )
            existing = {str(row[0]) for row in cursor}
            statements = schema_statements()
            if QUESTION_TABLE not in existing:
                cursor.execute(statements[0])
            if ANSWER_TABLE not in existing:
                cursor.execute(statements[1])
            cursor.execute(
                "SELECT index_name FROM user_indexes WHERE index_name IN (:ix1, :ix2)",
                {"ix1": "SG_AI_QA_ANSWER_QUESTION_IX", "ix2": "SG_AI_QA_ANSWER_RUN_UK"},
            )
            indexes = {str(row[0]) for row in cursor}
            if "SG_AI_QA_ANSWER_QUESTION_IX" not in indexes:
                cursor.execute(statements[2])
            if "SG_AI_QA_ANSWER_RUN_UK" not in indexes:
                cursor.execute(statements[3])
            connection.commit()


def timestamp_now() -> str:
    return datetime.now(timezone.utc).isoformat(timespec="seconds")
