#!/usr/bin/perl
use strict;
use DBI;
use HTTP::Tiny;

my $api_key="xxxxxxxxxxxxxxxxxxxxxxxxxxxxx";
my @databases = ("xxxxxxxxxxxxx", "xxxxxxxxxxx");
my $http = HTTP::Tiny->new();

#globals
my $response;
my $do_log=0;

open(LOG,">out.log") if ($do_log);

foreach my $db (@databases) {
  my $dbh = DBI->connect("DBI:mysql:database=$db;host=xxxxxxxxxxxxxxx",
                          "xxxxx", "xxxxx",{'RaiseError' => 1});

  #TV Shows
  my $sth = $dbh->prepare("SELECT media_id,value,type,count(media_id) AS nr, tvshow.c00 AS title FROM uniqueid, tvshow ".
                          "WHERE media_type LIKE 'tvshow' AND media_id = idShow GROUP BY media_id;");
  $sth->execute();

  while (my $ref = $sth->fetchrow_hashref()) {
    if ($ref->{'nr'} > 1) { next; }
    if ($ref->{'type'} eq 'unknown') {
      print "DB $db, $ref->{'title'} (MEDIA_ID $ref->{'media_id'}):\n";
      my $repeat = 1;
      while ($repeat) {
        print " -> asking TMDB for tvshow $ref->{'value'}\n";
        my $url = "https://api.themoviedb.org/3/find/".$ref->{'value'}.
          "?api_key=".$api_key."&language=en-US&external_source=tvdb_id";
        $response = $http->get($url);
        print LOG "$url\n$response->{content}\n" if ($do_log);
        if ($response->{content} =~ /"status_code":25,/) { print " -> sleep\n"; sleep 1; }
        else { $repeat = 0; }
      }
      if ($response->{content} =~ /"id":(\d+),"original_language"/) {
        $repeat = 1;
        while ($repeat) {
          print " -> asking TMDB for TMDB_ID $1\n";
          my $url = "https://api.themoviedb.org/3/tv/".$1."/external_ids".
            "?api_key=".$api_key."&language=en-US";
          $response = $http->get($url);
          print LOG "$url\n$response->{content}\n" if ($do_log);
          if ($response->{content} =~ /"status_code":25,/) { print " -> sleep\n"; sleep 1; }
          else { $repeat = 0; }
        }
        if ($response->{content} =~ /"imdb_id":"(tt\d+)"/) {
          print " -> putting IMDB_ID $1 to DB\n";
          $dbh->do("INSERT INTO uniqueid VALUES (?, ?, ?, ?, ?)",
            undef, undef, $ref->{'media_id'}, "tvshow", $1, "imdb");
        }
      }
      print " done.\n";
    }
  }
  $sth->finish;

  #episodes
  my $sth = $dbh->prepare("SELECT media_id,value,type,count(media_id) AS nr, tvshow.c00 AS title, ".
                          "episode.c00 AS eptitle FROM uniqueid, tvshow, episode ".
                          "WHERE media_type LIKE 'episode' AND media_id = idEpisode ".
                          "AND episode.idShow = tvshow.idShow GROUP BY media_id;");
  $sth->execute();

  while (my $ref = $sth->fetchrow_hashref()) {
    my ($show_id,$ep_nr,$se_nr);
    if ($ref->{'nr'} > 1) { next; }
    if ($ref->{'type'} eq 'unknown') {
      print "DB $db, $ref->{'title'}, $ref->{'eptitle'} (MEDIA_ID $ref->{'media_id'}):\n";
      my $repeat = 1;
      while ($repeat) {
        print " -> asking TMDB for tvshow $ref->{'value'}\n";
        my $url = "https://api.themoviedb.org/3/find/".$ref->{'value'}.
          "?api_key=".$api_key."&language=en-US&external_source=tvdb_id";
        $response = $http->get($url);
        print LOG "$url\n$response->{content}\n" if ($do_log);
        if ($response->{content} =~ /"status_code":25,/) { print " -> sleep\n"; sleep 1; }
        else { $repeat = 0; }
      }
      if ($response->{content} =~ /"show_id":(\d+),/) {
        $show_id = $1;
        if ($response->{content} =~ /"episode_number":(\d+),/) {
          $ep_nr = $1;
          if ($response->{content} =~ /"season_number":(\d+),/) {
            $se_nr = $1;
            $repeat = 1;
            while ($repeat) {
              print " -> asking TMDB for show_id $show_id season $se_nr episode $ep_nr\n";
              my $url = "https://api.themoviedb.org/3/tv/".$show_id."/season/".$se_nr."/episode/".$ep_nr.
                "/external_ids?api_key=".$api_key."&language=en-US";
              $response = $http->get($url);
              print LOG "$url\n$response->{content}\n" if ($do_log);
              if ($response->{content} =~ /"status_code":25,/) { print " -> sleep\n"; sleep 1; }
              else { $repeat = 0; }
            }
            if ($response->{content} =~ /"imdb_id":"(tt\d+)"/) {
              print " -> putting IMDB_ID $1 to DB\n";
              $dbh->do("INSERT INTO uniqueid VALUES (?, ?, ?, ?, ?)",
                undef, undef, $ref->{'media_id'}, "episode", $1, "imdb");
            }
          }
        }
      }
      print " done.\n";
    }
  }

  $sth->finish;

  $dbh->disconnect();
}

close LOG if ($do_log);