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`)
);

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

ALTER TABLE `inv_phone_category_$mid` ADD CONSTRAINT `inv_phone_category_parent_$mid` FOREIGN KEY (`parentId`) REFERENCES `inv_phone_category_$mid` (`id`);

CREATE TABLE IF NOT EXISTS `inv_phone_resource_$mid` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `phoneFrom` bigint(20) unsigned NOT NULL,
  `phoneTo` bigint(20) unsigned NOT NULL,
  `dateFrom` date DEFAULT NULL,
  `dateTo` date DEFAULT NULL,
  `comment` text NOT NULL,
  `categoryId` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `categoryId` (`categoryId`)
);

ALTER TABLE `inv_phone_resource_$mid` ADD CONSTRAINT `fk_phone_resource_category_$mid` FOREIGN KEY (`categoryId`) REFERENCES `inv_phone_category_$mid` (`id`);

CREATE TABLE IF NOT EXISTS `inv_phone_resource_subscription_$mid` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `phoneResId` int(11) NOT NULL,
  `phone` int(18) NOT NULL,
  `dateFrom` date DEFAULT NULL,
  `dateTo` date DEFAULT NULL,
  `subscriberId` int(11) NOT NULL,
  `subscriberTitle` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `phone` (`phoneResId`,`phone`),
  KEY `period` (`dateFrom`,`dateTo`),
  KEY `subscriber` (`subscriberId`)
);

ALTER TABLE `inv_phone_resource_subscription_$mid` ADD CONSTRAINT `fk_inv_phone_resource_subscription_resource_$mid` FOREIGN KEY (`phoneResId`) REFERENCES `inv_phone_resource_$mid` (`id`);

CREATE TABLE IF NOT EXISTS `voice_account_$mid` (
  `id` int(11) NOT NULL,
  `number` varchar(20) DEFAULT NULL,
  `comment` varchar(256) DEFAULT NULL,
  `status` tinyint(4) DEFAULT NULL,
  `deviceState` tinyint(4) NOT NULL,
  `accessCode` int(11) NOT NULL DEFAULT '-1',
  `login` varchar(45) DEFAULT NULL,
  `password` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
);
ALTER TABLE `voice_account_$mid` ADD CONSTRAINT `fk_voice_account_base_$mid` FOREIGN KEY (`id`) REFERENCES `voice_account_base_$mid` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

CREATE TABLE IF NOT EXISTS `voice_account_base_$mid` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `contractId` int(10) unsigned NOT NULL,
  `dateFrom` date DEFAULT NULL,
  `dateTo` date DEFAULT NULL,
  `typeId` int(11) NOT NULL,
  `deviceId` int(11) NOT NULL,
  `title` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `deviceId` (`deviceId`),
  KEY `typeId` (`typeId`),
  KEY `contractId` (`contractId`)
);
ALTER TABLE `voice_account_base_$mid` ADD CONSTRAINT `fk_voice_account_base_contract_$mid` FOREIGN KEY (`contractId`) REFERENCES `contract` (`id`);
ALTER TABLE `voice_account_base_$mid` ADD CONSTRAINT `fk_voice_account_base_device_$mid` FOREIGN KEY (`deviceId`) REFERENCES `voice_device_$mid` (`id`);
ALTER TABLE `voice_account_base_$mid` ADD CONSTRAINT `fk_voice_account_base_type_$mid` FOREIGN KEY (`typeId`) REFERENCES `voice_account_type_$mid` (`id`);

CREATE TABLE IF NOT EXISTS `voice_account_port_$mid` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `accountId` int(11) NOT NULL DEFAULT '0',
  `port` varchar(20) NOT NULL DEFAULT '0',
  `is_out` bit(1) NOT NULL DEFAULT b'0',
  PRIMARY KEY (`id`),
  KEY `accountId` (`accountId`),
  KEY `port` (`port`)
);
ALTER TABLE `voice_account_port_$mid` ADD CONSTRAINT `fk_voice_account_port_voice_account_base_$mid` FOREIGN KEY (`accountId`) REFERENCES `voice_account_base_$mid` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

CREATE TABLE IF NOT EXISTS `voice_account_type_$mid` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(128) DEFAULT NULL,
  `checkPassword` bit(1) DEFAULT NULL,
  `needPhone` bit(1) DEFAULT NULL,
  `needLogin` bit(1) DEFAULT NULL,
  `config` mediumtext,
  `needFromPorts` bit(1) DEFAULT NULL,
  `needToPorts` bit(1) DEFAULT NULL,
  `needRegexpA` bit(1) DEFAULT NULL,
  `needNoRegexpA` bit(1) DEFAULT NULL,
  `needRegexpB` bit(1) DEFAULT NULL,
  `needNoRegexpB` bit(1) DEFAULT NULL,
  `needRegexpFromPorts` bit(1) DEFAULT NULL,
  `needRegexpToPorts` bit(1) DEFAULT NULL,
  `isOperator` bit(1) NOT NULL,
  PRIMARY KEY (`id`)
);
ALTER TABLE voice_account_type_$mid ADD COLUMN `isOperator` bit(1) NOT NULL;
ALTER TABLE voice_account_type_$mid ADD COLUMN `needPhone` bit(1) DEFAULT NULL;

CREATE TABLE IF NOT EXISTS `voice_code_destination_$mid` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `codeId` int(11) NOT NULL,
  `destinationId` int(11) NOT NULL,
  `dateFrom` date DEFAULT NULL,
  `dateTo` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `codeId` (`codeId`),
  KEY `destinationId` (`destinationId`)
);
ALTER TABLE `voice_code_destination_$mid` ADD CONSTRAINT `fk_voice_code_destination_dest_$mid` FOREIGN KEY (`destinationId`) REFERENCES `voice_destination_$mid` (`id`);
ALTER TABLE `voice_code_destination_$mid` ADD CONSTRAINT `voice_code_destination_code_$mid` FOREIGN KEY (`codeId`) REFERENCES `voice_geographic_code_$mid` (`id`);

CREATE TABLE IF NOT EXISTS `voice_cost_map_$mid` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(128) NOT NULL,
  `comment` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `voice_cost_map_item_$mid` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `costMapId` int(11) NOT NULL,
  `dateFrom` date DEFAULT NULL,
  `dateTo` date DEFAULT NULL,
  `cost` decimal(10,2) NOT NULL,
  `codeId` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `costMapId` (`costMapId`)
);
ALTER TABLE `voice_cost_map_item_$mid` ADD CONSTRAINT `fk_voice_cost_map_item_cost_map_$mid` FOREIGN KEY (`costMapId`) REFERENCES `voice_cost_map_$mid` (`id`);

CREATE TABLE IF NOT EXISTS `voice_destination_$mid` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(256) NOT NULL,
  `operatorId` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `operatorId` (`operatorId`)
);
ALTER TABLE `voice_destination_$mid` ADD CONSTRAINT `fk_voice_destination_operator_$mid` FOREIGN KEY (`operatorId`) REFERENCES `voice_operator_$mid` (`id`);

CREATE TABLE IF NOT EXISTS `voice_device_$mid` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `entityId` int(11) NOT NULL,
  `title` varchar(128) DEFAULT NULL,
  `identifier` varchar(128) DEFAULT NULL,
  `parentId` int(11) DEFAULT NULL,
  `comment` mediumtext,
  `dateFrom` datetime DEFAULT NULL,
  `dateTo` datetime DEFAULT NULL,
  `config` mediumtext,
  `host` mediumtext,
  `logPath` varchar(128) DEFAULT NULL,
  `deviceTypeId` int(11) DEFAULT NULL,
  `secret` varchar(100) DEFAULT NULL,
  `username` varchar(100) NOT NULL,
  `password` varchar(100) NOT NULL,
  `uptime` text NOT NULL,
  `uptimeTime` text,
  `orderManagerDisabled` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `deviceTypeId` (`deviceTypeId`)
);
ALTER TABLE `voice_device_$mid` ADD CONSTRAINT `fk_voice_device_type_$mid` FOREIGN KEY (`deviceTypeId`) REFERENCES `voice_device_type_$mid` (`id`);

CREATE TABLE IF NOT EXISTS `voice_device_type_$mid` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(256) DEFAULT NULL,
  `mediationClass` varchar(256) DEFAULT NULL,
  `protocolHandlerClass` varchar(250) DEFAULT NULL,
  `omHandlerClass` varchar(250) DEFAULT NULL,
  `nas` tinyint(4) DEFAULT NULL,
  `comment` text,
  `config` text,
  `mediationRuntimeClass` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `voice_geographic_code_$mid` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(12) NOT NULL,
  `startLevel` tinyint(4) DEFAULT '0',
  `stopLevel` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `voice_oper_account_$mid` (
  `id` int(11) NOT NULL,
  `regexpFromPorts` varchar(45) DEFAULT NULL,
  `regexpToPorts` varchar(45) DEFAULT NULL,
  `regexpA` varchar(45) DEFAULT NULL,
  `noRegexpA` varchar(45) DEFAULT NULL,
  `regexpB` varchar(45) DEFAULT NULL,
  `noRegexpB` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
);
ALTER TABLE `voice_oper_account_$mid` ADD CONSTRAINT `FK_voice_oper_account_$mid_voice_account_base_$mid` FOREIGN KEY (`id`) REFERENCES `voice_account_base_$mid` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

CREATE TABLE IF NOT EXISTS `voice_operator_$mid` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(45) NOT NULL,
  `contractId` int(11) unsigned NOT NULL,
  `comment` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `contractId` (`contractId`)
);
ALTER TABLE `voice_operator_$mid` ADD CONSTRAINT `fk_voice_operator_contract_$mid` FOREIGN KEY (`contractId`) REFERENCES `contract` (`id`);

CREATE TABLE IF NOT EXISTS `voice_process_task_$mid` (
  `deviceId` int(11) NOT NULL,
  `day` datetime NOT NULL,
  `currenthour` SMALLINT NOT NULL DEFAULT '0',
  UNIQUE KEY `device_hour` (`deviceId`,`day`)
);
ALTER TABLE `voice_process_task_$mid` ADD CONSTRAINT `fk_voice_process_task_device_$mid` FOREIGN KEY (`deviceId`) REFERENCES `voice_device_$mid` (`id`);

CREATE TABLE IF NOT EXISTS `voice_session_$mid` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `contractId` int(10) unsigned NOT NULL,
  `accountId` int(11) DEFAULT '0',
  `sessionStart` datetime DEFAULT NULL,
  `sessionStop` datetime DEFAULT NULL,
  `sessionCost` decimal(10,5) DEFAULT NULL,
  `hour` datetime NOT NULL,
  `callingStationId` varchar(45) NOT NULL,
  `calledStationId` varchar(45) NOT NULL,
  `e164CallingStationId` varchar(45) DEFAULT NULL,
  `e164CalledStationId` varchar(45) NOT NULL,
  `lastActive` datetime DEFAULT NULL,
  `deviceId` int(11) NOT NULL,
  `sessionTime` int(11) NOT NULL,
  `identifier` varchar(50) DEFAULT NULL,
  `destinationId` int(11) NOT NULL,
  `callType` tinyint(4) NOT NULL,
  `acctSessionId` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `deviceId` (`deviceId`),
  KEY `accountId` (`accountId`),
  KEY `destinationId` (`destinationId`),
  KEY `contractId` (`contractId`)
);
ALTER TABLE `voice_session_$mid` ADD CONSTRAINT `fk_voice_session_account_base_$mid` FOREIGN KEY (`accountId`) REFERENCES `voice_account_base_$mid` (`id`);
ALTER TABLE `voice_session_$mid` ADD CONSTRAINT `fk_voice_session_contract_$mid` FOREIGN KEY (`contractId`) REFERENCES `contract` (`id`);
ALTER TABLE `voice_session_$mid` ADD CONSTRAINT `fk_voice_session_destination_$mid` FOREIGN KEY (`destinationId`) REFERENCES `voice_destination_$mid` (`id`);
ALTER TABLE `voice_session_$mid` ADD CONSTRAINT `fk_voice_session_device_$mid` FOREIGN KEY (`deviceId`) REFERENCES `voice_device_$mid` (`id`);

CREATE TABLE IF NOT EXISTS `voice_session_$mid_seq` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `voice_session_account_$mid` (
  `contractId` int(10) unsigned NOT NULL,
  `sessionId` int(11) NOT NULL,
  `serviceId` int(10) unsigned NOT NULL,
  `account` decimal(10,5) NOT NULL,
  `amount` bigint(20) DEFAULT NULL,
  `hour` datetime NOT NULL,
  `deviceId` int(11) NOT NULL,
  PRIMARY KEY (`contractId`,`sessionId`,`serviceId`),
  KEY `sessionId` (`sessionId`),
  KEY `serviceId` (`serviceId`),
  KEY `deviceId` (`deviceId`)
);
ALTER TABLE `voice_session_account_$mid` ADD CONSTRAINT `fk_session_account_deviceId_$mid` FOREIGN KEY (`deviceId`) REFERENCES `voice_device_$mid` (`id`);
ALTER TABLE `voice_session_account_$mid` ADD CONSTRAINT `fk_session_account_contract_$mid` FOREIGN KEY (`contractId`) REFERENCES `contract` (`id`);
ALTER TABLE `voice_session_account_$mid` ADD CONSTRAINT `fk_session_account_service_$mid` FOREIGN KEY (`serviceId`) REFERENCES `service` (`id`);
ALTER TABLE `voice_session_account_$mid` ADD CONSTRAINT `fk_session_account_session_$mid` FOREIGN KEY (`sessionId`) REFERENCES `voice_session_$mid` (`id`);

CREATE TABLE IF NOT EXISTS `voice_tariff_traffic_range_$mid` (
  `contractId` int(10) unsigned NOT NULL,
  `treeNodeId` bigint(20) NOT NULL,
  `rangeKey` bigint(20) NOT NULL,
  `amount` bigint(20) NOT NULL,
  `counter` int(11) NOT NULL,
  `maxAmount` bigint(20) NOT NULL DEFAULT '-1',
  `yy` int(11) NOT NULL,
  `mm` int(11) NOT NULL,
  PRIMARY KEY (`contractId`,`treeNodeId`,`rangeKey`,`yy`,`mm`)
)
/*!50100 PARTITION BY HASH ( contractId)
PARTITIONS 8 */;
CREATE TABLE IF NOT EXISTS `voice_tariff_zone_$mid` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL DEFAULT '''',
  PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `voice_zone_map_$mid` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `voice_zone_map_item_$mid` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mapId` int(11) NOT NULL,
  `codeId` int(11) DEFAULT NULL,
  `zoneId` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `mapId` (`mapId`),
  KEY `codeId` (`codeId`),
  KEY `zoneId` (`zoneId`)
);
ALTER TABLE `voice_zone_map_item_$mid` ADD CONSTRAINT `fk_zone_map_item_zone_map_$mid` FOREIGN KEY (`mapId`) REFERENCES `voice_zone_map_$mid` (`id`);
ALTER TABLE `voice_zone_map_item_$mid` ADD CONSTRAINT `fk_voice_zone_map_item_geographic_code_$mid` FOREIGN KEY (`codeId`) REFERENCES `voice_geographic_code_$mid` (`id`);
ALTER TABLE `voice_zone_map_item_$mid` ADD CONSTRAINT `fk_voice_zone_map_item_tariff_zone_$mid` FOREIGN KEY (`zoneId`) REFERENCES `voice_tariff_zone_$mid` (`id`);

