package casemanagementsystem;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;

public class DatabaseTalker {

    public static String createCase(Case newCase) throws SQLException, ClassNotFoundException {
        Connection con = null;
        String back = "Failure";
        try {
            con = connect();

            PreparedStatement stmt = con.prepareStatement("INSERT INTO cases (caseNo,idNo,supportCategory,comment,status,budgetTime,acctualTime) VALUES (?,?,?,?,?,?,?)");

            stmt.setInt(1, newCase.getCaseNo());
            stmt.setInt(2, newCase.getManageNo());
            stmt.setString(3, newCase.getSupportCategory());
            stmt.setString(4, newCase.getComment());
            stmt.setString(5, newCase.getStatus());
            stmt.setInt(6, newCase.getBudgetTime());
            stmt.setInt(7, newCase.getAcctualTime());

            int i = stmt.executeUpdate();

            if (i > 0) {
                back = "Success";
            }

        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DatabaseTalker.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            closeCon(con);
        }
        return back;
    }

    public static String createTask(Task newTask) throws SQLException, ClassNotFoundException {
        Connection con = null;
        String back = "Failure";
        try {
            con = connect();

            PreparedStatement stmt = con.prepareStatement("INSERT INTO taskInstance (taskInstNo,caseNo,taskNo,idNo,acctualTime,status,comment) VALUES (?,?,?,?,?,?,?)");

            stmt.setInt(1, newTask.getTaskInstNo());
            stmt.setInt(2, newTask.getCaseNo());
            stmt.setInt(3, newTask.getTaskNo());
            stmt.setInt(4, newTask.getTechNo());
            stmt.setInt(5, newTask.getAcctualTime());
            stmt.setString(6, newTask.getStatus());
            stmt.setString(7, newTask.getComment());

            int i = stmt.executeUpdate();

            if (i > 0) {
                back = "Success";
            }

        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DatabaseTalker.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            closeCon(con);
        }
        return back;

    }

