<?php
// app/Livewire/Dashboard.php
namespace App\Livewire;

use App\Models\File;
use App\Models\Category;
use App\Models\Stage;
use App\Models\FileStage;
use App\Models\NoticeUpdate;
use App\Models\FileAttachment;
use App\Models\User;
use Livewire\Component;
use Illuminate\Support\Facades\DB;

class Dashboard extends Component
{
    public $stats = [];
    public $recentFiles = [];
    public $recentNotices = [];
    public $recentAttachments = [];
    public $stageDistribution = [];
    public $monthlyStats = [];
    public $topCategories = [];
    public $activityTimeline = [];

    public function mount()
    {
        $this->loadStats();
        $this->loadRecentFiles();
        $this->loadRecentNotices();
        $this->loadRecentAttachments();
        $this->loadStageDistribution();
        $this->loadMonthlyStats();
        $this->loadTopCategories();
        $this->loadActivityTimeline();
    }

    public function loadStats()
    {
        $this->stats = [
            'total_files' => File::count(),
            'total_categories' => Category::count(),
            'total_stages' => Stage::count(),
            'total_assignments' => FileStage::count(),
            'total_notices' => NoticeUpdate::count(),
            'total_attachments' => FileAttachment::count(),
            'total_users' => User::count(),
            'active_assignments' => FileStage::where('active', true)->count(),
            'active_files' => File::where('active', true)->count(),
            'active_notices' => NoticeUpdate::where('active', true)->count(),
        ];
    }

    public function loadRecentFiles()
    {
        $this->recentFiles = File::with(['category'])
            ->orderBy('created_at', 'desc')
            ->limit(5)
            ->get();
    }

    public function loadRecentNotices()
    {
        $this->recentNotices = NoticeUpdate::with(['file', 'user_created'])
            ->orderBy('created_at', 'desc')
            ->limit(5)
            ->get();
    }

    public function loadRecentAttachments()
    {
        $this->recentAttachments = FileAttachment::with(['file', 'noticeUpdate', 'createdBy'])
            ->orderBy('created_at', 'desc')
            ->limit(5)
            ->get();
    }

    public function loadStageDistribution()
    {
        $this->stageDistribution = Stage::withCount(['fileAssignments' => function($query) {
                $query->where('active', true);
            }])
            ->orderBy('file_assignments_count', 'desc')
            ->limit(10)
            ->get()
            ->map(function($stage) {
                return [
                    'name' => $stage->name,
                    'count' => $stage->file_assignments_count,
                ];
            });
    }

    public function loadMonthlyStats()
    {
        $currentYear = now()->year;
        
        // PostgreSQL compatible queries using EXTRACT
        $filesByMonth = File::select(
                DB::raw('EXTRACT(MONTH FROM created_at) as month'),
                DB::raw('COUNT(*) as count')
            )
            ->whereYear('created_at', $currentYear)
            ->groupBy(DB::raw('EXTRACT(MONTH FROM created_at)'))
            ->orderBy(DB::raw('EXTRACT(MONTH FROM created_at)'))
            ->get()
            ->keyBy('month');

        $noticesByMonth = NoticeUpdate::select(
                DB::raw('EXTRACT(MONTH FROM created_at) as month'),
                DB::raw('COUNT(*) as count')
            )
            ->whereYear('created_at', $currentYear)
            ->groupBy(DB::raw('EXTRACT(MONTH FROM created_at)'))
            ->orderBy(DB::raw('EXTRACT(MONTH FROM created_at)'))
            ->get()
            ->keyBy('month');

        $attachmentsByMonth = FileAttachment::select(
                DB::raw('EXTRACT(MONTH FROM created_at) as month'),
                DB::raw('COUNT(*) as count')
            )
            ->whereYear('created_at', $currentYear)
            ->groupBy(DB::raw('EXTRACT(MONTH FROM created_at)'))
            ->orderBy(DB::raw('EXTRACT(MONTH FROM created_at)'))
            ->get()
            ->keyBy('month');

        $months = [];
        for ($i = 1; $i <= 12; $i++) {
            $months[] = [
                'month' => date('F', mktime(0, 0, 0, $i, 1)),
                'files' => isset($filesByMonth[$i]) ? (int)$filesByMonth[$i]->count : 0,
                'notices' => isset($noticesByMonth[$i]) ? (int)$noticesByMonth[$i]->count : 0,
                'attachments' => isset($attachmentsByMonth[$i]) ? (int)$attachmentsByMonth[$i]->count : 0,
            ];
        }

        $this->monthlyStats = $months;
    }

    public function loadTopCategories()
    {
        $this->topCategories = Category::withCount('files')
            ->orderBy('files_count', 'desc')
            ->limit(5)
            ->get();
    }

    public function loadActivityTimeline()
    {
        // Combine recent activities from different sources with PostgreSQL compatibility
        $fileActivities = File::with('user_created')
            ->select('id', 'name', 'created_by', 'created_at')
            ->selectRaw("'file' as type")
            ->selectRaw("name as title")
            ->limit(5)
            ->get();

        $noticeActivities = NoticeUpdate::with('createdBy')
            ->select('id', 'notice_description as name', 'created_by', 'created_at')
            ->selectRaw("'notice' as type")
            ->selectRaw("notice_description as title")
            ->limit(5)
            ->get();

        $assignmentActivities = FileStage::with(['createdBy', 'file', 'stage'])
            ->select('id', 'file_id', 'stage_id', 'created_by', 'created_at', 'comment')
            ->selectRaw("'assignment' as type")
            ->limit(5)
            ->get();

        $activities = collect()
            ->merge($fileActivities)
            ->merge($noticeActivities)
            ->merge($assignmentActivities)
            ->sortByDesc('created_at')
            ->take(10)
            ->values();

        $this->activityTimeline = $activities;
    }

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