<?php

namespace RoosterLMS\Invitations;

use RoosterLMS\Data\Db;
use RoosterLMS\Data\SqlPart;

defined( 'ABSPATH' ) || exit;

final class InvitationRepository {

	private static array $memo = array();

	private static array $report = array();

	public static function get( int $id ): ?Invitation {
		if ( $id <= 0 ) {
			return null;
		}
		if ( isset( self::$memo[ $id ] ) ) {
			return self::$memo[ $id ];
		}
		$row = Db::row( 'SELECT /* outside-tx */ * FROM %i WHERE id = %d', Db::table( 'invitations' ), $id );
		if ( null === $row ) {
			return null;
		}
		self::$memo[ $id ] = Invitation::from_row( $row );
		return self::$memo[ $id ];
	}

	public static function for_report( int $id ): ?array {
		if ( $id <= 0 ) {
			return null;
		}
		if ( isset( self::$report[ $id ] ) ) {
			return self::$report[ $id ];
		}
		global $wpdb;
		$count = static fn( SqlPart $where ): SqlPart => SqlPart::of( '( SELECT COUNT(*) FROM %i x WHERE x.invitation_id = v.id', Db::table( 'invitees' ) )->add( $where )->add( ' )' );
		$sql   = SqlPart::of( 'SELECT /* outside-tx */ v.*, c.home_post_id AS rl_home_post, ' )->add( $count( SqlPart::of( '' ) ) )->add( ' AS rl_total' );
		foreach ( RsvpAnswer::cases() as $answer ) {
			$sql = $sql->add( ', ' )->add( $count( SqlPart::of( ' AND x.answer = %d', $answer->value ) ) )->add( ' AS rl_a' . (int) $answer->value );
		}
		$row = Db::row( $sql->add( SqlPart::of( ' FROM %i v LEFT JOIN %i c ON c.id = v.course_id WHERE v.id = %d', Db::table( 'invitations' ), Db::table( 'courses' ), $id ) ) );
		if ( null === $row ) {
			return null;
		}
		$answers = array();
		foreach ( RsvpAnswer::cases() as $answer ) {
			$answers[ $answer->value ] = (int) $row[ 'rl_a' . $answer->value ];
		}
		$inv               = Invitation::from_row( $row );
		self::$memo[ $id ] = $inv;
		$home              = (int) $row['rl_home_post'];
		if ( $home > 0 ) {
			\RoosterLMS\Domain\Services::course_repo()->remember_posts( array( $home => $inv->course_id ) );
		}
		self::$report[ $id ] = array(
			'invitation'   => $inv,
			'home_post_id' => $home,
			'total'        => (int) $row['rl_total'],
			'answers'      => $answers,
		);
		return self::$report[ $id ];
	}

	public static function forget( int $id = 0 ): void {
		InvitationTexts::forget();
		if ( $id > 0 ) {
			unset( self::$memo[ $id ], self::$report[ $id ] );
			return;
		}
		self::$memo   = array();
		self::$report = array();
	}

	public static function for_target( int $course_id, int $unit_id = 0 ): array {
		global $wpdb;
		$sql = $unit_id > 0
			? SqlPart::of( 'SELECT /* outside-tx */ * FROM %i WHERE unit_id = %d AND course_id = %d AND kind <> %d ORDER BY id DESC LIMIT 50', Db::table( 'invitations' ), $unit_id, $course_id, InvitationKind::INTEREST->value )
			: SqlPart::of( 'SELECT /* outside-tx */ * FROM %i WHERE course_id = %d AND kind <> %d ORDER BY id DESC LIMIT 50', Db::table( 'invitations' ), $course_id, InvitationKind::INTEREST->value );
		$out = array();
		foreach ( Db::rows( $sql ) as $row ) {
			$invitation                    = Invitation::from_row( $row );
			self::$memo[ $invitation->id ] = $invitation;
			$out[]                         = $invitation;
		}
		return $out;
	}

	public static function for_announcement( int $announcement_id, int $course_id ): ?Invitation {
		$row = Db::row( 'SELECT /* outside-tx */ * FROM %i WHERE course_id = %d AND kind = %d AND announcement_id = %d LIMIT 1', Db::table( 'invitations' ), $course_id, InvitationKind::ANNOUNCEMENT->value, $announcement_id );
		return null === $row ? null : Invitation::from_row( $row );
	}

	public static function invitee( int $id ): ?array {
		if ( $id <= 0 ) {
			return null;
		}
		return Db::row( 'SELECT /* outside-tx */ * FROM %i WHERE id = %d', Db::table( 'invitees' ), $id );
	}

	public static function counts( Invitation $inv ): array {
		global $wpdb;
		$row = Db::row( SqlPart::of( 'SELECT /* outside-tx */ ' )->add( self::counts_select( SqlPart::of( '%d', $inv->version ) ) )->add( SqlPart::of( ' FROM %i x WHERE x.invitation_id = %d', Db::table( 'invitees' ), $inv->id ) ) ) ?? array();
		return self::counts_row( $row );
	}