CREATE TABLE IF NOT EXISTS `voice_option_$mid` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(150) NOT NULL,
    `config` MEDIUMTEXT NOT NULL,
    `comment` MEDIUMTEXT NOT NULL,
    PRIMARY KEY (`id`)
);

ALTER TABLE `voice_session_$mid` ADD COLUMN `sessionTimeRound` INT(11) NOT NULL AFTER `sessionTime`;
ALTER TABLE `voice_account_$mid` ADD COLUMN `sessionCountLimit` TINYINT NOT NULL DEFAULT '0' AFTER `password`;

ALTER TABLE `voice_account_type_$mid`
	ADD COLUMN `sessionCountLimit` TINYINT NOT NULL DEFAULT '0' AFTER `isOperator`,
	ADD COLUMN `sessionCountLimitLock` BIT NOT NULL DEFAULT b'0' AFTER `sessionCountLimit`;

ALTER TABLE `voice_session_$mid` CHANGE COLUMN `destinationId` `destinationId` INT(11) NULL AFTER `identifier`;

-- #BLOCK#
ALTER TABLE `voice_session_account_$mid` DROP FOREIGN KEY `fk_session_account_session_$mid`;
ALTER TABLE `voice_session_$mid` ALTER `id` DROP DEFAULT;
ALTER TABLE `voice_session_$mid` CHANGE COLUMN `id` `id` BIGINT(20) NOT NULL FIRST;
ALTER TABLE `voice_session_account_$mid` ALTER `sessionId` DROP DEFAULT;
ALTER TABLE `voice_session_account_$mid` CHANGE COLUMN `sessionId` `sessionId` BIGINT(20) NOT NULL AFTER `contractId`;
ALTER TABLE `voice_session_account_$mid` ADD CONSTRAINT `fk_session_account_session_$mid` FOREIGN KEY (`sessionId`) REFERENCES `voice_session_$mid` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `voice_session_$mid_seq` CHANGE COLUMN `id` `id` BIGINT(20) NOT NULL AUTO_INCREMENT FIRST;
-- #ENDB#

