package fr.leansys.business;

import fr.leansys.model.ShopItem;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ShopItemServiceImpl implements ShopItemService {
    private static final Logger log = LoggerFactory.getLogger(ShopItemServiceImpl.class);

    private static final String DRIVER_NAME = "org.h2.Driver";
    private static final String CONNECT_URL = "jdbc:h2:tcp://localhost/~/formation";
    private static final String USERNAME = "sa";
    private static final String PASSWORD = "sa";

    /**
     * Create a database connection
     * @return Connection
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    private Connection getConnection() throws ClassNotFoundException, SQLException {
        log.debug("Creating connection with user {}", USERNAME);
        Class.forName(DRIVER_NAME);
        return DriverManager.getConnection(CONNECT_URL, USERNAME, PASSWORD);
    }

    /**
     * Delete record
     * @param id
     * @return
     */
    public boolean delete(int id) {
        long before = System.currentTimeMillis();

        log.info("Entering Delete Method");

        boolean result = false;
        Connection cnx = null;
        PreparedStatement statement = null;

        try {
            cnx = getConnection();

            //Turn off AutoCommit
            cnx.setAutoCommit(false);

            //Create and execute the SQL statement
            String sqlOrder = "DELETE FROM SHOPPINGITEMS WHERE ITEMID = ?";

            //Create the Statement object
            statement = cnx.prepareStatement(sqlOrder);
            statement.setInt(1, id);
            result = (statement.executeUpdate() == 1);

            cnx.commit();
        } catch (ClassNotFoundException e) {
            log.error("Error creating class: {}", e.getMessage());
        } catch (SQLException e) {
            log.debug("Error creating connection: {}", e.getMessage());
            try {
                cnx.rollback();
            } catch (Exception ex) {
                log.error("Can't rollback transaction.");
            }
        } finally {
            log.debug("Closing connections...");
            try {
                statement.close();
            } catch (Exception e1) {
                log.error("Can't close statement.");
            }
            try {
                cnx.close();
            } catch (Exception e) {
                log.error("Can't close connection.");
            }
        }

        log.debug("Result: {}", result);
        log.debug("Duration: {} ms", (System.currentTimeMillis() - before));

        return result;
    }

    /**
     * Save record
     * @param shopItem
     * @return
     */
    public int save(ShopItem shopItem) {
        long before = System.currentTimeMillis();

        log.info("Entering Save Method");

        Connection cnx = null;
        PreparedStatement statement = null;
        Statement statementForId;
        ResultSet resultSet = null;

        int results = 0;

        try {
            cnx = getConnection();

            //Turn off AutoCommit
            cnx.setAutoCommit(false);

            //Create and execute the SQL statement
            String sqlOrder = "INSERT INTO SHOPPINGITEMS (NAME, DESCRIPTION, PRICE) VALUES (?, ?, ?)";

            //Create the Statement object
            statement = cnx.prepareStatement(sqlOrder);
            statement.setString(1, shopItem.getName());
            statement.setString(2, shopItem.getDescription());
            statement.setInt(3, shopItem.getPrice());

            results = statement.executeUpdate();
            assert results == 1; // process error

            // Retrieve our Id from database
            statementForId = cnx.createStatement();
            resultSet = statementForId.executeQuery("SELECT LAST_INSERT_ID()");

            if (resultSet.next()) {
                shopItem.setId(resultSet.getInt(1));
                log.debug("Inserted item with id {}", shopItem.getId(1));
            } // process error

            cnx.commit();
        } catch (ClassNotFoundException e) {
            log.error("Error creating class: {}", e.getMessage());
        } catch (SQLException e) {
            log.debug("Error creating connection: {}", e.getMessage());
            try {
                cnx.rollback();
            } catch (Exception ex) {
                log.error("Can't rollback transaction.");
            }
        } finally {
            log.debug("Closing connections...");
            try {
                if (resultSet != null) resultSet.close();
            } catch (Exception e1) {
                log.error("Can't close resultset.");
            }
            try {
                statement.close();
            } catch (Exception e1) {
                log.error("Can't close statement.");
            }
            try {
                cnx.close();
            } catch (Exception e) {
                log.error("Can't close connection.");
            }
        }

        log.debug("Results: {}", results);
        log.debug("Duration: {} ms", (System.currentTimeMillis() - before));

        return shopItem.getId(1);
    }

    /**
     * Get all records
     * @return
     */
    public List<ShopItem> getAll() {
        long before = System.currentTimeMillis();

        log.info("Entering List Method");

        Connection cnx = null;
        Statement statement = null;
        ResultSet resultSet = null;
        List<ShopItem> results = new ArrayList<>();

        try {
            cnx = getConnection();

            // Turn off AutoCommit
            cnx.setAutoCommit(false);

            // Create the Statement object
            statement = cnx.createStatement();

            // Create and execute the SQL statement
            String sqlOrder = "SELECT ITEMID, NAME, DESCRIPTION, PRICE FROM SHOPPINGITEMS";

            resultSet = statement.executeQuery(sqlOrder);

            // Iterate over ResultSet to build Items List
            ShopItem item;
            while (resultSet.next()) {
                item = new ShopItem();
                item.setId(resultSet.getInt("ITEMID"));
                item.setName(resultSet.getString("NAME"));
                item.setDescription(resultSet.getString("DESCRIPTION"));
                item.setPrice(resultSet.getInt("PRICE"));

                results.add(item);
            }
        } catch (ClassNotFoundException e) {
            log.debug("Error creating class: {}", e.getMessage());
        } catch (SQLException e) {
            log.error("Error SQL: {}", e.getMessage());
        } finally {
            log.debug("Closing connections...");
            try {
                if (resultSet != null) resultSet.close();
            } catch (Exception e1) {
                log.error("Can't close resultset.");
            }
            try {
                if (statement != null) statement.close();
            } catch (Exception e1) {
                log.error("Can't close statement.");
            }
            try {
                if (cnx != null) cnx.close();
            } catch (Exception e) {
                log.error("Can't close connection.");
            }
        }

        log.debug("Results: {}", results.size());
        log.debug("Duration: {} ms", (System.currentTimeMillis() - before));

        return results;
    }



    /**
     * Get byId records
     * @return
     */
    public boolean getById(int id) {
        long before = System.currentTimeMillis();

        log.info("Entering getById Method");

        boolean result = false;
        Connection cnx = null;
        PreparedStatement statement = null;

        try {
            cnx = getConnection();

            //Turn off AutoCommit
            cnx.setAutoCommit(false);

            //Create and execute the SQL statement
            String sqlOrder = "SELECT NAME, DESCRIPTION, PRICE FROM SHOPPINGITEMS where ITEMID = ?";

            //Create the Statement object
            statement = cnx.prepareStatement(sqlOrder);
            statement.setInt(1, id);


            cnx.commit();
        } catch (ClassNotFoundException e) {
            log.error("Error creating class: {}", e.getMessage());
        } catch (SQLException e) {
            log.debug("Error creating connection: {}", e.getMessage());
            try {
                cnx.rollback();
            } catch (Exception ex) {
                log.error("Can't rollback transaction.");
            }
        } finally {
            log.debug("Closing connections...");
            try {
                statement.close();
            } catch (Exception e1) {
                log.error("Can't close statement.");
            }
            try {
                cnx.close();
            } catch (Exception e) {
                log.error("Can't close connection.");
            }
        }

        log.debug("Result: {}", result);
        log.debug("Duration: {} ms", (System.currentTimeMillis() - before));

        return result;
    }
}