#!/usr/bin/perl

use strict;
use warnings;
use open ':std', ':encoding(UTF-8)';

use JSON::XS;
use Text::CSV;
use FileHandle;

my $csv = Text::CSV->new( {
        escape_char    => '"',
        sep_char       => '\t',
        binary         => 1,
        blank_is_undef => 1,
        empty_is_undef => 1,
    }
);

## get results from snowflake use tab delimited
## select pub_song_id,title from facts.prod.publishing_composition
## export to snowflake-songs.tsv

## seed song cache
my $sfSongs;
my $sfh = FileHandle->new( 'snowflake-songs.csv', 'r' );
while (<$sfh>) {
    next if $_ !~ m/^\d/;
    chomp;
    eval {
        my ( $id, $title ) = split ",", $_;
        $sfSongs->{$id} = $title;
    };
    if ($@) {
        print "$@\n";
    }
}
$sfh->close;

my $found;
my $missing;
my $undefined;
my $fh = FileHandle->new( shift @ARGV, 'r' ) || die "Sales file is required";
while (<$fh>) {
    chomp;
    my @row = split "\t", $_;
    next if $row[0] =~ m/^Song/;
    ## rev might be (1.00) for negative
    my $rev = $row[20];
    if ( $rev =~ m!\(! ) {
        $rev =~ s/\(|\)//g;
        if ( $rev =~ m/,/ ) {
            my $x;
        }
        $rev =~ s/,//;
        $rev =~ s/"//g;
        $rev = $rev * -1;
    }
    if ( defined $row[26] and exists $sfSongs->{ $row[26] } ) {
        $found->{ $row[26] }++;
    } elsif ( defined $row[26] and not exists $sfSongs->{ $row[26] } ) {
        if ( not exists $missing->{ $row[26] } ) {
            $missing->{ $row[26] } = {
                'song'    => $row[1],
                'writer'  => $row[2],
                'artist'  => defined $row[27] ? $row[27] : 'not set',
                'revenue' => 0
            };
        }
        $missing->{ $row[26] }->{'revenue'} += $rev;
    } else {
        $undefined->{"$row[1]-$row[2]"} += $rev;
    }
}
$fh->close;

print STDERR "missing from snowflake:\n";
print STDERR "pub_song_id\tsong_tile\tcomposer\tartist\trevenue\n";
foreach my $sid ( keys %{$missing} ) {
    my $href    = $missing->{$sid};
    my $revenue = sprintf( "%s%.2f", '$', $href->{'revenue'} );
    print STDERR "$sid\t$href->{'song'}\t$href->{'writer'}\t$href->{'artist'}\t$revenue\n";
}
print "\n";

print STDERR "Undefined in ATV file:\n";
print STDERR "song_tile\trevenue\n";
map { print STDERR "$_\t$undefined->{$_}\n" } sort keys %{$undefined};