<?php
/** Atomic ordering, bounded reads and undo. @package ThreeTaskPostOrder */
namespace ThreeTask\PostOrder;

use RuntimeException;

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

final class Order {
	/** SQL fragments contain only prepared values and fixed aliases. */
	/**
	 * @param array<string,mixed> $c Validated values.
	 * @return array<string,mixed>
	 */
	public static function source( array $c ): array {
		global $wpdb;

		if ( 'term' === $c['kind'] ) {
			return array(
				'from'  => $wpdb->prepare( '%i o INNER JOIN %i tt ON tt.term_id=o.term_id', $wpdb->terms, $wpdb->term_taxonomy ),
				'where' => $wpdb->prepare( 'tt.taxonomy=%s AND tt.parent=%d', $c['object_type'], $c['parent_id'] ),
				'id'    => 'o.term_id',
				'title' => 'o.name',
				'base'  => 'o.name ASC,o.term_id ASC',
			);
		}

		$where = $wpdb->prepare( "o.post_type=%s AND o.post_status='publish'", $c['object_type'] );

		if ( is_post_type_hierarchical( $c['object_type'] ) ) {
			$where .= $wpdb->prepare( ' AND o.post_parent=%d', $c['parent_id'] );
		}

		if ( $c['term_id'] ) {
			$where .= $wpdb->prepare( ' AND EXISTS (SELECT 1 FROM %i tr INNER JOIN %i tx ON tr.term_taxonomy_id=tx.term_taxonomy_id WHERE tr.object_id=o.ID AND tx.taxonomy=%s AND tx.term_id=%d)', $wpdb->term_relationships, $wpdb->term_taxonomy, $c['taxonomy'], $c['term_id'] );
		}

		return array(
			'from'  => $wpdb->prepare( '%i o', $wpdb->posts ),
			'where' => $where,
			'id'    => 'o.ID',
			'title' => 'o.post_title',
			'base'  => 'o.post_date DESC,o.ID DESC',
		);
	}

	/**
	 * @param array<string,mixed> $c Validated values.
	 * @return list<array<string,mixed>>
	 */
	public static function select( array $c, string $extra = '', int $limit = 50, int $offset = 0 ): array {
		global $wpdb;

		$s    = self::source( $c );
		$join = $wpdb->prepare( ' LEFT JOIN %i e ON e.context_id=%d AND e.object_id=', Store::table( 'entries' ), $c['id'] ) . $s['id'];

		// Fragments are fixed identifiers or prepared in source(); extra is prepared by the caller.
		$sql = "SELECT {$s['id']} id,{$s['title']} title,e.rank_value FROM {$s['from']} $join WHERE {$s['where']} $extra ORDER BY (e.rank_value IS NULL),e.rank_value ASC,{$s['base']}";

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- All values are prepared; source() supplies only fixed identifiers and prepared predicates.
		return $wpdb->get_results( $sql . $wpdb->prepare( ' LIMIT %d OFFSET %d', $limit, $offset ), ARRAY_A );
	}

	/**
	 * @param array<string,mixed> $c Validated values.
	 * @return array<string,mixed>
	 */
	public static function items( array $c, int $page, int $per_page, string $search ): array {
		global $wpdb;

		$s = self::source( $c );

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- All values are prepared; source() supplies only fixed identifiers and prepared predicates.
		$extra = '' !== $search ? $wpdb->prepare( ' AND ' . $s['title'] . ' LIKE %s', '%' . $wpdb->esc_like( $search ) . '%' ) : '';
		$rows  = self::select( $c, $extra, $per_page + 1, ( $page - 1 ) * $per_page );
		$more  = count( $rows ) > $per_page;
		$rows  = array_slice( $rows, 0, $per_page );

		$items = array();

		foreach ( $rows as $row ) {
			$id      = (int) $row['id'];
			$items[] = array(
				'id'       => $id,
				'title'    => wp_strip_all_tags( $row['title'] ),
				'editable' => self::object_allowed( $c, $id ),
			);
		}

		return array(
			'context'  => $c,
			'labels'   => self::labels( $c ),
			'archive'  => self::archive( $c ),
			'items'    => $items,
			'page'     => $page,
			'has_more' => $more,
			'revision' => $c['revision'],
		);
	}

