defmodule DaProductAppWeb.MerchantApiController do
  use DaProductAppWeb, :controller
  require Logger
  alias DaProductApp.Groups.Group
  alias DaProductApp.Brands.Brand
  alias DaProductApp.Stores.Store
  alias DaProductApp.Addresses.Address
  alias DaProductApp.Repo
  alias DaProductApp.PosTerminals.PosTerminal
  alias DaProductApp.Transactions.Transaction
  alias DaProductApp.Transactions.TransactionOperation
  import Ecto.Query

  def get_store_detail(conn, %{"merchantRefId" => code} = _params) do
    Logger.info("Fetching store details for merchantRefId: #{code}")

    query =
      from(g in Group,
        where: g.code == ^code,
        join: b in Brand,
        on: b.group_id == g.id,
        join: s in Store,
        on: s.brand_id == b.id,
        select: %{
          store_id: s.id,
          store_name: s.name,
          store_code: s.code,
          brand_id: b.id,
          brand_name: b.name,
          neo_merchant_id: s.neo_merchant_id,
          address_id: s.address_id,
          created_at: s.inserted_at,
          updated_at: s.updated_at
        }
      )

    case Repo.all(query) do
      [] ->
        conn
        |> put_status(:not_found)
        |> json(%{
          status: "error",
          count: 0,
          message: "No stores found for the provided merchantRefId",
          data: %{
            stores: []
          }
        })

      stores ->
        conn
        |> put_status(:ok)
        |> json(%{
          status: "success",
          count: length(stores),
          message: "#{length(stores)} store(s) retrieved successfully",
          data: %{
            stores: stores
          }
        })
    end
  end

  def get_store_detail(conn, _params) do
    conn
    |> put_status(:bad_request)
    |> json(%{
      status: "error",
      count: 0,
      message: "merchantRefId is required",
      data: %{
        stores: []
      }
    })
  end

  def get_store_details(conn, %{"store_id" => store_id}) do
    case Repo.get(DaProductApp.Stores.Store, store_id) do
      nil ->
        conn
        |> put_status(:not_found)
        |> json(%{
          status: "error",
          message: "Store not found",
          store_id: store_id
        })

      store ->
        address =
          if store.address_id do
            Repo.get(DaProductApp.Addresses.Address, store.address_id)
          else
            nil
          end

        conn
        |> put_status(:ok)
        |> json(%{
          status: "success",
          store: %{
            id: store.id,
            code: store.code,
            name: store.name,
            brand_id: store.brand_id,
            neo_merchant_id: store.neo_merchant_id,
            address:
              address &&
                %{
                  id: address.id,
                  line1: address.line1,
                  line2: address.line2,
                  line3: address.line3,
                  line4: address.line4,
                  city: address.city,
                  state: address.state,
                  country: address.country,
                  zipcode: address.zipcode,
                  country_code: address.country_code
                }
          }
        })
    end
  end

  def get_transaction_refund_details(conn, %{"transaction_id" => transaction_id, "operation_type" => operation_type}) do
    Logger.info("Fetching refund details for transaction_id: #{transaction_id}")

    query =
      from(to in TransactionOperation,
        where: to.transaction_id == ^transaction_id,
        where: to.operation_type == ^operation_type,
        select: %{
          id: to.id,
          operation_amount_value: to.operation_amount_value,
          operation_amount_currency: to.operation_amount_currency,
          status: to.status,
          transaction_id: to.transaction_id,
          operation_type: to.operation_type,
          operation_id: to.operation_id,
          operation_request_id: to.operation_request_id,
          result_status: to.result_status,
          result_code: to.result_code,
          result_message: to.result_message,
          operation_time: to.operation_time,
          provider_response: to.provider_response,
          inserted_at: to.inserted_at,
          updated_at: to.updated_at
        }
      )

    case Repo.all(query) do
      [] ->
        conn
        |> put_status(:not_found)
        |> json(%{
          status: "error",
          message: "Transaction not found",
          transaction_id: transaction_id
        })

      transaction_operation ->
        conn
        |> put_status(:ok)
        |> json(%{
          status: "success",
          message: "Transaction Refund details retrieved successfully",
          data: %{
            transaction_operation: transaction_operation
          }
        })
    end
  end


def get_device_detail(conn, %{"merchantRefId" => code} = _params) do
  Logger.info("Fetching device details for merchantRefId: #{code}")

  # First get all devices
  devices_query =
    from(g in Group,
      where: g.code == ^code,
      join: b in Brand,
      on: b.group_id == g.id,
      join: s in Store,
      on: s.brand_id == b.id,
      join: pt in PosTerminal,
      on: pt.store_id == s.id,
      select: %{
        device_id: pt.id,
        name: pt.name,
        serial_number: pt.serial_number,
        device_type: pt.device_type,
        terminal_id: pt.terminal_id,
        terminalid: pt.terminalid,
        terminalidmerchantid: pt.terminalidmerchantid,
        provider_id: pt.provider_id,
        store: %{
          store_id: s.id,
          store_name: s.name,
          store_code: s.code,
          brand_id: b.id,
          brand_name: b.name,
          neo_merchant_id: s.neo_merchant_id,
          address_id: s.address_id
        },
        brand: %{
          id: b.id,
          name: b.name,
          code: b.code
        },
        created_at: pt.inserted_at,
        updated_at: pt.updated_at
      }
    )

  case Repo.all(devices_query) do
    [] ->
      conn
      |> put_status(:not_found)
      |> json(%{
        status: "error",
        deviceCount: 0,
        message: "No devices found for the provided merchantRefId",
        data: %{
          devices: []
        }
      })

    devices ->
      # Now fetch all Shukria terminals for each device
      enriched_devices = Enum.map(devices, fn device ->
        # Convert device_id to string for comparison
        device_id_string = to_string(device.device_id)

        shukria_terminals =
          from(st in DaProductApp.ShukriaTerminal,
            where: st.shukria_terminal_id == ^device_id_string,
            join: pt in PosTerminal,
            on: pt.id == type(st.shukria_terminal_id, :integer),
            join: s in Store,
            on: s.id == pt.store_id,
            select: %{
              id: st.id,
              shukria_terminal_id: pt.terminal_id,
              provider_id: st.provider_id,
              provider_tid: st.provider_tid,
              provider_mid: st.provider_mid,
              status: st.status,
              description: st.description,
              shukria_mid: pt.terminalidmerchantid,
              ysp_tid: st.ysp_tid,
              ysp_mid: st.ysp_mid,
              inserted_at: st.inserted_at,
              updated_at: st.updated_at
            }
          ) |> Repo.all()

        # Add shukria_terminals and shukria_terminals_count to device
        device
        |> Map.put(:shukria_terminals, shukria_terminals)
        |> Map.put(:shukria_terminals_count, length(shukria_terminals))
      end)

      conn
      |> put_status(:ok)
      |> json(%{
        status: "success",
        deviceCount: length(enriched_devices),
        message: "#{length(enriched_devices)} device(s) retrieved successfully",
        data: %{
          devices: enriched_devices
        }
      })
  end
