<?php
/**
 * Reports and the review queue.
 *
 * @package SpaceBay
 */

namespace SpaceBay\Moderation;

use SpaceBay\Database\Tables;
use SpaceBay\Support\Cache;
use SpaceBay\Support\Time;

defined( 'ABSPATH' ) || exit;

/**
 * Stores member reports and automatic holds, one row per reason.
 *
 * Automatic holds (blocked words, review of new posts, too many reports)
 * are stored as reports without a reporter, so moderators see every item
 * that needs a decision in one queue.
 */
final class Reports {

	/**
	 * Reasons members can choose.
	 *
	 * @var string[]
	 */
	const REASONS = array( 'spam', 'abuse', 'inappropriate', 'other' );

	/**
	 * Reasons used for automatic holds.
	 *
	 * @var string[]
	 */
	const SYSTEM_REASONS = array( 'words', 'first_post', 'all_posts', 'edited', 'reports' );

	/**
	 * Things that can be reported.
	 *
	 * @var string[]
	 */
	const TYPES = array( 'post', 'comment', 'member', 'message' );

	/**
	 * Adds a member report. A second report of the same thing by the same
	 * member is ignored.
	 *
	 * @param string $object_type Object type.
	 * @param int    $object_id   Object ID.
	 * @param int    $reporter_id Reporting member.
	 * @param string $reason      One of REASONS.
	 * @param string $note        Optional note.
	 * @return array{id: int, duplicate: bool}
	 */
	public static function add( $object_type, $object_id, $reporter_id, $reason, $note = '' ) {
		$existing = self::open_id( $object_type, $object_id, $reporter_id, '' );

		if ( $existing ) {
			return array(
				'id'        => $existing,
				'duplicate' => true,
			);
		}

		return array(
			'id'        => self::insert( $object_type, $object_id, $reporter_id, in_array( $reason, self::REASONS, true ) ? $reason : 'other', $note ),
			'duplicate' => false,
		);
	}

	/**
	 * Adds an automatic hold, once per reason.
	 *
	 * @param string $object_type Object type.
	 * @param int    $object_id   Object ID.
	 * @param string $reason      One of SYSTEM_REASONS.
	 * @param string $note        Optional detail, such as the matched words.
	 * @return int Report ID.
	 */
	public static function hold( $object_type, $object_id, $reason, $note = '' ) {
		$existing = self::open_id( $object_type, $object_id, 0, $reason );

		return $existing ? $existing : self::insert( $object_type, $object_id, 0, $reason, $note );
	}

	/**
	 * Number of open member reports on one object.
	 *
	 * @param string $object_type Object type.
	 * @param int    $object_id   Object ID.
	 * @return int
	 */
	public static function open_member_reports( $object_type, $object_id ) {
		global $wpdb;

		return (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Fresh count right after a write.
			$wpdb->prepare(
				'SELECT COUNT(id) FROM %i WHERE object_type = %s AND object_id = %d AND status = %s AND reporter_id > 0',
				Tables::name( 'reports' ),
				$object_type,
				absint( $object_id ),
				'open'
			)
		);
	}

	/**
	 * Closes every open report of an object.
	 *
	 * @param string $object_type Object type.
	 * @param int    $object_id   Object ID.
	 * @param string $status      "approved", "dismissed" or "removed".
	 * @param int    $handled_by  Moderator ID, 0 for automatic.
	 * @return void
	 */
	public static function resolve( $object_type, $object_id, $status, $handled_by ) {
		global $wpdb;

		$wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table.
			Tables::name( 'reports' ),
			array(
				'status'     => $status,
				'handled_by' => absint( $handled_by ),
				'handled_at' => Time::now(),
			),
			array(
				'object_type' => $object_type,
				'object_id'   => absint( $object_id ),
				'status'      => 'open',
			)
		);

