defmodule Mix.Tasks.LoadBinTable do
  @shortdoc "Load BIN (Bank Identification Number) table from a YSP-supplied CSV file"

  @moduledoc """
  Bulk-loads the BIN table from a YSP-supplied CSV into the `bin_table` database table.

  ## Usage

      mix load_bin_table --file=path/to/bin.csv

  ## Options

    * `--file` — (required) path to the BIN CSV file supplied by YSP.
    * `--dry-run` — validate and count rows without writing to the database.
    * `--loaded-by` — integer user ID to stamp on each row (default: 0).

  ## Expected CSV Format

  The file must be UTF-8 encoded with a header row. Accepted column names
  (case-insensitive):

      bin_prefix, scheme, card_category, issuer_country, issuer_name

  `bin_prefix` must be 6 or 8 numeric digits. Rows with invalid BIN prefixes
  are skipped with a warning.

  ## Behavior

  - Inserts new BIN entries using INSERT ... ON CONFLICT DO NOTHING (idempotent).
  - Existing BINs (same `bin_prefix`) are left unchanged.
  - Progress is logged every 500 rows.

  ## Example

      mix load_bin_table --file=/data/ysp_bin_table_20260306.csv
      mix load_bin_table --file=/data/ysp_bin_table_20260306.csv --dry-run
  """

  use Mix.Task

  require Logger

  alias PlatformCore.Repo
  alias SettlementCore.BinTable

  @chunk_size 500
  @valid_schemes ~w(VISA MASTERCARD AMEX UNIONPAY MADA LOCAL OTHER)
  @valid_categories ~w(CREDIT DEBIT PREPAID CORPORATE GOVERNMENT)

  @impl Mix.Task
  def run(argv) do
    {opts, _rest, _invalid} =
      OptionParser.parse(argv,
        strict: [file: :string, dry_run: :boolean, loaded_by: :integer]
      )

    file_path = Keyword.get(opts, :file)
    dry_run   = Keyword.get(opts, :dry_run, false)
    loaded_by = Keyword.get(opts, :loaded_by, 0)

    unless file_path do
      Mix.raise("--file is required. Usage: mix load_bin_table --file=path/to/bin.csv")
    end

    unless File.exists?(file_path) do
      Mix.raise("File not found: #{file_path}")
    end

    Mix.Task.run("app.start")

    Mix.shell().info("Loading BIN table from: #{file_path}")
    if dry_run, do: Mix.shell().info("DRY RUN — no records will be written.")

    rows = parse_csv(file_path, loaded_by)

    Mix.shell().info("Parsed #{length(rows)} valid rows from CSV.")

    if dry_run do
      Mix.shell().info("Dry run complete. #{length(rows)} rows would be inserted.")
    else
      inserted = bulk_insert(rows)
      Mix.shell().info("Done. Inserted #{inserted} new BIN records (existing records unchanged).")
    end
  end

  # ─── CSV Parsing ─────────────────────────────────────────────────────────────

  defp parse_csv(file_path, loaded_by) do
    now = NaiveDateTime.utc_now() |> NaiveDateTime.truncate(:second)

    lines = File.read!(file_path) |> String.split(["\r\n", "\n"], trim: true)

    case lines do
      [] ->
        Mix.shell().info("Warning: empty file.")
        []

      [_header | _] = all_lines ->
        [header_line | data_lines] = all_lines
        headers = header_line |> String.split(",") |> Enum.map(&(String.trim(&1) |> String.downcase()))

        data_lines
        |> Enum.with_index(2)
        |> Enum.flat_map(fn {line, line_no} ->
          values = String.split(line, ",")
          row = headers |> Enum.zip(values) |> Map.new(fn {k, v} -> {k, String.trim(v)} end)

          case build_row(row, loaded_by, now) do
            {:ok, attrs} ->
              [attrs]

            {:skip, reason} ->
              Logger.warning("[LoadBinTable] Line #{line_no} skipped: #{reason}")
              []
          end
        end)
    end
  end

  defp build_row(row, loaded_by, loaded_at) do
    bin_prefix = get_field(row, ~w(bin_prefix bin))
    scheme     = upcase(get_field(row, ~w(scheme card_scheme)))
    category   = upcase(get_field(row, ~w(card_category category)))
    country    = upcase(get_field(row, ~w(issuer_country country)))
    issuer     = get_field(row, ~w(issuer_name issuer))

    with :ok <- validate_bin_prefix(bin_prefix),
         :ok <- validate_scheme(scheme) do

      attrs = %{
        bin_prefix:     bin_prefix,
        scheme:         scheme,
        card_category:  valid_category(category),
        issuer_country: country,
        issuer_name:    issuer,
        is_active:      true,
        loaded_at:      loaded_at,
        loaded_by:      loaded_by,
        inserted_at:    loaded_at,
        updated_at:     loaded_at
      }

      {:ok, attrs}
    end
  end

  defp validate_bin_prefix(nil), do: {:skip, "missing bin_prefix"}
  defp validate_bin_prefix(""),  do: {:skip, "empty bin_prefix"}
  defp validate_bin_prefix(bin) do
    if Regex.match?(~r/^\d{6,8}$/, bin),
      do: :ok,
      else: {:skip, "invalid bin_prefix '#{bin}' — must be 6-8 digits"}
  end

  defp validate_scheme(nil),   do: {:skip, "missing scheme"}
  defp validate_scheme(scheme) do
    if scheme in @valid_schemes,
      do: :ok,
      else: {:skip, "unknown scheme '#{scheme}'"}
  end

  defp valid_category(cat) when cat in @valid_categories, do: cat
  defp valid_category(_), do: nil

  defp get_field(row, candidates) do
    Enum.find_value(candidates, fn k ->
      v = Map.get(row, k)
      if v && v != "", do: v, else: nil
    end)
  end

  defp upcase(nil), do: nil
  defp upcase(v),   do: String.upcase(v)

  # ─── Bulk Insert ─────────────────────────────────────────────────────────────

  defp bulk_insert(rows) do
    rows
    |> Enum.chunk_every(@chunk_size)
    |> Enum.with_index()
    |> Enum.reduce(0, fn {chunk, chunk_idx}, acc ->
      {count, _} =
        Repo.insert_all(BinTable, chunk, on_conflict: :nothing, conflict_target: :bin_prefix)

      inserted_so_far = acc + count

      Mix.shell().info("  Chunk #{chunk_idx + 1}: #{inserted_so_far} total inserted so far...")

      inserted_so_far
    end)
  end
end