drop database if exists telephonedatabase;
create database telephonedatabase;
use telephonedatabase;


create table calls
(
	caller varchar(30),
	dialled varchar(30),
	timestamp datetime not null,
	direction boolean not null comment 'in=0 out=1',
	telephonecentral tinyint unsigned,
	id serial
);



#tables for each event is better. each table can then have different columns
/*********************************************************************
create table event_names
(
	name char(10),
	id tinyint unsigned not null auto_increment unique
);

insert event_names values
("connected",1),("hanged up",2)("hold",3),("unhold",4),("park",5),("unpark",6),("transfer internal",7),
("transfer external",8),("blind transfer",9),("attended transfer",10);

create table event
(
	name tinyint unsigned not null references event_names.id,
	timestamp datetime,
);

delete from event;
insert event values ("answered",""2007-25-02,"15:21:30"),("hanged up",""2007-25-02,"15:23:42");
**********************************************************************/

create table connected
(
	connected_number varchar(30),
	timestamp datetime not null,
	callid bigint unsigned not null references calls.id
);

create table hangup
(
	timestamp datetime not null,
	callid bigint unsigned not null references calls.id
);

create table other
(
	div1 varchar(30),
	div2 varchar(30)
);


delete from calls;
insert calls values ("45426817", "91245682", "2008-02-25 15:20:21", 0, 5, null);

delete from connected;
insert connected values ("91245682", "2008-02-25 15:20:42", (select id from calls 
where caller="45426817" and dialled="91245682" and timestamp="2008-02-25 15:20:21"));

delete from hangup;
insert hangup values ("2008-02-25 15:23:21", (select id from calls 
where caller="45426817" and dialled="91245682" and timestamp="2008-02-25 15:20:21"));