#!/usr/bin/perl -w

#
# zophImport.pl
# Zoph 0.3.3
# Jason Geiger, December 2002
#
# 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;

$| = 1;

die "Error: \$HOME/.zophrc not found, see /usr/share/doc/zoph/README.Debian for details"
  if !-e $ENV{HOME}."/.zophrc";
require $ENV{HOME}."/.zophrc" if -r $ENV{HOME}."/.zophrc";

my $version = '0.3.3';

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

# 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(0);
}

my $dbh = DBI->connect("DBI:mysql:$::db_name", $::db_user, $::db_pass);
# Found this "solution" about the "Name "main::db_user" used only once: possible typo.."
# error on the net. Dunno if its the best way, i'm no Perl fan... <eb>
$::db_name = '';
$::db_user = '';
$::db_pass = '';

GetOptions(
    'help' => sub { printUsage(); exit(0); },
    'update' => \$update,
    'updateSize' => \$updateSize,
    'updateExif' => \$updateExif,
    'useIds' => \$useIds,
    'datedDirs!' => \$datedDirs,
    '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 ($v = lookupPlaceId($v)) { $fieldHash{'location_id'} = $v; }
    },
    'path=s' => \$path,
    'field=s' => \%fieldHash,
    'clear' => sub { %fieldHash = (); }
) or die "Error parsing options";

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));

my $insert_sth = '';
if (not $update) {
    $insert_sth = $dbh->prepare(
        "insert into 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" .
        "	--update\n" .
        "	--updateSize (implies --update)\n" .
        "	--updateExif (implies --update)\n" .
        "	--useIds\n" .
        "	--nothumbnails\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);
        }

        my $newPath = $path;
        if ($datedDirs) {
            $newPath = useDatedDir($img);
        }
        elsif ($path) {
           if (not -d $path) {
               mkdir($path, 0755) or warn "Could not create dir: $!\n";
           }

           copy($img, "$path/" . stripPath($img)) or
               die "Could not copy file: $!\n";
           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
        print ".";
    }

}

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

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

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

    $image = stripPath($image);

    #print "$image\t$path\t$width\t$height\t$size\n";
    $insert_sth->execute($image, $path, $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 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 $imageName = $image;
    $imageName = stripPath($imageName);

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

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

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

        #print "mv $image $datePath/$imageName";
        copy("$image", "$datePath/$imageName")
            or die "Could not move file: $!\n";
        unlink("$image");
    }

    return $datePath;
}

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

    my $imageName = stripPath($image);
    my $newImageName = $prefix . '_' . $imageName;

    my $img = $image;
    if (not -f $img) {
        $img = "$outputDir/$imageName";
    }

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

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

    if (not $outputDir) {
        ($outputDir = $image) =~ s|/?[^/]+$||;
        unless ($outputDir) {
            $outputDir = ".";
        }
    }

    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) = @_;

    foreach my $album (@albums) {
        my $album_id = lookupAlbumId($album);
        if (not $album_id) { next; }

        my $insert =
            "insert into 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) = @_;

    foreach my $cat (@categories) {
        my $cat_id = lookupCategoryId($cat);
        if (not $cat_id) { next; }

        my $insert =
            "insert into 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 $insert =
            "insert into 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 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";
    return 0;
}

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

    my $query =
        "select name, path from 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";
    return "";
}

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

    $person = lc($person);

    my ($first, $last) = split / +/, $person;
    my $query =
        "select person_id from people where " .
        "lower(first_name) = " .  $dbh->quote($first) . " and " .
        "lower(last_name) = " .  $dbh->quote($last);

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

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

    print "Person not found: $person\n";
    return 0;
}

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

    $place = lc($place);

    my $query =
        "select place_id from 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";
    return 0;
}

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

    $album = lc($album);

    my $query =
        "select album_id from 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";
}

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

    $cat = lc($cat);

    my $query =
        "select category_id from 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";
}

