<?php

namespace RoosterLMS\Invitations;

use RoosterLMS\Consent\OffersConsent;
use RoosterLMS\Core\Clock;
use RoosterLMS\Data\Db;
use RoosterLMS\Data\SqlPart;
use RoosterLMS\Domain\Actor;
use RoosterLMS\Domain\Authorize;
use RoosterLMS\Domain\DomainError;
use RoosterLMS\Domain\ErrorCode;
use RoosterLMS\Domain\Services;
use RoosterLMS\Earlier\Audience;
use RoosterLMS\Earlier\Target;
use RoosterLMS\I18n\Language;

defined( 'ABSPATH' ) || exit;

final class Audiences {

	public const CSV_MAX_ROWS = 5000;

	private const CHUNK = 500;

	public const MEMBER_PAGE = 5000;

	private static array $options = array();

	public static function available( Invitation $inv, Actor $actor ): array {
		$orgs = array();
		foreach ( self::manageable_orgs( $actor ) as $org_id => $name ) {
			$orgs[] = array(
				'id'   => $org_id,
				'name' => $name,
			);
		}
		$lists = array();
		if ( self::site_admin( $actor ) ) {
			foreach ( NewsletterLists::lists() as $id => $name ) {
				$lists[] = array(
					'id'   => $id,
					'name' => $name,
				);
			}
		}
		$out      = array();
		$interest = $inv->course_id > 0 ? Interest::count( $inv->course_id ) : 0;
		if ( $interest > 0 ) {
			$out['interest'] = array(
				/* translators: %s: number of people. */
				'label'     => sprintf( __( 'People who asked to be notified (%s)', 'rooster-lms' ), number_format_i18n( $interest ) ),
				'available' => true,
				'count'     => $interest,
			);
		}
		$out += array(
			'earlier'      => array(
				'label'     => InviteeSource::EARLIER->label(),
				'available' => $inv->course_id > 0,
			),
			'csv'          => array(
				'label'     => InviteeSource::CSV->label(),
				'available' => true,
			),
			'organisation' => array(
				'label'     => InviteeSource::ORGANISATION->label(),
				'available' => array() !== $orgs,
				'options'   => $orgs,
			),
			'newsletter'   => array(
				'label'     => InviteeSource::NEWSLETTER->label(),
				'available' => array() !== $lists,
				'options'   => $lists,
			),
		);
		$cohorts = self::cohort_options( $actor );
		$units   = self::unit_options( $actor );
		$out    += array(
			'cohort' => array(
				'label'     => __( 'Members of a cohort', 'rooster-lms' ),
				'available' => array() !== $cohorts,
				'options'   => $cohorts,
			),
			'unit'   => array(
				'label'     => __( 'Members of part of an organisation', 'rooster-lms' ),
				'available' => array() !== $units,
				'options'   => $units,
			),
		);
		$filtered = apply_filters( 'rooster_lms_invitation_audiences', $out, $inv->id, $actor->user_id );
		return is_array( $filtered ) ? $filtered : $out;
	}

	public static function add( Invitation $inv, string $source, array $args, Actor $actor, bool $dry_run ): array {
		switch ( $source ) {
			case 'interest':
				return self::interest( $inv, $dry_run );
			case 'earlier':
				if ( $inv->course_id <= 0 ) {
					DomainError::raise( ErrorCode::INVALID, array( 'reason' => 'source' ) );
				}
				return self::earlier( $inv, $actor, $dry_run );
			case 'organisation':
				return self::organisation( $inv, (int) ( $args['org_id'] ?? 0 ), $actor, $dry_run );
			case 'cohort':
				return self::cohort( $inv, (int) ( $args['cohort_id'] ?? 0 ), $actor, $dry_run );
			case 'unit':
				return self::unit( $inv, (int) ( $args['unit_id'] ?? 0 ), $actor, $dry_run );
			case 'newsletter':
				return self::newsletter( $inv, (int) ( $args['list_id'] ?? 0 ), $actor, $dry_run );
			case 'csv':
				if ( true !== ( $args['consent_confirmed'] ?? false ) ) {
					DomainError::raise( ErrorCode::INVALID, array( 'reason' => 'consent' ) );
				}
				return self::rows( $inv, (array) ( $args['rows'] ?? array() ), InviteeSource::CSV, $dry_run, 'csv' );
		}
		$known = self::available( $inv, $actor );
		if ( ! isset( $known[ $source ] ) || ! ( $known[ $source ]['available'] ?? false ) ) {
			DomainError::raise( ErrorCode::INVALID, array( 'reason' => 'source' ) );
		}
		$users = apply_filters( 'rooster_lms_invitation_audience_users', null, $source, $args, $inv->id, $actor->user_id );
		if ( is_array( $users ) ) {
			return self::users( $inv, $users, $dry_run, $source );
		}
		$rows = apply_filters( 'rooster_lms_invitation_audience_rows', null, $source, $args, $inv->id, $actor->user_id );
		if ( ! is_array( $rows ) ) {
			DomainError::raise( ErrorCode::INVALID, array( 'reason' => 'source' ) );
		}
		return self::rows( $inv, $rows, InviteeSource::EXTERNAL, $dry_run, $source );
	}

