require 'suite_helper'
require 'shared_examples_for_flows'

# all specs making use of 'shared_examples_for_flows' need to define
@cloned_release_id = nil
$date = '2026-01-01'
$storeid = 624

# used by specs that ensure we didn't process physical releases
def add_physical_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, give it a new id, clone physical release values
  # and insert it as new release.
  release = snowflake_conn.fetch(
    "SELECT * FROM dim_release WHERE display_upc = '886788273825';"
  ).first
  @cloned_release_id = (max_release_id + 500_000).to_s
  release[:releaseid] = @cloned_release_id
  release[:product_type] = 'physical'
  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
  puts '*** setup_flow for FangangoNOW... ***\n'
  add_physical_release
end

RSpec.describe 'FangangoNOW etl' do
  it_behaves_like 'a swf-feed-ingestion flow', 'fandango_now', '2026-01-01', 600 do
    describe '*** Connects To Snowflake ***\n' do
      describe '*** Staging Raw FandangoNOW ***\n' do
        it 'has the right transaction date' do
          result = snowflake_conn.fetch(
            "SELECT COUNT(*) FROM STAGING_RAW_FANDANGO_NOW
            WHERE download_date='#{$date}';"
          ).first
          puts result
          expect(result[:"count(*)"]).to eq('48')
        end

        it 'has the right DAY_UNITS count' do
          result = snowflake_conn.fetch(
            "SELECT SUM(DAY_UNITS) as sum FROM STAGING_RAW_FANDANGO_NOW
            WHERE download_date='#{$date}';"
          ).first
          puts result
          expect(result[:'sum']).to eq('164')
        end

        it 'has the right DAY_REVENUE amount' do
          result = snowflake_conn.fetch(
            "SELECT SUM(DAY_REVENUE) as sum FROM STAGING_RAW_FANDANGO_NOW
            WHERE download_date='#{$date}';"
          ).first
          puts result
          expect(result[:'sum']).to eq('1327.16')
        end

        it 'has the right number of UHD transactions' do
          result = snowflake_conn.fetch(
            "SELECT COUNT(*) FROM STAGING_RAW_FANDANGO_NOW
            WHERE definition_type = 'UHD'
            and download_date='#{$date}';"
          ).first
          puts result
          expect(result[:'count(*)']).to eq('2')
        end
      end

      describe '*** Fact Analytics Error ***\n' do
        it 'has the correct record counts for the date' do
          result = snowflake_conn.fetch(
            "SELECT COUNT(*) FROM FACT_ANALYTICS_error
            WHERE reportdate='#{$date}' AND storeid=#{$storeid};"
          ).first
          puts result
          expect(result[:'count(*)']).to eq('5')
        end

        it 'has the correct sum of unit for the date' do
          result = snowflake_conn.fetch(
            "SELECT SUM(units) as sum FROM FACT_ANALYTICS_error
            WHERE reportdate='#{$date}' AND storeid=#{$storeid};"
          ).first
          puts result
          expect(result[:'sum']).to eq('8')
        end
      end

      describe '*** Fact Analytics ***\n' do
        it 'has correct counts of units, paidunits & freeunits for release' do
          result = snowflake_conn.fetch(
            "SELECT releaseid, COUNT(*) as count, SUM(units) as units,
            SUM(paidunits) as paidunits, SUM(freeunits) as freeunits,
            sum(royaltydollar) as royaltydollar
            FROM FACT_ANALYTICS
            WHERE download_activity_date='#{$date}' AND storeid=#{$storeid}
            GROUP BY releaseid
            ORDER BY releaseid ASC
            LIMIT 2;"
          ).all
          expect(result[0][:'releaseid']).to eq('190374485784')
          expect(result[0][:'count']).to eq('3')
          expect(result[0][:'units']).to eq('8')
          expect(result[0][:'paidunits']).to eq('8')
          expect(result[0][:'freeunits']).to eq('0')
          expect(result[0][:'royaltydollar']).to eq('34.050000')
          expect(result[1][:'releaseid']).to eq('190374490535')
          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[1][:'royaltydollar']).to eq('5.370000')
        end

        it 'has the correct count for formatid' do
          result = snowflake_conn.fetch(
            "SELECT formatid, COUNT(*) AS count
            FROM FACT_ANALYTICS
            WHERE download_activity_date='#{$date}'
            AND storeid=#{$storeid}
            GROUP BY formatid
            ORDER BY formatid ASC
            LIMIT 3;"
          ).all
          # HD
          expect(result[0][:'formatid']).to eq('2')
          expect(result[0][:'count']).to eq('20')
          # SD
          expect(result[1][:'formatid']).to eq('3')
          expect(result[1][:'count']).to eq('22')
          # UHD
          expect(result[2][:'formatid']).to eq('4')
          expect(result[2][:'count']).to eq('1')
        end

        it 'has the correct counts for transactiontypeid' do
          result = snowflake_conn.fetch(
            "SELECT transactiontypeid, COUNT(*) AS count
            FROM FACT_ANALYTICS
            WHERE download_activity_date='#{$date}' AND storeid=#{$storeid}
            GROUP BY transactiontypeid
            ORDER BY transactiontypeid ASC
            LIMIT 2;"
          ).all
          expect(result[0][:'transactiontypeid']).to eq('12')
          expect(result[0][:'count']).to eq('6')
          expect(result[1][:'transactiontypeid']).to eq('16')
          expect(result[1][:'count']).to eq('37')
        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

def truncate_rows
  # clean up any result rows we created
  puts '*** Truncating result FangangoNOW... ***\n'
  snowflake_conn.execute(
    "DELETE FROM STAGING_RAW_FANDANGO_NOW
    WHERE download_date='#{$date}';"
  )
  snowflake_conn.execute(
    "DELETE FROM FACT_ANALYTICS
    WHERE download_activity_date='#{$date}' AND storeid=#{$storeid};"
  )
  snowflake_conn.execute(
    "DELETE FROM FACT_ANALYTICS_error
    WHERE reportdate='#{$date}' AND storeid=#{$storeid};"
  )
  # clean up our cloned physical release
  snowflake_conn.execute(
    "DELETE FROM dim_release
    WHERE display_upc='886788273825' AND product_type='physical';"
  )
end