<?php

namespace App\Repositories\V1\Home;

use App\Enums\Project\ProjectStatusEnum;
use App\Enums\Project\ProjectTypeEnum;
use App\Enums\UserTypeEnum;
use Illuminate\Support\Facades\DB;

class MysqlHomeRepository implements HomeRepositoryInterface
{
    public function statistics(): array
    {
        $usersStats = DB::table('users')
            ->selectRaw('
                COUNT(*) as total,
                COUNT(CASE WHEN type = ? THEN 1 END) as professionals,
                COUNT(CASE WHEN type = ? THEN 1 END) as customers
            ', [UserTypeEnum::PROFESSIONAL->value, UserTypeEnum::CUSTOMER->value])
            ->first();

        $projectsStats = DB::table('projects')
            ->where('type', '=', ProjectTypeEnum::PROJECT->value)
            ->selectRaw('
                COUNT(*) as total,
                COUNT(CASE WHEN status = ? THEN 1 END) as underReview,
                COUNT(CASE WHEN status = ? THEN 1 END) as published,
                COUNT(CASE WHEN status = ? THEN 1 END) as rejected,
                COUNT(CASE WHEN status = ? THEN 1 END) as readyToPublish
            ', [ProjectStatusEnum::UNDER_REVIEW->value, ProjectStatusEnum::PUBLISHED->value, ProjectStatusEnum::REJECTED->value, ProjectStatusEnum::READY_TO_PUBLISH->value])
            ->first();

        $ideaBooksStats = DB::table('projects')
            ->where('type', '=', ProjectTypeEnum::IDEA_BOOK->value)
            ->selectRaw('
                COUNT(*) as total,
                COUNT(CASE WHEN status = ? THEN 1 END) as underReview,
                COUNT(CASE WHEN status = ? THEN 1 END) as published,
                COUNT(CASE WHEN status = ? THEN 1 END) as rejected,
                COUNT(CASE WHEN status = ? THEN 1 END) as readyToPublish
            ', [ProjectStatusEnum::UNDER_REVIEW->value, ProjectStatusEnum::PUBLISHED->value, ProjectStatusEnum::REJECTED->value, ProjectStatusEnum::READY_TO_PUBLISH->value])
            ->first();

        return compact('usersStats', 'projectsStats', 'ideaBooksStats');
    }
}
