require 'suite_helper'
require 'shared_examples_for_flows'

@cloned_release_id = nil

# 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 rows... ***'

  snowflake_conn.execute("DELETE FROM staging_raw_pandora WHERE download_date='2026-05-17';")
  snowflake_conn.execute("DELETE FROM fact_analytics WHERE download_activity_date='2026-05-17' AND storeid=708;")
  snowflake_conn.execute("DELETE FROM fact_analytics_error WHERE reportdate='2026-05-17' AND storeid=708;")
  # clean up our cloned physical release
  snowflake_conn.execute("DELETE FROM dim_release WHERE display_upc='886788273825' AND product_type='physical';")
end

# used by specs that ensure we didn't process physical releases
def add_physical_release
  # grab current max releaseid from dim_release
  result = snowflake_conn.fetch('SELECT MAX(releaseid) AS mx FROM dim_release;').first
  max_release_id = result[:'mx'].to_i

  # grab our test release
  release = snowflake_conn.fetch("SELECT * FROM dim_release WHERE display_upc = '886788273825';").first
  # give it a new releaseid
  @cloned_release_id = (max_release_id + 500_000).to_s
  release[:releaseid] = @cloned_release_id
  # make it physical
  release[:product_type] = 'physical'
  # add the cloned physical version of the release
  values = release.values.map{ |v| v ? "'#{v}'" : 'NULL' }.join(', ')
  snowflake_conn.execute(
    "INSERT INTO dim_release
    (#{release.keys.join(', ')}) VALUES (#{values});")
end

# any manual setup tasks that need to occur before SWF flow is kicked off.
# called by shared_example before(:all) hook.
def setup_flow
  add_physical_release
end

RSpec.describe 'pandora etl' do
  it_behaves_like 'a swf-feed-ingestion flow', 'pandora', '2026-05-17', 2500, 'theorchard'  do
    describe 'snowflake users table' do
      describe 'srdv' do
        it 'has the right transaction date' do
          result = snowflake_conn.fetch("SELECT COUNT(*) FROM staging_raw_pandora WHERE download_date='2026-05-17';").first
          puts result
          expect(result[:"count(*)"]).to eq('165819')
        end

        it 'has the right number of paid transactions' do
          result = snowflake_conn.fetch("SELECT COUNT(*) FROM staging_raw_pandora WHERE filename='orchard_US_2026-05-17.txt.bz2';").first
          puts result
          expect(result[:"count(*)"]).to eq('165819')
        end

        describe 'fact analytics error' do
          it 'has the correct record counts for the date' do
            result = snowflake_conn.fetch("SELECT COUNT(*) FROM fact_analytics_error WHERE reportdate='2026-05-17' AND storeid=708;").first
            puts result
            expect(result[:"count(*)"]).to eq('549')
          end

          it 'has the correct unit counts for the date' do
            result = snowflake_conn.fetch("SELECT SUM(units) FROM fact_analytics_error WHERE reportdate='2026-05-17' AND storeid=708;").first
            puts result
            expect(result[:'sum(units)']).to eq('549')
          end
        end

        describe 'fact analytics' do
          it 'has the correct counts of units, paid units and freeunits for label id' do
            result = snowflake_conn.fetch("SELECT labelid, COUNT(*) as count, SUM(units) as units,
            SUM(paidunits) as paidunits, SUM(freeunits) as freeunits FROM fact_analytics
            WHERE download_activity_date='2026-05-17' AND storeid=708 GROUP BY labelid ORDER BY labelid ASC LIMIT 2;").all
            result.each do |row|
              puts row
            end
            expect(result[0][:'labelid']).to eq('634')
            expect(result[0][:'count']).to eq('17')
            expect(result[0][:'units']).to eq('17')
            expect(result[0][:'paidunits']).to eq('17')
            expect(result[0][:'freeunits']).to eq('0')
            expect(result[1][:'labelid']).to eq('736')
            expect(result[1][:'count']).to eq('2')
            expect(result[1][:'units']).to eq('2')
            expect(result[1][:'paidunits']).to eq('2')
            expect(result[1][:'freeunits']).to eq('0')
          end
        end

        it 'has the correct count for countries' do
          result = snowflake_conn.fetch("SELECT countryid, COUNT(*) as count FROM fact_analytics WHERE download_activity_date='2026-05-17' AND storeid=708 GROUP BY countryid ORDER BY countryid ASC;").all
          result.each do |row|
            puts row
          end
          expect(result[0][:'countryid']).to eq('1')
          expect(result[0][:'count']).to eq('165270')
        end

        it 'has the correct counts for transaction types' do
          result = snowflake_conn.fetch("SELECT transactiontypeid, COUNT(*) as count FROM fact_analytics WHERE download_activity_date='2026-05-17' AND storeid=708 GROUP BY transactiontypeid ORDER BY transactiontypeid ASC;").all
          expect(result[0][:'transactiontypeid']).to eq('1')
          expect(result[0][:'count']).to eq('10792')
          expect(result[1][:'transactiontypeid']).to eq('27')
          expect(result[1][:'count']).to eq('154362')
          expect(result[2][:'transactiontypeid']).to eq('48')
          expect(result[2][:'count']).to eq('116')
          result.each do |row|
            puts row
          end
        end

        it 'has the correct ircs' do
          result = snowflake_conn.fetch("SELECT releaseid, COUNT(DISTINCT(isrcid)) as count FROM fact_analytics WHERE download_activity_date='2026-05-17' AND storeid=708 GROUP BY releaseid ORDER BY releaseid ASC;").all
          expect(result[0][:'releaseid']).to eq('8591104220')
          expect(result[0][:'count']).to eq('2')
          expect(result[1][:'releaseid']).to eq('8591500725')
          expect(result[1][:'count']).to eq('1')
          result.each do |row|
            puts row
          end
        end

        it 'did not add tracks for a physical release' do
          result = snowflake_conn.fetch("SELECT * FROM fact_analytics WHERE releaseid=#{@cloned_release_id};")
          expect(result.any?).to be false
        end
      end
    end
  end
end