package customermanager;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;


public class CustomerDB {


    public static List<Customer> getAll(){
        String sql = "SELECT * FROM Customer ORDER BY LastName";
        List<Customer> customer = newArrayList<>();
        Connection connection = DBUtil.getConnection();
        try (PreparedStatement ps = connection.prepareStatement(sql);
                ResultSet rs = ps.executeQuery()) {
            while(rs.next()) {
                String firstName = rs.getString("FirstName");
                String lastName = rs.getString("LastName");
                String email = rs.getString("EmailAddress");

                Customer c = new Customer();
                c.setFirstName(firstName);
                c.setLastName(lastName);
                c.setEmail(email);
            }
            return customer;
        } catch(SQLException e) {
        } return customer;
    }
    public static Customer get(String EmailAddress) {
        String sql = "SELECT * FROM Customer WHERE EmailAddress = ?";
        Connection connection = DBUtil.getConnection();
        try(PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setString(1, EmailAddress);
            ResultSet rs = ps.executeQuery();
            if(rs.next()) {
                String email = rs.getString("EmailAddress");
                String firstName = rs.getString("FirstName");
                String lastName = rs.getString("LastName");
                rs.close();

                Customer c = new Customer();
                c.setEmail(email);
                c.setFirstName(firstName);
                c.setLastName(lastName);

                return c;
            } else {
                rs.close();
                return null;
            }
        } catch(SQLException e){};
        return null;

    }
    public static void add(Customer customer) {
        String sql = "INSERT INTO Customer (FirstName, LastName, EmailAddress) " +
                "VALUES (?, ?, ?)";
        Connection connection = DBUtil.getConnection();
        try(PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setString(1, customer.getFirstName());
            ps.setString(2,customer.getLastName());
            ps.setString(3, customer.getEmail());
            ps.executeUpdate();
        } catch(SQLException e){}
    }

    public static void delete(Customer customer) {
        String sql = "DELETE FROM Customer " +
                "WHERE EmailAddress = ?";
        Connection connection = DBUtil.getConnection();
        try(PreparedStatement ps = connection.prepareStatement(sql)) {
            customer.setEmail(customer.getEmail());
            ps.executeUpdate();
        } catch(SQLException e){};
    }
    public class DBUtil() {
            private DBUtil(){}
    private static Connection connection;
        public static synchronized Connection getConnection() {
            if (connection != null) {
               return connection;
            } else {
                try {
                    String url = "jdbc:mysql://localhost:3306/mma";
                    String username = "mma_user";
                    String password = "sesame";

                    connection = DriverManager.getConnection(url, username, password);
                    return connection;
                } catch(SQLException e){
                }
            } return null;
        }
        public static synchronized void closeConnection() {
            if(connection != null) {
                try {
                    connection.close();
                } catch(SQLException e) {
                } finally {
                    connection = null;
                }
            }
        }
    }
}