nekonest-cloud/db/device-control-plane.ts

export const CONTROL_PLANE_CONTACT_FRESH_MS = 10 * 60 * 1_000;
export const CONTROL_PLANE_CONTACT_DELAYED_MS = 30 * 60 * 1_000;
const CONTROL_PLANE_CONTACT_FUTURE_SKEW_MS = 5 * 60 * 1_000;

const ADMIN_HOST_CONTACT_CTE = `
  WITH latest_contact AS (
    SELECT hosts.id, hosts.account_id, hosts.name, hosts.os,
           hosts.daemon_version, accounts.email,
           MAX(credentials.last_used_at) AS control_plane_last_seen_at
    FROM hosts
    INNER JOIN accounts ON accounts.id = hosts.account_id
    LEFT JOIN device_credentials AS credentials ON credentials.host_id = hosts.id
    WHERE hosts.lifecycle = 'active' AND hosts.slot_state = 'active'
    GROUP BY hosts.id, hosts.account_id, hosts.name, hosts.os,
             hosts.daemon_version, accounts.email
  ), categorized AS (
    SELECT *, CASE
      WHEN control_plane_last_seen_at IS NULL THEN 'never'
      WHEN julianday(control_plane_last_seen_at) IS NULL
        OR julianday(control_plane_last_seen_at) > julianday(?1) THEN 'invalid'
      WHEN julianday(control_plane_last_seen_at) >= julianday(?2) THEN 'fresh'
      WHEN julianday(control_plane_last_seen_at) >= julianday(?3) THEN 'delayed'
      ELSE 'stale'
    END AS contact_state
    FROM latest_contact
  )
`;

export const ADMIN_HOST_CONTROL_PLANE_SUMMARY_SQL = `${ADMIN_HOST_CONTACT_CTE}
  SELECT COUNT(*) AS total_active,
         SUM(CASE WHEN contact_state = 'fresh' THEN 1 ELSE 0 END) AS fresh,
         SUM(CASE WHEN contact_state = 'delayed' THEN 1 ELSE 0 END) AS delayed,
         SUM(CASE WHEN contact_state = 'stale' THEN 1 ELSE 0 END) AS stale,
         SUM(CASE WHEN contact_state = 'never' THEN 1 ELSE 0 END) AS never,
         SUM(CASE WHEN contact_state = 'invalid' THEN 1 ELSE 0 END) AS invalid,
         SUM(CASE WHEN daemon_version IS NULL THEN 1 ELSE 0 END) AS version_unknown
  FROM categorized
`;

export const ADMIN_HOST_CONTROL_PLANE_ATTENTION_SQL = `${ADMIN_HOST_CONTACT_CTE}
  SELECT id, account_id, name, os, daemon_version, email,
         control_plane_last_seen_at, contact_state
  FROM categorized
  WHERE contact_state <> 'fresh'
  ORDER BY CASE contact_state
             WHEN 'invalid' THEN 0
             WHEN 'never' THEN 1
             WHEN 'stale' THEN 2
             WHEN 'delayed' THEN 3
             ELSE 4
           END,
           control_plane_last_seen_at ASC, id ASC
  LIMIT 25
`;

export const OWNED_HOSTS_WITH_CONTROL_PLANE_CONTACT_SQL = `
  SELECT hosts.*,
         (SELECT MAX(credentials.last_used_at)
          FROM device_credentials AS credentials
          WHERE credentials.host_id = hosts.id)
           AS control_plane_last_seen_at
  FROM hosts
  WHERE hosts.account_id = ?
  ORDER BY hosts.lifecycle = 'active' DESC, hosts.created_at DESC
`;

export type ControlPlaneContactState =
  | "never"
  | "fresh"
  | "delayed"
  | "stale"
  | "invalid";

export type ControlPlaneContact = {
  state: ControlPlaneContactState;
  label: string;
  detail: string;
  tone: "good" | "warn" | "danger" | "neutral";
};

