# Apesu.new.apesu_populate                               # create records
# Apesu.find_each{|a| a.update_exchange_account_fields } # copy details from DB
# Apesu.find_each{|a| a.update_exchange_asset_fields }   # copy details from DB
# Apesu.evms  .find_each{|a| a.adjust_balance_by_day }
# Apesu.solana.find_each{|a| a.adjust_balance_by_day_solana }
class Apesu < ApplicationRecord
  self.table_name = "apesu"

  belongs_to :exchange_account
  belongs_to :exchange_asset
  has_many :movements,
    ->(apesu) {
      where(exchange_asset_id: apesu.exchange_asset_id)
    },
    through: :exchange_account,
    source: :movements

  EVMS = [
    "arbitrum",
    "avalanche",
    "base",
    "bsc",
    "ethereum",
    "hyperliquid",
    "linea",
    "mantle",
    "monad",
    "optimism",
    "polygon",
    "sonic"
  ]

  def self.internal_transfers_categorization
    workspace = Workspace.find("23cffe0f-9995-42bc-9338-cec9c1ffa087")
    tk_internal_transfer = TransactionKind.find("9119bbf0-efb1-4e02-958f-369d40e667ad") # internal_transfer
    raise "Internal Transfer TransactionKind not found" if tk_internal_transfer.nil?

    bar = ProgressBar.new(workspace.exchange_accounts.count)

    workspace.exchange_accounts.each do |ea|
      puts [ea.id, ea.name].inspect
      scope = workspace
        .movements
        .joins(:exchange_account)
        .where(transaction_kind_id: nil)
        .where(exchange_account: {exchange_name: ea.exchange_name})
        .where.not(exchange_account_id: ea.id)

      scope
        .where(from_address: ea.public_address)
        .update_all(
          transaction_kind_id: tk_internal_transfer.id,
          justified: true
        )
      scope
        .where(to_address: ea.public_address)
        .update_all(
          transaction_kind_id: tk_internal_transfer.id,
          justified: true
        )

      bar.increment!
    end
  end

  # Block numbers for timestamp 2024-12-31T23:59:59.9999 Europe/Paris.
  BLOCKS = {
    "arbitrum" => 290673032,
    "avalanche" => 55157797,
    "base" => 24448326,
    "bsc" => 45368282,
    "ethereum" => 21525593,
    "hyperliquid" => nil, # Genesis after 2024
    "linea" => 14021053,
    "mantle" => 73777844,
    "monad" => nil, # Genesis after 2024
    "optimism" => 130043611,
    "polygon" => 66157355,
    "sonic" => 2115676
  }

  BLOCKS_STOP = {
    "arbitrum" => 416579564,
    "avalanche" => 74822015,
    "base" => 40216326,
    "bsc" => 73626360,
    "ethereum" => 24135753,
    "hyperliquid" => 23351118,
    "linea" => 27281746,
    "mantle" => 89545843,
    "monad" => 45734004,
    "optimism" => 145811611,
    "polygon" => 81051370,
    "sonic" => 59332753
  }

  def etherscan_block_number(time)
    client = Etherscan::Client.new(exchange)
    Rails.cache.fetch("etherscan:#{exchange}:#{time.to_i}") do
      client.block_number(time)
    end
  end

  def etherscan_balance(block)
    client = Etherscan::Client.new(exchange)
    if contract_type == "native"
      client.balance_history(block, address).to_d
    elsif contract_type == "ERC20"
      client.token_balance_history(block, address, contract).to_d
    else
      raise "Etherscan cannot be used for contract type: #{contract_type.inspect}"
    end
  end

  def self.big
    where(address: [
      "0x03C01Acae3D0173a93d819efDc832C7C4F153B06",
      "0x03c01acae3d0173a93d819efdc832c7c4f153b06"
    ])
  end

  def self.wsteth
    find(6985)
  end

  def self.weth
    find(5945)
  end

  def self.solana
    where(exchange: "solana")
  end

  def self.evms
    where(exchange: EVMS)
      .where(contract_type: ["ERC20", "native"])
      .where(spam: false)
  end

  def self.nonspam
    where(spam: false)
  end

  def self.important
    where.not(treso_xlsx_line: nil)
  end

  # list: Apesu.with_euro_inventory.map{|a| [a.movements.order(:time).first.fiat_amount, a.euro_inventory, a.id, a.exchange, a.symbol, a.treso_xlsx_line] }
  def self.with_euro_inventory
    where.not(euro_inventory: nil)
  end

  def w
    Workspace.find("23cffe0f-9995-42bc-9338-cec9c1ffa087")
  end

  def time_init
    w.tz.parse("2024-12-31").end_of_day
  end

  def time_a
    w.tz.parse("2025-01-01")
  end

  def time_b
    w.tz.parse("2025-12-31").end_of_day
  end

  def adjustments
    movements
      .where("info ilike 'Adjustment Entry%'")
      .order(:time)
  end

  def update_exchange_asset_fields
    self.contract = exchange_asset.remote_id
    self.contract_type = exchange_asset.contract_type
    self.spam = exchange_asset.is_fraudulent?
    self.symbol = exchange_asset.exchange_symbol
    save
  end

  def update_exchange_account_fields
    self.name = exchange_account.name
    self.address = exchange_account.public_address
    self.exchange = exchange_account.exchange_name
    self.movements_count_2025 = movements.where(time: time_a..time_b).count
    self.full_import = exchange_account.import_source_default_integration.from.nil?
    save
  end

  def update_report_fields
    self.balance_adjustment_method = if !adjustments.exists?
      "none"
    elsif movements.where(info: "Adjustment Entry (state eoh)").exists?
      "by hour"
    else
      "by tx"
    end
    self.balance_start = movements.where(time: ..time_init).sum(:amount)
    self.balance_end = movements.where(time: time_init..time_b).sum(:amount)

    save
  end

  def update_block_init
    return if !EVMS.include?(exchange)

    if block_init_2025 != BLOCKS[exchange]
      self.block_init_2025 = BLOCKS[exchange]
      save
    end

    return if block_init_2025.blank?
    return if !["native", "ERC20"].include?(contract_type)

    block = block_init_2025
    balance_real = etherscan_balance(block).to_d
    self.block_init_balance_2025 = balance_real
    save
  end

  def update_block_stop
    return if !EVMS.include?(exchange)

    if block_stop_2025 != BLOCKS_STOP[exchange]
      self.block_stop_2025 = BLOCKS_STOP[exchange]
      save
    end

    return if block_stop_2025.blank?
    return if !["native", "ERC20"].include?(contract_type)

    block = block_stop_2025
    balance_real = etherscan_balance(block).to_d
    self.block_stop_balance_2025 = balance_real
    save
  end

  def check_balance_start
    if (contract_type == "ERC20" || contract_type == "native") && block_init_balance_2025.present?
      ea = ExchangeAccount.find(exchange_account_id)
      easset = ExchangeAsset.find(exchange_asset_id)
      a = ea.movements
        .where(exchange_asset_id: easset)
        .where(time: ...time_a)
        .select("sum(amount) as sum_amount")
        .group(:exchange_asset_id)
        .pluck(Arel.sql("sum(amount)"))
        .first
        .to_d
      b = block_init_balance_2025.to_d * 10**-easset.token_decimals
      return [a, b, a == b]
    end

    [nil, nil, false]
  end

  def check_balance_end
    if (contract_type == "ERC20" || contract_type == "native") && block_stop_balance_2025.present?
      easset = ExchangeAsset.find(exchange_asset_id)
      a = movements
        .where(time: ..time_b)
        .select("sum(amount) as sum_amount")
        .group(:exchange_asset_id)
        .pluck(Arel.sql("sum(amount)"))
        .first
        .to_d
      b = block_stop_balance_2025.to_d * 10**-easset.token_decimals
      return [a, b, a == b]
    end

    [nil, nil, false]
  end

  # Create the first balance adjustment based on the value in block_init_balance_2025.
  # List account needing a adjustment:
  #   Apesu.evms.order(:id).where.not(block_init_2025: nil).filter{|apesu| b = apesu.check_balance_start; b[0] != b[1] }
  def adjust_balance_start
    return if block_init_balance_2025.blank?
    return if !["native", "ERC20"].include?(contract_type)
    return if exchange_asset.token_decimals.blank?
    return if movements.where(info: "Adjustment Entry").exists?

    ea = exchange_account
    easset = exchange_asset
    balance_real = block_init_balance_2025.to_d * 10**-easset.token_decimals
    balance_computed = movements.where(time: ...time_a).pluck(:amount).sum
    balance_diff = balance_real - balance_computed

    if balance_diff.nonzero?
      now = Time.now
      Movement.create!({
        amount: balance_diff,
        blockchain_txid: nil,
        created_at: now,
        etl_import_id: nil,
        exchange_account_id: ea.id,
        exchange_asset_id: easset.id,
        exchange_asset_remote_id: easset.remote_id,
        fiat_amount: nil,
        from_address: nil,
        id: SecureRandom.uuid,
        import_source_id: nil,
        info: "Adjustment Entry",
        internal_transfer: false,
        justified: false,
        local_identifier: "adjustement_entry:#{easset.id}:init",
        manual: true,
        manually_modified: false,
        market_pair: nil,
        note: "A reconciliation entry added to match the on-chain balance (block: #{block_init_2025}).",
        quantity_after_movement: nil,
        remote_identifier: nil,
        remote_txid: nil,
        time: time_init,
        to_address: nil,
        trade_in_id: nil,
        transaction_kind_id: "1c6d47a0-a6fc-4e45-bc03-d3257faa1198", # 'a nouveau'
        tx_id: nil,
        updated_at: now,
        vat_percentage: 0,
        wac: nil,
        workspace_id: ea.workspace_id
      })
    end

    [balance_computed, balance_real]
  end

  # Create the last balance adjustment based on the value in block_stop_balance_2025.
  # List account needing a adjustment:
  #   Apesu.evms.order(:id).where.not(block_stop_2025: nil).filter{|apesu| b = apesu.check_balance_send; b[0] != b[1] }
  def adjust_balance_end
    return if block_stop_balance_2025.blank?
    return if !["native", "ERC20"].include?(contract_type)
    return if exchange_asset.token_decimals.blank?
    return if movements.where(info: "Adjustment Entry (last)").exists?

    ea = exchange_account
    easset = exchange_asset
    balance_real = block_stop_balance_2025.to_d * 10**-easset.token_decimals
    balance_computed = movements.where(time: ..time_b).pluck(:amount).sum
    balance_diff = balance_real - balance_computed

    if balance_diff.nonzero?
      now = Time.now
      Movement.create!({
        amount: balance_diff,
        blockchain_txid: nil,
        created_at: now,
        etl_import_id: nil,
        exchange_account_id: ea.id,
        exchange_asset_id: easset.id,
        exchange_asset_remote_id: easset.remote_id,
        fiat_amount: nil,
        from_address: nil,
        id: SecureRandom.uuid,
        import_source_id: nil,
        info: "Adjustment Entry (last)",
        internal_transfer: false,
        justified: false,
        local_identifier: "adjustement_entry:#{easset.id}:last",
        manual: true,
        manually_modified: false,
        market_pair: nil,
        note: "A reconciliation entry added to match the on-chain balance (block: #{block_stop_2025}).",
        quantity_after_movement: nil,
        remote_identifier: nil,
        remote_txid: nil,
        time: time_b,
        to_address: nil,
        trade_in_id: nil,
        transaction_kind_id: nil,
        tx_id: nil,
        updated_at: now,
        vat_percentage: 0,
        wac: nil,
        workspace_id: ea.workspace_id
      })
    end

    [balance_computed, balance_real]
  end

  # List of important accounts.
  # @see Apesu - B - Trésorerie - 2024 - vsource (2026-05-18 Augustin).xlsx
  def apesu_treso_2024
    # 0 address
    # 1 exchange
    # 2 contract
    # 3 line
    [
      ["0x461cab446731afa993e1477e91af342fe2a727bf", "base", "0xcbb7c0000ab88b473b1f5afd9ef808440eed33bf", "5", "3_056.44"],
      ["0x12467a66b68791f4a7ad4e360d37d16f491ce6ad", "ethereum", "0x19756be2473a9c3351a426568ff9630c9d67f300", "6", "696_822.93"],
      ["0x12467a66b68791f4a7ad4e360d37d16f491ce6af", "ethereum", "0x19756be2473a9c3351a426568ff9630c9d67f300", "7", "696_822.93"],
      ["0x12467a66b68791f4a7ad4e360d37d16f491ce6ae", "ethereum", "0x57995a00ba1e3e17bcf08ba775bf225ca1f5f25e", "8", "696_719.52"],
      ["0x12467a66b68791f4a7ad4e360d37d16f491ce6ac", "ethereum", "0xd45a56f5b67eba836295b4c95fd6ea48d1334938", "9", "255_432.93"],
      ["0xa2b8a656f9217a0d72dbd1c2175ea654f4600941", "ethereum", "eth", "10", "146.817"], # puts ("234.3893".to_d / "0.0758583068239934".to_d * "0.0475160835279244".to_d ).to_s("F")
      ["0xa2b8a656f9217a0d72dbd1c2175ea654f4600941", "base", "eth", "11", "6.503"], # puts ("234.3893".to_d / "0.0758583068239934".to_d * "0.00210463099435776".to_d).to_s("F")
      ["0xa2b8a656f9217a0d72dbd1c2175ea654f4600941", "arbitrum", "eth", "12", "1.997"], # puts ("234.3893".to_d / "0.0758583068239934".to_d * "0.000646220336780946".to_d).to_s("F")
      ["0xa2b8a656f9217a0d72dbd1c2175ea654f4600941", "optimism", "eth", "13", "4.561"], # puts ("234.3893".to_d / "0.0758583068239934".to_d * "0.00147606443088932".to_d).to_s("F")
      ["0x12467a66b68791f4a7ad4e360d37d16f491ce6af", "ethereum", "eth", "14", "41.136"], # puts ("234.3893".to_d / "0.0758583068239934".to_d * "0.01331325".to_d).to_s("F")
      ["0x461cab446731afa993e1477e91af342fe2a727bf", "base", "eth", "15", "2.175"], # puts ("234.3893".to_d / "0.0758583068239934".to_d * "0.0007038170004975".to_d).to_s("F")
      ["0xc6f45197629cfd42faad2e1f6a223b98fd017be6", "ethereum", "eth", "16", "7.563"], # puts ("234.3893".to_d / "0.0758583068239934".to_d * "0.00244769172560339".to_d).to_s("F")
      ["0x12467a66b68791f4a7ad4e360d37d16f491ce6af", "ethereum", "0x0f757ec706e0e7b6006ded4d38c3a56213357580", "18", nil],
      ["0x12467a66b68791f4a7ad4e360d37d16f491ce6af", "ethereum", "0xf2f826c190d020a6d1ec422bf2269e63b8b315e0", "19", "38_950.17"],
      ["0xa549db2a43b06d5633711717d40619fa07a24dd4", "polygon", "matic", "22", "0.262"], # puts ("27.3104".to_d / "35.6171007831812".to_d *  "0.342172442905261".to_d).to_s("F")
      ["0xc6f45197629cfd42faad2e1f6a223b98fd017be6", "polygon", "matic", "23", "27.005"], # puts ("27.3104".to_d / "35.6171007831812".to_d * "35.2182920501489".to_d).to_s("F")
      ["0x461cab446731afa993e1477e91af342fe2a727bf", "avalanche", "0x0a8f05033b608e7ccb2974f2f800882785fdde79", "26", "18_781.94"],
      ["0xfb74b78ade25a14b4bf2198e8e480b29fb8af5d3", "arbitrum", "0x83442991e4d91f2a5ec2e074dddb5ab6219fdf56", "27", "743.62"],
      ["0xc99fe80056375977d39a38a498d4d9e5f8deec42", "bsc", "0x42e638e39165b4fc1edb51b40b39cbd5caa26c31", "28", "15_909.41"],
      ["0xc99fe80056375977d39a38a498d4d9e5f8deec42", "bsc", "0x151dc4fb849fc9b7b4d7cd2d0ed294f199b1f025", "29", "15_565.15"],
      ["0xfb74b78ade25a14b4bf2198e8e480b29fb8af5d3", "arbitrum", "0x96ffbbb6566743288b82731fbf4eb3ffe4fad642", "30", "18_606.30"],
      ["0x461cab446731afa993e1477e91af342fe2a727bf", "base", "0x01586e0be5cea3e3d2f16afaf7b505aa58106a20", "31", "8_689.28"],
      ["0x461cab446731afa993e1477e91af342fe2a727bf", "base", "0xbe8dbeb8319d9231a2a83d0fe7b8d77597abf12e", "32", "18_046.82"],
      ["0x461cab446731afa993e1477e91af342fe2a727bf", "base", "0x937a0691d85511a9a8bcac0f5ceb53159adbe925", "33", "117_801.58"],
      ["0x461cab446731afa993e1477e91af342fe2a727bf", "base", "0x3a8b432ed2afdd27478e0ab88f1069259264b475", "34", "50_657.80"],
      ["0x9994d74b2b5a50d98fcc367172a1cce325fa29bf", "polygon", "0x10386ac4e294773a94f26b32df836e193ec6724c", "35", "28_622.64"],
      ["0xc99fe80056375977d39a38a498d4d9e5f8deec42", "linea", "0xa47eb451bf97694b6bb011c7646181d659529283", "36", "8_302.60"],
      ["0x461cab446731afa993e1477e91af342fe2a727bf", "optimism", "0x0ddf83f92883c110a3fc3dbed28e30a119063e80", "37", "50_309.53"],
      ["0xc99fe80056375977d39a38a498d4d9e5f8deec42", "bsc", "0x78e2c69394212796cb090d31c4ef4b85caf9f330", "38", "66_472.02"],
      ["0x461cab446731afa993e1477e91af342fe2a727bf", "base", "0xc221293c1517ec1834d346761e937298e6004635", "39", "19_992.70"],
      ["0x461cab446731afa993e1477e91af342fe2a727bf", "avalanche", "0x508c94f82383936f4861f432f16e19c80d5761dc", "40", "17_605.99"],
      ["0xfb74b78ade25a14b4bf2198e8e480b29fb8af5d3", "arbitrum", "0x0a938dd6c2adb226e6deda79d21fc87e504aff15", "41", "13_743.97"],
      ["0x9994d74b2b5a50d98fcc367172a1cce325fa29bf", "polygon", "0x2c4205ff6806ad68a7381abee5422a6aa7665c77", "42", "9_525.03"],
      ["0x9994d74b2b5a50d98fcc367172a1cce325fa29bf", "polygon", "0x152311da0f562e37e0c3c27c213ba9df39cb0bff", "43", "9_525.03"],
      ["0xc99fe80056375977d39a38a498d4d9e5f8deec42", "linea", "0xec76a792adb5db162ac599d82fe1d3dddd45eb73", "44", "11_224.45"],
      ["0x461cab446731afa993e1477e91af342fe2a727bf", "base", "0x1bb53dc3741c85d58e454addddde2f26b4cec9b7", "45", "29_961.61"],
      ["0x461cab446731afa993e1477e91af342fe2a727bf", "base", "0xe85c48401f5964bbaa5b50cb799d668c8ed1f3ef", "46", "200_838.79"],
      ["0x461cab446731afa993e1477e91af342fe2a727bf", "base", "0xb8d5683d58260458db419b1817791946bc4f45e6", "47", "9_220.70"],
      ["0xc6f45197629cfd42faad2e1f6a223b98fd017be6", "arbitrum", "0x98134802ef46c049f1c69bf87ee088be1a60137a", "48", "1.86"],
      ["0x461cab446731afa993e1477e91af342fe2a727bf", "optimism", "0xdd665a6815182ca7a294ec03867637217097e510", "49", "15_632.74"],
      ["0x461cab446731afa993e1477e91af342fe2a727bf", "base", "0xf6a410adf236b0085d817b7aef6b79e62b5d01b0", "50", "165_391.51"],
      ["0xfb74b78ade25a14b4bf2198e8e480b29fb8af5d3", "arbitrum", "0xc1c0b4124b09783ddb96eda844cd3d3ae75c31b7", "51", "73_213.57"],
      ["0x461cab446731afa993e1477e91af342fe2a727bf", "base", "0xdef66c6c178087fd931514e99b04479e4d3d956c", "52", "153_378.14"],
      ["0x461cab446731afa993e1477e91af342fe2a727bf", "optimism", "0x0d04ed18f84e1329a8fc6b6680628d1df683676f", "53", "44_017.75"],
      ["0xfb74b78ade25a14b4bf2198e8e480b29fb8af5d3", "arbitrum", "0xcd67e90f338659fbeffbaed356fdcd89c2f1eab9", "54", "32_310.68"],
      ["0x9994d74b2b5a50d98fcc367172a1cce325fa29bf", "polygon", "0xdec01dd6c0eb5267a2859a35b1fef77780ed1e44", "55", "18_681.94"],
      ["0x461cab446731afa993e1477e91af342fe2a727bf", "optimism", "0x3163f0e60923febef3acffb4e4c9617fc43cd0ad", "56", "12_110.56"],
      ["0x461cab446731afa993e1477e91af342fe2a727bf", "base", "0x45774be7a459219553802b6a89535e9651b3f00d", "57", "57_741.96"],
      ["0x9994d74b2b5a50d98fcc367172a1cce325fa29bf", "polygon", "0x0652d554ab48e6b75f37ee9f700dabd84993f8cd", "58", "25_599.04"],
      ["0x9994d74b2b5a50d98fcc367172a1cce325fa29bf", "polygon", "0xa98225812c6be9e4dcacc3d3c2384514cd52379d", "59", "19_262.25"],
      ["0x12467a66b68791f4a7ad4e360d37d16f491ce6af", "ethereum", "0xf5ca5a74c6f9fc23880865d8702eaeda6e1f38b1", "60", "143_574.38"],
      ["6vmb75P6PXDEbXmYHTRRbX6RMUy1ekxbAjGPv3ssa16M", "solana", "SOL", "61", "3.292"], # puts ("16.1389".to_d / "0.10303928".to_d * "0.02102".to_d).to_s("F")
      ["CT3yqWQN7ofGhoS9PdZMSRzA368iTBS4WxTXCu3hWhRQ", "solana", "SOL", "62", "15.500"], # puts ("16.1389".to_d / "0.10303928".to_d * "0.09896".to_d).to_s("F")
      ["0x461cab446731afa993e1477e91af342fe2a727bf", "base", "0xd9aaec86b65d86f6a7b5b1b0c42ffa531710b6ca", "64", "18_604.57"],
      ["0x461cab446731afa993e1477e91af342fe2a727bf", "base", "0x833589fcd6edb6e08f4c7c32d4f71b54bda02913", "65", "7.136"], # puts ("21270.9884".to_d / "21274.564996".to_d * "7.137035".to_d).to_s("F")
      ["0xa2b8a656f9217a0d72dbd1c2175ea654f4600941", "base", "0x833589fcd6edb6e08f4c7c32d4f71b54bda02913", "66", "21253.974"], # puts ("21270.9884".to_d / "21274.564996".to_d * "21257.547885".to_d).to_s("F")
      ["0xc6f45197629cfd42faad2e1f6a223b98fd017be6", "polygon", "0x3c499c542cef5e3811e1192ce70d8cc03d5c3359", "68", "0.58"],
      ["CT3yqWQN7ofGhoS9PdZMSRzA368iTBS4WxTXCu3hWhRQ", "solana", "Es9vMFrzaCERmJfrF4H2FYD4KCoNkY11McCe8BenwNYB", "69", "1.98"],
      ["0xc99fe80056375977d39a38a498d4d9e5f8deec42", "linea", "0xa219439258ca9da29e9cc4ce5596924745e12b93", "71", "1.19"],
      ["0x461cab446731afa993e1477e91af342fe2a727bf", "avalanche", "0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7", "72", "4_059.54"],
      ["0x461cab446731afa993e1477e91af342fe2a727bf", "base", "0x4200000000000000000000000000000000000006", "73", "18_179.93"],
      ["0xc6f45197629cfd42faad2e1f6a223b98fd017be6", "arbitrum", "0x82af49447d8a07e3bd95bd0d56f35241523fbab1", "74", "13.35"],
      ["0xc6f45197629cfd42faad2e1f6a223b98fd017be6", "polygon", "0x04565fe9aa3ae571ada8e1bebf8282c4e5247b2a", "75", "0.01"],
      ["0xc6f45197629cfd42faad2e1f6a223b98fd017be6", "arbitrum", "0x5979d7b546e38e414f7e9822514be443a4800529", "76", "18.84"]
    ].map { |e|
      {
        address: e[0],
        exchange: e[1],
        contract: e[2],
        line: e[3],
        euro_inventory: e[4]&.to_d
      }
    }
  end

  # Populate line number of important accounts.
  def update_treso_xlsx_line
    data = apesu_treso_2024.find do |t|
      t[:address] == address &&
        t[:exchange] == exchange &&
        t[:contract] == contract
    end

    return if data.blank?

    self.treso_xlsx_line = data[:line] if data[:line].present?
    self.euro_inventory = data[:euro_inventory] if data[:euro_inventory].present?

    save!
  end

  # List of pool accounts where clients funds are stored but needs to be ignored from the balance.
  # @see pool_recap (2026-06-10 Cyrille).xlsx
  def pool_balances
    # Token address, Balance, Exchange, Pool address (ExchangeAccount)
    [
      # Token 1
      ["0x912ce59144191c1204e64559fe8253a0e49e6548", "10658.8106374897", "arbitrum", "0x0c0add0f4d8858516075ebaf8cb1b98d1b33741a"],
      ["0x82af49447d8a07e3bd95bd0d56f35241523fbab1", "2.42731197547494", "arbitrum", "0xc54ba936c9e40e5c5d31c241e8e3cba90e0084e6"],
      ["0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48", "2681.065126", "ethereum", "0x5ee945d9bfa5ad48c64fc7acfed497d3546c0d03"],
      ["0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48", "603.897694", "ethereum", "0xa441cf75bcfb5833cb1ba7c93a0721ae9b292789"],
      ["0xd988097fb8612cc24eec14542bc03424c656005f", "257.365128", "mode", "0x1256a3384ec8ec15edffd765dc3cd2b9b6c6401a"],
      ["0x4200000000000000000000000000000000000006", "0.0000254453", "mode", "0xf08c2805c831a8b7269c4032f8864e2e7ec74c70"],
      ["0x2791bca1f2de4661ed88a30c99a7a9449aa84174", "7064.323235", "polygon", "0x0e58b97a209526d6c85fff215f48284be9611c8a"],
      ["0x0d500b1d8e8ef31e21c99d1db9a6444d3adf1270", "2345.26929229851", "polygon", "0x3fbf7753ff5b217ca8ffbb441939c20bf3ec3be1"],
      ["0x2791bca1f2de4661ed88a30c99a7a9449aa84174", "8566.95576404255", "polygon", "0x6bb19ff73cd6b35757f973ecf9541f2ef20e2555"],
      ["0x2791bca1f2de4661ed88a30c99a7a9449aa84174", "22236.677196", "polygon", "0xf5278c0c7572f40127d09399abe6e1305c088106"],
      # Token 2
      ["0xaf88d065e77c8cc2239327c5edb3a432268e5831", "12164.843438", "arbitrum", "0x0c0add0f4d8858516075ebaf8cb1b98d1b33741a"],
      ["0xaf88d065e77c8cc2239327c5edb3a432268e5831", "5075.287191", "arbitrum", "0xc54ba936c9e40e5c5d31c241e8e3cba90e0084e6"],
      ["0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2", "1.17132194160318", "ethereum", "0x5ee945d9bfa5ad48c64fc7acfed497d3546c0d03"],
      ["0xdac17f958d2ee523a2206206994597c13d831ec7", "816.15107", "ethereum", "0xa441cf75bcfb5833cb1ba7c93a0721ae9b292789"],
      ["0xf0f161fda2712db8b566946122a5af183995e2ed", "0.0624", "mode", "0x1256a3384ec8ec15edffd765dc3cd2b9b6c6401a"],
      ["0x80137510979822322193fc997d400d5a6c747bf7", "0.0000235183", "mode", "0xf08c2805c831a8b7269c4032f8864e2e7ec74c70"],
      ["0xc2132d05d31c914a87c6611c10748aeb04b58e8f", "6328.893147", "polygon", "0x0e58b97a209526d6c85fff215f48284be9611c8a"],
      ["0x2791bca1f2de4661ed88a30c99a7a9449aa84174", "1652.816287", "polygon", "0x3fbf7753ff5b217ca8ffbb441939c20bf3ec3be1"],
      ["0x7ceb23fd6bc0add59e62ac25578270cff1b9f619", "4.31474015336857", "polygon", "0x6bb19ff73cd6b35757f973ecf9541f2ef20e2555"],
      ["0x3c499c542cef5e3811e1192ce70d8cc03d5c3359", "5277.355755", "polygon", "0xf5278c0c7572f40127d09399abe6e1305c088106"]
    ].map { |e|
      {
        contract: e[0],
        balance: e[1],
        exchange: e[2],
        address: e[3]
      }
    }
  end

  # Create records from current DB and important accounts list from XLSX.
  def apesu_populate
    w
      .exchange_accounts
      .each do |ea|
      puts ea.name

      exchange_assets = ExchangeAsset
        .joins(:movements)
        .shared
        .where(movements: {exchange_account_id: ea.id})
        .distinct

      exchange_assets += ExchangeAsset.where(
        exchange_name: ea.exchange_name,
        remote_id: apesu_treso_2024
        .filter { |at2| at2[:address] == ea.public_address.downcase && at2[:exchange] == ea.exchange_name }
        .map { |at2| at2[:contract] }
      ).shared

      exchange_assets.each do |easset|
        if !Apesu.where(exchange_account_id: ea.id, exchange_asset_id: easset.id).exists?
          Apesu.create!(
            name: ea.name,
            exchange: ea.exchange_name,
            address: ea.public_address,
            contract: easset.remote_id,
            contract_type: easset.contract_type,
            exchange_account_id: ea.id,
            exchange_asset_id: easset.id,
            movements_count_2025: ea.movements.where(exchange_asset_id: easset.id, time: time_a..time_b).count
          )
        end
      end
    end
  end

  # Find and list duplicates.
  def apesu_duplicates
    duplicates = Apesu
      .select(
        :exchange_account_id,
        :exchange_asset_id,
        :exchange,
        "COUNT(*) AS duplicates_count"
      )
      .group(
        :exchange_account_id,
        :exchange_asset_id,
        :exchange
      )
      .having("COUNT(*) > 1")

    duplicates.each do |row|
      puts [
        row.exchange_account_id,
        row.exchange_asset_id,
        row.exchange,
        row.duplicates_count
      ].join(" | ")
    end
  end

  # reset: movements.where(info: "Adjustment Entry (state eoh)").delete_all
  def adjust_balance_by_day_solana(dry_run: false)
    s3 = Object.new
    s3.extend(Utils::S3)

    info = "Adjustment Entry (state eod)"
    ea = exchange_account
    easset = exchange_asset

    stop = w.tz.yesterday.end_of_day
    ms = movements
      .where(time: ..stop)
      .order(time: :asc)
    bar = ProgressBar.new(ms.count)

    balance_computed = 0
    previous_time = nil
    adjustment_last = movements.where(info: info).order(time: :asc).last
    ms.to_a.each { |m|
      bar.increment!

      balance_computed += m.amount

      if m.time < time_init
        next
      end
      if m.manual
        next
      end
      if m.info.to_s.include?("Adjustment Entry")
        next
      end
      if adjustment_last.present? && m.time < adjustment_last.time
        next
      end

      if previous_time.nil? || previous_time.in_time_zone(w.tz).day == m.time.in_time_zone(w.tz).day
        previous_time = m.time
        next
      end

      # Avoid duplicate adjustement
      local_identifier = "adjustement_entry:#{easset.id}:eod:#{previous_time.in_time_zone(w.tz).end_of_day}"
      if ea.movements.where(local_identifier: local_identifier).exists?
        previous_time = m.time
        next
      end

      filename, index = m.local_identifier.split(":")
      file = s3.retrieve(name: filename)

      if easset.remote_id == "SOL"
        json = JSON.parse(file.read)
        account_index = json
          .dig("result", "data")
          &.[](index.to_i)
          &.dig("transaction", "message", "accountKeys")
          &.find_index { |a| a["pubkey"] == ea.public_address }
        raise "can't find account index #{m.id} " if account_index.nil?
        ui_amount_str = json
          .dig("result", "data")
          &.[](index.to_i)
          &.dig("meta", "preBalances")
          &.[](account_index.to_i)
          &.to_d&.* 10**-9
      else
        pre_balances = JSON.parse(file.read)
          .dig("result", "data")
          &.[](index.to_i)
          &.dig("meta", "preTokenBalances")
        ui_amount_str = if pre_balances == []
          0.to_d
        else
          pre_balances
            &.find { |e| e["owner"] == ea.public_address && e["mint"] == easset.remote_id }
            &.dig("uiTokenAmount", "uiAmountString")
        end
      end

      File.delete(file.path)
      raise "ui amount not found #{m.id}" if ui_amount_str.nil?

      balance_real = ui_amount_str.to_d

      balance_computed -= m.amount
      balance_diff = balance_real - balance_computed
      puts [
        balance_computed.to_s("F"),
        balance_real.to_s("F"),
        balance_diff.to_s("F"),
        previous_time
      ].inspect

      if balance_diff.nonzero?
        now = Time.now
        if !dry_run
          Movement.create!({
            amount: balance_diff,
            blockchain_txid: nil,
            created_at: now,
            etl_import_id: nil,
            exchange_account_id: ea.id,
            exchange_asset_id: easset.id,
            exchange_asset_remote_id: easset.remote_id,
            fiat_amount: nil,
            from_address: nil,
            id: SecureRandom.uuid,
            import_source_id: nil,
            info: info,
            internal_transfer: false,
            justified: false,
            local_identifier: local_identifier,
            manual: false,
            manually_modified: false,
            market_pair: nil,
            note: "A reconciliation entry added to match the on-chain balance.",
            quantity_after_movement: nil,
            remote_identifier: nil,
            remote_txid: nil,
            time: previous_time.in_time_zone(w.tz).end_of_day,
            to_address: nil,
            trade_in_id: nil,
            transaction_kind_id: nil,
            tx_id: nil,
            updated_at: now,
            vat_percentage: 0,
            wac: nil,
            workspace_id: ea.workspace_id
          })
        end

        balance_computed += balance_diff
      end

      previous_time = m.time
    }

    movements.where(info: "Adjustment Entry (state eoh)").count
  end

  # Adjust the balance of the last movement if the exchange account has no new movements since
  # the last crawl minus 24h.
  def adjust_balance_end_solana
    ea = exchange_account
    easset = exchange_asset
    s3 = Object.new
    s3.extend(Utils::S3)

    return if ea.exchange_name != "solana"

    m = movements
      .order(time: :asc)
      .last
    return if m.time > (ea.crawled_at - 1.day)
    return if m.info.to_s.include?("Adjustment Entry")

    balance_computed = movements.pluck(:amount).sum
    filename, index = m.local_identifier.split(":")
    file = s3.retrieve(name: filename)
    if easset.remote_id == "SOL"
      json = JSON.parse(file.read)
      account_index = json
        .dig("result", "data")
        &.[](index.to_i)
        &.dig("transaction", "message", "accountKeys")
        &.find_index { |a| a["pubkey"] == ea.public_address }
      raise "can't find account index" if account_index.nil?
      ui_amount_str = json
        .dig("result", "data")
        &.[](index.to_i)
        &.dig("meta", "postBalances")
        &.[](account_index.to_i)
        &.to_d&.* 10**-9
    else
      ui_amount_str = JSON.parse(file.read)
        .dig("result", "data")
        &.[](index.to_i)
        &.dig("meta", "postTokenBalances")
        &.find { |e| e["owner"] == ea.public_address && e["mint"] == easset.remote_id }
        &.dig("uiTokenAmount", "uiAmountString")
    end
    File.delete(file.path)
    raise "ui amount not found" if ui_amount_str.nil?

    balance_real = ui_amount_str.to_d
    balance_diff = balance_real - balance_computed

    if balance_diff.nonzero?
      now = Time.now
      Movement.create!({
        amount: balance_diff,
        blockchain_txid: nil,
        created_at: now,
        etl_import_id: nil,
        exchange_account_id: ea.id,
        exchange_asset_id: easset.id,
        exchange_asset_remote_id: easset.remote_id,
        fiat_amount: nil,
        from_address: nil,
        id: SecureRandom.uuid,
        import_source_id: nil,
        info: "Adjustment Entry",
        internal_transfer: false,
        justified: false,
        local_identifier: "adjustement_entry:#{easset.id}:#{m.id}",
        manual: false,
        manually_modified: false,
        market_pair: nil,
        note: "A reconciliation entry added to match the on-chain balance.",
        quantity_after_movement: nil,
        remote_identifier: nil,
        remote_txid: nil,
        time: m.time + 1,
        to_address: nil,
        trade_in_id: nil,
        transaction_kind_id: nil,
        tx_id: nil,
        updated_at: now,
        vat_percentage: 0,
        wac: nil,
        workspace_id: ea.workspace_id
      })
    end

    [balance_computed, balance_real]
  end

  # reset: movements.where(info: "Adjustment Entry (state eoh)").delete_all
  def adjust_balance_by_hour
    s3 = Object.new
    s3.extend(Utils::S3)

    ea = exchange_account
    easset = exchange_asset

    ms = movements
      .where(time: ..time_b)
      .order(time: :asc)
    bar = ProgressBar.new(ms.count)

    balance_computed = 0
    previous_block = nil
    previous_time = nil
    adjustment_last = movements.where(info: "Adjustment Entry (state eoh)").order(time: :asc).last
    ms.to_a.each { |m|
      bar.increment!

      balance_computed += m.amount

      if m.time < time_init
        next
      end
      if m.manual
        next
      end
      if m.info.to_s.include?("Adjustment Entry")
        next
      end
      if adjustment_last.present? && m.time < adjustment_last.time
        next
      end

      block = lambda {
        filename, index = m.local_identifier.split(":")
        file = s3.retrieve(name: filename)
        entry = JSON.parse(file.read).dig("result").to_a[index.to_i]
        File.delete(file.path)
        entry["blockNumber"]
      }
      if previous_time.nil? || previous_time.in_time_zone(w.tz).hour == m.time.in_time_zone(w.tz).hour
        previous_time = m.time
        previous_block = block
        next
      end

      # Avoid duplicate adjustement
      local_identifier = "adjustement_entry:#{easset.id}:eoh:#{previous_time.in_time_zone(w.tz).end_of_hour}"
      if ea.movements.where(local_identifier: local_identifier).exists?
        previous_time = m.time
        previous_block = block
        next
      end

      previous_block_number = previous_block.call
      balance_real = etherscan_balance(previous_block_number)
      sleep(0.5)

      balance_real *= 10**-easset.token_decimals
      balance_diff = balance_real - balance_computed
      puts [
        balance_computed.to_s("F"),
        balance_real.to_s("F"),
        balance_diff.to_s("F"),
        previous_time
      ].inspect

      if balance_diff.nonzero?
        now = Time.now
        Movement.create!({
          amount: balance_diff,
          blockchain_txid: nil,
          created_at: now,
          etl_import_id: nil,
          exchange_account_id: ea.id,
          exchange_asset_id: easset.id,
          exchange_asset_remote_id: easset.remote_id,
          fiat_amount: nil,
          from_address: nil,
          id: SecureRandom.uuid,
          import_source_id: nil,
          info: "Adjustment Entry (state eoh)",
          internal_transfer: false,
          justified: false,
          local_identifier: local_identifier,
          manual: false,
          manually_modified: false,
          market_pair: nil,
          note: "A reconciliation entry added to match the on-chain balance (block: #{previous_block_number}).",
          quantity_after_movement: nil,
          remote_identifier: nil,
          remote_txid: nil,
          time: previous_time.in_time_zone(w.tz).end_of_hour,
          to_address: nil,
          trade_in_id: nil,
          transaction_kind_id: nil,
          tx_id: nil,
          updated_at: now,
          vat_percentage: 0,
          wac: nil,
          workspace_id: ea.workspace_id
        })

        balance_computed += balance_diff
      end

      previous_time = m.time
      previous_block = block
    }

    movements.where(info: "Adjustment Entry (state eoh)").count
  end

  # reset: movements.where(info: "Adjustment Entry (state eod)").delete_all
  def adjust_balance_by_day
    return if !EVMS.include?(exchange)
    return if !movements.exists?

    s3 = Object.new
    s3.extend(Utils::S3)

    ea = exchange_account
    easset = exchange_asset
    info = "Adjustment Entry (state eod)"

    start = balance_adjustment_last_update
    if start.nil?
      start = movements.order(time: :asc).first.time
    end
    stop = movements.order(time: :asc).last.time
    days = []
    Time.use_zone(w.tz) do
      current_time = start.in_time_zone.end_of_day
      final_time = stop.in_time_zone

      while current_time <= final_time
        days << current_time
        current_time += 1.day
      end
    end

    ProgressBar.new(days.count)

    days.each { |day|
      block = etherscan_block_number(day)
      balance_computed = movements.where(time: ..day).sum(:amount)
      sleep(0.5)
      balance_real = etherscan_balance(block)
      balance_real *= 10**-easset.token_decimals
      balance_diff = balance_real - balance_computed

      # Avoid duplicate adjustement
      local_identifier = "adjustement_entry:#{easset.id}:eod:#{day.to_i}"

      puts [
        balance_computed.to_s("F"),
        balance_real.to_s("F"),
        balance_diff.to_s("F"),
        day
      ].inspect

      if balance_diff.nonzero?
        now = Time.now
        Movement.create!({
          amount: balance_diff,
          blockchain_txid: nil,
          created_at: now,
          etl_import_id: nil,
          exchange_account_id: ea.id,
          exchange_asset_id: easset.id,
          exchange_asset_remote_id: easset.remote_id,
          fiat_amount: nil,
          from_address: nil,
          id: SecureRandom.uuid,
          import_source_id: nil,
          info: info,
          internal_transfer: false,
          justified: false,
          local_identifier: local_identifier,
          manual: false,
          manually_modified: false,
          market_pair: nil,
          note: "A reconciliation entry added to match the on-chain balance (block: #{block}).",
          quantity_after_movement: nil,
          remote_identifier: nil,
          remote_txid: nil,
          time: day,
          to_address: nil,
          trade_in_id: nil,
          transaction_kind_id: nil,
          tx_id: nil,
          updated_at: now,
          vat_percentage: 0,
          wac: nil,
          workspace_id: ea.workspace_id
        })
      end

      update_column(:balance_adjustment_last_update, day)
    }

    movements.where(info: info).count
  end

  # reset: movements.where(info: "Adjustment Entry (state)").delete_all
  def adjust_balance_by_tx(dry_run: false)
    s3 = Object.new
    s3.extend(Utils::S3)
    s3_cache = {}

    ea = exchange_account
    easset = exchange_asset

    ms = movements
      .order(time: :asc, amount: :asc, id: :asc)
    bar = ProgressBar.new(ms.count)

    balance_computed = 0
    array = ms.to_a
    array.each_with_index { |m, m_index|
      bar.increment!

      balance_computed += m.amount

      if balance_adjustment_last_update.present?
        if m.time < balance_adjustment_last_update
          next
        end
      end
      if m.time < time_init
        next
      end
      if m.time == array[m_index + 1]&.time
        next
      end
      if m.manual
        next
      end
      if m.info.to_s.include?("Adjustment Entry")
        next
      end

      filename, index = m.local_identifier.split(":")
      if s3_cache[filename].present?
        file = s3_cache[filename]
        file.rewind
      else
        file = s3.retrieve(name: filename)
        s3_cache[filename] = file
      end

      if ea.exchange_name == "solana"
        ui_amount_str = JSON.parse(file.read)
          .dig("result", "data")
          &.[](index.to_i)
          &.dig("meta", "postTokenBalances")
          &.find { |e| e["owner"] == ea.public_address && e["mint"] == easset.remote_id }
          &.dig("uiTokenAmount", "uiAmountString")

        next if ui_amount_str.nil?
        balance_real = ui_amount_str.to_d

        balance_real - balance_computed
      else
        entry = JSON.parse(file.read).dig("result").to_a[index.to_i]
        block = entry["blockNumber"]

        balance_real = etherscan_balance(block).to_d
        sleep(0.5)

        balance_real *= 10**-easset.token_decimals
      end

      balance_diff = balance_real - balance_computed

      puts [
        balance_computed.to_s("F"),
        balance_real.to_s("F"),
        balance_diff.to_s("F")
      ].inspect

      if balance_diff.nonzero?
        if !dry_run
          now = Time.now
          Movement.create!({
            amount: balance_diff,
            blockchain_txid: m.blockchain_txid,
            created_at: now,
            etl_import_id: nil,
            exchange_account_id: ea.id,
            exchange_asset_id: easset.id,
            exchange_asset_remote_id: easset.remote_id,
            fiat_amount: nil,
            from_address: nil,
            id: SecureRandom.uuid,
            import_source_id: nil,
            info: "Adjustment Entry (state)",
            internal_transfer: false,
            justified: false,
            local_identifier: "#{m.local_identifier}:state",
            manual: false,
            manually_modified: false,
            market_pair: nil,
            note: "A reconciliation entry added to match the on-chain balance (block: #{block}).",
            quantity_after_movement: nil,
            remote_identifier: m.remote_identifier,
            remote_txid: m.remote_txid,
            time: m.time,
            to_address: nil,
            trade_in_id: nil,
            transaction_kind_id: nil,
            tx_id: nil,
            updated_at: now,
            vat_percentage: 0,
            wac: nil,
            workspace_id: ea.workspace_id
          })
        end

        balance_computed += balance_diff
      end
    }

    s3_cache.each { |f|
      begin
        File.delete(f.path)
      rescue
        nil
      end
    }

    update_column(:balance_adjustment_last_update, array.last.time)
    movements.where(info: "Adjustment Entry (state)").count
  end

  def self.marked_deleted_previous_imported_data
    # Apesu.with_euro_inventory.where(full_import: true)
    where(id: [249, 981, 989, 1054, 1348, 2130, 2302, 2588, 2629])
  end

  def delete_previous_data
    count = movements.where(time: ...time_init).count
    puts "Mark as deleted #{count} records?"
    puts "Apesu(#{id}), #{symbol} (#{exchange})"
    puts "Are you sure? (yes/no)"
    ok = gets.strip
    if ok == "yes"
      puts "Deleting..."
      movements.where(time: ...time_init).update_all(deleted: true)
    else
      puts "Abort."
    end
  end

  def adjust_euro_value
    if euro_inventory.present?
      adjustment_init = movements.where(info: "Adjustment Entry")
      if adjustment_init.count != 1
        if movements.where("info ilike 'Adjustment Entry%'").exists?
          raise "One and only 1 initial balance adjustment must exist for Apesu(#{id})."
        end
        return
      end

      adjustment_init = adjustment_init.first
      adjustment_init.fiat_amount = euro_inventory
      adjustment_init.save
    end
  end

  def self.fix_trades(exs = EVMS)
    threshold_pct = if exs == "solana"
      0.15
    else
      0.20
    end

    ids = Apesu
      .where(exchange: exs)
      .where.not(exchange: "hyperliquid")
      .find_each.map { |a| a.exchange_account_id }
      .uniq
    exchange_accounts = ExchangeAccount
      .where(id: ids)
      .order(movements_count: :desc)

    bar = ProgressBar.new(exchange_accounts.count)
    exchange_accounts.each do |ea|
      puts
      puts "ExchangeAccount.find(\"#{ea.id}\")"
      ea
        .movements
        .uncategorized_or_with_kind
        .group_by { |m| m.remote_identifier }
        .to_a
      identifiers = ea.movements
        .uncategorized_or_with_kind
        .where(time: Apesu.first.time_a..Apesu.first.time_b)
        .group(:remote_identifier)
        .having("count(*) > 1")
        .pluck(:remote_identifier)

      movements_by_identifier = ea.movements
        .uncategorized_or_with_kind
        .where(remote_identifier: identifiers)
        .select(:id, :amount, :remote_identifier, :time, :exchange_asset_id, :workspace_id)
        .group_by(&:remote_identifier)

      bad_ones = movements_by_identifier.count { |ri, movements|
        movements.count.odd? || movements.pluck(:amount).count(&:positive?) != movements.count / 2
      }
      to_check_ones = movements_by_identifier.reject { |ri, movements|
        movements.count.odd? || movements.pluck(:amount).count(&:positive?) != movements.count / 2
      }
      puts "Bad ones: #{bad_ones}"

      to_check_ones_neg_and_pos = {}
      to_check_ones.each do |remote_identifier, movements|
        to_check_ones_neg_and_pos[remote_identifier] = {
          negatives: movements
            .select { |m| m.amount.negative? }
            .map { |m| [m, m.fiat_change_rate * m.amount] }
            .sort_by { |_, fiat_value| fiat_value.abs },
          positives: movements
            .select { |m| m.amount.positive? }
            .map { |m| [m, m.fiat_change_rate * m.amount] }
            .sort_by { |_, fiat_value| fiat_value.abs }
        }
      end
      no_match_left = []
      to_check_ones_neg_and_pos.each do |remote_identifier, el|
        negatives = el[:negatives]
        positives = el[:positives]
        is_good = true

        negatives.each_with_index do |(negative, n_fiat_value), index|
          closest_positive = positives[index]

          if closest_positive
            _, p_fiat_value = closest_positive
            threshold = [n_fiat_value.abs, p_fiat_value.abs].max * threshold_pct

            if (n_fiat_value.abs - p_fiat_value.abs).abs <= threshold
            else
              # fail the threshold
              puts [n_fiat_value, p_fiat_value].map { |d| d.to_s("F") }.inspect
              is_good = false
              # fail_the_threshold += [{remote_identifier => el}]
              break
            end
          else
            is_good = false
            no_match_left += [{remote_identifier => el}]
            break
          end
        end

        if is_good
          negatives.each_with_index do |(negative, _), index|
            Movement
              .where(id: negative.id)
              .update_all(
                transaction_kind_id: "9caa4580-0731-49f6-b84f-2faf3d078255", # Cession lors d'un échange
                justified: true,
                trade_in_id: positives[index][0].id,
                etl_import_id: "77777777-7777-7777-7777-777777777777"
              )
          end
          Movement
            .where(id: positives.map { |m, _| m.id })
            .update_all(
              transaction_kind_id: "dd46812a-0499-4aea-91d7-d418ad410744", # Acquisition lors d'un échange
              justified: true,
              etl_import_id: "77777777-7777-7777-7777-777777777777"
            )
        end
      end
      # puts "Failed the threshold: #{fail_the_threshold.count}"

      bar.increment!
    end

    nil
  end

  def self.csv_report
    CSV.open("apesu-exports.csv", "wb") do |csv|
      csv << [
        "line",
        "name",
        "exchange",
        "address",
        "symbol",
        "contract",
        "contract_type",
        "exchange_account_id",
        "exchange_asset_id",
        "initial block (2025)",
        "initial block balance (2025)",
        "final block (2025)",
        "final block balance (2025)",
        "balance start in decimal (2025)",
        "balance end in decimal (2025)",
        "balance adjustment method",
        "value start (2025)",
        "value end (2025)",
        "movements count for 2025",
        "import type",
        "spam"
      ]

      time_init = Apesu.first.time_init
      time_b = Apesu.first.time_b

      as = Apesu
        .order(:treso_xlsx_line, :name, :exchange)
      bar = ProgressBar.new(as.count)
      as.each { |a|
        value_start = a.movements.order(:time).where(time: time_init).first&.wac.to_d.to_s("F")
        last_movement = a.movements.order(:time).where(time: ..time_b).last
        value_end = last_movement&.wac.to_d.to_s("F")
        csv << [
          a.treso_xlsx_line,
          a.name,
          a.exchange,
          a.address,
          a.symbol,
          a.contract,
          a.contract_type,
          a.exchange_account_id,
          a.exchange_asset_id,
          a.block_init_2025,
          a.block_init_balance_2025.to_i,
          a.block_stop_2025,
          a.block_stop_balance_2025.to_i,
          a.balance_start,
          a.balance_end,
          a.balance_adjustment_method,
          value_start,
          value_end,
          a.movements_count_2025,
          a.full_import,
          a.spam
        ]
        bar.increment!
      }
    end
  end
end