package sqlLayer;

use strict;
use warnings;
use Data::Dumper;

use DBI;
use phonebook;


sub new( $ $ $ ) {
	my ($class, $dbHandle, $dbType, $version) = @_;
	my $this = {};
	bless $this, $class;
	
	$this->{'dbh'} = $dbHandle;
	
	$this->{'type'} = $dbType;
	
	
	#default to 0, perfect compatablity with C primary xml parsing
	# 1 = compatibility of C combo parsing
	# 2 = multilingual support and printers_byname in drivers
	$this->{'version'} = 0;
	$this->{'version'} = $version if(defined($version));
	
	my $phonebook = phonebook->new($this->{'version'});
	$this->{'printerPhonebook'} = $phonebook->printer();
	$this->{'driverPhonebook'}  = $phonebook->driver();
	$this->{'optionPhonebook'}  = $phonebook->option();
	$this->{'schemaPhonebook'}  = $phonebook->schema();
	
	
	return $this;
}

#TODO: Move this and the version in xmlParse to a helper lib
sub getCleanId {
	my ($id) = @_;
	$id =~ s/^[^\/]*\///;
	#remove everything before the leading slash
	return $id;
}

sub getSchema {
	my ($this, $table) = @_;
	
	return $this->{'schemaPhonebook'}{$table};
}

sub initDatabase {
	my ($this) = @_;
	
	#Create the Tables
	foreach my $table (keys %{$this->{'schemaPhonebook'}}) {
		my $schema = $this->getSchema($table);
		my $sth = $this->{'dbh'}->prepare($schema);
		
		$sth->execute();
	}
	$this->{'dbh'}->commit();
}

sub getSortedKeys {
	my ($this, $data) = @_;
	
	my @keys;
	foreach my $key (keys %{$data}) {
		push(@keys, $key);
	}
	
	@keys = sort(@keys);
	
	#create a string that sums the contents of @keys, this allows us to identify 
	#similar sets of keys
	my $sumString;
	foreach my $key (@keys) {
		$sumString .= $key;
	}
	
	return (\@keys, $sumString);
}

sub getPreparedStatement {
	my ($this, $wantedTable, $data) = @_;

	my ($sortedKeys, $summedKeys) = $this->getSortedKeys($data);
	
	#use cached copy if present
	if (defined($this->{'cache'}{'preparedStatements'}{$wantedTable}{$summedKeys})) {
		return $this->{'cache'}{'preparedStatements'}{$wantedTable}{$summedKeys};
	}
	
	#Create the SQL
	my $statementBegining = "INSERT INTO ".$wantedTable."(";
	
	my $tableKeys = join(",", @{$sortedKeys});
	
	my $statementMiddle = ") VALUES (";
	
	my $bindValues;
	for(my $i = 0; $i < @{$sortedKeys}; $i++) {
		$bindValues .= ',' if ($i > 0);
		$bindValues .= '?';
	}
	
	my $statementEnd = ");";
	
	my $statementSql = $statementBegining . $tableKeys . 
	                      $statementMiddle . $bindValues . $statementEnd;
	
	my $sth = $this->{'dbh'}->prepare($statementSql);
	
	#add the sth to our cache
	$this->{'cache'}{'preparedStatements'}{$wantedTable}{$summedKeys} = $sth;
	
	return $sth;
}


sub getConstraints {
	my ($this, $data, $optionId, $choiceId) = @_;
	
	my @constraints;
	foreach my $constraint (@{$data}) {
		my %preparedConstraint;
		$preparedConstraint{'option_id'} = getCleanId($optionId);
		
		$preparedConstraint{'choice_id'} = getCleanId($choiceId);
		
		$preparedConstraint{'driver'} = $constraint->{'driver'} 
			if($constraint->{'driver'});
			
		$preparedConstraint{'printer'} = getCleanId($constraint->{'printer'}) 
			if($constraint->{'printer'});
		
		$preparedConstraint{'defval'} = $constraint->{'arg_defval'} 
			if($constraint->{'arg_defval'});
			
		if($constraint->{'sense'}) {
			#correct, the schema uses strings not booleans =\
			#or even ints
			$preparedConstraint{'sense'} = 'true';
		} else {
			$preparedConstraint{'sense'} = 'false';
		}
		
		$preparedConstraint{'is_choice_constraint'} = 1 if($choiceId);
		
		push(@constraints, \%preparedConstraint);
	}
	
	return \@constraints;
}

sub setPreparedData {
	my ($this, $data, $phonebook) = @_;
	#We do most of our work here. This function takes the fooamtic perl data
	#structure and flattens things to match the database schema.
	
	
	#compute the namespace from the phonebook
	my $nodePath = $phonebook->[0][0];#the nodepath of the first element
	$nodePath =~ m!^/([^/]*)/!;
	my $namespace = $1;
		
	#We sort the data by table and rename if needed.
	foreach my $element (@{$phonebook}) {
		my ($ignore, $source, $mainGroup, $table, $destination, $minorGroup) = @{$element};
		$destination = $source if (!$destination);
		
		#Only sort if the table is defined and if the destination key has not 
		#been set, does not work for complex types 
		if($table && !exists $data->{'prepared'}{$table}{$destination}) {
			
			#The business loop, complex data gets flattened
			
			if($minorGroup == 3) {
				next() if !defined($data->{$source});
				if($namespace eq 'driver') {#inverse the boolean
					$data->{'prepared'}{$table}{$destination} = int !$data->{$source};
				}
			
			} elsif($minorGroup == 4) {
				next() if !defined($data->{$source});
				if($namespace eq 'option') {
					$data->{'prepared'}{$table}{$destination} = getCleanId($data->{$source});
				} elsif($namespace eq 'driver' && $data->{$source}) {
					$data->{'prepared'}{$table}{$destination} = $data->{$source};
				}
			
			} elsif($minorGroup == 7) {#multilingual comments
				foreach my $lang (keys %{$data->{$source}}) {
					if($namespace eq 'printer') {
						my %preparedComment;
						$preparedComment{'id'} = $data->{'id'};
						$preparedComment{'lang'} = $lang;
						$preparedComment{'comments'} = $data->{'comments'}{$lang};
						push(@{$data->{'prepared'}{$table}{$destination}}, \%preparedComment);
					} elsif ($namespace eq 'option') {
						my %preparedComment;
						$preparedComment{'id'} = getCleanId($data->{'idx'});
						$preparedComment{'lang'} = $lang;
						$preparedComment{'longname'} = $data->{'comments'}{$lang};
						push(@{$data->{'prepared'}{$table}{$destination}}, \%preparedComment);
					} elsif ($namespace eq 'driver') {
						my %preparedComment;
						$preparedComment{'id'} = $data->{'name'};
						$preparedComment{'lang'} = $lang;
						$preparedComment{'comments'} = $data->{'comments'}{$lang};
						$preparedComment{'licensetext'} = $data->{'licensetexts'}{$lang};
						$preparedComment{'licenselink'} = $data->{'licenselink'};#fake multilang
						$preparedComment{'supplier'} = $data->{'supplier'};#fake multilang
						push(@{$data->{'prepared'}{$table}{$destination}}, \%preparedComment);
					}
				}
				
			} elsif($mainGroup == 11) {
				next() if !defined($data->{$source});
				if($namespace eq 'option') {#option constraints
					my $constraints = $this->getConstraints($data->{$source},$data->{'idx'}, '');
					
					$data->{'prepared'}{$table}{$destination} = $constraints;
				}
				
			} elsif($mainGroup == 12) {
				next() if !defined($data->{$source});
				if($namespace eq 'option') {#choices
					
					#We must merge the choice constraints with the option constraints
					my $allConstraints;
					if (defined($data->{'prepared'}{'option_constraint'}{'constraints'})) {
						$allConstraints = $data->{'prepared'}{'option_constraint'}{'constraints'};
					} else {
						$allConstraints = []; 
					}
					
					my @choices = ();
					
					my @comments = ();
					
					foreach my $choice (@{$data->{$source}}) {
						#Choice constraints, go to option_constraint
						if (defined($choice->{'constraints'})) {
							my $constraints = $this->getConstraints($choice->{'constraints'},$data->{'idx'}, $choice->{'idx'});
							@{$allConstraints} = (@{$allConstraints}, @{$constraints});
						}
						
						#Choice data, goes to option_choice
						my %choice;
						$choice{'id'} = getCleanId($choice->{'idx'});
						$choice{'option_id'} = getCleanId($data->{'idx'});
						$choice{'longname'} = $choice->{'comment'}
							if $choice->{'comment'};
						$choice{'shortname'} = $choice->{'value'}
							if $choice->{'value'};
						$choice{'driverval'} = $choice->{'driverval'}
							if $choice->{'driverval'};
						
						push(@choices, \%choice);
						
						#choice comments
						foreach my $lang (keys %{$choice->{'comments'}}) {
							my %comment;
							$comment{'longname'} = $choice->{'comments'}{$lang};
							$comment{'lang'} = $lang;
							$comment{'id'} = getCleanId($choice->{'idx'});
							$comment{'option_id'} = getCleanId($data->{'idx'});
							push(@comments, \%comment);
						}
						
					}
					$data->{'prepared'}{$table}{$destination} = \@choices;
					
					$data->{'prepared'}{$table.'_translation'}{'comments'} = \@comments;
					
					#If there were no option constraints we must assign
					#our choice constraints to the constraints prepared data,
					#only if we were able to find a choice with constraints
					if(@{$allConstraints}) {
						$data->{'prepared'}{'option_constraint'}{'constraints'} = $allConstraints;
					}
				}
				
			} elsif($mainGroup == 13) { 
				next() if !defined($data->{$source});
				if($namespace eq 'printer') { #Languages
					foreach my $lang (@{$data->{$source}}) {
						my $name = $lang->{'name'};
						$data->{'prepared'}{$table}{$name}  = 1;
						
						if(defined($lang->{'level'}) && !($name eq 'proprietary') ) {
							$data->{'prepared'}{$table}{$name.'_level'} = $lang->{'level'};
						}
					}
					
				} elsif ($namespace eq 'driver') { #support contact
					my %contact;#the contact for the non-translation table
					my @contacts;
					foreach my $contact (@{$data->{$source}}) {
						$contact{'driver_id'} = $data->{'name'};
						$contact{'level'} = $contact->{'level'};
						$contact{'url'} = $contact->{'url'};
						$contact{'description'} = $contact->{'description'};
						
						#a clone, for use in the translated table
						my %multilangContact = %contact;
						
						#support contacts is not an actually multi-lingual field
						#but this way support can be added in the future
						if($contact->{'lang'}) {
							$multilangContact{'lang'} = $contact->{'lang'};
						} else {
							$multilangContact{'lang'} = 'en';
						}
						push(@contacts, \%multilangContact);
					}
					
					$data->{'prepared'}{$table} = \%contact;
					$data->{'prepared'}{$table.'_translation'}{'contacts'} = \@contacts;
					#print Dumper($data);
					#exit();
				}
			} elsif($mainGroup == 14) {
				next() if !defined($data->{$source});
				if($namespace eq 'driver') {#packages
					my %package;
					foreach my $package (@{$data->{$source}}) {
						$package{'driver_id'} = $data->{'name'};
						$package{'scope'} = $package->{'scope'};
						$package{'fingerprint'} = $package->{'fingerprint'}
							if($package->{'fingerprint'});
						$package{'name'} = $package->{'url'};
					}
					$data->{'prepared'}{$table} = \%package;
				}
			} elsif( defined($data->{$source})) {
				$data->{'prepared'}{$table}{$destination} = $data->{$source};
			}
		}
	}
}

