#!/usr/bin/perl

#####################################################################
# 
# ÇÁ·Î±×·¥ ±â´É : mail ¹­À½ÀÎ mbox ¸¦ jsboard Çü½ÄÀ¸·Î ¿Å±è.
#
# ¸¸µç ¸ñÀû : Mailing List archive ¸¦ jsboard ·Î °Ë»ö, º¸±âÀ§ÇØ¼­
# ¸¸µì´Ï´Ù.
#
# »ç¿ëÇÏ±â À§ÇØ ¼öÁ¤ÇØ¾ß ÇÏ´Â ºÎºÐ : ¾Æ·¡ÀÇ ¼³Á¤ ºÎºÐ¸¸ °íÃÄ
# ÁÖ½Ã¸é µË´Ï´Ù.
#
# »ç¿ë¹ý : ./mail2jsboard.pl ¸ÞÀÏÆÄÀÏÀÌ¸§_mbox
#
# 2003 ³â 05 ¿ù 12ÀÏ Á¤¿ø±³(weongyo@hotmail.com)°¡ ¸¸µì´Ï´Ù.
#
# ¾î¶² ¹ö±×³ª ÀÇ°ßÀº Àú¿¡°Ô º¸³»ÁÖ½Ê½Ã¿À.
#
# ¶óÀÌ¼¾½º´Â GPL ÀÔ´Ï´Ù.
#

use strict;
use Mail::MboxParser;
use Mysql;
use POSIX;

####################################################################
# CONFIGURATION : ¼³Á¤À» ÇÕ½Ã´Ù.

# MySQL Á¢¼Ó
my $DB_host     = "localhost";
my $DB_name     = "MY_DB_Name";
my $DB_user     = "MY_USER_ID";
my $DB_pass     = "MY_PASSWORD";

# MySQL ¼ÓÀÇ »ðÀÔÇÒ JSBoard Å×ÀÌºí ÀÌ¸§
my $JS_tablename        = "MY_DB_TABLE_NAME";

#####################################################################

my $db = Mysql->connect ($DB_host, $DB_name, $DB_user, $DB_pass);

my $count = 1;
my %atc;

my $mailfilename = $ARGV[0];
if ($mailfilename eq "") {
    usage ();
}

print "\nÃ³¸®¸¦ ¿øÇÏ´Â ÆÄÀÏ : $mailfilename\n\n";
print "ÀÌ ÆÄÀÏÀÌ È®½ÇÇÕ´Ï±î? [y/N] ";
my $is_ok = <STDIN>;
chomp ($is_ok);
if ($is_ok eq "" || $is_ok eq "n" || $is_ok eq "N") {
    exit;
}

print "-- Ã³¸®¸¦ ½ÃÀÛÇÕ´Ï´Ù.\n";

my $mb = Mail::MboxParser->new("./$mailfilename", decode=> 'ALL');

print "-- ÃÑ " . $mb->nmsgs . " °³ÀÇ ¸ÞÀÏÀ» Ã³¸®ÁßÀÔ´Ï´Ù.\n\n";
while (my $msg = $mb->next_message) {
    print STDERR (sprintf ("%04d", $count) . " ");
    #print "-- Header ------------------------------------------------\n";
    #print "Á¦¸ñ   : " . $msg->header->{'subject'}, "\n";
    #print "º¸³½ÀÌ : " . $msg->header->{'from'}, "\n";
    #print "¹Þ´ÂÀÌ : " . $msg->header->{'to'}, "\n";
    #print "³¯Â¥   : " . $msg->header->{'date'}, "\n";
    #get_timestamp ($msg->header->{'date'});
    #print "ID     : " . $msg->header->{'message-id'}, "\n";
    #print "-- Body --------------------------------------------------\n";
    #print $msg->body;
    clear_atc ();
    pre_message ($msg);
    if (length ($msg->header->{'in-reply-to'}) > 5 or length ($msg->header->{'references'}) > 5) {
        article_reply ($JS_tablename, $msg);
    } else {
        article_post ($JS_tablename, $msg);
    }
    if (!($count % 16)) {
        print "\n";
    }
    $count++;
}

