package GOSA::DBsqlite;

use strict;
use warnings;
use Carp;
use DBI;
use Data::Dumper;
use GOSA::GosaSupportDaemon;
use Time::HiRes qw(usleep);
use Fcntl qw/:DEFAULT :flock/; # import LOCK_* constants

my $col_names = {};

sub new {
	my $class = shift;
	my $db_name = shift;

	#my $lock = $db_name.".si.lock";
	my $lock = $db_name;
	my $self = {dbh=>undef,db_name=>undef,db_lock=>undef,db_lock_handle=>undef};
	my $dbh = DBI->connect("dbi:SQLite:dbname=$db_name", "", "", {RaiseError => 1, AutoCommit => 1, PrintError => 0});
	my $sth = $dbh->prepare("pragma integrity_check");
	$sth->execute();
	my @ret = $sth->fetchall_arrayref();
	$sth->finish();
	if(length(@ret)==1 && $ret[0][0][0] eq 'ok') {
		&main::daemon_log("DEBUG: Database image $db_name is ok", 7);
	} else {
		&main::daemon_log("ERROR: Database image $db_name is malformed, creating new database.", 1);
		$dbh->disconnect();
		unlink($db_name);
		$dbh = DBI->connect("dbi:SQLite:dbname=$db_name", "", "", {RaiseError => 1, AutoCommit => 1});
	}
	$self->{dbh} = $dbh;
	$self->{db_name} = $db_name;
	$self->{db_lock} = $lock;
	bless($self,$class);
	return($self);
}


sub lock {
	my $self = shift;
	if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
		&main::daemon_log("0 ERROR: GOSA::DBsqlite::lock was called static! Statement was '$self'!", 1);
		return;
	}
	if(not ref $self->{db_lock_handle}) {
		sysopen($self->{db_lock_handle}, $self->{db_lock}, O_RDWR) or &main::daemon_log("0 ERROR: Opening the database ".$self->{db_name}." failed with $!", 1);
	}
	my $lock_result = flock($self->{db_lock_handle}, LOCK_EX);
	if($lock_result==1) {
		seek($self->{db_lock_handle}, 0, 2);
		&main::daemon_log("0 DEBUG: Acquired lock for database ".$self->{db_name}, 7);
	} else {
		&main::daemon_log("0 ERROR: Could not acquire lock for database ".$self->{db_name}, 1);
	}
	return;
}


sub unlock {
	my $self = shift;
	if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
		&main::daemon_log("0 ERROR: GOSA::DBsqlite::unlock was called static! Statement was '$self'!", 1);
		return;
	}
	if(not ref $self->{db_lock_handle}) {
		&main::daemon_log("0 BIG ERROR: Lockfile for database ".$self->{db_name}."got closed within critical section!", 1);
	}
	flock($self->{db_lock_handle}, LOCK_UN);
	&main::daemon_log("0 DEBUG: Released lock for database ".$self->{db_name}, 7);
	return;
}


sub create_table {
	my $self = shift;
	if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
		&main::daemon_log("0 ERROR: GOSA::DBsqlite::create_table was called static! Statement was '$self'!", 1);
		return;
	}
	my $table_name = shift;
	my $col_names_ref = shift;
	my @col_names;
	my @col_names_creation;
	foreach my $col_name (@$col_names_ref) {
		# Save full column description for creation of database
		push(@col_names_creation, $col_name);
		my @t = split(" ", $col_name);
		my $column_name = $t[0];
		# Save column name internally for select_dbentry
		push(@col_names, $column_name);
	}
	
	$col_names->{ $table_name } = \@col_names;
	my $col_names_string = join(", ", @col_names_creation);
	my $sql_statement = "CREATE TABLE IF NOT EXISTS $table_name ( $col_names_string )"; 
	my $res = $self->exec_statement($sql_statement);

	return 0;
}


