#!/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";
    }
}