<?php

namespace App\Services\Cartables\SafetyAndPrivacy;

use App\Exceptions\ProhibitedAction;
use App\Facades\DataTable\DataTableFacade;
use App\Models\SafetyAndPrivacy;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class OperatorService
{
    public function dataTable(Request $request)
    {
        $user = auth()->user();

        $fields = [
            'id','lat','lon', 'recognize_picture','action_picture','created_at','step', 'final_description',
            'info_fa', 'activity_date_time', 'action_picture_document_upload_date', 'judiciary_document_upload_date',
            'step_fa', 'axis_type_id', 'axis_type_name', 'action_date', 'is_finished', 'status_fa', 'supervisor_description',
            'operator_description', 'finish_picture', 'evidence_picture', 'recognize_picture_second'
        ];

        throw_if(is_null($user->edarate_shahri_id), new ProhibitedAction('اداره ای برای شما در سامانه ثبت نشده است!'));

        $query = SafetyAndPrivacy::query()->where('edare_shahri_id', '=', $user->edarate_shahri_id);

        return DataTableFacade::run(
            $query,
            $request,
            allowedFilters: ['*'],
            allowedSortings: ['*'],
            allowedSelects: $fields
        );
    }

    public function countryActivity(int $info_id, Request $request): array
    {
        $params = [
            'infoId' => $info_id,
        ];

        $dateFields = [
            'created_at',
            'activity_date_time',
            'judiciary_document_upload_date',
            'action_date',
        ];


        foreach ($dateFields as $field) {
            $params["{$field}_from"] = null;
            $params["{$field}_to"]   = null;
            $params["{$field}_from_2"] = null;
            $params["{$field}_to_2"]   = null;
        }

        foreach (json_decode($request->filters, true) as $filter) {
            if ($filter['fn'] === "between")
            {
                $field = $filter['id'];
                $params["{$field}_from"] = $filter['value'][0] . ' 00:00:00';
                $params["{$field}_to"]   = $filter['value'][1] . ' 23:59:59';
                $params["{$field}_from_2"] = $filter['value'][0] . ' 00:00:00';
                $params["{$field}_to_2"]   = $filter['value'][1] . ' 23:59:59';
            }
        }

        return DB::select("
            WITH base AS (
                SELECT
                    p.id AS province_id,
                    p.name_fa AS province_name,
                    a.id AS axis_type_id,
                    COALESCE(SUM(t.step = 1), 0) AS s1,
                    COALESCE(SUM(t.step = 2), 0) AS s2,
                    COALESCE(SUM(t.step = 3), 0) AS s3
                FROM provinces p
                JOIN axis_types a 
                LEFT JOIN safety_and_privacy t
                       ON t.province_id = p.id
                      AND t.axis_type_id = a.id
                      AND t.info_id = :infoId
                      AND (
                              (:created_at_from IS NULL OR :created_at_to IS NULL)
                              OR t.created_at BETWEEN :created_at_from_2 AND :created_at_to_2)
                         AND (
                              (:activity_date_time_from IS NULL OR :activity_date_time_to IS NULL)
                              OR t.activity_date_time BETWEEN :activity_date_time_from_2 AND :activity_date_time_to_2
                         )
                         AND (
                              (:judiciary_document_upload_date_from IS NULL OR :judiciary_document_upload_date_to IS NULL)
                              OR t.judiciary_document_upload_date BETWEEN :judiciary_document_upload_date_from_2 AND :judiciary_document_upload_date_to_2
                         )
                         AND (
                              (:action_date_from IS NULL OR :action_date_to IS NULL)
                              OR t.action_date BETWEEN :action_date_from_2 AND :action_date_to_2
                         )
                GROUP BY p.id, a.id
            )
            SELECT
                province_id,
                province_name,
                axis_type_id,
                s1,
                s2,
                s3,
                COALESCE(
                    SUM(s1 + s2 + s3) 
                    OVER (PARTITION BY province_id), 0
                ) AS total_sum
            FROM base
            
            UNION ALL
            
            SELECT
                -1 AS province_id,
                'کل کشور' AS province_name,
                axis_type_id,
                COALESCE(SUM(s1), 0) AS s1,
                COALESCE(SUM(s2), 0) AS s2,
                COALESCE(SUM(s3), 0) AS s3,
                COALESCE(
                    SUM(SUM(s1 + s2 + s3)) OVER (), 0
                ) AS total_sum
            FROM base
            GROUP BY axis_type_id
            
            ORDER BY province_id, axis_type_id

    ",$params);
    }
    public function provinceActivity(Request $request, int $info_id): array
    {
        $params = [
            'infoId'     => $info_id,
        ];

        $dateFields = [
            'created_at',
            'activity_date_time',
            'judiciary_document_upload_date',
            'action_date',
        ];


        foreach ($dateFields as $field) {
            $params["{$field}_from"] = NULL;
            $params["{$field}_to"]   = NULL;
            $params["{$field}_from_2"] = NULL;
            $params["{$field}_to_2"]   = NULL;
        }

        foreach (json_decode($request->filters, true) as $filter) {
            if ($filter['fn'] === "between")
            {
                $field = $filter['id'];
                $params["{$field}_from"] = $filter['value'][0] . ' 00:00:00';
                $params["{$field}_to"]   = $filter['value'][1] . ' 23:59:59';
                $params["{$field}_from_2"] = $filter['value'][0] . ' 00:00:00';
                $params["{$field}_to_2"]   = $filter['value'][1] . ' 23:59:59';
            }
            if ($filter['id'] === "province_id")
            {
                $params["provinceId"] = $filter['value'];
            }
        }

        return DB::select("
             WITH edareh_data AS (
                  SELECT
                    e.id AS edare_id,
                    e.name_fa AS edare_name,
                    a.id AS axis_type_id,
                    COALESCE(SUM(t.step = 1), 0) AS s1,
                    COALESCE(SUM(t.step = 2), 0) AS s2,
                    COALESCE(SUM(t.step = 3), 0) AS s3
                  FROM edarate_shahri e
                  JOIN axis_types a ON 1
                  LEFT JOIN safety_and_privacy t
                         ON e.id = t.edare_shahri_id
                        AND t.axis_type_id = a.id
                        AND t.info_id = :infoId
                        AND (
                              (:created_at_from IS NULL OR :created_at_to IS NULL)
                              OR t.created_at BETWEEN :created_at_from_2 AND :created_at_to_2)
                         AND (
                              (:activity_date_time_from IS NULL OR :activity_date_time_to IS NULL)
                              OR t.activity_date_time BETWEEN :activity_date_time_from_2 AND :activity_date_time_to_2
                         )
                         AND (
                              (:judiciary_document_upload_date_from IS NULL OR :judiciary_document_upload_date_to IS NULL)
                              OR t.judiciary_document_upload_date BETWEEN :judiciary_document_upload_date_from_2 AND :judiciary_document_upload_date_to_2
                         )
                         AND (
                              (:action_date_from IS NULL OR :action_date_to IS NULL)
                              OR t.action_date BETWEEN :action_date_from_2 AND :action_date_to_2
                         )
                         WHERE e.province_id = :provinceId
                  GROUP BY e.id, a.id
                )
                SELECT
                   edare_id,
                   edare_name,
                  axis_type_id,
                  s1,
                  s2,
                  s3,
                  COALESCE(
                    SUM(s1 + s2 + s3)
                      OVER (PARTITION BY edare_id), 0
                  ) AS total_sum
                FROM edareh_data 
                
                UNION ALL
                
                SELECT
                  -1 AS edare_id,
                  'کل استان' AS edare_name,
                  axis_type_id,
                  COALESCE(SUM(s1), 0) AS s1,
                  COALESCE(SUM(s2), 0) AS s2,
                  COALESCE(SUM(s3), 0) AS s3,
                  COALESCE(
                    SUM(SUM(s1 + s2 + s3)) OVER (), 0
                  ) AS total_sum
                FROM edareh_data 
                GROUP BY axis_type_id
                
                ORDER BY edare_id , axis_type_id
    ", $params);
    }
}