#!/usr/bin/perl i-w

package czartool::Gpc1Db;

use warnings;
use strict;

use czartool::MySQLDb;
our @ISA = qw(czartool::MySQLDb);    # inherits from MySQLDb

###########################################################################
#
# Returns the right join table and ID to join on for for a given stage
#
###########################################################################
sub getJoinTableAndId {
    my ($self, $stage, $joinTable, $id) = @_;

    ${$id} = $stage."_id";

    if ($stage eq "chip") {${$joinTable}="chipProcessedImfile";}
    elsif ($stage eq "cam") {${$joinTable}="camProcessedExp";}
    elsif ($stage eq "fake") {${$joinTable}="fakeProcessedImfile";}
    elsif ($stage eq "warp") {${$joinTable}="warpSkyfile";}
    elsif ($stage eq "stack") {${$joinTable}="stackSumSkyfile";}
    elsif ($stage eq "staticsky") {${$id} = "sky_id"; ${$joinTable}="staticskyResult";}
    elsif ($stage eq "diff") {${$joinTable}="diffSkyfile";}
    elsif ($stage eq "magic") {${$joinTable}="magicNodeResult";}
    elsif ($stage eq "magicDS") {${$id} = "magic_ds_id"; ${$joinTable}="magicDSFile";}
    elsif ($stage eq "dist") {${$joinTable}="distComponent";}
    elsif ($stage eq "pub") {${$joinTable}="publishDone";}
    elsif ($stage eq "chipbackground") {${$id}="chip_bg_id"; ${$joinTable}="chipBackgroundImfile";}
    else {

        print "* ERROR: could not find joinTable and ID for '$stage' stage\n";
        return 0;
    }

    return 1;
}

###########################################################################
#
# Returns the right table for a given stage
#
###########################################################################
sub getTableForStage {
    my ($self, $stage) = @_;

    if ($stage eq "chip" ) {return "chipRun";}
    elsif ($stage eq "cam" ) {return "camRun";}
    elsif ($stage eq "fake" ) {return "fakeRun";}
    elsif ($stage eq "warp" ) {return "warpRun";}
    elsif ($stage eq "stack" ) {return "stackRun";}
    elsif ($stage eq "staticsky" ) {return "staticskyRun";}
    elsif ($stage eq "diff" ) {return "diffRun";}
    elsif ($stage eq "magic" ) {return "magicRun";}
    elsif ($stage eq "magicDS" ) {return "magicDSRun";}
    elsif ($stage eq "dist" ) {return "distRun";}
    elsif ($stage eq "pub" ) {return "publishRun";}
    elsif ($stage eq "chipbackground" ) {return "chipBackgroundRun";}

    return "ERROR";
}

###########################################################################
#
# Returns the number of science exposures taken 'last night' for a given day
# 
# Notes:
#
# - 03:00 HST = 17:00 last night UTC
# - 17:00 HST = 07:00 this morning UTC
# - 'OBJECT' = science exposures
#
###########################################################################
sub countScienceExposuresFromLastNight {
    my ($self, $day) = @_;

    my $begin = "$day 03"; 
    my $end = "$day 17"; 

    my $query = $self->{_db}->prepare(<<SQL);
    SELECT COUNT(*)
        FROM summitExp 
        WHERE dateobs > '$begin' 
        AND dateobs < '$end' AND
        exp_type = 'OBJECT';
SQL

    $query->execute;
return scalar $query->fetchrow_array();
}

###########################################################################
#
# Returns the number of exposures between two dates that have been registered with chip
# 
# Notes:
#
# necessary to convert times as rawExp.dateobs is in UTC
#
###########################################################################
sub getProcessedExposures {
    my ($self, $beginDate, $endDate, $expIds) = @_;

    $beginDate = "$beginDate 03";
    $endDate = "$endDate 17";

    my $query = $self->{_db}->prepare(<<SQL);
        SELECT rawExp.exp_id 
        FROM chipRun, rawExp 
        WHERE chipRun.exp_id = rawExp.exp_id 
        AND rawExp.dateobs >= '$beginDate' 
        AND rawExp.dateobs <= '$endDate';
SQL

    $query->execute;
    ${$expIds} = $query->fetchall_arrayref();

    return 1;
}

###########################################################################
#
# Returns count of faults for this stage and label
#
###########################################################################
sub countFaults {
    my ($self, $label, $stage, $state) = @_;

    my $table = undef;
    my $joinTable = undef;
    my $id = undef;
    $table = getTableForStage($self, $stage);
    if (!getJoinTableAndId($self, $stage, \$joinTable, \$id)) {return -1;}

    my $faultCol =  $joinTable.".fault";
    my $stateCol =  $table.".state";

    my $extraState = "";
    if ($state eq 'new') {
        $extraState = " OR $stateCol = 'update' ";
    }

    my $query =  $self->{_db}->prepare(<<SQL);
    SELECT COUNT(DISTINCT $id) 
        FROM $table
        JOIN $joinTable USING ($id)
        WHERE label LIKE '$label'
        AND ($faultCol != 0 AND ($stateCol = '$state' $extraState))
SQL
    $query->execute;
    return scalar $query->fetchrow_array();
}

