rebreak-monorepo/backend/server/db/mdm.ts

import { usePrisma } from "../utils/prisma";
import pg from "pg";
import { randomUUID } from "node:crypto";
import { upsertDeviceProtectionState } from "./device-protection";

const { Pool } = pg;

let _mdmPool: pg.Pool | null = null;

/**
 * Lazily initialised pg.Pool against the NanoMDM Postgres.
 * Connection string comes from runtimeConfig.mdmDatabaseUrl.
 */
function useMdmPool(): pg.Pool {
  if (_mdmPool) return _mdmPool;

  const config = useRuntimeConfig();
  const connectionString = (config as any).mdmDatabaseUrl;

  if (!connectionString) {
    throw new Error("MDM_DATABASE_URL not configured");
  }

  _mdmPool = new Pool({
    connectionString,
    // NanoMDM queries are point lookups — keep pool small.
    max: 5,
    connectionTimeoutMillis: 5000,
    query_timeout: 5000,
  });

  return _mdmPool;
}

export interface UserDeviceMdmRecord {
  id: string;
  userId: string;
  deviceId: string;
  platform: string;
  mdmId: string | null;
}

const USER_DEVICE_MDM_SELECT = {
  id: true,
  userId: true,
  deviceId: true,
  platform: true,
  mdmId: true,
} as const;

const USER_DEVICE_MDM_HEALTH_SELECT = {
  id: true,
  userId: true,
  deviceId: true,
  platform: true,
  mdmId: true,
  mdmEnrolled: true,
  mdmSupervised: true,
  mdmLastSeenAt: true,
} as const;

/**
 * Find a user's iOS device by Capacitor deviceId.
 */
export async function getUserDeviceByDeviceId(
  userId: string,
  deviceId: string,
  platform: string = "ios",
): Promise<UserDeviceMdmRecord | null> {
  const db = usePrisma();
  return db.userDevice.findFirst({
    where: { userId, deviceId, platform },
    select: USER_DEVICE_MDM_SELECT,
  });
}

/**
 * Persist the NanoMDM UDID for a user's device.
 */
export async function setUserDeviceMdmId(
  userId: string,
  deviceId: string,
  mdmId: string,
): Promise<void> {
  const db = usePrisma();
  await db.userDevice.updateMany({
    where: { userId, deviceId, platform: "ios" },
    data: { mdmId },
  });
}

/**
 * Clear the stored NanoMDM UDID (e.g. device no longer enrolled).
 */
export async function clearUserDeviceMdmId(
  userId: string,
  deviceId: string,
): Promise<void> {
  const db = usePrisma();
  await db.userDevice.updateMany({
    where: { userId, deviceId, platform: "ios" },
    data: { mdmId: null },
  });
}

/**
 * Load all iOS devices that have a NanoMDM UDID link.
 */
export async function getLinkedUserDevices(): Promise<
  UserDeviceMdmHealthRecord[]
> {
  const db = usePrisma();
  return db.userDevice.findMany({
    where: { platform: "ios", mdmId: { not: null } },
    select: USER_DEVICE_MDM_HEALTH_SELECT,
  });
}

export interface MdmDeviceStatus {
  enrolled: boolean;
  exists: boolean;
  company: string | null;
  supervised: boolean;
  tokenUpdateAt: Date | null;
  lastAckAt: Date | null;
  lastAppPushAt: Date | null;
}

export interface MdmEnrollmentStatus {
  enrolled: boolean;
  supervised: boolean;
  lastSeenAt: Date | null;
}

export interface UserDeviceMdmHealthRecord {
  id: string;
  userId: string;
  deviceId: string;
  platform: string;
  mdmId: string | null;
  mdmEnrolled: boolean | null;
  mdmSupervised: boolean | null;
  mdmLastSeenAt: Date | null;
}

/**
 * Query NanoMDM Postgres for a device by UDID.
 *
 * Throws if the MDM DB is unreachable — callers should treat this as an
 * infra/runtime error and not cache a negative result.
 */
export async function getMdmStatusByUdid(
  udid: string,
): Promise<MdmDeviceStatus> {
  const pool = useMdmPool();

  // Defensive: only raw parameters reach the query layer below.
  const result = await pool.query<{
    unlock_token: Buffer | null;
    token_update_at: Date | null;
    last_ack: Date | null;
    last_app_push_at: Date | null;
    enrolled: boolean;
  }>(
    `SELECT
      d.unlock_token,
      d.token_update_at,
      COALESCE(e.enabled = TRUE, FALSE) AS enrolled,
      (SELECT max(updated_at) FROM command_results WHERE id = d.id) AS last_ack,
      (SELECT max(r.updated_at)
       FROM command_results r
       JOIN commands c ON c.command_uuid = r.command_uuid
       WHERE r.id = d.id
         AND c.request_type = 'InstallApplication'
         AND r.status = 'Acknowledged') AS last_app_push_at
     FROM devices d
     LEFT JOIN enrollments e ON e.device_id = d.id
     WHERE d.id = $1`,
    [udid],
  );

  const row = result.rows[0];
  const exists = row !== undefined;
  const enrolled = row?.enrolled ?? false;

  return {
    enrolled,
    exists,
    company: enrolled ? "ReBreak" : null,
    supervised: exists && row?.unlock_token != null,
    tokenUpdateAt: row?.token_update_at ?? null,
    lastAckAt: row?.last_ack ?? null,
    lastAppPushAt: row?.last_app_push_at ?? null,
  };
}

