#!/usr/bin/perl
#/* ========================================
# * ███╗   ███╗██████╗ ██╗ █████╗ ██╗
# * ████╗ ████║██╔══██╗██║██╔══██╗██║
# * ██╔████╔██║██║  ██║██║███████║██║
# * ██║╚██╔╝██║██║  ██║██║██╔══██║██║
# * ██║ ╚═╝ ██║██████╔╝██║██║  ██║███████╗
# * ╚═╝     ╚═╝╚═════╝ ╚═╝╚═╝  ╚═╝╚══════╝
# * ========================================
# * mDial - Omni-Channel Contact Centre Suite.
# * Initially Written by Martin McCarthy.
# * Contributions welcome.
# * Active: 2020 - 2023.
# *
# * This software is licensed under AGPLv2.
# * You can find more information here;
# * https://www.gnu.org/licenses/agpl-3.0.en.html
# * A copy of the license is also shipped with this build.
# *
# * Important note: this software is provided to you free of charge.
# * If you paid for this software, you were ripped off.
# *
# * This project is a fork of the awesome FOSS project, ViCiDial.
# * ViCiDial is copyrighted by Matt Florell and the ViCiDial Group
# * under the AGPLv2 license.
# *
# * You can find out more about ViCiDial;
# * Web: https://www.vicidial.com/
# * Email: Matt Florell <vicidial@gmail.com>
# * IRC: Libera.Chat - ##vicidial
# *
# * Bug reports, feature requests and patches welcome!
# * ======================================== */
if ( length( $ARGV[0] ) > 1 ) {
    $i = 0;
    while ( $#ARGV >= $i ) {
        $args = "$args $ARGV[$i]";
        $i++;
    }
    if ( $args =~ /--help/i ) {
        print "allowed run time options:\n";
        print
"  [--without-campaign-dnc] = will skip analyzing the campaign dnc lists\n";
        print
"  [--campaign-dnc-only=X] = will only run on one campaign DNC list\n";
        print
"  [--remove-x-or-less=X] = remove phone numbers in the dnc list that are X digits or less <=\n";
        print
"  [--remove-x-or-more=X] = remove phone numbers in the dnc list that are X digits or more >=\n";
        print
"  [--remove-nondigits] = remove phone numbers that have non-digits in them\n";
        print
"  [--protect-areacodes] = protect areacode entries with wildcards from being deleted\n";
        print "  [--test] = test\n";
        print "  [--quiet] = quiet\n";
        print "  [--debug] = verbose debug messages\n";
        print "  [--debugX] = extra verbose debug messages\n\n";
        exit;
    }
    else {
        if ( $args =~ /--quiet/i ) {
            $Q = 1;
        }
        if ( $args =~ /--without-campaign-dnc/i ) {
            $without_campaign_dnc = 1;
        }
        if ( $args =~ /--campaign-dnc-only=/i ) {
            @data_in     = split( /--campaign-dnc-only=/, $args );
            $CLIcampaign = $data_in[1];
            $CLIcampaign =~ s/ .*$//gi;
            if ( $CLIcampaign =~ /---/ ) {
                $CLIcampaign =~ s/---/','/gi;
            }
            if ( $DB > 0 ) {
                print "\n-----CAMPAIGN DNC ONLY: $CLIcampaign -----\n\n";
            }
        }
        else { $CLIcampaign = ''; }
        if ( $args =~ /--remove-x-or-less=/i ) {
            @data_in       = split( /--remove-x-or-less=/, $args );
            $CLIremoveless = $data_in[1];
            $CLIremoveless =~ s/ .*$//gi;
            $CLIremoveless =~ s/\D//gi;
            if ( $DB > 0 ) {
                print
                  "\n-----REMOVE X DIGITS OR LESS: $CLIremoveless -----\n\n";
            }
        }
        else { $CLIremoveless = ''; }
        if ( $args =~ /--remove-x-or-more=/i ) {
            @data_in       = split( /--remove-x-or-more=/, $args );
            $CLIremovemore = $data_in[1];
            $CLIremovemore =~ s/ .*$//gi;
            $CLIremovemore =~ s/\D//gi;
            if ( $DB > 0 ) {
                print
                  "\n-----REMOVE X DIGITS OR MORE: $CLIremovemore -----\n\n";
            }
        }
        else { $CLIremovemore = ''; }
        if ( $args =~ /--remove-nondigits/i ) {
            $remove_nondigits = 1;
        }
        if ( $args =~ /--protect-areacodes/i ) {
            $protect_areacodes = 1;
        }
        if ( $args =~ /--debug/i ) {
            $DB = 1
              ; # Debug flag, set to 0 for no debug messages, On an active system this will generate hundreds of lines of output per minute
        }
        if ( $args =~ /--debugX/i ) {
            $DBX = 1
              ; # Debug flag, set to 0 for no debug messages, On an active system this will generate hundreds of lines of output per minute
        }
        if ( $args =~ /--test/i ) {
            $TEST = 1;
            $T    = 1;
        }
    }
}
else {
    print "no command line options set, exiting...\n";
    exit;
}
$PATHconf = '/etc/astguiclient.conf';
open( conf, "$PATHconf" ) || die "can't open $PATHconf: $!\n";
@conf = <conf>;
close(conf);
$i = 0;
foreach (@conf) {
    $line = $conf[$i];
    $line =~ s/ |>|\n|\r|\t|\#.*|;.*//gi;
    if ( ( $line =~ /^PATHhome/ ) && ( $CLIhome < 1 ) ) {
        $PATHhome = $line;
        $PATHhome =~ s/.*=//gi;
    }
    if ( ( $line =~ /^PATHlogs/ ) && ( $CLIlogs < 1 ) ) {
        $PATHlogs = $line;
        $PATHlogs =~ s/.*=//gi;
    }
    if ( ( $line =~ /^PATHagi/ ) && ( $CLIagi < 1 ) ) {
        $PATHagi = $line;
        $PATHagi =~ s/.*=//gi;
    }
    if ( ( $line =~ /^PATHweb/ ) && ( $CLIweb < 1 ) ) {
        $PATHweb = $line;
        $PATHweb =~ s/.*=//gi;
    }
    if ( ( $line =~ /^PATHsounds/ ) && ( $CLIsounds < 1 ) ) {
        $PATHsounds = $line;
        $PATHsounds =~ s/.*=//gi;
    }
    if ( ( $line =~ /^PATHmonitor/ ) && ( $CLImonitor < 1 ) ) {
        $PATHmonitor = $line;
        $PATHmonitor =~ s/.*=//gi;
    }
    if ( ( $line =~ /^VARserver_ip/ ) && ( $CLIserver_ip < 1 ) ) {
        $VARserver_ip = $line;
        $VARserver_ip =~ s/.*=//gi;
    }
    if ( ( $line =~ /^VARDB_server/ ) && ( $CLIDB_server < 1 ) ) {
        $VARDB_server = $line;
        $VARDB_server =~ s/.*=//gi;
    }
    if ( ( $line =~ /^VARDB_database/ ) && ( $CLIDB_database < 1 ) ) {
        $VARDB_database = $line;
        $VARDB_database =~ s/.*=//gi;
    }
    if ( ( $line =~ /^VARDB_user/ ) && ( $CLIDB_user < 1 ) ) {
        $VARDB_user = $line;
        $VARDB_user =~ s/.*=//gi;
    }
    if ( ( $line =~ /^VARDB_pass/ ) && ( $CLIDB_pass < 1 ) ) {
        $VARDB_pass = $line;
        $VARDB_pass =~ s/.*=//gi;
    }
    if ( ( $line =~ /^VARDB_port/ ) && ( $CLIDB_port < 1 ) ) {
        $VARDB_port = $line;
        $VARDB_port =~ s/.*=//gi;
    }
    $i++;
}
$server_ip = $VARserver_ip;    # Asterisk server IP
if ( !$VARDB_port ) { $VARDB_port = '3306'; }
use DBI;
$dbhA = DBI->connect( "DBI:mysql:$VARDB_database:$VARDB_server:$VARDB_port",
    "$VARDB_user", "$VARDB_pass" )
  or die "Couldn't connect to database: " . DBI->errstr;
( $sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $isdst ) =
  localtime(time);
$deleted = 0;
$MT[0]   = '';
$stmtA   = "SELECT count(*) from vicidial_dnc;";
$sthA    = $dbhA->prepare($stmtA) or die "preparing: ", $dbhA->errstr;
$sthA->execute or die "executing: $stmtA ", $dbhA->errstr;
$sthArowsDNC = $sthA->rows;

if ( $sthArowsDNC > 0 ) {
    @aryA     = $sthA->fetchrow_array;
    $DNCcount = $aryA[0];
}
$sthA->finish();
if ($DB) { print "\nTotal System Internal DNC entries: |$DNCcount|\n"; }
if ($DB) { print "\n"; }
if ($DB) { print "Total Campaign DNC entries:\n"; }
$stmtA =
"SELECT count(*),campaign_id from vicidial_campaign_dnc group by campaign_id order by campaign_id;";
$sthA = $dbhA->prepare($stmtA) or die "preparing: ",        $dbhA->errstr;
$sthA->execute                 or die "executing: $stmtA ", $dbhA->errstr;
$sthArowsCDNC = $sthA->rows;
$rec_count    = 0;

while ( $sthArowsCDNC > $rec_count ) {
    @aryA    = $sthA->fetchrow_array;
    $aryA[0] = sprintf( "%10s", $aryA[0] );
    $aryA[1] = sprintf( "%10s", $aryA[1] );
    if ($DB) { print "$aryA[1]: $aryA[0]\n"; }
    $rec_count++;
}
$sthA->finish();
if (   ( length($CLIremoveless) > 0 )
    or ( length($CLIremovemore) > 0 )
    or ( $remove_nondigits > 0 ) )
{
    $removelessSQL        = '';
    $removemoreSQL        = '';
    $remove_nondigitsSQL  = '';
    $protect_areacodesSQL = '';
    if ( length($CLIremoveless) > 0 ) {
        $removelessSQL = "(char_length(phone_number) <= $CLIremoveless)";
    }
    if ( length($CLIremovemore) > 0 ) {
        if ( length($removelessSQL) > 1 ) { $removemoreSQL = 'or '; }
        $removemoreSQL .= "(char_length(phone_number) >= $CLIremovemore)";
    }
    if ( $remove_nondigits > 0 ) {
        if ( ( length($removelessSQL) > 1 ) or ( length($removemoreSQL) > 1 ) )
        {
            $remove_nondigitsSQL = 'or ';
        }
        $remove_nondigitsSQL .=
"( (phone_number LIKE \"% %\") or (phone_number LIKE \"%*%\") or (phone_number LIKE \"%#%\") or (phone_number LIKE \"%a%\") or (phone_number LIKE \"%b%\") or (phone_number LIKE \"%c%\") or (phone_number LIKE \"%d%\") or (phone_number LIKE \"%e%\") or (phone_number LIKE \"%f%\") or (phone_number LIKE \"%g%\") or (phone_number LIKE \"%h%\") or (phone_number LIKE \"%i%\") or (phone_number LIKE \"%j%\") or (phone_number LIKE \"%k%\") or (phone_number LIKE \"%l%\") or (phone_number LIKE \"%m%\") or (phone_number LIKE \"%n%\") or (phone_number LIKE \"%o%\") or (phone_number LIKE \"%p%\") or (phone_number LIKE \"%q%\") or (phone_number LIKE \"%r%\") or (phone_number LIKE \"%s%\") or (phone_number LIKE \"%t%\") or (phone_number LIKE \"%u%\") or (phone_number LIKE \"%v%\") or (phone_number LIKE \"%q%\") or (phone_number LIKE \"%x%\") or (phone_number LIKE \"%y%\") or (phone_number LIKE \"%z%\") or (phone_number LIKE \"%.%\") or (phone_number LIKE \"%,%\") )";
    }
    if ( $protect_areacodes > 0 ) {
        if (   ( length($removelessSQL) > 1 )
            or ( length($removemoreSQL) > 1 )
            or ( length($remove_nondigitsSQL) > 1 ) )
        {
            $protect_areacodesSQL = "and (phone_number NOT LIKE \"%XXXXXXX\")";
        }
    }
    if ( length($CLIcampaign) < 1 ) {
        $stmtA =
"SELECT phone_number from vicidial_dnc where $removelessSQL $removemoreSQL $remove_nondigitsSQL $protect_areacodesSQL limit 100000000;";
        $sthA = $dbhA->prepare($stmtA) or die "preparing: ", $dbhA->errstr;
        $sthA->execute or die "executing: $stmtA ",          $dbhA->errstr;
        $sthArowsCT = $sthA->rows;
        if ($DB) { print "|$sthArowsCT|$stmtA|\n"; }
        $rec_count = 0;
        while ( $sthArowsCT > $rec_count ) {
            @aryA = $sthA->fetchrow_array;
            $phone_numbers[$rec_count] = $aryA[0];
            $rec_count++;
        }
        $sthA->finish();
        $rec_count = 0;
        while ( $sthArowsCT > $rec_count ) {
            $affected_rows  = 0;
            $affected_rowsA = 0;
            $stmtA =
"DELETE from vicidial_dnc where phone_number='$phone_numbers[$rec_count]';";
            if ( !$T ) { $affected_rows = $dbhA->do($stmtA); }
            if ($DBX) {
                print STDERR "\n|$affected_rows deleted|   |$stmtA|\n";
            }
            $deleted++;
            if ( $affected_rows > 0 ) {
                $stmtA =
"INSERT INTO vicidial_dnc_log SET phone_number='$phone_numbers[$rec_count]', campaign_id='-SYSINT-', action='delete', action_date=NOW(), user='CLIFLTR';";
                if ( !$T ) { $affected_rowsA = $dbhA->do($stmtA); }
                if ($DBX) {
                    print STDERR "\n|$affected_rowsA log|   |$stmtA|\n";
                }
            }
            $rec_count++;
        }
    }
    if ( ( length($CLIcampaign) > 0 ) or ( $without_campaign_dnc < 1 ) ) {
        $CLIcampaignSQL = '';
        if ( length($CLIcampaign) > 0 ) {
            $CLIcampaignSQL = "and campaign_id IN('$CLIcampaign')";
        }
        $stmtA =
"SELECT phone_number,campaign_id from vicidial_campaign_dnc where $removelessSQL $removemoreSQL $remove_nondigitsSQL $protect_areacodesSQL $CLIcampaignSQL limit 100000000;";
        $sthA = $dbhA->prepare($stmtA) or die "preparing: ", $dbhA->errstr;
        $sthA->execute or die "executing: $stmtA ",          $dbhA->errstr;
        $sthArowsCT = $sthA->rows;
        if ($DB) { print "|$sthArowsCT|$stmtA|\n"; }
        $rec_count = 0;
        while ( $sthArowsCT > $rec_count ) {
            @aryA                       = $sthA->fetchrow_array;
            $Cphone_numbers[$rec_count] = $aryA[0];
            $Ccampaigns[$rec_count]     = $aryA[1];
            $rec_count++;
        }
        $sthA->finish();
        $rec_count = 0;
        while ( $sthArowsCT > $rec_count ) {
            $affected_rows  = 0;
            $affected_rowsA = 0;
            $stmtA =
"DELETE from vicidial_campaign_dnc where phone_number='$Cphone_numbers[$rec_count]' and campaign_id='$Ccampaigns[$rec_count]';";
            if ( !$T ) { $affected_rows = $dbhA->do($stmtA); }
            if ($DBX) {
                print STDERR "\n|$affected_rows deleted|   |$stmtA|\n";
            }
            $deleted++;
            if ( $affected_rows > 0 ) {
                $stmtA =
"INSERT INTO vicidial_dnc_log SET phone_number='$Cphone_numbers[$rec_count]', campaign_id='$Ccampaigns[$rec_count]', action='delete', action_date=NOW(), user='CLIFLTR';";
                if ( !$T ) { $affected_rowsA = $dbhA->do($stmtA); }
                if ($DBX) {
                    print STDERR "\n|$affected_rowsA log|   |$stmtA|\n";
                }
            }
            $rec_count++;
        }
    }
}
else {
    if ( $DB > 0 ) {
        print "\n no dnc filter set: |$CLIremoveless|$CLIremovemore|\n\n";
    }
}
exit;