package sqldata;

import javax.servlet.ServletException;
import java.util.List;
import java.util.ArrayList;
import java.sql.*;

/**
 * @author Sean Robinson
 * @version v. 1
 */
public class LaptopData {
    public List getInventory(Connection conn) {
        List inventoryList = new ArrayList();
        try {
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery("select * FROM inventory order by laptopid asc");
            while (rs.next()) {
                inventoryList.add(new Inventory(rs.getString("laptopname"),
                                                rs.getString("laptopid"),
                                                rs.getString("brand"),
                                                rs.getString("model"),
                                                rs.getString("serial"),
                                                rs.getString("returned")));
            }
        } catch (Exception e) {
            System.out.println("Inventory gathering error occurred");
            e.printStackTrace();
        }
        return inventoryList;
    }

    public List getLaptopsOut(Connection conn) {
        List laptopsOut = new ArrayList();
        try {
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery("Select * from out");
            while (rs.next()) {
                laptopsOut.add(new Laptop(rs.getString("id"),
                                          rs.getString("teachername"),
                                          rs.getString("school"),
                                          rs.getString("laptopid"),
                                          rs.getString("laptopname"),
                                          rs.getString("issuedate")));
            }
        } catch (Exception e) {
            System.out.println("Error getting list of laptops that are in use");
            e.printStackTrace(System.out);
        }
        return laptopsOut;
    }

    public void insertNewInventory(Connection conn,
                                   String name,
                                   String brand,
                                   String model,
                                   String serial,
                                   String returned) throws ServletException {
        try {
            PreparedStatement ps =
                    conn.prepareStatement("INSERT INTO inventory(laptopname, brand, model, serial, returned) " +
                                          "VALUES(?,?,?,?,?)");

            ps.setString(1, name);
            ps.setString(2, brand);
            ps.setString(3, model);
            ps.setString(4, serial);
            ps.setString(5, returned);

            ps.executeUpdate();
            ps.close();
        } catch (Exception e) {
            throw new ServletException("Error in inserting new Laptop into Inventory", e);
        }
    }


    public List getReturned(Connection conn) {
        List returnedLaptops = new ArrayList();
        try {
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery("select * from inventory WHERE returned = 'yes' order by laptopid asc");
            while (rs.next()) {
                returnedLaptops.add(new Inventory(rs.getString("laptopname"),
                                                  rs.getString("laptopid"),
                                                  rs.getString("brand"),
                                                  rs.getString("model"),
                                                  rs.getString("serial"),
                                                  rs.getString("returned")));
            }
        } catch (Exception e) {
            System.out.println("Error building returned list");
            e.printStackTrace(System.out);
        }
        return returnedLaptops;
    }


    public List getTeacherswithLaptops(Connection conn) throws ServletException {
        List teacherlist = new ArrayList();
        try {
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery("Select teachername FROM out ORDER BY laptopid asc");
            while (rs.next()) {
                teacherlist.add(rs.getString("teachername"));
            }
        } catch (Exception e) {
            throw new ServletException("Error Gathering teachers for removal page", e);
        }
        return teacherlist;
    }


    public List getLaptopsinInventory(Connection conn) throws ServletException {
        List laptopsinventory = new ArrayList();
        try {
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery("Select laptopname from Inventory Order by laptopid asc");
            while (rs.next()) {
                laptopsinventory.add(rs.getString("laptopname"));
            }
        } catch (Exception e) {
            throw new ServletException("An Error Occurred gathering the Laptop names from Inventory for removal page", e);
        }
        return laptopsinventory;
    }


    public void RemoveLaptopfromInventory(Connection conn, String laptop) throws ServletException {
        try {
            Statement st = conn.createStatement();
            st.executeUpdate("DELETE FROM inventory WHERE laptopname ='" + laptop + "'");
            st.close();
        } catch (Exception e) {
            throw new ServletException("Error Occurred in deleting laptop from inventory", e);
        }
    }

    public void removeLaptopFromTeacher(Connection conn, String teacher) throws ServletException {
        try {
            Statement st = conn.createStatement();
            st.executeUpdate("DELETE FROM out WHERE teachername = '" + teacher + "'");
            st.close();
        } catch (Exception e) {
            throw new ServletException("An error occurred removing  " +
                                       "laptop usage record from the out table(removing laptop)", e);
        }
    }


    public void reAssignLaptop(Connection conn, String id) throws ServletException {
        try {
            PreparedStatement ps1 = conn.prepareStatement("UPDATE inventory SET returned = 'no' WHERE laptopid=?");
            String number = id;
            int idlaptop = Integer.decode(number);
            ps1.setInt(1, idlaptop);
            ps1.executeUpdate();
            ps1.close();

            PreparedStatement ps2 = conn.prepareStatement("DELETE FROM out WHERE laptopid='" + number + "'");
            ps2.executeUpdate();
            ps2.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void finishReAssign(Connection conn,
                               String id,
                               String laptopname,
                               String school,
                               String teachername,
                               String issuedate) throws ServletException {
        try {
            PreparedStatement ps = conn.prepareStatement(
                    "INSERT INTO out(laptopid,laptopname,school,teachername,issuedate)" +
                    "VALUES(?,?,?,?,?)");
            ps.setString(1, id);
            ps.setString(2, laptopname);
            ps.setString(3, school);
            ps.setString(4, teachername);
            ps.setString(5, issuedate);
            ps.executeUpdate();
            ps.close();
        } catch (Exception e) {
            throw new ServletException(e);
        }
    }


    public void laptopReturned(Connection conn, String id) throws ServletException {
        int laptopid = Integer.decode(id);
        try {
            PreparedStatement st = conn.prepareStatement("UPDATE inventory SET returned = 'yes' WHERE laptopid=" + laptopid);
            st.executeUpdate();
            st.close();
        } catch (Exception e) {
            throw new ServletException("Error in updating laptop status to returned", e);
        }
    }

    public static void main(String args[]) {
        LaptopData ld = new LaptopData();
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            Connection condb = DriverManager.getConnection("jdbc:odbc:laptops", "", "");
            ld.finishReAssign(condb, "12", "boo", "highschoo", "john", "90909");
        } catch (Exception e) {
            e.printStackTrace(System.out);
        }
    }
}