<?php

namespace TdyGames\Table;

use TdyGames\Model;
use TdyCommons\Table\Table;
use TdyPlayers\Model\Player;
use Zend\Db\ResultSet\HydratingResultSet;
use Zend\Db\Sql\Delete;
use Zend\Db\Sql\Insert;
use Zend\Db\Sql\Predicate;
use Zend\Db\Sql\Select;
use Zend\Db\Sql\Sql;
use Zend\Db\Sql\Update;
use Zend\Db\Sql\Where;
use Zend\Paginator\Adapter\DbSelect;
use Zend\Paginator\Paginator;

/**
 * Class Contests
 *
 * @package TdyContests\Table
 *
 * @author  James Lloyd Atwil <james@nadows.com>
 */
class Contests extends Table
{

    /**
     * @var array
     */
    public $relatedTables = [];

    /**
     * @param array $relatedTables
     */
    public function setRelatedTables($relatedTables)
    {
        $this->relatedTables = $relatedTables;
    }

    /**
     * @param string $q
     * @param array  $exclusion
     * @param array  $filters
     * @param array  $sort
     * @param bool   $paginated
     *
     * @return bool|HydratingResultSet|Paginator
     */
    public function fetch($q = '', $exclusion = [], $filters = [], $sort = [], $paginated = false)
    {
        $this->getLogger()->debug($this->indent . 'Contests::fetch() start.');

        $select = new Select();
        $select->from($this->getTableGateway()->getTable());
        $conditions = [];

        $where = new Where([]);
        $where->addPredicate(new Predicate\Expression('is_active = ?', 1));
        $conditions[] = $where;

        if (!empty($q)) {
            $where = new Where([], Predicate\PredicateSet::OP_OR);
            $where->addPredicate(new Predicate\Like($this->columns['slug'], '%' . $q . '%'));
            $where->addPredicate(new Predicate\Like($this->columns['name'], '%' . $q . '%'));
            $conditions[] = $where;
        }

        if (!empty($filters)) {
            $where = new Where([]);
            foreach ($filters as $k => $v) {
                if (is_array($v)) {
                    $where->addPredicate(new Predicate\In($k, $v));
                } else {
                    $where->addPredicate(new Predicate\Expression($k . ' = ?', $v));
                }
            }
            $conditions[] = $where;
        }

        if (!empty($exclusion)) {
            $where = new Where([]);
            $where->addPredicate(new Predicate\NotIn('id', $exclusion));
            $conditions[] = $where;
        }

        $select->where($conditions, Predicate\PredicateSet::OP_AND);

        if (!is_null($sort)) {
            if ($this->isSortEmpty($sort)) {
                $select->order(
                    [
                        $this->columns['id'] => 'DESC',
                    ]
                );
            } else {
                $select->order($sort);
            }
        } else {
            $select->order($sort);
        }

        $this->getLogger()->debug($this->indent . self::LOGGER_INDENT . 'SQL Statement: ' . $select->getSqlString($this->getAdapter()->getPlatform()));

        $resultSet = new HydratingResultSet();
        $resultSet->setObjectPrototype(new Model\Contest());

        if ($paginated) {
            $this->getLogger()->debug($this->indent . self::LOGGER_INDENT . 'Is Paginable: YES');

            $paginatorAdapter = new DbSelect($select, $this->getTableGateway()->getSql(), $resultSet);
            $paginator        = new Paginator($paginatorAdapter);

            $this->getLogger()->debug($this->indent . self::LOGGER_INDENT . 'Returns Paginator instance.');
            $this->getLogger()->debug($this->indent . 'Contests::fetch() end.');

            return $paginator;
        } else {
            $this->getLogger()->debug($this->indent . self::LOGGER_INDENT . 'Is Paginable: NO');

            $sql       = new Sql($this->tableGateway->getAdapter());
            $statement = $sql->prepareStatementForSqlObject($select);

            $results = $statement->execute();
            $resultSet->initialize($results);

            if (!$resultSet) {
                $this->getLogger()->debug($this->indent . self::LOGGER_INDENT . 'Returns FALSE');
                $this->getLogger()->debug($this->indent . 'Contests::fetch() end.');

                return false;
            } else {
                $this->getLogger()->debug($this->indent . self::LOGGER_INDENT . 'Returns HydratingResultSet instance.');
                $this->getLogger()->debug($this->indent . 'Contests::fetch() end.');

                return $resultSet;
            }
        }
    }

