-- 3.5_p1
ALTER TABLE `contract_pattern` ADD `tgid` INT NOT NULL AFTER `dtl`;
ALTER TABLE `contract_pattern` ADD `scrid` TINYINT NOT NULL AFTER `tgid`;
ALTER TABLE `contract_pattern_services` ADD INDEX pid (pid);

CREATE TABLE IF NOT EXISTS `tariff_group` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(200) NOT NULL default '',
  `tm` tinyint(3) NOT NULL default '0',
  `df` int(11) NOT NULL default '0',
  `beh` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
);

CREATE TABLE IF NOT EXISTS `tariff_group_tariff` (
  `tgid` int(11) NOT NULL default '0',
  `tpid` int(11) NOT NULL default '0',
  KEY `tgid` (`tgid`)
);

CREATE TABLE IF NOT EXISTS `tariff_change_task` (
  `id` int(11) NOT NULL auto_increment,
  `cid` int(11) NOT NULL default '0',
  `tpid` int(11) NOT NULL default '0',
  `from_date` date default NULL,
  `ex_time` datetime default NULL,
  PRIMARY KEY  (`id`),
  KEY `cid` (`cid`),
  KEY `ex_time` (`ex_time`)
);

CREATE TABLE IF NOT EXISTS `contract_tariff_group` (
  `id` int(11) NOT NULL auto_increment,
  `cid` int(11) NOT NULL default '0',
  `date1` date default NULL,
  `date2` date default NULL,
  `gid` int(11) NOT NULL default '0',
  `comment` char(200) NOT NULL default '',
  PRIMARY KEY  (`id`),
  KEY `cid` (`cid`)
);

CREATE TABLE IF NOT EXISTS `contract_script` (
  `id` int(11) NOT NULL auto_increment,
  `cid` int(11) NOT NULL default '0',
  `date1` date default NULL,
  `date2` date default NULL,
  `script_id` int(11) NOT NULL default '0',
  `comment` varchar(200) NOT NULL default '',
  PRIMARY KEY  (`id`),
  KEY `cid` (`cid`)
);

CREATE TABLE IF NOT EXISTS `script` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`id`)
);

CREATE TABLE IF NOT EXISTS `script_event_queue` (
  `id` int(11) NOT NULL auto_increment,
  `event` longtext NOT NULL,
  PRIMARY KEY  (`id`)
);

CREATE TABLE IF NOT EXISTS `script_event_type` (
  `id` int(11) NOT NULL auto_increment,
  `mid` int(11) NOT NULL default '0',
  `event_id` int(11) NOT NULL default '0',
  `title` varchar(200) NOT NULL default '',
  PRIMARY KEY  (`id`),
  KEY `mid_event` (`mid`,`event_id`)
);

CREATE TABLE IF NOT EXISTS `script_custom_template` (
	`id` INT auto_increment NOT NULL,
	`title` varchar(100) NULL,
	`create_date` datetime NULL,
	`date_last_mod` datetime NULL,
	`user_id` INT NOT NULL,
	`template_data` TEXT NULL,
	`comment` varchar(256) NULL,
	`extension` varchar(10) NULL,
	PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `script_function` (
  `id` int(11) NOT NULL auto_increment,
  `script_id` int(11) NOT NULL default '0',
  `title` varchar(100) NOT NULL default '',
  `code` text,
  PRIMARY KEY  (`id`),
  KEY `script_id` (`script_id`)
);

CREATE TABLE IF NOT EXISTS `script_function_event_type` (
  `fid` int(11) NOT NULL default '0',
  KEY `fid` (`fid`)
);

-- 3.5_p3
ALTER TABLE scheduled_tasks CHANGE COLUMN hh hh INT(10) DEFAULT 0 NOT NULL;
-- ALTER TABLE contract_account CHANGE summa summa FLOAT(15, 5);

-- 3.5_p4
ALTER TABLE `user` ADD `gr` BIGINT NOT NULL AFTER `laf`;

CREATE TABLE IF NOT EXISTS `bgs_group` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(250) NOT NULL default '',
  `comment` varchar(250) NOT NULL default '',
  PRIMARY KEY  (`id`)
);
CREATE TABLE IF NOT EXISTS `bgs_group_action` (
  `gid` int(11) NOT NULL default '0',
  `mid` int(11) NOT NULL default '0',
  `aid` int(11) NOT NULL default '0',
  KEY `gid` (`gid`)
);
CREATE TABLE IF NOT EXISTS `bgs_user_group` (
  `uid` int(11) NOT NULL default '0',
  `gid` int(11) NOT NULL default '0',
  KEY `uid` (`uid`),
  KEY `gid` (`gid`)
);
CREATE TABLE IF NOT EXISTS `bgs_module_action` (
  `module` varchar(100) NOT NULL default '',
  `data` text NOT NULL
);

ALTER TABLE `contract_parameter_type_2` ADD COLUMN `room` VARCHAR(5)  NOT NULL AFTER `flat`;

ALTER TABLE `contract_delete_money` CHANGE `gr` `gr` BIGINT(20)  DEFAULT "0" NOT NULL;
ALTER TABLE `contract_delete_time` CHANGE `gr` `gr` BIGINT(20)  DEFAULT "0" NOT NULL;

ALTER TABLE `user` ADD `status` TINYINT NOT NULL;

CREATE TABLE IF NOT EXISTS `bgs_user_action` (
  `uid` int(11) NOT NULL default '0',
  `mid` int(11) NOT NULL default '0',
  `aid` int(11) NOT NULL default '0',
  KEY `uid` (`uid`)
);


-- 3.7
ALTER TABLE `module_tariff_tree` ADD `parent_tree` INT NOT NULL AFTER `tree_id`;

ALTER TABLE `contract_pattern` CHANGE `tpid` `tpid` VARCHAR(250)  NOT NULL;
ALTER TABLE `contract_pattern` CHANGE `tgid` `tgid` VARCHAR(250)  NOT NULL;

ALTER TABLE `script_function_event_type` ADD `mid` INT NOT NULL;
ALTER TABLE `script_function_event_type` ADD `event_id` INT NOT NULL AFTER `mid`;

CREATE TABLE IF NOT EXISTS `object_type` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(250) NOT NULL default '',
  `name_macros` varchar(250) NOT NULL default '',
  PRIMARY KEY  (`id`)
);
CREATE TABLE IF NOT EXISTS `object_param` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(250) NOT NULL default '',
  `type` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`id`)
);
CREATE TABLE IF NOT EXISTS `object_type_param` (
  `type_id` int(11) NOT NULL default '0',
  `param_id` int(11) NOT NULL default '0',
  `pos` int(11) NOT NULL default '0',
  PRIMARY KEY  (`type_id`,`param_id`)
);
CREATE TABLE IF NOT EXISTS `object` (
  `id` int(11) NOT NULL auto_increment,
  `cid` int(11) NOT NULL default '0',
  `title` varchar(250) NOT NULL default '',
  `type_id` int(11) NOT NULL default '0',
  `date1` date default NULL,
  `date2` date default NULL,
  PRIMARY KEY  (`id`),
  KEY `cid` (`cid`)
);
CREATE TABLE IF NOT EXISTS `object_list_value` (
  `id` int(11) NOT NULL auto_increment,
  `param_id` int(11) NOT NULL default '0',
  `title` varchar(250) NOT NULL default '',
  PRIMARY KEY  (`id`),
  KEY `param_id` (`param_id`)
);
CREATE TABLE IF NOT EXISTS `object_param_value_text` (
  `object_id` int(11) NOT NULL default '0',
  `param_id` int(11) NOT NULL default '0',
  `value` varchar(250) NOT NULL default '',
  PRIMARY KEY  (`param_id`,`object_id`)
);
CREATE TABLE IF NOT EXISTS `object_param_value_address` (
  `object_id` int(11) NOT NULL default '0',
  `param_id` int(11) NOT NULL default '0',
  `hid` int(11) NOT NULL default '0',
  `flat` int(11) NOT NULL default '0',
  `room` varchar(20) NOT NULL default '',
  `pod` int(11) NOT NULL default '0',
  `floor` int(11) NOT NULL default '0',
  `address` varchar(250) NOT NULL default '',
  `comment` varchar(250) NOT NULL default '',
  `format_key` varchar(50) default NULL,
  PRIMARY KEY  (`param_id`,`object_id`)
);
CREATE TABLE IF NOT EXISTS `object_param_value_date` (
  `object_id` int(11) NOT NULL default '0',
  `param_id` int(11) NOT NULL default '0',
  `value` date default NULL,
  PRIMARY KEY  (`param_id`,`object_id`)
);
CREATE TABLE IF NOT EXISTS `object_param_value_list` (
  `object_id` int(11) NOT NULL default '0',
  `param_id` int(11) NOT NULL default '0',
  `value` int(11) NOT NULL default '0',
  PRIMARY KEY  (`param_id`,`object_id`)
);

-- 3.75
ALTER TABLE source ADD config TEXT NOT NULL;

CREATE TABLE IF NOT EXISTS `user_tables` (
  `userId` int(11) default NULL,
  `table_module` varchar(100) default NULL,
  `table_id` varchar(100) default NULL,
  `widths` text,
  `positions` text,
  `hiddens` text,
  KEY `userId` (`userId`),
  KEY `table_module` (`table_module`)
);

ALTER TABLE `contract_pattern` ADD `name_pattern` VARCHAR(200)  NOT NULL DEFAULT '' AFTER `scrid`;

DELETE FROM address_quarter WHERE id=0;
DELETE FROM address_area WHERE id=0;

ALTER TABLE `contract` ADD `scid` INT NOT NULL DEFAULT 0;
ALTER TABLE `contract` ADD INDEX scid (scid);
ALTER TABLE `contract` ADD `sub_list` TEXT  NOT NULL AFTER `scid`;

ALTER TABLE `tariff_tree_config` CHANGE `data` `data` LONGTEXT NOT NULL;
ALTER TABLE `setup` CHANGE `id` `id` VARCHAR(100)  NOT NULL DEFAULT '';

ALTER TABLE `contract_tariff` ADD `pos` TINYINT NOT NULL DEFAULT 0;
ALTER TABLE `contract_tree_link` ADD `pos` TINYINT NOT NULL DEFAULT 0;

ALTER TABLE `contract_charge` ADD INDEX cid (cid);
ALTER TABLE `contract_account` ADD INDEX cid (cid);


ALTER TABLE address_quarter ADD gid INT NOT NULL DEFAULT 0;

CREATE TABLE IF NOT EXISTS `web_request_count` (
  cid int(11) NOT NULL default '0',
  date date NOT NULL default '0000-00-00',
  count int(11) NOT NULL default '0',
  UNIQUE KEY cid (cid)
);

ALTER TABLE bgs_group ADD cgr bigint(20) NOT NULL default '0';
ALTER TABLE user ADD cgr bigint(20) NOT NULL default '0';

ALTER TABLE `contract_balance` ADD INDEX cid (cid);
ALTER TABLE `script_event_queue` CHANGE `event` `event` LONGBLOB NOT NULL;
ALTER TABLE `contract_parameter_type_2` CHANGE `flat` `flat` VARCHAR(10);

-- 4.0
CREATE TABLE IF NOT EXISTS `contract_parameter_type_8` (
 cid int(11) NOT NULL DEFAULT '0' ,
 pid int(11) NOT NULL DEFAULT '0' ,
 val int(11) NOT NULL DEFAULT '0' ,
 UNIQUE KEY cid_pid (cid,pid)
);

ALTER TABLE `bgs_group` ADD `pids` VARCHAR(120);
ALTER TABLE `user` ADD `pids` VARCHAR(120);

ALTER TABLE `user` ADD `contract_pid` INT DEFAULT "0" NOT NULL;
ALTER TABLE `user` ADD `contract_cid` INT DEFAULT "0" NOT NULL;

CREATE TABLE IF NOT EXISTS `web_request_count` (
  cid int(11) NOT NULL DEFAULT '0' ,
  date date NOT NULL DEFAULT '0000-00-00' ,
  count int(11) NOT NULL DEFAULT '0' ,
  UNIQUE KEY cid (cid)
);

