import pandas as pd
from data.solicitar_data import connection_db


def obtener_cliente_sucursal(id_reciclador, conn):
    q = f"""
    SELECT eu.business_name AS cliente
        , s.nombre_sucursal AS sucursal
        , s.id AS id_sucursal
    FROM end_users eu 
    LEFT JOIN sucursales s 
    ON eu.id = s.id_end_user 
    WHERE eu.id_cliente_reciclador = '{id_reciclador}'
    AND eu.enabled 
    AND s.enabled 
    """
    df = pd.read_sql_query(q, conn)

    return df

def obtener_chofer(id_reciclador, conn):
    q1 = f"""
    SELECT name AS chofer
        , id AS id_chofer
    FROM drivers d 
    WHERE id_cliente_reciclador = '{id_reciclador}'
    AND NOT disabled
    """

    df = pd.read_sql_query(q1, conn)
    return df

def obtener_camiones(id_reciclador, conn):
    q2 = f"""
    SELECT name AS camion
        , id AS id_truck
    FROM trucks t 
    WHERE id_cliente_reciclador = '{id_reciclador}'
    AND enabled
    """

    df = pd.read_sql_query(q2, conn)
    return df

def obtener_rutas(id_reciclador, conn):
    q3 = f"""
    SELECT cr."name" AS ruta
        ,cr.id AS id_cat
    FROM cat_route cr 
    WHERE id_cliente_reciclador = '{id_reciclador}'
    AND status
    """

    df = pd.read_sql_query(q3, conn)
    return df

def obtener_recolecciones(id_reciclador, conn):
    q4 = f"""
    SELECT r.id AS id_recollection 
        , s.id AS id_sucursal
        , eu.business_name AS cliente 
        , s.nombre_sucursal AS sucursal
        , d.id AS id_driver
        , d."name" AS chofer
        , t.id AS id_camion
        , t."name" AS camion
        , cr.id AS id_cat
        , cr."name" AS ruta
    FROM recollections r 
    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 cr.id = r.id_cat_route 
    LEFT JOIN drivers d
    ON d.id = cr.id_driver 
    LEFT JOIN trucks t 
    ON t.id = cr.id_truck 
    WHERE r.id_cliente_reciclador = '{id_reciclador}'
    AND r.id_cat_route NOTNULL 
    AND date = current_date + 1
    AND r.enabled 
    """
    df = pd.read_sql(q4, conn)

    return df


def recollections_existen(list_suc: list, cat_route:int, id_reciclaor:int, conn, fecha:str):
    """
    Esta función regresa una lista con las ids de las sucursales que no tienen recolecciones
    y los id_recollections de las sucurales que si tienen recolecciones
    """
    if len(list_suc) == 1:
        tupla_suc = tuple([list_suc[0], list_suc[0]])
    elif len(list_suc) > 1:
        tupla_suc = tuple(list_suc)
    else:
        tupla_suc = tuple()


    q = f"""
    SELECT id AS id_recollections 
        , id_sucursal
        ,date
        ,id_cat_route
    FROM recollections r 
    WHERE id_cliente_reciclador = '{id_reciclaor}'
    AND date = '{fecha}'
    AND enabled
    AND id_sucursal IN {tupla_suc}
    AND (id_cat_route ISNULL OR id_cat_route = '{cat_route}')
    """

    df_database = pd.read_sql_query(q, conn)

    suc_recoll = df_database["id_sucursal"].to_list()
    suc_no_recoll = [suc for suc in list_suc if suc not in suc_recoll] #ids_suc que no tienen recolecciones

    recoll_ids = df_database

    return suc_no_recoll, recoll_ids


def contenedores(id_reciclador, conn):
    q = f"""
    SELECT id_sucursal
        , CASE WHEN cantidad = 0 THEN 1
            ELSE cantidad END AS cantidad
        , material
        , CASE WHEN tipo = '3m3' THEN 'Contenedor 3m3' 
            WHEN tipo = 'metros cubicos' THEN 'Contenedor 1m3' ELSE tipo END AS contenedor
    FROM (
        SELECT id_sucursal
            ,mode() within group (order by cantidad desc) AS cantidad
            ,mode() within group (order by material desc) as material
            ,mode() within group (order by tipo desc) AS tipo
        FROM (
            SELECT DISTINCT ON (r.id) r.id
                ,r.id_sucursal
                ,(UNNEST(rd.collected_containers) ->> 'amount') :: NUMERIC AS cantidad
                    ,UNNEST(rd.collected_containers) ->> 'material' AS material
                    ,unnest(rd.collected_containers) ->> 'type' AS tipo
                FROM recollections r 
                LEFT JOIN recollection_declarations rd 
                ON r.id = rd.id_recollection 
                WHERE r.id_cliente_reciclador = '{id_reciclador}'
                AND r.date BETWEEN current_date - 14 AND current_date
                AND r.status = 4
            ) a
            GROUP BY id_sucursal
    ) b
    """
    df = pd.read_sql_query(q, conn)
    return df