import "server-only";
import { and, asc, eq, isNull } from "drizzle-orm";
import { connection } from "next/server";
import { getDb } from "@/db/client";
import { guides, teamCompositions, teamConditions, teamMembers } from "@/db/schema";
import type { TeamGuide } from "@/lib/team-guides";

export async function getTeamGuides(): Promise<TeamGuide[]> {
  await connection();
  if (!process.env.DATABASE_URL) return [];
  try {
    const rows = await getDb().select({
      guideId: guides.id,
      guideCharacterId: guides.characterKey,
      guideName: guides.name,
      teamId: teamCompositions.id,
      teamTitle: teamCompositions.title,
      combo: teamCompositions.combo,
      conditionName: teamConditions.name,
      position: teamMembers.position,
      characterId: teamMembers.characterKey,
      weaponId: teamMembers.weaponKey,
      artifactOneId: teamMembers.artifactKey,
      artifactTwoId: teamMembers.secondArtifactKey,
      constellation: teamMembers.constellation,
      refinement: teamMembers.refinement,
    }).from(guides)
      .innerJoin(teamCompositions, eq(teamCompositions.guideId, guides.id))
      .innerJoin(teamMembers, eq(teamMembers.teamId, teamCompositions.id))
      .leftJoin(teamConditions, eq(teamConditions.id, teamCompositions.conditionId))
      .where(and(eq(guides.isPublic, true), isNull(guides.trashedAt)))
      .orderBy(asc(guides.sortOrder), asc(guides.name), asc(teamConditions.sortOrder), asc(teamCompositions.sortOrder), asc(teamMembers.position));

    const result: TeamGuide[] = [];
    for (const row of rows) {
      let guide = result.at(-1);
      if (!guide || guide.id !== row.guideId) {
        guide = { id: row.guideId, characterId: row.guideCharacterId, name: row.guideName, teams: [] };
        result.push(guide);
      }
      let team = guide.teams.at(-1);
      if (!team || team.id !== row.teamId) {
        team = { id: row.teamId, title: row.teamTitle, conditionName: row.conditionName, combo: row.combo, members: [] };
        guide.teams.push(team);
      }
      team.members.push({
        position: row.position,
        characterId: row.characterId,
        weaponId: row.weaponId,
        artifactOneId: row.artifactOneId,
        artifactTwoId: row.artifactTwoId,
        constellation: row.constellation,
        refinement: row.refinement,
      });
    }
    return result;
  } catch (error) {
    console.error("Team guides unavailable", error);
    return [];
  }
}