package main_package;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDate;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

//import org.json.simple.JSONObject;

import other_package.DealerInfo;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.HttpURLConnection;
import java.net.URL;
import java.util.ArrayList;
import javax.faces.model.SelectItem;

import org.primefaces.json.JSONArray;
import org.primefaces.json.JSONObject;

import HdfcEmandate.DebugLogger;
import HdfcEmandate.EmandateRequest;
import HdfcEmandate.RequestType;
import cashfree.LoanEmiSummary;
import other_package.DealerInfo;

public class DataBaseMethodsNaman implements Serializable
{	
	DataBaseMethods db=new DataBaseMethods();
	
	
	
	public void shareholderList()
	{
	    Connection conn = null;
	    PreparedStatement ps = null;
	    ResultSet rs = null;

	    try
	    {
	        conn = DataBaseConnection.JAVAConnection();

	        String query =
	                "SELECT shareholder_id, shareholder_no, shareholder_name, mobile_no, account_status " +
	                "FROM shareholder_info " +
	                "ORDER BY shareholder_name ASC";

	        ps = conn.prepareStatement(query);

	        rs = ps.executeQuery();

	        while(rs.next())
	        {
	            int id = rs.getInt("shareholder_id");
	            String no = rs.getString("shareholder_no");
	            String name = rs.getString("shareholder_name");
	            String mobile = rs.getString("mobile_no");
	            String status = rs.getString("account_status");

	            System.out.println(
	                    id + " | " +
	                    no + " | " +
	                    name + " | " +
	                    mobile + " | " +
	                    status
	            );
	        }
	    }
	    catch(Exception e)
	    {
	        e.printStackTrace();
	    }
	    finally
	    {
	        try
	        {
	            if(rs != null) rs.close();
	            if(ps != null) ps.close();
	            if(conn != null) conn.close();
	        }
	        catch(Exception e)
	        {
	            e.printStackTrace();
	        }
	    }
	}
	
	
	
	public void shareholderDetails(int shareholderId)
	{
	    Connection conn = null;
	    PreparedStatement ps = null;
	    ResultSet rs = null;

	    try
	    {
	        conn = DataBaseConnection.JAVAConnection();

	        String query = "SELECT * FROM shareholder_info WHERE shareholder_id=?";

	        ps = conn.prepareStatement(query);

	        ps.setInt(1, shareholderId);

	        rs = ps.executeQuery();

	        if(rs.next())
	        {
	            System.out.println("ID === " + rs.getInt("shareholder_id"));
	            System.out.println("NAME === " + rs.getString("shareholder_name"));
	            System.out.println("MOBILE === " + rs.getString("mobile_no"));
	            System.out.println("STATUS === " + rs.getString("account_status"));

	            // add all other fields
	        }
	    }
	    catch(Exception e)
	    {
	        e.printStackTrace();
	    }
	    finally
	    {
	        try
	        {
	            if(rs != null) rs.close();
	            if(ps != null) ps.close();
	            if(conn != null) conn.close();
	        }
	        catch(Exception e)
	        {
	            e.printStackTrace();
	        }
	    }
	}
	
	
	
	public void shareholderStatusCount(JSONObject obj, int branchId)
	{
	    Connection conn = null;
	    PreparedStatement ps = null;
	    ResultSet rs = null;

	    try
	    {
	        conn = DataBaseConnection.JAVAConnection();

	        String query = "SELECT COUNT(*) AS totalMember, " +
			               "SUM(CASE WHEN account_status='active' THEN 1 ELSE 0 END) AS activeMember, " +
			               "SUM(CASE WHEN account_status='inactive' THEN 1 ELSE 0 END) AS inactiveMember " +
			               "FROM shareholder_info";
	        
	        if (branchId != -1) 
	        {
	        	query += " WHERE branch_id = '"+ branchId +"'";
			}

	        ps = conn.prepareStatement(query);

	        rs = ps.executeQuery();

	        if(rs.next())
	        {
	            int total = rs.getInt("totalMember");
	            int active = rs.getInt("activeMember");
	            int inactive = rs.getInt("inactiveMember");

	            System.out.println("TOTAL MEMBER === " + total);
	            System.out.println("ACTIVE MEMBER === " + active);
	            System.out.println("INACTIVE MEMBER === " + inactive);
	        
	            obj.put("total", total);
		        obj.put("active", active);
		        obj.put("inactive", inactive);
		        obj.put("status", "success");
	        }
	    }
	    catch(Exception e)
	    {
	        e.printStackTrace();
	        
	        obj.put("status", "error");
            obj.put("message", e.getMessage());
	    }
	    finally
	    {
	        try
	        {
	            if(rs != null)
	            {
	                rs.close();
	            }

	            if(ps != null)
	            {
	                ps.close();
	            }

	            if(conn != null)
	            {
	                conn.close();
	            }
	        }
	        catch(Exception e)
	        {
	            e.printStackTrace();
	        }
	    }
	}
	
	