-- 4.1
CREATE TABLE IF NOT EXISTS `time_type` (
  id int(11) NOT NULL auto_increment,
  title varchar(100) NOT NULL DEFAULT '' ,
  data text NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS `contract_limit_manage` (
  id int(11) NOT NULL auto_increment,
  cid int(11) NOT NULL ,
  clp_id int(11) ,
  summ float(10,2) ,
  date1 datetime NOT NULL ,
  date2 date ,
  pids varchar(100) ,
  rest float(10,2) NOT NULL DEFAULT '0.00' ,
  status int(11) DEFAULT '0' ,
  PRIMARY KEY (id),
   KEY cid (cid),
   KEY clp_id (clp_id)
);

CREATE TABLE IF NOT EXISTS `contract_limit_manage_mode` (
  cid int(11) NOT NULL ,
  mode int(11) NOT NULL DEFAULT '0' ,
  cnt int(11) NOT NULL DEFAULT '0' ,
  PRIMARY KEY (cid)
);

CREATE TABLE IF NOT EXISTS `log_contract_limit_manage_mode` (
  dt datetime NOT NULL DEFAULT '0000-00-00 00:00:00' ,
  uid int(11) NOT NULL DEFAULT 0 ,
  mode int(11) NOT NULL DEFAULT 0 ,
  cid int(11) NOT NULL DEFAULT 0 ,
   KEY cid (cid)
);

CREATE TABLE IF NOT EXISTS `web_request_limit` (
  cid int(11) NOT NULL ,
  lim int(11) ,
  PRIMARY KEY (cid)
);

CREATE TABLE IF NOT EXISTS `scheduled_task_run` (
  id int(11) NOT NULL auto_increment,
  data blob ,
  PRIMARY KEY (id)
);

-- 4.2
ALTER TABLE address_house CHANGE house house INT DEFAULT "0" NOT NULL;

-- 4.3
ALTER TABLE object ADD pos INT DEFAULT 0 NOT NULL;
UPDATE object SET pos=id WHERE pos=0;
ALTER TABLE object ADD INDEX pos (pos);

ALTER TABLE contract_parameters_pref ADD script TEXT NOT NULL AFTER sort;
ALTER TABLE news CHANGE gr gr BIGINT  DEFAULT "0" NOT NULL;

ALTER TABLE installed_modules ADD COLUMN `id` INT  NOT NULL AUTO_INCREMENT PRIMARY KEY;

ALTER TABLE bgs_user_action CHANGE mid mid VARCHAR(10)  NOT NULL;
ALTER TABLE bgs_group_action CHANGE mid mid VARCHAR(10)  NOT NULL;

ALTER TABLE `installed_modules` ADD COLUMN `enabled` TINYINT(1)  NOT NULL DEFAULT 0;

CREATE TABLE IF NOT EXISTS `plugin_config` (
  `pid` int(11) NOT NULL auto_increment COMMENT 'plugin id (from installed_modules)',
  `config` text,
  PRIMARY KEY (`pid`)
);

CREATE TABLE IF NOT EXISTS `contract_logon_error` (
  lu timestamp NOT NULL  ,
  cid int(11) NOT NULL,
  login varchar(255) NOT NULL,
  dt datetime NOT NULL,
  ip varchar(15) NOT NULL,
  error_code int(11) NOT NULL,
  request_data text NOT NULL,
   KEY cid_dt (cid,dt),
   KEY login_dt (login,dt)
);

CREATE TABLE IF NOT EXISTS `contract_logon_last` (
  `lu` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `cid` int(11) NOT NULL,
  `dt` datetime NOT NULL,
  `n` int(11) NOT NULL,
  `ip` varchar(15) NOT NULL,
  PRIMARY KEY (`cid`)
);

CREATE TABLE IF NOT EXISTS `contract_logon_ok` (
  lu timestamp NOT NULL ,
  cid int(11) NOT NULL,
  dt datetime NOT NULL,
  ip varchar(15) NOT NULL,
  session_id varchar(32) NOT NULL,
  user enum('c','a') NOT NULL DEFAULT 'c' ,
  KEY new_index (cid,dt)
);

CREATE TABLE IF NOT EXISTS `contract_password_once` (
  lu TIMESTAMP NOT NULL ,
  dt DATETIME NOT NULL,
  contract_title VARCHAR(150) NOT NULL,
  `password` VARCHAR(64) NOT NULL UNIQUE
);

ALTER TABLE script_event_type CHANGE mid mid VARCHAR(100) NOT NULL;
ALTER TABLE script_function_event_type CHANGE mid mid VARCHAR(100) NOT NULL;
ALTER TABLE contract_logon_ok MODIFY COLUMN `user` ENUM('p','c','a') NOT NULL DEFAULT 'c';

-- 4.4
ALTER TABLE `user` ADD COLUMN `config` TEXT NOT NULL;
ALTER TABLE source CHANGE host_or_dir host_or_dir VARCHAR(250)  NOT NULL;
ALTER TABLE address_house CHANGE frac frac VARCHAR(30);
ALTER TABLE log_contract_limit ADD comment VARCHAR(250)  NOT NULL DEFAULT '';
ALTER TABLE log_contract_limit ADD days VARCHAR(4)  NOT NULL DEFAULT 0;
ALTER TABLE contract ADD status TINYINT NOT NULL DEFAULT 0;
ALTER TABLE contract ADD status_date DATE;
ALTER TABLE contract ADD last_tariff_change DATETIME;

CREATE TABLE IF NOT EXISTS `object_type_module` (
  type_id int(11) NOT NULL DEFAULT 0 ,
  mid int(11) NOT NULL DEFAULT 0 ,
  UNIQUE KEY type_mid (mid,type_id)
);

ALTER TABLE  contract_pattern ADD data blob;

CREATE TABLE IF NOT EXISTS `contract_status` (
  id int(11) NOT NULL auto_increment,
  cid int(11) NOT NULL,
  status tinyint(4) NOT NULL,
  uid int(11) NOT NULL,
  date1 date,
  date2 date,
  comment varchar(250) NOT NULL DEFAULT '',
  PRIMARY KEY  (id)
);

ALTER TABLE contract_status ADD date1 DATE;
ALTER TABLE installed_modules CHANGE init init LONGTEXT NOT NULL;

-- 4.5
ALTER TABLE contract_service ADD emid INT DEFAULT 0 NOT NULL;
ALTER TABLE contract_service ADD eid INT DEFAULT 0 NOT NULL;

ALTER TABLE contract_tariff ADD emid INT DEFAULT 0 NOT NULL;
ALTER TABLE contract_tariff ADD eid INT DEFAULT 0 NOT NULL;

ALTER TABLE payment_register MODIFY COLUMN summa DECIMAL(10,2)  NOT NULL DEFAULT 0;
ALTER TABLE payment_register ADD COLUMN load_log TEXT NOT NULL;

ALTER TABLE address_street ADD COLUMN `p_index` varchar(6) DEFAULT '' not NULL;
ALTER TABLE address_street ADD COLUMN `cityid` int default '-1' not NULL;
ALTER TABLE address_quarter ADD COLUMN `cityid` int default '-1' not NULL;
ALTER TABLE address_house ADD COLUMN `pod` varchar(200) default '' not NULL;
ALTER TABLE address_area ADD COLUMN `cityid` int default '-1' not NULL;

ALTER TABLE contract_comment ADD COLUMN visibled BIT(1) NOT NULL DEFAULT 0 AFTER `dt`;

ALTER TABLE contract_balance MODIFY summa1 DECIMAL(10,2) NOT NULL;
ALTER TABLE contract_balance MODIFY summa2 DECIMAL(10,2) NOT NULL;
ALTER TABLE contract_balance MODIFY summa3 DECIMAL(10,2) NOT NULL;
ALTER TABLE contract_balance MODIFY summa4 DECIMAL(10,2) NOT NULL;

ALTER TABLE contract_payment MODIFY summa DECIMAL(10,2) NOT NULL;
ALTER TABLE contract_charge MODIFY summa DECIMAL(10,2) NOT NULL;

ALTER TABLE contract_limit_manage MODIFY summ DECIMAL(10,2) NOT NULL;
ALTER TABLE contract_limit_period MODIFY value DECIMAL(10,2) NOT NULL;
ALTER TABLE log_contract_limit MODIFY nvalue DECIMAL(10,2) NOT NULL;

ALTER TABLE contract MODIFY closesumma DECIMAL(10,2) NOT NULL DEFAULT 0;
ALTER TABLE tariff_group ADD COLUMN pos INT  NOT NULL;

ALTER TABLE contract_parameter_type_1 MODIFY val varchar(255) NOT NULL DEFAULT "";

ALTER TABLE contract_status ADD INDEX `cid`(`cid`);

ALTER TABLE script_event_type ADD COLUMN event_mode TINYINT NOT NULL DEFAULT 1 AFTER mid;

ALTER TABLE tariff_plan ADD COLUMN actual INT NOT NULL DEFAULT 1;

ALTER TABLE log_contract_limit MODIFY days VARCHAR(4);

ALTER TABLE user ADD `cgr_mode` TINYINT NOT NULL DEFAULT 1;
ALTER TABLE bgs_group ADD `cgr_mode` TINYINT NOT NULL DEFAULT 1;

-- 4.6
-- для CRM ки - не удалятть старые поля, так как испоьльузются при архивации договоров
ALTER TABLE contract ADD COLUMN crm_customer_id INTEGER  NOT NULL;
ALTER TABLE contract ADD INDEX crm_customer_id(crm_customer_id);

ALTER TABLE user ADD COLUMN crm_user_id INTEGER  NOT NULL;
ALTER TABLE contract ADD INDEX crm_user_id(crm_user_id);

CREATE TABLE IF NOT EXISTS `contract_parameter_type_9` (
  `pid` int(11) NOT NULL,
  `value` varchar(255) default NULL,
  `phone1` varchar(11) default NULL,
  `phone_comm1` varchar(40) default NULL,
  `phone2` varchar(11) default NULL,
  `phone_comm2` varchar(40) default NULL,
  `phone3` varchar(11) default NULL,
  `phone_comm3` varchar(40) default NULL,
  `phone4` varchar(11) default NULL,
  `phone_comm4` varchar(40) default NULL,
  `phone5` varchar(11) default NULL,
  `phone_comm5` varchar(40) default NULL,
  `cid` int(11) NOT NULL,
   KEY  cid_pid(cid, pid)
); 

ALTER TABLE contract_parameter_type_9 
 ADD COLUMN `phone_format1` VARCHAR(2)  DEFAULT NULL AFTER `phone1`,
 ADD COLUMN `phone_format2` VARCHAR(2)  DEFAULT NULL AFTER `phone2`,
 ADD COLUMN `phone_format3` VARCHAR(2)  DEFAULT NULL AFTER `phone3`,
 ADD COLUMN `phone_format4` VARCHAR(2)  DEFAULT NULL AFTER `phone4`,
 ADD COLUMN `phone_format5` VARCHAR(2)  DEFAULT NULL AFTER `phone5`;

ALTER TABLE contract ADD COLUMN sub_mode TINYINT(4) NOT NULL AFTER sub_list;

-- ошибка обновления адреса бывает
ALTER TABLE `address_house` CHANGE COLUMN `pod` `pod_diapazon` varchar(200) NOT NULL;

-- для тарифных планов
ALTER TABLE `tariff_plan` ADD COLUMN `actual` INT  DEFAULT 1 AFTER `lm`;
ALTER TABLE `tariff_plan` ADD COLUMN `gr` bigint(20) default 0;
ALTER TABLE `tariff_plan` ADD COLUMN `pattern` varchar(255) default NULL;
ALTER TABLE `tariff_plan` ADD COLUMN `face` tinyint(4) default 0;

-- флажок "используется" для услуг
ALTER TABLE `service` ADD COLUMN `isusing` TINYINT(1) default 1;

CREATE TABLE IF NOT EXISTS `contract_module` (
  cid INTEGER  NOT NULL,
  mid INTEGER  NOT NULL,
  PRIMARY KEY (cid, mid)
);

CREATE TABLE IF NOT EXISTS `contract_status_log` (
  `id` int(11) NOT NULL auto_increment,
  `status` tinyint(4) NOT NULL,
  `uid` int(11) NOT NULL,
  `date` datetime NOT NULL,
  `comment` varchar(250) NOT NULL,
  `cid` int(11) NOT NULL default '0',
  `date1` date NOT NULL,
  `date2` date default NULL,
  PRIMARY KEY  (`id`)
);

CREATE TABLE IF NOT EXISTS `periodic_errors` (
  id int(11) NOT NULL auto_increment,
  marker varchar(50) default NULL,
  calc_date date default NULL,
  subject varchar(50) default NULL,
  text text,
  reg_time date default NULL,
  PRIMARY KEY  (`id`)
);

ALTER TABLE contract_parameters_pref ADD COLUMN history BOOLEAN  NOT NULL AFTER script;

CREATE TABLE IF NOT EXISTS `contract_parameter_type_1_log` (
  cid int(11) NOT NULL,
  pid int(11) NOT NULL,
  val varchar(255) default NULL,
  dt_change datetime NOT NULL,
  user_id int(11) NOT NULL,
  KEY cid_pid (cid,pid)
);

CREATE TABLE IF NOT EXISTS `contract_parameter_type_5_log` (
  cid int(11) NOT NULL,
  pid int(11) NOT NULL,
  val int(2) default NULL,
  dt_change datetime NOT NULL,
  user_id int(11) NOT NULL,
  KEY `cid_pid` (cid,pid)
);

CREATE TABLE IF NOT EXISTS `contract_parameter_type_6_log` (
  cid int(11) NOT NULL,
  pid int(11) NOT NULL,
  val date default NULL,
  dt_change datetime NOT NULL,
  user_id int(11) NOT NULL,
  KEY cid_pid (cid,pid)
);

CREATE TABLE IF NOT EXISTS `contract_parameter_type_7_log` (
  cid int(11) NOT NULL,
  pid int(11) NOT NULL,
  val int(11) default NULL,
  title varchar(100) default NULL,
  dt_change datetime NOT NULL,
  user_id int(11) NOT NULL,
  KEY cid_pid (cid,pid)
);

CREATE TABLE IF NOT EXISTS `contract_parameter_type_8_log` (
  cid int(11) NOT NULL,
  pid int(11) NOT NULL,
  val int(11) default NULL,
  dt_change datetime NOT NULL,
  user_id int(11) NOT NULL,
  KEY cid_pid (cid,pid)
);

-- неверно указано название столбца value, но ниже идет исправляющий алтер
CREATE TABLE IF NOT EXISTS `contract_parameter_type_9_log` (
  cid int(11) NOT NULL,
  pid int(11) NOT NULL,
  value varchar(255) default NULL,
  phone1 varchar(11) default NULL,
  phone_format1 varchar(2) default NULL,
  phone_comm1 varchar(40) default NULL,
  phone2 varchar(11) default NULL,
  phone_format2 varchar(2) default NULL,
  phone_comm2 varchar(40) default NULL,
  phone3 varchar(11) default NULL,
  phone_format3 varchar(2) default NULL,
  phone_comm3 varchar(40) default NULL,
  phone4 varchar(11) default NULL,
  phone_format4 varchar(2) default NULL,
  phone_comm4 varchar(40) default NULL,
  phone5 varchar(11) default NULL,
  phone_format5 varchar(2) default NULL,
  phone_comm5 varchar(40) default NULL,
  dt_change datetime NOT NULL,
  user_id int(11) NOT NULL,
  KEY cip_pid (cid,pid)
);

ALTER TABLE contract_parameter_type_9_log CHANGE COLUMN `value` `val` VARCHAR(255) DEFAULT NULL;

CREATE TABLE IF NOT EXISTS `contract_pattern_modules` ( 
    pid	int(11) NOT NULL,
    mid	int(11) NOT NULL 
  );
  
-- удаление ненужных индексов из таблицы contract
ALTER TABLE contract DROP INDEX pfid_id;
ALTER TABLE contract DROP INDEX id_pfid;
ALTER TABLE contract DROP INDEX pfid_date2_id;
ALTER TABLE contract DROP INDEX pfid_gr_date2_id;
ALTER TABLE contract DROP INDEX date2_;
ALTER TABLE contract DROP INDEX pfid_date2;
ALTER TABLE contract DROP INDEX pfid_date2_gr;
ALTER TABLE contract DROP INDEX pfid_gr_date2;

-- удаление ненужного индекса из таблицы contract_account
ALTER TABLE contract_account DROP INDEX yy_mm_cid;

-- Таблица справочник шаблонов комментариев для договора
CREATE TABLE IF NOT EXISTS `contract_comment_patterns` (
  id INT  NOT NULL AUTO_INCREMENT,
  title VARCHAR(25)  NOT NULL,
  pat VARCHAR(255)  NOT NULL,
  PRIMARY KEY (id)
);

ALTER TABLE contract_pattern ADD COLUMN patid INT  NOT NULL DEFAULT 0;
ALTER TABLE contract ADD COLUMN title_pattern_id INT  NOT NULL DEFAULT 0;

ALTER TABLE user ADD cgr_mode TINYINT NOT NULL DEFAULT 1;
ALTER TABLE bgs_group ADD cgr_mode TINYINT NOT NULL DEFAULT 1;

ALTER TABLE payment_register ADD COLUMN ptitle VARCHAR(50) NOT NULL;
ALTER TABLE payment_register ADD COLUMN regtype INT NOT NULL DEFAULT 0;

ALTER TABLE contract_account MODIFY summa DECIMAL(15,5);

CREATE TABLE IF NOT EXISTS `contract_parameter_type_2_log` (
  cid int(11) NOT NULL,
  pid int(11) NOT NULL,
  val varchar(255) default NULL,
  dt_change datetime NOT NULL,
  user_id int(11) NOT NULL,
  KEY cid_pid (cid,pid)
);

CREATE TABLE IF NOT EXISTS `contract_parameter_type_3_log` (
  cid int(11) NOT NULL,
  pid int(11) NOT NULL,
  val varchar(255) default NULL,
  dt_change datetime NOT NULL,
  user_id int(11) NOT NULL,
  KEY cid_pid (cid,pid)
);

CREATE TABLE IF NOT EXISTS `contract_parameter_type_4_log` (
  cid int(11) NOT NULL,
  pid int(11) NOT NULL,
  val varchar(255) default NULL,
  dt_change datetime NOT NULL,
  user_id int(11) NOT NULL,
  KEY cid_pid (cid,pid)
);

CREATE TABLE IF NOT EXISTS `script_lib` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `script` TEXT NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
);

