badminton-scoreboard/server/server.mjs

import 'dotenv/config'
import express from 'express'
import mysql from 'mysql2/promise'
import path from 'node:path'
import { existsSync, mkdirSync, readFileSync, writeFileSync } from 'node:fs'
import { fileURLToPath } from 'node:url'

const app = express()
const port = Number(process.env.PORT ?? process.env.SERVER_PORT ?? 8788)
const matchTableName = process.env.DB_TABLE ?? 'badminton'
const historyTableName = process.env.DB_HISTORY_TABLE ?? 'history'
const appVersion = process.env.APP_VERSION ?? `${Date.now()}`
const appStartedAt = new Date().toISOString()
const LIVE_ROOM_STALE_MS = 30_000

const currentFilePath = fileURLToPath(import.meta.url)
const currentDir = path.dirname(currentFilePath)
const projectRoot = path.resolve(currentDir, '..')
const distDir = path.join(projectRoot, 'dist')
const roomDataDir = path.join(projectRoot, 'server', 'data')
const roomsFilePath = path.join(roomDataDir, 'live-rooms.json')
const distReady = existsSync(path.join(distDir, 'index.html'))

const requiredEnv = ['DB_HOST', 'DB_PORT', 'DB_USER', 'DB_PASSWORD', 'DB_DATABASE']
const missingEnv = requiredEnv.filter((key) => !process.env[key])

const pool =
  missingEnv.length === 0
    ? mysql.createPool({
        host: process.env.DB_HOST,
        port: Number(process.env.DB_PORT),
        user: process.env.DB_USER,
        password: process.env.DB_PASSWORD,
        database: process.env.DB_DATABASE,
        charset: 'utf8mb4',
        waitForConnections: true,
        connectionLimit: 10,
      })
    : null

const rooms = loadPersistedRooms()
const roomListClients = new Set()

app.use(express.json())

app.get('/api/health', (_request, response) => {
  response.json({
    appStartedAt,
    appVersion,
    ok: true,
    dbReady: Boolean(pool),
    distReady,
    historyTableName,
    matchTableName,
    missingEnv,
  })
})

app.get('/api/version', (_request, response) => {
  response.set({
    'Cache-Control': 'no-store, no-cache, must-revalidate, proxy-revalidate',
    Expires: '0',
    Pragma: 'no-cache',
    'Surrogate-Control': 'no-store',
  })

  response.json({
    ok: true,
    startedAt: appStartedAt,
    version: appVersion,
  })
})

app.get('/api/rooms', (_request, response) => {
  response.json({
    ok: true,
    data: getLiveRoomSummaries(),
  })
})

app.post('/api/rooms/reconcile', (_request, response) => {
  const removedRoomIds = pruneStaleRooms()

  response.json({
    ok: true,
    data: {
      removedRoomIds,
    },
  })
})

app.get('/api/rooms/stream', (request, response) => {
  setupSse(response)
  roomListClients.add(response)
  sendSse(response, 'rooms', getLiveRoomSummaries())

  request.on('close', () => {
    roomListClients.delete(response)
  })
})

app.post('/api/rooms', (request, response) => {
  const payload = normalizeRoomPayload(request.body)

  if (!payload.ok) {
    response.status(400).json({
      ok: false,
      message: payload.message,
    })
    return
  }

  const roomId = createRoomId()
  const hostToken = createHostToken()
  const now = new Date().toISOString()
  const room = {
    ...payload.value,
    clients: new Set(),
    createdAt: now,
    hostToken,
    hostSeenAt: now,
    roomId,
    status: 'live',
    updatedAt: now,
    winnerTeamName: null,
  }

  rooms.set(roomId, room)
  persistRooms()
  broadcastRoom(room)
  broadcastRoomList()

  response.json({
    ok: true,
    data: {
      hostToken,
      roomId,
      status: room.status,
    },
  })
})

