<?php
namespace App\Services\Indicator;

use DB;

class IndicatorService
{
    public function getIndicatorListDraft()
    {
        $status = 'draft';

        $result = DB::table('kpi_indicator as ki')
                ->join('kpi_achievement AS ka', 'ki.kpi_achievement_id', '=', 'ka.kpi_achievement_id')
                ->join('kpi_indicator_type AS kit', 'ki.kpi_indicator_type_id', '=', 'kit.kpi_indicator_type_id')
                ->select(
                    'ki.Indicator_id AS indicator_id',
                    'ki.Year AS year',
                    'ki.Division AS division',
                    'ki.Section AS section',
                    'ka.unit',
                    'ki.Indicator AS indicator',
                    'ka.indicator_Description AS description',
                    'ki.Submission AS submission',
                    'ka.annual_target',
                    'ki.Metric AS metric',
                    'ki.Category AS category',
                    'ki.Output_Outcome AS output_outcome',
                    'kit.indicator_type AS indicator_type',
                    'kit.indicator_type_description AS indicator_type_def',
                    'ki.Component_Achievement AS component_achv',
                    'ki.Status AS status',
                    'ka.kpi_achievement_id AS kpi_achievement_id',
                    'kit.kpi_indicator_type_id AS kpi_indicator_type_id'
                )
                ->where('ki.Status', $status)
                ->get();
        return $result;
    }

    public function getIndicatorListPrevious($year)
    {
        $status = 'draft';
        $result = DB::table('kpi_indicator as ki')
                ->join('kpi_achievement AS ka', 'ki.kpi_achievement_id', '=', 'ka.kpi_achievement_id')
                ->join('kpi_indicator_type AS kit', 'ki.kpi_indicator_type_id', '=', 'kit.kpi_indicator_type_id')
                ->select(
                    'ki.Indicator_id AS indicator_id',
                    'ki.Year AS year',
                    'ki.Division AS division',
                    'ki.Section AS section',
                    'ka.unit',
                    'ki.Indicator AS indicator',
                    'ka.indicator_Description AS description',
                    'ki.Submission AS submission',
                    'ka.annual_target',
                    'ki.Metric AS metric',
                    'ki.Category AS category',
                    'ki.Output_Outcome AS output_outcome',
                    'kit.indicator_type AS indicator_type',
                    'kit.indicator_type_description AS indicator_type_def',
                    'ki.Component_Achievement AS component_achv',
                    'ki.Status AS status',
                    'ka.kpi_achievement_id AS kpi_achievement_id',
                    'kit.kpi_indicator_type_id AS kpi_indicator_type_id'
                )
                ->where('ki.Year', $year)
                ->where('ki.Status','!=', $status)
                ->get();
        return $result;
    }

    public function getIndicatorTypeList()
    {
        $result = DB::table('kpi_indicator_type')
                ->select('*')
                ->get();
        return $result;
    }

    public function storeIndicator($data)
    {
        DB::beginTransaction();

        $user_id = auth()->user()->u_id;

        $achievement_id = DB::table('kpi_achievement')->insertGetId([
            'unit' => $data->input('unit'),
            //'indicator_Description' => $data->input('indicator'),
            'annual_target' => $data->input('annual_target'),
            'created_by' => $user_id,
            'updated_by' => $user_id,
            'created_at' => now(),
            'updated_at' => now(),
        ]);

        
        DB::table('kpi_indicator')->insert([
            'Year' => $data->input('year'),
            'Division' => $data->input('division'),
            'Section' => $data->input('section'),
            'Indicator' => $data->input('indicator'),
            'Submission' => $data->input('submission'),
            'Metric' => $data->input('metric'),
            'Category' => $data->input('category'),
            'Output_Outcome' => $data->input('output_outcome'),
            'Component_Achievement' => $data->input('component_achievement'),
            'Status' => 'draft',
            'kpi_achievement_id' => $achievement_id,
            'kpi_indicator_type_id' => $data->input('kpi_indicator_type_id'),
            'created_by' => $user_id,
            'updated_by' => $user_id,
            'created_at' => now(),
            'updated_at' => now(),
        ]);

        DB::commit();

        return true;
    }