	public double recivedamountofrdbyrdnumber(String rdNumber, Connection con) {
	    double amount = 0.0;
	    ResultSet rs = null;
	    PreparedStatement ps = null;

	    try {
	        String sql = "SELECT COALESCE(SUM(amount), 0) AS total_amount " +
	                     "FROM payment_receipt " +
	                     "WHERE user_no = ? " +
	                     "AND scheme_type = 'RD' " +
	                     "AND receipt_status = 'approved'";

	        ps = con.prepareStatement(sql);
	        ps.setString(1, rdNumber);

	        rs = ps.executeQuery();

	        if (rs.next()) {
	            amount = rs.getDouble("total_amount");
	        }

	    } catch (Exception e) {
	        e.printStackTrace();
	    } finally {
	        try {
	            if (rs != null) rs.close();
	            if (ps != null) ps.close();
	        } catch (Exception e) {
	            // ignore
	        }
	    }
	    return amount;
	}
	
	public String findDealerIdBySlLoanNo(String loanNo, Connection conn) {
	    String dealerId = null;
	    String sql = "SELECT mob_dealer FROM secure_loan WHERE loanNo = ?";

	    try (PreparedStatement ps = conn.prepareStatement(sql)) {
	        ps.setString(1, loanNo);

	        try (ResultSet rs = ps.executeQuery()) {
	            if (rs.next()) {
	                dealerId = rs.getString("mob_dealer");
	            }
	        }
	    } catch (SQLException e) {
	        e.printStackTrace();
	    }
	    return dealerId;
	}
	
	public Map<String, String> findDealerBankDetailsByDealerId(
	        String dealerId, Connection conn) {

	    Map<String, String> bankDetails = new HashMap<>();

	    String sql = "SELECT bank_code, ifsc_code, account_number,email,name " +
	                 "FROM dealers WHERE id = ? AND status = 'active'";

	    try (PreparedStatement ps = conn.prepareStatement(sql)) {
	        ps.setString(1, dealerId);

	        try (ResultSet rs = ps.executeQuery()) {
	            if (rs.next()) {
	                bankDetails.put("bank_code", rs.getString("bank_code"));
	                bankDetails.put("ifsc_code", rs.getString("ifsc_code"));
	                bankDetails.put("account_number", rs.getString("account_number"));
	                bankDetails.put("email", rs.getString("email"));
	                bankDetails.put("name", rs.getString("name"));
	            }
	        }
	    } catch (SQLException e) {
	        e.printStackTrace();
	    }

	    return bankDetails;
	}
	
