defmodule DaProductApp.TransactionRules do
  @moduledoc """
  Context module for managing and querying transaction rules.
  """

  require Logger
  import Ecto.Query
  alias DaProductApp.Repo
  alias DaProductApp.Repos.ShukriaMmsRepo
  alias DaProductApp.Schemas.ShukriaMms.TransactionRule
  alias DaProductApp.Schemas.PosTerminal
  alias DaProductApp.Schemas.Store
  alias DaProductApp.Schemas.Address

  @doc """
  Fetch all enabled transaction rules for a given merchant_id and optional terminal_id.
  
  Rules are returned with priority order:
  1. Terminal-specific rules (if terminal_id provided)
  2. Merchant-specific rules
  3. Global rules
  
  Returns rules ordered by priority (lower priority number = higher precedence).

  Note: merchant_id parameter is the merchant reference number, which is converted to internal user_id.
  """
  def fetch_rules_for(merchant_id, terminal_id \\ nil) do
    require Logger
    
    # Convert merchant reference number to internal user_id
    user_metadata =
      from(m in "user_metadata",
        where: m.merchant_refrence_number == ^merchant_id,
        select: %{user_id: m.user_id},
        limit: 1
      )
      |> ShukriaMmsRepo.one()

    case user_metadata do
      nil ->
        Logger.warning("fetch_rules_for - no user found for merchant_id: #{merchant_id}")
        {:ok, []}

      %{user_id: user_id} ->
        Logger.info("fetch_rules_for - merchant_id: #{merchant_id} mapped to user_id: #{user_id}")
        
        query =
          from r in TransactionRule,
            where: r.enabled == true and is_nil(r.deleted_at),
            order_by: [asc: r.priority, desc: r.created_at]

        query =
          if terminal_id do
            from r in query,
              where:
                (r.scope == :terminal and r.terminal_id == ^terminal_id) or
                (r.scope == :merchant and r.merchant_id == ^user_id) or
                r.scope == :global
          else
            from r in query,
              where:
                (r.scope == :merchant and r.merchant_id == ^user_id) or
                r.scope == :global
          end

        rules = ShukriaMmsRepo.all(query)
        Logger.info("fetch_rules_for - found #{length(rules)} rules for user_id: #{user_id}")
        
        {:ok, rules}
    end
  rescue
    e ->
      require Logger
      Logger.error("fetch_rules_for failed: #{inspect(e)}")
      {:error, e}
  end


  @doc """
  Fetch a single transaction rule by rule_id.
  """
  def get_rule_by_id(rule_id) do
    case ShukriaMmsRepo.get_by(TransactionRule, rule_id: rule_id) do
      nil -> {:not_found, "Rule not found"}
      rule -> {:ok, rule}
    end
  rescue
    e ->
      {:error, e}
  end

  @doc """
  Format rules into a response structure for the API.
  Returns a map with rules grouped by type along with metadata.
  """
  def format_rules_response(rules) do
    %{
      rules: Enum.map(rules, fn rule ->
        %{
          rule_id: rule.rule_id,
          rule_name: rule.rule_name,
          rule_type: rule.rule_type,
          response_code: rule.response_code,
          scope: rule.scope,
          params: rule.params,
          priority: rule.priority
        }
      end),
      total_count: length(rules)
    }
  end

  @doc """
  Fetch merchant info (name and address) by merchant_id.

  Looks up user_metadata by merchant_reference_number to get user_id,
  then fetches the kyc_requests data JSON to extract merchant name and address.
  Falls back to default values if data is not found or an error occurs.
  """
  def fetch_merchant_info(merchant_id) do

    user_metadata =
      from(m in "user_metadata",
        where: m.merchant_refrence_number == ^merchant_id,
        select: %{user_id: m.user_id},
        limit: 1
      )
      |> ShukriaMmsRepo.one()

    Logger.info("fetch_merchant_info - merchant_id: #{merchant_id}, user_metadata: #{inspect(user_metadata)}")

    case user_metadata do
      nil ->
        Logger.warning("fetch_merchant_info - no user_metadata found for merchant_id: #{merchant_id}")
        default_merchant_info()

      %{user_id: user_id} ->
        # Fetch ALL kyc_requests for this user (to search across multiple kyc_method_ids)
        kyc_records =
          from(k in "kyc_requests",
            where: k.user_id == ^user_id,
            select: %{data: k.data, kyc_method_id: k.kyc_method_id}
          )
          |> ShukriaMmsRepo.all()

        Logger.info("fetch_merchant_info - user_id: #{user_id}, kyc_records count: #{length(kyc_records)}")

        merchant_config =
          from(c in "merchant_configuration",
            where: c.user_id == ^user_id,
            select: %{logo: c.logo, description: c.description, message: c.message, model_name: c.model_name, settings: c.settings,show_cardholder_name: c.show_cardholder_name},
            limit: 1
          )
          |> ShukriaMmsRepo.one()

        Logger.info("fetch_merchant_info - merchant_config: #{inspect(merchant_config)}")

        # Extract merchant name from first record (for backward compatibility)
        merchant_name = extract_merchant_name_from_kyc_records(kyc_records)
        
        # Extract address by searching through ALL kyc records
        merchant_address = extract_address_from_kyc_records(kyc_records)

        {logo, description, message, model_name, settings, show_cardholder_name} =
          case merchant_config do
            %{
              logo: logo_val,
              description: desc,
              message: msg,
              model_name: model,
              settings: sets,
              show_cardholder_name: show_cardholder
            } ->
              {
                logo_val || "N/A",
                desc || "N/A",
                msg || "N/A",
                model || 0,
                sets,
                normalize_config_boolean(show_cardholder)
              }
            _ ->
              {"N/A", "N/A", "N/A", 0, nil, false}
          end

        %{
          user_id: user_id,
          merchant_name: merchant_name,
          merchant_address: merchant_address,
          logo: logo,
          description: description,
          message: message,
          model_name: model_name,
          settings: settings,
          show_cardholder_name: show_cardholder_name
        }
    end
  rescue
    e ->
      Logger.error("fetch_merchant_info failed: #{inspect(e)}")
      default_merchant_info()
  end

  @doc """
  Fetch merchant info (name and address) by terminal_id from main database.

  Query chain:
  1. pos_terminals.terminalid → store_id
  2. stores.id = store_id → name (merchant_name) + address_id
  3. addresses.id = address_id → line1 (merchant_address)
  
  Also fetches logo, description, message, settings from merchant_configuration (ShukriaMmsRepo)
  using the merchant_id from request params.
  Falls back to default values if data is not found or an error occurs.
  """
  def fetch_merchant_info_from_terminal(terminal_id, merchant_id) do
    Logger.info("fetch_merchant_info_from_terminal - terminal_id: #{terminal_id}, merchant_id: #{merchant_id}")

    # Optimized: Single query with LEFT JOINs to fetch terminal → store → address in one roundtrip
    result =
      from(pt in PosTerminal,
        left_join: s in Store, on: s.id == pt.store_id,
        left_join: a in Address, on: a.id == s.address_id,
        where: pt.terminalid == ^terminal_id,
        select: %{
          store_id: pt.store_id,
          merchant_name: s.name,
          address_line1: a.line1
        },
        limit: 1
      )
      |> Repo.one()

    Logger.info("fetch_merchant_info_from_terminal - query result: #{inspect(result)}")

    case result do
      nil ->
        Logger.warning("fetch_merchant_info_from_terminal - no terminal found for terminal_id: #{terminal_id}")
        # Fallback to ShukriaMms database for all merchant info
        fallback_merchant_name_address(merchant_id)

      %{merchant_name: merchant_name, address_line1: address_line1} ->
        # Process merchant_address (handle NULL from LEFT JOIN)
        merchant_address = 
          if address_line1 && address_line1 != "", do: address_line1, else: "N/A"

        # Fetch merchant_configuration from ShukriaMmsRepo using merchant_id from request
        # merchant_id → user_metadata.merchant_refrence_number → user_id → merchant_configuration
        # If not found, use default values for logo/description/message/settings
        
        # Get merchant config using merchant_id from request params
        merchant_config = fetch_merchant_config_by_merchant_id(merchant_id)

        {logo, description, message, model_name, settings, user_id, show_cardholder_name} =
          case merchant_config do
            %{logo: logo_val, description: desc, message: msg, model_name: model, settings: sets, user_id: uid, show_cardholder_name: show_cardholder} ->
              {logo_val || "N/A", desc || "N/A", msg || "N/A", model || 0, sets, uid, show_cardholder}
            _ ->
              {"N/A", "N/A", "N/A", 0, nil, nil, false}
          end

        # Check if merchant_name or merchant_address is missing from main database
        # If missing, fallback to ShukriaMms database (kyc_requests extraction)
        final_merchant_name = merchant_name || "N/A"
        final_merchant_address = merchant_address
        
        needs_name_fallback = is_nil(final_merchant_name) or final_merchant_name == "" or final_merchant_name == "N/A"
        needs_address_fallback = is_nil(final_merchant_address) or final_merchant_address == "" or final_merchant_address == "N/A"
        
        {final_merchant_name, final_merchant_address} = 
          if needs_name_fallback or needs_address_fallback do
            Logger.info("fetch_merchant_info_from_terminal - falling back to ShukriaMms for name/address. needs_name: #{needs_name_fallback}, needs_address: #{needs_address_fallback}")
            fallback_data = fetch_name_address_fallback(merchant_id, user_id)
            
            {
              if(needs_name_fallback, do: fallback_data.merchant_name, else: final_merchant_name),
              if(needs_address_fallback, do: fallback_data.merchant_address, else: final_merchant_address)
            }
          else
            {final_merchant_name, final_merchant_address}
          end

        %{
          user_id: user_id,
          merchant_name: final_merchant_name,
          merchant_address: final_merchant_address,
          logo: logo,
          description: description,
          message: message,
          model_name: model_name,
          settings: settings,
          show_cardholder_name: show_cardholder_name
        }
    end
  rescue
    e ->
      Logger.error("fetch_merchant_info_from_terminal failed: #{inspect(e)}")
      # Try fallback to ShukriaMms if merchant_id is available
      if merchant_id && merchant_id != "" do
        Logger.info("fetch_merchant_info_from_terminal - attempting ShukriaMms fallback after error")
        fallback_merchant_name_address(merchant_id)
      else
        default_merchant_info()
      end
  end

  # Helper: Fetch merchant configuration from ShukriaMmsRepo using merchant_id from request
  # merchant_id (from request) → user_metadata.merchant_refrence_number → user_id → merchant_configuration
  defp fetch_merchant_config_by_merchant_id(merchant_id) when is_nil(merchant_id) or merchant_id == "" do
    Logger.warning("fetch_merchant_config_by_merchant_id - merchant_id is nil or empty")
    nil
  end
  
  defp fetch_merchant_config_by_merchant_id(merchant_id) do
    Logger.info("fetch_merchant_config_by_merchant_id - merchant_id: #{merchant_id}")
    
    # Map merchant_id to user_id via user_metadata table in ShukriaMmsRepo
    user_metadata =
      from(m in "user_metadata",
        where: m.merchant_refrence_number == ^merchant_id,
        select: %{user_id: m.user_id},
        limit: 1
      )
      |> ShukriaMmsRepo.one()
    
    Logger.info("fetch_merchant_config_by_merchant_id - user_metadata: #{inspect(user_metadata)}")

    case user_metadata do
      %{user_id: user_id} when not is_nil(user_id) ->
        Logger.info("fetch_merchant_config_by_merchant_id - found user_id: #{user_id}")
        
        # Fetch merchant_configuration from ShukriaMmsRepo
        merchant_config =
          from(c in "merchant_configuration",
            where: c.user_id == ^user_id,
            select: %{logo: c.logo, description: c.description, message: c.message, model_name: c.model_name, settings: c.settings, show_cardholder_name: c.show_cardholder_name},
            limit: 1
          )
          |> ShukriaMmsRepo.one()
        
        Logger.info("fetch_merchant_config_by_merchant_id - merchant_config: #{inspect(merchant_config)}")

        case merchant_config do
          %{} = config ->
            # Load global defaults from in-memory cache and merge per-key for settings
            global_map = DaProductApp.GlobalSettings.get_all()
            {merged_settings, sources_map} = merge_settings_with_global(Map.get(config, :settings), global_map)

            # Logging: summarize origin of settings keys
            total_keys = map_size(merged_settings || %{})
            global_keys =
              sources_map
              |> Enum.filter(fn {_k, v} -> v == :global end)
              |> Enum.map(fn {k, _v} -> k end)

            Logger.info("Merged settings for merchant_id=#{merchant_id} user_id=#{user_id} - total_keys=#{total_keys} global_fallbacks=#{length(global_keys)} keys_from_global=#{inspect(global_keys)}")

            # Detailed debug log per key with masking for sensitive keys
            Enum.each(sources_map, fn {k, src} ->
              val = Map.get(merged_settings || %{}, k)
              masked = mask_setting_for_log(k, val)
              Logger.debug("setting=#{k} value=#{masked} source=#{inspect(src)} merchant_id=#{merchant_id} user_id=#{user_id}")
            end)

            config
            |> Map.put(:settings, merged_settings)
            |> Map.put(:settings_source, sources_map)
            |> Map.put(:user_id, user_id)
            |> Map.update(:show_cardholder_name, false, &normalize_config_boolean/1)
          _ -> nil
        end

      _ ->
        Logger.warning("fetch_merchant_config_by_merchant_id - no user_metadata found for merchant_id: #{merchant_id}")
        nil
    end
  rescue
    e ->
      Logger.error("fetch_merchant_config_by_merchant_id failed: #{inspect(e)}")
      nil
  end

  # Helper: Fetch merchant name and address from ShukriaMms database as fallback
  # Uses merchant_id → user_metadata → kyc_requests → extract from JSON data
  # This is used when main database (pos_terminals → stores → addresses) fails to provide name/address
  defp fetch_name_address_fallback(merchant_id, user_id \\ nil) do
    Logger.info("fetch_name_address_fallback - merchant_id: #{merchant_id}, user_id: #{inspect(user_id)}")
    
    # Get user_id if not provided
    resolved_user_id = if is_nil(user_id) do
      user_metadata =
        from(m in "user_metadata",
          where: m.merchant_refrence_number == ^merchant_id,
          select: %{user_id: m.user_id},
          limit: 1
        )
        |> ShukriaMmsRepo.one()
      
      case user_metadata do
        %{user_id: uid} -> uid
        _ -> nil
      end
    else
      user_id
    end
    
    Logger.info("fetch_name_address_fallback - resolved_user_id: #{inspect(resolved_user_id)}")
    
    if is_nil(resolved_user_id) do
      Logger.warning("fetch_name_address_fallback - no user_id found for merchant_id: #{merchant_id}")
      %{merchant_name: "N/A", merchant_address: "N/A"}
    else
      # Fetch ALL kyc_requests for this user
      kyc_records =
        from(k in "kyc_requests",
          where: k.user_id == ^resolved_user_id,
          select: %{data: k.data, kyc_method_id: k.kyc_method_id}
        )
        |> ShukriaMmsRepo.all()
      
      Logger.info("fetch_name_address_fallback - kyc_records count: #{length(kyc_records)}")
      
      # Extract merchant name and address from kyc records
      merchant_name = extract_merchant_name_from_kyc_records(kyc_records)
      merchant_address = extract_address_from_kyc_records(kyc_records)
      
      Logger.info("fetch_name_address_fallback - extracted name: #{merchant_name}, address: #{merchant_address}")
      
      %{merchant_name: merchant_name, merchant_address: merchant_address}
    end
  rescue
    e ->
      Logger.error("fetch_name_address_fallback failed: #{inspect(e)}")
      %{merchant_name: "N/A", merchant_address: "N/A"}
  end

  # Helper: Complete fallback when entire main database query fails
  # Fetches name, address, logo, description, message, settings all from ShukriaMms
  defp fallback_merchant_name_address(merchant_id) do
    Logger.info("fallback_merchant_name_address - using complete ShukriaMms fallback for merchant_id: #{merchant_id}")
    
    # Get merchant config (contains logo, description, message, settings, user_id)
    merchant_config = fetch_merchant_config_by_merchant_id(merchant_id)
    
    case merchant_config do
      %{user_id: user_id} = config ->
        # Get name and address from kyc_requests
        name_address = fetch_name_address_fallback(merchant_id, user_id)
        
        # Merge everything
        %{
          user_id: user_id,
          merchant_name: name_address.merchant_name,
          merchant_address: name_address.merchant_address,
          logo: config.logo || "N/A",
          description: config.description || "N/A",
          message: config.message || "N/A",
          model_name: config.model_name || 0,
          settings: config.settings,
          show_cardholder_name: config.show_cardholder_name || false
        }
      
      _ ->
        # Couldn't find merchant config, try to at least get name/address
        name_address = fetch_name_address_fallback(merchant_id)
        
        %{
          user_id: nil,
          merchant_name: name_address.merchant_name,
          merchant_address: name_address.merchant_address,
          logo: "N/A",
          description: "N/A",
          message: "N/A",
          model_name: 0,
          settings: nil,
          show_cardholder_name: false
        }
    end
  rescue
    e ->
      Logger.error("fallback_merchant_name_address failed: #{inspect(e)}")
      default_merchant_info()
  end

  defp default_merchant_info do
    %{
      user_id: nil,
      merchant_name: "N/A",
      merchant_address: "N/A",
      logo: "N/A",
      description: "N/A",
      message: "N/A",
      model_name: 0,
      settings: nil,
      show_cardholder_name: false
    }
  end

  defp normalize_config_boolean(value) when value in [true, 1, "1"], do: true
  defp normalize_config_boolean(_), do: false

  # Helper: Get value from map with case-insensitive key matching
  defp get_case_insensitive(map, key) when is_map(map) and is_binary(key) do
    normalized_key = String.downcase(key)

    map
    |> Enum.find(fn {k, _v} -> String.downcase(to_string(k)) == normalized_key end)
    |> case do
      {_k, v} -> v
      _ -> nil
    end
  end

  defp get_case_insensitive(_map, _key), do: nil

  # Helper: Get non-empty string value from map (case-insensitive)
  defp get_non_empty_string(map, key) do
    val = get_case_insensitive(map, key)
    if is_binary(val) and String.trim(val) != "", do: val, else: nil
  end

  # Helper: Determine if a key is sensitive and should be masked in logs
  defp sensitive_setting_key?(key) when is_binary(key) do
    Regex.match?(~r/(PASSWORD|PIN|KEY|SECRET|TOKEN)/i, key)
  end
  defp sensitive_setting_key?(_), do: false

  # Helper: Mask value for logging if key is sensitive; otherwise truncate long values
  defp mask_setting_for_log(key, value) do
    if sensitive_setting_key?(key) do
      "(masked)"
    else
      cond do
        is_binary(value) and String.length(value) > 200 -> String.slice(value, 0, 200) <> "..."
        true -> inspect(value)
      end
    end
  end

  # Helper: Extract merchant name with priority (kyc_method_id=19 first, then others)
  defp extract_merchant_name_from_kyc_records([]), do: "N/A"
  defp extract_merchant_name_from_kyc_records(kyc_records) do
    # Priority 1: Try to find in kyc_method_id=19
    method_19_record = Enum.find(kyc_records, fn record -> 
      record.kyc_method_id == 19 
    end)
    
    case method_19_record do
      %{data: data} ->
        # Found kyc_method_id=19, try to extract name from it
        data_map = parse_kyc_data(data)
        name = get_non_empty_string(data_map, "Merchant's Legal Name")
        
        if name do
          Logger.info("Merchant name found in kyc_method_id=19: #{name}")
          name  # Found in kyc_method_id=19, STOP here
        else
          Logger.info("Merchant name empty/null in kyc_method_id=19, searching other records")
          # Not found in kyc_method_id=19, search other records
          search_merchant_name_in_other_records(kyc_records)
        end
      
      nil ->
        Logger.info("kyc_method_id=19 not found, searching all records")
        # kyc_method_id=19 doesn't exist, search all records
        search_merchant_name_in_other_records(kyc_records)
    end
  end
  
  # Helper: Search merchant name in all records (fallback)
  defp search_merchant_name_in_other_records(kyc_records) do
    found_name = Enum.find_value(kyc_records, fn record ->
      data_map = parse_kyc_data(record.data)
      case get_non_empty_string(data_map, "Merchant's Legal Name") do
        nil -> nil
        name -> 
          Logger.info("Merchant name found in kyc_method_id=#{record.kyc_method_id}: #{name}")
          name
      end
    end)
    
    found_name || "N/A"
  end

  # Helper: Extract address from ALL kyc records with fallback logic
  # Searches all records for "Address" first, then "Complete Address", then "N/A"
  defp extract_address_from_kyc_records([]), do: "N/A"
  defp extract_address_from_kyc_records(kyc_records) do
    # Parse all data fields into maps
    data_maps = Enum.map(kyc_records, fn record -> parse_kyc_data(record.data) end)
    
    # First, try to find "Address" in any record
    address = Enum.find_value(data_maps, fn data_map ->
      get_non_empty_string(data_map, "Address")
    end)
    
    case address do
      nil ->
        # If Address not found, try "Complete Address" in any record
        complete_address = Enum.find_value(data_maps, fn data_map ->
          get_non_empty_string(data_map, "Complete Address")
        end)
        
        complete_address || "N/A"
      
      found_address ->
        found_address
    end
  end

  # Helper: Parse kyc data (handles both map and JSON string)
  defp parse_kyc_data(data) when is_map(data), do: data
  defp parse_kyc_data(data) when is_binary(data) do
    case Jason.decode(data) do
      {:ok, data_map} -> data_map
      _ -> %{}
    end
  end
  defp parse_kyc_data(_), do: %{}

  # Helper: Load global defaults from ShukriaMms `global_table`
  # Returns a map of string_key => config_value
  defp load_global_settings do
    query = from(g in "global_table", select: {g.config_key, g.config_value})
    ShukriaMmsRepo.all(query)
    |> Enum.into(%{}, fn {k, v} -> {to_string(k), v} end)
  rescue
    e ->
      Logger.error("load_global_settings failed: #{inspect(e)}")
      %{}
  end

  # Helper: Merge merchant settings with global defaults per-key.
  # Returns {merged_settings_map, sources_map}
  # - merchant_settings may be nil, a map, or a JSON string.
  # - global_map should be map of string keys to values.
  # Merchant values are preserved when non-empty; missing/nil/empty strings use global value.
  # sources_map contains values :merchant, :global, or :missing
  defp merge_settings_with_global(merchant_settings, global_map) when is_map(global_map) do
    # Normalize merchant settings to a map with string keys
    merchant_map =
      cond do
        is_map(merchant_settings) -> merchant_settings
        is_binary(merchant_settings) -> parse_kyc_data(merchant_settings)
        true -> %{}
      end

    merchant_map = Enum.into(merchant_map, %{}, fn {k, v} -> {to_string(k), v} end)

    # initial sources: mark merchant keys that have non-empty values as :merchant
    initial_sources =
      merchant_map
      |> Enum.map(fn {k, v} ->
        source =
          cond do
            v == nil -> :missing
            v == "" -> :missing
            is_binary(v) and String.trim(v) == "" -> :missing
            true -> :merchant
          end

        {k, source}
      end)
      |> Enum.into(%{})

    # start merged with merchant values; add any global keys that merchant lacks
    {merged, sources} =
      Enum.reduce(global_map, {merchant_map, initial_sources}, fn {gk, gv}, {acc_map, acc_src} ->
        current = Map.get(acc_map, gk)

        if current == nil or current == "" or (is_binary(current) and String.trim(current) == "") do
          {Map.put(acc_map, gk, gv), Map.put(acc_src, gk, :global)}
        else
          {acc_map, acc_src}
        end
      end)

    # Merge initial_sources with any updates from the reduce (global overrides take precedence)
    sources_final = Map.merge(initial_sources, sources, fn _k, _init, new -> new end)

    {merged, sources_final}
  end
  defp merge_settings_with_global(_merchant_settings, _), do: {%{}, %{}}

  @doc """
  Update merchant_configuration.model_name based on device model check.
  Sets model_name to 1 (true) if MF919, 0 (false) otherwise.
  """
  def update_merchant_device_model(merchant_id, is_mf919) do
    
    # Get user_id from merchant_id
    user_metadata =
      from(m in "user_metadata",
        where: m.merchant_refrence_number == ^merchant_id,
        select: %{user_id: m.user_id},
        limit: 1
      )
      |> ShukriaMmsRepo.one()

    case user_metadata do
      nil ->
        Logger.warning("update_merchant_device_model - no user found for merchant_id: #{merchant_id}")
        {:error, :merchant_not_found}

      %{user_id: user_id} ->
        # Convert boolean to integer (1 or 0)
        model_value = if is_mf919, do: 1, else: 0
        
        # Update merchant_configuration table
        result =
          from(c in "merchant_configuration",
            where: c.user_id == ^user_id
          )
          |> ShukriaMmsRepo.update_all(set: [model_name: model_value])

        case result do
          {1, _} ->
            Logger.info("Updated model_name=#{model_value} for user_id: #{user_id} (merchant: #{merchant_id})")
            {:ok, model_value}
          
          {0, _} ->
            Logger.warning("No merchant_configuration found for user_id: #{user_id}")
            {:error, :config_not_found}
          
          _ ->
            Logger.info("Updated model_name=#{model_value} for user_id: #{user_id}")
            {:ok, model_value}
        end
    end
  rescue
    e ->
      Logger.error("update_merchant_device_model failed: #{inspect(e)}")
      {:error, e}
  end

  @doc """
  Evaluate transaction against rules and return decision.
  
  Returns:
  - {:allow, code, message} - Transaction allowed
  - {:decline, code, message, rule} - Transaction declined by specific rule
  - {:flag, code, message, rule} - Transaction flagged for review
  """
  def evaluate_transaction(rules, transaction_data) do
    # Extract transaction details
    amount_value = get_in(transaction_data, ["amount", "value"]) |> parse_amount()
    amount_currency = get_in(transaction_data, ["amount", "currency"]) || "784"
    
    # Extract MTI and Processing Code for transaction type derivation
    mti = transaction_data["mti"]
    processing_code = transaction_data["processing_code"]
    
    # Derive transaction type from MTI + Processing Code (ISO 8583)
    derived_type = derive_transaction_type(mti, processing_code)
    transaction_type = derived_type || "PURCHASE"
    
    # Log if we fell back to default
    if is_nil(derived_type) do
      Logger.warning("Transaction type could not be derived from MTI '#{inspect(mti)}' and PC '#{inspect(processing_code)}', defaulting to 'PURCHASE'")
    end
    
    merchant_id = transaction_data["merchant_id"]
    terminal_id = transaction_data["terminal_id"]
    card_number = transaction_data["card_number"]
    mcc = get_in(transaction_data, ["mcc"]) || get_in(transaction_data, ["context", "mcc"])
    bin = get_in(transaction_data, ["card", "bin"]) || get_in(transaction_data, ["bin"])
    country = get_in(transaction_data, ["context", "country"]) || get_in(transaction_data, ["country"])
    stan = get_in(transaction_data, ["transaction", "stan"])
    rrn = get_in(transaction_data, ["transaction", "rrn"])
    # Use transaction timestamp if present, else fallback to server time
    txn_timestamp =
      case get_in(transaction_data, ["transaction", "timestamp"]) do
        nil -> DateTime.utc_now()
        ts when is_binary(ts) ->
          case DateTime.from_iso8601(ts) do
            {:ok, dt, _offset} -> dt
            _ -> DateTime.utc_now()
          end
        _ -> DateTime.utc_now()
      end
    
    Logger.info("Evaluating transaction - MTI: #{mti}, PC: #{processing_code}, Type: #{transaction_type}, Amount: #{amount_value}, Rules: #{length(rules)}, Timestamp: #{txn_timestamp}")
    
    # Evaluate each rule in priority order
    case evaluate_rules_sequence(rules, %{
      amount: amount_value,
      currency: amount_currency,
      type: transaction_type,
      mti: mti,
      processing_code: processing_code,
      merchant_id: merchant_id,
      terminal_id: terminal_id,
      card_number: card_number,
      timestamp: txn_timestamp,
      mcc: mcc,
      bin: bin,
      country: country,
      stan: stan,
      rrn: rrn
    }) do
      {:decline, rule, reason} ->
        Logger.info("DECLINED - Rule: #{rule.rule_id}, Reason: #{reason}")
        {:decline, rule.response_code || "05", reason, rule}
      {:flag, rule, reason} ->
        Logger.info("FLAGGED - Rule: #{rule.rule_id}, Reason: #{reason}")
        {:flag, "FLAG", reason, rule}
      :allow ->
        Logger.info("ALLOWED - All rules passed")
        {:allow, "00", "Approved by rules engine"}
    end
  end

  # Parse amount from string format (handles both "000000002500" and "2500.00" formats)
  defp parse_amount(nil), do: 0.0
  defp parse_amount(value) when is_number(value), do: value * 1.0
  defp parse_amount(value) when is_binary(value) do
    cond do
      String.contains?(value, ".") ->
        case Float.parse(value) do
          {float_val, _} -> float_val
          :error -> 0.0
        end
      
      String.length(value) > 6 and String.match?(value, ~r/^\d+$/) ->
        # Assume ISO 8583 format (amount in minor units, last 2 digits are decimals)
       case Integer.parse(value) do
          {int_val, _} -> int_val / 100.0
          :error -> 0.0
        end
      
      true ->
       case Integer.parse(value) do
          {int_val, _} -> int_val * 1.0
          :error -> 0.0
        end
    end
  end

  # Evaluate rules in sequence, stop at first decline/flag
  defp evaluate_rules_sequence([], _context), do: :allow
  defp evaluate_rules_sequence([rule | rest], context) do
    case evaluate_single_rule(rule, context) do
      :allow -> evaluate_rules_sequence(rest, context)
      {:decline, reason} -> {:decline, rule, reason}
      {:flag, reason} -> {:flag, rule, reason}
    end
  end

  # Evaluate a single rule against transaction context
  defp evaluate_single_rule(rule, context) do
    case rule.rule_type do
      "MIN_AMOUNT" -> evaluate_min_amount(rule, context)
      "MAX_AMOUNT" -> evaluate_max_amount(rule, context)
      "TIME_WINDOW" -> evaluate_time_window(rule, context)
      "BLACKLIST" -> evaluate_blacklist(rule, context)
      "VELOCITY_COUNT" -> evaluate_velocity_count(rule, context)
      "VELOCITY_AMOUNT" -> evaluate_velocity_amount(rule, context)
      "WEEKLY_TOTAL" -> evaluate_weekly_total(rule, context)
      "DAILY_LIMIT" -> evaluate_velocity(rule, context)
      "DAILY_TOTAL" -> evaluate_daily_total(rule, context)
      "MONTHLY_TOTAL" -> evaluate_monthly_total(rule, context)
      "COUNT_LIMIT" -> evaluate_count_limit(rule, context)
      "REFUND_POLICY" -> evaluate_refund_policy(rule, context)
      "DUPLICATE_DETECTION" -> evaluate_duplicate_detection(rule, context)
      "MCC_RESTRICTION" -> evaluate_mcc_restriction(rule, context)
      "BIN_LIMITS" -> evaluate_bin_limits(rule, context)
      "TXN_TYPE_CONTROL" -> evaluate_txn_type_control(rule, context)
      "GEO_RESTRICTION" -> evaluate_geo_restriction(rule, context)
      "SOFT_DECLINE" -> evaluate_soft_decline(rule, context)
      "CUSTOM_SCRIPT" -> evaluate_custom_script(rule, context)
      "REFUND_VELOCITY" -> evaluate_refund_velocity(rule, context)
      _ -> :allow  # Unknown rule types are ignored
    end
  end

  # MIN_AMOUNT rule: Decline if transaction amount is below minimum
  defp evaluate_min_amount(rule, context) do
    min_amount = get_rule_param(rule, "min_amount", 0.0) |> parse_amount()
    
    if context.amount < min_amount do
      formatted_amount = :erlang.float_to_binary(context.amount, decimals: 2)
      formatted_min = :erlang.float_to_binary(min_amount, decimals: 2)
      {:decline, "Transaction amount #{formatted_amount} is below minimum #{formatted_min}"}
    else
      :allow
    end
  end

  # MAX_AMOUNT rule: Decline if transaction amount exceeds maximum
  defp evaluate_max_amount(rule, context) do
    max_amount = get_rule_param(rule, "max_amount", 999999.0) |> parse_amount()
    
    Logger.info("MAX_AMOUNT check - Transaction: #{context.amount}, Limit: #{max_amount}, Rule: #{rule.rule_id}")
    
    if context.amount > max_amount do
      formatted_amount = :erlang.float_to_binary(context.amount, decimals: 2)
      formatted_max = :erlang.float_to_binary(max_amount, decimals: 2)
      {:decline, "Transaction amount #{formatted_amount} exceeds maximum #{formatted_max}"}
    else
      :allow
    end
  end

  # TIME_WINDOW rule: Control transaction acceptance based on time windows
  # Supports multiple time periods with timezone conversion
  #
  # IMPORTANT: This rule supports THREE operational modes controlled by "default_action":
  #
  # MODE 1 - WHITELIST (default_action = "allow"):
  #   - Periods define ONLY times when transactions are ALLOWED
  #   - Inside a defined period → ALLOW transaction
  #   - Outside all periods → DECLINE transaction
  #   - Use case: "Only allow transactions during business hours 9am-5pm"
  #
  # MODE 2 - BLACKLIST (default_action = "block" or "decline"):
  #   - Periods define times when transactions are BLOCKED/RESTRICTED
  #   - Inside a defined period → DECLINE transaction
  #   - Outside all periods → ALLOW transaction
  #   - Use case: "Block transactions during nightly maintenance 2am-4am"
  #
  # MODE 3 - FLAG MODE (default_action = "flag"):
  #   - Periods define times when transactions should be FLAGGED for review
  #   - Inside a defined period → FLAG transaction
  #   - Outside all periods → ALLOW transaction
  #   - Use case: "Flag transactions during risky hours (midnight-6am) for manual review"
  #
  # Params structure:
  #   {
  #     "default_action": "allow",  # "allow" = whitelist, "block"/"decline" = blacklist, "flag" = flag mode
  #     "allowed_periods": [        # Note: name is misleading - these are checked periods
  #       {"start": "09:00", "end": "17:00", "tz": "UTC"},
  #       {"start": "09:00", "end": "17:00", "tz": "UTC+4"}
  #     ]
  #   }
  defp evaluate_time_window(rule, context) do
    default_action = get_rule_param(rule, "default_action", "allow")
    allowed_periods = get_rule_param(rule, "allowed_periods", [])
    
    current_time = DateTime.to_time(context.timestamp)
    
    Logger.info("TIME_WINDOW check - Transaction UTC: #{context.timestamp}, Time: #{current_time}, Mode: #{default_action}")
    
    # If no periods defined, apply default_action logic
    if Enum.empty?(allowed_periods) do
      case default_action do
        "allow" -> {:decline, "No time periods configured"}
        _ -> :allow
      end
    else
      # Check if current time falls in any defined period
      time_in_any_period = Enum.any?(allowed_periods, fn period ->
        result = check_time_period(context.timestamp, period)
        Logger.info("  Checking period #{period["start"]}-#{period["end"]} (#{period["tz"]}): #{result}")
        result
      end)
      
      Logger.info("  Time in any period: #{time_in_any_period}, Periods: #{inspect(allowed_periods)}")
      
      # Apply mode-specific logic based on default_action
      case {default_action, time_in_any_period} do
        # WHITELIST MODE: Periods = allowed times
        {"allow", true} -> 
          Logger.info("  Decision: ALLOW (whitelist mode, inside allowed period)")
          :allow
        {"allow", false} -> 
          Logger.info("  Decision: DECLINE (whitelist mode, outside allowed periods)")
          {:decline, "Transaction outside allowed time window"}
        
        # BLACKLIST MODE: Periods = blocked times (supports both "block" and "decline")
        {action, true} when action in ["block", "decline"] -> 
          Logger.info("  Decision: DECLINE (blacklist mode, inside blocked period)")
          {:decline, "Transaction in restricted time window"}
        {action, false} when action in ["block", "decline"] -> 
          Logger.info("  Decision: ALLOW (blacklist mode, outside blocked periods)")
          :allow
        
        # FLAG MODE: Periods = times to flag for review
        {"flag", true} -> 
          Logger.info("  Decision: FLAG (flag mode, inside flagged period)")
          {:flag, "Transaction during flagged time window - requires review"}
        {"flag", false} -> 
          Logger.info("  Decision: ALLOW (flag mode, outside flagged periods)")
          :allow
        
        _ -> 
          Logger.warning("Unknown default_action: #{default_action}, defaulting to allow")
          :allow
      end
    end
  end

  # Helper: Check if timestamp falls within a specific time period
  defp check_time_period(timestamp, period) do
    start_time_str = Map.get(period, "start", "00:00")
    end_time_str = Map.get(period, "end", "23:59")
    tz = Map.get(period, "tz", "UTC")
    
    # Convert timestamp to the period's timezone
    converted_time = case shift_timezone(timestamp, tz) do
      {:ok, dt} -> DateTime.to_time(dt)
      {:error, _} -> DateTime.to_time(timestamp)  # Fallback to UTC
    end
    
    case {parse_time(start_time_str), parse_time(end_time_str)} do
      {{:ok, start_t}, {:ok, end_t}} ->
        time_in_range?(converted_time, start_t, end_t)
      _ -> 
        false  # If time parsing fails, treat as not in range
    end
  end

  # Helper: Shift DateTime to a different timezone
  # Supports UTC, UTC+N, UTC-N formats
  defp shift_timezone(datetime, "UTC"), do: {:ok, datetime}
  defp shift_timezone(datetime, tz) when is_binary(tz) do
    cond do
      # Handle UTC+4, UTC-5, etc.
      String.starts_with?(tz, "UTC+") or String.starts_with?(tz, "UTC-") ->
        offset_str = String.replace_prefix(tz, "UTC", "")
        parse_utc_offset(datetime, offset_str)
      
      # For named timezones, fallback to UTC for now
      # In production, integrate tzdata library for full timezone support
      true ->
        Logger.warning("Timezone #{tz} not fully supported, using UTC")
        {:ok, datetime}
    end
  end
  defp shift_timezone(datetime, _), do: {:ok, datetime}

  # Helper: Parse UTC offset like "+4" or "-5" and adjust datetime
  defp parse_utc_offset(datetime, offset_str) do
    case Integer.parse(offset_str) do
      {hours, _} ->
        # Adjust datetime by offset hours
        shifted = DateTime.add(datetime, hours * 3600, :second)
        {:ok, shifted}
      :error ->
        {:ok, datetime}
    end
  end

  # BLACKLIST rule: Evaluate based on type (blacklist/allowlist) and action (block/flag/allow)
  # Supports matching against card_number, merchant_id, and terminal_id
  defp evaluate_blacklist(rule, context) do
    # Extract new format params
    params_type = get_rule_param(rule, "type", "blacklist") 
      |> to_string() 
      |> String.downcase()
    params_action = get_rule_param(rule, "action", "block") 
      |> to_string() 
      |> String.downcase()
    params_values = get_rule_param(rule, "values", [])
    
    # Convert context values to strings for exact comparison
    # Treat nil and empty string as valid (can be matched)
    card_number_str = to_string(context.card_number || "")
    merchant_id_str = to_string(context.merchant_id || "")
    terminal_id_str = to_string(context.terminal_id || "")
    
    # Check if any transaction value matches any value in the params.values array
    # Build list of transaction values to check
    transaction_values = [
      {card_number_str, "Card", context.card_number},
      {merchant_id_str, "Merchant", context.merchant_id},
      {terminal_id_str, "Terminal", context.terminal_id}
    ]
    
    # Find if there's a match and which field matched
    match_result = Enum.find(transaction_values, fn {str_val, _label, _original} ->
      str_val != "" && Enum.member?(params_values, str_val)
    end)
    
    match_found = match_result != nil
    
    # Determine which field matched (for specific error messages)
    matched_field = case match_result do
      {_str_val, label, original_val} -> "#{label} #{original_val}"
      nil -> "Value"
    end
    
    Logger.info("BLACKLIST check - Type: #{params_type}, Action: #{params_action}, Match found: #{match_found}, Field: #{matched_field}")
    
    # Apply logic based on type
    case params_type do
      "blacklist" ->
        if match_found do
          # Match found in blacklist → apply action
          apply_action(params_action, "#{matched_field} is blacklisted")
        else
          # No match in blacklist → allow
          :allow
        end
      
      "allowlist" ->
        if match_found do
          # Match found in allowlist → allow
          :allow
        else
          # No match in allowlist → apply action (deny by default)
          apply_action(params_action, "#{matched_field} not in allowlist")
        end
      
      _ ->
        # Unknown type, log warning and default to allow
        Logger.warning("BLACKLIST rule has unknown type: #{params_type}")
        :allow
    end
  end
  
  # Helper: Apply action based on action type
  defp apply_action(action, message) do
    case action do
      "block" -> {:decline, message}
      "flag" -> {:flag, message}
      "allow" -> :allow
      _ -> 
        Logger.warning("Unknown action type: #{action}, defaulting to decline")
        {:decline, message}
    end
  end

  # VELOCITY_COUNT rule: Decline if transaction count exceeds limit within time window
  # Prevents rapid-fire automated activity by counting ALL transaction attempts (all types, all response codes)
  # Supports scope: "merchant" (all terminals), "terminal" (specific terminal), "merchant+terminal"
  defp evaluate_velocity_count(rule, context) do
    # Support both new and old parameter names for backward compatibility
    # New: "count", "window_seconds" | Old: "max_count", "time_window_minutes"
    max_count = (get_rule_param(rule, "count", nil) || get_rule_param(rule, "max_count", 5)) |> parse_count_value()
    window_seconds = 
      case get_rule_param(rule, "window_seconds", nil) do
        nil -> (get_rule_param(rule, "time_window_minutes", 1) |> parse_count_value()) * 60
        val -> parse_count_value(val)
      end
    scope = get_rule_param(rule, "scope", "terminal") |> String.downcase()
    
    Logger.info("VELOCITY_COUNT check - Scope: #{scope}, Max: #{max_count} in #{window_seconds}s, Merchant: #{context.merchant_id}, Terminal: #{context.terminal_id}")
    
    # Calculate time threshold
    threshold_time = DateTime.add(context.timestamp, -window_seconds, :second)
    Logger.info("VELOCITY_COUNT threshold_time: #{threshold_time}")
    
    # Count transactions based on scope (counts ALL transaction types and response codes)
    past_transaction_count = case scope do
      "merchant" -> 
        count_velocity_transactions_by_merchant(context.merchant_id, threshold_time)
      
      "terminal" -> 
        count_velocity_transactions_by_terminal(context.terminal_id, threshold_time)
      
      "merchant+terminal" -> 
        # Both merchant AND terminal (essentially same as terminal since terminal belongs to one merchant)
        count_velocity_transactions_by_merchant_terminal(context.merchant_id, context.terminal_id, threshold_time)
      
      _ -> 
        # Default to terminal scope
        Logger.warning("Unknown scope '#{scope}' for VELOCITY_COUNT, defaulting to terminal")
        count_velocity_transactions_by_terminal(context.terminal_id, threshold_time)
    end
    
    # Include current transaction in count
    total_count = past_transaction_count + 1
    
    Logger.info("VELOCITY_COUNT - Found #{past_transaction_count} past transactions + 1 current = #{total_count} total in #{window_seconds}s at #{scope} level")
    
    if total_count > max_count do
      {:decline, "Velocity limit exceeded (#{total_count}/#{max_count} in #{window_seconds}s at #{scope} level)"}
    else
      :allow
    end
  rescue
    e ->
      Logger.error("VELOCITY_COUNT check failed: #{inspect(e)}")
      :allow
  end

  # Query database to count recent transactions for a terminal
  # Counts only successful SALE transactions (response_code = "00", s_txn_type = "SALE")
  # Used by COUNT_LIMIT rule
  defp count_recent_transactions(terminal_id, since_time) do
    # Convert DateTime to naive_datetime for database query
    since_naive = DateTime.to_naive(since_time)
    Logger.info("Counting successful SALE transactions for terminal #{terminal_id} since #{since_naive}")
    
    # Query pos_transaction from shukria_transactions database using Repo
    # Note: pos_transaction table uses created_dateTime instead of inserted_at
    # Using s_tid (seller/merchant terminal ID) instead of b_tid (backend/acquirer terminal ID)
    # Filter for successful SALE transactions only
    transaction_count = 
      from(t in "pos_transaction",
        where: t.s_tid == ^terminal_id 
          and t.created_dateTime >= ^since_naive
          and t.response_code == "00"
          and t.s_txn_type == "SALE",
        select: count(t.id)
      )
      |> Repo.one() || 0
    
    Logger.info("count_recent_transactions result: #{transaction_count} successful SALE transactions (from shukria_transactions)")
    transaction_count
  end

  # Query database to count ALL transaction attempts by terminal (for VELOCITY_COUNT)
  # Counts ALL transaction types and ALL response codes to detect rapid-fire activity
  defp count_velocity_transactions_by_terminal(terminal_id, since_time) do
    since_naive = DateTime.to_naive(since_time)
    Logger.info("Counting ALL velocity transactions for terminal #{terminal_id} since #{since_naive}")
    
    transaction_count = 
      from(t in "pos_transaction",
        where: t.s_tid == ^terminal_id and t.created_dateTime >= ^since_naive,
        # NO filters on response_code or s_txn_type - count ALL attempts
        select: count(t.id)
      )
      |> Repo.one() || 0
    
    Logger.info("count_velocity_transactions_by_terminal result: #{transaction_count} transactions")
    transaction_count
  end

  # Query database to count ALL transaction attempts by merchant (all terminals)
  defp count_velocity_transactions_by_merchant(merchant_id, since_time) do
    since_naive = DateTime.to_naive(since_time)
    Logger.info("Counting ALL velocity transactions for merchant #{merchant_id} since #{since_naive}")
    
    transaction_count = 
      from(t in "pos_transaction",
        where: t.s_mid == ^merchant_id and t.created_dateTime >= ^since_naive,
        # NO filters - count ALL attempts across ALL terminals of this merchant
        select: count(t.id)
      )
      |> Repo.one() || 0
    
    Logger.info("count_velocity_transactions_by_merchant result: #{transaction_count} transactions")
    transaction_count
  end

  # Query database to count ALL transaction attempts by merchant+terminal
  defp count_velocity_transactions_by_merchant_terminal(merchant_id, terminal_id, since_time) do
    since_naive = DateTime.to_naive(since_time)
    Logger.info("Counting ALL velocity transactions for merchant #{merchant_id} + terminal #{terminal_id} since #{since_naive}")
    
    transaction_count = 
      from(t in "pos_transaction",
        where: t.s_mid == ^merchant_id 
          and t.s_tid == ^terminal_id 
          and t.created_dateTime >= ^since_naive,
        # NO filters - count ALL attempts
        select: count(t.id)
      )
      |> Repo.one() || 0
    
    Logger.info("count_velocity_transactions_by_merchant_terminal result: #{transaction_count} transactions")
    transaction_count
  end

  # VELOCITY_AMOUNT rule: Decline if total transaction amount exceeds limit within time window
  defp evaluate_velocity_amount(rule, context) do
    max_amount = get_rule_param(rule, "max_amount", 999999.0) |> parse_amount()
    time_window_minutes = get_rule_param(rule, "time_window_minutes", 10)
    
    Logger.info("VELOCITY_AMOUNT check - Terminal: #{context.terminal_id}, Max: #{max_amount} in #{time_window_minutes} minutes, Transaction timestamp: #{context.timestamp}")
    
    # Calculate time threshold (X minutes ago)
    threshold_time = DateTime.add(context.timestamp, -time_window_minutes * 60, :second)
    Logger.info("VELOCITY_AMOUNT threshold_time: #{threshold_time}")
    
    # Query transaction history for this terminal in the time window
    total_amount = sum_recent_transaction_amounts(context.terminal_id, threshold_time)
    Logger.info("VELOCITY_AMOUNT - Found #{total_amount} AED total in last #{time_window_minutes} minutes for terminal #{context.terminal_id} (threshold: #{threshold_time})")
    
    if total_amount >= max_amount do
      formatted_total = :erlang.float_to_binary(total_amount, decimals: 2)
      formatted_max = :erlang.float_to_binary(max_amount, decimals: 2)
      {:decline, "Transaction amount limit exceeded (#{formatted_total}/#{formatted_max} AED in #{time_window_minutes} minutes)"}
    else
      :allow
    end
  rescue
    e ->
      Logger.error("VELOCITY_AMOUNT check failed: #{inspect(e)}")
      :allow
  end

  # Query database to sum recent transaction amounts for a terminal
  defp sum_recent_transaction_amounts(terminal_id, since_time) do
    # Convert DateTime to naive_datetime for database query
    since_naive = DateTime.to_naive(since_time)
    Logger.info("Summing transaction amounts for terminal #{terminal_id} since #{since_naive}")
    
    # Query pos_transaction from shukria_transactions database using Repo
    # Note: pos_transaction table uses created_dateTime instead of inserted_at
    # Using s_tid (seller/merchant terminal ID) instead of b_tid (backend/acquirer terminal ID)
    total_amount = 
      from(t in "pos_transaction",
        where: t.s_tid == ^terminal_id and t.created_dateTime >= ^since_naive,
        select: sum(t.total_amount)
      )
      |> Repo.one()
    
    # Handle NULL case when no transactions found
    total = case total_amount do
      nil -> 0.0
      amount when is_number(amount) -> amount * 1.0
      %Decimal{} = decimal -> Decimal.to_float(decimal)
      _ -> 0.0
    end
    
    Logger.info("sum_recent_transaction_amounts result: #{total} (from shukria_transactions)")
    total
  end

  # Query database to sum daily transaction amounts with filters for response_code and currency
  defp sum_daily_transaction_amounts(terminal_id, since_time, include_declines, currency) do
    # Convert DateTime to naive_datetime for database query
    since_naive = DateTime.to_naive(since_time)
    Logger.info("Summing daily transaction amounts for terminal #{terminal_id} since #{since_naive}, include_declines: #{include_declines}, currency: #{inspect(currency)}")
    
    # Build base query
    # Using s_tid (seller/merchant terminal ID) instead of b_tid (backend/acquirer terminal ID)
    query = from(t in "pos_transaction",
      where: t.s_tid == ^terminal_id and t.created_dateTime >= ^since_naive
    )
    
    # Add response_code filter if include_declines is false
    query = if include_declines do
      query
    else
      # Only include successful transactions (response_code = "00")
      from(t in query, where: t.response_code == "00")
    end
    
    # Add currency filter if specified
    query = if is_nil(currency) or currency == "" do
      query
    else
      from(t in query, where: t.currency_code == ^currency)
    end
    
    # Execute sum query
    total_amount = 
      from(t in query, select: sum(t.total_amount))
      |> Repo.one()
    
    # Handle NULL case when no transactions found
    total = case total_amount do
      nil -> 0.0
      amount when is_number(amount) -> amount * 1.0
      %Decimal{} = decimal -> Decimal.to_float(decimal)
      _ -> 0.0
    end
    
    Logger.info("sum_daily_transaction_amounts result: #{total} (from shukria_transactions)")
    total
  end

  # VELOCITY rules (WEEKLY_TOTAL, DAILY_LIMIT, etc.): Check transaction velocity
  # Note: This is a simplified implementation. Full implementation would query
  # transaction history from database to check actual velocity.
  defp evaluate_velocity(_rule, _context) do
    # For now, we'll allow all transactions and log a warning
    # A full implementation would:
    # 1. Query transaction history for the time period
    # 2. Sum amounts or count transactions
    # 3. Compare against limits in rule.params
    
    # This would require access to transaction history database
    :allow
  end

  # WEEKLY_TOTAL rule: Decline if total transaction amount for the current week exceeds limit
  # Week starts on Monday (ISO 8601 standard)
  defp evaluate_weekly_total(rule, context) do
    max_total_amount = get_rule_param(rule, "max_total_amount", 999_999.0) |> parse_amount()
    include_declines = get_rule_param(rule, "include_declines", false)
    currency = get_rule_param(rule, "currency", nil)

    Logger.info("WEEKLY_TOTAL check - Terminal: #{context.terminal_id}, Max: #{max_total_amount}, Include Declines: #{include_declines}, Currency: #{inspect(currency)}")

    now = context.timestamp
    # Calculate start of current week (Monday at 00:00:00 UTC)
    current_date = DateTime.to_date(now)
    day_of_week = Date.day_of_week(current_date)  # 1 = Monday, 7 = Sunday
    days_since_monday = day_of_week - 1
    start_of_week_date = Date.add(current_date, -days_since_monday)
    start_of_week = DateTime.new!(start_of_week_date, ~T[00:00:00], "Etc/UTC")

    Logger.info("WEEKLY_TOTAL - Week starts on #{start_of_week_date} (Monday)")

    total_amount = sum_daily_transaction_amounts(context.terminal_id, start_of_week, include_declines, currency)
    Logger.info("WEEKLY_TOTAL - Found #{total_amount} total this week for terminal #{context.terminal_id}")

    # Format amounts for comparison logging
    formatted_total = :erlang.float_to_binary(total_amount, decimals: 2)
    formatted_limit = :erlang.float_to_binary(max_total_amount, decimals: 2)
    Logger.info("WEEKLY_TOTAL - Transaction amount already completed this week: #{formatted_total}/#{formatted_limit}")

    if total_amount >= max_total_amount do
      {:decline, "Weekly transaction total limit exceeded (#{formatted_total}/#{formatted_limit})"}
    else
      :allow
    end
  rescue
    e ->
      Logger.error("WEEKLY_TOTAL check failed: #{inspect(e)}")
      :allow
  end

  # DAILY_TOTAL rule: Decline if total transaction amount for the current day exceeds limit
  defp evaluate_daily_total(rule, context) do
    max_total_amount = get_rule_param(rule, "max_total_amount", 999_999.0) |> parse_amount()
    include_declines = get_rule_param(rule, "include_declines", false)
    currency = get_rule_param(rule, "currency", nil)

    Logger.info("DAILY_TOTAL check - Terminal: #{context.terminal_id}, Max: #{max_total_amount}, Include Declines: #{include_declines}, Currency: #{inspect(currency)}")

    now = context.timestamp
    # Build start of the current day at midnight UTC
    start_of_day = DateTime.new!(DateTime.to_date(now), ~T[00:00:00], "Etc/UTC")

    total_amount = sum_daily_transaction_amounts(context.terminal_id, start_of_day, include_declines, currency)
    Logger.info("DAILY_TOTAL - Found #{total_amount} total today for terminal #{context.terminal_id}")

    # Format amounts for comparison logging
    formatted_total = :erlang.float_to_binary(total_amount, decimals: 2)
    formatted_limit = :erlang.float_to_binary(max_total_amount, decimals: 2)
    Logger.info("DAILY_TOTAL - Transaction amount already completed today: #{formatted_total}/#{formatted_limit}")

    if total_amount >= max_total_amount do
      {:decline, "Daily transaction total limit exceeded (#{formatted_total}/#{formatted_limit})"}
    else
      :allow
    end
  rescue
    e ->
      Logger.error("DAILY_TOTAL check failed: #{inspect(e)}")
      :allow
  end

  # MONTHLY_TOTAL rule: Decline if total transaction amount for the current month exceeds limit
  defp evaluate_monthly_total(rule, context) do
    max_total_amount = get_rule_param(rule, "max_total_amount", 999_999.0) |> parse_amount()
    include_declines = get_rule_param(rule, "include_declines", false)
    currency = get_rule_param(rule, "currency", nil)

    Logger.info("MONTHLY_TOTAL check - Terminal: #{context.terminal_id}, Max: #{max_total_amount}, Include Declines: #{include_declines}, Currency: #{inspect(currency)}")

    now = context.timestamp
    # Build start of the current calendar month at midnight UTC
    start_of_month = DateTime.new!(Date.new!(now.year, now.month, 1), ~T[00:00:00], "Etc/UTC")
    start_of_month_date = DateTime.to_date(start_of_month)

    Logger.info("MONTHLY_TOTAL - Month starts on #{start_of_month_date} (1st day of month)")

    total_amount = sum_daily_transaction_amounts(context.terminal_id, start_of_month, include_declines, currency)
    Logger.info("MONTHLY_TOTAL - Found #{total_amount} total this month for terminal #{context.terminal_id}")

    # Format amounts for comparison logging
    formatted_total = :erlang.float_to_binary(total_amount, decimals: 2)
    formatted_limit = :erlang.float_to_binary(max_total_amount, decimals: 2)
    Logger.info("MONTHLY_TOTAL - Transaction amount already completed this month: #{formatted_total}/#{formatted_limit}")

    if total_amount >= max_total_amount do
      {:decline, "Monthly transaction total limit exceeded (#{formatted_total}/#{formatted_limit})"}
    else
      :allow
    end
  rescue
    e ->
      Logger.error("MONTHLY_TOTAL check failed: #{inspect(e)}")
      :allow
  end

  # COUNT_LIMIT rule: Decline if transaction count exceeds limit within specified period
  # Counts successful SALE transactions (response_code = "00") including the current transaction
  # Supports flexible period formats: "24h", "48h", "7d", "30d" or named periods: "daily", "weekly", "monthly"
  defp evaluate_count_limit(rule, context) do
    max_count = get_rule_param(rule, "max_count", 999) |> parse_count_value()
    period = get_rule_param(rule, "period", "24h")

    Logger.info("COUNT_LIMIT check - Terminal: #{context.terminal_id}, Max: #{max_count}, Period: #{period}")

    now = context.timestamp
    threshold_time = calculate_threshold_time(period, now)
    
    # Count recent successful SALE transactions (excludes current transaction)
    past_transaction_count = count_recent_transactions(context.terminal_id, threshold_time)
    
    # Include current transaction in the count
    total_count = past_transaction_count + 1
    
    Logger.info("COUNT_LIMIT - Found #{past_transaction_count} past SALE transactions + 1 current = #{total_count} total in #{period} period for terminal #{context.terminal_id}")

    if total_count > max_count do
      {:decline, "Transaction count limit exceeded (#{total_count}/#{max_count} in #{period} period)"}
    else
      :allow
    end
  rescue
    e ->
      Logger.error("COUNT_LIMIT check failed: #{inspect(e)}")
      :allow
  end

  # REFUND_POLICY rule: Enforce restrictions on refund transactions
  # Validates: refund allowed flag, max amount, refund window, cumulative refunds
  # Params: {"refund_allowed": true/false, "max_refund_amount": "3000", "refund_window_days": "1"}
  defp evaluate_refund_policy(rule, context) do
    # Check if this is a refund transaction (case-insensitive, matches "REFUND", "SALE REFUND", etc.)
    is_refund = context.type && String.downcase(to_string(context.type)) =~ ~r/refund/
    
    if not is_refund do
      # Rule only applies to refund transactions
      :allow
    else
      # Get rule parameters
      refund_allowed = get_rule_param(rule, "refund_allowed", true)
      max_refund_amount = get_rule_param(rule, "max_refund_amount", 999_999.0) |> parse_amount()
      refund_window_days = get_rule_param(rule, "refund_window_days", 30) |> parse_integer()
      
      Logger.info("Evaluating REFUND_POLICY: allowed=#{refund_allowed}, max_amount=#{max_refund_amount}, window=#{refund_window_days} days")
      
      cond do
        not refund_allowed ->
          {:decline, "Refund transactions are not allowed"}
        
        context.amount > max_refund_amount ->
          {:decline, "Refund amount #{:erlang.float_to_binary(context.amount, decimals: 2)} exceeds maximum refund limit #{:erlang.float_to_binary(max_refund_amount, decimals: 2)}"}
        
        true ->
          # Find original SALE transaction to validate against
          case find_original_sale_transaction(context.terminal_id, context.merchant_id, context) do
            nil ->
              Logger.warning("Original SALE transaction not found for refund validation")
              {:decline, "Original transaction not found for refund"}
            
            original_txn ->
              Logger.info("Found original transaction: ID=#{original_txn.id}, amount=#{original_txn.total_amount}, date=#{original_txn.created_dateTime}")
              
              # Calculate days since original transaction
              days_since = calculate_days_since_transaction(original_txn.created_dateTime, context.timestamp)
              
              cond do
                days_since > refund_window_days ->
                  {:decline, "Refund window of #{refund_window_days} days has expired (#{days_since} days since original transaction)"}
                
                context.amount > Decimal.to_float(original_txn.total_amount) ->
                  {:decline, "Refund amount exceeds original transaction amount #{Decimal.to_string(original_txn.total_amount)}"}
                
                true ->
                  # Check cumulative refunds to prevent over-refunding
                  cumulative_refunds = sum_previous_refunds_for_transaction(
                    context.terminal_id, 
                    context.merchant_id, 
                    original_txn
                  )
                  
                  total_refund_amount = context.amount + cumulative_refunds
                  original_amount = Decimal.to_float(original_txn.total_amount)
                  
                  if total_refund_amount > original_amount do
                    {:decline, "Cumulative refund amount #{:erlang.float_to_binary(total_refund_amount, decimals: 2)} exceeds original transaction amount #{:erlang.float_to_binary(original_amount, decimals: 2)}"}
                  else
                    Logger.info("Refund allowed: amount=#{context.amount}, cumulative=#{cumulative_refunds}, original=#{original_amount}")
                    :allow
                  end
              end
          end
      end
    end
  rescue
    e ->
      Logger.error("REFUND_POLICY evaluation failed: #{inspect(e)}")
      :allow
  end

  # DUPLICATE_DETECTION rule: Decline if a duplicate transaction is detected within a time window
  # Supports flexible matching on multiple keys: stan, amount, rrn
  # Rule params: {"keys": ["stan", "amount"], "dedupe_window_seconds": "60"}
  defp evaluate_duplicate_detection(rule, context) do
    # Parse dedupe window (supports both parameter names for backwards compatibility)
    dedupe_window_seconds = 
      (get_rule_param(rule, "dedupe_window_seconds", nil) ||
       get_rule_param(rule, "time_window_seconds", 60))
      |> parse_count_value()
    
    # Parse keys to match on (default to ["amount"] if not specified)
    keys = get_rule_param(rule, "keys", ["amount"])
    keys_list = case keys do
      list when is_list(list) -> list
      str when is_binary(str) -> String.split(str, ",") |> Enum.map(&String.trim/1)
      _ -> ["amount"]
    end

    Logger.info("DUPLICATE_DETECTION check - Terminal: #{context.terminal_id}, Keys: #{inspect(keys_list)}, Window: #{dedupe_window_seconds}s")

    threshold_time = DateTime.add(context.timestamp, -dedupe_window_seconds, :second)

    # Build match fields map from context based on specified keys
    match_fields = build_match_fields(keys_list, context)
    
    if map_size(match_fields) == 0 do
      Logger.warning("DUPLICATE_DETECTION - No valid match fields found, allowing transaction")
      :allow
    else
      duplicate_count = count_duplicate_transactions(context.terminal_id, match_fields, threshold_time)

      Logger.info("DUPLICATE_DETECTION - Found #{duplicate_count} duplicate transactions matching ANY of #{inspect(match_fields)}")

      if duplicate_count > 0 do
        match_summary = Enum.map_join(match_fields, " OR ", fn {k, v} -> "#{k}=#{v}" end)
        {:decline, "Duplicate transaction detected (matching: #{match_summary} within #{dedupe_window_seconds}s)"}
      else
        :allow
      end
    end
  rescue
    e ->
      Logger.error("DUPLICATE_DETECTION check failed: #{inspect(e)}")
      :allow
  end

  # MCC_RESTRICTION rule: Restrict transactions based on Merchant Category Code
  defp evaluate_mcc_restriction(rule, context) do
    allowed_mccs = get_rule_param(rule, "allowed_mccs", [])
    blocked_mccs = get_rule_param(rule, "blocked_mccs", [])
    mcc = context.mcc

    cond do
      is_nil(mcc) ->
        :allow

      length(blocked_mccs) > 0 and mcc in blocked_mccs ->
        {:decline, "Merchant Category Code #{mcc} is restricted"}

      length(allowed_mccs) > 0 and mcc not in allowed_mccs ->
        {:decline, "Merchant Category Code #{mcc} is not in the allowed list"}

      true ->
        :allow
    end
  end

  # BIN_LIMITS rule: Restrict transactions based on card BIN prefix with velocity checks
  # Params: {"bin_prefix": "4166", "max_amount": "5000", "max_count": "2", "period": "24h"}
  # Checks if SALE transactions for cards with this BIN prefix exceed limits within time period
  # Scope: merchant, terminal, or global
  defp evaluate_bin_limits(rule, context) do
    bin_prefix = get_rule_param(rule, "bin_prefix", nil)
    max_amount = get_rule_param(rule, "max_amount", nil) |> parse_amount_or_nil()
    max_count = get_rule_param(rule, "max_count", nil) |> parse_count_value_or_nil()
    period = get_rule_param(rule, "period", "24h")
    
    Logger.info("BIN_LIMITS check - BIN Prefix: #{bin_prefix}, Max Amount: #{inspect(max_amount)}, Max Count: #{inspect(max_count)}, Period: #{period}")
    
    # Validate we have bin_prefix and at least one limit
    cond do
      is_nil(bin_prefix) or bin_prefix == "" ->
        Logger.warning("BIN_LIMITS rule missing bin_prefix parameter")
        :allow
      
      is_nil(max_amount) and is_nil(max_count) ->
        Logger.warning("BIN_LIMITS rule missing both max_amount and max_count parameters")
        :allow
      
      is_nil(context.bin) or context.bin == "" ->
        Logger.warning("BIN_LIMITS check skipped - no BIN in context")
        :allow
      
      true ->
        # Use BIN directly from context
        Logger.info("BIN_LIMITS check - Transaction BIN: #{context.bin}, Rule BIN Prefix: #{bin_prefix}")
        
        # Check if this rule applies to the current transaction BIN
        if not String.starts_with?(context.bin, bin_prefix) do
          Logger.info("BIN #{context.bin} does not match rule prefix #{bin_prefix} - rule does not apply")
          :allow
        else
          # BIN matches - proceed with limit checks
          bin_length = String.length(bin_prefix)
          
          # Calculate time threshold
          threshold_time = calculate_threshold_time(period, context.timestamp)
          threshold_naive = DateTime.to_naive(threshold_time)
          
          Logger.info("Checking BIN #{bin_prefix} transactions since #{threshold_naive}")
          
          # Query historical SALE transactions with this BIN prefix
          # Build base query
          base_query = from(t in "pos_transaction",
            where: t.created_dateTime >= ^threshold_naive
              and t.response_code == "00"
              and t.s_txn_type == "SALE"
              and fragment("LEFT(?, ?) = ?", t.masked_card_no, ^bin_length, ^bin_prefix),
            select: %{
              count: count(t.id),
              total_amount: coalesce(sum(t.total_amount), 0)
            }
          )
          
          # Apply scope filtering
          query = case rule.scope do
            :terminal ->
              Logger.info("Applying terminal scope: #{context.terminal_id}")
              from(t in base_query,
                where: t.s_tid == ^context.terminal_id
              )
            
            :merchant ->
              Logger.info("Applying merchant scope: #{context.merchant_id}")
              from(t in base_query,
                where: t.s_mid == ^context.merchant_id
              )
            
            :global ->
              Logger.info("Applying global scope")
              base_query
            
            _ ->
              Logger.warning("Unknown scope: #{rule.scope}, using merchant scope")
              from(t in base_query,
                where: t.s_mid == ^context.merchant_id
              )
          end
          
          # Execute query
          result = Repo.one(query)
          
          # Convert result values (handles both regular numbers and Decimal types)
          historical_count = if result do
            count_val = Map.get(result, :count) || Map.get(result, "count") || 0
            if is_integer(count_val), do: count_val, else: 0
          else
            0
          end
          
          historical_amount = if result do
            amount_val = Map.get(result, :total_amount) || Map.get(result, "total_amount") || 0
            convert_to_float(amount_val)
          else
            0.0
          end
          
          # Include current transaction in the calculation
          total_count = historical_count + 1
          total_amount = historical_amount + context.amount
          
          Logger.info("BIN_LIMITS result - Historical: #{historical_count} txns, #{historical_amount} AED | With Current: #{total_count} txns, #{total_amount} AED")
          
          # Check limits - decline if EITHER exceeds
          cond do
            not is_nil(max_count) and total_count > max_count ->
              {:decline, "BIN #{bin_prefix} transaction count limit exceeded (#{total_count}/#{max_count} in #{period})"}
            
            not is_nil(max_amount) and total_amount > max_amount ->
              formatted_total = :erlang.float_to_binary(total_amount, decimals: 2)
              formatted_max = :erlang.float_to_binary(max_amount, decimals: 2)
              {:decline, "BIN #{bin_prefix} amount limit exceeded (#{formatted_total}/#{formatted_max} AED in #{period})"}
            
            true ->
              Logger.info("BIN_LIMITS check passed")
              :allow
          end
        end
    end
  end

  # TXN_TYPE_CONTROL rule: Control which transaction types are allowed
  defp evaluate_txn_type_control(rule, context) do
    allowed_types = get_rule_param(rule, "allowed_types", [])
    blocked_types = get_rule_param(rule, "blocked_types", [])

    cond do
      length(blocked_types) > 0 and context.type in blocked_types ->
        {:decline, "Transaction type #{context.type} is not allowed"}

      length(allowed_types) > 0 and context.type not in allowed_types ->
        {:decline, "Transaction type #{context.type} is not permitted"}

      true ->
        :allow
    end
  end

  # GEO_RESTRICTION rule: Restrict transactions based on geographic location
  defp evaluate_geo_restriction(rule, context) do
    allowed_countries = get_rule_param(rule, "allowed_countries", [])
    blocked_countries = get_rule_param(rule, "blocked_countries", [])
    country = context.country

    cond do
      is_nil(country) ->
        :allow

      length(blocked_countries) > 0 and country in blocked_countries ->
        {:decline, "Transactions from country #{country} are restricted"}

      length(allowed_countries) > 0 and country not in allowed_countries ->
        {:decline, "Transactions from country #{country} are not permitted"}

      true ->
        :allow
    end
  end

  # SOFT_DECLINE rule: Flag transaction for review instead of a hard decline
  defp evaluate_soft_decline(rule, context) do
    threshold_amount = get_rule_param(rule, "threshold_amount", 999_999.0) |> parse_amount()

    if context.amount > threshold_amount do
      {:flag,
       "Transaction amount #{context.amount} exceeds soft decline threshold #{threshold_amount}"}
    else
      :allow
    end
  end

  # CUSTOM_SCRIPT rule: Evaluate a configurable set of field/operator/value conditions
  defp evaluate_custom_script(rule, context) do
    conditions = get_rule_param(rule, "conditions", [])
    action = get_rule_param(rule, "action", "decline")
    message = get_rule_param(rule, "message", "Transaction declined by custom rule")

    all_match = Enum.all?(conditions, &evaluate_custom_condition(&1, context))

    if all_match and length(conditions) > 0 do
      case action do
        "flag" -> {:flag, message}
        _ -> {:decline, message}
      end
    else
      :allow
    end
  end

  defp evaluate_custom_condition(
         %{"field" => field, "operator" => op, "value" => value},
         context
       ) do
    field_value =
      case field do
        "amount" -> context.amount
        "currency" -> context.currency
        "type" -> context.type
        "mcc" -> context.mcc
        "country" -> context.country
        "bin" -> context.bin
        _ -> nil
      end

    case op do
      "eq" -> 
        if field in ["amount"], do: field_value == parse_amount(value), else: field_value == value
      "ne" -> 
        if field in ["amount"], do: field_value != parse_amount(value), else: field_value != value
      "gt" when is_number(field_value) -> field_value > parse_amount(value)
      "lt" when is_number(field_value) -> field_value < parse_amount(value)
      "gte" when is_number(field_value) -> field_value >= parse_amount(value)
      "lte" when is_number(field_value) -> field_value <= parse_amount(value)
      "in" when is_list(value) -> field_value in value
      "not_in" when is_list(value) -> field_value not in value
      _ ->
        Logger.warning("CUSTOM_SCRIPT: unknown operator #{inspect(op)} for field #{inspect(field)} with value #{inspect(field_value)}")
        false
    end
  end

  defp evaluate_custom_condition(_, _), do: false

  # REFUND_VELOCITY rule: Decline if the refund count exceeds the limit within a time window
  # Limits rapid refund attempts to prevent abuse
  # Params: {"max_refund_count": "3", "period": "24h"}
  defp evaluate_refund_velocity(rule, context) do
    # Get rule parameters (matching database field names)
    max_refund_count = get_rule_param(rule, "max_refund_count", "10") |> parse_integer()
    period = get_rule_param(rule, "period", "24h")

    # Check if this is a refund transaction (case-insensitive, matches "REFUND", "SALE REFUND", etc.)
    is_refund = context.type && String.downcase(to_string(context.type)) =~ ~r/refund/

    Logger.info(
      "REFUND_VELOCITY check - Terminal: #{context.terminal_id}, Max: #{max_refund_count} refunds in period #{period}"
    )

    if is_refund do
      # Calculate threshold time using flexible period format (supports "24h", "7d", etc.)
      threshold_time = calculate_threshold_time(period, context.timestamp)
      
      # Count all refund attempts (successful and failed) to detect velocity abuse
      # Query excludes current transaction
      past_refund_count = count_recent_refund_transactions(context.terminal_id, threshold_time)
      
      # Include current transaction in the count
      total_refund_count = past_refund_count + 1

      Logger.info(
        "REFUND_VELOCITY - Found #{past_refund_count} past refunds + 1 current = #{total_refund_count} total in period #{period} for terminal #{context.terminal_id}, max allowed: #{max_refund_count}"
      )

      # Decline if total count exceeds maximum
      if total_refund_count > max_refund_count do
        {:decline,
         "Refund velocity limit exceeded (#{total_refund_count}/#{max_refund_count} allowed in #{period})"}
      else
        :allow
      end
    else
      :allow
    end
  rescue
    e ->
      Logger.error("REFUND_VELOCITY check failed: #{inspect(e)}")
      :allow
  end

  # Helper: Get parameter from rule.params (stored as JSON)
  defp get_rule_param(rule, key, default) do
    case rule.params do
      %{^key => value} -> value
      _ when is_map(rule.params) -> Map.get(rule.params, key, default)
      _ -> default
    end
  end

  # Query database to count recent refund transactions for a terminal
  defp count_recent_refund_transactions(terminal_id, since_time) do
    since_naive = DateTime.to_naive(since_time)
    Logger.info("Counting refund transactions for terminal #{terminal_id} since #{since_naive}")

    refund_count =
      from(t in "pos_transaction",
        where:
          t.s_tid == ^terminal_id and
            t.created_dateTime >= ^since_naive and
            t.s_txn_type in ["REFUND", "CREDIT"],
        select: count(t.id)
      )
      |> Repo.one() || 0

    Logger.info("count_recent_refund_transactions result: #{refund_count}")
    refund_count
  end

  # Find the original SALE transaction for refund validation
  # Matches by terminal_id + merchant_id, and optionally by approval_code or reference_no
  # Returns the most recent successful SALE transaction if specific identifiers not found
  defp find_original_sale_transaction(terminal_id, merchant_id, context) do
    # Try to match by approval_code or reference_no if provided in context
    approval_code = context[:approval_code]
    reference_no = context[:reference_no]
    
    Logger.info("Searching for original SALE transaction: terminal=#{terminal_id}, merchant=#{merchant_id}, approval_code=#{approval_code}, reference_no=#{reference_no}")
    
    # Build query to find successful SALE transaction
    base_query = from(t in "pos_transaction",
      where: t.s_tid == ^terminal_id 
        and t.s_mid == ^merchant_id
        and t.s_txn_type == "SALE"
        and t.response_code == "00",
      order_by: [desc: t.created_dateTime],
      limit: 1,
      select: %{
        id: t.id,
        total_amount: t.total_amount,
        created_dateTime: t.created_dateTime,
        approval_code: t.approval_code,
        reference_no: t.reference_no,
        s_tid_stan: t.s_tid_stan
      }
    )
    
    # If approval_code provided, try exact match first
    if approval_code && approval_code != "" do
      query = from(t in base_query, where: t.approval_code == ^approval_code)
      case Repo.one(query) do
        nil -> 
          Logger.info("No SALE found with approval_code=#{approval_code}, trying without")
          Repo.one(base_query)
        result -> 
          result
      end
    # If reference_no provided, try exact match first  
    else if reference_no && reference_no != "" do
      query = from(t in base_query, where: t.reference_no == ^reference_no)
      case Repo.one(query) do
        nil -> 
          Logger.info("No SALE found with reference_no=#{reference_no}, trying without")
          Repo.one(base_query)
        result -> 
          result
      end
    else
      # No specific identifier, get most recent SALE
      Repo.one(base_query)
    end
    end
  rescue
    e ->
      Logger.error("Error finding original SALE transaction: #{inspect(e)}")
      nil
  end

  # Calculate number of days between original transaction and current timestamp
  defp calculate_days_since_transaction(original_datetime, current_timestamp) do
    # Convert original_datetime to NaiveDateTime if needed
    original_naive = case original_datetime do
      %NaiveDateTime{} -> original_datetime
      dt when is_binary(dt) -> 
        case NaiveDateTime.from_iso8601(dt) do
          {:ok, naive} -> naive
          _ -> NaiveDateTime.utc_now()
        end
      _ -> NaiveDateTime.utc_now()
    end
    
    # Calculate difference in days (absolute value) using Date.diff
    Date.diff(DateTime.to_date(current_timestamp), NaiveDateTime.to_date(original_naive)) |> abs()
  rescue
    e ->
      Logger.error("Error calculating days since transaction: #{inspect(e)}")
      999  # Return large number to trigger window expiry on error
  end

  # Sum all previous refund amounts for a specific original transaction
  # Matches refunds by terminal + merchant + approval_code or reference_no
  # Returns total amount of previous refunds (excluding current transaction)
  defp sum_previous_refunds_for_transaction(terminal_id, merchant_id, original_txn) do
    # Query refunds that occurred after the original transaction
    # Match by terminal, merchant, and approval_code or reference_no
    since_naive = original_txn.created_dateTime
    
    # Use approval_code or reference_no to link refunds to original transaction
    identifier_field = cond do
      original_txn.approval_code && original_txn.approval_code != "" ->
        {:approval_code, original_txn.approval_code}
      
      original_txn.reference_no && original_txn.reference_no != "" ->
        {:reference_no, original_txn.reference_no}
      
      true ->
        # No identifier available, can't reliably track cumulative refunds
        # Return 0.0 to allow refund (single refund check already done)
        Logger.warning("No approval_code or reference_no available for cumulative refund tracking")
        nil
    end
    
    if identifier_field == nil do
      0.0
    else
      {field_name, field_value} = identifier_field
    
      Logger.info("Summing previous refunds: terminal=#{terminal_id}, merchant=#{merchant_id}, #{field_name}=#{field_value}")
      
      # Build query based on which identifier field we're using
      total = case field_name do
        :approval_code ->
          from(t in "pos_transaction",
            where: t.s_tid == ^terminal_id
              and t.s_mid == ^merchant_id
              and t.approval_code == ^field_value
              and t.s_txn_type in ["REFUND", "CREDIT"]
              and t.response_code == "00"
              and t.created_dateTime >= ^since_naive,
            select: sum(t.total_amount)
          )
          |> Repo.one()
          
        :reference_no ->
          from(t in "pos_transaction",
            where: t.s_tid == ^terminal_id
              and t.s_mid == ^merchant_id
              and t.reference_no == ^field_value
              and t.s_txn_type in ["REFUND", "CREDIT"]
              and t.response_code == "00"
              and t.created_dateTime >= ^since_naive,
            select: sum(t.total_amount)
          )
          |> Repo.one()
      end
      
      # Convert Decimal to float, handle nil
      result = if total && total != nil do
        Decimal.to_float(total)
      else
        0.0
      end
      
      Logger.info("Previous refunds total: #{result}")
      result
    end
  rescue
    e ->
      Logger.error("Error summing previous refunds: #{inspect(e)}")
      0.0  # Return 0 on error to allow current refund (single amount check already done)
  end

  # Helper: Parse integer from string or return default
  defp parse_integer(value) when is_binary(value) do
    case Integer.parse(value) do
      {int_val, _} -> int_val
      :error -> 0
    end
  end
  defp parse_integer(value) when is_integer(value), do: value
  defp parse_integer(_), do: 0

  # Build match fields map from context based on specified keys
  defp build_match_fields(keys, context) do
    Enum.reduce(keys, %{}, fn key, acc ->
      case String.downcase(key) do
        "stan" ->
          if context.stan, do: Map.put(acc, :stan, context.stan), else: acc
        "amount" ->
          if context.amount, do: Map.put(acc, :amount, context.amount), else: acc
        "rrn" ->
          if context.rrn, do: Map.put(acc, :rrn, context.rrn), else: acc
        _ ->
          Logger.warning("Unknown duplicate detection key: #{key}")
          acc
      end
    end)
  end

  # Query database to count recent transactions matching specified fields (for duplicate detection)
  # Supports dynamic matching on: stan, amount, rrn
  # Uses OR logic: blocks if ANY of the specified fields match
  defp count_duplicate_transactions(terminal_id, match_fields, since_time) do
    since_naive = DateTime.to_naive(since_time)

    Logger.info(
      "Checking for duplicate transactions for terminal #{terminal_id} matching ANY of #{inspect(match_fields)} since #{since_naive}"
    )

    # Build OR conditions dynamically
    or_conditions = Enum.map(match_fields, fn {field, value} ->
      case field do
        :stan ->
          # Normalize stan: ensure 6 digits with leading zeros
          normalized_stan = String.pad_leading("#{value}", 6, "0")
          dynamic([t], t.s_tid_stan == ^normalized_stan)
        
        :amount ->
          decimal_amount = Decimal.from_float(value)
          dynamic([t], t.total_amount == ^decimal_amount)
        
        :rrn ->
          # RRN stored in reference_no column
          dynamic([t], t.reference_no == ^value)
        
        _ ->
          dynamic([t], false)
      end
    end)

    # Combine all OR conditions
    combined_condition = Enum.reduce(or_conditions, dynamic([t], false), fn condition, acc ->
      dynamic([t], ^acc or ^condition)
    end)

    # Build query with OR logic
    query = from(t in "pos_transaction",
      where: t.s_tid == ^terminal_id and t.created_dateTime >= ^since_naive,
      where: ^combined_condition,
      select: count(t.id)
    )

    duplicate_count = Repo.one(query) || 0

    Logger.info("count_duplicate_transactions result: #{duplicate_count} (using OR logic)")
    duplicate_count
  end

  # Helper: Parse time string "HH:MM" to Time struct
  defp parse_time(time_str) when is_binary(time_str) do
    case String.split(time_str, ":") do
      [hour, minute] ->
        with {h, _} <- Integer.parse(hour),
             {m, _} <- Integer.parse(minute),
             {:ok, time} <- Time.new(h, m, 0) do
          {:ok, time}
        else
          _ -> {:error, :invalid_time}
        end
      
      _ -> {:error, :invalid_time}
    end
  end
  defp parse_time(_), do: {:error, :invalid_time}

  # Helper: Calculate threshold time from flexible period format
  # Supports: "24h", "48h" (hours), "7d", "30d" (days), "daily", "weekly", "monthly"
  defp calculate_threshold_time(period_str, now) when is_binary(period_str) do
    cond do
      # Duration format: "24h", "48h", etc.
      String.match?(period_str, ~r/^(\d+)h$/i) ->
        [_, hours] = Regex.run(~r/^(\d+)h$/i, period_str)
        DateTime.add(now, -String.to_integer(hours) * 60 * 60, :second)
      
      # Duration format: "7d", "30d", etc.
      String.match?(period_str, ~r/^(\d+)d$/i) ->
        [_, days] = Regex.run(~r/^(\d+)d$/i, period_str)
        DateTime.add(now, -String.to_integer(days) * 24 * 60 * 60, :second)
      
      # Named periods (backward compatibility)
      period_str == "daily" ->
        DateTime.add(now, -24 * 60 * 60, :second)
      
      period_str == "weekly" ->
        DateTime.add(now, -7 * 24 * 60 * 60, :second)
      
      period_str == "monthly" ->
        DateTime.new!(Date.new!(now.year, now.month, 1), ~T[00:00:00], "Etc/UTC")
      
      true ->
        # Default to 24 hours if format not recognized
        Logger.warning("Unrecognized period format: #{period_str}, defaulting to 24h")
        DateTime.add(now, -24 * 60 * 60, :second)
    end
  end
  defp calculate_threshold_time(_period_str, now), do: DateTime.add(now, -24 * 60 * 60, :second)

  # Helper: Parse count value from string or integer
  defp parse_count_value(value) when is_binary(value) do
    case Integer.parse(value) do
      {int_val, _} -> int_val
      :error -> 999
    end
  end
  defp parse_count_value(value) when is_integer(value), do: value
  defp parse_count_value(_), do: 999

  # Helper: Parse count value from string or integer, returns nil if not present
  defp parse_count_value_or_nil(nil), do: nil
  defp parse_count_value_or_nil(""), do: nil
  defp parse_count_value_or_nil(value) when is_binary(value) do
    case Integer.parse(value) do
      {int_val, _} -> int_val
      :error -> nil
    end
  end
  defp parse_count_value_or_nil(value) when is_integer(value), do: value
  defp parse_count_value_or_nil(_), do: nil

  # Helper: Parse amount value, returns nil if not present
  defp parse_amount_or_nil(nil), do: nil
  defp parse_amount_or_nil(""), do: nil
  defp parse_amount_or_nil(value) when is_number(value), do: value * 1.0
  defp parse_amount_or_nil(value) when is_binary(value) do
    case parse_amount(value) do
      amount when is_number(amount) and amount > 0 -> amount
      _ -> nil
    end
  end
  defp parse_amount_or_nil(_), do: nil

  # Helper: Convert various numeric types to float (handles Decimal from DB queries)
  defp convert_to_float(nil), do: 0.0
  defp convert_to_float(value) when is_float(value), do: value
  defp convert_to_float(value) when is_integer(value), do: value * 1.0
  defp convert_to_float(%Decimal{} = decimal), do: Decimal.to_float(decimal)
  defp convert_to_float(_), do: 0.0

  # Helper: Check if time is within range
  defp time_in_range?(current, start_time, end_time) do
    if Time.compare(start_time, end_time) == :lt do
      # Normal range: start < end (e.g., 09:00 - 17:00)
      Time.compare(current, start_time) in [:gt, :eq] and
      Time.compare(current, end_time) in [:lt, :eq]
    else
      # Wrap-around range: start > end (e.g., 22:00 - 06:00)
      Time.compare(current, start_time) in [:gt, :eq] or
      Time.compare(current, end_time) in [:lt, :eq]
    end
  end

  # Derive transaction type from MTI and Processing Code (ISO 8583 standard)
  # Returns transaction type string or nil if cannot determine
  # MTI (Message Type Indicator) defines message class and function
  # Processing Code defines transaction type (first 2 digits) and account types
  defp derive_transaction_type(nil, _) do
    Logger.debug("derive_transaction_type: MTI is nil")
    nil
  end
  
  defp derive_transaction_type(_, nil) do
    Logger.debug("derive_transaction_type: Processing Code is nil")
    nil
  end
  
  defp derive_transaction_type(mti, processing_code) when is_binary(mti) and is_binary(processing_code) do
    # Extract first 2 digits of processing code for transaction type
    pc_type = String.slice(processing_code, 0, 2)
    Logger.info("derive_transaction_type - MTI: '#{mti}', PC: '#{processing_code}', PC_Type: '#{pc_type}'")
    
    case mti do
      # Settlement messages (MTI 05xx)
      "0500" -> "SETTLEMENT"
      "0510" -> "SETTLEMENT_RESPONSE"
      
      # Network management / Logon messages (MTI 08xx)
      "0800" ->
        case processing_code do
          "990000" -> "LOGON"
          "991000" -> "LOGOFF"
          "992000" -> "ECHO_TEST"
          _ -> "NETWORK_MANAGEMENT"
        end
      
      "0810" -> "NETWORK_MANAGEMENT_RESPONSE"
      
      # Reversal messages (MTI 04xx)
      # VOID is a reversal of a SALE transaction (PC 000000)
      "0400" ->
        case pc_type do
          "00" -> "VOID"           # Void/Reversal of Sale transaction
          _ -> "REVERSAL"          # Reversal of other transaction types
        end
      
      "0420" ->
        case pc_type do
          "00" -> "VOID_ADVICE"    # Void/Reversal advice for Sale
          _ -> "REVERSAL_ADVICE"   # Reversal advice for other transaction types
        end
      
      # Financial request messages (MTI 0200)
      "0200" ->
        case pc_type do
          "00" -> "SALE"           # Purchase/Sale transaction
          "02" -> "VOID"           # Void/Reversal (alternative format)
          "20" -> "REFUND"         # Refund transaction
          "01" -> "CASH_WITHDRAWAL" # Cash withdrawal
          "30" -> "BALANCE_INQUIRY" # Balance inquiry
          "40" -> "PRE_AUTH"        # Pre-authorization
          _ -> nil
        end
      
      # Financial advice messages (MTI 0220)
      "0220" ->
        case pc_type do
          "00" -> "SALE_ADVICE"   # Sale advice
          "20" -> "REFUND_ADVICE" # Refund advice
          "92" -> "SETTLEMENT"    # Settlement (alternative format)
          _ -> nil
        end
      
      # Unknown MTI - cannot determine type
      _ -> 
        Logger.debug("derive_transaction_type: Unknown MTI '#{mti}', returning nil")
        nil
    end
    
    # Log the derived transaction type
    |> tap(fn result ->
      Logger.info("derive_transaction_type result: MTI '#{mti}' + PC '#{processing_code}' => '#{inspect(result)}'")
    end)
  end
  
  defp derive_transaction_type(mti, processing_code) do
    Logger.warning("derive_transaction_type: Invalid types - MTI: #{inspect(mti)}, PC: #{inspect(processing_code)}")
    nil
  end
end