package cashfree;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeParseException;
import java.util.ArrayList;
import java.util.Arrays;

import org.primefaces.json.JSONObject;

import com.mysql.jdbc.Statement;

import main_package.DataBaseConnection;
import main_package.DataBaseMethods;
import main_package.DataBaseMethodsNaman;
import main_package.LoanInfo;

public class EmiPreparationScheduler {

	private static final ZoneId INDIA_ZONE = ZoneId.of("Asia/Kolkata");
	private static final DateTimeFormatter CASHFREE_DATE_TIME = DateTimeFormatter.ISO_OFFSET_DATE_TIME;


	public void processUpcomingEmis() { 
	    LocalDate today = LocalDate.now(INDIA_ZONE);
	    LocalDate tomorrow = today.plusDays(1);
	    String targetDueDate = tomorrow.toString();

	    String selectSql = "SELECT s.id, s.subscription_id, s.cf_subscription_id, s.payment_group, s.loan_no, s.rd_connected, s.rd_number "
	            + "FROM subscriptions_overview s "
	            + "WHERE UPPER(s.subscription_status) = 'ACTIVE' "
	            + "AND UPPER(s.plan_type) = 'ON_DEMAND' "
	            + "AND s.loan_no IS NOT NULL AND s.loan_no <> '' "
	            + "AND LOWER(s.payment_group) IN ('enach','upi')";

	    try (Connection conn = DataBaseConnection.JAVAConnection();
	         PreparedStatement ps = conn.prepareStatement(selectSql);
	         ResultSet rs = ps.executeQuery()) {

	        DataBaseMethods dbMethods = new DataBaseMethods();
	        DataBaseMethodsNaman dbn=new DataBaseMethodsNaman();

	        int subscriptionCount = 0, loanCount = 0, scheduleCount = 0;
	        int matchingCount = 0, chargeRaisedCount = 0, chargeFailedCount = 0;

	        while (rs.next()) {
	            subscriptionCount++;
	            long subscriptionOverviewId = rs.getLong("id");
	            String subscriptionId = rs.getString("subscription_id");
	            String cfSubscriptionId = rs.getString("cf_subscription_id");
	            String paymentGroup = rs.getString("payment_group");
	            String loanNo = rs.getString("loan_no");
	            boolean rdConnected = rs.getBoolean("rd_connected");
	            String rdNumber = rs.getString("rd_number");
	            try {
	                String loanType = resolveLoanType(loanNo);
	                if (loanType == null) {
	                    System.err.println("Unknown loan type: " + loanNo);
	                    continue;
	                }
	                loanCount++;

	                ArrayList<LoanInfo> scheduleList = dbMethods.loanInstallment(loanType, loanNo, conn);
	                if (scheduleList == null || scheduleList.isEmpty()) {
	                    continue;
	                }

	                for (LoanInfo info : scheduleList) {
	                    scheduleCount++;
	                    String installmentDate = info.getStrInstlmntDate();
	                    String status = info.getStatus();
	                    double emiAmount = info.getDueAmount();
	                    long installmentNo = info.getSno();

	                    if (!isMatchingDueDate(installmentDate, tomorrow)) {
	                        continue;
	                    }

	                    if (!"Due".equalsIgnoreCase(status)) {
	                        continue;
	                    }
	                    double rdInstallmentAmount=0.00;
	                    if (rdConnected && rdNumber != null && !rdNumber.trim().isEmpty()) {

	                       rdInstallmentAmount =
	                                dbn.getRdInstallmentAmount(rdNumber, conn);

	                        if (rdInstallmentAmount <= 0) {
	                            System.err.println(
	                                "Invalid RD installment amount for RD number: " + rdNumber
	                            );
	                            continue;
	                        }

	                       
	                    }

	                    matchingCount++;

	                    if (emiAmount <= 0) {
	                        System.err.println("Invalid EMI amount: " + emiAmount);
	                        continue;
	                    }
	                    
	                    
	                    double penaltyAmount = 0.00;
	                    double totalAmount = emiAmount + penaltyAmount+ rdInstallmentAmount;

	                    TrackingRecord tracking = findTrackingRecord(conn, loanNo, installmentNo);
	                    String paymentId;

	                    if (tracking == null) {
	                        paymentId = createPaymentId(loanNo, installmentNo);
	                        long trackingId = createTrackingRecord(conn, loanNo, loanType, installmentNo,
	                                subscriptionId, cfSubscriptionId, paymentGroup, paymentId,
	                                emiAmount,rdInstallmentAmount, penaltyAmount, totalAmount, tomorrow);
	                        tracking = findTrackingRecord(conn, loanNo, installmentNo);
	                    } else {
	                        paymentId = tracking.paymentId;
	                        if (paymentId == null || paymentId.trim().isEmpty()) {
	                            paymentId = createPaymentId(loanNo, installmentNo);
	                            updatePaymentId(conn, tracking.id, paymentId);
	                            tracking.paymentId = paymentId;
	                        }
	                    }

	                    if (isChargeRaised(tracking)) {
	                        continue;
	                    }

	                    String scheduleDateTime = tomorrow.atTime(8, 0).atZone(INDIA_ZONE).format(CASHFREE_DATE_TIME);
	                    String raiseResponse;

	                    try {
	                        raiseResponse = CashfreeSubscriptionPaymentService.raisePayment(
	                                subscriptionId, paymentId, totalAmount, scheduleDateTime);
	                    } catch (Exception e) {
	                        chargeFailedCount++;
	                        updateChargeFailure(conn, tracking.id, "CHARGE_RAISE_FAILED", "RAISE_EXCEPTION", e.getMessage(), null);
	                        System.err.println("Cashfree raise exception: " + e.getMessage());
	                        continue;
	                    }

	                    JSONObject raiseJson;
	                    try {
	                        if (raiseResponse == null || raiseResponse.trim().isEmpty()) {
	                            chargeFailedCount++;
	                            updateChargeFailure(conn, tracking.id, "CHARGE_RAISE_FAILED", "RAISE", "Empty Cashfree response", raiseResponse);
	                            continue;
	                        }
	                        raiseJson = new JSONObject(raiseResponse);
	                    } catch (Exception e) {
	                        chargeFailedCount++;
	                        updateChargeFailure(conn, tracking.id, "CHARGE_RAISE_FAILED", "RAISE", "Invalid Cashfree response", raiseResponse);
	                        continue;
	                    }

	                    String cfPaymentId = raiseJson.optString("cf_payment_id", null);
	                    String returnedPaymentId = raiseJson.optString("payment_id", paymentId);
	                    String paymentStatus = raiseJson.optString("payment_status", null);

	                    if (cfPaymentId == null || cfPaymentId.trim().isEmpty()) {
	                        chargeFailedCount++;
	                        String error = raiseJson.optString("message", "Cashfree charge raise failed");
	                        updateChargeFailure(conn, tracking.id, "CHARGE_RAISE_FAILED", "RAISE", error, raiseResponse);
	                        System.err.println("Cashfree charge failed: " + error);
	                        continue;
	                    }

	                    updateChargeRaised(conn, tracking.id, returnedPaymentId, cfPaymentId, paymentStatus, raiseResponse);
	                    chargeRaisedCount++;
	                }
	            } catch (Exception e) {
	                System.err.println("Error processing Loan No: " + loanNo);
	                e.printStackTrace();
	            }
	        }
	    } catch (Exception e) {
	        System.err.println("Fatal error in EmiPreparationScheduler: " + e.getMessage());
	        e.printStackTrace();
	    }
	}


