#---------------------------------------------------------------
# ____                   _ _         ____  _
#|  _ \ ___  _   _  __ _| | |_ _   _/ ___|| |__   __ _ _ __ ___
#| |_) / _ \| | | |/ _` | | __| | | \___ \| '_ \ / _` | '__/ _ \
#|  _ < (_) | |_| | (_| | | |_| |_| |___) | | | | (_| | | |  __/
#|_| \_\___/ \__, |\__,_|_|\__|\__, |____/|_| |_|\__,_|_|  \___|
#            |___/             |___/
#
# Copyright (C) 2012 RoyaltyShare, Inc.   All Rights Reserved
#---------------------------------------------------------------

package BookPub::Catalog::Import::Parser::FlatFile::RSStandard;

use strict;

use Spreadsheet::ParseExcel;
use Spreadsheet::XLSX;
use Data::Dumper;
use Date::Calc;

use lib '/app/tools/common/lib';
use lib '/app/tools/bookpub/lib';
use Common::UTF8;
use Common::Log;
use Common::Util;
use Common::Assert;
use base 'BookPub::Catalog::Import::Parser::FlatFile';

use constant kColTitle          => 0;
use constant kColSubtitle       => 1;
use constant kColEdition        => 2;
use constant kColAuthor         => 3;
use constant kColImprint        => 4;
use constant kColPublisher      => 5;
use constant kColFormat         => 6;
use constant kColReleaseDate    => 7;
use constant kColISBN13         => 8;
use constant kColISBN10         => 9;
use constant kColSupplier       => 10;
use constant kColPrice1         => 11;
use constant kColPriceType1     => 12;
use constant kColPriceCurrency1 => 13;
use constant kColPriceCountry1  => 14;
use constant kColPrice2         => 15;
use constant kColPriceType2     => 16;
use constant kColPriceCurrency2 => 17;
use constant kColPriceCountry2  => 18;
use constant kColPrice3         => 19;
use constant kColPriceType3     => 20;
use constant kColPriceCurrency3 => 21;
use constant kColPriceCountry3  => 22;
use constant kColPrice4         => 23;
use constant kColPriceType4     => 24;
use constant kColPriceCurrency4 => 25;
use constant kColPriceCountry4  => 26;

# !!! Really all this is used for is to return the sentdate value.
#
sub header {
    my ($self) = @_;

    return { sentdate => $self->{sentdate} };
}

sub _parser { $_[0]->{_parser} }

sub productFileVersion {
    # at the moment can take values (old version 2, or new v3)
    # by default we will use v2 for all child classes for backward compatibility
    # even if a file is not in ONIX format (like RSStandard)
    return $_[0]->{_product_file_version} = 2;
}

