<?php

namespace RoosterLMS\CourseBuilder;

use RoosterLMS\Community\CommunitySettings;
use RoosterLMS\Community\DiscussionState;
use RoosterLMS\Community\ReviewState;
use RoosterLMS\Content\Course;
use RoosterLMS\Data\Db;
use RoosterLMS\Data\SqlPart;
use RoosterLMS\Domain\Services;
use RoosterLMS\Faq\Faq;
use RoosterLMS\Faq\FaqGroup;
use RoosterLMS\Faq\FaqLinks;
use RoosterLMS\Faq\FaqService;
use RoosterLMS\Faq\FaqTarget;
use RoosterLMS\I18n\Names;
use RoosterLMS\I18n\Strings;
use RoosterLMS\I18n\TextObject;

defined( 'ABSPATH' ) || exit;

final class CommunityTabs {

	public const LIST_LIMIT = 300;

	public static function faq( Course $course, string $lang, int $user_id ): array {
		global $wpdb;
		$links  = Services::faq_links()->links( FaqTarget::COURSE, $course->id );
		$faq_id = array();
		$grp_id = array();
		foreach ( $links as $link ) {
			if ( FaqLinks::KIND_FAQ === $link['kind'] ) {
				$faq_id[] = $link['ref_id'];
			} else {
				$grp_id[] = $link['ref_id'];
			}
		}
		$faqs = array();
		if ( array() !== $links ) {
			$where = array();
			if ( array() !== $faq_id ) {
				$where[] = SqlPart::of( '1 = 1' )->add( SqlPart::and_in( 'id', $faq_id ) );
			}
			if ( array() !== $grp_id ) {
				$where[] = SqlPart::of( '1 = 1' )->add( SqlPart::and_in( 'group_id', $grp_id ) );
			}
			foreach ( Db::rows( SqlPart::of( 'SELECT * FROM %i WHERE ', Db::table( 'faqs' ) )->add( '( ( ' )->add( SqlPart::join( ' ) OR ( ', $where ) )->add( ' ) ) ORDER BY position, id LIMIT 2000' ) ) as $row ) {
				$faq              = Faq::from_row( $row );
				$faqs[ $faq->id ] = $faq;
			}
		}
		$groups = Services::faqs()->groups();
		$tree   = Services::faqs()->tree( true );
		$view   = Strings::edit_language( $lang );
		$names  = Services::faqs()->group_names( array_map( static fn( array $n ): FaqGroup => $n['group'], $tree ), $view );
		$texts  = Services::faqs()->texts( array_values( $faqs ), $view );
		$uses   = self::faq_uses( $faqs, $grp_id );
		$counts = array();
		foreach ( Db::rows( 'SELECT group_id, COUNT(*) AS n FROM %i GROUP BY group_id', Db::table( 'faqs' ) ) as $row ) {
			$counts[ (int) $row['group_id'] ] = (int) $row['n'];
		}
		$missing = self::missing_faqs( $faqs );
		$default = Strings::writes_stored( $lang );
		$out     = array();
		foreach ( $faqs as $faq ) {
			$item = array(
				'id'       => $faq->id,
				'group_id' => $faq->group_id,
				'question' => $texts[ $faq->id ]['question'] ?? $faq->question,
				'answer'   => $texts[ $faq->id ]['answer'] ?? $faq->answer,
				'state'    => Faq::state_slug( $faq->state ),
				'editable' => FaqService::can_edit( $faq, $user_id ),
				'uses'     => ( $uses['faq'][ $faq->id ] ?? 0 ) + ( $uses['group'][ $faq->group_id ] ?? 0 ),
				'missing'  => $missing[ $faq->id ] ?? array(),
			);
			if ( ! $default ) {
				$item['default_question'] = $faq->question;
				$item['default_answer']   = $faq->answer;
			}
			$out[] = $item;
		}
		$title = trim( wp_strip_all_tags( (string) get_the_title( $course->home_post_id ) ) );
		$own   = 0;
		foreach ( $groups as $group ) {
			if ( '' !== $title && $group->is_active() && 0 === strcasecmp( $group->name, $title ) ) {
				$own = $group->id;
				break;
			}
		}
		return array(
			'links'        => array_map(
				static fn( array $l ): array => array(
					'kind' => FaqLinks::KIND_GROUP === $l['kind'] ? 'group' : 'faq',
					'id'   => $l['ref_id'],
				),
				$links
			),
			'faqs'         => $out,
			'groups'       => array_map(
				static fn( array $n ): array => array(
					'id'        => $n['group']->id,
					'parent_id' => $n['group']->parent_id,
					'name'      => (string) ( $names[ $n['group']->id ] ?? $n['group']->name ),
					'depth'     => $n['depth'],
					'archived'  => ! $n['group']->is_active(),
					'count'     => $counts[ $n['group']->id ] ?? 0,
					'uses'      => $uses['group'][ $n['group']->id ] ?? 0,
				),
				$tree
			),
			'ungrouped'    => $counts[0] ?? 0,
			'course_group' => $own,
			'course_title' => $title,
			'course_title_shown' => $own > 0 && isset( $names[ $own ] ) ? (string) $names[ $own ] : self::shown_title( $course, $view, $title ),
			'can_link'     => FaqLinks::can_link( FaqTarget::COURSE, $course->id, $user_id ),
			'can_write'    => FaqService::can_write( $user_id ),
			'can_groups'   => FaqService::can_edit_all( $user_id ),
			'library_url'  => admin_url( 'admin.php?page=rooster-lms-faq' ),
		);
	}