    public function storeSubmitIndicator($data)
    {   
        $user_id = auth()->user()->u_id;
        $selected = $data->input('selected', []);
        foreach ($selected as $item) {
            [$id, $value] = explode('__', $item);

            if($value == 1) {
                DB::table('kpi_indicator AS ki')
                    ->where('Indicator_id', $id)
                    ->update([
                        'status' => 'submitted',
                        'updated_by' => $user_id,
                        'updated_at' => now()
                    ]);
            }
        }
        return true;
    }

    public function updateIndicator($data)
    {
        
        DB::beginTransaction();

        $user_id = auth()->user()->u_id;
        $achievement_id = $data->input('kpi_achievement_id_e');
        $indicator_id = $data->input('indicator_id_e');

        
        DB::table('kpi_achievement')
            ->where('kpi_achievement_id', $achievement_id)
            ->update([
                'unit' => $data->input('unit_e'),
                //'indicator_Description' => $data->input('indicator_e'),
                'annual_target' => $data->input('annual_target_e'),
                'updated_by' => $user_id,
                'updated_at' => now(),
            ]);

        DB::table('kpi_indicator')
            ->where('Indicator_id', $indicator_id)
            ->update([
                'Year' => $data->input('year_e'),
                'Division' => $data->input('division_e'),
                'Section' => $data->input('section_e'),
                'Indicator' => $data->input('indicator_e'),
                'Submission' => $data->input('submission_e'),
                'Metric' => $data->input('metric_e'),
                'Category' => $data->input('category_e'),
                'Output_Outcome' => $data->input('output_outcome_e'),
                'Component_Achievement' => $data->input('component_achievement_e'),
                'Status' => 'draft',
                'kpi_indicator_type_id' => $data->input('kpi_indicator_type_id_e'),
                'updated_by' => $user_id,
                'updated_at' => now(),
            ]);

        DB::commit();

        return true;
    }

    public function copyIndicator($data)
    {
        $user_id = auth()->user()->u_id;
        $selected = $data->input('selected', []);
        foreach ($selected as $item) {
            [$id, $value] = explode('__', $item);

            if($value == 1) {
                DB::table('kpi_indicator AS ki')
                    ->where('Indicator_id', $id)
                    ->update([
                        'status' => 'draft',
                        'Year' => date('Y'),
                        'updated_by' => $user_id,
                        'updated_at' => now()
                    ]);
            }
        }
    }

    public function getIndicatorList($request)
    {

        //$status = 'submitted';

        $query = DB::table('kpi_indicator as ki')
                ->join('kpi_achievement AS ka', 'ki.kpi_achievement_id', '=', 'ka.kpi_achievement_id')
                ->join('kpi_indicator_type AS kit', 'ki.kpi_indicator_type_id', '=', 'kit.kpi_indicator_type_id')
                ->select(
                    'ki.Indicator_id AS indicator_id',
                    'ki.Year AS year',
                    'ki.Division AS division',
                    'ki.Section AS section',
                    'ka.unit',
                    'ki.Indicator AS indicator',
                    'ka.indicator_Description AS description',
                    'ki.Submission AS submission',
                    'ka.annual_target',
                    'ki.Metric AS metric',
                    'ki.Category AS category',
                    'ki.Output_Outcome AS output_outcome',
                    'kit.indicator_type AS indicator_type',
                    'kit.indicator_type_description AS indicator_type_def',
                    'ki.Component_Achievement AS component_achv',
                    'ki.Status AS status',
                    'ka.kpi_achievement_id AS kpi_achievement_id',
                    'kit.kpi_indicator_type_id AS kpi_indicator_type_id',
                    'ka.PMU_activity_remark AS pmu_remarks'
                );
                //->where('ki.Status', $status);

        if($request->year) {
            $query->where('ki.Year', $request->year);
        }

        if($request->division) {
            $query->where('ki.Division', $request->division);
        }

        if($request->section) {
            $query->where('ki.Section', $request->section);
        }
        
        if($request->section) {
            $query->where('ki.Section', $request->section);
        }

        if($request->unit) {
            $query->where('ka.unit', $request->unit);
        }

        if($request->indicator_type) {
            $query->where('ki.kpi_indicator_type_id', $request->indicator_type);
        }

        if($request->status) {
            $query->where('ki.Status', $request->status);
        }

        if($request->indicator) {
            $query->where('ki.Indicator', $request->indicator);
        }

        $result = $query->get();
        return $result;
    }

