drop function busqueda_producto(varchar);

create function busqueda_producto(querystring character varying)
    returns TABLE(prod_afecto smallint, prec_vta_sugerido double precision, prod_lista4b boolean,tiprec_descripcion character varying, tipprod_codigo character, procodbar_codigo json, proest_descripcion character varying, prod_bolsa boolean, cond_codigo character, prod_fraccion smallint, procpi_valor double precision, prod_codigo character varying, proori_codigo smallint, priact_descripcion character varying, prod_tags_busqueda character varying, prod_descorta character varying, prod_otc smallint, lab_descorta character varying, prod_blister smallint, prec_vta_final double precision, prod_stock_fraccion smallint, proalc_unidad_n real, preing_cantidad smallint, prec_margen character varying, lab_descripcion character varying, prod_stock_cantidad smallint, pvvc_vtacero boolean, prtifr_codigo smallint, promos_descripcion json, flag_bolsa boolean, prec_costo_compra character varying, protip_codigo smallint, prod_descripcion character varying, preing_fraccion smallint, prom_solo_bcp boolean, similares json, complementarios json, lotes json)
    language sql
as
$$
SELECT prod_afecto,COALESCE(prec_vta_sugerido,0) as prec_vta_sugerido,prod_lista4b,TR.tiprec_descripcion,tipprod_codigo,
       json_agg(pcb.procodbar_codigo) as procodbar_codigo,
       pe.proest_descripcion, COALESCE(prod_bolsa, false) as prod_bolsa, cond_codigo, prod_fraccion, cpi.procpi_valor, producto.prod_codigo::varchar,promos.proori_codigo, pact.priact_descripcion, prod_tags_busqueda,
       prod_descorta,prod_otc, lab_descorta, prod_blister, COALESCE(prec_vta_final,0) prec_vta_final, prod_stock_fraccion,proalc_unidad_n,
       preing_cantidad, COALESCE(prec_margen, 0)::varchar prec_margen, lab_descripcion, prod_stock_cantidad, pvvc_vtacero::int::bool, prtifr_codigo,
       json_agg(CASE WHEN (mensajes.producto_mensaje <> ''::text OR mensajes.producto_mensaje IS NOT NULL) IS TRUE AND (promos.promos_descripcion <> ''::text OR promos.promos_descripcion IS NOT NULL) IS TRUE THEN
           concat(promos.promos_descripcion, ',', mensajes.producto_mensaje)
       WHEN (mensajes.producto_mensaje <> ''::text OR mensajes.producto_mensaje IS NOT NULL) IS TRUE AND (promos.promos_descripcion = ''::text OR promos.promos_descripcion IS NULL) IS TRUE THEN
           mensajes.producto_mensaje
       ELSE promos.promos_descripcion
       END) AS promos_descripcion,
        COALESCE(flag_bolsa, false) as flag_bolsa,COALESCE(prec_costo_compra, 0)::varchar as prec_costo_compra, protip_codigo, prod_descripcion, preing_fraccion, False AS prom_solo_bcp,
        (select json_agg(SIMIL) from (select prod_afecto, parent::varchar as parent_prod_codigo,
            COALESCE(prec_vta_sugerido, 0) as prec_vta_sugerido, prod_lista4b, tiprec_descorta, string_to_array(procodbar_codigo, ',')::varchar[] as procodbar_codigo, proest_descripcion, prod_bolsa, prod_fraccion, prod_codigo::varchar, tiprec_descripcion, priact_descripcion, prod_tags_busqueda, prod_descorta, lab_descorta, prod_blister,
            COALESCE(prec_vta_final,0) as prec_vta_final, prod_stock_fraccion, COALESCE(prec_margen, 0)::varchar as prec_margen, lab_descripcion, prod_stock_cantidad, pvvc_vtacero::int::bool, prtifr_codigo,string_to_array(promos_descripcion, ',')::varchar[] as promos_descripcion, COALESCE(flag_bolsa, false) as flag_bolsa,
            COALESCE(prec_costo_compra, 0)::varchar as prec_costo_compra, protip_codigo, prod_descripcion, tiprec_codigo from lv_producto_similar lvps where lvps.parent=producto.prod_codigo) AS SIMIL) AS similares,
        (select json_agg(COMP) from (select prod_afecto, parent::varchar as parent_prod_codigo,
            COALESCE(prec_vta_sugerido, 0) as prec_vta_sugerido, prod_lista4b, tiprec_descorta, string_to_array(procodbar_codigo, ',')::varchar[] as procodbar_codigo, proest_descripcion, prod_bolsa, prod_fraccion, prod_codigo::varchar, tiprec_descripcion, priact_descripcion, prod_tags_busqueda,prod_descorta, lab_descorta, prod_blister,
            COALESCE(prec_vta_final,0) as prec_vta_final, prod_stock_fraccion, prec_margen::varchar, lab_descripcion, prod_stock_cantidad, pvvc_vtacero::int::bool, prtifr_codigo,string_to_array(promos_descripcion, ',')::varchar[] as promos_descripcion, flag_bolsa,
            COALESCE(prec_costo_compra, 0)::varchar as prec_costo_compra, protip_codigo, prod_descripcion, tiprec_codigo from lv_producto_complementario lvpc where lvpc.parent=producto.prod_codigo) AS COMP) AS complementarios,
       (select json_agg(LOTS) from (SELECT lts.aud_fecha,sasl_fraccion,regsan_codigo,sasl_cantidad,sasle_codigo,sasl_lote,sasl_vencimiento,lts.aud_user,lts.prod_codigo::varchar FROM sucursal_almacen_stock_lote lts WHERE lts.prod_codigo = producto.prod_codigo and sasl_vencimiento > current_date and (sasl_cantidad > 0 or sasl_fraccion > 0)) AS LOTS) as lotes
