<?php

namespace RoosterLMS\Events;

use RoosterLMS\Content\CourseVisibility;
use RoosterLMS\Content\Delivery;
use RoosterLMS\Content\EnrolPolicy;
use RoosterLMS\Content\ObjectState;
use RoosterLMS\Content\ObjectType;
use RoosterLMS\Data\Db;
use RoosterLMS\Data\SqlPart;
use RoosterLMS\Domain\Services;
use RoosterLMS\Invitations\InvitationKind;
use RoosterLMS\Invitations\InvitationState;
use RoosterLMS\Invitations\InviteeState;
use RoosterLMS\Invitations\Tokens as RsvpTokens;
use RoosterLMS\Live\Calendar\CalendarColours;
use RoosterLMS\Live\Occurrence;
use RoosterLMS\Live\Placement;
use RoosterLMS\Live\Session;
use RoosterLMS\Live\SessionState;
use RoosterLMS\Orgs\OrgStatus;
use RoosterLMS\Venues\Venue;

defined( 'ABSPATH' ) || exit;

final class EventRepository {

	public const GUEST = 'guest';

	public const VIEWER = 'viewer';

	public const FEED_USER = 'feed';

	public const FEED_PAST_DAYS = 30;

	private const MAX_DURATION_MIN = 1440;

	public static function find( EventQuery $q, int $user_id, \DateTimeImmutable $now, string $who = self::GUEST, bool $feed = false, int $limit = 0, int $offset = -1 ): array {
		global $wpdb;
		$user_id = self::GUEST === $who ? 0 : max( 0, $user_id );
		$window  = $q->window( $now );
		$limit   = $limit > 0 ? $limit : $q->per_page + 1;
		$offset  = $offset >= 0 ? $offset : ( $q->page - 1 ) * $q->per_page;
		$where   = self::where( $q, $user_id, $who, $window, $feed );
		if ( null === $where ) {
			return array();
		}
		$select = SqlPart::of( 's.id, s.unit_id, s.placement, s.course_id, s.provider, s.provider_ref, s.eroom_post_id, s.timezone, s.starts_local, s.starts_at, s.duration_min, s.rrule, s.ends_at,'
			. " '' AS join_url, '' AS host_url, '' AS password, '' AS stream_url, '' AS studio_url,"
			. ' s.join_before_min, s.orgs_only, s.options, s.state, s.created_by, s.created_at, s.updated_at, s.venue_id, s.delivery, s.capacity,'
			. ' o.id AS o_id, o.starts_at AS o_starts_at, o.ends_at AS o_ends_at, o.state AS o_state, o.phase AS o_phase,'
			. ' c.visibility AS c_visibility, c.enrol_policy AS c_policy, c.capacity AS c_capacity, cst.learners AS c_learners,'
			. ' vn.id AS v_id, vn.name AS v_name, vn.address AS v_address, vn.city AS v_city, vn.country AS v_country, vn.map_url AS v_map' );
		$select = $select->add( SqlPart::of( ', ' )->add( $user_id > 0 ? SqlPart::of( '( CASE WHEN ' )->add( Services::live_visibility()->sql_for_user( $user_id, 's' ) )->add( ' THEN 1 ELSE 0 END )' ) : SqlPart::of( '0' ) )->add( ' AS may_join' ) );
		$select = $select->add( SqlPart::of( ', ( CASE WHEN ' )->add( $user_id > 0 ? self::orgs_sql( $user_id ) : SqlPart::of( 's.orgs_only = 0' ) )->add( ' THEN 1 ELSE 0 END ) AS open_to_viewer' ) );
		$select = $select->add( SqlPart::of( ', ' )->add( $user_id > 0 && ! $feed ? self::rsvp_sql( $user_id ) : SqlPart::of( "''" ) )->add( ' AS rsvp' ) );
		$select = $select->add( SqlPart::of( ', ' )->add( $feed ? SqlPart::of( "''" ) : CalendarColours::category_sql( 's' ) )->add( ' AS c_cat' ) );
		if ( $q->series ) {
			$more    = SqlPart::of( 'FROM %i o3 WHERE o3.session_id = o.session_id AND o3.state = %d AND o3.starts_at > o.starts_at AND o3.starts_at < %s', Db::table( 'live_occurrences' ), Occurrence::SCHEDULED, $window[1] );
			$select = $select->add( SqlPart::of( ', ( SELECT COUNT(*) ' )->add( $more )->add( ' ) AS more_count, ( SELECT SUBSTRING_INDEX( GROUP_CONCAT( o3.starts_at ORDER BY o3.starts_at SEPARATOR \',\' ), \',\', 3 ) ' )->add( $more )->add( ' ) AS more_dates' ) );
		}
		$sql  = SqlPart::of( 'SELECT /* outside-tx */ ' )->add( $select )->add( SqlPart::of(
			' FROM %i o JOIN %i s ON s.id = o.session_id JOIN %i c ON c.id = s.course_id JOIN %i it ON it.course_id = s.course_id AND it.unit_id = s.unit_id AND it.state = 1 LEFT JOIN %i cst ON cst.course_id = c.id LEFT JOIN %i vn ON vn.id = COALESCE( s.venue_id, CASE WHEN s.delivery <> %d THEN c.venue_id END ) AND vn.state = %d',
			Db::table( 'live_occurrences' ),
			Db::table( 'live_sessions' ),
			Db::table( 'courses' ),
			Db::table( 'items' ),
			Db::table( 'course_stats' ),
			Db::table( 'venues' ),
			Delivery::ONLINE->value,
			Venue::ACTIVE
		) )->add( ' WHERE ' )->add( $where )->add( SqlPart::of( ' ORDER BY o.starts_at, o.id LIMIT %d OFFSET %d', $limit, $offset ) );
		$rows = Db::rows( $sql );
		$out  = array();
		foreach ( $rows as $row ) {
			$out[] = self::event( $row );
		}
		return $out;
	}

