#!/usr/bin/perl
use strict;
use HSPC::WebDB qw(select_hashrows select_run select_rows select_row select_hash);
use Data::Dumper;
use HSPC::MT::Billing::Constants;

use constant TYPE_BOOLEAN		=> 1;
use constant TYPE_STRING		=> 2;
use constant TYPE_NULLABLE		=> 3;
use constant TYPE_NUMBER		=> 4;


main();

my $error = 0;
my $clients_check_errors = [];## client errors - contains hashref with errors
my $domains_check_errors = [];## domains errors - contains list IDs
my $domains_nonvalid_errors = [];## plesk domain ENV_IDs list
my $clients_nonvalid_errors = [];## plesk client ENV_IDs list
my $nonunique_da_errors = [];## domain aliases, non-unique


sub main {
	ulog("Looking for DB inconsistency...");
	pd_check_unique_names();
	non_valid_pd_entries();
	non_valid_pc_entries();
	clients_check();
	domain_check();
	domain_alias_unique();
	unless ($error) {
		ulog("No error found. You may proceed with upgrade.");
		exit 0;
	}

	ulog("Start interactive resolver...");
	start_resolver();

	ulog("\n\nInteractive resolver ended.\nStarting checks once again - looking for problems has not been resolved yet.\nFix errors before upgrade if any...\n");
	$error = 0;
	pd_check_unique_names();
	non_valid_pd_entries();
	non_valid_pc_entries();
	clients_check();
	domain_check();
	domain_alias_unique();

	if($error) {
	 ulog("\n\t[ATTENTION]\t\tFIX DB ERRORS BEFORE UPGRADE!\n \n");
	} else {
		ulog("No error found. You may proceed with upgrade.");
	}
	exit $error;
}

sub base_subscr {
	my $subscr_id = shift;
	my $subscr_item = select_hash(qq|SELECT * from subscr_base where subscr_id=?|,$subscr_id); 
	return $subscr_item;
}


sub wulog {
	my $msg = shift;
	my @dates = localtime();
	my $date_str = sprintf("%02d", $dates[2]) . ':' . sprintf("%02d", $dates[1]) . ':' .  sprintf("%02d", $dates[0]);
	foreach (split "\n", $msg) {
		print "[$date_str] " . $_ . "\n";
	}
	$error = 1;
}

sub ulog {
	my $msg = shift;
	my @dates = localtime();
	my $date_str = sprintf("%02d", $dates[2]) . ':' . sprintf("%02d", $dates[1]) . ':' .  sprintf("%02d", $dates[0]);
	foreach (split "\n", $msg) {
		print "[$date_str] " . $_ . "\n";
	}
}


## Check that there are domains in plesk_domain_ve and no in list table
## We will be unable to find appropriate colelctor records - no plesk_id !
## Put errors(PD_env_id) to domains_nonvalid_errors
sub non_valid_pd_entries {
	my $ve_entries = select_hashrows(qq|select pd_id,hw_id,pd_hostname from plesk_domain_ve|);

	foreach my $ve (@{$ve_entries}) {
		## Skip terminated subscriptions and warn about non-valid entries
		my $p_subscr = select_hashrows(qq|select subscr_id from subscr_pleskdomain where pd_id=?|, $ve->{pd_id});
		unless(ref($p_subscr) && scalar(@{$p_subscr}) && $p_subscr->[0]->{subscr_id}) {
			wulog("Can't find subscription for plesk domain #" . $ve->{pd_id} . " with hostname " . $ve->{pd_hostname});
			push @{$domains_nonvalid_errors}, $ve->{pd_id};
			next;
		}
		my $subscr = base_subscr($p_subscr->[0]->{subscr_id});
		unless(ref($subscr)) {
			wulog("Can't load subscription#" . $p_subscr->[0]->{subscr_id} .
				" for plesk domain env#" . $ve->{pd_id} . " with hostname " . $ve->{pd_hostname});
			push @{$domains_nonvalid_errors}, $ve->{pd_id};
			next;
		}
		next if ($subscr->{status} == EN_DELETED);## It's a correct situation

		my $items = select_hashrows(qq|select * from plesk_domain_list where hspc_pdenv_id=?| , $ve->{pd_id});

  unless(defined($items)) {
			wulog("Domain env#" .$ve->{pd_id} ." " . $ve->{pd_hostname} . " will be lost on upgrade (no domain in LIST table)");
			next;
		}

		my $num = scalar(@{$items});
		if($num == 0) {
			wulog("Domain env#" . $ve->{pd_id} ." ". $ve->{pd_hostname} . " will be lost on upgrade (no domain in LIST table)");
		} elsif($num > 1) {
			wulog("Domain env#" . $ve->{pd_id} ." ". $ve->{pd_hostname} . " may be lost on upgrade (more than 1 record in LIST table for the domain)");
		}
	}
}

