# -*- coding: utf-8 -*-
import csv
import datetime
import os
import sys
from decouple import config

import pymysql
import pymysql.cursors
from sqlalchemy import create_engine


def conexao_local_remoto(conexao=None):
    if conexao == "local":
        connection = pymysql.connect(
            host=config("HOST_LOCAL"),
            user=config("USER_LOCAL_MYSQL"),
            password=config("PASSWORD_LOCAL_MYSQL"),
            db=config("DB_LOCAL_MYSQL"),
            charset="utf8mb4",
            cursorclass=pymysql.cursors.DictCursor,
        )
    else:
        connection = pymysql.connect(
            host=config("HOST_LUCIANO"),
            user=config("USER_LUCIANO"),
            password=config("PASSWORD_LUCIANO"),
            db=config("DB_LUCIANO"),
            charset="utf8mb4",
            cursorclass=pymysql.cursors.DictCursor,
        )
    return connection


def conexao(connection, tbl=None, nome_conexao=None):
    if nome_conexao == None:
        db = config("DB_LUCIANO")
    else:
        db = config("DB_LOCAL_MYSQL")
    if tbl:
        sql = f"SELECT * FROM {tabela(tbl)}"
    else:
        sql = f"SELECT * FROM information_schema.tables WHERE table_schema = '{db}'"
    try:
        with connection.cursor() as cursor:
            # Read a single record
            # sql = u"SELECT {} FROM {}".format(campos(),tabela())
            # sql = "SELECT * FROM `profissionais`" # WHERE `email`=%s"
            # sql = "SELECT * FROM `agendas` WHERE `profissional_ID`=%s"
            # cursor.execute(sql, (216))
            cursor.execute(sql)
            listar_dados_banco = cursor.fetchall()
            return listar_dados_banco
    finally:
        connection.close()


def nomeTabelas(connection, nome_conexao):
    nome = conexao(connection, nome_conexao)
    print(nome)
    for n in nome:
        print(n["TABLE_NAME"])


def tabela(nome):
    print(f"função: Tabela.: {nome}")
    return nome


def cabecalho(listar_dados_banco):
    corpo = []
    for dados in listar_dados_banco:
        for key, value in dados.items():
            corpo.append(f"{key} : {value}")
    return corpo


def consultar(connection, tbl=None):
    listar_dados = conexao(connection, tbl)
    filename = f'{tbl}.csv'
    homepath = os.path.expanduser(os.getenv("USERPROFILE"))
    desktoppath = "Desktop"
    path_to_desktop = os.path.join(homepath, desktoppath)
    filename_desktop = os.path.join(path_to_desktop, filename)
    with open(filename_desktop, "w", newline="") as _f:
        headers = [r for r in listar_dados[0]]
        writer = csv.DictWriter(_f, fieldnames=headers)
        writer.writeheader()
        for dict_info in listar_dados:
            info_in_dict = {}
            for info in dict_info.items():
                info_in_dict[info[0]] = info[1]
            writer.writerow(info_in_dict)
        # _f.write("\n")
        # for dict_info in listar_dados:
        #     for key, value in dict_info.items():
        #         if isinstance(value, str) and len(value) == 0:
        #             value = "-"
        #         _f.write(f"{value},")
        #         print(f"{value}")
        #     break
    return


def inserir(table, data):
    # mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]
    connect = f"mysql+pymysql://{config('USER_LOCAL_MYSQL')}:{config('PASSWORD_LOCAL_MYSQL')}" \
              f"@localhost/{config('DB_LOCAL_MYSQL')}"
    engine = create_engine(connect)  # connect to server
    sql = "SELECT * FROM django_migrations"
    try:
        resultado = engine.execute(sql)
        print(resultado)
    except pymysql.err.ProgrammingError as e:
        print(e)


if __name__ == "__main__":
    try:
        nome_conexao = sys.argv[1]
        print(nome_conexao)
        connection = conexao_local_remoto(sys.argv[1])
        consultar(connection, 'wp_posts')
        # inserir("d", {})
    except Exception as err:
        line = "*" * 80
        print(
            f"{line}\n"
            f"Não esqueça de colocar dessa forma: {(__file__)} <LOCAL ou LUCIANO> <TABELA>"
            f"\n{line}"
        )
        connection = conexao_local_remoto()
        consultar(connection, 'wp_posts')
        # print(f"Error.: {err}")
        # sys.exit(0)
