package Scoop2::DBH;
use strict;
use Apache::DBI;
our $AUTOLOAD;
my $DEBUG = 0;
my $DB_EXPLAIN = 0;
my $CACHE_ONLY_DEBUG = 0;
$Scoop2::COUNT_DEBUG = 0;
$Scoop2::DB_QUERY_COUNT = 0;
$Scoop2::DB_CACHE_HITS = 0;
$Scoop2::DB_CACHE_MISSES = 0;
$Scoop2::DB_NOCACHE = 0;

sub new{
        my $pkg = shift;
        my %args = @_;

        my $class = ref($pkg) || $pkg;
        my $self  = bless {}, $class;
        warnings::warnif('debugging',"A new ".ref( $self )." Created and ready to go.");
        $self->{scoop} = Scoop2->instance();

        $self->{data_source_live}="DBI:".$self->{scoop}->config('db_type').
                ":dbname=".$self->{scoop}->config('db_name').
                ";host=".$self->{scoop}->config('db_host');
#        $self->{data_source_archive}="DBI:".$self->{scoop}->config('archive_db_type').
#                ":dbname=".$self->{scoop}->config('archive_db_name').
#                ";host=".$self->{scoop}->config('archive_db_host');
        $self->{db_down}=0;
        $self->{dbh_live} = $self->SUPER::connect($self->{data_source_live}, $self->{scoop}->config('db_user'), $self->{scoop}->config('db_pass')) ||
                warn "(Scoop2::Database_Connect) Can't connect to database! $@\n", $self->{db_down}++;
        if($self->{scoop}->config('archive_db')){
                my $self->{dbh_archive} = $self->SUPER::connect($self->{data_source_archive}, $self->{scoop}->config('archive_db_user'), $self->{scoop}->config('archive_db_pass')) ||
                        warn "(Scoop2::Database_Connect) Can't connect to database! $@\n", $self->{db_down}++;
        }
        if($self->{db_down}){
                $self->{scoop}->apache->status(502);
                $self->{scoop}->{HEADER_ONLY} = 1;
                $self->{scoop}->apache->send_http_header;
                undef $Scoop2::_instance;
                exit;
        }
        return $self; # Return it!
}