end


  def get_device_detail(conn, _params) do
    conn
    |> put_status(:bad_request)
    |> json(%{
      status: "error",
      deviceCount: 0,
      message: "merchantRefId is required",
      data: %{
        devices: []
      }
    })
  end

  def get_unique_device_detail(conn, %{"merchantRefId" => code} = _params) do
    Logger.info("Fetching device details for merchantRefId: #{code}")

    query =
      from(g in Group,
        where: g.code == ^code,
        join: b in Brand,
        on: b.group_id == g.id,
        join: s in Store,
        on: s.brand_id == b.id,
        join: pt in PosTerminal,
        on: pt.store_id == s.id,
        select: %{
          device_id: pt.id,
          name: pt.name,
          serial_number: pt.serial_number,
          device_type: pt.device_type,
          terminal_id: pt.terminal_id,
          provider_id: pt.provider_id,
          store: %{
            store_id: s.id,
            store_name: s.name,
            store_code: s.code,
            brand_id: b.id,
            brand_name: b.name,
            neo_merchant_id: s.neo_merchant_id,
            address_id: s.address_id
          },
          brand: %{
            id: b.id,
            name: b.name,
            code: b.code
          },
          created_at: pt.inserted_at,
          updated_at: pt.updated_at
        }
      )

    case Repo.all(query) |> Enum.uniq_by(& &1.serial_number) do
      [] ->
        conn
        |> put_status(:not_found)
        |> json(%{
          status: "error",
          deviceCount: 0,
          message: "No devices found for the provided merchantRefId",
          data: %{
            devices: []
          }
        })

      devices ->
        conn
        |> put_status(:ok)
        |> json(%{
          status: "success",
          deviceCount: length(devices),
          message: "#{length(devices)} device(s) retrieved successfully",
          data: %{
            devices: devices
          }
        })
    end
  end

  def get_unique_device_detail(conn, _params) do
    conn
    |> put_status(:bad_request)
    |> json(%{
      status: "error",
      deviceCount: 0,
      message: "merchantRefId is required",
      data: %{
        devices: []
      }
    })
  end

  def get_today_transactions(conn, %{"merchantRefId" => code} = _params) do
    Logger.info("Fetching today's transactions for merchantRefId: #{code}")

    today = Date.utc_today()
    today_start = DateTime.new!(today, ~T[00:00:00.000], "Etc/UTC")
    today_end = DateTime.new!(today, ~T[23:59:59.999], "Etc/UTC")

    base_query =
      from(g in Group,
        where: g.code == ^code,
        join: b in Brand,
        on: b.group_id == g.id,
        join: s in Store,
        on: s.brand_id == b.id,
        join: pt in PosTerminal,
        on: pt.store_id == s.id,
        left_join: t in Transaction,
        on:
          fragment(
            "CAST(? AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci = CAST(? AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci",
            t.device_id,
            pt.serial_number
          ) and
            t.inserted_at >= ^today_start and
            t.inserted_at <= ^today_end
      )

    # Get settlement amount
    settlement_query =
      from([g, b, s, pt, t] in base_query,
        where: not is_nil(t.id) and not is_nil(t.settlement_date_time),
        select: %{
          total_settlement_amount: coalesce(sum(t.transaction_amount), 0)
        }
      )

    # Get transactions with details
    transactions_query =
      from([g, b, s, pt, t] in base_query,
        where: not is_nil(t.id),
        select: %{
          transaction_id: t.id,
          transaction_amount: t.transaction_amount,
          device_id: t.device_id,
          processing_id: t.processing_id,
          status: t.status,
          email: t.email,
          user_id: t.user_id,
          transaction_ref_number: t.transaction_ref_number,
          m_ref_num: t.m_ref_num,
          name: t.name,
          merchant_id: t.merchant_id,
          additional_data: t.additional_data,
          payment_reference_id: t.payment_reference_id,
          payload: t.payload,
          settlement_date_time: t.settlement_date_time,
          is_settled: not is_nil(t.settlement_date_time),
          device_details: %{
            device_id: pt.id,
            name: pt.name,
            serial_number: pt.serial_number,
            device_type: pt.device_type,
            terminal_id: pt.terminal_id
          },
          store_details: %{
            store_id: s.id,
            store_name: s.name,
            store_code: s.code,
            neo_merchant_id: s.neo_merchant_id
          },
          brand_details: %{
            brand_id: b.id,
            brand_name: b.name,
            brand_code: b.code
          },
          created_at: t.inserted_at
        }
      )

    with settlement = %{total_settlement_amount: settlement_amount} <- Repo.one(settlement_query),
         transactions when is_list(transactions) <- Repo.all(transactions_query) do
      total_amount =
        Enum.reduce(transactions, Decimal.new("0"), fn tx, acc ->
          Decimal.add(acc, tx.transaction_amount || Decimal.new("0"))
        end)

      conn
      |> put_status(:ok)
      |> json(%{
        status: "success",
        count: length(transactions),
        total_amount: total_amount,
        total_settlement_amount: settlement_amount,
        message: "#{length(transactions)} transaction(s) retrieved successfully",
        data: %{
          transactions: transactions
        }
      })
    else
      [] ->
        conn
        |> put_status(:not_found)
        |> json(%{
          status: "error",
          count: 0,
          total_amount: "0.00",
          total_settlement_amount: "0.00",
          message: "No transactions found for today",
          data: %{
            transactions: []
          }
        })
    end
  end

  def get_today_transactions(conn, _params) do
    conn
    |> put_status(:bad_request)
    |> json(%{
      status: "error",
      count: 0,
      total_amount: "0.00",
      total_settlement_amount: "0.00",
      message: "merchantRefId is required",
      data: %{
        transactions: []
      }
    })
  end

  def get_total_transactions(conn, params) do
    merchantRefId = params["merchantRefId"]
    provider_id = params["provider_id"]

    if is_nil(merchantRefId) do
      conn
      |> put_status(:bad_request)
      |> json(%{
        status: "error",
        message: "merchantRefId is required",
        data: %{
          transactions: []
        }
      })
    else
      Logger.info(
        "Fetching transactions for merchantRefId: #{merchantRefId} with params: #{inspect(params)}"
      )

      {start_date, end_date} = get_date_range(params)

      base_query =
        from(g in Group,
          where: g.code == ^merchantRefId,
          join: b in Brand,
          on: b.group_id == g.id,
          join: s in Store,
          on: s.brand_id == b.id,
          join: pt in PosTerminal,
          on: pt.store_id == s.id,
          left_join: t in Transaction,
          on:
            fragment(
              "CAST(? AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci = CAST(? AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci",
              t.device_id,
              pt.serial_number
            ) and
              t.inserted_at >= ^start_date and
              t.inserted_at <= ^end_date,

          left_join: st in DaProductApp.ShukriaTerminal,
            on: st.shukria_terminal_id == type(pt.id, :string) and st.provider_id == t.provider_id,
          where: t.provider_id == ^provider_id and t.bank_user_id == ^merchantRefId
        )

      # Get settlement amount
      settlement_query =
        from([g, b, s, pt, t, st] in base_query,
          where: not is_nil(t.id) and not is_nil(t.settlement_date_time),
          select: %{
            total_settlement_amount: coalesce(sum(t.transaction_amount), 0)
          }
        )

      # Get transactions with details
      transactions_query =
        from([g, b, s, pt, t, st] in base_query,
          where: not is_nil(t.id),
          order_by: [desc: t.inserted_at],
          select: %{
            transaction_id: t.id,
            transaction_amount: t.transaction_amount,
            device_id: t.device_id,
            processing_id: t.processing_id,
            status: t.status,
            email: t.email,
            transaction_ref_number: t.transaction_ref_number,
            m_ref_num: t.m_ref_num,
            merchant_id: t.shukria_mid,
            #ysp_tid: t.ysp_tid,
            ysp_tid: st.ysp_tid,   # <-- from shukria_terminals
            ysp_mid: st.ysp_mid,   # <-- from shukria_terminals
            bank_user_id: t.bank_user_id,
            payment_reference_id: t.payment_reference_id,
            settlement_date_time: t.settlement_date_time,
            is_settled: not is_nil(t.settlement_date_time),
            provider_name: t.provider_name,
            device_details: %{
              device_id: pt.id,
              name: pt.name,
              serial_number: pt.serial_number,
              device_type: pt.device_type,
              terminal_id: pt.terminal_id
            },
            store_details: %{
              store_id: s.id,
              store_name: s.name,
              store_code: s.code,
              neo_merchant_id: s.neo_merchant_id
            },
            brand_details: %{
              brand_id: b.id,
              brand_name: b.name,
              brand_code: b.code
            },
            created_at: t.inserted_at,
            transaction_type: "QR"
          }
        )

      with settlement = %{total_settlement_amount: settlement_amount} <-
             Repo.one(settlement_query),
           transactions when is_list(transactions) <-
             Repo.all(transactions_query) |> Enum.uniq_by(& &1.transaction_id) do

        # Convert created_at to UAE time
        transactions =
          Enum.map(transactions, fn tx ->
            Map.update!(tx, :created_at, &to_uae_datetime/1)
          end)

        total_amount =
          Enum.reduce(transactions, Decimal.new("0"), fn tx, acc ->
            Decimal.add(acc, tx.transaction_amount || Decimal.new("0"))
          end)

        conn
        |> put_status(:ok)
        |> json(%{
          status: "success",
          count: length(transactions),
          total_amount: total_amount,
          total_settlement_amount: settlement_amount,
          date_range: %{
            start_date: DateTime.to_date(start_date),
            end_date: DateTime.to_date(end_date)
          },
          message: "#{length(transactions)} transaction(s) retrieved successfully",
          data: %{
            transactions: transactions
          }
        })
      else
        [] ->
          conn
          |> put_status(:not_found)
          |> json(%{
            status: "error",
            count: 0,
            total_amount: "0.00",
            total_settlement_amount: "0.00",
            date_range: %{
              start_date: DateTime.to_date(start_date),
              end_date: DateTime.to_date(end_date)
            },
            message: "No transactions found for the specified period",
            data: %{
              transactions: []
            }
          })
      end
    end
  end

  def get_total_card_transactions(conn, params) do
    Logger.info("Fetching total card transactions with params: #{inspect(params)}")
    merchantRefId = params["merchantRefId"]

    if is_nil(merchantRefId) do
      conn
      |> put_status(:bad_request)
      |> json(%{
        status: "error",
        message: "merchantRefId is required",
        data: %{
          transactions: []
        }
      })
    else
      Logger.info(
        "Fetching card transactions for merchantRefId: #{merchantRefId} with params: #{inspect(params)}"
      )

      {start_date, end_date} = get_date_range(params)

      case get_card_terminals_details(merchantRefId) do
        {:ok, hierarchy_details} ->
          # Extract terminal IDs, making sure they're not nil
          terminal_ids =
            Enum.map(hierarchy_details, & &1.terminal_id)
            |> Enum.reject(&is_nil/1)

          if Enum.empty?(terminal_ids) do
            conn
            |> put_status(:not_found)
            |> json(%{
              status: "error",
              count: 0,
              total_amount: "0.00",
              total_settlement_amount: "0.00",
              date_range: %{
                start_date: DateTime.to_date(start_date),
                end_date: DateTime.to_date(end_date)
              },
              message: "No terminals found for the provided merchantRefId",
              data: %{
                transactions: []
              }
            })
          else
            case get_card_transactions_from_api(
                   terminal_ids,
                   start_date,
                   end_date,
                   merchantRefId,
                   hierarchy_details,
                   conn
                 ) do
              {:ok, transactions} ->
                total_amount = calculate_total_amount(transactions)
                settlement_amount = calculate_settlement_amount(transactions)

                conn
                |> put_status(:ok)
                |> json(%{
                  status: "success",
                  count: length(transactions),
                  total_amount: total_amount,
                  total_settlement_amount: settlement_amount,
                  date_range: %{
                    start_date: DateTime.to_date(start_date),
                    end_date: DateTime.to_date(end_date)
                  },
                  message: "#{length(transactions)} transaction(s) retrieved successfully",
                  data: %{
                    transactions: transactions
                  }
                })

              {:error, reason} ->
                conn
                |> put_status(:internal_server_error)
                |> json(%{
                  status: "error",
                  message: "Failed to fetch card transactions: #{reason}",
                  data: %{
                    transactions: []
                  }
                })
            end
          end

        {:error, :not_found} ->
          conn
          |> put_status(:not_found)
          |> json(%{
            status: "error",
            count: 0,
            total_amount: "0.00",
            total_settlement_amount: "0.00",
            date_range: %{
              start_date: DateTime.to_date(start_date),
              end_date: DateTime.to_date(end_date)
            },
            message: "No merchant hierarchy found for the provided merchantRefId",
            data: %{
              transactions: []
            }
          })
      end
    end
  end

  # Modify the get_card_transactions_from_api function signature to accept hierarchy info
  defp get_card_transactions_from_api(
         terminal_ids,
         start_date,
         end_date,
         merchantRefId,
         terminals,
         conn
       ) do
    base_url = "http://demo.ctrmv.com:4001/api/getAllCardTransaction"
    # Get authorization header from incoming request headers
    auth_header =
      case get_req_header(conn, "authorization") do
        [header | _] ->
          # Remove surrounding quotes if present
          String.trim(header, "\"")
        [] ->
          # Fallback to params if not in header, or use default config
          ""
      end

    Logger.info("Using Authorization header: #{inspect(auth_header)}")


    # Format dates to YYYY-MM-DD
    formatted_start_date = DateTime.to_date(start_date) |> Date.to_iso8601()
    formatted_end_date = DateTime.to_date(end_date) |> Date.to_iso8601()

    # Join terminal IDs with comma
    terminal_ids_string = Enum.join(terminal_ids, ",")

    query_params =
      URI.encode_query(%{
        "action" => "getAll",
        "start_date" => formatted_start_date,
        "end_date" => formatted_end_date,
        "merchant_id" => merchantRefId,
        "terminal_id" => terminal_ids_string
      })

    url = "#{base_url}?#{query_params}"

    Logger.info("Fetching card transactions from URL: #{url}")

    case HTTPoison.get(url, [
      {"Content-Type", "application/json"},
      {"Authorization", auth_header}
    ]) do
      {:ok, %HTTPoison.Response{status_code: 200, body: body}} ->
        Logger.info("Card transactions API response: #{inspect(body)}")

        case Jason.decode(body) do
          {:ok, decoded_response} ->
            case decoded_response do
              %{"success" => true, "data" => transactions} ->
                Logger.info("printing terminals: #{inspect(terminals)}")

                # Merge hierarchy data with each transaction
                enriched_transactions =
                  Enum.map(transactions, fn transaction ->
                    # Find matching terminal info for this transaction
                    terminal_info =
                      Enum.find(terminals, fn terminal ->
                        # Log the comparison values for debugging
                        Logger.info(
                          "Comparing terminal_id: #{terminal.terminal_id} with transaction terminal_id: #{transaction["terminal_id"]}"
                        )

                        # Convert both values to strings for comparison
                        String.trim(to_string(terminal.terminal_id)) ==
                          String.trim(to_string(transaction["terminal_id"]))
                      end)

                    Logger.info("Found terminal_info: #{inspect(terminal_info)}")

                    if terminal_info do
                      Map.merge(transaction, %{
                        "store_details" => terminal_info.store_details,
                        "brand_details" => terminal_info.brand_details,
                        "device_details" => terminal_info.device_details,
                        "terminal_id" => terminal_info.terminal_id,
                        "device_id" => terminal_info.device_id,
                        "transaction_type" => "Card"
                      })
                    else
                      # If no terminal info found, provide empty structures
                      Map.merge(transaction, %{
                        "store_details" => %{},
                        "brand_details" => %{},
                        "terminal_id" => transaction["terminal_id"],
                        "terminal_serial" => nil
                      })
                    end
                  end)

                Logger.info(
                  "Successfully processed #{length(enriched_transactions)} transactions"
                )

                {:ok, enriched_transactions}

              %{"success" => false, "message" => error_message} ->
                Logger.error("API returned error: #{error_message}")
                {:error, error_message}

              _ ->
                Logger.error("Unexpected response format: #{inspect(decoded_response)}")
                {:error, "Invalid response format"}
            end

          {:error, reason} ->
            Logger.error("Failed to decode response: #{inspect(reason)}\nBody: #{inspect(body)}")
            {:error, "Invalid JSON response"}
        end

      {:ok, %HTTPoison.Response{status_code: status_code, body: body}} ->
        Logger.error(
          "API request failed with status code: #{status_code}, body: #{inspect(body)}"
        )

        {:error, "API request failed with status #{status_code}"}

      {:error, %HTTPoison.Error{reason: reason}} ->
        Logger.error("API request failed, reason: #{inspect(reason)}")
        {:error, "API request failed: #{inspect(reason)}"}
    end
  end

  defp enrich_transactions_with_hierarchy(transactions, terminals) do
    terminals_map = Enum.into(terminals, %{}, &{&1.terminal_id, &1})

    Enum.map(transactions, fn transaction ->
      # Use string key instead of atom
      terminal_info =
        Map.get(terminals_map, transaction["terminal_id"]) ||
          %{
            store_details: %{},
            brand_details: %{}
          }

      # Merge the transaction with terminal info and ensure consistent key types
      Map.merge(transaction, %{
        "store_details" => terminal_info.store_details,
        "brand_details" => terminal_info.brand_details,
        "terminal_id" => transaction["terminal_id"]
      })
    end)
  end

  defp calculate_total_amount(transactions) do
    Enum.reduce(transactions, Decimal.new("0"), fn tx, acc ->
      amount =
        case tx["transaction_amount"] do
          nil -> Decimal.new("0")
          # Handle empty string
          "" -> Decimal.new("0")
          amount when is_binary(amount) -> Decimal.new(String.trim(amount))
          amount -> Decimal.new(amount)
        end

      Decimal.add(acc, amount)
    end)
  end

  defp calculate_settlement_amount(transactions) do
    transactions
    |> Enum.filter(& &1["settlement_date"])
    |> Enum.reduce(Decimal.new("0"), fn tx, acc ->
      amount =
        case tx["transaction_amount"] do
          nil -> Decimal.new("0")
          # Handle empty string
          "" -> Decimal.new("0")
          amount when is_binary(amount) -> Decimal.new(String.trim(amount))
          amount -> Decimal.new(amount)
        end

      Decimal.add(acc, amount)
    end)
  end

  def get_merchant_brands(conn, %{"merchantRefId" => code}) do
    Logger.info("Fetching brands for merchantRefId: #{code}")

    query =
      from(g in Group,
        where: g.code == ^code,
        join: b in Brand,
        on: b.group_id == g.id,
        select: %{
          brand_id: b.id,
          brand_name: b.name,
          brand_code: b.code,
          group_id: g.id,
          group_name: g.name,
          group_code: g.code
        }
      )

    brands = Repo.all(query)

    if brands == [] do
      conn
      |> put_status(:not_found)
      |> json(%{
        status: "error",
        message: "No brands found for the provided merchantRefId",
        data: %{brands: []}
      })
    else
      conn
      |> put_status(:ok)
      |> json(%{
        status: "success",
        count: length(brands),
        message: "#{length(brands)} brand(s) retrieved successfully",
        data: %{brands: brands}
      })
    end
  end

  def get_merchant_brands(conn, _params) do
    conn
    |> put_status(:bad_request)
    |> json(%{
      status: "error",
      message: "merchantRefId is required",
      data: %{brands: []}
    })
  end

  def get_merchant_hierarchy(conn, %{"merchantRefId" => code} = _params) do
    Logger.info("Fetching merchant hierarchy for merchantRefId: #{code}")

    query =
      from(g in Group,
        where: g.code == ^code,
        join: b in Brand,
        on: b.group_id == g.id,
        join: s in Store,
        on: s.brand_id == b.id,
        join: ad in Address,
        on: ad.id == s.address_id,
        left_join: pt in PosTerminal,
        on: pt.store_id == s.id,
        select: %{
          group_id: g.id,
          group_name: g.name,
          group_code: g.code,
          brand_id: b.id,
          brand_name: b.name,
          brand_code: b.code,
          store_id: s.id,
          store_name: s.name,
          store_code: s.code,
          address_id: s.address_id,
          neo_merchant_id: s.neo_merchant_id,
          id: pt.id,
          terminal_name: pt.name,
          serial_number: pt.serial_number,
          device_type: pt.device_type,
          terminal_id: pt.terminal_id,
          address: %{
            address_id: ad.id,
            street: ad.line1,
            city: ad.city,
            state: ad.state,
            country: ad.country,
            zipcode: ad.zipcode,
            country_code: ad.country_code
          }
        }
      )

    case Repo.all(query) do
      [] ->
        conn
        |> put_status(:not_found)
        |> json(%{
          status: "error",
          message: "No merchant chains found for the provided merchantRefId",
          data: %{
            merchantRefId: code,
            chains: []
          }
        })

      results ->
        hierarchy =
          results
          |> Enum.reduce(%{}, fn record, acc ->
            brand_key = record.brand_id
            store_key = record.store_id

            terminal =
              if record.terminal_id do
                %{
                  id: record.id,
                  name: record.terminal_name,
                  serial_number: record.serial_number,
                  device_type: record.device_type,
                  terminal_id: record.terminal_id
                }
              end

            acc
            |> Map.update(
              brand_key,
              %{
                brand_id: record.brand_id,
                brand_name: record.brand_name,
                brand_code: record.brand_code,
                stores: %{
                  store_key => %{
                    store_id: record.store_id,
                    store_name: record.store_name,
                    store_code: record.store_code,
                    address_id: record.address_id,
                    neo_merchant_id: record.neo_merchant_id,
                    terminals: [terminal] |> Enum.reject(&is_nil/1),
                    address: record.address
                  }
                }
              },
              fn brand ->
                brand
                |> Map.update!(:stores, fn stores ->
                  stores
                  |> Map.update(
                    store_key,
                    %{
                      store_id: record.store_id,
                      store_name: record.store_name,
                      store_code: record.store_code,
                      address_id: record.address_id,
                      neo_merchant_id: record.neo_merchant_id,
                      terminals: [terminal] |> Enum.reject(&is_nil/1),
                      address: record.address
                    },
                    fn store ->
                      if terminal do
                        Map.update!(store, :terminals, &[terminal | &1])
                      else
                        store
                      end
                    end
                  )
                end)
              end
            )
          end)

        formatted_hierarchy = %{
          chain_id: hd(results).group_id,
          chain_name: hd(results).group_name,
          chain_code: hd(results).group_code,
          brands:
            hierarchy
            |> Map.values()
            |> Enum.map(fn brand ->
              %{
                brand
                | stores:
                    brand.stores
                    |> Map.values()
                   |> Enum.map(fn store ->
                      %{
                        store_id: store.store_id,
                        store_name: store.store_name,
                        store_code: store.store_code,
                        address_id: store.address_id,
                        neo_merchant_id: store.neo_merchant_id,
                        address: store.address,
                        terminals: Enum.uniq_by(store.terminals, & &1.id)
                      }
                    end)
              }
            end)
        }

        conn
        |> put_status(:ok)
        |> json(%{
          status: "success",
          message: "Merchant chains retrieved successfully",
          data: %{
            merchantRefId: code,
            chains: formatted_hierarchy
          }
        })
    end
  end

  def get_merchant_hierarchy(conn, _params) do
    conn
    |> put_status(:bad_request)
    |> json(%{
      status: "error",
      message: "merchantRefId is required",
      data: %{
        merchantRefId: nil,
        chains: []
      }
    })
  end

  def create_group(conn, params) do
    Logger.info("Creating new group with params: #{inspect(params)}")

    case validate_required_params(params) do
      :ok ->
        changeset =
          Group.changeset(%Group{}, %{
            name: params["name"],
            code: params["code"],
            description: params["description"],
            status: params["status"] || "active",
            phone_number: params["phone_number"],
            transaction_currency: "AED"
          })

        case Repo.insert(changeset) do
          {:ok, group} ->
            conn
            |> put_status(:created)
            |> json(%{
              status: "success",
              success: true,
              message: "Chain successfully created",
              chain_id: group.id
            })

          {:error, changeset} ->
            conn
            |> put_status(:unprocessable_entity)
            |> json(%{
              status: "error",
              success: false,
              message: "Failed to create chain",
              errors: Ecto.Changeset.traverse_errors(changeset, fn {msg, _opts} -> msg end)
            })
        end

      {:error, missing_fields} ->
        conn
        |> put_status(:bad_request)
        |> json(%{
          status: "error",
          success: false,
          message: "Missing required fields",
          errors: missing_fields
        })
    end
  end

  def update_group(conn, params) do
    Logger.info("Updating group with params: #{inspect(params)}")

    with {:ok, id} <- validate_id(params["id"]),
         {:ok, update_params} <- validate_update_params(params),
         {:ok, group} <- get_group(id) do
      changeset =
        Group.changeset(group, %{
          name: update_params["chain_name"] || group.name,
          code: update_params["chain_code"] || group.code,
          description: update_params["description"] || group.description,
          phone_number: update_params["phone_number"] || group.phone_number
        })

      case Repo.update(changeset) do
        {:ok, _updated_group} ->
          conn
          |> put_status(:ok)
          |> json(%{
            success: true,
            message: "Chain updated successfully"
          })

        {:error, changeset} ->
          conn
          |> put_status(:unprocessable_entity)
          |> json(%{
            success: false,
            message: "Failed to update chain",
            errors: Ecto.Changeset.traverse_errors(changeset, fn {msg, _opts} -> msg end)
          })
      end
    else
      {:error, :missing_id} ->
        conn
        |> put_status(:bad_request)
        |> json(%{
          success: false,
          message: "Chain ID is required"
        })

      {:error, :no_params} ->
        conn
        |> put_status(:bad_request)
        |> json(%{
          success: false,
          message: "At least one parameter is required for update"
        })

      {:error, :not_found} ->
        conn
        |> put_status(:not_found)
        |> json(%{
          success: false,
          message: "Chain not found"
        })
    end
  end

  def create_store(conn, params) do
    Logger.info("Creating new store with params: #{inspect(params)}")

    with {:ok, chain_id} <- validate_chain_id(params["chain_id"]),
         {:ok, brand_id} <- validate_brand_id(params["brand_id"]),
         :ok <- validate_store_params(params),
         :ok <- validate_unique_store_code(params["store_code"]) do
      changeset =
        Store.changeset(%Store{}, %{
          brand_id: brand_id,
          name: params["store_name"],
          code: params["store_code"],
          address_id: params["address_id"],
          neo_merchant_id: params["neo_merchant_id"],
          status: "active"
        })

      case Repo.insert(changeset) do
        {:ok, store} ->
          conn
          |> put_status(:created)
          |> json(%{
            status: "success",
            success: true,
            message: "Store added successfully",
            store_id: store.id,
            errors: nil
          })

        {:error, changeset} ->
          conn
          |> put_status(:unprocessable_entity)
          |> json(%{
            status: "error",
            success: false,
            message: "Failed to create store",
            store_id: nil,
            errors: Ecto.Changeset.traverse_errors(changeset, fn {msg, _opts} -> msg end)
          })
      end
    else
      {:error, :chain_not_found} ->
        conn
        |> put_status(:not_found)
        |> json(%{
          status: "error",
          success: false,
          message: "Chain not found",
          store_id: nil,
          errors: "Chain not found"
        })

      {:error, :brand_not_found} ->
        conn
        |> put_status(:not_found)
        |> json(%{
          status: "error",
          success: false,
          message: "Brand not found",
          store_id: nil,
          errors: "Brand not found"
        })

      {:error, :store_code_exists} ->
        conn
        |> put_status(:conflict)
        |> json(%{
          status: "error",
          success: false,
          message: "Store code already exists",
          store_id: nil,
          errors: "Store code already exists"
        })

      {:error, missing_fields} ->
        conn
        |> put_status(:bad_request)
        |> json(%{
          status: "error",
          success: false,
          message: "Missing required fields",
          store_id: nil,
          errors:
            "Either store_code, store_name, address_id, neo_merchant_id, chain_id or brand_id is missed"
        })
    end
  end

  def update_store(conn, params) do
    Logger.info("Updating store with params: #{inspect(params)}")

    with {:ok, id} <- validate_id(params["id"]),
         {:ok, store} <- get_store(id) do
      changeset =
        Store.changeset(store, %{
          name: params["store_name"] || store.name
          # Only update store_name, do not update code or neo_merchant_id
        })

      case Repo.update(changeset) do
        {:ok, updated_store} ->
          conn
          |> put_status(:ok)
          |> json(%{
            status: "success",
            success: true,
            message: "Store name updated successfully",
            store_id: updated_store.id,
            errors: nil
          })

        {:error, changeset} ->
          conn
          |> put_status(:unprocessable_entity)
          |> json(%{
            status: "error",
            success: false,
            message: "Failed to update store name",
            errors: Ecto.Changeset.traverse_errors(changeset, fn {msg, _opts} -> msg end),
            store_id: params["id"]
          })
      end
    else
      {:error, :missing_id} ->
        conn
        |> put_status(:bad_request)
        |> json(%{
          status: "error",
          success: false,
          message: "Store ID is required",
          errors: "Store ID is required",
          store_id: nil
        })

      {:error, :not_found} ->
        conn
        |> put_status(:not_found)
        |> json(%{
          status: "error",
          success: false,
          message: "Store not found",
          errors: "Store not found",
          store_id: params["id"]
        })
    end
  end

  def save_store_and_address(conn, params) do
    Logger.info("Saving store and address with params: #{inspect(params)}")

    # Validate required params
    required_fields = [
      "merchantRefId",
      "name",
      "provider_id",
      "brand_id",
      "address_line1",
      "city",
      "state",
      "country",
      "country_code",
      "postal_code",
      "device_type"
    ]

    missing_fields =
      Enum.filter(required_fields, fn field ->
        is_nil(params[field]) || String.trim(to_string(params[field])) == ""
      end)

    if missing_fields != [] do
      conn
      |> put_status(:bad_request)
      |> json(%{
        status: "error",
        success: false,
        message: "Missing required fields",
        errors: missing_fields
      })
    else
      # Insert address first (assuming you have an Address schema)
      address_changeset =
        %DaProductApp.Addresses.Address{}
        |> DaProductApp.Addresses.Address.changeset(%{
          line1: params["address_line1"],
          city: params["city"],
          state: params["state"],
          country: params["country"],
          country_code: params["country_code"],
          zipcode: params["postal_code"]
        })

      case Repo.insert(address_changeset) do
        {:ok, address} ->
          # Insert into address table with raw SQL
          insert_address_query = """
          INSERT INTO address (id, line1, line2, line3, line4, city, state, country, postalcode)
          VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
          ON DUPLICATE KEY UPDATE
            line1 = VALUES(line1),
            line2 = VALUES(line2),
            line3 = VALUES(line3),
            line4 = VALUES(line4),
            city = VALUES(city),
            state = VALUES(state),
            country = VALUES(country),
            postalcode = VALUES(postalcode)
          """
          case Ecto.Adapters.SQL.query(Repo, insert_address_query, [
            address.id,
            String.slice(address.line1 || "", 0, 45),
            "line2",
            "line3",
            "line4",
            address.city,
            address.state,
            address.country,
            address.zipcode
          ]) do
            {:ok, _result} ->
              Logger.info("Successfully inserted/updated address table for id: #{address.id}")
            {:error, reason} ->
              Logger.error("Failed to insert into address table: #{inspect(reason)}")
          end
          # Update address_id in pos_merchant table
          update_pos_merchant_query = """
          UPDATE pos_merchant SET address_id = ? WHERE merchantid = ?
          """

          case Ecto.Adapters.SQL.query(Repo, update_pos_merchant_query, [address.id, params["merchantRefId"]]) do
            {:ok, _} ->
              Logger.info("Updated address_id in pos_merchant for merchantid: #{params["merchantRefId"]}")
            {:error, reason} ->
              Logger.error("Failed to update address_id in pos_merchant: #{inspect(reason)}")
          end



          # Generate store code
          store_code = :rand.uniform(100_000) |> Integer.to_string() |> String.pad_leading(5, "0")

          # Insert store with address_id
          store_changeset =
            Store.changeset(%Store{}, %{
              brand_id: params["brand_id"],
              name: params["name"],
              # <-- use generated code here
              code: store_code,
              address_id: address.id,
              neo_merchant_id: params["merchantRefId"],
              status: "active"
            })

          case Repo.insert(store_changeset) do
            {:ok, store} ->
              # Get pos_merchant_id based on merchantRefId
              pos_merchant_query = """
              SELECT id FROM pos_merchant WHERE merchantid = ?
              """

              pos_merchant_id = case Ecto.Adapters.SQL.query(Repo, pos_merchant_query, [params["merchantRefId"]]) do
                {:ok, %{rows: [[id] | _]}} -> id
                _ -> nil
              end

              # Check if there's an existing pos_terminal for this pos_merchant_id
              existing_terminal_query = """
              SELECT terminalidmerchantid FROM pos_terminals WHERE pos_merchant_id = ? LIMIT 1
              """

              params = case Ecto.Adapters.SQL.query(Repo, existing_terminal_query, [pos_merchant_id]) do
                {:ok, %{rows: [[existing_terminalidmerchantid] | _]}} ->
                  Map.put(params, "smid", existing_terminalidmerchantid)
                _ ->
                  params
              end
              Logger.info("Using smid: #{inspect(params["smid"])} for pos_terminal creation")

              terminal_id = "#{:rand.uniform(100_000_000) |> Integer.to_string() |> String.pad_leading(8, "0")}"
              terminalidmerchantid = "#{:rand.uniform(100_000_000) |> Integer.to_string() |> String.pad_leading(8, "0")}"

              # Create a new POS terminal for the store
              pos_terminal_attrs = %{
                # or params["terminal_name"] if you have it
                name: "#{params["name"]}-Device",
                serial_number: nil,
                device_type: params["device_type"],
                terminal_id: terminal_id,
                store_id: store.id,
                provider_id: params["provider_id"],
                status: "active",
                terminalid: terminal_id,
                pos_merchant_id: pos_merchant_id,
                terminalidmerchantid: terminalidmerchantid
              }

              pos_terminal_changeset =
                DaProductApp.PosTerminals.PosTerminal.changeset(
                  %DaProductApp.PosTerminals.PosTerminal{},
                  pos_terminal_attrs
                )

              # Generate provider_tid code
              provider_tid =
                :rand.uniform(1_000_000_000_000_000) |> Integer.to_string() |> String.pad_leading(15, "0")

              provider_mid =
                :rand.uniform(100_000_000) |> Integer.to_string() |> String.pad_leading(8, "0")


              case Repo.insert(pos_terminal_changeset) do
                {:ok, pos_terminal} ->
                  # Update pos_terminal with correct terminal_id, terminalid, and terminalidmerchantid
                  # Check if stid is provided in params, otherwise generate
                  new_terminal_id = if params["stid"] && params["stid"] != "" do
                    params["stid"]
                  else
                    new_terminal_id = "#{store.code}#{pos_terminal.id}"
                    String.pad_trailing(new_terminal_id, 8, "0")
                  end

                  # Check if smid is provided in params, otherwise generate
                  new_terminalidmerchantid = if params["smid"] && params["smid"] != "" do
                    params["smid"]
                  else
                    new_terminalidmerchantid = "#{params["brand_id"]}#{store.code}"
                    String.pad_trailing(new_terminalidmerchantid, 15, "0")
                  end

                  update_changeset = DaProductApp.PosTerminals.PosTerminal.changeset(pos_terminal, %{
                    terminal_id: new_terminal_id,
                    terminalid: new_terminal_id,
                    terminalidmerchantid: new_terminalidmerchantid
                  })

                  {:ok, pos_terminal} = Repo.update(update_changeset)


                  # Update all pos_terminals with same pos_merchant_id to have same terminalidmerchantid
                  update_all_terminals_query = """
                  UPDATE pos_terminals
                  SET terminalidmerchantid = ?
                  WHERE pos_merchant_id = ?
                  """

                  case Ecto.Adapters.SQL.query(Repo, update_all_terminals_query, [
                    new_terminalidmerchantid,
                    pos_terminal.pos_merchant_id
                  ]) do
                    {:ok, result} ->
                      Logger.info("Updated terminalidmerchantid to #{new_terminalidmerchantid} for all terminals with pos_merchant_id: #{pos_terminal.pos_merchant_id}. Rows affected: #{result.num_rows}")
                    {:error, reason} ->
                      Logger.error("Failed to update all pos_terminals: #{inspect(reason)}")
                  end


                  # Update pos_merchant table - set merchantid to new_terminalidmerchantid
                  update_pos_merchant_query = """
                  UPDATE pos_merchant
                  SET merchantid = ?
                  WHERE id = ?
                  """

                  case Ecto.Adapters.SQL.query(Repo, update_pos_merchant_query, [
                    new_terminalidmerchantid,
                    pos_terminal.pos_merchant_id
                  ]) do
                    {:ok, _} ->
                      Logger.info("Updated pos_merchant merchantid to #{new_terminalidmerchantid} for id: #{pos_terminal.pos_merchant_id}")
                    {:error, reason} ->
                      Logger.error("Failed to update pos_merchant: #{inspect(reason)}")
                  end

                  # Update brands table - set code and merchant_reference_id based on brand_id
                  update_brands_query = """
                  UPDATE brands
                  SET code = ?, merchant_reference_id = ?
                  WHERE id = ?
                  """

                  case Ecto.Adapters.SQL.query(Repo, update_brands_query, [
                    new_terminalidmerchantid,
                    new_terminalidmerchantid,
                    params["brand_id"]
                  ]) do
                    {:ok, _} ->
                      Logger.info("Updated brands code and merchant_reference_id to #{new_terminalidmerchantid} for brand_id: #{params["brand_id"]}")
                      # Get group_id from brands table
                      get_group_id_query = """
                      SELECT group_id FROM brands WHERE id = ?
                      """
                      case Ecto.Adapters.SQL.query(Repo, get_group_id_query, [params["brand_id"]]) do
                        {:ok, %{rows: [[group_id] | _]}} ->
                          # Update groups table - set code based on group_id
                          update_groups_query = """
                          UPDATE `groups`
                          SET code = ?
                          WHERE id = ?
                          """

                          case Ecto.Adapters.SQL.query(Repo, update_groups_query, [
                            new_terminalidmerchantid,
                            group_id
                          ]) do
                            {:ok, _} ->
                              Logger.info("Updated groups code to #{new_terminalidmerchantid} for group_id: #{group_id}")
                            {:error, reason} ->
                              Logger.error("Failed to update groups: #{inspect(reason)}")
                          end

                        {:error, reason} ->
                          Logger.error("Failed to get group_id from brands: #{inspect(reason)}")
                      end

                    {:error, reason} ->
                      Logger.error("Failed to update brands: #{inspect(reason)}")
                  end

                  # Insert into pos_terminal table with raw SQL
                  insert_pos_terminal_query = """
                  INSERT INTO pos_terminal (id, terminalid, serial_number, pos_merchant_id)
                  VALUES (?, ?, ?, ?)
                  ON DUPLICATE KEY UPDATE
                    terminalid = VALUES(terminalid),
                    serial_number = VALUES(serial_number),
                    pos_merchant_id = VALUES(pos_merchant_id)
                  """

                  case Ecto.Adapters.SQL.query(Repo, insert_pos_terminal_query, [
                    pos_terminal.id,
                    pos_terminal.terminalid,
                    pos_terminal.serial_number,
                    pos_terminal.pos_merchant_id
                  ]) do
                    {:ok, _result} ->
                      Logger.info("Successfully inserted/updated pos_terminal record for id: #{pos_terminal.id}")
                      # Insert into pos_log_terminal
                      insert_log_query = """
                      INSERT INTO pos_log_terminal (pos_terminal_id, old_serial_number, new_serial_number, date_added, inserted_at, updated_at)
                      VALUES (?, ?, ?, NOW(), NOW(), NOW())
                      """
                      Ecto.Adapters.SQL.query(Repo, insert_log_query, [
                        pos_terminal.id,
                        nil,
                        pos_terminal.serial_number
                      ])

                    {:error, reason} ->
                      Logger.error("Failed to insert into pos_terminal table: #{inspect(reason)}")
                  end

                  ysp_mid = if params["ysp_mid"] && params["ysp_mid"] != "" do
                    params["ysp_mid"]
                  else
                    :rand.uniform(1_000_000_000_000_000) |> Integer.to_string() |> String.pad_leading(15, "0")
                  end

                  ysp_tid = if params["ysp_tid"] && params["ysp_tid"] != "" do
                    params["ysp_tid"]
                  else
                    :rand.uniform(100_000_000) |> Integer.to_string() |> String.pad_leading(8, "0")
                  end


                  # Create a new ShukriaTerminal after POS terminal is created
                  shukria_attrs = %{
                    shukria_terminal_id: to_string(pos_terminal.id),
                    provider_id: to_string(params["provider_id"]),
                    provider_tid: provider_tid,
                    provider_mid: provider_mid,
                    ysp_mid: ysp_mid,
                    ysp_tid: ysp_tid,
                    status: "active",
                    description: pos_terminal.terminal_id,
                    shukria_mid: to_string(params["brand_id"])
                  }

                  shukria_changeset =
                    DaProductApp.ShukriaTerminal.changeset(
                      %DaProductApp.ShukriaTerminal{},
                      shukria_attrs
                    )

                  case Repo.insert(shukria_changeset) do
                    {:ok, shukria_terminal} ->
                      # Only insert into acquirer tables if provider_id is 4
                      # Insert into acquirer_merchant table
                      mcc_code = if params["mcc_code"] && params["mcc_code"] != "", do: params["mcc_code"], else: nil
                      #ysp_mid = if params["ysp_mid"] && params["ysp_mid"] != "", do: params["ysp_mid"], else: nil
                      #ysp_tid = if params["ysp_tid"] && params["ysp_tid"] != "", do: params["ysp_tid"], else: nil



                      if ysp_mid && ysp_tid && (params["provider_id"] == 4 || to_string(params["provider_id"]) == "4") do


                        insert_acquirer_merchant_query = """
                        INSERT INTO acquirer_merchant (acquirer_id, merchantid, mcc, address_id)
                        VALUES (?, ?, ?, ?)
                        """

                        acquirer_merchant_id = case Ecto.Adapters.SQL.query(Repo, insert_acquirer_merchant_query, [3, ysp_mid, mcc_code, address.id]) do
                          {:ok, %{num_rows: 1, last_insert_id: id}} ->
                            Logger.info("Inserted into acquirer_merchant with id: #{id} for merchantid: #{ysp_mid}")
                            id
                          {:error, reason} ->
                            Logger.error("Failed to insert into acquirer_merchant: #{inspect(reason)}")
                            nil
                        end

                        # Insert into acquirer_terminal table
                        acquirer_terminal_id = if acquirer_merchant_id do
                          insert_acquirer_terminal_query = """
                          INSERT INTO acquirer_terminal (terminalid, acquirer_id, acquirer_merchant_id)
                          VALUES (?, ?, ?)
                          """

                          case Ecto.Adapters.SQL.query(Repo, insert_acquirer_terminal_query, [ysp_tid, 3, acquirer_merchant_id]) do
                            {:ok, %{num_rows: 1, last_insert_id: id}} ->
                              Logger.info("Inserted into acquirer_terminal with id: #{id} for terminalid: #{ysp_tid}")
                              id
                            {:error, reason} ->
                              Logger.error("Failed to insert into acquirer_terminal: #{inspect(reason)}")
                              nil
                          end
                        else
                          nil
                        end

                        # Insert into pos_terminal_acquirer_terminal table
                        if acquirer_terminal_id do
                          insert_pos_terminal_acquirer_query = """
                          INSERT INTO pos_terminal_acquirer_terminal (pos_terminal_id, acquirer_terminal_id)
                          VALUES (?, ?)
                          """

                          case Ecto.Adapters.SQL.query(Repo, insert_pos_terminal_acquirer_query, [pos_terminal.id, acquirer_terminal_id]) do
                            {:ok, _} ->
                              Logger.info("Inserted into pos_terminal_acquirer_terminal for pos_terminal_id: #{pos_terminal.id}, acquirer_terminal_id: #{acquirer_terminal_id}")
                            {:error, reason} ->
                              Logger.error("Failed to insert into pos_terminal_acquirer_terminal: #{inspect(reason)}")
                          end
                        end
                      end

                      conn
                      |> put_status(:created)
                      |> json(%{
                        status: "success",
                        success: true,
                        message:
                          "Store, address, POS terminal, and Shukria terminal saved successfully",
                        store_id: store.id,
                        address_id: address.id,
                        pos_terminal_id: pos_terminal.id,
                        shukria_terminal_id: shukria_terminal.id,
                        terminalidmerchantid: pos_terminal.terminalidmerchantid
                      })

                    {:error, changeset} ->
                      conn
                      |> put_status(:unprocessable_entity)
                      |> json(%{
                        status: "error",
                        success: false,
                        message:
                          "Store, address, and POS terminal saved, but failed to create Shukria terminal",
                        store_id: store.id,
                        address_id: address.id,
                        pos_terminal_id: pos_terminal.id,
                        terminalidmerchantid: pos_terminal.terminalidmerchantid,
                        errors:
                          Ecto.Changeset.traverse_errors(changeset, fn {msg, _opts} -> msg end)
                      })
                  end

                {:error, changeset} ->
                  conn
                  |> put_status(:unprocessable_entity)
                  |> json(%{
                    status: "error",
                    success: false,
                    message: "Store and address saved, but failed to create POS terminal",
                    store_id: store.id,
                    address_id: address.id,
                    errors: Ecto.Changeset.traverse_errors(changeset, fn {msg, _opts} -> msg end)
                  })
              end

            {:error, changeset} ->
              conn
              |> put_status(:unprocessable_entity)
              |> json(%{
                status: "error",
                success: false,
                message: "Failed to create store",
                errors: Ecto.Changeset.traverse_errors(changeset, fn {msg, _opts} -> msg end)
              })
          end

        {:error, changeset} ->
          conn
          |> put_status(:unprocessable_entity)
          |> json(%{
            status: "error",
            success: false,
            message: "Failed to create address",
            errors: Ecto.Changeset.traverse_errors(changeset, fn {msg, _opts} -> msg end)
          })
      end
    end
  end

  def create_group_and_brand(conn, params) do
    Logger.info("Creating group and brand with params: #{inspect(params)}")

    required_fields = [
      "group_name",
      "merchant_refrence_id",
      "brand_name",
      "brand_code",
      "region"
    ]

    missing_fields =
      Enum.filter(required_fields, fn field ->
        is_nil(params[field]) || String.trim(to_string(params[field])) == ""
      end)

    if missing_fields != [] do
      conn
      |> put_status(:bad_request)
      |> json(%{
        status: "error",
        success: false,
        message: "Missing required fields",
        errors: missing_fields
      })
    else
      group_changeset =
        Group.changeset(%Group{}, %{
          name: params["group_name"],
          code: params["merchant_refrence_id"],
          description: params["group_name"],
          phone_number: params["phone_number"],
          status: params["status"] || "active",
          transaction_currency: "AED",
          settlement_currency: "AED"
        })

      case Repo.insert(group_changeset) do
        {:ok, group} ->
          brand_changeset =
            Brand.changeset(%Brand{}, %{
              name: params["brand_name"],
              code: params["brand_code"],
              merchant_reference_id: params["merchant_refrence_id"],
              group_id: group.id,
              region: params["region"]
            })

          case Repo.insert(brand_changeset) do
            {:ok, brand} ->
              # Insert into pos_merchant table
              pos_merchant_query = """
              INSERT INTO pos_merchant (merchantid, merchant_name, address_id)
              VALUES (?, ?, NULL)
              """

              case Ecto.Adapters.SQL.query(Repo, pos_merchant_query, [
                params["merchant_refrence_id"],
                params["brand_name"]
              ]) do
                {:ok, _result} ->
                  conn
                  |> put_status(:created)
                  |> json(%{
                    status: "success",
                    success: true,
                    message: "Group and brand created successfully",
                    group_id: group.id,
                    brand_id: brand.id
                  })

                {:error, reason} ->
                  Logger.error("Failed to insert into pos_merchant: #{inspect(reason)}")
                  conn
                  |> put_status(:unprocessable_entity)
                  |> json(%{
                    status: "error",
                    success: false,
                    message: "Brand created but failed to insert into pos_merchant",
                    errors: inspect(reason),
                    group_id: group.id,
                    brand_id: brand.id
                  })
              end

            {:error, changeset} ->
              conn
              |> put_status(:unprocessable_entity)
              |> json(%{
                status: "error",
                success: false,
                message: "Failed to create brand",
                errors: Ecto.Changeset.traverse_errors(changeset, fn {msg, _opts} -> msg end),
                group_id: group.id
              })
          end

        {:error, changeset} ->
          conn
          |> put_status(:unprocessable_entity)
          |> json(%{
            status: "error",
            success: false,
            message: "Failed to create group",
            errors: Ecto.Changeset.traverse_errors(changeset, fn {msg, _opts} -> msg end)
          })
      end
    end
  end

  def save_merchant_batch_number(conn, params) do
    Logger.info("Saving merchant batch number with params: #{inspect(params)}")

    merchant_id = params["merchant_id"]
    provider_id = params["provider_id"]
    batch_number = params["batch_number"]

    # Validate required fields
    cond do
      is_nil(merchant_id) || String.trim(merchant_id) == "" ->
        conn
        |> put_status(:bad_request)
        |> json(%{
          status: "error",
          success: false,
          message: "merchant_id is required"
        })

      is_nil(batch_number) || String.trim(batch_number) == "" ->
        conn
        |> put_status(:bad_request)
        |> json(%{
          status: "error",
          success: false,
          message: "batch_number is required"
        })

      true ->
        # Generate UUID for id field
        uuid = Ecto.UUID.generate()
        uuid_binary = Ecto.UUID.dump!(uuid)

        # Insert new record
        insert_query = """
        INSERT INTO merchant_batch_numbers (id, merchant_id, batch_number, provider_id, inserted_at, updated_at)
        VALUES (?, ?, ?, ?, NOW(), NOW())
        """

        params_list = if provider_id && String.trim(to_string(provider_id)) != "" do
          [uuid_binary, merchant_id, batch_number, provider_id]
        else
          [uuid_binary, merchant_id, batch_number, nil]
        end

        case Ecto.Adapters.SQL.query(Repo, insert_query, params_list) do
          {:ok, _result} ->
            conn
            |> put_status(:created)
            |> json(%{
              status: "success",
              success: true,
              message: "Batch number saved successfully"
            })

          {:error, error} ->
            Logger.error("Failed to save batch number: #{inspect(error)}")

            conn
            |> put_status(:internal_server_error)
            |> json(%{
              status: "error",
              success: false,
              message: "Failed to save batch number"
            })
        end
    end
  end

  def get_providers(conn, _params) do
    query =
      from(p in DaProductApp.Providers.Provider,
        select: %{
          id: p.id,
          name: p.name,
          status: p.status,
          production_url: p.production_url,
          production_mode: p.production_mode,
          description: p.description,
          provider_code: p.provider_code,
          inserted_at: p.inserted_at,
          updated_at: p.updated_at
        }
      )

    providers = Repo.all(query)

    if providers == [] do
      conn
      |> put_status(:not_found)
      |> json(%{
        status: "error",
        message: "No providers found",
        data: %{providers: []}
      })
    else
      conn
      |> put_status(:ok)
      |> json(%{
        status: "success",
        count: length(providers),
        message: "#{length(providers)} provider(s) retrieved successfully",
        data: %{providers: providers}
      })
    end
  end

  def get_save_device(conn, params) do
    Logger.info("Saving device with params: #{inspect(params)}")

    required_fields = [
      "merchantRefId",
      "device_name",
      "terminal_id",
      "device_type",
      "store_id",
      "provider_id",
      "status"
    ]

    missing_fields =
      Enum.filter(required_fields, fn field ->
        is_nil(params[field]) || String.trim(to_string(params[field])) == ""
      end)

    if missing_fields != [] do
      conn
      |> put_status(:bad_request)
      |> json(%{
        status: "error",
        success: false,
        message: "Missing required fields",
        errors: missing_fields
      })
    else
      # Check if serial_number already exists
      serial_no = params["serial_no"]

      existing_terminal =
        if serial_no && serial_no != "" do
          Repo.get_by(PosTerminal, serial_number: serial_no)
        else
          nil
        end

      force_create = params["forceDeviceCreate"] == 1

      if existing_terminal do
        if force_create do
          # Force remove serial_number from existing device
          existing_serial = existing_terminal.serial_number
          changeset = PosTerminal.changeset(existing_terminal, %{serial_number: nil})
          case Repo.update(changeset) do
            {:ok, _} ->
              # Insert log into pos_log_terminal
              insert_log_query = """
              INSERT INTO pos_log_terminal (pos_terminal_id, old_serial_number, new_serial_number, date_added, inserted_at, updated_at)
              VALUES (?, ?, ?, NOW(), NOW(), NOW())
              """
              Ecto.Adapters.SQL.query(Repo, insert_log_query, [
                existing_terminal.id,
                existing_serial,
                nil
              ])
              # Explicitly set serial_number to NULL in the database
              update_serial_query = """
              UPDATE pos_terminal SET serial_number = NULL WHERE id = ?
              """
              Ecto.Adapters.SQL.query(Repo, update_serial_query, [existing_terminal.id])

              # Proceed with creation
              create_device(params, conn)
            {:error, _} ->
              conn
              |> put_status(:unprocessable_entity)
              |> json(%{
                status: "error",
                success: false,
                message: "Failed to remove serial number from existing device",
                serial_number: serial_no
              })
          end
        else
          conn
          |> put_status(:conflict)
          |> json(%{
            status: "error",
            success: false,
            message: "Device with this serial number already exists",
            serial_number: serial_no,
            pos_terminal_id: existing_terminal.id,
            existing_pos_terminal: %{
              id: existing_terminal.id,
              name: existing_terminal.name,
              device_type: existing_terminal.device_type,
              store_id: existing_terminal.store_id,
              provider_id: existing_terminal.provider_id,
              status: existing_terminal.status
            }
          })
        end
      else
        # No existing terminal, proceed with creation
        create_device(params, conn)
      end
    end
  end

  defp create_device(params, conn) do
    # Get store first to use store.code and brand_id
    store = Repo.get(Store, params["store_id"])
    brand_id = store && store.brand_id


    # Get pos_merchant_id based on merchantRefId
    pos_merchant_query = """
    SELECT id FROM pos_merchant WHERE merchantid = ?
    """

    pos_merchant_id = case Ecto.Adapters.SQL.query(Repo, pos_merchant_query, [params["merchantRefId"]]) do
      {:ok, %{rows: [[id] | _]}} -> id
      _ -> nil
    end

    # Check if there's an existing pos_terminal for this pos_merchant_id
    existing_terminal_query = """
    SELECT terminalidmerchantid FROM pos_terminals WHERE pos_merchant_id = ? LIMIT 1
    """

    params = case Ecto.Adapters.SQL.query(Repo, existing_terminal_query, [pos_merchant_id]) do
      {:ok, %{rows: [[existing_terminalidmerchantid] | _]}} ->
        Map.put(params, "terminalidmerchantid", existing_terminalidmerchantid)
      _ ->
        params
    end



    # Check if terminalid is provided in params, otherwise generate random
    terminal_id = if params["terminalid"] && params["terminalid"] != "" do
      params["terminalid"]
    else
      "#{:rand.uniform(100_000_000) |> Integer.to_string() |> String.pad_leading(8, "0")}"
    end

    # Check if terminalidmerchantid is provided in params, otherwise generate random
    terminalidmerchantid = if params["terminalidmerchantid"] && params["terminalidmerchantid"] != "" do
      params["terminalidmerchantid"]
    else
      "#{:rand.uniform(1_000_000_000_000_000) |> Integer.to_string() |> String.pad_leading(15, "0")}"
    end


    pos_terminal_attrs = %{
      name: params["device_name"],
      serial_number: params["serial_no"],
      device_type: params["device_type"],
      terminal_id: terminal_id,
      terminalidmerchantid: terminalidmerchantid,
      terminalid: terminal_id,
      pos_merchant_id: pos_merchant_id,
      store_id: params["store_id"],
      provider_id: params["provider_id"],
      status: params["status"]
    }

    pos_terminal_changeset =
      PosTerminal.changeset(
        %PosTerminal{},
        pos_terminal_attrs
      )

    provider_tid =
      :rand.uniform(1_000_000_000_000_000) |> Integer.to_string() |> String.pad_leading(15, "0")

    provider_mid =
      :rand.uniform(100_000_000) |> Integer.to_string() |> String.pad_leading(8, "0")

    case Repo.insert(pos_terminal_changeset) do
      {:ok, pos_terminal} ->
        # Update pos_terminal with correct terminal_id, terminalid, and terminalidmerchantid
        # Only update if params didn't provide values
        new_terminal_id = if params["terminalid"] && params["terminalid"] != "" do
          params["terminalid"]
        else
          "#{store.code}#{pos_terminal.id}" |> String.pad_trailing(8, "0")
        end

        new_terminalidmerchantid = if params["terminalidmerchantid"] && params["terminalidmerchantid"] != "" do
          params["terminalidmerchantid"]
        else
          "#{brand_id}#{store.code}" |> String.pad_trailing(15, "0")
        end

        update_changeset = PosTerminal.changeset(pos_terminal, %{
          terminal_id: new_terminal_id,
          terminalid: new_terminal_id,
          terminalidmerchantid: new_terminalidmerchantid
        })

        {:ok, pos_terminal} = Repo.update(update_changeset)

        # Update all pos_terminals with same pos_merchant_id to have same terminalidmerchantid
        update_all_terminals_query = """
        UPDATE pos_terminals
        SET terminalidmerchantid = ?
        WHERE pos_merchant_id = ?
        """

        case Ecto.Adapters.SQL.query(Repo, update_all_terminals_query, [
          new_terminalidmerchantid,
          pos_terminal.pos_merchant_id
        ]) do
          {:ok, result} ->
            Logger.info("Updated terminalidmerchantid to #{new_terminalidmerchantid} for all terminals with pos_merchant_id: #{pos_terminal.pos_merchant_id}. Rows affected: #{result.num_rows}")
          {:error, reason} ->
            Logger.error("Failed to update all pos_terminals: #{inspect(reason)}")
        end

        # Update pos_merchant table - set merchantid to new_terminalidmerchantid
        update_pos_merchant_query = """
        UPDATE pos_merchant
        SET merchantid = ?
        WHERE id = ?
        """

        case Ecto.Adapters.SQL.query(Repo, update_pos_merchant_query, [
          new_terminalidmerchantid,
          pos_terminal.pos_merchant_id
        ]) do
          {:ok, _} ->
            Logger.info("Updated pos_merchant merchantid to #{new_terminalidmerchantid} for id: #{pos_terminal.pos_merchant_id}")
          {:error, reason} ->
            Logger.error("Failed to update pos_merchant: #{inspect(reason)}")
        end

        # Update brands table - set code and merchant_reference_id based on brand_id
        update_brands_query = """
        UPDATE brands
        SET code = ?, merchant_reference_id = ?
        WHERE id = ?
        """

        case Ecto.Adapters.SQL.query(Repo, update_brands_query, [
          new_terminalidmerchantid,
          new_terminalidmerchantid,
          brand_id
        ]) do
          {:ok, _} ->
            Logger.info("Updated brands code and merchant_reference_id to #{new_terminalidmerchantid} for brand_id: #{brand_id}")
            # Get group_id from brands table
            get_group_id_query = """
            SELECT group_id FROM brands WHERE id = ?
            """
            case Ecto.Adapters.SQL.query(Repo, get_group_id_query, [brand_id]) do
              {:ok, %{rows: [[group_id] | _]}} ->
                # Update groups table - set code based on group_id
                update_groups_query = """
                UPDATE `groups`
                SET code = ?
                WHERE id = ?
                """

                case Ecto.Adapters.SQL.query(Repo, update_groups_query, [
                  new_terminalidmerchantid,
                  group_id
                ]) do
                  {:ok, _} ->
                    Logger.info("Updated groups code to #{new_terminalidmerchantid} for group_id: #{group_id}")
                  {:error, reason} ->
                    Logger.error("Failed to update groups: #{inspect(reason)}")
                end

              {:error, reason} ->
                Logger.error("Failed to get group_id from brands: #{inspect(reason)}")
            end

          {:error, reason} ->
            Logger.error("Failed to update brands: #{inspect(reason)}")
        end


        # Insert/Update pos_terminal table with raw SQL
        upsert_pos_terminal_query = """
        INSERT INTO pos_terminal (id, terminalid, serial_number, pos_merchant_id)
        VALUES (?, ?, ?, ?)
        ON DUPLICATE KEY UPDATE
          terminalid = VALUES(terminalid),
          serial_number = VALUES(serial_number),
          pos_merchant_id = VALUES(pos_merchant_id)
        """

        case Ecto.Adapters.SQL.query(Repo, upsert_pos_terminal_query, [
          pos_terminal.id,
          pos_terminal.terminalid,
          pos_terminal.serial_number,
          pos_terminal.pos_merchant_id
        ]) do
          {:ok, _result} ->
            Logger.info("Successfully inserted/updated pos_terminal record for id: #{pos_terminal.id}")
            # Insert log into pos_log_terminal
            insert_log_query = """
            INSERT INTO pos_log_terminal (pos_terminal_id, old_serial_number, new_serial_number, date_added, inserted_at, updated_at)
            VALUES (?, ?, ?, NOW(), NOW(), NOW())
            """
            Ecto.Adapters.SQL.query(Repo, insert_log_query, [
              pos_terminal.id,
              nil,
              pos_terminal.serial_number
            ])
          {:error, reason} ->
            Logger.error("Failed to insert/update pos_terminal table: #{inspect(reason)}")
        end

        ysp_mid = if params["ysp_mid"] && params["ysp_mid"] != "" do
          params["ysp_mid"]
        else
          :rand.uniform(1_000_000_000_000_000) |> Integer.to_string() |> String.pad_leading(15, "0")
        end

        ysp_tid = if params["ysp_tid"] && params["ysp_tid"] != "" do
          params["ysp_tid"]
        else
          :rand.uniform(100_000_000) |> Integer.to_string() |> String.pad_leading(8, "0")
        end


        # Create ShukriaTerminal
        shukria_attrs = %{
          shukria_terminal_id: to_string(pos_terminal.id),
          provider_id: to_string(params["provider_id"]),
          provider_tid: provider_tid,
          provider_mid: provider_mid,
          status: "active",
          ysp_mid: ysp_mid,
          ysp_tid: ysp_tid,
          description: pos_terminal.terminal_id,
          shukria_mid: to_string(brand_id)
        }

        shukria_changeset =
          DaProductApp.ShukriaTerminal.changeset(
            %DaProductApp.ShukriaTerminal{},
            shukria_attrs
          )

        case Repo.insert(shukria_changeset) do
          {:ok, shukria_terminal} ->

            if ysp_tid && ysp_mid && (shukria_terminal.provider_id == 4 || to_string(shukria_terminal.provider_id) == "4") do
              # Get address_id from stores via pos_terminals
              address_id_query = """
              SELECT s.address_id
              FROM pos_terminals pt
              JOIN stores s ON pt.store_id = s.id
              WHERE pt.id = ?
              """

              address_id = case Ecto.Adapters.SQL.query(Repo, address_id_query, [shukria_terminal.shukria_terminal_id]) do
                {:ok, %{rows: [[addr_id] | _]}} -> addr_id
                _ -> nil
              end

              # Insert into acquirer_merchant table
              mcc_code_value = if params["mcc_code"] && params["mcc_code"] != "", do: params["mcc_code"], else: nil

              Logger.info("inserting new acquirer_merchant records")
              insert_acquirer_merchant_query = """
              INSERT INTO acquirer_merchant (acquirer_id, merchantid, mcc, address_id)
              VALUES (?, ?, ?, ?)
              """

              acquirer_merchant_id = case Ecto.Adapters.SQL.query(Repo, insert_acquirer_merchant_query, [3, ysp_mid, mcc_code_value, address_id]) do
                {:ok, %{num_rows: 1, last_insert_id: id}} ->
                  Logger.info("Inserted into acquirer_merchant with id: #{id} for merchantid: #{ysp_mid}")
                  id
                {:error, reason} ->
                  Logger.error("Failed to insert into acquirer_merchant: #{inspect(reason)}")
                  nil
              end

              # Insert into acquirer_terminal table
              acquirer_terminal_id = if acquirer_merchant_id do
                insert_acquirer_terminal_query = """
                INSERT INTO acquirer_terminal (terminalid, acquirer_id, acquirer_merchant_id)
                VALUES (?, ?, ?)
                """

                case Ecto.Adapters.SQL.query(Repo, insert_acquirer_terminal_query, [ysp_tid, 3, acquirer_merchant_id]) do
                  {:ok, %{num_rows: 1, last_insert_id: id}} ->
                    Logger.info("Inserted into acquirer_terminal with id: #{id} for terminalid: #{ysp_tid}")
                    id
                  {:error, reason} ->
                    Logger.error("Failed to insert into acquirer_terminal: #{inspect(reason)}")
                    nil
                end
              else
                nil
              end

              # Insert into pos_terminal_acquirer_terminal table
              if acquirer_terminal_id do
                insert_pos_terminal_acquirer_query = """
                INSERT INTO pos_terminal_acquirer_terminal (pos_terminal_id, acquirer_terminal_id)
                VALUES (?, ?)
                """

                case Ecto.Adapters.SQL.query(Repo, insert_pos_terminal_acquirer_query, [shukria_terminal.shukria_terminal_id, acquirer_terminal_id]) do
                  {:ok, _} ->
                    Logger.info("Inserted into pos_terminal_acquirer_terminal for pos_terminal_id: #{shukria_terminal.shukria_terminal_id}, acquirer_terminal_id: #{acquirer_terminal_id}")
                  {:error, reason} ->
                    Logger.error("Failed to insert into pos_terminal_acquirer_terminal: #{inspect(reason)}")
                end
              end
            end
            conn
            |> put_status(:created)
            |> json(%{
              status: "success",
              success: true,
              message: "Device and Shukria terminal saved successfully",
              pos_terminal_id: pos_terminal.id,
              shukria_terminal_id: shukria_terminal.id,
              cashDeskId: shukria_terminal.provider_tid,
              terminalidmerchantid: pos_terminal.terminalidmerchantid
            })

          {:error, changeset} ->
            conn
            |> put_status(:unprocessable_entity)
            |> json(%{
              status: "error",
              success: false,
              message: "Device saved, but failed to create Shukria terminal",
              pos_terminal_id: pos_terminal.id,
              terminalidmerchantid: pos_terminal.terminalidmerchantid,
              errors: Ecto.Changeset.traverse_errors(changeset, fn {msg, _opts} -> msg end)
            })
        end

      {:error, changeset} ->
        conn
        |> put_status(:unprocessable_entity)
        |> json(%{
          status: "error",
          success: false,
          message: "Failed to create device",
          errors: Ecto.Changeset.traverse_errors(changeset, fn {msg, _opts} -> msg end)
        })
    end
  end

  def get_terminals_details(conn, %{"store_id" => store_id, "provider_name" => provider_name}) do
    # Find provider by name
    provider =
      Repo.get_by(DaProductApp.Providers.Provider, name: provider_name)

    if provider do
      query =
        from(pt in DaProductApp.PosTerminals.PosTerminal,
          where: pt.store_id == ^store_id,
          join: st in DaProductApp.ShukriaTerminal,
          on:
            st.shukria_terminal_id == type(pt.id, :string) and
              st.provider_id == ^to_string(provider.id),
          select: %{
            id: pt.id,
            name: pt.name,
            serial_number: pt.serial_number,
            device_type: pt.device_type,
            terminal_id: pt.terminal_id,
            provider_id: pt.provider_id,
            status: pt.status,
            store_id: pt.store_id,
            inserted_at: pt.inserted_at,
            updated_at: pt.updated_at
          }
        )

      terminals = Repo.all(query)

      Logger.info(
        "Fetched #{length(terminals)} terminals for store_id: #{store_id} and provider_name: #{provider_name}"
      )

      terminals_array =
        Enum.map(terminals, fn terminal ->
          %{
            id: terminal.id,
            name: terminal.name,
            serial_number: terminal.serial_number,
            device_type: terminal.device_type,
            terminal_id: terminal.terminal_id,
            provider_id: terminal.provider_id,
            status: terminal.status,
            store_id: terminal.store_id,
            inserted_at: terminal.inserted_at,
            updated_at: terminal.updated_at
          }
        end)

      if terminals_array == [] do
        # If no terminals found, create a new terminal
        store = Repo.get(DaProductApp.Stores.Store, store_id)
        store_code = (store && store.code) || "00000"
        brand_id = store && store.brand_id
        brand = brand_id && Repo.get(DaProductApp.Brands.Brand, brand_id)
        merchantRefId = brand && brand.merchant_reference_id
        # Get pos_merchant_id based on merchantRefId
        pos_merchant_query = """
        SELECT id FROM pos_merchant WHERE merchantid = ?
        """

        pos_merchant_id = case Ecto.Adapters.SQL.query(Repo, pos_merchant_query, [merchantRefId]) do
          {:ok, %{rows: [[id] | _]}} -> id
          _ -> nil
        end
        terminal_id = "#{:rand.uniform(100_000_000) |> Integer.to_string() |> String.pad_leading(8, "0")}"
        terminalidmerchantid = "#{:rand.uniform(100_000_000) |> Integer.to_string() |> String.pad_leading(8, "0")}"

        pos_terminal_attrs = %{
          name: "Device",
          serial_number: nil,
          device_type: "SoundBox",
          terminal_id: terminal_id,
          store_id: store && store.id,
          provider_id: provider.id,
          status: "active",
          terminalid: terminal_id,
          pos_merchant_id: pos_merchant_id,
          terminalidmerchantid: terminalidmerchantid
        }

        pos_terminal_changeset =
          DaProductApp.PosTerminals.PosTerminal.changeset(
            %DaProductApp.PosTerminals.PosTerminal{},
            pos_terminal_attrs
          )

        provider_tid =
          :rand.uniform(1_000_000_000_000_000) |> Integer.to_string() |> String.pad_leading(15, "0")

        provider_mid =
          :rand.uniform(100_000_000) |> Integer.to_string() |> String.pad_leading(8, "0")

        case Repo.insert(pos_terminal_changeset) do
          {:ok, pos_terminal} ->
            # Update pos_terminal with correct terminal_id, terminalid, and terminalidmerchantid
            new_terminal_id = "#{pos_terminal.id}#{store.code}" |> String.pad_leading(8, "0")
            new_terminalidmerchantid = "#{store.brand_id}#{store.code}" |> String.pad_leading(8, "0")

            update_changeset = DaProductApp.PosTerminals.PosTerminal.changeset(pos_terminal, %{
              terminal_id: new_terminal_id,
              terminalid: new_terminal_id,
              terminalidmerchantid: new_terminalidmerchantid
            })

            {:ok, pos_terminal} = Repo.update(update_changeset)

            # Create a new ShukriaTerminal after POS terminal is created
            shukria_attrs = %{
              shukria_terminal_id: to_string(pos_terminal.id),
              provider_id: to_string(provider.id),
              provider_tid: provider_tid,
              provider_mid: provider_mid,
              status: "active",
              description: pos_terminal.terminal_id,
              shukria_mid: to_string(store.brand_id)
            }

            shukria_changeset =
              DaProductApp.ShukriaTerminal.changeset(
                %DaProductApp.ShukriaTerminal{},
                shukria_attrs
              )

            case Repo.insert(shukria_changeset) do
              {:ok, shukria_terminal} ->
                conn
                |> put_status(:created)
                |> json(%{
                  status: "success",
                  success: true,
                  message:
                    "No terminals found, new terminal and Shukria terminal created successfully",
                  store_id: store_id,
                  provider_name: provider_name,
                  provider_id: provider.id,
                  terminals: [
                    %{
                      id: pos_terminal.id,
                      name: pos_terminal.name,
                      serial_number: pos_terminal.serial_number,
                      device_type: pos_terminal.device_type,
                      terminal_id: pos_terminal.terminal_id,
                      provider_id: pos_terminal.provider_id,
                      status: pos_terminal.status,
                      store_id: pos_terminal.store_id,
                      inserted_at: pos_terminal.inserted_at,
                      updated_at: pos_terminal.updated_at
                    }
                  ]
                })

              {:error, changeset} ->
                conn
                |> put_status(:unprocessable_entity)
                |> json(%{
                  status: "error",
                  success: false,
                  message:
                    "No terminals found, new terminal created but failed to create Shukria terminal",
                  store_id: store_id,
                  provider_name: provider_name,
                  provider_id: provider.id,
                  terminal: %{
                    id: pos_terminal.id,
                    name: pos_terminal.name,
                    serial_number: pos_terminal.serial_number,
                    device_type: pos_terminal.device_type,
                    terminal_id: pos_terminal.terminal_id,
                    provider_id: pos_terminal.provider_id,
                    status: pos_terminal.status,
                    store_id: pos_terminal.store_id,
                    inserted_at: pos_terminal.inserted_at,
                    updated_at: pos_terminal.updated_at
                  },
                  errors: Ecto.Changeset.traverse_errors(changeset, fn {msg, _opts} -> msg end)
                })
            end

          {:error, changeset} ->
            conn
            |> put_status(:unprocessable_entity)
            |> json(%{
              status: "error",
              success: false,
              message: "No terminals found and failed to create new terminal",
              store_id: store_id,
              provider_name: provider_name,
              provider_id: provider.id,
              errors: Ecto.Changeset.traverse_errors(changeset, fn {msg, _opts} -> msg end)
            })
        end
      else
        conn
        |> put_status(:ok)
        |> json(%{
          status: "success",
          success: true,
          message: "Terminals fetched successfully",
          store_id: store_id,
          provider_name: provider_name,
          provider_id: provider.id,
          terminals: terminals_array
        })
      end
    else
      conn
      |> put_status(:not_found)
      |> json(%{
        status: "error",
        success: false,
        message: "Provider not found for given provider_name",
        store_id: store_id,
        provider_name: provider_name
      })
    end
  end

  def get_terminals_details(conn, _params) do
    conn
    |> put_status(:bad_request)
    |> json(%{
      status: "error",
      success: false,
      message: "Missing store_id or provider_name"
    })
  end

  def getShukriaTerminalsDetails(conn, %{"terminal_id" => terminal_id}) do
    # Fetch the POS terminal
    case Repo.get(DaProductApp.PosTerminals.PosTerminal, terminal_id) do
      nil ->
        conn
        |> put_status(:not_found)
        |> json(%{
          status: "error",
          success: false,
          message: "POS terminal not found",
          terminal_id: terminal_id
        })

      pos_terminal ->
        # Fetch the Shukria terminal
        shukria_terminal =
          Repo.get_by(DaProductApp.ShukriaTerminal,
            shukria_terminal_id: to_string(pos_terminal.id),
            provider_id: to_string(pos_terminal.provider_id)
          )

        if shukria_terminal do
          conn
          |> put_status(:ok)
          |> json(%{
            status: "success",
            success: true,
            message: "Shukria terminal details fetched successfully",
            shukria_terminal: %{
              id: shukria_terminal.id,
              shukria_terminal_id: shukria_terminal.shukria_terminal_id,
              provider_id: shukria_terminal.provider_id,
              provider_tid: shukria_terminal.provider_tid,
              provider_mid: shukria_terminal.provider_mid,
              status: shukria_terminal.status,
              description: shukria_terminal.description,
              inserted_at: shukria_terminal.inserted_at,
              updated_at: shukria_terminal.updated_at
            }
          })
        else
          conn
          |> put_status(:not_found)
          |> json(%{
            status: "error",
            success: false,
            message: "Shukria terminal not found for given POS terminal",
            terminal_id: terminal_id
          })
        end
    end
  end

  def add_mcc_code(conn, %{"merchant_refrence_id" => merchant_ref_id, "mcc_code" => mcc_code}) do
    import Ecto.Query
    alias DaProductApp.Groups.Group
    alias DaProductApp.Repo

    case Repo.get_by(Group, code: merchant_ref_id) do
      nil ->
        conn
        |> put_status(:not_found)
        |> json(%{
          status: "error",
          success: false,
          message: "Merchant group not found",
          merchant_refrence_id: merchant_ref_id
        })

      group ->
        changeset = Group.changeset(group, %{mcc_code: mcc_code})

        case Repo.update(changeset) do
          {:ok, updated_group} ->
            conn
            |> put_status(:ok)
            |> json(%{
              status: "success",
              success: true,
              message: "MCC code updated successfully",
              merchant_refrence_id: merchant_ref_id,
              mcc_code: updated_group.mcc_code
            })

          {:error, changeset} ->
            conn
            |> put_status(:unprocessable_entity)
            |> json(%{
              status: "error",
              success: false,
              message: "Failed to update MCC code",
              errors: Ecto.Changeset.traverse_errors(changeset, fn {msg, _opts} -> msg end),
              merchant_refrence_id: merchant_ref_id
            })
        end
    end
  end

  def get_merchant_store_qr(conn, %{"merchant_refrence_id" => merchant_id}) do
    import Ecto.Query
    alias DaProductApp.MerchantStaticQr.MerchantStoreQr

    qr =
      MerchantStoreQr
      |> where([q], q.merchant_id == ^merchant_id)
      |> order_by([q], desc: q.id)
      |> limit(1)
      |> DaProductApp.Repo.one()

    if qr do
      conn
      |> put_status(:ok)
      |> json(%{
        status: "success",
        success: true,
        message: "Merchant store QR found",
        qr: %{
          id: qr.id,
          merchant_id: qr.merchant_id,
          store_id: qr.store_id,
          static_qr_code: qr.static_qr_code,
          inserted_at: qr.inserted_at,
          updated_at: qr.updated_at
        }
      })
    else
      conn
      |> put_status(:not_found)
      |> json(%{
        status: "error",
        success: false,
        message: "No QR found for given merchant_refrence_id",
        merchant_refrence_id: merchant_id
      })
    end
  end

  def get_static_qr_transactions(conn, params) do
    merchantRefId = params["merchantRefId"]
    paymode = params["paymode"]
    Logger.info("Params received for static QR transactions: #{inspect(params)}")

    if is_nil(merchantRefId) or is_nil(paymode) do
      conn
      |> put_status(:bad_request)
      |> json(%{
        status: "error",
        message: "merchantRefId and paymode are required",
        data: %{transactions: []}
      })
    else
      Logger.info(
        "Fetching static QR transactions for code: #{merchantRefId}, paymode: #{paymode}, params: #{inspect(params)}"
      )

      {start_date, end_date} = get_date_range(params)

      base_query =
        from(t in Transaction,
          where: t.bank_user_id == ^merchantRefId and t.pay_mode == "Static QR" and t.inserted_at >= ^start_date and
              t.inserted_at <= ^end_date,
          join: b in Brand,
           on:
      fragment(
        "CAST(? AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci = CAST(? AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci",
        b.merchant_reference_id,
        t.bank_user_id
      ),
          join: s in Store,
          on: s.brand_id == b.id,
          left_join: pt in PosTerminal,
          on: pt.store_id == s.id
        )

      settlement_query =
        #from([g, b, s, pt, t] in base_query,
        from([t, b, s, pt] in base_query,
          where: not is_nil(t.id) and not is_nil(t.settlement_date_time),
          select: %{
            total_settlement_amount: coalesce(sum(t.transaction_amount), 0)
          }
        )

      transactions_query =
        from([t, b, s, pt] in base_query,
          where: not is_nil(t.id),
          order_by: [desc: t.inserted_at],
          select: %{
            transaction_id: t.id,
            transaction_amount: t.transaction_amount,
            device_id: t.device_id,
            processing_id: t.processing_id,
            status: t.status,
            email: t.email,
            transaction_ref_number: t.transaction_ref_number,
            m_ref_num: t.m_ref_num,
            merchant_id: t.merchant_id,
            bank_user_id: t.bank_user_id,
            ysp_tid: t.ysp_tid,
            payment_reference_id: t.payment_reference_id,
            settlement_date_time: t.settlement_date_time,
            is_settled: not is_nil(t.settlement_date_time),
            provider_name: t.provider_name,
            paymode: t.pay_mode,
            device_details: %{
              device_id: pt.id,
              name: pt.name,
              serial_number: pt.serial_number,
              device_type: pt.device_type,
              terminal_id: pt.terminal_id
            },
            store_details: %{
              store_id: s.id,
              store_name: s.name,
              store_code: s.code,
              neo_merchant_id: s.neo_merchant_id
            },
            brand_details: %{
              brand_id: b.id,
              brand_name: b.name,
              brand_code: b.code
            },
            created_at: t.inserted_at,
            transaction_type: "StaticQR"
          }
        )

      with settlement = %{total_settlement_amount: settlement_amount} <- Repo.one(settlement_query),
           transactions when is_list(transactions) <- Repo.all(transactions_query) |> Enum.uniq_by(& &1.transaction_id) do
        total_amount =
          Enum.reduce(transactions, Decimal.new("0"), fn tx, acc ->
            Decimal.add(acc, tx.transaction_amount || Decimal.new("0"))
          end)

        conn
        |> put_status(:ok)
        |> json(%{
          status: "success",
          count: length(transactions),
          total_amount: total_amount,
          total_settlement_amount: settlement_amount,
          date_range: %{
            start_date: DateTime.to_date(start_date),
            end_date: DateTime.to_date(end_date)
          },
          message: "#{length(transactions)} static QR transaction(s) retrieved successfully",
          data: %{transactions: transactions}
        })
      else
        [] ->
          conn
          |> put_status(:not_found)
          |> json(%{
            status: "error",
            count: 0,
            total_amount: "0.00",
            total_settlement_amount: "0.00",
            date_range: %{
              start_date: DateTime.to_date(start_date),
              end_date: DateTime.to_date(end_date)
            },
            message: "No static QR transactions found for the specified period",
            data: %{transactions: []}
          })
      end
    end
  end

  # Private functions to handle validation
  defp validate_id(id) when is_nil(id), do: {:error, :missing_id}

  defp validate_id(id) do
    case Integer.parse(to_string(id)) do
      {id, ""} -> {:ok, id}
      _ -> {:error, :missing_id}
    end
  end

  defp validate_update_params(params) do
    update_fields = ["chain_name", "chain_code", "description", "phone_number"]
    has_updates = Enum.any?(update_fields, &Map.has_key?(params, &1))

    if has_updates do
      {:ok, params}
    else
      {:error, :no_params}
    end
  end

  defp get_group(id) do
    case Repo.get(Group, id) do
      nil -> {:error, :not_found}
      group -> {:ok, group}
    end
  end

  defp get_store(id) do
    case Repo.get(Store, id) do
      nil -> {:error, :not_found}
      store -> {:ok, store}
    end
  end

  defp validate_required_params(params) do
    required_fields = ["name", "code"]

    missing_fields =
      Enum.filter(required_fields, fn field ->
        is_nil(params[field]) || String.trim(params[field] || "") == ""
      end)

    case missing_fields do
      [] ->
        :ok

      fields ->
        errors =
          Enum.into(fields, %{}, fn field ->
            {field, "#{field} is required and cannot be empty"}
          end)

        {:error, errors}
    end
  end

  defp validate_chain_id(chain_id) when is_nil(chain_id),
    do: {:error, %{chain_id: "Chain ID is required"}}

  defp validate_chain_id(chain_id) do
    case Repo.get(Group, chain_id) do
      nil -> {:error, :chain_not_found}
      _group -> {:ok, chain_id}
    end
  end

  defp validate_brand_id(brand_id) when is_nil(brand_id),
    do: {:error, %{brand_id: "Brand ID is required"}}

  defp validate_brand_id(brand_id) do
    case Repo.get(Brand, brand_id) do
      nil -> {:error, :brand_not_found}
      _brand -> {:ok, brand_id}
    end
  end

  defp validate_store_params(params) do
    required_fields = ["store_code", "store_name", "address_id", "neo_merchant_id"]

    missing_fields =
      Enum.filter(required_fields, fn field ->
        is_nil(params[field]) || String.trim(to_string(params[field]) || "") == ""
      end)

    case missing_fields do
      [] ->
        :ok

      fields ->
        errors =
          Enum.into(fields, %{}, fn field ->
            {field, "#{field} is required and cannot be empty"}
          end)

        {:error, errors}
    end
  end

  defp validate_unique_store_code(store_code) do
    case Repo.get_by(Store, code: store_code) do
      nil -> :ok
      _store -> {:error, :store_code_exists}
    end
  end

  defp validate_unique_store_code_for_update(nil, _current_store_id), do: :ok

  defp validate_unique_store_code_for_update(store_code, current_store_id) do
    case Repo.get_by(Store, code: store_code) do
      nil ->
        :ok

      store ->
        if store.id == current_store_id do
          :ok
        else
          {:error, :store_code_exists}
        end
    end
  end

  defp get_card_date_range(%{"start_date" => start_date, "end_date" => end_date})
       when not is_nil(start_date) and not is_nil(end_date) and start_date != "" and
              end_date != "" do
    {
      parse_datetime(start_date, :start),
      parse_datetime(end_date, :end)
    }
  end

  defp get_card_date_range(_params) do
    today = Date.utc_today()
    # 30 days including today
    start_date = Date.add(today, -29)
    end_date = today

    {
      DateTime.new!(start_date, ~T[00:00:00.000], "Etc/UTC"),
      DateTime.new!(end_date, ~T[23:59:59.999], "Etc/UTC")
    }
  end

  defp parse_datetime(date_string, bound) do
    date = Date.from_iso8601!(date_string)

    time =
      case bound do
        :start -> ~T[00:00:00.000]
        :end -> ~T[23:59:59.999]
      end

    DateTime.new!(date, time, "Etc/UTC")
  end

  # Card datetime range handler - supports both date and datetime formats
  defp get_card_datetime_range(%{"start_date" => start_date, "end_date" => end_date})
       when not is_nil(start_date) and not is_nil(end_date) and start_date != "" and
              end_date != "" do
    {
      parse_card_datetime(start_date, :start),
      parse_card_datetime(end_date, :end)
    }
  end

  defp get_card_datetime_range(_params) do
    today = Date.utc_today()
    # 30 days including today
    start_date = Date.add(today, -29)
    end_date = today

    {
      DateTime.new!(start_date, ~T[00:00:00.000], "Etc/UTC"),
      DateTime.new!(end_date, ~T[23:59:59.999], "Etc/UTC")
    }
  end

  # Helper to parse card datetime - handles both date and datetime formats
  defp parse_card_datetime(datetime_string, bound) do
    # Check if the string contains time (has 'T' separator)
    cond do
      String.contains?(datetime_string, "T") ->
        # Parse as datetime (e.g., "2026-05-01T05:42" or "2026-05-01T05:42:00")
        case NaiveDateTime.from_iso8601(datetime_string) do
          {:ok, naive_dt} ->
            # Convert to UTC DateTime directly (no timezone conversion for cards)
            DateTime.from_naive!(naive_dt, "Etc/UTC")

          {:error, _} ->
            # Fallback: try parsing with seconds added
            case NaiveDateTime.from_iso8601(datetime_string <> ":00") do
              {:ok, naive_dt} ->
                DateTime.from_naive!(naive_dt, "Etc/UTC")
              {:error, _} ->
                # Last fallback: parse as date only
                parse_datetime(datetime_string, bound)
            end
        end

      true ->
        # Parse as date only (e.g., "2026-05-01")
        parse_datetime(datetime_string, bound)
    end
  end

  defp get_date_range(%{"start_date" => start_date, "end_date" => end_date})
       when not is_nil(start_date) and not is_nil(end_date) and start_date != "" and
              end_date != "" do
    {
      parse_uae_date_to_utc(start_date, :start),
      parse_uae_date_to_utc(end_date, :end)
    }
  end

  defp get_date_range(_params) do
    # Get today in UAE timezone first
    today_uae = Date.utc_today()  # Approximate UAE date
    # 30 days including today in UAE
    start_date_uae = Date.add(today_uae, -29)
    end_date_uae = today_uae

    {
      parse_uae_date_to_utc(Date.to_iso8601(start_date_uae), :start),
      parse_uae_date_to_utc(Date.to_iso8601(end_date_uae), :end)
    }
  end

  # Helper to convert UAE date string to UTC DateTime
  defp parse_uae_date_to_utc(date_string, bound) do
    date = Date.from_iso8601!(date_string)

    # Set UAE time based on bound
    uae_time = case bound do
      :start -> ~T[00:00:00.000]  # Start of day in UAE (00:00:00)
      :end -> ~T[23:59:59.999]    # End of day in UAE (23:59:59)
    end

    # Create UAE naive datetime
    uae_naive_datetime = NaiveDateTime.new!(date, uae_time)

    # Convert to UTC by subtracting 4 hours (UAE is UTC+4)
    utc_naive_datetime = NaiveDateTime.add(uae_naive_datetime, -4 * 3600, :second)

    # Convert to DateTime with UTC timezone
    DateTime.from_naive!(utc_naive_datetime, "Etc/UTC")
  end

  # Admin date range handler - supports both date and datetime formats
  defp get_datetime_range(%{"start_date" => start_date, "end_date" => end_date})
       when not is_nil(start_date) and not is_nil(end_date) and start_date != "" and
              end_date != "" do
    {
      parse_uae_datetime_to_utc(start_date, :start),
      parse_uae_datetime_to_utc(end_date, :end)
    }
  end
  defp get_datetime_range(_params) do
    # Get today in UAE timezone first
    today_uae = Date.utc_today()  # Approximate UAE date
    # 30 days including today in UAE
    start_date_uae = Date.add(today_uae, -29)
    end_date_uae = today_uae

    {
      parse_uae_date_to_utc(Date.to_iso8601(start_date_uae), :start),
      parse_uae_date_to_utc(Date.to_iso8601(end_date_uae), :end)
    }
  end

  # Helper to convert UAE datetime string (with or without time) to UTC DateTime
  defp parse_uae_datetime_to_utc(datetime_string, bound) do
    # Check if the string contains time (has 'T' separator)
    cond do
      String.contains?(datetime_string, "T") ->
        # Parse as datetime (e.g., "2026-05-01T05:42" or "2026-05-01T05:42:00")
        case NaiveDateTime.from_iso8601(datetime_string) do
          {:ok, naive_dt} ->
            # Convert UAE datetime to UTC by subtracting 4 hours
            utc_naive_datetime = NaiveDateTime.add(naive_dt, -4 * 3600, :second)
            DateTime.from_naive!(utc_naive_datetime, "Etc/UTC")

          {:error, _} ->
            # Fallback: try parsing with seconds added
            case NaiveDateTime.from_iso8601(datetime_string <> ":00") do
              {:ok, naive_dt} ->
                utc_naive_datetime = NaiveDateTime.add(naive_dt, -4 * 3600, :second)
                DateTime.from_naive!(utc_naive_datetime, "Etc/UTC")
              {:error, _} ->
                # Last fallback: parse as date only
                parse_uae_date_to_utc(datetime_string, bound)
            end
        end

      true ->
        # Parse as date only (e.g., "2026-05-01")
        parse_uae_date_to_utc(datetime_string, bound)
    end
  end

  defp get_store_and_brand_details(merchantRefId) do
    query =
      from(g in Group,
        where: g.code == ^merchantRefId,
        join: b in Brand,
        on: b.group_id == g.id,
        join: s in Store,
        on: s.brand_id == b.id,
        left_join: pt in PosTerminal,
        on: pt.store_id == s.id,
        select: %{
          terminal_id: pt.terminal_id,
          device_id: pt.serial_number,
          device_details: %{
            name: pt.name,
            serial_number: pt.serial_number,
            device_type: pt.device_type,
            terminal_id: pt.terminal_id,
            device_id: pt.id
          },
          store_details: %{
            store_id: s.id,
            store_name: s.name,
            store_code: s.code,
            neo_merchant_id: s.neo_merchant_id
          },
          brand_details: %{
            brand_id: b.id,
            brand_name: b.name,
            brand_code: b.code
          }
        }
      )

    case Repo.all(query) do
      [] ->
        {:error, :not_found}

      details ->
        valid_details = Enum.filter(details, &(&1.terminal_id != nil))
        {:ok, valid_details}
    end
  end

  defp get_card_terminals_details(merchantRefId) do
    # Extract actual merchant ID if it has "Mercury_" prefix
    actual_merchant_id = merchantRefId

    # Query pos_merchant table using LIKE to match from the end
    pos_merchant_query =
      from(pm in "pos_merchant",
        where: pm.merchantid == ^actual_merchant_id,
        select: %{
          id: pm.id,
          merchant_id: pm.merchantid
        },
        limit: 1
      )

    case Repo.one(pos_merchant_query) do
      nil ->
        {:error, :not_found}

      pos_merchant ->
        # Get all terminals from pos_terminal table using pos_merchant_id
        terminals_query =
          from(pt in "pos_terminals",
            where: pt.pos_merchant_id == ^pos_merchant.id,
            left_join: s in "stores",
            on: s.id == pt.store_id,
            left_join: b in "brands",
            on: b.id == s.brand_id,
            select: %{
              id: pt.id,
              terminal_id: pt.terminalid,
              terminalid: pt.terminalid,
              serial_number: pt.serial_number,
              name: pt.name,
              device_type: pt.device_type,
              store_details: %{
                store_id: s.id,
                store_name: s.name,
                store_code: s.code,
                brand_id: s.brand_id,
                neo_merchant_id: s.neo_merchant_id,
                address_id: s.address_id
              },
              brand_details: %{
                brand_id: b.id,
                brand_name: b.name,
                brand_code: b.code,
                merchant_reference_id: b.merchant_reference_id
              }
            }
          )

        terminals = Repo.all(terminals_query)

        if Enum.empty?(terminals) do
          {:error, :not_found}
        else
          # Format terminal details with store and brand info from Group/Brand/Store structure
          formatted_terminals =
            Enum.map(terminals, fn terminal ->
              %{
                terminal_id: terminal.terminal_id,
                terminalid: terminal.terminalid,
                device_id: terminal.serial_number,
                device_details: %{
                  device_id: terminal.id,
                  name: terminal.name,
                  serial_number: terminal.serial_number,
                  device_type: terminal.device_type,
                  terminal_id: terminal.terminalid
                },
                store_details: terminal.store_details,
                brand_details: terminal.brand_details
              }
            end)

          {:ok, formatted_terminals}
        end
    end
  end

  def getProvidersByAlias(conn, %{"provider_alias" => provider_alias}) do
    provider =
      Repo.get_by(DaProductApp.Providers.Provider, name: provider_alias)

    if provider do
      conn
      |> put_status(:ok)
      |> json(%{
        status: "success",
        success: true,
        message: "Provider found",
        provider: %{
          id: provider.id,
          name: provider.name,
          provider_code: provider.provider_code,
          status: provider.status
        }
      })
    else
      conn
      |> put_status(:not_found)
      |> json(%{
        status: "error",
        success: false,
        message: "Provider not found for given alias",
        provider_alias: provider_alias
      })
    end
  end

  def delete_store(conn, %{"id" => id}) do
    Logger.info("Deleting store with id: #{inspect(id)}")

    case Repo.get(Store, id) do
      nil ->
        conn
        |> put_status(:not_found)
        |> json(%{
          status: "error",
          success: false,
          message: "Store not found",
          store_id: id
        })

      store ->
        # Delete related POS terminals and Shukria terminals
        pos_terminals =
          Repo.all(
            from pt in DaProductApp.PosTerminals.PosTerminal,
              where: pt.store_id == ^store.id
          )

        Enum.each(pos_terminals, fn pt ->
          # Delete related ShukriaTerminal(s)
          Repo.delete_all(
            from st in DaProductApp.ShukriaTerminal,
              where: st.shukria_terminal_id == ^to_string(pt.id)
          )
          # Delete POS terminal
          Repo.delete(pt)
        end)

        case Repo.delete(store) do
          {:ok, _struct} ->
            conn
            |> put_status(:ok)
            |> json(%{
              status: "success",
              success: true,
              message: "Store and related terminals deleted successfully",
              store_id: id
            })

          {:error, changeset} ->
            conn
            |> put_status(:unprocessable_entity)
            |> json(%{
              status: "error",
              success: false,
              message: "Failed to delete store",
              errors: Ecto.Changeset.traverse_errors(changeset, fn {msg, _opts} -> msg end),
              store_id: id
            })
        end
    end
  end

  def updateShukriaProviderMidTid(conn, %{"shukriaTerminalId" => shukria_terminal_id, "provider_tid" => provider_tid, "provider_mid" => provider_mid}) do
    # Fetch the Shukria terminal by ID
    case Repo.get(DaProductApp.ShukriaTerminal, shukria_terminal_id) do
      nil ->
        conn
        |> put_status(:not_found)
        |> json(%{
          status: "error",
          success: false,
          message: "Shukria terminal not found",
          shukriaTerminalId: shukria_terminal_id
        })

      shukria_terminal ->
        # Update the Shukria terminal with new provider_tid and provider_mid
        changeset = DaProductApp.ShukriaTerminal.changeset(shukria_terminal, %{
          provider_tid: provider_tid,
          provider_mid: provider_mid
        })

        case Repo.update(changeset) do
          {:ok, updated_shukria_terminal} ->
            conn
            |> put_status(:ok)
            |> json(%{
              status: "success",
              success: true,
              message: "Shukria terminal updated successfully",
              shukria_terminal: %{
                id: updated_shukria_terminal.id,
                shukria_terminal_id: updated_shukria_terminal.shukria_terminal_id,
                provider_id: updated_shukria_terminal.provider_id,
                provider_tid: updated_shukria_terminal.provider_tid,
                provider_mid: updated_shukria_terminal.provider_mid,
                status: updated_shukria_terminal.status,
                description: updated_shukria_terminal.description,
                shukria_mid: updated_shukria_terminal.shukria_mid,
                updated_at: updated_shukria_terminal.updated_at
              }
            })

          {:error, changeset} ->
            conn
            |> put_status(:unprocessable_entity)
            |> json(%{
              status: "error",
              success: false,
              message: "Failed to update Shukria terminal",
              shukriaTerminalId: shukria_terminal_id,
              errors: Ecto.Changeset.traverse_errors(changeset, fn {msg, _opts} -> msg end)
            })
        end
    end
  end

  def updateShukriaProviderMidTid(conn, _params) do
    conn
    |> put_status(:bad_request)
    |> json(%{
      status: "error",
      success: false,
      message: "Missing required parameters: shukriaTerminalId, provider_tid, and provider_mid are required"
    })
  end

  def updateShukriaYspMidTid(conn, %{"shukriaTerminalId" => shukria_terminal_id, "ysp_tid" => ysp_tid, "ysp_mid" => ysp_mid, "mcc_code" => mcc_code}) do
    # Fetch the Shukria terminal by ID
    case Repo.get(DaProductApp.ShukriaTerminal, shukria_terminal_id) do
      nil ->
        conn
        |> put_status(:not_found)
        |> json(%{
          status: "error",
          success: false,
          message: "Shukria terminal not found",
          shukriaTerminalId: shukria_terminal_id
        })

      shukria_terminal ->
        # Get existing shukria_terminal first
        shukria_terminal = Repo.get_by(DaProductApp.ShukriaTerminal,
          id: shukria_terminal_id
        )

        # Generate ysp_mid and ysp_tid if provider_id is 4 and they're missing
        ysp_mid = if ysp_mid && ysp_mid != "" do
          ysp_mid
        else
          :rand.uniform(1_000_000_000_000_000) |> Integer.to_string() |> String.pad_leading(15, "0")
        end

        ysp_tid = if ysp_tid && ysp_tid != "" do
          ysp_tid
        else
          :rand.uniform(100_000_000) |> Integer.to_string() |> String.pad_leading(8, "0")
        end



        # Update the Shukria terminal with new ysp_tid and ysp_mid
        changeset = DaProductApp.ShukriaTerminal.changeset(shukria_terminal, %{
          ysp_tid: ysp_tid,
          ysp_mid: ysp_mid
        })

        case Repo.update(changeset) do
          {:ok, updated_shukria_terminal} ->
            # Get provider_id from updated shukria terminal
            provider_id = updated_shukria_terminal.provider_id

            # Extract ysp_mid and ysp_tid with nil handling
            #ysp_mid_value = if ysp_mid && ysp_mid != "", do: ysp_mid, else: nil
            #ysp_tid_value = if ysp_tid && ysp_tid != "", do: ysp_tid, else: nil
            {ysp_mid_value, ysp_tid_value} = if provider_id == 4 || to_string(provider_id) == "4" do
              generated_ysp_mid = if ysp_mid && ysp_mid != "" do
                ysp_mid
              else
                :rand.uniform(1_000_000_000_000_000) |> Integer.to_string() |> String.pad_leading(15, "0")
              end

              generated_ysp_tid = if ysp_tid && ysp_tid != "" do
                ysp_tid
              else
                :rand.uniform(100_000_000) |> Integer.to_string() |> String.pad_leading(8, "0")
              end

              {generated_ysp_mid, generated_ysp_tid}
            else
              {ysp_mid, ysp_tid}
            end

            # Perform additional logic if conditions are met
            if ysp_tid_value && ysp_mid_value && (provider_id == 4 || to_string(provider_id) == "4") do
              # Get address_id from stores via pos_terminals
              address_id_query = """
              SELECT s.address_id
              FROM pos_terminals pt
              JOIN stores s ON pt.store_id = s.id
              WHERE pt.id = ?
              """

              address_id = case Ecto.Adapters.SQL.query(Repo, address_id_query, [updated_shukria_terminal.shukria_terminal_id]) do
                {:ok, %{rows: [[addr_id] | _]}} -> addr_id
                _ -> nil
              end

              # Insert into acquirer_merchant table
              mcc_code_value = if mcc_code && mcc_code != "", do: mcc_code, else: nil

              # Check if pos_terminal_acquirer_terminal record exists
              check_pos_terminal_acquirer_query = """
              SELECT acquirer_terminal_id FROM pos_terminal_acquirer_terminal WHERE pos_terminal_id = ? LIMIT 1
              """

              case Ecto.Adapters.SQL.query(Repo, check_pos_terminal_acquirer_query, [updated_shukria_terminal.shukria_terminal_id]) do
                {:ok, %{rows: [[existing_acquirer_terminal_id] | _]}} ->
                  # Record exists - UPDATE flow
                  Logger.info("Found existing pos_terminal_acquirer_terminal record with acquirer_terminal_id: #{existing_acquirer_terminal_id}")

                  # Update terminalid in acquirer_terminal
                  update_acquirer_terminal_query = """
                  UPDATE acquirer_terminal SET terminalid = ? WHERE id = ?
                  """

                  case Ecto.Adapters.SQL.query(Repo, update_acquirer_terminal_query, [ysp_tid_value, existing_acquirer_terminal_id]) do
                    {:ok, _} ->
                      Logger.info("Updated acquirer_terminal terminalid to #{ysp_tid_value} for id: #{existing_acquirer_terminal_id}")
                    {:error, reason} ->
                      Logger.error("Failed to update acquirer_terminal: #{inspect(reason)}")
                  end

                  # Get acquirer_merchant_id from acquirer_terminal
                  get_acquirer_merchant_query = """
                  SELECT acquirer_merchant_id FROM acquirer_terminal WHERE id = ?
                  """

                  case Ecto.Adapters.SQL.query(Repo, get_acquirer_merchant_query, [existing_acquirer_terminal_id]) do
                    {:ok, %{rows: [[acquirer_merchant_id] | _]}} ->
                      # Update merchantid in acquirer_merchant
                      update_acquirer_merchant_query = """
                      UPDATE acquirer_merchant SET merchantid = ?, mcc = ? WHERE id = ?
                      """

                      case Ecto.Adapters.SQL.query(Repo, update_acquirer_merchant_query, [ysp_mid_value, mcc_code_value, acquirer_merchant_id]) do
                        {:ok, _} ->
                          Logger.info("Updated acquirer_merchant merchantid to #{ysp_mid_value} and mcc to #{mcc_code_value} for id: #{acquirer_merchant_id}")
                        {:error, reason} ->
                          Logger.error("Failed to update acquirer_merchant: #{inspect(reason)}")
                      end

                    {:error, reason} ->
                      Logger.error("Failed to get acquirer_merchant_id: #{inspect(reason)}")
                  end

                _ ->
                  # Record doesn't exist - INSERT flow
                  Logger.info("No existing pos_terminal_acquirer_terminal record found, inserting new records")

                  insert_acquirer_merchant_query = """
                  INSERT INTO acquirer_merchant (acquirer_id, merchantid, mcc, address_id)
                  VALUES (?, ?, ?, ?)
                  """

                  acquirer_merchant_id = case Ecto.Adapters.SQL.query(Repo, insert_acquirer_merchant_query, [3, ysp_mid_value, mcc_code_value, address_id]) do
                    {:ok, %{num_rows: 1, last_insert_id: id}} ->
                      Logger.info("Inserted into acquirer_merchant with id: #{id} for merchantid: #{ysp_mid_value}")
                      id
                    {:error, reason} ->
                      Logger.error("Failed to insert into acquirer_merchant: #{inspect(reason)}")
                      nil
                  end

                  # Insert into acquirer_terminal table
                  acquirer_terminal_id = if acquirer_merchant_id do
                    insert_acquirer_terminal_query = """
                    INSERT INTO acquirer_terminal (terminalid, acquirer_id, acquirer_merchant_id)
                    VALUES (?, ?, ?)
                    """

                    case Ecto.Adapters.SQL.query(Repo, insert_acquirer_terminal_query, [ysp_tid_value, 3, acquirer_merchant_id]) do
                      {:ok, %{num_rows: 1, last_insert_id: id}} ->
                        Logger.info("Inserted into acquirer_terminal with id: #{id} for terminalid: #{ysp_tid_value}")
                        id
                      {:error, reason} ->
                        Logger.error("Failed to insert into acquirer_terminal: #{inspect(reason)}")
                        nil
                    end
                  else
                    nil
                  end

                  # Insert into pos_terminal_acquirer_terminal table
                  if acquirer_terminal_id do
                    insert_pos_terminal_acquirer_query = """
                    INSERT INTO pos_terminal_acquirer_terminal (pos_terminal_id, acquirer_terminal_id)
                    VALUES (?, ?)
                    """

                    case Ecto.Adapters.SQL.query(Repo, insert_pos_terminal_acquirer_query, [updated_shukria_terminal.shukria_terminal_id, acquirer_terminal_id]) do
                      {:ok, _} ->
                        Logger.info("Inserted into pos_terminal_acquirer_terminal for pos_terminal_id: #{updated_shukria_terminal.shukria_terminal_id}, acquirer_terminal_id: #{acquirer_terminal_id}")
                      {:error, reason} ->
                        Logger.error("Failed to insert into pos_terminal_acquirer_terminal: #{inspect(reason)}")
                    end
                  end
              end
            end

            conn
            |> put_status(:ok)
            |> json(%{
              status: "success",
              success: true,
              message: "Shukria terminal updated successfully",
              shukria_terminal: %{
                id: updated_shukria_terminal.id,
                shukria_terminal_id: updated_shukria_terminal.shukria_terminal_id,
                provider_id: updated_shukria_terminal.provider_id,
                provider_tid: updated_shukria_terminal.provider_tid,
                provider_mid: updated_shukria_terminal.provider_mid,
                ysp_tid: updated_shukria_terminal.ysp_tid,
                ysp_mid: updated_shukria_terminal.ysp_mid,
                status: updated_shukria_terminal.status,
                description: updated_shukria_terminal.description,
                shukria_mid: updated_shukria_terminal.shukria_mid,
                updated_at: updated_shukria_terminal.updated_at
              }
            })

          {:error, changeset} ->
            conn
            |> put_status(:unprocessable_entity)
            |> json(%{
              status: "error",
              success: false,
              message: "Failed to update Shukria terminal",
              shukriaTerminalId: shukria_terminal_id,
              errors: Ecto.Changeset.traverse_errors(changeset, fn {msg, _opts} -> msg end)
            })
        end
    end
  end

  def updateShukriaYspMidTid(conn, _params) do
    conn
    |> put_status(:bad_request)
    |> json(%{
      status: "error",
      success: false,
      message: "Missing required parameters: shukriaTerminalId, ysp_tid, and ysp_mid are required"
    })
  end


