CooPaging/etc/asterisk/sysconf/database/coopaging_db.sql

--
-- Host: localhost    Database: coopaging_db
-- ------------------------------------------------------
--

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `t_exinfo`
--

DROP TABLE IF EXISTS `t_exinfo`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t_exinfo` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `hostname` varchar(20) NOT NULL,
  `macaddr` varchar(20) NOT NULL,
  `mode` varchar(20) NOT NULL,
  `ip` varchar(20) NOT NULL,
  `netmask` varchar(20) NOT NULL,
  `gateway` varchar(20) NOT NULL,
  `publicip` varchar(20) NOT NULL DEFAULT '',
  `port` varchar(20) NOT NULL DEFAULT '9999',
  `fxssendkey` varchar(6) NOT NULL DEFAULT '#',
  `onlinestatus` int(6) unsigned NOT NULL DEFAULT '0',
  `username` varchar(20) NOT NULL DEFAULT '',
  `password` varchar(20) NOT NULL DEFAULT '',
  `configstatus` int(10) unsigned NOT NULL DEFAULT '0',
  `everconfigstatus` varchar(6) NOT NULL DEFAULT 'no',
  `portifavailable` varchar(80) NOT NULL DEFAULT '',
  `_F1` varchar(128) NOT NULL DEFAULT 'no',
  `_F2` varchar(128) NOT NULL DEFAULT '',
  `_F3` varchar(128) NOT NULL DEFAULT '',
  `_F4` varchar(128) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `ip` (`ip`),
  UNIQUE KEY `macaddr` (`macaddr`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t_exinfo`
--

LOCK TABLES `t_exinfo` WRITE;
/*!40000 ALTER TABLE `t_exinfo` DISABLE KEYS */;
/*!40000 ALTER TABLE `t_exinfo` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `t_pbx_inbound_did`
--

DROP TABLE IF EXISTS `t_pbx_inbound_did`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t_pbx_inbound_did` (
  `_id` int(16) NOT NULL AUTO_INCREMENT,
  `_didnumber` varchar(32) NOT NULL,
  `_destexten` varchar(128) NOT NULL,
  PRIMARY KEY (`_id`),
  UNIQUE KEY `_didnumber` (`_didnumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t_pbx_inbound_did`
--

LOCK TABLES `t_pbx_inbound_did` WRITE;
/*!40000 ALTER TABLE `t_pbx_inbound_did` DISABLE KEYS */;
/*!40000 ALTER TABLE `t_pbx_inbound_did` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `t_pbx_number_dod`
--

DROP TABLE IF EXISTS `t_pbx_number_dod`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t_pbx_number_dod` (
  `_id` int(16) NOT NULL AUTO_INCREMENT,
  `_dodnumber` varchar(32) NOT NULL,
  `_dodtrunk` varchar(128) NOT NULL,
  PRIMARY KEY (`_id`),
  UNIQUE KEY `_dodnumber` (`_dodnumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t_pbx_number_dod`
--

LOCK TABLES `t_pbx_number_dod` WRITE;
/*!40000 ALTER TABLE `t_pbx_number_dod` DISABLE KEYS */;
/*!40000 ALTER TABLE `t_pbx_number_dod` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `t_pbx_recording`
--

DROP TABLE IF EXISTS `t_pbx_recording`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t_pbx_recording` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `src` varchar(64) DEFAULT NULL,
  `dest` varchar(64) DEFAULT NULL,
  `call_type` varchar(64) DEFAULT NULL,
  `record_start_time` double DEFAULT NULL,
  `record_len` double DEFAULT NULL,
  `record_filename` varchar(255) DEFAULT NULL,
  `createdAt` varchar(64) DEFAULT NULL,
  `updatedAt` varchar(64) DEFAULT NULL,
  `uniqueid` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `note` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `record_filename` (`record_filename`)
) ENGINE=InnoDB;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t_pbx_recording`
--

LOCK TABLES `t_pbx_recording` WRITE;
/*!40000 ALTER TABLE `t_pbx_recording` DISABLE KEYS */;
/*!40000 ALTER TABLE `t_pbx_recording` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `t_pbx_users_extension`
--

DROP TABLE IF EXISTS `t_pbx_users_extension`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t_pbx_users_extension` (
  `_id` int(16) NOT NULL AUTO_INCREMENT,
  `_exten` varchar(32) NOT NULL,
  `_cid_number` varchar(32) NOT NULL DEFAULT '',
  `_transfer` varchar(3) NOT NULL DEFAULT 'yes',
  `_mailbox` varchar(16) NOT NULL DEFAULT '',
  `_type` varchar(6) NOT NULL DEFAULT 'peer',
  `_qualify` varchar(4) NOT NULL DEFAULT 'yes',
  `_hasdirectory` varchar(3) NOT NULL DEFAULT 'yes',
  `_call-limit` varchar(2) NOT NULL DEFAULT '5',
  `_host` varchar(16) NOT NULL DEFAULT 'dynamic',
  `_context` varchar(32) NOT NULL DEFAULT 'default',
  `_fullname` varchar(64) NOT NULL DEFAULT '',
  `_secret` varchar(128) NOT NULL DEFAULT '',
  `_outboundcid` varchar(32) NOT NULL DEFAULT '',
  `_hasvoicemail` varchar(3) NOT NULL DEFAULT 'yes',
  `_vmsecret` varchar(32) NOT NULL DEFAULT '',
  `_delete` varchar(3) NOT NULL DEFAULT 'no',
  `_email` varchar(64) NOT NULL DEFAULT '',
  `_hassip` varchar(3) NOT NULL DEFAULT 'no',
  `_hasiax` varchar(3) NOT NULL DEFAULT 'no',
  `_dahdichan` varchar(3) NOT NULL DEFAULT '',
  `_hasmanager` varchar(3) NOT NULL DEFAULT 'no',
  `_hasexten` varchar(3) NOT NULL DEFAULT 'yes',
  `_nat` varchar(32) NOT NULL DEFAULT 'no',
  `_directmedia` varchar(3) NOT NULL DEFAULT 'no',
  `_dtmfmode` varchar(8) NOT NULL DEFAULT 'rfc2833',
  `_hasagent` varchar(3) NOT NULL DEFAULT 'no',
  `_callwaiting` varchar(3) NOT NULL DEFAULT 'yes',
  `_callgroup` varchar(32) NOT NULL DEFAULT '1',
  `_pickupgroup` varchar(32) NOT NULL DEFAULT '1',
  `_videosupport` varchar(3) NOT NULL DEFAULT 'no',
  `_transport` varchar(11) NOT NULL DEFAULT 'udp',
  `_encryption` varchar(3) NOT NULL DEFAULT 'no',
  `_srtpcapable` varchar(3) NOT NULL DEFAULT 'no',
  `_deny` varchar(64) NOT NULL DEFAULT '',
  `_permit` varchar(124) NOT NULL DEFAULT '',
  `_ulaw` int(1) NOT NULL DEFAULT '1',
  `_alaw` int(1) NOT NULL DEFAULT '1',
  `_g722` int(1) NOT NULL DEFAULT '0',
  `_g729` int(1) NOT NULL DEFAULT '0',
  `_g726` int(1) NOT NULL DEFAULT '0',
  `_gsm` int(1) NOT NULL DEFAULT '0',
  `_speex` int(1) NOT NULL DEFAULT '0',
  `_h261` int(1) NOT NULL DEFAULT '0',
  `_h263` int(1) NOT NULL DEFAULT '0',
  `_h263p` int(1) NOT NULL DEFAULT '0',
  `_h264` int(1) NOT NULL DEFAULT '0',
  `_vp8` int(4) DEFAULT '0',
  `_opus` int(4) DEFAULT '0',
  `_position` varchar(256) NOT NULL DEFAULT '',
  `_extenbinding` varchar(3) NOT NULL DEFAULT 'no',
  `_exmacaddr` varchar(32) DEFAULT '',
  `_exdahdichan` varchar(2) DEFAULT '',
  `_recordin` varchar(32) NOT NULL DEFAULT '',
  `_recordout` varchar(32) NOT NULL DEFAULT '',
  `_F1` varchar(128) NOT NULL DEFAULT '',
  `_F2` varchar(128) NOT NULL DEFAULT '',
  `_F3` varchar(128) NOT NULL DEFAULT '',
  `_F4` varchar(128) NOT NULL DEFAULT '',
  PRIMARY KEY (`_id`),
  UNIQUE KEY `_exten` (`_exten`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t_pbx_users_extension`
--

LOCK TABLES `t_pbx_users_extension` WRITE;
/*!40000 ALTER TABLE `t_pbx_users_extension` DISABLE KEYS */;
/*!40000 ALTER TABLE `t_pbx_users_extension` ENABLE KEYS */;
UNLOCK TABLES;

