defmodule DaProductAppWeb.PosTransactionController do
  use DaProductAppWeb, :controller
  require Logger
  alias DaProductApp.PosTransactions.PosTransaction
  alias DaProductApp.DeviceMiddlelayer.PosFailedTransaction
  alias DaProductApp.DeviceMiddlelayer.PosTempTransaction
  alias DaProductApp.Repo
  import Ecto.Query

  alias DaProductApp.Stores.Store
  alias DaProductApp.PosTerminals.PosTerminal
  alias DaProductApp.Transactions.Transaction
  alias DaProductApp.DeviceMiddlelayer.PosTransactionReversal


  def get_all_card_transaction(conn, params) do
    Logger.debug("Params: #{inspect(params)}")

    # Extract filters from params
    merchant_id = params["merchant_id"]
    start_date = params["start_date"]
    end_date = params["end_date"]

    # Build base query for pos_transaction with explicit select
    pos_query =
      from t in PosTransaction,
        select: %{
          id: t.id,
          encrypted_pan: t.encrypted_pan,
          proc_code: t.proc_code,
          total_amount: t.total_amount,
          created_dateTime: t.created_dateTime,
          mcc_code: t.mcc_code,
          s_tid: t.s_tid,
          s_mid: t.s_mid,
          reference_no: t.reference_no,
          approval_code: t.approval_code,
          response_code: t.response_code,
          metadata: t.metadata,
          source_table: type(fragment("'pos_transaction'"), :string)
        }

    # Build base query for failed_transaction with explicit select
    failed_query =
      from t in PosFailedTransaction,
        select: %{
          id: t.id,
          encrypted_pan: t.encrypted_pan,
          proc_code: t.proc_code,
          total_amount: t.total_amount,
          created_dateTime: t.created_dateTime,
          mcc_code: t.mcc_code,
          s_tid: t.s_tid,
          s_mid: t.s_mid,
          reference_no: t.reference_no,
          approval_code: t.approval_code,
          response_code: t.response_code,
          metadata: t.metadata,
          source_table: type(fragment("'failed_transaction'"), :string)
        }

    # Build base query for temp_transaction with explicit select
    temp_query =
      from t in PosTempTransaction,
        select: %{
          id: t.id,
          encrypted_pan: t.encrypted_pan,
          proc_code: t.proc_code,
          total_amount: t.total_amount,
          created_dateTime: t.created_dateTime,
          mcc_code: t.mcc_code,
          s_tid: t.s_tid,
          s_mid: t.s_mid,
          reference_no: t.reference_no,
          approval_code: t.approval_code,
          response_code: t.response_code,
          metadata: t.metadata,
          source_table: type(fragment("'temp_transaction'"), :string)
        }

    # Apply filters to each query
    pos_query = apply_filters(pos_query, merchant_id, start_date, end_date)
    failed_query = apply_filters(failed_query, merchant_id, start_date, end_date)
    temp_query = apply_filters(temp_query, merchant_id, start_date, end_date)

    # Combine queries using UNION ALL and sort at database level
    combined_query =
      pos_query
      |> union_all(^failed_query)
      |> union_all(^temp_query)
      |> subquery()
      |> order_by([t], desc: t.created_dateTime)

    # Execute single query
    all_transactions = Repo.all(combined_query)

    formatted =
      Enum.map(all_transactions, fn row ->
        transaction_amount =
          case row.total_amount do
            nil -> ""
            amt when is_binary(amt) ->
              amt = String.trim_leading(amt, "0")
              if amt == "", do: "", else: Integer.parse(amt) |> elem(0) |> Kernel./(100) |> to_string()
            _ -> to_string(row.total_amount)
          end

        # Determine status based on source table
        status =
          case row.source_table do
            "pos_transaction" -> "success"
            "failed_transaction" -> "failed"
            "temp_transaction" -> "pending"
            _ -> "unknown"
          end

        %{
          "transaction_id" => row.id || "",
          "pan" => row.encrypted_pan || "",
          "proc_code" => row.proc_code || "",
          "transaction_amount" => transaction_amount,
          "date_time" => to_uae_datetime(row.created_dateTime) || "",
          "settlement_date" => "",
          "mcc" => row.mcc_code || "",
          "terminal_id" => row.s_tid || "",
          "merchant_id" => row.s_mid || "",
          "m_ref_num" => row.reference_no || "",
          "auth_code" => row.approval_code || "",
          "response_code" => row.response_code || "",
          "meta_data" => row.metadata || "",
          "merchant_name" => "",
          "created_at" => to_uae_datetime(row.created_dateTime) || "",
          "status" => status,
          "email" => "",
          "processing_id" => "",
          "transaction_ref_number" => row.reference_no || "",
          "payment_reference_id" => row.reference_no || ""
        }
      end)

    response = %{
      "success" => true,
      "data" => formatted,
      "count" => length(formatted)
    }

    json(conn, response)
  end

  def get_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, source_table} =
      case status do
        "success" ->
          {Repo.get(PosTransaction, id), "pos_transaction"}
        "pending" ->
          {Repo.get(PosTempTransaction, id), "temp_transaction"}
        status when status in ["fail", "failed"] ->
          {Repo.get(PosFailedTransaction, id), "failed_transaction"}
        _ ->
          # If no status provided, search all three tables
          cond do
            row = Repo.get(PosTransaction, id) -> {row, "pos_transaction"}
            row = Repo.get(PosTempTransaction, id) -> {row, "temp_transaction"}
            row = Repo.get(PosFailedTransaction, id) -> {row, "failed_transaction"}
            true -> {nil, nil}
          end
      end

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

      row ->
        transaction_amount =
          case row.total_amount do
            nil -> ""
            amt when is_binary(amt) ->
              amt = String.trim_leading(amt, "0")
              if amt == "", do: "", else: Integer.parse(amt) |> elem(0) |> Kernel./(100) |> to_string()
            _ -> to_string(row.total_amount)
          end

        # Determine status based on source table
        status =
          case source_table do
            "pos_transaction" -> "success"
            "failed_transaction" -> "failed"
            "temp_transaction" -> "pending"
            _ -> "unknown"
          end

        # Query pos_merchant table using s_mid from transaction
        {device_id, terminal_id, pos_terminal_id} =
          if row.s_mid do
            # Query pos_merchant table using exact match for merchantid
            pos_merchant =
              Repo.one(
                from(pm in "pos_merchant",
                  where: pm.merchantid == ^row.s_mid,
                  select: %{
                    id: pm.id,
                    merchant_id: pm.merchantid
                  },
                  limit: 1
                )
              )

            device_details =
              if pos_merchant do
                get_pos_terminals_details(pos_merchant.id, row.s_tid)
              else
                {"", "", nil}
              end
          else
            {"", "", nil}
          end

        formatted = %{

          "id" => row.id || "",
          "pan" => row.encrypted_pan || "",
          "proc_code" => row.proc_code || "",
          "transaction_amount" => transaction_amount,
          #"stan" => row.stan || "",
          "date_time" => to_uae_datetime(row.created_dateTime) || "",
          #"settlement_date" => row.settlement_date || "",
          "mcc" => row.mcc_code || "",
          "terminal_id" => row.s_tid || "",
          "merchant_id" => row.s_mid || "",
          "m_ref_num" => row.reference_no || "",
          "auth_code" => row.approval_code || "",
          "response_code" => row.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" => status,
          "email" => "", # email if available
          "processing_id" => "", # processing_id if available
          "transaction_ref_number" => row.reference_no || "",
          "payment_reference_id" => row.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

  def get_device_detail_by_serial_terminal_id(conn, %{"device_id" => device_id, "terminal_id" => terminal_id}) do
    Logger.info("Fetching device details for device_id: #{device_id}")
    Logger.info("Fetching device details for terminal_id: #{terminal_id}")

    # Query pos_terminal table directly
    result =
      Repo.one(
        from(pt in "pos_terminal",
          where: pt.serial_number == ^device_id and pt.terminalid == ^terminal_id,
          select: %{
            id: pt.id,
            terminalid: pt.terminalid,
            serial_number: pt.serial_number,
            pos_merchant_id: pt.pos_merchant_id
          },
          limit: 1
        )
      )

    if result do
      conn
      |> put_status(:ok)
      |> json(%{
        status: "success",
        message: "Device details retrieved successfully",
        data: %{
          device: %{
            id: result.id,
            terminal_id: result.terminalid,
            serial_number: result.serial_number,
            device_id: result.serial_number,
            pos_merchant_id: result.pos_merchant_id
          }
        }
      })
    else
      conn
      |> put_status(:not_found)
      |> json(%{
        "success" => false,
        "error" => "POS terminal not found",
        "data" => %{}
      })
    end
  end

  defp parse_date(date) do
    case NaiveDateTime.from_iso8601("#{date} 00:00:00") do
      {:ok, ndt} -> ndt
      _ -> NaiveDateTime.utc_now()
    end
  end

  def get_device_by_device_id_provider_id(conn, %{"device_id" => device_id, "provider_id" => provider_id} = _params) do
    import Ecto.Query
    alias DaProductApp.PosTerminals.PosTerminal
    alias DaProductApp.ShukriaTerminal
    alias DaProductApp.Repo

    Logger.info("Fetching device details for device_id-------: #{device_id}")
    Logger.info("Fetching device details for provider_id: #{provider_id}")

    result =
      Repo.one(
        from pt in PosTerminal,
          join: st in ShukriaTerminal,
          on: st.shukria_terminal_id == pt.id and st.provider_id == ^to_string(provider_id),
          where: pt.serial_number == ^device_id,
          select: %{
            device_id: pt.id,
            name: pt.name,
            serial_number: pt.serial_number,
            device_type: pt.device_type,
            terminal_id: pt.terminalid,
            provider_id: st.provider_id,
            status: pt.status,
            store_id: pt.store_id

          }
      )

    if result do
      conn
        |> put_status(:ok)
        |> json(%{
          status: "success",
          message: "Device details retrieved successfully",
          data: %{
            device: result
          }
        })
    else
      conn
      |> put_status(:not_found)
      |> json(%{
        "success" => false,
        "error" => "POS terminal or Shukria terminal not found",
        "data" => %{}
      })
    end
  end

  def get_device_by_device_id_provider_id(conn, _params) do
    conn
    |> put_status(:bad_request)
    |> json(%{
      status: "error",
      message: "device_id is required",
      data: %{
        device: nil
      }
    })
  end


  def get_card_device_by_device_id_provider_id(conn, %{"device_id" => device_id, "provider_id" => provider_id} = _params) do
    import Ecto.Query
    alias DaProductApp.PosTerminals.PosTerminal
    alias DaProductApp.ShukriaTerminal
    alias DaProductApp.Repo

    Logger.info("Fetching device details for device_id-------: #{device_id}")
    Logger.info("Fetching device details for provider_id: #{provider_id}")

    result =
      Repo.one(
        from pt in PosTerminal,
          #join: st in ShukriaTerminal,
          #on: st.shukria_terminal_id == pt.id and st.provider_id == ^to_string(provider_id),
          where: pt.serial_number == ^device_id,
          select: %{
            device_id: pt.id,
            name: pt.name,
            serial_number: pt.serial_number,
            device_type: pt.device_type,
            terminal_id: pt.terminalid,
            provider_id: 4,
            status: pt.status,
            store_id: pt.store_id

          }
      )

    if result do
      conn
        |> put_status(:ok)
        |> json(%{
          status: "success",
          message: "Device details retrieved successfully",
          data: %{
            device: result
          }
        })
    else
      conn
      |> put_status(:not_found)
      |> json(%{
        "success" => false,
        "error" => "POS terminal or Shukria terminal not found",
        "data" => %{}
      })
    end
  end

  def get_card_device_by_device_id_provider_id(conn, _params) do
    conn
    |> put_status(:bad_request)
    |> json(%{
      status: "error",
      message: "device_id is required",
      data: %{
        device: nil
      }
    })
  end

  # Helper function to apply filters to any query
  defp apply_filters(query, merchant_id, start_date, end_date) do
    # Add merchant_id filter if present
    query =
      if merchant_id do
        from t in query, where: t.s_mid == ^merchant_id
      else
        query
      end

    # Add date range filter if present (comparing at date level)
    query =
      cond do
        start_date && end_date ->

          # Convert UAE dates to UTC datetimes
          # UAE 00:00:00 = UTC previous day 20:00:00
          # UAE 23:59:59 = UTC same day 19:59:59
          start_datetime = parse_uae_date_to_utc(start_date, :start)
          end_datetime = parse_uae_date_to_utc(end_date, :end)

          from t in query,
            where: t.created_dateTime >= ^start_datetime and
                  t.created_dateTime <= ^end_datetime
        start_date ->
          start_datetime = parse_uae_date_to_utc(start_date, :start)
          from t in query, where: t.created_dateTime >= ^start_datetime
        end_date ->
          end_datetime = parse_uae_date_to_utc(end_date, :end)
          from t in query, where: t.created_dateTime <= ^end_datetime
        true ->
          query
      end

    query
  end


  # Helper to convert UAE date string to UTC NaiveDateTime
  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]      # Start of day in UAE (00:00:00)
      :end -> ~T[23:59:59]        # End of day in UAE (23:59:59)
    end

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

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

  # Helper function to apply filters with dates already in UTC (no conversion needed)
  defp apply_date_time_filter(query, merchant_id, start_date, end_date) do
    # Add merchant_id filter if present
    query =
      if merchant_id do
        from t in query, where: t.s_mid == ^merchant_id
      else
        query
      end

    # Add date range filter - dates are already in UTC, just parse them
    query =
      cond do
        start_date && end_date ->
          # Parse dates without timezone conversion
          start_datetime = start_date
          end_datetime = end_date

          from t in query,
            where: t.created_dateTime >= ^start_datetime and
                  t.created_dateTime <= ^end_datetime
        start_date ->
          start_datetime = start_date
          from t in query, where: t.created_dateTime >= ^start_datetime
        end_date ->
          end_datetime = end_date
          from t in query, where: t.created_dateTime <= ^end_datetime
        true ->
          query
      end

    query
  end


  def get_all_merchant_card_transaction(conn, params) do
    Logger.debug("Params: #{inspect(params)}")

    # Extract filters from params
    start_date = params["start_date"]
    end_date = params["end_date"]
    Logger.info("Computed date range - Start: #{start_date}, End: #{end_date}")

    # Build base query for pos_transaction with explicit select
    pos_query =
      from t in PosTransaction,
        select: %{
          id: t.id,
          encrypted_pan: t.encrypted_pan,
          proc_code: t.proc_code,
          total_amount: t.total_amount,
          created_dateTime: t.created_dateTime,
          mcc_code: t.mcc_code,
          s_tid: t.s_tid,
          s_mid: t.s_mid,
          reference_no: t.reference_no,
          approval_code: t.approval_code,
          response_code: t.response_code,
          metadata: t.metadata,
          masked_card_no: t.masked_card_no,
          ysp_tid: t.b_tid,
          ysp_mid: t.b_mid,
          entry_mode: t.entry_mode,
          status: "success",
          #order_number: t.order_number,
          source_table: type(fragment("'pos_transaction'"), :string)
        }

    # Build base query for failed_transaction with explicit select
    failed_query =
      from t in PosFailedTransaction,
        select: %{
          id: t.id,
          encrypted_pan: t.encrypted_pan,
          proc_code: t.proc_code,
          total_amount: t.total_amount,
          created_dateTime: t.created_dateTime,
          mcc_code: t.mcc_code,
          s_tid: t.s_tid,
          s_mid: t.s_mid,
          reference_no: t.reference_no,
          approval_code: t.approval_code,
          response_code: t.response_code,
          metadata: t.metadata,
          masked_card_no: t.masked_card_no,
          ysp_tid: t.b_tid,
          ysp_mid: t.b_mid,
          entry_mode: t.entry_mode,
          status: "failed",
          #order_number: t.order_number,
          source_table: type(fragment("'failed_transaction'"), :string)
        }

    # Build base query for temp_transaction with explicit select
    temp_query =
      from t in PosTempTransaction,
        select: %{
          id: t.id,
          encrypted_pan: t.encrypted_pan,
          proc_code: t.proc_code,
          total_amount: t.total_amount,
          created_dateTime: t.created_dateTime,
          mcc_code: t.mcc_code,
          s_tid: t.s_tid,
          s_mid: t.s_mid,
          reference_no: t.reference_no,
          approval_code: t.approval_code,
          response_code: t.response_code,
          metadata: t.metadata,
          masked_card_no: t.masked_card_no,
          ysp_tid: t.b_tid,
          ysp_mid: t.b_mid,
          entry_mode: t.entry_mode,
          status: "pending",
          #order_number: t.order_number,
          source_table: type(fragment("'temp_transaction'"), :string)
        }

      reversal_query =
      from t in PosTransactionReversal,
        select: %{
          id: t.id,
          encrypted_pan: t.original_encrypted_pan,
          proc_code: t.original_proc_code,
          total_amount: t.original_amount,
          created_dateTime: t.created_dateTime,
          mcc_code: "",
          s_tid: t.s_tid,
          s_mid: t.s_mid,
          reference_no: t.original_reference_no,
          approval_code: "",
          response_code: t.reversal_response_code,
          metadata: t.metadata,
          masked_card_no: "",
          ysp_tid: t.b_tid,
          ysp_mid: t.b_mid,
          entry_mode: t.original_entry_mode,
          status: t.reversal_status,
          #order_number: t.order_number,
          source_table: type(fragment("'reversal_transaction'"), :string)
        }

    # Apply filters to each query - use apply_date_time_filter since dates from admin API are already in UTC
    pos_query = apply_date_time_filter(pos_query, nil, start_date, end_date)
    failed_query = apply_date_time_filter(failed_query, nil, start_date, end_date)
    temp_query = apply_date_time_filter(temp_query, nil, start_date, end_date)
    reversal_query = apply_date_time_filter(reversal_query, nil, start_date, end_date)

    # Combine queries using UNION ALL and sort at database level
    combined_query =
      pos_query
      |> union_all(^failed_query)
      |> union_all(^temp_query)
      |> union_all(^reversal_query)
      |> subquery()
      |> order_by([t], desc: t.created_dateTime)

    # Execute single query
    all_transactions = Repo.all(combined_query)

    formatted =
      Enum.map(all_transactions, fn row ->
        transaction_amount =
          case row.total_amount do
            nil -> ""
            amt when is_binary(amt) ->
              amt = String.trim_leading(amt, "0")
              if amt == "", do: "", else: Integer.parse(amt) |> elem(0) |> Kernel./(100) |> to_string()
            _ -> to_string(row.total_amount)
          end

        # Determine status based on source table
        status =
          case row.source_table do
            "pos_transaction" -> "success"
            "failed_transaction" -> "failed"
            "temp_transaction" -> "pending"
            "reversal_transaction" -> row.status
            _ -> "unknown"
          end
          device_store_details = get_device_details(row.s_mid, row.s_tid)
          #device_and_store_details = device_store_details || %{}
          #device_details = device_and_store_details.device_details || %{}
          #store_details = device_and_store_details.store_details || %{}
          device_and_store_details = device_store_details || %{}
          device_and_store_details = if is_map(device_and_store_details), do: device_and_store_details, else: %{}
          device_details = Map.get(device_and_store_details, :device_details, %{})
          store_details = Map.get(device_and_store_details, :store_details, %{})

        %{
          "transaction_id" => row.id || "",
          "pan" => row.encrypted_pan || "",
          "proc_code" => row.proc_code || "",
          "transaction_amount" => transaction_amount,
          "date_time" => row.created_dateTime || "",
          #"device_id" => Map.get(device_details, :serial_number, ""),
          #"provider_id" => Map.get(device_details, :provider_id, ""),
          #"provider_name" => Map.get(device_details, :provider_name, ""),
          "device_id" => (is_map(device_details) && Map.get(device_details, :serial_number, "")) || "",
          "provider_id" => (is_map(device_details) && Map.get(device_details, :provider_id, "")) || "",
          "provider_name" => (is_map(device_details) && Map.get(device_details, :provider_name, "")) || "",
          "settlement_date" => "",
          "mcc" => row.mcc_code || "",
          "terminal_id" => row.s_tid || "",
          "merchant_id" => row.s_mid || "",
          "m_ref_num" => row.reference_no || "",
          "bank_user_id" => row.s_mid || "",
          "approval_code" => row.approval_code || "",
          "response_code" => row.response_code || "",
          "meta_data" => row.metadata || "",
          "merchant_name" => "",
          "created_at" => to_uae_datetime(row.created_dateTime) || "",
          "status" => status,
          "email" => "",
          "processing_id" => "",
          "masked_card_no" => row.masked_card_no || "",
          "ysp_tid" => row.ysp_tid || "",
          "ysp_mid" => row.ysp_mid || "",
          "transaction_ref_number" => row.reference_no || "",
          "payment_reference_id" => row.reference_no || "",
          "device_details" => device_details,
          "transaction_type" => "Card",
          "entry_mode" => row.entry_mode || "",
          "store_details" => store_details
        }
      end)

      # Calculate total_amount from formatted
      total_amount =
        formatted
        |> Enum.reduce(0.0, fn row, acc ->
          case Float.parse(row["transaction_amount"]) do
            {val, _} -> acc + val
            :error -> acc
          end
        end)



    response = %{
      "count" => length(formatted),
      "data" => formatted,
      "message" => "#{length(formatted)} transaction(s) retrieved successfully",
      "status" => "success",
      "total_amount" => total_amount,
      "success" => true,
      "date_range" => %{
        "start_date" => start_date || "",
        "end_date" => end_date || ""
      }
    }

    json(conn, response)
  end

  defp get_device_details(s_mid, s_tid) do
    # 1. Get pos_merchant by merchantid
    pos_merchant =
      Repo.one(
        from(pm in "pos_merchant",
          where: pm.merchantid == ^s_mid,
          select: %{id: pm.id, merchant_id: pm.merchantid, merchant_name: pm.merchant_name}
        )
      )

    if pos_merchant do
      # 2. Get serial_number from pos_terminal (singular)
      mapping =
        Repo.one(
          from(pt in "pos_terminal",
            where: pt.pos_merchant_id == ^pos_merchant.id and pt.terminalid == ^s_tid,
            select: %{serial_number: pt.serial_number}
          )
        )

      case mapping do
        nil -> nil
        %{serial_number: serial_number} when not is_nil(serial_number) ->
          # 3. Get device details from pos_terminals (plural)
          device_details =
            Repo.one(
              from(pt in "pos_terminals",
                where: pt.serial_number == ^serial_number,
                select: %{
                  id: pt.id,
                  name: pt.name,
                  serial_number: pt.serial_number,
                  device_type: pt.device_type,
                  terminal_id: pt.terminal_id,
                  store_id: pt.store_id,
                  provider_id: 4, # or pt.provider_id if you want dynamic
                  provider_name: "YSP",
                  status: pt.status
                }
              )
            )

          # 4. Get store details if device_details and store_id exist
          store_details =
            case device_details do
              nil -> nil
              %{store_id: store_id} when not is_nil(store_id) ->
                Repo.one(
                  from(s in "stores",
                    where: s.id == ^store_id,
                    select: %{
                      store_id: s.id,
                      store_code: s.code,
                      store_name: s.name
                    }
                  )
                )
              _ -> %{}
            end

          # Return both device_details and store_details
          %{
            device_details: device_details,
            store_details: store_details
          }
        _ -> %{device_details: %{}, store_details: %{}}
      end
    else
      nil
    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


  def get_merchant_card_transactions(conn, params) do
  Logger.info("Fetching total card transactions with params: #{inspect(params)}")
  merchant_id = params["merchantRefId"]
  {start_date, end_date} = get_card_date_range(params)
  Logger.info("Computed date range before - Start: #{start_date}, End: #{end_date}")
  start_date = DateTime.to_date(start_date) |> Date.to_iso8601()
  end_date = DateTime.to_date(end_date) |> Date.to_iso8601()
  Logger.info("Computed date range after - Start: #{start_date}, End: #{end_date}")
  Logger.info("Merchant ID: #{merchant_id}")

  pos_query =
    from t in PosTransaction,
      select: %{
        id: t.id,
        encrypted_pan: t.encrypted_pan,
        proc_code: t.proc_code,
        total_amount: t.total_amount,
        created_dateTime: t.created_dateTime,
        mcc_code: t.mcc_code,
        s_tid: t.s_tid,
        s_mid: t.s_mid,
        reference_no: t.reference_no,
        approval_code: t.approval_code,
        response_code: t.response_code,
        metadata: t.metadata,
        masked_card_no: t.masked_card_no,
        ysp_tid: t.b_tid,
        ysp_mid: t.b_mid,
        entry_mode: t.entry_mode,
        source_table: type(fragment("'pos_transaction'"), :string)
      }

  failed_query =
    from t in PosFailedTransaction,
      select: %{
        id: t.id,
        encrypted_pan: t.encrypted_pan,
        proc_code: t.proc_code,
        total_amount: t.total_amount,
        created_dateTime: t.created_dateTime,
        mcc_code: t.mcc_code,
        s_tid: t.s_tid,
        s_mid: t.s_mid,
        reference_no: t.reference_no,
        approval_code: t.approval_code,
        response_code: t.response_code,
        metadata: t.metadata,
        masked_card_no: t.masked_card_no,
        ysp_tid: t.b_tid,
        ysp_mid: t.b_mid,
        entry_mode: t.entry_mode,
        source_table: type(fragment("'failed_transaction'"), :string)
      }

  temp_query =
    from t in PosTempTransaction,
      select: %{
        id: t.id,
        encrypted_pan: t.encrypted_pan,
        proc_code: t.proc_code,
        total_amount: t.total_amount,
        created_dateTime: t.created_dateTime,
        mcc_code: t.mcc_code,
        s_tid: t.s_tid,
        s_mid: t.s_mid,
        reference_no: t.reference_no,
        approval_code: t.approval_code,
        response_code: t.response_code,
        metadata: t.metadata,
        masked_card_no: t.masked_card_no,
        ysp_tid: t.b_tid,
        ysp_mid: t.b_mid,
        entry_mode: t.entry_mode,
        source_table: type(fragment("'temp_transaction'"), :string)
      }

  pos_query = apply_filters(pos_query, merchant_id, start_date, end_date)
  failed_query = apply_filters(failed_query, merchant_id, start_date, end_date)
  temp_query = apply_filters(temp_query, merchant_id, start_date, end_date)

  combined_query =
    pos_query
    |> union_all(^failed_query)
    |> union_all(^temp_query)
    |> subquery()
    |> order_by([t], desc: t.created_dateTime)

  all_transactions = Repo.all(combined_query)

  transactions =
    Enum.map(all_transactions, fn row ->
      transaction_amount =
        case row.total_amount do
          nil -> ""
          amt when is_binary(amt) ->
            amt = String.trim_leading(amt, "0")
            if amt == "", do: "", else: Integer.parse(amt) |> elem(0) |> Kernel./(100) |> to_string()
          _ -> to_string(row.total_amount)
        end

      status =
        case row.source_table do
          "pos_transaction" -> "success"
          "failed_transaction" -> "failed"
          "temp_transaction" -> "pending"
          _ -> "unknown"
        end

      brand_details = get_brand_details(row.s_mid)
      terminal_details = get_terminal_details(row.s_tid)


      %{
        "transaction_id" => row.id || "",
        "pan" => row.encrypted_pan || "",
        "proc_code" => row.proc_code || "",
        "transaction_amount" => transaction_amount,
        "date_time" => row.created_dateTime || "",
        "settlement_date" => "",
        "mcc" => row.mcc_code || "",
        "terminal_id" => row.s_tid || "",
        "merchant_id" => row.s_mid || "",
        "m_ref_num" => row.reference_no || "",
        "bank_user_id" => row.s_mid || "",
        "approval_code" => row.approval_code || "",
        "response_code" => row.response_code || "",
        "entry_mode" => row.entry_mode || "",
        "meta_data" => row.metadata || "",
        "merchant_name" => "",
        "status" => status,
        "email" => "",
        "processing_id" => "",
        "transaction_ref_number" => row.reference_no || "",
        "payment_reference_id" => row.reference_no || "",
        "device_details" => terminal_details.device_details,
        "store_details" => terminal_details.store_details,
        "brand_details" => brand_details,
        "device_id" => terminal_details.device_details && terminal_details.device_details.serial_number || "",
        "processing_id" => "",
        "masked_card_no" => row.masked_card_no || "",
        "ysp_tid" => row.ysp_tid || "",
        "ysp_mid" => row.ysp_mid || "",
        #"created_at" => row.created_dateTime || "",
        "created_at" => to_uae_datetime(row.created_dateTime),
        "provider_name" => "ysp",
        "transaction_type" => "card"
      }
    end)

  # Calculate total_amount and total_settlement_amount
  total_amount =
    transactions
    |> Enum.reduce(0.0, fn row, acc ->
      case Float.parse(row["transaction_amount"]) do
        {val, _} -> acc + val
        :error -> acc
      end
    end)

  # If you have a settlement amount field, sum it here. Otherwise, use total_amount.
  settlement_amount = total_amount

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

