import os
import os.path
import re
import shutil
import socket
import subprocess
import sys

# bootstrap imports
import string
import random
import MySQLdb

from ConfigParser import ConfigParser

from lwdbadmin.util import *

LOGS = "logs"
HOSTFILE = "host"
BASEDIR = "/var/lib/mysql"
BACKUPDIR = "/var/lib/mysql_backup"
CONFIGDIR = "/var/lib/mysql/config"
SYSTEMD_SERVICE = "/etc/systemd/system/mysql.service"

def mysqlVersion():
    p = subprocess.Popen(["mysql","-V"], stdout=subprocess.PIPE)
    version = p.communicate()[0]

    return version.split(' ')[5]

def create(name, dataset, datasetbackup, serviceip):
    # mount the dataset and create the structure
    nfs(dataset, "/media", "nolock")

    # create paths. UID 27: mysql and 260: lwdbadmin
    basedir = os.path.join("/media", name)
    config = os.path.join(basedir, "config")
    data = os.path.join(basedir, "data")
    logs = os.path.join(basedir, "logs")
    os.mkdir(basedir, 0755)
    os.chown(basedir, 27, 27)
    os.mkdir(config, 0750)
    os.chown(config, 27, 27)
    os.mkdir(data, 0750)
    os.chown(data, 27, 27)
    os.mkdir(logs, 0770)
    os.chown(logs, 27, 260)

    version = mysqlVersion()
    if  "5.6." in version:
        open("/etc/default/locaweb/description/mysql56", "w").close()
        for f in ["1_my_lw_host.cnf56", "2_my_lw_service.cnf56", "3_my_lw_forced.cnf56", "percona_audit.cnf"]:
            src = os.path.join("/usr/share/lwdbadmin/mysql", f)
            shutil.copy(src, config)
            os.chown(os.path.join(config, f), 27, 27)
        if os.path.exists("/etc/my.cnf"):
            subprocess.check_call(["rm", "-f", "/etc/my.cnf"])
            shutil.copy("/usr/share/lwdbadmin/mysql/my.cnf56","/etc/my.cnf")
    else:
    # copy templates
        for f in ["1_my_lw_host.cnf", "2_my_lw_service.cnf", "3_my_lw_forced.cnf", "percona_audit.cnf"]:
            src = os.path.join("/usr/share/lwdbadmin/mysql", f)
            shutil.copy(src, config)
            os.chown(os.path.join(config, f), 27, 27)
        if os.path.exists("/etc/my.cnf"):
            subprocess.check_call(["rm", "-f", "/etc/my.cnf"]) 
            shutil.copy("/usr/share/lwdbadmin/mysql/my.cnf","/etc/")

    if 'myresellerwin' in name:
        f = open(os.path.join(config,"2_my_lw_service.cnf"), "a+")
        f.write("\n#Revenda Windows")
        f.write("\nlower_case_table_names=1\n")
        f.close()

    # create the service file
    setsrvips(config, serviceip)

    umount("/media")

    # mount the datasetbackup and create the structure
    nfs(datasetbackup, "/media", "nolock")

    # create paths. UID 26: postgres and 260: lwdbadmin
    backupdir = os.path.join("/media", name)
    os.mkdir(backupdir, 0750)
    os.chown(backupdir, 27, 27)

    if not os.path.exists(BACKUPDIR):
        os.mkdir(BACKUPDIR, 0755)

    umount("/media")


def trylock(lockfile, attach=True):
    machine = socket.gethostname()

    try:
        with open(lockfile) as f:
            host = f.read().strip()
        if attach or host != machine:
            raise Exception("Error: this service is hosted by " + host)
    except IOError, e:
        if e.errno != 2:
            raise e

    # lock now
    open(lockfile, 'w').write(socket.gethostname())


