create table control_login_sells
(
id serial4 primary key,
id_user character varying not null ,
cant int null,
state int not null,
date date not null
)


select u.id from "user" u where u.user_name = 'usuario_de_ventas_1' and u.password = 'MF0CRaO1BmtllywJ.mHqTrIi84Y/THNs2A6YE/p6qrPeT6nN75urh1d/HA2DXG97gSN8W.f9fOFYkj7UzIHYO0'



CREATE OR REPLACE FUNCTION public.insert_executive_by_day(num character varying,fecha date)
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $function$
DECLARE
    cant bigint;
    login BOOLEAN := false;
	suma bigint;
BEGIN

    SELECT COUNT(*) into cant
    FROM role_user ru
    JOIN "role" r ON ru.role_id = r.id
    JOIN "user" u ON ru.user_id = u.id
    WHERE ru.user_id = num
        AND (ru.role_id = '651dcd848eaf2d9d7' OR r.id = '6525d7b9d2d331fdf')
		AND u.deleted = 'true';


    IF cant <> 0 THEN
        login := true;
    END IF;
	
	IF login == 'true' then
		SELECT COUNT(*) INTO suma FROM control_login_sells cl WHERE cl.id_user=num and cl.date <> fecha;
		if suma <>0 THEN
			DELETE * FROM control_login_sells;
			INSERT INTO control_login_sells(id_user,cant,"state",date) values (num,0,1,fecha);
			login:=true;
		else 
			login:=false;
		END IF	
	END IF 
    RETURN login;
END;
$function$;
 
drop function insert_executive_by_day(num character varying, fecha date)
select insert_executive_by_day('651dcea0cb492d5f2','2023-11-10')

'651dcea0cb492d5f2'	
'651da66aca8f14de5'


CREATE OR REPLACE FUNCTION public.insert_executive_by_day(num character varying, fecha date)
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $function$
DECLARE
    fecha1 date;
	fecha2 date;
	datos bigint;
BEGIN

	SELECT to_char(MAX(alr.created_at),'YYYY-MM-DD') AS last_connection INTO FECHA1
    FROM role_user ru
    JOIN "role" r ON ru.role_id = r.id
    JOIN "user" u ON ru.user_id = u.id
	JOIN auth_log_record alr ON alr.user_id = u.id
    WHERE(ru.role_id = '651dcd848eaf2d9d7' OR r.id = '6525d7b9d2d331fdf')
		AND u.deleted = 'true'
		AND alr.is_denied = 'false';
			
	SELECT to_char(now(),'YYYY-MM-DD') INTO fecha2	
	
	
	IF fecha1 = fecha2 THEN
			
	ELSE 
		SELECT COUNT(*) into datos FROM control_login_sells
		while datos>0 loop
			delete from control_login_sells
			SELECT COUNT(*) into datos FROM control_login_sells
		end loop
		
		if fecha1 = fecha2 THEN
		
		ELSE
			INSERT INTO control_login_sells (id_user, cant, "state", "date")
			SELECT DISTINCT alr.user_id, 0, 1, '2023-10-11'
			FROM role_user ru
			JOIN "role" r ON ru.role_id = r.id
			JOIN "user" u ON ru.user_id = u.id
			JOIN auth_log_record alr ON alr.user_id = u.id
			WHERE (ru.role_id = '651dcd848eaf2d9d7' OR r.id = '6525d7b9d2d331fdf')
				AND u.deleted = 'true'
				AND alr.is_denied = 'false'
				AND to_char(alr.created_at, 'YYYY-MM-DD') = '2023-10-11'
				AND NOT EXISTS (
				SELECT 1
				FROM control_login_sells cls
				WHERE cls.id_user = alr.user_id
				);
		END IF
		
	END IF
	
END;
$function$;


DELETE FROM control_login_sells

