<?php

namespace Maestro\Core\DataTables\Resources;

use Maestro\Core\DataTables\DataTableResource;
use App\Models\Company;
use App\Models\CompanyLocation;
use App\Services\Access\PermissionResolver;
use App\Services\Access\ScopedAccessService;
use Illuminate\Auth\Access\AuthorizationException;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

/**
 * DataTable resource for companies. Table: companies.
 * List column "city" uses the default company_locations row (`type` = hoofdadres constant; tie-break min(id)).
 */
class CompaniesTable implements DataTableResource
{
    public function key(): string
    {
        return 'companies';
    }

    /**
     * Module access: allow if user has companies.view (resolver global or Spatie). Data is scoped in baseQuery (Phase 6.1.1).
     */
    public function authorize(Request $request): void
    {
        if (! $request->user()) {
            throw new AuthorizationException('You are not authorized to access this DataTable resource.');
        }
        $resolver = app(PermissionResolver::class);
        if (! $resolver->userHasModuleAccess($request->user(), 'companies.view')) {
            throw new AuthorizationException('You do not have permission to view the companies list.');
        }
    }

    /**
     * Base query. Restricted to companies the user can access (scoped by actor/group permissions).
     * Join sectors so sector_name is available for sort/filter and transform.
     * Join subsidy tables so we can filter companies by linked subsidy themes.
     * Eager-load subsidyThemes so we can show a readable list of subsidies per company.
     */
    public function baseQuery(Request $request): Builder
    {
        $scopedAccess = app(ScopedAccessService::class);
        $allowedIds = $scopedAccess->getAllowedCompanyIds($request->user());
        if ($allowedIds === []) {
            return Company::query()->whereRaw('1 = 0');
        }
        $allowedIdsList = implode(',', array_map('intval', $allowedIds));
        $mainLocationType = DB::connection()->getPdo()->quote(CompanyLocation::TYPE_DEFAULT);

        $projectOwnCountsSql = <<<'SQL'
(
    SELECT company_id,
        SUM(CASE WHEN status != 'closed' AND phase = 'lead' THEN 1 ELSE 0 END) AS cnt_lead,
        SUM(CASE WHEN status != 'closed' AND phase = 'aanvraag' THEN 1 ELSE 0 END) AS cnt_aanvraag,
        SUM(CASE WHEN status != 'closed' AND phase = 'project' THEN 1 ELSE 0 END) AS cnt_project,
        SUM(CASE WHEN status != 'closed' AND phase = 'verantwoording' THEN 1 ELSE 0 END) AS cnt_vaststelling
    FROM projects
    GROUP BY company_id
) AS proj_own
SQL;

        $projectPrimaryTotalsInner = <<<'SQL'
SELECT cs.primary_company_id,
    SUM(CASE WHEN p.id IS NOT NULL AND p.status != 'closed' AND p.phase = 'lead' THEN 1 ELSE 0 END) AS cnt_lead,
    SUM(CASE WHEN p.id IS NOT NULL AND p.status != 'closed' AND p.phase = 'aanvraag' THEN 1 ELSE 0 END) AS cnt_aanvraag,
    SUM(CASE WHEN p.id IS NOT NULL AND p.status != 'closed' AND p.phase = 'project' THEN 1 ELSE 0 END) AS cnt_project,
    SUM(CASE WHEN p.id IS NOT NULL AND p.status != 'closed' AND p.phase = 'verantwoording' THEN 1 ELSE 0 END) AS cnt_vaststelling
FROM company_structures cs
INNER JOIN company_structure_members csm ON csm.company_structure_id = cs.id
LEFT JOIN projects p ON p.company_id = csm.company_id
GROUP BY cs.primary_company_id
SQL;

        $projectPrimaryTotalsSql = '('.$projectPrimaryTotalsInner.') AS proj_primary_totals';
        $projectPrimaryGroupTotalsSql = '('.$projectPrimaryTotalsInner.') AS proj_primary_group_totals';

        $query = Company::query()
            ->whereIn('companies.id', $allowedIds)
            ->with('subsidyThemes')
            ->leftJoin(DB::raw($projectOwnCountsSql), 'proj_own.company_id', '=', 'companies.id')
            ->leftJoin(DB::raw($projectPrimaryTotalsSql), 'proj_primary_totals.primary_company_id', '=', 'companies.id')
            ->leftJoin('users as project_manager_user', 'companies.project_manager_id', '=', 'project_manager_user.id')
            ->leftJoin('users as project_and_control_user', 'companies.project_and_control_id', '=', 'project_and_control_user.id')
            ->leftJoin(DB::raw("(select min(id) as id, company_id from company_locations where type = {$mainLocationType} group by company_id) as cl_pick"), 'cl_pick.company_id', '=', 'companies.id')
            ->leftJoin('company_locations as cl_main', 'cl_main.id', '=', 'cl_pick.id')
            ->leftJoin('company_structure_members as csm', 'companies.id', '=', 'csm.company_id')
            ->leftJoin('company_structures as cs', 'csm.company_structure_id', '=', 'cs.id')
            ->leftJoin('companies as group_primary_comp', DB::raw('coalesce(cs.primary_company_id, companies.id)'), '=', 'group_primary_comp.id')
            ->leftJoin(DB::raw($projectPrimaryGroupTotalsSql), 'proj_primary_group_totals.primary_company_id', '=', 'group_primary_comp.id')
            ->leftJoin(DB::raw("(select min(id) as id, company_id from company_locations where type = {$mainLocationType} group by company_id) as gp_cl_pick"), 'gp_cl_pick.company_id', '=', 'group_primary_comp.id')
            ->leftJoin('company_locations as group_primary_cl', 'group_primary_cl.id', '=', 'gp_cl_pick.id')
            ->leftJoin('sectors as primary_sectors', 'group_primary_comp.sector_id', '=', 'primary_sectors.id')
            ->leftJoin('users as primary_pm_user', 'group_primary_comp.project_manager_id', '=', 'primary_pm_user.id')
            ->leftJoin('users as primary_pac_user', 'group_primary_comp.project_and_control_id', '=', 'primary_pac_user.id')
            ->leftJoin('company_structure_members as csm_all', function ($join) use ($allowedIdsList) {
                $join->on('cs.id', '=', 'csm_all.company_structure_id')
                    ->whereRaw("csm_all.company_id in ({$allowedIdsList})");
            })
            ->leftJoin('companies as primary_comp', 'cs.primary_company_id', '=', 'primary_comp.id')
            ->leftJoin('company_subsidy_theme', 'companies.id', '=', 'company_subsidy_theme.company_id')
            ->leftJoin('subsidy_themes', 'company_subsidy_theme.subsidy_theme_id', '=', 'subsidy_themes.id')
            ->leftJoin('sectors', 'companies.sector_id', '=', 'sectors.id')
            ->select(
                'companies.id',
                'companies.name',
                'companies.alias',
                'companies.kvk_number',
                'companies.kvk_legal_form',
                'companies.company_size',
                'companies.kvk_number_of_employees',
                'companies.kvk_registration_date',
                DB::raw('cl_main.city as city'),
                'companies.email',
                'companies.project_manager_id',
                'companies.project_and_control_id',
                'companies.created_at',
                'project_manager_user.name as project_manager_name',
                'project_and_control_user.name as project_and_control_name',
                'sectors.name as sector_name',
                'cs.id as structure_id',
                'csm.parent_company_id as group_parent_company_id',
                DB::raw('coalesce(cs.primary_company_id, companies.id) as group_primary_company_id'),
                DB::raw('coalesce(group_primary_comp.name, companies.name) as group_primary_company_name'),
                DB::raw('case when cs.id is null then 0 else 1 end as is_in_structure'),
                DB::raw('case when cs.id is not null and companies.id = cs.primary_company_id then 1 else 0 end as is_primary'),
                DB::raw('case when coalesce(cs.primary_company_id, companies.id) = companies.id then 1 else 0 end as is_group_primary_row'),
                DB::raw("case when cs.primary_company_id in ({$allowedIdsList}) then primary_comp.name else null end as primary_company_name"),
                DB::raw('count(distinct csm_all.company_id) as structure_size'),
                DB::raw('MAX(CASE WHEN cs.id IS NOT NULL AND companies.id = cs.primary_company_id THEN COALESCE(proj_primary_totals.cnt_lead, 0) ELSE COALESCE(proj_own.cnt_lead, 0) END) AS project_count_lead'),
                DB::raw('MAX(CASE WHEN cs.id IS NOT NULL AND companies.id = cs.primary_company_id THEN COALESCE(proj_primary_totals.cnt_aanvraag, 0) ELSE COALESCE(proj_own.cnt_aanvraag, 0) END) AS project_count_aanvraag'),
                DB::raw('MAX(CASE WHEN cs.id IS NOT NULL AND companies.id = cs.primary_company_id THEN COALESCE(proj_primary_totals.cnt_project, 0) ELSE COALESCE(proj_own.cnt_project, 0) END) AS project_count_project'),
                DB::raw('MAX(CASE WHEN cs.id IS NOT NULL AND companies.id = cs.primary_company_id THEN COALESCE(proj_primary_totals.cnt_vaststelling, 0) ELSE COALESCE(proj_own.cnt_vaststelling, 0) END) AS project_count_vaststelling'),
            )
            ->groupBy(
                'companies.id',
                'companies.name',
                'companies.alias',
                'companies.kvk_number',
                'companies.kvk_legal_form',
                'companies.company_size',
                'companies.kvk_number_of_employees',
                'companies.kvk_registration_date',
                'cl_main.id',
                'cl_main.city',
                'companies.email',
                'companies.project_manager_id',
                'companies.project_and_control_id',
                'companies.created_at',
                'project_manager_user.name',
                'project_and_control_user.name',
                'sectors.name',
                'cs.id',
                'csm.parent_company_id',
                'cs.primary_company_id',
                'group_primary_comp.id',
                'group_primary_comp.name',
                'group_primary_comp.alias',
                'group_primary_comp.kvk_number',
                'group_primary_comp.kvk_legal_form',
                'group_primary_comp.company_size',
                'group_primary_comp.kvk_number_of_employees',
                'group_primary_comp.kvk_registration_date',
                'group_primary_cl.id',
                'group_primary_cl.city',
                'group_primary_comp.email',
                'group_primary_comp.created_at',
                'primary_sectors.name',
                'primary_pm_user.name',
                'primary_pac_user.name',
                'primary_comp.name',
            );

        /** @var Builder $query */
        return $query;
    }

