import type { FastifyInstance } from 'fastify';
import { eq, and, desc, sql } from 'drizzle-orm';
import { attendanceLogs, attendanceRecords, students } from '../../db/schema/index.js';

export default async function attendanceRoutes(fastify: FastifyInstance) {
  const guard = [fastify.authenticate];

  // Today's dashboard summary
  fastify.get('/today', { preHandler: guard }, async (request) => {
    const tenantId = request.user.tenantId!;
    const today = new Date().toISOString().split('T')[0];

    const [stats] = await fastify.db
      .select({
        totalStudents: sql<number>`count(distinct ${students.id})::int`,
        presentToday: sql<number>`count(distinct case when ${attendanceRecords.status} = 'PRESENT' then ${attendanceRecords.studentId} end)::int`,
        lateToday: sql<number>`count(distinct case when ${attendanceRecords.status} = 'LATE' then ${attendanceRecords.studentId} end)::int`,
        currentlyInside: sql<number>`count(distinct case when ${attendanceRecords.isCurrentlyInside} = true then ${attendanceRecords.studentId} end)::int`,
      })
      .from(students)
      .leftJoin(
        attendanceRecords,
        and(
          eq(attendanceRecords.studentId, students.id),
          eq(attendanceRecords.attendanceDate, today),
          eq(attendanceRecords.tenantId, tenantId)
        )
      )
      .where(and(eq(students.tenantId, tenantId), eq(students.status, 'ACTIVE')));

    const recentPunches = await fastify.db
      .select({
        id: attendanceLogs.id,
        studentId: attendanceLogs.studentId,
        studentName: students.studentName,
        punchType: attendanceLogs.punchType,
        scanTime: attendanceLogs.scanTime,
        scanSequence: attendanceLogs.scanSequence,
      })
      .from(attendanceLogs)
      .innerJoin(students, eq(students.id, attendanceLogs.studentId))
      .where(and(eq(attendanceLogs.tenantId, tenantId), eq(attendanceLogs.attendanceDate, today)))
      .orderBy(desc(attendanceLogs.scanTime))
      .limit(20);

    return {
      ...stats,
      absentToday: (stats?.totalStudents ?? 0) - (stats?.presentToday ?? 0) - (stats?.lateToday ?? 0),
      recentPunches,
      date: today,
    };
  });

  // Attendance logs with filters
  fastify.get('/logs', { preHandler: guard }, async (request) => {
    const tenantId = request.user.tenantId!;
    const { date, studentId, page = '1', limit = '100' } = request.query as Record<string, string>;
    const offset = (parseInt(page) - 1) * parseInt(limit);

    const today = new Date().toISOString().split('T')[0];
    const filterDate = date ?? today;

    const conditions = [
      eq(attendanceLogs.tenantId, tenantId),
      eq(attendanceLogs.attendanceDate, filterDate),
    ];
    if (studentId) conditions.push(eq(attendanceLogs.studentId, studentId));

    const logs = await fastify.db
      .select({
        id: attendanceLogs.id,
        studentId: attendanceLogs.studentId,
        studentName: students.studentName,
        deviceId: attendanceLogs.deviceId,
        scanTime: attendanceLogs.scanTime,
        scanSequence: attendanceLogs.scanSequence,
        punchType: attendanceLogs.punchType,
      })
      .from(attendanceLogs)
      .innerJoin(students, eq(students.id, attendanceLogs.studentId))
      .where(and(...conditions))
      .orderBy(desc(attendanceLogs.scanTime))
      .limit(parseInt(limit))
      .offset(offset);

    return logs;
  });

  // Full punch timeline for a specific student on a date
  fastify.get('/student/:id', { preHandler: guard }, async (request, reply) => {
    const { id } = request.params as { id: string };
    const tenantId = request.user.tenantId!;
    const { date } = request.query as { date?: string };
    const filterDate = date ?? new Date().toISOString().split('T')[0];

    const [summary] = await fastify.db
      .select()
      .from(attendanceRecords)
      .where(
        and(
          eq(attendanceRecords.tenantId, tenantId),
          eq(attendanceRecords.studentId, id),
          eq(attendanceRecords.attendanceDate, filterDate)
        )
      )
      .limit(1);

    const punches = await fastify.db
      .select()
      .from(attendanceLogs)
      .where(
        and(
          eq(attendanceLogs.tenantId, tenantId),
          eq(attendanceLogs.studentId, id),
          eq(attendanceLogs.attendanceDate, filterDate)
        )
      )
      .orderBy(attendanceLogs.scanSequence);

    return { summary: summary ?? null, punches };
  });
}