<?php

/**
 * Vvveb
 *
 * Copyright (C) 2020  Ziadin Givan
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License as
 * published by the Free Software Foundation, either version 3 of the
 * License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU Affero General Public License for more details.
 *
 * You should have received a copy of the GNU Affero General Public License
 * along with this program.  If not, see <https://www.gnu.org/licenses/>.
 *
 */

namespace Vvveb\Sql;

class postsSQL extends \Vvveb\System\Db\mysqli {
	public function __construct() {
		parent::__construct();
	}

	function getPosts($params = []) {
		//multitple
		$results    = [];
		$stmt       = [];
		$paramTypes = ['start' => 'i', 'limit' => 'i', 'type' => 's', 'search' => 's', 'post_id' => 'a'];

		$prevSql = $sql ?? '';
		$sql     = 'SELECT *
		FROM post AS posts
			LEFT JOIN post_description pd ON (posts.post_id = pd.post_id AND pd.language_id = 1)  
			LEFT JOIN admin u ON (posts.user_id = u.admin_id)  
		WHERE 1';

		if (isset($params['type'])) {
			$sql .= ' AND posts.type = :type ';
		}

		if (isset($params['search'])) {
			$sql .= ' AND pd.name LIKE CONCAT(\'%\',:search,\'%\') ';
		}

		if (isset($params['post_id']) && count($params['post_id']) > 0) {
			$sql .= ' AND posts.post_id IN (:post_id) ';
		}

		if (isset($params['limit'])) {
			$sql .= ' LIMIT :start, :limit ';
		}

		if ($sql) {
			$stmt['posts'] = $this->execute($sql, $params, $paramTypes);

			$result = false;

			if ($stmt['posts']) {
				if (method_exists($stmt['posts'], 'get_result')) {
					$result = $stmt['posts']->get_result();
				} else {
					$result = $this->get_result($stmt['posts']);
				}
			}

			/*
			if ('posts' == '_') {
				$value = $result->fetch_all(MYSQLI_ASSOC);
				if (is_array($value))
				{
					$results = $results + $value;
				} else
				{
					$results = $value;
				}
			} else { */
			if (! empty('')) {
				if ($result) {
					while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
						$values = $row;

						if (! empty('')) {
							//$values = $row[''];
							$values = $row['array_value'];
						}

						if ('posts' == '_') {
							//$results[$row['']] = $values;
							$results[$row['array_key']] = $values;
						} else {
							//$results['posts'][$row['']] = $values;

							$results['posts'][$row['array_key']] = $values;
						}
					}
				}
			} else {
				if ('posts' == '_') {
					$value = $result->fetch_all(MYSQLI_ASSOC);

					if (is_array($value)) {
						$results = $results + $value;
					} else {
						$results = $value;
					}
				} else {
					$results['posts'] = $result->fetch_all(MYSQLI_ASSOC);
				}
			}
			//}
		}

		$prevSql = $sql ?? '';
		$sql     = 'SELECT count(*) FROM (';

