use std::fs;
#[cfg(unix)]
use std::os::unix::fs::MetadataExt;
use std::path::{Path, PathBuf};
use std::time::UNIX_EPOCH;

use anyhow::Result;
use rusqlite::{Connection, OpenFlags};
use serde::Serialize;

use crate::db::schema;
use crate::paths::state::StateLayout;
use crate::state::session as session_state;

const AGENT_READINESS_SCHEMA_VERSION: u32 = 1;

#[derive(Serialize)]
pub(crate) struct AgentDiagnostics {
    pub(crate) package_version: &'static str,
    pub(crate) binary: BinaryDiagnostics,
    pub(crate) state_db: StateDbDiagnostics,
}

#[derive(Serialize)]
pub(crate) struct BinaryDiagnostics {
    pub(crate) running_binary_path: String,
    pub(crate) invoked_binary_path: Option<String>,
    pub(crate) running_binary: BinaryMetadata,
    pub(crate) on_disk_binary: BinaryMetadata,
    pub(crate) status: BinaryStatus,
    pub(crate) likely_host_process_drift: bool,
    pub(crate) restart_hint: Option<String>,
}

#[derive(Clone, Copy, Debug, Eq, PartialEq, Serialize)]
#[serde(rename_all = "snake_case")]
pub(crate) enum BinaryStatus {
    MatchesOnDisk,
    DiffersFromOnDisk,
    OnDiskUnavailable,
}

#[derive(Serialize)]
pub(crate) struct BinaryMetadata {
    path: String,
    exists: bool,
    size_bytes: Option<u64>,
    modified_epoch_s: Option<u64>,
    #[cfg(unix)]
    inode: Option<u64>,
    #[cfg(unix)]
    device: Option<u64>,
    error: Option<String>,
}

#[derive(Serialize)]
pub(crate) struct StateDbDiagnostics {
    pub(crate) path: String,
    pub(crate) supported_schema_version: u32,
    pub(crate) current_schema_version: Option<u32>,
    pub(crate) status: StateDbStatus,
    pub(crate) likely_host_process_drift: bool,
    pub(crate) message: String,
    pub(crate) restart_hint: Option<String>,
}

#[derive(Clone, Copy, Debug, Eq, PartialEq, Serialize)]
#[serde(rename_all = "snake_case")]
pub(crate) enum StateDbStatus {
    Unavailable,
    Missing,
    NewerThanSupported,
    Compatible,
    Unreadable,
}

#[derive(Clone, Copy, Debug, Eq, PartialEq, Serialize)]
#[serde(rename_all = "snake_case")]
pub(crate) enum AgentReadinessStatus {
    Ready,
    Warning,
    Blocked,
}

#[derive(Clone, Copy, Debug, Eq, PartialEq, Serialize)]
#[serde(rename_all = "snake_case")]
pub(crate) enum AgentReadinessNextAction {
    Proceed,
    RepairMarker,
    AttachWorkspace,
    LinkWorkspace,
    RefreshSession,
    RestartHostProcess,
    InstallSkills,
    ResolveBlockers,
    ReviewWarnings,
}

#[derive(Clone, Copy, Debug, Eq, PartialEq, Serialize)]
#[serde(rename_all = "snake_case")]
pub(crate) enum MarkerSchemaStatus {
    Valid,
    Missing,
    Invalid,
}

#[derive(Clone, Copy, Debug, Eq, PartialEq, Serialize)]
#[serde(rename_all = "snake_case")]
pub(crate) enum FindingSeverity {
    Info,
    Warning,
    Error,
}

#[derive(Clone, Debug, Serialize)]
pub(crate) struct AgentReadinessFinding {
    pub(crate) check: String,
    pub(crate) severity: FindingSeverity,
    pub(crate) message: String,
    #[serde(skip_serializing_if = "Option::is_none")]
    pub(crate) path: Option<String>,
}

impl AgentReadinessFinding {
    pub(crate) fn new(
        check: impl Into<String>,
        severity: FindingSeverity,
        message: impl Into<String>,
        path: Option<String>,
    ) -> Self {
        Self {
            check: check.into(),
            severity: severity.into(),
            message: message.into(),
            path,
        }
    }
}

