<?php

//include config file
include('../config.inc.php');

//set the database variables 
$servername = $config['DB']['ServerName'];
$username = $config['DB']['UserName'];
$password = $config['DB']['Password'];
$dbname = $config['DB']['Database'];

//create SQL connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

//check SQL Connection
if (!$conn) {
	die("SQL Connection Failed: " . mysqli_connect_error());
}

if ($conn) {
echo "<font color='green'>&#10004;</font>Database Connection Established Successfully</br>";
sleep(10);

//Create ASSET Table
$ASSET_SQL = "CREATE TABLE `ASSET` ( `ID` int( 255 ) NOT NULL auto_increment,
`SERVICE_ID` varchar( 1000 ) NOT NULL, 
`CUSTOMER_ID` int( 255 ) NOT NULL,
`SERVICE_NAME` varchar( 1000 ) NOT NULL, 
`ASSET_TYPE_ID` int( 100 ) NOT NULL,
PRIMARY KEY ( `ID` ),
UNIQUE KEY `SERVICE_ID` ( `SERVICE_ID` ) ) ENGINE = MyISAM DEFAULT CHARSET = latin1;";

if (mysqli_query($conn, $ASSET_SQL)) {
	echo "<font color='green'>&#10004;</font>Table 1 of 8 Created Successfully</br>";
}
else {
	echo "<font color='red'>&#10008;</font>Table 1 of 8 Failed to create!</br>";
	echo "please provide below information to support</br>";
	echo mysqli_error($conn);
}
sleep(10);

//Create ASSET_TYPE Table
$ASSET_TYPE_SQL = "CREATE TABLE `ASSET_TYPE` ( `ID` int( 100 ) NOT NULL auto_increment,
`TYPE` varchar( 1000 ) NOT NULL,
PRIMARY KEY ( `ID` ) ) ENGINE = MyISAM DEFAULT CHARSET = latin1;";

if (mysqli_query($conn, $ASSET_TYPE_SQL)) {
        echo "<font color='green'>&#10004;</font>Table 2 of 8 Created Successfully</br>";
}
else {
        echo "<font color='red'>&#10008;</font>Table 2 of 8 Failed to create!</br>";
        echo "please provide below information to support</br>";
        echo mysqli_error($conn);
}
sleep(10);


//Create JOB Table
$JOB_SQL = "CREATE TABLE `JOB` ( `JOB_ID` int( 255 ) NOT NULL auto_increment ,
`JOB_NAME` varchar( 1000 ) NOT NULL ,
`SOP_URL` varchar( 1000 ) NOT NULL ,
PRIMARY KEY ( `JOB_ID` ) ) ENGINE = MyISAM DEFAULT CHARSET = latin1;";

if (mysqli_query($conn, $JOB_SQL)) {
        echo "<font color='green'>&#10004;</font>Table 3 of 8 Created Successfully</br>";
}
else {
        echo "<font color='red'>&#10008;</font>Table 3 of 8 Failed to create!</br>";
        echo "please provide below information to support</br>";
        echo mysqli_error($conn);
}
sleep(10);


//Create JOB_LOGGING Table
$JOB_LOGGING_SQL= "CREATE TABLE `JOB_LOGGING` ( `ID` int( 255 ) NOT NULL auto_increment ,
`TASK_ID` varchar( 1000 ) NOT NULL ,
`JOB_ID` int( 255 ) NOT NULL ,
`DUE_DATE` date NOT NULL ,
`DATE_COMPLETED` date NOT NULL ,
`STATUS_ID` int( 255 ) NOT NULL ,
`NOTE` varchar( 1000 ) NOT NULL ,
`USER` varchar( 1000 ) NOT NULL ,
PRIMARY KEY ( `ID` ) ) ENGINE = MyISAM DEFAULT CHARSET = latin1;";

if (mysqli_query($conn, $JOB_LOGGING_SQL)) {
        echo "<font color='green'>&#10004;</font>Table 4 of 8 Created Successfully</br>";
}
else {
        echo "<font color='red'>&#10008;</font>Table 4 of 8 Failed to create!</br>";
        echo "please provide below information to support</br>";
        echo mysqli_error($conn);
}
sleep(10);


//Create JOB_STATUS_ID Table
$JOB_STATUS_ID_SQL= "CREATE TABLE `JOB_STATUS_ID` ( `ID` int( 255 ) NOT NULL auto_increment ,
`DESCRIPTION` varchar( 1000 ) NOT NULL ,
PRIMARY KEY ( `ID` ) ) ENGINE = MyISAM DEFAULT CHARSET = latin1;";