    /**
     * Secondary ORDER BY so rows in the same company structure stay contiguous (hoofdbedrijf first).
     *
     * @return array<int, string>
     */
    private static function groupLevelSortTail(): array
    {
        return [
            'case when coalesce(cs.primary_company_id, companies.id) = companies.id then 1 else 0 end DESC',
            'companies.name ASC',
        ];
    }

    /**
     * Sort by the structure group's hoofdbedrijf field, then group-local order.
     *
     * @return array<int, string>
     */
    private static function groupSort(string $primaryExpr): array
    {
        // Keep whole structures contiguous even when the primary sort value is equal
        // across multiple groups (e.g. duplicate company names).
        return array_merge(
            [
                $primaryExpr,
                'coalesce(cs.primary_company_id, companies.id)',
            ],
            self::groupLevelSortTail()
        );
    }

    /**
     * Sort phase-count columns by the structure total (hoofdklant aggregate), then the usual
     * group tail (primary row first, then member companies by name). Stand-alone companies
     * have no structure row: fall back to per-company counts via proj_own.
     *
     * @param  'cnt_lead'|'cnt_aanvraag'|'cnt_project'|'cnt_vaststelling'  $phaseCnt
     * @return array<int, string>
     */
    private static function groupSortPhaseCount(string $phaseCnt): array
    {
        $expr = 'COALESCE(proj_primary_group_totals.'.$phaseCnt.', proj_own.'.$phaseCnt.', 0)';

        return self::groupSort($expr);
    }