		Cache::flush( 'reports' );
		delete_transient( 'spacebay_admin_stats' );
	}

	/**
	 * Number of things waiting for a moderator.
	 *
	 * Counts distinct (type, id) pairs through a subquery: COUNT(DISTINCT a, b)
	 * only works on MySQL and fails on SQLite (WordPress Playground).
	 *
	 * @return int
	 */
	public static function open_count() {
		return (int) Cache::remember(
			'reports',
			'open_count',
			static function () {
				global $wpdb;

				return (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Cached by Cache::remember().
					$wpdb->prepare( 'SELECT COUNT(*) FROM ( SELECT DISTINCT object_type, object_id FROM %i WHERE status = %s ) AS sbay_open', Tables::name( 'reports' ), 'open' )
				);
			}
		);
	}

	/**
	 * Lists reported or held objects, oldest first.
	 *
	 * @param string $type     "all" or one of TYPES.
	 * @param int    $page     Page, from 1.
	 * @param int    $per_page Items per page.
	 * @return array{items: array[], total: int} Items: object_type, object_id, first_at, reports.
	 */
	public static function queue( $type = 'all', $page = 1, $per_page = 20 ) {
		global $wpdb;

		$table    = Tables::name( 'reports' );
		$per_page = max( 1, min( 50, absint( $per_page ) ) );
		$offset   = ( max( 1, absint( $page ) ) - 1 ) * $per_page;
		$types    = in_array( $type, self::TYPES, true ) ? array( $type ) : self::TYPES;
		$list     = implode( ',', array_fill( 0, count( $types ), '%s' ) );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- Moderator queue; the IN() list has one %s per type.
		$total  = (int) $wpdb->get_var(
			$wpdb->prepare( "SELECT COUNT(*) FROM ( SELECT DISTINCT object_type, object_id FROM %i WHERE status = %s AND object_type IN ($list) ) AS sbay_open", array_merge( array( $table, 'open' ), $types ) )
		);
		$groups = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT object_type, object_id, MIN(created_at) AS first_at FROM %i WHERE status = %s AND object_type IN ($list) GROUP BY object_type, object_id ORDER BY first_at ASC LIMIT %d OFFSET %d",
				array_merge( array( $table, 'open' ), $types, array( $per_page, $offset ) )
			),
			ARRAY_A
		);
		// phpcs:enable

		$items = array();

		foreach ( (array) $groups as $group ) {
			$items[] = array(
				'object_type' => $group['object_type'],
				'object_id'   => (int) $group['object_id'],
				'first_at'    => $group['first_at'],
				'reports'     => self::open_for( $group['object_type'], (int) $group['object_id'] ),
			);
		}

		return array(
			'items' => $items,
			'total' => $total,
		);
	}

	/**
	 * Open report rows of one object, newest first.
	 *
	 * @param string $object_type Object type.
	 * @param int    $object_id   Object ID.
	 * @return array[]
	 */
	public static function open_for( $object_type, $object_id ) {
		global $wpdb;

		$rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Moderator queue.
			$wpdb->prepare(
				'SELECT id, reporter_id, reason, note, created_at FROM %i WHERE object_type = %s AND object_id = %d AND status = %s ORDER BY created_at DESC, id DESC LIMIT 50',
				Tables::name( 'reports' ),
				$object_type,
				absint( $object_id ),
				'open'
			),
			ARRAY_A
		);

		return array_map(
			static function ( $row ) {
				return array(
					'id'          => (int) $row['id'],
					'reporter_id' => (int) $row['reporter_id'],
					'reason'      => $row['reason'],
					'note'        => (string) $row['note'],
					'created_at'  => $row['created_at'],
				);
			},
			(array) $rows
		);
	}

	/**
	 * Removes reports written by a deleted user and reports about them.
	 *
	 * @param int $user_id User ID.
	 * @return void
	 */
	public static function delete_for_user( $user_id ) {
		global $wpdb;

		$table = Tables::name( 'reports' );
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table cleanup.
		$wpdb->delete( $table, array( 'reporter_id' => absint( $user_id ) ) );
		$wpdb->delete(
			$table,
			array(
				'object_type' => 'member',
				'object_id'   => absint( $user_id ),
			)
		);
		// phpcs:enable
		Cache::flush( 'reports' );
	}

	/**
	 * ID of an open report by this reporter (and reason, for holds).
	 *
	 * @param string $object_type Object type.
	 * @param int    $object_id   Object ID.
	 * @param int    $reporter_id Reporter, 0 for holds.
	 * @param string $reason      Reason to match, or "" for any.
	 * @return int
	 */
	private static function open_id( $object_type, $object_id, $reporter_id, $reason ) {
		global $wpdb;

		$table = Tables::name( 'reports' );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Fresh check before writing.
		if ( '' === $reason ) {
			$id = $wpdb->get_var(
				$wpdb->prepare( 'SELECT id FROM %i WHERE object_type = %s AND object_id = %d AND reporter_id = %d AND status = %s', $table, $object_type, absint( $object_id ), absint( $reporter_id ), 'open' )
			);
		} else {
			$id = $wpdb->get_var(
				$wpdb->prepare( 'SELECT id FROM %i WHERE object_type = %s AND object_id = %d AND reporter_id = %d AND reason = %s AND status = %s', $table, $object_type, absint( $object_id ), absint( $reporter_id ), $reason, 'open' )
			);
		}
		// phpcs:enable

		return (int) $id;
	}

	/**
	 * Inserts a report row.
	 *
	 * @param string $object_type Object type.
	 * @param int    $object_id   Object ID.
	 * @param int    $reporter_id Reporter, 0 for holds.
	 * @param string $reason      Reason.
	 * @param string $note        Note.
	 * @return int Report ID.
	 */
	private static function insert( $object_type, $object_id, $reporter_id, $reason, $note ) {
		global $wpdb;

		$wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table.
			Tables::name( 'reports' ),
			array(
				'object_type' => in_array( $object_type, self::TYPES, true ) ? $object_type : 'post',
				'object_id'   => absint( $object_id ),
				'reporter_id' => absint( $reporter_id ),
				'reason'      => sanitize_key( $reason ),
				'note'        => mb_substr( sanitize_textarea_field( (string) $note ), 0, 500 ),
				'status'      => 'open',
				'created_at'  => Time::now(),
			)
		);

		Cache::flush( 'reports' );
		delete_transient( 'spacebay_admin_stats' );

		return (int) $wpdb->insert_id;
	}
}