DROP TRIGGER IF EXISTS `USEREXTEN_INSERT_BEFORE`;
DELIMITER ||
CREATE TRIGGER `USEREXTEN_INSERT_BEFORE` BEFORE INSERT ON `t_pbx_users_extension`
FOR EACH ROW
BEGIN
SET NEW._cid_number=NEW._exten;
SET NEW._mailbox=NEW._exten;
SET NEW._callgroup=NEW._pickupgroup;
SET NEW._srtpcapable=NEW._encryption;
END ||
DELIMITER ;

--
-- Table structure for table `t_pbx_users_webrtc`
--

DROP TABLE IF EXISTS `t_pbx_users_webrtc`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t_pbx_users_webrtc` (
  `_id` int(16) NOT NULL AUTO_INCREMENT,
  `_exten` varchar(32) NOT NULL,
  `_cid_number` varchar(32) NOT NULL DEFAULT '',
  `_transfer` varchar(3) NOT NULL DEFAULT 'yes',
  `_mailbox` varchar(16) NOT NULL DEFAULT '',
  `_type` varchar(6) NOT NULL DEFAULT 'friend',
  `_qualify` varchar(4) NOT NULL DEFAULT 'yes',
  `_hasdirectory` varchar(3) NOT NULL DEFAULT 'yes',
  `_call-limit` varchar(2) NOT NULL DEFAULT '5',
  `_host` varchar(16) NOT NULL DEFAULT 'dynamic',
  `_context` varchar(32) NOT NULL DEFAULT 'default',
  `_fullname` varchar(64) NOT NULL DEFAULT '',
  `_secret` varchar(128) NOT NULL DEFAULT '',
  `_outboundcid` varchar(32) NOT NULL DEFAULT '',
  `_hasvoicemail` varchar(3) NOT NULL DEFAULT 'no',
  `_vmsecret` varchar(32) NOT NULL DEFAULT '',
  `_delete` varchar(3) NOT NULL DEFAULT 'no',
  `_email` varchar(64) NOT NULL DEFAULT '',
  `_hassip` varchar(3) NOT NULL DEFAULT 'yes',
  `_hasiax` varchar(3) NOT NULL DEFAULT 'no',
  `_dahdichan` varchar(3) NOT NULL DEFAULT '',
  `_hasmanager` varchar(3) NOT NULL DEFAULT 'no',
  `_hasexten` varchar(3) NOT NULL DEFAULT '',
  `_nat` varchar(32) NOT NULL DEFAULT 'force_rport,comedia',
  `_directmedia` varchar(3) NOT NULL DEFAULT '',
  `_dtmfmode` varchar(8) NOT NULL DEFAULT 'rfc2833',
  `_hasagent` varchar(3) NOT NULL DEFAULT 'no',
  `_callwaiting` varchar(3) NOT NULL DEFAULT 'yes',
  `_callgroup` varchar(32) NOT NULL DEFAULT '1',
  `_pickupgroup` varchar(32) NOT NULL DEFAULT '1',
  `_videosupport` varchar(3) NOT NULL DEFAULT 'no',
  `_transport` varchar(16) NOT NULL DEFAULT 'udp,ws',
  `_encryption` varchar(3) NOT NULL DEFAULT 'yes',
  `_srtpcapable` varchar(3) NOT NULL DEFAULT 'no',
  `_deny` varchar(64) NOT NULL DEFAULT '',
  `_permit` varchar(124) NOT NULL DEFAULT '',
  `_avpf` varchar(3) NOT NULL DEFAULT 'yes',
  `_force_avp` varchar(3) NOT NULL DEFAULT 'yes',
  `_icesupport` varchar(3) NOT NULL DEFAULT 'yes',
  `_dtlsverify` varchar(3) NOT NULL DEFAULT 'no',
  `_dtlsenable` varchar(3) NOT NULL DEFAULT 'yes',
  `_dtlscertfile` varchar(64) NOT NULL DEFAULT '/etc/asterisk/keys/asterisk.pem',
  `_dtlscafile` varchar(64) NOT NULL DEFAULT '/etc/asterisk/keys/ca.crt',
  `_dtlssetup` varchar(32) NOT NULL DEFAULT 'actpass',
  `_alaw` int(1) NOT NULL DEFAULT '1',
  `_ulaw` int(1) NOT NULL DEFAULT '1',
  `_g722` int(1) NOT NULL DEFAULT '0',
  `_g729` int(1) NOT NULL DEFAULT '0',
  `_g726` int(1) NOT NULL DEFAULT '0',
  `_gsm` int(1) NOT NULL DEFAULT '0',
  `_speex` int(1) NOT NULL DEFAULT '0',
  `_h261` int(1) NOT NULL DEFAULT '0',
  `_h263` int(1) NOT NULL DEFAULT '0',
  `_h263p` int(1) NOT NULL DEFAULT '0',
  `_h264` int(1) NOT NULL DEFAULT '0',
  `_position` varchar(256) NOT NULL DEFAULT '',
  `_extenbinding` varchar(3) NOT NULL DEFAULT 'no',
  `_recordin` varchar(32) NOT NULL DEFAULT '',
  `_recordout` varchar(32) NOT NULL DEFAULT '',
  `_F1` varchar(128) NOT NULL DEFAULT '',
  `_F2` varchar(128) NOT NULL DEFAULT '',
  `_F3` varchar(128) NOT NULL DEFAULT '',
  `_F4` varchar(128) NOT NULL DEFAULT '',
  PRIMARY KEY (`_id`),
  UNIQUE KEY `_exten` (`_exten`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t_pbx_users_webrtc`
