package com.reallinkbilling.add;


import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

import org.apache.struts2.dispatcher.SessionMap;
import org.apache.struts2.interceptor.SessionAware;

import com.reallinkbilling.cipher.EncryptionDecryption;

import com.opensymphony.xwork2.ActionSupport;
import com.reallinkbilling.DAO.WareHouseStockTransferInfoDAO;
import com.reallinkbilling.DAO.WareHouseStockTransferProductDetailsDAO;
import com.reallinkbilling.db.GetDBConnection;
import com.reallinkbilling.resource.GetListResource;

public class SaveWareHouseStockTransferDetails extends ActionSupport implements SessionAware {

    private static final long serialVersionUID = 7421378693394500358L;

    private SessionMap<String, Object> sessionMap;

    private WareHouseStockTransferInfoDAO warehouseStockTransferInfoDAO = null;

    private ArrayList<WareHouseStockTransferProductDetailsDAO> warehouseStockTransferProductDAOList = null;

    private Map<String, String> warehouseList = new HashMap<>();
    
    private boolean warehouseStockTransferEntrySaved = false;
    
    private boolean fromUpdate = false;

    private String key;

    public String execute() throws IOException {

        String result = LOGIN;

        if (sessionMap == null || sessionMap.get("login") == null) {
            addActionMessage("Please Logout and Login again !");
            return result;
        }

        /*
         * Load Godown list
         */
        warehouseList.putAll(GetListResource.getGodownList(sessionMap));

        /*
         * Main stock is represented by "Main"
         */
        warehouseList.put("Main", "Main");

        String user = (String) sessionMap.get("userFullName");
        String userName = user != null ? user.split(" ")[0] : "unknown";

        String shopId = (String) sessionMap.get("shopId");
        String shopCode = (String) sessionMap.get("shopCode");
        String db = (String) sessionMap.get("db");

        Connection connection = null;

        try {

            connection = GetDBConnection.getDBConnection(db);

            if (connection == null) {
                addActionError("Unable to connect to database.");
                return ERROR;
            }

            connection.setAutoCommit(false);

            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");

            java.util.Date date = new java.util.Date();

            java.sql.Date sqlDate = new java.sql.Date(date.getTime());
            java.sql.Time sqlTime = new java.sql.Time(date.getTime());

            boolean isUpdate = "true".equalsIgnoreCase(
                    warehouseStockTransferInfoDAO.getFromUpdate());

            int transferEntryId = 0;

            /*
             * ============================================================
             * INSERT / UPDATE TRANSFER HEADER
             * ============================================================
             */

            if (!isUpdate) {

                String sql = "INSERT INTO shop_stock_transfer_info_master "
                        + "(shop_id, shop_code, stock_transfer_date, stock_transfer_time, "
                        + "from_godown_id, to_godown_id, total_products, entry_by) "
                        + "VALUES (?,?,?,?,?,?,?,?)";

                try (PreparedStatement ps = connection.prepareStatement(
                        sql, Statement.RETURN_GENERATED_KEYS)) {

                    ps.setString(1, shopId);
                    ps.setString(2, shopCode);
                    ps.setDate(3, sqlDate);
                    ps.setTime(4, sqlTime);

                    /*
                     * FROM GODOWN
                     *
                     * NULL means Main stock.
                     */
                    setGodownId(
                            ps,
                            5,
                            warehouseStockTransferInfoDAO.getFromWarehouseid());

                    /*
                     * TO GODOWN
                     *
                     * NULL means Main stock.
                     */
                    setGodownId(
                            ps,
                            6,
                            warehouseStockTransferInfoDAO.getToWarehouseid());

                    

                    ps.setInt(
                            7,
                            warehouseStockTransferProductDAOList.size());

                    ps.setString(8, userName);

                    int saveCount = ps.executeUpdate();

                    if (saveCount <= 0) {
                        connection.rollback();
                        addActionError("Failed to save stock transfer details.");
                        return ERROR;
                    }

                    /*
                     * Get AUTO_INCREMENT transfer ID
                     */
                    try (ResultSet rs = ps.getGeneratedKeys()) {

                        if (rs.next()) {
                            transferEntryId = rs.getInt(1);
                        } else {
                            connection.rollback();
                            addActionError(
                                    "Unable to generate stock transfer entry ID.");
                            return ERROR;
                        }
                    }
                }

                warehouseStockTransferInfoDAO.setTransferEntryId(
                        String.valueOf(transferEntryId));

            } else {

                /*
                 * Existing transfer ID
                 */
                transferEntryId = Integer.parseInt(
                        warehouseStockTransferInfoDAO.getTransferEntryId());

                /*
                 * --------------------------------------------------------
                 * IMPORTANT:
                 * First reverse OLD stock quantities.
                 * Otherwise editing a transfer will double-adjust stock.
                 * --------------------------------------------------------
                 */
                reverseOldTransferStock(
                        connection,
                        transferEntryId,
                        shopId,
                        shopCode);

                String sql = "UPDATE shop_stock_transfer_info_master SET "
                        + "stock_transfer_date=?, "
                        + "stock_transfer_time=?, "
                        + "from_godown_id=?, "
                        + "to_godown_id=?, "
                       
                        + "total_products=?, "
                        + "entry_by=? "
                        + "WHERE stock_transfer_entry_id=? "
                        + "AND shop_id=? "
                        + "AND shop_code=?";

                try (PreparedStatement ps = connection.prepareStatement(sql)) {

                    ps.setDate(1, sqlDate);
                    ps.setTime(2, sqlTime);

                    setGodownId(
                            ps,
                            3,
                            warehouseStockTransferInfoDAO.getFromWarehouseid());

                    setGodownId(
                            ps,
                            4,
                            warehouseStockTransferInfoDAO.getToWarehouseid());

                    

                    ps.setInt(
                            5,
                            warehouseStockTransferProductDAOList.size());

                    ps.setString(6, userName);

                    ps.setInt(7, transferEntryId);
                    ps.setString(8, shopId);
                    ps.setString(9, shopCode);

                    int updateCount = ps.executeUpdate();

                    if (updateCount <= 0) {
                        connection.rollback();
                        addActionError(
                                "Unable to update stock transfer details.");
                        return ERROR;
                    }
                }
            }

            /*
             * ============================================================
             * DELETE OLD PRODUCT DETAILS
             * ============================================================
             */

            String deleteSql =
                    "DELETE FROM shop_stock_transfer_product_details_master "
                    + "WHERE stock_transfer_entry_id=? "
                    + "AND shop_id=? "
                    + "AND shop_code=?";

            try (PreparedStatement ps =
                    connection.prepareStatement(deleteSql)) {

                ps.setInt(1, transferEntryId);
                ps.setString(2, shopId);
                ps.setString(3, shopCode);

                ps.executeUpdate();
            }

            /*
             * ============================================================
             * INSERT NEW PRODUCT DETAILS
             * ============================================================
             *
             * Expected table:
             *
             * shop_stock_transfer_product_details_master
             *
             * (
             *   stock_transfer_entry_id,
             *   shop_id,
             *   shop_code,
             *   product_id,
             *   product_name,
             *   stock_transfer_quantity,
             *   stock_transfer_unit
             * )
             *
             */

            String insertProductSql =
                    "INSERT INTO shop_stock_transfer_product_details_master "
                    + "(stock_transfer_entry_id, shop_id, shop_code, "
                    + "product_id, product_name, stock_transfer_quantity, "
                    + "stock_transfer_unit) "
                    + "VALUES (?,?,?,?,?,?,?)";

            try (PreparedStatement ps =
                    connection.prepareStatement(insertProductSql)) {

                for (WareHouseStockTransferProductDetailsDAO dao
                        : warehouseStockTransferProductDAOList) {

                    ps.setInt(1, transferEntryId);
                    ps.setString(2, shopId);
                    ps.setString(3, shopCode);
                    ps.setString(4, dao.getProductId());
                    ps.setString(5, dao.getProductName());
                    ps.setFloat(6, dao.getQuantity());
                    ps.setString(7, dao.getProductUnit());

                    ps.addBatch();
                }

                int[] counts = ps.executeBatch();

                if (counts.length !=
                        warehouseStockTransferProductDAOList.size()) {

                    connection.rollback();

                    addActionError(
                            "Failed to save all product details.");

                    return ERROR;
                }
            }

            /*
             * ============================================================
             * STOCK ADJUSTMENT
             * ============================================================
             */

            for (WareHouseStockTransferProductDetailsDAO dao
                    : warehouseStockTransferProductDAOList) {

                String productId = dao.getProductId();

                float quantity = dao.getQuantity();

                /*
                 * --------------------------------------------------------
                 * SOURCE
                 * --------------------------------------------------------
                 */

                String fromWarehouseId =
                        warehouseStockTransferInfoDAO.getFromWarehouseid();

                String fromWarehouseName =
                        warehouseStockTransferInfoDAO.getFromWarehouseName();

                /*
                 * Main -> deduct from shop_product_master
                 *
                 * Godown -> deduct from warehouse_stock_master
                 */
                if (isMain(fromWarehouseId, fromWarehouseName)) {

                    String sql =
                            "UPDATE shop_product_master "
                            + "SET in_stock_quantity = "
                            + "in_stock_quantity - ? "
                            + "WHERE product_id=? "
                            + "AND shop_id=? "
                            + "AND shop_code=?";

                    try (PreparedStatement ps =
                            connection.prepareStatement(sql)) {

                        ps.setFloat(1, quantity);
                        ps.setString(2, productId);
                        ps.setString(3, shopId);
                        ps.setString(4, shopCode);

                        ps.executeUpdate();
                    }

                } else {

                    String sql =
                            "UPDATE warehouse_stock_master "
                            + "SET in_stock_quantity = "
                            + "in_stock_quantity - ? "
                            + "WHERE warehouse_id=? "
                            + "AND product_id=? "
                            + "AND shop_id=? "
                            + "AND shop_code=?";

                    try (PreparedStatement ps =
                            connection.prepareStatement(sql)) {

                        ps.setFloat(1, quantity);
                        ps.setInt(
                                2,
                                Integer.parseInt(fromWarehouseId));
                        ps.setString(3, productId);
                        ps.setString(4, shopId);
                        ps.setString(5, shopCode);

                        ps.executeUpdate();
                    }
                }

                /*
                 * --------------------------------------------------------
                 * DESTINATION
                 * --------------------------------------------------------
                 *
                 * Main -> add to shop_product_master
                 *
                 * Godown -> add/update warehouse_stock_master
                 */

                String toWarehouseId =
                        warehouseStockTransferInfoDAO.getToWarehouseid();

                String toWarehouseName =
                        warehouseStockTransferInfoDAO.getToWarehouseName();

                if (isMain(toWarehouseId, toWarehouseName)) {

                    String sql =
                            "UPDATE shop_product_master "
                            + "SET in_stock_quantity = "
                            + "in_stock_quantity + ? "
                            + "WHERE product_id=? "
                            + "AND shop_id=? "
                            + "AND shop_code=?";

                    try (PreparedStatement ps =
                            connection.prepareStatement(sql)) {

                        ps.setFloat(1, quantity);
                        ps.setString(2, productId);
                        ps.setString(3, shopId);
                        ps.setString(4, shopCode);

                        ps.executeUpdate();
                    }

                } else {

                    /*
                     * Check whether product already exists
                     * in destination godown.
                     */
                    String checkSql =
                            "SELECT entry_id "
                            + "FROM warehouse_stock_master "
                            + "WHERE warehouse_id=? "
                            + "AND product_id=? "
                            + "AND shop_id=? "
                            + "AND shop_code=?";

                    boolean exists = false;

                    try (PreparedStatement ps =
                            connection.prepareStatement(checkSql)) {

                        ps.setInt(
                                1,
                                Integer.parseInt(toWarehouseId));
                        ps.setString(2, productId);
                        ps.setString(3, shopId);
                        ps.setString(4, shopCode);

                        try (ResultSet rs = ps.executeQuery()) {

                            if (rs.next()) {
                                exists = true;
                            }
                        }
                    }

                    if (exists) {

                        String updateSql =
                                "UPDATE warehouse_stock_master "
                                + "SET in_stock_quantity = "
                                + "in_stock_quantity + ? "
                                + "WHERE warehouse_id=? "
                                + "AND product_id=? "
                                + "AND shop_id=? "
                                + "AND shop_code=?";

                        try (PreparedStatement ps =
                                connection.prepareStatement(updateSql)) {

                            ps.setFloat(1, quantity);
                            ps.setInt(
                                    2,
                                    Integer.parseInt(toWarehouseId));
                            ps.setString(3, productId);
                            ps.setString(4, shopId);
                            ps.setString(5, shopCode);

                            ps.executeUpdate();
                        }

                    } else {

                        String insertSql =
                                "INSERT INTO warehouse_stock_master "
                                + "(warehouse_id, shop_id, shop_code, "
                                + "product_id, product_name, "
                                + "in_stock_quantity) "
                                + "VALUES (?,?,?,?,?,?)";

                        try (PreparedStatement ps =
                                connection.prepareStatement(insertSql)) {

                            ps.setInt(
                                    1,
                                    Integer.parseInt(toWarehouseId));
                            ps.setString(2, shopId);
                            ps.setString(3, shopCode);
                            ps.setString(4, productId);
                            ps.setString(5, dao.getProductName());
                            ps.setFloat(6, quantity);

                            ps.executeUpdate();
                        }
                    }
                }
            }

            /*
             * ============================================================
             * COMMIT
             * ============================================================
             */

            connection.commit();

            /*
             * ============================================================
             * SAVE SUCCESS
             * ============================================================
             */

            warehouseStockTransferEntrySaved = true;
            
            fromUpdate = isUpdate;

            /*
             * Keep transfer ID available for Print.
             */
            warehouseStockTransferInfoDAO.setTransferEntryId(
                    String.valueOf(transferEntryId));

            /*
             * Generate encrypted key for Print Stock Transfer.
             *
             * Print action expects the encrypted transfer ID
             * through the "key" parameter.
             */
            key = EncryptionDecryption.encrypt(
                    String.valueOf(transferEntryId));

            addActionMessage(
                    "Shop stock transfer details saved successfully!");

            result = SUCCESS;
            
        } catch (SQLException | NumberFormatException |
                 NullPointerException e) {

            try {

                if (connection != null) {
                    connection.rollback();
                }

            } catch (SQLException ignore) {
            }

            e.printStackTrace();

            addActionError("DB Error: " + e.getMessage());

            result = ERROR;

        } finally {

            try {

                if (connection != null) {
                    connection.close();
                }

            } catch (SQLException ignored) {
            }
        }

        /*
         * ============================================================
         * KEEP SAVED TRANSFER DATA AVAILABLE
         * ============================================================
         *
         * The JSP needs the transfer information and product list
         * after Save so that:
         *
         * Save button  -> disappears
         * Print button -> appears
         *
         */

        if (SUCCESS.equals(result)) {

            sessionMap.put(
                    "wareHouseStockTransferInfoDao",
                    warehouseStockTransferInfoDAO);

            sessionMap.put(
                    "wareHouseStockTransferProductDAOList",
                    warehouseStockTransferProductDAOList);
        }

        return result;
    }

