var express = require('express');//Express method
var app = express();
var mysql= require('mysql');
app.use(express.static("."));//Static Pages change if you change the location
var con = mysql.createConnection({
  host:  'localhost',
  user:  'root',
  password: 'Wistful1231',
  database: 'students'});

app.listen(8080,function(){
  console.log("Server's Running") // Just to show the server is up
});

app.get('/student',function(req,res){
 con.query("SELECT * FROM student",function(err,rows,fields){
		if(err){
			console.log('Error during query processing');
			console.log(err);
			res.send('Error during query processing');
		}
		else{
			//contruct html to be sent to client
			var Output = '<table border="1"><tr>';
			//process column headers
			var headers = [];
			for(i=0; i<fields.length; i++){
				headers.push(fields[i].name);
				Output += '<th>' + fields[i].name + '</th>';
			}
			Output += '</tr>';
			//process row values
			for(i=0; i<rows.length; i++){
				Output += '<tr>';
				for(j=0; j<headers.length; j++){
					Output += '<td>' + rows[i][headers[j]] + '</td>';
				}
				Output += '</tr>';
			}
			Output += '</table>'
			console.log('Sending  student');
			res.send(Output);
    }
  });
});

app.get('/grades',function(req,res){
  con.query("SELECT * FROM grades",function(err,rows,fields){
     if(err){
       console.log('Error during query processing');
       console.log(err);
       res.send('Error during query processing');
     }
     else{
       //contruct html to be sent to client
       var Output = '<table border="1"><tr>';
       //process column headers
       var headers = [];
       for(i=0; i<fields.length; i++){
         headers.push(fields[i].name);
         Output += '<th>' + fields[i].name + '</th>';
       }
       Output += '</tr>';
       //process row values
       for(i=0; i<rows.length; i++){
         Output += '<tr>';
         for(j=0; j<headers.length; j++){
           Output += '<td>' + rows[i][headers[j]] + '</td>';
         }
         Output += '</tr>';
       }
       Output += '</table>'
       console.log('Sending grades');
       res.send(Output);
     }
   });
 });

 app.get('/courses',function(req,res){
  con.query("SELECT * FROM courses",function(err,rows,fields){
     if(err){
       console.log('Error during query processing');
       console.log(err);
       res.send('Error during query processing');
     }
     else{
       //contruct html to be sent to client
       var Output = '<table border="1"><tr>';
       //process column headers
       var headers = [];
       for(i=0; i<fields.length; i++){
         headers.push(fields[i].name);
         Output += '<th>' + fields[i].name + '</th>';
       }
       Output += '</tr>';
       //process row values
       for(i=0; i<rows.length; i++){
         Output += '<tr>';
         for(j=0; j<headers.length; j++){
           Output += '<td>' + rows[i][headers[j]] + '</td>';
         }
         Output += '</tr>';
       }
       Output += '</table>'
       console.log('Sending  courses');
       res.send(Output);
     }
   });
 });


app.get('/main.html', function(req,res){
  res.open(main.html); //Opening the html file
});

app.get('/student',function(req,res){
  con.query("SELECT * FROM student",function(err,rows,fields){
     if(err){
       console.log('Error during query processing');
       console.log(err);
       res.send('Error during query processing');
     }
     else{
       // Data to be sent to html side
       var Output = '<table border="1"><tr>';
       //Adding tables to html
       var headers = [];
       for(i=0; i<fields.length; i++){
         headers.push(fields[i].name);
         Output += '<th>' + fields[i].name + '</th>';
       }
       Output += '</tr>';
       //process row values
       for(i=0; i<rows.length; i++){
         Output += '<tr>';
         for(j=0; j<headers.length; j++){
           Output += '<td>' + rows[i][headers[j]] + '</td>';
         }
         Output += '</tr>';
       }
       Output += '</table>'
       console.log('Sending  Student table');
       res.send(Output);
     }
   });
 });

 app.get('/grades',function(req,res){
   con.query("SELECT * FROM grades",function(err,rows,fields){
      if(err){
        console.log('Error ');
        console.log(err);
        res.send('Error');
      }
      else{
        //Html data to be sent to the client side
        var Output = '<table border="1"><tr>';
        var headers = [];
        for(i=0; i<fields.length; i++){
          headers.push(fields[i].name);
          Output += '<th>' + fields[i].name + '</th>';
        }
        Output += '</tr>';
        for(i=0; i<rows.length; i++){
          Output += '<tr>';
          for(j=0; j<headers.length; j++){
            Output += '<td>' + rows[i][headers[j]] + '</td>';
          }
          Output += '</tr>';
        }
        Output += '</table>'
        console.log('Sending Grades Table');
        res.send(Output);
      }
    });
  });

app.get('/transcript', function (req,res){
    //getting name and the term from the user input
    var name = req.query.n;
    var term = req.query.t;
    console.log('Processing transcript request for student ID=' + name);
    q_str = 'select student.`Student ID`,`First Name`,`Last Name`,Term,courses.`Course ID`,`Course Description`,Grade from student, courses,grades WHERE student.`Student ID` = grades.studentid AND courses.`Course ID` = grades.courseid AND `First Name` = ' + name + ' ' + 'and Term = ' + term + '   ' + ';';

    con.query(q_str, function(err,rows,fields){
      if(err){
        console.log('Error during transcript query processing');
        console.log(err);
        res.send('Error during transcript query processing');
      }
      else{

        var Output = '<table border="1"><tr>';

        var headers = [];
        for(i=0; i<fields.length; i++){
          headers.push(fields[i].name);
          Output += '<th>' + fields[i].name + '</th>';
        }
        Output += '</tr>';

        for(i=0; i<rows.length; i++){
          Output += '<tr>';
          for(j=0; j<headers.length; j++){
            Output += '<td>' + rows[i][headers[j]] + '</td>';
          }
          Output += '</tr>';
        }
        Output += '</table>'
        console.log('Sending transcript');
        res.send(Output);
      }
    });
  });