#!/usr/bin/perl
use strict;
use warnings;
use DBI;

my $dbh = DBI->connect("dbi:SQLite:dbname=users.lite","","",
	{PrintError => 1, AutoCommit => 0}) or die "Can't connect";

# Prepare and print list of all websites to every user
my $sth = $dbh->prepare(<<SQL);
SELECT *
FROM users
ORDER BY user, site
SQL

$sth->execute;
while(my @row = $sth->fetchrow_array) {
	printf "%-15s%-20s%s\n", @row;
}
print "\n";

# Create list of users from most visits to least for @users array
$sth = $dbh->prepare(<<SQL);
SELECT user, COUNT(user) Count
FROM users
GROUP BY user
ORDER BY Count DESC, user
SQL

$sth->execute;
my @users;
while(my @row = $sth->fetchrow_array) {
	push @users, $row[0];
}

# Counts for each website and counts of categories visited by user
for my $user (@users) {
	$sth = $dbh->prepare(qq{SELECT site, COUNT(site) Count
							FROM users
							WHERE user = '$user'
							GROUP BY site
							ORDER BY Count DESC
					});
	$sth->execute;
	printf "Name: %s\n\t%-20s%s\n", $user, qw/ Website Count /;
	while(my @row = $sth->fetchrow_array) {
		printf "\t%-20s%s\n", @row;
	}
	print "\n";
	
	printf "\t%-20s%s\n", qw/ Category Count /;
	$sth = $dbh->prepare(qq{SELECT type, COUNT(type) Count
							FROM users
							WHERE user = '$user'
							GROUP BY type
							ORDER BY Count DESC
					});
	$sth->execute;
	while(my @row = $sth->fetchrow_array) {
		printf "\t%-20s%s\n", @row;
	}
	print "\n";
}

$dbh->disconnect;