"""候選審核 queue review_state writer post-write 只讀 smoke。

本模組只依 review_state transaction preview 的 dedupe key 查詢
market_alert_review_queue，確認人工 CLI 更新後 row 的 review_state 是否符合預期；
不建立 ORM session、不更新 review_state、不 commit、不掛 scheduler。
"""

from sqlalchemy import bindparam, create_engine, text

from services.market_intel.candidate_queue_review_decision_transaction import (
    ALLOWED_DECISIONS,
    TARGET_TABLE,
)


def _as_dict(value):
    return value if isinstance(value, dict) else {}


def _expected_updates_from_transaction(transaction_preview):
    updates = []
    for statement in _as_dict(transaction_preview).get("statements", []):
        statement = _as_dict(statement)
        parameter_preview = _as_dict(statement.get("parameter_preview"))
        lookup = _as_dict(statement.get("lookup"))
        dedupe_key = (
            parameter_preview.get("dedupe_key")
            or lookup.get("dedupe_key")
            or statement.get("dedupe_key")
        )
        expected_state = (
            parameter_preview.get("next_review_state")
            or statement.get("next_review_state")
        )
        expected_current_state = (
            parameter_preview.get("expected_current_review_state")
            or statement.get("expected_current_review_state")
        )
        updates.append(
            {
                "dedupe_key": str(dedupe_key) if dedupe_key else None,
                "expected_current_review_state": expected_current_state,
                "expected_review_state": expected_state,
                "idempotency_key": statement.get("idempotency_key"),
                "parameter_payload_hash": statement.get("parameter_payload_hash"),
            }
        )
    return updates


def _safe_row(row):
    payload = dict(row._mapping)
    for key, value in list(payload.items()):
        if value is not None and not isinstance(value, (bool, int, float, str)):
            payload[key] = str(value)
    return payload


def _base_payload(*, mode, execute_requested, expected_updates):
    dedupe_keys = sorted(
        {item["dedupe_key"] for item in expected_updates if item.get("dedupe_key")}
    )
    return {
        "mode": mode,
        "target_table": TARGET_TABLE,
        "target_operation": "verify_review_state_update",
        "execute_requested": bool(execute_requested),
        "read_only_query_executed": False,
        "database_connection_opened": False,
        "database_session_created": False,
        "explicit_transaction_opened": False,
        "transaction_opened": False,
        "transaction_committed": False,
        "database_write_executed": False,
        "database_commit_executed": False,
        "database_rollback_executed": False,
        "external_network_executed": False,
        "scheduler_attached": False,
        "review_state_update_executed": False,
        "api_updates_review_state": False,
        "ready_for_api_review_state_update": False,
        "ready_for_api_database_write": False,
        "ready_for_scheduler_attach": False,
        "writes_executed": False,
        "would_write_database": False,
        "expected_update_count": len(expected_updates),
        "expected_dedupe_key_count": len(dedupe_keys),
        "dedupe_keys": dedupe_keys,
        "expected_review_state_updates": expected_updates,
        "allowed_review_states": list(ALLOWED_DECISIONS),
        "found_count": 0,
        "missing_count": len(dedupe_keys),
        "state_mismatch_count": 0,
        "found_dedupe_keys": [],
        "missing_dedupe_keys": dedupe_keys,
        "state_mismatches": [],
        "row_summaries": [],
        "postwrite_smoke_passed": False,
        "review_state_update_verified": False,
        "ready_for_operator_review": False,
        "safety_contract": _safety_contract(),
        "safe_boundaries": [
            "do_not_execute_review_state_writer_from_smoke_api",
            "do_not_update_review_state_from_postwrite_smoke",
            "do_not_read_approval_token_from_postwrite_smoke",
            "do_not_commit_review_state_postwrite_smoke",
            "do_not_attach_scheduler_from_review_state_postwrite_smoke",
            "no_remove_orphans",
            "no_momo_db_lifecycle_change",
        ],
    }


def _safety_contract():
    return {
        "target_table": TARGET_TABLE,
        "target_operation": "verify_review_state_update",
        "read_only_query_only": True,
        "does_not_read_approval_token": True,
        "does_not_open_transaction": True,
        "does_not_update_review_state": True,
        "does_not_commit": True,
        "does_not_attach_scheduler": True,
        "allowed_review_states": list(ALLOWED_DECISIONS),
    }