    public static String updateCase(Case newCase) throws SQLException, ClassNotFoundException {
        Connection con = null;
        String back = "Failure";
        try {
            con = connect();

            PreparedStatement stmt = con.prepareStatement("UPDATE cases SET caseNo=?,idNo=?,supportCategory=?,comment=?,status=?,budgetTime=?,acctualTime=? WHERE caseNo=?");

            stmt.setInt(1, newCase.getCaseNo());
            stmt.setInt(2, newCase.getManageNo());
            stmt.setString(3, newCase.getSupportCategory());
            stmt.setString(4, newCase.getComment());
            stmt.setString(5, newCase.getStatus());
            stmt.setInt(6, newCase.getBudgetTime());
            stmt.setInt(7, newCase.getAcctualTime());
            stmt.setInt(8, newCase.getCaseNo());

            int i = stmt.executeUpdate();

            if (i > 0) {
                back = "Success";
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DatabaseTalker.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            closeCon(con);
        }
        return back;

    }

    public static String updateTask(Task newTask) throws SQLException, ClassNotFoundException {
        Connection con = null;
        String back = "Failure";
        try {
            con = connect();

            PreparedStatement stmt = con.prepareStatement("UPDATE taskInstance SET taskInstNo=?,caseNo=?,taskNo=?,idNo=?,acctualTime=?,status=?,comment=? WHERE taskInstNo=?");

            stmt.setInt(1, newTask.getTaskInstNo());
            stmt.setInt(2, newTask.getCaseNo());
            stmt.setInt(3, newTask.getTaskNo());
            stmt.setInt(4, newTask.getTechNo());
            stmt.setInt(5, newTask.getAcctualTime());
            stmt.setString(6, newTask.getStatus());
            stmt.setString(7, newTask.getComment());
            stmt.setInt(8, newTask.getTaskInstNo());

            int i = stmt.executeUpdate();

            if (i > 0) {
                back = "Success";
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DatabaseTalker.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            closeCon(con);
        }
        return back;

    }

    public static ArrayList retriveCaseList(int idNo) throws SQLException, ClassNotFoundException {
        Connection con = null;
        ArrayList caseList = new ArrayList();
        try {
            con = connect();
            PreparedStatement stmt = con.prepareStatement("SELECT caseNo FROM cases WHERE idNo=?");
            stmt.setString(1, "" + idNo);

            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                int caseNumber = Integer.parseInt(rs.getString("caseNo"));
                caseList.add(caseNumber);
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DatabaseTalker.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            closeCon(con);
        }
        return caseList;

    }

    public static ArrayList retriveTasksList(int caseNo) throws SQLException, ClassNotFoundException {
        Connection con = null;
        ArrayList tasksList = new ArrayList();
        try {
            con = connect();
            PreparedStatement stmt = con.prepareStatement("SELECT taskInstNo FROM taskInstance WHERE caseNo=?");
            stmt.setString(1, "" + caseNo);

            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                String taskInstNumber = rs.getString("taskInstNo");
                tasksList.add(taskInstNumber);
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DatabaseTalker.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            closeCon(con);
        }
        return tasksList;

    }

    public static Case getCase(int caseNo) throws SQLException, ClassNotFoundException {
        Connection con = null;
        Case storedCase = null;
        try {
            con = connect();
            PreparedStatement stmt = con.prepareStatement("SELECT caseNo, idNo, supportCategory, comment, status, budgetTime, acctualTime FROM cases WHERE caseNo=?");
            stmt.setInt(1, caseNo);

            ResultSet rs = stmt.executeQuery();
            rs.next();
            String supportCategory = rs.getString("supportCategory");
            int caseNum = rs.getInt("caseNo");
            int manageNo = rs.getInt("idNo");
            String status = rs.getString("status");
            String comment = rs.getString("comment");
            storedCase = new Case(supportCategory, caseNum, manageNo, status, comment);
            storedCase.setBudgetTime(Integer.parseInt(rs.getString("budgetTime")));
            storedCase.setAcctualTime(Integer.parseInt(rs.getString("acctualTime")));

        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DatabaseTalker.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            closeCon(con);
        }
        return storedCase;

    }

    public static Task getTaskInst(int taskInstNo) throws SQLException, ClassNotFoundException {
        Connection con = null;
        Task storedTaskInst = null;
        try {
            con = connect();

            PreparedStatement stmt = con.prepareStatement("SELECT caseNo,task.taskNo,idNo,budgetTime,acctualTime,taskName,status,comment FROM taskInstance JOIN task ON taskInstance.taskNo = task.taskNo WHERE taskInstNo=?");
            stmt.setInt(1, taskInstNo);

            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                int caseNo = rs.getInt("caseNo");
                int taskNo = rs.getInt("taskNo");
                int techNo = rs.getInt("idNo");
                int budgetTime = rs.getInt("budgetTime");
                int acctualTime = rs.getInt("acctualTime");
                String task = rs.getString("taskName");
                String status = rs.getString("status");
                String comment = rs.getString("comment");
                storedTaskInst = new Task(task, taskInstNo, taskNo, caseNo, acctualTime, techNo, status, comment);
                storedTaskInst.setBudgetTime(budgetTime);
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DatabaseTalker.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            closeCon(con);
        }
        return storedTaskInst;
    }

    public static ArrayList compileCaseNoList(String caseNo) throws SQLException, ClassNotFoundException {
        Connection con = null;
        ArrayList caseNoList = new ArrayList();
        try {
            con = connect();
            PreparedStatement stmt = con.prepareStatement("SELECT caseNo FROM cases");

            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                String caseNumber = rs.getString("caseNo");
                caseNoList.add(caseNumber);
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DatabaseTalker.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            closeCon(con);
        }
        return caseNoList;

    }

    public static ArrayList compileTaskInstNoList(String taskInstNo) throws SQLException, ClassNotFoundException {
        Connection con = null;
        ArrayList taskInstNoList = new ArrayList();
        try {
            con = connect();
            PreparedStatement stmt = con.prepareStatement("SELECT taskInstNo FROM taskInstance");

            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                String caseNumber = rs.getString("taskInstNo");
                taskInstNoList.add(caseNumber);
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DatabaseTalker.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            closeCon(con);
        }
        return taskInstNoList;

    }

    public static ArrayList<String> compileManagerNameList() throws SQLException, ClassNotFoundException {
        Connection con = null;
        ArrayList<String> nameList = new ArrayList();
        String fullName = "Fail!";
        try {
            con = connect();

            PreparedStatement stmt = con.prepareStatement("SELECT fName, lName FROM users WHERE possition=?");
            stmt.setString(1, "MANAGER");
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                fullName = rs.getString("fName") + " " + rs.getString("lName");
                nameList.add(fullName);
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DatabaseTalker.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            closeCon(con);
        }
        return nameList;
    }

    public static String retriveTaskBudgetTime(String task) throws SQLException, ClassNotFoundException {
        Connection con = null;
        String budgetTime = "Fail!";
        try {
            con = connect();
            PreparedStatement stmt = con.prepareStatement("SELECT budgetTime FROM task WHERE taskName=?");
            stmt.setString(1, task);
            ResultSet rs = stmt.executeQuery();
            rs.next();
            budgetTime = rs.getString("budgetTime");

        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DatabaseTalker.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            closeCon(con);
        }
        return budgetTime;
    }

    public static String retriveTaskNo(String task) throws SQLException, ClassNotFoundException {
        Connection con = null;
        String budgetTime = "Fail!";
        try {
            con = connect();

            PreparedStatement stmt = con.prepareStatement("SELECT taskNo FROM task WHERE taskName=?");
            stmt.setString(1, task);
            ResultSet rs = stmt.executeQuery();
            rs.next();
            budgetTime = rs.getString("taskNo");

        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DatabaseTalker.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            closeCon(con);
        }
        return budgetTime;

    }

    public static String retriveIdNo(String fName) throws SQLException, ClassNotFoundException {
        Connection con = null;
        String idNum = "Fail!";
        try {
            con = connect();

            PreparedStatement stmt = con.prepareStatement("SELECT idNo FROM users WHERE fName=?");
            stmt.setString(1, fName);
            ResultSet rs = stmt.executeQuery();
            rs.next();
            idNum = rs.getString("idNo");

        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DatabaseTalker.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            closeCon(con);
        }
        return idNum;
    }

    public static int compileCaseBudgetTime(int caseNo) throws SQLException, ClassNotFoundException {
        Connection con = null;
        int budgetTime = 0;
        try {
            con = connect();

            PreparedStatement stmt = con.prepareStatement("SELECT budgetTime FROM task JOIN taskInstance ON task.taskNo = taskInstance.taskNo WHERE caseNo=?");
            stmt.setString(1, "" + caseNo);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                budgetTime += rs.getInt("budgetTime");
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DatabaseTalker.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            closeCon(con);
        }
        return budgetTime;
    }

    public static int compileCaseAcctualTime(int caseNo) throws SQLException {
        Connection con = null;
        int acctualTime = 0;
        try {
            con = connect();

            PreparedStatement stmt = con.prepareStatement("SELECT acctualTime FROM task JOIN taskInstance ON task.taskNo = taskInstance.taskNo WHERE caseNo=?");
            stmt.setString(1, "" + caseNo);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                acctualTime += rs.getInt("acctualTime");
            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DatabaseTalker.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            closeCon(con);
        }
        return acctualTime;
    }

    private static Connection connect() throws SQLException, ClassNotFoundException {
        Connection con = null;
        Class.forName("org.apache.derby.jdbc.ClientDriver");
        con = DriverManager.getConnection("jdbc:derby://localhost:1527/CMS;create=true;user=FiktivAB;password=12345");
        if (con == null) {
            throw new SQLException("Connection failed! ");
        }
        return con;
    }

    private static void closeCon(Connection con) throws SQLException {
        if (con != null) {
            con.close();
        }
    }

}