    /*
     * ================================================================
     * SET GODOWN ID
     * ================================================================
     *
     * Main = NULL
     * Empty = NULL
     * 0 = NULL
     *
     */

    private void setGodownId(
            PreparedStatement ps,
            int parameterIndex,
            String godownId) throws SQLException {

        if (godownId == null
                || godownId.trim().isEmpty()
                || "0".equals(godownId.trim())
                || "Main".equalsIgnoreCase(godownId.trim())) {

            ps.setNull(
                    parameterIndex,
                    java.sql.Types.INTEGER);

        } else {

            ps.setInt(
                    parameterIndex,
                    Integer.parseInt(godownId));
        }
    }

    /*
     * ================================================================
     * CHECK MAIN STOCK
     * ================================================================
     */

    private boolean isMain(
            String warehouseId,
            String warehouseName) {

        if (warehouseId == null
                || warehouseId.trim().isEmpty()
                || "0".equals(warehouseId.trim())) {

            return true;
        }

        if (warehouseName != null
                && "Main".equalsIgnoreCase(
                        warehouseName.trim())) {

            return true;
        }

        return false;
    }

    /*
     * ================================================================
     * REVERSE OLD TRANSFER STOCK
     * ================================================================
     *
     * Used when editing an existing stock transfer.
     *
     * Example:
     *
     * Old:
     * Main -> Godown 1 = 10
     *
     * Edit:
     * Main -> Godown 1 = 5
     *
     * First:
     * Main +10
     * Godown 1 -10
     *
     * Then:
     * Main -5
     * Godown 1 +5
     *
     */

