#!/usr/bin/perl

use DBI;
use Getopt::Std;

getopts('td:u:p:U:P:l');

$database = $opt_d ? $opt_d : "midgard";
$user = $opt_u ? $opt_u : "midgard";
$password = $opt_p ? $opt_p : "midgard";
$root = $opt_U ? $opt_U : 'root';
$rootpw = $opt_P ? $opt_P : '';

######

$driver=0;
foreach (DBI->available_drivers) {
   ($_ eq 'mysql') && ($driver = 1) && last;
}
if (!$driver) { fail("The MySQL DBD driver is not installed"); }

$dsn = "DBI:mysql:database=$database";
($dbh = DBI->connect($dsn, $user, $password)) || fail("Failed to connect as $user");
($rdbh = DBI->connect($dsn, $root, $rootpw)) || fail("Failed to connect as $root");

my @tables = ('article', 'blobs', 'cache', 'element', 'event', 'eventmember', 'file', 'grp',
	            'host', 'image', 'member', 'page', 'pageelement', 'person', 'preference',
	            'record_extension', 'repligard', 'sitegroup', 'snippet', 'snippetdir', 'style', 'topic' );
if (tableexists('pagelink')) {
   push @tables, 'pagelink';
} else {
   print "No pagelink table, do not enable pagelink support\n";
}
############ UPGRADE ##############################

test125articles();

createblobs();
createcache();
createparams();
createrepligard();
createsnippet();
createsnippetdir();
createevent();
createeventmember();

# sitegroups must go last
createsitegroups();

testexpectedtables();

checkguids();

exit;

############## BLOBS ############################

