package My_DBVersions;

use strict;

our %ua;
our %cache;

our @versionchanges;
my $versionnumber=0;

$versionchanges[$versionnumber++] = "select now()";
$versionchanges[$versionnumber++] =  "ALTER TABLE `dbStaffEvents`.`tUSER_CATEGORY`  ADD COLUMN `agency_accepted` TINYINT NULL DEFAULT 0 COMMENT '' AFTER `hourly_cost`;";

$versionchanges[$versionnumber++] =  "INSERT INTO `dbStaffEvents`.`tAGENCY_SETTING_TYPES` (`value`, `long_desc`, `default_value`, `can_edit`, `can_view`, `setting_type`) VALUES ('EventStaffForgotPasswordEmail', 'Forgot Password Email', 'You have requested a new password<br>\nHere is you login details:<br>\nEmail : ~~EMAIL~~<br>\nPassword : ~~PASSWORD~~  <br>\n<br>\nRegards\n', '1', '1', 'TEXT');";
$versionchanges[$versionnumber++] =  "INSERT INTO `dbStaffEvents`.`tAGENCY_SETTING_TYPES` (`value`, `long_desc`, `default_value`, `can_edit`, `can_view`, `setting_type`) VALUES ('EventStaffForgotPasswordSMS', 'Forgot Password SMS', 'New password\nHere is you login details:\nEmail : ~~EMAIL~~\nPassword : ~~PASSWORD~~\n', '1', '1', 'TEXT');";

$versionchanges[$versionnumber++] =  "ALTER TABLE `dbStaffEvents`.`tUSER_CATEGORY` ADD UNIQUE INDEX `index_main` (`tUSER_id` ASC, `tUSER_CATEGORY_TYPES_id` ASC)  COMMENT '';";

$versionchanges[$versionnumber++] =  "ALTER TABLE `dbStaffEvents`.`tUSER_PROFILE_TYPES` ADD COLUMN `compulsory` TINYINT(1) NULL DEFAULT 0 COMMENT '' AFTER `profile_type`;";

$versionchanges[$versionnumber++] =  "INSERT INTO `dbStaffEvents`.`tUSER_PROFILE_TYPES` (`tUSER_TYPE_id`, `value`, `long_desc`, `can_edit`, `can_view`, `client_can_view`, `save_to_disk`, `profile_type`, `compulsory`) VALUES ('5', 'Suburb', 'Which Suburb do you live in?', '1', '1', '1', '0', 'TEXT', '1');";

$versionchanges[$versionnumber++] =  "INSERT INTO `dbStaffEvents`.`tAGENCY_SETTING_TYPES` (`value`, `long_desc`, `default_value`, `can_edit`, `can_view`, `setting_type`) VALUES ('UserActivated', 'Confirmation Email for Activated Users', 'Congratulations, Your account has now been activated on the system.\n', '1', '1', 'TEXT');";

$versionchanges[$versionnumber++] =  "INSERT INTO `dbStaffEvents`.`tAGENCY_SETTING_TYPES` (`value`, `long_desc`, `default_value`, `can_edit`, `can_view`, `setting_type`) VALUES ('UserDeclinedActivation', 'Email to the user on a Activation Declined Event', 'Your activation was declined for the following reason ~~REASON~~. Please fix the above to attempt another activation', '1', '1', 'TEXT');";

# $versionchanges[$versionnumber++] =  "";

#--------------------------------------------------------------------------------------------------
sub new
{
  my $pclass = shift;
  my $pdbhMain = shift;
  my $pGLOBALS = shift;
  my $self = {
    dbh=>$pdbhMain,
    GLOBALS=>$pGLOBALS,
    logger=>Log::Log4perl->get_logger()
  };
  bless $self, $pclass;
  $self->{logger}->info("::DBVersions::Instance Started");
  return $self;
}

#--------------------------------------------------------------------------------------------------
#--------------------------------------------------------------------------------------------------
sub check_db_version($)
{
  my $this=shift;
  my $rxml="";
  my $statement ="";
  my $sth;  
  my @row;
  my $dbversion =0;
  my $failure="";

  for(my $x=0;$x<@versionchanges;$x++)
  {
    $statement = "select id from tDBVERSION where `sql`=?";
    #$this->{logger}->info($statement."||".$versionchanges[$x]);
    $sth = $this->{dbh}->prepare("$statement");
    $sth->execute($versionchanges[$x]);
    if($sth->rows<=0 )
    {
      if($sth->rows<0)
      {
        $statement = "CREATE TABLE `tDBVERSION` (`id` int(11) NOT NULL AUTO_INCREMENT,`sql` varchar(767) DEFAULT '', PRIMARY KEY (`id`), INDEX `main_index` (`sql`))";
        $this->{logger}->info($statement);
        $sth = $this->{dbh}->prepare("$statement");
        $sth->execute;
      }
      $statement = $versionchanges[$x];
      $this->{logger}->info($statement);
      $sth = $this->{dbh}->prepare("$statement");
      $sth->execute;
    
      if($sth->rows>=0)
      {
        $statement = "insert into tDBVERSION (`sql`) values (?)";
        #$this->{logger}->info($statement."||".$versionchanges[$x]);
        $sth = $this->{dbh}->prepare("$statement");
        $sth->execute($versionchanges[$x]);
      }
      else
      {
        $failure .= "$versionchanges[$x]\n\n\n";
      }
    }
  }  
  
  if($failure ne "")
  {
    $failure =~ s/\"/\\\"/ig;
    $failure =~ s/\`/\\\`/ig;
    my $cmd = "/usr/bin/sendEmail -t \"daniev\@smartcall.co.za\" -f \"dbversions\@`hostname`\" -u \"DB Version Failed to execute a query\" -m \"The query that failed:\n\n$failure\"";          
    $this->{logger}->info($cmd);
    `$cmd`;  
  }
}

1;