delimiter $$
drop procedure if exists cEstadoVisita;
create procedure cEstadoVisita
(clave$ varchar(20))
begin
    declare id_$ bigint default(select id from visitantes where clave = clave$);
    select descifrar(p.predio) as predio, descifrar(p.lote) as lote, concat(descifrar(p.calle), ' ', descifrar(p.exterior)) as calle, concat(descifrar(r.nombre), ' ', descifrar(paterno)) as residente, descifrar(v.nombre) as visita, descifrar(v.placa) as placa, descifrar(v.marca) as marca, descifrar(v.modelo) as modelo, descifrar(v.color) as color, fechas(v.visita, 3) as fecha, v.nota as nota, case when v.estado = 11 then 'QR NO ESCANEADO' when v.estado = 1001 then 'LA VISITA INGRESO' else 'LA VISITA TERMINO' end as estado 
    from visitantes as v
    inner join predios as p on v.predio = p.id
    inner join residentes as r on v.residente = r.id
    where v.id = id_$;

    select time(registro) as registro, case estado when 11 then 'SE REACTIVO EL QR' when 101 then 'LA VISITA INGRESO' else 'LA VISITA SALIO' end as estado
    from ingresos_visitas where visita = id_$ order by registro;
end
$$

delimiter $$
drop procedure if exists cVisitas;
create procedure cVisitas
(inicio$ date, fin$ date, predio$ varchar(8))
BEGIN
    if predio$ = '' then
        SELECT v.clave as clave, concat('PREDIO: ', descifrar(p.predio),' LTE: ', descifrar(p.lote), ', CALLE: ', descifrar(p.calle), ' ', descifrar(p.exterior)) as predio, concat(descifrar(r.nombre), ' ', descifrar(r.paterno)) as residente, descifrar(v.nombre) as visita, descifrar(v.placa) as placa, fechas (v.visita, 3) as fecha, case when v.estado = 11 then 'QR NO ESCANEADO' when v.estado = 1001 then 'LA VISITA INGRESO' else 'LA VISITA TERMINO' end as estado
    from visitantes as v
    inner JOIN predios as p on v.predio = p.id
    inner join residentes as r on v.residente = r.id
    where visita between inicio$ and fin$ order by v.visita, descifrar(p.predio);
ELSE
           SELECT v.clave as clave, concat(descifrar(r.nombre), ' ', descifrar(r.paterno)) as residente, descifrar(v.nombre) as visita, descifrar(v.placa) as placa, fechas (v.visita, 3) as fecha, case when v.estado = 11 then 'QR NO ESCANEADO' when v.estado = 1001 then 'LA VISITA INGRESO' else 'LA VISITA TERMINO' end as estado
    from visitantes as v
    inner JOIN predios as p on v.predio = p.id
    inner join residentes as r on v.residente = r.id
    where visita between inicio$ and fin$ and descifrar(p.predio) collate utf8mb4_unicode_ci = predio$ order by v.visita;
end if;
END
$$

DELIMITER $$
DROP PROCEDURE IF EXISTS reactiva_qr;
CREATE PROCEDURE reactiva_qr
(clave$ VARCHAR(20))
BEGIN
    DECLARE id_$ BIGINT DEFAULT(SELECT id FROM visitantes WHERE clave = clave$);
    IF (SELECT estado FROM visitantes WHERE id = id_$) = 404 THEN
        UPDATE visitantes SET estado = 11 WHERE id = id_$;
        IF (ROW_COUNT()) = 1 THEN
            INSERT INTO ingresos_visitas(visita, registro, estado) VALUES(id_$, NOW(), 11);
            SELECT 800 AS estado, 'Se reactivo el QR' AS mssg;
        ELSE
            SELECT 500 AS estado, 'Intenta de nuevo' AS mssg;
        END IF;
    ELSE
        SELECT 500 AS estado, 'El QR no se puede reactivar' AS mssg;
    END IF;
END
$$

DELIMITER $$
DROP PROCEDURE IF EXISTS cVisitasResidente;
CREATE PROCEDURE cVisitasResidente
(residente$ VARCHAR(20))
BEGIN
    DECLARE residente_$ INT DEFAULT(SELECT id FROM residentes WHERE clave = residente$);
    SELECT v.clave AS clave, CONCAT('PREDIO: ', descifrar(p.predio), ', ', descifrar(p.lote), ' ', descifrar(p.calle), ' ', descifrar(p.exterior)) AS predio, descifrar(v.nombre) AS nombre, descifrar(v.placa) AS placa, fechas(v.visita, 3) AS visita, CASE v.estado WHEN 11 THEN 'En espera' WHEN 101 THEN 'La visita ingreso' ELSE 'La visita termino' END AS estado
    FROM familias AS f
    INNER JOIN visitantes AS v ON f.predio = v.predio
    INNER JOIN predios AS p ON v.predio = p.id
    WHERE f.estado = 11 AND f.residente = residente_$ AND CURDATE() <= v.visita ORDER BY v.visita;
END
$$

