#!/usr/bin/perl
use strict;

use Data::Dumper;
use lib '/app/tools/common/lib';
use lib '/app/tools/rps/lib';

use Getopt::Std;
use POSIX qw(ceil);
use Common::UTF8;

use Common::RSApp;
use Common::RSDB;

use constant kAscii  => 1;
use constant kLatin1 => 2;
use constant kUTF8   => 3;

my %gTableCharset;

my $gVerbosityLevel = 1;
my $gCommitChanges  = 0;

binmode( STDOUT, ":utf8" );

my %options;
_parseCommandLine( \%options );
my $clientID = $options{clientID};

#foreach my $clientID (_getLocalClientIDs())
#{
eval { _checkDB($clientID); };
print $@ . "\n" if $@;
print "\n\n";

#}

sub _parseCommandLine {
    my ($settings) = @_;

    my %opt;
    getopts( 'c:V:g', \%opt );

    if ( !$opt{c} ) {
        _usage();
        exit(1);
    }
    $settings->{clientID} = $opt{c};

    if ( defined $opt{V} ) {
        $gVerbosityLevel = $opt{V};
    }

    if ( defined $opt{g} ) {
        $gCommitChanges = 1;
    }
}

sub _usage {
    print "\nusage: $0 -c <client_id> [-g]\n";
    print "\n";
    print "Arguments:\n";
    print "\t-c <client_id>\t\tThe client_id of the client to process\n";
    print "\t-g \t\t\tThe 'go flag' - If this is not specified, no changes will be committed\n";
}

sub _checkDB {
    my ($clientID) = @_;

    _report("");
    _report("------------");
    _report( "client $clientID " . Common::RSDB::ClientIDToDBName($clientID) );

    # TURN OFF the 'auto-utf8' mechanism.
    #
    my $app = Common::RSApp->new( clientID => $clientID, useRSDBI => 0 );

    #    my $app = Common::RSApp->new(clientID => $clientID);

    my $dbo = Common::RSApp::GetClientDB();

    # Alter artist and composer to remove the unique constraint on 'name'
    #
    if ($gCommitChanges) {
        eval { $dbo->DoCmd("ALTER TABLE artist DROP KEY `artist_name`"); };

        eval { $dbo->DoCmd("ALTER TABLE composer DROP KEY `name`"); };
    }

    #  look at every table.
    #
    my @allTables = _getAllTables($dbo);

    # Get the encoding of each table.
    #
    my $sth = $dbo->DoCmd('show table status');
    while ( my $hr = $sth->fetchrow_hashref() ) {
        my $tableName = $hr->{Name};
        my $collation = $hr->{Collation};
        my $encoding  = kLatin1;
        if ( $collation =~ /utf8/ ) {
            $encoding = kUTF8;
        }
        $gTableCharset{$tableName} = $encoding;
    }

    foreach my $table (@allTables) {
        _convertTable( $dbo, $table, $clientID );
    }
}

sub _convertTable {
    my ( $dbo, $tableName, $clientID ) = @_;

    # What we'll do is scan the table, and collect a sequence
    # of alter statements.
    #
    # Then we'll convert the data in place back to latin1 if necessary.
    #
    # Finally, we'll execute those alters.
    #
    my @alters;

    my $hasPrimaryKey = 0;
    my @keyFields;

    _report( "scanning $tableName", 2 );

    # First I'll take a look at all the indexes.
    # We'll need to know if any of the TEXT fields are FULLTEXT INDEX.
    #
    my %indexData;
    my $sth = $dbo->DoCmd("show index from $tableName");
    my $ar  = $sth->fetchall_arrayref();
    if ($ar) {
        foreach my $indexDesc (@$ar) {
            $indexData{ $indexDesc->[4] } = {
                keyName => $indexDesc->[2],
                type    => $indexDesc->[10],
            };
        }
    }

    # Alter the table encoding if necessary
    #
    if ( kUTF8 != $gTableCharset{$tableName} ) {
        push @alters, "ALTER TABLE $tableName CHARACTER SET utf8";
    }

    # get the schema, determine which are text fields
    #
    $sth = $dbo->DoCmd("desc $tableName");
    $ar  = $sth->fetchall_arrayref();

    my @textFields;
    foreach my $fieldDesc (@$ar) {
        if ( $fieldDesc->[1] =~ /varchar\((.*)\)/ ) {
            my $num = $1;
            push @textFields, $fieldDesc->[0];

            push @alters, "ALTER TABLE $tableName MODIFY " . $fieldDesc->[0] . " VARCHAR($num) CHARACTER SET utf8"
              if ( kUTF8 != $gTableCharset{$tableName} );
        } elsif ( $fieldDesc->[1] =~ /text/ ) {
            push @textFields, $fieldDesc->[0];

            push @alters, "ALTER TABLE $tableName MODIFY " . $fieldDesc->[0] . " TEXT CHARACTER SET utf8"
              if ( kUTF8 != $gTableCharset{$tableName} );
        }

        if ( 'PRI' eq $fieldDesc->[3] ) {
            $hasPrimaryKey = 1;
            $#keyFields    = 0;
            $keyFields[0]  = $fieldDesc->[0];
        }

        if ( !$hasPrimaryKey && 'MUL' eq $fieldDesc->[3] ) {
            push @keyFields, $fieldDesc->[0];
        }
    }

    #    return unless scalar @textFields;

    # Now we scan for high-order characters.
    #
    $sth = $dbo->DoCmd("SELECT * FROM $tableName");
    while ( my $hr = $sth->fetchrow_hashref() ) {
        foreach my $fieldToCheck (@textFields) {
            my $textToCheck = $hr->{$fieldToCheck};
            if ( defined $textToCheck ) {
                my $textChanged = 0;
                my $encoding    = describeString($textToCheck);
                if ( kUTF8 == $encoding && $gTableCharset{$tableName} == kLatin1 ) {

                    # Attempt to convert back to latin1.
                    # !!! This could fail !!!
                    #
                    _report( "   ...downgrading field to latin1", 3 );
                    if ( !utf8::downgrade( $textToCheck, 1 ) ) {
                        _report( "ERROR!!! Unable to convert string back to latin1 for this record: " . Dumper($hr), 3 );
                        next;
                    }
                    $textChanged = 1;
                } elsif ( kLatin1 == $encoding && $gTableCharset{$tableName} == kUTF8 ) {

                    # This always works...
                    #
                    _report( "   ...upgrading to utf8", 3 );
                    utf8::upgrade($textToCheck);
                    $textChanged = 1;
                }

                if ($textChanged) {

                    # Update the record with the new data.
                    #
                    my @keys;
                    foreach my $keyField (@keyFields) {
                        push @keys, "$keyField=" . $dbo->DBQuote( $hr->{$keyField} );
                    }

                    my $sql = "UPDATE $tableName SET $fieldToCheck=" . $dbo->DBQuote($textToCheck) . " WHERE " . join( ' AND ', @keys );
                    _report( "SQL: $sql", 3 );
                    $dbo->DoCmd($sql) if $gCommitChanges;
                }
            }
        }
    }

    # now apply any alters, if necessary
    #
    foreach my $alter (@alters) {
        _report( "   APPLYING THIS ALTER: $alter", 2 );
        $dbo->DoCmd($alter) if $gCommitChanges;
    }
}

