DROP PROCEDURE IF EXISTS softsat_locacao_veiculo_disponivel;

CREATE PROCEDURE `softsat_locacao_veiculo_disponivel`(
	IN `in_de` DATE,
	IN `in_ate` DATE,
	IN `in_marca_id` INT,
	IN `in_veiculo_id` INT)
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
SQL SECURITY INVOKER
SELECT 
    vei.*
FROM softsat_locacao_veiculo_detalhe AS vei
LEFT JOIN softsat_locacao_contrato AS con ON con.veiculo_id = vei.id
    AND con.`status` NOT IN('F', 'C')
    AND (con.saida <= DATE(in_de) OR (
        (((in_de >= con.saida OR in_ate >= con.saida) AND con.previsto IS NULL)
        OR in_de BETWEEN con.saida AND con.previsto OR in_ate BETWEEN con.saida AND con.previsto
        OR con.saida BETWEEN in_de AND in_ate OR con.previsto BETWEEN in_de AND in_ate)))
WHERE in_de <= in_ate
    AND vei.ativo = 1
    AND con.id IS NULL
    AND (in_veiculo_id IS NULL OR vei.id = in_veiculo_id)
    AND (in_marca_id IS NULL OR vei.marca_id = in_marca_id)