create database virtual_conference;
use virtual_conference;

-- List of Tables:
create table user
(
    user_id int unsigned AUTO_INCREMENT primary key,
    name varchar(64) not null,
    email varchar(128) not null,
    password char(32) not null,
    is_signed_in boolean not null,
    sip_uri varchar(128) not null,
    unique(email)
) Engine=Innodb;

create table recurring_meeting
(

recurring_meeting_id int unsigned AUTO_INCREMENT primary key
) Engine = Innodb;

create table conference
(
    conference_id int unsigned AUTO_INCREMENT primary key, -- sys_guid - random
    title varchar(128), -- Conference Title chosen by admin
    description text,
    meeting_notes text,
    owner int unsigned not null,
    start_time timestamp not null,
    end_time timestamp null default null,
    is_closed boolean,

recurring_meeting_id int unsigned,

previous_meeting_id int unsigned,

next_meeting_id int unsigned,

foreign key(previous_meeting_id) references conference(conference_id) on delete cascade on update cascade,

foreign key(next_meeting_id) references conference(conference_id) on delete cascade on update cascade,
    foreign key(recurring_meeting_id) references recurring_meeting(recurring_meeting_id) on delete cascade on update cascade,
    foreign key(owner) references user(user_id) on delete cascade on update cascade
) Engine=Innodb;



create table privilege
(
    privilege_id int unsigned AUTO_INCREMENT primary key,
    privilege_name varchar(32) not null,
    unique(privilege_name)
) Engine=Innodb;

create table chat
(
    id int unsigned AUTO_INCREMENT primary key,
    user1 int unsigned not null,
    user2 int unsigned not null,
    time timestamp not null,
    message text,

foreign key(user1) references user(user_id) on delete cascade on update cascade,

foreign key(user2) references user(user_id) on delete cascade on update cascade
) Engine=Innodb;

create table conference_chat(
    id int unsigned AUTO_INCREMENT primary key,

user_id int unsigned not null,

conference_id int unsigned not null,

time timestamp not null,
    message text,

foreign key(user_id) references user(user_id) on delete cascade on update cascade,

foreign key(conference_id) references conference(conference_id) on delete cascade on update cascade
) Engine=Innodb;

create table todo_item
(

conference_id int unsigned,

todo_item_id int unsigned AUTO_INCREMENT primary key,

title varchar(32) not null,

description text,

time_assigned timestamp not null,

due_time timestamp,

user_id int unsigned not null,
    assignee_id int unsigned not null,
    priority int unsigned, -- 1 - 5

foreign key(conference_id) references conference(conference_id) on delete cascade on update cascade,

foreign key(user_id) references user(user_id) on delete cascade on update cascade,

foreign key(assignee_id) references user(user_id) on delete cascade on update cascade
) Engine=Innodb;

create table file
(
    file_id int unsigned auto_increment primary key,
    filename varchar(128) not null,
    location varchar(256) not null,
    submitted_by int unsigned not null,
    conference_id int unsigned not null,

foreign key(submitted_by) references user(user_id) on delete cascade on update cascade,

foreign key(conference_id) references conference(conference_id) on delete cascade on update cascade

) Engine=Innodb;

create table poll
(
    poll_id int unsigned AUTO_INCREMENT primary key,
    conference_id int unsigned not null,
    created_by int unsigned not null,
    question text not null,
    is_checkbox boolean, -- checkbox or radio

foreign key(created_by) references user(user_id) on delete cascade on update cascade,

foreign key(conference_id) references conference(conference_id) on delete cascade on update cascade
) Engine=Innodb;

create table poll_option
(
    poll_option_id int unsigned AUTO_INCREMENT primary key,
    poll_id int unsigned not null,
    value text not null,
    foreign key(poll_id) references poll(poll_id) on delete cascade on update cascade
) Engine=Innodb;