    public function columns(): array
    {
        return [
            [
                'key' => 'id',
                'expr' => 'companies.id',
                'type' => 'number',
                'sortable' => true,
                'searchable' => false,
                'filterable' => false,
                'filterMode' => 'equals',
                'label' => __('datatable.columns.companies.id'),
                'defaultVisible' => false,
                'sortExpressions' => self::groupSort('group_primary_comp.id'),
            ],
            [
                'key' => 'name',
                'expr' => 'companies.name',
                'type' => 'text',
                'sortable' => true,
                'searchable' => true,
                'filterable' => true,
                'filterMode' => 'contains',
                'label' => __('datatable.columns.companies.name'),
                'defaultVisible' => true,
                'sortExpressions' => self::groupSort('group_primary_comp.name'),
            ],
            [
                'key' => 'alias',
                'expr' => 'companies.alias',
                'type' => 'text',
                'sortable' => true,
                'searchable' => true,
                'filterable' => true,
                'filterMode' => 'contains',
                'label' => __('datatable.columns.companies.alias'),
                'defaultVisible' => true,
                'sortExpressions' => self::groupSort('group_primary_comp.alias'),
            ],
            [
                'key' => 'kvk_number',
                'expr' => 'companies.kvk_number',
                'type' => 'text',
                'sortable' => true,
                'searchable' => true,
                'filterable' => true,
                'filterMode' => 'equals',
                'label' => __('datatable.columns.companies.kvk_number'),
                'defaultVisible' => true,
                'sortExpressions' => self::groupSort('group_primary_comp.kvk_number'),
            ],
            [
                'key' => 'kvk_legal_form',
                'expr' => 'companies.kvk_legal_form',
                'type' => 'text',
                'sortable' => true,
                'searchable' => true,
                'filterable' => true,
                'filterMode' => 'contains',
                'label' => __('datatable.columns.companies.kvk_legal_form'),
                'defaultVisible' => false,
                'sortExpressions' => self::groupSort('group_primary_comp.kvk_legal_form'),
            ],
            [
                'key' => 'company_size',
                'expr' => 'companies.company_size',
                'type' => 'text',
                'sortable' => true,
                'searchable' => true,
                'filterable' => true,
                'filterMode' => 'equals',
                'allowedOperators' => ['equals', 'not_equals'],
                'label' => __('datatable.columns.companies.company_size'),
                'defaultVisible' => true,
                'sortExpressions' => self::groupSort('group_primary_comp.company_size'),
            ],
            [
                'key' => 'kvk_number_of_employees',
                'expr' => 'companies.kvk_number_of_employees',
                'type' => 'number',
                'sortable' => true,
                'searchable' => false,
                'filterable' => true,
                'filterMode' => 'equals',
                'label' => __('datatable.columns.companies.kvk_number_of_employees'),
                'defaultVisible' => false,
                'sortExpressions' => self::groupSort('group_primary_comp.kvk_number_of_employees'),
            ],
            [
                'key' => 'kvk_registration_date',
                'expr' => 'companies.kvk_registration_date',
                'type' => 'date',
                'sortable' => true,
                'searchable' => false,
                'filterable' => true,
                'filterMode' => 'date_equals',
                'label' => __('datatable.columns.companies.kvk_registration_date'),
                'defaultVisible' => false,
                'sortExpressions' => self::groupSort('group_primary_comp.kvk_registration_date'),
            ],
            [
                'key' => 'sector_name',
                'expr' => 'sectors.name',
                'type' => 'text',
                'sortable' => true,
                'searchable' => true,
                'filterable' => true,
                'filterMode' => 'equals',
                'label' => __('datatable.columns.companies.sector_name'),
                'defaultVisible' => true,
                'sortExpressions' => self::groupSort('primary_sectors.name'),
            ],
            [
                'key' => 'city',
                'expr' => 'cl_main.city',
                'type' => 'text',
                'sortable' => true,
                'searchable' => true,
                'filterable' => true,
                'filterMode' => 'contains',
                'label' => __('datatable.columns.companies.city'),
                'defaultVisible' => true,
                'sortExpressions' => self::groupSort('group_primary_cl.city'),
            ],
            [
                'key' => 'email',
                'expr' => 'companies.email',
                'type' => 'text',
                'sortable' => true,
                'searchable' => true,
                'filterable' => true,
                'filterMode' => 'contains',
                'label' => __('datatable.columns.companies.email'),
                'defaultVisible' => true,
                'sortExpressions' => self::groupSort('group_primary_comp.email'),
            ],
            [
                'key' => 'project_manager_name',
                'expr' => 'project_manager_user.name',
                'type' => 'text',
                'sortable' => true,
                'searchable' => true,
                'filterable' => true,
                'filterMode' => 'equals',
                'allowedOperators' => ['equals', 'not_equals'],
                'filterHandler' => static function (Builder $query, string $op, mixed $value): void {
                    $rawValues = is_array($value) ? $value : [$value];
                    $ids = array_values(array_filter(array_map(static fn ($v): ?int => is_numeric($v) ? (int) $v : null, $rawValues)));
                    if ($ids === []) {
                        return;
                    }

                    if ($op === 'not_equals') {
                        $query->where(function (Builder $q) use ($ids): void {
                            $q->whereNull('companies.project_manager_id')
                                ->orWhereNotIn('companies.project_manager_id', $ids);
                        });

                        return;
                    }

                    $query->whereIn('companies.project_manager_id', $ids);
                },
                'label' => __('datatable.columns.companies.project_manager_name'),
                'defaultVisible' => true,
                'sortExpressions' => self::groupSort('primary_pm_user.name'),
            ],
            [
                'key' => 'project_and_control_name',
                'expr' => 'project_and_control_user.name',
                'type' => 'text',
                'sortable' => true,
                'searchable' => true,
                'filterable' => true,
                'filterMode' => 'equals',
                'allowedOperators' => ['equals', 'not_equals'],
                'filterHandler' => static function (Builder $query, string $op, mixed $value): void {
                    $rawValues = is_array($value) ? $value : [$value];
                    $ids = array_values(array_filter(array_map(static fn ($v): ?int => is_numeric($v) ? (int) $v : null, $rawValues)));
                    if ($ids === []) {
                        return;
                    }

                    if ($op === 'not_equals') {
                        $query->where(function (Builder $q) use ($ids): void {
                            $q->whereNull('companies.project_and_control_id')
                                ->orWhereNotIn('companies.project_and_control_id', $ids);
                        });

                        return;
                    }

                    $query->whereIn('companies.project_and_control_id', $ids);
                },
                'label' => __('datatable.columns.companies.project_and_control_name'),
                'defaultVisible' => true,
                'sortExpressions' => self::groupSort('primary_pac_user.name'),
            ],
            [
                'key' => 'subsidy_names',
                // Filter on subsidy name; display value is built in transformRow.
                'expr' => 'subsidy_themes.name',
                'type' => 'text',
                'sortable' => false,
                'searchable' => false,
                'filterable' => true,
                'filterMode' => 'equals',
                'allowedOperators' => ['equals', 'not_equals'],
                /**
                 * Many-to-many filter: select one or more theme names.
                 * equals: company has at least one selected theme
                 * not_equals: company has none of the selected themes
                 */
                'filterHandler' => static function (Builder $query, string $op, mixed $value): void {
                    $rawValues = is_array($value) ? $value : [$value];
                    $names = array_values(array_filter(array_map(static function ($v): ?string {
                        if (is_array($v) || is_object($v)) {
                            return null;
                        }
                        $s = trim((string) $v);

                        return $s === '' ? null : mb_strtolower($s);
                    }, $rawValues)));

                    if ($names === []) {
                        return;
                    }

                    $applyExists = static function (\Illuminate\Database\Query\Builder $q) use ($names): void {
                        $q->selectRaw('1')
                            ->from('company_subsidy_theme as cst_f')
                            ->join('subsidy_themes as st_f', 'cst_f.subsidy_theme_id', '=', 'st_f.id')
                            ->whereColumn('cst_f.company_id', 'companies.id')
                            ->whereIn(DB::raw('LOWER(st_f.name)'), $names);
                    };

                    if ($op === 'not_equals') {
                        $query->whereNotExists($applyExists);

                        return;
                    }

                    $query->whereExists($applyExists);
                },
                'label' => __('datatable.columns.companies.subsidy_names'),
                'defaultVisible' => true,
            ],
            [
                'key' => 'structure',
                'expr' => 'cs.id',
                'type' => 'text',
                'sortable' => true,
                'searchable' => false,
                'filterable' => false,
                'label' => __('datatable.columns.companies.structure'),
                'defaultVisible' => true,
                'sortExpressions' => [
                    'group_primary_company_name',
                    'is_group_primary_row DESC',
                    'group_parent_company_id IS NOT NULL',
                    'companies.name ASC',
                ],
            ],
            [
                'key' => 'project_count_lead',
                'expr' => 'MAX(CASE WHEN cs.id IS NOT NULL AND companies.id = cs.primary_company_id THEN COALESCE(proj_primary_totals.cnt_lead, 0) ELSE COALESCE(proj_own.cnt_lead, 0) END)',
                'type' => 'number',
                'sortable' => true,
                'searchable' => false,
                'filterable' => false,
                'filterMode' => 'equals',
                'label' => __('datatable.columns.companies.project_count_lead'),
                'defaultVisible' => true,
                'sortExpressions' => self::groupSortPhaseCount('cnt_lead'),
            ],
            [
                'key' => 'project_count_aanvraag',
                'expr' => 'MAX(CASE WHEN cs.id IS NOT NULL AND companies.id = cs.primary_company_id THEN COALESCE(proj_primary_totals.cnt_aanvraag, 0) ELSE COALESCE(proj_own.cnt_aanvraag, 0) END)',
                'type' => 'number',
                'sortable' => true,
                'searchable' => false,
                'filterable' => false,
                'filterMode' => 'equals',
                'label' => __('datatable.columns.companies.project_count_aanvraag'),
                'defaultVisible' => true,
                'sortExpressions' => self::groupSortPhaseCount('cnt_aanvraag'),
            ],
            [
                'key' => 'project_count_project',
                'expr' => 'MAX(CASE WHEN cs.id IS NOT NULL AND companies.id = cs.primary_company_id THEN COALESCE(proj_primary_totals.cnt_project, 0) ELSE COALESCE(proj_own.cnt_project, 0) END)',
                'type' => 'number',
                'sortable' => true,
                'searchable' => false,
                'filterable' => false,
                'filterMode' => 'equals',
                'label' => __('datatable.columns.companies.project_count_project'),
                'defaultVisible' => true,
                'sortExpressions' => self::groupSortPhaseCount('cnt_project'),
            ],
            [
                'key' => 'project_count_vaststelling',
                'expr' => 'MAX(CASE WHEN cs.id IS NOT NULL AND companies.id = cs.primary_company_id THEN COALESCE(proj_primary_totals.cnt_vaststelling, 0) ELSE COALESCE(proj_own.cnt_vaststelling, 0) END)',
                'type' => 'number',
                'sortable' => true,
                'searchable' => false,
                'filterable' => false,
                'filterMode' => 'equals',
                'label' => __('datatable.columns.companies.project_count_vaststelling'),
                'defaultVisible' => true,
                'sortExpressions' => self::groupSortPhaseCount('cnt_vaststelling'),
            ],
            [
                'key' => 'created_at',
                'expr' => 'companies.created_at',
                'type' => 'date',
                'sortable' => true,
                'searchable' => false,
                'filterable' => true,
                'filterMode' => 'date_equals',
                'label' => __('datatable.columns.companies.created_at'),
                'defaultVisible' => true,
                'sortExpressions' => self::groupSort('group_primary_comp.created_at'),
            ],
        ];
    }

