CREATE TABLE channel_$mid
(	
	id int(11) NOT NULL auto_increment,
	title varchar(200) NOT NULL default '',
	PRIMARY KEY  (id) 
);

CREATE TABLE channel_mapping_$mid
(
	id int(11) NOT NULL auto_increment,
	chid int(11) NOT NULL default '0',
	date1 date default NULL,
	date2 date default NULL,
	chcode int(11) NOT NULL default '0',
	cncode int(11) NOT NULL default '0',
	comment varchar(250) NOT NULL default '',
	PRIMARY KEY  (id),
	KEY chid (chid),
	KEY chcode (cncode) 
);

CREATE TABLE packet_$mid
(
	id int(11) NOT NULL auto_increment,
	title varchar(200) NOT NULL default '',
	sid int(11) NOT NULL default '0',
	virtual_cinema TINYINT(1)  NOT NULL, 
	PRIMARY KEY (id) 
);

CREATE TABLE packet_channel_$mid
(
	id int(11) NOT NULL auto_increment,
	pid int(11) NOT NULL default '0',
	chid int(11) NOT NULL default '0',
	date1 date default NULL,
	date2 date default NULL,
	comment varchar(250) NOT NULL default '',
	PRIMARY KEY  (id) 
);

CREATE TABLE event_log_$mid
(
	cpid int(11) NOT NULL default '0',
	dt date default NULL,
	type tinyint(4) NOT NULL default '0',
	uid int(11) NOT NULL default '0',
	UNIQUE KEY cpid_dtime (dt,cpid),
	KEY cpid (cpid) 
);

CREATE TABLE user_card_$mid
(
	id int(11) NOT NULL auto_increment,
	cid int(11) NOT NULL default '0',
	number int(11) NOT NULL default '0',
	date1 date default NULL,
	date2 date default NULL,
	subscr_dt DATE,
	comment varchar(250) NOT NULL default '',
	PRIMARY KEY  (id),
	KEY cid (cid),
	KEY number (number) 
);

CREATE TABLE card_packet_$mid
(
	id int(11) NOT NULL auto_increment,
	cid int(11) NOT NULL default '0',
	ucid int(11) NOT NULL default '0',
	status tinyint(4) NOT NULL default '0',
	pid int(11) NOT NULL default '0',
	date1 datetime default NULL,
	date2 datetime default NULL,
	comment varchar(250) NOT NULL default '',
	PRIMARY KEY  (id),
	KEY cid (cid),
	KEY pid (pid),
	KEY ucid (ucid) 
);

CREATE TABLE card_$mid
(
	number INT NOT NULL, 
	pswd VARCHAR(200) NOT NULL,
	dealer_id INT NOT NULL,
	pass_dt DATE, 
	cid INT NOT NULL,
	contract_dt DATE, 
	PRIMARY KEY(number), 
	UNIQUE(number) 
);

CREATE TABLE dealer_$mid
(
	id INT NOT NULL AUTO_INCREMENT, 
	title VARCHAR (200) NOT NULL, 
	date1 DATE, 
	date2 DATE, 
	comment VARCHAR (250) NOT NULL, 
	PRIMARY KEY(id)
);

ALTER TABLE packet_$mid ADD virtual_cinema TINYINT(1)  NOT NULL;
ALTER TABLE card_packet_$mid CHANGE date1 date1 datetime default NULL;
ALTER TABLE card_packet_$mid CHANGE date2 date2 datetime default NULL;

ALTER TABLE channel_mapping_$mid ADD scode INT NOT NULL DEFAULT 0;

ALTER TABLE card_$mid ADD pswd VARCHAR(200) NOT NULL;

CREATE TABLE packet_detail_$mid	( 
	id INT auto_increment,
	pid INT NOT NULL, 
	yy SMALLINT NOT NULL, 
	mm 	SMALLINT NOT NULL,
	day SMALLINT NOT NULL,
	summa	float NOT NULL,
	PRIMARY KEY  (id),
	KEY client_packet (pid,yy,mm,day));
	
ALTER TABLE `channel_mapping_$mid` MODIFY COLUMN `chcode` VARCHAR(100) NOT NULL DEFAULT '0';

