# Steps:
# Apesu.init_table                                       # create table
# Apesu.apesu_populate                                   # create records
# Apesu.find_each{|a| a.update_block_init }              # copy details from class
# Apesu.find_each{|a| a.update_block_stop }              # copy details from class
# Apesu.find_each{|a| a.update_treso_xlsx_line   }       # copy details from class
# 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 = Apesu.find(<id>).adjust_balance_start
# apesu = Apesu.find(<id>).adjust_balance_by_hour
# or
# apesu = Apesu.find(<id>).adjust_balance_by_tx
# apesu = Apesu.find(<id>).adjust_balance_end
# apesu = Apesu.find(<id>).adjust_euro_value
#
# Apesu.fix_trades
# Apesu.fix_trades("solana")
#
# Apesu.find_each{|a| a.update_report_fields }           # copy details from DB
# Apesu.csv_report
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"
  ]

  # 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_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}"
    end
  end

  def self.mig_table
    ActiveRecord::Schema.define do
      change_table :apesu, bulk: true do |t|
        t.string :euro_inventory
      end
    end
    Apesu.reset_column_information
  end

  def self.init_table
    conn = ActiveRecord::Base.connection

    conn.create_table :apesu, force: false do |t|
      t.uuid :exchange_account_id
      t.uuid :exchange_asset_id
      t.string :name
      t.string :address
      t.string :exchange
      t.string :contract
      t.string :contract_type
      t.string :block_init_2025
      t.string :block_init_balance_2025
      t.string :block_stop_2025
      t.string :block_stop_balance_2025
      t.string :symbol
      t.string :balance_adjustment_method
      t.bigint :movements_count_2025
      t.decimal :balance_start
      t.decimal :balance_end
      t.integer :treso_xlsx_line
      t.boolean :full_import
      t.boolean :spam

      t.timestamps
    end
    Apesu.reset_column_information
  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
    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.where(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 self.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 self.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.
  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

  # 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_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

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

      balance_real = 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}).",
          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)").delete_all
  def adjust_balance_by_tx
    s3 = Object.new
    s3.extend(Utils::S3)
    s3_cache = {}

    ea = exchange_account
    easset = exchange_asset

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

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

      balance_computed += m.amount

      if m.time < time_init
        next
      end
      if m.time == movements[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
      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 = 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"),
      ].inspect

      if balance_diff.nonzero?
        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,
        })

        balance_computed += balance_diff
      end
    }

    s3_cache.each{|f| File.delete(f.path) rescue nil }
    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}\")"
      groups = ea
        .movements
        .uncategorized_or_with_kind
        .group_by{|m| m.remote_identifier}
        .to_a