sub nextProduct {
    my $self = shift;

    my $row = $self->_nextRow();
    return undef unless $row;

    # Map the columns properly.
    #

    # Parse out all the various bits.
    # I want to make this somewhat flexible... The initial file I am working from
    # is all epub books, but we might need to use this for physical stuff at some point.
    # So I don't want to _assume_ anything.
    #
    # Note that I am going to make this 'brittle' on purpose.   If things aren't exactly as I
    # expect, I want to halt (and force somebody to take a closer look).
    #
    my $title        = $row->[kColTitle];
    my $subtitle     = $row->[kColSubtitle];
    my $edition      = $row->[kColEdition];
    my $contributors = $self->_parseContributors( $row->[kColAuthor] );
    my $imprint      = $row->[kColImprint];
    my $publisher    = $row->[kColPublisher];
    my ( $format, $epubType ) = $self->_parseFormat( $row->[kColFormat] );
    my $pubDate  = $self->_parseDate( $row->[kColReleaseDate] );
    my $isbn13   = $row->[kColISBN13];
    my $isbn10   = $row->[kColISBN10];
    my $supplier = $row->[kColSupplier];

    # Default supplier to publisher if it was left blank.
    if ( !$supplier ) {
        $supplier = $publisher;
    }

    my $price1         = $self->_parsePrice( $row->[kColPrice1] );
    my $priceType1     = $row->[kColPriceType1];
    my $priceCurrency1 = $row->[kColPriceCurrency1];
    my $priceCountry1  = $row->[kColPriceCountry1];

    my $price2         = $self->_parsePrice( $row->[kColPrice2] );
    my $priceType2     = $row->[kColPriceType2];
    my $priceCurrency2 = $row->[kColPriceCurrency2];
    my $priceCountry2  = $row->[kColPriceCountry2];

    my $price3         = $self->_parsePrice( $row->[kColPrice3] );
    my $priceType3     = $row->[kColPriceType3];
    my $priceCurrency3 = $row->[kColPriceCurrency3];
    my $priceCountry3  = $row->[kColPriceCountry3];

    my $price4         = $self->_parsePrice( $row->[kColPrice4] );
    my $priceType4     = $row->[kColPriceType4];
    my $priceCurrency4 = $row->[kColPriceCurrency4];
    my $priceCountry4  = $row->[kColPriceCountry4];

    my $prodRef = {
        title => {
            titletype => '01',
            titletext => $title,
            subtitle  => $subtitle,
        },
        editionnumber     => $edition,
        publicationdate   => $pubDate,
        productidentifier => {
            idtype  => 'ISBN-13',
            idvalue => $isbn13,
        },

        #            productform => 'AJ',  # AJ == downloadable audio file.  Might not be the best choice?
        productform => $format,
        epubtype    => $epubType,
        contributor => $contributors,
        publisher   => {
            pubrole       => '01',
            publishername => $publisher,
        },
        imprint => {
            imprintname => $imprint,
        },
    };

    if ($isbn10) {
        $prodRef->{productidentifier} = [ {
                idtype  => 'ISBN-13',
                idvalue => $isbn13,
            },
            {
                idtype  => 'ISBN-10',
                idvalue => $isbn10,
            },
        ];
    }

    if ( defined $price1 && $price1 != '' ) {
        die "ERROR - incomplete price data" unless ( $supplier && $priceType1 && $priceCurrency1 && $priceCountry1 );

        # default the ID to RRP
        my $priceTypeID = '01';

        if ( lc($priceType1) eq 'rrp' ) {
            $priceTypeID = '01';
        }

        if ( lc($priceType1) eq 'rrpplustax' ) {
            $priceTypeID = '02';
        }

        if ( lc($priceType1) eq 'agency' ) {
            $priceTypeID = '41';
        }

        if ( lc($priceType1) eq 'agencyplustax' ) {
            $priceTypeID = '42';
        }

        $prodRef->{supplydetail} = {
            suppliername        => $supplier,
            supplierrole        => '01',
            availabilitycode    => 'NP',
            productavailability => '10',
            price               => {
                pricetypecode => $priceTypeID,
                pricestatus   => '01',
                priceamount   => $price1,
                currencycode  => $priceCurrency1,
                countrycode   => $priceCountry1,
            },
        };
    }

    if ( defined $price2 && $price2 != '' ) {
        die "ERROR - incomplete price data" unless ( $supplier && $priceType2 && $priceCurrency2 && $priceCountry2 );

        # default the ID to RRP
        my $priceTypeID = '01';

        if ( lc($priceType2) eq 'rrp' ) {
            $priceTypeID = '01';
        }

        if ( lc($priceType2) eq 'rrpplustax' ) {
            $priceTypeID = '02';
        }

        if ( lc($priceType2) eq 'agency' ) {
            $priceTypeID = '41';
        }

        if ( lc($priceType2) eq 'agencyplustax' ) {
            $priceTypeID = '42';
        }

        $prodRef->{supplydetail2} = {
            suppliername        => $supplier,
            supplierrole        => '01',
            availabilitycode    => 'NP',
            productavailability => '10',
            price               => {
                pricetypecode => $priceTypeID,
                pricestatus   => '01',
                priceamount   => $price2,
                currencycode  => $priceCurrency2,
                countrycode   => $priceCountry2,
            },
        };
    }

    if ( defined $price3 && $price3 != '' ) {
        die "ERROR - incomplete price data" unless ( $supplier && $priceType3 && $priceCurrency3 && $priceCountry3 );

        # default the ID to RRP
        my $priceTypeID = '01';

        if ( lc($priceType3) eq 'rrp' ) {
            $priceTypeID = '01';
        }

        if ( lc($priceType3) eq 'rrpplustax' ) {
            $priceTypeID = '02';
        }

        if ( lc($priceType3) eq 'agency' ) {
            $priceTypeID = '41';
        }

        if ( lc($priceType3) eq 'agencyplustax' ) {
            $priceTypeID = '42';
        }

        $prodRef->{supplydetail3} = {
            suppliername        => $supplier,
            supplierrole        => '01',
            availabilitycode    => 'NP',
            productavailability => '10',
            price               => {
                pricetypecode => $priceTypeID,
                pricestatus   => '01',
                priceamount   => $price3,
                currencycode  => $priceCurrency3,
                countrycode   => $priceCountry3,
            },
        };
    }

    if ( defined $price4 && $price4 != '' ) {
        die "ERROR - incomplete price data" unless ( $supplier && $priceType4 && $priceCurrency4 && $priceCountry4 );

        # default the ID to RRP
        my $priceTypeID = '01';

        if ( lc($priceType4) eq 'rrp' ) {
            $priceTypeID = '01';
        }

        if ( lc($priceType4) eq 'rrpplustax' ) {
            $priceTypeID = '02';
        }

        if ( lc($priceType4) eq 'agency' ) {
            $priceTypeID = '41';
        }

        if ( lc($priceType4) eq 'agencyplustax' ) {
            $priceTypeID = '42';
        }

        $prodRef->{supplydetail4} = {
            suppliername        => $supplier,
            supplierrole        => '01',
            availabilitycode    => 'NP',
            productavailability => '10',
            price               => {
                pricetypecode => $priceTypeID,
                pricestatus   => '01',
                priceamount   => $price4,
                currencycode  => $priceCurrency4,
                countrycode   => $priceCountry4,
            },
        };
    }

    return $prodRef;
}