    /**
     * Returns the Contest.
     *
     * @param int $id
     *
     * @return Model\Contest|null
     * @throws \Exception
     */
    public function get($id)
    {
        $rowset = $this->tableGateway->select([$this->columns['id'] => $id]);
        $row    = $rowset->current();
        if (!$row) {
            return null;
        }

        return $row;
    }

    /**
     * @param Model\Contest $contest
     *
     * @return Model\Contest
     * @throws \Exception
     */
    public function save(Model\Contest $contest)
    {
        $this->getLogger()->debug($this->indent . 'Contests::save() start.');

        $data = [
            $this->columns['slug']        => $contest->slug,
            $this->columns['name']        => $contest->name,
            $this->columns['description'] => $contest->description,
            $this->columns['sport-id']    => $contest->description,
            $this->columns['league-id']   => $contest->description,
            $this->columns['description'] => $contest->description,
        ];

        $id = (int) $contest->id;
        if ($id == 0) {
            $this->getLogger()->debug($this->indent . self::LOGGER_INDENT . 'Inserting Contest record.');

            $data['is_active']  = 1;
            $data['created_by'] = $contest->createdBy;
            $data['created_on'] = date('Y-m-d H:i:s');

            $insert = new Insert($this->getTableGateway()->getTable());
            $insert->values($data);

            $this->getLogger()->debug($this->indent . self::LOGGER_INDENT . 'SQL Statement: ' . $insert->getSqlString($this->getAdapter()->getPlatform()));

            $sql       = new Sql($this->tableGateway->getAdapter());
            $statement = $sql->prepareStatementForSqlObject($insert);

            $statement->execute();

            $id = $this->getAdapter()->getDriver()->getLastGeneratedValue();

            $this->getLogger()->debug($this->indent . self::LOGGER_INDENT . 'Contest ' . $id . ' has been inserted.');
            $this->getLogger()->debug($this->indent . 'Contests::save() end.');

            return $this->get($id);
        } else {
            if ($this->get($id)) {
                $this->getLogger()->debug($this->indent . self::LOGGER_INDENT . 'Updating Contest record.');

                $data[$this->columns['updated-by']] = $contest->updatedBy;
                $data[$this->columns['updated-on']] = date('Y-m-d H:i:s');

                $update = new Update($this->getTableGateway()->getTable());
                $update->set($data);
                $update->where([$this->columns['id'] => $id]);

                $this->getLogger()->debug($this->indent . self::LOGGER_INDENT . 'SQL Statement: ' . $update->getSqlString($this->getAdapter()->getPlatform()));

                $sql       = new Sql($this->tableGateway->getAdapter());
                $statement = $sql->prepareStatementForSqlObject($update);

                $statement->execute();

                $this->getLogger()->debug($this->indent . self::LOGGER_INDENT . 'Contest ' . $id . ' has been updated.');
                $this->getLogger()->debug($this->indent . 'Contests::save() end.');

                return $this->get($id);
            } else {
                $this->getLogger()->debug($this->indent . self::LOGGER_INDENT . 'Contest ID ' . $id . ' does not exists. Update failed.');
                $this->getLogger()->debug($this->indent . 'Contests::save() end.');

                throw new \Exception('Contest ID does not exist');
            }
        }
    }

