app/Domain/Essentials/EssentialsService.php

<?php

namespace App\Domain\Essentials;

use App\Infrastructure\Ids\UuidGenerator;
use Illuminate\Support\Facades\DB;

class EssentialsService
{
    private const RESOURCE_TABLES = [
        'categories' => 'essentials_categories',
        'vendors' => 'essentials_vendors',
        'listings' => 'essentials_listings',
        'requests' => 'essentials_requests',
        'reviews' => 'essentials_reviews',
        'complaints' => 'essentials_complaints',
        'promotions' => 'essentials_promotions',
    ];

    public function __construct(private UuidGenerator $ids)
    {
    }

    public function dashboard(): array
    {
        $counts = [];
        foreach (self::RESOURCE_TABLES as $resource => $table) {
            $query = DB::table($table);
            if ($this->hasSoftDeletes($resource)) {
                $query->whereNull('deleted_at');
            }
            $counts[$resource] = $query->count();
        }

        return [
            'metrics' => [
                'activeCategories' => DB::table('essentials_categories')->where('is_active', true)->whereNull('deleted_at')->count(),
                'approvedVendors' => DB::table('essentials_vendors')->where('status', 'approved')->whereNull('deleted_at')->count(),
                'publishedListings' => DB::table('essentials_listings')->where('status', 'published')->whereNull('deleted_at')->count(),
                'openRequests' => DB::table('essentials_requests')->whereNotIn('status', ['closed', 'cancelled'])->whereNull('deleted_at')->count(),
                'openComplaints' => DB::table('essentials_complaints')->whereIn('status', ['open', 'investigating'])->count(),
                'activePromotions' => DB::table('essentials_promotions')->where('is_active', true)->count(),
            ],
            'counts' => $counts,
            'recentRequests' => $this->list('requests', ['per_page' => 5])['items'],
            'featuredListings' => $this->list('listings', ['per_page' => 5, 'status' => 'published'])['items'],
        ];
    }

    public function list(string $resource, array $filters = []): array
    {
        $table = $this->tableFor($resource);
        $page = max(1, (int) ($filters['page'] ?? 1));
        $perPage = min(100, max(1, (int) ($filters['per_page'] ?? 25)));
        $search = trim((string) ($filters['search'] ?? ''));
        $status = trim((string) ($filters['status'] ?? ''));

        $query = DB::table($table);
        if ($this->hasSoftDeletes($resource)) {
            $query->whereNull('deleted_at');
        }

        if ($status !== '' && $status !== 'all') {
            if ($resource === 'categories') {
                $query->where('is_active', in_array($status, ['active', '1', 'true'], true));
            } elseif ($resource === 'promotions') {
                $query->where('is_active', in_array($status, ['active', '1', 'true'], true));
            } else {
                $query->where('status', $status);
            }
        }

        if ($search !== '') {
            $this->applySearch($query, $resource, $search);
        }

        $total = (clone $query)->count();
        $rows = $query
            ->orderByDesc($this->orderColumn($resource))
            ->offset(($page - 1) * $perPage)
            ->limit($perPage)
            ->get();

        return [
            'items' => $rows->map(fn (object $row) => $this->recordPayload($resource, $row))->all(),
            'total' => $total,
            'page' => $page,
            'per_page' => $perPage,
        ];
    }

    public function get(string $resource, string $id): ?array
    {
        $table = $this->tableFor($resource);
        $query = DB::table($table)->where('id', $id);
        if ($this->hasSoftDeletes($resource)) {
            $query->whereNull('deleted_at');
        }
        $row = $query->first();

        return $row === null ? null : $this->recordPayload($resource, $row);
    }

    public function save(string $resource, array $input): array
    {
        $table = $this->tableFor($resource);
        $id = (string) ($input['id'] ?? $input[$this->idKey($resource)] ?? $this->ids->generate());
        $now = now();
        $payload = $this->writePayload($resource, $input, $id, $now);

        $exists = DB::table($table)->where('id', $id)->exists();
        if ($exists) {
            $updates = $payload;
            if ($resource !== 'reviews') {
                $updates['updated_at'] = $now;
            }
            DB::table($table)->where('id', $id)->update($updates);
        } else {
            $insert = array_merge($payload, ['created_at' => $now]);
            if ($resource !== 'reviews') {
                $insert['updated_at'] = $now;
            }
            DB::table($table)->insert($insert);
        }

        return $this->get($resource, $id) ?? [];
    }

