<?php
/**
 * Admin list queries: filtered, searchable, paginated links with their locations.
 *
 * @package Unbreak
 */

namespace Unbreak\Data;

use Unbreak\Check\StatusPolicy;
use Unbreak\Install\Schema;
use Unbreak\Queue\FullScan;

defined( 'ABSPATH' ) || exit;

/**
 * Read model for the Links screen.
 */
final class LinkQuery {

	public const PER_PAGE = 50;

	/**
	 * Status filters accepted by the API. "problems" = every confirmed failure plus failures that
	 * are still being re-checked (see problem_view_statuses()).
	 */
	public const STATUS_FILTERS = array( 'problems', 'broken', 'redirect', 'unverifiable', 'server_error', 'timeout', 'ssl_error', 'suspicious', 'unconfirmed', 'pending', 'ok', 'dismissed', 'all' );

	/**
	 * Max instances returned per link row.
	 */
	private const INSTANCES_PER_LINK = 25;

	/**
	 * Statuses listed under "All problems": confirmed failures and failures waiting for
	 * confirmation ("Re-checking (not confirmed yet)"), so a failing link is never hidden there.
	 *
	 * @return string[]
	 */
	public static function problem_view_statuses(): array {
		return array_merge( StatusPolicy::problem_statuses(), array( StatusPolicy::UNCONFIRMED ) );
	}

	/**
	 * Run a list query.
	 *
	 * @param array<string,mixed> $args status, scope (internal|external), source_type, post_type, search, page, orderby, order.
	 * @return array{items:array<int,array<string,mixed>>,total:int,page:int,per_page:int,pages:int}
	 */
	public function query( array $args ): array {
		global $wpdb;
		$links     = Schema::table( 'links' );
		$instances = Schema::table( 'instances' );

		$where  = array();
		$params = array();

		$status = in_array( $args['status'] ?? 'problems', self::STATUS_FILTERS, true ) ? (string) $args['status'] : 'problems';
		if ( 'dismissed' === $status ) {
			$where[] = 'l.dismissed = 1';
		} else {
			$where[] = 'l.dismissed = 0';
			if ( 'problems' === $status ) {
				$where[] = 'l.status IN (' . implode( ',', array_fill( 0, count( self::problem_view_statuses() ), '%s' ) ) . ')';
				$params  = array_merge( $params, self::problem_view_statuses() );
			} elseif ( 'all' !== $status ) {
				$where[]  = 'l.status = %s';
				$params[] = $status;
			}
		}

		$scope = (string) ( $args['scope'] ?? '' );
		if ( 'internal' === $scope || 'external' === $scope ) {
			$where[]  = 'l.is_internal = %d';
			$params[] = 'internal' === $scope ? 1 : 0;
		}

		$source_type = sanitize_key( (string) ( $args['source_type'] ?? '' ) );
		$post_type   = sanitize_key( (string) ( $args['post_type'] ?? '' ) );
		if ( '' !== $source_type || '' !== $post_type ) {
			$sub       = 'SELECT 1 FROM %i i';
			$params[]  = $instances;
			$sub_where = array( 'i.link_id = l.id' );
			if ( '' !== $post_type ) {
				$sub        .= ' INNER JOIN %i p ON p.ID = i.source_id';
				$params[]    = $wpdb->posts;
				$sub_where[] = "i.source_type IN ('post_content','elementor')";
				$sub_where[] = 'p.post_type = %s';
				$params[]    = $post_type;
			}
			if ( '' !== $source_type ) {
				$sub_where[] = 'i.source_type = %s';
				$params[]    = $source_type;
			}
			$where[] = 'EXISTS (' . $sub . ' WHERE ' . implode( ' AND ', $sub_where ) . ')';
		}

		$search = trim( (string) ( $args['search'] ?? '' ) );
		if ( '' !== $search ) {
			$like     = '%' . $wpdb->esc_like( $search ) . '%';
			$where[]  = '(l.url LIKE %s OR EXISTS (SELECT 1 FROM %i si WHERE si.link_id = l.id AND si.anchor_text LIKE %s))';
			$params[] = $like;
			$params[] = $instances;
			$params[] = $like;
		}

		$orderable = array(
			'url'             => 'l.url',
			'status'          => 'l.status',
			'last_checked_at' => 'l.last_checked_at',
			'fail_count'      => 'l.fail_count',
			'http_code'       => 'l.http_code',
		);
		$orderby   = $orderable[ (string) ( $args['orderby'] ?? '' ) ] ?? 'l.status_changed_at';
		$order     = 'asc' === strtolower( (string) ( $args['order'] ?? 'desc' ) ) ? 'ASC' : 'DESC';

		$page   = max( 1, (int) ( $args['page'] ?? 1 ) );
		$offset = ( $page - 1 ) * self::PER_PAGE;
		$sql    = implode( ' AND ', $where );

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare,WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber,PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql is assembled from fixed fragments holding placeholders only; $orderby/$order come from whitelists.
		$total = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i l WHERE {$sql}", array_merge( array( $links ), $params ) ) );
		$rows  = (array) $wpdb->get_results(
			$wpdb->prepare(
				"SELECT l.* FROM %i l WHERE {$sql} ORDER BY {$orderby} {$order}, l.id DESC LIMIT %d OFFSET %d",
				array_merge( array( $links ), $params, array( self::PER_PAGE, $offset ) )
			)
		);
		// phpcs:enable

		$items = array();
		if ( ! empty( $rows ) ) {
			$locations = $this->locations( array_map( 'intval', wp_list_pluck( $rows, 'id' ) ) );
			foreach ( $rows as $row ) {
				$items[] = self::shape( $row, $locations[ (int) $row->id ] ?? array() );
			}
		}

		return array(
			'items'    => $items,
			'total'    => $total,
			'page'     => $page,
			'per_page' => self::PER_PAGE,
			'pages'    => (int) ceil( $total / self::PER_PAGE ),
		);
	}

