<?php
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

/**
 * Data access layer for reviews. All DB reads/writes funnel through here.
 */
class OmoRev_Data {

	public static function table() {
		global $wpdb;
		return $wpdb->prefix . 'omorev_reviews';
	}

	public static function votes_table() {
		global $wpdb;
		return $wpdb->prefix . 'omorev_votes';
	}

	/**
	 * Insert a review. $data is already-sanitized array.
	 *
	 * @return int|false review id or false
	 */
	public static function insert( $data ) {
		global $wpdb;

		$defaults = array(
			'post_id'          => 0,
			'user_id'          => 0,
			'author_name'      => '',
			'author_email'     => '',
			'title'            => '',
			'content'          => '',
			'rating_overall'   => 0,
			'criteria_ratings' => '',
			'pros'             => '',
			'cons'             => '',
			'recommended'      => 0,
			'verified'         => 0,
			'status'           => 'pending',
			'ip'               => '',
			'parent_id'        => 0,
			'created_at'       => current_time( 'mysql' ),
		);
		$data = wp_parse_args( $data, $defaults );

		$ok = $wpdb->insert( self::table(), $data ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom table.
		if ( false === $ok ) {
			return false;
		}
		$id = (int) $wpdb->insert_id;

		do_action( 'omorev_review_inserted', $id, $data );
		self::bust_aggregate_cache( (int) $data['post_id'] );
		return $id;
	}

	public static function update_status( $id, $status ) {
		global $wpdb;
		$id     = (int) $id;
		$status = in_array( $status, array( 'pending', 'approved', 'spam', 'trash' ), true ) ? $status : 'pending';
		$row    = self::get( $id );
		$wpdb->update( self::table(), array( 'status' => $status ), array( 'id' => $id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- Custom table.
		if ( $row ) {
			self::bust_aggregate_cache( (int) $row->post_id );
		}
		return true;
	}

	public static function set_verified( $id, $verified ) {
		global $wpdb;
		$id  = (int) $id;
		$row = self::get( $id );
		$wpdb->update( self::table(), array( 'verified' => $verified ? 1 : 0 ), array( 'id' => $id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- Custom table.
		if ( $row ) {
			self::bust_aggregate_cache( (int) $row->post_id );
		}
		return true;
	}

	public static function delete( $id ) {
		global $wpdb;
		$id  = (int) $id;
		$row = self::get( $id );
		$wpdb->delete( self::table(), array( 'id' => $id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- Custom table.
		$wpdb->delete( self::votes_table(), array( 'review_id' => $id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- Custom table.
		if ( $row ) {
			self::bust_aggregate_cache( (int) $row->post_id );
		}
		return true;
	}

	public static function get( $id ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table.
		return $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', self::table(), (int) $id ) );
	}

	/**
	 * Approved top-level reviews for a post, sorted and paginated.
	 *
	 * @param array $args post_id, sort, per_page, page.
	 * @return array
	 */
	public static function query( $args = array() ) {
		global $wpdb;
		$args = wp_parse_args(
			$args,
			array(
				'post_id'  => 0,
				'sort'     => 'recent',
				'per_page' => 10,
				'page'     => 1,
			)
		);

		$sorts = array(
			'recent'      => array( 'created_at', 'DESC' ),
			'rating_high' => array( 'rating_overall', 'DESC' ),
			'rating_low'  => array( 'rating_overall', 'ASC' ),
			'helpful'     => array( 'helpful_yes', 'DESC' ),
		);
		$sort     = isset( $sorts[ $args['sort'] ] ) ? $sorts[ $args['sort'] ] : $sorts['recent'];
		$per_page = max( 1, (int) $args['per_page'] );
		$offset   = ( max( 1, (int) $args['page'] ) - 1 ) * $per_page;
		$post_id  = (int) $args['post_id'];
		$table    = self::table();

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table.
		if ( 'ASC' === $sort[1] ) {
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					'SELECT * FROM %i WHERE post_id = %d AND status = %s AND parent_id = 0 ORDER BY %i ASC, created_at DESC LIMIT %d OFFSET %d',
					$table,
					$post_id,
					'approved',
					$sort[0],
					$per_page,
					$offset
				)
			);
		} else {
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					'SELECT * FROM %i WHERE post_id = %d AND status = %s AND parent_id = 0 ORDER BY %i DESC, created_at DESC LIMIT %d OFFSET %d',
					$table,
					$post_id,
					'approved',
					$sort[0],
					$per_page,
					$offset
				)
			);
		}
		// phpcs:enable

		return $rows;
	}

	public static function count( $post_id, $status = 'approved', $parent_id = 0 ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table.
		return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE post_id = %d AND status = %s AND parent_id = %d', self::table(), (int) $post_id, $status, (int) $parent_id ) );
	}

	public static function get_replies( $parent_id ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table.
		return $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE parent_id = %d AND status = %s ORDER BY created_at ASC', self::table(), (int) $parent_id, 'approved' ) );
	}

	/**
	 * All replies regardless of status (admin screen).
	 */
	public static function get_all_replies( $parent_id ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table.
		return $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE parent_id = %d ORDER BY created_at ASC', self::table(), (int) $parent_id ) );
	}

	/**
	 * Top-level reviews with a given status (admin screen).
	 */
	public static function admin_list( $status, $limit = 100 ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table.
		return $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE status = %s AND parent_id = 0 ORDER BY created_at DESC LIMIT %d', self::table(), $status, (int) $limit ) );
	}

	/**
	 * Count of top-level reviews per status (admin screen).
	 *
	 * @return array status => count
	 */
	public static function admin_counts() {
		global $wpdb;
		$counts = array();
		foreach ( array( 'pending', 'approved', 'spam', 'trash' ) as $st ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table.
			$counts[ $st ] = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE status = %s AND parent_id = 0', self::table(), $st ) );
		}
		return $counts;
	}

	/**
	 * Aggregate rating data for a post: average, count, star distribution,
	 * and per-criteria averages. Cached in a transient.
	 */
	public static function aggregates( $post_id ) {
		$post_id = (int) $post_id;
		$key     = 'omorev_agg_' . $post_id;
		$cached  = get_transient( $key );
		if ( false !== $cached ) {
			return $cached;
		}

		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table; result cached in a transient below.
		$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT rating_overall, criteria_ratings FROM %i WHERE post_id = %d AND status = %s AND parent_id = 0', self::table(), $post_id, 'approved' ) );

		$count = count( $rows );
		$dist  = array( 5 => 0, 4 => 0, 3 => 0, 2 => 0, 1 => 0 );
		$sum   = 0.0;
		$crit  = array();

		foreach ( $rows as $r ) {
			$val  = (float) $r->rating_overall;
			$sum += $val;
			$bucket = max( 1, min( 5, (int) round( $val ) ) );
			$dist[ $bucket ]++;

			$cr = json_decode( (string) $r->criteria_ratings, true );
			if ( is_array( $cr ) ) {
				foreach ( $cr as $k => $v ) {
					if ( ! isset( $crit[ $k ] ) ) {
						$crit[ $k ] = array( 'sum' => 0, 'n' => 0 );
					}
					$crit[ $k ]['sum'] += (float) $v;
					$crit[ $k ]['n']++;
				}
			}
		}

		$crit_avg = array();
		foreach ( $crit as $k => $c ) {
			$crit_avg[ $k ] = $c['n'] ? round( $c['sum'] / $c['n'], 2 ) : 0;
		}

		$data = array(
			'count'    => $count,
			'average'  => $count ? round( $sum / $count, 2 ) : 0,
			'dist'     => $dist,
			'criteria' => $crit_avg,
		);

		set_transient( $key, $data, HOUR_IN_SECONDS );
		return $data;
	}

	public static function bust_aggregate_cache( $post_id ) {
		delete_transient( 'omorev_agg_' . (int) $post_id );
	}

	/**
	 * Record a helpful vote. $hash uniquely identifies a voter.
	 *
	 * @return array|false updated counts
	 */
	public static function vote( $review_id, $hash, $vote ) {
		global $wpdb;
		$review_id = (int) $review_id;
		$vote      = $vote ? 1 : 0;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table.
		$exists = $wpdb->get_var( $wpdb->prepare( 'SELECT id FROM %i WHERE review_id = %d AND voter_hash = %s', self::votes_table(), $review_id, $hash ) );
		if ( $exists ) {
			return false; // already voted
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom table.
		$wpdb->insert( self::votes_table(), array(
			'review_id'  => $review_id,
			'voter_hash' => $hash,
			'vote'       => $vote,
			'created_at' => current_time( 'mysql' ),
		) );

		$col = $vote ? 'helpful_yes' : 'helpful_no';
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table.
		$wpdb->query( $wpdb->prepare( 'UPDATE %i SET %i = %i + 1 WHERE id = %d', self::table(), $col, $col, $review_id ) );

		$row = self::get( $review_id );
		return array(
			'yes' => (int) $row->helpful_yes,
			'no'  => (int) $row->helpful_no,
		);
	}

	public static function has_reviewed( $post_id, $user_id, $email = '' ) {
		global $wpdb;
		if ( $user_id ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table.
			return (bool) $wpdb->get_var( $wpdb->prepare( 'SELECT id FROM %i WHERE post_id = %d AND user_id = %d AND parent_id = 0', self::table(), (int) $post_id, (int) $user_id ) );
		}
		if ( $email ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table.
			return (bool) $wpdb->get_var( $wpdb->prepare( 'SELECT id FROM %i WHERE post_id = %d AND author_email = %s AND parent_id = 0', self::table(), (int) $post_id, $email ) );
		}
		return false;
	}
}