<?php

namespace App\Http\Controllers\Api;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Carbon\Carbon;

class DashboardController extends Controller
{
    public function getStats(Request $request)
    {
        $filters = $this->buildFilters($request);
        
        $stats = [
            'totalRequests' => $this->getTotalCreditRequests($filters),
            'grantedCredits' => $this->getGrantedCredits($filters),
            'repaymentRate' => $this->getRepaymentRate($filters),
            'avgLoanSize' => $this->getAverageLoanSize($filters)
        ];

        return response()->json($stats);
    }

    public function getAlerts()
    {
        $alerts = [
            [
                'title' => 'Repayment Delay Detected',
                'description' => $this->getDelayedRepaymentCount() . ' clients with delayed payments',
                'type' => 'critical',
                'icon' => 'exclamation-circle'
            ],
            [
                'title' => 'Upcoming Field Visits',
                'description' => $this->getUpcomingVisitsCount() . ' visits scheduled',
                'type' => 'warning',
                'icon' => 'exclamation-triangle'
            ],
            [
                'title' => 'Credit Applications Pending',
                'description' => $this->getPendingApplicationsCount() . ' new applications pending review',
                'type' => 'info',
                'icon' => 'info-circle'
            ]
        ];

        return response()->json($alerts);
    }

    public function getActivities(Request $request)
    {
        $filters = $this->buildFilters($request);
        
        $activities = DB::table('credit_requests as cr')
            ->select(
                DB::raw("CONCAT(p.first_name, ' ', p.last_name) as client"),
                DB::raw("CASE 
                    WHEN cr.status = 'approved' THEN 'Credit approved'
                    WHEN cr.status = 'pending' THEN 'New application submitted'
                    WHEN cr.status = 'rejected' THEN 'Credit application rejected'
                    ELSE 'Credit status updated'
                END as activity"),
                'u.name as agent',
                'cr.updated_at as date',
                DB::raw("CASE 
                    WHEN cr.status = 'approved' THEN 'completed'
                    WHEN cr.status = 'pending' THEN 'pending'
                    ELSE 'critical'
                END as status")
            )
            ->join('clients as c', 'cr.client_id', '=', 'c.id')
            ->join('persons as p', 'c.id', '=', 'p.client_id')
            ->leftJoin('users as u', 'cr.created_by', '=', 'u.id')
            ->when($filters['dateFrom'], function($query) use ($filters) {
                $query->where('cr.updated_at', '>=', $filters['dateFrom']);
            })
            ->when($filters['dateTo'], function($query) use ($filters) {
                $query->where('cr.updated_at', '<=', $filters['dateTo']);
            })
            ->orderBy('cr.updated_at', 'desc')
            ->limit(10)
            ->get();

        return response()->json($activities);
    }

    public function getSectorDistribution(Request $request)
    {
        $distribution = DB::table('client_contribution_summaries as ccs')
            ->select('ccs.seed_name as sector', DB::raw('COUNT(*) as count'))
            ->whereNotNull('ccs.seed_name')
            ->groupBy('ccs.seed_name')
            ->get();

        return response()->json($distribution);
    }

    private function buildFilters(Request $request)
    {
        return [
            'season' => $request->input('season'),
            'status' => $request->input('status'),
            'dateFrom' => $request->input('fromDate'),
            'dateTo' => $request->input('toDate')
        ];
    }

    private function getTotalCreditRequests($filters)
    {
        return DB::table('credit_requests')
            ->when($filters['dateFrom'], function($query) use ($filters) {
                $query->where('created_at', '>=', $filters['dateFrom']);
            })
            ->when($filters['dateTo'], function($query) use ($filters) {
                $query->where('created_at', '<=', $filters['dateTo']);
            })
            ->count();
    }

    private function getGrantedCredits($filters)
    {
        return DB::table('credits_deductables')
            ->where('status', 'approved')
            ->when($filters['dateFrom'], function($query) use ($filters) {
                $query->where('created_at', '>=', $filters['dateFrom']);
            })
            ->when($filters['dateTo'], function($query) use ($filters) {
                $query->where('created_at', '<=', $filters['dateTo']);
            })
            ->sum('requested_amount');
    }

    private function getRepaymentRate($filters)
    {
        // Simplified calculation - you might want to implement more complex logic
        $totalCredits = DB::table('credits_deductables')->where('status', 'approved')->count();
        $repaidCredits = DB::table('creditdedpayments')->count();
        
        return $totalCredits > 0 ? round(($repaidCredits / $totalCredits) * 100, 1) : 0;
    }

    private function getAverageLoanSize($filters)
    {
        return DB::table('credits_deductables')
            ->where('status', 'approved')
            ->when($filters['dateFrom'], function($query) use ($filters) {
                $query->where('created_at', '>=', $filters['dateFrom']);
            })
            ->when($filters['dateTo'], function($query) use ($filters) {
                $query->where('created_at', '<=', $filters['dateTo']);
            })
            ->avg('requested_amount') ?? 0;
    }

    private function getDelayedRepaymentCount()
    {
        // Implement your delayed repayment logic
        return DB::table('credits_deductables')
            ->where('status', 'approved')
            ->whereNotExists(function ($query) {
                $query->select(DB::raw(1))
                    ->from('creditdedpayments')
                    ->whereRaw('creditdedpayments.credit_id = credits_deductables.id');
            })
            ->count();
    }

    private function getUpcomingVisitsCount()
    {
        return DB::table('scheduled_visits')
            ->where('scheduled_date', '>=', now())
            ->where('scheduled_date', '<=', now()->addWeek())
            ->count();
    }

    private function getPendingApplicationsCount()
    {
        return DB::table('credit_requests')
            ->where('status', 'pending')
            ->count();
    }
}