def attach(name, dataset, datasetbackup, force=False, created=False):
    srcddir = os.path.join(dataset, name)
    srcbdir = os.path.join(datasetbackup, name)
    nfs(srcddir, BASEDIR, NFSFLAGS)
    nfs(srcbdir, BACKUPDIR, NFSFLAGS)

    #ajuste localtime
    localtime()

    #disable selinux
    subprocess.check_call(["setenforce", "0"])

    if not force:
        try:
            lockfile = os.path.join(CONFIGDIR, HOSTFILE)
            trylock(lockfile, True)
            os.chown(lockfile, 27, 27)
        except Exception, e:
            umount(BASEDIR)
            raise e

    # get ips from service.conf file and configure
    serviceip_prefix = getsrvips(CONFIGDIR)
    # This variable is used in templates. Do not touch it
    serviceip = serviceip_prefix.split("/")[0]
    
    # TODO: get network from cegonha

    # get current ips from adm/service
    # same for backup
    for ip, conname in [(serviceip_prefix, "adm/service")]:
        subprocess.check_call(["nmcli", "connection", "modify", conname, "+ipv4.addresses", ip])

    #nfs(os.path.join(dataset, name, LOGS), "/var/log/lwdbadmin", NFSFLAGS)
    # mounts and fstab
    # do not care to mess this file, hosts should be expendable
    with open("/etc/fstab", "a+") as fstab:
        fstab.write("\n{0}	{1}	nfs	{2}	0 0".format(srcddir, BASEDIR, NFSFLAGS))
        fstab.write("\n{0}	{1}	nfs	{2}	0 0".format(srcbdir, BACKUPDIR, NFSFLAGS))
        fstab.write("\n{0}	{1}	nfs	{2}	0 0".format(datasetbackup+'/.zfs/snapshot', '/snapshot', 'ro'))
        fstab.write("\n{0}	{1}	{2}	{3}     0 0\n".format('tmpfs', '/var/lib/mysql/tmpdir', 'tmpfs','size=6G'))

    if not os.path.exists('/var/lib/mysql/tmpdir'):
        os.mkdir('/var/lib/mysql/tmpdir', 0777)
    if not os.path.exists('/snapshot'):
        os.mkdir('/snapshot', 0755)
    subprocess.check_call(["mount", "/snapshot"])
    subprocess.check_call(["mount", "/var/lib/mysql/tmpdir"])

    if not os.path.exists(SYSTEMD_SERVICE):
        with open(SYSTEMD_SERVICE, "w") as f:
            f.write(".include /lib/systemd/system/mysqld.service\n")
            f.write("\n[Unit]\n")
            f.write("RequiresMountsFor={0}\n".format(BASEDIR))
    #        f.write("\n[Service]\n")
    #        f.write("ExecStartPre=/bin/lwmysrvadm test-lock {0}\n".format(name))


    # start and enable the service
    #subprocess.check_call(["systemctl", "enable", "mysqld"])
    subprocess.check_call(["systemctl", "restart", "network"])
    try:
        _ = subprocess.check_output(["systemctl", "is-enabled", "firewalld"])
        subprocess.check_call(["firewall-cmd", "--add-service=mysql", "--permanent"])
        subprocess.check_call(["firewall-cmd", "--add-service=mysql"])
    except:
        pass

    monitdir = "/etc/locaweb/monitoring-{}".format(name)
    os.mkdir(monitdir)
    # very simple template engine!
    expandvariables = [
            ("mysql.yaml", os.path.join(monitdir, "mysql.yaml")),
            ("check_mk", "/etc/xinetd.d/check_mk-{}".format(name)),
    ]
    for src, dst in expandvariables:
        src = os.path.join("/usr/share/lwdbadmin/mysql", src)
        with open(src) as t, open(dst, "w") as w:
            for line in t:
                w.write(line.format(**locals()))
    shutil.copy("/usr/share/lwdbadmin/mysql/fs_mysql.yaml", os.path.join(monitdir, "fs_mysql.yaml"))
  
    #first inicialize  
    if created:
        # bootstrap
        version = mysqlVersion()
        if  "5.6." in version:
            bootstrap_56(name)
        else:
            _ = subprocess.check_call(["/usr/sbin/mysqld", "--initialize-insecure", "--user=mysql", "--datadir=/var/lib/mysql/data"])
            bootstrap_57(name)

        #cp /root/.my.cnf
        shutil.copy("/root/.my.cnf","/var/lib/mysql/data/.my.cnf")

	if "5.6." in version:
        	f = open("/var/lib/mysql/config/2_my_lw_service.cnf56", "a+")
	else:
		f = open("/var/lib/mysql/config/2_my_lw_service.cnf", "a+")
        f.write("\n#Percona Audit")
        f.write("\n!include /var/lib/mysql/config/percona_audit.cnf\n")
        f.close()

        subprocess.check_call(["systemctl", "stop", "mysqld"])
        if os.path.exists("/var/lib/mysql/data/audit.log"):
            os.unlink("/var/lib/mysql/data/audit.log")
        subprocess.check_call(["systemctl", "start", "mysqld"])

    if not force:
        subprocess.check_call(["systemctl", "start", "mysqld"])
    
        #cp /root/.my.cnf
        shutil.copy("/var/lib/mysql/data/.my.cnf", "/root/.my.cnf")

        #edite bash profile
        with open("/root/.bash_profile", "r+") as f:
            lines = f.readlines()
            lines = filter(lambda x: 'LW_MYSQL_USER' not in x, lines)
            f.seek(0)
            f.truncate(0)
            f.writelines(lines)
            lines = filter(lambda x: 'LW_MYSQL_PASSWORD' not in x, lines)
            f.seek(0)
            f.truncate(0)
            f.writelines(lines)

        conffile = "/var/lib/mysql/data/.my.cnf"
        profile = ConfigParser()
        profile.readfp(open(conffile))
        user = profile.get('client','user')
        password = profile.get('client','password')

        f = open("/root/.bash_profile", "a+")
        f.write("\nexport LW_MYSQL_USER={}".format(user))
        f.write("\nexport LW_MYSQL_PASSWORD={}\n".format(password.split('"')[1]))
        f.close()

        # Editando description
        for f in ["mysql", "sla"]:
            link = os.path.join("/etc/default/locaweb/description", f)
            open(link, "w").close()
        if 'myreseller' in name:
            open("/etc/default/locaweb/description/revenda", "w").close()
        if 'mysharedhost' in name:
            open("/etc/default/locaweb/description/shared", "w").close()

        #symbolic link
        mysqld_log = "/var/log/mysqld.log"
        lwdbadmin_log = "/var/log/lwdbadmin/lwdbadmin.log"
        log_lwdbadmin = "/var/lib/mysql/logs/lwdbadmin.log"

        for f in [mysqld_log, lwdbadmin_log]:
            if os.path.exists(f):
                os.unlink(f)

        if not os.path.exists(log_lwdbadmin):
            open(log_lwdbadmin, "w").close()
            os.chown(log_lwdbadmin,260,260)

        if not os.path.islink(mysqld_log):
            os.symlink("/var/lib/mysql/logs/mysqld.log", mysqld_log)
        if not os.path.islink(lwdbadmin_log):
            os.symlink(log_lwdbadmin, lwdbadmin_log)

