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,
  PRIMARY KEY (`deviceId`,`deviceGroupId`)
);

CREATE TABLE IF NOT EXISTS `inv_ip_category_$mid` (
  `id` int(11) NOT NULL auto_increment,
  `parentId` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `parent` (`parentId`)
);

CREATE TABLE IF NOT EXISTS `inv_ip_resource_$mid` (
  `id` int(11) NOT NULL auto_increment,
  `categoryId` int(11) NOT NULL,
  `addressFrom` varbinary(24) NOT NULL,
  `addressTo` varbinary(24) NOT NULL,
  `dateFrom` date,
  `dateTo` date,
  `comment` text NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `ip` (`categoryId`,`addressFrom`,`addressTo`),
  KEY `period` (`dateFrom`,`dateTo`)
);

CREATE TABLE IF NOT EXISTS `inv_ip_resource_dyn_reserve_$mid` (
  `address` varbinary(24) NOT NULL,
  `timeout` datetime NOT NULL,
  `ipResourceId` int(11) NOT NULL,
  KEY `timeout` (`timeout`)
);

CREATE TABLE IF NOT EXISTS `inet_device_$mid` (
  `id` int(11) NOT NULL auto_increment,
  `entityId` int(11) NOT NULL,
  `parentId` int(11) NOT NULL,
  `deviceTypeId` int(11) NOT NULL,
  `identifier` varchar(150) NOT NULL,
  `host` varchar(150) 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,
  PRIMARY KEY  (`id`)
);

CREATE TABLE IF NOT EXISTS `inet_accounting_period_$mid` (
  `id` int(11) NOT NULL auto_increment,
  `contractId` int(11) NOT NULL,
  `dateFrom` date NOT NULL,
  `dateTo` date NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `contract` (`contractId`)
);

CREATE TABLE IF NOT EXISTS `inet_device_type_$mid` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(250) NOT NULL,
  `configId` int(11) NOT NULL,
  `config` text NOT NULL,
  `uniqueInterfaces` tinyint(4) NOT NULL,
  `scriptId` int(11) NOT NULL,
  `saScript` text NOT NULL,
  `eventScript` text NOT NULL,
  `comment` varchar(250) NOT NULL,
  PRIMARY KEY  (`id`)
);

CREATE TABLE IF NOT EXISTS `inet_interface_$mid` (
  `id` int(11) NOT NULL,
  `title` varchar(100) NOT NULL,
  `deviceTypeId` int(11) NOT NULL,
  `zoneId` int(11) NOT NULL,
  PRIMARY KEY  (`id`,`deviceTypeId`),
  KEY `deviceTypeId` (`deviceTypeId`)
);

CREATE TABLE IF NOT EXISTS `inet_option_$mid` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(150) NOT NULL,
  PRIMARY KEY  (`id`)
);

CREATE TABLE IF NOT EXISTS `inet_serv_$mid` (
  `id` int(11) NOT NULL auto_increment,
  `contractId` int(11) NOT NULL DEFAULT 0,
  `typeId` int(11) NOT NULL DEFAULT 0,
  `deviceId` int(11) NOT NULL DEFAULT 0,
  `interfaceId` int(11) NOT NULL DEFAULT 0,
  `vlan` int(11) NOT NULL default 0,
  `ipResourceId` int(11),
  `addressFrom` varbinary(24),
  `addressTo` varbinary(24),
  `login` varchar(100) NOT NULL  DEFAULT '',
  `password` char(30),
  `dateFrom` date,
  `dateTo` date,
  `status` int(11) NOT NULL DEFAULT 0,
  `sessionCountLimit` int(11) NOT NULL DEFAULT 0,
  `deviceState` int(11) NOT NULL DEFAULT 0,
  `accessCode` int(11) NOT NULL DEFAULT -1,
  `deviceOptions` varchar(200) NOT NULL DEFAULT '',
  `comment` varchar(250) NOT NULL DEFAULT '',
  PRIMARY KEY  (`id`)
);

CREATE TABLE IF NOT EXISTS `inet_serv_type_$mid` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(150) NOT NULL,
  `parentTypeIds` varchar(45) NOT NULL DEFAULT '',
  `sessionInitiationType` tinyint(4) NOT NULL,
  `sessionCountLimit` int(11) NOT NULL,
  `addressType` tinyint(4) NOT NULL,
  `trafficTypeLinkId` int(11) NOT NULL,
  `needLogin` tinyint(4) NOT NULL,
  `needDevice` tinyint(4) NOT NULL,
  `needInterface` tinyint(4) NOT NULL,
  `personalInterface` tinyint(4) NOT NULL DEFAULT '1',
  `needVlan` tinyint(4) NOT NULL,
  `config` text,
  `personalVlan` tinyint(4) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `inet_serv_type_device_group_link_$mid` (
  `inetServId` int(11) NOT NULL,
  `deviceGroupId` int(11) NOT NULL,
  KEY `inetServId` (`inetServId`)
);

CREATE TABLE IF NOT EXISTS `inet_serv_type_device_type_link_$mid` (
  `inetServId` int(11) NOT NULL,
  `deviceTypeId` int(11) NOT NULL,
  PRIMARY KEY (`inetServId`, `deviceTypeId`)
);

CREATE TABLE IF NOT EXISTS `inet_traffic_type_$mid` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(150) NOT NULL,
  PRIMARY KEY  (`id`)
);