def _planned_result(expected_updates):
    payload = _base_payload(
        mode="candidate_queue_review_decision_writer_postwrite_smoke_planned",
        execute_requested=False,
        expected_updates=expected_updates,
    )
    payload["blocked_reasons"] = [
        "execute_false_planned_only",
        "review_decision_writer_postwrite_smoke_not_loaded",
    ]
    if not payload["dedupe_keys"]:
        payload["blocked_reasons"].append("transaction_statement_dedupe_keys_missing")
    return payload


def build_candidate_queue_review_decision_writer_postwrite_smoke(
    *,
    transaction_preview,
    execute_requested=False,
    database_url=None,
    database_type=None,
    engine=None,
):
    """建立 review_state writer post-write smoke；execute=true 仍只讀查詢。"""
    expected_updates = _expected_updates_from_transaction(transaction_preview)
    if not execute_requested:
        return _planned_result(expected_updates)

    from config import DATABASE_PATH, DATABASE_TYPE

    effective_database_type = (database_type or DATABASE_TYPE or "").lower()
    effective_database_url = database_url or DATABASE_PATH
    created_engine = False
    connection_opened = False
    payload = _base_payload(
        mode="candidate_queue_review_decision_writer_postwrite_smoke_read_only",
        execute_requested=True,
        expected_updates=expected_updates,
    )

    try:
        if engine is None:
            connect_args = {}
            if effective_database_type == "postgresql":
                connect_args = {
                    "connect_timeout": 8,
                    "options": "-c statement_timeout=15000",
                }
            engine = create_engine(
                effective_database_url,
                isolation_level="AUTOCOMMIT",
                pool_pre_ping=True,
                connect_args=connect_args,
            )
            created_engine = True

        rows = []
        if payload["dedupe_keys"]:
            query = text(
                """
                SELECT
                    id,
                    alert_candidate_id,
                    review_state,
                    priority_lane,
                    threshold_level,
                    total_score,
                    evidence_bundle_id,
                    dedupe_key,
                    source_batch_id,
                    reviewed_at,
                    created_at,
                    updated_at
                FROM market_alert_review_queue
                WHERE dedupe_key IN :dedupe_keys
                ORDER BY dedupe_key
                """
            ).bindparams(bindparam("dedupe_keys", expanding=True))
            with engine.connect() as conn:
                connection_opened = True
                rows = conn.execute(
                    query,
                    {"dedupe_keys": tuple(payload["dedupe_keys"])},
                ).fetchall()
        else:
            with engine.connect():
                connection_opened = True

        row_summaries = [_safe_row(row) for row in rows]
        found_by_key = {row["dedupe_key"]: row for row in row_summaries}
        expected_by_key = {
            item["dedupe_key"]: item
            for item in expected_updates
            if item.get("dedupe_key")
        }
        found_keys = sorted(found_by_key)
        missing_keys = [
            key for key in payload["dedupe_keys"] if key not in found_by_key
        ]
        state_mismatches = []
        for dedupe_key, expected in expected_by_key.items():
            row = found_by_key.get(dedupe_key)
            if not row:
                continue
            actual_state = row.get("review_state")
            expected_state = expected.get("expected_review_state")
            if actual_state != expected_state:
                state_mismatches.append(
                    {
                        "dedupe_key": dedupe_key,
                        "expected_review_state": expected_state,
                        "actual_review_state": actual_state,
                    }
                )

        passed = bool(payload["dedupe_keys"] and not missing_keys and not state_mismatches)
        blocked_reasons = []
        if not payload["dedupe_keys"]:
            blocked_reasons.append("transaction_statement_dedupe_keys_missing")
        if missing_keys:
            blocked_reasons.append("queue_rows_missing_after_review_state_write")
        if state_mismatches:
            blocked_reasons.append("review_state_mismatch_after_write")

        payload.update(
            {
                "read_only_query_executed": True,
                "database_connection_opened": connection_opened,
                "found_count": len(found_keys),
                "missing_count": len(missing_keys),
                "state_mismatch_count": len(state_mismatches),
                "found_dedupe_keys": found_keys,
                "missing_dedupe_keys": missing_keys,
                "state_mismatches": state_mismatches,
                "row_summaries": row_summaries,
                "postwrite_smoke_passed": passed,
                "review_state_update_verified": passed,
                "ready_for_operator_review": passed,
                "blocked_reasons": blocked_reasons,
            }
        )
        return payload
    except Exception as exc:
        payload.update(
            {
                "mode": "candidate_queue_review_decision_writer_postwrite_smoke_error",
                "database_connection_opened": connection_opened,
                "blocked_reasons": [
                    "review_decision_writer_postwrite_smoke_error"
                ],
                "error_message": str(exc),
            }
        )
        return payload
    finally:
        if created_engine:
            engine.dispose()