sub non_valid_pc_entries {
	my $ve_entries = select_hashrows(qq|select pc_id,hw_id,client_login from plesk_client_ve|);

	foreach my $ve (@{$ve_entries}) {
		## Skip terminated subscriptions and warn about non-valid entries
		my $p_subscr = select_hashrows(qq|select subscr_id from subscr_pleskclient where pc_id=?|, $ve->{pc_id});
		unless(ref($p_subscr) && scalar(@{$p_subscr}) && $p_subscr->[0]->{subscr_id}) {
			wulog("Can't find subscription for plesk client env#" . $ve->{pc_id} . " with login " . $ve->{client_login});
			push @{$clients_nonvalid_errors}, $ve->{pc_id};
			next;
		}
		my $subscr = base_subscr($p_subscr->[0]->{subscr_id});
		unless(ref($subscr)) {
			wulog("Can't load subscription#" . $p_subscr->[0]->{subscr_id} .
				" for plesk client env#" . $ve->{pc_id} . " with login " . $ve->{client_login});
			push @{$clients_nonvalid_errors}, $ve->{pc_id};
			next;
		}
		next if ($subscr->{status} == EN_DELETED);## It's a correct situation

		my $items = select_hashrows(qq|select * from plesk_client_list where hspc_pcenv_id=?| , $ve->{pc_id});

  unless(defined($items)) {
			wulog("Client env#" .$ve->{pc_id} ." " . $ve->{client_login} . " will be lost on upgrade (no client in LIST table)");
			next;
		}

		my $num = scalar(@{$items});
		if($num == 0) {
			wulog("Client env#" . $ve->{pc_id} ." ". $ve->{client_login} . " will be lost on upgrade (no client in LIST table)");
		} elsif($num > 1) {
			wulog("Client env#" . $ve->{pc_id} ." ". $ve->{client_login} . " may be lost on upgrade (more than 1 record in LIST table for the client)");
		}
	}
}


## Check that there are no more than one ACTIVE subscriptions with the same name
sub pd_check_unique_names {
	my $res = select_hashrows(qq|select pd_hostname, count(*) C from subscr_pleskdomain group by pd_hostname having C > 1|);
	foreach my $row (@{$res}) {
		my $ds = select_hashrows(qq|select * from subscr_pleskdomain where pd_hostname=?| , $row->{pd_hostname});

		my $active_subscrs = 0;
		my $subscr_ids = [];
		foreach my $subscr_rec (@{$ds}) {
			my $subscr = base_subscr($subscr_rec->{subscr_id});
			if ($subscr->{status} == EN_ACTIVE){
				$active_subscrs++;
				push @{$subscr_ids}, $subscr_rec->{subscr_id};
			}
		}
		if ($active_subscrs > 1) {
			wulog("There are more than one ACTIVE subscriptions($active_subscrs) for the domain " . $row->{pd_hostname});
			wulog("It's impossible to resolve such conflict automatically - plz review following subscription");
			wulog("and terminate wrong items(leave only one as ACTIVE): ID#" . (join ', ID#', @{$subscr_ids}));
		}
	}
}

## Check that we have non-empty client logins: we are unable to save it's
## because of MT checks to keep data sanity & purity
sub clients_check {
	my $res = select_hashrows(qq|
		SELECT pc_collector_id, hw_id hw_id, plesk_client_pname client_name, plesk_client_login client_login
		FROM plesk_collector_raw_client
	|);
	my $uniq = {};## to hold uniq combination of hw_id-plesk_login
	foreach my $item (@{$res}) {
		unless($item->{client_login}) {## Absent on Plesk
			wulog("Plesk Client '" . $item->{client_name} . "' will be lost on upgrade(no login)");
			push @{$clients_check_errors}, {pc_collector_id => $item->{pc_collector_id}, type => 1};
			next;
		}
		my $seq = $item->{hw_id} . '-' . $item->{client_login};## Sequence
		if($uniq->{$seq}) {
			wulog("Plesk Client '" . $item->{client_name} . "' has non-unique name on the node");
			push @{$clients_check_errors}, {pc_collector_id => $item->{pc_collector_id}, type => 2};
		}
		$uniq->{$seq} = 1;
	}
}

