import "server-only";
import { and, asc, desc, eq, ilike, or, sql } from "drizzle-orm";
import { db } from "@/lib/db/client";
import { orthoprepQuestions } from "@/lib/db/schema";

export async function listPublishedQuestions() {
  return db
    .select()
    .from(orthoprepQuestions)
    .where(eq(orthoprepQuestions.status, "published"))
    .orderBy(asc(orthoprepQuestions.number));
}

export async function listPublishedQuestionsByPart(part: string) {
  return db
    .select()
    .from(orthoprepQuestions)
    .where(
      and(
        eq(orthoprepQuestions.status, "published"),
        eq(orthoprepQuestions.part, part),
      ),
    )
    .orderBy(asc(orthoprepQuestions.number));
}

export async function getPartsWithCounts() {
  const rows = await db
    .select({
      part: orthoprepQuestions.part,
      count: sql<number>`count(*)::int`,
    })
    .from(orthoprepQuestions)
    .where(eq(orthoprepQuestions.status, "published"))
    .groupBy(orthoprepQuestions.part)
    .orderBy(asc(orthoprepQuestions.part));
  return rows.map((r) => ({ part: r.part, count: Number(r.count) }));
}

export async function getQuestionByNumber(num: number) {
  const [row] = await db
    .select()
    .from(orthoprepQuestions)
    .where(eq(orthoprepQuestions.number, num))
    .limit(1);
  return row ?? null;
}

export async function getQuestionForAdmin(id: string) {
  const [row] = await db
    .select()
    .from(orthoprepQuestions)
    .where(eq(orthoprepQuestions.id, id))
    .limit(1);
  return row ?? null;
}

export async function listQuestionsForAdmin({
  q,
  part,
  status,
  limit = 100,
  offset = 0,
}: {
  q?: string;
  part?: string;
  status?: "draft" | "published" | "archived" | "all";
  limit?: number;
  offset?: number;
} = {}) {
  const conditions = [];
  if (status && status !== "all") {
    conditions.push(eq(orthoprepQuestions.status, status));
  }
  if (part && part.length > 0) {
    conditions.push(eq(orthoprepQuestions.part, part));
  }
  if (q && q.trim().length > 0) {
    const pattern = `%${q.trim()}%`;
    conditions.push(
      or(
        ilike(orthoprepQuestions.stem, pattern),
        ilike(orthoprepQuestions.explanation, pattern),
        ilike(orthoprepQuestions.chapter, pattern),
      )!,
    );
  }
  const whereClause = conditions.length > 0 ? and(...conditions) : undefined;

  const [rows, totalRow] = await Promise.all([
    db
      .select()
      .from(orthoprepQuestions)
      .where(whereClause)
      .orderBy(desc(orthoprepQuestions.updatedAt))
      .limit(limit)
      .offset(offset),
    db
      .select({ count: sql<number>`count(*)::int` })
      .from(orthoprepQuestions)
      .where(whereClause),
  ]);
  return { rows, total: Number(totalRow[0]?.count ?? 0) };
}

export async function getQuestionKpis() {
  const [total, published, draft] = await Promise.all([
    db
      .select({ count: sql<number>`count(*)::int` })
      .from(orthoprepQuestions),
    db
      .select({ count: sql<number>`count(*)::int` })
      .from(orthoprepQuestions)
      .where(eq(orthoprepQuestions.status, "published")),
    db
      .select({ count: sql<number>`count(*)::int` })
      .from(orthoprepQuestions)
      .where(eq(orthoprepQuestions.status, "draft")),
  ]);
  return {
    total: Number(total[0]?.count ?? 0),
    published: Number(published[0]?.count ?? 0),
    draft: Number(draft[0]?.count ?? 0),
  };
}
