<?php

namespace App\Http\Controllers;

use App\Models\CostoUnitario;
use App\Models\ManoObraDirecta;
use App\Models\SuministrosDirectos;
use App\Models\CostoPaquetes;
use App\Models\CostoUnitarioAjustado;
use Illuminate\Http\Request;

class CostoUnitarioController extends Controller
{
    public function consolidarCostoUnitario(Request $request) {
        $contrato = $request->input('con_fk_id');
        $institucion = $request->input('ins_fk_id');
        $ano = $request->input('cun_ano');
        $mes = $request->input('cun_mes');
        $conExtrapolacion = $request->input('con_extrapolacion');

        \DB::transaction(function() use($contrato, $institucion, $ano, $mes, $conExtrapolacion) {
            ManoObraDirecta::on('costos_principal')
                           ->where('con_fk_id', $contrato)
                           ->where('ins_fk_id', $institucion)
                           ->where('mod_ano', $ano)
                           ->where('mod_mes', $mes)
                           ->delete();

            SuministrosDirectos::on('costos_principal')
                               ->where('con_fk_id', $contrato)
                               ->where('ins_fk_id', $institucion)
                               ->where('sud_ano', $ano)
                               ->where('sud_mes', $mes)
                               ->delete();

            CostoUnitario::on('costos_principal')
                         ->where('con_fk_id', $contrato)
                         ->where('ins_fk_id', $institucion)
                         ->where('cun_ano', $ano)
                         ->where('cun_mes', $mes)
                         ->delete();

            CostoUnitarioAjustado::on('costos_principal')
                                 ->where('con_fk_id', $contrato)
                                 ->where('ins_fk_id', $institucion)
                                 ->where('cua_ano', $ano)
                                 ->where('cua_mes', $mes)
                                 ->delete();

            \DB::select("insert into mano_obra_directa (con_fk_id, ins_fk_id, mod_ano, mod_mes, cco_fk_id, act_fk_id, mob_fk_id, mod_costo)
                        select ".$contrato." as con_fk_id,
                               '".$institucion."' as ins_fk_id,
                               ".$ano." as mod_ano,
                               ".$mes." as mod_mes,
                               ram.cco_fk_id,
                               ram.act_fk_id,
                               ram.mob_fk_id,
                               case when ram_valor_actividad is not null then ram_valor_actividad else
                                    ((1 / ram_relacion) * ram_consumo_actividad * vam_valor_minuto) end as costo_mob_dir
                        from costos_principal.rec_actividad_mano_obra as ram
                        left join costos_principal.valor_minuto as vam on (vam.cco_fk_id = ram.cco_fk_id and
                                                                           vam.mob_fk_id = ram.mob_fk_id and
                                                                           vam.vam_ano = ".$ano." and
                                                                           vam.vam_mes = ".$mes.")
                        where ram.con_fk_id = ".$contrato." and
                              ram.ins_fk_id = '".$institucion."' and
                              ram_ano = ".$ano);

            // Para la consolidación, toma el último suministro existente en el detalle de suministros, hasat 1 vigencia atrás
            \DB::select("insert into suministros_directos (con_fk_id, ins_fk_id, sud_ano, sud_mes, cco_fk_id, act_fk_id, sum_fk_id, sud_costo)
                        select ".$contrato." as con_fk_id,
                               '".$institucion."' as ins_fk_id,
                               ".$ano." as sud_ano,
                               ".$mes." as sud_mes,
                               ras.cco_fk_id,
                               ras.act_fk_id,
                               ras.sum_fk_id,
                               sum(((1 / ras_relacion) * ras_consumo_actividad * des_valor_unitario)) as costo_sum_dir
                        from costos_principal.rec_actividad_suministros as ras
                        join costos_principal.centros_costo as cco on (ras.cco_fk_id = cco.cco_pk_id)
                        join costos_principal.suministros as sumi on (ras.sum_fk_id = sumi.sum_pk_id)
                        left join (
                            select *
                            from (
                                select cco_cod_homologado,
                                       sum_codigo,
                                       des_cantidad,
                                       des_valor_unitario,
                                       row_number() over(
                                          partition by cco_cod_homologado, sum_codigo
                                          order by (des_ano, des_mes) desc
                                       ) as ultimo_disponible
                                from costos_principal.detalle_suministros as des
                                join costos_principal.centros_costo as cco on (des.cco_fk_id = cco.cco_pk_id)
                                join costos_principal.suministros as sumi on (des.sum_fk_id = sumi.sum_pk_id)
                                where des.con_fk_id = ".$contrato." and
                                      des.ins_fk_id = '".$institucion."' and
                                      (des_ano <= ".$ano." and des_mes <= ".$mes.") and
                                      (des_ano >= (".$ano." - 1))
                                order by 1,2
                            ) as dat
                            where dat.ultimo_disponible = 1
                        ) as des on (des.cco_cod_homologado = cco.cco_cod_homologado and des.sum_codigo = sumi.sum_codigo)
                        where ras.con_fk_id = ".$contrato." and ras.ins_fk_id = '".$institucion."' and ras_ano = ".$ano."
                        group by 1, 2, 3, 4, 5, 6, 7
                        having sum(((1 / ras_relacion) * ras_consumo_actividad * des_valor_unitario)) is not null");

            \DB::select("insert into costo_unitario (con_fk_id, ins_fk_id, cun_ano, cun_mes, cco_fk_id, act_fk_id, cun_mob_directa, cun_mob_indirecta,
                                                     cun_sum_directo, cun_sum_indirecto, cun_gastos_gen, cun_costo_dist, cun_costo_unitario)
                        select aux.*,
                               (mod_directo + mod_indirecto + sum_directo + sum_indirecto + gastos_gen + costo_dist) as cun_costo_unitario
                        from (
                            select ".$contrato.",
                                   '".$institucion."',
                                   ".$ano.",
                                   ".$mes.",
                                   mdi.cco_fk_id,
                                   mdi.act_fk_id,
                                   coalesce(mdi.mod_costo, 0) as mod_directo,
                                   (coalesce(mdi.mod_costo, 0) * tri.tri_tasa_mano_obra / 100) as mod_indirecto,
                                   coalesce(sud.sud_costo, 0) as sum_directo,
                                   (coalesce(sud.sud_costo, 0) * tri.tri_tasa_suministros / 100) as sum_indirecto,
                                   (coalesce(mdi.mod_costo, 0) + coalesce(sud.sud_costo, 0)) * tri.tri_tasa_gastos_gen / 100 as gastos_gen,
                                   (coalesce(mdi.mod_costo, 0) + coalesce(sud.sud_costo, 0)) * tri.tri_tasa_costo / 100 as costo_dist
                            from (
                                select acc.cco_fk_id,
                                       acc.act_fk_id,
                                       sum(mod_costo) as mod_costo
                                from costos_principal.actividades_centro_costo as acc
                                join costos_principal.actividades as act on (act.act_pk_id = acc.act_fk_id)
                                join costos_principal.centros_costo as cco on (cco.cco_pk_id = acc.cco_fk_id)
                                left join costos_principal.mano_obra_directa as mod on (mod.cco_fk_id = acc.cco_fk_id and
                                                                                        mod.act_fk_id = acc.act_fk_id and
                                                                                        mod_ano = ".$ano." and
                                                                                        mod_mes = ".$mes.")
                                where acc.con_fk_id = ".$contrato." and
                                      acc.ins_fk_id = '".$institucion."' and
                                      acc_ano = ".$ano." and
                                      cco.cco_final = true and
                                      act_paquete = false
                                group by 1,2
                            ) as mdi
                            left join (
                                select cco_fk_id, act_fk_id, sum(sud_costo) as sud_costo
                                from costos_principal.suministros_directos
                                where con_fk_id = ".$contrato." and
                                      ins_fk_id = '".$institucion."' and
                                      sud_ano = ".$ano." and
                                      sud_mes = ".$mes."
                                group by 1,2
                            ) as sud on (mdi.cco_fk_id = sud.cco_fk_id and mdi.act_fk_id = sud.act_fk_id)
                            left join costos_principal.tasa_recursos_indirectos as tri on (tri.cco_fk_id = mdi.cco_fk_id and
                                                                                           tri_ano = ".$ano." and tri_mes = ".$mes.")
                        ) as aux");

            // Extrapolar costo unitario
            if ($conExtrapolacion) {
                $this->extrapolarCostoUnitario($contrato, $institucion, $ano, $mes);
            }

            // Calcular costo paquetes
            $this->calcularCostoPaquetes($contrato, $institucion, $ano, $mes);

            // Ajustar las desviaciones
            $this->ajustarDesviaciones($contrato, $institucion, $ano, $mes);
        });

        return array("response" => 0);
    }

    public function extrapolarCostoUnitario($contrato, $institucion, $ano, $mes) {
        $costoExt = \DB::select("
            select *
            from (
                select inf.cco_fk_id,
                       inf.act_fk_id,
                       inf.ipr_valor * (1 - ext.margen_promedio_cco) as costo_extrapolado
                from (
                    /* Mes actual*/
                    select ipr.cco_fk_id,
                           ipr.act_fk_id,
                           sum(ipr_valor) as ipr_valor,
                           coalesce(sum((cun_mob_directa + cun_mob_indirecta + cun_sum_directo + cun_sum_indirecto + cun_gastos_gen + cun_costo_dist)), 0) as cun_costo_unitario
                    from costos_principal.informacion_produccion as ipr
                    left join costos_principal.costo_unitario as cun on (ipr.act_fk_id = cun.act_fk_id and ipr.cco_fk_id = cun.cco_fk_id and
                                                                         cun.cun_ano = ".$ano." and cun.cun_mes = ".$mes.")
                    where ipr.con_fk_id = ".$contrato." and
                          ipr.ins_fk_id = '".$institucion."' and
                          ipr_ano = ".$ano." and
                          ipr_mes = ".$mes." and
                          ipr.act_fk_id is not null
                    group by 1,2
                ) as inf
                left join (
                    /* Informacion anual */
                    select distinct cco_fk_id, margen_promedio_cco
                    from (
                        select cua.cco_fk_id,
                               cua.act_fk_id,
                               case when costos > 0 then (ipr_valor - costos) / nullif(ipr_valor, 0) else null end as margen,
                               avg(case when costos > 0 then (ipr_valor - costos) / nullif(ipr_valor, 0) else null end) over (partition by cco_fk_id order by cco_fk_id) as margen_promedio_cco
                        from (
                            select ipr.cco_fk_id,
                                   ipr.act_fk_id,
                                   sum(ipr_cantidad) as ipr_cantidad,
                                   sum(ipr_valor) as ipr_valor,
                                   coalesce(sum((cun_mob_directa + cun_mob_indirecta + cun_sum_directo + cun_sum_indirecto + cun_gastos_gen + cun_costo_dist)), 0) as costos
                            from costos_principal.informacion_produccion as ipr
                            left join costos_principal.costo_unitario as cun on (ipr.act_fk_id = cun.act_fk_id and ipr.cco_fk_id = cun.cco_fk_id and
                                                                                cun.cun_ano = ".$ano.")
                            where ipr.con_fk_id = ".$contrato." and
                                  ipr.ins_fk_id = '".$institucion."' and
                                  ipr_ano = ".$ano." and
                                  ipr.act_fk_id is not null
                            group by 1,2
                        ) as cua
                    ) as cua2
                ) as ext on (inf.cco_fk_id = ext.cco_fk_id)
                where inf.cun_costo_unitario = 0
            ) as ext2
            where costo_extrapolado is not null
        "); 

        foreach ($costoExt as $cun) {
            CostoUnitario::on('costos_principal')
                         ->where('con_fk_id', $contrato)
                         ->where('ins_fk_id', $institucion)
                         ->where('cun_ano', $ano)
                         ->where('cun_mes', $mes)
                         ->where('cco_fk_id', $cun->cco_fk_id)
                         ->where('act_fk_id', $cun->act_fk_id)
            ->update(
                [
                    "cun_costo_unitario" => $cun->costo_extrapolado,
                    "cun_extrapolado" => true
                ]
            );
        }
    }

    public function calcularCostoPaquetes($contrato, $institucion, $ano, $mes) {
        CostoPaquetes::on('costos_principal')
                     ->where('con_fk_id', $contrato)
                     ->where('ins_fk_id', $institucion)
                     ->where('cpq_ano', $ano)
                     ->where('cpq_mes', $mes)
                     ->delete();

        \DB::select("
            insert into costo_paquetes (con_fk_id, ins_fk_id, cpq_ano, cpq_mes, cpq_paquete, cco_fk_id, act_fk_id, sum_fk_id, cpq_mob_dir_costo,
                                        cpq_mob_indir_costo, cpq_sum_dir_costo, cpq_sum_indir_costo, cpq_gastos_gen_costo, cpq_costo_dist_costo, cpq_costo)
            select ".$contrato.",
                   '".$institucion."',
                   ".$ano.",
                   ".$mes.",
                   paq_paquete,
                   paq.cco_fk_id,
                   paq_actividad,
                   paq_suministro,
                   coalesce(
                        case when paq_actividad is not null then (paq_frec_realizacion / 100.0) * paq_cantidad * cun_mob_directa else null end
                   , 0) as paq_mano_obra_directa,
                   coalesce(
                        case when paq_actividad is not null then (paq_frec_realizacion / 100.0) * paq_cantidad * cun_mob_indirecta else null end
                   , 0) as paq_mano_obra_indirecta,
                   coalesce(
                        case when paq_actividad is not null then (paq_frec_realizacion / 100.0) * paq_cantidad * cun_sum_directo
                             else (paq_frec_realizacion / 100.0) * paq_cantidad * des_valor_unitario
                        end
                   , 0) as paq_suministros_directos,
                   coalesce(
                        case when paq_actividad is not null then (paq_frec_realizacion / 100.0) * paq_cantidad * cun_sum_indirecto else null end
                   , 0) as paq_suministros_indirectos,
                   coalesce(
                        case when paq_actividad is not null then (paq_frec_realizacion / 100.0) * paq_cantidad * cun_gastos_gen else null end
                   , 0) as paq_gastos_generales,
                   coalesce(
                        case when paq_actividad is not null then (paq_frec_realizacion / 100.0) * paq_cantidad * cun_costo_dist else null end
                   , 0) as paq_costo_dist,
                   coalesce(
                       case when paq_actividad is not null then (paq_frec_realizacion / 100.0) * paq_cantidad * cun_costo_unitario
                            else (paq_frec_realizacion / 100.0) * paq_cantidad * des_valor_unitario
                       end
                   , 0) as paq_total
            from costos_principal.paquetes_actividades as paq
            left join costos_principal.costo_unitario as cun on (paq.cco_fk_id = cun.cco_fk_id and paq.paq_actividad = cun.act_fk_id and
                                                                 cun.con_fk_id = ".$contrato." and cun.ins_fk_id = '".$institucion."' and cun.cun_ano = ".$ano." and cun.cun_mes = ".$mes.")
            left join costos_principal.detalle_suministros as des on (paq.cco_fk_id = des.cco_fk_id and paq.paq_suministro = des.sum_fk_id and
                                                                      des.con_fk_id = ".$contrato." and des.ins_fk_id = '".$institucion."' and des.des_ano = ".$ano." and des.des_mes = ".$mes.")
            where paq.con_fk_id = ".$contrato." and
                  paq.ins_fk_id = '".$institucion."' and
                  paq_ano = ".$ano."
        ");
    }

    public function ajustarDesviaciones($contrato, $institucion, $ano, $mes) {
        $ajustes = \DB::select("select cun2.cco_fk_id,
                                        cun2.act_fk_id,
                                        cun2.mob_directa,
                                        cun2.mob_directa * cun2.tri_tasa_mano_obra as mob_indirecta,
                                        cun2.sum_directo,
                                        cun2.sum_directo * cun2.tri_tasa_suministros as sum_indirecto,
                                        cun2.gastos_gen,
                                        cun2.costo_dist
                                from (
                                    select cun1.cco_fk_id,
                                            cun1.act_fk_id,
                                            cun1.ipr_cantidad,
                                            cun1.tri_tasa_mano_obra,
                                            cun1.tri_tasa_suministros,
                                            cun1.tri_tasa_gastos_gen,
                                            cun1.tri_tasa_costo,
                                            coalesce(cun1.mob_directa_ini * (1 + (1 - nullif(cun1.mob_directa / nullif(cun1.tri_mano_obra_directa, 0), 0))), 0) as mob_directa,
                                            coalesce(cun1.sum_directo_ini * (1 + (1 - nullif((cun1.sum_directo / nullif(cun1.tri_sumin_directos, 0)), 0))), 0) as sum_directo,
                                            coalesce(cun1.gastos_gen_ini * (1 + (1 - nullif((cun1.gastos_gen / nullif(cun1.tri_gast_generales, 0)), 0))), 0) as gastos_gen,
                                            coalesce(cun1.costo_dist_ini * (1 + (1 - nullif((cun1.costo_dist / nullif(cun1.tri_costo_dist, 0)), 0))), 0) as costo_dist
                                    from (
                                        select cun0.cco_fk_id,
                                                cun0.act_fk_id,
                                                cun0.ipr_cantidad,
                                                cun0.tri_tasa_mano_obra,
                                                cun0.tri_tasa_suministros,
                                                cun0.tri_tasa_gastos_gen,
                                                cun0.tri_tasa_costo,
                                                cun0.tri_mano_obra_directa,
                                                cun0.tri_sumin_directos,
                                                cun0.tri_gast_generales,
                                                cun0.tri_costo_dist,
                                                cun0.mob_directa_ini,
                                                cun0.mob_directa,
                                                cun0.mob_directa * cun0.tri_tasa_mano_obra as mob_indirecta,
                                                cun0.sum_directo_ini,
                                                cun0.sum_directo,
                                                cun0.sum_directo * cun0.tri_tasa_suministros as sum_indirecto,
                                                cun0.gastos_gen_ini,
                                                cun0.gastos_gen,
                                                cun0.costo_dist_ini,
                                                (cun0.mob_directa + cun0.sum_directo) * cun0.tri_tasa_costo as costo_dist
                                        from (
                                            /* Costo unitario */
                                            select cup.*,
                                                    tri.tri_tasa_mano_obra,
                                                    tri.tri_tasa_suministros,
                                                    tri.tri_tasa_gastos_gen,
                                                    tri.tri_tasa_costo,
                                                    tri.tri_mano_obra_directa,
                                                    tri.tri_sumin_directos,
                                                    tri.tri_gast_generales,
                                                    tri.tri_costo_dist
                                            from (
                                                select cun.cco_fk_id,
                                                        cun.act_fk_id,
                                                        ipr.ipr_cantidad,
                                                        cun.cun_mob_directa as mob_directa_ini,
                                                        (cun.cun_mob_directa * ipr.ipr_cantidad) as mob_directa,
                                                        cun.cun_sum_directo as sum_directo_ini,
                                                        (cun.cun_sum_directo * ipr.ipr_cantidad) as sum_directo,
                                                        cun.cun_gastos_gen as gastos_gen_ini,
                                                        (cun.cun_gastos_gen * ipr.ipr_cantidad) as gastos_gen,
                                                        cun.cun_costo_dist as costo_dist_ini
                                                from (
                                                    select cco_fk_id, act_fk_id, cun_mob_directa, cun_mob_indirecta, cun_sum_directo, cun_sum_indirecto,
                                                            cun_gastos_gen, cun_costo_dist, cun_costo_unitario
                                                    from costos_principal.costo_unitario
                                                    where con_fk_id = ".$contrato." and
                                                        ins_fk_id = '".$institucion."' and
                                                        cun_ano = ".$ano." and
                                                        cun_mes = ".$mes."
                                                ) as cun
                                                join ( /* Informacion de produccion */
                                                    select ipr.cco_fk_id,
                                                            ipr.act_fk_id,
                                                            sum(ipr_cantidad) as ipr_cantidad
                                                    from costos_principal.informacion_produccion as ipr
                                                    join costos_principal.actividades_centro_costo as acc on (ipr.act_fk_id = acc.act_fk_id and ipr.cco_fk_id = acc.cco_fk_id)
                                                    where ipr.con_fk_id = ".$contrato." and
                                                        ipr.ins_fk_id = '".$institucion."' and
                                                        ipr_ano = ".$ano." and
                                                        ipr_mes = ".$mes." and
                                                        acc_suma_produccion = true
                                                    group by 1,2
                                                ) as ipr on (cun.cco_fk_id = ipr.cco_fk_id and cun.act_fk_id = ipr.act_fk_id)
                                            ) as cup
                                            left join ( /* Tasa de recursos indirectos */
                                                select cco_fk_id,
                                                        tri_tasa_mano_obra,
                                                        tri_tasa_suministros,
                                                        tri_tasa_gastos_gen,
                                                        tri_tasa_costo,
                                                        tri_mano_obra_directa,
                                                        tri_sumin_directos,
                                                        tri_gast_generales,
                                                        tri_costo_dist
                                                from costos_principal.tasa_recursos_indirectos
                                                where con_fk_id = ".$contrato." and
                                                    ins_fk_id = '".$institucion."' and
                                                    tri_ano = ".$ano." and
                                                    tri_mes = ".$mes."
                                            ) as tri on (cup.cco_fk_id = tri.cco_fk_id)
                                        ) as cun0
                                    ) as cun1
                                ) as cun2
                                order by 1,2
                            ");

        foreach ($ajustes as $ajuste) {
            CostoUnitarioAjustado::on('costos_principal')->create(
                [
                    "con_fk_id" => $contrato,
                    "ins_fk_id" => $institucion,
                    "cua_ano" => $ano,
                    "cua_mes" => $mes,
                    "cco_fk_id" => $ajuste->cco_fk_id,
                    "act_fk_id" => $ajuste->act_fk_id,
                    "cua_mob_directa" => $ajuste->mob_directa,
                    "cua_mob_indirecta" => $ajuste->mob_indirecta,
                    "cua_sum_directo" => $ajuste->sum_directo,
                    "cua_sum_indirecto" => $ajuste->sum_indirecto,
                    "cua_gastos_gen" => $ajuste->gastos_gen,
                    "cua_costo_dist" => $ajuste->costo_dist
                ]
            );
        }
    }

    public function getManoObraDirecta(Request $request) {
        return ManoObraDirecta::on('costos_principal')
                              ->where('con_fk_id', $request->input('con_fk_id'))
                              ->where('ins_fk_id', $request->input('ins_fk_id'))
                              ->where('mod_ano', $request->input('mod_ano'))
                              ->where('mod_mes', $request->input('mod_mes'))
                              ->where('mod_costo', '>', 0)
                              ->get()->toArray();
    }

    public function getSuministrosDirectos(Request $request) {
        return SuministrosDirectos::on('costos_principal')
                                  ->where('con_fk_id', $request->input('con_fk_id'))
                                  ->where('ins_fk_id', $request->input('ins_fk_id'))
                                  ->where('sud_ano', $request->input('sud_ano'))
                                  ->where('sud_mes', $request->input('sud_mes'))
                                  ->where('sud_costo', '>', 0)
                                  ->get()->toArray();
    }

    public function getCostoUnitario(Request $request) {
        return CostoUnitario::on('costos_principal')
                            ->where('con_fk_id', $request->input('con_fk_id'))
                            ->where('ins_fk_id', $request->input('ins_fk_id'))
                            ->where('cun_ano', $request->input('cun_ano'))
                            ->where('cun_mes', $request->input('cun_mes'))
                            ->where('cun_costo_unitario', '>', 0)
                            ->get()->toArray();
    }

    public function getCostoUnitarioMeses(Request $request) {
        $meses = $request->input('meses');

        return CostoUnitario::on('costos_principal')
                            ->selectRaw('cun_mes, cco_fk_id, act_fk_id, cun_mob_directa, cun_mob_indirecta, cun_sum_directo, cun_sum_indirecto,
                                         cun_gastos_gen, cun_costo_dist, cun_costo_unitario')
                            ->where('con_fk_id', $request->input('con_fk_id'))
                            ->where('ins_fk_id', $request->input('ins_fk_id'))
                            ->where('cun_ano', $request->input('cun_ano'))
                            ->whereRaw('cun_mes in ('.$meses.')')
                            ->where('cun_costo_unitario', '>', 0)
                            ->orderBy('cun_mes')
                            ->get()->toArray();
    }
}
