<!DOCTYPE html>
<!--
********************************************************************************

    filename:    foodsinit.php
    description: initialize food database table
    inputs:      none
    outputs:     none
    actions:     none
    redirection: none

********************************************************************************
-->
<html>
    <head>
        <?php include 'head.html' ?>
        <script src = "navigation.js"></script>
    </head>
    <body>
        <?php include 'navigationmanagedatabase.html' ?>
        <h3>INITIALIZE FOOD LIST TABLE</h3>
        <div id="content">
            <?php
/*
                ... connect to database
*/
                include 'functions.php';
                $conn = connectToDB();

/*
                ... drop foods table
*/
                $sql = "DROP TABLE IF EXISTS foods";
                if (!mysqli_query($conn, $sql)) {
                    echo "Error dropping table: " . mysqli_error($conn);
                }

/*
                ... create foods table
*/
                $sql = "CREATE TABLE foods ("
                    . "foodId INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,"
                    . "food VARCHAR(256) NULL,"
                    . DONENESSES . ","
                    . "externalTemp INT UNSIGNED NULL," // 0-999 degC
                    . "externalHighTemp INT UNSIGNED NULL," // 0-999 degC, too low alert
                    . "externalLowTemp INT UNSIGNED NULL," // 0-999 degC, too high alert
                    . "internalTemp INT UNSIGNED NULL," // 0-999 degC, done alert
                    . "internalHighTemp INT UNSIGNED NULL," // 0-999 degC, over done alert
                    . "internalLowTemp INT UNSIGNED NULL," // 0-999 degC, near done alert
                    . "comments VARCHAR(256) NULL)";
                if (!mysqli_query($conn, $sql)) {
                    error_log("Error creating table: " . mysqli_error($conn));
                    exit('"foods" table NOT initialized');
                }

/*
                ... populate foods table
*/
/*
                    ... beef
*/
                    $sql = "INSERT INTO foods (food, doneness, externalTemp, externalHighTemp, externalLowTemp,  internalTemp,
                        internalHighTemp, internalLowTemp)
                        VALUES ('Beef', 'Extra Rare', 250, 270, 230, 115, 120, 110)";
                    if (!mysqli_query($conn, $sql)) {
                        error_log("Foods Initialization Error: " . $sql . "<br>" . mysqli_error($conn));
                        exit('"foods" table NOT initialized');
                    }
                    $sql = "INSERT INTO foods (food, doneness, externalTemp, externalHighTemp, externalLowTemp,  internalTemp,
                        internalHighTemp, internalLowTemp)
                        VALUES ('Beef', 'Rare', 250, 270, 230, 125, 130, 120)";
                    if (!mysqli_query($conn, $sql)) {
                        error_log("Foods Initialization Error: " . $sql . "<br>" . mysqli_error($conn));
                        exit('"foods" table NOT initialized');
                    }
                    $sql = "INSERT INTO foods (food, doneness, externalTemp, externalHighTemp, externalLowTemp,  internalTemp,
                        internalHighTemp, internalLowTemp)
                        VALUES ('Beef', 'Medium Rare', 250, 270, 230, 130, 140, 125)";
                    if (!mysqli_query($conn, $sql)) {
                        error_log("Foods Initialization Error: " . $sql . "<br>" . mysqli_error($conn));
                        exit('"foods" table NOT initialized');
                    }
                    $sql = "INSERT INTO foods (food, doneness, externalTemp, externalHighTemp, externalLowTemp,  internalTemp,
                    internalHighTemp, internalLowTemp)
                    VALUES ('Beef', 'Medium', 250, 270, 230, 140, 150, 135)";
                    if (!mysqli_query($conn, $sql)) {
                        error_log("Foods Initialization Error: " . $sql . "<br>" . mysqli_error($conn));
                        exit('"foods" table NOT initialized');
                    }
                    $sql = "INSERT INTO foods (food, doneness, externalTemp, externalHighTemp, externalLowTemp,  internalTemp,
                        internalHighTemp, internalLowTemp)
                        VALUES ('Beef', 'Medium Well', 250, 270, 230, 150, 155, 145)";
                    if (!mysqli_query($conn, $sql)) {
                        echo "Foods Initialization Error: " . $sql . "<br>" . mysqli_error($conn);
                        exit('"foods" table NOT initialized');
                    }
                    $sql = "INSERT INTO foods (food, doneness, externalTemp, externalHighTemp, externalLowTemp,  internalTemp, internalHighTemp, internalLowTemp)
