#!/usr/bin/perl 

use strict;

use lib '/app/tools/common/lib';
use lib '/app/tools/sale_import/lib';

use Date::Calc qw(Add_Delta_Days);

use Common::Util qw(trimspaces);
use Business::UPC;
use Getopt::Std;
use Spreadsheet::ParseExcel;

my %opt;
getopts( 'f:', \%opt );

my $catalog_file = $opt{f};

unless ($catalog_file) {
    die usage("You must specify a catalog file to import");
}

unless ( -e $catalog_file ) {
    die usage("The specified catalog file does not exist");
}

my @headers = qw(
  album-name
  album-artist
  label-name
  catalog-id
  upc
  album-id
  release-date
  upc-alt
  album-custom-1
  album-custom-2
  album-custom-3
  track-name
  disc-no
  disc-name
  track-no
  track-minutes
  track-seconds
  track-artist
  isrc
  track-id
  genre
  track-custom-1
  track-custom-2
  track-custom-3
);

my %fieldMap = (
    'album-name'     => 1,
    'album-artist'   => 0,
    'label-name'     => undef,
    'catalog-id'     => 2,
    'upc'            => undef,
    'album-id'       => undef,
    'release-date'   => 8,
    'upc-alt'        => undef,
    'album-custom-1' => undef,
    'album-custom-2' => undef,
    'album-custom-3' => undef,
    'track-name'     => 4,
    'disc-no'        => undef,
    'disc-name'      => undef,
    'track-no'       => undef,
    'track-minutes'  => undef,
    'track-seconds'  => undef,
    'track-artist'   => 0,
    'isrc'           => 3,
    'track-id'       => undef,
    'genre'          => undef,
    'track-custom-1' => undef,
    'track-custom-2' => undef,
    'track-custom-3' => undef,
);

my $i = 1;

print join( "\t", @headers ) . "\n";

my $oBook = Spreadsheet::ParseExcel::Workbook->Parse($catalog_file);
my ( $iR, $iC, $oWks, $oWkc );
my %catalog = ();

foreach my $oWks ( @{ $oBook->{Worksheet} } ) {
    next unless defined $oWks->{MaxRow};
    print STDERR "processing $oWks->{Name}\n";

    my $label = $oWks->{Name};

    #$sheetName =~ s/\s+/_/g;

    for ( my $iR = $oWks->{MinRow} ; $iR <= $oWks->{MaxRow} ; $iR++ ) {
        my @cols;
        map {
            #push(@cols, ref($_) ? $_->Value : '');
            push( @cols, ref($_) ? $_->{Val} : '' );
        } @{ $oWks->{Cells}[$iR] };

        map { $catalog{$_} = defined $fieldMap{$_} ? trimspaces( $cols[ $fieldMap{$_} ] ) : '' } @headers;

        # skip blank lines (the label field will ususally be filled in even for blank lines)
        next
          unless ( $catalog{'album-artist'}
            && $catalog{'album-name'}
            && $catalog{isrc}
            && $catalog{'track-name'}
            && $catalog{'catalog-id'} =~ /^\d+$/ );

        $catalog{'label-name'} = $label;

        # clean up some of the fields

        if ( $catalog{'release-date'} =~ m/^\d+$/ ) {
            my (@date) = Add_Delta_Days( 1900, 1, 1, $catalog{'release-date'} );
            $catalog{'release-date'} = sprintf( "%d-%02d-%02d", @date );
        }

        # strip format labels (lp,cd) in album names
        $catalog{'album-name'} =~ s/\s+\((lp|cd)\)\s*$//;

        # reformat catalog-id to 5-digit number (and save format number, if present)
        $catalog{'catalog-id'} =~ s/^.*(\d{5})(-(\d))?$/$1/;

        # use the format number from the catalog-id (rare)
        # otherwise, assume it is = 2 (which seems to be the case for CDs)
        my $format_num = ($3) ? $3 : 2;

        unless ( $catalog{'catalog-id'} =~ /^\d{5}$/ ) {
            $catalog{'catalog-id'} = undef;
            $format_num = undef;
        }

        # derive the upc from the catalog-id and format values
        # I am artificially setting the checkdigit to '0' -- we'll fix it later
        my $proposed_upc = '04577' . $catalog{'catalog-id'} . $format_num . '0';
        my $upcObj       = new Business::UPC($proposed_upc);

        unless ( $upcObj->is_valid ) {
            $upcObj->fix_check_digit;
        }

        $catalog{upc} = $upcObj->as_upc;

        # use catalog-id to populate album_id
        $catalog{'album-id'} = $catalog{'catalog-id'};

        # combine upc and isrc to populate track_id
        $catalog{'track-id'} =
            $catalog{isrc}
          ? $catalog{upc} . $catalog{isrc}
          : $catalog{upc} . $i++;

        # finally, print the result
        for ( my $j = 0 ; $j < scalar @headers - 1 ; $j++ ) {
            print $catalog{ $headers[$j] }, "\t";
        }
        print $catalog{ $headers[-1] }, "\n";

    }
}