"""候選審核 queue AI summary persistence transaction preview。

本模組只把 persistence preflight 轉成 metadata_json 更新 statement preview；
不開 DB connection、不執行 SQL、不讀 approval token、不執行 CLI、不寫檔、
不更新 review_state、不派送 Telegram、不呼叫 LLM、不 commit、不掛 scheduler。
"""


FORBIDDEN_TOKEN_KEYWORDS = (
    "approval_token",
    "approval-token",
    "market_intel_queue_write_approval",
)
SAFE_TOKEN_METADATA_KEYS = {
    "approval_token_present",
    "approval_token_valid",
    "approval_token_secret_configured",
}
SAFE_APPROVAL_ENV_VAR = "MARKET_INTEL_QUEUE_WRITE_APPROVAL"
TARGET_TABLE = "market_alert_review_queue"
TARGET_COLUMN = "metadata_json"
SUMMARY_METADATA_KEY = "ai_summary_review"


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


def _as_list(value):
    if value is None:
        return []
    if isinstance(value, (list, tuple, set)):
        return list(value)
    return [value]


def _safe_text(value, limit=300):
    if value is None:
        return None
    text = str(value).strip()
    return text[:limit] if text else None


def _contains_forbidden_token_key(value):
    if isinstance(value, dict):
        for key, nested in value.items():
            normalized_key = str(key).lower()
            if normalized_key in SAFE_TOKEN_METADATA_KEYS and isinstance(nested, bool):
                continue
            if normalized_key == "approval_env_var" and nested == SAFE_APPROVAL_ENV_VAR:
                continue
            if any(token_key in normalized_key for token_key in FORBIDDEN_TOKEN_KEYWORDS):
                return True
            if _contains_forbidden_token_key(nested):
                return True
    elif isinstance(value, list):
        return any(_contains_forbidden_token_key(item) for item in value)
    return False


def _side_effects_clear(*payloads):
    blocked_keys = (
        "api_executes_cli",
        "api_executes_llm",
        "api_reads_approval_token",
        "api_writes_file",
        "api_writes_database",
        "api_updates_review_state",
        "summary_persistence_preflight_file_written",
        "summary_persistence_transaction_file_written",
        "summary_file_written",
        "summary_record_written",
        "summary_persistence_record_written",
        "metadata_patch_written",
        "transaction_file_written",
        "database_connection_opened",
        "database_session_created",
        "explicit_transaction_opened",
        "transaction_opened",
        "transaction_committed",
        "database_write_executed",
        "database_commit_executed",
        "review_state_update_executed",
        "llm_call_executed",
        "ollama_call_executed",
        "gemini_call_executed",
        "telegram_dispatched",
        "external_network_executed",
        "scheduler_attached",
        "writes_executed",
        "would_write_database",
    )
    return all(
        not _as_dict(payload).get(key)
        for payload in payloads
        for key in blocked_keys
    )


