DELIMITER $$
DROP VIEW IF EXISTS cColores;
CREATE VIEW cColores
(clave, descripcion)
AS
SELECT clave, descripcion FROM colores ORDER BY descripcion;
$$

DELIMITER $$
DROP VIEW IF EXISTS cMarcas;
CREATE VIEW cMarcas
(clave, descripcion)
AS
SELECT clave, descripcion FROM marcas ORDER BY descripcion;
$$

DELIMITER $$
DROP VIEW IF EXISTS cCarros;
CREATE VIEW cCarros
(clave, predio, placa, modelo, marca, color, holograma, estado)
AS
SELECT c.clave, CONCAT('PREDIO: ', descifrar(p.predio), ' LOTE: ', descifrar(p.lote), ', CALLE: ', descifrar(p.calle), ' ', descifrar(p.exterior)), descifrar(c.placa), descifrar(c.modelo), CASE c.marca WHEN 0 THEN '' ELSE m.descripcion END, CASE c.color WHEN 0 THEN '' ELSE co.descripcion END, CASE c.holograma WHEN 0 THEN 'SIN HOLOGRAMA' ELSE 'CON HOLOGRAMA' END, estados(c.estado)
FROM carros AS c
INNER JOIN predios AS p ON c.predio = p.id
LEFT JOIN marcas AS m ON c.marca = m.id
LEFT JOIN colores AS co ON c.color = co.id
WHERE c.estado IN (11, 10, 33);
$$
