--
-- Base de données: `tarentelle`
--

-- --------------------------------------------------------

--
-- Structure de la table `adherent`
--

CREATE TABLE IF NOT EXISTS `adherent` (
  `id_adherent` int(5) unsigned NOT NULL AUTO_INCREMENT,
  `nom_adherent` varchar(50) NOT NULL,
  `prenom_adherent` varchar(50) NOT NULL,
  `photo_adherent` varchar(50) DEFAULT NULL,
  `date_naissance_adherent` date DEFAULT NULL,
  `email_adherent` varchar(80) DEFAULT NULL,
  `telfixe_adherent` varchar(10) DEFAULT NULL,
  `telmobile_adherent` varchar(10) DEFAULT NULL,
  `adresse_adherent` varchar(100) DEFAULT NULL,
  `codepostal_adherent` varchar(5) DEFAULT NULL,
  `ville_adherent` varchar(50) DEFAULT NULL,
  `login_adherent` varchar(20) NOT NULL,
  `password_adherent` varchar(100) NOT NULL,
  `admin_adherent` tinyint(1) NOT NULL,
  `id_categorie` int(2) unsigned NOT NULL,
  `id_pupitre` int(2) unsigned DEFAULT NULL,
  `id_responsable_legal` int(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`id_adherent`),
  UNIQUE KEY `login_adherent` (`login_adherent`),
  KEY `id_categorie` (`id_categorie`),
  KEY `id_pupitre` (`id_pupitre`),
  KEY `id_responsable_legal` (`id_responsable_legal`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

--
-- Déclencheurs `adherent`
--
DROP TRIGGER IF EXISTS `archive_adherent`;
DELIMITER //
CREATE TRIGGER `archive_adherent` BEFORE DELETE ON `adherent`
 FOR EACH ROW BEGIN

            -- pour l'intitulé du pupitre
            SET @pupitre = (SELECT intitule_pupitre FROM pupitre WHERE id_pupitre = OLD.id_pupitre);
            -- pour l'intitulé de la catégorie
            SET @categorie = (SELECT intitule_categorie FROM categorie WHERE id_categorie = OLD.id_categorie);
            -- pour les données du responsable légal
            SET @nom_responsable = (SELECT nom_responsable_legal FROM responsable_legal WHERE id_responsable_legal = OLD.id_responsable_legal);
            SET @prenom_responsable = (SELECT prenom_responsable_legal FROM responsable_legal WHERE id_responsable_legal = OLD.id_responsable_legal);
            SET @titre_responsable = (SELECT titre_responsable_legal FROM responsable_legal WHERE id_responsable_legal = OLD.id_responsable_legal);

            -- on stocke dans la table archive_adherent les données de l'adhérent supprimé
            -- (la date de suppression de l'adhérent est la date du jour)
            INSERT INTO archive_adherent VALUES(null, OLD.nom_adherent, OLD.prenom_adherent, OLD.date_naissance_adherent,
            OLD.email_adherent, OLD.telfixe_adherent, OLD.telmobile_adherent, OLD.adresse_adherent, OLD.codepostal_adherent,
            OLD.ville_adherent, @pupitre, @categorie, @nom_responsable, @prenom_responsable, @titre_responsable, NOW());

  	END
//
DELIMITER ;

-- --------------------------------------------------------

--
-- Structure de la table `archive_adherent`
--

CREATE TABLE IF NOT EXISTS `archive_adherent` (
  `id_adherent` int(5) unsigned NOT NULL AUTO_INCREMENT,
  `nom_adherent` varchar(50) NOT NULL,
  `prenom_adherent` varchar(50) NOT NULL,
  `date_naissance_adherent` date DEFAULT NULL,
  `email_adherent` varchar(80) DEFAULT NULL,
  `telfixe_adherent` varchar(10) DEFAULT NULL,
  `telmobile_adherent` varchar(10) DEFAULT NULL,
  `adresse_adherent` varchar(100) DEFAULT NULL,
  `codepostal_adherent` varchar(5) DEFAULT NULL,
  `ville_adherent` varchar(50) DEFAULT NULL,
  `intitule_pupitre` varchar(20) DEFAULT NULL,
  `intitule_categorie` varchar(20) NOT NULL,
  `nom_responsable_legal` varchar(50) DEFAULT NULL,
  `prenom_responsable_legal` varchar(50) DEFAULT NULL,
  `titre_responsable_legal` varchar(30) DEFAULT NULL,
  `date_suppression_adherent` date NOT NULL,
  PRIMARY KEY (`id_adherent`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Structure de la table `article`
--

CREATE TABLE IF NOT EXISTS `article` (
  `id_article` int(6) unsigned NOT NULL AUTO_INCREMENT,
  `titre_article` varchar(75) DEFAULT NULL,
  `date_article` date NOT NULL,
  `texte_article` text,
  `illustration_article` varchar(60) DEFAULT NULL,
  `id_adherent` int(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`id_article`),
  KEY `id_adherent` (`id_adherent`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Structure de la table `article_prive`
--

CREATE TABLE IF NOT EXISTS `article_prive` (
  `id_article` int(6) unsigned NOT NULL AUTO_INCREMENT,
  `titre_article` varchar(75) DEFAULT NULL,
  `date_article` date NOT NULL,
  `texte_article` text,
  `illustration_article` varchar(60) DEFAULT NULL,
  `id_adherent` int(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`id_article`),
  KEY `id_adherent` (`id_adherent`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Structure de la table `categorie`
--

CREATE TABLE IF NOT EXISTS `categorie` (
  `id_categorie` int(2) unsigned NOT NULL AUTO_INCREMENT,
  `intitule_categorie` varchar(20) NOT NULL,
  PRIMARY KEY (`id_categorie`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Structure de la table `commentaire`
--

CREATE TABLE IF NOT EXISTS `commentaire` (
  `id_commentaire` int(7) NOT NULL AUTO_INCREMENT,
  `auteur_commentaire` varchar(100) NOT NULL,
  `date_commentaire` datetime NOT NULL,
  `texte_commentaire` text NOT NULL,
  `id_article` int(6) unsigned NOT NULL,
  `id_adherent` int(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`id_commentaire`),
  KEY `id_adherent` (`id_adherent`),
  KEY `id_article` (`id_article`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Structure de la table `commentaire_prive`
--

CREATE TABLE IF NOT EXISTS `commentaire_prive` (
  `id_commentaire` int(7) NOT NULL AUTO_INCREMENT,
  `auteur_commentaire` varchar(100) NOT NULL,
  `date_commentaire` datetime NOT NULL,
  `texte_commentaire` text NOT NULL,
  `id_article` int(6) unsigned NOT NULL,
  `id_adherent` int(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`id_commentaire`),
  KEY `id_adherent` (`id_adherent`),
  KEY `id_article` (`id_article`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Structure de la table `evenement`
--

CREATE TABLE IF NOT EXISTS `evenement` (
  `id_evenement` int(6) unsigned NOT NULL AUTO_INCREMENT,
  `date_evenement` date NOT NULL,
  `description_evenement` varchar(100) NOT NULL,
  `public_evenement` varchar(50) DEFAULT 'tout le monde',
  `id_adherent` int(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`id_evenement`),
  KEY `id_adherent` (`id_adherent`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Structure de la table `pupitre`
--

CREATE TABLE IF NOT EXISTS `pupitre` (
  `id_pupitre` int(2) unsigned NOT NULL AUTO_INCREMENT,
  `intitule_pupitre` varchar(20) NOT NULL,
  PRIMARY KEY (`id_pupitre`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Structure de la table `recette`
--

CREATE TABLE IF NOT EXISTS `recette` (
  `id_recette` int(4) NOT NULL AUTO_INCREMENT,
  `titre_recette` varchar(75) NOT NULL,
  `contenu_recette` text NOT NULL,
  `auteur_recette` varchar(101) DEFAULT NULL,
  PRIMARY KEY (`id_recette`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Structure de la table `responsable_legal`
--

CREATE TABLE IF NOT EXISTS `responsable_legal` (
  `id_responsable_legal` int(5) unsigned NOT NULL AUTO_INCREMENT,
  `nom_responsable_legal` varchar(50) NOT NULL,
  `prenom_responsable_legal` varchar(50) NOT NULL,
  `titre_responsable_legal` varchar(30) NOT NULL,
  PRIMARY KEY (`id_responsable_legal`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

--
-- Contraintes pour les tables exportées
--

--
-- Contraintes pour la table `adherent`
--
ALTER TABLE `adherent`
  ADD CONSTRAINT `adherent_ibfk_1` FOREIGN KEY (`id_categorie`) REFERENCES `categorie` (`id_categorie`) ON DELETE NO ACTION ON UPDATE CASCADE,
  ADD CONSTRAINT `adherent_ibfk_2` FOREIGN KEY (`id_pupitre`) REFERENCES `pupitre` (`id_pupitre`) ON DELETE NO ACTION ON UPDATE CASCADE,
  ADD CONSTRAINT `adherent_ibfk_3` FOREIGN KEY (`id_responsable_legal`) REFERENCES `responsable_legal` (`id_responsable_legal`) ON DELETE NO ACTION ON UPDATE CASCADE;

--
-- Contraintes pour la table `article`
--
ALTER TABLE `article`
  ADD CONSTRAINT `article_ibfk_1` FOREIGN KEY (`id_adherent`) REFERENCES `adherent` (`id_adherent`) ON DELETE NO ACTION ON UPDATE CASCADE;

--
-- Contraintes pour la table `article_prive`
--
ALTER TABLE `article_prive`
  ADD CONSTRAINT `article_prive_ibfk_1` FOREIGN KEY (`id_adherent`) REFERENCES `adherent` (`id_adherent`) ON DELETE NO ACTION ON UPDATE CASCADE;

--
-- Contraintes pour la table `commentaire`
--
ALTER TABLE `commentaire`
  ADD CONSTRAINT `commentaire_ibfk_1` FOREIGN KEY (`id_adherent`) REFERENCES `adherent` (`id_adherent`) ON DELETE NO ACTION ON UPDATE CASCADE,
  ADD CONSTRAINT `commentaire_ibfk_3` FOREIGN KEY (`id_article`) REFERENCES `article` (`id_article`) ON DELETE NO ACTION ON UPDATE CASCADE;

--
-- Contraintes pour la table `commentaire_prive`
--
ALTER TABLE `commentaire_prive`
  ADD CONSTRAINT `commentaire_prive_ibfk_1` FOREIGN KEY (`id_article`) REFERENCES `article_prive` (`id_article`) ON DELETE NO ACTION ON UPDATE CASCADE,
  ADD CONSTRAINT `commentaire_prive_ibfk_2` FOREIGN KEY (`id_adherent`) REFERENCES `adherent` (`id_adherent`) ON DELETE NO ACTION ON UPDATE CASCADE;

--
-- Contraintes pour la table `evenement`
--
ALTER TABLE `evenement`
  ADD CONSTRAINT `evenement_ibfk_1` FOREIGN KEY (`id_adherent`) REFERENCES `adherent` (`id_adherent`) ON DELETE NO ACTION ON UPDATE CASCADE;