#[derive(Serialize)]
pub(crate) struct AgentReadinessSummary {
    pub(crate) schema_version: u32,
    pub(crate) status: AgentReadinessStatus,
    pub(crate) next_action: AgentReadinessNextAction,
    #[serde(skip_serializing_if = "Option::is_none")]
    pub(crate) recommended_command: Option<AgentRecommendedCommand>,
    pub(crate) marker_schema_status: MarkerSchemaStatus,
    pub(crate) state_db: AgentStateDbReadiness,
    pub(crate) session: AgentSessionReadiness,
    pub(crate) heartbeat: AgentHeartbeatReadiness,
    pub(crate) mcp_cli_diagnostics: AgentMcpCliReadiness,
    pub(crate) blocking_failures: Vec<AgentReadinessFinding>,
    pub(crate) non_blocking_warnings: Vec<AgentReadinessFinding>,
}

#[derive(Serialize)]
pub(crate) struct AgentRecommendedCommand {
    pub(crate) program: &'static str,
    pub(crate) args: Vec<String>,
    pub(crate) display: String,
}

#[derive(Serialize)]
pub(crate) struct AgentStateDbReadiness {
    pub(crate) path: String,
    pub(crate) status: StateDbStatus,
    pub(crate) supported_schema_version: u32,
    pub(crate) current_schema_version: Option<u32>,
    pub(crate) likely_host_process_drift: bool,
}

#[derive(Clone, Copy, Debug, Eq, PartialEq, Serialize)]
#[serde(rename_all = "snake_case")]
pub(crate) enum AgentSessionStatus {
    Active,
    Stale,
    Missing,
    Unavailable,
    Unreadable,
}

#[derive(Serialize)]
pub(crate) struct AgentSessionReadiness {
    pub(crate) path: String,
    pub(crate) status: AgentSessionStatus,
    #[serde(skip_serializing_if = "Option::is_none")]
    pub(crate) lifecycle: Option<session_state::SessionLifecycle>,
    #[serde(skip_serializing_if = "Option::is_none")]
    pub(crate) owner_kind: Option<session_state::SessionOwnerKind>,
    #[serde(skip_serializing_if = "Option::is_none")]
    pub(crate) actor_id: Option<String>,
    #[serde(skip_serializing_if = "Option::is_none")]
    pub(crate) lease_expires_at_epoch_s: Option<u64>,
    #[serde(skip_serializing_if = "Option::is_none")]
    pub(crate) stale: Option<bool>,
    #[serde(skip_serializing_if = "Option::is_none")]
    pub(crate) message: Option<String>,
}

#[derive(Clone, Copy, Debug, Eq, PartialEq, Serialize)]
#[serde(rename_all = "snake_case")]
pub(crate) enum AgentHeartbeatStatus {
    Fresh,
    Stale,
    Missing,
    NotApplicable,
    Unavailable,
}

#[derive(Serialize)]
pub(crate) struct AgentHeartbeatReadiness {
    pub(crate) status: AgentHeartbeatStatus,
    #[serde(skip_serializing_if = "Option::is_none")]
    pub(crate) last_heartbeat_at_epoch_s: Option<u64>,
    #[serde(skip_serializing_if = "Option::is_none")]
    pub(crate) lease_expires_at_epoch_s: Option<u64>,
    #[serde(skip_serializing_if = "Option::is_none")]
    pub(crate) message: Option<String>,
}

#[derive(Clone, Copy, Debug, Eq, PartialEq, Serialize)]
#[serde(rename_all = "snake_case")]
pub(crate) enum AgentMcpCliStatus {
    Ok,
    Partial,
    DriftSuspected,
}

#[derive(Serialize)]
pub(crate) struct AgentMcpCliReadiness {
    pub(crate) status: AgentMcpCliStatus,
    pub(crate) binary_status: BinaryStatus,
    pub(crate) state_db_status: StateDbStatus,
    pub(crate) likely_host_process_drift: bool,
    #[serde(skip_serializing_if = "Option::is_none")]
    pub(crate) restart_hint: Option<String>,
}

