<?php

namespace Signteer\Core\Finder;

use Plainwork\Database\Database;
use Plainwork\Database\Query;
use Signteer\Core\Criteria\SignupCriteria as Criteria;
use Signteer\Core\Model\Signup;
use Signteer\Core\Model\SignupWithVolunteer;
use Signteer\Core\Criteria\VolunteerCriteria;
use Signteer\Core\Finder\VolunteerFinder;
use Signteer\Core\Database\SignupTable as Table;
use Signteer\Core\Database\TaskTable;
use Signteer\Core\Database\SheetTable;

class SignupFinder
{
    public $db = Database::class;
    public $table = Table::class;
    public $volunteerFinder = VolunteerFinder::class;

    public function applyCriteriaToQuery(Query $q, Criteria $c): Query
    {
        foreach ($c->getWhereId() as [$compare, $v]) {
            $q = $q->whereInt(Table::NAME . '.' . Table::COL_ID, $compare, $v);
        }

        foreach ($c->getWhereTaskId() as [$compare, $v]) {
            $q = $q->whereInt(Table::NAME . '.' . Table::COL_TASK_ID, $compare, $v);
        }

        foreach ($c->getWhereVolunteerId() as [$compare, $v]) {
            $q = $q->whereInt(Table::NAME . '.' . Table::COL_VOLUNTEER_ID, $compare, $v);
        }

        $whereSheetId = $c->getWhereSheetId();
        if ($whereSheetId) {
            $q = $q->join(TaskTable::NAME)
                ->on(TaskTable::NAME . '.' . TaskTable::COL_ID, '=', Table::NAME . '.' . Table::COL_TASK_ID)
                ;
            foreach ($whereSheetId as [$compare, $v]) {
                $q = $q->whereInt(TaskTable::NAME . '.' . TaskTable::COL_SHEET_ID, $compare, $v);
            }
        }

        return $q;
    }

    public function exists(Criteria $c): bool
    {
        $q = $this->db->q();
        $q = $this->applyCriteriaToQuery($q, $c);

        $q->select(1)->from(Table::NAME)->limit(1);
        $res = $q->execute();

        return ($res) ? true : false;
    }

    public function count(Criteria $c): int
    {
        $q = $this->db->q();
        $q = $this->applyCriteriaToQuery($q, $c);

        $q->select('COUNT(' . Table::COL_VOLUNTEER_ID . ')')->from(Table::NAME);

        $res = $q->execute();
        $ret = $res ? current(current($res)) : 0;

        return $ret;
    }

    public function find(Criteria $c): array
    {
        $ret = [];

        $q = $this->db->q();
        $q = $this->applyCriteriaToQuery($q, $c);

        $q->select(Table::NAME . '.' . Table::COL_ID);
        $q->select(Table::COL_TASK_ID);
        $q->select(Table::COL_VOLUNTEER_ID);
        $q->from(Table::NAME);

        $rows = $q->execute();

        foreach ($rows as $r) {
            $m = new Signup(
                $r[Table::COL_ID],
                $r[Table::COL_TASK_ID],
                $r[Table::COL_VOLUNTEER_ID],
            );
            $ret[] = $m;
        }

        return $ret;
    }

    public function findWithVolunteer(Criteria $c): array
    {
        $ret = [];

        $q = $this->db->q();
        $q = $this->applyCriteriaToQuery($q, $c);

        $q->select(Table::NAME . '.' . Table::COL_ID);
        $q->select(Table::COL_TASK_ID);
        $q->select(Table::COL_VOLUNTEER_ID);
        $q->from(Table::NAME);

        $rows = $q->execute();
        if (!$rows) {
            return $ret;
        }

        $volunteerIds = array_unique(array_column($rows, Table::COL_VOLUNTEER_ID));
        $c_ = (new VolunteerCriteria())->whereId('=', $volunteerIds);
        $volunteers = $this->volunteerFinder->find($c_);

        foreach ($rows as $r) {
            $volunteerId = $r[Table::COL_VOLUNTEER_ID];
            $volunteer = $volunteers[$volunteerId] ?? null;
            if (null === $volunteer) {
                continue;
            }

            $m = new SignupWithVolunteer(
                $r[Table::COL_ID],
                $r[Table::COL_TASK_ID],
                $volunteer
            );

            $ret[] = $m;
        }

        return $ret;
    }

    public function findVolunteerIds(Criteria $c): array
    {
        $ret = [];

        $q = $this->db->q();
        $q = $this->applyCriteriaToQuery($q, $c);

        $q->select(Table::COL_VOLUNTEER_ID);
        $q->from(Table::NAME);

        $rows = $q->execute();
        if (!$rows) {
            return $ret;
        }

        $ret = array_unique(array_column($rows, Table::COL_VOLUNTEER_ID));

        return $ret;
    }

    public function get(int $id): ?Signup
    {
        $c = (new Criteria())->whereId('=', $id);
        $res = $this->find($c);

        return $res ? current($res) : null;
    }
}
