rsync -avP --exclude="*.dat" --exclude="*.bak" --exclude="*.lock" --exclude=".*" --exclude="*.sized.*" --exclude="*.thumb.*" --exclude="*.highlight.*" albums/ piwigo/galleries/

#!/usr/bin/perl -w

use strict;
use Getopt::Std;
use Data::Dumper;
use PHP::Serialization qw(unserialize);
use POSIX qw(strftime);
use Storable qw(dclone);
use DBI;

# -m to generate filesystem mv commands and exit
# -c to NOT import comments (which would be duplicated if they were already imported)
my %opts;
getopts('mc', \%opts);

# G1 albums directory
my $g1_albumdir = "/home/myuser/public_html/albums/";

my $pwg_db_dsn  = "DBI:mysql:database=myuser_piwigo;host=localhost";
my $pwg_db_user = "myuser_piwigo";
my $pwg_db_pass = "abc123";
my $pwg_db_prefix = "piwigo_";

# Connect to the new Piwigo database
my $dbh = DBI->connect( $pwg_db_dsn, $pwg_db_user, $pwg_db_pass,  {
    PrintError => 0,
    RaiseError => 1
});

# Get a list of the G1 album names
my @g1_albums = getalbums($g1_albumdir);

# %parsedgallery will hold all info pulled from G1. Isn't used outside of the loop right now.
my %parsedgallery;
# Highlights for albums of albums need to be saved for a later pass to find their highlights.
my %externalalbumhighlights;
# All keywords appearing in G1
my %g1_keywords;

