<?php
/**
 * Data access for the lgbf_links table.
 *
 * @package 404Zen
 */

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

class LGBF_Links_Store {

	/**
	 * @return string Fully-prefixed links table name.
	 */
	public static function table() {
		global $wpdb;

		return $wpdb->prefix . 'lgbf_links';
	}

	/**
	 * MD5 hash used to group identical URLs.
	 *
	 * @param string $url URL.
	 * @return string
	 */
	public static function hash( $url ) {
		return md5( $url );
	}

	/**
	 * Synchronizes the stored link set for a post with a freshly extracted one.
	 *
	 * Existing rows for URLs still present keep their status (including
	 * 'dismissed'); rows for URLs no longer in the content are removed; new
	 * URLs are inserted as 'pending'.
	 *
	 * @param int     $post_id Post ID.
	 * @param array[] $links   Items from LGBF_Link_Extractor::extract().
	 */
	public static function sync_post_links( $post_id, $links ) {
		global $wpdb;

		$table    = self::table();
		$post_id  = (int) $post_id;
		$existing = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT id, url_hash, link_type, anchor_text FROM {$table} WHERE post_id = %d", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$post_id
			),
			ARRAY_A
		);

		$existing_by_key = array();
		foreach ( (array) $existing as $row ) {
			$existing_by_key[ $row['link_type'] . '|' . $row['url_hash'] ] = $row;
		}

		$seen_keys = array();
		foreach ( $links as $link ) {
			$hash = self::hash( $link['url'] );
			$key  = $link['type'] . '|' . $hash;

			$seen_keys[ $key ] = true;

			if ( isset( $existing_by_key[ $key ] ) ) {
				if ( $existing_by_key[ $key ]['anchor_text'] !== $link['anchor'] ) {
					$wpdb->update(
						$table,
						array( 'anchor_text' => $link['anchor'] ),
						array( 'id' => (int) $existing_by_key[ $key ]['id'] ),
						array( '%s' ),
						array( '%d' )
					);
				}
				continue;
			}

			$wpdb->insert(
				$table,
				array(
					'post_id'     => $post_id,
					'url'         => $link['url'],
					'url_hash'    => $hash,
					'anchor_text' => $link['anchor'],
					'link_type'   => $link['type'],
					'status'      => 'pending',
					'created_at'  => current_time( 'mysql', true ),
				),
				array( '%d', '%s', '%s', '%s', '%s', '%s', '%s' )
			);
		}

		// Remove rows whose URL disappeared from the content.
		foreach ( $existing_by_key as $key => $row ) {
			if ( ! isset( $seen_keys[ $key ] ) ) {
				$wpdb->delete( $table, array( 'id' => (int) $row['id'] ), array( '%d' ) );
			}
		}
	}

	/**
	 * Removes all rows for a post (post deleted / excluded).
	 *
	 * @param int $post_id Post ID.
	 */
	public static function delete_post_links( $post_id ) {
		global $wpdb;

		$wpdb->delete( self::table(), array( 'post_id' => (int) $post_id ), array( '%d' ) );
	}

	/**
	 * Marks every non-dismissed row pending so a new cycle rechecks all URLs.
	 */
	public static function reset_for_new_cycle() {
		global $wpdb;

		$table = self::table();
		$wpdb->query( "UPDATE {$table} SET status = 'pending' WHERE status != 'dismissed'" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	}

	/**
	 * Returns distinct pending URLs to check, oldest first.
	 *
	 * @param int $limit Max URLs.
	 * @return array[] Rows with url and url_hash.
	 */
	public static function get_pending_urls( $limit ) {
		global $wpdb;

		$table = self::table();

		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT url, url_hash FROM {$table} WHERE status = 'pending' GROUP BY url_hash, url ORDER BY MIN(id) ASC LIMIT %d", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				max( 1, (int) $limit )
			),
			ARRAY_A
		);
	}

	/**
	 * Applies a check result to every row sharing the URL hash.
	 *
	 * @param string $url_hash URL hash.
	 * @param array  $result   Result from LGBF_Checker::check().
	 */
	public static function apply_check_result( $url_hash, $result ) {
		global $wpdb;

		$table     = self::table();
		$is_broken = in_array( $result['status'], array( 'broken', 'timeout' ), true );
		$now       = current_time( 'mysql', true );

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		if ( $is_broken ) {
			$wpdb->query(
				$wpdb->prepare(
					"UPDATE {$table}
					 SET status = %s, http_status = %d, redirect_count = %d, final_url = %s,
					     last_checked = %s, check_count = check_count + 1,
					     first_detected = COALESCE(first_detected, %s)
					 WHERE url_hash = %s AND status != 'dismissed'",
					$result['status'],
					(int) $result['http_status'],
					(int) $result['redirect_count'],
					(string) $result['final_url'],
					$now,
					$now,
					$url_hash
				)
			);
		} else {
			$wpdb->query(
				$wpdb->prepare(
					"UPDATE {$table}
					 SET status = %s, http_status = %d, redirect_count = %d, final_url = %s,
					     last_checked = %s, check_count = check_count + 1, first_detected = NULL
					 WHERE url_hash = %s AND status != 'dismissed'",
					$result['status'],
					(int) $result['http_status'],
					(int) $result['redirect_count'],
					(string) $result['final_url'],
					$now,
					$url_hash
				)
			);
		}
		// phpcs:enable
	}

	/**
	 * Dashboard counts by status.
	 *
	 * @return array
	 */
	public static function get_counts() {
		global $wpdb;

		$table = self::table();
		$rows  = $wpdb->get_results( "SELECT status, COUNT(*) AS total FROM {$table} GROUP BY status", ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		$counts = array(
			'pending'   => 0,
			'ok'        => 0,
			'broken'    => 0,
			'timeout'   => 0,
			'redirect'  => 0,
			'dismissed' => 0,
		);
		foreach ( (array) $rows as $row ) {
			$counts[ $row['status'] ] = (int) $row['total'];
		}
		$counts['problems'] = $counts['broken'] + $counts['timeout'] + $counts['redirect'];

		return $counts;
	}

	/**
	 * Issue counts broken down by status and link type (for the issues card).
	 *
	 * @return array e.g. array( 'broken' => array( 'link' => 3, 'image' => 1 ) ).
	 */
	public static function get_issue_breakdown() {
		global $wpdb;

		$table = self::table();
		$rows  = $wpdb->get_results( "SELECT status, link_type, COUNT(*) AS total FROM {$table} GROUP BY status, link_type", ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		$breakdown = array();
		foreach ( (array) $rows as $row ) {
			$breakdown[ $row['status'] ][ $row['link_type'] ] = (int) $row['total'];
		}

		return $breakdown;
	}

	/**
	 * Broken links first detected after a timestamp (for the email digest).
	 *
	 * @param string $since_gmt MySQL datetime (GMT).
	 * @param int    $limit     Max rows.
	 * @return array[]
	 */
	public static function get_new_broken_since( $since_gmt, $limit = 100 ) {
		global $wpdb;

		$table = self::table();

		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT id, post_id, url, anchor_text, link_type, status, http_status, first_detected
				 FROM {$table}
				 WHERE status IN ('broken', 'timeout') AND first_detected >= %s
				 ORDER BY first_detected DESC LIMIT %d", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$since_gmt,
				max( 1, (int) $limit )
			),
			ARRAY_A
		);
	}

	/**
	 * Loads a single row.
	 *
	 * @param int $id Row ID.
	 * @return array|null
	 */
	public static function get( $id ) {
		global $wpdb;

		$table = self::table();
		$row   = $wpdb->get_row(
			$wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", (int) $id ), // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			ARRAY_A
		);

		return $row ? $row : null;
	}
}