iboard/server/mihome/history.js

// 历史查询聚合: today(时) / month(天) / year(周)
// 响应统一: [{ t(桶起始 ms, Asia/Shanghai), temperature, temperatureAvg, temperatureMin, temperatureMax,
//             humidity, humidityAvg, humidityMin, humidityMax, sampleCount?, missing? }]
import { prisma } from '../db.js'
import { dayStart, hourStart, monthStart, yearStart, weekStart, addDays, addHours, cnDateStr } from './time.js'

// 设备是否存在 (路由层校验)
export async function getDevice(did) {
  return prisma.mihomeDevice.findUnique({ where: { did } })
}

// ---------- 当天逐小时 ----------
export async function todayBuckets(device) {
  const start = dayStart()
  const now = hourStart()
  const rows = await prisma.mihomeEnvHour.findMany({
    where: { deviceId: device.id, hour: { gte: start, lt: addDays(start, 1) } },
    orderBy: { hour: 'asc' },
  })
  const byHour = new Map(rows.map((r) => [r.hour.getTime(), r]))
  const out = []
  for (let t = start.getTime(); t <= now.getTime(); t += 3600000) {
    const r = byHour.get(t)
    out.push({
      t,
      temperature: r?.temperatureAvg ?? null,
      humidity: r?.humidityAvg ?? null,
      sampleCount: r ? 1 : 0,
    })
  }
  return out
}

// ---------- 当前月逐日 ----------
export async function monthBuckets(device) {
  const start = monthStart()
  const today = dayStart()
  const end = addDays(today, 1)
  const rows = await prisma.mihomeEnvSample.findMany({
    where: { deviceId: device.id, date: { gte: start, lt: end } },
    orderBy: { date: 'asc' },
  })
  const byDay = new Map(rows.map((r) => [r.date.getTime(), r]))
  const out = []
  for (let t = start.getTime(); t < end.getTime(); t += 86400000) {
    const r = byDay.get(t)
    out.push(r
      ? {
          t,
          temperature: r.temperature,
          temperatureAvg: r.temperatureAvg,
          temperatureMin: r.temperatureMin,
          temperatureMax: r.temperatureMax,
          humidity: r.humidity,
          humidityAvg: r.humidityAvg,
          humidityMin: r.humidityMin,
          humidityMax: r.humidityMax,
          origin: r.origin,
          sampleCount: 1,
        }
      : { t, sampleCount: 0, missing: true })
  }
  return out
}

// ---------- 当前年逐周 (按日档现场聚合, 不落库) ----------
export async function yearBuckets(device) {
  const start = yearStart()
  const today = dayStart()
  const end = addDays(today, 1)
  const rows = await prisma.mihomeEnvSample.findMany({
    where: { deviceId: device.id, date: { gte: start, lt: end } },
    orderBy: { date: 'asc' },
  })
  // 日 -> 周 (上海周一为一周起点)
  const weekOf = (dayStartMs) => weekStart(new Date(dayStartMs)).getTime()
  const weeks = new Map() // weekStartMs -> { tempA: [], tempMin: [], tempMax: [], humA: [], humMin: [], humMax: [], days: [] }
  for (const r of rows) {
    const k = weekOf(r.date.getTime())
    if (!weeks.has(k)) {
      weeks.set(k, { tempA: [], tempMin: [], tempMax: [], humA: [], humMin: [], humMax: [], days: [] })
    }
    const w = weeks.get(k)
    if (r.temperatureAvg != null) w.tempA.push(r.temperatureAvg)
    if (r.temperatureMin != null) w.tempMin.push(r.temperatureMin)
    if (r.temperatureMax != null) w.tempMax.push(r.temperatureMax)
    if (r.humidityAvg != null) w.humA.push(r.humidityAvg)
    if (r.humidityMin != null) w.humMin.push(r.humidityMin)
    if (r.humidityMax != null) w.humMax.push(r.humidityMax)
    w.days.push(r.date)
  }
  const avg = (a) => (a.length ? a.reduce((x, y) => x + y, 0) / a.length : null)
  const out = []
  for (const [k, w] of [...weeks.entries()].sort((a, b) => a[0] - b[0])) {
    // 补齐周内缺的天数 (统计无数据的日算缺失)
    const wkStart = new Date(k)
    const missing = addDays(wkStart, 7)
    const haveMap = new Set(w.days.map((d) => d.getTime()))
    let missingDays = 0
    for (let t = k; t < missing.getTime(); t += 86400000) {
      // 只统计当年/到今天的日
      if (t >= start.getTime() && t < end.getTime() && !haveMap.has(t)) missingDays++
    }
    out.push({
      t: k,
      label: cnDateStr(wkStart).slice(5) + '~' + cnDateStr(addDays(wkStart, 6)).slice(5),
      temperatureAvg: avg(w.tempA),
      temperatureMin: w.tempMin.length ? Math.min(...w.tempMin) : null,
      temperatureMax: w.tempMax.length ? Math.max(...w.tempMax) : null,
      humidityAvg: avg(w.humA),
      humidityMin: w.humMin.length ? Math.min(...w.humMin) : null,
      humidityMax: w.humMax.length ? Math.max(...w.humMax) : null,
      sampleCount: w.days.length,
      missingDays,
    })
  }
  return out
}

// ---------- 设备概览 (状态页用): 最新样本 + 近 30 天缺日列表 ----------
export async function deviceOverview(device) {
  const latest = await prisma.mihomeEnvSample.findFirst({
    where: { deviceId: device.id },
    orderBy: { date: 'desc' },
  })
  // 近 30 天 (含今天) 缺少样本的上海日
  const start = addDays(dayStart(), -29)
  const end = addDays(dayStart(), 1)
  const have = await prisma.mihomeEnvSample.findMany({
    where: { deviceId: device.id, date: { gte: start, lt: end } },
    select: { date: true },
  })
  const haveSet = new Set(have.map((h) => h.date.getTime()))
  const missingDays = []
  for (let t = start.getTime(); t < end.getTime(); t += 86400000) {
    if (!haveSet.has(t)) missingDays.push(cnDateStr(new Date(t)))
  }
  return {
    latest,
    missingDays,
  }
}