<?php
declare(strict_types=1);

namespace App\Model\Table;

use Cake\ORM\Table;
use Cake\ORM\Query;

class FollowersTable extends Table
{
	public function initialize(array $config): void
	{
		parent::initialize($config);
		$this->setTable('follower');
		$this->setPrimaryKey('id');
		$this->belongsTo('FollowingList', [
			'className' => 'Users',
			'foreignKey' => 'receiver_id',
			'fields' => [
				'id','first_name','last_name','bio','website','profile_pic','profile_pic_small',
				'profile_gif','device_token','business','parent','username'
			]
		]);
		$this->belongsTo('FollowerList', [
			'className' => 'Users',
			'foreignKey' => 'sender_id',
			'fields' => [
				'id','first_name','last_name','bio','website','profile_pic','profile_pic_small',
				'profile_gif','device_token','business','parent','username'
			]
		]);
	}

	public function getAll(): array
	{
		return $this->find()
			->order(['Follower.id' => 'DESC'])
			->enableHydration(false)
			->toArray();
	}

	public function getDetails(int $id): ?array
	{
		return $this->find()
			->where(['Follower.id' => $id])
			->enableHydration(false)
			->first();
	}

	public function ifFollowing(int $sender_id, int $receiver_id): ?array
	{
		return $this->find()
			->where(['sender_id' => $sender_id, 'receiver_id' => $receiver_id])
			->enableHydration(false)
			->first();
	}

	public function isFollowerOrFollowed(int $user_id): array
	{
		return $this->find()
			->where(['OR' => ['sender_id' => $user_id, 'receiver_id' => $user_id]])
			->order(['Follower.id' => 'DESC'])
			->contain(['FollowingList','FollowerList'])
			->enableHydration(false)
			->toArray();
	}

	public function countFollowers(int $user_id): int
	{
		return $this->find()
			->innerJoinWith('FollowerList')
			->where(['receiver_id' => $user_id])
			->count();
	}

	public function countFollowersBetweenDatetime(int $user_id, string $start_datetime, string $end_datetime): int
	{
		return $this->find()
			->where([
				'receiver_id' => $user_id,
				'DATE(created) >=' => $start_datetime,
				'DATE(created) <=' => $end_datetime
			])
			->count();
	}

	public function countFollowersByDate(int $user_id, string $start_datetime, string $end_datetime): array
	{
		return $this->find()
			->select(['date' => 'DATE(created)','count' => 'COUNT(*)'])
			->where([
				'receiver_id' => $user_id,
				'DATE(created) >=' => $start_datetime,
				'DATE(created) <=' => $end_datetime
			])
			->group('date')
			->order(['date' => 'ASC'])
			->enableHydration(false)
			->toArray();
	}

	public function countFollowersByCountry(int $user_id, string $start_datetime, string $end_datetime): array
	{
		return $this->find()
			->select(['country' => 'FollowingList.country','count' => 'COUNT(*)'])
			->innerJoinWith('FollowingList')
			->where([
				'receiver_id' => $user_id,
				'FollowingList.country IS NOT' => null,
				'FollowingList.country !=' => ''
			])
			->andWhere([
				'DATE(created) >=' => $start_datetime,
				'DATE(created) <=' => $end_datetime
			])
			->group(['FollowingList.country'])
			->order(['count' => 'DESC'])
			->enableHydration(false)
			->toArray();
	}

	public function countFollowersByCity(int $user_id, string $start_datetime, string $end_datetime): array
	{
		return $this->find()
			->select(['city' => 'FollowingList.city','count' => 'COUNT(*)'])
			->innerJoinWith('FollowingList')
			->where([
				'receiver_id' => $user_id,
				'FollowingList.city IS NOT' => null,
				'FollowingList.city !=' => ''
			])
			->andWhere([
				'DATE(created) >=' => $start_datetime,
				'DATE(created) <=' => $end_datetime
			])
			->group(['FollowingList.city'])
			->order(['count' => 'DESC'])
			->enableHydration(false)
			->toArray();
	}