sub add_dbentry {
	my $self = shift;
	my $arg = shift;
	my $res = 0;   # default value

	# if dbh not specified, return errorflag 1
	my $table = $arg->{table};
	if( not defined $table ) {
		return 1 ;
	}

	# if timestamp is not provided, add timestamp   
	if( not exists $arg->{timestamp} ) {
		$arg->{timestamp} = &get_time;
	}

	# check primkey and run insert or update
	my $primkeys = $arg->{'primkey'};
	my $prim_statement="";
	if( 0 != @$primkeys ) {   # more than one primkey exist in list
		my @prim_list;
		foreach my $primkey (@$primkeys) {
			if( not exists $arg->{$primkey} ) {
				return (3, "primkey '$primkey' has no value for add_dbentry");
			}
			push(@prim_list, "$primkey='".$arg->{$primkey}."'");
		}
		$prim_statement = "WHERE ".join(" AND ", @prim_list);

		# check wether primkey is unique in table, otherwise return errorflag
		my $sql_statement = "SELECT * FROM $table $prim_statement";
		$res = @{ $self->exec_statement($sql_statement) };
	}

	# primkey is unique or no primkey specified -> run insert
	if ($res == 0) {
		# fetch column names of table
		my $col_names = &get_table_columns($self, $table);

		my $create_id=0;
		foreach my $col_name (@{$col_names}) {
			if($col_name eq "id" && (! exists $arg->{$col_name})) {
				$create_id=1;
			}
		}
		# assign values to column name variables
		my @col_list;
		my @val_list;
		foreach my $col_name (@{$col_names}) {
			# use function parameter for column values
			if (exists $arg->{$col_name}) {
				push(@col_list, "'".$col_name."'");
				push(@val_list, "'".$arg->{$col_name}."'");
			}
		}

		my $sql_statement;
		if($create_id==1) {
			$sql_statement = "INSERT INTO $table (id, ".join(", ", @col_list).") VALUES (null, ".join(", ", @val_list).")";
		} else {
			$sql_statement = "INSERT INTO $table (".join(", ", @col_list).") VALUES (".join(", ", @val_list).")";
		}
		my $db_res;
		my $success=0;
		$self->lock();
		eval {
			my $sth = $self->{dbh}->prepare_cached($sql_statement);
			$db_res = $sth->execute();
			$sth->finish();
			&main::daemon_log("0 ERROR: Execution of statement '$sql_statement' succeeded!", 7);
			$success = 1;
		};
		if($@) {
			eval {
				$self->{dbh}->do("ANALYZE");
				$self->{dbh}->do("VACUUM");
			};
		}
		if($success==0) {
			eval {
				my $sth = $self->{dbh}->prepare_cached($sql_statement);
				$db_res = $sth->execute();
				$sth->finish();
				&main::daemon_log("0 ERROR: Execution of statement '$sql_statement' succeeded!", 7);
				$success = 1;
			};
			if($@) {
				eval {
					$self->{dbh}->do("ANALYZE");
					$self->{dbh}->do("VACUUM");
				};
			}
		}
		if($success==0) {
			eval {
				my $sth = $self->{dbh}->prepare_cached($sql_statement);
				$db_res = $sth->execute();
				$sth->finish();
				&main::daemon_log("0 ERROR: Execution of statement '$sql_statement' succeeded!", 7);
				$success = 1;
			};
			if($@) {
				&main::daemon_log("0 ERROR: Execution of statement '$sql_statement' failed with $@", 1);
			}
		}
		$self->unlock();

		if( $db_res != 1 ) {
			return (4, $sql_statement);
		}

		# entry already exists -> run update
	} else  {
		my @update_l;
		while( my ($pram, $val) = each %{$arg} ) {
			if( $pram eq 'table' ) { next; }
			if( $pram eq 'primkey' ) { next; }
			push(@update_l, "$pram='$val'");
		}
		my $update_str= join(", ", @update_l);
		$update_str= " SET $update_str";

		my $sql_statement= "UPDATE $table $update_str $prim_statement";
		my $db_res = &update_dbentry($self, $sql_statement );
	}

	return 0;
}
sub update_dbentry {
	my ($self, $sql)= @_;
	if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
		&main::daemon_log("0 ERROR: GOSA::DBsqlite::update_dbentry was called static! Statement was '$self'!", 1);
		return;
	}
	my $db_answer= $self->exec_statement($sql); 
	return $db_answer;
}


sub del_dbentry {
	my ($self, $sql)= @_;;
	if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
		&main::daemon_log("0 ERROR: GOSA::DBsqlite::del_dbentry was called static! Statement was '$self'!", 1);
		return;
	}
	my $db_res= $self->exec_statement($sql);
	return $db_res;
}


