#!/usr/bin/perl

# Yamamoto.T

$sqlcmd = "psql";
$sqlopt = "-At -c";

print STDERR "--------------------------------------------\n";
print STDERR "* script for moving Item through Community *\n";
print STDERR "--------------------------------------------\n";

#---------------
while(1) {
  print STDERR "input the handle of item which moves:\n";

  $a=<STDIN> or die;
  chomp $a;

  $item_id = `$sqlcmd $sqlopt \"select resource_id from handle where handle = '$a' and resource_type_id = 2\"`;
  chomp $item_id;

  last if $item_id;
  print STDERR "error: no such item\n";

}

print STDERR "\n";
print STDERR "[item_id: $item_id] ... OK\n";

#---------------
while(1) {
  print STDERR "input the handle of collection the item moves to:\n";

  $a=<STDIN> or die;
  chomp $a;

  $collection_id = `$sqlcmd $sqlopt \"select resource_id from handle where handle = '$a' and resource_type_id = 3\"`;
  chomp $collection_id;

  last if $collection_id;
  print STDERR "error: no such collection\n";

}

print STDERR "\n";
print STDERR "[collection_id: $collection_id] ... OK\n";

#----------
$community_id = `$sqlcmd $sqlopt \"select community_id from community2collection where collection_id = $collection_id\"`;
chomp $community_id;

print STDERR "[community_id: $community_id] ... OK\n";

$community_id2 = `$sqlcmd $sqlopt \"select parent_comm_id from community2community where child_comm_id = $community_id\"`;
chomp $community_id2;

print STDERR "[community_id2: $community_id2] ... OK\n" if $community_id2;

$sqls = '';
$sqls .= "-- phase 1\n";
$sqls .= "update item set owning_collection = $collection_id where item_id = $item_id;\n";
$sqls .= "--phase 2\n";
$sqls .= "delete from collection2item where item_id = $item_id;\n";
$sqls .= "insert into collection2item values (getnextid('collection2item'), $collection_id, $item_id);\n";
$sqls .= "--phase 3\n";
$sqls .= "delete from communities2item where item_id = $item_id;\n";
$sqls .= "insert into communities2item values (getnextid('communities2item'), $community_id, $item_id);\n";
$sqls .= "insert into communities2item values (getnextid('communities2item'), $community_id2, $item_id);\n" if $community_id2;

print $sqls;

print "Execute? [y/n]";
$c = <STDIN> or die;
if ($c =~ /^[yY]/) {
  print "-- execute sql\n";
  open CPIPE, "| psql dspace";
  print CPIPE $sqls;
  close CPIPE;
} else {
  print "-- no change\n";
}

