#!/usr/bin/perl
package BuildMonitorDB;

use strict; use warnings;
use DBI;
use Data::Dumper;
use BSUtil;
use BSConfig;
use BSUtil;
use File::Copy;
use POSIX qw(strftime);
use Time::HiRes qw( clock_gettime CLOCK_REALTIME);
use URI::Escape;
use BSRPC;
use MIME::Base64;
use JSON::XS;

my $bsdir = $BSConfig::bsdir || "/srv/obs";
my $reporoot = "$bsdir/build";
my $rundir = "$bsdir/run";
my $extrepodir = "$bsdir/repos";
my $g_dbh = undef;

# DB statement handle objects.
my $project_build_check_sth;
my $project_disable_last_flag_sth;
my $check_build_target_failed_sth;
my $update_build_target_succeeded_sth;
my $project_build_start_sth;
my $get_build_target_id_sth;
my $project_build_finished_sth;
my $compensate_project_build_status_sth;
my $project_build_finished_if_not_started_check_sth;
my $project_build_finished_if_not_started_sth;
my $package_build_start_sth;
my $package_build_finished_sth;
my $search_not_succeeded_packages_sth;
my $build_status_sth;
my $build_status_exists_sth;
my $check_prior_to_build_success_sth;
my $update_build_start_time_sth;
my $package_disable_last_flag_sth;
my $insert_package_build_if_not_exist_sth;
my $package_build_status_sth;
my $package_build_broken_status_sth;
my $package_build_broken_status_end_time_only_sth;
my $check_package_build_sth;
my $get_build_project_id_sth;
my $get_build_project_id_R_sth;
my $insert_build_project_sth;
my $complete_build_project_sth;
my $get_build_project_status_sth;
my $get_project_id_sth;
my $insert_project_sth;
my $update_project_sth;
my $delete_project_info_sth;
my $get_package_id_sth;
my $insert_package_info_sth;
my $update_package_info_sth;
my $delete_package_info_sth;
my $update_latest_sr_sth;
my $delete_packages_info_sth;

#declare global variable.
my %build_target_id_cache;
my %project_package_id_cache;

#-------------------------------------------------------------------------------
# local functions
#-------------------------------------------------------------------------------
sub get_cur_time {
  my $real_time = clock_gettime(CLOCK_REALTIME);

  return $real_time;
}

sub is_main_project {
  my ($proj_name) = @_;

  my @main_projects = (
    "Tizen:Base",
    "Tizen:Mobile",
    "Tizen:TV",
    "Tizen:Wearable",
    "Tizen:Common",
    "Tizen:IVI",

    "Tizen:3.0:Base",
    "Tizen:3.0:Mobile",
    "Tizen:3.0:TV",
    "Tizen:3.0:Wearable",
    "Tizen:3.0:Common",
    "Tizen:3.0:IVI",
  );

  for my $p (@main_projects) {
    if( $p eq $proj_name ) {
      return 1;
    }
  }

  return 0;
}

sub is_prerelease_project {
  my ($proj_name) = @_;

  if( $proj_name =~ /home:prerelease/ ) {
    return 1;
  } elsif( $proj_name =~ /home:trbs/ ) {
    return 1;
  }
  return  0;
}

sub pattern_match {
  my ($event_str, $config_str) = @_;

  #print "pattern_match: event_str=[$event_str]\n";
  for my $p (@{$config_str}) {
    #print "  config_str=[$p]...\n";
    if ($event_str =~ /$p/) {
      return 1;
    }
  }
  return 0;
}

sub check_proj_pattern {
  my ($proj_name) = @_;

  if( pattern_match($proj_name, \@BSConfig::exclude_build_monitor_projects_pattern) ) {
    #print "[", __LINE__, "] BuildMonitor: Exclude project $proj_name\n";
    return 1;
  }
  if( !pattern_match($proj_name, \@BSConfig::include_build_monitor_projects_pattern) ) {
    #print "[", __LINE__, "] BuildMonitor: Not include project $proj_name\n";
    return 1;
  }

  return 0;
}

sub check_proj_pattern_for_relay {
  my ($proj_name) = @_;

  if( pattern_match($proj_name, \@BSConfig::exclude_build_monitor_projects_pattern_for_relay) ) {
    #print "[", __LINE__, "] BuildMonitor: Exclude project $proj_name\n";
    return 1;
  }
  if( !pattern_match($proj_name, \@BSConfig::include_build_monitor_projects_pattern_for_relay) ) {
    #print "[", __LINE__, "] BuildMonitor: Not include project $proj_name\n";
    return 1;
  }

  return 0;
}

sub check_prior_to_build_success {
  my ($build_project_id, $build_target_id, $info_package_id, $package_build_time) = @_;

  $check_prior_to_build_success_sth->execute($build_project_id, $build_target_id,
    $info_package_id, $package_build_time);

  my $arr_ref = $check_prior_to_build_success_sth->fetchrow_arrayref;
  if( ! $arr_ref ) {
    # if not found, return 0.
    return 0;
  }

  my $bpa_id = @$arr_ref[0];

  print "[", __LINE__, "] returning bpa_id = $bpa_id\n";

  # return bpa.id
  return $bpa_id;
}

sub update_build_start_time {
  my ($bpa_id, $package_build_time) = @_;

  $update_build_start_time_sth->execute($package_build_time, $bpa_id);
}

#-------------------------------------------------------------------------------
# event handlers
#-------------------------------------------------------------------------------
sub srcsrv_start {
  my ($paramRef) = @_;

  # update all projects and packages info.
  print "[", __LINE__, "] SRCSRV_START!\n";

  my $start_time = get_cur_time();
  initialize_projpack_info($paramRef->{'projpack'});
  my $elapsed_time = get_cur_time() - $start_time;
  print "[",__LINE__,"] took $elapsed_time seconds.\n";

  return 1;
}

sub srcsrv_create_project {
  my ($paramRef) = @_;

  my $proj_name = $paramRef->{'project'};
  my $description = $paramRef->{'description'};
  my $link_proj = $paramRef->{'link_proj'};

  if( check_proj_pattern($proj_name) ) {
    print "[", __LINE__, "] Skip DB logging $proj_name.\n";
    return 0;
  }

  $description = substr($description, 0, 3000);
  print "[", __LINE__, "] create a project ($proj_name, $description)\n";
  insert_or_update_project_info($proj_name, $description, $link_proj);

  return 1;
}

sub srcsrv_update_project {
  my ($paramRef) = @_;

  my $proj_name = $paramRef->{'project'};
  my $description = $paramRef->{'description'};
  my $link_proj = $paramRef->{'link_proj'};

  if( check_proj_pattern($proj_name) ) {
    print "[", __LINE__, "] Skip DB logging $proj_name.\n";
    return 0;
  }

  $description = substr($description, 0, 3000);
  print "[", __LINE__, "] update a project ($proj_name, $description)\n";
  insert_or_update_project_info($proj_name, $description, $link_proj);

  return 1;
}

sub srcsrv_delete_project {
  my ($paramRef) = @_;

  my $proj_name = $paramRef->{'project'};

  if( check_proj_pattern($proj_name) ) {
    print "[", __LINE__, "] Skip DB logging $proj_name.\n";
    return 0;
  }

  print "[", __LINE__, "] delete a project ($proj_name )\n";
  delete_project_info($proj_name);

  return 1;
}

sub srcsrv_create_package {
  my ($paramRef) = @_;

  my $proj_name = $paramRef->{'project'};
  my $pkg_name  = $paramRef->{'package'};
  my $description = $paramRef->{'description'};

  if( check_proj_pattern($proj_name) ) {
    print "[", __LINE__, "] Skip DB logging $proj_name.\n";
    return 0;
  }

  $description = substr($description, 0, 3000);
  print "[", __LINE__, "] create a package ($proj_name, $pkg_name, $description)\n";
  insert_or_update_package_info($proj_name, $pkg_name, $description);

  return 1;
}

sub srcsrv_update_package {
  my ($paramRef) = @_;

  my $proj_name = $paramRef->{'project'};
  my $pkg_name  = $paramRef->{'package'};
  my $description = $paramRef->{'description'};

  if( check_proj_pattern($proj_name) ) {
    print "[", __LINE__, "] Skip DB logging $proj_name.\n";
    return 0;
  }

  $description = substr($description, 0, 3000);
  print "[", __LINE__, "] update a package ($proj_name, $pkg_name, $description)\n";
  insert_or_update_package_info($proj_name, $pkg_name, $description);

  return 1;
}

sub srcsrv_delete_package {
  my ($paramRef) = @_;

  my $proj_name = $paramRef->{'project'};
  my $pkg_name  = $paramRef->{'package'};

  if( check_proj_pattern($proj_name) ) {
    print "[", __LINE__, "] Skip DB logging $proj_name.\n";
    return 0;
  }

  print "[", __LINE__, "] delete a package ($proj_name, $pkg_name)\n";
  delete_package_info($proj_name, $pkg_name);

  return 1;
}

