<?php

namespace RoosterLMS\Community;

use RoosterLMS\Data\Db;
use RoosterLMS\Data\SqlPart;
use RoosterLMS\Data\Transaction;
use RoosterLMS\Domain\DomainError;
use RoosterLMS\Domain\ErrorCode;
use RoosterLMS\I18n\Language;
use RoosterLMS\I18n\Switcher;
use RoosterLMS\Progress\ProgressStatus;

defined( 'ABSPATH' ) || exit;

final class Reviews {

	public const MAX_BODY = 5000;

	public const MAX_TITLE = 190;

	public function refusal( int $user_id, int $course_id ): string {
		if ( $user_id <= 0 ) {
			return 'login';
		}
		if ( ! CourseFacts::exists( $course_id ) || ! CourseFacts::reviews_on( $course_id ) ) {
			return 'off';
		}
		if ( ! CourseFacts::is_learner( $user_id, $course_id ) ) {
			return 'not_enrolled';
		}
		$rule = (string) CommunitySettings::get( 'reviews_eligibility' );
		if ( 'enrolled' === $rule ) {
			return '';
		}
		list( $status, $percent ) = CourseFacts::progress( $user_id, $course_id );
		if ( ProgressStatus::COMPLETED === $status ) {
			return '';
		}
		if ( 'progress' === $rule ) {
			return $percent >= (int) CommunitySettings::get( 'reviews_min_percent' ) ? '' : 'not_enough_progress';
		}
		return 'not_completed';
	}

	public function submit( int $user_id, int $course_id, int $rating, string $title, string $body, string $lang = '' ): array {
		$why = $this->refusal( $user_id, $course_id );
		if ( '' !== $why ) {
			DomainError::raise( ErrorCode::FORBIDDEN, array( 'reason' => $why ) );
		}
		if ( $rating < 1 || $rating > 5 ) {
			DomainError::raise( ErrorCode::INVALID, array( 'reason' => 'rating' ) );
		}
		$title = mb_substr( trim( sanitize_text_field( $title ) ), 0, self::MAX_TITLE );
		$body  = mb_substr( trim( wp_kses( $body, Discussions::KSES_TAGS ) ), 0, self::MAX_BODY );
		$lang  = Language::normalize( '' !== $lang ? $lang : Switcher::current() );
		self::check_rate( $user_id );
		$state  = true === CommunitySettings::get( 'reviews_approval' ) ? ReviewState::PENDING : ReviewState::APPROVED;
		$notify = false;
		$id     = (int) Transaction::run(
			function () use ( $user_id, $course_id, $rating, $title, $body, $lang, $state, &$notify ): int {
			$now = Transaction::now();
				$old = Db::row( 'SELECT id, rating, state, updated_at FROM %i WHERE course_id = %d AND user_id = %d FOR UPDATE', Db::table( 'course_reviews' ), $course_id, $user_id );
				if ( null !== $old && ReviewState::REJECTED->value === (int) $old['state'] ) {
					$state = ReviewState::PENDING;
				}
				$notify = null === $old || (string) $old['updated_at'] < gmdate( 'Y-m-d H:i:s', \RoosterLMS\Core\Clock::timestamp() - HOUR_IN_SECONDS );
				if ( null === $old ) {
					Db::query(

							'INSERT INTO %i (course_id, user_id, rating, title, body, lang, state, source, created_at, updated_at) VALUES (%d, %d, %d, %s, %s, %s, %d, %d, %s, %s)',
							Db::table( 'course_reviews' ),
							$course_id,
							$user_id,
							$rating,
							$title,
							$body,
							$lang,
							$state->value,
							ReviewSource::SITE->value,
							$now,
							$now

					);
					$id = Db::insert_id();
				} else {
					$id = (int) $old['id'];
					Db::query( 'UPDATE %i SET rating = %d, title = %s, body = %s, lang = %s, state = %d, updated_at = %s WHERE id = %d', Db::table( 'course_reviews' ), $rating, $title, $body, $lang, $state->value, $now, $id );
				}
				self::apply( $course_id, null === $old ? null : array( (int) $old['state'], (int) $old['rating'] ), array( $state->value, $rating ) );
				return $id;
			}
		);
		if ( $notify ) {
			Notices::review_posted( $course_id, $user_id );
		}
		return (array) $this->get( $id );
	}

