#!/usr/bin/env perl
#
# 
# script to convert existing diffRun and diffInputSkyfiles rows 
# From version 1.1.46 of the schema to 1.1.47
#
# Assumes that the changes up to the diffInputSkyfile columns have been
# made up to the place where it says that this script should be run

use strict;
use warnings;

use Getopt::Long qw( GetOptions :config auto_help auto_version gnu_getopt );
use Pod::Usage qw( pod2usage );


use DBI;

my $dbname = shift;
my $dbuser = shift;
my $dbpass = shift;
my $dbhost = shift;
die "usage: $0 dbname dbuser dbpassword dbhost\n" unless $dbname and $dbuser and $dbpass and $dbhost;

my $dbh  = getDBHandle($dbname, $dbuser, $dbpass, $dbhost);

# find all exposures in summitExp
# my $query = "SELECT * FROM diffInputSkyfile where template = 1";

my $query = "
select
    diff_id,
    warp1,
    stack1,
    warp2,
    stack2,
    skycell_id
    FROM
        (select
            diff_id,
            warp1 as warp1,
            stack2 as stack1,
            skycell_id as skycell_id
        from diffInputSkyfile
        WHERE template = 0
        ) as inputs
    JOIN
        (select
            diff_id,
            warp1 as warp2,
            stack2 as stack2,
            skycell_id as stack_skycell
        from diffInputSkyfile
        WHERE template = 1
        ) as templates
    USING (diff_id)
";

my $stmt = $dbh->prepare($query);
$stmt->execute();


my $sub_query .= "(SELECT exp_id FROM diffInputSkyfile JOIN warpRun on diffInputSkyfile.warp1 = warpRun.warp_id join fakeRun using(fake_id) join camRun using(cam_id) join chipRun using(chip_id) where diff_id = ? )";

my $exp_query = "UPDATE diffRun SET exp_id = $sub_query where diff_id = ?";

while (my $ref = $stmt->fetchrow_hashref()) {
    my $diff_id = $ref->{diff_id};
    my $warp1 = $ref->{warp1};
    my $warp2 = $ref->{warp2};
    my $stack1 = $ref->{stack1};
    my $stack2 = $ref->{stack2};
    my $skycell_id = $ref->{skycell_id};

    # reorganizing columns puts warp1 and stack 1 correctly for template.
    # set all of the stack and warp fields in the template = 0 input
    # (the template = 1 row will be deleted)
    if ($warp1 and $stack2) {
        # usual case warp - stack
        $dbh->do("UPDATE diffInputSkyfile SET stack2 = $stack2  where diff_id = $diff_id AND template = 0");
    } elsif ($stack1 and $stack2) {
        # stack - stack
        $dbh->do("UPDATE diffInputSkyfile SET stack1 = $stack1, stack2 = $stack2  where diff_id = $diff_id AND template = 0");
    } elsif ($warp1 and $warp2) {
        # warp - warp
        $dbh->do("UPDATE diffInputSkyfile SET warp2 = $warp2  where diff_id = $diff_id AND template = 0");
    } elsif ($stack1 and $warp2) {
        # stack - warp
        $dbh->do("UPDATE diffInputSkyfile SET stack1 = $stack1, warp2 = $warp2  where diff_id = $diff_id AND template = 0");
    } else {
        die "unexpected input set for $diff_id";
    }

    # set skycell_id in any existing diffSkyfile
    $dbh->do("UPDATE diffSkyfile set skycell_id = ? where diff_id = ?", undef, $skycell_id, $diff_id);

    #
    # set the exp_id for the diffRun if the input is a warp
    #
    if ($warp1) {
        my $rows = $dbh->do($exp_query, undef, $diff_id, $diff_id);
        die "failed to update diffRun.exp_id for $diff_id" if ($rows != 1);
    }
}

exit 0;


sub getDBHandle {
    my $dbname   = shift;
    my $dbuser   = shift;
    my $dbpass   = shift;
    my $dbserver = shift;

    die "database environment not set up" unless defined($dbserver) and defined($dbuser)
        and defined($dbpass) and defined($dbname);

    my $dsn = "DBI:mysql:host=$dbserver;database=$dbname";

    my $dbh = DBI->connect($dsn, $dbuser, $dbpass) or die "Cannot connect to server\n";

    return $dbh;
}

