const express = require('express');
const cors = require('cors');
const sql = require('mssql');
require('dotenv').config();

const app = express();
const PORT = 3000;

// Middleware
app.use(express.json());
app.use(cors({
    origin: [
        'http://localhost:3001',
        'http://localhost:8000',
        'http://127.0.0.1:3001',
        'https://mygtf.ensup-adm.net:3001',
        'https://mygtf.ensup-adm.net'
    ],
    credentials: true
}));

// Configuration SQL
const dbConfig = {
    server: process.env.DB_SERVER,
    database: process.env.DB_DATABASE,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    options: { encrypt: true, trustServerCertificate: true, enableArithAbort: true }
};

let pool = null;
let systemStatus = {
    hasFormateurEmailColumn: false,
    hasFormateurView: false,
    canAccessFormateurView: false,
    hasFormateurLocal: false,
    operatingMode: 'unknown'
};

// Connexion DB
async function connectDatabase() {
    try {
        pool = await sql.connect(dbConfig);
        console.log('Base de données connectée');
        await checkSystemStatus();
        return true;
    } catch (error) {
        console.error('Erreur de connexion DB:', error.message);
        return false;
    }
}

// Diagnostic du système
async function checkSystemStatus() {
    try {
        const columnCheck = await pool.request().query(`
            SELECT COUNT(*) AS count
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_NAME = 'declarations'
            AND COLUMN_NAME = 'formateur_email_fk'
        `);
        systemStatus.hasFormateurEmailColumn = columnCheck.recordset[0].count > 0;

        const viewCheck = await pool.request().query(`
            SELECT COUNT(*) AS count
            FROM INFORMATION_SCHEMA.VIEWS
            WHERE TABLE_NAME = 'v_Formateurs_CD_Declarations'
        `);
        systemStatus.hasFormateurView = viewCheck.recordset[0].count > 0;

        if (systemStatus.hasFormateurView) {
            try {
                await pool.request().query(`SELECT TOP 1 userPrincipalName FROM dbo.v_Formateurs_CD_Declarations`);
                systemStatus.canAccessFormateurView = true;
            } catch {
                systemStatus.canAccessFormateurView = false;
            }
        }

        systemStatus.operatingMode = systemStatus.hasFormateurEmailColumn && systemStatus.canAccessFormateurView
            ? 'new_with_view'
            : 'legacy_hash';

        console.log('État système:', systemStatus);

    } catch (error) {
        console.error('Erreur diagnostic:', error.message);
        systemStatus.operatingMode = 'legacy_hash';
    }
}

// Hash legacy
function generateHashFromEmail(email) {
    let hash = 0;
    for (let i = 0; i < email.length; i++) {
        const char = email.charCodeAt(i);
        hash = ((hash << 5) - hash) + char;
        hash = hash & hash;
    }
    return Math.abs(hash) % 10000 + 1000;
}

// Conversion heure
const toSQLTime = (timeStr) => {
    if (!timeStr) return null;
    const [hours, minutes] = timeStr.split(':').map(Number);
    return `${hours.toString().padStart(2, '0')}:${minutes.toString().padStart(2, '0')}:00`;
};

// ROUTES

// Diagnostic complet
app.get('/api/diagnostic', async (req, res) => {
    await checkSystemStatus();
    res.json(systemStatus);
});