	/**
	 * Readable names of the category and parent a context is limited to.
	 *
	 * @param array<string,mixed> $c Validated values.
	 * @return array<string,string>
	 */
	public static function labels( array $c ): array {
		$labels = array(
			'term'   => '',
			'parent' => '',
		);

		if ( $c['term_id'] && $c['taxonomy'] ) {
			$term           = get_term( $c['term_id'], $c['taxonomy'] );
			$labels['term'] = $term instanceof \WP_Term ? wp_strip_all_tags( $term->name ) : '';
		}

		if ( $c['parent_id'] ) {
			if ( 'term' === $c['kind'] ) {
				$parent           = get_term( $c['parent_id'], $c['object_type'] );
				$labels['parent'] = $parent instanceof \WP_Term ? wp_strip_all_tags( $parent->name ) : '';
			} elseif ( current_user_can( 'edit_post', $c['parent_id'] ) ) {
				$labels['parent'] = wp_strip_all_tags( get_the_title( $c['parent_id'] ) );
			}
		}

		return $labels;
	}

	/**
	 * The public archive page this order can sort, so the workspace can say whether that page uses it.
	 * Null for parent orders, term orders, types without an archive and deleted categories.
	 *
	 * @param array<string,mixed> $c Validated values.
	 * @return array{url:string,name:string,applies:bool}|null
	 */
	public static function archive( array $c ): ?array {
		if ( 'post' !== $c['kind'] || $c['parent_id'] ) {
			return null;
		}

		if ( $c['term_id'] && $c['taxonomy'] ) {
			$term = get_term( (int) $c['term_id'], $c['taxonomy'] );
			$link = $term instanceof \WP_Term ? get_term_link( $term ) : '';
			$name = $term instanceof \WP_Term ? $term->name : '';
		} else {
			$type = get_post_type_object( $c['object_type'] );
			$link = $type ? get_post_type_archive_link( $c['object_type'] ) : '';
			$name = $type ? $type->labels->name : '';

			// A static front page without a posts page has no blog archive to sort.
			if ( 'post' === $c['object_type'] && 'page' === get_option( 'show_on_front' ) && ! get_option( 'page_for_posts' ) ) {
				$link = '';
			}
		}

		if ( ! is_string( $link ) || '' === $link || '' === $name ) {
			return null;
		}

		return array(
			'url'     => esc_url_raw( $link ),
			'name'    => wp_strip_all_tags( $name ),
			'applies' => 'archive' === $c['scope'],
		);
	}

	/**
	 * @param array<string,mixed> $c Validated values.
	 */
	public static function object_allowed( array $c, int $id ): bool {
		return 'post' === $c['kind'] ? current_user_can( 'edit_post', $id ) : current_user_can( 'edit_term', $id );
	}

	/**
	 * @param array<string,mixed> $c Validated values.
	 */
	public static function member( array $c, int $id ): bool {
		global $wpdb;

		$s = self::source( $c );

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- All values are prepared; source() supplies only fixed identifiers and prepared predicates.
		return (bool) self::select( $c, $wpdb->prepare( ' AND ' . $s['id'] . '=%d', $id ), 1 );
	}

	/** Reject unsupported storage and outer transactions before opening our transaction. */
	public static function begin(): void {
		global $wpdb;

		foreach ( array( 'contexts', 'entries', 'history', 'imports', 'snapshots' ) as $table ) {
			$engine = $wpdb->get_var( $wpdb->prepare( 'SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=%s', Store::table( $table ) ) );

			if ( strtolower( (string) $engine ) !== 'innodb' ) {
				throw new RuntimeException( 'storage' );
			}
		}

		$quiet = $wpdb->suppress_errors();
		$probe = 'threepo_' . str_replace( '-', '', wp_generate_uuid4() );
		$a     = $wpdb->query( $wpdb->prepare( 'SAVEPOINT %i', $probe ) );
		$b     = $wpdb->query( $wpdb->prepare( 'RELEASE SAVEPOINT %i', $probe ) );
		$wpdb->suppress_errors( $quiet );

		if ( false === $a || false !== $b ) {
			throw new RuntimeException( 'outer_transaction' );
		}

		self::check( $wpdb->query( 'START TRANSACTION' ) );
	}

	/**
	 * @param int|bool $result WordPress boundary value.
	 */
	public static function check( $result ): void {
		if ( false === $result ) {
			throw new RuntimeException( 'database' );
		}
	}

