<?php

namespace Modules\Dashboard\Repositories;

use Modules\Dashboard\Repositories\Contracts\PKJDashboardRepositoryInterface;
use Modules\PKJAssetEntitlement\Entities\PKJAssetEntitlement;
use Modules\PKJAssetHolding\Entities\PKJAssetHolding;
use Modules\PKJRepair\Entities\PKJRepair;
use Modules\PKJReport\Entities\PKJReport;
use Modules\PKJVariant\Entities\PKJCategory;
use Illuminate\Support\Facades\DB;
use Modules\Unit\Entities\Unit;
use App\Services\VisibilityService;
use Carbon\Carbon;

class PKJDashboardRepository implements PKJDashboardRepositoryInterface
{
    public function __construct(
        protected VisibilityService $visibilityService
    ) {}
    /**
     * Get total PKJ asset entitlement
     */
    public function getTotalEntitlement(array $filters = [], $user = null): int
    {
        $query = PKJAssetEntitlement::query();
        
        // Apply visibility scoping if user is provided
        if ($user) {
            $query->visibleTo($user);
        }
        
        $this->applyFilters($query, $filters, excludeDateFilter: true);
        
        return $query->sum('entitlement');
    }

    public function getTotalHoldings(array $filters = [], $user = null): int
    {
        $query = PKJAssetEntitlement::query();
        
        // Apply visibility scoping if user is provided
        if ($user) {
            $query->visibleTo($user);
        }
        
        $this->applyFilters($query, $filters, excludeDateFilter: true);
        
        return $query->sum('holding') ?? 0;
    }

    /**
     * Get PKJ asset holdings grouped by status
     */
    public function getHoldingsByStatus(array $filters = [], $user = null): array
    {
        $query = PKJAssetHolding::query();
        
        // Apply visibility scoping if user is provided
        if ($user) {
            $query->visibleTo($user);
        }
        
        $this->applyFilters($query, $filters);
        
        return $query->selectRaw('status, COUNT(*) as count')
            ->groupBy('status')
            ->orderBy('status')
            ->get()
            ->toArray();
    }

    /**
     * Get PKJ asset holdings by specific status
     */
    public function getHoldingsBySpecificStatus(string $status, array $filters = [], $user = null): array
    {
        $query = PKJAssetHolding::query();
        
        // Apply visibility scoping if user is provided
        if ($user) {
            $query->visibleTo($user);
        }
        
        $this->applyFilters($query, $filters);
        
        $holdings = $query->where('status', $status)->paginate(10);
        
        return [
            'data' => $holdings->items(),
            'pagination' => [
                'current_page' => $holdings->currentPage(),
                'per_page' => $holdings->perPage(),
                'total' => $holdings->total(),
                'last_page' => $holdings->lastPage(),
                'from' => $holdings->firstItem(),
                'to' => $holdings->lastItem()
            ]
        ];
    }

