try {
  const connection = await mysql.connection();
  await connection.query("START TRANSACTION");
  let inputVal=0;

  const finalAmount =
    data.providerCode === "TVL" || data.providerCode === "PDTV"
      ? data.amountLKR
      : data.amountINR;

  let _reason, _reasonCode, _rid, _refNo;
  let RechargeStatus, finalroundofamount;
  finalroundofamount = Math.ceil(finalAmount);
  if (finalroundofamount < 10) {
    finalroundofamount = 10;
  }

  if (data.providerCode === "dsd") {
    const validAmounts = [
      "179.00",
      "209.00",
      "239.00",
      "318.00",
      "225.00",
      "271.00",
    ];
    if (!validAmounts.includes(data.amountINR)) {
      throw new Error(
        "Invalid amount for Sun Direct provider. Valid amounts are: INR 179 / 209 / 239 / 318 for SD or INR 225.00 or 271.00 for HD"
      );
    }
  }

  try {
    const url = `${
      process.env.QUICK_PAY_URL
    }provider=${data.providerCode.trim()}&number=${
      data.vcNumber
    }&amount=${finalroundofamount}&refno=${data.ref_no}&format=JSON`;
    const res = await axios.get(url);
    RechargeStatus = res.data.RechargeStatus;
    _reason = RechargeStatus.reason;
    _reasonCode = RechargeStatus.reasoncode;
    _rid = RechargeStatus.rid || "NA";
    _refNo = RechargeStatus.refno;
  } catch (error) {
    _reason = "QUICK_PAY server error";
    _reasonCode = "error";
  }

  let _msg = "";
  let _orderStatus = ""; // for quick pay

  if (_reasonCode === "000") {
    _orderStatus = "success";
    _msg = `VC No. ${data.vcNumber} has been successfully recharged on ${data.transactionDate} for Rs.${data.amountLKR}. Your ref No. ${_refNo}`;
  } else {
    _rid = "NA";
    _orderStatus = "failed";
    _msg = `VC No. ${data.vcNumber} unable to complete the recharge process at the moment on ${data.transactionDate} for Rs.${data.amountLKR}. Your ref No. ${_refNo}`;
  }

  let rechargeInfo = {
    dth_operator: data.provider,
    package: data.rechargeType,
    vc_number: data.vcNumber,
    price_lkr: data.amountLKR,
    price_inr: data.amountINR,
    customer_id: data.customerId,
    transaction_status: data.transactionStatus,
    transaction_date: data.transactionDate,
    transaction_id: data.transactionId,
    order_status: _orderStatus,
    order_type: data.orderType,
    rid: _rid,
    ref_no: data.ref_no,
    reason: _reason,
    transaction_by: data.transactionBy,
    updated_on: mysql.CURRENT_TIMESTAMP,
  };

  await connection.query("INSERT INTO recharges SET ?", rechargeInfo);

  if (data.orderType === "wallet" && _reasonCode === "000") {
    const newBalance = Number(data.walletBalance) - Number(data.amountLKR);
    let walletInfo = {
      amount: data.amountLKR,
      balance: newBalance,
      type: "used",
      customer_id: data.customerId,
      status: _reason,
      transaction_id: data.transactionId,
      transaction_date: data.transactionDate,
      updated_on: mysql.CURRENT_TIMESTAMP,
    };

    await connection.query("INSERT INTO customer_wallet SET ?", walletInfo);
  } else if (data.orderType !== "wallet" && _reasonCode !== "000") {
    const walletCheckQuery =
      "SELECT * FROM `customer_wallet` WHERE customer_id = ?";
    const [walletCheckRows] = await connection.query(walletCheckQuery, [
      data.customerId,
    ]);

    const lastWalletQuery =
      "SELECT * FROM `customer_wallet` WHERE customer_id = ? ORDER BY id DESC LIMIT 1";
    const [lastWalletRows] = await connection.query(lastWalletQuery, [
      data.customerId,
    ]);
    const currentBal = lastWalletRows[0].balance;
    inputVal = currentBal + data.amountLKR;

    if (walletCheckRows.length > 0) {
      const insertWalletQuery =
        "INSERT INTO customer_wallet (amount, balance, type, customer_id, status, transaction_id, transaction_date, waltype) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
      await connection.query(insertWalletQuery, [
        data.amountLKR,
        inputVal,
        "topup",
        data.customerId,
        "Recharge Fail - " + data.ref_no,
        data.transactionId,
        data.transactionDate,
        "system",
      ]);
    } else {
      const insertWalletQuery =
        "INSERT INTO customer_wallet (amount, balance, type, customer_id, status, transaction_id, transaction_date, waltype) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
      await connection.query(insertWalletQuery, [
        data.amountLKR,
        inputVal,
        "topup",
        data.customerId,
        "Recharge Fail - " + data.ref_no,
        data.transactionId,
        data.transactionDate,
        "system",
      ]);
    }
  }

  if (data.fallback_amount && Number(data.fallback_amount) > 0) {
    await walletTopUp(data);
  }

  if (data.rechargeType === "package-recharge") {
    const packageInfo = {
      dth_operator: data.provider,
      package: data.package,
      language: data.language,
      vc_number: data.vcNumber,
      lkr_amount: data.amountLKR,
      inr_amount: data.amountINR,
      order_date: "", // ?
      expiry_date: "", // ?
      ref_no: data.ref_no,
      order_status: _orderStatus,
      reason: _reason,
    };
    await connection.query(
      "INSERT INTO package_recharges SET ?",
      packageInfo
    );

    if (data.customerAddons) {
      const addonsInfo = {
        addons: data.customerAddons.addons || "",
        addon_price: data.customerAddons.addons_price || "",
        reference: data.ref_no,
        vc_number: data.vcNumber,
        order_date: "",
        expiry_date: "",
        updated_on: mysql.CURRENT_TIMESTAMP,
      };
      await connection.query(
        "INSERT INTO customer_addons SET ?",
        addonsInfo
      );
    }

    if (data.customerChannels) {
      const channelsInfo = {
        channel: data.customerChannels.channels || "",
        channel_price: data.customerChannels.channels_price || "",
        reference: data.ref_no,
        vc_number: data.vcNumber,
        order_date: "",
        expiry_date: "",
        updated_on: mysql.CURRENT_TIMESTAMP,
      };
      await connection.query(
        "INSERT INTO customer_channels SET ?",
        channelsInfo
      );
    }
  }

  //do all cashbacks here
  const customer = await connection.query(
    "SELECT contact_no,referral FROM customers WHERE id = ?",
    [data.customerId]
  );
  const pro = await connection.query(
    "SELECT provider.*,dth_api.* FROM provider left join dth_api ON provider.dthapi=dth_api.id WHERE provider.provider='" +
      data.provider +
      "'"
  );
  const provider = pro[0];
  let newBalance = 0;

  if (
    provider?.cashback == 1 &&
    parseFloat(provider?.cashback_percentage) > 0 &&
    data.orderType !== "wallet" &&
    _reasonCode !== "000"
  ) {
    let cashback =
      (data.amountLKR / 100) * parseFloat(provider?.cashback_percentage);
      newBalance=inputVal + cashback;

    let walletInfo = {
      amount: cashback,
      balance: newBalance,
      type: "cashback",
      customer_id: data.customerId,
      status: `Cashback for order ${data.ref_no}`,
      transaction_id: "NA",
      transaction_date: new Date().toISOString(),
      updated_on: mysql.CURRENT_TIMESTAMP,
    };

    await connection.query("INSERT INTO customer_wallet SET ?", walletInfo);

    const msg = `Cashback ${cashback}LKR added to your wallet. Wallet Balance ${newBalance}`;

    await sendWhatsappSMS(customer[0]["contact_no"], msg, data.customerId);
    await sendSMS(customer[0]["contact_no"], msg, data.customerId);
  }

  const ref = customer[0]["referral"];

  if (ref && _reasonCode !== "000") {
    let set = await connection.query("SELECT * FROM refreral_settings");
    const settings = set[0];

    if (settings.status == 1) {
      let refcashback =
        (data.amountLKR / 100) * parseFloat(settings?.percentage);
      const newBalance2 = newBalance + refcashback;

      let walletInfo = {
        amount: refcashback,
        balance: newBalance2,
        type: "refferal cashback",
        customer_id: data.customerId,
        status: `Refferel cashback for order ${data.ref_no}`,
        transaction_id: "NA",
        transaction_date: new Date().toISOString(),
        updated_on: mysql.CURRENT_TIMESTAMP,
      };

      await connection.query(
        "INSERT INTO customer_wallet SET ?",
        walletInfo
      );
      let checkMobileNum = await connection.query(
        "SELECT contact_no FROM customers WHERE id = ?",
        [data.customerId]
      );
      const msg = `Refferal cashback ${refcashback}LKR added to your wallet. Wallet Balance ${newBalance2}`;
      await sendSMS(checkMobileNum[0]["contact_no"], msg, data.customerId);
      await sendWhatsappSMS(
        checkMobileNum[0]["contact_no"],
        msg,
        data.customerId
      );
    }
  }
  //end

  await connection.query("COMMIT");
  return RechargeStatus;
} catch (err) {
  console.log('here is the error',err);
  await connection.query("ROLLBACK");
  console.error("ROLLBACK at DTHRechargeService", err);
  throw err;
} finally {
  await connection.release();
}