type AdminHostControlPlaneAggregateRow = Record<string, number | null>;

export type AdminHostControlPlaneAttentionRow = {
  id: string;
  account_id: string;
  name: string;
  os: string;
  daemon_version: string | null;
  email: string;
  control_plane_last_seen_at: string | null;
  contact_state: Exclude<ControlPlaneContactState, "fresh">;
};

export type AdminHostControlPlaneSnapshot = {
  generatedAt: string;
  totalActive: number;
  fresh: number;
  delayed: number;
  stale: number;
  never: number;
  invalid: number;
  versionUnknown: number;
  attentionHosts: AdminHostControlPlaneAttentionRow[];
};

function aggregateCount(
  row: AdminHostControlPlaneAggregateRow,
  key: string,
): number {
  const value = Number(row[key] ?? 0);
  return Number.isFinite(value) && value > 0 ? Math.floor(value) : 0;
}

export function controlPlaneContactCutoffs(generatedAt: string) {
  const nowMs = Date.parse(generatedAt);
  if (!Number.isFinite(nowMs)) throw new Error("invalid_control_plane_contact_time");
  return {
    futureLimitAt: new Date(nowMs + CONTROL_PLANE_CONTACT_FUTURE_SKEW_MS).toISOString(),
    freshCutoff: new Date(nowMs - CONTROL_PLANE_CONTACT_FRESH_MS).toISOString(),
    delayedCutoff: new Date(nowMs - CONTROL_PLANE_CONTACT_DELAYED_MS).toISOString(),
  };
}

export function deriveAdminHostControlPlaneSnapshot(input: {
  generatedAt: string;
  summary: AdminHostControlPlaneAggregateRow;
  attentionHosts: AdminHostControlPlaneAttentionRow[];
}): AdminHostControlPlaneSnapshot {
  return {
    generatedAt: input.generatedAt,
    totalActive: aggregateCount(input.summary, "total_active"),
    fresh: aggregateCount(input.summary, "fresh"),
    delayed: aggregateCount(input.summary, "delayed"),
    stale: aggregateCount(input.summary, "stale"),
    never: aggregateCount(input.summary, "never"),
    invalid: aggregateCount(input.summary, "invalid"),
    versionUnknown: aggregateCount(input.summary, "version_unknown"),
    attentionHosts: input.attentionHosts,
  };
}

export function deriveControlPlaneContact(
  lastSeenAt: string | null,
  nowMs = Date.now(),
): ControlPlaneContact {
  if (!lastSeenAt) {
    return {
      state: "never",
      label: "尚未联系",
      detail: "daemon 还没有成功查询 Cloud 开通状态。",
      tone: "neutral",
    };
  }

  const seenAtMs = Date.parse(lastSeenAt);
  if (
    !Number.isFinite(seenAtMs) ||
    seenAtMs > nowMs + CONTROL_PLANE_CONTACT_FUTURE_SKEW_MS
  ) {
    return {
      state: "invalid",
      label: "时间待核实",
      detail: "最近一次控制面签到时间无效或明显超前。",
      tone: "warn",
    };
  }

  const ageMs = Math.max(0, nowMs - seenAtMs);
  if (ageMs <= CONTROL_PLANE_CONTACT_FRESH_MS) {
    return {
      state: "fresh",
      label: "控制面正常",
      detail: "daemon 最近成功查询了 Cloud 开通状态。",
      tone: "good",
    };
  }
  if (ageMs <= CONTROL_PLANE_CONTACT_DELAYED_MS) {
    return {
      state: "delayed",
      label: "联系延迟",
      detail: "daemon 一段时间没有再次查询 Cloud 开通状态。",
      tone: "warn",
    };
  }
  return {
    state: "stale",
    label: "长时间未联系",
    detail: "daemon 已超过半小时没有查询 Cloud 开通状态。",
    tone: "danger",
  };
}