DELIMITER ;

--
-- Удлинняем колонку body для индексации больших отчётов
--

ALTER TABLE `reportindex` CHANGE `body` `body` LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;

--
-- Процедуры новые для guid длинной 40 символов
--
DROP PROCEDURE IF EXISTS `add_forummessages`;
DROP PROCEDURE IF EXISTS `add_objects`;
DROP PROCEDURE IF EXISTS `add_personallinks`;
DROP PROCEDURE IF EXISTS `add_rating_reportdata`;
DROP PROCEDURE IF EXISTS `add_viewcount_reportdata`;
DROP PROCEDURE IF EXISTS `clear_forummessages`;
DROP PROCEDURE IF EXISTS `clear_objects`;
DROP PROCEDURE IF EXISTS `createtree`;
DROP PROCEDURE IF EXISTS `create_objectsrightstmp`;
DROP PROCEDURE IF EXISTS `create_reportsrightstmp`;
DROP PROCEDURE IF EXISTS `del_personallinks`;
DROP PROCEDURE IF EXISTS `load_objectpath`;
DROP PROCEDURE IF EXISTS `load_objects`;
DROP PROCEDURE IF EXISTS `load_objectsAdmin`;
DROP PROCEDURE IF EXISTS `load_objects_admin`;
DROP PROCEDURE IF EXISTS `load_objecttree`;
DROP PROCEDURE IF EXISTS `load_objecttree_search`;
DROP PROCEDURE IF EXISTS `load_reports`;
DROP PROCEDURE IF EXISTS `load_reportsAdmin`;
DROP PROCEDURE IF EXISTS `move_personallinks`;

SET @@session.time_zone = "+00:00";


INSERT IGNORE INTO `tables` VALUES (0, 'languages');
INSERT IGNORE INTO `tables` VALUES (6, 'users');
INSERT IGNORE INTO `tables` VALUES (7, 'branches');
INSERT IGNORE INTO `tables` VALUES (8, 'polltypes');
INSERT IGNORE INTO `tables` VALUES (801, 'polltypes.description');
INSERT IGNORE INTO `tables` VALUES (9, 'polltypequestions');
INSERT IGNORE INTO `tables` VALUES (90, 'polltypequestionselectvalues');
INSERT IGNORE INTO `tables` VALUES (10, 'projects');
INSERT IGNORE INTO `tables` VALUES (1001, 'projects.description');
INSERT IGNORE INTO `tables` (`idtable`, `tbl_name`) VALUES ('11', 'pollobject');
INSERT IGNORE INTO `tables` VALUES (1101, 'pollingobjects.objectversiondescription');
INSERT IGNORE INTO `tables` VALUES (1102, 'pollingobjects.reportversiondescription');
INSERT IGNORE INTO `tables` VALUES (1103, 'pollingobjectsarchive.objectversiondescription');
INSERT IGNORE INTO `tables` VALUES (1104, 'pollingobjectsarchive.reportversiondescription');
INSERT IGNORE INTO `tables` VALUES (1111, 'pollingobjects.objectversionfullname');
INSERT IGNORE INTO `tables` VALUES (1113, 'pollingobjectsarchive.objectversionfullname');
-- ToDo: change on INSERT OR UPDATE
UPDATE `tables` SET `tbl_name`='pollobject' WHERE `idtable`= 11;
UPDATE `tables` SET `tbl_name`='object.version.fullname' WHERE  `idtable`=1111;
UPDATE `tables` SET `tbl_name`='archive.object.version.fullname' WHERE  `idtable`=1114;
UPDATE `tables` SET `tbl_name`='archive.report.version.description' WHERE  `idtable`=1104;
UPDATE `tables` SET `tbl_name`='archive.object.version.description' WHERE  `idtable`=1103;
UPDATE `tables` SET `tbl_name`='report.version.description' WHERE  `idtable`=1102;
UPDATE `tables` SET `tbl_name`='object.version.description' WHERE  `idtable`=1101;

	
-- Clear all maybe procedure before create
DROP PROCEDURE IF EXISTS alterTableMultiLanguages;
DROP PROCEDURE IF EXISTS ReCreateIndexRightsObjects;
DROP PROCEDURE IF EXISTS alterTableUsers;
DROP PROCEDURE IF EXISTS alterTableForumMessage;
DROP PROCEDURE IF EXISTS ADDforummessagessawIndex;
DROP PROCEDURE IF EXISTS ADDfrmm_ischanged;
DROP PROCEDURE IF EXISTS ADDss_key;
DROP PROCEDURE IF EXISTS `user_add_column`;
DROP PROCEDURE IF EXISTS `objects_add_column`;
DROP PROCEDURE IF EXISTS `branches_add_column`;
DROP PROCEDURE IF EXISTS `poll_main_table_create`;
DROP PROCEDURE IF EXISTS `rights_admin_polling_table_create`;
DROP PROCEDURE IF EXISTS `mailqueue_table_create`;
DROP PROCEDURE IF EXISTS `maitypes_table_create`;
DROP PROCEDURE IF EXISTS `objectsprohibitionrightscopy_table_create`;
DROP PROCEDURE IF EXISTS `users_remove_column_author`;
DROP PROCEDURE IF EXISTS `resourcefromtablespollarchive_table_create`;
DROP PROCEDURE IF EXISTS `userpositions_table_create`;
DROP PROCEDURE IF EXISTS `poll_rightspollingobjects_table_create`;




-- Добавленно 050214

DELIMITER $$
-- Добавленно 050214
-- Переделано из соображений NOT EXIST

CREATE PROCEDURE `user_add_column`()
BEGIN

	IF (NOT EXISTS(SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'users' AND COLUMN_NAME='user_notification_allallow'  AND TABLE_SCHEMA = (SELECT DATABASE()))) THEN
		ALTER TABLE users ADD user_notification_allallow tinyint(1) AFTER user_notification_newversions;
		UPDATE users  SET user_notification_allallow = 0;
	END IF;
-- Добавлено 11.08.2015 для поддержки guid'ов
	IF (NOT EXISTS(SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'users' AND COLUMN_NAME='user_guid'  AND TABLE_SCHEMA = (SELECT DATABASE()))) THEN
		ALTER TABLE users ADD user_guid varchar(40) AFTER user_hash;
		ALTER TABLE `users` ADD INDEX ( `user_guid` ); 
	END IF;
END$$


-- Добавлено 11.08.2015 для сокрытия элементов в дереве

-- Добавленно 050214
-- Переделано из соображений NOT EXIST

CREATE PROCEDURE `objects_add_column`()
BEGIN
	IF (NOT EXISTS(SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'objects' AND COLUMN_NAME='o_hidefortree'  AND TABLE_SCHEMA = (SELECT DATABASE()))) THEN
		ALTER TABLE `objects` ADD o_hidefortree int(1)  AFTER o_useinsearch;
		UPDATE `objects` SET o_hidefortree = 0;
	END IF;
END$$

CREATE PROCEDURE `branches_add_column`()
BEGIN
	IF (NOT EXISTS(SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'branches' AND COLUMN_NAME='iscomplete'  AND TABLE_SCHEMA = (SELECT DATABASE()))) THEN
		ALTER TABLE branches ADD iscomplete tinyint(1) NOT NULL DEFAULT '0' AFTER `ismain`;
		UPDATE `branches` SET `iscomplete` = 1;
	END IF;
END$$
-- Добавленно 280619

CREATE PROCEDURE `rights_admin_polling_table_create`()
BEGIN
	IF (NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'rightsadminpollingobjects' AND TABLE_SCHEMA = (SELECT DATABASE()))) THEN		
		DROP TABLE IF EXISTS `rightsadminpollingobjects`;
		CREATE TABLE `rightsadminpollingobjects` (
				`iduser` INT(11) NOT NULL,
				`skpolltype` INT(11) NOT NULL,
				`skbranch` INT(11) NULL,
				`skproject` INT(11) NULL, 
				`deny_allow` TINYINT(1) NOT NULL,
				PRIMARY KEY (`iduser`, `skpolltype`, `deny_allow`)
			)
			COLLATE='utf8mb4_general_ci'
			ENGINE=InnoDB;
	END IF;
END$$