def updateDevice(conn, %{"device_id" => device_id, "merchantRefId" => merchant_ref_id} = params) do
  Logger.info("Updating device with params: #{inspect(params)}")

  # Convert device_id to integer for comparison
  device_id = case Integer.parse(device_id) do
    {id, ""} -> id
    _ -> device_id
  end

  # Fetch the POS terminal by ID
  case Repo.get(DaProductApp.PosTerminals.PosTerminal, device_id) do
    nil ->
      conn
      |> put_status(:not_found)
      |> json(%{
        status: "error",
        success: false,
        message: "Device not found",
        device_id: device_id
      })

    pos_terminal ->
      # Check if serial_number is being updated and if it already exists in another device
      if params["serial_number"] && params["serial_number"] != "" do
        existing_terminal = Repo.get_by(PosTerminal, serial_number: params["serial_number"])
        Logger.info("Checking serial number conflict - existing_terminal.id: #{inspect(existing_terminal && existing_terminal.id)}, device_id: #{inspect(device_id)}")
        if existing_terminal && existing_terminal.id != device_id do
          conn
          |> put_status(:conflict)
          |> json(%{
            status: "error",
            success: false,
            message: "Serial number already exists in another device",
            existing_device: %{
              id: existing_terminal.id,
              name: existing_terminal.name,
              serial_number: existing_terminal.serial_number,
              device_type: existing_terminal.device_type,
              terminal_id: existing_terminal.terminal_id,
              store_id: existing_terminal.store_id,
              provider_id: existing_terminal.provider_id,
              status: existing_terminal.status
            }
          })
        else
          # Proceed with update if serial_number is unique or unchanged
          update_device(pos_terminal, params, conn)
        end
      else
        # No serial_number update, proceed
        update_device(pos_terminal, params, conn)
      end
  end
