package com.reallinkbilling.utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import com.reallinkbilling.DAO.CapMasterDAO;
import com.reallinkbilling.DAO.LabelMasterDAO;

public class PackingMasterOptions {

	public static Map<String, String> customerOptions(Connection connection, String shopId, String shopCode) throws SQLException {
		Map<String, String> options = new LinkedHashMap<String, String>();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			String sql = "SELECT "
					+ "customer_id,"
					+ "customer_name "
					+ "FROM "
					+ "shop_customer_master "
					+ "WHERE "
					+ "shop_id = ? "
					+ "AND "
					+ "shop_code = ? "
					+ "ORDER BY customer_name";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, shopId);
			preparedStatement.setString(2, shopCode);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				String name = resultSet.getString(2);
				if (name == null || name.trim().equals("")) {
					name = "CUSTOMER " + resultSet.getString(1);
				}
				options.put(resultSet.getString(1), name);
			}
		} finally {
			close(resultSet, preparedStatement);
		}
		return options;
	}

	public static String customerName(Connection connection, String shopId, String shopCode, String customerId) throws SQLException {
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			String sql = "SELECT customer_name FROM shop_customer_master WHERE customer_id = ? AND shop_id = ? AND shop_code = ?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, customerId);
			preparedStatement.setString(2, shopId);
			preparedStatement.setString(3, shopCode);
			resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				return resultSet.getString(1);
			}
			return null;
		} finally {
			close(resultSet, preparedStatement);
		}
	}

	public static void loadFinishedProducts(Connection connection, String shopId, String shopCode, Map<String, String> target) throws SQLException {
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			String sql = "SELECT "
					+ "finished_product_id,"
					+ "finished_product_name_eng "
					+ "FROM "
					+ "shop_finished_product_details_master "
					+ "WHERE "
					+ "shop_id = ? "
					+ "AND "
					+ "shop_code = ?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, shopId);
			preparedStatement.setString(2, shopCode);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				target.put(resultSet.getString(1), resultSet.getString(2));
			}
		} finally {
			close(resultSet, preparedStatement);
		}
	}

	public static void loadLabels(Connection connection, String shopId, String shopCode, Map<String, String> target) throws SQLException {
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			String sql = "SELECT label_id, label_name, label_code, customer_name "
					+ "FROM shop_label_master "
					+ "WHERE shop_id = ? AND shop_code = ? "
					+ "ORDER BY label_name";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, shopId);
			preparedStatement.setString(2, shopCode);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				target.put(resultSet.getString(1), display(resultSet.getString(2), resultSet.getString(3), resultSet.getString(4)));
			}
		} finally {
			close(resultSet, preparedStatement);
		}
	}

	public static void loadCaps(Connection connection, String shopId, String shopCode, Map<String, String> target) throws SQLException {
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			String sql = "SELECT cap_id, cap_name, cap_code, customer_name "
					+ "FROM shop_cap_master "
					+ "WHERE shop_id = ? AND shop_code = ? "
					+ "ORDER BY cap_name";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, shopId);
			preparedStatement.setString(2, shopCode);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				target.put(resultSet.getString(1), display(resultSet.getString(2), resultSet.getString(3), resultSet.getString(4)));
			}
		} finally {
			close(resultSet, preparedStatement);
		}
	}

	public static List<LabelMasterDAO> labelRows(Connection connection, String shopId, String shopCode) throws SQLException {
		List<LabelMasterDAO> rows = new ArrayList<LabelMasterDAO>();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			String sql = "SELECT label_id, label_name, label_details, label_code, customer_id, customer_name "
					+ "FROM shop_label_master WHERE shop_id = ? AND shop_code = ? ORDER BY label_name";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, shopId);
			preparedStatement.setString(2, shopCode);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				LabelMasterDAO dao = new LabelMasterDAO();
				dao.setLabelId(resultSet.getString(1));
				dao.setLabelName(resultSet.getString(2));
				dao.setLabelDetails(resultSet.getString(3));
				dao.setLabelCode(resultSet.getString(4));
				dao.setCustomerId(resultSet.getString(5));
				dao.setCustomerName(resultSet.getString(6));
				rows.add(dao);
			}
		} finally {
			close(resultSet, preparedStatement);
		}
		return rows;
	}

	public static List<CapMasterDAO> capRows(Connection connection, String shopId, String shopCode) throws SQLException {
		List<CapMasterDAO> rows = new ArrayList<CapMasterDAO>();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			String sql = "SELECT cap_id, cap_name, cap_details, cap_code, customer_id, customer_name "
					+ "FROM shop_cap_master WHERE shop_id = ? AND shop_code = ? ORDER BY cap_name";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, shopId);
			preparedStatement.setString(2, shopCode);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				CapMasterDAO dao = new CapMasterDAO();
				dao.setCapId(resultSet.getString(1));
				dao.setCapName(resultSet.getString(2));
				dao.setCapDetails(resultSet.getString(3));
				dao.setCapCode(resultSet.getString(4));
				dao.setCustomerId(resultSet.getString(5));
				dao.setCustomerName(resultSet.getString(6));
				rows.add(dao);
			}
		} finally {
			close(resultSet, preparedStatement);
		}
		return rows;
	}

	public static boolean labelCodeExists(Connection connection, String shopId, String shopCode, String labelCode) throws SQLException {
		return codeExists(connection, "shop_label_master", "label_code", shopId, shopCode, labelCode);
	}

	public static boolean capCodeExists(Connection connection, String shopId, String shopCode, String capCode) throws SQLException {
		return codeExists(connection, "shop_cap_master", "cap_code", shopId, shopCode, capCode);
	}

	private static boolean codeExists(Connection connection, String tableName, String columnName, String shopId, String shopCode, String code) throws SQLException {
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			String sql = "SELECT " + columnName + " FROM " + tableName + " WHERE " + columnName + " = ? AND shop_id = ? AND shop_code = ?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, code);
			preparedStatement.setString(2, shopId);
			preparedStatement.setString(3, shopCode);
			resultSet = preparedStatement.executeQuery();
			return resultSet.next();
		} finally {
			close(resultSet, preparedStatement);
		}
	}

	public static String displayName(String name, String code) {
		String safeName = name == null ? "" : name;
		String safeCode = code == null ? "" : code;
		if (safeName.equals("") && safeCode.equals("")) {
			return "";
		}
		if (safeCode.equals("")) {
			return safeName;
		}
		if (safeName.equals("")) {
			return safeCode;
		}
		return safeName + " (" + safeCode + ")";
	}

	private static String display(String name, String code, String customer) {
		String text = displayName(name, code);
		if (customer != null && !customer.trim().equals("")) {
			text = text + " - " + customer;
		}
		return text;
	}

	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) {
			}
		}
	}
}