ALTER TABLE `inv_phone_resource_subscription_$mid` CHANGE COLUMN `phone` `phone` BIGINT(20) NOT NULL AFTER `phoneResId`;

-- события
-- #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.voice.server.radius.RadiusAccessRequestEvent', 1, 'Перед ответом на RADIUS Access-Request');
-- #ENDB#

ALTER TABLE `voice_cost_map_item_$mid` CHANGE COLUMN `cost` `cost` decimal(10,5) NOT NULL;

-- #BLOCK#
ALTER TABLE `voice_cost_map_item_$mid` DROP FOREIGN KEY `fk_voice_cost_map_item_cost_map_$mid`;
ALTER TABLE `voice_cost_map_item_$mid` ADD CONSTRAINT `fk_voice_cost_map_item_cost_map_$mid` FOREIGN KEY (`costMapId`) REFERENCES `voice_cost_map_$mid` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
-- #ENDB#

-- #BLOCK#
ALTER TABLE `voice_session_$mid` ADD COLUMN `parentId` BIGINT(20) NULL DEFAULT NULL AFTER `acctSessionId`;
ALTER TABLE `voice_session_$mid` ADD COLUMN `type` INT(11) NULL DEFAULT NULL AFTER `parentId`;
ALTER TABLE `voice_session_$mid` ADD CONSTRAINT `fk_voice_session_parent_$mid` FOREIGN KEY (`parentId`) REFERENCES `voice_session_$mid` (`id`);
-- #ENDB#

