import { db } from '~/drizzle/db'
import { expectedPlayDates, songs } from '~/drizzle/schema'
import { eq, and } from 'drizzle-orm'

export default defineEventHandler(async (event) => {
  try {
    // 获取所有开启的期望播放日期
    const dates = await db.select().from(expectedPlayDates).where(eq(expectedPlayDates.enabled, true))

    // 对每个日期，解析其可用的 sequence 列表，并排除已被未播放歌曲占用的 sequence
    const result = []
    
    for (const d of dates) {
      // 获取日期的星期几
      const dateObj = new Date(d.date)
      const weekdays = ['周日', '周一', '周二', '周三', '周四', '周五', '周六']
      const weekday = weekdays[dateObj.getDay()]

      // 解析 availableSequences (例如 "1,2,3,4,5")
      let availableSeqs: number[] = []
      if (d.availableSequences) {
        availableSeqs = d.availableSequences.split(',').map(s => parseInt(s.trim(), 10)).filter(n => !isNaN(n))
      }

      // 查找在这个日期上已经被占用的 sequence（且歌曲还没播放）
      const takenSongs = await db.select({
        sequence: songs.expectedPlaySequence,
        title: songs.title,
        artist: songs.artist,
        requesterId: songs.requesterId
      })
      .from(songs)
      .where(
        and(
          eq(songs.expectedPlayDate, d.date),
          eq(songs.played, false)
        )
      )

      const takenSeqs = new Set(takenSongs.map(s => s.sequence).filter(seq => seq !== null))
      const takenDetails: Record<number, any> = {}
      takenSongs.forEach(s => {
        if (s.sequence !== null) {
          takenDetails[s.sequence] = { title: s.title, artist: s.artist }
        }
      })

      // 过滤出真正空闲的 sequence
      const freeSeqs = availableSeqs.filter(seq => !takenSeqs.has(seq))

      result.push({
        id: d.id,
        date: d.date,
        weekday: weekday,
        allSequences: availableSeqs.sort((a, b) => a - b),
        takenSequences: Array.from(takenSeqs).sort((a, b) => a - b),
        takenDetails: takenDetails,
        freeSequences: freeSeqs.sort((a, b) => a - b),
        isFull: freeSeqs.length === 0
      })
    }

    // 按日期排序
    result.sort((a, b) => a.date.localeCompare(b.date))

    return result
  } catch (error) {
    console.error('获取期望播出日期失败:', error)
    throw createError({
      statusCode: 500,
      message: '获取期望播出日期失败'
    })
  }
})