RSpec.describe "mysql art_relations db" do
  describe "vendor_accounting table" do
    it "has data for the new period" do
      result = mysql.query "SELECT max(period_id) AS latest_period FROM art_relations.vendor_accounting"
      expect(result.first["latest_period"].to_s).to eq ENV["PERIOD"]
    end

    it "has the amount payable to 8869" do
      result = mysql.query "SELECT amount FROM art_relations.vendor_accounting WHERE vendor_id = 8869 AND entry_type = 'amount_payable' AND period_id = #{ENV['PERIOD']}"
      expect(result.first["amount"]).to be_a BigDecimal
    end

    it "has currency exchange rates for the new period" do
      result = mysql.query "SELECT exchange_rate FROM art_relations.currency_exchange_rates WHERE period_id = #{ENV['PERIOD']} LIMIT 1"
      expect(result.first["exchange_rate"]).to be_a BigDecimal
    end
  end

  # excluding this from main suite since this scenario has it's own task and own jenkins job
  describe "accountingflat.TEMP_dig_sales_statements", :isrc_check, :exclude_from_main_suite do
    it "should have all tracks ISRCs assigned" do
      query = "select DISTINCT t.id
               from accountingflat.TEMP_dig_sales_statements d
               inner join art_relations.track t ON  d.upc = t.upc AND d.cd = t.cd and d.track_id = t.track_id
               where 1
               and (d.cd <> 0 and d.track_id <> 0)
               AND t.isrc IS NULL"

      results = mysql.query query

      if results.count > 0
        results.each do |result|
          puts "ISRC was not found for art_relations.track.id: #{result['id']}"
        end
        fail
      end
    end
  end

  describe "Release Manual Adjustment" do
    # excluding this from main suite since this scenario has it's own task and own jenkins job
    it "should be equal to Manual Adjustment", :rma_comparison, :exclude_from_main_suite do
      query = "SELECT *, abs(xx.xr_rate-xx.amount) delta FROM (
             SELECT
               ma.id,
               xr.period_id,
               round(sum(rma.amount),4) original_currency,
               round(ma.amount_in_original_currency,4) ma_orig_currency,
               round(sum(rma.amount)*xr.exchange_rate,4) xr_rate,
               round(ma.amount,4) amount,
               count(rma.id) rma_trans,
               xr.currency_to_id,
               xr.currency_from_id
             FROM
               art_relations.release_manual_adjustment rma
             JOIN
               art_relations.currency_exchange_rates xr ON xr.currency_from_id=rma.currencies_id
               AND xr.currency_to_id = 1
               AND xr.currency_from_id != 1
             JOIN
               art_relations.manual_adjustment ma ON ma.id=rma.vendor_manual_adjustment_id
               AND ma.apply_to_period_id=xr.period_id
               AND ma.apply_to_period_id=#{ENV['PERIOD']}
             GROUP BY
               ma.id
             ) xx
             WHERE 1
               AND abs(xx.xr_rate-xx.amount) != 0
               ORDER BY xx.period_id, delta DESC"
      results = mysql.query query

      results.each do |result|
        expect(result['xr_rate'].to_s("F").to_f).to be_within(1).of(result['amount'].to_s("F").to_f)
      end
    end
  end
end