"""市場情報正式 DB catalog 判讀。

本模組只整合 schema_db_probe 與 platform_seed_db_diff 的只讀結果，產生
operator 可讀的風險判定；不執行 migration、不寫 DB、不建立 ORM session。
"""


def _catalog_state(schema_db_probe):
    mode = schema_db_probe.get("mode")
    if mode == "schema_db_probe_planned":
        return "planned_no_probe"
    if mode == "schema_db_probe_error":
        return "probe_error"
    if not schema_db_probe.get("read_only_query_executed"):
        return "planned_no_probe"
    if schema_db_probe.get("schema_tables_exist"):
        return "already_applied"
    if schema_db_probe.get("existing_tables"):
        return "partial_schema"
    return "not_applied"


def _risk_for_state(catalog_state, seed_state):
    if catalog_state == "probe_error":
        return "high"
    if catalog_state == "partial_schema":
        return "high"
    if seed_state == "differs":
        return "medium"
    if catalog_state == "not_applied":
        return "low"
    if catalog_state == "already_applied":
        return "low"
    return "info"


def _apply_path_for_state(catalog_state, *, execute_requested):
    if catalog_state == "planned_no_probe":
        return "run_execute_true_read_only_probe_first"
    if catalog_state == "probe_error":
        return "blocked_until_probe_error_reviewed"
    if catalog_state == "partial_schema":
        return "blocked_until_partial_schema_reviewed"
    if catalog_state == "already_applied":
        return "skip_apply_verify_post_apply_state"
    if catalog_state == "not_applied" and execute_requested:
        return "manual_apply_review_candidate"
    return "run_execute_true_read_only_probe_first"


def _seed_state(platform_seed_db_diff):
    mode = platform_seed_db_diff.get("mode")
    if mode == "platform_seed_db_diff_planned":
        return "planned_no_probe"
    if mode == "platform_seed_db_diff_error":
        return "probe_error"
    if platform_seed_db_diff.get("changed_codes"):
        return "differs"
    if platform_seed_db_diff.get("missing_codes"):
        return "missing"
    if platform_seed_db_diff.get("seed_rows_ready"):
        return "ready"
    return "unknown"


def _finding(key, severity, label, status):
    return {
        "key": key,
        "severity": severity,
        "label": label,
        "status": status,
    }


def _build_findings(*, catalog_state, schema_db_probe, seed_state, platform_seed_db_diff):
    findings = []
    missing_tables = schema_db_probe.get("missing_tables") or []
    existing_tables = schema_db_probe.get("existing_tables") or []
    if catalog_state == "planned_no_probe":
        findings.append(
            _finding(
                "catalog_probe_not_executed",
                "info",
                "尚未執行正式 DB catalog 只讀探測，維持 planned 狀態。",
                "planned",
            )
        )
    elif catalog_state == "not_applied":
        findings.append(
            _finding(
                "market_schema_not_applied",
                "low",
                "正式 DB 尚未建立 market_* schema，可進入人工 migration review。",
                "review",
            )
        )
    elif catalog_state == "partial_schema":
        findings.append(
            _finding(
                "partial_market_schema_detected",
                "high",
                f"正式 DB 只存在 {len(existing_tables)} 張 market_* 表，缺少 {len(missing_tables)} 張。",
                "blocked",
            )
        )
    elif catalog_state == "already_applied":
        findings.append(
            _finding(
                "market_schema_already_applied",
                "low",
                "正式 DB 已存在所有 market_* 表，migration apply 可能不需要重跑。",
                "verify",
            )
        )
    elif catalog_state == "probe_error":
        findings.append(
            _finding(
                "catalog_probe_error",
                "high",
                "正式 DB catalog 只讀探測失敗，需人工檢查連線與權限。",
                "blocked",
            )
        )

    if seed_state == "probe_error":
        findings.append(
            _finding(
                "seed_diff_probe_error",
                "medium",
                "platform seed DB diff 只讀探測失敗；若 market_platforms 尚未建立，需等 migration 後重跑。",
                "review",
            )
        )
    elif seed_state == "planned_no_probe":
        findings.append(
            _finding(
                "seed_diff_not_executed",
                "info",
                "尚未執行 platform seed DB diff，只能停留在 preview 判讀。",
                "planned",
            )
        )
    elif seed_state == "missing":
        findings.append(
            _finding(
                "platform_seed_missing",
                "low",
                f"platform seed 尚缺 {len(platform_seed_db_diff.get('missing_codes') or [])} 筆，需等 migration 後獨立批准 seed writer。",
                "review",
            )
        )
    elif seed_state == "differs":
        findings.append(
            _finding(
                "platform_seed_differs",
                "medium",
                f"platform seed 有 {len(platform_seed_db_diff.get('changed_codes') or [])} 筆與預期不同，需人工比對。",
                "review",
            )
        )
    elif seed_state == "ready":
        findings.append(
            _finding(
                "platform_seed_ready",
                "low",
                "platform seed 與目前 DB 內容一致。",
                "verify",
            )
        )
    return findings


