# Read a CSV file and create a Sqlite3 file from it
# that can be used in the Android "Ref Library" app
# from Tsort.

# (c) 2010, TsorT
#
# This sample script shows how to prepare a dataset for 
# the RefLib Android app.  This script may be freely 
# copied, modified and adapted without any restriction,
# it is specifically NOT covered as part of the RefLib 
# software.  There is expressly no warranty for this 
# script, it is provided 'As Is' without any express 
# or implied warranty of any kind.
#

use strict;
use warnings;

use DBI;
# use DBI::Sqlite;

use Text::CSV;
use IO::File;
use Carp;

$| = 1;

# Convert CSV file to Sqlite3
my %details =
  (
    'help' =>
      {
        # The name the user will see
        name => "Help Information",

        # The version number for this dataset
        version => 1,

        # The name of the rld file to create
        file => "help.rld",

        # This needs to be a CSV file containing 
        # the data.  With "\n", "\t" and "\\" 
        # characters
        src_file => "help.csv",

        # The names used when displaying the data
        display_names => ["Topic","","Web"],

        # The way the data is formatted.  Empty 
        # values just take the matching column from 
        # the CSV file.  Otherwise the format defines
        # what is shown (except that sequences with 
        # angle brackets, like <topic> get replaced 
        # by the CSV data)
        display_formats => ["","",
                   "http://tsort.info/software/reflib/index.htm"],
        
        # The column to use as the name of each row
        name_col => "topic",

        # The name of the table that will contain the 
        # data.  There's probably not a good reason 
        # to change this
        table => "data",

        # One day the front page list will be organised 
        # by group, name, date of creation, date of adding etc
        group => "Information",
      },
  );

my($db_handle);

my %groups;
foreach my $dataset (keys %details)
  {
    process_csv($dataset);

    $groups{$details{$dataset}->{group}} = {}
                        if(!defined $groups{$details{$dataset}->{group}});
    $groups{$details{$dataset}->{group}}->{$dataset} = 1;
  }

# And now lets create an index file
my $ofh = IO::File->new("new_refdata.rli","w");
foreach my $group (sort keys %groups)
  {
    print $ofh "#! $group\n";
    foreach my $ds (sort keys %{$groups{$group}})
      {
        my $file    = $details{$ds}->{file};
        my $version = $details{$ds}->{version};
        my $name    = $details{$ds}->{name};

        print $ofh "$file\t$version\t$name\n";
      }
  }
$ofh->close();
exit 0;

sub create_db
  {
    my($dest_file,$dataset,@columns) = @_;

    $db_handle = DBI->connect("dbi:SQLite:$dest_file", "", "",
                              {RaiseError => 1, AutoCommit => 1});

    # Need two special magic tables, one for the Android locale...
    my $prepared = $db_handle->prepare(
      "CREATE TABLE \"android_metadata\" ( locale TEXT )");
    $prepared->execute();
    $prepared = $db_handle->prepare(
      "INSERT into \"android_metadata\" ( locale ) VALUES ( \"en_US\" )");
    $prepared->execute();
    $prepared = $db_handle->prepare(
      "CREATE TABLE \"properties\" ( name TEXT, value TEXT )");
    $prepared->execute();

    $details{$dataset}->{table} = "data"
              if(!defined $details{$dataset}->{table});
    if(defined $details{$dataset}->{name_col})
      {
        for(my $i=0;$i<=$#columns;$i++)
          {
            if($details{$dataset}->{name_col} eq $columns[$i])
              {
                $details{$dataset}->{name_col_idx} = $i;
                last;
              }
          }
        croak("Cannot find name column $details{$dataset}->{name_col}")
                            if(!defined $details{$dataset}->{name_col_idx});
      }
    croak("Different number of names and formats")
                if($#{$details{$dataset}->{display_names}} !=
                                 $#{$details{$dataset}->{display_formats}});
    
    add_property($db_handle,"name",$details{$dataset}->{name});
    add_property($db_handle,"version",$details{$dataset}->{version});
    add_property($db_handle,"table",$details{$dataset}->{table});

    add_property($db_handle,"num_columns",$#columns + 1);
    add_property($db_handle,"name_column",$details{$dataset}->{name_col_idx});
    add_property($db_handle,"num_names",$#{$details{$dataset}->{display_names}}+1);
    for(my $i=0;$i<=$#columns;$i++)
      {
        my $val = $columns[$i];

        add_property($db_handle,"column_".$i,$val);
      }
    for(my $i=0;$i<=$#{$details{$dataset}->{display_names}};$i++)
      {
        add_property($db_handle,"name_".$i,$details{$dataset}->{display_names}->[$i]);
        add_property($db_handle,"format_".$i,$details{$dataset}->{display_formats}->[$i]);
      }

    # Finally create the data table
    
    my $stmt = "CREATE TABLE \"$details{$dataset}->{table}\" (";
    foreach my $col (@columns)
      {
        $stmt .= ", " if($col ne $columns[0]);
        $stmt .= "$col TEXT";
      }
    $stmt .= ")";

    $prepared = $db_handle->prepare($stmt);
    $prepared->execute();

    return $details{$dataset}->{table};
  }

sub add_property
  {
    my($db_handle,$p_name,$p_value) = @_;
    my $prepared = $db_handle->prepare(
        "INSERT into properties ( name, value) VALUES (\"$p_name\",\"$p_value\")");
    $prepared->execute();
  }

sub process_csv
  {
    my($dataset) = @_;

    if(-r $details{$dataset}->{file})
      {
        # carp("The file $details{$dataset}->{file} already exists (not rebuilding)");
        # return;

        # Delete the old one
        unlink($details{$dataset}->{file});
      }
    print "$details{$dataset}->{name}\n";
    my $csv = Text::CSV->new();
    my $ifh;
    if(-r $dataset."/".$details{$dataset}->{src_file})
      {
        $ifh = IO::File->new($dataset."/".$details{$dataset}->{src_file});
      }
    elsif(-r $details{$dataset}->{src_file})
      {
        $ifh = IO::File->new($details{$dataset}->{src_file});
      }
    else
      {
        carp("Cannot find source csv file \"$details{$dataset}->{src_file}\"");
        return;
      }
    my $fields = $csv->getline($ifh);
    my @fields = @{$fields};
    # unshift @fields,"_id\@int"
    #                           if($add_id);

    # Create the table to insert the values into
    my $table_name = create_db($details{$dataset}->{file},$dataset,@fields);
    my $stmt = "INSERT INTO $table_name (".
                  join(',',@fields).") VALUES (";
    for(my $i=0;$i<=$#fields;$i++)
      {
        $stmt .= "," if($i > 0);
        $stmt .= "?";
      }
    $stmt .= ");";

    my $add_row = $db_handle->prepare($stmt);
    my $count = 1;
    while(!$ifh->eof())
      {
        my $values = $csv->getline($ifh);
        next if(!defined $values);
        my @values = @{$values};

        if($#values <= 0)
          {
            next;
          }
        elsif($#values != $#fields)
          {
            carp("Line in $dataset has ".($#values+1)." not ".($#fields+1));
            next;
          }

        $add_row->execute(@values);

        print "\r$count - ".substr($values[0]. (" " x 20),0,20)."     ";
        $count++;
      }
    print "\n";
    $ifh->close();

    carp("Failed to find any data for $dataset") 
                                        if($count < 1);
#    # OK now lets put that in a zip file
#    system("zip -v $dataset.zip $details{$dataset}->{file}");
  }
