#!/usr/bin/perl -w

#
# zophImport.pl
# Zoph 0.6
# Jason Geiger & Jeroen Roos, 2002-2006
#
# This file is part of Zoph.
#
# Zoph is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# Zoph is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with Zoph; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA
#
#
# Inserts images into the zoph database.  Info from the exif headers is
# inserted along with any other fields/values passed in.
#
# example: zophImport.pl --photographer "Joe Smith" --album "Big Trees Hike" --category "Trees" --category "Sunset" --path "kodak_dc280" --datedDirs incoming/*.jpg
#
# Note that any album, category, person or place passed in must already exist
# in the database.
#
# The -datedDirs flag will cause the photos to be moved from the incoming
# directory to a directory generated by the date the photo was taken.  This
# directory is created in the directory specified by the --path flag, or in
# the current directory if no --path was given.
#
# The --update flags causes existing records to be updated instead of inserted.
#
# jhead is used to parse the exif, it needs to be in your path.
#
# imagemagick's convert utility is used to create thumbnails.  Creating these
# thumbnails is what takes by far the most time.
#

use strict;
use Getopt::Long;
use DBI;
use Image::Size;
use File::Copy;
use File::stat;
use Cwd 'abs_path';

$| = 1;

die "Error: \$HOME/.zophrc not found"
  if !-e $ENV{HOME}."/.zophrc";

require $ENV{HOME}."/.zophrc" if -r $ENV{HOME}."/.zophrc";
my $db_prefix = $::db_prefix;
my $image_dir = $::image_dir;
 
my $version = '0.6';
 

my $update     = 0; # update existing photo records instead of inserting
my $updateSize = 0; # update the size, width and height (implies -update)
my $updateExif = 0; # reparse the exif headers (implies -update)

my $useIds     = 0; # arguments are photo_ids, not file names

my $datedDirs  = 0; # photos should be moved to a YYYY.MM.DD directory
my $thumbnails = 2; # create thumbails of image

my $hierarchical = 0; # when set, dateddirs will be yyyy/mm/dd instead of
                     # yyyy.mm.dd, thus creating a hierarchical structure.

my $ignoreerror = 0; # when set, zophImport will ignore missing albums,
                    # people, categories and locations.
                    # this was the default behaviour until 0.4.
my $verbose = 0;    # zopHImport will be more verbose about what it's doing.
my $copy = 0;       # copy files instead of move.

# the maxinum dimension of the two sizes of images to be generated
my $midSize = 480;
my $thumbSize = 120;

my $midPrefix = 'mid';
my $thumbPrefix = 'thumb';

# if set to 0, one type of thumbnail will be generated for all image types
my $mixed_thumbnails = 1;

# the extension (and image type) to be used for thumbnails.
# ignored (for many image types) if mixed_thumbnails is set to 1.
my $thumb_extension = "jpg";

my %fieldHash;  # values that can be applied to all photos processed
my %exifHash;   # these will vary photo by photo

my $path;       # the path to the image, modified if $datedDirs is used

my @albums;     # albums the photos should be added to
my @categories; # categories the photos should be added to
my @people;     # the people in the photograph

if ($#ARGV < 0) {
    printUsage();
    exit(1);
}

my $dbh = DBI->connect("DBI:mysql:$::db_name:$::db_host", $::db_user, $::db_pass);
$::db_name = '';
$::db_user = '';
$::db_pass = '';
$::db_host = '';
$::db_prefix = '';
$::image_dir = '';

GetOptions(
    'help' => sub { printUsage(); exit(0); },
    'update' => \$update,
    'updateSize' => \$updateSize,
    'updateExif' => \$updateExif,
    'useIds' => \$useIds,
    'datedDirs!' => \$datedDirs,
    'hierarchical!'=> \$hierarchical,
    'thumbnails!' => \$thumbnails,
    'album|albums=s' => \@albums,
    'category|categories=s' => \@categories,
    'person|people=s' => \@people,
    'photographer=s' => sub {
        my ($n, $v) = @_;
        if ($v = lookupPersonId($v)) { $fieldHash{'photographer_id'} = $v; }
    },
    'location=s' => sub {
        my ($n, $v) = @_;
        if ( index($v,"/") >= 0 ) {
            if ($v = lookupLocationIdFromTree($v)) { $fieldHash{'location_id'} = $v; }
        }
        else {
            if ($v = lookupPlaceId($v)) { $fieldHash{'location_id'} = $v; }
        }
    },
    'path=s' => \$path,
    'field=s' => \%fieldHash,
    'copy!' => \$copy,
    'verbose!' => \$verbose,
    'clear' => sub { %fieldHash = (); }
) or die "Error parsing options";

