<?php
/**
 * Query class for retrieving outdated and excluded content.
 *
 * @package ContentCortex
 */

defined( 'ABSPATH' ) || exit;

/**
 * Class OCAP_Query
 */
class OCAP_Query {

	/**
	 * Query arguments.
	 *
	 * @var array
	 */
	protected $args = array();

	/**
	 * Total items matching query.
	 *
	 * @var int
	 */
	protected $total_items = 0;

	/**
	 * Constructor.
	 *
	 * @param array $args Query parameters.
	 */
	public function __construct( $args = array() ) {
		$defaults = array(
			'post_type' => '',
			'author'    => 0,
			'search'    => '',
			'view'      => 'stale', // Stale or excluded view.
			'orderby'   => 'days',
			'order'     => 'DESC', // For 'days', DESC means stalest first (oldest date).
			'per_page'  => 20,
			'paged'     => 1,
			'offset'    => null,
			'fields'    => 'all',
		);

		$this->args = wp_parse_args( $args, $defaults );
		$this->args = apply_filters( 'ocap_stale_query_args', $this->args );
	}

	/**
	 * Get enabled post types with their day limits.
	 *
	 * @return array Array of post_type => days.
	 */
	public function get_enabled_post_types() {
		$settings   = ocap_get_settings();
		$post_types = isset( $settings['post_types'] ) ? $settings['post_types'] : array();
		$enabled    = array();

		foreach ( $post_types as $type => $config ) {
			if ( ! empty( $config['enabled'] ) && post_type_exists( $type ) ) {
				$enabled[ $type ] = max( 1, isset( $config['days'] ) ? (int) $config['days'] : 90 );
			}
		}

		return $enabled;
	}

	/**
	 * Build term exclusions SQL fragment and placeholders.
	 *
	 * @return array Array containing SQL string and placeholder parameters.
	 */
	protected function build_term_exclusion_clause() {
		global $wpdb;

		$settings       = ocap_get_settings();
		$excluded_terms = isset( $settings['excluded_terms'] ) ? array_filter( array_map( 'absint', (array) $settings['excluded_terms'] ) ) : array();

		if ( empty( $excluded_terms ) ) {
			return array(
				'sql'    => '',
				'params' => array(),
			);
		}

		$placeholders = implode( ',', array_fill( 0, count( $excluded_terms ), '%d' ) );

		// Subquery against term_relationships and term_taxonomy.
		$sql = " AND p.ID NOT IN (
			SELECT tr.object_id
			FROM {$wpdb->term_relationships} tr
			INNER JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
			WHERE tt.term_id IN ($placeholders)
		)";

