/*
Mise ajour Dolibarr

------------------
*/

create table llx_accountingsystem_det
(
  rowid           integer AUTO_INCREMENT PRIMARY KEY,
  fk_pcg_version  varchar(12)  NOT NULL,
  pcg_type        varchar(20)  NOT NULL,
  pcg_subtype     varchar(20)  NOT NULL,
  label           varchar(128) NOT NULL,
  account_number  varchar(20)  NOT NULL,
  account_parent  varchar(20)
)type=innodb;


ALTER TABLE llx_accountingsystem_det ADD INDEX idx_accountingsystem_det_fk_pcg_version (fk_pcg_version);


ALTER TABLE llx_accountingsystem_det ADD CONSTRAINT fk_accountingsystem_det_fk_pcg_version  FOREIGN KEY (fk_pcg_version)    REFERENCES llx_accountingsystem (pcg_version);



create table llx_accountingsystem
(
  pcg_version       varchar(12)     PRIMARY KEY,
  fk_pays           integer         NOT NULL,
  label             varchar(128)    NOT NULL,
  datec             varchar(12)     NOT NULL,
  fk_author         varchar(20),
  tms               timestamp,
  active            tinyint        NOT NULL   DEFAULT '0'
)type=innodb;

ALTER TABLE `llx_action_def` ADD `code` VARCHAR( 24 ) NOT NULL;

ALTER TABLE `llx_bank_account` ADD `rappro` SMALLINT DEFAULT '1' NOT NULL ;

ALTER TABLE `llx_bank_url` ADD `type` ENUM( "company", "payment", "member", "donation", "charge" ) NOT NULL ;

ALTER TABLE `llx_bookmark` ADD `url` VARCHAR( 128 ) NOT NULL ,
ADD `target` VARCHAR( 16 ) ,
ADD `title` VARCHAR( 64 ) ,
ADD `favicon` VARCHAR( 24 ) ;

create table llx_categorie_association
(
  fk_categorie_mere   integer NOT NULL,
  fk_categorie_fille  integer NOT NULL
)type=innodb;
ALTER TABLE llx_categorie_association ADD INDEX idx_categorie_association_fk_categorie_mere (fk_categorie_mere);
ALTER TABLE llx_categorie_association ADD INDEX idx_categorie_association_fk_categorie_fille (fk_categorie_fille);

ALTER TABLE llx_categorie_association ADD CONSTRAINT fk_categorie_asso_fk_categorie_mere  FOREIGN KEY (fk_categorie_mere) REFERENCES llx_categorie (rowid);
ALTER TABLE llx_categorie_association ADD CONSTRAINT fk_categorie_asso_fk_categorie_fille FOREIGN KEY (fk_categorie_fille) REFERENCES llx_categorie (rowid);

create table llx_categorie_product
(
  fk_categorie  integer NOT NULL,
  fk_product    integer NOT NULL
)type=innodb;
ALTER TABLE llx_categorie_product ADD INDEX idx_categorie_product_fk_categorie (fk_categorie);
ALTER TABLE llx_categorie_product ADD INDEX idx_categorie_product_fk_product (fk_product);

ALTER TABLE llx_categorie_product ADD FOREIGN KEY (fk_categorie) REFERENCES llx_categorie (rowid);
ALTER TABLE llx_categorie_product ADD FOREIGN KEY (fk_product) REFERENCES llx_product (rowid);

create table llx_categorie
(
	rowid 		integer AUTO_INCREMENT PRIMARY KEY,
	label 		VARCHAR(255),
	description 	text	
)type=innodb;

ALTER TABLE `llx_commande` ADD `ref_client` VARCHAR( 30 ) NOT NULL ;
ALTER TABLE `llx_cond_reglement` CHANGE `actif` `active` TINYINT( 4 ) DEFAULT '1';
ALTER TABLE `llx_contrat` ADD `ref` VARCHAR( 30 ) NOT NULL ;
ALTER TABLE `llx_c_pays` ADD `code_iso` VARCHAR( 3 )  ;