def detach(name):
    try:
        subprocess.check_call(["systemctl", "disable", "mysqld"])
        os.unlink(SYSTEMD_SERVICE)
    except:
        pass
    
    try:
        os.unlink(os.path.join(CONFIGDIR, HOSTFILE))
    except:
        pass

    subprocess.check_call(["systemctl", "stop", "mysqld"])
    serviceip = getsrvips(CONFIGDIR)

    for ip, conname in [(serviceip, "adm/service")]:
        subprocess.check_call(["nmcli", "connection", "modify", conname, "-ipv4.addresses", ip])

    # umount storage
    for f in ["/var/lib/mysql/tmpdir",BASEDIR,BACKUPDIR,"/snapshot"]:
        if os.path.ismount(f):
            umount(f)

    # remove mountpoints from fstab
    with open("/etc/fstab", "r+") as f:
        lines = f.readlines()
        lines = filter(lambda x: name not in x, lines)
        f.seek(0)
        f.truncate(0)
        f.writelines(lines)
        lines = filter(lambda x: 'snapshot' not in x, lines)
        f.seek(0)
        f.truncate(0)
        f.writelines(lines)
        lines = filter(lambda x: 'tmpdir' not in x, lines)
        f.seek(0)
        f.truncate(0)
        f.writelines(lines)

    #remove user and password from bash_profile
    with open("/root/.bash_profile", "r+") as f:
        lines = f.readlines()
        lines = filter(lambda x: 'LW_MYSQL_USER' not in x, lines)
        f.seek(0)
        f.truncate(0)
        f.writelines(lines)
        lines = filter(lambda x: 'LW_MYSQL_PASSWORD' not in x, lines)
        f.seek(0)
        f.truncate(0)
        f.writelines(lines)
        lines = filter(lambda x: 'LW_PEASANT_PASSWORD' not in x, lines)
        f.seek(0)
        f.truncate(0)
        f.writelines(lines)

    subprocess.check_call(["systemctl", "restart", "network"])
    monitdir = "/etc/locaweb/monitoring-{}".format(name)
    os.unlink(os.path.join(monitdir, "mysql.yaml"))
    os.unlink(os.path.join(monitdir, "fs_mysql.yaml"))
    os.rmdir(monitdir)
    os.unlink("/etc/xinetd.d/check_mk-{}".format(name))
    os.unlink("/root/.my.cnf")

    # Editando description
    for f in ["mysql", "sla", "shared", "revenda","shared_mysql"]:
        link = os.path.join("/etc/default/locaweb/description", f)
        if os.path.exists(link):
            os.unlink(link)

    # Unlink nos logs
    for f in ["/var/log/mysqld.log","/var/log/lwdbadmin/lwdbadmin.log"]:
        if os.path.exists(f):
            os.unlink(f)

