-- ========================================
-- Table TESTIMONIALS - SERVICES-AE
-- Structure complète pour les témoignages clients
-- ========================================

CREATE TABLE IF NOT EXISTS `testimonials` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `nom_client` varchar(100) NOT NULL COMMENT 'Nom complet du client',
    `email` varchar(255) NOT NULL COMMENT 'Email du client',
    `entreprise` varchar(100) DEFAULT NULL COMMENT 'Nom de l''entreprise du client',
    `poste` varchar(100) DEFAULT NULL COMMENT 'Poste/fonction du client',
    `message` text NOT NULL COMMENT 'Contenu du témoignage',
    `note` int(1) NOT NULL DEFAULT 5 COMMENT 'Note sur 5 étoiles (1-5)',
    `service_utilise` varchar(255) DEFAULT NULL COMMENT 'Service utilisé par le client',
    `image_client` varchar(255) DEFAULT NULL COMMENT 'Chemin vers l''image du client',
    `ip_address` varchar(45) DEFAULT NULL COMMENT 'Adresse IP du client',
    `user_agent` text DEFAULT NULL COMMENT 'User agent du navigateur',
    `approuve` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Statut d''approbation (0=en attente, 1=approuvé)',
    `affiche_page_accueil` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Afficher sur la page d''accueil (0=non, 1=oui)',
    `date_creation` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Date de création du témoignage',
    `date_approbation` timestamp NULL DEFAULT NULL COMMENT 'Date d''approbation du témoignage',
    
    PRIMARY KEY (`id`),
    KEY `idx_approuve` (`approuve`),
    KEY `idx_affiche_accueil` (`affiche_page_accueil`),
    KEY `idx_date_creation` (`date_creation`),
    KEY `idx_note` (`note`),
    KEY `idx_service` (`service_utilise`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table des témoignages clients SERVICES-AE';

-- ========================================
-- Contraintes et validations
-- ========================================

-- Contrainte sur les notes (1 à 5 seulement)
ALTER TABLE `testimonials` 
ADD CONSTRAINT `chk_note_range` CHECK (`note` >= 1 AND `note` <= 5);

-- Contrainte sur l'approbation (0 ou 1 seulement)
ALTER TABLE `testimonials` 
ADD CONSTRAINT `chk_approuve_boolean` CHECK (`approuve` IN (0, 1));

-- Contrainte sur l'affichage page d'accueil (0 ou 1 seulement)
ALTER TABLE `testimonials` 
ADD CONSTRAINT `chk_affiche_accueil_boolean` CHECK (`affiche_page_accueil` IN (0, 1));

-- ========================================
-- Trigger pour auto-update date_approbation
-- ========================================

DELIMITER $$

CREATE TRIGGER `update_date_approbation` 
BEFORE UPDATE ON `testimonials`
FOR EACH ROW 
BEGIN
    -- Mettre à jour la date d'approbation quand le statut passe à approuvé
    IF NEW.approuve = 1 AND OLD.approuve = 0 THEN
        SET NEW.date_approbation = CURRENT_TIMESTAMP;
    END IF;
    
    -- Réinitialiser la date d'approbation si le statut est rejeté
    IF NEW.approuve = 0 AND OLD.approuve = 1 THEN
        SET NEW.date_approbation = NULL;
    END IF;
END$$

DELIMITER ;

-- ========================================
-- Données d'exemple (optionnel)
-- ========================================

INSERT INTO `testimonials` (
    `nom_client`, 
    `email`, 
    `entreprise`, 
    `poste`, 
    `message`, 
    `note`, 
    `service_utilise`, 
    `approuve`, 
    `affiche_page_accueil`
) VALUES 
(
    'Ahmed Al Mansouri', 
    'ahmed@techcorp.ae', 
    'TechCorp UAE', 
    'Directeur IT', 
    'Service exceptionnel ! L\'équipe SERVICES-AE a résolu tous nos problèmes informatiques rapidement et efficacement. Je recommande vivement leurs services.', 
    5, 
    'Réparation ordinateurs', 
    1, 
    1
),
(
    'Fatima Hassan', 
    'fatima@innovate.ae', 
    'Innovate Solutions', 
    'Chef de projet', 
    'Très satisfaite de l\'intervention pour la récupération de données. Professionnalisme et expertise au rendez-vous. Merci SERVICES-AE !', 
    5, 
    'Récupération de données', 
    1, 
    1
),
(
    'Mohammed Saeed', 
    'mohammed@smartbiz.ae', 
    'Smart Business', 
    'Gérant', 
    'Installation réseau parfaite dans nos nouveaux locaux. Équipe compétente et délais respectés. Excellent travail !', 
    4, 
    'Installation réseau', 
    1, 
    0
);

-- ========================================
-- Vue pour les témoignages approuvés
-- ========================================

CREATE VIEW `v_testimonials_approved` AS
SELECT 
    id,
    nom_client,
    entreprise,
    poste,
    message,
    note,
    service_utilise,
    image_client,
    date_creation,
    date_approbation,
    CASE 
        WHEN affiche_page_accueil = 1 THEN 'Oui'
        ELSE 'Non'
    END as affiche_accueil_text,
    CONCAT(note, '/5 ⭐') as note_formatted
FROM testimonials 
WHERE approuve = 1
ORDER BY date_approbation DESC;

-- ========================================
-- Vue pour les statistiques
-- ========================================

CREATE VIEW `v_testimonials_stats` AS
SELECT 
    COUNT(*) as total_testimonials,
    SUM(CASE WHEN approuve = 1 THEN 1 ELSE 0 END) as approved_count,
    SUM(CASE WHEN approuve = 0 THEN 1 ELSE 0 END) as pending_count,
    AVG(CASE WHEN approuve = 1 THEN note ELSE NULL END) as avg_rating,
    SUM(CASE WHEN affiche_page_accueil = 1 THEN 1 ELSE 0 END) as homepage_count,
    MAX(date_creation) as latest_testimonial,
    COUNT(DISTINCT service_utilise) as services_count
FROM testimonials;

-- ========================================
-- Index de performance
-- ========================================

-- Index composite pour recherche rapide
CREATE INDEX `idx_search` ON `testimonials` (`approuve`, `affiche_page_accueil`, `date_creation`);

-- Index pour recherche par service
CREATE INDEX `idx_service_approved` ON `testimonials` (`service_utilise`, `approuve`);

-- Index fulltext pour recherche dans le contenu
ALTER TABLE `testimonials` ADD FULLTEXT(`nom_client`, `entreprise`, `message`);

-- ========================================
-- Procédures stockées utiles
-- ========================================

DELIMITER $$

-- Procédure pour approuver un témoignage
CREATE PROCEDURE `ApproveTestimonial`(IN testimonial_id INT)
BEGIN
    UPDATE testimonials 
    SET approuve = 1, date_approbation = CURRENT_TIMESTAMP 
    WHERE id = testimonial_id;
    
    SELECT 'Témoignage approuvé avec succès' as message;
END$$

-- Procédure pour rejeter un témoignage
CREATE PROCEDURE `RejectTestimonial`(IN testimonial_id INT)
BEGIN
    UPDATE testimonials 
    SET approuve = 0, date_approbation = NULL 
    WHERE id = testimonial_id;
    
    SELECT 'Témoignage rejeté' as message;
END$$

-- Procédure pour obtenir les statistiques
CREATE PROCEDURE `GetTestimonialsStats`()
BEGIN
    SELECT * FROM v_testimonials_stats;
END$$

DELIMITER ;

-- ========================================
-- Requêtes utiles pour l'administration
-- ========================================

/* 
-- Obtenir tous les témoignages avec formatage
SELECT 
    id,
    nom_client,
    entreprise,
    SUBSTRING(message, 1, 100) as preview,
    CONCAT(note, '/5') as rating,
    CASE WHEN approuve = 1 THEN 'Approuvé' ELSE 'En attente' END as statut,
    DATE_FORMAT(date_creation, '%d/%m/%Y %H:%i') as date_creation_fmt
FROM testimonials 
ORDER BY date_creation DESC;

-- Recherche dans les témoignages
SELECT * FROM testimonials 
WHERE MATCH(nom_client, entreprise, message) 
AGAINST('mot-clé' IN NATURAL LANGUAGE MODE);

-- Top services par nombre de témoignages
SELECT 
    service_utilise,
    COUNT(*) as nb_testimonials,
    AVG(note) as note_moyenne
FROM testimonials 
WHERE approuve = 1 
GROUP BY service_utilise 
ORDER BY nb_testimonials DESC;

-- Témoignages récents pour modération
SELECT 
    id, nom_client, entreprise, 
    SUBSTRING(message, 1, 150) as apercu,
    note, service_utilise,
    DATE_FORMAT(date_creation, '%d/%m/%Y à %H:%i') as date_soumission
FROM testimonials 
WHERE approuve = 0 
ORDER BY date_creation DESC 
LIMIT 10;
*/

-- FIN DU SCRIPT