CREATE TABLE llx_c_type_contact(
rowid integer PRIMARY KEY ,
element varchar( 30 ) NOT NULL ,
source varchar( 8 ) DEFAULT 'external' NOT NULL ,
code varchar( 16 ) NOT NULL ,
libelle varchar( 64 ) NOT NULL ,
active tinyint DEFAULT "1" NOT NULL
) TYPE = innodb;

CREATE TABLE llx_dolibarr_modules(
numero integer PRIMARY KEY ,
active tinyint DEFAULT '0' NOT NULL ,
active_date datetime NOT NULL ,
active_version varchar( 25 ) NOT NULL
) TYPE = innodb;

CREATE TABLE llx_element_contact(
rowid integer AUTO_INCREMENT PRIMARY KEY ,
datecreate datetime NULL ,
statut smallint DEFAULT '5',
element_id int NOT NULL ,
fk_c_type_contact int NOT NULL ,
fk_socpeople integer NOT NULL
) TYPE = innodb;

CREATE TABLE llx_energie_compteur_groupe(
fk_energie_compteur integer NOT NULL ,
fk_energie_groupe integer NOT NULL
) TYPE = innodb;

CREATE TABLE llx_energie_compteur_releve(
rowid integer AUTO_INCREMENT PRIMARY KEY ,
fk_compteur integer NOT NULL ,
date_releve datetime,
valeur real,
datec datetime,
fk_user_author integer NOT NULL ,
note text
) TYPE = innodb;

CREATE TABLE llx_energie_compteur(
rowid integer AUTO_INCREMENT PRIMARY KEY ,
libelle varchar( 50 ) ,
fk_energie integer NOT NULL ,
datec datetime,
fk_user_author integer NOT NULL ,
note text
) TYPE = innodb;

CREATE TABLE llx_energie_groupe(
rowid integer AUTO_INCREMENT PRIMARY KEY ,
libelle varchar( 100 ) ,
datec datetime,
fk_user_author integer NOT NULL ,
note text
) TYPE = innodb;

ALTER TABLE `llx_entrepot` ADD `lieu` VARCHAR( 64 ) ,
ADD `address` VARCHAR( 255 ) ,
ADD `cp` VARCHAR( 10 ) ,
ADD `ville` VARCHAR( 50 ) ,
ADD `fk_pays` INT DEFAULT '0' NOT NULL ;

ALTER TABLE `llx_facturedet` ADD `rang` INT DEFAULT '0' NOT NULL ;
ALTER TABLE `llx_facture_fourn` ADD `fk_projet` INT,
ADD `fk_cond_reglement` INT DEFAULT '1' NOT NULL ,
ADD `date_lim_reglement` DATE;

ALTER TABLE `llx_facture_rec` ADD `frequency` CHAR( 2 ) NOT NULL ,
ADD `last_gen` VARCHAR( 7 ) NOT NULL ;

ALTER TABLE `llx_facture` ADD `ref_client` VARCHAR( 30 ) ,
ADD `date_valid` DATE,
ADD `model` VARCHAR( 50 ) ;

ALTER TABLE `llx_mailing_cibles` ADD `statut` SMALLINT DEFAULT '0' NOT NULL ,
ADD `url` VARCHAR( 160 ) ,
ADD `date_envoi` DATETIME NOT NULL ;

CREATE TABLE llx_paiementfourn_facturefourn(
rowid integer AUTO_INCREMENT PRIMARY KEY ,
fk_paiementfourn INT( 11 ) DEFAULT NULL ,
fk_facturefourn INT( 11 ) DEFAULT NULL ,
amount DOUBLE DEFAULT '0'
) TYPE = innodb;

CREATE TABLE llx_prelevement_notifications(
rowid integer AUTO_INCREMENT PRIMARY KEY ,
fk_user integer NOT NULL ,
ACTION varchar( 2 )
) TYPE = innodb;

