import type { Response } from "express"
import pool from "../config/database"
import type { AuthRequest } from "../middleware/auth.middleware"
import type { RowDataPacket } from "mysql2"
import ExcelJS from "exceljs"

interface AbsenceReport extends RowDataPacket {
  student_id: number
  mtr: string
  nom: string
  classe: string
  option: string
  total_absences: number
  total_hours: number
  dates: string
}

interface AttendanceDetail extends RowDataPacket {
  date: Date
  justification: string | null
  hours_absent: number | null
}

export const getAbsenceReport = async (req: AuthRequest, res: Response) => {
  try {
    const { studentId, class: classFilter, option: optionFilter, startDate, endDate } = req.query

    let query = `
      SELECT 
        s.id as student_id,
        s.mtr,
        s.nom,
        s.classe,
        s.option,
        COUNT(a.id) as total_absences,
        SUM(COALESCE(a.hours_absent, 0)) as total_hours,
        GROUP_CONCAT(DATE_FORMAT(a.date, '%Y-%m-%d') ORDER BY a.date) as dates
      FROM students s
      LEFT JOIN attendance a ON s.id = a.student_id AND a.status = 'absent'
    `

    const params: any[] = []
    const conditions: string[] = []

    if (studentId) {
      conditions.push("s.id = ?")
      params.push(studentId)
    }

    if (classFilter) {
      conditions.push("s.classe = ?")
      params.push(classFilter)
    }

    if (optionFilter) {
      conditions.push("s.option = ?")
      params.push(optionFilter)
    }

    if (startDate) {
      conditions.push("(a.date IS NULL OR a.date >= ?)")
      params.push(startDate)
    }

    if (endDate) {
      conditions.push("(a.date IS NULL OR a.date <= ?)")
      params.push(endDate)
    }

    if (conditions.length > 0) {
      query += " WHERE " + conditions.join(" AND ")
    }

    query += " GROUP BY s.id ORDER BY total_absences DESC, s.nom"

    const [rows] = await pool.query<AbsenceReport[]>(query, params)
    res.json(rows)
  } catch (error) {
    console.error("Get absence report error:", error)
    res.status(500).json({ error: "Erreur interne du serveur" })
  }
}

export const getStudentAbsenceDetails = async (req: AuthRequest, res: Response) => {
  try {
    const { studentId } = req.params
    const { startDate, endDate } = req.query

    let query = `
      SELECT date, justification, hours_absent
      FROM attendance
      WHERE student_id = ? AND status = 'absent'
    `

    const params: any[] = [studentId]

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

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

    query += " ORDER BY date DESC"

    const [rows] = await pool.query<AttendanceDetail[]>(query, params)
    res.json(rows)
  } catch (error) {
    console.error("Get student absence details error:", error)
    res.status(500).json({ error: "Erreur interne du serveur" })
  }
}

export const generateExcelReport = async (req: AuthRequest, res: Response) => {
  try {
    const { studentId, class: classFilter, option: optionFilter, startDate, endDate } = req.query

    // Get report data
    let query = `
      SELECT 
        s.id as student_id,
        s.mtr,
        s.nom,
        s.classe,
        s.option,
        COUNT(a.id) as total_absences,
        SUM(COALESCE(a.hours_absent, 0)) as total_hours
      FROM students s
      LEFT JOIN attendance a ON s.id = a.student_id AND a.status = 'absent'
    `

    const params: any[] = []
    const conditions: string[] = []

    if (studentId) {
      conditions.push("s.id = ?")
      params.push(studentId)
    }

    if (classFilter) {
      conditions.push("s.classe = ?")
      params.push(classFilter)
    }

    if (optionFilter) {
      conditions.push("s.option = ?")
      params.push(optionFilter)
    }

    if (startDate) {
      conditions.push("(a.date IS NULL OR a.date >= ?)")
      params.push(startDate)
    }

    if (endDate) {
      conditions.push("(a.date IS NULL OR a.date <= ?)")
      params.push(endDate)
    }

    if (conditions.length > 0) {
      query += " WHERE " + conditions.join(" AND ")
    }

    query += " GROUP BY s.id ORDER BY total_absences DESC, s.nom"

    const [students] = await pool.query<AbsenceReport[]>(query, params)

    // Create Excel workbook
    const workbook = new ExcelJS.Workbook()
    const worksheet = workbook.addWorksheet("Rapport d'absences")

    // Define columns
    worksheet.columns = [
      { header: "Matricule", key: "mtr", width: 15 },
      { header: "Nom", key: "nom", width: 25 },
      { header: "Classe", key: "classe", width: 15 },
      { header: "Total des absences", key: "absences", width: 15 },
      { header: "Décision", key: "decision", width: 20 },
    ]

    // Add data
    students.forEach(student => {
        // assuming 6 hours = 1 day
        const dayHours = 6;
        const totalDays = Math.floor(student.total_hours / dayHours); // full days
        const remainingHours = student.total_hours % dayHours;       // leftover hours

        // build absences string
        const absences = 
            `${totalDays > 0 ? totalDays + (totalDays > 1 ? " jours" : " jour") : ""}` +
            `${totalDays > 0 && remainingHours > 0 ? " " : ""}` +
            `${remainingHours > 0 ? remainingHours + (remainingHours > 1 ? " heures" : " heure") : (totalDays === 0 ? "0 heure" : "")}`;


        worksheet.addRow({
          mtr: student.mtr,
          nom: student.nom,
          classe: student.classe,
          absences: absences,
          decision: ""
        });
    });


    // Style header
    worksheet.getRow(1).font = { bold: true }
    worksheet.getRow(1).fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FFD3D3D3' }
    }

    // Generate Excel file
    res.setHeader(
      "Content-Type",
      "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    )
    res.setHeader(
      "Content-Disposition",
      "attachment; filename=rapport-absences.xlsx"
    )

    await workbook.xlsx.write(res)
    res.end()
  } catch (error) {
    console.error("Generate Excel report error:", error)
    res.status(500).json({ error: "Erreur interne du serveur" })
  }
}