sub srcsrv_request_accepted {
  my ($paramRef) = @_;

  my $target_proj = $paramRef->{'targetproject'};
  my $source_proj = $paramRef->{'sourceproject'};

  if( check_proj_pattern($target_proj) ) {
    print "[", __LINE__, "] Skip DB logging $target_proj.\n";
    return 0;
  }

  print "[", __LINE__, "] request accepted src=$source_proj, target=$target_proj\n";
  insert_build_project_table($target_proj);

  return 1;
}

sub srcsrv_request_statechange {
  my ($paramRef) = @_;

  if( $paramRef->{'state'} == 'accepted' ) {
    print "[", __LINE__, "] paramRef = ";
    print Dumper($paramRef);
    return srcsrv_request_accepted($paramRef);
  }

  return 0;
}

sub srcsrv_commit {
  my ($paramRef) = @_;

  #my $proj_name = $paramRef->{'project'};
  #my $pack = $paramRef->{'pack'};

  #my $build_project_id = get_build_project_id("test", $proj_name);
#
  #my $param = {
    #'uri' => "$BSConfig::srcserver/source/$proj_name/_meta",
  #};
  #my $proj = BSRPC::rpc($param, $BSXML::proj);
  #my $repos = $proj->{'repository'};
  #for my $repo (@$repos) {
    #my $archs = $repo->{'arch'};
    #for my $arch (@$archs) {
      ##print "SRCSRV_COMMIT: $repo->{'name'}/$arch\n";
      #my $repo_name = $repo->{'name'};
      #print "[",__LINE__, "] SRCSRV_COMMIT. trigger make_dep_graph for $proj_name/$repo_name/$arch, $build_project_id\n";
      #trigger_make_dep_graph($build_project_id, $proj_name, $repo_name, $arch);
    #}
  #}

  return 0;
}

sub build_start {
  my ($paramRef) = @_;

  my $proj_name = $paramRef->{'project'};
  my $repo = $paramRef->{'repository'};
  my $arch = $paramRef->{'arch'};
  my $pkg_name = $paramRef->{'package'};
  my $reason = $paramRef->{'reason'};
  my $time = $paramRef->{'time'};
  my $build_log_url = decode_base64($paramRef->{'build_log_url'});

  my $config = "";

  if( check_proj_pattern($proj_name) ) {
    print "[", __LINE__, "] Skip DB logging $proj_name.\n";
    return 0;
  }

  package_build_start($config, $proj_name, $repo, $arch, $pkg_name, $time, $reason, $build_log_url);

  return 1;
}

sub build_unchanged {
  my ($paramRef) = @_;

  my $proj_name = $paramRef->{'project'};
  my $repo = $paramRef->{'repository'};
  my $arch = $paramRef->{'arch'};
  my $pkg_name = $paramRef->{'package'};
  my $pre_install_time = $paramRef->{'pre_install_time'};
  my $install_time = $paramRef->{'install_time'};
  my $main_build_time = $paramRef->{'main_build_time'};
  my $time = $paramRef->{'time'};

  my $status = "unchanged";
  my $config = "";

  if( check_proj_pattern($proj_name) ) {
    print "[", __LINE__, "] Skip DB logging $proj_name.\n";
    return 0;
  }

  package_build_finished($config, $proj_name, $repo, $arch, $pkg_name, $time, $status,
      $pre_install_time, $install_time, $main_build_time);

  return 1;
}

sub build_success {
  my ($paramRef) = @_;

  my $proj_name = $paramRef->{'project'};
  my $repo = $paramRef->{'repository'};
  my $arch = $paramRef->{'arch'};
  my $pkg_name = $paramRef->{'package'};
  my $pre_install_time = $paramRef->{'pre_install_time'};
  my $install_time = $paramRef->{'install_time'};
  my $main_build_time = $paramRef->{'main_build_time'};
  my $time = $paramRef->{'time'};

  my $status = "succeeded";
  my $config = "";

  if( check_proj_pattern($proj_name) ) {
    print "[", __LINE__, "] Skip DB logging $proj_name.\n";
    return 0;
  }

  package_build_finished($config, $proj_name, $repo, $arch, $pkg_name, $time, $status,
      $pre_install_time, $install_time, $main_build_time);

  return 1;
}

sub build_kill {
  my ($paramRef) = @_;

  my $proj_name = $paramRef->{'project'};
  my $repo = $paramRef->{'repository'};
  my $arch = $paramRef->{'arch'};
  my $pkg_name = $paramRef->{'package'};
  my $pre_install_time = 0;
  my $install_time = 0;
  my $main_build_time = 0;
  my $time = $paramRef->{'time'};

  my $status = "killed";
  my $config = "";

  if( check_proj_pattern($proj_name) ) {
    print "[", __LINE__, "] Skip DB logging $proj_name.\n";
    return 0;
  }

  package_build_finished($config, $proj_name, $repo, $arch, $pkg_name, $time, $status,
      $pre_install_time, $install_time, $main_build_time);

  return 1;
}

sub build_fail {
  my ($paramRef) = @_;

  my $proj_name = $paramRef->{'project'};
  my $repo = $paramRef->{'repository'};
  my $arch = $paramRef->{'arch'};
  my $pkg_name = $paramRef->{'package'};
  my $pre_install_time = $paramRef->{'pre_install_time'};
  my $install_time = $paramRef->{'install_time'};
  my $main_build_time = $paramRef->{'main_build_time'};
  my $time = $paramRef->{'time'};

  my $status = "failed";
  my $config = "";

  if( check_proj_pattern($proj_name) ) {
    print "[", __LINE__, "] Skip DB logging $proj_name.\n";
    return 0;
  }

  package_build_finished($config, $proj_name, $repo, $arch, $pkg_name, $time, $status,
      $pre_install_time, $install_time, $main_build_time);

  return 1;
}

sub build_broken {
  my ($paramRef) = @_;

  my $proj_name = $paramRef->{'project'};
  my $repo = $paramRef->{'repository'};
  my $arch = $paramRef->{'arch'};
  my $pkg_name = $paramRef->{'package'};
  my $detail = $paramRef->{'detail'};
  my $time = $paramRef->{'time'};

  my $status = "broken";
  my $config = "";

  if( check_proj_pattern($proj_name) ) {
    print "[", __LINE__, "] Skip DB logging $proj_name.\n";
    return 0;
  }

  package_build_status($config, $proj_name, $repo, $arch, $pkg_name, $time, $status, $detail);

  return 1;
}

sub build_unresolvable {
  my ($paramRef) = @_;

  my $proj_name = $paramRef->{'project'};
  my $repo = $paramRef->{'repository'};
  my $arch = $paramRef->{'arch'};
  my $pkg_name = $paramRef->{'package'};
  my $detail = $paramRef->{'detail'};
  my $time = $paramRef->{'time'};

  my $status = "unresolvable";
  my $config = "";

  if( check_proj_pattern($proj_name) ) {
    print "[", __LINE__, "] Skip DB logging $proj_name.\n";
    return 0;
  }

  package_build_status($config, $proj_name, $repo, $arch, $pkg_name, $time, $status, $detail);

  return 1;
}

sub build_disabled {
  my ($paramRef) = @_;

  my $proj_name = $paramRef->{'project'};
  my $repo = $paramRef->{'repository'};
  my $arch = $paramRef->{'arch'};
  my $pkg_name = $paramRef->{'package'};
  my $detail = $paramRef->{'detail'};
  my $time = $paramRef->{'time'};

  my $status = "disabled";
  my $config = "";

  if( check_proj_pattern($proj_name) ) {
    print "[", __LINE__, "] Skip DB logging $proj_name.\n";
    return 0;
  }

  print "[", __LINE__, "] BUILD_DISABLED$proj_name, $repo, $arch, $pkg_name\n";
  package_build_status($config, $proj_name, $repo, $arch, $pkg_name, $time, $status, $detail);

  return 1;
}

sub build_excluded {
  my ($paramRef) = @_;

  my $proj_name = $paramRef->{'project'};
  my $repo = $paramRef->{'repository'};
  my $arch = $paramRef->{'arch'};
  my $pkg_name = $paramRef->{'package'};
  my $detail = $paramRef->{'detail'};
  my $time = $paramRef->{'time'};

  my $status = "excluded";
  my $config = "";

  if( check_proj_pattern($proj_name) ) {
    print "[", __LINE__, "] Skip DB logging $proj_name.\n";
    return 0;
  }

  print "[", __LINE__, "] BUILD_EXCLUDED $proj_name, $repo, $arch, $pkg_name\n";
  package_build_status($config, $proj_name, $repo, $arch, $pkg_name, $time, $status, $detail);

  update_project_build_status_if_failed($proj_name, $repo, $arch);

  return 1;
}