-- #BLOCK#
ALTER TABLE `voice_zone_map_$mid` ALTER `title` DROP DEFAULT;
ALTER TABLE `voice_zone_map_$mid` CHANGE COLUMN `title` `title` VARCHAR(100) NOT NULL AFTER `id`,
ADD COLUMN `comment` VARCHAR(255) NULL AFTER `title`;
-- #ENDB#

-- #BLOCK#
ALTER TABLE `voice_account_base_$mid` DROP FOREIGN KEY `fk_voice_account_base_contract_$mid`;
ALTER TABLE `voice_account_base_$mid` ADD CONSTRAINT `fk_voice_account_base_contract_$mid` FOREIGN KEY (`contractId`) REFERENCES `contract` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `inv_phone_resource_subscription_$mid` ADD CONSTRAINT `fk_inv_phone_resource_subscription_account_$mid` FOREIGN KEY (`subscriberId`) REFERENCES `voice_account_base_$mid` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE `inv_phone_resource_subscription_$mid` DROP FOREIGN KEY `fk_inv_phone_resource_subscription_resource_$mid`;
ALTER TABLE `inv_phone_resource_subscription_$mid` ADD CONSTRAINT `fk_inv_phone_resource_subscription_resource_$mid` FOREIGN KEY (`phoneResId`) REFERENCES `inv_phone_resource_$mid` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;    
-- #ENDB#