CREATE TABLE IF NOT EXISTS `inet_traffic_type_link_$mid` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(150) NOT NULL,
  PRIMARY KEY  (`id`)
);

CREATE TABLE IF NOT EXISTS `inet_traffic_type_link_rule_$mid` (
  `id` int(11) NOT NULL auto_increment,
  `linkId` int(11) NOT NULL,
  `position` int(11) NOT NULL,
  `dateFrom` date,
  `dateTo` date,
  `type` int(11) NOT NULL,
  `direction` int(11) NOT NULL,
  `addressFrom` varbinary(24),
  `addressTo` varbinary(24),
  `portFrom` int(11) NOT NULL,
  `portTo` int(11) NOT NULL,
  `diffServ` varchar(50) DEFAULT NULL,
  `counterVendor` int(11) NOT NULL,
  `counterType` int(11) NOT NULL,
  `counterPrefix` varchar(50) DEFAULT NULL,
  `trafficTypeId` int(11) NOT NULL,
  `comment` text NOT NULL DEFAULT '',
  PRIMARY KEY  (`id`)
);

CREATE TABLE IF NOT EXISTS `inet_zone_$mid` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(150) NOT NULL,
  PRIMARY KEY  (`id`)
);

CREATE TABLE IF NOT EXISTS `inv_ip_resource_subscription_$mid` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subscriberId` int(11) NOT NULL,
  `ipResourceId` int(11) NOT NULL,
  `addressFrom` varbinary(24) NOT NULL,
  `addressTo` varbinary(24) NOT NULL,
  `dateFrom` date,
  `dateTo` date,
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `inet_serv_option_$mid` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `servId` int(11) NOT NULL,
  `optionId` int(11) NOT NULL,
  `dateFrom` date,
  `dateTo` date,
  PRIMARY KEY (`id`),
  KEY `servId` (`servId`)
);

CREATE TABLE IF NOT EXISTS `inet_task_process_$mid` (
   `id` INT  NOT NULL AUTO_INCREMENT,
  `deviceId` INT  NOT NULL,
  `day` DATETIME  NOT NULL,
  `startTime` DATETIME,
  `curentHour` INT  NOT NULL,
  PRIMARY KEY (`id`)
);

ALTER TABLE inet_device_$mid ADD title VARCHAR(250) NOT NULL AFTER deviceTypeId;
ALTER TABLE inet_device_type_$mid ADD source tinyint(1) DEFAULT '0';
ALTER TABLE inv_ip_resource_$mid ADD  dynamic tinyint(1);
ALTER TABLE inet_serv_type_$mid ADD COLUMN `config` TEXT ;
ALTER TABLE inet_serv_$mid ADD COLUMN `title` VARCHAR(255) ;

ALTER TABLE `inet_device_type_$mid` ADD `deviceEntitySpecId` INT NOT NULL DEFAULT 0;
ALTER TABLE `inet_device_type_$mid` CHANGE COLUMN `deviceEntitySpecId` `entitySpecId` int(11) NOT NULL DEFAULT 0;

ALTER TABLE inet_device_type_$mid ADD saHandlerClass VARCHAR(250) NOT NULL AFTER config;
ALTER TABLE inet_device_type_$mid ADD protocolHandlerClass VARCHAR(250) NOT NULL AFTER config;

ALTER TABLE inet_device_type_$mid 
MODIFY COLUMN `protocolHandlerClass` VARCHAR(250),
MODIFY COLUMN `saHandlerClass` VARCHAR(250),
MODIFY COLUMN `saScript` TEXT,
MODIFY COLUMN `eventScript` TEXT;

CREATE TABLE IF NOT EXISTS `inv_vlan_resource_$mid` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `vlanFrom` int(11) NOT NULL,
  `vlanTo` int(11) NOT NULL,
  `dateFrom` date,
  `dateTo` date,
  `comment` text NOT NULL,
  `categoryId` int(11) NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `inv_vlan_category_$mid` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parentId` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `parent` (`parentId`)
);

CREATE TABLE IF NOT EXISTS `inv_vlan_resource_subscription_$mid` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `vlanResId` int(11) NOT NULL,
  `vlan` int(11) NOT NULL,
  `dateFrom` date,
  `dateTo` date,
  `subscriberId` int(11) NOT NULL,
  `subscriberTitle` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `vlan` (`vlanResId`,`vlan`),
  KEY `period` (`dateFrom`,`dateTo`),
  KEY `subscriber` (`subscriberId`)
);

ALTER TABLE inv_ip_resource_subscription_$mid CHANGE COLUMN `subsciberId` `subscriberId` INTEGER  NOT NULL;

CREATE TABLE IF NOT EXISTS `inv_device_port_subscription_$mid` (
`id` INT  NOT NULL AUTO_INCREMENT,
  `deviceId` INT  NOT NULL,
  `port` INT  NOT NULL,
  `dateFrom` DATE,
  `dateTo` DATE,
  `subscriberId` INT  NOT NULL,
  `subscriberTitle` VARCHAR(255),
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `inv_device_port_$mid` (
  `deviceId` int(11) NOT NULL,
  `port` int(11) NOT NULL,
  `status` int(11) NOT NULL,
  `ipCategoryId` int(11) NOT NULL,
  `comment` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`deviceId`,`port`),
  KEY `device_port` (`deviceId`,`port`)
);

