<?php

namespace App\Http\Controllers;

use App\Models\Document;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Response;

class IndexController extends Controller
{
    public function index()
    {
        $publicStats = [
            'reported_cases' => DB::table('victim')->count(),
            'incident_records' => DB::table('incident_data')->count(),
            'districts' => DB::table('districts')->count(),
            'resources' => DB::table('documents')->where('is_deleted', 0)->count(),
        ];
        return view('index.index', compact('publicStats'));
    }

    public function contact()
    {
        return view('index.contact');
    }

    public function dashboard()
    {
        return view('index.dashboard');
    }

    public function resources()
    {
        $resources = Document::where('is_deleted', '=', '0')->get();
        return view('index.resources', compact('resources'));
    }

    public function all()
    {
        return view('index.all');
    }

    public function gender()
    {
        return view('index.sex');
    }

    public function age()
    {
        return view('index.age');
    }

    public function pwd()
    {
        return view('index.pwd');
    }

    public function disability()
    {
        return $this->pwd();
    }

    public function regions()
    {
        $data['regions'] = DB::table('incident_data as id')
            ->select('r.name as name', DB::raw('count(id.id) as y'))
            ->leftJoin('regions as r', 'id.regions_id', '=', 'r.id')
            ->groupBy('r.name')
            ->get();

        return view('index.regioncharts', $data);
    }

    public function district()
    {
        return view('index.district');
    }

    public function mapdata(Request $request)
    {
        $validated = $request->validate([
            'district_id' => ['nullable', 'integer', 'min:1', 'exists:districts,id'],
        ]);

        if (!empty($validated['district_id'])) {
            $districtId = (int) $validated['district_id'];
            $district = DB::table('districts as d')
                ->leftJoin('regions as r', 'd.regions_id', '=', 'r.id')
                ->leftJoin('sub_regions as sr', 'd.sub_regions_id', '=', 'sr.id')
                ->select('d.id', 'd.name', 'd.latitudes', 'd.longitudes', 'r.name as region', 'sr.name as sub_region')
                ->where('d.id', $districtId)
                ->first();

            $total = DB::table('incident_data')->where('districtid', $districtId)->count();
            $categories = DB::table('incident_data as id')
                ->leftJoin('incidents as i', 'id.incidentid', '=', 'i.id')
                ->select(DB::raw("COALESCE(i.name, 'Unspecified') as name"), DB::raw('COUNT(id.id) as total'))
                ->where('id.districtid', $districtId)
                ->groupBy('i.name')
                ->orderByDesc('total')
                ->limit(10)
                ->get();

            $gender = DB::table('incident_data as id')
                ->leftJoin('victim as v', 'id.victimid', '=', 'v.id')
                ->leftJoin('gender as g', 'v.gender', '=', 'g.id')
                ->select(DB::raw("COALESCE(g.name, 'Unspecified') as name"), DB::raw('COUNT(id.id) as total'))
                ->where('id.districtid', $districtId)
                ->groupBy('g.name')
                ->orderByDesc('total')
                ->get();

            return Response::json([
                'district' => $district,
                'total' => $total,
                'categories' => $categories,
                'gender' => $gender,
            ]);
        }

        $incidents = DB::table('incident_data as id')
            ->select('d.id as district_id', 'd.name', 'd.longitudes', 'd.latitudes', DB::raw('count(id.id) as incidents'))
            ->leftJoin('districts as d', 'id.districtid', '=', 'd.id')
            ->whereNotNull('d.latitudes')
            ->whereNotNull('d.longitudes')
            ->groupBy('d.id', 'd.name', 'd.longitudes', 'd.latitudes')
            ->get();

        return Response::json($incidents);
    }
}