app.post('/api/rooms/:roomId/release', (request, response) => {
  const room = rooms.get(request.params.roomId)

  if (!room) {
    response.json({
      ok: true,
    })
    return
  }

  const { hostToken } = request.body ?? {}

  if (typeof hostToken !== 'string' || hostToken !== room.hostToken) {
    response.status(403).json({
      ok: false,
      message: '沒有權限清除此房間。',
    })
    return
  }

  if (room.status === 'finished') {
    response.json({
      ok: true,
    })
    return
  }

  room.clients.forEach((client) => {
    sendSse(client, 'room-closed', {
      roomId: room.roomId,
      status: 'released',
    })
    client.end()
  })

  rooms.delete(room.roomId)
  persistRooms()
  broadcastRoomList()

  response.json({
    ok: true,
  })
})

app.post('/api/rooms/:roomId/heartbeat', (request, response) => {
  const room = rooms.get(request.params.roomId)

  if (!room) {
    response.status(404).json({
      ok: false,
      message: '找不到這個房間。',
    })
    return
  }

  const { hostToken } = request.body ?? {}

  if (typeof hostToken !== 'string' || hostToken !== room.hostToken) {
    response.status(403).json({
      ok: false,
      message: '沒有權限更新房間心跳。',
    })
    return
  }

  room.hostSeenAt = new Date().toISOString()
  persistRooms()

  response.json({
    ok: true,
  })
})

app.get('/api/rooms/:roomId', (request, response) => {
  const room = rooms.get(request.params.roomId)

  if (!room) {
    response.status(404).json({
      ok: false,
      message: '找不到這個房間。',
    })
    return
  }

  response.json({
    ok: true,
    data: serializeRoom(room),
  })
})

app.get('/api/rooms/:roomId/stream', (request, response) => {
  const room = rooms.get(request.params.roomId)

  if (!room) {
    response.status(404).json({
      ok: false,
      message: '找不到這個房間。',
    })
    return
  }

  setupSse(response)
  room.clients.add(response)
  sendSse(response, 'room', serializeRoom(room))

  request.on('close', () => {
    room.clients.delete(response)
  })
})

app.put('/api/rooms/:roomId', (request, response) => {
  const room = rooms.get(request.params.roomId)

  if (!room) {
    response.status(404).json({
      ok: false,
      message: '找不到這個房間。',
    })
    return
  }

  const { hostToken, status, winnerTeamName } = request.body ?? {}

  if (typeof hostToken !== 'string' || hostToken !== room.hostToken) {
    response.status(403).json({
      ok: false,
      message: '沒有更新這個房間的權限。',
    })
    return
  }

  const payload = normalizeRoomPayload(request.body)

  if (!payload.ok) {
    response.status(400).json({
      ok: false,
      message: payload.message,
    })
    return
  }

  room.groupId = payload.value.groupId
  room.leftTeamName = payload.value.leftTeamName
  room.matchupLabel = payload.value.matchupLabel
  room.pointLog = payload.value.pointLog
  room.rightTeamName = payload.value.rightTeamName
  room.scoreState = payload.value.scoreState
  room.targetDate = payload.value.targetDate
  room.status = status === 'finished' ? 'finished' : 'live'
  room.updatedAt = new Date().toISOString()
  room.winnerTeamName = room.status === 'finished' && typeof winnerTeamName === 'string'
    ? winnerTeamName
    : null

  persistRooms()
  broadcastRoom(room)
  broadcastRoomList()

  response.json({
    ok: true,
    data: serializeRoom(room),
  })
})