def getpkglibdir(f="/etc/locaweb/lwdbadmin/mysql.cnf"):
    cfg = ConfigParser()
    cfg.readfp(open(f))
    return cfg.get('MySQL', 'pkglibdir')


def getservices(fname="/etc/fstab"):
    ret = []
    r = re.compile("^bdfs-\d{4,4}\.fs\.locaweb\.com\.br:/storage/BDFS-\d{4,4}/(.*)\s+(.*)\s+nfs")
    with open(fname) as f:
        for l in f:
            m = r.match(l)
            if m:
                ret.append((m.group(1), m.group(2)))
    return ret


def setsrvips(directory, serviceip):
    with open(os.path.join(directory, "service.conf"), "w+") as f:
        f.write("[mysqld]\n".format(serviceip))
        f.write("# SERVICEIP: {0}\n".format(serviceip))
        serviceip = serviceip.split('/')[0]
        f.write("bind_address = {0}".format(serviceip))


def getsrvips(directory):
    serviceip = None
    with open(os.path.join(CONFIGDIR, "service.conf")) as f:
        for line in f:
            m = re.match(".*#.*SERVICEIP: (.*)", line)
            if m:
                serviceip = m.group(1)
    return serviceip

def bootstrap_57(name):

    def r(choices, n):
        return ''.join(random.SystemRandom().choice(choices) for _ in range(n))

    user_list = string.ascii_lowercase + string.digits
    pwd_list = string.ascii_letters + string.digits + "!@%*-_+.,"

    user = r(user_list, 15)
    password = r(pwd_list, 18)
    peasant_password = r(pwd_list, 18)

    #script
    script = """TRUNCATE TABLE mysql.proxies_priv;
DELETE FROM mysql.user WHERE user='root' AND host='localhost';

INSERT INTO mysql.user (Host, User, authentication_string, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Create_user_priv, Event_priv, Trigger_priv, Create_tablespace_priv,ssl_type,ssl_cipher,x509_issuer,x509_subject) VALUES ('localhost','{user}',PASSWORD('{password}'), 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','');

CREATE DATABASE IF NOT EXISTS teste;
CREATE TABLE IF NOT EXISTS teste.teste(teste VARCHAR(50) NOT NULL);
TRUNCATE TABLE teste.teste;
INSERT INTO teste.teste values ('Locaweb');
INSERT INTO mysql.user (Host, User, authentication_string,ssl_type,ssl_cipher,x509_issuer,x509_subject) VALUES ('%', 'teste', '*1A2FA58B8ADDA83A100686FB4FACC2AFF1316FEA','','','','');
INSERT INTO mysql.db VALUES ('%', 'teste', 'teste', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');

INSTALL PLUGIN audit_log SONAME 'audit_log.so';

DROP EVENT IF EXISTS mysql.rotate_log_tables;
DELIMITER @
create event mysql.rotate_log_tables
on schedule
every 1 month
do begin
    drop table if exists mysql.general_log2;
    drop table if exists mysql.general_log_old;
    drop table if exists mysql.slow_log2;
    drop table if exists mysql.slow_log_old;
    create table mysql.general_log2 LIKE mysql.general_log;
    create table mysql.slow_log2 LIKE mysql.slow_log;
    rename table mysql.general_log TO mysql.general_log_old, mysql.general_log2 TO mysql.general_log;
    rename table mysql.slow_log TO mysql.slow_log_old, mysql.slow_log2 TO mysql.slow_log;
end
@
DELIMITER ;

""".format(**locals())

    #Editar user root
    p = subprocess.Popen(["mysqld", "--bootstrap", "--user=mysql"], stdin=subprocess.PIPE)
    _ = p.communicate(script)


    with open("/root/.my.cnf", "w") as f:
        f.write("[client]\n")
        f.write("user={}\n".format(user))
        f.write('password="{}"\n'.format(password))

    f = open("/root/.bash_profile", "a+")
    f.write("\nexport LW_MYSQL_USER={}".format(user))
    f.write("\nexport LW_MYSQL_PASSWORD={}\n".format(password))
    if 'myreseller' in name:
        f.write("export LW_PEASANT_PASSWORD={}\n".format(peasant_password))
    f.close()

