CREATE TABLE IF NOT EXISTS `tv_account_$mid` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parentId` int(11) NOT NULL,
  `title` varchar(150) NOT NULL,
  `contractId` int(11) NOT NULL,
  `specId` int(11) NOT NULL,
  `deviceId` int(11) NOT NULL,
  `login` varchar(200) DEFAULT NULL,
  `pin` varchar(100) DEFAULT NULL,
  `dateFrom` date DEFAULT NULL,
  `dateTo` date DEFAULT NULL,
  `status` int(11) NOT NULL,
  `comment` varchar(255) NOT NULL,
  `config` varchar(255) DEFAULT NULL,
  `deviceState` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `contract` (`contractId`),
  KEY `device` (`deviceId`),
  KEY `period` (`dateFrom`,`dateTo`),
  KEY `parent` (`parentId`)
);

CREATE TABLE IF NOT EXISTS `tv_account_spec_$mid` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(150) NOT NULL,
  `parentTypeIds` varchar(45) NOT NULL DEFAULT '',
  `needLogin` tinyint(4) NOT NULL,
  `needPin` tinyint(4) NOT NULL,
  `needDevice` tinyint(4) NOT NULL,
  `config` text,
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `tv_account_spec_device_group_link_$mid` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tvAccountSpecId` int(11) NOT NULL,
  `deviceGroupId` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `tvAccountSpec` (`tvAccountSpecId`)
);

CREATE TABLE IF NOT EXISTS `tv_account_spec_device_type_link_$mid` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tvAccountSpecId` int(11) NOT NULL,
  `deviceTypeId` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `tvAccountSpec` (`tvAccountSpecId`)
);

CREATE TABLE IF NOT EXISTS `tv_device_$mid` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `entityId` int(11) NOT NULL,
  `parentId` int(11) NOT NULL,
  `deviceTypeId` int(11) NOT NULL,
  `title` varchar(200) NOT NULL,
  `host` varchar(100) NOT NULL,
  `username` varchar(100) NOT NULL,
  `password` varchar(100) NOT NULL,
  `secret` varchar(100) NOT NULL,
  `config` text NOT NULL,
  `comment` text NOT NULL,
  `identifier` varchar(150) NOT NULL,
  `uptime` text NOT NULL,
  `uptimeTime` text,
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `tv_device_type_$mid` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(250) NOT NULL,
  `entitySpecId` int(11) NOT NULL,
  `config` text NOT NULL,
  `protocolHandlerClass` varchar(250) DEFAULT NULL,
  `saHandlerClass` varchar(250) DEFAULT NULL,
  `deviceManagerClass` varchar(250) DEFAULT NULL,
  `comment` varchar(250) NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `tv_service_spec_channel_link_$mid` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `serviceSpecId` int(11) NOT NULL,
  `deviceId` int(11) NOT NULL,
  `channel` varchar(255) NOT NULL,
  `dateFrom` date DEFAULT NULL,
  `dateTo` date DEFAULT NULL,
  `comment` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `service` (`serviceSpecId`)
);

CREATE TABLE IF NOT EXISTS `inv_device_group_$mid` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parentId` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `cityId` int(11) NOT NULL,
  `comment` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `parent_id` (`parentId`),
  KEY `city_id` (`cityId`)
);

CREATE TABLE IF NOT EXISTS `inv_device_group_link_$mid` (
  `deviceId` int(11) NOT NULL,
  `deviceGroupId` int(11) NOT NULL,
  KEY `device_id` (`deviceId`,`deviceGroupId`)
);

ALTER TABLE `tv_account_$mid` CHANGE COLUMN `pin` `password` varchar(100) DEFAULT NULL;

ALTER TABLE `tv_account_spec_$mid` ADD COLUMN `needIdentifier` TINYINT(4)  NOT NULL DEFAULT 0 AFTER `needDevice`;
ALTER TABLE `tv_account_$mid` ADD COLUMN `identifier` varchar(80) NULL  AFTER `password` ;

ALTER TABLE `tv_account_spec_$mid` ADD COLUMN `needMacAddress` TINYINT(4)  NOT NULL DEFAULT 0 AFTER `needIdentifier`;
ALTER TABLE `tv_account_$mid` ADD COLUMN `macAddress` varbinary(8) NULL  AFTER `identifier` ;
ALTER TABLE `tv_account_$mid` CHANGE COLUMN `macAddress` `macAddress` VARBINARY(64) NULL DEFAULT NULL  ;

ALTER TABLE `tv_account_$mid` ADD COLUMN `deviceOptions` varchar(200) NOT NULL AFTER `deviceState`;
ALTER TABLE `tv_account_$mid` ADD COLUMN `accessCode` INT(11) NOT NULL DEFAULT '-1' AFTER `deviceOptions`;

CREATE TABLE IF NOT EXISTS `tv_channel_spec_$mid` (
  `id` INT NOT NULL ,
  `entityId` INT NOT NULL ,
  `title` VARCHAR(150) NOT NULL ,
  `serviceSpecId` INT NOT NULL ,
  `identifier` VARCHAR(150) NOT NULL ,
  `dateFrom` DATE NULL ,
  `dateTo` DATE NULL ,
  `comment` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `serviceSpec` (`serviceSpecId` ASC) ,
  INDEX `entity` (`entityId` ASC) );