# °Ô½Ã¹° ÀÛ¼º ÇÔ¼ö
sub article_post {
    my ($table, $msg) = @_;

    my $result = sql_query ("SELECT MAX(num) AS num, MAX(idx) AS idx FROM $table");
    my %hash   = $result->fetchhash;
    $atc{'mxnum'} = $hash{'num'} + 1;
    $atc{'mxidx'} = $hash{'idx'} + 1;

    if ($atc{'date'} eq 0 or $atc{'date'} eq "") {
        print "¿Ö date °¡ NULL ÀÎ°¡?\n".$atc{'host'}."\n".$atc{'title'}."\n".$atc{'name'}."\n ";
        exit;
    } elsif ($atc{'date'} eq -1) {
        print "³¯Â¥°¡ ÀÌ»óÇØ¼­ skip : " . $msg->header->{'date'} . "\n";
        return;
    }
    # ¾÷·Îµå ºÎºÐÀº ¹«½ÃµÇ¾ú½À´Ï´Ù.
    $result = sql_query ("INSERT INTO $table (no,num,idx,date,host,name,rname,passwd,email,url,
                                              title,text,refer,reyn,reno,rede,reto,html,bofile,
                                              bcfile,bfsize)
                          VALUES ('','".$atc{'mxnum'}."','".$atc{'mxidx'}."',".$atc{'date'}.",'".$atc{'host'}."',
                                  '".$atc{'name'}."','".$atc{'rname'}."','".$atc{'passwd'}."','".$atc{'email'}."',
                                 '".$atc{'url'}."','".$atc{'title'}."','".$atc{'text'}."',0,0,0,0,0,'".$atc{'html'}."',
                                  '','','')");

}

# °Ô½Ã¹° ´äÀå ÇÔ¼ö
sub article_reply {
    my ($table, $msg) = @_;

    my (%reply, $msginreplyto, $result);

    $msginreplyto = $msg->header->{'in-reply-to'};
    $msginreplyto =~ m/.*<(.*)>/;
    $msginreplyto = $1;
    if ($msginreplyto eq "") {
        $msginreplyto = $msg->header->{'references'};
        $msginreplyto =~ m/.*<(.*)>/;
        $msginreplyto = $1;
    }
    %reply = get_article($table, $msginreplyto);
    if ($reply{'no'} eq "") {
        return;
    }
    $atc{'rede'} = $reply{'rede'} + 1;
    $atc{'idx'}  = $reply{'idx'};
    $atc{'reno'} = $reply{'no'};

    if($reply{'reto'}) {
        $atc{'reto'} = $reply{'reto'};
    } else {
        $atc{'reto'} = $reply{'no'};
    }

    $result = sql_query ("UPDATE $table SET idx = idx + 1 WHERE (idx + 0) >= '".$atc{'idx'}."'");
    $result = sql_query ("UPDATE $table SET reyn = 1 WHERE no = '".$atc{'reno'}."'");
    $result = sql_query ("INSERT INTO $table (no,num,idx,date,host,name,rname,passwd,email,url,
                                             title,text,refer,reyn,reno,rede,reto,html,bofile,
                                             bcfile,bfsize)
                                VALUES ('',0,'".$atc{'idx'}."','".$atc{'date'}."','".$atc{'host'}."','".$atc{'name'}."','".$atc{'rname'}."',
                                        '".$atc{'passwd'}."','".$atc{'email'}."','".$atc{'url'}."','".$atc{'title'}."','".$atc{'text'}."',
                                        0,0,'".$atc{'reno'}."','".$atc{'rede'}."','".$atc{'reto'}."','".$atc{'html'}."','',
                                        '','')");

}

sub clear_atc {
    $atc{'mxnum'} = 0;
    $atc{'mxidx'} = 0;
    $atc{'idx'} = 0;
    $atc{'date'} = 0;
    $atc{'host'} = 0;
    $atc{'name'} = 0;
    $atc{'rname'} = 0;
    $atc{'passwd'} = 0;
    $atc{'email'} = 0;
    $atc{'url'} = 0;
    $atc{'title'} = 0;
    $atc{'text'} = 0;
    $atc{'reno'} = 0;
    $atc{'rede'} = 0;
    $atc{'reto'} = 0;
    $atc{'html'} = 0;
}
sub pre_message {
    my ($msg) = @_;
    my ($msgid, $tmp_fst, $tmp_snd);

    $msgid = $msg->header->{'message-id'};
    $msgid =~ s/[<>]//g;

    $atc{'date'} = get_timestamp ($msg->header->{'date'});
    $atc{'host'} = "127.0.0.1";

    $msg->header->{'from'} =~ m/(.*) [<\(](.*)[>\)]/;
    $tmp_fst = $1;
    $tmp_snd = $2;
    $tmp_fst =~ s/\"//g;
    $tmp_snd =~ s/\"//g;
    $tmp_fst =~ s/\\//g;
    $tmp_snd =~ s/\\//g;
    $tmp_fst =~ s/'/\\'/g;
    $tmp_snd =~ s/'/\\'/g;
    if ($tmp_fst eq "") {
        if ($msg->header->{'from'} =~ /@/) {
            $tmp_fst = $msg->header->{'from'};
        }
    }
    if ($tmp_fst =~ /@/) {
        $atc{'email'} = $tmp_fst;
        if ($tmp_snd eq "") {
            $tmp_fst =~ m/(.*)@/;
            $atc{'name'} = $1;
        } else {
            $atc{'name'} = $tmp_snd;
        }
    } elsif ($tmp_snd =~ /@/) {
        $atc{'name'} = $tmp_fst;
        $atc{'email'} = $tmp_snd;
    } else {
        # E ¸ÞÀÏ ÁÖ¼Ò°¡ ÀÌ»óÇÑ °ÍÀº ±×³É ¹«½ÃÇÑ´Ù.
        return;
    }
    $atc{'name'} =~ m/\s*(.*)\s*$/;
    $atc{'name'} = $1;
    $atc{'passwd'} = "\$1\$FUarzc/a\$OZ08lzcVq9FRFeg0pRi6F/";
    $atc{'title'} = $msg->header->{'subject'};
    $atc{'title'} =~ s/'/\\'/g;
    $atc{'text'} = $msg->body;
    $atc{'text'} =~ s/\\/\\\\/g;
    $atc{'text'} =~ s/'/\\'/g;

    if ($atc{'title'} eq "") {
        $atc{'title'} = "No subject";
    }

    # Message ID ¸¦ ÀÓ½Ã·Î $atc{'rname'} ¿¡ ³Ö¾î ³õ½À´Ï´Ù.
    $atc{'rname'} = $msgid;

    print $atc{'title'} . "\n";
}