	/**
	 * One link with its locations.
	 *
	 * @param int $id Link ID.
	 * @return array<string,mixed>|null
	 */
	public function get( int $id ): ?array {
		$row = ( new LinkRepository() )->find( $id );
		if ( null === $row ) {
			return null;
		}
		$locations = $this->locations( array( $id ) );
		return self::shape( $row, $locations[ $id ] ?? array() );
	}

	/**
	 * Where each link appears, with titles and edit links.
	 *
	 * @param int[] $ids Link IDs.
	 * @return array<int,array<int,array<string,mixed>>>
	 */
	public function locations( array $ids ): array {
		global $wpdb;
		if ( empty( $ids ) ) {
			return array();
		}
		$table = Schema::table( 'instances' );
		$in    = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare,WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Placeholders generated above.
		$rows = (array) $wpdb->get_results( $wpdb->prepare( "SELECT * FROM %i WHERE link_id IN ({$in}) ORDER BY id ASC", array_merge( array( $table ), $ids ) ) );

		$post_ids = array();
		foreach ( $rows as $row ) {
			if ( in_array( $row->source_type, array( 'post_content', 'elementor', 'menu' ), true ) ) {
				$post_ids[] = (int) $row->source_id;
			}
		}
		if ( ! empty( $post_ids ) ) {
			_prime_post_caches( array_unique( $post_ids ), false, false );
		}

		$out = array();
		foreach ( $rows as $row ) {
			$link_id = (int) $row->link_id;
			if ( isset( $out[ $link_id ] ) && count( $out[ $link_id ] ) >= self::INSTANCES_PER_LINK ) {
				continue;
			}
			$out[ $link_id ][] = array_merge(
				array(
					'source_type' => (string) $row->source_type,
					'source_id'   => (int) $row->source_id,
					'source_key'  => (string) $row->source_key,
					'anchor_text' => (string) $row->anchor_text,
					'element'     => (string) $row->element,
					'context'     => (string) $row->context,
				),
				self::describe_source( (string) $row->source_type, (int) $row->source_id, (string) $row->source_key )
			);
		}
		return $out;
	}

	/**
	 * Human-readable title, edit link and post type for a source.
	 *
	 * @param string $type Source type.
	 * @param int    $id   Source ID.
	 * @param string $key  Source key.
	 * @return array{title:string,edit_url:string,view_url:string,post_type:string}
	 */
	public static function describe_source( string $type, int $id, string $key ): array {
		$title     = '';
		$edit_url  = '';
		$view_url  = '';
		$post_type = '';
		switch ( $type ) {
			case 'post_content':
			case 'elementor':
				$post = get_post( $id );
				if ( $post instanceof \WP_Post ) {
					$title     = '' !== $post->post_title ? $post->post_title : __( '(no title)', 'unbreak' );
					$edit_url  = (string) get_edit_post_link( $id, 'raw' );
					$view_url  = (string) get_permalink( $post );
					$post_type = $post->post_type;
					if ( 'elementor' === $type ) {
						$edit_url = add_query_arg(
							array(
								'post'   => $id,
								'action' => 'elementor',
							),
							admin_url( 'post.php' )
						);
					}
				}
				break;
			case 'menu':
				$menus = wp_get_post_terms( $id, 'nav_menu' );
				$menu  = is_array( $menus ) && ! empty( $menus ) ? reset( $menus ) : null;
				$item  = get_post( $id );
				$name  = $menu instanceof \WP_Term ? $menu->name : __( 'Menu', 'unbreak' );
				/* translators: 1: menu name, 2: menu item title. */
				$title    = sprintf( __( '%1$s → %2$s', 'unbreak' ), $name, $item instanceof \WP_Post ? $item->post_title : '#' . $id );
				$edit_url = $menu instanceof \WP_Term ? admin_url( 'nav-menus.php?action=edit&menu=' . $menu->term_id ) : admin_url( 'nav-menus.php' );
				break;
			case 'widget':
				$labels   = array(
					'widget_text'        => __( 'Text widget', 'unbreak' ),
					'widget_custom_html' => __( 'Custom HTML widget', 'unbreak' ),
					'widget_block'       => __( 'Block widget', 'unbreak' ),
				);
				$title    = ( $labels[ $key ] ?? __( 'Widget', 'unbreak' ) ) . ' #' . $id;
				$edit_url = admin_url( wp_is_block_theme() ? 'site-editor.php' : 'widgets.php' );
				break;
			case 'comment':
				$comment = get_comment( $id );
				if ( $comment instanceof \WP_Comment ) {
					/* translators: 1: comment author, 2: post title. */
					$title    = sprintf( __( 'Comment by %1$s on “%2$s”', 'unbreak' ), $comment->comment_author, get_the_title( (int) $comment->comment_post_ID ) );
					$edit_url = admin_url( 'comment.php?action=editcomment&c=' . $id );
					$view_url = (string) get_comment_link( $comment );
				}
				break;
		}
		return array(
			'title'     => $title,
			'edit_url'  => $edit_url,
			'view_url'  => $view_url,
			'post_type' => $post_type,
		);
	}

