DELIMITER $$
DROP PROCEDURE IF EXISTS predios_residente;
CREATE PROCEDURE predios_residente
(residente$ VARCHAR(20))
BEGIN
	DECLARE residente_$ INT;
	SET residente_$ := (SELECT id FROM residentes WHERE clave = residente$);
	SELECT descifrar(p.predio) AS predio, descifrar(p.lote) AS lote, CONCAT(descifrar(p.calle), ' ', descifrar(p.exterior)) AS calle
	FROM familias AS f
	INNER JOIN predios AS p ON f.predio = p.id
	WHERE f.residente = residente_$ AND f.estado = 11;
END
$$

DELIMITER $$
DROP PROCEDURE IF EXISTS acceso_residente;
CREATE PROCEDURE acceso_residente
(usuario$ VARCHAR(20), passwd$ VARCHAR(20))
BEGIN
	DECLARE id_$ INT;
	IF EXISTS (SELECT id FROM residentes WHERE LOWER(descifrar(usuario)) = LOWER(usuario$)) THEN
		SET id_$ := (SELECT id FROM residentes WHERE LOWER(descifrar(usuario)) = LOWER(usuario$));
		IF (SELECT descifrar(passwd) FROM residentes WHERE id = id_$) = passwd$ THEN
			IF (SELECT COUNT(predio) FROM familias WHERE residente = id_$ AND estado = 11) > 0 THEN
				SELECT 800 AS estado, clave AS clave, CONCAT(descifrar(nombre),' ',descifrar(paterno)) AS mssg
				FROM residentes WHERE id = id_$;
			ELSE
				SELECT 500 AS estado, '' AS clave, 'El residente no tiene predios asociados, visita la administración de colinas' AS mssg;
			END IF;
		ELSE
			SELECT 500 AS estado, '' AS clave, 'Ingresa las credenciales correctas' AS mssg;
		END IF;
	ELSE
		SELECT 500 AS estado, '' AS clave, 'Ingresa las credenciales correctas' AS mssg;
	END IF;
END
$$

DELIMITER $$
DROP PROCEDURE IF EXISTS datos_fiscales;
CREATE PROCEDURE datos_fiscales
(predio$ varchar(8))
BEGIN
	DECLARE residente_$ INT;
	IF EXISTS (SELECT 88 FROM familias as f inner join predios as p on f.predio = p.id WHERE descifrar(p.predio) = predio$ AND f.parentesco = 1 AND f.estado = 11) THEN
		SET residente_$ := (SELECT f.residente FROM familias as f inner join predios as p on f.predio = p.id WHERE descifrar(p.predio) = predio$ AND f.parentesco = 1 AND f.estado = 11);
		IF (SELECT descifrar(rfc) FROM residentes WHERE id = residente_$) COLLATE utf8mb4_uca1400_ai_ci = '' or (SELECT descifrar(codigo) FROM residentes WHERE id = residente_$) COLLATE utf8mb4_uca1400_ai_ci = '' THEN
			SELECT 'PUBLICO EN GENERAL' AS nombre, '14608' AS codigo, 'XAXX010101000' AS rfc, '616' AS regimen;
		ELSE
	     SELECT TRIM(CONCAT(descifrar(r.nombre),' ',descifrar(r.paterno),' ',descifrar(r.materno))) AS nombre, descifrar(r.codigo) AS codigo, descifrar(r.rfc) AS rfc, u.clave AS regimen
			FROM residentes AS r
			INNER JOIN regimenes AS u ON r.regimen = u.id
			WHERE r.id = residente_$;
			-- SELECT 'PUBLICO EN GENERAL' AS nombre, '14608' AS codigo, 'XAXX010101000' AS rfc, '616' AS regimen;	
		END IF;
	ELSE
		SELECT 'PUBLICO EN GENERAL' AS nombre, '14608' AS codigo, 'XAXX010101000' AS rfc, '616' AS regimen;	
	END IF;
