// To compile:
// javac -cp lib\mysql-selector-java.jar FaultReport.java
// set CLASSPATH=D:\Koulu\ot01\harjoitustyö\src\tervo_juho;D:\Koulu\ot01\harjoitustyö\src\tervo_juho\lib\mysql-connector-java.jar

import java.sql.*;
import java.util.Scanner;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * @author      Tervo Juho <juho.tervo@ee.tamk.fi>
 * @version     2011-05-07
 * @since       1.0
 */

 class FaultReport {

    public static Scanner cin = new Scanner(System.in);

    public static String driverName = "com.mysql.jdbc.Driver";
    public static String url = "jdbc:mysql://mydb.tamk.fi/dbt8jtervo1"
                             + "?user=t8jtervo&password=Nimrod666";

    public static void main (String[] args) {

        boolean personCheck = false;
        boolean isAdmin = false;
        int personId = 0;
        int chooser;

        System.out.println("Welcome to Testing House!");
        System.out.println("This program is intended for posting, receiving and reviewing fault reports.");

        System.out.println();

        System.out.println("What is your id number?");

        do {

            personId = getInt("What is your id number?");
            personCheck = checkId(personId);

            if (!personCheck) {

                System.out.println("No person with your id number were found in our database!");

            }

        }
        while (!personCheck);

        do {

            System.out.println("What would you like to do?");
            System.out.println("1: Post fault report.");
            System.out.println("2: Receive fault reports. (For system operators only!)");
            System.out.println("3: View faultreport statistics.");
            System.out.println("0: Exit program.");
            System.out.println("Please choose number between 0 - 3.");

            chooser = getInt("Please choose number between 0 - 3.");

            if ( chooser == 1 ) {

                postReport(personId);

            }

            else if ( chooser == 2 ) {

                isAdmin = operatorCheck(personId);

                if (isAdmin) {

                    receiveReport(personId);

                }

                else {

                    System.out.println("You don't have privliges to use this feature!");

                }

            }

            else if ( chooser == 3 ) {

                viewStatistics();

            }

            else if ( chooser == 0 ) {

                System.out.println("Thank you for using this program!");

            }

            else {

                System.out.println("Invalid input!");

            }

        }
        while (chooser != 0);

    }

    public static void postReport(int senderId) {

        int classNumber = 1, priorityNumber = 1;
        int faultId = getId();

        boolean descriptionOk = false;

        String faultClass = " ", faultPriority = " ", faultDescription = " ";
        String faultTime = " ";
        String faultSender = getName(senderId);

        System.out.println("What kind of problem you have?");
        System.out.println("1: hardware");
        System.out.println("2: software");
        System.out.println("3: network");
        System.out.println("4: other");

        do {

            System.out.println("Please choose number between 1 - 4");
            classNumber = getInt("Please choose number between 1 - 4");

            switch (classNumber) {

                case 1: faultClass = "hardware";
                    break;
                case 2: faultClass = "software";
                    break;
                case 3: faultClass = "network";
                    break;
                case 4: faultClass = "other";
                    break;
                default:
                    break;

            }

        }
        while (classNumber > 4 || classNumber < 1);

        System.out.println("What priority is your problem?");
        System.out.println("1: wishlist");
        System.out.println("2: low");
        System.out.println("3: medium");
        System.out.println("4: high");
        System.out.println("5: urgent");

        do {

            System.out.println("Please choose number between 1 - 5");
            classNumber = getInt("Please choose number between 1 - 5");

            switch (classNumber) {

                case 1: faultPriority = "wishlist";
                    break;
                case 2: faultPriority = "low";
                    break;
                case 3: faultPriority = "medium";
                    break;
                case 4: faultPriority = "high";
                    break;
                case 5: faultPriority = "urgent";
                    break;
                default:
                    break;

            }

        }
        while (priorityNumber > 4 || priorityNumber < 1);

        do {

            System.out.println("Short description about your problem: (1000 characters or less)");
            faultDescription = getString("Short description about your problem: (1000 characters or less)");

            if (faultDescription.length() < 1000) {

                descriptionOk = true;

            }

            else {

                System.out.println("Your description is too long!");
                descriptionOk = false;

            }

        }
        while (!descriptionOk);

        faultTime = getTimestamp();

        insertReport(senderId, faultClass, faultPriority, faultDescription, faultTime, faultId);

    }

    public static void insertReport(int senderId, String faultClass, String faultPriority,
                                    String faultDescription, String faultTime, int faultId) {

        try {

            Class.forName(driverName);

            Connection con = DriverManager.getConnection(url);

            String sql = ""                                     // 3
                + "INSERT INTO report "
                + "  ( id, class, description, fix, priority, status, startTime, updateTime, endTime, senderId, handlerId ) "
                + "VALUES "
                + "  ( " + faultId + " "
                + "    , '" + faultClass + "' "
                + "    , '" + faultDescription + "' "
                + "    , NULL "
                + "    , '" + faultPriority + "' "
                + "    , 'open' "
                + "    , '" + faultTime + "' "
                + "    , NULL "
                + "    , NULL "
                + "    , " + senderId + " "
                + "    , NULL "
                + "  ) "
                ;

            Statement stmt = con.createStatement();

            int count = stmt.executeUpdate(sql);

            stmt.close();
            con.close();
        }
        catch (Exception error) {
            System.err.println("Exception: " + error.getMessage() );
        }

    }

    public static void receiveReport(int updaterId) {

        int chooser = 0;

        receiveReportMenu();

        do {

            chooser = getInt("Please choose number between 0 - 4");

            if (chooser == 1) {

                openReport(updaterId);
                receiveReportMenu();

            }

            else if (chooser == 2) {

                listReports("open");
                receiveReportMenu();

            }

            else if (chooser == 3) {

                listReports("investigating");
                receiveReportMenu();

            }

            else if (chooser == 4) {

                listReports("fixed");
                receiveReportMenu();

            }

            else if (chooser == 0) {

                System.out.println("\nExiting to previous menu...\n");

            }

            else {

                System.out.println("Invalid input!");

            }

        }
        while (chooser != 0);

    }

    public static void receiveReportMenu() {

        System.out.println("\nWhat would you like to do?");
        System.out.println("1: open report");
        System.out.println("2: list open reports");
        System.out.println("3: list reports under investigation");
        System.out.println("4: list fixed reports");
        System.out.println("0: exit");
        System.out.println("Please choose number between 0 - 4\n");

    }

    public static void openReport(int updaterId) {

        String updatetPriority = " ";
        String faultFix = " ";

        boolean idChooser = false;
        boolean fixOk = false;
        int reportId = 0;
        int actionChooser = 0;
        int priorityNumber = 1;

        do {

            System.out.println("Give id-number of the wanted report:");
            reportId = getInt("Give id-number of the wanted report:");

            idChooser = reportIdChecker(reportId);

            if(!idChooser) {

                System.out.println("\nThere is no open of investigated report with given id-number!\n");

            }

        }
        while (!idChooser);

        viewReport(reportId);

        do {

            actionChooser = openReportMenu();

            if (actionChooser == 1) {

                System.out.println("New priority for report:");
                System.out.println("1: wishlist");
                System.out.println("2: low");
                System.out.println("3: medium");
                System.out.println("4: high");
                System.out.println("5: urgent");

                do {

                    System.out.println("Please choose number between 1 - 5");
                    priorityNumber = getInt("Please choose number between 1 - 5");

                    switch (priorityNumber) {

                        case 1: updatetPriority = "wishlist";
                            break;
                        case 2: updatetPriority = "low";
                            break;
                        case 3: updatetPriority = "medium";
                            break;
                        case 4: updatetPriority = "high";
                            break;
                        case 5: updatetPriority = "urgent";
                            break;
                        default:
                            break;

                    }

                }
                while (priorityNumber > 4 || priorityNumber < 1);

                updateReport(reportId, updatetPriority, updaterId);

                actionChooser = openReportMenu();

            }

            else if (actionChooser == 2) {

                do {

                    System.out.println("Short fix report: (1000 characters or less)");
                    faultFix = getString("Short fix report: (1000 characters or less)");

                    if (faultFix.length() < 1000) {

                        fixOk = true;

                    }

                    else {

                        System.out.println("Your description is too long!");
                        fixOk = false;

                    }

                }
                while (!fixOk);

                fixReport(reportId, faultFix, updaterId);

                System.out.println("\nExiting to previous menu...\n");

                actionChooser = 0;

            }

            else if (actionChooser == 3) {

                viewReport(reportId);

                actionChooser = openReportMenu();

            }

            else if (actionChooser == 0) {}

            else {

                System.out.println("Invalid input!");

            }

        }
        while (actionChooser != 0);

    }

    public static int openReportMenu() {

        System.out.println("What do you want to do?");
        System.out.println("1: update report");
        System.out.println("2: fix report");
        System.out.println("3: view report again");
        System.out.println("0: exit to previous menu");
        System.out.println("Please choose number between 0 - 3");

        return getInt("Please choose number between 0 - 3");

    }

    public static void updateReport(int reportId, String reportPriority, int updaterId) {

        try {

            Class.forName(driverName);

            Connection con = DriverManager.getConnection(url);

            String sql = ""
                + "UPDATE   report "
                + "SET      priority = '" + reportPriority + "' "
                + "         , status = 'investigating' "
                + "         , updateTime = '" + getTimestamp() + "' "
                + "         , handlerId = '" + updaterId + "' "
                + "WHERE    id = " + reportId + " ";

            Statement stmt = con.createStatement();

            int count = stmt.executeUpdate(sql);

            stmt.close();
            con.close();
        }
        catch (Exception error) {
            System.err.println("Exception: " + error.getMessage() );
        }

    }

    public static void fixReport(int reportId, String reportFix, int updaterId) {

        try {

            Class.forName(driverName);

            Connection con = DriverManager.getConnection(url);

            String sql = ""
                + "UPDATE   report "
                + "SET      fix = '" + reportFix + "' "
                + "         , status = 'fixed' "
                + "         , endTime = '" + getTimestamp() + "' "
                + "         , handlerId = '" + updaterId + "' "
                + "WHERE    id = " + reportId + " ";

            Statement stmt = con.createStatement();

            int count = stmt.executeUpdate(sql);

            stmt.close();
            con.close();
        }
        catch (Exception error) {
            System.err.println("Exception: " + error.getMessage() );
        }

    }

    public static void viewReport(int reportId) {

        try {

            Class.forName(driverName);

            Connection con = DriverManager.getConnection(url);

            String sql = "SELECT    * "
                       + "FROM      report "
                       + "WHERE     id = " + reportId + " ";

            Statement stmt = con.createStatement();

            ResultSet result = stmt.executeQuery(sql);

            while(result.next()) {

                int idDatabase = result.getInt("id");

                if (idDatabase == reportId) {

                    System.out.println("\nReport id: " + result.getInt("id"));
                    System.out.println("Class: " + result.getString("class"));
                    System.out.println("Priority: " + result.getString("priority"));
                    System.out.println("Status: " + result.getString("status"));
                    System.out.println("Description: " + result.getString("description"));
                    System.out.println("Posted: " + result.getString("startTime"));
                    System.out.println("Sender: " + getName(result.getInt("senderId")) + "\n");

                    int updater = result.getInt("handlerId");
                    boolean empty = result.wasNull();

                    if (!empty) {

                        System.out.println("Last updater: " + getName(updater));
                        System.out.println("Last update: " + result.getString("updateTime") + "\n");

                    }
                }

            }

            result.close();
            stmt.close();
            con.close();

        } catch (Exception error) {

                System.err.println("[ERROR]" + error.getMessage() );

                error.printStackTrace();
                System.exit(1);

        }

    }

    public static void viewStatistics () {

        int chooser = viewStatisticsMenu();
        boolean dateOk = false;


        String name = " ";
        String newName = " ";
        String time1 = " ";
        String time2 = " ";

        String startingDate = " ";
        String endingDate = " ";

        int nameId = 0;

        do {

            if (chooser == 1) { // statistics by name

                System.out.println("Give name:");

                do {

                    name = getString("Give name:");

                    newName = name.trim();
                    newName = newName.toLowerCase();

                    nameId = checkName(newName);

                    if (nameId == 0) {

                        System.out.println("There wasn't user with that name!");

                    }

                }
                while (nameId == 0);

                reportsByName(name, nameId);

                chooser = viewStatisticsMenu();

            }

            else if (chooser == 2) { // statistics by time

                do {

                    System.out.println("Give starting date for period in format YYYY-MM-DD:");
                    startingDate = getString("Give starting date for period in format YYYY-MM-DD:");
                    dateOk = checkTime(startingDate);

                }
                while(!dateOk);

                do {

                    System.out.println("Give ending date for period in format YYYY-MM-DD:");
                    endingDate = getString("Give ending date for period in format YYYY-MM-DD:");
                    dateOk = checkTime(endingDate);

                }
                while(!dateOk);

                System.out.println("Sum of reports posted in given time period: " + reportsByTime(startingDate, endingDate));

                chooser = viewStatisticsMenu();

            }

            else if (chooser == 3) { // statistics by status

                reportsByStatus();

                chooser = viewStatisticsMenu();

            }

            else if (chooser == 0) { } // exit to previous menu

            else {

                System.out.println("Invalid input!");

            }

        }
        while (chooser != 0);

        System.out.println("\nExiting to previous menu...\n");

    }

    public static int viewStatisticsMenu () {

        int chooser;

        System.out.println("\nWhat do you want to do?");
        System.out.println("1: View statistics by name.");
        System.out.println("2: View statistics by time.");
        System.out.println("3: View statistics by status.");
        System.out.println("0: Exit to previous menu.");
        System.out.println("Please choose number between 0 - 3.");
        chooser = getInt("Please choose number between 0 - 3.");

        return chooser;

    }

    public static void reportsByName (String requestName, int nameId) {

        int senderOpen = 0;
        int senderInvestigating = 0;
        int senderFixed = 0;
        int senderTotal = 0;

        int handlerOpen = 0;
        int handlerInvestigating = 0;
        int handlerFixed = 0;
        int handlerTotal = 0;

        try {

            Class.forName(driverName);

            Connection con = DriverManager.getConnection(url);

            String sql = "SELECT    * "
                       + "FROM      report "
                       + "WHERE     senderId = " + nameId + " "
                       + "          OR "
                       + "          handlerId = " + nameId + " ";

            Statement stmt = con.createStatement();

            ResultSet result = stmt.executeQuery(sql);

            while(result.next()) {

                if (result.getInt("senderId") == nameId) {

                    if (result.getString("status").equals("open")) {

                        senderOpen++;

                    }

                    else if (result.getString("status").equals("investigating")) {

                        senderInvestigating++;

                    }

                    else if (result.getString("status").equals("fixed")) {

                        senderFixed++;

                    }

                }

                if (result.getInt("handlerId") == nameId) {

                    if (result.getString("status").equals("open")) {

                        handlerOpen++;

                    }

                    else if (result.getString("status").equals("investigating")) {

                        handlerInvestigating++;

                    }

                    else if (result.getString("status").equals("fixed")) {

                        handlerFixed++;

                    }


                }


            }

            result.close();
            stmt.close();
            con.close();

        } catch (Exception error) {

                System.err.println("[ERROR]" + error.getMessage() );

                error.printStackTrace();
                System.exit(1);

        }

        senderTotal = senderOpen + senderInvestigating + senderFixed;
        handlerTotal = handlerOpen + handlerInvestigating + handlerFixed;

        if (senderTotal == 0 && handlerTotal == 0) {

            System.out.println("\nThere was no reports by or handled by user " + requestName + ".\n");

        }

        if (senderTotal > 0) {

            System.out.println("\nReports sent by user " + requestName + ":");
            System.out.println("Open reports: " + senderOpen);
            System.out.println("Investigated reports: " + senderInvestigating);
            System.out.println("Fixed reports: " + senderFixed);
            System.out.println("Total: " + senderTotal + "\n");

        }

        if (handlerTotal > 0) {

            System.out.println("\nReports handled by user " + requestName + ":");
            System.out.println("Open reports: " + handlerOpen);
            System.out.println("Investigated reports: " + handlerInvestigating);
            System.out.println("Fixed reports: " + handlerFixed);
            System.out.println("Total: " + handlerTotal + "\n");

        }

    }

    public static int reportsByTime (String start, String end) {

        int reportCounter = 0;

        try {

            Class.forName(driverName);

            Connection con = DriverManager.getConnection(url);

            String sql = "SELECT    * "
                       + "FROM      report "
                       + "WHERE     startTime BETWEEN '" + start + " 00:00:00' AND '" + end + " 23:59:59'";

            Statement stmt = con.createStatement();

            ResultSet result = stmt.executeQuery(sql);

            while(result.next()) {

                reportCounter++;

            }

            result.close();
            stmt.close();
            con.close();

        } catch (Exception error) {

                System.err.println("[ERROR]" + error.getMessage() );

                error.printStackTrace();
                System.exit(1);

        }

        return reportCounter;

    }

    public static void reportsByStatus() {

        int open = 0;
        int investigating = 0;
        int fixed = 0;
        int total = 0;

        try {

            Class.forName(driverName);

            Connection con = DriverManager.getConnection(url);

            String sql = "SELECT    status "
                       + "FROM      report ";

            Statement stmt = con.createStatement();

            ResultSet result = stmt.executeQuery(sql);

            while(result.next()) {


                if (result.getString("status").equals("open")) {

                    open++;

                }

                else if (result.getString("status").equals("investigating")) {

                    investigating++;

                }

                else if (result.getString("status").equals("fixed")) {

                    fixed++;

                }

            }

            result.close();
            stmt.close();
            con.close();

        } catch (Exception error) {

                System.err.println("[ERROR]" + error.getMessage() );

                error.printStackTrace();
                System.exit(1);

        }

        total = open + investigating + fixed;

        System.out.println("\nOpen reports: " + open);
        System.out.println("Reports under investigation: " + investigating);
        System.out.println("Fixed reports: " + fixed);
        System.out.println("Reports total: " + total + "\n");

    }

    public static boolean checkTime(String time) {

        boolean ok = false;

        if (time.length() == 10 && time.charAt(4) == '-' && time.charAt(7) == '-') {

        ok = true;

        }

        return ok;

    }

    public static int checkName(String name) {

        int idName = 0;

        try {

            Class.forName(driverName);

            Connection con = DriverManager.getConnection(url);

            String sql = "SELECT * "
                       + "FROM person "
                       + "WHERE LOWER(name) = '" + name + "' "
                       + "ORDER BY id ASC ";

            Statement stmt = con.createStatement();

            ResultSet result = stmt.executeQuery(sql);

            while(result.next()) {

            String nameDatabase = result.getString("name");

            nameDatabase = nameDatabase.trim();
            nameDatabase = nameDatabase.toLowerCase();

                if (nameDatabase.equals(name)) {

                    idName = result.getInt("id");
                }

            }

            result.close();
            stmt.close();
            con.close();

        } catch (Exception error) {

            System.err.println("[ERROR]" + error.getMessage() );

            error.printStackTrace();
            System.exit(1);

        }

        return idName;

    }

    public static boolean reportIdChecker(int reportId) {

        boolean reportIdOk = false;

        try {

            Class.forName(driverName);

            Connection con = DriverManager.getConnection(url);

            String sql = "SELECT    id "
                       + "FROM      report "
                       + "WHERE     LOWER(status) = 'open' "
                       + "          OR "
                       + "          LOWER(status) = 'investigating' "
                       + "ORDER BY  id DESC ";

            Statement stmt = con.createStatement();

            ResultSet result = stmt.executeQuery(sql);

            while(result.next()) {

                int idDatabase = result.getInt("id");

                if (idDatabase == reportId) {

                    reportIdOk = true;

                }

            }

            result.close();
            stmt.close();
            con.close();

        } catch (Exception error) {

            System.err.println("[ERROR]" + error.getMessage() );

            error.printStackTrace();
            System.exit(1);

        }

        return reportIdOk;

    }

    public static void listReports(String status) {

        System.out.println("List of faultreports:");

        try {

            Class.forName(driverName);

            Connection con = DriverManager.getConnection(url);

            String sql = "SELECT    * "
                       + "FROM      report "
                       + "WHERE     LOWER(status) = '" + status + "' "
                       + "ORDER BY  id ASC ";

            Statement stmt = con.createStatement();

            ResultSet result = stmt.executeQuery(sql);



            while(result.next()) {

                System.out.println(result.getInt("id") + " " + getName(result.getInt("senderId")) + " "
                                   + result.getString("priority") + " " + result.getString("class") + " "
                                   + result.getString("startTime"));

            }

            result.close();
            stmt.close();
            con.close();

        } catch (Exception error) {

            System.err.println("[ERROR]" + error.getMessage() );

            error.printStackTrace();
            System.exit(1);

        }

        System.out.println();

    }

    public static int getId() {

        int yourId = 1;

        try {

            Class.forName(driverName);

            Connection con = DriverManager.getConnection(url);

            String sql = "SELECT id "
                       + "FROM report "
                       + "ORDER BY id DESC ";

            Statement stmt = con.createStatement();

            ResultSet result = stmt.executeQuery(sql);

            while(result.next()) {

                int idDatabase = result.getInt("id");

                if (idDatabase >= yourId) {

                    yourId = idDatabase + 1;

                }

            }

            result.close();
            stmt.close();
            con.close();

        } catch (Exception error) {

            System.err.println("[ERROR]" + error.getMessage() );

            error.printStackTrace();
            System.exit(1);

        }

        return yourId;

    }

    public static String getName(int id) {

        String name = " ";

        try {

            Class.forName(driverName);

            Connection con = DriverManager.getConnection(url);

            String sql = "SELECT * "
                       + "FROM person "
                       + "ORDER BY id ASC ";

            Statement stmt = con.createStatement();

            ResultSet result = stmt.executeQuery(sql);

            while(result.next()) {

                int idDatabase = result.getInt("id");

                if (idDatabase == id) {

                    name = result.getString("name");

                }

            }

            result.close();
            stmt.close();
            con.close();

        } catch (Exception error) {

            System.err.println("[ERROR]" + error.getMessage() );

            error.printStackTrace();
            System.exit(1);

        }

        return name;

    }

    public static boolean operatorCheck(int id) {

        boolean adminOk = false;

        try {

            Class.forName(driverName);

            Connection con = DriverManager.getConnection(url);

            String sql = "SELECT * "
                       + "FROM person "
                       + "WHERE LOWER(status) LIKE 'admin%' "
                       + "      OR "
                       + "      LOWER(status) = 'operator' "
                       + "ORDER BY id ASC ";

            Statement stmt = con.createStatement();

            ResultSet result = stmt.executeQuery(sql);

            while(result.next()) {

                int idDatabase = result.getInt("id");

                if (idDatabase == id) {

                    adminOk = true;

                }

            }

            result.close();
            stmt.close();
            con.close();

        } catch (Exception error) {

            System.err.println("[ERROR]" + error.getMessage() );

            error.printStackTrace();
            System.exit(1);

        }

        return adminOk;

    }

    public static boolean checkId(int id) {

        boolean idOk = false;

        try {

            Class.forName(driverName);

            Connection con = DriverManager.getConnection(url);

            String sql = "SELECT * "
                       + "FROM person "
                       + "ORDER BY id ASC ";

            Statement stmt = con.createStatement();

            ResultSet result = stmt.executeQuery(sql);

            while(result.next()) {

            int idDatabase = result.getInt("id");

                if (idDatabase == id) {

                    idOk = true;
                }

            }

            result.close();
            stmt.close();
            con.close();

        } catch (Exception error) {

            System.err.println("[ERROR]" + error.getMessage() );

            error.printStackTrace();
            System.exit(1);

        }

        return idOk;

    }

    public static String getTimestamp() {

    String timestampFormat = "yyyy-MM-dd HH:mm";

    SimpleDateFormat dateFormat = new SimpleDateFormat(timestampFormat);

    Date date = new Date();

    String timestamp = dateFormat.format(date);

    return timestamp;

    }

    public static int getInt(String question) {

        int number = 0;
        boolean ok = false;

        do {

            try {

                number = cin.nextInt();
                ok = true;

            } catch (Exception error) {

                cin.nextLine();
                System.out.println("False input!");
                System.out.println(question);
                ok = false;

            }

        } while (!ok);

        return number;
    }

    public static String getString(String question) {

        Scanner cin = new Scanner(System.in);
        String word = "";
        boolean ok = false;

        do {

            try {

                word = cin.nextLine();
                ok = true;

            } catch (Exception error) {

                cin.nextLine();
                System.out.println("False input!");
                System.out.println(question);
                ok = false;

            }

        } while (!ok);

        return word;
    }

}