ALTER TABLE `voice_account_type_$mid` ADD COLUMN `needDevice` BIT(1) NULL DEFAULT b'1' AFTER `sessionCountLimitLock`;

ALTER TABLE `voice_account_type_$mid` ADD COLUMN `needPorts` BIT(1) NULL AFTER `needDevice`;
ALTER TABLE `voice_oper_account_$mid` ADD COLUMN `callType` TINYINT NULL DEFAULT '1' AFTER `noRegexpB`;

ALTER TABLE `voice_session_$mid` ADD COLUMN `fromPort` VARCHAR(50) NULL DEFAULT NULL AFTER `type`,
ADD COLUMN `toPort` VARCHAR(50) NULL DEFAULT NULL AFTER `fromPort`;

ALTER TABLE `voice_device_type_$mid` ADD COLUMN `source` TINYINT(4) NULL DEFAULT NULL AFTER `nas`;

ALTER TABLE `voice_geographic_code_$mid`  ADD COLUMN `operatorId` INT NULL AFTER `stopLevel`;
ALTER TABLE `voice_geographic_code_$mid`  ADD CONSTRAINT `fk_geocode_operator_217` FOREIGN KEY (`operatorId`) REFERENCES `voice_operator_$mid` (`id`);
    
ALTER TABLE `voice_cost_map_$mid` ADD COLUMN `operatorId` INT NULL AFTER `comment`;
ALTER TABLE `voice_cost_map_$mid` ADD CONSTRAINT `fr_costmap_operator_217` FOREIGN KEY (`operatorId`) REFERENCES `voice_operator_$mid` (`id`);