	private static function interest( Invitation $inv, bool $dry_run ): array {
		global $wpdb;
		$hash    = '( CASE WHEN x.user_id > 0 THEN SHA2( LOWER( TRIM( u.user_email ) ), 256 ) ELSE x.email_hash END )';
		$refused = SqlPart::of( '( ' )->add( SqlPart::of( 'COALESCE( p.notify_offers, 0 ) = %d', OffersConsent::NO->value ) )->add( SqlPart::of( ' OR EXISTS ( SELECT 1 FROM %i sx WHERE sx.email_hash = ', Db::table( 'invitees' ) ) )->add( $hash )->add( SqlPart::of( ' AND sx.user_id = 0 AND sx.state = %d )', InviteeState::SUPPRESSED->value ) )->add( ' OR ' )->add( \RoosterLMS\Consent\Suppressions::exists_sql( SqlPart::of( $hash ) ) )->add( ' )' );
		$from    = SqlPart::of( " FROM %i x JOIN %i v ON v.id = x.invitation_id AND v.course_id = %d AND v.kind = %d LEFT JOIN %i u ON u.ID = x.user_id LEFT JOIN %i p ON p.user_id = x.user_id WHERE ( ( x.user_id > 0 AND u.ID IS NOT NULL ) OR ( x.user_id = 0 AND x.email <> '' ) )", Db::table( 'invitees' ), Db::table( 'invitations' ), $inv->course_id, InvitationKind::INTEREST->value, $wpdb->users, Db::table( 'people' ) );
		if ( $dry_run ) {
			$row   = Db::row( SqlPart::of( 'SELECT /* outside-tx */ COUNT(*) AS found, SUM( ' )->add( self::here_sql( $inv, $hash ) )->add( ' ) AS duplicate, SUM( NOT ' )->add( self::here_sql( $inv, $hash ) )->add( ' AND ' )->add( self::elsewhere_sql( $inv, $hash ) )->add( ' ) AS already, SUM( NOT ' )->add( self::here_sql( $inv, $hash ) )->add( ' AND NOT ' )->add( self::elsewhere_sql( $inv, $hash ) )->add( ' AND ' )->add( $refused )->add( ' ) AS refused' )->add( $from ) );
			$found = (int) ( $row['found'] ?? 0 );
			$skip  = array(
				'duplicate'       => (int) ( $row['duplicate'] ?? 0 ),
				'already_invited' => (int) ( $row['already'] ?? 0 ),
				'suppressed'      => (int) ( $row['refused'] ?? 0 ),
			);
			return self::result( 'interest', 0, $found, $skip ) + array( 'new' => max( 0, $found - array_sum( $skip ) ) );
		}
		$found = (int) Db::value( SqlPart::of( 'SELECT /* outside-tx */ COUNT(*)' )->add( $from ) );
		$added = Db::query(
			SqlPart::of( 'INSERT IGNORE INTO %i (invitation_id, user_id, email_hash, email, first_name, last_name, lang, source, created_at) SELECT %d, x.user_id, ', Db::table( 'invitees' ), $inv->id )->add( $hash )->add( SqlPart::of( ", IF( x.user_id > 0, '', x.email ), IF( x.user_id > 0, '', x.first_name ), IF( x.user_id > 0, '', x.last_name ), LEFT( COALESCE( NULLIF( p.lang, '' ), x.lang ), 12 ), %d, %s", InviteeSource::INTEREST->value, Clock::now() ) )->add( $from )->add( ' AND NOT ' )->add( self::elsewhere_sql( $inv, $hash ) )->add( ' AND NOT ' )->add( $refused )
		);
		$added = max( 0, $added );
		return self::result( 'interest', $added, $found, array( 'duplicate' => max( 0, $found - $added ) ) );
	}

	private static function earlier( Invitation $inv, Actor $actor, bool $dry_run ): array {
		$target  = Target::of( $inv->course_id, $inv->unit_id );
		$courses = Audience::courses( $target, $actor );
		if ( $dry_run ) {
			$found = Audience::count( $target, $courses );
			return self::result( 'earlier', 0, $found, array() );
		}
		$after = 0;
		$found = 0;
		$added = 0;
		do {
			$people = Audience::batch( $target, $courses, array(), $after, self::CHUNK );
			$full   = count( $people ) === self::CHUNK;
			if ( array() !== $people ) {
				$found += count( $people );
				$added += self::insert_users( $inv, $people, InviteeSource::EARLIER );
				$after  = (int) end( $people );
			}
		} while ( $full );
		return self::result( 'earlier', $added, $found, array( 'duplicate' => $found - $added ) );
	}

