#!/usr/bin/perl

BEGIN { use lib '/usr/home/cfg' ; require push_inc ; }
    
require cfg ;

use CGI::Carp qw(fatalsToBrowser) ;
# use Date::Calc qw(:all);
# use DBI;

# https://itvadmin.co.za/cgi-bin/scripts/admin/oneoff/build_poc_table.pl?1&poc
# https://itvadmin.co.za/cgi-bin/scripts/admin/oneoff/build_poc_table.pl?1&recipient

#-------------------------------------------------------------------------------

my  @ARGV     = split(/\&/, $ENV{'QUERY_STRING'});
our $debug    = $ARGV[0] ;
our $poc_or_recipient 	= $ARGV[1] ;
# our $username = 'rory' ;

if ($debug) { print "Content-type: text/html\n\n"; }

#------------------------------------------------------------------------------------------

&today ;
	
&db_open_upd ;
# &db_open_ro ;
    our $db_ignore_open_close = 1 ; # do one open and one close instead of repeating it
		&load_vars ;
	$db_ignore_open_close = 0 ; # do one open and one close instead of repeating it
&db_close_conn ;

if ($debug) { print "Done."; }

exit;

#------------------------------------------------------------------------------------------

sub load_vars {
	
 
	# $poc_or_recipient = "poc" ;
	
	exit unless $poc_or_recipient ;
	# my $poc_or_recipient = "poc" ; 	
	my $id_field = "$poc_or_recipient\_id" ;
	my $table_ = "event_quotes_$poc_or_recipient" ;
	my $name_field = "$poc_or_recipient\_name" ;
	my $nr_field = ($poc_or_recipient eq 'poc') ? "$poc_or_recipient\_contact_nr" : "$poc_or_recipient\_nr" ;
 
	&db_min_ro('event_quotes aisa',"id,$name_field,$nr_field","$name_field <> '' AND $name_field IS NOT NULL","","") ; 
	&db_min_ro($table_,'id,name,contact_nr',"","","") ; 
	&db_switch_conn('sss') ;
	&db_min_ro('event_quotes sss',"id,$name_field,$nr_field","$name_field <> '' AND $name_field IS NOT NULL","","") ; 
	my %seen_poc = () ;
	&db_switch_conn('aisa') ;
	
	foreach (keys %{$db{$table_}}) {
		$seen_poc{$db{$table_}{$_}{name}}{$db{$table_}{$_}{contact_nr}} = 1 ;
	}
	
	my %seen_name = () ;  my %seen_name_with_blank_number = () ; my %seen_name_with_non_blank_number = () ; my %seen_number_with_complete_name = () ; my %seen_number_cnt = () ; my %seen_name_cnt = () ; 
	
	my %unique_number_linked_to_name = () ; 
	
	my $cnt = 0 ; 
	use utf8;
	
	foreach (sort { lc $db{"event_quotes aisa"}{$a}{$name_field} cmp lc $db{"event_quotes aisa"}{$b}{$name_field} } keys %{$db{"event_quotes aisa"}}) {
		
		my $number = &common_fix_poc_numbers($db{"event_quotes aisa"}{$_}{$nr_field}) ;
		my $name = &common_fix_poc_name($db{"event_quotes aisa"}{$_}{$name_field}) ;
		
		$db{"event_quotes aisa"}{$_}{$nr_field} = $number ;
		$db{"event_quotes aisa"}{$_}{$name_field} = $name ;
		
		if ($unique_number_linked_to_name{$name} && $unique_number_linked_to_name{$name} ne $number && $number) {
			$unique_number_linked_to_name{$name} = "NA" ;
		} elsif ($number) {
			$unique_number_linked_to_name{$name} = $number ;
		}
	
		$seen_name{$name}{$number} = 1 ;
		$seen_name_with_non_blank_number{$name} = 1 if $number ;
	}	
	
	foreach (sort { lc $db{"event_quotes sss"}{$a}{$name_field} cmp lc $db{"event_quotes sss"}{$b}{$name_field} } keys %{$db{"event_quotes sss"}}) {
		
		my $number = &common_fix_poc_numbers($db{"event_quotes sss"}{$_}{$nr_field}) ;
		my $name = &common_fix_poc_name($db{"event_quotes sss"}{$_}{$name_field}) ;
		# $number = "" if !$number || length($number) <= 5 ; 
		$db{"event_quotes sss"}{$_}{$nr_field} = $number ;
		$db{"event_quotes sss"}{$_}{$name_field} = $name ; 
		if ($unique_number_linked_to_name{$name} && $unique_number_linked_to_name{$name} ne $number && $number) {
			$unique_number_linked_to_name{$name} = "NA" ;
		} elsif ($number) {
			$unique_number_linked_to_name{$name} = $number ;
		}		

		$seen_name{$name}{$number} = 1 ;
		$seen_name_with_non_blank_number{$name} = 1 if $number ;  
	}
	
	my %poc_id = () ; my %final_names_numbers = () ; my %names_seen_with_number = () ; my %skip_name_number = () ;  
	
	foreach my $lc_name (sort { $seen_name{$a} cmp $seen_name{$b} } keys %seen_name) {
		foreach my $nr (sort { $seen_name{$lc_name}{$a} cmp $seen_name{$lc_name}{$b} } keys %{$seen_name{$lc_name}}) {
			# next if $seen_poc{$lc_name}{$nr}  ;
 
			next if $seen_poc{$lc_name}{$nr}  || (!$nr && $seen_name_with_non_blank_number{$lc_name} && $unique_number_linked_to_name{$lc_name} ne 'NA') || $seen_name{$lc_name}{$nr} < 0 ;
			# next if $seen_name_with_non_blank_number{$lc_name} ;
			# $cnt++ ; 			
			# $poc_id{$lc_name}{$nr} = $cnt ;
			$seen_number_cnt{$nr}++ if $nr ; 
			$seen_number_cnt{$nr} = 0 unless $nr ; 
			$names_seen_with_number{$nr}{$seen_number_cnt{$nr}} = $lc_name ;
			$final_names_numbers{$lc_name}{$nr} = 1 ;
			
		}
	}

	$cnt = 0 ;
	
	our %skip_name_number = () ; #our %skip_name_number_because_of = () ; 
	
	foreach my $nr (keys %names_seen_with_number) {
		
		my $seen_cnt = 0 ; 
		
		foreach my $number_cnt (sort {$b <=> $a} keys %{$names_seen_with_number{$nr}}) {
			
			next if $number_cnt <= 1 && !$seen_cnt ;
			$seen_cnt++ ;

			for my $cnt1 (1 .. $number_cnt) {
				
				my @name_split = split(/ /,$names_seen_with_number{$nr}{$cnt1}) ; 
				my $first_name1 = $name_split[0] ;
				my $rest_of_name1 = join(" ", @name_split[1..$#name_split]);
				
				for my $cnt2 (1 .. $number_cnt) {
					
					next if $cnt1 eq $cnt2 ;
					
					# print "\n MAWND $names_seen_with_number{$nr}{$cnt2} =~ /\b$names_seen_with_number{$nr}{$cnt1}b/" ;
					
					if ($names_seen_with_number{$nr}{$cnt2} =~ /\b$names_seen_with_number{$nr}{$cnt1}\b/) {
						# print "\n SKIP : $nr : $names_seen_with_number{$nr}{$cnt1}" ;
						$skip_name_number{$names_seen_with_number{$nr}{$cnt1}}{$nr} = "$names_seen_with_number{$nr}{$cnt2}\_:_$nr" ;
						# $skip_name_number_because_of{$names_seen_with_number{$nr}{$cnt1}}{$nr} = "$names_seen_with_number{$nr}{$cnt2}\_:_$nr" ;
					} else {
						
						@name_split = split(/ /,$names_seen_with_number{$nr}{$cnt2}) ; 
						my $first_name2 = $name_split[0] ;
						my $rest_of_name2 = join(" ", @name_split[1..$#name_split]);
						if ($first_name2 =~ /$first_name1/ && $rest_of_name2 =~ /$rest_of_name1/) {
							$skip_name_number{$names_seen_with_number{$nr}{$cnt1}}{$nr} = "$names_seen_with_number{$nr}{$cnt2}\_:_$nr" ;
							# $skip_name_number_because_of{$names_seen_with_number{$nr}{$cnt1}}{$nr} = "$names_seen_with_number{$nr}{$cnt2}\_:_$nr" ;
						}
					}
				}
			}
			last ;
		} 
	}
	
	&skip_names_numbers ;
	# sort { $seen_name{$lc_name}{$a} cmp $seen_name{$lc_name}{$b} }
	
	$cnt = 0 ;
	
	foreach my $lc_name (sort { $seen_name{$a} cmp $seen_name{$b} } keys %final_names_numbers) {
		foreach my $nr ( keys %{$final_names_numbers{$lc_name}}) {
			# next if $seen_poc{$lc_name}{$nr}  ;
			# next if $seen_name_with_non_blank_number{$lc_name} ;
			
			next if $skip_name_number{$lc_name}{$nr} || !$lc_name || $lc_name eq '1' || $lc_name eq '123' ;
			# next if $lc_name =~ /Iren/ && $lc_name ne 'Irene' && $nr eq '27832524894' ;
			# next if $lc_name =~ /Handr/ && $lc_name ne 'Handro Le Roux' && $nr eq '27766215100' ;
			$cnt++ ; 			
			$poc_id{$lc_name}{$nr} = $cnt ; 
			print "\n INSERT INTO $table_ (id,name,contact_nr) VALUES ($cnt,\"$lc_name\",'$nr') ; " ;
		}
	}
	
	my %event_quotes_ = () ; 

	foreach (sort { lc $db{"event_quotes aisa"}{$a}{$name_field} cmp lc $db{"event_quotes aisa"}{$b}{$name_field} } keys %{$db{"event_quotes aisa"}}) {
		
		my $number = &common_fix_poc_numbers($db{"event_quotes aisa"}{$_}{$nr_field}) ;
		my $name = &common_fix_poc_name($db{"event_quotes aisa"}{$_}{$name_field}) ;
		
		if (!$number) {
			$number = $unique_number_linked_to_name{$name} ; 
			$number = "" if $number eq 'NA' ; 
			# print "\n unique_numbers_linked_to_name : $name : $unique_numbers_linked_to_name{$name}" ;
		}
		if ($skip_name_number{$name}{$number}) {
			($name,$number) = split(/\_:_/,$skip_name_number{$name}{$number}) ; 
			($name,$number) = split(/\_:_/,$skip_name_number{$name}{$number}) if $skip_name_number{$name}{$number} ; 
			($name,$number) = split(/\_:_/,$skip_name_number{$name}{$number}) if $skip_name_number{$name}{$number} ; 
			($name,$number) = split(/\_:_/,$skip_name_number{$name}{$number}) if $skip_name_number{$name}{$number} ; 
			($name,$number) = split(/\_:_/,$skip_name_number{$name}{$number}) if $skip_name_number{$name}{$number} ; 
		}
		
		if ($poc_id{$name}{$number}) {
			# print "\n ID : $name,$number ,$poc_id{$name}{$number}" ;
		} else {
			# print "\n 3 NO ID : $name,$number" ;
		}
		
		$event_quotes_{$poc_id{$name}{$number}}{$_} = 1 if $poc_id{$name}{$number} ;
	}

	foreach my $pid (keys %event_quotes_) {
		my $sql_where = join (" OR ", map { "id='$_'" } keys %{$event_quotes_{$pid}} ) ;
		print "\n UPDATE event_quotes aisa SET $poc_or_recipient\_id = '$pid' WHERE $sql_where ; " if $sql_where ;
	}
	
	&db_switch_conn('sss') ;
	
	%event_quotes_ = () ; 
	
	foreach (sort { lc $db{"event_quotes sss"}{$a}{$name_field} cmp lc $db{"event_quotes sss"}{$b}{$name_field} } keys %{$db{"event_quotes sss"}}) {
		my $number = &common_fix_poc_numbers($db{"event_quotes sss"}{$_}{$nr_field}) ;
		my $name = &common_fix_poc_name($db{"event_quotes sss"}{$_}{$name_field}) ;
		
		if (!$number) {
			$number = $unique_number_linked_to_name{$name} ; 
			$number = "" if $number eq 'NA' ; 
			# print "\n unique_numbers_linked_to_name : $name : $unique_numbers_linked_to_name{$name}" ;
		}
		
		if ($skip_name_number{$name}{$number}) {
			($name,$number) = split(/\_:_/,$skip_name_number{$name}{$number}) ; 
			($name,$number) = split(/\_:_/,$skip_name_number{$name}{$number}) if $skip_name_number{$name}{$number} ; 
			($name,$number) = split(/\_:_/,$skip_name_number{$name}{$number}) if $skip_name_number{$name}{$number} ; 
			($name,$number) = split(/\_:_/,$skip_name_number{$name}{$number}) if $skip_name_number{$name}{$number} ; 
			($name,$number) = split(/\_:_/,$skip_name_number{$name}{$number}) if $skip_name_number{$name}{$number} ; 
		}
		
		$event_quotes_{$poc_id{$name}{$number}}{$_} = 1 if $poc_id{$name}{$number} ;
		# print "\n UPDATE event_quotes SET poc_id = '$poc_id{$name}{$number}' WHERE id = '$_'" if $poc_id{$name}{$number} ;
	}

	foreach my $pid (keys %event_quotes_) {
		my $sql_where = join (" OR ", map { "id='$_'" } keys %{$event_quotes_{$pid}} ) ;
		print "\n UPDATE event_quotes sss SET $poc_or_recipient\_id = '$pid' WHERE $sql_where ; " if $sql_where ;
	}
	
} #-------------------------------------------------------------------------------

sub skip_names_numbers {

	# $skip_name_number{"Klye Reddy"}{"27716057436"} 			= "Kyle Reddy_:_27716057436" ; 
	# $skip_name_number{"Adi"}{"27828831558"} 				= "Adrian Norris_:_27828831558" ;    
	# $skip_name_number{"Monwabisi ( Mo)"}{"27791349494"} 	= "Monwabisi (mo)_:_27791349494" ; 
	# $skip_name_number{"Patrick"}{"073286160"} 				= "Patrick_:_27732876160" ;
	# $skip_name_number{"Wikus"}{"27766969301"} 				= "Wickus Coetzer_:_27766969301" ; 
	# $skip_name_number{"Werner Jv Rensburg"}{"27714103925"} 	= "Werner Jansen van Rensburg_:_27714103925" ; 
	# $skip_name_number{"Werener"}{"27714103925"} 			= "Werner Jansen van Rensburg_:_27714103925" ; 
	# $skip_name_number{"Warner"}{"27714103925"} 				= "Werner Jansen van Rensburg_:_27714103925" ; 
	# $skip_name_number{"Warren Tanner Ellis"}{"27832124379"} = "Warren Tanner-Ellis_:_27832124379" ; 
	# $skip_name_number{"Shaun Labscher"}{"27847751762"} 		= "Shaun Laubscher_:_27847751762" ; 
	# $skip_name_number{"Andrew Nel Skool"}{"27822186009"} 	= "Andrew Nel-Skool_:_27822186009" ; 
	# $skip_name_number{"Anna Marie - Pmbghs"}{"27829293432"} = "Anna-Marie Snyman_:_27829293432" ;  
	# $skip_name_number{"Willem vd Merwe"}{"27844028276"}		= "Willem van De Merwe_:_27844028276" ; 
	# $skip_name_number{"Wanri Venter"}{"07780195566"} 		= "Wanri Venter_:_27780195566" ; 
	# $skip_name_number{"Willem van De Merwe"}{"844028276"} 	= "Willem van De Merwe_:_27844028276" ;  
	# $skip_name_number{"Wayne Philips"}{"27827832211"} 		= "Wayne Phillips_:_27827832211" ; 
	# $skip_name_number{"Martin Vw"}{"27737466628"} 			= "Martin van Wyngaardt_:_27737466628" ; 
	# $skip_name_number{"Max ,mohale"}{"27824974244"}			= "Max Mohale_:_27824974244" ; 
	# $skip_name_number{"Greg"}{"27746011640"} 				= "Gregg Von Molendorf_:_27746011640" ; 
	# $skip_name_number{"Greg"}{"27746011640"} 				= "Gregg Von Molendorf_:_27746011640" ; 
	# $skip_name_number{"Hendo Le Rouz"}{"27766215100"}		= "Hendo Le Roux_:_27766215100" ; 
	# $skip_name_number{"Henrich Kruger"}{"27825024824"}		= "Heinrich Kruger_:_27825024824" ; 
	# $skip_name_number{"Ilse Davds"}{"27605017896"}			= "Ilse Davids_:_27605017896" ; 
	# $skip_name_number{"Renee van Wayk"}{"27847039138"}		= "Renee van Wyk_:_27847039138" ; 
	# $skip_name_number{"Cathrine Mccreesh"}{"27825673044"}	= "Catherine Mccreesh_:_27825673044" ; 
	
} #-------------------------------------------------------------------------------

sub common_fix_poc_numbers {
	
	my ($number) = @_ ;
	
	# use utf8; Make sure this is called
	
	$number =~ s/\-//g ;
	$number =~ s/\p{Space}//g;
	$number = &common_fix_cell($number) ;
	$number = "" if !$number || length($number) <= 5 ; 
	
	if ($number =~ /\//) {
		$number = join ("/",map{ &common_fix_cell($_) } split(/\//,$number)) ;
	} elsif (length ($number) == 10 && substr($number,0,1) eq '7') {
		$number = '2' . $number ;
	}
	return $number ;	
	
} #-------------------------------------------------------------------------------

sub common_fix_poc_name {
	
	my ($name) = @_ ;
	$name = lc $name ;
	$name = join(" ", map { ($_ eq 'v' || $_ eq 'van' || $_ eq 'der' || $_ eq 'vd') ? $_ : ucfirst $_ } split(/ /,$name)) ;
	$name = join("-", map { ucfirst $_ } split(/\-/,$name)) ;
	$name = join(":", map { ucfirst $_ } split(/\:/,$name)) ;
	$name =~ s/  / /g ;
	if ($name =~ /\b- \b/ ) {
		$name =~ s/- /-/g ;
	} elsif ($name =~ /\b -\b/) {
		$name =~ s/ -/-/g ;
	}
	
	$name =~ s/:(?=\S)/: /g;
	$name =~ s/^\s+|\s+$//g;
	
	return $name ; 
	
}  #-------------------------------------------------------------------------------

use db ;
use today ;
use common ;
use common_min ;