	private static function shown_title( Course $course, string $lang, string $fallback ): string {
		$post = (int) ( Services::posts()->for_objects( \RoosterLMS\Content\ObjectType::COURSE, array( $course->id ), $lang )[ $course->id ] ?? 0 );
		$text = $post > 0 && $post !== $course->home_post_id ? trim( wp_strip_all_tags( (string) get_the_title( $post ) ) ) : '';
		return '' !== $text ? $text : $fallback;
	}

	private static function faq_uses( array $faqs, array $groups ): array {
		$out    = array(
			'faq'   => array(),
			'group' => array(),
		);
		$groups = array_values( array_unique( array_filter( array_merge( $groups, array_map( static fn( Faq $f ): int => $f->group_id, $faqs ) ) ) ) );
		if ( array() === $faqs && array() === $groups ) {
			return $out;
		}
		global $wpdb;
		$where = array();
		if ( array() !== $faqs ) {
			$where[] = SqlPart::of( 'kind = %d', FaqLinks::KIND_FAQ )->add( SqlPart::and_in( 'ref_id', array_keys( $faqs ) ) );
		}
		if ( array() !== $groups ) {
			$where[] = SqlPart::of( 'kind = %d', FaqLinks::KIND_GROUP )->add( SqlPart::and_in( 'ref_id', $groups ) );
		}
		foreach ( Db::rows( SqlPart::of( 'SELECT kind, ref_id, COUNT(*) AS n FROM %i WHERE ', Db::table( 'faq_links' ) )->add( '( ( ' )->add( SqlPart::join( ' ) OR ( ', $where ) )->add( ' ) ) GROUP BY kind, ref_id' ) ) as $row ) {
			$out[ FaqLinks::KIND_GROUP === (int) $row['kind'] ? 'group' : 'faq' ][ (int) $row['ref_id'] ] = (int) $row['n'];
		}
		return $out;
	}

	private static function missing_faqs( array $faqs ): array {
		if ( array() === $faqs || ! self::translates() ) {
			return array();
		}
		$out = array();
		foreach ( Names::other_languages() as $code ) {
			$questions = array();
			$answers   = array();
			foreach ( $faqs as $faq ) {
				$questions[ $faq->id ] = $faq->question;
				if ( '' !== $faq->answer ) {
					$answers[ $faq->id ] = $faq->answer;
				}
			}
			$has_q = Services::strings()->has_translations( TextObject::FAQ, $questions, $code, 'question' );
			$has_a = array() === $answers ? array() : Services::strings()->has_translations( TextObject::FAQ, $answers, $code, 'answer' );
			foreach ( $faqs as $faq ) {
				if ( ! ( $has_q[ $faq->id ] ?? false ) || ( isset( $answers[ $faq->id ] ) && ! ( $has_a[ $faq->id ] ?? false ) ) ) {
					$out[ $faq->id ][] = $code;
				}
			}
		}
		return $out;
	}

