#!/usr/bin/perl -w

# rank_update.pl
# (c) CWZ, LLC

# All ranks interval update

use strict;
use lib '.';
use CWZ;

Exec: {
    # Wait a couple seconds to make sure the minute-to-minute market update completes
    # Has to happen after both rank_hourly and rank_daily also
   sleep(2);


   # UPDATE Squad_Rank with SUMs from all Player values (with special one for loc 0 being Overall)
   #
   qDb_do("UPDATE Squad_Rank sr SET value = (SELECT SUM(market + cash) FROM Player p WHERE p.sid = sr.sid) WHERE loc = 0");
   qDb_do("REPLACE INTO Squad_Rank SELECT sid, loc, 0, SUM(market + cash), 0, 0, 0, 0
         FROM Player
         GROUP BY sid, loc
         HAVING sid <> 0 AND loc > 0");
         qDb_do("UPDATE Squad_Rank sr
LEFT JOIN Player p ON sr.sid = p.sid AND sr.loc = p.loc
SET sr.value = NULL
WHERE p.loc IS NULL AND sr.loc > 0;");
   qDb_do("DELETE FROM Squad_Rank WHERE value is null");   			# Eliminate locations with null values

   #
   # UPDATE Alliance_Rank with SUMs from all Squad values (with special one for loc 0 being Overall)
   #
   qDb_do("UPDATE Alliance_Rank ar
         SET value = (SELECT SUM(sr.value) FROM Squad_Rank sr JOIN Squad s ON sr.sid = s.sid WHERE s.aid = ar.aid AND sr.loc = 0)
         WHERE loc = 0");

   qDb_do("DELETE FROM Squad_Rank WHERE value is null");   			# Eliminate locations with null values

   #
   # Do the global and local Player rank update
   #
   my @loc_rank = (0,0,0,0,0,0,0,0,0,0,0);

   my $all_players = qDb_ar("SELECT pid, sid, glob_rank, loc_rank, loc, market + cash AS value
   		FROM Player
   		ORDER BY value DESC, pid");

   foreach ( @$all_players ) {
   	$_->{glob_rank} = ++$loc_rank[0];											# 0 is for Overall
   	$_->{loc_rank} = ++$loc_rank[$_->{loc}];
   }

   # Prepare and execute a statement handle for this massive Player rank update
   my $sth = $DBH->prepare("UPDATE Player SET glob_rank = ?, loc_rank = ? WHERE pid = ?");
   $sth->execute($_->{glob_rank}, $_->{loc_rank}, $_->{pid}) foreach ( @$all_players);
   $sth->finish();

   #
   # Now do kind of the same for just local Squad rank updates
   #
   my @squad_rank = (0,0,0,0,0,0,0,0,0,0,0);

	my $all_squads = qDb_ar("SELECT * FROM Squad_Rank ORDER BY loc, value DESC, sid");
	$_->{loc_rank} = ++$squad_rank[$_->{loc}] foreach @$all_squads;

   # Prepare and execute another statement handle for Squad_Rank update
   $sth = $DBH->prepare("UPDATE Squad_Rank SET loc_rank = ? WHERE sid = ? AND loc = ?");
   $sth->execute($_->{loc_rank}, $_->{sid}, $_->{loc}) foreach ( @$all_squads);
   $sth->finish();

   #
   # Now do kind of the same for Alliance rank updates
   #
   my @alliance_rank = (0,0,0,0,0,0,0,0,0,0,0);

 my $all_alliances = qDb_ar("SELECT * FROM Alliance_Rank ORDER BY loc, value DESC, aid");
 $_->{loc_rank} = ++$alliance_rank[$_->{loc}] foreach @$all_alliances;

   # Prepare and execute another statement handle for Squad_Rank update
   $sth = $DBH->prepare("UPDATE Alliance_Rank SET loc_rank = ? WHERE aid = ? AND loc = ?");
   $sth->execute($_->{loc_rank}, $_->{aid}, $_->{loc}) foreach ( @$all_alliances);
   $sth->finish();

   # UPDATE Squad tabulations for kills, steals, loot etc
   # ..STUB

   # UPDATE the Squad_Rank hourly and daily percentage changes
   # ..trying this (value + 1) thing to see what it does about divide by 0s
   qDb_do("UPDATE Squad_Rank
SET hourly_pct = IF(hourly_ref = 0, 0, ROUND(((value - hourly_ref) / hourly_ref) * 100, 2)),
    daily_pct = IF(daily_ref = 0, 0, ROUND(((value - daily_ref) / daily_ref) * 100, 2))");
    qDb_do("UPDATE Alliance_Rank
 SET hourly_pct = IF(hourly_ref = 0, 0, ROUND(((value - hourly_ref) / hourly_ref) * 100, 2)),
     daily_pct = IF(daily_ref = 0, 0, ROUND(((value - daily_ref) / daily_ref) * 100, 2))");

   # Finally, Update player class based on training
	qDb_do("UPDATE Player SET sk_class = 'Investor'
				WHERE sk_funding + sk_strength > sk_marketing + sk_techanal + sk_intuition
					AND sk_funding + sk_strength > sk_cunning + sk_debate + sk_re");

	qDb_do("UPDATE Player SET sk_class = 'Influencer'
				WHERE sk_marketing + sk_techanal + sk_intuition >  sk_funding + sk_strength
					AND sk_marketing + sk_techanal + sk_intuition > sk_cunning + sk_debate + sk_re");

	qDb_do("UPDATE Player SET sk_class = 'Doubter'
				WHERE sk_cunning + sk_debate + sk_re > sk_funding + sk_strength
					AND sk_cunning + sk_debate + sk_re > sk_marketing + sk_techanal + sk_intuition");

          # Insert the data into Crypto Live History Table in the database
          qDb_do("UPDATE Crypto_Live_History SET price_history = CONCAT((SELECT price_now FROM Crypto_Live WHERE Crypto_Live.cd = Crypto_Live_History.cd), ',', price_history), timestamp = NOW() WHERE EXISTS (SELECT 1 FROM Crypto_Live WHERE Crypto_Live.cd = Crypto_Live_History.cd);");
          qDb_do("UPDATE Crypto_Game_History SET price_history = CONCAT((SELECT price_now FROM Crypto_Game WHERE Crypto_Game.cd = Crypto_Game_History.cd), ',', price_history), timestamp = NOW() WHERE EXISTS (SELECT 1 FROM Crypto_Game WHERE Crypto_Game.cd = Crypto_Game_History.cd);");

}
