import { and, asc, eq } from 'drizzle-orm'

export type ProjectRow = typeof tables.projects.$inferSelect
export type CharacterRow = typeof tables.characters.$inferSelect
export type LoreRow = typeof tables.loreEntries.$inferSelect
export type AgentRow = typeof tables.agents.$inferSelect
export type ChapterRow = typeof tables.chapters.$inferSelect
export type VersionRow = typeof tables.chapterVersions.$inferSelect
export type ModelRow = typeof tables.models.$inferSelect

export function getProject(projectId: string): ProjectRow {
  const p = useDb().select().from(tables.projects).where(eq(tables.projects.id, projectId)).get()
  if (!p) throw createError({ statusCode: 404, message: '项目不存在' })
  return p
}

export function getChapter(chapterId: string): ChapterRow {
  const c = useDb().select().from(tables.chapters).where(eq(tables.chapters.id, chapterId)).get()
  if (!c) throw createError({ statusCode: 404, message: '章节不存在' })
  return c
}

export function getAgent(agentId: string | null | undefined): AgentRow | undefined {
  if (!agentId) return undefined
  return useDb().select().from(tables.agents).where(eq(tables.agents.id, agentId)).get()
}

export function getRoleAgent(projectId: string, role: 'editor' | 'summarizer'): AgentRow | undefined {
  return useDb().select().from(tables.agents)
    .where(and(eq(tables.agents.projectId, projectId), eq(tables.agents.role, role)))
    .get()
}

export function listChapters(projectId: string) {
  return useDb().select().from(tables.chapters).where(eq(tables.chapters.projectId, projectId)).orderBy(asc(tables.chapters.index)).all()
}

export function getVersion(versionId: string | null | undefined): VersionRow | undefined {
  if (!versionId) return undefined
  return useDb().select().from(tables.chapterVersions).where(eq(tables.chapterVersions.id, versionId)).get()
}

/** 一次性加载生成提示词所需的全部项目数据 */
export function loadProjectContext(projectId: string) {
  const db = useDb()
  const project = getProject(projectId)
  return {
    project,
    characters: db.select().from(tables.characters).where(eq(tables.characters.projectId, projectId)).orderBy(asc(tables.characters.sortOrder), asc(tables.characters.createdAt)).all(),
    lore: db.select().from(tables.loreEntries).where(eq(tables.loreEntries.projectId, projectId)).orderBy(asc(tables.loreEntries.sortOrder)).all(),
    agents: db.select().from(tables.agents).where(eq(tables.agents.projectId, projectId)).orderBy(asc(tables.agents.createdAt)).all(),
    chapters: listChapters(projectId),
    models: db.select({ id: tables.models.id, label: tables.models.label, modelId: tables.models.modelId, providerName: tables.providers.name })
      .from(tables.models).innerJoin(tables.providers, eq(tables.models.providerId, tables.providers.id)).all()
  }
}

export type ProjectContext = ReturnType<typeof loadProjectContext>