drop table if exists bspa_comment;
drop table if exists bspa_user;
drop table if exists bspa_episode;

CREATE TABLE IF NOT EXISTS bspa_episode (
  epi_id int(11) NOT NULL,
  epi_title varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  epi_content text COLLATE utf8_unicode_ci NOT NULL,
  epi_date_created datetime DEFAULT NULL,
  epi_date_modified datetime DEFAULT NULL,
  epi_status varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'draft'
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE bspa_episode
  ADD PRIMARY KEY (epi_id);

CREATE TABLE bspa_user (
  usr_id int(11) NOT NULL,
  usr_name varchar(150) COLLATE utf8_unicode_ci NOT NULL,
  usr_password varchar(88) COLLATE utf8_unicode_ci NOT NULL,
  usr_salt varchar(23) COLLATE utf8_unicode_ci NOT NULL,
  usr_role varchar(50) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE bspa_user
  ADD PRIMARY KEY (usr_id);

CREATE TABLE IF NOT EXISTS bspa_comment (
  com_id int(11) NOT NULL,
  com_content varchar(500) COLLATE utf8_unicode_ci NOT NULL,
  com_date_created datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  com_date_modified datetime DEFAULT NULL,
  com_a_moderer tinyint(1) NOT NULL DEFAULT '0',
  com_author varchar(40) COLLATE utf8_unicode_ci NOT NULL,
  epi_id int(11) NOT NULL,
  com_idparent int(11) DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=48 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Index pour la table bspa_comment
--
ALTER TABLE bspa_comment
  ADD PRIMARY KEY (com_id),
  ADD KEY fk_com_epi (epi_id);
--
-- Contraintes pour la table bspa_comment
--
ALTER TABLE bspa_comment
  ADD CONSTRAINT fk_com_epi FOREIGN KEY (epi_id) REFERENCES bspa_episode (epi_id);
