import type { Request, Response } from "express"
import pool from "../config/database"
import type { RowDataPacket } from "mysql2"

interface Student extends RowDataPacket {
  id: number
  nom: string
  classe: string
  option: string
  mtr: string
  nni: string
  téléphone: string
  genre: "M" | "F"
  created_at: string
  updated_at: string
}

// Get all students
export const getAllStudents = async (req: Request, res: Response) => {
  try {
    const [rows] = await pool.query<Student[]>("SELECT * FROM students ORDER BY id ASC")
    res.json(rows)
  } catch (error) {
    console.error("Erreur de récupération des étudiants:", error)
    res.status(500).json({ error: "Erreur serveur interne" })
  }
}

// Get students by class and option
export const getStudentsByClassAndOption = async (req: Request, res: Response) => {
  try {
    const { classe, option } = req.query
    if (!classe || !option) {
      return res.status(400).json({ error: "Classe et option sont requis" })
    }
    const [rows] = await pool.query<Student[]>(
      "SELECT * FROM students WHERE classe = ? AND `option` = ? ORDER BY COALESCE(nom, '') ASC",
      [classe, option]
    )
    res.json(rows)
  } catch (error: any) {
    console.error("Erreur de récupération des étudiants par classe et option:", error.message, error.stack)
    res.status(500).json({ error: "Échec de la récupération des étudiants", details: error.message })
  }
}

// Get a single student
export const getStudentById = async (req: Request, res: Response) => {
  try {
    const { id } = req.params
    const [rows] = await pool.query<Student[]>("SELECT * FROM students WHERE id = ?", [id])
    if (rows.length === 0) return res.status(404).json({ error: "Étudiant non trouvé" })
    res.json(rows[0])
  } catch (error) {
    console.error("Erreur de récupération de l'étudiant:", error)
    res.status(500).json({ error: "Erreur serveur interne" })
  }
}

// Create student
export const createStudent = async (req: Request, res: Response) => {
  try {
    const { nom, classe, option, mtr, nni, téléphone, genre } = req.body

    // Check for duplicates
    const [existing] = await pool.query<Student[]>(
      "SELECT mtr, nni FROM students WHERE mtr = ? OR nni = ?",
      [mtr, nni]
    )
    if (existing.length > 0) {
      return res.status(400).json({
        error: "Un étudiant avec ce MTR ou NNI existe déjà",
        details: `MTR: ${mtr}, NNI: ${nni}`,
      })
    }

    const [result] = await pool.query(
      "INSERT INTO students (nom, classe, `option`, mtr, nni, téléphone, genre) VALUES (?, ?, ?, ?, ?, ?, ?)",
      [nom, classe, option, mtr, nni, téléphone, genre]
    )
    const insertId = (result as any).insertId
    const [rows] = await pool.query<Student[]>("SELECT * FROM students WHERE id = ?", [insertId])
    res.status(201).json(rows[0])
  } catch (error: any) {
    console.error("Erreur de création de l'étudiant:", error.message, error.stack)
    res.status(500).json({ error: "Erreur serveur interne", details: error.message })
  }
}

// Update student
export const updateStudent = async (req: Request, res: Response) => {
  try {
    const { id } = req.params
    const { nom, classe, option, mtr, nni, téléphone, genre } = req.body

    // Check for duplicates (excluding current student)
    const [existing] = await pool.query<Student[]>(
      "SELECT mtr, nni FROM students WHERE (mtr = ? OR nni = ?) AND id != ?",
      [mtr, nni, id]
    )
    if (existing.length > 0) {
      return res.status(400).json({
        error: "Un autre étudiant avec ce MTR ou NNI existe déjà",
        details: `MTR: ${mtr}, NNI: ${nni}`,
      })
    }

    await pool.query(
      "UPDATE students SET nom = ?, classe = ?, option = ?, mtr = ?, nni = ?, téléphone = ?, genre = ?, updated_at = NOW() WHERE id = ?",
      [nom, classe, option, mtr, nni, téléphone, genre, id]
    )
    const [rows] = await pool.query<Student[]>("SELECT * FROM students WHERE id = ?", [id])
    res.json(rows[0])
  } catch (error: any) {
    console.error("Erreur de mise à jour de l'étudiant:", error.message, error.stack)
    res.status(500).json({ error: "Erreur serveur interne", details: error.message })
  }
}

// Delete student
export const deleteStudent = async (req: Request, res: Response) => {
  try {
    const { id } = req.params
    await pool.query("DELETE FROM students WHERE id = ?", [id])
    res.json({ message: "Étudiant supprimé avec succès" })
  } catch (error) {
    console.error("Erreur de suppression de l'étudiant:", error)
    res.status(500).json({ error: "Erreur serveur interne" })
  }
}