		return array(
			'sql'    => $sql,
			'params' => $excluded_terms,
		);
	}

	/**
	 * Build WHERE conditions and parameters.
	 *
	 * @return array Array with 'where' SQL string and 'params' array.
	 */
	protected function build_where() {
		global $wpdb;

		$enabled_types = $this->get_enabled_post_types();
		$basis         = ocap_get_setting( 'date_basis', 'modified' );
		$date_col      = ( 'published' === $basis ) ? 'p.post_date_gmt' : 'p.post_modified_gmt';

		$where_clauses = array();
		$params        = array();

		// Filter to a single post type if specified.
		$filter_type = ! empty( $this->args['post_type'] ) ? sanitize_key( $this->args['post_type'] ) : '';

		if ( ! empty( $filter_type ) ) {
			if ( ! isset( $enabled_types[ $filter_type ] ) ) {
				// Requested post type is not enabled; return impossible condition.
				return array(
					'where'  => '1 = 0',
					'params' => array(),
				);
			}
			$types_to_query = array( $filter_type => $enabled_types[ $filter_type ] );
		} else {
			$types_to_query = $enabled_types;
		}

		if ( empty( $types_to_query ) ) {
			return array(
				'where'  => '1 = 0',
				'params' => array(),
			);
		}

		$is_excluded_view = ( 'excluded' === $this->args['view'] );

		if ( $is_excluded_view ) {
			// In excluded view: published posts of enabled types where _ocap_excluded = '1' or _sct_excluded = '1'.
			$type_placeholders = implode( ',', array_fill( 0, count( $types_to_query ), '%s' ) );
			$where_clauses[]   = "p.post_type IN ($type_placeholders)";
			$params            = array_merge( $params, array_keys( $types_to_query ) );
			$where_clauses[]   = "ex.meta_value = '1'";
		} else {
			// Normal stale view.
			$type_conditions = array();
			$current_time    = time();

			foreach ( $types_to_query as $type => $days ) {
				$cutoff            = gmdate( 'Y-m-d H:i:s', $current_time - ( $days * DAY_IN_SECONDS ) );
				$type_conditions[] = "( p.post_type = %s AND {$date_col} < %s AND ( rv.meta_value IS NULL OR rv.meta_value < %s ) )";
				array_push( $params, $type, $cutoff, $cutoff );
			}

			$where_clauses[] = '(' . implode( ' OR ', $type_conditions ) . ')';
			$where_clauses[] = '( ex.meta_id IS NULL OR ex.meta_value != \'1\' )';

			// Excluded terms only apply to stale view.
			$term_clause = $this->build_term_exclusion_clause();
			if ( ! empty( $term_clause['sql'] ) ) {
				$where_clauses[] = ltrim( $term_clause['sql'], ' AND' );
				$params          = array_merge( $params, $term_clause['params'] );
			}
		}

		// Filter by author if requested.
		if ( ! empty( $this->args['author'] ) ) {
			$where_clauses[] = 'p.post_author = %d';
			$params[]        = absint( $this->args['author'] );
		}

		// Search by title if requested.
		if ( ! empty( $this->args['search'] ) ) {
			$where_clauses[] = 'p.post_title LIKE %s';
			$params[]        = '%' . $wpdb->esc_like( $this->args['search'] ) . '%';
		}

		$where = implode( ' AND ', $where_clauses );

		return array(
			'where'  => $where,
			'params' => $params,
		);
	}

	/**
	 * Build ORDER BY SQL fragment.
	 *
	 * @return string
	 */
	protected function build_orderby() {
		$basis    = ocap_get_setting( 'date_basis', 'modified' );
		$date_col = ( 'published' === $basis ) ? 'p.post_date_gmt' : 'p.post_modified_gmt';

		// SQL expression for effective date.
		$effective_date_sql = "GREATEST( {$date_col}, COALESCE(rv.meta_value, '1970-01-01 00:00:00') )";

		$orderby = strtolower( trim( $this->args['orderby'] ) );
		$order   = ( 'ASC' === strtoupper( $this->args['order'] ) ) ? 'ASC' : 'DESC';

		switch ( $orderby ) {
			case 'title':
				return "p.post_title {$order}, p.ID DESC";

			case 'type':
				return "p.post_type {$order}, {$effective_date_sql} ASC";

			case 'author':
				return "p.post_author {$order}, {$effective_date_sql} ASC";

			case 'modified':
			case 'date':
				return "{$date_col} {$order}";

			case 'days':
			default:
				if ( 'ASC' === $order ) {
					return "{$effective_date_sql} DESC, p.ID DESC";
				} else {
					return "{$effective_date_sql} ASC, p.ID DESC";
				}
		}
	}

	/**
	 * Execute the query and return results.
	 *
	 * @return array Array of formatted post items.
	 */
	public function get_results() {
		global $wpdb;

		$where_data = $this->build_where();
		if ( empty( $where_data['where'] ) || '1 = 0' === $where_data['where'] ) {
			$this->total_items = 0;
			return array();
		}

		$where_sql = $where_data['where'];
		$params    = $where_data['params'];

		// Get total count first.
		$this->total_items = (int) $this->get_count();

		if ( 0 === $this->total_items ) {
			return array();
		}

		$cache_key = 'ocap_results_' . md5( wp_json_encode( $this->args ) );
		$cached    = wp_cache_get( $cache_key, 'ocap_query' );
		if ( false !== $cached ) {
			return $cached;
		}

		$orderby_sql = $this->build_orderby();

		// Calculate pagination.
		$per_page = max( 1, (int) $this->args['per_page'] );
		$paged    = max( 1, (int) $this->args['paged'] );
		$offset   = ( null !== $this->args['offset'] ) ? max( 0, (int) $this->args['offset'] ) : ( $paged - 1 ) * $per_page;

		$query_params = $params;
		$limit_sql    = '';

		if ( $per_page > 0 ) {
			$limit_sql      = 'LIMIT %d OFFSET %d';
			$query_params[] = $per_page;
			$query_params[] = $offset;
		}

		$sql = "SELECT p.ID, p.post_title, p.post_type, p.post_author,
					   p.post_date_gmt, p.post_modified_gmt, rv.meta_value AS last_reviewed
				FROM {$wpdb->posts} p
				LEFT JOIN {$wpdb->postmeta} rv ON rv.post_id = p.ID AND (rv.meta_key = '_ocap_last_reviewed' OR rv.meta_key = '_sct_last_reviewed')
				LEFT JOIN {$wpdb->postmeta} ex ON ex.post_id = p.ID AND (ex.meta_key = '_ocap_excluded' OR ex.meta_key = '_sct_excluded')
				WHERE p.post_status = 'publish'
				  AND ({$where_sql})
				ORDER BY {$orderby_sql}
				{$limit_sql}";

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Dynamic query constructed with placeholders.
		$prepared_sql = $wpdb->prepare( $sql, $query_params );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Query executed with prepared parameters and cached.
		$rows = $wpdb->get_results( $prepared_sql );

		if ( empty( $rows ) ) {
			return array();
		}

		$formatted = array();
		$basis     = ocap_get_setting( 'date_basis', 'modified' );

		foreach ( $rows as $row ) {
			$limit             = ocap_get_post_type_day_limit( $row->post_type );
			$effective_date    = ocap_get_effective_date( $row->ID, $basis );
			$days_since_update = ocap_calculate_days_since_update( $row->ID, $basis );
			$severity          = ( $days_since_update > ( 2 * $limit ) ) ? 'high' : 'medium';
			$post_type_obj     = get_post_type_object( $row->post_type );
			$type_label        = $post_type_obj ? $post_type_obj->labels->singular_name : ucfirst( $row->post_type );

			$edit_url    = get_edit_post_link( $row->ID, 'raw' );
			$view_url    = get_permalink( $row->ID );
			$author_name = get_the_author_meta( 'display_name', $row->post_author );

			$formatted[] = array(
				'ID'                => (int) $row->ID,
				'title'             => $row->post_title ? $row->post_title : __( '(no title)', 'contentcortex' ),
				'post_type'         => $row->post_type,
				'post_type_label'   => $type_label,
				'post_author'       => (int) $row->post_author,
				'author_name'       => $author_name ? (string) $author_name : __( 'Unknown', 'contentcortex' ),
				'post_date_gmt'     => $row->post_date_gmt,
				'post_modified_gmt' => $row->post_modified_gmt,
				'last_reviewed'     => $row->last_reviewed,
				'effective_date'    => $effective_date,
				'days_since_update' => $days_since_update,
				'day_limit'         => $limit,
				'severity'          => $severity,
				'edit_url'          => $edit_url ? (string) $edit_url : ( $view_url ? (string) $view_url : '' ),
				'view_url'          => $view_url ? (string) $view_url : '',
			);
		}

		$result = apply_filters( 'ocap_stale_results', $formatted, $this->args );
		wp_cache_set( $cache_key, $result, 'ocap_query', 300 );
		return $result;
	}

	/**
	 * Get total count matching current query.
	 *
	 * @return int Total matching items.
	 */
	public function get_count() {
		global $wpdb;

		$where_data = $this->build_where();
		if ( empty( $where_data['where'] ) || '1 = 0' === $where_data['where'] ) {
			return 0;
		}

		$where_sql = $where_data['where'];
		$params    = $where_data['params'];

		$cache_key = 'ocap_count_' . md5( wp_json_encode( $this->args ) );
		$cached    = wp_cache_get( $cache_key, 'ocap_query' );
		if ( false !== $cached ) {
			return (int) $cached;
		}

		$count_sql = "SELECT COUNT(*)
			FROM {$wpdb->posts} p
			LEFT JOIN {$wpdb->postmeta} rv ON rv.post_id = p.ID AND (rv.meta_key = '_ocap_last_reviewed' OR rv.meta_key = '_sct_last_reviewed')
			LEFT JOIN {$wpdb->postmeta} ex ON ex.post_id = p.ID AND (ex.meta_key = '_ocap_excluded' OR ex.meta_key = '_sct_excluded')
			WHERE p.post_status = 'publish' AND ({$where_sql})";

		if ( ! empty( $params ) ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Dynamic query string with placeholder tokens.
			$prepared_count = $wpdb->prepare( $count_sql, $params );
		} else {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Static count query without parameters.
			$prepared_count = $count_sql;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Query executed with prepared parameters and cached.
		$count = (int) $wpdb->get_var( $prepared_count );

		wp_cache_set( $cache_key, $count, 'ocap_query', 300 );

		return $count;
	}

	/**
	 * Get total items found after get_results() has run.
	 *
	 * @return int
	 */
	public function get_total_items() {
		return $this->total_items;
	}

	/**
	 * Get stale count broken down by post type.
	 *
	 * @return array Array of post_type => count.
	 */
	public function get_counts_by_type() {
		global $wpdb;

		// Force stale view and no type filter for summary breakdown.
		$args_copy              = $this->args;
		$args_copy['view']      = 'stale';
		$args_copy['post_type'] = '';

		$temp_query = new OCAP_Query( $args_copy );
		$where_data = $temp_query->build_where();

		if ( empty( $where_data['where'] ) || '1 = 0' === $where_data['where'] ) {
			return array();
		}

		$where_sql = $where_data['where'];
		$params    = $where_data['params'];

		$cache_key = 'ocap_type_counts_' . md5( wp_json_encode( $this->args ) );
		$cached    = wp_cache_get( $cache_key, 'ocap_query' );
		if ( false !== $cached ) {
			return $cached;
		}

		$sql = "SELECT p.post_type, COUNT(*) as count
			FROM {$wpdb->posts} p
			LEFT JOIN {$wpdb->postmeta} rv ON rv.post_id = p.ID AND (rv.meta_key = '_ocap_last_reviewed' OR rv.meta_key = '_sct_last_reviewed')
			LEFT JOIN {$wpdb->postmeta} ex ON ex.post_id = p.ID AND (ex.meta_key = '_ocap_excluded' OR ex.meta_key = '_sct_excluded')
			WHERE p.post_status = 'publish' AND ({$where_sql})
			GROUP BY p.post_type";

		if ( ! empty( $params ) ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Dynamic query string with placeholder tokens.
			$prepared = $wpdb->prepare( $sql, $params );
		} else {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Static query without parameters.
			$prepared = $sql;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Query executed with prepared parameters and cached.
		$rows   = $wpdb->get_results( $prepared );
		$counts = array();

		if ( ! empty( $rows ) ) {
			foreach ( $rows as $row ) {
				$counts[ $row->post_type ] = (int) $row->count;
			}
		}

		wp_cache_set( $cache_key, $counts, 'ocap_query', 300 );

		return $counts;
	}

	/**
	 * Get count of excluded items.
	 *
	 * @return int
	 */
	public function get_excluded_count() {
		$args_copy         = $this->args;
		$args_copy['view'] = 'excluded';
		$query             = new OCAP_Query( $args_copy );
		return $query->get_count();
	}
}