sub repo_publish_state {
  my ($paramRef) = @_;

  my $proj_name = $paramRef->{'project'};
  my $repo = $paramRef->{'repo'};
  my $state = $paramRef->{'state'};
  my $arch = $paramRef->{'arch'};
  my $time = $paramRef->{'time'};

  my $config = "";

  if( check_proj_pattern($proj_name) ) {
    print "[", __LINE__, "] Skip DB logging $proj_name.\n";
    return 0;
  }

  if( $state eq "published") {
    #if published, the project build is finished.
    project_build_finished($config, $proj_name, $repo, $arch, $time);

    return 1;
  }
  return 0;
}

sub repo_published {
  my ($paramRef) = @_;

  my $proj_name = $paramRef->{'project'};

  if( check_proj_pattern($proj_name) ) {
    print "[", __LINE__, "] Skip DB logging $proj_name.\n";
    return 0;
  }

  build_project_completed($proj_name);

  return 1;
}

#-------------------------------------------------------------------------------
sub trigger_make_dep_graph {
  my ($build_project_id, $proj_name, $repo, $arch) = @_;

  # trigger making build_progress graph.
  my $jenkinsuri = "$BSConfig::jenkinsserver/job/make_dep_graph/buildWithParameters";
  my $param = {
    'request' => 'POST',
    'uri' => $jenkinsuri,
    'timeout' => 60,
    'maxredirects' => 1,
  };
  my $args = {
    'action' => 'build_progress',
    'build_project_id'=>$build_project_id,
    'obs_project' => $proj_name,
    'repo' => $repo,
    'arch' => $arch,
  };
  my @para = ("TRIGGER_INFO=".encode_base64(encode_json($args), ''));
  print "notify: trigger Jenkins uri=[$jenkinsuri] para=[@para]\n";
  eval {
    BSRPC::rpc( $param, undef, @para );
  };
  warn("Jenkins: $@") if $@;
}

sub trigger_obs_published {
  my ($proj_name) = @_;

  # trigger OBS_REPO_PUBLISHED.
  my $jenkinsuri = "$BSConfig::jenkinsserver/job/obs-event-dispatcher/buildWithParameters";
  my $param = {
    'request' => 'POST',
    'uri' => $jenkinsuri,
    'timeout' => 60,
    'maxredirects' => 1,
  };
  my $args = {
    'repo' => 'manually triggered by BuildMonitorDB',
    'project' => $proj_name,
    'event_type' => 'OBS_REPO_PUBLISHED'
  };
  my @para = ("project=$proj_name",
              "event_type=OBS_REPO_PUBLISHED",
              "para=".encode_base64(encode_json($args), ''));
  print "notify: trigger Jenkins uri=[$jenkinsuri] para=[@para]\n";
  eval {
    BSRPC::rpc( $param, undef, @para );
  };
  warn("Jenkins: $@") if $@;
}

sub parse_reason {
  my ($tt) = @_;

  my $explain="";
  my $detail="";

  return $tt if ref($tt) ne 'HASH';

  #print Dumper($tt);
  while( my ($key, $value) = each $tt ) {

    if( $key eq 'explain' ) {
      $explain = $value;
    } elsif( $key eq 'packagechange' ) {
      $detail = $detail . parse_packagechange($value);
    } elsif( $key eq 'oldsource' ) {
    } else {
      print "undefined key-->\n";
      print Dumper($key);
      print Dumper($value);
    }
  }

  return substr("$explain($detail)", 0, 100);
}

sub parse_packagechange {
  my ($packages) = @_;

  my $detail_str = "";
  my $str_prev = undef;

  foreach my $hash_ref (@{$packages}) {
    if ($str_prev) {
      $detail_str = $detail_str . ",";
    }
    $str_prev="$hash_ref->{'key'}($hash_ref->{'change'})";
    $detail_str = $detail_str .  $str_prev;
  }

  return $detail_str;
}

#-------------------------------------------------------------------------------
sub get_project_id {
  my ($project_name) = @_;

  if( defined $project_package_id_cache{$project_name}{'id'} ) {
    return $project_package_id_cache{$project_name}{'id'};
  }

  $get_project_id_sth->execute($project_name);

  my $arr_ref = $get_project_id_sth->fetchrow_arrayref;
  if( !$arr_ref ) {
    return undef;
  }

  #print Dumper($arr_ref);
  my $project_id=@$arr_ref[0];

  $project_package_id_cache{$project_name}{'id'} = $project_id;

  return $project_id;
}

#-------------------------------------------------------------------------------
sub get_package_id {
  my ($project_name, $package_name) = @_;

  if( defined $project_package_id_cache{$project_name}{$package_name} ) {
    return $project_package_id_cache{$project_name}{$package_name};
  }

  my $proj_id=get_project_id($project_name);
  if( ! $proj_id  ) {
    print "$project_name: No such project.\n";
    return undef;
  }

  my $package_id;
  $get_package_id_sth->execute($proj_id, $package_name);
  my $arr_ref = $get_package_id_sth->fetchrow_arrayref;
  if( ! $arr_ref ) {
    $package_id = @$arr_ref[0];
    eval {
      $g_dbh->begin_work();
      $get_package_id_sth->execute($proj_id, $package_name);
      my $arr_ref = $get_package_id_sth->fetchrow_arrayref;
      if( ! $arr_ref ) {
# if not found, create one.
        my $description = "";
        #print "[", __LINE__, "][jh0822][insert_package_info] $proj_id, $package_name, $description\n";
        $insert_package_info_sth->execute($proj_id, "$package_name", $description);
        $package_id = $insert_package_info_sth->{mysql_insertid};
        #print "newly created package_id($package_id) for $package_name.\n";
      } else {
        $package_id = @$arr_ref[0];
      }
      $g_dbh->commit();
    };

    if($@) {
      print "rollback: $@\n";
      $g_dbh->rollback();
      $get_package_id_sth->execute($proj_id, $package_name);
      my $arr_ref = $get_package_id_sth->fetchrow_arrayref;
      $package_id = @$arr_ref[0];
    }
  } else {
    $package_id = @$arr_ref[0];
  }

  $project_package_id_cache{$project_name}{$package_name} = $package_id;

  #print "[", __LINE__, "][get_package_id] $package_name($package_id)\n";
  return $package_id;
}

sub initialize_projpack_info {
  my ($projpack) = @_;

  if( ref($projpack) ne 'HASH' ) {
    $projpack = decode_json(decode_base64($projpack));
  }

  my $projects = $projpack->{'project'};

  for my $proj (@$projects) {
    my $proj_name   = $proj->{'name'};

    if( check_proj_pattern($proj_name) ) {
      next;
    }

    my $description = "";
    $description = $proj->{'description'} if defined $proj->{'description'};

    #print "[", __LINE__, "] insert project: $proj_name\n";
    insert_or_update_project_info($proj_name, $description, "");

    my $packages    = $proj->{'package'};
    if( scalar(@$packages) > 0 ) {
      for my $pkg (@$packages) {
        my $pkg_name = $pkg->{'name'};
        #print "[", __LINE__, "] insert package: $proj_name, $pkg_name\n";
        insert_package_info($proj_name, $pkg_name, "");
      }
    }
  }

}

#-------------------------------------------------------------------------------
sub insert_build_project_table {
  my ($proj_name) = @_;

  my $proj_id=get_project_id($proj_name);
  my $failed = 1;
  my $ret = 0;
  while ($failed) {
    $failed = 0;
    eval {
      $g_dbh->begin_work();

      $get_build_project_id_R_sth->execute($proj_id, "R");
      my $arr_ref = $get_build_project_id_R_sth->fetchrow_arrayref;
      if( ! $arr_ref ) {
        $insert_build_project_sth->execute($proj_id, "R");
        my $build_project_id = $insert_build_project_sth->{mysql_insertid};
        print "[", __LINE__, "] new build_project_id: $build_project_id for $proj_name\n";
      } else {
        print "[", __LINE__, "] already has an item ($proj_id, R) proj=$proj_name!\n";
        $ret = 1;
      }

      $g_dbh->commit();
    };

    if($@) {
      warn "[", __LINE__, "] Transaction aborted because $@\n";
      $g_dbh->rollback();
      $failed = 1;
      printf "[", __LINE__, "] waiting 10 seconds...\n";
      sleep(10);
    }
  }

  return $ret;
}

