#!/usr/bin/perl
#

use strict;

use Getopt::Std;

my $mangler = Mangler->new();
$mangler->start();

package Mangler;
use lib '/app/tools/common/lib';
use lib '/app/tools/bookpub/lib';
use Common::RSApp;
use Common::Script;
use Common::Client;
use BookPub::DB::Item::Book;
use BookPub::DB::Item::BookProduct;
use BookPub::DB::Item::BookSubject;
use BookPub::DB::Item::BookContributor;
use BookPub::DB::Item::BookProductContributor;
use Data::Dumper;

use base 'Common::Script';

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

    my $options = $self->SUPER::_get_options();

    $options->{client_id}{required} = 1;
    return $options;
}

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

    print "CLIENT: " . Common::RSApp::GetClientID() . "\n";

    # So, we're going to start with a 'fresh', unaltered database.
    # We're going to add in the new columns.
    # Then move the data from Book to BookProduct.
    # Then DROP the old columns from Book.

    $self->_createNewTables();
    $self->_alterBookProduct();
    $self->_migrateData();
    $self->_consolidateBooks();
    $self->_alterBook();
}

sub _createNewTables {
    my ($self) = @_;
    my $dbo = Common::RSApp::GetClientDB();

    my $sql = <<'END';
CREATE TABLE `archive_book_product_contributor` (
`archive_action` enum('updt','delt') default NULL,
`archive_book_product_contributor_id` int(10) unsigned NOT NULL auto_increment,
`book_product_contributor_id` int(10) unsigned NOT NULL,
`catalog_import_id` int(10) unsigned default NULL,
`contributor_id` int(10) unsigned NOT NULL default '0',
`contributor_role_id` int(10) unsigned NOT NULL default '0',
`created_by` int(10) unsigned NOT NULL default '0',
`date_created` timestamp NOT NULL default '0000-00-00 00:00:00',
`date_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`modified_by` int(10) unsigned NOT NULL default '0',
`product_id` int(10) unsigned NOT NULL default '0',
`sequence` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`archive_book_product_contributor_id`),
KEY `book_product_contributor_id` (`book_product_contributor_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
END

    $dbo->DoCmd($sql);

    $sql = <<'END2';
CREATE TABLE `book_product_contributor` (
`book_product_contributor_id` int(10) unsigned NOT NULL auto_increment,
`catalog_import_id` int(10) unsigned default NULL,
`contributor_id` int(10) unsigned NOT NULL default '0',
`contributor_role_id` int(10) unsigned NOT NULL default '0',
`created_by` int(10) unsigned NOT NULL default '0',
`date_created` timestamp NOT NULL default '0000-00-00 00:00:00',
`date_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`modified_by` int(10) unsigned NOT NULL default '0',
`product_id` int(10) unsigned NOT NULL default '0',
`sequence` int(10) unsigned NOT NULL default '0',
KEY `contributor_id` (`contributor_id`),
PRIMARY KEY (`book_product_contributor_id`),
KEY `product_id` (`product_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
END2
    $dbo->DoCmd($sql);

    $sql = <<'END3';
CREATE TABLE `catalog_import_book_product_contributor` (
`catalog_import_book_product_contributor_id` int(10) unsigned NOT NULL auto_increment,
`catalog_import_contributor_id` int(10) unsigned NOT NULL default '0',
`catalog_import_id` int(10) unsigned NOT NULL,
`catalog_import_product_id` int(10) unsigned NOT NULL,
`contributor_role_id` int(10) unsigned NOT NULL default '0',
`created_by` int(10) unsigned NOT NULL default '0',
`date_created` timestamp NOT NULL default '0000-00-00 00:00:00',
`date_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`import_status` char(4) default NULL,
`imported` int(1) default '0',
`modified_by` int(10) unsigned NOT NULL default '0',
`original_book_product_contributor_id` int(10) unsigned NOT NULL default '0',
`original_contributor_id` int(10) unsigned NOT NULL default '0',
`original_product_id` int(10) unsigned default NULL,
`sequence` int(10) unsigned NOT NULL default '0',
KEY `catalog_import_id` (`catalog_import_id`),
KEY `original_product_id` (`original_product_id`),
KEY `catalog_import_product_id` (`catalog_import_product_id`),
KEY `catalog_import_contributor_id` (`catalog_import_contributor_id`),
PRIMARY KEY (`catalog_import_book_product_contributor_id`),
KEY `original_book_product_contributor_id` (`original_book_product_contributor_id`),
KEY `original_contributor_id` (`original_contributor_id`),
KEY `contributor_role_id` (`contributor_role_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
END3
    $dbo->DoCmd($sql);
}

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

    # Add the new columns to book_product and friends.
    #
    my $dbo = Common::RSApp::GetClientDB();

    $dbo->DoCmd('ALTER TABLE book_product ADD COLUMN language_code char(2) default NULL AFTER book_format_id');
    $dbo->DoCmd('ALTER TABLE book_product ADD COLUMN num_pages int(10) default NULL AFTER language_code');
    $dbo->DoCmd('ALTER TABLE book_product ADD COLUMN edition int(4) default NULL AFTER num_pages');
    $dbo->DoCmd('ALTER TABLE book_product ADD COLUMN imprint_id int(10) unsigned default NULL AFTER edition');
    $dbo->DoCmd('ALTER TABLE book_product ADD KEY `imprint_id` (`imprint_id`)');

    $dbo->DoCmd('ALTER TABLE archive_book_product ADD COLUMN language_code char(2) default NULL AFTER book_format_id');
    $dbo->DoCmd('ALTER TABLE archive_book_product ADD COLUMN num_pages int(10) default NULL AFTER language_code');
    $dbo->DoCmd('ALTER TABLE archive_book_product ADD COLUMN edition int(4) default NULL AFTER num_pages');
    $dbo->DoCmd('ALTER TABLE archive_book_product ADD COLUMN imprint_id int(10) unsigned default NULL AFTER edition');

    $dbo->DoCmd('ALTER TABLE catalog_import_book_product ADD COLUMN language_code char(2) default NULL AFTER book_format_id');
    $dbo->DoCmd('ALTER TABLE catalog_import_book_product ADD COLUMN num_pages int(10) default NULL AFTER language_code');
    $dbo->DoCmd('ALTER TABLE catalog_import_book_product ADD COLUMN edition int(4) default NULL AFTER num_pages');
    $dbo->DoCmd('ALTER TABLE catalog_import_book_product ADD COLUMN catalog_import_imprint_id int(10) unsigned default NULL AFTER edition');
    $dbo->DoCmd(
'ALTER TABLE catalog_import_book_product ADD COLUMN original_imprint_id int(10) unsigned default NULL AFTER catalog_import_imprint_id'
    );
    $dbo->DoCmd('ALTER TABLE catalog_import_book_product ADD KEY `catalog_import_imprint_id` (`catalog_import_imprint_id`)');
    $dbo->DoCmd('ALTER TABLE catalog_import_book_product ADD KEY `original_imprint_id` (`original_imprint_id`)');
}

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

    # !!! What this is NOT doing currently is consolidate similar books.
    # !!! It might be necessary to do that as a second pass.

    my $allBooks = BookPub::DB::Item::Book->GetAll();
    while ( my $book = $allBooks->next() ) {

        # keep track of the non-author book contributors.
        # We'll translate these into book_product_contributors, then remove them from the book.
        #
        my $bookID = $book->book_id();

        my @productContributors;
        my $allBookContributors = BookPub::DB::Item::BookContributor->GetAllByBookID($bookID);
        while ( my $c = $allBookContributors->next() ) {
            if ( BookPub::DB::Item::ContributorRole::kRoleIDAuthor != $c->contributor_role_id() ) {
                push @productContributors, $c;
            }
        }

        # Copy data from book to all book products.
        #
        my $allBookProducts = BookPub::DB::Item::BookProduct->GetAllByBookID($bookID);
        while ( my $bp = $allBookProducts->next() ) {
            $bp->language_code( $book->language_code() );
            $bp->num_pages( $book->num_pages() );
            $bp->edition( $book->edition() );
            $bp->imprint_id( $book->imprint_id() );
            $bp->save();

            foreach my $contrib (@productContributors) {

                # These aren't authors, so I don't know that I care much about their sequence value.
                # That will get reset anyway during the next import.
                #
                my $newProductContrib = BookPub::DB::Item::BookProductContributor->Create(
                    contributor_id      => $contrib->contributor_id(),
                    contributor_role_id => $contrib->contributor_role_id(),
                    sequence            => $contrib->sequence(),
                    product_id          => $bp->product_id(),
                );
                $newProductContrib->save();
            }
        }

        # Now remove these non-author contributors from this book.
        #
        foreach my $contrib (@productContributors) {
            $contrib->delete();
        }
    }
}

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

    my $allBooks = BookPub::DB::Item::Book->GetAll();
    while ( my $book = $allBooks->next() ) {
        my $bookID = $book->book_id();
        next if $self->{_consolidated}{$bookID};

        my $authors = BookPub::DB::Item::BookContributor->GetAuthorNamesByBookID($bookID);

        # Find all books that are the same.  That means:
        # - The same title and subtitle
        # - The same authors.
        #
        my @sameBooks;

        my $similarBooks = BookPub::DB::Item::Book->GetAllByTitleAndSubtitle( $book->title(), $book->subtitle() );
        while ( my $similarBook = $similarBooks->next() ) {
            next if ( $similarBook->book_id() == $bookID );

            my $similarAuthors = BookPub::DB::Item::BookContributor->GetAuthorNamesByBookID( $similarBook->book_id() );
            if ( $authors eq $similarAuthors ) {
                push @sameBooks, $similarBook->book_id();
            }
        }

        if ( scalar @sameBooks ) {
            foreach my $sameBookID (@sameBooks) {

                # Set the book products for this book to use the original book id.
                #
                my $bookProducts = BookPub::DB::Item::BookProduct->GetAllByBookID($sameBookID);
                while ( my $bookProduct = $bookProducts->next() ) {
                    $bookProduct->book_id($bookID);
                    $bookProduct->save();
                }

                # Delete the duplicate book stuff.
                #
                BookPub::DB::Item::BookSubject->DeleteAllByBookID($sameBookID);
                BookPub::DB::Item::BookContributor->DeleteAllByBookID($sameBookID);
                my $sameBook = BookPub::DB::Item::Book->Lookup( book_id => $sameBookID );
                $sameBook->delete();

                $self->{_consolidated}{$sameBookID} = 1;
            }
        }

        $self->{_consolidated}{$bookID} = 1;
    }
}