end

# Extract the update logic into a private function for clarity
defp update_device(pos_terminal, params, conn) do
  # Get the existing serial_number before update
  existing_serial_number = pos_terminal.serial_number
  # Prepare update attributes - only include fields that are present in params
  update_attrs = %{}
  |> maybe_put_if_exists("name", params, "name")
  |> maybe_put_if_exists("serial_number", params, "serial_number")
  |> maybe_put_if_exists("terminalid", params, "terminalid")
  |> maybe_put_if_exists("device_type", params, "device_type")
  |> maybe_put_if_exists("terminal_id", params, "terminalid")
  |> maybe_put_if_exists("status", params, "status")

  # Update the POS terminal
  changeset = PosTerminal.changeset(pos_terminal, update_attrs)

  case Repo.update(changeset) do
    {:ok, updated_pos_terminal} ->
      # Update pos_terminal table with raw SQL
      update_pos_terminal_query = """
      UPDATE pos_terminal
      SET terminalid = ?, serial_number = ?, pos_merchant_id = ?
      WHERE id = ?
      """

      case Ecto.Adapters.SQL.query(Repo, update_pos_terminal_query, [
        updated_pos_terminal.terminalid,
        updated_pos_terminal.serial_number,
        updated_pos_terminal.pos_merchant_id,
        updated_pos_terminal.id
      ]) do
        {:ok, _result} ->
          Logger.info("Successfully updated pos_terminal record for id: #{updated_pos_terminal.id}")
          # Insert into pos_log_terminal
          insert_log_query = """
          INSERT INTO pos_log_terminal (pos_terminal_id, old_serial_number, new_serial_number, date_added, inserted_at, updated_at)
          VALUES (?, ?, ?, NOW(), NOW(), NOW())
          """
          Ecto.Adapters.SQL.query(Repo, insert_log_query, [
            updated_pos_terminal.id,
            existing_serial_number,
            updated_pos_terminal.serial_number
          ])
        {:error, reason} ->
          Logger.error("Failed to update pos_terminal table: #{inspect(reason)}")
      end
      conn
      |> put_status(:ok)
      |> json(%{
        status: "success",
        success: true,
        message: "Device updated successfully",
        device: %{
          id: updated_pos_terminal.id,
          name: updated_pos_terminal.name,
          serial_number: updated_pos_terminal.serial_number,
          device_type: updated_pos_terminal.device_type,
          terminal_id: updated_pos_terminal.terminal_id,
          store_id: updated_pos_terminal.store_id,
          provider_id: updated_pos_terminal.provider_id,
          status: updated_pos_terminal.status,
          updated_at: updated_pos_terminal.updated_at
        }
      })

    {:error, changeset} ->
      conn
      |> put_status(:unprocessable_entity)
      |> json(%{
        status: "error",
        success: false,
        message: "Failed to update device",
        device_id: pos_terminal.id,
        errors: Ecto.Changeset.traverse_errors(changeset, fn {msg, _opts} -> msg end)
      })
  end
