use strict;
use warnings;
use DBI;
my @projects = (
	[ 'NL0241', 'Axios Improvements' ],
	[ 'NL0230', 'U-Next' ],
	[ 'NL0232', 'Resource Data Management' ],
	[ 'NL0234', 'Improved Baseline Documentation' ]
	[ 'NL0185', 'Slotnota' ],
	[ 'NL0236', 'More Fun on KPN' ],
	[ 'NL0235', 'Broadband Impact 1 IVR_Manual Solution' ],
	[ 'NL0237', 'Unique Extension to KPN WS-MX Stream' ],
	[ 'NL0243', 'SMS Gateway' ],
	[ 'NL0245', 'Baby Box' ],
	[ 'NL0240', 'Re-engineering Test Environments' ],
	[ 'NL0248', 'CIOT Migration' ],
	[ 'NL0200', 'Service Assurance Study' ],
	[ 'NL0252', 'VoIP on Starter - Parrot' ],
	[ 'NL0253', 'Multiple Year Contract' ],
	[ 'NL0254', 'Infranet Improvements' ],
	[ 'NL0255', 'Service At Home' ],
	[ 'NL0209', 'One CRM' ],
	[ 'NL0212', 'UMA-One Voice' ],
	[ 'NL0215', 'KPN Wholesale' ],
	[ 'NL0218', 'SMC' ],
	[ 'NL0221', 'Split up retention' ],
	[ 'NL0223', 'Multi Line' ],
	[ 'NL0225', 'Desktop Security Suite' ],
	[ 'NL0229', 'SIP Migration' ],
	[ 'NL0231', 'E-Shop' ],
	[ 'NL0233', 'Speedtest Tool' ],
	[ 'NL0217', 'Voice platform in NL' ],
	[ 'NL0239', 'O2O' ],
	[ 'NL0242', 'Upgrade D-Layer' ],
	[ 'NL0238', 'Fixed number for Unique (Flying Fish)' ],
	[ 'NL0191', 'Directory Services Wanadoo Telephony' ],
	[ 'NL0244', 'Hyperion Migration' ],
	[ 'NL0246', 'News Outsourcing' ],
	[ 'NL0247', 'KMS ph2' ],
	[ 'NL0249', 'Slotnota (Ph2)' ],
	[ 'NL0201', 'One KMS' ],
	[ 'NL0203', 'More FUN' ],
	[ 'NL0207', 'PRP-Pre Registration' ],
	[ 'NL0250', 'Fox (Unique on ADSL Starter)' ],
	[ 'NL0210', 'Voice Low End' ],
	[ 'NL0256', 'KPN Wholesale Ethernet' ],
	[ 'NL0213', 'CPE Management Tooling' ],
	[ 'NL0216', 'Citynet Pilot' ],
	[ 'NL0219', 'Converged Ordering' ],
	[ 'NL0222', 'Replacement of Enertel solution for VOIP and Dial-in' ],
	[ 'NL0224', 'Unique Ordering' ],
	[ 'NL0226', 'Unified Messaging' ],
	[ 'NL0228', 'PPD-Test Automation' ]
);

my @teams = (
	[ 'PPD.PM', 		'' ],
	[ 'PPD.FDA', 		'' ],
	[ 'PPD.BPA', 		'' ],
	[ 'PPD.DEV.BILLING', 	'Billing development' ],
	[ 'PPD.DEV.FE', 	'Front end development'],
	[ 'PPD.DEV.MW', 	'Middle ware development' ],
	[ 'PPD.DEV.AXIOSS', 	'Axioss development' ],
	[ 'PPD.DART', 		'' ],
	[ 'ITO OM', 		'' ],
	[ 'ITO BS', 		'' ],
	[ 'ITO SD', 		'' ],
	[ 'NETWORK', 		'' ]
);

my @gates = ("T-1", "T0", "T1", "T2", "T3", "T4");

main();

sub main {
	my $dbh = DBI->connect('dbi:Oracle:xe', 'QUOTES', 'quotes', { RaiseError => 1, AutoCommit => 0 }) || die "Database connection not made: $DBI::errstr";


#	my $dbh = DBI->connect('dbi:Oracle:xe', 'JOURNIX', 'journix', { RaiseError => 1, AutoCommit => 0 }) || die "Database connection not made: $DBI::errstr";
#	truncateTables($dbh);
	addTeamsOLD($dbh);
#	addProjects($dbh);
#	addQuotes($dbh);
#	addDates($dbh);
#	addApproved($dbh);
#	addFinal($dbh);
#	addEstimate($dbh);

	$dbh->disconnect;
}