	private static function organisation( Invitation $inv, int $org_id, Actor $actor, bool $dry_run ): array {
		if ( $org_id <= 0 ) {
			DomainError::raise( ErrorCode::INVALID, array( 'reason' => 'org' ) );
		}
		Authorize::org_write( $actor, $org_id );
		global $wpdb;
		$from = SqlPart::of( ' FROM %i m JOIN %i u ON u.ID = m.user_id LEFT JOIN %i p ON p.user_id = m.user_id WHERE m.org_id = %d AND m.left_at IS NULL', Db::table( 'org_members' ), $wpdb->users, Db::table( 'people' ), $org_id );
		if ( $dry_run ) {
			$row   = Db::row( SqlPart::of( 'SELECT /* outside-tx */ COUNT(*) AS found, SUM( ' )->add( self::here_sql( $inv, 'SHA2( LOWER( TRIM( u.user_email ) ), 256 )' ) )->add( ' ) AS duplicate, SUM( ' )->add( self::elsewhere_sql( $inv, 'SHA2( LOWER( TRIM( u.user_email ) ), 256 )' ) )->add( ' ) AS already, SUM( COALESCE( p.notify_offers, 0 ) = ' . OffersConsent::NO->value . ' ) AS refused' )->add( $from ) );
			$found = (int) ( $row['found'] ?? 0 );
			return self::result(
				'organisation',
				0,
				$found,
				array(
					'duplicate'       => (int) ( $row['duplicate'] ?? 0 ),
					'already_invited' => (int) ( $row['already'] ?? 0 ),
					'suppressed'      => (int) ( $row['refused'] ?? 0 ),
				)
			);
		}
		$found = (int) Db::value( SqlPart::of( 'SELECT /* outside-tx */ COUNT(*)' )->add( $from ) );
		$added = Db::query(
			SqlPart::of( 'INSERT IGNORE INTO %i (invitation_id, user_id, email_hash, lang, source, created_at) SELECT %d, u.ID, SHA2( LOWER( TRIM( u.user_email ) ), 256 ), COALESCE( p.lang, %s ), %d, %s', Db::table( 'invitees' ), $inv->id, '', InviteeSource::ORGANISATION->value, Clock::now() )->add( $from )->add( ' AND NOT ' )->add( self::elsewhere_sql( $inv, 'SHA2( LOWER( TRIM( u.user_email ) ), 256 )' ) )
		);
		return self::result( 'organisation', max( 0, $added ), $found, array( 'duplicate' => max( 0, $found - $added ) ) );
	}

	private static function cohort( Invitation $inv, int $cohort_id, Actor $actor, bool $dry_run ): array {
		$cohort = $cohort_id > 0 ? Services::cohort_repo()->get( $cohort_id ) : null;
		if ( null === $cohort ) {
			DomainError::raise( ErrorCode::INVALID, array( 'reason' => 'cohort' ) );
		}
		Authorize::org_write( $actor, $cohort->org_id );
		$total = self::result( 'cohort', 0, 0, array() );
		$after = 0;
		do {
			$page = Services::cohort_repo()->member_ids_after( $cohort->id, $after, self::MEMBER_PAGE );
			if ( array() === $page ) {
				break;
			}
			$part            = self::users( $inv, $page, $dry_run, 'cohort' );
			$total['added'] += $part['added'];
			$total['found'] += $part['found'];
			foreach ( $part['skipped'] as $reason => $count ) {
				$total['skipped'][ $reason ] = ( $total['skipped'][ $reason ] ?? 0 ) + $count;
			}
			$after = (int) end( $page );
			$full  = count( $page ) >= self::MEMBER_PAGE;
		} while ( $full );
		return $total;
	}

	private static function unit( Invitation $inv, int $unit_id, Actor $actor, bool $dry_run ): array {
		$unit = $unit_id > 0 ? Services::org_unit_repo()->get( $unit_id ) : null;
		if ( null === $unit || ! $unit->is_active() ) {
			DomainError::raise( ErrorCode::INVALID, array( 'reason' => 'unit' ) );
		}
		Authorize::unit_write( $actor, $unit->org_id, $unit->id );
		return self::users( $inv, Services::org_unit_repo()->member_ids_in_subtree( $unit->id ), $dry_run, 'unit' );
	}

