require 'suite_helper'
require 'shared_examples_for_flows'

# all specs making use of 'shared_examples_for_flows' need to define the below cleanup method...
def truncate_rows
  # clean up any result rows we created
  puts '*** Truncating result YouTube VOD.... ***'
  pg.exec("DELETE FROM staging_raw_youtubevod WHERE download_date>='20160314' AND download_date<='20160320';")
  pg.exec("DELETE FROM fact_analytics WHERE download_activity_date>='2016-03-14' AND download_activity_date<='2016-03-20' AND storeid=463;")
  pg.exec("DELETE FROM fact_analytics_error WHERE reportdate='20160320' AND storeid=463;")
end

RSpec.describe 'youtube_vod etl' do
  it_behaves_like 'a swf-feed-ingestion flow', 'youtube_vod', '2016-03-20', 600 do
    describe 'postgres users table' do
      describe 'srdv' do
        it 'has the right transaction date' do
          result = pg.exec("SELECT COUNT(*) FROM staging_raw_youtubevod WHERE download_date>='20160314' AND download_date<='20160320';").first
          puts result
          expect(result['count']).to eq('40')
        end

        it 'has the correct count of total_views' do
          result = pg.exec("SELECT SUM(total_views) FROM staging_raw_youtubevod WHERE download_date>='20160314' AND download_date<='20160320' AND filename='YouTube_dmgi_W_20160314_20160320_rawdata_v1-0.csv.zip';").first
          puts result
          expect(result['sum']).to eq('178')
        end

        it 'has the correct count of watch_views' do
          result = pg.exec("SELECT SUM(watch_views) FROM staging_raw_youtubevod WHERE download_date>='20160314' AND download_date<='20160320' AND filename='YouTube_dmgi_W_20160314_20160320_rawdata_v1-0.csv.zip';").first
          puts result
          expect(result['sum']).to eq('178')
        end

        it 'has the correct count of recorded_views' do
          result = pg.exec("SELECT SUM(recordedviews) FROM staging_raw_youtubevod WHERE download_date>='20160314' AND download_date<='20160320' AND filename='YouTube_dmgi_W_20160314_20160320_rawdata_v1-0.csv.zip';").first
          puts result
          expect(result['sum']).to eq('178')
        end

        it 'has the correct count of ad_enabled_views' do
          result = pg.exec("SELECT SUM(adenabledview) FROM staging_raw_youtubevod WHERE download_date>='20160314' AND download_date<='20160320' AND filename='YouTube_dmgi_W_20160314_20160320_rawdata_v1-0.csv.zip';").first
          puts result
          expect(result['sum']).to eq('160')
        end

        describe 'fact analytics error' do
          it 'has the correct record counts for the date' do
            result = pg.exec("SELECT COUNT(*) FROM fact_analytics_error WHERE reportdate='20160320' AND storeid=463;").first
            puts result
            expect(result['count']).to eq('1')
          end

          it 'has the correct unit counts for the date' do
            result = pg.exec("SELECT SUM(units) FROM fact_analytics_error WHERE reportdate='20160320' AND storeid=463;").first
            puts result
            expect(result['sum']).to eq('1')
          end
        end

        describe 'fact analytics' do
          it 'has the correct counts of units, paid units and freeunits for label id' do
            result = pg.exec("SELECT labelid, COUNT(*) as count, SUM(units) as units, SUM(paidunits) as paidunits, SUM(freeunits) as freeunits FROM fact_analytics WHERE download_activity_date>='2016-03-14' AND download_activity_date<='2016-03-20' AND storeid=463 GROUP BY labelid ORDER BY labelid ASC;")
            result.each do |row|
              puts row
            end
            expect(result[0]['labelid']).to eq('16615')
            expect(result[0]['count']).to eq('4')
            expect(result[0]['units']).to eq('6')
            expect(result[0]['paidunits']).to eq('6')
            expect(result[0]['freeunits']).to eq('0')
            expect(result[1]['labelid']).to eq('16967')
            expect(result[1]['count']).to eq('1')
            expect(result[1]['units']).to eq('1')
            expect(result[1]['paidunits']).to eq('1')
            expect(result[1]['freeunits']).to eq('0')
            expect(result[2]['labelid']).to eq('23000')
            expect(result[2]['count']).to eq('10')
            expect(result[2]['units']).to eq('30')
            expect(result[2]['paidunits']).to eq('30')
            expect(result[2]['freeunits']).to eq('0')
            expect(result[3]['labelid']).to eq('23553')
            expect(result[3]['count']).to eq('1')
            expect(result[3]['units']).to eq('3')
            expect(result[3]['paidunits']).to eq('3')
            expect(result[3]['freeunits']).to eq('0')
            expect(result[4]['labelid']).to eq('24981')
            expect(result[4]['count']).to eq('23')
            expect(result[4]['units']).to eq('137')
            expect(result[4]['paidunits']).to eq('137')
            expect(result[4]['freeunits']).to eq('0')
          end
        end

        it 'has the correct count for countries' do
          result = pg.exec("SELECT countryid, COUNT(*) FROM fact_analytics WHERE download_activity_date>='2016-03-14' AND download_activity_date<='2016-03-20' AND storeid=463 GROUP BY countryid ORDER BY countryid ASC;")
          result.each do |row|
            puts row
          end
          expect(result[0]['countryid']).to eq('1')
          expect(result[0]['count']).to eq('5')
          expect(result[1]['countryid']).to eq('2')
          expect(result[1]['count']).to eq('2')
          expect(result[2]['countryid']).to eq('4')
          expect(result[2]['count']).to eq('2')
          expect(result[3]['countryid']).to eq('7')
          expect(result[3]['count']).to eq('1')
          expect(result[4]['countryid']).to eq('12')
          expect(result[4]['count']).to eq('1')
          expect(result[5]['countryid']).to eq('13')
          expect(result[5]['count']).to eq('11')
        end

        it 'has the correct counts for transaction types' do
          result = pg.exec("SELECT transactiontypeid, COUNT(*) FROM fact_analytics WHERE download_activity_date>='2016-03-14' AND download_activity_date<='2016-03-20' AND storeid=463 GROUP BY transactiontypeid ORDER BY transactiontypeid ASC;")
          expect(result[0]['transactiontypeid']).to eq('9')
          expect(result[0]['count']).to eq('39')
          result.each do |row|
            puts row
          end
        end

        it 'has the correct ircs' do
          result = pg.exec("SELECT releaseid, COUNT(DISTINCT(isrcid)) FROM fact_analytics WHERE download_activity_date>='2016-03-14' AND download_activity_date<='2016-03-20' AND storeid=463 GROUP BY releaseid ORDER BY releaseid ASC;")
          expect(result[0]['releaseid']).to eq('741459273095')
          expect(result[0]['count']).to eq('1')
          expect(result[1]['releaseid']).to eq('886444787895')
          expect(result[1]['count']).to eq('1')
          expect(result[2]['releaseid']).to eq('886788261464')
          expect(result[2]['count']).to eq('1')
          expect(result[3]['releaseid']).to eq('889845198215')
          expect(result[3]['count']).to eq('1')
          expect(result[4]['releaseid']).to eq('889845338765')
          expect(result[4]['count']).to eq('1')
          expect(result[5]['releaseid']).to eq('889845866152')
          expect(result[5]['count']).to eq('1')
          result.each do |row|
            puts row
          end
        end
      end
    end
  end
end