sub _alterBook {
    my ($self) = @_;
    my $dbo = Common::RSApp::GetClientDB();

    $dbo->DoCmd('ALTER TABLE book DROP COLUMN language_code');
    $dbo->DoCmd('ALTER TABLE book DROP COLUMN num_pages');
    $dbo->DoCmd('ALTER TABLE book DROP COLUMN edition');
    $dbo->DoCmd('ALTER TABLE book DROP COLUMN imprint_id');

    $dbo->DoCmd('ALTER TABLE archive_book DROP COLUMN language_code');
    $dbo->DoCmd('ALTER TABLE archive_book DROP COLUMN num_pages');
    $dbo->DoCmd('ALTER TABLE archive_book DROP COLUMN edition');
    $dbo->DoCmd('ALTER TABLE archive_book DROP COLUMN imprint_id');

    $dbo->DoCmd('ALTER TABLE catalog_import_book DROP COLUMN language_code');
    $dbo->DoCmd('ALTER TABLE catalog_import_book DROP COLUMN num_pages');
    $dbo->DoCmd('ALTER TABLE catalog_import_book DROP COLUMN edition');
    $dbo->DoCmd('ALTER TABLE catalog_import_book DROP COLUMN catalog_import_imprint_id');
    $dbo->DoCmd('ALTER TABLE catalog_import_book DROP COLUMN original_imprint_id');
}

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

    # limit to bookpub clients

    return Common::Client::Current()->isClientType(Common::Client::kBookPublishing);
}

1;