package MOJO::List::PostgreSQL; 

use DBI;
	
my $database         = $SQL_PARAMS{database};
my $dbserver         = $SQL_PARAMS{dbserver};    	  
my $port             = $SQL_PARAMS{port};     	  
my $dbtype           = $SQL_PARAMS{dbtype} || 'Pg';        
my $user             = $SQL_PARAMS{user};         
my $pass             = $SQL_PARAMS{pass};
my $subscriber_table = $SQL_PARAMS{subscriber_table};         
my $email_id         = $SQL_PARAMS{id_column} || 'email_id';


use lib './'; 
use lib '../'; 
use lib '../../'; 
use MOJO::Config; 
use Fcntl qw(
O_WRONLY 
O_TRUNC 
O_CREAT 
O_CREAT 
O_RDWR
O_RDONLY
LOCK_EX
LOCK_SH 
LOCK_NB); 

use MOJO::Guts;
use MOJO::Log;

my $log = new MOJO::Log;
use strict; 
my %fields; 

sub new {
	my $class = shift;
	my %args = (-List => undef, 
				-Path => $FILES, 
				@_); 
	   my $self = {};			
       bless $self, $class;
	   $self->_init(%args); 
	   return $self;
}

sub _init  { 
    my $self = shift; 
    my %args = @_; 
    %fields = %args; 
    $self->{fields} = {%args};
    $self->{list} = $args{-List};
    $self->{list_info} = open_database(-List=> $self->{list});
    $self->connectdb;
}



sub open_email_list { 
	my $self = shift; 
	my %args = (-Type      => 'list',
				-As_Ref    => 0, 
				-Sorted    => 1,
				@_);
	my $list = $fields{-List} || undef; 
	if($list){
		my @list = ();    
		my $sth = $self->{dbh}->prepare("SELECT email 
						  	             FROM " . $subscriber_table . 
						  	           " WHERE list = ? AND list_type = ? AND list_status = 1");    
		   $sth->execute($fields{-List}, $args{-Type}) or die "cannot do statment (for open_email_list)! $DBI::errstr\n";   
		   while((my $email) = $sth->fetchrow_array){ 
				push(@list, $email); 
			}
			$sth->finish;
		if($args{-As_Ref} eq "1"){ 
		return \@list; 
		}else{ 
		return @list; 
		}
	}else{ 
	return undef;
	}
}


sub open_list_handle { my $self = shift;}
sub sort_email_list {  my $self = shift;}

sub search_email_list { 

	my $self = shift; 
	
	my %args = (
	-Keyword  => undef,
	-Method   => undef, 
	-Type     => 'list',
	@_
	);
	my @matches; 
	my $domain_regex = "(.*)\@(.*)"; 
	my $found = 0; 
	
	my $method  = $args{-Method} || "standard"; 
	my $keyword = $args{-Keyword};
	   $keyword = quotemeta($keyword);  
	
	if($fields{-List}){ 

	
		my $sth = $self->{dbh}->prepare("  SELECT email  
		                                   FROM " . $subscriber_table .  
		                                 " WHERE list = ? AND list_type = ? AND list_status = 1"); 
		                                    
		   $sth->execute($fields{-List}, $args{-Type}) or die "cannot do statment (for search_email_list)! $DBI::errstr\n";   
	
		if(defined($keyword) && $keyword ne ""){
			if($method eq "domain"){ 
				while((my $email) = $sth->fetchrow_array){ 
					if($email =~ m/$domain_regex$keyword$/i){ 
						print"<input type=\"checkbox\" name=\"address\" value=\"$email\">&nbsp;&nbsp; $email<br>\n";
						$found++;
					}
				}
			}elsif($method eq "service"){ 
				while((my $email) = $sth->fetchrow_array){ 
					if($email =~ m/\@$keyword$/i){ 
						print"<input type=\"checkbox\" name=\"address\" value=\"$email\">&nbsp;&nbsp; $email<br>\n";
				 		$found++;  
					}	   
				}  
			}else{ 
				while((my $email) = $sth->fetchrow_array){ 
					if($email =~ m/$keyword/i){ 
						print"<input type=\"checkbox\" name=\"address\" value=\"$email\">&nbsp;&nbsp; $email<br>\n";
						$found++;
					}
				}
			}
		}
		$sth->finish;
		return($found); 
	}else{ 
		return undef;
	}
}

 
sub get_black_list_match{ 
	my $self = shift; 
	my @got_black; 
	my @got_white; 
	my $black_list = shift; 
	my $try_this = shift; 
	if($black_list and $try_this){ 
		my $black; 
		foreach $black(@$black_list){ 
				$black = quotemeta($black);
			my $try; 
			foreach $try(@$try_this){ 
				my $qm_try = $try; 
				if($qm_try =~ m/$black/i){ 
					push(@got_black, $try); 
				}
			}
		}
		return (\@got_black);
	}else{ 
		return 0;
	}
}