# strip trailing slashes
if ($path) { $path =~ s/\/+$//; }

if ($updateSize or $updateExif) { $update = 1; }
if ($update and $path) { $fieldHash{'path'} = $path; }
if ($update and $thumbnails != 1) { $thumbnails = 0; }

# allows for comma separated lists as well as multiple flags
# oops, I have albums with a comma in the name
# should probably do some sort of escape for these
#@albums = split(/\s*,\s*/, join(',', @albums));
@categories = split(/\s*,\s*/, join(',', @categories));
@people = split(/\s*,\s*/, join(',', @people));

if (!$ignoreerror) {
   # Do a check of all parameters before loading the first image
    foreach my $album (@albums) {
        if ( index($album,"/") >= 0 ) {
            lookupAlbumIdFromTree($album);
        }
        else {
            lookupAlbumId($album);
        }
    }
    
    foreach my $cat (@categories) {
        if ( index($cat,"/") >= 0 ) {
            lookupCategoryIdFromTree($cat);
        }
        else {
            lookupCategoryId($cat);
        }
    }
    
    foreach my $person (@people) {
        lookupPersonId($person);
    }
}
   
my $insert_sth = '';
if (not $update) {
    $insert_sth = $dbh->prepare(
        "insert into " . $db_prefix . "photos " .
        "(name, path, width, height, size) values (?, ?, ?, ?, ?)");
}

while ($_ = shift) {
    processImage($_);
}

$dbh->disconnect();

print "\n";

######################################################################

#
# Usage.
#
sub printUsage {
    print
        "zophImport.pl $version\n" .
        "Usage: zophImport.pl [OPTIONS] [IMAGE ...]\n" .
        "OPTIONS:\n" .
        "   --album ALBUM\n" .
        "   --category CATEGORY\n" .
        "   --photographer \"FIRST_NAME LAST_NAME\"\n" .
        "   --people \"FIRST_NAME LAST_NAME, FIRST_NAME LAST_NAME\"\n" .
        "   --location PLACE_TITLE\n" .
        "   --field NAME=VALUE\n" .
        "   --path\n" .
        "   --datedDirs\n" .
        "   --hierarchical\n" .
        "   --update\n" .
        "   --updateSize (implies --update)\n" .
        "   --updateExif (implies --update)\n" .
        "   --useIds\n" .
        "   --nothumbnails\n" .
        "   --verbose\n" .
        "   --copy\n";
}

#
# Processes an image.
#
sub processImage {
    my ($arg) = @_;

    if (not $update and not -f $arg) {
        # file must be found if doing an insert
        print "Not a file: $arg\n";
        return;
    }

    %exifHash = (); # clear previous entries

    my @ids;
    my @imgs;

    if ($update) {
        if ($useIds) {
            my ($min, $max) = (0, 0);

            if (index($arg, '-') > 0) {
                ($min, $max) = split '-', $arg;
            }
            else {
                $min = $arg;
                $max = $min;
            }

            for (my $i = $min; $i <= $max; $i++) {
                push @ids, $i;
                push @imgs, lookupPhoto($i);
            }
        }
        else {
            push @imgs, $arg;
            push @ids, lookupPhotoId($arg);
        }

    }
    else {
        push @imgs, $arg;
        push @ids, insertPhoto($arg);
        $updateExif = 1;
    }

    while (my $id = shift @ids) {

        my $img = shift @imgs;

        if ($updateSize) {
            my $size = -s $img;
            my ($width, $height, $imgInfo) = imgsize($img);

            $fieldHash{'size'} = $size;
            $fieldHash{'width'} = $width;
            $fieldHash{'height'} = $height;
        }

        if ($updateExif) {
            parseExif($img);
            if (!$exifHash{"date"}) {
                ($exifHash{"date"}, $exifHash{"time"}) = getDateFromFile($img);
                print "\n$img: no date found in EXIF, using file date: " . $exifHash{"date"} . ", " . $exifHash{"time"} ."\n";
            }
        }
        my $newPath = $path;

        if ($datedDirs) {
            $newPath = useDatedDir($img);
        }
        elsif ($path) {
            my $thisPath = $img;
            $thisPath =~ s|/?[^/]+$||;
            if (abs_path($image_dir . "/" . $path) ne abs_path($thisPath)) {
                if (not -d "$image_dir/$path") {
                    mkdir("$image_dir/$path", 0755) or warn "Could not create dir: $!\n";
                }
                copy($img, "$image_dir/$path/" . stripPath($img)) or
                    die "Could not copy file: $!\n";
                if (!$copy) {
                    unlink($img);
                }
            }
        } else {
        my $thisPath = $img;
            $thisPath =~ s|/?[^/]+$||;
            if (abs_path($image_dir) ne abs_path($thisPath)) {
                copy($img, "$image_dir/" . stripPath($img)) or
                    die "Could not copy file: $!\n";
                if (!$copy) {
                    unlink($img);
                }
            }
        } 
    
        if ($newPath) {
            $fieldHash{'path'} = $newPath;
        } 
    
        if ($thumbnails) {
            createThumbnails($img, $midPrefix, $midSize, $newPath);
            createThumbnails($img, $thumbPrefix, $thumbSize, $newPath);
        }

        updatePhoto($id, $img);
        addToAlbums($id);
        addToCategories($id);
        addPeople($id);

        # the fancy status indicator
        if ($verbose && !$copy) {
            print "Image $img moved to $image_dir/$fieldHash{'path'}/".stripPath($img)."\n";
        } elsif ($verbose && $copy) {
            print "Image $img copied to $image_dir/$fieldHash{'path'}/".stripPath($img)."\n";
        } else {
            print ".";
        }
    }

}

#
# Inserts a photo (file name, path, width, height, size).
#
sub insertPhoto {
    my ($image) = @_; 

    my $size = -s $image;
    my ($width, $height, $imgInfo) = imgsize($image);

    my $imgPath = $path;
    # if a path was not passed, try to extract one from the image
    if (not $imgPath) {
        $imgPath = $image;
        $imgPath =~ s|/?[^/]+$||;
    }

    $image = stripPath($image);

    #print "$image\t$path\t$width\t$height\t$size\n";
    $insert_sth->execute($image, $imgPath, $width, $height, $size);

    return $insert_sth->{'mysql_insertid'};
}

#
# Updates a photo record using the contents of the fieldHash and exifHash.
#
sub updatePhoto {
    my ($id, $image) = @_;

    my $update = '';
    foreach my $field (keys %exifHash) {
        if ($update ne '') { $update .= ", "; }
        $update .= "$field = " . $dbh->quote($exifHash{$field});
    }
    foreach my $field (keys %fieldHash) {
        if ($update ne '') { $update .= ", "; }
        $update .= "$field = " . $dbh->quote($fieldHash{$field});
    }

    if ($update ne '') {
        $update =
            "update " . $db_prefix . "photos set $update " .
            "where photo_id = $id";

        #print "$update\n";
        my $updateSth = $dbh->prepare($update);
        $updateSth->execute();
    }

}

#
# Puts the image in a directory of the form PATH/YYYY.MM.DD
# (creating it if needed).  The date is taken from the exif hash.
#
sub useDatedDir {
    my ($image) = @_;
    
    my $year;
    my $month;
    my $day;
    my @hierpath;

    my $imageName = $image;
    $imageName = stripPath($imageName);
    
    my $fullPath;

    my $datePath = $exifHash{'date'};
    if ($datePath) {
    if ($hierarchical) {
            $datePath =~ s/-/\//g;

            if ($path) {
                $datePath = $path . '/' . $datePath;
            }
        $fullPath = $image_dir . "/" . $datePath;

            if (not -d $fullPath) {
                mkdirRecursive($fullPath, 0755);
            }
        }
        else {
            $datePath =~ s/-/./g;
            if ($path) {
                $datePath = $path . '/' . $datePath;
            }
        
            $fullPath = $image_dir . '/' . $datePath;
            mkdirRecursive($fullPath, 0755);
        }
        copy("$image", "$fullPath/$imageName")
            or die "Could not move file: $!\n";
        if (!$copy) {
            unlink("$image");
        }
    }

    return $datePath;
}

#
# Creates a thumbnail for the given image.
#
sub createThumbnails {
    my ($image, $prefix, $maxSide, $newPath) = @_;

    
    my $outputDir = $image_dir;

    my $imageName = stripPath($image);
    my $newImageName = $prefix . '_' . $imageName;
    my $img = $image;
    if($newPath) {
    $outputDir="$image_dir/$newPath";
    }
    
    if (not -f $img) {
        $img = "$outputDir/$imageName";
    }

    if (not -f $img) {
        print "Could not find $img ($imageName) to create thumbnail\n";
        return;
    }

    my ($width, $height, $imgInfo) = imgsize($img);

    if (not -d "$outputDir/$prefix") {
        mkdir("$outputDir/$prefix", 0755)
            or die "Could not create dir: $!\n";
    }

    if ($width <= $maxSide && $height <= $maxSide) {
        copy($img, "$outputDir/$prefix/$newImageName")
            or die "Could not copy file: $!\n";
    }
    else {
        my $thumbWidth = $maxSide;
        my $thumbHeight = $maxSide;

        if ($width > $height) {
            $thumbHeight = int $maxSide/$width * $height;
        }
        else {
            $thumbWidth = int $maxSide/$height * $width;
        }

        if ($mixed_thumbnails) {
            my $extension =
                lc(substr($newImageName, rindex($newImageName, '.') + 1));

            # maintain image types for the following,
            # generate jpegs for other (gif, bmp, etc.)
            if ($extension ne "jpg" and
                $extension ne "jpeg" and
                $extension ne "jpe" and
                $extension ne "png" and
                $extension ne "gif" and
                $extension ne "tif" and
                $extension ne "tiff") {

                $newImageName =~ s/\.[^\.]+$//;
                $newImageName .= ".$thumb_extension";
            }
        }
        else {
            # generate jpegs for all types
            $newImageName =~ s/\.[^\.]+$//;
            $newImageName .= ".$thumb_extension";
        }

        # use imagemagick's convert utility
        system ("convert -geometry $thumbWidth" . "x$thumbHeight \"$img\" \"$outputDir/$prefix/$newImageName\"");
        # the +profile flag seems to cause problems on some systems
        #system ("convert +profile \"*\" -geometry $thumbWidth" . "x$thumbHeight \"$img\" \"$outputDir/$prefix/$newImageName\"");
    }
}

#
# Adds a photo to one or more albums.
#
sub addToAlbums {
    my ($id) = @_;
    my $album_id = 0;

    foreach my $album (@albums) {
        if ( index($album,"/") >= 0 ) {
            $album_id = lookupAlbumIdFromTree($album);
        }
        else {
            $album_id = lookupAlbumId($album);
        }
        
        if (not $album_id) { next; }

        my $query =
           "select * from " . $db_prefix . "photo_albums " .
           "where photo_id = " . $dbh->quote($id) .
           "and album_id = " . $dbh->quote($album_id);

        my @row_array = $dbh->selectrow_array($query);

        if (@row_array) {
           print "photo " . $id . " is already in album " . $album . ". Not added.\n";
           next;
       }

        my $insert =
            "insert into " . $db_prefix . "photo_albums (photo_id, album_id) " .
            "values ($id, $album_id)";
        #print "$insert\n";

        my $insertSth = $dbh->prepare($insert);
        $insertSth->execute();
    }
}

#
# Adds a photo to one or more categories.
#
sub addToCategories {
    my ($id) = @_;
    my $cat_id = 0;

    foreach my $cat (@categories) {
        if ( index($cat,"/") >= 0 ) {
            $cat_id = lookupCategoryIdFromTree($cat);
        }
        else {
            $cat_id = lookupCategoryId($cat);
        }

        if (not $cat_id) { next; }

        my $query =
           "select * from " . $db_prefix . "photo_categories " .
           "where photo_id = " . $dbh->quote($id) .
           "and category_id = " . $dbh->quote($cat_id);

        my @row_array = $dbh->selectrow_array($query);

        if (@row_array) {
           print "photo " . $id . " is already in category " . $cat . ". Not added.\n";
           next;
       }

        my $insert =
            "insert into " . $db_prefix . "photo_categories " .
            "(photo_id, category_id) values ($id, $cat_id)";
        #print "$insert\n";

        my $insertSth = $dbh->prepare($insert);
        $insertSth->execute();
    }

}

#
# Adds an array of people to a photo.
#
sub addPeople {
    my ($id) = @_;

    my $position = 1;
    foreach my $person (@people) {
        my $person_id = lookupPersonId($person);
        if (not $person_id) { next; }

        my $query =
           "select * from " . $db_prefix . "photo_people " .
           "where photo_id = " . $dbh->quote($id) .
           "and person_id = " . $dbh->quote($person_id);

        my @row_array = $dbh->selectrow_array($query);

        if (@row_array) {
           print $person . " is already in photo " . $id . ". Not added.\n";
           next;
       }

        my $insert =
            "insert into " . $db_prefix . "photo_people " .
            "(photo_id, person_id, position) " .
            "values ($id, $person_id, $position)";
        #print "$insert\n";

        my $insertSth = $dbh->prepare($insert);
        $insertSth->execute();

        $position++;
    }

}

#
# Uses jhead to parse the exif headers.
#
sub parseExif {
    my ($image) = @_; 

    open INFO, "jhead \"$image\" |" or die "Could not get EXIF: $!\n";

    # I've only included those fields that are stored by the cameras that
    # I have used (a Kodak DC280 and an Olympus C3040), so I'm probably
    # missing some.
    while (<INFO>) {
        chomp;

        my ($name, $value) = split /\s*:\s*/, $_, 2;
        if (not $name or not $value) { next; }

        #print "$name\t$value\n";

        $name = lc($name);
        $name =~ s/\s/_/g;
        $name =~ s/[^\w\/]//g;

        if ($name eq "camera_make" or
            $name eq "camera_model") {

            $value =~ s/(\w+)/\u\L$1/g;
            $exifHash{$name} = $value;
        }
        elsif ($name eq "date/time") {
            s/.*?: //;
            my ($date, $time)  = split ' ';
            $date =~ s/:/-/g;
            $exifHash{"date"} = $date;
            $exifHash{"time"} = $time;
        }
        elsif ($name eq "flash_used") {
            $value =~ s/(\w).*/$1/;
            $exifHash{$name} = $value;
        }
        elsif ($name eq "focal_length" or
               $name eq "aperture" or
               $name eq "iso_equiv" or
               $name eq "metering_mode" or
               $name eq "ccd_width" or
               $name eq "focus_dist" or
               $name eq "comment") {
            $exifHash{$name} = $value;
        }
        elsif ($name eq "exposure_time") {
            $name = "exposure";
            if ($exifHash{$name}) {
                $value .= $exifHash{$name};
            }
            $exifHash{$name} = $value;
        }
        elsif ($name eq "exposure") {
            $exifHash{$name} .= " [$value]";
        }
        elsif ($name eq "jpeg_process" or $name eq "jpg_quality") {
            $name =~ s/_/ /;
            $value = "$name: $value";
            if ($exifHash{"compression"}) {
                $value = $exifHash{"compression"} . ", $value";
            }
            $exifHash{"compression"} = $value;
        }
    }

    close INFO;
}

#
# Strips the path from a file name.
#
sub stripPath {
    my ($name) = @_;

    $name =~ s|.*?/?([^/]+)$|$1|;
    return $name;
}

#
# Looks up a photo_id from the file name (and path if available).
#
sub lookupPhotoId {
    my ($img) = @_;

    my $photo = lc(stripPath($img));

    my $query =
        "select photo_id from " . $db_prefix . "photos where " .
        "lower(name) = " .  $dbh->quote($photo);

    # if the path to the image is present, use it too look up the photo as well
    # (don't overwrite the $path global so that a modified value can be passed)
    my $lookupPath = lc($img);
    $lookupPath =~ s|/?[^/]+$||;

    if ($lookupPath) {
        $query .= " and lower(path) =" . $dbh->quote($lookupPath);
    }

    my @row_array = $dbh->selectrow_array($query);

    if (@row_array) {
        return $row_array[0];
    }

    print "Photo not found: $photo\n";

    if (!$ignoreerror) {
        exit(10);
    }
    return 0;
}

#
# Looks up a photo name, plus path, given the id.
#
sub lookupPhoto {
    my ($id) = @_;

    my $query =
        "select name, path from " . $db_prefix . "photos " .
        "where photo_id = " . $dbh->quote($id);

    my @row_array = $dbh->selectrow_array($query);

    if (@row_array) {
        return $row_array[1] . '/' . $row_array[0];
    }

    print "Photo not found: $id\n";
    if (!$ignoreerror) {
        exit(10);
    }
    return "";
}

#
# Looks up a person_id from a FirstName LastName.
#
sub lookupPersonId {
    my ($person) = @_;

    $person = lc($person);

    my $query =
        "select person_id from " . $db_prefix . "people where " .
        "concat(lower(first_name),\" \", lower(last_name)) = " .
        $dbh->quote($person);

    my @row_array = $dbh->selectrow_array($query);

    if (@row_array) {
        return $row_array[0];
    }

    print "Person not found: $person\n";
    if (!$ignoreerror) {
        exit(20);
    }
    return 0;
}

#
# Looks up a place_id from the title.
#
sub lookupPlaceId {
    my ($place) = @_;

    $place = lc($place);

    my $query =
        "select place_id from " . $db_prefix . "places where " .
        "lower(title) = " .  $dbh->quote($place);

    my @row_array = $dbh->selectrow_array($query);

    if (@row_array) {
        return $row_array[0];
    }

    print "Place not found: $place\n";
    if (!$ignoreerror) {
        exit(30);
    }
    return 0;
}

sub lookupLocationIdFromTree {
    my ($place) = @_;
    $place = lc($place);
    
    # The place root is not 0, but a child from '0' (usually 1)
    my $place_id = lookupLocationChild(0);
    
    my @placetree = split("/", $place);
    foreach my $branch (@placetree) {
        if (!$branch) { next; }
        $place_id = lookupLocationChildByName($place_id, $branch);
    }
    return $place_id;
}

sub lookupLocationChildByName {
    my ($place_id, $place) = @_;
    my $query =
       "select place_id from " . $db_prefix . "places where parent_place_id = " . $place_id . " and title = " . $dbh->quote($place);
    my @row_array = $dbh->selectrow_array($query);

    if (@row_array) {
        return $row_array[0];
    }
    print "Location not found: $place\n";
    if (!$ignoreerror) {
        exit(30);
    }
}

#
# Looks up an album_id from an album name.
#
sub lookupAlbumId {
    my ($album) = @_;

    $album = lc($album);

    my $query =
        "select album_id from " . $db_prefix . "albums where lower(album) = " .
        $dbh->quote($album);

    my @row_array = $dbh->selectrow_array($query);

    if (@row_array) {
        return $row_array[0];
    }

    print "Album not found: $album\n";
    if (!$ignoreerror) {
        exit(40);
    }
}

sub lookupAlbumIdFromTree {
    my ($alb) = @_;
    $alb = lc($alb);
    
    # The album root is not 0, but a child from '0' (usually 1)
    my $album_id = lookupAlbumChild(0);
    
    my @albumtree = split("/", $alb);
    foreach my $branch (@albumtree) {
        if (!$branch) { next; }
        $album_id = lookupAlbumChildByName($album_id, $branch);
    }
    return $album_id;
}

sub lookupAlbumChildByName {
    my ($alb_id, $alb) = @_;
    my $query =
       "select album_id from " . $db_prefix . "albums where parent_album_id = " . $alb_id . " and album = " . $dbh->quote($alb);
    my @row_array = $dbh->selectrow_array($query);

    if (@row_array) {
        return $row_array[0];
    }
    
    print "Album not found: $alb\n";
    if (!$ignoreerror) {
        exit(40);
    }
}

sub lookupAlbumChild {
    # Only returns first child!
    my ($alb_id, $alb) = @_;
    my $query =
       "select album_id from " . $db_prefix . "albums where parent_album_id = " . $alb_id;
    my @row_array = $dbh->selectrow_array($query);

    if (@row_array) {
        return $row_array[0];
    }
    print "Album not found: $alb\n";
    if (!$ignoreerror) {
        exit(40);
    }
}

#
# Looks up a category_id from a category name.
#
sub lookupCategoryId {
    my ($cat) = @_;

    $cat = lc($cat);

    my $query =
        "select category_id from " . $db_prefix . "categories " .
        "where lower(category) = " .  $dbh->quote($cat);

    my @row_array = $dbh->selectrow_array($query);

    if (@row_array) {
        return $row_array[0];
    }

    print "Category not found: $cat\n";
    if (!$ignoreerror) {
        exit(50);
    }
}

sub lookupCategoryIdFromTree {
    my ($cat) = @_;
    $cat = lc($cat);
    
    # The category root is not 0, but a child from '0' (usually 1)
    my $category_id = lookupCategoryChild(0);
    
    my @cattree = split("/", $cat);
    foreach my $branch (@cattree) {
        if (!$branch) { next; }
        $category_id = lookupCategoryChildByName($category_id, $branch);
    }
    return $category_id;
}

sub lookupCategoryChildByName {
    my ($cat_id, $cat) = @_;
    my $query =
       "select category_id from " . $db_prefix . "categories where parent_category_id = " . $cat_id . " and category = " . $dbh->quote($cat);
    my @row_array = $dbh->selectrow_array($query);

    if (@row_array) {
        return $row_array[0];
    }
    
    print "Category not found: $cat\n";
    if (!$ignoreerror) {
        exit(50);
    }
}

sub lookupCategoryChild {
    # Only returns first child!
    my ($cat_id, $cat) = @_;
    my $query =
       "select category_id from " . $db_prefix . "categories where parent_category_id = " . $cat_id;
    my @row_array = $dbh->selectrow_array($query);

    if (@row_array) {
        return $row_array[0];
    }
    print "Category not found: $cat\n";
    if (!$ignoreerror) {
        exit(50); 
    }
}

sub lookupLocationChild {
    # Only returns first child!
    my ($place_id, $place) = @_;
    my $query =
       "select place_id from " . $db_prefix . "places where parent_place_id = " . $place_id;
    my @row_array = $dbh->selectrow_array($query);

    if (@row_array) {
        return $row_array[0];
    }
    print "Place not found: $place\n";
    if (!$ignoreerror) {
        exit(30);
    }
}

sub getDateFromFile() {
    my ($image) = @_;
    my $date;
    my $time;
    my $filestat = stat($image);
    my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime($filestat->mtime);
    $date = sprintf("%04d-%02d-%02d",$year+1900, $mon+1, $mday);
    $time = sprintf("%2d:%2d:%2d", $hour, $min, $sec);
    return $date, $time;
   # $exifHash{"date"} = $date;
   # $exifHash{"time"} = $time;
}

sub mkdirRecursive() {
    my ($directory, $mode) = @_;
    my $fulldir;
    my @dirs = split("/", $directory);
    foreach my $dir (@dirs) {
        if(!$dir) { next; }
        $fulldir .= "/" . $dir;
        if(not -d $fulldir) { 
            mkdir($fulldir, $mode) or die "Could not create directory $fulldir: $!\n";
        }
    }
}