end

def updateDevice(conn, _params) do
  conn
  |> put_status(:bad_request)
  |> json(%{
    status: "error",
    success: false,
    message: "Missing required parameters: device_id and merchantRefId are required"
  })
end

def forceUpdateDevice(conn, %{"device_id" => device_id, "merchantRefId" => merchant_ref_id} = params) do
  Logger.info("Force updating device with params: #{inspect(params)}")

  # Fetch the POS terminal by ID
  case Repo.get(DaProductApp.PosTerminals.PosTerminal, device_id) do
    nil ->
      conn
      |> put_status(:not_found)
      |> json(%{
        status: "error",
        success: false,
        message: "Device not found",
        device_id: device_id
      })

    pos_terminal ->
      # If serial_number is provided, force remove it from any existing device
      if params["serial_number"] && params["serial_number"] != "" do
        existing_terminal = Repo.get_by(PosTerminal, serial_number: params["serial_number"])
        if existing_terminal && existing_terminal.id != device_id do
          existing_serial_number = existing_terminal.serial_number
          # Remove serial_number from existing device
          changeset = PosTerminal.changeset(existing_terminal, %{serial_number: nil})
          case Repo.update(changeset) do
            {:ok, _} ->
              # Insert log into pos_log_terminal
              insert_log_query = """
              INSERT INTO pos_log_terminal (pos_terminal_id, old_serial_number, new_serial_number, date_added, inserted_at, updated_at)
              VALUES (?, ?, ?, NOW(), NOW(), NOW())
              """
              Ecto.Adapters.SQL.query(Repo, insert_log_query, [
                existing_terminal.id,
                existing_serial_number,
                nil
              ])
              # Explicitly set serial_number to NULL in the database
              update_serial_query = """
              UPDATE pos_terminal SET serial_number = NULL WHERE id = ?
              """
              Ecto.Adapters.SQL.query(Repo, update_serial_query, [existing_terminal.id])


              # Proceed with update
              force_update_device(pos_terminal, params, conn)
            {:error, _} ->
              conn
              |> put_status(:unprocessable_entity)
              |> json(%{
                status: "error",
                success: false,
                message: "Failed to remove serial number from existing device",
                device_id: device_id
              })
          end
        else
          # No existing device or it's the same, proceed
          force_update_device(pos_terminal, params, conn)
        end
      else
        # No serial_number update, proceed
        force_update_device(pos_terminal, params, conn)
      end
  end
