package basic_package;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Date;

import javax.servlet.http.HttpServletResponse;

import org.primefaces.json.JSONObject;

import main_package.DataBaseConnection;
import main_package.DataBaseMethods;

public class CallbackHandlerService {

	public boolean handleMandateCallback(JSONObject callbackData, HttpServletResponse response) {

	    String callbackStatus = callbackData.optString("status", "");
	    String umrn = callbackData.optString("umrn", "");
	    String refId = callbackData.optString("merchantTrxnRefId", "");

	    String status;

	    if ("SUCCESS".equalsIgnoreCase(callbackStatus)) {
	        status = "approved";
	    } else {
	        status = callbackStatus;
	    }

	    int res = updateMandateStatus(refId, status, umrn);

	    return res > 0;
	}
	  
	  
	public int updateMandateStatus(String refId, String status, String umrn) {

	    String query;
	    boolean updateUmrn = "approved".equalsIgnoreCase(status);

	    if (updateUmrn) {
	        query = "UPDATE nach_mandate_request " +
	                "SET umrn = ?, status = ? " +
	                "WHERE merchant_trxn_ref_id = ?";
	    } else {
	        query = "UPDATE nach_mandate_request " +
	                "SET status = ? " +
	                "WHERE merchant_trxn_ref_id = ?";
	    }

	    try (Connection conn = DataBaseConnection.JAVAConnection();
	         PreparedStatement ps = conn.prepareStatement(query)) {

	        if (updateUmrn) {
	            ps.setString(1, umrn);
	            ps.setString(2, status);
	            ps.setString(3, refId);
	        } else {
	            ps.setString(1, status);
	            ps.setString(2, refId);
	        }

	        int rows = ps.executeUpdate();

	        System.out.println("Mandate status updated. Rows affected: " + rows);

	        return rows;

	    } catch (Exception e) {
	        System.err.println("Error updating mandate status:");
	        e.printStackTrace();
	    }

	    return 0;
	}
	    
	    public boolean handlePresentationCallback(JSONObject callbackData, HttpServletResponse response) {
	        String merchantTrxnRefId = callbackData.optString("merchantTrxnRefId");
	        String status = callbackData.optString("status").toLowerCase();
	        String code = callbackData.optString("code");
	        String description = callbackData.optString("description");
	        
	        String  umrn = callbackData.optString("umrn");

	        String finalStatus;
	        switch (status) {
	            case "success":
	                finalStatus = "success";
	                break;
	            case "failed":
	                finalStatus = "failed";
	                break;
	            case "pending":
	                finalStatus = "pending";
	                break;
	            default:
	                finalStatus = "unknown";
	        }

	        System.out.println("[PresentationCallback] Final mapped status: " + finalStatus);

	        updatePaymentStatus(merchantTrxnRefId, finalStatus,code, description,0,umrn);
	        return true;
	    }
	    
	    public boolean insertLoanPaymentFromCashfreeEnach(
	            String employee,
	            String loanType,
	            String loanNo,
	            Date payDate,
	            double amount,
	            String payMode,
	            String status,
	            String savingType,
	            String transactionId,
	            String response,
	            String attendance,
	            String branchId,
	            String umrnNo) {

	        attendance = "A";
	        savingType = "NA";

	        // Query with payment_source column
	        String queryWithSource = "INSERT INTO loan_payment_from_app " +
	                "(employee, loan_type, loan_no, pay_date, amount, pay_mode, " +
	                "status, saving_type, transaction_id, response, attendance, " +
	                "branch_id, umrn_no, payment_source) " +
	                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

	        // Fallback query without payment_source column
	        String queryWithoutSource = "INSERT INTO loan_payment_from_app " +
	                "(employee, loan_type, loan_no, pay_date, amount, pay_mode, " +
	                "status, saving_type, transaction_id, response, attendance, " +
	                "branch_id, umrn_no) " +
	                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

	        try (Connection conn = DataBaseConnection.JAVAConnection()) {

	            // Step 1: Try inserting with the payment_source column
	            try (PreparedStatement pstmt = conn.prepareStatement(queryWithSource)) {
	                setStatementParameters(pstmt, employee, loanType, loanNo, payDate, amount, payMode,
	                        status, savingType, transactionId, response, attendance, branchId, umrnNo);
	                pstmt.setString(14, "CASHFREE_ENACH");

	                return pstmt.executeUpdate() > 0;

	            } catch (java.sql.SQLException e) {
	                // Step 2: Check if the error is specifically due to the missing column
	                if (e.getMessage() != null && e.getMessage().contains("Unknown column 'payment_source'")) {
	                    System.err.println("Warning: 'payment_source' column not found. Falling back to insert without it.");

	                    // Step 3: Execute fallback query without payment_source
	                    try (PreparedStatement pstmtFallback = conn.prepareStatement(queryWithoutSource)) {
	                        setStatementParameters(pstmtFallback, employee, loanType, loanNo, payDate, amount, payMode,
	                                status, savingType, transactionId, response, attendance, branchId, umrnNo);

	                        return pstmtFallback.executeUpdate() > 0;
	                    }
	                } else {
	                    // Re-throw if it's a different SQL error
	                    throw e;
	                }
	            }

	        } catch (Exception e) {
	            e.printStackTrace();
	        }

	        return false;
	    }
        