sub get_table_columns {
	my $self = shift;
	if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
		&main::daemon_log("0 ERROR: GOSA::DBsqlite::get_table_columns was called static! Statement was '$self'!", 1);
		return;
	}
	my $table = shift;
	my @column_names;

	if(exists $col_names->{$table}) {
		@column_names = @{$col_names->{$table}};
	} else {
		my @res;
		foreach my $column ( $self->exec_statement ( "pragma table_info('$table')" ) ) {
			push(@column_names, @$column[1]);
		}
	}
	return \@column_names;

}


sub select_dbentry {
	my ($self, $sql)= @_;
	if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
		&main::daemon_log("0 ERROR: GOSA::DBsqlite::select_dbentry was called static! Statement was '$self'!", 1);
		return;
	}
	my $error= 0;
	my $answer= {};
	my $db_answer= $self->exec_statement($sql); 
	my @column_list;

	# fetch column list of db and create a hash with column_name->column_value of the select query
	$sql =~ /SELECT ([\S\s]*?) FROM ([\S]*?)( |$)/g;
	my $selected_cols = $1;
	my $table = $2;

	# all columns are used for creating answer
	if ($selected_cols eq '*') {
		@column_list = @{ $self->get_table_columns($table) };    

		# specific columns are used for creating answer
	} else {
		# remove all blanks and split string to list of column names
		$selected_cols =~ s/ //g;          
		@column_list = split(/,/, $selected_cols);
	}

	# create answer
	my $hit_counter = 0;
	my $list_len = @column_list;
	foreach my $hit ( @{$db_answer} ){
		$hit_counter++;
		for ( my $i = 0; $i < $list_len; $i++) {
			$answer->{ $hit_counter }->{ $column_list[$i] } = @{ $hit }[$i];
		}
	}

	return $answer;  
}


sub show_table {
	my $self = shift;
	if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
		&main::daemon_log("0 ERROR: GOSA::DBsqlite::show_table was called static! Statement was '$self'!", 1);
		return;
	}
	my $table_name = shift;

	my $sql_statement= "SELECT * FROM $table_name ORDER BY timestamp";
	my $res= $self->exec_statement($sql_statement);
	my @answer;
	foreach my $hit (@{$res}) {
		push(@answer, "hit: ".join(', ', @{$hit}));
	}

	return join("\n", @answer);
}


sub exec_statement {
	my $self = shift;
	my $sql_statement = shift;

	if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
		&main::daemon_log("0 ERROR: GOSA::DBsqlite::exec_statement was called static! Statement was '$self'!", 1);
		return;
	}
	if(not defined($sql_statement) or length($sql_statement) == 0) {
		&main::daemon_log("0 ERROR: GOSA::DBsqlite::exec_statement was called with empty statement!", 1);
		return;
	}

	my @db_answer;
	my $success= 0;
	$self->lock();
	# Give three chances to the sqlite database
	# 1st chance
	eval {
		my $sth = $self->{dbh}->prepare_cached($sql_statement);
		my $res = $sth->execute();
		@db_answer = @{$sth->fetchall_arrayref()};
		$sth->finish();
		$success=1;
		&main::daemon_log("0 DEBUG: $sql_statement succeeded.", 7);
	};
	if($@) {
		eval {
			$self->{dbh}->do("ANALYZE");
			$self->{dbh}->do("VACUUM");
		};
	}
	if($success) {
		$self->unlock();
		return \@db_answer ;
	}
	
	# 2nd chance
	eval {
		DBI->trace(2) if($main::verbose >= 7);
		my $sth = $self->{dbh}->prepare_cached($sql_statement);
		my $res = $sth->execute();
		@db_answer = @{$sth->fetchall_arrayref()};
		$sth->finish();
		DBI->trace(0);
		$success=1;
		&main::daemon_log("0 DEBUG: $sql_statement succeeded.", 7);
	};
	if($@) {
		eval {
			$self->{dbh}->do("ANALYZE");
			$self->{dbh}->do("VACUUM");
		};
		DBI->trace(0);
	}
	if($success) {
		$self->unlock();
		return \@db_answer ;
	}

	# 3rd chance
	eval {
		DBI->trace(2) if($main::verbose >= 7);
		my $sth = $self->{dbh}->prepare_cached($sql_statement);
		my $res = $sth->execute();
		@db_answer = @{$sth->fetchall_arrayref()};
		$sth->finish();
		DBI->trace(0);
		&main::daemon_log("0 DEBUG: $sql_statement succeeded.", 7);
	};
	if($@) {
		DBI->trace(0);
		&main::daemon_log("ERROR: $sql_statement failed with $@", 1);
	}
	# TODO : maybe an error handling and an erro feedback to invoking function
	#my $error = @$self->{dbh}->err;
	#if ($error) {
	#	my $error_string = @$self->{dbh}->errstr;
	#}

	$self->unlock();
	return \@db_answer;
}