	/** Materialize previously unordered members inside the locked context. */
	/**
	 * @param array<string,mixed> $c Validated values.
	 */
	public static function seed( array $c ): void {
		global $wpdb;

		$s     = self::source( $c );
		$table = Store::table( 'entries' );
		$max   = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COALESCE(MAX(rank_value),0) FROM %i WHERE context_id=%d', $table, $c['id'] ) );
		$head  = $wpdb->prepare( 'INSERT INTO %i (context_id,object_id,rank_value) SELECT %d,', $table, $c['id'] );
		$tail  = $wpdb->prepare( ' AND NOT EXISTS (SELECT 1 FROM %i e WHERE e.context_id=%d AND e.object_id=', $table, $c['id'] ) . $s['id'] . ')';

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared -- All values are prepared; source() supplies only fixed identifiers and prepared predicates.
		self::check( $wpdb->query( $head . $s['id'] . ", $max + ROW_NUMBER() OVER (ORDER BY {$s['base']})*1024 FROM {$s['from']} WHERE {$s['where']} $tail" ) );
	}

	/**
	 * @param array<string,mixed> $c Validated values.
	 */
	public static function rank( array $c, int $id ): int {
		global $wpdb;

		return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT rank_value FROM %i WHERE context_id=%d AND object_id=%d', Store::table( 'entries' ), $c['id'], $id ) );
	}

	/**
	 * @param array<string,mixed> $c Validated values.
	 * @return array{int,int}
	 */
	public static function neighbors( array $c, int $id ): array {
		global $wpdb;

		$rank  = self::rank( $c, $id );
		$s     = self::source( $c );
		$table = Store::table( 'entries' );

		// The source can contain its own join for terms; use correlated membership instead.
		$membership = ' AND EXISTS (SELECT 1 FROM ' . $s['from'] . ' WHERE ' . $s['where'] . ' AND ' . $s['id'] . '=e.object_id)';
		$base       = $wpdb->prepare( 'SELECT object_id FROM %i e WHERE context_id=%d AND object_id<>%d', $table, $c['id'], $id ) . $membership;

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- All values are prepared; source() supplies only fixed identifiers and prepared predicates.
		$prev = (int) $wpdb->get_var( $base . $wpdb->prepare( ' AND rank_value<%d ORDER BY rank_value DESC,object_id DESC LIMIT 1', $rank ) );

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- All values are prepared; source() supplies only fixed identifiers and prepared predicates.
		$next = (int) $wpdb->get_var( $base . $wpdb->prepare( ' AND rank_value>%d ORDER BY rank_value,object_id LIMIT 1', $rank ) );

		return array( $prev, $next );
	}

	/**
	 * @param array<string,mixed> $c Validated values.
	 */
	public static function place( array $c, int $id, string $position, int $target ): void {
		global $wpdb;

		if ( ! self::member( $c, $id ) || ! self::object_allowed( $c, $id ) || ( $target && ( ! self::member( $c, $target ) || ! self::object_allowed( $c, $target ) ) ) || $id === $target ) {
			throw new RuntimeException( 'object' );
		}

		$s          = self::source( $c );
		$table      = Store::table( 'entries' );
		$membership = ' AND EXISTS (SELECT 1 FROM ' . $s['from'] . ' WHERE ' . $s['where'] . ' AND ' . $s['id'] . '=e.object_id)';
		$base       = $wpdb->prepare( 'SELECT rank_value FROM %i e WHERE context_id=%d AND object_id<>%d', $table, $c['id'], $id ) . $membership;
		$left       = null;
		$right      = null;

		if ( 'first' === $position ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- All values are prepared; source() supplies only fixed identifiers and prepared predicates.
			$right = $wpdb->get_var( $base . ' ORDER BY rank_value,object_id LIMIT 1' );
		} elseif ( 'last' === $position ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- All values are prepared; source() supplies only fixed identifiers and prepared predicates.
			$left = $wpdb->get_var( $base . ' ORDER BY rank_value DESC,object_id DESC LIMIT 1' );
		} elseif ( 'before' === $position ) {
			$right = self::rank( $c, $target );

			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- All values are prepared; source() supplies only fixed identifiers and prepared predicates.
			$left = $wpdb->get_var( $base . $wpdb->prepare( ' AND rank_value<%d ORDER BY rank_value DESC LIMIT 1', $right ) );
		} else {
			$left = self::rank( $c, $target );

				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- All values are prepared; source() supplies only fixed identifiers and prepared predicates.
				$right = $wpdb->get_var( $base . $wpdb->prepare( ' AND rank_value>%d ORDER BY rank_value LIMIT 1', $left ) );
		}

		if ( null !== $left && null !== $right && (int) $right - (int) $left < 2 ) {
			$sql = $wpdb->prepare( 'UPDATE %i e INNER JOIN (SELECT object_id,ROW_NUMBER() OVER (ORDER BY rank_value,object_id)*1024 new_rank FROM %i WHERE context_id=%d) r ON r.object_id=e.object_id SET e.rank_value=r.new_rank WHERE e.context_id=%d', $table, $table, $c['id'], $c['id'] );

			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- The complete statement is prepared immediately above.
			self::check( $wpdb->query( $sql ) );
			self::place( $c, $id, $position, $target );

			return;
		}

		$rank = null === $left ? ( null === $right ? 1024 : (int) $right - 1024 ) : ( null === $right ? (int) $left + 1024 : (int) $left + intdiv( (int) $right - (int) $left, 2 ) );

		if ( abs( $rank ) > 9000000000000000 ) {
			throw new RuntimeException( 'capacity' );
		}

		self::check(
			$wpdb->update(
				$table,
				array( 'rank_value' => $rank ),
				array(
					'context_id' => $c['id'],
					'object_id'  => $id,
				),
				array( '%d' ),
				array( '%d', '%d' )
			)
		);
	}