ALTER TABLE `voice_operator_$mid` ADD COLUMN `useCustomGeoDict` BIT NULL AFTER `comment`;    
    
ALTER TABLE `voice_cost_map_item_$mid` ADD CONSTRAINT `fk_voice_cost_map_item_geographic_code_$mid` FOREIGN KEY (`codeId`) REFERENCES `voice_geographic_code_$mid` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE `voice_destination_$mid` MODIFY title VARCHAR(100) NOT NULL;
CREATE UNIQUE INDEX `voice_destination_$mid_title_operatorId_uindex` ON `voice_destination_$mid` (title, operatorId);

CREATE TABLE IF NOT EXISTS `voice_process_data_$mid` (
  day      DATE NULL,
  deviceId INT  NULL,
  CONSTRAINT voice_process_data_day_deviceId_uindex
  UNIQUE (day, deviceId)
);

ALTER TABLE `voice_process_task_$mid` ADD COLUMN `radius` BIT NOT NULL DEFAULT b'0' AFTER `currenthour`;

ALTER TABLE `voice_oper_account_$mid` ADD COLUMN `serviceId` INT(10) UNSIGNED NULL DEFAULT NULL AFTER `callType`;
ALTER TABLE `voice_oper_account_$mid`ADD CONSTRAINT `FK_voice_oper_account_$mid_service` FOREIGN KEY (`serviceId`) REFERENCES `service` (`id`);

ALTER TABLE `voice_account_type_$mid` ADD COLUMN `needObject` BIT(1) NULL DEFAULT NULL AFTER `needPorts`;
ALTER TABLE `voice_account_$mid` ADD COLUMN `contractObjectId` INT NULL AFTER `sessionCountLimit`;
ALTER TABLE `voice_account_$mid` ADD CONSTRAINT `fk_account_object_$mid` FOREIGN KEY (`contractObjectId`) REFERENCES `object` (`id`);

ALTER TABLE `voice_session_$mid` ADD COLUMN `codeId` INT(11) NULL DEFAULT NULL;
ALTER TABLE `voice_session_$mid` ADD COLUMN `costMapItemId` INT(11) NULL DEFAULT NULL;
ALTER TABLE `voice_session_$mid` ADD COLUMN `serviceId` INT(10) UNSIGNED NULL AFTER `costMapItemId`;
ALTER TABLE `voice_session_$mid` ADD CONSTRAINT `fk_voice_session_service_$mid` FOREIGN KEY (`serviceId`) REFERENCES `service` (`id`);
ALTER TABLE `voice_session_$mid` ADD COLUMN `minuteCost` DECIMAL(13,5) UNSIGNED NULL DEFAULT NULL AFTER `serviceId`;

ALTER TABLE `voice_session_$mid`
	ADD COLUMN `cdrCallCost` DECIMAL(13,5) NULL DEFAULT NULL AFTER `minuteCost`,
	ADD COLUMN `cdrCallOperCost` DECIMAL(13,5) NULL DEFAULT NULL AFTER `cdrCallCost`;