	public static function notice( Course $course, string $lang ): array {
		$details = Services::course_details();
		$texts   = $details->texts_in( $course->id, Strings::edit_language( $lang ) );
		$stored  = (string) ( $details->texts( $course->id )[ \RoosterLMS\CourseInfo\CourseTextKind::NOTICE->value ] ?? '' );
		$out     = array(
			'html'    => (string) ( $texts['notice'] ?? '' ),
			'missing' => array(),
		);
		if ( ! Strings::writes_stored( $lang ) ) {
			$out['default_html'] = $stored;
		}
		if ( '' !== $stored && self::translates() ) {
			foreach ( Names::other_languages() as $code ) {
				if ( ! ( Services::strings()->has_translations( TextObject::COURSE, array( $course->id => $stored ), $code, 'notice' )[ $course->id ] ?? false ) ) {
					$out['missing'][] = $code;
				}
			}
		}
		return $out;
	}

	public static function reviews( Course $course ): array {
		$counts = self::counts( 'course_reviews', $course->id, array_map( static fn( ReviewState $s ): array => array( $s->value, $s->slug() ), ReviewState::cases() ), false );
		$rows   = Db::rows( 'SELECT * FROM %i WHERE course_id = %d ORDER BY ( state = %d ) DESC, created_at DESC, id DESC LIMIT %d', Db::table( 'course_reviews' ), $course->id, ReviewState::PENDING->value, self::LIST_LIMIT );
		$names  = self::authors( $rows );
		$items  = array();
		foreach ( $rows as $r ) {
			$items[] = array(
				'id'         => (int) $r['id'],
				'author'     => self::author( $r, $names ),
				'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'      => (string) ( $r['reply'] ?? '' ),
				'replied_at' => (string) ( $r['replied_at'] ?? '' ),
				'created_at' => (string) $r['created_at'],
			);
		}
		return array(
			'mode'           => $course->reviews_mode,
			'site_on'        => true === CommunitySettings::get( 'reviews_enabled' ),
			'approval'       => true === CommunitySettings::get( 'reviews_approval' ),
			'summary'        => Services::reviews()->summary( $course->id ),
			'counts'         => $counts,
			'items'          => $items,
			'total'          => array_sum( $counts ),
			'limit'          => self::LIST_LIMIT,
			'moderation_url' => admin_url( 'admin.php?page=rooster-lms-community&tab=reviews' ),
		);
	}