	private static function where( EventQuery $q, int $user_id, string $who, array $window, bool $feed ): ?SqlPart {
		global $wpdb;
		$visible = self::visible_sql( $user_id, $who );
		if ( null === $visible ) {
			return null;
		}
		$parts   = array();
		$parts[] = $feed
			? SqlPart::of( 's.state IN (%d, %d) AND o.state IN (%d, %d)', SessionState::ACTIVE->value, SessionState::CANCELLED->value, Occurrence::SCHEDULED, Occurrence::CANCELLED )
			: SqlPart::of( 's.state = %d AND o.state = %d', SessionState::ACTIVE->value, Occurrence::SCHEDULED );
		$parts[] = SqlPart::of( 's.placement IN (%d, %d) AND c.state = %d', Placement::LESSON->value, Placement::ACTIVITY->value, ObjectState::ACTIVE->value );
		$parts[] = self::span_sql( 'o', $window, $feed );
		$parts[] = SqlPart::of( "EXISTS ( SELECT 1 FROM %i uop JOIN %i up ON up.ID = uop.post_id AND up.post_status = 'publish' WHERE uop.object_type = %d AND uop.object_id = s.unit_id )", Db::table( 'object_posts' ), $wpdb->posts, ObjectType::UNIT->value );
		$parts[] = $visible;
		if ( $q->series ) {
			$parts[] = SqlPart::of( 'o.starts_at = ( SELECT MIN( o2.starts_at ) FROM %i o2 WHERE o2.session_id = o.session_id AND o2.state = %d AND ', Db::table( 'live_occurrences' ), Occurrence::SCHEDULED )->add( self::span_sql( 'o2', $window, false ) )->add( ' )' );
		}
		if ( array() !== $q->term_ids ) {
			$parts[] = self::terms_sql( $q->term_ids );
		}
		if ( $q->filter_term > 0 ) {
			$parts[] = self::terms_sql( array( $q->filter_term ) );
		}
		if ( array() !== $q->course_posts ) {
			$parts[] = SqlPart::of( 's.course_id IN ( SELECT cop.object_id FROM %i cop WHERE cop.object_type = %d AND cop.post_id IN (' . implode( ',', array_fill( 0, count( $q->course_posts ), '%d' ) ) . ') )', array_merge( array( Db::table( 'object_posts' ), ObjectType::COURSE->value ), $q->course_posts ) );
		}
		if ( $q->course_id > 0 ) {
			$parts[] = SqlPart::of( 's.course_id = %d', $q->course_id );
		}
		if ( $q->unit_id > 0 ) {
			$parts[] = SqlPart::of( 's.unit_id = %d', $q->unit_id );
		}
		if ( $q->occurrence > 0 ) {
			$parts[] = SqlPart::of( 'o.id = %d', $q->occurrence );
		}
		$delivery = Delivery::from_slug( $q->delivery );
		if ( '' !== $q->delivery && null !== $delivery ) {
			$parts[] = SqlPart::of( 's.delivery = %d', $delivery->value );
		}
		if ( '' !== $q->city ) {
			$parts[] = SqlPart::of( 'vn.city = %s', $q->city );
		}
		if ( $q->trainer > 0 ) {
			$parts[] = SqlPart::of( 'EXISTS ( SELECT 1 FROM %i stf WHERE stf.course_id = s.course_id AND stf.user_id = %d )', Db::table( 'course_staff' ), $q->trainer );
		}
		return SqlPart::join( ' AND ', $parts );
	}