app.get('/api/match-results/:time', async (request, response) => {
  if (!pool) {
    response.status(500).json({
      ok: false,
      message: `DB 尚未設定完成，缺少 ${missingEnv.join(', ')}`,
    })
    return
  }

  const time = String(request.params.time ?? '')

  if (!/^\d{8}$/.test(time)) {
    response.status(400).json({
      ok: false,
      message: '日期格式必須是 YYYYMMDD。',
    })
    return
  }

  try {
    await ensureMatchTable(pool, matchTableName)
    const [rows] = await pool.execute(
      `SELECT time, personnel, battlecombination FROM \`${matchTableName}\` WHERE time = ? LIMIT 1`,
      [Number(time)],
    )

    const record = rows[0]

    if (!record) {
      response.status(404).json({
        ok: false,
        message: '指定日期沒有資料。',
      })
      return
    }

    response.json({
      ok: true,
      data: record,
    })
  } catch (error) {
    console.error('match-results load error:', error)
    response.status(500).json({
      ok: false,
      message: error instanceof Error ? error.message : '讀取對戰資料失敗。',
    })
  }
})

app.post('/api/history', async (request, response) => {
  if (!pool) {
    response.status(500).json({
      ok: false,
      message: `DB 尚未設定完成，缺少 ${missingEnv.join(', ')}`,
    })
    return
  }

  const {
    dayOfWeek,
    players,
    score,
    scoreList,
    team,
    time,
    type,
    winScore,
  } = request.body ?? {}

  if (
    typeof time !== 'number' ||
    typeof dayOfWeek !== 'number' ||
    typeof winScore !== 'number' ||
    typeof type !== 'number' ||
    !Array.isArray(score) ||
    score.length !== 2 ||
    !Array.isArray(players) ||
    !Array.isArray(team) ||
    !Array.isArray(scoreList)
  ) {
    response.status(400).json({
      ok: false,
      message: '戰績資料格式不正確。',
    })
    return
  }

  try {
    await ensureHistoryTable(pool, historyTableName)
    const [result] = await pool.execute(
      `
        INSERT INTO \`${historyTableName}\`
          (time, dayOfWeek, score, winScore, type, players, team, scoreList)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
      `,
      [
        time,
        dayOfWeek,
        JSON.stringify(score),
        winScore,
        type,
        JSON.stringify(players),
        JSON.stringify(team),
        JSON.stringify(scoreList),
      ],
    )

    response.json({
      ok: true,
      data: {
        id: result.insertId,
      },
      message: '戰績已寫入 DB。',
    })
  } catch (error) {
    console.error('history save error:', error)
    response.status(500).json({
      ok: false,
      message: error instanceof Error ? error.message : '寫入戰績失敗。',
    })
  }
})

app.get('/api/history', async (_request, response) => {
  if (!pool) {
    response.status(500).json({
      ok: false,
      message: `DB 尚未設定完成，缺少 ${missingEnv.join(', ')}`,
    })
    return
  }

  try {
    await ensureHistoryTable(pool, historyTableName)
    const [rows] = await pool.execute(
      `
        SELECT id, time, dayOfWeek, score, winScore, type, players, team, scoreList
        FROM \`${historyTableName}\`
        ORDER BY id DESC
      `,
    )

    response.json({
      ok: true,
      data: rows,
    })
  } catch (error) {
    console.error('history load error:', error)
    response.status(500).json({
      ok: false,
      message: error instanceof Error ? error.message : '讀取歷史戰績失敗。',
    })
  }
})

app.delete('/api/history/:id', async (request, response) => {
  if (!pool) {
    response.status(500).json({
      ok: false,
      message: `DB 設定不完整，缺少：${missingEnv.join(', ')}`,
    })
    return
  }

  const id = Number(request.params.id)

  if (!Number.isInteger(id) || id <= 0) {
    response.status(400).json({
      ok: false,
      message: '戰績編號格式不正確。',
    })
    return
  }

  try {
    await ensureHistoryTable(pool, historyTableName)
    const [result] = await pool.execute(
      `DELETE FROM \`${historyTableName}\` WHERE id = ? LIMIT 1`,
      [id],
    )

    if (result.affectedRows === 0) {
      response.status(404).json({
        ok: false,
        message: '找不到要刪除的戰績。',
      })
      return
    }

    response.json({
      ok: true,
      message: '戰績已刪除。',
    })
  } catch (error) {
    console.error('history delete error:', error)
    response.status(500).json({
      ok: false,
      message: error instanceof Error ? error.message : '刪除戰績失敗。',
    })
  }
})

