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 Deezer... ***'
  snowflake_conn.execute(
    "DELETE FROM staging_raw_deezer_v2
    WHERE transaction_date='2026-05-18';")
  snowflake_conn.execute(
    "DELETE FROM fact_analytics
    WHERE download_activity_date='2026-05-18' AND storeid=348;")
  snowflake_conn.execute(
    "DELETE FROM fact_analytics_error
    WHERE reportdate='2026-05-18' AND storeid=348;")
  # 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 'deezer etl' do
  it_behaves_like 'a swf-feed-ingestion flow', 'deezer', '2026-05-18', 600 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_deezer_v2
            WHERE transaction_date='2026-05-18';").first
          puts result
          expect(result[:"count(*)"]).to eq('16')
        end

        it 'has the right unit count' do
          result = snowflake_conn.fetch(
            "SELECT SUM(unit_count) FROM staging_raw_deezer_v2
            WHERE transaction_date='2026-05-18';").first
          puts result
          expect(result[:'sum(unit_count)']).to eq('73')
        end

        it 'has the right number of paid transactions' do
          result = snowflake_conn.fetch(
            "SELECT COUNT(*) FROM staging_raw_deezer_v2
            WHERE filename='TheOrchard_20260518_20260518.txt';").first
          puts result
          expect(result[:'count(*)']).to eq('9')
        end

        it 'has the right number of free transactions' do
          result = snowflake_conn.fetch(
            "SELECT COUNT(*) FROM staging_raw_deezer_v2
            WHERE filename='TheOrchard_20260518_20260518_TB.txt';").first
          puts result
          expect(result[:'count(*)']).to eq('7')
        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-18' AND storeid=348;").first
            puts result
            expect(result[:'count(*)']).to eq('6')
          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-18' AND storeid=348;").first
            puts result
            expect(result[:'sum(units)']).to eq('24')
          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-18' AND storeid=348
              GROUP BY labelid ORDER BY labelid ASC;").all
            expect(result[0][:'labelid']).to eq('8869')
            expect(result[0][:'count']).to eq('6')
            expect(result[0][:'units']).to eq('39')
            expect(result[0][:'paidunits']).to eq('39')
            expect(result[0][:'freeunits']).to eq('0')
            expect(result[1][:'labelid']).to eq('18000')
            expect(result[1][:'count']).to eq('4')
            expect(result[1][:'units']).to eq('10')
            expect(result[1][:'paidunits']).to eq('0')
            expect(result[1][:'freeunits']).to eq('10')
            result.each do |row|
              puts row
            end
          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-18'
            AND storeid=348 GROUP BY countryid
            ORDER BY countryid ASC;").all
          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('3')
          expect(result[2][:'countryid']).to eq('3')
          expect(result[2][:'count']).to eq('1')
          expect(result[3][:'countryid']).to eq('6')
          expect(result[3][:'count']).to eq('1')
          result.each do |row|
            puts row
          end
        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-18' AND storeid=348
            GROUP BY transactiontypeid ORDER BY transactiontypeid ASC;").all
          expect(result[0][:'transactiontypeid']).to eq('1')
          expect(result[0][:'count']).to eq('4')
          expect(result[1][:'transactiontypeid']).to eq('10')
          expect(result[1][:'count']).to eq('6')
        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-18' AND storeid=348
            GROUP BY releaseid ORDER BY releaseid ASC;").all
          expect(result[0][:'releaseid']).to eq('28948113187')
          expect(result[0][:'count']).to eq('2')
          expect(result[1][:'releaseid']).to eq('886788273825')
          expect(result[1][:'count']).to eq('2')
          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