import type { Response } from "express";
import pool from "../config/database";
import type { AuthRequest } from "../middleware/auth.middleware";
import type { RowDataPacket } from "mysql2";
import { format } from "date-fns";

interface Attendance extends RowDataPacket {
  id: number;
  student_id: number;
  observer_id: number;
  date: Date;
  status: "present" | "absent";
  justification: string | null;
  hours_absent: number | null;
  created_at: Date;
  updated_at: Date;
  student_name: string;
  classe: string;
  option: string;
  observer_name: string;
}

export const recordAttendance = async (req: AuthRequest, res: Response) => {
  try {
    const { date, class: studentClass, option, attendance } = req.body;
    const observerId = req.user?.id;

    if (!date || !studentClass || !option || !attendance || !Array.isArray(attendance)) {
      return res.status(400).json({ error: "Invalid request data" });
    }

    // Delete existing attendance for this date, class, and option
    await pool.query(
      "DELETE FROM attendance WHERE date = ? AND student_id IN (SELECT id FROM students WHERE classe = ? AND `option` = ?)",
      [date, studentClass, option]
    );

    // Insert new attendance records
    for (const record of attendance) {
      if (record.status === "absent") {
        await pool.query(
          "INSERT INTO attendance (student_id, observer_id, date, status, justification, hours_absent) VALUES (?, ?, ?, ?, ?, ?)",
          [record.studentId, observerId, date, record.status, record.justification || null, record.hours_absent || null]
        );
      } else {
        await pool.query("INSERT INTO attendance (student_id, observer_id, date, status) VALUES (?, ?, ?, ?)", [
          record.studentId,
          observerId,
          date,
          record.status,
        ]);
      }
    }

    res.json({ message: "Attendance recorded successfully" });
  } catch (error) {
    console.error("Record attendance error:", error);
    res.status(500).json({ error: "Internal server error" });
  }
};

export const getAttendance = async (req: AuthRequest, res: Response) => {
  try {
    const { startDate, endDate, class: classFilter, status, search } = req.query;

    let query = `
      SELECT 
        a.id,
        a.student_id,
        a.observer_id,
        a.date,
        a.status,
        a.justification,
        a.hours_absent,
        a.created_at,
        a.updated_at,
        s.nom AS student_name,
        s.classe,
        s.option,
        u.name AS observer_name
      FROM attendance a
      JOIN students s ON a.student_id = s.id
      JOIN users u ON a.observer_id = u.id
      WHERE 1=1
    `;
    const params: any[] = [];

    if (startDate) {
      query += " AND a.date >= ?";
      params.push(startDate);
    }

    if (endDate) {
      query += " AND a.date <= ?";
      params.push(endDate);
    }

    if (classFilter) {
      query += " AND s.classe = ?";
      params.push(classFilter);
    }

    if (status) {
      query += " AND a.status = ?";
      params.push(status);
    }

    if (search) {
      query += " AND (s.nom LIKE ? OR s.classe LIKE ?)";
      const searchTerm = `%${search}%`;
      params.push(searchTerm, searchTerm);
    }

    query += " ORDER BY a.date DESC, s.nom";

    const [rows] = await pool.query<Attendance[]>(query, params);
    res.json(rows);
  } catch (error) {
    console.error("Get attendance error:", error);
    res.status(500).json({ error: "Internal server error" });
  }
};

export const updateAttendance = async (req: AuthRequest, res: Response) => {
  try {
    const { id } = req.params;
    const { justification, hoursAbsent } = req.body;

    const [existing] = await pool.query<Attendance[]>("SELECT id, status FROM attendance WHERE id = ?", [id]);

    if (existing.length === 0) {
      return res.status(404).json({ error: "Attendance record not found" });
    }

    if (existing[0].status === "absent") {
      await pool.query("UPDATE attendance SET justification = ?, hours_absent = ? WHERE id = ?", [
        justification || null,
        hoursAbsent || null,
        id,
      ]);
    } else {
      await pool.query("UPDATE attendance SET justification = NULL, hours_absent = NULL WHERE id = ?", [id]);
    }

    const [updated] = await pool.query<Attendance[]>(
      `SELECT 
        a.id,
        a.student_id,
        a.observer_id,
        a.date,
        a.status,
        a.justification,
        a.hours_absent,
        a.created_at,
        a.updated_at,
        s.nom AS student_name,
        s.classe,
        s.option,
        u.name AS observer_name
      FROM attendance a
      JOIN students s ON a.student_id = s.id
      JOIN users u ON a.observer_id = u.id
      WHERE a.id = ?`,
      [id]
    );

    res.json(updated[0]);
  } catch (error) {
    console.error("Update attendance error:", error);
    res.status(500).json({ error: "Internal server error" });
  }
};

export const deleteAttendance = async (req: AuthRequest, res: Response) => {
  try {
    const { id } = req.params;

    const [existing] = await pool.query<Attendance[]>("SELECT id FROM attendance WHERE id = ?", [id]);

    if (existing.length === 0) {
      return res.status(404).json({ error: "Attendance record not found" });
    }

    await pool.query("DELETE FROM attendance WHERE id = ?", [id]);

    res.json({ message: "Attendance record deleted successfully" });
  } catch (error) {
    console.error("Delete attendance error:", error);
    res.status(500).json({ error: "Internal server error" });
  }
};

export const getClassStatus = async (req: AuthRequest, res: Response) => {
  try {
    // Use UTC or adjust to your server's time zone
    const today = format(new Date(), "yyyy-MM-dd");

    const [classRows] = await pool.query<RowDataPacket[]>("SELECT name FROM classes");
    const classes = classRows.map((row) => row.name);
    const timeSlots = [
      { name: "08:00-10:00", start: 8, end: 10 },
      { name: "10:00-12:00", start: 10, end: 12 },
      { name: "12:00-14:00", start: 12, end: 14 },
    ];

    const result = await Promise.all(
      classes.map(async (cls) => {
        const statusBySlot = await Promise.all(
          timeSlots.map(async (slot) => {
            const [records] = await pool.query<Attendance[]>(
              `
              SELECT a.id, a.date, s.classe
              FROM attendance a
              JOIN students s ON a.student_id = s.id
              WHERE s.classe = ?
                AND a.date >= ? AND a.date < ?
                AND HOUR(a.date) >= ? AND HOUR(a.date) < ?
              LIMIT 1
              `,
              [
                cls,
                `${today} 00:00:00`,
                `${today} 23:59:59`,
                slot.start,
                slot.end,
              ]
            );
            return { name: slot.name, checked: records.length > 0 ? "Oui" : "Non" };
          })
        );
        return { class: cls, status: statusBySlot };
      })
    );

    res.json(result);
  } catch (error) {
    console.error("Get class status error:", error);
    res.status(500).json({ error: "Internal server error", details: error });
  }
};