ALTER TABLE `user_card_$mid` MODIFY COLUMN `number` BIGINT NOT NULL;
	
ALTER TABLE `packet_$mid` ADD COLUMN `depends` VARCHAR(255) NOT NULL AFTER `virtual_cinema`,
 ADD COLUMN `unacceptable` VARCHAR(255) NOT NULL AFTER `depends`;

CREATE TABLE user_card_copy_$mid
(	
	id INT auto_increment,
	uid INT(11) NOT NULL,
	date1 date default NULL,
	date2 date default NULL,
	comment varchar(250) NOT NULL default '',
	PRIMARY KEY(id),
	KEY number(uid)
);

ALTER TABLE `packet_$mid` ADD COLUMN `free` tinyint(3) unsigned NOT NULL default '0';
 
ALTER TABLE `packet_$mid` ADD COLUMN `pcode` VARCHAR(100) NOT NULL;

ALTER TABLE `packet_$mid` ADD COLUMN `comment` TEXT NULL;

ALTER TABLE `user_card_$mid` ADD COLUMN `basecardId` INTEGER NOT NULL DEFAULT 0;

ALTER TABLE `user_card_$mid` ADD COLUMN `object_id` INT  NOT NULL DEFAULT -1;

ALTER TABLE `user_card_$mid` ADD COLUMN `need_sync` TINYINT(1)  NULL DEFAULT 0;

CREATE TABLE IF NOT EXISTS `cerbercrypt_user_devices_$mid` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(256) NULL ,
  `comment` TEXT NULL ,
  `config` TEXT NULL ,
  PRIMARY KEY (`id`) );

ALTER TABLE `user_card_$mid` ADD COLUMN `userdevice_id` INT(11) NOT NULL DEFAULT 0;

CREATE TABLE IF NOT EXISTS `cerbercrypt_gradually_subscription_$mid` (
  `entityId` INT NOT NULL ,
  `date` DATE NOT NULL ,
  PRIMARY KEY (`entityId`) );

ALTER TABLE `packet_$mid` CHANGE COLUMN `pcode` `pcode` VARCHAR(100) NULL;

ALTER TABLE `packet_$mid` ADD COLUMN `can_web_open` TINYINT(1) NOT NULL DEFAULT 0  AFTER `sid` , ADD COLUMN `can_web_close` TINYINT(1) NOT NULL DEFAULT 0  AFTER `can_web_open` ;

-- новый клиентский package
UPDATE installed_modules SET pack_client='ru.bitel.bgbilling.modules.cerbercrypt.client' WHERE pack_client='bitel.billing.module.services.cerbercrypt';
UPDATE installed_modules SET pack_server='ru.bitel.bgbilling.modules.cerbercrypt.server' WHERE pack_server='bitel.billing.server.cerbercrypt';

ALTER TABLE `packet_$mid` ADD COLUMN `server_code` VARCHAR(100) NULL;

ALTER TABLE `card_$mid` MODIFY COLUMN `number` BIGINT NOT NULL;

ALTER TABLE `card_$mid` ADD COLUMN `pairing_module` VARCHAR(58) NULL;

ALTER TABLE `user_card_$mid` ADD COLUMN `userdevice_sn` VARCHAR(58) NULL;


-- #BLOCK#
DELETE FROM script_event_type WHERE mid=$mid;

SET @mid=$mid, @old_event_id=1, @new_event_id='ru.bitel.bgbilling.modules.cerbercrypt.server.event.SubscriptionChangedEvent', @event_title='Изменение подписки карты';
INSERT INTO script_event_type (mid, event_id, event_mode, title) VALUES (@mid, @new_event_id, 1, @event_title);
INSERT INTO script_function_event_type(fid, mid, event_id) SELECT sfet1.fid, sfet1.mid, @new_event_id FROM script_function_event_type AS sfet1 LEFT JOIN script_function_event_type AS sfet2 ON sfet1.fid=sfet2.fid AND sfet1.mid=sfet2.mid AND sfet2.event_id=@new_event_id WHERE sfet1.mid=@mid AND sfet1.event_id=@old_event_id AND sfet2.fid IS NULL;