DELIMITER $$
DROP PROCEDURE IF EXISTS cVisita;
CREATE PROCEDURE cVisita
(clave$ VARCHAR(20))
BEGIN
	DECLARE id_$ BIGINT DEFAULT(SELECT id FROM visitantes WHERE clave = clave$);
    DECLARE estado_$ INT DEFAULT(SELECT estado FROM visitantes WHERE id = id_$);
    declare ingreso_$ datetime;
    IF estado_$ = 11 THEN
        IF (SELECT visita FROM visitantes WHERE id = id_$) = CURDATE() THEN
            SELECT 800 AS estado, descifrar(p.predio) AS predio, descifrar(p.lote) AS lote, CONCAT (descifrar(p.calle), ' ', descifrar(exterior)) AS calle, estados(CAST(SUBSTRING(p.uso, 1, 3) AS INT)) AS uso, estados(CAST(SUBSTRING(p.uso, 5) AS INT)) AS socio, propietario_predio(v.predio) AS propietario, CONCAT(descifrar(r.nombre), ' ', descifrar(r.paterno)) AS residente, descifrar(v.nombre) AS nombre, descifrar(v.placa) AS placa, descifrar(v.marca) AS marca, descifrar(v.modelo) AS modelo, descifrar(v.color) AS color, v.nota AS nota
            FROM visitantes AS v
	         INNER JOIN predios AS p ON v.predio = p.id
	         INNER JOIN residentes AS r ON v.residente = r.id
	         WHERE v.id = id_$;
            INSERT INTO ingresos_visitas(visita, registro, estado) VALUES(id_$, NOW(), 101);
            UPDATE visitantes SET estado = 101 WHERE id = id_$;
        ELSE
            SELECT 500 AS estado, 'Hoy no es la fecha de visita' AS mssg;
        END IF;
    ELSEIF estado_$ = 101 THEN
        set ingreso_$ := (select registro from ingresos_visitas where visita = id_$ and estado = 101 order by registro desc limit 1);
        if (timestampdiff(minute, ingreso_$, now()) >= 1) then
            SELECT 808 AS estado, 'La visita puede salir, el QR se inactivo' AS mssg, descifrar(p.predio) AS predio, descifrar(p.lote) AS lote, CONCAT (descifrar(p.calle), ' ', descifrar(exterior)) AS calle, estados(CAST(SUBSTRING(p.uso, 1, 3) AS INT)) AS uso, estados(CAST(SUBSTRING(p.uso, 5) AS INT)) AS socio, propietario_predio(v.predio) AS propietario, CONCAT(descifrar(r.nombre), ' ', descifrar(r.paterno)) AS residente, descifrar(v.nombre) AS nombre, descifrar(v.placa) AS placa, descifrar(v.marca) AS marca, descifrar(v.modelo) AS modelo, descifrar(v.color) AS color, v.nota AS nota
            FROM visitantes AS v
	         INNER JOIN predios AS p ON v.predio = p.id
	         INNER JOIN residentes AS r ON v.residente = r.id
	         WHERE v.id = id_$;
            INSERT INTO ingresos_visitas(visita, registro, estado) VALUES(id_$, NOW(), 404);
            UPDATE visitantes SET estado = 404 WHERE id = id_$;
        else
            SELECT 800 AS estado, descifrar(p.predio) AS predio, descifrar(p.lote) AS lote, CONCAT (descifrar(p.calle), ' ', descifrar(exterior)) AS calle, estados(CAST(SUBSTRING(p.uso, 1, 3) AS INT)) AS uso, estados(CAST(SUBSTRING(p.uso, 5) AS INT)) AS socio, propietario_predio(v.predio) AS propietario, CONCAT(descifrar(r.nombre), ' ', descifrar(r.paterno)) AS residente, descifrar(v.nombre) AS nombre, descifrar(v.placa) AS placa, descifrar(v.marca) AS marca, descifrar(v.modelo) AS modelo, descifrar(v.color) AS color, v.nota AS nota
            FROM visitantes AS v
	         INNER JOIN predios AS p ON v.predio = p.id
	         INNER JOIN residentes AS r ON v.residente = r.id
	         WHERE v.id = id_$;
        end if;
    ELSE
        SELECT 500 AS estado, 'QR desactivado, solicitar reactivación o nuevo' AS mssg;
    END IF;
END
$$

DELIMITER $$
DROP PROCEDURE IF EXISTS iVisita;
CREATE PROCEDURE iVisita
(predio$ VARCHAR(8), residente$ VARCHAR(20), nombre$ VARCHAR(500), placa$ VARCHAR(15), marca$ VARCHAR(70), modelo$ VARCHAR(70), color$ VARCHAR(70), visita$ DATE, nota$ TEXT)
BEGIN
	DECLARE predio_$ INT;
	DECLARE residente_$ INT;
	DECLARE clave_$ VARCHAR(20) DEFAULT(CONCAT(DAY(NOW()), MONTH(NOW()), YEAR(NOW()), clave()));
	SET predio_$ := (SELECT id FROM predios WHERE descifrar(predio) = predio$ AND estado_registro = 11);
	SET residente_$ := (SELECT id FROM residentes WHERE clave = residente$);
	IF NOT EXISTS (SELECT id FROM visitantes WHERE clave = clave_$) THEN
		INSERT INTO visitantes(clave, predio, residente, nombre, placa, marca, modelo, color, visita, nota)
		VALUES(clave_$, predio_$, residente_$, cifrar(UPPER(nombre$)), cifrar(UPPER(placa$)), cifrar(UPPER(marca$)), cifrar(UPPER(modelo$)), cifrar(UPPER(color$)), visita$, nota$);
		IF (ROW_COUNT()) = 1 THEN
			SELECT 800 AS estado, clave_$ AS clave, 'Se registro la visita' AS mssg;
		ELSE
			SELECT 500 AS estado, '' AS clave, 'Intenta de nuevo' AS mssg;
		END IF;
	ELSE
		SELECT 500 AS estado, '' AS clave, 'Intenta de nuevo' AS mssg;
	END IF;
END
$$
