package BookPub::POS::Sale::File;

use strict;

#use warnings;

use File::Basename;

use Spreadsheet::ParseExcel;
use Spreadsheet::XLSX;
use Text::CSV_XS;
use Date::Calc qw(Add_Delta_YMD Add_Delta_Days);
use Common::Date;

# built in perl func
use File::Copy;

use lib '/app/tools/bookpub/lib';
use BookPub::POS::Import::Factory;
use BookPub::Tracker::Service;
use BookPub::DB::Item::POSFile;
use BookPub::POS::Sale::Feed;

use lib '/app/tools/common/lib';
use Common::Util;
use Common::Log;
use Common::Client;
use Common::Assert;
use Common::RSApp;
use Common::File::UTF8;
use Common::File::UTF16;

use base 'Sale::File';

# ------------------------
# Public Methods
# ------------------------

sub BaseDirectory {

    # !!! Update this to use the config system?
    return "/app/shared/pos_sale_import";
}

sub SimpleUploadFromMemory {
    my %args = @_;

    assert( $args{client_id} );
    assert( $args{content} );
    assert( $args{filename} );
    assert( $args{md5} );
    assert( $args{pos_feed_id} );
    assert( $args{date_start} );
    assert( $args{date_end} );

    return if ( _duplicateUpload( $args{filename}, $args{md5}, $args{pos_feed_id} ) );

    my $uploadDir = _getUploadDir( $args{client_id}, $args{pos_feed_id} );
    my $filename  = _getDestFilename( $args{filename} );
    my $path      = _getUniquePath("$uploadDir/$filename");
    $filename = basename($path);

    open( OUTFILE, ">$path" ) || die "Can not write output file: $path";
    print OUTFILE $args{content};
    close OUTFILE;

    Log->info("   -- Write file to $path");
    if ( -e $path ) {
        return _createFileRecord(
            file_dir       => $uploadDir,
            pos_file_id    => $args{pos_file_id},
            pos_feed_id    => $args{pos_feed_id},
            file_name      => $filename,
            file_md5sum    => $args{md5},
            orig_file_name => $args{filename},
            date_start     => $args{date_start},
            date_end       => $args{date_end}
        );
    }

    return;

}

sub _getUniquePath {
    my $path = shift;
    return $path unless ( -e $path );

    my ( $file, $ext ) = $path =~ /(.*)\.(.*)/;

    for ( my $i = 1 ; $i < 99 ; $i++ ) {
        my $p = sprintf( "%s_%02d.%s", $file, $i, $ext );
        unless ( -e $p ) {
            system("touch $p");
            return $p;
        }
    }

    die "Failed to create a unique filename";
}

sub _duplicateUpload {
    my $filename = shift;
    my $md5      = shift;
    my $feedID   = shift;

    assert($filename);
    assert($md5);
    assert($feedID);

    Log->notice("Checking for duplicated file for $filename, $md5");
    my $file = BookPub::DB::Item::POSFile->DetectDuplicate( pos_feed_id => $feedID, file_name => $filename, file_md5sum => $md5 );

    if ($file) {

        # Filename mismatch
        if ( $filename ne $file->orig_file_name ) {
            die( "ERROR: Duplicate file detected. File name mismatch: '" . $file->orig_file_name . "' ne '$filename' Aborting.\n" );
        }

        elsif ( $md5 ne $file->file_md5sum ) {
            die("ERROR: Duplicate file detected. md5 mismatch. Aborting!\n");
        }

        # MD5 Mismatch
        else {
            Log->notice(" -- File already downloaded.  Ignoring");
        }

        return 1;
    }

    return;
}

sub _getUploadDir {
    my $client_id = shift;
    my $feed_id   = shift;

    my $feed        = new BookPub::POS::Sale::Feed( posFeedID => $feed_id );
    my $today       = new Common::Date->now();
    my $client      = Common::Client->new( clientID => $client_id );
    my $client_name = $client->ClientNameClean();

    my $uploadDir = sprintf( "%s/%s/%03d/%04d%02d", BaseDirectory(), $client_name, $feed->ServiceID, $today->year, $today->month );
    if ( !-e $uploadDir ) {
        `mkdir $uploadDir`;
    }

    Log->info("   -- Upload dir: $uploadDir");
    system("mkdir -p $uploadDir 2>/dev/null");
    return $uploadDir;
}

