# -*- coding: utf-8 -*-
"""
Created on Fri May 24 11:56:58 2019

@author: Aback
"""


import pandas as pd
from datetime import datetime
import numpy as np
from mailer_quant import Mailer
import os
from datetime import date
from sqlalchemy import create_engine



def leer_notas_dia_emp():
    url = """postgresql://ksrdupfcxccrwy:01d97d80f0d0d741e59199655fbc5c0650a138c2a104b1d3081d9370a5ed8055@ec2-52-21-193-223.compute-1.amazonaws.com:5432/d7cdgfuquvol04"""
    engine = create_engine(url, echo=False)
    today = date.today()
    query = """select * from  notas_analistas where Date_nota between
              '{Today} 00:00:00' and '{Today} 23:59:59' and Id_quant != 0""".format(Today = today)
    data = pd.read_sql_query(query, con=engine)
    data.columns = ["Analista", "Comentario", "Date", "Empresa", "ID_Quant",
                    "LV1", "Nota", "Pais", "Ticker Bloomberg",
                    "Tipo de Comentario"]
    data=data[["Analista", "Comentario", "Date", "Empresa","LV1", "Nota",
               "Pais"]]
    data.index = pd.to_datetime(data['Date']).dt.strftime('%d/%m/%Y')
    data.index.name = "Fecha"
    data = data.sort_index(ascending=False)
    return data


def leer_notas_dia_ind():
    url = """postgresql://ksrdupfcxccrwy:01d97d80f0d0d741e59199655fbc5c0650a138c2a104b1d3081d9370a5ed8055@ec2-52-21-193-223.compute-1.amazonaws.com:5432/d7cdgfuquvol04"""
    engine = create_engine(url, echo=False)
    today = date.today()
    query = """select * from  notas_analistas where Date_nota between
              '{Today} 00:00:00' and '{Today} 23:59:59'
               and Id_quant = 0""".format(Today=today)
    data = pd.read_sql_query(query, con=engine)
    data.columns = ["Analista", "Comentario", "Date", "Empresa", "ID_Quant",
                    "LV1", "Nota",
                    "Pais", "Ticker Bloomberg", "Tipo de Comentario"]
    data = data[["Analista", "Comentario", "Date", "Empresa", "LV1", "Nota",
                 "Pais"]]
    data.index = pd.to_datetime(data['Date']).dt.strftime('%d/%m/%Y')
    data.index.name = "Fecha"
    data = data.sort_index(ascending=False)
    return data


def leer_nota_anterior(empresa):
    url = """postgresql://ksrdupfcxccrwy:01d97d80f0d0d741e59199655fbc5c0650a138c2a104b1d3081d9370a5ed8055@ec2-52-21-193-223.compute-1.amazonaws.com:5432/d7cdgfuquvol04"""
    engine = create_engine(url, echo=False)
    today = date.today()
    query = """select nota from  notas_analistas where Date_nota
               < '{Today} 00:00:00' and Empresa = '{Empresa}'
               ORDER BY Date_nota desc""".format(Today=today,
                                                 Empresa=empresa)
    data = pd.read_sql_query(query, con=engine)
    nota_ant = data.head(1)
    return nota_ant


if __name__ == '__main__':
    data = leer_notas_dia_emp()
    data2 = leer_notas_dia_ind()
    tabla_empresas = data
    tabla_sectores = data2
    empresas = list(tabla_empresas["Empresa"])
    sectores = list(tabla_sectores["Empresa"])
    notas_anteriores = []
    notas_anteriores_sec = []
    for empresa in empresas:
        nota_ant = leer_nota_anterior(empresa)
        if len(nota_ant) > 0:
            notas_anteriores.append(nota_ant.iloc[0]["nota"])
        else:
            notas_anteriores.append(0)
    for sector in sectores:
        nota_ant = leer_nota_anterior(sector)
        if len(nota_ant) > 0:
            notas_anteriores_sec.append(nota_ant.iloc[0]["nota"])
        else:
            notas_anteriores_sec.append(0)
    tabla_empresas["Nota anterior"] = notas_anteriores
    tabla_sectores["Nota anterior"] = notas_anteriores_sec
    tabla_empresas["Fecha"] = date.today()
    tabla_sectores["Fecha"] = date.today()
    tabla_empresas = tabla_empresas[["Analista", "Empresa", "Nota anterior",
                                    "Nota", "Fecha", "LV1", "Pais"]]
    tabla_sectores = tabla_sectores[["Analista", "Empresa", "Nota anterior",
                                    "Nota", "Fecha", "LV1", "Pais"]]
    tabla_empresas.sort_values('Analista', inplace=True)
    tabla_sectores.sort_values('Analista', inplace=True)
    # print(tabla_empresas)
    # print(tabla_sectores)
    pd.set_option('display.max_colwidth', None)
    if len(tabla_empresas) > 0 or len(tabla_sectores) > 0:
        table1 = tabla_empresas.to_html(index_names=False, na_rep='-',
                                        col_space=10, index=False,
                                        justify='right')
        table2 = tabla_sectores.to_html(index_names=False, na_rep='-',
                                        col_space=10, index=False,
                                        justify='right')
        text = 'A continuación se presentan los últimos cambios de notas/comentarios por los analistas.'
        html = '<!doctype html>\n <html>\n <body>\n {}\n\n <p>Cambios en Empresas</p>\n\n {}\n\n <p>Cambios en Sectores</p>\n\n {}\n</body> </html>'.format(text, table1, table2)
        html = html.replace('nan%', '-')
        subject = 'Cambios notas/comentarios analistas'
        recipient = 'bullm@larrainvial.com'
        cc = ['bullm@larrainvial.com']
        mail = Mailer(subject, "", html, recipient)
        mail.send_message(["aback@larrainvial.com"])
                          # "cguzman@larrainvial.com",
                          # "ybanach@larrainvial.com"])
        pd.set_option('display.max_colwidth', 50)
    else:
        subject = 'Cambios notas/comentarios analistas'
        recipient = 'bullm@larrainvial.com'
        cc = ['bullm@larrainvial.com']
        mail = Mailer(subject, "",'<html>No hay cambios</html>', recipient)
        mail.send_message(["aback@larrainvial.com"])
                          # "cguzman@larrainvial.com",
                          # "ybanach@larrainvial.com"])
        print('No han habido cambios')