# Author: Zhang Huangbin <zhb@iredmail.org>

import sys
import web
import types
from libs import iredutils
from libs.amavisd import core

cfg = web.iredconfig
session = web.config.get('_session')

# Import backend related modules.
if cfg.general.backend == 'ldap':
    from libs.ldaplib import admin as adminlib
elif cfg.general.backend == 'mysql':
    from libs.mysql import admin as adminlib

class Log(core.AmavisdWrap):
    def reverseDomainNames(self, domains=[],):
        allReversedDomainNames = []
        for d in domains:
            reversedDomain = d.split('.')
            reversedDomain.reverse()
            allReversedDomainNames += ['.'.join(reversedDomain)]

        return allReversedDomainNames


    def deleteRecordsOlderThanDays(self, days=0):
        # Delete old sent/received mails from table 'msgs', it will also delete records
        # in table 'msgrcpt'.
        if days > 0:
            try:
                # msgs.content:
                #   - U: unchecked
                #   - M: bad-mime
                #   - H: bad-header
                #   - O: oversized
                #   - C: clean

                self.db.delete(
                    'msgs',
                    where="""content IN ('U', 'M', 'H', 'O', 'C') \
                            AND FROM_UNIXTIME(time_num) < DATE_SUB(NOW(), INTERVAL %d DAY)
                    """ % (iredutils.AMAVISD_REMOVE_RECEIVED_IN_DAYS,)
                )
            except:
                pass

        # Always return True.
        return True

    def deleteRecordsByMailID(self, logType='sent', id=[],):
        # logType in ['received', 'sent', 'quarantined', 'quarantine']
        self.logType = str(logType)

        if not isinstance(id, types.ListType):
            return (False, 'INCORRECT_MAILID')

        if len(id) == 0:
            return (True,)
        else:
            # Converted into SQL style list.
            id = web.sqlquote(id)

        if self.logType in ['received', 'sent', 'quarantined', 'quarantine',]:
            try:
                # Delete records in tables: msgs, msgrcpt.
                self.db.delete('msgs', where='mail_id IN %s' % id)
                self.db.delete('msgrcpt', where='mail_id IN %s' % id)
            except Exception, e:
                return (False, str(e))

        if self.logType in ['quarantined', 'quarantine']:
            try:
                self.db.delete('quarantine', where="mail_id IN %s" % (id))
            except Exception, e:
                return (False, str(e))

        return (True,)

    def getNumberOfIncomingMails(self, reversedDomainNames=[], timeLength=None,):
        # timeLength is seconds.
        self.total = 0
        self.sql_append_where = ''

        if len(reversedDomainNames) == 0 or not isinstance(reversedDomainNames, types.ListType):
            return self.total
        else:
            self.sql_append_where += ' AND recip.domain IN %s' % web.sqlquote(reversedDomainNames)

        if isinstance(timeLength, types.IntType):
            self.sql_append_where += ' AND FROM_UNIXTIME(msgs.time_num) > DATE_SUB(NOW(), INTERVAL %d SECOND)' % timeLength

        try:
            resultOfCount = self.db.query(
                '''
                SELECT COUNT(msgs.mail_id) AS total
                FROM msgs
                LEFT JOIN msgrcpt ON msgs.mail_id = msgrcpt.mail_id
                LEFT JOIN maddr AS sender ON msgs.sid = sender.id
                LEFT JOIN maddr AS recip ON msgrcpt.rid = recip.id
                WHERE msgs.content IS NOT NULL AND msgs.content NOT IN ('S', 's', 'Y', 'V') %s
                ORDER BY msgs.time_num DESC
                ''' % (self.sql_append_where,)
            )
            self.total = resultOfCount[0].total
            if self.total is None:
                self.total = 0
        except:
            pass

        try:
            self.deleteRecordsOlderThanDays(days=iredutils.AMAVISD_REMOVE_MAILLOG_IN_DAYS)
        except:
            pass

        return self.total


    def getNumberOfOutgoingMails(self, reversedDomainNames=[], timeLength=None,):
        # timeLength is seconds.
        self.total = 0
        self.sql_append_where = ''

        if len(reversedDomainNames) == 0 or not isinstance(reversedDomainNames, types.ListType):
            return self.total
        else:
            self.sql_append_where += ' AND sender.domain IN %s' % web.sqlquote(reversedDomainNames)

        if isinstance(timeLength, types.IntType):
            self.sql_append_where += ' AND FROM_UNIXTIME(msgs.time_num) > DATE_SUB(NOW(), INTERVAL %d SECOND)' % timeLength

        try:
            resultOfCount = self.db.query(
                '''
                SELECT COUNT(msgs.mail_id) AS total
                FROM msgs
                RIGHT JOIN msgrcpt ON (msgs.mail_id = msgrcpt.mail_id)
                RIGHT JOIN maddr AS sender ON (msgs.sid = sender.id)
                RIGHT JOIN maddr AS recip ON (msgrcpt.rid = recip.id)
                WHERE msgs.content IS NOT NULL %s
                AND msgs.content NOT IN ('S', 's', 'Y', 'V')
                ORDER BY msgs.time_num DESC
                ''' % (self.sql_append_where,)
            )
            self.total = resultOfCount[0].total
            if self.total is None:
                self.total = 0
        except:
            pass

        # Delete records older than specified days.
        try:
            self.deleteRecordsOlderThanDays(days=iredutils.AMAVISD_REMOVE_MAILLOG_IN_DAYS)
        except:
            pass

        return self.total


    def getNumberOfVirusMails(self, reversedDomainNames=[], timeLength=None,):
        # timeLength is seconds.
        self.total = 0
        self.sql_append_where = ''

        if len(reversedDomainNames) == 0 or not isinstance(reversedDomainNames, types.ListType):
            return self.total
        else:
            self.sql_append_where += ' AND (sender.domain IN %s OR recip.domain IN %s)' % (
                web.sqlquote(reversedDomainNames),
                web.sqlquote(reversedDomainNames),
            )

        if isinstance(timeLength, types.IntType):
            self.sql_append_where += ' AND FROM_UNIXTIME(msgs.time_num) > DATE_SUB(NOW(), INTERVAL %d SECOND)' % timeLength

        try:
            resultOfCount = self.db.query(
                '''
                SELECT COUNT(msgs.mail_id) AS total
                FROM msgs
                RIGHT JOIN msgrcpt ON (msgs.mail_id = msgrcpt.mail_id)
                RIGHT JOIN maddr AS sender ON (msgs.sid = sender.id)
                RIGHT JOIN maddr AS recip ON (msgrcpt.rid = recip.id)
                WHERE msgs.content = 'V' %s
                ORDER BY msgs.time_num DESC
                ''' % (self.sql_append_where,)
            )
            self.total = resultOfCount[0].total
            if self.total is None:
                self.total = 0
        except:
            pass

        try:
            self.deleteRecordsOlderThanDays(days=iredutils.AMAVISD_REMOVE_MAILLOG_IN_DAYS)
        except:
            pass

        return self.total


    def getInOutMails(self, logType='sent', cur_page=1):
        """
        logType in ['sent', 'received', 'all']
        """
        self.cur_page = int(cur_page)

        # Reversed domain name if need to sql query server with domain names.
        # Example: demo.iredmail.org -> org.iredmail.demo
        allReversedDomainNames = []

        self.count = 0          # Number of total mails.
        self.records = {}       # Detail records.
        self.mailIds = []       # List of msgs.mail_id
        self.sql_append_where = ''

        # Get all managed domain names and reversed names.
        allDomains = []

        if cfg.general.backend == 'ldap':
            adminLib = adminlib.Admin()
            resultOfAllDomains = adminLib.getManagedDomains(session.get('username'), attrs=['domainName'],)
            if resultOfAllDomains[0] is True:
                for i in resultOfAllDomains[1]:
                    domain = i[1].get('domainName', [''])[0]
                    allDomains += [domain]
        elif cfg.general.backend == 'mysql':
            adminLib = adminlib.Admin()
            resultOfAllDomains = adminLib.getManagedDomains(session.get('username'), domainNameOnly=True,)
            if resultOfAllDomains[0] is True:
                allDomains = resultOfAllDomains[1]

        allReversedDomainNames = self.reverseDomainNames(allDomains)

        if logType == 'received':
            self.sql_append_where += ' AND recip.domain IN %s' % web.sqlquote(allReversedDomainNames)
        elif logType == 'sent':
            self.sql_append_where += ' AND sender.domain IN %s' % web.sqlquote(allReversedDomainNames)
        else:
            pass

        ########################
        # Get detail records.
        #
        try:
            if logType == 'received':
                self.count = self.getNumberOfIncomingMails(allReversedDomainNames)

                result = self.db.query(
                    '''
                    SELECT
                        msgs.mail_id, msgs.subject, msgs.time_iso,
                        sender.email as sender_email,
                        recip.email as recipient
                    FROM msgs
                    LEFT JOIN msgrcpt ON (msgs.mail_id = msgrcpt.mail_id)
                    LEFT JOIN maddr AS sender ON (msgs.sid = sender.id)
                    LEFT JOIN maddr AS recip ON (msgrcpt.rid = recip.id)
                    WHERE msgs.content IS NOT NULL AND msgs.content NOT IN ('S', 's', 'Y', 'V') %s
                    ORDER BY msgs.time_num DESC
                    LIMIT %d
                    OFFSET %d
                    ''' % (self.sql_append_where,
                           session.pageSizeLimit,
                           (self.cur_page-1) * session.pageSizeLimit,
                          )
                )
                self.records = iredutils.convertAmavisdRecords(result)
            elif logType == 'sent':
                self.count = self.getNumberOfOutgoingMails(allReversedDomainNames)

                result = self.db.query(
                    '''
                    SELECT
                        msgs.mail_id, msgs.subject, msgs.time_iso,
                        sender.email as sender_email,
                        recip.email as recipient
                    FROM msgs
                    RIGHT JOIN msgrcpt ON (msgs.mail_id = msgrcpt.mail_id)
                    RIGHT JOIN maddr AS sender ON (msgs.sid = sender.id)
                    RIGHT JOIN maddr AS recip ON (msgrcpt.rid = recip.id)
                    WHERE msgs.content IS NOT NULL AND msgs.content NOT IN ('S', 's', 'Y', 'V') %s
                    ORDER BY msgs.time_num DESC
                    LIMIT %d
                    OFFSET %d
                    ''' % (self.sql_append_where,
                           session.pageSizeLimit,
                           (self.cur_page-1) * session.pageSizeLimit,
                          )
                )
                self.records = iredutils.convertAmavisdRecords(result)
            else:
                self.records = {}
        except Exception, e:
            pass

        # Get list of msgs.mail_id.
        for r in self.records:
            self.mailIds += [r.mail_id]

        return (self.count, list(self.records), self.mailIds)

    def getTopUser(self, reversedDomainNames=[], logType='sent', timeLength=None, number=10,):
        self.records = {}
        self.sql_append_where = ''

        if len(reversedDomainNames) == 0 or not isinstance(reversedDomainNames, types.ListType):
            return []

        if isinstance(timeLength, types.IntType):
            self.sql_append_where += ' AND FROM_UNIXTIME(msgs.time_num) > DATE_SUB(NOW(), INTERVAL %d SECOND)' % timeLength

        if logType == 'sent':
            try:
                result = self.db.query(
                    """
                    SELECT
                        COUNT(*) as total,
                        -- msgs.mail_id,
                        -- msgs.subject,
                        -- msgs.time_iso,
                        -- recip.email as recipient,
                        sender.email as mail
                    FROM msgs
                    RIGHT JOIN msgrcpt ON (msgs.mail_id = msgrcpt.mail_id)
                    RIGHT JOIN maddr AS sender ON (msgs.sid = sender.id)
                    RIGHT JOIN maddr AS recip ON (msgrcpt.rid = recip.id)
                    WHERE
                        msgs.content IS NOT NULL
                        AND sender.domain IN %s
                        %s
                    GROUP BY mail
                    ORDER BY total DESC
                    LIMIT %d
                    """ % (web.sqlquote(reversedDomainNames), self.sql_append_where, number)
                )
                self.records = list(result)
            except Exception, e:
                pass

        elif logType == 'received':
            try:
                result = self.db.query(
                    """
                    SELECT
                        COUNT(*) as total,
                        -- msgs.mail_id,
                        -- msgs.subject,
                        -- msgs.time_iso,
                        -- sender.email as sender_email,
                        recip.email as mail
                    FROM msgs
                    RIGHT JOIN msgrcpt ON (msgs.mail_id = msgrcpt.mail_id)
                    RIGHT JOIN maddr AS sender ON (msgs.sid = sender.id)
                    RIGHT JOIN maddr AS recip ON (msgrcpt.rid = recip.id)
                    WHERE
                        msgs.content IS NOT NULL
                        AND recip.domain IN %s
                        %s
                    GROUP BY mail
                    ORDER BY total DESC
                    LIMIT %d
                    """ % (web.sqlquote(reversedDomainNames), self.sql_append_where, number)
                )
                self.records = list(result)
            except Exception, e:
                pass

        return list(self.records)