	public function bridge( int $user_id, int $course_id, int $rating, string $body, string $lang, int $response_id ): array {
		$none = array(
			'id'      => 0,
			'created' => false,
		);
		if ( $rating < 1 || $rating > 5 ) {
			DomainError::raise( ErrorCode::INVALID, array( 'reason' => 'rating' ) );
		}
		if ( $user_id <= 0 || $course_id <= 0 || $response_id <= 0 || ! CourseFacts::exists( $course_id ) ) {
			return $none;
		}
		$body = mb_substr( trim( wp_kses( $body, Discussions::KSES_TAGS ) ), 0, self::MAX_BODY );
		$lang = Language::normalize( '' !== $lang ? $lang : Switcher::current() );
		return (array) Transaction::run(
			static function () use ( $user_id, $course_id, $rating, $body, $lang, $response_id, $none ): array {
			$own = Db::row( 'SELECT user_id, course_id FROM %i WHERE id = %d LOCK IN SHARE MODE', Db::table( 'feedback_responses' ), $response_id );
				if ( null === $own || (int) $own['user_id'] !== $user_id || (int) $own['course_id'] !== $course_id ) {
					return $none;
				}
				$now     = Transaction::now();
				$pending = ReviewState::PENDING->value;
				$source  = ReviewSource::FEEDBACK_FORM->value;
				$old     = Db::row( 'SELECT id, rating, state, source FROM %i WHERE course_id = %d AND user_id = %d FOR UPDATE', Db::table( 'course_reviews' ), $course_id, $user_id );
				if ( null !== $old && $source !== (int) $old['source'] ) {
					return $none;
				}
				if ( null === $old ) {
					Db::query( 'INSERT INTO %i (course_id, user_id, rating, title, body, lang, state, source, feedback_response_id, created_at, updated_at) VALUES (%d, %d, %d, %s, %s, %s, %d, %d, %d, %s, %s)', Db::table( 'course_reviews' ), $course_id, $user_id, $rating, '', $body, $lang, $pending, $source, $response_id, $now, $now );
					$id = Db::insert_id();
				} else {
					$id = (int) $old['id'];
					Db::query( 'UPDATE %i SET rating = %d, body = %s, lang = %s, state = %d, feedback_response_id = %d, approved_by = NULL, approved_at = NULL, updated_at = %s WHERE id = %d', Db::table( 'course_reviews' ), $rating, $body, $lang, $pending, $response_id, $now, $id );
				}
				self::apply( $course_id, null === $old ? null : array( (int) $old['state'], (int) $old['rating'] ), array( $pending, $rating ) );
				return array(
					'id'      => $id,
					'created' => null === $old,
				);
			}
		);
	}

	public function unbridge( int $response_id ): int {
		if ( $response_id <= 0 ) {
			return 0;
		}
		$table = Db::table( 'course_reviews' );
		$own   = Db::row( 'SELECT user_id, course_id FROM %i WHERE id = %d', Db::table( 'feedback_responses' ), $response_id );
		$ids = null === $own
			? Db::col( 'SELECT id FROM %i WHERE feedback_response_id = %d AND source = %d', $table, $response_id, ReviewSource::FEEDBACK_FORM->value )
			: Db::col( 'SELECT id FROM %i WHERE course_id = %d AND user_id = %d AND feedback_response_id = %d AND source = %d', $table, (int) $own['course_id'], (int) $own['user_id'], $response_id, ReviewSource::FEEDBACK_FORM->value );
		return $this->remove( array_map( 'intval', $ids ) );
	}

	private static function check_rate( int $user_id ): void {
		$limit = (int) CommunitySettings::get( 'qa_rate_limit' );
		if ( $limit <= 0 ) {
			return;
		}
		$key  = 'rooster_lms_review_rate_' . $user_id;
		$now  = \RoosterLMS\Core\Clock::timestamp();
		$slot = get_transient( $key );
		$slot = is_array( $slot ) && (int) ( $slot[1] ?? 0 ) > $now ? $slot : array( 0, $now + HOUR_IN_SECONDS );
		if ( (int) $slot[0] >= $limit ) {
			DomainError::raise( ErrorCode::INVALID, array( 'reason' => 'rate_limit' ) );
		}
		set_transient( $key, array( (int) $slot[0] + 1, (int) $slot[1] ), max( 1, (int) $slot[1] - $now ) );
	}

	public function moderate( int $actor_id, int $id, ?ReviewState $state, ?string $reply = null ): array {
		$row = $this->get( $id );
		if ( null === $row ) {
			DomainError::raise( ErrorCode::NOT_FOUND );
		}
		if ( ! CourseFacts::is_staff( $actor_id, (int) $row['course_id'] ) ) {
			DomainError::raise( ErrorCode::FORBIDDEN );
		}
		$this->set( $id, $state, $actor_id, $reply );
		return (array) $this->get( $id );
	}