sub print_out_list { 

	my $self = shift; 
	
	my %args = (-Type => 'list',
				-FH  => \*STDOUT,
				@_); 
	my $fh = $args{-FH};
	
	my $count; 
	if($fields{-List}){ 
		my $sth = $self->{dbh}->prepare("SELECT email 
								   FROM " . $subscriber_table .  
								   " WHERE list = ? AND list_type = ? AND list_status = 1");    
		   $sth->execute($fields{-List}, $args{-Type}) or die "cannot do statment (for print out list)! $DBI::errstr\n";    
		while((my $email) = $sth->fetchrow_array){ 
			print $fh $email;
			print "\n"; 
			$count++; 
		}
		$sth->finish;		
		return $count; 
	}
}



sub check_for_double_email {

	my $self = shift; 
	my %args = ( 
	-Email          => undef,
	-Type           => 'list',
	-Status         => 1, 
	@_
	);
	my @list; 
	
	if($fields{-List} and $args{-Email}){ 					 
		my $sth = $self->{dbh}->prepare("SELECT email FROM " . $subscriber_table .  
	                           	        " WHERE list = ? AND list_type = ? AND email= ? AND list_status = ?");   
	                                  
	       $sth->execute($fields{-List}, $args{-Type}, $args{-Email}, $args{-Status}) or die "cannot do statment (for check for double email)! $DBI::errstr\n";  
			while((my $email) = $sth->fetchrow_array){ 
				push(@list, $email); 
			}
		my $in_list = 0; 
		if($list[0]){ 
			$in_list=1;
		}
		$sth->finish;
		return $in_list; 
	}else{ 
		return 0;
	}
}


sub add_to_email_list { 

	my $self = shift; 
	
	my %args = (-Email_Ref => undef, 
				-Type      => "list",
				-Mode      => 'append',
				@_);
	
	my $address = $args{-Email_Ref} || undef;
	my $email_count = 0;
	my $ending = $args{-Type}; 
	my $write_list = $fields{-List} || undef; 
	
	my %list_info = open_database(-List=> $write_list);
	
	if($write_list and $address){
		
		
		
		if($list_info{hard_remove} == 1){ 
		
			foreach(@$address){
				chomp($_);
				$_ = strip($_); 
			
				my $new_id = $self->new_id;
				my $sth = $self->{dbh}->prepare("INSERT INTO ".$subscriber_table." VALUES (nextval('".$subscriber_table."_".$email_id."_seq'),?,?,?,?)");    
				   $sth->execute($_, $fields{-List}, $args{-Type}, 1) or die "cannot do statment (for add_to_email_list1)! $DBI::errstr\n";   
				   $sth->finish;
					$email_count++;
					$log->mj_log($fields{-List},"Subscribed to $write_list.$ending", $_) if (($LOG{subscriptions}) && ($args{-Mode} ne 'writeover')); 
			}
		
		
		}else{ 
		
			foreach(@$address){
				chomp($_);
				$_ = strip($_);
				
				if($self->check_for_double_email(-Email => $_, -Type => $args{-Type}, -Status => 0)){ 
					my $sth = $self->{dbh}->prepare("UPDATE " . $subscriber_table . 
											" SET list_status = 1 WHERE email   = ? AND list = ? AND list_type = ?");    
			   		$sth->execute($_, $fields{-List}, $args{-Type}) or die "cannot do statment (for add_email_list2)! $DBI::errstr\n";   
			   		$sth->finish;
				}else{ 
			
					#my $new_id = $self->new_id;
					my $sth = $self->{dbh}->prepare("INSERT INTO ".$subscriber_table." VALUES (nextval('".$subscriber_table."_".$email_id."_seq'),?,?,?,?)");    
					   $sth->execute($_, $fields{-List}, $args{-Type}, 1) or die "cannot do statment (for add_email_list3)! $DBI::errstr\n";   
					   $sth->finish;
						$email_count++;
						$log->mj_log($fields{-List},"Subscribed to $write_list.$ending", $_) if (($LOG{subscriptions}) && ($args{-Mode} ne 'writeover')); 	
				}
			}
		}

		return $email_count; 
	}else{ 
		warn('Mojo Mail Error: No list, or list ref was given in add_email_list() in Guts.pm');
		return undef;
	}
}