ides = [
    "3zTPrjviNCnLqwNsroBZKfq6s3rzvRbD2Zsrc1ENB2xaECdnU3wur9rUi85MneDbqFSFUy2YtJM9SCrgHkukpDmN",
    "3zTPrjviNCnLqwNsroBZKfq6s3rzvRbD2Zsrc1ENB2xaECdnU3wur9rUi85MneDbqFSFUy2YtJM9SCrgHkukpDmN",
    "4MgmPMSLUYKco4pWFJsYChVxx7oQsNcmqSzbQNYc7HiVL3uWfgFAoy7rte4ftAA9NXxp4pJPMRbAQZeQupCnXhBB",
    "4MgmPMSLUYKco4pWFJsYChVxx7oQsNcmqSzbQNYc7HiVL3uWfgFAoy7rte4ftAA9NXxp4pJPMRbAQZeQupCnXhBB",
    "5Vo5kRXTrekwPvR5EDvMw1FKftFG7Mb61skE2zLBDn22ymebQ33bVbmfw6V6A56m6GekVjicQkkquXMyqEpDekSf",
    "5Vo5kRXTrekwPvR5EDvMw1FKftFG7Mb61skE2zLBDn22ymebQ33bVbmfw6V6A56m6GekVjicQkkquXMyqEpDekSf",
    "2uLvbZYt6aVDQLX8GUMKP7Q98i2uuAoJmKQuDxqYSfhZutNud42sTh9AhyHchBxpSm1hxs8YTnTXiqfMT4H62z8q",
    "2uLvbZYt6aVDQLX8GUMKP7Q98i2uuAoJmKQuDxqYSfhZutNud42sTh9AhyHchBxpSm1hxs8YTnTXiqfMT4H62z8q",
    "61svnadomQsviPzSXhHvmaZFLtVCdn3kKjFdqq4Z8r4tRhJYkub7XTAYSvu9FacAaWxs9QLUKkvJtNiV1ELNUXAT",
    "61svnadomQsviPzSXhHvmaZFLtVCdn3kKjFdqq4Z8r4tRhJYkub7XTAYSvu9FacAaWxs9QLUKkvJtNiV1ELNUXAT",
    "5xiumqFueXmrfwQ87dhEv9aHJZqGCcqkGBo1AWh5vpTvpGQB89JVQhnJe639o67LuPomp3GoJF9Rqdib8TZpVykY",
    "5xiumqFueXmrfwQ87dhEv9aHJZqGCcqkGBo1AWh5vpTvpGQB89JVQhnJe639o67LuPomp3GoJF9Rqdib8TZpVykY",
    "5zFW8oGuq59WZBfb4qRF88NgahYmzPFn6KVKocs8tjKmXR4ZLQdQZEXafP1j57p5P9fbnYy9VNDCJ5EkYHakwWb3",
    "5zFW8oGuq59WZBfb4qRF88NgahYmzPFn6KVKocs8tjKmXR4ZLQdQZEXafP1j57p5P9fbnYy9VNDCJ5EkYHakwWb3",
    "3CznkybPRo2eEu2Vc7Kp7dPqrkR6bbMyLAKYGtrCQ5KhqEWSH24z2NB2jmApHbsAFtKUqBUWHdQttE9q6u8YxCPV",
    "3CznkybPRo2eEu2Vc7Kp7dPqrkR6bbMyLAKYGtrCQ5KhqEWSH24z2NB2jmApHbsAFtKUqBUWHdQttE9q6u8YxCPV",
    "59EQoqkKP5CDxWN31AQafkXN3ad6p9XHV4uDRgf3PJUUwUk9DnjuzGGecsbLudM8rvQB952ZhbjxWxE3krnp5yjs",
    "59EQoqkKP5CDxWN31AQafkXN3ad6p9XHV4uDRgf3PJUUwUk9DnjuzGGecsbLudM8rvQB952ZhbjxWxE3krnp5yjs",
    "5qQbbqLrVqLPUD31mt2dVeZwtFDALE8F4PiWSH9iFLkMn2YqYJF7AMM29cSd8mfbr9PZdn3XCrYGAPggJkV5cpXo",
    "5qQbbqLrVqLPUD31mt2dVeZwtFDALE8F4PiWSH9iFLkMn2YqYJF7AMM29cSd8mfbr9PZdn3XCrYGAPggJkV5cpXo",
    "43zJfm93xYcyBX2TGzQApQCFBMvA3upUd9T8HMXuxCaZF2Hm7D2x9ZaTgeLkovuDMhKGVQLSzv9ezyfs72dCtteM",
    "43zJfm93xYcyBX2TGzQApQCFBMvA3upUd9T8HMXuxCaZF2Hm7D2x9ZaTgeLkovuDMhKGVQLSzv9ezyfs72dCtteM",
    "2toM2fSPQPtCC65WZcxWEdqEawj5VaruLtSzrFFrrz5aJsWMCRJJfXWDqxzUtkebeqmqgKvs1XCZNviwSWn2S7Fm",
    "2toM2fSPQPtCC65WZcxWEdqEawj5VaruLtSzrFFrrz5aJsWMCRJJfXWDqxzUtkebeqmqgKvs1XCZNviwSWn2S7Fm",
    "5YvQB8Gk8X9hZy3HSJX6ZzSTGja8RUUzNvYzUd1V1jYrtpjfgtDQBg4XNd3uMLZd1Hetg2MeA5PChVdrWFC1giZG",
    "5YvQB8Gk8X9hZy3HSJX6ZzSTGja8RUUzNvYzUd1V1jYrtpjfgtDQBg4XNd3uMLZd1Hetg2MeA5PChVdrWFC1giZG",
    "LHqfJdH5oXRAjmqXhyxtJYEYMsZFqH4kXyL2A55a7NEkjuisbzAiE8yQwcFn8VudgKSqS1gVpz5BZyMPfLhBa5B",
    "LHqfJdH5oXRAjmqXhyxtJYEYMsZFqH4kXyL2A55a7NEkjuisbzAiE8yQwcFn8VudgKSqS1gVpz5BZyMPfLhBa5B",
    "WBJDBM5jYGuDDdhChtpwbedD6Ponq65qBQaPNRAAdPHVJX3Nhv3sQNJ1rJFQJp4dt235yJnQAz2S2aWE4V2ZsKS",
    "WBJDBM5jYGuDDdhChtpwbedD6Ponq65qBQaPNRAAdPHVJX3Nhv3sQNJ1rJFQJp4dt235yJnQAz2S2aWE4V2ZsKS",
    "ugnExFuHnyfF5hjCFKucCQsMq6LohMqKAXqkyqVhjiRjsd5fUrcAVohk6yF68WC3sJGHTeg4w84iUCmi322KWsc",
    "ugnExFuHnyfF5hjCFKucCQsMq6LohMqKAXqkyqVhjiRjsd5fUrcAVohk6yF68WC3sJGHTeg4w84iUCmi322KWsc",
    "np2z84quUDNF4b9NQJYjCcScNRAyiHejnbBHvtZWE8WDFsQFcaprmZa6zE6CCEtHTJ94oBVWF6M1cAZsDYBEN5W",
    "np2z84quUDNF4b9NQJYjCcScNRAyiHejnbBHvtZWE8WDFsQFcaprmZa6zE6CCEtHTJ94oBVWF6M1cAZsDYBEN5W",
    "36aVAKe82D8P2qoGFspPjZgwwvz7GEqbgMQmz1HBXCbUKxLDJFh5BWJrC1RLymMir7px16jQudiiMpZZXt8gygdP",
    "36aVAKe82D8P2qoGFspPjZgwwvz7GEqbgMQmz1HBXCbUKxLDJFh5BWJrC1RLymMir7px16jQudiiMpZZXt8gygdP",
    "f1U5K6ZHioPy2LRo5c5XKegKjthVfGzbNttT361vvUgrgeVeWVHbKNLiTYsWBnUeJAYZBUpTfE7w8TMBahuNgMz",
    "f1U5K6ZHioPy2LRo5c5XKegKjthVfGzbNttT361vvUgrgeVeWVHbKNLiTYsWBnUeJAYZBUpTfE7w8TMBahuNgMz",
    "4T1dbbTRnMs87ug3CrnTAEqkEefpG1nycUtuAmLtEBTwGqMvtFuJKbqPk46QE9yNRxEkxERPaY1d6HCVdkbZPdeY",
    "4T1dbbTRnMs87ug3CrnTAEqkEefpG1nycUtuAmLtEBTwGqMvtFuJKbqPk46QE9yNRxEkxERPaY1d6HCVdkbZPdeY",
    "4Y6fvngB7BtUL15wnK2nEtWLqch3H3vFBxGWmmMQ8pYdtN7EqhgNtuiyssjLoKXBwH3MgVoZvgMe3zueQMP8JHBf",
    "4Y6fvngB7BtUL15wnK2nEtWLqch3H3vFBxGWmmMQ8pYdtN7EqhgNtuiyssjLoKXBwH3MgVoZvgMe3zueQMP8JHBf",
    "5o6iPSY1CHeaZVZagnBieRMCCGsTcgMBiQWx49i76cD1rxnJbWKMiFeUasuemKQFQzbhecgdDAG1VKN5SCocSHMa",
    "5o6iPSY1CHeaZVZagnBieRMCCGsTcgMBiQWx49i76cD1rxnJbWKMiFeUasuemKQFQzbhecgdDAG1VKN5SCocSHMa",
    "4sthg299Zn7H3S72pPVZFxXjh1SxkhHLeqVKAdv4qXrNrKHwNB2w66t8BMPH7rvXpfxY5jpPZzfcDy8kkjtuwRm",
    "4sthg299Zn7H3S72pPVZFxXjh1SxkhHLeqVKAdv4qXrNrKHwNB2w66t8BMPH7rvXpfxY5jpPZzfcDy8kkjtuwRm",
    "3vDKvWmvDoRLnA8RoZaSf9HgxViqm6V4ncFbZm9es8FtphzwrGoYqi6zK3oYNGMzDXgAHnQeamE5BMAxmr7BUNvs",
    "3vDKvWmvDoRLnA8RoZaSf9HgxViqm6V4ncFbZm9es8FtphzwrGoYqi6zK3oYNGMzDXgAHnQeamE5BMAxmr7BUNvs",
    "4axni9n9v9rYkCZJy97gEe3dCFsLP83naS1QdxXBo4nqiPaRQH5WXNCCuKPwvAevbMYcgeNhbCt8Yve3nfNapwNd",
    "4axni9n9v9rYkCZJy97gEe3dCFsLP83naS1QdxXBo4nqiPaRQH5WXNCCuKPwvAevbMYcgeNhbCt8Yve3nfNapwNd",
    "5yqUprHne9PvmpKwW4e2htxCtkeVKnx1kH6qVf6utUdUKMgjASeJiRLrWsqRUMSCN8KZXdVQP1rFwaJ4YnanwSJG",
    "5yqUprHne9PvmpKwW4e2htxCtkeVKnx1kH6qVf6utUdUKMgjASeJiRLrWsqRUMSCN8KZXdVQP1rFwaJ4YnanwSJG",
    "4Czj5kdRibYRKpi9tNa6znd1r99XWGERXkY565kfh2BTdQk46AAHTed3kHvunmmQ3DrkU2ZxmorkQ1jfAq7WQ3EX",
    "4Czj5kdRibYRKpi9tNa6znd1r99XWGERXkY565kfh2BTdQk46AAHTed3kHvunmmQ3DrkU2ZxmorkQ1jfAq7WQ3EX",
    "nJt4nTxWAKqHPW2xf1yhYMtDjh8Xn5HfVZmRHSRsk6KKRkHc8mouJZBzA5FGB4755JC58SeM4kgiitXGBrmw6su",
    "nJt4nTxWAKqHPW2xf1yhYMtDjh8Xn5HfVZmRHSRsk6KKRkHc8mouJZBzA5FGB4755JC58SeM4kgiitXGBrmw6su",
    "5fSspCopTg57GBwkkNVyADPTUDw5nhXwy1CGfuBUxq6nppaRxs6kFTTZVBsYKk5QeSKBgJE7ABgREmkdQXhdajfP",
    "5fSspCopTg57GBwkkNVyADPTUDw5nhXwy1CGfuBUxq6nppaRxs6kFTTZVBsYKk5QeSKBgJE7ABgREmkdQXhdajfP",
    "57D7JFXuKAnUSL4vhAH9KECj2gzrVYCBPMwkixHPLFjMiAfUpyaGRZqcSNg8pQ96M2oUyhgog8iKKQuQmZfkwkAD",
    "57D7JFXuKAnUSL4vhAH9KECj2gzrVYCBPMwkixHPLFjMiAfUpyaGRZqcSNg8pQ96M2oUyhgog8iKKQuQmZfkwkAD",
    "2sQoNRWXYN2u4FMfhj9nevKjShPvS51HMPxCABMRCDTxyT8YnbdvcTaBPhtLcQcK9i9fnugFTJU7ML14rMcqecNz",
    "2sQoNRWXYN2u4FMfhj9nevKjShPvS51HMPxCABMRCDTxyT8YnbdvcTaBPhtLcQcK9i9fnugFTJU7ML14rMcqecNz",
    "64vqyvFQeSdew9zGyVpr5mKYhvk3y2XhqrdqAArLW56CgzuChHMysASrKkXW6sc74VxA2pCK8quecPeiRMARwCc5",
    "64vqyvFQeSdew9zGyVpr5mKYhvk3y2XhqrdqAArLW56CgzuChHMysASrKkXW6sc74VxA2pCK8quecPeiRMARwCc5",
    "4cGSQz5Kt78dCjMcc95Hh6EEQLHHR2GwTTB2jWT2WpCRtHCrGMMgqzLo4FNgq4nwcrewdrL76QJybsS93QaWjJ4x",
    "4cGSQz5Kt78dCjMcc95Hh6EEQLHHR2GwTTB2jWT2WpCRtHCrGMMgqzLo4FNgq4nwcrewdrL76QJybsS93QaWjJ4x",
    "PmQQ5S1aY7QJ7wJeFgD28AQqVfV1qY3UvSQxBrHsM7sm92hs4xYGkqeYNDu2AcCZe3S73JAGkumvuWeyCwuXudZ",
    "PmQQ5S1aY7QJ7wJeFgD28AQqVfV1qY3UvSQxBrHsM7sm92hs4xYGkqeYNDu2AcCZe3S73JAGkumvuWeyCwuXudZ",
    "4gqRLYCVjmBJf1hXQwf4ArPQTnnzd7U6TnsqwuVuo8K7kQVmpaq79st3J6zAboDu4sjYeSse9EeDW6pScTL1pT1b",
    "4gqRLYCVjmBJf1hXQwf4ArPQTnnzd7U6TnsqwuVuo8K7kQVmpaq79st3J6zAboDu4sjYeSse9EeDW6pScTL1pT1b",
    "4HkP1fuz4yhRmhtGdPYQuobVUmwLP7uMx1Luj4YMxRK2yyAWYicLKCF9iBQdMnjFp5FEAYvCHKwzZ3jhRA7ZKnys",
    "4HkP1fuz4yhRmhtGdPYQuobVUmwLP7uMx1Luj4YMxRK2yyAWYicLKCF9iBQdMnjFp5FEAYvCHKwzZ3jhRA7ZKnys",
    "4Qkm1GTx8ax4oqTfaTYzcpmHEreNuAeEeztFAZmRuQff867abCDvhGTTn9gvHALpZRBmCXA8cVwU5gjB1Mj7hG9w",
    "4Qkm1GTx8ax4oqTfaTYzcpmHEreNuAeEeztFAZmRuQff867abCDvhGTTn9gvHALpZRBmCXA8cVwU5gjB1Mj7hG9w",
    "2eE6xHGjEXKLQFTwhDDnPNb3yxCJYjjYGiwF4sYEgNGJGJWhJX8Mg4JZFMdwHZDMxxn3ons9bAdUYQ343ciJE1oc",
    "2eE6xHGjEXKLQFTwhDDnPNb3yxCJYjjYGiwF4sYEgNGJGJWhJX8Mg4JZFMdwHZDMxxn3ons9bAdUYQ343ciJE1oc",
    "57c1Fm865J7tYaTKcKszoHu2ymgBpNuAv4TcY7gVQfWBAdCtrD1rXNhaSs9uZ9Vmj9djFyBisuBN92Rju1FxQCCs",
    "57c1Fm865J7tYaTKcKszoHu2ymgBpNuAv4TcY7gVQfWBAdCtrD1rXNhaSs9uZ9Vmj9djFyBisuBN92Rju1FxQCCs",
    "64SDZKbNdxW8YK1vqiCWEY7Zg7uiBT3rqGZgytRTSmAXZry2SKxZA9wfLaoL363dizNZqWUGV5EtF5YYqPEdwEkn",
    "64SDZKbNdxW8YK1vqiCWEY7Zg7uiBT3rqGZgytRTSmAXZry2SKxZA9wfLaoL363dizNZqWUGV5EtF5YYqPEdwEkn",
    "5YthJ4h6GgZ7na1gQinFLpNrdmjNGYdp3kLJ2NuYqLfDyRC6RUJRLzsaTZ5DPxBzmYxZ1d816oL3MJJfp1yKRHG7",
    "5YthJ4h6GgZ7na1gQinFLpNrdmjNGYdp3kLJ2NuYqLfDyRC6RUJRLzsaTZ5DPxBzmYxZ1d816oL3MJJfp1yKRHG7",
    "2mkfEs7YW9ihS64tzEkhiWTjrnnMunwamTY8r2brQiNsvZG9Y5LCkDJQpCkBKP5m7dUNVq5xAfrfKvgNb6iyyKBM",
    "2mkfEs7YW9ihS64tzEkhiWTjrnnMunwamTY8r2brQiNsvZG9Y5LCkDJQpCkBKP5m7dUNVq5xAfrfKvgNb6iyyKBM",
    "3zNLFwCmhjWAEjCSAwu3ckhU7uCBAwKbdjtnqMc76XRtGx2dSMRPiWbmN5KR7reKgm1Ljy3fUpP91RiKCuQNZxU6",
    "3zNLFwCmhjWAEjCSAwu3ckhU7uCBAwKbdjtnqMc76XRtGx2dSMRPiWbmN5KR7reKgm1Ljy3fUpP91RiKCuQNZxU6",
    "5nNHwEse8UYPRqGD3jNU3thdbv4Af8p5aLrWUyLQW7PLcLVLpJSgFZvQzdAtGRSDsAX5QST6n8vFQAyh5TVmKgc8",
    "5nNHwEse8UYPRqGD3jNU3thdbv4Af8p5aLrWUyLQW7PLcLVLpJSgFZvQzdAtGRSDsAX5QST6n8vFQAyh5TVmKgc8",
    "2HuMgxbpKzC5w7zbLhSPK4JQ2BQVnpHcexhyJ2ZWCsLszRQiy934MVcz15vQuwzMKjQUFY9Vzqg8GuFw7aUWkB9h",
    "2HuMgxbpKzC5w7zbLhSPK4JQ2BQVnpHcexhyJ2ZWCsLszRQiy934MVcz15vQuwzMKjQUFY9Vzqg8GuFw7aUWkB9h",
    "5XFdKKBjaTDKxvFB8ThRpq2thMKTat7RXNNoFaLVbEotZ8P7LqxdjbA9QR25EH8s4JUwaBfEhmn4DCTiNsSL1pWH",
    "5XFdKKBjaTDKxvFB8ThRpq2thMKTat7RXNNoFaLVbEotZ8P7LqxdjbA9QR25EH8s4JUwaBfEhmn4DCTiNsSL1pWH",
    "2oRqo7w8USa1Keg3EEpBV8Tb9HQTtNRd3yWEqihk2pFwWQSxXMaVJgHmSn9QciJppHUXaTRKatsU18JyNhGACNDy",
    "2oRqo7w8USa1Keg3EEpBV8Tb9HQTtNRd3yWEqihk2pFwWQSxXMaVJgHmSn9QciJppHUXaTRKatsU18JyNhGACNDy",
    "4Cc7nGr6YEiREb9Zj81qHYo1rtXobrEo9dUj7uMjuHshLFRDWr9buXWW5gyA5ffAoTD97HHZCi6Fb6pGRtEeqtrR",
    "4Cc7nGr6YEiREb9Zj81qHYo1rtXobrEo9dUj7uMjuHshLFRDWr9buXWW5gyA5ffAoTD97HHZCi6Fb6pGRtEeqtrR",
    "58sN8eG3EtdASmm6Xzqo4XDSMY1pNDDRvUmj74NV8q87hNRPR2HC3RJHiQmj3X432n9Ssa9FEGuJsPpxQnZcGiJd",
    "58sN8eG3EtdASmm6Xzqo4XDSMY1pNDDRvUmj74NV8q87hNRPR2HC3RJHiQmj3X432n9Ssa9FEGuJsPpxQnZcGiJd",
    "5vHsMD5LTiqH6mxfp5KuRbHzdhsH2bi4Uzmr9HJ2n5NutU6wc8LEp1F5N2pNATeEGxRwFHYPNjLRmV67hz3dG7Ej",
    "5vHsMD5LTiqH6mxfp5KuRbHzdhsH2bi4Uzmr9HJ2n5NutU6wc8LEp1F5N2pNATeEGxRwFHYPNjLRmV67hz3dG7Ej",
    "sz5jnhhXuUfUSA8VRzbMphPFuzkt7wqX6a53hSZkveEZxwPeyo99cbr4ZUZMmTyZWi93KoD4TTTwdE5Nv1oEgNi",
    "sz5jnhhXuUfUSA8VRzbMphPFuzkt7wqX6a53hSZkveEZxwPeyo99cbr4ZUZMmTyZWi93KoD4TTTwdE5Nv1oEgNi",
    "5QDKEpe6UgtsUDo5W3HqQhN1uwrY2u3vVnnXk3yckkHTaqN4zwSdzzkVs8JVGafGrZaQEWQucjnJKcgXPzPf1Vtc",
    "5QDKEpe6UgtsUDo5W3HqQhN1uwrY2u3vVnnXk3yckkHTaqN4zwSdzzkVs8JVGafGrZaQEWQucjnJKcgXPzPf1Vtc",
    "2P6JxN5h8F7SnwG5zkR2yFHqb8E9RL8crhuwknLkcNDjKxqgw7DtiiPgvKa6ammBLdEAVjxenvpnEmX8wPaUpHRo",
    "2P6JxN5h8F7SnwG5zkR2yFHqb8E9RL8crhuwknLkcNDjKxqgw7DtiiPgvKa6ammBLdEAVjxenvpnEmX8wPaUpHRo",
    "5werh5qYPZzoMG7vYugZ1BEEXDwPZiyvUw8qpY7144em682F6wEUyq7baRzCD7h2oqdGc2gTRPaw7sPjCsGzLv3s",
    "5werh5qYPZzoMG7vYugZ1BEEXDwPZiyvUw8qpY7144em682F6wEUyq7baRzCD7h2oqdGc2gTRPaw7sPjCsGzLv3s",
    "32d5kFhWRwqqonsazoHbfdraG9XRuzR317dC6HGK9ARTYeQrZVGkRrKibfZJQby59UDCaUzzzBFJ6iSd1e5fPdVM",
    "32d5kFhWRwqqonsazoHbfdraG9XRuzR317dC6HGK9ARTYeQrZVGkRrKibfZJQby59UDCaUzzzBFJ6iSd1e5fPdVM",
    "3jgXxiSrsqSsT5oQDeyrVbND7YQsXMpmbMx1nGpiXQw8u2hqLjWcVoQx8Nq86699qAYhCEcsYHQYZnk3XFUNvKRW",
    "3jgXxiSrsqSsT5oQDeyrVbND7YQsXMpmbMx1nGpiXQw8u2hqLjWcVoQx8Nq86699qAYhCEcsYHQYZnk3XFUNvKRW",
    "4vM7Bv4W69sMATJYhGreyDGeAizC4Y6qG27v378KQaahYZbZ2D9fPtsr92o2VqaETwZyv4rRYSRsST4mdPWFyNyd",
    "4vM7Bv4W69sMATJYhGreyDGeAizC4Y6qG27v378KQaahYZbZ2D9fPtsr92o2VqaETwZyv4rRYSRsST4mdPWFyNyd",
    "KDQbf8L4J9hJMai3bzsDvRoLwqGHwpvhMyatLRFat6wrwz1u3wmDXYgGR7fiCTieFvgWZoA3JjbWtPAq2qhKpNW",
    "KDQbf8L4J9hJMai3bzsDvRoLwqGHwpvhMyatLRFat6wrwz1u3wmDXYgGR7fiCTieFvgWZoA3JjbWtPAq2qhKpNW",
    "FiHgjKe7QaKSr9vNBK1AFnrJMr7WaDJXru7GfKuBd9YaGqMM7w5Z4cf9meKCJrXevU4SRQzsFgnft3xD5En1ffY",
    "FiHgjKe7QaKSr9vNBK1AFnrJMr7WaDJXru7GfKuBd9YaGqMM7w5Z4cf9meKCJrXevU4SRQzsFgnft3xD5En1ffY",
    "4N3minCxxPhKNnva7MHNm3B7pTX8gqNef1kGmwwsrW9vX5TvSJ7xVDo12UDXrcV6SYeaqGnvfWFpYvCfiqhmc7GE",
    "4N3minCxxPhKNnva7MHNm3B7pTX8gqNef1kGmwwsrW9vX5TvSJ7xVDo12UDXrcV6SYeaqGnvfWFpYvCfiqhmc7GE",
    "2MsVy8drEEgPgg91Pi2tgGanwqKUipjQutM7zkSmuF3djQve7AYYcbvW13f9RMLQxm78fTwnQmbRpFaaC8m3yUMG",
    "2MsVy8drEEgPgg91Pi2tgGanwqKUipjQutM7zkSmuF3djQve7AYYcbvW13f9RMLQxm78fTwnQmbRpFaaC8m3yUMG",
    "3A7H81nYtaPZrYV4cdJn69pRCLsi4HofybzB9y4Y2NqLXT71VMyNjuJF3CXmjv56xfkt37R1eMW1ecNzZcrR469k",
    "3A7H81nYtaPZrYV4cdJn69pRCLsi4HofybzB9y4Y2NqLXT71VMyNjuJF3CXmjv56xfkt37R1eMW1ecNzZcrR469k",
    "uC7AxMDha6UQTWyJNMB3W3oWSUAhxdybeb8HDaPFn932a6ZorF5FjwqzA9M7nYJz8Pwek8fZ4Lzra679yGkyXXu",
    "uC7AxMDha6UQTWyJNMB3W3oWSUAhxdybeb8HDaPFn932a6ZorF5FjwqzA9M7nYJz8Pwek8fZ4Lzra679yGkyXXu",
    "5zy56Hs6GWdpb8A685Zr4w6zDoQbg2sPm5WX7k6eH6FtmA3e5kAgkmExS3imzdLwpq2ahu2L7chp41YVMMQzFMvQ",
    "5zy56Hs6GWdpb8A685Zr4w6zDoQbg2sPm5WX7k6eH6FtmA3e5kAgkmExS3imzdLwpq2ahu2L7chp41YVMMQzFMvQ",
    "3qrWCn4gXzhktHBXRV5e8oDtDH76Ph1tUbkcxexqnzzaESfwVhooC7vCki2yJFM34vrNj3iZ4M6cghYwtNdTdBAG",
    "3qrWCn4gXzhktHBXRV5e8oDtDH76Ph1tUbkcxexqnzzaESfwVhooC7vCki2yJFM34vrNj3iZ4M6cghYwtNdTdBAG",
    "341YfGGDjCBQW2NZbXCh7LPtdhZ2wnRLacRH83LgHK1U3uLLrJBLkXJs79DgRn2fjqXLa4FbxTZfc2mBpGTZc9qD",
    "341YfGGDjCBQW2NZbXCh7LPtdhZ2wnRLacRH83LgHK1U3uLLrJBLkXJs79DgRn2fjqXLa4FbxTZfc2mBpGTZc9qD",
    "5zwXi1tW6wtuiy4fa3RZHm3jQ7g7vP9y7yPn1Dhi1Ta48UNfa8gL7tnvePzn3H2ugGNoKDMAJU2zz5tNXPkJNFwW",
    "5zwXi1tW6wtuiy4fa3RZHm3jQ7g7vP9y7yPn1Dhi1Ta48UNfa8gL7tnvePzn3H2ugGNoKDMAJU2zz5tNXPkJNFwW",
    "3ifv1jKsebvEfEAwZrWjQBrwQ5YLc3yjPuysCYKcdXu3LakDsoPTDHfAYZtfpCXY7WKt3gDxL7EvGVJtCo92Quch",
    "3ifv1jKsebvEfEAwZrWjQBrwQ5YLc3yjPuysCYKcdXu3LakDsoPTDHfAYZtfpCXY7WKt3gDxL7EvGVJtCo92Quch",
    "27i7nJ6wAgYMGB8ibUTc5W1Mr7dZNkhiA6pozqpvvxhKJ8TLXegE5e6BdHpsAmW1hkM3QNtPmzow1otMR82H23vw",
    "27i7nJ6wAgYMGB8ibUTc5W1Mr7dZNkhiA6pozqpvvxhKJ8TLXegE5e6BdHpsAmW1hkM3QNtPmzow1otMR82H23vw",
    "4qX5ebshHLmKCK7jfKCS6Anmv3baisdc6Ts6j82taxG4ZTztX1x82CeYCL94pSA8V5wsBKprt7DzKSbx4at2Mpag",
    "4qX5ebshHLmKCK7jfKCS6Anmv3baisdc6Ts6j82taxG4ZTztX1x82CeYCL94pSA8V5wsBKprt7DzKSbx4at2Mpag",
    "2DL25i7PtwjeV1Jf4n8GkjaUvhrzY3qczZSyenXdq8cwHZixJdAiZtANadVo4ZxT5LyRih9zx7xeAH1BAy7vRs8T",
    "2DL25i7PtwjeV1Jf4n8GkjaUvhrzY3qczZSyenXdq8cwHZixJdAiZtANadVo4ZxT5LyRih9zx7xeAH1BAy7vRs8T",
    "2mmNbgq2w2nC4m3yvnLXv8nyFz6dtKjefPxL9tMajXRg2gPJc9dvjmQH8L1N7BeeUUBk72qjCV27ts9TvvkV4sKL"
        ] # remove
      identifiers = ea.movements
        .uncategorized_or_with_kind
        .where(time: Apesu.first.time_a..Apesu.first.time_b)
        .where(remote_identifier: ides) # remove
        .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

      fail_the_threshold = []
      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 ides.include?(remote_identifier) # remove
              is_good = true
              next
            end

            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_a = Apesu.first.time_a
      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