defmodule DaProductAppWeb.SettlementController do
  # Helper to safely parse a decimal from a string or number
  defp parse_safe_decimal(value) when is_binary(value) do
    case Decimal.parse(value) do
      {decimal, ""} -> decimal
      _ -> Decimal.new(0)
    end
  end

  defp parse_safe_decimal(value), do: Decimal.new(value)

  # Helper to safely parse an integer from a string or integer
  defp parse_safe_integer(value) when is_binary(value) do
    case Integer.parse(value) do
      {int, ""} -> int
      _ -> 0
    end
  end

  defp parse_safe_integer(value) when is_integer(value), do: value
  defp parse_safe_integer(_), do: 0

  # Basic implementation for parse_tms_transaction_row/2
  # You should adjust this logic to match your CSV structure
  defp parse_tms_transaction_row(line, headers) do
    Enum.zip(headers, String.split(line, ","))
    |> Enum.into(%{})
  end

  use DaProductAppWeb, :controller
  require Logger
  alias DaProductApp.Repo
  import Ecto.Query, warn: false
  alias DaProductApp.Settlements
  alias DaProductApp.Settlements.EodFileGenerator
  alias DaProductApp.Settlements.TransactionEodGenerator
  alias DaProductApp.Workers.EodFileGenerationWorker
  alias DaProductApp.Settlements.Settlement

  @doc """
  GET /api/v1/merchant/settlements
  Retrieve a paginated list of settlement records.
  """
  def index(conn, params) do
    settlements = Settlements.list_settlements(params)
    total = Settlements.count_settlements(params)

    page =
      case Integer.parse(Map.get(params, "page", "1")) do
        {n, ""} when n > 0 -> n
        _ -> 1
      end

    page_size =
      case Integer.parse(Map.get(params, "page_size", "10")) do
        {n, ""} when n > 0 -> n
        _ -> 10
      end

    response = %{
      total: total,
      page: page,
      page_size: page_size,
      settlements: Enum.map(settlements, &format_settlement_summary/1)
    }

    json(conn, response)
  end

  @doc """
  GET /api/v1/merchant/settlement/:settlement_id
  Returns metadata and summary of a specific settlement.
  """
  def show(conn, %{"settlement_id" => settlement_id}) do
    case Settlements.get_settlement_by_settlement_id(settlement_id) do
      nil ->
        Logger.warning("TMS: Settlement not found: #{settlement_id}")

        conn
        |> put_status(:not_found)
        |> json(%{
          error: "Settlement not found",
          message: "No settlement found with ID: #{settlement_id}"
        })

      settlement ->
        # Build the correct file path using settlement date and participant_id
        date_str = Date.to_iso8601(settlement.date) |> String.replace("-", "")
        participant_id = settlement.participant_id || "1000012345"
        # Try to find any CSV file for this settlement
        csv_dir = "/v1/settlements/settlement/#{participant_id}/#{date_str}"

        csv_files =
          if File.dir?(csv_dir) do
            Path.wildcard(Path.join(csv_dir, "settlement_*.csv"))
          else
            []
          end

        case csv_files do
          [] ->
            conn
            |> put_status(:not_found)
            |> json(%{error: "CSV file not found for settlement: #{settlement.settlement_id}"})

          [csv_file | _] ->
            case match_settlement_status_from_csv_file(settlement, csv_file) do
              {:ok, result} ->
                json(conn, %{result: result, settlement_id: settlement.settlement_id})

              {:error, reason} ->
                conn
                |> put_status(:bad_request)
                |> json(%{
                  error: "Failed to process CSV",
                  reason: inspect(reason),
                  settlement_id: settlement.settlement_id
                })
            end
        end
    end
  end

  @doc """
  GET /api/v1/merchant/settlement/:settlement_id/transactions
  Returns list of transactions for a given settlement.
  """
  def transactions(conn, %{"settlement_id" => settlement_id} = params) do
    case Settlements.get_settlement_by_settlement_id(settlement_id) do
      nil ->
        conn
        |> put_status(:not_found)
        |> json(%{error: "Settlement not found"})

      _settlement ->
        settlement_transactions =
          Settlements.list_settlement_transaction_records(settlement_id, params)

        response = %{
          transactions: Enum.map(settlement_transactions, &format_transaction/1)
        }

        json(conn, response)
    end
  end

  @doc """
  GET /api/v1/merchant/settlement/:settlement_id/download
  Downloads the detailed transaction report in CSV or PDF.
  """
  def download(conn, %{"settlement_id" => settlement_id, "format" => format}) do
    case Settlements.get_settlement_by_settlement_id(settlement_id) do
      nil ->
        conn
        |> put_status(:not_found)
        |> json(%{error: "Settlement not found"})

      settlement ->
        transaction_count = settlement.total_transaction_count || 0

        if transaction_count > 1000 do
          # TODO: Implement background job/cron to generate large settlement reports and provide downloadable link when ready.
          conn
          |> put_status(:accepted)
          |> json(%{
            message:
              "Report is being generated due to large transaction volume. You will receive a downloadable link when it's ready.",
            settlement_id: settlement_id
          })
        else
          settlement_transactions =
            Settlements.list_settlement_transaction_records(settlement_id, %{})

          case format do
            "csv" ->
              csv_content = generate_csv_report(settlement, settlement_transactions)

              conn
              |> put_resp_content_type("text/csv")
              |> put_resp_header(
                "content-disposition",
                "attachment; filename=\"settlement_#{settlement_id}.csv\""
              )
              |> send_resp(200, csv_content)

            "pdf" ->
              # For now, return a simple text response for PDF
              # In a real implementation, you'd use a PDF library
              pdf_content = generate_text_report(settlement, settlement_transactions)

              conn
              |> put_resp_content_type("application/pdf")
              |> put_resp_header(
                "content-disposition",
                "attachment; filename=\"settlement_#{settlement_id}.pdf\""
              )
              |> send_resp(200, pdf_content)

            _ ->
              conn
              |> put_status(:bad_request)
              |> json(%{error: "Invalid format. Use 'csv' or 'pdf'"})
          end
        end
    end
  end

  @doc """
  GET /api/v1/merchant/settlement/:settlement_id/csv
  Downloads the detailed transaction report in CSV format.
  """
  def download_csv(conn, %{"settlement_id" => settlement_id}) do
    download(conn, %{"settlement_id" => settlement_id, "format" => "csv"})
  end

  @doc """
  GET /api/v1/merchant/settlement/:settlement_id/pdf
  Downloads the detailed transaction report in PDF format.
  """
  def download_pdf(conn, %{"settlement_id" => settlement_id}) do
    download(conn, %{"settlement_id" => settlement_id, "format" => "pdf"})
  end

  @doc """
  GET /api/v1/merchant/settlement/summary
  Gives quick stats for the merchant's dashboard.
  """
  def summary(conn, params) do
    merchant_id = Map.get(params, "merchant_id")
    bank_user_id = Map.get(params, "bank_user_id")
    summary = Settlements.get_settlement_summary(merchant_id)

    response = %{
      total_settled: summary.total_settled,
      pending: summary.pending,
      exception_count: summary.exception_count,
      last_settlement_date:
        if(summary.last_settlement_date,
          do: Date.to_iso8601(summary.last_settlement_date),
          else: nil
        )
    }

    json(conn, response)
  end

  @doc """
  POST /api/v1/settlements/settlement/eod/date
  Triggers EOD settlement file generation for a specific date.
  """
  def generate_eod_file(conn, params) do
    date =
      case Map.get(params, "date") do
        nil ->
          Date.utc_today()

        date_str ->
          case Date.from_iso8601(date_str) do
            {:ok, date} -> date
            {:error, _} -> Date.utc_today()
          end
      end

    sequence =
      case Map.get(params, "sequence") do
        nil ->
          1

        seq when is_integer(seq) and seq > 0 ->
          seq

        seq_str when is_binary(seq_str) ->
          case Integer.parse(seq_str) do
            {seq, ""} when seq > 0 -> seq
            _ -> 1
          end

        _ ->
          1
      end

    force = Map.get(params, "force", false)

    case EodFileGenerator.generate_eod_file(date: date, sequence: sequence, force: force) do
      {:ok, file_path} ->
        conn
        |> put_status(:created)
        |> json(%{
          message: "EOD settlement file generated successfully",
          file_path: file_path,
          date: Date.to_iso8601(date),
          sequence: sequence
        })

      {:scheduled, job_id} ->
        conn
        |> put_status(:accepted)
        |> json(%{
          message: "EOD settlement file generation has been queued for background processing",
          job_id: job_id,
          date: Date.to_iso8601(date),
          sequence: sequence,
          scheduled: true
        })

      {:error, :all_processed} ->
        conn
        |> put_status(:conflict)
        |> json(%{
          message: "EOD settlement file for this date has already been processed.",
          date: Date.to_iso8601(date),
          sequence: sequence
        })

      {:error, reason} ->
        conn
        |> put_status(:bad_request)
        |> json(%{
          error: reason,
          date: Date.to_iso8601(date)
        })
    end
  end

  @doc """
  GET /api/v1/settlements/settlement/eod/job/:job_id
  Gets the status of an EOD file generation job.
  """
  def get_job_status(conn, %{"job_id" => job_id}) do
    case Integer.parse(job_id) do
      {job_id_int, ""} ->
        case Oban.Job |> DaProductApp.Repo.get(job_id_int) do
          nil ->
            conn
            |> put_status(:not_found)
            |> json(%{error: "Job not found"})

          job ->
            response = %{
              job_id: job.id,
              state: job.state,
              worker: job.worker,
              args: job.args,
              queue: job.queue,
              inserted_at: job.inserted_at,
              scheduled_at: job.scheduled_at,
              attempted_at: job.attempted_at,
              completed_at: job.completed_at,
              errors: job.errors
            }

            json(conn, response)
        end

      _ ->
        conn
        |> put_status(:bad_request)
        |> json(%{error: "Invalid job ID"})
    end
  end

  @doc """
  GET /api/v1/settlements/settlement/{bankUserId}/{date}/
  Generates and serves settlement files in CSV or JSON format for a specific bank user and date.
  Returns both formats by default, or specific format based on Accept header.
  """
  def generate_merchant_settlement_files(conn, %{
        "bank_user_id" => bank_user_id,
        "date" => date_str
      }) do
    case Date.from_iso8601(date_str) do
      {:ok, date} ->
        format = determine_format_from_headers(conn)
        sequence = Map.get(conn.params, "sequence", 1) |> parse_sequence()

        case TransactionEodGenerator.generate_settlement_files(
               bank_user_id: bank_user_id,
               date: date,
               format: format,
               sequence: sequence
             ) do
          {:ok, %{csv: csv_path, json: json_path}} ->
            # Return both files' metadata
            conn
            |> put_status(:created)
            |> json(%{
              message: "Settlement files generated successfully",
              files: %{
                csv: %{
                  path: csv_path,
                  filename: Path.basename(csv_path),
                  download_url: "/api/v1/settlements/download/#{Path.basename(csv_path)}"
                },
                json: %{
                  path: json_path,
                  filename: Path.basename(json_path),
                  download_url: "/api/v1/settlements/download/#{Path.basename(json_path)}"
                }
              },
              bank_user_id: bank_user_id,
              date: date_str,
              sequence: sequence
            })

          {:ok, %{csv: csv_path}} ->
            # Return CSV file directly
            serve_settlement_file(conn, csv_path, "text/csv")

          {:ok, %{json: json_path}} ->
            # Return JSON file directly
            serve_settlement_file(conn, json_path, "application/json")

          {:error, reason} ->
            conn
            |> put_status(:bad_request)
            |> json(%{
              error: reason,
              bank_user_id: bank_user_id,
              date: date_str
            })
        end

      {:error, _} ->
        conn
        |> put_status(:bad_request)
        |> json(%{error: "Invalid date format. Use YYYY-MM-DD"})
    end
  end

  @doc """
  GET /api/v1/settlements/settlement/{bankUserId}/{date}/csv
  Generates and serves CSV settlement file for a specific bank user and date.
  """
  def generate_merchant_settlement_csv(conn, %{"bank_user_id" => bank_user_id, "date" => date_str}) do
    case Date.from_iso8601(date_str) do
      {:ok, date} ->
        sequence = Map.get(conn.params, "sequence", 1) |> parse_sequence()
        now = DateTime.now!("Asia/Kolkata")
        time_str = now |> Time.to_iso8601() |> String.replace(":", "") |> String.slice(0, 6)
        file_name = "mercury_pay_#{date_str}_#{time_str}_#{sequence}.csv"
        dir_path = Path.join(["priv/static/settlements", bank_user_id, date_str])
        file_path = Path.join([dir_path, file_name])
        File.mkdir_p!(dir_path)

        case TransactionEodGenerator.generate_settlement_files(
               bank_user_id: bank_user_id,
               date: date,
               format: :csv,
               sequence: sequence,
               file_path: file_path
             ) do
          {:ok, %{csv: ^file_path}} ->
            send_download(conn, {:file, file_path},
              filename: Path.basename(file_path),
              content_type: "text/csv"
            )

          {:ok, %{csv: actual_path}} ->
            send_download(conn, {:file, actual_path},
              filename: Path.basename(actual_path),
              content_type: "text/csv"
            )

          {:error, reason} ->
            conn
            |> put_status(:bad_request)
            |> json(%{
              error: reason,
              bank_user_id: bank_user_id,
              date: date_str
            })
        end

      {:error, _} ->
        conn
        |> put_status(:bad_request)
        |> json(%{error: "Invalid date format. Use YYYY-MM-DD"})
    end
  end

  @doc """
  GET /api/v1/settlements/settlement/{bankUserId}/{date}/json
  Generates and serves JSON settlement file for a specific bank user and date.
  """
  def generate_merchant_settlement_json(conn, %{
        "bank_user_id" => bank_user_id,
        "date" => date_str
      }) do
    case Date.from_iso8601(date_str) do
      {:ok, date} ->
        sequence = Map.get(conn.params, "sequence", 1) |> parse_sequence()

        case TransactionEodGenerator.generate_settlement_files(
               bank_user_id: bank_user_id,
               date: date,
               format: :json,
               sequence: sequence
             ) do
          {:ok, %{json: json_path}} ->
            serve_settlement_file(conn, json_path, "application/json")

          {:error, reason} ->
            conn
            |> put_status(:bad_request)
            |> json(%{
              error: reason,
              bank_user_id: bank_user_id,
              date: date_str
            })
        end

      {:error, _} ->
        conn
        |> put_status(:bad_request)
        |> json(%{error: "Invalid date format. Use YYYY-MM-DD"})
    end
  end

  @doc """
  GET /api/v1/settlements/download/:filename
  Downloads a settlement file by merchant_id, date, and filename.
  """
  def download_settlement_file(conn, %{
        "merchant_id" => merchant_id,
        "date" => date,
        "filename" => filename
      }) do
    file_path = Path.join(["priv/static/settlements", merchant_id, date, filename])

    if File.exists?(file_path) do
      send_download(conn, {:file, file_path}, filename: filename)
    else
      conn
      |> put_status(:not_found)
      |> json(%{error: "File not found"})
    end
  end

  @doc """
  GET /api/v1/settlements/settlement/settlement_<participantId>_<settlementCurrency>_<settlementBatchId>_<participantAgreementId>_<seq>.csv
  Parses the specified CSV file in priv/static/settlements/ and runs settlement matching.
  """
  def parse_settlement_csv_by_filename(conn, %{"filename" => filename}) do
    # Use the correct AlipayPlus settlement file directory structure
    # Extract participant_id from filename to build correct path
    case Regex.run(~r/^settlement_([^_]+)_([^_]+)_([^_]+)_([^_]+)_.*\.csv$/, filename) do
      [_, participant_id, currency, batch_id, agreement_id] ->
        # Try to find settlement to get the date
        settlement =
          Repo.one(
            from s in Settlement,
              where:
                s.participant_id == ^participant_id and
                  s.gross_settlement_currency == ^currency and
                  s.settlement_batch_id == ^batch_id and
                  s.participant_agreement_id == ^agreement_id,
              limit: 1
          )

        case settlement do
          nil ->
            conn
            |> put_status(:not_found)
            |> json(%{error: "Settlement not found for filename: #{filename}"})

          settlement ->
            # Build the correct file path using settlement date
            date_str = Date.to_iso8601(settlement.date) |> String.replace("-", "")
            file_path = "/v1/settlements/settlement/#{participant_id}/#{date_str}/#{filename}"

            if File.exists?(file_path) do
              result = match_settlement_status_from_csv_file(settlement, file_path)
              json(conn, %{result: result, settlement_id: settlement.settlement_id})
            else
              conn
              |> put_status(:not_found)
              |> json(%{error: "CSV file not found at #{file_path}"})
            end
        end

      _ ->
        conn
        |> put_status(:bad_request)
        |> json(%{error: "Invalid filename format"})
    end
  end

  # Private helper functions

  defp determine_format_from_headers(conn) do
    accept_header = get_req_header(conn, "accept") |> List.first()

    cond do
      accept_header && String.contains?(accept_header, "application/json") -> :json
      accept_header && String.contains?(accept_header, "text/csv") -> :csv
      # Default to both formats
      true -> :both
    end
  end

  defp parse_sequence(sequence) when is_integer(sequence) and sequence > 0, do: sequence

  defp parse_sequence(sequence_str) when is_binary(sequence_str) do
    case Integer.parse(sequence_str) do
      {seq, ""} when seq > 0 -> seq
      _ -> 1
    end
  end

  defp parse_sequence(_), do: 1

  defp serve_settlement_file(conn, file_path, content_type) do
    case File.read(file_path) do
      {:ok, content} ->
        filename = Path.basename(file_path)

        conn
        |> put_resp_content_type(content_type)
        |> put_resp_header("content-disposition", "attachment; filename=\"#{filename}\"")
        |> send_resp(200, content)

      {:error, reason} ->
        conn
        |> put_status(:internal_server_error)
        |> json(%{error: "Failed to read file: #{reason}"})
    end
  end

  defp format_settlement_summary(settlement) do
    %{
      id: settlement.settlement_id,
      date: Date.to_iso8601(settlement.date),
      amount: settlement.amount,
      status: settlement.status,
      transaction_count: settlement.total_transaction_count,
      bank_user_id: settlement.bank_user_id
    }
  end

  defp format_transaction(transaction) do
    %{
      txn_id: transaction.transaction_id || transaction.processing_id,
      amount: transaction.transaction_amount || transaction.charge_rate,
      status: map_settlement_status(transaction.settlement_status),
      type: map_payment_type(transaction.pay_mode),
      timestamp: format_datetime(transaction.inserted_at)
    }
  end

  defp map_settlement_status("unmatched"), do: "unmatched"
  defp map_settlement_status("settled"), do: "settled"
  defp map_settlement_status(nil), do: "unmatched"
  defp map_settlement_status(status), do: status

  defp map_payment_type(pay_mode) when pay_mode in [nil, ""], do: "QR"
  defp map_payment_type(pay_mode) when pay_mode in ["QR", "qr", "QR_AANI"], do: "QR"
  defp map_payment_type(pay_mode) when pay_mode in ["card", "Card", "CARD"], do: "Card"
  defp map_payment_type(pay_mode) when pay_mode in ["upi", "UPI"], do: "UPI"
  defp map_payment_type(pay_mode), do: pay_mode

  defp format_datetime(nil), do: nil
  defp format_datetime(%DateTime{} = datetime), do: DateTime.to_iso8601(datetime)

  defp format_datetime(%NaiveDateTime{} = naive_datetime) do
    # Convert NaiveDateTime to DateTime assuming UTC
    naive_datetime
    |> DateTime.from_naive!("Etc/UTC")
    |> DateTime.to_iso8601()
  end

  defp generate_csv_report(settlement, transactions) do
    headers = "Transaction ID,Amount,Status,Type,Timestamp\n"

    transaction_rows =
      Enum.map(transactions, fn t ->
        "#{t.transaction_id},#{t.transaction_amount},#{t.status},#{Map.get(t.transaction_details || %{}, :pay_mode, "N/A")},#{format_datetime(t.matched_at)}"
      end)
      |> Enum.join("\n")

    summary =
      "\n\nSettlement Summary\n" <>
        "Settlement ID: #{settlement.settlement_id}\n" <>
        "Date: #{Date.to_iso8601(settlement.date)}\n" <>
        "Total Amount: #{settlement.amount}\n" <>
        "Status: #{settlement.status}\n" <>
        "Transaction Count: #{settlement.total_transaction_count}\n"

    headers <> transaction_rows <> summary
  end

  defp generate_text_report(settlement, transactions) do
    # Simple text report for PDF (in real implementation, use a PDF library)
    transaction_details =
      Enum.map(transactions, fn t ->
        "#{t.transaction_id} - #{t.transaction_amount} - #{t.settlement_status} - #{t.pay_mode}"
      end)
      |> Enum.join("\n")

    ("Settlement Report\n" <>
       "================\n\n" <>
       "Settlement ID: #{settlement.settlement_id}\n" <>
       "Date: #{Date.to_iso8601(settlement.date)}\n" <>
       "Total Amount: #{settlement.amount}\n" <>
       "Status: #{settlement.status}\n" <>
       "Transaction Count: #{settlement.total_transaction_count}\n\n" <>
       "Transactions:\n" <>
       "=============\n\n" <>
       Enum.map(transactions, fn t ->
         "#{t.transaction_id} - #{t.transaction_amount} - #{t.settlement_status} - #{t.pay_mode}"
       end))
    |> Enum.join("\n")
  end

  def download(conn, %{"bank_user_id" => bank_user_id, "date" => date}) do
    case TransactionEodGenerator.generate_settlement_files(
           bank_user_id: bank_user_id,
           date: date,
           format: :csv
         ) do
      {:ok, %{csv: path}} ->
        send_download(conn, {:file, path}, filename: Path.basename(path))

      {:error, reason} ->
        conn
        |> put_status(:bad_request)
        |> json(%{error: reason})
    end
  end

  defp get_settlement_by_bank_user_id_and_date(bank_user_id, date) do
    settlement =
      Repo.one(
        from s in Settlement,
          where: s.bank_user_id == ^bank_user_id and s.date == ^date,
          limit: 1
      )

    case settlement do
      nil ->
        {:error, "Settlement not found"}

      settlement ->
        {:ok, settlement}
    end
  end

  # Private helper functions

  defp match_settlement_status_from_csv_file(settlement, csv_file) do
    import Ecto.Query
    alias DaProductApp.Repo
    alias DaProductApp.Transactions.Transaction

    case File.read(csv_file) do
      {:ok, csv_content} ->
        # Split lines and remove empty lines
        lines =
          csv_content
          |> String.split(~r/\r?\n/)
          |> Enum.map(&String.trim/1)
          |> Enum.reject(&(&1 == ""))

        # Helper to detect header lines
        is_header = fn line ->
          String.starts_with?(line, [
            "settleDate", "clearingBatchId", "participantId", "transactionRequestId"
          ])
        end

        # Find summary row (first non-header, non-empty line)
        summary_row =
          lines
          |> Enum.drop_while(&is_header.(&1))
          |> List.first()

        # Find detail header and detail rows
        detail_header_idx =
          lines
          |> Enum.find_index(fn line -> String.starts_with?(line, "clearingBatchId") end)

        detail_header =
          if detail_header_idx, do: Enum.at(lines, detail_header_idx), else: nil

        detail_rows =
          if detail_header_idx do
            Enum.slice(lines, detail_header_idx + 1, length(lines))
            |> Enum.reject(&is_header.(&1))
          else
            []
          end

        if is_nil(summary_row) or summary_row == "" do
          {:error, :invalid_format}
        else
          # Parse summary row
          summary_cols = String.split(summary_row, ~r/[\t,]/)
          [
            settle_date,
            _total_count,
            _fund_direction,
            _currency,
            _net_settlement_amount,
            _txn_currency,
            net_txn_amount | _
          ] = summary_cols

          settle_date = String.replace(settle_date, "/", "-")
          {:ok, date} = Date.from_iso8601(settle_date)
          net_amount = Decimal.new(net_txn_amount)

          transactions =
            Repo.all(
              from t in Transaction,
                where: fragment("DATE(?)", t.inserted_at) == ^date
            )

          db_sum =
            transactions
            |> Enum.map(& &1.transaction_amount)
            |> Enum.reduce(Decimal.new(0), &Decimal.add/2)

          if db_sum == net_amount do
            Repo.update_all(
              from(t in Transaction, where: fragment("DATE(?)", t.inserted_at) == ^date),
              set: [settlement_status: "matched"]
            )

            insert_settlement_transactions(settlement.settlement_id, date, "matched")
            {:ok, :matched}
          else
            # Dynamically find the index for transactionAmountValue in detail header
            txn_amt_idx =
              if detail_header do
                String.split(detail_header, ~r/[\t,]/)
                |> Enum.find_index(&(&1 == "transactionAmountValue"))
              else
                nil
              end

            Enum.each(detail_rows, fn row ->
              cols = String.split(row, ~r/[\t,]/)
              # Defensive: skip empty or header lines
              if txn_amt_idx && length(cols) > txn_amt_idx && !is_header.(row) do
                txn_time = Enum.at(cols, 6)
                txn_date = String.slice(txn_time || "", 0, 10)
                {:ok, d_date} = Date.from_iso8601(String.replace(txn_date, "/", "-"))
                detail_amount = Enum.at(cols, txn_amt_idx) || "0"
                # Only parse if it's a number
                case Decimal.parse(detail_amount) do
                  {detail_amount_decimal, ""} ->
                    Repo.update_all(
                      from(t in Transaction,
                        where:
                          fragment("DATE(?)", t.inserted_at) == ^d_date and
                            t.transaction_amount == ^detail_amount_decimal
                      ),
                      set: [settlement_status: "partially matched"]
                    )

                    insert_settlement_transactions(
                      settlement.settlement_id,
                      d_date,
                      "partially matched"
                    )

                  _ ->
                    :skip
                end
              end
            end)

            {:ok, :partially_matched}
          end
        end

      {:error, reason} ->
        Logger.error("Failed to read CSV file #{csv_file}: #{inspect(reason)}")
        {:error, reason}
    end
  end

  defp insert_settlement_transactions(settlement_id, date, status) do
    import Ecto.Query
    alias DaProductApp.Repo
    alias DaProductApp.Transactions.Transaction
    alias DaProductApp.Settlements.SettlementTransaction

    matched_txns =
      Repo.all(
        from t in Transaction,
          where:
            fragment("DATE(?)", t.inserted_at) == ^date and t.settlement_status == ^status and
              not is_nil(t.transaction_id)
      )

    Enum.each(matched_txns, fn txn ->
      attrs = %{
        settlement_id: get_settlement_db_id(settlement_id),
        transaction_id: txn.transaction_id,
        qr_id: Map.get(txn, :qr_id),
        terminal_id: Map.get(txn, :terminal_id),
        transaction_amount: txn.transaction_amount,
        transaction_currency: Map.get(txn, :transaction_currency),
        transaction_status: status, # <-- Use the actual status here!
        transaction_time: txn.inserted_at,
        mdr_charge: Map.get(txn, :mdr_charge),
        mdr_charge_currency: Map.get(txn, :mdr_charge_currency),
        tax_on_mdr: Map.get(txn, :tax_on_mdr),
        tax_on_mdr_currency: Map.get(txn, :tax_on_mdr_currency),
        net_received_amount: Map.get(txn, :net_settlement_amount),
        net_received_currency: Map.get(txn, :net_settlement_currency),
        status: status
      }

      %SettlementTransaction{}
      |> SettlementTransaction.changeset(attrs)
      |> Repo.insert(on_conflict: :nothing)
      |> case do
        {:ok, _} ->
          Logger.info("TMS: Inserted settlement_transaction for txn_id #{txn.transaction_id}")

        {:error, changeset} ->
          Logger.error(
            "TMS: Failed to insert settlement_transaction: #{inspect(changeset.errors)}"
          )
      end
    end)
  end

  defp get_settlement_db_id(settlement_id) do
    case Repo.one(from s in Settlement, where: s.settlement_id == ^settlement_id, select: s.id) do
      nil -> raise "No settlement found for settlement_id=#{settlement_id}"
      id -> id
    end
  end
end