FROM producto
INNER JOIN producto_estado pe on pe.proest_codigo = producto.proest_codigo
INNER JOIN laboratorio l on l.lab_codigo = producto.lab_codigo
LEFT JOIN tipo_receta tr on producto.tiprec_codigo = tr.tiprec_codigo
LEFT JOIN producto_codigo_barra pcb on producto.prod_codigo = pcb.prod_codigo
LEFT JOIN (SELECT string_agg(pa.priact_descripcion::text, ','::text) AS priact_descripcion,
                       pap.prod_codigo
                FROM producto_principio_activo pap
                         JOIN principio_activo pa ON pa.priact_codigo = pap.priact_codigo
                WHERE pa.activo = true
                  AND pap.activo = true
                GROUP BY pap.prod_codigo) pact ON producto.prod_codigo = pact.prod_codigo
LEFT JOIN (SELECT producto_cpi.prod_codigo, producto_cpi.procpi_valor,
                           string_agg(producto_cpi.cpitip_codigo::text, ','::text) AS cpitip_codigos
                    FROM producto_cpi
                    WHERE 'now'::text::date >= producto_cpi.procpi_inicia
                      AND 'now'::text::date <= producto_cpi.procpi_finaliza
                      AND producto_cpi.procpi_item <= 1
                    GROUP BY producto_cpi.prod_codigo, producto_cpi.procpi_valor) cpi
                ON producto.prod_codigo = cpi.prod_codigo
LEFT JOIN (SELECT promv.prod_codigo,
               prom.proori_codigo,
               string_agg(pm.prommen_descripcion::text, ','::text) AS promos_descripcion
        FROM promocion_venta promv
                 JOIN promocion prom ON promv.prom_codigo = prom.prom_codigo
                 LEFT JOIN promocion_mensaje pm
                           ON prom.emp_codigo = pm.emp_codigo AND prom.prom_codigo = pm.prom_codigo AND
                              prom.proori_codigo = pm.proori_codigo
        WHERE promv.activo = true
          AND 'now'::text::date >= prom.prom_inicia
          AND 'now'::text::date <= prom.prom_finaliza
          AND prom.activo = true
          AND pm.activo = true
        GROUP BY promv.prod_codigo, prom.proori_codigo) promos ON promos.prod_codigo = producto.prod_codigo
     LEFT JOIN (SELECT string_agg(producto_mensaje.promen_mensaje::text, ','::text) AS producto_mensaje,
                       producto_mensaje.prod_codigo
                FROM producto_mensaje
                WHERE producto_mensaje.activo = true
                  AND 'now'::text::date >= producto_mensaje.promen_inicio
                  AND 'now'::text::date <= producto_mensaje.promen_fin
                GROUP BY producto_mensaje.prod_codigo, producto_mensaje.promen_inicio) mensajes
               ON mensajes.prod_codigo = producto.prod_codigo
where producto.proest_codigo in (1, 10) and producto.procon_visualizar_vta=true and protip_codigo=1 and (producto.prod_descripcion like CONCAT('%',querystring,'%') or producto.prod_codigo::text = querystring or pcb.procodbar_codigo = querystring)
group by producto.prod_codigo,producto.prod_stock_cantidad,producto.prod_stock_fraccion,l.lab_codigo, l.lab_descorta,l.lab_descripcion,tr.tiprec_descripcion, pe.proest_descripcion, cpi.procpi_valor, promos.proori_codigo, mensajes.producto_mensaje, promos.promos_descripcion, pact.priact_descripcion
order by l.lab_codigo in (273, 278) desc, producto.prod_stock_cantidad desc, producto.prod_stock_fraccion desc;
$$;

alter function busqueda_producto(character varying) owner to postgres;