<?php

// $Id$
// $URL$

namespace App\Services;

use App;
use App\Model;
use Nette;

class Database extends Nette\Object {
	/** @var \Nette\Database\Context @inject */
	public $context;

	/** @inject @var \App\Services\LDAP */
	public $ldap;

	/** @inject @var \Nette\DI\Container */
	public $container;

	public function createModel($name, ...$args) {
		$class = "App\\Model\\$name";
		$rc = Nette\Reflection\ClassType::from($class);
		assert('$rc->instantiable');
		$model = $rc->newInstanceArgs($args);
		$this->container->callInjects($model);
		return $model;
	}

	/** @return User */
	public function get_user($uid) {
		$table = $this->context->table('lusers');
		$selection = $table->where('name = ?', $uid);
		foreach($selection as $row)
			return $this->createModel('User', $row);
		return \App\Model\User::create($this, $uid);
	}

	/** @return Usergroup */
	public function get_usergroup($name) {
		$table = $this->context->table('lusergroups');
		$selection = $table->where('name = ?', $username);
		foreach($selection as $row)
			return $this->createModel('Usergroup', $row);
		return false;
	}

	public function get_usergroups() {
		$table = $this->context->table('lusergroups');
		$usergroups = [];
		foreach($table as $row)
			$usergroups[] = $this->createModel('Usergroup', $row);
		return $usergroups;
	}

	public function get_addresslists() {
		$table = $this->context->table('addresslists');
		$addresslists = [];
		foreach($table as $row)
			$addresslists[] = $this->createModel('Addresslist', $row);
		return $addresslists;
	}

	public function get_mailings() {
		$table = $this->context->table('mailings');
		$mailings = [];
		foreach($table as $row)
			$mailings[] = $this->createModel('Mailing', $row);
		return $mailings;
	}

	public function create_mailing($params = []) {
		return \App\Model\Mailing::create($this, $params);
	}

	public function get_stages() {
		$table = $this->context->table('stages');
		$stages = [];
		foreach($table as $row)
			$stages[] = $this->createModel('Stage', $row);
		return $stages;
	}

	public function get_random_unsent_destination() {
		$rows = $this->context->table('finaldestinations')->where('sent_when IS NULL')->limit(1);
		foreach($rows as $row)
			return $this->createModel('FinalDestination', $row);
		return null;
	}

	public function get_finaldestination_by_verp($verp) {
		$rows = $this->context->table('finaldestinations')->where('verp = ?', $verp);
		foreach($rows as $row)
			return $this->createModel('FinalDestination', $row);
		return null;
	}

	public function addresslist_bulk_insert(Addresslist $addresslist, $addresses, $append = true) {
		$id = $addresslist->primary;
		$pdo = $this->context->connection->pdo;
		$batch_size = 100;

		// make unique
		$addresses = array_change_key_case(array_combine($addresses, $addresses));

		if($append) {
			$q = $pdo->prepare("SELECT emailaddresses.address
									FROM addresslistentries
										NATURAL JOIN emailaddresses
									WHERE addresslist = ?");
			if(!$q->execute([$id]))
				throw new \ErrorException($q->errorInfo()[2]);

			while($row = $q->fetch())
				unset($addresses[strtolower($row[0])]);
			unset($found);
		} else {
			$q = $pdo->prepare("DELETE FROM addresslistentries WHERE addresslist = ?");
			if(!$q->execute([$id]))
				throw new \ErrorException($q->errorInfo()[2]);
		}

		$koppel = [];
		$insert = [];

		while(count($addresses)) {
			$batch = array_splice($addresses, -$batch_size);

			if(count($batch) == $batch_size && isset($select_q_max)) {
				$q = $select_q_max;
			} else {
				$placeholders = implode(', ', array_fill(0, count($batch), '?'));
				$q = $pdo->prepare("SELECT emailaddress, address FROM emailaddresses WHERE lower(address) IN ($placeholders)");
				if(count($batch) == $batch_size)
					$select_q_max = $q;
			}

			if(!$q->execute(array_keys($batch)))
				throw new \ErrorException($q->errorInfo()[2]);

			$found = $q->fetchAll();
			foreach($found as $row) {
				$koppel[] = $row[0];
				unset($batch[strtolower($row[1])]);
			}

			//	zolang er wat te koppelen valt:
			//		doe een bulkquery op $koppel om deze in addresslistentries te INSERTen
			while(count($koppel) > 0 && (count($koppel) >= $batch_size || !count($addresses))) {
				$koppel_batch = array_splice($koppel, -$batch_size);

				if(count($koppel_batch) == $batch_size && isset($koppel_q_max)) {
					$q = $koppel_q_max;
				} else {
					$placeholders = implode(', ', array_fill(0, count($koppel_batch), '(?, ?)'));
					$q = $pdo->prepare("INSERT INTO addresslistentries (addresslist, emailaddress) VALUES $placeholders");
					if(count($koppel_batch) == $batch_size)
						$koppel_q_max = $q;
				}

				$queryargs = [];
				foreach($koppel_batch as $emailaddress) {
					$queryargs[] = $id;
					$queryargs[] = $emailaddress;
				}

				if(!$q->execute($queryargs))
					throw new \ErrorException($q->errorInfo()[2]);
			}

			//	doe een bulkquery op $batch om deze te INSERTen in zowel de addresses-tabel als de addresslistentries-tabel
			//			(dmv een WITH-query: https://www.postgresql.org/docs/9.1/static/sql-insert.html)
			$insert += $batch;
			while(count($insert) > 0 && (count($insert) >= $batch_size || !count($addresses))) {
				$insert_batch = array_values(array_splice($insert, -$batch_size));

				if(count($insert_batch) == $batch_size && isset($insert_q_max)) {
					$q = $insert_q_max;
				} else {
					$placeholders = implode(', ', array_fill(0, count($insert_batch), '(?)'));
					$q = $pdo->prepare("
						WITH email AS (
							INSERT INTO emailaddresses (address)
								VALUES $placeholders
								RETURNING emailaddress
						) INSERT INTO addresslistentries (addresslist, emailaddress)
								SELECT ? AS addresslist, email.emailaddress FROM email
					");

					if(count($insert_batch) == $batch_size)
						$insert_q_max = $q;
				}

				$insert_batch[] = $id;
				if(!$q->execute($insert_batch))
					throw new \ErrorException($q->errorInfo()[2]);
			}
		}

		assert('count($koppel) == 0');
		assert('count($insert) == 0');
	}
}