/* SQL STATEMENTS FOR CREATING THE TRAVEL AGENCY DATABASE */

/* create database travel agency */
/*create database travelagency
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;
*/

/* create table users */
create table users(uid INT AUTO_INCREMENT,
email varchar(100),
password varchar(32), /* md5 field */
primary key(uid)
)DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;

INSERT INTO users(email,password) VALUES ('testadminsql@ucnet.uoc.gr',MD5('test123'));

/* create table for customers */
create table customers(cid INT AUTO_INCREMENT,
surname varchar(50) NOT NULL, /* ypoxreotiko pedio */
name varchar(50) NOT NULL,    /* ypoxreotiko pedio */
email varchar(100) NOT NULL,  /* ypoxreotiko pedio */
address varchar(100),
country varchar(50),
telephone_number varchar(50),
gender varchar(20),	
primary key(cid)
)
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;


/* create table status_tours for tours' status e.g. cancelled, ended, etc... */ 
create table status_tours(statusId INT AUTO_INCREMENT,
description varchar(100) NOT NULL,
primary key(statusId))
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;

/* create table status for requests' status e.g. verified,cancelled, pending etc... */ 
create table status_requests(statusId INT AUTO_INCREMENT,
description varchar(100) NOT NULL,
primary key(statusId))
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;


/* create table tour */
create table tours(tid INT AUTO_INCREMENT,
title varchar(100),
description text,
begin_date date,
end_date date,
maxPersons INT,
minPersons INT,
multimedia_file varchar(100),	/* name of multimedia file */
costPerPerson double,	/* kostos ana atomo */	
statusId INT,
primary key(tid),
foreign key(statusId) references status_tours(statusId))
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;


/* create table requests - aitimata gia ekdromes */
create table requests (reqId INT AUTO_INCREMENT,
cid INT NOT NULL,	/* customer Id */
tid INT NOT NULL,	/* tour Id */
statusId INT NOT NULL, /* status Id of request */
numOfPersons INT NOT NULL DEFAULT 1,
PRIMARY KEY (reqId),
foreign key(cid) references customers(cid),
foreign key(tid) references tours(tid),
foreign key(statusId) references status_requests(statusId))
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;

/* ***************************************************** */
/* INSERT DATA INTO TABLES */
/* ***************************************************** */

/* CUSTOMER TABLE */
INSERT INTO customers(surname,name,email,address,country,telephone_number,gender) VALUES ('Παπαδάκης','Μανόλης','man_test@example123.com','Πλ. Κορνάρου 188','Ελλάδα','+30 - 2810 - 345678','Α');
INSERT INTO customers(surname,name,email,address,country,telephone_number,gender) VALUES ('Κοκκινάκη','Μαρία','mary_123_@example123.com','Δικαιοσύνης 178','Ελλάδα','0030 - 210 - 1111234456','Γ');
INSERT INTO customers(surname,name,email,address,country,telephone_number,gender) VALUES ('Παπαδόπουλος','Μάριος','marios_123_@example123.com','Δικαιοσύνης 175','Ελλάδα','0030 - 210 - 1111234456','A');
INSERT INTO customers(surname,name,email,address,country,telephone_number,gender) VALUES ('Αυγενοπούλου','Σοφία','sofy_123_@example123.com','Δικαιοσύνης 178','Ελλάδα','0030 - 210 - 1111234456','Γ');
INSERT INTO customers(surname,name,email,address,country,telephone_number,gender) VALUES ('Φασουλάκη','Μαρία','fas_123_@example123.com','Δικαιοσύνης 178','Ελλάδα','0030 - 210 - 1111234456','Γ');


/* STATUS_TOURS TABLE */
INSERT INTO status_tours(statusId,description) VALUES (1,'Εν Αναμονή Εγγραφών');
INSERT INTO status_tours(statusId,description) VALUES (2,'Ολοκληρώθηκε');
INSERT INTO status_tours(statusId,description) VALUES (3,'Ακυρώθηκε');