defp get_brand_details(s_mid) do
  Repo.one(
    from(b in "brands",
      where: b.merchant_reference_id == ^s_mid,
      select: %{
        id: b.id,
        code: b.code,
        name: b.name,
        description: b.description,
        merchant_reference_id: b.merchant_reference_id
      }
    )
  )
end

defp get_terminal_details(s_tid) do
  # 1. Get mapping from pos_terminal (singular)
  mapping =
    Repo.one(
      from(pt in "pos_terminal",
        where: pt.terminalid == ^s_tid,
        select: %{serial_number: pt.serial_number, pos_merchant_id: pt.pos_merchant_id}
      )
    )

  # 2. Get device details from pos_terminals (plural)
  device_details =
    case mapping do
      nil -> nil
      %{serial_number: serial_number} ->
        Repo.one(
          from(pt in "pos_terminals",
            where: pt.serial_number == ^serial_number,
            select: %{
              id: pt.id,
              name: pt.name,
              serial_number: pt.serial_number,
              device_type: pt.device_type,
              terminal_id: pt.terminal_id,
              store_id: pt.store_id,
              provider_id: pt.provider_id,
              status: pt.status
            }
          )
        )
    end

  # 3. Get store details if available
  store_details =
    case device_details do
      nil -> nil
      %{store_id: store_id} when not is_nil(store_id) ->
        Repo.one(
          from(s in "stores",
            where: s.id == ^store_id,
            select: %{store_id: s.id, store_code: s.code, store_name: s.name}
          )
        )
      _ -> nil
    end

  %{
    device_details: device_details,
    store_details: store_details
  }
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}"


end