    public function transition(string $resource, string $id, string $status, ?string $note = null, ?string $actorId = null): array
    {
        $table = $this->tableFor($resource);
        $existing = DB::table($table)->where('id', $id)->first();
        if ($existing === null) {
            return [];
        }

        if ($resource === 'categories') {
            DB::table($table)->where('id', $id)->update(['is_active' => $status === 'active', 'updated_at' => now()]);
        } elseif ($resource === 'promotions') {
            DB::table($table)->where('id', $id)->update(['is_active' => $status === 'active', 'updated_at' => now()]);
        } else {
            $updates = ['status' => $status];
            if ($resource !== 'reviews') {
                $updates['updated_at'] = now();
            }
            DB::table($table)->where('id', $id)->update($updates);
        }

        if ($resource === 'requests') {
            DB::table('essentials_request_status_history')->insert([
                'id' => $this->ids->generate(),
                'request_id' => $id,
                'from_status' => $existing->status ?? null,
                'to_status' => $status,
                'changed_by' => $actorId,
                'note' => $note,
                'created_at' => now(),
            ]);
        }

        return $this->get($resource, $id) ?? [];
    }

    private function writePayload(string $resource, array $input, string $id, mixed $now): array
    {
        return match ($resource) {
            'categories' => [
                'id' => $id,
                'parent_id' => $input['parent_id'] ?? $input['parentId'] ?? null,
                'name' => (string) ($input['name'] ?? $input['title'] ?? 'Essentials category'),
                'slug' => (string) ($input['slug'] ?? $this->slug($input['name'] ?? $input['title'] ?? $id)),
                'description' => $input['description'] ?? null,
                'icon' => $input['icon'] ?? null,
                'sort_order' => (int) ($input['sort_order'] ?? $input['sortOrder'] ?? 0),
                'is_active' => (bool) ($input['is_active'] ?? $input['isActive'] ?? true),
                'row_version' => (int) ($input['row_version'] ?? 1),
            ],
            'vendors' => [
                'id' => $id,
                'vendor_number' => $input['vendor_number'] ?? $input['vendorNumber'] ?? null,
                'vendor_type' => $input['vendor_type'] ?? $input['vendorType'] ?? 'general_marketplace_seller',
                'category_id' => $input['category_id'] ?? $input['categoryId'] ?? null,
                'business_name' => (string) ($input['business_name'] ?? $input['businessName'] ?? $input['title'] ?? 'Essentials vendor'),
                'country' => $input['country'] ?? null,
                'city' => $input['city'] ?? null,
                'business_email' => $input['business_email'] ?? $input['businessEmail'] ?? null,
                'business_phone' => $input['business_phone'] ?? $input['businessPhone'] ?? null,
                'business_description' => $input['business_description'] ?? $input['description'] ?? null,
                'services_offered' => $input['services_offered'] ?? $input['servicesOffered'] ?? null,
                'status' => $input['status'] ?? 'draft',
                'risk_level' => $input['risk_level'] ?? $input['riskLevel'] ?? null,
                'row_version' => (int) ($input['row_version'] ?? 1),
            ],
            'listings' => [
                'id' => $id,
                'category_id' => (string) ($input['category_id'] ?? $input['categoryId'] ?? $this->defaultCategoryId()),
                'vendor_id' => $input['vendor_id'] ?? $input['vendorId'] ?? null,
                'title' => (string) ($input['title'] ?? 'Essentials listing'),
                'slug' => (string) ($input['slug'] ?? $this->slug($input['title'] ?? $id)),
                'description' => $input['description'] ?? null,
                'listing_type' => $input['listing_type'] ?? $input['listingType'] ?? 'fixed_price',
                'price_amount' => $input['price_amount'] ?? $input['priceAmount'] ?? null,
                'price_max' => $input['price_max'] ?? $input['priceMax'] ?? null,
                'currency' => $input['currency'] ?? 'KES',
                'member_offer_text' => $input['member_offer_text'] ?? $input['memberOfferText'] ?? null,
                'delivery_timeline' => $input['delivery_timeline'] ?? $input['deliveryTimeline'] ?? null,
                'location_served' => $input['location_served'] ?? $input['locationServed'] ?? null,
                'status' => $input['status'] ?? 'draft',
                'published_at' => ($input['status'] ?? null) === 'published' ? $now : null,
                'row_version' => (int) ($input['row_version'] ?? 1),
            ],
            'requests' => [
                'id' => $id,
                'reference_no' => $input['reference_no'] ?? $input['referenceNo'] ?? 'ESS-'.strtoupper(substr($id, -6)),
                'member_id' => (string) ($input['member_id'] ?? $input['memberId'] ?? '00000000-0000-7000-8000-000000000101'),
                'listing_id' => $input['listing_id'] ?? $input['listingId'] ?? null,
                'category_id' => (string) ($input['category_id'] ?? $input['categoryId'] ?? $this->defaultCategoryId()),
                'request_type' => $input['request_type'] ?? $input['requestType'] ?? 'custom_request',
                'title' => (string) ($input['title'] ?? 'Essentials request'),
                'status' => $input['status'] ?? 'draft',
                'budget_amount' => $input['budget_amount'] ?? $input['budgetAmount'] ?? null,
                'currency' => $input['currency'] ?? 'KES',
                'timeline' => $input['timeline'] ?? null,
                'location' => $input['location'] ?? null,
                'contact_details' => $this->jsonOrNull($input['contact_details'] ?? $input['contactDetails'] ?? null),
                'beneficiary_details' => $this->jsonOrNull($input['beneficiary_details'] ?? $input['beneficiaryDetails'] ?? null),
                'form_payload' => $this->jsonOrNull($input['form_payload'] ?? $input['formPayload'] ?? null),
                'priority' => $input['priority'] ?? 'normal',
                'assigned_admin_id' => $input['assigned_admin_id'] ?? $input['assignedAdminId'] ?? null,
                'submitted_at' => in_array(($input['status'] ?? ''), ['submitted', 'in_progress'], true) ? $now : null,
                'row_version' => (int) ($input['row_version'] ?? 1),
            ],
            'complaints' => [
                'id' => $id,
                'request_id' => $input['request_id'] ?? $input['requestId'] ?? null,
                'review_id' => $input['review_id'] ?? $input['reviewId'] ?? null,
                'member_id' => (string) ($input['member_id'] ?? $input['memberId'] ?? '00000000-0000-7000-8000-000000000101'),
                'vendor_id' => $input['vendor_id'] ?? $input['vendorId'] ?? null,
                'subject' => (string) ($input['subject'] ?? $input['title'] ?? 'Essentials complaint'),
                'description' => (string) ($input['description'] ?? ''),
                'severity' => $input['severity'] ?? 'medium',
                'status' => $input['status'] ?? 'open',
                'resolution_note' => $input['resolution_note'] ?? $input['resolutionNote'] ?? null,
                'row_version' => (int) ($input['row_version'] ?? 1),
            ],
            'reviews' => [
                'id' => $id,
                'request_id' => (string) ($input['request_id'] ?? $input['requestId'] ?? '00000000-0000-7000-8000-000000003004'),
                'listing_id' => $input['listing_id'] ?? $input['listingId'] ?? null,
                'vendor_id' => $input['vendor_id'] ?? $input['vendorId'] ?? null,
                'member_id' => (string) ($input['member_id'] ?? $input['memberId'] ?? '00000000-0000-7000-8000-000000000101'),
                'service_quality' => (int) ($input['service_quality'] ?? $input['serviceQuality'] ?? 5),
                'responsiveness' => (int) ($input['responsiveness'] ?? 5),
                'reliability' => (int) ($input['reliability'] ?? 5),
                'value_for_money' => (int) ($input['value_for_money'] ?? $input['valueForMoney'] ?? 5),
                'overall_satisfaction' => (int) ($input['overall_satisfaction'] ?? $input['overallSatisfaction'] ?? 5),
                'would_recommend' => (bool) ($input['would_recommend'] ?? $input['wouldRecommend'] ?? true),
                'written_feedback' => $input['written_feedback'] ?? $input['writtenFeedback'] ?? null,
                'complaint_flag' => (bool) ($input['complaint_flag'] ?? $input['complaintFlag'] ?? false),
                'visibility' => $input['visibility'] ?? 'internal',
                'status' => $input['status'] ?? 'pending',
            ],
            'promotions' => [
                'id' => $id,
                'listing_id' => $input['listing_id'] ?? $input['listingId'] ?? null,
                'vendor_id' => $input['vendor_id'] ?? $input['vendorId'] ?? null,
                'category_id' => $input['category_id'] ?? $input['categoryId'] ?? null,
                'title' => (string) ($input['title'] ?? 'Essentials promotion'),
                'offer_type' => $input['offer_type'] ?? $input['offerType'] ?? 'discount',
                'value' => $input['value'] ?? null,
                'description' => $input['description'] ?? null,
                'starts_at' => $input['starts_at'] ?? $input['startsAt'] ?? null,
                'ends_at' => $input['ends_at'] ?? $input['endsAt'] ?? null,
                'is_active' => (bool) ($input['is_active'] ?? $input['isActive'] ?? true),
                'row_version' => (int) ($input['row_version'] ?? 1),
            ],
            default => ['id' => $id],
        };
    }

