#!/usr/bin/perl -w

=head1 NAME

  Image Annotation Data Importer

=head1 VERSION

  0.1

=head1 DESCRIPTION 

  Read the data from the tab de;imited files present in the image_data directory present adjacent to this file
  Add more description

=head1 USAGE

  annotation_data_importer.pl

=cut

# general

use strict;
use English;
use DBI;
use DbiFloret qw(dbconnect);
use String::Util qw(:all);

# configurations

use constant IMAGE_DATA_DIR => "image_data";

sub establish_db_connection
{
  return DbiFloret::dbconnect();
}


sub null_if_empty
{
  return (defined $_[0]) ? trim($_[0]) : 'NULL';
}


sub insert_collection_data_into_database
{
  my($dbh, $collection_data) = @_;
  my @collection_data_fields = split(/\t/, $collection_data);


  #creating image_source
  my($source_name)      = null_if_empty($collection_data_fields[0]);
  my($url) 		= null_if_empty($collection_data_fields[1]);
  my($contact_email)    = null_if_empty($collection_data_fields[3]);
  my($contributor_name) = null_if_empty($collection_data_fields[2]);

  my $image_source_id;
  my $sth = $dbh->prepare("call import_image_source('$source_name', '$url', '$contact_email', '$contributor_name');");
  $sth->execute();
  $sth->bind_col(1, \$image_source_id);
  $sth->fetch();


  #creating image_source_version
  my($source_version)    = null_if_empty($collection_data_fields[6]);
  my($contribution_date) = null_if_empty($collection_data_fields[4]);
  my($publication_id)    = null_if_empty($collection_data_fields[5]);

  my $image_source_version_id;
  my $sth = $dbh->prepare("call import_image_source_version('$image_source_id', '$source_version', '$contribution_date', '$publication_id');");
  $sth->execute();
  $sth->bind_col(1, \$image_source_version_id);
  $sth->fetch();

  return $image_source_version_id;

}


sub insert_image_data_into_database
{  

  my($dbh, $image_data, $image_source_version_id) = @_;
  my @image_data_fields = split(/\t/, $image_data);


  # Creating curator data and storing the respective id
  my($curator_first_name, $curator_last_name) = split(/ /, trim($image_data_fields[6]), 2);
  my $curator_email			      = null_if_empty($image_data_fields[7]);
  my $curator_affiliation                     = null_if_empty($image_data_fields[8]);
  
  my $curator_id;
  my $sth = $dbh->prepare("call import_curator('$curator_first_name', '$curator_last_name', '$curator_email', '$curator_affiliation');");
  $sth->execute();
  $sth->bind_col(1, \$curator_id);
  $sth->fetch();


  # Creating taxon data and storing the respective id
  my $species 		     = null_if_empty($image_data_fields[4]);
  my ($genus, $species_name) = split(/\s/, $species, 2);
  my $species_id             = null_if_empty($image_data_fields[5]);

  my $taxon_id;
  my $sth = $dbh->prepare("call import_taxon('$species_id', '$species_name', '$genus');");
  $sth->execute();
  $sth->bind_col(1, \$taxon_id);
  $sth->fetch();

  # Forming image_path by concatinating import_location and filename
  my $image_path = null_if_empty($image_data_fields[1]) . null_if_empty($image_data_fields[0]);

  # preprocessing ip_comment data
  my $ip_comment     = null_if_empty($image_data_fields[12]);
  my $source_db      = null_if_empty($image_data_fields[14]);
  my $source_db_name;
  my $source_db_id;

  if(defined $source_db)
  {
    ($source_db_name, $source_db_id) = split(/:/, $source_db, 2);
  }

  $source_db_name         = null_if_empty($source_db_name);
  $source_db_id           = null_if_empty($source_db_id);
  my $doi 		  = null_if_empty($image_data_fields[13]);
  my $collection_location = null_if_empty($image_data_fields[9]);
  my $collection_date     = null_if_empty($image_data_fields[11]);
  my $comments 		  = null_if_empty($image_data_fields[15]);

  my $annotated_image_id;
  my $sth = $dbh->prepare("call import_annotated_image('$curator_id', '$image_source_version_id', '$taxon_id', '$image_path', '$collection_location', '$collection_date', '$ip_comment', '$doi', '$source_db_name', '$source_db_id', '$comments');");
  $sth->execute();
  $sth->bind_col(1, \$annotated_image_id);
  $sth->fetch();


  my @keywords 		= split(/\|/, trim($image_data_fields[2]));
  my @ontology_term_ids = split(/\|/, trim($image_data_fields[3]));

  foreach (0..(scalar(@keywords)-1))
  {

    my $annotated_term_id;
    my $sth = $dbh->prepare("call import_annotated_term('$keywords[$_]', '$ontology_term_ids[$_]');");
    $sth->execute();
    $sth->bind_col(1, \$annotated_term_id);
    $sth->fetch();


    my $annotated_term_image_id;
    my $sth = $dbh->prepare("call import_annotated_term_image('$annotated_term_id', '$annotated_image_id');");
    $sth->execute();
    $sth->bind_col(1, \$annotated_term_image_id);
    $sth->fetch();

  }

}


sub import_image_data
{
  
  print "Connecting to the Database...\n\n";
  my $dbh = establish_db_connection;


  print "Reading content of collection specification data(IADB_coll_spec_*.tsv) file and reading data...\n\n";

  my @collection_files = glob(IMAGE_DATA_DIR."/IADB_coll_spec_*.tsv");
  my $collection_content = do {
      local $/ = undef;
      open (my $file, "<", $collection_files[0]) or die("could not open $collection_files[0]: $!");
      <$file>;
  };

  my @collection_spec_records = split(/\n/, $collection_content);

  shift @collection_spec_records;

  my $image_source_version_id = insert_collection_data_into_database($dbh, $collection_spec_records[0]);
  

  print "Reading content of image data(IADB_img_data_*.tsv) file and reading data...\n\n";

  my @image_data_files = glob(IMAGE_DATA_DIR."/IADB_img_data_*.tsv");
  my $image_content = do {
      local $/ = undef;
      open (my $file, "<", $image_data_files[0]) or die("could not open $image_data_files[0]: $!");
      <$file>;
  };

  my @image_data_records = split(/\n/, $image_content);
  
  shift @image_data_records;

  foreach (@image_data_records)
  {
    insert_image_data_into_database($dbh, $_, $image_source_version_id);
  }

}

# ---------------------------------------------------------------------------
  # main
# ---------------------------------------------------------------------------

import_image_data;
exit;

# ---------------------------------------------------------------------------
# end
# ---------------------------------------------------------------------------