citrus-cms/app/Services/Dashboards/TestPackageDashboardService.php

<?php

namespace App\Services\Dashboards;

use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;

class TestPackageDashboardService
{
    /**
     * Build summary grouped by test type, area and subcontractor.
     */
    public static function summaryByTestType(): array
    {
        $detailRows = self::fetchSummaryRows([
            ['column' => 'test_type', 'alias' => 'test_type', 'default' => 'Unknown'],
            ['column' => 'area', 'alias' => 'area', 'default' => 'Unknown'],
            ['column' => 'subcontractor', 'alias' => 'subcontractor', 'default' => 'Unassigned'],
        ]);

        $groupTotals = self::buildGroupTotals(
            $detailRows,
            'test_type',
            [
                'area' => 'Grand Total',
                'subcontractor' => 'All Subcontractors',
            ]
        );

        $overall = self::buildOverallRow(
            $detailRows,
            [
                'test_type' => 'All Test Types',
                'area' => 'Overall Grand Total',
                'subcontractor' => 'All Subcontractors',
            ]
        );

        return self::finalizeRows(array_merge($detailRows, $groupTotals, [$overall]));
    }

    /**
     * Build summary grouped by area, subcontractor and responsible person.
     */
    public static function summaryByArea(): array
    {
        $detailRows = self::fetchSummaryRows([
            ['column' => 'area', 'alias' => 'area', 'default' => 'Unknown'],
            ['column' => 'subcontractor', 'alias' => 'subcontractor', 'default' => 'Unassigned'],
            ['column' => 'responsible_person', 'alias' => 'responsible_person', 'default' => 'Unassigned'],
        ]);

        $groupTotals = self::buildGroupTotals(
            $detailRows,
            'area',
            [
                'subcontractor' => 'All Subcontractors',
                'responsible_person' => 'All Responsible',
            ]
        );

        $overall = self::buildOverallRow(
            $detailRows,
            [
                'area' => 'Overall Grand Total',
                'subcontractor' => 'All Subcontractors',
                'responsible_person' => 'All Responsible',
            ]
        );

        return self::finalizeRows(array_merge($detailRows, $groupTotals, [$overall]));
    }

    /**
     * Normalized dataset that powers pivot views (test type + area perspectives).
     */
    public static function statusPivotDataset(): array
    {
        $baseRows = self::finalizeRows(self::fetchSummaryRows([
            ['column' => 'area', 'alias' => 'area', 'default' => 'Unknown'],
            ['column' => 'test_type', 'alias' => 'test_type', 'default' => 'Unknown'],
            ['column' => 'subcontractor', 'alias' => 'subcontractor', 'default' => 'Unassigned'],
            ['column' => 'responsible_person', 'alias' => 'responsible_person', 'default' => 'Unassigned'],
        ]));

        return [
            'rows' => $baseRows,
            'normalized' => self::expandRowsForPivot($baseRows),
        ];
    }