if (distReady) {
  app.use(express.static(distDir))

  app.get(/^(?!\/api).*/, (_request, response) => {
    response.sendFile(path.join(distDir, 'index.html'))
  })
}

app.listen(port, () => {
  console.log(`Server ready on http://localhost:${port}`)
  if (missingEnv.length > 0) {
    console.log(`Missing env: ${missingEnv.join(', ')}`)
  }
})

function createHostToken() {
  return `${Date.now().toString(36)}${Math.random().toString(36).slice(2, 10)}`
}

function createRoomId() {
  let roomId = ''

  do {
    roomId = String(Math.floor(100000 + Math.random() * 900000))
  } while (rooms.has(roomId))

  return roomId
}

function loadPersistedRooms() {
  const nextRooms = new Map()

  try {
    if (!existsSync(roomsFilePath)) {
      return nextRooms
    }

    const raw = readFileSync(roomsFilePath, 'utf8')

    if (!raw.trim()) {
      return nextRooms
    }

    const savedRooms = JSON.parse(raw)

    if (!Array.isArray(savedRooms)) {
      return nextRooms
    }

    savedRooms.forEach((savedRoom) => {
      if (!savedRoom || typeof savedRoom !== 'object' || typeof savedRoom.roomId !== 'string') {
        return
      }

      nextRooms.set(savedRoom.roomId, {
        ...savedRoom,
        hostSeenAt:
          typeof savedRoom.hostSeenAt === 'string' ? savedRoom.hostSeenAt : savedRoom.updatedAt,
        clients: new Set(),
      })
    })
  } catch (error) {
    console.error('load persisted rooms error:', error)
  }

  return nextRooms
}

function persistRooms() {
  try {
    mkdirSync(roomDataDir, { recursive: true })
    writeFileSync(
      roomsFilePath,
      JSON.stringify(
        Array.from(rooms.values()).map((room) => ({
          createdAt: room.createdAt,
          groupId: room.groupId,
          hostToken: room.hostToken,
          hostSeenAt: room.hostSeenAt,
          leftTeamName: room.leftTeamName,
          matchupLabel: room.matchupLabel,
          pointLog: room.pointLog,
          rightTeamName: room.rightTeamName,
          roomId: room.roomId,
          scoreState: room.scoreState,
          status: room.status,
          targetDate: room.targetDate,
          updatedAt: room.updatedAt,
          winnerTeamName: room.winnerTeamName,
        })),
        null,
        2,
      ),
      'utf8',
    )
  } catch (error) {
    console.error('persist rooms error:', error)
  }
}

function normalizeRoomPayload(value) {
  const {
    groupId,
    leftTeamName,
    matchupLabel,
    pointLog,
    rightTeamName,
    scoreState,
    targetDate,
  } = value ?? {}

  if (
    typeof leftTeamName !== 'string' ||
    typeof rightTeamName !== 'string' ||
    typeof matchupLabel !== 'string' ||
    typeof targetDate !== 'string' ||
    !Array.isArray(pointLog) ||
    !scoreState ||
    typeof scoreState !== 'object'
  ) {
    return {
      ok: false,
      message: '房間資料格式不正確。',
    }
  }

  return {
    ok: true,
    value: {
      groupId: typeof groupId === 'number' ? groupId : null,
      leftTeamName,
      matchupLabel,
      pointLog,
      rightTeamName,
      scoreState,
      targetDate,
    },
  }
}