sub _parseDate {
    my ( $self, $date ) = @_;

    # the date is sometimes being returned in the "days since 1/1/1900" format, so we need
    # to convert it.  We'll check if it's a 5 digit number and greater than the
    # number of days since 1/1/1900 on 1/1/2006, which is 38716
    if ( $date =~ /^\d{5}$/ and $date > 38716 ) {
        $date = Spreadsheet::ParseExcel::Utility::ExcelFmt( "yyyy-mm-dd", $date );
        return $date;
    } else {
        $date =~ s/^(\d{4})(\d\d)(\d\d)/$1-$2-$3/;
    }
    return $date;
}

my %gFormats = (

    # Digital formats
    'adobe ebook reader'        => { format => 'DG', epub => '004' },
    'epub'                      => { format => 'DG', epub => '029' },
    'epub2'                     => { format => 'DG', epub => '029' },    # non-standard name
    'epub3'                     => { format => 'DG', epub => '029' },    # non-standard name
    'ereader (aka palm reader)' => { format => 'DG', epub => '010' },
    'gemstar reb 1100'          => { format => 'DG', epub => '013' },
    'google edition'            => { format => 'DG', epub => '032' },
    'html'                      => { format => 'DG', epub => '001' },
    'kindle'                    => { format => 'DG', epub => '031' },
    'microsoft reader level 5'  => { format => 'DG', epub => '006' },
    'mobipocket'                => { format => 'DG', epub => '022' },
    'open ebook'                => { format => 'DG', epub => '023' },
    'pdf'                       => { format => 'DG', epub => '002' },
    'web pdf'                   => { format => 'DG', epub => '002' },    # non-standard name
    'sony bbeb'                 => { format => 'DG', epub => '027' },
    'txt'                       => { format => 'DG', epub => '025' },
    'vook'                      => { format => 'DG', epub => '033' },
    'book \'app\' for ios'      => { format => 'DG', epub => '040' },
    'android \'app\''           => { format => 'DG', epub => '041' },
    'other \'app\''             => { format => 'DG', epub => '042' },
    'ibook'                     => { format => 'DG', epub => '044' },
    'epib'                      => { format => 'DG', epub => '045' },
    'vitalbook'                 => { format => 'DG', epub => '039' },

    # The following were added for SAGE.  We don't really have anything to map them to,
    # so we're going with code 099 (Unknown, or no code yet assigned for this format).
    'custom institutional edition' => { format => 'DG', epub => '099' },
    'slim pack'                    => { format => 'DG', epub => '099' },
    'vst picturebook'              => { format => 'DG', epub => '099' },
    'vst xml'                      => { format => 'DG', epub => '099' },
    'xml-tei'                      => { format => 'DG', epub => '099' },

    # Physical formats
    'board book'              => { format => 'BH' },
    'hardback'                => { format => 'BB' },
    'hardcover'               => { format => 'BB' },    # non-standard name
    'other book format'       => { format => 'BZ' },
    'paperback / softback'    => { format => 'BC' },
    'paperback'               => { format => 'BC' },    # non-standard name
    'audio'                   => { format => 'AA' },
    'cd-audio'                => { format => 'AC' },
    'downloadable audio file' => { format => 'AJ' },
    'e-journal'               => { format => 'JD' },
);