	private static function users( Invitation $inv, array $user_ids, bool $dry_run, string $key ): array {
		$ids = array();
		foreach ( $user_ids as $id ) {
			if ( ! is_int( $id ) && ! ( is_string( $id ) && '' !== $id && ctype_digit( $id ) ) ) {
				continue;
			}
			$id = (int) $id;
			if ( $id > 0 ) {
				$ids[ $id ] = $id;
			}
		}
		$ids = array_values( $ids );
		if ( array() === $ids ) {
			return self::result( $key, 0, 0, array() );
		}
		global $wpdb;
		$hash = 'SHA2( LOWER( TRIM( u.user_email ) ), 256 )';
		if ( $dry_run ) {
			$sum = array(
				'found'     => 0,
				'duplicate' => 0,
				'already'   => 0,
				'refused'   => 0,
			);
			foreach ( array_chunk( $ids, 1000 ) as $chunk ) {
				$row = Db::row( SqlPart::of( 'SELECT /* outside-tx */ COUNT(*) AS found, SUM( ' )->add( self::here_sql( $inv, $hash ) )->add( ' ) AS duplicate, SUM( ' )->add( self::elsewhere_sql( $inv, $hash ) )->add( ' ) AS already, SUM( COALESCE( p.notify_offers, 0 ) = ' . OffersConsent::NO->value . ' ) AS refused' )->add( SqlPart::of( ' FROM %i u LEFT JOIN %i p ON p.user_id = u.ID WHERE u.ID IN (' . implode( ',', array_fill( 0, count( $chunk ), '%d' ) ) . ')', array_merge( array( $wpdb->users, Db::table( 'people' ) ), $chunk ) ) ) );
				foreach ( array_keys( $sum ) as $k ) {
					$sum[ $k ] += (int) ( $row[ $k ] ?? 0 );
				}
			}
			return self::result(
				$key,
				0,
				$sum['found'],
				array(
					'duplicate'       => $sum['duplicate'],
					'already_invited' => $sum['already'],
					'suppressed'      => $sum['refused'],
				)
			);
		}
		$added = 0;
		$found = 0;
		foreach ( array_chunk( $ids, self::CHUNK ) as $chunk ) {
			$found += (int) Db::value( 'SELECT /* outside-tx */ COUNT(*) FROM %i WHERE ID IN (' . implode( ',', array_fill( 0, count( $chunk ), '%d' ) ) . ')', array_merge( array( $wpdb->users ), $chunk ) );
			$added += self::insert_users( $inv, $chunk, InviteeSource::ORGANISATION );
		}
		return self::result( $key, $added, $found, array( 'duplicate' => max( 0, $found - $added ) ) );
	}

	public static function cohort_options( Actor $actor ): array {
		$key = 'c' . $actor->user_id . ( $actor->is_trusted() ? 't' : '' );
		if ( isset( self::$options[ $key ] ) ) {
			return self::$options[ $key ];
		}
		$orgs = self::option_orgs( $actor );
		$out  = array();
		if ( null === $orgs || array() !== $orgs ) {
			global $wpdb;
			$sql  = SqlPart::of( 'SELECT /* outside-tx */ c.id, c.name, c.org_id, o.name AS org_name FROM %i c JOIN %i o ON o.id = c.org_id WHERE c.state = %d AND o.status = 1', Db::table( 'cohorts' ), Db::table( 'orgs' ), \RoosterLMS\Orgs\Cohort::ACTIVE )->add( null === $orgs ? SqlPart::of( '' ) : SqlPart::and_in( 'c.org_id', $orgs ) )->add( ' ORDER BY o.name, c.name, c.id LIMIT 500' );
			$rows = Db::rows( $sql );
			$org_names = self::org_names( $rows );
			foreach ( $rows as $row ) {
				$out[] = array(
					'id'   => (int) $row['id'],
					/* translators: 1: cohort name, 2: organisation name. */
					'name' => sprintf( __( '%1$s (%2$s)', 'rooster-lms' ), (string) $row['name'], $org_names[ (int) $row['org_id'] ] ?? (string) $row['org_name'] ),
				);
			}
		}
		self::$options[ $key ] = $out;
		return $out;
	}

	public static function unit_options( Actor $actor ): array {
		$key = 'u' . $actor->user_id . ( $actor->is_trusted() ? 't' : '' );
		if ( isset( self::$options[ $key ] ) ) {
			return self::$options[ $key ];
		}
		$orgs  = self::option_orgs( $actor );
		$where = null === $orgs ? SqlPart::of( '' ) : self::unit_option_where( $actor, $orgs );
		$out   = array();
		if ( null === $orgs || ! $where->is_empty() ) {
			global $wpdb;
			$sql  = SqlPart::of( 'SELECT /* outside-tx */ u.id, u.org_id, u.parent_id, u.name, o.name AS org_name FROM %i u JOIN %i o ON o.id = u.org_id WHERE u.state = 1 AND u.depth > 0 AND o.status = 1', Db::table( 'org_units' ), Db::table( 'orgs' ) )->add( $where )->add( ' ORDER BY o.name, u.org_id, u.depth, u.position, u.id LIMIT 500' );
			$rows = Db::rows( $sql );
			$name = array();
			foreach ( $rows as $row ) {
				$name[ (int) $row['id'] ] = (string) $row['name'];
			}
			$name      = array() === $name ? $name : Services::strings()->translate_many( \RoosterLMS\I18n\TextObject::LOCATION, $name, \RoosterLMS\I18n\Switcher::current() );
			$org_names = self::org_names( $rows );
			$path      = array();
			foreach ( $rows as $row ) {
				$id     = (int) $row['id'];
				$parent = (int) $row['parent_id'];
				if ( \RoosterLMS\Orgs\ApparentRoot::is_above( (int) $row['org_id'], $id ) ) {
					continue;
				}
				$head        = \RoosterLMS\Orgs\ApparentRoot::unit_in( (int) $row['org_id'] ) === $id && \RoosterLMS\Orgs\ApparentRoot::hides();
				$path[ $id ] = $head ? (string) ( $name[ $id ] ?? $row['name'] ) : ( isset( $path[ $parent ] ) ? $path[ $parent ] : ( $org_names[ (int) $row['org_id'] ] ?? (string) $row['org_name'] ) ) . ' / ' . (string) ( $name[ $id ] ?? $row['name'] );
				$out[]       = array(
					'id'   => $id,
					'name' => $path[ $id ],
				);
			}
		}
		self::$options[ $key ] = $out;
		return $out;
	}