    // LOAN TYPE
	 private String resolveLoanType(String loanNo) {
	    if (loanNo == null) return null;
	    
	    String value = loanNo.trim().toUpperCase();
	    if (value.startsWith("PL")) return "personalloan";
	    if (value.startsWith("SL")) return "secure_loan";
	    if (value.startsWith("GL")) return "grouploan";
	    
	    return null;
	}

    // DATE CHECK

	 private static final DateTimeFormatter SLASH_DATE_FORMAT = DateTimeFormatter.ofPattern("dd/MM/yyyy");

	 private boolean isMatchingDueDate(String installmentDateStr, LocalDate targetDate) {
	     if (installmentDateStr == null || installmentDateStr.trim().isEmpty()) return false;
	     
	     String value = installmentDateStr.trim();
	     try {
	         LocalDate parsed = value.contains("/") 
	             ? LocalDate.parse(value, SLASH_DATE_FORMAT) 
	             : LocalDate.parse(value); // Default ISO_LOCAL_DATE (yyyy-MM-dd)
	             
	         return targetDate.equals(parsed);
	     } catch (DateTimeParseException e) {
	         System.err.println("Invalid installment date: " + value);
	         return false;
	     }
	 }


    // PAYMENT ID

	 private String createPaymentId(String loanNo, long installmentNo) {
		    return "EMI_" + loanNo + "_" + installmentNo + "_" + System.currentTimeMillis();
		}

    // FIND TRACKING RECORD

	 private TrackingRecord findTrackingRecord(Connection conn, String loanNo, long installmentNo) throws SQLException {
		    String sql = "SELECT id, payment_id, status, cf_payment_id, payment_status, failure_reason, last_error, retry_count "
		               + "FROM cashfree_emi_tracking WHERE loan_no = ? AND installment_no = ? LIMIT 1";

		    try (PreparedStatement ps = conn.prepareStatement(sql)) {
		        ps.setString(1, loanNo);
		        ps.setLong(2, installmentNo);

		        try (ResultSet rs = ps.executeQuery()) {
		            if (!rs.next()) return null;

		            TrackingRecord record = new TrackingRecord();
		            record.id = rs.getLong("id");
		            record.paymentId = rs.getString("payment_id");
		            record.status = rs.getString("status");
		            record.cfPaymentId = rs.getString("cf_payment_id");
		            record.paymentStatus = rs.getString("payment_status");
		            record.failureReason = rs.getString("failure_reason");
		            record.lastError = rs.getString("last_error");
		            record.retryCount = rs.getInt("retry_count");
		            return record;
		        }
		    }
		}

