package com.reallinkbilling.util;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpSession;

import org.apache.struts2.dispatcher.SessionMap;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.reallinkbilling.DAO.ProductBarcodeDetailsDAO;
import com.reallinkbilling.db.GetDBConnection;
import com.reallinkbilling.pojo.BarcodeProductCatalogItem;

/**
 * Single place for "Reload Product List" / barcode session map. Used by
 * {@link com.reallinkbilling.ajax.ReloadProductList} and
 * {@link com.reallinkbilling.ajax.GetBarcodeProductCatalog} so the catalog
 * is never built from a stale or incompatible session object.
 */
public final class BarcodeProductDetailsSessionLoader {

    private BarcodeProductDetailsSessionLoader() {
    }

    public static String sessionString(SessionMap<String, Object> session, String key) {
        if (session == null) {
            return null;
        }
        Object o = session.get(key);
        if (o == null) {
            return null;
        }
        if (o instanceof String) {
            return (String) o;
        }
        return String.valueOf(o);
    }

    public static String httpSessionString(HttpSession httpSession, String key) {
        if (httpSession == null) {
            return null;
        }
        Object o = httpSession.getAttribute(key);
        if (o == null) {
            return null;
        }
        if (o instanceof String) {
            return (String) o;
        }
        return String.valueOf(o);
    }

    /**
     * Same DB load as {@link #loadAndPutInSession(SessionMap)} for JSP (HttpSession).
     */
    @SuppressWarnings("unchecked")
    public static int loadAndPutInSession(HttpSession httpSession) throws SQLException, IOException {
        if (httpSession == null) {
            return 0;
        }
        String shopId = httpSessionString(httpSession, "shopId");
        String shopCode = httpSessionString(httpSession, "shopCode");
        String db = httpSessionString(httpSession, "db");
        if (shopId == null || shopId.isEmpty() || shopCode == null || shopCode.isEmpty() || db == null || db.isEmpty()) {
            return 0;
        }

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = GetDBConnection.getDBConnection(db);
            String sql = "SELECT "
                    + "product_id,"
                    + "product_short_code,"
                    + "product_name_eng,"
                    + "product_name_mar,"
                    + "tax_id,"
                    + "tax_percentage,"
                    + "sales_unit,"
                    + "hsn_sac_code,"
                    + "barcode_mrp,"
                    + "discount_type,"
                    + "discount_amount_percentage,"
                    + "sales_price,"
                    + "default_barcode_quantity,"
                    + "purchase_rate,"
                    + "customer_sales_price,"
                    + "product_alias,"
                    + "in_stock_quantity,"
                    + "in_stock_quantity_2 "
                    + "FROM "
                    + "shop_product_master "
                    + "WHERE "
                    + "shop_id = ? "
                    + "AND "
                    + "shop_code = ? ";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, shopId);
            preparedStatement.setString(2, shopCode);