/* STATUS_REQUESTS TABLE */
INSERT INTO status_requests(statusId,description) VALUES (1,'Αναμονή');
INSERT INTO status_requests(statusId,description) VALUES (2,'Αποδοχή');
INSERT INTO status_requests(statusId,description) VALUES (3,'Ακυρώθηκε');


/* TOUR TABLE */
INSERT INTO tours(title,description,begin_date,end_date,maxPersons,minPersons,multimedia_file,costPerPerson,statusId) 
values 
('Γύρος Ισπανίας','Εκδρομή στην Μαδρίτη-Τολέδο-Βαλένθια-Βαρκελώνη','2015-09-01','2015-09-10',50,30,'spain.jpg',430,1);

INSERT INTO tours(title,description,begin_date,end_date,maxPersons,minPersons,multimedia_file,costPerPerson,statusId) 
values 
('Παρίσι από Ηράκλειο','ΠΑΡΙΣΙ ΜΕ ΔΙΑΜΟΝΗ ΣΤΗΝ DISNEY ΜΕ ΑΠΕΥΘΕΙΑΣ ΠΤΗΣΕΙΣ ΑΠΟ ΗΡΑΚΛΕΙΟ','2015-06-01','2015-06-10',60,30,'paris.jpg',630,2);

INSERT INTO tours(title,description,begin_date,end_date,maxPersons,minPersons,multimedia_file,costPerPerson,statusId) 
values 
('Ήπειρος','Εκδρομή στην Ηπειρωτική Ελλάδα','2015-09-01','2015-09-10',50,30,'ipiros.jpg',130,1);

INSERT INTO tours(title,description,begin_date,end_date,maxPersons,minPersons,multimedia_file,costPerPerson,statusId) 
values 
('Εκδρομή στην Σαντορίνη','Από τους πιο ρομαντικούς προορισμούς στην Ελλάδα.','2015-06-01','2015-06-10',20,10,'santorini.jpg',220,3);

INSERT INTO tours(title,description,begin_date,end_date,maxPersons,minPersons,multimedia_file,costPerPerson,statusId) 
values 
('Εκδρομή στην Βαρκελώνη','Εκδρομή στην Βαρκελώνη - Πρόγραμμα','2015-08-01','2015-08-10',50,30,'varkenoni.jpg',850,1);

INSERT INTO tours(title,description,begin_date,end_date,maxPersons,minPersons,multimedia_file,costPerPerson,statusId) 
values 
('Βουδαπέστη - Βιέννη','ΒΟΥΔΑΠΕΣΤΗ - ΒΙΕΝΝΗ ΜΕ ΑΠΕΥΘΕΙΑΣ ΠΤΗΣΕΙΣ ΑΠΟ ΗΡΑΚΛΕΙΟ','2015-06-01','2015-06-10',20,10,'paris.jpg',630,3);



/* REQUESTS TABLE */
INSERT INTO requests(cid,tid,statusId) VALUES (1,1,1);
INSERT INTO requests(cid,tid,statusId) VALUES (2,1,2);
INSERT INTO requests(cid,tid,statusId) VALUES (2,2,3);

INSERT INTO requests(cid,tid,statusId,numOfPersons) VALUES (4,5,2,4);
INSERT INTO requests(cid,tid,statusId,numOfPersons) VALUES (2,5,2,2);
INSERT INTO requests(cid,tid,statusId,numOfPersons) VALUES (5,5,2,8);
INSERT INTO requests(cid,tid,statusId,numOfPersons) VALUES (1,5,3,2);
INSERT INTO requests(cid,tid,statusId,numOfPersons) VALUES (3,5,3,2);

INSERT INTO requests(cid,tid,statusId,numOfPersons) VALUES (4,3,2,5);
INSERT INTO requests(cid,tid,statusId,numOfPersons) VALUES (2,3,3,3);