# only for MySQL 5.7

DROP TABLE IF EXISTS `calls`;

CREATE TABLE `calls` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `calldate` datetime NOT NULL,
  `date` date DEFAULT NULL,
  `clid` varchar(80) NOT NULL,
  `src` varchar(80) NOT NULL,
  `dst` varchar(80) NOT NULL,
  `channel` varchar(80) NOT NULL,
  `duration` int(11) NOT NULL DEFAULT '0',
  `billsec` int(11) NOT NULL DEFAULT '0',
  `disposition` enum('FAILED','NO ANSWER','BUSY','ANSWERED') DEFAULT NULL,
  `accountcode` varchar(20) NOT NULL,
  `uniqueid` varchar(32) NOT NULL,
  `src_device_id` int(11) NOT NULL DEFAULT '0',
  `dst_device_id` int(11) NOT NULL DEFAULT '0',
  `processed` tinyint(4) NOT NULL DEFAULT '0',
  `did_price` decimal(30,15) DEFAULT '0.000000000000000',
  `card_id` int(11) DEFAULT NULL,
  `provider_id` int(11) DEFAULT NULL,
  `provider_rate` decimal(30,15) DEFAULT '0.000000000000000',
  `provider_billsec` int(11) DEFAULT NULL,
  `provider_price` decimal(30,15) DEFAULT '0.000000000000000',
  `user_id` int(11) DEFAULT NULL,
  `user_rate` decimal(30,15) DEFAULT '0.000000000000000',
  `user_billsec` int(11) DEFAULT NULL,
  `user_price` decimal(30,15) DEFAULT '0.000000000000000',
  `reseller_id` int(11) DEFAULT NULL,
  `reseller_rate` decimal(30,15) DEFAULT '0.000000000000000',
  `reseller_billsec` int(11) DEFAULT NULL,
  `reseller_price` decimal(30,15) DEFAULT '0.000000000000000',
  `partner_id` int(11) DEFAULT NULL,
  `partner_rate` decimal(30,15) DEFAULT '0.000000000000000',
  `partner_billsec` int(11) DEFAULT NULL,
  `partner_price` decimal(30,15) DEFAULT '0.000000000000000',
  `prefix` varchar(50) DEFAULT NULL,
  `server_id` int(11) DEFAULT '1',
  `hangupcause` int(11) DEFAULT NULL,
  `callertype` enum('Local','Outside') DEFAULT NULL,
  `peerip` varchar(20) DEFAULT NULL,
  `recvip` varchar(20) DEFAULT NULL,
  `sipfrom` varchar(50) DEFAULT NULL,
  `uri` varchar(50) DEFAULT NULL,
  `useragent` varchar(50) DEFAULT NULL,
  `peername` varchar(50) DEFAULT NULL,
  `t38passthrough` tinyint(4) DEFAULT NULL,
  `did_inc_price` decimal(30,15) DEFAULT '0.000000000000000',
  `did_prov_price` decimal(30,15) DEFAULT '0.000000000000000',
  `localized_dst` varchar(50) DEFAULT NULL,
  `did_provider_id` int(11) DEFAULT '0',
  `did_id` int(11) DEFAULT NULL,
  `originator_ip` varchar(20) DEFAULT NULL,
  `terminator_ip` varchar(20) DEFAULT NULL,
  `real_duration` decimal(30,15) DEFAULT '0.000000000000000',
  `real_billsec` decimal(30,15) DEFAULT '0.000000000000000',
  `did_billsec` int(11) DEFAULT '0',
  `dst_user_id` int(11) DEFAULT NULL,
  `src_user_id` int(11) DEFAULT NULL,
  `pdd` decimal(30,15) DEFAULT '0.000000000000000',
  `terminated_by` enum('unknown','originator','terminator','system') DEFAULT NULL,
  `answer_time` timestamp NULL DEFAULT NULL,
  `end_time` timestamp NULL DEFAULT NULL,
  `originator_codec` enum('unknown','G.711 A-law','G.711 u-law','G.722','G.723.1','G.726','G.729','GSM','Speex','OPUS') DEFAULT NULL,
  `terminator_codec` enum('unknown','G.711 A-law','G.711 u-law','G.722','G.723.1','G.726','G.729','GSM','Speex','OPUS') DEFAULT NULL,
  `pai` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`id`,`calldate`),
  KEY `accountcode_index` (`accountcode`),
  KEY `src_device_id_index` (`src_device_id`),
  KEY `dst_device_id_index` (`dst_device_id`),
  KEY `server_id_index` (`server_id`),
  KEY `src_index` (`src`(6)),
  KEY `dst_index` (`dst`(6)),
  KEY `localized_dst_index` (`localized_dst`(6)),
  KEY `prefix_index` (`prefix`(6)),
  KEY `provider_id_index` (`provider_id`),
  KEY `user_id_index` (`user_id`),
  KEY `src_user_id_index` (`src_user_id`),
  KEY `disposition_index` (`disposition`),
  KEY `hangupcause_index` (`hangupcause`),
  KEY `dst_user_id_index` (`dst_user_id`),
  KEY `uniqueid_index` (`uniqueid`(6)),
  KEY `calldate_index` (`calldate`),
  KEY `originator_codec_index` (`originator_codec`),
  KEY `terminator_codec_index` (`terminator_codec`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY RANGE  COLUMNS(calldate)
(PARTITION d20200101 VALUES LESS THAN ('2020-01-02') ENGINE = InnoDB,
 PARTITION future VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB);
 
 
DROP TABLE IF EXISTS `calls_old`;
 
CREATE TABLE `calls_old` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `calldate` datetime NOT NULL,
  `date` date DEFAULT NULL,
  `clid` varchar(80) NOT NULL,
  `src` varchar(80) NOT NULL,
  `dst` varchar(80) NOT NULL,
  `channel` varchar(80) NOT NULL,
  `duration` int(11) NOT NULL DEFAULT '0',
  `billsec` int(11) NOT NULL DEFAULT '0',
  `disposition` enum('FAILED','NO ANSWER','BUSY','ANSWERED') DEFAULT NULL,
  `accountcode` varchar(20) NOT NULL,
  `uniqueid` varchar(32) NOT NULL,
  `src_device_id` int(11) NOT NULL DEFAULT '0',
  `dst_device_id` int(11) NOT NULL DEFAULT '0',
  `processed` tinyint(4) NOT NULL DEFAULT '0',
  `did_price` decimal(30,15) DEFAULT '0.000000000000000',
  `card_id` int(11) DEFAULT NULL,
  `provider_id` int(11) DEFAULT NULL,
  `provider_rate` decimal(30,15) DEFAULT '0.000000000000000',
  `provider_billsec` int(11) DEFAULT NULL,
  `provider_price` decimal(30,15) DEFAULT '0.000000000000000',
  `user_id` int(11) DEFAULT NULL,
  `user_rate` decimal(30,15) DEFAULT '0.000000000000000',
  `user_billsec` int(11) DEFAULT NULL,
  `user_price` decimal(30,15) DEFAULT '0.000000000000000',
  `reseller_id` int(11) DEFAULT NULL,
  `reseller_rate` decimal(30,15) DEFAULT '0.000000000000000',
  `reseller_billsec` int(11) DEFAULT NULL,
  `reseller_price` decimal(30,15) DEFAULT '0.000000000000000',
  `partner_id` int(11) DEFAULT NULL,
  `partner_rate` decimal(30,15) DEFAULT '0.000000000000000',
  `partner_billsec` int(11) DEFAULT NULL,
  `partner_price` decimal(30,15) DEFAULT '0.000000000000000',
  `prefix` varchar(50) DEFAULT NULL,
  `server_id` int(11) DEFAULT '1',
  `hangupcause` int(11) DEFAULT NULL,
  `callertype` enum('Local','Outside') DEFAULT NULL,
  `peerip` varchar(20) DEFAULT NULL,
  `recvip` varchar(20) DEFAULT NULL,
  `sipfrom` varchar(50) DEFAULT NULL,
  `uri` varchar(50) DEFAULT NULL,
  `useragent` varchar(50) DEFAULT NULL,
  `peername` varchar(50) DEFAULT NULL,
  `t38passthrough` tinyint(4) DEFAULT NULL,
  `did_inc_price` decimal(30,15) DEFAULT '0.000000000000000',
  `did_prov_price` decimal(30,15) DEFAULT '0.000000000000000',
  `localized_dst` varchar(50) DEFAULT NULL,
  `did_provider_id` int(11) DEFAULT '0',
  `did_id` int(11) DEFAULT NULL,
  `originator_ip` varchar(20) DEFAULT NULL,
  `terminator_ip` varchar(20) DEFAULT NULL,
  `real_duration` decimal(30,15) DEFAULT '0.000000000000000',
  `real_billsec` decimal(30,15) DEFAULT '0.000000000000000',
  `did_billsec` int(11) DEFAULT '0',
  `dst_user_id` int(11) DEFAULT NULL,
  `src_user_id` int(11) DEFAULT NULL,
  `pdd` decimal(30,15) DEFAULT '0.000000000000000',
  `terminated_by` enum('unknown','originator','terminator','system') DEFAULT NULL,
  `answer_time` timestamp NULL DEFAULT NULL,
  `end_time` timestamp NULL DEFAULT NULL,
  `originator_codec` enum('unknown','G.711 A-law','G.711 u-law','G.722','G.723.1','G.726','G.729','GSM','Speex','OPUS') DEFAULT NULL,
  `terminator_codec` enum('unknown','G.711 A-law','G.711 u-law','G.722','G.723.1','G.726','G.729','GSM','Speex','OPUS') DEFAULT NULL,
  `pai` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`id`,`calldate`),
  KEY `accountcode_index` (`accountcode`),
  KEY `src_device_id_index` (`src_device_id`),
  KEY `dst_device_id_index` (`dst_device_id`),
  KEY `server_id_index` (`server_id`),
  KEY `src_index` (`src`(6)),
  KEY `dst_index` (`dst`(6)),
  KEY `localized_dst_index` (`localized_dst`(6)),
  KEY `prefix_index` (`prefix`(6)),
  KEY `provider_id_index` (`provider_id`),
  KEY `user_id_index` (`user_id`),
  KEY `src_user_id_index` (`src_user_id`),
  KEY `disposition_index` (`disposition`),
  KEY `hangupcause_index` (`hangupcause`),
  KEY `dst_user_id_index` (`dst_user_id`),
  KEY `uniqueid_index` (`uniqueid`(6)),
  KEY `calldate_index` (`calldate`),
  KEY `originator_codec_index` (`originator_codec`),
  KEY `terminator_codec_index` (`terminator_codec`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY RANGE  COLUMNS(calldate)
(PARTITION d20200101 VALUES LESS THAN ('2020-01-02') ENGINE = InnoDB,
 PARTITION future VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB);