#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use File::Path;

my $really_delete_the_directories = 1;
#require 'db-common.sub' or die "Error loading db-common.sub";

#my $rootpath = '/usr/www/users/me/reg/';
my $rootpath = './';
die if not -d $rootpath;

my $regtype = 'ch';
my $yeartodelete = '2002';

my $dbh;
#&Conn_to_DB; # Something to initialize $dbh, I hope!

TEST_STUFF: {
   my $sqlite_db_name = 'pm_666415.db';
   unlink $sqlite_db_name or die if -e $sqlite_db_name;

   $dbh = DBI->connect("dbi:SQLite:dbname=$sqlite_db_name") or die;

   # Create data just for testing.
   my @db_setup = grep { /\S/ } split /(?:#.+)?\n/, <<'END_OF_SQL';
CREATE TABLE registrations ( username CHAR(10), regtype CHAR(2), dateadded CHAR(8) );
INSERT INTO  registrations VALUES( 'Able'   , 'ch', '20020101' );
INSERT INTO  registrations VALUES( 'Baker'  , 'ch', '20020101' );
INSERT INTO  registrations VALUES( 'Charlie', 'ch', '20020101' );
INSERT INTO  registrations VALUES( 'Roger'  , 'ch', '20020101' );
INSERT INTO  registrations VALUES( 'Fox'    , 'zz', '20020101' ); # Wrong regtype
INSERT INTO  registrations VALUES( 'Dog'    , 'ch', '20030101' ); # Not in date range
CREATE TABLE joblistings ( username CHAR(10), active CHAR(3) );
INSERT INTO  joblistings   VALUES( 'Able'   , 'yes' ); # Has both yes
INSERT INTO  joblistings   VALUES( 'Able'   , 'no'  ); #      and no
INSERT INTO  joblistings   VALUES( 'Baker'  , 'yes' );
INSERT INTO  joblistings   VALUES( 'Charlie', 'no'  );
INSERT INTO  joblistings   VALUES( 'Roger'  , 'no'  );
INSERT INTO  joblistings   VALUES( 'Fox'    , 'yes' );
INSERT INTO  joblistings   VALUES( 'Dog'    , 'no' );
END_OF_SQL
   # I expect Charlie and Roger to be removed.

   $dbh->do($_) or die for @db_setup;

   mkdir $_ for qw( Able Baker Charlie Roger Fox Dog );
}


my $sql1 = <<'END_OF_SQL';
    SELECT   username
    FROM     registrations
    WHERE    regtype      = ?
    AND      dateadded LIKE ?
END_OF_SQL
my @parms1 = ( $regtype, '%'.$yeartodelete.'%' );

my $jobstable = ( $regtype eq 'ch' ) ? 'joblistings' : 'jobswanted';

my $sql2 = <<"END_OF_SQL";
    SELECT   username
    FROM     $jobstable
    WHERE    active   = 'yes'
    GROUP BY username
END_OF_SQL

# All data will fit into memory, so slurp in:
# 1) an array of all the usernames that were
#    registered in some past year, and
# 2) an array of all the users considered "active".
my @registered_users = get_all_of_a_single_column( $dbh, $sql1, @parms1 );
my @active_users     = get_all_of_a_single_column( $dbh, $sql2 );
$dbh->disconnect;

# make a lookup table of the actives
my %active_user = map { $_ => 1 } @active_users;

#use Data::Dumper;
#print Dumper \@registered_users, \@active_users;

# Only consider inactive users
my @users_to_remove = grep { not $active_user{$_} } @registered_users;

# Generate a list of those users directories.
my @user_dirs_to_remove = map { $rootpath . $_ } @users_to_remove;

# Any dirs already deleted, we remove from the list.
@user_dirs_to_remove = grep { -d $_ } @user_dirs_to_remove;


print "Going to remove directories:\n  ",
      join("\n  ", @user_dirs_to_remove), "\n";

if ($really_delete_the_directories) {
  File::Path::rmtree( \@user_dirs_to_remove, { verbose => 1 } );
}


sub get_all_of_a_single_column {
  my ( $dbh, $sql, @params ) = @_;
  my $all_rows_aref = $dbh->selectall_arrayref( $sql, undef, @params )
    or die;

  # Return a list of the first column of each row.
  return map { $_->[0] } @{ $all_rows_aref };
}