    /**
     * When a company matches the search, also include other companies in the same structure.
     */
    public function expandGlobalSearch(Builder $query, string $term): void
    {
        $searchTerm = '%'.mb_strtolower(trim($term)).'%';

        $query->orWhereIn('companies.id', function ($sub) use ($searchTerm) {
            $sub->select('csm_expand.company_id')
                ->from('company_structure_members as csm_expand')
                ->whereIn('csm_expand.company_structure_id', function ($inner) use ($searchTerm) {
                    $inner->select('csm_match.company_structure_id')
                        ->from('company_structure_members as csm_match')
                        ->join('companies as c_match', 'csm_match.company_id', '=', 'c_match.id')
                        ->where(function ($w) use ($searchTerm) {
                            $w->whereRaw('LOWER(c_match.name) LIKE ?', [$searchTerm])
                                ->orWhereRaw('LOWER(c_match.alias) LIKE ?', [$searchTerm]);
                        });
                });
        });
    }

    /**
     * Expand filtered/search result to complete hoofdbedrijf groups.
     *
     * The $matchedQuery contains row-level filter/search conditions.
     * We translate that to matching group ids and apply it to the base query.
     */
    public function expandMatchedGroups(Builder $query, Builder $matchedQuery): void
    {
        $groupsQuery = clone $matchedQuery;
        $groupsBase = $groupsQuery->getQuery();
        $groupsBase->orders = null;
        $groupsBase->limit = null;
        $groupsBase->offset = null;
        // Replace the full grouped SELECT; selectRaw() would append and produce invalid
        // `..., distinct coalesce(...)`. Clear aggregates/grouping and use SELECT DISTINCT.
        $groupsBase->groups = null;
        $groupsBase->havings = null;

        $groupExpr = 'coalesce(cs.primary_company_id, companies.id)';
        $groupsQuery->distinct();
        $groupsQuery->select(DB::raw($groupExpr.' as group_primary_company_id'));

        $query->whereIn(DB::raw($groupExpr), function ($sub) use ($groupsQuery): void {
            $sub->fromSub($groupsQuery->toBase(), 'matched_groups')
                ->select('matched_groups.group_primary_company_id');
        });
    }