	public double getPaidRDInstallmentCount(String rdNumber, Connection con) {

	    double installmentCount = 0;
	    ResultSet rs = null;
	    PreparedStatement ps = null;

	    try {
	        String sql = "SELECT COUNT(*) AS installment_count " +
	                     "FROM payment_receipt " +
	                     "WHERE user_no = ? " +
	                     "AND scheme_type = 'RD' " +
	                     "AND receipt_status = 'approved'";

	        ps = con.prepareStatement(sql);
	        ps.setString(1, rdNumber);

	        rs = ps.executeQuery();

	        if (rs.next()) {
	            installmentCount = rs.getDouble("installment_count");
	        }

	    } catch (Exception e) {
	        e.printStackTrace();
	    } finally {
	        try {
	            if (rs != null) rs.close();
	            if (ps != null) ps.close();
	        } catch (Exception e) {
	            // ignore
	        }
	    }

	    return installmentCount;
	}

	
	public double getInterestRateOfRD(String shareHolderId) {
		
		double interestAmount = 0;
	
		try(Connection conn = DataBaseConnection.JAVAConnection()){
			
			String query = "select interest  from customer_info where shareHolderNo=?  and account_status='active'";
			
			try(PreparedStatement ps = conn.prepareStatement(query) ){
				
				ps.setString(1, shareHolderId);
				
				ResultSet rs = ps.executeQuery();
				
				if(rs.next()) {
					
					interestAmount= rs.getInt("interest");
					
				}
				
			}catch(Exception e) {
				
				e.printStackTrace();
				
			}
		
		}catch(Exception e) {
			
			e.printStackTrace();
			
		}
		
		 return interestAmount;
	
	}
	
	
	public String rdFdNoByShareHolderNo(String shareHolderNo, Connection conn) {

	    String rdFdNo = "";

	    String sql = "SELECT user_no FROM customer_info WHERE shareHolderNo = ?";

	    try (PreparedStatement ps = conn.prepareStatement(sql)) {

	        ps.setString(1, shareHolderNo);

	        try (ResultSet rs = ps.executeQuery()) {
	            if (rs.next()) {
	                rdFdNo = rs.getString("user_no");
	            }
	        }

	    } catch (Exception e) {
	        System.out.println("Error in rdFdNoByShareHolderNo()");
	        e.printStackTrace();
	    }

	    return rdFdNo;
	}

	public double getRdInstallmentAmount(String rdNumber,Connection conn) {
		
		Double rdInstallmentAmount=0.0;
		String sql = "SELECT installment_amount FROM customer_info WHERE user_no = ?";

	    try (PreparedStatement ps = conn.prepareStatement(sql)) {

	        ps.setString(1, rdNumber);

	        try (ResultSet rs = ps.executeQuery()) {
	            if (rs.next()) {
	                rdInstallmentAmount = rs.getDouble("installment_amount");
	            }
	        }

	    } catch (Exception e) {
	        System.out.println("Error in rdFdNoByShareHolderNo()");
	        e.printStackTrace();
	    }
		return rdInstallmentAmount;
	}
	
	
	public boolean getShowDepositStatus(Connection conn) {
			
			boolean depositStatus=false;
			String sql = "SELECT show_deposit FROM message_api";
	
		    try (PreparedStatement ps = conn.prepareStatement(sql)) {
	
		        try (ResultSet rs = ps.executeQuery()) {
		            if (rs.next()) {
		            	depositStatus = rs.getBoolean("show_deposit");
		            }
		        }
	
		    } catch (Exception e) {
		        System.out.println("Error in rdFdNoByShareHolderNo()");
		        e.printStackTrace();
		    }
			return depositStatus;
		}

	public LocalDate getRDStartDate(String rdNumber, Connection conn) {

	    LocalDate rdStartDate = null;

	    String sql = "SELECT office_date FROM customer_info WHERE user_no = ?";

	    try (PreparedStatement ps = conn.prepareStatement(sql)) {

	        ps.setString(1, rdNumber);

	        try (ResultSet rs = ps.executeQuery()) {

	            if (rs.next()) {
	                Date sqlDate = rs.getDate("office_date");
	                if (sqlDate != null) {
	                    rdStartDate = sqlDate.toLocalDate();
	                }
	            }
	        }

	    } catch (Exception e) {
	        System.out.println("Error in getRDStartDate()");
	        e.printStackTrace();
	    }

	    return rdStartDate;
	}