	public function getFollowersByAge(int $user_id, string $start_datetime, string $end_datetime): array
	{
		$conn = $this->getConnection();
		$sql = "SELECT CASE
			WHEN TIMESTAMPDIFF(YEAR, Users.dob, CURDATE()) BETWEEN 18 AND 24 THEN '18-24'
			WHEN TIMESTAMPDIFF(YEAR, Users.dob, CURDATE()) BETWEEN 25 AND 34 THEN '25-34'
			WHEN TIMESTAMPDIFF(YEAR, Users.dob, CURDATE()) BETWEEN 35 AND 44 THEN '35-44'
			WHEN TIMESTAMPDIFF(YEAR, Users.dob, CURDATE()) BETWEEN 45 AND 54 THEN '45-54'
			ELSE '55+'
		END AS age_range, COUNT(*) AS count
		FROM follower F
		JOIN users Users ON F.receiver_id = Users.id
		WHERE F.receiver_id = :user_id
		  AND DATE(F.created) >= :start_datetime
		  AND DATE(F.created) <= :end_datetime
		GROUP BY age_range";
		$stmt = $conn->execute($sql, compact('user_id','start_datetime','end_datetime'));
		return $stmt->fetchAll('assoc');
	}

	public function countFollowersByGender(int $user_id, string $start_datetime, string $end_datetime, string $gender): int
	{
		return $this->find()
			->innerJoinWith('FollowerList', function(Query $q) use($gender) {
				return $q->where(['FollowerList.gender' => $gender]);
			})
			->where([
				'receiver_id' => $user_id,
				'DATE(created) >=' => $start_datetime,
				'DATE(created) <=' => $end_datetime
			])
			->count();
	}

	public function countFollowing(int $user_id): int
	{
		return $this->find()
			->innerJoinWith('FollowingList')
			->where(['sender_id' => $user_id])
			->count();
	}

	public function deleteFollowerAgainstUserID(int $user_id): void
	{
		$this->deleteAll(['OR' => ['sender_id' => $user_id,'receiver_id' => $user_id]]);
	}

	public function getUserFollowersAgainstPromotionID(int $user_id, int $promotion_id): int
	{
		return $this->find()
			->where(['receiver_id' => $user_id,'promotion_id' => $promotion_id])
			->count();
	}

	public function getUserFriends(int $user_id): array
	{
		return $this->find()
			->where([
				'OR' => [
					['Friend.user_id' => $user_id],
					['Friend.friend_id' => $user_id]
				],
				'Friend.block_id' => 0
			])
			->order(['Friend.id' => 'DESC'])
			->enableHydration(false)
			->toArray();
	}

	public function searchFollowerOrFollowingNew(string $keyword, int $starting_point, int $user_id): array
	{
		return $this->find()
			->where([
				'OR' => [
					'FollowerList.first_name LIKE' => "%{$keyword}%",
					'FollowerList.last_name LIKE'  => "%{$keyword}%",
					'FollowerList.username LIKE'   => "%{$keyword}%",
					'FollowingList.first_name LIKE'=> "%{$keyword}%",
					'FollowingList.last_name LIKE' => "%{$keyword}%",
					'FollowingList.username LIKE'  => "%{$keyword}%"
				],
				'OR' => [
					['receiver_id' => $user_id],
					['sender_id'   => $user_id]
				]
			])
			->select([
				'FollowerList.id','FollowerList.first_name','FollowerList.last_name','FollowerList.username',
				'FollowingList.id','FollowingList.first_name','FollowingList.last_name','FollowingList.username'
			])
			->limit(10)
			->offset($starting_point * 10)
			->enableHydration(false)
			->toArray();
	}

	public function searchFollower(string $keyword, int $starting_point, int $user_id): array
	{
		return $this->find()
			->where([
				'OR' => [
					'FollowerList.first_name LIKE' => "%{$keyword}%",
					'FollowerList.last_name LIKE'  => "%{$keyword}%",
					'FollowerList.username LIKE'   => "%{$keyword}%",
					'FollowingList.first_name LIKE'=> "%{$keyword}%",
					'FollowingList.last_name LIKE' => "%{$keyword}%",
					'FollowingList.username LIKE'  => "%{$keyword}%"
				],
				'receiver_id' => $user_id
			])
			->select([
				'FollowerList.id','FollowerList.first_name','FollowerList.last_name','FollowerList.username',
				'FollowingList.id','FollowingList.first_name','FollowingList.last_name','FollowingList.username'
			])
			->limit(10)
			->offset($starting_point * 10)
			->enableHydration(false)
			->toArray();
	}