    // CHECK CHARGE RAISED

	 private boolean isChargeRaised(TrackingRecord tracking) {
		    if (tracking == null) return false;
		    if (tracking.cfPaymentId != null && !tracking.cfPaymentId.trim().isEmpty()) return true;

		    return Arrays.asList("CHARGE_RAISED", "PAYMENT_PENDING", "PAYMENT_SUCCESS", "PAID")
		                 .contains(tracking.status != null ? tracking.status.toUpperCase() : "");
		}
	 
		// CREATE TRACKING

	 private long createTrackingRecord(Connection conn, String loanNo, String loanType, long installmentNo,
             String subscriptionId, String cfSubscriptionId, String paymentGroup,
             String paymentId, double emiAmount,double rdAmount, double penaltyAmount, double totalAmount,
             LocalDate dueDate) throws SQLException {

			String sql = "INSERT INTO cashfree_emi_tracking (loan_no, loan_type, installment_no, subscription_id, "
			+ "cf_subscription_id, payment_group, payment_id, emi_amount, rd_amount, penalty_amount, total_amount, "
			+ "due_date, status, last_action) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'CREATED', 'CREATE')";
			
			try (PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
			ps.setString(1, loanNo);
			ps.setString(2, loanType);
			ps.setLong(3, installmentNo);
			ps.setString(4, subscriptionId);
			ps.setString(5, cfSubscriptionId);
			ps.setString(6, paymentGroup);
			ps.setString(7, paymentId);
			ps.setDouble(8, emiAmount);
			ps.setDouble(9, rdAmount);
			ps.setDouble(10, penaltyAmount);
			ps.setDouble(11, totalAmount);
			ps.setString(12, dueDate.toString());
			
			ps.executeUpdate();
			
			try (ResultSet rs = ps.getGeneratedKeys()) {
			if (rs.next()) return rs.getLong(1);
			}
			}
			throw new SQLException("Unable to create tracking record.");
			}
		// ============================================================
		// UPDATE PAYMENT ID
		// ============================================================

	 private void updatePaymentId(Connection conn, long trackingId, String paymentId) throws SQLException {
		    String sql = "UPDATE cashfree_emi_tracking SET payment_id = ? WHERE id = ?";
		    try (PreparedStatement ps = conn.prepareStatement(sql)) {
		        ps.setString(1, paymentId);
		        ps.setLong(2, trackingId);
		        ps.executeUpdate();
		    }
		}

		// ============================================================
		// CHARGE SUCCESS
		// ============================================================

	 private void updateChargeRaised(Connection conn, long trackingId, String paymentId, String cfPaymentId,
             String paymentStatus, String response) throws SQLException {

			String sql = "UPDATE cashfree_emi_tracking SET payment_id = ?, cf_payment_id = ?, payment_status = ?, "
			+ "raise_response = ?, status = 'CHARGE_RAISED', last_action = 'RAISE', last_error = NULL, "
			+ "failure_reason = NULL, charge_raised_at = NOW() WHERE id = ?";
			
			try (PreparedStatement ps = conn.prepareStatement(sql)) {
			ps.setString(1, paymentId);
			ps.setString(2, cfPaymentId);
			ps.setString(3, paymentStatus);
			ps.setString(4, response);
			ps.setLong(5, trackingId);
			ps.executeUpdate();
			}
			}

		// ============================================================
		// CHARGE FAILURE
		// ============================================================

		 private void updateChargeFailure(Connection conn, long trackingId, String status, String action,
	             String error, String response) throws SQLException {
	
				String sql = "UPDATE cashfree_emi_tracking SET status = ?, last_action = ?, last_error = ?, "
				+ "failure_reason = ?, raise_response = ?, retry_count = retry_count + 1, "
				+ "last_retry_at = NOW() WHERE id = ?";
				
				try (PreparedStatement ps = conn.prepareStatement(sql)) {
				ps.setString(1, status);
				ps.setString(2, action);
				ps.setString(3, error);
				ps.setString(4, error);
				ps.setString(5, response);
				ps.setLong(6, trackingId);
				ps.executeUpdate();
			}
		}

    // ============================================================
    // TRACKING OBJECT
    // ============================================================

    private static class TrackingRecord {

        long id;

        String paymentId;

        String status;

        String cfPaymentId;

        String paymentStatus;

        String failureReason;

        String lastError;

        int retryCount;
    }
}