SET @mid=$mid, @old_event_id=2, @new_event_id='ru.bitel.bgbilling.modules.cerbercrypt.server.event.GetOpenPacketDatesEvent', @event_title='Получение дат открытия пакетов';
INSERT INTO script_event_type (mid, event_id, event_mode, title) VALUES (@mid, @new_event_id, 1, @event_title);
INSERT INTO script_function_event_type(fid, mid, event_id) SELECT sfet1.fid, sfet1.mid, @new_event_id FROM script_function_event_type AS sfet1 LEFT JOIN script_function_event_type AS sfet2 ON sfet1.fid=sfet2.fid AND sfet1.mid=sfet2.mid AND sfet2.event_id=@new_event_id WHERE sfet1.mid=@mid AND sfet1.event_id=@old_event_id AND sfet2.fid IS NULL;

SET @mid=$mid, @old_event_id=3, @new_event_id='ru.bitel.bgbilling.modules.cerbercrypt.server.event.GetClosePacketDatesEvent', @event_title='Получение дат закрытия пакетов';
INSERT INTO script_event_type (mid, event_id, event_mode, title) VALUES (@mid, @new_event_id, 1, @event_title);
INSERT INTO script_function_event_type(fid, mid, event_id) SELECT sfet1.fid, sfet1.mid, @new_event_id FROM script_function_event_type AS sfet1 LEFT JOIN script_function_event_type AS sfet2 ON sfet1.fid=sfet2.fid AND sfet1.mid=sfet2.mid AND sfet2.event_id=@new_event_id WHERE sfet1.mid=@mid AND sfet1.event_id=@old_event_id AND sfet2.fid IS NULL;

SET @mid=$mid, @old_event_id=4, @new_event_id='ru.bitel.bgbilling.modules.cerbercrypt.server.event.OpenPacketSubscriptionEvent', @event_title='Перед открытием пакета';
INSERT INTO script_event_type (mid, event_id, event_mode, title) VALUES (@mid, @new_event_id, 1, @event_title);
INSERT INTO script_function_event_type(fid, mid, event_id) SELECT sfet1.fid, sfet1.mid, @new_event_id FROM script_function_event_type AS sfet1 LEFT JOIN script_function_event_type AS sfet2 ON sfet1.fid=sfet2.fid AND sfet1.mid=sfet2.mid AND sfet2.event_id=@new_event_id WHERE sfet1.mid=@mid AND sfet1.event_id=@old_event_id AND sfet2.fid IS NULL;

SET @mid=$mid, @old_event_id=5, @new_event_id='ru.bitel.bgbilling.modules.cerbercrypt.server.event.ClosePacketSubscriptionEvent', @event_title='Перед закрытием пакета';
INSERT INTO script_event_type (mid, event_id, event_mode, title) VALUES (@mid, @new_event_id, 1, @event_title);
INSERT INTO script_function_event_type(fid, mid, event_id) SELECT sfet1.fid, sfet1.mid, @new_event_id FROM script_function_event_type AS sfet1 LEFT JOIN script_function_event_type AS sfet2 ON sfet1.fid=sfet2.fid AND sfet1.mid=sfet2.mid AND sfet2.event_id=@new_event_id WHERE sfet1.mid=@mid AND sfet1.event_id=@old_event_id AND sfet2.fid IS NULL;

INSERT INTO script_event_type (mid, event_id, event_mode, title) VALUES ($mid, 'ru.bitel.bgbilling.kernel.event.events.CalculateEvent', 0, 'Начисление');

SET @mid=$mid, @old_event_id=7, @new_event_id='ru.bitel.bgbilling.modules.cerbercrypt.server.event.UserCardUpdateEvent', @event_title='Изменение юзеркарты на договоре';
INSERT INTO script_event_type (mid, event_id, event_mode, title) VALUES (@mid, @new_event_id, 1, @event_title);
INSERT INTO script_function_event_type(fid, mid, event_id) SELECT sfet1.fid, sfet1.mid, @new_event_id FROM script_function_event_type AS sfet1 LEFT JOIN script_function_event_type AS sfet2 ON sfet1.fid=sfet2.fid AND sfet1.mid=sfet2.mid AND sfet2.event_id=@new_event_id WHERE sfet1.mid=@mid AND sfet1.event_id=@old_event_id AND sfet2.fid IS NULL;

-- #ENDB#

