package natch_package;

import org.primefaces.json.JSONObject;
import basic_package.CallbackHandlerService;
import main_package.DataBaseConnection;
import main_package.DataBaseMethods;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

public class SubscriptionSaver {

    public static boolean paymentWebhookSaver(String jsonStr) {
        return paymentWebhookSaver(jsonStr, "");
    }

    public static boolean paymentWebhookSaver(String jsonStr, String signature) {
        try {
            if (jsonStr == null || jsonStr.trim().isEmpty()) {
                System.err.println("Cashfree webhook body is empty.");
                return false;
            }

            JSONObject json = new JSONObject(jsonStr);
            JSONObject data = json.optJSONObject("data");
            if (data == null) {
                System.err.println("Cashfree webhook data object not found.");
                return false;
            }

            JSONObject authDetails = data.optJSONObject("authorization_details");
            String authorizationReference = authDetails != null ? authDetails.optString("authorization_reference", null) : null;
            String paymentId = data.optString("payment_id", null);
            String cfPaymentId = data.optString("cf_payment_id", null);
            String orderId = data.optString("cf_order_id", null);
            String subscriptionId = data.optString("subscription_id", null);
            BigDecimal paymentAmount = getDecimal(data, "payment_amount");
            String paymentStatus = data.optString("payment_status", null);
            
            JSONObject failureDetails = data.optJSONObject("failure_details");
            String failureReason = failureDetails != null ? failureDetails.optString("failure_reason", null) : null;
            String eventType = json.optString("type", "UNKNOWN");

            // Extract plan_type from payload or fallback to DB lookup in subscriptions_overview
            JSONObject planDetails = data.optJSONObject("plan_details");
            String planType = planDetails != null ? planDetails.optString("plan_type", "") : json.optString("plan_type", "");
            
            if ((planType == null || planType.trim().isEmpty()) && subscriptionId != null && !subscriptionId.trim().isEmpty()) {
                planType = fetchPlanTypeFromDb(subscriptionId);
            }

            System.out.println("\n========================================\nCashfree Payment Webhook\nEvent Type      : " + eventType + "\nPayment ID      : " + paymentId + "\nCF Payment ID   : " + cfPaymentId + "\nSubscription ID : " + subscriptionId + "\nPayment Status  : " + paymentStatus + "\nPayment Amount  : " + paymentAmount + "\nPlan Type       : " + planType + "\n========================================\n");

            if (paymentId == null || paymentId.trim().isEmpty() || subscriptionId == null || subscriptionId.trim().isEmpty() || paymentStatus == null || paymentStatus.trim().isEmpty()) {
                System.err.println("Cashfree payment_id, subscription_id, or payment_status is missing.");
                return false;
            }

            if (paymentAmount.compareTo(BigDecimal.ZERO) < 0) {
                System.err.println("Invalid Cashfree payment amount: " + paymentAmount);
                return false;
            }

            if (!saveCashfreeWebhook(paymentId, cfPaymentId, orderId, subscriptionId, paymentAmount, failureReason, eventType, jsonStr, signature)) {
                System.err.println("Warning: raw Cashfree webhook could not be saved.");
            }

            // Execute cashfree_emi_tracking logic only for ON_DEMAND (Skip for PERIODIC)
            if ("ON_DEMAND".equalsIgnoreCase(planType)) {
                if (!updateCashfreeEmiTracking(paymentId, cfPaymentId, subscriptionId, paymentStatus, failureReason, jsonStr) && "SUCCESS".equalsIgnoreCase(paymentStatus)) {
                    System.err.println("Cashfree EMI tracking was not updated for payment_id: " + paymentId);
                    return false;
                }
            }

            boolean processed = handleCfPresentationCallback(subscriptionId, paymentId, paymentStatus, authorizationReference, failureReason, paymentAmount.doubleValue(), planType);
            System.out.println("Cashfree payment processing result: " + processed);
            return processed;

        } catch (Exception e) {
            System.err.println("Error saving Cashfree webhook: " + e.getMessage());
            e.printStackTrace();
            return false;
        }
    }

