#!/usr/bin/perl

# -- Accounting & Hosting System
# (C) 2002 Dir.bg AD
# (C) 2002 Eugene Jerusalimov, mailto:Eugene@garp.ru


use warnings;
use strict;

use lib $ENV{'CF_SYSTEM_ROOT'}.'/lib/massmailer/perl',$ENV{'CF_SYSTEM_ROOT'}.'/lib/common/perl';

use CLI;

use Data::Dumper;
$Data::Dumper::Sortkeys = 1;

use Massmailer::Config;
use Massmailer::System;


my $dbh = connect_db(DEFAULT_DB_NAME); my $cli = connect_cgp() or die("mail
server connection error: ".$!);

#print Dumper $cli->ListLists('dir.bg');


 if( my $Lists=$cli->ListLists('dir.bg')) {

    foreach my $Listname (@$Lists) {
	print "MAILLIST $Listname\@dir.bg:\n" ;
	
	
	my $CG = $cli->ListSubscribers("$Listname\@dir.bg");
	print "\tCG reported subscribers: ".scalar(@$CG)."\n";

##		if( $Listname eq 'myzyta' ) {
				
			my $maillist_id = ($dbh->selectcol_arrayref("SELECT id FROM maillist WHERE name=?", undef, "$Listname\@dir.bg"))->[0];

				unless( $maillist_id ) {
						print "\tNo in the massmailer, skipped..\n";
						next;
				}
				
				print "\tMaillist id: ".$maillist_id."\n";		
				
				print "\tPhysically in the list: ".($dbh->selectcol_arrayref("SELECT COUNT(*) FROM subscribed_email WHERE id_maillist=$maillist_id"))->[0]."\n";

				print "\tExcluded from the list: ".($dbh->selectcol_arrayref("SELECT COUNT(*) FROM exclude_email_maillist WHERE id_maillist=$maillist_id"))->[0]."\n";
			
				print "\tManaged to the list: ".($dbh->selectcol_arrayref("SELECT COUNT(*) FROM maillist_email WHERE id_maillist=$maillist_id"))->[0]."\n";			
				
				my $sql = <<SQL;
SELECT se.email
FROM subscribed_email se
LEFT JOIN maillist_email me ON me.id_maillist=se.id_maillist AND me.email=se.email
WHERE se.id_maillist=? AND me.email IS NULL				
SQL
				my $i = 0;
				my $sth = $dbh->prepare($sql) or die($dbh->errstr);
				$sth->execute($maillist_id) or die($sth->errstr);
				while( my $row = $sth->fetchrow_arrayref() ) {
						$i++;
						print $row->[0]."\n";
				}
				
				print "\tTotal missed emails: $i\n";
				
##			print Dumper $CG;
##		}

		print "\n";

#  if(my $Settings=$cli->GetList("$Listname\@dir.bg")) {
#    print "The list settings are:\n"; #print " $_ = \"@$Settings{$_}\"\n"
#    #foreach (keys %$Settings);
#    print Dumper $Settings;
#    
#    print "\n";
#  }
#  else {
#    ($cli->isSuccess) ? print "Nothing...\n"
#		     : die "Error: ".$cli->getErrMessage.", quitting";
#  }
	
	
	
	#if( $Listname eq 'user' ) {
	#       $cli->DeleteList("$Listname\@dir.bg")
	#               || die "Error: ".$cli->getErrMessage.", quitting";
	#}
	
    }
    

    
    
	}
  else {
    ($cli->isSuccess) ? print "No lists.\n"
		      : die "Error: ".$cli->getErrMessage.", quitting";
  }
#
#
#exit();
#
#
#my $Maillist = get_maillist_list();
#
#foreach my $id_maillist (keys %$Maillist) {
#
##	print $Maillist->{$id_maillist}->{'name'}."\n";
#
#	next
#		unless check_maillist( $Maillist->{$id_maillist} );
#
#	my $STORED_LIST = $dbh->selectcol_arrayref("SELECT email FROM subscribed_email WHERE id_maillist=?", undef, $id_maillist)
#		or make_die $dbh->errstr;
#
#	update_subscribers($Maillist->{$id_maillist}, $STORED_LIST);
#}
#
#$cli->Logout();
#
#
#sub get_maillist_list
#{
#
#	my $SQL = <<SQL;
#SELECT
#	m.*,e.email
#FROM
#	maillist m,
#	email e
#WHERE
#	e.id=m.id_email
#SQL
#
#	return $dbh->selectall_hashref($SQL, 'id')
#		or make_die $dbh->errstr;
#}
#
#
#sub check_maillist
#{
#	my $Maillist = shift or die "No maillist name given!";
#
#	my $mailbox = $Maillist->{'email'};
#
#	return undef
#		unless $cli->GetAccountSettings($mailbox);
#
#	my $List = $cli->GetAccountLists($mailbox)
#		or die cli_err();
#
#	return undef
#		unless defined $List->{__norm($Maillist->{'name'})};
#
#	return 1;
#}
#
#
#sub _insert_into_exclude_list
#{
#	my $id_maillist = shift or die "No id_maillist given!";
#	my $email = shift or die "No email given!";
#
#	## first check for email presence in exlude_email and add if not present
#	my $id_email = $dbh->selectrow_arrayref("SELECT id FROM exclude_email WHERE email=?", undef, $email);
#
#	unless( $id_email && $id_email->[0] ) {
#		$dbh->do("INSERT INTO exclude_email (email) VALUES (?)", undef, $email)
#			or make_die $dbh->errstr;
#		$id_email = ($dbh->selectrow_array("SELECT LAST_INSERT_ID()"))[0];
#	}
#	else {
#		$id_email = $id_email->[0];
#		print "Found id=$id_email for '$email'\n";
#	}
#
#	## second insert plan maillist->email
#	$dbh->do("INSERT INTO exclude_email_maillist (id_exclude_email, id_maillist) VALUES (?,?)", undef, $id_email, $id_maillist)
#		or make_die $dbh->errstr;
#
#	return undef;
#}
#
#
#sub _remove_from_exclude_list
#{
#	my $id_maillist = shift or die "No id_maillist given!";
#	my $email = shift or die "No email given!";
#
#	## first check for email presence in exlude_email and add if not present
#	my $id_email = $dbh->selectrow_arrayref("SELECT id FROM exclude_email WHERE email=?", undef, $email);
#
#	return undef
#		unless( $id_email && $id_email->[0] );
#
#	my $ids = $dbh->selectcol_arrayref("SELECT id_maillist FROM exclude_email_maillist WHERE id_exclude_email=?", undef, $id_email)
#		or make_die $dbh->errstr;
#
#	$dbh->do("DELETE FROM exclude_email_maillist WHERE id_exclude_email=? AND id_maillist=?", undef, $id_email, $id_maillist)
#		or make_die $dbh->errstr;
#
#	## remove email from exlude if no
#	scalar(@$ids)<2 && ( $dbh->do("DELETE FROM exclude_email WHERE id=?", undef, $id_email) or make_die $dbh->errstr );
#
#	return undef;
#}
#
#
#sub update_subscribers
#{
#	my $Maillist = shift or die "No maillist name given!";
#	my $DB = shift or die "No emails list given!";
#
#	my $CG = $cli->ListSubscribers($Maillist->{'name'});
#
#	my ($SUBSCR, $UNSUBSCR) = _build_subscribers_diff_lists($CG, $DB);
#
#	$dbh->do("LOCK TABLES subscribed_email WRITE, exclude_email WRITE, exclude_email_maillist WRITE")
#		or die $dbh->errstr;
#
#	foreach my $email (@$SUBSCR) {
#		## new subscriber, from @ -> put in 'subscribed_email' table
#		## and check for presence in exclude and remove from exclude (if presents)
#
#		$dbh->do("INSERT INTO subscribed_email (id_maillist, email) VALUES (?,?)", undef, $Maillist->{'id'}, $email)
#			or make_die $dbh->errstr;
#
#		_remove_from_exclude_list($Maillist->{'id'}, $email);
#
#print "new subscriber $email for '$Maillist->{name}' detected\n";
#	}
#
#	foreach my $email (@$UNSUBSCR) {
#		## manually @ unsubscribed email
#		## put in exclude list
#		## remove from 'subscribed_email' table
#
#		$dbh->do("DELETE FROM subscribed_email WHERE id_maillist=? AND email=?", undef, $Maillist->{'id'}, $email)
#			or make_die $dbh->errstr;
#
#		_insert_into_exclude_list($Maillist->{'id'}, $email);
#
#print "to be unsubscribed $email from '$Maillist->{name}'\n";
#	}
#
#	$dbh->do("UNLOCK TABLES");
#
#	return undef;
#}
#
#
### args order is different!
#sub _build_subscribers_diff_lists
#{
#	my $N = shift or die "No new emails given!";
#	my $E = shift or die "No existing emails given!";
#
#	my %N_hash = map { $_=>1 } @$N;
#	my %E_hash = map { $_=>1 } @$E;
#
#	my (@NEW,@DEL) = ((),());
#
#	## search for new one
#	foreach my $email ( keys %N_hash ) {
#		push @NEW, $email
#			unless( defined $E_hash{$email} );
#	}
#
#	## search for delete
#	foreach my $email ( keys %E_hash ) {
#		push @DEL, $email
#			unless( defined $N_hash{$email} );
#	}
#
#	return (\@NEW, \@DEL);
#}
#
#
#sub store_error
#{
#	my $id_maillist = shift;
#	my $error = shift;
#
#	warn "ERROR: $id_maillist - $error\n";
#
##	$dbh->do("UPDATE maillist SET state=3, output=? WHERE id=?", undef, $error, $id_maillist);
#
#	return undef;
#}
#
#
#sub __norm
#{
#	return (split /\@/, shift)[0];
#}
#
#
#sub cli_err
#{
#	my $err = shift;
#
#	return "CGP: ".($err ? $err." " : '').$cli->getErrMessage." (".$cli->getErrCommand.")";
#}
