defmodule DaProductAppWeb.MerchantTransactionsController do
  use DaProductAppWeb, :controller
  require Logger
  alias DaProductApp.Transactions.Transaction
  alias DaProductApp.PosTerminals.PosTerminal
  alias DaProductApp.ShukriaTerminal
  alias DaProductApp.Stores.Store
  alias DaProductApp.DeviceMiddlelayer.PosTransaction
  alias DaProductApp.DeviceMiddlelayer.PosFailedTransaction
  alias DaProductApp.DeviceMiddlelayer.PosTempTransaction
  alias DaProductApp.DeviceMiddlelayer.PosTransactionReversal

  alias DaProductApp.Repo
  import Ecto.Query
  # Helper function to get device and store details for card transactions

  defp get_device_details(s_mid, s_tid) do
    # 1. Get device details directly from pos_terminals
    device_details =
      Repo.one(
        from(pt in "pos_terminals",
          where: pt.terminalidmerchantid == ^s_mid and pt.terminal_id == ^s_tid,
          select: %{
            id: pt.id,
            name: pt.name,
            serial_number: pt.serial_number,
            device_type: pt.device_type,
            terminal_id: pt.terminal_id,
            provider_id: 4,
            provider_name: "ysp",
            store_id: pt.store_id,
            status: pt.status
          }
        )
      )

    # 2. Get store details if device_details and store_id exist
    store_details =
      if device_details && device_details[:store_id] do
        Repo.one(
          from(s in "stores",
            where: s.id == ^device_details[:store_id],
            select: %{
              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
            }
          )
        )
      else
        %{}
      end

    %{
      device_details: device_details || %{},
      store_details: store_details || %{}
    }
  end
  # Helper functions
  # Admin date range handler - supports both date and datetime formats
  # Admin date range handler - supports both date and datetime formats
  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 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 get_admin_total_stores(conn, _params) do
    # Get total count of stores
    total_stores = Repo.one(from s in Store, select: count(s.id))

    conn
    |> put_status(:ok)
    |> json(%{
      status: "success",
      count: total_stores || 0,
      message: "Total stores count retrieved successfully",
      data: %{
        total_stores: total_stores || 0
      }
    })
  end

  def get_admin_total_devices(conn, _params) do
    # Get total count of devices from pos_terminals table
    total_devices = Repo.one(from pt in PosTerminal, select: count(pt.id))

    conn
    |> put_status(:ok)
    |> json(%{
      status: "success",
      count: total_devices || 0,
      message: "Total devices count retrieved successfully",
      data: %{
        total_devices: total_devices || 0
      }
    })
  end


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

    {start_date, end_date} = get_datetime_range(params)
    status = Map.get(params, "status", "success")

    # ========================================
    # PART 1: QR TRANSACTIONS (from transactions table)
    # ========================================
    qr_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: s in DaProductApp.Stores.Store,
        on: pt.store_id == s.id,
        where: t.inserted_at >= ^start_date and
               t.inserted_at <= ^end_date and
               t.status == ^status and
               t.provider_id == 1 and
               t.bank_user_id == ^params["merchantRefId"],

        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.bank_user_id,
          ysp_tid: t.ysp_tid,
          ysp_mid: t.merchant_id,
          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"
        }

    qr_transactions = DaProductApp.Repo.all(qr_query)

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

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

    qr_count = length(qr_transactions)

    conn
    |> put_status(:ok)
    |> json(%{
      status: "success",
      count: qr_count,
      qr_count: qr_count,
      card_count: 0,
      total_amount: total_amount,
      date_range: %{
        start_date: DateTime.to_date(start_date),
        end_date: DateTime.to_date(end_date)
      },
      message: "#{qr_count} successful transaction(s) retrieved (#{qr_count} QR, 0 Card)",
      data: %{transactions: qr_transactions}
    })
  end

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

    {start_date, end_date} = get_datetime_range(params)
    status = Map.get(params, "status", "success")

    # ========================================
    # PART 1: QR TRANSACTIONS (from transactions table)
    # ========================================
    qr_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: s in DaProductApp.Stores.Store,
        on: pt.store_id == s.id,
        where: t.inserted_at >= ^start_date and
               t.inserted_at <= ^end_date and
               t.status ==  ^status and
               t.provider_id == 3 and
               t.bank_user_id == ^params["merchantRefId"],

        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.bank_user_id,
          ysp_tid: t.ysp_tid,
          ysp_mid: t.merchant_id,
          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"
        }

    qr_transactions = DaProductApp.Repo.all(qr_query)

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

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

    qr_count = length(qr_transactions)

    conn
    |> put_status(:ok)
    |> json(%{
      status: "success",
      count: qr_count,
      qr_count: qr_count,
      card_count: 0,
      total_amount: total_amount,
      date_range: %{
        start_date: DateTime.to_date(start_date),
        end_date: DateTime.to_date(end_date)
      },
      message: "#{qr_count} successful transaction(s) retrieved (#{qr_count} QR, 0 Card)",
      data: %{transactions: qr_transactions}
    })
  end

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

    {start_date, end_date} = get_datetime_range(params)
    status = Map.get(params, "status", "success")

    # ========================================
    # PART 1: QR TRANSACTIONS (from transactions table)
    # ========================================
    qr_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: s in DaProductApp.Stores.Store,
        on: pt.store_id == s.id,
        where: t.inserted_at >= ^start_date and
               t.inserted_at <= ^end_date and
               t.status ==  ^status and
               t.pay_mode == "Static QR" and
               t.bank_user_id == ^params["merchantRefId"],

        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.bank_user_id,
          ysp_tid: t.ysp_tid,
          ysp_mid: t.merchant_id,
          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: "StaticQR"
        }

    qr_transactions = DaProductApp.Repo.all(qr_query)

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

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

    qr_count = length(qr_transactions)

    conn
    |> put_status(:ok)
    |> json(%{
      status: "success",
      count: qr_count,
      qr_count: qr_count,
      card_count: 0,
      total_amount: total_amount,
      date_range: %{
        start_date: DateTime.to_date(start_date),
        end_date: DateTime.to_date(end_date)
      },
      message: "#{qr_count} successful transaction(s) retrieved (#{qr_count} QR, 0 Card)",
      data: %{transactions: qr_transactions}
    })
  end

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

    {start_date, end_date} = get_datetime_range(params)
    status = Map.get(params, "status", "success")

    # Convert DateTime to NaiveDateTime for card transaction queries
    start_naive = DateTime.to_naive(start_date)
    end_naive = DateTime.to_naive(end_date)

    {table_module, source_table, tx_status} =
      case status do
        "success" -> {PosTransaction, "pos_transaction", "success"}
        "pending" -> {PosTempTransaction, "pos_temp_transaction", "pending"}
        "failed" -> {PosFailedTransaction, "pos_failed_transaction", "failed"}
        "reversal" -> {PosTransactionReversal, "pos_transaction_reversal", "reversal"}
        _ -> {PosTransaction, "pos_transaction", "success"}
      end

    pos_query =
      case status do
        "reversal" ->
          from t in table_module,
            left_join: pt in DaProductApp.PosTerminals.PosTerminal,
            on: pt.terminalidmerchantid == t.s_mid and pt.terminal_id == t.s_tid,
            left_join: s in DaProductApp.Stores.Store,
            on: pt.store_id == s.id,
            where: t.created_dateTime >= ^start_naive and
                   t.created_dateTime <= ^end_naive and
                   t.s_mid == ^params["merchantRefId"],
            select: %{
              transaction_id: t.id,
              transaction_amount: t.original_amount,
              device_id: pt.serial_number,
              processing_id: "",
              status: t.reversal_status,
              email: "",
              transaction_ref_number: t.original_reference_no,
              m_ref_num: t.original_reference_no,
              merchant_id: t.s_mid,
              ysp_tid: t.b_tid,
              ysp_mid: t.b_mid,
              bank_user_id: t.s_mid,
              payment_reference_id: t.original_reference_no,
              settlement_date_time: nil,
              is_settled: false,
              provider_name: "ysp",
              provider_id: "4",
              paymode: "card",
              created_at: t.created_dateTime,
              device_details: %{
                id: pt.id,
                name: pt.name,
                serial_number: pt.serial_number,
                device_type: pt.device_type,
                terminalid: 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: "Card",
              pan: t.original_encrypted_pan,
              masked_card_no: "",
              proc_code: t.original_proc_code,
              mcc: "",
              approval_code: "",
              response_code: t.reversal_response_code,
              entry_mode: t.original_entry_mode,
              metadata: t.metadata,
              source_table: ^source_table
            }
        _ ->
          from t in table_module,
            left_join: pt in DaProductApp.PosTerminals.PosTerminal,
            on: pt.terminalidmerchantid == t.s_mid and pt.terminal_id == t.s_tid,
            left_join: s in DaProductApp.Stores.Store,
            on: pt.store_id == s.id,
            where: t.created_dateTime >= ^start_naive and
                   t.created_dateTime <= ^end_naive and
                   t.s_mid == ^params["merchantRefId"],
            select: %{
              transaction_id: t.id,
              transaction_amount: t.total_amount,
              device_id: pt.serial_number,
              processing_id: "",
              status: ^tx_status,
              email: "",
              transaction_ref_number: t.reference_no,
              m_ref_num: t.reference_no,
              merchant_id: t.s_mid,
              ysp_tid: t.b_tid,
              ysp_mid: t.b_mid,
              bank_user_id: t.s_mid,
              payment_reference_id: t.reference_no,
              settlement_date_time: nil,
              is_settled: false,
              provider_name: "ysp",
              provider_id: "4",
              paymode: "card",
              created_at: t.created_dateTime,
              device_details: %{
                id: pt.id,
                name: pt.name,
                serial_number: pt.serial_number,
                device_type: pt.device_type,
                terminalid: 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: "Card",
              # Additional card-specific fields
              pan: t.encrypted_pan,
              masked_card_no: t.masked_card_no,
              proc_code: t.proc_code,
              mcc: t.mcc_code,
              approval_code: t.approval_code,
              response_code: t.response_code,
              entry_mode: t.entry_mode,
              metadata: t.metadata,
              source_table: ^source_table
           }
      end

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



    card_count = length(card_transactions)
    total_amount =
      Enum.reduce(card_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: card_count,
      qr_count: 0,
      card_count: card_count,
      total_amount: total_amount,
      date_range: %{
        start_date: DateTime.to_date(start_date),
        end_date: DateTime.to_date(end_date)
      },
      message: "#{card_count} card transaction(s) retrieved (0 QR, #{card_count} Card)",
      data: %{transactions: card_transactions}
    })
  end


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

    {start_date, end_date} = get_datetime_range(params)
    status = Map.get(params, "status", "success")

    # ========================================
    # PART 1: QR TRANSACTIONS (from transactions table)
    # ========================================
    qr_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: s in DaProductApp.Stores.Store,
        on: pt.store_id == s.id,
        where: t.inserted_at >= ^start_date and
               t.inserted_at <= ^end_date and
               t.status == ^status and
               t.bank_user_id == ^params["merchantRefId"],
        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.bank_user_id,
          ysp_tid: t.ysp_tid,
          ysp_mid: t.merchant_id,
          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"
        }

    qr_transactions = DaProductApp.Repo.all(qr_query)

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

    # ========================================
    # PART 2: CARD TRANSACTIONS (from pos_transaction tables)
    # ========================================

    # Convert DateTime to NaiveDateTime for card transaction queries
    start_naive = DateTime.to_naive(start_date)
    end_naive = DateTime.to_naive(end_date)

    # Determine which table to query based on status
    {table_module, source_table, card_status} =
      case status do
        "success" -> {PosTransaction, "pos_transaction", "success"}
        "pending" -> {PosTempTransaction, "pos_temp_transaction", "pending"}
        "failed" -> {PosFailedTransaction, "pos_failed_transaction", "failed"}
        "reversal" -> {PosTransactionReversal, "pos_transaction_reversal", "reversal"}
        _ -> {PosTransaction, "pos_transaction", "success"}
      end

    # Build query based on status
    pos_query =
      case status do
        "reversal" ->
          from t in table_module,
            left_join: pt in DaProductApp.PosTerminals.PosTerminal,
            on: pt.terminalidmerchantid == t.s_mid and pt.terminal_id == t.s_tid,
            left_join: s in DaProductApp.Stores.Store,
            on: pt.store_id == s.id,
            where: t.created_dateTime >= ^start_naive and
                   t.created_dateTime <= ^end_naive and
                   t.s_mid == ^params["merchantRefId"],
            select: %{
              transaction_id: t.id,
              transaction_amount: t.original_amount,
              device_id: pt.serial_number,
              processing_id: "",
              status: t.reversal_status,
              email: "",
              transaction_ref_number: t.original_reference_no,
              m_ref_num: t.original_reference_no,
              merchant_id: t.s_mid,
              ysp_tid: t.b_tid,
              ysp_mid: t.b_mid,
              bank_user_id: t.s_mid,
              payment_reference_id: t.original_reference_no,
              settlement_date_time: nil,
              is_settled: false,
              provider_name: "ysp",
              provider_id: "4",
              paymode: "card",
              created_at: t.created_dateTime,
              device_details: %{
                id: pt.id,
                name: pt.name,
                serial_number: pt.serial_number,
                device_type: pt.device_type,
                terminalid: 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: "Card",
              pan: t.original_encrypted_pan,
              masked_card_no: "",
              proc_code: t.original_proc_code,
              mcc: "",
              approval_code: "",
              response_code: t.reversal_response_code,
              entry_mode: t.original_entry_mode,
              metadata: t.metadata,
              source_table: ^source_table
            }
        _ ->
          from t in table_module,
            left_join: pt in DaProductApp.PosTerminals.PosTerminal,
            on: pt.terminalidmerchantid == t.s_mid and pt.terminal_id == t.s_tid,
            left_join: s in DaProductApp.Stores.Store,
            on: pt.store_id == s.id,
            where: t.created_dateTime >= ^start_naive and
                   t.created_dateTime <= ^end_naive and
                   t.s_mid == ^params["merchantRefId"],
            select: %{
              transaction_id: t.id,
              transaction_amount: t.total_amount,
              device_id: pt.serial_number,
              processing_id: "",
              status: ^card_status,
              email: "",
              transaction_ref_number: t.reference_no,
              m_ref_num: t.reference_no,
              merchant_id: t.s_mid,
              ysp_tid: t.b_tid,
              ysp_mid: t.b_mid,
              bank_user_id: t.s_mid,
              payment_reference_id: t.reference_no,
              settlement_date_time: nil,
              is_settled: false,
              provider_name: "ysp",
              provider_id: "4",
              paymode: "card",
              created_at: t.created_dateTime,
              device_details: %{
                id: pt.id,
                name: pt.name,
                serial_number: pt.serial_number,
                device_type: pt.device_type,
                terminalid: 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: "Card",
              # Additional card-specific fields
              pan: t.encrypted_pan,
              masked_card_no: t.masked_card_no,
              proc_code: t.proc_code,
              mcc: t.mcc_code,
              approval_code: t.approval_code,
              response_code: t.response_code,
              entry_mode: t.entry_mode,
              metadata: t.metadata,
              source_table: ^source_table
            }
      end

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



    # ========================================
    # PART 3: COMBINE AND CALCULATE TOTALS
    # ========================================

    # Combine both QR and card transactions
    all_transactions = qr_transactions ++ card_transactions

    # Calculate total amount
    total_amount =
      Enum.reduce(all_transactions, Decimal.new("0"), fn tx, acc ->
        Decimal.add(acc, tx.transaction_amount || Decimal.new("0"))
      end)

    # Separate counts
    qr_count = length(qr_transactions)
    card_count = length(card_transactions)
    total_count = length(all_transactions)

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

  def get_reversal_card_transaction_by_id(conn, %{"id" => id} = params) do
    # Get status from params if provided
    status = params["status"]

    # Determine which table to query based on status
    row = Repo.get(PosTransactionReversal, id)

    case row do
      nil ->
        json(conn, %{
          "success" => false,
          "error" => "Transaction not found",
          "data" => %{}
        })

      row ->
        device_store_details = get_device_details(row.s_mid, row.s_tid)
        device_details_map = Map.get(device_store_details, :device_details, %{})

        device_id = Map.get(device_details_map, :serial_number, "")
        pos_terminal_id = Map.get(device_details_map, :id, "")

        formatted = %{

          "id" => row.id || "",
          "pan" => row.original_encrypted_pan || "",
          "proc_code" => row.original_proc_code || "",
          "transaction_amount" => row.original_amount || "",
          #"stan" => row.stan || "",
          "date_time" => to_uae_datetime(row.created_dateTime) || "",
          #"settlement_date" => row.settlement_date || "",
          "mcc" => "",
          "terminal_id" => row.s_tid || "",
          "merchant_id" => row.s_mid || "",
          "m_ref_num" => row.original_reference_no || "",
          "auth_code" => "",
          "response_code" => row.reversal_response_code || "",
          #"upi_url" => row.upi_url || "",
          #"upi_info" => row.upi_info || "",
          "meta_data" => row.metadata || "",
          "name" => "", # merchant_name if available
          "created_at" => to_uae_datetime(row.created_dateTime) || "",
          "payment_status" => row.reversal_status,
          "email" => "", # email if available
          "processing_id" => "", # processing_id if available
          "transaction_ref_number" => row.original_reference_no || "",
          "payment_reference_id" => row.original_reference_no || "",
          #"settlement_date_time" => row.settlement_date || "",
          #"is_settled" => is_settled,
          "payload" => [],
          "updated_at" => "", # updated_at if available
          "provider_id" => "4", # provider_id if available
          "transaction_id" => "", # transaction_id if available
          "user_id" => "", # user_id if available
          "additional_data" => [],
          "location_id" => "",
          "mobile_no" => "",
          "patient_name" => "",
          "pay_mode" => "",
          "transaction_location" => "",
          "uhid" => "",
          "device_id" => device_id,
          "pos_terminal_id" => pos_terminal_id,

        }

        json(conn, %{
          "success" => true,
          "data" => [formatted]
        })
    end
  end

  defp get_pos_terminals_details(pos_merchant_id, terminalid) do
    mapping =
      Repo.one(
        from(pt in "pos_terminal",
          where: pt.pos_merchant_id == ^pos_merchant_id and pt.terminalid == ^terminalid,
          select: %{serial_number: pt.serial_number}
        )
      )

    case mapping do
      nil -> {"", "", nil}
      %{serial_number: serial_number} when not is_nil(serial_number) ->
        case Repo.one(
          from(pt in "pos_terminals",
            where: pt.serial_number == ^serial_number,
            select: %{pos_terminal_id: pt.id, terminal_id: pt.terminal_id, serial_number: pt.serial_number}
          )
        ) do
          nil -> {"", "", nil}
          %{pos_terminal_id: pos_terminal_id, terminal_id: terminal_id, serial_number: serial_number} ->
            {serial_number || "", terminal_id || "", pos_terminal_id || nil}
        end
      _ -> {"", "", nil}
    end
  end
end