# -*- coding: ascii -*-
from os import system, mkdir, listdir, walk, stat, remove, path
from shutil import rmtree
from zipfile import ZipFile, ZIP_DEFLATED
from datetime import datetime
from smtplib import SMTP
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
import pymysql


def mysql_schemas(Servidor, Sqluser, Sqlpass):

    db = pymysql.connect(Servidor, Sqluser, Sqlpass)

    cursor = db.cursor()

    cursor.execute('show databases')

    schemas = cursor.fetchall()

    schemas = [s[0] for s in schemas]

    schemas.remove('information_schema')

    return schemas


def mysql_dump(Pastabkp, Pastatmp, Schemas, Sqldump, Servidor, Sqluser, Sqlpass):

    mkdir(Pastabkp + Pastatmp)

    log = []

    for schema in Schemas:

        hr = (datetime.now().strftime('%d%m%Y-%H%M%S'))

        arg = (Sqldump + ' -h ' + Servidor + ' -u ' + Sqluser + ' -p' + Sqlpass + ' --single-transaction=true ' +
            schema + ' > ' + Pastabkp + Pastatmp + '/' + schema + '-' + hr + '.sql')

        stat = str(system(arg))

        if stat == '0':
            stat = ('OK')
        else: stat = ('ERRO')

        status = (schema, stat)

        log.append(status)

    return log


def zipar(Pastazip, Pastatmp, Pastabkp):

    arquivo_zip = (Pastazip + Pastatmp)

    zips = ZipFile(arquivo_zip + '.zip', 'w', allowZip64=True, compression=ZIP_DEFLATED)

    for folder, subfolders, files in walk(Pastabkp + Pastatmp):
        for file in files:
            zips.write(path.join(folder, file), path.relpath(path.join(folder, file), Pastabkp + Pastatmp))


def email(Usuario, Senha, Smtp, Porta, Para, Servidor, log, Inicio_bkp, Final_bkp, Pastazip, Pastatmp):

    caminho = Pastazip+Pastatmp+'.zip'

    Schemas = str(log)

    Assunto = str('['+Servidor+'] '+'Backup do banco de dados')

    if Usuario == '' or Senha == '' or Smtp == '' or Porta == '' or Para == '':
        quit()
    else:
        msg = MIMEMultipart('alternative')
        msg['Subject'] = Assunto
        msg['From'] = Usuario
        msg['To'] = Para
        corpo = str('\nServidor: ' + Servidor + '\n''\nSchemas: ' + Schemas + '\n''\nInicio: ' +
                    Inicio_bkp + '\nTermino: ' + Final_bkp + '\n''\nPath: '+caminho)

        msg.attach(MIMEText(corpo, 'plain'))
        mail = SMTP(Smtp, Porta)
        mail.login(Usuario, Senha)
        mail.sendmail(Usuario, Para, msg.as_string())
        mail.quit()


def retencao(Caminho_pasta, Dias):
    if Dias > 0:

        data_agora_form = datetime.now().strftime('%d%m%Y')
        data_agora_form_int = datetime.now().strptime(data_agora_form, '%d%m%Y')

        lista = listdir(Caminho_pasta)
        for l in lista:
            if l.endswith('.zip'):
                info = stat(Caminho_pasta + l)
                tstamp_mod = info.st_mtime
                data_mod = datetime.fromtimestamp(tstamp_mod)
                data_mod_form = data_mod.strftime('%d%m%Y')
                data_mod_form_int = data_mod.strptime(data_mod_form, '%d%m%Y')
                res = abs((data_agora_form_int - data_mod_form_int).days)
                if res > Dias:
                    remove(Caminho_pasta + l)


servidor = 'servidor'
sql_user = 'usuario'
sql_pass = 'senha'

pastabkp = 'C:/BACKUP/'
pastazip = 'C:/BACKUP/BD/'
sqldump = 'C:/BACKUP/BD/mysqldump.exe'
nomearq = 'SQL-BKP'
reter = 7

email_usuario = 'backup@teste.com.br'
email_senha = 'teste'
email_smtp = 'smtp.teste.com.br'
email_porta = 587
email_para = 'backup@teste.com.br'

hora = (datetime.now().strftime('%H:%M'))
pastatmp = (datetime.now().strftime(nomearq + '-%d%m%Y-%H%M%S'))


# Registra horario de inicio do backup.
inicio_bkp = (datetime.now().strftime('%d/%m/%Y %H:%M:%S'))

# Apaga backups antigos.
retencao(pastazip, reter)

# Lista schemas do banco de dados
schemas = mysql_schemas(servidor, sql_user, sql_pass)

# Gera os dumps dos schemas informados.
log = mysql_dump(pastabkp, pastatmp, schemas, sqldump, servidor, sql_user, sql_pass)

# Gera arquivo zip com os dumps.
zipar(pastazip, pastatmp, pastabkp)

# Remove a pasta.
rmtree(pastabkp + pastatmp)

# Registra hora final do backup.
final_bkp = (datetime.now().strftime('%d/%m/%Y %H:%M:%S'))

# Envia o email.
email(email_usuario, email_senha, email_smtp, email_porta,email_para,
      servidor, log, inicio_bkp, final_bkp, pastazip, pastatmp)