package com.reallinkbilling.add;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpSession;

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

import com.opensymphony.xwork2.ActionSupport;
import com.reallinkbilling.DAO.BillInformationDAO;
import com.reallinkbilling.DAO.BillProductInfoDAO;
import com.reallinkbilling.db.GetDBConnection;
import com.reallinkbilling.resource.GetListResource;
import com.reallinkbilling.update.UpdateProductStockDetails;
import com.reallinkbilling.utils.GetAccountListForDrCr;

public class SaveCompositeBill extends ActionSupport implements SessionAware {

	private static final long serialVersionUID = 1L;
	private static final String COMPOSITE_BILL_TYPE = "COMPOSITE-BILL";
	private static final String SUB_LIST_SESSION_KEY = "compositeSubProductList";
	private static final String MAIN_PRODUCT_TABLE = "shop_bill_product_info_master";
	private static final String SUB_PRODUCT_TABLE = "shop_bill_sub_product_info_master";

	private HttpSession session;
	private SessionMap<String, Object> sessionMap;
	private BillInformationDAO billInfoDAO;
	private List<BillProductInfoDAO> productInfoList;
	private List<BillProductInfoDAO> deletedProductInfoList;

	private Map<String, String> taxList = new LinkedHashMap<String, String>();
	private Map<String, String> unitList = new LinkedHashMap<String, String>();
	private Map<String, String> paymentTypeList = new LinkedHashMap<String, String>();
	private Map<String, String> rateTypeList = new LinkedHashMap<String, String>();
	private Map<String, String> accountList = new LinkedHashMap<String, String>();
	private ArrayList<String> customerTypeList = new ArrayList<String>();
	private List<String> paymentTypeList2 = new ArrayList<String>();

	private String defaultValue;
	private String billType;
	private String customerName;
	private String customerId;
	private String customerIdNameAll;
	private String customerMobileNumber;
	private String customerType;
	private String billNumber;
	private String billDateTime;
	private String totalAmount;
	private String totalDiscount;
	private String totalAmouAfterDisount;
	private String totalPaybleAmount;
	private Float amountPaid;
	private Float transportCharge;
	private String paymentType;
	private String returnAmount;
	private String billSave;
	private boolean billSaved;
	private String comments;
	private String rateType;
	private Float cashAmount;
	private Float bankRtgsUpiAmount;
	private Float directDiscountAmount;
	private String modeOfPayment;
	private String accountId;
	private String buyerOrderNumber;
	private String dispatchDocNumber;
	private String vehicleNumber;
	private boolean sendDirectSMS;
	private String lastBillNumber;

	@SuppressWarnings("unchecked")
	@Override
	synchronized public String execute() {
		String result = LOGIN;
		session = ServletActionContext.getRequest().getSession(false);
		if (session == null || session.getAttribute("login") == null) {
			addActionMessage("Please Login First !!!");
			return LOGIN;
		}

		this.billInfoDAO = (BillInformationDAO) this.sessionMap.get("billInformation");
		this.productInfoList = (List<BillProductInfoDAO>) this.sessionMap.get("productList");
		this.deletedProductInfoList = (List<BillProductInfoDAO>) this.sessionMap.get("deletedProductList");

		if (billInfoDAO == null || productInfoList == null || productInfoList.isEmpty()) {
			addActionError("Bill session expired or no product available. Please add product again.");
			prepareUiState();
			return ERROR;
		}
		if (!COMPOSITE_BILL_TYPE.equals(billInfoDAO.getBillType())) {
			addActionError("Invalid bill type for composite save.");
			prepareUiState();
			return ERROR;
		}

		applyRequestValuesToBillInfo();
		normalizeBillDefaults();
		float[] totals = calculateTotals();
		float totalAmountVal = totals[0];
		float totalDiscountVal = totals[1];
		float totalAfterDiscountVal = totals[2];

		if (amountPaid == null) {
			addActionError("Amount Paid Null ! Bill Cannot be Saved !");
		}
		if (returnAmount == null || returnAmount.trim().isEmpty()) {
			addActionError("Return amount Null ! Bill Cannot be Saved !");
		}
		if (accountId == null || accountId.trim().isEmpty()) {
			addActionError("Please select account for Credit entry!");
		}
		if (transportCharge == null || transportCharge < 0) {
			addActionError("Please provide correct transportaion amount!");
		}
		if (hasActionErrors()) {
			prepareUiState();
			return ERROR;
		}

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		try {
			String db = (String) this.sessionMap.get("db");
			String shopId = (String) this.sessionMap.get("shopId");
			String shopCode = (String) this.sessionMap.get("shopCode");
			System.out.println("[SaveCompositeBill] START save. billType=" + billInfoDAO.getBillType()
					+ ", billNo=" + billInfoDAO.getBillNumber()
					+ ", billInfoInserted=" + billInfoDAO.isBillInformationInserted()
					+ ", oldCustId=" + billInfoDAO.getOldCustomerId()
					+ ", oldBillDate=" + billInfoDAO.getOldBillDate()
					+ ", custId=" + billInfoDAO.getCustId()
					+ ", billDate=" + billInfoDAO.getBillDate()
					+ ", shopId=" + shopId + ", shopCode=" + shopCode);

			connection = GetDBConnection.getDBConnection(db);
			connection.setAutoCommit(false);

			assignBillNumberIfRequired(connection, shopId, shopCode);
			upsertBillHeader(connection, shopId, shopCode, totalAfterDiscountVal);
			refreshMainProductRows(connection, shopId, shopCode);
			refreshSubProductRows(connection, shopId, shopCode);

			connection.commit();

			billInfoDAO.setBillSaved(true);
			billInfoDAO.setBillInformationInserted(true);
			this.billSaved = true;
			this.billSave = "true";

			this.sessionMap.put("billInformation", billInfoDAO);
			this.sessionMap.put("productList", productInfoList);
			this.sessionMap.put(SUB_LIST_SESSION_KEY, getSubProductsFromSession());
			this.sessionMap.remove("deletedProductList");

			addActionMessage("Composite bill saved successfully.");
			System.out.println("[SaveCompositeBill] SUCCESS save. billNo=" + billInfoDAO.getBillNumber());
			result = SUCCESS;
		} catch (Exception e) {
			
			try {
				if (connection != null) {
					connection.rollback();
				}
			} catch (SQLException ignored) {
			}
			this.billSave = "false";
			System.out.println("[SaveCompositeBill] ERROR save. billNo=" + (billInfoDAO != null ? billInfoDAO.getBillNumber() : "NA")
					+ ", message=" + e.getMessage());
			e.printStackTrace();
			addActionError("Bill details not saved! Please try again! " + e.getMessage());
			result = ERROR;
		} finally {
			try {
				if (resultSet != null) {
					resultSet.close();
				}
			} catch (SQLException ignored) {
			}
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
			} catch (SQLException ignored) {
			}
			try {
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException ignored) {
			}
		}