	private static function org_names( array $rows ): array {
		$names = array();
		foreach ( $rows as $row ) {
			$names[ (int) $row['org_id'] ] = (string) $row['org_name'];
		}
		$names = array() === $names ? $names : Services::strings()->translate_many( \RoosterLMS\I18n\TextObject::ORG, $names, \RoosterLMS\I18n\Switcher::current() );
		return \RoosterLMS\Orgs\ApparentRoot::labels( $names, \RoosterLMS\I18n\Switcher::current() );
	}

	private static function unit_option_where( Actor $actor, array $orgs ): SqlPart {
		global $wpdb;
		$any = array();
		if ( array() !== $orgs ) {
			$any[] = SqlPart::of( 'u.org_id IN (' . implode( ',', array_fill( 0, count( $orgs ), '%d' ) ) . ')', array_map( 'intval', $orgs ) );
		}
		if ( $actor->user_id > 0 && ! $actor->is_trusted() ) {
			$scope = \RoosterLMS\Orgs\OrgScope::for_user( $actor->user_id );
			foreach ( $scope->scoped_org_ids( \RoosterLMS\Orgs\MemberRole::MANAGER->value ) as $org_id ) {
				$units = in_array( $org_id, $orgs, true ) ? null : $scope->unit_scope( $org_id, \RoosterLMS\Orgs\OrgScope::MANAGE_MASK );
				if ( null !== $units && array() !== $units ) {
					$any[] = SqlPart::of( '( u.org_id = %d', $org_id )->add( \RoosterLMS\Orgs\OrgUnitRepository::subtree_where( 'u.id', $units ) )->add( ' )' );
				}
			}
		}
		return array() === $any ? SqlPart::of( '' ) : SqlPart::of( ' AND ( ' )->add( SqlPart::join( ' OR ', $any ) )->add( ' )' );
	}

	private static function option_orgs( Actor $actor ): ?array {
		if ( self::site_admin( $actor ) ) {
			return null;
		}
		if ( $actor->user_id <= 0 ) {
			return array();
		}
		return \RoosterLMS\Orgs\OrgScope::for_user( $actor->user_id )->org_ids( \RoosterLMS\Orgs\MemberRole::MANAGER->value );
	}

	public static function forget(): void {
		self::$options = array();
	}

	private static function newsletter( Invitation $inv, int $list_id, Actor $actor, bool $dry_run ): array {
		if ( ! self::site_admin( $actor ) ) {
			DomainError::raise( ErrorCode::FORBIDDEN );
		}
		if ( '' === NewsletterLists::column( $list_id ) ) {
			DomainError::raise( ErrorCode::INVALID, array( 'reason' => 'list' ) );
		}
		global $wpdb;
		$hash  = 'SHA2( LOWER( TRIM( n.email ) ), 256 )';
		$valid = SqlPart::of( 'n.email LIKE %s', '%_@_%._%' );
		$from  = SqlPart::of( ' FROM %i n LEFT JOIN %i u ON u.user_email = n.email LEFT JOIN %i p ON p.user_id = u.ID WHERE ', NewsletterLists::table(), $wpdb->users, Db::table( 'people' ) )->add( NewsletterLists::where( $list_id ) );
		if ( $dry_run ) {
			$row = Db::row( SqlPart::of( 'SELECT /* outside-tx */ COUNT(*) AS found, SUM( NOT ' )->add( $valid )->add( ' ) AS invalid, SUM( ' )->add( $valid )->add( ' AND ' )->add( self::here_sql( $inv, $hash ) )->add( ' ) AS duplicate, SUM( ' )->add( $valid )->add( ' AND ' )->add( self::elsewhere_sql( $inv, $hash ) )->add( ' ) AS already, SUM( COALESCE( p.notify_offers, 0 ) = ' . OffersConsent::NO->value . ' ) AS refused' )->add( $from ) );
			return self::result(
				'newsletter',
				0,
				(int) ( $row['found'] ?? 0 ),
				array(
					'invalid'         => (int) ( $row['invalid'] ?? 0 ),
					'duplicate'       => (int) ( $row['duplicate'] ?? 0 ),
					'already_invited' => (int) ( $row['already'] ?? 0 ),
					'suppressed'      => (int) ( $row['refused'] ?? 0 ),
				)
			);
		}
		$count       = NewsletterLists::count( $list_id );
		$added       = Db::query(
			SqlPart::of(
				"INSERT IGNORE INTO %i (invitation_id, user_id, email_hash, email, first_name, last_name, lang, source, created_at) SELECT %d, COALESCE( u.ID, 0 ), SHA2( LOWER( TRIM( n.email ) ), 256 ), IF( u.ID IS NULL, LOWER( TRIM( n.email ) ), '' ), IF( u.ID IS NULL, LEFT( n.name, 100 ), '' ), IF( u.ID IS NULL, LEFT( n.surname, 100 ), '' ), LEFT( COALESCE( p.lang, n.language ), 12 ), %d, %s",
				Db::table( 'invitees' ),
				$inv->id,
				InviteeSource::NEWSLETTER->value,
				Clock::now()
			)->add( $from )->add( ' AND ' )->add( $valid )->add( ' AND NOT ' )->add( self::elsewhere_sql( $inv, $hash ) )
		);
		$valid_found = $count['members'] - $count['invalid'];
		return self::result(
			'newsletter',
			max( 0, $added ),
			$count['members'],
			array(
				'invalid'   => $count['invalid'],
				'duplicate' => max( 0, $valid_found - $added ),
			)
		);
	}

