#!/usr/bin/env perl
use strict;
use warnings;

# Max Values
my $MaxbookplateleafId = 0;
my $MaxboxId = 0;
my $MaxcameraId = 0;
my $MaxcollectionId = 0;

sub GetMaxDbItem
{
   $col1   = $_[0];
   $table  = $_[1];
   $id     = $_[2];

   $query = "SELECT max($col1) FROM $table\;";
   $queryhandle = $dbh->prepare($query);
   $queryhandle->execute();
   $queryhandle->bind_col(1, \${$id});
   $queryhandle->fetch();
   if($debug eq 2) { print "$table MaxId: ${$id}\n"; }
   $queryhandle->finish();
}

sub PreloadMaxValues
{
   # BookplateLeaf
   GetMaxDbItem ("BookplateLeafId", "BookplateLeaf", \$MaxbookplateleafId);

   # Box
   GetMaxDbItem ("BoxId", "Box", \$MaxboxId);

   # Camera
   GetMaxDbItem ("CameraId", "Camera", \$MaxcameraId);
}

##</code><code>##

sub AddId
{
   my $Index = $_[0];
   my $Value;
   my $MaxPtr = "";

   ${$id} = "";
   if(exists $TableEntries{ $Index }) {
      $Value  = $TableEntries{ $Index }[0];
      print "AddId $Index\n";
      print "Value: $Value\n";
      $query = "SELECT $Index"."Id FROM $Index  where $Index"."name = \'$Value\'\;";
      if( $debug eq 2 ) { print "\nquery: $query\n"; }
      $queryhandle = $dbh->prepare($query);
      $queryhandle->execute();
      $queryhandle->bind_col(1, \${$id});
      $queryhandle->fetch();
      if($debug eq 2) { print "value: $Value id: ${$id} [end]\n"; }
      if( ${$id} eq "" ) {
         # Insert new item
         $MaxPtr = "\$"."Max"."$Index"."Id";
         print "MaxPtr: $MaxPtr, ${$MaxPtr}\n";
         my $newinsert = "Insert into $Index values(";
         # Rest goes here
      }
      $queryhandle->finish();
   }
}

##</code><code>##

sub CreateTableEntries()
{
   $ItemNo = 0;
   &FixedEntity;
   if  ( $EntityRec ne "" ) {
      if ($debug eq 2) { print "EntityRec: $EntityRec\n" };
      if ($count++ eq 25) { die; }   #debug only, limit to 25 XML documents   
   } else {
      print "Identifier missing for file $filename\n";
      return;
   }
   AddId("bookplateleaf");
   AddId("box");
}

##</code><code>##

CREATE TABLE bookplateleaf (
  bookplateleafId INTEGER  NOT NULL  ,
  bookplateleafname VARCHAR      ,
PRIMARY KEY(bookplateleafId));
insert into bookplateleaf values(0, "Dummy");

CREATE TABLE box (
  boxId INTEGER  NOT NULL  ,
  boxname VARCHAR      ,
PRIMARY KEY(BoxId));
insert into box values(0, "Dummy");