CREATE PROCEDURE `poll_main_table_create`()
BEGIN
	IF (NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'branches' AND TABLE_SCHEMA = (SELECT DATABASE()))) THEN		
		DROP TABLE IF EXISTS `branches`;
		CREATE TABLE IF NOT EXISTS `branches` (
			`idbranch` VARCHAR(50) NOT NULL,
			`skbranch` INT(11) NOT NULL AUTO_INCREMENT,
			`ismain` TINYINT(4) NOT NULL,
			UNIQUE INDEX `idbranch` (`idbranch`),
			INDEX `skbranch` (`skbranch`)
		)
		ENGINE=InnoDB  DEFAULT CHARSET=utf8;
		INSERT IGNORE INTO `branches` VALUES ('0',0,1);

		CREATE TABLE IF NOT EXISTS `polltypes` (
			`idpolltype` VARCHAR(150) NOT NULL,
			`skpolltype` INT NOT NULL AUTO_INCREMENT,
			`AllowComment` TINYINT NULL,
			PRIMARY KEY (`skpolltype`),
			UNIQUE INDEX `idpolltype` (`idpolltype`)
		)
		ENGINE=InnoDB DEFAULT CHARSET=utf8;

		CREATE TABLE IF NOT EXISTS `polltypequestiontypes` (
			`idpolltypequestiontype` INT NOT NULL,
			`description` TINYTEXT NOT NULL,
			PRIMARY KEY (`idpolltypequestiontype`)
		)
		ENGINE=InnoDB DEFAULT CHARSET=utf8;

		CREATE TABLE IF NOT EXISTS `polltypequestions` (
			`idpolltypequestion` VARCHAR(150) NOT NULL,
			`skpolltypequestion` INT NOT NULL AUTO_INCREMENT,
			`skpolltype` INT NOT NULL,
			`idpolltypequestiontype` INT NOT NULL DEFAULT '1',
			UNIQUE INDEX `idpolltypequestion` (`idpolltypequestion`),
			PRIMARY KEY (`skpolltypequestion`)
		)
		ENGINE=InnoDB DEFAULT CHARSET=utf8;

		CREATE TABLE IF NOT EXISTS `polltypequestionselectvalues` (
			`idpolltypequestionselectvalue` VARCHAR(150) NOT NULL,
			`skpolltypequestionselectvalue` INT NOT NULL AUTO_INCREMENT,
			`skpolltypequestion` INT NOT NULL,
			UNIQUE INDEX `idpolltypequestionselectvalue` (`idpolltypequestionselectvalue`),
			PRIMARY KEY (`skpolltypequestionselectvalue`)
		)
		ENGINE=InnoDB DEFAULT CHARSET=utf8;


		INSERT IGNORE INTO `polltypequestiontypes` SELECT 1, 'CheckBox' UNION SELECT 2, 'List';  

		CREATE TABLE IF NOT EXISTS `projects` (
			`idproject` VARCHAR(150) NOT NULL,
			`skproject` INT NOT NULL AUTO_INCREMENT,
			`startdate` VARCHAR(10) NOT NULL,
			`enddate` VARCHAR(10) NOT NULL,	
			PRIMARY KEY (`skproject`),
			UNIQUE INDEX `idproject` (`idproject`)
		)
		ENGINE=InnoDB DEFAULT CHARSET=utf8;

		CREATE TABLE IF NOT EXISTS `pollingobjectstatus` (
			`idpollingobjectstatus` TINYINT(4) NOT NULL,
			`description` VARCHAR(10) NOT NULL,	
			PRIMARY KEY (`idpollingobjectstatus`)
		)
		ENGINE=InnoDB DEFAULT CHARSET=utf8;

		INSERT IGNORE INTO `pollingobjectstatus` VALUES (0,'Остановлен'), (1,'Активен'), (2,'Не активен');


		CREATE TABLE IF NOT EXISTS `pollingobjects` (
			`idpollingobject` VARCHAR(150) NOT NULL,
			`skpollingobject` INT NOT NULL AUTO_INCREMENT,
			`objectversion` VARCHAR(250) NOT NULL,
			`objectversionhash` VARCHAR(128) NOT NULL,
			`objectversiondate_tmst` INT NOT NULL,
			`objectversionuhash` VARCHAR(250) NOT NULL,
			`reportversion` VARCHAR(250) NOT NULL,
			`reportversiondate_tmst` INT NOT NULL,
			`reportversionuhash` VARCHAR(250) NOT NULL,
			`skbranch` INT NOT NULL,
			`skproject` INT NULL,
			`o_guid` VARCHAR(40) NOT NULL,
			`r_guid` VARCHAR(40) NOT NULL,
			PRIMARY KEY (`skpollingobject`),
			UNIQUE INDEX `idpollingobject` (`idpollingobject`)
		)
		ENGINE=InnoDB DEFAULT CHARSET=utf8;


		CREATE TABLE IF NOT EXISTS `pollingobjectsarchive` (
			`idpollingobjectarchive` INT NOT NULL AUTO_INCREMENT,
			`skpollingobject` INT NOT NULL,
			`objectversion` VARCHAR(150) NOT NULL,
			`objectversionhash` VARCHAR(128) NOT NULL,
			`objectversiondate_tmst` INT NOT NULL,
			`objectversionuhash` VARCHAR(250) NOT NULL,
			`reportversion` VARCHAR(250) NOT NULL,
			`reportversiondate_tmst` INT NOT NULL,
			`reportversionuhash` VARCHAR(250) NOT NULL,
			`isfileswassave` TINYINT NOT NULL DEFAULT '0',
			`o_guid` VARCHAR(40) NOT NULL,
			`r_guid` VARCHAR(40) NOT NULL,
			PRIMARY KEY (`idpollingobjectarchive`),
			UNIQUE INDEX `objectversion` (`objectversion`)
		)
		ENGINE=InnoDB DEFAULT CHARSET=utf8;


		CREATE TABLE IF NOT EXISTS `useranswers` (
			`idpollingobject` VARCHAR(250) NOT NULL,
			`idpolltype` VARCHAR(250) NOT NULL,
			`user_hash` VARCHAR(64) NOT NULL,
			`idpollingobjectstatus` TINYINT NOT NULL DEFAULT '10',
			PRIMARY KEY (`idpollingobject`(91),`idpolltype`(36),`user_hash`(64)) -- STU fix "Specified key was too long; max key length is 767 bytes" 91(need maximize)+36(guid)+64(hashsid)=191=>191*4=764<767
		)
		ENGINE=InnoDB DEFAULT CHARSET=utf8;

		CREATE TABLE IF NOT EXISTS `useranswersvalues` (
			`skpollingobject` INT NOT NULL,
			`skpolltype` INT NOT NULL,
			`iduser` INT NOT NULL,
			`uav_value` INT NOT NULL,
			`uav_body` LONGTEXT NULL,
			`idforummessage` BIGINT NULL,
			`uav_tmst` INT NULL,
			PRIMARY KEY (`skpollingobject`,`skpolltype`,`iduser`)
		)
		ENGINE=InnoDB DEFAULT CHARSET=utf8;

		CREATE TABLE IF NOT EXISTS `useranswersvaluesarchive` (
			`idpollingobjectarchive` INT NOT NULL,
			`skpolltype` INT NOT NULL,
			`iduser` INT NOT NULL,
			`uav_value` INT NOT NULL,
			`uav_body` LONGTEXT NULL,
			`idforummessage` BIGINT NULL,
			`uav_tmst` INT NULL,
			PRIMARY KEY (`idpollingobjectarchive`,`skpolltype`,`iduser`)
		)
		ENGINE=InnoDB DEFAULT CHARSET=utf8;

		CREATE TABLE IF NOT EXISTS `useranswersvaluesarchivetext` (
			`idpollingobjectarchive` INT NOT NULL,
			`skpolltype` INT NOT NULL,
			`iduser` INT NOT NULL,
			`idlanguage` INT NOT NULL,
			`text` VARCHAR(2500) NULL,
			PRIMARY KEY (`idpollingobjectarchive`,`skpolltype`,`iduser`,`idlanguage`)
		)
		ENGINE=InnoDB DEFAULT CHARSET=utf8;
		
		
		
		ALTER TABLE `personallinks` ADD COLUMN skbranch INT;
		ALTER TABLE `personallinks` ADD COLUMN addurlparams VARCHAR(2000);
		UPDATE personallinks SET skbranch = 1;
		ALTER TABLE `personallinks`
			CHANGE COLUMN `skbranch` `skbranch` INT(11) NOT NULL AFTER `r_guid`;
		ALTER TABLE `personallinks`
			DROP INDEX `o_guid`,
			ADD INDEX `o_guid` (`o_guid`, `r_guid`, `skbranch`);	


		ALTER TABLE `reports` DROP COLUMN r_name;
		ALTER TABLE `reports` DROP COLUMN r_expirationdate;
		ALTER TABLE `reports` DROP COLUMN r_creationdate;
		ALTER TABLE `reports` ADD COLUMN skbranch INT;
		UPDATE reports SET skbranch = 1;
		CREATE INDEX indx_reports_skbranch ON reports(skbranch);	

		ALTER TABLE `rightsreports` ADD COLUMN skbranch INT;
		UPDATE rightsreports SET skbranch = 1;
		ALTER TABLE `rightsreports`
			ALTER `skbranch` DROP DEFAULT;
		ALTER TABLE `rightsreports`
			CHANGE COLUMN `skbranch` `skbranch` INT(11) NOT NULL AFTER `deny_allow`,
			DROP PRIMARY KEY,
			ADD PRIMARY KEY (`iduser`, `idreport`, `deny_allow`, `skbranch`);
		CREATE INDEX indx_rightsreports_skbranch ON rightsreports(skbranch);

		ALTER TABLE `rightsobjects` ADD COLUMN skbranch INT;
		UPDATE rightsobjects SET skbranch = 1;
		ALTER TABLE `rightsobjects`
			ALTER `skbranch` DROP DEFAULT;
		ALTER TABLE `rightsobjects`
			CHANGE COLUMN `skbranch` `skbranch` INT(11) NOT NULL AFTER `deny_allow`,
			DROP PRIMARY KEY,
			ADD PRIMARY KEY (`iduser`, `idobject`, `deny_allow`, `skbranch`);
		CREATE INDEX indx_rightsobjects_skbranch ON rightsobjects(skbranch);

		ALTER TABLE `resourcefromtables` ADD COLUMN skbranch INT;
		UPDATE resourcefromtables SET skbranch = 1;
		ALTER TABLE `resourcefromtables`
			ALTER `skbranch` DROP DEFAULT;
		ALTER TABLE `resourcefromtables`
			CHANGE COLUMN `skbranch` `skbranch` INT(11) NOT NULL AFTER `idlanguage`,
			DROP PRIMARY KEY,
			ADD PRIMARY KEY (`idrecord`, `idtable`, `idlanguage`, `skbranch`);

		ALTER TABLE `titleindex` ADD COLUMN skbranch INT;
		UPDATE titleindex SET skbranch = 1;
		ALTER TABLE `titleindex`
			ALTER `skbranch` DROP DEFAULT;
		ALTER TABLE `titleindex`
			CHANGE COLUMN `skbranch` `skbranch` INT(11) NOT NULL AFTER `idlanguage`,
			DROP PRIMARY KEY,
			ADD PRIMARY KEY (`idobject`, `idreport`, `idlanguage`, `skbranch`);

		ALTER TABLE `reportsusersquery` ADD COLUMN skbranch INT;
		UPDATE reportsusersquery SET skbranch = 1;
		ALTER TABLE `reportsusersquery`
			ALTER `skbranch` DROP DEFAULT;	
		ALTER TABLE `reportsusersquery` ADD COLUMN idlanguage INT NULL;
		ALTER TABLE `reportsusersquery`
			CHANGE COLUMN `skbranch` `skbranch` INT(11) NOT NULL AFTER `idlanguage`,
			DROP PRIMARY KEY,
			ADD PRIMARY KEY (`o_guid`, `r_guid`, `iduser`, `idlanguage`, `skbranch`);


		-- ALTER TABLE `titleindex` ADD COLUMN skbranch INT;
		UPDATE titleindex SET skbranch = 1;
		ALTER TABLE `titleindex`
			ALTER `skbranch` DROP DEFAULT;
		ALTER TABLE `titleindex`
			CHANGE COLUMN `skbranch` `skbranch` INT(11) NOT NULL AFTER `idlanguage`,
			DROP PRIMARY KEY,
			ADD PRIMARY KEY (`idobject`, `idreport`, `idlanguage`, `skbranch`);



		ALTER TABLE `objects` DROP COLUMN o_name;
		ALTER TABLE `objects` ADD COLUMN skbranch INT;
		UPDATE objects SET skbranch = 1 WHERE parent_idobject != 0; -- o_guid = '00000000-0000-0000-0000-000000000000'; 
		ALTER TABLE `objects`
			DROP INDEX `index_objects_o_guid`,
			ADD UNIQUE INDEX `index_objects_o_guid` (`o_guid`, `skbranch`);
		CREATE INDEX indx_objects_skbranch ON objects (skbranch);


		ALTER TABLE `objectlinks` ADD COLUMN skbranch INT;
		UPDATE objectlinks SET skbranch = 1;
		ALTER TABLE `objectlinks`
			ALTER `skbranch` DROP DEFAULT;
		ALTER TABLE `objectlinks`
			CHANGE COLUMN `skbranch` `skbranch` INT(11) NOT NULL AFTER `idobject`,
			DROP PRIMARY KEY,
			ADD PRIMARY KEY (`iduser`, `idobject`, `skbranch`, `ol_type`);


		ALTER TABLE `avgreportdata` ADD COLUMN skbranch INT;
		UPDATE avgreportdata SET skbranch = 1;
		ALTER TABLE `avgreportdata`
			ALTER `skbranch` DROP DEFAULT;
		ALTER TABLE `avgreportdata`
			CHANGE COLUMN `skbranch` `skbranch` INT(11) NOT NULL AFTER `r_guid`,
			DROP PRIMARY KEY,
			ADD PRIMARY KEY (`o_guid`, `r_guid`, `skbranch`);

		ALTER TABLE `reportdata` ADD COLUMN skbranch INT;
		UPDATE reportdata SET skbranch = 1;
		ALTER TABLE `reportdata`
			ALTER `skbranch` DROP DEFAULT;
		ALTER TABLE `reportdata`
			DROP INDEX `o_guid`,
			ADD INDEX `o_guid` (`o_guid`, `r_guid`, `skbranch`);


		ALTER TABLE `users` DROP COLUMN user_firstname;
		ALTER TABLE `users` DROP COLUMN user_middlename;
		ALTER TABLE `users` DROP COLUMN user_lastname;


--		ALTER TABLE `objectsreports` DROP COLUMN or_datechange;
		ALTER TABLE `objectsreports` ADD COLUMN skbranch INT;
		UPDATE objectsreports SET skbranch = 1;
		CREATE INDEX indx_objectsreports_skbranch ON objectsreports(skbranch);

		ALTER TABLE `reportsusersquery` 
		 DROP PRIMARY KEY,
		  ADD PRIMARY KEY (o_guid, r_guid, iduser, idlanguage );

		ALTER TABLE `forummessages` ADD COLUMN frmm_datechange_tmst int(11) NULL;
		UPDATE `forummessages` SET frmm_datechange_tmst = UNIX_TIMESTAMP(frmm_datechange);
		ALTER TABLE `forummessages` MODIFY frmm_datechange_tmst int(11) NOT NULL;
		ALTER TABLE `forummessages` DROP COLUMN frmm_datechange;
		ALTER TABLE `forummessages` ADD COLUMN skbranch INT;
		UPDATE forummessages SET skbranch = 1;
		UPDATE forummessages SET skbranch = 0 WHERE o_guid ='00000000-0000-0000-0000-000000000000';
		CREATE INDEX indx_forummessages_skbranch ON forummessages(skbranch);

		CREATE TABLE IF NOT EXISTS forummessagessaw (
				`iduser` INT NOT NULL ,
				`o_guid` CHAR(40) NOT NULL,
				`frmsw_sessionhash` VARCHAR(255) NOT NULL,
				`frmsw_datesaw` DATETIME NOT NULL,
				 PRIMARY KEY(iduser, o_guid)
				)ENGINE=InnoDB;

		ALTER TABLE `forummessagessaw`  ADD frmsw_datelastsaw DATETIME AFTER frmsw_datesaw;
		ALTER TABLE `forummessagessaw` ADD COLUMN frmsw_datesaw_tmst int(11) NULL;
		ALTER TABLE `forummessagessaw` ADD COLUMN frmsw_datelastsaw_tmst int(11) NULL;
		UPDATE `forummessagessaw` SET frmsw_datesaw_tmst = UNIX_TIMESTAMP(frmsw_datesaw), frmsw_datelastsaw_tmst = UNIX_TIMESTAMP(frmsw_datelastsaw);
		ALTER TABLE `forummessagessaw` MODIFY frmsw_datesaw_tmst int(11) NOT NULL;
		ALTER TABLE `forummessagessaw` DROP COLUMN frmsw_datesaw;
		ALTER TABLE `forummessagessaw` MODIFY frmsw_datelastsaw_tmst int(11) NOT NULL;
		ALTER TABLE `forummessagessaw` DROP COLUMN frmsw_datelastsaw;
		ALTER TABLE `forummessagessaw` ADD COLUMN skbranch INT;
		UPDATE forummessagessaw SET skbranch = 1;
		ALTER TABLE `forummessagessaw`
			CHANGE COLUMN `skbranch` `skbranch` INT(11) NOT NULL AFTER `o_guid`,
			DROP PRIMARY KEY,
			ADD PRIMARY KEY (`iduser`, `o_guid`, `skbranch`);
		
		ALTER TABLE `reportindex` ADD COLUMN ri_expirationdate_tmst int(11) NULL;
		ALTER TABLE `reportindex` ADD COLUMN ri_creationdate_tmst int(11) NULL;
		UPDATE `reportindex` SET ri_creationdate_tmst = UNIX_TIMESTAMP(ri_creationdate), ri_expirationdate_tmst = UNIX_TIMESTAMP(ri_expirationdate);
		ALTER TABLE `reportindex` DROP COLUMN ri_creationdate;
		ALTER TABLE `reportindex` DROP COLUMN ri_expirationdate;

		ALTER TABLE `resourcefromtables`
			CHANGE COLUMN `rft_columnname_text` `rft_columnname_text` VARCHAR(2500) NULL AFTER `skbranch`;
		ALTER TABLE `resourcefromtables`
			ADD COLUMN `rft_columnname_long` LONGTEXT NULL DEFAULT NULL AFTER `rft_columnname_text`;
	END IF;
END$$