    private void reverseOldTransferStock(
            Connection connection,
            int transferEntryId,
            String shopId,
            String shopCode) throws SQLException {

        String headerSql =
                "SELECT from_godown_id, to_godown_id "
                + "FROM shop_stock_transfer_info_master "
                + "WHERE stock_transfer_entry_id=? "
                + "AND shop_id=? "
                + "AND shop_code=?";

        Integer oldFromGodownId = null;
        Integer oldToGodownId = null;

        try (PreparedStatement ps =
                connection.prepareStatement(headerSql)) {

            ps.setInt(1, transferEntryId);
            ps.setString(2, shopId);
            ps.setString(3, shopCode);

            try (ResultSet rs = ps.executeQuery()) {

                if (!rs.next()) {
                    return;
                }

                int fromId = rs.getInt("from_godown_id");

                if (!rs.wasNull()) {
                    oldFromGodownId = fromId;
                }

                int toId = rs.getInt("to_godown_id");

                if (!rs.wasNull()) {
                    oldToGodownId = toId;
                }
            }
        }

        /*
         * Get old transfer products.
         */
        String productSql =
                "SELECT product_id, stock_transfer_quantity "
                + "FROM shop_stock_transfer_product_details_master "
                + "WHERE stock_transfer_entry_id=? "
                + "AND shop_id=? "
                + "AND shop_code=?";

        try (PreparedStatement ps =
                connection.prepareStatement(productSql)) {

            ps.setInt(1, transferEntryId);
            ps.setString(2, shopId);
            ps.setString(3, shopCode);

            try (ResultSet rs = ps.executeQuery()) {

                while (rs.next()) {

                    String productId =
                            rs.getString("product_id");

                    float quantity =
                            rs.getFloat("stock_transfer_quantity");

                    /*
                     * ----------------------------------------------------
                     * Return OLD SOURCE STOCK
                     * ----------------------------------------------------
                     *
                     * Old source was Main:
                     * Main + quantity
                     *
                     * Old source was Godown:
                     * Godown + quantity
                     */

                    if (oldFromGodownId == null) {

                        String sql =
                                "UPDATE shop_product_master "
                                + "SET in_stock_quantity = "
                                + "in_stock_quantity + ? "
                                + "WHERE product_id=? "
                                + "AND shop_id=? "
                                + "AND shop_code=?";

                        try (PreparedStatement updatePs =
                                connection.prepareStatement(sql)) {

                            updatePs.setFloat(1, quantity);
                            updatePs.setString(2, productId);
                            updatePs.setString(3, shopId);
                            updatePs.setString(4, shopCode);

                            updatePs.executeUpdate();
                        }

                    } else {

                        String sql =
                                "UPDATE warehouse_stock_master "
                                + "SET in_stock_quantity = "
                                + "in_stock_quantity + ? "
                                + "WHERE warehouse_id=? "
                                + "AND product_id=? "
                                + "AND shop_id=? "
                                + "AND shop_code=?";

                        try (PreparedStatement updatePs =
                                connection.prepareStatement(sql)) {

                            updatePs.setFloat(1, quantity);
                            updatePs.setInt(2, oldFromGodownId);
                            updatePs.setString(3, productId);
                            updatePs.setString(4, shopId);
                            updatePs.setString(5, shopCode);

                            updatePs.executeUpdate();
                        }
                    }

                    /*
                     * ----------------------------------------------------
                     * REMOVE OLD DESTINATION STOCK
                     * ----------------------------------------------------
                     *
                     * Old destination was Main:
                     * Main - quantity
                     *
                     * Old destination was Godown:
                     * Godown - quantity
                     */

                    if (oldToGodownId == null) {

                        String sql =
                                "UPDATE shop_product_master "
                                + "SET in_stock_quantity = "
                                + "in_stock_quantity - ? "
                                + "WHERE product_id=? "
                                + "AND shop_id=? "
                                + "AND shop_code=?";

                        try (PreparedStatement updatePs =
                                connection.prepareStatement(sql)) {

                            updatePs.setFloat(1, quantity);
                            updatePs.setString(2, productId);
                            updatePs.setString(3, shopId);
                            updatePs.setString(4, shopCode);

                            updatePs.executeUpdate();
                        }

                    } else {

                        String sql =
                                "UPDATE warehouse_stock_master "
                                + "SET in_stock_quantity = "
                                + "in_stock_quantity - ? "
                                + "WHERE warehouse_id=? "
                                + "AND product_id=? "
                                + "AND shop_id=? "
                                + "AND shop_code=?";

                        try (PreparedStatement updatePs =
                                connection.prepareStatement(sql)) {

                            updatePs.setFloat(1, quantity);
                            updatePs.setInt(2, oldToGodownId);
                            updatePs.setString(3, productId);
                            updatePs.setString(4, shopId);
                            updatePs.setString(5, shopCode);

                            updatePs.executeUpdate();
                        }
                    }
                }
            }
        }
    }