# Loop through all of the albums
my $i = 0;
ALBUM: for my $albumname (@g1_albums)  {

    $i++;

    # get detailed information about the given G1 album
    $parsedgallery{$albumname} = dclone( getalbuminfo($g1_albumdir, $albumname) );

    # Generate shell command to move album directory to proper subdirectory
    if ($opts{m})  {
        if ( $parsedgallery{$albumname}{parentAlbumName} ne "0" )  {
            print "mv $albumname `find . -type d -name $parsedgallery{$albumname}{parentAlbumName}`\n";
        }
        next ALBUM;
    }

    print "Processing $albumname ($i of ", scalar @g1_albums, ")...\n";

    my $pwg_name = $parsedgallery{$albumname}{title};
    my $pwg_comment = $parsedgallery{$albumname}{description};
    my $pwg_lastmodified = $parsedgallery{$albumname}{last_mod_time};

    my $pwg_status = "public";
    unless ($parsedgallery{$albumname}{canRead})  {
        $pwg_status = "private";
    }

    # name is a direct copy of title
    # comment is a direct copy of description. (TODO What about summary?)
    # lastmodified is a copy of last_mod_time (converted from epoch)
    # rank is infered from the serialized order
    #       The G1 serialized data is written out in the order the albums appear.
    #       Using the serialized index means albums will be in the right order but numbers will be
    #       skipped (because some albums are moved to subalbums). Piwigo doesn't seem to care
    #       if numbers are skipped.
    # image_order is forced to manual since G1 doesn't seem to diferientiate between
    #   automatically and manually sorted. The actual order of the images is set in
    #   the individual image records.
    # status is a modified copy of canRead

    my $sth = $dbh->prepare("UPDATE ".$pwg_db_prefix."categories SET name = ?,
                                                          comment = ?,
                                                          lastmodified = FROM_UNIXTIME( ? ),
                                                          rank = ?,
                                                          image_order = 'rank ASC',
                                                          status = ?
                                                       WHERE dir = ?
                            ");
     $sth->execute($pwg_name,
                   $pwg_comment,
                   $pwg_lastmodified,
                   $i,
                   $pwg_status,
                   $albumname
                   );

    # We moved album directories into other album directories above.
    # The only way for us to reliably locate a Piwigo image database record
    # is with the full path to the image. We need to determine the new location
    # of the album to get the full path.
    my $pwg_albumpath = &getpwgcatpath($albumname);

    # Get detailed information about the photos in the given album and loop through them.
    $parsedgallery{$albumname}{photos} = dclone( getphotosinfo($g1_albumdir, $albumname) );
    PHOTO: foreach my $photo (keys %{ $parsedgallery{$albumname}{photos} } )  {

        # If the current "photo" is actually an album, the only info it (might) have
        # is the highlight photo. We can't reliably resolve that yet so tuck it away
        # for later processing and skip to the next photo.
        if (defined $parsedgallery{$albumname}{photos}{$photo}{isAlbumName}
                && defined $parsedgallery{$albumname}{photos}{$photo}{highlight}
                && $parsedgallery{$albumname}{photos}{$photo}{highlight} )  {
            $externalalbumhighlights{$albumname} = $parsedgallery{$albumname}{photos}{$photo}{isAlbumName};
        }
        next PHOTO if defined $parsedgallery{$albumname}{photos}{$photo}{isAlbumName} && $parsedgallery{$albumname}{photos}{$photo}{isAlbumName};

        # G1 stores the filename in two parts. We need to use them individually later on.
        my $img_file_name = $parsedgallery{$albumname}{photos}{$photo}{name};
        my $img_file_ext = $parsedgallery{$albumname}{photos}{$photo}{type};

        my $pwg_img_full_path = "./galleries/$pwg_albumpath/$img_file_name.$img_file_ext";

        # Set a default empty comment
        my $pwg_comment = undef;
        my $g1_caption = $parsedgallery{$albumname}{photos}{$photo}{caption};
        # Only copy the G1 caption if it's not empty and it's not the filename
        if ( $g1_caption && $g1_caption ne $img_file_name && $g1_caption ne uc($img_file_name.'.'.$img_file_ext) )  {
            $pwg_comment = $g1_caption;
        }

        my $pwg_date_available = $parsedgallery{$albumname}{photos}{$photo}{uploadDate};
        my $pwg_hit = $parsedgallery{$albumname}{photos}{$photo}{clicks};

        # Piwigo level 0 is everyone and level 8 is admins-only.
        my $pwg_level = 0;
        if ( defined $parsedgallery{$albumname}{photos}{$photo}{hidden}
                && $parsedgallery{$albumname}{photos}{$photo}{hidden} )  {
            $pwg_level = 8;
        }

        # Update the image details in the Piwigo database
        my $sth = $dbh->prepare("UPDATE ".$pwg_db_prefix."images SET comment = ?,
                                                          date_available = FROM_UNIXTIME(?),
                                                          hit = ?,
                                                          level = ?
                                                      WHERE path = ?
                                ");
        $sth->execute($pwg_comment, $pwg_date_available, $pwg_hit, $pwg_level, $pwg_img_full_path);

        # Don't import comments if flagged so we can avoid duplicates.
        # All comments are imported as the guest user. Good enough for now.
        # Being more accurate pretty much requires importing the users. TODO
        if ( ! $opts{c}
                && defined $parsedgallery{$albumname}{photos}{$photo}{comments}
                && scalar keys %{ $parsedgallery{$albumname}{photos}{$photo}{comments} } > 0)  {
            foreach my $g1_comment (keys %{ $parsedgallery{$albumname}{photos}{$photo}{comments} })  {
                my $text = $parsedgallery{$albumname}{photos}{$photo}{comments}{$g1_comment}{commentText};
                my $name = $parsedgallery{$albumname}{photos}{$photo}{comments}{$g1_comment}{name};
                my $date = $parsedgallery{$albumname}{photos}{$photo}{comments}{$g1_comment}{datePosted};
                my $ip = $parsedgallery{$albumname}{photos}{$photo}{comments}{$g1_comment}{IPNumber};

                my $sth = $dbh->prepare("INSERT INTO ".$pwg_db_prefix."comments
                                             (image_id, date, author, author_id, anonymous_id, content)
                                         SELECT ".$pwg_db_prefix."images.id, FROM_UNIXTIME(?), ?, ".$pwg_db_prefix."users.id, ?, ?
                                         FROM ".$pwg_db_prefix."images, ".$pwg_db_prefix."users
                                         WHERE ".$pwg_db_prefix."images.path = ?
                                         AND ".$pwg_db_prefix."users.username = 'guest'
                                        ");
                $sth->execute($date, $name, $ip, $text, $pwg_img_full_path);
            }
        }

        # Add keywords to hash to be used later
        if (defined $parsedgallery{$albumname}{photos}{$photo}{keywords}
                && $parsedgallery{$albumname}{photos}{$photo}{keywords})  {
            # This assumes keywords are characters separated by spaces or commas.
            my @g1_photo_keywords = split(/[,\s]+/, $parsedgallery{$albumname}{photos}{$photo}{keywords});
            foreach my $g1_photo_keyword ( trim(@g1_photo_keywords) )  {
                $g1_photo_keyword = lc($g1_photo_keyword);
                push @{ $g1_keywords{$g1_photo_keyword} }, $pwg_img_full_path;
            }
        }


        # Image order is implied by the order of the serialized G1 data (see album notes).
        my $pwg_img_rank = $parsedgallery{$albumname}{photos}{$photo}{rank};

        # Update the image order in the Piwigo database
        # We have to join tables to be able to get the category and image ids with the info we have
        $sth = $dbh->prepare("UPDATE ".$pwg_db_prefix."image_category pic
                                            JOIN ".$pwg_db_prefix."images pi ON pic.image_id = pi.id
                                            JOIN ".$pwg_db_prefix."categories pc ON pic.category_id = pc.id
                                        SET pic.rank = ?
                                        WHERE pi.path = ?
                                            AND pc.dir = ?");
        $sth->execute($pwg_img_rank, $pwg_img_full_path, $albumname);

        # check if the current image is the highlight image for the album
        if ( defined $parsedgallery{$albumname}{photos}{$photo}{highlight}
             && $parsedgallery{$albumname}{photos}{$photo}{highlight} )  {
            # Update the album details with the highlight image id
            my $sth2 = $dbh->prepare("UPDATE ".$pwg_db_prefix."categories pc
                                             JOIN ".$pwg_db_prefix."images pi ON pc.id = pi.storage_category_id
                                         SET pc.representative_picture_id = pi.id
                                         WHERE pi.path = ?");
            $sth2->execute( $pwg_img_full_path );
        }

    }
}

# Quit if we are just generating the mv commands
exit if $opts{m};

# Populate the Piwigo global_rank for categories.
# We can't do this until all individual albums have had their rank populated.
print "Updating global_rank...\n";
my $sth = $dbh->prepare("SELECT id FROM ".$pwg_db_prefix."categories");
$sth->execute;
my ( $id );
$sth->bind_columns(\$id );

while ( $sth->fetch() )  {

    my $sth2 = $dbh->prepare("UPDATE ".$pwg_db_prefix."categories SET global_rank = ? WHERE id = ?");
    $sth2->execute( getglobalrank($id), $id );

}


# Albums that only contain other albums don't have photos they can choose as highlight photos.
# They can just point at another album and use that album's highlight photo.
# We can't determine the id of another album's photo until that album has been loaded.
# Even worse, that album may be another album of albums that has an unknown highlight photo.
# So we take the whole list of albums of albums and repeastedly loop over it
# until all the highlight photos are known.
print "Updating highlight photos of albums without photos...\n";
while (keys %externalalbumhighlights > 0)  {
    foreach my $albumofalbums (keys %externalalbumhighlights )  {

        my $sth = $dbh->prepare("SELECT pc1.representative_picture_id, pc2.representative_picture_id
                                 FROM `".$pwg_db_prefix."categories` pc1
                                 JOIN `".$pwg_db_prefix."categories` pc2 ON pc1.id = pc2.id_uppercat
                                 WHERE pc1.dir = ?
                                 AND pc2.dir = ?");
        $sth->execute($albumofalbums, $externalalbumhighlights{$albumofalbums});

        my ($rpid, $child_rpid) = $sth->fetchrow_array();
        # If the child category doesn't have a highlight photo yet,
        # bail and save it for the next loop.
        next if not defined $child_rpid;

        $sth = $dbh->prepare("UPDATE ".$pwg_db_prefix."categories
                                 SET representative_picture_id = ?
                                 WHERE dir = ?
                                ");
        $sth->execute($child_rpid, $albumofalbums);

        # We have our highlight photo, delete the category from the hash.
        delete $externalalbumhighlights{$albumofalbums};
    }
}

print "Adding tags...\n";
foreach my $g1_keyword (keys %g1_keywords)  {
    # Does the keyword already exist from a previous run?
    my $sth = $dbh->prepare("SELECT COUNT(*)
                             FROM ".$pwg_db_prefix."tags
                             WHERE name = ?
                            ");
    $sth->execute($g1_keyword);
    my ($count) = $sth->fetchrow_array;
    # If it doesn't already exist, insert it.
    if ($count == 0)  {
        my $sth = $dbh->prepare("INSERT INTO ".$pwg_db_prefix."tags (name, url_name)
                                 VALUES (?, ?)
                                ");
        $sth->execute($g1_keyword, $g1_keyword);
    }
    foreach my $photo (@{ $g1_keywords{$g1_keyword} })  {
        my $sth = $dbh->prepare("INSERT IGNORE INTO ".$pwg_db_prefix."image_tag (image_id, tag_id)
                                 SELECT pi.id, pt.id
                                 FROM ".$pwg_db_prefix."images pi, ".$pwg_db_prefix."tags pt
                                 WHERE pi.path = ?
                                 AND pt.name = ?
                                ");
        $sth->execute($photo, $g1_keyword);
    }
}


#############################
# Subroutines
#############################

sub getalbums  {

    my ($gallerydirectory) = @_;
    my $data;
    my @albums;

    open FILE, "$gallerydirectory/albumdb.dat" or die "Couldn't open $gallerydirectory: $!";
    {
        undef $/;
        $data = <FILE>;
    }
    close FILE;
    my $albumdb = unserialize($data);

    for my $albumname (@$albumdb)  {
        push @albums, $albumname;
    }

    return @albums;

}


sub getalbuminfo  {

    my ($gallerydirectory, $albumname) = @_;
    my $data;

    open FILE, "$gallerydirectory/$albumname/album.dat" or die "Couldn't open $gallerydirectory/$albumname/album.dat: $!";
    {
        undef $/;
        $data = <FILE>;
    }
    close FILE;
    my $albumtree = unserialize($data);

    my %albuminfo;

    $albuminfo{name} = $albumtree->{fields}{name};
    $albuminfo{title} = $albumtree->{fields}{title};
    $albuminfo{parentAlbumName} = $albumtree->{fields}{parentAlbumName};
    if ( $albumtree->{fields}{description} )  {
        $albuminfo{description} = $albumtree->{fields}{description};
    } else  {
        $albuminfo{description} = undef;
    }
    $albuminfo{summary} = $albumtree->{fields}{summary};
    $albuminfo{last_mod_time} = $albumtree->{fields}{last_mod_time};
    $albuminfo{canRead} = $albumtree->{fields}{perms}{canRead}{everybody};

    return \%albuminfo;
}

sub getphotosinfo  {

    my ($gallerydirectory, $albumname) = @_;
    my $data;

    open FILE, "$gallerydirectory/$albumname/photos.dat" or die "Couldn't open $gallerydirectory/$albumname/photos.dat: $!";
    {
        undef $/;
        $data = <FILE>;
    }
    close FILE;
    my $photostree = unserialize($data);

    my %photosinfo;

    for my $i ( 0 .. $#{$photostree} ) {
        no warnings 'uninitialized';
        $photosinfo{$i}{rank} = $i;
        $photosinfo{$i}{isAlbumName} = @{$photostree}[$i]->{'isAlbumName'};
        $photosinfo{$i}{name} = @{$photostree}[$i]->{'image'}{'name'};
        $photosinfo{$i}{type} = @{$photostree}[$i]->{'image'}{'type'};
        $photosinfo{$i}{uploadDate} = @{$photostree}[$i]->{'uploadDate'};
        $photosinfo{$i}{highlight} = @{$photostree}[$i]->{'highlight'};
        $photosinfo{$i}{hidden} = @{$photostree}[$i]->{'hidden'};
        $photosinfo{$i}{caption} = @{$photostree}[$i]->{'caption'};
        $photosinfo{$i}{keywords} = @{$photostree}[$i]->{'keywords'};
        $photosinfo{$i}{clicks} = @{$photostree}[$i]->{'clicks'};

        for my $j ( 0 .. $#{ @{$photostree}[$i]->{'comments'} } ) {
            $photosinfo{$i}{comments}{$j}{name} = @{$photostree}[$i]->{'comments'}[$j]->{'name'};
            $photosinfo{$i}{comments}{$j}{datePosted} = @{$photostree}[$i]->{'comments'}[$j]->{'datePosted'};
            $photosinfo{$i}{comments}{$j}{commentText} = @{$photostree}[$i]->{'comments'}[$j]->{'commentText'};
            $photosinfo{$i}{comments}{$j}{IPNumber} = @{$photostree}[$i]->{'comments'}[$j]->{'IPNumber'};
        }
    }

    return \%photosinfo;
}

# Get the list of fileststem directories for a Piwigo category
sub getpwgcatpath()  {
    my ($albumname) = @_;

    # uppercats is a comma-separated list of the individual category ids of the
    # categories in the tree.
    my $sth = $dbh->prepare("SELECT uppercats FROM ".$pwg_db_prefix."categories WHERE dir = '$albumname'; ");
    $sth->execute;

    my ($uppercats) = $sth->fetchrow_array();
    my @cats = split /,/, $uppercats;

    my $albumpath = '';
    foreach my $cat (@cats)  {

        # Get the directory name of the current category
        my $sth2 = $dbh->prepare("SELECT dir FROM ".$pwg_db_prefix."categories WHERE id = $cat; ");
        $sth2->execute;

        # Add the current directory to the end of the list of directories.
        my ($dir) = $sth2->fetchrow_array();
        if ($albumpath)  {
            $albumpath = join('/', $albumpath, $dir);
        } else  {
            $albumpath = $dir;
        }
    }

    return $albumpath;

}

# Get the global_rank value for a given category
#   The global_rank lists the rank value of each category in the tree
sub getglobalrank  {
    my ($id) = @_;

    # Get the parent category's id and the current category's rank
    my $sth = $dbh->prepare("SELECT id_uppercat, rank FROM ".$pwg_db_prefix."categories WHERE id = $id");
    $sth->execute;

    my ($id_uppercat, $rank) = $sth->fetchrow_array();

    my $global_rank;

    # If a parent category is listed, we aren't at the top of the tree yet.
    if (defined $id_uppercat
            && $id_uppercat)  {
        # Recurse to get the id(s) from the parent(s) and add ours to the list
        $global_rank = getglobalrank($id_uppercat) . "." . $rank;
    } else  {
        $global_rank = $rank;
    }

    return $global_rank;

}

# trim simply trims any leading or trailing whitespace from a string or array of strings.
# It returns the trimmed strings.
#
sub trim {
    my @out = @_;
    for (@out) {
        s/^\s+//;
        s/\s+$//;
    }
    return wantarray ? @out : $out[0];
}

__END__