#!/bin/sh -xe

DBFILE=`perl -MData::Downloader -e 'print Data::Downloader::DB->new()->database'`
TABLE=file_metadata

[ -e $DBFILE ]

SQLITE="sqlite3 $DBFILE"
TMP_PIVOT_DATA=/tmp/pivotted
TMP_FIELDS=/tmp/pivotted_fields

echo -e "
.mode tabs
select name from metadata_source order by id;
select '';
select file,name,value from metadatum order by file;
" \
 | $SQLITE \
 | perl -alnF"\t" -e \
     'BEGIN { $,="\t"; }
         1../^$/                       and do { push @fields, $_ if length; next;    };
         $file_id && $file_id != $F[0] and do { print $file_id,@a{@fields}; %a = (); };
         ( $file_id, $a{ $F[1] } ) = @F[ 0, 2 ];
         END { print $file_id,@a{@fields}; print STDERR join ",", @fields if @fields; }
     ' \
> $TMP_PIVOT_DATA 2> $TMP_FIELDS

echo "drop table if exists $TABLE;" | $SQLITE

if [ -s "$TMP_FIELDS" ]; then
    echo "create table $TABLE (file key references file(id), `cat $TMP_FIELDS` ); " | $SQLITE
else
    echo "create table $TABLE (file key references file(id) ); " | $SQLITE
fi

cat <<EOT | $SQLITE
.separator "\t"
.import $TMP_PIVOT_DATA $TABLE
EOT