<?php

namespace RoosterLMS\Invitations;

use RoosterLMS\Consent\ConsentService;
use RoosterLMS\Consent\OffersConsent;
use RoosterLMS\Consent\Suppressions;
use RoosterLMS\Core\Clock;
use RoosterLMS\Data\Db;
use RoosterLMS\Data\SqlPart;
use RoosterLMS\Earlier\Audience;
use RoosterLMS\Ledger\Verb;

defined( 'ABSPATH' ) || exit;

final class SendRules {

	public static function due_sql( int|string $version ): SqlPart {
		global $wpdb;
		$current = is_int( $version ) ? (string) $version : ( 'v.version' === $version ? 'v.version' : '1' );
		return SqlPart::of( '( i.state IN (%d, %d) OR ( i.state = %d AND i.sent_version < ', InviteeState::QUEUED->value, InviteeState::FAILED->value, InviteeState::SENT->value )->add( $current )->add( SqlPart::of( ' AND i.source <> %d ) OR ( i.state = %d AND i.user_id > 0 ) )', InviteeSource::NEWSLETTER->value, InviteeState::SUPPRESSED->value ) );
	}

	public static function consent_sql(): SqlPart {
		global $wpdb;
		$soft = ConsentService::soft_optin() ? SqlPart::of( ' WHEN p.notify_offers = %d THEN 1 WHEN COALESCE( p.notify_offers, %d ) = %d AND EXISTS ( SELECT 1 FROM %i s WHERE s.user_id = i.user_id ) THEN 1', OffersConsent::SOFT->value, OffersConsent::NOT_ASKED->value, OffersConsent::NOT_ASKED->value, Db::table( 'sales' ) ) : SqlPart::of( '' );
		$guest_no = SqlPart::of( "EXISTS ( SELECT 1 FROM %i ua JOIN %i pa ON pa.user_id = ua.ID WHERE i.email <> '' AND ua.user_email = i.email AND pa.notify_offers = %d )", $wpdb->users, Db::table( 'people' ), OffersConsent::NO->value );
		return SqlPart::of( '( CASE WHEN i.user_id = 0 THEN ( CASE WHEN ' )->add( $guest_no )->add( ' THEN 0 ELSE 1 END )' )->add( SqlPart::of(
				' WHEN COALESCE( p.notify_offers, 0 ) = %d THEN 0 WHEN i.source IN (%d, %d, %d) THEN 1 WHEN p.notify_offers = %d THEN 1',
				OffersConsent::NO->value,
				InviteeSource::CSV->value,
				InviteeSource::NEWSLETTER->value,
				InviteeSource::EXTERNAL->value,
				OffersConsent::YES->value
			) )->add( $soft )->add( ' ELSE 0 END )' );
	}

	public static function unsubscribed_sql(): SqlPart {
		global $wpdb;
		return SqlPart::of( '( CASE WHEN EXISTS ( SELECT 1 FROM %i x WHERE x.email_hash = i.email_hash AND x.user_id = 0 AND x.state = %d ) OR ', Db::table( 'invitees' ), InviteeState::SUPPRESSED->value )->add( Suppressions::exists_sql( 'i.email_hash' ) )->add( ' THEN 1 ELSE 0 END )' );
	}

	public static function recent_sql(): SqlPart {
		global $wpdb;
		$since = self::since();
		return SqlPart::of(
			'( ( SELECT COUNT(*) FROM %i w WHERE w.email_hash = i.email_hash AND w.sent_at >= %s AND w.id <> i.id ) + ( CASE WHEN i.user_id > 0 THEN ( SELECT COUNT(*) FROM %i ev WHERE ev.user_id = i.user_id AND ev.verb = %d AND ev.occurred_at >= %s ) ELSE 0 END ) )',
			Db::table( 'invitees' ),
			$since,
			Db::table( 'events' ),
			Verb::OFFER_NOTICE_SENT->value,
			$since
		);
	}

	public static function since(): string {
		return gmdate( 'Y-m-d H:i:s', Clock::timestamp() - Audience::CAP_DAYS * DAY_IN_SECONDS );
	}

	public static function due_ids( Invitation $inv, int $limit = 2 ): array {
		global $wpdb;
		$cap = Audience::weekly_cap() > 0 ? Audience::weekly_cap() : PHP_INT_MAX;
		$sql = SqlPart::of( 'SELECT /* outside-tx */ i.id FROM %i i LEFT JOIN %i p ON p.user_id = i.user_id WHERE i.invitation_id = %d AND ', Db::table( 'invitees' ), Db::table( 'people' ), $inv->id )->add( self::due_sql( $inv->version ) )->add( ' AND ' )->add( self::unsubscribed_sql() )->add( ' = 0 AND ' )->add( self::consent_sql() )->add( ' = 1 AND ' )->add( self::recent_sql() )->add( ' < ' . (int) $cap )->add( SqlPart::of( ' ORDER BY i.id LIMIT %d', max( 1, $limit ) ) );
		return array_map( 'intval', Db::col( $sql ) );
	}

	public static function preview( Invitation $inv ): array {
		global $wpdb;
		$cap = Audience::weekly_cap() > 0 ? Audience::weekly_cap() : PHP_INT_MAX;
		$sql = SqlPart::of( 'SELECT /* outside-tx */ COUNT(*) AS total,' . ' SUM( t.due = 1 AND t.unsub = 0 AND t.consent = 1 AND t.recent < ' . (int) $cap . ' ) AS to_send,' . ' SUM( t.due = 0 AND t.unsub = 0 ) AS sent_current,' . ' SUM( t.due = 1 AND t.unsub = 0 AND t.consent = 0 ) AS no_consent,' . ' SUM( t.unsub = 1 ) AS unsubscribed,' . ' SUM( t.due = 1 AND t.unsub = 0 AND t.consent = 1 AND t.recent >= ' . (int) $cap . ' ) AS weekly_cap,' . ' SUM( t.failed ) AS failed' . ' FROM ( SELECT ( CASE WHEN ' )->add( self::due_sql( $inv->version ) )->add( ' THEN 1 ELSE 0 END ) AS due, ' )->add( self::unsubscribed_sql() )->add( ' AS unsub, ' )->add( self::consent_sql() )->add( ' AS consent, ' )->add( self::recent_sql() )->add( ' AS recent, ' )->add( SqlPart::of( '( i.state = %d ) AS failed FROM %i i LEFT JOIN %i p ON p.user_id = i.user_id WHERE i.invitation_id = %d', InviteeState::FAILED->value, Db::table( 'invitees' ), Db::table( 'people' ), $inv->id ) )->add( ' ) t' );
		$row = Db::row( $sql ) ?? array();
		return array(
			'total'        => (int) ( $row['total'] ?? 0 ),
			'to_send'      => (int) ( $row['to_send'] ?? 0 ),
			'sent_current' => (int) ( $row['sent_current'] ?? 0 ),
			'no_consent'   => (int) ( $row['no_consent'] ?? 0 ),
			'unsubscribed' => (int) ( $row['unsubscribed'] ?? 0 ),
			'weekly_cap'   => (int) ( $row['weekly_cap'] ?? 0 ),
			'failed'       => (int) ( $row['failed'] ?? 0 ),
		);
	}
}