ALTER TABLE `inet_option_$mid` ADD COLUMN `config` TEXT NOT NULL;

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

CREATE TABLE IF NOT EXISTS `inet_connection_$mid` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `parentId` bigint(20) NOT NULL DEFAULT 0,
  `deviceId` int(11) NOT NULL DEFAULT 0,
  `devicePort` int(11) NOT NULL DEFAULT 0,
  `acctSessionId` varchar(80) NOT NULL,
  `username` varchar(100) NOT NULL,
  `type` int(11) NOT NULL DEFAULT 0,
  `accessCode` int(11) NOT NULL DEFAULT 0,
  `servId` int(11) NOT NULL DEFAULT 0,
  `calledStationId` char(50) NOT NULL,
  `callingStationId` char(50) NOT NULL,
  `ipResourceId` int(11) NOT NULL DEFAULT 0,
  `ipAddress` varbinary(24) NOT NULL,
  `connectionStart` datetime NOT NULL,
  `deviceOptions` char(250) NOT NULL,
  `status` int(11) NOT NULL DEFAULT 0,
  KEY `id` (`id`),
  KEY `nas` (`deviceId`,`devicePort`),
  KEY `serv` (`servId`),
  KEY `parent` (`parentId`),
  KEY `connectionStart` (`connectionStart`),
  KEY `ipAddress` (`ipAddress`)
) /*!50106 PARTITION BY HASH (deviceId) PARTITIONS 8 */;

CREATE TABLE IF NOT EXISTS `inet_session_$mid` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `connectionId` bigint(20) NOT NULL,
  `sessionStart` datetime NOT NULL,
  `sessionStop` datetime,
  `lastActive` datetime NOT NULL,
  `sessionTime` bigint(20) NOT NULL,
  `sessionCost` decimal(12,5) NOT NULL,
  `status` int(11) NOT NULL,
  KEY `id` (`id`,`connectionId`),
  KEY `start` (`sessionStart`) 
) /*!50106 PARTITION BY HASH (connectionId)
PARTITIONS 8 */;

CREATE TABLE IF NOT EXISTS `inet_session_detail_$mid` (
  `sessionId` bigint(20) NOT NULL,
  `day` int(11) NOT NULL,
  `hour` int(11) NOT NULL,
  `trafficTypeId` int(11) NOT NULL,
  `amount` bigint(20) NOT NULL,
  PRIMARY KEY  (`sessionId`,`day`,`hour`, `trafficTypeId`)
  ) /*!50106 PARTITION BY HASH( sessionId )
  PARTITIONS 8*/;

CREATE TABLE IF NOT EXISTS `inet_session_account_$mid` (
  `contractId` int(11) NOT NULL,
  `sessionId` bigint(20) NOT NULL,
  `serviceId` int(11) NOT NULL,
  `amount` bigint(20) NOT NULL,
  `account` decimal(10,5) NOT NULL,
  PRIMARY KEY (`contractId`, `sessionId`, `serviceId`),
  INDEX `serviceId`(`serviceId`),
  INDEX `sessionId` (`sessionId`)
  ) /*!50106 PARTITION BY HASH( contractId )
  PARTITIONS 8*/;

CREATE TABLE IF NOT EXISTS `inet_connection_route_$mid` (
 `id` INT  NOT NULL AUTO_INCREMENT,
 `connectionId` bigint(20) NOT NULL,
 `subnet` varbinary(24)  NOT NULL,
 `mask` INT  NOT NULL,
 PRIMARY KEY (`id`),
 INDEX `connectionId`(`connectionId`) );
 
ALTER TABLE inet_interface_$mid DROP COLUMN `zoneId`;
ALTER TABLE inet_interface_$mid DROP COLUMN `ipCategoryId`;

ALTER TABLE inv_device_port_$mid ADD COLUMN `ipCategoryId` INT;

ALTER TABLE inv_ip_resource_subscription_$mid ADD COLUMN `subscriberTitle` VARCHAR(255) NOT NULL;

ALTER TABLE inv_device_port_subscription_$mid CHANGE COLUMN subcriberId subscriberId INTEGER NOT NULL;
ALTER TABLE inv_device_port_subscription_$mid CHANGE COLUMN subcriberTitle subscriberTitle VARCHAR(255) NOT NULL;

ALTER TABLE `inet_session_detail_$mid` ADD COLUMN `deviceId` INT NOT NULL AFTER `trafficTypeId`
, CHANGE COLUMN `day` `day` TINYINT NOT NULL
, CHANGE COLUMN `hour` `hour` TINYINT NOT NULL  
, DROP PRIMARY KEY 
, ADD PRIMARY KEY (`sessionId`, `day`, `hour`, `trafficTypeId`, `deviceId`);

ALTER TABLE `inet_option_$mid` ADD COLUMN `parentId` INT NOT NULL  AFTER `id` 
, ADD COLUMN `comment` TEXT NOT NULL  AFTER `config`;

ALTER TABLE `inet_option_$mid` ADD COLUMN `groupIntersection` TINYINT NOT NULL  AFTER `title`;