	public static function rows( Invitation $inv, array $rows, InviteeSource $source, bool $dry_run, string $key ): array {
		if ( count( $rows ) > self::CSV_MAX_ROWS ) {
			DomainError::raise(
				ErrorCode::INVALID,
				array(
					'reason' => 'rows',
					'max'    => self::CSV_MAX_ROWS,
				)
			);
		}
		$skipped = array(
			'invalid'         => 0,
			'duplicate'       => 0,
			'already_invited' => 0,
			'suppressed'      => 0,
		);
		$report  = array();
		$clean   = array();
		$seen    = array();
		foreach ( $rows as $line => $row ) {
			$row   = self::row_fields( is_array( $row ) ? $row : array() );
			$email = Tokens::address( $row['email'] );
			if ( '' === $email ) {
				++$skipped['invalid'];
				$report[] = array(
					'line'   => (int) $line,
					'status' => 'invalid',
				);
				continue;
			}
			$hash = Tokens::email_hash( $email );
			if ( isset( $seen[ $hash ] ) ) {
				++$skipped['duplicate'];
				$report[] = array(
					'line'   => (int) $line,
					'status' => 'duplicate',
				);
				continue;
			}
			$seen[ $hash ]  = true;
			$clean[ $hash ] = array(
				'line'       => (int) $line,
				'email'      => $email,
				'first_name' => $row['first_name'],
				'last_name'  => $row['last_name'],
				'lang'       => $row['lang'],
			);
		}
		$known = self::known( $inv, array_keys( $clean ) );
		$users = self::accounts( array_column( $clean, 'email' ) );
		$add   = array();
		foreach ( $clean as $hash => $row ) {
			$status = 'new';
			$user   = $users[ $row['email'] ] ?? null;
			if ( isset( $known['here'][ $hash ] ) ) {
				$status = 'duplicate';
			} elseif ( isset( $known['elsewhere'][ $hash ] ) ) {
				$status = 'already_invited';
			} elseif ( isset( $known['unsubscribed'][ $hash ] ) || ( null !== $user && OffersConsent::NO->value === $user['offers'] ) ) {
				$status = 'suppressed';
			}
			if ( 'new' !== $status ) {
				++$skipped[ $status ];
				$report[] = array(
					'line'   => $row['line'],
					'status' => $status,
				);
				continue;
			}
			$add[ $hash ] = $row + array(
				'user_id' => null === $user ? 0 : $user['id'],
				'ulang'   => null === $user ? '' : $user['lang'],
			);
		}
		usort( $report, static fn( array $a, array $b ): int => $a['line'] <=> $b['line'] );
		$found = count( $rows );
		if ( $dry_run ) {
			return self::result( $key, 0, $found, $skipped ) + array(
				'new'  => count( $add ),
				'rows' => array_slice( $report, 0, 100 ),
			);
		}
		$added = 0;
		foreach ( array_chunk( $add, self::CHUNK, true ) as $chunk ) {
			$added += self::insert_rows( $inv, $chunk, $source );
		}
		$skipped['duplicate'] += max( 0, count( $add ) - $added );
		return self::result( $key, $added, $found, $skipped ) + array( 'rows' => array_slice( $report, 0, 100 ) );
	}

	private static function row_fields( array $row ): array {
		if ( array_is_list( $row ) ) {
			$row = array(
				'email'      => $row[0] ?? '',
				'first_name' => $row[1] ?? '',
				'last_name'  => $row[2] ?? '',
				'lang'       => $row[3] ?? '',
			);
		}
		$text = static fn( $v, int $max ): string => mb_substr( trim( sanitize_text_field( is_scalar( $v ) ? (string) $v : '' ) ), 0, $max );
		return array(
			'email'      => $text( $row['email'] ?? '', 200 ),
			'first_name' => $text( $row['first_name'] ?? '', 100 ),
			'last_name'  => $text( $row['last_name'] ?? '', 100 ),
			'lang'       => Language::normalize( $text( $row['lang'] ?? '', 12 ) ),
		);
	}