END
$$

-- >>> se registran los residentes por almacen, indicando el parentesco
DELIMITER $$
DROP PROCEDURE IF EXISTS iFamilia;
CREATE PROCEDURE iFamilia
(residente$ VARCHAR(10), predio$ VARCHAR(5), parentesco$ VARCHAR(100), nota$ TEXT)
BEGIN
	-- variable para id del almacen y residente
	DECLARE predio_$ INT;
	DECLARE residente_$ INT;
	DECLARE parentesco_$ INT;
	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 *FROM familias WHERE predio = predio_$ AND residente = residente_$ AND estado = 11) THEN
		SET parentesco_$ = parentescos(parentesco$);
		IF parentesco_$ = 1 THEN
			UPDATE familias SET estado = 80 WHERE parentesco = 1 AND predio = predio_$;
		END IF;
		INSERT INTO familias(predio, residente, parentesco, nota)
		VALUES(predio_$, residente_$, parentesco_$, nota$);
		IF (ROW_COUNT()) = 1 THEN
			SELECT 800 AS estado, 'Se registró el residente en el predio' AS mssg;
		ELSE
			SELECT 500 AS estado, 'Intenta de nuevo' AS mssg;
		END IF;
	ELSE
		SELECT 500 AS estado, "El residente ya esta ligado al predio" AS mssg;
	END IF;
END
$$

-- >>> busca residentes
DELIMITER $$
DROP PROCEDURE IF EXISTS mapResidentes;
CREATE PROCEDURE mapResidentes
(map$ VARCHAR(100))
BEGIN
	-- variable para dato a buscar
	DECLARE map_$ VARCHAR(100);
	SET map_$ = CONCAT('%', map$, '%');
	SELECT DISTINCT r.clave AS clave, descifrar(r.nombre) AS nombre, descifrar(r.paterno) AS paterno, descifrar(r.materno) AS materno
	FROM residentes AS r
	WHERE descifrar(r.nombre) LIKE map_$ OR descifrar(r.paterno) LIKE map_$ OR descifrar(r.materno) LIKE map_$ or concat(descifrar(r.nombre), ' ', descifrar(r.paterno), ' ', descifrar(r.materno)) COLLATE utf8mb4_unicode_ci like map_$
	ORDER BY descifrar(r.nombre);
END
$$

-- >>> consulta los datos de los residentes
DELIMITER $$
DROP PROCEDURE IF EXISTS cResidente;
CREATE PROCEDURE cResidente
(clave$ VARCHAR(10))
BEGIN
	-- variable para id del residente
	DECLARE id_$ INT;
	SET id_$ := (SELECT id FROM residentes WHERE clave = clave$);
	-- consulta los datos del residente
	SELECT descifrar(r.nombre) AS nombre, descifrar(r.paterno) AS paterno, descifrar(r.materno) AS materno, descifrar(r.codigo) AS codigo, descifrar(r.rfc) AS rfc, CASE WHEN x.id IS NULL THEN '' ELSE x.clave END AS regimen, descifrar(usuario) AS usuario, descifrar(passwd) AS passwd
	FROM residentes AS r
	LEFT JOIN regimenes AS x ON r.regimen = x.id
	WHERE r.id = id_$;
	
	SELECT descifrar(contacto) AS contacto, estados(estado) AS estado
	FROM contactos WHERE clave = id_$ AND estado IN (18, 184, 181, 1814)
	ORDER BY estado DESC;

	SELECT descifrar(p.predio) AS predio, descifrar(p.lote) AS lote, CONCAT(descifrar(p.calle), ' ', descifrar(p.exterior)) AS calle, pa.descripcion AS parentesco
	FROM familias AS f
	JOIN predios AS p ON f.predio = p.id
	JOIN parentescos AS pa ON f.parentesco = pa.id
	WHERE f.residente = id_$ AND f.estado = 11 ORDER BY f.parentesco;