ALTER TABLE `llx_prelevement_rejet` ADD `afacturer` TINYINT DEFAULT '0' NOT NULL ,
ADD `fk_facture` INT;

CREATE TABLE llx_product_fournisseur_price_log(
rowid integer AUTO_INCREMENT PRIMARY KEY ,
datec datetime,
fk_product integer,
fk_soc integer,
price real,
quantity real,
fk_user integer
) TYPE = innodb;

ALTER TABLE llx_product_fournisseur_price ADD INDEX idx_product_fournisseur_price_fk_user (fk_user);
ALTER TABLE llx_product_fournisseur_price ADD INDEX idx_product_fournisseur_price_fk_soc (fk_soc);
ALTER TABLE llx_product_fournisseur_price ADD INDEX idx_product_fournisseur_price_fk_product (fk_product);

ALTER TABLE llx_product_fournisseur_price ADD CONSTRAINT fk_product_fournisseur_price_fk_user    FOREIGN KEY (fk_user)    REFERENCES llx_user (rowid);
ALTER TABLE llx_product_fournisseur_price ADD CONSTRAINT fk_product_fournisseur_price_fk_soc     FOREIGN KEY (fk_soc)     REFERENCES llx_societe (idp);
ALTER TABLE llx_product_fournisseur_price ADD CONSTRAINT fk_product_fournisseur_price_fk_product FOREIGN KEY (fk_product) REFERENCES llx_product (rowid);


ALTER TABLE `llx_product` ADD `note` TINYINT;

create table llx_projet_task_actors
(
  fk_projet_task integer NOT NULL,
  fk_user        integer NOT NULL,
  role           enum ('admin','read','acto','info') DEFAULT 'admin',

  UNIQUE (fk_projet_task, fk_user),
  key (role)

)type=innodb;


create table llx_projet_task
(
  rowid              integer AUTO_INCREMENT PRIMARY KEY,
  fk_projet          integer NOT NULL,
  fk_task_parent     integer NOT NULL,
  title              varchar(255),
  duration_effective real NOT NULL,
  fk_user_creat      integer,      -- createur
  statut             enum('open','closed') DEFAULT 'open',
  note               text,

  key(fk_projet),
  key(statut),
  key(fk_user_creat)
  
)type=innodb;

create table llx_projet_task_time
(
  rowid            integer AUTO_INCREMENT PRIMARY KEY,
  fk_task          integer  NOT NULL,
  task_date        date,
  task_duration    double,
  fk_user          integer,
  note             text,

  key(fk_task),
  key(fk_user)

)type=innodb;


ALTER TABLE `llx_propal` ADD `total_ht` DOUBLE DEFAULT '0' NOT NULL ,
ADD `fk_cond_reglement` INT NOT NULL ,
ADD `fk_mode_reglement` INT NOT NULL ;

create table llx_societe_consult
(
  fk_soc             integer,
  fk_user            integer,
  datec	             timestamp,
  action             enum('w','r')
)type=innodb;

CREATE TABLE llx_societe_perms(
fk_soc integer,
fk_user integer,
pread tinyint unsigned DEFAULT '0',
pwrite tinyint unsigned DEFAULT '0',
pperms tinyint unsigned DEFAULT '0',
UNIQUE INDEX ( fk_soc, fk_user )
) TYPE = innodb;

ALTER TABLE `llx_societe` ADD `code_fournisseur` VARCHAR( 15 ) ,
ADD `code_compta_fournisseur` VARCHAR( 15 ) ;

ALTER TABLE `llx_user` ADD `office_phone` VARCHAR( 20 ) ,
ADD `office_fax` VARCHAR( 20 ) ,
ADD `user_mobile` VARCHAR( 20 ) ,
ADD `datelastaccess` DATETIME NOT NULL ;
ALTER TABLE `llx_user` ADD `egroupware_id` INT NOT NULL ;