		$sql .= $this->sqlCount($prevSql, 'posts.post_id', $this->prefix . 'post');
		$sql .= '
	
	) as count';

		if ($sql) {
			$stmt['count'] = $this->execute($sql, $params, $paramTypes);

			$result = false;

			if ($stmt['count']) {
				if (method_exists($stmt['count'], 'get_result')) {
					$result = $stmt['count']->get_result();
				} else {
					$result = $this->get_result($stmt['count']);
				}
			}

			/*
			if ('count' == '_') {
				$value = $result->fetch_array(MYSQLI_NUM)[0];
				if (is_array($value))
				{
					$results = $results + $value;
				} else
				{
					$results = $value;
				}
			} else { */
			if (! empty('')) {
				if ($result) {
					while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
						$values = $row;

						if (! empty('')) {
							//$values = $row[''];
							$values = $row['array_value'];
						}

						if ('count' == '_') {
							//$results[$row['']] = $values;
							$results[$row['array_key']] = $values;
						} else {
							//$results['count'][$row['']] = $values;

							$results['count'][$row['array_key']] = $values;
						}
					}
				}
			} else {
				if ('count' == '_') {
					$value = $result->fetch_array(MYSQLI_NUM)[0];

					if (is_array($value)) {
						$results = $results + $value;
					} else {
						$results = $value;
					}
				} else {
					$results['count'] = $result->fetch_array(MYSQLI_NUM)[0];
				}
			}
			//}
		}

		if ($results) {
			return $results;
		}
	}

	function getPost($params = []) {
		//multitple
		$results    = [];
		$stmt       = [];
		$paramTypes = ['post_id' => 'i'];

		$prevSql = $sql ?? '';
		$sql     = 'SELECT * 
			FROM post AS _
		WHERE post_id = :post_id';

		if ($sql) {
			$stmt['_'] = $this->execute($sql, $params, $paramTypes);

			$result = false;

			if ($stmt['_']) {
				if (method_exists($stmt['_'], 'get_result')) {
					$result = $stmt['_']->get_result();
				} else {
					$result = $this->get_result($stmt['_']);
				}
			}

			/*
			if ('_' == '_') {
				$value = $result->fetch_array(MYSQLI_ASSOC);
				if (is_array($value))
				{
					$results = $results + $value;
				} else
				{
					$results = $value;
				}
			} else { */
			if (! empty('')) {
				if ($result) {
					while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
						$values = $row;

						if (! empty('')) {
							//$values = $row[''];
							$values = $row['array_value'];
						}

						if ('_' == '_') {
							//$results[$row['']] = $values;
							$results[$row['array_key']] = $values;
						} else {
							//$results['_'][$row['']] = $values;

							$results['_'][$row['array_key']] = $values;
						}
					}
				}
			} else {
				if ('_' == '_') {
					$value = $result->fetch_array(MYSQLI_ASSOC);

					if (is_array($value)) {
						$results = $results + $value;
					} else {
						$results = $value;
					}
				} else {
					$results['_'] = $result->fetch_array(MYSQLI_ASSOC);
				}
			}
			//}
		}

		$prevSql = $sql ?? '';
		$sql     = 'SELECT *, language_id as array_key FROM post_description 
		WHERE post_id = :post_id';

		if ($sql) {
			$stmt['post_description'] = $this->execute($sql, $params, $paramTypes);

			$result = false;

			if ($stmt['post_description']) {
				if (method_exists($stmt['post_description'], 'get_result')) {
					$result = $stmt['post_description']->get_result();
				} else {
					$result = $this->get_result($stmt['post_description']);
				}
			}

			/*
			if ('post_description' == '_') {
				$value = $result->fetch_array(MYSQLI_ASSOC);
				if (is_array($value))
				{
					$results = $results + $value;
				} else
				{
					$results = $value;
				}
			} else { */
			if (! empty('language_id')) {
				if ($result) {
					while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
						$values = $row;

						if (! empty('')) {
							//$values = $row[''];
							$values = $row['array_value'];
						}

						if ('post_description' == '_') {
							//$results[$row['language_id']] = $values;
							$results[$row['array_key']] = $values;
						} else {
							//$results['post_description'][$row['language_id']] = $values;

							$results['post_description'][$row['array_key']] = $values;
						}
					}
				}
			} else {
				if ('post_description' == '_') {
					$value = $result->fetch_array(MYSQLI_ASSOC);

					if (is_array($value)) {
						$results = $results + $value;
					} else {
						$results = $value;
					}
				} else {
					$results['post_description'] = $result->fetch_array(MYSQLI_ASSOC);
				}
			}
			//}
		}

		if ($results) {
			return $results;
		}
	}

	function addPost($params = []) {
		//multitple
		$results    = [];
		$stmt       = [];
		$paramTypes = ['post' => 'a'];

		$prevSql = $sql ?? '';
		$sql     = '';

		$filterArray = [
			'post_id' => [
				'd' => NULL,
				'n' => false,
				't' => 'bigint',
				'e' => 'auto_increment',
			],
			'user_id' => [
				'd' => '0',
				'n' => false,
				't' => 'bigint',
			],
			'status' => [
				'd' => '\'publish\'',
				'n' => false,
				't' => 'varchar',
			],
			'image' => [
				'd' => '\'\'',
				'n' => false,
				't' => 'varchar',
			],
			'comment_status' => [
				'd' => '\'open\'',
				'n' => false,
				't' => 'varchar',
			],
			'password' => [
				'd' => '\'\'',
				'n' => false,
				't' => 'varchar',
			],
			'parent' => [
				'd' => '0',
				'n' => false,
				't' => 'bigint',
			],
			'menu_order' => [
				'd' => '0',
				'n' => false,
				't' => 'int',
			],
			'type' => [
				'd' => '\'post\'',
				'n' => false,
				't' => 'varchar',
			],
			'comment_count' => [
				'd' => '0',
				'n' => false,
				't' => 'bigint',
			],
			'date_added' => [
				'd' => '\'1000-01-01 00:00:00\'',
				'n' => false,
				't' => 'datetime',
			],
			'date_modified' => [
				'd' => '\'1000-01-01 00:00:00\'',
				'n' => false,
				't' => 'datetime',
			],
		];
		$params['post_data']= $this->filter($params['post'], $filterArray,false);

		$sql = '';

		if ($sql) {
			$stmt[''] = $this->execute($sql, $params, $paramTypes);

			$result = false;

			if ($stmt['']) {
				if (method_exists($stmt[''], 'get_result')) {
					$result = $stmt['']->get_result();
				} else {
					$result = $this->get_result($stmt['']);
				}
			}

			/*
			if ('' == '_') {
				$value = $this->insertId;
				if (is_array($value))
				{
					$results = $results + $value;
				} else
				{
					$results = $value;
				}
			} else { */
			if (! empty('')) {
				if ($result) {
					while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
						$values = $row;

						if (! empty('')) {
							//$values = $row[''];
							$values = $row['array_value'];
						}

						if ('' == '_') {
							//$results[$row['']] = $values;
							$results[$row['array_key']] = $values;
						} else {
							//$results[''][$row['']] = $values;

							$results[''][$row['array_key']] = $values;
						}
					}
				}
			} else {
				if ('' == '_') {
					$value = $this->insertId;

					if (is_array($value)) {
						$results = $results + $value;
					} else {
						$results = $value;
					}
				} else {
					$results[''] = $this->insertId;
				}
			}
			//}
		}

		$prevSql = $sql ?? '';
		$sql     = 'INSERT INTO post 
			
			(';
		$sql .= '`' . implode('`,`', array_keys($params['post_data']));
		$sql .= '` )
			
	  	VALUES ( :post_data )';

		if ($sql) {
			$stmt['post'] = $this->execute($sql, $params, $paramTypes);

			$result = false;

			if ($stmt['post']) {
				if (method_exists($stmt['post'], 'get_result')) {
					$result = $stmt['post']->get_result();
				} else {
					$result = $this->get_result($stmt['post']);
				}
			}

			/*
			if ('post' == '_') {
				$value = $this->insertId;
				if (is_array($value))
				{
					$results = $results + $value;
				} else
				{
					$results = $value;
				}
			} else { */
			if (! empty('')) {
				if ($result) {
					while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
						$values = $row;

						if (! empty('')) {
							//$values = $row[''];
							$values = $row['array_value'];
						}

						if ('post' == '_') {
							//$results[$row['']] = $values;
							$results[$row['array_key']] = $values;
						} else {
							//$results['post'][$row['']] = $values;

							$results['post'][$row['array_key']] = $values;
						}
					}
				}
			} else {
				if ('post' == '_') {
					$value = $this->insertId;

					if (is_array($value)) {
						$results = $results + $value;
					} else {
						$results = $value;
					}
				} else {
					$results['post'] = $this->insertId;
				}
			}
			//}
		}

		$prevSql = $sql ?? '';
		$sql     = '';

		$filterArray = [
			'post_id' => [
				'd' => NULL,
				'n' => false,
				't' => 'int',
			],
			'language_id' => [
				'd' => NULL,
				'n' => false,
				't' => 'int',
			],
			'name' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'slug' => [
				'd' => NULL,
				'n' => false,
				't' => 'varchar',
			],
			'content' => [
				'd' => NULL,
				'n' => false,
				't' => 'longtext',
			],
			'excerpt' => [
				'd' => NULL,
				'n' => false,
				't' => 'text',
			],
		];

		foreach ($params['post_description'] as $key => &$filter) {
			$params['post_description'][$key] = $this->filter($filter, $filterArray,false);
		}

		$sql = '';

		if ($sql) {
			$stmt[''] = $this->execute($sql, $params, $paramTypes);

			$result = false;

			if ($stmt['']) {
				if (method_exists($stmt[''], 'get_result')) {
					$result = $stmt['']->get_result();
				} else {
					$result = $this->get_result($stmt['']);
				}
			}

			/*
			if ('' == '_') {
				$value = $this->insertId;
				if (is_array($value))
				{
					$results = $results + $value;
				} else
				{
					$results = $value;
				}
			} else { */
			if (! empty('')) {
				if ($result) {
					while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
						$values = $row;

						if (! empty('')) {
							//$values = $row[''];
							$values = $row['array_value'];
						}

						if ('' == '_') {
							//$results[$row['']] = $values;
							$results[$row['array_key']] = $values;
						} else {
							//$results[''][$row['']] = $values;

							$results[''][$row['array_key']] = $values;
						}
					}
				}
			} else {
				if ('' == '_') {
					$value = $this->insertId;

					if (is_array($value)) {
						$results = $results + $value;
					} else {
						$results = $value;
					}
				} else {
					$results[''] = $this->insertId;
				}
			}
			//}
		}

		$prevSql = $sql ?? '';

		foreach ($params['post']['post_description'] as $key => $rowParent) {
			$params['each']     = $rowParent;
			$paramTypes['each'] = 'a';
			$sql                = 'INSERT INTO post_description 
		
				(';
			$sql .= '`' . implode('`,`', array_keys($params['each']));
			$sql .= '` , post_id)
			
			VALUES ( :each, ' . ($results['post'] ?? 'NULL') . ')';

			if ($sql) {
				$stmt['post_description'] = $this->execute($sql, $params, $paramTypes);

				$result = false;

				if ($stmt['post_description']) {
					if (method_exists($stmt['post_description'], 'get_result')) {
						$result = $stmt['post_description']->get_result();
					} else {
						$result = $this->get_result($stmt['post_description']);
					}
				}

				/*
				if ('post_description' == '_') {
					$value = $this->insertId;
					if (is_array($value))
					{
						$results = $results + $value;
					} else
					{
						$results = $value;
					}
				} else { */
				if (! empty('')) {
					if ($result) {
						while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
							$values = $row;

							if (! empty('')) {
								//$values = $row[''];
								$values = $row['array_value'];
							}

							if ('post_description' == '_') {
								//$results[$row['']] = $values;
								$results[$row['array_key']] = $values;
							} else {
								//$results['post_description'][$row['']] = $values;

								$results['post_description'][$row['array_key']] = $values;
							}
						}
					}
				} else {
					if ('post_description' == '_') {
						$value = $this->insertId;

						if (is_array($value)) {
							$results = $results + $value;
						} else {
							$results = $value;
						}
					} else {
						$results['post_description'] = $this->insertId;
					}
				}
				//}
			}
		}
		unset($params['each'], $paramTypes['each']);

		if ($results) {
			return $results;
		}
	}

	function editPost($params = []) {
		//multitple
		$results    = [];
		$stmt       = [];
		$paramTypes = ['post_array' => 'a', 'post_id' => 'i'];

		$prevSql = $sql ?? '';
		$sql     = 'DELETE FROM post_description WHERE post_id = :post_id';

		if ($sql) {
			$stmt['post_description'] = $this->execute($sql, $params, $paramTypes);

			$result = false;

			if ($stmt['post_description']) {
				if (method_exists($stmt['post_description'], 'get_result')) {
					$result = $stmt['post_description']->get_result();
				} else {
					$result = $this->get_result($stmt['post_description']);
				}
			}

			/*
			if ('post_description' == '_') {
				$value = $this->insertId;
				if (is_array($value))
				{
					$results = $results + $value;
				} else
				{
					$results = $value;
				}
			} else { */
			if (! empty('')) {
				if ($result) {
					while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
						$values = $row;

						if (! empty('')) {
							//$values = $row[''];
							$values = $row['array_value'];
						}

						if ('post_description' == '_') {
							//$results[$row['']] = $values;
							$results[$row['array_key']] = $values;
						} else {
							//$results['post_description'][$row['']] = $values;

							$results['post_description'][$row['array_key']] = $values;
						}
					}
				}
			} else {
				if ('post_description' == '_') {
					$value = $this->insertId;

					if (is_array($value)) {
						$results = $results + $value;
					} else {
						$results = $value;
					}
				} else {
					$results['post_description'] = $this->insertId;
				}
			}
			//}
		}

		$prevSql = $sql ?? '';

		foreach ($params['post_array']['post_description'] as $key => $rowParent) {
			$params['each']     = $rowParent;
			$paramTypes['each'] = 'a';
			$sql                = 'INSERT INTO post_description 
		
				(';
			$sql .= '`' . implode('`,`', array_keys($params['each']));
			$sql .= '` , post_id, excerpt)
			
			VALUES ( :each, :post_id, \'\')';

			if ($sql) {
				$stmt['post_description'] = $this->execute($sql, $params, $paramTypes);

				$result = false;

				if ($stmt['post_description']) {
					if (method_exists($stmt['post_description'], 'get_result')) {
						$result = $stmt['post_description']->get_result();
					} else {
						$result = $this->get_result($stmt['post_description']);
					}
				}

				/*
				if ('post_description' == '_') {
					$value = $this->insertId;
					if (is_array($value))
					{
						$results = $results + $value;
					} else
					{
						$results = $value;
					}
				} else { */
				if (! empty('')) {
					if ($result) {
						while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
							$values = $row;

							if (! empty('')) {
								//$values = $row[''];
								$values = $row['array_value'];
							}

							if ('post_description' == '_') {
								//$results[$row['']] = $values;
								$results[$row['array_key']] = $values;
							} else {
								//$results['post_description'][$row['']] = $values;

								$results['post_description'][$row['array_key']] = $values;
							}
						}
					}
				} else {
					if ('post_description' == '_') {
						$value = $this->insertId;

						if (is_array($value)) {
							$results = $results + $value;
						} else {
							$results = $value;
						}
					} else {
						$results['post_description'] = $this->insertId;
					}
				}
				//}
			}
		}
		unset($params['each'], $paramTypes['each']);

		$prevSql = $sql ?? '';
		$sql     = '';

		$filterArray = [
			'post_id' => [
				'd' => NULL,
				'n' => false,
				't' => 'bigint',
				'e' => 'auto_increment',
			],
			'user_id' => [
				'd' => '0',
				'n' => false,
				't' => 'bigint',
			],
			'status' => [
				'd' => '\'publish\'',
				'n' => false,
				't' => 'varchar',
			],
			'image' => [
				'd' => '\'\'',
				'n' => false,
				't' => 'varchar',
			],
			'comment_status' => [
				'd' => '\'open\'',
				'n' => false,
				't' => 'varchar',
			],
			'password' => [
				'd' => '\'\'',
				'n' => false,
				't' => 'varchar',
			],
			'parent' => [
				'd' => '0',
				'n' => false,
				't' => 'bigint',
			],
			'menu_order' => [
				'd' => '0',
				'n' => false,
				't' => 'int',
			],
			'type' => [
				'd' => '\'post\'',
				'n' => false,
				't' => 'varchar',
			],
			'comment_count' => [
				'd' => '0',
				'n' => false,
				't' => 'bigint',
			],
			'date_added' => [
				'd' => '\'1000-01-01 00:00:00\'',
				'n' => false,
				't' => 'datetime',
			],
			'date_modified' => [
				'd' => '\'1000-01-01 00:00:00\'',
				'n' => false,
				't' => 'datetime',
			],
		];
		$params['post_array']= $this->filter($params['post_array'], $filterArray,false);

		$sql = '';

		if (! empty($params['post_array'])) {
			$sql .= ' UPDATE post 
				
				SET';

			list($_sql, $_params) = $this->expandList($params['post_array'], 'post_array');

			$sql .= ' ' . $_sql;

			if (is_array($_params)) {
				$paramTypes = array_merge($paramTypes, $_params);
			}

			$sql .= ' ' . '
	WHERE post_id = :post_id ';
		}

		if ($sql) {
			$stmt['post'] = $this->execute($sql, $params, $paramTypes);

			$result = false;

			if ($stmt['post']) {
				if (method_exists($stmt['post'], 'get_result')) {
					$result = $stmt['post']->get_result();
				} else {
					$result = $this->get_result($stmt['post']);
				}
			}

			/*
			if ('post' == '_') {
				$value = $this->insertId;
				if (is_array($value))
				{
					$results = $results + $value;
				} else
				{
					$results = $value;
				}
			} else { */
			if (! empty('')) {
				if ($result) {
					while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
						$values = $row;

						if (! empty('')) {
							//$values = $row[''];
							$values = $row['array_value'];
						}

						if ('post' == '_') {
							//$results[$row['']] = $values;
							$results[$row['array_key']] = $values;
						} else {
							//$results['post'][$row['']] = $values;

							$results['post'][$row['array_key']] = $values;
						}
					}
				}
			} else {
				if ('post' == '_') {
					$value = $this->insertId;

					if (is_array($value)) {
						$results = $results + $value;
					} else {
						$results = $value;
					}
				} else {
					$results['post'] = $this->insertId;
				}
			}
			//}
		}

		if ($results) {
			return $results;
		}
	}

	function deletePost($params = []) {
		//multitple
		$results    = [];
		$stmt       = [];
		$paramTypes = ['id_post' => 'i'];

		$prevSql = $sql ?? '';
		$sql     = 'DELETE FROM post WHERE post_id = :id_post';

		if ($sql) {
			$stmt['post'] = $this->execute($sql, $params, $paramTypes);

			$result = false;

			if ($stmt['post']) {
				if (method_exists($stmt['post'], 'get_result')) {
					$result = $stmt['post']->get_result();
				} else {
					$result = $this->get_result($stmt['post']);
				}
			}

			/*
			if ('post' == '_') {
				$value = $this->affectedRows;
				if (is_array($value))
				{
					$results = $results + $value;
				} else
				{
					$results = $value;
				}
			} else { */
			if (! empty('')) {
				if ($result) {
					while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
						$values = $row;

						if (! empty('')) {
							//$values = $row[''];
							$values = $row['array_value'];
						}

						if ('post' == '_') {
							//$results[$row['']] = $values;
							$results[$row['array_key']] = $values;
						} else {
							//$results['post'][$row['']] = $values;

							$results['post'][$row['array_key']] = $values;
						}
					}
				}
			} else {
				if ('post' == '_') {
					$value = $this->affectedRows;

					if (is_array($value)) {
						$results = $results + $value;
					} else {
						$results = $value;
					}
				} else {
					$results['post'] = $this->affectedRows;
				}
			}
			//}
		}

		if ($results) {
			return $results;
		}
	}

	function postTags($params = []) {
		//multitple
		$results    = [];
		$stmt       = [];
		$paramTypes = ['id_post' => 'i'];

		if ($results) {
			return $results;
		}
	}

	function postCategories($params = []) {
		//multitple
		$results    = [];
		$stmt       = [];
		$paramTypes = ['id_post' => 'i'];

		if ($results) {
			return $results;
		}
	}
}