def build_migration_catalog_review_preview(
    *,
    runtime_status,
    migration_blueprint,
    schema_db_probe,
    platform_seed_db_diff,
):
    """建立正式 DB catalog 判讀 payload；不執行 migration 或 DB write。"""
    execute_requested = bool(
        schema_db_probe.get("execute_requested")
        or platform_seed_db_diff.get("execute_requested")
    )
    catalog_state = _catalog_state(schema_db_probe)
    seed_state = _seed_state(platform_seed_db_diff)
    risk_level = _risk_for_state(catalog_state, seed_state)
    apply_path = _apply_path_for_state(catalog_state, execute_requested=execute_requested)
    safe_checks = {
        "schema_probe_read_only_or_planned": bool(
            not schema_db_probe.get("database_write_executed")
            and not schema_db_probe.get("database_commit_executed")
            and not schema_db_probe.get("database_session_created")
            and not schema_db_probe.get("migration_executed")
        ),
        "seed_diff_read_only_or_planned": bool(
            not platform_seed_db_diff.get("database_write_executed")
            and not platform_seed_db_diff.get("database_commit_executed")
            and not platform_seed_db_diff.get("database_session_created")
            and not platform_seed_db_diff.get("seed_write_executed")
            and not platform_seed_db_diff.get("migration_executed")
        ),
        "migration_file_matches_blueprint": bool(
            migration_blueprint.get("file_created")
            and migration_blueprint.get("file_matches_blueprint")
        ),
        "forward_sql_additive_only": bool(
            migration_blueprint.get("safety_checks", {}).get("forward_sql_additive_only")
            and not migration_blueprint.get("forward_has_destructive_sql")
        ),
        "feature_flags_default_safe": bool(
            not runtime_status.enabled
            and not runtime_status.crawler_enabled
            and not runtime_status.write_enabled
        ),
        "database_write_blocked": bool(not runtime_status.database_write_allowed),
        "scheduler_detached": bool(not runtime_status.scheduler_attached),
    }
    review_ready = all(safe_checks.values())
    ready_for_manual_migration_review = bool(
        review_ready
        and execute_requested
        and catalog_state == "not_applied"
    )
    blocked_reasons = [
        "migration_not_executed_by_catalog_review",
        "api_never_runs_migration",
        "database_write_still_blocked",
    ]
    blocked_reasons.extend(
        key for key, passed in safe_checks.items()
        if not passed
    )
    if not execute_requested:
        blocked_reasons.append("execute_false_planned_only")
    if catalog_state == "probe_error":
        blocked_reasons.append("catalog_probe_error")
    if catalog_state == "partial_schema":
        blocked_reasons.append("partial_schema_requires_manual_reconciliation")
    if catalog_state == "already_applied":
        blocked_reasons.append("market_schema_already_present")

    expected_tables = schema_db_probe.get("expected_tables") or []
    existing_tables = schema_db_probe.get("existing_tables") or []
    missing_tables = schema_db_probe.get("missing_tables") or expected_tables
    table_statuses = schema_db_probe.get("table_statuses") or [
        {"table": table_name, "exists": False}
        for table_name in expected_tables
    ]

    return {
        "mode": "migration_catalog_review_preview",
        "execute_requested": execute_requested,
        "catalog_state": catalog_state,
        "seed_state": seed_state,
        "risk_level": risk_level,
        "apply_path": apply_path,
        "review_ready": review_ready,
        "ready_for_manual_migration_review": ready_for_manual_migration_review,
        "ready_to_apply_migration": False,
        "migration_executed": False,
        "rollback_executed": False,
        "database_connection_opened": bool(
            schema_db_probe.get("database_connection_opened")
            or platform_seed_db_diff.get("database_connection_opened")
        ),
        "read_only_query_executed": bool(
            schema_db_probe.get("read_only_query_executed")
            or platform_seed_db_diff.get("read_only_query_executed")
        ),
        "database_session_created": False,
        "explicit_transaction_opened": False,
        "database_write_executed": False,
        "database_commit_executed": False,
        "external_network_executed": False,
        "scheduler_attached": False,
        "api_executes_migration": False,
        "api_executes_rollback": False,
        "safe_checks": safe_checks,
        "blocked_reasons": blocked_reasons,
        "findings": _build_findings(
            catalog_state=catalog_state,
            schema_db_probe=schema_db_probe,
            seed_state=seed_state,
            platform_seed_db_diff=platform_seed_db_diff,
        ),
        "table_catalog": {
            "expected_count": len(expected_tables),
            "existing_count": len(existing_tables),
            "missing_count": len(missing_tables),
            "expected_tables": expected_tables,
            "existing_tables": existing_tables,
            "missing_tables": missing_tables,
            "table_statuses": table_statuses,
        },
        "seed_catalog": {
            "expected_seed_count": platform_seed_db_diff.get("expected_seed_count", 0),
            "existing_seed_count": platform_seed_db_diff.get("existing_seed_count", 0),
            "missing_codes": platform_seed_db_diff.get("missing_codes") or [],
            "changed_codes": platform_seed_db_diff.get("changed_codes") or [],
            "matching_codes": platform_seed_db_diff.get("matching_codes") or [],
            "seed_rows_ready": bool(platform_seed_db_diff.get("seed_rows_ready")),
        },
        "operator_next_steps": [
            {
                "key": "run_catalog_review_execute_true",
                "label": "人工 smoke 時呼叫 /api/market_intel/migration_catalog_review?execute=true，只讀判讀正式 DB 狀態",
                "status": "required" if not execute_requested else "completed",
            },
            {
                "key": "review_catalog_state",
                "label": "依 catalog_state 決定是否是未套用、部分套用、已套用或探測錯誤",
                "status": "required",
            },
            {
                "key": "manual_migration_apply_window",
                "label": "只有 catalog_state=not_applied 且人工批准後，才可在維護窗口手動執行 psql migration",
                "status": "blocked_by_operator_approval",
            },
            {
                "key": "post_apply_read_only_probe",
                "label": "套用後重跑 execute=true，只確認所有 market_* 表存在，不自動寫 seed",
                "status": "required_after_manual_apply",
            },
        ],
        "manual_probe_targets": [
            "/api/market_intel/schema_db_probe?execute=true",
            "/api/market_intel/platform_seed_db_diff?execute=true",
            "/api/market_intel/migration_catalog_review?execute=true",
        ],
    }