		prepareUiState();
		return result;
	}

	private void applyRequestValuesToBillInfo() {
		if (this.transportCharge == null) {
			this.transportCharge = 0.0F;
		}
		if (this.directDiscountAmount == null) {
			this.directDiscountAmount = 0.0F;
		}
		if (this.cashAmount == null) {
			this.cashAmount = 0.0F;
		}
		if (this.bankRtgsUpiAmount == null) {
			this.bankRtgsUpiAmount = 0.0F;
		}

		this.billInfoDAO.setTransportCharge(this.transportCharge);
		this.billInfoDAO.setDirectDiscountAmount(this.directDiscountAmount);
		this.billInfoDAO.setComments(this.comments == null ? "" : this.comments);
		this.billInfoDAO.setBuyerOrderNumber(this.buyerOrderNumber);
		this.billInfoDAO.setDispatchDocNumber(this.dispatchDocNumber);
		this.billInfoDAO.setVehicleNumber(this.vehicleNumber);
	}

	private void normalizeBillDefaults() {
		if (billInfoDAO.getBillDate() == null || billInfoDAO.getBillDate().trim().isEmpty()) {
			billInfoDAO.setBillDate(new SimpleDateFormat("dd-MM-yyyy").format(new Date()));
		}
		if (billInfoDAO.getDirectDiscountAmount() == null) {
			billInfoDAO.setDirectDiscountAmount(0.0F);
		}
		if (billInfoDAO.getTransportCharge() == null) {
			billInfoDAO.setTransportCharge(0.0F);
		}
		if (buyerOrderNumber == null || buyerOrderNumber.trim().isEmpty()) {
			buyerOrderNumber = "NA";
		}
		if (dispatchDocNumber == null || dispatchDocNumber.trim().isEmpty()) {
			dispatchDocNumber = "NA";
		}
		if (vehicleNumber == null || vehicleNumber.trim().isEmpty()) {
			vehicleNumber = "NA";
		}
		this.billInfoDAO.setBuyerOrderNumber(buyerOrderNumber);
		this.billInfoDAO.setDispatchDocNumber(dispatchDocNumber);
		this.billInfoDAO.setVehicleNumber(vehicleNumber);
		if (this.billInfoDAO.getOldCustomerId() == null || this.billInfoDAO.getOldCustomerId().trim().isEmpty()) {
			this.billInfoDAO.setOldCustomerId(this.billInfoDAO.getCustId());
		}
		if (this.billInfoDAO.getOldBillDate() == null || this.billInfoDAO.getOldBillDate().trim().isEmpty()) {
			this.billInfoDAO.setOldBillDate(this.billInfoDAO.getBillDate());
		}
	}

	private float[] calculateTotals() {
		float totalAmountVal = 0.0F;
		float totalDiscountVal = 0.0F;
		float totalAfterDiscountVal = 0.0F;
		for (BillProductInfoDAO infoDAO : this.productInfoList) {
			totalAmountVal += getSafeFloat(infoDAO.getAmount());
			totalDiscountVal += getSafeFloat(infoDAO.getProductTotalDiscount());
			totalAfterDiscountVal += getSafeFloat(infoDAO.getAmountAfterDiscount());
		}
		totalDiscountVal += getSafeFloat(this.billInfoDAO.getDirectDiscountAmount());
		totalAfterDiscountVal -= getSafeFloat(this.billInfoDAO.getDirectDiscountAmount());
		totalAfterDiscountVal += getSafeFloat(this.billInfoDAO.getTransportCharge());

		this.totalAmount = String.valueOf(totalAmountVal + getSafeFloat(this.billInfoDAO.getTransportCharge()));
		this.totalDiscount = String.valueOf(totalDiscountVal);
		this.totalAmouAfterDisount = String.valueOf(totalAfterDiscountVal);
		this.totalPaybleAmount = String.valueOf(totalAfterDiscountVal);
		return new float[] { totalAmountVal, totalDiscountVal, totalAfterDiscountVal };
	}

	private void assignBillNumberIfRequired(Connection connection, String shopId, String shopCode) throws SQLException {
		// Never generate a new bill number for edited/already inserted bills.
		
		System.out.println("Real Link : Bill Info insterd : "+billInfoDAO.isBillInformationInserted());
		if (billInfoDAO.isBillInformationInserted()) {
			return;
		}
		if (billInfoDAO.getBillNumber() != null && !billInfoDAO.getBillNumber().equalsIgnoreCase("NA")) {
			return;
		}
		String sql = "SELECT bill_number FROM shop_bill_info_master WHERE bill_type <> 'TAX-INVOICE' AND shop_id = ? AND shop_code = ? AND bill_date >= ?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = connection.prepareStatement(sql);
			ps.setString(1, shopId);
			ps.setString(2, shopCode);
			ps.setString(3, (String) this.sessionMap.get("financialyearstartdate"));
			rs = ps.executeQuery();
			ArrayList<Integer> billNumbers = new ArrayList<Integer>();
			while (rs.next()) {
				try {
					billNumbers.add(Integer.parseInt(rs.getString(1).split("-")[1]));
				} catch (Exception e) {
					billNumbers.add(Integer.parseInt(rs.getString(1)));
				}
			}
			Collections.sort(billNumbers);
			if (billNumbers.size() > 0) {
				billInfoDAO.setBillNumber(String.valueOf(billNumbers.get(billNumbers.size() - 1) + 1));
			} else {
				billInfoDAO.setBillNumber("1");
			}
		} finally {
			if (rs != null) {
				rs.close();
			}
			if (ps != null) {
				ps.close();
			}
		}
	}

	private void upsertBillHeader(Connection connection, String shopId, String shopCode, float totalAfterDiscountVal) throws SQLException {
		Time time = new Time(System.currentTimeMillis());
		String sql;
		PreparedStatement ps = null;
		ResultSet rs = null;

		int countBillInfoInserted = 0;
		int countBillInfoUpdated = 0;

		try {
			if (!billInfoDAO.isBillInformationInserted()) {
				System.out.println("[SaveCompositeBill] Header path=INSERT for billNo=" + billInfoDAO.getBillNumber());
				sql = "INSERT INTO shop_bill_info_master "
						+ "(shop_id,shop_code,bill_number,bill_time,bill_date,payment_type,bill_type,customer_id,customer_name,"
						+ "transportation_charges,comments,bill_total_amount,bill_amount_received,bill_amount_retrurn,billed_by,"
						+ "cash_amount,bank_rtgs_upi_amount,direct_discount_amount,buyer_order_number,dispatch_doc_number,vehicle_number) "
						+ "VALUES (?,?,?,?,STR_TO_DATE(?, '%d-%m-%Y'),?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
				ps = connection.prepareStatement(sql);
				ps.setString(1, shopId);
				ps.setString(2, shopCode);
				ps.setString(3, billInfoDAO.getBillNumber());
				ps.setTime(4, time);
				ps.setString(5, billInfoDAO.getBillDate());
				ps.setString(6, billInfoDAO.getPaymentType());
				ps.setString(7, billInfoDAO.getBillType());
				ps.setString(8, billInfoDAO.getCustId());
				ps.setString(9, billInfoDAO.getCustId() + "-" + billInfoDAO.getCustomerName() + "-" + billInfoDAO.getCustomerMobile() + "-" + billInfoDAO.getCustomerType());
				ps.setFloat(10, getSafeFloat(billInfoDAO.getTransportCharge()));
				ps.setString(11, billInfoDAO.getComments());
				ps.setFloat(12, totalAfterDiscountVal);
				ps.setFloat(13, getSafeFloat(amountPaid));
				ps.setString(14, returnAmount);
				ps.setString(15, (String) this.sessionMap.get("userFullName"));
				ps.setFloat(16, getSafeFloat(cashAmount));
				ps.setFloat(17, getSafeFloat(bankRtgsUpiAmount));
				ps.setFloat(18, getSafeFloat(billInfoDAO.getDirectDiscountAmount()));
				ps.setString(19, buyerOrderNumber.trim().toUpperCase());
				ps.setString(20, dispatchDocNumber.trim().toUpperCase());
				ps.setString(21, vehicleNumber.trim().toUpperCase());
				countBillInfoInserted = ps.executeUpdate();
				System.out.println("[SaveCompositeBill] Header INSERT count=" + countBillInfoInserted);
			} else {
				System.out.println("[SaveCompositeBill] Header path=UPDATE for edited bill. billNo=" + billInfoDAO.getBillNumber()
						+ ", oldCustId=" + billInfoDAO.getOldCustomerId()
						+ ", oldBillDate=" + billInfoDAO.getOldBillDate()
						+ ", newCustId=" + billInfoDAO.getCustId()
						+ ", newBillDate=" + billInfoDAO.getBillDate());
				sql = "UPDATE shop_bill_info_master SET bill_time=?,bill_date=STR_TO_DATE(?,'%d-%m-%Y'),payment_type=?,customer_id=?,"
						+ "customer_name=?,transportation_charges=?,comments=?,bill_total_amount=?,bill_amount_received=?,bill_amount_retrurn=?,"
						+ "cash_amount=?,bank_rtgs_upi_amount=?,direct_discount_amount=?,buyer_order_number=?,dispatch_doc_number=?,vehicle_number=? "
						+ "WHERE bill_type=? AND bill_number=? AND customer_id=? AND bill_date=STR_TO_DATE(?,'%d-%m-%Y') AND shop_id=? AND shop_code=?";
				ps = connection.prepareStatement(sql);
				ps.setTime(1, time);
				ps.setString(2, billInfoDAO.getBillDate());
				ps.setString(3, billInfoDAO.getPaymentType());
				ps.setString(4, billInfoDAO.getCustId());
				ps.setString(5, billInfoDAO.getCustId() + "-" + billInfoDAO.getCustomerName() + "-" + billInfoDAO.getCustomerMobile() + "-" + billInfoDAO.getCustomerType());
				ps.setFloat(6, getSafeFloat(billInfoDAO.getTransportCharge()));
				ps.setString(7, billInfoDAO.getComments());
				ps.setFloat(8, totalAfterDiscountVal);
				ps.setFloat(9, getSafeFloat(amountPaid));
				ps.setString(10, returnAmount);
				ps.setFloat(11, getSafeFloat(cashAmount));
				ps.setFloat(12, getSafeFloat(bankRtgsUpiAmount));
				ps.setFloat(13, getSafeFloat(billInfoDAO.getDirectDiscountAmount()));
				ps.setString(14, buyerOrderNumber.trim().toUpperCase());
				ps.setString(15, dispatchDocNumber.trim().toUpperCase());
				ps.setString(16, vehicleNumber.trim().toUpperCase());
				ps.setString(17, billInfoDAO.getBillType());
				ps.setString(18, billInfoDAO.getBillNumber());
				ps.setString(19, billInfoDAO.getOldCustomerId());
				ps.setString(20, billInfoDAO.getOldBillDate());
				ps.setString(21, shopId);
				ps.setString(22, shopCode);
				countBillInfoUpdated = ps.executeUpdate();
				System.out.println("[SaveCompositeBill] Header UPDATE strict count=" + countBillInfoUpdated);

				if (countBillInfoUpdated == 0) {
					System.out.println("[SaveCompositeBill] Header strict update returned 0. Trying fallback update by bill keys only.");
					ps.close();
					sql = "UPDATE shop_bill_info_master SET bill_time=?,bill_date=STR_TO_DATE(?,'%d-%m-%Y'),payment_type=?,customer_id=?,"
							+ "customer_name=?,transportation_charges=?,comments=?,bill_total_amount=?,bill_amount_received=?,bill_amount_retrurn=?,"
							+ "cash_amount=?,bank_rtgs_upi_amount=?,direct_discount_amount=?,buyer_order_number=?,dispatch_doc_number=?,vehicle_number=? "
							+ "WHERE bill_type=? AND bill_number=? AND shop_id=? AND shop_code=?";
					ps = connection.prepareStatement(sql);
					ps.setTime(1, time);
					ps.setString(2, billInfoDAO.getBillDate());
					ps.setString(3, billInfoDAO.getPaymentType());
					ps.setString(4, billInfoDAO.getCustId());
					ps.setString(5, billInfoDAO.getCustId() + "-" + billInfoDAO.getCustomerName() + "-" + billInfoDAO.getCustomerMobile() + "-" + billInfoDAO.getCustomerType());
					ps.setFloat(6, getSafeFloat(billInfoDAO.getTransportCharge()));
					ps.setString(7, billInfoDAO.getComments());
					ps.setFloat(8, totalAfterDiscountVal);
					ps.setFloat(9, getSafeFloat(amountPaid));
					ps.setString(10, returnAmount);
					ps.setFloat(11, getSafeFloat(cashAmount));
					ps.setFloat(12, getSafeFloat(bankRtgsUpiAmount));
					ps.setFloat(13, getSafeFloat(billInfoDAO.getDirectDiscountAmount()));
					ps.setString(14, buyerOrderNumber.trim().toUpperCase());
					ps.setString(15, dispatchDocNumber.trim().toUpperCase());
					ps.setString(16, vehicleNumber.trim().toUpperCase());
					ps.setString(17, billInfoDAO.getBillType());
					ps.setString(18, billInfoDAO.getBillNumber());
					ps.setString(19, shopId);
					ps.setString(20, shopCode);
					countBillInfoUpdated = ps.executeUpdate();
					System.out.println("[SaveCompositeBill] Header UPDATE fallback count=" + countBillInfoUpdated);
					
					if (countBillInfoUpdated == 0) {
						System.out.println("[SaveCompositeBill] Header fallback still 0. Trying relaxed update by billNumber+shop (ignoring old billType mismatch).");
						ps.close();
						sql = "UPDATE shop_bill_info_master SET bill_time=?,bill_date=STR_TO_DATE(?,'%d-%m-%Y'),payment_type=?,customer_id=?,"
								+ "customer_name=?,transportation_charges=?,comments=?,bill_total_amount=?,bill_amount_received=?,bill_amount_retrurn=?,"
								+ "cash_amount=?,bank_rtgs_upi_amount=?,direct_discount_amount=?,buyer_order_number=?,dispatch_doc_number=?,vehicle_number=?,bill_type=? "
								+ "WHERE TRIM(CAST(bill_number AS CHAR)) = TRIM(?) AND shop_id=? AND shop_code=?";
						ps = connection.prepareStatement(sql);
						ps.setTime(1, time);
						ps.setString(2, billInfoDAO.getBillDate());
						ps.setString(3, billInfoDAO.getPaymentType());
						ps.setString(4, billInfoDAO.getCustId());
						ps.setString(5, billInfoDAO.getCustId() + "-" + billInfoDAO.getCustomerName() + "-" + billInfoDAO.getCustomerMobile() + "-" + billInfoDAO.getCustomerType());
						ps.setFloat(6, getSafeFloat(billInfoDAO.getTransportCharge()));
						ps.setString(7, billInfoDAO.getComments());
						ps.setFloat(8, totalAfterDiscountVal);
						ps.setFloat(9, getSafeFloat(amountPaid));
						ps.setString(10, returnAmount);
						ps.setFloat(11, getSafeFloat(cashAmount));
						ps.setFloat(12, getSafeFloat(bankRtgsUpiAmount));
						ps.setFloat(13, getSafeFloat(billInfoDAO.getDirectDiscountAmount()));
						ps.setString(14, buyerOrderNumber.trim().toUpperCase());
						ps.setString(15, dispatchDocNumber.trim().toUpperCase());
						ps.setString(16, vehicleNumber.trim().toUpperCase());
						ps.setString(17, billInfoDAO.getBillType());
						ps.setString(18, billInfoDAO.getBillNumber());
						ps.setString(19, shopId);
						ps.setString(20, shopCode);
						countBillInfoUpdated = ps.executeUpdate();
						System.out.println("[SaveCompositeBill] Header UPDATE relaxed count=" + countBillInfoUpdated);
					}
				}
			}

			// Final fallbacks: align with BillSave behavior for edited bills.
			if (countBillInfoInserted == 0 && countBillInfoUpdated == 0) {
				System.out.println("[SaveCompositeBill] Header counts still zero. Checking existing header/product rows as final fallback.");
				if (ps != null) {
					ps.close();
					ps = null;
				}

				sql = "SELECT 1 FROM shop_bill_info_master "
						+ "WHERE bill_type = ? AND bill_number = ? AND shop_id = ? AND shop_code = ? LIMIT 1";
				ps = connection.prepareStatement(sql);
				ps.setString(1, billInfoDAO.getBillType());
				ps.setString(2, billInfoDAO.getBillNumber());
				ps.setString(3, shopId);
				ps.setString(4, shopCode);
				rs = ps.executeQuery();
				if (rs.next()) {
					countBillInfoUpdated = 1;
					System.out.println("[SaveCompositeBill] Final fallback matched existing bill header row.");
				}
				if (rs != null) {
					rs.close();
					rs = null;
				}

				if (countBillInfoUpdated == 0) {
					System.out.println("[SaveCompositeBill] Header exact existence not found. Trying relaxed existence by billNumber+shop.");
					if (ps != null) {
						ps.close();
						ps = null;
					}
					sql = "SELECT 1 FROM shop_bill_info_master "
							+ "WHERE TRIM(CAST(bill_number AS CHAR)) = TRIM(?) AND shop_id = ? AND shop_code = ? LIMIT 1";
					ps = connection.prepareStatement(sql);
					ps.setString(1, billInfoDAO.getBillNumber());
					ps.setString(2, shopId);
					ps.setString(3, shopCode);
					rs = ps.executeQuery();
					if (rs.next()) {
						countBillInfoUpdated = 1;
						System.out.println("[SaveCompositeBill] Final fallback matched relaxed bill header row.");
					}
					if (rs != null) {
						rs.close();
						rs = null;
					}
				}

				if (countBillInfoUpdated == 0) {
					if (ps != null) {
						ps.close();
						ps = null;
					}
					sql = "SELECT 1 FROM " + MAIN_PRODUCT_TABLE + " "
							+ "WHERE bill_type = ? AND bill_number = ? AND shop_id = ? AND shop_code = ? LIMIT 1";
					ps = connection.prepareStatement(sql);
					ps.setString(1, billInfoDAO.getBillType());
					ps.setString(2, billInfoDAO.getBillNumber());
					ps.setString(3, shopId);
					ps.setString(4, shopCode);
					rs = ps.executeQuery();
					if (rs.next()) {
						countBillInfoUpdated = 1;
						System.out.println("[SaveCompositeBill] Final fallback matched existing main product rows.");
					}
					if (rs != null) {
						rs.close();
						rs = null;
					}
				}
			}

			// Deterministic edited-bill fallback:
			// If bill was already saved earlier and no header row matches, insert header with same bill number.
			// This keeps bill number stable and ensures edited totals are actually saved in header.
			if (countBillInfoInserted == 0 && countBillInfoUpdated == 0 && billInfoDAO.isBillInformationInserted()) {
				System.out.println("[SaveCompositeBill] Header unresolved for edited bill. Trying insert-upsert with same bill number. billNo="
						+ billInfoDAO.getBillNumber());
				if (ps != null) {
					ps.close();
					ps = null;
				}

				sql = "INSERT INTO shop_bill_info_master "
						+ "(shop_id,shop_code,bill_number,bill_time,bill_date,payment_type,bill_type,customer_id,customer_name,"
						+ "transportation_charges,comments,bill_total_amount,bill_amount_received,bill_amount_retrurn,billed_by,"
						+ "cash_amount,bank_rtgs_upi_amount,direct_discount_amount,buyer_order_number,dispatch_doc_number,vehicle_number) "
						+ "VALUES (?,?,?,?,STR_TO_DATE(?, '%d-%m-%Y'),?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
				ps = connection.prepareStatement(sql);
				ps.setString(1, shopId);
				ps.setString(2, shopCode);
				ps.setString(3, billInfoDAO.getBillNumber());
				ps.setTime(4, time);
				ps.setString(5, billInfoDAO.getBillDate());
				ps.setString(6, billInfoDAO.getPaymentType());
				ps.setString(7, billInfoDAO.getBillType());
				ps.setString(8, billInfoDAO.getCustId());
				ps.setString(9, billInfoDAO.getCustId() + "-" + billInfoDAO.getCustomerName() + "-" + billInfoDAO.getCustomerMobile() + "-" + billInfoDAO.getCustomerType());
				ps.setFloat(10, getSafeFloat(billInfoDAO.getTransportCharge()));
				ps.setString(11, billInfoDAO.getComments());
				ps.setFloat(12, totalAfterDiscountVal);
				ps.setFloat(13, getSafeFloat(amountPaid));
				ps.setString(14, returnAmount);
				ps.setString(15, (String) this.sessionMap.get("userFullName"));
				ps.setFloat(16, getSafeFloat(cashAmount));
				ps.setFloat(17, getSafeFloat(bankRtgsUpiAmount));
				ps.setFloat(18, getSafeFloat(billInfoDAO.getDirectDiscountAmount()));
				ps.setString(19, buyerOrderNumber.trim().toUpperCase());
				ps.setString(20, dispatchDocNumber.trim().toUpperCase());
				ps.setString(21, vehicleNumber.trim().toUpperCase());
				countBillInfoInserted = ps.executeUpdate();
				System.out.println("[SaveCompositeBill] Header INSERT-upsert count=" + countBillInfoInserted);
			}
			System.out.println("[SaveCompositeBill] Header final counters: inserted=" + countBillInfoInserted + ", updated=" + countBillInfoUpdated);

			if (countBillInfoInserted == 0 && countBillInfoUpdated == 0) {
				throw new SQLException("Bill details not saved! Please try again!");
			}
		} finally {
			if (rs != null) {
				rs.close();
			}
			if (ps != null) {
				ps.close();
			}
		}
	}

	private void refreshMainProductRows(Connection connection, String shopId, String shopCode) throws SQLException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			System.out.println("[SaveCompositeBill] Refresh main products start. billNo=" + billInfoDAO.getBillNumber()
					+ ", listSize=" + (this.productInfoList == null ? 0 : this.productInfoList.size()));
			String fetchSql = "SELECT product_id,product_name,product_quantity,product_quantity_2 FROM " + MAIN_PRODUCT_TABLE
					+ " WHERE bill_type = ? AND bill_number = ? AND shop_id = ? AND shop_code = ?";
			ps = connection.prepareStatement(fetchSql);
			ps.setString(1, billInfoDAO.getBillType());
			ps.setString(2, billInfoDAO.getBillNumber());
			ps.setString(3, shopId);
			ps.setString(4, shopCode);
			rs = ps.executeQuery();
			while (rs.next()) {
				boolean stockAdded = UpdateProductStockDetails.addStockToCurrentStock(connection, this.sessionMap, rs.getString(1), rs.getFloat(3), rs.getFloat(4));
				if (!stockAdded) {
					throw new SQLException("Unable to revert old main product stock for: " + rs.getString(2));
				}
			}
			rs.close();
			ps.close();

			String deleteSql = "DELETE FROM " + MAIN_PRODUCT_TABLE + " WHERE bill_type = ? AND bill_number = ? AND shop_id = ? AND shop_code = ?";
			ps = connection.prepareStatement(deleteSql);
			ps.setString(1, billInfoDAO.getBillType());
			ps.setString(2, billInfoDAO.getBillNumber());
			ps.setString(3, shopId);
			ps.setString(4, shopCode);
			ps.executeUpdate();
			ps.close();

			int entryId = getNextEntryId(connection, MAIN_PRODUCT_TABLE, shopId, shopCode);
			String insertSql = "INSERT INTO " + MAIN_PRODUCT_TABLE
					+ " (entry_id,shop_id,shop_code,bill_number,bill_type,bill_date,product_id,product_name,sales_unit,id_tax_table,sales_rate,"
					+ "product_quantity,discount_percentage,discount_ruppees,product_total_amount,product_total_amount_aft_discount,product_net_amount,"
					+ "product_igst_amount,product_cgst_amount,product_sgst_amount,making_labor_charge,product_name_in_marathi,product_quantity_2,"
					+ "product_batch_id,product_expiry_date) VALUES (?,?,?,?,?,STR_TO_DATE(?,'%d-%m-%Y'),?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			ps = connection.prepareStatement(insertSql);
			for (BillProductInfoDAO product : this.productInfoList) {
				ps.setInt(1, entryId++);
				ps.setString(2, shopId);
				ps.setString(3, shopCode);
				ps.setString(4, billInfoDAO.getBillNumber());
				ps.setString(5, billInfoDAO.getBillType());
				ps.setString(6, billInfoDAO.getBillDate());
				ps.setString(7, product.getProductId());
				ps.setString(8, product.getProductName());
				ps.setString(9, product.getSalesUnit());
				ps.setString(10, product.getTaxId());
				ps.setFloat(11, getSafeFloat(product.getSalesRate()));
				ps.setFloat(12, getSafeFloat(product.getQuantity()));
				ps.setFloat(13, getSafeFloat(product.getDiscountPecentage()));
				ps.setFloat(14, getSafeFloat(product.getDiscountRupees()));
				ps.setFloat(15, getSafeFloat(product.getAmount()));
				ps.setFloat(16, getSafeFloat(product.getAmountAfterDiscount()));
				ps.setFloat(17, getSafeFloat(product.getGrossAmount()));
				ps.setFloat(18, getSafeFloat(product.getIgst()));
				ps.setFloat(19, getSafeFloat(product.getCgst()));
				ps.setFloat(20, getSafeFloat(product.getSgst()));
				ps.setFloat(21, getSafeFloat(product.getMakingLaborCharge()));
				ps.setString(22, product.getProductNameMarathi());
				ps.setFloat(23, getSafeFloat(product.getQuantity2()));
				ps.setString(24, product.getBatchId());
				ps.setString(25, product.getExpiryDate());
				ps.addBatch();
			}
			int[] rows = ps.executeBatch();
			System.out.println("[SaveCompositeBill] Main product insert batch size=" + rows.length);
			if (rows.length != this.productInfoList.size()) {
				throw new SQLException("Main product save failed.");
			}
			ps.close();

			for (BillProductInfoDAO product : this.productInfoList) {
				boolean stockMinus = UpdateProductStockDetails.minusStockToCurrentStock(
						connection, this.sessionMap, product.getProductId(), product.getProductName(), getSafeFloat(product.getQuantity()), getSafeFloat(product.getQuantity2()));
				if (!stockMinus) {
					throw new SQLException("Unable to reduce stock for main product: " + product.getProductName());
				}
			}
			System.out.println("[SaveCompositeBill] Refresh main products completed.");
		} finally {
			if (rs != null) {
				rs.close();
			}
			if (ps != null) {
				ps.close();
			}
		}
	}

	private void refreshSubProductRows(Connection connection, String shopId, String shopCode) throws SQLException {
		List<BillProductInfoDAO> subProducts = getSubProductsFromSession();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			System.out.println("[SaveCompositeBill] Refresh sub products start. billNo=" + billInfoDAO.getBillNumber()
					+ ", subListSize=" + (subProducts == null ? 0 : subProducts.size()));
			String fetchOldSql = "SELECT product_id,product_name,product_quantity,product_quantity_2 FROM " + SUB_PRODUCT_TABLE
					+ " WHERE shop_id = ? AND shop_code = ? AND bill_number = ? AND bill_type = ?";
			ps = connection.prepareStatement(fetchOldSql);
			ps.setString(1, shopId);
			ps.setString(2, shopCode);
			ps.setString(3, billInfoDAO.getBillNumber());
			ps.setString(4, COMPOSITE_BILL_TYPE);
			rs = ps.executeQuery();
			while (rs.next()) {
				boolean stockAdded = UpdateProductStockDetails.addStockToCurrentStock(connection, this.sessionMap, rs.getString(1), rs.getFloat(3), rs.getFloat(4));
				if (!stockAdded) {
					throw new SQLException("Unable to revert old sub product stock for: " + rs.getString(2));
				}
			}
			rs.close();
			ps.close();

			String deleteSql = "DELETE FROM " + SUB_PRODUCT_TABLE + " WHERE shop_id = ? AND shop_code = ? AND bill_number = ? AND bill_type = ?";
			ps = connection.prepareStatement(deleteSql);
			ps.setString(1, shopId);
			ps.setString(2, shopCode);
			ps.setString(3, billInfoDAO.getBillNumber());
			ps.setString(4, COMPOSITE_BILL_TYPE);
			ps.executeUpdate();
			ps.close();

			if (subProducts.isEmpty()) {
				return;
			}

			int entryId = getNextEntryId(connection, SUB_PRODUCT_TABLE, shopId, shopCode);
			String insertSql = "INSERT INTO " + SUB_PRODUCT_TABLE
					+ " (entry_id,shop_id,shop_code,bill_number,bill_type,bill_date,product_id,product_name,product_code,product_quantity,product_quantity_2)"
					+ " VALUES (?,?,?,?,?,STR_TO_DATE(?,'%d-%m-%Y'),?,?,?,?,?)";
			ps = connection.prepareStatement(insertSql);
			for (BillProductInfoDAO sub : subProducts) {
				ps.setInt(1, entryId++);
				ps.setString(2, shopId);
				ps.setString(3, shopCode);
				ps.setString(4, billInfoDAO.getBillNumber());
				ps.setString(5, COMPOSITE_BILL_TYPE);
				ps.setString(6, billInfoDAO.getBillDate());
				ps.setString(7, sub.getProductId());
				ps.setString(8, sub.getProductName());
				ps.setString(9, sub.getProductCode() == null ? "NA" : sub.getProductCode());
				ps.setFloat(10, getSafeFloat(sub.getQuantity()));
				ps.setFloat(11, getSafeFloat(sub.getQuantity2()));
				ps.addBatch();
			}
			int[] rows = ps.executeBatch();
			System.out.println("[SaveCompositeBill] Sub product insert batch size=" + rows.length);
			if (rows.length != subProducts.size()) {
				throw new SQLException("Sub products not saved properly.");
			}
			ps.close();

			for (BillProductInfoDAO sub : subProducts) {
				boolean stockMinus = UpdateProductStockDetails.minusStockToCurrentStock(
						connection, this.sessionMap, sub.getProductId(), sub.getProductName(), getSafeFloat(sub.getQuantity()), getSafeFloat(sub.getQuantity2()));
				if (!stockMinus) {
					throw new SQLException("Unable to reduce stock for sub product: " + sub.getProductName());
				}
			}
			System.out.println("[SaveCompositeBill] Refresh sub products completed.");
		} finally {
			if (rs != null) {
				rs.close();
			}
			if (ps != null) {
				ps.close();
			}
		}
	}

	@SuppressWarnings("unchecked")
	private List<BillProductInfoDAO> getSubProductsFromSession() {
		List<BillProductInfoDAO> list = (List<BillProductInfoDAO>) this.sessionMap.get(SUB_LIST_SESSION_KEY);
		return list == null ? new ArrayList<BillProductInfoDAO>() : list;
	}

	private int getNextEntryId(Connection connection, String tableName, String shopId, String shopCode) throws SQLException {
		String sql = "SELECT entry_id FROM " + tableName + " WHERE shop_id = ? AND shop_code = ? ORDER BY entry_id DESC LIMIT 1";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = connection.prepareStatement(sql);
			ps.setString(1, shopId);
			ps.setString(2, shopCode);
			rs = ps.executeQuery();
			if (rs.next() && rs.getString(1) != null) {
				return rs.getInt(1) + 1;
			}
			return 1;
		} finally {
			if (rs != null) {
				rs.close();
			}
			if (ps != null) {
				ps.close();
			}
		}
	}

	@SuppressWarnings("unchecked")
	private void prepareUiState() {
		try {
			this.taxList = GetListResource.GetTaxList(this.sessionMap);
		} catch (Exception e) {
			this.taxList = new LinkedHashMap<String, String>();
		}
		this.unitList = GetListResource.GetUnitList();
		this.paymentTypeList.put("CASH", "CASH");
		this.paymentTypeList.put("CREDIT", "CREDIT");
		this.rateTypeList.put("INCLUDEGST", "WITH GST");
		this.rateTypeList.put("EXCLUDEGST", "WITH-OUT GST");

		this.paymentTypeList2 = new ArrayList<String>();
		this.paymentTypeList2.add("CASH");
		this.paymentTypeList2.add("UPI");
		this.paymentTypeList2.add("RTGS");
		this.paymentTypeList2.add("NEFT");
		this.paymentTypeList2.add("CHEQUE");

		this.accountList = GetAccountListForDrCr.getAccountList(this.sessionMap);
		this.customerTypeList = GetListResource.getCustomerTypeList();

		if (this.billInfoDAO != null) {
			this.customerName = billInfoDAO.getCustomerName();
			this.customerId = billInfoDAO.getCustId();
			this.customerMobileNumber = billInfoDAO.getCustomerMobile();
			this.customerType = billInfoDAO.getCustomerType();
			this.billNumber = billInfoDAO.getBillNumber();
			this.billType = billInfoDAO.getBillType();
			this.billDateTime = billInfoDAO.getBillDate();
			this.defaultValue = billInfoDAO.getPaymentType();
			this.paymentType = billInfoDAO.getPaymentType();
			this.transportCharge = billInfoDAO.getTransportCharge();
			this.directDiscountAmount = billInfoDAO.getDirectDiscountAmount();
			this.comments = billInfoDAO.getComments();
			this.buyerOrderNumber = billInfoDAO.getBuyerOrderNumber();
			this.dispatchDocNumber = billInfoDAO.getDispatchDocNumber();
			this.vehicleNumber = billInfoDAO.getVehicleNumber();
		}
		if (this.productInfoList == null) {
			this.productInfoList = new ArrayList<BillProductInfoDAO>();
		}
		HashMap<String, String> settings = (HashMap<String, String>) this.sessionMap.get("shop_settings");
		if (settings != null) {
			this.rateType = settings.get("default_rate_type");
		}
	}

	private float getSafeFloat(Float value) {
		return value == null ? 0.0F : value;
	}

	@Override
	public void setSession(Map<String, Object> map) {
		this.sessionMap = (SessionMap<String, Object>) map;
	}

	public SessionMap<String, Object> getSessionMap() {
		return sessionMap;
	}
	public HttpSession getSession() { return session; }
	public void setSession(HttpSession session) { this.session = session; }
	public BillInformationDAO getBillInfoDAO() { return billInfoDAO; }
	public void setBillInfoDAO(BillInformationDAO billInfoDAO) { this.billInfoDAO = billInfoDAO; }
	public List<BillProductInfoDAO> getProductInfoList() { return productInfoList; }
	public void setProductInfoList(List<BillProductInfoDAO> productInfoList) { this.productInfoList = productInfoList; }
	public List<BillProductInfoDAO> getDeletedProductInfoList() { return deletedProductInfoList; }
	public void setDeletedProductInfoList(List<BillProductInfoDAO> deletedProductInfoList) { this.deletedProductInfoList = deletedProductInfoList; }
	public Map<String, String> getTaxList() { return taxList; }
	public void setTaxList(Map<String, String> taxList) { this.taxList = taxList; }
	public Map<String, String> getUnitList() { return unitList; }
	public void setUnitList(Map<String, String> unitList) { this.unitList = unitList; }
	public Map<String, String> getPaymentTypeList() { return paymentTypeList; }
	public void setPaymentTypeList(Map<String, String> paymentTypeList) { this.paymentTypeList = paymentTypeList; }
	public Map<String, String> getRateTypeList() { return rateTypeList; }
	public void setRateTypeList(Map<String, String> rateTypeList) { this.rateTypeList = rateTypeList; }
	public Map<String, String> getAccountList() { return accountList; }
	public void setAccountList(Map<String, String> accountList) { this.accountList = accountList; }
	public ArrayList<String> getCustomerTypeList() { return customerTypeList; }
	public void setCustomerTypeList(ArrayList<String> customerTypeList) { this.customerTypeList = customerTypeList; }
	public List<String> getPaymentTypeList2() { return paymentTypeList2; }
	public void setPaymentTypeList2(List<String> paymentTypeList2) { this.paymentTypeList2 = paymentTypeList2; }
	public String getDefaultValue() { return defaultValue; }
	public void setDefaultValue(String defaultValue) { this.defaultValue = defaultValue; }
	public String getBillType() { return billType; }
	public void setBillType(String billType) { this.billType = billType; }
	public String getCustomerName() { return customerName; }
	public void setCustomerName(String customerName) { this.customerName = customerName; }
	public String getCustomerId() { return customerId; }
	public void setCustomerId(String customerId) { this.customerId = customerId; }
	public String getCustomerIdNameAll() { return customerIdNameAll; }
	public void setCustomerIdNameAll(String customerIdNameAll) { this.customerIdNameAll = customerIdNameAll; }
	public String getCustomerMobileNumber() { return customerMobileNumber; }
	public void setCustomerMobileNumber(String customerMobileNumber) { this.customerMobileNumber = customerMobileNumber; }
	public String getCustomerType() { return customerType; }
	public void setCustomerType(String customerType) { this.customerType = customerType; }
	public String getBillNumber() { return billNumber; }
	public void setBillNumber(String billNumber) { this.billNumber = billNumber; }
	public String getBillDateTime() { return billDateTime; }
	public void setBillDateTime(String billDateTime) { this.billDateTime = billDateTime; }
	public String getTotalAmount() { return totalAmount; }
	public void setTotalAmount(String totalAmount) { this.totalAmount = totalAmount; }
	public String getTotalDiscount() { return totalDiscount; }
	public void setTotalDiscount(String totalDiscount) { this.totalDiscount = totalDiscount; }
	public String getTotalAmouAfterDisount() { return totalAmouAfterDisount; }
	public void setTotalAmouAfterDisount(String totalAmouAfterDisount) { this.totalAmouAfterDisount = totalAmouAfterDisount; }
	public String getTotalPaybleAmount() { return totalPaybleAmount; }
	public void setTotalPaybleAmount(String totalPaybleAmount) { this.totalPaybleAmount = totalPaybleAmount; }
	public Float getAmountPaid() { return amountPaid; }
	public void setAmountPaid(Float amountPaid) { this.amountPaid = amountPaid; }
	public Float getTransportCharge() { return transportCharge; }
	public void setTransportCharge(Float transportCharge) { this.transportCharge = transportCharge; }
	public String getPaymentType() { return paymentType; }
	public void setPaymentType(String paymentType) { this.paymentType = paymentType; }
	public String getReturnAmount() { return returnAmount; }
	public void setReturnAmount(String returnAmount) { this.returnAmount = returnAmount; }
	public String getBillSave() { return billSave; }
	public void setBillSave(String billSave) { this.billSave = billSave; }
	public boolean isBillSaved() { return billSaved; }
	public void setBillSaved(boolean billSaved) { this.billSaved = billSaved; }
	public String getComments() { return comments; }
	public void setComments(String comments) { this.comments = comments; }
	public String getRateType() { return rateType; }
	public void setRateType(String rateType) { this.rateType = rateType; }
	public Float getCashAmount() { return cashAmount; }
	public void setCashAmount(Float cashAmount) { this.cashAmount = cashAmount; }
	public Float getBankRtgsUpiAmount() { return bankRtgsUpiAmount; }
	public void setBankRtgsUpiAmount(Float bankRtgsUpiAmount) { this.bankRtgsUpiAmount = bankRtgsUpiAmount; }
	public Float getDirectDiscountAmount() { return directDiscountAmount; }
	public void setDirectDiscountAmount(Float directDiscountAmount) { this.directDiscountAmount = directDiscountAmount; }
	public String getModeOfPayment() { return modeOfPayment; }
	public void setModeOfPayment(String modeOfPayment) { this.modeOfPayment = modeOfPayment; }
	public String getAccountId() { return accountId; }
	public void setAccountId(String accountId) { this.accountId = accountId; }
	public String getBuyerOrderNumber() { return buyerOrderNumber; }
	public void setBuyerOrderNumber(String buyerOrderNumber) { this.buyerOrderNumber = buyerOrderNumber; }
	public String getDispatchDocNumber() { return dispatchDocNumber; }
	public void setDispatchDocNumber(String dispatchDocNumber) { this.dispatchDocNumber = dispatchDocNumber; }
	public String getVehicleNumber() { return vehicleNumber; }
	public void setVehicleNumber(String vehicleNumber) { this.vehicleNumber = vehicleNumber; }
	public boolean isSendDirectSMS() { return sendDirectSMS; }
	public void setSendDirectSMS(boolean sendDirectSMS) { this.sendDirectSMS = sendDirectSMS; }
	public String getLastBillNumber() { return lastBillNumber; }
	public void setLastBillNumber(String lastBillNumber) { this.lastBillNumber = lastBillNumber; }
}
