-- initialisation de la base postgre pour les indicateurs Esup

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

-- DROP TABLE stat_service;
-- DROP TABLE stat_service_mois;
-- DROP TABLE stat_session;
-- DROP TABLE stat_session_mois;
-- DROP TABLE population;
-- DROP TABLE service;
-- DROP TABLE etablissement;


CREATE TABLE etablissement (
  id_eta serial NOT NULL,
  cle_eta varchar(32) NOT NULL default '',
  nom_eta text NOT NULL,
  PRIMARY KEY  (id_eta)
);

CREATE TABLE population (
  id_pop serial NOT NULL,
  cle_pop varchar(32) NOT NULL,
  nom_pop text NOT NULL,
  rang_pop int NOT NULL default '0',
  PRIMARY KEY  (id_pop)
);

CREATE TABLE service (
  id_serv serial NOT NULL,
  cle_serv varchar(32) NOT NULL,
  nom_serv text NOT NULL,
  rang_serv int NOT NULL,
  PRIMARY KEY  (id_serv)
);

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',
  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
);

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
);

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',
  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
);

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
);