	private static function span_sql( string $alias, array $window, bool $feed ): SqlPart {
		global $wpdb;
		$now  = new \DateTimeImmutable( $window[2], new \DateTimeZone( 'UTC' ) );
		$ends = $feed ? $now->modify( '-' . self::FEED_PAST_DAYS . ' days' ) : $now;
		$low  = $ends->modify( '-' . self::MAX_DURATION_MIN . ' minutes' )->format( 'Y-m-d H:i:s' );
		$sql  = SqlPart::of( '%i.ends_at > %s AND %i.starts_at > %s AND %i.starts_at < %s', $alias, $ends->format( 'Y-m-d H:i:s' ), $alias, $low, $alias, $window[1] );
		if ( '' !== $window[0] ) {
			$sql = $sql->add( SqlPart::of( ' AND %i.starts_at >= %s', $alias, $window[0] ) );
		}
		return $sql;
	}

	public static function visible_sql( int $user_id, string $who ): ?SqlPart {
		global $wpdb;
		$catalogue = Services::visibility();
		if ( $user_id <= 0 || self::GUEST === $who ) {
			$course = $catalogue->course_sql( $catalogue->viewer( 0 ), 'c', false );
			return '1 = 0' === $course->text() ? null : SqlPart::of( '( ' )->add( $course )->add( ' AND s.orgs_only = 0 )' );
		}
		$members = SqlPart::of(
			'c.id IN ( SELECT eoc.course_id FROM %i eoc JOIN %i eom ON eom.org_id = eoc.org_id AND eom.user_id = %d AND eom.left_at IS NULL JOIN %i eog ON eog.id = eoc.org_id AND eog.status = %d )',
			Db::table( 'org_catalogue' ),
			Db::table( 'org_members' ),
			$user_id,
			Db::table( 'orgs' ),
			OrgStatus::ACTIVE->value
		);
		if ( self::FEED_USER === $who ) {
			$enrolled = SqlPart::of(
				'c.id IN ( SELECT fe.course_id FROM %i fe WHERE fe.user_id = %d AND fe.status = 1 AND ( fe.access_until IS NULL OR fe.access_until > %s ) )',
				Db::table( 'enrolments' ),
				$user_id,
				\RoosterLMS\Core\Clock::now()
			);
			$course   = SqlPart::of( '( c.visibility = %d OR ', CourseVisibility::PUBLIC->value )->add( $enrolled )->add( SqlPart::of( ' OR ( c.visibility = %d AND ', CourseVisibility::ORG_ONLY->value ) )->add( $members )->add( ' ) )' );
		} else {
			$course = SqlPart::of( '( ' )->add( $catalogue->course_sql( $catalogue->viewer( $user_id ), 'c', false ) )->add( SqlPart::of( ' OR ( c.visibility = %d AND ', CourseVisibility::ORG_ONLY->value ) )->add( $members )->add( ' ) )' );
		}
		return SqlPart::of( '( ' )->add( $course )->add( ' AND ' )->add( self::orgs_sql( $user_id ) )->add( ' )' );
	}

	public static function showcase_sql( int $user_id, string $who ): ?SqlPart {
		global $wpdb;
		$visible = self::visible_sql( $user_id, $who );
		if ( null === $visible ) {
			return null;
		}
		return SqlPart::of( '( s.placement IN (%d, %d) AND c.state = %d AND EXISTS ( SELECT 1 FROM %i it WHERE it.course_id = s.course_id AND it.unit_id = s.unit_id AND it.state = 1 )', Placement::LESSON->value, Placement::ACTIVITY->value, ObjectState::ACTIVE->value, Db::table( 'items' ) )->add( SqlPart::of( " AND EXISTS ( SELECT 1 FROM %i uop JOIN %i up ON up.ID = uop.post_id AND up.post_status = 'publish' WHERE uop.object_type = %d AND uop.object_id = s.unit_id )", Db::table( 'object_posts' ), $wpdb->posts, ObjectType::UNIT->value ) )->add( ' AND ' )->add( $visible )->add( ' )' );
	}

