-- initialisation de la base mysql pour les indicateurs Esup

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

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

DROP TABLE IF EXISTS `stat_service`;
DROP TABLE IF EXISTS `stat_service_mois`;
DROP TABLE IF EXISTS `stat_session`;
DROP TABLE IF EXISTS `stat_session_mois`;
DROP TABLE IF EXISTS `service`;
DROP TABLE IF EXISTS `population`;
DROP TABLE IF EXISTS `etablissement`;


CREATE TABLE `etablissement` (
  `id_eta` int NOT NULL auto_increment,
  `cle_eta` varchar(32) NOT NULL default '',
  `nom_eta` text NOT NULL,
  PRIMARY KEY  (`id_eta`)
) DEFAULT CHARSET=utf8;

CREATE TABLE `population` (
  `id_pop` int NOT NULL auto_increment,
  `cle_pop` varchar(32) NOT NULL,
  `nom_pop` text NOT NULL,
  `rang_pop` int NOT NULL default '0',
  PRIMARY KEY  (`id_pop`)
) DEFAULT CHARSET=utf8;

CREATE TABLE `service` (
  `id_serv` int NOT NULL auto_increment,
  `cle_serv` varchar(32) NOT NULL,
  `nom_serv` text NOT NULL,
  `rang_serv` int NOT NULL,
  PRIMARY KEY  (`id_serv`)
) DEFAULT CHARSET=utf8;

CREATE TABLE `stat_service` (
  `id_eta` int NOT NULL default 0,
  `id_pop` int NOT NULL default 0,
  `id_serv` int NOT NULL default '0',
  `date_serv` date NOT NULL DEFAULT '2000-01-01',-- année-mois-jour
  `nb_user` int NOT NULL default '0',
  `nb_access` int NOT NULL default '0',
  PRIMARY KEY  (`id_eta`,`id_pop`,`id_serv`,`date_serv`),
  FOREIGN KEY (`id_eta`) REFERENCES `etablissement`(`id_eta`) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (`id_pop`) REFERENCES `population`(`id_pop`) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (`id_serv`) REFERENCES `service`(`id_serv`) ON UPDATE CASCADE ON DELETE RESTRICT
) DEFAULT CHARSET=utf8;

CREATE TABLE `stat_service_mois` (
  `id_eta` int NOT NULL default 0,
  `id_pop` int NOT NULL default 0,
  `id_serv` int NOT NULL default '0',
  `mois_serv` varchar(7) NOT NULL default '',
  `nb_user` int NOT NULL default '0',
  `nb_access` int NOT NULL default '0',
  PRIMARY KEY  (`id_eta`,`id_pop`,`id_serv`,`mois_serv`),
  FOREIGN KEY (`id_eta`) REFERENCES `etablissement`(`id_eta`) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (`id_pop`) REFERENCES `population`(`id_pop`) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (`id_serv`) REFERENCES `service`(`id_serv`) ON UPDATE CASCADE ON DELETE RESTRICT
) DEFAULT CHARSET=utf8;

CREATE TABLE `stat_session` (
  `id_eta` int NOT NULL default 0,
  `id_pop` int NOT NULL default 0,
  `date_sess` date NOT NULL DEFAULT '2000-01-01',-- année-mois-jour
  `nb_user` int NOT NULL default '0',
  `nb_session` int NOT NULL default '0',
  `time_session` int NOT NULL default '0',
  PRIMARY KEY  (`id_eta`,`id_pop`,`date_sess`),
  FOREIGN KEY (`id_eta`) REFERENCES `etablissement`(`id_eta`) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (`id_pop`) REFERENCES `population`(`id_pop`) ON UPDATE CASCADE ON DELETE RESTRICT
) DEFAULT CHARSET=utf8;

CREATE TABLE `stat_session_mois` (
  `id_eta` int NOT NULL default 0,
  `id_pop` int NOT NULL default 0,
  `mois_sess` varchar(7) NOT NULL default '',
  `nb_user_max` int NOT NULL default '0',
  `nb_user` int NOT NULL default '0',
  `nb_user_active` int NOT NULL default '0',
  `nb_session` int NOT NULL default '0',
  `time_session` int NOT NULL default '0',
  PRIMARY KEY  (`id_eta`,`id_pop`,`mois_sess`),
  FOREIGN KEY (`id_eta`) REFERENCES `etablissement`(`id_eta`) ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (`id_pop`) REFERENCES `population`(`id_pop`) ON UPDATE CASCADE ON DELETE RESTRICT
) DEFAULT CHARSET=utf8;