sub _parseFormat {
    my ( $self, $formatString ) = @_;

    $formatString = lc($formatString);

    # I want to find the one format string that matches the incoming string.
    # So, for example, 'paperback' should match 'paperback / softback' uniquely.
    #
    my $matchingFormat;
    foreach my $testFormat ( keys %gFormats ) {
        if ( $testFormat eq $formatString ) {

            # exact match wins.
            #
            $matchingFormat = $testFormat;
            last;
        }

        if ( $testFormat =~ m/$formatString/ ) {
            if ($matchingFormat) {
                die "ERROR - format '$formatString' is ambiguous";
            }
            $matchingFormat = $testFormat;
        }
    }

    die "ERROR - Unknown format '$formatString'" unless $matchingFormat;

    my $format = $gFormats{$matchingFormat}{format};
    my $epub   = $gFormats{$matchingFormat}{epub};

    return ( $format, $epub );
}

sub _parseISBN {
    my ( $self, $isbnDigital, $isbnPhysical, $format ) = @_;
    return $isbnDigital if ( 'DG' eq $format );
    return $isbnPhysical;
}

sub _parsePrice {
    my ( $self, $price ) = @_;

    $price =~ s/[^\d\.]//g;
    return $price;
}

sub _parseContributors {
    my ( $self, $authors ) = @_;

    my $sequence = 1;
    my @contributors;

    my $authorList = $self->_splitContributorString($authors);

    my $withFlag = 0;

    # Note that a 'with' denotes a contributor code of A02.
    #
    $withFlag = 1 if $authors =~ / with /;
    for ( my $i = 0 ; $i < scalar @$authorList ; $i++ ) {
        my $code = 'A01';
        if ( $withFlag && $i > 0 ) {
            $code = 'A02';
        }

        # Check for (Edited By), which has a different code. (B01)
        #
        my $name = $authorList->[$i]->{name};
        if ( $name =~ /\(edited by\)/i ) {
            $name =~ s/\(edited by\)//gi;
            $name = Common::Util::trimspaces($name);
            $code = 'B01';
        }

        push @contributors,
          {
            contributorrole          => $code,
            sequencenumber           => $sequence,
            $authorList->[$i]->{tag} => $name,
          };

        $sequence++;
    }

    return \@contributors;
}