sub exec_statementlist {
	my $self = shift;
	my $sql_list = shift;
	if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
		&main::daemon_log("0 ERROR: GOSA::DBsqlite::exec_statementlist was called static!", 1);
		return;
	}
	my @db_answer;

	foreach my $sql_statement (@$sql_list) {
		if(defined($sql_statement) && length($sql_statement) > 0) {
			push @db_answer, $self->exec_statement($sql_statement);
		} else {
			next;
		}
	}

	return \@db_answer;
}


sub count_dbentries {
	my ($self, $table)= @_;
	if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
		&main::daemon_log("0 ERROR: GOSA::DBsqlite::count_dbentries was called static!", 1);
		return;
	}
	my $error= 0;
	my $count= -1;

	my $sql_statement= "SELECT count() FROM $table";
	my $db_answer= $self->select_dbentry($sql_statement); 
	if(defined($db_answer) && defined($db_answer->{1}) && defined($db_answer->{1}->{'count()'})) {
		$count = $db_answer->{1}->{'count()'};
	}

	return $count;
}


sub move_table {
	my ($self, $from, $to) = @_;
	if(not defined($self) or ref($self) ne 'GOSA::DBsqlite') {
		&main::daemon_log("0 ERROR: GOSA::DBsqlite::move_table was called static!", 1);
		return;
	}

	my $sql_statement_drop = "DROP TABLE IF EXISTS $to";
	my $sql_statement_alter = "ALTER TABLE $from RENAME TO $to";

	&main::daemon_log("INFO move_table called successfully! Exiting!",1 );
	
	$self->lock();
	eval {
		$self->{dbh}->begin_work();
		$self->{dbh}->do($sql_statement_drop);
		$self->{dbh}->do($sql_statement_alter);
		$self->{dbh}->commit();
	};
	if($@) {
		$self->{dbh}->rollback();
		eval {
			$self->{dbh}->do("ANALYZE");
		};
		if($@) {
			&main::daemon_log("ERROR: 'ANALYZE' on database '".$self->{db_name}."' failed with $@", 1);
		}
		eval {
			$self->{dbh}->do("VACUUM");
		};
		if($@) {
			&main::daemon_log("ERROR: 'VACUUM' on database '".$self->{db_name}."' failed with $@", 1);
		}
	}

	eval {
		$self->{dbh}->begin_work();
		$self->{dbh}->do($sql_statement_drop);
		$self->{dbh}->do($sql_statement_alter);
		$self->{dbh}->commit();
	};
	if($@) {
		$self->{dbh}->rollback();
		eval {
			$self->{dbh}->do("ANALYZE");
		};
		if($@) {
			&main::daemon_log("ERROR: 'ANALYZE' on database '".$self->{db_name}."' failed with $@", 1);
		}
		eval {
			$self->{dbh}->do("VACUUM");
		};
		if($@) {
			&main::daemon_log("ERROR: 'VACUUM' on database '".$self->{db_name}."' failed with $@", 1);
		}
	}
	
	eval {
		$self->{dbh}->begin_work();
		$self->{dbh}->do($sql_statement_drop);
		$self->{dbh}->do($sql_statement_alter);
		$self->{dbh}->commit();
	};
	if($@) {
		$self->{dbh}->rollback();
		&main::daemon_log("0 ERROR: GOSA::DBsqlite::move_table crashed! Operation failed with $@", 1);
	}
	$self->unlock();

	return;
} 


1;
