app/Domain/Applications/ApplicationRepository.php

<?php

namespace App\Domain\Applications;

use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\DB;
use stdClass;

class ApplicationRepository
{
    public function create(array $attributes): void
    {
        $now = Carbon::now();

        DB::table('applications')->insert(array_merge($attributes, [
            'created_at' => $now,
            'updated_at' => $now,
        ]));
    }

    public function find(string $id): ?stdClass
    {
        return DB::table('applications')->where('id', $id)->whereNull('deleted_at')->first();
    }

    public function update(string $id, array $attributes): void
    {
        DB::table('applications')->where('id', $id)->whereNull('deleted_at')->update(array_merge($attributes, [
            'updated_at' => Carbon::now(),
        ]));
    }

    public function createConsent(array $attributes): string
    {
        $now = Carbon::now();
        DB::table('application_consents')->insert(array_merge($attributes, [
            'accepted_at' => $attributes['accepted_at'] ?? $now,
            'created_at' => $now,
            'updated_at' => $now,
        ]));

        return $attributes['id'];
    }

    public function consentsFor(string $applicationId): array
    {
        return DB::table('application_consents')
            ->where('application_id', $applicationId)
            ->whereNull('deleted_at')
            ->pluck('consent_type')
            ->map(fn (string $type): string => strtoupper($type))
            ->all();
    }

    public function createIspValidation(array $attributes): string
    {
        $now = Carbon::now();
        DB::table('isp_validations')->insert(array_merge($attributes, [
            'created_at' => $now,
            'updated_at' => $now,
        ]));

        return $attributes['id'];
    }

    public function findIspValidation(string $id): ?stdClass
    {
        return DB::table('isp_validations')->where('id', $id)->whereNull('deleted_at')->first();
    }

    public function updateIspValidation(string $id, array $attributes): void
    {
        DB::table('isp_validations')->where('id', $id)->whereNull('deleted_at')->update(array_merge($attributes, [
            'updated_at' => Carbon::now(),
        ]));
    }

    public function confirmedIspValidation(string $applicationId): ?stdClass
    {
        return DB::table('isp_validations')
            ->where('application_id', $applicationId)
            ->where('status', 'CONFIRMED')
            ->whereNull('deleted_at')
            ->orderByDesc('validated_at')
            ->first();
    }

    public function createReview(array $attributes): string
    {
        $now = Carbon::now();
        DB::table('application_reviews')->insert(array_merge($attributes, [
            'created_at' => $now,
            'updated_at' => $now,
        ]));

        return $attributes['id'];
    }

    public function latestReview(string $applicationId): ?stdClass
    {
        return DB::table('application_reviews')
            ->where('application_id', $applicationId)
            ->whereNull('deleted_at')
            ->orderByDesc('created_at')
            ->first();
    }

    public function updateReview(string $id, array $attributes): void
    {
        DB::table('application_reviews')->where('id', $id)->whereNull('deleted_at')->update(array_merge($attributes, [
            'updated_at' => Carbon::now(),
        ]));
    }

    public function createVetting(array $attributes): string
    {
        $now = Carbon::now();
        DB::table('vetting_records')->insert(array_merge($attributes, [
            'created_at' => $now,
            'updated_at' => $now,
        ]));

        return $attributes['id'];
    }

    public function latestVetting(string $applicationId): ?stdClass
    {
        return DB::table('vetting_records')
            ->where('application_id', $applicationId)
            ->whereNull('deleted_at')
            ->orderByDesc('created_at')
            ->first();
    }

    public function updateVetting(string $id, array $attributes): void
    {
        DB::table('vetting_records')->where('id', $id)->whereNull('deleted_at')->update(array_merge($attributes, [
            'updated_at' => Carbon::now(),
        ]));
    }

    public function createClarification(array $attributes): string
    {
        $now = Carbon::now();
        DB::table('application_clarifications')->insert(array_merge($attributes, [
            'requested_at' => $attributes['requested_at'] ?? $now,
            'created_at' => $now,
            'updated_at' => $now,
        ]));

        return $attributes['id'];
    }

    public function latestOpenClarification(string $applicationId): ?stdClass
    {
        return DB::table('application_clarifications')
            ->where('application_id', $applicationId)
            ->where('status', 'OPEN')
            ->whereNull('deleted_at')
            ->orderByDesc('created_at')
            ->first();
    }

    public function updateClarification(string $id, array $attributes): void
    {
        DB::table('application_clarifications')->where('id', $id)->whereNull('deleted_at')->update(array_merge($attributes, [
            'updated_at' => Carbon::now(),
        ]));
    }

    public function createDecision(array $attributes): string
    {
        $now = Carbon::now();
        DB::table('application_decisions')->insert(array_merge($attributes, [
            'decided_at' => $attributes['decided_at'] ?? $now,
            'created_at' => $now,
            'updated_at' => $now,
        ]));

        return $attributes['id'];
    }