    /**
     * Get PKJ monthly trends
     */
    public function getMonthlyTrends(array $filters = [], $user = null): array
    {
        $dateRange = $this->getDateRange($filters);
        
        $query = PKJReport::query();
        
        // Apply visibility scoping if user is provided
        if ($user) {
            $query->visibleTo($user);
        }
        
        $trends = $query->selectRaw('
                TO_CHAR(created_at, \'YYYY-MM\') as month,
                COUNT(*) as total_reports
            ')
            ->whereBetween('created_at', [$dateRange['from'], $dateRange['to']])
            ->groupBy('month')
            ->orderBy('month')
            ->get()
            ->toArray();

        return $trends;
    }

    /**
     * Get PKJ holdings by category
     */
    public function getHoldingsByCategory(array $filters = [], $user = null): array
    {
        // Build a subquery for filtered entitlements
        // This ensures all categories are included, but only matching entitlements are summed
        $entitlementSubquery = PKJAssetEntitlement::query()
            ->select('pkj_category_id', DB::raw('SUM(holding) as total_holding'))
            ->groupBy('pkj_category_id');

        // Apply visibility scoping if user is provided
        if ($user) {
            $entitlementSubquery->visibleTo($user);
        }

        // Apply filters to entitlement subquery
        if (isset($filters['unit_id'])) {
            $entitlementSubquery->where('unit_id', $filters['unit_id']);
        }

        // Apply government_id filter
        if (isset($filters['government_id']) && $filters['government_id']) {
            $unitIds = $this->getUnitsByGovernment($filters['government_id']);
            if (!empty($unitIds)) {
                $entitlementSubquery->whereIn('unit_id', $unitIds);
            } else {
                // If no units match, set to empty result but don't filter categories
                $entitlementSubquery->whereRaw('1 = 0');
            }
        }

        // Apply formation_id filter
        if (isset($filters['formation_id']) && $filters['formation_id']) {
            $unitIds = Unit::where('formation_id', $filters['formation_id'])
                ->pluck('id')
                ->toArray();
            
            if (!empty($unitIds)) {
                $entitlementSubquery->whereIn('unit_id', $unitIds);
            } else {
                // If no units match, set to empty result but don't filter categories
                $entitlementSubquery->whereRaw('1 = 0');
            }
        }

        // Main query: Get ALL categories and LEFT JOIN with filtered entitlements
        // This ensures all categories are returned even if they have no matching entitlements
        $query = PKJCategory::query()
            ->leftJoinSub($entitlementSubquery, 'filtered_entitlements', function ($join) {
                $join->on('pkj_categories.id', '=', 'filtered_entitlements.pkj_category_id');
            })
            ->selectRaw('
                pkj_categories.id as category_id,
                pkj_categories.name as category_name,
                COALESCE(filtered_entitlements.total_holding, 0) as total_holding
            ')
            ->orderBy('pkj_categories.name');

        return $query->get()->toArray();
    }

    /**
     * Get PKJ category metrics (KEUPAYAAN, KESIAGAAN, SERVISIBILITI)
     */
    public function getCategoryMetrics(int $categoryId, array $filters = [], $user = null): array
    {
        // Get category name
        $category = PKJCategory::find($categoryId);
        $categoryName = $category ? $category->name : 'Unknown Category';

        // Get entitlement and holding data for the category
        $entitlementQuery = PKJAssetEntitlement::query()
            ->where('pkj_category_id', $categoryId);

        // Apply visibility scoping if user is provided
        if ($user) {
            $entitlementQuery->visibleTo($user);
        }

        // Apply filters
        if (isset($filters['unit_id'])) {
            $entitlementQuery->where('unit_id', $filters['unit_id']);
        }

        $entitlementData = $entitlementQuery->selectRaw('
            SUM(entitlement) as total_entitlement,
            SUM(holding) as total_holding
        ')->first();

        // Get operational/usable assets count (BDG, BT, BP statuses) for the category
        $operationalQuery = PKJAssetHolding::query()
            ->where('pkj_category_id', $categoryId)
            ->whereIn('status', ['BDG', 'BT', 'BP']);

        // Apply visibility scoping if user is provided
        if ($user) {
            $operationalQuery->visibleTo($user);
        }

        // Apply filters
        if (isset($filters['unit_id'])) {
            $operationalQuery->where('unit_id', $filters['unit_id']);
        }

        $operationalCount = $operationalQuery->count();

        // Get BDG, BT, BG count specifically for servisibiliti calculation
        $bdgQuery = PKJAssetHolding::query()
            ->where('pkj_category_id', $categoryId)
            ->whereIn('status', ['BDG', 'BT', 'BG']);

        // Apply visibility scoping if user is provided
        if ($user) {
            $bdgQuery->visibleTo($user);
        }

        // Apply filters
        if (isset($filters['unit_id'])) {
            $bdgQuery->where('unit_id', $filters['unit_id']);
        }

        $bdgCount = $bdgQuery->count();

        $totalEntitlement = $entitlementData ? $entitlementData->total_entitlement : 0;
        $totalHolding = $entitlementData ? $entitlementData->total_holding : 0;

        // Calculate metrics
        $keupayaan = $totalEntitlement > 0 ? ($totalHolding / $totalEntitlement) * 100 : 0;
        $kesiagaan = $totalEntitlement > 0 ? ($operationalCount / $totalEntitlement) * 100 : 0;
        $servisibiliti = $totalHolding > 0 ? ($bdgCount / $totalHolding) * 100 : 0;

        return [
            'category_id' => $categoryId,
            'category_name' => $categoryName,
            'keupayaan' => round($keupayaan, 2),
            'kesiagaan' => round($kesiagaan, 2),
            'servisibiliti' => round($servisibiliti, 2),
            'total_entitlement' => $totalEntitlement,
            'total_holding' => $totalHolding,
            'operational_count' => $operationalCount,
            'bdg_count' => $bdgCount
        ];
    }

    /**
     * Get PKJ category status breakdown
     */
    public function getCategoryStatusBreakdown(int $categoryId, array $filters = [], $user = null): array
    {
        // Get category name
        $category = PKJCategory::find($categoryId);
        $categoryName = $category ? $category->name : 'Unknown Category';

        $query = PKJAssetHolding::query()
            ->where('pkj_category_id', $categoryId)
            ->selectRaw('status, COUNT(*) as count')
            ->groupBy('status')
            ->orderBy('status');

        // Apply visibility scoping if user is provided
        if ($user) {
            $query->visibleTo($user);
        }

        // Apply filters
        if (isset($filters['unit_id'])) {
            $query->where('unit_id', $filters['unit_id']);
        }

        $statusCounts = $query->get()->toArray();

        return [
            'category_id' => $categoryId,
            'category_name' => $categoryName,
            'status_breakdown' => $statusCounts
        ];
    }

    /**
     * Get PKJ asset details by category and status.
     *
     * When the asset has no status yet, the status column in the database is NULL.
     * Callers pass status as '' or the string 'null' to request these "no status" assets.
     */
    public function getAssetDetailsByCategoryAndStatus(int $categoryId, string $status, array $filters = [], $user = null): array
    {
        // Handle no-status: API passes '' or 'null'; DB column is NULL for assets not yet assigned a status
        $status = trim($status ?? '');

        $query = PKJAssetHolding::query()
            ->with(['unit', 'pkjCategory', 'pkjSubcategory', 'pkjModel'])
            ->where('pkj_category_id', $categoryId);

        if ($status === '' || $status === 'null') {
            // Assets with status column NULL (not yet assigned a status)
            $query->whereNull('status');
        } else {
            // For specific status, must match exactly and NOT be null
            $query->whereNotNull('status')
                  ->where('status', '=', $status);
        }
        
        $query->select([
                'id',
                'uuid',
                'registration_number',
                'kewpa_registration_number',
                'status',
                'unit_id',
                'pkj_category_id',
                'pkj_subcategory_id',
                'pkj_model_id',
                'purchase_date',
                'receipt_date',
                'purchase_price',
                'contract_reference',
                'country_of_manufacture',
                'economic_year',
                'economic_distance',
                'note',
                'engine_number',
                'chassis_number',
                'created_at',
                'updated_at'
            ]);

        // Apply visibility scoping if user is provided
        if ($user) {
            $query->visibleTo($user);
        }

        // Apply filters
        if (isset($filters['unit_id'])) {
            $query->where('unit_id', $filters['unit_id']);
        }

        $perPage = isset($filters['per_page']) ? (int) $filters['per_page'] : 10;
        $page = isset($filters['page']) ? (int) $filters['page'] : null;
        $assets = $query->paginate($perPage, ['*'], 'page', $page);

        return [
            'data' => $assets->items(),
            'pagination' => [
                'current_page' => $assets->currentPage(),
                'per_page' => $assets->perPage(),
                'total' => $assets->total(),
                'last_page' => $assets->lastPage(),
                'from' => $assets->firstItem(),
                'to' => $assets->lastItem(),
                'has_more_pages' => $assets->hasMorePages()
            ]
        ];
    }

    /**
     * Apply common filters to query
     */
    private function applyFilters($query, array $filters = [], bool $excludeDateFilter = false): void
    {
        // Only apply created_at filter if the model has created_at column
        $model = $query->getModel();
        if ($model && method_exists($model, 'getTable')) {
            $schema = $model->getConnection()->getSchemaBuilder();
            $columns = $schema->getColumnListing($model->getTable());
            
            if (!$excludeDateFilter && in_array('created_at', $columns)) {
                $dateRange = $this->getDateRange($filters);
                $query->whereBetween('created_at', [$dateRange['from'], $dateRange['to']]);
            }
        }
        
        // Apply unit_id filter if model has unit_id column
        if (isset($filters['unit_id']) && $filters['unit_id']) {
            if ($model && method_exists($model, 'getTable')) {
                $columns = $schema->getColumnListing($model->getTable());
                if (in_array('unit_id', $columns)) {
                    $query->where('unit_id', $filters['unit_id']);
                }
            }
        }
        
        // Apply government_id filter if provided
        if (isset($filters['government_id']) && $filters['government_id']) {
            // Get units under the government
            $unitIds = $this->getUnitsByGovernment($filters['government_id']);
            
            if ($model && method_exists($model, 'getTable')) {
                $columns = $schema->getColumnListing($model->getTable());
                if (in_array('unit_id', $columns)) {
                    if (!empty($unitIds)) {
                        $query->whereIn('unit_id', $unitIds);
                    } else {
                        // No units found for this government - return empty result
                        $query->whereNull('id');
                    }
                }
            }
        }
        
        // Apply formation_id filter if provided
        if (isset($filters['formation_id']) && $filters['formation_id']) {
            if ($model && method_exists($model, 'getTable')) {
                $columns = $schema->getColumnListing($model->getTable());
                if (in_array('unit_id', $columns)) {
                    // Get units under this formation
                    $unitIds = Unit::where('formation_id', $filters['formation_id'])
                        ->pluck('id')
                        ->toArray();
                    
                    if (!empty($unitIds)) {
                        $query->whereIn('unit_id', $unitIds);
                    } else {
                        // No units found for this formation - return empty result
                        $query->whereNull('id');
                    }
                }
            }
        }
        
        // Apply camp_id filter if provided
        if (isset($filters['camp_id']) && $filters['camp_id']) {
            if ($model && method_exists($model, 'getTable')) {
                $columns = $schema->getColumnListing($model->getTable());
                if (in_array('camp_id', $columns)) {
                    $query->where('camp_id', $filters['camp_id']);
                }
            }
        }
    }

    /**
     * Get units by government ID
     * Uses VisibilityService for consistent logic
     */
    private function getUnitsByGovernment(int $governmentId): array
    {
        $governmentUnit = Unit::where('government_id', $governmentId)->first();
        if (!$governmentUnit) {
            // If no direct unit found, try to get from formation
            $formation = \Modules\Formation\Entities\Formation::where('government_id', $governmentId)->first();
            if ($formation) {
                $governmentUnit = Unit::where('formation_id', $formation->id)->first();
            }
        }
        
        if (!$governmentUnit) {
            return [];
        }
        
        return $this->visibilityService->getUnitsUnderGovernment($governmentUnit);
    }

    /**
     * Get date range for filtering
     */
    private function getDateRange(array $filters = []): array
    {
        $dateFrom = $filters['date_from'] ?? Carbon::now()->startOfYear();
        $dateTo = $filters['date_to'] ?? Carbon::now()->endOfYear();
        
        return [
            'from' => Carbon::parse($dateFrom)->startOfDay(),
            'to' => Carbon::parse($dateTo)->endOfDay()
        ];
    }
}