
CREATE TABLE `library_vocabulary`
  (
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(256)  NOT NULL,
	PRIMARY KEY (`id`)
);

CREATE TABLE `library_word`
(
  `id`                    INTEGER NOT NULL AUTO_INCREMENT,
  `library_vocabulary_id` INTEGER NOT NULL,
  PRIMARY KEY (`id`),
  KEY `library_word_FI_1`(`library_vocabulary_id`),
  CONSTRAINT `library_word_FK_1`
  FOREIGN KEY (`library_vocabulary_id`)
  REFERENCES `library_vocabulary` (`id`)
    ON UPDATE RESTRICT
    ON DELETE RESTRICT
);

CREATE TABLE `library_word_i18n`
  (
	`id` INTEGER  NOT NULL,
	`culture` VARCHAR(7)  NOT NULL,
	`name` TEXT  NOT NULL,
	PRIMARY KEY (`id`,`culture`),
	CONSTRAINT `library_word_i18n_FK_1`
		FOREIGN KEY (`id`)
		REFERENCES `library_word` (`id`)
		ON UPDATE RESTRICT
		ON DELETE CASCADE
);

CREATE TABLE `library_document_collection`
  (
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(255),
	`workflow_id` INTEGER,
	`form` TEXT,
	`created_at` DATETIME,
	`updated_at` DATETIME,
	PRIMARY KEY (`id`),
	INDEX `library_document_collection_FI_1` (`workflow_id`),
	CONSTRAINT `library_document_collection_FK_1`
		FOREIGN KEY (`workflow_id`)
		REFERENCES `workflow` (`id`)
		ON UPDATE RESTRICT
		ON DELETE SET NULL
);

CREATE TABLE `library_document`
  (
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(255)  NOT NULL,
	`library_document_collection_id` INTEGER  NOT NULL,
	`hierarchy_id` INTEGER  NOT NULL,
	`datas` TEXT,
	`created_at` DATETIME,
	`updated_at` DATETIME,
	PRIMARY KEY (`id`),
	INDEX `library_document_FI_1` (`library_document_collection_id`),
	CONSTRAINT `library_document_FK_1`
		FOREIGN KEY (`library_document_collection_id`)
		REFERENCES `library_document_collection` (`id`)
		ON UPDATE RESTRICT
		ON DELETE RESTRICT,
	INDEX `library_document_FI_2` (`hierarchy_id`),
	CONSTRAINT `library_document_FK_2`
		FOREIGN KEY (`hierarchy_id`)
		REFERENCES `hierarchy` (`id`)
		ON UPDATE RESTRICT
		ON DELETE RESTRICT
);


ALTER TABLE  `library_document` ADD  `local_filename` VARCHAR( 255 ) NULL ;

ALTER TABLE  `library_word`
ADD	`notes` TEXT,
ADD	`definition` TEXT,
ADD	`candidate` INTEGER default 0,
ADD	`synonym` INTEGER default 0,
ADD	`parent_library_word_id` INTEGER;


ALTER TABLE  `library_word`
ADD KEY `library_word_FI_2`(`parent_library_word_id`);

ALTER TABLE  `library_word`
ADD 	CONSTRAINT `library_word_FK_2`
		FOREIGN KEY (`parent_library_word_id`)
		REFERENCES `library_word` (`id`)
		ON UPDATE RESTRICT
		ON DELETE RESTRICT;

ALTER TABLE `sf_guard_user` ADD COLUMN
    `is_validated` INTEGER default 0 NOT NULL AFTER `is_active`;

ALTER TABLE `sf_guard_user` ADD COLUMN
    `end_subscription` DATETIME default NULL AFTER `is_validated`;

UPDATE `sf_guard_user` SET
    `is_validated` = 1
WHERE 1;