	public static function csv_rows( iterable $sheet ): array {
		$names = array(
			'email'      => array( 'email', 'e-mail', 'mail', 'emailaddress', 'e-mailaddress' ),
			'first_name' => array( 'firstname', 'first', 'givenname', 'name' ),
			'last_name'  => array( 'lastname', 'last', 'surname', 'familyname' ),
			'lang'       => array( 'language', 'lang', 'locale' ),
		);
		$map   = null;
		$out   = array();
		foreach ( $sheet as $line => $cells ) {
			$cells = array_map( static fn( $c ): string => null === $c ? '' : (string) $c, (array) $cells );
			if ( null === $map ) {
				$map = array();
				foreach ( $cells as $i => $cell ) {
					$label = strtolower( (string) preg_replace( '/[\s_]+/', '', $cell ) );
					foreach ( $names as $field => $aliases ) {
						if ( ! isset( $map[ $field ] ) && in_array( $label, $aliases, true ) ) {
							$map[ $field ] = $i;
						}
					}
				}
				if ( isset( $map['email'] ) ) {
					continue;
				}
				$map = array(
					'email'      => 0,
					'first_name' => 1,
					'last_name'  => 2,
					'lang'       => 3,
				);
			}
			$row = array();
			foreach ( array( 'email', 'first_name', 'last_name', 'lang' ) as $field ) {
				$row[ $field ] = isset( $map[ $field ] ) ? (string) ( $cells[ $map[ $field ] ] ?? '' ) : '';
			}
			$out[ (int) $line ] = $row;
		}
		return $out;
	}

	private static function insert_users( Invitation $inv, array $users, InviteeSource $source ): int {
		$users = array_values( array_filter( array_map( 'intval', $users ), static fn( int $id ): bool => $id > 0 ) );
		if ( array() === $users ) {
			return 0;
		}
		global $wpdb;
		$sql = SqlPart::of( 'INSERT IGNORE INTO %i (invitation_id, user_id, email_hash, lang, source, created_at) SELECT %d, u.ID, SHA2( LOWER( TRIM( u.user_email ) ), 256 ), COALESCE( p.lang, %s ), %d, %s FROM %i u LEFT JOIN %i p ON p.user_id = u.ID WHERE ', Db::table( 'invitees' ), $inv->id, '', $source->value, Clock::now(), $wpdb->users, Db::table( 'people' ) )->add( SqlPart::of( 'u.ID IN (' . implode( ',', array_fill( 0, count( $users ), '%d' ) ) . ')', $users ) )->add( ' AND NOT ' )->add( self::elsewhere_sql( $inv, 'SHA2( LOWER( TRIM( u.user_email ) ), 256 )' ) );
		return max( 0, Db::query( $sql ) );
	}

	private static function insert_rows( Invitation $inv, array $rows, InviteeSource $source ): int {
		if ( array() === $rows ) {
			return 0;
		}
		global $wpdb;
		$now    = Clock::now();
		$values = array();
		foreach ( $rows as $hash => $row ) {
			$guest    = (int) $row['user_id'] <= 0;
			$lang     = $guest ? (string) $row['lang'] : ( '' !== (string) $row['ulang'] ? (string) $row['ulang'] : (string) $row['lang'] );
			$values[] = SqlPart::of( '(%d, %d, %s, %s, %s, %s, %s, %d, %s)', $inv->id, (int) $row['user_id'], (string) $hash, (string) $row['email'], (string) $row['first_name'], (string) $row['last_name'], $lang, $source->value, $now );
		}
		$sql = SqlPart::of( 'INSERT IGNORE INTO %i (invitation_id, user_id, email_hash, email, first_name, last_name, lang, source, created_at) VALUES ', Db::table( 'invitees' ) )->add( SqlPart::join( ', ', $values ) );
		return max( 0, Db::query( $sql ) );
	}