sub _getDestFilename {
    my $originalFilename = shift;
    $originalFilename =~ /\.(\w+)$/;
    my $ext = $1 ? ".$1" : "";

    my $timestamp = _getTimeStamp();

    Log->info("   -- Destination File: $timestamp$ext");
    return $timestamp . $ext;
}

sub _createFileRecord {
    my %args = @_;

    Log->info("   -- Create POSFile record");

    my $file;

    if ( $args{pos_file_id} ) {
        $file = BookPub::DB::Item::POSFile->Lookup( pos_file_id => $args{pos_file_id} );
        unless ($file) {
            Log->error("POS File ID: $args{pos_file_id} not found");
            return;
        }

        foreach my $col ( keys %args ) {
            $file->$col( $args{$col} );
        }

        $file->file_status(BookPub::DB::Item::POSFile::STATUS_NEW);
    } else {
        $file = BookPub::DB::Item::POSFile->Create(%args);
    }

    $file->save();

    if ( $file->pos_file_id ) {
        return $file->pos_file_id;
    } else {
        Log->error("Failed to create POS File record");
        return;
    }
}

sub ParseFull {
    my $self = shift;
    my %args = @_;

    $self->_init();

    my $file_obj = $args{file};
    assert($file_obj);

    my $path_to_import_file = $args{file_path} || $file_obj->FileDir . "/" . $file_obj->FileName;
    $self->{path} = $path_to_import_file;

    if ( !-e $path_to_import_file ) {
        $self->errstr("File $path_to_import_file does not exist");
        return undef;
    }

    Log->notice("ParseFull: reading file: $path_to_import_file");
    my $aref_lines_array;
    my $sheets_aref     = [];
    my $sheetnames_aref = [];

    my $fileName = lc( $file_obj->FileName );
    my $fileExtension;
    if ( $fileName =~ m/(\.[a-z]+)$/ ) {
        $fileExtension = $1;
    }

    my $fileType;
    my $utf16File = new Common::File::UTF16($path_to_import_file);
    my $utf8File  = new Common::File::UTF8($path_to_import_file);

    # The only type of binary file that we can really handle is an excel spreadsheet,
    # so we're excluding .csv files here.
    # One day we hope to have a more robust solution to all this file handling stuff.
    if ( _looks_like_excel($path_to_import_file) ) {
        $fileType = "excel";
    } elsif ( $utf8File->isValid() ) {
        $fileType = "utf8";
    } elsif ( $utf16File->isValid() ) {
        $fileType = "utf16";
    }

    # .cvs files should be treated like text files and ignore all PDF files
    elsif ( ( -T $path_to_import_file && $fileExtension ne '.pdf' ) || $fileExtension eq '.csv' ) {
        $fileType = "text";
    } else {
        $self->errstr('File not identified');
        return undef;
    }

    if ( $fileType eq 'excel' ) {
        Log->notice("ParseFull: reading excel file");
        my $sheet_data = $self->_read_excel_file() || return undef;

        $sheets_aref     = $sheet_data->[0];
        $sheetnames_aref = $sheet_data->[1];

        unless ( ref( $sheets_aref->[0] ) eq 'ARRAY' ) {
            $self->errstr('Failed to read excel file');
            return undef;
        }

        # for backwards compatibility
        $aref_lines_array = $sheets_aref->[0];
    } elsif ( $fileType eq 'text' ) {
        Log->notice("ParseFull: reading text file");
        $aref_lines_array = $self->_read_ascii_file();
        unless ( ref( $aref_lines_array->[0] ) eq 'ARRAY' ) {
            $self->errstr('Failed to read text file');
            return undef;
        }

        # back-populate the single sheet
        $sheets_aref->[0] = $aref_lines_array;
    } elsif ( $fileType eq 'utf16' ) {
        print STDERR "ParseFull: reading utf16 text file\n";
        $aref_lines_array = $self->_read_utf16_file( file => $utf16File );
        unless ( ref( $aref_lines_array->[0] ) eq 'ARRAY' ) {
            $self->errstr('Failed to read utf16 text file') unless ( $self->errstr() && '' ne $self->errstr() );
            return undef;
        }

        # back-populate the single sheet
        $sheets_aref->[0] = $aref_lines_array;
    } elsif ( $fileType eq 'utf8' ) {
        print STDERR "ParseFull: reading utf8 text file\n";
        $aref_lines_array = $self->_read_utf8_file( file => $utf8File );
        unless ( ref( $aref_lines_array->[0] ) eq 'ARRAY' ) {
            $self->errstr('Failed to read utf8 text file') unless ( $self->errstr() && '' ne $self->errstr() );
            return undef;
        }

        # back-populate the single sheet
        $sheets_aref->[0] = $aref_lines_array;
    }

    Log->notice("ParseFull: determining service/version");
    if ( $args{service_id} && $args{version} ) {
        Log->notice("ParseFull: forcing to service $args{service_id} : $args{version}");
        $self->{service_id}  = $args{service_id};
        $self->{version_num} = $args{version};
    } elsif ( $self->PreParse( $sheets_aref, $file_obj->OrigFileName ) ) {
        Log->notice("ParseFull: PreParse determined file service is $self->{service_id}, version $self->{version_num}");
    } else {
        $self->errstr('File not identified');
        return undef;
    }

    BookPub::Tracker::Service::AddService( service_id => $self->{service_id} );

    $file_obj->ServiceID( $self->{service_id} );
    $file_obj->VersionNum( $self->{version_num} );
    $file_obj->TypeID( $self->{type_id} );

    $file_obj->save();

    Log->notice("calling importer...");

    my $importer = BookPub::POS::Import::Factory::GetImporter( service_id => $file_obj->ServiceID, version_num => $file_obj->VersionNum );

    # if we parsed an excel file then
    # we will have an arrayref or arrayrefs (ie. sheets)
    # so pass those as a 'sheets' param in leui of a 'lines' param

    my $result = $importer->Import(
        client_id   => Common::RSApp::GetClientID(),
        file        => $file_obj,
        lines       => $aref_lines_array,
        sheets      => $sheets_aref,
        sheet_names => $sheetnames_aref,
    );

    $self->errstr( $importer->errstr() );
    return $result;
}