sub build_project_completed {
  my ($proj_name) = @_;

  my $proj_id=get_project_id($proj_name);
  my $build_project_id = get_build_project_id("", $proj_name);

  # check all packages built in this project.
  # if any last package build status is failed.

  my $final_status = "C";
  my $failed_trans = 1;
  while($failed_trans) {
    $failed_trans = 0;
    eval {
      $g_dbh->begin_work();
      $build_status_sth->execute($build_project_id);
      my %build_status;
      while( my $arr_ref = $build_status_sth->fetchrow_arrayref ) {
        my $packid = @$arr_ref[0];
        my $status = @$arr_ref[1];
        my $repo = @$arr_ref[2];
        my $arch = @$arr_ref[3];

        $build_status{$packid}{$repo}{$arch} = $status;
      }

      my $previous_status = "";
      $get_build_project_status_sth->execute($build_project_id);
      while( my $arr_ref = $get_build_project_status_sth->fetchrow_arrayref ) {
        $previous_status = @$arr_ref[0];
      }

      my $reason = "";
      for my $packid (keys %build_status) {
        for my $repo (keys $build_status{$packid} ) {
          for my $arch (keys $build_status{$packid}{$repo}) {
            if( $build_status{$packid}{$repo}{$arch} !~ /succeeded/i &&
                $build_status{$packid}{$repo}{$arch} !~ /building/i &&
                $build_status{$packid}{$repo}{$arch} !~ /excluded/i &&
                $build_status{$packid}{$repo}{$arch} !~ /disabled/i ) {
              $final_status = "F";
              $reason = "$reason$packid($repo/$arch):$build_status{$packid}{$repo}{$arch}, ";
            }
          }
        }
      }

      print "$proj_name: final_project_build_status=$final_status\n";
      print "  reason: $reason\n";

      if( $final_status ne $previous_status ) {
        print "[",__LINE__, "] updating project_build_status $build_project_id, $final_status\n";
        $complete_build_project_sth->execute($final_status, $build_project_id);
      }

      $g_dbh->commit();
    };
    if($@) {
      warn "[", __LINE__, "] Transaction aborted because $@\n";
      $g_dbh->rollback();
      $failed_trans = 1;
      printf "[", __LINE__, "] waiting 10 seconds...\n";
      sleep(10);
    }
  }

  return $final_status;
}

#-------------------------------------------------------------------------------
sub insert_or_update_project_info {
  my ($project_name, $description, $link_proj) = @_;

  my $pre_release_flag = 'N';
  my $active_flag = 'Y';

  if( is_prerelease_project($project_name) ) {
    $pre_release_flag = 'Y';
  }
  my $ref_proj = "";

  if( $link_proj =~ /ref:/ ) {
    $ref_proj = $link_proj;
  }

  # Insert the project only if there is no project that has the same name.
  my $proj_id = get_project_id($project_name);
  if( $proj_id ) {
    #print "We have a project that has the same name: $project_name.\n";
    # if we have the same name, update description.
    $update_project_sth->execute($description, $pre_release_flag, $active_flag, $ref_proj, $project_name);

    return;
  }

  print "[", __LINE__, "][jh0822][insert_or_update_project_info] $project_name, $description, $pre_release_flag, $active_flag, $ref_proj\n";
  $insert_project_sth->execute($project_name, $description, $pre_release_flag, $active_flag, $ref_proj);
  $project_package_id_cache{$project_name}{'id'} = $insert_project_sth->{mysql_insertid};

  if( is_prerelease_project($project_name) ) {
    insert_build_project_table($project_name);
  }
}

#-------------------------------------------------------------------------------
sub delete_project_info {
  my ($project_name) = @_;

  my $proj_id=get_project_id($project_name);
  if( ! $proj_id ) {
    print "$project_name: No such project.\n";

    return;
  }

  $delete_packages_info_sth->execute($proj_id);

  print "[", __LINE__, "][jh0822][delete_project_info] $project_name\n";
  $delete_project_info_sth->execute($project_name);
  delete $project_package_id_cache{$project_name};
}

#-------------------------------------------------------------------------------
sub delete_all_packages {
  my ($project_name) = @_;

  my $proj_id=get_project_id($project_name);
  if( ! $proj_id ) {
    print "$project_name: No such project.\n";

    return;
  }

  print "[", __LINE__, "][jh0822][delete_all_packages] $project_name\n";
  $delete_packages_info_sth->execute($proj_id);

}

#-------------------------------------------------------------------------------
sub insert_package_info {
  my ($project_name, $package_name, $description) = @_;

  my $pkg_id = get_package_id($project_name, $package_name);
  if( $pkg_id ) {
    #print "$package_name: Already existing package name in $project_name.\n";

    return;
  }

  my $proj_id = get_project_id($project_name);
  if( ! $proj_id ) {
    print "$project_name: No such project name.\n";

    return;
  }

  $description = "" unless $description;

  #print "[", __LINE__, "][jh0822][insert_package_info] $proj_id, $package_name, $description\n";
  $insert_package_info_sth->execute($proj_id, $package_name, $description);
  $project_package_id_cache{$project_name}{$package_name} = $insert_package_info_sth->{mysql_insertid};
}

#-------------------------------------------------------------------------------
sub insert_or_update_package_info {
  my ($project_name, $package_name, $description) = @_;

  my $proj_id = get_project_id($project_name);
  if( ! $proj_id ) {
    print "$project_name: No such project name.\n";

    return;
  }

  if( ! defined $description ) { $description = ""; }

  my $pkg_id = get_package_id($project_name, $package_name, $description);
  if( $pkg_id ) {
    #print "$package_name: Already existing package name in $project_name.\n";
    $update_package_info_sth->execute($description, $pkg_id);

    return;
  }

  #print "[", __LINE__, "][jh0822][insert_package_info] $proj_id, $package_name, $description\n";
  $insert_package_info_sth->execute($proj_id, "$package_name", "$description");
  $project_package_id_cache{$project_name}{$package_name} = $insert_package_info_sth->{mysql_insertid};
}

#-------------------------------------------------------------------------------
sub delete_package_info {
  my ($project_name, $package_name) = @_;

  my $proj_id = get_project_id($project_name);
  if( ! $proj_id ) {
    print "$project_name: No such project name.\n";

    return;
  }

  my $pkg_id = get_package_id($project_name, $package_name);
  if( ! $pkg_id ) {
    print "$package_name: no such package in $project_name.\n";

    return;
  }

  $delete_package_info_sth->execute($pkg_id);
  delete $project_package_id_cache{$project_name}{$package_name};
}

#-------------------------------------------------------------------------------
sub update_latest_sr {
  my ($project_name, $package_name, $latest_sr_status_id) = @_;

  my $proj_id = get_project_id($project_name);
  if( ! $proj_id ) {
    print "$project_name: No such project.\n";
    disconnect_db();
    return;
  }

  my $pkg_id = get_package_id($project_name, $package_name);
  if( ! $pkg_id ) {
    print "$package_name: No such package in $project_name.\n";
    disconnect_db();
    return;
  }

  print "[", __LINE__, "][jh0822][update_latest_sr] $latest_sr_status_id, $pkg_id\n";
  $update_latest_sr_sth->execute($latest_sr_status_id, $pkg_id);

  disconnect_db();
}

#-------------------------------------------------------------------------------
sub project_build_start {
  my ($build_project_id, $proj_name, $repo, $arch, $state, $start_time) = @_;

  if( $build_project_id <= 0 ) {
    print "[",__LINE__,"]build_project_id($build_project_id) is strange. do not execute.\n";
    return;
  }

  if( defined $build_target_id_cache{$build_project_id}{$repo}{$arch}{'status'} &&
      $build_target_id_cache{$build_project_id}{$repo}{$arch}{'status'} eq 'building' ) {
    return;
  }

  if( $state ne "Building" ) {
    print "[",__LINE__,"] state should be 'Building'!!!\n";
  }

  $state = 'R';

  $build_target_id_cache{$build_project_id}{$repo}{$arch}{'status'} = 'building';
  # insert only if there is not a row that has start_time!=0 and end_time=0.
  my $build_target_id = 0;
  eval {
    $g_dbh->begin_work();

    print "[", __LINE__, "][project_build_start] $build_project_id, $proj_name $repo, $arch, $state, $start_time\n";
    $project_build_check_sth->execute($build_project_id, $repo, $arch);
    my $arr_ref = $project_build_check_sth->fetchrow_arrayref;
    if ( ! $arr_ref ) {

      # last_flag = 'N';
      $project_disable_last_flag_sth->execute($build_project_id, $repo, $arch);

      # insert only if there is no statement that start_time=0.
      $project_build_start_sth->execute($build_project_id, $repo, $arch, $start_time, $state);
      $build_target_id = $project_build_start_sth->{mysql_insertid};
      $build_target_id_cache{$build_project_id}{$repo}{$arch}{'id'} = $build_target_id;
      print "build_target_id_cache{$build_project_id}{$repo}{$arch}{'id'} = $build_target_id_cache{$build_project_id}{$repo}{$arch}{'id'}\n";
    } else {
      print Dumper($arr_ref);
      die "WARNING!! There should be only one build start for this build_project_id($build_project_id)!!\n";
    }

    $g_dbh->commit();
  };

  if($@) {
    warn "[", __LINE__, "] Transaction aborted because $@\n";
    $g_dbh->rollback();
    return ;
  }

  if( is_main_project($proj_name) ) {
    print "[", __LINE__, "] trigger_make_dep_graph for main project $proj_name, $repo/$arch, $build_project_id\n";
    trigger_make_dep_graph($build_project_id, $proj_name, $repo, $arch);
  }

}
#-------------------------------------------------------------------------------
sub project_build_finished_if_not_started {
  my ($build_project_id, $proj, $repo, $arch, $start_time, $end_time) = @_;

  if( $build_project_id <= 0 ) {
    print "[",__LINE__,"]build_project_id($build_project_id) is strange. do not execute.\n";
    return;
  }

  my $build_target_id = get_build_target_id($build_project_id, $repo, $arch);
  my ($status, $status_detail) = get_buildmonitor_project_status($build_target_id, $proj, $repo, $arch);
  if (! $status_detail) { $status_detail=""; }

  # if there is no row that has start_time!=0 and end_time=0,
  # This means the first package is broken or unresolvable.
  eval {
    $g_dbh->begin_work();

    print "[", __LINE__, "][project_build_finished_if_not_started] $build_project_id, $repo, $arch, $status, $status_detail\n";
    $project_build_finished_if_not_started_check_sth->execute($build_project_id, $repo, $arch);
    my $arr_ref = $project_build_finished_if_not_started_check_sth->fetchrow_arrayref;
    if( ! $arr_ref ) {

      # last_flag = 'N';
      $project_disable_last_flag_sth->execute($build_project_id, $repo, $arch);

      print "[", __LINE__, "][project_build_finished_if_not_started] $build_project_id, $repo, $arch, $status, $status_detail\n";
      $project_build_finished_if_not_started_sth->execute($build_project_id, $repo, $arch, $start_time, $end_time, $status, $status_detail);
    }

    $g_dbh->commit();
  };

  $build_target_id_cache{$build_project_id}{$repo}{$arch}{'status'} = 'done';

  if($@) {
    warn "[", __LINE__, "] Transaction aborted because $@\n";
    $g_dbh->rollback();
  }
}