pub(crate) fn build_agent_diagnostics(state_db_path: Option<&Path>) -> AgentDiagnostics {
    AgentDiagnostics {
        package_version: env!("CARGO_PKG_VERSION"),
        binary: build_binary_diagnostics(),
        state_db: build_state_db_diagnostics(state_db_path),
    }
}

pub(crate) fn build_summary(
    repo_root: &Path,
    marker_schema_status: MarkerSchemaStatus,
    diagnostics: &AgentDiagnostics,
    session: AgentSessionReadiness,
    heartbeat: AgentHeartbeatReadiness,
    mut blocking_failures: Vec<AgentReadinessFinding>,
    mut non_blocking_warnings: Vec<AgentReadinessFinding>,
) -> AgentReadinessSummary {
    let state_db = state_db_readiness(&diagnostics.state_db);
    let mcp_cli_diagnostics = mcp_cli_readiness(diagnostics);
    add_derived_findings(
        marker_schema_status,
        &mcp_cli_diagnostics,
        &session,
        &mut blocking_failures,
        &mut non_blocking_warnings,
    );
    let (next_action, recommended_command) = recommended_next_action(
        repo_root,
        marker_schema_status,
        &session,
        diagnostics,
        &blocking_failures,
        &non_blocking_warnings,
    );
    let status = if !blocking_failures.is_empty() {
        AgentReadinessStatus::Blocked
    } else if !non_blocking_warnings.is_empty() {
        AgentReadinessStatus::Warning
    } else {
        AgentReadinessStatus::Ready
    };

    AgentReadinessSummary {
        schema_version: AGENT_READINESS_SCHEMA_VERSION,
        status,
        next_action,
        recommended_command,
        marker_schema_status,
        state_db,
        session,
        heartbeat,
        mcp_cli_diagnostics,
        blocking_failures,
        non_blocking_warnings,
    }
}

fn add_derived_findings(
    marker_schema_status: MarkerSchemaStatus,
    mcp_cli_diagnostics: &AgentMcpCliReadiness,
    session: &AgentSessionReadiness,
    blocking_failures: &mut Vec<AgentReadinessFinding>,
    non_blocking_warnings: &mut Vec<AgentReadinessFinding>,
) {
    if matches!(
        marker_schema_status,
        MarkerSchemaStatus::Missing | MarkerSchemaStatus::Invalid
    ) && !has_check(blocking_failures, "repo_marker")
    {
        blocking_failures.push(AgentReadinessFinding::new(
            "repo_marker",
            FindingSeverity::Error,
            match marker_schema_status {
                MarkerSchemaStatus::Missing => "workspace marker is missing",
                MarkerSchemaStatus::Invalid => "workspace marker is invalid",
                _ => unreachable!(),
            },
            None,
        ));
    }

    match mcp_cli_diagnostics.status {
        AgentMcpCliStatus::DriftSuspected
            if !has_check(blocking_failures, "mcp_cli_diagnostics") =>
        {
            blocking_failures.push(AgentReadinessFinding::new(
                "mcp_cli_diagnostics",
                FindingSeverity::Error,
                mcp_cli_diagnostics
                    .restart_hint
                    .clone()
                    .unwrap_or_else(|| "MCP/CLI drift is suspected".to_owned()),
                None,
            ));
        }
        AgentMcpCliStatus::Partial if !has_check(non_blocking_warnings, "mcp_cli_diagnostics") => {
            non_blocking_warnings.push(AgentReadinessFinding::new(
                "mcp_cli_diagnostics",
                FindingSeverity::Warning,
                "MCP/CLI diagnostics are partial; inspect state-db and binary diagnostics before treating the host as fully healthy",
                None,
            ));
        }
        _ => {}
    }

    if session.status == AgentSessionStatus::Stale
        && !has_check(non_blocking_warnings, "session_state")
    {
        non_blocking_warnings.push(AgentReadinessFinding::new(
            "session_state",
            FindingSeverity::Warning,
            "session telemetry is stale",
            Some(session.path.clone()),
        ));
    }
}