    private function recordPayload(string $resource, object $row): array
    {
        $raw = (array) $row;
        $title = match ($resource) {
            'categories' => $row->name,
            'vendors' => $row->business_name,
            'listings' => $row->title,
            'requests' => $row->title,
            'reviews' => 'Review '.$row->id,
            'complaints' => $row->subject,
            'promotions' => $row->title,
            default => $row->id,
        };
        $status = match ($resource) {
            'categories' => ((int) $row->is_active) === 1 ? 'active' : 'inactive',
            'promotions' => ((int) $row->is_active) === 1 ? 'active' : 'inactive',
            default => (string) ($row->status ?? 'active'),
        };
        $summary = match ($resource) {
            'categories' => (string) ($row->description ?? ''),
            'vendors' => (string) ($row->services_offered ?? $row->business_description ?? ''),
            'listings' => trim((string) ($row->description ?? '').' '.($row->price_amount ? $row->currency.' '.$row->price_amount : '')),
            'requests' => trim(($row->reference_no ?? '').' '.($row->budget_amount ? $row->currency.' '.$row->budget_amount : '')),
            'reviews' => (string) ($row->written_feedback ?? 'Overall satisfaction '.$row->overall_satisfaction),
            'complaints' => (string) ($row->description ?? ''),
            'promotions' => (string) ($row->description ?? $row->offer_type),
            default => '',
        };

        return [
            'id' => $row->id,
            'title' => $title,
            'status' => $status,
            'summary' => $summary,
            'updatedAt' => (string) ($row->updated_at ?? $row->created_at ?? ''),
            'resource' => $resource,
            'raw' => $raw,
        ] + $raw;
    }