ALTER TABLE `contract` MODIFY COLUMN `sub_list` TEXT  NOT NULL;
ALTER TABLE `contract_pattern` MODIFY COLUMN `scrid` VARCHAR(250) NOT NULL;
DELETE FROM scheduled_class WHERE class="bitel.billing.server.contract.ContractMonitor";

ALTER TABLE contract_parameter_type_9 DROP PRIMARY KEY;
ALTER TABLE contract_parameter_type_9 ADD KEY cid_pid (cid, pid);

CREATE TABLE IF NOT EXISTS `global_script` (
  id INT  NOT NULL AUTO_INCREMENT,
  title VARCHAR(255) ,
  script TEXT ,
  PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS `tariff_option` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(250) NOT NULL,
  `tariff_ids` text NOT NULL,
  `comment` varchar(250) NOT NULL,
  `date1` date DEFAULT NULL,
  `date2` date DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `tariff_option_activate_mode` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `option_id` int(11) NOT NULL,
  `charge_type_id` int(11) NOT NULL,
  `charge_summa` decimal(10,2) NOT NULL,
  `period_mode` tinyint(2) NOT NULL,
  `period_col` int(11) NOT NULL,
  `date1` date DEFAULT NULL,
  `date2` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `option_id` (`option_id`)
);

CREATE TABLE IF NOT EXISTS `contract_tariff_option` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL,
  `cid` int(11) NOT NULL,
  `option_id` int(11) NOT NULL,
  `time_from` datetime NOT NULL,
  `time_to` datetime NOT NULL,
  `charge_id` int(11) NOT NULL,
  `summa` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `cid` (`cid`)
);

-- #BLOCK#
CREATE TABLE IF NOT EXISTS _contract_status_bak_bir SELECT * FROM contract_status;
DELETE FROM contract_status WHERE (SELECT COUNT(*) FROM contract WHERE contract.id=contract_status.cid)=0;
-- #ENDB#

ALTER TABLE  periodic_errors MODIFY marker varchar(250) NOT NULL;
ALTER TABLE  periodic_errors MODIFY subject varchar(250) NOT NULL;

-- 5.0

-- Период видимости тарифов в группе тарифов, для смены тарифов пользователями через веб.
ALTER TABLE `tariff_group_tariff`
ADD COLUMN `date1` date DEFAULT NULL COMMENT 'начало периода видимости тарифа в группе тарифов',
ADD COLUMN `date2` date DEFAULT NULL COMMENT 'конец периода видимости тарифа в группе тарифов';

ALTER TABLE `tariff_option` ADD COLUMN `depends` VARCHAR(255)  NOT NULL DEFAULT '';
ALTER TABLE `tariff_option` ADD COLUMN `incompatible` VARCHAR(255)  NOT NULL DEFAULT '';

ALTER TABLE `contract_tariff_option` MODIFY COLUMN `time_to` DATETIME NULL;

ALTER TABLE `contract_tariff_option` ADD COLUMN `activated_mode` INT(11) NOT NULL;
ALTER TABLE `contract_tariff_option` ADD COLUMN `activated_time` DATETIME NOT NULL;

ALTER TABLE `contract_tariff_option` ADD COLUMN `deactivated_time` DATETIME NULL;

ALTER TABLE `tariff_option_activate_mode` ADD COLUMN `deactivation_mode` INT(11) NOT NULL;

ALTER TABLE `tariff_option_activate_mode` ADD COLUMN `reactivation_mode` INT(11) NOT NULL DEFAULT 0;

ALTER TABLE `tariff_option` ADD COLUMN `contract_groups` BIGINT NOT NULL DEFAULT 0;

ALTER TABLE `module_config` MODIFY COLUMN `config` longtext NULL;

-- Время редактирования скриптов для совместной разработки
ALTER TABLE `global_script` ADD COLUMN `user_id` int(11)  NOT NULL;
ALTER TABLE `global_script` ADD COLUMN `change_time` TIMESTAMP NOT NULL;
ALTER TABLE `script_lib` ADD COLUMN `user_id` int(11)  NOT NULL;
ALTER TABLE `script_lib` ADD COLUMN `change_time` TIMESTAMP NOT NULL;
ALTER TABLE `script_function` ADD COLUMN `user_id` int(11)  NOT NULL;
ALTER TABLE `script_function` ADD COLUMN `change_time` TIMESTAMP NOT NULL;

-- 5.1
ALTER TABLE contract_pattern MODIFY scrid VARCHAR(250) NOT NULL;
ALTER TABLE `log_contract_mode` CHANGE COLUMN `mode` `value` INTEGER  NOT NULL DEFAULT 0;

ALTER TABLE `object_param` ADD COLUMN `comment` VARCHAR(255)  NOT NULL;
ALTER TABLE `object_type` ADD COLUMN `comment` VARCHAR(255)  NOT NULL;

CREATE TABLE IF NOT EXISTS `log_contract_face` (
  `dt` DATETIME  NOT NULL DEFAULT '0000-00-00 00:00:00',
  `uid` INTEGER  NOT NULL DEFAULT 0,
  `value` INTEGER  NOT NULL DEFAULT 0,
  `cid` INTEGER  NOT NULL DEFAULT 0
);

