<?php

namespace ImageSeoWP\Services;

if (!defined('ABSPATH')) {
	exit;
}

use ImageSeoWP\Helpers\AttachmentMeta;
use ImageSeoWP\Helpers\Bulk\AltSpecification;

class QueryImages
{
	protected $numberImageNonOptimizeAlt = 0;

	protected $totalImages = 0;

	public function getImagesNoAltByListIds($ids)
	{
		global $wpdb;
		$sqlQuery = 'SELECT p.ID ';
		$sqlQuery .= "FROM {$wpdb->posts} p ";
		$sqlQuery .= "LEFT JOIN {$wpdb->postmeta} AS pmOnlyEmpty ON ( p.ID = pmOnlyEmpty.post_id ) ";
		$sqlQuery .= "LEFT JOIN {$wpdb->postmeta} AS pmOnlyEmpty2 ON (p.ID = pmOnlyEmpty2.post_id AND pmOnlyEmpty2.meta_key = '_wp_attachment_image_alt' ) ";

		// == WHERE
		$sqlQuery .= 'WHERE 1=1 ';
		$sqlQuery .= "AND p.ID IN ($ids) ";
		$sqlQuery .= "AND (
            ( pmOnlyEmpty.meta_key = '_wp_attachment_image_alt' AND pmOnlyEmpty.meta_value = '' )
            OR
            pmOnlyEmpty2.post_id IS NULL
            )  ";

		$sqlQuery .= "AND (p.post_mime_type = 'image/jpeg' OR p.post_mime_type = 'image/gif' OR p.post_mime_type = 'image/jpg' OR p.post_mime_type = 'image/png' OR p.post_mime_type = 'image/webp') ";
		$sqlQuery .= "AND p.post_type = 'attachment' ";
		$sqlQuery .= "AND ((p.post_status = 'publish' OR p.post_status = 'future' OR p.post_status = 'pending' OR p.post_status = 'inherit' OR p.post_status = 'private')) ";
		$sqlQuery .= 'GROUP BY p.ID';

		$result = $wpdb->get_results($sqlQuery, ARRAY_N);
		if (empty($result)) {
			return [];
		}

