-- ---
-- Globals
-- ---

-- SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
-- SET FOREIGN_KEY_CHECKS=0;

-- ---
-- Table 'profiles'
-- {"label":"Profils"}
-- ---

		
CREATE TABLE `profiles` (
  `id` TINYINT NOT NULL COMMENT '{"lowest":true}',
  `name` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '{"label":"Nom"}',
  `props` SMALLINT NOT NULL DEFAULT 0 COMMENT '{"label":"Propriétés"}',
  PRIMARY KEY (`id`)
) COMMENT '{"label":"Profils"}';

-- ---
-- Table 'users'
-- {"label":"Utilisateurs"}
-- ---

		
CREATE TABLE `users` (
  `id` SMALLINT NOT NULL COMMENT '{"lowest":true}',
  `profile` TINYINT NOT NULL COMMENT '{"label":"Profil"}',
  `structure` TINYINT NOT NULL COMMENT '{"label":"Structure"}',
  `user` VARCHAR(32) NOT NULL COMMENT '{"label":"Utilisateur"}',
  `pass` VARCHAR(32) NOT NULL COMMENT '{"label":"Mot de passe"}',
  `full` VARCHAR(64) NOT NULL COMMENT '{"label":"Nom complet"}',
  `mail` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '{"label":"Adresse courriel"}',
  `tel` VARCHAR(24) NOT NULL DEFAULT '' COMMENT '{"label":"Téléphone"}',
  PRIMARY KEY (`id`)
) COMMENT '{"label":"Utilisateurs"}';

-- ---
-- Table 'logins'
-- {"label":"Logins"}
-- ---

		
CREATE TABLE `logins` (
  `id` SMALLINT NOT NULL COMMENT '{"label":"Utilisateur"}',
  `ip` VARCHAR(40) NOT NULL COMMENT '{"label":"Dernière adresse IP"}',
  `ua` VARCHAR(256) NOT NULL COMMENT '{"label":"User-agent"}',
  `last_time` INT NOT NULL DEFAULT 0 COMMENT '{"label":"Dernière connexion"}',
  `auth` CHAR(32) NOT NULL COMMENT '{"label":"Authentification"}',
  PRIMARY KEY (`id`, `ip`, `ua`)
) DEFAULT CHARSET=ascii COMMENT '{"label":"Logins"}';

-- ---
-- Table 'structures'
-- {"label":"Structures"}
-- ---

		
CREATE TABLE `structures` (
  `id` TINYINT NOT NULL COMMENT '{"lowest":true}',
  `name` VARCHAR(64) NOT NULL COMMENT '{"label":"Nom"}',
  `logo` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '{"label":"Image logo"}',
  `bgcolor` CHAR(6) NOT NULL DEFAULT '' COMMENT '{"label":"Couleur de fond"}',
  `fgcolor` CHAR(6) NOT NULL DEFAULT '' COMMENT '{"label":"Couleur de texte"}',
  PRIMARY KEY (`id`)
) COMMENT '{"label":"Structures"}';

-- ---
-- Table 'apprentices'
-- {"label":"Apprentis"}
-- ---

		
CREATE TABLE `apprentices` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `structure` TINYINT NOT NULL COMMENT '{"label":"Structure"}',
  `name` VARCHAR(64) NOT NULL COMMENT '{"label":"Nom"}',
  `firstname` VARCHAR(32) NOT NULL COMMENT '{"label":"Prénom"}',
  `use_fem` BOOL NOT NULL DEFAULT FALSE COMMENT '{"label":"Utilise le féminin"}',
  `birthday` DATE NOT NULL COMMENT '{"label":"Date de naissance"}',
  `start` DATE NULL DEFAULT NULL COMMENT '{"label":"Début de contrat"}',
  PRIMARY KEY (`id`)
) COMMENT '{"label":"Apprentis"}';

-- ---
-- Table 'sections'
-- {"label":"Sections"}
-- ---

		
CREATE TABLE `sections` (
  `id` SMALLINT NOT NULL COMMENT '{"lowest":true}',
  `parent` SMALLINT NULL DEFAULT NULL COMMENT '{"label":"Section parent"}',
  `rank` TINYINT NOT NULL COMMENT '{"label":"Rang"}',
  `title` VARCHAR(256) NOT NULL COMMENT '{"label":"Titre"}',
  `details` VARCHAR(256) NOT NULL DEFAULT '' COMMENT '{"label":"Détails"}',
  `bgcolor` CHAR(6) NOT NULL DEFAULT '' COMMENT '{"label":"Couleur de fond"}',
  `fgcolor` CHAR(6) NOT NULL DEFAULT '' COMMENT '{"label":"Couleur de texte"}',
  `is_old` BOOL NOT NULL DEFAULT FALSE COMMENT '{"label":"Section abandonnée"}',
  PRIMARY KEY (`id`)
) COMMENT '{"label":"Sections"}';

