-- #BLOCK#
DELETE FROM script_event_type WHERE mid="p$mid";
INSERT INTO script_event_type ( mid, event_id, title ) VALUES( 'p$mid', 6, " ");
INSERT INTO script_event_type ( mid, event_id, title ) VALUES( 'p$mid', 7, " ");
INSERT INTO script_event_type ( mid, event_id, title ) VALUES( 'p$mid', 8, "   ");
INSERT INTO script_event_type ( mid, event_mode, event_id, title ) VALUES( 'p$mid', 0, 9, "  ");
INSERT INTO script_event_type ( mid, event_id, event_mode, title ) VALUES( 'p$mid', 'ru.bitel.bgbilling.plugins.crm.server.bean.event.RegisterWorkStatusChangedEvent', 0, "  ");
INSERT INTO script_event_type ( mid, event_id, title ) VALUES( 'p$mid', 10, "   ");
INSERT INTO script_event_type ( mid, event_id, title ) VALUES( 'p$mid', 11, "   ");
INSERT INTO script_event_type ( mid, event_id, title ) VALUES( 'p$mid', 12, "/ ");
INSERT INTO script_event_type ( mid, event_id, title ) VALUES( 'p$mid', 14, " /");
-- #ENDB#

CREATE TABLE register_problem (
  `id` int(11) NOT NULL auto_increment,
  `open_dt` datetime default NULL,
  `open_uid` int(11) NOT NULL default '0',
  `status_dt` datetime default NULL,
  `status_uid` int(11) NOT NULL default '0',
  `close_dt` datetime default NULL,
  `close_uid` int(11) NOT NULL default '0',
  `status` int(11) NOT NULL default '0',
  `urgency` int(11) NOT NULL default '0',
  `subject` int(11) NOT NULL default '0',
  `comment` text NOT NULL,
  `gr` int(11) NOT NULL default '0',
  `cat` int(11) NOT NULL default '0',
  `pp` int(11) NOT NULL default '0',
  `resolution` text NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `open_dt` (`open_dt`),
  KEY `status` (`status`),
  KEY `subject` (`subject`),
  KEY `gr` (`gr`)
);

CREATE TABLE  register_problem_log (
  `id` int(11) NOT NULL default '0',
  `change_dt` datetime default NULL,
  `change_uid` int(11) NOT NULL default '0',
  `status_dt` datetime default NULL,
  `status_uid` int(11) NOT NULL default '0',
  `status` int(11) NOT NULL default '0',
  `urgency` int(11) NOT NULL default '0',
  `subject` int(11) NOT NULL default '0',
  `comment` text NOT NULL,
  `gr` int(11) NOT NULL default '0',
  `cat` int(11) NOT NULL default '0',
  `pp` int(11) NOT NULL default '0',
  `resolution` text NOT NULL,
  KEY `id_change_dt` (id, change_dt)
);

CREATE TABLE `register_call` (
  `id` int(11) NOT NULL auto_increment,
  `cid` int(11) NOT NULL,
  `oid` int(11) NOT NULL,
  `uid` int(11) NOT NULL default '0',
  `time` datetime default NULL,
  `sid` int(3) NOT NULL default '0',
  `gid` int(11) NOT NULL default '0',
  `rpid` int(11) NOT NULL default '0',
  `rtid` int(11) NOT NULL default '0',
  `comment` text NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `time` (`time`),
  KEY `cid` (`cid`),
  KEY `rpid` (`rpid`),
  KEY `rtid` (`rtid`)
);