            resultSet = preparedStatement.executeQuery();
            Map<String, ProductBarcodeDetailsDAO> barcodeDetailsMap = new HashMap<String, ProductBarcodeDetailsDAO>();
            int n = 0;
            while (resultSet.next()) {
                n++;
                ProductBarcodeDetailsDAO detailsDAO = new ProductBarcodeDetailsDAO();
                detailsDAO.setProductId(resultSet.getString(1));
                detailsDAO.setProductCode(resultSet.getString(2));
                detailsDAO.setProductNameEn(resultSet.getString(3));
                detailsDAO.setProductNameMr(resultSet.getString(4));
                detailsDAO.setTaxId(resultSet.getInt(5));
                detailsDAO.setTaxPercentage(resultSet.getFloat(6));
                detailsDAO.setSalesUnit(resultSet.getString(7));
                detailsDAO.setHsnSacCode(resultSet.getString(8));
                detailsDAO.setBarcodeMRP(resultSet.getFloat(9));
                detailsDAO.setDiscountType(resultSet.getString(10));
                detailsDAO.setDiscount(resultSet.getFloat(11));
                detailsDAO.setSalesPrice(resultSet.getFloat(12));
                detailsDAO.setBarCodeQuantity(resultSet.getFloat(13));
                detailsDAO.setPurchasePrice(resultSet.getFloat(14));
                detailsDAO.setCustomerSalePrice(resultSet.getFloat(15));
                detailsDAO.setProductAlias(resultSet.getString(16));
                detailsDAO.setInStockQuantity(resultSet.getFloat(17));
                detailsDAO.setInStockQuantity2(resultSet.getFloat(18));
                barcodeDetailsMap.put(resultSet.getString(2), detailsDAO);
            }
            httpSession.removeAttribute("barcodeProductDetails");
            httpSession.setAttribute("barcodeProductDetails", barcodeDetailsMap);
            return n;
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                }
            }
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    /**
     * JSON array for the barcode-bill JSP: built from the same
     * <code>barcodeProductDetails</code> the server loaded at login (refreshed
     * if missing). Synchronous; no race with typing in the name field.
     */
    @SuppressWarnings("unchecked")
    public static String buildClientCatalogJson(HttpSession httpSession) {
        if (httpSession == null || httpSession.getAttribute("login") == null) {
            return "[]";
        }
        try {
            Object raw = httpSession.getAttribute("barcodeProductDetails");
            Map<String, ProductBarcodeDetailsDAO> map = null;
            if (raw instanceof Map) {
                map = (Map<String, ProductBarcodeDetailsDAO>) raw;
            }
            if (map == null || map.isEmpty()) {
                try {
                    loadAndPutInSession(httpSession);
                } catch (SQLException e) {
                } catch (IOException e) {
                }
                Object raw2 = httpSession.getAttribute("barcodeProductDetails");
                if (raw2 instanceof Map) {
                    map = (Map<String, ProductBarcodeDetailsDAO>) raw2;
                }
            }
            if (map == null || map.isEmpty()) {
                return "[]";
            }
            List<BarcodeProductCatalogItem> out = new ArrayList<BarcodeProductCatalogItem>();
            for (ProductBarcodeDetailsDAO d : new ArrayList<>(map.values())) {
                if (d == null || d.getProductCode() == null) {
                    continue;
                }
                try {
                    BarcodeProductCatalogItem row = new BarcodeProductCatalogItem();
                    String alias = d.getProductAlias() != null ? d.getProductAlias() : "";
                    String nameE = d.getProductNameEn() != null ? d.getProductNameEn() : "";
                    String nameM = d.getProductNameMr() != null ? d.getProductNameMr() : "";
                    row.setProductId(d.getProductId() != null ? d.getProductId().trim() : "");
                    row.setProductCode(d.getProductCode().trim());
                    row.setLabel(nameE + " | " + nameM + " | " + alias);
                    row.setProductName(nameE);
                    row.setProductNameMr(nameM);
                    row.setProductAlias(alias);
                    row.setTaxId(String.valueOf(d.getTaxId()));
                    row.setSalesUnit(d.getSalesUnit());
                    row.setSalesRate(d.getBarcodeMRP());
                    row.setAmount(d.getBarcodeMRP());
                    row.setAmountAfterDiscount(d.getSalesPrice());
                    row.setQuantity(d.getBarCodeQuantity());
                    out.add(row);
                } catch (RuntimeException ignore) {
                }
            }
            return new ObjectMapper().writeValueAsString(out);
        } catch (Exception e) {
            return "[]";
        }
    }

    /**
     * Replaces <code>barcodeProductDetails</code> in the session from
     * <code>shop_product_master</code>, same as the Reload button.
     *
     * @return number of product rows read
     */
    public static int loadAndPutInSession(SessionMap<String, Object> session) throws SQLException, IOException {
        if (session == null) {
            return 0;
        }
        String shopId = sessionString(session, "shopId");
        String shopCode = sessionString(session, "shopCode");
        String db = sessionString(session, "db");
        if (shopId == null || shopId.isEmpty() || shopCode == null || shopCode.isEmpty() || db == null || db.isEmpty()) {
            return 0;
        }

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = GetDBConnection.getDBConnection(db);
            String sql = "SELECT "
                    + "product_id,"
                    + "product_short_code,"
                    + "product_name_eng,"
                    + "product_name_mar,"
                    + "tax_id,"
                    + "tax_percentage,"
                    + "sales_unit,"
                    + "hsn_sac_code,"
                    + "barcode_mrp,"
                    + "discount_type,"
                    + "discount_amount_percentage,"
                    + "sales_price,"
                    + "default_barcode_quantity,"
                    + "purchase_rate,"
                    + "customer_sales_price,"
                    + "product_alias,"
                    + "in_stock_quantity,"
                    + "in_stock_quantity_2 "
                    + "FROM "
                    + "shop_product_master "
                    + "WHERE "
                    + "shop_id = ? "
                    + "AND "
                    + "shop_code = ? ";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, shopId);
            preparedStatement.setString(2, shopCode);

            resultSet = preparedStatement.executeQuery();
            Map<String, ProductBarcodeDetailsDAO> barcodeDetailsMap = new HashMap<String, ProductBarcodeDetailsDAO>();
            int n = 0;
            while (resultSet.next()) {
                n++;
                ProductBarcodeDetailsDAO detailsDAO = new ProductBarcodeDetailsDAO();
                detailsDAO.setProductId(resultSet.getString(1));
                detailsDAO.setProductCode(resultSet.getString(2));
                detailsDAO.setProductNameEn(resultSet.getString(3));
                detailsDAO.setProductNameMr(resultSet.getString(4));
                detailsDAO.setTaxId(resultSet.getInt(5));
                detailsDAO.setTaxPercentage(resultSet.getFloat(6));
                detailsDAO.setSalesUnit(resultSet.getString(7));
                detailsDAO.setHsnSacCode(resultSet.getString(8));
                detailsDAO.setBarcodeMRP(resultSet.getFloat(9));
                detailsDAO.setDiscountType(resultSet.getString(10));
                detailsDAO.setDiscount(resultSet.getFloat(11));
                detailsDAO.setSalesPrice(resultSet.getFloat(12));
                detailsDAO.setBarCodeQuantity(resultSet.getFloat(13));
                detailsDAO.setPurchasePrice(resultSet.getFloat(14));
                detailsDAO.setCustomerSalePrice(resultSet.getFloat(15));
                detailsDAO.setProductAlias(resultSet.getString(16));
                detailsDAO.setInStockQuantity(resultSet.getFloat(17));
                detailsDAO.setInStockQuantity2(resultSet.getFloat(18));
                barcodeDetailsMap.put(resultSet.getString(2), detailsDAO);
            }
            session.remove("barcodeProductDetails");
            session.put("barcodeProductDetails", barcodeDetailsMap);
            return n;
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                }
            }
            if (preparedStatement != null) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                }
            }
        }
    }
}