#-------------------------------------------------------------------------------
sub project_build_finished {
  my ($config, $projid, $repo, $arch, $time, $status, $detail) = @_;

  return unless $g_dbh;

  my $start_time = get_cur_time();
  my $build_project_id = get_build_project_id($config, $projid);

  if( $build_project_id <= 0 ) {
    #print "[",__LINE__,"]build_project_id($build_project_id) is strange. do not execute.\n";
    return;
  }

  if( ! defined $status ) {
    my $build_target_id = get_build_target_id($build_project_id, $repo, $arch);
    ($status, $detail) = get_buildmonitor_project_status($build_target_id, $projid, $repo, $arch);
  }

  # if status='S' and end_time is not zero, do not update.
  # check out $project_build_finished_sql if you want to see how to do this.

  $build_target_id_cache{$build_project_id}{$repo}{$arch}{'status'} = 'done';

  print "[", __LINE__, "][project_build_finished] $build_project_id, $projid, $repo, $arch, $status, $detail\n";
  $project_build_finished_sth->execute($time, $status, $detail, $build_project_id, "$repo", "$arch");
  my $elapsed_time = get_cur_time() - $start_time;
  print "[",__LINE__,"] took $elapsed_time seconds.\n";
}

sub cancel_package_build {
  my ($config, $prp, $arch, $package_name) = @_;

  return unless $g_dbh;
  my $start_time = get_cur_time();
  package_build_finished($config, $prp, $arch, $package_name, time(), "cancelled");
  my $elapsed_time = get_cur_time() - $start_time;
  print "[",__LINE__,"] took $elapsed_time seconds.\n";
}

sub is_package_in_project {
  my ($package_name, $proj_name) = @_;

  my $found = 0;
  my $param = {
    'uri' => "$BSConfig::srcserver/source/$proj_name/",
  };
  eval {
    my $proj = BSRPC::rpc($param, $BSXML::dir);
    my $entries = $proj->{'entry'};

    for my $e (@$entries) {
      if( $e->{'name'} eq $package_name ) {
        $found = 1;
        last;
      }
    }
  };
  warn("BuildMonitor: $@") if $@;

  return $found;
}

#-------------------------------------------------------------------------------
sub package_build_start {
  my ($config, $proj_name, $repo, $arch, $package_name, $time, $reason, $build_log_url) = @_;

  return unless $g_dbh;

  my $start_time = get_cur_time();

  my $build_project_id = get_build_project_id($config, $proj_name);
  my $info_package_id = get_package_id($proj_name, $package_name);

  if( $build_project_id <= 0 ) {
    print "[",__LINE__,"]build_project_id($build_project_id) is strange. do not execute.\n";
    print "[", __LINE__, "][package_build_start($package_name)] $build_project_id, $repo, $arch, $info_package_id\n";
    return;
  }

  $build_log_url = "" if !defined $build_log_url;

  if ( !defined $reason ) {
    $reason = "";
  } else {
    $reason=parse_reason($reason);
  }
  $reason = substr($reason, 0, 1000);

  my $state = "Building";
  my $project_build_time = $time;
  my $package_build_time = $project_build_time;

  # start the project build if this is the first building of a package in this project.
  project_build_start($build_project_id, $proj_name, $repo, $arch, $state, $project_build_time);
  my $build_target_id=get_build_target_id($build_project_id, $repo, $arch);

  # At first, we need to check this BUILD_START is an event that should be processed before BUILD_SUCCESS.
  print "[", __LINE__, "] $build_project_id, $build_target_id, $info_package_id, $package_build_time\n";
  if( my $bpa_id = check_prior_to_build_success($build_project_id, $build_target_id, $info_package_id, $package_build_time) ) {
    print "[", __LINE__, "] update build time only!!! $bpa_id, $package_build_time\n";
    update_build_start_time($bpa_id, $package_build_time);
  } else {

    insert_package_build($build_target_id, $info_package_id);
    print "[", __LINE__, "][package_build_start($package_name)] $reason, $build_target_id, $info_package_id, $build_log_url\n";
    $package_build_start_sth->execute($package_build_time, $state, $reason, $build_log_url, $build_target_id, $info_package_id);
  }

  my $elapsed_time = get_cur_time() - $start_time;
  print "[",__LINE__,"] took $elapsed_time seconds.\n";

  if( is_prerelease_project($proj_name) && is_package_in_project($package_name, $proj_name) ) {
    print "[$package_name] trigger_make_dep_graph... $proj_name, $repo/$arch, $build_project_id\n";
    trigger_make_dep_graph($build_project_id, $proj_name, $repo, $arch);
  }
}

#-------------------------------------------------------------------------------
sub get_build_target_id {
  my ($build_project_id, $repo, $arch) = @_;

  if ( defined $build_target_id_cache{$build_project_id}{$repo}{$arch}{'id'} ) {
    return $build_target_id_cache{$build_project_id}{$repo}{$arch}{'id'};
  }

  $get_build_target_id_sth->execute($build_project_id, $repo, $arch);
  my $arr_ref = $get_build_target_id_sth->fetchrow_arrayref;
  return 0 if ! defined $arr_ref;

  my $build_id = @$arr_ref[0];

  $build_target_id_cache{$build_project_id}{$repo}{$arch}{'id'} = $build_id;

  return $build_id;
}

#-------------------------------------------------------------------------------
sub insert_package_build {
  my ($build_target_id, $info_package_id) = @_;

  # last_flag = 'N';
  $package_disable_last_flag_sth->execute($build_target_id, $info_package_id);

  print "[", __LINE__, "][insert_package_build] $build_target_id, $info_package_id\n";
  return $insert_package_build_if_not_exist_sth->execute($build_target_id, $info_package_id);
}

#-------------------------------------------------------------------------------
sub insert_package_build_if_not_exist {
  my ($build_target_id, $info_package_id, $current_status) = @_;

  $check_package_build_sth->execute($build_target_id, $info_package_id);
  my $arr_ref = $check_package_build_sth->fetchrow_arrayref;
  if ( defined $arr_ref ) {
    # if the last status is the same as the current status, do not insert.
    if( @$arr_ref[0] eq $current_status ) {
      return 0;
    }

    #going through...
  }

  # last_flag = 'N';
  $package_disable_last_flag_sth->execute($build_target_id, $info_package_id);

  #print "[", __LINE__, "][insert_package_build_if_not_exist] $build_target_id, $info_package_id\n";
  return $insert_package_build_if_not_exist_sth->execute($build_target_id, $info_package_id);
}

#-------------------------------------------------------------------------------
sub get_buildmonitor_project_status {
  my ($build_target_id, $projid, $repoid, $archid) = @_;

  my $failed_detail = "";
  my $failed = 'S';

  print "[",__LINE__,"] get_buildmonitor_project_status $projid, $repoid, $archid, $build_target_id...\n";

  # commit the current transaction to see the lastest value.
  my $failed_trans = 1;
  while($failed_trans) {
    $failed_trans = 0;
    eval {
      $g_dbh->begin_work();
      $search_not_succeeded_packages_sth->execute($build_target_id);
      while( my $arr_ref = $search_not_succeeded_packages_sth->fetchrow_arrayref ) {
# we found not succeeded packages in this build_target_id.
        $failed_detail = $failed_detail . @$arr_ref[0] . "(" . @$arr_ref[1] . ":" . @$arr_ref[2] . "),";
        $failed = 'F';
      }
      $g_dbh->commit();
    };
    if($@) {
      warn "[", __LINE__, "] Transaction aborted because $@\n";
      $g_dbh->rollback();
      $failed_trans = 1;
      printf "[", __LINE__, "] waiting 10 seconds...\n";
      sleep(10);
    }
  }

  $failed_detail = substr($failed_detail, 0, 100);

  print "[", __LINE__, "] $projid: $failed, $failed_detail\n";

  return ($failed, $failed_detail);
}