ALTER TABLE `object_type` ADD COLUMN `ishidden` BOOLEAN  DEFAULT 0;
ALTER TABLE `address_city` ADD COLUMN `pos` INTEGER DEFAULT 1;
ALTER TABLE `installed_modules` ADD COLUMN `uninstall` LONGTEXT;

ALTER TABLE script_event_type MODIFY event_id VARCHAR(255) NOT NULL DEFAULT '0';
ALTER TABLE script_function_event_type MODIFY event_id VARCHAR(255) NOT NULL DEFAULT '0';

CREATE TABLE IF NOT EXISTS `user_menu` (
  `uid` int(11) NOT NULL,
  `menu_id` varchar(50) NOT NULL,
  `hidden` tinyint(2) NOT NULL,
  PRIMARY KEY (`uid`,`menu_id`)
);

CREATE TABLE IF NOT EXISTS `bgs_group_menu` (
  `gid` int(11) NOT NULL,
  `menu_id` varchar(50) NOT NULL,
  `hidden` tinyint(2) NOT NULL,
  PRIMARY KEY (`gid`,`menu_id`)
);

ALTER TABLE `contract_tariff` ADD COLUMN `replaced_from` INTEGER;

ALTER TABLE `address_house` DROP INDEX  `House`;
ALTER TABLE `address_house` DROP COLUMN  `cityid`;
ALTER TABLE `address_house` ADD UNIQUE INDEX  `HouseU`(`streetid`, `house`, `frac`);

CREATE TABLE IF NOT EXISTS `scheduled_periodic_run` (
  `description` text,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
);

ALTER TABLE `scheduled_task_run` ADD COLUMN `description` TEXT;
ALTER TABLE `scheduled_task_run` ADD COLUMN `executed` TINYINT(2) DEFAULT 0;

ALTER TABLE `contract_charge` CHANGE COLUMN `lm` `time_change` TIMESTAMP  NOT NULL;
ALTER TABLE `contract_payment` ADD COLUMN `time_change` TIMESTAMP  NOT NULL;

CREATE TABLE IF NOT EXISTS `contract_parameter_type_phone` (
  `pid` int(11) NOT NULL,
  `cid` int(11) NOT NULL,
  `value` varchar(255) NOT NULL,
  PRIMARY KEY (`pid`,`cid`)
);

CREATE TABLE IF NOT EXISTS `contract_parameter_type_phone_item` (
  `pid` int(11) NOT NULL,
  `cid` int(11) NOT NULL,
  `n` tinyint(4) NOT NULL,
  `phone` varchar(11) DEFAULT NULL,
  `format` varchar(2) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`pid`,`cid`,`n`),
  KEY `phone` (`phone`)
);

CREATE TABLE IF NOT EXISTS `contract_parameter_type_phone_log` (
  `pid` int(11) NOT NULL,
  `cid` int(11) NOT NULL,
  `val` varchar(255) DEFAULT NULL,
  `data` text,
  `dt_change` datetime NOT NULL,
  `user_id` int(11) NOT NULL,
  KEY `cid_pid` (`cid`,`pid`)
);

ALTER TABLE `user`  ADD COLUMN `email` char(50) NOT NULL;

ALTER TABLE `module_config` MODIFY COLUMN `config` longtext NULL;

ALTER TABLE `contract_tree_link` ADD COLUMN  `emid` int(11) NOT NULL DEFAULT '0';
ALTER TABLE `contract_tree_link` ADD COLUMN   `eid` int(11) NOT NULL DEFAULT '0';

ALTER TABLE `user_dispatch_reference` ADD COLUMN `client_class_name` VARCHAR(200)  NOT NULL;
UPDATE user_dispatch_reference SET client_class_name='bitel.billing.module.contract.ContractDispatchBalanceEditor' WHERE web_action='DispatchBalance';

ALTER TABLE `address_house` MODIFY `pod_diapazon` TEXT NOT NULL;
ALTER TABLE `address_city` ADD COLUMN  `country_id` int(11) NOT NULL DEFAULT '1';
CREATE TABLE IF NOT EXISTS `address_country` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL DEFAULT '',
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
);

ALTER TABLE `scheduled_task_run` ADD COLUMN `start_time` DATETIME;
ALTER TABLE `scheduled_periodic_run` ADD COLUMN `amount` INTEGER  DEFAULT 0;

-- последовательные идентификаторы
CREATE TABLE IF NOT EXISTS `sequential_ids` (
  `mid` VARCHAR(8)  NOT NULL,
  `value` BIGINT  NOT NULL,
  PRIMARY KEY (`mid`)
);

-- история параметров объектов
ALTER TABLE object_param ADD COLUMN history tinyint(4) NOT NULL;
	
CREATE TABLE IF NOT EXISTS `object_param_value_text_log` ( 
    object_id	int(11) NOT NULL,
    param_id 	int(11) NOT NULL,
    value    	varchar(250) NOT NULL,
    dt_change datetime NOT NULL,
    user_id int(11) NOT NULL,
    KEY(param_id,object_id)
);

CREATE TABLE IF NOT EXISTS `object_param_value_list_log` ( 
    object_id	int(11) NOT NULL,
    param_id 	int(11) NOT NULL,
    value    	int(11) NOT NULL,
    title    	varchar(250) NOT NULL,
    dt_change datetime NOT NULL,
    user_id int(11) NOT NULL,
     KEY(param_id,object_id)
);

CREATE TABLE IF NOT EXISTS `object_param_value_date_log` ( 
    object_id	int(11) NOT NULL,
    param_id 	int(11) NOT NULL,
    value    	date NULL,
    dt_change datetime NOT NULL,
    user_id int(11) NOT NULL,
     KEY(param_id,object_id)
);

CREATE TABLE IF NOT EXISTS `object_param_value_address_log` ( 
    object_id 	int(11) NOT NULL,
    param_id  	int(11) NOT NULL,
    value   	varchar(250) NOT NULL,
    dt_change datetime NOT NULL,
    user_id int(11) NOT NULL,
    KEY(object_id,param_id)
);

-- параметр обектов типа флаг
CREATE TABLE IF NOT EXISTS `object_param_value_flag` ( 
    object_id	int(11) NOT NULL,
    param_id	int(11) NOT NULL,
    value	int(2) NOT NULL,
    PRIMARY KEY(object_id,param_id)
);

CREATE TABLE IF NOT EXISTS `object_param_value_flag_log` ( 
    object_id	int(11) NOT NULL,
    param_id	int(11) NOT NULL,
    value	int(2) NOT NULL,
    dt_change datetime NOT NULL,
    user_id int(11) NOT NULL,
    KEY(object_id,param_id)
);

-- 5.2
ALTER TABLE `contract_parameter_type_7` ADD COLUMN `custom_value` VARCHAR(100)  NOT NULL;

-- добавление столбца tree_id в таблицу tariff_plan
ALTER TABLE `tariff_plan` ADD COLUMN `tree_id` INTEGER NOT NULL;

-- перенос данных из таблицы tariff_tree_link
--  tp.tree_id IS NULL - это лишняя проверка с учетом RIGHT JOIN, но опусть будет. А вот  OR  tp.tree_id = 0 как оказалось - не лишняя.
UPDATE `tariff_plan` AS tp 
RIGHT JOIN `tariff_tree_link` AS ttl ON tp.id=ttl.tpid -- страхуемся, чтобы не перетерло новые тарифы 
SET tp.tree_id=ttl.tree_id
WHERE tp.tree_id IS NULL OR tp.tree_id = 0;  

ALTER TABLE `contract_group` ADD `editable` TINYINT(3) NOT NULL default '1';

ALTER TABLE `contract_charge`  CHANGE COLUMN `time_change` `lm` TIMESTAMP  NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
ALTER TABLE `contract_payment` CHANGE COLUMN `time_change` `lm` TIMESTAMP  NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

CREATE TABLE IF NOT EXISTS `entity` (
  `id` int(11) NOT NULL auto_increment,
  `entitySpecId` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
);

CREATE TABLE IF NOT EXISTS `entity_attr_address` (
  `entityId` int(11) NOT NULL,
  `entitySpecAttrId` int(11) NOT NULL,
  `houseId` int(10) unsigned default NULL,
  `flat` varchar(10) default NULL,
  `room` varchar(5) NOT NULL default '',
  `pod` int(10),
  `floor` int(10),
  `value` varchar(255) default NULL,
  `comment` varchar(255) default NULL,
  PRIMARY KEY   (`entityId`,`entitySpecAttrId`)
);

CREATE TABLE IF NOT EXISTS `entity_attr_date` (
  `entityId` int(11) NOT NULL,
  `entitySpecAttrId` int(11) NOT NULL,
  `value` date NOT NULL,
  PRIMARY KEY (`entityId`,`entitySpecAttrId`)
);

CREATE TABLE IF NOT EXISTS `entity_attr_house` (
  `entityId` int(11) NOT NULL,
  `entitySpecAttrId` int(11) NOT NULL,
  `value` int(11) NOT NULL,
  PRIMARY KEY   (`entityId`,`entitySpecAttrId`)
);

CREATE TABLE IF NOT EXISTS `entity_attr_int` (
  `entityId` int(11) NOT NULL,
  `entitySpecAttrId` int(11) NOT NULL,
  `value` int(11) NOT NULL,
  PRIMARY KEY   (`entityId`,`entitySpecAttrId`)
);

CREATE TABLE IF NOT EXISTS `entity_attr_list` (
  `entityId` int(11) NOT NULL,
  `entitySpecAttrId` int(11) NOT NULL,
  `value` int(11) NOT NULL,
  PRIMARY KEY   (`entityId`,`entitySpecAttrId`)
);

CREATE TABLE IF NOT EXISTS `entity_attr_text` (
  `entityId` int(11) NOT NULL,
  `entitySpecAttrId` int(11) NOT NULL,
  `value` varchar(255) NOT NULL,
  PRIMARY KEY   (`entityId`,`entitySpecAttrId`)
);

CREATE TABLE IF NOT EXISTS `entity_spec` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(255) NOT NULL,
  `entitySpecTypeId` int(11) NOT NULL,
  `comment` text NOT NULL,
  `hidden` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `entity_type_id` (`entitySpecTypeId`)
);

CREATE TABLE IF NOT EXISTS `entity_spec_attr` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(255) NOT NULL,
  `type` int(11) NOT NULL,
  `comment` text NOT NULL,
  PRIMARY KEY  (`id`)
);

CREATE TABLE IF NOT EXISTS `entity_spec_attr_link` (
  `entitySpecId` int(11) NOT NULL,
  `entitySpecAttrId` int(11) NOT NULL,
  `pos` int(11) NOT NULL,
  KEY `entity_spec_id` (`entitySpecId`)
);

CREATE TABLE IF NOT EXISTS `entity_spec_attr_list` (
  `id` int(11) NOT NULL auto_increment,
  `entitySpecAttrId` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `entity_spec_attr_id` (`entitySpecAttrId`)
);

CREATE TABLE IF NOT EXISTS `entity_spec_type` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(255) NOT NULL,
  `type` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
);
  