    private function applySearch(mixed $query, string $resource, string $search): void
    {
        $columns = match ($resource) {
            'categories' => ['name', 'slug', 'description'],
            'vendors' => ['business_name', 'vendor_number', 'business_email', 'services_offered'],
            'listings' => ['title', 'slug', 'description', 'location_served'],
            'requests' => ['reference_no', 'title', 'location', 'timeline'],
            'reviews' => ['written_feedback'],
            'complaints' => ['subject', 'description', 'severity'],
            'promotions' => ['title', 'description', 'offer_type'],
            default => ['id'],
        };

        $query->where(function ($inner) use ($columns, $search) {
            foreach ($columns as $column) {
                $inner->orWhere($column, 'like', '%'.$search.'%');
            }
        });
    }

    private function tableFor(string $resource): string
    {
        if (! array_key_exists($resource, self::RESOURCE_TABLES)) {
            abort(404, 'Unknown Essentials resource.');
        }

        return self::RESOURCE_TABLES[$resource];
    }

    private function idKey(string $resource): string
    {
        return rtrim($resource, 's').'_id';
    }

    private function hasSoftDeletes(string $resource): bool
    {
        return in_array($resource, ['categories', 'vendors', 'listings', 'requests'], true);
    }

    private function orderColumn(string $resource): string
    {
        return $resource === 'reviews' ? 'created_at' : 'updated_at';
    }

    private function defaultCategoryId(): string
    {
        $category = DB::table('essentials_categories')->whereNull('deleted_at')->orderBy('sort_order')->first();

        return (string) ($category->id ?? '00000000-0000-7000-8000-000000003001');
    }

    private function slug(mixed $value): string
    {
        $slug = strtolower(trim(preg_replace('/[^a-z0-9]+/i', '-', (string) $value), '-'));

        return $slug !== '' ? $slug : strtolower(substr($this->ids->generate(), -12));
    }

    private function jsonOrNull(mixed $value): ?string
    {
        if ($value === null || $value === '') {
            return null;
        }

        return is_string($value) ? $value : json_encode($value);
    }
}