#!/usr/bin/perl

## $Id: obec.unlink.pl,v 1.5 2001/05/27 08:28:28 lord Exp $
##-----------------------------------------------------
## Utility to unlink comments threads.
##
## http://obec.sourceforge.net/
## Vadim Zaliva <lord@crocodile.org>
##-----------------------------------------------------

use ConfigReader::Simple  ;
use DBI                   ;
use Mail::Header          ;
use Date::Parse           ;
use POSIX qw(strftime)    ;

# ---------------- Constants --------------------------

$DBCHARSET = 'KOI8';

# ---------------------- Main -------------------------

# Load CFG.
$config    = load_cfg("obec.fetcher.cfg");

# Connect to DB
($dbh, $drh) = db_connect($config);

unlink_threads();

# Disconnect from the database.
db_disconnect();

print "Done.\n";

exit(0);

# ---------------------- Subroutines -------------------------

sub trim($)
  {
    my @out = @_;
    for(@out) {
      s/^\s+//;
      s/\s+$//;
    }
    return wantarray ? @out:$out[0];
  }
  
sub load_cfg($)
  {
    my $config = ConfigReader::Simple->new($_[0], [qw(Server DSN DBUser DBPassword ArticlesGroup)]);
    $config->parse();
    $config->_validate_keys();  
    return $config;
  };
             
           
sub db_disconnect()
  {
    $dbh->disconnect();
  }

sub db_connect($)
{
  my($config)=@_;

  my $dsn         = $config->get("DSN");
  my $db_user     = $config->get("DBUser");
  my $db_password = $config->get("DBPassword");
  
  print "Connecting to: $dsn as $db_user\n";
  $dbh = DBI->connect($dsn, $db_user, $db_password);
  $drh = DBI->install_driver("mysql");

  $start_thread                = $dbh->prepare("INSERT INTO Threads (CreatedAt, Title) VALUES (?, ?)");

  $insert_unsolved_msgid       = $dbh->prepare("INSERT INTO UnsolvedMsgIdReferences (ThreadId, MsgId) VALUES (?,?)");
  
  $insert_unsolved_ftnmsgid    = $dbh->prepare("INSERT INTO UnsolvedFTNMsgIdReferences (ThreadId, FTNMsgId) VALUES (?,?)");

  $traverse_comments           = $dbh->prepare("SELECT Comments.Id, Header FROM Comments, EmailMessages WHERE Comments.EmailId=EmailMessages.Id");

  $traverse_texts              = $dbh->prepare("SELECT Texts.Id, Header FROM Texts, EmailMessages WHERE Texts.EmailId=EmailMessages.Id");

  $set_thread                  = $dbh->prepare("UPDATE Comments SET ThreadId=? WHERE Id=?");

  $set_text_date               = $dbh->prepare("UPDATE Texts    SET PublishedAt=? WHERE Id=?");
  $set_comment_date            = $dbh->prepare("UPDATE Comments SET PublishedAt=? WHERE Id=?");

  return ($dbh, $drh);
}

sub start_new_thread
{
  my($title, $when) = @_;
  
  $start_thread->execute($when, $title);
  my $res = $start_thread->{"mysql_insertid"};
  $start_thread->finish();
  return $res;
}

# Parse RFC822 'References' field 
# and returns all found message ids.
sub parse_references($)
{
  my $str = $_[0] ;
  my $i   = 0     ;
  my @res         ; 
  
  $_=$str;
  while(/(\<[^\s]+\@[^\s]+\>)/g)
    {
      @res[$i++]=$1;
    }
  return @res;
}

sub unlink_threads()
{
  $dbh->do("UPDATE Comments SET ThreadId=NULL, ThreadOffset=0, ThreadPosition=0");
  $dbh->do("UPDATE Texts    SET CommentThreadId=NULL");
  $dbh->do("DELETE FROM UnsolvedMsgIdReferences");
  $dbh->do("DELETE FROM UnsolvedFTNMsgIdReferences");
  $dbh->do("DELETE FROM Threads");

  $traverse_texts->execute();
  while(my @res=$traverse_texts->fetchrow_array())
    {
      my($id, $shead) = @res;
      my @lines = split(/[\r\n]+/,$shead);
      my $hdr = new Mail::Header;
      $hdr->header(\@lines);
      $hdr->unfold;
      my $when        = parse_msg_date(trim($hdr->get("Date")));
      $set_text_date->execute($when, $id);
      $set_text_date->finish();
    }

  $traverse_texts->finish();

  $traverse_comments->execute();
  while(my @res=$traverse_comments->fetchrow_array())
    {
      my($id, $shead)=@res;
      my @lines = split(/[\r\n]+/,$shead);
      my $hdr = new Mail::Header;
      $hdr->header(\@lines);
      $hdr->unfold;
      
      my $subject     = trim($hdr->get("Subject", 0));
      my $ftnreply    = trim($hdr->get("X-FTN-REPLY"));
      my $references  = trim($hdr->get("References"));

      my $when        = parse_msg_date(trim($hdr->get("Date")));
      $set_comment_date->execute($when, $id);
      $set_comment_date->finish();
      
      my $thread_id = start_new_thread($subject, $when);

      $set_thread->execute($thread_id, $id);
      $set_thread->finish();

      if($ftnreply)
        {
          $insert_unsolved_ftnmsgid->execute($thread_id, $ftnreply);
          $insert_unsolved_ftnmsgid->finish();
        }
      
      if($references)
        {
          my @refs = parse_references($references);
          foreach my $ref (@refs)
            {
              $insert_unsolved_msgid->execute($thread_id, $ref);
              $insert_unsolved_msgid->finish();
            }
        }
      
      #print "$id\t'$subject'\n"; 
    }
  $traverse_comments->finish();
}

sub parse_msg_date
  {
    my $str = $_[0] ;
    $time = str2time($str);
    if(!$time)
      {
        # parsing failed. Assume NOW.
        print "Parsing of '$str' failed. Using current time instead.\n";
        $time = time();
      }

    return strftime "%Y-%m-%d %H:%M:%S", gmtime($time);
  }