    /**
     * @param  object  $model  Company instance
     * @return array<string, mixed>
     */
    public function transformRow(object $model): array
    {
        $createdAt = $model->created_at ?? null;

        $subsidyNames = '';
        if (method_exists($model, 'relationLoaded') && $model->relationLoaded('subsidyThemes')) {
            $subsidyNames = $model->subsidyThemes
                ->pluck('name')
                ->filter()
                ->unique()
                ->implode(', ');
        }

        return [
            'id' => $model->id,
            'name' => $model->name ?? '',
            'alias' => $model->alias ?? '',
            'kvk_number' => $model->kvk_number ?? '',
            'kvk_legal_form' => $model->kvk_legal_form ?? '',
            'company_size' => $model->company_size ?? '',
            'kvk_number_of_employees' => $model->kvk_number_of_employees ?? null,
            'kvk_registration_date' => $model->kvk_registration_date instanceof \DateTimeInterface
                ? $model->kvk_registration_date->format('Y-m-d')
                : (string) $model->kvk_registration_date,
            'sector_name' => $model->sector_name ?? '',
            'city' => $model->city ?? '',
            'email' => $model->email ?? '',
            'project_manager_name' => $model->project_manager_name ?? '',
            'project_and_control_name' => $model->project_and_control_name ?? '',
            'subsidy_names' => $subsidyNames,
            'created_at' => $createdAt instanceof \DateTimeInterface ? $createdAt->format('Y-m-d H:i:s') : (string) $createdAt,
            'is_in_structure' => (bool) ($model->is_in_structure ?? false),
            'is_primary' => (bool) ($model->is_primary ?? false),
            'primary_company_name' => $model->primary_company_name ?? null,
            'structure_size' => (int) ($model->structure_size ?? 0),
            'group_primary_company_id' => (int) ($model->group_primary_company_id ?? $model->id),
            'group_primary_company_name' => $model->group_primary_company_name ?? ($model->name ?? ''),
            'is_group_primary_row' => (bool) ($model->is_group_primary_row ?? false),
            'group_parent_company_id' => isset($model->group_parent_company_id) ? (int) $model->group_parent_company_id : null,
            'project_count_lead' => (int) ($model->project_count_lead ?? 0),
            'project_count_aanvraag' => (int) ($model->project_count_aanvraag ?? 0),
            'project_count_project' => (int) ($model->project_count_project ?? 0),
            'project_count_vaststelling' => (int) ($model->project_count_vaststelling ?? 0),
        ];
    }
}
