# 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 in