"""市場情報 migration 套用前演練。

本模組只組裝正式 migration 前的檢查、人工步驟與回滾演練，不執行 SQL、
不建立 ORM session、不寫入資料庫。
"""


def _schema_state(schema_db_probe):
    mode = schema_db_probe.get("mode")
    if mode == "schema_db_probe_planned":
        return "planned_no_db_probe"
    if mode == "schema_db_probe_error":
        return "probe_error"
    if not schema_db_probe.get("read_only_query_executed"):
        return "planned_no_db_probe"
    if schema_db_probe.get("schema_tables_exist"):
        return "already_applied"
    existing_tables = schema_db_probe.get("existing_tables") or []
    if existing_tables:
        return "partial_schema"
    return "not_applied"


def _check_item(key, label, passed, status_when_blocked="blocked"):
    return {
        "key": key,
        "label": label,
        "passed": bool(passed),
        "status": "pass" if passed else status_when_blocked,
    }


def _manual_step(key, label, status="required"):
    return {
        "key": key,
        "label": label,
        "status": status,
    }


def _build_blocked_reasons(*, checks, schema_state, execute_requested):
    blocked_reasons = [
        "migration_not_executed_by_drill",
        "api_never_runs_migration",
        "backup_not_verified",
        "operator_approval_missing",
        "production_maintenance_window_required",
    ]
    blocked_reasons.extend(
        key for key, passed in checks.items()
        if not passed
    )
    if not execute_requested:
        blocked_reasons.append("read_only_db_probe_not_executed")
    if schema_state == "probe_error":
        blocked_reasons.append("schema_probe_error_requires_manual_review")
    if schema_state == "partial_schema":
        blocked_reasons.append("partial_market_schema_requires_manual_review")
    if schema_state == "already_applied":
        blocked_reasons.append("market_schema_already_present_apply_not_required")
    return blocked_reasons