	    // Helper method to set common parameters and keep code clean
	    private void setStatementParameters(PreparedStatement pstmt, String employee, String loanType, 
	                                        String loanNo, Date payDate, double amount, String payMode, 
	                                        String status, String savingType, String transactionId, 
	                                        String response, String attendance, String branchId, String umrnNo) throws java.sql.SQLException {
	        pstmt.setString(1, employee);
	        pstmt.setString(2, loanType);
	        pstmt.setString(3, loanNo);
	        pstmt.setDate(4, new java.sql.Date(payDate.getTime()));
	        pstmt.setDouble(5, amount);
	        pstmt.setString(6, payMode);
	        pstmt.setString(7, status);
	        pstmt.setString(8, savingType);
	        pstmt.setString(9, transactionId);
	        pstmt.setString(10, response);
	        pstmt.setString(11, attendance);
	        pstmt.setString(12, branchId);
	        pstmt.setString(13, umrnNo);
	    }

	   

	    public void updatePaymentStatus(String merchantTrxnRefId, String status, String code, String description, double amount, String umrn) {
	      
	    	String sql =
	    		    "INSERT INTO nach_payments " +
	    		    "(merchant_trxn_ref_id, status, code, description, callback_type, amount, umrn, loan_no) " +
	    		    "VALUES (?, ?, ?, ?, ?, ?, ?, ?) " +
	    		    "ON DUPLICATE KEY UPDATE " +
	    		    "status = VALUES(status), " +
	    		    "code = VALUES(code), " +
	    		    "description = VALUES(description), " +
	    		    "amount = VALUES(amount), " +
	    		    "loan_no = VALUES(loan_no)";
	    	
	    	DataBaseMethods db = new DataBaseMethods();

	        String loanNo=db.fetchDataBymerchantId(umrn);
	        boolean isRdConnected=db.checkIfKjSubscriptionConnectedWithRd(umrn);
	        String rdNumber=null;
	        double rdAmount = 0;	
	        if(isRdConnected) {
	        	rdNumber = db.findRdNumberByKjSubscriptionId(umrn);
	        	rdAmount=db.findRdinstallmentAmountByRdNumber(rdNumber);
	        }
	        try {
	        	amount=fetchEmiAmountFromMandateTableBy(umrn);
	        }catch (Exception e) {
				e.printStackTrace();
			}
	        try (Connection conn = DataBaseConnection.JAVAConnection()) {
	            try (PreparedStatement ps = conn.prepareStatement(sql)) {
	                ps.setString(1, merchantTrxnRefId);
	                ps.setString(2, status);
	                ps.setString(3, code);
	                ps.setString(4, description);
	                ps.setString(5, "DEBIT"); 
	                ps.setDouble(6, amount);
	                ps.setString(7, umrn);
	                if(isRdConnected) {
	                	ps.setString(8, loanNo +" + "+ rdNumber);
	                }else {
	                	ps.setString(8, loanNo);
	                }
	                
	                int rows = ps.executeUpdate();
	                if (rows == 0) {
	                    System.out.println("Duplicate callback ignored: " + merchantTrxnRefId);
	                    return;
	                }
	             //   System.out.println("Insert successful, rows affected: " + rows);
	                if("success".equalsIgnoreCase(status)) {
	        	         Date date= new Date();
	        	    	
	        	         String loanType = "unknown";
	        	         if (loanNo != null) {
	        	             if (loanNo.startsWith("PL")) {
	        	                 loanType = "personalloan";
	        	             } else if (loanNo.startsWith("SL")) {
	        	                 loanType = "secure_loan";
	        	             }
	        	         }

	        	    	String branchId=db.fetchBranchIdByLoanNo(loanNo);
	        	    	
		        	   
	        	    	if(isRdConnected) {
		        			   double emiAmount=amount-rdAmount;
			               insertLoanPaymentFromApp("admin",loanType, loanNo, date, emiAmount, "online" , "pending",null, merchantTrxnRefId, description, null, branchId,umrn);
			                insertLoanPaymentFromApp("admin","RD", rdNumber, date, rdAmount, "online" , "pending",null, merchantTrxnRefId, description, null, branchId,umrn);
			                   
		        		}else {
			                   insertLoanPaymentFromApp("admin",loanType, loanNo, date, amount, "online" , "pending",null, merchantTrxnRefId, description, null, branchId,umrn);

		        		}
		                
	                }else if("pending".equalsIgnoreCase(status)) {
	                	System.out.println("Pending");

	                }else if("failed".equalsIgnoreCase(status)) {
	                	insertManualChargeRecord(umrn,true);
	                
	                }else {
	                	System.out.println("unknown status");
	                }
	                
	            } catch (Exception e) {
	                System.err.println("Error during PreparedStatement execution.");
	                e.printStackTrace();
	            }
	        } catch (Exception e) {
	            System.err.println("Error while establishing DB connection.");
	            e.printStackTrace();
	        }
	    }
	    