	/**
	 * API shape of a link row.
	 *
	 * @param object                         $row       DB row.
	 * @param array<int,array<string,mixed>> $locations Locations.
	 * @return array<string,mixed>
	 */
	public static function shape( object $row, array $locations ): array {
		return array(
			'id'               => (int) $row->id,
			'url'              => (string) $row->url,
			'host'             => (string) $row->host,
			'is_internal'      => (bool) (int) $row->is_internal,
			'status'           => (string) $row->status,
			'http_code'        => null !== $row->http_code ? (int) $row->http_code : null,
			'final_url'        => $row->final_url,
			'redirect_count'   => (int) $row->redirect_count,
			'error_message'    => $row->error_message,
			'response_ms'      => null !== $row->response_ms ? (int) $row->response_ms : null,
			'first_failure_at' => $row->first_failure_at,
			'last_checked_at'  => $row->last_checked_at,
			'next_check_at'    => $row->next_check_at,
			'fail_count'       => (int) $row->fail_count,
			'dismissed'        => (bool) (int) $row->dismissed,
			'locations'        => $locations,
		);
	}

	/**
	 * Dashboard counts and progress.
	 *
	 * @return array<string,mixed>
	 */
	public function summary(): array {
		global $wpdb;
		$links = Schema::table( 'links' );
		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin table.
		$rows      = (array) $wpdb->get_results( $wpdb->prepare( 'SELECT status, COUNT(*) AS n FROM %i WHERE dismissed = 0 GROUP BY status', $links ) );
		$dismissed = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE dismissed = 1', $links ) );
		// phpcs:enable

		$counts = array_fill_keys( array( 'broken', 'redirect', 'unverifiable', 'server_error', 'timeout', 'ssl_error', 'suspicious', 'unconfirmed', 'pending', 'ok' ), 0 );
		$total  = 0;
		foreach ( $rows as $row ) {
			$counts[ (string) $row->status ] = (int) $row->n;
			$total                          += (int) $row->n;
		}
		$counts['dismissed'] = $dismissed;
		// Confirmed failures only; links still being re-checked are counted under "unconfirmed".
		$counts['problems'] = $counts['broken'] + $counts['server_error'] + $counts['timeout'] + $counts['ssl_error'];

		$scan    = FullScan::state();
		$checked = $total - $counts['pending'];
		return array(
			'counts'   => $counts,
			'total'    => $total,
			'scan'     => $scan,
			'progress' => array(
				'phase'      => 'extracting' === $scan['status'] ? 'extracting' : ( $counts['pending'] > 0 ? 'checking' : 'idle' ),
				'extracted'  => $scan['processed'],
				'to_extract' => $scan['total'],
				'checked'    => $checked,
				'pending'    => $counts['pending'],
				'to_check'   => $total,
				'percent'    => self::percent( $scan, $checked, $total ),
			),
		);
	}

	/**
	 * Overall progress: extraction is the first half, first checks of every link the second half.
	 *
	 * @param array{status:string,total:int,processed:int} $scan    Scan state.
	 * @param int                                          $checked Links checked at least once.
	 * @param int                                          $total   Total links.
	 */
	private static function percent( array $scan, int $checked, int $total ): int {
		$extract = 'extracting' === $scan['status'] ? ( $scan['total'] > 0 ? $scan['processed'] / $scan['total'] : 0.0 ) : 1.0;
		$check   = $total > 0 ? $checked / $total : ( 'extracting' === $scan['status'] ? 0.0 : 1.0 );
		return (int) floor( ( $extract * 50 ) + ( 'extracting' === $scan['status'] ? 0 : $check * 50 ) );
	}
}