sub remove_from_list { 

	my $self = shift; 
	my %args = ( 
	-Email_List => undef, 
	-Type       => 'list',
	@_,
	); 
	
	my $list     = $fields{-List}; 
	my $path     = $fields{-Path};
	 
	my $type     = $args{-Type}; 
	my $deep_six = $args{-Email_List}; 
	my $count = 0; 
	
	my %list_info = open_database(-List=> $list);
	
	
	
	if($list and $deep_six){ 
		
		
		if($list_info{hard_remove} == 1){ 
			foreach(@$deep_six){ 
			my $sth = $self->{dbh}->prepare("DELETE FROM " . $subscriber_table . " 
									   WHERE email   = ?
									   AND list      = ?
									   AND list_type = ?");   
									    
			   $sth->execute($_, $fields{-List}, $args{-Type}) or die "cannot do statment! $DBI::errstr\n";   
			   $sth->finish;
			   $count++;
				# js - log it
				$log->mj_log($fields{-List},"Unsubscribed from $list - $type", $_) if $LOG{subscriptions}; 
			}
		}else{ 
			foreach(@$deep_six){ 
			my $sth = $self->{dbh}->prepare("UPDATE " . $subscriber_table . " 
									   		 SET list_status   = 0
									   	 	 WHERE email   = ?
										     AND list      = ?
									   		 AND list_type = ?");    
			   $sth->execute($_, $fields{-List}, $args{-Type}) or die "cannot do statment! $DBI::errstr\n";   
			   $sth->finish;
			   $count++;
				# js - log it
				$log->mj_log($fields{-List},"Unsubscribed (soft) from $list - $type", $_) if $LOG{subscriptions}; 
			}
		
		}
		
		
		
		
		
		return $count; 
	}else{ 
	warn('Mojo Mail Error: No list information given at Guts.pm for remove_from_list()');
	return ('no list');
	}		 
}


sub list_option_form { 

my $self = shift; 

my %args = (
-Type           =>  'list', 
-In_Order       =>  0, 
@_,
);


if(defined($fields{-List})){ 

#make the 'Show Domains' Hash
my %domains; 
foreach(@DOMAINS){ $domains{$_} = 0; }
$domains{Other} = 0;

#make the 'Show Services' Hash
my %SERVICES; 
foreach(@SERVICES){ $SERVICES{$_} = 0; }


	my $email;
	my $count = 0; 

my $test = 0;
   $test = $SHOW_DOMAIN_TABLE + $SHOW_SERVICES_TABLE + $SHOW_EMAIL_LIST; 
  
unless ($test == 0){

#this is a stupid, dumb, idiotic idea, but, people want it. 
if($args{-In_Order} == 1){
	$self->sort_email_list(%args);
}



my $sth = $self->{dbh}->prepare("SELECT email FROM ". $subscriber_table .  
	                     " WHERE list = ? 
	                       AND  list_type = ?
	                       AND  list_status    = 1");    
   $sth->execute($fields{-List}, $args{-Type}) or die "cannot do statment! $DBI::errstr\n";   
			while((my $email) = $sth->fetchrow_array){
				chomp($email);
				if($SHOW_EMAIL_LIST ==1){
					print "<option value=",$email,">",$email,"</option>\n";
				}
                # this is the 'Show Domains' Hash Generator
				
				if($SHOW_DOMAIN_TABLE ==1){
					my $domain_email = $email;
					#delete everything before the first . after the @. 
					$domain_email =~ s/(^.*)@//; 
					#lowercase the ending..
					$domain_email = lc($domain_email); 
					#strip everything before the last . 
					$domain_email  =~ s/(^.*)\.//;

					# hey, if it matches, recorde it. 
					if(exists($domains{$domain_email})){ 
					 $domains{$domain_email}++; 
					 }else{ 
					 $domains{Other}++;
					 } 
                 }
        
				
				
				
				
				
				# Here be the 'Show Services' Hash Generator; 
			    if($SHOW_SERVICES_TABLE == 1){ 
				    my $services_email = $email;
					#delete everything before the first . after the @. 
	                $services_email =~ s/(^.*)@//; 
	                #lowercase the ending..
	                $services_email = lc($services_email); 

	                # hey, if it matches, record it. 
	                if(exists($SERVICES{$services_email})){ 
	                $SERVICES{$services_email}++ ;
	                }else{ 
	                $SERVICES{Other}++;
	                }
                }
 				$count++;
				
				
			}
		$sth->finish;	
}	
	return($count, \%domains,\%SERVICES);
	
}else{ 
	return undef; 
}

}


sub create_bulk_sending_file { 

my $self = shift; 

my %args = ( 
-Type      => 'list', 
-Pin       =>  1,
-ID        =>  undef, 
-Ban       =>  undef, 
-Bulk_Test =>  0,
@_,
); 

my $list = $fields{-List}; 
my $path = $TMP; 

my $type = $args{-Type}; 

$list =~ s/ /_/g;

my ($sec, $min, $hour, $day, $month, $year) = (localtime)[0,1,2,3,4,5];
my $message_id = sprintf("%02d%02d%02d%02d%02d%02d", $year+1900, $month+1, $day,  $hour, $min, $sec);

#use the message ID, If we have one. 
my $letter_id = $args{'-ID'} ||  $message_id;


my %banned_list; 

if($args{-Ban}){ 
my $banned_list = $args{-Ban};
foreach(@$banned_list){$banned_list{$_} = 1} 
}


if($list){ 

my $list_file    = $FILES . '/' . $list . '.' . $type; 
		
my $sending_file = $TMP   . '/' . $list . '.' . $type . '.' .$letter_id; 
		

#open one file, write to the other. 
my $email; 


my %list_info = open_database(-List=>$list);
	
sysopen (SENDINGFILE, "$sending_file",  O_RDWR|O_CREAT, $FILE_CHMOD) or
	die "Mojo Mail $VER Error: Cannot create temporary email list file for sending out bulk message: $!"; 
flock(SENDINGFILE, LOCK_EX); 	

my $to_pin = make_pin(-Email => $list_info{mojo_email});
print SENDINGFILE "$list_info{mojo_email}\:\:$to_pin\n";
my $test_test = $args{'-Bulk_Test'};
chomp($test_test);
unless($test_test == 1){ 

my $query = "SELECT email";
  
my @merge_fields = split(',',  $list_info{merge_fields}); 

foreach(@merge_fields){ 
	$query.= ', ' . $_;
}
$query .= " FROM " . $subscriber_table .  
	      " WHERE list=? AND list_type=? AND list_status=1";

warn "query: " . $query;
my $sth = $self->{dbh}->prepare($query) or die "cannot prepare statment! $DBI::errstr\n";     	  
	                              
  #$sth->execute() or die "cannot do statment! $DBI::errstr\n";     
   $sth->execute($fields{-List}, $args{-Type}) or die "cannot do statment! $DBI::errstr\n";     
 
  
   my $field_ref; 
    
    while($field_ref = $sth->fetchrow_hashref){  		
		chomp($field_ref->{email}); 
		unless(exists($banned_list{$field_ref->{email}})){
			my $pin = make_pin(-Email => $field_ref->{email}); 
			
			print SENDINGFILE $field_ref->{email} . '::' . $pin;
			foreach(@merge_fields){ 
				print SENDINGFILE '::' . $field_ref->{$_};
			}
			print SENDINGFILE "\n";
		}
	}
	$sth->finish;	
}

close(SENDINGFILE) 
	or die ("Mojo Mail $VER Error - could not close temporary sending  file '$sending_file' successfully"); 

return $sending_file; 



}else{ 
	warn('Mojo Mail Error: No list information given for Guts.pm at creat_bulk_sending_file()');
	return undef;
}


}


=pod

=head2 my ($unique, $duplicate) = unique_and_duplicate(-List=> $list, -New_List => \@new_list); 

This is used to mass add and remove email addresses from a list, it takes a 
array ref full of new email addresses, and see if they are already in the list. 

my ($unique_ref, $duplicate_ref) = weed_out_subscribers(-List     => $list, 
														-Path     => $FILES, 
														-Type     => 'list', 
														-New_List => \@addresses, 
														);

=cut

 
sub unique_and_duplicate { 

my $self = shift; 

my %args = ( 
-New_List      => undef, 
-Type          => 'list', 
@_,
); 


# first thing we got to do is to make a lookup hash. 
my %lookup_table; 
my $address_ref = $args{-New_List}; 
my $list        = $fields{-List};

if($list and $address_ref){

foreach(@$address_ref){$lookup_table{$_} = 0}
				 
my $email; 

my $sth = $self->{dbh}->prepare("SELECT email FROM " . $subscriber_table .  
	                            " WHERE list = ? 
	                              AND list_type = ?
	                              AND  list_status   = 1");    
   $sth->execute($fields{-List}, $args{-Type}) or die "cannot do statment! $DBI::errstr\n";     
    while((my $email) = $sth->fetchrow_array){  
		chomp($email);
		$lookup_table{$email} = 1 if(exists($lookup_table{$email})); 
		#nabbed it, 
	}
	$sth->finish;	    
    
    
#lets lookie and see what we gots.     
my @unique; 
my @double; 
my $value; 



foreach(keys %lookup_table){
	$value = $lookup_table{$_}; 
	if($value == 1){ 
		push(@double, $_)
	}else{ 
		push(@unique, $_) 
	}
}

#again, harmony is restored to the force.
return(\@unique, \@double); 
}else{ 

warn('Mojo Mail Error: No list name or list reference provided at Guts.pm');
return undef;
}

}

sub find_unique_elements { 

my $self = shift; 

my $A = shift || undef; 
my $B = shift || undef; 
if($A and $B){ 


#lookup table
my %seen = ();     

# we'lls tore unique things in here            
my @unique = ();

#we'll store what we already got in here
my @already_in = ();                 

# build lookup table
foreach my $item (@$B) { $seen{$item} = 1 }

# find only elements in @$A and not in @$B
foreach my $item (@$A) {
    unless ($seen{$item}) {
        # it's not in %seen, so add to @aonly
        push(@unique, $item);
    }else{
        push(@already_in, $item);
        }
}




return (\@unique, \@already_in); 
}else{ 

return 0; 

}
}



#sub new_id { 
#
#	my $self = shift; 
#	my $sth = $self->{dbh} -> prepare("SELECT max(".$email_id.") FROM ".$subscriber_table);
#	   $sth -> execute; 
#	my @row = $sth -> fetchrow_array(); 
#	   $sth -> finish; 
#	my $new_id = $row[0];
#	   $new_id++;
#	return $new_id;
#	
#}

sub tables { 
	my $self = shift; 
	my @tables = $self->{dbh}->tables();
	return \@tables;
}

sub columns { 
	my $self = shift; 
	my $sth = $self->{dbh}->prepare("SELECT * FROM " . $subscriber_table);    
	$sth->execute() or die "cannot do statment! $DBI::errstr\n";  
	my $i; 
	my @cols;
	for($i = 1; $i <= $sth->{NUM_OF_FIELDS}; $i++){ 
		push(@cols, $sth->{NAME}->[$i-1]);
	} 
	$sth->finish;
	return \@cols;
}

sub merge_fields { 
	my $self = shift;
	my $l = $self->columns;
	my %omit_fields = (id          => 1,
					   email       => 1,
					   list        => 1,
					   list_type   => 1,
					   list_status => 1);	

	my @r;
	foreach(@$l){ 
		push(@r, $_) unless defined($omit_fields{$_});
	}
	return \@r;
}



sub connectdb {
  
  my $self = shift; 
  my $data_source = "dbi:$dbtype:dbname=$database;host=$dbserver;port=$port";
  $self->{dbh} = DBI->connect("$data_source", $user, $pass) || die("can't connect to db: $!");
 
}


sub remove_this_listype {
	my $self = shift; 
	my %args = (-Type => 'list', @_); 
	my $sth = $self->{dbh}->prepare("DELETE FROM " . $subscriber_table .
		                            " WHERE list    = ?
		                              AND list_type = ?");    
		   $sth->execute($fields{-List}, $args{-Type}) or die "cannot do statment! $DBI::errstr\n";   
		   $sth->finish;
}

sub disconnectdb {
  my $self = shift; 
  $self->{dbh}->disconnect;

}





sub DESTROY { 
	my $self = shift; 
	$self->disconnectdb;

}

1;