-- ---
-- Table 'questions'
-- {"label":"Questions"}
-- ---

		
CREATE TABLE `questions` (
  `id` SMALLINT NOT NULL COMMENT '{"lowest":true}',
  `section` SMALLINT NOT NULL COMMENT '{"label":"Section parent"}',
  `rank` TINYINT NOT NULL COMMENT '{"label":"Rang"}',
  `csvcol` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '{"label":"Colonne CSV"}',
  `label` VARCHAR(256) NOT NULL DEFAULT '' COMMENT '{"label":"Libellé"}',
  `bgcolor` CHAR(6) NOT NULL DEFAULT '' COMMENT '{"label":"Couleur de fond"}',
  `fgcolor` CHAR(6) NOT NULL DEFAULT '' COMMENT '{"label":"Couleur de texte"}',
  `choice_list` SMALLINT NOT NULL DEFAULT 0 COMMENT '{"label":"Liste de choix"}',
  `text` BOOL NOT NULL DEFAULT FALSE COMMENT '{"label":"Avec texte"}',
  `is_old` BOOL NOT NULL DEFAULT FALSE COMMENT '{"label":"Question abandonnée"}',
  PRIMARY KEY (`id`)
) COMMENT '{"label":"Questions"}';

-- ---
-- Table 'choice_lists'
-- {"label":"Listes de choix"}
-- ---

		
CREATE TABLE `choice_lists` (
  `id` SMALLINT NOT NULL COMMENT '{"label":"Choix (0:global)"}',
  `name` VARCHAR(64) NOT NULL COMMENT '{"label":"Nom du choix"}',
  `threshold` TINYINT NOT NULL DEFAULT 0 COMMENT '{"label":"Seuil"}',
  PRIMARY KEY (`id`)
) COMMENT '{"label":"Listes de choix"}';

-- ---
-- Table 'choices'
-- {"label":"Choix"}
-- ---

		
CREATE TABLE `choices` (
  `list` SMALLINT NOT NULL COMMENT '{"lowest":true}',
  `rank` TINYINT NOT NULL COMMENT '{"label":"Rang"}',
  `label` VARCHAR(256) NOT NULL COMMENT '{"label":"Libellé"}',
  PRIMARY KEY (`list`, `rank`)
) COMMENT '{"label":"Choix"}';

-- ---
-- Table 'adapts'
-- {"label":"Adaptations"}
-- ---

		
CREATE TABLE `adapts` (
  `id` SMALLINT NOT NULL COMMENT '{"lowest":true}',
  `parent` SMALLINT NULL DEFAULT NULL COMMENT '{"label":"Parent"}',
  `rank` TINYINT NOT NULL COMMENT '{"label":"Rang"}',
  `label` VARCHAR(256) NOT NULL COMMENT '{"label":"Titre"}',
  `bgcolor` CHAR(6) NOT NULL DEFAULT '' COMMENT '{"label":"Couleur de fond"}',
  `fgcolor` CHAR(6) NOT NULL DEFAULT '' COMMENT '{"label":"Couleur de texte"}',
  `is_old` BOOL NOT NULL DEFAULT FALSE COMMENT '{"label":"Choix abandonné"}',
  PRIMARY KEY (`id`)
) COMMENT '{"label":"Adaptations"}';

-- ---
-- Table 'answers'
-- {"label":"Réponses"}
-- ---

		
CREATE TABLE `answers` (
  `apprentice` INT NOT NULL COMMENT '{"label":"Apprenti"}',
  `question` SMALLINT NOT NULL COMMENT '{"label":"Question"}',
  `choice` SMALLINT NOT NULL DEFAULT 0 COMMENT '{"label":"Choix"}',
  `text` MEDIUMTEXT NOT NULL DEFAULT '' COMMENT '{"label":"Réponse ou observation"}',
  `entry_date` DATE NOT NULL COMMENT '{"label":"Date de saisie"}',
  `entry_user` SMALLINT NOT NULL COMMENT '{"label":"Acteur de saisie"}',
  PRIMARY KEY (`apprentice`, `question`, `entry_date`)
) COMMENT '{"label":"Réponses"}';

-- ---
-- Table 'needs'
-- {"label":"Besoins apprenti"}
-- ---

		
CREATE TABLE `needs` (
  `apprentice` INT NOT NULL COMMENT '{"label":"Apprenti"}',
  `adapt` SMALLINT NOT NULL COMMENT '{"label":"Adaptation"}',
  `need` BOOL NOT NULL DEFAULT FALSE COMMENT '{"label":"Nécessaire ?"}',
  `details` MEDIUMTEXT NOT NULL DEFAULT '' COMMENT '{"label":"Précisions"}',
  `entry_date` DATE NOT NULL COMMENT '{"label":"Date de saisie"}',
  `entry_user` SMALLINT NOT NULL COMMENT '{"label":"Acteur de saisie"}',
  PRIMARY KEY (`apprentice`, `adapt`, `entry_date`)
) COMMENT '{"label":"Besoins apprenti"}';

