<?php

namespace App\Modules\Admin\Dashboard;

use App\Models\Company;
use Illuminate\Support\Facades\DB;

class RegisteredCompanies
{
    public function allCompanies($payload)
    {
        return Company::whereYear('created_at', $payload->selectedYear)
            ->select(
                DB::raw('
                    COUNT(CASE MONTH(created_at) WHEN  1 THEN companies.id END) AS January,
                    COUNT(CASE MONTH(created_at) WHEN  2 THEN companies.id END) AS February,
                    COUNT(CASE MONTH(created_at) WHEN  3 THEN companies.id END) AS March,
                    COUNT(CASE MONTH(created_at) WHEN  4 THEN companies.id END) AS April,
                    COUNT(CASE MONTH(created_at) WHEN  5 THEN companies.id END) AS May,
                    COUNT(CASE MONTH(created_at) WHEN  6 THEN companies.id END) AS June,
                    COUNT(CASE MONTH(created_at) WHEN  7 THEN companies.id END) AS July,
                    COUNT(CASE MONTH(created_at) WHEN  8 THEN companies.id END) AS August,
                    COUNT(CASE MONTH(created_at) WHEN  9 THEN companies.id END) AS September,
                    COUNT(CASE MONTH(created_at) WHEN 10 THEN companies.id END) AS October,
                    COUNT(CASE MONTH(created_at) WHEN 11 THEN companies.id END) AS November,
                    COUNT(CASE MONTH(created_at) WHEN 12 THEN companies.id END) AS December
                ')
            )
            ->first();
    }

    public function newAffiliates($payload)
    {
        return Company::where('type', 'affiliated')
            ->whereYear('created_at', $payload->selectedYear)
            ->select(
                DB::raw('
                    COUNT(CASE MONTH(created_at) WHEN  1 THEN companies.id END) AS January,
                    COUNT(CASE MONTH(created_at) WHEN  2 THEN companies.id END) AS February,
                    COUNT(CASE MONTH(created_at) WHEN  3 THEN companies.id END) AS March,
                    COUNT(CASE MONTH(created_at) WHEN  4 THEN companies.id END) AS April,
                    COUNT(CASE MONTH(created_at) WHEN  5 THEN companies.id END) AS May,
                    COUNT(CASE MONTH(created_at) WHEN  6 THEN companies.id END) AS June,
                    COUNT(CASE MONTH(created_at) WHEN  7 THEN companies.id END) AS July,
                    COUNT(CASE MONTH(created_at) WHEN  8 THEN companies.id END) AS August,
                    COUNT(CASE MONTH(created_at) WHEN  9 THEN companies.id END) AS September,
                    COUNT(CASE MONTH(created_at) WHEN 10 THEN companies.id END) AS October,
                    COUNT(CASE MONTH(created_at) WHEN 11 THEN companies.id END) AS November,
                    COUNT(CASE MONTH(created_at) WHEN 12 THEN companies.id END) AS December
                ')
            )
            ->first();
    }

    public function newGuests($payload)
    {
        return Company::where('type', 'guest')
            ->whereYear('created_at', $payload->selectedYear)
            ->select(
                DB::raw('
                    COUNT(CASE MONTH(created_at) WHEN  1 THEN companies.id END) AS January,
                    COUNT(CASE MONTH(created_at) WHEN  2 THEN companies.id END) AS February,
                    COUNT(CASE MONTH(created_at) WHEN  3 THEN companies.id END) AS March,
                    COUNT(CASE MONTH(created_at) WHEN  4 THEN companies.id END) AS April,
                    COUNT(CASE MONTH(created_at) WHEN  5 THEN companies.id END) AS May,
                    COUNT(CASE MONTH(created_at) WHEN  6 THEN companies.id END) AS June,
                    COUNT(CASE MONTH(created_at) WHEN  7 THEN companies.id END) AS July,
                    COUNT(CASE MONTH(created_at) WHEN  8 THEN companies.id END) AS August,
                    COUNT(CASE MONTH(created_at) WHEN  9 THEN companies.id END) AS September,
                    COUNT(CASE MONTH(created_at) WHEN 10 THEN companies.id END) AS October,
                    COUNT(CASE MONTH(created_at) WHEN 11 THEN companies.id END) AS November,
                    COUNT(CASE MONTH(created_at) WHEN 12 THEN companies.id END) AS December
                ')
            )
            ->first();
    }
}