sub build_status_exists {
  my ($build_project_id, $repo, $arch, $info_package_id) = @_;

  return 0 unless $g_dbh;

  $build_status_exists_sth->execute($build_project_id, $repo, $arch, $info_package_id);
  my $arr_ref = $build_status_exists_sth->fetchrow_arrayref;
  if( ! $arr_ref ) {
    return 0;
  }

  return 1;
}

#-------------------------------------------------------------------------------
sub package_build_status {
  my ($config, $proj, $repo, $arch, $package_name, $time, $status, $detail) = @_;

  return unless $g_dbh;
  if ($status eq 'scheduled' ) {
    # This 'scheduled' state occurs after package_build_start, so do not insert this state to the table.
    return;
  }
  if ($status eq 'blocked' ) {
    # This 'blocked' state always changed to scheduling state, do not log.
    return;
  }

  my $start_time = get_cur_time();
  my $build_project_id = get_build_project_id($config, $proj);

  if( ! $detail ) { $detail = ""; }

  if( $build_project_id <= 0 ) {
    #print Dumper($config);
    #print "[",__LINE__,"][$proj] build_project_id($build_project_id) is strange. do not execute.\n";
    #print "[", __LINE__, "][package_build_status] $status, $detail, $build_project_id, $repo, $arch, $info_package_id, $trigger_reason\n";
    return;
  }

  if ($status eq 'done') {
    # do not insert 'done', this 'done' implies all of 'succeeded', 'failed', and 'unchanged'.
    $status = $detail;
    $detail = "";
  }

  my $info_package_id = get_package_id($proj, $package_name);

  my $state = "Building";
  project_build_start($build_project_id, $proj, $repo, $arch, $state, $start_time);
  my $build_target_id=get_build_target_id($build_project_id, $repo, $arch);
  print "[", __LINE__, "] $build_project_id, $build_target_id, $info_package_id, $start_time\n";

  # for excluded, it is inserted only if there is any other package build_status.
  if( $status eq 'excluded' || $status eq 'disabled' ) {
    if( ! build_status_exists($build_project_id, $repo, $arch, $info_package_id) ) {
      return;
    }
  }

  # if no row for this package_build. insert a row.
  my $affected_rows = insert_package_build_if_not_exist($build_target_id, $info_package_id, $status);

  if( $affected_rows == 0 ) {
    if( $status eq "broken" ) {
      my $cur_time = $time;
      print "[", __LINE__, "][package_build_status] $status, $detail, $build_target_id, $info_package_id\n";
      $package_build_broken_status_end_time_only_sth->execute("$status", "$detail", $cur_time, $build_target_id, $info_package_id);
    }
  } else {
    if( $status eq "failed" || $status eq "broken"  || $status eq "unresolvable" ||
        $status eq "excluded" || $status eq "disabled" ) {
      # except "no source uploaded."
      if( $detail ne "no source uploaded" ) {
        my $cur_time = $time;
        print "[", __LINE__, "][package_build_status] $status, $detail, $build_project_id, $repo, $arch, $info_package_id\n";
        $package_build_broken_status_sth->execute("$status", "$detail", $cur_time-1, $cur_time, $build_target_id, $info_package_id);
        #project_build_finished_if_not_started($build_project_id,$proj,$repo,$arch,$cur_time-1, $cur_time);
      }
    } else {
      #print "[", __LINE__, "][package_build_status] $status, $detail, $build_project_id, $repo, $arch, $info_package_id\n";
      #$package_build_status_sth->execute("$status", "$detail", $build_target_id, $info_package_id);
    }
  }
  my $elapsed_time = get_cur_time() - $start_time;
  print "[",__LINE__,"] took $elapsed_time seconds.\n";
}

sub check_build_target_failed {
  my ($build_target_id) = @_;

  $check_build_target_failed_sth->execute($build_target_id);
  my $arr_ref = $check_build_target_failed_sth->fetchrow_arrayref;
  if( !$arr_ref ) {
    return 0;
  }

  return @$arr_ref[0] eq 'F';
}

sub update_build_target_succeeded {
  my ($build_target_id) = @_;

  print "[", __LINE__, "] update build_target succeeded.\n";
  $update_build_target_succeeded_sth->execute($build_target_id);
}

sub update_project_build_status_if_failed {
  my ($proj_name, $repo, $arch) = @_;

  my $update_required = 0;

  my $build_project_id = get_build_project_id("", $proj_name);
  my $build_target_id  = get_build_target_id($build_project_id, $repo, $arch);

  # if the build_status of build_target is failed, we need to recalculate this.
  print "[", __LINE__, "] Check build target failed.\n";
  if( ! check_build_target_failed($build_target_id) ) {
    return;
  }

  my ($status, $detail) = get_buildmonitor_project_status($build_target_id, $proj_name, $repo, $arch);
  print "[", __LINE__, "] buildmonitor_project_status = $status\n";

  if( $status eq 'F' ) {
    return;
  }

  # update build_target status
  update_build_target_succeeded($build_target_id);

  # update build_project status.
  if( build_project_completed($proj_name) eq 'C' ) {
    # if final status is completed, send published event to jenkins to make
    # a snapshot for this version.
    trigger_obs_published($proj_name);
  }
}

#-------------------------------------------------------------------------------
sub get_rawlog_url {
  my ($proj_name, $repo, $arch, $pkg_name) = @_;

  my @arr;
  if ( $BSConfig::obs_version eq "2.4" ) {
    # for obs 2.4
    @arr = (
        $BSConfig::obs_frontend_url,
        "package",
        "rawlog?arch=$arch&package=$pkg_name&project=$proj_name&repository=$repo"
        );
  } else {
    # for obs 2.7
    @arr = (
        $BSConfig::obs_frontend_url,
        "public/build",
        $proj_name,
        $repo,
        $arch,
        $pkg_name,
        "_log"
        );
  }
  my $url = join("/", @arr);

  return $url;
}

sub copy_build_log {
  my ($projid, $repo, $arch, $packid, $log_file, $status) = @_;

  my $aextrep = "$projid/$repo/$arch";
  $aextrep =~ s/:/:\//g;
  my $build_log_dir = "buildlogs/$aextrep";
  my $build_log_machine_dir = "$extrepodir/$build_log_dir";
  mkdir_p($build_log_machine_dir);

  my $datetime_str=strftime "%Y%m%d_%H:%M:%S", localtime;
  my $filename = "$packid-$datetime_str.log";
  my $dst_file_path = "$build_log_machine_dir/$filename";

  # copy only if the package build is failed.
  if( $status ne "succeeded" ) {
    copy($log_file, $dst_file_path) or print "[",__LINE__,"] Copy failed: $!\n";
  }

  my $log_url = "$BSConfig::repodownload/"."$build_log_dir/$filename";

  return $log_url;
}
#-------------------------------------------------------------------------------
sub package_build_finished {
  my ($config, $projid, $repo, $arch, $package_name, $time,
      $status, $pre_install_time, $install_time, $main_build_time) = @_;

  return unless $g_dbh;
  my $start_time = get_cur_time();
  my $build_project_id = get_build_project_id($config, $projid);

  my $detail = "";

  if( $status eq 'unchanged' ) {
    $status = 'succeeded';
    $detail = 'unchanged';
  }

  my $info_package_id=get_package_id($projid, $package_name);
  if ( $package_name =~ ".*_aggregate") {
      $pre_install_time = 0;
      $install_time = 0;
      $main_build_time = 0;
  } else {
    #home:prerelease:Tizen:Mobile:submit:tizen:20161101.025248
    #$build_log_url=copy_build_log($projid, $repo, $arch, $package_name, "$build_result_dir/logfile", $status);
    #$build_log_url=get_rawlog_url($projid, $repo, $arch, $package_name);

    if ( ! defined $pre_install_time ) {
      $pre_install_time = 0;
      $install_time = 0;
      $main_build_time = 0;
    }
  }

  if( $build_project_id <= 0 ) {
    #print "[",__LINE__,"]build_project_id($build_project_id) is strange. do not execute.\n";
    #print "[", __LINE__, "][package_build_finished($package_name)] $pre_install_time, $install_time, $main_build_time, $build_log_url, $status, $build_project_id, $repo, $arch, $info_package_id\n";
    return;
  }

  # Since _aggregate packages does not trigger BUILD_START, it always calls package_build_start().
  if ( $package_name =~ ".*_aggregate" ) {
    package_build_start("", $projid, $repo, $arch, $package_name, $time);
  }

  my $build_target_id=get_build_target_id($build_project_id, $repo, $arch);
  print "[", __LINE__, "][package_build_finished($package_name)] $pre_install_time, $install_time, $main_build_time, $status, $build_target_id, $repo, $arch, $info_package_id\n";
  my $transaction_aborted = 1;
  while( $transaction_aborted ) {
    $transaction_aborted = 0;
    eval {
      $g_dbh->begin_work();

      # if there is no 'Building' rows, insert a row.
      # This happens when BUILD_SUCCESS is processed before BUILD_START is processed.
      if( insert_package_build_if_not_exist($build_target_id, $info_package_id, 'Building') ) {
        my $state = "Building";
        my $reason = "";
        my $build_log_url = "";

        print "[", __LINE__, "][NO_BUILDING! package_build_start($package_name)] $reason, $build_target_id, $info_package_id, $build_log_url\n";
        $package_build_start_sth->execute(1, $state, $reason, $build_log_url, $build_target_id, $info_package_id);
      }

      $package_build_finished_sth->execute($time, $pre_install_time, $install_time, $main_build_time, "$status", "$detail", $build_target_id, $info_package_id);
      $g_dbh->commit();
    };
    if($@) {
      $transaction_aborted = 1;
      warn "[", __LINE__, "] Transaction aborted because $@\n";
    }
  }

  if( $status eq 'failed' ) {
    eval {
      $g_dbh->begin_work();
      # compensate project build status if it is failed.
      $compensate_project_build_status_sth->execute('F', "$package_name:Failed, ", $build_target_id, $time);
      $g_dbh->commit();
    };
    if($@) {
      warn "[", __LINE__, "] Transaction aborted because $@\n";
    }
  }

  my $elapsed_time = get_cur_time() - $start_time;
  print "[",__LINE__,"] took $elapsed_time seconds.\n";
}