sub PreParse {
    my $self      = shift;
    my $sheets    = shift;
    my $file_name = shift;

    $self->_init();

    my $rules = $self->_file_match_rules();

    my $service_id_found  = 0;
    my $version_num_found = 0;
  RULE:
    foreach my $rule (@$rules) {
        my $service_id  = $rule->{service};
        my $version_num = $rule->{version};
        Log->debug("======== SERVICE: $service_id, VERSION: $version_num ===========================");

        if ( $rule->{file_name} ) {
            my $fname_match = $rule->{file_name};
            if ( $file_name =~ m/$fname_match/i ) {
                $self->{service_id}  = $service_id;
                $self->{version_num} = $version_num;
                return $service_id;
            }
            next RULE;
        }

        # default to the first sheet
        my $data_sheet_index = $rule->{sheet} || 0;
        my $i = 0;
      SHEET:
        foreach my $sheet (@$sheets) {
            if ( ref($sheet) ne 'ARRAY' or ( $data_sheet_index ne 'any' and $data_sheet_index != $i ) ) {
                $i++;
                next SHEET;
            }

            my $data = $rule->{lines};

            if ( $rule->{match_on_any_row} ) {

                # look for header match on any of the first 80 rows
                my $limit = scalar @$sheet;
                $limit = 80 if ( $limit > 80 );

              OFFSET:
                for ( my $offset = 0 ; $offset < $limit ; $offset++ ) {
                    for ( my $row = 0 ; $row < scalar @$data ; $row++ ) {
                        for ( my $col = 0 ; $col < scalar @{ $data->[$row] } ; $col++ ) {
                            Log->debug( "HEADER: row(" . ( $row + $offset ) . ") col($col) = '" . $sheet->[ $row + $offset ][$col] . "'" );
                            if ( defined $data->[$row][$col] ) {
                                Log->debug("HEADER: ($sheet->[$row+$offset][$col]), DATA: ($data->[$row][$col])");
                                if ( $sheet->[ $row + $offset ][$col] =~ m/$data->[$row][$col]/i ) {
                                    $service_id_found  = $service_id;
                                    $version_num_found = $version_num;
                                } else {
                                    $service_id_found  = 0;
                                    $version_num_found = 0;
                                    next OFFSET;
                                }
                            }
                        }
                    }
                    last OFFSET if ($service_id_found);
                }
            } else {
                for ( my $row = 0 ; $row < scalar @$data ; $row++ ) {
                    for ( my $col = 0 ; $col < scalar @{ $data->[$row] } ; $col++ ) {
                        Log->debug( "HEADER: row($row) col($col) = '" . $sheet->[$row][$col] . "'" );
                        if ( defined $data->[$row][$col] ) {
                            Log->debug("HEADER: ($sheet->[$row][$col]), DATA: ($data->[$row][$col])");
                            if ( $sheet->[$row][$col] =~ m/$data->[$row][$col]/i ) {
                                $service_id_found  = $service_id;
                                $version_num_found = $version_num;
                            } else {
                                $service_id_found  = 0;
                                $version_num_found = 0;
                                $i++;
                                next SHEET;
                            }
                        }
                    }
                }
            }

            if ($service_id_found) {
                $self->{service_id}  = $service_id_found;
                $self->{version_num} = $version_num_found;
                return $service_id_found;
            }
            $i++;
        }
    }

    $self->errstr("Unrecognized file format");
    return undef;
}