// Liste formateurs avec déclaration jointe
// Route /api/formateurs modifiée pour utiliser formateurs_local
app.get('/api/formateurs', async (req, res) => {
    try {
        const { email, search } = req.query;

        let query = `
            SELECT 
                userPrincipalName as email,
                CONCAT(givenname, ' ', surname) as nom_complet,
                Campus,
                Contrat
            FROM formateurs_local
        `;

        const request = pool.request();

        if (email) {
            query += ` WHERE userPrincipalName = @email`;
            request.input('email', sql.NVarChar, email);
        } else if (search) {
            query += ` WHERE CONCAT(givenname, ' ', surname) LIKE @search OR userPrincipalName LIKE @search`;
            request.input('search', sql.NVarChar, `%${search}%`);
        }

        query += ` ORDER BY surname, givenname`;

        const result = await request.query(query);

        res.json({
            success: true,
            data: result.recordset
        });

    } catch (error) {
        console.error('Erreur /api/formateurs:', error);
        res.status(500).json({
            success: false,
            error: error.message
        });
    }
});
app.post('/api/save_declaration', async (req, res) => {
    try {
        const { id, date, activityType, hours, description, user, startTime, endTime } = req.body;
        if (!user?.email) return res.status(400).json({ error: 'Email requis' });

        const heureDebutSQL = toSQLTime(startTime);
        const heureFinSQL = toSQLTime(endTime);

        // Récupérer l'ID du type "preparation"
        const typeResult = await pool.request()
            .query("SELECT id FROM types_demandes WHERE libelle = 'preparation'");

        if (typeResult.recordset.length === 0) {
            return res.status(500).json({
                error: 'Configuration manquante: le type "preparation" n\'existe pas dans la base.'
            });
        }

        const typeDemandeId = typeResult.recordset[0].id;

        // Vérifier que le formateur existe dans formateurs_local
        const formateur = await pool.request()
            .input('email', sql.NVarChar, user.email)
            .query('SELECT TOP 1 userPrincipalName FROM formateurs_local WHERE userPrincipalName=@email');

        if (formateur.recordset.length === 0) {
            return res.status(403).json({ error: 'Formateur non trouvé dans la table formateurs_local.' });
        }

        const formateurEmail = formateur.recordset[0].userPrincipalName;

        if (id) {
            // MODIFICATION
            console.log('Modification de la déclaration ID:', id);
            await pool.request()
                .input('id', sql.Int, id)
                .input('typeDemandeId', sql.Int, typeDemandeId)
                .input('hours', sql.Float, hours)
                .input('description', sql.NVarChar, description || null)
                .input('heure_debut', sql.VarChar, heureDebutSQL)
                .input('heure_fin', sql.VarChar, heureFinSQL)
                .query(`
                    UPDATE declarations
                    SET type_demande_id=@typeDemandeId, duree=@hours, description=@description,
                        heure_debut=@heure_debut, heure_fin=@heure_fin
                    WHERE id=@id
                `);
            console.log('✅ Déclaration modifiée');
        } else {
            // NOUVELLE SAISIE
            console.log('Création nouvelle déclaration pour', formateurEmail, 'le', date);
            await pool.request()
                .input('formateurEmail', sql.NVarChar, formateurEmail)
                .input('typeDemandeId', sql.Int, typeDemandeId)
                .input('date', sql.Date, date)
                .input('hours', sql.Float, hours)
                .input('description', sql.NVarChar, description || null)
                .input('heure_debut', sql.VarChar, heureDebutSQL)
                .input('heure_fin', sql.VarChar, heureFinSQL)
                .query(`
                    INSERT INTO declarations
                        (formateur_email_fk, type_demande_id, date, duree, description, heure_debut, heure_fin)
                    VALUES
                        (@formateurEmail, @typeDemandeId, @date, @hours, @description, @heure_debut, @heure_fin)
                `);
            console.log('✅ Nouvelle déclaration créée');
        }

        res.json({ success: true, message: 'Déclaration sauvegardée' });

    } catch (error) {
        console.error("Erreur save_declaration:", error);
        res.status(500).json({ error: error.message, stack: error.stack });
    }
});
// Récupérer déclarations par email
app.get('/api/get_declarations_by_email', async (req, res) => {
    try {
        const { email } = req.query;
        if (!email) return res.status(400).json({ error: 'Email requis' });

        let query = `
            SELECT
                d.id,
                d.formateur_email_fk AS formateur_email,
                CONCAT(f.givenname, ' ', f.surname) AS formateur_nom,
                f.Campus,
                td.libelle AS activityType,
                d.date,
                d.duree,
                d.heure_debut,
                d.heure_fin,
                d.description,
                d.status
            FROM declarations d
            INNER JOIN types_demandes td ON d.type_demande_id = td.id
            LEFT JOIN formateurs_local f ON d.formateur_email_fk = f.userPrincipalName
            WHERE d.formateur_email_fk = @email
            ORDER BY d.date DESC
        `;
        const result = await pool.request().input('email', sql.NVarChar, email).query(query);
        res.json(result.recordset);
    } catch (error) {
        console.error(error);
        res.status(500).json({ error: error.message });
    }
});

