#! /usr/bin/perl

use strict;
use warnings;
use DBD::SQLite;
use feature 'switch';
no warnings 'experimental::smartmatch';

=pod

Firefox history analyzer -- print all domains visited and the number 
of times visited, or a dump of all history URLs in chronological order

based on firefox bookmarks exporter by jdporter

https://www.perlmonks.org/?node_id=11113866

=cut


sub usage {
    print <<USAGE;
Usage: 
$0 [path-to-Firefox-profile/places.sqlite] [command]

Command is one of:

h -- list all history by date order.
d -- List domains and visit counts, sorted by most often visited.

Firefox must be closed for this to work, or you'll get "Database locked"
errors.

USAGE
}

sub unique_domains;
sub list_visit_dates;

my $dbfile = shift;
$dbfile or usage, exit;
-r $dbfile or die "Unreadable  $dbfile\n";
$dbfile =~ /\bplaces\.sqlite$/ or die "File should be places.sqlite\n";

my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","") or die "Error opening db $dbfile\n";

my $history = $dbh->selectall_hashref( q(
SELECT moz_historyvisits.id, visit_date, url
FROM moz_places, moz_historyvisits
WHERE moz_places.id = moz_historyvisits.place_id ), 'id' );

given ( $ARGV[0] ) {
    when ( undef ) {
	usage;
    }
    when( 'h' ) {
        list_visit_dates;
    }
    when( 'd' ) {
	unique_domains;
    }
    default {
        usage;
    }
}


sub unique_domains {
    my %domains;
    for my $k ( keys %$history ) {
	if ( $history->{$k}{url} =~ m! \w+:// ([^/]+) !x ) {
	    $domains{ $1 }++;
	}
    }

    for my $d ( reverse sort { $domains{$a} <=> $domains{$b} || $a cmp $b } keys %domains ) {
	printf "%4d\t%s\n", $domains{$d}, $d;
    }
}

# https://support.mozilla.org/en-US/questions/972178 indicates that
# visit_date is a million times the Unix epoch date (with potentially
# microsecond accuracy on some machines?)
sub list_visit_dates {
   for my $k ( sort {
	my $c = $history->{$a}{visit_date} // 0;
	my $d = $history->{$b}{visit_date} // 0;
	    $c <=> $d
	||
	    $history->{$a}{url} cmp $history->{$a}{url}
	||
	    $a <=> $b	    
	       } keys %$history ) 
   {
	my $t = $history->{$k}{visit_date} // 0;
	next unless $t;
	$t /= 1_000_000;
	printf "%20s %s\n", scalar localtime($t), $history->{$k}{url};
   }

}