    private static String fetchPlanTypeFromDb(String subscriptionId) {
        String sql = "SELECT plan_type FROM subscriptions_overview WHERE subscription_id = ? LIMIT 1";
        try (Connection conn = DataBaseConnection.JAVAConnection(); PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, subscriptionId);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return rs.getString("plan_type");
                }
            }
        } catch (Exception e) {
            System.err.println("Error fetching plan_type from DB for sub_id: " + subscriptionId);
            e.printStackTrace();
        }
        return "";
    }

    private static BigDecimal getDecimal(JSONObject json, String key) {
        try {
            Object value = json.opt(key);
            return (value == null || String.valueOf(value).trim().isEmpty()) ? BigDecimal.ZERO : new BigDecimal(String.valueOf(value));
        } catch (Exception e) {
            return BigDecimal.ZERO;
        }
    }

    private static boolean saveCashfreeWebhook(String paymentId, String cfPaymentId, String orderId, String subscriptionId, BigDecimal paymentAmount, String failureReason, String eventType, String payload, String signature) {
        String sql = "INSERT INTO cashfree_payment_webhooks (payment_id, cf_payment_id, order_id, subscription_id, payment_amount, failure_reason, event_type, payload, signature, processed) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        try (Connection con = DataBaseConnection.JAVAConnection(); PreparedStatement ps = con.prepareStatement(sql)) {
            ps.setString(1, paymentId);
            ps.setString(2, cfPaymentId);
            ps.setString(3, orderId);
            ps.setString(4, subscriptionId);
            ps.setBigDecimal(5, paymentAmount);
            ps.setString(6, failureReason);
            ps.setString(7, eventType);
            ps.setString(8, payload);
            ps.setString(9, signature);
            ps.setBoolean(10, false);
            ps.executeUpdate();
            System.out.println("Raw Cashfree webhook saved.");
            return true;
        } catch (SQLException e) {
            System.err.println("Could not save raw Cashfree webhook: " + e.getMessage());
            return false;
        } catch (Exception e) {
            System.err.println("Error saving raw Cashfree webhook.");
            e.printStackTrace();
            return false;
        }
    }

    private static boolean updateCashfreeEmiTracking(String paymentId, String cfPaymentId, String subscriptionId, String paymentStatus, String failureReason, String webhookResponse) {
        if (paymentId == null || paymentId.trim().isEmpty()) {
            System.err.println("Payment ID is empty. Cannot update tracking.");
            return false;
        }

        String finalStatus = "SUCCESS".equalsIgnoreCase(paymentStatus) ? "PAYMENT_SUCCESS" :
                            "PENDING".equalsIgnoreCase(paymentStatus) ? "PAYMENT_PENDING" :
                            "FAILED".equalsIgnoreCase(paymentStatus) ? "PAYMENT_FAILED" :
                            "CANCELLED".equalsIgnoreCase(paymentStatus) ? "PAYMENT_CANCELLED" : "PAYMENT_UNKNOWN";

        String sql = "UPDATE cashfree_emi_tracking SET cf_payment_id = ?, payment_status = ?, webhook_response = ?, failure_reason = ?, last_action = 'WEBHOOK', status = ?, payment_received = CASE WHEN ? = 'PAYMENT_SUCCESS' THEN 1 ELSE payment_received END, payment_received_at = CASE WHEN ? = 'PAYMENT_SUCCESS' AND payment_received_at IS NULL THEN NOW() ELSE payment_received_at END, updated_at = CURRENT_TIMESTAMP WHERE payment_id = ?";
        try (Connection conn = DataBaseConnection.JAVAConnection(); PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, cfPaymentId);
            ps.setString(2, paymentStatus);
            ps.setString(3, webhookResponse);
            ps.setString(4, failureReason);
            ps.setString(5, finalStatus);
            ps.setString(6, finalStatus);
            ps.setString(7, finalStatus);
            ps.setString(8, paymentId);

            if (ps.executeUpdate() == 0) {
                System.err.println("No cashfree_emi_tracking record found for payment_id: " + paymentId);
                return false;
            }
            System.out.println("cashfree_emi_tracking updated.\nPayment ID : " + paymentId + "\nStatus     : " + finalStatus);
            return true;
        } catch (Exception e) {
            System.err.println("Error updating cashfree_emi_tracking.");
            e.printStackTrace();
            return false;
        }
    }

    public static boolean handleCfPresentationCallback(String subId, String paymentId, String status, String umrn, String reason, double amount, String planType) {
        if (paymentId == null || paymentId.trim().isEmpty() || subId == null || subId.trim().isEmpty()) {
            System.err.println("Transaction reference or Subscription ID is empty.");
            return false;
        }

        DataBaseMethods db = new DataBaseMethods();
        String loanNo = db.fetchDataBySubscriptionId(subId);
        if (loanNo == null || loanNo.trim().isEmpty()) {
            System.err.println("Loan number not found for subscription: " + subId);
            return false;
        }

        System.out.println("Cashfree payment status = " + status);
        
        // Dispatch to explicit status handler functions
        if ("SUCCESS".equalsIgnoreCase(status)) {
            return handleSuccessPayment(paymentId, subId, loanNo, umrn, amount, planType, db);
        } else if ("FAILED".equalsIgnoreCase(status)) {
            return handleFailedPayment(paymentId, loanNo, reason,amount,umrn);
        } else if ("CANCELLED".equalsIgnoreCase(status)) {
            return handleCancelledPayment(paymentId, loanNo, reason,amount,umrn);
        } else if ("PENDING".equalsIgnoreCase(status)) {
            return handlePendingPayment(paymentId);
        }

        System.out.println("Unknown payment status: " + status);
        return false;
    }
    
    private static boolean handleSuccessPayment(String merchantTrxnRefId, String subId, String loanNo, String umrn, double amount, String planType, DataBaseMethods db) {
        boolean isPeriodic = "PERIODIC".equalsIgnoreCase(planType);
        String code = "200";
        String description = "Payment successfully debited from the customer's account.";

        // Skip claim tracking check if PERIODIC
        if (!isPeriodic) {
            boolean claimed = claimSuccessfulPayment(merchantTrxnRefId);
            if (!claimed) {
                return true;
            }  
        }

        try {
            boolean isRdConnected = db.checkIfSubscriptionConnectedWithRd(subId);
            String rdNumber = null;
            double rdAmount = 0;

            if (isRdConnected) {
                rdNumber = db.findRdNumberBySubscriptionId(subId);
                if (rdNumber != null && !rdNumber.trim().isEmpty()) {
                    rdAmount = db.findRdinstallmentAmountByRdNumber(rdNumber);
                }
            }

            if (rdAmount < 0 || (isRdConnected && rdAmount > amount)) {
                if (!isPeriodic) {
                    resetSuccessfulPaymentClaim(merchantTrxnRefId);
                }
                return false;
            }

            String branchId = db.fetchBranchIdByLoanNo(loanNo);
            String loanType = resolveLoanType(loanNo);
            Date date = new Date();
            boolean res;

            if (isRdConnected) {
                double emiAmount = amount - rdAmount;

                if (emiAmount < 0) {
                    if (!isPeriodic) {
                        resetSuccessfulPaymentClaim(merchantTrxnRefId);
                    }
                    return false;
                }

                boolean nachInserted = insertNachPayment(merchantTrxnRefId, "success", code, description, amount, umrn, loanNo, rdNumber);

                if (!nachInserted) {
                    if (!isPeriodic) {
                        resetSuccessfulPaymentClaim(merchantTrxnRefId);
                    }
                    return false;
                }

                boolean loanRes = new CallbackHandlerService().insertLoanPaymentFromCashfreeEnach("admin", loanType, loanNo, date, emiAmount, "online", "pending", null, merchantTrxnRefId, description, null, branchId, umrn);
                boolean rdRes = new CallbackHandlerService().insertLoanPaymentFromCashfreeEnach("admin", "RD", rdNumber, date, rdAmount, "online", "pending", null, merchantTrxnRefId, description, null, branchId, umrn);

                res = loanRes && rdRes;

            } else {
                boolean nachInserted = insertNachPayment(merchantTrxnRefId, "success", code, description, amount, umrn, loanNo, null);

                if (!nachInserted) {
                    if (!isPeriodic) {
                        resetSuccessfulPaymentClaim(merchantTrxnRefId);
                    }
                    return false;
                }

                res = new CallbackHandlerService().insertLoanPaymentFromCashfreeEnach("admin", loanType, loanNo, date, amount, "online", "pending", null, merchantTrxnRefId, description, null, branchId, umrn);
            }

            if (res) {
                if (!isPeriodic) {
                    markEmiCompleted(merchantTrxnRefId);
                }
                markWebhookProcessed(merchantTrxnRefId);
                return true;
            }

            if (!isPeriodic) {
                resetSuccessfulPaymentClaim(merchantTrxnRefId);
            }
            return false;

        } catch (Exception e) {
            if (!isPeriodic) {
                resetSuccessfulPaymentClaim(merchantTrxnRefId);
            }
            return false;
        }
    }

    /**
     * Handler for FAILED status
     */
    private static boolean handleFailedPayment(String merchantTrxnRefId, String loanNo, String reason,double amount,String umrn) {
        if (insertNachPayment(merchantTrxnRefId, "FAILED", "202", reason, amount, umrn, loanNo, null)) {
            markWebhookProcessed(merchantTrxnRefId);
            return true;
        }  
        return false;
    }

    /**
     * Handler for CANCELLED status
     */
    private static boolean handleCancelledPayment(String merchantTrxnRefId, String loanNo, String reason,double amount,String umrn) {
          if (insertNachPayment(merchantTrxnRefId, "CANCELLED", "204", reason, amount, umrn, loanNo, null)) {
              markWebhookProcessed(merchantTrxnRefId);
              return true;
          }  
          return false;
    }

    /**
     * Handler for PENDING status
     */
    private static boolean handlePendingPayment(String merchantTrxnRefId) {
        System.out.println("Payment is pending (" + merchantTrxnRefId + "). Waiting for next webhook.");
        return true;
    }

    public static boolean updatePaymentStatusForCashfree(String merchantTrxnRefId, String status, String code, String description, double amount, String umrn, String sub_id, String planType) {
        if (merchantTrxnRefId == null || merchantTrxnRefId.trim().isEmpty() || sub_id == null || sub_id.trim().isEmpty()) {
            System.err.println("Transaction reference or Subscription ID is empty.");
            return false;
        }

        DataBaseMethods db = new DataBaseMethods();
        String loanNo = db.fetchDataBySubscriptionId(sub_id);
        if (loanNo == null || loanNo.trim().isEmpty()) {
            System.err.println("Loan number not found for subscription: " + sub_id);
            return false;
        }

        if ("success".equalsIgnoreCase(status)) {
            boolean isPeriodic = "PERIODIC".equalsIgnoreCase(planType);

            // Skip claim tracking check if PERIODIC
            if (!isPeriodic) {
                if (!claimSuccessfulPayment(merchantTrxnRefId)) {
                    System.out.println("SUCCESS already being/been processed for payment: " + merchantTrxnRefId);
                    return true;
                }
            }

            try {
                boolean isRdConnected = db.checkIfSubscriptionConnectedWithRd(sub_id);
                String rdNumber = null;
                double rdAmount = 0;

                if (isRdConnected) {
                    rdNumber = db.findRdNumberBySubscriptionId(sub_id);
                    if (rdNumber != null && !rdNumber.trim().isEmpty()) {
                        rdAmount = db.findRdinstallmentAmountByRdNumber(rdNumber);
                    }
                }

                if (rdAmount < 0 || (isRdConnected && rdAmount > amount)) {
                    System.err.println("Invalid or excessive RD installment amount: " + rdAmount);
                    if (!isPeriodic) resetSuccessfulPaymentClaim(merchantTrxnRefId);
                    return false;
                }

                String branchId = db.fetchBranchIdByLoanNo(loanNo);

                String loanType =
                        resolveLoanType(loanNo);
                Date date = new Date();
                boolean res;

                if (isRdConnected) {
                    double emiAmount = amount - rdAmount;
                    if (emiAmount < 0) {
                        System.err.println("EMI amount became negative.");
                        if (!isPeriodic) resetSuccessfulPaymentClaim(merchantTrxnRefId);
                        return false;
                    }

                    if (!insertNachPayment(merchantTrxnRefId, status, code, description, amount, umrn, loanNo, rdNumber)) {
                        System.err.println("Failed to insert nach_payments.");
                        if (!isPeriodic) resetSuccessfulPaymentClaim(merchantTrxnRefId);
                        return false;
                    }

                    boolean loanRes = new CallbackHandlerService().insertLoanPaymentFromApp("admin", loanType, loanNo, date, emiAmount, "online", "pending", null, merchantTrxnRefId, description, null, branchId, umrn);
                    boolean rdRes = new CallbackHandlerService().insertLoanPaymentFromApp("admin", "RD", rdNumber, date, rdAmount, "online", "pending", null, merchantTrxnRefId, description, null, branchId, umrn);
                    res = loanRes && rdRes;
                } else {
                    if (!insertNachPayment(merchantTrxnRefId, status, code, description, amount, umrn, loanNo, null)) {
                        System.err.println("Failed to insert nach_payments.");
                        if (!isPeriodic) resetSuccessfulPaymentClaim(merchantTrxnRefId);
                        return false;
                    }
                    res = new CallbackHandlerService().insertLoanPaymentFromApp("admin", loanType, loanNo, date, amount, "online", "pending", null, merchantTrxnRefId, description, null, branchId, umrn);
                }

                if (res) {
                    if (!isPeriodic) markEmiCompleted(merchantTrxnRefId);
                    markWebhookProcessed(merchantTrxnRefId);
                    return true;
                }

                System.err.println("Business payment processing failed. Releasing payment claim for retry: " + merchantTrxnRefId);
                if (!isPeriodic) resetSuccessfulPaymentClaim(merchantTrxnRefId);
                return false;

            } catch (Exception e) {
                System.err.println("Exception while processing successful Cashfree payment: " + merchantTrxnRefId);
                e.printStackTrace();
                if (!isPeriodic) resetSuccessfulPaymentClaim(merchantTrxnRefId);
                return false;
            }
        }

        if ("pending".equalsIgnoreCase(status)) {
            System.out.println("Payment is pending. Waiting for next webhook.");
            return true;
        }

        if ("failed".equalsIgnoreCase(status) || "cancelled".equalsIgnoreCase(status)) {
            if (new CallbackHandlerService().insertManualChargeRecord(loanNo, false)) {
                markWebhookProcessed(merchantTrxnRefId);
                return true;
            }
            return false;
        }

        System.out.println("Unknown payment status: " + status);
        return false;
    }
    
   
    private static boolean insertNachPayment(String merchantTrxnRefId, String status, String code, String description, double amount, String umrn, String loanNo, String rdNumber) {
        if (merchantTrxnRefId == null || merchantTrxnRefId.trim().isEmpty() || loanNo == null || loanNo.trim().isEmpty()) {
            return false;
        }

        String loanReference = (rdNumber != null && !rdNumber.trim().isEmpty()) ? loanNo + " + " + rdNumber : loanNo;
        String sql = "INSERT INTO nach_payments (merchant_trxn_ref_id, status, code, description, callback_type, amount, umrn, loan_no) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";

        try (Connection conn = DataBaseConnection.JAVAConnection(); 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.setBigDecimal(6, BigDecimal.valueOf(amount));
            ps.setString(7, umrn);
            ps.setString(8, loanReference);
            ps.executeUpdate();
            System.out.println("nach_payments inserted.\nTransaction Ref : " + merchantTrxnRefId);
            return true;
        } catch (Exception e) {
            System.err.println("Error inserting nach_payments.");
            e.printStackTrace();
            return false;
        }
    }

    private static boolean claimSuccessfulPayment(String paymentId) {
        if (paymentId == null || paymentId.trim().isEmpty()) return false;
        String sql = "UPDATE cashfree_emi_tracking SET emi_approved = 1, last_action = 'SUCCESS_PROCESSING', updated_at = CURRENT_TIMESTAMP WHERE payment_id = ? AND status = 'PAYMENT_SUCCESS' AND COALESCE(emi_approved, 0) = 0";

        try (Connection conn = DataBaseConnection.JAVAConnection(); PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, paymentId);
            int rows = ps.executeUpdate();
            System.out.println(rows == 1 ? "Payment successfully claimed: " + paymentId : "Payment already claimed or tracking record not eligible: " + paymentId);
            return rows == 1;
        } catch (Exception e) {
            System.err.println("Error claiming successful payment.");
            e.printStackTrace();
            return false;
        }
    }

    private static void resetSuccessfulPaymentClaim(String paymentId) {
        if (paymentId == null || paymentId.trim().isEmpty()) return;
        String sql = "UPDATE cashfree_emi_tracking SET emi_approved = 0, last_action = 'SUCCESS_RETRY', updated_at = CURRENT_TIMESTAMP WHERE payment_id = ? AND COALESCE(emi_completed, 0) = 0";

        try (Connection conn = DataBaseConnection.JAVAConnection(); PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, paymentId);
            int rows = ps.executeUpdate();
            System.out.println("Payment claim reset for retry: " + paymentId + " rows=" + rows);
        } catch (Exception e) {
            System.err.println("Error resetting successful payment claim.");
            e.printStackTrace();
        }
    }

    private static void markEmiCompleted(String paymentId) {
        String sql = "UPDATE cashfree_emi_tracking SET emi_completed = 1, payment_received = 1, payment_completed_at = NOW(), last_action = 'PAYMENT_COMPLETED', status = 'PAYMENT_SUCCESS', updated_at = CURRENT_TIMESTAMP WHERE payment_id = ?";
        try (Connection conn = DataBaseConnection.JAVAConnection(); PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, paymentId);
            int rows = ps.executeUpdate();
            System.out.println("EMI marked completed: " + paymentId + " rows=" + rows);
        } catch (Exception e) {
            System.err.println("Error marking EMI completed.");
            e.printStackTrace();
        }
    }

    private static void markWebhookProcessed(String paymentId) {
        if (paymentId == null || paymentId.trim().isEmpty()) return;
        String sql = "UPDATE cashfree_payment_webhooks SET processed = 1 WHERE payment_id = ?";
        try (Connection conn = DataBaseConnection.JAVAConnection(); PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, paymentId);
            int rows = ps.executeUpdate();
            System.out.println("Webhook marked processed. Payment ID=" + paymentId + " rows=" + rows);
        } catch (Exception e) {
            System.err.println("Error marking webhook processed.");
            e.printStackTrace();
        }
    }

   
    private static class SubscriptionData {

        String subscriptionId;
        String subscriptionSessionId;   // Cashfree subscription session ID
        String cfSubscriptionId;

        String subscriptionStatus;
        String expiryTime;
        String firstChargeTime;
        String nextScheduleDate;

        JSONObject customer;
        JSONObject plan;
        JSONObject auth;
        JSONObject tags;
    }
   


    
    public static void insertSubscription(
            String jsonResponse,
            String userid,
            String loanNo,
            boolean rd_connected,
            String rd_number) {

        SubscriptionData d = extractSubscriptionData(jsonResponse);

        if (d == null
                || d.subscriptionId == null
                || d.subscriptionId.trim().isEmpty()) {

            System.err.println(
                    "Cannot insert subscription. subscription_id is missing.");
            return;
        }

        try (Connection conn = DataBaseConnection.JAVAConnection()) {

            // Check whether subscription already exists
            String checkSql =
                    "SELECT id FROM subscriptions_overview " +
                    "WHERE subscription_id = ? LIMIT 1";

            boolean exists = false;

            try (PreparedStatement checkPs =
                         conn.prepareStatement(checkSql)) {

                checkPs.setString(1, d.subscriptionId);

                try (ResultSet rs = checkPs.executeQuery()) {
                    if (rs.next()) {
                        exists = true;
                    }
                }
            }

            // If already exists, update instead of inserting
            if (exists) {
                updateSubscription(jsonResponse);
                return;
            }

            String sql =
                    "INSERT INTO subscriptions_overview (" +
                    "subscription_id, " +
                    "subscription_session_id, " +
                    "cf_subscription_id, " +
                    "plan_id, " +
                    "plan_name, " +
                    "plan_type, " +
                    "plan_currency, " +
                    "plan_recurring_amount, " +
                    "plan_interval_type, " +
                    "plan_intervals, " +
                    "plan_max_cycles, " +
                    "plan_max_amount, " +
                    "customer_name, " +
                    "customer_email, " +
                    "customer_phone, " +
                    "subscription_status, " +
                    "subscription_first_charge_time, " +
                    "next_schedule_date, " +
                    "subscription_expiry_time, " +
                    "psp_note, " +
                    "user_id, " +
                    "loan_no, " +
                    "rd_connected, " +
                    "rd_number" +
                    ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

            try (PreparedStatement ps =
                         conn.prepareStatement(sql)) {

                int i = 1;

                // Subscription
                ps.setString(i++, d.subscriptionId);

                // NEW: Cashfree subscription session ID
                ps.setString(i++, d.subscriptionSessionId);

                // Cashfree subscription ID
                ps.setString(i++, d.cfSubscriptionId);

                // Plan details
                ps.setString(i++, getJsonString(d.plan, "plan_id"));
                ps.setString(i++, getJsonString(d.plan, "plan_name"));
                ps.setString(i++, getJsonString(d.plan, "plan_type"));
                ps.setString(i++, getJsonString(d.plan, "plan_currency"));
                ps.setBigDecimal(i++,
                        getJsonDecimal(d.plan, "plan_recurring_amount"));
                ps.setString(i++,
                        getJsonString(d.plan, "plan_interval_type"));
                ps.setInt(i++,
                        getPlanInt(d.plan, "plan_intervals"));
                ps.setInt(i++,
                        getPlanInt(d.plan, "plan_max_cycles"));
                ps.setBigDecimal(i++,
                        getJsonDecimal(d.plan, "plan_max_amount"));

                // Customer details
                ps.setString(i++,
                        getJsonString(d.customer, "customer_name"));
                ps.setString(i++,
                        getJsonString(d.customer, "customer_email"));
                ps.setString(i++,
                        getJsonString(d.customer, "customer_phone"));

                // Subscription status
                ps.setString(i++, d.subscriptionStatus);

                // Dates
                ps.setString(i++, formatDateTime(d.firstChargeTime));
                ps.setString(i++, formatDateTime(d.nextScheduleDate));
                ps.setString(i++, formatDateTime(d.expiryTime));

                // Tags
                ps.setString(i++,
                        getJsonString(d.tags, "psp_note"));

                // Your application details
                ps.setString(i++, userid);
                ps.setString(i++, loanNo);
                ps.setBoolean(i++, rd_connected);
                ps.setString(i++, rd_number);

                ps.executeUpdate();

                System.out.println(
                        "Subscription inserted: " + d.subscriptionId);

                System.out.println(
                        "Subscription session ID: "
                        + d.subscriptionSessionId);
            }

        } catch (Exception e) {

            System.err.println(
                    "Error inserting subscription.");

            e.printStackTrace();
        }
    }
  


    public static void updateSubscription(String jsonResponse) {
        SubscriptionData d = extractSubscriptionData(jsonResponse);
        if (d == null || d.subscriptionId == null || d.subscriptionId.trim().isEmpty()) {
            System.err.println("Cannot update subscription. subscription_id is missing.");
            return;
        }

        String sql = "UPDATE subscriptions_overview SET authorization_reference=?, authorization_status=?, authorization_amount=?, authorization_amount_refund=?, authorization_time=?, payment_id=?, payment_group=?, payment_mode=?, subscription_status=?, next_schedule_date=?, subscription_expiry_time=?, psp_note=?, updated_at=CURRENT_TIMESTAMP WHERE subscription_id=?";
        try (Connection conn = DataBaseConnection.JAVAConnection(); PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, getJsonString(d.auth, "authorization_reference"));
            ps.setString(2, getJsonString(d.auth, "authorization_status"));
            ps.setBigDecimal(3, getJsonDecimal(d.auth, "authorization_amount"));
            ps.setBoolean(4, d.auth != null && d.auth.optBoolean("authorization_amount_refund", false));
            ps.setString(5, formatDateTime(getJsonString(d.auth, "authorization_time")));
            ps.setString(6, getJsonString(d.auth, "payment_id"));
            ps.setString(7, getJsonString(d.auth, "payment_group"));
            ps.setString(8, extractPaymentMode(d.auth));
            ps.setString(9, d.subscriptionStatus);
            ps.setString(10, formatDateTime(d.nextScheduleDate));
            ps.setString(11, formatDateTime(d.expiryTime));
            ps.setString(12, getJsonString(d.tags, "psp_note"));
            ps.setString(13, d.subscriptionId);

            int rows = ps.executeUpdate();
            System.out.println("Subscription updated: " + d.subscriptionId + " rows=" + rows);
        } catch (Exception e) {
            System.err.println("Error updating subscription.");
            e.printStackTrace();
        }
    }

    private static SubscriptionData extractSubscriptionData(String jsonResponse) {

        SubscriptionData d = new SubscriptionData();

        if (jsonResponse == null || jsonResponse.trim().isEmpty()) {
            System.err.println("Subscription JSON is empty.");
            return d;
        }

        try {

            JSONObject json = new JSONObject(jsonResponse);

            /*
             * ============================================================
             * CASE 1:
             * Direct Cashfree subscription response
             *
             * {
             *   "subscription_id": "...",
             *   "subscription_session_id": "...",
             *   "cf_subscription_id": "...",
             *   ...
             * }
             * ============================================================
             */
            if (json.has("subscription_id")) {

                d.subscriptionId =
                        json.optString("subscription_id", null);

                d.subscriptionSessionId =
                        json.optString("subscription_session_id", null);

                d.cfSubscriptionId =
                        json.optString("cf_subscription_id", null);

                d.subscriptionStatus =
                        json.optString("subscription_status", null);

                d.expiryTime =
                        json.optString("subscription_expiry_time", null);

                d.firstChargeTime =
                        json.optString(
                                "subscription_first_charge_time",
                                null);

                d.nextScheduleDate =
                        json.optString(
                                "next_schedule_date",
                                null);

                d.customer =
                        json.optJSONObject("customer_details");

                d.plan =
                        json.optJSONObject("plan_details");

                d.auth =
                        json.optJSONObject("authorization_details");

                d.tags =
                        json.optJSONObject("subscription_tags");
            }


            /*
             * ============================================================
             * CASE 2:
             * Nested response
             *
             * {
             *   "data": {
             *      "subscription_details": {
             *          ...
             *      }
             *   }
             * }
             * ============================================================
             */
            else if (json.has("data")) {

                JSONObject inner =
                        json.optJSONObject("data");

                if (inner != null) {

                    JSONObject subscriptionDetails =
                            inner.optJSONObject("subscription_details");

                    if (subscriptionDetails != null) {

                        d.subscriptionId =
                                subscriptionDetails.optString(
                                        "subscription_id",
                                        null);

                        d.subscriptionSessionId =
                                subscriptionDetails.optString(
                                        "subscription_session_id",
                                        null);

                        d.cfSubscriptionId =
                                subscriptionDetails.optString(
                                        "cf_subscription_id",
                                        null);

                        d.subscriptionStatus =
                                subscriptionDetails.optString(
                                        "subscription_status",
                                        null);

                        d.expiryTime =
                                subscriptionDetails.optString(
                                        "subscription_expiry_time",
                                        null);

                        d.firstChargeTime =
                                subscriptionDetails.optString(
                                        "subscription_first_charge_time",
                                        null);

                        d.nextScheduleDate =
                                subscriptionDetails.optString(
                                        "next_schedule_date",
                                        null);

                        d.customer =
                                subscriptionDetails.optJSONObject(
                                        "customer_details");

                        d.plan =
                                subscriptionDetails.optJSONObject(
                                        "plan_details");

                        d.auth =
                                subscriptionDetails.optJSONObject(
                                        "authorization_details");

                        d.tags =
                                subscriptionDetails.optJSONObject(
                                        "subscription_tags");
                    }

                    /*
                     * Sometimes customer/plan/auth may be directly
                     * under data instead of subscription_details.
                     */
                    if (d.customer == null) {
                        d.customer =
                                inner.optJSONObject("customer_details");
                    }

                    if (d.plan == null) {
                        d.plan =
                                inner.optJSONObject("plan_details");
                    }

                    if (d.auth == null) {
                        d.auth =
                                inner.optJSONObject("authorization_details");
                    }

                    if (d.tags == null) {
                        d.tags =
                                inner.optJSONObject("subscription_tags");
                    }

                    /*
                     * Fallback in case subscription_session_id is
                     * directly under data.
                     */
                    if (d.subscriptionSessionId == null
                            || d.subscriptionSessionId.trim().isEmpty()) {

                        d.subscriptionSessionId =
                                inner.optString(
                                        "subscription_session_id",
                                        null);
                    }
                }
            }


            /*
             * ============================================================
             * DEBUG
             * ============================================================
             */

            System.out.println(
                    "========== EXTRACTED SUBSCRIPTION ==========");

            System.out.println(
                    "subscription_id       : "
                    + d.subscriptionId);

            System.out.println(
                    "subscription_session_id: "
                    + d.subscriptionSessionId);

            System.out.println(
                    "cf_subscription_id    : "
                    + d.cfSubscriptionId);

            System.out.println(
                    "subscription_status   : "
                    + d.subscriptionStatus);

            System.out.println(
                    "expiry_time           : "
                    + d.expiryTime);

            System.out.println(
                    "first_charge_time     : "
                    + d.firstChargeTime);

            System.out.println(
                    "next_schedule_date    : "
                    + d.nextScheduleDate);

            System.out.println(
                    "=============================================");


            /*
             * ============================================================
             * IMPORTANT VALIDATION
             * ============================================================
             */

            if (d.subscriptionId == null
                    || d.subscriptionId.trim().isEmpty()) {

                System.err.println(
                        "WARNING: subscription_id was not found.");
            }

            if (d.subscriptionSessionId == null
                    || d.subscriptionSessionId.trim().isEmpty()) {

                System.err.println(
                        "WARNING: subscription_session_id was not found.");
            } else {

                System.out.println(
                        "Cashfree Session ID successfully extracted.");
            }


        } catch (Exception e) {

            System.err.println(
                    "Error parsing subscription JSON: "
                    + e.getMessage());

            e.printStackTrace();
        }

        return d;
    }
    private static String extractPaymentMode(JSONObject auth) {
        if (auth == null) return null;
        JSONObject paymentMethod = auth.optJSONObject("payment_method");
        if (paymentMethod == null) return null;

        JSONObject enach = paymentMethod.optJSONObject("enach");
        if (enach != null) {
            String channel = enach.optString("channel", null);
            return (channel != null && !channel.trim().isEmpty()) ? channel : "ENACH";
        }

        JSONObject upi = paymentMethod.optJSONObject("upi");
        if (upi != null) {
            String channel = upi.optString("channel", null);
            return (channel != null && !channel.trim().isEmpty()) ? channel : "UPI";
        }

        return null;
    }

    private static String getJsonString(JSONObject json, String key) {
        if (json == null || !json.has(key)) return null;
        String value = json.optString(key, null);
        return (value == null || value.trim().isEmpty() || "null".equalsIgnoreCase(value.trim())) ? null : value;
    }

    private static BigDecimal getJsonDecimal(JSONObject json, String key) {
        if (json == null || !json.has(key)) return BigDecimal.ZERO;
        try {
            Object value = json.opt(key);
            return (value == null || String.valueOf(value).trim().isEmpty()) ? BigDecimal.ZERO : new BigDecimal(String.valueOf(value));
        } catch (Exception e) {
            return BigDecimal.ZERO;
        }
    }

    private static int getPlanInt(JSONObject plan, String key) {
        if (plan == null || !plan.has(key)) return 0;
        try {
            return plan.optInt(key, 0);
        } catch (Exception e) {
            return 0;
        }
    }

    public static String formatDateTime(String dateTime) {
        if (dateTime == null || dateTime.trim().isEmpty()) return null;
        String value = dateTime.trim().replace("T", " ");
        if (value.endsWith("Z")) value = value.substring(0, value.length() - 1);
        value = value.replaceFirst("[+-][0-9]{2}:[0-9]{2}$", "").replaceFirst("\\.[0-9]+$", "");
        return value.length() > 19 ? value.substring(0, 19) : value;
    }
    
    private static 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 "secureloan";
        }

        if (value.startsWith("GL")) {
            return "grouploan";
        }

        return null;
    }


}