<?php
class ImoblesIndexHelper {
    private function createValidTypeUnitsData($lastMonthOnly = true){
        if(!$lastMonthOnly){
            // Crio tabela temporária para auxiliar no geração dos dados iniciais
            $sql = "drop table if exists tmp_month;
                    create table tmp_month(
                        `date` date not null
                    )";
            ORM::execute($sql);

            // Preencho com meses anteriores
            $sql = "insert into tmp_month (date) values";
            for ( $i = 1; $i <= 36; $i++){
                if ($i > 1) $sql .= ',';
                $sql .= " (DATE_ADD(date_format(now(), '%Y-%m-01'), INTERVAL -$i MONTH))";
            };

            ORM::execute($sql);
            $dateValue = "tm.date";
            $dateTable = "tmp_month tm,";
            $dateField = "tm.date,";
        } else {
            $dateValue = "now() - interval 1 month";
            $dateTable = "";
            $dateField = "";
        }

        // Populo a tabela de preços consolidados com todos os tipo de unidade válidos com unidades disponíveis para todos os meses da tabela temporária
        $sql = "insert into module_imobles_listings_prices_consolidated (
                    reference_date,
                    enterprise_id,
                    unit_type_id,
                    number_rooms,
                    address_city_id,
                    address_neighborhood_id,
                    address_neighborhood_sanitized,
                    release_date,
                    release_year,
                    area,
                    -- min_price,
                    avg_price -- ,
                    -- max_price
                )
                select 
                    date_format($dateValue, '%Y-%m-01'),
                    e.id,
                    ut.id,
                    0,
                    0,
                    0,
                    '',
                    '1900-01-01',
                    0, 
                    0, 
                    -- 0, 
                    -- 0, 
                    0
                from
                    $dateTable
                    module_imobles_enterprise_units_type ut
                    
                left join 
                    module_imobles_enterprise e
                on
                    e.id = ut.module_imobles_enterprise_id

                where
                    e.imobles_client = true
                    and e.delete_at is null
                    and e.module_imobles_enterprise_standard_id <> 5 -- MCMV
                    and (e.estimated_price is null or e.estimated_price = 0)
                    and (e.init_sales is null or date_format(e.init_sales, '%Y-%m') <= date_format($dateValue, '%Y-%m'))
                    and (ut.crawler_type_id = 4 OR ut.crawler_type_id IS NULL)
                    and ut.delete_at is null
                group by $dateField e.id, ut.id
                order by $dateField e.id, ut.id";
        ORM::execute($sql);