    /*
     * ================================================================
     * VALIDATE
     * ================================================================
     */

    @Override
    @SuppressWarnings("unchecked")
    public void validate() {

        if (sessionMap == null
                || sessionMap.get("login") == null) {

            return;
        }

        warehouseStockTransferInfoDAO =
                (WareHouseStockTransferInfoDAO)
                        sessionMap.get(
                                "wareHouseStockTransferInfoDao");

        warehouseStockTransferProductDAOList =
                (ArrayList<WareHouseStockTransferProductDetailsDAO>)
                        sessionMap.get(
                                "wareHouseStockTransferProductDAOList");

        if (warehouseStockTransferInfoDAO == null) {

            addActionError(
                    "Missing transfer info.");
        }

        if (warehouseStockTransferProductDAOList == null
                || warehouseStockTransferProductDAOList.isEmpty()) {

            addActionError(
                    "Please add at least one product to transfer.");
        }
    }

    /*
     * ================================================================
     * SESSION
     * ================================================================
     */

    @Override
    @SuppressWarnings("unchecked")
    public void setSession(Map<String, Object> map) {

        sessionMap =
                (SessionMap<String, Object>) map;
    }

    public SessionMap<String, Object> getSessionMap() {

        return sessionMap;
    }

    public void setSessionMap(
            SessionMap<String, Object> sessionMap) {

        this.sessionMap = sessionMap;
    }