CREATE TABLE `library_word_occurence`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`library_word_id` INTEGER,
	`library_document_id` INTEGER,
	PRIMARY KEY (`id`),
	KEY `library_word_occurence_FI_1`(`library_word_id`),
	KEY `library_word_occurence_FI_2`(`library_document_id`),
	CONSTRAINT `library_word_occurence_FK_1`
		FOREIGN KEY (`library_word_id`)
		REFERENCES `library_word` (`id`)
		ON UPDATE RESTRICT
		ON DELETE RESTRICT,
	CONSTRAINT `library_word_occurence_FK_2`
		FOREIGN KEY (`library_document_id`)
		REFERENCES `library_document` (`id`)
		ON UPDATE RESTRICT
		ON DELETE RESTRICT
);

CREATE TABLE `library_document_status`
(
  `id`   INTEGER      NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `library_document_status` (id, name)
VALUES (1, 'uploaded'), (2, 'being_indexed'), (3, 'valid');


ALTER TABLE `library_document`
  ADD COLUMN `creator_id` INTEGER default NULL AFTER id,
  ADD COLUMN `validator_id` INTEGER default NULL AFTER creator_id,
  ADD COLUMN `status_id` INTEGER NOT NULL DEFAULT 1,
  ADD COLUMN `published` TINYINT DEFAULT 0,
    ADD COLUMN `validated_at` DATETIME,
  ADD CONSTRAINT `library_document_fk3`
    FOREIGN KEY (`creator_id`)
    REFERENCES `sf_guard_user` (`id`)
    ON UPDATE RESTRICT
    ON DELETE RESTRICT,
  ADD CONSTRAINT `library_document_fk4`
    FOREIGN KEY (`validator_id`)
    REFERENCES `sf_guard_user` (`id`)
    ON UPDATE RESTRICT
    ON DELETE RESTRICT;

ALTER TABLE `library_document`
  ADD COLUMN `workflow_id` INTEGER default NULL AFTER id,
  ADD COLUMN `current_validation_level` VARCHAR(255),
ADD CONSTRAINT `library_document_fk5`
FOREIGN KEY (`workflow_id`)
REFERENCES `workflow` (`id`)
  ON UPDATE RESTRICT
  ON DELETE RESTRICT;


CREATE TABLE `library_document_selection_type`
(
  `id` INTEGER  NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255)  NOT NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO `library_document_selection_type` (id, name)
VALUES (1, 'cart'), (2, 'user_selection'), (3, 'workflow_selection');

CREATE TABLE `library_document_selection`
(
  `id` INTEGER  NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255)  NOT NULL,
  `type_id` INTEGER  NOT NULL,
  `user_id` INTEGER  DEFAULT NULL,
  `hierarchy_id` INTEGER  DEFAULT NULL,
  `validation_level` VARCHAR(255),
  `created_at` DATETIME,
  `updated_at` DATETIME,
  PRIMARY KEY (`id`),
  INDEX `library_document_selection_FI_1` (`type_id`),
  CONSTRAINT `library_document_selection_FK_1`
  FOREIGN KEY (`type_id`)
  REFERENCES `library_document_selection_type` (`id`)
    ON UPDATE RESTRICT
    ON DELETE RESTRICT,
  INDEX `library_document_selection_FI_2` (`user_id`),
  CONSTRAINT `library_document_selection_FK_2`
  FOREIGN KEY (`user_id`)
  REFERENCES `sf_guard_user` (`id`)
    ON UPDATE RESTRICT
    ON DELETE RESTRICT,
  INDEX `library_document_selection_FI_3` (`hierarchy_id`),
  CONSTRAINT `library_document_selection_FK_3`
  FOREIGN KEY (`hierarchy_id`)
  REFERENCES `hierarchy` (`id`)
    ON UPDATE RESTRICT
    ON DELETE RESTRICT
);


