#!/usr/bin/env python3

import os
import os.path
import pathlib
import shutil
import subprocess
import socket
import MySQLdb
from datetime import datetime, timedelta


DB_USER = 'admin'
DB_PWD_FILE = '/etc/psa/.psa.shadow'
VHOST_ROOT = '/var/www/vhosts'
DBDUMP_DIR = 'db_backups'


def get_dbs():
    db_pass = open(DB_PWD_FILE).read().strip()
    dbcon = MySQLdb.connect('localhost', DB_USER, db_pass, 'psa')
    cursor=dbcon.cursor()
    q = """select db.name as 'Database',d.name as 'Subscription' from data_bases db,domains d,clients c where d.cl_id=c.id and db.dom_id=d.id"""
    cursor.execute(q)
    ret = []
    for row in cursor.fetchall():
        ret.append({'db': row[0], 'domain': row[1]})
    dbcon.close()
    return ret


def get_user_group(vhost_path):
    info = pathlib.Path(vhost_path)
    return info.owner(), info.group()


def create_dbdump_dir(vhost_path, user, group):
    dbdump_path = os.path.join(vhost_path, DBDUMP_DIR)
    if os.path.exists(dbdump_path):
        return False
    print('Creating db dump directory %s' % dbdump_path)
    os.mkdir(dbdump_path)
    shutil.chown(dbdump_path, user, group)
    return True


def remove_old_backups(dbdump_path):
    for filename in os.listdir(dbdump_path):
        if not filename.endswith('db.gz'):
            continue
        filepath = os.path.join(dbdump_path, filename)
        if not os.path.isfile(filepath):
            continue
        dump_modified = datetime.fromtimestamp(os.path.getmtime(filepath))
        if datetime.now() - dump_modified > timedelta(hours=23,minutes=55):
            os.unlink(filepath)


def dump_db(db_name, db_filepath, user, group):
    print('Dumping DB %s to %s' % (db_name, db_filepath))
    env = os.environ.copy()
    hostname = socket.gethostname()
    # shwl-0110 is special as it is the only shwl server that uses a separate DB server.
    # It has mysqldump info set via /root/.my.cnf
    if hostname == 'shwl-0110.s.beebyte.se':
        mysqldump_cmd = ['/usr/bin/mysqldump', '--single-transaction', db_name]
    else:
        mysqldump_cmd = ['/usr/bin/mysqldump', '--single-transaction', '-u', 'admin', db_name]
        env['MYSQL_PWD'] = open(DB_PWD_FILE).read().strip()
    gzip_cmd = ['/bin/gzip']
    fd = open(db_filepath, 'wb')
    p1 = subprocess.Popen(mysqldump_cmd, stdout=subprocess.PIPE, env=env)
    p2 = subprocess.Popen(gzip_cmd, stdin=p1.stdout, stdout=fd)
    p2.communicate()
    fd.close()
    shutil.chown(db_filepath, user, group)


def main():
    current_date_time = datetime.now().strftime("%Y-%m-%d-%H_%M")
    cleaned_paths = set()
    for dbdata in get_dbs():
        vhost_path = os.path.join(VHOST_ROOT, dbdata['domain'])
        if not os.path.isdir(vhost_path):
            continue
        user, group = get_user_group(vhost_path)
        dbdump_path = os.path.join(vhost_path, DBDUMP_DIR)
        if not os.path.exists(dbdump_path):
            create_dbdump_dir(vhost_path, user, group)
        # Don't do anything if the dumpdir is a symlink. Security issue.
        if os.path.islink(dbdump_path):
            continue
        if dbdump_path not in cleaned_paths:
            remove_old_backups(dbdump_path)
            cleaned_paths.add(dbdump_path)
        db_filepath = os.path.join(dbdump_path, '%s-%s.db.gz' % (dbdata['db'], current_date_time))
        dump_db(dbdata['db'], db_filepath, user, group)


if __name__ == '__main__':
    main()