## Check that ACTIVE and NOT IN HSPc domains HAS Plesk Clients
#
# 1 - got domains from plesk_domain_list status - 0,2,3
# 2 - got item from plesk_collector_raw_domain
# 3  - got client from collector
sub domain_check {
	my $pd_list = select_hashrows(
		qq|SELECT pd_list_id, pd_collector_id, hw_id,hostname,status,pd_list_id from plesk_domain_list where status in (0,2,3)|
	);
	foreach my $list_item (@{$pd_list}) {
		my $pd_col_item = select_hash(qq|SELECT plesk_domain_id,plesk_client_id from plesk_collector_raw_domain 
			where pd_collector_id=?|,$list_item->{pd_collector_id});
		unless (ref($pd_col_item)) {
			##wulog("There is no collector information for domain " . $list_item->{hostname} . " found in DB, list ID#" . $list_item->{pd_list_id});
			## Such domains will be automatically moved to Conflict Resolver or dropped
			next;
		}
		my $pc_col_item = select_hashrows(
			qq|SELECT * from plesk_collector_raw_client where plesk_client_id=? and hw_id=?|,
			$pd_col_item->{plesk_client_id}, $list_item->{hw_id});
		if (!ref($pc_col_item) || scalar(@{$pc_col_item})<1) {
			wulog("There is no Plesk Client for domain " . $list_item->{hostname} . " found in DB, list ID#" . $list_item->{pd_list_id});
			push @{$domains_check_errors}, $list_item->{pd_list_id};
		}
	}
}


sub domain_alias_unique {
	my $pa_list = select_hashrows(
		qq|select name, hw_id, count(name) c from plesk_domain_alias group by name,hw_id having c > 1|
	);
	foreach my $da (@{$pa_list}) {
		wulog("There are more than one domain aliases with hostname $da->{name} on HW node $da->{hw_id}");
		push @{$nonunique_da_errors}, [$da->{name}, $da->{hw_id}];
	}
}