fn has_check(findings: &[AgentReadinessFinding], check: &str) -> bool {
    findings.iter().any(|finding| finding.check == check)
}

pub(crate) fn session_readiness_for_layout(
    layout: Option<&StateLayout>,
) -> (AgentSessionReadiness, AgentHeartbeatReadiness) {
    let Some(layout) = layout else {
        return (
            AgentSessionReadiness {
                path: String::new(),
                status: AgentSessionStatus::Unavailable,
                lifecycle: None,
                owner_kind: None,
                actor_id: None,
                lease_expires_at_epoch_s: None,
                stale: None,
                message: Some(
                    "workspace-state paths could not be resolved before session inspection"
                        .to_owned(),
                ),
            },
            AgentHeartbeatReadiness {
                status: AgentHeartbeatStatus::Unavailable,
                last_heartbeat_at_epoch_s: None,
                lease_expires_at_epoch_s: None,
                message: Some(
                    "workspace-state paths could not be resolved before heartbeat inspection"
                        .to_owned(),
                ),
            },
        );
    };

    let path = layout.state_db_path().display().to_string();
    match session_state::load_for_layout(layout) {
        Ok(Some(state)) => {
            let activity = session_state::load_activity_for_layout(layout)
                .ok()
                .flatten();
            let projection = session_state::now_epoch_s().map(|now| {
                session_state::lifecycle_projection(&state, now, None, activity.as_ref())
            });
            match projection {
                Ok(projection) => {
                    let stale = projection.stale == Some(true) || state.session_id.is_none();
                    let session_status = if stale {
                        AgentSessionStatus::Stale
                    } else {
                        AgentSessionStatus::Active
                    };
                    let heartbeat_status = match projection.lifecycle {
                        Some(session_state::SessionLifecycle::Autonomous) if stale => {
                            AgentHeartbeatStatus::Stale
                        }
                        Some(session_state::SessionLifecycle::Autonomous)
                            if projection.last_heartbeat_at_epoch_s.is_some() =>
                        {
                            AgentHeartbeatStatus::Fresh
                        }
                        Some(session_state::SessionLifecycle::Autonomous) => {
                            AgentHeartbeatStatus::Missing
                        }
                        Some(session_state::SessionLifecycle::Interactive) => {
                            AgentHeartbeatStatus::NotApplicable
                        }
                        None => AgentHeartbeatStatus::Unavailable,
                    };
                    (
                        AgentSessionReadiness {
                            path,
                            status: session_status,
                            lifecycle: projection.lifecycle,
                            owner_kind: projection.owner_kind,
                            actor_id: projection.actor_id,
                            lease_expires_at_epoch_s: projection.lease_expires_at_epoch_s,
                            stale: projection.stale,
                            message: None,
                        },
                        AgentHeartbeatReadiness {
                            status: heartbeat_status,
                            last_heartbeat_at_epoch_s: projection.last_heartbeat_at_epoch_s,
                            lease_expires_at_epoch_s: projection.lease_expires_at_epoch_s,
                            message: None,
                        },
                    )
                }
                Err(error) => unreadable_session_readiness(path, error.to_string()),
            }
        }
        Ok(None) => (
            AgentSessionReadiness {
                path,
                status: AgentSessionStatus::Missing,
                lifecycle: None,
                owner_kind: None,
                actor_id: None,
                lease_expires_at_epoch_s: None,
                stale: None,
                message: Some("no session telemetry is recorded".to_owned()),
            },
            AgentHeartbeatReadiness {
                status: AgentHeartbeatStatus::NotApplicable,
                last_heartbeat_at_epoch_s: None,
                lease_expires_at_epoch_s: None,
                message: Some("no autonomous session heartbeat is expected".to_owned()),
            },
        ),
        Err(error) => unreadable_session_readiness(path, error.to_string()),
    }
}

