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

import calendar
import email
import os
import re
import time
import traceback

from datetime import datetime
from email.Utils import getaddresses

from trac.attachment import Attachment, AttachmentModule
from trac.core import *
from trac.mimeview.api import Context
from trac.perm import PermissionSystem
from trac.resource import Resource, ResourceNotFound
from trac.ticket.model import Ticket
from trac.util import NaivePopen, Markup
from trac.util.compat import set, sorted
from trac.util.datefmt import utc, to_timestamp
from trac.wiki import wiki_to_html,wiki_to_oneliner
from trac.wiki.formatter import Formatter, WikiProcessor

from api import IEmailHandler
from attachment import MailArchiveAttachment
from mail_parser import MailParser, _decode_to_unicode
from util import *

SELECT_FROM_MAILARC = "SELECT id, category, messageid, utcdate, zoneoffset, subject, fromname, fromaddr, header, text, threadroot, threadparent FROM mailarc "

class Mail(object):
    """model for the Mail."""
    
    id_is_valid = staticmethod(lambda num: 0 < int(num) <= 1L << 31)
    
    def __init__(self, env, id=None, db=None, messageid=None, row=None):
        self.env = env
        self.db = db
        self.log = Logger(env)
        
        if id is not None:
            self.resource = Resource('mailarchive', str(id), None)
            self._fetch_mail(id)
        elif messageid is not None:
            self._fetch_mail_by_messageid(messageid[0], messageid[1])
            self.resource = Resource('mailarchive', self.id, None)
        elif row is not None:
            self._fetch_mail_by_row(row)
            self.resource = Resource('mailarchive', self.id, None)
        else:
            self.messageid = ''
            self.subject = ''
            self.utcdate = 0
            self.localdate = ''
            self.zoneoffset = 0
            self.body = ''
            self.raw_headers = []
        
    def __eq__(self, other):
        if isinstance(other, Mail):
            return self.messageid == other.messageid
        return super.__eq__(other)
        
    def _get_db(self):
        if self.db:
            return self.db
        else:
            return self.env.get_db_cnx()

    def _get_db_for_write(self):
        if self.db:
            return (self.db, False)
        else:
            return (self.env.get_db_cnx(), True)
        
    def get_sanitized_fromaddr(self):
        return self.fromaddr.replace('@',
                                     self.env.config.get('mailarchive',
                                                         'replaceat', '@'))
        
    def get_fromtext(self):
        return get_author(self.fromname, self.fromaddr) 
        
    def _make_category(self):
        yearmonth = time.strftime("%Y%m", time.gmtime(self.utcdate))
        category = self.mlid + yearmonth
        return category
        
    def get_plain_body(self):
        return self._sanitize(self.env, self.body)
    
    def get_html_body(self, req):
        
        # for HTML Mail
        if self.body.lstrip().startswith('<'):
            return Markup(self.body)
        
        contentlines = self.body.splitlines()
        htmllines = ['',]
        
        #customize!
        #http://d.hatena.ne.jp/ohgui/20090604/1244114483
        wikimode = req.args.get('wikimode', 'on')
        for line in contentlines:
            if self.env.config.get('mailarchive', 'wikiview', 'enabled') == 'enabled' and wikimode == 'on':
                htmllines.append(wiki_to_oneliner(line, self.env, self.db, False, False, req))
            else:
                htmllines.append(Markup(Markup().escape(line).replace(' ','&nbsp;')))
            
        content = Markup('<br/>').join(htmllines)
        return content
        
    def _sanitize(self, env, text):
        return text.replace('@', env.config.get('mailarchive', 'replaceat','_at_') )
    
    def _fetch_mail(self, id):
        row = None
        if self.id_is_valid(id):
            db = self._get_db()
            cursor = db.cursor()
            cursor.execute(SELECT_FROM_MAILARC + " WHERE id=%s", (id,))

            row = cursor.fetchone()
        if not row:
            raise ResourceNotFound('Mail %s does not exist.' % id,
                                   'Invalid Mail Number')

        self._fetch_mail_by_row(row)
    
    def _fetch_mail_by_messageid(self, messageid, category):
        row = None

        db = self._get_db()
        cursor = db.cursor()
        
        #TODO categoryでさらに範囲を絞るべきかどうか...
        cursor.execute(SELECT_FROM_MAILARC + " WHERE messageid=%s",
                        (messageid,))

        row = cursor.fetchone()
        if not row:
            raise ResourceNotFound('Mail messageid %s does not exist.' % (messageid),
                                   'Invalid Mail messageid Number')

        self._fetch_mail_by_row(row)
        
    def _fetch_mail_by_row(self, row):
        self.id = row[0]
        self.category = row[1]
        self.messageid = row[2]
        self.utcdate = row[3]
        self.zoneoffset = row[4]
        self.subject = row[5]
        self.fromname = row[6]
        self.fromaddr = row[7]
        if row[8] != '':
            self.raw_headers = eval(row[8])
        else:
            self.raw_headers = []
        self.body = row[9]
        self.thread_root = row[10]
        self.thread_parent = row[11]
        
        self.mlid = self.category[:-6]
        
        self.zone = self._to_zone(self.zoneoffset)
        self.localdate = to_localdate(self.utcdate, self.zoneoffset)
        
        self._parse_raw_headers(self.raw_headers)
        
    def _parse_raw_headers(self, raw_headers):
        tos = [_decode_to_unicode(self, x[1]) for x in self.raw_headers if x[0] == 'To']
        ccs = [_decode_to_unicode(self, x[1]) for x in self.raw_headers if x[0] == 'Cc']
        
        self.tos = getaddresses(tos)
        self.ccs = getaddresses(ccs)
        self.all_recipients = self.tos + self.ccs
        
    def _to_zone(self, zoneoffset):
        #zone and date
        zone = ''
        if zoneoffset == '':
            zoneoffset = 0
        if zoneoffset > 0:
            zone = ' +' + time.strftime('%H%M', time.gmtime(zoneoffset))
        elif zoneoffset < 0:
            zone = ' -' + time.strftime('%H%M', time.gmtime(-1 * zoneoffset))
        return zone
                
    def get_href(self, req):
        return req.href.mailarchive(self.id)
    
    def get_toaddrs(self):
        return [x[1] for x in self.tos]
        
    def get_tonames(self):
        return [x[0] for x in self.tos]
    
    def get_reply_ccs(self):
        return [x for x in self.all_recipients if x[1] != self.fromaddr]
    
    def get_subject(self):
        if is_empty(self.subject):
            return '(no subject)'
        else:
            return self.subject
    
    def get_senddate(self):
        return self.localdate + self.zone
    
    def get_thread_root(self, cached_mails=[]):
        if self.thread_root == '':
            return self
        
        #キャッシュにあればそれを返す
        for mail in cached_mails:
            if mail.messageid == self.thread_root and mail.category == self.category:
                return mail
        #thread_rootで検索可能であればそれを返す
        try:
            return Mail(self.env, messageid=(self.thread_root, self.category))
        except ResourceNotFound:
            pass
        
        #親を辿っていって探す
        parent = self
        try:
            parent = self.get_thread_parent()
            while parent.category == self.category and parent.get_thread_parent_id() is not None:
                parent = parent.get_thread_parent()
            return parent
        except ResourceNotFound:
            return parent
    
    def get_thread_parent_id(self):
        if self.thread_parent != '':
            return self.thread_parent.split(' ')[0]
        return None
    
    def get_thread_parent(self):
        if self.thread_parent != '':
            return Mail(self.env, db=self.db, messageid=(self.get_thread_parent_id(), self.category))
        return self
    
    def get_children(self, desc=False, cached_mails=None):
        if cached_mails:
            self.log.debug("[%s] mail's threads is cached." % self.id)
            return [x for x in cached_mails if x.get_thread_parent_id() == self.messageid]
            
        db = self._get_db()
        cursor = db.cursor()
        sql = SELECT_FROM_MAILARC + " WHERE threadparent LIKE %s ORDER BY utcdate"
        
        if desc:
            sql += " DESC"
        
        cursor.execute(sql, ('%s %%' % self.messageid,))
        
        children = []
        
        for row in cursor:
            child_mail = Mail(self.env, row=row, db=self.db)
            children.append(child_mail)
        return children
    
    def get_thread_mails(self, desc=False):
        #過去バージョンのDBでは動作しないロジックとなっているので注意
        #thread_rootがルートのmessageidであることを前提としています
        
        #TODO 途中で親子関係が切れている場合は全部ひっぱれない
        
        db = self._get_db()
        cursor = db.cursor()
        sql = SELECT_FROM_MAILARC + " WHERE threadroot = %s ORDER BY utcdate"
        
        if desc:
            sql += " DESC"
        
        mails = []
        if self.thread_root == '':
            #自分がルートの場合は、自分のmessageidをルートとするメールを探す
            cursor.execute(sql, (self.messageid,))
        else:
            cursor.execute(sql, (self.thread_root,))
        for row in cursor:
            mails.append(Mail(self.env, row=row, db=self.db))
        return mails
    
    def has_children(self, cached_mails=None):
        rtn = len(self.get_children(cached_mails=cached_mails)) > 0
        return rtn 

    def get_related_tickets(self, req):
        db = self._get_db()
        return get_related_tickets(self.env, req, db, self.id)
    
    def has_attachments(self, req):
        attachment = MailArchiveAttachment(self.env, self.id)
        return attachment.has_attachments(req)

    def has_permission(self, req):
        mailarchive_realm = Resource('mailarchive', self.id, None)
        action = get_mlperm_action(self.mlid)
        if action in req.perm(mailarchive_realm):
            return True
        else:
            return False
        
    def assert_permission(self, req):
        action = get_mlperm_action(self.mlid)
        req.perm.assert_permission(action)

    def populate(self, author, msg, mlid):
        """Populate the mail with 'suitable' values from a message"""
        
        if 'message-id' not in msg:
            raise 'Illegal Format Mail!'
        
        self.mlid = mlid
        
        mail_parser = MailParser(self.env, self.db)
        mail_header = mail_parser.parse_header(msg)
        mail_body = mail_parser.parse_body(author, msg)
        
        #パース結果をmodelに反映
        #header
        self.messageid = mail_header.messageid
        self.utcdate = mail_header.utcdate
        self.zoneoffset = mail_header.zoneoffset
        self.subject = mail_header.subject
        self.fromname = mail_header.fromname
        self.fromaddr = mail_header.fromaddr
        self.zone = mail_header.zone
        self.localdate = mail_header.localdate
        self.raw_headers = mail_header.headers
        
        self._parse_raw_headers(self.raw_headers)
        
        #body
        self.body = mail_body.text
        
        self.mail_header = mail_header
        self.mail_body = mail_body
        
        ref_messageid = mail_header.ref_messageid
        self._make_thread(ref_messageid)
        
        category = self._make_category()
        self.category = category
        
    def update_or_save(self):
        if self.messageid is None or self.messageid == '':
            raise "Can't save mail to database because of No messageid."
        
        db, has_tran = self._get_db_for_write()
        cursor = db.cursor()

        try:
            # insert or update mailarc
            cursor.execute("UPDATE mailarc SET "
                    "category=%s, utcdate=%s, zoneoffset=%s, subject=%s,"
                    "fromname=%s, fromaddr=%s, header=%s, text=%s, threadroot=%s, threadparent=%s "
                    "WHERE messageid=%s AND category=%s",
                    (self.category,
                    self.utcdate,
                    self.zoneoffset,
                    self.subject, self.fromname,
                    self.fromaddr, str(self.raw_headers), self.body,
                    self.thread_root, self.thread_parent,
                    self.messageid, self.category))
    
            new_mail = False
    
            if cursor.rowcount == 0:
                cursor.execute("INSERT INTO mailarc ("
                    "category, messageid, utcdate, zoneoffset, subject,"
                    "fromname, fromaddr, header, text, threadroot, threadparent) "
                    "VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",
                    (self.category,
                    self.messageid,
                    self.utcdate,
                    self.zoneoffset,
                    self.subject, self.fromname,
                    self.fromaddr, str(self.raw_headers), self.body,
                    self.thread_root, self.thread_parent))
                new_mail = True
    
            #DBで発行されたidを取得する
            cursor.execute("SELECT id from mailarc WHERE messageid=%s AND category=%s",
                           (self.messageid, self.category))
            self.id = cursor.fetchone()[0]
            
            #添付ファイルありの場合はDBに登録する
            self.mail_body.commit(self.id)
    
            #update category count
            #mailarcテーブルを更新しているの、Transactionコミットまでロックがかかっている。
            #ここで安全にカテゴリのカウントアップが可能。
            if new_mail:
                count = self._get_category_count(cursor)
                cursor.execute("UPDATE mailarc_category SET count=%s WHERE category=%s",
                               (count + 1, self.category))
    
            if has_tran:
                db.commit()
        except:
            db.rollback()
            
            #添付ファイルの一時ファイルを削除する
            self.mail_body.rollback()
            raise

    def _get_category_count(self, cursor):
        yearmonth = time.strftime("%Y%m", time.gmtime(self.utcdate))
        category = self.mlid + yearmonth
        cursor.execute("SELECT category, mlid, yearmonth, count FROM mailarc_category WHERE category=%s",
                        (category,))
        row = cursor.fetchone()
        if row:
            count = row[3]
            return count
        else:
            cursor.execute("INSERT INTO mailarc_category (category, mlid, yearmonth, count) VALUES(%s, %s, %s, %s)",
                            (category,
                             self.mlid,
                             yearmonth,
                             0))
            return 0

    def _make_thread(self, ref_messageid):
        #body = body.replace(os.linesep,'\n')
        self.log.debug('Thread')

        thread_parent = ref_messageid.replace("'", '').replace(',',' ')
        thread_root = ''
        if thread_parent !='':
        # search first parent id
            self.log.debug("SearchThread;" + thread_parent)
            
            db = self._get_db()
            cursor = db.cursor()
            sql = "SELECT threadroot, messageid, utcdate FROM mailarc where messageid in (%s)" \
                  " ORDER BY utcdate DESC" % ref_messageid
            
            self.log.debug(sql)
            
            cursor.execute(sql)

            row = cursor.fetchone()
            if row:
                print row[0], row[1]
                #親スレッドがルートの場合
                if row[0] == '':
                    #ルートに親を設定
                    thread_root = thread_parent.split(' ').pop()
                    self.log.debug("NewThread;" + thread_root)
                else:
                    #親スレッドはルートではい場合、親スレッドのルートを設定する
                    thread_root = row[0]
                    self.log.debug("AddToThread;" + thread_root)
            else:
                    self.log.debug("NoThread;" + thread_parent)
                    
        self.thread_root = thread_root.strip()
        self.thread_parent = thread_parent