	public DealerInfo getSecureLoanMobileDealerInfo(String loanNo, Connection conn) {

	    String sql =
	        "SELECT d.name, d.mobile, st.security_type " +
	        "FROM secure_loan sl " +
	        "JOIN secure_type_table st ON st.id = sl.security_type " +
	        "JOIN dealers d ON d.id = sl.mob_dealer " +
	        "WHERE sl.loanNo = ? " +
	        "AND UPPER(st.security_type) LIKE '%MOBILE%'";

	    DealerInfo info = null;

	    try (PreparedStatement ps = conn.prepareStatement(sql)) {
	        ps.setString(1, loanNo);
	        ResultSet rs = ps.executeQuery();

	        if (rs.next()) {
	            info = new DealerInfo();

	            String fullName =
	                rs.getString("name") + " - " + rs.getString("mobile");

	            String shortName =
	                formatDealerShortName(rs.getString("name"), rs.getString("mobile"));

	            info.setFullName(fullName);
	            info.setShortName(shortName);
	        }
	    } catch (Exception e) {
	        e.printStackTrace();
	    }
	    return info;
	}
	
	public String getSecureLoanMobileDealerName(String loanNo, Connection conn) {

	    String sql =
	        "SELECT d.name, d.mobile, st.security_type " +
	        "FROM secure_loan sl " +
	        "JOIN secure_type_table st ON st.id = sl.security_type " +
	        "JOIN dealers d ON d.id = sl.mob_dealer " +
	        "WHERE sl.loanNo = ? " +
	        "AND UPPER(st.security_type) LIKE '%MOBILE%'";

	    String info = null;

	    try (PreparedStatement ps = conn.prepareStatement(sql)) {
	        ps.setString(1, loanNo);
	        ResultSet rs = ps.executeQuery();

	        if (rs.next()) {
	            info = null;

	           info =
	                rs.getString("name") + " - " + rs.getString("mobile");

	           
	           
	            
	        }
	    } catch (Exception e) {
	        e.printStackTrace();
	    }
	    return info;
	}

	
	
	private String formatDealerShortName(String name, String mobile) {

	    if (name == null) return null;

	    // Remove M/S or similar prefixes
	    name = name.replaceAll("(?i)M/S", "").trim();

	    // Remove extra spaces
	    name = name.replaceAll("\\s+", " ");

	    // Limit length for table
	    if (name.length() > 30) {
	        name = name.substring(0, 30).trim();
	    }

	    String maskedMobile =
	            mobile.substring(0, 3) + "XXXX" + mobile.substring(7);

	    return name + " - " + maskedMobile;
	}
	public String findLoanSecurityTypeByLoanNumber(String loanNo, Connection conn) {
	    String sql =
	        "SELECT st.security_type " +
	        "FROM secure_loan sl " +
	        "JOIN secure_type_table st ON st.id = sl.security_type " +
	        "WHERE sl.loanNo = ?";

	    try (PreparedStatement ps = conn.prepareStatement(sql)) {
	        ps.setString(1, loanNo);

	        try (ResultSet rs = ps.executeQuery()) {
	            if (rs.next()) {
	                return rs.getString("security_type");
	            }
	        }
	    } catch (SQLException e) {
	        // log properly in real code
	        e.printStackTrace();
	    }

	    return null; 
	}

	public Map<String, String> findCustomerBankDetailsByLoanNo(String loanNo, Connection conn) {

	    Map<String, String> bankDetails = new HashMap<>();

	    String sql =
	        "SELECT " +
	        "   COALESCE(pl.app_acc_no, si.bank_accountNo) AS account_number, " +
	        "   COALESCE(pl.app_bank_ifsc, si.bank_ifsc) AS ifsc_code, " +
	        "   COALESCE(pl.app_bank_name, si.bank_name) AS bank_code, " +
	        "   si.email AS email, " +
	        "   si.first_applicantName AS name " +
	        "FROM personalloan pl " +
	        "JOIN shareholder_info si ON si.shareholderno = pl.shareholderno " +
	        "WHERE pl.loanNo = ?";

	    try (PreparedStatement ps = conn.prepareStatement(sql)) {

	        ps.setString(1, loanNo);

	        try (ResultSet rs = ps.executeQuery()) {

	            if (rs.next()) {

	                bankDetails.put(
	                    "account_number",
	                    rs.getString("account_number") != null
	                        ? rs.getString("account_number").trim()
	                        : ""
	                );

	                bankDetails.put(
	                    "ifsc_code",
	                    rs.getString("ifsc_code") != null
	                        ? rs.getString("ifsc_code").trim()
	                        : ""
	                );

	                bankDetails.put(
	                    "bank_code",
	                    rs.getString("bank_code") != null
	                        ? rs.getString("bank_code").trim()
	                        : ""
	                );

	                bankDetails.put(
	                    "email",
	                    rs.getString("email") != null
	                        ? rs.getString("email").trim()
	                        : ""
	                );

	                bankDetails.put(
	                    "name",
	                    rs.getString("name") != null
	                        ? rs.getString("name").trim()
	                        : ""
	                );
	            }
	        }

	    } catch (SQLException e) {
	        e.printStackTrace();
	    }

	    return bankDetails;
	}
	