end
defp force_update_device(pos_terminal, params, conn) do
  existing_serial_number = pos_terminal.serial_number
  # Prepare update attributes
  update_attrs = %{}
  |> maybe_put("name", params["name"])
  |> maybe_put("serial_number", params["serial_number"])
  |> maybe_put("device_type", params["device_type"])
  |> maybe_put("terminal_id", params["terminalid"])
  |> maybe_put("terminalid", params["terminalid"])
  |> maybe_put("status", params["status"])

  # Update the POS terminal
  changeset = PosTerminal.changeset(pos_terminal, update_attrs)

  case Repo.update(changeset) do
    {:ok, updated_pos_terminal} ->
      # Insert/Update pos_terminal table with raw SQL
      upsert_pos_terminal_query = """
      INSERT INTO pos_terminal (id, terminalid, serial_number, pos_merchant_id)
      VALUES (?, ?, ?, ?)
      ON DUPLICATE KEY UPDATE
        terminalid = VALUES(terminalid),
        serial_number = VALUES(serial_number),
        pos_merchant_id = VALUES(pos_merchant_id)
      """

      case Ecto.Adapters.SQL.query(Repo, upsert_pos_terminal_query, [
        updated_pos_terminal.id,
        updated_pos_terminal.terminalid,
        updated_pos_terminal.serial_number,
        updated_pos_terminal.pos_merchant_id
      ]) do
        {:ok, _result} ->
          Logger.info("Successfully inserted/updated pos_terminal record for id: #{updated_pos_terminal.id}")
          # Insert into pos_log_terminal
          insert_log_query = """
          INSERT INTO pos_log_terminal (pos_terminal_id, old_serial_number, new_serial_number, date_added, inserted_at, updated_at)
          VALUES (?, ?, ?, NOW(), NOW(), NOW())
          """
          Ecto.Adapters.SQL.query(Repo, insert_log_query, [
            updated_pos_terminal.id,
            existing_serial_number,
            updated_pos_terminal.serial_number
          ])
        {:error, reason} ->
          Logger.error("Failed to insert/update pos_terminal table: #{inspect(reason)}")
      end

      conn
      |> put_status(:ok)
      |> json(%{
        status: "success",
        success: true,
        message: "Device force updated successfully",
        device: %{
          id: updated_pos_terminal.id,
          name: updated_pos_terminal.name,
          serial_number: updated_pos_terminal.serial_number,
          device_type: updated_pos_terminal.device_type,
          terminal_id: updated_pos_terminal.terminal_id,
          store_id: updated_pos_terminal.store_id,
          provider_id: updated_pos_terminal.provider_id,
          status: updated_pos_terminal.status,
          updated_at: updated_pos_terminal.updated_at
        }
      })

    {:error, changeset} ->
      conn
      |> put_status(:unprocessable_entity)
      |> json(%{
        status: "error",
        success: false,
        message: "Failed to force update device",
        device_id: pos_terminal.id,
        errors: Ecto.Changeset.traverse_errors(changeset, fn {msg, _opts} -> msg end)
      })
  end