CREATE PROCEDURE `mailqueue_table_create`()
BEGIN
	IF (NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'mailqueue' AND TABLE_SCHEMA = (SELECT DATABASE()))) THEN		
		-- DROP TABLE IF EXISTS `mailqueue`;
		CREATE TABLE `mailqueue` (
			`idmailqueue` INT NOT NULL AUTO_INCREMENT,
			`idmaitype` INT(11) NOT NULL DEFAULT '0',
			`priority` INT(11) NOT NULL DEFAULT '0',
			`mq_recipients` VARCHAR(1000) NOT NULL COLLATE 'utf8mb4_general_ci',
			`mq_replyto` VARCHAR(1000) NOT NULL COLLATE 'utf8mb4_general_ci',
			`mq_subject` VARCHAR(1000) NOT NULL COLLATE 'utf8mb4_general_ci',
			`mq_body` LONGTEXT NOT NULL COLLATE 'utf8mb4_general_ci',
			PRIMARY KEY (`idmailqueue`)
		)
		COLLATE='utf8mb4_general_ci'
		ENGINE=InnoDB;		
	END IF;
END$$
CREATE PROCEDURE `maitypes_table_create`()
BEGIN
	IF (NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'maitypes' AND TABLE_SCHEMA = (SELECT DATABASE()))) THEN	
		
		CREATE TABLE `maitypes` (
			`idmaitype` INT(11) NOT NULL DEFAULT '0',
			`mt_name` VARCHAR(100) NOT NULL COLLATE 'utf8mb4_general_ci',
			`mt_description` VARCHAR(1000) NOT NULL COLLATE 'utf8mb4_general_ci',
			PRIMARY KEY (`idmaitype`)
		)
		COLLATE='utf8mb4_general_ci'
		ENGINE=InnoDB;

		INSERT IGNORE INTO  `maitypes` VALUES( 200, 'pollNew', '');
		INSERT IGNORE INTO  `maitypes` VALUES( 300, 'pollNewAnalyst', '');
		INSERT IGNORE INTO  `maitypes` VALUES( 400, 'pollNewAdmin', '');
		INSERT IGNORE INTO  `maitypes` VALUES( 500, 'pollContainsErrors', '');
		INSERT IGNORE INTO  `maitypes` VALUES( 500, 'pollContainsErrorsAdmin', '');
		INSERT IGNORE INTO  `maitypes` VALUES( 600, 'pollDelete', '');
		INSERT IGNORE INTO  `maitypes` VALUES( 700, 'pollStop', '');
		INSERT IGNORE INTO  `maitypes` VALUES( 800, 'pollContinue', '');
		INSERT IGNORE INTO  `maitypes` VALUES( 900, 'pollHide', '');
				
	END IF;
END$$

CREATE PROCEDURE `objectsprohibitionrightscopy_table_create`()
BEGIN
	IF (NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'objectsprohibitionrightscopy' AND TABLE_SCHEMA = (SELECT DATABASE()))) THEN	
		CREATE TABLE `objectsprohibitionrightscopy` (
			`idobject` INT(11) NOT NULL,
			`obcr_prohibit` TINYINT(1) NOT NULL,
			PRIMARY KEY (`idobject`)
		)
		COLLATE='utf8mb4_general_ci'
		ENGINE=InnoDB
		AUTO_INCREMENT=8;
	END IF;
END$$

CREATE PROCEDURE `users_remove_column_author`()
BEGIN
	IF (EXISTS(SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'users' AND COLUMN_NAME='user_notification_owner'  AND TABLE_SCHEMA = (SELECT DATABASE()))) THEN
		ALTER TABLE `users`	DROP COLUMN `user_notification_owner`;	
		ALTER TABLE `users`	DROP COLUMN `user_notification_autor`;	
		ALTER TABLE `users`	DROP COLUMN `user_notification_newversions`;	
	END IF;
END$$

CREATE PROCEDURE `resourcefromtablespollarchive_table_create`()
BEGIN
	IF (NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'resourcefromtablespollarchive' AND TABLE_SCHEMA = (SELECT DATABASE()))) THEN		
		-- DROP TABLE IF EXISTS `branches`;
		CREATE TABLE `resourcefromtablespollarchive` (
			`idpollingobjectarchive` INT(11) NOT NULL,
			`guidrecord` VARCHAR(40) NULL,
			`idrecord` INT(11) NOT NULL,
			`idtable` INT(11) NOT NULL,
			`idlanguage` INT(11) NOT NULL,
			`skbranch` INT(11) NOT NULL,
			`rft_columnname_text` VARCHAR(1100) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
			`rft_columnname_long` LONGTEXT NULL COLLATE 'utf8mb4_general_ci',
			PRIMARY KEY (`idpollingobjectarchive`, `idrecord`, `idtable`, `idlanguage`, `skbranch`),
			INDEX ix1 (`guidrecord`)
		)
		COLLATE='utf8mb4_general_ci'
		ENGINE=InnoDB;

	END IF;
END$$

CREATE PROCEDURE `userpositions_table_create`()
BEGIN
	IF (NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'userpositions' AND TABLE_SCHEMA = (SELECT DATABASE()))) THEN		
		CREATE TABLE `userpositions` (
			`iduser` INT(11) NOT NULL,
			`o_guid` VARCHAR(40) NOT NULL COLLATE 'utf8mb4_general_ci',
			INDEX `iduser` (`iduser`)
		)
		COLLATE='utf8mb4_general_ci';
	END IF;
END$$

-- Добавленно 051119

CREATE PROCEDURE `poll_rightspollingobjects_table_create`()
BEGIN
	IF (NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'rightspollingobjects' AND TABLE_SCHEMA = (SELECT DATABASE()))) THEN		
		DROP TABLE IF EXISTS `rightspollingobjects`;
		CREATE TABLE `rightspollingobjects` (
			`iduser` INT(11) NOT NULL,
			`skpollingobject` INT(11) NOT NULL,
			`deny_allow` TINYINT(1) NOT NULL,
			PRIMARY KEY (`iduser`, `skpollingobject`)
		)
		COLLATE='utf8mb4_general_ci'
		ENGINE=InnoDB;
	END IF;
END$$

--
-- Эта процедура выглядит идентично той, что прислали разработчики 140913, но еще и учитывает размер guid
-- Обновлено 12.09.2016 - изменил тип переменной frmm_body
--
CREATE PROCEDURE `add_forummessages`( IN `in_parent_idforummessage` INT, IN `o_guid` VARCHAR(40), IN `iduser` INT, IN `frmm_body` LONGTEXT, IN `branch` INT)
BEGIN
	declare cnt BIGINT DEFAULT null;
	declare parent_iright BIGINT;
	declare parent_depth BIGINT;
	declare param_skbranch INT DEFAULT -1;
	declare calc_parent_idforummessage INT;

	SELECT CASE WHEN parent_idforummessage = 0 THEN in_parent_idforummessage ELSE parent_idforummessage END into calc_parent_idforummessage FROM `forummessages` WHERE `idforummessage` = in_parent_idforummessage;

	IF (branch IS NULL) THEN
		SELECT b.`skbranch` into param_skbranch FROM `branches` as b WHERE b.ismain = 1 LIMIT 1; 
	ELSE 
		SELECT `branch` into param_skbranch;
	END IF;

	SET @@session.time_zone = "+00:00";
	SELECT COUNT(*) into cnt from `forummessages`;
	IF (cnt = 0 ) THEN
		INSERT INTO `forummessages` (`idforummessage`, `parent_idforummessage`, `o_guid`, `iduser`, `frmm_ileft`, `frmm_iright`, `frmm_depth`, `frmm_body`, `frmm_isdelete`, `frmm_ischanged`, `frmm_datechange_tmst`, `skbranch`) 
		VALUES ('0', '0', '00000000-0000-0000-0000-000000000000', '0', '1', '2', '0', 'root', 0, 0, UNIX_TIMESTAMP(NOW()), 0); 
		UPDATE `forummessages` SET `idforummessage` = 0;
	END IF;

	IF (CHAR_LENGTH(o_guid) <= 40 AND iduser > 1) THEN
		SELECT `frmm_iright`, `frmm_depth` into parent_iright, parent_depth from `forummessages` as frmm where frmm.`idforummessage` = calc_parent_idforummessage;
		UPDATE forummessages SET `frmm_ileft`=`frmm_ileft`+2,`frmm_iright`=`frmm_iright`+2 where `frmm_ileft`>parent_iright;
		UPDATE forummessages SET `frmm_iright`=`frmm_iright`+2 where `frmm_iright`>=parent_iright AND `frmm_ileft`<parent_iright;
		INSERT INTO `forummessages` (`idforummessage`, `parent_idforummessage`, `o_guid`, `iduser`, `frmm_ileft`, `frmm_iright`, `frmm_depth`, `frmm_body`, `frmm_isdelete`,  `frmm_ischanged`, `frmm_datechange_tmst`, `skbranch`) VALUES (NULL, calc_parent_idforummessage, o_guid, iduser, parent_iright, parent_iright+1, parent_depth+1, frmm_body, 0, 0, UNIX_TIMESTAMP(NOW()), param_skbranch);
	END IF;
END
$$

--
-- Эта процедура идентична той, что прислали разработчики 140913 + добавка для скрытия от 11.08.2015
-- Обновлено 07.11.2016 для поддержки длинных имен
--
CREATE PROCEDURE `add_objects`(IN `parent_guid` CHAR(40), IN `guid` CHAR(40), IN `icon` INT, IN `datechange` DATETIME, IN `ver` VARCHAR(800), IN `datever` DATE, IN `statever` INT, IN `hasreports` TINYINT, IN `useinsearch` TINYINT, IN `hidefortree` TINYINT, `branchid` VARCHAR(50))
BEGIN
     declare parent_iright BIGINT DEFAULT null;
    declare parent_id BIGINT DEFAULT null;
    declare parent_depth BIGINT DEFAULT null;
    declare parent_skbranch INT DEFAULT null;
	SELECT `skbranch` into parent_skbranch FROM branches WHERE idbranch = branchid;
    IF(NOT EXISTS( SELECT 1 FROM `objects` WHERE `o_guid` = `guid` AND skbranch = parent_skbranch )) THEN
        SELECT `o_iright`, `o_depth`, `idobject` into parent_iright, parent_depth, parent_id from objects where o_guid=parent_guid AND (skbranch = parent_skbranch OR parent_guid = '00000000-0000-0000-0000-000000000000');
        IF ( parent_id IS NOT null ) THEN	
            UPDATE objects SET `o_ileft`=`o_ileft`+2,`o_iright`=`o_iright`+2 where `o_ileft`>parent_iright;
            UPDATE objects SET `o_iright`=`o_iright`+2 where `o_iright`>=parent_iright AND `o_ileft`<parent_iright;
            INSERT INTO `objects` (`parent_idobject`, `o_ileft`, `o_iright`, `o_depth`, `o_guid`, `o_icon`, `o_datechange`, `o_ver` , `o_datever` , `o_statever`, `o_hasreports`, `o_useinsearch`, `o_hidefortree`, `skbranch` ) VALUES (parent_id, parent_iright, parent_iright+1, parent_depth+1, guid, icon, datechange, ver , datever , statever, hasreports, useinsearch, hidefortree, parent_skbranch );
        END IF;	      
    END IF;	      
END$$  