	/**
	 * @param array<string,mixed> $c Validated values.
	 */
	public static function all_allowed( array $c ): void {
		$offset = 0;

		do {
			$rows = self::select( $c, '', 500, $offset );

			foreach ( $rows as $row ) {
				if ( ! self::object_allowed( $c, (int) $row['id'] ) ) {
					throw new RuntimeException( 'object' );
				}
			}
			$offset += count( $rows );
			$more    = count( $rows ) === 500;
		} while ( $more );
	}

	/**
	 * @param array<string,mixed> $data Validated values.
	 * @return array<string,mixed>|\WP_Error
	 */
	public static function mutate( int $id, string $action, array $data ) {
		if ( defined( 'ICL_SITEPRESS_VERSION' ) || function_exists( 'pll_languages_list' ) ) {
			return Store::error( 'language', __( 'Multilingual ordering is not supported in this version.', '3task-post-order' ), 409 );
		}

		global $wpdb;

		$started = false;

		try {
			self::begin();
			$started = true;
			$c       = $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE id=%d FOR UPDATE', Store::table( 'contexts' ), $id ), ARRAY_A );

			if ( ! $c || ! Store::allowed( $c ) ) {
				throw new RuntimeException( 'forbidden' );
			}

			$c           = Store::cast( $c );
			$fingerprint = hash( 'sha256', wp_json_encode( array( $action, $data, get_current_user_id() ) ) );
			$done        = $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE context_id=%d AND operation=%s', Store::table( 'history' ), $id, $data['operation'] ), ARRAY_A );

			if ( $done ) {
				if ( ! hash_equals( $done['fingerprint'], $fingerprint ) ) {
							throw new RuntimeException( 'replay' );
				}

				self::check( $wpdb->query( 'COMMIT' ) );
				$started = false;

				return array(
					'revision'  => (int) $done['revision'],
					'operation' => $data['operation'],
					'replayed'  => true,
				);
			}

			if ( $c['revision'] !== $data['revision'] ) {
				throw new RuntimeException( 'conflict' );
			}

			if ( 'importing' === $c['scope'] ) {
				throw new RuntimeException( 'importing' );
			}

			self::seed( $c );
			$object = 0;
			$prev   = 0;
			$next   = 0;

			if ( 'undo' === $action ) {
				$last = $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE context_id=%d ORDER BY revision DESC LIMIT 1', Store::table( 'history' ), $id ), ARRAY_A );

				if ( ! $last || ! in_array( $last['action'], array( 'move', 'reset' ), true ) || (int) $last['revision'] !== $c['revision'] ) {
					throw new RuntimeException( 'undo' );
				}

				$object = (int) $last['object_id'];
				$prev   = (int) $last['previous_id'];
				$next   = (int) $last['next_id'];

				if ( 'reset' === $last['action'] ) {
					self::all_allowed( $c );
					self::check( $wpdb->delete( Store::table( 'entries' ), array( 'context_id' => $id ) ) );
					self::check( $wpdb->query( $wpdb->prepare( 'INSERT INTO %i (context_id,object_id,rank_value) SELECT context_id,object_id,rank_value FROM %i WHERE context_id=%d AND revision=%d', Store::table( 'entries' ), Store::table( 'snapshots' ), $id, (int) $last['revision'] ) ) );
				} else {
					self::place( $c, $object, $next ? 'before' : ( $prev ? 'after' : 'first' ), $next ? $next : $prev );
				}
			} elseif ( 'move' === $action ) {
				if ( in_array( $data['position'], array( 'before', 'after' ), true ) && ! $data['target_id'] ) {
					throw new RuntimeException( 'object' );
				}

				$object            = $data['object_id'];
				list($prev, $next) = self::neighbors( $c, $object );
				self::place( $c, $object, $data['position'], $data['target_id'] );
			} elseif ( 'reset' === $action ) {
				self::all_allowed( $c );
				self::check( $wpdb->query( $wpdb->prepare( 'INSERT INTO %i (context_id,revision,object_id,rank_value) SELECT context_id,%d,object_id,rank_value FROM %i WHERE context_id=%d', Store::table( 'snapshots' ), $c['revision'] + 1, Store::table( 'entries' ), $id ) ) );
				self::check( $wpdb->delete( Store::table( 'entries' ), array( 'context_id' => $id ) ) );
			} elseif ( 'scope' === $action ) {
				if ( ! current_user_can( 'manage_options' ) ) {
					throw new RuntimeException( 'forbidden' );
				}

				self::check( $wpdb->update( Store::table( 'contexts' ), array( 'scope' => $data['scope'] ), array( 'id' => $id ) ) );
			}

			$revision = $c['revision'] + 1;
			self::check( $wpdb->update( Store::table( 'contexts' ), array( 'revision' => $revision ), array( 'id' => $id ), array( '%d' ), array( '%d' ) ) );
			self::check(
				$wpdb->insert(
					Store::table( 'history' ),
					array(
						'context_id'  => $id,
						'operation'   => $data['operation'],
						'fingerprint' => $fingerprint,
						'actor'       => get_current_user_id(),
						'revision'    => $revision,
						'action'      => $action,
						'object_id'   => $object,
						'previous_id' => $prev,
						'next_id'     => $next,
						'created'     => current_time( 'mysql', true ),
					)
				)
			);
			self::check( $wpdb->query( $wpdb->prepare( 'DELETE FROM %i WHERE context_id=%d AND (revision<%d OR created<%s)', Store::table( 'history' ), $id, max( 0, $revision - 19 ), gmdate( 'Y-m-d H:i:s', time() - 30 * DAY_IN_SECONDS ) ) ) );
			self::check( $wpdb->query( 'COMMIT' ) );
			$started = false;

			// A fresh revision changes the SQL and WordPress query cache key. Page caches can subscribe.
			do_action( 'threepo_order_changed', $id, $revision, $object );

			return array(
				'revision'  => $revision,
				'operation' => $data['operation'],
				'replayed'  => false,
			);
		} catch ( RuntimeException $error ) {
			if ( $started ) {
				$wpdb->query( 'ROLLBACK' );
			}

			if ( ! $started && isset( $revision ) ) {
				return array(
					'revision'      => $revision,
					'operation'     => $data['operation'],
					'replayed'      => false,
					'cache_warning' => true,
				);
			}

			$code   = $error->getMessage();
			$status = in_array( $code, array( 'conflict', 'replay', 'undo', 'importing' ), true ) ? 409 : ( 'object' === $code || 'forbidden' === $code ? 403 : 503 );

			$messages = array(
				'conflict'          => __( 'This order changed in another window. Reload before trying again.', '3task-post-order' ),
				'replay'            => __( 'This operation was already used for a different change.', '3task-post-order' ),
				'object'            => __( 'An item is unavailable or outside this context.', '3task-post-order' ),
				'undo'              => __( 'This change can no longer be undone.', '3task-post-order' ),
				'outer_transaction' => __( 'Ordering is unavailable during another database transaction.', '3task-post-order' ),
			);

			return Store::error( $code, $messages[ $code ] ?? __( 'The order could not be saved. Reload and try again.', '3task-post-order' ), $status );
		}
	}
}