end

def forceUpdateDevice(conn, _params) do
  conn
  |> put_status(:bad_request)
  |> json(%{
    status: "error",
    success: false,
    message: "Missing required parameters: device_id and merchantRefId are required"
  })
end

# Helper function to conditionally add fields to update map only if key exists in params
defp maybe_put_if_exists(map, _field_key, params, param_key) do
  if Map.has_key?(params, param_key) do
    Map.put(map, _field_key, params[param_key])
  else
    map
  end
end

# Old helper function still used in other places
defp maybe_put(map, _key, nil), do: map
defp maybe_put(map, _key, ""), do: map
defp maybe_put(map, key, value), do: Map.put(map, key, value)
def get_admin_transactions(conn, params) do
  import Ecto.Query

  {start_date, end_date} = get_datetime_range(params)

  query =
    from t in DaProductApp.Transactions.Transaction,
      left_join: pt in DaProductApp.PosTerminals.PosTerminal,
      on: fragment(
        "CAST(? AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci = CAST(? AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci",
        pt.serial_number,
        t.device_id
      ),
      left_join: st in DaProductApp.ShukriaTerminal,
      on: st.shukria_terminal_id == pt.id and st.provider_id == t.provider_id,
      left_join: s in DaProductApp.Stores.Store,
      on: pt.store_id == s.id,
      where: t.inserted_at >= ^start_date and
             t.inserted_at <= ^end_date,
      order_by: [desc: t.id],
      select: %{
        transaction_id: t.id,
        transaction_amount: t.transaction_amount,
        device_id: t.device_id,
        processing_id: t.processing_id,
        status: t.status,
        email: t.email,
        transaction_ref_number: t.transaction_ref_number,
        m_ref_num: t.m_ref_num,
        merchant_id: t.shukria_mid,
        ysp_tid: st.ysp_tid,
        ysp_mid: st.ysp_mid,
        bank_user_id: t.bank_user_id,
        payment_reference_id: t.payment_reference_id,
        settlement_date_time: t.settlement_date_time,
        is_settled: not is_nil(t.settlement_date_time),
        provider_name: t.provider_name,
        provider_id: t.provider_id,
        paymode: t.pay_mode,
        created_at: t.inserted_at,
        device_details: %{
          id: pt.id,
          name: pt.name,
          serial_number: pt.serial_number,
          device_type: pt.device_type,
          terminal_id: pt.terminal_id,
          provider_id: pt.provider_id,
          store_id: pt.store_id,
          status: pt.status
        },
        store_details: %{
          store_id: s.id,
          store_code: s.code,
          store_name: s.name,
          brand_id: s.brand_id,
          neo_merchant_id: s.neo_merchant_id,
          address_id: s.address_id
        },
        transaction_type: "QR"
      }

  transactions = DaProductApp.Repo.all(query)
  # Convert the timezone in Elixir:
  transactions =
    Enum.map(transactions, fn tx ->
      Map.update!(tx, :created_at, &to_uae_datetime/1)
    end)

  total_amount =
    Enum.reduce(transactions, Decimal.new("0"), fn tx, acc ->
      Decimal.add(acc, tx.transaction_amount || Decimal.new("0"))
    end)

  conn
  |> put_status(:ok)
  |> json(%{
    status: "success",
    count: length(transactions),
    total_amount: total_amount,
    date_range: %{
      start_date: DateTime.to_date(start_date),
      end_date: DateTime.to_date(end_date)
    },
    message: "#{length(transactions)} transaction(s) retrieved successfully",
    data: %{transactions: transactions}
  })
end

def get_admin_card_transactions(conn, params) do
  import Ecto.Query

  {start_date, end_date} = get_datetime_range(params)
  #formatted_start_date = DateTime.to_date(start_date) |> Date.to_iso8601()
  #formatted_end_date = DateTime.to_date(end_date) |> Date.to_iso8601()

  # Prepare params for the direct function call
  function_params = %{
    "start_date" => start_date,
    "end_date" => end_date
  }

  # Directly call the function from PosTransactionController
  DaProductAppWeb.PosTransactionController.get_all_merchant_card_transaction(conn, function_params)
end


def createShukriaTerminalsDetails(conn, params) do
  provider_mid =
    case params["provider_mid"] do
      nil -> :rand.uniform(100_000_000) |> Integer.to_string() |> String.pad_leading(8, "0")
      "" -> :rand.uniform(100_000_000) |> Integer.to_string() |> String.pad_leading(8, "0")
      val -> val
    end

  provider_tid =
    case params["provider_tid"] do
      nil -> :rand.uniform(1_000_000_000_000_000) |> Integer.to_string() |> String.pad_leading(15, "0")
      "" -> :rand.uniform(1_000_000_000_000_000) |> Integer.to_string() |> String.pad_leading(15, "0")
      val -> val
    end

  ysp_mid = if params["ysp_mid"] && params["ysp_mid"] != "" do
    params["ysp_mid"]
  else
    :rand.uniform(1_000_000_000_000_000) |> Integer.to_string() |> String.pad_leading(15, "0")
  end

  ysp_tid = if params["ysp_tid"] && params["ysp_tid"] != "" do
    params["ysp_tid"]
  else
    :rand.uniform(100_000_000) |> Integer.to_string() |> String.pad_leading(8, "0")
  end

  attrs = %{
    "shukria_terminal_id" => to_string(params["shukria_terminal_id"]),
    "provider_id" => to_string(params["provider_id"]),
    "provider_mid" => provider_mid,
    "provider_tid" => provider_tid,
    "status" => params["status"] || "active",
    "description" => params["description"] || "Neo terminal",
    "shukria_mid" => to_string(params["shukria_mid"])
  }
  |> maybe_put("ysp_tid", ysp_tid)
  |> maybe_put("ysp_mid", ysp_mid)

  changeset = DaProductApp.ShukriaTerminal.changeset(%DaProductApp.ShukriaTerminal{}, attrs)

  # Get address_id from stores via pos_terminals
  address_id_query = """
  SELECT s.address_id
  FROM pos_terminals pt
  JOIN stores s ON pt.store_id = s.id
  WHERE pt.id = ?
  """

  address_id = case Ecto.Adapters.SQL.query(Repo, address_id_query, [params["shukria_terminal_id"]]) do
    {:ok, %{rows: [[addr_id] | _]}} -> addr_id
    _ -> nil
  end

  # Only insert into acquirer tables if provider_id is 4
  mcc_code = if params["mcc_code"] && params["mcc_code"] != "", do: params["mcc_code"], else: nil
  #ysp_mid = if params["ysp_mid"] && params["ysp_mid"] != "", do: params["ysp_mid"], else: nil
  #ysp_tid = if params["ysp_tid"] && params["ysp_tid"] != "", do: params["ysp_tid"], else: nil


  # Check if record already exists in pos_terminal_acquirer_terminal
  check_existing_query = """
  SELECT id FROM pos_terminal_acquirer_terminal WHERE pos_terminal_id = ?
  """

  existing_record = case Ecto.Adapters.SQL.query(Repo, check_existing_query, [params["shukria_terminal_id"]]) do
    {:ok, %{rows: []}} -> nil
    {:ok, %{rows: _}} -> :exists
    {:error, _} -> :error
  end

  # Only proceed if no existing record found
  if is_nil(existing_record) && ysp_mid && ysp_tid && (params["provider_id"] == 4 || to_string(params["provider_id"]) == "4") do
    # Insert into acquirer_merchant table
    insert_acquirer_merchant_query = """
    INSERT INTO acquirer_merchant (acquirer_id, merchantid, mcc, address_id)
    VALUES (?, ?, ?, ?)
    """

    acquirer_merchant_id = case Ecto.Adapters.SQL.query(Repo, insert_acquirer_merchant_query, [3, ysp_mid, mcc_code, address_id]) do
      {:ok, %{num_rows: 1, last_insert_id: id}} ->
        Logger.info("Inserted into acquirer_merchant with id: #{id} for merchantid: #{ysp_mid}")
        id
      {:error, reason} ->
        Logger.error("Failed to insert into acquirer_merchant: #{inspect(reason)}")
        nil
    end

    # Insert into acquirer_terminal table
    acquirer_terminal_id = if acquirer_merchant_id do
      insert_acquirer_terminal_query = """
      INSERT INTO acquirer_terminal (terminalid, acquirer_id, acquirer_merchant_id)
      VALUES (?, ?, ?)
      """

      case Ecto.Adapters.SQL.query(Repo, insert_acquirer_terminal_query, [ysp_tid, 3, acquirer_merchant_id]) do
        {:ok, %{num_rows: 1, last_insert_id: id}} ->
          Logger.info("Inserted into acquirer_terminal with id: #{id} for terminalid: #{ysp_tid}")
          id
        {:error, reason} ->
          Logger.error("Failed to insert into acquirer_terminal: #{inspect(reason)}")
          nil
      end
    else
      nil
    end

    # Insert into pos_terminal_acquirer_terminal table
    if acquirer_terminal_id do
      insert_pos_terminal_acquirer_query = """
      INSERT INTO pos_terminal_acquirer_terminal (pos_terminal_id, acquirer_terminal_id)
      VALUES (?, ?)
      """

      case Ecto.Adapters.SQL.query(Repo, insert_pos_terminal_acquirer_query, [params["shukria_terminal_id"], acquirer_terminal_id]) do
        {:ok, _} ->
          Logger.info("Inserted into pos_terminal_acquirer_terminal for pos_terminal_id: #{params["shukria_terminal_id"]}, acquirer_terminal_id: #{acquirer_terminal_id}")
        {:error, reason} ->
          Logger.error("Failed to insert into pos_terminal_acquirer_terminal: #{inspect(reason)}")
      end
    end


  end


  case Repo.insert(changeset) do
    {:ok, shukria_terminal} ->
      conn
      |> put_status(:created)
      |> json(%{
        status: "success",
        message: "Shukria terminal created successfully",
        success: true,
        shukria_terminal: %{
          id: shukria_terminal.id,
          shukria_terminal_id: shukria_terminal.shukria_terminal_id,
          provider_id: shukria_terminal.provider_id,
          provider_tid: shukria_terminal.provider_tid,
          provider_mid: shukria_terminal.provider_mid,
          ysp_tid: shukria_terminal.ysp_tid,
          ysp_mid: shukria_terminal.ysp_mid,
          status: shukria_terminal.status,
          description: shukria_terminal.description,
          inserted_at: shukria_terminal.inserted_at,
          updated_at: shukria_terminal.updated_at
        }
      })

    {:error, changeset} ->
      conn
      |> put_status(:unprocessable_entity)
      |> json(%{
        status: "error",
        message: "Failed to create Shukria terminal",
        errors: Ecto.Changeset.traverse_errors(changeset, fn {msg, _opts} -> msg end)
      })
  end
end
def updateStore(conn, %{"store_id" => store_id_str, "merchantRefId" => merchant_ref_id} = params) do
  Logger.info("Updating store with params: #{inspect(params)}")

  # Parse store_id to integer
  case Integer.parse(store_id_str) do
    {store_id, ""} ->
      # Fetch the store by ID
      case Repo.get(Store, store_id) do
        nil ->
          conn
          |> put_status(:not_found)
          |> json(%{
            status: "error",
            success: false,
            message: "Store not found",
            store_id: store_id_str
          })

        store ->
          # Check if store_code is being updated and if it already exists in another store
          if params["store_code"] && params["store_code"] != "" do
            existing_store = Repo.get_by(Store, code: params["store_code"])
            if existing_store && existing_store.id != store_id do
              conn
              |> put_status(:conflict)
              |> json(%{
                status: "error",
                success: false,
                message: "Store code already exists in another store",
                existing_store: %{
                  id: existing_store.id,
                  name: existing_store.name,
                  code: existing_store.code,
                  address_id: existing_store.address_id,
                  brand_id: existing_store.brand_id                }
              })
            else
              # Proceed with update if store_code is unique or unchanged
              update_store(store, params, conn)
            end
          else
            # No store_code update, proceed
            update_store(store, params, conn)
          end
      end

    _ ->
      conn
      |> put_status(:bad_request)
      |> json(%{
        status: "error",
        success: false,
        message: "Invalid store_id",
        store_id: store_id_str
      })
  end
end
defp update_store(store, params, conn) do
  # Prepare update attributes
  update_attrs = %{}
  |> maybe_put("name", params["store_name"])
  |> maybe_put("code", params["store_code"])

  # Update the store
  changeset = Store.changeset(store, update_attrs)

  case Repo.update(changeset) do
    {:ok, updated_store} ->
      conn
      |> put_status(:ok)
      |> json(%{
        status: "success",
        success: true,
        message: "Store updated successfully",
        store: %{
          id: updated_store.id,
          name: updated_store.name,
          code: updated_store.code,
          address_id: updated_store.address_id,
          brand_id: updated_store.brand_id,
          updated_at: updated_store.updated_at
        }
      })

    {:error, changeset} ->
      conn
      |> put_status(:unprocessable_entity)
      |> json(%{
        status: "error",
        success: false,
        message: "Failed to update store",
        store_id: store.id,
        errors: Ecto.Changeset.traverse_errors(changeset, fn {msg, _opts} -> msg end)
      })
  end
end

def updateStore(conn, _params) do
  conn
  |> put_status(:bad_request)
  |> json(%{
    status: "error",
    success: false,
    message: "Missing required parameters: store_id and merchantRefId are required"
  })
end

def updateAddress(conn, %{"address_id" => address_id_str, "merchantRefId" => merchant_ref_id} = params) do
  Logger.info("Updating address with params: #{inspect(params)}")

  # Parse address_id to integer
  case Integer.parse(address_id_str) do
    {address_id, ""} ->
      # Fetch the address by ID
      case Repo.get(Address, address_id) do
        nil ->
          conn
          |> put_status(:not_found)
          |> json(%{
            status: "error",
            success: false,
            message: "Address not found",
            address_id: address_id_str
          })

        address ->
          # Proceed with update
          update_address(address, params, conn)
      end

    _ ->
      conn
      |> put_status(:bad_request)
      |> json(%{
        status: "error",
        success: false,
        message: "Invalid address_id",
        address_id: address_id_str
      })
  end
end

defp update_address(address, params, conn) do
  # Prepare update attributes for addresses table
  addresses_attrs = %{}
  |> maybe_put("line1", params["street"])
  |> maybe_put("city", params["city"])
  |> maybe_put("state", params["state"])
  |> maybe_put("country", params["country"])
  |> maybe_put("country_code", params["country_code"])
  |> maybe_put("zipcode", params["zipcode"])

  # Update the addresses table
  changeset = Address.changeset(address, addresses_attrs)

  case Repo.update(changeset) do
    {:ok, updated_address} ->
      # After successfully updating addresses table, update address table with raw query
      update_address_table_query(address.id, params)

      conn
      |> put_status(:ok)
      |> json(%{
        status: "success",
        success: true,
        message: "Address updated successfully in both tables",
        address: %{
          id: updated_address.id,
          line1: updated_address.line1,
          line2: updated_address.line2,
          line3: updated_address.line3,
          line4: updated_address.line4,
          city: updated_address.city,
          state: updated_address.state,
          country: updated_address.country,
          country_code: updated_address.country_code,
          zipcode: updated_address.zipcode,
          updated_at: updated_address.updated_at
        }
      })

    {:error, changeset} ->
      conn
      |> put_status(:unprocessable_entity)
      |> json(%{
        status: "error",
        success: false,
        message: "Failed to update address",
        address_id: address.id,
        errors: Ecto.Changeset.traverse_errors(changeset, fn {msg, _opts} -> msg end)
      })
  end
end

defp update_address_table_query(address_id, params) do
  # Build the SET clause dynamically based on provided parameters
  # Truncate street to 45 characters for the address table
  truncated_street = if params["street"], do: String.slice(params["street"], 0, 45), else: nil

  set_clauses = []
  |> maybe_add_set_clause("line1", truncated_street)
  |> maybe_add_set_clause("city", params["city"])
  |> maybe_add_set_clause("state", params["state"])
  |> maybe_add_set_clause("country", params["country"])
  |> maybe_add_set_clause("postalcode", params["zipcode"])

  unless Enum.empty?(set_clauses) do
    set_clause_str = Enum.join(set_clauses, ", ")

    query = """
    UPDATE address
    SET #{set_clause_str}
    WHERE id = #{address_id}
    """

    Logger.info("Executing address table update query: #{query}")

    case Repo.query(query) do
      {:ok, result} ->
        Logger.info("Address table updated successfully: #{inspect(result)}")
        {:ok, result}
      {:error, error} ->
        Logger.error("Failed to update address table: #{inspect(error)}")
        {:error, error}
    end
  end
end

defp maybe_add_set_clause(clauses, _field, nil), do: clauses
defp maybe_add_set_clause(clauses, _field, ""), do: clauses
defp maybe_add_set_clause(clauses, field, value) do
  # Escape the value to prevent SQL injection
  escaped_value = value |> String.replace("'", "''")
  clauses ++ ["#{field} = '#{escaped_value}'"]
end

def updateAddress(conn, _params) do
  conn
  |> put_status(:bad_request)
  |> json(%{
    status: "error",
    success: false,
    message: "Missing required parameters: address_id and merchantRefId are required"
  })
end