/**
 * Bulk-query NanoMDM for enrollment/supervision/last-seen status.
 * Returns a map keyed by UDID. Missing devices are omitted.
 *
 * Throws if the MDM DB is unreachable — callers should treat this as an
 * infra/runtime error and not cache a negative result.
 */
export async function getMdmEnrollmentStatusesByUdids(
  udids: string[],
): Promise<Map<string, MdmEnrollmentStatus>> {
  if (udids.length === 0) {
    return new Map();
  }

  const pool = useMdmPool();
  const result = await pool.query<{
    udid: string;
    enrolled: boolean;
    supervised: boolean;
    last_seen_at: Date | null;
  }>(
    `SELECT
       d.id AS udid,
       COALESCE(e.enabled = TRUE, FALSE) AS enrolled,
       (d.unlock_token IS NOT NULL) AS supervised,
       e.last_seen_at
     FROM devices d
     LEFT JOIN enrollments e ON e.device_id = d.id
     WHERE d.id = ANY($1::text[])`,
    [udids],
  );

  const map = new Map<string, MdmEnrollmentStatus>();
  for (const row of result.rows) {
    map.set(row.udid, {
      enrolled: row.enrolled,
      supervised: row.supervised,
      lastSeenAt: row.last_seen_at,
    });
  }
  return map;
}

/**
 * Persist mirrored MDM health status on a UserDevice row.
 */
export async function updateUserDeviceMdmHealth(
  id: string,
  status: MdmEnrollmentStatus,
): Promise<void> {
  const db = usePrisma();
  await db.userDevice.update({
    where: { id },
    data: {
      mdmEnrolled: status.enrolled,
      mdmSupervised: status.supervised,
      mdmLastSeenAt: status.lastSeenAt,
    },
  });
}

// ─── ProfileList-based lock-profile health check ─────────────────────────────

const LOCK_PROFILE_ID = "org.rebreak.protection.contentfilter.sideload";

/**
 * Send a ProfileList command to a device via the NanoMDM HTTP API.
 * Returns true if the command was accepted by NanoMDM.
 */
export async function enqueueProfileListCommand(udid: string): Promise<boolean> {
  const config = useRuntimeConfig();
  const baseUrl = (config as any).mdmApiUrl as string | undefined;
  const apiKey = (config as any).mdmApiKey as string | undefined;

  if (!baseUrl || !apiKey) {
    throw new Error("MDM_API_URL or MDM_API_KEY not configured");
  }

  const commandUuid = randomUUID();
  const plist = `<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
    <key>CommandUUID</key>
    <string>${commandUuid}</string>
    <key>Command</key>
    <dict>
        <key>RequestType</key>
        <string>ProfileList</string>
    </dict>
</dict>
</plist>`;

  const auth = Buffer.from(`nanomdm:${apiKey}`).toString("base64");
  const url = `${baseUrl.replace(/\/$/, "")}/v1/enqueue/${encodeURIComponent(udid)}?push=1`;

  const res = await fetch(url, {
    method: "PUT",
    headers: {
      Authorization: `Basic ${auth}`,
      "Content-Type": "application/x-plist",
    },
    body: plist,
  });

  if (!res.ok) {
    const text = await res.text().catch(() => "");
    throw new Error(`NanoMDM enqueue failed: ${res.status} ${res.statusText} ${text}`);
  }

  return true;
}

/**
 * Read the most recent ProfileList command result for a device.
 * Returns null if no result exists yet.
 */
export async function getLatestProfileListResult(
  udid: string,
): Promise<{ result: string; updatedAt: Date } | null> {
  const pool = useMdmPool();
  const res = await pool.query<{
    result: string;
    updated_at: Date;
  }>(
    `SELECT cr.result, cr.updated_at
     FROM command_results cr
     JOIN commands c ON c.command_uuid = cr.command_uuid
     WHERE cr.id = $1
       AND c.request_type = 'ProfileList'
       AND cr.status = 'Acknowledged'
     ORDER BY cr.updated_at DESC
     LIMIT 1`,
    [udid],
  );

  return res.rows[0] ?? null;
}

/**
 * Extract all PayloadIdentifier values from a ProfileList response plist
 * and check whether the ReBreak sideload lock profile is present.
 */
export function isLockProfileInstalled(xml: string): boolean {
  const regex = /<key>PayloadIdentifier<\/key>\s*<string>([^<]+)<\/string>/g;
  let match: RegExpExecArray | null;
  const identifiers: string[] = [];
  while ((match = regex.exec(xml)) !== null) {
    identifiers.push(match[1]);
  }
  return identifiers.includes(LOCK_PROFILE_ID);
}

/**
 * If the lock profile is missing according to a ProfileList response,
 * mark the device's nefilter protection state as inactive.
 */
export async function markNefilterInactiveIfLockProfileMissing(
  userId: string,
  deviceId: string,
  xml: string,
  lastSeenAt?: Date,
): Promise<void> {
  if (isLockProfileInstalled(xml)) {
    return;
  }

  await upsertDeviceProtectionState(
    userId,
    deviceId,
    "ios",
    "nefilter",
    false,
    lastSeenAt ?? new Date(),
    "MDM ProfileList: lock profile not installed",
    "mdm",
  );
}