<?php

namespace Tests\Feature;

use App;
use App\Models\Application;
use App\Models\Client;
use App\Notifications\ApplicationCreatedNotification;
use App\Notifications\ApplicationStatusPositiveNotification;
use DB;
use Illuminate\Support\Debug\Dumper;
use Tests\TestCase;

class DocQueryTest extends TestCase
{
    /**
     * A basic test example.
     *
     * @return void
     */
    public function testBasicTest()
    {
        DB::enableQueryLog();
        $result = DB::table("applications")
            ->select(
                "applications.id",
                DB::raw("DATE(applications.created_at) AS created"),
                DB::raw("count(application_docs.id) AS doc_cnt"),
                DB::raw("count(notification_messages.id) AS msg_cnt")
            )
            ->leftJoin(
                "application_docs",
                "application_docs.application_id",
                "=",
                "applications.id"
            )
            ->leftJoin("notification_messages", function ($join) {
                $join->on("notification_messages.application_id", "=", "applications.id")
                    ->on("notification_messages.message_id", "=", DB::raw("'App\\Notifications\\ApplicationNeedDocumentsNotification'"));
            })
            ->whereIn("applications.status_id", [1, 2])
            ->where("applications.created_at", ">", DB::raw("NOW() - INTERVAL 1 WEEK"))
            ->groupBy("applications.id")
            ->havingRaw("count(application_docs.id) = 0")
            ->havingRaw("count(notification_messages.id) = 0")
            ->get();

        $log = DB::getQueryLog();
        $lastQuery = array_pop($log);

        $dumper = new Dumper();
        $dumper->dump($lastQuery["query"]);
        $dumper->dump($result);
    }
}