<?php

//DB connection variables
$host = 'localhost';
$user = 'root';
$password = '';

//create mysql connection
$mysqli = new mysqli($host,$user,$password);
if ($mysqli->connect_errno) {
    printf("Connection failed: %s\n", $mysqli->connect_error);
    die();
}

//Drop database if exist
$mysqli->query('
DROP DATABASE IF EXISTS `pooler`;
') or die($mysqli->error);


//create the database
if ( !$mysqli->query('CREATE DATABASE pooler') ) {
    printf("Errormessage: %s\n", $mysqli->error);
}


//create admin table
$mysqli->query('
CREATE TABLE `pooler`.`admins`
(
    `admin_id` VARCHAR(20) NOT NULL,
    `fname` VARCHAR(50) NOT NULL,
    `lname` VARCHAR(50) NOT NULL,
    `password` VARCHAR(50) NOT NULL,
	 PRIMARY KEY (`admin_id`)
);') or die($mysqli->error);

//create students table
$mysqli->query('
CREATE TABLE `pooler`.`students`
(
      `student_id` VARCHAR(50) NOT NULL,
      `fname` VARCHAR(50) NOT NULL,
	  `lname` VARCHAR(50) NOT NULL,
	  `password` VARCHAR(50) NOT NULL,
	  `age` INT NOT NULL,
      `form` VARCHAR(50) NOT NULL,
      `gender` VARCHAR(6) NOT NULL,
      `telephone` VARCHAR(50) NOT NULL,
      `parent_telephone` VARCHAR(50) NOT NULL,
      `career1` VARCHAR(50) NOT NULL,
	  `career2` VARCHAR(50) NOT NULL,
	  `career3` VARCHAR(50) NOT NULL,
      `year` INT NOT NULL,
	  PRIMARY KEY (`student_id`)
);') or die($mysqli->error);


//create teachers table
$mysqli->query('
CREATE TABLE `pooler`.`teachers`
(
      `teacher_id` VARCHAR(50) NOT NULL,
      `fname` VARCHAR(50) NOT NULL,
	  `lname` VARCHAR(50) NOT NULL,
      `gender` VARCHAR(6) NOT NULL,
	  `password` VARCHAR(50) NOT NULL,
	   PRIMARY KEY (`teacher_id`)
);') or die($mysqli->error);


//create courses table
$mysqli->query('
CREATE TABLE `pooler`.`courses`
(
     `course_id` VARCHAR(100) NOT NULL,
     `teacher_id` VARCHAR(50) NOT NULL,
     `course_name` VARCHAR(50) NOT NULL,
     `pool` VARCHAR(10) NOT NULL,
     `day1` VARCHAR(50) NOT NULL,
	 `start_time1` VARCHAR(50) NOT NULL,
     `end_time1` VARCHAR(50) NOT NULL,
     `day2` VARCHAR(50) NOT NULL,
     `start_time2` VARCHAR(50) NOT NULL,
     `end_time2` VARCHAR(50) NOT NULL,
 	 PRIMARY KEY (`course_id`,`pool`)
);') or die($mysqli->error);


//create class members table
$mysqli->query('
CREATE TABLE `pooler`.`class_members`
(
     `course_id` VARCHAR(100) NOT NULL,
     `student_id` VARCHAR(50) NOT NULL,
     `teacher_id` VARCHAR(50) NOT NULL,
     `pool` VARCHAR(10) NOT NULL,
     `day1` VARCHAR(50) NOT NULL,
	 `start_time1` VARCHAR(50) NOT NULL,
     `end_time1` VARCHAR(50) NOT NULL,
     `day2` VARCHAR(50) NOT NULL,
     `start_time2` VARCHAR(50) NOT NULL,
     `end_time2` VARCHAR(50) NOT NULL,
 	 PRIMARY KEY (`course_id`,`student_id`)
);') or die($mysqli->error);


//create general time table
$mysqli->query('
CREATE TABLE `pooler`.`general_table`
(
     `table_id` VARCHAR(30) NOT NULL,
     `pool1_time1` VARCHAR(10) NOT NULL,
     `pool2_time1` VARCHAR(10) NOT NULL,
     `pool3_time1` VARCHAR(10) NOT NULL,
     `pool4_time1` VARCHAR(10) NOT NULL,
     `pool5_time1` VARCHAR(10) NOT NULL,
     `pool6_time1` VARCHAR(10) NOT NULL,

     `pool1_time2` VARCHAR(10) NOT NULL,
     `pool2_time2` VARCHAR(10) NOT NULL,
     `pool3_time2` VARCHAR(10) NOT NULL,
     `pool4_time2` VARCHAR(10) NOT NULL,
     `pool5_time2` VARCHAR(10) NOT NULL,
     `pool6_time2` VARCHAR(10) NOT NULL,
     `form` VARCHAR(20) NOT NULL,
     `year` INT NOT NULL,
 	 PRIMARY KEY (`table_id`)
);') or die($mysqli->error);


//create notice table
$mysqli->query('
CREATE TABLE `pooler`.`notice`
(
     `notice_id` VARCHAR(30) NOT NULL,
     `school_year` INT NOT NULL,
     `admin_name` VARCHAR(100) NOT NULL,
     `notice_detail` VARCHAR(5000) NOT NULL,
     `form` VARCHAR(20) NOT NULL,
     `date` VARCHAR(30) NOT NULL,
 	 PRIMARY KEY (`notice_id`,`school_year`)
);') or die($mysqli->error);


//sent messages table
$mysqli->query('
CREATE TABLE `pooler`.`sent`
(
     `sent_id` VARCHAR(30) NOT NULL,
     `student_id` VARCHAR(30) NOT NULL,
     `sent_detail` VARCHAR(5000) NOT NULL,
     `date` VARCHAR(30) NOT NULL,
 	 PRIMARY KEY (`sent_id`,`student_id`)
);') or die($mysqli->error);


//course stats table
$mysqli->query('
CREATE TABLE `pooler`.`stats`
(
     `course_id` VARCHAR(30) NOT NULL,
     `course_year` INT NOT NULL,
     `num_students` VARCHAR(10) NOT NULL,
     `num_pools` VARCHAR(10) NOT NULL,
     `num_teachers` VARCHAR(10) NOT NULL,
 	  PRIMARY KEY (`course_id`,`course_year`)
);') or die($mysqli->error);



//adding course record
$mysqli->query('INSERT INTO `pooler`.`courses`(`course_id`, `teacher_id`, `course_name`, `pool`, `day1`, `start_time1`, `end_time1`, `day2`, `start_time2`, `end_time2`) VALUES ("Geo123", "Mr.Jones", "Geography", "1", "Monday", "10:30 Am", "11:50 Am", "Wednesday", "11:40 Am", "1:00  Am");') or die($mysqli->error);

//adding student record
$mysqli->query('INSERT INTO `pooler`.`students`(`student_id`, `fname`, `lname`, `password`, `age`, `form`, `gender`, `telephone`, `parent_telephone`, `career1`, `career2`,`career3`,`year`) VALUES ("stud1995", "Gary", "Roberts","1995", "22", "4-5", "Male", "529-4118", "426-783", "Doctor", "Mechanical Engineer", "Civil Engineer",2017);') or die($mysqli->error);

//adding teacher record
$mysqli->query('INSERT INTO `pooler`.`teachers`(`teacher_id`, `fname`, `lname`,`gender`,`password`) VALUES ("teach1995", "John", "Brown","Male","1995");') or die($mysqli->error);

echo "Information added to database successfully";

?>