	private static final String LIVE_BASE =
	        "https://enach.npci.org.in/apiservices/getLiveBankDtls";

	private static final String UAT_BASE =
	        "https://enachuat.npci.org.in:8086/apiservices_new/getLiveBankDtls";

	private static final Map<RequestType, String> PATH_MAP =
	        new HashMap<RequestType, String>();

	static
	{
	    PATH_MAP.put(RequestType.CREATE, "");
	    PATH_MAP.put(RequestType.AMEND, "/amend");
	    PATH_MAP.put(RequestType.CANCEL, "/cancel");
	    PATH_MAP.put(RequestType.SUSPEND, "/suspend");
	    PATH_MAP.put(RequestType.REVOKE, "/revoke");
	}
	public List<SelectItem> getNpcibankListFetch(boolean isLive, RequestType type) {

	    if (type == null) {
	        throw new IllegalArgumentException("RequestType cannot be null");
	    }

	    String urlString = buildUrl(isLive, type);
	    String response=null;
	    HttpURLConnection connection = null;

	    try {
	        URL url = new URL(urlString);
	        connection = (HttpURLConnection) url.openConnection();

	        connection.setRequestMethod("GET");
	        connection.setConnectTimeout(5000);
	        connection.setReadTimeout(15000);
	        connection.setRequestProperty("Accept", "application/json");

	        int status = connection.getResponseCode();

	        InputStream stream = (status >= 200 && status < 300)
	                ? connection.getInputStream()
	                : connection.getErrorStream();

	         response = readResponse(stream);

	        //  If API failed → fallback to DB
	        if (status < 200 || status >= 300 || isInvalidJson(response)) {
	            System.err.println("API failed, loading from DB backup...");
	         //   return fetchBankCodeFromDb();
	        }

	        //  Save fresh data
	      //  saveBankJsonToDb(response);

	        //  Parse and return
	        return parseBankList(response);

	    } catch (Exception e) {
	        System.err.println("Error calling NPCI API: " + e.getMessage());
	      //  return fetchBankCodeFromDb(); // fallback on exception
	    } finally {
	        if (connection != null) {
	            connection.disconnect();
	        }
	        
	    }
	    return parseBankList(response);
	    
	    
	}
	
	private String readResponse(InputStream stream) throws IOException {
	    StringBuilder response = new StringBuilder();

	    try (BufferedReader reader = new BufferedReader(new InputStreamReader(stream))) {
	        String line;
	        while ((line = reader.readLine()) != null) {
	            response.append(line);
	        }
	    }

	    return response.toString();
	}
	
	private boolean isInvalidJson(String json) {
	    return json == null || json.trim().isEmpty() || !json.trim().startsWith("{");
	}
	
	private List<SelectItem> parseBankList(String jsonResponse) {

	    List<SelectItem> codeList = new ArrayList<>();

	    JSONObject jsonObject = new JSONObject(jsonResponse);
	    JSONArray bankList;

	    if (jsonObject.has("liveBankList")) {
	        bankList = jsonObject.getJSONArray("liveBankList");
	    } else if (jsonObject.has("banks")) {
	        bankList = jsonObject.getJSONArray("banks");
	    } else {
	        System.err.println("No valid bank list found in JSON.");
	        return codeList;
	    }

	    for (int i = 0; i < bankList.length(); i++) {
	        JSONObject bank = bankList.getJSONObject(i);

	        String bankId = bank.optString("bankId");
	        String bankName = bank.optString("bankName");

	        if (bankId != null && !bankId.isEmpty()) {
	            codeList.add(new SelectItem(bankId, bankName + " (" + bankId + ")"));
	        }
	    }

	    return codeList;
	}
	