#-------------------------------------------------------------------------------
sub parse_statistics {
  my ($filename) = @_;

  if ( ! defined $filename ) {
    return undef;
  }

  #print "filename=$filename\n";
  #if( open(DATA1, "<$filename") ) {
    #while( <DATA1> ) {
      #print $_;
    #}
  #}
  my $statistics=BSUtil::readxml($filename, $BSXML::buildstatistics, 1);

  #print Dumper($statistics);

  my $preinstall=$statistics->{'times'}->{'preinstall'}->{'time'}->{'_content'};
  my $install   =$statistics->{'times'}->{'install'}->{'time'}->{'_content'};
  my $main      =$statistics->{'times'}->{'main'}->{'time'}->{'_content'};

  return $preinstall, $install, $main;
}

sub connect_db {
  my ($db_conf_file_user) = @_;

  return $g_dbh if defined $g_dbh && $g_dbh->ping ;

  my $db_conf_file = $db_conf_file_user || "db.conf";
  my %db_conn_info = get_db_connect_info($db_conf_file);

  #print Dumper(%db_conn_info);

  my $db=$db_conn_info{'DB'};
  my $host=$db_conn_info{'HOST'};
  my $port=$db_conn_info{'PORT'};
  my $user=$db_conn_info{'USER'};
  my $pass=$db_conn_info{'PASS'};

  my $enabled=$db_conn_info{'ENABLED'};
  return if( $enabled && $enabled eq 'FALSE' );

  my $dbh=DBI->connect("DBI:mysql:database=$db;host=$host;port=$port;mysql_connect_timeout=3",$user,$pass);
  if( ! $dbh ) {
    print "BuildMonitor DB connection failed. host=$host, port=$port, user=$user\n";
    return;
  }

  $dbh->{mysql_auto_reconnect} = 1;

  print "[BuildMonitor] DB is connected to $host, DB:$db\n";

  $g_dbh = $dbh;

  #-----------------------------------------------------------------------------
  # build_target TABLE
  my $project_build_check_sql=qq/SELECT * FROM build_target WHERE build_project_id=? AND repository=? AND arch=? AND start_time!=0 AND end_time=0;/;
  $project_build_check_sth=$g_dbh->prepare($project_build_check_sql);

  my $project_disable_last_flag_sql=qq/UPDATE build_target SET last_flag='N' WHERE build_project_id=? AND repository=? AND arch=?;/;
  $project_disable_last_flag_sth=$g_dbh->prepare($project_disable_last_flag_sql);

  my $check_build_target_failed_sql = qq/SELECT status FROM build_target WHERE id=?;/;
  $check_build_target_failed_sth = $g_dbh->prepare($check_build_target_failed_sql);

  my $update_build_target_succeeded_sql = qq/UPDATE build_target SET status='S' WHERE id=?;/;
  $update_build_target_succeeded_sth = $g_dbh->prepare($update_build_target_succeeded_sql);

  #my $project_build_start_sql=qq/UPDATE build_target SET start_time=FROM_UNIXTIME(?), status=? WHERE build_project_id=? and repository=? and arch=? and start_time=0;/;
  my $project_build_start_sql=qq/INSERT INTO build_target (build_project_id, repository, arch, start_time, status) VALUES (?,?,?,FROM_UNIXTIME(?),?);/;
  $project_build_start_sth=$g_dbh->prepare($project_build_start_sql);

  my $get_build_target_id_sql=qq/SELECT id FROM build_target WHERE build_project_id=? AND repository=? AND arch=? ORDER BY id DESC LIMIT 1;/;
  $get_build_target_id_sth=$g_dbh->prepare($get_build_target_id_sql);

  #my $project_build_finished_sql=qq/UPDATE build_target SET end_time=FROM_UNIXTIME(?), status=?, status_reason=? WHERE build_project_id=? and repository=? and arch=? and start_time!=0 ORDER BY id DESC LIMIT 1;/;
  my $project_build_finished_sql=qq/UPDATE build_target SET end_time=FROM_UNIXTIME(?), status=?, status_reason=? WHERE build_project_id=? and repository=? and arch=? and start_time!=0 AND end_time=0;/;
  $project_build_finished_sth=$g_dbh->prepare($project_build_finished_sql);

  my $compensate_project_build_status_sql=qq/UPDATE build_target SET status=?, status_reason=CONCAT(?,status_reason) WHERE id=? and start_time!=0 AND end_time > ? AND last_flag='Y';/;
  $compensate_project_build_status_sth=$g_dbh->prepare($compensate_project_build_status_sql);

  my $project_build_finished_if_not_started_check_sql=qq/SELECT * FROM build_target WHERE build_project_id=? AND repository=? AND arch=? AND start_time!=0 AND end_time=0;/;
  $project_build_finished_if_not_started_check_sth=$g_dbh->prepare($project_build_finished_if_not_started_check_sql);

  my $project_build_finished_if_not_started_sql=qq/INSERT build_target (build_project_id, repository, arch, start_time, end_time, status, status_reason) VALUES(?, ?, ?, FROM_UNIXTIME(?), FROM_UNIXTIME(?), ?, ?);/;
  $project_build_finished_if_not_started_sth = $g_dbh->prepare($project_build_finished_if_not_started_sql);

  #-----------------------------------------------------------------------------
  # build_package TABLE
  my $package_build_start_sql=qq/UPDATE build_package SET start_time=FROM_UNIXTIME(?), build_status=?, trigger_reason=?, build_log_url=? WHERE build_target_id=? AND info_package_id=? ORDER BY id DESC LIMIT 1;/;
  $package_build_start_sth=$g_dbh->prepare($package_build_start_sql);

  my $package_build_status_sql=qq/UPDATE build_package SET build_status=?, status_reason=? WHERE build_target_id=? AND info_package_id=? ORDER BY id DESC LIMIT 1;/;
  $package_build_status_sth=$g_dbh->prepare($package_build_status_sql);

  my $package_build_broken_status_sql=qq/UPDATE build_package SET build_status=?, status_reason=?, start_time=FROM_UNIXTIME(?), end_time=FROM_UNIXTIME(?) WHERE build_target_id=? AND info_package_id=? ORDER BY id DESC LIMIT 1;/;
  $package_build_broken_status_sth=$g_dbh->prepare($package_build_broken_status_sql);

  my $package_build_broken_status_end_time_only_sql=qq/UPDATE build_package SET build_status=?, status_reason=?, end_time=FROM_UNIXTIME(?) WHERE build_target_id=? AND info_package_id=? ORDER BY id DESC LIMIT 1;/;
  $package_build_broken_status_end_time_only_sth=$g_dbh->prepare($package_build_broken_status_end_time_only_sql);

  my $package_disable_last_flag_sql=qq/UPDATE build_package SET last_flag='N' WHERE build_target_id=? AND info_package_id=?;/;
  $package_disable_last_flag_sth=$g_dbh->prepare($package_disable_last_flag_sql);

  my $insert_package_build_if_not_exist_sql=qq/INSERT INTO build_package (build_target_id, info_package_id) VALUES(?,?);/;
  $insert_package_build_if_not_exist_sth=$g_dbh->prepare($insert_package_build_if_not_exist_sql);

  my $check_package_build_sql=qq/SELECT build_status FROM build_package WHERE build_target_id=? AND info_package_id=? ORDER BY id DESC LIMIT 1;/;
  $check_package_build_sth=$g_dbh->prepare($check_package_build_sql);

  my $package_build_finished_sql=qq/UPDATE build_package SET end_time=FROM_UNIXTIME(?), pre_install_time=?, install_time=?, main_build_time=?, build_status=?, status_reason=? WHERE build_target_id=? AND info_package_id=? AND start_time!=0 AND end_time=0 ORDER BY id DESC LIMIT 1;/;
  $package_build_finished_sth=$g_dbh->prepare($package_build_finished_sql);

  my $search_not_succeeded_packages_sql = qq/SELECT ip.package_name, bp.build_status, bp.status_reason FROM build_package bp, info_package ip WHERE bp.build_target_id=? AND bp.build_status != 'succeeded' AND bp.build_status != 'excluded' AND bp.build_status != 'disabled' AND ip.id = bp.info_package_id AND bp.last_flag='Y';/;
  $search_not_succeeded_packages_sth = $g_dbh->prepare($search_not_succeeded_packages_sql);

  my $build_status_sql = qq/SELECT ipa.package_name, bpa.build_status, bt.repository, bt.arch FROM info_package ipa, build_package bpa, build_project bp, build_target bt WHERE bpa.build_target_id = bt.id AND bt.build_project_id = bp.id AND bpa.info_package_id = ipa.id AND bp.id=? ORDER BY bpa.id;/;
  $build_status_sth = $g_dbh->prepare($build_status_sql);

  my $build_status_exists_sql = qq/SELECT bp.id FROM build_project bp, build_target bt, build_package bpa WHERE bp.id = bt.build_project_id AND bpa.build_target_id = bt.id AND bp.id = ?  AND bt.repository = ?  AND bt.arch = ?  AND bpa.info_package_id = ? LIMIT 1;/;
  $build_status_exists_sth = $g_dbh->prepare($build_status_exists_sql);

  my $check_prior_to_build_success_sql =
qq/
SELECT bpa.id
FROM build_project bp, build_target bt, build_package bpa
WHERE bt.build_project_id = bp.id
AND bpa.build_target_id = bt.id
AND bp.id = ?
AND bt.id = ?
AND bpa.info_package_id = ?
AND bpa.end_time > FROM_UNIXTIME(?)
AND bpa.start_time = FROM_UNIXTIME(1)
/;
  $check_prior_to_build_success_sth = $g_dbh->prepare($check_prior_to_build_success_sql);

  my $update_build_start_time_sql =
qq/
UPDATE build_package SET start_time=FROM_UNIXTIME(?) WHERE id=?
/;
  $update_build_start_time_sth = $g_dbh->prepare($update_build_start_time_sql);

  #-----------------------------------------------------------------------------
  # build_project TABLE
  my $get_build_project_id_sql=qq/SELECT id FROM build_project WHERE info_project_id=? ORDER BY id DESC LIMIT 1;/;
  $get_build_project_id_sth=$g_dbh->prepare($get_build_project_id_sql);

  my $get_build_project_id_R_sql=qq/SELECT id FROM build_project WHERE info_project_id=? AND status=?;/;
  $get_build_project_id_R_sth = $g_dbh->prepare($get_build_project_id_R_sql);

  my $insert_build_project_sql=qq/INSERT INTO build_project (info_project_id, status) VALUES(?,?);/;
  $insert_build_project_sth=$g_dbh->prepare($insert_build_project_sql);

  my $get_build_project_status_sql = qq/SELECT status FROM build_project WHERE id=?;/;
  $get_build_project_status_sth = $g_dbh->prepare($get_build_project_status_sql);

  my $complete_build_project_sql=qq/UPDATE build_project SET status=? WHERE id=?;/;
  $complete_build_project_sth = $g_dbh->prepare($complete_build_project_sql);

  #-----------------------------------------------------------------------------
  # info_project TABLE
  my $get_project_id_sql=qq/SELECT id FROM info_project WHERE project_name=? AND active_flag='Y' LIMIT 1;/;
  $get_project_id_sth=$g_dbh->prepare($get_project_id_sql);

  my $insert_project_sql=qq/INSERT INTO info_project (project_name, description, pre_release_flag, active_flag, ref_proj) VALUES (?,?,?,?,?);/;
  $insert_project_sth=$g_dbh->prepare($insert_project_sql);

  my $update_project_sql=qq/UPDATE info_project SET description=?, pre_release_flag=?, active_flag=?, ref_proj=? WHERE project_name=?;/;
  $update_project_sth=$g_dbh->prepare($update_project_sql);

  #my $delete_project_info_sql=qq/DELETE FROM info_project WHERE project_name=?;/;
  my $delete_project_info_sql=qq/UPDATE info_project SET active_flag='N' WHERE project_name=? AND active_flag='Y';/;
  $delete_project_info_sth=$g_dbh->prepare($delete_project_info_sql);

  #-----------------------------------------------------------------------------
  # info_package TABLE
  my $get_package_id_sql=qq/SELECT id FROM info_package WHERE info_project_id=? AND package_name=? AND active_flag='Y' LIMIT 1;/;
  $get_package_id_sth=$g_dbh->prepare($get_package_id_sql);

  my $insert_package_info_sql=qq/INSERT INTO info_package (info_project_id, package_name, description, active_flag) VALUES (?,?,?, 'Y');/;
  $insert_package_info_sth=$g_dbh->prepare($insert_package_info_sql);

  my $update_package_info_sql=qq/UPDATE info_package SET description=? WHERE id=? AND active_flag='Y';/;
  $update_package_info_sth=$g_dbh->prepare($update_package_info_sql);

  my $delete_package_info_sql=qq/UPDATE info_package SET active_flag='N' WHERE id=? AND active_flag='Y';/;
  $delete_package_info_sth=$g_dbh->prepare($delete_package_info_sql);

  my $update_latest_sr_sql=qq/UPDATE info_package SET latest_sr_status_id=? WHERE id=? AND active_flag='Y';/;
  $update_latest_sr_sth=$g_dbh->prepare($update_latest_sr_sql);

  #my $delete_packages_info_sql=qq/DELETE FROM info_package WHERE info_project_id=?;/;
  my $delete_packages_info_sql=qq/UPDATE info_package SET active_flag='N' WHERE info_project_id=? AND active_flag='Y';/;
  $delete_packages_info_sth=$g_dbh->prepare($delete_packages_info_sql);

  return $dbh;
}