sub start_resolver {

	################ CLIENTS ###################
	wulog("Start processing clients conflicts:");
	my @emply_login = grep {$_->{type} == 1} @{$clients_check_errors};
	## Try to load appropriate logins from plesk_client_ve table
	foreach my $e_time (@emply_login) {
		## Load COLLECTOR item
		my $e_col_rec = select_hash(qq|SELECT * from plesk_collector_raw_client where pc_collector_id=?|,$e_time->{pc_collector_id});
		wulog("Try to restore client login for collector record #" . $e_time->{pc_collector_id} .
			" and client name is " . $e_col_rec->{plesk_client_pname});
		## Load LIST item
		my $e_list_rec = select_hash(qq|SELECT * from plesk_client_list where pc_collector_id=?|,$e_time->{pc_collector_id});
		unless ($e_list_rec) {
			wulog("Can't find list record for collector item. Probably it's a garbage. It's impossible to restore client login automatically.");
			##TODO: Propose to delete
			my $is_del = ask_param("Do you want to delete this collector record? ", 'n', TYPE_BOOLEAN);
			if($is_del) {
				select_run('delete from plesk_collector_raw_client where pc_collector_id=?', $e_time->{pc_collector_id});
			}
			next;
		}
		## Load VE item. Will be undef for AUX CLIENTS
		my $e_ve_rec = select_hash(qq|SELECT * from plesk_client_ve where pc_id=?|,$e_list_rec->{hspc_pcenv_id});
		if(!$e_ve_rec) {
			wulog("It's rather AUX client or database corruption (no record in plesk_client_ve table)");
			## Check _ve table to SAME IDs:
			my $e_ve_rec_ids = select_hash(qq|SELECT * from plesk_client_ve where client_id=? and hw_id=?|,
				$e_col_rec->{plesk_client_id}, $e_col_rec->{hw_id});
			if ($e_ve_rec_ids) {
				wulog("There is record about client subscr with the same ClientID on the same node but different collector records. Plz review manually. It's impossible to restore client login automatically.");
			}
		} else {
			if ($e_ve_rec->{client_login}) {
				wulog("Restoring client login using values used on initial provisioning...");
				select_run("update plesk_collector_raw_client set plesk_client_login=? where pc_collector_id=?",
					$e_ve_rec->{client_login},$e_time->{pc_collector_id});
				wilog("Succeeded.");
			} else {
				wulog("It's impossible to restore client login automatically. Plz get client login directly from Plesk and update DB manually.");
			}
		}
	}

	my @dublicate_login = grep {$_->{type} == 2} @{$clients_check_errors};
	foreach my $e_time (@dublicate_login) {
		my $e_col_rec = select_hash(qq|SELECT * from plesk_collector_raw_client where pc_collector_id=?|,$e_time->{pc_collector_id});
		wulog("Try to resolve collector dublicate record #" . $e_time->{pc_collector_id} .
			" and client name is " . $e_col_rec->{plesk_client_pname});
		my $same_logins = select_hashrows(qq|SELECT * from plesk_collector_raw_client where plesk_client_login=? and hw_id=?|,
			$e_col_rec->{plesk_client_login}, $e_col_rec->{hw_id});

		foreach my $dup_item (@{$same_logins}) {
			#In no item - in plesk_client_list - delete it
			my $list_item = select_hash(qq|select * from plesk_client_list where pc_collector_id=?|,$dup_item->{pc_collector_id});
			unless ($list_item) {
				my $is_del = ask_param("There is no list record for client. Do you want to delete this collector record#".
					$dup_item->{pc_collector_id}. " ?", 'n', TYPE_BOOLEAN);
				if ($is_del){
					select_run(qq|delete from plesk_collector_raw_client where pc_collector_id=?|, $dup_item->{pc_collector_id});
				}
			}
		}
	}


	################# DOMAINS ######################
	wulog("Start processing domains conflicts:");
	## Propose to delete list records if no client if:
	## 1 - domain is AUX (collector will find themself if any)
	## 2 - domain in ABSENT_ON_PLESK && SUBSCR deleted
	##
	## Propose change status to ABSENT_ON_PLESK if:
	## Status is ACTIVE && subscr not deleted
	foreach(@{$domains_check_errors}) {
		my $list_item = select_hash(qq|select * from plesk_domain_list where pd_list_id=?|,$_);
		wulog("Processing plesk domain " . $list_item->{hostname});
		if ($list_item->{status} == 3 || $list_item->{status} == 2) {##AUX || not registered
				my $is_del = ask_param("Failed to find client for AUX or NotRegistered domain. Delete domain list#". $list_item->{pd_list_id}." ?", 'n', TYPE_BOOLEAN);
				if ($is_del){
					select_run(qq|delete from plesk_domain_list where pd_list_id=?|, $list_item->{pd_list_id});
				}
				next;
		} elsif($list_item->{status} == 0) {##ACTIVE
				my $is_del = ask_param("Domain list#" .$list_item->{pd_list_id}. " has ACTIVE status but no info about domain on node found. Mark as Absent_On_Plesk ? ", 'n', TYPE_BOOLEAN);
				if ($is_del){
					select_run(qq|update plesk_domain_list set status=1 where pd_list_id=?|, $list_item->{pd_list_id});
				}
				next;
		}
	}

	## NON-valid entries, domains
	foreach my $ve_id (@{$domains_nonvalid_errors}) {
		my $ve_item = select_hash(qq|select * from plesk_domain_ve where pd_id=?|,$ve_id);
		my $list_item = select_hash(qq|select * from plesk_domain_list where hspc_pdenv_id=?|,$ve_id);
		wulog("Processing Plesk Domain " . $ve_item->{pd_hostname});
		unless($list_item) {
			## If no subscr - propose to delete
			my $subscr_item = select_hash(qq|select * from subscr_pleskdomain where pd_id=?|,$ve_id);
			if (!$subscr_item) {
				my $is_del = ask_param("There is no LIST record for domain env#" . $ve_id . ' ' . $ve_item->{pd_hostname} .
					" and no subscr exists(however ENV record exists). Delete domain?", 'n', TYPE_BOOLEAN);
				if ($is_del){
					select_run(qq|delete from plesk_domain_ve where pd_id=?|, $ve_id);
				}
			}else{
				wulog("There is no LIST record for domain env#" . $ve_id . ' ' . $ve_item->{pd_hostname} . " but subscr exists. Fix DB manually");
			}
			next;
		}
		if ($list_item->{status} == 1) {## Not On Plesk
			## Only non-aux domains may have such status
			## Look for subscr
			my $p_subscr_item = select_hash(qq|select * from subscr_pleskdomain where pd_id=?|,$list_item->{hspc_pdenv_id});
			unless($p_subscr_item) {
				my $is_del = ask_param("Domain env#$ve_id status is ABSENT_ON_PLESK but no subscription found(1). Delete domain ?", 'n', TYPE_BOOLEAN);
				if ($is_del){
					select_run(qq|delete from plesk_domain_list where pd_list_id=?|, $list_item->{pd_list_id});
					select_run(qq|delete from plesk_domain_ve where pd_id=?|, $ve_id);
				}
				next;
			}
			my $subscr_item = select_hash(qq|select * from subscr_base where aubscr_id=?|,$p_subscr_item->{subscr_id});
			unless($subscr_item) {
				my $is_del = ask_param("Domain env#$ve_id status is ABSENT_ON_PLESK but no subscription found(2). Delete domain ?", 'n', TYPE_BOOLEAN);
				if ($is_del){
					select_run(qq|delete from plesk_domain_list where pd_list_id=?|, $list_item->{pd_list_id});
					select_run(qq|delete from plesk_domain_ve where pd_id=?|, $ve_id);
				}
				next;
			}
			if($subscr_item->{status} == 2) {## DELETED
				my $is_del = ask_param("Domain env#$ve_id status is ABSENT_ON_PLESK but no subscription is DELETED. Delete domain ?", 'n', TYPE_BOOLEAN);
				if ($is_del){
					select_run(qq|delete from plesk_domain_list where pd_list_id=?|, $list_item->{pd_list_id});
					select_run(qq|delete from plesk_domain_ve where pd_id=?|, $ve_id);
				}
				next;
			}
			wulog("You should either fix linking of plesk_domain_ve with subscr_pleskdomain && subscr_base tables or delete record from plesk_domain_ve manually for domain env#$ve_id");
		}
	}

	## NON-valid entries, clients
	foreach my $ve_id (@{$clients_nonvalid_errors}) {
		my $ve_item = select_hash(qq|select * from plesk_client_ve where pc_id=?|,$ve_id);
		my $list_item = select_hash(qq|select * from plesk_client_list where hspc_pcenv_id=?|,$ve_id);
		wulog("Processing Plesk Client " . $ve_item->{client_login});
		unless($list_item) {
			## If no subscr - propose to delete
			my $subscr_item = select_hash(qq|select * from subscr_pleskdomain where pd_id=?|,$ve_id);
			if (!$subscr_item) {
				my $is_del = ask_param("There is no LIST record for client env#" . $ve_id . ' ' . $ve_item->{client_login} .
					" and no subscr exists(however ENV record exists). Delete client?", 'n', TYPE_BOOLEAN);
				if ($is_del){
					select_run(qq|delete from plesk_client_ve where pc_id=?|, $ve_id);
				}
			}else{
				wulog("There is no LIST record for client env#" . $ve_id . ' ' . $ve_item->{client_login} . " but subscr exists. Fix DB manually");
			}
			next;
		}
	}

	## NON-unique domain aliases
	foreach my $da (@{$nonunique_da_errors}) {
		my $da_list = select_hashrows("select * from plesk_domain_alias where name=? and hw_id=?", $da->[0], $da->[1]);
		ulog("There are " . scalar(@{$da_list}) . "domain aliases on HW node " . $da->[1] . " with hostname " . $da->[0]);
		foreach my $da_item (@{$da_list}) {
			my $is_del = ask_param("Delete domain_alias with ID #" . $da_item->{da_id}, 'n', TYPE_BOOLEAN);
			if ($is_del){
				select_run(qq|delete from plesk_domain_alias where da_id=?|, $da_item->{da_id});
				select_run(qq|delete from plesk_domain_alias_dns where da_id=?|, $da_item->{da_id});
			}
		}
	}
}
	