# accessor methods

sub GetServiceID {
    my $self = shift;

    return $self->{service_id};
}

sub GetVersionNum {
    my $self = shift;

    return $self->{version_num};
}

sub GetFileType {
    my $self = shift;

    if ( $self->{file_type} eq 'e' ) {
        return File::File::FILETYPE_EXCEL;
    } elsif ( $self->{file_type} eq 't' ) {
        if ( $self->{delimiter} eq "\t" ) {
            return File::File::FILETYPE_TABSEP;
        } elsif ( $self->{delimiter} eq '\|' ) {
            return File::File::FILETYPE_PIPESEP;
        } elsif ( $self->{delimiter} eq ',' ) {
            return File::File::FILETYPE_COMMASEP;
        }
    }

    return undef;
}

sub errstr {
    my $self  = shift;
    my $error = shift;

    if ( defined($error) ) {
        $self->{errstr} = $error;
        print STDERR $self->{errstr} . "\n";
    }
    return $self->{errstr};
}

sub bail {
    my $self = shift;
    my $err  = shift;

    $self->errstr($err) if ( defined $err );

    return undef;
}

# private methods

sub _init {
    my $self = shift;

    $self->{path}        = undef;
    $self->{header}      = undef;
    $self->{service_id}  = undef;
    $self->{version_num} = undef;
    $self->{file_type}   = undef;
    $self->{delimiter}   = undef;
    $self->{errstr}      = undef;
}

sub _getTimeStamp {

    # I get YYYY-MM-DD HH:MM:SS
    my $today = Common::Util::today_and_now();

    # return YYYYMMDDHHMMSS
    $today =~ s/\D//g;

    return $today;
}

sub _read_utf8_file {
    my $self     = shift;
    my %args     = @_;
    my $utf8File = $args{file};
    my $lines    = $utf8File->read();

    $self->{delimiter} = Common::Util::GetDelimiter( join( "\n", @$lines ) );

    if ( $self->{delimiter} ) {
        return $self->_read_text_file( %args, lines => $lines );
    } else {
        $self->errstr('Can not determine delimeter');
        return ();
    }
}