	public function set( int $id, ?ReviewState $state, int $actor_id, ?string $reply = null ): void {
		Transaction::run(
			static function () use ( $id, $state, $actor_id, $reply ): void {
				global $wpdb;
				$row = Db::row( 'SELECT id, course_id, rating, state FROM %i WHERE id = %d FOR UPDATE', Db::table( 'course_reviews' ), $id );
				if ( null === $row ) {
					return;
				}
				$now  = Transaction::now();
				$sets = array( SqlPart::of( 'updated_at = %s', $now ) );
				if ( null !== $state ) {
					$sets[] = SqlPart::of( 'state = %d', $state->value );
					if ( ReviewState::APPROVED === $state ) {
						$sets[] = SqlPart::of( 'approved_by = %d, approved_at = %s', $actor_id, $now );
					}
				}
				if ( null !== $reply ) {
					$reply  = mb_substr( trim( wp_kses( $reply, Discussions::KSES_TAGS ) ), 0, self::MAX_BODY );
					$sets[] = '' === $reply ? SqlPart::of( 'reply = NULL, replied_by = NULL, replied_at = NULL' ) : SqlPart::of( 'reply = %s, replied_by = %d, replied_at = %s', $reply, $actor_id, $now );
				}
				Db::query( SqlPart::of( 'UPDATE %i SET ', Db::table( 'course_reviews' ) )->add( SqlPart::join( ', ', $sets ) )->add( SqlPart::of( ' WHERE id = %d', $id ) ) );
				if ( null !== $state ) {
					self::apply( (int) $row['course_id'], array( (int) $row['state'], (int) $row['rating'] ), array( $state->value, (int) $row['rating'] ) );
				}
			}
		);
	}

	public function delete( int $actor_id, int $id ): void {
		$row = $this->get( $id );
		if ( null === $row ) {
			DomainError::raise( ErrorCode::NOT_FOUND );
		}
		if ( ( $actor_id <= 0 || (int) $row['user_id'] !== $actor_id ) && ! CourseFacts::is_staff( $actor_id, (int) $row['course_id'] ) ) {
			DomainError::raise( ErrorCode::FORBIDDEN );
		}
		$this->remove( array( $id ) );
	}

	public function remove( array $ids ): int {
		$ids = array_values( array_unique( array_filter( array_map( 'intval', $ids ) ) ) );
		if ( array() === $ids ) {
			return 0;
		}
		return (int) Transaction::run(
			static function () use ( $ids ): int {
			$rows = Db::rows( 'SELECT id, course_id, rating, state FROM %i WHERE id IN (' . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ') FOR UPDATE', array_merge( array( Db::table( 'course_reviews' ) ), $ids ) );
				$n    = Db::query( 'DELETE FROM %i WHERE id IN (' . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ')', array_merge( array( Db::table( 'course_reviews' ) ), $ids ) );
				foreach ( $rows as $row ) {
					self::apply( (int) $row['course_id'], array( (int) $row['state'], (int) $row['rating'] ), null );
				}
				return $n;
			}
		);
	}

	public function delete_user( int $user_id ): int {
		if ( $user_id <= 0 ) {
			return 0;
		}
		return $this->remove( array_map( 'intval', Db::col( 'SELECT id FROM %i WHERE user_id = %d', Db::table( 'course_reviews' ), $user_id ) ) );
	}

	public function delete_course( int $course_id ): int {
		if ( $course_id <= 0 ) {
			return 0;
		}
		return (int) Transaction::run(
			static function () use ( $course_id ): int {
			$n = Db::query( 'DELETE FROM %i WHERE course_id = %d', Db::table( 'course_reviews' ), $course_id );
				self::recount( $course_id );
				return $n;
			}
		);
	}

	public function get( int $id ): ?array {
		return $id > 0 ? Db::row( 'SELECT * FROM %i WHERE id = %d', Db::table( 'course_reviews' ), $id ) : null;
	}

	public function of_user( int $user_id, int $course_id ): ?array {
		return $user_id > 0 ? Db::row( 'SELECT * FROM %i WHERE course_id = %d AND user_id = %d', Db::table( 'course_reviews' ), $course_id, $user_id ) : null;
	}

	public function list( int $course_id, int $page = 1, int $per_page = 20, ?ReviewState $state = null, int $viewer_id = 0 ): array {
		$per_page = max( 1, min( 100, $per_page ) );
		$state    = $state ?? ReviewState::APPROVED;
		$table    = Db::table( 'course_reviews' );
		$total    = (int) Db::value( 'SELECT COUNT(*) FROM %i WHERE course_id = %d AND state = %d', $table, $course_id, $state->value );
		$rows     = Db::rows( 'SELECT * FROM %i WHERE course_id = %d AND state = %d ORDER BY created_at DESC, id DESC LIMIT %d OFFSET %d', $table, $course_id, $state->value, $per_page, ( max( 1, $page ) - 1 ) * $per_page );
		return array(
			'items' => $this->shape( $rows, $viewer_id ),
			'total' => $total,
		);
	}