		return call_user_func_array('array_merge', $result);
	}

	public function getWooCommerceIdsGallery($options)
	{
		global $wpdb;
		$sqlQuery = "SELECT pm.meta_value
            FROM {$wpdb->posts} p
            INNER JOIN {$wpdb->postmeta} pm ON (
                pm.post_id = p.id
                AND pm.meta_value IS NOT NULL
                AND pm.meta_key = '_product_image_gallery'
        )";

		$result = $wpdb->get_results($sqlQuery, ARRAY_N);
		if (empty($result)) {
			return [];
		}

		$ids = [];
		foreach ($result as $item) {
			$idsGallery = array_filter(explode(',', $item[0]));

			$ids = array_merge($ids, $idsGallery);
		}

		$ids = array_unique($ids);

		if (AltSpecification::FILL_ONLY_EMPTY !== $options['alt_fill']) {
			return $ids;
		}

		return $this->getImagesNoAltByListIds(implode(', ', $ids));
	}

	public function getPostByAttachmentId($attachmentId)
	{
		global $wpdb;

		$sqlQuery = "SELECT {$wpdb->posts}.ID
            FROM {$wpdb->posts}
            INNER JOIN {$wpdb->postmeta} ON ( {$wpdb->posts}.ID = {$wpdb->postmeta}.post_id AND  {$wpdb->postmeta}.meta_key = '_thumbnail_id' )
            WHERE 1=1
            AND {$wpdb->postmeta}.meta_value = '{$attachmentId}'";

		$ids = $wpdb->get_results($sqlQuery);
		if (empty($ids)) {
			return null;
		}

		return $ids[0]->ID;
	}

	public function getQueryArgsDefault()
	{
		return [
			'post_type'      => 'attachment',
			'posts_per_page' => -1,
			'post_status'    => ['publish', 'pending', 'future', 'private', 'inherit'],
			'post_mime_type' => 'image/jpeg,image/gif,image/jpg,image/png,image/webp',
		];
	}

	public function getQueryArgsIdsAttachmentOptimized()
	{
		return  array_merge($this->getQueryArgsDefault(), [
			'meta_key'       => AttachmentMeta::REPORT,
			'meta_compare'   => 'EXISTS',
			'fields'         => 'ids',
			'orderby'        => 'id',
			'order'          => 'ASC',
		]);
	}

	public function getIdsAttachmentOptimized()
	{
		$args = $this->getQueryArgsIdsAttachmentOptimized();

		return  new \WP_Query($args);
	}

	public function getAllIdsAttachment()
	{
		$args = array_merge($this->getQueryArgsDefault(), [
			'fields'         => 'ids',
			'orderby'        => 'id',
			'order'          => 'ASC',
		]);

		return  new \WP_Query($args);
	}

	public function getIdsAttachmentWithAltEmpty()
	{
		$args = array_merge($this->getQueryArgsDefault(), [
			'fields'         => 'ids',
			'orderby'        => 'id',
			'order'          => 'ASC',
			'meta_query'     => [
				'relation' => 'OR',
				[
					'key'     => '_wp_attachment_image_alt',
					'value'   => '',
					'compare' => '=',
				],
				[
					'key'     => '_wp_attachment_image_alt',
					'compare' => 'NOT EXISTS',
				],
			],
		]);

		return  new \WP_Query($args);
	}

	/**
	 * @return int
	 */
	public function getNumberImageNonOptimizeAlt($options = ['withCache' => true, 'forceQuery' => false])
	{

		if (!isset($options['withCache'])) {
			$options['withCache'] = true;
		}

		if (!isset($options['forceQuery'])) {
			$options['forceQuery'] = false;
		}

		if ($this->numberImageNonOptimizeAlt && $options['withCache']) {
			return $this->numberImageNonOptimizeAlt;
		}

		if (!$options['forceQuery']) {
			$total = get_option('imageseo_get_number_image_non_optimize_alt');
			if ($total) {
				$this->numberImageNonOptimizeAlt = $total;

				return $total;
			}
		}

		global $wpdb;
		$sqlQuery = "SELECT COUNT(DISTINCT {$wpdb->posts}.ID)
            FROM {$wpdb->posts}
            LEFT JOIN {$wpdb->postmeta} ON ( {$wpdb->posts}.ID = {$wpdb->postmeta}.post_id )
            LEFT JOIN {$wpdb->postmeta} AS mt1 ON ({$wpdb->posts}.ID = mt1.post_id AND mt1.meta_key = '_wp_attachment_image_alt' )
            WHERE 1=1
            AND ({$wpdb->posts}.post_mime_type = 'image/jpeg' OR {$wpdb->posts}.post_mime_type = 'image/jpg' OR {$wpdb->posts}.post_mime_type = 'image/png' OR {$wpdb->posts}.post_mime_type = 'image/webp')
            AND ( ( {$wpdb->postmeta}.meta_key = '_wp_attachment_image_alt' AND {$wpdb->postmeta}.meta_value = '' ) OR mt1.post_id IS NULL )
            AND {$wpdb->posts}.post_type = 'attachment'
            AND (({$wpdb->posts}.post_status = 'publish' OR {$wpdb->posts}.post_status = 'inherit'))";

		$this->numberImageNonOptimizeAlt = (int) $wpdb->get_var(apply_filters('imageseo_sql_query_number_image_non_optimize_alt', $sqlQuery));

		update_option('imageseo_get_number_image_non_optimize_alt', $this->numberImageNonOptimizeAlt, false);

		return $this->numberImageNonOptimizeAlt;
	}

	/**
	 * @return int
	 */
	public function getTotalImages($options = ['withCache' => true, 'forceQuery' => false])
	{
		if (!isset($options['withCache'])) {
			$options['withCache'] = true;
		}

		if (!isset($options['forceQuery'])) {
			$options['forceQuery'] = false;
		}

		if ($this->totalImages && $options['withCache']) {
			return $this->totalImages;
		}

		if (!$options['forceQuery']) {
			$total = get_option('imageseo_get_total_images');
			if ($total) {
				$this->totalImages = $total;

				return $total;
			}
		}

		global $wpdb;

		$sqlQuery = "SELECT COUNT(DISTINCT {$wpdb->posts}.ID)
            FROM {$wpdb->posts}
            WHERE 1=1
            AND ({$wpdb->posts}.post_mime_type = 'image/jpeg' OR {$wpdb->posts}.post_mime_type = 'image/jpg' OR {$wpdb->posts}.post_mime_type = 'image/png' OR {$wpdb->posts}.post_mime_type = 'image/webp')
            AND {$wpdb->posts}.post_type = 'attachment'
            AND (({$wpdb->posts}.post_status = 'publish' OR {$wpdb->posts}.post_status = 'inherit' ))";

		$this->totalImages = (int) $wpdb->get_var(apply_filters('imageseo_sql_query_total_images', $sqlQuery));

		update_option('imageseo_get_total_images', $this->totalImages, false);

		return $this->totalImages;
	}
}
