-- =====================================================
-- Installation des stored procedures une par une
-- Copier-coller chaque bloc dans phpMyAdmin SQL
-- =====================================================

-- ============ BLOC 1 - Véhicule par ID ============
USE gagnonn157;
DELIMITER $$
DROP PROCEDURE IF EXISTS forage_get_vehicule$$
CREATE PROCEDURE forage_get_vehicule(IN p_id VARCHAR(20))
BEGIN
    SELECT 
        v.*,
        c.nom as conducteur_nom,
        c.telephone as conducteur_tel,
        f.modele as foreuse_modele,
        p.nom as projet_nom,
        p.localisation as projet_localisation
    FROM forage_vehicules v
    LEFT JOIN forage_conducteurs c ON v.conducteur = c.nom
    LEFT JOIN forage_foreuses f ON v.foreuse_id = f.id
    LEFT JOIN forage_projets p ON v.projet_actuel = p.numero
    WHERE v.id = p_id;
END$$
DELIMITER ;

-- ============ BLOC 2 - Upsert véhicule ============
USE gagnonn157;
DELIMITER $$
DROP PROCEDURE IF EXISTS forage_upsert_vehicule$$
CREATE PROCEDURE forage_upsert_vehicule(
    IN p_id VARCHAR(20),
    IN p_type VARCHAR(20),
    IN p_marque VARCHAR(50),
    IN p_modele VARCHAR(50),
    IN p_annee INT,
    IN p_immatriculation VARCHAR(20),
    IN p_kilometrage INT,
    IN p_statut VARCHAR(20),
    IN p_conducteur VARCHAR(100),
    IN p_foreuse_id VARCHAR(20),
    IN p_projet_actuel VARCHAR(50),
    IN p_localisation VARCHAR(200),
    IN p_latitude DECIMAL(10,6),
    IN p_longitude DECIMAL(10,6)
)
BEGIN
    INSERT INTO forage_vehicules (
        id, type, marque, modele, annee, immatriculation, kilometrage,
        statut, conducteur, foreuse_id, projet_actuel, localisation,
        gps_latitude, gps_longitude
    ) VALUES (
        p_id, p_type, p_marque, p_modele, p_annee, p_immatriculation, p_kilometrage,
        p_statut, p_conducteur, p_foreuse_id, p_projet_actuel, p_localisation,
        p_latitude, p_longitude
    )
    ON DUPLICATE KEY UPDATE
        type = p_type,
        marque = p_marque,
        modele = p_modele,
        annee = p_annee,
        immatriculation = p_immatriculation,
        kilometrage = p_kilometrage,
        statut = p_statut,
        conducteur = p_conducteur,
        foreuse_id = p_foreuse_id,
        projet_actuel = p_projet_actuel,
        localisation = p_localisation,
        gps_latitude = p_latitude,
        gps_longitude = p_longitude,
        updated_at = CURRENT_TIMESTAMP;
END$$
DELIMITER ;

-- ============ BLOC 3 - Foreuses ============
USE gagnonn157;
DELIMITER $$
DROP PROCEDURE IF EXISTS forage_get_all_foreuses$$
CREATE PROCEDURE forage_get_all_foreuses()
BEGIN
    SELECT 
        f.*,
        v.marque as vehicule_marque,
        v.modele as vehicule_modele,
        p.nom as projet_nom,
        p.client as projet_client
    FROM forage_foreuses f
    LEFT JOIN forage_vehicules v ON f.vehicule_id = v.id
    LEFT JOIN forage_projets p ON f.projet_id = p.numero
    ORDER BY f.id;
END$$
DELIMITER ;

-- ============ BLOC 4 - Foreuses disponibles ============
USE gagnonn157;
DELIMITER $$
DROP PROCEDURE IF EXISTS forage_get_foreuses_disponibles$$
CREATE PROCEDURE forage_get_foreuses_disponibles()
BEGIN
    SELECT * FROM forage_foreuses 
    WHERE statut IN ('Disponible', 'En service')
    ORDER BY id;
END$$
DELIMITER ;

-- ============ BLOC 5 - Conducteurs ============
USE gagnonn157;
DELIMITER $$
DROP PROCEDURE IF EXISTS forage_get_all_conducteurs$$
CREATE PROCEDURE forage_get_all_conducteurs()
BEGIN
    SELECT 
        c.*,
        COUNT(hc.id) as nb_trajets,
        SUM(hc.duree_minutes) as total_minutes
    FROM forage_conducteurs c
    LEFT JOIN forage_heures_conduite hc ON c.id = hc.conducteur_id
    GROUP BY c.id
    ORDER BY c.nom;
END$$
DELIMITER ;