	public static function counts_many( array $ids ): array {
		$ids = array_values( array_unique( array_filter( array_map( 'intval', $ids ), static fn( int $id ): bool => $id > 0 ) ) );
		$out = array();
		foreach ( $ids as $id ) {
			$out[ $id ] = self::counts_row( array() );
		}
		if ( array() === $ids ) {
			return $out;
		}
		global $wpdb;
		$sql  = SqlPart::of( 'SELECT /* outside-tx */ x.invitation_id, ' )->add( self::counts_select( SqlPart::of( 'v.version' ) ) )->add( SqlPart::of( ' FROM %i x JOIN %i v ON v.id = x.invitation_id WHERE x.invitation_id IN (' . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ') GROUP BY x.invitation_id', array_merge( array( Db::table( 'invitees' ), Db::table( 'invitations' ) ), $ids ) ) );
		$rows = Db::rows( $sql );
		foreach ( $rows as $row ) {
			$out[ (int) $row['invitation_id'] ] = self::counts_row( $row );
		}
		return $out;
	}

	private static function counts_select( SqlPart $version ): SqlPart {
		global $wpdb;
		return SqlPart::of( 'COUNT(*) AS total, SUM(x.state = %d) AS sent, SUM(x.state = %d AND x.sent_version >= ', InviteeState::SENT->value, InviteeState::SENT->value )->add( $version )->add( SqlPart::of(
				') AS sent_current, SUM(x.state = %d) AS queued, SUM(x.state = %d) AS failed, SUM(x.state = %d) AS suppressed, SUM(x.answer = %d) AS yes, SUM(x.answer = %d) AS no, SUM(x.answer = %d) AS maybe, SUM(x.outcome > 0) AS attended, SUM(x.answer = %d AND x.outcome > 0) AS converted, SUM(x.sent_version > 0) AS f_invited, SUM(x.sent_version > 0 AND x.answer = %d) AS f_yes, SUM(x.sent_version > 0 AND x.answer = %d) AS f_no, SUM(x.sent_version > 0 AND x.answer = %d) AS f_maybe, SUM(x.sent_version > 0 AND x.outcome > 0) AS f_attended, SUM(x.sent_version > 0 AND x.answer = %d AND x.outcome > 0) AS f_converted',
				InviteeState::QUEUED->value,
				InviteeState::FAILED->value,
				InviteeState::SUPPRESSED->value,
				RsvpAnswer::YES->value,
				RsvpAnswer::NO->value,
				RsvpAnswer::MAYBE->value,
				RsvpAnswer::YES->value,
				RsvpAnswer::YES->value,
				RsvpAnswer::NO->value,
				RsvpAnswer::MAYBE->value,
				RsvpAnswer::YES->value
			) );
	}

	private static function counts_row( array $row ): array {
		$total = (int) ( $row['total'] ?? 0 );
		$yes   = (int) ( $row['yes'] ?? 0 );
		$no    = (int) ( $row['no'] ?? 0 );
		$maybe = (int) ( $row['maybe'] ?? 0 );
		return array(
			'total'        => $total,
			'sent'         => (int) ( $row['sent'] ?? 0 ),
			'sent_current' => (int) ( $row['sent_current'] ?? 0 ),
			'queued'       => (int) ( $row['queued'] ?? 0 ),
			'failed'       => (int) ( $row['failed'] ?? 0 ),
			'suppressed'   => (int) ( $row['suppressed'] ?? 0 ),
			'answers'      => array(
				'yes'   => $yes,
				'no'    => $no,
				'maybe' => $maybe,
				'none'  => max( 0, $total - $yes - $no - $maybe ),
			),
			'attended'     => (int) ( $row['attended'] ?? 0 ),
			'converted'    => (int) ( $row['converted'] ?? 0 ),
			'funnel'       => array(
				'invited'   => (int) ( $row['f_invited'] ?? 0 ),
				'yes'       => (int) ( $row['f_yes'] ?? 0 ),
				'no'        => (int) ( $row['f_no'] ?? 0 ),
				'maybe'     => (int) ( $row['f_maybe'] ?? 0 ),
				'attended'  => (int) ( $row['f_attended'] ?? 0 ),
				'converted' => (int) ( $row['f_converted'] ?? 0 ),
			),
		);
	}

	public static function for_cohort( int $cohort_id ): array {
		if ( $cohort_id <= 0 ) {
			return array();
		}
		$out = array();
		foreach ( Db::rows( 'SELECT /* outside-tx */ * FROM %i WHERE target_cohort_id = %d AND kind = %d ORDER BY id DESC LIMIT 50', Db::table( 'invitations' ), $cohort_id, InvitationKind::RSVP->value ) as $row ) {
			$invitation                    = Invitation::from_row( $row );
			self::$memo[ $invitation->id ] = $invitation;
			$out[]                         = $invitation;
		}
		return $out;
	}

	public static function for_user( int $user_id ): array {
		if ( $user_id <= 0 ) {
			return array();
		}
		return Db::rows(

				'SELECT /* outside-tx */ i.id, i.invitation_id, i.answer, i.answered_at, i.answer_src, i.sent_at, v.course_id, v.unit_id, v.occurrence_id, v.rsvp, v.rsvp_until, v.state AS inv_state, v.target_cohort_id, v.yes_action, c.name AS cohort_name, o.starts_at, o.ends_at FROM %i i JOIN %i v ON v.id = i.invitation_id LEFT JOIN %i c ON c.id = v.target_cohort_id LEFT JOIN %i o ON o.id = v.occurrence_id WHERE i.user_id = %d AND v.kind = %d AND v.state IN (%d, %d) AND i.state = %d ORDER BY i.id DESC LIMIT 50',
				Db::table( 'invitees' ),
				Db::table( 'invitations' ),
				Db::table( 'cohorts' ),
				Db::table( 'live_occurrences' ),
				$user_id,
				InvitationKind::RSVP->value,
				InvitationState::SENDING->value,
				InvitationState::SENT->value,
				InviteeState::SENT->value

		);
	}
}