    /**
     * @param Model\Contest $contest
     * @param Player        $player
     * @param array         $mixes
     */
    public function join(Model\Contest $contest, Player $player, $mixes)
    {
        $this->getLogger()->debug($this->indent . 'Contests::join() start.');

        $connection = $this->getAdapter()->getDriver()->getConnection();

        $connection->beginTransaction();

        try {

            $this->getLogger()->debug($this->indent . self::LOGGER_INDENT . 'Allocate a  Player to the Slot in the Contest.');

            $insert = new Insert($this->relatedTables['contests_slots']['name']);
            $insert->values(
                [
                    $this->relatedTables['contests_slots']['columns']['contest-id'] => $contest->id,
                    $this->relatedTables['contests_slots']['columns']['player-id']  => $player->id,
                ]
            );

            $this->getLogger()->debug($this->indent . self::LOGGER_INDENT . 'SQL Statement: ' . $insert->getSqlString($this->getAdapter()->getPlatform()));

            $sql       = new Sql($this->tableGateway->getAdapter());
            $statement = $sql->prepareStatementForSqlObject($insert);

            $statement->execute();

            $id = $this->getAdapter()->getDriver()->getLastGeneratedValue();

            $this->getLogger()->debug($this->indent . self::LOGGER_INDENT . 'Insert mixtures made by Player start.');

            foreach ($mixes as $mix) {
                $insert = new Insert($this->relatedTables['contests_slots_mixtures']['name']);
                $insert->values(
                    [
                        $this->relatedTables['contests_slots_mixtures']['columns']['slot-id']        => $id,
                        $this->relatedTables['contests_slots_mixtures']['columns']['team-home']      => $mix['home'][0]['id'],
                        $this->relatedTables['contests_slots_mixtures']['columns']['team-home-side'] => $mix['home'][0]['type'] == 'home' ? 0 : 1,
                        $this->relatedTables['contests_slots_mixtures']['columns']['team-away']      => $mix['away'][0]['id'],
                        $this->relatedTables['contests_slots_mixtures']['columns']['team-away-side'] => $mix['away'][0]['type'] == 'home' ? 0 : 1,
                        $this->relatedTables['contests_slots_mixtures']['columns']['bet']            => $mix['bet'],
                    ]
                );

                $this->getLogger()->debug($this->indent . self::LOGGER_INDENT . self::LOGGER_INDENT . 'SQL Statement: ' . $insert->getSqlString($this->getAdapter()->getPlatform()));

                $sql       = new Sql($this->tableGateway->getAdapter());
                $statement = $sql->prepareStatementForSqlObject($insert);

                $statement->execute();
            }

            $this->getLogger()->debug($this->indent . self::LOGGER_INDENT . 'Insert mixtures made by Player end.');

            $this->getLogger()->debug($this->indent . self::LOGGER_INDENT . 'Updating Contest record.');

            $data = [
                $this->columns['joined-players'] => $contest->joinedPlayers + 1,
                $this->columns['updated-by']     => $player->id,
                $this->columns['updated-on']     => date('Y-m-d H:i:s'),
            ];

            $update = new Update($this->getTableGateway()->getTable());
            $update->set($data);
            $update->where([$this->columns['id'] => $contest->id]);

            $this->getLogger()->debug($this->indent . self::LOGGER_INDENT . 'SQL Statement: ' . $update->getSqlString($this->getAdapter()->getPlatform()));

            $sql       = new Sql($this->tableGateway->getAdapter());
            $statement = $sql->prepareStatementForSqlObject($update);

            $statement->execute();

            $this->getLogger()->debug($this->indent . self::LOGGER_INDENT . 'Contest ' . $contest->id . ' has been updated.');


            $this->getLogger()->debug($this->indent . self::LOGGER_INDENT . 'Commiting transaction...');
            $connection->commit();
            $this->getLogger()->debug($this->indent . self::LOGGER_INDENT . 'Transaction committed.');

        } catch (\Exception $e) {
            $this->getLogger()->debug($this->indent . self::LOGGER_INDENT . 'Tossed an Exception grenade: ' . $e->getMessage());
            $this->getLogger()->debug($this->indent . self::LOGGER_INDENT . 'Rolling back...');
            $connection->rollback();
            $this->getLogger()->debug($this->indent . self::LOGGER_INDENT . 'Rolled back.');
        }

        $this->getLogger()->debug($this->indent . 'Contests::save() end.');
    }


    public function isJoined($contestId, $playerId)
    {
        $select = new Select($this->relatedTables['contests_slots']['name']);
        $select->where(
            [
                $this->relatedTables['contests_slots']['columns']['contest-id'] => $contestId,
                $this->relatedTables['contests_slots']['columns']['player-id']  => $playerId,
                $this->relatedTables['contests_slots']['columns']['is-active']  => 1,
            ]
        );

        $resultSet = new HydratingResultSet();
        $resultSet->setObjectPrototype(new Model\Contest());

        $sql       = new Sql($this->tableGateway->getAdapter());
        $statement = $sql->prepareStatementForSqlObject($select);

        $results = $statement->execute();
        $resultSet->initialize($results);

        $contests = iterator_to_array($resultSet);

        return count($contests) > 0;
    }

