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 Monthly... ***'
  snowflake_conn.execute("DELETE FROM staging_raw_youtube_monthly_v2 WHERE download_date>='2001-01-01' and download_date<='2001-01-31';")
end

RSpec.describe 'youtube_monthly etl' do
  it_behaves_like 'a swf-feed-ingestion flow', 'youtube_monthly', '2001-01-31', 600 do
    describe 'staging raw youtube monthly' do
        it 'successfully connects to snowflake' do
            result = snowflake_conn.fetch("SELECT COUNT(*) from staging_raw_youtube_monthly_v2 WHERE download_date>='2001-01-01' AND download_date<='2001-01-31';").first
            puts result
            expect(result[:"count(*)"]).to eq('7')
        end
    end
    describe 'record ids' do
        it 'has correct custom id, extracted upc and isrc for all sources' do
            result = snowflake_conn.fetch("SELECT DISTINCT custom_id, extracted_upc, extracted_upc_long, isrc
            FROM staging_raw_youtube_monthly_v2
            WHERE download_date>='2001-01-01' AND download_date<='2001-01-31'
            ORDER BY custom_id, extracted_upc, extracted_upc_long, isrc;").all
            result.each do |row|
              puts row
            end
            expect(result[0][:'custom_id']).to eq('01289824')
            expect(result[0][:'extracted_upc']).to eq('01289824')
            expect(result[0][:'extracted_upc_long']).to eq('1289824')
            expect(result[0][:'isrc']).to eq('USDQ30501407')
            expect(result[1][:'custom_id']).to eq('027297134324_USEWC0881996')
            expect(result[1][:'extracted_upc']).to eq('027297134324')
            expect(result[1][:'extracted_upc_long']).to eq('27297134324')
            expect(result[1][:'isrc']).to eq('USEWC0881996')
            expect(result[2][:'custom_id']).to eq('888831442738_QMFMG1478504_16959734')
            expect(result[2][:'extracted_upc']).to eq('888831442738')
            expect(result[2][:'extracted_upc_long']).to eq('888831442738')
            expect(result[2][:'isrc']).to eq('QMFMG1478504')
            expect(result[3][:'custom_id']).to eq('889176767739')
            expect(result[3][:'extracted_upc']).to eq('889176767739')
            expect(result[3][:'extracted_upc_long']).to eq('889176767739')
            expect(result[3][:'isrc']).to eq(nil)
            expect(result[4][:'custom_id']).to eq('889845198215_QM4TX1583936')
            expect(result[4][:'extracted_upc']).to eq('889845198215')
            expect(result[4][:'extracted_upc_long']).to eq('889845198215')
            expect(result[4][:'isrc']).to eq('QM4TX1583936')
            expect(result[5][:'custom_id']).to eq('988365200948')
            expect(result[5][:'extracted_upc']).to eq('988365200948')
            expect(result[5][:'extracted_upc_long']).to eq('988365200948')
            expect(result[5][:'isrc']).to eq('MXF365200948')
            expect(result[6][:'custom_id']).to eq('MXF365200948')
            expect(result[6][:'extracted_upc']).to eq(nil)
            expect(result[6][:'extracted_upc_long']).to eq(nil)
            expect(result[6][:'isrc']).to eq('')
        end
    end
    describe 'views in youtube monthly' do
          it 'has correct counts of ad enabled, and total views for all sources' do
            result = snowflake_conn.fetch("SELECT source, COUNT(*) as count,
            SUM(owned_views_ad_enabled) as owned_views_ad_enabled,
            SUM(owned_views) as owned_views
            FROM staging_raw_youtube_monthly_v2
            WHERE download_date>='2001-01-01' AND download_date<='2001-01-31'
            GROUP BY source ORDER BY source ASC;").all
            result.each do |row|
              puts row
            end
            expect(result[0][:'source']).to eq('DMGI')
            expect(result[0][:'count']).to eq('2')
            expect(result[0][:'owned_views_ad_enabled']).to eq('2347')
            expect(result[0][:'owned_views']).to eq('2387')
            expect(result[1][:'source']).to eq('IODA')
            expect(result[1][:'count']).to eq('2')
            expect(result[1][:'owned_views_ad_enabled']).to eq('2')
            expect(result[1][:'owned_views']).to eq('2')
            expect(result[2][:'source']).to eq('ORCHARD')
            expect(result[2][:'count']).to eq('3')
            expect(result[2][:'owned_views_ad_enabled']).to eq('3')
            expect(result[2][:'owned_views']).to eq('3')
          end
          it 'has correct counts of embedded views, and watch page views for all sources' do
            result = snowflake_conn.fetch("SELECT source, COUNT(*) as count,
            SUM(owned_views_embedded_player) as owned_views_embedded_player,
            SUM(owned_views_watch_page) as owned_views_watch_page
            FROM staging_raw_youtube_monthly_v2
            WHERE download_date>='2001-01-01' AND download_date<='2001-01-31'
            GROUP BY source ORDER BY source ASC;").all
            result.each do |row|
              puts row
            end
            expect(result[0][:'source']).to eq('DMGI')
            expect(result[0][:'count']).to eq('2')
            expect(result[0][:'owned_views_embedded_player']).to eq('2')
            expect(result[0][:'owned_views_watch_page']).to eq('2385')
            expect(result[1][:'source']).to eq('IODA')
            expect(result[1][:'count']).to eq('2')
            expect(result[1][:'owned_views_embedded_player']).to eq('0')
            expect(result[1][:'owned_views_watch_page']).to eq('2')
            expect(result[2][:'source']).to eq('ORCHARD')
            expect(result[2][:'count']).to eq('3')
            expect(result[2][:'owned_views_embedded_player']).to eq('0')
            expect(result[2][:'owned_views_watch_page']).to eq('3')
          end
        end
  end
end