create table poll_vote
(
    poll_id int unsigned,
    poll_option_id int unsigned,
    voter_id int unsigned,
    foreign key(poll_id) references poll(poll_id) on delete cascade on update cascade,
    foreign key(poll_option_id) references poll_option(poll_option_id) on delete cascade on update cascade,

foreign key(voter_id) references user(user_id) on delete cascade on update cascade
) Engine=Innodb;

create table conference_user
(
    conference_id int unsigned,
    user_id int unsigned,
    conf_user_id int unsigned AUTO_INCREMENT,

foreign key(conference_id) references conference(conference_id) on delete cascade on update cascade,

foreign key(user_id) references user(user_id) on delete cascade on update cascade,

primary key (conf_user_id)
)Engine = Innodb;

create table conference_user_privilege
(

conf_user_id int unsigned not null,

privilege_id int unsigned not null,

foreign key(conf_user_id) references conference_user(conf_user_id) on delete cascade on update cascade,

foreign key(privilege_id) references privilege(privilege_id) on delete cascade on update cascade
) Engine=Innodb;

create table meeting_log
(
    conference_id int unsigned not null,
    user_id int unsigned not null,

description text not null,

time timestamp not null,

primary key(conference_id, user_id, time),

foreign key(conference_id) references conference(conference_id) on delete cascade on update cascade,

foreign key(user_id) references user(user_id) on delete cascade on update cascade
)Engine = Innodb;

-- Test Data Population
insert into privilege values(2,'Moderator');
insert into privilege values(3,'Assign Todo Tasks');
insert into privilege values(4,'Modify Meeting Notes');
insert into privilege values(5,'Create Polls');

insert into user values(1, 'Sabari Murugesan', 'sm3478@columbia.edu',MD5('password'),true,'neosab@sip2sip.info');
insert into user values(2, 'Albert Jimenez', 'alj2110@columbia.edu',MD5('password'),true,'alj2110@sip2sip.info');
insert into user values(3, 'Aditya Pipersenia','ap2904@columbia.edu',MD5('password'),false,'adityap@sip2sip.info');

-- Conference 1
insert into conference (conference_id, title,description,owner,is_closed,start_time,end_time) values (1000, 'TestConf1', 'TestConf1', 1,0,current_timestamp,start_time+INTERVAL 30 MINUTE );
insert into conference_user (conf_user_id, conference_id, user_id) values (1, 1000,1);
insert into conference_user (conf_user_id, conference_id, user_id) values (2, 1000,2);
insert into conference_user (conf_user_id, conference_id, user_id) values (3, 1000,3);
-- Conference 2
insert into conference (conference_id, title,description,owner,is_closed,start_time,end_time) values (2000, 'TestConf2', 'TestConf2', 3, 1,current_timestamp - INTERVAL 1 DAY+INTERVAL 65 MINUTE,start_time+INTERVAL 90 MINUTE);
insert into conference_user (conf_user_id, conference_id, user_id) values (4, 2000,2);
insert into conference_user (conf_user_id, conference_id, user_id) values (5, 2000,1);

-- Conference 3
insert into conference (conference_id, title,description,owner,is_closed,start_time,end_time) values (3000, 'TestConf3', 'TestConf3', 2, 0,current_timestamp+INTERVAL 1 day-INTERVAL 45 MINUTE,start_time+INTERVAL 450 MINUTE);
insert into conference_user (conf_user_id, conference_id, user_id) values (6, 3000,2);
insert into conference_user (conf_user_id, conference_id, user_id) values (7, 3000,1);
insert into conference_user (conf_user_id, conference_id, user_id) values (8, 3000,3);

-- 

insert into conference_user_privilege values(1,3);
insert into conference_user_privilege values(1,2);
insert into conference_user_privilege values(2,2);
insert into conference_user_privilege values(3,3);
insert into conference_user_privilege values(4,3);
insert into conference_user_privilege values(6,3);
insert into conference_user_privilege values(6,5);
insert into conference_user_privilege values(7,2);