-- резервные копии скриптов
CREATE TABLE IF NOT EXISTS `script_backup` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(255) default NULL,
  `script_id` int(11) NOT NULL,
  `script` text,
  `user_id` int(11) NOT NULL,
  `change_time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `comment` varchar(255) default '',
  `type` tinyint(4) NOT NULL,
  PRIMARY KEY  (`id`)
);

-- уведомления
CREATE TABLE IF NOT EXISTS `contract_notification` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `cid` int(10) unsigned NOT NULL,
  `subject` varchar(255) NOT NULL,
  `message` text NOT NULL,
  `dt` datetime NOT NULL,
  `is_read` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
);

-- сообщения пользователям биллинга 
CREATE TABLE IF NOT EXISTS `message_for_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date_from` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `date_to` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `text` text NOT NULL,
  `gr` varchar(100) NOT NULL,
  `title` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
);

ALTER TABLE `message_for_users` MODIFY COLUMN `gr` VARCHAR(100) NOT NULL;
ALTER TABLE `message_for_users` ADD COLUMN `users` VARCHAR(100)  NOT NULL;

INSERT INTO installed_modules(name, title, type) VALUES('update_lib','UpdateLibraries','update_lib');

-- чтобы большие рассылки влезали  
ALTER TABLE scheduled_task_run MODIFY COLUMN `data` LONGBLOB  DEFAULT NULL;

CREATE TABLE IF NOT EXISTS `script_classes` (
  `name` varchar(255) NOT NULL,
  `data` blob NOT NULL,
  PRIMARY KEY (`name`)
);

CREATE TABLE IF NOT EXISTS `script_classes_ifaces` (
  `name` varchar(255) NOT NULL,
  `iface` varchar(255) NOT NULL,
  PRIMARY KEY (`name`,`iface`)
);

CREATE TABLE IF NOT EXISTS `global_script_link` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `class_name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `event_script_link` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `class_name` varchar(255) NOT NULL,
  `event_key` varchar(255) NOT NULL,
  `script_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
);

ALTER TABLE `script_classes` ADD `last_mod` BIGINT(20) NOT NULL DEFAULT 0;

ALTER TABLE `user_group` DROP INDEX `gr_code`, DROP INDEX `gr_code_2`;
ALTER TABLE `contract_logon_last` DROP INDEX `new_index`;

ALTER TABLE `contract_parameter_type_2` ADD COLUMN `format_key` VARCHAR(50) DEFAULT NULL;
ALTER TABLE `contract_parameter_type_2` MODIFY COLUMN `format_key` VARCHAR(50) DEFAULT NULL;
ALTER TABLE `object_param_value_address` ADD COLUMN `format_key` VARCHAR(50) DEFAULT NULL;

CREATE TABLE IF NOT EXISTS `address_config` (
  `table_id` varchar(50) NOT NULL,
  `record_id` int(11) NOT NULL,
  `key` varchar(50) NOT NULL,
  `value` text NOT NULL,
  PRIMARY KEY (`table_id`,`record_id`,`key`)
);

-- запросы из-за того, что события в 5.2 первоначально были в 'ru.bitel.bgbilling.kernel.balance.server.event
UPDATE script_event_type SET event_id=REPLACE(event_id, 'ru.bitel.bgbilling.kernel.balance.server.event', 'ru.bitel.bgbilling.kernel.contract.balance.server.event' );
UPDATE script_function_event_type SET event_id=REPLACE(event_id, 'ru.bitel.bgbilling.kernel.balance.server.event', 'ru.bitel.bgbilling.kernel.contract.balance.server.event' );

ALTER TABLE tariff_option ADD COLUMN `description` TEXT NOT NULL;
ALTER TABLE tariff_option_activate_mode ADD COLUMN `title` VARCHAR(255) NOT NULL DEFAULT '';

ALTER TABLE `payment_register` ADD COLUMN `errorCount` INT NOT NULL DEFAULT '-1' AFTER `count`;

ALTER TABLE `contract_parameter_type_phone_item` MODIFY COLUMN `phone` VARCHAR(14) DEFAULT NULL;

ALTER TABLE `tariff_option` ADD COLUMN `hideForWeb` SMALLINT NOT NULL DEFAULT '0'  AFTER `contract_groups` ,
ADD COLUMN `hideForWebContractGroups` BIGINT NOT NULL DEFAULT '0'  AFTER `hideForWeb` ,
ADD COLUMN `hideForWebContractGroupsMode` SMALLINT NOT NULL DEFAULT '0'  AFTER `hideForWebContractGroups` ;


ALTER TABLE `scheduled_tasks` ADD COLUMN `class` VARCHAR(255) NOT NULL AFTER `class_id`;
ALTER TABLE `scheduled_tasks` ADD COLUMN `module_id` VARCHAR(5) NOT NULL DEFAULT '0' AFTER `class`;

ALTER TABLE `contract_parameter_type_3` MODIFY COLUMN `email` VARCHAR(400) DEFAULT NULL;

ALTER TABLE `tariff_option_activate_mode` MODIFY COLUMN `period_mode` int(11) NOT NULL;

ALTER TABLE `entity_attr_address` ADD COLUMN `format_key` VARCHAR(45) NULL DEFAULT '';

ALTER TABLE `entity_attr_address` CHANGE COLUMN `format_key` `format_key` VARCHAR(45) NULL DEFAULT '';

ALTER TABLE `entity_spec` ADD COLUMN `entityTitleMacros` VARCHAR(100) NOT NULL DEFAULT '';

ALTER TABLE `script_backup` ADD INDEX `script_type`(`script_id`, `type`);

UPDATE setup SET id='closed_date_1' WHERE id='1';

ALTER TABLE `service` ADD COLUMN `unit` INT NOT NULL DEFAULT 0  AFTER `isusing`;

ALTER TABLE contract_logon_ok MODIFY COLUMN ip VARCHAR(40) NOT NULL DEFAULT '';

ALTER TABLE contract_logon_error MODIFY COLUMN ip VARCHAR(40) NOT NULL DEFAULT '';

CREATE TABLE IF NOT EXISTS `tariff_label` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) NOT NULL DEFAULT '0',
  `title` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `tariff_label_link` (
  `tariff_id` int(11) NOT NULL DEFAULT '0',
  `label_id` int(11) NOT NULL DEFAULT '0',
  KEY `tariff_id` (`tariff_id`,`label_id`)
);

-- 6.1

-- резервы
CREATE TABLE IF NOT EXISTS `contract_reserve` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `cid` int(10) NOT NULL,
  `typeId` int(11) NOT NULL,
  `sum` decimal(10,2) NOT NULL,
  `dateCreate` datetime NOT NULL,
  `dateTo` datetime DEFAULT NULL,
  `dateClose` datetime DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

-- таблица с итоговым значением резервов(при каждом добавлении и закрытие резервов - пересчитывается)
CREATE TABLE IF NOT EXISTS `contract_reserve_total` (
  `cid` int(11) NOT NULL,
  `sum` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`cid`)
);

-- типы резервов
CREATE TABLE IF NOT EXISTS `contract_reserve_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(45) NOT NULL,
  `used` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

-- Добавлен флаг указывающий что расход является возвратом. 
ALTER TABLE `contract_charge` ADD COLUMN `payback` TINYINT(1) NOT NULL DEFAULT 0 ;
-- И для типа
ALTER TABLE `contract_charge_types` ADD COLUMN `payback` TINYINT(1) NOT NULL DEFAULT 0 ;

CREATE TABLE IF NOT EXISTS `inv_product` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `contractId` int(11) NOT NULL,
  `accountId` int(11) NOT NULL,
  `productSpecId` int(11) NOT NULL,
  `timeFrom` datetime NOT NULL,
  `timeTo` datetime DEFAULT NULL,
  `activationModeId` int(11) NOT NULL,
  `activationTime` datetime NOT NULL,
  `deactivationTime` datetime DEFAULT NULL,
  `userId` int(11) NOT NULL,
  `comment` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `inv_product_spec` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `entityId` int(11) NOT NULL,
  `moduleId` int(11) NOT NULL,
  `parentId` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `tariffIds` varchar(255) DEFAULT NULL,
  `contractGroups` varchar(255) DEFAULT NULL,
  `depends` varchar(255) DEFAULT NULL,
  `incompatible` varchar(255) DEFAULT NULL,
  `activationModeIds` varchar(255) DEFAULT NULL,
  `dateFrom` date DEFAULT NULL,
  `dateTo` date DEFAULT NULL,
  `status` tinyint(4) NOT NULL,
  `hideForCustomer` tinyint(4) NOT NULL,
  `hideForContractGroups` bigint(20) NOT NULL DEFAULT '0',
  `hideForContractGroupsMode` tinyint(4) NOT NULL DEFAULT '0',
  `activationByCustomer` tinyint(4) NOT NULL,
  `deactivationByCustomer` tinyint(4) NOT NULL,
  `comment` text NOT NULL,
  `description` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `moduleId` (`moduleId`),
  KEY `date` (`dateFrom`,`dateTo`),
  KEY `parent` (`parentId`)
);

CREATE TABLE IF NOT EXISTS `inv_product_spec_activation_mode` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `productSpecId` int(11) NOT NULL,
  `title` varchar(100) DEFAULT NULL,
  `dateFrom` date DEFAULT NULL,
  `dateTo` date DEFAULT NULL,
  `periodMode` int(11) NOT NULL,
  `periodAmount` int(11) NOT NULL,
  `deactivationMode` int(11) NOT NULL,
  `reactivationMode` int(11) NOT NULL,
  `chargeTypeId` int(11) NOT NULL,
  `chargeAmount` decimal(10,0) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `productSpec` (`productSpecId`),
  KEY `period` (`dateFrom`,`dateTo`)
);

CREATE TABLE IF NOT EXISTS `inv_product_spec_service_link` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `productSpecId` int(11) NOT NULL,
  `serviceSpecId` int(11) NOT NULL,
  `dateFrom` date DEFAULT NULL,
  `dateTo` date DEFAULT NULL,
  `comment` text,
  PRIMARY KEY (`id`),
  KEY `product` (`productSpecId`),
  KEY `service` (`serviceSpecId`)
);

CREATE TABLE IF NOT EXISTS `inv_service_spec` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `moduleId` int(11) NOT NULL,
  `parentId` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `identifier` varchar(255) NOT NULL,
  `dateFrom` date DEFAULT NULL,
  `dateTo` date DEFAULT NULL,
  `comment` text NOT NULL,
  `description` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `moduleId` (`moduleId`),
  KEY `date` (`dateFrom`,`dateTo`),
  KEY `parent` (`parentId`)
);

CREATE TABLE IF NOT EXISTS `inv_product_period` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `contractId` int(11) NOT NULL,
  `accountId` int(11) NOT NULL,
  `productSpecId` int(11) NOT NULL,
  `productId` int(11) NOT NULL,
  `timeFrom` datetime NOT NULL,
  `timeTo` datetime DEFAULT NULL,
  KEY `id` (`id`),
  KEY `contract` (`contractId`),
  KEY `account` (`accountId`),
  KEY `product` (`productId`),
  KEY `period` (`timeFrom`,`timeTo`)
)
/*!50100 PARTITION BY HASH (contractId)
PARTITIONS 8 */;

ALTER TABLE `inv_product_period` ADD COLUMN `flags` INT NOT NULL DEFAULT 0  AFTER `timeTo` , 
ADD COLUMN `version` INT NOT NULL DEFAULT 0  AFTER `flags` ;

ALTER TABLE `inv_product_spec` ADD COLUMN `periodic` TINYINT(4) NOT NULL DEFAULT '1'  AFTER `parentId` ;

ALTER TABLE `inv_product_spec` ADD COLUMN `identifier` varchar(255) NOT NULL DEFAULT '' AFTER `title` ;

ALTER TABLE `inv_product` ADD COLUMN `description` varchar(100) NOT NULL DEFAULT '' AFTER `comment` ;

CREATE TABLE IF NOT EXISTS `inv_service` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `contractId` int(11) NOT NULL,
  `accountId` int(11) NOT NULL,
  `productId` int(11) NOT NULL,
  `serviceSpecId` int(11) NOT NULL,
  `timeFrom` datetime NOT NULL,
  `timeTo` datetime DEFAULT NULL,
  `deviceState` tinyint(4) NOT NULL,
  PRIMARY KEY (`id`,`contractId`),
  KEY `contractId` (`contractId`),
  KEY `accountId` (`accountId`),
  KEY `productId` (`productId`),
  KEY `serviceSpecId` (`serviceSpecId`)
) /*!50100 PARTITION BY HASH (contractId)
PARTITIONS 16 */;

ALTER TABLE `inv_service` ADD COLUMN `deviceState` TINYINT NOT NULL  AFTER `timeTo`;

ALTER TABLE `inv_product` ADD COLUMN `deviceState` TINYINT NOT NULL  AFTER `userId`;

ALTER TABLE `inv_service_spec` ADD COLUMN `entityId` INT NOT NULL  AFTER `id` 
, ADD INDEX `entity` (`entityId` ASC);