sub db_select {
        my $self = shift;
        $self->{args} = shift;
        $self->{archive}=0;
        $Scoop2::DB_QUERY_COUNT++ if ($Scoop2::COUNT_DEBUG);
        $self->{query} = "SELECT";
        $self->{query} .= " DISTINCT" if ($self->{args}->{DISTINCT});
        $self->{table} = $self->modify_for_shared_db($self->{args}{FROM});

        if ($self->{args}->{ARCHIVE}) {
                $self->{archive} = 1;
                $self->{dbh}=$self->{dbh_archive};
        } else {
                $self->{archive} = 0;
                $self->{dbh}=$self->{dbh_live};
        }
        return('0E0',undef) unless ($self->{dbh});


        $self->{table} =~ s/^\s*(.*)\s*$/$1/;
        $self->{query} .= " ". $self->{args}->{WHAT}." FROM ".$self->{table};

        my $resource = 'sql_'.$self->{table};
        my $element = 'SQL_DATA_'.$self->{table};

        $self->{query}.=" WHERE ".$self->{args}{WHERE} if($self->{args}{WHERE});
        $self->{query}.=" GROUP BY ".$self->{args}{GROUP_BY} if($self->{args}{GROUP_BY});
        $self->{query}.=" ORDER BY ".$self->{args}{ORDER_BY} if($self->{args}{ORDER_BY});

        if ($self->{args}{LIMIT}) {
                $self->{query}.=" LIMIT";
                $self->{query}.=" ".$self->{args}{OFFSET}."," if($self->{args}{OFFSET});
                $self->{query}.=" ".$self->{args}{LIMIT};
        }
        warn "in db_select: Query is: ".$self->{query}  if ($self->{args}{DEBUG} || $DEBUG);

        # Don't cache if we're doing a join, for now.
        $self->{args}{NOCACHE} = 1 if ($self->{table} =~ /,/);
        $self->{args}{NOCACHE} = 1 if ($self->{table} =~ m/join/i);
        $self->{args}{NOCACHE} = 1 unless $self->{scoop}->var('use_db_cache');

        my ($sth, $rv) = undef;
        my $cached = {};
        $cached->{data} = [];
        $cached->{name} = '';

        if($DB_EXPLAIN){
                my $explain;
                my $sthexp = $self->SUPER::prepare("EXPLAIN ".$self->{query});
                my $rvexp = $sthexp->execute();
                my @values=$sthexp->fetchrow_array;
                for('table','type','possible_keys','key','key_len','ref','rows','extra'){
                        $explain.="$_: ".shift(@values)."\n";
                } warn "Query: ".$self->{query}."\n$explain"; $sthexp->finish();
        }

        if (!$self->{args}{NOCACHE} &&
                ($cached = $self->{scoop}->cache->fetch_data({resource => $resource,
                        element => $element, item => $self->{query}}))) {
                $Scoop2::DB_CACHE_HITS++;
                $rv = ($#{$cached->{data}}+1);
                $rv = '0E0' if ($rv == 0);
        } else {
                ($self->{args}{NOCACHE}) ? $Scoop2::DB_NOCACHE++ : $Scoop2::DB_CACHE_MISSES++;

                warn "in db_select: not found in cache. Running query" if ($DEBUG);
                warn "Cache miss! ($resource".'::'.$self->{query}.")" if ($CACHE_ONLY_DEBUG);
                $sth = $self->SUPER::prepare($self->{query});
                $rv = $sth->execute();

                if ($self->{args}{NOCACHE}) {
                        unless($rv){
                                my ($package, $filename, $line) = caller;
                                warn "<<ERROR>> <<".$self->{scoop}->{REMOTE_IP}.">> in db_select: $DBI::errstr (Query is $self->{query}) : Error in $filename line $line"
                        }
                        return ($rv, $sth);
                }

                my $size = 0;
                my $line;
                while ($line = $sth->fetchrow_hashref()) {
                        foreach my $key (keys %{$line}) {
                                $size += length($line->{$key});
                        }
                        push @{$cached->{data}}, $line;
                }
                $cached->{name} = $sth->{NAME};
                $sth->finish();

                warn "Size is $size\n" if $DEBUG;
                # Cache this stuff
                warn "Caching data for ".$element.'::'.$self->{query} if ($CACHE_ONLY_DEBUG);
                $self->{scoop}->cache->cache_data({resource=>$resource, element=>$element,
                        item => $self->{query}, data => $cached, size => $size});
        }

        my $scoop_sth = Scoop2::DBH::Statement->new($cached->{data}, $cached->{name});
        $cached = {};
        warn "Made Scoop sth. Returning." if $DEBUG;
        my ($package, $filename, $line) = caller;
        warn "<<ERROR>> <<".$self->{scoop}->{REMOTE_IP}.">> in db_select: $DBI::errstr (Query is $self->{query}) : Error in $filename line $line" unless ($rv);
        return ($rv, $scoop_sth);
}


sub db_wipe_table_cache {
        my $self = shift;
        my $table = shift;
        $table =~ s/^\s*(.*)\s*$/$1/;
        my $resource = 'sql_'.$table;
        my $element = 'SQL_DATA_'.$table;
        # Clear the table's whole cache
        $self->{scoop}->cache->clear({resource => $resource, element => $element});
        return;
}


sub db_update {
        my $self = shift;
        $self->{args} = shift;
        $Scoop2::DB_QUERY_COUNT++ if ($Scoop2::COUNT_DEBUG);
        $self->{args}{NOCACHE} = 1 unless $self->{scoop}->var('use_db_cache');
        $self->{args}{WHAT}=$self->modify_for_shared_db($self->{args}{WHAT});
        $self->{query} = "UPDATE ".$self->{args}{WHAT}." SET ".$self->{args}{SET};
        $self->{query} .= " WHERE ".$self->{args}{WHERE} if($self->{args}{WHERE});
        $self->{query} .= " LIMIT ".$self->{args}{LIMIT} if($self->{args}{LIMIT});
        warn "in db_update: Query is: ".$self->{query}  if($self->{args}{DEBUG} || $DEBUG);

        if ($self->{args}->{ARCHIVE}) {
                $self->{archive} = 1;
                $self->{dbh}=$self->{dbh_archive};
        } else {
                $self->{archive} = 0;
                $self->{dbh}=$self->{dbh_live};
        }
        return('0E0',undef) unless ($self->{dbh});

        my $sth = $self->SUPER::prepare($self->{query});
        my $rv = $sth->execute();

        my ($package, $filename, $line) = caller;
        warn "<<ERROR>> in db_update: $DBI::errstr (Query is $self->{query}) : Error in $filename line $line" unless $rv;
        $self->db_wipe_table_cache($self->{args}{WHAT}) if ($rv && !$self->{args}{NOCACHE});
        return ($rv, $sth);
}

sub db_insert {
        my $self = shift;
        $self->{args} = shift;
        $Scoop2::DB_QUERY_COUNT++ if ($Scoop2::COUNT_DEBUG);
        $self->{args}{NOCACHE} = 1 unless $self->{scoop}->var('use_db_cache');
        $self->{args}{INTO}=$self->modify_for_shared_db($self->{args}{INTO});
        $self->{query}= "INSERT INTO ".$self->{args}{INTO};
        $self->{query} .= " (".$self->{args}{COLS}.")" if($self->{args}{COLS});
        $self->{query} .= " VALUES (".$self->{args}{VALUES}.")";
        warn "in db_insert: Query is: ".$self->{query} if($self->{args}{DEBUG} || $DEBUG);

        if ($self->{args}->{ARCHIVE}) {
                $self->{archive} = 1;
                $self->{dbh}=$self->{dbh_archive};
        } else {
                $self->{archive} = 0;
                $self->{dbh}=$self->{dbh_live};
        }
        return('0E0',undef) unless ($self->{dbh});

        my $sth = $self->SUPER::prepare($self->{query});
        my $rv = $sth->execute();

        my ($package, $filename, $line) = caller;
        warn "<<ERROR>> <<".$self->{REMOTE_IP}.">> in db_insert: $DBI::errstr (Query is $self->{query}) : Error in $filename line $line" unless $rv;
        $self->db_wipe_table_cache($self->{args}{INTO}) if ($rv && !$self->{args}{NOCACHE});
        return ($rv, $sth);
}

sub db_delete {
        my $self = shift;
        $self->{query} = shift;
        $Scoop2::DB_QUERY_COUNT++ if ($Scoop2::COUNT_DEBUG);
        $self->{args}{NOCACHE} = 1 unless $self->{scoop}->var('use_db_cache');
        $self->{args}{FROM}=$self->modify_for_shared_db($self->{args}{FROM});
        $self->{query} = "DELETE FROM ".$self->{args}{FROM};
        $self->{query} .= " WHERE ".$self->{args}{WHERE} if($self->{args}{WHERE});
        $self->{query} .= " LIMIT ".$self->{args}{LIMIT} if($self->{args}{LIMIT});
        warn "in db_delete: Query is: ".$self->{query}  if($self->{args}{DEBUG} || $DEBUG);

        if ($self->{args}->{ARCHIVE}) {
                $self->{archive} = 1;
                $self->{dbh}=$self->{dbh_archive};
        } else {
                $self->{archive} = 0;
                $self->{dbh}=$self->{dbh_live};
        }
        return('0E0',undef) unless ($self->{dbh});
        my $sth = $self->SUPER::prepare($self->{query});
        my $rv = $sth->execute();

        my ($package, $filename, $line) = caller;
        warn "<<ERROR>> in db_delete: $DBI::errstr (Query is $self->{query}) : Error in $filename line $line" unless $rv;
        $self->db_wipe_table_cache($self->{args}{FROM}) if ($rv && !$self->{args}{NOCACHE});
        return ($rv, $sth);
}

sub db_lock_tables {
        my $self = shift;
        $self->{args} = shift;
        $Scoop2::DB_QUERY_COUNT++ if ($Scoop2::COUNT_DEBUG);
        $self->{query} = "LOCK TABLES ";
        while (my($k, $v) = each %{ $self->{args} }) {
                $self->{query} .= "$k $v" unless $k eq "DEBUG";
        }
        warn "in db_lock_tables: Query is: ".$self->{query} if($self->{args}{DEBUG} || $DEBUG);

        if ($self->{args}->{ARCHIVE}) {
                $self->{archive} = 1;
                $self->{dbh}=$self->{dbh_archive};
        } else {
                $self->{archive} = 0;
                $self->{dbh}=$self->{dbh_live};
        }
        return('0E0',undef) unless ($self->{dbh});
        my $sth = $self->SUPER::prepare($self->{query});
        my $rv = $sth->execute();

        warn "<<ERROR>> in db_lock_tables: $DBI::errstr (Query is: ".$self->{query} unless $rv;
        return ($rv, $sth);
}

sub modify_for_shared_db{
  my $self=shift;
  my $queryfrag=shift;
  warn("Determining Shared Database Usage Status for '$queryfrag'") if $DEBUG;
  return $queryfrag unless $self->{scoop}->var('use_shared_db');
  warn("We're using a Shared Database. Proceeding to Replacement") if $DEBUG;
  my $shared_db_name=$self->{scoop}->var('shared_db_name');
  for(split(/\s+/,$self->{scoop}->var('shared_db_tables'))){
    warn("Checking for Instances of '$_' for Replacement") if $DEBUG;
    $queryfrag=~s/(\b$_\b)/$shared_db_name\.$1/ig;
  }
  warn("Returning Updated Query Fragment for Shared DB: '$queryfrag'") if $DEBUG;
  return $queryfrag;
}

sub db_unlock_tables {
        my $self = shift;
        $self->{args} = shift;
        $Scoop2::DB_QUERY_COUNT++ if ($Scoop2::COUNT_DEBUG);
        $self->{query} = "UNLOCK TABLES";

        warn "in db_unlock_tables: Query is: ".$self->{query} if($self->{args}{DEBUG} || $DEBUG);

        if ($self->{args}->{ARCHIVE}) {
                $self->{archive} = 1;
                $self->{dbh}=$self->{dbh_archive};
        } else {
                $self->{archive} = 0;
                $self->{dbh}=$self->{dbh_live};
        }
        return('0E0',undef) unless ($self->{dbh});
        my $sth = $self->SUPER::prepare($self->{query});
        my $rv = $sth->execute();
        warn "<<ERROR>> in db_unlock_tables: $DBI::errstr\n Query is: ". $self->{query} unless $rv;
        return ($rv, $sth);
}

# Add functions for transactions. Can only be used with versions of mysql
# that use transactions, of course.

sub db_start_transaction {
        my $self = shift;
        $self->{args} = shift;

        unless ($self->{scoop}->config('mysql_version') =~ /^4/) {
                return "Transactions not supported in this version of MySQL.\n";
                }
        $Scoop2::DB_QUERY_COUNT++ if ($Scoop2::COUNT_DEBUG);
        $self->{query} = "START TRANSACTION";
        warn "in db_start_transaction: Query is: ".$self->{query} if($self->{args}{DEBUG} || $DEBUG);

        if ($self->{args}->{ARCHIVE}) {
                $self->{archive} = 1;
                $self->{dbh}=$self->{dbh_archive};
        } else {
                $self->{archive} = 0;
                $self->{dbh}=$self->{dbh_live};
        }
        return('0E0',undef) unless ($self->{dbh});
        my $sth = $self->SUPER::prepare($self->{query});
        my $rv = $sth->execute();
        warn "<<ERROR>> in db_start_transaction: $DBI::errstr\n Query is: ". $self->{query} unless $rv;
        return ($rv, $sth);
}

sub db_commit {
        my $self = shift;
        $self->{args} = shift;
        unless ($self->{scoop}->config('mysql_version') =~ /^4/) {
                warn("Transactions not supported in this version of MySQL");
                return "Transactions not supported in this version of MySQL.\n";
        }
        $Scoop2::DB_QUERY_COUNT++ if ($Scoop2::COUNT_DEBUG);
        $self->{query} = "COMMIT";

        warn "in db_commit: Query is: ".$self->{query} if($self->{args}{DEBUG} || $DEBUG);

        if ($self->{args}->{ARCHIVE}) {
                $self->{archive} = 1;
                $self->{dbh}=$self->{dbh_archive};
        } else {
                $self->{archive} = 0;
                $self->{dbh}=$self->{dbh_live};
        }
        return('0E0',undef) unless ($self->{dbh});
        my $sth = $self->SUPER::prepare($self->{query});
        my $rv = $sth->execute();
        warn "<<ERROR>> in db_commit: $DBI::errstr\n Query is: ". $self->{query} unless $rv;
        return ($rv, $sth);
}

sub db_rollback {
        my $self = shift;
        $self->{args} = shift;
        unless ($self->{scoop}->config('mysql_version') =~ /^4/) {
                warn("Transactions not supported in this version of MySQL");
                return "Transactions not supported in this version of MySQL.\n";
        }
        $Scoop2::DB_QUERY_COUNT++ if ($Scoop2::COUNT_DEBUG);
        $self->{query} = "ROLLBACK";

        warn "in db_rollback: Query is: ".$self->{query} if($self->{args}{DEBUG} || $DEBUG);

        if ($self->{args}->{ARCHIVE}) {
                $self->{archive} = 1;
                $self->{dbh}=$self->{dbh_archive};
        } else {
                $self->{archive} = 0;
                $self->{dbh}=$self->{dbh_live};
        }
        return('0E0',undef) unless ($self->{dbh});
        my $sth = $self->SUPER::prepare($self->{query});
        my $rv = $sth->execute();
        warn "<<ERROR>> in db_rollback: $DBI::errstr\n Query is: ". $self->{query} unless $rv;
        return ($rv, $sth);
}

sub db_date_sub {
        my $self = shift;
        my $left = shift;
        my $right = shift;

        if (lc($self->{scoop}->config('DBType')) eq "mysql") {
                return "DATE_SUB(($left), INTERVAL $right)";
        } else {
                return "$left + INTERVAL '$right'";
        }
}

sub db_date_add {
        my $self = shift;
        my $left = shift;
        my $right = shift;

        if (lc($self->{scoop}->config('DBType')) eq "mysql") {
                return "DATE_ADD(($left), INTERVAL $right)";
        } else {
                 return "$left + INTERVAL '$right'";
        }
}

sub AUTOLOAD {
        my $self = shift;
        my ($meth) = $AUTOLOAD =~ /(\w+)$/;
        return $self->{dbh_live}->$meth(@_);
}

sub DESTROY {
        my $self = shift;
        warnings::warnif('debugging',"<<Another ".ref( $self )." bites the dust>>");
        # remove these references so that everything is cleaned up properly
        undef $self->{scoop};
}

1;