def _operator_summary(operator_evidence):
    operator_evidence = _as_dict(operator_evidence)
    return {
        "provided_keys": sorted(operator_evidence.keys()),
        "operator_confirmed_ai_summary_persistence_transaction": bool(
            operator_evidence.get("operator_confirmed_ai_summary_persistence_transaction")
            or operator_evidence.get("operator_confirmed_summary_persistence_transaction")
        ),
        "operator_confirmed_metadata_merge_reviewed": bool(
            operator_evidence.get("operator_confirmed_metadata_merge_reviewed")
            or operator_evidence.get("operator_confirmed_metadata_json_merge_reviewed")
        ),
        "operator_confirmed_summary_persistence_is_cli_only": bool(
            operator_evidence.get("operator_confirmed_summary_persistence_is_cli_only")
            or operator_evidence.get("operator_confirmed_persistence_requires_cli")
        ),
        "operator_confirmed_no_api_db_write": bool(
            operator_evidence.get("operator_confirmed_no_api_db_write")
        ),
        "operator_confirmed_no_telegram_dispatch": bool(
            operator_evidence.get("operator_confirmed_no_telegram_dispatch")
        ),
        "operator_confirmed_no_scheduler_attach": bool(
            operator_evidence.get("operator_confirmed_no_scheduler_attach")
        ),
        "ai_summary_output_artifact_path": _safe_text(
            operator_evidence.get("ai_summary_output_artifact_path")
            or operator_evidence.get("summary_output_artifact_path")
        ),
        "ai_summary_persistence_preflight_artifact_path": _safe_text(
            operator_evidence.get("ai_summary_persistence_preflight_artifact_path")
            or operator_evidence.get("summary_persistence_preflight_artifact_path")
        ),
        "ai_summary_persistence_transaction_artifact_path": _safe_text(
            operator_evidence.get("ai_summary_persistence_transaction_artifact_path")
            or operator_evidence.get("summary_persistence_transaction_artifact_path")
        ),
        "approval_token_submitted_to_api": _contains_forbidden_token_key(
            operator_evidence
        ),
    }


def _preflight_payload(ai_summary_persistence_preflight):
    preflight = _as_dict(ai_summary_persistence_preflight)
    contract = _as_dict(preflight.get("summary_persistence_contract"))
    record = _as_dict(preflight.get("summary_record_preview"))
    patch = _as_dict(preflight.get("metadata_patch_preview"))
    return {
        "provided": bool(preflight),
        "mode": preflight.get("mode"),
        "summary_persistence_preflight_ready": bool(
            preflight.get("summary_persistence_preflight_ready")
        ),
        "ready_for_summary_transaction_preview": bool(
            preflight.get("ready_for_summary_transaction_preview")
        ),
        "expected_dedupe_keys": [
            str(key) for key in _as_list(preflight.get("expected_dedupe_keys"))
        ],
        "summary_payload_hash": _safe_text(preflight.get("summary_payload_hash"), 80),
        "summary_persistence_contract": contract,
        "summary_record_preview": record,
        "metadata_patch_preview": patch,
        "blocked_reasons": _as_list(preflight.get("blocked_reasons")),
        "safe_boundaries": _as_list(preflight.get("safe_boundaries")),
        "llm_call_executed": bool(preflight.get("llm_call_executed")),
        "telegram_dispatched": bool(preflight.get("telegram_dispatched")),
        "database_connection_opened": bool(preflight.get("database_connection_opened")),
        "database_write_executed": bool(preflight.get("database_write_executed")),
        "database_commit_executed": bool(preflight.get("database_commit_executed")),
        "review_state_update_executed": bool(preflight.get("review_state_update_executed")),
        "scheduler_attached": bool(preflight.get("scheduler_attached")),
    }


def _contract_ready(preflight):
    contract = preflight["summary_persistence_contract"]
    return bool(
        contract.get("target_table") == TARGET_TABLE
        and contract.get("target_column") == TARGET_COLUMN
        and _as_list(contract.get("target_json_path")) == [SUMMARY_METADATA_KEY]
        and contract.get("api_write_allowed") is False
        and preflight["expected_dedupe_keys"]
        and preflight["summary_payload_hash"]
    )


def _statement_previews(*, preflight, operator):
    statements = []
    for dedupe_key in preflight["expected_dedupe_keys"]:
        statements.append(
            {
                "statement_type": "UPDATE",
                "table": TARGET_TABLE,
                "target_column": TARGET_COLUMN,
                "operation": "metadata_json_merge_preview",
                "where": {"dedupe_key": dedupe_key},
                "sql_preview": (
                    "UPDATE market_alert_review_queue "
                    "SET metadata_json = :metadata_json, updated_at = CURRENT_TIMESTAMP "
                    "WHERE dedupe_key = :dedupe_key"
                ),
                "parameter_preview": {
                    "dedupe_key": dedupe_key,
                    "metadata_key": SUMMARY_METADATA_KEY,
                    "payload_hash": preflight["summary_payload_hash"],
                    "summary_output_artifact_path": operator[
                        "ai_summary_output_artifact_path"
                    ],
                    "preflight_artifact_path": operator[
                        "ai_summary_persistence_preflight_artifact_path"
                    ],
                },
                "requires_existing_metadata_merge": True,
                "execute_in_api": False,
            }
        )
    return statements


