public class ProductFrame extends javax.swing.JFrame {

    /**
     * Creates new form ProductFrame
     */
    public ProductFrame() {
        initComponents();
        Show_Products_In_JTable();


    }

    public Connection getConnection()
    {

        Connection con = null;
        try {
            con = DriverManager.getConnection("jdbc:mysql://localhost/smart-mart","root","");
            return con;
        } catch (SQLException ex) {
            Logger.getLogger(ProductFrame.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }

    }




        public boolean checkInputs()
    {
        if(
              txt_name.getText() == null
           || txt_price.getText() == null
           || txt_quantity.getText() == null
            || txt_arrdate.getText() == null
            || txt_minreq.getText() == null
            || txt_maxreq.getText() == null
                || txt_staff.getText() == null

          ){
            return false;
        }
        else{
            try{
                Float.parseFloat(txt_price.getText());
                return true;
            }catch(Exception ex)
            {
                return false;
            }
        }
    }

   //** Display Date in the ItemTable ** //
        //** Step 1- Create an arraylist and fill it with date **//

        public ArrayList<Product> getProductList ()
        {
            ArrayList<Product> productList  = new ArrayList<Product>();
            Connection con = getConnection();
            String query = "SELECT * FROM product";

            Statement st;
            ResultSet rs;

        try {
            st = con.createStatement();
             rs = st.executeQuery(query);
              Product product;


   Show_Products_In_JTable();



              while (rs.next())
              {
                  product  = new Product (rs.getInt("id"),rs.getString("name"), Float.parseFloat(rs.getString("price")),rs.getInt("quantity"), rs.getString("arrdate"), rs.getInt("minreq"),rs.getInt("maxreq"), rs.getString("staff"));
                  productList.add(product);
                  con.close();
              }
        } catch (SQLException ex) {
            Logger.getLogger(ProductFrame.class.getName()).log(Level.SEVERE, null, ex);

        }
        return  productList;
        }






        //** Stept 2- Populate the table ** //



    public void Show_Products_In_JTable()
    {
        ArrayList<Product> list = getProductList();
        DefaultTableModel model = (DefaultTableModel) J_Table_Products.getModel();






        Object[] row = new Object [8];
        for(int i = 0; i<list.size();i++)
        {
        row[0]=list.get(i).getId();
        row[1]=list.get(i).getName();
        row[2]=list.get(i).getPrice();
        row[3]=list.get(i).getQuantity();
        row[4]=list.get(i).getArrDate();
        row[5]=list.get(i).getMinReq();
        row[6]=list.get(i).getMaxReq();
        row[7]=list.get(i).getStaff();

        model.addRow(row);


    }
    }

  // Show Data In Inputs
    public void ShowItem(int index)
    {
            txt_id.setText(Integer.toString(getProductList().get(index).getId()));
            txt_name.setText(getProductList().get(index).getName());
            txt_price.setText(Float.toString(getProductList().get(index).getPrice()));
            txt_quantity.setText(Integer.toString(getProductList().get(index).getQuantity()));
            txt_arrdate.setText(getProductList().get(index).getArrDate());
            txt_minreq.setText(Integer.toString(getProductList().get(index).getMinReq()));
            txt_maxreq.setText(Integer.toString(getProductList().get(index).getMaxReq()));
            txt_staff.setText(getProductList().get(index).getStaff());
    }

    private void btn_insertActionPerformed(java.awt.event.ActionEvent evt) {
      if (checkInputs() && txt_name !=null){

          try {
           Connection con = getConnection();
          PreparedStatement ps;
              ps = con.prepareStatement("INSERT INTO product(name, price, quantity, arrdate, minreq, maxreq, staff)"+"values(?,?,?,?,?,?,?)");
               ps.setString(1, txt_name.getText());
                ps.setString(2, txt_price.getText());
                ps.setString(3, txt_quantity.getText());
                ps.setString(4, txt_arrdate.getText());
                ps.setString(5 , txt_minreq.getText());
                ps.setString(6 , txt_maxreq.getText());
                ps.setString(7, txt_staff.getText());




                ps.executeUpdate();

                Show_Products_In_JTable();
                JOptionPane.showMessageDialog(null, "Data Inserted");

                ps.close();
          } catch (SQLException ex) {
              JOptionPane.showMessageDialog(null, ex.getMessage());
          }

      }
      else{
          JOptionPane.showMessageDialog(null, "One or more field empty");
      }


          System.out.println("Name=>" + txt_name.getText());
          System.out.println("Price=>" + txt_price.getText());
          System.out.println("Quantity=>" + txt_quantity.getText());
          System.out.println("Arrdate=>" + txt_arrdate.getText());
          System.out.println("MinReq=>" + txt_minreq.getText());
          System.out.println("MaxReq=>" + txt_maxreq.getText());
           System.out.println("Staff=>" + txt_staff.getText());



    }

    private void btn_updateActionPerformed(java.awt.event.ActionEvent evt) {
if (checkInputs() && txt_id.getText () !=null)
{
       String UpdateQuery = null;
    PreparedStatement ps = null;
    Connection con = getConnection ();

    try {


    UpdateQuery = "UPDATE product SET name=?, price=?,quantity=?, arrdate=?, minreq=?,maxreq=?, staff=? WHERE id=?";
    ps = con.prepareStatement(UpdateQuery);
                   ps.setString(1, txt_name.getText());
                ps.setString(2, txt_price.getText());
                ps.setString(3, txt_quantity.getText());
                ps.setString(4, txt_arrdate.getText());
                ps.setString(5 , txt_minreq.getText());
                ps.setString(6 , txt_maxreq.getText());
                ps.setString(7, txt_staff.getText());
                ps.setInt(8, Integer.parseInt(txt_id.getText()));


        ps.executeUpdate();
        ps.close();
    } catch (SQLException ex) {
        Logger.getLogger(ProductFrame.class.getName()).log(Level.SEVERE, null, ex);
    }
}else{
    JOptionPane.showMessageDialog(null, "One or more fields are Empty");
}
    }

    private void btn_deleteActionPerformed(java.awt.event.ActionEvent evt) {

        if (!txt_id.getText().equals(""))

        {

            try {
                Connection con = getConnection();
                PreparedStatement ps = con.prepareStatement("DELETE FROM product WHERE id = ?");
                int id = Integer.parseInt(txt_id.getText());
                ps.setInt(1, id);
                ps.executeUpdate();


                JOptionPane.showMessageDialog(null, "Item Deleted!");
                ps.close();
            } catch (SQLException ex) {
                Logger.getLogger(ProductFrame.class.getName()).log(Level.SEVERE, null, ex);
                JOptionPane.showMessageDialog(null, "Item Not Deleted!");
                 ex.printStackTrace();

                    } finally {

}

        }
        else{
            JOptionPane.showMessageDialog(null, " Product not found. Please enter the product ID!");
        }

    }

    private void J_Table_ProductsMouseClicked(java.awt.event.MouseEvent evt) {
        int index = J_Table_Products.getSelectedRow();
        ShowItem(index);
    }




    /**
     * @param args the command line arguments
     */
    public static void main(String args[]) {
        /* Set the Nimbus look and feel */
        //<editor-fold defaultstate="collapsed" desc=" Look and feel setting code (optional) ">
        /* If Nimbus (introduced in Java SE 6) is not available, stay with the default look and feel.
         * For details see http://download.oracle.com/javase/tutorial/uiswing/lookandfeel/plaf.html
         */
        try {
            for (javax.swing.UIManager.LookAndFeelInfo info : javax.swing.UIManager.getInstalledLookAndFeels()) {
                if ("Nimbus".equals(info.getName())) {
                    javax.swing.UIManager.setLookAndFeel(info.getClassName());
                    break;
                }
            }
        } catch (ClassNotFoundException ex) {
            java.util.logging.Logger.getLogger(ProductFrame.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            java.util.logging.Logger.getLogger(ProductFrame.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (IllegalAccessException ex) {
            java.util.logging.Logger.getLogger(ProductFrame.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (javax.swing.UnsupportedLookAndFeelException ex) {
            java.util.logging.Logger.getLogger(ProductFrame.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        }
        //</editor-fold>

        /* Create and display the form */
        java.awt.EventQueue.invokeLater(new Runnable() {
            public void run() {
                new ProductFrame().setVisible(true);
            }
        });
    }