package journal_database;
use DBD::SQLite;
use DBI;
use Data::Dumper;
my $dbh = DBI->connect("dbi:SQLite:dbname=./database/journals.db","","");


CREATE TABLE journals(
    id INTEGER PRIMARY KEY NOT NULL, --optional 
    date INTEGER NOT NULL,
    fulltext TEXT,
    image_url TEXT
);

sub remove_from_db{
  my $id_to_remove = shift;
  my $sth = $dbh->prepare(
    "DELETE FROM journals ".
    "WHERE id=$id_to_remove;"
  );
  $sth->execute();
}
sub get_entry_by_date {
	my $date_int = shift;
	my $sth = $dbh->prepare(
		"SELECT *".
		"FROM journals ".
		"WHERE `date`=? "
	);
	$sth->execute($date_int);
	return $sth->fetchrow_hashref();
}

sub add_journal_entry{
    my $entry = shift; 
    my $sqlCmd = "insert into journals values (?,?,?,?);" ;
#                  ."on duplicate key update ".
#                  "id = ?,".
#                  "date = ?,".
#                  "fulltext = ?,".
#                  "image_url = ?,".
#                  ";";
    my $sth = $dbh->do($sqlCmd,
                  undef,
                  $entry->{id},
                  $entry->{date} || time(),
                  $entry->{fulltext},
                  $entry->{image_url}
    );
  }

  my $entry = {};
  $entry->{id} = '12';
  $entry->{date} = '123432';
  $entry->{fulltext} = 'this is a test of the fulltext'; 
  $entry->{image_url} = 'this is a test of the image url';
  add_journal_entry($entry); 
  print Dumper get_entry_by_date(123432);
  remove_from_db(12);