<?php

declare(strict_types=1);

/*
 * This file is part of contao-weinanlieferung-bundle.
 *
 * (c) vonRotenberg
 *
 * @license commercial
 */

namespace vonRotenberg\WeinanlieferungBundle\Model;

use Contao\Controller;
use Contao\Database;
use Contao\Date;
use Contao\Model;
use Contao\Model\Registry;
use Doctrine\DBAL\Connection;

class WeinanlieferungSlotsModel extends Model
{
    /**
     * Table name
     * @var string
     */
    protected static $strTable = 'tl_vr_wa_slot';

    public static function findPublishedById($intId, array $arrOptions=array())
    {
        $time = time();
        $t = static::$strTable;
        $arrColumns = array("$t.id=?");

        // Skip unsaved elements (see #2708)
        $arrColumns[] = "$t.tstamp!=0";
        $arrColumns[] = "$t.published='1'";

        $arrColumns[] = "$t.buchbar_bis>$time";

        if (!isset($arrOptions['order']))
        {
            $arrOptions['order'] = "$t.time ASC";
        }

        return static::findOneBy($arrColumns, $intId, $arrOptions);
    }

    public static function findPublishedByPid($intPid, array $arrOptions=array())
    {
        $time = time();
        $t = static::$strTable;
        $arrColumns = array("$t.pid=?");

        // Skip unsaved elements (see #2708)
        $arrColumns[] = "$t.tstamp!=0";
        $arrColumns[] = "$t.published='1'";

        $arrColumns[] = "$t.buchbar_bis>$time";

        if (!isset($arrOptions['order']))
        {
            $arrOptions['order'] = "$t.time ASC";
        }

        return static::findBy($arrColumns, $intPid, $arrOptions);
    }

    public static function findMultiplePublishedByPids(array $arrPids, array $arrOptions=array())
    {
        if (empty($arrPids) || !\is_array($arrPids))
        {
            return null;
        }

        $arrPids = array_filter($arrPids, function($var) {
            return is_numeric($var);
        });

        if (empty($arrPids))
        {
            return null;
        }

        $time = time();
        $t = static::$strTable;
        $arrColumns = array("$t.pid IN (".implode(',',$arrPids).")");

        // Skip unsaved elements (see #2708)
        $arrColumns[] = "$t.tstamp!=0";
        $arrColumns[] = "$t.published='1'";

        $arrColumns[] = "$t.buchbar_bis>$time";

        if (!isset($arrOptions['order']))
        {
            $arrOptions['order'] = "$t.time ASC";
        }

        if (isset($arrOptions['column']))
        {
            $arrColumns = array_merge($arrColumns,$arrOptions['column']);
            $arrOptions = array_diff_key($arrOptions,['column'=>true]);
        }

        return static::findBy($arrColumns,null,$arrOptions);
    }

    public static function findAllFuturePublished(array $arrOptions=array())
    {
        $t = static::$strTable;
        $time = Date::floorToMinute();

        if (!isset($arrOptions['order']))
        {
            $arrOptions['order'] = "$t.time ASC";
        }

        return static::findBy(array("$t.time >= ?","$t.tstamp!=0","$t.published='1' AND $t.buchbar_bis > ?"), [$time,$time,$time], $arrOptions);
    }

    public function getAvailableBehaelter(?int $intOffset=null)
    {
        /** @var Connection $db */
        $db = Controller::getContainer()->get('database_connection');

        $ReservedBehaelter = $db->prepare("SELECT SUM(behaelter) FROM tl_vr_wa_reservation WHERE pid = ? AND approved != '0'")
            ->executeQuery([$this->id]);

        $intReserved = $ReservedBehaelter->fetchOne();

        if ($intReserved === null)
        {
            $intReserved = 0;
        }

        if ($intOffset > 0)
        {
            $intReserved += $intOffset;
        }

        return (int) $this->behaelter - $intReserved;
    }

    public function getReservedBehaelter(?int $memberId=null, bool $exclude=false)
    {
        /** @var Connection $db */
        $db = Controller::getContainer()->get('database_connection');

        $query = $db->createQueryBuilder()
            ->from("tl_vr_wa_reservation")
            ->addSelect("SUM(behaelter)")
            ->andWhere("pid = :pid")
            ->andWhere("approved != '0'");

        $arrParams = ['pid'=>$this->id];

        if ($memberId !== null)
        {
            if ($exclude)
            {
                $query->andWhere("uid != :uid");
            } else
            {
                $query->andWhere("uid = :uid");
            }
            $arrParams['uid'] = $memberId;
        }

        $ReservedBehaelter = $query->setParameters($arrParams)
            ->executeQuery();

        $intReserved = $ReservedBehaelter->fetchOne();

        return (int) $intReserved;
    }
}