def checkExistingAnniShukriaTerminal(conn, %{"store_id" => store_id, "provider_id" => provider_id}) do
  Logger.info("Checking existing Anni Shukria terminal for store_id: #{store_id}, provider_id: #{provider_id}")

  # Get the first POS terminal (ASC order, limit 1) for the given store_id and provider_id
  pos_terminal_query =
    from(pt in PosTerminal,
      where: pt.store_id == ^store_id and pt.provider_id == ^provider_id,
      order_by: [asc: pt.id],
      limit: 1
    )

  case Repo.one(pos_terminal_query) do
    nil ->
      conn
      |> put_status(:not_found)
      |> json(%{
        status: "error",
        success: false,
        message: "No POS terminal found for the given store_id and provider_id",
        store_id: store_id,
        provider_id: provider_id
      })

    pos_terminal ->
      # Get all Shukria terminals for this POS terminal
      shukria_terminals_query =
        from(st in DaProductApp.ShukriaTerminal,
          where: st.shukria_terminal_id == ^to_string(pos_terminal.id)and st.provider_id == ^to_string(provider_id),
          order_by: [asc: st.id]
        )

      shukria_terminals = Repo.all(shukria_terminals_query)

      # Get brand details from shukria_mid (which is the brand_id)
      brand_details =
        if length(shukria_terminals) > 0 do
          first_shukria = hd(shukria_terminals)

          if first_shukria.shukria_mid do
            case Integer.parse(first_shukria.shukria_mid) do
              {brand_id, ""} ->
                case Repo.get(Brand, brand_id) do
                  nil -> nil
                  brand -> %{
                    id: brand.id,
                    name: brand.name,
                    code: brand.code,
                    description: brand.description,
                    merchant_reference_id: brand.merchant_reference_id,
                    region: brand.region,
                    group_id: brand.group_id,
                    merchant_tag: brand.merchant_tag
                  }
                end
              _ -> nil
            end
          else
            nil
          end
        else
          nil
        end

      # Format Shukria terminals
      formatted_shukria_terminals =
        Enum.map(shukria_terminals, fn st ->
          %{
            id: st.id,
            shukria_terminal_id: st.shukria_terminal_id,
            provider_id: st.provider_id,
            provider_tid: st.provider_tid,
            provider_mid: st.provider_mid,
            shukria_mid: st.shukria_mid,
            ysp_tid: st.ysp_tid,
            ysp_mid: st.ysp_mid,
            status: st.status,
            description: st.description,
            inserted_at: st.inserted_at,
            updated_at: st.updated_at
          }
        end)

      conn
      |> put_status(:ok)
      |> json(%{
        status: "success",
        success: true,
        message: "POS terminal and Shukria terminals found successfully",
        data: %{
          pos_terminal: %{
            id: pos_terminal.id,
            name: pos_terminal.name,
            serial_number: pos_terminal.serial_number,
            device_type: pos_terminal.device_type,
            terminal_id: pos_terminal.terminal_id,
            provider_id: pos_terminal.provider_id,
            store_id: pos_terminal.store_id,
            status: pos_terminal.status,
            inserted_at: pos_terminal.inserted_at,
            updated_at: pos_terminal.updated_at
          },
          shukria_terminals: formatted_shukria_terminals,
          shukria_terminals_count: length(formatted_shukria_terminals),
          brand_details: brand_details
        }
      })
  end
end

def checkExistingAnniShukriaTerminal(conn, _params) do
  conn
  |> put_status(:bad_request)
  |> json(%{
    status: "error",
    success: false,
    message: "Missing required parameters: store_id and provider_id are required"
  })
end

  def check_duplicate_tid_mid(conn, params) do
    Logger.info("Checking for duplicate TID and MID with params: #{inspect(params)}")


    # Check for duplicate TID and MID in pos_terminals table
    stid = params["stid"]
    smid = params["smid"]
    device_id = params["device_id"]
    Logger.info("Checking duplicates for stid: #{stid}, smid: #{smid}, excluding device_id: #{device_id}")




    # Check stid (terminalid) if provided
    stid_duplicate = if stid && stid != "" do
      stid_query = if device_id && device_id != "" do

        from(pt in PosTerminal,
          where: pt.terminalid == ^stid and pt.id != ^device_id,
          limit: 1
        )
      else
        from(pt in PosTerminal,
          where: pt.terminalid == ^stid,
          limit: 1
        )
      end

      Repo.one(stid_query)
    else
      nil
    end

    # Check smid (terminalidmerchantid) if provided
    smid_duplicate = if smid && smid != "" do
      smid_query = if device_id && device_id != "" do
        from(pt in PosTerminal,
          where: pt.terminalidmerchantid == ^smid and pt.id != ^device_id,
          limit: 1
        )
      else
        from(pt in PosTerminal,
          where: pt.terminalidmerchantid == ^smid,
          limit: 1
        )
      end

      Repo.one(smid_query)
    else
      nil
    end

    # Determine response based on duplicates found
    cond do
      stid_duplicate && smid_duplicate ->
        # Both TID and MID are duplicates
        conn
        |> put_status(:ok)
        |> json(%{
          status: "success",
          success: true,
          message: "Both Shukria terminalId #{stid_duplicate.terminalid}  and merchantId #{smid_duplicate.terminalidmerchantid} are already configured",
          duplicate_found: true,
          duplicate_type: "both",
          duplicate_terminal_tid: %{
            id: stid_duplicate.id,
            name: stid_duplicate.name,
            serial_number: stid_duplicate.serial_number,
            device_type: stid_duplicate.device_type,
            terminal_id: stid_duplicate.terminalid,
            merchant_id: stid_duplicate.terminalidmerchantid,
            provider_id: stid_duplicate.provider_id,
            store_id: stid_duplicate.store_id,
            status: stid_duplicate.status
          },
          duplicate_terminal_mid: %{
            id: smid_duplicate.id,
            name: smid_duplicate.name,
            serial_number: smid_duplicate.serial_number,
            device_type: smid_duplicate.device_type,
            terminal_id: smid_duplicate.terminalid,
            merchant_id: smid_duplicate.terminalidmerchantid,
            provider_id: smid_duplicate.provider_id,
            store_id: smid_duplicate.store_id,
            status: smid_duplicate.status
          }
        })
        stid_duplicate ->
        # Only TID is duplicate
        conn
        |> put_status(:ok)
        |> json(%{
          status: "success",
          success: true,
          message: "This Shukria terminalId is already configured",
          message: "This Shukria terminalId #{stid_duplicate.terminalid} is already configured",
          duplicate_found: true,
          duplicate_type: "tid",
          duplicate_terminal: %{
            id: stid_duplicate.id,
            name: stid_duplicate.name,
            serial_number: stid_duplicate.serial_number,
            device_type: stid_duplicate.device_type,
            terminal_id: stid_duplicate.terminalid,
            merchant_id: stid_duplicate.terminalidmerchantid,
            provider_id: stid_duplicate.provider_id,
            store_id: stid_duplicate.store_id,
            status: stid_duplicate.status
          }
        })
        smid_duplicate ->
        # Only MID is duplicate
        conn
        |> put_status(:ok)
        |> json(%{
          status: "success",
          success: true,
          message: "This Shukria merchantId #{smid_duplicate.terminalidmerchantid} is already configured",
          duplicate_found: true,
          duplicate_type: "mid",
          duplicate_terminal: %{
            id: smid_duplicate.id,
            name: smid_duplicate.name,
            serial_number: smid_duplicate.serial_number,
            device_type: smid_duplicate.device_type,
            terminal_id: smid_duplicate.terminalid,
            merchant_id: smid_duplicate.terminalidmerchantid,
            provider_id: smid_duplicate.provider_id,
            store_id: smid_duplicate.store_id,
            status: smid_duplicate.status
          }
        })

        true ->
        # No duplicates found
        conn
        |> put_status(:ok)
        |> json(%{
          status: "success",
          success: true,
          message: "This TID and MID are not configured till now.",
          duplicate_found: false
        })
    end
  end


  def check_duplicate_ysp_tid_mid(conn, params) do
    Logger.info("Checking for duplicate Ysp TID and MID with params: #{inspect(params)}")

    # Check for duplicate TID and MID in pos_terminals table
    ysp_tid = params["ysp_tid"]
    ysp_mid = params["ysp_mid"]
    shukria_terminal_id_param = params["shukria_terminal_id"]
    merchantRefId = params["merchantRefId"]


    # If params["pos_terminal_id"] is present and not empty, use it directly
    shukria_terminal_id =
      cond do
        params["pos_terminal_id"] && params["pos_terminal_id"] != "" ->
          params["pos_terminal_id"]
        shukria_terminal_id_param && shukria_terminal_id_param != "" ->
          case Repo.get(DaProductApp.ShukriaTerminal, shukria_terminal_id_param) do
            nil -> nil
            terminal -> terminal.shukria_terminal_id
          end
        true ->
          nil
      end

    # Check ysp_tid (ysp_tid) if provided
    ysp_tid_duplicate = if ysp_tid && ysp_tid != "" do
      ysp_tid_query = if shukria_terminal_id && shukria_terminal_id != "" do
        from(st in DaProductApp.ShukriaTerminal,
          where: st.ysp_tid == ^ysp_tid and st.shukria_terminal_id != ^shukria_terminal_id,
          limit: 1
        )
      else
        from(st in DaProductApp.ShukriaTerminal,
          where: st.ysp_tid == ^ysp_tid,
          limit: 1
        )
      end

      Repo.one(ysp_tid_query)
    else
      nil
    end




    # Check ysp_mid (ysp_mid) if provided
    ysp_mid_duplicate = if ysp_mid && ysp_mid != "" do
      ysp_mid_query = if shukria_terminal_id && shukria_terminal_id != "" do
        from(st in DaProductApp.ShukriaTerminal,
          where: st.ysp_mid == ^ysp_mid and st.shukria_terminal_id != ^shukria_terminal_id,
          join: b in Brand,
          on: b.id == type(st.shukria_mid, :integer),
          where: b.merchant_reference_id != ^merchantRefId,
          limit: 1,
          select: st
        )
      else
        from(st in DaProductApp.ShukriaTerminal,
          where: st.ysp_mid == ^ysp_mid,
          join: b in Brand,
          on: b.id == type(st.shukria_mid, :integer),
          where: b.merchant_reference_id != ^merchantRefId,
          limit: 1,
          select: st
        )
      end

      Repo.one(ysp_mid_query)
    else
      nil
    end

    # Determine response based on duplicates found
    cond do
      ysp_tid_duplicate && ysp_mid_duplicate ->
        # Both ysp_tid and ysp_mid are duplicates
        conn
        |> put_status(:ok)
        |> json(%{
          status: "success",
          success: true,
          message: "Both Ysp tid #{ysp_tid_duplicate.ysp_tid}  and Ysp Mid #{ysp_mid_duplicate.ysp_mid} are already configured",
          duplicate_found: true,
          duplicate_type: "both",
          duplicate_ysp_tid: %{
            id: ysp_tid_duplicate.id,
            pos_terminal_id: ysp_tid_duplicate.shukria_terminal_id,
            provider_id: ysp_tid_duplicate.provider_id,
            provider_tid: ysp_tid_duplicate.provider_tid,
            provider_mid: ysp_tid_duplicate.provider_mid,
            status: ysp_tid_duplicate.status,
            description: ysp_tid_duplicate.description,
            shukria_mid: ysp_tid_duplicate.shukria_mid,
            ysp_tid: ysp_tid_duplicate.ysp_tid,
            ysp_mid: ysp_tid_duplicate.ysp_mid
          },
          duplicate_ysp_mid: %{
            id: ysp_mid_duplicate.id,
            pos_terminal_id: ysp_mid_duplicate.shukria_terminal_id,
            provider_id: ysp_mid_duplicate.provider_id,
            provider_tid: ysp_mid_duplicate.provider_tid,
            provider_mid: ysp_mid_duplicate.provider_mid,
            status: ysp_mid_duplicate.status,
            description: ysp_mid_duplicate.description,
            shukria_mid: ysp_mid_duplicate.shukria_mid,
            ysp_tid: ysp_mid_duplicate.ysp_tid,
            ysp_mid: ysp_mid_duplicate.ysp_mid
          }
        })

        ysp_tid_duplicate ->
        # Only ysp_tid is duplicate
        conn
        |> put_status(:ok)
        |> json(%{
          status: "success",
          success: true,
          message: "This Ysp tid #{ysp_tid_duplicate.ysp_tid} is already configured",
          duplicate_found: true,
          duplicate_type: "ysp_tid",
          duplicate_ysp_tid: %{
            id: ysp_tid_duplicate.id,
            pos_terminal_id: ysp_tid_duplicate.shukria_terminal_id,
            provider_id: ysp_tid_duplicate.provider_id,
            provider_mid: ysp_tid_duplicate.provider_mid,
            terminal_id: ysp_tid_duplicate.status,
            merchant_id: ysp_tid_duplicate.description,
            shukria_mid: ysp_tid_duplicate.shukria_mid,
            ysp_tid: ysp_tid_duplicate.ysp_tid,
            ysp_mid: ysp_tid_duplicate.ysp_mid
          },
        })

        ysp_mid_duplicate ->
        # Only ysp_mid is duplicate
        conn
        |> put_status(:ok)
        |> json(%{
          status: "success",
          success: true,
           message: "This Ysp mid #{ysp_mid_duplicate.ysp_mid} is already configured",
          duplicate_found: true,
          duplicate_type: "ysp_mid",
          duplicate_ysp_mid: %{
            id: ysp_mid_duplicate.id,
            pos_terminal_id: ysp_mid_duplicate.shukria_terminal_id,
            provider_id: ysp_mid_duplicate.provider_id,
            provider_mid: ysp_mid_duplicate.provider_mid,
            terminal_id: ysp_mid_duplicate.status,
            merchant_id: ysp_mid_duplicate.description,
            shukria_mid: ysp_mid_duplicate.shukria_mid,
            ysp_tid: ysp_mid_duplicate.ysp_tid,
            ysp_mid: ysp_mid_duplicate.ysp_mid
          }
        })

        true ->
        # No duplicates found
        conn
        |> put_status(:ok)
        |> json(%{
          status: "success",
          success: true,
          message: "This Ysp TID and MID are not configured till now.",
          duplicate_found: false
        })
    end







  end

  def updateMerchantReference(conn, %{"old_merchant_reference_id" => old_ref, "new_merchant_reference_id" => new_ref} = params) do
  Logger.info("Updating merchant reference from #{old_ref} to #{new_ref}")

  # Validate both parameters are present and not empty
  if (is_nil(old_ref) || old_ref == "") || (is_nil(new_ref) || new_ref == "") do
    conn
    |> put_status(:bad_request)
    |> json(%{
      status: "error",
      success: false,
      message: "Both old_merchant_reference_id and new_merchant_reference_id are required"
    })
  else
    # Start a transaction to ensure all updates succeed or fail together
    Repo.transaction(fn ->
      # 1. Update pos_merchant table - merchantid column
      update_pos_merchant_query = """
      UPDATE pos_merchant
      SET merchantid = ?
      WHERE merchantid = ?
      """

      case Ecto.Adapters.SQL.query(Repo, update_pos_merchant_query, [new_ref, old_ref]) do
        {:ok, result} ->
          Logger.info("Updated pos_merchant: #{result.num_rows} rows affected")
        {:error, reason} ->
          Logger.error("Failed to update pos_merchant: #{inspect(reason)}")
          Repo.rollback("Failed to update pos_merchant")
      end

      # 2. Update groups table - code column
      update_groups_query = """
      UPDATE `groups`
      SET code = ?
      WHERE code = ?
      """

      case Ecto.Adapters.SQL.query(Repo, update_groups_query, [new_ref, old_ref]) do
        {:ok, result} ->
          Logger.info("Updated groups: #{result.num_rows} rows affected")
        {:error, reason} ->
          Logger.error("Failed to update groups: #{inspect(reason)}")
          Repo.rollback("Failed to update groups")
      end

      # 3. Update brands table - code and merchant_reference_id columns
      update_brands_query = """
      UPDATE brands
      SET code = ?, merchant_reference_id = ?
      WHERE merchant_reference_id = ?
      """

      case Ecto.Adapters.SQL.query(Repo, update_brands_query, [new_ref, new_ref, old_ref]) do
        {:ok, result} ->
          Logger.info("Updated brands: #{result.num_rows} rows affected")
        {:error, reason} ->
          Logger.error("Failed to update brands: #{inspect(reason)}")
          Repo.rollback("Failed to update brands")
      end

      # 4. Update pos_terminals table - terminalidmerchantid column
      update_pos_terminals_query = """
      UPDATE pos_terminals
      SET terminalidmerchantid = ?
      WHERE terminalidmerchantid = ?
      """

      case Ecto.Adapters.SQL.query(Repo, update_pos_terminals_query, [new_ref, old_ref]) do
        {:ok, result} ->
          Logger.info("Updated pos_terminals: #{result.num_rows} rows affected")
        {:error, reason} ->
          Logger.error("Failed to update pos_terminals: #{inspect(reason)}")
          Repo.rollback("Failed to update pos_terminals")
      end

      # Return success response
      %{
        status: "success",
        success: true,
        message: "Merchant reference updated successfully across all tables",
        old_merchant_reference_id: old_ref,
        new_merchant_reference_id: new_ref
      }
    end)
    |> case do
      {:ok, result} ->
        conn
        |> put_status(:ok)
        |> json(result)

      {:error, reason} ->
        conn
        |> put_status(:internal_server_error)
        |> json(%{
          status: "error",
          success: false,
          message: "Failed to update merchant reference: #{reason}",
          old_merchant_reference_id: old_ref,
          new_merchant_reference_id: new_ref
        })
    end
  end
end

def updateMerchantReference(conn, _params) do
  conn
  |> put_status(:bad_request)
  |> json(%{
    status: "error",
    success: false,
    message: "Missing required parameters: old_merchant_reference_id and new_merchant_reference_id are required"
  })
end


def check_duplicate_mid(conn, params) do
  Logger.info("Checking for duplicate MID in groups table: #{inspect(params)}")

  # Check for duplicate MID (code) in `groups` table
  smid = params["smid"]
  Logger.info("Checking if code exists in groups table for smid: #{smid}")

  # Check smid (code) if provided
  smid_duplicate = if smid && smid != "" do
    from(g in Group,
      where: g.code == ^smid,
      limit: 1
    )
    |> Repo.one()
  else
    nil
  end

  # Determine response based on duplicates found
  cond do
    smid_duplicate ->
      # Code exists in groups table
      conn
      |> put_status(:ok)
      |> json(%{
        status: "success",
        success: true,
        message: "Merchant reference code #{smid_duplicate.code} is already configured",
        duplicate_found: true,
        duplicate_type: "smid",
        duplicate_group: %{
          id: smid_duplicate.id,
          name: smid_duplicate.name,
          code: smid_duplicate.code,
          status: smid_duplicate.status,
          description: smid_duplicate.description,
          phone_number: smid_duplicate.phone_number,
          mcc_code: smid_duplicate.mcc_code
        }
      })

    true ->
      # No duplicates found
      conn
      |> put_status(:ok)
      |> json(%{
        status: "success",
        success: true,
        message: "This merchant reference code is not configured till now.",
        duplicate_found: false
      })
  end
end

defp to_uae_datetime(nil), do: ""

defp to_uae_datetime(%NaiveDateTime{} = naive_dt) do
  dt_utc = DateTime.from_naive!(naive_dt, "Etc/UTC")
  dt_uae = DateTime.add(dt_utc, 4 * 3600, :second)
  "#{dt_uae.year}-#{pad2(dt_uae.month)}-#{pad2(dt_uae.day)} #{pad2(dt_uae.hour)}:#{pad2(dt_uae.minute)}:#{pad2(dt_uae.second)}"
end

defp to_uae_datetime(%DateTime{} = dt_utc) do
  dt_uae = DateTime.add(dt_utc, 4 * 3600, :second)
  "#{dt_uae.year}-#{pad2(dt_uae.month)}-#{pad2(dt_uae.day)} #{pad2(dt_uae.hour)}:#{pad2(dt_uae.minute)}:#{pad2(dt_uae.second)}"
end

defp to_uae_datetime(datetime) when is_binary(datetime) do
  case NaiveDateTime.from_iso8601(datetime) do
    {:ok, naive_dt} -> to_uae_datetime(naive_dt)
    _ -> datetime
  end
end

defp pad2(n) when n < 10, do: "0#{n}"
defp pad2(n), do: "#{n}"



def checkDuplicateShukriaTerminal(conn, %{"shukria_terminal_id" => shukria_terminal_id, "provider_id" => provider_id}) do
  # Query for duplicate
  duplicate =
    DaProductApp.ShukriaTerminal
    |> where([st], st.shukria_terminal_id == ^to_string(shukria_terminal_id) and st.provider_id == ^to_string(provider_id))
    |> order_by([st], desc: st.id)
    |> limit(1)
    |> Repo.one()

  if duplicate do
    conn
    |> put_status(:ok)
    |> json(%{
      status: "success",
      success: true,
      message: "Shukria terminal is already configured with the given device and provider",
      duplicate_found: true,
      duplicate_terminal: %{
        id: duplicate.id,
        shukria_terminal_id: duplicate.shukria_terminal_id,
        provider_id: duplicate.provider_id,
        provider_tid: duplicate.provider_tid,
        provider_mid: duplicate.provider_mid,
        status: duplicate.status,
        description: duplicate.description,
        shukria_mid: duplicate.shukria_mid,
        ysp_tid: duplicate.ysp_tid,
        ysp_mid: duplicate.ysp_mid,
        inserted_at: duplicate.inserted_at,
        updated_at: duplicate.updated_at
      }
    })
  else
    conn
    |> put_status(:ok)
    |> json(%{
      status: "success",
      success: true,
      message: "No duplicate Shukria terminal found",
      duplicate_found: false
    })
  end
end


end