DELIMITER $$
DROP PROCEDURE IF EXISTS mapCarros;
CREATE PROCEDURE mapCarros
(map$ VARCHAR(200))
BEGIN
	DECLARE map_$ VARCHAR(210) DEFAULT(CONCAT('%',LOWER(map$), '%'));
	SELECT c.clave AS clave, CONCAT('PREDIO: ', descifrar(p.predio), ' LOTE: ', descifrar(p.lote), ', CALLE: ', descifrar(p.calle), ' ', descifrar(p.exterior)) AS predio, descifrar(c.placa) AS placa, descifrar(c.modelo) AS modelo, CASE c.marca WHEN 0 THEN '' ELSE m.descripcion END AS marca, CASE c.color WHEN 0 THEN '' ELSE co.descripcion END AS color, CASE c.holograma WHEN 0 THEN 'SIN HOLOGRAMA' ELSE 'CON HOLOGRAMA' END AS holograma, estados(c.estado) AS estado
	FROM carros AS c
	INNER JOIN colores AS co ON c.color = co.id
	INNER JOIN marcas AS m ON c.marca = m.id
	INNER JOIN predios AS p ON c.predio = p.id
	WHERE descifrar(p.predio) LIKE map_$ AND c.estado IN (11, 10, 33, 800) OR LOWER(descifrar(p.lote)) LIKE map_$ AND c.estado IN (11, 10, 33, 800) OR LOWER(descifrar(p.calle)) LIKE map_$ AND c.estado IN (11, 10, 33, 800) OR LOWER(descifrar(c.placa)) LIKE map_$ AND c.estado IN (11, 10, 33, 800) OR LOWER(descifrar(c.modelo)) LIKE map_$ AND c.estado IN (11, 10, 33, 800) OR LOWER(m.descripcion) LIKE map_$ AND c.estado IN (11, 10, 33, 800) OR LOWER(co.descripcion) LIKE map_$ AND c.estado IN (11, 10, 33, 800) or concat(descifrar(p.calle), ' ', descifrar(p.exterior)) COLLATE utf8mb4_unicode_ci like map_$ and c.estado in (11,10,33,800) ;
END
$$

DELIMITER $$
DROP PROCEDURE IF EXISTS cambiaPredioCarro;
CREATE PROCEDURE cambiaPredioCarro
(clave$ VARCHAR(10), predio$ VARCHAR(10), nota$ TEXT)
BEGIN
	DECLARE id_$ INT;
	DECLARE predio_$ INT;
	SET id_$ := (SELECT id FROM carros WHERE clave = clave$ AND estado IN (11, 10, 33));
	SET predio_$ := (SELECT id FROM predios WHERE descifrar(predio) = predio$ AND estado_registro = 11);
	IF (SELECT predio FROM carros WHERE id = id_$) <> predio_$ THEN
		UPDATE carros SET estado = 80 WHERE clave = clave$;
		INSERT INTO carros(clave, predio, placa, modelo, marca, color, periodo, nota, holograma)
		SELECT clave, predio_$, placa, modelo, marca, color, periodo, nota$, holograma FROM carros WHERE id = id_$;
		SELECT 800 AS estado, 'Se cambio de predio el vehículo' AS mssg;
	ELSE
		SELECT 500 AS estado, 'Selecciona otro predio al actual' AS mssg;
	END IF;
END
$$

DELIMITER $$
DROP PROCEDURE IF EXISTS uCarro;
CREATE PROCEDURE uCarro
(clave$ VARCHAR(10), placa$ VARCHAR(15), modelo$ VARCHAR(100), marca$ VARCHAR(200), color$ VARCHAR(200), periodo$ INT, holograma$ INT, nota$ TEXT, estado$ INT)
BEGIN
	DECLARE id_$ INT;
	SET id_$ := (SELECT id FROM carros WHERE clave = clave$ AND estado IN (11, 10, 33, 800));
	IF NOT EXISTS (SELECT id FROM carros WHERE descifrar(placa) = UPPER(placa$) AND NOT id = id_$ AND estado IN (11, 10, 33)) THEN
		UPDATE carros SET placa = cifrar(UPPER(placa$)), modelo = cifrar(UPPER(modelo$)), marca = obtiene_marca(marca$), color = obtiene_color(color$), periodo = periodo$, holograma = holograma$, nota = nota$, estado = estado$ WHERE id = id_$;
		IF (ROW_COUNT()) = 1 THEN
			SELECT 800 AS estado, 'Se actualizaron los datos del vehículo' AS mssg;
		ELSE
			SELECT 500 AS estado, 'Intenta de nuevo' AS mssg;
		END IF;
	ELSE
		SELECT 500 AS estado, 'El vehículo ya existe' AS mssg;
	END IF;
END
$$

DELIMITER $$
DROP PROCEDURE IF EXISTS cCarro;
CREATE PROCEDURE cCarro
(clave$ VARCHAR(10))
BEGIN
	DECLARE id_$ INT;
	SET id_$ := (SELECT id FROM carros WHERE clave = clave$ AND estado IN (11, 10, 33, 800));
	SELECT CONCAT('PREDIO: ', descifrar(p.predio)) AS predio, CONCAT('LOTE: ', descifrar(p.lote)) AS lote, CONCAT('CALLE: ', descifrar(p.calle), ' ', descifrar(p.exterior)) AS calle, concat('USO: ', estados(cast(substring(p.uso, 1, 3) as int)), ' - ', estados(cast(substring(uso, 5) as int))) as uso, propietario_predio(p.id) as propietario, descifrar(c.placa) AS placa, descifrar(c.modelo) AS modelo, m.clave AS marca, co.clave AS color, c.periodo AS periodo, c.holograma AS holograma, c.nota AS nota, c.estado AS estado, CONCAT('FECHA DE REGISTRO: ', fechas(c.registro, 3)) AS registro
	FROM carros AS c
	INNER JOIN predios AS p ON c.predio = p.id
	INNER JOIN marcas AS m ON c.marca = m.id
	INNER JOIN colores AS co ON c.color = co.id
	WHERE c.id = id_$;
END
$$

DELIMITER $$
DROP PROCEDURE IF EXISTS iCarro;
CREATE PROCEDURE iCarro
(predio$ VARCHAR(10), placa$ VARCHAR(15), modelo$ VARCHAR(100), marca$ VARCHAR(200), color$ VARCHAR(200), periodo$ INT, nota$ TEXT)
BEGIN
	DECLARE predio_$ INT;
	DECLARE clave_$ VARCHAR(10);
	SET clave_$ = clave();
	SET predio_$ := (SELECT id FROM predios WHERE descifrar(predio) = predio$);
	IF NOT EXISTS (SELECT id FROM carros WHERE clave = clave_$ OR descifrar(placa) = UPPER(placa$)) THEN
		INSERT INTO carros(clave, predio, placa, modelo, marca, color, periodo, nota)
		VALUES(clave_$, predio_$, cifrar(UPPER(placa$)), cifrar(UPPER(modelo$)), obtiene_marca(marca$), obtiene_color(color$), periodo$, nota$);
		IF (ROW_COUNT()) = 1 THEN
			SELECT 800 AS estado, 'Se registró el vehículo' AS mssg, clave_$ AS clave;
		ELSE
			SELECT 500 AS estado, 'Intenta de nuevo' AS mssg;
		END IF;
	ELSE
		SELECT 500 AS estado, 'El vehículo ya existe' AS mssg;
	END IF;
END
$$