-- ============ BLOC 6 - Projets actifs ============
USE gagnonn157;
DELIMITER $$
DROP PROCEDURE IF EXISTS forage_get_projets_actifs$$
CREATE PROCEDURE forage_get_projets_actifs()
BEGIN
    SELECT 
        p.*,
        f.modele as foreuse_modele,
        f.heures_total as foreuse_heures
    FROM forage_projets p
    LEFT JOIN forage_foreuses f ON p.foreuse_assignee = f.id
    WHERE p.statut IN ('En attente', 'En cours')
    ORDER BY 
        CASE p.statut 
            WHEN 'En cours' THEN 1 
            WHEN 'En attente' THEN 2 
        END,
        p.date_debut DESC;
END$$
DELIMITER ;

-- ============ BLOC 7 - Entretiens à venir ============
USE gagnonn157;
DELIMITER $$
DROP PROCEDURE IF EXISTS forage_get_entretiens_a_venir$$
CREATE PROCEDURE forage_get_entretiens_a_venir(IN p_jours INT)
BEGIN
    SELECT 
        e.*,
        v.marque as vehicule_marque,
        v.modele as vehicule_modele,
        v.kilometrage as vehicule_km
    FROM forage_entretien e
    JOIN forage_vehicules v ON e.vehicule_id = v.id
    WHERE e.statut = 'Planifié'
    AND e.date_planifiee BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL p_jours DAY)
    ORDER BY e.date_planifiee;
END$$
DELIMITER ;

-- ============ BLOC 8 - Créer entretien ============
USE gagnonn157;
DELIMITER $$
DROP PROCEDURE IF EXISTS forage_create_entretien$$
CREATE PROCEDURE forage_create_entretien(
    IN p_vehicule_id VARCHAR(20),
    IN p_type VARCHAR(20),
    IN p_description TEXT,
    IN p_date_planifiee DATE
)
BEGIN
    INSERT INTO forage_entretien (vehicule_id, type, description, date_planifiee, statut)
    VALUES (p_vehicule_id, p_type, p_description, p_date_planifiee, 'Planifié');
    
    SELECT LAST_INSERT_ID() as id;
END$$
DELIMITER ;

-- ============ BLOC 9 - Stats dashboard ============
USE gagnonn157;
DELIMITER $$
DROP PROCEDURE IF EXISTS forage_stats_dashboard$$
CREATE PROCEDURE forage_stats_dashboard()
BEGIN
    SELECT 
        (SELECT COUNT(*) FROM forage_vehicules WHERE statut = 'En service') as vehicules_actifs,
        (SELECT COUNT(*) FROM forage_vehicules WHERE statut = 'En entretien') as vehicules_entretien,
        (SELECT COUNT(*) FROM forage_entretien WHERE statut = 'Planifié' AND date_planifiee <= DATE_ADD(CURDATE(), INTERVAL 7 DAY)) as entretiens_semaine,
        (SELECT COUNT(*) FROM forage_projets WHERE statut = 'En cours') as projets_actifs,
        (SELECT COUNT(*) FROM forage_conducteurs WHERE statut = 'Actif') as conducteurs_actifs,
        (SELECT COUNT(*) FROM forage_foreuses WHERE statut = 'En service') as foreuses_en_service;
END$$
DELIMITER ;

-- ============ BLOC 10 - Rapport kilométrage ============
USE gagnonn157;
DELIMITER $$
DROP PROCEDURE IF EXISTS forage_rapport_kilometrage$$
CREATE PROCEDURE forage_rapport_kilometrage(
    IN p_date_debut DATE,
    IN p_date_fin DATE
)
BEGIN
    SELECT 
        v.id,
        v.marque,
        v.modele,
        v.type,
        SUM(hc.kilometrage_fin - hc.kilometrage_debut) as km_total,
        COUNT(hc.id) as nb_trajets,
        SUM(hc.duree_minutes) as minutes_total
    FROM forage_vehicules v
    LEFT JOIN forage_heures_conduite hc ON v.id = hc.vehicule_id
    WHERE hc.date BETWEEN p_date_debut AND p_date_fin
    GROUP BY v.id
    ORDER BY km_total DESC;
END$$
DELIMITER ;

-- ============ BLOC 11 - Get settings ============
USE gagnonn157;
DELIMITER $$
DROP PROCEDURE IF EXISTS forage_get_settings$$
CREATE PROCEDURE forage_get_settings()
BEGIN
    SELECT cle, valeur, type, description 
    FROM forage_settings
    ORDER BY cle;
END$$
DELIMITER ;

-- ============ BLOC 12 - Update setting ============
USE gagnonn157;
DELIMITER $$
DROP PROCEDURE IF EXISTS forage_update_setting$$
CREATE PROCEDURE forage_update_setting(
    IN p_cle VARCHAR(100),
    IN p_valeur TEXT
)
BEGIN
    UPDATE forage_settings 
    SET valeur = p_valeur, updated_at = CURRENT_TIMESTAMP
    WHERE cle = p_cle;
END$$
DELIMITER ;