-- ---
-- Table 'locks'
-- {"label":"Verrous saisie apprenti"}
-- ---

		
CREATE TABLE `locks` (
  `apprentice` INT NOT NULL COMMENT '{"label":"Apprenti"}',
  `entry_user` SMALLINT NOT NULL COMMENT '{"label":"Acteur de saisie"}',
  `lock_time` DATETIME NOT NULL COMMENT '{"label":"Heure de pose du verrou"}',
  `last_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '{"label":"Heure de dernière activité"}',
  PRIMARY KEY (`apprentice`)
) DEFAULT CHARSET=ascii COMMENT '{"label":"Verrous saisie apprenti"}';

-- ---
-- Foreign Keys 
-- ---

ALTER TABLE `users` ADD FOREIGN KEY (profile) REFERENCES `profiles` (`id`);
ALTER TABLE `users` ADD FOREIGN KEY (structure) REFERENCES `structures` (`id`);
ALTER TABLE `logins` ADD FOREIGN KEY (id) REFERENCES `users` (`id`);
ALTER TABLE `apprentices` ADD FOREIGN KEY (structure) REFERENCES `structures` (`id`);
ALTER TABLE `sections` ADD FOREIGN KEY (parent) REFERENCES `sections` (`id`);
ALTER TABLE `questions` ADD FOREIGN KEY (section) REFERENCES `sections` (`id`);
ALTER TABLE `questions` ADD FOREIGN KEY (choice_list) REFERENCES `choice_lists` (`id`);
ALTER TABLE `choices` ADD FOREIGN KEY (list) REFERENCES `choice_lists` (`id`);
ALTER TABLE `adapts` ADD FOREIGN KEY (parent) REFERENCES `adapts` (`id`);
ALTER TABLE `answers` ADD FOREIGN KEY (apprentice) REFERENCES `apprentices` (`id`);
ALTER TABLE `answers` ADD FOREIGN KEY (question) REFERENCES `questions` (`id`);
ALTER TABLE `answers` ADD FOREIGN KEY (entry_user) REFERENCES `users` (`id`);
ALTER TABLE `needs` ADD FOREIGN KEY (apprentice) REFERENCES `apprentices` (`id`);
ALTER TABLE `needs` ADD FOREIGN KEY (adapt) REFERENCES `adapts` (`id`);
ALTER TABLE `needs` ADD FOREIGN KEY (entry_user) REFERENCES `users` (`id`);
ALTER TABLE `locks` ADD FOREIGN KEY (apprentice) REFERENCES `apprentices` (`id`);
ALTER TABLE `locks` ADD FOREIGN KEY (entry_user) REFERENCES `users` (`id`);

-- ---
-- Table Properties
-- ---

-- ALTER TABLE `profiles` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ALTER TABLE `users` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ALTER TABLE `logins` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ALTER TABLE `structures` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ALTER TABLE `apprentices` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ALTER TABLE `sections` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ALTER TABLE `questions` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ALTER TABLE `choice_lists` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ALTER TABLE `choices` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ALTER TABLE `adapts` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ALTER TABLE `answers` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ALTER TABLE `needs` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ALTER TABLE `locks` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ---
-- Test Data
-- ---

-- INSERT INTO `profiles` (`id`,`name`,`props`) VALUES
-- ('','','');
-- INSERT INTO `users` (`id`,`profile`,`structure`,`user`,`pass`,`full`,`mail`,`tel`) VALUES
-- ('','','','','','','','');
-- INSERT INTO `logins` (`id`,`ip`,`ua`,`last_time`,`auth`) VALUES
-- ('','','','','');
-- INSERT INTO `structures` (`id`,`name`,`logo`,`bgcolor`,`fgcolor`) VALUES
-- ('','','','','');
-- INSERT INTO `apprentices` (`id`,`structure`,`name`,`firstname`,`use_fem`,`birthday`,`start`) VALUES
-- ('','','','','','','');
-- INSERT INTO `sections` (`id`,`parent`,`rank`,`title`,`details`,`bgcolor`,`fgcolor`,`is_old`) VALUES
-- ('','','','','','','','');
-- INSERT INTO `questions` (`id`,`section`,`rank`,`csvcol`,`label`,`bgcolor`,`fgcolor`,`choice_list`,`text`,`is_old`) VALUES
-- ('','','','','','','','','','');
-- INSERT INTO `choice_lists` (`id`,`name`,`threshold`) VALUES
-- ('','','');
-- INSERT INTO `choices` (`list`,`rank`,`label`) VALUES
-- ('','','');
-- INSERT INTO `adapts` (`id`,`parent`,`rank`,`label`,`bgcolor`,`fgcolor`,`is_old`) VALUES
-- ('','','','','','','');
-- INSERT INTO `answers` (`apprentice`,`question`,`choice`,`text`,`entry_date`,`entry_user`) VALUES
-- ('','','','','','');
-- INSERT INTO `needs` (`apprentice`,`adapt`,`need`,`details`,`entry_date`,`entry_user`) VALUES
-- ('','','','','','');
-- INSERT INTO `locks` (`apprentice`,`entry_user`,`lock_time`,`last_time`) VALUES
-- ('','','','');
