CREATE TABLE IF NOT EXISTS facebook_group_recommendations (
    facebook_group_id BIGINT UNSIGNED NOT NULL,
    recommendation ENUM('watch','occasional','remove') NOT NULL,
    reason TEXT NULL,
    decided_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    PRIMARY KEY (facebook_group_id),
    KEY idx_facebook_group_recommendation (recommendation),
    CONSTRAINT fk_facebook_group_recommendation_group FOREIGN KEY (facebook_group_id) REFERENCES facebook_groups(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO facebook_group_recommendations (facebook_group_id,recommendation,reason,decided_at,updated_at)
SELECT v.facebook_group_id,
       CASE WHEN v.value_score < 20 OR v.off_topic_rate >= 65 OR v.spam_rate >= 85 THEN 'remove' WHEN v.value_score >= 55 AND v.coherence_score >= 60 THEN 'watch' ELSE 'occasional' END,
       CASE WHEN v.value_score < 20 OR v.off_topic_rate >= 65 OR v.spam_rate >= 85 THEN 'Contenus trop souvent hors sujet, indésirables ou sans valeur suffisante pour justifier le suivi.' WHEN v.value_score >= 55 AND v.coherence_score >= 60 THEN 'Groupe cohérent et suffisamment utile pour une surveillance régulière.' ELSE 'Intérêt limité ou irrégulier : à consulter seulement lorsqu’un besoin précis le justifie.' END,
       UTC_TIMESTAMP(),UTC_TIMESTAMP()
FROM facebook_group_vitality v
ON DUPLICATE KEY UPDATE facebook_group_id=VALUES(facebook_group_id);
