#!/usr/bin/perl

# HOWTO
#
# 1) save Google Spread sheet as CSV
# 2) run this script as: gold2sqlite <file.csv> <file.sqlite>

use warnings;
use strict;

use Parse::CSV;
use DBI;
use DBD::SQLite;
use Data::Dumper;

my $file = shift;
my $dbfile = shift;
die unless ($file and $dbfile);

my $csv = Parse::CSV->new(file=>$file);
my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile");

my $tmp = __get_pkgid();
$dbh->do("DELETE FROM idgold WHERE pkgid='$tmp'"); # FIXME
my $sth = $dbh->prepare("INSERT INTO idgold (pkgid,iduid,idsplits,idterms,status,multi) VALUES (?, ?, ?, ?, ?, ?)");  
while (my $r = $csv->fetch) {
  my ($id, $pkgid, $iduid, $idname, $idsplits, $idterms, $state, $multi) = @$r;
  next unless ($id =~ m/^\d+$/);

  $sth->execute($pkgid, $iduid, lc $idsplits, lc $idterms, $state, $multi);
}
$dbh->disconnect();

# HACK
sub __get_pkgid {
  open F, '<', $file;
  my $l1 = <F>;
  my $l2 = <F>;
  close F;
  ($l2 =~ m/\d+,(.*?),/) ? return $1 : return undef;
}