def build_migration_apply_drill_preview(
    *,
    runtime_status,
    migration_blueprint,
    schema_db_probe,
    platform_seed_db_diff,
):
    """建立 migration apply drill payload；不執行 migration 或 DB write。"""
    execute_requested = bool(
        schema_db_probe.get("execute_requested")
        or platform_seed_db_diff.get("execute_requested")
    )
    schema_state = _schema_state(schema_db_probe)
    checks = {
        "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")
        ),
        "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("migration_executed")
            and not schema_db_probe.get("database_session_created")
        ),
        "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("migration_executed")
            and not platform_seed_db_diff.get("database_session_created")
            and not platform_seed_db_diff.get("seed_write_executed")
        ),
        "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),
        "external_fetch_blocked": bool(not runtime_status.crawler_enabled),
        "rollback_manual_only": bool(
            migration_blueprint.get("rollback_requires_manual_approval")
        ),
    }
    drill_ready_for_operator_review = all(checks.values())
    ready_for_manual_apply_review = bool(
        drill_ready_for_operator_review
        and execute_requested
        and schema_state in {"not_applied", "partial_schema"}
    )
    ready_to_apply_migration = False

    pre_apply_checklist = [
        _manual_step(
            "confirm_worktree_and_release_scope",
            "確認本次只包含 market_intel migration / drill 相關變更",
        ),
        _manual_step(
            "run_backup_system",
            "套正式 migration 前先完成 python backup_system.py 並確認備份可用",
        ),
        _manual_step(
            "run_schema_probe_execute_true",
            "人工呼叫 /api/market_intel/schema_db_probe?execute=true 做只讀 catalog 檢查",
        ),
        _manual_step(
            "run_seed_diff_execute_true",
            "人工呼叫 /api/market_intel/platform_seed_db_diff?execute=true 做只讀 seed diff",
        ),
        _manual_step(
            "review_forward_sql",
            "確認 forward SQL 只有 CREATE TABLE / CREATE INDEX / GRANT 類 additive 操作",
        ),
        _manual_step(
            "open_maintenance_window",
            "確認正式 DB migration 維護窗口與操作員身分",
        ),
        _manual_step(
            "apply_psql_manually",
            "由操作員手動執行 migration command；API 不執行 psql",
        ),
        _manual_step(
            "post_apply_smoke",
            "套用後驗證 /health、schema_db_probe?execute=true、deployment_readiness",
        ),
    ]
    post_apply_verification = [
        _manual_step("health_endpoint", "驗證 /health healthy 且版本正確"),
        _manual_step(
            "schema_probe_all_tables",
            "確認 schema_db_probe?execute=true 顯示所有 market_* 表存在",
        ),
        _manual_step(
            "seed_writer_cli_status",
            "確認 seed writer CLI status 仍未自動寫入，等待下一次獨立批准",
        ),
        _manual_step(
            "flags_still_off",
            "確認 MARKET_INTEL_* flags 仍全關，crawler 與 scheduler 未啟用",
        ),
    ]
    rollback_drill = {
        "mode": "rollback_drill_preview",
        "rollback_sql_available": bool(migration_blueprint.get("rollback_sql")),
        "rollback_executed": False,
        "database_write_executed": False,
        "database_commit_executed": False,
        "requires_manual_approval": True,
        "requires_backup_before_rollback": True,
        "requires_data_loss_review": True,
        "rollback_table_order": list(reversed(migration_blueprint.get("expected_tables") or [])),
        "manual_command_shape": (
            "將已審核 rollback SQL 寫入臨時檔後，手動執行 "
            "psql \"$DATABASE_URL\" -v ON_ERROR_STOP=1 -f <reviewed_rollback.sql>"
        ),
        "fallback_first": [
            "先關閉 MARKET_INTEL_* feature flags",
            "確認 scheduler 未掛載市場情報 job",
            "app-only 回退上一版並只 recreate momo-app",
            "只有在確認 market_* 無需保留資料時才評估 rollback SQL",
        ],
    }
    risk_register = [
        {
            "key": "partial_schema",
            "label": "若只建立部分 market_* 表，必須人工比對 migration 檔與 catalog，再決定重跑或補修。",
            "severity": "medium",
        },
        {
            "key": "grant_or_sequence_missing",
            "label": "表存在不代表 GRANT / sequence 權限完整；post-apply smoke 需檢查 seed writer 仍可用。",
            "severity": "medium",
        },
        {
            "key": "rollback_drops_market_data",
            "label": "rollback SQL 會 drop market_* 表，未來有正式資料後不可當一般回退手段。",
            "severity": "high",
        },
        {
            "key": "api_boundary",
            "label": "API 只呈現 drill，不得替操作員執行 psql、docker、SSH 或 DB write。",
            "severity": "high",
        },
    ]

    return {
        "mode": "migration_apply_drill_preview",
        "execute_requested": execute_requested,
        "schema_state": schema_state,
        "drill_ready_for_operator_review": drill_ready_for_operator_review,
        "ready_for_manual_apply_review": ready_for_manual_apply_review,
        "ready_to_apply_migration": ready_to_apply_migration,
        "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,
        "api_executes_docker": False,
        "api_executes_ssh": False,
        "checks": checks,
        "check_items": [
            _check_item(key, key, passed)
            for key, passed in checks.items()
        ],
        "blocked_reasons": _build_blocked_reasons(
            checks=checks,
            schema_state=schema_state,
            execute_requested=execute_requested,
        ),
        "pre_apply_checklist": pre_apply_checklist,
        "post_apply_verification": post_apply_verification,
        "rollback_drill": rollback_drill,
        "risk_register": risk_register,
        "manual_commands": {
            "schema_probe": "/api/market_intel/schema_db_probe?execute=true",
            "platform_seed_db_diff": "/api/market_intel/platform_seed_db_diff?execute=true",
            "migration_apply": migration_blueprint.get("command_plan", {})
            .get("migration_apply_command", {})
            .get("command", ""),
        },
        "migration_blueprint_summary": {
            "suggested_filename": migration_blueprint.get("suggested_filename"),
            "file_created": bool(migration_blueprint.get("file_created")),
            "file_matches_blueprint": bool(
                migration_blueprint.get("file_matches_blueprint")
            ),
            "table_count": migration_blueprint.get("table_count", 0),
            "forward_statement_count": migration_blueprint.get(
                "forward_statement_count",
                0,
            ),
            "rollback_statement_count": migration_blueprint.get(
                "rollback_statement_count",
                0,
            ),
        },
        "schema_db_probe_summary": {
            "mode": schema_db_probe.get("mode"),
            "read_only_query_executed": bool(
                schema_db_probe.get("read_only_query_executed")
            ),
            "schema_tables_exist": bool(schema_db_probe.get("schema_tables_exist")),
            "missing_tables": schema_db_probe.get("missing_tables") or [],
            "existing_tables": schema_db_probe.get("existing_tables") or [],
        },
        "platform_seed_db_diff_summary": {
            "mode": platform_seed_db_diff.get("mode"),
            "read_only_query_executed": bool(
                platform_seed_db_diff.get("read_only_query_executed")
            ),
            "seed_rows_ready": bool(platform_seed_db_diff.get("seed_rows_ready")),
            "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 [],
        },
    }