<?php

namespace App\Http\Controllers;

use App\Models\CaseRecord;
use App\Models\PoliceStation;
use App\Models\User;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class CaseStatisticsController extends Controller
{
    /**
     * Display overall case statistics.
     */
    public function index()
    {
        // Get case counts by status
        $casesByStatus = CaseRecord::select('status', DB::raw('count(*) as total'))
            ->groupBy('status')
            ->get()
            ->pluck('total', 'status')
            ->toArray();
            
        // Get case counts by priority
        $casesByPriority = CaseRecord::select('priority', DB::raw('count(*) as total'))
            ->groupBy('priority')
            ->get()
            ->pluck('total', 'priority')
            ->toArray();
            
        // Get case counts by month (last 12 months)
        $casesByMonth = CaseRecord::select(
                DB::raw('YEAR(created_at) as year'),
                DB::raw('MONTH(created_at) as month'),
                DB::raw('count(*) as total')
            )
            ->whereDate('created_at', '>=', now()->subMonths(12))
            ->groupBy('year', 'month')
            ->orderBy('year', 'asc')
            ->orderBy('month', 'asc')
            ->get();
            
        // Format month labels and data for chart
        $monthLabels = [];
        $monthData = [];
        
        foreach ($casesByMonth as $monthItem) {
            $date = \Carbon\Carbon::createFromDate($monthItem->year, $monthItem->month, 1);
            $monthLabels[] = $date->format('M Y');
            $monthData[] = $monthItem->total;
        }
        
        // Get total cases
        $totalCases = CaseRecord::count();
        $openCases = CaseRecord::whereIn('status', ['open', 'under_investigation', 'pending_review'])->count();
        $closedCases = CaseRecord::where('status', 'closed')->count();
        $criticalPriorityCases = CaseRecord::where('priority', 'critical')->count();
        
        return view('case-statistics.index', compact(
            'casesByStatus',
            'casesByPriority',
            'monthLabels',
            'monthData',
            'totalCases',
            'openCases',
            'closedCases',
            'criticalPriorityCases'
        ));
    }
    
    /**
     * Display cases by station statistics.
     */
    public function byStation()
    {
        // Get all stations
        $stations = PoliceStation::withCount(['cases'])->get();
        
        // Get case status breakdown by station
        $stationStatusBreakdown = PoliceStation::select(
                'police_stations.id',
                'police_stations.name',
                'cases.status',
                DB::raw('count(cases.id) as total')
            )
            ->leftJoin('cases', 'cases.station_id', '=', 'police_stations.id')
            ->groupBy('police_stations.id', 'police_stations.name', 'cases.status')
            ->get()
            ->groupBy('id');
            
        // Get stations with most cases
        $stationsWithMostCases = PoliceStation::withCount(['cases'])
            ->orderBy('cases_count', 'desc')
            ->take(5)
            ->get();
            
        // Get stations with most open cases
        $stationsWithMostOpenCases = PoliceStation::withCount(['cases' => function($query) {
                $query->whereIn('status', ['open', 'under_investigation', 'pending_review']);
            }])
            ->orderBy('cases_count', 'desc')
            ->take(5)
            ->get();
            
        return view('case-statistics.by-station', compact(
            'stations',
            'stationStatusBreakdown',
            'stationsWithMostCases',
            'stationsWithMostOpenCases'
        ));
    }
    
    /**
     * Display cases by officer statistics.
     */
    public function byOfficer()
    {
        // Get officers with case counts
        $officersWithCases = User::where('is_officer', true)
            ->withCount(['leadInvestigatorCases as lead_cases_count'])
            ->withCount(['reportingOfficerCases as reporting_cases_count'])
            ->having('lead_cases_count', '>', 0)
            ->orHaving('reporting_cases_count', '>', 0)
            ->orderBy('lead_cases_count', 'desc')
            ->get();
            
        // Get top investigators
        $topInvestigators = User::where('is_officer', true)
            ->withCount(['leadInvestigatorCases as cases_count'])
            ->orderBy('cases_count', 'desc')
            ->take(10)
            ->get();
            
        // Get top reporting officers
        $topReportingOfficers = User::where('is_officer', true)
            ->withCount(['reportingOfficerCases as cases_count'])
            ->orderBy('cases_count', 'desc')
            ->take(10)
            ->get();
            
        // Get case status breakdown by top investigators
        $investigatorCaseStatusBreakdown = [];
        foreach ($topInvestigators->take(5) as $officer) {
            $breakdown = CaseRecord::where('lead_investigator_id', $officer->id)
                ->select('status', DB::raw('count(*) as total'))
                ->groupBy('status')
                ->get()
                ->pluck('total', 'status')
                ->toArray();
                
            $investigatorCaseStatusBreakdown[$officer->id] = [
                'name' => $officer->name,
                'breakdown' => $breakdown
            ];
        }
        
        return view('case-statistics.by-officer', compact(
            'officersWithCases',
            'topInvestigators',
            'topReportingOfficers',
            'investigatorCaseStatusBreakdown'
        ));
    }
    
    /**
     * Display cases by crime type statistics.
     */
    public function byCrimeType()
    {
        // Define crime types
        $crimeTypes = [
            'assault' => 'Assault',
            'theft' => 'Theft',
            'robbery' => 'Robbery',
            'burglary' => 'Burglary',
            'fraud' => 'Fraud',
            'homicide' => 'Homicide',
            'sexual_assault' => 'Sexual Assault',
            'drug_related' => 'Drug Related',
            'cybercrime' => 'Cybercrime',
            'domestic_violence' => 'Domestic Violence',
            'other' => 'Other',
        ];
        
        // Get case counts by crime type
        $casesByCrimeType = CaseRecord::select('crime_type', DB::raw('count(*) as total'))
            ->groupBy('crime_type')
            ->get()
            ->pluck('total', 'crime_type')
            ->toArray();
            
        // Get case status breakdown by crime type
        $crimeTypeStatusBreakdown = CaseRecord::select(
                'crime_type',
                'status',
                DB::raw('count(*) as total')
            )
            ->groupBy('crime_type', 'status')
            ->get()
            ->groupBy('crime_type');
            
        // Get crime types by priority
        $crimeTypesByPriority = CaseRecord::select(
                'crime_type',
                'priority',
                DB::raw('count(*) as total')
            )
            ->groupBy('crime_type', 'priority')
            ->get()
            ->groupBy('crime_type');
            
        // Get crime types by month (last 12 months)
        $crimeTypesByMonth = CaseRecord::select(
                'crime_type',
                DB::raw('YEAR(created_at) as year'),
                DB::raw('MONTH(created_at) as month'),
                DB::raw('count(*) as total')
            )
            ->whereDate('created_at', '>=', now()->subMonths(12))
            ->groupBy('crime_type', 'year', 'month')
            ->orderBy('year', 'asc')
            ->orderBy('month', 'asc')
            ->get()
            ->groupBy('crime_type');
            
        return view('case-statistics.by-crime-type', compact(
            'crimeTypes',
            'casesByCrimeType',
            'crimeTypeStatusBreakdown',
            'crimeTypesByPriority',
            'crimeTypesByMonth'
        ));
    }
}