fn unreadable_session_readiness(
    path: String,
    error: String,
) -> (AgentSessionReadiness, AgentHeartbeatReadiness) {
    (
        AgentSessionReadiness {
            path,
            status: AgentSessionStatus::Unreadable,
            lifecycle: None,
            owner_kind: None,
            actor_id: None,
            lease_expires_at_epoch_s: None,
            stale: None,
            message: Some(format!("failed to read session telemetry: {error}")),
        },
        AgentHeartbeatReadiness {
            status: AgentHeartbeatStatus::Unavailable,
            last_heartbeat_at_epoch_s: None,
            lease_expires_at_epoch_s: None,
            message: Some("session telemetry is unreadable".to_owned()),
        },
    )
}

fn recommended_next_action(
    repo_root: &Path,
    marker_schema_status: MarkerSchemaStatus,
    session: &AgentSessionReadiness,
    diagnostics: &AgentDiagnostics,
    blocking_failures: &[AgentReadinessFinding],
    non_blocking_warnings: &[AgentReadinessFinding],
) -> (AgentReadinessNextAction, Option<AgentRecommendedCommand>) {
    if diagnostics.binary.likely_host_process_drift
        || diagnostics.state_db.likely_host_process_drift
    {
        return (AgentReadinessNextAction::RestartHostProcess, None);
    }

    if marker_schema_status == MarkerSchemaStatus::Invalid {
        return (
            AgentReadinessNextAction::RepairMarker,
            Some(command(vec![
                "marker".to_owned(),
                "repair".to_owned(),
                "--path".to_owned(),
                repo_root.display().to_string(),
                "--yes".to_owned(),
            ])),
        );
    }
    if marker_schema_status == MarkerSchemaStatus::Missing {
        return (
            AgentReadinessNextAction::AttachWorkspace,
            Some(command(vec![
                "attach".to_owned(),
                "--path".to_owned(),
                repo_root.display().to_string(),
            ])),
        );
    }

    if blocking_failures.iter().any(|finding| {
        matches!(
            finding.check.as_str(),
            "profile_kernel" | "profile_file" | "clone_state"
        )
    }) {
        return (
            AgentReadinessNextAction::AttachWorkspace,
            Some(command(vec![
                "attach".to_owned(),
                "--path".to_owned(),
                repo_root.display().to_string(),
            ])),
        );
    }

    if blocking_failures
        .iter()
        .any(|finding| matches!(finding.check.as_str(), "repo_registry" | "repo_overlay"))
    {
        return (
            AgentReadinessNextAction::LinkWorkspace,
            Some(command(vec![
                "link".to_owned(),
                "--path".to_owned(),
                repo_root.display().to_string(),
            ])),
        );
    }

    if !blocking_failures.is_empty() {
        return (
            AgentReadinessNextAction::ResolveBlockers,
            Some(command(vec![
                "doctor".to_owned(),
                "--path".to_owned(),
                repo_root.display().to_string(),
            ])),
        );
    }

    if session.status == AgentSessionStatus::Stale {
        return (
            AgentReadinessNextAction::RefreshSession,
            Some(session_refresh_command(repo_root, session)),
        );
    }

    if non_blocking_warnings
        .iter()
        .any(|finding| finding.check == "installed_skills")
    {
        return (
            AgentReadinessNextAction::InstallSkills,
            Some(command(vec!["skills".to_owned(), "install".to_owned()])),
        );
    }

    if !non_blocking_warnings.is_empty() {
        return (
            AgentReadinessNextAction::ReviewWarnings,
            Some(command(vec![
                "doctor".to_owned(),
                "--path".to_owned(),
                repo_root.display().to_string(),
            ])),
        );
    }

    (AgentReadinessNextAction::Proceed, None)
}

fn session_refresh_command(
    repo_root: &Path,
    session: &AgentSessionReadiness,
) -> AgentRecommendedCommand {
    if session.lifecycle == Some(session_state::SessionLifecycle::Autonomous) {
        if let Some(actor_id) = &session.actor_id {
            return command(vec![
                "session-state".to_owned(),
                "start".to_owned(),
                "--path".to_owned(),
                repo_root.display().to_string(),
                "--lifecycle".to_owned(),
                "autonomous".to_owned(),
                "--actor-id".to_owned(),
                actor_id.clone(),
                "--recover-stale".to_owned(),
            ]);
        }
    }
    command(vec![
        "session-state".to_owned(),
        "start".to_owned(),
        "--path".to_owned(),
        repo_root.display().to_string(),
    ])
}