CREATE TABLE `library_document_selection_library_document`
(
  `id` INTEGER  NOT NULL AUTO_INCREMENT,
  `selection_id` INTEGER  NOT NULL,
  `library_document_id` INTEGER  NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `library_document_selection_library_document_FI_1` (`selection_id`),
  CONSTRAINT `library_document_selection_library_document_FK_1`
  FOREIGN KEY (`selection_id`)
  REFERENCES `library_document_selection` (`id`)
    ON UPDATE RESTRICT
    ON DELETE RESTRICT,
  INDEX `library_document_selection_library_document_FI_2` (`library_document_id`),
  CONSTRAINT `library_document_selection_library_document_FK_2`
  FOREIGN KEY (`library_document_id`)
  REFERENCES `library_document` (`id`)
    ON UPDATE RESTRICT
    ON DELETE RESTRICT
);

ALTER TABLE `library_document` ADD `label` VARCHAR(255) NULL DEFAULT NULL;

CREATE TABLE `library_document_file`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`library_document_id` INTEGER  NOT NULL,
	`type` VARCHAR(255)  NOT NULL,
	`url` TEXT,
	`label` VARCHAR(255),
	`created_at` DATETIME,
	`updated_at` DATETIME,
	PRIMARY KEY (`id`),
	INDEX `library_document_file_FI_1` (`library_document_id`),
	CONSTRAINT `library_document_file_FK_1`
		FOREIGN KEY (`library_document_id`)
		REFERENCES `library_document` (`id`)
		ON UPDATE RESTRICT
		ON DELETE RESTRICT
);

ALTER TABLE `library_document_file` ADD `metadata` TEXT;

ALTER TABLE `library_document_selection` ADD `active` TINYINT DEFAULT 0;

ALTER TABLE `library_vocabulary` ADD `thesaurus` INT NOT NULL DEFAULT '0' ;

ALTER TABLE `library_document_file` ADD `primary_file` INT NOT NULL DEFAULT '0' ;

ALTER TABLE `library_document_file` ADD `name` VARCHAR(255) NULL DEFAULT NULL;

CREATE TABLE `library_document_cart_selection_status`
(
  `id` INTEGER  NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255)  NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `library_document_cart_selection_status` (id, name)
VALUES (1, 'in_cart'), (2, 'awaiting_validation'), (3, 'validated');

ALTER TABLE `library_document_selection` ADD `cart_status_id` INT DEFAULT NULL;
ALTER TABLE `library_document_selection` ADD `message` TEXT DEFAULT NULL;
ALTER TABLE `library_document_selection` ADD `download_type` VARCHAR(64) DEFAULT NULL;
ALTER TABLE `library_document_selection` ADD `download_definition` VARCHAR(64) DEFAULT NULL;
ALTER TABLE `library_document_selection` ADD `current_validation_level` VARCHAR(64) DEFAULT NULL;
ALTER TABLE `library_document_selection` ADD `workflow_id` INT DEFAULT NULL;

CREATE TABLE `library_document_download_workflow`
(
  `id` INTEGER  NOT NULL AUTO_INCREMENT,
  `hierarchy_id` INTEGER  NOT NULL,
  `workflow_id` INTEGER,
  PRIMARY KEY (`id`),
  INDEX `library_document_download_workflow_FI_1` (`hierarchy_id`),
  CONSTRAINT `library_document_download_workflow_FK_1`
  FOREIGN KEY (`hierarchy_id`)
  REFERENCES `hierarchy` (`id`)
    ON UPDATE RESTRICT
    ON DELETE RESTRICT,
  INDEX `library_document_download_workflow_FI_2` (`workflow_id`),
  CONSTRAINT `library_document_download_workflow_FK_2`
  FOREIGN KEY (`workflow_id`)
  REFERENCES `workflow` (`id`)
    ON UPDATE RESTRICT
    ON DELETE SET NULL
);

ALTER TABLE `library_document_file` ADD `data` TEXT NULL , ADD `reference` VARCHAR(13) NULL;

