/*DELIMITER $$
DROP PROCEDURE IF EXISTS cambiaSocio;
CREATE PROCEDURE cambiaSocio
(predio$ VARCHAR(5))
BEGIN
	DECLARE id_$ INT;
	DECLARE uso_$ VARCHAR(10);
	SET id_$ := (SELECT id FROM predios WHERE descifrar(predio) = predio$);
	SET uso_$ := (SELECT SUBSTRING(uso, 1,4) FROM predios WHERE id = id_$);
	IF (SELECT SUBSTRING(uso, 5) FROM predios WHERE id = id_$) = '88' THEN
		SET uso_$ = CONCAT(uso_$, '66');
	ELSE
		SET uso_$ = CONCAT(uso_$, '88');
	END IF;
	UPDATE predios SET uso = uso_$ WHERE id = id_$;
	IF (ROW_COUNT()) = 1 THEN
		SELECT 800 AS estado;
	ELSE
		SELECT 500 AS estado, 'Intenta de nuevo' AS mssg;
	END IF;
END
$$*/

-- >>> busca predios
DELIMITER $$
DROP PROCEDURE IF EXISTS mapPredios;
CREATE PROCEDURE mapPredios
(map$ VARCHAR(100))
BEGIN
	-- variable para buscar
	DECLARE map_$ VARCHAR(100);
	SET map_$ = CONCAT('%', map$, '%');
	-- realiza busqueda de predios parecidos al dato de entrada
	SELECT descifrar(predio) AS predio, descifrar(lote) AS lote, descifrar(calle) AS calle, descifrar(interior) AS interior, descifrar(exterior) AS exterior, CONCAT(estados(CAST(SUBSTRING(uso, 1, 3) AS INT)), '-', estados(CAST(SUBSTRING(uso, 5) AS INT))) AS uso, estados(estado) AS estado
	FROM predios
	WHERE descifrar(predio) LIKE map_$ AND estado_registro = 11 OR descifrar(lote) LIKE map_$ AND estado_registro = 11 OR descifrar(calle) LIKE map_$ AND estado_registro = 11 OR descifrar(interior) LIKE map_$ AND estado_registro = 11 OR descifrar(exterior) LIKE map_$ AND estado_registro = 11 or concat(descifrar(calle), ' ', descifrar(exterior)) COLLATE utf8mb4_unicode_ci like map_$ and estado_registro = 11 ORDER BY descifrar(predio);
END
$$

-- >>> actualiza el estado, uso o si es socio o no, de los predios
DELIMITER $$
DROP PROCEDURE IF EXISTS uPredio;
CREATE PROCEDURE uPredio
(predio$ VARCHAR(8), uso$ VARCHAR(10), estado$ INT)
BEGIN
	DECLARE predio_$ INT;
	SET predio_$ := (SELECT id FROM predios WHERE descifrar(predio) = predio$ AND estado_registro = 11);
	IF (SELECT uso FROM predios WHERE id = predio_$) = uso$ THEN
		UPDATE predios SET estado = estado$ WHERE id = predio_$;
	ELSE	
		INSERT INTO predios(predio, lote, calle, interior, exterior, sector, uso, estado)
		SELECT predio, lote, calle, interior, exterior, sector, uso$, estado$ FROM predios WHERE id = predio_$;
		UPDATE predios SET estado_registro = 80 WHERE id = predio_$;
	END IF;
	IF (ROW_COUNT()) = 1 THEN
		SELECT 800 AS estado, 'Se actualizo el predio' AS mssg;
	ELSE
		SELECT 500 AS estado, 'Intenta de nuevo' AS mssg;
	END IF;
END
$$