CREATE TABLE `register_task` (
  `id` int(11) NOT NULL auto_increment,
  `cid` int(11) NOT NULL default '0',
  `aobject_id` int(11) NOT NULL default '0',
  `apid` int(11) NOT NULL default '0',
  `type` int(11) NOT NULL default '0',
  `open_dt` datetime default NULL,
  `open_uid` int(11) NOT NULL default '0',
  `accept_dt` datetime default NULL,
  `accept_uid` int(11) NOT NULL default '0',
  `close_dt` datetime default NULL,
  `close_uid` int(11) NOT NULL default '0',
  `lm_dt` datetime default NULL,
  `lm_uid` int(10) NOT NULL default '0',
  `status` int(11) NOT NULL default '0',
  `processed` tinyint(2) NOT NULL default '0',
  `target_dt` date default NULL,
  `comment` text NOT NULL,
  `gr` int(11) NOT NULL default '0',
  `resolution` text NOT NULL,
  `prid` int(11) NOT NULL default '0',
  `execute_dt` date default NULL,
  PRIMARY KEY  (`id`),
  KEY `cid` (`cid`),
  KEY `open_dt` (`open_dt`),
  KEY `status` (`status`),
  KEY `gr` (`gr`),
  KEY `type` (`type`)
);
CREATE TABLE `register_category` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(200) NOT NULL default '',
  PRIMARY KEY  (`id`)
);
CREATE TABLE `register_executor` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`id`)
);
CREATE TABLE `register_group` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(100) NOT NULL default '',
  `email` varchar(200) NOT NULL default '',
  PRIMARY KEY  (`id`)
);
CREATE TABLE `register_task_type` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(100) default NULL,
  PRIMARY KEY  (`id`)
);
CREATE TABLE `register_subject` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(100) NOT NULL default '',
  `gid` int(11) NOT NULL default '0',
  `ac` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`id`)
);
CREATE TABLE `register_problem_executor` (
  `rid` int(11) NOT NULL default '0',
  `eid` int(11) NOT NULL default '0',
  KEY `rid` (`rid`)
);
CREATE TABLE `register_task_executor` (
  `tid` int(11) NOT NULL default '0',
  `eid` int(11) NOT NULL default '0',
  KEY `tid` (`tid`)
);
CREATE TABLE `register_problem_group` (
  `id` int(11) NOT NULL auto_increment,
  `riid` int(11) NOT NULL default '0',
  `gid` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `riid` (`riid`)
);
CREATE TABLE `register_executor_group` (
  `eid` int(11) NOT NULL default '0',
  `gid` int(11) NOT NULL default '0',
  KEY `eid` (`eid`)
);

ALTER TABLE `register_subject` ADD `ac` TINYINT NOT NULL AFTER `gid`;

ALTER TABLE `register` RENAME `register_problem`;
ALTER TABLE `register_executor_link` RENAME `register_problem_executor`;
ALTER TABLE `register_group_link` RENAME `register_problem_group`;
ALTER TABLE `register_call` CHANGE `riid` `rpid` INT NOT NULL;
ALTER TABLE `register_call` ADD `rtid` INT NOT NULL AFTER `rpid`;
ALTER TABLE `register_call` DROP INDEX riid;
ALTER TABLE `register_call` ADD INDEX rpid (rpid);
ALTER TABLE `register_call` ADD INDEX rtid (rtid);

CREATE TABLE `register_task_type` (
`id` INT NOT NULL AUTO_INCREMENT, 
`title` VARCHAR (100), PRIMARY KEY(`id`));

CREATE TABLE `register_work_type` (
`id` INT NOT NULL AUTO_INCREMENT, 
`title` VARCHAR (255), PRIMARY KEY(`id`));

CREATE TABLE register_task_executor (
  tid int(11) NOT NULL default '0',
  eid int(11) NOT NULL default '0',
  KEY tid (tid)
);

CREATE TABLE register_task (
  id int(11) NOT NULL auto_increment,
  cid int(11) NOT NULL default '0',
  type int(11) NOT NULL default '0',
  open_dt datetime default NULL,
  open_uid int(11) NOT NULL default '0',
  accept_dt datetime default NULL,
  accept_uid int(11) NOT NULL default '0',
  close_dt datetime default NULL,
  close_uid int(11) NOT NULL default '0',
  status int(11) NOT NULL default '0',
  target_dt date default NULL,
  comment text NOT NULL,
  gr int(11) NOT NULL default '0',
  resolution text NOT NULL,
  prid int(11) NOT NULL default '0',
  PRIMARY KEY  (id),
  KEY cid (cid),
  KEY open_dt (open_dt),
  KEY status (status),
  KEY gr (gr),
  KEY type (type)
);

