# via Perl

sub get_photos {
    my ( $dbh, $verbose ) = @_;

   # exposure_time maps to date/time original field (time of picture's exposure)
    my $select_phototable_sql =
        q{SELECT `id`,`filename`,`rating`,`title`,`exposure_time` }
      . q{FROM `PhotoTable`};
    my $phototable = $dbh->selectall_arrayref($select_phototable_sql);
    print "Found ", scalar(@$phototable), " photos in shotwell
PhotoTable\n" if $verbose;
    $select_phototable_sql =~ s/PhotoTable/VideoTable/;
    my $videotable = $dbh->selectall_arrayref($select_phototable_sql);
    print "Found ", scalar(@$videotable), " videos in shotwell
VideoTable\n" if $verbose;
    my %photos_byhexid = (
        map( (
                sprintf( 'thumb%016x', $_->[0] ),
                [ $_->[1], $_->[2], $_->[3], [], $_->[4] ]
            ),
            @$phototable ),
        map( (
                sprintf( 'video-%016x', $_->[0] ),
                [ $_->[1], $_->[2], $_->[3], [], $_->[4] ]
            ),
            @$videotable )
    );

    my $select_tagtable_sql =
      q{SELECT `name`,`photo_id_list` } . q{FROM `TagTable`};
    my $tagtable = $dbh->selectall_arrayref($select_tagtable_sql);
    print "Found ", scalar(@$tagtable), " tags in shotwell TagTable,
distributing to PhotoTable\n" if $verbose;
    foreach my $row (@$tagtable) {
        my $name = $row->[0];
        $name =~
          s/^\///;    # trim leading / (not sure why it is present in database)
        next unless defined( $row->[1] );    # sometimes a tag is not
        associated with any images
          foreach my $hexid->( split( /,/, $row->[1] ) ){
            die(
                "Error: id $hexid found in TagTable was not found in PhotoTable"
            ) unless exists( $photos_byhexid{$hexid} );
            push( @{ $photos_byhexid{$hexid}[3] }, $name );
          };
    }
    return ( [ sort( { $a->[0] cmp $b->[0] } values(%photos_byhexid) ) ] );

    # returned ref is an array of 5-element arrays:
    # [0]: filename
    # [1]: rating
    # [2]: title (caption)
    # [3]: tag array
    # [4]: date/time
}