fn command(args: Vec<String>) -> AgentRecommendedCommand {
    let display_parts = std::iter::once("ccd".to_owned())
        .chain(args.iter().cloned())
        .map(|part| shell_display_word(&part))
        .collect::<Vec<_>>();
    AgentRecommendedCommand {
        program: "ccd",
        args,
        display: display_parts.join(" "),
    }
}

fn shell_display_word(value: &str) -> String {
    if value.bytes().all(|byte| {
        byte.is_ascii_alphanumeric() || matches!(byte, b'-' | b'_' | b'.' | b'/' | b':')
    }) {
        return value.to_owned();
    }
    format!("'{}'", value.replace('\'', "'\\''"))
}

fn state_db_readiness(diagnostics: &StateDbDiagnostics) -> AgentStateDbReadiness {
    AgentStateDbReadiness {
        path: diagnostics.path.clone(),
        status: diagnostics.status,
        supported_schema_version: diagnostics.supported_schema_version,
        current_schema_version: diagnostics.current_schema_version,
        likely_host_process_drift: diagnostics.likely_host_process_drift,
    }
}

fn mcp_cli_readiness(diagnostics: &AgentDiagnostics) -> AgentMcpCliReadiness {
    let likely_host_process_drift = diagnostics.binary.likely_host_process_drift
        || diagnostics.state_db.likely_host_process_drift;
    let status = if likely_host_process_drift {
        AgentMcpCliStatus::DriftSuspected
    } else if diagnostics.binary.status == BinaryStatus::OnDiskUnavailable
        || matches!(
            diagnostics.state_db.status,
            StateDbStatus::Unavailable | StateDbStatus::Missing | StateDbStatus::Unreadable
        )
    {
        AgentMcpCliStatus::Partial
    } else {
        AgentMcpCliStatus::Ok
    };
    let restart_hint = diagnostics
        .binary
        .restart_hint
        .clone()
        .or_else(|| diagnostics.state_db.restart_hint.clone());

    AgentMcpCliReadiness {
        status,
        binary_status: diagnostics.binary.status,
        state_db_status: diagnostics.state_db.status,
        likely_host_process_drift,
        restart_hint,
    }
}

fn build_binary_diagnostics() -> BinaryDiagnostics {
    let running_binary_path = std::env::current_exe()
        .map(|path| path.display().to_string())
        .unwrap_or_else(|error| format!("<unavailable: {error}>"));
    let invoked_binary_path = std::env::args().next();
    let on_disk_path = invoked_binary_path
        .as_ref()
        .map(PathBuf::from)
        .filter(|path| path.exists())
        .or_else(|| Some(PathBuf::from(&running_binary_path)));

    let running_binary = metadata_for_path(Path::new(&running_binary_path));
    let on_disk_binary = on_disk_path
        .as_deref()
        .map(metadata_for_path)
        .unwrap_or_else(|| metadata_for_path(Path::new(&running_binary_path)));

    let status = if metadata_identity_matches(&running_binary, &on_disk_binary) {
        BinaryStatus::MatchesOnDisk
    } else if on_disk_binary.exists {
        BinaryStatus::DiffersFromOnDisk
    } else {
        BinaryStatus::OnDiskUnavailable
    };
    let restart_hint = (status != BinaryStatus::MatchesOnDisk).then(|| {
        "running CCD process metadata differs from the invoked on-disk binary; restart or reconnect the long-lived MCP/agent process before treating repository state as corrupt".to_owned()
    });

    BinaryDiagnostics {
        running_binary_path,
        invoked_binary_path,
        running_binary,
        on_disk_binary,
        status,
        likely_host_process_drift: status == BinaryStatus::DiffersFromOnDisk,
        restart_hint,
    }
}

