import pandas as pd
import numpy as np
import streamlit as st
import psycopg2
from sqlalchemy import create_engine #Paquetería para subir datos a una base de datos
from pandasql import sqldf
import json

#Conexión a la DB usando st.secrets

def connection_db():
    conn = psycopg2.connect(**{
        "host": "blau-production.cvcvpaamxkwo.us-east-2.rds.amazonaws.com",
        "port": 5432,
        "dbname": "rme_prod",
        "user": "3P_dashboards",
        "password": "WoUYGWzI6J8IgqHCNDjLiifHwQvSdRj"
    })
    return conn

def get_event(cliente_reciclador):

    conn = connection_db()

    q1 = f"""
    SELECT r.id as id_servicio
    ,eu.business_name || ' Suc: ' || s.nombre_sucursal || ' | ' || ' Ruta: ' || cr.name || ' - ' || d.name || ' | ' || ' Estatus: ' || rs.description as title
    ,r.id || ' ' || eu.business_name || ' Suc: ' || s.nombre_sucursal || ' | ' || ' Ruta: ' || cr.name || ' - ' || d.name || ' | ' || ' Estatus: ' || rs.description as extendedProps
    ,cr.name AS ruta
    ,eu.business_name as cliente
    ,s.nombre_sucursal as sucursal
	,d.name AS chofer
    ,rs.description as estatus
    ,cr.name || ' - ' || d.name as ruta_chofer
	,t.name as truck_name
    ,CASE WHEN r.status < 4 THEN '#F1C40F' --'Pendiente'
		 WHEN r.status = 4 THEN '#2ECC71' --'Completada'
		 WHEN r.status = 5 THEN '#E74C3C' --'Cancelada'
		 WHEN r.status > 5 THEN '#E67E22' --'Servicio con incidencia'
	     ELSE NULL END AS color
    ,rs.description
    ,r.date as start --fecha
    ,r.date as end
    ,cr.id as resourceId --ruta_id
    ,r.id_cliente_reciclador
    FROM recollections r 
    left join route_status rs on r.status = rs.id
    LEFT JOIN sucursales s 
    ON s.id = r.id_sucursal 
    LEFT JOIN end_users eu 
    ON eu.id = s.id_end_user 
    LEFT JOIN cat_route cr 
    ON r.id_cat_route = cr.id
    left join trucks t 
    on t.id = cr.id_truck
    LEFT JOIN drivers d 
    ON d.id = r.id_driver 
    WHERE r.id_cliente_reciclador = '{cliente_reciclador}'
    AND r.enabled 
    AND r.id_cat_route NOTNULL
    AND r.date BETWEEN current_date -7 AND current_date +3  
    """
    df0 = pd.read_sql_query(q1, conn)
    df0['start'] = pd.to_datetime(df0['start']).dt.strftime('%Y-%m-%d')
    df0['end'] = pd.to_datetime(df0['end']).dt.strftime('%Y-%m-%d')
    #df1 = df0.to_json(orient='records', date_format='iso')
    conn.close()
    return df0

#print(get_event())
#print(get_resources())

#a=get_event()

#b=a.to_json(orient='records')
#c= decoder.raw_decode(b)

#pprint.pprint(b)
#print(b)