<?php

class Sinistro2_Model_CobrancaValorCombustivel extends Agro_Db_Table_Abstract
{
    protected $_schema = 'sinistro';
    protected $_name = 'cobranca_valor_combustivel';
    protected $_primary = 'id';

    public const PESO_COMBUSTIVEL = 0.3;

    public static function calculaGastoCombustivel(float $kmRodados, $precoCombustivel): float
    {
        return $kmRodados * $precoCombustivel * self::PESO_COMBUSTIVEL;
    }

    public function getValorCombustivelByProposta(int $idProposta, string $dataVistoria)
    {
        $select = $this->select()->setIntegrityCheck(false);
        $select->from(['cvc' => "{$this->_schema}.{$this->_name}"], ['valor']);
        $select->join(['m' => 'sistema.municipios'], 'm.id_estado = cvc.id_estado', false);
        $select->join(['pp' => 'seguro.propostas_propriedades'], 'pp.id_municipio = m.id', false);
        $select->where("pp.id_proposta = {$idProposta}");
        $select->where("cvc.data_inicio <= ?", Agro_Util::formatDate($dataVistoria, Zend_Date::W3C));
        $select->order('data_inicio desc');
        
        return $this->_db->fetchRow($select);
    }

    public function getValorCombustivelPorVistorias(array $idsVistorias, bool $isPrevia)
    {
        $sqlPrincipal = '
            SELECT
                id_vistoria,
                max(data_inicio) AS data_definicao_combustivel,
                max(valor) FILTER (WHERE rn = 1) AS valor_combustivel
            FROM
                Aux
            GROUP BY id_vistoria
        ';

        if (!$isPrevia) {
            $sql = "
                (
                    WITH Aux AS (
                        SELECT
                            svlf.id_vistoria,
                            scvc.data_inicio,
                            scvc.valor,
                            ROW_NUMBER() OVER (PARTITION BY svlf.id_vistoria ORDER BY scvc.data_inicio DESC) AS rn
                        FROM
                            sinistro.cobranca_valor_combustivel AS scvc
                            INNER JOIN sistema.municipios sm ON sm.id_estado = scvc.id_estado
                            INNER JOIN seguro.propostas_propriedades spp ON spp.id_municipio = sm.id
                            INNER JOIN seguro.propostas AS sp ON sp.id = spp.id_proposta
                            INNER JOIN sinistro.processos AS sproc ON sproc.id_proposta = sp.id
                            INNER JOIN sinistro.laudos_finais AS slf ON slf.id_processo = sproc.id
                            INNER JOIN sistema.vistorias_laudos_finais AS svlf ON svlf.id_laudo_final = slf.id
                            INNER JOIN sinistro.vistoriador_despesa svd
                                ON svd.id_vistoria = svlf.id_vistoria
                                    AND svd.id_proposta = sp.id
                        WHERE
                            svlf.id_vistoria in (". implode(',', $idsVistorias) .")
                            AND data_inicio <= svd.dt_despesa::DATE
                    )
                    {$sqlPrincipal}
                )
                UNION
                (
                    WITH Aux AS (
                        SELECT
                            svlp.id_vistoria,
                            scvc.data_inicio,
                            scvc.valor,
                            ROW_NUMBER() OVER (PARTITION BY svlp.id_vistoria ORDER BY scvc.data_inicio DESC) AS rn
                        FROM
                            sinistro.cobranca_valor_combustivel AS scvc
                            INNER JOIN sistema.municipios sm ON sm.id_estado = scvc.id_estado
                            INNER JOIN seguro.propostas_propriedades spp ON spp.id_municipio = sm.id
                            INNER JOIN seguro.propostas AS sp ON sp.id = spp.id_proposta
                            INNER JOIN sinistro.processos AS sproc ON sproc.id_proposta = sp.id
                            INNER JOIN sinistro.laudos_preliminares AS slp ON slp.id_processo = sproc.id
                            INNER JOIN sistema.vistorias_laudos_preliminares AS svlp ON svlp.id_laudo_preliminar = slp.id
                            INNER JOIN sinistro.vistoriador_despesa svd
                                ON svd.id_vistoria = svlp.id_vistoria
                                    AND svd.id_proposta = sp.id
                        WHERE
                            svlp.id_vistoria in (". implode(',', $idsVistorias) .")
                            AND data_inicio <= svd.dt_despesa::DATE
                    )
                    {$sqlPrincipal}
                )
            ";
        } else {
            $sql = "
                WITH Aux AS (
                    SELECT
                        spvp.id_vistoria,
                        scvc.data_inicio,
                        scvc.valor,
                        ROW_NUMBER() OVER (PARTITION BY spvp.id_vistoria ORDER BY scvc.data_inicio DESC) AS rn
                    FROM
                        sinistro.cobranca_valor_combustivel AS scvc
                        INNER JOIN sistema.municipios sm ON sm.id_estado = scvc.id_estado
                        INNER JOIN seguro.propostas_propriedades spp ON spp.id_municipio = sm.id
                        INNER JOIN seguro.propostas_vistorias_previas spvp ON spvp.id_proposta = spp.id_proposta
                        INNER JOIN sinistro.vistoriador_despesa svd
                            ON svd.id_vistoria = spvp.id_vistoria
                                AND svd.id_proposta = spvp.id_proposta
                    WHERE
                        spvp.id_vistoria in (". implode(',', $idsVistorias) .")
                        AND data_inicio <= svd.dt_despesa::DATE
                )
                {$sqlPrincipal}
            ";
        }
        
        $rs = $this->_db->fetchAll($sql);
        $retorno = [];

        foreach ($rs as $row) {
            $retorno[$row['id_vistoria']] = $row['valor_combustivel'];
        }

        return $retorno;
    }
}