<?php
/**
 * CampaignRepository — CRUD and aggregate reporting for campaign groups.
 *
 * @package CartCue
 */

namespace CartCue;

defined( 'ABSPATH' ) || exit;

final class CampaignRepository {

	private static function is_available(): bool {
		return defined( 'CARTCUE_PRO_VERSION' );
	}

	public static function all(): array {
		if ( ! self::is_available() ) {
			return [];
		}
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		return $wpdb->get_results(
			$wpdb->prepare( 'SELECT * FROM %i ORDER BY name ASC', DB::campaigns() ),
			ARRAY_A
		) ?: [];
	}

	public static function exists( int $id ): bool {
		if ( ! self::is_available() ) {
			return false;
		}
		return $id > 0 && null !== self::get( $id );
	}

	public static function resolve_id( int $campaign_id ): int {
		if ( ! self::is_available() ) {
			return 0;
		}
		$campaign_id = max( 0, $campaign_id );
		if ( 0 === $campaign_id ) {
			return 0;
		}
		return self::exists( $campaign_id ) ? $campaign_id : 0;
	}

	public static function get( int $id ): ?array {
		if ( ! self::is_available() ) {
			return null;
		}
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$row = $wpdb->get_row(
			$wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', DB::campaigns(), $id ),
			ARRAY_A
		);

		return $row ?: null;
	}

	public static function create( string $name, string $description = '' ): int {
		if ( ! self::is_available() ) {
			return 0;
		}
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$wpdb->insert(
			DB::campaigns(),
			[
				'name'        => sanitize_text_field( $name ),
				'description' => sanitize_textarea_field( $description ),
				'created_at'  => current_time( 'mysql' ),
				'updated_at'  => current_time( 'mysql' ),
			],
			[ '%s', '%s', '%s', '%s' ]
		);

		return (int) $wpdb->insert_id;
	}

	public static function update( int $id, string $name, string $description = '' ): void {
		if ( ! self::is_available() ) {
			return;
		}
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$wpdb->update(
			DB::campaigns(),
			[
				'name'        => sanitize_text_field( $name ),
				'description' => sanitize_textarea_field( $description ),
				'updated_at'  => current_time( 'mysql' ),
			],
			[ 'id' => $id ],
			[ '%s', '%s', '%s' ],
			[ '%d' ]
		);
	}

	public static function delete( int $id ): void {
		if ( ! self::is_available() ) {
			return;
		}
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$wpdb->update( DB::workflows(), [ 'campaign_id' => 0 ], [ 'campaign_id' => $id ], [ '%d' ], [ '%d' ] );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$wpdb->delete( DB::campaigns(), [ 'id' => $id ], [ '%d' ] );
	}

	public static function assign_play( int $play_id, int $campaign_id ): void {
		if ( ! self::is_available() ) {
			return;
		}
		$campaign_id = self::resolve_id( $campaign_id );
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$wpdb->update( DB::workflows(), [ 'campaign_id' => $campaign_id ], [ 'id' => $play_id ], [ '%d' ], [ '%d' ] );
	}

	public static function fetch_aggregate_stats(): array {
		if ( ! self::is_available() ) {
			return [];
		}
		global $wpdb;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return $wpdb->get_results(
			$wpdb->prepare(
				'SELECT
					c.id,
					c.name,
					COUNT(DISTINCT w.id)                                                   AS plays,
					COUNT(DISTINCT et.id)                                                  AS emails_sent,
					SUM( COALESCE( et.opens_count, 0 ) )                                   AS opens,
					COUNT(DISTINCT cv.id)                                                  AS conversions,
					COALESCE(SUM(cv.order_total), 0)                                       AS revenue
				FROM %i c
				LEFT JOIN %i w  ON w.campaign_id  = c.id
				LEFT JOIN %i et ON et.workflow_id = w.id
							   AND et.sent_at     >= DATE_SUB(NOW(), INTERVAL 30 DAY)
				LEFT JOIN %i cv ON cv.workflow_id = w.id
							   AND cv.converted_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
				GROUP BY c.id
				ORDER BY revenue DESC',
				DB::campaigns(),
				DB::workflows(),
				DB::email_tracking(),
				DB::conversions()
			),
			ARRAY_A
		) ?: [];
		// phpcs:enable WordPress.DB.DirectDatabaseQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter
	}

	public static function fetch_plays_for_campaign( int $campaign_id ): array {
		if ( ! self::is_available() ) {
			return [];
		}
		global $wpdb;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return $wpdb->get_results(
			$wpdb->prepare(
				'SELECT
					w.id, w.name, w.trigger_slug, w.status,
					COUNT(DISTINCT et.id)            AS emails_sent,
					SUM( COALESCE( et.opens_count, 0 ) ) AS opens,
					COUNT(DISTINCT cv.id)            AS conversions,
					COALESCE(SUM(cv.order_total), 0) AS revenue
				FROM %i w
				LEFT JOIN %i et ON et.workflow_id = w.id AND et.sent_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
				LEFT JOIN %i cv ON cv.workflow_id = w.id AND cv.converted_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
				WHERE w.campaign_id = %d
				GROUP BY w.id
				ORDER BY revenue DESC',
				DB::workflows(),
				DB::email_tracking(),
				DB::conversions(),
				$campaign_id
			),
			ARRAY_A
		) ?: [];
		// phpcs:enable WordPress.DB.DirectDatabaseQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter
	}
}
