#!/usr/bin/env python3

# apt install python3-psycopg2

import subprocess
import datetime
import psycopg2

valid_destinations = [
    b'ticketsystem@hostname'
]


def send_to_rspamd(ticketid, title, spamflag, maildata):
    # send the mail to rspamc to process as ham/spam
    with subprocess.Popen(['/usr/bin/rspamc', f"learn_{spamflag}"], stdin=subprocess.PIPE, stdout=subprocess.PIPE, stderr=subprocess.PIPE) as rspamd:
        stdout, stderr = rspamd.communicate(input=maildata)
        if len(stdout) > 0:
            print(f"Ticket {ticketid}: {spamflag}\nSubject: {title}\nstdout: {stdout.decode('utf-8')}")
        if len(stderr) > 0:
            print(f"Ticket {ticketid}: {spamflag}\nSubject: {title}\nstderr: {stderr.decode('utf-8')}")


# Send mails from zammad to rspamc to learn already processed mails
if __name__ == '__main__':
    conn = psycopg2.connect("dbname=zammad host=DBHOST user=rspamd password=DBPASSWORD")
    cur = conn.cursor()
    # get starting position, one week ago
    last_week = (datetime.datetime.now() - datetime.timedelta(days=7)).date()

    # fetch all current tickets, which are in state closed and take its' first article
    cur.execute("SELECT tickets.id AS ticket, tickets.title AS title, tags.tag_item_id AS flag, MIN(ticket_articles.id) AS article \
            FROM tickets LEFT JOIN tags ON tags.o_id=tickets.id \
            LEFT JOIN ticket_articles ON tickets.id=ticket_articles.ticket_id \
            WHERE tickets.state_id=4 \
            AND (tags.tag_item_id=1 OR tags.tag_item_id IS NULL) \
            AND tickets.close_at>=%s \
            GROUP BY tickets.id, tags.tag_item_id", (last_week.strftime('%Y-%m-%d'),))
    tickets = cur.fetchall()

    for ticketrow in tickets:
        ticket, title, flag, article = ticketrow
        # fetch the associated mail file from db, there should only be one with store_object_id=2 (Ticket::Article::Mail)
        cur.execute("SELECT stores.filename AS filename, store_provider_dbs.data AS data \
                FROM stores LEFT JOIN store_provider_dbs ON store_provider_dbs.id=store_file_id \
                WHERE stores.store_object_id=2 \
                AND stores.o_id=%s", (str(article),))
        mail = cur.fetchall()
        if len(mail) != 1:
            print(f"ERROR: did find more than one mail in ticket {ticket}")
        else:
            filename, data = mail[0]
            mailbytes = data.tobytes()

            # safety check
            has_been_delivered_to_me = False
            for dest in valid_destinations:
                if b'Delivered-To: ' + dest in mailbytes:
                    has_been_delivered_to_me = True
                    break

            if not has_been_delivered_to_me:
                print(f"ERROR: mail for ticket {ticket} does not contain Delivered-To header field")
            elif flag == 1:
                send_to_rspamd(ticket, title, 'spam', mailbytes)
            elif flag is None:
                send_to_rspamd(ticket, title, 'ham', mailbytes)
            else:
                print(f"ERROR: mail flag in ticket {ticket} undefined: {flag}")

    cur.close()
    conn.close()