-- >>> consulta los datos de un predio
DELIMITER $$
DROP PROCEDURE IF EXISTS cPredio;
CREATE PROCEDURE cPredio
(predio$ VARCHAR(8))
BEGIN
	DECLARE id_$ INT;
	SET id_$ := (SELECT id FROM predios WHERE descifrar(predio) = predio$ AND estado_registro = 11);
	SELECT descifrar(predio) AS predio, descifrar(lote) AS lote, descifrar(calle) AS calle, descifrar(interior) AS interior, descifrar(exterior) AS exterior, descifrar(sector) AS sector, SUBSTRING(uso, 1, 3) AS uso, estado AS estado, CASE WHEN SUBSTRING(uso, 5) = '88' THEN 1 ELSE 0 END AS socio, propietario_predio(id) AS propietario 
	FROM predios
	WHERE id = id_$;

	SELECT descifrar(contacto) AS contacto
	FROM contactos AS c
	INNER JOIN predios AS p ON c.clave = p.id 
	WHERE descifrar(p.predio) = predio$ AND c.estado = 1;

    SELECT CONCAT(fechas(c.inicio, 2), ' A ', fechas(c.fin, 2)) AS periodo, c.valor AS valor, pagado_convenio(c.id) AS pagado, (c.valor - pagado_convenio(c.id)) AS deuda 
	 FROM convenios AS c
	 INNER JOIN predios AS p ON c.predio = p.id 
	 WHERE descifrar(p.predio) = predio$ AND (c.valor - pagado_convenio(c.id)) > 0;

END
$$

delimiter $$
drop procedure if exists datos_predio;
create procedure datos_predio
(predio$ varchar(8))
begin
    declare id_$ int default(select id from predios where descifrar(predio) = predio$ and estado_registro = 11);
    select descifrar(predio) as predio, descifrar(lote) as lote, descifrar(exterior) as exterior, descifrar(calle) as calle, propietario_predio(id) as propietario, estados(cast(substring(uso, 1, 3) as int)) as uso, estados(cast(substring(uso, 5) as int)) as socio
    from predios where id = id_$;
end
$$

-- >>> registra los predios
DELIMITER $$
DROP PROCEDURE IF EXISTS iPredio;
CREATE PROCEDURE iPredio
(predio$ VARCHAR(5), lote$ VARCHAR(20), calle$ VARCHAR(100), interior$ VARCHAR(5), exterior$ VARCHAR(5), sector$ VARCHAR(70), uso$ VARCHAR(10), estado$ INT)
BEGIN
	-- valida si no existe el registro por predio y lote
	IF NOT EXISTS (SELECT id FROM predios WHERE descifrar(predio) = predio$ OR descifrar(lote) = lote$) THEN
		INSERT INTO predios(predio, lote, calle, interior, exterior, sector, uso, estado)
		VALUES(cifrar(predio$), cifrar(lote$), cifrar(calle$), cifrar(interior$), cifrar(exterior$), cifrar(sector$), uso$, estado$);
		IF (ROW_COUNT()) = 1 THEN
			SELECT 800 AS estado, CONCAT('Se registró el predio: ', predio$, ', lote: ', UPPER(lote$)) AS mssg;
		ELSE
			SELECT 500 AS estado, 'Intenta de nuevo' AS mssg;
		END IF; 
	ELSE
		SELECT 500 AS estado, CONCAT('Ya existe predio: ', predio$, ' o lote: ', UPPER(lote$)) AS mssg; 
	END IF;
END
$$

DELIMITER $$
DROP PROCEDURE IF EXISTS residentes_predio;
CREATE PROCEDURE residentes_predio
(predio$ VARCHAR(20))
BEGIN
    SELECT CONCAT(descifrar(r.nombre), ' ', descifrar(r.paterno), ' ', descifrar(r.materno)) AS residente, p.descripcion AS parentesco
	 FROM familias AS f
	 INNER JOIN residentes AS r ON f.residente = r.id
	 INNER JOIN parentescos AS p ON f.parentesco = p.id
	 INNER JOIN predios AS pr ON f.predio = pr.id
	 WHERE f.estado = 11 AND descifrar(pr.predio) = predio$;
END
$$

DELIMITER $$
DROP PROCEDURE IF EXISTS carros_predio;
CREATE PROCEDURE carros_predio
(predio$ VARCHAR(20))
BEGIN
    SELECT descifrar(c.placa) AS placa, descifrar(c.modelo) AS modelo, col.descripcion AS color, m.descripcion AS marca, CASE WHEN c.holograma = 0 THEN 'Sin holograma' ELSE 'Holograma colocado' END AS holograma, estados(c.estado) AS estado
    FROM carros AS c
    INNER JOIN predios AS p ON c.predio = p.id
    INNER JOIN colores AS col ON c.color = col.id
    INNER JOIN marcas AS m ON c.marca = m.id
    WHERE descifrar(p.predio) = predio$;
END
$$
