package pl.polsl.zti.db1.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import pl.polsl.zti.db1.domain.Client;
import pl.polsl.zti.db1.util.JdbcUtils;

public class ClientDaoImplJdbc implements ClientDao {


    public Client getClient(int id) {
        Client client = null;
        Connection con = null;
        PreparedStatement preparedStatement = null;
        try {
            con = JdbcUtils.getConnection();
            preparedStatement = con.prepareStatement("SELECT * FROM CLIENTS WHERE id = ?");
            preparedStatement.setInt(1, id);

            final ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                client = readClient(resultSet);
            }
        } catch (SQLException ex) {
            JdbcUtils.handleSqlException(ex);
        } finally {
            JdbcUtils.closeSilently(preparedStatement, con);
        }

        return client;
    }


    public List<Client> getClients() {
        final List<Client> clients = new ArrayList<Client>();

        Connection con = null;
        Statement stmt = null;
        try {
            con = JdbcUtils.getConnection();
            stmt = con.createStatement();
            final ResultSet resultSet = stmt.executeQuery("SELECT ID, CLIENT_NO, NAME, SSN, ADDRESS FROM CLIENTS");
            while (resultSet.next()) {
                final Client client = readClient(resultSet);
                clients.add(client);
            }
        } catch (SQLException ex) {
            JdbcUtils.handleSqlException(ex);
        } finally {
            JdbcUtils.closeSilently(stmt, con);
        }

        return clients;
    }


    public List<Client> getClients(String name) {


        final List<Client> clients = new ArrayList<Client>();

        Connection con = null;
        Statement stmt = null;
        String command;
        if (name == null)
            command = "SELECT ID, CLIENT_NO, NAME, SSN, ADDRESS FROM CLIENTS";
        else
            command = "SELECT ID, CLIENT_NO, NAME, SSN, ADDRESS FROM CLIENTS WHERE NAME LIKE '" + name + "'";
        try {
            con = JdbcUtils.getConnection();
            stmt = con.createStatement();
            final ResultSet resultSet = stmt.executeQuery(command);
            while (resultSet.next()) {
                final Client client = readClient(resultSet);
                clients.add(client);
            }
        } catch (SQLException ex) {
            JdbcUtils.handleSqlException(ex);
        } finally {
            JdbcUtils.closeSilently(stmt, con);
        }

        return clients;
    }

    private Client readClient(final ResultSet rs) throws SQLException {
        final Client client = new Client();
        client.setId(rs.getInt("ID"));
        client.setNo(rs.getInt("CLIENT_NO"));
        client.setName(rs.getString("NAME"));
        client.setSsn(rs.getString("SSN"));
        client.setAddress(rs.getString("ADDRESS"));
        return client;
    }


    public void insertClients(List<Client> clients) {
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = JdbcUtils.getConnection();
            pstmt = con.prepareStatement("INSERT INTO clients VALUES (?,?,?,?,?)");
            for (Client cln : clients) {
                con.setAutoCommit(false);
                pstmt.setInt(1, cln.getId());
                pstmt.setInt(2, cln.getNo());
                pstmt.setString(3, cln.getName());
                pstmt.setString(4, cln.getSsn());
                pstmt.setString(5, cln.getAddress());
                pstmt.executeUpdate();
                con.commit();
            }
        } catch (SQLException ex) {
            JdbcUtils.handleSqlException(ex);
        } finally {
            JdbcUtils.closeSilently(pstmt, con);
        }
    }


    public void updateClient(Client client) {


        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = JdbcUtils.getConnection();
            pstmt = con.prepareStatement("UPDATE clients SET CLIENT_NO=?, NAME=?, SSN=?, ADDRESS=? WHERE ID=?");

            pstmt.setInt(1, client.getNo());
            pstmt.setString(2, client.getName());
            pstmt.setString(3, client.getSsn());
            pstmt.setString(4, client.getAddress());
            pstmt.setInt(5, client.getId());
            pstmt.executeUpdate();

        } catch (SQLException ex) {
            JdbcUtils.handleSqlException(ex);
        } finally {
            JdbcUtils.closeSilently(pstmt, con);
        }
    }


    public void deleteClient(int id) {
        Connection con = null;
        Statement stmt = null;
        try {
            con = JdbcUtils.getConnection();
            stmt = con.createStatement();
            stmt.executeUpdate("DELETE FROM CLIENTS WHERE id = " + id);
        } catch (SQLException ex) {
            JdbcUtils.handleSqlException(ex);
        } finally {
            JdbcUtils.closeSilently(stmt, con);
        }
    }


    public void deleteClient(Client client) {
        deleteClient(client.getId());
    }
}