<?php

namespace RoosterLMS\Blocks;

use RoosterLMS\Assessment\QuizService;
use RoosterLMS\Assessment\ReleaseService;
use RoosterLMS\Content\CourseRepository;
use RoosterLMS\Content\ObjectType;
use RoosterLMS\Content\UnitType;
use RoosterLMS\Core\Clock;
use RoosterLMS\Data\Db;
use RoosterLMS\Data\SqlPart;
use RoosterLMS\Domain\Services;
use RoosterLMS\I18n\Fold;
use RoosterLMS\I18n\Providers;
use RoosterLMS\Orgs\AccessTemplate;
use RoosterLMS\Orgs\Branch;
use RoosterLMS\Orgs\MemberRole;
use RoosterLMS\Orgs\MemberStatus;
use RoosterLMS\Orgs\OrgScope;
use RoosterLMS\Orgs\StaffStatus;
use RoosterLMS\Orgs\UnitLabels;
use RoosterLMS\Reports\ReportCounts;
use RoosterLMS\Reports\ScopedStats;

defined( 'ABSPATH' ) || exit;

final class PortalData {

	public static function dashboard( OrgScope $scope, int $org_id, string $lang ): array {
		$scope->require_reports( $org_id );
		global $wpdb;
		$unit  = null === $scope->unit_scope( $org_id, OrgScope::REPORT_MASK ) ? '' : 'branch';
		$multi = $scope->multi_unit( $org_id );
		if ( '' === $unit ) {
			$stats = Db::rows(

					'SELECT course_id, assigned, not_started, in_progress, completed FROM %i WHERE org_id = %d ORDER BY course_id',
					Db::table( 'org_course_stats' ),
					$org_id

			);
		} else {
			$stats = array();
			foreach ( ScopedStats::per_course( $scope, $org_id ) as $course_id => $figures ) {
				$stats[] = array( 'course_id' => $course_id ) + $figures;
			}
		}
		$totals = array(
			'assigned'    => 0,
			'not_started' => 0,
			'in_progress' => 0,
			'completed'   => 0,
		);
		$ids    = array();
		foreach ( $stats as $row ) {
			foreach ( array_keys( $totals ) as $key ) {
				$totals[ $key ] += (int) $row[ $key ];
			}
			if ( (int) $row['assigned'] > 0 ) {
				$ids[] = (int) $row['course_id'];
			}
		}
		$titles    = array() === $ids ? array() : Services::posts()->titles( ObjectType::COURSE, $ids, $lang );
		$by_course = array();
		foreach ( $stats as $row ) {
			$id = (int) $row['course_id'];
			if ( (int) $row['assigned'] <= 0 ) {
				continue;
			}
			$post_id     = (int) ( $titles[ $id ]['post_id'] ?? 0 );
			$by_course[] = array(
				'course_id'   => $id,
				'title'       => (string) ( $titles[ $id ]['title'] ?? '' ),
				'url'         => $post_id > 0 ? Services::posts()->permalink( $post_id ) : '',
				'assigned'    => (int) $row['assigned'],
				'not_started' => (int) $row['not_started'],
				'in_progress' => (int) $row['in_progress'],
				'completed'   => (int) $row['completed'],
			);
		}
		usort( $by_course, static fn( array $a, array $b ): int => array( Fold::fold( $a['title'] ), $a['course_id'] ) <=> array( Fold::fold( $b['title'] ), $b['course_id'] ) );

		$today = Clock::org_today( $org_id );
		if ( $multi ) {
			$groups = Db::rows(
				SqlPart::of(
					'SELECT 1 AS rl_kind, job_role_id, 0 AS location_id, status, CASE WHEN active_until IS NOT NULL AND active_until < %s THEN 1 ELSE 0 END AS rl_expired,
						CASE WHEN ( roles & %d ) <> 0 THEN 1 ELSE 0 END AS rl_learner, COUNT(*) AS n
					FROM %i WHERE org_id = %d AND left_at IS NULL',
					$today,
					MemberRole::LEARNER->value,
					Db::table( 'org_members' ),
					$org_id
				)->add( Branch::where( $scope, $org_id, '', OrgScope::REPORT_MASK ) )->add( '
					GROUP BY job_role_id, status, rl_expired, rl_learner
				UNION ALL SELECT 2 AS rl_kind, 0, t.rl_unit, 0, 0, 0, SUM(t.n) FROM ( ' )->add( Services::placement_repo()->people_by_unit_sql( $org_id, $scope->unit_scope( $org_id, OrgScope::REPORT_MASK ) ) )->add( ' ) t GROUP BY t.rl_unit HAVING SUM(t.n) > 0' )
			);
		} else {
			$groups = Db::rows(
				SqlPart::of(
					'SELECT job_role_id, location_id, status, CASE WHEN active_until IS NOT NULL AND active_until < %s THEN 1 ELSE 0 END AS rl_expired,
						CASE WHEN ( roles & %d ) <> 0 THEN 1 ELSE 0 END AS rl_learner, COUNT(*) AS n
					FROM %i WHERE org_id = %d AND left_at IS NULL',
					$today,
					MemberRole::LEARNER->value,
					Db::table( 'org_members' ),
					$org_id
				)->add( $scope->unit_where( $org_id, 'location_id', OrgScope::REPORT_MASK ) )->add( '
					GROUP BY job_role_id, location_id, status, rl_expired, rl_learner' )
			);
		}
		$members  = 0;
		$learners = 0;
		$roles    = array();
		$places   = array();
		$staff    = array_fill_keys( StaffStatus::ALL, 0 );
		foreach ( $groups as $row ) {
			$n = (int) $row['n'];
			if ( 2 === (int) ( $row['rl_kind'] ?? 0 ) ) {
				$places[ (int) $row['location_id'] ] = $n;
				continue;
			}
			$members       += $n;
			$learners      += 1 === (int) $row['rl_learner'] ? $n : 0;
			$role           = (int) $row['job_role_id'];
			$roles[ $role ] = ( $roles[ $role ] ?? 0 ) + $n;
			if ( ! $multi ) {
				$place            = (int) $row['location_id'];
				$places[ $place ] = ( $places[ $place ] ?? 0 ) + $n;
			}
			$status         = MemberStatus::tryFrom( (int) $row['status'] ) ?? MemberStatus::ACTIVE;
			$slug           = MemberStatus::ACTIVE === $status && 1 === (int) $row['rl_expired'] ? StaffStatus::EXPIRED : $status->slug();
			$staff[ $slug ] = ( $staff[ $slug ] ?? 0 ) + $n;
		}
		$name_lang = self::name_lang( $lang );
		return array(
			'counts'          => new ReportCounts( $totals['assigned'], $totals['not_started'], $totals['in_progress'], $totals['completed'] ),
			'by_course'       => $by_course,
			'members'         => $members,
			'learners'        => $learners,
			'by_job_role'     => self::named( $roles, Services::job_role_repo()->names( array_keys( $roles ), $name_lang ), __( 'No job role', 'rooster-lms' ) ),
			'by_location'     => self::named( $places, self::location_names( $org_id, array_keys( $places ), $name_lang ), UnitLabels::member_column( $org_id, $lang )[3] ),
			'by_staff_status' => $staff,
		);
	}

	public static function course_items( array $course_ids ): array {
		$course_ids = array_slice( CourseRepository::ids( $course_ids ), 0, 50 );
		$out        = array_fill_keys( $course_ids, array() );
		if ( array() === $course_ids ) {
			return $out;
		}
		$rows = Db::rows(

				'SELECT i.id, i.course_id, i.unit_id FROM %i i
				JOIN %i u ON u.id = i.unit_id AND u.state = 1
				LEFT JOIN %i s ON s.id = i.section_id
				WHERE i.course_id IN (' . implode( ',', array_fill( 0, count( $course_ids ), '%d' ) ) . ') AND i.state = 1
				ORDER BY i.course_id, CASE WHEN i.section_id IS NULL THEN 0 ELSE 1 END, s.position, s.id, i.position, i.id',
				array_merge( array( Db::table( 'items' ), Db::table( 'units' ), Db::table( 'sections' ) ), $course_ids )

		);
		foreach ( $rows as $row ) {
			$out[ (int) $row['course_id'] ][] = array(
				'id'      => (int) $row['id'],
				'unit_id' => (int) $row['unit_id'],
			);
		}
		return $out;
	}

	public static function templates( OrgScope $scope, int $org_id ): array {
		$scope->require_manage( $org_id );
		$out  = array(
			'instances' => array(),
			'masters'   => array(),
		);
		$rows = Db::rows( 'SELECT * FROM %i WHERE org_id IN (0, %d) ORDER BY name, id', Db::table( 'access_templates' ), $org_id );
		foreach ( $rows as $row ) {
			$template = AccessTemplate::from_row( $row );
			$out[ $template->is_master() ? 'masters' : 'instances' ][] = $template;
		}
		return $out;
	}

	public static function rules( int $template_id, array $course_ids ): array {
		$course_ids = array_slice( CourseRepository::ids( $course_ids ), 0, 50 );
		$out        = array();
		if ( $template_id <= 0 || array() === $course_ids ) {
			return $out;
		}
		$rows = Db::rows(

				'SELECT job_role_id, course_id, item_id FROM %i WHERE template_id = %d AND course_id IN (' . implode( ',', array_fill( 0, count( $course_ids ), '%d' ) ) . ')',
				array_merge( array( Db::table( 'access_rules' ), $template_id ), $course_ids )

		);
		foreach ( $rows as $row ) {
			$out[ (int) $row['job_role_id'] . ':' . (int) $row['course_id'] . ':' . (int) $row['item_id'] ] = true;
		}
		return $out;
	}

	public static function matrix_categories( array $course_ids, string $lang ): array {
		$course_ids = CourseRepository::ids( $course_ids );
		$out        = array(
			'paths'      => array(),
			'categories' => array(),
		);
		if ( array() === $course_ids ) {
			return $out;
		}
		global $wpdb;
		$default = Providers::current()->default_language();
		$rows    = Db::rows(

				'SELECT co.object_id, f.id AS filing, f.tree_rank AS rank_f, c.id, c.parent_id, c.depth, c.tree_rank, ct.lang, t.name
				FROM %i co
				JOIN %i f ON f.id = co.category_id AND f.state = 1
				JOIN %i p ON p.descendant_id = co.category_id
				JOIN %i c ON c.id = p.ancestor_id AND c.state = 1 AND c.depth <= 1
				LEFT JOIN %i ct ON ct.category_id = c.id AND ct.lang IN (%s, %s, %s)
				LEFT JOIN %i t ON t.term_id = ct.term_id
				WHERE co.object_type = %d AND co.object_id IN (' . implode( ',', array_fill( 0, count( $course_ids ), '%d' ) ) . ')
				ORDER BY co.object_id, f.tree_rank, f.id, c.depth',
				array_merge(
					array( Db::table( 'category_objects' ), Db::table( 'categories' ), Db::table( 'category_paths' ), Db::table( 'categories' ), Db::table( 'category_terms' ), $lang, $default, '', $wpdb->terms, ObjectType::COURSE->value ),
					$course_ids
				)

		);
		$names   = array();
		$filing  = array();
		$order   = array();
		foreach ( $rows as $row ) {
			$id     = (int) $row['id'];
			$course = (int) $row['object_id'];
			$rank   = null === $row['lang'] ? 9 : ( $lang === $row['lang'] ? 0 : ( $default === $row['lang'] ? 1 : 2 ) );
			if ( null !== $row['name'] && ( ! isset( $names[ $id ] ) || $rank < $names[ $id ][0] ) ) {
				$names[ $id ] = array( $rank, (string) $row['name'] );
			}
			$order[ $id ] = array( (int) $row['tree_rank'], (int) $row['parent_id'], (int) $row['depth'] );
			if ( ! isset( $filing[ $course ] ) ) {
				$filing[ $course ]       = (int) $row['filing'];
				$out['paths'][ $course ] = array(
					'top' => 0,
					'sub' => 0,
				);
			}
			if ( $filing[ $course ] === (int) $row['filing'] ) {
				$out['paths'][ $course ][ 0 === (int) $row['depth'] ? 'top' : 'sub' ] = $id;
			}
		}
		uasort( $order, static fn( array $a, array $b ): int => $a[0] <=> $b[0] );
		foreach ( $order as $id => $info ) {
			$out['categories'][ $id ] = array(
				'name'   => isset( $names[ $id ] ) ? wp_specialchars_decode( $names[ $id ][1], ENT_QUOTES ) : '',
				'parent' => $info[1],
				'depth'  => $info[2],
			);
		}
		return $out;
	}

	public static function learner_names( int $org_id, int $limit = 200, ?OrgScope $scope = null ): ?array {
		global $wpdb;
		$scope = $scope ?? OrgScope::for_user( get_current_user_id() );
		$rows = Db::rows(
			SqlPart::of(
				'SELECT t.user_id, t.display_name FROM ( SELECT m.user_id, u.display_name FROM %i m JOIN %i u ON u.ID = m.user_id
				WHERE m.org_id = %d AND m.left_at IS NULL AND ( m.roles & %d ) <> 0',
				Db::table( 'org_members' ),
				$wpdb->users,
				$org_id,
				MemberRole::LEARNER->value
			)->add( Branch::where( $scope, $org_id, 'm', OrgScope::REPORT_MASK ) )->add( SqlPart::of( ' LIMIT %d ) t ORDER BY t.display_name, t.user_id', $limit + 1 ) )
		);
		if ( count( $rows ) > $limit ) {
			return null;
		}
		$out = array();
		foreach ( $rows as $row ) {
			$out[ (int) $row['user_id'] ] = (string) $row['display_name'];
		}
		return $out;
	}

	public static function assessment_placements( int $org_id, string $lang, int $limit = 50, ?OrgScope $scope = null, int $offset = 0 ): array {
		global $wpdb;
		$scope  = $scope ?? OrgScope::for_user( get_current_user_id() );
		$within = $scope->unit_scope( $org_id, OrgScope::REPORT_MASK );
		$members = null === $within ? SqlPart::of( '' ) : SqlPart::of( ' AND user_id IN (SELECT rlm.user_id FROM %i rlm WHERE rlm.org_id = %d AND rlm.left_at IS NULL', Db::table( 'org_members' ), $org_id )->add( Branch::where( $scope, $org_id, 'rlm', OrgScope::REPORT_MASK ) )->add( ')' );
		$rows    = Db::rows( 'SELECT i.id AS item_id, i.course_id, i.unit_id, u.pass_percent, u.max_attempts, u.time_limit_sec, u.settings, u.quiz_mode FROM %i oc JOIN %i i ON i.course_id = oc.course_id AND i.state = 1 JOIN %i u ON u.id = i.unit_id AND u.unit_type = %d WHERE oc.org_id = %d ORDER BY i.id LIMIT %d OFFSET %d', Db::table( 'org_catalogue' ), Db::table( 'items' ), Db::table( 'units' ), UnitType::QUIZ->value, $org_id, $limit, max( 0, $offset ) );
		if ( array() === $rows ) {
			return array();
		}
		$ids   = array_map( static fn( array $r ): int => (int) $r['item_id'], $rows );
		$units = array_map( static fn( array $r ): int => (int) $r['unit_id'], $rows );
		foreach ( $rows as $row ) {
			Services::quizzes()->prime( (int) $row['unit_id'], QuizService::from_row( $row ) );
		}
		Services::curriculum_repo()->items( $ids );
		Services::policy_repo()->prime_org( $ids, $org_id );
		$sealed = ReleaseService::sealed_by_items( $org_id, $units, $members );
		$titles = Services::posts()->titles( ObjectType::UNIT, $units, $lang );
		$out    = array();
		foreach ( $rows as $row ) {
			$item_id   = (int) $row['item_id'];
			$effective = Services::policy_resolver()->for_scope( $item_id, $org_id, 0 )->to_array();
			$out[]     = array(
				'item_id'         => $item_id,
				'course_id'       => (int) $row['course_id'],
				'quiz_title'      => (string) ( $titles[ (int) $row['unit_id'] ]['title'] ?? '' ),
				'effective'       => $effective,
				'sources'         => $effective['sources'],
				'sealed'          => (int) ( $sealed[ $item_id ]['count'] ?? 0 ),
				'next_release_at' => $sealed[ $item_id ]['next'] ?? null,
			);
		}
		return $out;
	}

	public static function assessment_courses( int $org_id ): array {
		$out = array();
		foreach ( Db::rows( 'SELECT i.course_id, COUNT(*) AS n FROM %i oc JOIN %i i ON i.course_id = oc.course_id AND i.state = 1 JOIN %i u ON u.id = i.unit_id AND u.unit_type = %d WHERE oc.org_id = %d GROUP BY i.course_id ORDER BY i.course_id', Db::table( 'org_catalogue' ), Db::table( 'items' ), Db::table( 'units' ), UnitType::QUIZ->value, $org_id ) as $row ) {
			$out[ (int) $row['course_id'] ] = (int) $row['n'];
		}
		return $out;
	}

	public static function accommodations( int $org_id, int $limit = 500, ?OrgScope $scope = null, int $offset = 0 ): array {
		global $wpdb;
		$rows = Db::rows( SqlPart::of( 'SELECT m.user_id, u.display_name, p.time_factor ' )->add( self::accommodations_from( $org_id, $scope ) )->add( SqlPart::of( ' ORDER BY u.display_name, m.user_id LIMIT %d OFFSET %d', $limit, max( 0, $offset ) ) ) );
		$out  = array();
		foreach ( $rows as $row ) {
			$out[ (int) $row['user_id'] ] = array(
				'name'   => (string) $row['display_name'],
				'factor' => (int) $row['time_factor'],
			);
		}
		return $out;
	}

	public static function accommodations_total( int $org_id, ?OrgScope $scope = null ): int {
		return (int) Db::value( SqlPart::of( 'SELECT COUNT(*) ' )->add( self::accommodations_from( $org_id, $scope ) ) );
	}

	private static function accommodations_from( int $org_id, ?OrgScope $scope ): SqlPart {
		global $wpdb;
		$scope = $scope ?? OrgScope::for_user( get_current_user_id() );
		return SqlPart::of(
			'FROM %i m JOIN %i p ON p.user_id = m.user_id JOIN %i u ON u.ID = m.user_id WHERE m.org_id = %d AND m.left_at IS NULL AND p.time_factor <> 100',
			Db::table( 'org_members' ),
			Db::table( 'people' ),
			$wpdb->users,
			$org_id
		)->add( Branch::where( $scope, $org_id, 'm', OrgScope::MANAGE_MASK ) );
	}

	public static function name_lang( string $lang ): string {
		return strtolower( $lang ) === strtolower( Providers::current()->default_language() ) ? '' : $lang;
	}

	public static function location_names( int $org_id, array $ids, string $lang ): array {
		return UnitLabels::paths( $org_id, $ids, $lang );
	}

	private static function named( array $counts, array $names, string $none ): array {
		$out  = array();
		$tail = null;
		foreach ( $counts as $id => $n ) {
			$row = array(
				'id'      => (int) $id,
				/* translators: %d: ID of a job role or location that no longer exists. */
				'name'    => 0 === (int) $id ? $none : ( $names[ $id ] ?? sprintf( __( 'Removed #%d', 'rooster-lms' ), $id ) ),
				'members' => $n,
			);
			if ( 0 === (int) $id ) {
				$tail = $row;
			} else {
				$out[] = $row;
			}
		}
		usort( $out, static fn( array $a, array $b ): int => array( Fold::fold( $a['name'] ), $a['id'] ) <=> array( Fold::fold( $b['name'] ), $b['id'] ) );
		if ( null !== $tail ) {
			$out[] = $tail;
		}
		return $out;
	}
}