<?php

namespace Signteer\Core\Finder;

use Plainwork\Database\Database;
use Plainwork\Database\Query;
use Plainwork\DateTime\TimeFactory;
use Signteer\Core\Criteria\SheetCriteria as Criteria;
use Signteer\Core\Model\Sheet;
use Signteer\Core\Model\SheetWithTasks;
use Signteer\Core\Model\Task;
use Signteer\Core\Database\SheetTable as Table;
use Signteer\Core\Database\TaskTable;

class SheetFinder
{
    public $db = Database::class;
    public $timeFactory = TimeFactory::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->getWhereTitle() as [$compare, $v]) {
            $q = $q->whereString(Table::NAME . '.' . Table::COL_TITLE, $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_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::COL_ID);
        $q->select(Table::COL_TITLE);
        $q->select(Table::COL_DESCRIPTION);
        $q->from(Table::NAME);
        $q->orderAsc(Table::COL_TITLE);

        $today = $this->timeFactory->startDay($this->timeFactory->now());

        $rows = $q->execute();
        foreach ($rows as $r) {
            $m = new Sheet(
                $r[Table::COL_ID],
                $today,
                $r[Table::COL_TITLE],
                $r[Table::COL_DESCRIPTION] ?? '',
            );
            $ret[$m->getId()] = $m;
        }

        return $ret;
    }

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

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

        $q->select(Table::COL_ID);
        $q->select(Table::COL_TITLE);
        $q->from(Table::NAME);
        $q->orderAsc(Table::COL_TITLE);

        $rows = $q->execute();
        foreach ($rows as $r) {
            $ret[$r[Table::COL_ID]] = $r[Table::COL_TITLE];
        }

        return $ret;
    }

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

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

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

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

        $q->select(Table::NAME . '.' . Table::COL_ID);
        $q->select(Table::NAME . '.' . Table::COL_TITLE);
        $q->select(Table::NAME . '.' . Table::COL_DESCRIPTION);
        $q->from(Table::NAME);
        $q->join(TaskTable::NAME)
            ->on(TaskTable::NAME . '.' . TaskTable::COL_SHEET_ID, '=', Table::NAME . '.' . Table::COL_ID)
            ;
        $q->select(TaskTable::NAME . '.' . TaskTable::COL_ID)->as('task_id');
        $q->select(TaskTable::NAME . '.' . TaskTable::COL_TITLE)->as('task_title');
        $q->select(TaskTable::NAME . '.' . TaskTable::COL_SPOTS)->as('task_spots');

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

        $today = $this->timeFactory->startDay($this->timeFactory->now());
        foreach ($rows as $r) {
            $id = $r[Table::COL_ID];
            if (!isset($ret[$id])) {
                $ret[$id] = new SheetWithTasks(
                    $r[Table::COL_ID],
                    $today,
                    $r[Table::COL_TITLE],
                    $r[Table::COL_DESCRIPTION] ?? '',
                );
            }

            $task = new Task($r['task_id'], $id, $r['task_title'], $r['task_spots']);
            $ret[$id]->addTask($task);
        }

        return $ret;
    }
}
