<?php

namespace LQDN\Handler;

use Doctrine\DBAL\Connection;
use LQDN\Command\UserUpdateByAdminCommand;
use LQDN\Command\UserUpdateTotalCommand;
use LQDN\Command\UserUpdateCumulCommand;
use LQDN\Command\UserCreateCommand;
use LQDN\Command\AdminUpdateTotalUsersCommand;
use LQDN\Exception\InvalidEmailException;

function checkEmail($email)
{
    if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
        throw new InvalidEmailException();
    }
}

class UserHandler
{
    private $connection;

    public function __construct(Connection $connection)
    {
        $this->connection = $connection;
    }

    /**
     * Update of a user from the admin.
     *
     * @param UserUpdateByAdminCommand $command
     */
    public function handleUserUpdateByAdminCommand(UserUpdateByAdminCommand $command)
    {
        checkEmail($command->getEmail());
        $this->connection->executeUpdate('UPDATE users SET pseudo = :username, email = :email, commentaire = :comment, cumul = :cumul, total = :total WHERE id = :id', [
            'username' => $command->getUsername(),
            'email' => $command->getEmail(),
            'comment' => $command->getComment(),
            'id' => $command->getId(),
            'total' => $command->getTotal(),
            'cumul' => $command->getCumul(),
        ]);
    }

    /**
     * Create a user in database
     *
     * @param UserCreateCommand $command
     */
    public function handleUserCreateCommand(UserCreateCommand $command)
    {
        checkEmail($command->getEmail());
        $this->connection->executeUpdate('INSERT INTO users(email, hash, pseudo, total, cumul) VALUES (:email, :hash, :pseudo, :total, :cumul)', [
            'email'=> $command->getEmail(),
            'hash'=> $command->getHash(),
            'pseudo'=> $command->getPseudo(),
            'total'=> $command->getTotal(),
            'cumul'=> $command->getCumul(),
        ]);
    }

    /**
     * Update the user total
     *
     * @param UserUpdateTotalCommand $command
     */
    public function handleUserUpdateTotalCommand(UserUpdateTotalCommand $command)
    {
        $this->connection->executeUpdate(
            'UPDATE users SET total = :total WHERE id = :id',
            [
                'total' => $command->getTotal(),
                'id'=> $command->getId()
            ]
        );
    }

    /**
     * Update the user cumul
     *
     * @param UserUpdateCumulCommand $command
     */
    public function handleUserUpdateCumulCommand(UserUpdateCumulCommand $command)
    {
        $this->connection->executeUpdate(
            'UPDATE users SET cumul = :cumul WHERE id = :id',
            [
                'cumul' => $command->getCumul(),
                'id' => $command->getId(),
            ]
        );
    }

    /**
     * Get through all the users and databases to recompute all total from users.
     *
     * @param AdminUpdateTotalUsersCommand $command
     */
    public function handleAdminUpdateTotalUsersCommand(AdminUpdateTotalUsersCommand $command)
    {
        // Let's get all users first
        $user_ids = $this->connection->executeQuery('SELECT id FROM users')->fetchAll(\PDO::FETCH_COLUMN);

        foreach ($user_ids as $user_id) {
            $user_id = (int) $user_id;
            $total = $this->connection->executeQuery('SELECT SUM(somme) AS total FROM dons WHERE user_id = :user_id AND STATUS IN (1, 102)', [
                'user_id' => $user_id
            ])->fetchAll(\PDO::FETCH_COLUMN)[0];

            // Let's compute the cumul too
            // We only want line where id == parent, others are children of one claim
            // quoi = 'hoodie' somme = 250
            // quoi = 'pishirt' somme = 100
            // quoi = 'pibag' somme = 50
            // quoi = 'piplome' somme = 30
            $spent = $this->connection->executeQuery(
                "SELECT sum(IF(quoi = 'hoodie',314,0)) + sum(IF(quoi = 'pishirt', 128, 0)) + sum(IF(quoi = 'pibag', 64, 0)) + sum(IF(quoi = 'piplome', 32, 0)) AS spent FROM contreparties WHERE user_id = :user_id AND id = parent",
                [ 'user_id' => $user_id]
            )->fetchAll(\PDO::FETCH_COLUMN)[0];
            $this->connection->executeUpdate('UPDATE users SET total = :total, cumul = :cumul WHERE id = :user_id', ['total' => (int) $total, 'cumul' => (int) $total - (int) $spent, 'user_id' => $user_id]);
        }
    }
}