class MailFinder(object):
    
    @staticmethod
    def get_categories(env, req, db, target_category='',
                       ml_permission_map={}):
        view_actions, accessable_mlids = get_accessable_ml(env, req, ml_permission_map)

        cursor = db.cursor()
            
        if len(accessable_mlids) > 0:
            sql = """SELECT category, mlid, yearmonth, count 
                       FROM mailarc_category
                       WHERE mlid IN (%s)
                       ORDER BY mlid, yearmonth DESC""" % (('%s,' * len(accessable_mlids))[:-1])
            cursor.execute(sql, accessable_mlids)
        else:
            sql = """SELECT category, mlid, yearmonth, count 
                       FROM mailarc_category
                       ORDER BY mlid, yearmonth DESC"""
            cursor.execute(sql)

        mls = []
        pre_mlid = ''
        name = ''
        year = ''
        month = ''
        for category, mlid, yearmonth, count in cursor:
            if target_category == '':
                target_category = category 

            category_item = {
                'id': mlid + yearmonth,
                'name': mlid,
                'year': yearmonth[:4],
                'month': yearmonth[4:],
                'count': str(count),
                'href': get_category_href(req, category)
            }
            if category == target_category:
                name = mlid
                year = yearmonth[:4]
                month = yearmonth[4:]
                category_item['href'] = ""

            if pre_mlid != mlid:
                mls.append({'name':mlid,'yearmonths':[]})
                pre_mlid = mlid
            mls[-1]['yearmonths'].append(category_item)

        return mls, name, year, month, target_category
    
    @staticmethod
    def find_root_id(env, messageid):
        #self.thread_rootはオリジナル版では親のメールなので注意
        #互換性維持のため自力で探しにいく
        db = env.get_db_cnx()
        cursor = db.cursor()
        cursor.execute("SELECT id, threadparent FROM mailarc WHERE messageid = %s",
                       (messageid, ))
        row = cursor.fetchone()
        if row:
            #見つかったが親が無い場合
            if row[1] == '':
                return messageid
            else:
                root_id = MailFinder.find_root_id(env, row[1].split(' ')[0])
                if root_id:
                    return root_id
                else:
                    return messageid
        else:
            #親メールが見つからない場合
            return None
    
    @staticmethod
    def find_by_category(env, category, db=None, desc=True, limit=None):
        """カテゴリ名をキーにしてmailarcテーブルよりメールデータを検索する。
                ヒットした件数分、Mailオブジェクトを作成するためそれなりに重い
        """
        if not db:
            db = env.get_db_cnx()
        cursor = db.cursor()
        
        sql = SELECT_FROM_MAILARC + " WHERE category = %s ORDER BY utcdate"
        if not desc:
            sql += " ASC"
            
        if limit is None:
            limit = get_item_per_page(env) * get_num_shown_pages(env)
        sql += " LIMIT %d" % limit
        
        cursor.execute(sql, (category,))
        
        mails = [Mail(env, row=row, db=db) for row in cursor]
        
        return mails
    
    @staticmethod
    def find_ids_by_category(env, category, db=None, desc=True, limit=None):
        """カテゴリ名をキーにしてmailarcテーブルよりメールIDのみを検索する。
        """
        
        if not db:
            db = env.get_db_cnx()
        cursor = db.cursor()
        
        sql = "SELECT id from mailarc WHERE category = %s ORDER BY utcdate"
        if not desc:
            sql += " ASC"
            
        if limit is None:
            limit = get_item_per_page(env) * get_num_shown_pages(env)
        sql += " LIMIT %d" % limit
        
        cursor.execute(sql, (category,))
        
        mail_ids = [row[0] for row in cursor]
        
        return mail_ids
    
    @staticmethod
    def find_roots_by_id(env, mail_ids, db=None, desc=True):
        if len(mail_ids) == 0:
            return []
        
        if not db:
            db = env.get_db_cnx()
        cursor = db.cursor()
        
        param = ('%s,' * len(mail_ids))[:-1]
        
        sql = """
        SELECT DISTINCT ma1.id, ma1.category, ma1.messageid, ma1.utcdate, ma1.zoneoffset, ma1.subject, ma1.fromname, ma1.fromaddr, ma1.header, ma1.text, ma1.threadroot, ma1.threadparent, ma2.utcdate AS sortcol 
          from mailarc ma1, mailarc ma2
          WHERE ma2.id IN(%s) AND ma1.messageid = ma2.threadroot
          
        UNION
        
        SELECT ma1.id, ma1.category, ma1.messageid, ma1.utcdate, ma1.zoneoffset, ma1.subject, ma1.fromname, ma1.fromaddr, ma1.header, ma1.text, ma1.threadroot, ma1.threadparent, ma1.utcdate AS sortcol
          from mailarc ma1
          WHERE ma1.id IN(%s) AND ma1.threadroot = %s
        ORDER BY sortcol""" % (param, param, '%s')
                                     
        if not desc:
            sql += " ASC"
            
        env.log.debug(sql)
            
        args = (mail_ids*2)
        args.append('')
        cursor.execute(sql, (args))
        
        rows = {}
        for idx, row in enumerate(cursor):
            rows[row[0]] = (idx, row)
            
        root_mails = []
        for key in rows:
            root_mails.append((rows[key][0], Mail(env, row=rows[key][1], db=db)))
        
        root_mails.sort(lambda x, y: x[0] - y[0])
        
        root_mails = [x[1] for x in root_mails]
        
        return root_mails
    
    @staticmethod
    def find_not_root(env):
        db = env.get_db_cnx()
        cursor = db.cursor()
        cursor.execute(SELECT_FROM_MAILARC + " WHERE threadroot <> ''")
        mails = [Mail(env, row=row, db=db) for row in cursor]
        return mails
    
    @staticmethod
    def find_thread_mails(env, root_messageids):
        db = env.get_db_cnx()
        cursor = db.cursor()
        sql = SELECT_FROM_MAILARC + " WHERE threadroot IN (%s)" % ','.join(['%s' for x in root_messageids])
        
        env.log.debug('MailFinder.find_thread_mails: %s' % sql)
        
        cursor.execute(sql, root_messageids)
        thread_mails = [Mail(env, row=row, db=db) for row in cursor]
        return thread_mails
    
    @staticmethod
    def find_mails(env, ids):
        db = env.get_db_cnx()
        cursor = db.cursor()
        sql = SELECT_FROM_MAILARC + " WHERE id IN (%s)" % ','.join(['%s' for x in ids])
        
        env.log.debug('MailFinder.find_mails: %s' % sql)
        
        cursor.execute(sql, ids)
        mails = [Mail(env, row=row, db=db) for row in cursor]
        return mails
    
    @staticmethod
    def find_by_date(env, from_date, to_date):
        
        db = env.get_db_cnx()
        cursor = db.cursor()
        
        sql = SELECT_FROM_MAILARC + " WHERE utcdate >= %s AND utcdate <= %s"
        
        cursor.execute(sql, (to_timestamp(from_date), to_timestamp(to_date)))
        
        mails = [Mail(env, row=row, db=db) for row in cursor]
        
        return mails

class MailImportHandler(Component):
    """create a ticket from an email"""

    implements(IEmailHandler)
    
    def match(self, mail):
        return true

    def invoke(self, mail, warnings):
        t ="""
        print "invoke"
        print mail.id
        print mail.messageid
        print mail.body
        print mail.mlid
        print mail.subject
        print mail.zone
        print mail.zoneoffset
        print mail.localdate
        print mail.utcdate
        print mail.fromname
        print mail.fromaddr
        print mail.thread_root
        print mail.thread_parent
        """    
        
        mail.update_or_save()

    def order(self):
        return None