	private static function known( Invitation $inv, array $hashes ): array {
		$out = array(
			'here'         => array(),
			'elsewhere'    => array(),
			'unsubscribed' => array(),
		);
		if ( array() === $hashes ) {
			return $out;
		}
		global $wpdb;
		$sql = SqlPart::of( 'SELECT /* outside-tx */ x.email_hash, x.invitation_id, x.user_id, x.state, y.course_id, y.unit_id, y.kind, y.state AS inv_state, y.target_cohort_id FROM %i x JOIN %i y ON y.id = x.invitation_id WHERE ', Db::table( 'invitees' ), Db::table( 'invitations' ) )->add( SqlPart::of( 'x.email_hash IN (' . implode( ',', array_fill( 0, count( $hashes ), '%s' ) ) . ')', array_values( $hashes ) ) )->add( SqlPart::of( ' UNION ALL SELECT sp.email_hash, 0, 0, %d, 0, 0, 0, 0, 0 FROM %i sp WHERE ', InviteeState::SUPPRESSED->value, Db::table( 'suppressions' ) ) )->add( SqlPart::of( 'sp.email_hash IN (' . implode( ',', array_fill( 0, count( $hashes ), '%s' ) ) . ')', array_values( $hashes ) ) );
		foreach ( Db::rows( $sql ) as $row ) {
			$hash = (string) $row['email_hash'];
			if ( (int) $row['invitation_id'] === $inv->id ) {
				$out['here'][ $hash ] = true;
			} elseif ( (int) $row['course_id'] === $inv->course_id && (int) $row['unit_id'] === $inv->unit_id && (int) $row['target_cohort_id'] === $inv->target_cohort_id && InvitationKind::RSVP->value === (int) $row['kind'] && InvitationState::CANCELLED->value !== (int) $row['inv_state'] ) {
				$out['elsewhere'][ $hash ] = true;
			}
			if ( 0 === (int) $row['user_id'] && InviteeState::SUPPRESSED->value === (int) $row['state'] ) {
				$out['unsubscribed'][ $hash ] = true;
			}
		}
		return $out;
	}

	private static function accounts( array $emails ): array {
		if ( array() === $emails ) {
			return array();
		}
		global $wpdb;
		$out = array();
		$sql = SqlPart::of( 'SELECT /* outside-tx */ u.ID, u.user_email, p.notify_offers, p.lang FROM %i u LEFT JOIN %i p ON p.user_id = u.ID WHERE ', $wpdb->users, Db::table( 'people' ) )->add( SqlPart::of( 'u.user_email IN (' . implode( ',', array_fill( 0, count( $emails ), '%s' ) ) . ')', array_values( $emails ) ) );
		foreach ( Db::rows( $sql ) as $row ) {
			$out[ strtolower( (string) $row['user_email'] ) ] = array(
				'id'     => (int) $row['ID'],
				'offers' => (int) ( $row['notify_offers'] ?? 0 ),
				'lang'   => (string) ( $row['lang'] ?? '' ),
			);
		}
		return $out;
	}

	private static function here_sql( Invitation $inv, string $hash ): SqlPart {
		global $wpdb;
		return SqlPart::of( '( EXISTS ( SELECT 1 FROM ' )->add( SqlPart::of( '%i', Db::table( 'invitees' ) ) )->add( ' h WHERE h.invitation_id = ' . (int) $inv->id . ' AND h.email_hash = ' . $hash . ' ) )' );
	}

	private static function elsewhere_sql( Invitation $inv, string $hash ): SqlPart {
		global $wpdb;
		return SqlPart::of( '( EXISTS ( SELECT 1 FROM ' )->add( SqlPart::of( '%i', Db::table( 'invitees' ) ) )->add( ' ox JOIN ' )->add( SqlPart::of( '%i', Db::table( 'invitations' ) ) )->add( ' oy ON oy.id = ox.invitation_id WHERE ox.email_hash = ' . $hash )->add( SqlPart::of( ' AND oy.id <> %d AND oy.course_id = %d AND oy.unit_id = %d AND oy.target_cohort_id = %d AND oy.kind = %d AND oy.state <> %d ) )', $inv->id, $inv->course_id, $inv->unit_id, $inv->target_cohort_id, InvitationKind::RSVP->value, InvitationState::CANCELLED->value ) );
	}

	public static function manageable_orgs( Actor $actor ): array {
		if ( $actor->user_id <= 0 && ! $actor->is_trusted() ) {
			return array();
		}
		if ( $actor->is_trusted() || self::site_admin( $actor ) ) {
			$rows = Db::rows( 'SELECT /* outside-tx */ id, name FROM %i WHERE status = 1 ORDER BY name LIMIT 500', Db::table( 'orgs' ) );
		} else {
			$ids = \RoosterLMS\Orgs\OrgScope::for_user( $actor->user_id )->org_ids( \RoosterLMS\Orgs\MemberRole::MANAGER->value );
			$ids = array_values( array_filter( array_map( 'intval', $ids ), static fn( int $id ): bool => $id > 0 ) );
			if ( array() === $ids ) {
				return array();
			}
			$rows = Db::rows( 'SELECT /* outside-tx */ id, name FROM %i WHERE id IN (' . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ') AND status <> %d ORDER BY name', array_merge( array( Db::table( 'orgs' ) ), $ids, array( \RoosterLMS\Orgs\OrgStatus::DELETED->value ) ) );
		}
		$out = array();
		foreach ( $rows as $row ) {
			$out[ (int) $row['id'] ] = (string) $row['name'];
		}
		return $out;
	}

	public static function site_admin( Actor $actor ): bool {
		return $actor->is_trusted() || $actor->is_site_admin();
	}

	private static function result( string $source, int $added, int $found, array $skipped ): array {
		return array(
			'source'  => $source,
			'added'   => $added,
			'found'   => $found,
			'skipped' => array_map( 'intval', $skipped ),
		);
	}
}