sub _splitContributorString {
    my ( $self, $string ) = @_;

    # There are several different patterns I can discern here:
    #
    # Last, First
    # First Last
    # First Last, First Last
    # Last, First; Last, First
    # First Last and First Last
    # Last, First & First
    # First Last with First Last
    # First Last, Ph.D.
    # First Last, M.D.
    # Last/Last/Last/Last
    # Last, First (something else?)

    my @returnList;

    if ( $string =~ /(.*) and (.*)/ ) {
        my $firstName  = $1;
        my $secondName = $2;
        $firstName =~ s/,$//;
        $secondName =~ s/,$//;
        push @returnList, { tag => 'personname', name => Common::Util::trimspaces($firstName) };
        push @returnList, { tag => 'personname', name => Common::Util::trimspaces($secondName) };
    } elsif ( $string =~ /(.*) with (.*)/ ) {
        my $firstName  = $1;
        my $secondName = $2;
        $firstName =~ s/,$//;
        $secondName =~ s/,$//;
        push @returnList, { tag => 'personname', name => Common::Util::trimspaces($firstName) };
        push @returnList, { tag => 'personname', name => Common::Util::trimspaces($secondName) };
    } elsif ( $string =~ /;/ ) {
        my @allNames = split( ';', $string );
        foreach my $name (@allNames) {
            push @returnList, { tag => 'personnameinverted', name => Common::Util::trimspaces($name) };
        }
    } elsif ( $string =~ /(.*), (.*) & (.*)/ ) {
        my $lastName    = Common::Util::trimspaces($1);
        my $firstFirst  = Common::Util::trimspaces($2);
        my $secondFirst = Common::Util::trimspaces($3);
        push @returnList, { tag => 'personname', name => "$firstFirst $lastName" };
        push @returnList, { tag => 'personname', name => "$secondFirst $lastName" };
    } else {

        # So much for the 'easy' cases.  Now we need to look a bit more closely.
        # I might have several names separated by commas, or just one last, first, or
        # some other wackadoodle structure.
        # (!!!) The name/name/name/name thing is stupid, and I will treat that as a single name for now.
        #
        my @commaChunks = split( ',', $string );
        if ( 1 == scalar @commaChunks ) {
            push @returnList, { tag => 'personname', name => $string };
        } else {

            # So we'll look at the first chunk.  If that has multiple words, then we have
            # either a set of 'first last, first last', or perhaps a ',.Ph.D'.
            #
            my @spaceChunks  = split( / /, Common::Util::trimspaces( $commaChunks[0] ) );
            my @spaceChunks2 = split( / /, Common::Util::trimspaces( $commaChunks[1] ) );
            if ( 1 == scalar @spaceChunks ) {

                # Guessing this is a single last, first
                #
                push @returnList, { tag => 'personnameinverted', name => Common::Util::trimspaces($string) };
            } elsif ( 2 == scalar @spaceChunks && 2 != scalar @spaceChunks2 ) {

                # This is either two names, or one name with an honorific
                # OR, it could be a wacky double last name, like 'Randolph Price, John'
                #
                my $name1 = Common::Util::trimspaces( $spaceChunks[0] );
                my $name2 = Common::Util::trimspaces( $spaceChunks[1] );

                if ( 1 == scalar @spaceChunks2 ) {
                    if ( $name2 =~ /\..\./ ) {

                        # One nifty name
                        #
                        push @returnList, { tag => 'personname', name => Common::Util::trimspaces($string) };
                    } else {
                        push @returnList, { tag => 'personnameinverted', name => Common::Util::trimspaces($string) };
                    }
                } else {
                    push @returnList, { tag => 'personname', name => $name1 };
                    push @returnList, { tag => 'personname', name => $name2 };
                }
            } else {

                # A list of several comma-separated regular names
                #
                foreach my $name (@commaChunks) {
                    push @returnList, { tag => 'personname', name => Common::Util::trimspaces($name) };
                }
            }
        }

    }

    return \@returnList;
}

