#!/usr/bin/env php

<?php

//Variables
$servername = "localhost";
$username = "root";
$password = "test123";
$dbname = "employees";
$resultArr = array();

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

//Request to get the top 5 paid employees by their emp_no sorting only the latest date (9999)
$sqlSal = "SELECT emp_no, salary FROM salaries WHERE INSTR(to_date, 9999) ORDER BY salary DESC LIMIT 5";
$salResult = mysqli_query($conn, $sqlSal);

if ($salResult->num_rows > 0) {
    // output data of each row


    $i = 0;

    while($row = $salResult->fetch_assoc()) {
        //echo "emp_no: " . $row["emp_no"]. " - Salary: " . $row["salary"];
        //echo "  i=" . $i . "\n";

        //Getting into the joint table to get dept_no from emp_no
        $sqlDeptEmp = "SELECT dept_no FROM dept_emp WHERE emp_no=" . $row["emp_no"];
        $deptEmpResult = mysqli_query($conn, $sqlDeptEmp);
        $deptNo = $deptEmpResult->fetch_assoc();


        //Getting deppartment name using dept_no
        $sqlDept = "SELECT dept_name FROM departments WHERE dept_no='" . $deptNo["dept_no"] . "'";
        $deptResult = mysqli_query($conn, $sqlDept);
        $dept = $deptResult->fetch_assoc();


        //Getting Name
        $sqlName = "SELECT first_name, last_name FROM employees WHERE emp_no=" . $row["emp_no"];
        $nameResult = mysqli_query($conn, $sqlName);
        $name = $nameResult->fetch_assoc();


        //Getting Title
        $sqlTitle = "SELECT title FROM titles WHERE INSTR(to_date, 9999) AND emp_no=" . $row["emp_no"];
        $titleResult = mysqli_query($conn, $sqlTitle);
        $title = $titleResult->fetch_assoc();

        //Putting all fields into final array before posting to json file
        $resultArr[$i] = array($title["title"] , $name["first_name"], $name["last_name"], $row["salary"], $deptNo["dept_no"]);
        $i = $i +1;
    }

   file_put_contents('top5.json', json_encode($resultArr));

} else {
    echo "0 results";
}

?>