# this file includes the commands to build a db for the ACD app
# along with some sample queues and agents

# drop database acd
# create database acd
# to import the areacode database
# mysqlimport -uroot -p -hpasterisk -v --lines-terminated-by="\r" acd d:\callcenter\areacode.txt
# load data local infile 'd:\\callcenter\\areacode.txt' into table areacode lines terminated by '\r\n';

use acd;

drop table settings;
create table settings (
company		enum ('P','C'),
callcontrol	char(20),
spins       int,
minspintime int
) comment="Per company tuning parameters";

# callcontrol is appended to the end of the channel descriptor after dial.  ie, Zap/1|25|tm
# ordering 

insert into settings values ('C',"|25|tm","order by a.lastcall+rand()*180"),('P',"|25|tm","order by a.lastcall+rand()*180");

#drop table areacode;
#create table areacode (
#ac         char(3) not null primary key,
#queueid    int     not null default 0,
#state      char(2) not null,
#timezone   char(6),
#name       char(255)
#) comment="US / Can area codes";

drop table chanip;
create table chanip (
channelid  char(8) not null primary key,
wallplugid char(6),
location   char(64),
ipaddr     int,
port       int
) comment="Extension to IP and wall plug linkage";

insert into chanip (channelid ) values ("Zap/1"),("Zap/2"),("Zap/3"),("Zap/4");
insert into chanip (channelid ) values ("Zap/5"),("Zap/6"),("Zap/7"),("Zap/8");
insert into chanip (channelid ) values ("Zap/9"),("Zap/10"),("Zap/11"),("Zap/12");
insert into chanip (channelid ) values ("Zap/13"),("Zap/14"),("Zap/15"),("Zap/16");


drop table agent;
create table agent (
agentid    int not null primary key,
name       char(64),
password   char(4),
company    enum ('C','P'),
channelid  char(6),
active     enum('Y','N') default 'Y',
chactive   datetime,
lastcall   datetime 
) comment="Call center agents";

# lastcall is last ACD call answered
# chactive is time of last status change

insert into agent values (2510,"Chris Klein","1234","P","Zap/34",'Y'),(2511,"Rob Pedley","1234","P","Zap/35",'Y');
insert into agent values (2512,"Gina Nocito","1234","P","Zap/36",'Y'),(2513,"Brian Emark","1234","P","Zap/37",'Y');
insert into agent values (2514,"Stephanie Smith","1234","P","Zap/38",'Y'),(2515,"Amber Bennett","1234","P","Zap/39",'Y');
insert into agent values (2516,"Kelly Madigan","1234","P","Zap/40",'Y'),(2517,"Lori","1234","P","Zap/41",'Y');
insert into agent values (2518,"Colleen","1234","P","Zap/42",'Y'),(2521,"Cara Vitale","1234","P","Zap/45",'Y');
insert into agent values (2520,"Jessica Cronenweth","1234","P","Zap/44",'Y');

insert into agent values (2601,"Joe Nocito Jr","1234","C","Zap/2",'Y'),(2602,"Jessica Cronenweth","1234","C","Zap/44",'Y');
insert into agent values (2603,"John Gill","1234","C","Zap/3",'Y'),(2604,"Pat Kernick","1234","C","Zap/4",'Y');
insert into agent values (2605,"Mary Wrenshaw","1234","C","Zap/5",'Y'),(2606,"Leslie Carpenter","1234","C","Zap/6",'Y');
insert into agent values (2607,"Dan Flara","1234","C","Zap/7",'Y'),(2608,"Debbie Lavelle","1234","C","Zap/8",'Y');
insert into agent values (2609,"Greg McDonald","1234","C","Zap/9",'Y'),(2610,"Gina Nocito","1234","C","Zap/10",'Y');





drop table queue;
create table queue (
queueid    int not null auto_increment primary key, 
name       char(255)
) comment="call queues";

insert into queue values (1,"Default queue");
update areacode set queueid=1;

drop table qagent;
create table qagent(
qagentid   int not null auto_increment primary key,
queueid    int not null,
company	   enum ('C','P'),
agentid    int not null,
skill      int not null default 10,
callmod    int not null default 0
) comment="agent assignment to call queue";

insert into qagent values (0,1,'P',2510,10,10),(0,1,'P',2511,10,10),(0,1,'P',2512,10,10);  # default queue
insert into qagent values (0,1,'P',2513,10,10),(0,1,'P',2514,10,10),(0,1,'P',2515,10,10);  # default queue
insert into qagent values (0,1,'P',2516,10,10),(0,1,'P',2517,10,10),(0,1,'P',2518,10,10);  # default queue
insert into qagent values (0,1,'P',2520,10,10),(0,1,'P',2521,10,10);  # default queue

insert into qagent values (0,1,'C',2601,10,10),(0,1,'C',2602,10,10),(0,1,'C',2603,10,10);  # default queue
insert into qagent values (0,1,'C',2604,10,10),(0,1,'C',2605,10,10),(0,1,'C',2606,10,10);  # default queue
insert into qagent values (0,1,'C',2607,10,10),(0,1,'C',2608,10,10),(0,1,'C',2609,10,10);  # default queue
insert into qagent values (0,1,'C',2610,10,10);  # default queue



create table client (
clientid	int not null auto_increment primary key,
agentid     int,
name        char(255)
) comment="callers who have an agent";

create table clientnumber (
clientid    int not null,
phone		char(12) not null,		      
type        char(32),
primary key(clientid,phone)
) comment="Numbers associated with a client";

create table acdcall (
acdcallid	int not null auto_increment primary key,
dnis		char(6),
callid		char(48),
timein      datetime,
timeans     datetime,
queueid		int,
qagentid	int,
extipid		int
)  comment="Additional info to help track calls";

create table acdqueueentry (
acdqeid		int not null auto_increment primary key,



#Typical queries to handle a call

#select * from areacode where ac='412';  
#select * from qagent where queueid=2 order by skill-callmod+rand()*12  desc;
#select * from agent where agentid in (4,5,6);
#select * from extip where extension=2114;