--

LOCK TABLES `t_pbx_users_webrtc` WRITE;
/*!40000 ALTER TABLE `t_pbx_users_webrtc` DISABLE KEYS */;
/*!40000 ALTER TABLE `t_pbx_users_webrtc` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `t_pbx_users_voiptrunk`
--

DROP TABLE IF EXISTS `t_pbx_users_voiptrunk`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t_pbx_users_voiptrunk` (
  `_id` int(16) NOT NULL AUTO_INCREMENT,
  `_trunkactive` varchar(3) NOT NULL DEFAULT 'yes',
  `_trunk` varchar(64) NOT NULL DEFAULT '',
  `_trunkname` varchar(64) NOT NULL,
  `_trunkstyle` varchar(64) NOT NULL DEFAULT '',
  `_host` varchar(64) NOT NULL DEFAULT 'yes',
  `_port` varchar(8) NOT NULL DEFAULT '',
  `_voipusername` varchar(64) NOT NULL DEFAULT '',
  `_authuser` varchar(64) NOT NULL DEFAULT '',
  `_fromuser` varchar(64) NOT NULL DEFAULT '',
  `_fromdomain` varchar(64) NOT NULL DEFAULT '',
  `_contact` varchar(64) NOT NULL DEFAULT '',
  `_voipsecret` varchar(128) NOT NULL DEFAULT '',
  `_outboundcid` varchar(32) NOT NULL DEFAULT '',
  `_trunkcid_preferred` varchar(3) NOT NULL DEFAULT 'no',
  `_prefix` varchar(16)	NOT NULL DEFAULT '',
  `_insecure` varchar(16) NOT NULL DEFAULT 'port,invite',
  `_calllimit` varchar(3) NOT NULL DEFAULT '0',
  `_language` varchar(8) NOT NULL DEFAULT '',
  `_qualify` varchar(8) NOT NULL DEFAULT '2000',
  `_qualifyfreq` varchar(8) NOT NULL DEFAULT '60',
  `_transport` varchar(16) NOT NULL DEFAULT 'udp',
  `_faxdetect` varchar(3) NOT NULL DEFAULT 'no',
  `_hassip` varchar(3) NOT NULL DEFAULT 'yes',
  `_registersip` varchar(3) NOT NULL DEFAULT 'no',
  `_hasiax` varchar(3) NOT NULL DEFAULT 'no',
  `_registeriax` varchar(3) NOT NULL DEFAULT 'no',
  `_context` varchar(32) NOT NULL DEFAULT 'default',
  `_hasexten` varchar(3) NOT NULL DEFAULT 'yes',
  `_nat` varchar(32) NOT NULL DEFAULT 'force_rport,comedia',
  `_directmedia` varchar(16) NOT NULL DEFAULT 'no',
  `_dtmfmode` varchar(8) NOT NULL DEFAULT 'rfc2833',
  `_videosupport` varchar(3) NOT NULL DEFAULT 'yes',
  `_encryption` varchar(3) NOT NULL DEFAULT 'no',
  `_srtpcapable` varchar(3) NOT NULL DEFAULT 'no',
  `_prack` varchar(3) NOT NULL DEFAULT 'no',
  `_alaw` int(1) NOT NULL DEFAULT '1',
  `_ulaw` int(1) NOT NULL DEFAULT '1',
  `_g722` int(1) NOT NULL DEFAULT '0',
  `_g729` int(1) NOT NULL DEFAULT '0',
  `_g726` int(1) NOT NULL DEFAULT '0',
  `_gsm` int(1) NOT NULL DEFAULT '0',
  `_speex` int(1) NOT NULL DEFAULT '0',
  `_h261` int(1) NOT NULL DEFAULT '0',
  `_h263` int(1) NOT NULL DEFAULT '0',
  `_h263p` int(1) NOT NULL DEFAULT '0',
  `_h264` int(1) NOT NULL DEFAULT '0',
  `_vp8` int(1) DEFAULT '0',
  `_opus` int(1) DEFAULT '0',
  `_position` varchar(256) NOT NULL DEFAULT '',
  `_recordin` varchar(32) NOT NULL DEFAULT '',
  `_recordout` varchar(32) NOT NULL DEFAULT '',
  `_F1` varchar(128) NOT NULL DEFAULT '',
  `_F2` varchar(128) NOT NULL DEFAULT '',
  `_F3` varchar(128) NOT NULL DEFAULT '',
  `_F4` varchar(128) NOT NULL DEFAULT '',
  PRIMARY KEY (`_id`),
  UNIQUE KEY `_trunk` (`_trunk`),
  UNIQUE KEY `_trunkname` (`_trunkname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t_pbx_users_voiptrunk`
--

LOCK TABLES `t_pbx_users_voiptrunk` WRITE;
/*!40000 ALTER TABLE `t_pbx_users_voiptrunk` DISABLE KEYS */;
/*!40000 ALTER TABLE `t_pbx_users_voiptrunk` ENABLE KEYS */;
UNLOCK TABLES;

