#!/usr/bin/python

import paths
from Mailman import Archiver

import os
import sys
import marshal
import re
import time

try:
    import cPickle
    pickle = cPickle
except ImportError:
    import pickle

# MySQL ...
#print 'drop database if exists ezmlmremap;'
#print 'create database ezmlmremap;'
#print 'use ezmlmremap;'
#print 'create table list_mapping (id int unsigned not null primary key, name varchar(32) not null) character set utf8;'
#print 'create table message_mapping (ezmlmlist int unsigned not null, ezmlmmsg int unsigned not null, month tinyint unsigned not null, year int unsigned not null, mailmanmsg int unsigned not null, index (ezmlmlist, ezmlmmsg)) character set utf8;'

# SQLite...
print 'create table list_mapping (id int unsigned not null primary key, name varchar(32) not null);'
print 'create table message_mapping (ezmlmlist int unsigned not null, ezmlmmsg int unsigned not null, month tinyint unsigned not null, year int unsigned not null, mailmanmsg int unsigned not null);'
print 'begin transaction;'

for listname in sys.argv[1:]:
    listnum = -1

    # !!! FIXME: should just read this once into a dictionary, instead
    # !!! FIXME:  of every time through the loop, but oh well.
    fp = open('/etc/ezmlm/ezcgirc')
    for line in fp.readlines():
        listcfg = line.split(';')
        if len(listcfg) > 2:
            if listcfg[2] == '/var/qmail/alias/' + listname:
                listnum = listcfg[0]
                break
    fp.close()

    if listnum == -1:
        print "can't find listnum for " + listname
        sys.exit(0)

    print "insert into list_mapping (id, name) values (%d, '%s');" % (int(listnum), listname)

    p = re.compile('^.*\-article$')
    mailmandict = {}
    mailmandate = {}

    path = '/usr/local/mailman/archives/private/'  + listname + '/database'

    for filename in os.listdir(path):
        if not p.match(filename):
            continue
        dbpath = path + '/' + filename
        fp = open(dbpath, 'rb')
        dict = marshal.load(fp)
        fp.close()

        for key in dict:
            obj = pickle.loads(dict[key])
            mailmandict[key] = int(obj.sequence)
            mailmandate[key] = obj.date

    # Okay, now we have all the hopefully-unique msgids, and how they map to
    #  indexes in the archives.
    #for key in mailmandict:
    #    print 'MAILMAN:', mailmandict[key], key


    # okay, now we need to read through all the ezmlm message archives to find
    #  the msgid. This is inefficient, but it's the best way to match messages,
    #  and the only way to find it in the ezmlm archives.

    ezmlmdict = {}
    p = re.compile('^\d+$')
    msgidp = re.compile('^Message-Id:\s+<(.*)>$', re.IGNORECASE)
    base = '/var/qmail/alias/' + listname + '/archive'
    for f in os.listdir(base):
        if p.match(f):
            base2 = base + '/' + f
            for f2 in os.listdir(base2):
                if p.match(f2):
                    base3 = base2 + '/' + f2
                    fp = open(base3)
                    found = False
                    for line in fp.readlines():
                        msgid = msgidp.match(line)
                        if msgid != None:
                            ezmlmdict[msgid.group(1)] = int(f + f2)
                            found = True
                            break
                    fp.close()
                    if not found:
                        print '--ERROR: no message-id for ' + base3

    #for key in ezmlmdict:
    #    print 'EZMLM:', ezmlmdict[key], key

    # Okay, now we have ezmlm ids, mailman ids, and the msgid that a given pair uses.

    for key in ezmlmdict:
        if key not in mailmandict:
            print "--ERROR: mailman doesn't have ezmlm message %d" % int(ezmlmdict[key])
        else:
            gmt = time.gmtime(float(mailmandate[key]))
            month = gmt.tm_mon-1
	    year = gmt.tm_year
            print 'insert into message_mapping (ezmlmlist, ezmlmmsg, mailmanmsg, month, year) values (%d, %d, %d, %d, %d);' % (int(listnum), int(ezmlmdict[key]), int(mailmandict[key]), int(month), int(year))
    
print 'end transaction;'