<?php

namespace RxConnexionBundle\Repository;

/**
 * BranchRepository
 *
 * This class was generated by the Doctrine ORM. Add your own custom
 * repository methods below.
 */
class ChatRepository extends \Doctrine\ORM\EntityRepository
{


    public function validate($account_form, $userData) {

        $errors = array();

        $action = $account_form['action'];

        // d = deleteA
        if($action !== 'd') {

            /*$chatExists = $this->getEntityManager()->getRepository('RxConnexionBundle:ChatEntity')
                ->createQueryBuilder('c')
                ->where('c.id != :id')
                ->andWhere('c.isDeleted != :isDeleted')
                ->andWhere('c.description = :description')
                ->setParameters(array(
                    'id' => $account_form['id'],
                    'isDeleted' => 1,
                    'description' => $account_form['description']
                ))
                ->getQuery()->getResult();

            if($chatExists)
                $errors[] = 'You already have a conversation with the participant(s)';*/
            $sql = "SELECT * 
                    FROM `chat` c
                    WHERE c.`id` != :id
                    AND c.`is_deleted` != :isDeleted
                    AND ((c.`description` = :description) OR (c.`description` = :fullName AND c.`chat_type` = :chatType))";

            $query = $this->getEntityManager()->getConnection()->prepare($sql);

            $query->execute(array(
                'id' => $account_form['id'],
                'isDeleted' => 1,
                'description' => $account_form['description'],
                'fullName' => $userData['fullName'],
                'chatType' => 'Private',
            ));

            $result = $query->fetchAll();
//            if(count($result) > 0)
//                $errors[] = 'You already have a conversation with the participant(s)';
        }

        return $errors;
    }

    public function chatAutocompleteAccountUser($q, $id) {

        $sql = "
            SELECT
                p.`id`,
                p.`full_name` AS data,
                p.`full_name` AS value
            FROM `account_user` p
            LEFT JOIN `account_user_type` aut ON p.`account_user_type_id` = aut.`id`
            WHERE p.`full_name` LIKE :description AND p.`is_active` = 1
            AND p.`is_deleted` = 0 AND p.`id`!= :userid
            ORDER BY p.`full_name`
            LIMIT 0,20
        ";

        $query = $this->getEntityManager()->getConnection()->prepare($sql);

        $query->execute(array(
            'description' => "%$q%",
            'userid' => $id
        ));

        $result = $query->fetchAll();

        return $result;
    }

    public function getChatConversationList($id){

        $sql = "SELECT c.`id`, c.`description`, c.`account_user_id`, c.`chat_type`, au.`full_name` as creatorFullName  FROM `chat_participant` cp
                LEFT JOIN `chat` c ON cp.`chat_id` = c.`id`
                LEFT JOIN `account_user` au ON c.`account_user_id` = au.`id`
                WHERE cp.`account_user_id` = :userId AND c.`is_deleted` = 0
                ORDER BY c.`id` DESC";

        $query = $this->getEntityManager()->getConnection()->prepare($sql);

        $query->execute(array(
            'userId' => $id
        ));

        $result = $query->fetchAll();

        return $result;
    }

    public function getChatConversationMessages($id){

        $sql = "SELECT * FROM `chat_messages` cm WHERE cm.`chat_id` = :id";

        $query = $this->getEntityManager()->getConnection()->prepare($sql);

        $query->execute(array(
            'id' => $id
        ));

        $result = $query->fetchAll();

        return $result;
    }

    public function countRecentChat($userId){

        $sql = "SELECT c.id as chat_id, cp.*,c.*,au.full_name as creatorFullName, cp.`seen_latest` FROM `chat_participant` cp
                LEFT JOIN `chat` c ON cp.`chat_id` = c.`id`
                LEFT JOIN `account_user` au ON c .`account_user_id` = au.`id`
                WHERE c.`is_deleted` != 1 AND 
                cp.`account_user_id` = :userId
                ORDER BY cp.`seen_latest`, c.`id` DESC";

        $query = $this->getEntityManager()->getConnection()->prepare($sql);

        $query->execute(array(
            'userId' => $userId
        ));

        $result = $query->fetchAll();

        return $result;
    }

    public function getReceiverIds($chatId){

        $sql = "SELECT GROUP_CONCAT(cp.`account_user_id`) as ids FROM `chat_participant` cp WHERE cp.`chat_id` = :chatId";

        $query = $this->getEntityManager()->getConnection()->prepare($sql);

        $query->execute(array(
            'chatId' => $chatId
        ));

        $result = $query->fetch(0)['ids'];

        return $result;
    }
}