DROP TRIGGER IF EXISTS `VOIPTRUNK_INSERT_BEFORE`;
DELIMITER ||
CREATE TRIGGER `VOIPTRUNK_INSERT_BEFORE` BEFORE INSERT ON `t_pbx_users_voiptrunk`
FOR EACH ROW
BEGIN
IF (NEW._hassip='yes') THEN
    SET NEW._registersip='no';
    SET NEW._srtpcapable=NEW._encryption;
    IF (NEW._voipusername='') THEN
	SET NEW._trunkstyle="SP-SIP";
    ELSE
	SET NEW._trunkstyle="SIP";
    END IF;
    IF (NEW._host='dynamic') THEN
	SET NEW._trunkstyle="Peer";
	SET NEW._trunk=NEW._voipusername;
    ELSE
	SET NEW._trunk=CONCAT("trunk-sip-",NEW._trunkname);
    END IF;
ELSEIF (NEW._hasiax='yes') THEN
    IF (NEW._voipusername='') THEN
	SET NEW._trunkstyle="SP-IAX";
    ELSE
	SET NEW._trunkstyle="IAX";
	SET NEW._registeriax='yes';
    END IF;
    IF (NEW._host='dynamic') THEN
	SET NEW._trunk=NEW._voipusername;
    ELSE
	SET NEW._trunk=CONCAT("trunk-iax-",NEW._trunkname);
    END IF;
END IF;
END ||
DELIMITER ;

--
-- Table structure for table `t_pbx_call_queues`
--

DROP TABLE IF EXISTS `t_pbx_call_queues`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t_pbx_call_queues` (
  `id` int(16) NOT NULL AUTO_INCREMENT,
  `queuenum` varchar(32) NOT NULL,
  `waittime` varchar(8) NOT NULL DEFAULT '',
  `label` varchar(64) NOT NULL DEFAULT '',
  `queuename` varchar(64) NOT NULL DEFAULT '',
  `dest` varchar(64) NOT NULL DEFAULT 'yes',
  `setinterfacevar` varchar(3) NOT NULL DEFAULT 'yes',
  `setqueueentryvar` varchar(3) NOT NULL DEFAULT 'yes',
  `retry` int(2) NOT NULL DEFAULT '1',
  `ringinuse` varchar(3) NOT NULL DEFAULT 'no',
  `musicclass` varchar(32) NOT NULL DEFAULT 'queuemusic',
  `context` varchar(32) NOT NULL DEFAULT 'queue-custom',
  `strategy` varchar(16) NOT NULL DEFAULT 'rrordered',
  `timeout` varchar(8) NOT NULL DEFAULT '15',
  `wrapuptime` varchar(8) NOT NULL DEFAULT '0',
  `autofill` varchar(3) NOT NULL DEFAULT 'yes',
  `autopause` varchar(3) NOT NULL DEFAULT 'no',
  `joinempty` varchar(32) NOT NULL DEFAULT 'no',
  `leavewhenempty` varchar(32) NOT NULL DEFAULT 'no',
  `reportholdtime` varchar(3) NOT NULL DEFAULT 'no',
  `rreminder` varchar(3) NOT NULL DEFAULT 'no',
  `maxlen` varchar(4)	NOT NULL DEFAULT '16',
  `announce_frequency` varchar(4) NOT NULL DEFAULT '30',
  `announce_holdtime` varchar(3) NOT NULL DEFAULT 'no',
  `announce_position` varchar(8) NOT NULL DEFAULT 'yes',
  `periodic_announce_frequency` varchar(4) NOT NULL DEFAULT '30',
  `periodic_announce` varchar(64) NOT NULL DEFAULT '/var/lib/asterisk/prompts/all-busy',
  `queue_youarenext` varchar(8) NOT NULL DEFAULT '',
  `queue_thankyou` varchar(16) NOT NULL DEFAULT '',
  `static_members` varchar(2048) DEFAULT NULL,
  `dynamic_members` varchar(2048) DEFAULT NULL,
  `work_order` varchar(3) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `queuenum` (`queuenum`),
  UNIQUE KEY `queuename` (`queuename`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t_pbx_call_queues`
--