    /**
     * @param Model\Contest $contest
     *
     * @throws \Exception
     */
    public function delete(Model\Contest $contest)
    {
        $this->getLogger()->debug($this->indent . 'Contests::delete() start.');

        if ($this->get($contest->id)) {
            $data = [
                $this->columns['is-active']  => 0,
                $this->columns['deleted-by'] => $contest->deletedBy,
                $this->columns['deleted-on'] => date('Y-m-d H:i:s'),
            ];

            $update = new Update($this->getTableGateway()->getTable());
            $update->set($data);
            $update->where([$this->columns['id'] => $contest->id]);

            $this->getLogger()->debug($this->indent . self::LOGGER_INDENT . 'SQL Statement: ' . $update->getSqlString($this->getAdapter()->getPlatform()));

            $sql       = new Sql($this->tableGateway->getAdapter());
            $statement = $sql->prepareStatementForSqlObject($update);

            $statement->execute();

            $this->getLogger()->debug($this->indent . self::LOGGER_INDENT . 'Contest ' . $contest->id . ' has been deleted.');
            $this->getLogger()->debug($this->indent . 'Contests::delete() end.');

        } else {
            $this->getLogger()->debug($this->indent . self::LOGGER_INDENT . 'Contest ID ' . $contest->id . ' does not exists. Deletion failed.');
            $this->getLogger()->debug($this->indent . 'Contests::delete() end.');

            throw new \Exception('Contest ID does not exist');
        }
    }

    /**
     * Restore Contest to the table. Sets the Contest to active.
     *
     * @param Model\Contest $contest
     *
     * @throws \Exception
     */
    public function restore(Model\Contest $contest)
    {
        $this->getLogger()->debug($this->indent . 'Contests::restore() start.');

        if ($this->get($contest->id)) {
            $data = [
                $this->columns['is-active']  => 1,
                $this->columns['updated-by'] => $contest->updatedBy,
                $this->columns['updated-on'] => date('Y-m-d H:i:s'),
            ];

            $update = new Update($this->getTableGateway()->getTable());
            $update->set($data);
            $update->where([$this->columns['id'] => $contest->id]);

            $this->getLogger()->debug($this->indent . self::LOGGER_INDENT . 'SQL Statement: ' . $update->getSqlString($this->getAdapter()->getPlatform()));

            $sql       = new Sql($this->tableGateway->getAdapter());
            $statement = $sql->prepareStatementForSqlObject($update);

            $statement->execute();

            $this->getLogger()->debug($this->indent . self::LOGGER_INDENT . 'Contest ' . $contest->id . ' has been restored.');
            $this->getLogger()->debug($this->indent . 'Contests::restore() end.');

        } else {
            $this->getLogger()->debug($this->indent . self::LOGGER_INDENT . 'Contest ID ' . $contest->id . ' does not exists. Restoration failed.');
            $this->getLogger()->debug($this->indent . 'Contests::restore() end.');

            throw new \Exception('Contest ID does not exist');
        }
    }

    /**
     * Permanently delete the Contest.
     *
     * @param Model\Contest $contest
     *
     * @throws \Exception
     */
    public function purge(Model\Contest $contest)
    {
        $this->getLogger()->debug($this->indent . 'Contests::purge() start.');

        if ($this->get($contest->id)) {
            $update = new Delete($this->getTableGateway()->getTable());
            $update->where([$this->columns['id'] => $contest->id]);

            $this->getLogger()->debug($this->indent . self::LOGGER_INDENT . 'SQL Statement: ' . $update->getSqlString($this->getAdapter()->getPlatform()));

            $sql       = new Sql($this->tableGateway->getAdapter());
            $statement = $sql->prepareStatementForSqlObject($update);

            $statement->execute();

            $this->getLogger()->debug($this->indent . self::LOGGER_INDENT . 'Contest ' . $contest->id . ' has been purged, as in permanently deleted.');
            $this->getLogger()->debug($this->indent . 'Contests::purge() end.');

        } else {
            $this->getLogger()->debug($this->indent . self::LOGGER_INDENT . 'Contest ID ' . $contest->id . ' does not exists. Purge failed.');
            $this->getLogger()->debug($this->indent . 'Contests::purge() end.');

            throw new \Exception('Contest ID does not exist');
        }

    }

}