END
$$

-- >>> actualiza los datos de los residentes
DELIMITER $$
DROP PROCEDURE IF EXISTS uResidente;
CREATE PROCEDURE uResidente
(clave$ VARCHAR(10), nombre$ VARCHAR(100), paterno$ VARCHAR(70), materno$ VARCHAR(70), codigo$ VARCHAR(5), rfc$ VARCHAR(13), regimen$ VARCHAR(5), usuario$ VARCHAR(20), passwd$ VARCHAR(10))
BEGIN
	-- variable para id del residente y regimen
	DECLARE id_$ INT;
	DECLARE regimen_$ INT DEFAULT(0);
	SET id_$ := (SELECT id FROM residentes WHERE clave = clave$);
	IF NOT EXISTS (SELECT id FROM residentes WHERE LOWER(descifrar(usuario)) = LOWER(usuario$) AND NOT id = id_$ AND usuario$ <> '') THEN
		IF regimen$ <> '' THEN
			SET regimen_$ := (SELECT id FROM regimenes WHERE clave = regimen$);
		END IF;
		UPDATE residentes SET nombre = cifrar(UPPER(nombre$)), paterno = cifrar(UPPER(paterno$)), materno = cifrar(UPPER(materno$)), codigo = cifrar(codigo$), rfc = cifrar(UPPER(rfc$)), regimen = regimen_$, usuario = cifrar(usuario$), passwd = cifrar(passwd$) 
		WHERE id = id_$;
		IF (ROW_COUNT()) = 1 THEN
			SELECT 800 AS estado, 'Se actualizó el residente' AS mssg;
		ELSE
			SELECT 500 AS estado, 'Intenta de nuevo' AS mssg;
		END IF;
	ELSE
		SELECT 500 AS estado, 'El usuario de acceso ya existe' AS mssg;
	END IF;
END
$$

-- >>> registra a los residentes
DELIMITER $$
DROP PROCEDURE IF EXISTS iResidente;
CREATE PROCEDURE iResidente
(nombre$ VARCHAR(100), paterno$ VARCHAR(70), materno$ VARCHAR(70), codigo$ VARCHAR(5), rfc$ VARCHAR(13), regimen$ VARCHAR(5), usuario$ VARCHAR(20), passwd$ VARCHAR(10))
BEGIN
	-- variable para regimen y clave
	DECLARE clave_$ VARCHAR(10);
	DECLARE regimen_$ INT DEFAULT(0);
	-- obtiene clave random
	SET clave_$ = clave();
	IF NOT EXISTS (SELECT id FROM residentes WHERE clave = clave_$ OR descifrar(rfc) = UPPER(rfc$) AND rfc$ <> '') THEN
		IF NOT EXISTS (SELECT id FROM residentes WHERE LOWER(descifrar(usuario)) = LOWER(usuario$) AND usuario$ <> '') THEN
			IF regimen$ <> '' THEN
				SET regimen_$ := (SELECT id FROM regimenes WHERE clave = regimen$);
			END IF;
			INSERT INTO residentes(clave, nombre, paterno, materno, codigo, rfc, regimen, usuario, passwd)
			VALUES(clave_$, cifrar(UPPER(nombre$)), cifrar(UPPER(paterno$)), cifrar(UPPER(materno$)), cifrar(codigo$), cifrar(UPPER(rfc$)), regimen_$, cifrar(usuario$), cifrar(passwd$));
			IF (ROW_COUNT()) = 1 THEN
				SELECT 800 AS estado, clave_$ AS mssg;
			ELSE
				SELECT 500 AS estado, 'Intenta de nuevo' AS mssg;
			END IF;
		ELSE
			SELECT 500 AS estado, 'El usuario de acceso ya existe' AS mssg;
		END IF;
	ELSE
		SELECT 500 AS estado, 'Intenta de nuevo' AS mssg;
	END IF;
END
$$
