#!/usr/bin/perl -w

#See http://rachaelandtom.info/merge-amarok-collections for details/updates

use DBI;
use strict;

my %idToStats=();
my %urlsToId=();

#get all track from import.sql

open(INFO, $ARGV[0]) or die "$ARGV[0]: $! "; # Open the file given in argument
my @lines = <INFO>;
close(INFO);

foreach (@lines){
  my $line=$_;
  if ($line=~/INSERT INTO `statistics`/){
    $line=~/VALUES \([0-9]+,([0-9]+),[0-9]+,[0-9]+,([.0-9]+),([0-9]+)/;
    my $id=$1;
    my $stats="$2:$3";	#score colon rating
#print "$line $id is $stats\n";
    $idToStats{$id}=$stats;
  }
  if ($line=~/INSERT INTO `urls`/){
    $line=~/VALUES \(([0-9]+),[-0-9]+,'(.*)',[0-9]+/;
#print $line;    
    my $id=$1;
    my $rpath=$2;
    $rpath=~s/\\'/'/g;#unescape the quotes
#print "\n$id is $rpath\n";    
    $urlsToId{$rpath}=$id;
  }
}

my $dbh = DBI->connect("DBI:mysql:database=amarok;host=127.0.0.1", 
		      '', '',
                      { RaiseError => 1 }) or die "Can't connect to Amarok database: $!";
my $findSth = $dbh->prepare("SELECT count(*) FROM urls WHERE rpath=?");
my $findUpdateSth = $dbh->prepare("SELECT count(*) FROM statistics WHERE url = (select id from urls where rpath=?)");

my $updateSth = $dbh->prepare("UPDATE statistics SET score=?, rating=? WHERE url = (select id from urls where rpath=?)");

my $findIdSth = $dbh->prepare("SELECT id,rpath FROM urls WHERE rpath=?");
my $insertSth = $dbh->prepare("INSERT into statistics SET url=?, createdate=?, accessdate=?, score=?,rating=?, playcount=0,deleted=0");

my $toImport=0;
my $found=0;
my $updated=0;
my $inserted=0;
my $failed=0;

#for each tracks
for my $rpath ( keys %urlsToId ) {
  $toImport++;
  #if find filename in local DB (by url) 
  $findSth->execute( $rpath );
  my @row = $findSth->fetchrow_array;
  my $stats=$idToStats{ $urlsToId{$rpath} };
  #print "$rpath ";
  #print "urls to id ". $urlsToId{$rpath};
  #print "all ".$idToStats{ $urlsToId{$rpath} }."\n";
  
  if ($row[0] > 0 && $stats ){#just because we have a track to import, might not have been played on source we import from, so no stats
    my ($s,$r)=split /:/ , $stats ;
  
    $found++;
    $findUpdateSth->execute( $rpath );
    @row = $findUpdateSth->fetchrow_array;
    if ($row[0] > 0){
      $updated++;
      $updateSth->execute( $s,$r,$rpath);
    }else{
      $inserted++;
      $findIdSth->execute( $rpath );
      @row=$findIdSth->fetchrow_array;
      $insertSth->execute( $row[0],time,time,$s,$r );
    }
  }elsif ($stats){
    my ($s,$r)=split /:/ , $stats ;
  
    $failed++;
    print "Failed to find $rpath in local Amarok unable to set ";
    print "score $s rating $r.\n";
  }else{
    $failed++;
    print "$rpath never played in remote Amarok; no score or rating found.\n";
  }
}

print "Attempted to import $toImport. Found $found, updated $updated and inserted $inserted. $failed failed.\n";