<?php

namespace App\Event;

use Qs_Db;
use Qs_Db_Table;
use Zend_Db_Expr;

class Model
{
    public static function getStartDateTimeExpr()
    {
        $select = Qs_Db::getSelect();
        $select->from(Qs_Db::getPair('EventTime', 'et'), ['dateTime' => 'CONCAT(e.startDate, " ", et.startTime)']);
        $select->where('et.eventId = e.id');
        $select->order('date ASC');
        $select->limit(1);
        return new Zend_Db_Expr('(' . $select . ')');
    }

    public static function getEndDateTimeExpr()
    {
        $select = Qs_Db::getSelect();
        $select->from(Qs_Db::getPair('EventTime', 'et'), ['dateTime' => 'CONCAT(e.endDate, " ", et.endTime)']);
        $select->where('et.eventId = e.id');
        $select->order('date DESC');
        $select->limit(1);
        return new Zend_Db_Expr('(' . $select . ')');
    }

    public static function getRegistrationAllowedExpr()
    {
        $db = Qs_Db::getInstance();
        $_typeRegistration = $db->quote(Entity::TYPE_REGISTRATION);
        $_attendeeCountExpr = self::getEventRegisteredCountExpr();
        $expr = "e.type = {$_typeRegistration}"
            . " AND e.registrationStart < NOW()"
            . " AND e.registrationEnd > NOW()"
            . " AND (e.registrationLimit IS NULL OR e.registrationLimit > {$_attendeeCountExpr} )";
        return new Zend_Db_Expr($expr);

    }

    public static function getEventRegisteredCountExpr()
    {
        $select = Qs_Db::getSelect();
        $select->from(Qs_Db::getPair('EventAttendee', 'ea'), 'COUNT(*)');
        $select->where('`ea`.`bought` = ?', 'y');
        $select->where('`ea`.`eventId` = `e`.`id`');
        return new Zend_Db_Expr('IF(`e`.`type` = ' . Qs_Db::getInstance()->quote(Entity::TYPE_REGISTRATION)
            . ', (' . $select . '), NULL)');
    }

    /**
     * @param array|int $eventId
     *
     * @return array
     */
    public static function readEventTimeRanges($eventId)
    {
        if (is_array($eventId) && empty($eventId)) {
            return [];
        }

        $select = Qs_Db::getSelect();
        $select->from(Qs_Db::getPair('EventTime', 'et'), ['eventId', 'date', 'startTime', 'endTime']);
        if (is_array($eventId)) {
            $fetchMode = Qs_Db::FETCH_GROUP;
            $select->where('et.eventId IN(?)', $eventId);
        } else {
            $fetchMode = null;
            $select->where('et.eventId = ?', $eventId);
        }
        $select->order('et.date');
        return Qs_Db::getInstance()->fetchAll($select, [], $fetchMode);
    }

    public static function getAttendeeNameExpr()
    {
        return new Zend_Db_Expr('CONCAT_WS(" ", `ea`.`firstName`, `ea`.`lastName`)');
    }

    /**
     * @param string $alias EventAttendee table alias
     * @return \Zend_Db_Expr
     */
    public static function getAttendeeUserAvailableExpr($alias = null)
    {
        $select = Qs_Db::getSelect();
        $select->from(Qs_Db::getPair('User'), ['id']);
        $select->where("User.id = $alias.userId");
        $select->where('User.bought = "y"');
        $select->where('User.status = "active"');
        $select->limit(1);
        return new Zend_Db_Expr("IF({$alias}.`type` = 'nonmember', 1, EXISTS ({$select}))");
    }

    public static function getEventTypes4Select()
    {
        return (new Qs_Db_Table('EventType'))->get4Select();
    }
}