sub _init {
    my ( $self, %args ) = @_;

    my $file = $args{file};
    assert( $file, "ERROR - missing file parameter" );

    $self->{filePath} = $file;

    # If the filename doesn't have a date stamp, we can't continue.
    # !!! Going to use the first 8 digits we see.
    #
    if ( $file =~ /(\d\d\d\d\d\d\d\d)/ )

      #    if ($file =~ /(\d\d\d\d\d\d\d\d)_.*\.xls$/)
    {
        $self->{sentdate} = $1;
    } else {
        die "ERROR - filename does not contain a timestamp, or doesn't appear to be an .xls file";
    }

    # Prime the row iterator, then read the header line.
    # We can make sure the header matches our expectations.
    #
    my $workbook;
    if ( $file =~ /\.xlsx$/i ) {
        $workbook = Spreadsheet::XLSX->new($file);
    } else {
        $workbook = Spreadsheet::ParseExcel::Workbook->Parse($file);
    }

    #    my $workbook = Spreadsheet::ParseExcel::Workbook->Parse($self->{filePath});

    if ( !$workbook ) {
        die "ERROR - unable to parse file $file as an Excel file";
    }
    if ( !$workbook->{Worksheet} ) {
        die "ERROR - unable to find a 'Worksheet' in file $file";
    }

    $self->{workbook} = $workbook;
    $self->{rowIndex} = 0;

    # Now we can start looking for the header.
    #
    my $firstSheet = $workbook->{Worksheet}->[0];

    # !!! DEBUG - Spew forth the data!
    #
    my $foundHeader = 0;
    while ( my $row = $self->_nextRow() ) {
        if (   $row->[kColTitle] eq 'Title'
            && $row->[kColSubtitle] eq 'Subtitle'
            && $row->[kColAuthor] eq 'Author'
            && $row->[kColImprint] eq 'Imprint'
            && $row->[kColPublisher] eq 'Publisher'
            && $row->[kColFormat] eq 'Format'
            && $row->[kColReleaseDate] eq 'Release Date'
            && ( $row->[kColISBN13] eq 'ISBN-13' || $row->[kColISBN13] eq 'ISBN13' )
            && ( $row->[kColISBN10] eq 'ISBN-10' || $row->[kColISBN10] eq 'ISBN10' )
            && $row->[kColSupplier] eq 'Supplier'
            && $row->[kColPrice1] eq 'Current Price 1'
            && $row->[kColPriceType1] eq 'Price Type 1'
            && $row->[kColPriceCurrency1] eq 'Price Currency 1'
            && $row->[kColPriceCountry1] eq 'Price Country 1'
            && $row->[kColPrice2] eq 'Current Price 2'
            && $row->[kColPriceType2] eq 'Price Type 2'
            && $row->[kColPriceCurrency2] eq 'Price Currency 2'
            && $row->[kColPriceCountry2] eq 'Price Country 2' ) {
            $foundHeader = 1;
            last;
        }
    }

    if ( !$foundHeader ) {
        die "ERROR - Unable to find the Header row in file $file";
    }

    return $self;
}

sub _nextRow {
    my ($self) = @_;

    # Everything ought to be in the first sheet.
    #
    my $sheet = $self->{workbook}->{Worksheet}->[0];

    # If we've read all the lines, we're done.
    #
    if ( !defined $sheet->{MaxRow} || $self->{rowIndex} > $sheet->{MaxRow} ) {
        return undef;
    }

    # If we run into a blank line, then we're also going to call it quits.
    #
    my $hasData;
    my @row;

    for ( my $colIndex = 0 ; defined $sheet->{MaxCol} && $colIndex <= $sheet->{MaxCol} ; $colIndex++ ) {
        my $cell = $sheet->{Cells}[ $self->{rowIndex} ][$colIndex];
        if ($cell) {

            # !!! This code is lifted from BookPub::Sale::File.
            #
            my $value = $cell->{Val};

            if ( $cell->{Code} eq 'ucs2' ) {
                $value = Common::UTF8::Encode( $value, Common::UTF8::kEncodingUCS2 );
            }

            #            if (lc($cell->{Type}) eq 'date')
            if ( 4 == $colIndex ) {
                my $heldValue = $value;
                $value = Spreadsheet::ParseExcel::Utility::ExcelFmt( "yyyy-mm-dd", $value );
                if ( $self->{workbook}{Flg1904} ) {
                    my ( $y, $m, $d ) = Add_Delta_Days( 1904, 1, 1, $heldValue );
                    $value = sprintf( "%04d-%02d-%02d", $y, $m, $d );
                }
            } else {

                # Trim quotes, remove wacky white space, etc.
                #

                $value =~ s/[\n\r\t]+/ /g;
                $value =~ s/^\s+//;
                $value =~ s/\s+$//;
                $value = Common::Util::trimquotes($value);
            }

            my $file = $self->{filePath};

            if ( $file =~ /\.xlsx$/ ) {
                $value =~ s/&amp;/&/;
                $value =~ s/&lt;/</;
                $value =~ s/&gt;/>/;
                $value =~ s/&apos;/'/g;
                $value =~ s/&quot;/"/g;
            }

            push @row, $value;
            $hasData++;
        } else {
            push @row, '';
        }
    }

    $self->{rowIndex}++;

    return undef unless $hasData;
    return undef unless '' ne $row[0];

    #print STDERR "ROW: " . Dumper(\@row) . "\n";

    return \@row;
}

1;