ALTER TABLE `inet_connection_$mid` CHANGE COLUMN acctSessionId acctSessionId varchar(60) DEFAULT NULL;
ALTER TABLE `inet_connection_$mid` CHANGE COLUMN username username varchar(100) DEFAULT NULL;
ALTER TABLE `inet_connection_$mid` CHANGE COLUMN calledStationId calledStationId varchar(50) DEFAULT NULL;
ALTER TABLE `inet_connection_$mid` CHANGE COLUMN callingStationId callingStationId varchar(50) DEFAULT NULL;
ALTER TABLE `inet_connection_$mid` CHANGE COLUMN ipAddress ipAddress varbinary(24) DEFAULT NULL;

ALTER TABLE `inet_device_$mid` ADD COLUMN `uptime` TEXT NOT NULL DEFAULT ''  AFTER `identifier` 
, ADD COLUMN `uptimeTime` DATETIME NULL  AFTER `uptime` ;

ALTER TABLE inet_traffic_type_link_rule_$mid CHANGE COLUMN diffServ diffServ varchar(50) DEFAULT NULL;
ALTER TABLE inet_traffic_type_link_rule_$mid CHANGE COLUMN counterPrefix counterPrefix varchar(50) DEFAULT NULL;

ALTER TABLE `inet_traffic_type_link_rule_$mid` 
	ADD COLUMN `sourceId` INT NOT NULL DEFAULT 0  AFTER `type`,
	ADD COLUMN `interfaceId` INT NOT NULL DEFAULT -1  AFTER `sourceId`;
 
CREATE TABLE IF NOT EXISTS `inet_tariff_traffic_max_$mid` (
  `contractId` int(11) NOT NULL,
  `treeNodeId` bigint(20) NOT NULL,
  `maxKey` bigint(20) NOT NULL,
  `counter` int(11) NOT NULL,
  `yy` int(11) NOT NULL,
  `mm` int(11) NOT NULL,
  `amountMax` bigint(20) NOT NULL,
  `amount1` bigint(20) NOT NULL,
  `amount2` bigint(20) NOT NULL,
  PRIMARY KEY (`contractId`,`treeNodeId`,`maxKey`,`yy`,`mm` )
)/*!50106 PARTITION BY HASH( contractId )
PARTITIONS 8*/;


ALTER TABLE `inet_session_$mid` CHANGE COLUMN status status SMALLINT NOT NULL;

ALTER TABLE `inet_connection_$mid` CHANGE COLUMN accessCode accessCode SMALLINT NOT NULL;
ALTER TABLE `inet_connection_$mid` CHANGE COLUMN status status SMALLINT NOT NULL;

ALTER TABLE `inet_connection_$mid` ADD COLUMN deviceState SMALLINT NOT NULL DEFAULT '1' AFTER `connectionStart`;
ALTER TABLE `inet_session_$mid` ADD COLUMN deviceState SMALLINT NOT NULL DEFAULT '1' AFTER `sessionCost`;

ALTER TABLE `inet_serv_$mid` ADD COLUMN `parentId` INT(11) NOT NULL  AFTER `id` 
, ADD INDEX `parentId` (`parentId` ASC) 
, ADD INDEX `contractId` (`contractId` ASC) 
, ADD INDEX `deviceId` (`deviceId` ASC) ;

ALTER TABLE `inet_serv_type_$mid` ADD COLUMN `parentTypeIds` VARCHAR(80) NOT NULL DEFAULT '' AFTER `title`;

ALTER TABLE `inet_session_$mid` ADD COLUMN `parentId` BIGINT(20) NOT NULL  AFTER `id` 
, DROP INDEX `id` 
, ADD INDEX `id` (`id` ASC) 
, ADD INDEX `parentId` (`parentId` ASC) 
, ADD INDEX `connectionId` (`connectionId` ASC) ;

ALTER TABLE `inet_traffic_type_link_rule_$mid` ADD COLUMN `counterService` VARCHAR(45) NOT NULL  AFTER `diffServ` ;

CREATE TABLE IF NOT EXISTS `inet_tariff_traffic_range_$mid` (
  `contractId` int(11) NOT NULL,
  `treeNodeId` bigint(20) NOT NULL,
  `rangeKey` bigint(20) NOT NULL,
  `amount` bigint(20) NOT NULL,
  `counter` int(11) NOT NULL,
  `yy` int(11) NOT NULL,
  `mm` int(11) NOT NULL,
  PRIMARY KEY (`contractId`,`treeNodeId`,`rangeKey`,`yy`,`mm`) 
) 
/*!50106 PARTITION BY HASH ( contractId) PARTITIONS 8 */;
   
ALTER TABLE inet_tariff_traffic_max_$mid ADD COLUMN `yy` INT  NOT NULL AFTER `counter`,
ADD COLUMN `mm` INT  NOT NULL AFTER `yy`,
DROP PRIMARY KEY,
ADD PRIMARY KEY (`contractId`, `treeNodeId`, `maxKey`, `yy`, `mm`);

ALTER TABLE `inet_tariff_traffic_range_$mid`
	ADD COLUMN `yy` INT  NOT NULL AFTER `counter`,
	ADD COLUMN `mm` INT  NOT NULL AFTER `yy`,
	DROP PRIMARY KEY,
	ADD PRIMARY KEY  (`contractId`, `treeNodeId`, `rangeKey`, `yy`, `mm`);