	private String buildUrl(boolean isLive, RequestType type) {
	    String baseUrl = isLive ? LIVE_BASE : UAT_BASE;
	    String path = PATH_MAP.get(type);

	    if (path == null) {
	        throw new IllegalArgumentException("Invalid RequestType: " + type);
	    }

	    return baseUrl + path;
	}
	
	public boolean insertEmandateTxn(EmandateRequest req,
            String branchId,
            String userId,
            String customerId,
            String loanNo) {
		
	    boolean status = false;
	    java.sql.Connection conn = null;
	    java.sql.PreparedStatement ps = null;

	    try {
	        conn = DataBaseConnection.JAVAConnection();

	        String sql = "INSERT INTO hdfc_emandates ("
	                + "msg_id, "
	                + "branch_id, user_id, customer_id, loan_no, "   // NEW
	                + "customer_name, customer_mobile, customer_email, customer_account_no, "
	                + "enc_customer_name, enc_customer_mobile, enc_customer_email, enc_customer_account_no, "
	                + "enc_short_code, enc_util_code, "
	                + "start_date, expiry_date, debit_amount, max_amount, "
	                + "frequency, sequence_type, "
	                + "ifsc_code, bank_code, account_type, "
	                + "channel, mandate_type, checksum, status"
	                + ") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

	        ps = conn.prepareStatement(sql);

	        int i = 1;

	        // BASIC
	        ps.setString(i++, req.getMsgId());

	        // NEW FIELDS
	        ps.setString(i++, branchId);
	        ps.setString(i++, userId);
	        ps.setString(i++, customerId);
	        ps.setString(i++, loanNo);

	        // CUSTOMER
	        ps.setString(i++, req.getCustomerName());
	        ps.setString(i++, req.getCustomerMobile());
	        ps.setString(i++, req.getCustomerEmail());
	        ps.setString(i++, req.getCustomerAccountNo());

	        // ENCRYPTED
	        ps.setString(i++, req.getEncCustomerName());
	        ps.setString(i++, req.getEncCustomerMobile());
	        ps.setString(i++, req.getEncCustomerEmail());
	        ps.setString(i++, req.getEncCustomerAccountNo());
	        ps.setString(i++, req.getEncShortCode());
	        ps.setString(i++, req.getEncUtilCode());

	        // DATES
	        ps.setString(i++, req.getCustomerStartDate());
	        ps.setString(i++, req.getCustomerExpiryDate());

	        // AMOUNT
	        ps.setBigDecimal(i++, req.getCustomerDebitAmount());
	        ps.setBigDecimal(i++, req.getCustomerMaxAmount());

	        // MANDATE
	        ps.setString(i++, req.getCustomerDebitFrequency());
	        ps.setString(i++, req.getCustomerSequenceType());

	        // BANK
	        ps.setString(i++, req.getCustomerInstructedMemberId());
	        ps.setString(i++, req.getFiller6()); // bank code
	        ps.setString(i++, req.getFiller5()); // account type

	        // META
	        ps.setString(i++, req.getChannel());
	        ps.setString(i++, req.getMandateReqType());

	        // CHECKSUM
	        ps.setString(i++, req.getCheckSum());

	        // STATUS
	        ps.setString(i++, "INITIATED");

	        ps.executeUpdate();

	        status = true;

	    } catch (Exception e) {
	        e.printStackTrace();
	    } finally {
	        try {
	            if (ps != null) ps.close();
	            if (conn != null) conn.close();
	        } catch (Exception e) {
	            e.printStackTrace();
	        }
	    }

	    return status;
	}

	
	  public EmandateRequest getTxnByMsgId(String msgId) {

		    EmandateRequest req = null;
		    java.sql.Connection conn = null;
		    java.sql.PreparedStatement ps = null;
		    java.sql.ResultSet rs = null;

		    try {
		        conn = DataBaseConnection.JAVAConnection();

		        String sql = "SELECT * FROM hdfc_emandates WHERE msg_id = ?";

		        ps = conn.prepareStatement(sql);
		        ps.setString(1, msgId);

		        rs = ps.executeQuery();

		        if (rs.next()) {

		            req = new EmandateRequest();

		            // BASIC
		            req.setMsgId(rs.getString("msg_id"));
		            req.setCustomerName(rs.getString("customer_name"));
		            req.setCustomerMobile(rs.getString("customer_mobile"));
		            req.setCustomerEmail(rs.getString("customer_email"));
		            req.setCustomerAccountNo(rs.getString("customer_account_no"));

		            // ENCRYPTED
		            req.setEncCustomerName(rs.getString("enc_customer_name"));
		            req.setEncCustomerMobile(rs.getString("enc_customer_mobile"));
		            req.setEncCustomerEmail(rs.getString("enc_customer_email"));
		            req.setEncCustomerAccountNo(rs.getString("enc_customer_account_no"));
		            req.setEncShortCode(rs.getString("enc_short_code"));
		            req.setEncUtilCode(rs.getString("enc_util_code"));

		            // DATES
		            req.setCustomerStartDate(rs.getString("start_date"));
		            req.setCustomerExpiryDate(rs.getString("expiry_date"));

		            // AMOUNT
		            req.setCustomerDebitAmount(rs.getBigDecimal("debit_amount"));
		            req.setCustomerMaxAmount(rs.getBigDecimal("max_amount"));

		            // MANDATE
		            req.setCustomerDebitFrequency(rs.getString("frequency"));
		            req.setCustomerSequenceType(rs.getString("sequence_type"));

		            // BANK
		            req.setCustomerInstructedMemberId(rs.getString("ifsc_code"));
		            req.setFiller6(rs.getString("bank_code"));
		            req.setFiller5(rs.getString("account_type"));

		            // META
		            req.setChannel(rs.getString("channel"));
		            req.setMandateReqType(rs.getString("mandate_type"));

		            // CHECKSUM
		            req.setCheckSum(rs.getString("checksum"));
		            req.setUmrn(rs.getString("umrn"));
		            req.setRefId(rs.getString("ref_id"));
		            
		        }

		    } catch (Exception e) {
		        e.printStackTrace();
		    } finally {
		        try {
		            if (rs != null) rs.close();
		            if (ps != null) ps.close();
		            if (conn != null) conn.close();
		        } catch (Exception e) {
		            e.printStackTrace();
		        }
		    }

		    return req;
		}
	
	
	  public boolean updateMandateStatus(
		        String msgId,
		        String status) {

		    String query =
		        "UPDATE hdfc_emandates " +
		        "SET status=? " +
		        "WHERE msg_id=?";

		    try (
		        Connection conn = DataBaseConnection.JAVAConnection();
		        PreparedStatement ps =
		            conn.prepareStatement(query)
		    ) {

		        ps.setString(1, status);
		        ps.setString(2, msgId);

		        return ps.executeUpdate() > 0;

		    } catch (Exception e) {

		        e.printStackTrace();
		    }

		    return false;
		}