CREATE TABLE `library_document_history`
(
  `id` INTEGER  NOT NULL AUTO_INCREMENT,
  `library_document_id` INTEGER  NOT NULL,
  `user_id` INTEGER  NOT NULL,
  `action` VARCHAR(255),
  `date` DATETIME,
  PRIMARY KEY (`id`),
  INDEX `library_document_history_FI_1` (`library_document_id`),
  CONSTRAINT `library_document_history_FK_1`
  FOREIGN KEY (`library_document_id`)
  REFERENCES `library_document` (`id`)
    ON UPDATE RESTRICT
    ON DELETE RESTRICT,
  INDEX `library_document_history_FI_2` (`user_id`),
  CONSTRAINT `library_document_history_FK_2`
  FOREIGN KEY (`user_id`)
  REFERENCES `sf_guard_user` (`id`)
    ON UPDATE RESTRICT
    ON DELETE RESTRICT
);

ALTER TABLE library_document_selection ADD isPublic INTEGER NULL;

CREATE TABLE `library_document_collection_link`
(
	`library_document_collection_source_id` INTEGER  NOT NULL,
	`library_document_collection_target_id` INTEGER  NOT NULL,
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	PRIMARY KEY (`id`),
	INDEX `library_document_collection_link_FI_1` (`library_document_collection_source_id`),
	CONSTRAINT `library_document_collection_link_FK_1`
		FOREIGN KEY (`library_document_collection_source_id`)
		REFERENCES `library_document_collection` (`id`)
		ON UPDATE RESTRICT
		ON DELETE RESTRICT,
	INDEX `library_document_collection_link_FI_2` (`library_document_collection_target_id`),
	CONSTRAINT `library_document_collection_link_FK_2`
		FOREIGN KEY (`library_document_collection_target_id`)
		REFERENCES `library_document_collection` (`id`)
		ON UPDATE RESTRICT
		ON DELETE RESTRICT
);


INSERT INTO library_document_selection_type (name) VALUES ('rejected');

ALTER TABLE  `library_word` ADD  `created_by` VARCHAR( 255 ) NOT NULL ,
ADD  `created_at` DATETIME NOT NULL;

ALTER TABLE  `library_word` ADD  `validated_by` INT NOT NULL ,
ADD  `validated_at` DATETIME NOT NULL;

CREATE TABLE `library_document_link`
(
	`library_document_source_id` INTEGER  NOT NULL,
	`library_document_target_id` INTEGER  NOT NULL,
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	PRIMARY KEY (`id`),
	INDEX `library_document_link_FI_1` (`library_document_source_id`),
	CONSTRAINT `library_document_link_FK_1`
		FOREIGN KEY (`library_document_source_id`)
		REFERENCES `library_document` (`id`)
		ON UPDATE RESTRICT
		ON DELETE CASCADE,
	INDEX `library_document_link_FI_2` (`library_document_target_id`),
	CONSTRAINT `library_document_link_FK_2`
		FOREIGN KEY (`library_document_target_id`)
		REFERENCES `library_document` (`id`)
		ON UPDATE RESTRICT
		ON DELETE CASCADE
);

ALTER TABLE `library_document_link` ADD `library_document_collection_name` VARCHAR(512)  NOT NULL;

ALTER TABLE  `library_document_file` ADD  `wm_reference` VARCHAR( 13 ) NOT NULL ;

ALTER TABLE library_document_selection ADD translation_status INT;
ALTER TABLE library_document_selection ADD translation_request_date DATETIME;



CREATE TABLE `library_document_selection_user`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`selection_id` INTEGER  NOT NULL,
	`user_id` INTEGER  NOT NULL,
	PRIMARY KEY (`id`),
	INDEX `library_document_selection_user_FI_1` (`selection_id`),
	CONSTRAINT `library_document_selection_user_FK_1`
		FOREIGN KEY (`selection_id`)
		REFERENCES `library_document_selection` (`id`)
		ON UPDATE RESTRICT
		ON DELETE RESTRICT,
	INDEX `library_document_selection_user_FI_2` (`user_id`),
	CONSTRAINT `library_document_selection_user_FK_2`
		FOREIGN KEY (`user_id`)
		REFERENCES `sf_guard_user` (`id`)
		ON UPDATE RESTRICT
		ON DELETE RESTRICT
);