## Interactively asks for some parameter, given
## the question to ask, default value, and
## value type, used for validation purposes
sub ask_param {
	my $askn = shift;
	my $def_screen = shift;
	my $validator = shift;


	while(1) {
		print $askn . " [" . (defined $def_screen ? $def_screen: " ")
			. "]: ";

		my $res = readline STDIN;
		$res =~ s/\s+//g;
		if($res) {
			my $cres;
			if(defined $validator) {
				$cres = validate($res, $validator);
			} else {
				$cres = $res;
			}

			return $cres
				if defined $cres
					or (defined $validator
					and $validator == TYPE_NULLABLE);

			print "Invalid input parameter format, try again!\n";
		} else {
			my $ret = validate($def_screen, $validator);
			return $ret
				if defined $ret
					or (defined $validator
					and $validator == TYPE_NULLABLE);

			warn "This parameter can't be left as it is,
				you have to actually input something.
				Refer to the manual.\n";
		}
	}
}


## Validate value, given its type.
## Return value must be interpretation is based on type.
## For all, except TYPE_NULLABLE returns the value to be 
## used on successfull validation and undef otherwise.
## In case of TYPE_NULLABLE undef return value might indicate success too.
sub validate {
	my ($val, $type) = @_;
	if($type == TYPE_BOOLEAN) {
		return ($val =~ /^y/i) ? 1 : 0;
	} elsif($type == TYPE_NUMBER) {
		return ($val =~ /^\d+$/) ? $val : undef;
	} else {
		return $val;
	}
}