#!/usr/bin/perl

use warnings;
use strict;

use DBI;


my $dbh = DBI->connect("dbi:Pg:service=snp",'','',{AutoCommit => 1}) or
    die "Unable to connect to database: ".$DBI::errstr;

my %sth;
$sth{insert_sample} = $dbh->prepare(<<'END') // die "Unable to prepare insert sample statement: ".$dbh->errstr;
INSERT INTO gcrma_samples (tissue) VALUES (?);
END

$sth{select_sample} = $dbh->prepare(<<'END') // die "Unable to prepare select sample statement: ".$dbh->errstr;
SELECT * FROM gcrma_samples WHERE tissue = ?;
END

$sth{insert_probe} = $dbh->prepare(<<'END') // die "Unable to prepare insert probe statement: ".$dbh->errstr;
INSERT INTO affy_probe (probe) VALUES (?);
END

$sth{select_probe} = $dbh->prepare(<<'END') // die "Unable to prepare select probe statement: ".$dbh->errstr;
SELECT * FROM affy_probe WHERE probe = ?;
END

$sth{insert_reading} = $dbh->prepare(<<'END') // die "Unable to prepare insert reading statement: ".$dbh->errstr;
INSERT INTO gcrma_expression (probe,sample,expression) VALUES (?,?,?);
END



my @samples;

use Term::ProgressBar;
use Fcntl qw(:seek);

my @ifh;
for my $ifn (@ARGV) {
    my $ifh = IO::File->new($ifn,'r') or
	die "Unable to open $ifn for reading: $!";
    push @ifh,$ifh;
}

# read avg.csv file
for my $ifh (@ifh) {
    my $p;
    if ($ifh->seek(0,SEEK_END)) {
        $p = Term::ProgressBar->new({count => $ifh->tell,
                                     remove => 1,
                                     ETA=>'linear'});
        $ifh->seek(0,SEEK_SET);
    }
    while (<$ifh>) {
        chomp;
        my @line = split /\s*,\s*/;
        if (not @samples) {
            shift @line;
            for my $sample (@line) {
                push @samples, insert_sample($dbh,\%sth,$sample);
            }
            next;
        }
        my $probe = insert_probe($dbh,\%sth,shift @line);
        $dbh->do('COPY gcrma_expression (probe,sample,expression) FROM STDIN;');
        for (0..$#line) {
            $dbh->pg_putcopydata("$probe\t$samples[$_]\t$line[$_]\n");
        }
        $dbh->pg_putcopyend();
        if (defined $p) {
            $p->update($ifh->tell);
        }
    }
}

sub insert_sample{
    my ($dbh,$sth,$sample) = @_;
    my $rv = $sth->{insert_sample}->execute($sample) //
        die "Unable to execute statement properly: ".$dbh->errstr;
    $sth->{insert_sample}->finish;
    return select_sample(@_);
}

sub select_sample {
    my ($dbh,$sth,$sample) = @_;
    my $rv = $sth->{select_sample}->execute($sample) //
        die "Unable to execute statement properly: ".$dbh->errstr;
    my ($sample_id) = map {ref $_ ?@{$_}:()}
	map {ref $_ ?@{$_}:()} $sth->{select_sample}->fetchall_arrayref([0]);
    $sth->{select_sample}->finish;
    return $sample_id;
}

sub insert_probe{
    my ($dbh,$sth,$probe) = @_;
    my $rv = $sth->{insert_probe}->execute($probe) //
        die "Unable to execute statement properly: ".$dbh->errstr;
    $sth->{insert_probe}->finish;
    return select_probe(@_);
}

sub select_probe {
    my ($dbh,$sth,$probe) = @_;
    my $rv = $sth->{select_probe}->execute($probe) //
        die "Unable to execute statement properly: ".$dbh->errstr;
    my ($probe_id) = map {ref $_ ?@{$_}:()}
	map {ref $_ ?@{$_}:()} $sth->{select_probe}->fetchall_arrayref([0]);
    $sth->{select_probe}->finish;
    return $probe_id;
}

sub insert_reading{
    my ($dbh,$sth,$probe,$sample,$reading) = @_;
    my $rv = $sth->{insert_reading}->execute($probe,$sample,$reading) //
        die "Unable to execute statement properly: ".$dbh->errstr;
    $sth->{insert_reading}->finish;
}