ALTER TABLE `voice_device_type_$mid` ADD COLUMN `deviceEntitySpecId` int(11) NOT NULL DEFAULT 0 AFTER `title`;
ALTER TABLE `voice_device_type_$mid` CHANGE COLUMN `deviceEntitySpecId` `entitySpecId` int(11) NOT NULL DEFAULT 0;
ALTER TABLE `voice_device_type_$mid` ADD COLUMN `deviceManagerClass` varchar(250) DEFAULT NULL AFTER `omHandlerClass`;
ALTER TABLE `voice_process_task_$mid` MODIFY COLUMN `currentHour` SMALLINT NOT NULL DEFAULT '0';
ALTER TABLE `voice_account_type_$mid` ADD COLUMN `entitySpecId` int(11) NOT NULL DEFAULT 0;
ALTER TABLE `voice_account_type_$mid` ADD COLUMN `needLines` bit(1) DEFAULT b'0' AFTER `needObject`;
ALTER TABLE `voice_device_$mid` ADD COLUMN `sort` int(11) NOT NULL DEFAULT 0 AFTER `id`;
ALTER TABLE `voice_account_$mid` ADD COLUMN `lineCount` int(11) NOT NULL DEFAULT 1 AFTER `number`;

ALTER TABLE `voice_account_type_$mid` ADD COLUMN `statusConfig` text DEFAULT NULL AFTER `config`;

CREATE TABLE IF NOT EXISTS `voice_session_detail_order_$mid` (
  `create_date` datetime DEFAULT NULL,
  `status` varchar(10) DEFAULT NULL,
  `account_id` int(11) NOT NULL,
  `month` int(11) NOT NULL,
  `year` int(11) NOT NULL,
  `format_type` varchar(10) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `email_send` varchar(20) DEFAULT NULL,
  `file_size` bigint(20) NOT NULL,
  PRIMARY KEY (`account_id`,`month`,`year`)
);

ALTER TABLE `voice_session_detail_order_$mid` ADD COLUMN `contract_id` int(11) NOT NULL AFTER `status`;

CREATE TABLE IF NOT EXISTS `voice_zone_cost_map_$mid` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `comment` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `voice_zone_cost_map_item_$mid` (
  `zone_cost_map_id` int(11) NOT NULL DEFAULT 0,
  `zone_id` int(11) NOT NULL DEFAULT 0,
  `cost` decimal(15,2) NOT NULL DEFAULT 0.00,
  `cost_second` int(11) NOT NULL DEFAULT 60,
  KEY `zone_cost_map_id` (`zone_cost_map_id`),
  KEY `zone_id` (`zone_id`)
);

CREATE TABLE IF NOT EXISTS `voice_geographic_range_$mid` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `codeFrom` bigint(20) NOT NULL DEFAULT 0,
  `codeTo` bigint(20) NOT NULL DEFAULT 0,
  `destinationId` int(11) NOT NULL DEFAULT 0,
  `operatorId` int(11) DEFAULT NULL,
  `mobileId` varchar(15) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `codeFrom` (`codeFrom`),
  KEY `mobileId` (`mobileId`),
  KEY `destinationId` (`destinationId`),
  KEY `mobileId_2` (`mobileId`)
);

CREATE TABLE IF NOT EXISTS `voice_cost_map_range_$mid` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cost_map_id` int(11) NOT NULL DEFAULT 0,
  `range_id` int(11) NOT NULL DEFAULT 0,
  `date_from` date DEFAULT NULL,
  `date_to` date DEFAULT NULL,
  `cost` decimal(12,2) NOT NULL DEFAULT 0.00,
  `comment` varchar(512) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `cost_map_id` (`cost_map_id`,`date_from`),
  KEY `range_id` (`range_id`)
);

ALTER TABLE `voice_session_$mid` ADD COLUMN `uploadTime` timestamp NULL DEFAULT current_timestamp() AFTER `toPort`;

-- переименование задачи
UPDATE `scheduled_tasks` SET class='ru.bitel.bgbilling.modules.voice.server.task.VoiceAccountPeriodTask' WHERE class='ru.bitel.bgbilling.modules.voice.task.VoiceAccountPeriodTask';
UPDATE `scheduled_tasks` SET class='ru.bitel.bgbilling.modules.voice.server.task.VoiceTaskGenerator' WHERE class='ru.bitel.bgbilling.modules.voice.task.VoiceTaskGenerator';
UPDATE `scheduled_tasks` SET class='ru.bitel.bgbilling.apps.voice.accounting.task.VoiceBalanceTask' WHERE class='ru.bitel.bgbilling.modules.voice.accounting.task.VoiceBalanceTask';
UPDATE `scheduled_tasks` SET class='ru.bitel.bgbilling.modules.voice.server.task.VoiceBalanceTask' WHERE class='ru.bitel.bgbilling.apps.voice.accounting.task.VoiceBalanceTask';
