#!/usr/bin/perl

use strict;
use warnings;
use MIME::Lite;
use HTML::Template;
use DBI;
use Cwd qw( abs_path );
use File::Basename qw( dirname );
use Config::Properties;
use Getopt::Long;
use Term::ANSIColor;

if (scalar $#ARGV == -1) { &usage(); }
my %props;
&properties();

my ($mail,$list,$remove,$add);

GetOptions(
    'mail' => \$mail,
    'list' => \$list,
    'remove' => \$remove,
    'add' => \$add,
) or die &usage();


my %connect = (
    'database'=>$props{MySQL_Database},
    'host'=>$props{MySQL_Host},
    'port'=>$props{MySQL_Port},
    'user'=>$props{MySQL_User},
    'password'=>$props{MySQL_Password},
    'file'=>$props{HTML_File},
);

my $dsn = "DBI:mysql:database=$connect{database};host=$connect{host};port=$connect{port}";
my $dbh = DBI->connect( $dsn, $connect{user}, $connect{password} ) or die "Failed to connect to the database: " . DBI->errstr;

my $sql = qq|SELECT `emailAddress`, `firstName` FROM $connect{database}.`users` WHERE `status` = (SELECT `Id` FROM $connect{database}.`assc_status` WHERE `Status` = 'FULL') AND `blacklisted` = (SELECT `Id` FROM $connect{database}.`assc_blacklist` WHERE `Blacklisted` = 'No') AND `unsubscribed` = (SELECT `Id` FROM $connect{database}.`assc_unsubscribed` WHERE `Status` = 'No')|;

my $sql_remove = qq|UPDATE $connect{database}.`users` SET `unsubscribed` = (SELECT `Id` FROM $connect{database}.`assc_unsubscribed` WHERE `Status` = 'Yes') WHERE `emailAddress` = ?|;

my $sql_add = qq|UPDATE $connect{database}.`users` SET `unsubscribed` = (SELECT `Id` FROM $connect{database}.`assc_unsubscribed` WHERE `Status` = 'No') WHERE `emailAddress` = ?|;

if($mail) {
    print "This option will email all subscribed users.  Are you sure you would like to continue? : (yes|no) ";
    chomp(my $res = <>);
    if ($res !~ /^yes$/) { 
        print "Closing Script\n";
        exit;
    }
    
    my $sth = $dbh->prepare($sql);
    $sth->execute or die "Failed to execute query:$!";

    my $file = HTML::Template->new(filename => $connect{file});

    while (my $result = $sth->fetchrow_hashref) {
        $file->param(USER_NAME => $result->{firstName});
        &sendMail($result->{emailAddress},$file->output);
    }
    $dbh->disconnect;
    exit;
}

if($list) {
    my $sth = $dbh->prepare($sql);
    $sth->execute or die "Failed to execute query:$!";
    
    while (my $result = $sth->fetchrow_hashref) {
        print "$result->{emailAddress}\n";
    }
    $dbh->disconnect;
    exit;
}

if($remove) {
    my $ans = 1;
    while($ans) {
        print "Enter email address you would like to remove from list : ";
        chomp(my $em = <>);
        my $sth = $dbh->prepare($sql_remove);
        $sth->execute($em) or die "Failed to execute query:$!";
        print "$em has been unsubscribed!\n\nWould you like to remove another user? : (yes|no) ";
        chomp(my $res = <>);
        if (lc $res !~ /^yes$/ ) { $ans = 0; }
    }
    $dbh->disconnect;
    exit;
}

if($add) {
    my $ans = 1;
    while($ans) {
        print "Enter email address you would like to add to list : ";
        chomp(my $em = <>);
        my $sth = $dbh->prepare($sql_add);
        $sth->execute($em) or die "Failed to execute query:$!";
        print "$em has been subscribed!\n\nWould you like to add another user? : (yes|no) ";
        chomp(my $res = <>);
        if (lc $res !~ /^yes$/ ) { $ans = 0; }
    }
    $dbh->disconnect;
    exit;
}
    
sub sendMail{
    my $subject = "<subject title goes here>";
    my $to = $_[0];
    my $body = $_[1];
    
    my $msg = MIME::Lite->new(
        From    => '<someemail@somewhere.com',
        To      => $to,
        Subject => $subject,
        Type    => 'text/html',
        Data    => $body,
    ) or die "Error creating multipart container: $!\n";

    $msg->send or die "Failed To Send!: $!\n";
    print "Message sent!\n";
}

sub properties {
    open my $fh, '<', dirname(abs_path($0))."/mailer.props" || warn "Failed to open :$!";
    my $properties = Config::Properties->new();
    $properties->load($fh);

    %props = $properties->properties;
    return;
}

sub usage {
    print color("yellow"), "\n$0  Usage :\n", color("reset");
    my $message = <<EOF;

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+                                                                                                                                                    +
+   --mail        -:-     Send weekly email to all users subscribed                                                                                  +
+                                                                                                                                                    +
+   --list        -:-     List subscribed users by email                                                                                             +
+                                                                                                                                                    +
+   --remove      -:-     Unsubscribe user from email, requires you to enter the email address                                                       +
+                                                                                                                                                    +
+   --add         -:-     Add user to subscribed list                                                                                                +
+                                                                                                                                                    +
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


EOF

    print $message;
    exit;
}