<?php

use App\Models\PunchList;
use App\Models\TestPackage;
use App\Services\Dashboards\TestPackageDashboardService;
use Illuminate\Support\Facades\DB;

$projectMap = TestPackageDashboardService::projectMap();

$punchStats = PunchList::select('test_package', 'category', 'status', DB::raw('COUNT(*) as qty'))
    ->groupBy('test_package', 'category', 'status')
    ->get();

$punchMap = [];

foreach ($punchStats as $stat) {
    $testPackageNo = $stat->test_package ?? '';
    if ($testPackageNo === '') {
        continue;
    }

    $category = strtoupper($stat->category ?? '');
    if (!in_array($category, ['A', 'B', 'C'])) {
        continue;
    }

    $status = strtolower($stat->status ?? '');
    $qty = (int) $stat->qty;

    if (!isset($punchMap[$testPackageNo][$category])) {
        $punchMap[$testPackageNo][$category] = [
            'open' => 0,
            'closed' => 0,
            'cancel' => 0,
        ];
    }

    if ($status === 'closed') {
        $punchMap[$testPackageNo][$category]['closed'] += $qty;
    } elseif ($status === 'cancel' || $status === 'canceled') {
        $punchMap[$testPackageNo][$category]['cancel'] += $qty;
    } else {
        $punchMap[$testPackageNo][$category]['open'] += $qty;
    }
}

$testPackages = TestPackage::select(
    'test_package_number',
    'unit',
    'subcontractor',
    'test_type',
    'ndt_status',
    'tp_general_status',
    'test_status',
    'a_punch_point_open',
    'b_punch_point_open',
    'c_punch_point_open'
)->get();

$rows = [];
$categories = ['A', 'B', 'C'];

foreach ($testPackages as $testPackage) {
    $tpNumber = $testPackage->test_package_number;
    if (!$tpNumber) {
        continue;
    }

    // Normalize values (trim for case-insensitive comparison)
    $projectRaw = trim($projectMap[$tpNumber] ?? 'Unknown');
    $locationRaw = trim($testPackage->unit ?: 'Unknown');
    $subcontractorRaw = trim($testPackage->subcontractor ?: 'Unassigned');
    $testTypeRaw = trim($testPackage->test_type ?: 'Unknown');
    
    // Create normalized display values (ucwords for title case - each word capitalized)
    $project = ($projectRaw === 'Unknown' || $projectRaw === '') ? 'Unknown' : ucwords(strtolower($projectRaw));
    $location = ($locationRaw === 'Unknown' || $locationRaw === '') ? 'Unknown' : ucwords(strtolower($locationRaw));
    $subcontractor = ($subcontractorRaw === 'Unassigned' || $subcontractorRaw === '') ? 'Unassigned' : ucwords(strtolower($subcontractorRaw));
    $testType = ($testTypeRaw === 'Unknown' || $testTypeRaw === '') ? 'Unknown' : ucwords(strtolower($testTypeRaw));

    // Create case-insensitive key for merging (lowercase)
    $key = strtolower(implode('|', [$projectRaw, $locationRaw, $subcontractorRaw, $testTypeRaw]));

    if (!isset($rows[$key])) {
        $rows[$key] = [
            'project' => $project,
            'location' => $location,
            'subcontractor' => $subcontractor,
            'test_type' => $testType,
            'tp_general_status' => $testPackage->tp_general_status ?? '',
            'total_packages' => 0,
            'tested' => 0,
            'not_tested' => 0,
            'ready_for_test' => 0,
            'accepted' => 0,
            'rejected' => 0,
            'a_accepted' => 0,
            'a_not_tested' => 0,
            'a_rejected' => 0,
            'b_accepted' => 0,
            'b_not_tested' => 0,
            'b_rejected' => 0,
            'c_accepted' => 0,
            'c_not_tested' => 0,
            'c_rejected' => 0,
        ];
    }

    $rows[$key]['total_packages']++;

    $testStatus = strtolower($testPackage->test_status ?? '');
    $generalStatus = strtolower($testPackage->tp_general_status ?? '');
    $ndtStatus = strtolower($testPackage->ndt_status ?? '');

    if ($testStatus === 'accepted' || $generalStatus === 'completed') {
        $rows[$key]['tested']++;
    } else {
        $rows[$key]['not_tested']++;
    }

    if (in_array($generalStatus, ['ready for test', 'ready for reinstatement', 'completed'])) {
        $rows[$key]['ready_for_test']++;
    }

    if ($ndtStatus === 'accepted') {
        $rows[$key]['accepted']++;
    } else {
        $rows[$key]['rejected']++;
    }

    foreach ($categories as $category) {
        $categoryKey = strtolower($category);
        $stats = $punchMap[$tpNumber][$category] ?? null;

        if ($stats) {
            $rows[$key]["{$categoryKey}_accepted"] += $stats['closed'];
            $rows[$key]["{$categoryKey}_not_tested"] += $stats['cancel'];
            $rows[$key]["{$categoryKey}_rejected"] += $stats['open'];
        } else {
            $fallback = (int) $testPackage->{$categoryKey . '_punch_point_open'};
            $rows[$key]["{$categoryKey}_rejected"] += $fallback;
        }
    }
}

echo json_encode_tr(array_values($rows));