def _rollback_plan(statements):
    return [
        {
            "key": "backup_metadata_json_before_update",
            "label": "CLI 寫入前先輸出目標 dedupe_key 的原始 metadata_json 備份",
            "required": True,
        },
        {
            "key": "restore_metadata_json_by_dedupe_key",
            "label": "若寫入後 smoke 失敗，以備份檔逐筆還原 metadata_json",
            "required": True,
            "statement_count": len(statements),
        },
        {
            "key": "do_not_change_review_state",
            "label": "summary persistence 不得改動 review_state 或人工決策欄位",
            "required": True,
        },
    ]


def _transaction_gates(*, preflight, operator, statements):
    return [
        {
            "key": "summary_persistence_preflight_ready",
            "label": "summary persistence preflight 必須已通過",
            "passed": bool(
                preflight["provided"]
                and preflight["mode"]
                == "candidate_queue_review_ai_summary_persistence_preflight_preview"
                and preflight["summary_persistence_preflight_ready"]
                and preflight["ready_for_summary_transaction_preview"]
            ),
        },
        {
            "key": "summary_persistence_contract_ready",
            "label": "target table、metadata_json path 與 payload hash 必須齊備",
            "passed": _contract_ready(preflight),
        },
        {
            "key": "summary_persistence_update_statements_planned",
            "label": "每個 dedupe key 必須有一筆 UPDATE preview",
            "passed": bool(
                statements
                and len(statements) == len(preflight["expected_dedupe_keys"])
                and all(not item["execute_in_api"] for item in statements)
            ),
        },
        {
            "key": "operator_confirmed_summary_persistence_transaction",
            "label": "操作員確認本步只產生 transaction preview，正式寫入走 CLI-only gate",
            "passed": bool(
                operator["operator_confirmed_ai_summary_persistence_transaction"]
                and operator["operator_confirmed_metadata_merge_reviewed"]
                and operator["operator_confirmed_summary_persistence_is_cli_only"]
                and operator["operator_confirmed_no_api_db_write"]
                and operator["operator_confirmed_no_telegram_dispatch"]
                and operator["operator_confirmed_no_scheduler_attach"]
            ),
        },
        {
            "key": "summary_persistence_transaction_artifacts_recorded",
            "label": "output、preflight 與 transaction artifact path 必須由操作員提供",
            "passed": bool(
                operator["ai_summary_output_artifact_path"]
                and operator["ai_summary_persistence_preflight_artifact_path"]
                and operator["ai_summary_persistence_transaction_artifact_path"]
            ),
        },
        {
            "key": "summary_persistence_transaction_no_approval_token_submitted_to_api",
            "label": "payload 不得包含一次性 approval token key",
            "passed": not operator["approval_token_submitted_to_api"],
        },
        {
            "key": "summary_persistence_transaction_has_no_side_effects",
            "label": "transaction preview 不得開 DB、寫檔、呼叫 LLM、派 Telegram 或掛 scheduler",
            "passed": _side_effects_clear(preflight),
        },
    ]