	private static function orgs_sql( int $user_id ): SqlPart {
		global $wpdb;
		return SqlPart::of( '( s.orgs_only = 0 OR EXISTS ( SELECT 1 FROM %i elo JOIN %i elm ON elm.org_id = elo.org_id AND elm.user_id = %d AND elm.left_at IS NULL WHERE elo.session_id = s.id ) )', Db::table( 'live_session_orgs' ), Db::table( 'org_members' ), $user_id );
	}

	public static function terms_sql( array $term_ids ): SqlPart {
		global $wpdb;
		return SqlPart::of(
			'EXISTS ( SELECT 1 FROM %i eco JOIN %i ecp ON ecp.descendant_id = eco.category_id JOIN %i ect ON ect.category_id = ecp.ancestor_id WHERE eco.object_type = %d AND eco.object_id = c.id AND ect.term_id IN (' . implode( ',', array_fill( 0, count( $term_ids ), '%d' ) ) . ') )',
			array_merge( array( Db::table( 'category_objects' ), Db::table( 'category_paths' ), Db::table( 'category_terms' ), ObjectType::COURSE->value ), $term_ids )
		);
	}

	private static function rsvp_sql( int $user_id ): SqlPart {
		$user = get_current_user_id() === $user_id ? wp_get_current_user() : get_userdata( $user_id );
		if ( ! $user instanceof \WP_User || ! $user->exists() ) {
			return SqlPart::of( "''" );
		}
		global $wpdb;
		$hash   = RsvpTokens::email_hash( (string) $user->user_email );
		$proven = \RoosterLMS\Orgs\VerifiedEmail::woocommerce_verified( $user_id ) ? SqlPart::of( '1' ) : SqlPart::of( 'EXISTS ( SELECT 1 FROM %i pv WHERE pv.user_id = %d AND pv.email_verified = %s )', Db::table( 'people' ), $user_id, $hash );
		return SqlPart::of(
			"( SELECT CONCAT( ri.id, ':', COALESCE( ri.token_seed, '' ), ':', ri.token_hash ) FROM %i ri JOIN %i rv ON rv.id = ri.invitation_id WHERE rv.course_id = s.course_id AND rv.unit_id = s.unit_id AND ( rv.occurrence_id = o.id OR rv.occurrence_id = 0 ) AND rv.kind = %d AND rv.state <> %d AND ri.state = %d AND ri.token_hash IS NOT NULL AND ( ri.user_id = %d OR ( ri.user_id = 0 AND ri.email_hash = %s AND ",
			Db::table( 'invitees' ),
			Db::table( 'invitations' ),
			InvitationKind::RSVP->value,
			InvitationState::CANCELLED->value,
			InviteeState::SENT->value,
			$user_id,
			$hash
		)->add( $proven )->add( ' ) ) ORDER BY rv.id DESC LIMIT 1 )' );
	}

	private static function event( array $row ): Event {
		$session  = Session::from_row( $row );
		$o        = new Occurrence( (int) $row['o_id'], $session->id, (string) $row['o_starts_at'], (string) $row['o_ends_at'], '', (int) $row['o_state'], \RoosterLMS\Live\Phase::tryFrom( (int) ( $row['o_phase'] ?? 1 ) ) ?? \RoosterLMS\Live\Phase::SCHEDULED );
		$capacity = (int) $row['c_capacity'];
		$venue    = null;
		if ( null !== $row['v_id'] && (int) $row['v_id'] > 0 ) {
			$venue = array(
				'id'      => (int) $row['v_id'],
				'name'    => (string) $row['v_name'],
				'address' => (string) $row['v_address'],
				'city'    => (string) $row['v_city'],
				'country' => (string) $row['v_country'],
				'map_url' => (string) $row['v_map'],
			);
		}
		$more = array();
		foreach ( explode( ',', (string) ( $row['more_dates'] ?? '' ) ) as $start ) {
			if ( 1 === preg_match( '/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/', $start ) ) {
				$more[] = $start;
			}
		}
		return new Event(
			$o,
			$session,
			$session->course_id,
			CourseVisibility::tryFrom( (int) $row['c_visibility'] ) ?? CourseVisibility::PUBLIC,
			EnrolPolicy::tryFrom( (int) $row['c_policy'] ) ?? EnrolPolicy::GRANTED,
			$capacity > 0 ? max( 0, $capacity - (int) ( $row['c_learners'] ?? 0 ) ) : null,
			$venue,
			1 === (int) $row['may_join'],
			(string) ( $row['rsvp'] ?? '' ),
			$more,
			max( 0, (int) ( $row['more_count'] ?? 0 ) ),
			1 === (int) ( $row['open_to_viewer'] ?? 1 ),
			CalendarColours::category_of( $row['c_cat'] ?? null )
		);
	}
}