	    private double fetchEmiAmountFromMandateTableBy(String umrn) {
	        double mandateAmount = 0.0;

	        try (Connection conn = DataBaseConnection.JAVAConnection()) {
	            String qq = "SELECT mandate_amount FROM nach_mandate_request WHERE umrn = ?";

	            try (PreparedStatement ps = conn.prepareStatement(qq)) {
	                ps.setString(1, umrn);
	                
	                try (ResultSet rs = ps.executeQuery()) {
	                    if (rs.next()) {
	                        mandateAmount = rs.getDouble("mandate_amount");
	                    }
	                }
	            }
	        } catch (Exception e) {
	            e.printStackTrace();
	        }

	        return mandateAmount;
	    }

	    public boolean insertLoanPaymentFromApp(String employee, String loanType, String loanNo,Date payDate,
	            double amount, String payMode, String status, String savingType,
	            String transactionId, String response, String attendance, String branchId,String umrn_no) {
	        

	        String query = "INSERT INTO loan_payment_from_app " +
	                       "(employee, loan_type, loan_no, pay_date, amount, pay_mode, status, " +
	                       "saving_type, transaction_id, response, attendance, branch_id,umrn_no) " +
	                       "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

	        attendance="A";
	        savingType="NA";
	        try (Connection conn = DataBaseConnection.JAVAConnection();
	             PreparedStatement pstmt = conn.prepareStatement(query)) {

	            pstmt.setString(1, employee);
	            pstmt.setString(2, loanType);
	            pstmt.setString(3, loanNo);
	            pstmt.setDate(4,  new java.sql.Date(payDate.getTime()));
	            pstmt.setDouble(5, amount);
	            pstmt.setString(6, payMode);
	            pstmt.setString(7, status);
	            pstmt.setString(8, savingType);
	            pstmt.setString(9, transactionId);
	            pstmt.setString(10, response);
	            pstmt.setString(11, attendance);
	            pstmt.setString(12, branchId);
	            pstmt.setString(13, umrn_no);

	            int rowsInserted = pstmt.executeUpdate();

	            if (rowsInserted > 0) {
	                return true;
	            } else {
	               return false;
	            }

	        } catch (Exception e) {
	            e.printStackTrace();
	        }
	        
	        return false;
	    }
	    
  public boolean insertManualChargeRecord(String umrn , boolean isumrn) {
	    	
	    	String loanNo="";
	    	if(isumrn) {
		    	 loanNo=new DataBaseMethods().fetchDataBymerchantId(umrn);
	    	}else {
	    		loanNo=umrn;
	    	}
	    	
	    	
	    	String loanType = loanNo.startsWith("PL") ? "personalloan"
	                  : loanNo.startsWith("SL") ? "secure_loan"
	                  : "unknown";

	        				
	        Date date =new Date();
	        
	        String remark = "NATCH auto-debit failed";
	        
	        double amount=500;

	        String query = "INSERT INTO manual_charges (loan_no, loan_type, date, amount, remark) " +
	                       "VALUES (?, ?, ?, ?, ?)";

	        try (Connection conn = DataBaseConnection.JAVAConnection();
	             PreparedStatement pstmt = conn.prepareStatement(query)) {
	        	
	            pstmt.setString(1, loanNo);
	            pstmt.setString(2, loanType);
	            pstmt.setDate(3,  new java.sql.Date(date.getTime()));
	            pstmt.setDouble(4, amount);
	            pstmt.setString(5, remark);

	            int rows = pstmt.executeUpdate();

	            if (rows > 0) {
	                return true;
	            } else {
	                return false;
	            }

	        } catch (Exception e) {
	            e.printStackTrace();
	        }
	        return false;
	    }
	

}