# credo:disable-for-this-file Credo.Check.Readability.LargeNumbers
defmodule CompatibilityTest do
  use ExUnit.Case

  describe "elixlsx" do
    test "file generated by elixlsx" do
      test_row = [
        "string1",
        "",
        nil,
        :empty,
        "string1",
        "string2",
        123,
        true,
        false
      ]

      workbook = %Elixlsx.Workbook{
        sheets: [
          %Elixlsx.Sheet{
            name: "sheet1",
            rows: [
              test_row
            ]
          },
          %Elixlsx.Sheet{name: "sheet2", rows: []}
        ]
      }

      assert {:ok, {_filename, zip_binary}} = Elixlsx.write_to_memory(workbook, "test.xlsx")

      assert {:ok, package} = XlsxReader.open(zip_binary, source: :binary)

      assert ["sheet1", "sheet2"] = XlsxReader.sheet_names(package)

      assert {:ok,
              [
                [
                  "string1",
                  "",
                  nil,
                  nil,
                  "string1",
                  "string2",
                  123.0,
                  true,
                  false
                ]
              ]} = XlsxReader.sheet(package, "sheet1", blank_value: nil)

      assert {:ok, []} = XlsxReader.sheet(package, "sheet2")
    end

    test "sheets with dates and datetimes" do
      s1 =
        Elixlsx.Sheet.with_name("1")
        |> Elixlsx.Sheet.set_cell("A1", {{2015, 11, 30}, {21, 20, 38}}, datetime: true)

      s2 =
        Elixlsx.Sheet.with_name("2")
        |> Elixlsx.Sheet.set_cell("A1", {{2015, 11, 30}, {21, 20, 38}}, yyyymmdd: true)

      wk = %Elixlsx.Workbook{sheets: [s1, s2]}

      {:ok, {_filename, bin}} = Elixlsx.write_to_memory(wk, "test.xlsx")
      {:ok, package} = XlsxReader.open(bin, source: :binary)
      {:ok, sheets} = XlsxReader.sheets(package)

      assert [{"1", [[~N[2015-11-30 21:20:38]]]}, {"2", [[~D[2015-11-30]]]}] = sheets
    end
  end

  test "google_spreadsheet.xlsx" do
    assert {:ok, package} =
             XlsxReader.open(
               TestFixtures.path("google_spreadsheet.xlsx"),
               supported_custom_formats: [{"mmmm d", :date}]
             )

    assert ["Sheet1"] = XlsxReader.sheet_names(package)

    assert {:ok,
            [
              ["integer", 123.0],
              ["float", 123.456],
              ["percentage", 12.5],
              ["date", ~D[2019-11-15]],
              ["time", ~N[1899-12-30 11:45:00]],
              ["ticked\n", true],
              ["unticked", false],
              ["image", ""]
            ]} = XlsxReader.sheet(package, "Sheet1")
  end

  test "merged.xlsx" do
    assert {:ok, package} = XlsxReader.open(TestFixtures.path("merged.xlsx"))

    assert ["merged"] = XlsxReader.sheet_names(package)

    assert {:ok,
            [
              ["horizontal", "", "vertical"],
              ["horizontal + vertical", "", ""],
              ["", "", "none"]
            ]} = XlsxReader.sheet(package, "merged")
  end

  test "file with omitted row elements" do
    assert {:ok, package} = XlsxReader.open(TestFixtures.path("omitted_row.xlsx"))

    assert {:ok, [["", ""], ["", "b2"]]} = XlsxReader.sheet(package, "Sheet1", empty_rows: true)
    assert {:ok, [["", "b2"]]} = XlsxReader.sheet(package, "Sheet1", empty_rows: false)
  end

  test "cells with missing attributes" do
    # The worksheet has a mix of "s", "t" and no attributes
    assert {:ok, package} = XlsxReader.open(TestFixtures.path("cells_missing_attributes.xlsx"))

    assert {
             :ok,
             [
               [
                 "Number",
                 "Name",
                 "Effective Date",
                 "ID Number",
                 "Operator",
                 "County",
                 "State",
                 "Product",
                 "Description",
                 "Acres"
               ],
               [
                 "Number",
                 "Name",
                 # Date with s="5" is converted
                 ~D[2024-07-01],
                 # This cell has no attributes at all, it's treated as a string
                 # and not converted to a date because all cell attributes are
                 # properly reset
                 "5575789630",
                 "Operator",
                 "County",
                 "State",
                 "Product",
                 "Description",
                 "Acres"
               ]
             ]
           } = XlsxReader.sheet(package, "Sheet 1")
  end

  test "cells with empty <v/> element" do
    # The worksheet has some formula cells with empty <v/> elements
    #  Example:
    # <c r="O5259" s="7" t="str">
    #   <f>IF(ISNUMBER(N5259),_xll.BDP($C5259, "OPT_UNDL_TICKER"),"")</f>
    #   <v/>
    # </c>
    {:ok, package} = XlsxReader.open(TestFixtures.path("empty_v_elements.xlsx"))
    {:ok, rows} = XlsxReader.sheet(package, "Simplify Portfolio Tracker")

    assert [_, _, sample_row | _] = rows

    assert [
             "AGGH",
             "ISHARES CORE US AGGREGATE BOND ETF",
             "AGG",
             "2897404",
             "US4642872265",
             "464287226",
             3_005_147.0,
             99.4,
             298_711_611.8,
             92.83633300000001,
             321_761_537.77,
             15_525_001.0,
             "20.725379520000001",
             "#ERROR",
             "",
             " ",
             "#ERROR",
             " ",
             "#ERROR",
             "464287226",
             "Fund",
             "",
             "",
             "",
             "",
             "",
             "",
             "",
             "",
             "",
             "",
             "",
             "2.5300000000000001E-3"
           ] = sample_row
  end
end
