import { NextRequest } from "next/server";
import * as XLSX from "xlsx";
import { db } from "@/lib/db";
import { requirePageRole, resolveSchoolId } from "@/lib/auth";
import { handleApiError } from "@/lib/api-helpers";
import { effectiveFee, GRADES, LEGACY_GRADE_LABELS } from "@/lib/constants";
import { getCustomGradeLabels } from "@/lib/grade-helpers";

/** تصدير جميع الطلبة النشطين إلى ملف Excel بأعمدة عربية واتجاه RTL (نطاق المدرسة) */
export async function GET(req: NextRequest) {
  try {
    const user = await requirePageRole("students", "ADMIN", "ACCOUNTANT", "STAFF");
    const schoolId = await resolveSchoolId(user, req.nextUrl.searchParams.get("schoolId"));

    const [students, customLabels] = await Promise.all([
      db.student.findMany({
        where: { isActive: true, schoolId },
        orderBy: [{ grade: "asc" }, { classroom: "asc" }, { nameAr: "asc" }],
        include: { payments: { select: { amount: true } } },
      }),
      getCustomGradeLabels(schoolId),
    ]);

    // خريطة كود الصف -> الاسم الظاهر (ثابت + مخصص + قديم مؤرشف)
    const labelMap: Record<string, string> = { ...LEGACY_GRADE_LABELS };
    for (const g of GRADES) labelMap[g.code] = g.labelAr;
    for (const [code, label] of Object.entries(customLabels)) labelMap[code] = label;

    const rows = students.map((s) => {
      const paid = s.payments.reduce((sum, p) => sum + p.amount, 0);
      const eff = effectiveFee(s.totalFee, s.discountPercent);
      return {
        "الكود": s.studentCode,
        "الاسم": s.nameAr,
        "الاسم بالإنجليزية": s.nameEn,
        "الصف": labelMap[s.grade] ?? s.grade,
        "الشعبة": s.classroom,
        "ولي الأمر": s.guardianName,
        "هاتف ولي الأمر": s.guardianPhone,
        "العنوان": s.guardianAddress ?? "",
        "القسط الكلي": s.totalFee,
        "الخصم %": s.discountPercent,
        "الصافي": eff,
        "المدفوع": paid,
        "المتبقي": Math.max(0, eff - paid),
        "تاريخ التسجيل": s.registeredAt.toISOString().slice(0, 10),
        "ملاحظات": s.notes ?? "",
      };
    });

    const ws = XLSX.utils.json_to_sheet(rows);
    ws["!Views"] = [{ RTL: true }];
    ws["!cols"] = [
      { wch: 15 }, { wch: 34 }, { wch: 24 }, { wch: 22 }, { wch: 9 },
      { wch: 28 }, { wch: 16 }, { wch: 26 }, { wch: 12 }, { wch: 9 },
      { wch: 12 }, { wch: 12 }, { wch: 12 }, { wch: 13 }, { wch: 26 },
    ];
    const wb = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(wb, ws, "الطلبة");

    const buf = XLSX.write(wb, { type: "buffer", bookType: "xlsx" }) as Buffer;
    const today = new Date().toISOString().slice(0, 10);
    return new Response(new Uint8Array(buf), {
      headers: {
        "Content-Type": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        "Content-Disposition": `attachment; filename="students-${today}.xlsx"`,
        "Cache-Control": "no-store",
      },
    });
  } catch (err) {
    return handleApiError(err);
  }
}