ALTER TABLE `inet_serv_$mid` CHANGE COLUMN `vlan` `vlan` INT(11) NOT NULL DEFAULT '-1';
ALTER TABLE `inet_serv_type_$mid` ADD COLUMN `personalVlan` TINYINT(4)  NOT NULL DEFAULT 1 AFTER `config`;
ALTER TABLE `inet_serv_type_$mid` CHANGE COLUMN `personalInterface` `personalInterface` tinyint(4) NOT NULL DEFAULT '1';	
ALTER TABLE `inv_ip_resource_dyn_reserve_$mid` MODIFY COLUMN timeout BIGINT NOT NULL;
ALTER TABLE `inet_serv_$mid` ADD COLUMN `ipResourceSubscriptionId` INT NOT NULL  AFTER `ipResourceId` ;

ALTER TABLE `inet_task_process_$mid` CHANGE COLUMN `hour` `day` DATETIME  NOT NULL,
	ADD COLUMN `curentHour` INT  NOT NULL AFTER `startTime`;

ALTER TABLE `inet_session_account_$mid` ADD INDEX `sessionId`(`sessionId`);
ALTER TABLE `inet_serv_$mid` ADD KEY login(login);
ALTER TABLE `inet_accounting_period_$mid` ADD COLUMN `userId` INTEGER NOT NULL DEFAULT '0';
ALTER TABLE `inet_device_type_$mid` ADD COLUMN `deviceManagerClass` VARCHAR(250) NULL  AFTER `saHandlerClass` ;
ALTER TABLE `inet_serv_$mid` ADD COLUMN `macAddress` varbinary(8) NULL  AFTER `vlan` ;
ALTER TABLE `inet_serv_type_$mid` ADD COLUMN `needMacAddress` TINYINT(4)  NOT NULL DEFAULT 0 AFTER `needVlan`;
ALTER TABLE `inet_serv_type_$mid` ADD COLUMN `sessionCountLimitLock` TINYINT(4)  NOT NULL DEFAULT 0 AFTER `sessionCountLimit`;
ALTER TABLE `inv_device_port_$mid` ADD COLUMN `comment` VARCHAR(255) NOT NULL DEFAULT '' AFTER `ipCategoryId` ;
ALTER TABLE `inv_device_port_$mid` MODIFY COLUMN `comment` VARCHAR(255) NOT NULL DEFAULT '';  
ALTER TABLE `inet_session_detail_$mid` PARTITION BY HASH( sessionId ) PARTITIONS 8;
ALTER TABLE `inet_tariff_traffic_range_$mid` PARTITION BY HASH( contractId ) PARTITIONS 8;
ALTER TABLE `inet_tariff_traffic_max_$mid` PARTITION BY HASH( contractId ) PARTITIONS 8;
ALTER TABLE `inet_traffic_type_link_rule_$mid` ADD COLUMN `counterRealm` VARCHAR(35) NOT NULL DEFAULT '' AFTER `diffServ` ;
ALTER TABLE `inet_traffic_type_link_rule_$mid` MODIFY COLUMN `counterService` VARCHAR(100) NOT NULL  AFTER `counterRealm` ;
ALTER TABLE `inet_serv_$mid` ADD COLUMN `config` VARCHAR(255) NULL  AFTER `accessCode` ;
ALTER TABLE `inet_serv_$mid` MODIFY COLUMN `interfaceId` int(11) NOT NULL;
ALTER TABLE `inet_connection_$mid` ADD COLUMN agentDeviceId int(11) NOT NULL DEFAULT '0' AFTER `devicePort`;
ALTER TABLE `inet_session_$mid` ADD COLUMN `splittedId` BIGINT(20) NOT NULL AFTER `parentId`;
ALTER TABLE `inet_connection_$mid` CHANGE COLUMN acctSessionId acctSessionId varchar(80) DEFAULT NULL;
ALTER TABLE `inv_ip_resource_$mid` ADD COLUMN `router` VARCHAR(150) NOT NULL  AFTER `dateTo` , 
	ADD COLUMN `subnetMask` VARCHAR(150) NOT NULL  AFTER `router` , 
	ADD COLUMN `dns` VARCHAR(150) NOT NULL  AFTER `subnetMask` , 
	ADD COLUMN `config` TEXT NOT NULL  AFTER `dynamic` ;
ALTER TABLE `inet_serv_$mid` CHANGE COLUMN `macAddress` `macAddress` VARBINARY(64) NULL DEFAULT NULL  ;
ALTER TABLE `inet_serv_type_$mid` ADD COLUMN `needIdentifier` TINYINT(4)  NOT NULL DEFAULT 0 AFTER `needVlan`;
ALTER TABLE `inet_serv_$mid` ADD COLUMN `identifier` varchar(80) NULL  AFTER `vlan` ;

