#!/usr/bin/env perl
use strict;
use warnings;
use Getopt::Long;
use Net::FTP;
use LWP::Simple;
use Spreadsheet::WriteExcel;
use Spreadsheet::ParseExcel;
use Term::ReadPassword;
use Regexp::Common qw/URI/;
use File::Basename;

#Parse the command line

GetOptions(
   'ftp=s' => \my $ftp_server,
   'url=s' => \my $url,
   'user=s' => \my $user,
   'debug' => \my $debug,
) or die "Invalid Parameters\n";

#make sure all arguments were included

die "No FTP server provided\n" unless defined $ftp_server;
die "No URL provided\n" unless defined $url;
die "No Username provided\n" unless defined $user; 

#This subroutine enables me to not have to keep saying "if $debug" over and over
sub debug {
   print @_ if $debug;
}

#Check for valid URL
die "Invalid URL\n"
  unless $url =~ /$RE{URI}{HTTP}{-keep}/;

#Get the path to the file from the URL, and then grab just the file name
my $path = $5;
my $filename = basename($path);

debug "Attempting to retrieve $filename from $url\n";
getstore($url, $filename) or die $!;

debug "$filename retrieved, now attempting to open with Spreadsheet::ParseExcel\n";
my $dir_xls = Spreadsheet::ParseExcel->new()->parse($filename) or die "Could not parse $filename as an XLS file: $!\n";
my $wksht = $dir_xls->worksheet(0); #Deal with the first worksheet in the file.

#Find the row range for this worksheet:
my $max_row = $wksht->row_range();

debug "Looping from rows 0 through $max_row to find directories\n";
my @dirs;
for my $row (0..$max_row) {
   my $cell = $wksht->get_cell($row, 0);
   next unless $cell;
   my $dir = $cell->value();
   debug "Found directory '$dir' in row $row\n";
   push @dirs, $dir;
}

debug "Found " . @dirs . " directories in $filename\n";

#Attempt to create a new XLS file, and add worksheet to it.
my $file_xls = Spreadsheet::WriteExcel->new("files.xls")
  or die $!;
my $new_wksht = $file_xls->add_worksheet();

debug "files.xls created.  Now parsing through directories, and writing to new XLS\n";
for my $col (0..$#dirs) {
   $new_wksht->write(0, $col, $dirs[$col]);
   opendir my $dh, $dirs[$col] or die "Cannot open directory $dirs[$col]: $!\n";
   my $row = 1;
   while (my $file = readdir($dh)) {
      next unless -f "$dirs[$col]/$file";
      $new_wksht->write($row, $col, $file);
      $row++;
   }
}

debug "All files written, closing new XLS file\n";
$file_xls->close() or die "Error closing files.xls: $!\n";

debug "Now getting password from the user\n";
my $password = read_password("Enter the password for $user\@$ftp_server:");

debug "Logging in to $ftp_server with username $user\n";
my $ftp = Net::FTP->new($ftp_server) or die "Cannot connect to $ftp_server: $!\n";
$ftp->login($user, $password) or die "Cannot login to $ftp_server with $user and password read in\n";

debug "Now attempting to upload files.xls\n";
$ftp->binary();
$ftp->put("files.xls") or die "Failed to upload files.xls: $!\n";

debug "Upload succeeded, program ended\n";