#!/usr/bin/perl

use strict;
use warnings;
use DBI;

# MySQL
our $DB_NAME = "YOUR_DB_NAME";
our $DB_USER = "YOUR_DB_USER";
our $DB_PASS = "YOUR_DB_PASS";
our $DB_HOST = "YOUR_DB_HOST";
our $DB_PORT = "3306";

my $dbh = DBI->connect("dbi:mysql:dbname=$DB_NAME;host=$DB_HOST;port=$DB_PORT","$DB_USER","$DB_PASS") or die "$!\n Error: failed to connect to DB.\n";

# get blog path
my $blog = $dbh->prepare("select blog_parent_id ,blog_site_path from mt_blog where blog_site_path like 'service/_' OR blog_site_path like 'service/__';");
# get website path
my $web = $dbh->prepare("select blog_id , blog_site_path from mt_blog where blog_class = 'website' AND blog_id = ANY (select blog_parent_id from mt_blog where blog_site_path like 'service/_' OR blog_site_path like 'service/__')");

$blog->execute();
$web->execute();

my $website;
while (my $ary_web_ref = $web->fetchrow_arrayref) {
  my($web_id, $web_path) = @$ary_web_ref;
  $web_path =~ s!/([0-9]{1,})$!/$1/!g;
  $website->{$web_id} = $web_path;
}

while (my $ary_ref = $blog->fetchrow_arrayref) {
  my ($blog_parent_id, $blog_path) = @$ary_ref;
  my $blog_path_m = $blog_path;
  $blog_path =~ s!/([0-9]{1,})$!/$1/index.html!g;
  $blog_path =~ s!/([0-9]{1,})/$!/$1/index.html!g;

  $blog_path_m =~ s!/([0-9]{1,})$!/$1/m/index.php!g;
  $blog_path_m =~ s!/([0-9]{1,})/$!/$1/m/index.php!g;

  foreach my $key(keys $website){
    if ($blog_parent_id == $key) {
      if (unlink "$website->{$key}$blog_path") {
        print "'$website->{$key}$blog_path'は削除されました。\n";
      } else {
      	print "'$website->{$key}$blog_path'は削除できませんでした。\n";
      }
      if (unlink "$website->{$key}$blog_path_m") {
        print "'$website->{$key}$blog_path_m'は削除されました。\n";
      } else {
      	print "'$website->{$key}$blog_path_m'は削除できませんでした。\n";
      }
    }
  }
}

$blog->finish;
$web->finish;
$dbh->disconnect;