#!/usr/bin/env perl
#
# sakai-resource-files - list the files in Sakai Resources
#
# $Id$

use warnings;
use strict;

use Getopt::Std;
use Text::CSV_XS;
use DBI;

my %opt;
getopts( '?hnq:t:uv', \%opt );

HELP_MESSAGE() if ( $opt{h} || $opt{'?'} );

sub HELP_MESSAGE
{
  print STDERR <<ZZ;

Usage: $0 [options]

to list the Sakai resource files in CSV format,
ordered by increasing file size.
Each row is a file.  The columns denote:

  + the file size
  + the file path (on disk)
  + the resource ID (from which the WevDAV filename is derived)
  + the resource type
  + the name of the site the resource is part of

Options:

  -q char  The quote character to use.  Use '' to not quote at all.
  -t sep   The column separator character.
  -u       Never print the same output row twice.

  -n       Don't actually execute the query.
  -v       Print the query.

Environment variables:

  SAKAI_PROPERTIES  The Sakai properties file to read for defaults
                    (default: /var/lib/tomcat7/sakai/local.properties).
  The values in there can be overriden using

  SAKAI_USER      The MySQL user to connect as.
  SAKAI_HOST      The host to connect to.
  SAKAI_PASSWORD  The MySQL password to use.
  SAKAI_DATABASE  The MySQL database to use.

You can feed the output to ./sakai-resource-file-properties
to get more information on the files.

ZZ
  exit(0);
}

sub if_opt
{
  my ( $name, $o ) = @_;
  my $opt = $opt{$o};

  !defined($opt) ? ()
   : ( $opt eq '' ) ? ( $name => undef )
   :                  ( $name => $opt );
}

my $csvh = new Text::CSV_XS(
  {
    eol => $/,
    if_opt( 'sep_char',   't' ),
    if_opt( 'quote_char', 'q' )
  }
) or die "cannot create CSV writer: $@\n";

binmode STDOUT, ':utf8';

sub grep_in_file
{
  my ( $rx, $file ) = @_ or return;

  defined($file) or return;
  open( my $fh, '<', $file ) or return;

  while (<$fh>)
  {
    #/^$rx[^=]*=\s*(.*\S)/ and warn "$rx is $1 in $file\n";
    /^$rx[^=]*=\s*(.*\S)/ and return $1;
  }

  close($fh);

  #warn "$rx not found in $file\n";

  return;
}

my $local_properties = $ENV{SAKAI_PROPERTIES}
 // '/var/lib/tomcat7/sakai/local.properties';

my $username = $ENV{SAKAI_USER} // grep_in_file( 'username', $local_properties )
 // 'sakai';
my $password = $ENV{SAKAI_PASSWORD}
 // grep_in_file( 'password', $local_properties ) // 'Daisen kofun';
my $url = grep_in_file( 'url', $local_properties )
 // 'jdbc:mysql://127.0.0.1:3306/sakai293';
my $hostname = $ENV{SAKAI_HOST} // ( $url =~ m#://(\d.]+)# ? $1 : 'localhost' );
my $database = $ENV{SAKAI_DATABASE}
 // ( $url =~ m#.*/([^?/]++)# ? $1 : 'sakai293' );

my $dbh = DBI->connect( "dbi:mysql:$database;hostname=$hostname",
  $username, $password,
  { RaiseError => 1, AutoCommit => 0, mysql_enable_utf8 => 1 } )
 or die "cannot connect to Sakai database: $@\n";

sub trimming_leading
{
  sprintf( 'TRIM(LEADING "%s" FROM %s) AS %s', $_[0], $_[1], $_[1], $_[1] );
}

my @columns = (
  'file_size',
  trimming_leading( '/',                               'file_path' ),
  trimming_leading( '/',                               'resource_id' ),
  trimming_leading( 'org.sakaiproject.content.types.', 'resource_type_id' ),
  'title AS site_name'
);

my $select = join( "\n",
  'SELECT' . ( $opt{u} ? ' DISTINCT ' : ' ' ) . join( ', ', @columns ),
  'FROM content_resource',
'LEFT JOIN sakai_site ON site_id = substring_index(substring_index(resource_id, "/", 3), "/", -1)',
  'ORDER BY file_size' );

my $sth = $dbh->prepare( uc( $select =~ s/\n/ /gr ) )
 or die "cannot prepare SQL query: $@\n";

if ( $opt{v} )
{
  warn "$select\n";
}

if ( !$opt{n} )
{
  $sth->execute()
   or die "cannot execute SQL query: $@\n";

  $csvh->print( *STDOUT, $sth->{NAME} );                          # the header
  $csvh->print( *STDOUT, $_ ) for @{ $sth->fetchall_arrayref() }; # the contents
  #$csvh->print( *STDOUT, [ as_utf8(@$_) ] ) for @{ $sth->fetchall_arrayref() };   # the contents
}

$sth->finish();
$dbh->disconnect()