package com.reallinkbilling.utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Map;

import org.apache.struts2.dispatcher.SessionMap;

import com.reallinkbilling.DAO.ProductBarcodeDetailsDAO;
import com.reallinkbilling.resource.GetNewIdForNewEntry;

public class LabelCapProductUtil {

	public static LabelCapProduct findOrCreate(Connection connection, SessionMap<String, Object> sessionMap,
			String finishedProductId, String labelId, String capId) throws SQLException {

		String shopId = (String) sessionMap.get("shopId");
		String shopCode = (String) sessionMap.get("shopCode");

		LabelCapProduct mapped = findMappedProduct(connection, shopId, shopCode, finishedProductId, labelId, capId);
		if (mapped != null) {
			return mapped;
		}

		LabelCapProduct finished = loadProductById(connection, shopId, shopCode, finishedProductId);
		if (finished == null) {
			throw new SQLException("Finished product was not found in product master.");
		}

		String labelCode = loadCode(connection, "SELECT label_code FROM shop_label_master WHERE label_id = ? AND shop_id = ? AND shop_code = ?", shopId, shopCode, labelId);
		String capCode = loadCode(connection, "SELECT cap_code FROM shop_cap_master WHERE cap_id = ? AND shop_id = ? AND shop_code = ?", shopId, shopCode, capId);
		if (labelCode == null || capCode == null) {
			throw new SQLException("Selected label or cap was not found.");
		}

		String productName = (blank(finished.getProductName()) + " " + labelCode + " " + capCode).trim().toUpperCase();
		String marathiBase = blank(finished.getProductNameMar());
		if (marathiBase.equals("")) {
			marathiBase = blank(finished.getProductName());
		}
		String productNameMar = (marathiBase + " " + labelCode + " " + capCode).trim().toUpperCase();

		LabelCapProduct existingByName = findProductByName(connection, shopId, shopCode, productName);
		if (existingByName != null) {
			insertMap(connection, sessionMap, shopId, shopCode, finishedProductId, labelId, capId, existingByName);
			existingByName.setNewlyCreated(false);
			return existingByName;
		}

		String shortCode = uniqueShortCode(connection, shopId, shopCode, buildShortCode(finished.getProductCode(), labelCode, capCode));
		int newProductId = GetNewIdForNewEntry.getNewIdForNewEntry("shop_product_master", "product_id", sessionMap, connection);
		if (newProductId <= 0) {
			throw new SQLException("Could not generate a new product id.");
		}

		String today = new SimpleDateFormat("dd-MM-yyyy").format(new Date());
		String sql = "INSERT INTO shop_product_master ("
				+ "product_id,shop_id,shop_code,add_date,"
				+ "product_name_eng,product_name_mar,product_short_code,product_alias,"
				+ "low_stock_count,purchase_rate,customer_sales_price,dealer_sales_price,retailer_sales_price,"
				+ "tax_id,tax_percentage,purchase_unit,sales_unit,hsn_sac_code,stock_conversion_ratio,"
				+ "product_image,in_stock_quantity,in_stock_quantity_2,rack_number,barcode_mrp,"
				+ "discount_type,discount_amount_percentage,sales_price,default_barcode_quantity) "
				+ "VALUES (?,?,?,STR_TO_DATE(?, '%d-%m-%Y'),?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

		PreparedStatement preparedStatement = null;
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, newProductId);
			preparedStatement.setString(2, shopId);
			preparedStatement.setString(3, shopCode);
			preparedStatement.setString(4, today);
			preparedStatement.setString(5, productName);
			preparedStatement.setString(6, productNameMar);
			preparedStatement.setString(7, shortCode);
			preparedStatement.setString(8, shortCode);
			preparedStatement.setString(9, blankTo(finished.getLowStockCount(), "0"));
			preparedStatement.setFloat(10, zero(finished.getPurchaseRate()));
			preparedStatement.setFloat(11, zero(finished.getCustomerSalesPrice()));
			preparedStatement.setFloat(12, zero(finished.getDealerSalesPrice()));
			preparedStatement.setFloat(13, zero(finished.getRetailerSalesPrice()));
			preparedStatement.setString(14, blankTo(finished.getTaxId(), "0"));
			preparedStatement.setFloat(15, zero(finished.getTaxPercentage()));
			preparedStatement.setString(16, blankTo(finished.getPurchaseUnit(), "NA"));
			preparedStatement.setString(17, blankTo(finished.getSalesUnit(), "NA"));
			preparedStatement.setString(18, blankTo(finished.getHsnCode(), "NA"));
			preparedStatement.setString(19, blankTo(finished.getStockConversionRatio(), "1"));
			preparedStatement.setBinaryStream(20, null);
			preparedStatement.setFloat(21, 0F);
			preparedStatement.setFloat(22, 0F);
			preparedStatement.setString(23, blankTo(finished.getRackNumber(), "NA"));
			preparedStatement.setFloat(24, zero(finished.getBarcodeMrp()));
			preparedStatement.setString(25, blankTo(finished.getDiscountType(), "PERQUANTITY"));
			preparedStatement.setFloat(26, zero(finished.getDiscount()));
			preparedStatement.setFloat(27, zero(finished.getSalesPrice()));
			preparedStatement.setFloat(28, finished.getDefaultBarcodeQty() == null || finished.getDefaultBarcodeQty() == 0F ? 1F : finished.getDefaultBarcodeQty());
			int inserted = preparedStatement.executeUpdate();
			if (inserted <= 0) {
				throw new SQLException("Product could not be added for this label and cap.");
			}
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
				}
			}
		}

		LabelCapProduct created = loadProductById(connection, shopId, shopCode, String.valueOf(newProductId));
		if (created == null) {
			throw new SQLException("Product was added but could not be read back.");
		}
		created.setNewlyCreated(true);
		insertMap(connection, sessionMap, shopId, shopCode, finishedProductId, labelId, capId, created);
		return created;
	}

	@SuppressWarnings("unchecked")
	public static void syncSessionStock(SessionMap<String, Object> sessionMap, LabelCapProduct product, float addedQty) {
		if (sessionMap == null || product == null || product.getProductCode() == null) {
			return;
		}
		Object stored = sessionMap.get("barcodeProductDetails");
		if (!(stored instanceof Map)) {
			return;
		}
		Map<String, ProductBarcodeDetailsDAO> barcodeDetailsMap = (Map<String, ProductBarcodeDetailsDAO>) stored;
		ProductBarcodeDetailsDAO dao = barcodeDetailsMap.get(product.getProductCode());
		if (dao == null) {
			for (ProductBarcodeDetailsDAO item : barcodeDetailsMap.values()) {
				if (item != null && product.getProductId() != null && product.getProductId().equals(item.getProductId())) {
					dao = item;
					break;
				}
			}
		}
		if (dao == null) {
			barcodeDetailsMap.put(product.getProductCode(), product.toBarcodeDao(addedQty));
		} else {
			float oldQty = dao.getInStockQuantity() == null ? 0F : dao.getInStockQuantity();
			dao.setInStockQuantity(oldQty + addedQty);
		}
		sessionMap.put("barcodeProductDetails", barcodeDetailsMap);
	}

	private static LabelCapProduct findMappedProduct(Connection connection, String shopId, String shopCode,
			String finishedProductId, String labelId, String capId) throws SQLException {
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String productId = null;
		try {
			String sql = "SELECT product_id FROM shop_product_label_cap_map "
					+ "WHERE shop_id = ? AND shop_code = ? AND finished_product_id = ? AND label_id = ? AND cap_id = ?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, shopId);
			preparedStatement.setString(2, shopCode);
			preparedStatement.setString(3, finishedProductId);
			preparedStatement.setString(4, labelId);
			preparedStatement.setString(5, capId);
			resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				productId = resultSet.getString(1);
			}
		} finally {
			close(resultSet, preparedStatement);
		}
		if (productId == null) {
			return null;
		}
		LabelCapProduct product = loadProductById(connection, shopId, shopCode, productId);
		if (product == null) {
			deleteMap(connection, shopId, shopCode, finishedProductId, labelId, capId);
			return null;
		}
		product.setNewlyCreated(false);
		return product;
	}

	private static void deleteMap(Connection connection, String shopId, String shopCode, String finishedProductId, String labelId, String capId) throws SQLException {
		PreparedStatement preparedStatement = null;
		try {
			String sql = "DELETE FROM shop_product_label_cap_map "
					+ "WHERE shop_id = ? AND shop_code = ? AND finished_product_id = ? AND label_id = ? AND cap_id = ?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, shopId);
			preparedStatement.setString(2, shopCode);
			preparedStatement.setString(3, finishedProductId);
			preparedStatement.setString(4, labelId);
			preparedStatement.setString(5, capId);
			preparedStatement.executeUpdate();
		} finally {
			close(null, preparedStatement);
		}
	}

	private static LabelCapProduct findProductByName(Connection connection, String shopId, String shopCode, String productName) throws SQLException {
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String productId = null;
		try {
			String sql = "SELECT product_id FROM shop_product_master WHERE product_name_eng = ? AND shop_id = ? AND shop_code = ?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, productName);
			preparedStatement.setString(2, shopId);
			preparedStatement.setString(3, shopCode);
			resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				productId = resultSet.getString(1);
			}
		} finally {
			close(resultSet, preparedStatement);
		}
		if (productId == null) {
			return null;
		}
		return loadProductById(connection, shopId, shopCode, productId);
	}

	private static LabelCapProduct loadProductById(Connection connection, String shopId, String shopCode, String productId) throws SQLException {
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			String sql = "SELECT product_id, product_name_eng, product_name_mar, product_short_code, product_alias, "
					+ "low_stock_count, purchase_rate, customer_sales_price, dealer_sales_price, retailer_sales_price, "
					+ "tax_id, tax_percentage, purchase_unit, sales_unit, hsn_sac_code, stock_conversion_ratio, "
					+ "rack_number, barcode_mrp, discount_type, discount_amount_percentage, sales_price, default_barcode_quantity "
					+ "FROM shop_product_master WHERE product_id = ? AND shop_id = ? AND shop_code = ?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, productId);
			preparedStatement.setString(2, shopId);
			preparedStatement.setString(3, shopCode);
			resultSet = preparedStatement.executeQuery();
			if (!resultSet.next()) {
				return null;
			}
			LabelCapProduct product = new LabelCapProduct();
			product.setProductId(resultSet.getString("product_id"));
			product.setProductName(resultSet.getString("product_name_eng"));
			product.setProductNameMar(resultSet.getString("product_name_mar"));
			product.setProductCode(resultSet.getString("product_short_code"));
			product.setAlias(resultSet.getString("product_alias"));
			product.setLowStockCount(resultSet.getString("low_stock_count"));
			product.setPurchaseRate(resultSet.getFloat("purchase_rate"));
			product.setCustomerSalesPrice(resultSet.getFloat("customer_sales_price"));
			product.setDealerSalesPrice(resultSet.getFloat("dealer_sales_price"));
			product.setRetailerSalesPrice(resultSet.getFloat("retailer_sales_price"));
			product.setTaxId(resultSet.getString("tax_id"));
			product.setTaxPercentage(resultSet.getFloat("tax_percentage"));
			product.setPurchaseUnit(resultSet.getString("purchase_unit"));
			product.setSalesUnit(resultSet.getString("sales_unit"));
			product.setHsnCode(resultSet.getString("hsn_sac_code"));
			product.setStockConversionRatio(resultSet.getString("stock_conversion_ratio"));
			product.setRackNumber(resultSet.getString("rack_number"));
			product.setBarcodeMrp(resultSet.getFloat("barcode_mrp"));
			product.setDiscountType(resultSet.getString("discount_type"));
			product.setDiscount(resultSet.getFloat("discount_amount_percentage"));
			product.setSalesPrice(resultSet.getFloat("sales_price"));
			product.setDefaultBarcodeQty(resultSet.getFloat("default_barcode_quantity"));
			return product;
		} finally {
			close(resultSet, preparedStatement);
		}
	}

	private static void insertMap(Connection connection, SessionMap<String, Object> sessionMap, String shopId, String shopCode,
			String finishedProductId, String labelId, String capId, LabelCapProduct product) throws SQLException {
		int mapId = GetNewIdForNewEntry.getNewIdForNewEntry("shop_product_label_cap_map", "map_id", sessionMap, connection);
		if (mapId <= 0) {
			mapId = 1;
		}
		String today = new SimpleDateFormat("dd-MM-yyyy").format(new Date());
		PreparedStatement preparedStatement = null;
		try {
			String sql = "INSERT INTO shop_product_label_cap_map ("
					+ "map_id,shop_id,shop_code,finished_product_id,label_id,cap_id,product_id,product_name,product_short_code,add_date) "
					+ "VALUES (?,?,?,?,?,?,?,?,?,STR_TO_DATE(?,'%d-%m-%Y'))";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, mapId);
			preparedStatement.setString(2, shopId);
			preparedStatement.setString(3, shopCode);
			preparedStatement.setString(4, finishedProductId);
			preparedStatement.setString(5, labelId);
			preparedStatement.setString(6, capId);
			preparedStatement.setString(7, product.getProductId());
			preparedStatement.setString(8, product.getProductName());
			preparedStatement.setString(9, product.getProductCode());
			preparedStatement.setString(10, today);
			preparedStatement.executeUpdate();
		} finally {
			close(null, preparedStatement);
		}
	}

	private static String loadCode(Connection connection, String sql, String shopId, String shopCode, String id) throws SQLException {
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, id);
			preparedStatement.setString(2, shopId);
			preparedStatement.setString(3, shopCode);
			resultSet = preparedStatement.executeQuery();
			if (resultSet.next() && resultSet.getString(1) != null) {
				return resultSet.getString(1).trim().toUpperCase();
			}
			return null;
		} finally {
			close(resultSet, preparedStatement);
		}
	}

	private static String buildShortCode(String finishedCode, String labelCode, String capCode) {
		String raw = (blank(finishedCode) + labelCode + capCode).toUpperCase().replaceAll("[^A-Z0-9]", "");
		if (raw.equals("")) {
			raw = "PROD";
		}
		if (raw.length() > 20) {
			raw = raw.substring(0, 20);
		}
		return raw;
	}

	private static String uniqueShortCode(Connection connection, String shopId, String shopCode, String shortCode) throws SQLException {
		String candidate = shortCode;
		int suffix = 1;
		while (shortCodeExists(connection, shopId, shopCode, candidate)) {
			String extra = String.valueOf(suffix);
			int keep = 20 - extra.length();
			if (keep < 1) {
				keep = 1;
			}
			String base = shortCode.length() > keep ? shortCode.substring(0, keep) : shortCode;
			candidate = base + extra;
			suffix++;
			if (suffix > 50) {
				break;
			}
		}
		return candidate;
	}

	private static boolean shortCodeExists(Connection connection, String shopId, String shopCode, String shortCode) throws SQLException {
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			String sql = "SELECT product_id FROM shop_product_master WHERE (product_short_code = ? OR product_alias = ?) AND shop_id = ? AND shop_code = ?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, shortCode);
			preparedStatement.setString(2, shortCode);
			preparedStatement.setString(3, shopId);
			preparedStatement.setString(4, shopCode);
			resultSet = preparedStatement.executeQuery();
			return resultSet.next();
		} finally {
			close(resultSet, preparedStatement);
		}
	}

	private static String blank(String value) {
		return value == null ? "" : value.trim();
	}

	private static String blankTo(String value, String fallback) {
		String text = blank(value);
		return text.equals("") ? fallback : text;
	}

	private static float zero(Float value) {
		return value == null ? 0F : value;
	}

	private static void close(ResultSet resultSet, PreparedStatement preparedStatement) {
		if (resultSet != null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
			}
		}
		if (preparedStatement != null) {
			try {
				preparedStatement.close();
			} catch (SQLException e) {
			}
		}
	}
}
