<?php

namespace SerpIQ\Cron;

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

final class Alerts {
	private static ?Alerts $instance = null;

	private const POSITION_DROP_THRESHOLD = 3.0;
	private const CTR_DROP_RATIO = 0.20;
	private const TRAFFIC_DROP_RATIO = 0.20;
	private const IMPRESSIONS_SPIKE_RATIO = 2.0;
	private const MIN_IMPRESSIONS_PREV = 20;

	public static function instance(): Alerts {
		if ( null === self::$instance ) {
			self::$instance = new self();
		}
		return self::$instance;
	}

	public function run(): void {
		global $wpdb;

		$table_q = $wpdb->prefix . 'serpiq_queries';
		$table_a = $wpdb->prefix . 'serpiq_alerts';

		$current_end   = gmdate( 'Y-m-d', strtotime( '-1 day' ) );
		$current_start = gmdate( 'Y-m-d', strtotime( '-7 day' ) );
		$previous_end  = gmdate( 'Y-m-d', strtotime( '-8 day' ) );
		$previous_start = gmdate( 'Y-m-d', strtotime( '-14 day' ) );

		$created = current_time( 'mysql', true );

		$sql_prev = "
			SELECT
				query,
				COALESCE(SUM(clicks),0) AS clicks,
				COALESCE(SUM(impressions),0) AS impressions,
				CASE WHEN SUM(impressions) > 0 THEN SUM(clicks)/SUM(impressions) ELSE 0 END AS ctr,
				CASE WHEN SUM(impressions) > 0 THEN SUM(position*impressions)/SUM(impressions) ELSE 0 END AS position
			FROM %i
			WHERE date BETWEEN %s AND %s
			GROUP BY query
			HAVING SUM(impressions) >= %d
		";
		$prev_rows = $wpdb->get_results( $wpdb->prepare( $sql_prev, array_merge( array( $table_q ), array( $previous_start, $previous_end, self::MIN_IMPRESSIONS_PREV ) ) ), ARRAY_A );
		$prev_by_query = array();
		foreach ( $prev_rows as $r ) {
			$prev_by_query[ $r['query'] ] = $r;
		}

		$sql_curr = "
			SELECT
				query,
				COALESCE(SUM(clicks),0) AS clicks,
				COALESCE(SUM(impressions),0) AS impressions,
				CASE WHEN SUM(impressions) > 0 THEN SUM(clicks)/SUM(impressions) ELSE 0 END AS ctr,
				CASE WHEN SUM(impressions) > 0 THEN SUM(position*impressions)/SUM(impressions) ELSE 0 END AS position
			FROM %i
			WHERE date BETWEEN %s AND %s
			GROUP BY query
		";
		$curr_rows = $wpdb->get_results( $wpdb->prepare( $sql_curr, array_merge( array( $table_q ), array( $current_start, $current_end ) ) ), ARRAY_A );

		$to_insert = array();
		foreach ( $curr_rows as $curr ) {
			$q = $curr['query'];
			$prev = $prev_by_query[ $q ] ?? null;
			if ( ! $prev || (float) $prev['impressions'] < self::MIN_IMPRESSIONS_PREV ) {
				continue;
			}

			$prev_pos = (float) $prev['position'];
			$curr_pos = (float) $curr['position'];
			if ( $curr_pos - $prev_pos >= self::POSITION_DROP_THRESHOLD ) {
				$to_insert[] = array(
					'position_drop',
					'query',
					$q,
					'position',
					$prev_pos,
					$curr_pos,
					$previous_start,
					$previous_end,
					$current_start,
					$current_end,
					$created,
				);
			}

			$prev_ctr = (float) $prev['ctr'];
			$curr_ctr = (float) $curr['ctr'];
			if ( $prev_ctr > 0.001 && ( $prev_ctr - $curr_ctr ) / $prev_ctr >= self::CTR_DROP_RATIO ) {
				$to_insert[] = array(
					'ctr_drop',
					'query',
					$q,
					'ctr',
					$prev_ctr,
					$curr_ctr,
					$previous_start,
					$previous_end,
					$current_start,
					$current_end,
					$created,
				);
			}

			$prev_clicks = (float) $prev['clicks'];
			$curr_clicks = (float) $curr['clicks'];
			if ( $prev_clicks >= 5 && ( $prev_clicks - $curr_clicks ) / $prev_clicks >= self::TRAFFIC_DROP_RATIO ) {
				$to_insert[] = array(
					'traffic_drop',
					'query',
					$q,
					'clicks',
					$prev_clicks,
					$curr_clicks,
					$previous_start,
					$previous_end,
					$current_start,
					$current_end,
					$created,
				);
			}

			$prev_impr = (float) $prev['impressions'];
			$curr_impr = (float) $curr['impressions'];
			if ( $prev_impr >= 10 && $curr_impr >= $prev_impr * self::IMPRESSIONS_SPIKE_RATIO ) {
				$to_insert[] = array(
					'impressions_spike',
					'query',
					$q,
					'impressions',
					$prev_impr,
					$curr_impr,
					$previous_start,
					$previous_end,
					$current_start,
					$current_end,
					$created,
				);
			}
		}

		foreach ( $to_insert as $row ) {
			$wpdb->insert(
				$table_a,
				array(
					'alert_type'           => $row[0],
					'dimension_type'       => $row[1],
					'dimension_value'      => $row[2],
					'metric'               => $row[3],
					'previous_value'       => $row[4],
					'current_value'        => $row[5],
					'period_previous_start' => $row[6],
					'period_previous_end'   => $row[7],
					'period_current_start' => $row[8],
					'period_current_end'   => $row[9],
					'created_at'           => $row[10],
				),
				array( '%s', '%s', '%s', '%s', '%f', '%f', '%s', '%s', '%s', '%s', '%s' )
			);
		}
	}
}