    /*
     * ================================================================
     * GETTERS / SETTERS
     * ================================================================
     */

    public WareHouseStockTransferInfoDAO
            getWarehouseStockTransferInfoDAO() {

        return warehouseStockTransferInfoDAO;
    }

    public void setWarehouseStockTransferInfoDAO(
            WareHouseStockTransferInfoDAO
                    warehouseStockTransferInfoDAO) {

        this.warehouseStockTransferInfoDAO =
                warehouseStockTransferInfoDAO;
    }

    public ArrayList<WareHouseStockTransferProductDetailsDAO>
            getWarehouseStockTransferProductDAOList() {

        return warehouseStockTransferProductDAOList;
    }

    public void setWarehouseStockTransferProductDAOList(
            ArrayList<WareHouseStockTransferProductDetailsDAO>
                    warehouseStockTransferProductDAOList) {

        this.warehouseStockTransferProductDAOList =
                warehouseStockTransferProductDAOList;
    }

    public Map<String, String> getWarehouseList() {

        return warehouseList;
    }

    public void setWarehouseList(
            Map<String, String> warehouseList) {

        this.warehouseList = warehouseList;
    }

	public boolean isWarehouseStockTransferEntrySaved() {
		return warehouseStockTransferEntrySaved;
	}

	public void setWarehouseStockTransferEntrySaved(boolean warehouseStockTransferEntrySaved) {
		this.warehouseStockTransferEntrySaved = warehouseStockTransferEntrySaved;
	}

	public String getKey() {
		return key;
	}

	public void setKey(String key) {
		this.key = key;
	}

	public boolean isFromUpdate() {
		return fromUpdate;
	}

	public void setFromUpdate(boolean fromUpdate) {
		this.fromUpdate = fromUpdate;
	}
    

}