	public function searchFollowing(string $keyword, int $starting_point, int $user_id): array
	{
		return $this->find()
			->where([
				'OR' => [
					'FollowingList.first_name LIKE' => "%{$keyword}%",
					'FollowingList.last_name LIKE'  => "%{$keyword}%",
					'FollowingList.username LIKE'   => "%{$keyword}%"
				],
				'sender_id' => $user_id
			])
			->select(['FollowingList.id','FollowingList.first_name','FollowingList.last_name','FollowingList.username'])
			->limit(10)
			->offset($starting_point * 10)
			->enableHydration(false)
			->toArray();
	}

	public function searchFollowerOrFollowing(string $keyword, int $starting_point, int $user_id): array
	{
		return $this->find()
			->where([
				'OR' => [
					'FollowerList.first_name LIKE' => "%{$keyword}%",
					'FollowerList.last_name LIKE'  => "%{$keyword}%",
					'FollowerList.username LIKE'   => "%{$keyword}%"
				],
				'OR' => [
					['receiver_id' => $user_id],
					['sender_id'   => $user_id]
				]
			])
			->select(['FollowerList.id','FollowerList.first_name','FollowerList.last_name','FollowerList.username'])
			->limit(10)
			->offset($starting_point * 10)
			->enableHydration(false)
			->toArray();
	}

	public function getUserFollowers(int $user_id, int $starting_point): array
	{
		return $this->find()
			->innerJoinWith('FollowerList')
			->where(['receiver_id' => $user_id])
			->contain(['FollowerList'])
			->limit(20)
			->offset($starting_point * 20)
			->order(['Follower.id' => 'DESC'])
			->enableHydration(false)
			->toArray();
	}

	public function getUserFollowersAdmin(int $user_id): array
	{
		return $this->find()
			->innerJoinWith('FollowerList')
			->where(['receiver_id' => $user_id])
			->select(['FollowerList.id','FollowerList.first_name','FollowerList.last_name','FollowerList.username'])
			->limit(1)
			->order(['Follower.id' => 'DESC'])
			->enableHydration(false)
			->toArray();
	}

	public function getUserFollowersWithoutLimit(int $user_id): array
	{
		return $this->find()
			->where(['receiver_id' => $user_id,'notification' => 1])
			->contain(['FollowerList'])
			->order(['Follower.id' => 'DESC'])
			->enableHydration(false)
			->toArray();
	}

	public function getUserFollowing(int $user_id, int $starting_point): array
	{
		return $this->find()
			->innerJoinWith('FollowingList')
			->where(['sender_id' => $user_id])
			->contain(['FollowingList'])
			->limit(20)
			->offset($starting_point * 20)
			->order(['Follower.id' => 'DESC'])
			->enableHydration(false)
			->toArray();
	}

	public function getUserFollowingStores(int $user_id, int $starting_point): array
	{
		return $this->find()
			->where(['sender_id' => $user_id,'FollowingList.business >' => 0])
			->contain(['FollowingList'])
			->limit(10)
			->offset($starting_point * 10)
			->order(['Follower.id' => 'DESC'])
			->enableHydration(false)
			->toArray();
	}

	public function getUserFollowingAdmin(int $user_id): array
	{
		return $this->find()
			->innerJoinWith('FollowingList')
			->where(['sender_id' => $user_id])
			->select(['FollowingList.id','FollowingList.first_name','FollowingList.last_name','FollowingList.username'])
			->order(['Follower.id' => 'DESC'])
			->enableHydration(false)
			->toArray();
	}

	public function getUserFollowingWithoutLimit(int $user_id): array
	{
		return $this->find()
			->where(['sender_id' => $user_id])
			->contain(['FollowingList'])
			->order(['Follower.id' => 'DESC'])
			->enableHydration(false)
			->toArray();
	}

	public function isFriend(int $user_id, int $friend_id): ?array
	{
		return $this->find()
			->where([
				'OR' => [
					['AND' => ['Friend.user_id' => $user_id,'Friend.friend_id' => $friend_id,'Friend.block_id' => 0]],
					['AND' => ['Friend.user_id' => $friend_id,'Friend.friend_id' => $user_id,'Friend.block_id' => 0]]
				]
			])
			->enableHydration(false)
			->first();
	}

	public function ifBlocked(int $user_id, int $friend_id): int
	{
		return $this->find()
			->where([
				'OR' => [
					['AND' => ['Friend.user_id' => $user_id,'Friend.friend_id' => $friend_id,'Friend.block_id !=' => 0]],
					['AND' => ['Friend.user_id' => $friend_id,'Friend.friend_id' => $user_id,'Friend.block_id !=' => 0]]
				]
			])
			->count();
	}
}