    /**
     * Return monthly trend, pie and subcontractor/project trend datasets.
     */
    public static function monthlyTrends(int $year): array
    {
        $periodExpr = "COALESCE(tpbs.target_test_date, tpbs.created_at)";
        $monthExpr = "DATE_FORMAT($periodExpr, '%Y-%m-01')";
        $labelExpr = "DATE_FORMAT($periodExpr, '%b %Y')";
        $testedCondition = self::baseStatusTestedCondition('tpbs');

        $projectData = DB::table('test_pack_base_statuses as tpbs')
            ->leftJoinSub(self::projectMapSubquery(), 'projects', function ($join) {
                $join->on('projects.test_package_no', '=', 'tpbs.test_package_no');
            })
            ->selectRaw("
                COALESCE(projects.project, 'Unknown') as project,
                $monthExpr as period_start,
                $labelExpr as period_label,
                COUNT(*) as total_packages,
                SUM(CASE WHEN ($testedCondition) THEN 1 ELSE 0 END) as tested_packages
            ")
            ->whereYear(DB::raw($periodExpr), $year)
            ->groupBy('project', DB::raw($monthExpr), DB::raw($labelExpr))
            ->orderBy(DB::raw($monthExpr))
            ->orderBy('project')
            ->get()
            ->map(function ($row) {
                $row = (array) $row;
                $row['total_packages'] = (int) $row['total_packages'];
                $row['tested_packages'] = (int) $row['tested_packages'];
                $row['remaining_packages'] = max($row['total_packages'] - $row['tested_packages'], 0);
                $row['period_label'] = $row['period_label'] ?? '';
                $row['series_key'] = $row['project'];
                $row['display_label'] = trim(($row['project'] ?? 'Unknown') . ' | ' . ($row['period_label'] ?? ''));
                return $row;
            })
            ->toArray();

        $totals = self::calculateMonthlyTotals($projectData);

        $subcontractorData = DB::table('test_pack_base_statuses as tpbs')
            ->leftJoinSub(self::projectMapSubquery(), 'projects', function ($join) {
                $join->on('projects.test_package_no', '=', 'tpbs.test_package_no');
            })
            ->selectRaw("
                COALESCE(projects.project, 'Unknown') as project,
                COALESCE(tpbs.subcontractor, 'Unassigned') as subcontractor,
                $monthExpr as period_start,
                $labelExpr as period_label,
                SUM(CASE WHEN ($testedCondition) THEN 1 ELSE 0 END) as tested_packages
            ")
            ->whereYear(DB::raw($periodExpr), $year)
            ->groupBy('project', 'subcontractor', DB::raw($monthExpr), DB::raw($labelExpr))
            ->orderBy(DB::raw($monthExpr))
            ->orderBy('project')
            ->orderBy('subcontractor')
            ->get()
            ->map(function ($row) {
                $row = (array) $row;
                $row['tested_packages'] = (int) $row['tested_packages'];
                $row['series_key'] = trim(($row['project'] ?? 'Unknown') . ' - ' . ($row['subcontractor'] ?? 'Unassigned'));
                return $row;
            })
            ->toArray();

        return [
            'monthly' => $projectData,
            'pie' => [
                ['status' => 'Tested', 'value' => $totals['tested']],
                ['status' => 'Remaining', 'value' => $totals['remaining']],
            ],
            'summary' => $totals,
            'subcontractor' => $subcontractorData,
        ];
    }

    /**
     * Map of test_package_no => project.
     */
    public static function projectMap(): array
    {
        return self::projectMapSubquery()
            ->pluck('project', 'test_package_no')
            ->toArray();
    }

    /**
     * Fetch aggregated rows based on provided grouping definition.
     */
    protected static function fetchSummaryRows(array $grouping): array
    {
        $selectParts = [];
        $groupParts = [];

        foreach ($grouping as $definition) {
            $expression = "COALESCE({$definition['column']}, '{$definition['default']}')";
            $selectParts[] = "{$expression} as {$definition['alias']}";
            $groupParts[] = DB::raw($expression);
        }

        $select = implode(', ', array_merge($selectParts, self::metricExpressions()));

        $query = DB::table('test_pack_base_statuses')
            ->selectRaw($select)
            ->groupBy(...$groupParts);

        foreach ($grouping as $definition) {
            $query->orderBy($definition['alias']);
        }

        return $query->get()
            ->map(function ($row) {
                $data = (array) $row;
                foreach (self::numericColumns() as $column) {
                    if (isset($data[$column])) {
                        $data[$column] = (float) $data[$column];
                    }
                }
                $data['sort_index'] = 0;
                $data['is_total_row'] = false;
                return $data;
            })
            ->toArray();
    }

    protected static function buildGroupTotals(array $rows, string $groupField, array $overrides): array
    {
        $numeric = self::numericColumns();
        $groupTotals = [];

        foreach ($rows as $row) {
            $groupValue = $row[$groupField] ?? 'Unknown';
            if (!isset($groupTotals[$groupValue])) {
                $groupTotals[$groupValue] = array_fill_keys($numeric, 0);
            }
            foreach ($numeric as $column) {
                $groupTotals[$groupValue][$column] += (float) ($row[$column] ?? 0);
            }
        }

        $result = [];
        foreach ($groupTotals as $groupValue => $values) {
            $result[] = array_merge(
                [
                    $groupField => $groupValue,
                    'sort_index' => 1,
                    'is_total_row' => true,
                ],
                $overrides,
                $values
            );
        }

        return $result;
    }

    protected static function buildOverallRow(array $rows, array $overrides): array
    {
        $numeric = self::numericColumns();
        $totals = array_fill_keys($numeric, 0);

        foreach ($rows as $row) {
            foreach ($numeric as $column) {
                $totals[$column] += (float) ($row[$column] ?? 0);
            }
        }

        return array_merge(
            $overrides,
            $totals,
            [
                'sort_index' => 2,
                'is_total_row' => true,
            ]
        );
    }

    protected static function finalizeRows(array $rows): array
    {
        $wdiColumns = self::wdiColumns();

        return array_values(array_map(function ($row) use ($wdiColumns) {
            foreach ($wdiColumns as $column) {
                if (isset($row[$column])) {
                    $row[$column] = round((float) $row[$column], 2);
                }
            }
            return $row;
        }, $rows));
    }

    /**
     * Convert aggregated summary rows into normalized pivot-friendly records.
     */
    protected static function expandRowsForPivot(array $rows): array
    {
        $blueprint = self::pivotMetricBlueprint();
        $normalized = [];

        foreach ($rows as $row) {
            $base = [
                'area' => $row['area'] ?? 'Unknown',
                'test_type' => $row['test_type'] ?? 'Unknown',
                'subcontractor' => $row['subcontractor'] ?? 'Unassigned',
                'responsible_person' => $row['responsible_person'] ?? 'Unassigned',
            ];

            foreach ($blueprint as $group => $definitions) {
                foreach ($definitions as $definition) {
                    $column = $definition['column'];
                    $value = (float) ($row[$column] ?? 0);

                    $normalized[] = array_merge($base, [
                        'metric_group' => $group,
                        'metric_label' => $definition['label'],
                        'metric_key' => $column,
                        'metric_type' => $definition['type'],
                        'value' => round($value, 2),
                    ]);
                }
            }
        }

        return $normalized;
    }

    /**
     * Blueprint describing how numeric columns should be exposed inside the pivot grids.
     */
    protected static function pivotMetricBlueprint(): array
    {
        return [
            'Total TP' => [
                ['label' => 'Qty', 'column' => 'total_tp_qty', 'type' => 'qty'],
                ['label' => 'WDI', 'column' => 'total_tp_wdi', 'type' => 'wdi'],
            ],
            'Test Package Preparation' => [
                ['label' => 'Qty', 'column' => 'prep_qty', 'type' => 'qty'],
                ['label' => 'WDI', 'column' => 'prep_wdi', 'type' => 'wdi'],
            ],
            'Approved TP' => [
                ['label' => 'Qty', 'column' => 'approved_qty', 'type' => 'qty'],
                ['label' => 'WDI', 'column' => 'approved_wdi', 'type' => 'wdi'],
            ],
            'Line Check' => [
                ['label' => 'Qty', 'column' => 'linecheck_qty', 'type' => 'qty'],
                ['label' => 'WDI', 'column' => 'linecheck_wdi', 'type' => 'wdi'],
            ],
            'Punch Points' => [
                ['label' => 'A Punch', 'column' => 'a_punch_qty', 'type' => 'count'],
                ['label' => 'B Punch', 'column' => 'b_punch_qty', 'type' => 'count'],
                ['label' => 'C Punch', 'column' => 'c_punch_qty', 'type' => 'count'],
            ],
            'TP Released for NDT Clear' => [
                ['label' => 'Qty', 'column' => 'tp_released_qty', 'type' => 'qty'],
                ['label' => 'WDI', 'column' => 'tp_released_wdi', 'type' => 'wdi'],
            ],
            'Status Summary' => [
                ['label' => 'B Waiting', 'column' => 'b_waiting_qty', 'type' => 'status'],
                ['label' => 'NDT', 'column' => 'ndt_status_qty', 'type' => 'status'],
                ['label' => 'Punch', 'column' => 'punch_status_qty', 'type' => 'status'],
                ['label' => 'Ready for Reinstatement', 'column' => 'ready_for_reinstatement_qty', 'type' => 'status'],
                ['label' => 'Ready for Test', 'column' => 'ready_for_test_qty', 'type' => 'status'],
                ['label' => 'Repair Waiting', 'column' => 'repair_waiting_qty', 'type' => 'status'],
                ['label' => 'Welding Ongoing', 'column' => 'welding_ongoing_qty', 'type' => 'status'],
                ['label' => 'Completed', 'column' => 'completed_qty', 'type' => 'status'],
            ],
        ];
    }

    protected static function metricExpressions(): array
    {
        $wdi = 'COALESCE(total_wdi, 0)';
        $tpStatus = "LOWER(COALESCE(tp_status, ''))";
        $weldingStatus = "LOWER(COALESCE(welding_status, ''))";
        $generalStatus = "LOWER(COALESCE(tp_status, welding_status, ''))";

        return [
            'COUNT(*) as total_tp_qty',
            "SUM($wdi) as total_tp_wdi",
            "SUM(CASE WHEN LOWER(welding_status) LIKE '%prep%' THEN 1 ELSE 0 END) as prep_qty",
            "SUM(CASE WHEN LOWER(welding_status) LIKE '%prep%' THEN $wdi ELSE 0 END) as prep_wdi",
            "SUM(CASE WHEN ($tpStatus LIKE '%approved%' OR $weldingStatus LIKE '%approved%') THEN 1 ELSE 0 END) as approved_qty",
            "SUM(CASE WHEN ($tpStatus LIKE '%approved%' OR $weldingStatus LIKE '%approved%') THEN $wdi ELSE 0 END) as approved_wdi",
            "SUM(CASE WHEN punch_status IS NOT NULL AND punch_status != '' THEN 1 ELSE 0 END) as linecheck_qty",
            "SUM(CASE WHEN punch_status IS NOT NULL AND punch_status != '' THEN $wdi ELSE 0 END) as linecheck_wdi",
            'SUM(COALESCE(punch_a_quantity, 0)) as a_punch_qty',
            'SUM(COALESCE(punch_b_quantity, 0)) as b_punch_qty',
            'SUM(COALESCE(punch_c_quantity, 0)) as c_punch_qty',
            "SUM(CASE WHEN $tpStatus LIKE '%released%' THEN 1 ELSE 0 END) as tp_released_qty",
            "SUM(CASE WHEN $tpStatus LIKE '%released%' THEN $wdi ELSE 0 END) as tp_released_wdi",
            "SUM(CASE WHEN $generalStatus = 'b waiting' THEN 1 ELSE 0 END) as b_waiting_qty",
            "SUM(CASE WHEN $generalStatus = 'ndt' THEN 1 ELSE 0 END) as ndt_status_qty",
            "SUM(CASE WHEN $generalStatus = 'punch' THEN 1 ELSE 0 END) as punch_status_qty",
            "SUM(CASE WHEN $generalStatus = 'ready for reinstatement' THEN 1 ELSE 0 END) as ready_for_reinstatement_qty",
            "SUM(CASE WHEN $generalStatus = 'ready for test' THEN 1 ELSE 0 END) as ready_for_test_qty",
            "SUM(CASE WHEN $generalStatus = 'repair waiting' THEN 1 ELSE 0 END) as repair_waiting_qty",
            "SUM(CASE WHEN $generalStatus = 'welding ongoing' THEN 1 ELSE 0 END) as welding_ongoing_qty",
            "SUM(CASE WHEN $generalStatus = 'completed' THEN 1 ELSE 0 END) as completed_qty",
        ];
    }

    protected static function numericColumns(): array
    {
        return [
            'total_tp_qty',
            'total_tp_wdi',
            'prep_qty',
            'prep_wdi',
            'approved_qty',
            'approved_wdi',
            'linecheck_qty',
            'a_punch_qty',
            'b_punch_qty',
            'c_punch_qty',
            'linecheck_wdi',
            'tp_released_qty',
            'tp_released_wdi',
            'b_waiting_qty',
            'ndt_status_qty',
            'punch_status_qty',
            'ready_for_reinstatement_qty',
            'ready_for_test_qty',
            'repair_waiting_qty',
            'welding_ongoing_qty',
            'completed_qty',
        ];
    }

    protected static function wdiColumns(): array
    {
        return [
            'total_tp_wdi',
            'prep_wdi',
            'approved_wdi',
            'linecheck_wdi',
            'tp_released_wdi',
        ];
    }

    protected static function projectMapSubquery()
    {
        return DB::table('weld_logs')
            ->select('test_package_no', DB::raw('MAX(project) as project'))
            ->whereNotNull('test_package_no')
            ->where('test_package_no', '!=', '')
            ->groupBy('test_package_no');
    }

    protected static function testedCondition(string $alias = ''): string
    {
        $prefix = $alias ? $alias . '.' : '';

        $tpStatus = "LOWER({$prefix}test_status)";
        $generalStatus = "LOWER(COALESCE({$prefix}tp_status, {$prefix}welding_status, ''))";

        return "$tpStatus = 'accepted' OR $generalStatus IN ('ready for test','ready for reinstatement','completed')";
    }

    protected static function baseStatusTestedCondition(string $alias = ''): string
    {
        $prefix = $alias ? $alias . '.' : '';
        $statusExpr = "LOWER(COALESCE({$prefix}tp_status, {$prefix}welding_status, ''))";
        return "$statusExpr IN ('ready for test','ready for reinstatement','completed','accepted')";
    }

    protected static function calculateMonthlyTotals(array $rows): array
    {
        $tested = 0;
        $total = 0;

        foreach ($rows as $row) {
            $tested += (int) ($row['tested_packages'] ?? 0);
            $total += (int) ($row['total_packages'] ?? 0);
        }

        $remaining = max($total - $tested, 0);

        return [
            'total' => $total,
            'tested' => $tested,
            'remaining' => $remaining,
        ];
    }
}