sub get_article {
    my ($table, $inreplyto) = @_;
    my ($result, %hash);

    $result = sql_query ("SELECT MAX(no) AS no FROM $table");
    my %hash   = $result->fetchhash;
    my $maxnum = $hash{'no'};
    my $minnum = $hash{'no'} - 3000;
    $result = sql_query("SELECT * FROM $table WHERE rname = '$inreplyto' AND no <= $maxnum AND no >= $minnum ORDER by no DESC");
    %hash = $result->fetchhash;
    return %hash;
}

# Mon, 1 Sep 1997 10:27:38 +0200 ¿Í °°Àº ÇüÅÂ¿¡¼­ timestamp ¸¦
# ¾ò´Â´Ù.
sub get_timestamp {
    my ($time_str) = @_;
    my (@days, @months, $sday, $month);
    my ($i, $timestamp);
    my $diff = 1900;

    @days       = ("Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat");
    @months     = ("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec");
    $time_str =~ m/(\w\w\w)?,?\s?(\d?\d?) (\w\w\w) (\d\d\d?\d?) (\d?\d?):?(\d?\d?):?(\d?\d?)?/;

    for ($i=0; $i <= $#days; $i++) {
        if ($days[$i] eq $1) {
            $sday = $i;
            last;
        }
    }
    for ($i=0; $i <= $#months; $i++) {
        if ($months[$i] eq $3) {
            $month = $i;
            last;
        }
    }

    if ($4 < 1900) {
        # 2000, 2001, 2002, 2003 ³âÀÇ °æ¿ì 00, 01, 02, 03 °ú °°ÀÌ ³ªÅ¸³»´Â ³ª»Û
        # mailer °¡ ÀÖ½À´Ï´Ù. ÀÌ°ÍÀº 100, 101, 102, 103 °ú °°ÀÌ Ã³¸®ÇØ Áà¾ß ÇÕ´Ï´Ù.
        if (length ($4) eq 2) {
            if ($4 < 70) {
                $diff = -100;
            } else {
                $diff = 0;
            }
        }
    }
    #print $time_str . "\n";
    #print "$7, $6, $5, $2, $month, $4-$diff, $sday, 0, 0\n";
    if ($4 > 2003) {
        #print "³âµµ°¡ ÀÌ»óÇÒ ¼ö ÀÖ½À´Ï´Ù. È®ÀÎ¹Ù¶ø´Ï´Ù. ÀÌ ¸Þ¼¼Áö´Â skip ÇÕ´Ï´Ù.\n";
        #$diff = <STDIN>;
        return -1;
    }
    $timestamp = mktime ($7, $6, $5, $2, $month, $4-$diff, $sday, 0, 0);
    if ($timestamp eq 0 or $timestamp eq "") {
        print "³¯Â¥ Æ÷¸ËÀÌ Áö¿øÇÏÁö ¾Ê´Â Æ÷¸ËÀÔ´Ï´Ù.  Àß¸øµÈ ÆíÁöÀÏ °¡´É¼ºÀÌ\n";
        print "¸¹½À´Ï´Ù.  ÀÌ ÆíÁö¸¦ skip ÇÕ´Ï´Ù.\n";
        print "-- ÆíÁö Á¤º¸ ------------------------------------------------\n";
        print $time_str . "\n";
        print "$7, $6, $5, $2, $month, $4-$diff, $sday, 0, 0\n";
        print "-------------------------------------------------------------\n";
        return -1;
    }
    return $timestamp;
}

# SQL Query ¸¦ Ã³¸®ÇÑ´Ù.
sub sql_query {
    my ($query) = @_;

    my $result = $db->query ($query);
    if (!$result) {
        print "DB query ½ÇÆÐ : $query\n";
        exit;
    }

    return $result;
}

sub usage {
    print "»ç¿ë¹ý : mailt2jsboard.pl ¸ÞÀÏÆÄÀÏ_mbox\n";
    exit;
}