CREATE TABLE `library_document_selection_history`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`library_document_selection_id` INTEGER  NOT NULL,
	`user_id` INTEGER  NOT NULL,
	`action` VARCHAR(255),
	`date` DATETIME,
	PRIMARY KEY (`id`),
	INDEX `library_document_selection_history_FI_1` (`library_document_selection_id`),
	CONSTRAINT `library_document_selection_history_FK_1`
		FOREIGN KEY (`library_document_selection_id`)
		REFERENCES `library_document_selection` (`id`)
		ON UPDATE CASCADE
		ON DELETE CASCADE,
	INDEX `library_document_selection_history_FI_2` (`user_id`),
	CONSTRAINT `library_document_selection_history_FK_2`
		FOREIGN KEY (`user_id`)
		REFERENCES `sf_guard_user` (`id`)
		ON UPDATE RESTRICT
		ON DELETE RESTRICT
);

ALTER TABLE `library_document_selection_library_document` ADD `library_document_download_availability` BOOLEAN DEFAULT 0;

ALTER TABLE `library_document` ADD `last_compute_date` DATETIME NOT NULL DEFAULT '1985-07-03 16:00:00';

CREATE TABLE `library_document_selection_download_link`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(255)  NOT NULL,
	`user_id` INTEGER  NOT NULL,
	`download_type` VARCHAR(64),
	`keyHash` VARCHAR(255),
	`date` DATETIME,
	`expire` DATETIME,
	PRIMARY KEY (`id`),
	INDEX `library_document_selection_download_link_FI_1` (`user_id`),
	CONSTRAINT `library_document_selection_download_link_FK_1`
		FOREIGN KEY (`user_id`)
		REFERENCES `sf_guard_user` (`id`)
);

CREATE TABLE `library_document_selection_download_link_library_document`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`download_link_id` INTEGER  NOT NULL,
	`library_document_id` INTEGER  NOT NULL,
	PRIMARY KEY (`id`),
	INDEX `library_document_selection_download_link_library_document_FI_1` (`download_link_id`),
	CONSTRAINT `library_document_selection_download_link_library_document_FK_1`
		FOREIGN KEY (`download_link_id`)
		REFERENCES `library_document_selection_download_link` (`id`),
	INDEX `library_document_selection_download_link_library_document_FI_2` (`library_document_id`),
	CONSTRAINT `library_document_selection_download_link_library_document_FK_2`
		FOREIGN KEY (`library_document_id`)
		REFERENCES `library_document` (`id`)
);

CREATE TABLE `library_document_search_form`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`userId` INTEGER  NOT NULL,
	`name` VARCHAR(512)  NOT NULL,
	`searchType` VARCHAR(512)  NOT NULL,
	`documentCollection` VARCHAR(512)  NOT NULL,
	`formDatas` TEXT,
	`created_at` DATETIME,
	`updated_at` DATETIME,
	`deleted_at` DATETIME,
	`is_deleted` INTEGER default 0,
	PRIMARY KEY (`id`),
	INDEX `library_document_search_form_FI_1` (`userId`),
	CONSTRAINT `library_document_search_form_FK_1`
		FOREIGN KEY (`userId`)
		REFERENCES `sf_guard_user` (`id`)
		ON UPDATE RESTRICT
		ON DELETE CASCADE
);

ALTER TABLE `library_document_collection` ADD `form_summary` TEXT NULL DEFAULT NULL AFTER `form`;
ALTER TABLE `library_document_collection` CHANGE `form_summary` `summary` TEXT NULL DEFAULT NULL;
ALTER TABLE `sf_guard_user_profile` ADD `sftp_password` VARCHAR(13) NULL AFTER `updated_at`;

ALTER TABLE `library_document_collection` ADD `expiration` TEXT NULL DEFAULT NULL AFTER `form`;