sub createcache
{
   $opt_t || print "\n>> CACHE\n";
   if (tableexists('cache')) {
      $opt_t || print "   Cache table present\n";
      return;
   }
   elsif ($opt_t) {
      fail("Cache table missing", 2);
   }

   $result = $rdbh->do("
      CREATE TABLE cache (
         id int DEFAULT '0' NOT NULL,
         type int DEFAULT '0' NOT NULL,
         page int DEFAULT '0' NOT NULL,
         sitegroup int DEFAULT '0' NOT NULL,
         PRIMARY KEY (page,type,id)
      )
   ");

   if ($result) {
      print "   ++ Cache table created\n";
   }
   else {
      fail("Failed to create cache table");
   }
}
sub createblobs
{
   $opt_t || print "\n>> BLOBS\n";
   if (tableexists('blobs')) {
      $opt_t || print "   Blobs table present\n";
      return;
   }
   elsif ($opt_t) {
      fail("Blobs table missing", 2);
   }

   $result = $rdbh->do("
      CREATE TABLE blobs (
         id int DEFAULT '0' NOT NULL auto_increment,
         ptable varchar(255) DEFAULT '' NOT NULL,
         pid int DEFAULT '0' NOT NULL,

         score int DEFAULT '0' NOT NULL,
         name varchar(255) DEFAULT '' NOT NULL,
         title varchar(255) DEFAULT '' NOT NULL,
         location varchar(255) DEFAULT '' NOT NULL,
         mimetype varchar(255) DEFAULT '' NOT NULL,
         sitegroup int DEFAULT '0' NOT NULL,
         author int DEFAULT '0' NOT NULL,
         created datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
         PRIMARY KEY (id)
      )
   ");

   if ($result) {
      print "   ++ Blobs table created\n";
   }
   else {
      fail("Failed to create blobs table");
   }
}

############# PARAMS #################################

sub createparams
{
my $result;

   $opt_t || print "\n>> PARAMETERS\n";
   if (tableexists('record_extension')) {
      $opt_t || print "   Parameters table present\n";
      return;
   }
   elsif ($opt_t) {
      fail("Parameter table missing", 2);
   }

   $result = $rdbh->do("
      CREATE TABLE record_extension (
      id int DEFAULT '0' NOT NULL auto_increment,
      tablename varchar(255) DEFAULT '' NOT NULL,
      oid int DEFAULT '0' NOT NULL,
      domain varchar(255) DEFAULT '' NOT NULL,
      name varchar(255) DEFAULT '' NOT NULL,
      value varchar(255) DEFAULT '' NOT NULL,
      sitegroup int DEFAULT '0' NOT NULL,
      PRIMARY KEY (id)
      )
   ");

   if ($result) {
      print "   ++ Params table created\n";
   }
   else {
      fail("Failed to create params table");
   }
}

################ INSTALL SITEGROUPS ##################
sub createsitegroups
{
my $table;
my $sth;
my $sg;
my @names;
my $result;

   $opt_t || print "\n>> SITEGROUPS\n";

   if (tableexists('sitegroup')) {
      $opt_t || print "   Sitegroup table present\n";
      if (columnexists('sitegroup', 'realm')) {
         $opt_t || print "   and it has the realm field\n";
      } else {
         if ($opt_t) { fail ("Sitegroups table is missing the realm field", 2); }

         $result = $rdbh->do("
            ALTER TABLE sitegroup ADD COLUMN
            realm varchar(255) DEFAULT '' NOT NULL
         ");
         if ($result) {
            print "      realm field added to sitegroup\n";
         }
         else {
            print "      !! Failed to add realm field to sitegroup\n";
         }
      }
   }
   else {
      if ($opt_t) { fail ("Sitegroup table missing", 2); }

      $result = $rdbh->do("
         CREATE TABLE sitegroup (
            id int  DEFAULT '0' NOT NULL auto_increment,
            name varchar(255) DEFAULT '' NOT NULL,
            realm varchar(255) DEFAULT '' NOT NULL,
            admingroup int  DEFAULT '0' NOT NULL,
            PRIMARY KEY (id)
            )
         ");

      if ($result) {
         print "   ++ Sitegroup table created\n";
      }
      else {
         fail("Failed to create sitegroup table");
      }
   }

   foreach $table (@tables) {
      if ($table eq 'sitegroup') { next; }

      $sg = columnexists($table, 'sitegroup');

      if ($sg) {
         $opt_t || print "      $table has the sitegroup field\n";
      }
      else {
         if ($opt_t) { fail ("Table $table is missing the sitegroup field", 2); }

         $result = $rdbh->do("
            ALTER TABLE $table ADD COLUMN
            sitegroup int  DEFAULT '0' NOT NULL
         ");
         if ($result) {
            print "      sitegroup field added to $table\n";
         }
         else {
            print "      !! Failed to add sitegroup field to $table\n";
         }
      }
   }
}

sub createrepligard
{
   $opt_t || print "\n>> REPLIGARD\n";
   if (tableexists('repligard')) {
      $opt_t || print "   Repligard table present\n";
      return;
   }
   elsif ($opt_t) {
      fail ("Repligard table missing", 2);
   }

   $result = $rdbh->do("
CREATE TABLE repligard (
  id int(11) DEFAULT '0' NOT NULL,
  changed timestamp(14),
  updated timestamp(14),
  realm varchar(32) DEFAULT '' NOT NULL,
  guid varchar(32) DEFAULT '' NOT NULL,
  action enum('create','update','delete') DEFAULT 'create' NOT NULL,
  sitegroup int(11) DEFAULT '0' NOT NULL,
  PRIMARY KEY (guid),
  KEY realm (realm,id)
)
");
   if ($result) {
      print "   ++ Repligard table created\n";
   }
   else {
      fail("Failed to create repligard table");
   }
}

sub createsnippet
{
   $opt_t || print "\n>> SNIPPET\n";
   if (tableexists('snippet')) {
      $opt_t || print "   Snippet table present\n";
      return;
   }
   elsif ($opt_t) {
      fail ("Snippet table missing", 2);
   }

   $result = $rdbh->do("
CREATE TABLE snippet (
  id int DEFAULT '0' NOT NULL auto_increment,
  up int DEFAULT '0' NOT NULL,
  name varchar(255) DEFAULT '' NOT NULL,
  code text NOT NULL,
  doc text NOT NULL,
  author text NOT NULL,
  creator int(10) unsigned DEFAULT '0' NOT NULL,
  created datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  revisor int(10) unsigned DEFAULT '0' NOT NULL,
  revised datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  revision int(10) unsigned DEFAULT '0' NOT NULL,
  PRIMARY KEY (id),
  KEY up (up,name(14))
)
");
   if ($result) {
      print "   ++ Snippet table created\n";
   }
   else {
      fail("Failed to create snippet table");
   }
}

sub createsnippetdir
{
   $opt_t || print "\n>> SNIPPETDIR\n";
   if (tableexists('snippetdir')) {
      $opt_t || print "   SnippetDir table present\n";
      return;
   }
   elsif ($opt_t) {
      fail ("Snippet table missing", 2);
   }

   $result = $rdbh->do("
CREATE TABLE snippetdir (
  id int DEFAULT '0' NOT NULL auto_increment,
  up int DEFAULT '0' NOT NULL,
  name varchar(255) DEFAULT '' NOT NULL,
  description text NOT NULL,
  owner int DEFAULT '0' NOT NULL,
  PRIMARY KEY (id),
  KEY up (up,name(14))
)
");
   if ($result) {
      print "   ++ SnippetDir table created\n";
   }
   else {
      fail("Failed to create SnippetDir table");
   }
}

sub createevent
{
   $opt_t || print "\n>> EVENT\n";
   if (tableexists('event')) {
      $opt_t || print "   Event table present\n";
      return;
   }
   elsif ($opt_t) {
      fail ("Event table missing", 2);
   }

   $result = $rdbh->do("
CREATE TABLE event (
  id int(11) unsigned DEFAULT '0' NOT NULL auto_increment,
  up int(11) unsigned DEFAULT '0' NOT NULL,
  start int(11) DEFAULT '0' NOT NULL,
  end int(11) DEFAULT '2147483647',
  title varchar(255) NOT NULL,
  description text NOT NULL,
  type int(10) unsigned DEFAULT '0' NOT NULL,
  extra text NOT NULL,
  busy int(1) unsigned DEFAULT '0' NOT NULL,
  owner int(11) unsigned DEFAULT '0' NOT NULL,
  creator int(11) unsigned DEFAULT '0' NOT NULL,
  created datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  locker int(11) unsigned DEFAULT '0' NOT NULL,
  locked datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  revisor int(11) unsigned DEFAULT '0' NOT NULL,
  revised datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  revision int(11) unsigned DEFAULT '0' NOT NULL,
  PRIMARY KEY (id),
  KEY up (up),
  KEY start (start),
  KEY type (type)
)
");
   if ($result) {
      print "   ++ Event table created\n";
   }
   else {
      fail("Failed to create event table");
   }
}

sub createeventmember
{
   $opt_t || print "\n>> EVENT MEMBER\n";
   if (tableexists('eventmember')) {
      $opt_t || print "   EventMember table present\n";
      return;
   }
   elsif ($opt_t) {
      fail ("Event table missing", 2);
   }

   $result = $rdbh->do("
CREATE TABLE eventmember (
  id int(11) DEFAULT '0' NOT NULL auto_increment,
  eid int(11) DEFAULT '0' NOT NULL,
  uid int(11) DEFAULT '0' NOT NULL,
  count int(11) DEFAULT '0' NOT NULL,
  period int(11) DEFAULT '0' NOT NULL,
  extra text NOT NULL,
  PRIMARY KEY (id),
  KEY eid (eid),
  KEY uid (uid)
)
");
   if ($result) {
      print "   ++ EventMember table created\n";
   }
   else {
      fail("Failed to create EventMember table");
   }
}

sub checkguids
{
my $table;
my $sth;
my $rows;
my $failures = 0;
my $n;

   $opt_t || print "\n>> GUIDS\n";
   foreach $table (@tables) {
      if ($table eq 'repligard') { next; }
      $opt_t || print "   ++ Testing '$table'...\n";

      if (!columnexists($table, 'id')) {
         print "   !! Table '$table' is missing the 'id' field.\n";
         next;
      }

      $sth = $dbh->prepare("
         SELECT $table.id FROM $table
         LEFT JOIN repligard ON $table.id = repligard.id
         WHERE repligard.id is NULL
         ");
      if (!$sth) { 
         print "   !! Failed select from '$table'\n";
         next;
      }
      if (!$sth->execute()) {
         print "   !! Failed execute for '$table'\n";
         next;
      }
      if (($rows = $sth->rows()) != 0) {
         $n = $rows > 1 ? "ies" : "y";
         print "   !! '$table' has $rows entr$n without a repligard GUID\n";
         $failures += $rows;
      }
   }

   if ($failures) {
      $n = $failures > 1 ? "are $failures GUIDS" : "is 1 GUID";
      fail("There $n missing! You will need to run 'repligard -m'.");
   }
}

sub test125articles
{
my $sth = $dbh->prepare("SELECT id FROM article WHERE name=''");
my $rows;

   ($sth && $sth->execute())
      || fail("Testing for nameless articles: Could not select");

   $rows = $sth->rows();

   $rows && print("Testing for nameless articles: $rows found. Non-fatal but not recommended\n");

   $sth = $dbh->prepare("SELECT DISTINCT a1.id FROM article a1, article a2 WHERE a1.name<>'' AND a1.name=a2.name and a1.id<>a2.id AND a1.topic=a2.topic AND a1.up=0 AND a2.up=0");

   ($sth && $sth->execute())
      || fail("Testing for duplicate article names: Could not select");

   $rows = $sth->rows();

   $rows && fail("Testing for duplicate article names: $rows found. Run article-uniquename to fix it.");

   $sth = $dbh->prepare("SELECT DISTINCT a1.id FROM article a1, article a2 WHERE a1.name<>'' AND a1.name=a2.name and a1.id<>a2.id AND a1.up=a2.up");

   ($sth && $sth->execute())
      || fail("Testing for duplicate reply article names: Could not select");

   $rows = $sth->rows();

   $rows && fail("Testing for duplicate reply article names: $rows found. Run article-uniquename to fix it.");
}

sub testexpectedtables
{

my %state = map { $_ => 1 } @tables;

   $opt_t || print "\n>> TABLES\n";

   foreach ($dbh->tables) {
      if ($state{$_}) {
         $state{$_} = 0;
      } else {
         print "   !! '$_' table present but not expected\n";
      }
   }

   foreach (@tables) {
      if ($state{$_}) {
         print "   !! '$_' table expected but not present\n";
      }
   }
}

################ UTIL ################################

sub tableexists
{
my $table = shift;

   foreach ($dbh->tables) {
      if ($_ eq $table) {
         return 1;
      }
   }

   return 0;
}

sub columnexists
{
my $table = shift;
my $column = shift;
my $colname;

my $sth = $rdbh->prepare("describe $table");
   ($sth && $sth->execute()) || return 0;

   while (($colname) = $sth->fetchrow_array) {
      if ($colname eq $column) { return 1; }
   }

   return 0;
}

sub fail
{
   print STDERR $_[0], "\n";
   exit($_[1] ? $_[1] : 1);
}