def bootstrap_56(name):
    def r(choices, n):
        return ''.join(random.SystemRandom().choice(choices) for _ in range(n))

    user_list = string.ascii_lowercase + string.digits
    pwd_list = string.ascii_letters + string.digits + "!@%*-_+.,"

    user = r(user_list, 15)
    password = r(pwd_list, 18)

    script = """DROP DATABASE IF EXISTS test;
TRUNCATE TABLE mysql.user;
TRUNCATE TABLE mysql.db;
TRUNCATE TABLE mysql.proxies_priv;
INSERT INTO mysql.user (Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Create_user_priv, Event_priv, Trigger_priv, Create_tablespace_priv) VALUES ('localhost','{user}',PASSWORD('{password}'), 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

INSTALL PLUGIN audit_log SONAME 'audit_log.so';

CREATE DATABASE IF NOT EXISTS teste;
CREATE TABLE IF NOT EXISTS teste.teste(teste VARCHAR(50) NOT NULL);
TRUNCATE TABLE teste.teste;
INSERT INTO teste.teste values ('Locaweb');
INSERT INTO mysql.user (Host, User, Password) VALUES ('%', 'teste', '*1A2FA58B8ADDA83A100686FB4FACC2AFF1316FEA');
INSERT INTO mysql.db VALUES ('%', 'teste', 'teste', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');

DROP EVENT IF EXISTS mysql.rotate_log_tables;
DELIMITER @
create event mysql.rotate_log_tables
on schedule
every 1 month
do begin
    drop table if exists mysql.general_log2;
    drop table if exists mysql.general_log_old;
    drop table if exists mysql.slow_log2;
    drop table if exists mysql.slow_log_old;
    create table mysql.general_log2 LIKE mysql.general_log;
    create table mysql.slow_log2 LIKE mysql.slow_log;
    rename table mysql.general_log TO mysql.general_log_old, mysql.general_log2 TO mysql.general_log;
    rename table mysql.slow_log TO mysql.slow_log_old, mysql.slow_log2 TO mysql.slow_log;
end
@
DELIMITER ;

FLUSH PRIVILEGES;
""".format(**locals())


    p = subprocess.Popen(["mysqld", "--bootstrap", "--user=mysql"], stdin=subprocess.PIPE)
    _ = p.communicate(script)


    with open("/root/.my.cnf", "w") as f:
        f.write("[client]\n")
        f.write("user={}\n".format(user))
        f.write('password="{}"\n'.format(password))

    _ = subprocess.check_call(["systemctl", "start", "mysqld"])
    _ = subprocess.check_call(["mysql", "-u", "root", "-p", "-e", script])
    _ = subprocess.check_call(["systemctl", "restart", "mysqld"])


    f = open("/root/.bash_profile", "a+")
    f.write("\nexport LW_MYSQL_USER={}".format(user))
    f.write("\nexport LW_MYSQL_PASSWORD={}\n".format(password))
    if 'myreseller' in name:
        f.write("export LW_PEASANT_PASSWORD={}\n".format(peasant_password))
    f.close()