    public function storeSubmitIndicatorFromIndicatorList($data)
    {   
        $user_id = auth()->user()->u_id;
        $selected = $data->input('selected', []);
        foreach ($selected as $item) {
            [$id, $value] = explode('__', $item);

            if($value == 1) {
                DB::table('kpi_indicator AS ki')
                    ->where('Indicator_id', $id)
                    ->update([
                        'status' => 'submitted',
                        'updated_by' => $user_id,
                        'updated_at' => now()
                    ]);
            }
        }
        return true;
    }

    public function getSubmittedIndicatorList($request)
    {

        $status = 'submitted';

        $query = DB::table('kpi_indicator as ki')
                ->join('kpi_achievement AS ka', 'ki.kpi_achievement_id', '=', 'ka.kpi_achievement_id')
                ->join('kpi_indicator_type AS kit', 'ki.kpi_indicator_type_id', '=', 'kit.kpi_indicator_type_id')
                ->select(
                    'ki.Indicator_id AS indicator_id',
                    'ki.Year AS year',
                    'ki.Division AS division',
                    'ki.Section AS section',
                    'ka.unit',
                    'ki.Indicator AS indicator',
                    'ka.indicator_Description AS description',
                    'ki.Submission AS submission',
                    'ka.annual_target',
                    'ki.Metric AS metric',
                    'ki.Category AS category',
                    'ki.Output_Outcome AS output_outcome',
                    'kit.indicator_type AS indicator_type',
                    'kit.indicator_type_description AS indicator_type_def',
                    'ki.Component_Achievement AS component_achv',
                    'ki.Status AS status',
                    'ka.kpi_achievement_id AS kpi_achievement_id',
                    'ki.kpi_indicator_type_id AS kpi_indicator_type_id',
                );

        if($request->year) {
            $query->where('ki.Year', $request->year);
        }

        if($request->division) {
            $query->where('ki.Division', $request->division);
        }

        if($request->section) {
            $query->where('ki.Section', $request->section);
        }

        if($request->unit) {
            $query->where('ka.unit', $request->unit);
        }

        if($request->indicator_type) {
            $query->where('ki.kpi_indicator_type_id', $request->indicator_type);
        }

        if($request->status) {
            $query->where('ki.Status', $request->status);
        } else {
            $query->where('ki.Status', $status);
        }

        if($request->indicator) {
            $query->where('ki.Indicator', $request->indicator);
        }

        $result = $query->get();
        return $result;
    }

    public function endorseIndicator($data)
    {   
        $user_id = auth()->user()->u_id;
        $selected = $data->input('selected', []);
        $type_ids = $data->input('kpi_indicator_type_id', []);
        foreach ($selected as $item) {
            [$id, $value] = explode('__', $item);
            foreach ($type_ids as $type) {
                [$indicator_id, $type_id] = explode('__', $type);
                if($value == 1 && $id == $indicator_id) {
                    DB::table('kpi_indicator AS ki')
                        ->where('Indicator_id', $id)
                        ->update([
                            'Status' => 'endorsed',
                            'kpi_indicator_type_id' => $type_id,
                            'updated_by' => $user_id,
                            'updated_at' => now()
                        ]);
                }
            }
        }
        return true;
    }

    public function disableIndicator($data)
    {   
        $user_id = auth()->user()->u_id;
        $indicator_id = $data->input('indicator_id_m');
        $kpi_achievement_id = $data->input('kpi_achievement_id_m');

        DB::table('kpi_indicator AS ki')
            ->where('Indicator_id', $indicator_id)
            ->update([
                'Status' => 'disabled',
                'updated_by' => $user_id,
                'updated_at' => now()
            ]);

        // DB::table('kpi_achievement AS ka')
        //     ->where('kpi_achievement_id', $kpi_achievement_id)
        //     ->update([
        //         'updated_by' => $user_id,
        //         'updated_at' => now()
        //     ]);

        return true;
    }
}