ALTER TABLE `inv_product_spec`  ADD INDEX `entity` (`entityId` ASC);

ALTER TABLE `inv_product_period` ADD COLUMN `activationTime` DATETIME NOT NULL  AFTER `productId` ;

UPDATE `inv_product_period` SET activationTime=timeFrom WHERE activationTime IS NULL OR activationTime='0000-00-00 00:00:00';

-- переименования классов задач

UPDATE scheduled_class SET class='ru.bitel.bgbilling.kernel.task.server.task.TaskUpdateAddressDirectory' WHERE class='ru.bitel.bgbilling.kernel.task.server.TaskUpdateAddressDirectory';

-- 6.0

CREATE TABLE IF NOT EXISTS `web_menu` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `default` tinyint(1) NOT NULL DEFAULT '0',
  `title` varchar(255) NOT NULL DEFAULT '',
  `data` text NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `contract_web_menu` (
  `contract_id` int(11) NOT NULL DEFAULT '0',
  `web_menu_id` int(11) NOT NULL DEFAULT '0',
  KEY `contract_id` (`contract_id`),
  KEY `web_menu_id` (`web_menu_id`)
);

ALTER TABLE `inv_product` ADD COLUMN `activationPrice` DECIMAL(10,5) NULL  AFTER `activationTime` ;

ALTER TABLE `script_classes` CHANGE COLUMN `data` `data` MEDIUMBLOB NOT NULL;

ALTER TABLE `inv_product` ADD COLUMN `deviceProductId` VARCHAR(100) NULL DEFAULT NULL  AFTER `userId` ;

ALTER TABLE `contract_parameters_pref` CHANGE COLUMN `history` `flags` TINYINT(3) NOT NULL DEFAULT 0;

ALTER TABLE `inv_product_period` ADD COLUMN `prolongationTime` DATETIME NOT NULL  AFTER `timeTo` ;

ALTER TABLE `object_param` CHANGE COLUMN `history` `flags` TINYINT(4) DEFAULT 0;

ALTER TABLE `bgs_group` ADD COLUMN `opids` VARCHAR(255) DEFAULT NULL AFTER `pids`;

ALTER TABLE `user` ADD COLUMN `opids` VARCHAR(255) DEFAULT NULL AFTER `pids`;

--

CREATE TABLE IF NOT EXISTS `contract_parameter_type_multilist` (
  `cid` int(11) NOT NULL,
  `pid` int(11) NOT NULL,
  `val` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`cid`,`pid`)
);
  
CREATE TABLE IF NOT EXISTS `contract_parameter_type_multilist_values` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pid` int(11) NOT NULL,
  `title` varchar(100) NOT NULL,
  PRIMARY KEY (`id`) );
  
CREATE TABLE IF NOT EXISTS `contract_parameter_type_multilist_log` (
  `cid` int(11) NOT NULL,
  `pid` int(11) NOT NULL,
  `val` varchar(255) DEFAULT NULL,
  `dt_change` datetime NOT NULL,
  `user_id` int(11) NOT NULL,
  KEY `cid_pid` (`cid`,`pid`)
);
  
CREATE TABLE IF NOT EXISTS `contract_parameter_type_multilist_item` (
  `cid` int(11) NOT NULL,
  `pid` int(11) NOT NULL,
  `val` int(11) NOT NULL,
  `custom_value` varchar(100) NOT NULL DEFAULT '',
  PRIMARY KEY (`cid`,`pid`,`val`,`custom_value`)
);

ALTER TABLE `contract_group` ADD COLUMN `comment` varchar(255) NOT NULL DEFAULT '';

ALTER TABLE `tariff_option_activate_mode` ADD COLUMN `delete_mode` INT NOT NULL DEFAULT 0  AFTER `reactivation_mode` ,

ADD COLUMN `delete_charge_mode` INT NULL DEFAULT 1  AFTER `delete_mode`;
 
ALTER TABLE `tariff_plan` ADD COLUMN `title_web` varchar(255) DEFAULT NULL AFTER `title`;

-- 6.2

CREATE TABLE IF NOT EXISTS `contract_pattern_named_numbers` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(45) NOT NULL,
  `ln` INT UNSIGNED NOT NULL,
  `comment` VARCHAR(90) NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `title_UNIQUE` (`title` )
);

CREATE TABLE IF NOT EXISTS `contract_payment_deleted` (
  `id` int(11) NOT NULL DEFAULT '0',
  `dt` date NOT NULL DEFAULT '0000-00-00',
  `cid` int(10) unsigned NOT NULL DEFAULT '0',
  `pt` int(10) unsigned NOT NULL DEFAULT '0',
  `uid` int(11) NOT NULL DEFAULT '0',
  `summa` decimal(10,2) NOT NULL,
  `comment` varchar(200) NOT NULL DEFAULT '',
  `lm` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `time_change` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `pt_dt` (`pt`,`dt`),
  KEY `dt_cid` (`dt`,`cid`),
  KEY `uid` (`uid`),
  KEY `cid_dt` (`cid`,`dt`)
);

CREATE TABLE IF NOT EXISTS `contract_autopayment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `contract_id` int(11) NOT NULL DEFAULT '0',
  `module_id` int(11) NOT NULL DEFAULT '0',
  `date_from` datetime DEFAULT NULL,
  `date_to` datetime DEFAULT NULL,
  `user_from` int(11) NOT NULL DEFAULT '-2',
  `user_to` int(11) NOT NULL DEFAULT '-2',
  PRIMARY KEY (`id`),
  KEY `cid_mid` (`contract_id`,`module_id`)
);

CREATE TABLE IF NOT EXISTS `contract_module_config` (
  `contract_id` int(11) NOT NULL DEFAULT '0',
  `module_id` int(11) NOT NULL DEFAULT '0',
  `key` varchar(50) NOT NULL DEFAULT '',
  `value` text NOT NULL,
  PRIMARY KEY (`contract_id`,`module_id`,`key`),
  KEY `module_id_key` (`module_id`,`key`)
);

CREATE TABLE IF NOT EXISTS `contract_plugin_config` (
  `contract_id` int(11) NOT NULL DEFAULT '0',
  `plugin_id` int(11) NOT NULL DEFAULT '0',
  `key` varchar(50) NOT NULL DEFAULT '',
  `value` text NOT NULL,
  PRIMARY KEY (`contract_id`,`plugin_id`,`key`),
  KEY `plugin_id_key` (`plugin_id`,`key`)
);

ALTER TABLE `inv_product_spec` ADD COLUMN `notRealtime` TINYINT(4) NOT NULL DEFAULT '0'  AFTER `periodic` ;
ALTER TABLE `contract_pattern_named_numbers` ADD COLUMN `count_number` INT DEFAULT '-1' AFTER `ln`;
ALTER TABLE `contract_payment` DROP COLUMN `time_change`;
ALTER TABLE `contract_payment_deleted` DROP COLUMN `time_change`;
ALTER TABLE `inv_product_spec` ADD `priority` INT  NOT NULL DEFAULT 0 AFTER `notRealtime`;
ALTER TABLE `contract_limit_period` ADD COLUMN `status` VARCHAR(6) DEFAULT 'on';
ALTER TABLE `contract_payment_deleted` ADD INDEX `id` (`id`);
ALTER TABLE `contract_payment_deleted` DROP PRIMARY KEY;

-- 7.1

CREATE TABLE IF NOT EXISTS `scheduled_task_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `task_id` int(11) NOT NULL,
  `start` datetime NOT NULL,
  `finish` datetime NOT NULL,
  PRIMARY KEY (`id`)
);

ALTER TABLE `scheduled_task_log` CHANGE COLUMN `start` `start` BIGINT NOT NULL , CHANGE COLUMN `finish` `finish` BIGINT NOT NULL ;
ALTER TABLE `contract_status_log` ADD INDEX `cid` (`cid`);
ALTER TABLE `tariff_plan`	ADD COLUMN `config` MEDIUMTEXT NULL AFTER `tree_id`;

CREATE TABLE IF NOT EXISTS `contract_history_open_log` (
  `user_id` int(11) NOT NULL DEFAULT '0',
  `contract_id` int(11) NOT NULL,
  `last_open` datetime NOT NULL,
  PRIMARY KEY (`user_id`,`contract_id`),
  KEY `user_id_last_open` (`user_id`,`last_open`),
  KEY `contract_id` (`contract_id`)
);

ALTER TABLE `inv_product_spec` CHANGE COLUMN `tariffIds` `tariffIds` varchar(1024) DEFAULT NULL;
ALTER TABLE `inv_product_spec` CHANGE COLUMN `contractGroups` `contractGroups` varchar(1024) DEFAULT NULL;
ALTER TABLE `inv_product_spec` CHANGE COLUMN `depends` `depends` varchar(1024) DEFAULT NULL;
ALTER TABLE `inv_product_spec` CHANGE COLUMN `incompatible` `incompatible` varchar(1024) DEFAULT NULL;

CREATE TABLE IF NOT EXISTS `domain` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `parentId` INT NOT NULL,
  `title` VARCHAR(150) NOT NULL,
  `comment` TEXT NOT NULL,
  PRIMARY KEY (`id`)
);

ALTER TABLE `contract` ADD COLUMN `domainId` INT NOT NULL DEFAULT 0 AFTER `id`;
ALTER TABLE `contract_pattern` ADD COLUMN `domainId` INT NOT NULL DEFAULT 0;
ALTER TABLE `user` ADD COLUMN `domainIds` VARCHAR(255) NOT NULL DEFAULT '';
ALTER TABLE `contract` ADD INDEX `domainId` (`domainId` ASC);
ALTER TABLE `user` CHANGE COLUMN `pids` `pids` VARCHAR(512) NULL DEFAULT NULL ;
ALTER TABLE `bgs_group` CHANGE COLUMN `pids` `pids` VARCHAR(512) NULL DEFAULT NULL ;
ALTER TABLE `scheduled_task_log` ADD `task_title` varchar(128) NULL;
ALTER TABLE `contract_delete_money` ADD COLUMN `params` MEDIUMTEXT NULL AFTER `comment`;
ALTER TABLE `contract_delete_time` ADD COLUMN `params` MEDIUMTEXT NULL AFTER `comment`;
ALTER TABLE `contract` CHANGE COLUMN `comment` `comment` VARCHAR(200) NOT NULL DEFAULT '' ;

-- 7.2 ------------------------------
CREATE TABLE IF NOT EXISTS `contract_label` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) NOT NULL DEFAULT 0,
  `title` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `contract_label_link` (
  `contract_id` int(11) NOT NULL DEFAULT 0,
  `label_id` int(11) NOT NULL DEFAULT 0,
  KEY `contract_id` (`contract_id`,`label_id`)
);

ALTER TABLE `contract_label_link` CHANGE COLUMN `tariff_id` `contract_id` int(11) NOT NULL DEFAULT 0;
ALTER TABLE `contract` CHANGE COLUMN `comment` `comment` VARCHAR(200) NOT NULL DEFAULT '' ;
ALTER TABLE `contract_tree_link` ADD COLUMN `title_web` VARCHAR(255) NULL DEFAULT NULL, ADD COLUMN `config` MEDIUMTEXT NULL DEFAULT NULL ;
ALTER TABLE `module` DROP COLUMN `config_id`;
ALTER TABLE `contract_parameter_type_multilist_values` CHANGE `id` `id` INT NOT NULL AUTO_INCREMENT;
ALTER TABLE `user` ADD COLUMN `personal_action` int(11) NOT NULL DEFAULT 0;

CREATE TABLE IF NOT EXISTS `license` (
  `id` int(11) NOT NULL DEFAULT 0,
  `active` tinyint(1) NOT NULL DEFAULT 0,
  `dt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `count` int(11) NOT NULL DEFAULT 0,
  `hash` varchar(65) NOT NULL DEFAULT '',
  `encryptData` text NOT NULL,
  `plainData` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `hash` (`hash`),
  KEY `dt` (`dt`)
);

