package database;
import java.awt.GridLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.*;

import javax.swing.JButton;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;

import objects.ESQLCustomer;

public class EmbeddedSQLConnection
{
        private Connection con;

        public EmbeddedSQLConnection()
        {

        }

        public void getConnection()
        {
                try
                {
                        if(con==null)
                        {
                                Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
                                con=DriverManager.getConnection("jdbc:db2://localhost:50001/SAMPLE","db2inst1","password");
                        }
                System.out.println(con);
                }
                catch (SQLException e)
                {
                        System.out.println(e.getMessage());
                        System.out.println(e.getErrorCode());
                        e.printStackTrace();
                }
                catch (Exception e)
                {
                        e.printStackTrace();
                }
        }

        public void closeConnection()
        {
                try
                {
                        con.close();
                        con = null;
                }
                catch (SQLException e)
                {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }
        }

        public boolean getCustomerInformation(ESQLCustomer u, String name)
        {
                PreparedStatement s = null;

                try
                {
                        String query = "SELECT customerID, name, address, city, postalCode, telephoneNumber, email FROM ESQLCustomer WHERE name = ?";
                        s = con.prepareStatement(query);
                        s.setString(1, name);
                        ResultSet rs=s.executeQuery();

                        while(rs.next())
                        {

                                u.changeUserID(rs.getString(1));

                                u.changeName(rs.getString(2));
                                u.changeAddress(rs.getString(3));
                                u.changeCity(rs.getString(4));
                                u.changePostalCode(rs.getString(5));
                                u.changeTelephoneNumber(rs.getString(6));
                                u.changeEmail(rs.getString(7));

                        }
                }
                catch (SQLException e1)
                {
                        // TODO Auto-generated catch block
                        e1.printStackTrace();
                }
                return true;
        }

        public boolean addCustomer(String customerID,  String name, String address, String city, String postalCode, String telephoneNumber, String email)
        {
                PreparedStatement s = null;

                try
                {
                        String query = "INSERT INTO ESQLCustomer ( customerID, name, address, city, postalcode, telephoneNumber, email )VALUES (?,?,?,?,?,?,?)";

                        System.out.println(name);

                        s = con.prepareStatement(query);


                        s.setString(1, customerID);

                        s.setString(2, name);
                        s.setString(3, address);
                        s.setString(4, city);
                        s.setString(5, postalCode);
                        s.setString(6, telephoneNumber);
                        s.setString(7, email);


                        s.execute();
                }
                catch (SQLException e1)
                {
                        // TODO Auto-generated catch block
                        e1.printStackTrace();
                }
                return true;
        }

        public boolean removeCustomer(String customerID)
        {
                PreparedStatement s = null;

                try
                {
                        String query = "DELETE FROM ESQLCustomer WHERE customerID = ?";

                        s = con.prepareStatement(query);

                        s.setString(1, customerID);

                        s.execute();
                }
                catch (SQLException e1)
                {
                        // TODO Auto-generated catch block
                        e1.printStackTrace();
                }
                return true;
        }

        public boolean updateCustomer( String customerID,  String name, String address, String city, String postalCode, String telephoneNumber, String email)
        {
                PreparedStatement s = null;

                try
                {
                        String query = "Update ESQLCustomer SET (customerID,  name, address, city, postalCode, telephoneNumber, email) = (?,?,?,?,?,?,?) WHERE customerID= ?";

                        s = con.prepareStatement(query);

                        s.setString(1, customerID);

                        s.setString(2, name);
                        s.setString(3, address);
                        s.setString(4, city);
                        s.setString(5, postalCode);
                        s.setString(6, telephoneNumber);
                        s.setString(7, email);
                        s.setString(8, customerID);


                        s.execute();
                }
                catch (SQLException e1)
                {
                        // TODO Auto-generated catch block
                        e1.printStackTrace();
                }
                return true;
        }
}