package Model_SQLite;
use warnings;
use 5.014;
use utf8;
use DBI;

my $dbname = 'adressen.db';
my $table = 'adressen';

sub new {
   my ( $class ) = @_;
   my $dbh = DBI->connect( "dbi:SQLite:dbname=$dbname", '', '',
   { RaiseError => 1, PrintError => 0, AutoCommit => 1, sqlite_unicode => 1, }
   ) or die $DBI::errstr;
   $dbh->do( "PRAGMA cache_size = 400000" );
   $dbh->do( "PRAGMA synchronous = OFF" );
   $dbh->do( "CREATE TABLE IF NOT EXISTS $table 
      ( vorname STRING NOT NULL, nachname STRING NOT NULL, 
      steuernummer STRING PRIMARY KEY, strasse STRING NOT NULL, 
      nummer STRING NOT NULL, ort STRING NOT NULL, plz STRING NOT NULL )" );
   my $self = bless { dbh => $dbh }, $class;
   return $self;
}

sub insert_db {
   my ( $self, %opts ) = @_;
   my @cols = ( qw( vorname nachname steuernummer strasse nummer ort plz ) );
   for ( @cols ) {
      die "Option '$_' is missing" if not exists $opts{$_};
      die "Value for '$_' is missing" if not $opts{$_};
   }
   my $sth = $self->{dbh}->prepare( 
      "INSERT INTO  $table (" . join( ', ', @cols ) . ') 
      VALUES (' . join( ', ', ( '?' ) x @cols ) . ')' 
   );
   $sth->execute( @opts{@cols} );
}

sub print_db {
   my ( $self ) = @_;
   my $sth = $self->{dbh}->prepare( 
      "SELECT * FROM $table ORDER BY nachname, vorname" 
   ); 
   $sth->execute;
   return $sth;
}

sub search_entry_steuernummer {
   my ( $self, $steuernummer ) = @_;
   my $ref = $self->{dbh}->selectall_hashref( 
      "SELECT * FROM $table  WHERE steuernummer == ?", 'steuernummer', {}, $steuernummer 
   ); 
   my @keys = keys %$ref;
   die if @keys != 1;
   return $ref->{$keys[0]};
}

sub search_entries_nachname {
   my ( $self, $nachname ) = @_;
   my $rows;
   my $sth = $self->{dbh}->prepare( 
      "SELECT vorname, nachname, steuernummer FROM $table WHERE nachname == ?" 
   );
   $sth->execute( $nachname );
   while ( my $row = $sth->fetchrow_hashref() ) {
      push @$rows, $row;
   }
   return $rows;
}

1;