	public function summary( int $course_id ): array {
		$stats = Db::row( 'SELECT rating_count, rating_avg FROM %i WHERE course_id = %d', Db::table( 'course_stats' ), $course_id );
		$dist  = array_fill( 1, 5, 0 );
		foreach ( Db::rows( 'SELECT rating, COUNT(*) AS n FROM %i WHERE course_id = %d AND state = %d GROUP BY rating', Db::table( 'course_reviews' ), $course_id, ReviewState::APPROVED->value ) as $r ) {
			if ( isset( $dist[ (int) $r['rating'] ] ) ) {
				$dist[ (int) $r['rating'] ] = (int) $r['n'];
			}
		}
		return array(
			'count'        => null === $stats ? 0 : (int) $stats['rating_count'],
			'average'      => null === $stats || null === $stats['rating_avg'] ? null : (float) $stats['rating_avg'],
			'distribution' => $dist,
		);
	}

	public function shape( array $rows, int $viewer_id = 0 ): array {
		$users = array_values( array_unique( array_filter( array_map( static fn( array $r ): int => (int) $r['user_id'], $rows ) ) ) );
		if ( array() !== $users ) {
			cache_users( $users );
		}
		$out = array();
		foreach ( $rows as $r ) {
			$user  = (int) $r['user_id'];
			$data  = $user > 0 ? get_userdata( $user ) : false;
			$item  = array(
				'id'         => (int) $r['id'],
				'course_id'  => (int) $r['course_id'],
				'own'        => $viewer_id > 0 && $user === $viewer_id,
				'author'     => $data instanceof \WP_User ? (string) $data->display_name : '',
				'rating'     => (int) $r['rating'],
				'title'      => (string) $r['title'],
				'body'       => (string) $r['body'],
				'lang'       => (string) $r['lang'],
				'state'      => ( ReviewState::tryFrom( (int) $r['state'] ) ?? ReviewState::PENDING )->slug(),
				'source'     => (int) $r['source'],
				'reply'      => null === $r['reply'] ? '' : (string) $r['reply'],
				'replied_at' => (string) ( $r['replied_at'] ?? '' ),
				'created_at' => (string) $r['created_at'],
				'updated_at' => (string) $r['updated_at'],
			);
			if ( $viewer_id > 0 && CourseFacts::is_staff( $viewer_id, (int) $r['course_id'] ) ) {
				$item['user_id'] = $user;
			}
			$out[] = $item;
		}
		return $out;
	}

	public static function apply( int $course_id, ?array $before, ?array $after ): void {
		$approved = ReviewState::APPROVED->value;
		$dc       = 0;
		$ds       = 0;
		if ( null !== $before && $approved === $before[0] ) {
			--$dc;
			$ds -= $before[1];
		}
		if ( null !== $after && $approved === $after[0] ) {
			++$dc;
			$ds += $after[1];
		}
		if ( 0 === $dc && 0 === $ds ) {
			return;
		}
		global $wpdb;
		$avg = $dc > 0 && $ds > 0 ? SqlPart::of( '%s', number_format( $ds / $dc, 2, '.', '' ) ) : SqlPart::of( 'NULL' );
		Db::delta(
			SqlPart::of( 'INSERT INTO %i (course_id, rating_count, rating_sum, rating_avg, updated_at) VALUES (%d, %d, %d, ', Db::table( 'course_stats' ), $course_id, max( 0, $dc ), max( 0, $ds ) )->add( $avg )->add( SqlPart::of( ', %s) ON DUPLICATE KEY UPDATE rating_count = GREATEST( CAST( rating_count AS SIGNED ) + %d, 0 ), rating_sum = GREATEST( CAST( rating_sum AS SIGNED ) + %d, 0 ), rating_avg = IF( rating_count > 0, ROUND( rating_sum / rating_count, 2 ), NULL )', Transaction::now(), $dc, $ds ) )
		);
	}

	public static function recount( int $course_id ): void {
		global $wpdb;
		$row   = Db::row( 'SELECT COUNT(*) AS n, COALESCE( SUM( rating ), 0 ) AS s FROM %i WHERE course_id = %d AND state = %d', Db::table( 'course_reviews' ), $course_id, ReviewState::APPROVED->value );
		$count = (int) ( $row['n'] ?? 0 );
		$sum   = (int) ( $row['s'] ?? 0 );
		$avg   = $count > 0 ? SqlPart::of( '%s', number_format( $sum / $count, 2, '.', '' ) ) : SqlPart::of( 'NULL' );
		Db::query(
			SqlPart::of( 'INSERT INTO %i (course_id, rating_count, rating_sum, rating_avg, updated_at) VALUES (%d, %d, %d, ', Db::table( 'course_stats' ), $course_id, $count, $sum )->add( $avg )->add( SqlPart::of( ', %s) ON DUPLICATE KEY UPDATE rating_count = VALUES(rating_count), rating_sum = VALUES(rating_sum), rating_avg = VALUES(rating_avg)', Transaction::now() ) )
		);
	}
}