<?php
namespace classes\lib;

class CGRewards {

	public static function create_cg_reward(int $concierge_id, int $event_id, int $cgr_type_id, int $points, string $comments = NULL): bool {
		global $db;
		$sql = "
			INSERT INTO cg_rewards
			SET
				concierge_id = :concierge_id,
				event_id = :event_id,
				cgr_type_id = :cgr_type_id,
				points = :points,
				comments = :comments,
				create_date = NOW(),
				update_date = NOW(),
				create_by = :user_id,
				update_by = :user_id
		";
		$stmt_update = $db->prepare($sql);
		$stmt_update->execute([
			':concierge_id' => $concierge_id,
			':event_id' => $event_id,
			':cgr_type_id' => $cgr_type_id,
			':points' => $points,
			':comments' => $comments,
			':user_id' => trim($_SESSION['user_id']),
		]);
		return self::approve_cg_reward_status($concierge_id);
	}

	public static function approve_cg_reward_status(int $concierge_id): bool {
		global $db;
		$sql = "
			UPDATE concierges
			SET
				cgr_approved = 1
			WHERE
				concierge_id = :concierge_id AND cgr_approved = 0
		";
		$stmt_update = $db->prepare($sql);
		return $stmt_update->execute([
			':concierge_id' => $concierge_id,
		]);
	}

	public static function allocate_recent_cg_rewards(int $concierge_id, int $lookback_days = 30, string $cgr_type = 'Event Attendance'): int {
		global $db;
		$sql = "
			SELECT
				ec.concierge_id,
				ec.event_id,
				cgrt.id AS cgr_type_id,
				cgrt.points
			FROM events_concierges ec 
				JOIN events e ON (ec.event_id = e.event_id)
				JOIN cg_reward_types cgrt ON (cgrt.cgr_type = :cgr_type)
			WHERE
				concierge_id = :concierge_id AND
				e.event_date_time BETWEEN DATE_SUB(NOW(), INTERVAL :lookback_days DAY) AND NOW() AND
				ec.attended_1 = 1
		";
		$stmt = $db->prepare($sql);
		$stmt->execute([
			':concierge_id' => $concierge_id,
			':lookback_days' => $lookback_days,
			':cgr_type' => $cgr_type,
		]);
		$count = 0;
		while ($row = $stmt->fetch(\PDO::FETCH_OBJ)) :
			self::create_cg_reward($row->concierge_id, $row->event_id, $row->cgr_type_id, $row->points, "Given upon joining for events in the last $lookback_days days.");
			$count++;
		endwhile;
		return $count;
	}

	public static function apply_rewards_to_event(int $event_id, string $cgr_type = 'Event Attendance') {
		global $db;
		$sql = "
			SELECT
				ec.concierge_id,
				ec.event_id,
				cgrt.id AS cgr_type_id,
				cgrt.points
			FROM
				events e
				JOIN events_concierges ec ON (e.event_id = ec.event_id)
				JOIN concierges c ON (ec.concierge_id = c.concierge_id)
				JOIN cg_reward_types cgrt ON (cgrt.cgr_type = :cgr_type)
				LEFT JOIN cg_rewards cgr ON (ec.event_id = cgr.event_id AND ec.concierge_id = cgr.concierge_id AND cgrt.id = cgr.cgr_type_id)
			WHERE
				e.event_id = :event_id
				AND c.cgr = 1
				AND ec.attended_1 = 1
				AND cgr.id IS NULL
		";
		$stmt = $db->prepare($sql);
		$stmt->execute([
			':event_id' => $event_id,
			':cgr_type' => $cgr_type,
		]);
		while ($row = $stmt->fetch(\PDO::FETCH_OBJ)) :
			self::create_cg_reward($row->concierge_id, $row->event_id, $row->cgr_type_id, $row->points, "Given for attending event.");
		endwhile;
	}

	public static function update_master_pull_date(array $ids_to_update): void {
		global $db;
		$in_query = implode(',', array_fill(0, count($ids_to_update), '?'));
		$sql = "
			UPDATE concierges
			SET
				cgr_master_pull_date = NOW()
			WHERE 
				concierge_id IN ($in_query)
		";
		$stmt_update = $db->prepare($sql);
		$stmt_update->execute($ids_to_update);
	}

	public static function update_activity_pull(array $ids_to_update): void {
		global $db;
		$sql = "
			INSERT INTO cg_reward_pulls SET pull_date = NOW()
		";
		$stmt_insert = $db->prepare($sql);
		$stmt_insert->execute();
		$last_id = $db->lastInsertId();

		$in_query = implode(',', array_fill(0, count($ids_to_update), '?'));
		$sql = "
			UPDATE cg_rewards
			SET
				cgr_pull_id = ?
			WHERE 
				id IN ($in_query)
		";
		$stmt_update = $db->prepare($sql);
		$update_parms = array_merge([
			$last_id,
		], $ids_to_update);
		$stmt_update->execute($update_parms);
	}
}