-- SIMPLE consistency check:
-- grep -i varchar ./console/files/ocsbase.sql | \
-- 	sed -e 's/^[[:space:]]*//' -e 's,varchar,VARCHAR,' -e 's,default,DEFAULT,'|\
--	sort -u | less
--

CREATE TABLE hardware (
  ID INTEGER NOT NULL auto_increment,
  DEVICEID VARCHAR(127) not NULL,
  NAME VARCHAR(127) default NULL,
  WORKGROUP VARCHAR(63) default NULL,
  USERDOMAIN VARCHAR(127) default NULL,
  OSNAME VARCHAR(63) default NULL,
  OSVERSION VARCHAR(63) default NULL,
  OSCOMMENTS VARCHAR(255) default NULL,
  PROCESSORT VARCHAR(63) default NULL,
  PROCESSORS INTEGER default 0,
  PROCESSORN SMALLINT default NULL,
  MEMORY INTEGER default NULL,
  SWAP INTEGER default NULL,
  IPADDR VARCHAR(63) default NULL,
  DNS VARCHAR(255) default NULL,
  DEFAULTGATEWAY VARCHAR(63) default NULL,
  ETIME DATETIME default NULL,
  LASTDATE DATETIME default NULL,
  LASTCOME DATETIME default NULL,
  QUALITY DECIMAL(7,4) default 0,
  FIDELITY BIGINT default 1,
  USERID VARCHAR(63) default NULL,
  `TYPE` INTEGER default NULL,
  DESCRIPTION VARCHAR(255) default NULL,
  WINCOMPANY VARCHAR(127) default NULL,
  WINOWNER VARCHAR(127) default NULL,
  WINPRODID VARCHAR(127) default NULL,
  WINPRODKEY VARCHAR(127) default NULL,
  USERAGENT VARCHAR(50) default NULL,
  CHECKSUM BIGINT UNSIGNED default 0,
  SSTATE INTEGER default 0,
  IPSRC VARCHAR(63) default NULL,
  UUID VARCHAR(63) default NULL,
  PRIMARY KEY  (ID),
  INDEX NAME (NAME),
  INDEX CHECKSUM (CHECKSUM),
  INDEX USERID(USERID),
  INDEX WORKGROUP(WORKGROUP),
  INDEX OSNAME(OSNAME),
  INDEX MEMORY(MEMORY),
  INDEX DEVICEID (DEVICEID)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE accesslog (
  ID INTEGER NOT NULL auto_increment,
  HARDWARE_ID INTEGER NOT NULL,
  USERID VARCHAR(63) default NULL,
  LOGDATE DATETIME default NULL,
  PROCESSES TEXT,
  INDEX USERID(USERID),
  PRIMARY KEY  (ID, HARDWARE_ID)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE accountinfo (
  HARDWARE_ID INTEGER NOT NULL,
  TAG VARCHAR(127) default 'NA',
  primary key(HARDWARE_ID),
  INDEX TAG (TAG)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE deploy (
  NAME VARCHAR(127) NOT NULL,
  CONTENT LONGBLOB NOT NULL,
  PRIMARY KEY  (NAME)
) ENGINE=MYISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE netmap (
  IP VARCHAR(15) NOT NULL,
  MAC VARCHAR(17) NOT NULL,
  MASK VARCHAR(15) NOT NULL,
  NETID VARCHAR(15) NOT NULL,
  DATE TIMESTAMP default CURRENT_TIMESTAMP,
  NAME VARCHAR(127) default NULL,
  PRIMARY KEY  (MAC),
  INDEX IP (IP),
  INDEX NETID (NETID)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE bios (
  HARDWARE_ID INTEGER NOT NULL,
  SMANUFACTURER VARCHAR(127) default NULL,
  SMODEL VARCHAR(127) default NULL,
  SSN VARCHAR(127) default NULL,
  `TYPE` VARCHAR(63) default NULL,
  BMANUFACTURER VARCHAR(127) default NULL,
  BVERSION VARCHAR(63) default NULL,
  BDATE VARCHAR(63) default NULL,
  ASSETTAG VARCHAR(127) default NULL,
  PRIMARY KEY  (HARDWARE_ID),
  INDEX SSN (SSN),
  INDEX ASSETTAG (ASSETTAG)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE config (
  NAME VARCHAR(50) NOT NULL,
  IVALUE INTEGER default NULL,
  TVALUE VARCHAR(255) default NULL,
  COMMENTS TEXT,
  PRIMARY KEY (NAME)
) ENGINE=MYISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE controllers (
  ID INTEGER NOT NULL auto_increment,
  HARDWARE_ID INTEGER NOT NULL,
  MANUFACTURER VARCHAR(127) default NULL,
  NAME VARCHAR(127) default NULL,
  CAPTION VARCHAR(255) default NULL,
  DESCRIPTION VARCHAR(255) default NULL,
  VERSION VARCHAR(63) default NULL,
  `TYPE` VARCHAR(63) default NULL,
  PRIMARY KEY  (ID, HARDWARE_ID)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE devices (
  HARDWARE_ID INTEGER NOT NULL,
  NAME VARCHAR(50) NOT NULL,
  IVALUE INTEGER default NULL,
  TVALUE VARCHAR(127) default NULL,
  COMMENTS TEXT,
  INDEX HARDWARE_ID (HARDWARE_ID),
  INDEX TVALUE (TVALUE),
  INDEX IVALUE (IVALUE),
  INDEX NAME (NAME)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE drives (
  ID INTEGER NOT NULL auto_increment,
  HARDWARE_ID INTEGER NOT NULL,
  LETTER VARCHAR(63) default NULL,
  `TYPE` VARCHAR(63) default NULL,
  FILESYSTEM VARCHAR(63) default NULL,
  TOTAL INTEGER default NULL,
  FREE INTEGER default NULL,
  NUMFILES INTEGER default NULL,
  VOLUMN VARCHAR(63) default NULL,
  CREATEDATE DATE default NULL,
  PRIMARY KEY  (ID, HARDWARE_ID)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE files (
  NAME VARCHAR(120) NOT NULL,
  VERSION VARCHAR(63) NOT NULL,
  OS VARCHAR(63) NOT NULL,
  CONTENT LONGBLOB NOT NULL,
  PRIMARY KEY  (NAME, OS, VERSION)
) ENGINE=MYISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE inputs (
  ID INTEGER NOT NULL auto_increment,
  HARDWARE_ID INTEGER NOT NULL,
  `TYPE` VARCHAR(63) default NULL,
  MANUFACTURER VARCHAR(127) default NULL,
  CAPTION VARCHAR(255) default NULL,
  DESCRIPTION VARCHAR(255) default NULL,
  INTERFACE VARCHAR(127) default NULL,
  POINTTYPE VARCHAR(127) default NULL,
  PRIMARY KEY  (ID, HARDWARE_ID)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE memories (
  ID INTEGER NOT NULL auto_increment,
  HARDWARE_ID INTEGER NOT NULL,
  CAPTION VARCHAR(255) default NULL,
  DESCRIPTION VARCHAR(255) default NULL,
  CAPACITY VARCHAR(63) default NULL,
  PURPOSE VARCHAR(255) default NULL,
  `TYPE` VARCHAR(63) default NULL,
  SPEED VARCHAR(63) default NULL,
  NUMSLOTS SMALLINT default NULL,
  SERIALNUMBER VARCHAR(127) default NULL,
  PRIMARY KEY  (ID, HARDWARE_ID)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE modems (
  ID INTEGER NOT NULL auto_increment,
  HARDWARE_ID INTEGER NOT NULL,
  NAME VARCHAR(127) default NULL,
  MODEL VARCHAR(127) default NULL,
  DESCRIPTION VARCHAR(255) default NULL,
  `TYPE` VARCHAR(63) default NULL,
  PRIMARY KEY  (ID, HARDWARE_ID)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE monitors (
  ID INTEGER NOT NULL auto_increment,
  HARDWARE_ID INTEGER NOT NULL,
  MANUFACTURER VARCHAR(127) default NULL,
  CAPTION VARCHAR(255) default NULL,
  DESCRIPTION VARCHAR(255) default NULL,
  `TYPE` VARCHAR(63) default NULL,
  SERIAL VARCHAR(127) default NULL,
  PRIMARY KEY  (ID, HARDWARE_ID)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE networks (
  ID INTEGER NOT NULL auto_increment,
  HARDWARE_ID INTEGER NOT NULL,
  DESCRIPTION VARCHAR(255) default NULL,
  `TYPE` VARCHAR(63) default NULL,
  TYPEMIB VARCHAR(127) default NULL,
  SPEED VARCHAR(63) default NULL,
  MACADDR VARCHAR(127) default NULL,
  `STATUS` VARCHAR(127) default NULL,
  IPADDRESS VARCHAR(63) default NULL,
  IPMASK VARCHAR(63) default NULL,
  IPGATEWAY VARCHAR(63) default NULL,
  IPSUBNET VARCHAR(63) default NULL,
  IPDHCP VARCHAR(63) default NULL,
  VIRTUALDEV BOOL default FALSE,
  PRIMARY KEY  (ID, HARDWARE_ID),
  INDEX MACADDR (MACADDR),
  INDEX IPADDRESS(IPADDRESS),
  INDEX IPGATEWAY(IPGATEWAY),
  INDEX IPSUBNET (IPSUBNET)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE network_devices(
  ID INTEGER NOT NULL auto_increment,
  DESCRIPTION VARCHAR(255) default NULL,
  `TYPE` VARCHAR(63) default NULL,
  MACADDR VARCHAR(127) default NULL,
  `USER` VARCHAR(255) default NULL,
  PRIMARY KEY (ID),
  INDEX MACADDR (MACADDR)
) ENGINE=MYISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE operators (
  ID VARCHAR(127) NOT NULL default '',
  FIRSTNAME VARCHAR(127) default NULL,
  LASTNAME VARCHAR(127) default NULL,
  PASSWD VARCHAR(50) default NULL,
  ACCESSLVL INTEGER default NULL,
  COMMENTS text,
  PRIMARY KEY  (ID)
) ENGINE=MYISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE ports (
  ID INTEGER NOT NULL auto_increment,
  HARDWARE_ID INTEGER NOT NULL,
  `TYPE` VARCHAR(63) default NULL,
  NAME VARCHAR(127) default NULL,
  CAPTION VARCHAR(255) default NULL,
  DESCRIPTION VARCHAR(255) default NULL,
  PRIMARY KEY  (ID, HARDWARE_ID)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE printers (
  ID INTEGER NOT NULL auto_increment,
  HARDWARE_ID INTEGER NOT NULL,
  NAME VARCHAR(127) default NULL,
  DRIVER VARCHAR(127) default NULL,
  PORT VARCHAR(127) default NULL,
  PRIMARY KEY  (ID, HARDWARE_ID)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE regconfig (
  ID INTEGER NOT NULL auto_increment,
  NAME VARCHAR(127) default NULL,
  REGTREE INTEGER default NULL,
  REGKEY text,
  REGVALUE VARCHAR(127) default NULL,
  PRIMARY KEY  (ID),
  KEY NAME (NAME)
) ENGINE=MYISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE registry (
  ID INTEGER NOT NULL auto_increment,
  HARDWARE_ID INTEGER NOT NULL,
  NAME VARCHAR(127) default NULL,
  REGVALUE VARCHAR(127) default NULL,
  PRIMARY KEY  (ID, HARDWARE_ID),
  KEY NAME (NAME)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE slots (
  ID INTEGER NOT NULL auto_increment,
  HARDWARE_ID INTEGER NOT NULL,
  NAME VARCHAR(127) default NULL,
  DESCRIPTION VARCHAR(255) default NULL,
  DESIGNATION VARCHAR(255) default NULL,
  PURPOSE VARCHAR(255) default NULL,
  `STATUS` VARCHAR(127) default NULL,
  PSHARE tinyint(4) default NULL,
  PRIMARY KEY  (ID, HARDWARE_ID)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE softwares (
  ID INTEGER NOT NULL auto_increment,
  HARDWARE_ID INTEGER NOT NULL,
  PUBLISHER VARCHAR(127) default NULL,
  NAME VARCHAR(127) default NULL,
  VERSION VARCHAR(63) default NULL,
  FOLDER text,
  COMMENTS text,
  FILENAME VARCHAR(127) default NULL,
  FILESIZE INTEGER default '0',
  SOURCE INTEGER default NULL,
  PRIMARY KEY  (ID, HARDWARE_ID),
  INDEX NAME (NAME),
  INDEX `VERSION`(`VERSION`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `sounds` (
  ID INTEGER NOT NULL auto_increment,
  HARDWARE_ID INTEGER NOT NULL,
  MANUFACTURER VARCHAR(127) default NULL,
  NAME VARCHAR(127) default NULL,
  DESCRIPTION VARCHAR(255) default NULL,
  PRIMARY KEY  (ID, HARDWARE_ID)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE storages (
  ID INTEGER NOT NULL auto_increment,
  HARDWARE_ID INTEGER NOT NULL,
  MANUFACTURER VARCHAR(127) default NULL,
  NAME VARCHAR(127) default NULL,
  MODEL VARCHAR(127) default NULL,
  DESCRIPTION VARCHAR(255) default NULL,
  `TYPE` VARCHAR(63) default NULL,
  DISKSIZE INTEGER default NULL,
  SERIALNUMBER VARCHAR(127) default NULL,
  FIRMWARE VARCHAR(127) default NULL,
  PRIMARY KEY  (ID, HARDWARE_ID)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE videos (
  ID INTEGER NOT NULL auto_increment,
  HARDWARE_ID INTEGER NOT NULL,
  NAME VARCHAR(127) default NULL,
  CHIPSET VARCHAR(127) default NULL,
  MEMORY VARCHAR(127) default NULL,
  RESOLUTION VARCHAR(255) default NULL,
  PRIMARY KEY  (ID, HARDWARE_ID)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE devicetype (
  ID INTEGER NOT NULL auto_increment,
  NAME VARCHAR(127) default NULL,
  PRIMARY KEY  (ID)
) ENGINE=MYISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE subnet (
  NETID VARCHAR(15) NOT NULL,
  NAME VARCHAR(127),
  ID INTEGER,
  MASK VARCHAR(63),
  PRIMARY KEY (NETID),
  INDEX ID(ID)
) ENGINE=MYISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE virtualmachines (
  ID INTEGER NOT NULL auto_increment,
  HARDWARE_ID INTEGER NOT NULL,
  NAME VARCHAR(127) default NULL,
  STATUS VARCHAR(127) default NULL,
  SUBSYSTEM VARCHAR(127) default NULL,
  VMTYPE VARCHAR(63) default NULL,
  UUID VARCHAR(63) default NULL,
  VCPU INTEGER default NULL,
  MEMORY INTEGER default NULL,
  PRIMARY KEY  (ID, HARDWARE_ID)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE locks(
  HARDWARE_ID INTEGER NOT NULL PRIMARY KEY,
  ID INTEGER DEFAULT NULL,
  SINCE TIMESTAMP,
  INDEX SINCE (SINCE)
) ENGINE=HEAP DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE dico_ignored(
  EXTRACTED VARCHAR(127) NOT NULL,
  PRIMARY KEY(EXTRACTED)
) ENGINE=MYISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE dico_soft( 
  EXTRACTED VARCHAR(127) NOT NULL,
  FORMATTED VARCHAR(255) NOT NULL,
  PRIMARY KEY(EXTRACTED)
) ENGINE=MYISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE deleted_equiv(
  DATE TIMESTAMP, 
  DELETED VARCHAR(255) NOT NULL,
  EQUIVALENT VARCHAR(255) default NULL
) ENGINE=MYISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE download_available(
	FILEID VARCHAR(127) NOT NULL PRIMARY KEY,
	NAME VARCHAR(127) NOT NULL,
	PRIORITY INTEGER NOT NULL,
	FRAGMENTS INTEGER NOT NULL,
	SIZE INTEGER NOT NULL,
	OSNAME VARCHAR(63) NOT NULL,
	COMMENT TEXT
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE download_enable(
	ID INTEGER NOT NULL auto_increment PRIMARY KEY,
	FILEID VARCHAR(127) NOT NULL,
	INFO_LOC VARCHAR(255) NOT NULL,
	PACK_LOC VARCHAR(255) NOT NULL,
	CERT_PATH VARCHAR(255),
	CERT_FILE VARCHAR(255),
	INDEX FILEID(FILEID)
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE download_history(
	HARDWARE_ID INTEGER NOT NULL,
	PKG_ID INTEGER default NULL,
	PKG_NAME VARCHAR(127),
	PRIMARY KEY(HARDWARE_ID, PKG_ID)
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE conntrack(
	IP VARCHAR(15),
	`TIMESTAMP` TIMESTAMP,
	PRIMARY KEY(IP)
) ENGINE = HEAP DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE groups(
	HARDWARE_ID integer default NULL,
	REQUEST longtext,
	CREATE_TIME INT,
	PRIMARY KEY(HARDWARE_ID)
) ENGINE=MYISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE groups_cache(
	HARDWARE_ID integer NOT NULL default 0,
	GROUP_ID integer NOT NULL default 0,
	STATIC integer default 0,
	PRIMARY KEY(HARDWARE_ID,GROUP_ID)
) ENGINE=MYISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE blacklist_macaddresses(
	ID INTEGER auto_increment,
	MACADDRESS VARCHAR(127),
	PRIMARY KEY(MACADDRESS),
	INDEX ID(ID)
) ENGINE = MYISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE blacklist_serials(
	ID INTEGER auto_increment,
	SERIAL VARCHAR(127),
	PRIMARY KEY(SERIAL),
	INDEX ID(ID)
) ENGINE = MYISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE registry_name_cache(
        ID INTEGER auto_increment,
        NAME VARCHAR(127) UNIQUE,
        PRIMARY KEY(ID)
) ENGINE = MYISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
TRUNCATE TABLE registry_name_cache;
INSERT INTO registry_name_cache(name) SELECT DISTINCT name FROM registry;

CREATE TABLE registry_regvalue_cache(
	ID INTEGER auto_increment,
	REGVALUE VARCHAR(127) UNIQUE,
	PRIMARY KEY(ID)
) ENGINE = MYISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
TRUNCATE TABLE registry_regvalue_cache;
INSERT INTO registry_regvalue_cache(regvalue) SELECT DISTINCT regvalue FROM registry;

CREATE TABLE hardware_osname_cache(
        ID INTEGER auto_increment,
        OSNAME VARCHAR(63) UNIQUE,
        PRIMARY KEY(ID)
) ENGINE = MYISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
TRUNCATE TABLE hardware_osname_cache;
INSERT INTO hardware_osname_cache(osname) SELECT DISTINCT osname FROM hardware;

CREATE TABLE softwares_name_cache(
        ID INTEGER auto_increment,
        NAME VARCHAR(127) UNIQUE,
        PRIMARY KEY(ID)
) ENGINE = MYISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
TRUNCATE TABLE softwares_name_cache;
INSERT INTO softwares_name_cache(name) SELECT DISTINCT name FROM softwares;

CREATE TABLE tags (
  Tag VARCHAR(127) NOT NULL default '',
  Login VARCHAR(63) NOT NULL default '',
  PRIMARY KEY  (Tag,Login),
  KEY Tag (Tag),
  KEY Login (Login)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE engine_mutex (
  NAME VARCHAR(127) NOT NULL default '',
  PID int(11) default NULL,
  TAG varchar(127) NOT NULL default '',
  PRIMARY KEY  (NAME,TAG)
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE engine_persistent (
 ID int(11) NOT NULL auto_increment,
 NAME varchar(127) NOT NULL default '',
 IVALUE int(11) default NULL,
 TVALUE varchar(127) default NULL,
  UNIQUE KEY NAME (NAME),
  KEY ID (ID)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE journallog (
  HARDWARE_ID INT(11) not null,
  ID integer auto_increment not null,
  JOURNALLOG LONGTEXT,
  LISTENERNAME VARCHAR(255) default 'NONAME',
  `DATE` VARCHAR(63) default null,
  STATUS INTEGER default 0,
  ERRORCODE INTEGER default 0,
  PRIMARY KEY(ID,HARDWARE_ID)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE javainfo (
  HARDWARE_ID INT(11) not null,
  JAVANAME VARCHAR(127) default 'NONAME',
  JAVAPATHLEVEL INTEGER default 0,
  JAVACOUNTRY VARCHAR(63),
  JAVACLASSPATH VARCHAR(255) default null,
  JAVAHOME VARCHAR(255) default null,
  PRIMARY KEY (HARDWARE_ID)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE devices ADD INDEX IVALUE (IVALUE);
ALTER TABLE devices ADD INDEX NAME (NAME);
ALTER TABLE monitors ADD COLUMN SERIAL VARCHAR(127);
ALTER TABLE netmap ADD COLUMN MASK VARCHAR(15);
ALTER TABLE netmap ADD COLUMN NETID VARCHAR(15);
ALTER TABLE netmap ADD INDEX NETID (NETID);
ALTER TABLE netmap ADD COLUMN DATE TIMESTAMP;
ALTER TABLE netmap ADD COLUMN NAME VARCHAR(127) default NULL;
ALTER TABLE networks ADD COLUMN IPSUBNET VARCHAR(15);
ALTER TABLE networks ADD INDEX IPSUBNET (IPSUBNET);
ALTER TABLE networks ADD INDEX MACADDR (MACADDR);
ALTER TABLE hardware ADD COLUMN CHECKSUM INTEGER default NULL;
ALTER TABLE hardware CHANGE COLUMN CHECKSUM CHECKSUM INTEGER default 131071;
ALTER TABLE hardware CHANGE COLUMN CHECKSUM CHECKSUM BIGINT UNSIGNED default 262143;
ALTER TABLE hardware add column WINPRODKEY VARCHAR(127) default NULL;
ALTER TABLE hardware add column USERDOMAIN VARCHAR(127) default NULL;
ALTER TABLE hardware ADD COLUMN SSTATE INTEGER default 0;
ALTER TABLE hardware ADD COLUMN DEFAULTGATEWAY VARCHAR(63) default NULL;
ALTER TABLE hardware ADD COLUMN DNS VARCHAR(255) default NULL;
ALTER TABLE drives ADD COLUMN CREATEDATE DATE default NULL;
ALTER TABLE hardware ADD COLUMN IPSRC VARCHAR(63) default NULL;

ALTER TABLE hardware CHANGE ID ID INTEGER;
ALTER TABLE hardware DROP PRIMARY KEY;
ALTER TABLE hardware ADD COLUMN ID integer not NULL FIRST;
ALTER TABLE hardware ADD INDEX ID (ID);
ALTER TABLE hardware CHANGE ID ID INTEGER auto_increment;
ALTER TABLE hardware add PRIMARY KEY(DEVICEID, ID);

ALTER TABLE bios DROP PRIMARY KEY;
ALTER TABLE bios ADD COLUMN HARDWARE_ID integer not NULL FIRST;
UPDATE bios SET bios.HARDWARE_ID= (SELECT ID FROM hardware WHERE bios.DEVICEID = hardware.DEVICEID);
ALTER TABLE bios DROP DEVICEID;
ALTER TABLE bios ADD PRIMARY KEY(HARDWARE_ID);
ALTER TABLE bios ADD COLUMN ASSETTAG VARCHAR(127) default NULL;
ALTER TABLE bios ADD INDEX ASSETTAG (ASSETTAG);

ALTER TABLE accountinfo DROP PRIMARY KEY;
ALTER TABLE accountinfo ADD COLUMN HARDWARE_ID integer not NULL FIRST;
UPDATE accountinfo SET accountinfo.HARDWARE_ID= (SELECT ID FROM hardware WHERE accountinfo.DEVICEID = hardware.DEVICEID);
ALTER TABLE accountinfo DROP DEVICEID;
ALTER TABLE accountinfo ADD PRIMARY KEY(HARDWARE_ID);

ALTER TABLE devices DROP PRIMARY KEY;
ALTER TABLE devices ADD COLUMN HARDWARE_ID integer not NULL FIRST;
UPDATE devices SET devices.HARDWARE_ID= (SELECT ID FROM hardware WHERE devices.DEVICEID = hardware.DEVICEID);
ALTER TABLE devices DROP DEVICEID;
ALTER TABLE devices ADD INDEX HARDWARE_ID (HARDWARE_ID);

ALTER TABLE controllers change ID ID INTEGER;
ALTER TABLE controllers DROP PRIMARY KEY;
ALTER TABLE controllers ADD COLUMN HARDWARE_ID integer not NULL FIRST;
UPDATE controllers SET controllers.HARDWARE_ID= (SELECT ID FROM hardware WHERE controllers.DEVICEID = hardware.DEVICEID);
ALTER TABLE controllers ADD INDEX ID (ID);
ALTER TABLE controllers change ID ID INTEGER auto_increment;
ALTER TABLE controllers ADD PRIMARY KEY(HARDWARE_ID,ID);
ALTER TABLE controllers DROP DEVICEID;

ALTER TABLE slots change ID ID INTEGER;
ALTER TABLE slots DROP PRIMARY KEY;
ALTER TABLE slots ADD COLUMN HARDWARE_ID integer not NULL FIRST;
UPDATE slots SET slots.HARDWARE_ID= (SELECT ID FROM hardware WHERE slots.DEVICEID = hardware.DEVICEID);
ALTER TABLE slots ADD INDEX ID (ID);
ALTER TABLE slots change ID ID INTEGER auto_increment;
ALTER TABLE slots ADD PRIMARY KEY(HARDWARE_ID,ID);
ALTER TABLE slots DROP DEVICEID;

ALTER TABLE registry change ID ID INTEGER;
ALTER TABLE registry DROP PRIMARY KEY;
ALTER TABLE registry ADD COLUMN HARDWARE_ID integer not NULL FIRST;
UPDATE registry SET registry.HARDWARE_ID= (SELECT ID FROM hardware WHERE registry.DEVICEID = hardware.DEVICEID);
ALTER TABLE registry ADD INDEX ID (ID);
ALTER TABLE registry change ID ID INTEGER auto_increment;
ALTER TABLE registry ADD PRIMARY KEY(HARDWARE_ID,ID);
ALTER TABLE registry DROP DEVICEID;

INSERT INTO network_devices(DESCRIPTION,TYPE,MACADDR,`USER`) SELECT DESCRIPTION,TYPE,MACADDR,TYPEMIB FROM networks WHERE DEVICEID LIKE "NETWORK_DEVICE-%";
DELETE FROM network_devices WHERE DEVICEID LIKE "NETWORK_DEVICE-%";

ALTER TABLE networks change ID ID INTEGER;
ALTER TABLE networks DROP PRIMARY KEY;
ALTER TABLE networks ADD COLUMN HARDWARE_ID integer not NULL FIRST;
UPDATE networks SET networks.HARDWARE_ID= (SELECT ID FROM hardware WHERE networks.DEVICEID = hardware.DEVICEID);
ALTER TABLE networks ADD INDEX ID (ID);
ALTER TABLE networks change ID ID INTEGER auto_increment;
ALTER TABLE networks ADD PRIMARY KEY(HARDWARE_ID,ID);
ALTER TABLE networks DROP DEVICEID;

ALTER TABLE memories change ID ID INTEGER;
ALTER TABLE memories DROP PRIMARY KEY;
ALTER TABLE memories ADD COLUMN HARDWARE_ID integer not NULL FIRST;
UPDATE memories SET memories.HARDWARE_ID= (SELECT ID FROM hardware WHERE memories.DEVICEID = hardware.DEVICEID);
ALTER TABLE memories ADD INDEX ID (ID);
ALTER TABLE memories change ID ID INTEGER auto_increment;
ALTER TABLE memories ADD PRIMARY KEY(HARDWARE_ID,ID);
ALTER TABLE memories DROP DEVICEID;
ALTER TABLE memories ADD COLUMN SERIALNUMBER VARCHAR(127) default NULL AFTER NUMSLOTS;

ALTER TABLE drives change ID ID INTEGER;
ALTER TABLE drives DROP PRIMARY KEY;
ALTER TABLE drives ADD COLUMN HARDWARE_ID integer not NULL FIRST;
UPDATE drives SET drives.HARDWARE_ID= (SELECT ID FROM hardware WHERE drives.DEVICEID = hardware.DEVICEID);
ALTER TABLE drives ADD INDEX ID (ID);
ALTER TABLE drives change ID ID INTEGER auto_increment;
ALTER TABLE drives ADD PRIMARY KEY(HARDWARE_ID,ID);
ALTER TABLE drives DROP DEVICEID;

ALTER TABLE storages change ID ID INTEGER;
ALTER TABLE storages DROP PRIMARY KEY;
ALTER TABLE storages ADD COLUMN HARDWARE_ID integer not NULL FIRST;
UPDATE storages SET storages.HARDWARE_ID= (SELECT ID FROM hardware WHERE storages.DEVICEID = hardware.DEVICEID);
ALTER TABLE storages ADD INDEX ID (ID);
ALTER TABLE storages change ID ID INTEGER auto_increment;
ALTER TABLE storages ADD PRIMARY KEY(HARDWARE_ID,ID);
ALTER TABLE storages DROP DEVICEID;
ALTER TABLE storages ADD COLUMN SERIALNUMBER VARCHAR(127) default NULL AFTER DISKSIZE;
ALTER TABLE storages ADD COLUMN FIRMWARE VARCHAR(127) default NULL AFTER SERIALNUMBER;

ALTER TABLE ports change ID ID INTEGER;
ALTER TABLE ports DROP PRIMARY KEY;
ALTER TABLE ports ADD COLUMN HARDWARE_ID integer not NULL FIRST;
UPDATE ports SET ports.HARDWARE_ID= (SELECT ID FROM hardware WHERE ports.DEVICEID = hardware.DEVICEID);
ALTER TABLE ports ADD INDEX ID (ID);
ALTER TABLE ports change ID ID INTEGER auto_increment;
ALTER TABLE ports ADD PRIMARY KEY(HARDWARE_ID,ID);
ALTER TABLE ports DROP DEVICEID;

ALTER TABLE accesslog change ID ID INTEGER;
ALTER TABLE accesslog DROP PRIMARY KEY;
ALTER TABLE accesslog ADD COLUMN HARDWARE_ID integer not NULL FIRST;
UPDATE accesslog SET accesslog.HARDWARE_ID= (SELECT ID FROM hardware WHERE accesslog.DEVICEID = hardware.DEVICEID);
ALTER TABLE accesslog ADD INDEX ID (ID);
ALTER TABLE accesslog change ID ID INTEGER auto_increment;
ALTER TABLE accesslog ADD PRIMARY KEY(HARDWARE_ID,ID);
ALTER TABLE accesslog DROP DEVICEID;

ALTER TABLE softwares change ID ID INTEGER;
ALTER TABLE softwares DROP PRIMARY KEY;
ALTER TABLE softwares ADD COLUMN HARDWARE_ID integer not NULL FIRST;
UPDATE softwares SET softwares.HARDWARE_ID= (SELECT ID FROM hardware WHERE softwares.DEVICEID = hardware.DEVICEID);
ALTER TABLE softwares ADD INDEX ID (ID);
ALTER TABLE softwares change ID ID INTEGER auto_increment;
ALTER TABLE softwares ADD PRIMARY KEY(HARDWARE_ID,ID);
ALTER TABLE softwares DROP DEVICEID;

ALTER TABLE monitors change ID ID INTEGER;
ALTER TABLE monitors DROP PRIMARY KEY;
ALTER TABLE monitors ADD COLUMN HARDWARE_ID integer not NULL FIRST;
UPDATE monitors SET monitors.HARDWARE_ID= (SELECT ID FROM hardware WHERE monitors.DEVICEID = hardware.DEVICEID);
ALTER TABLE monitors ADD INDEX ID (ID);
ALTER TABLE monitors change ID ID INTEGER auto_increment;
ALTER TABLE monitors ADD PRIMARY KEY(HARDWARE_ID,ID);
ALTER TABLE monitors DROP DEVICEID;

ALTER TABLE modems change ID ID INTEGER;
ALTER TABLE modems DROP PRIMARY KEY;
ALTER TABLE modems ADD COLUMN HARDWARE_ID integer not NULL FIRST;
UPDATE modems SET modems.HARDWARE_ID= (SELECT ID FROM hardware WHERE modems.DEVICEID = hardware.DEVICEID);
ALTER TABLE modems ADD INDEX ID (ID);
ALTER TABLE modems change ID ID INTEGER auto_increment;
ALTER TABLE modems ADD PRIMARY KEY(HARDWARE_ID,ID);
ALTER TABLE modems DROP DEVICEID;

ALTER TABLE inputs change ID ID INTEGER;
ALTER TABLE inputs DROP PRIMARY KEY;
ALTER TABLE inputs ADD COLUMN HARDWARE_ID integer not NULL FIRST;
UPDATE inputs SET inputs.HARDWARE_ID= (SELECT ID FROM hardware WHERE inputs.DEVICEID = hardware.DEVICEID);
ALTER TABLE inputs ADD INDEX ID (ID);
ALTER TABLE inputs change ID ID INTEGER auto_increment;
ALTER TABLE inputs ADD PRIMARY KEY(HARDWARE_ID,ID);
ALTER TABLE inputs DROP DEVICEID;

ALTER TABLE printers change ID ID INTEGER;
ALTER TABLE printers DROP PRIMARY KEY;
ALTER TABLE printers ADD COLUMN HARDWARE_ID integer not NULL FIRST;
UPDATE printers SET printers.HARDWARE_ID= (SELECT ID FROM hardware WHERE printers.DEVICEID = hardware.DEVICEID);
ALTER TABLE printers ADD INDEX ID (ID);
ALTER TABLE printers change ID ID INTEGER auto_increment;
ALTER TABLE printers ADD PRIMARY KEY(HARDWARE_ID,ID);
ALTER TABLE printers DROP DEVICEID;
ALTER TABLE printers ADD COLUMN DESCRIPTION VARCHAR(255);


ALTER TABLE videos change ID ID INTEGER;
ALTER TABLE videos DROP PRIMARY KEY;
ALTER TABLE videos ADD COLUMN HARDWARE_ID integer not NULL FIRST;
UPDATE videos SET videos.HARDWARE_ID= (SELECT ID FROM hardware WHERE videos.DEVICEID = hardware.DEVICEID);
ALTER TABLE videos ADD INDEX ID (ID);
ALTER TABLE videos change ID ID INTEGER auto_increment;
ALTER TABLE videos ADD PRIMARY KEY(HARDWARE_ID,ID);
ALTER TABLE videos DROP DEVICEID;

ALTER TABLE sounds change ID ID INTEGER;
ALTER TABLE sounds DROP PRIMARY KEY;
ALTER TABLE sounds ADD COLUMN HARDWARE_ID integer not NULL FIRST;
UPDATE sounds SET sounds.HARDWARE_ID= (SELECT ID FROM hardware WHERE sounds.DEVICEID = hardware.DEVICEID);
ALTER TABLE sounds ADD INDEX ID (ID);
ALTER TABLE sounds change ID ID INTEGER auto_increment;
ALTER TABLE sounds ADD PRIMARY KEY(HARDWARE_ID,ID);
ALTER TABLE sounds DROP DEVICEID;

TRUNCATE TABLE locks;

ALTER TABLE softwares CHANGE NAME NAME VARCHAR(127) default NULL;
ALTER TABLE locks DROP DEVICEID;
ALTER TABLE locks ADD HARDWARE_ID INTEGER NOT NULL PRIMARY KEY FIRST;
ALTER TABLE locks ADD INDEX SINCE (SINCE);

ALTER TABLE accesslog ADD INDEX USERID(USERID);
ALTER TABLE download_enable ADD INDEX FILEID(FILEID);
ALTER TABLE hardware ADD INDEX USERID(USERID);
ALTER TABLE hardware ADD INDEX WORKGROUP(WORKGROUP);
ALTER TABLE hardware ADD INDEX OSNAME(OSNAME);
ALTER TABLE hardware ADD INDEX MEMORY(MEMORY);
ALTER TABLE networks ADD INDEX IPADDRESS(IPADDRESS);
ALTER TABLE networks ADD INDEX IPGATEWAY(IPGATEWAY);
ALTER TABLE softwares ADD INDEX `VERSION`(`VERSION`);
ALTER TABLE subnet ADD INDEX ID(ID);
ALTER TABLE hardware CHANGE QUALITY QUALITY DECIMAL(7,4) default NULL;

ALTER TABLE groups change REVALIDATE_FROM REVALIDATE_FROM INT default 0; 	 
ALTER TABLE groups change CREATE_TIME CREATE_TIME INT default 0;
UPDATE groups SET CREATE_TIME=0 WHERE CREATE_TIME IS NULL;
UPDATE groups SET REVALIDATE_FROM=0 WHERE REVALIDATE_FROM IS NULL;

DELETE FROM config WHERE name='GUI_VERSION';
DELETE FROM config WHERE name='IP_MIN_QUALITY';

INSERT INTO config VALUES ('FREQUENCY', 0, '', 'Specify the frequency (days) of inventories. (0: inventory at each login. -1: no inventory)');
INSERT INTO config VALUES ('PROLOG_FREQ', 24, '', 'Specify the frequency (hours) of prolog, on agents');
INSERT INTO config VALUES ('IPDISCOVER', 2, '', 'Max number of computers per gateway retrieving IP on the network');
INSERT INTO config VALUES ('INVENTORY_DIFF', 1, '', 'Activate/Deactivate inventory incremental writing');
INSERT INTO config VALUES ('IPDISCOVER_LATENCY', 100, '', 'Default latency between two arp requests');
INSERT INTO config VALUES ('INVENTORY_TRANSACTION', 1, '', 'Enable/disable db commit at each inventory section');
INSERT INTO config VALUES ('REGISTRY', 0, '', 'Activates or not the registry query function');
INSERT INTO config VALUES ('IPDISCOVER_MAX_ALIVE', 7, '','Max number of days before an Ip Discover computer is replaced');
INSERT INTO config VALUES ('DEPLOY', 1, '', 'Activates or not the automatic deployment option');
INSERT INTO config VALUES ('UPDATE', 0, '', 'Activates or not the update feature');
INSERT INTO config VALUES ('TRACE_DELETED', 0, '', 'Trace deleted/duplicated computers (Activated by GLPI)');
INSERT INTO config VALUES ('LOGLEVEL', 0, '', 'ocs engine loglevel');
INSERT INTO config VALUES ('AUTO_DUPLICATE_LVL', 7, '', 'Duplicates bitmap');
INSERT INTO config VALUES ('DOWNLOAD', 0, '', 'Activate softwares auto deployment feature');
INSERT INTO config VALUES ('DOWNLOAD_CYCLE_LATENCY', 60, '', 'Time between two cycles (seconds)');
INSERT INTO config VALUES ('DOWNLOAD_PERIOD_LENGTH', 10, '', 'Number of cycles in a period');
INSERT INTO config VALUES ('DOWNLOAD_FRAG_LATENCY', 10, '', 'Time between two downloads (seconds)');
INSERT INTO config VALUES ('DOWNLOAD_PERIOD_LATENCY', 1, '', 'Time between two periods (seconds)');
INSERT INTO config VALUES ('DOWNLOAD_TIMEOUT', 30, '', 'Validity of a package (in days)');
INSERT INTO config VALUES ('LOCAL_URI_SERVER',0,'@LOCAL_URI_SERVER@','The submission URI for clients');
INSERT INTO config VALUES ('DOWNLOAD_PACK_DIR','','@DOWNLOAD_PACK_DIR@','Directory for download files');
INSERT INTO config VALUES ('IPDISCOVER_IPD_DIR','','@IPDISCOVER_IPD_DIR@','Directory for Ipdiscover files');
INSERT INTO config VALUES ('LOG_DIR',0,'@LOG_DIR@','Directory for logs files');
INSERT INTO config VALUES ('LOG_SCRIPT',0,'@LOG_SCRIPT@','Directory for logs scripts files');

INSERT INTO blacklist_serials(SERIAL) VALUES ('N/A'),('(null string)'),('INVALID'),('SYS-1234567890'),('SYS-9876543210'),('SN-12345'),('SN-1234567890'),('1111111111'),('1111111'),('1'),('0123456789'),('12345'),('123456'),('1234567'),('12345678'),('123456789'),('1234567890'),('123456789000'),('12345678901234567'),('0000000000'),('000000000'),('00000000'),('0000000'),('000000'),('NNNNNNN'),('xxxxxxxxxxx'),('EVAL'),('IATPASS'),('none'),('To Be Filled By O.E.M.'),('Tulip Computers'),('Serial Number xxxxxx'),('SN-123456fvgv3i0b8o5n6n7k'),('');
INSERT INTO blacklist_macaddresses(MACADDRESS) VALUES ('00:00:00:00:00:00'),('FF:FF:FF:FF:FF:FF'),('44:45:53:54:00:00'),('44:45:53:54:00:01'),('00:01:02:7D:9B:1C'),('00:08:A1:46:06:35'),('00:08:A1:66:E2:1A'),('00:09:DD:10:37:68'),('00:0F:EA:9A:E2:F0'),('00:10:5A:72:71:F3'),('00:11:11:85:08:8B'),('10:11:11:11:11:11'),('44:45:53:54:61:6F'),('');


INSERT INTO operators(ID,FIRSTNAME,LASTNAME,PASSWD,ACCESSLVL,COMMENTS) VALUES ('admin','Herbert','Root','admin',1, 'Default administrator account');

INSERT INTO config VALUES ('GUI_VERSION', 0, '7006', 'Version of the installed GUI and database');

CREATE TABLE download_servers (
  HARDWARE_ID int(11) NOT NULL,
  URL varchar(250) collate latin1_general_ci NOT NULL,
  ADD_PORT int(11) NOT NULL,
  ADD_REP varchar(250) collate latin1_general_ci NOT NULL,
  GROUP_ID int(11) NOT NULL,
  PRIMARY KEY  (HARDWARE_ID)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE download_affect_rules (
  ID int(11) NOT NULL auto_increment,
  RULE int(11) NOT NULL,
  PRIORITY int(11) NOT NULL,
  CFIELD varchar(20) NOT NULL,
  OP varchar(20) NOT NULL,
  COMPTO varchar(20) NOT NULL,
  SERV_VALUE varchar(20) default NULL,
  RULE_NAME varchar(200) NOT NULL,
  PRIMARY KEY  (ID)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


INSERT INTO config VALUES ('DOWNLOAD_REDISTRIB',1,'','Use redistribution servers');
insert into config (NAME,IVALUE,TVALUE,COMMENTS) values ('DOWNLOAD_SERVER_URI','','@DOWNLOAD_SERVER_URI@','Server url used for group of server');
insert into config (NAME,IVALUE,TVALUE,COMMENTS) values ('DOWNLOAD_SERVER_DOCROOT','','@DOWNLOAD_SERVER_DOCROOT@','Server directory used for group of server');
INSERT INTO config VALUES ('DOWNLOAD_REP_CREAT',0,'@DOWNLOAD_REP_CREAT@','Download directory for replicated packages');

insert into config (NAME,IVALUE,TVALUE,COMMENTS) values ('LOCK_REUSE_TIME',600,'','Validity of a computer\'s lock');
insert into config (NAME,IVALUE,TVALUE,COMMENTS) values ('INVENTORY_DIFF',1,'','Configure engine to update inventory regarding to CHECKSUM agent value (lower DB backend load)');
insert into config (NAME,IVALUE,TVALUE,COMMENTS) values ('INVENTORY_TRANSACTION',1,'','Make engine consider an inventory as a transaction (lower concurency, better disk usage)');
insert into config (NAME,IVALUE,TVALUE,COMMENTS) values ('INVENTORY_WRITE_DIFF',0,'','Configure engine to make a differential update of inventory sections (row level). Lower DB backend load, higher frontend load');
insert into config (NAME,IVALUE,TVALUE,COMMENTS) values ('INVENTORY_CACHE_ENABLED',1,'','Enable some stuff to improve DB queries, especially for GUI multicriteria searching system');
insert into config (NAME,IVALUE,TVALUE,COMMENTS) values ('DOWNLOAD_GROUPS_TRACE_EVENTS',1,'','Specify if you want to track packages affected to a group on computer\'s level');
insert into config (NAME,IVALUE,TVALUE,COMMENTS) values ('ENABLE_GROUPS',1,'','Enable the computer\'s groups feature');
insert into config (NAME,IVALUE,TVALUE,COMMENTS) values ('GROUPS_CACHE_OFFSET',43200,'','Random number computed in the defined range. Designed to avoid computing many groups in the same process');
insert into config (NAME,IVALUE,TVALUE,COMMENTS) values ('GROUPS_CACHE_REVALIDATE',43200,'','Specify the validity of computer\'s groups (default: compute it once a day - see offset)');
insert into config (NAME,IVALUE,TVALUE,COMMENTS) values ('IPDISCOVER_BETTER_THRESHOLD',1,'','Specify the minimal difference to replace an ipdiscover agent');
insert into config (NAME,IVALUE,TVALUE,COMMENTS) values ('IPDISCOVER_NO_POSTPONE',0,'','Disable the time before a first election (not recommended)');
insert into config (NAME,IVALUE,TVALUE,COMMENTS) values ('IPDISCOVER_USE_GROUPS',1,'','Enable groups for ipdiscover (for example, you might want to prevent some groups');
insert into config (NAME,IVALUE,TVALUE,COMMENTS) values ('GENERATE_OCS_FILES',0,'','Use with ocsinventory-injector, enable the multi entities feature');
insert into config (NAME,IVALUE,TVALUE,COMMENTS) values ('OCS_FILES_FORMAT','','OCS','Generate either compressed file or clear XML text');
insert into config (NAME,IVALUE,TVALUE,COMMENTS) values ('OCS_FILES_OVERWRITE',0,'','Specify if you want to keep trace of all inventory between to synchronisation with the higher level server');
insert into config (NAME,IVALUE,TVALUE,COMMENTS) values ('OCS_FILES_PATH','','@OCS_FILES_PATH@','Path to ocs files directory (must be writeable)');
insert into config (NAME,IVALUE,TVALUE,COMMENTS) values ('PROLOG_FILTER_ON',0,'','Enable prolog filter stack');
insert into config (NAME,IVALUE,TVALUE,COMMENTS) values ('INVENTORY_FILTER_ENABLED',0,'','Enable core filter system to modify some things "on the fly"');
insert into config (NAME,IVALUE,TVALUE,COMMENTS) values ('INVENTORY_FILTER_FLOOD_IP',0,'','Enable inventory flooding filter. A dedicated ipaddress ia allowed to send a new computer only once in this period');
insert into config (NAME,IVALUE,TVALUE,COMMENTS) values ('INVENTORY_FILTER_FLOOD_IP_CACHE_TIME',300,'','Period definition for INVENTORY_FILTER_FLOOD_IP');
insert into config (NAME,IVALUE,TVALUE,COMMENTS) values ('INVENTORY_FILTER_ON',0,'','Enable inventory filter stack');
insert into config (NAME,IVALUE,TVALUE,COMMENTS) values ('GUI_REPORT_RAM_MAX',512,'','Filter on RAM for console page');
insert into config (NAME,IVALUE,TVALUE,COMMENTS) values ('GUI_REPORT_RAM_MINI',128,'','Filter on RAM for console page');
insert into config (NAME,IVALUE,TVALUE,COMMENTS) values ('GUI_REPORT_NOT_VIEW',3,'','Filter on DAY for console page');
insert into config (NAME,IVALUE,TVALUE,COMMENTS) values ('GUI_REPORT_PROC_MINI',1000,'','Filter on Hard Drive for console page');
insert into config (NAME,IVALUE,TVALUE,COMMENTS) values ('GUI_REPORT_DD_MAX',4000,'','Filter on Hard Drive for console page');
insert into config (NAME,IVALUE,TVALUE,COMMENTS) values ('GUI_REPORT_PROC_MAX',3000,'','Filter on PROCESSOR for console page');
insert into config (NAME,IVALUE,TVALUE,COMMENTS) values ('GUI_REPORT_DD_MINI',500,'','Filter on PROCESSOR for console page');
insert into config (NAME,IVALUE,TVALUE,COMMENTS) values ('GUI_REPORT_AGIN_MACH',30,'','Filter on lastdate for console page');


ALTER TABLE download_enable ADD SERVER_ID INT(11);
ALTER TABLE download_enable ADD GROUP_ID INT(11);
ALTER TABLE groups ADD REVALIDATE_FROM INT(11);
ALTER TABLE groups ADD XMLDEF longtext;

CREATE TABLE prolog_conntrack (
  ID int(11) NOT NULL auto_increment,
  DEVICEID varchar(127) default NULL,
  TIMESTAMP int(11) default NULL,
  PID int(11) default NULL,
  KEY ID (ID),
  KEY DEVICEID (DEVICEID)
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE itmgmt_comments (
  ID int(11) NOT NULL AUTO_INCREMENT,
  HARDWARE_ID int(11) NOT NULL,
  COMMENTS longtext,
  USER_INSERT varchar(100) DEFAULT NULL,
  DATE_INSERT date DEFAULT NULL,
  ACTION varchar(255) DEFAULT NULL,
  VISIBLE int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

UPDATE operators SET passwd=md5('admin') where ID='admin';

CREATE TABLE languages (
  NAME varchar(60) NOT NULL,
  IMG blob,
  JSON_VALUE longtext,
  PRIMARY KEY (NAME)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE temp_files (
  ID int(11) NOT NULL AUTO_INCREMENT,
  TABLE_NAME varchar(127) DEFAULT NULL,
  FIELDS_NAME varchar(127) DEFAULT NULL,
  FILE blob,
  COMMENT longtext,
  AUTHOR varchar(127) DEFAULT NULL,
  FILE_NAME varchar(127) DEFAULT NULL,
  FILE_TYPE varchar(63) DEFAULT NULL,
  FILE_SIZE int(11) DEFAULT NULL,
  ID_DDE int(11) DEFAULT NULL,
  PRIMARY KEY (ID)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



ALTER TABLE networks ADD COLUMN VIRTUALDEV BOOL not NULL DEFAULT FALSE;
ALTER TABLE hardware ADD COLUMN UUID VARCHAR(63) default NULL;
ALTER TABLE operators ADD COLUMN NEW_ACCESSLVL VARCHAR(63) default NULL;
ALTER TABLE operators ADD COLUMN EMAIL VARCHAR(63) default NULL;
UPDATE operators SET NEW_ACCESSLVL='sadmin' where ACCESSLVL=1;
UPDATE operators SET NEW_ACCESSLVL='admin' where ACCESSLVL=2;
UPDATE operators SET NEW_ACCESSLVL='ladmin' where ACCESSLVL=3;
ALTER TABLE operators ADD COLUMN USER_GROUP VARCHAR(63) default NULL;
ALTER TABLE download_available ADD COLUMN ID_WK int(11) default NULL;

CREATE TABLE downloadwk_tab_values (
  FIELD varchar(100) DEFAULT NULL,
  VALUE varchar(100) DEFAULT NULL,
  LBL varchar(100) DEFAULT NULL,
  ID int(11) NOT NULL AUTO_INCREMENT,
  DEFAULT_FIELD int(1) DEFAULT NULL,
  PRIMARY KEY (ID)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO downloadwk_tab_values (ID,FIELD,VALUE,LBL,DEFAULT_FIELD) values (1,'TAB','INFO_DEM','1033',1);
INSERT INTO downloadwk_tab_values (ID,FIELD,VALUE,LBL,DEFAULT_FIELD) values (2,'TAB','INFO_PAQUET','1034',1);
INSERT INTO downloadwk_tab_values (ID,FIELD,VALUE,LBL,DEFAULT_FIELD) values (3,'TAB','INFO_CONF','1035',1);
INSERT INTO downloadwk_tab_values (ID,FIELD,VALUE,LBL,DEFAULT_FIELD) values (4,'TAB','INFO_VALID','1036',1);
INSERT INTO downloadwk_tab_values (ID,FIELD,VALUE,LBL,DEFAULT_FIELD) values (5,'TAB','INFO_HISTO','1052',1);

CREATE TABLE downloadwk_fields (
  TAB varchar(100) DEFAULT NULL,
  FIELD varchar(100) DEFAULT NULL,
  TYPE int(11) DEFAULT NULL,
  LBL varchar(100) DEFAULT NULL,
  MUST_COMPLETED int(11) DEFAULT NULL,
  ID int(11) NOT NULL AUTO_INCREMENT,
  VALUE varchar(255) DEFAULT NULL,
  DEFAULT_FIELD int(1) DEFAULT NULL,
  RESTRICTED int(1) DEFAULT NULL,
  LINK_STATUS int(11) DEFAULT NULL,
  PRIMARY KEY (ID)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO downloadwk_fields (TAB,FIELD,TYPE,LBL,MUST_COMPLETED,VALUE,DEFAULT_FIELD,RESTRICTED,LINK_STATUS,ID) values (1,'USER',3,1038,1,'loggeduser',1,0,0,1);
INSERT INTO downloadwk_fields (TAB,FIELD,TYPE,LBL,MUST_COMPLETED,VALUE,DEFAULT_FIELD,RESTRICTED,LINK_STATUS,ID) values (2,'NAME_TELEDEPLOY',0,1037,1,'',1,0,0,2);
INSERT INTO downloadwk_fields (TAB,FIELD,TYPE,LBL,MUST_COMPLETED,VALUE,DEFAULT_FIELD,RESTRICTED,LINK_STATUS,ID) values (2,'INFO_PACK',0,53,1,'',1,0,0,3);
INSERT INTO downloadwk_fields (TAB,FIELD,TYPE,LBL,MUST_COMPLETED,VALUE,DEFAULT_FIELD,RESTRICTED,LINK_STATUS,ID) values (3,'PRIORITY',2,1039,1,'',1,0,0,4);
INSERT INTO downloadwk_fields (TAB,FIELD,TYPE,LBL,MUST_COMPLETED,VALUE,DEFAULT_FIELD,RESTRICTED,LINK_STATUS,ID) values (3,'NOTIF_USER',2,1040,1,'',1,0,0,5);
INSERT INTO downloadwk_fields (TAB,FIELD,TYPE,LBL,MUST_COMPLETED,VALUE,DEFAULT_FIELD,RESTRICTED,LINK_STATUS,ID) values (3,'REPORT_USER',2,1041,1,'',1,0,0,6);
INSERT INTO downloadwk_fields (TAB,FIELD,TYPE,LBL,MUST_COMPLETED,VALUE,DEFAULT_FIELD,RESTRICTED,LINK_STATUS,ID) values (3,'REBOOT',2,1042,1,'',1,0,0,7);
INSERT INTO downloadwk_fields (TAB,FIELD,TYPE,LBL,MUST_COMPLETED,VALUE,DEFAULT_FIELD,RESTRICTED,LINK_STATUS,ID) values (4,'VALID_INSTALL',6,1043,1,'',1,0,0,8);
INSERT INTO downloadwk_fields (TAB,FIELD,TYPE,LBL,MUST_COMPLETED,VALUE,DEFAULT_FIELD,RESTRICTED,LINK_STATUS,ID) values (4,'STATUS',2,1046,0,'2',1,1,0,9);
INSERT INTO downloadwk_fields (TAB,FIELD,TYPE,LBL,MUST_COMPLETED,VALUE,DEFAULT_FIELD,RESTRICTED,LINK_STATUS,ID) values (5,'LIST_HISTO',10,1052,0,'select AUTHOR,DATE,ACTION from downloadwk_history where id_dde=%s$$$$OLD_MODIF',1,0,0,10);

CREATE TABLE downloadwk_pack (
  ID int(11) NOT NULL AUTO_INCREMENT,
  LOGIN_USER varchar(63) DEFAULT NULL,
  GROUP_USER varchar(63) DEFAULT NULL,
  Q_DATE int(11) DEFAULT NULL,
  fields_1 varchar(255) DEFAULT NULL,
  fields_2 varchar(255) DEFAULT NULL,
  fields_3 varchar(255) DEFAULT NULL,
  fields_4 varchar(255) DEFAULT NULL,
  fields_5 varchar(255) DEFAULT NULL,
  fields_6 varchar(255) DEFAULT NULL,
  fields_7 varchar(255) DEFAULT NULL,
  fields_8 varchar(255) DEFAULT NULL,
  fields_9 varchar(255) DEFAULT NULL,
  fields_10 varchar(255) DEFAULT NULL,
  PRIMARY KEY (ID)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE downloadwk_statut_request (
  ID int(11) NOT NULL AUTO_INCREMENT,
  NAME varchar(20) DEFAULT NULL,
  LBL varchar(63) DEFAULT NULL,
  ACTIF int(11) DEFAULT NULL,
  PRIMARY KEY (ID)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO downloadwk_statut_request (ID,NAME,LBL,ACTIF) values (1,'NIV0','DELETE',0);
INSERT INTO downloadwk_statut_request (ID,NAME,LBL,ACTIF) values (2,'NIV1','WAITING FOR INCLUSION',0);
INSERT INTO downloadwk_statut_request (ID,NAME,LBL,ACTIF) values (3,'NIV2','ACKNOWLEDGEMENT',0);
INSERT INTO downloadwk_statut_request (ID,NAME,LBL,ACTIF) values (4,'NIV3','REFUSAL',0);
INSERT INTO downloadwk_statut_request (ID,NAME,LBL,ACTIF) values (5,'NIV4','NEED TO CHANGE',0);
INSERT INTO downloadwk_statut_request (ID,NAME,LBL,ACTIF) values (6,'NIV5','CREATE PACKAGE',0);
INSERT INTO downloadwk_statut_request (ID,NAME,LBL,ACTIF) values (7,'NIV6','LOCAL TEST',0);
INSERT INTO downloadwk_statut_request (ID,NAME,LBL,ACTIF) values (8,'NIV7','PERIMETER LIMITED DEPLOYMENT',0);
INSERT INTO downloadwk_statut_request (ID,NAME,LBL,ACTIF) values (9,'NIV8','DURING DEPLOYMENT',0);

CREATE TABLE downloadwk_conf_values (
  FIELD int(11) DEFAULT NULL,
  VALUE varchar(100) DEFAULT NULL,
  ID int(11) NOT NULL AUTO_INCREMENT,
  DEFAULT_FIELD int(1) DEFAULT NULL,
  PRIMARY KEY (ID)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE downloadwk_history (
  ID int(11) NOT NULL AUTO_INCREMENT,
  ID_DDE int(11) DEFAULT NULL,
  AUTHOR varchar(127) DEFAULT NULL,
  DATE date DEFAULT NULL,
  ACTION longtext,
  PRIMARY KEY (ID)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE accountinfo_config(
  ID int(11) NOT NULL AUTO_INCREMENT,
  NAME_ACCOUNTINFO varchar(127) DEFAULT NULL,
  TYPE int(11) DEFAULT NULL,
  NAME varchar(127) DEFAULT NULL,
  ID_TAB int(11) DEFAULT NULL,
  COMMENT varchar(255) DEFAULT NULL,
  SHOW_ORDER int(11) NOT NULL,
  PRIMARY KEY (ID)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

insert into config (NAME,IVALUE,TVALUE,COMMENTS) values ('TAB_ACCOUNTAG_1',1,'TAG','Default TAB on computers accountinfo');
insert into config (NAME,IVALUE,TVALUE,COMMENTS) values ('TAB_ACCOUNTSNMP_1',1,'TAG','Default TAB on snmp accountinfo');
ALTER TABLE subnet CHANGE ID ID VARCHAR(63);

CREATE TABLE blacklist_subnet(
	ID INTEGER auto_increment,
	SUBNET VARCHAR(20),
	MASK VARCHAR(20),
	PRIMARY KEY(SUBNET,MASK),
	INDEX ID(ID)
) ENGINE = MYISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE accountinfo_config ADD COLUMN ACCOUNT_TYPE VARCHAR(63) default NULL;
INSERT INTO accountinfo_config (`ID`,`NAME_ACCOUNTINFO`,`TYPE`,`NAME`,`ID_TAB`,`COMMENT`,`SHOW_ORDER`,`ACCOUNT_TYPE`) VALUES (1,'TAG',0,'TAG',1,'TAG',1,'COMPUTERS'),(2,'TAG',0,'TAG',1,'TAG',1,'SNMP');
UPDATE accountinfo_config SET ACCOUNT_TYPE='COMPUTERS' where ACCOUNT_TYPE IS NULL;


CREATE TABLE snmp_accountinfo (
  SNMP_ID INTEGER NOT NULL,
  TAG VARCHAR(127) default 'NA',
  primary key(SNMP_ID),
  INDEX TAG (TAG)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DELETE FROM config WHERE name='SNMP_URI' or name='SNMP_DIR';
insert into config (NAME,IVALUE,COMMENTS) values ('SNMP_INVENTORY_DIFF',1,'Configure engine to update snmp inventory regarding to snmp_laststate table (lower DB backend load)');
INSERT INTO config VALUES ('SNMP_DIR','','@SNMP_DIR@','Directory for download files');

ALTER TABLE softwares ADD COLUMN GUID VARCHAR(63) DEFAULT NULL;
ALTER TABLE softwares ADD COLUMN LANGUAGE VARCHAR(63) DEFAULT NULL;
ALTER TABLE softwares ADD COLUMN INSTALLDATE DATETIME DEFAULT NULL ;
ALTER TABLE softwares ADD COLUMN BITSWIDTH int(11) DEFAULT NULL;


CREATE TABLE snmp (
  ID INTEGER NOT NULL AUTO_INCREMENT,
  IPADDR VARCHAR(63) DEFAULT NULL,
  MACADDR VARCHAR(127) NOT NULL,
  SNMPDEVICEID VARCHAR(127) NOT NULL,
  NAME VARCHAR(127) DEFAULT NULL,
  DESCRIPTION VARCHAR(255) DEFAULT NULL,
  CONTACT VARCHAR(255) DEFAULT NULL,
  LOCATION VARCHAR(255) DEFAULT NULL,
  UPTIME VARCHAR(127) DEFAULT NULL,
  DOMAIN VARCHAR(127) DEFAULT NULL,
  TYPE VARCHAR(63) DEFAULT NULL,
  LASTDATE DATETIME default NULL,
  CHECKSUM BIGINT UNSIGNED DEFAULT 0,
  PRIMARY KEY (ID) 
) DEFAULT CHARSET=UTF8mb4;

CREATE TABLE snmp_printers (
  SNMP_ID INTEGER NOT NULL,
  NAME VARCHAR(127) DEFAULT NULL,
  SERIALNUMBER VARCHAR(127) DEFAULT NULL,
  COUNTER VARCHAR(127) DEFAULT NULL,
  STATUS VARCHAR(127) DEFAULT NULL,
  ERRORSTATE VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (SNMP_ID)
) DEFAULT CHARSET=UTF8mb4;

CREATE TABLE snmp_trays (
  ID INTEGER NOT NULL AUTO_INCREMENT,
  SNMP_ID INTEGER NOT NULL,
  NAME VARCHAR(127) DEFAULT NULL,
  DESCRIPTION VARCHAR(255) DEFAULT NULL,
  LEVEL VARCHAR(127) DEFAULT NULL,
  MAXCAPACITY INTEGER DEFAULT NULL,
  PRIMARY KEY (ID, SNMP_ID)
) DEFAULT CHARSET=UTF8;

CREATE TABLE snmp_cartridges (
  ID INTEGER NOT NULL AUTO_INCREMENT,
  SNMP_ID INTEGER NOT NULL,
  DESCRIPTION VARCHAR(255) DEFAULT NULL,
  TYPE VARCHAR(63) DEFAULT NULL,
  LEVEL INTEGER DEFAULT NULL,
  MAXCAPACITY INTEGER DEFAULT NULL,
  COLOR VARCHAR(127) DEFAULT NULL,
  PRIMARY KEY (ID, SNMP_ID)
) DEFAULT CHARSET=UTF8;
		
CREATE TABLE snmp_networks (
  ID INTEGER NOT NULL AUTO_INCREMENT,
  SNMP_ID INTEGER NOT NULL,
  DESCRIPTION VARCHAR(255) DEFAULT NULL,
  MACADDR VARCHAR(127) DEFAULT NULL,
  DEVICEMACADDR VARCHAR(127) DEFAULT NULL,
  SLOT VARCHAR(127) DEFAULT NULL,
  STATUS VARCHAR(127) DEFAULT NULL,
  SPEED VARCHAR(63) DEFAULT NULL,
  TYPE VARCHAR(63) DEFAULT NULL,
  DEVICEADDRESS VARCHAR(127) DEFAULT NULL,
  DEVICENAME VARCHAR(127) DEFAULT NULL,
  DEVICEPORT VARCHAR(127) DEFAULT NULL,
  DEVICETYPE VARCHAR(63) DEFAULT NULL,
  TYPEMIB VARCHAR(127) DEFAULT NULL,
  IPADDR VARCHAR(63) DEFAULT NULL,
  IPMASK VARCHAR(63) DEFAULT NULL,
  IPGATEWAY VARCHAR(63) DEFAULT NULL,
  IPSUBNET VARCHAR(63) DEFAULT NULL,
  IPDHCP VARCHAR(63) DEFAULT NULL,
  DRIVER VARCHAR(127) DEFAULT NULL,
  VIRTUALDEV INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (ID, SNMP_ID)
) DEFAULT CHARSET=UTF8;
		
CREATE TABLE snmp_switchs (
  ID INTEGER NOT NULL AUTO_INCREMENT,
  SNMP_ID INTEGER NOT NULL,
  MANUFACTURER VARCHAR(127) DEFAULT NULL,
  REFERENCE VARCHAR(255) DEFAULT NULL,
  TYPE VARCHAR(63) DEFAULT NULL,
  SOFTVERSION VARCHAR(63) DEFAULT NULL,
  FIRMVERSION VARCHAR(63) DEFAULT NULL,
  SERIALNUMBER VARCHAR(127) DEFAULT NULL,
  REVISION VARCHAR(63) DEFAULT NULL,
  DESCRIPTION VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (ID, SNMP_ID)
) DEFAULT CHARSET=UTF8;
	
CREATE TABLE snmp_blades (
  SNMP_ID INTEGER NOT NULL AUTO_INCREMENT,
  SERIALNUMBER VARCHAR(127) DEFAULT NULL,
  SYSTEM VARCHAR(127) DEFAULT NULL, 
  PRIMARY KEY (SNMP_ID)
) DEFAULT CHARSET=UTF8;
		
CREATE TABLE snmp_storages (
  ID INTEGER NOT NULL AUTO_INCREMENT DEFAULT NULL,
  SNMP_ID INTEGER DEFAULT NULL,
  DESCRIPTION VARCHAR(255) DEFAULT NULL,
  MANUFACTURER VARCHAR(127) DEFAULT NULL,
  NAME VARCHAR(127) DEFAULT NULL,
  MODEL VARCHAR(127) DEFAULT NULL,
  DISKSIZE INTEGER DEFAULT NULL,
  TYPE VARCHAR(63) DEFAULT NULL,
  SERIALNUMBER VARCHAR(127) DEFAULT NULL,
  FIRMWARE VARCHAR(127) DEFAULT NULL,
  PRIMARY KEY (ID, SNMP_ID)
) DEFAULT CHARSET=UTF8;
		
CREATE TABLE snmp_drives (
  ID INTEGER NOT NULL AUTO_INCREMENT,
  SNMP_ID INTEGER NOT NULL,
  LETTER VARCHAR(63) DEFAULT NULL,
  TYPE VARCHAR(63) DEFAULT NULL,
  FILESYSTEM VARCHAR(63) DEFAULT NULL,
  TOTAL INTEGER DEFAULT NULL,
  FREE INTEGER DEFAULT NULL,
  NUMFILES INTEGER DEFAULT NULL,
  VOLUMN VARCHAR(63) DEFAULT NULL,
  LABEL VARCHAR(127) DEFAULT NULL,
  SERIAL VARCHAR(127) DEFAULT NULL,
  PRIMARY KEY (ID, SNMP_ID)
) DEFAULT CHARSET=UTF8;
		
CREATE TABLE snmp_powersupplies (
  ID INTEGER NOT NULL AUTO_INCREMENT,
  SNMP_ID INTEGER DEFAULT NULL,
  MANUFACTURER VARCHAR(127) DEFAULT NULL,
  REFERENCE VARCHAR(255) DEFAULT NULL,
  TYPE VARCHAR(63) DEFAULT NULL,
  SERIALNUMBER VARCHAR(127) DEFAULT NULL,
  DESCRIPTION VARCHAR(255) DEFAULT NULL,
  REVISION VARCHAR(63) DEFAULT NULL,
  PRIMARY KEY (ID, SNMP_ID)
) DEFAULT CHARSET=UTF8;
		
CREATE TABLE snmp_fans (
  ID INTEGER NOT NULL AUTO_INCREMENT,
  SNMP_ID INTEGER NOT NULL,
  DESCRIPTION VARCHAR(255) DEFAULT NULL,
  REFERENCE VARCHAR(255) DEFAULT NULL,
  REVISION VARCHAR(63) DEFAULT NULL,
  SERIALNUMBER VARCHAR(127) DEFAULT NULL,
  MANUFACTURER VARCHAR(127) DEFAULT NULL,
  TYPE VARCHAR(63) DEFAULT NULL,
  PRIMARY KEY (ID, SNMP_ID)
) DEFAULT CHARSET=UTF8;
		
CREATE TABLE snmp_firewalls (
  SNMP_ID INTEGER NOT NULL AUTO_INCREMENT,
  SERIALNUMBER VARCHAR(127) DEFAULT NULL,
  SYSTEM VARCHAR(127) DEFAULT NULL, 
  PRIMARY KEY (SNMP_ID)
) DEFAULT CHARSET=UTF8;
		
CREATE TABLE snmp_switchinfos (
  SNMP_ID INTEGER NOT NULL,
  TYPE VARCHAR(63) DEFAULT NULL,
  PRIMARY KEY (SNMP_ID)
) DEFAULT CHARSET=UTF8;
		
CREATE TABLE snmp_loadbalancers (
  SNMP_ID INTEGER NOT NULL AUTO_INCREMENT,
  SERIALNUMBER VARCHAR(127) DEFAULT NULL,
  SYSTEM VARCHAR(127) DEFAULT NULL, 
  PRIMARY KEY (SNMP_ID)
) DEFAULT CHARSET=UTF8;

CREATE TABLE snmp_computers (
  SNMP_ID INTEGER NOT NULL AUTO_INCREMENT,
  SYSTEM VARCHAR(127) DEFAULT NULL,
  PRIMARY KEY (SNMP_ID)
) DEFAULT CHARSET=UTF8;
		
CREATE TABLE snmp_cards (
  ID INTEGER NOT NULL AUTO_INCREMENT,
  SNMP_ID INTEGER NOT NULL,
  DESCRIPTION VARCHAR(255) DEFAULT NULL,
  REFERENCE VARCHAR(255) DEFAULT NULL,
  FIRMWARE VARCHAR(127) DEFAULT NULL,
  SOFTWARE VARCHAR(127) DEFAULT NULL,
  REVISION VARCHAR(63) DEFAULT NULL,
  SERIALNUMBER VARCHAR(127) DEFAULT NULL,
  MANUFACTURER VARCHAR(127) DEFAULT NULL,
  TYPE VARCHAR(63) DEFAULT NULL,
  PRIMARY KEY (ID, SNMP_ID)
) DEFAULT CHARSET=UTF8;

CREATE TABLE snmp_softwares (
  ID INTEGER NOT NULL AUTO_INCREMENT,
  SNMP_ID INTEGER NOT NULL,
  NAME VARCHAR(127) DEFAULT NULL,
  INSTALLDATE VARCHAR(63) DEFAULT NULL,
  COMMENTS TEXT,
  VERSION VARCHAR(63) default NULL,
  PRIMARY KEY (ID, SNMP_ID)
) DEFAULT CHARSET=UTF8;

CREATE TABLE snmp_memories (
  ID INTEGER NOT NULL AUTO_INCREMENT,
  SNMP_ID INTEGER NOT NULL,
  CAPACITY VARCHAR(63) DEFAULT NULL,
  PRIMARY KEY (ID, SNMP_ID)
) DEFAULT CHARSET=UTF8;

CREATE TABLE snmp_cpus (
  ID INTEGER NOT NULL AUTO_INCREMENT,
  SNMP_ID INTEGER NOT NULL,
  SPEED VARCHAR(63) DEFAULT NULL,
  TYPE VARCHAR(63) DEFAULT NULL,
  MANUFACTURER VARCHAR(127) DEFAULT NULL,
  PRIMARY KEY (ID, SNMP_ID)
) DEFAULT CHARSET=UTF8;

CREATE TABLE snmp_inputs (
  ID INTEGER NOT NULL AUTO_INCREMENT,
  SNMP_ID INTEGER NOT NULL,
  DESCRIPTION VARCHAR(255) DEFAULT NULL,
  TYPE VARCHAR(63) DEFAULT NULL,
  PRIMARY KEY (ID, SNMP_ID)
) DEFAULT CHARSET=UTF8;

CREATE TABLE snmp_ports (
  ID INTEGER NOT NULL AUTO_INCREMENT,
  SNMP_ID INTEGER NOT NULL,
  NAME VARCHAR(127) DEFAULT NULL,
  TYPE VARCHAR(63) DEFAULT NULL,
  PRIMARY KEY (ID, SNMP_ID)
) DEFAULT CHARSET=UTF8;

CREATE TABLE snmp_sounds (
  ID INTEGER NOT NULL AUTO_INCREMENT,
  SNMP_ID INTEGER NOT NULL,
  NAME VARCHAR(127) DEFAULT NULL,  
  PRIMARY KEY (ID, SNMP_ID)
) DEFAULT CHARSET=UTF8;

CREATE TABLE snmp_videos (
  ID INTEGER NOT NULL AUTO_INCREMENT,
  SNMP_ID INTEGER NOT NULL,
  NAME VARCHAR(127) DEFAULT NULL,  
  PRIMARY KEY (ID, SNMP_ID)
) DEFAULT CHARSET=UTF8;


CREATE TABLE snmp_modems (
  ID INTEGER NOT NULL AUTO_INCREMENT,
  SNMP_ID INTEGER NOT NULL,
  NAME VARCHAR(127) DEFAULT NULL,  
  PRIMARY KEY (ID, SNMP_ID)
) DEFAULT CHARSET=UTF8;


CREATE TABLE snmp_localprinters (
  ID INTEGER NOT NULL AUTO_INCREMENT,
  SNMP_ID INTEGER NOT NULL,
  NAME VARCHAR(127) DEFAULT NULL,  
  PRIMARY KEY (ID, SNMP_ID)
) DEFAULT CHARSET=UTF8;


CREATE TABLE snmp_accountinfo (
  SNMP_ID INTEGER NOT NULL,
  TAG VARCHAR(127) default 'NA',
  primary key(SNMP_ID),
  INDEX TAG (TAG)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE snmp_laststate (
SNMP_ID INTEGER NOT NULL,
COMMON VARCHAR(255) DEFAULT NULL,
PRINTERS VARCHAR(255) DEFAULT NULL,
TRAYS VARCHAR(255) DEFAULT NULL,
CARTRIDGES VARCHAR(255) DEFAULT NULL,
NETWORKS VARCHAR(255) DEFAULT NULL,
SWITCHS VARCHAR(255) DEFAULT NULL,
BLADES VARCHAR(255) DEFAULT NULL,
STORAGES VARCHAR(255) DEFAULT NULL,
DRIVES VARCHAR(255) DEFAULT NULL,
POWERSUPPLIES VARCHAR(255) DEFAULT NULL,
FANS VARCHAR(255) DEFAULT NULL,
SWITCHINFOS VARCHAR(255) DEFAULT NULL,
LOADBALANCERS VARCHAR(255) DEFAULT NULL,
CARDS VARCHAR(255) DEFAULT NULL,
COMPUTERS VARCHAR(255) DEFAULT NULL,
SOFTWARES VARCHAR(255) DEFAULT NULL,
MEMORIES VARCHAR(255) DEFAULT NULL,
CPUS VARCHAR(255) DEFAULT NULL,
INPUTS VARCHAR(255) DEFAULT NULL,
PORTS VARCHAR(255) DEFAULT NULL,
SOUNDS VARCHAR(255) DEFAULT NULL,
VIDEOS VARCHAR(255) DEFAULT NULL,
MODEMS VARCHAR(255) DEFAULT NULL,
LOCALPRINTERS VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (SNMP_ID) ) DEFAULT CHARSET=UTF8;

INSERT INTO config VALUES ('INVENTORY_CACHE_REVALIDATE',7,'','the engine will clean the inventory cache structures');
ALTER TABLE groups CHANGE REVALIDATE_FROM REVALIDATE_FROM INT(11) default 0;
UPDATE groups set REVALIDATE_FROM = 0 where REVALIDATE_FROM is null;

DELETE FROM config WHERE name='LOCAL_SERVER' or name='LOCAL_PORT';

INSERT INTO config VALUES ('SESSION_VALIDITY_TIME',600,'','Validity of a session (prolog=>postinventory)');

CREATE TABLE ssl_store (
ID INTEGER NOT NULL AUTO_INCREMENT,
FILE LONGBLOB DEFAULT NULL,
AUTHOR VARCHAR(127) DEFAULT NULL,
FILE_NAME VARCHAR(127) DEFAULT NULL,
FILE_TYPE VARCHAR(20) DEFAULT NULL,
DESCRIPTION VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (ID) ) DEFAULT CHARSET=UTF8;

INSERT INTO config VALUES ('DOWNLOAD_URI_FRAG',0,'@DOWNLOAD_URI_FRAG@','Address to find teledeploy packages fragments to activate');
INSERT INTO config VALUES ('DOWNLOAD_URI_INFO',0,'@DOWNLOAD_URI_INFO@','Address to find teledeploy packages INFO files to activate');
ALTER TABLE snmp_loadbalancers ADD COLUMN MANUFACTURER varchar(127) DEFAULT NULL;
ALTER TABLE snmp_loadbalancers ADD COLUMN TYPE varchar(63) DEFAULT NULL;
ALTER TABLE hardware ADD COLUMN ARCH varchar(10) DEFAULT NULL;


CREATE TABLE snmp_communities (
 ID INTEGER NOT NULL auto_increment,
 VERSION VARCHAR(5) default NULL,
 NAME VARCHAR(127) default NULL,
 USERNAME VARCHAR(127) default NULL,
 AUTHKEY VARCHAR(255) default NULL,
 AUTHPASSWD VARCHAR(255) default NULL,
 PRIMARY KEY (ID)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE accountinfo_config ADD COLUMN DEFAULT_VALUE varchar(127) DEFAULT NULL;
INSERT INTO config VALUES ('CONF_PROFILS_DIR',0,'@CONF_PROFILS_DIR@','Directory for Admin/User profiles');
INSERT INTO config VALUES ('OLD_CONF_DIR',0,'@OLD_CONF_DIR@','Backup directory for Admin/User profiles');
INSERT INTO config VALUES ('WOL_PORT',0,'7,9','Wol ports');
ALTER TABLE temp_files change file file longblob;
CREATE TABLE cpus (
  ID int(11) NOT NULL AUTO_INCREMENT,
  HARDWARE_ID int(11) NOT NULL,
  MANUFACTURER varchar(127) DEFAULT NULL,
  TYPE varchar(63) DEFAULT NULL,
  SERIALNUMBER varchar(127) DEFAULT NULL,
  SPEED varchar(63) DEFAULT NULL,
  CORES int(11) DEFAULT NULL,
  L2CACHESIZE varchar(63) DEFAULT NULL,
  CPUARCH varchar(63) DEFAULT NULL,
  DATA_WIDTH int(11) DEFAULT NULL,
  CURRENT_ADDRESS_WIDTH int(11) DEFAULT NULL,
  LOGICAL_CPUS int(11) DEFAULT NULL,
  VOLTAGE varchar(63) DEFAULT NULL,
  CURRENT_SPEED varchar(63) DEFAULT NULL,
  SOCKET varchar(63) DEFAULT NULL,
  PRIMARY KEY (`ID`,`HARDWARE_ID`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE sim (
  ID int(11) NOT NULL AUTO_INCREMENT,
  HARDWARE_ID int(11) NOT NULL,
  OPERATOR varchar(127) DEFAULT NULL,
  OPNAME varchar(63) DEFAULT NULL,
  COUNTRY varchar(63) DEFAULT NULL,
  SERIALNUMBER varchar(127) DEFAULT NULL,
   PRIMARY KEY (`ID`,`HARDWARE_ID`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE snmp_virtualmachines (
  ID INTEGER NOT NULL AUTO_INCREMENT,
  SNMP_ID INTEGER NOT NULL,
  CONFIG_FILE VARCHAR(255) DEFAULT NULL,
  CPU INTEGER DEFAULT NULL,
  MEMORY INTEGER DEFAULT NULL,
  NAME VARCHAR(127) DEFAULT NULL,
  OS VARCHAR(63) DEFAULT NULL,
  POWER VARCHAR(127) DEFAULT NULL,
  UUID VARCHAR(63) DEFAULT NULL,
  PRIMARY KEY (ID,SNMP_ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE printers ADD COLUMN SERVERNAME VARCHAR(127) DEFAULT NULL;
ALTER TABLE printers ADD COLUMN SHARENAME VARCHAR(127) DEFAULT NULL;
ALTER TABLE printers ADD COLUMN RESOLUTION VARCHAR(50) DEFAULT NULL;
ALTER TABLE printers ADD COLUMN `COMMENT` VARCHAR(255) DEFAULT NULL;
ALTER TABLE printers ADD COLUMN SHARED INTEGER DEFAULT NULL;
ALTER TABLE printers ADD COLUMN NETWORK INTEGER DEFAULT NULL;
ALTER TABLE sim ADD COLUMN DEVICEID VARCHAR(127) DEFAULT NULL;

CREATE TABLE `snmp_mibs` (
  `ID` INTEGER NOT NULL AUTO_INCREMENT,
  `VENDOR` VARCHAR(127) DEFAULT NULL,
  `URL` VARCHAR(255) DEFAULT NULL,
  `CHECKSUM` VARCHAR(127) DEFAULT NULL,
  `VERSION` VARCHAR(5) DEFAULT NULL,
  `PARSER` VARCHAR(5) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
