# -*- coding: utf-8 -*-

import re

from trac.core import *
from trac.db import Table, Column, Index, DatabaseManager
from trac.env import IEnvironmentSetupParticipant
from trac.util.translation import _

class MailArchiveSetup(Component):
    implements(IEnvironmentSetupParticipant)
    
    # IEnvironmentSetupParticipant methods
    def environment_created(self):
        self.found_db_version = 0
        self.upgrade_environment(self.env.get_db_cnx())

    def environment_needs_upgrade(self, db):
        # check for our custom fields
        if 'mail_id' not in self.config['ticket-custom']:
            return True
        
        if self.is_no_table(db):
            return True
        
        # old db schema?
        if self.is_old_schema(db):
            self.log.info("mailarc table is old schema. please update your environment.")
            return True
        return False

    def upgrade_environment(self, db):
        # create custom field
        custom = self.config['ticket-custom']
        if 'mail_id' not in custom:
            custom.set('mail_id', 'text')
            custom.set('mail_id.label', 'Mail ID')
            self.config.save()
            
        db = self.env.get_db_cnx()
        
        # create database tables
        if self.is_no_table(db):
            self.create_db(db)
        
        db.commit()
            
        if self.is_old_schema(db):
            self.change_schema(db)
            
        self.update_thread_root(db)
            
        db.commit()

    def create_db(self, db):
        mailarc_table = Table('mailarc', key=('id'))[
            Column('id', auto_increment=True),
            Column('category'),
            Column('messageid'),
            Column('utcdate', type='int'),
            Column('zoneoffset', type='int'),
            Column('subject'),
            Column('fromname'),
            Column('fromaddr'),
            Column('header'),
            Column('text'),
            Column('threadroot'),
            Column('threadparent'),
            Index(['messageid']),
            Index(['id']),
            Index(['category']),
            Index(['utcdate']),
            Index(['threadroot'])]

        mailarc_category_table = Table('mailarc_category', key=('category'))[
            Column('category'),
            Column('mlid'),
            Column('yearmonth'),
            Column('count', type='int'),
            Index(['category']),
            Index(['mlid']),
            Index(['yearmonth'])]

        self.create_table(db, mailarc_table)
        self.create_table(db, mailarc_category_table)
        
    def update_thread_root(self, db):
        from model import MailFinder
        
        self.log.info("update thread_root...")
        
        mails = MailFinder.find_not_root(self.env)
        
        cursor = db.cursor()
        for mail in mails:
            root_id = mail.get_thread_root().messageid
            sql = "UPDATE mailarc SET threadroot = %s WHERE messageid = %s" 
            self.log.debug(_('root_id=%s, messageid=%s' % (root_id, mail.messageid)))
            if root_id == mail.messageid:
                #自分が親(親メッセージIDのメールがDBに存在しない)の場合、更新しない
                continue
            cursor.execute(sql, (root_id, mail.messageid))
            self.log.debug('%s' % sql)
            
        self.log.info("update thread_root is done.")
        
    def change_schema(self, db):
        cursor = db.cursor()
        
        all_column = "id,category,messageid,utcdate,zoneoffset,subject,fromname,fromaddr,header,text,threadroot,threadparent"
        all_column_c = "category,mlid,yearmonth,count"
        try:
            cursor.execute("CREATE TEMPORARY TABLE mailarc_backup"
                           "(%s)" % all_column)
            cursor.execute("CREATE TEMPORARY TABLE mailarc_category_backup"
                           "(%s)" % all_column_c)
            
            self.log.info("create temporary tables is done.")
            
            cursor.execute("INSERT INTO mailarc_backup SELECT %s FROM mailarc" % all_column)
            cursor.execute("INSERT INTO mailarc_category_backup SELECT %s FROM mailarc_category" % all_column_c)
            
            self.log.info("copy to backup table is done.")

            cursor.execute("DROP TABLE mailarc")
            cursor.execute("DROP TABLE mailarc_category")
            
            self.log.info("drop old schema tables is done.")
            
            self.create_db(db)
            
            self.log.info("create new tables is done.")
            
            cursor.execute("INSERT INTO mailarc SELECT %s FROM mailarc_backup" % all_column)
            cursor.execute("INSERT INTO mailarc_category SELECT %s FROM mailarc_category_backup" % all_column_c)
            
            self.log.info("restore data is done.")
            
            cursor.execute("DROP TABLE mailarc_backup")
            cursor.execute("DROP TABLE mailarc_category_backup")
            
            self.log.info("drop temporary tables is done.")
            
            cursor.execute("SELECT count(*) FROM mailarc")
            row = cursor.fetchone()[0]
            
            self.log.info("converted count: %s" % row)
            
        except Exception, e:
            import traceback
            traceback.print_exc(e)
            db.rollback()
        
    def is_no_table(self, db):
        # check for database table
        cursor = db.cursor()
        try:
            cursor.execute("SELECT id FROM mailarc WHERE id='1'")
            cursor.execute("SELECT category FROM mailarc_category WHERE category='1'")
        except Exception, e:
#            import traceback
#            traceback.print_exc(e)
            return True
        return False
        
    def is_old_schema(self, db):
        cursor = db.cursor()
        try:
            cursor.execute("INSERT INTO mailarc ("
                    "category, messageid) "
                    "VALUES (%s,%s)", ('testcategory', 'testid'))
            cursor.execute("SELECT id from mailarc WHERE messageid = 'testid'")
            id = cursor.fetchone()[0]
            if id is None:
                db.rollback()
                return True
        except :
            db.rollback()
            return True
            
        db.rollback()
        return False

    def create_table(self, db, table):
        db_connector, _ = DatabaseManager(self.env)._get_connector()
        
        stmts = db_connector.to_sql(table)
        for stmt in stmts:
            self.execute_query(db, stmt)
    
    def execute_query(self, db, sql, *params):
        cur = db.cursor()
        try:
            cur.execute(sql, params)
        except Exception, e:
            pass