def build_candidate_queue_review_ai_summary_persistence_transaction(
    *,
    ai_summary_persistence_preflight,
    operator_evidence=None,
    execute_requested=False,
):
    """建立 AI summary persistence transaction preview；不執行 SQL 或副作用。"""
    preflight = _preflight_payload(ai_summary_persistence_preflight)
    operator = _operator_summary(operator_evidence)
    statements = _statement_previews(preflight=preflight, operator=operator)
    gates = _transaction_gates(
        preflight=preflight,
        operator=operator,
        statements=statements,
    )
    blocked_reasons = [gate["key"] for gate in gates if not gate["passed"]]
    transaction_ready = bool(not blocked_reasons)

    return {
        "mode": "candidate_queue_review_ai_summary_persistence_transaction_preview",
        "target_table": TARGET_TABLE,
        "target_operation": "update_ai_summary_metadata_json",
        "execute_requested": bool(execute_requested),
        "summary_persistence_transaction_ready": transaction_ready,
        "ready_for_summary_persistence_writer_gate": transaction_ready,
        "ready_for_next_manual_phase": transaction_ready,
        "ready_for_summary_persistence_cli_run": False,
        "ready_for_api_database_write": False,
        "ready_for_telegram_dispatch": False,
        "ready_for_scheduler_attach": False,
        "api_executes_cli": False,
        "api_executes_llm": False,
        "api_reads_approval_token": False,
        "api_writes_file": False,
        "api_writes_database": False,
        "api_updates_review_state": False,
        "summary_persistence_transaction_file_written": False,
        "summary_file_written": False,
        "summary_record_written": False,
        "summary_persistence_record_written": False,
        "metadata_patch_written": False,
        "transaction_file_written": False,
        "ai_summary_generated": False,
        "llm_call_executed": False,
        "ollama_call_executed": False,
        "gemini_call_executed": False,
        "telegram_dispatched": False,
        "review_state_update_executed": False,
        "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,
        "writes_executed": False,
        "would_write_database": False,
        "expected_dedupe_keys": preflight["expected_dedupe_keys"],
        "summary_payload_hash": preflight["summary_payload_hash"],
        "statement_count": len(statements),
        "statements": statements,
        "transaction_summary": {
            "target_table": TARGET_TABLE,
            "target_column": TARGET_COLUMN,
            "statement_count": len(statements),
            "metadata_key": SUMMARY_METADATA_KEY,
            "payload_hash": preflight["summary_payload_hash"],
            "manual_cli_required": True,
            "api_write_allowed": False,
        },
        "rollback_plan": _rollback_plan(statements),
        "preflight_summary": {
            "mode": preflight["mode"],
            "summary_persistence_preflight_ready": preflight[
                "summary_persistence_preflight_ready"
            ],
            "ready_for_summary_transaction_preview": preflight[
                "ready_for_summary_transaction_preview"
            ],
        },
        "operator_ai_summary_persistence_transaction": operator,
        "blocked_reasons": blocked_reasons,
        "gates": gates,
        "next_operator_steps": [
            "人工確認 UPDATE preview 的 dedupe_key 與 payload hash",
            "後續若要正式寫入，必須另開 CLI writer gate 與 approval token 流程",
            "寫入前先備份每筆既有 metadata_json，寫入後再跑只讀 smoke",
            "Telegram dispatch 必須另開 dispatch gate，不得由 transaction preview 派送",
        ],
        "safe_boundaries": [
            "do_not_open_database_from_ai_summary_persistence_transaction",
            "do_not_write_metadata_json_from_ai_summary_persistence_transaction",
            "do_not_update_review_state_from_ai_summary_persistence_transaction",
            "do_not_write_summary_transaction_file_from_api",
            "do_not_read_approval_token_from_ai_summary_persistence_transaction",
            "do_not_execute_cli_from_ai_summary_persistence_transaction",
            "do_not_call_llm_from_ai_summary_persistence_transaction",
            "do_not_dispatch_telegram_from_ai_summary_persistence_transaction",
            "do_not_attach_scheduler_from_ai_summary_persistence_transaction",
            "future_summary_persistence_write_must_use_cli_only_gate",
            "metadata_json_merge_requires_backup_before_write",
            "ai_summary_persistence_transaction_preview_only",
            "no_remove_orphans",
            "no_momo_db_lifecycle_change",
        ],
    }