LOCK TABLES `t_pbx_call_queues` WRITE;
/*!40000 ALTER TABLE `t_pbx_call_queues` DISABLE KEYS */;
/*!40000 ALTER TABLE `t_pbx_call_queues` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `t_pbx_dialrule`
--

DROP TABLE IF EXISTS `t_pbx_dialrule`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t_pbx_dialrule` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `trunks` varchar(64) DEFAULT NULL,
  `rule` varchar(64) DEFAULT NULL,
  `del_prefix` int(2) DEFAULT NULL,
  `add_before` varchar(32) DEFAULT NULL,
  `add_after` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t_pbx_dialrule`
--

LOCK TABLES `t_pbx_dialrule` WRITE;
/*!40000 ALTER TABLE `t_pbx_dialrule` DISABLE KEYS */;
INSERT INTO `t_pbx_dialrule` VALUES ('0','紧急电话规则','trunk_1','_1XX',NULL,NULL,NULL);
INSERT INTO `t_pbx_dialrule` VALUES ('1','本地电话规则','trunk_1','_ZXX.',NULL,NULL,NULL);
INSERT INTO `t_pbx_dialrule` VALUES ('2','国内长途规则','trunk_1','_0ZXX.',NULL,NULL,NULL);
INSERT INTO `t_pbx_dialrule` VALUES ('3','国际长途规则','trunk_1','_00ZXX.',NULL,NULL,NULL);
/*!40000 ALTER TABLE `t_pbx_dialrule` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `t_pbx_dialrule`
--

DROP TABLE IF EXISTS `t_pbx_ivr`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t_pbx_ivr` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `extension` varchar(32) DEFAULT NULL,
  `prompt` varchar(255) DEFAULT NULL,
  `loops` int(2) DEFAULT NULL,
  `timeout` int(8) DEFAULT NULL,
  `language` varchar(8) DEFAULT NULL,
  `dialplan` varchar(64) DEFAULT NULL,
  `keys_action` json DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  UNIQUE KEY `extension` (`extension`)
) ENGINE=InnoDB;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t_pbx_ivr`
--

LOCK TABLES `t_pbx_ivr` WRITE;
/*!40000 ALTER TABLE `t_pbx_ivr` DISABLE KEYS */;
INSERT INTO `t_pbx_ivr` VALUES ('0','上班时间话务员','6500','/etc/asterisk/sysconf/prompts/welcome',1,3,NULL,'default','{"i":"hangup","t":"hangup"}');
INSERT INTO `t_pbx_ivr` VALUES ('1','下班时间话务员','6501','/etc/asterisk/sysconf/prompts/closed',1,3,NULL,'default','{"i":"hangup","t":"hangup"}');
/*!40000 ALTER TABLE `t_pbx_ivr` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `t_cdr`
--

DROP TABLE IF EXISTS `t_pbx_cdr`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t_pbx_cdr` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `clid` varchar(80) DEFAULT NULL,
  `src` varchar(80) DEFAULT NULL,
  `dst` varchar(80) DEFAULT NULL,
  `dcontext` varchar(80) DEFAULT NULL,
  `channel` varchar(80) DEFAULT NULL,
  `dstchannel` varchar(80) DEFAULT NULL,
  `lastapp` varchar(80) DEFAULT NULL,
  `lastdata` varchar(80) DEFAULT NULL,
  `start` varchar(80) DEFAULT NULL,
  `end` varchar(80) DEFAULT NULL,
  `answer` varchar(80) DEFAULT NULL,
  `duration` int(11) DEFAULT NULL,
  `billsec` int(11) DEFAULT NULL,
  `ringduration` int(11) DEFAULT NULL,
  `disposition` varchar(32) DEFAULT NULL,
  `ama_flags` int(11) DEFAULT NULL,
  `accountcode` varchar(150) DEFAULT NULL,
  `calltype` varchar(32) DEFAULT 'internal',
  `uniqueid` varchar(80) DEFAULT NULL,
  `trunk` varchar(64) DEFAULT NULL,
  `recordfile` varchar(256) DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  `updatedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t_cdr`
--

LOCK TABLES `t_cdr` WRITE;
/*!40000 ALTER TABLE `t_cdr` DISABLE KEYS */;
/*!40000 ALTER TABLE `t_cdr` ENABLE KEYS */;
UNLOCK TABLES;
DROP TRIGGER IF EXISTS `CDR_INSERT_BEFORE`;
DELIMITER ||
CREATE TRIGGER `CDR_INSERT_BEFORE` BEFORE INSERT ON `t_cdr`
FOR EACH ROW
BEGIN
IF (NEW.calltype!='incoming') THEN
  IF (NEW.disposition='ANSWERED') THEN
    SET NEW.ringduration=NEW.duration - NEW.billsec;
  ELSE
    SET NEW.ringduration=NEW.duration;
    SET NEW.billsec=0;
  END IF;
ELSE
  IF (NEW.disposition!='ANSWERED') THEN
    SET NEW.ringduration=NEW.duration;
    SET NEW.billsec=0;
  END IF;
END IF;
IF (NEW.dst='t' OR NEW.dst='s' OR NEW.dst is NULL OR NEW.dst LIKE 'vm-%') THEN
    DELETE FROM cdr WHERE id=NEW.id;
END IF;
END ||
DELIMITER ;

--
-- Table structure for table `t_paging_tasks`
--

DROP TABLE IF EXISTS `t_paging_tasks`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t_paging_tasks` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `enable` tinyint(1) NOT NULL DEFAULT 1,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `mode` varchar(16) NOT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `week_days` varchar(32) DEFAULT NULL,
  `start_time` time DEFAULT NULL,
  `end_time` time DEFAULT NULL,
  `type` varchar(32) DEFAULT NULL,
  `sound_type` varchar(32) DEFAULT NULL,
  `play_mode` varchar(32) DEFAULT NULL,
  `content` varchar(128) DEFAULT NULL,
  `group_ids` JSON NOT NULL,
  `createdAt` datetime DEFAULT NULL,
  `updatedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `t_paging_devices`
--

DROP TABLE IF EXISTS `t_paging_devices`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t_paging_devices` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `model` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `extension` int(32) NOT NULL,
  `contact` varchar(64) DEFAULT NULL,
  `phonenumber` int(32) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `type_id` int(11) DEFAULT NULL,
  `remark` varchar(255) DEFAULT NULL,
  `features` json DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  `updatedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `extension` (`extension`)
) ENGINE=InnoDB;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `t_paging_groups`
--

DROP TABLE IF EXISTS `t_paging_groups`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t_paging_groups` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `extension` int(32) NOT NULL,
  `device_ids` JSON DEFAULT NULL,
  `remark` varchar(255) DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  `updatedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  UNIQUE KEY `extension` (`extension`)
) ENGINE=InnoDB;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t_paging_groups`
--

LOCK TABLES `t_paging_groups` WRITE;
/*!40000 ALTER TABLE `t_paging_groups` DISABLE KEYS */;
INSERT INTO `t_paging_groups` VALUES ('0','all',6000,'{"device":[]}',NULL,'2019-07-10 09:23:37','2019-07-10 09:23:37');
/*!40000 ALTER TABLE `t_paging_groups` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `t_paging_types`
--

DROP TABLE IF EXISTS `t_paging_types`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t_paging_types` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `remark` varchar(255) DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  `updatedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t_paging_types`
--