sub truncateTables {
	my ($dbh) = @_;

	$dbh->prepare("TRUNCATE TABLE $_")->execute for ('approved', 'dates', 'final', 'estimate', 'projects')
}

sub truncateTablesOLD {
	my ($dbh) = @_;

	$dbh->prepare("TRUNCATE TABLE $_")->execute for ('projects', 'teams', 'final', 'estimate', 'quotes')
}
sub addTeamsOLD {
	my ($dbh) = @_;
 	my $sth = $dbh->prepare('INSERT INTO teams("ID","NAME") VALUES (?,?)');

	$sth->execute($_->[0], $_->[1]) || die $sth->errstr foreach @teams;
}

sub addTeams {
	my ($dbh) = @_;
 	my $sth = $dbh->prepare('INSERT INTO teams("ID") VALUES (?)');

	$sth->execute($_->[0]) || die $sth->errstr foreach @teams;
}

sub addProjects {
	my ($dbh) = @_;
 	my $sth = $dbh->prepare('INSERT INTO projects("ID","NAME") VALUES (?,?)');

	$sth->execute($_->[0], $_->[1]) || die $sth->errstr foreach @projects;
}

sub addQuotes {
	my ($dbh) = @_;
 	my $sth = $dbh->prepare('INSERT INTO quotes("PROJECT","T-1", "T0", "T1", "T2", "T3", "T4", "FINAL", "ESTIMATE") VALUES (?,?,?,?,?,?,?,?,?)');

	$sth->execute($_->[0], randomGateDates(), 0, 0) || die $sth->errstr foreach @projects;
}

sub addDates {
	my ($dbh) = @_;
	my $sth = $dbh->prepare('INSERT INTO dates("PROJECT","GATE", "DATE") VALUES (?,?,?)');

	foreach my $project (map { $_->[0] } @projects) {
		my $datevalue = time;
		foreach my $gate (@gates) {
			my $datestring;

			($datevalue, $datestring) = randomGateDate($datevalue);

			$sth->execute($project, $gate, $datestring);
		}
	}
}

sub addApproved {
	my ($dbh) = @_;
 	my $sth = $dbh->prepare('INSERT INTO approved("PROJECT", "FINAL", "ESTIMATE") VALUES (?,?,?)');

	$sth->execute($_->[0], 0, 0) || die $sth->errstr foreach @projects;
}

sub randomGateDate {
	my ($date) = @_;

	$date += (3 + int(rand(10))) * 60 * 60 * 24;
	my ($seconds, $minutes, $hours, $day_of_month, $month, $year, $wday, $yday, $isdst) = localtime($date);

	return ($date, sprintf("%02d-%02d-%02d", $day_of_month, $month + 1, $year - 100) )
}

sub randomGateDates {
	my $last = time;
	my @dates =  map {
		$last += (3 + int(rand(10))) * 60 * 60 * 24;
		my ($seconds, $minutes, $hours, $day_of_month, $month, $year, $wday, $yday, $isdst) = localtime($last);

		sprintf("%02d-%02d-%02d", $day_of_month, $month + 1, $year - 100);
	} (0 .. 5);

	print join(" / ", @dates) . "\n";
	return @dates;
}

sub addFinal {
	addQuotedata(@_, 'final');
}

sub addEstimate {
	addQuotedata(@_, 'estimate');
}

sub addQuotedataOLD {
	my ($dbh, $table) = @_;
 	my $sth = $dbh->prepare('INSERT INTO ' . $table . '("PROJECT", "TEAM", "T-1", "T0", "T1", "T2", "T3", "T4" ) VALUES (?,?,?,?,?,?,?,?)');

	foreach my $project (map { $_->[0] } @projects) {
		foreach my $team (map { $_->[0] } @teams) {
			$sth->execute($project, $team, map { 1 + int(rand(10)) } ( 0 .. 5));
		}
	}
}

sub addQuotedata {
	my ($dbh, $table) = @_;
 	my $sth = $dbh->prepare('INSERT INTO ' . $table . '("PROJECT", "TEAM", "GATE", "HOURS" ) VALUES (?,?,?,?)');

	foreach my $project (map { $_->[0] } @projects) {
		foreach my $team (map { $_->[0] } @teams) {
			foreach my $gate (@gates) {
				$sth->execute($project, $team, $gate, 1 + int(rand(10)));
			}
		}
	}
}