import type { FastifyInstance } from 'fastify';
import { z } from 'zod';
import { eq, and, or, ilike, sql } from 'drizzle-orm';
import * as XLSX from 'xlsx';
import { students } from '../../db/schema/index.js';

const studentBody = z.object({
  studentName: z.string().min(1).max(100),
  fatherName: z.string().optional(),
  mobile: z.string().optional(),
  biometricId: z.string().optional(),
  rfidCardNo: z.string().optional(),
  divisionId: z.string().uuid().nullable().optional(),
  subDivisionId: z.string().uuid().nullable().optional(),
  photoUrl: z.string().url().optional(),
});

export default async function studentRoutes(fastify: FastifyInstance) {
  const guard = [fastify.authenticate];
  const adminGuard = [fastify.authenticate, fastify.authorize(['SUPER_ADMIN', 'SCHOOL_ADMIN'])];

  fastify.get('/', { preHandler: guard }, async (request) => {
    const tenantId = request.user.tenantId!;
    const { q, divisionId, subDivisionId, page = '1', limit = '50' } = request.query as Record<string, string>;
    const offset = (parseInt(page) - 1) * parseInt(limit);

    const conditions = [eq(students.tenantId, tenantId), eq(students.status, 'ACTIVE')];
    if (divisionId) conditions.push(eq(students.divisionId, divisionId));
    if (subDivisionId) conditions.push(eq(students.subDivisionId, subDivisionId));
    if (q) {
      conditions.push(
        sql`(${ilike(students.studentName, `%${q}%`)} OR ${ilike(students.biometricId, `%${q}%`)} OR ${ilike(students.mobile, `%${q}%`)})`
      );
    }

    const rows = await fastify.db
      .select()
      .from(students)
      .where(and(...conditions))
      .limit(parseInt(limit))
      .offset(offset)
      .orderBy(students.studentName);

    return rows;
  });

  fastify.get('/search', { preHandler: guard }, async (request) => {
    const tenantId = request.user.tenantId!;
    const { q = '' } = request.query as { q: string };

    return fastify.db
      .select()
      .from(students)
      .where(
        and(
          eq(students.tenantId, tenantId),
          or(
            ilike(students.studentName, `%${q}%`),
            ilike(students.biometricId, `%${q}%`),
            ilike(students.rfidCardNo, `%${q}%`)
          )
        )
      )
      .limit(20);
  });

  fastify.get('/:id', { preHandler: guard }, async (request, reply) => {
    const { id } = request.params as { id: string };
    const tenantId = request.user.tenantId!;
    const [row] = await fastify.db.select().from(students)
      .where(and(eq(students.id, id), eq(students.tenantId, tenantId))).limit(1);
    if (!row) return reply.code(404).send({ error: 'Student not found' });
    return row;
  });

  fastify.post('/', { preHandler: adminGuard }, async (request, reply) => {
    const body = studentBody.parse(request.body);
    const tenantId = request.user.tenantId!;

    if (body.biometricId) {
      const dup = await fastify.db.select({ id: students.id }).from(students)
        .where(and(eq(students.tenantId, tenantId), eq(students.biometricId, body.biometricId))).limit(1);
      if (dup.length > 0) return reply.code(409).send({ error: 'Biometric ID already assigned to another student' });
    }

    const [row] = await fastify.db.insert(students).values({ ...body, tenantId }).returning();
    return reply.code(201).send(row);
  });

  fastify.patch('/:id', { preHandler: adminGuard }, async (request, reply) => {
    const { id } = request.params as { id: string };
    const body = studentBody.partial().parse(request.body);
    const tenantId = request.user.tenantId!;

    const [updated] = await fastify.db
      .update(students)
      .set({ ...body, updatedAt: new Date() })
      .where(and(eq(students.id, id), eq(students.tenantId, tenantId)))
      .returning();

    if (!updated) return reply.code(404).send({ error: 'Student not found' });
    return updated;
  });

  fastify.delete('/:id', { preHandler: adminGuard }, async (request, reply) => {
    const { id } = request.params as { id: string };
    const tenantId = request.user.tenantId!;

    const [updated] = await fastify.db
      .update(students)
      .set({ status: 'INACTIVE', updatedAt: new Date() })
      .where(and(eq(students.id, id), eq(students.tenantId, tenantId)))
      .returning({ id: students.id });

    if (!updated) return reply.code(404).send({ error: 'Student not found' });
    return reply.code(204).send();
  });

  // Excel import
  fastify.post('/import', { preHandler: adminGuard }, async (request, reply) => {
    const tenantId = request.user.tenantId!;
    const file = await request.file();
    if (!file) return reply.code(400).send({ error: 'No file uploaded' });

    const buffer = await file.toBuffer();
    const workbook = XLSX.read(buffer, { type: 'buffer' });
    const sheet = workbook.Sheets[workbook.SheetNames[0]];
    const rows = XLSX.utils.sheet_to_json<Record<string, string>>(sheet);

    const errors: { row: number; error: string }[] = [];
    const toInsert = [];

    for (let i = 0; i < rows.length; i++) {
      const r = rows[i];
      if (!r['Student Name']) {
        errors.push({ row: i + 2, error: 'Student Name is required' });
        continue;
      }
      toInsert.push({
        tenantId,
        studentName: String(r['Student Name']).trim(),
        fatherName: r['Father Name'] ? String(r['Father Name']).trim() : null,
        mobile: r['Mobile'] ? String(r['Mobile']).trim() : null,
        biometricId: r['Biometric ID'] ? String(r['Biometric ID']).trim() : null,
        rfidCardNo: r['RFID Card No'] ? String(r['RFID Card No']).trim() : null,
      });
    }

    let inserted = 0;
    if (toInsert.length > 0) {
      const result = await fastify.db.insert(students).values(toInsert).returning({ id: students.id });
      inserted = result.length;
    }

    return { inserted, errors };
  });
}