LOCK TABLES `t_paging_types` WRITE;
/*!40000 ALTER TABLE `t_paging_types` DISABLE KEYS */;
INSERT INTO `t_paging_types` VALUES (0,'广播终端',NULL,'2019-07-10 09:23:37','2019-07-10 09:23:37');
INSERT INTO `t_paging_types` VALUES (1,'对讲终端',NULL,'2019-07-10 09:23:37','2019-07-10 09:23:37');
INSERT INTO `t_paging_types` VALUES (2,'视频终端',NULL,'2019-07-10 09:23:37','2019-07-10 09:23:37');
INSERT INTO `t_paging_types` VALUES (3,'IP电话',NULL,'2019-07-10 09:23:37','2019-07-10 09:23:37');
/*!40000 ALTER TABLE `t_paging_types` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `t_paging_users`
--

DROP TABLE IF EXISTS `t_paging_users`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t_paging_users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `useraccount` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `secret` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `phonenumber` int(32) DEFAULT NULL,
  `leftphone` int(32) DEFAULT NULL,
  `rightphone` int(32) DEFAULT NULL,
  `level` int(2) DEFAULT NULL,
  `service_ids` JSON NOT NULL,
  `group_ids` JSON NOT NULL,
  `currentSessionId` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `lastActiveTime` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `lastip` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  `updatedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `useraccount` (`useraccount`)
) ENGINE=InnoDB;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t_paging_users`
--

LOCK TABLES `t_paging_users` WRITE;
/*!40000 ALTER TABLE `t_paging_users` DISABLE KEYS */;
INSERT INTO `t_paging_users` VALUES (0,'admin','admin','admin',NULL,NULL,NULL,1,'{"service":[0,1,2,3,4,5,6,7]}','{"group":[0]}',NULL,NULL,NULL,'2019-07-10 09:23:37','2019-07-10 09:23:37');
/*!40000 ALTER TABLE `t_paging_users` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `t_paging_service`
--

DROP TABLE IF EXISTS `t_paging_service`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t_paging_service` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `level` int(2) DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  `updatedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  UNIQUE KEY `level` (`level`)
) ENGINE=InnoDB;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t_paging_service`
--

LOCK TABLES `t_paging_service` WRITE;
/*!40000 ALTER TABLE `t_paging_service` DISABLE KEYS */;
INSERT INTO `t_paging_service` VALUES (0,'寻呼喊话',1,'2019-07-10 09:23:37','2019-07-10 09:23:37');
INSERT INTO `t_paging_service` VALUES (1,'一键告警',2,'2019-07-10 09:23:37','2019-07-10 09:23:37');
INSERT INTO `t_paging_service` VALUES (2,'消防告警',3,'2019-07-10 09:23:37','2019-07-10 09:23:37');
INSERT INTO `t_paging_service` VALUES (3,'设备对讲',4,'2019-07-10 09:23:37','2019-07-10 09:23:37');
INSERT INTO `t_paging_service` VALUES (4,'钟声提醒',5,'2019-07-10 09:23:37','2019-07-10 09:23:37');
INSERT INTO `t_paging_service` VALUES (5,'定时插播',6,'2019-07-10 09:23:37','2019-07-10 09:23:37');
INSERT INTO `t_paging_service` VALUES (6,'定时广播',7,'2019-07-10 09:23:37','2019-07-10 09:23:37');
INSERT INTO `t_paging_service` VALUES (7,'背景音乐',8,'2019-07-10 09:23:37','2019-07-10 09:23:37');
/*!40000 ALTER TABLE `t_paging_service` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `t_paging_action_log`
--

DROP TABLE IF EXISTS `t_paging_action_log`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t_paging_action_log` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `action_time` varchar(80) DEFAULT NULL,
  `operator` varchar(64) DEFAULT NULL,
  `action_info` json DEFAULT NULL,
  `service_id` int(2) DEFAULT NULL,
  `status` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t_paging_action_log`
--

LOCK TABLES `t_paging_action_log` WRITE;
/*!40000 ALTER TABLE `t_paging_action_log` DISABLE KEYS */;
/*!40000 ALTER TABLE `t_paging_action_log` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `t_paging_alarm_log`
--

DROP TABLE IF EXISTS `t_paging_alarm_log`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t_paging_alarm_log` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `alarm_time` varchar(80) DEFAULT NULL,
  `device_id` varchar(64) DEFAULT NULL,
  `alarm_info` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t_paging_alarm_log`
--

LOCK TABLES `t_paging_alarm_log` WRITE;
/*!40000 ALTER TABLE `t_paging_alarm_log` DISABLE KEYS */;
/*!40000 ALTER TABLE `t_paging_alarm_log` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `t_holidays_range`
--

DROP TABLE IF EXISTS `t_holidays_range`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t_holidays_range` (
  `_holidayname` varchar(512) NOT NULL DEFAULT '',
  `_start` datetime NOT NULL,
  `_end` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t_holidays_range`
--

LOCK TABLES `t_holidays_range` WRITE;
/*!40000 ALTER TABLE `t_holidays_range` DISABLE KEYS */;
/*!40000 ALTER TABLE `t_holidays_range` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `t_week_range`
--

DROP TABLE IF EXISTS `t_week_range`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t_week_range` (
  `_rulename` varchar(512) NOT NULL DEFAULT '',
  `_week` int(1) NOT NULL,
  `_start` time NOT NULL DEFAULT '00:00:00',
  `_end` time NOT NULL DEFAULT '23:59:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t_week_range`
--

LOCK TABLES `t_week_range` WRITE;
/*!40000 ALTER TABLE `t_week_range` DISABLE KEYS */;
INSERT INTO `t_week_range` VALUES ('office%20time',2,'09:00:00','12:00:00'),('office%20time',2,'14:00:00','18:00:00'),('office%20time',3,'09:00:00','12:00:00'),('office%20time',3,'14:00:00','18:00:00'),('office%20time',4,'09:00:00','12:00:00'),('office%20time',4,'14:00:00','18:00:00'),('office%20time',5,'09:00:00','12:00:00'),('office%20time',5,'14:00:00','18:00:00'),('office%20time',6,'09:00:00','12:00:00'),('office%20time',6,'14:00:00','18:00:00'),('office%20time',7,'09:00:00','12:00:00');
/*!40000 ALTER TABLE `t_week_range` ENABLE KEYS */;
UNLOCK TABLES;