ALTER TABLE `register_call` ADD `comment` TEXT NOT NULL AFTER `rtid`;

ALTER TABLE register_task ADD execute_dt DATE;

ALTER TABLE `register_task` ADD `apid` INT NOT NULL AFTER `cid`;
CREATE TABLE register_executor_group (
  eid int(11) NOT NULL default '0',
  gid int(11) NOT NULL default '0',
  KEY eid (eid)
);

ALTER TABLE `register_task` ADD `processed` TINYINT(2)  NOT NULL DEFAULT "0";

ALTER TABLE `register_task` ADD `lm_dt` DATETIME AFTER `close_uid`;
ALTER TABLE `register_task` ADD `lm_uid` INT NOT NULL AFTER `lm_dt`;

CREATE TABLE `register_group_task_type` (
  `gid` int(11) NOT NULL default '0',
  `type_id` int(11) NOT NULL default '0',
  KEY `gid` (`gid`)
);

ALTER TABLE register_task ADD aobject_id INT DEFAULT 0 NOT NULL AFTER cid;

ALTER TABLE register_call CHANGE cid cid INT NOT NULL;
ALTER TABLE register_call ADD oid INT NOT NULL AFTER cid;

CREATE TABLE  register_work (
  `id` int(11) NOT NULL auto_increment,
  `target` varchar(255) NOT NULL,
  `description` varchar(255) NOT NULL,
  `type` int(11) NOT NULL default '0',
  `status` int(11) NOT NULL default '1',
  `comment` text NOT NULL,
  `plan_period_start` datetime default NULL,
  `plan_period_stop` datetime default NULL,
  `plan_timeout` int(11) NOT NULL,
  `fact_period_start` datetime default NULL,
  `fact_period_stop` datetime default NULL,
  `fact_timeout` int(11) NOT NULL,
  `notify_int` text,
  `notify_ext` text,
  `user_order_id` int(11) NOT NULL default '-1',
  `user_control_id` int(11) NOT NULL default '-1',
  PRIMARY KEY  (`id`),
  KEY `status` (`status`)
);

CREATE TABLE register_work_problem (
  `wid` int(11) default NULL,
  `pid` int(11) default NULL
);

CREATE TABLE register_work_type (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(100) default NULL,
  PRIMARY KEY  (`id`)
);

CREATE TABLE  register_work_object (
  `lu` timestamp NOT NULL,
  `id` int(11) NOT NULL auto_increment,
  `work_id` int(11) NOT NULL,
  `contract_id` int(11) NOT NULL,
  `object_id` int(11) NOT NULL,
  `task_id` int(11) NOT NULL,
  PRIMARY KEY USING BTREE (`id`),
  UNIQUE KEY `work_id` (`work_id`,`contract_id`,`object_id`)
);

ALTER TABLE register_work ADD COLUMN `notify_int` TEXT AFTER `fact_timeout`;
ALTER TABLE register_work ADD COLUMN `notify_ext` TEXT AFTER `notify_int`;

ALTER TABLE register_problem CHANGE COLUMN accept_dt status_dt DATETIME DEFAULT NULL, CHANGE COLUMN accept_uid status_uid INTEGER NOT NULL DEFAULT 0;
UPDATE register_problem SET status_dt=open_dt, status_uid=open_uid WHERE isNull( status_dt );
 
ALTER TABLE `register_problem_log` ADD `exec` varchar(255) NOT NULL;
ALTER TABLE `register_executor` ADD COLUMN `params` TEXT NOT NULL DEFAULT '' AFTER `title`;

CREATE TABLE `register_task_log` (
  `task_id` int(11)  NOT NULL,
  `change_dt` datetime NOT NULL,
  `status` int(11) NOT NULL,
  `change_uid` int(11) NOT NULL
);

ALTER TABLE `register_problem` ADD COLUMN `task_list` TEXT  NOT NULL;
ALTER TABLE `register_task` MODIFY COLUMN `target_dt` DATETIME  DEFAULT NULL;