RSpec.describe "snowflake db" do
  describe "currency_exchange_rates table" do
    it "has data for the new period" do
      result = snowflake.fetch(
        "SELECT max(period_id) AS latest_period
         FROM currency_exchange_rates").first
      puts result
      expect(result[:latest_period].to_s).to eq ENV["PERIOD"]
    end

    it "has non-zero exchange rate for USD to GBP" do
      usd_to_gbp = <<-SQL
        SELECT exchange_rate
        FROM currency_exchange_rates
        WHERE currency_from_id = (SELECT currencyid
                                  FROM dim_currency
                                  WHERE currency_code = 'USD')
        AND currency_to_id = (SELECT currencyid
                              FROM dim_currency
                              WHERE currency_code = 'GBP')
        AND period_id = #{ENV['PERIOD']}
      SQL

      result = snowflake.fetch(usd_to_gbp).first
      expect(result[:exchange_rate].to_d).to be > 0
    end
  end

  describe "fact_sales table" do
    it "has data for the new period" do
      result = snowflake.fetch("SELECT max(accountingperiodid) AS latest_period FROM fact_sales").first
      expect(result[:latest_period].to_s).to eq ENV["PERIOD"]
    end

    it "matches value from art_relations for label 17901 net receipt" do
      my_result = mysql.query "SELECT amount FROM art_relations.vendor_accounting WHERE vendor_id = 17901 AND entry_type = 'dig_net_receipt' AND period_id = #{ENV['PERIOD']}"
      result = snowflake.fetch("SELECT sum(net_receipt) AS label_net_receipt FROM fact_sales WHERE labelid = 17901 AND accountingperiodid = #{ENV['PERIOD']}")
      expect(result.first[:label_net_receipt].to_d).to be_within(0.001).of(my_result.first["amount"].to_d)
    end

    # Sanity checking the ETL, https://github.com/theorchard/php-etl/blob/64bb353b52b88c7415a37340c7a716ee1bf911fa/src/Orchard/FactSales/FactSales.php#L78
    describe "net receipt in foreign currency for label 19405" do
      it "should be net receipt in USD multiplied by fx_adjusted_exchange_rate" do
        # Payout currency, at the time of writing this, is GBP... test assumes payout currency is *not* USD
        expected = snowflake.fetch("SELECT sum(net_receipt*fx_adjusted_exchange_rate) AS calculated FROM fact_sales WHERE labelid = 19405 AND accountingperiodid = #{ENV['PERIOD']}").first[:calculated]
        actual = snowflake.fetch("SELECT sum(fx_net_receipt) AS fx_net_receipt FROM fact_sales WHERE labelid = 19405 AND accountingperiodid = #{ENV['PERIOD']}").first[:fx_net_receipt]

        # Off by several pence because of rounding error, presumably
        expect(actual.to_d).to be_within(1.00).of(expected.to_d)
      end
    end

    # Row count check. If for example data was loaded twice (incorrectly), then row count in production will be 2X that of staging and this expectation will fail
    it "matches the row count from staging_fact_sales" do
      prod_row_count = snowflake.fetch("SELECT COUNT(*) FROM facts.prod.fact_sales WHERE accountingperiodid = #{ENV['PERIOD']};").first[:count]
      staging_row_count = snowflake.fetch("SELECT COUNT(*) FROM staging_fact_sales;").first[:count]
      expect(prod_row_count).to eq(staging_row_count)
    end

    # Row count check between Art Relations and Redshift Fact Sales
    it "matches the row count from accountingflat.processed_dig_sales" do
      prod_row_count = snowflake.fetch("SELECT COUNT(*) as count FROM fact_sales WHERE accountingperiodid = #{ENV['PERIOD']};").first[:count].to_i
      ar_row_count = mysql.query("SELECT COUNT(*) FROM accountingflat.processed_dig_sales WHERE period_id = #{ENV['PERIOD']};").first["COUNT(*)"]
      expect(prod_row_count).to eq(ar_row_count)
    end

    # data check between Art Relations and snowflake Fact Sales
    # excluding this from main suite since this scenario has it's own task and own jenkins job
    it "matches data from accountingflat.processed_dig_sales", :processed_dig_sales, :exclude_from_main_suite do
      snwflk = snowflake.fetch("select accountingperiodid as period_id,
                                        count(*) as count,
                                        sum(actual_net) as actual_net,
                                        sum(adjusted_gross) as adjusted_gross,
                                        sum(distribution_fees) as distribution_fees,
                                        sum(dpd_publishing) as dpd_publishing,
                                        sum(cloud_publishing) as cloud_publishing,
                                        sum(gross) as gross,
                                        sum(net_receipt) as net_receipt,
                                        sum(oms_fees) as oms_fees,
                                        sum(partner_share) as partner_share,
                                        sum(ringtone_publishing) as ringtone_publishing,
                                        sum(fx_spread_fee) as fx_spread_fee
                                        from fact_sales
                                        where accountingperiodid = #{ENV['PERIOD']}
                                        group by 1
                                        order by 1 desc;").first

      expect(snwflk).to_not be_nil, "fact_sales had no data for the PERIOD: #{ENV['PERIOD']}"

      # converting hash keys from sym to string for easier comparison
      snowflake_results = snwflk.map{ |key, v| [key.to_s, v] }.to_h

      art_rel = mysql.query("select period_id,
                                      count(*) as count,
                                      sum(actual_net) as actual_net,
                                      sum(adjusted_gross) as adjusted_gross,
                                      sum(distribution_fees) as distribution_fees,
                                      sum(dpd_publishing) as dpd_publishing,
                                      sum(cloud_publishing) as cloud_publishing,
                                      sum(gross) as gross,
                                      sum(net_receipt) as net_receipt,
                                      sum(oms_fees) as oms_fees,
                                      sum(partner_share) as partner_share,
                                      sum(ringtone_publishing) as ringtone_publishing,
                                      sum(fx_spread_fee) as fx_spread_fee
                                      from accountingflat.processed_dig_sales
                                      where period_id = #{ENV['PERIOD']}
                                      group by 1;").first

      expect(art_rel).to_not be_nil, "accountingflat.processed_dig_sales had no data for the PERIOD: #{ENV['PERIOD']}"

      snowflake_results.keys.each do |key|
        case key
        when "count", "period_id"
          expect(art_rel[key].to_s).to eq(snowflake_results[key])
        else
          expect(art_rel[key].to_s("F").to_f).to eq(snowflake_results[key].to_f), "#{key} field didn't match"
        end
      end
    end
  end
end