        if(!$lastMonthOnly){
            // Removo a tabela temporária com os meses anteriores
            $sql = "drop table if exists tmp_month";
            ORM::execute($sql);
        }
    }

    private function consolidateOldData($lastMonthOnly = true){
        $whereDate = "";
        if ($lastMonthOnly) {
            $whereDate = "and date_format(lp1.date, '%Y-%m') = date_format(now() - interval 1 month, '%Y-%m')";
        }

        // Atualizo a tabela de preços consolidados com o restante dos valores para cada tipo de unidade e mês que possuir valor na tabela de preços
        $sql = "UPDATE 
                    module_imobles_listings_prices_consolidated as lpc,
                (
                    select
                        DATE_FORMAT(lp1.date, '%Y-%m-01') as refdate,
                        e.id as enterprise_id,
                        lp1.unit_type_id as unit_type_id,
                        ut.number_rooms,
                        e.address_city_id as address_city_id,
                        -- 0 as address_neighborhood_id,
                        e.address_neighborhood_sanitized,
                        DATE_FORMAT(e.release_date, '%Y-%m-%d') as release_date,
                        DATE_FORMAT(e.release_date, '%Y') as release_year,
                        ut.area,
                        -- min(lp1.price) as min_price,
                        avg(lp1.price) as avg_price -- ,
                        -- max(lp1.price) as max_price
                    from
                        module_imobles_listings_prices lp1
                
                    -- Para buscar o último valor de cada mês, ao invés do mínimo, médio e máximo de cada mês
                    -- Descomentar as linhas abaixo e a última linha do WHERE (and lp2.id is null)
                    --
                    -- Busco o último valor do mês para cada tipo de unidade
                    left join
                    	module_imobles_listings_prices lp2
                    on (
                    	lp1.unit_type_id = lp2.unit_type_id 
                    	AND lp1.id < lp2.id 
                    	and DATE_FORMAT(lp1.date, '%Y-%m') = DATE_FORMAT(lp2.date, '%Y-%m')
                    )
                
                    left join
                        module_imobles_listings l
                    on
                        l.id = lp1.listing_id
                
                    left join
                        module_imobles_enterprise_units_type ut
                    on
                        lp1.unit_type_id = ut.id
                
                    left join
                        module_imobles_enterprise e
                    on
                        e.id = ut.module_imobles_enterprise_id
                
                    where
                        (l.crawler_type_id = 4 OR l.crawler_type_id IS NULL)
                        and l.advertiser_id is null
                        and lp1.rental is null
                        and lp1.price > 50000
                        " . $whereDate . "
                        and e.imobles_client = true
                        and e.delete_at is null
                        and e.module_imobles_enterprise_standard_id <> 5 -- MCMV
                        and (e.estimated_price is null or e.estimated_price = 0)
                        and e.init_sales < lp1.date
                        and (ut.crawler_type_id = 4 OR ut.crawler_type_id IS NULL)
                        and ut.delete_at is null
                        and lp2.id is null

                    group by refdate, unit_type_id
                    order by refdate, unit_type_id
                ) as lp
                
                SET
                    lpc.number_rooms = lp.number_rooms,
                    lpc.address_city_id = lp.address_city_id,
                    -- address_neighborhood_id = 0,
                    lpc.address_neighborhood_sanitized = lp.address_neighborhood_sanitized,
                    lpc.release_date = lp.release_date,
                    lpc.release_year = lp.release_year,
                    lpc.area = lp.area,
                    -- lpc.min_price = lp.min_price,
                    lpc.avg_price = lp.avg_price -- ,
                    -- lpc.max_price = lp.max_price
                
                WHERE
                    lpc.reference_date = refdate
                    AND lpc.enterprise_id = lp.enterprise_id
                    AND lpc.unit_type_id = lp.unit_type_id";

        ORM::execute($sql);
    }

    private function importOldDataCSV($fileName){
        $months = array(
                '01' => 'jan',
                '02' => 'fev',
                '03' => 'mar',
                '04' => 'abr',
                '05' => 'mai',
                '06' => 'jun',
                '07' => 'jul',
                '08' => 'ago',
                '09' => 'set',
                '10' => 'out',
                '11' => 'nov',
                '12' => 'dez');
        
        $csv = new parseCSV($fileName);
        $csv->auto($fileName);
        $indexData = $csv->data;
        
        foreach($indexData as $d){
            $enterpriseID = trim($d["planta.RECORD_ID EMP"]);
            $unitTypeID   = trim($d["RECORD_ID UNI"]);
            $dateStr      = trim($d["Data gráfico"]);
            $price        = trim($d["pag_preco"]);
            $area         = trim($d["planta.Área - m²"]);
            $numberRooms  = trim($d["planta.Quartos"]);
            $dateDelivery = trim($d["empreendimento.Data Entrega"]);

            $price = str_replace(',', '.', str_replace('.', '', $price));

            $month = array_search(substr($dateStr, 0, 3), $months);
            $year = substr($dateStr, -2);
            $refDate = '20' . $year . '-' . $month . '-01';

            $consolidated = ORM::for_table('module_imobles_listings_prices_consolidated')
                ->where('reference_date', $refDate)
                ->where('unit_type_id', $unitTypeID)
                ->find_one();
            if ($consolidated && $consolidated->avg_price > 0){
                // Data already exists
                // NO NEED TO UPDATE
                echo "DATA EXISTS\n";
                echo $enterpriseID . " | " . $unitTypeID . " | " . $refDate . " | " . $price . " | " . $area . " | " . $dateStr . " | " .  $numberRooms . " | " . $dateDelivery . "\n";
                echo $consolidated->enterprise_id . " | " . $consolidated->unit_type_id . " | " . $consolidated->reference_date . " | " . $consolidated->avg_price . " | " . $consolidated->area . "\n";
            } else {
                // Insert new data
                echo "NEW DATA\n";
                echo $enterpriseID . " | " . $unitTypeID . " | " . $refDate . " | " . $price . " | " . $area . " | " . $dateStr . " | " .  $numberRooms . " | " . $dateDelivery . "\n";
                $enterprise = ORM::for_table('module_imobles_enterprise')
                    ->find_one($enterpriseID);

                if($enterprise){
                    $sql = "insert into module_imobles_listings_prices_consolidated (
                            reference_date,
                            enterprise_id,
                            unit_type_id,
                            number_rooms,
                            address_city_id,
                            address_neighborhood_id,
                            address_neighborhood_sanitized,
                            release_date,
                            release_year,
                            area,
                            -- min_price,
                            avg_price -- ,
                            -- max_price
                        ) values (
                            '$refDate',
                            $enterpriseID,
                            $unitTypeID,
                            $numberRooms,
                            $enterprise->address_city_id,
                            0,
                            '$enterprise->address_neighborhood_sanitized',
                            DATE_FORMAT('$enterprise->release_date', '%Y-%m-%d'),
                            DATE_FORMAT('$enterprise->release_date', '%Y'),
                            $area, 
                            -- $price, 
                            -- $price, 
                            $price
                        ) ON DUPLICATE KEY UPDATE    
                            reference_date = '$refDate',
                            enterprise_id = $enterpriseID,
                            unit_type_id = $unitTypeID,
                            number_rooms = $numberRooms,
                            address_city_id = $enterprise->address_city_id,
                            address_neighborhood_id = 0,
                            address_neighborhood_sanitized = '$enterprise->address_neighborhood_sanitized',
                            release_date = DATE_FORMAT('$enterprise->release_date', '%Y-%m-%d'),
                            release_year = DATE_FORMAT('$enterprise->release_date', '%Y'),
                            area = $area,
                            -- min_price = $price,
                            avg_price = $price -- ,
                            -- max_price = $price";
                    // echo $sql . "/n";
                    ORM::execute($sql);
                } else {
                    echo "EMPREENDIMENTO NÃO LOCALIZADO\n";
                    echo "ENTERPRISE_ID = " . $enterpriseID . "\n";
                    echo "UNIT_TYPE_ID = " . $unitTypeID . "\n";
                }

            }
        }
    }

    private function populateEmptyData(){
        // Preencho os campos vazios com a informação do mês anterior do mesmo tipo de unidade
        $sql = "select
                    distinct(unit_type_id) as id
                from
                    module_imobles_listings_prices_consolidated
                where 
                    avg_price = 0
                order by
                    unit_type_id";
        $unitTypeIDs = ORM::for_table('module_imobles_listings_prices_consolidated')
            ->raw_query($sql)
            ->find_many();
        foreach($unitTypeIDs as $utID){

            // Se não houver unidades disponíveis, deixa vazio que será apagado no próximo passo
            $typeUnit = ORM::for_table('module_imobles_enterprise_units_type')
                ->find_one($utID->id);
            if ($typeUnit->available_units == 0){
                continue;
            }

            $sql = "set
                        @unit_type_id := 0,
                        @number_rooms := 0,
                        @address_city_id := 0,
                        @address_neighborhood_id := 0,
                        @address_neighborhood_sanitized := '',
                        @release_date := 0,
                        @release_year := 0,
                        @area := 0,
                        -- @min_price := 0,
                        @avg_price := 0 -- ,
                        -- @max_price := 0
                    ;
            
                    update 
                        module_imobles_listings_prices_consolidated lpc,
                    (    
                        select
                            id,
                            reference_date,
                            enterprise_id,
                            @unit_type_id := unit_type_id,
                            if(number_rooms > 0, @number_rooms := number_rooms, if(@unit_type_id = unit_type_id , @number_rooms, 0)) as number_rooms,
                            if(address_city_id > 0, @address_city_id := address_city_id, if(@unit_type_id = unit_type_id, @address_city_id, 0)) as address_city_id,
                            if(address_neighborhood_id > 0, @address_neighborhood_id := address_neighborhood_id, if(@unit_type_id = unit_type_id, @address_neighborhood_id, 0)) as address_neighborhood_id,
                            if(address_neighborhood_sanitized <> '', @address_neighborhood_sanitized := address_neighborhood_sanitized, if(@unit_type_id = unit_type_id, @address_neighborhood_sanitized, 0)) as address_neighborhood_sanitized,
                            if(release_date > 0, @release_date := release_date, if(@unit_type_id = unit_type_id ,@release_date, 0)) as release_date,
                            if(release_year > 0, @release_year := release_year, if(@unit_type_id = unit_type_id ,@release_year, 0)) as release_year,
                            if(area > 0, @area := area, if(@unit_type_id = unit_type_id ,@area, 0)) as area,
                            -- if(min_price > 0, @min_price := min_price, if(@unit_type_id = unit_type_id ,@min_price, 0)) as min_price,
                            if(avg_price > 0, @avg_price := avg_price, if(@unit_type_id = unit_type_id ,@avg_price, 0)) as avg_price -- ,
                            -- if(max_price > 0, @max_price := max_price, if(@unit_type_id = unit_type_id ,@max_price, 0)) as max_price
                        from module_imobles_listings_prices_consolidated
                        where
                            unit_type_id = $utID->id
                        order by reference_date
                    ) as lpc2
                    SET
                        lpc.number_rooms = lpc2.number_rooms,
                        lpc.address_city_id = lpc2.address_city_id,
                        lpc.address_neighborhood_id = lpc2.address_neighborhood_id,
                        lpc.address_neighborhood_sanitized = lpc2.address_neighborhood_sanitized,
                        lpc.release_date = lpc2.release_date,
                        lpc.release_year = lpc2.release_year,
                        lpc.area = lpc2.area,
                        -- lpc.min_price = lpc2.min_price,
                        lpc.avg_price = lpc2.avg_price -- ,
                        -- lpc.max_price = lpc2.max_price
                    
                    WHERE
                        lpc.id = lpc2.id";
            ORM::execute($sql);
        }
    }

    private function removeEmptyData(){
        // Removo dados vazios da tabela de consolidados
        $sql = "delete from
                    module_imobles_listings_prices_consolidated
                where
                    avg_price = 0";
        ORM::execute($sql);
    }

    public function consolidateOldIndex($lastMonthOnly = false){

        echo "\nConsolidando dados inciais\n";

        try{
            // if (!$lastMonthOnly){
            //     $fileName = "application/plugins/module_imobles/indiceimobles.csv";
            //     if(!is_file($fileName)){
            //         throw new Exception('ARQUIVO NÃO ENCONTRADO! -> ' . $fileName);
            //     }
            // }

            // Crio os dados vazios para os meses anteriores
            echo "\nCriando dados vazios";
            $this->createValidTypeUnitsData($lastMonthOnly);

            // Preencho a tabela de valores consolidados com os dados existentes
            echo "\nConsolidando dados";
            $this->consolidateOldData($lastMonthOnly);

            // if (!$lastMonthOnly){
            //     // Importo dados do Índice antigo
            //     echo "\nImportando Índice antigo";
            //     $this->importOldDataCSV($fileName);
            // }

            // Completa os dados dos meses vazios, quando houver dados em meses anteriores
            echo "\nCompletando dados vazios";
            $this->populateEmptyData();

            // remove dados vazios
            echo "\nRemovendo dados vazios restantes";
            $this->removeEmptyData();
 
            echo "\nDados Consolidados!\n";
        } catch (Exception $e){
            echo "\nERRO AO CONSOLIDAR DADOS!\n";
            echo $e->getMessage();
        }
    }

    public function consolidateLastMonth(){
        // Populo a tabela de preços consolidados com todos os tipo de unidade válidos com unidades disponíveis no momento atual
        $errors = array();
        $countFinded   = 0;
        $countInserted = 0;
        $countErrors   = 0;
        try{
            $sql = "select 
                        date_format(now() - interval 1 month, '%Y-%m-01') as reference_date,
                        e.id as enterprise_id,
                        ut.id as unit_type_id,
                        ut.number_rooms,
                        e.address_city_id,
                        0,
                        e.address_neighborhood_sanitized,
                        e.release_date,
                        date_format(e.release_date, '%Y') as release_year,
                        ut.area,
                        ut.price_min, 
                        ut.price_avg, 
                        ut.price_max
                    from
                        module_imobles_enterprise_units_type ut
                        
                    left join 
                        module_imobles_enterprise e
                    on
                        e.id = ut.module_imobles_enterprise_id

                    where
                        e.imobles_client = true
                        and e.delete_at is null
                        and e.module_imobles_enterprise_standard_id <> 5 -- MCMV
                        and (e.estimated_price is null or e.estimated_price = 0)
                        and (e.init_sales is null or date_format(e.init_sales, '%Y-%m') <= date_format(now() - interval 1 month, '%Y-%m'))
                        and ut.delete_at is null
                        AND ut.price_avg > 0
                        and (ut.crawler_type_id = 4 OR ut.crawler_type_id IS NULL)
                        and ut.available_units > 0
                        and (
                            select
                                count(u.id)
                            from
                                module_imobles_enterprise_units u
                            where
                                u.delete_at is null
                                and (u.crawler_type_id = 4 OR u.crawler_type_id IS NULL)
                                and (u.unavailable = 0 OR u.unavailable is null)
                                and u.module_imobles_enterprise_units_type_id = ut.id
                            ) > 0
                    group by e.id, ut.id
                    order by e.id, ut.id";
            // ORM::execute($sql);
            $prices = ORM::for_table('module_imobles_listings_prices_consolidated')
                ->raw_query($sql)
                ->find_array();
            $countFinded = count($prices);
            foreach($prices as $k=>$p){
                try {
                    $sql = "insert ignore into module_imobles_listings_prices_consolidated (
                                reference_date,
                                enterprise_id,
                                unit_type_id,
                                number_rooms,
                                address_city_id,
                                address_neighborhood_id,
                                address_neighborhood_sanitized,
                                release_date,
                                release_year,
                                area,
                                min_price,
                                avg_price,
                                max_price
                            )
                            values (" .
                                "'" . $p["reference_date"] . "', " .
                                $p["enterprise_id"] . ", " .
                                $p["unit_type_id"] . ", " .
                                $p["number_rooms"] . ", " .
                                $p["address_city_id"] . ", " .
                                "0, " .
                                "'" . $p["address_neighborhood_sanitized"] . "', " .
                                "'" . $p["release_date"] . "', " .
                                $p["release_year"] . ", " .
                                $p["area"] . ", " .
                                $p["price_min"] . ", " .
                                $p["price_avg"] . ", " .
                                $p["price_max"] . " 
                            )";
                    ORM::execute($sql);
                    $countInserted++;
                } catch (Exception $e){
                    $countErrors++;
                    $message = "Erro ao inserir dados consolidados (array pos = $k).<br/>";
                    $message .= "SQL: $sql<br/>";
                    $message .= "O erro retornado foi: <br/>";
                    $message .= $e->getMessage() . "<br/>";
                    $errors[] = $message;
                }
            }
        } catch (Exception $e){
            $countErrors++;
            $message = "Erro buscando dados para consolidação.<br/>";
            $message .= "O erro retornado foi: <br/>";
            $message .= $e->getMessage();
            $errors[] = $message;
        } finally {
            $message = "";
            if ($countFinded > 0){
                $message .= "Dados localizados: $countFinded<br/>";
            }
            if ($countInserted > 0){
                $message .= "Dados inseridos: $countInserted<br/>";
            }
            if ($countErrors == 0){
                $message .= "Nenhum erro encontrado<br/>";
            } else {
                $message .= "Erros encontrados: $countErrors<br/>";
                foreach($errors as $e){
                    $message .= $e;
                    $message .= "-------------------------------------------------<br/>";
                }
            }
            
            echo str_replace("<br/>", "\n", $message);
            $recipients = [
                ["name" => "Leonardo Lopes", "email" => "leonardo@myside.com.br"],
                ["name" => "Glauco Emerim", "email" => "glauco.emerim@myside.com.br"]
            ];
            $subject = 'ÍNDICE IMOBLES - Consolidação mensal - ' . date("d/m/Y H:i");
            $message = "<html><body><h2>$subject</h2><br/>$message</body></html>";
            foreach ($recipients as $r){
                Notify_Email::_send($r["name"], $r["email"], $subject, $message, 0, 0, 'leonardo@myside.com.br');
            }
        }
    }
}