DROP VIEW IF EXISTS softsat_locacao_veiculo_manutencao;

CREATE VIEW softsat_locacao_veiculo_manutencao AS
SELECT
    vei.*,
    vei.km_atual - vei.km_oleo > sko.value 
        OR TO_DAYS(CURRENT_DATE) - TO_DAYS(vei.data_oleo) > sdo.value AS manutencao_oleo,
    vei.km_atual - vei.km_oleo AS km_oleo_percorrido,
    TO_DAYS(CURRENT_DATE) - TO_DAYS(vei.data_oleo) AS dias_oleo_percorrido,
    vei.km_atual - vei.km_suspensao > CAST(sks.value AS DECIMAL) AS manutencao_suspensao,
    vei.km_atual - vei.km_suspensao AS km_suspensao_percorrido,
    vei.km_atual - vei.km_pneu > skp.value AS manutencao_pneu,
    vei.km_atual - vei.km_pneu AS km_pneu_percorrido
FROM softsat_locacao_veiculo_detalhe AS vei
LEFT JOIN softsat_locacao_grupo AS gru ON gru.id = vei.grupo_id
LEFT JOIN softsat_locacao_marca AS mar ON mar.id = vei.marca_id
LEFT JOIN hwapx_setting AS sko ON sko.id = 'softsat.locacao.manutencao.km_oleo'
LEFT JOIN hwapx_setting AS sdo ON sdo.id = 'softsat.locacao.manutencao.dias_oleo'
LEFT JOIN hwapx_setting AS sks ON sks.id = 'softsat.locacao.manutencao.km_suspensao'
LEFT JOIN hwapx_setting AS skp ON skp.id = 'softsat.locacao.manutencao.km_pneu'
WHERE vei.ativo = 1;

SELECT * FROM softsat_locacao_veiculo_manutencao;