CREATE TABLE IF NOT EXISTS `inv_ip_resource_subscription_real_$mid` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subscriberId` int(11) NOT NULL,
  `subscriberTitle` varchar(255) DEFAULT NULL,
  `addressFrom` varbinary(24) NOT NULL,
  `addressTo` varbinary(24) NOT NULL,
  `dateFrom` date DEFAULT NULL,
  `dateTo` date DEFAULT NULL,
  PRIMARY KEY (`id`)
);

ALTER TABLE `inet_traffic_type_$mid` ADD COLUMN `unit` INT(11) NOT NULL DEFAULT 30000  AFTER `title` ;
ALTER TABLE `inet_serv_type_$mid` ADD COLUMN `addressAllInterface` TINYINT(4) NOT NULL DEFAULT '0'  AFTER `addressType` ;

-- #BLOCK#
ALTER TABLE `inv_device_port_$mid` ADD COLUMN `title` VARCHAR(80) NOT NULL DEFAULT '-'  AFTER `port` ;
UPDATE inv_device_port_$mid as port 
LEFT JOIN inet_device_$mid as device ON port.deviceId=device.id
LEFT JOIN inet_interface_$mid as iface ON device.deviceTypeId=iface.deviceTypeId AND port.port=iface.id
SET port.title=IFNULL(iface.title,'')
WHERE port.title='-' ;
-- #ENDB#

ALTER TABLE `inet_device_type_$mid` ADD COLUMN `uniqueInterfaces` TINYINT NOT NULL  AFTER `deviceManagerClass` ;

CREATE TABLE IF NOT EXISTS `inv_device_interface_index_$mid` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `deviceId` int(11) NOT NULL,
  `interfaceId` int(11) NOT NULL,
  `timeFrom` datetime DEFAULT NULL,
  `timeTo` datetime DEFAULT NULL,
  `ifIndex` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `iface` (`deviceId`,`interfaceId`)
);

ALTER TABLE `inet_task_process_$mid` ADD COLUMN `type` TINYINT NULL DEFAULT 0  AFTER `curentHour`;
-- #BLOCK#
ALTER TABLE `inv_device_port_$mid` ADD COLUMN `title` VARCHAR(80) NOT NULL DEFAULT '-'  AFTER `port` ;
UPDATE inv_device_port_$mid as port 
LEFT JOIN inet_device_$mid as device ON port.deviceId=device.id
LEFT JOIN inet_interface_$mid as iface ON device.deviceTypeId=iface.deviceTypeId AND port.port=iface.id
SET port.title=IFNULL(iface.title,'')
WHERE port.title='-' ;
-- #ENDB#

ALTER TABLE `inet_serv_$mid` CHANGE COLUMN `login` `login` varchar(100) NOT NULL;
ALTER TABLE `inet_connection_$mid` CHANGE COLUMN username username varchar(100) DEFAULT NULL;
ALTER TABLE `inet_tariff_traffic_range_$mid` ADD COLUMN `maxAmount` BIGINT(20) NOT NULL DEFAULT '-1'  AFTER `counter` ;
ALTER TABLE `inet_serv_type_$mid` ADD COLUMN `needContractObject` TINYINT(4) NOT NULL DEFAULT '0'  AFTER `needMacAddress` ;
ALTER TABLE `inet_serv_$mid`
	ADD COLUMN `contractObjectId` INT NOT NULL DEFAULT '0'  AFTER `dateTo`, 
	ADD INDEX `contractObjectId` (`contractObjectId` ASC) ;

CREATE TABLE IF NOT EXISTS `inet_serv_restriction_$mid` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `servId` INT NOT NULL ,
  `dateFrom` DATE NULL ,
  `dateTo` DATE NULL ,
  `type` INT NOT NULL ,
  `serviceIds` VARCHAR(255) NOT NULL DEFAULT '' ,
  `amount` DECIMAL(10,5) NOT NULL DEFAULT 0 ,
  `comment` VARCHAR(255) NOT NULL DEFAULT '' ,
  PRIMARY KEY (`id`) ,
  INDEX `servId` (`servId` ASC)
);
  
ALTER TABLE `inet_serv_type_$mid` ADD COLUMN `needRestriction` TINYINT(4) NOT NULL DEFAULT '0'  AFTER `needContractObject` ;
ALTER TABLE `inet_serv_restriction_$mid` CHANGE COLUMN `amount` `amount` DECIMAL(20,5) NOT NULL DEFAULT '0.00000'  ;
ALTER TABLE `inv_device_port_subscription_$mid` ADD INDEX `devicePort` (`deviceId` ASC, `port` ASC) ;
ALTER TABLE `inet_connection_$mid` ADD COLUMN `circuitId` varchar(45) NULL DEFAULT NULL  AFTER `agentDeviceId` ;
ALTER TABLE `inet_serv_type_$mid` ADD COLUMN `ipFromParentRange` TINYINT(4) NULL DEFAULT 0;
ALTER TABLE `inet_device_$mid` ADD COLUMN `dateFrom` DATE NULL  AFTER `host` , ADD COLUMN `dateTo` DATE NULL  AFTER `dateFrom` ;
ALTER TABLE `inet_device_$mid` ADD COLUMN `orderManagerDisabled` TINYINT(4) NOT NULL DEFAULT '0'  AFTER `dateTo`;

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

-- #BLOCK#
CREATE TABLE IF NOT EXISTS `inet_accounting_period_$mid_bak_sec` LIKE `inet_accounting_period_$mid`;
INSERT INTO `inet_accounting_period_$mid_bak_sec` SELECT * FROM `inet_accounting_period_$mid`;
-- #ENDB#

-- #BLOCK#
ALTER TABLE `inet_accounting_period_$mid`
	ADD COLUMN `timeFrom` DATETIME NOT NULL  AFTER `contractId` ,
	ADD COLUMN `timeTo` DATETIME NOT NULL  AFTER `timeFrom` ;
UPDATE `inet_accounting_period_$mid` 
	SET timeFrom=dateFrom, timeTo=DATE_ADD( DATE_ADD( dateTo, INTERVAL 23 HOUR), INTERVAL '59:59' MINUTE_SECOND)
	WHERE (timeFrom IS NULL OR timeFrom='0000-00-00 00:00:00') ;
ALTER TABLE `inet_accounting_period_$mid`
	CHANGE COLUMN `dateFrom` `dateFrom` DATE NULL ,
	CHANGE COLUMN `dateTo` `dateTo` DATE NULL  ;
-- #ENDB#

DELETE d.* FROM inv_ip_resource_subscription_dyn_$mid as d 
	LEFT JOIN inet_connection_$mid as c ON c.id=d.connectionId
	WHERE d.timeTo IS NULL AND c.id IS NULL AND d.timeFrom<="2015-10-05 00:00:00";

CREATE TABLE IF NOT EXISTS `inet_tariff_traffic_range_spec_$mid` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(255) NOT NULL,
  `titleForCustomer` VARCHAR(255) NOT NULL,
  `mode` TINYINT NOT NULL,
  `type` TINYINT NOT NULL,
  `tariffOptionId` INT NOT NULL,
  `maxAmount` BIGINT NOT NULL,
  `maxAmountType` TINYINT NOT NULL,
  `tariffOptionDeactivate` TINYINT NOT NULL,
  `comment` TEXT NOT NULL,
  PRIMARY KEY (`id`)
);

ALTER TABLE `inet_accounting_period_$mid` ADD KEY `ctt` (`contractId`,`timeTo`);
ALTER TABLE `inet_session_$mid`
	CHANGE COLUMN `id` `id` BIGINT(20) NOT NULL,
	DROP INDEX `id`,
	ADD PRIMARY KEY `id` (`connectionId` ASC, `id` ASC),
	DROP INDEX `connectionId`;
ALTER TABLE `inet_connection_$mid`
	CHANGE COLUMN `id` `id` BIGINT(20) NOT NULL,
	DROP INDEX `id`,
	ADD PRIMARY KEY `id` (`id` ASC, `deviceId` ASC);
ALTER TABLE `inv_device_port_subscription_$mid` ADD KEY `subscriberId` (`subscriberId`);
ALTER TABLE `inv_vlan_resource_subscription_$mid` ADD COLUMN `subscriberType` TINYINT NOT NULL DEFAULT 1 AFTER `dateTo`;
ALTER TABLE `inet_device_tree_$mid` ADD COLUMN `svlan` INT(11) NOT NULL DEFAULT -1;
ALTER TABLE `inv_ip_resource_$mid` ADD COLUMN `prefixLength` TINYINT NOT NULL DEFAULT 0  AFTER `addressTo`;
ALTER TABLE `inet_connection_$mid`
  ADD COLUMN `prefixResourceId` int(11) NOT NULL default 0 AFTER `ipAddress`,
  ADD COLUMN `prefix` varbinary(16) default NULL AFTER `prefixResourceId`,
  ADD COLUMN `prefixLength` TINYINT NOT NULL default 0 AFTER `prefix`,
  ADD COLUMN `delegatedPrefixResourceId` int(11) NOT NULL default 0 AFTER `prefixLength`,
  ADD COLUMN `delegatedPrefix` varbinary(16) default NULL AFTER `delegatedPrefixResourceId`,
  ADD COLUMN `delegatedPrefixLength` TINYINT NOT NULL default 0 AFTER `delegatedPrefix`;
ALTER TABLE `inet_serv_restriction_$mid` ADD COLUMN `config` TEXT NOT NULL DEFAULT '';
ALTER TABLE `inv_ip_category_$mid` ADD COLUMN `comment` varchar(500) NULL;
ALTER TABLE `inet_serv_type_$mid` ADD COLUMN `entitySpecId` int NOT NULL DEFAULT 0;
ALTER TABLE `inv_ip_resource_$mid` MODIFY COLUMN `prefixLength` SMALLINT NOT NULL DEFAULT 0;
ALTER TABLE `inet_serv_$mid` ADD COLUMN `data` text DEFAULT NULL;
ALTER TABLE `inet_serv_type_$mid` CHANGE COLUMN `ipVersion` `data` text DEFAULT NULL;
ALTER TABLE `inet_serv_type_$mid` ADD COLUMN `data` text DEFAULT NULL;

UPDATE `event_script_link` SET `event_key`=REPLACE(`event_key`, 'ru.bitel.bgbilling.modules.inet.api.server.event.InetServChangingEvent', 'ru.bitel.bgbilling.modules.inet.server.event.InetServChangingEvent');
UPDATE `event_script_link` SET `event_key`=REPLACE(`event_key`, 'ru.bitel.bgbilling.modules.inet.radius.RadiusAccessRequestEvent', 'ru.bitel.bgbilling.modules.inet.server.radius.RadiusAccessRequestEvent');
UPDATE `event_script_link` SET `event_key`=REPLACE(`event_key`, 'ru.bitel.bgbilling.modules.inet.api.server.event.InetAccountingPeriodActivateEvent', 'ru.bitel.bgbilling.modules.inet.server.event.InetAccountingPeriodActivateEvent');

ALTER TABLE `inet_device_type_$mid` CHANGE COLUMN `deviceEntitySpecId` `entitySpecId` int(11) NOT NULL DEFAULT 0;
ALTER TABLE `inet_device_$mid` ADD COLUMN `sort` int(11) NOT NULL DEFAULT 0 AFTER `id`;
ALTER TABLE `inet_device_tree_$mid` ADD COLUMN `sort` int(11) NOT NULL DEFAULT 0 AFTER `id`;
ALTER TABLE `inv_device_$mid` ADD COLUMN `sort` int(11) NOT NULL DEFAULT 0 AFTER `id`;

UPDATE `module_config` SET `config`=replace(`config`, 'ru.bitel.bgbilling.modules.inet.npay.InetModuleQuantity', 'ru.bitel.bgbilling.modules.npay.server.bean.quantity.InetModuleQuantity' ) WHERE `mid`=$mid;

CREATE TABLE IF NOT EXISTS `inet_session_$mid_seq` ( id bigint(20) NOT NULL AUTO_INCREMENT, PRIMARY KEY(id) );
CREATE TABLE IF NOT EXISTS `inet_connection_$mid_seq` ( id bigint(20) NOT NULL AUTO_INCREMENT, PRIMARY KEY(id) );

ALTER TABLE `inv_ip_resource_$mid` ADD COLUMN `flags` tinyint(4) NOT NULL DEFAULT 0 AFTER `dynamic`;

ALTER TABLE `inet_session_log_$mid_$yyyymm` ADD `parentId` BIGINT NOT NULL;
ALTER TABLE `inet_session_log_$mid_$yyyymm` ADD `deviceState` SMALLINT NOT NULL;
ALTER TABLE `inet_session_log_$mid_$yyyymm` ADD `deviceOptions` VARCHAR(120) NOT NULL;
ALTER TABLE `inet_session_log_$mid_$yyyymm` ADD `realm` VARCHAR(20) DEFAULT NULL;
ALTER TABLE `inet_session_log_$mid_$yyyymm` ADD `agentDeviceId` INT(11) NOT NULL DEFAULT 0;
ALTER TABLE `inet_session_log_$mid_$yyyymm` ADD `splittedId` BIGINT NOT NULL DEFAULT 0;
ALTER TABLE `inet_session_log_$mid_$yyyymm` ADD `prefix` VARBINARY(16) DEFAULT NULL;
ALTER TABLE `inet_session_log_$mid_$yyyymm` ADD `prefixLength` TINYINT NOT NULL DEFAULT 0;
ALTER TABLE `inet_session_log_$mid_$yyyymm` ADD `prefixResourceId` INT(11) NOT NULL DEFAULT 0;
ALTER TABLE `inet_session_log_$mid_$yyyymm` ADD `delegatedPrefix` VARBINARY(16) DEFAULT NULL;
ALTER TABLE `inet_session_log_$mid_$yyyymm` ADD `delegatedPrefixLength` TINYINT NOT NULL DEFAULT 0;
ALTER TABLE `inet_session_log_$mid_$yyyymm` ADD `delegatedPrefixResourceId` INT(11) NOT NULL DEFAULT 0;

ALTER TABLE `inet_session_log_detail_$mid_$yyyymm` ADD COLUMN `deviceId` INT NOT NULL AFTER `trafficTypeId`;
ALTER TABLE `inet_session_log_detail_$mid_$yyyymm` CHANGE COLUMN `day` `day` TINYINT NOT NULL;
ALTER TABLE `inet_session_log_detail_$mid_$yyyymm` CHANGE COLUMN `hour` `hour` TINYINT NOT NULL;

ALTER TABLE 'inet_auth_error_$mid_$yyyymm'
	ADD COLUMN `hash` INT NOT NULL DEFAULT '0' AFTER `servId`,
	DROP INDEX `time_dcsc`,
	ADD INDEX `time_dcsc` (`lastTime` ASC, `deviceId` ASC, `contractId` ASC, `servId` ASC, `code` ASC, `hash` ASC);
	
-- переименование задачи
UPDATE `scheduled_class` SET `class`='ru.bitel.bgbilling.modules.inet.server.task.InetServPeriodTask' WHERE `class`='ru.bitel.bgbilling.modules.inet.task.InetServPeriodTask';
UPDATE `scheduled_tasks` SET `class`='ru.bitel.bgbilling.modules.inet.server.task.InetServPeriodTask' WHERE `class`='ru.bitel.bgbilling.modules.inet.task.InetServPeriodTask';

-- события
-- #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.inet.server.event.InetAccountingPeriodActivateEvent', 1, 'Запрос учетного периода');
INSERT INTO script_event_type (mid, event_id, event_mode, title) VALUES ($mid, 'ru.bitel.bgbilling.modules.inet.server.event.InetServChangingEvent', 1, 'Изменяется сервис договора');
INSERT INTO script_event_type (mid, event_id, event_mode, title) VALUES ($mid, 'ru.bitel.bgbilling.modules.inet.server.event.InetServStateChangedEvent', 1, 'Изменилось состояние сервиса на договоре');
INSERT INTO script_event_type (mid, event_id, event_mode, title) VALUES ($mid, 'ru.bitel.bgbilling.modules.inet.server.radius.RadiusAccessRequestEvent', 1, 'Перед ответом на RADIUS Access-Request');
INSERT INTO script_event_type (mid, event_id, event_mode, title) VALUES ($mid, 'ru.bitel.bgbilling.kernel.wifi.common.event.WiFiActivateEvent', 0, 'WiFi активация');
-- #ENDB#