sub _getAllTables {
    my ($dbo) = @_;

    my @allTables;
    my $sth = $dbo->DoCmd('show tables');
    while ( my $ar = $sth->fetchrow_arrayref() ) {
        push @allTables, $ar->[0];
    }

    return @allTables;
}

sub _getLocalClientIDs {

    #return 60; # RSDEMO
    #    return 68; # SIXDEGREES
    #    return 23; # COMPASS

    # We have a nifty little script that will give us a list of client databases.
    #
    my $clientListString = `/app/tools/rps/bin/db/list_client_dbs.pl`;
    chomp $clientListString;

    # Find the client id that matches each database.
    #
    my @ids;
    my @dbNames = split( / /, $clientListString );
    foreach my $dbName (@dbNames) {
        my $id = Common::RSDB::DBNameToClientID($dbName);
        push @ids, $id;
    }
    return @ids;
}

# There are 3 different cases that I want to capture:
# 1 - Everything in the string is low-order ascii.
# 2 - There are high-order latin1 characters.
# 3 - There are high-order UTF8 characters.
#
sub describeString {
    my ($string) = @_;

    my @bytes = unpack( "C*", $string );

    my $numBytes = scalar @bytes;
    for ( my $i = 0 ; $i < $numBytes ; $i++ ) {
        next if ( $bytes[$i] < 128 );

        # Check the high-order bits, to see how many multi-bytes to expect.
        #
        # We're just going to check the first (possibly) wide character we see.

        if ( 0xf0 == ( $bytes[$i] & 0xf0 ) ) {

            # Possibly 4-byte
            #
            return kUTF8 if _checkUTF8Bytes( \@bytes, 4, $i );
        } elsif ( 0xe0 == ( $bytes[$i] & 0xe0 ) ) {

            # Possibly 3-byte
            #
            return kUTF8 if _checkUTF8Bytes( \@bytes, 3, $i );
        } elsif ( 0xc0 == ( $bytes[$i] & 0xc0 ) ) {

            # Possibly 2-byte
            #
            return kUTF8 if _checkUTF8Bytes( \@bytes, 2, $i );
        } else {

            # It ain't utf8
            #
            return kLatin1;
        }
    }

    return kAscii;
}

sub _checkUTF8Bytes {
    my ( $bytes, $order, $i ) = @_;

    # Do we have enough bytes left in the string?
    #
    return 0 if ( $i + $order >= ( scalar @$bytes + 1 ) );

    # All subsequent bytes should start with 0b10xxxxxx
    #
    for ( my $x = 1 ; $x < $order ; $x++ ) {
        my $j    = $i + $x;
        my $byte = $bytes->[$j];

        if ( 0x80 != ( $byte & 0xc0 ) ) {
            return 0;
        }
    }

    return 1;
}

sub _report {
    my ( $msg, $level ) = @_;

    $level = 1 unless $level;
    if ( $level <= $gVerbosityLevel ) {
        print localtime() . " $msg\n";
    }
}