smanab/Sistem-Pelanggaran-Siswa/routes/achievement-criteria.js

import express from 'express';
import pool from '../database.js';

const router = express.Router();

// GET all achievement criteria
router.get('/', async (req, res) => {
    try {
        const [rows] = await pool.execute(`
            SELECT id, level, achievement_type, participant_type, group_size, score
            FROM achievement_criteria 
            ORDER BY level, achievement_type, participant_type
        `);
        res.json(rows);
    } catch (error) {
        console.error('Error fetching achievement criteria:', error);
        res.status(500).json({ error: 'Failed to fetch achievement criteria' });
    }
});

// POST create new achievement criteria
router.post('/', async (req, res) => {
    try {
        const { level, achievementType, participantType, groupSize, score } = req.body;

        // Check if combination already exists
        const [existing] = await pool.execute(`
            SELECT id FROM achievement_criteria 
            WHERE level = ? AND achievement_type = ? AND participant_type = ? AND group_size = ?
        `, [level, achievementType, participantType, groupSize || null]);

        if (existing.length > 0) {
            return res.status(400).json({
                status: 'error',
                error: 'Kombinasi kriteria ini sudah ada'
            });
        }

        const [result] = await pool.execute(`
            INSERT INTO achievement_criteria (level, achievement_type, participant_type, group_size, score)
            VALUES (?, ?, ?, ?, ?)
        `, [level, achievementType, participantType, groupSize || null, score]);

        res.json({
            status: 'success',
            id: result.insertId,
            message: 'Kriteria prestasi berhasil ditambahkan'
        });
    } catch (error) {
        console.error('Error creating achievement criteria:', error);
        res.status(500).json({ status: 'error', error: 'Failed to create achievement criteria' });
    }
});

// PUT update achievement criteria
router.put('/:id', async (req, res) => {
    try {
        const { level, achievementType, participantType, groupSize, score } = req.body;
        const { id } = req.params;

        // Check if combination already exists for other entries
        const [existing] = await pool.execute(`
            SELECT id FROM achievement_criteria 
            WHERE level = ? AND achievement_type = ? AND participant_type = ? AND group_size = ? AND id != ?
        `, [level, achievementType, participantType, groupSize || null, id]);

        if (existing.length > 0) {
            return res.status(400).json({
                status: 'error',
                error: 'Kombinasi kriteria ini sudah ada'
            });
        }

        await pool.execute(`
            UPDATE achievement_criteria 
            SET level = ?, achievement_type = ?, participant_type = ?, group_size = ?, score = ?
            WHERE id = ?
        `, [level, achievementType, participantType, groupSize || null, score, id]);

        res.json({ status: 'success', message: 'Kriteria prestasi berhasil diperbarui' });
    } catch (error) {
        console.error('Error updating achievement criteria:', error);
        res.status(500).json({ status: 'error', error: 'Failed to update achievement criteria' });
    }
});

// DELETE achievement criteria
router.delete('/:id', async (req, res) => {
    try {
        await pool.execute('DELETE FROM achievement_criteria WHERE id = ?', [req.params.id]);
        res.json({ status: 'success', message: 'Kriteria prestasi berhasil dihapus' });
    } catch (error) {
        console.error('Error deleting achievement criteria:', error);
        res.status(500).json({ status: 'error', error: 'Failed to delete achievement criteria' });
    }
});

// POST import bulk achievement criteria
router.post('/import', async (req, res) => {
    try {
        const { criteria } = req.body;
        let success = 0;
        let failed = 0;
        const errors = [];

        for (const item of criteria) {
            try {
                const { level, achievementType, participantType, groupSize, score } = item;

                // Insert or update if combination exists
                await pool.execute(`
                    INSERT INTO achievement_criteria (level, achievement_type, participant_type, group_size, score)
                    VALUES (?, ?, ?, ?, ?)
                    ON DUPLICATE KEY UPDATE score = VALUES(score)
                `, [level, achievementType, participantType, groupSize || null, score]);
                success++;
            } catch (e) {
                failed++;
                errors.push(`${item.level} - ${item.achievementType}: ${e.message}`);
            }
        }

        res.json({ status: 'success', success, failed, errors });
    } catch (error) {
        console.error('Error importing achievement criteria:', error);
        res.status(500).json({ status: 'error', error: 'Failed to import achievement criteria' });
    }
});

// GET score by criteria (for use in achievement form)
router.get('/score', async (req, res) => {
    try {
        const { level, achievementType, participantType, groupSize } = req.query;

        const [rows] = await pool.execute(`
            SELECT score FROM achievement_criteria 
            WHERE level = ? AND achievement_type = ? AND participant_type = ? AND (group_size = ? OR group_size IS NULL)
            ORDER BY group_size DESC
            LIMIT 1
        `, [level, achievementType, participantType, groupSize || null]);

        if (rows.length > 0) {
            res.json({ score: rows[0].score });
        } else {
            res.json({ score: null, message: 'Kriteria tidak ditemukan' });
        }
    } catch (error) {
        console.error('Error fetching score:', error);
        res.status(500).json({ error: 'Failed to fetch score' });
    }
});

export default router;