DROP TABLE IF EXISTS `t_sip_settings`;
CREATE TABLE `t_sip_settings` (
    `id`                INT(11)         NOT NULL AUTO_INCREMENT,
    `key_name`          VARCHAR(40)     DEFAULT NULL,
    `key_value`         VARCHAR(40)     DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Dumping data for table `t_sip_settings`
--

LOCK TABLES `t_sip_settings` WRITE;
/*!40000 ALTER TABLE `t_sip_settings` DISABLE KEYS */;
INSERT INTO `t_sip_settings` VALUES (1,'UDP','5060'),(2,'TCP','5060'),(3,'TLS','5061'),(4,'UDP,TCP','5060');
/*!40000 ALTER TABLE `t_sip_settings` ENABLE KEYS */;
UNLOCK TABLES;

DROP TABLE IF EXISTS `t_phone_manufacturer`;
CREATE TABLE `t_phone_manufacturer` (
    `id`                INT(11)         NOT NULL AUTO_INCREMENT,
    `conf_name`         VARCHAR(32)     NOT NULL UNIQUE,
    `show_name`         VARCHAR(32)     NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO t_phone_manufacturer(id,conf_name,show_name)
    VALUES(1,'Zycoo','Zycoo'),(2,'Cisco','Cisco'),
            (3,'MOCET','MOCET'),(4,'Snom','Snom');

DROP TABLE IF EXISTS `t_phone_model`;
CREATE TABLE `t_phone_model` (
    `id`                INT(11)         NOT NULL AUTO_INCREMENT,
    `manufacturer_id`   INT(11)         NOT NULL,
    `conf_name`         VARCHAR(32)     NOT NULL,
    `show_name`         VARCHAR(32)     NOT NULL,
    `mac_prefix`        VARCHAR(20)     NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

/*zycoo*/
INSERT INTO t_phone_model(manufacturer_id,conf_name,show_name,mac_prefix)
    VALUES(1,'D30/D30P','D30/D30P','68692e'),(1,'D60','D60','68692e'),(1,'H81/H81P','H81/H81P','68692e'),
            (1,'H83','H83','68692e'),(1,'B20/C20','B20/C20','68692e');
/*Cisco*/
INSERT INTO t_phone_model(manufacturer_id,conf_name,show_name,mac_prefix)
    VALUES(2,'SPA303','SPA303','3cce73'),(2,'SPA502G','SPA502G','3cce73'),
            (2,'SPA504G','SPA504G','3cce73'),(2,'SPA508G','SPA508G','3cce73'),
            (2,'SPA525G','SPA525G','3cce73'),(2,'SPA525G2','SPA525G2','3cce73'),
            (2,'SPA121','SPA121','3cce73'),(2,'7911','7911','3cce73'),(2,'7941','7941','3cce73'),
            (2,'7942','7942','3cce73'),(2,'7945','7945','3cce73');
/*MOCET*/
INSERT INTO t_phone_model(manufacturer_id,conf_name,show_name,mac_prefix)
    VALUES(3,'IP3032E','IP3032E','001915');
/*Snom*/
INSERT INTO t_phone_model(manufacturer_id,conf_name,show_name,mac_prefix)
    VALUES(4,'D305','D305','000413');


DROP TABLE IF EXISTS `t_phone_device`;
CREATE TABLE `t_phone_device` (
    `id`                INT(11)         NOT NULL AUTO_INCREMENT,
    `mac`               VARCHAR(20)     NOT NULL UNIQUE,
    `ip`                VARCHAR(20)     NOT NULL,
    `manufacturer_id`   INT(11)         NOT NULL,
    `model_id`          INT(11)         DEFAULT 0,
    `status`            ENUM('fresh','configed','activated')    DEFAULT 'fresh',
    `call_waiting`      TINYINT(1)      DEFAULT 1,
    `greeting_word`     VARCHAR(20)     DEFAULT '',
    PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

DROP TRIGGER IF EXISTS `INSERT_PHONE_DEVICE_AFTER`;
DELIMITER ;;
CREATE TRIGGER `INSERT_PHONE_DEVICE_AFTER`
AFTER INSERT ON `t_phone_device`
FOR EACH ROW
BEGIN
    INSERT INTO t_phone_exten (device_id,exten) VALUES (NEW.id,'');
END;;
DELIMITER ;

DROP TABLE IF EXISTS `t_phone_exten`;
CREATE TABLE `t_phone_exten` (
    `id`                INT(11)         NOT NULL AUTO_INCREMENT,
    `device_id`         INT(11)         NOT NULL,
    `exten`             VARCHAR(40)     DEFAULT NULL,
    `exten_index`       INT(11)         DEFAULT 1,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


/*
 * zycoo h83
 */
DROP TABLE IF EXISTS `t_phone_func_key`;
CREATE TABLE `t_phone_func_key` (
    `id`                INT(11)         NOT NULL AUTO_INCREMENT,
    `device_id`         INT(11)         NOT NULL,
    `key_name`          VARCHAR(40)     DEFAULT NULL,
    `func_type`         VARCHAR(40)     DEFAULT NULL,
    `show_name`         VARCHAR(40)     DEFAULT NULL,
    `key_value`         VARCHAR(40)     DEFAULT NULL,
    `line`              VARCHAR(40)     DEFAULT NULL,
    `sub_type`          VARCHAR(40)     DEFAULT NULL,
    `pickup_number`     VARCHAR(40)     DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


/*
 * SELECT func_record_discover_phone_device('005544444444','zycoo','h83','192.168.10.12');
 */
DROP FUNCTION IF EXISTS `func_record_discover_phone_device`;
DELIMITER ;;
CREATE FUNCTION `func_record_discover_phone_device`(
    `in_mac`                VARCHAR(20),
    `in_manufacturer`       VARCHAR(32),
    `in_model`              VARCHAR(32),
    `in_ip`                 VARCHAR(20)
)
RETURNS text
BEGIN
    DECLARE var_count           INT(11);
    DECLARE var_model_id        INT(11) DEFAULT 0;
    DECLARE var_manufacturer_id INT(11);
    SELECT COUNT(*) INTO var_count
        FROM t_phone_device
        WHERE mac=in_mac;
    IF(var_count=0) THEN
        SELECT id INTO var_manufacturer_id
            FROM t_phone_manufacturer
            WHERE conf_name=in_manufacturer;
        IF(in_model!='') THEN
            SELECT id INTO var_model_id
                FROM t_phone_model
                WHERE conf_name=in_model AND manufacturer_id=var_manufacturer_id;
        END IF;
	IF(var_manufacturer_id!='') THEN
	    INSERT INTO t_phone_device(mac,ip,manufacturer_id,model_id)
                VALUE(in_mac,in_ip,var_manufacturer_id,var_model_id);
	END IF;
    ELSE
        UPDATE t_phone_device
            SET ip=in_ip
            WHERE mac=in_mac;
    END IF;
    RETURN 'true';
END
;;
DELIMITER ;


/*
 * SELECT func_record_add_phone_device('1','005544444444','zycoo','h83','801','configed','1','ZycooTech');
 */
DROP FUNCTION IF EXISTS `func_record_add_phone_device`;
DELIMITER ;;
CREATE FUNCTION `func_record_add_phone_device`(
    `in_id`                 INT(11),
    `in_mac`                VARCHAR(20),
    `in_manufacturer`       VARCHAR(32),
    `in_model`              VARCHAR(32),
    `in_exten`              VARCHAR(40),
    `in_status`             VARCHAR(24),
    `in_call_waiting`       TINYINT(1),
    `in_greeting_word`      VARCHAR(20)
)
RETURNS text
BEGIN
    DECLARE var_count           INT(11);
    DECLARE var_model_id        INT(11) DEFAULT 0;
    DECLARE var_manufacturer_id INT(11);
    SELECT COUNT(*) INTO var_count
        FROM t_phone_device
        WHERE mac=in_mac;
    IF(var_count=0) THEN
        SELECT id INTO var_manufacturer_id
            FROM t_phone_manufacturer
            WHERE conf_name=in_manufacturer;
        IF(in_model!='') THEN
            SELECT id INTO var_model_id
                FROM t_phone_model
                WHERE conf_name=in_model AND manufacturer_id=var_manufacturer_id;
        END IF;
	IF(var_manufacturer_id!='') THEN
	    INSERT INTO t_phone_device(id,mac,manufacturer_id,model_id,status,call_waiting,greeting_word)
                VALUE(in_id,in_mac,var_manufacturer_id,var_model_id,in_status,in_call_waiting,in_greeting_word);
	    UPDATE t_phone_exten SET exten=in_exten where device_id=in_id;
	END IF;
    ELSE
        UPDATE t_phone_device
            SET ip=in_ip
            WHERE mac=in_mac;
    END IF;
    RETURN 'true';
END
;;
DELIMITER ;


/*
 * SELECT func_delete_phone_device(1);
 */
DROP FUNCTION IF EXISTS `func_delete_phone`;
DELIMITER ;;
CREATE FUNCTION `func_delete_phone`(
    `in_device_id`          VARCHAR(20)
)
RETURNS text
BEGIN
    DELETE FROM t_phone_func_key WHERE device_id=in_device_id;
    DELETE FROM t_phone_exten WHERE device_id=in_device_id;
    DELETE FROM t_phone_device WHERE id=in_device_id;
    RETURN 'true';
END
;;
DELIMITER ;


/*
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| id            | int(11)     | NO   |     | 0       |       |
| mac           | varchar(20) | NO   |     | NULL    |       |
| ip            | varchar(20) | NO   |     | NULL    |       |
| manufacturer  | varchar(32) | NO   |     | NULL    |       |
| model         | varchar(32) | YES  |     | NULL    |       |
| call_waiting  | tinyint(1)  | YES  |     | 1       |       |
| greeting_word | varchar(20) | YES  |     |         |       |
+---------------+-------------+------+-----+---------+-------+
 */
DROP VIEW IF EXISTS `v_phone_device_activated`;
CREATE VIEW v_phone_device_activated
AS
SELECT t_phone_device.id AS id,
    mac, 
    ip,
    t_phone_manufacturer.conf_name AS manufacturer,
    t_phone_model.conf_name AS model,
    exten,
    _fullname,
    _transport,
    t_sip_settings.key_value AS port,
    _secret,
    call_waiting,
    greeting_word
    FROM t_phone_device
        JOIN t_phone_manufacturer ON status='activated'
            AND t_phone_device.manufacturer_id=t_phone_manufacturer.id
        LEFT JOIN t_phone_exten ON t_phone_device.id=t_phone_exten.device_id
	LEFT JOIN t_users_extension ON t_phone_exten.exten=t_users_extension._exten
	LEFT JOIN t_sip_settings ON t_users_extension._transport=t_sip_settings.key_name
        LEFT JOIN t_phone_model ON t_phone_device.model_id=t_phone_model.id;


/*
 * 
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| id            | int(11)     | NO   |     | 0       |       |
| dodexten      | varchar(32) | NO   |     | NULL    |       |
| trunkname     | varchar(64) | NO   |     | NULL    |       |
| trunk         | varchar(128)| NO   |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
 */
DROP VIEW IF EXISTS `v_extension_trunk_dod`;
CREATE VIEW v_extension_trunk_dod
AS
SELECT t_number_dod._id AS id,
    _dodnumber AS dodexten,
    _dodtrunk AS trunkname,
    _trunk AS trunk 
    FROM t_number_dod
        JOIN t_users_voiptrunk ON t_users_voiptrunk._trunkname=t_number_dod._dodtrunk;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2010-01-04  7:18:52