defmodule DaProductApp.Settlements.EodFileGenerator do
  @moduledoc """
  Generates End-of-Day (EOD) settlement files for all merchants.

  This module handles the generation of daily settlement files in CSV format
  with proper headers, data, and checksum validation.
  """

  alias DaProductApp.Settlements
  alias DaProductApp.Settlements.Settlement
  alias DaProductApp.Workers.EodFileGenerationWorker
  alias DaProductApp.Repo
  import Ecto.Query
  require Logger

  @file_type "QR_Payment_Settlement"
  @version "1.1"
  @generated_by "AaniQR System"
  @base_path "priv/static/eod_files"

  @doc """
  Generates EOD settlement file for a specific date.

  This function handles both synchronous and asynchronous file generation.
  For large transaction counts (>10,000), it queues a background job unless
  the force flag is set.

  UPDATED: Now filters out already processed settlements from YSP summary table.

  ## Options
  - `:date` - The date to generate the settlement file for (defaults to today)
  - `:sequence` - The sequence number for the file (defaults to 1)
  - `:force` - Force generation even if transaction count is large (defaults to false)

  ## Returns
  - `{:ok, file_path}` - If file is generated successfully
  - `{:scheduled, job_id}` - If file generation is scheduled via Oban
  - `{:error, reason}` - If file generation fails
  - `{:error, :all_processed}` - If all settlements are already processed
  """
  def generate_eod_file(opts \\ []) do
    date = Keyword.get(opts, :date, Date.utc_today())
    sequence = Keyword.get(opts, :sequence, 1)
    force = Keyword.get(opts, :force, false)

    Logger.info("EOD File Generator: Starting generation for date: #{Date.to_iso8601(date)}")
    all_settlements = get_settlements_for_date(date)

    Logger.info(
      "EOD File Generator: Found #{length(all_settlements)} total settlements for date #{Date.to_iso8601(date)}"
    )

    # Filter out already processed settlements
    unprocessed_settlements = Settlements.filter_unprocessed_settlements(all_settlements, date)

    Logger.info(
      "EOD File Generator: After filtering, #{length(unprocessed_settlements)} unprocessed settlements remain"
    )

    Logger.debug(
      "EOD File Generator: Unprocessed settlements data: #{inspect(unprocessed_settlements)}"
    )

    if length(unprocessed_settlements) == 0 do
      if length(all_settlements) > 0 do
        Logger.info(
          "EOD File Generator: All settlements for date #{Date.to_iso8601(date)} have already been processed"
        )

        {:error, :all_processed}
      else
        Logger.warn("EOD File Generator: No settlements found for date #{Date.to_iso8601(date)}")
        {:error, "No settlements found for date #{Date.to_iso8601(date)}"}
      end
    else
      total_transactions = calculate_total_transactions(unprocessed_settlements)

      if Decimal.compare(total_transactions, Decimal.new(10_000)) == :gt and not force do
        # Queue background job using Oban
        job_args = %{
          date: Date.to_iso8601(date),
          sequence: sequence,
          force: force
        }

        case EodFileGenerationWorker.new(job_args) |> Oban.insert() do
          {:ok, job} ->
            Logger.info(
              "EOD file generation job queued for date: #{Date.to_iso8601(date)}, job_id: #{job.id}"
            )

            {:scheduled, job.id}

          {:error, reason} ->
            Logger.error("Failed to queue EOD file generation job: #{inspect(reason)}")
            {:error, "Failed to schedule file generation"}
        end
      else
        # Generate file synchronously
        generate_eod_file_sync(date: date, sequence: sequence, force: force)
      end
    end
  end

  @doc """
  Synchronously generates EOD settlement file for a specific date.

  This function is used for immediate file generation, either for small
  transaction counts or when forced, and by the Oban worker.

  UPDATED: Now filters out already processed settlements and records new ones.

  ## Options
  - `:date` - The date to generate the settlement file for (defaults to today)
  - `:sequence` - The sequence number for the file (defaults to 1)
  - `:force` - Force generation even if transaction count is large (defaults to false)

  ## Returns
  - `{:ok, file_path}` - If file is generated successfully
  - `{:error, reason}` - If file generation fails
  - `{:error, :all_processed}` - If all settlements are already processed
  """
  def generate_eod_file_sync(opts \\ []) do
    date = Keyword.get(opts, :date, Date.utc_today())
    sequence = Keyword.get(opts, :sequence, 1)
    _force = Keyword.get(opts, :force, false)

    Logger.info("EOD File Generator Sync: Starting generation for date: #{Date.to_iso8601(date)}")
    all_settlements = get_settlements_for_date(date)

    Logger.info(
      "EOD File Generator Sync: Found #{length(all_settlements)} total settlements for date #{Date.to_iso8601(date)}"
    )

    # Filter out already processed settlements
    unprocessed_settlements = Settlements.filter_unprocessed_settlements(all_settlements, date)

    Logger.info(
      "EOD File Generator Sync: After filtering, #{length(unprocessed_settlements)} unprocessed settlements remain"
    )

    Logger.debug(
      "EOD File Generator Sync: Unprocessed settlements data: #{inspect(unprocessed_settlements)}"
    )

    if length(unprocessed_settlements) == 0 do
      if length(all_settlements) > 0 do
        Logger.info(
          "EOD File Generator Sync: All settlements for date #{Date.to_iso8601(date)} have already been processed"
        )

        {:error, :all_processed}
      else
        Logger.warn(
          "EOD File Generator Sync: No settlements found for date #{Date.to_iso8601(date)}"
        )

        {:error, "No settlements found for date #{Date.to_iso8601(date)}"}
      end
    else
      do_generate_file(unprocessed_settlements, date, sequence)
    end
  end

  @doc """
  Generates the actual CSV file content.
  """
  def generate_csv_content(settlements, date) do
    header_lines = build_header_lines(date)
    data_lines = build_data_lines(settlements)
    footer_lines = build_footer_lines(settlements)

    all_lines = header_lines ++ data_lines ++ footer_lines
    content_for_checksum = build_content_for_checksum(all_lines)
    checksum = calculate_checksum(content_for_checksum)

    final_content =
      all_lines
      |> insert_checksum(checksum)
      |> Enum.join("\n")

    final_content
  end

  @doc """
  Simple function to test database date queries
  """
  def test_date_queries(date) do
    Logger.info("=== TESTING DATE QUERIES ===")

    # Test 1: Count with Date struct
    date_struct = date
    count1 = Repo.aggregate(from(s in Settlement, where: s.date == ^date_struct), :count, :id)
    Logger.info("Date struct query (~D[#{Date.to_iso8601(date_struct)}]): #{count1} results")

    # Test 2: Count with string
    date_string = Date.to_iso8601(date)
    count2 = Repo.aggregate(from(s in Settlement, where: s.date == ^date_string), :count, :id)
    Logger.info("Date string query ('#{date_string}'): #{count2} results")

    # Test 3: Check actual data types in database
    sample_dates = Repo.all(from(s in Settlement, select: {s.settlement_id, s.date}, limit: 5))
    Logger.info("Sample settlement dates:")

    Enum.each(sample_dates, fn {id, date_val} ->
      Logger.info("  #{id}: #{inspect(date_val)} (type: #{inspect(date_val.__struct__)})")
    end)

    # Test 4: Try fragment query to bypass Ecto casting
    count3 =
      Repo.one(
        from(s in Settlement,
          where: fragment("DATE(?) = ?", s.date, ^date_string),
          select: count(s.id)
        )
      )

    Logger.info("Fragment query with DATE() function: #{count3} results")

    %{date_struct: count1, date_string: count2, fragment: count3}
  end

  @doc """
  Debug function to check settlements in the database for a specific date.
  """
  def debug_settlements_for_date(date) do
    Logger.info("=== DEBUG: Checking settlements for date: #{Date.to_iso8601(date)} ===")

    # First, check if any settlements exist at all
    all_settlements_count = Repo.aggregate(Settlement, :count, :id)
    Logger.info("DEBUG: Total settlements in database: #{all_settlements_count}")

    # Check settlements for specific date
    date_settlements = Repo.all(from s in Settlement, where: s.date == ^date)

    Logger.info(
      "DEBUG: Settlements for date #{Date.to_iso8601(date)}: #{length(date_settlements)}"
    )

    # Check if there are settlements with settlement_id containing the date
    date_string = Date.to_iso8601(date) |> String.replace("-", "")
    settlement_pattern = "%SETT#{date_string}%"

    pattern_settlements =
      Repo.all(from s in Settlement, where: like(s.settlement_id, ^settlement_pattern))

    Logger.info(
      "DEBUG: Settlements with pattern SETT#{date_string}: #{length(pattern_settlements)}"
    )

    # List all settlements with their details
    all_settlements = Repo.all(from s in Settlement, limit: 10)
    Logger.info("DEBUG: First 10 settlements in database:")

    Enum.each(all_settlements, fn s ->
      Logger.info("  - ID: #{s.settlement_id}, Date: #{s.date}, Merchant: #{s.merchant_id}")
    end)

    # List all settlements that match the date pattern in settlement_id
    Logger.info("DEBUG: Settlements matching pattern SETT#{date_string}:")

    Enum.each(pattern_settlements, fn s ->
      Logger.info("  - ID: #{s.settlement_id}, Date: #{s.date}, Merchant: #{s.merchant_id}")
    end)

    # Return results
    %{
      total_settlements: all_settlements_count,
      date_settlements: length(date_settlements),
      pattern_settlements: length(pattern_settlements),
      specific_settlement: nil
    }
  end

  @doc """
  Calculates SHA256 checksum for the file content.
  """
  def calculate_checksum(content) do
    :crypto.hash(:sha256, content)
    |> Base.encode16()
    |> String.upcase()
  end

  # Private functions

  defp get_settlements_for_date(date) do
    Logger.info("EOD File Generator: Querying settlements for date: #{Date.to_iso8601(date)}")
    Logger.info("EOD File Generator: Date type: #{inspect(date)}")

    # Debug: Check database connection info
    db_config = Application.get_env(:da_product_app, DaProductApp.Repo)

    Logger.info(
      "EOD File Generator: Connected to database: #{db_config[:database]} on #{db_config[:hostname]}:#{db_config[:port] || 3306}"
    )

    # First, let's try different approaches to find the data
    # Approach 1: Direct date comparison (current approach)
    query1 = from(s in Settlement, where: s.date == ^date)
    count1 = Repo.aggregate(query1, :count, :id)
    Logger.info("EOD File Generator: Direct date comparison found #{count1} settlements")

    # Approach 2: String comparison
    date_string = Date.to_iso8601(date)
    query2 = from(s in Settlement, where: s.date == ^date_string)
    count2 = Repo.aggregate(query2, :count, :id)
    Logger.info("EOD File Generator: String date comparison found #{count2} settlements")

    # Approach 3: Check what dates actually exist in the database
    all_dates = Repo.all(from(s in Settlement, select: s.date, distinct: true, limit: 10))
    Logger.info("EOD File Generator: Sample dates in database: #{inspect(all_dates)}")

    # Approach 4: Check settlements with settlement_id containing the target date
    date_pattern = Date.to_iso8601(date) |> String.replace("-", "")

    pattern_settlements =
      Repo.all(
        from s in Settlement,
          where: like(s.settlement_id, ^"%SETT#{date_pattern}%"),
          select: {s.settlement_id, s.date},
          limit: 5
      )

    Logger.info(
      "EOD File Generator: Settlements with ID pattern SETT#{date_pattern}: #{inspect(pattern_settlements)}"
    )

    # Use the approach that finds data
    date_to_use = if count1 > 0, do: date, else: date_string
    Logger.info("EOD File Generator: Using #{inspect(date_to_use)} for final query")

    query =
      from(s in Settlement,
        where: s.date == ^date_to_use,
        left_join: m in assoc(s, :merchant),
        group_by: [
          s.merchant_id,
          s.merchant_tag,
          s.bank_user_id,
          s.batch_number,
          s.gross_settlement_currency
        ],
        select: %{
          merchant_tag: s.merchant_tag,
          bank_user_id: s.bank_user_id,
          # Using merchant ID as MID
          mid: coalesce(m.id, s.merchant_id),
          total_transaction_count: sum(s.total_transaction_count),
          gross_settlement_amount: sum(s.gross_settlement_amount),
          currency: s.gross_settlement_currency,
          mdr_charges: sum(s.mdr_charges),
          tax_on_mdr: sum(s.tax_on_mdr),
          net_settlement_amount: sum(s.net_settlement_amount),
          # Default to NO, would need logic to determine
          mismatch_detected: "NO",
          # Read actual batch_number from database
          batch_number: s.batch_number
        }
      )

    Logger.debug("EOD File Generator: Final SQL Query: #{inspect(query)}")

    settlements = Repo.all(query)
    Logger.info("EOD File Generator: Final query returned #{length(settlements)} settlements")

    if length(settlements) > 0 do
      Logger.debug("EOD File Generator: First settlement: #{inspect(List.first(settlements))}")
    end

    settlements
  end

  defp calculate_total_transactions(settlements) do
    Enum.reduce(settlements, Decimal.new(0), fn settlement, acc ->
      count =
        case settlement.total_transaction_count do
          nil -> Decimal.new(0)
          %Decimal{} = decimal -> decimal
          number when is_number(number) -> Decimal.new(number)
          _ -> Decimal.new(0)
        end

      Decimal.add(acc, count)
    end)
  end

  defp do_generate_file(settlements, date, sequence) do
    csv_content = generate_csv_content(settlements, date)
    filename = generate_filename(date, sequence)
    file_path = Path.join(@base_path, filename)

    # Ensure directory exists
    File.mkdir_p!(Path.dirname(file_path))

    case File.write(file_path, csv_content) do
      :ok ->
        Logger.info("EOD File Generator: Successfully wrote file: #{file_path}")

        # Record settlements in YSP summary table after successful file generation
        case Settlements.record_settlements_batch(settlements, file_path, date) do
          {:ok, recorded_summaries} ->
            Logger.info(
              "EOD File Generator: Successfully recorded #{length(recorded_summaries)} settlements in YSP summary"
            )

            {:ok, file_path}

          {:error, reason} ->
            Logger.error(
              "EOD File Generator: Failed to record settlements in YSP summary: #{inspect(reason)}"
            )

            # File was generated successfully, but YSP recording failed
            # Return success since file exists, but log the issue
            {:ok, file_path}
        end

      {:error, reason} ->
        Logger.error("EOD File Generator: Failed to write file: #{reason}")
        {:error, "Failed to write file: #{reason}"}
    end
  end

  defp build_header_lines(date) do
    settlement_date = Date.to_iso8601(date)

    generated_timestamp =
      DateTime.utc_now()
      |> DateTime.to_iso8601()
      # Convert to IST
      |> String.replace("Z", "+05:30")

    [
      "#FileType,#{@file_type}",
      "#Version,#{@version}",
      "#SettlementDate,#{settlement_date}",
      "#GeneratedTimestamp,#{generated_timestamp}",
      "#FileGeneratedBy,#{@generated_by}",
      "#-------------------------------------",
      "MerchantTag,BankUserID,MID,TotalTransactionCount,GrossSettlementAmount,Currency,MDRCharges,TaxOnMDR,NetSettlementAmount,MismatchDetected,BatchNumber"
    ]
  end

  defp build_data_lines(settlements) do
    Enum.map(settlements, fn settlement ->
      [
        settlement.merchant_tag || "",
        settlement.bank_user_id || "",
        settlement.mid || "",
        settlement.total_transaction_count || 0,
        format_decimal(settlement.gross_settlement_amount),
        settlement.currency || "AED",
        format_decimal(settlement.mdr_charges),
        format_decimal(settlement.tax_on_mdr),
        format_decimal(settlement.net_settlement_amount),
        settlement.mismatch_detected || "NO",
        settlement.batch_number || "000001"
      ]
      |> Enum.join(",")
    end)
  end

  defp build_footer_lines(settlements) do
    total_merchants = length(settlements)

    [
      "#-------------------------------------",
      "#TotalMerchants,#{total_merchants}",
      "#Checksum,PLACEHOLDER_CHECKSUM",
      "#EndOfFile"
    ]
  end

  defp build_content_for_checksum(lines) do
    lines
    |> Enum.reject(fn line ->
      String.starts_with?(line, "#Checksum") or String.starts_with?(line, "#EndOfFile")
    end)
    |> Enum.join("\n")
  end

  defp insert_checksum(lines, checksum) do
    Enum.map(lines, fn line ->
      if String.starts_with?(line, "#Checksum,PLACEHOLDER_CHECKSUM") do
        "#Checksum,#{checksum}"
      else
        line
      end
    end)
  end

  defp generate_filename(date, sequence) do
    mmdd = date |> Date.to_string() |> String.slice(5, 5) |> String.replace("-", "")

    hhmmss =
      DateTime.utc_now()
      |> DateTime.to_time()
      |> Time.to_string()
      |> String.slice(0, 8)
      |> String.replace(":", "")

    seq_str = sequence |> Integer.to_string() |> String.pad_leading(2, "0")

    "mercury_pay_#{mmdd}_#{hhmmss}_#{seq_str}.csv"
  end

  defp format_decimal(nil), do: "0"

  defp format_decimal(decimal) when is_struct(decimal, Decimal) do
    Decimal.to_string(decimal)
  end

  defp format_decimal(value), do: to_string(value)
end