	  public boolean updateMandateOperation(
		        EmandateRequest mandate) {

		    String query =
		        "UPDATE hdfc_emandates SET " +
		        "operation_type=?, " +
		        "status=?, " +
		        "mandate_type=?, " +
		        "updated_at=NOW() " +
		        "WHERE id=?";

		    try (
		        Connection conn = DataBaseConnection.JAVAConnection();
		        PreparedStatement ps =
		            conn.prepareStatement(query)
		    ) {

		        ps.setString(1, mandate.getMandateReqType());

		        ps.setString(2, mandate.getStatus());

		        ps.setString(3, mandate.getMandateReqType());

		        ps.setLong(4, mandate.getId());

		        return ps.executeUpdate() > 0;

		    } catch (Exception e) {

		        e.printStackTrace();
		    }

		    return false;
		}
	  
	  public boolean updateMsgId(
		        Long id,
		        String msgId) {

		    String query =
		        "UPDATE hdfc_emandates " +
		        "SET msg_id=? " +
		        "WHERE id=?";

		    try (
		        Connection conn =
		            DataBaseConnection.JAVAConnection();

		        PreparedStatement ps =
		            conn.prepareStatement(query)
		    ) {

		        ps.setString(1, msgId);
		        ps.setLong(2, id);

		        return ps.executeUpdate() > 0;

		    } catch (Exception e) {

		        e.printStackTrace();
		    }

		    return false;
		}
	
	  	
		public ArrayList<SelectItem> employeeList(int branchId)
		{
			ArrayList<SelectItem> list=new ArrayList<>();
			try(Connection conn=DataBaseConnection.JAVAConnection())
			{
				Statement st=conn.createStatement();
				String query="select name,dsacode from employee where status='active'  ";
				ResultSet rr=st.executeQuery(selectQueWithBranch(query, branchId));
				while(rr.next())
				{
					SelectItem ll=new SelectItem();
					ll.setLabel(rr.getString("name")); 
					ll.setValue(rr.getString("dsacode"));
					list.add(ll);
				}
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
			return list;
		}
		
		  public String selectQueWithBranch(String query,int branchId)
			{
				
				if(branchId == -1)
				{
					
				}
				else
				{
					if(query.contains("where"))
					{
						query=query+" and branch_id='"+branchId+"'";
					}
					else
					{
						query=query+" where branch_id='"+branchId+"'";
					}
				}
				
				//System.out.println(query);
				return query;
			}
		  
		  public LoanEmiSummary getLoanEmiSummary(String loanType, String loanNo, Connection conn) {

			    LoanEmiSummary summary = new LoanEmiSummary();

			    PreparedStatement ps = null;
			    ResultSet rs = null;

			    try {

			        String sql;

			        if ("grouploan".equalsIgnoreCase(loanType)) {

			            sql = "SELECT terms, emi, discount "
			                + "FROM " + loanType
			                + " WHERE sub_loan_no=? AND status='Approve'";

			        } else {

			            sql = "SELECT terms, emi, discount "
			                + "FROM " + loanType
			                + " WHERE loanNo=? AND status='Approve'";
			        }

			        ps = conn.prepareStatement(sql);
			        ps.setString(1, loanNo);

			        rs = ps.executeQuery();

			        if (!rs.next()) {
			            return summary;
			        }

			        int terms = rs.getInt("terms");
			        double emi = rs.getDouble("emi");
			        double discount = rs.getDouble("discount");

			        double paidAmount =
			            db.loanPaidAmountByUser(loanType, loanNo, conn);

			        summary.setTotalEmi(terms);

			        for (int i = 1; i <= terms; i++) {

			            double currentEmi = emi;

			            // Discount on last EMI
			            if (i == terms) {
			                currentEmi = currentEmi - discount;
			            }

			            currentEmi = Double.parseDouble(
			                db.df.format(currentEmi)
			            );

			            if (paidAmount >= currentEmi) {

			                // Fully paid EMI
			                summary.setPaidEmi(
			                    summary.getPaidEmi() + 1
			                );

			                paidAmount = Double.parseDouble(
			                    db.df.format(
			                        paidAmount - currentEmi
			                    )
			                );

			            } else {

			                // Pending / partially paid EMI
			                summary.setPendingEmi(
			                    summary.getPendingEmi() + 1
			                );

			                double dueAmount = currentEmi - paidAmount;

			                summary.setTotalDueAmount(
			                    summary.getTotalDueAmount() + dueAmount
			                );

			                // Don't carry remaining amount to next EMI
			                paidAmount = 0;
			            }
			        }

			        summary.setTotalDueAmount(
			            Double.parseDouble(
			                db.df.format(
			                    summary.getTotalDueAmount()
			                )
			            )
			        );

			    } catch (Exception e) {

			        e.printStackTrace();
			        db.fileCreation(e.getMessage());

			    } finally {

			        try {
			            if (rs != null) {
			                rs.close();
			            }
			        } catch (Exception e) {
			        }

			        try {
			            if (ps != null) {
			                ps.close();
			            }
			        } catch (Exception e) {
			        }
			    }

			    return summary;
			}
		  


	
	
	

	
	

}