--
-- Эта процедура идентична той, что прислали разработчики 140913
--
CREATE PROCEDURE `add_personallinks`(
	IN `iduser` INT,
	IN `o_guid` varchar(1000),
	IN `r_guid` varchar(1000),
	IN `branch` INT,
	IN `urlparams` varchar(2000)
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    declare pl_pos INT DEFAULT null;
    declare cnt INT DEFAULT null;
    SELECT COUNT(*) into cnt from `personallinks` as pl where pl.`iduser` = iduser AND pl.`o_guid` = SUBSTRING(o_guid, 1, 40) AND pl.`r_guid` = SUBSTRING(r_guid, 1, 40) AND pl.`skbranch` = branch;
    IF (cnt = 0 ) THEN
	    SELECT MAX(pl.`pl_pos`) + 1 into pl_pos from `personallinks` as pl where pl.`iduser` = iduser;
	  	
	    IF ( pl_pos is null ) THEN
		INSERT INTO `personallinks` (`idpersonallink`, `iduser`, `o_guid`, `r_guid`, `skbranch`, `pl_pos`, `addurlparams`) VALUES (NULL, iduser, SUBSTRING(o_guid, 1, 40), SUBSTRING(r_guid, 1, 40), branch, 1, urlparams);
	    ELSE	
		INSERT INTO `personallinks` (`idpersonallink`, `iduser`, `o_guid`, `r_guid`, `skbranch`, `pl_pos`, `addurlparams`) VALUES (NULL, iduser, SUBSTRING(o_guid, 1, 40), SUBSTRING(r_guid, 1, 40), branch, pl_pos, urlparams);
	    END IF;	      
    END IF;	      
END$$

--
-- Эта процедура идентична той, что прислали разработчики 140913
--
CREATE PROCEDURE `add_rating_reportdata`(
	IN `rd_session` CHAR(32),
	IN `o_guid` VARCHAR(1000),
	IN `r_guid` VARCHAR(1000),
	IN `iduser` INT,
	IN `rd_rating` INT,
	IN `branch` INT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN

    declare rating FLOAT;
    declare l_idreportdata INT;
    declare l_hasrecord INT default NULL;
	declare param_skbranch INT DEFAULT -1;

	IF (branch IS NULL) THEN
		SELECT  b.`skbranch` into param_skbranch FROM `branches` as b WHERE b.ismain = 1 LIMIT 1; 
	ELSE 
		SELECT  `branch` into param_skbranch;
	END IF;

    SELECT rd.`idreportdata` INTO l_idreportdata FROM `reportdata` as rd WHERE rd.`o_guid` = SUBSTRING(o_guid, 1, 40) AND rd.`r_guid` = SUBSTRING(r_guid, 1, 40) AND rd.`iduser` = iduser AND rd.`skbranch` = param_skbranch;
    SELECT 1 INTO l_hasrecord FROM `avgreportdata` as ard WHERE ard.`o_guid` = SUBSTRING(o_guid, 1, 40)  AND ard.`r_guid` = SUBSTRING(r_guid, 1, 40) AND ard.`skbranch` = param_skbranch;

    IF (l_idreportdata) THEN
	UPDATE `reportdata` as rd SET  rd.`rd_rating` = rd_rating WHERE rd.`idreportdata` = l_idreportdata AND rd.`skbranch` = param_skbranch;
    ELSE
	INSERT INTO `reportdata` (`idreportdata`, `rd_session`, `o_guid`, `r_guid`, `iduser`, `rd_rating`, `rd_viewcount`, `skbranch`) VALUES (null, rd_session, SUBSTRING(o_guid, 1, 40), SUBSTRING(r_guid, 1, 40), iduser, rd_rating, 1, param_skbranch);
    END IF;

    SELECT IFNULL(SUM(rd.`rd_rating`) / COUNT(0),0) INTO rating from`reportdata` as rd WHERE  rd.`o_guid` = SUBSTRING(o_guid, 1, 40) AND rd.`r_guid` = SUBSTRING(r_guid, 1, 40) AND rd.`rd_rating` > 0 AND rd.`skbranch` = param_skbranch; 

    IF (l_hasrecord) THEN
	UPDATE `avgreportdata` as ard SET  ard.`ard_rating` = rating WHERE ard.`o_guid` = SUBSTRING(o_guid, 1, 40) AND ard.`r_guid` = SUBSTRING(r_guid, 1, 40) AND ard.`skbranch` = param_skbranch;
    ELSE
	INSERT INTO `avgreportdata` (`o_guid` ,`r_guid`, `skbranch`,`ard_rating` ,`ard_viewcount` ,`ard_msgcount` ) VALUES (SUBSTRING(o_guid, 1, 40), SUBSTRING(r_guid, 1, 40), param_skbranch,  rating, 1, 0);
    END IF;
END$$

--
-- Эта процедура идентична той, что прислали разработчики 140913
--
CREATE PROCEDURE `add_viewcount_reportdata`(
	IN `session` CHAR(32),
	IN `o_guid` VARCHAR(1000),
	IN `r_guid` VARCHAR(1000),
	IN `iduser` INT,
	IN `branch` INT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN

    declare viewcount INT;
    declare ses_viewcount INT;
    declare l_hasrecord INT default NULL;
    declare l_idreportdata INT default null;
    declare ses_idreportdata INT default null;
	declare param_skbranch INT DEFAULT -1;

	IF (branch IS NULL) THEN
		SELECT  b.`skbranch` into param_skbranch FROM `branches` as b WHERE b.ismain = 1 LIMIT 1; 
	ELSE 
		SELECT  `branch` into param_skbranch;
	END IF;

    SELECT 1 INTO l_hasrecord FROM `avgreportdata` as ard WHERE ard.`o_guid` = SUBSTRING(o_guid, 1, 40)  AND ard.`r_guid` = SUBSTRING(r_guid, 1, 40) AND `skbranch` = param_skbranch;
    SELECT rd.`idreportdata` INTO l_idreportdata FROM `reportdata` as rd WHERE rd.`o_guid` = SUBSTRING(o_guid, 1, 40) AND rd.`r_guid` = SUBSTRING(r_guid, 1, 40) AND rd.`iduser` = iduser AND rd.`skbranch` = param_skbranch;

    IF (l_idreportdata) THEN
	SELECT rd.`idreportdata`, rd.`rd_viewcount` INTO ses_idreportdata, ses_viewcount FROM `reportdata` as rd WHERE rd.idreportdata = l_idreportdata AND rd.`rd_session` = session AND rd.`skbranch` = param_skbranch;
	IF ((ses_idreportdata is null) OR (ses_viewcount = 0)) THEN
	    UPDATE `reportdata` as rd SET rd.`rd_viewcount` = rd.`rd_viewcount` + 1, rd.`rd_session` = session WHERE rd.`idreportdata` = l_idreportdata AND rd.`skbranch` = param_skbranch;
	END IF;
    ELSE
	INSERT INTO `reportdata` (`idreportdata`, `rd_session`, `o_guid`, `r_guid`, `iduser`, `rd_rating`, `rd_viewcount`, `skbranch`) VALUES (null, session, SUBSTRING(o_guid, 1, 40), SUBSTRING(r_guid, 1, 40), iduser, 0, 1, param_skbranch);
    END IF;

    SELECT IFNULL(SUM(rd.`rd_viewcount`),0) INTO viewcount from`reportdata` as rd WHERE  rd.`o_guid` = SUBSTRING(o_guid, 1, 40) AND rd.`r_guid` = SUBSTRING(r_guid, 1, 40)  AND rd.`skbranch` = param_skbranch;

    IF (l_hasrecord) THEN
		UPDATE `avgreportdata` as ard SET ard.`ard_viewcount` = viewcount WHERE ard.`o_guid` = SUBSTRING(o_guid, 1, 40) AND ard.`r_guid` = SUBSTRING(r_guid, 1, 40) AND ard.`skbranch` = param_skbranch;
    ELSE
		INSERT INTO `avgreportdata` (`o_guid` ,`r_guid`, `skbranch` ,`ard_rating` ,`ard_viewcount` ,`ard_msgcount` ) VALUES (SUBSTRING(o_guid, 1, 40), SUBSTRING(r_guid, 1, 40), param_skbranch, 0, viewcount, 0);
    END IF;
	
END$$

--
-- Эта процедура идентична той, что прислали разработчики 140913
-- 12.09.2016 добавлено поле frmm_ischanged со значением 0
--
CREATE PROCEDURE `clear_forummessages`()
BEGIN
	SET @@session.time_zone = "+00:00";
	DELETE FROM `forummessages`;
	INSERT INTO `forummessages` (`idforummessage`, `parent_idforummessage`, `o_guid`, `iduser`, `frmm_ileft`, `frmm_iright`, `frmm_depth`, `frmm_body`, `frmm_isdelete`, `frmm_ischanged`, `frmm_datechange_tmst`, `skbranch`) 
	VALUES ('0', '0', '00000000-0000-0000-0000-000000000000', '0', '1', '2', '1', NULL, 0, 0, UNIX_TIMESTAMP(NOW()),0);
	UPDATE `forummessages` SET `idforummessage` = 0;
	ALTER TABLE `forummessages` AUTO_INCREMENT = 1;

END$$

--
-- Эта процедура идентична той, что прислали разработчики 140913
--
CREATE PROCEDURE `clear_objects`(IN i_skbranch INT)
BEGIN
    DECLARE lft BIGINT;
    DECLARE rgt BIGINT;
    DECLARE depth BIGINT;

    SELECT MIN(o_ileft) into lft FROM objects WHERE skbranch = i_skbranch;
	SELECT o_iright into rgt  FROM objects WHERE o_ileft = lft;
	SELECT (o_iright - o_ileft + 1) into depth FROM objects WHERE o_ileft = lft;
    DELETE FROM objects WHERE o_ileft BETWEEN lft AND rgt;
    UPDATE objects SET o_iright = o_iright - depth WHERE o_iright > rgt;
    UPDATE objects SET o_ileft = o_ileft - depth WHERE o_ileft > lft;
	
	DELETE FROM objects WHERE o_guid = '00000000-0000-0000-0000-000000000000';
	INSERT IGNORE INTO `objects` (`parent_idobject`, `o_ileft`, `o_iright`, `o_depth`, `o_guid`, `o_icon`, `o_datechange`, `o_hasreports`, `o_useinsearch`, `o_hidefortree`, `skbranch` ) VALUES (0, 1, 2, 0, '00000000-0000-0000-0000-000000000000', 0, NOW(), 0, 0, 0, NULL);
END$$

--
-- Эта процедура идентична той, что прислали разработчики 140913, но я увеличил масив users_id с 500 до 2500
--
CREATE PROCEDURE `create_objectsrightstmp`(IN `users_id` VARCHAR(2500) )
BEGIN
    DROP TEMPORARY TABLE IF EXISTS `or_temp` ;

    CREATE TEMPORARY TABLE or_temp (    
    `idobject` BIGINT NOT NULL,
    `sumr` INT NOT NULL,
     PRIMARY KEY (`idobject`)
    )ENGINE=MEMORY;

    SET @query = CONCAT('INSERT INTO `or_temp` SELECT `idobject`, SUM(IF( ( `deny_allow` = 0 ) , -3,  `deny_allow` )) FROM `rightsobjects` WHERE `iduser` IN (',users_id,') GROUP BY `idobject`;');
    PREPARE zxc FROM @query;
    EXECUTE zxc;

    DELETE FROM `or_temp` WHERE sumr <> 3;
END$$  

--
-- Эта процедура идентична той, что прислали разработчики 140913, но я увеличил масив users_id с 500 до 2500
--
CREATE PROCEDURE `create_reportsrightstmp`(	IN `users_id` VARCHAR(2500), IN `o_guid` VARCHAR(40) )
BEGIN
    declare idobject BIGINT DEFAULT -1;
    DROP TEMPORARY TABLE IF EXISTS `rr_temp` ;

    CREATE TEMPORARY TABLE rr_temp (    
    `idreport` INT NOT NULL,
    `sumr` INT NOT NULL,
     PRIMARY KEY (`idreport`)
    )ENGINE=MEMORY;

    SELECT obj.`idobject` into idobject from `objects` as obj where obj.`o_guid` = o_guid;

    SET @query = CONCAT('INSERT INTO `or_temp` SELECT `idobject`, SUM(IF( ( `deny_allow` = 0 ) , -3,  `deny_allow` )) FROM `rightsobjects` WHERE `iduser` IN (',users_id,') AND `idreport` IN (SELECT `idreport` FROM `objectsreports` WHERE `idobject` = ',idobject,') GROUP BY `idobject`;');
    PREPARE zxc FROM @query;
    EXECUTE zxc;

    DELETE FROM `or_temp` WHERE sumr <> 3;
END$$ 

--
-- Эта процедура идентична той, что прислали разработчики 140913
--
CREATE PROCEDURE `del_personallinks`(iduser INT, id INT)
BEGIN
  declare _pos INT DEFAULT null;
  SELECT `pl_pos` INTO _pos FROM `personallinks` WHERE `idpersonallink` = id and `personallinks`.`iduser` = iduser;
  DELETE FROM `personallinks` WHERE `idpersonallink` = id and `personallinks`.`iduser` = iduser;  	
  UPDATE `personallinks` SET `pl_pos` = `pl_pos` - 1 WHERE `personallinks`.`iduser` = iduser AND `pl_pos` > _pos;    	
END$$

--
-- Эта процедура идентична той, что прислали разработчики 140913
--
CREATE PROCEDURE `load_objectsAdmin`(IN `parent_idobject` BIGINT  )
BEGIN
    declare root_parent_idobject BIGINT;
    DROP TEMPORARY TABLE IF EXISTS `o_temp` ;
    CREATE TEMPORARY TABLE o_temp (	
	`idobject` BIGINT NOT NULL,
	`parent_idobject` BIGINT NOT NULL,
	 PRIMARY KEY (`idobject`)
	)ENGINE=MEMORY;

    CREATE TEMPORARY TABLE or_temp (    
    `idobject` BIGINT NOT NULL,
     PRIMARY KEY (`idobject`)
    )ENGINE=MEMORY;

    DROP TEMPORARY TABLE IF EXISTS `o_temp_clone` ;
    CREATE TEMPORARY TABLE o_temp_clone (	
	`idobject` BIGINT NOT NULL,
	`parent_idobject` BIGINT NOT NULL,
	 PRIMARY KEY (`idobject`)
	)ENGINE=MEMORY;

    DROP TEMPORARY TABLE IF EXISTS `or_temp_clone` ;
    CREATE TEMPORARY TABLE or_temp_clone (	
	`idobject` BIGINT NOT NULL,
	 PRIMARY KEY (`idobject`)
	)ENGINE=MEMORY;

   IF ( parent_idobject = 0 ) THEN

   	SELECT obj.`idobject` into root_parent_idobject from `objects` as obj where obj.`parent_idobject` = 0 AND ((o_iright - o_ileft) > 1);
	INSERT INTO `o_temp` SELECT obj.`idobject`, obj.`parent_idobject` FROM `objects` as obj WHERE obj.`idobject` <> root_parent_idobject AND ((o_iright - o_ileft) > 1);
	INSERT INTO `o_temp_clone` SELECT obj.`idobject`, obj.`parent_idobject` FROM `objects` as obj WHERE obj.`idobject` <> root_parent_idobject AND ((o_iright - o_ileft) > 1);
	DELETE FROM `o_temp` WHERE `o_temp`.`parent_idobject` IN ( SELECT `o_temp_clone`.`idobject` FROM `o_temp_clone` );
   ELSE
	INSERT INTO `o_temp` SELECT obj.`idobject`, obj.`parent_idobject` FROM `objects` as obj WHERE obj.`parent_idobject` = parent_idobject AND ((o_iright - o_ileft) > 1);
   END IF;	      

END$$  

--
-- Эта процедура - merge той, что прислали разработчики 140913 b 160214
--
CREATE PROCEDURE `load_objects_admin`(	IN `parent_id` BIGINT, IN `depth` BIGINT, IN `r_guid` VARCHAR(1000), IN `branch` INT )
BEGIN
    declare ileft BIGINT DEFAULT -1;
    declare iright BIGINT DEFAULT -1;
	declare param_skbranch INT DEFAULT -1;

	IF (branch IS NULL) THEN
		SELECT  b.`skbranch` into param_skbranch FROM `branches` as b WHERE b.ismain = 1 LIMIT 1; 
	ELSE 
		SELECT  `branch` into param_skbranch;
	END IF;

    DROP TEMPORARY TABLE IF EXISTS `admin_temp` ;
    CREATE TEMPORARY TABLE `admin_temp` (
    `idobject` BIGINT NOT NULL,
    `o_guid` VARCHAR(36) NOT NULL,
    `r_guid` VARCHAR(36) NOT NULL,
    `at_rating` INT,
    `at_viewcount` INT,
    `at_msgcnt` INT,
    `at_msglastdate` DATETIME,
     PRIMARY KEY(idobject)
    )ENGINE=MEMORY;

    CREATE INDEX o_guid_admin_temp ON admin_temp (o_guid);
    SELECT obj.`o_ileft`, obj.`o_iright` into ileft, iright from `objects` as obj where obj.`idobject` = parent_id and obj.`skbranch` = param_skbranch;

    INSERT INTO admin_temp SELECT obj.idobject, obj.o_guid, SUBSTRING(r_guid, 1, 40), NULL, NULL, NULL, NULL FROM `objects` as obj where obj.`skbranch` = param_skbranch and obj.`o_ileft` BETWEEN ileft+1 AND iright AND o_depth < depth;

    UPDATE `admin_temp` as at, `avgreportdata` as ard
	SET `at_rating` =  `ard_rating`,
	    `at_viewcount` = `ard_viewcount`
	WHERE ard.`skbranch` = param_skbranch AND CONVERT(at.`o_guid` USING utf32) = CONVERT(ard.`o_guid` USING utf32) AND CONVERT(at.`r_guid` USING utf32) = CONVERT(ard.`r_guid` USING utf32) ;

   UPDATE `admin_temp`
	SET `at_viewcount` = 0
	WHERE `at_viewcount` IS NULL;

    UPDATE `admin_temp` as at  
	SET `at_msgcnt` =  (SELECT IFNULL(COUNT(0),0) FROM `forummessages` as frmm WHERE frmm.`skbranch` = param_skbranch AND CONVERT(frmm.`o_guid` USING utf32)  = CONVERT(at.`o_guid` USING utf32)  and frmm.`frmm_isdelete`=0);

    UPDATE `admin_temp` as at  
	SET `at_msglastdate` =  (SELECT MAX(from_unixtime(frmm_datechange_tmst)) FROM `forummessages` as frmm WHERE frmm.`skbranch` = param_skbranch AND  CONVERT(frmm.`o_guid` USING utf32)  = CONVERT(at.`o_guid` USING utf32)  and frmm.`frmm_isdelete`=0);

END$$  

--
-- Эта процедура идентична той, что прислали разработчики 140913 и ...
-- ... сделана переделка на основе еще не заведному багу [124] про дерево
-- Добавлено исправление [216], вычеркивающее скрытые справочники из поиска
-- Добавлено исправление [317], учитывающее ветки
--
CREATE PROCEDURE `load_objecttree_search`(	IN `branch` INT )
BEGIN
	declare param_skbranch INT DEFAULT -1;

	IF (branch IS NOT NULL) THEN
		SELECT  `branch` into param_skbranch;
	END IF;

    DROP TEMPORARY TABLE IF EXISTS `o_temp` ;
    CREATE TEMPORARY TABLE o_temp (	
	`idobject` BIGINT NOT NULL,
	`parent_idobject` BIGINT NOT NULL,
	 PRIMARY KEY (`idobject`)
	)ENGINE=MEMORY;

    INSERT INTO `o_temp` SELECT obj.`idobject`, obj.`parent_idobject` FROM `objects` as obj WHERE obj.`o_useinsearch` = 1 AND obj.`o_hidefortree` = 0  AND (obj.`skbranch` = param_skbranch OR param_skbranch = -1);
END$$ 
      
--
-- Эта процедура идентична той, что прислали разработчики 140913, но я увеличил масив users_id с 500 до 2500
--

CREATE PROCEDURE `load_reports`(IN `o_guid` VARCHAR(1000),IN `userights` INT,IN `users_id` VARCHAR(2500),IN `idlanguage` INT,IN `branch` INT )
BEGIN
	declare idobject BIGINT DEFAULT -1;
	declare param_skbranch INT DEFAULT -1;
	declare param_lang INT DEFAULT -1;
	IF (branch IS NULL) THEN
		SELECT  b.`skbranch` into param_skbranch FROM `branches` as b WHERE b.ismain = 1 LIMIT 1; 
	ELSE 
		SELECT  `branch` into param_skbranch;
	END IF;

   DROP TEMPORARY TABLE IF EXISTS `r_temp` ;
	CREATE TEMPORARY TABLE `r_temp` (
		`idreport` INT NOT NULL,
   	`r_guid` VARCHAR(40),
     PRIMARY KEY (`idreport`)
	)ENGINE=MEMORY;

   DROP TEMPORARY TABLE IF EXISTS `r_temp_deflng` ;
	CREATE TEMPORARY TABLE `r_temp_deflng` (
		`idreport` INT NOT NULL,
   	`r_guid` VARCHAR(40),
     PRIMARY KEY (`idreport`)
	)ENGINE=MEMORY;

   DROP TEMPORARY TABLE IF EXISTS `rr_temp` ;
   CREATE TEMPORARY TABLE rr_temp (    
   	`idreport` INT NOT NULL,
     PRIMARY KEY (`idreport`)
   )ENGINE=MEMORY;

   SELECT obj.`idobject` into idobject from `objects` as obj where obj.`o_guid` = SUBSTRING(o_guid, 1, 40)  AND  obj.`skbranch` = param_skbranch;
   SELECT languages.idlanguage into param_lang FROM languages WHERE main = 1;

   IF ( idobject > 0 ) THEN
	 	INSERT INTO r_temp 
		 	SELECT rep.`idreport`, rep.`r_guid` FROM `reports` as rep INNER JOIN `objectsreports` as orl ON orl.`idreport` = rep.`idreport` AND  orl.`skbranch` = param_skbranch  
			 WHERE orl.`idobject` = idobject AND  rep.`skbranch` = param_skbranch AND (ifnull(orl.`idlanguage`,0) = ifnull(idlanguage,0) OR ifnull(orl.`idlanguage`,0) = 0); 
	 	INSERT INTO r_temp_deflng 
		 	SELECT rep.`idreport`, rep.`r_guid` FROM `reports` as rep INNER JOIN `objectsreports` as orl ON orl.`idreport` = rep.`idreport` AND  orl.`skbranch` = param_skbranch  
			 WHERE orl.`idobject` = idobject AND  rep.`skbranch` = param_skbranch AND orl.`idlanguage` = param_lang
			   AND rep.`r_guid` NOT IN (SELECT r_guid FROM r_temp ); 
   	INSERT INTO `r_temp` SELECT `idreport`, `r_guid` FROM `r_temp_deflng`; 

		IF ( userights <> 0 ) THEN
	   	INSERT INTO `rr_temp` SELECT `idreport` FROM `r_temp`; 
			SET @query = CONCAT('DELETE FROM rr_temp WHERE rr_temp.`idreport` IN ( SELECT rr.`idreport` FROM `rightsreports` as rr INNER JOIN  `r_temp` as rt ON rt.`idreport` = rr.`idreport` WHERE  rr.`skbranch` = ',param_skbranch,' AND rr.`iduser` IN (',users_id,') AND rr.`deny_allow` = 0 );'  );
			PREPARE zxc FROM @query;
			EXECUTE zxc;
	
			SET @query = CONCAT('DELETE FROM rr_temp WHERE rr_temp.`idreport` NOT IN ( SELECT rr.`idreport` FROM `rightsreports` as rr INNER JOIN  `r_temp` as rt ON rt.`idreport` = rr.`idreport` WHERE  rr.`skbranch` = ',param_skbranch,' AND rr.`iduser` IN (',users_id,') AND rr.`deny_allow` = 1 );'  );
			PREPARE zxc FROM @query;
			EXECUTE zxc;
	
			SET @query = CONCAT('DELETE FROM rr_temp WHERE rr_temp.`idreport` NOT IN ( SELECT rr.`idreport` FROM `rightsreports` as rr INNER JOIN  `r_temp` as rt ON rt.`idreport` = rr.`idreport` WHERE  rr.`skbranch` = ',param_skbranch,' AND rr.`iduser` IN (',users_id,') AND rr.`deny_allow` = 2 );'  );
			PREPARE zxc FROM @query;
			EXECUTE zxc;
	
	      SET @query = CONCAT('INSERT IGNORE INTO `rr_temp`	SELECT DISTINCT orts.idreport FROM `objectsreports` as orts LEFT JOIN `objects` as o ON orts.`idobject` = o.`idobject`	LEFT JOIN `users` as u ON u.`user_guid` = o.`o_guid`  WHERE u.iduser in (',users_id,') and o.o_guid = "',o_guid,'" AND o.`skbranch` = ',param_skbranch,'  AND orts.idreport NOT IN (SELECT rr.idreport FROM  `rightsreports` as rr WHERE  rr.`skbranch` = ',param_skbranch,'  AND rr.deny_allow in (0,1,2) AND iduser in (',users_id,'));'  );
			PREPARE zxc FROM @query;
			EXECUTE zxc;
			
			DELETE FROM r_temp  WHERE r_temp.`idreport` NOT IN ( SELECT rr_temp.`idreport` FROM rr_temp );
		END IF;	  
   END IF;	      
END$$  

--
-- Эта процедура идентична той, что прислали разработчики 140913
--
CREATE  PROCEDURE `load_reportsAdmin`(
	IN `o_guid` VARCHAR(1000),
	IN `idlanguage` INT,
	IN `branch` INT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    declare ileft BIGINT DEFAULT -1; 	
    declare iright BIGINT DEFAULT -1;
	declare param_skbranch INT DEFAULT -1;

	IF (branch IS NULL) THEN
		SELECT  b.`skbranch` into param_skbranch FROM `branches` as b WHERE b.ismain = 1 LIMIT 1; 
	ELSE 
		SELECT  `branch` into param_skbranch;
	END IF;

    DROP TEMPORARY TABLE IF EXISTS `r_temp` ;
	CREATE TEMPORARY TABLE `r_temp` (
	`idreport` INT NOT NULL
	)ENGINE=MEMORY;


    SELECT obj.`o_ileft`, obj.`o_iright` into  ileft, iright from `objects` as obj where obj.`skbranch` = param_skbranch AND CONVERT(obj.`o_guid` USING utf32) = SUBSTRING(CONVERT( o_guid  USING utf32), 1, 40)  ;
    INSERT INTO r_temp SELECT DISTINCT orl.`idreport` FROM `objects` as obj INNER JOIN `objectsreports` as orl ON obj.`idobject` = orl.`idobject` WHERE obj.`skbranch` = param_skbranch AND obj.`o_ileft` BETWEEN ileft AND iright AND ifnull(orl.`idlanguage`,0) = ifnull(idlanguage,0); 
END$$

--
-- Эта процедура идентична той, что прислали разработчики 140913
--
CREATE PROCEDURE `move_personallinks`(iduser INT, pos INT, dest INT)
BEGIN
  declare max_pos INT DEFAULT null;
  IF ( (dest = 1) OR (dest = -1) ) THEN	
  	SELECT MAX(pl.`pl_pos`) into max_pos from `personallinks` as pl where pl.`iduser` = iduser;
	  IF ( ((pos + dest) > 0) AND ( (pos + dest) < (max_pos + 1) ) ) THEN	
	    UPDATE `personallinks` SET `pl_pos` = 0 WHERE `iduser` = iduser AND `pl_pos` = pos + dest;
	    UPDATE `personallinks` SET `pl_pos` = `pl_pos` + dest WHERE `iduser` = iduser AND `pl_pos` = pos;
	    UPDATE `personallinks` SET `pl_pos` = pos WHERE `iduser` = iduser AND `pl_pos` = 0;
	  END IF;	      
  END IF;	      

END$$

--
-- Эта процедура идентична той, что прислали разработчики 140913, но я увеличил масив users_id с 500 до 2500
--
CREATE PROCEDURE `load_objects`(IN `parent_idobject` BIGINT, IN `userights` INT, IN `users_id` VARCHAR(2500) ,IN `branch` INT )
BEGIN
	declare root_parent_idobject BIGINT;
	declare param_skbranch INT DEFAULT -1;
	IF (branch IS NULL) THEN
		SELECT  b.`skbranch` into param_skbranch FROM `branches` as b WHERE b.ismain = 1 LIMIT 1; 
	ELSE 
		SELECT  `branch` into param_skbranch;
	END IF;
	DROP TEMPORARY TABLE IF EXISTS `o_temp` ;
		CREATE TEMPORARY TABLE o_temp (	
		`idobject` BIGINT NOT NULL,
		`parent_idobject` BIGINT NOT NULL,
		PRIMARY KEY (`idobject`)
		)ENGINE=MEMORY;
	IF ( userights = 0 ) THEN
		IF ( parent_idobject = 0 ) THEN
			SELECT obj.`idobject` into root_parent_idobject from `objects` as obj where obj.`parent_idobject` = 0;
		ELSE   
			SET root_parent_idobject = parent_idobject;
		END IF;   
			INSERT IGNORE INTO `o_temp` SELECT obj.`idobject`, obj.`parent_idobject` FROM `objects` as obj WHERE obj.`parent_idobject` = root_parent_idobject AND `o_hidefortree` = 0 AND obj.`skbranch` = param_skbranch;
	ELSE
			CALL createtree(users_id, param_skbranch);
			INSERT IGNORE INTO `o_temp` SELECT obj.`idobject`, obj.`parent_idobject` FROM `chkrt_o_temp` as obj WHERE obj.`parent_idobject` = parent_idobject ;
	END IF;
END$$  

--
-- Эта процедура идентична той, что прислали разработчики 140913, но я увеличил масив users_id с 500 до 2500
-- 26.04.2016 обновил по присланной версии от SVN 198.
--
CREATE PROCEDURE `load_objecttree`(	IN `idobject` BIGINT, IN `userights` INT, IN `users_id` VARCHAR(2500), IN `branch` INT )
BEGIN
    declare parent_idobject BIGINT DEFAULT idobject;
	declare param_skbranch INT DEFAULT -1;
	IF (branch IS NULL) THEN
		SELECT  b.`skbranch` into param_skbranch FROM `branches` as b WHERE b.ismain = 1 LIMIT 1; 
	ELSE 
		SELECT  `branch` into param_skbranch;
	END IF;

    DROP TEMPORARY TABLE IF EXISTS `o_temp_tree` ;
    CREATE TEMPORARY TABLE o_temp_tree (	
	`idpobject` BIGINT NOT NULL,
	`parent_idobject` BIGINT NOT NULL,
	 PRIMARY KEY (`idpobject`)
	)ENGINE=MEMORY;
	
  IF ( userights = 0 ) THEN
    WHILE ( parent_idobject <> 0 AND ISNULL(parent_idobject) = 0) DO	
        SELECT IF(ISNULL(obj.`parent_idobject`) = 1, 0, obj.`parent_idobject`) into parent_idobject from `objects` as obj where obj.`idobject` = parent_idobject AND obj.`skbranch` = param_skbranch;
        INSERT IGNORE INTO `o_temp_tree` SELECT obj.`idobject`, obj.`parent_idobject` FROM `objects` as obj WHERE obj.`parent_idobject` = parent_idobject AND obj.`parent_idobject` <> 0 AND obj.`skbranch` = param_skbranch AND `o_hidefortree` = 0;
    END WHILE;
  ELSE
    CALL createtree(users_id, param_skbranch);
    WHILE ( parent_idobject <> 0 AND ISNULL(parent_idobject) = 0 ) DO	
  		IF EXISTS (SELECT 1 from `chkrt_o_temp` as obj where obj.`idobject` = parent_idobject) THEN
        	SELECT IF(ISNULL(obj.`parent_idobject`) = 1, 0, obj.`parent_idobject`) into parent_idobject from `chkrt_o_temp` as obj where obj.`idobject` = parent_idobject AND obj.`skbranch` = param_skbranch;
        	INSERT IGNORE INTO `o_temp_tree` SELECT obj.`idobject`, obj.`parent_idobject` FROM `chkrt_o_temp` as obj WHERE obj.`parent_idobject` = parent_idobject AND obj.`parent_idobject` <> 0 AND obj.`skbranch` = param_skbranch;
    	ELSE
    		SELECT 0 into parent_idobject;
    	END IF;	    
    END WHILE;
  END IF;

  CALL load_objects(0, userights, users_id, param_skbranch);
END$$  

--
-- Эта процедура идентична той, что прислали разработчики 140913, но я увеличил масив users_id с 500 до 2500
-- 26.04.2016 обновил по присланной версии от SVN 198.
--
CREATE PROCEDURE `load_objectpath`(IN `idobject` BIGINT, IN `userights` INT, IN `users_id` VARCHAR(2500), IN `branch` INT)
BEGIN
    declare parent_idobject BIGINT DEFAULT idobject;
	declare param_skbranch INT DEFAULT -1;

	IF (branch IS NULL) THEN
		SELECT  b.`skbranch` into param_skbranch FROM `branches` as b WHERE b.ismain = 1 LIMIT 1; 
	ELSE 
		SELECT  `branch` into param_skbranch;
	END IF;

    DROP TEMPORARY TABLE IF EXISTS `o_temp_tree` ;
    CREATE TEMPORARY TABLE o_temp_tree (	
        `idpobject` BIGINT NOT NULL,
        `parent_idobject` BIGINT NOT NULL,
        `o_ileft` BIGINT NOT NULL,
        PRIMARY KEY (`idpobject`)
    )ENGINE=MEMORY;
	
  IF ( userights = 0 ) THEN
    WHILE ( parent_idobject <> 0 AND ISNULL(parent_idobject) = 0  ) DO	
        INSERT IGNORE INTO `o_temp_tree` SELECT obj.`idobject`, IF(ISNULL(obj.`parent_idobject`) = 1, 0, obj.`parent_idobject`), obj.`o_ileft` FROM `objects` as obj WHERE obj.`idobject` = parent_idobject AND obj.`parent_idobject` <> 0 AND obj.`skbranch` = param_skbranch;
        SELECT IF(ISNULL(obj.`parent_idobject`) = 1, 0, obj.`parent_idobject`) into parent_idobject from `objects` as obj where obj.`idobject` = parent_idobject AND obj.`skbranch` = param_skbranch;
    END WHILE;
  ELSE
    CALL createtree(users_id, param_skbranch);
    WHILE ( parent_idobject <> 0 AND ISNULL(parent_idobject) = 0 ) DO	
   	IF EXISTS (SELECT 1 FROM `chkrt_o_temp` as obj WHERE obj.`idobject` = parent_idobject AND obj.`parent_idobject` <> 0 AND obj.`skbranch` = param_skbranch) THEN
			INSERT IGNORE INTO `o_temp_tree` SELECT obj.`idobject`, IF(ISNULL(obj.`parent_idobject`) = 1, 0, obj.`parent_idobject`), obj.`o_ileft` FROM `chkrt_o_temp` as obj WHERE obj.`idobject` = parent_idobject AND obj.`parent_idobject` <> 0;
      	SELECT IF(ISNULL(obj.`parent_idobject`) = 1, 0, obj.`parent_idobject`) into parent_idobject from `chkrt_o_temp` as obj where obj.`idobject` = parent_idobject;
    	ELSE
    		SELECT 0 into parent_idobject;
    	END IF;	
	 END WHILE;
  END IF;
END$$  

DELIMITER ;

--
-- Сверху добавил расширение до 2500, вместо 250 у users_id.
-- Копию наверху убрал, т.к. все равно пересоздаем.
--
--
-- createtree переписанная для быстрой работы с правами
-- также от 11.08.2015 добавлено учитывание скрытия элементов из дерева
--
DROP PROCEDURE IF EXISTS `createtree` ;
DELIMITER $$
CREATE PROCEDURE `createtree`(IN `users_id` VARCHAR(2500), IN `branch` INT)
BEGIN

	DECLARE l_idobject BIGINT;
	DECLARE l_parent_idobject BIGINT;
	DECLARE l_o_ileft BIGINT;
	DECLARE l_o_iright BIGINT;
	DECLARE done INT default 0;
	declare param_skbranch INT DEFAULT -1;

	DECLARE cursor_roots Cursor for 
		SELECT `idobject`, `o_ileft`, `o_iright`  FROM chkrt_o_temp WHERE `o_useinsearch` = 1;

	DECLARE cursor_tree Cursor for 
		SELECT `idobject`, `o_ileft`, `o_iright`  FROM chkrt_o_temp_cut WHERE `parent_idobject` = 0 ORDER BY o_ileft DESC;

	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

	IF (branch IS NULL) THEN
		SELECT  b.`skbranch` into param_skbranch FROM `branches` as b WHERE b.ismain = 1 LIMIT 1; 
	ELSE 
		SELECT  `branch` into param_skbranch;
	END IF;

	DROP TEMPORARY TABLE IF EXISTS `chkrt_o_temp` ;
	CREATE TEMPORARY TABLE chkrt_o_temp (	
	`idobject` BIGINT NOT NULL,
	`parent_idobject` BIGINT NOT NULL,
	`ad0` INT NOT NULL,
	`ad1` INT NOT NULL,
	`ad2` INT NOT NULL,
	`root` INT NOT NULL,
	`o_ileft` INT NOT NULL,
	`o_iright` INT NOT NULL,
	`o_useinsearch` INT NOT NULL,
	`skbranch` INT NOT NULL,
	 PRIMARY KEY (`idobject`)
	)ENGINE=MEMORY;

	DROP TEMPORARY TABLE IF EXISTS `chkrt_o_temp_cut` ;
	CREATE TEMPORARY TABLE chkrt_o_temp_cut (	
	`idobject` BIGINT NOT NULL,
	`parent_idobject` BIGINT NOT NULL,
	`o_ileft` BIGINT NOT NULL,
	`o_iright` BIGINT NOT NULL,
	 PRIMARY KEY (`idobject`)
	)ENGINE=MEMORY;

	INSERT IGNORE INTO `chkrt_o_temp` 
		SELECT `idobject`, `parent_idobject`, 0, 0, 0, 1, `o_ileft`, `o_iright`, `o_useinsearch`, `skbranch`  FROM `objects` WHERE `parent_idobject` <> 0 AND `o_hidefortree` = 0 AND `skbranch` = param_skbranch AND `idobject`;

	CREATE INDEX ix_pid ON chkrt_o_temp (parent_idobject);	

	DROP TEMPORARY TABLE IF EXISTS `t_rightsobjects` ;
	CREATE TEMPORARY TABLE `t_rightsobjects` (
		`iduser` INT(11) NOT NULL,
		`idobject` BIGINT NOT NULL,
		`deny_allow` TINYINT(1) NOT NULL,
		`skbranch` INT NOT NULL
	)ENGINE=MEMORY;

	SET @query = CONCAT('INSERT IGNORE INTO `t_rightsobjects` SELECT `iduser`, `idobject`, `deny_allow`, `skbranch` FROM `rightsobjects` WHERE `iduser` IN (',users_id,') AND `skbranch` = ',param_skbranch,';' );
		PREPARE zxc FROM @query;
		EXECUTE zxc;


	SET @query = CONCAT('UPDATE chkrt_o_temp INNER JOIN `t_rightsobjects` ON (chkrt_o_temp.idobject = t_rightsobjects.idobject) SET  ad0 = 1 WHERE t_rightsobjects.`deny_allow` = 0 AND t_rightsobjects.`skbranch` = ',param_skbranch,';'  );
		PREPARE zxc FROM @query;
		EXECUTE zxc;

    SET @query = CONCAT('UPDATE chkrt_o_temp INNER JOIN `t_rightsobjects` ON (chkrt_o_temp.idobject = t_rightsobjects.idobject) SET  ad1 = 1 WHERE  t_rightsobjects.`deny_allow` = 1 AND t_rightsobjects.`skbranch` = ',param_skbranch,';'  ); 
		PREPARE zxc FROM @query;
		EXECUTE zxc;

    SET @query = CONCAT('UPDATE chkrt_o_temp INNER JOIN `t_rightsobjects` ON (chkrt_o_temp.idobject = t_rightsobjects.idobject) SET  ad2 = 1 WHERE  t_rightsobjects.`deny_allow` = 2 AND t_rightsobjects.`skbranch` = ',param_skbranch,';'  );
		PREPARE zxc FROM @query;
		EXECUTE zxc;

 	DELETE FROM chkrt_o_temp WHERE (ad1 < 1 OR ad2 < 1) AND o_useinsearch = 0;

   DELETE FROM chkrt_o_temp WHERE ad0 = 1 AND o_useinsearch = 0;

   SET @query = CONCAT('INSERT IGNORE INTO chkrt_o_temp SELECT o.`idobject`, o.`parent_idobject`, 1, 1, 0, 1, o.`o_ileft`, o.`o_iright`, o.`o_useinsearch`, `skbranch`  FROM `objects` as o INNER JOIN  `users` as u ON u.`user_guid` = o.`o_guid` WHERE `parent_idobject` <> 0 AND o.`skbranch` = ',param_skbranch,' AND  o.`idobject` NOT IN (SELECT idobject FROM `rightsobjects` WHERE `deny_allow` IN (0,1,2) AND `iduser` IN (',users_id,')) AND  `o_hidefortree` = 0 AND u.`iduser` IN (',users_id,') ;'  );
		PREPARE zxc FROM @query;
		EXECUTE zxc;
		
	OPEN cursor_roots;
	WHILE done = 0 DO 
	FETCH cursor_roots INTO l_idobject,l_o_ileft,l_o_iright;
	SELECT COUNT(*) into @c FROM `chkrt_o_temp` WHERE `o_ileft` BETWEEN l_o_ileft AND l_o_iright AND `o_useinsearch` = 0;
	IF (@c = 0) THEN 
		DELETE FROM chkrt_o_temp WHERE idobject = l_idobject;
	END IF;
	END WHILE;
	Close cursor_roots; 


		DROP TEMPORARY TABLE IF EXISTS `chkrt_o_temp_id` ;
		CREATE TEMPORARY TABLE chkrt_o_temp_id (	
		`idobject` BIGINT NOT NULL,
		PRIMARY KEY (`idobject`)
		)ENGINE=MEMORY;


		INSERT IGNORE INTO `chkrt_o_temp_id` 
			SELECT `idobject` FROM chkrt_o_temp;


	UPDATE chkrt_o_temp RIGHT OUTER JOIN `chkrt_o_temp_id` ON (chkrt_o_temp.parent_idobject = chkrt_o_temp_id.idobject) 
	SET root = 0;
	UPDATE chkrt_o_temp SET parent_idobject = 0 WHERE root = 1;
	INSERT INTO chkrt_o_temp_cut
	SELECT `idobject`, `parent_idobject`,`o_ileft`,`o_iright` FROM chkrt_o_temp;
	
	SET done=0;
	OPEN cursor_tree;
	WHILE done = 0 DO 
		FETCH cursor_tree INTO l_idobject,l_o_ileft,l_o_iright;
		IF( EXISTS( SELECT idobject FROM `chkrt_o_temp` WHERE `idobject` <> l_idobject AND l_o_ileft BETWEEN `o_ileft` AND `o_iright` ORDER BY `o_ileft` DESC LIMIT 1 )) THEN
			SELECT idobject INTO l_parent_idobject FROM `chkrt_o_temp` WHERE `idobject` <> l_idobject AND l_o_ileft BETWEEN `o_ileft` AND `o_iright` ORDER BY `o_ileft` DESC LIMIT 1;
			UPDATE `chkrt_o_temp` SET `parent_idobject` = IFNULL(l_parent_idobject,0) WHERE `idobject`= l_idobject;
		ELSE 
			UPDATE `chkrt_o_temp` SET `parent_idobject` = 0 WHERE `idobject`= l_idobject;
		END IF;
	END WHILE;
	Close cursor_tree;      
END$$  

DELIMITER ;

--
-- Переделываем таблицы на новые для guid длинной 40 символов
--

ALTER TABLE `reportindex` CHANGE `body` `body` LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;

ALTER TABLE `avgreportdata` CHANGE `o_guid` `o_guid` varchar(40) NOT NULL;
ALTER TABLE `avgreportdata` CHANGE `r_guid` `r_guid` varchar(40) NOT NULL;

ALTER TABLE `forummessages` CHANGE `o_guid` `o_guid` varchar(40) NOT NULL;

ALTER TABLE `links` CHANGE `lnk_guid` `lnk_guid` varchar(40) NOT NULL;

ALTER TABLE `objects` CHANGE `o_guid` `o_guid` varchar(40) NOT NULL;
ALTER TABLE `objects` CHANGE `o_ver` `o_ver` varchar(800) NOT NULL DEFAULT ' ';

ALTER TABLE `personallinks` CHANGE `o_guid` `o_guid` varchar(40) NOT NULL;
ALTER TABLE `personallinks` CHANGE `r_guid` `r_guid` varchar(40) NOT NULL;

ALTER TABLE `reportdata` CHANGE `o_guid` `o_guid` varchar(40) NOT NULL;
ALTER TABLE `reportdata` CHANGE `r_guid` `r_guid` varchar(40) NOT NULL;

ALTER TABLE `reports` CHANGE `r_guid` `r_guid` varchar(40) NOT NULL;


ALTER TABLE `reportsusersquery` CHANGE `o_guid` `o_guid` varchar(40) NOT NULL;
ALTER TABLE `reportsusersquery` CHANGE `r_guid` `r_guid` varchar(40) NOT NULL;


ALTER TABLE `sections` CHANGE `sct_guid` `sct_guid` varchar(40) NOT NULL;

ALTER TABLE `users` CHANGE `user_email` `user_email` varchar(256) NOT NULL;

-- Добавленно 050214
-- Переделано из соображений NOT EXIST
CALL `user_add_column`();

-- Добавлено 11.08.2015 для сокрытия элементов в дереве
CALL `objects_add_column`();

CALL `poll_main_table_create`();
CALL `rights_admin_polling_table_create`();


CALL `mailqueue_table_create`();
CALL `maitypes_table_create`();
CALL `objectsprohibitionrightscopy_table_create`();

CALL `users_remove_column_author`();

CALL `resourcefromtablespollarchive_table_create`();

CALL `userpositions_table_create`();

CALL `poll_rightspollingobjects_table_create`();
CALL `branches_add_column`();

-- Добавлено 07.11.2016

ALTER TABLE `resourcefromtables` MODIFY `rft_columnname_text` VARCHAR(1100);
-- ALTER TABLE `objects` MODIFY `o_name` VARCHAR(1100);
-- ALTER TABLE `reports` MODIFY `r_name` VARCHAR(1100);

-- Добавлено 12.09.2016

--
-- Позже пересоздается таблица
-- ALTER TABLE `portal` CHANGE `guid` `guid` varchar(40) NOT NULL;
--

DROP TABLE IF EXISTS `portal`;

CREATE TABLE IF NOT EXISTS `portal` (
  `guid` varchar(40) NOT NULL DEFAULT '-',
  `sessionkey` varchar(36) NOT NULL,
  PRIMARY KEY (`guid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Добавлено 12.09.2016
ALTER TABLE forummessages CHANGE frmm_body frmm_body LONGTEXT;

DROP FUNCTION  IF EXISTS `get_forummessageroot` ;
DELIMITER $$

CREATE FUNCTION  get_forummessageroot(idforummessage BIGINT)
	RETURNS BIGINT 
BEGIN
    DECLARE root_forummessage BIGINT DEFAULT 0;
	 DECLARE cur_idforummessage BIGINT DEFAULT 0;
	 SELECT idforummessage INTO cur_idforummessage;
	 
    WHILE ( cur_idforummessage > 0 ) DO	
    	  SELECT cur_idforummessage INTO root_forummessage;	
        SELECT frm.`parent_idforummessage` into cur_idforummessage from `forummessages` as frm where frm.`idforummessage` = cur_idforummessage;
    END WHILE;
    RETURN root_forummessage;
END; $$
DELIMITER ;





DELIMITER $$

CREATE PROCEDURE ADDforummessagessawIndex()
BEGIN
	IF (NOT EXISTS(SELECT INDEX_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA=(SELECT DATABASE()) AND TABLE_NAME = 'forummessagessaw' AND INDEX_NAME='forummessagessaw_o_guid')) THEN
		CREATE INDEX forummessagessaw_o_guid ON forummessagessaw (`o_guid`);
	END IF;	
END $$

CREATE PROCEDURE ADDfrmm_ischanged()
BEGIN

	IF (NOT EXISTS(SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'forummessages' AND COLUMN_NAME='frmm_ischanged'  AND TABLE_SCHEMA = (SELECT DATABASE()))) THEN
		ALTER TABLE `forummessages`  ADD frmm_ischanged int(1)  AFTER frmm_isdelete;
		UPDATE `forummessages` SET frmm_ischanged = 0;
	END IF;
	
END $$

CREATE PROCEDURE ADDss_key()
BEGIN
	IF (NOT EXISTS(SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'useranswersvalues' AND COLUMN_NAME='ss_key'  AND TABLE_SCHEMA = (SELECT DATABASE()))) THEN
		ALTER TABLE `useranswersvalues`
			ADD COLUMN `ss_key` VARCHAR(60) NULL AFTER `uav_tmst`;
	END IF;
END $$

CREATE PROCEDURE alterTableForumMessage()
BEGIN
	DROP TABLE IF EXISTS parents;
	CREATE TEMPORARY TABLE IF NOT EXISTS parents AS (SELECT idforummessage, frmm_ileft, frmm_iright FROM forummessages WHERE parent_idforummessage = 0 AND idforummessage > 0);
	UPDATE forummessages SET  frmm_depth = 2, parent_idforummessage = (
		SELECT f2.idforummessage FROM parents f2 WHERE  forummessages.frmm_ileft BETWEEN f2.frmm_ileft AND f2.frmm_iright)
	WHERE forummessages.parent_idforummessage > 0;
	DROP TABLE IF EXISTS parents;
END $$
DELIMITER ;


CALL ADDforummessagessawIndex();

CALL ADDfrmm_ischanged();


CALL ADDss_key();

-- 
-- Скрипт 010615
-- В этом скрипте производится создание таблиц для учета групповых прав и проводится обновление БД
-- 


-- 
-- Создание таблицы пользователей и групп
-- 

CREATE TABLE IF NOT EXISTS `usersgroups` (
	  `iduser` int(11) NOT NULL,
	  `idgroup` int(11) NOT NULL,
	  `user_hash` varchar(255) NOT NULL,
	  `group_hash` varchar(255) NOT NULL,
	  PRIMARY KEY (`iduser`,`idgroup`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Relations users and groups';

-- 
-- Создание процедуры обновления пользователей
-- 

DELIMITER $$

CREATE PROCEDURE alterTableUsers()
BEGIN
SET @s = (SELECT IF(
    (SELECT COUNT(*)
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE table_name = 'users'
        AND table_schema = DATABASE()
        AND column_name = 'user_notification_allallow'
    ) > 0,
    "SELECT 'ok';",
    "ALTER TABLE users ADD user_notification_allallow tinyint(1) AFTER user_notification_newversions;"
));

SET @s2 = (SELECT IF(
    (SELECT COUNT(*)
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE table_name = 'users'
        AND table_schema = DATABASE()
        AND column_name = 'user_notification_allallow'
    ) > 0,
    "SELECT 'ok';",
    "UPDATE users  SET user_notification_allallow = 0;"
));

PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

PREPARE stmt FROM @s2;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END $$
DELIMITER ;

-- 
-- Вызов процедуры обновления таблицы пользователей
-- 

CALL alterTableUsers();



CALL alterTableForumMessage();







-- 
-- Создание процедуры обновления таблицы последнего показа на форуме
-- 

-- DELIMITER $$

/*
CREATE PROCEDURE alterTableForumMessageSaw()
BEGIN
	IF (
		NOT EXISTS(
			SELECT COLUMN_NAME 
			FROM INFORMATION_SCHEMA.COLUMNS 
			WHERE TABLE_NAME = 'forummessagessaw' 
				AND COLUMN_NAME='frmsw_datelastsaw'  
				AND TABLE_SCHEMA = (SELECT DATABASE())
		)
	) THEN
		ALTER TABLE `forummessagessaw`  ADD frmsw_datelastsaw DATETIME AFTER frmsw_datesaw;
		UPDATE `forummessagessaw` SET frmsw_datelastsaw = frmsw_datesaw;
	END IF;
END $$
DELIMITER ;
*/
-- 
-- Вызов процедуры обновления таблицы пользователей
-- 

-- CALL alterTableForumMessageSaw();

-- DROP PROCEDURE IF EXISTS alterTableForumMessageSaw;


-- 
-- Процедура очистки лишних индексов и создание одного в rightsobjects
-- 
DELIMITER $$

CREATE PROCEDURE ReCreateIndexRightsObjects()
BEGIN
	DECLARE done INT default 0;
	DECLARE index_deleted_name VARCHAR(64) DEFAULT '';
	DECLARE cursor_index Cursor for
		SELECT `INDEX_NAME`  FROM `information_schema`.`STATISTICS` WHERE TABLE_NAME = 'rightsobjects' AND INDEX_NAME <> 'PRIMARY' AND TABLE_SCHEMA = (SELECT DATABASE()) AND COLUMN_NAME='idobject';
		
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

	OPEN cursor_index;
	WHILE done = 0 DO
		FETCH NEXT FROM cursor_index INTO index_deleted_name;
		IF (index_deleted_name <> '' AND done=0)
		THEN
			SET @query = CONCAT('DROP INDEX `',index_deleted_name,'` ON `rightsobjects`');
			PREPARE zxc FROM @query;
			EXECUTE zxc;
		END IF;
	END WHILE;
	Close cursor_index;

	ALTER TABLE `rightsobjects` ADD INDEX (`idobject`);
END $$
DELIMITER ;

CALL ReCreateIndexRightsObjects();

-- 
-- 28092017 была добавлена работа с мультиязычностью
-- Создание процедуры обновления таблицы языков
-- 

DELIMITER $$

CREATE PROCEDURE alterTableMultiLanguages()
BEGIN
	IF (
		NOT EXISTS(
			SELECT COLUMN_NAME 
			FROM INFORMATION_SCHEMA.COLUMNS 
			WHERE TABLE_NAME = 'languages' 
				AND COLUMN_NAME='native_name'  
				AND TABLE_SCHEMA = (SELECT DATABASE())
		)
	) THEN
		ALTER TABLE `languages` ADD native_name VARCHAR(255) after lang_name;
		ALTER TABLE `languages` ADD allow BIT after lang_short;
		ALTER TABLE `languages` ADD main BIT after allow;
		ALTER TABLE `languages` MODIFY lang_short VARCHAR(25) NOT NULL UNIQUE;
		ALTER TABLE `reports` MODIFY idlanguage INT(11) NOT NULL;
--		alter table `languages` MODIFY lang_name VARCHAR(50) NOT NULL UNIQUE;
		
-- Раз ещё не было информации, про языки - обновим номера русского языка в соответствующих таблицах
		update `reports` set `idlanguage` = 0 where `idlanguage` = 419;
		update `resourcefromtables` set `idlanguage` = 0 where `idlanguage` = 419;
		update `users` set `user_idlanguageinterface` = NULL where `user_idlanguageinterface` = 419;
		update `users` set `user_idlanguagecontent` = NULL where `user_idlanguagecontent` = 419;
		update `titleindex` set `idlanguage` = NULL where `idlanguage` = 419;
-- Добавим нужную структуру для reportindex(тексты отчётов для индексации)
		ALTER TABLE `reportindex` ADD idlanguage INT after idreport;
		ALTER TABLE `reportindex` ADD COLUMN skbranch int(11) NULL AFTER `idlanguage`;
		create index titleindex_idlanguage ON titleindex(idlanguage);
		create index reportindex_idlanguage ON reportindex(idlanguage);
		update `reportindex` set `idlanguage` = NULL;
-- Таблицу objectreports
-- Вот эти 2 строки делают тоже, что и следующие три:
--		ALTER TABLE `objectsreports` ADD `idlanguage` INT(11) NOT NULL AFTER `idreport`;
--		ALTER TABLE `objectsreports` DROP PRIMARY KEY, ADD PRIMARY KEY (`idobject`, `idreport`, `idlanguage`);
		ALTER TABLE `objectsreports` ADD idlanguage INT NULL;
		ALTER TABLE `objectsreports` ALTER `idlanguage` DROP DEFAULT;
		ALTER TABLE `objectsreports`
			CHANGE COLUMN `idlanguage` `idlanguage` INT(11) NOT NULL AFTER `idreport`,
			DROP PRIMARY KEY,
			ADD PRIMARY KEY (`idobject`, `idreport`, `idlanguage`);
-- Таблицу reports		
		DROP INDEX index_reports_r_guid ON reports;
		ALTER TABLE `reports` ADD UNIQUE (`r_guid`, `idlanguage`);
	END IF;
END $$
DELIMITER ;



-- 
-- Вызов процедуры обновления таблицы языков
-- 
CALL alterTableMultiLanguages();


-- 
-- Recreate Index - we need know about skbranch in UNIQUE
-- 
DROP INDEX r_guid ON reports;		
ALTER TABLE `reports` ADD UNIQUE (`r_guid`, `idlanguage`, `skbranch`);

-- This Was in distinct function with check
ALTER DATABASE CHARACTER SET utf8mb4 COLLATE 'utf8mb4_general_ci';
ALTER TABLE `avgreportdata` CONVERT TO CHARSET utf8mb4 COLLATE 'utf8mb4_general_ci';
ALTER TABLE `branches` CONVERT TO CHARSET utf8mb4 COLLATE 'utf8mb4_general_ci';
ALTER TABLE `files` CONVERT TO CHARSET utf8mb4 COLLATE 'utf8mb4_general_ci';
ALTER TABLE `forummessages` CONVERT TO CHARSET utf8mb4 COLLATE 'utf8mb4_general_ci';
ALTER TABLE `forummessagessaw` CONVERT TO CHARSET utf8mb4 COLLATE 'utf8mb4_general_ci';
ALTER TABLE `languages` CONVERT TO CHARSET utf8mb4 COLLATE 'utf8mb4_general_ci';
ALTER TABLE `links` CONVERT TO CHARSET utf8mb4 COLLATE 'utf8mb4_general_ci';
ALTER TABLE `objectlinks` CONVERT TO CHARSET utf8mb4 COLLATE 'utf8mb4_general_ci';
ALTER TABLE `objects` CONVERT TO CHARSET utf8mb4 COLLATE 'utf8mb4_general_ci';
ALTER TABLE `objectsreports` CONVERT TO CHARSET utf8mb4 COLLATE 'utf8mb4_general_ci';
ALTER TABLE `personallinks` CONVERT TO CHARSET utf8mb4 COLLATE 'utf8mb4_general_ci';
ALTER TABLE `pollingobjects` CONVERT TO CHARSET utf8mb4 COLLATE 'utf8mb4_general_ci';
ALTER TABLE `pollingobjectsarchive` CONVERT TO CHARSET utf8mb4 COLLATE 'utf8mb4_general_ci'; 
ALTER TABLE `pollingobjectstatus` CONVERT TO CHARSET utf8mb4 COLLATE 'utf8mb4_general_ci';
ALTER TABLE `polltypequestions` CONVERT TO CHARSET utf8mb4 COLLATE 'utf8mb4_general_ci'; 
ALTER TABLE `polltypequestionselectvalues` CONVERT TO CHARSET utf8mb4 COLLATE 'utf8mb4_general_ci'; 
ALTER TABLE `polltypequestiontypes` CONVERT TO CHARSET utf8mb4 COLLATE 'utf8mb4_general_ci';
ALTER TABLE `polltypes` CONVERT TO CHARSET utf8mb4 COLLATE 'utf8mb4_general_ci';
ALTER TABLE `portal` CONVERT TO CHARSET utf8mb4 COLLATE 'utf8mb4_general_ci';
ALTER TABLE `projects` CONVERT TO CHARSET utf8mb4 COLLATE 'utf8mb4_general_ci';
ALTER TABLE `reportdata` CONVERT TO CHARSET utf8mb4 COLLATE 'utf8mb4_general_ci';
ALTER TABLE `reportindex` CONVERT TO CHARSET utf8mb4 COLLATE 'utf8mb4_general_ci';
ALTER TABLE `reports` CONVERT TO CHARSET utf8mb4 COLLATE 'utf8mb4_general_ci';
ALTER TABLE `reportsusersquery` CONVERT TO CHARSET utf8mb4 COLLATE 'utf8mb4_general_ci';
ALTER TABLE `resourcefromtables` CONVERT TO CHARSET utf8mb4 COLLATE 'utf8mb4_general_ci';
ALTER TABLE `rightsobjects` CONVERT TO CHARSET utf8mb4 COLLATE 'utf8mb4_general_ci';
ALTER TABLE `rightsreports` CONVERT TO CHARSET utf8mb4 COLLATE 'utf8mb4_general_ci';
ALTER TABLE `sections` CONVERT TO CHARSET utf8mb4 COLLATE 'utf8mb4_general_ci';
ALTER TABLE `tables` CONVERT TO CHARSET utf8mb4 COLLATE 'utf8mb4_general_ci';
ALTER TABLE `titleindex` CONVERT TO CHARSET utf8mb4 COLLATE 'utf8mb4_general_ci';
ALTER TABLE `useranswers` CONVERT TO CHARSET utf8mb4 COLLATE 'utf8mb4_general_ci'; -- !This fail
ALTER TABLE `useranswersvalues` CONVERT TO CHARSET utf8mb4 COLLATE 'utf8mb4_general_ci';
ALTER TABLE `useranswersvaluesarchive` CONVERT TO CHARSET utf8mb4 COLLATE 'utf8mb4_general_ci';
ALTER TABLE `useranswersvaluesarchivetext` CONVERT TO CHARSET utf8mb4 COLLATE 'utf8mb4_general_ci';
ALTER TABLE `users` CONVERT TO CHARSET utf8mb4 COLLATE 'utf8mb4_general_ci';
ALTER TABLE `usersgroups` CONVERT TO CHARSET utf8mb4 COLLATE 'utf8mb4_general_ci';
ALTER TABLE `userslinks` CONVERT TO CHARSET utf8mb4 COLLATE 'utf8mb4_general_ci';
-- Not need - only INT ALTER TABLE `version` CONVERT TO CHARSET utf8mb4 COLLATE 'utf8mb4_general_ci';
UPDATE languages set lang_name = 'Invariant' where native_name like 'Invariant Language (Invariant Country)';

-- This Was in distinct function with check
ALTER TABLE branches AUTO_INCREMENT = 1001; -- Becouse main branch == have id 1000
UPDATE branches SET skbranch = skbranch + 1000; 
UPDATE avgreportdata SET skbranch = skbranch + 1000; 
UPDATE forummessages SET skbranch = skbranch + 1000; 
UPDATE forummessagessaw SET skbranch = skbranch + 1000; 
UPDATE objectlinks SET skbranch = skbranch + 1000; 
UPDATE objects SET skbranch = skbranch + 1000; 
UPDATE objectsreports SET skbranch = skbranch + 1000; 
UPDATE personallinks SET skbranch = skbranch + 1000; 
UPDATE pollingobjects SET skbranch = skbranch + 1000; 
UPDATE reportdata SET skbranch = skbranch + 1000; 
UPDATE reportindex SET skbranch = skbranch + 1000; 
UPDATE reports SET skbranch = skbranch + 1000; 
UPDATE reportsusersquery SET skbranch = skbranch + 1000; 
UPDATE resourcefromtables SET idrecord = idrecord + 1000 WHERE idtable = 7; 
UPDATE resourcefromtables SET skbranch = skbranch + 1000; 
UPDATE rightsobjects SET skbranch = skbranch + 1000; 
UPDATE rightsreports SET skbranch = skbranch + 1000; 
UPDATE titleindex SET skbranch = skbranch + 1000; 

-- Update column 08042020 - raise up Int To Big Int
ALTER TABLE `objects`
	CHANGE COLUMN `idobject` `idobject` BIGINT NOT NULL AUTO_INCREMENT FIRST;
ALTER TABLE `objects`
	ALTER `parent_idobject` DROP DEFAULT;
ALTER TABLE `objects`
	CHANGE COLUMN `parent_idobject` `parent_idobject` BIGINT NOT NULL AFTER `idobject`;
ALTER TABLE `objects`
	ALTER `o_ileft` DROP DEFAULT,
	ALTER `o_iright` DROP DEFAULT,
	ALTER `o_depth` DROP DEFAULT;
ALTER TABLE `objects`
	CHANGE COLUMN `o_ileft` `o_ileft` BIGINT NOT NULL AFTER `parent_idobject`,
	CHANGE COLUMN `o_iright` `o_iright` BIGINT NOT NULL AFTER `o_ileft`,
	CHANGE COLUMN `o_depth` `o_depth` BIGINT NOT NULL AFTER `o_iright`;
ALTER TABLE `resourcefromtables`
	ALTER `idrecord` DROP DEFAULT;
ALTER TABLE `resourcefromtables`
	CHANGE COLUMN `idrecord` `idrecord` BIGINT NOT NULL FIRST;
ALTER TABLE `resourcefromtablespollarchive`
	ALTER `idrecord` DROP DEFAULT;
ALTER TABLE `resourcefromtablespollarchive`
	CHANGE COLUMN `idrecord` `idrecord` BIGINT NOT NULL AFTER `guidrecord`;
ALTER TABLE `objectlinks`
	ALTER `idobject` DROP DEFAULT;
ALTER TABLE `objectlinks`
	CHANGE COLUMN `idobject` `idobject` BIGINT NOT NULL AFTER `iduser`;
ALTER TABLE `objectsprohibitionrightscopy`
	ALTER `idobject` DROP DEFAULT;
ALTER TABLE `objectsprohibitionrightscopy`
	CHANGE COLUMN `idobject` `idobject` BIGINT NOT NULL FIRST;
ALTER TABLE `objectsreports`
	ALTER `idobject` DROP DEFAULT;
ALTER TABLE `objectsreports`
	CHANGE COLUMN `idobject` `idobject` BIGINT NOT NULL FIRST;
ALTER TABLE `reportindex`
	ALTER `idobject` DROP DEFAULT;
ALTER TABLE `reportindex`
	CHANGE COLUMN `idobject` `idobject` BIGINT NOT NULL FIRST;
ALTER TABLE `rightsobjects`
	ALTER `idobject` DROP DEFAULT;
ALTER TABLE `rightsobjects`
	CHANGE COLUMN `idobject` `idobject` BIGINT NOT NULL AFTER `iduser`;
ALTER TABLE `titleindex`
	ALTER `idobject` DROP DEFAULT;
ALTER TABLE `titleindex`
	CHANGE COLUMN `idobject` `idobject` BIGINT NOT NULL FIRST;
ALTER TABLE `branches`
	ALTER `ismain` DROP DEFAULT;
ALTER TABLE `branches`
	CHANGE COLUMN `ismain` `ismain` TINYINT(4) NOT NULL AFTER `skbranch`;


-- Clear all maybe procedure after use
DROP PROCEDURE IF EXISTS alterTableMultiLanguages;
DROP PROCEDURE IF EXISTS ReCreateIndexRightsObjects;
DROP PROCEDURE IF EXISTS alterTableUsers;
DROP PROCEDURE IF EXISTS alterTableForumMessage;
DROP PROCEDURE IF EXISTS ADDforummessagessawIndex;
DROP PROCEDURE IF EXISTS ADDfrmm_ischanged;
DROP PROCEDURE IF EXISTS ADDss_key;
DROP PROCEDURE IF EXISTS `user_add_column`;
DROP PROCEDURE IF EXISTS `objects_add_column`;
DROP PROCEDURE IF EXISTS `branches_add_column`;
DROP PROCEDURE IF EXISTS `poll_main_table_create`;
DROP PROCEDURE IF EXISTS `rights_admin_polling_table_create`;
DROP PROCEDURE IF EXISTS `mailqueue_table_create`;
DROP PROCEDURE IF EXISTS `maitypes_table_create`;
DROP PROCEDURE IF EXISTS `objectsprohibitionrightscopy_table_create`;
DROP PROCEDURE IF EXISTS `users_remove_column_author`;
DROP PROCEDURE IF EXISTS `resourcefromtablespollarchive_table_create`;
DROP PROCEDURE IF EXISTS `userpositions_table_create`;
DROP PROCEDURE IF EXISTS `poll_rightspollingobjects_table_create`;

-- 
-- 28092017 Сами языки
-- 
-- delete from  `languages` ;
-- insert into `languages` values (1049, 'Russian(Russia)', 'Русский', 'ru-ru', 1, 1);
-- insert into `languages` values (1033, 'English (United States)', 'English', 'en-us', 1, 0);

-- И ресурсы для отображения зыков данных
/*
delete from `resourcefromtables` where `idtable` = 0;
insert into `resourcefromtables` values (1033, 0, 1033, 'English');
insert into `resourcefromtables` values (1033, 0, 1049, 'Английский');
insert into `resourcefromtables` values (1049, 0, 1033, 'Russian');
insert into `resourcefromtables` values (1049, 0, 1049, 'Русский');
*/

--
-- В скрипте разработчиков 110513 UPDATE portal SET sessionkey = '1';
-- Как я понял, это, чтобы не было пустых. А потом мы заменяем на корректную сессию, см. ниже
--
INSERT INTO `portal` (`guid`, `sessionkey`) VALUES
(uuid( ), uuid( ));

--
-- Маркируем версию БД
--

DROP TABLE IF EXISTS `version`;

CREATE TABLE IF NOT EXISTS  `version` (
  `version` int(11) NOT NULL,
  PRIMARY KEY (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `version` (`version`) VALUES ('20200610');