VALUES ('Beef', 'Well Done', 250, 270, 230, 160, 170, 155)";
                    if (!mysqli_query($conn, $sql)) {
                        echo "Foods Initialization Error: " . $sql . "<br>" . mysqli_error($conn);
                        exit('"foods" table NOT initialized');
                    }
/*
                    ... pork
*/
                    $sql = "INSERT INTO foods (food, doneness, externalTemp, externalHighTemp, externalLowTemp,  internalTemp, internalHighTemp, internalLowTemp, comments)
VALUES ('Pork', 'Medium', 350, 370, 330, 145, 155, 140, 'pork chops, pork tenderloin')";
                    if (!mysqli_query($conn, $sql)) {
                        echo "Foods Initialization Error: " . $sql . "<br>" . mysqli_error($conn);
                        exit('"foods" table NOT initialized');
                    }
                    $sql = "INSERT INTO foods (food, doneness, externalTemp, externalHighTemp, externalLowTemp,  internalTemp, internalHighTemp, internalLowTemp, comments)
VALUES ('Pork', 'Medium Well', 350, 370, 330, 160, 165, 155, 'pork chops, pork tenderloin')";
                    if (!mysqli_query($conn, $sql)) {
                        echo "Foods Initialization Error: " . $sql . "<br>" . mysqli_error($conn);
                        exit('"foods" table NOT initialized');
                    }
                    $sql = "INSERT INTO foods (food, doneness, externalTemp, externalHighTemp, externalLowTemp,  internalTemp, internalHighTemp, internalLowTemp, comments)
VALUES ('Pork', 'Well Done', 350, 370, 330, 175, 185, 170, 'pork chops, pork tenderloin')";
                    if (!mysqli_query($conn, $sql)) {
                        echo "Foods Initialization Error: " . $sql . "<br>" . mysqli_error($conn);
                        exit('"foods" table NOT initialized');
                    }
/*
                    ... turkey
*/
                    $sql = "INSERT INTO foods (food, doneness, externalTemp, externalHighTemp, externalLowTemp,  internalTemp, internalHighTemp, internalLowTemp, comments)
VALUES ('Chicken (dark meat)', 'Well Done', 350, 330, 370, 170, 175, 170, 'thigh, leg')";
                    if (!mysqli_query($conn, $sql)) {
                        echo "Foods Initialization Error: " . $sql . "<br>" . mysqli_error($conn);
                        exit('"foods" table NOT initialized');
                    }
                    $sql = "INSERT INTO foods (food, doneness, externalTemp, externalHighTemp, externalLowTemp,  internalTemp, internalHighTemp, internalLowTemp, comments)
VALUES ('Chicken (white meat)', 'Well Done', 350, 330, 370, 160, 165, 160, 'breast, wing')";
                    if (!mysqli_query($conn, $sql)) {
                        echo "Foods Initialization Error: " . $sql . "<br>" . mysqli_error($conn);
                        exit('"foods" table NOT initialized');
                    }
/*
                    ... chicken
*/
                    $sql = "INSERT INTO foods (food, doneness, externalTemp, externalHighTemp, externalLowTemp,  internalTemp, internalHighTemp, internalLowTemp, comments)
VALUES ('Chicken (dark meat)', 'Well Done', 350, 330, 370, 170, 175, 170, 'thigh, leg')";
                    if (!mysqli_query($conn, $sql)) {
                        echo "Foods Initialization Error: " . $sql . "<br>" . mysqli_error($conn);
                        exit('"foods" table NOT initialized');
                    }
                    $sql = "INSERT INTO foods (food, doneness, externalTemp, externalHighTemp, externalLowTemp,  internalTemp, internalHighTemp, internalLowTemp, comments)
VALUES ('Chicken (white meat)', 'Well Done', 350, 330, 370, 160, 165, 160, 'breast, wing')";
                    if (!mysqli_query($conn, $sql)) {
                        error_log("Foods Initialization Error: " . $sql . "<br>" . mysqli_error($conn));
                        exit('"foods" table NOT initialized');
                    }