function serializeRoom(room) {
  return {
    roomId: room.roomId,
    groupId: room.groupId,
    leftTeamName: room.leftTeamName,
    matchupLabel: room.matchupLabel,
    pointLog: room.pointLog,
    rightTeamName: room.rightTeamName,
    scoreState: room.scoreState,
    status: room.status,
    targetDate: room.targetDate,
    createdAt: room.createdAt,
    updatedAt: room.updatedAt,
    winnerTeamName: room.winnerTeamName,
  }
}

function pruneStaleRooms() {
  const now = Date.now()
  const removedRoomIds = []

  rooms.forEach((room, roomId) => {
    if (room.status !== 'live') {
      return
    }

    const hostSeenAtTime = Date.parse(room.hostSeenAt ?? '')

    if (!Number.isFinite(hostSeenAtTime) || now - hostSeenAtTime > LIVE_ROOM_STALE_MS) {
      room.clients.forEach((client) => {
        sendSse(client, 'room-closed', {
          roomId: room.roomId,
          status: 'stale',
        })
        client.end()
      })

      rooms.delete(roomId)
      removedRoomIds.push(roomId)
    }
  })

  if (removedRoomIds.length > 0) {
    persistRooms()
    broadcastRoomList()
  }

  return removedRoomIds
}

function getLiveRoomSummaries() {
  return Array.from(rooms.values())
    .filter((room) => room.status === 'live')
    .sort((left, right) => right.updatedAt.localeCompare(left.updatedAt))
    .map((room) => ({
      roomId: room.roomId,
      createdAt: room.createdAt,
      leftTeamName: room.leftTeamName,
      rightTeamName: room.rightTeamName,
      scoreLeft: room.scoreState.scoreLeft,
      scoreRight: room.scoreState.scoreRight,
      status: room.status,
      targetScore: room.scoreState.targetScore,
      updatedAt: room.updatedAt,
    }))
}

function setupSse(response) {
  response.writeHead(200, {
    'Cache-Control': 'no-cache, no-transform',
    Connection: 'keep-alive',
    'Content-Type': 'text/event-stream',
  })
}

function sendSse(response, eventName, payload) {
  response.write(`event: ${eventName}\n`)
  response.write(`data: ${JSON.stringify(payload)}\n\n`)
}

function broadcastRoom(room) {
  const payload = serializeRoom(room)
  room.clients.forEach((client) => {
    sendSse(client, 'room', payload)
  })
}

function broadcastRoomList() {
  const payload = getLiveRoomSummaries()
  roomListClients.forEach((client) => {
    sendSse(client, 'rooms', payload)
  })
}

async function ensureMatchTable(poolInstance, currentTableName) {
  await poolInstance.execute(`
    CREATE TABLE IF NOT EXISTS \`${currentTableName}\` (
      time INT(11) NOT NULL,
      personnel TEXT NOT NULL,
      battlecombination TEXT DEFAULT NULL,
      PRIMARY KEY (time)
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
  `)
}

async function ensureHistoryTable(poolInstance, currentTableName) {
  await poolInstance.execute(`
    CREATE TABLE IF NOT EXISTS \`${currentTableName}\` (
      id INT(11) NOT NULL AUTO_INCREMENT,
      time INT(11) NOT NULL COMMENT '記錄時間',
      dayOfWeek INT(1) NOT NULL COMMENT '星期',
      score VARCHAR(255) NOT NULL COMMENT '隊伍分數 [ [隊伍1分數], [隊伍2分數] ]',
      winScore INT(2) NOT NULL COMMENT '幾分獲勝',
      type INT(1) NOT NULL COMMENT '遊戲類型(0:雙打,1:單打)',
      players VARCHAR(255) NOT NULL COMMENT '玩家',
      team VARCHAR(255) NOT NULL COMMENT '玩家隊伍 [ [隊伍1成員], [隊伍2成員] ]',
      scoreList TEXT DEFAULT NULL COMMENT '得分過程[round, starter, winCount, winner]',
      PRIMARY KEY (id)
    ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
  `)
}