#-------------------------------------------------------------------------------
sub disconnect_db {
  my ($dbh) = @_;

  print "BuildMonitorDB disconnecting...\n";

  $dbh->disconnect();
}

#-------------------------------------------------------------------------------
# Private modules.
#-------------------------------------------------------------------------------
sub trim { my $s = shift; $s =~ s/^\s+|\s+$//g; return $s };

#-------------------------------------------------------------------------------
sub get_db_connect_info {
  my ($db_conf_file) = @_;
  my %db_conn_info;

  open(DB_CONF_FILE, "<$db_conf_file") or die "$db_conf_file: No DB conf file.";

  while(<DB_CONF_FILE>) {
    my $line = $_;

    if( $line =~ /^DB:/ ) {
      my @sp= split /:/, $line;
      $db_conn_info{'DB'} = trim($sp[1]);
    } elsif( $line =~ /^HOST:/ ) {
      my @sp= split /:/, $line;
      $db_conn_info{'HOST'} = trim($sp[1]);
    } elsif( $line =~ /^PORT:/ ) {
      my @sp= split /:/, $line;
      $db_conn_info{'PORT'} = trim($sp[1]);
    } elsif( $line =~ /^USER:/ ) {
      my @sp= split /:/, $line;
      $db_conn_info{'USER'} = trim($sp[1]);
    } elsif( $line =~ /^PASS:/ ) {
      my @sp= split /:/, $line;
      $db_conn_info{'PASS'} = trim($sp[1]);
    } elsif( $line =~ /^ENABLED:/ ) {
      my @sp= split /:/, $line;
      $db_conn_info{'ENABLED'} = trim($sp[1]);
    }
  }

  return %db_conn_info;
}

sub get_build_project_id {
  my ($config_str, $proj_name) = @_;

  my $info_project_id = get_project_id($proj_name);
  eval {
    $g_dbh->begin_work();
    $get_build_project_id_sth->execute($info_project_id);
    $g_dbh->commit();
  };
  my $arr_ref = $get_build_project_id_sth->fetchrow_arrayref;
  if ( defined $arr_ref ) {
    my $build_project_id = @$arr_ref[0];
    print "build_project_id $proj_name => $info_project_id => $build_project_id\n";
    return $build_project_id;
  }

  if( $config_str eq "test" ) {
    return $info_project_id;
  }

  if( ! $config_str ) {
    return 0;
  }

  if( $config_str =~ /BuildProjectId:\s*([0-9]+)/ ) {
    if( ! $1 ) {
      return 0;
    }

    return $1;
  }

  return 0;
}
#-------------------------------------------------------------------------------
# final statement.
1;