/*
                    ... fish
*/
                    $sql = "INSERT INTO foods (food, doneness, externalTemp, externalHighTemp, externalLowTemp,  internalTemp, internalHighTemp, internalLowTemp)
VALUES ('Fish', 'Medium Rare', 375, 400, 350, 120, 120, 120)";
                    if (!mysqli_query($conn, $sql)) {
                        error_log("Foods Initialization Error: " . $sql . "<br>" . mysqli_error($conn));
                        exit('"foods" table NOT initialized');
                    }
                    $sql = "INSERT INTO foods (food, doneness, externalTemp, externalHighTemp, externalLowTemp,  internalTemp, internalHighTemp, internalLowTemp)
VALUES ('Fish', 'Medium', 375, 400, 350, 135, 135, 135)";
                    if (!mysqli_query($conn, $sql)) {
                        error_log("Foods Initialization Error: " . $sql . "<br>" . mysqli_error($conn));
                        exit('"foods" table NOT initialized');
                    }
/*
                    ... lamb
*/
                    $sql = "INSERT INTO foods (food, doneness, externalTemp, externalHighTemp, externalLowTemp,  internalTemp, internalHighTemp, internalLowTemp)
VALUES ('Lamb', 'Rare', 350, 330, 370, 130, 130, 130)";
                    if (!mysqli_query($conn, $sql)) {
                        error_log("Foods Initialization Error: " . $sql . "<br>" . mysqli_error($conn));
                        exit('"foods" table NOT initialized');
                    }
                    $sql = "INSERT INTO foods (food, doneness, externalTemp, externalHighTemp, externalLowTemp,  internalTemp, internalHighTemp, internalLowTemp)
VALUES ('Lamb', 'Medium Rare', 350, 330, 370, 135, 140, 130)";
                    if (!mysqli_query($conn, $sql)) {
                        error_log("Foods Initialization Error: " . $sql . "<br>" . mysqli_error($conn));
                        exit('"foods" table NOT initialized');
                    }
                    $sql = "INSERT INTO foods (food, doneness, externalTemp, externalHighTemp, externalLowTemp,  internalTemp, internalHighTemp, internalLowTemp)
VALUES ('Lamb', 'Medium', 350, 330, 370, 145, 155, 140)";
                    if (!mysqli_query($conn, $sql)) {
                        error_log("Foods Initialization Error: " . $sql . "<br>" . mysqli_error($conn));
                        exit('"foods" table NOT initialized');
                    }
                    $sql = "INSERT INTO foods (food, doneness, externalTemp, externalHighTemp, externalLowTemp,  internalTemp, internalHighTemp, internalLowTemp)
VALUES ('Lamb', 'Medium Well', 350, 330, 370, 160, 165, 155)";
                    if (!mysqli_query($conn, $sql)) {
                        echo_log("Foods Initialization Error: " . $sql . "<br>" . mysqli_error($conn));
                        exit('"foods" table NOT initialized');
                    }
                    $sql = "INSERT INTO foods (food, doneness, externalTemp, externalHighTemp, externalLowTemp,  internalTemp, internalHighTemp, internalLowTemp)
VALUES ('Lamb', 'Well Done', 350, 330, 370, 175, 180, 170)";
                    if (!mysqli_query($conn, $sql)) {
                        error_log("Foods Initialization Error: " . $sql . "<br>" . mysqli_error($conn));
                        exit('"foods" table NOT initialized');
                    }
/*
                    ... veal
*/
                    $sql = "INSERT INTO foods (food, doneness, externalTemp, externalHighTemp, externalLowTemp,  internalTemp, internalHighTemp, internalLowTemp)
VALUES ('Veal', 'Medium', 250, 270, 250, 150, 155, 145)";
                    if (!mysqli_query($conn, $sql)) {
                        error_log("Foods Initialization Error: " . $sql . "<br>" . mysqli_error($conn));
                        exit('"foods" table NOT initialized');
                    }
/*
                ... close database connection
*/
                mysqli_close($conn);
                echo '"foods" table initialized';
            ?>
        </div>
    </body>
</html>