CREATE TABLE IF NOT EXISTS `updater_task` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `task_class` varchar(255) NOT NULL DEFAULT '',
  `title` varchar(255) NOT NULL DEFAULT '',
  `comment` varchar(255) NOT NULL DEFAULT '',
  `run_date` datetime NOT NULL DEFAULT current_timestamp(),
  `status` varchar(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `updater_task_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `task_id` int(11) NOT NULL DEFAULT 0,
  `run_date` datetime NOT NULL DEFAULT current_timestamp(),
  `status` varchar(10) NOT NULL DEFAULT '',
  `user_id` int(11) NOT NULL DEFAULT 0,
  `data` text NOT NULL,
  PRIMARY KEY (`id`)
);

ALTER TABLE inv_product ADD KEY `contractId` (`contractId`);

ALTER TABLE `scheduled_task_log` ADD KEY `start` (`start`), ADD KEY `task_id` (`task_id`,`start`);

CREATE TABLE IF NOT EXISTS `contract_account_detail` (
  `contract_id` int(10) unsigned NOT NULL DEFAULT 0,
  `service_id` int(10) unsigned NOT NULL DEFAULT 0,
  `entity_id` int(10) unsigned NOT NULL DEFAULT 0,
  `date_from` datetime DEFAULT NULL,
  `date_to` datetime DEFAULT NULL,
  `title` varchar(255) NOT NULL DEFAULT '',
  `cost` decimal(15,2) DEFAULT 0,
  `amount` decimal(10,2) DEFAULT 0,
  `sum` decimal(15,2) DEFAULT 0,
   KEY `contract_service_period` (`contract_id`,`service_id`,`date_from`,`date_to`)
);

CREATE TABLE IF NOT EXISTS `contract_period` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `contract_id` int(11) NOT NULL DEFAULT 0,
  `date_from` datetime DEFAULT NULL,
  `date_to` datetime DEFAULT NULL,
  `generate_event` tinyint(4) DEFAULT 0,
  `user_id` int(11) NOT NULL DEFAULT 0,
  `job_id` bigint(20) DEFAULT 0,
  `lm` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `contract_id_date_from` (`contract_id`,`date_from`),
  KEY `date_to_generate_event` (`date_to`,`generate_event`)
);

CREATE TABLE IF NOT EXISTS `user_group_right` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) NOT NULL DEFAULT 0,
  `title` varchar(255) DEFAULT '',
  `module_id` int(11) NOT NULL DEFAULT 0,
  `type` tinyint(4) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `title` (`title`)
);

CREATE TABLE IF NOT EXISTS `user_right_rule` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `group_id` int(11) NOT NULL DEFAULT 0,
  `title` varchar(255) DEFAULT '',
  `module_id` int(11) NOT NULL DEFAULT 0,
  `type` tinyint(4) NOT NULL DEFAULT 0,
  `active` tinyint(4) NOT NULL DEFAULT 0,
  `rule` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `title` (`title`)
);

ALTER TABLE `contract` MODIFY `sub_list` mediumtext NOT NULL DEFAULT '' ;
ALTER TABLE `contract` MODIFY `crm_customer_id` int(11) NOT NULL DEFAULT 0;

ALTER TABLE `tariff_group_tariff` ADD COLUMN `comment` VARCHAR(500) DEFAULT NULL;
ALTER TABLE `tariff_plan` ADD COLUMN `comment` VARCHAR(500) DEFAULT NULL;

-- 8.1+

ALTER TABLE `contract_pattern` CHANGE COLUMN `groups` `contract_groups` bigint(20) NOT NULL DEFAULT 0;
ALTER TABLE `contract_tariff` ADD COLUMN `tariff_group_id` int(11) NOT NULL DEFAULT -1 AFTER `tpid`;
ALTER TABLE `tariff_group` ADD COLUMN `domain_id` int(11) NOT NULL DEFAULT 0 AFTER `pos`;
ALTER TABLE `inv_product_spec` ADD COLUMN `data` text NOT NULL;
ALTER TABLE `contract_label`  ADD COLUMN `type` int(11) NOT NULL DEFAULT 1;

CREATE TABLE IF NOT EXISTS `customer` (  
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `data` text NOT NULL,
    PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `customer_link` (
  `customer_id` int(10) unsigned NOT NULL,
  `contract_id` int(10) unsigned NOT NULL,
  `date_from` datetime NOT NULL,
  `date_to` datetime DEFAULT NULL,
  KEY `customer_id` (`customer_id`),
  KEY `contract_id` (`contract_id`),
  KEY `date_from` (`date_from`),
  KEY `date_to` (`date_to`)
);

CREATE TABLE IF NOT EXISTS `customer_log` (
  `customer_id` int(10) unsigned NOT NULL,
  `update_time` datetime NOT NULL,
  `user_id` int(10) unsigned NOT NULL,
  `field_key` varchar(255) NOT NULL,
  `field_value` varchar(255) NOT NULL,
  KEY `customer_id` (`customer_id`,`field_key`),
  KEY `update_time` (`update_time`)
);

ALTER TABLE `customer` ADD COLUMN `type` char(1) NOT NULL AFTER `id`;
ALTER TABLE `customer` ALTER COLUMN `type` SET DEFAULT 'f';
ALTER TABLE `customer` MODIFY COLUMN `data` MEDIUMTEXT NOT NULL DEFAULT '';
ALTER TABLE `customer_log` ADD COLUMN `old` tinyint(4) NOT NULL AFTER `user_id`;
ALTER TABLE `customer_log` ALTER COLUMN `old` SET DEFAULT 0;
ALTER TABLE `customer_log` ADD INDEX (`old`,`field_key`,`field_value`);
ALTER TABLE `inv_product_spec` ADD COLUMN `data` text NOT NULL;

CREATE TABLE IF NOT EXISTS `contract_search_implementation` ( 
	`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
	`title` varchar(500) DEFAULT NULL,
	`implClass` varchar(500) DEFAULT NULL,
	`moduleTitle` varchar(100) DEFAULT NULL,
	`moduleId` int(11) DEFAULT NULL,
	`enable` tinyint(4) DEFAULT NULL,
	PRIMARY KEY (`id`)
);

ALTER TABLE `contract_parameter_type_8_log` ADD COLUMN `title` varchar(255) DEFAULT NULL;
ALTER TABLE `tariff_plan` ADD COLUMN `contract_label_ids` varchar(255) DEFAULT '' AFTER `gr`;

UPDATE `scheduled_tasks` SET `class`='ru.bitel.bgbilling.kernel.contract.balance.server.task.Validator' WHERE `class`='ru.bitel.bgbilling.kernel.task.server.Validator';
UPDATE `scheduled_tasks` SET `class`='ru.bitel.bgbilling.kernel.script.server.task.GlobalScriptTimer' WHERE `class`='bitel.billing.server.script.global.bean.GlobalScriptTimer';

ALTER TABLE `service` ADD COLUMN `config` text NOT NULL;

ALTER TABLE `contract_label` ADD COLUMN `sort_position` INT NOT NULL DEFAULT 0;

ALTER TABLE `setup` MODIFY COLUMN `value` text DEFAULT NULL;

ALTER TABLE `user` ADD COLUMN `menu_id` int NOT NULL DEFAULT -1 AFTER `personal_action`;
ALTER TABLE `bgs_group` ADD COLUMN `menu_id` int NOT NULL DEFAULT -1 AFTER `cgr_mode`;

CREATE TABLE IF NOT EXISTS `bgs_menu` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `default` tinyint(1) NOT NULL DEFAULT 0,
  `title` varchar(255) NOT NULL DEFAULT '',
  `data` longtext NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
);

ALTER TABLE `service` ADD COLUMN `active_date_from` DATE DEFAULT NULL, ADD COLUMN `active_date_to` DATE DEFAULT NULL;

ALTER TABLE `contract_label` ADD COLUMN `comment` TEXT NULL;

ALTER TABLE `contract_limit_period` ADD KEY `contractId` (`cid`);

-- Заменены группы договоров на метки для тарифных опций
ALTER TABLE `tariff_option` ROW_FORMAT=DYNAMIC;
ALTER TABLE `tariff_option` ADD COLUMN `contract_labels` TEXT NOT NULL;
ALTER TABLE `tariff_option` ADD COLUMN `hide_for_web_contract_labels` TEXT NOT NULL;

-- Заменены группы договоров на метки дл продуктв ТВ
ALTER TABLE `inv_product_spec` ROW_FORMAT=DYNAMIC;
ALTER TABLE `inv_product_spec` ADD COLUMN `contractLabels` TEXT NOT NULL;
ALTER TABLE `inv_product_spec` ADD COLUMN `hideForContractLabels` TEXT NOT NULL;

CREATE TABLE IF NOT EXISTS `role` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(300) NOT NULL DEFAULT '',
    `menu_id` INT NOT NULL DEFAULT 0,
    `childs` TEXT NOT NULL,
    `permission_kit_ids` TEXT NOT NULL,
    PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `permission_kit` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(300) NOT NULL DEFAULT '',
  `contract_labels` TEXT NOT NULL,
  `domens` TEXT NOT NULL,
  `bgs_action_ids` TEXT NOT NULL,
  `bgs_group_action_ids` TEXT NOT NULL,
  `pids` VARCHAR(512) NOT NULL DEFAULT '',
  `contract_objects` VARCHAR(255) NOT NULL DEFAULT '',
  `description` TEXT NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `bgs_action_group` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(255) NOT NULL DEFAULT '',
  `bgs_action_ids` TEXT NOT NULL,
  PRIMARY KEY (`id`)
);

ALTER TABLE `user` ADD COLUMN `role_id` INT NOT NULL DEFAULT 0;
ALTER TABLE `bgs_user_action` ADD COLUMN `bgs_action_group_id` INTEGER NULL;
ALTER TABLE `contract_label` ADD COLUMN `forbidden` TINYINT NOT NULL DEFAULT 0;
ALTER TABLE `scheduled_tasks` ADD COLUMN `dependent_tasks` TEXT;

CREATE TABLE IF NOT EXISTS `tariff_vars` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tariff_id` int(11) NOT NULL,
  `type` varchar(50) NOT NULL,
  `module_id` int(11) NOT NULL,
  `title` varchar(250) NOT NULL,
  `default_value` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

ALTER TABLE tariff_vars CHANGE COLUMN `value` default_value varchar(255) NOT NULL;

CREATE TABLE IF NOT EXISTS `contract_tariff_vars` (
  `tariff_var_id` int(11) NOT NULL,
  `contract_tariff_id` int(11) NOT NULL,
  `value` varchar(255) NOT NULL
);

ALTER TABLE `contract_tariff_vars` CHANGE COLUMN traiff_var_id tariff_var_id int(11) NOT NULL;
ALTER TABLE `contract_tariff_vars` ADD UNIQUE `tariff_var_id` (`tariff_var_id`,`contract_tariff_id`);
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_2`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_3`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_4`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_5`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_6`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_7`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_8`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_9`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_10`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_11`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_12`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_13`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_14`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_15`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_16`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_17`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_18`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_19`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_20`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_21`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_22`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_23`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_24`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_25`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_26`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_27`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_28`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_29`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_30`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_31`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_32`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_33`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_34`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_35`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_36`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_37`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_38`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_39`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_40`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_41`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_42`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_43`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_44`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_45`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_46`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_47`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_48`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_49`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_50`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_51`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_52`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_53`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_54`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_55`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_56`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_57`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_58`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_59`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_60`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_61`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_62`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_63`;
ALTER TABLE `contract_tariff_vars` DROP INDEX `tariff_var_id_64`;
ALTER TABLE `inv_product_spec` ADD COLUMN `sort` int NOT NULL AFTER `parentId`;

ALTER TABLE `tariff_option_activate_mode` ADD COLUMN `period_activate` varchar(50) AFTER `period_col`;

CREATE TABLE IF NOT EXISTS `payment_info` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `payment_id` int(11) NOT NULL DEFAULT 0,
    `key` varchar(250),
    `value` varchar(1000),
    PRIMARY KEY  (`id`),
    FOREIGN KEY (`payment_id`) REFERENCES `contract_payment` (`id`) ON DELETE CASCADE
);

ALTER TABLE `payment_register_item` ADD COLUMN `extra_params` TEXT;
ALTER TABLE `user` MODIFY COLUMN `id` INT(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `role` MODIFY COLUMN `id` INT(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `contract_tariff_option` ADD COLUMN `option_comment` varchar(255) AFTER `deactivated_time`;

CREATE TABLE IF NOT EXISTS `mail_config` (
  `component_id` varchar(100) NOT NULL,
  `module_id` int(11) NOT NULL,
  `key` varchar(50) NOT NULL,
  `subject_mode` varchar(10) NOT NULL,
  `subject_custom` varchar(255) DEFAULT NULL,
  `pattern_mode` varchar(10) NOT NULL,
  `pattern_custom` mediumtext DEFAULT NULL,
  `handler_mode` varchar(10) NOT NULL,
  `handler_custom` varchar(255) DEFAULT NULL,
  `handler_params` text DEFAULT NULL,
  PRIMARY KEY (`component_id`,`module_id`,`key`)
);

CREATE TABLE IF NOT EXISTS `bgs_request_unregistration` (
  `module_id` varchar(50) DEFAULT NULL,
  `action` varchar(50) DEFAULT NULL,
  `service` varchar(50) DEFAULT NULL,
  `operation` varchar(50) DEFAULT NULL,
  `contract_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `last_time` datetime NOT NULL,
  UNIQUE KEY `action` (`action`,`service`,`operation`)
); 