if (mysqli_query($conn, $JOB_STATUS_ID_SQL)) {
        echo "<font color='green'>&#10004;</font>Table 5 of 8 Created Successfully</br>";
}
else {
        echo "<font color='red'>&#10008;</font>Table 5 of 8 Failed to create!</br>";
        echo "please provide below information to support</br>";
        echo mysqli_error($conn);
}
sleep(10);


//Create SCHEDULE_ID Table
$SCHEDULE_ID_SQL = "CREATE TABLE `SCHEDULE_ID` ( `ID` int( 255 ) NOT NULL auto_increment ,
`DESCRIPTION` varchar( 1000 ) NOT NULL ,
PRIMARY KEY ( `ID` ) ) ENGINE = MyISAM DEFAULT CHARSET = latin1;";

if (mysqli_query($conn, $SCHEDULE_ID_SQL)) {
        echo "<font color='green'>&#10004;</font>Table 6 of 8 Created Successfully</br>";
}
else {
        echo "<font color='red'>&#10008;</font>Table 6 of 8 Failed to create!</br>";
        echo "please provide below information to support</br>";
        echo mysqli_error($conn);
}
sleep(10);


//Create TASK Table
$TASK_SQL = "CREATE TABLE `TASK` ( `ID` int( 255 ) NOT NULL auto_increment ,
`SERVICE_ID` varchar( 1000 ) NOT NULL ,
`CUSTOMER_ID` int( 255 ) NOT NULL ,
`SERVICE_NAME` varchar( 1000 ) NOT NULL ,
`JOB_ID` int( 255 ) NOT NULL ,
`JOB_STATUS_ID` int( 100 ) NOT NULL ,
`SCHEDULE_ID` int( 255 ) NOT NULL ,
`TASK_STATUS_ID` int( 255 ) NOT NULL ,
PRIMARY KEY ( `ID` ) ) ENGINE = MyISAM DEFAULT CHARSET = latin1;";

if (mysqli_query($conn, $TASK_SQL)) {
        echo "<font color='green'>&#10004;</font>Table 7 of 8 Created Successfully</br>";
}
else {
        echo "<font color='red'>&#10008;</font>Table 7 of 8 Failed to create!</br>";
        echo "please provide below information to support</br>";
        echo mysqli_error($conn);
}
sleep(10);


//Create TASK_STATUS_ID Table
$TASK_STATUS_ID_SQL = "CREATE TABLE `TASK_STATUS_ID` ( `ID` int( 100 ) NOT NULL auto_increment ,
`DESCRIPTION` varchar( 1000 ) NOT NULL ,
PRIMARY KEY ( `ID` ) ) ENGINE = MyISAM DEFAULT CHARSET = latin1;";

if (mysqli_query($conn, $TASK_STATUS_ID_SQL)) {
        echo "<font color='green'>&#10004;</font>Table 8 of 8 Created Successfully</br>";
}
else {
        echo "<font color='red'>&#10008;</font>Table 8 of 8 Failed to create!</br>";
        echo "please provide below information to support</br>";
        echo mysqli_error($conn);
}
sleep(10);

//Insert Default Job Status IDs
$JOB_STATUS_ID_INSERT ="INSERT INTO `JOB_STATUS_ID` (`ID`,`DESCRIPTION`) VALUES ('1', 'Completed -- OK');";
$JOB_STATUS_ID_INSERT1 ="INSERT INTO `JOB_STATUS_ID` (`ID`,`DESCRIPTION`) VALUES ('2', 'Not Completed');"; 
$JOB_STATUS_ID_INSERT2 ="INSERT INTO `JOB_STATUS_ID` (`ID`,`DESCRIPTION`) VALUES ('3', 'Completed -- OK -- With Note');";
$JOB_STATUS_ID_INSERT3 ="INSERT INTO `JOB_STATUS_ID` (`ID`,`DESCRIPTION`) VALUES ('4', 'Overdue');";
$JOB_STATUS_ID_INSERT4 ="INSERT INTO `JOB_STATUS_ID` (`ID`,`DESCRIPTION`) VALUES ('5', 'Due Today');";
$JOB_STATUS_ID_INSERT5 ="INSERT INTO `JOB_STATUS_ID` (`ID`,`DESCRIPTION`) VALUES ('6', 'Due Within Next 1-5 Days');";
$JOB_STATUS_ID_INSERT6 ="INSERT INTO `JOB_STATUS_ID` (`ID`,`DESCRIPTION`) VALUES ('7', 'Due within Next 5-10 Days');";