sub insertData {
	my ($this, $data, $table) = @_;
	
	my $sth = $this->getPreparedStatement($table, $data);
	
	#Sort data alphabetically, required for prepared statements
	my ($sortedKeys) = $this->getSortedKeys($data);
	my @sortedData;
	foreach my $key (@{$sortedKeys}) {
		push(@sortedData, $data->{$key});
	}
	
	
	if( !$sth->execute(@sortedData) ) {
		print Dumper($data);
		die $this->{'dbh'}->errstr . "\n------\n";
	}
}

sub insertArrayData {
	my ($this, $data, $table, $key) = @_;
	foreach my $constraint (@{ $data->{'prepared'}{$table}{$key} }) {
		$this->insertData($constraint, $table);
	}
}


sub pushOption {
	my ($this, $option) = @_;
	$this->setPreparedData($option, $this->{'optionPhonebook'});
	
	$this->insertData($option->{'prepared'}{'options'},'options');
	$this->insertArrayData($option, 'options_translation', 'comments');
	$this->insertArrayData($option, 'option_constraint', 'constraints');
	$this->insertArrayData($option, 'option_choice', 'vals');
	$this->insertArrayData($option, 'option_choice_translation', 'comments');
	
	$this->{'dbh'}->commit;
}

sub pushPrinter {
	my ($this, $printer) = @_;
	
	$this->setPreparedData($printer, $this->{'printerPhonebook'});
	
	$this->insertData($printer->{'prepared'}{'printer'},'printer');
	$this->insertArrayData($printer, 'printer_translation', 'comments');
	
	$this->{'dbh'}->commit;
}

sub pushDriver {
	my ($this, $driver) = @_;
	
	$this->setPreparedData($driver, $this->{'driverPhonebook'});
	
	$this->insertData($driver->{'prepared'}{'driver'},'driver');
	$this->insertArrayData($driver, 'driver_translation', 'comments');
	if(%{ $driver->{'prepared'}{'driver_support_contact'} }) {
		$this->insertData($driver->{'prepared'}{'driver_support_contact'},'driver_support_contact');
		$this->insertArrayData($driver, 'driver_support_contact_translation', 'contacts');
	}
	if(%{ $driver->{'prepared'}{'driver_package'} }) {
		$this->insertData($driver->{'prepared'}{'driver_package'},'driver_package');
	}
	
	$this->{'dbh'}->commit;
}

1;