-- #BLOCK#
CREATE TABLE IF NOT EXISTS `tv_account_detail_$mid_201304_new` (
  `contractId` int(11) NOT NULL,
  `accountId` int(11) NOT NULL,
  `productId` int(11) NOT NULL,
  `sid` int(11) NOT NULL,
  `day` int(11) NOT NULL,
  `account` decimal(10,5) NOT NULL,
  PRIMARY KEY (`contractId`,`accountId`,`productId`,`sid`,`day`)
) /*!50100 PARTITION BY HASH (contractId)
PARTITIONS 8 */;
INSERT INTO tv_account_detail_$mid_201304_new SELECT contractId, accountId, productId, sid, day, SUM(account)
FROM tv_account_detail_$mid_201304 GROUP BY contractId, accountId, productId, sid, day;
ALTER TABLE `tv_account_detail_$mid_201304` RENAME TO `tv_account_detail_$mid_201304_old`;
ALTER TABLE `tv_account_detail_$mid_201304_new` RENAME TO `tv_account_detail_$mid_201304`;
-- #ENDB#

ALTER TABLE `tv_account_spec_$mid` CHANGE COLUMN `needPin` `needPassword` TINYINT(4) NOT NULL , ADD COLUMN `needPin` TINYINT(4) NOT NULL  AFTER `needPassword` ;
ALTER TABLE `tv_account_spec_$mid` ADD COLUMN `entitySpecId` int(11) NOT NULL DEFAULT 0 AFTER `title`;
ALTER TABLE `tv_account_$mid` ADD COLUMN `pin` VARCHAR(100) NULL DEFAULT NULL  AFTER `password` ;
ALTER TABLE `tv_account_$mid` ADD COLUMN `deviceAccountId` VARCHAR(100) NULL DEFAULT NULL  AFTER `config` ;
ALTER TABLE `tv_account_$mid` ADD INDEX `deviceAccountId` (`deviceAccountId` ASC) ;
ALTER TABLE `tv_device_$mid` ADD COLUMN `dateFrom` DATE NULL  AFTER `host` , ADD COLUMN `dateTo` DATE NULL  AFTER `dateFrom` ;
ALTER TABLE `tv_device_$mid` ADD COLUMN `orderManagerDisabled` TINYINT(4) NOT NULL DEFAULT '0'  AFTER `dateTo`;
ALTER TABLE `tv_channel_spec_$mid` CHANGE COLUMN `id` `id` INT(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `tv_device_type_$mid` CHANGE COLUMN `deviceEntitySpecId` `entitySpecId` int(11) NOT NULL DEFAULT 0;
ALTER TABLE `tv_device_$mid` ADD COLUMN `sort` int(11) NOT NULL DEFAULT 0 AFTER `id`;

UPDATE `event_script_link` SET `event_key`=REPLACE(`event_key`, 'ru.bitel.bgbilling.modules.tv.api.common.event.TvAccountCreatedEvent', 'ru.bitel.bgbilling.modules.tv.common.event.TvAccountCreatedEvent');
UPDATE `event_script_link` SET `event_key`=REPLACE(`event_key`, 'ru.bitel.bgbilling.modules.tv.api.common.event.TvAccountModifyEvent', 'ru.bitel.bgbilling.modules.tv.common.event.TvAccountModifyEvent');

-- переименование задачи
UPDATE `scheduled_class` SET `class`='ru.bitel.bgbilling.modules.tv.server.task.TvAccountPeriodTask' WHERE `class`='ru.bitel.bgbilling.modules.tv.task.TvAccountPeriodTask';
UPDATE `scheduled_tasks` SET `class`='ru.bitel.bgbilling.modules.tv.server.task.TvAccountPeriodTask' WHERE `class`='ru.bitel.bgbilling.modules.tv.task.TvAccountPeriodTask';
UPDATE `scheduled_class` SET `class`='ru.bitel.bgbilling.modules.tv.server.task.TvProlongationTask' WHERE `class`='ru.bitel.bgbilling.modules.tv.task.TvProlongationTask';
UPDATE `scheduled_tasks` SET `class`='ru.bitel.bgbilling.modules.tv.server.task.TvProlongationTask' WHERE `class`='ru.bitel.bgbilling.modules.tv.task.TvProlongationTask';

-- события
-- #BLOCK#
DELETE FROM script_event_type WHERE mid=$mid;
INSERT INTO script_event_type (mid, event_id, event_mode, title) VALUES ($mid, 'ru.bitel.bgbilling.modules.tv.common.event.TvAccountModifyEvent', 1, 'Аккаунт: изменяется');
INSERT INTO script_event_type (mid, event_id, event_mode, title) VALUES ($mid, 'ru.bitel.bgbilling.modules.tv.common.event.TvAccountCreatedEvent', 1, 'Аккаунт: создан и синхронизирован с MW');
-- #ENDB#