if (mysqli_query($conn, $JOB_STATUS_ID_INSERT)) {
        echo "<font color='green'>&#10004;</font>Status 1 of 7 Created Successfully</br>";
}
else {
        echo "<font color='red'>&#10008;</font>Table 1 of 7 Failed to create!</br>";
        echo "please provide below information to support</br>";
        echo mysqli_error($conn);
}
sleep(10);

if (mysqli_query($conn, $JOB_STATUS_ID_INSERT1)) {
        echo "<font color='green'>&#10004;</font>Status 2 of 7 Created Successfully</br>";
}
else {
        echo "<font color='red'>&#10008;</font>Status 2 of 7 Failed to create!</br>";
        echo "please provide below information to support</br>";
        echo mysqli_error($conn);
}
sleep(10);

if (mysqli_query($conn, $JOB_STATUS_ID_INSERT2)) {
        echo "<font color='green'>&#10004;</font>Status 3 of 7 Created Successfully</br>";
}
else {
        echo "<font color='red'>&#10008;</font>Status 3 of 7 Failed to create!</br>";
        echo "please provide below information to support</br>";
        echo mysqli_error($conn);
}
sleep(10);

if (mysqli_query($conn, $JOB_STATUS_ID_INSERT3)) {
        echo "<font color='green'>&#10004;</font>Status 4 of 7 Created Successfully</br>";
}
else {
        echo "<font color='red'>&#10008;</font>Status 4 of 7 Failed to create!</br>";
        echo "please provide below information to support</br>";
        echo mysqli_error($conn);
}
sleep(10);

if (mysqli_query($conn, $JOB_STATUS_ID_INSERT4)) {
        echo "<font color='green'>&#10004;</font>Status 5 of 7 Created Successfully</br>";
}
else {
        echo "<font color='red'>&#10008;</font>Status 5 of 7 Failed to create!</br>";
        echo "please provide below information to support</br>";
        echo mysqli_error($conn);
}
sleep(10);

if (mysqli_query($conn, $JOB_STATUS_ID_INSERT5)) {
        echo "<font color='green'>&#10004;</font>Status 6 of 7 Created Successfully</br>";
}
else {
        echo "<font color='red'>&#10008;</font>Status 6 of 7 Failed to create!</br>";
        echo "please provide below information to support</br>";
        echo mysqli_error($conn);
}
sleep(10);

if (mysqli_query($conn, $JOB_STATUS_ID_INSERT6)) {
        echo "<font color='green'>&#10004;</font>Status 7 of 7 Created Successfully</br>";
}
else {
        echo "<font color='red'>&#10008;</font>Status 7 of 7 Failed to create!</br>";
        echo "please provide below information to support</br>";
        echo mysqli_error($conn);
}
sleep(10);

//Insert Default Schedule IDs
$SCHEDULE_ID_INSERT = "INSERT INTO `SCHEDULE_ID`(`ID`,`DESCRIPTION`) VALUES ('1', 'Daily');";

if (mysqli_query($conn, $SCHEDULE_ID_INSERT)) {
        echo "<font color='green'>&#10004;</font>Schedule 1 of 1 Created Successfully</br>";
}
else {
        echo "<font color='red'>&#10008;</font>Schedule 1 of 1 Failed to create!</br>";
        echo "please provide below information to support</br>";
        echo mysqli_error($conn);
}
sleep(10);

//Insert Task Status ID
$TASK_STATUS_ID_INSERT = " INSERT INTO `TASK_STATUS_ID` (`ID`,`DESCRIPTION`) VALUES ('1', 'Active');";
$TASK_STATUS_ID_INSERT1 = " INSERT INTO `TASK_STATUS_ID` (`ID`,`DESCRIPTION`) VALUES ('2', 'Suspended');";

if (mysqli_query($conn, $TASK_STATUS_ID_INSERT)) {
        echo "<font color='green'>&#10004;</font>Task Status 1 of 2 Created Successfully</br>";
}
else {
        echo "<font color='red'>&#10008;</font>Task Status 1 of 2 Failed to create!</br>";
        echo "please provide below information to support</br>";
        echo mysqli_error($conn);
}
sleep(10);

if (mysqli_query($conn, $TASK_STATUS_ID_INSERT1)) {
        echo "<font color='green'>&#10004;</font>Task Status 2 of 2 Created Successfully</br>";
}
else {
        echo "<font color='red'>&#10008;</font>Task Status 2 of 2 Failed to create!</br>";
        echo "please provide below information to support</br>";
        echo mysqli_error($conn);
}
sleep(10);

echo "Expo has now been installed. Please remove the Install folder for increased security. You can now visit the program via https://" .$config['URL'] ."";
sleep(10);

}
mysqli_close($conn);
?>
