#!/usr/bin/env python3

import argparse
import getpass
import subprocess

INITDB_SQL = """
CREATE DATABASE mailserver;
GRANT SELECT ON mailserver.* 
TO 'mailuser'@'localhost' IDENTIFIED BY '{mailserver_password}';

CREATE TABLE `mailserver`.`virtual_domains` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `mailserver`.`virtual_users` (
  `email` VARCHAR(100) NOT NULL,
  `domain_id` INT(11) NOT NULL,
  `password` VARCHAR(106) NOT NULL,
  PRIMARY KEY (`email`),
  FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `mailserver`.`virtual_aliases` (
  `source` VARCHAR(100) NOT NULL,
  `domain_id` INT(11) NOT NULL,
  `destination` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`source`),
  FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
"""

CHECK_DOMAIN_SQL = """
SELECT id FROM `mailserver`.`virtual_domains`
WHERE name = '{domain}'
"""

ADD_DOMAIN_SQL = """
INSERT INTO `mailserver`.`virtual_domains` (`name`)
VALUES ('{domain}');
"""

ADD_USER_SQL = """
INSERT INTO `mailserver`.`virtual_users`
  (`email`, `domain_id`, `password`)
VALUES
  ('{email}', '{domain_id}', 
  ENCRYPT('{user_password}', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))))
"""

ADD_ALIAS_SQL = """
INSERT INTO `mailserver`.`virtual_aliases`
  (`domain_id`, `source`, `destination`)
VALUES
  ('{domain_id}', '{source}', '{destination}');
"""

CHANGE_PASSWORD_SQL = """
UPDATE `mailserver`.`virtual_users`
SET password = ENCRYPT('{user_password}', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))) 
WHERE email = '{email}';
"""

def parse_arguments():
    parser = argparse.ArgumentParser()
    
    subparsers = parser.add_subparsers(dest='subcommand', help='Sub command')
    subparsers.add_parser('initdb', help='Initialze the mailaccounts database')
    
    adduser = subparsers.add_parser('adduser', help='Add a new mail user')
    adduser.add_argument('email', metavar='EMAIL', help='The email address')
    
    changepass = subparsers.add_parser(
        'changepassword', 
        help='Change the password of a mail user')
    
    changepass.add_argument('email', metavar='EMAIL', help='The email address')
    
    subparsers.add_parser('removeuser', help='Remove a mail user')
    
    addalias = subparsers.add_parser('addalias', help='Add a new mail alias')
    addalias.add_argument('source', metavar='SOURCE', 
                          help='The source email address (alias)')
    addalias.add_argument('destination', metavar='DEST', 
                          help='The destination email address')
    
    subparsers.add_parser('removealias', help='Remove a mail user')
    
    subparsers.required = True
    return parser.parse_args()

def execute_query(query):
    output = subprocess.check_output([
            'mysql', '--execute', '%s' % query]).decode('utf-8')
    return output

def initdb():
    mailserver_password = getpass.getpass('Enter new password for the mailserver user: ')
    query = INITDB_SQL.format(mailserver_password=mailserver_password)
    execute_query(query)

def get_domain_id(domain):
    result = execute_query(CHECK_DOMAIN_SQL.format(domain=domain))
    if len(result.split()) == 2:
        return result.split()[1]

def get_and_add_domain(email):
    assert email.count('@') == 1, 'Email address is not correct!'
    domain = email.split('@')[1]
    
    domain_id = get_domain_id(domain)
    
    if not domain_id:
        execute_query(ADD_DOMAIN_SQL.format(domain=domain))
        domain_id = get_domain_id(domain) 
        print('Added domain %s' % domain)    

    return domain_id

def adduser(email):
    domain_id = get_and_add_domain(email)
    user_password = getpass.getpass('Enter new password for the mail user: ')
   
    execute_query(ADD_USER_SQL.format(email=email,
                                      domain_id=domain_id,
                                      user_password=user_password))
    
    print('Added user %s' % email)

def addalias(source, destination):
    domain_id = get_and_add_domain(source)
    execute_query(ADD_ALIAS_SQL.format(source=source,
                                       domain_id=domain_id,
                                       destination=destination))
    print('Added alias %s with destination %s' % (source, destination))

def changepass(email):
    user_password = getpass.getpass('Enter new password for the mail user: ')
    
    execute_query(CHANGE_PASSWORD_SQL.format(email=email, 
                                             user_password=user_password))
    print('Changed password of user %s' % email)

def main():
    args = parse_arguments()
    subcommand = args.subcommand
    if subcommand == 'initdb':
        initdb()
    elif subcommand == 'adduser':
        adduser(args.email)
    elif subcommand == 'addalias':
        addalias(args.source, args.destination)
    elif subcommand == 'changepassword':
        changepass(args.email)
    elif subcommand in ('removeuser','removealias'):
        raise NotImplementedError('%s is not yet implemented' % subcommand)
    
if __name__ == '__main__':
    main()