sub _read_utf16_file {
    my $self      = shift;
    my %args      = @_;
    my $utf16File = $args{file};
    my $lines     = $utf16File->read();

    $self->{delimiter} = Common::Util::GetDelimiter( join( "\n", @$lines ) );

    if ( $self->{delimiter} ) {
        return $self->_read_text_file( %args, lines => $lines );
    } else {
        $self->errstr('Can not determine delimeter');
        return ();
    }
}

sub _read_ascii_file {
    my $self = shift;
    my %args = @_;
    my $file = $self->{path};

    unless ( open FILE, $file ) {
        $self->errstr("Can't open file $file: $!");
        return undef;
    }

    $self->{delimiter} = Common::Util::GetDelimiter(*FILE);
    my $lines = Common::Util::ReadMostTextFiles(*FILE);
    close FILE;

    if ( $self->{delimiter} ) {
        return $self->_read_text_file( %args, lines => $lines );
    } else {
        $self->errstr('Can not determine delimeter');
        return ();
    }

}

sub _read_text_file {
    my $self = shift;
    my %args = @_;

    $self->{file_type} = 't';

    my $csv = Text::CSV_XS->new( { binary => 1, escape_char => "\\" } );

    my @header;

    if ( $self->{delimiter} ) {
        my $lines = $args{lines};
        my $limit = $args{limit} || scalar @$lines;

        my $lineNum = 1;

        for ( my $i = 0 ; $i < $limit ; $i++ ) {
            my $line = $lines->[$i];
            my @row;
            if ( $self->{delimiter} eq ',' ) {

                # some files have a single " which confuses the parse method
                my @commaCount = $lines->[$i] =~ m/[^\\]"/g;

                #$lines->[$i] =~ s/"// if (scalar @commaCount == 1);

                # a rather gross thing we have to do for CSV files with spaces around the commas.
                # the big drawback is that it get's rid of spaces within quoted strings.
                # we could deal with the quoted fields, but then i forget the point of using
                # the Text::CSV_XS library...
                $lines->[$i] =~ s/"\s*,\s*"/","/g;

                my $test_quote = $lines->[$i];
                $test_quote =~ s/","//g;
                $test_quote =~ s/^"//;
                $test_quote =~ s/"$//;
                $self->{possible_quote} = 1 if ( $test_quote =~ /\"/ );

                if ( $self->{possible_quote} ) {
                    if ( $lines->[$i] !~ /\"/ ) {
                        $lines->[$i] =~ s/,/\t/g;
                    } else {
                        if ( $lines->[$i] =~ /^\"/ && $lines->[$i] =~ /\"$/ && $lines->[$i] =~ /\",\"/ ) {
                            $lines->[$i] =~ s/^\"//;
                            $lines->[$i] =~ s/\"$//;
                            $lines->[$i] =~ s/\",\"/\t/g;
                            $lines->[$i] =~ s/"/\\"/g;
                        } else {
                            $lines->[$i] =~ s/\'/\\\'/g;
                            my $subSwitch   = 0;
                            my $workingLine = $lines->[$i];
                            my @comaSubs;
                            my $comaSubsCount = 0;
                            while ( $subSwitch < 1 ) {
                                $workingLine =~ m/(\"[^\"]*\")/;
                                $comaSubs[$comaSubsCount] = $1;
                                my $subField = "ddd" . $comaSubsCount . "bbb";
                                $workingLine =~ s/(\"[^\"]*\")/$subField/;
                                $comaSubs[$comaSubsCount] =~ s/^\"//;
                                $comaSubs[$comaSubsCount] =~ s/\"$//;
                                if ( $workingLine !~ /\"[^\"]*\"/ ) {
                                    $workingLine =~ s/,/\t/g;
                                    $subSwitch = 1;
                                }
                                $comaSubsCount++;
                            }
                            for ( my $x = 0 ; $x < @comaSubs ; $x++ ) {
                                my $subField  = "ddd" . $x . "bbb";
                                my $realField = $comaSubs[$x];
                                $workingLine =~ s/$subField/$realField/;
                            }
                            $lines->[$i] = $workingLine;
                        }
                    }

                    #die "$lines->[$i]";
                    if ( $csv->parse( $lines->[$i] ) ) {
                        @row = split( "\t", $lines->[$i] );

                        # Unescape the quotes now the they have been parsed
                        foreach my $d (@row) {
                            $d =~ s/\\"/"/g;
                            $d =~ s/\s+/ /g;
                        }
                    } else {
                        $self->errstr("Double quotes must be escaped at line $lineNum");
                        return undef;
                    }
                } else {
                    if ( $csv->parse( $lines->[$i] ) ) {
                        @row = Common::Util::trimquotes( $self->_cleanup( $csv->fields() ) );
                    } else {
                        $self->errstr( "Double quotes must be escaped at line $lineNum: " . $csv->error_diag() );
                        return undef;
                    }
                }
            } else {
                @row = Common::Util::trimquotes( $self->_cleanup( split( $self->{delimiter}, $lines->[$i] ) ) );
            }

            $lineNum++;

            push @header, \@row;
        }
        if ( $args{clean_quotes} && $self->{possible_quote} ) {
            $self->{possible_quote} = 0;
            $args{clean_quotes} = 0;
        }
    }

    return \@header;
}

sub _read_excel_file {
    my $self  = shift;
    my %args  = @_;
    my $print = $args{print};
    my $isXLSX;

    my $file = $self->{path} || $args{path};
    my $book;

    if ( $file =~ /\.xlsx$/ || lc(`file $self->{path}`) =~ /\bzip\b/ ) {
        $book   = Spreadsheet::XLSX->new($file);
        $isXLSX = 1;
    } else {
        open( IN, "$file" ) || die "Can not read: $file";
        binmode(IN);
        read( IN, my $buf, 2 );

        if ( sprintf( "%02x", ord($buf) ) eq "d0" ) {
            $book = Spreadsheet::ParseExcel::Workbook->Parse($file);
        } else {
            die "Unrecognized Excel Format\n";
        }
    }

    unless ( defined $book && defined $book->{Worksheet} ) {
        $self->errstr("The file could not be parsed as an Excel file");
        return undef;
    }

    $self->{file_type} = 'e';

    my @sheets      = ();
    my @sheet_names = ();
    push @sheets, @{ $book->{Worksheet} };
    for ( my $i = 0 ; $i < $book->{SheetCount} ; $i++ ) {
        push @sheet_names, $book->{Worksheet}->[$i]->{Name};
    }

    my @sheet_lines = ();
    foreach my $sheet (@sheets) {
        my @lines = ();
        for ( my $row_num = 0 ; defined $sheet->{MaxRow} && $row_num <= $sheet->{MaxRow} ; $row_num++ ) {
            my @row;
            for ( my $col_num = 0 ; defined $sheet->{MaxCol} && $col_num <= $sheet->{MaxCol} ; $col_num++ ) {
                my $cell = $sheet->{Cells}[$row_num][$col_num];
                if ($cell) {
                    my $value = $cell->{Val};

                    # clean up ucs2/utf16 (two byte) strings.
                    # right now, the 'Code' attribute of the Cell
                    # is our only indicator for this. There might
                    # be other values besides ucs2 that we haven't seen yet.

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

                    if ( lc( $cell->{Type} ) eq 'date' ) {
                        my $held_value = $value;
                        $value = Spreadsheet::ParseExcel::Utility::ExcelFmt( "yyyy-mm-dd", $value );
                        if ( $book->{Flg1904} )    # need to add 4 yrs
                        {
                            my ( $y, $m, $d ) = Add_Delta_Days( 1904, 1, 1, $held_value );
                            $value = sprintf( "%04d-%02d-%02d", $y, $m, $d );
                        }
                    } else {
                        $value = Common::Util::trimquotes( $self->_cleanup($value) );    # raw value without formatting
                    }

                    if ($isXLSX) {
                        $value =~ s/&amp;/&/;
                        $value =~ s/&lt;/</;
                        $value =~ s/&gt;/>/;
                    }

                    push @row, $value;
                } else {
                    push @row, '';                                                       # put an empty string in this cell
                }
            }
            if ($print) {
                print join( "\t", @row ), "\n";
            } else {
                push @lines, \@row;
            }
        }
        push @sheet_lines, \@lines;
    }

    if ($print) {
        return 1;
    } else {
        return [ \@sheet_lines, \@sheet_names ];
    }
}

sub _looks_like_excel {
    my $filePath = shift;
    my ($ext)    = $filePath =~ /\.(\w+)$/;
    my $fileDesc = lc(`file $filePath`);

    return -B $filePath
      && ( $ext =~ /xls/
        || $fileDesc =~ /\boffice\b/
        || $fileDesc =~ /\bzip\b/ ) ? 1 : 0;
}

sub _cleanup {
    my $self = shift;
    my @data = @_;

    for (@data) {
        s/[\n\r\t]+/ /g;    # strip tabs and linebreaks
        s/^\s+//;
        s/\s+$//;
    }

    return wantarray ? @data : $data[0];
}

sub _file_match_rules {
    my $self = shift;

    # array(ref) of hashrefs containing service, version and rule lines
    # ex: $rule = $rules->[i]; service_id->{$version_num}

    # rule order is important!
    # general rules of thumb:
    # 1) rule order is important!
    # 2) more specific rules should be placed before less specific rules
    # 3) each rule within the same service should typically have a unique version number
    # 4) version numbers must stay the same forever (unless you *know* a version has NEVER been used)
    # 5) it's okay for rule versions to be out of sequence in order to satisfy rules 1 and 2
    #    (i.e. a new rule n+1 for a service might be placed before older (and lower numbered) versions
    #    because it is more specific)
    # 6) so far, there hasn't been any overlap between services, but certainly possible, when adding
    #    a new service look for similarity and place accordingly (again, service_id/version_num order
    #    isn't important as long as they're unique)
    # 7) use sheet -> 'any' when the sheet order varies
    # 8) try to place any rules with match_on_any_row => 1 at/near the end to minimize unecessary searching
    # 9) only use the file_name option (instead of specifying lines) if multiple services have the exact same format

    my $rules = [

        # Apple
        {
            service => BookPub::Tracker::Service::APPLE,
            version => 1,
            lines   => [ [
                    'Title',
                    'Author',
                    'Units',
                    'Publisher Proceeds',
                    'Currency Of Proceeds',
                    'Customer Price',
                    'Customer Currency',
                    'Country Code',
                    'Product Type Identifier',
                    'Pre-Order',
                    'Promo Code',
                    'ISBN',
                    'Apple Identifier',
                    'Vendor Identifier',
                    'Vendor Offer Code',
                    'Publisher',
                    'Imprint',
                    'Download Date \(PST\)',
                    'Order Id',
                    'Postal Code',
                    'Customer Identifier',
                    'Report Date \(Local\)',
                    'Sales\/Return'
                ],
            ],
        },

        # Barnes
        {
            service => BookPub::Tracker::Service::BARNES_NOBLE,
            version => 1,
            lines   => [ [
                    'Date Range', 'Date Sold',  'EAN',          'Title', 'Author\(s\)', 'Publisher',
                    'Format',     'List Price', 'Retail Price', 'Units Sold'
                ],
            ],
        },

        # Amazon
        {
            service => BookPub::Tracker::Service::AMAZON,
            version => 1,
            lines   => [ [
                    'vendor code',         'kindle asin', 'eisbn',      'title name',
                    'kindle_release_date', 'author_name', 'list price', 'agency price',
                    'activity_day',        'delivered_units'
                ],
            ],
        },
    ];
    return $rules;
}

1;