CREATE DATABASE  IF NOT EXISTS `plateformee5` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `plateformee5`;


--
-- Table structure for table `astuce`
--
DROP TABLE IF EXISTS `utilisateur`;

CREATE TABLE `utilisateur` (
  `id` int NOT NULL AUTO_INCREMENT,
  `nom` varchar(50) NOT NULL,
  `prenom` varchar(45) NOT NULL,
  `username` varchar(45) NOT NULL,
  `email` varchar(50) NOT NULL,
  `password_hash` varchar(256) NOT NULL,
  `dateInscription` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
;

--
-- Dumping data for table `utilisateur`
--


INSERT INTO `utilisateur` VALUES (1,'Abdou Abdallah','Radja','radja','radja.abdou.pro@gmail.com','e4868585234052bd6c5fd54bb86cf933026b47fb0399015e58c931d98fd2c11a','2024-03-29'),(2,'Manroufou','El-Fayed','El-Fayed','El-Fayed@frferfrr.com','5ec4f8f4faf2bc705d7acf0198965c69ac85f9f21ad98b0cd1943cdb2e5097d9','2024-03-29'),(3,'Zoldik','kirua','kirua','kirua@gmail.com','a39c6fd223240f1e46fb213620e349c25cdb2c86d2ae70a7a0b9f4130ea4a354','2024-04-03');


DROP TABLE IF EXISTS `astuce`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `astuce` (
  `numero` int NOT NULL AUTO_INCREMENT,
  `domaine` varchar(45) NOT NULL,
  `titre` varchar(50) NOT NULL,
  `descriptionn` varchar(300) NOT NULL,
  `contenu` longtext,
  `datePublication` datetime NOT NULL,
  `idUtilisateur` int NOT NULL,
  PRIMARY KEY (`numero`),
  KEY `idUtilisateur` (`idUtilisateur`)
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `astuce` VALUES (1,'cuisine','kari poulet','kari poulet','kari ','2024-04-02 10:10:50',1),(13,'mecanique','test','La moto','<div><strong>La moto de clark est rouge&nbsp;</strong>et celui de&nbsp;<em>radja est noir</em></div>','2024-04-03 11:20:00',1);

--
-- Table structure for table `culture`
--

DROP TABLE IF EXISTS `culture`;
CREATE TABLE `culture` (
  `numero` INT NOT NULL AUTO_INCREMENT,
  `domaine` varchar(45) NOT NULL,
  `titre` varchar(50) NOT NULL,
  `descriptionn` varchar(200) NOT NULL,
  `contenu` longtext NOT NULL,
  `datePublication` datetime DEFAULT NULL,
  `idUtilisateur` int NOT NULL,
  PRIMARY KEY (`numero`),
  KEY `id` (`idUtilisateur`)
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


UNLOCK TABLES;

--
-- Table structure for table `idee`
--

DROP TABLE IF EXISTS `idee`;

CREATE TABLE `idee` (
  `numero` int NOT NULL AUTO_INCREMENT,
  `domaine` varchar(45) NOT NULL,
  `titre` varchar(50) NOT NULL,
  `descriptionn` varchar(200) NOT NULL,
  `contenu` longtext NOT NULL,
  `datePublication` datetime NOT NULL,
  `idUtilisateur` int NOT NULL,
  PRIMARY KEY (`numero`),
  KEY `idUtilisateur` (`idUtilisateur`)
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
;



--
-- Table structure for table `utilisateur`
--



CREATE TABLE `journal_des_suppressions` (
  `id` int NOT NULL AUTO_INCREMENT,
  'typePublication' varchar(45)  NOT NULL, 
  `publication_id` int NOT NULL,
  `domaine` varchar(45) NOT NULL,
  `titre` varchar(50) NOT NULL,
  `descriptionn` varchar(200) NOT NULL,
  `contenu` longtext NOT NULL,
  `datePublication` datetime NOT NULL,
  `utilisateur_id` int NOT NULL,
  `date_suppression` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


drop procedure if EXISTS deletePublication ;
DELIMITER //

CREATE PROCEDURE plateformee5.deletePublication(
    IN typePublication VARCHAR(255), 
    IN userId INT, 
    IN idPublication INT
)
BEGIN
    DECLARE tableName VARCHAR(255); 
    
    CASE typePublication
        WHEN 'astuce' THEN SET tableName = 'astuce';
        WHEN 'culture' THEN SET tableName = 'culture';
        WHEN 'idee' THEN SET tableName = 'idee';
        ELSE SET tableName = ''; 
    END CASE;

    IF tableName <> '' THEN
        SET @query = CONCAT('DELETE FROM ', tableName, ' WHERE idUtilisateur = ', userId, ' AND numero = ', idPublication);
        PREPARE stmt FROM @query;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
    
END //

DELIMITER ;
---
-- trigger pour enregistrer les publication supprimer
---
DELIMITER //

CREATE TRIGGER journal_supp_astuce AFTER DELETE ON astuce
FOR EACH ROW
BEGIN
    INSERT INTO journal_des_suppressions (typePublication,publication_id, domaine, titre, descriptionn, contenu, datePublication, utilisateur_id, date_suppression)
    VALUES ('astuce',OLD.numero, OLD.domaine, OLD.titre, OLD.descriptionn, OLD.contenu, OLD.datePublication, OLD.idUtilisateur, NOW());
END;
//

CREATE TRIGGER journal_supp_culture AFTER DELETE ON culture
FOR EACH ROW
BEGIN
    INSERT INTO journal_des_suppressions (typePublication,publication_id, domaine, titre, descriptionn, contenu, datePublication, utilisateur_id, date_suppression)
    VALUES ("culture",OLD.numero, OLD.domaine, OLD.titre, OLD.descriptionn, OLD.contenu, OLD.datePublication, OLD.idUtilisateur, NOW());
END;
//

CREATE TRIGGER journal_supp_idee AFTER DELETE ON idee
FOR EACH ROW
BEGIN
    INSERT INTO journal_des_suppressions (typePublication,publication_id, domaine, titre, descriptionn, contenu, datePublication, utilisateur_id, date_suppression)
    VALUES ('idee',OLD.numero, OLD.domaine, OLD.titre, OLD.descriptionn, OLD.contenu, OLD.datePublication, OLD.idUtilisateur, NOW());
END;
//

DELIMITER ;