###########################################################################
#
# Returns priority for this label
#
###########################################################################
sub getPriority {
    my ($self, $label) = @_;

    my $query = $self->{_db}->prepare(<<SQL);
    SELECT priority 
        FROM Label 
        WHERE label LIKE '$label' 
SQL

    $query->execute;
    my $priority = scalar $query->fetchrow_array();
    if (!$priority) {return 50000;} # assume labels not given priority in gpc1 Db have highest priority
    return $priority;
}

###########################################################################
#
# Returns count of exposures that have been registered
#
# NB JOINing summitExp to newExp to rawExp because summit_id is indexed, whereas
# a direct JOIN from summitExp to rawExp using exp_name is more direct, but slower
# as exp_name is not indexed in rawExp
#
###########################################################################
sub countRegisteredExposures {
    my ($self, $label, $date) = @_;

    my $query = $self->{_db}->prepare(<<SQL);
    SELECT COUNT(*) 
        FROM summitExp 
        JOIN newExp USING (summit_id) 
        JOIN rawExp USING(exp_id) 
        JOIN chipRun USING(exp_id) 
        WHERE summitExp.dateobs > '$date' 
        AND summitExp.exp_type = 'OBJECT' 
        AND chipRun.label = "$label";
SQL

    $query->execute;
    return scalar $query->fetchrow_array();
}

###########################################################################
#
# Returns count of exposures with this state for this label
#
###########################################################################
sub countExposures {
    my ($self, $label, $stage, $state) = @_;

    my $table = getTableForStage($self, $stage);
    my $query = undef;

    my $joinTable = undef;
    my $id = undef;
    if (!getJoinTableAndId($self, $stage, \$joinTable, \$id)) {return -1;}

    # XXX: the "update" page is now obsolete. The standard processing page should now show
    # counts in new and update state
    if ($state eq "update") {
        if ($stage eq "dist") {return 0;}

        if ($stage eq "chip" || $stage eq "fake" || $stage eq "warp" || $stage eq "diff" || $stage eq "magicDS") {
            $query = $self->{_db}->prepare(<<SQL);
            SELECT COUNT(state)
                FROM $table JOIN $joinTable 
                USING($id) 
                WHERE label LIKE '$label'
                AND state = 'update' 
                AND data_state = 'update';
SQL
        }
        else {
            $query = $self->{_db}->prepare(<<SQL);
            SELECT COUNT(state)
                FROM $table
                WHERE label LIKE '$label'
                AND state = 'update'; 
SQL
        
        }

    }
    # 'new' stuff really includes 'update' stuff, at least for LAP. This is a hack, but there you go    
    elsif ($state eq "new") {

        if ($stage eq "chip" || $stage eq "fake" || $stage eq "warp" || $stage eq "diff" || $stage eq "magicDS") {
            # get count of runs in new state and update state that have components left to update
            $query = $self->{_db}->prepare(<<SQL);
            SELECT COUNT(DISTINCT $id)  
                FROM $table LEFT JOIN $joinTable
                USING($id)
                WHERE label LIKE '$label' 
                AND (state = 'new' OR (state = 'update' AND data_state = 'update'))
SQL
        } else {
            $query = $self->{_db}->prepare(<<SQL);
            SELECT COUNT(state)
                FROM $table
                WHERE label LIKE '$label'
                AND (state = 'new' OR state = 'update'); 
SQL
        }
    }
    else {

        $query = $self->{_db}->prepare(<<SQL);
        SELECT COUNT(state)  
            FROM $table 
            WHERE label LIKE '$label' 
            AND state = '$state'
SQL
    }

    # print $query->{Statement} . "\n";

    $query->execute;
    return scalar $query->fetchrow_array();
}

###########################################################################
#
# Returns magic mask fraction across all chips for a particular exposure
#
###########################################################################
sub getMagicMaskFraction {
        my ($self, $exp_id, $fracs) = @_;

            my $query = $self->{_db}->prepare(<<SQL);
            SELECT component, streak_frac 
                FROM magicDSFile  
                JOIN magicDSRun USING(magic_ds_id) 
                JOIN camRun USING(cam_id) 
                JOIN chipRun USING(chip_id) 
                WHERE exp_id = $exp_id 
                AND camRun.label LIKE "%nightlyscience"
                AND component LIKE "XY%";
SQL
    $query->execute;
    ${$fracs} = $query->fetchall_arrayref();

    return 1;
}

###########################################################################
#
# Returns average magic mask fraction, sum of mask fractions and chip count 
# for a particular exposure
#
###########################################################################
sub getMagicMaskStats {
        my ($self, $exp_id, $mean, $sum, $chipCount) = @_;

            my $query = $self->{_db}->prepare(<<SQL);
            SELECT AVG(streak_frac), SUM(streak_frac), COUNT(*) 
                FROM magicDSFile  
                JOIN magicDSRun USING(magic_ds_id) 
                JOIN camRun USING(cam_id) 
                JOIN chipRun USING(chip_id) 
                WHERE exp_id = $exp_id 
                AND camRun.label LIKE "%nightlyscience"
                AND component LIKE "XY%"
                AND stage = 'chip';
SQL
    if (!$query->execute) {return 0;} # TODO do this everywhere

    (${$mean}, ${$sum}, ${$chipCount}) = $query->fetchrow_array();

    return 1;
}
1;