fn metadata_for_path(path: &Path) -> BinaryMetadata {
    match fs::metadata(path) {
        Ok(metadata) => BinaryMetadata {
            path: path.display().to_string(),
            exists: true,
            size_bytes: Some(metadata.len()),
            modified_epoch_s: metadata
                .modified()
                .ok()
                .and_then(|modified| modified.duration_since(UNIX_EPOCH).ok())
                .map(|duration| duration.as_secs()),
            #[cfg(unix)]
            inode: Some(metadata.ino()),
            #[cfg(unix)]
            device: Some(metadata.dev()),
            error: None,
        },
        Err(error) => BinaryMetadata {
            path: path.display().to_string(),
            exists: false,
            size_bytes: None,
            modified_epoch_s: None,
            #[cfg(unix)]
            inode: None,
            #[cfg(unix)]
            device: None,
            error: Some(error.to_string()),
        },
    }
}

fn metadata_identity_matches(left: &BinaryMetadata, right: &BinaryMetadata) -> bool {
    if !left.exists || !right.exists {
        return false;
    }
    #[cfg(unix)]
    {
        left.inode == right.inode
            && left.device == right.device
            && left.size_bytes == right.size_bytes
    }
    #[cfg(not(unix))]
    {
        left.path == right.path
            && left.size_bytes == right.size_bytes
            && left.modified_epoch_s == right.modified_epoch_s
    }
}

fn build_state_db_diagnostics(state_db_path: Option<&Path>) -> StateDbDiagnostics {
    let Some(path) = state_db_path else {
        return StateDbDiagnostics {
            path: String::new(),
            supported_schema_version: schema::CURRENT_VERSION,
            current_schema_version: None,
            status: StateDbStatus::Unavailable,
            likely_host_process_drift: false,
            message: "workspace-state paths could not be resolved; inspect repo marker/profile resolution before diagnosing schema drift".to_owned(),
            restart_hint: None,
        };
    };

    if !path.exists() {
        return StateDbDiagnostics {
            path: path.display().to_string(),
            supported_schema_version: schema::CURRENT_VERSION,
            current_schema_version: None,
            status: StateDbStatus::Missing,
            likely_host_process_drift: false,
            message: "state.db is missing; run a normal CCD command to initialize workspace state"
                .to_owned(),
            restart_hint: None,
        };
    }

    match read_state_db_user_version(path) {
        Ok(version) if version > schema::CURRENT_VERSION => StateDbDiagnostics {
            path: path.display().to_string(),
            supported_schema_version: schema::CURRENT_VERSION,
            current_schema_version: Some(version),
            status: StateDbStatus::NewerThanSupported,
            likely_host_process_drift: true,
            message: format!(
                "state.db schema version {version} is newer than supported ({}); this can indicate a stale long-lived MCP/agent process running an older CCD binary while the installed CLI is newer",
                schema::CURRENT_VERSION
            ),
            restart_hint: Some("restart or reconnect the long-lived MCP/agent process, then rerun `ccd doctor --output json` with the current CLI before editing repository state".to_owned()),
        },
        Ok(version) => StateDbDiagnostics {
            path: path.display().to_string(),
            supported_schema_version: schema::CURRENT_VERSION,
            current_schema_version: Some(version),
            status: StateDbStatus::Compatible,
            likely_host_process_drift: false,
            message: format!(
                "state.db schema version {version} is supported by this CCD binary (current supported {})",
                schema::CURRENT_VERSION
            ),
            restart_hint: None,
        },
        Err(error) => StateDbDiagnostics {
            path: path.display().to_string(),
            supported_schema_version: schema::CURRENT_VERSION,
            current_schema_version: None,
            status: StateDbStatus::Unreadable,
            likely_host_process_drift: false,
            message: format!("could not read state.db schema version without migration: {error}"),
            restart_hint: Some("verify the repository marker/profile first; if fresh CLI probes pass but MCP tools fail, restart or reconnect the long-lived MCP/agent process".to_owned()),
        },
    }
}

fn read_state_db_user_version(path: &Path) -> Result<u32> {
    let conn = Connection::open_with_flags(path, OpenFlags::SQLITE_OPEN_READ_ONLY)?;
    Ok(conn.pragma_query_value(None, "user_version", |row| row.get(0))?)
}