	public static function qa( Course $course ): array {
		global $wpdb;
		$table   = Db::table( 'discussions' );
		$counts  = self::counts( 'discussions', $course->id, array_map( static fn( DiscussionState $s ): array => array( $s->value, $s->slug() ), DiscussionState::cases() ), true );
		$rows    = Db::rows( 'SELECT * FROM %i WHERE course_id = %d AND parent_id = 0 AND state <> %d ORDER BY ( state = %d ) DESC, pinned DESC, last_activity_at DESC, id DESC LIMIT %d', $table, $course->id, DiscussionState::TRASHED->value, DiscussionState::PENDING->value, self::LIST_LIMIT );
		$replies = array();
		foreach ( Db::rows( 'SELECT parent_id, MAX( CASE WHEN state = %d THEN is_answer ELSE 0 END ) AS answered, SUM( state = %d ) AS waiting FROM %i WHERE course_id = %d AND parent_id > 0 GROUP BY parent_id', DiscussionState::VISIBLE->value, DiscussionState::PENDING->value, $table, $course->id ) as $row ) {
			$replies[ (int) $row['parent_id'] ] = array( 1 === (int) $row['answered'], (int) $row['waiting'] );
		}
		$names = self::authors( $rows );
		$items = array();
		foreach ( $rows as $r ) {
			$id      = (int) $r['id'];
			$items[] = array(
				'id'               => $id,
				'unit_id'          => (int) $r['unit_id'],
				'author'           => self::author( $r, $names ),
				'body'             => (string) $r['body'],
				'state'            => ( DiscussionState::tryFrom( (int) $r['state'] ) ?? DiscussionState::HIDDEN )->slug(),
				'private'          => 1 === (int) ( $r['is_private'] ?? 0 ),
				'pinned'           => 1 === (int) $r['pinned'],
				'reply_count'      => (int) $r['reply_count'],
				'answered'         => $replies[ $id ][0] ?? false,
				'waiting_replies'  => $replies[ $id ][1] ?? 0,
				'lang'             => (string) $r['lang'],
				'created_at'       => (string) $r['created_at'],
				'last_activity_at' => (string) $r['last_activity_at'],
			);
		}
		$lessons = array();
		foreach ( Db::rows( 'SELECT i.unit_id, p.post_title FROM %i i JOIN %i u ON u.id = i.unit_id LEFT JOIN %i p ON p.ID = u.home_post_id LEFT JOIN %i s ON s.id = i.section_id WHERE i.course_id = %d ORDER BY COALESCE( s.position, 0 ), i.position, i.id', Db::table( 'items' ), Db::table( 'units' ), $wpdb->posts, Db::table( 'sections' ), $course->id ) as $row ) {
			$title     = trim( wp_strip_all_tags( (string) $row['post_title'] ) );
			$lessons[] = array(
				'unit_id' => (int) $row['unit_id'],
				'title'   => '' !== $title ? $title : __( '(no title)', 'rooster-lms' ),
			);
		}
		return array(
			'mode'           => $course->qa_mode,
			'site_on'        => true === CommunitySettings::get( 'qa_enabled' ),
			'site_mode'      => (int) CommunitySettings::get( 'qa_mode' ),
			'counts'         => $counts,
			'items'          => $items,
			'total'          => $counts['visible'] + $counts['pending'] + $counts['hidden'] + $counts['external'],
			'limit'          => self::LIST_LIMIT,
			'lessons'        => $lessons,
			'moderation_url' => admin_url( 'admin.php?page=rooster-lms-community&tab=qa' ),
		);
	}

	private static function counts( string $table, int $course, array $states, bool $questions ): array {
		global $wpdb;
		$by  = array();
		$sql = $questions
			? SqlPart::of( 'SELECT state, COUNT(*) AS n FROM %i WHERE course_id = %d AND parent_id = 0 GROUP BY state', Db::table( $table ), $course )
			: SqlPart::of( 'SELECT state, COUNT(*) AS n FROM %i WHERE course_id = %d GROUP BY state', Db::table( $table ), $course );
		foreach ( Db::rows( $sql ) as $row ) {
			$by[ (int) $row['state'] ] = (int) $row['n'];
		}
		$out = array();
		foreach ( $states as $state ) {
			$out[ $state[1] ] = $by[ $state[0] ] ?? 0;
		}
		return $out;
	}

	private static function authors( array $rows ): array {
		$ids = array_values( array_unique( array_filter( array_map( static fn( array $r ): int => (int) ( $r['user_id'] ?? 0 ), $rows ) ) ) );
		if ( array() === $ids ) {
			return array();
		}
		global $wpdb;
		$out = array();
		foreach ( Db::rows( SqlPart::of( 'SELECT ID, display_name FROM %i WHERE 1 = 1', $wpdb->users )->add( SqlPart::and_in( 'ID', $ids ) ) ) as $row ) {
			$out[ (int) $row['ID'] ] = (string) $row['display_name'];
		}
		return $out;
	}

	private static function author( array $row, array $names ): string {
		$name = $names[ (int) ( $row['user_id'] ?? 0 ) ] ?? '';
		if ( '' === $name ) {
			$name = (string) ( $row['author_label'] ?? '' );
		}
		return '' !== $name ? $name : __( 'Former learner', 'rooster-lms' );
	}

	private static function translates(): bool {
		return count( Names::languages() ) > 1 && \RoosterLMS\I18n\Providers::current()->supports_strings();
	}
}