ALTER TABLE `tariff_plan` ADD COLUMN `description` varchar(4096) DEFAULT NULL AFTER `comment`;
ALTER TABLE `contract_parameter_type_multilist_values` ADD COLUMN `inUse` int(1) NOT NULL DEFAULT 1 AFTER `title`;
ALTER TABLE `contract_parameter_type_7_values` ADD COLUMN `inUse` int(1) NOT NULL DEFAULT 1 AFTER `title`;
ALTER TABLE `inv_product_spec_activation_mode` ADD COLUMN `moduleId` int(11) NOT NULL DEFAULT 0 AFTER `id`;

CREATE TABLE IF NOT EXISTS `bgs_request_record` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `start_record_time` timestamp,
    `stop_record_time` timestamp null,
    `all_actions_count` int(11),
    `actions` text,
    `user_id` int(11),
    `title` varchar(255),
    PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `bgs_user_action_expression` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL DEFAULT '',
  `service_id` int(11) NOT NULL,
  `service_name` varchar(255) NOT NULL DEFAULT '',
  `service_operation` varchar(255) NOT NULL DEFAULT '',
  `service_expression` text NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
);

ALTER TABLE `bgs_user_action_expression` ADD COLUMN `component_id` varchar(255) NOT NULL DEFAULT '' AFTER `title`;

CREATE TABLE IF NOT EXISTS `customer_account` (
  `account` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` int(10) unsigned NOT NULL DEFAULT 0,
  `created` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `user_id` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`account`),
  KEY `customer_id` (`customer_id`)
);
ALTER TABLE `customer_link` ADD COLUMN `account` bigint unsigned NOT NULL DEFAULT 0 AFTER `contract_id`;

ALTER TABLE `tariff_vars` ADD COLUMN `up_id` int NOT NULL DEFAULT 0 AFTER `id`;
ALTER TABLE `tariff_vars` ADD COLUMN `top_id` int NOT NULL DEFAULT 0 AFTER `up_id`;
ALTER TABLE `tariff_vars` ADD COLUMN `parent_default_value` varchar(255) DEFAULT NULL AFTER `default_value`;

ALTER TABLE `contract_account_detail` CHANGE `title` `comment` varchar(255) NOT NULL DEFAULT '';

ALTER TABLE `contract_status` ADD COLUMN `object_id` int(11) NOT NULL DEFAULT 0 AFTER `cid`;
ALTER TABLE `contract_status` DROP INDEX `cid`;
ALTER TABLE `contract_status` ADD INDEX `cid_object_id` (`cid`,`object_id`);
ALTER TABLE `contract_status_log` ADD COLUMN `object_id` INT NOT NULL DEFAULT 0 AFTER `cid`;

ALTER TABLE `object` ADD COLUMN  `status` tinyint(4) NOT NULL DEFAULT -1 AFTER `cid`;
ALTER TABLE `object` ADD COLUMN `status_date` date DEFAULT NULL AFTER `status`;

ALTER TABLE `contract_account_detail` ADD COLUMN `object_id` int NOT NULL DEFAULT 0 AFTER `contract_id`;
ALTER TABLE `contract_account_detail` ADD COLUMN `entity_module_id` int NOT NULL DEFAULT 0 AFTER `service_id`;
ALTER TABLE `contract_account_detail` ADD COLUMN `entity_title` varchar(255) NOT NULL DEFAULT '' AFTER `entity_id`;

UPDATE scheduled_tasks SET class = 'ru.bitel.bgbilling.kernel.contract.balance.server.task.LimitRestorer' WHERE class = 'bitel.billing.server.contract.LimitRestorer';

ALTER TABLE `customer` ADD INDEX `type` (`type`);

CREATE TABLE IF NOT EXISTS `task_future` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `type` varchar(50) NOT NULL DEFAULT '',
  `start_time` timestamp NOT NULL,
  `module_id` int(11) NOT NULL DEFAULT 0,
  `entity_type` varchar(50) NOT NULL DEFAULT '',
  `entity_id` int(11) NOT NULL DEFAULT 0,
  `data` text NOT NULL,
  `create_time` timestamp NOT NULL,
  `create_user_id` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `start_time` (`start_time`),
  KEY `type-entity_type` (`type`,`entity_type`),
  KEY `entity_id` (`entity_id`)
);

ALTER TABLE `address_city` ADD COLUMN `title_prefix` varchar(50) NOT NULL DEFAULT '' AFTER `country_id`;
ALTER TABLE `address_city` ADD COLUMN `title_suffix` varchar(50) NOT NULL DEFAULT '' AFTER `title`;
ALTER TABLE `address_street` ADD COLUMN `title_prefix` varchar(50) NOT NULL DEFAULT '' AFTER `id`;
ALTER TABLE `address_street` ADD COLUMN `title_suffix` varchar(50) NOT NULL DEFAULT '' AFTER `title`;
ALTER TABLE `address_quarter` ADD COLUMN `title_prefix` varchar(50) NOT NULL DEFAULT '' AFTER `id`;
ALTER TABLE `address_quarter` ADD COLUMN `title_suffix` varchar(50) NOT NULL DEFAULT '' AFTER `title`;
ALTER TABLE `address_area` ADD COLUMN `title_prefix` varchar(50) NOT NULL DEFAULT '' AFTER `id`;
ALTER TABLE `address_area` ADD COLUMN `title_suffix` varchar(50) NOT NULL DEFAULT '' AFTER `title`;

ALTER TABLE `tariff_option` ADD COLUMN `config` longtext NOT NULL AFTER `description`;

CREATE TABLE IF NOT EXISTS `house_coordinates` (
    `house_id` int(10) unsigned NOT NULL DEFAULT 0,
    `latitude` DECIMAL(10,7),
    `longitude`DECIMAL(10,7),
    KEY (`house_id`)
);

ALTER TABLE `contract_pattern` MODIFY COLUMN `tpid` text NOT NULL;

CREATE TABLE IF NOT EXISTS `counter_sequence` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL DEFAULT '',
  `counter` bigint(20) NOT NULL DEFAULT 0,
  `sequence_type` varchar(20) NOT NULL,
  `sequence_mode` varchar(20) NOT NULL,
  `reset_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `sequence_type` (`sequence_type`)
);

CREATE TABLE IF NOT EXISTS `counter_generator` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL DEFAULT '',
  `pattern` varchar(255) NOT NULL DEFAULT '',
  `sequence_id` int(11) NOT NULL DEFAULT 0,
  `generator_type` varchar(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `generator_type` (`generator_type`)
);

ALTER TABLE `contract_pattern` ADD COLUMN `name_pattern_id` int(11) NOT NULL DEFAULT 0 AFTER `name_pattern`;
ALTER TABLE `tariff_option_activate_mode` MODIFY `period_activate` varchar(100) DEFAULT NULL;

CREATE TABLE IF NOT EXISTS `tariff_vars_value` (
  `tariff_var_id` int(11) NOT NULL DEFAULT 0,
  `date_from` date DEFAULT NULL,
  `date_to` date DEFAULT NULL,
  `value` text NOT NULL DEFAULT '',
  KEY `tariff_var_id` (`tariff_var_id`,`date_from`)
);

ALTER TABLE `tariff_vars_value` MODIFY `value` text DEFAULT NULL;

CREATE TABLE IF NOT EXISTS `xsl_pattern` (
  `name` varchar(50) NOT NULL,
  `update_time` bigint NOT NULL DEFAULT 0,
  `data` mediumtext NOT NULL,
  PRIMARY KEY (`name`)
);

ALTER TABLE `contract_payment` ADD COLUMN `data` text NOT NULL AFTER `comment`;
ALTER TABLE `contract_payment` ADD COLUMN `module_id` int(11) NULL AFTER `uid`;
ALTER TABLE `contract_payment` ADD COLUMN `transaction_id` varchar(20) NULL AFTER `module_id`; 
ALTER TABLE `contract_payment` ADD CONSTRAINT `module_transaction` UNIQUE (module_id,transaction_id);

ALTER TABLE `contract_payment_deleted` ADD COLUMN `data` text NOT NULL AFTER `comment`;
ALTER TABLE `contract_payment_deleted` ADD COLUMN `module_id` int(11) NULL AFTER `uid`;
ALTER TABLE `contract_payment_deleted` ADD COLUMN `transaction_id` varchar(20) NULL AFTER `module_id`;

ALTER TABLE `contract_account_detail` ADD INDEX (`contract_id`);

ALTER TABLE `contract_pattern` ADD COLUMN `hidden` tinyint(1) NOT NULL DEFAULT 0  AFTER `domainId`;

ALTER TABLE user_tables CHANGE COLUMN userId user_id int(11) DEFAULT NULL;
ALTER TABLE task_future ADD COLUMN task_status tinyint NOT NULL DEFAULT 1 AFTER type;
ALTER TABLE contract_password_once ADD COLUMN verification_counter int(11) DEFAULT 0 AFTER `password`;

CREATE TABLE IF NOT EXISTS `contract_remove_rule` ( `id` int(11) NOT NULL auto_increment,
                                                      `title` varchar(512),
                                                      `comment` varchar(512),
                                                      `date_from` datetime,
                                                      `date_to` datetime,
                                                      `labels` text,
                                                      `statuses` text,
                                                      `rule_type` int(11),
                                                      `balance` decimal(10,2),
                                                      `type_remove_by_time` int(11),
                                                      `time_count` int(11),
                                                      PRIMARY KEY  (`id`)
);

ALTER TABLE address_house ADD COLUMN pod_count int(11) NOT NULL DEFAULT 1 AFTER box_index;
ALTER TABLE address_house CHANGE COLUMN streetid street_id int(11) NOT NULL DEFAULT 0;
ALTER TABLE customer_log ADD COLUMN field_uuid varchar(36) DEFAULT '' AFTER field_key;
ALTER TABLE contract_account_detail ADD COLUMN contract_tariff_id int(11) NOT NULL DEFAULT 0 AFTER service_id;
ALTER TABLE contract_payment ADD COLUMN create_datetime datetime DEFAULT NULL;
ALTER TABLE contract_payment_deleted ADD COLUMN create_datetime datetime DEFAULT NULL;
ALTER TABLE contract_tariff_vars ADD KEY contract_tariff_id (contract_tariff_id);

-- ---------------------------------------------------------------------------------------------
-- Пожалуйста, добавляйте инструкции после комментария той версии, в которую они попадают!!!! 
-- Не забывайте докинуть дамп таблиц в kernel/dump.bat!										   	
-- ---------------------------------------------------------------------------------------------

-- описание событий в ../kernel/kernel_event.sql