// Récupérer toutes les déclarations
app.get('/api/get_declarations', async (req, res) => {
    try {
        const { user_email, admin } = req.query;
        if (user_email && !admin) return app._router.handle({ ...req, query: { email: user_email } }, res);

        const query = `
            SELECT
                d.id,
                d.formateur_email_fk AS formateur_email,
                CONCAT(f.givenname, ' ', f.surname) AS formateur_nom,
                f.Campus,
                td.libelle AS activityType,
                d.date,
                d.duree,
                d.heure_debut,
                d.heure_fin,
                d.description,
                d.status
            FROM declarations d
            INNER JOIN types_demandes td ON d.type_demande_id = td.id
            LEFT JOIN formateurs_local f ON d.formateur_email_fk = f.userPrincipalName
            ORDER BY d.date DESC
        `;
        const result = await pool.request().query(query);
        res.json(result.recordset);
    } catch (error) {
        console.error(error);
        res.status(500).json({ error: error.message });
    }
});

// Supprimer une déclaration
app.delete('/api/delete_declaration/:id', async (req, res) => {
    try {
        const { id } = req.params;
        if (!id) return res.status(400).json({ error: 'ID de la déclaration requis' });

        // Vérifier si la déclaration existe
        const existing = await pool.request()
            .input('id', sql.Int, id)
            .query('SELECT * FROM declarations WHERE id = @id');

        if (existing.recordset.length === 0) {
            return res.status(404).json({ error: 'Déclaration non trouvée' });
        }

        // Supprimer la déclaration
        await pool.request()
            .input('id', sql.Int, id)
            .query('DELETE FROM declarations WHERE id = @id');

        res.json({ success: true, message: 'Déclaration supprimée' });

    } catch (error) {
        console.error('Erreur delete_declaration:', error);
        res.status(500).json({ error: error.message });
    }
});


// ==================== ROUTES GESTION DES CLÔTURES ====================

// Vérifier si une date est clôturée
app.get('/api/check-cloture', async (req, res) => {
    try {
        const { date, campus } = req.query;

        if (!date) {
            return res.status(400).json({ error: 'Date requise' });
        }

        const moisAnnee = date.substring(0, 7); // '2025-01-15' -> '2025-01'

        const result = await pool.request()
            .input('mois_annee', sql.VarChar, moisAnnee)
            .input('campus', sql.VarChar, campus || null)
            .query(`
                SELECT * FROM clotures_saisie 
                WHERE mois_annee = @mois_annee 
                AND (campus = @campus OR campus IS NULL OR @campus IS NULL)
            `);

        res.json({
            cloture: result.recordset.length > 0,
            details: result.recordset[0] || null
        });
    } catch (error) {
        res.status(500).json({ error: error.message });
    }
});

// Récupérer toutes les clôtures
app.get('/api/clotures', async (req, res) => {
    try {
        const result = await pool.request().query(`
            SELECT * FROM clotures_saisie 
            ORDER BY date_cloture DESC
        `);

        res.json({
            success: true,
            clotures: result.recordset
        });
    } catch (error) {
        res.status(500).json({ success: false, error: error.message });
    }
});



// Démarrage serveur
async function startServer() {
    const dbConnected = await connectDatabase();
    if (!dbConnected) return console.log('Impossible de démarrer sans DB');

    app.listen(PORT, () => {
        console.log(`🚀 Serveur démarré sur http://localhost:${PORT}`);
        console.log('Mode fonctionnement:', systemStatus.operatingMode);
    });
}

process.on('SIGINT', async () => {
    if (pool) await pool.close();
    process.exit(0);
});

startServer();