    public function readyQueue(): array
    {
        return DB::table('applications')
            ->whereNull('deleted_at')
            ->where(function ($query) {
                $query->whereIn('status', ['APPLICATION_COMPLETED', 'CLARIFICATION_SUBMITTED'])
                    ->orWhere(function ($nested) {
                        $nested->where('status', 'NEW')->whereNotNull('submitted_at');
                    });
            })
            ->orderBy('created_at')
            ->limit(50)
            ->get()
            ->all();
    }

    public function searchForReview(array $filters): array
    {
        $page = max(1, (int) ($filters['page'] ?? 1));
        $perPage = min(100, max(1, (int) ($filters['per_page'] ?? 25)));
        $reviewableStatuses = [
            'APPLICATION_COMPLETED',
            'CLARIFICATION_SUBMITTED',
            'IN_REVIEW',
            'AWAITING_APPLICANT_ACTION',
            'VETTING_SCHEDULED',
            'APPROVED',
            'REJECTED',
        ];

        $query = DB::table('applications')
            ->whereNull('applications.deleted_at')
            ->where(function ($nested) use ($reviewableStatuses): void {
                $nested->whereIn('applications.status', $reviewableStatuses)
                    ->orWhere(function ($legacy): void {
                        $legacy->where('applications.status', 'NEW')
                            ->whereNotNull('applications.submitted_at');
                    });
            });

        if (! empty($filters['status'])) {
            $statuses = is_array($filters['status']) ? $filters['status'] : explode(',', (string) $filters['status']);
            $query->whereIn('applications.status', array_map(fn ($status): string => strtoupper(trim((string) $status)), $statuses));
        }

        foreach (['pathway', 'country_of_residence'] as $field) {
            if (! empty($filters[$field])) {
                $query->where("applications.{$field}", $filters[$field]);
            }
        }

        if (! empty($filters['submitted_from'])) {
            $query->whereRaw('COALESCE(applications.ready_for_verification_at, applications.submitted_at) >= ?', [$filters['submitted_from']]);
        }

        if (! empty($filters['submitted_to'])) {
            $query->whereRaw('COALESCE(applications.ready_for_verification_at, applications.submitted_at) <= ?', [$filters['submitted_to']]);
        }

        if (! empty($filters['assigned_to_user_id'])) {
            $query->whereExists(function ($subquery) use ($filters): void {
                $subquery->selectRaw('1')
                    ->from('application_reviews')
                    ->whereColumn('application_reviews.application_id', 'applications.id')
                    ->where('application_reviews.assigned_to_user_id', $filters['assigned_to_user_id'])
                    ->whereNull('application_reviews.deleted_at');
            });
        }

        if (array_key_exists('has_open_tasks', $filters)) {
            $hasOpenTasks = filter_var($filters['has_open_tasks'], FILTER_VALIDATE_BOOL);
            $method = $hasOpenTasks ? 'whereExists' : 'whereNotExists';
            $query->{$method}(function ($subquery) use ($filters): void {
                $subquery->selectRaw('1')
                    ->from('application_review_tasks')
                    ->whereColumn('application_review_tasks.application_id', 'applications.id')
                    ->whereIn('application_review_tasks.status', ['OPEN', 'PENDING', 'FAILED'])
                    ->whereNull('application_review_tasks.deleted_at');

                if (! empty($filters['task_type'])) {
                    $subquery->where('application_review_tasks.task_type', strtoupper((string) $filters['task_type']));
                }
            });
        } elseif (! empty($filters['task_type'])) {
            $query->whereExists(function ($subquery) use ($filters): void {
                $subquery->selectRaw('1')
                    ->from('application_review_tasks')
                    ->whereColumn('application_review_tasks.application_id', 'applications.id')
                    ->where('application_review_tasks.task_type', strtoupper((string) $filters['task_type']))
                    ->whereNull('application_review_tasks.deleted_at');
            });
        }

        if (! empty($filters['search'])) {
            $search = '%'.strtolower(trim((string) $filters['search'])).'%';
            $query->where(function ($nested) use ($search): void {
                $nested->whereRaw('LOWER(applications.application_reference) LIKE ?', [$search])
                    ->orWhereRaw('LOWER(applications.email) LIKE ?', [$search])
                    ->orWhereRaw('LOWER(applications.phone_number) LIKE ?', [$search])
                    ->orWhereRaw('LOWER(applications.country_of_residence) LIKE ?', [$search])
                    ->orWhereRaw("LOWER(CONCAT(COALESCE(applications.first_name, ''), ' ', COALESCE(applications.last_name, ''))) LIKE ?", [$search])
                    ->orWhereRaw('LOWER(COALESCE(applications.selected_product_codes, applications.metadata, ?)) LIKE ?', ['', $search]);
            });
        }

        $total = (clone $query)->count();
        $applications = $query
            ->orderByDesc('applications.ready_for_verification_at')
            ->orderByDesc('applications.submitted_at')
            ->orderByDesc('applications.created_at')
            ->forPage($page, $perPage)
            ->get()
            ->all();

        return [
            'applications' => $applications,
            'pagination' => [
                'page' => $page,
                'per_page' => $perPage,
                'total' => $total,
                'total_pages' => (int) ceil($total / $perPage),
            ],
        ];
    }
}