<?php
/**
 * The numbers behind the charts.
 *
 * @package Dealroot
 */

namespace Dealroot;

defined( 'ABSPATH' ) || exit;

/**
 * Enquiries, customers, revenue and ad costs of a period, grouped for the charts of the reports
 * screen and of the PDF report. Enquiries, customers and revenue belong to the day of the
 * enquiry, as in all reports; spam is left out.
 *
 * phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
 */
class Chart_Data {

	/**
	 * Leads read at most for one chart.
	 */
	const MAX_ROWS = 50000;

	/**
	 * Channels shown by name; the others are added up as "Other".
	 */
	const CHANNELS = 7;

	/**
	 * Each channel keeps its colour on every chart, so its place in this list never changes.
	 * The order keeps neighbouring colours apart, also for colour-blind readers.
	 */
	const CHANNEL_ORDER = array( 'paid_search', 'organic_search', 'direct', 'paid_social', 'organic_social', 'referral', 'ai_assistant', 'email' );

	/**
	 * The currency of the charts: the one most revenue of the period came in, as a shop can
	 * sell in another currency than the one set in Dealroot.
	 *
	 * @param string $start From, UTC.
	 * @param string $end   To, UTC.
	 * @return string
	 */
	public static function currency( $start, $end ) {
		global $wpdb;
		static $found = array();

		$key = $start . '|' . $end;

		if ( ! isset( $found[ $key ] ) ) {
			$main          = $wpdb->get_var( $wpdb->prepare( "SELECT currency FROM %i WHERE status = 'won' AND currency <> '' AND value > 0 AND created_at >= %s AND created_at < %s GROUP BY currency ORDER BY SUM(value) DESC LIMIT 1", Installer::table(), $start, $end ) );
			$found[ $key ] = $main ? (string) $main : (string) Settings::get( 'currency' );
		}

		return $found[ $key ];
	}

	/**
	 * Start and end of a period in UTC, with the start of "All time" at the first day recorded.
	 *
	 * @param string $period Period key.
	 * @return array{0: string, 1: string}
	 */
	public static function bounds( $period ) {
		list( $from, $to ) = Reports::range( $period );

		if ( '' === $from ) {
			$since = Reports::tracking_since();
			$from  = '' !== $since ? get_gmt_from_date( $since . ' 00:00:00' ) : gmdate( 'Y-m-d H:i:s', time() - 30 * DAY_IN_SECONDS );
		}

		return array( $from, '' !== $to ? $to : gmdate( 'Y-m-d H:i:s' ) );
	}

	/**
	 * Enquiries, customers, revenue and ad costs per day, week or month.
	 *
	 * @param string $start From, UTC.
	 * @param string $end   To, UTC.
	 * @param string $unit  day, week or month; chosen by the length of the period when empty.
	 * @return array{unit: string, currency: string, rows: array<string, array{label: string, day: string, leads: int, won: int, revenue: float, cost: float, partial: string}>}
	 */
	public static function series( $start, $end, $unit = '' ) {
		global $wpdb;

		$currency = self::currency( $start, $end );
		$begin    = strtotime( $start . ' UTC' );
		$last     = min( time(), strtotime( $end . ' UTC' ) - 1 );
		$days     = max( 1, ( $last - $begin ) / DAY_IN_SECONDS );
		$auto     = $days <= 45 ? 'day' : ( $days <= 200 ? 'week' : 'month' );
		// Days, weeks or months as asked, with at most a year of single days.
		$unit = in_array( $unit, array( 'day', 'week', 'month' ), true ) && ! ( 'day' === $unit && $days > 400 ) ? $unit : $auto;
		$rows = array();

		// Every day, week or month of the period, also those without an enquiry.
		for ( $time = $begin; $time <= $last; $time += DAY_IN_SECONDS ) {
			$key = self::unit_key( $unit, $time );

			if ( ! isset( $rows[ $key ] ) ) {
				$rows[ $key ] = self::bucket( $unit, $time );
			}
		}

		// The last day, e.g. today, also when the period started at a later hour of the day.
		if ( ! isset( $rows[ self::unit_key( $unit, $last ) ] ) ) {
			$rows[ self::unit_key( $unit, $last ) ] = self::bucket( $unit, $last );
		}

		$leads = (array) $wpdb->get_results(
			$wpdb->prepare(
				"SELECT created_at, status, value, currency FROM %i WHERE status <> 'spam' AND created_at >= %s AND created_at < %s LIMIT %d",
				Installer::table(),
				$start,
				$end,
				self::MAX_ROWS
			)
		);

		foreach ( $leads as $lead ) {
			$time = strtotime( $lead->created_at . ' UTC' );
			$key  = self::unit_key( $unit, $time );

			if ( ! isset( $rows[ $key ] ) ) {
				$rows[ $key ] = self::bucket( $unit, $time );
			}

			++$rows[ $key ]['leads'];

			if ( 'won' === $lead->status ) {
				++$rows[ $key ]['won'];

				if ( '' === (string) $lead->currency || $currency === $lead->currency ) {
					$rows[ $key ]['revenue'] += (float) $lead->value;
				}
			}
		}

		/**
		 * Filters the ad costs per day for the charts, e.g. from the ad platforms in Dealroot Pro.
		 *
		 * @param array<string, float> $costs    Y-m-d => amount.
		 * @param string               $first    First day, Y-m-d.
		 * @param string               $last     Last day, Y-m-d.
		 * @param string               $currency Currency of the amounts.
		 */
		$costs = (array) apply_filters( 'dealroot_chart_costs', array(), wp_date( 'Y-m-d', $begin ), wp_date( 'Y-m-d', $last ), $currency );

		foreach ( $costs as $day => $amount ) {
			$time = strtotime( $day . ' 12:00 ' . wp_timezone_string() );
			$key  = self::unit_key( $unit, $time );

			if ( isset( $rows[ $key ] ) ) {
				$rows[ $key ]['cost'] += (float) $amount;
			}
		}

		ksort( $rows );

		// The day, week or month still running is not over yet, and a week or month cut off by
		// the period has fewer days than the others.
		if ( $rows ) {
			$keys    = array_keys( $rows );
			$running = strtotime( $end . ' UTC' ) >= time() - MINUTE_IN_SECONDS && self::unit_key( $unit, time() ) === end( $keys );

			if ( 'day' !== $unit && self::unit_key( $unit, $begin - DAY_IN_SECONDS ) === reset( $keys ) ) {
				$rows[ reset( $keys ) ]['partial'] = 'cut';
			}

			if ( $running ) {
				$rows[ end( $keys ) ]['partial'] = 'now';
			} elseif ( 'day' !== $unit && self::unit_key( $unit, $last + DAY_IN_SECONDS ) === end( $keys ) ) {
				$rows[ end( $keys ) ]['partial'] = 'cut';
			}
		}

		return array(
			'unit'     => $unit,
			'currency' => $currency,
			'costs'    => (bool) $costs,
			'rows'     => $rows,
		);
	}

	/**
	 * An empty day, week or month.
	 *
	 * @param string $unit day, week or month.
	 * @param int    $time A time in it.
	 * @return array
	 */
	private static function bucket( $unit, $time ) {
		return array(
			'label'   => self::unit_label( $unit, $time ),
			'long'    => self::unit_label( $unit, $time, true ),
			'leads'   => 0,
			'won'     => 0,
			'revenue' => 0.0,
			'cost'    => 0.0,
			'partial' => '',
		);
	}

	/**
	 * Key of the day, week or month of a time, in the time zone of the site.
	 *
	 * @param string $unit day, week or month.
	 * @param int    $time Timestamp.
	 * @return string
	 */
	public static function unit_key( $unit, $time ) {
		return 'day' === $unit ? wp_date( 'Y-m-d', $time ) : ( 'week' === $unit ? wp_date( 'o-\WW', $time ) : wp_date( 'Y-m', $time ) );
	}

	/**
	 * Label of the day, week or month of a time; a week by its Monday.
	 *
	 * @param string $unit day, week or month.
	 * @param int    $time Timestamp.
	 * @param bool   $long With the year, for the tooltip.
	 * @return string
	 */
	public static function unit_label( $unit, $time, $long = false ) {
		if ( 'week' === $unit ) {
			$time -= ( (int) wp_date( 'N', $time ) - 1 ) * DAY_IN_SECONDS;
		}

		if ( 'month' === $unit ) {
			return wp_date( $long ? 'F Y' : 'M', $time );
		}

		if ( $long ) {
			/* translators: %s: date of a Monday */
			return 'week' === $unit ? sprintf( __( 'Week of %s', 'dealroot' ), wp_date( get_option( 'date_format' ), $time ) ) : wp_date( get_option( 'date_format' ), $time );
		}

		return wp_date( 'j.n.', $time );
	}

	/**
	 * Enquiries, customers, open and lost deals and revenue per channel, the biggest first, with
	 * the small ones added up as "Other".
	 *
	 * @param string $start From, UTC.
	 * @param string $end   To, UTC.
	 * @return array<string, array{label: string, slot: int, leads: int, won: int, lost: int, open: int, revenue: float}>
	 */
	public static function channels( $start, $end ) {
		global $wpdb;

		$currency = self::currency( $start, $end );
		$rows     = (array) $wpdb->get_results(
			$wpdb->prepare(
				"SELECT channel, status, COUNT(*) AS n, SUM(CASE WHEN status = 'won' AND (currency = '' OR currency = %s) THEN COALESCE(value, 0) ELSE 0 END) AS revenue
				FROM %i WHERE status <> 'spam' AND created_at >= %s AND created_at < %s GROUP BY channel, status",
				$currency,
				Installer::table(),
				$start,
				$end
			)
		);

		$channels = array();

		foreach ( $rows as $row ) {
			$key = in_array( $row->channel, self::CHANNEL_ORDER, true ) ? (string) $row->channel : 'other';

			if ( ! isset( $channels[ $key ] ) ) {
				$channels[ $key ] = array(
					'label'   => 'other' === $key ? __( 'Other', 'dealroot' ) : Attribution::channel_label( $key ),
					'slot'    => 'other' === $key ? 0 : (int) array_search( $key, self::CHANNEL_ORDER, true ) + 1,
					'leads'   => 0,
					'won'     => 0,
					'lost'    => 0,
					'open'    => 0,
					'revenue' => 0.0,
				);
			}

			$n                            = (int) $row->n;
			$channels[ $key ]['leads']   += $n;
			$channels[ $key ]['revenue'] += (float) $row->revenue;

			if ( 'won' === $row->status ) {
				$channels[ $key ]['won'] += $n;
			} elseif ( 'lost' === $row->status ) {
				$channels[ $key ]['lost'] += $n;
			} else {
				$channels[ $key ]['open'] += $n;
			}
		}

		// The channels in their fixed order, so each keeps its colour and its neighbours.
		uasort(
			$channels,
			static function ( $a, $b ) {
				return ( $a['slot'] ? $a['slot'] : 99 ) <=> ( $b['slot'] ? $b['slot'] : 99 );
			}
		);

		return $channels;
	}

	/**
	 * Enquiries per day of the week and hour, in the time zone of the site.
	 *
	 * @param string $start From, UTC.
	 * @param string $end   To, UTC.
	 * @return array{cells: array<int, array<int, int>>, most: int, total: int}
	 */
	public static function hours( $start, $end ) {
		global $wpdb;

		$cells = array_fill( 1, 7, array_fill( 0, 24, 0 ) );
		$times = (array) $wpdb->get_col(
			$wpdb->prepare(
				"SELECT created_at FROM %i WHERE status <> 'spam' AND form_plugin <> 'manual' AND created_at >= %s AND created_at < %s LIMIT %d",
				Installer::table(),
				$start,
				$end,
				self::MAX_ROWS
			)
		);
		$zone  = wp_timezone();

		foreach ( $times as $time ) {
			$local = ( new \DateTimeImmutable( $time, new \DateTimeZone( 'UTC' ) ) )->setTimezone( $zone );

			++$cells[ (int) $local->format( 'N' ) ][ (int) $local->format( 'G' ) ];
		}

		$most = 0;
		foreach ( $cells as $hours ) {
			$most = max( $most, max( $hours ) );
		}

		return array(
			'cells' => $cells,
			'most'  => $most,
			'total' => count( $times ),
		);
	}

	/**
	 * How fast enquiries were answered, and how many of them became customers.
	 *
	 * @param string $start From, UTC.
	 * @param string $end   To, UTC.
	 * @return array<string, array{label: string, leads: int, won: int}>
	 */
	public static function speed( $start, $end ) {
		global $wpdb;

		$buckets = array(
			'hour'  => array( HOUR_IN_SECONDS, __( 'Within an hour', 'dealroot' ) ),
			'four'  => array( 4 * HOUR_IN_SECONDS, __( '1 to 4 hours', 'dealroot' ) ),
			'day'   => array( DAY_IN_SECONDS, __( '4 to 24 hours', 'dealroot' ) ),
			'three' => array( 3 * DAY_IN_SECONDS, __( '1 to 3 days', 'dealroot' ) ),
			'later' => array( PHP_INT_MAX, __( 'Later', 'dealroot' ) ),
		);
		$out     = array();

		foreach ( $buckets as $key => $bucket ) {
			$out[ $key ] = array(
				'label' => $bucket[1],
				'leads' => 0,
				'won'   => 0,
			);
		}

		// The first message sent, or the first change of the status, whichever came first.
		$rows = (array) $wpdb->get_results(
			$wpdb->prepare(
				"SELECT l.created_at, l.contacted_at, l.status,
					( SELECT MIN( m.created_at ) FROM %i m WHERE m.lead_id = l.id AND m.direction = 'out' AND m.channel <> 'confirm' ) AS replied_at
				FROM %i l
				WHERE l.form_plugin NOT IN ('manual', 'woocommerce', 'order') AND l.ai_kind NOT IN ('order', 'spam', 'job') AND l.status <> 'spam'
					AND l.created_at >= %s AND l.created_at < %s
				ORDER BY l.created_at DESC LIMIT 5000",
				Messages::table(),
				Installer::table(),
				$start,
				$end
			)
		);

		foreach ( $rows as $row ) {
			$times = array_filter( array( (string) $row->contacted_at, (string) $row->replied_at ) );

			if ( ! $times ) {
				continue;
			}

			$wait = max( 0, strtotime( min( $times ) . ' UTC' ) - strtotime( $row->created_at . ' UTC' ) );

			foreach ( $buckets as $key => $bucket ) {
				if ( $wait < $bucket[0] ) {
					++$out[ $key ]['leads'];
					$out[ $key ]['won'] += 'won' === $row->status ? 1 : 0;
					break;
				}
			}
		}

		return $out;
	}

	/**
	 * Enquiries per device.
	 *
	 * @param string $start From, UTC.
	 * @param string $end   To, UTC.
	 * @return array<string, array{label: string, leads: int, won: int}>
	 */
	public static function devices( $start, $end ) {
		global $wpdb;

		$rows = (array) $wpdb->get_results(
			$wpdb->prepare(
				"SELECT device, COUNT(*) AS n, SUM(CASE WHEN status = 'won' THEN 1 ELSE 0 END) AS won FROM %i
				WHERE status <> 'spam' AND device <> '' AND created_at >= %s AND created_at < %s GROUP BY device ORDER BY n DESC",
				Installer::table(),
				$start,
				$end
			)
		);
		$out  = array();

		foreach ( $rows as $row ) {
			$out[ (string) $row->device ] = array(
				'label' => Reports::label( 'device', (string) $row->device ),
				'leads' => (int) $row->n,
				'won'   => (int) $row->won,
			);
		}

		return $out;
	}

	/**
	 * Why deals were lost.
	 *
	 * @param string $start From, UTC.
	 * @param string $end   To, UTC.
	 * @return array<string, int> Reason => deals, the most first.
	 */
	public static function lost( $start, $end ) {
		global $wpdb;

		$rows   = (array) $wpdb->get_results( $wpdb->prepare( "SELECT lost_reason, COUNT(*) AS n FROM %i WHERE status = 'lost' AND created_at >= %s AND created_at < %s GROUP BY lost_reason", Installer::table(), $start, $end ) );
		$labels = Lost_Reasons::labels();
		$counts = array();

		foreach ( $rows as $row ) {
			$label            = $labels[ $row->lost_reason ] ?? __( 'No reason given', 'dealroot' );
			$counts[ $label ] = ( $counts[ $label ] ?? 0 ) + (int) $row->n;
		}

		arsort( $counts );

		return $counts;
	}

	/**
	 * Totals of a period, for the figures at the top.
	 *
	 * @param string $start From, UTC.
	 * @param string $end   To, UTC.
	 * @return array{leads: int, won: int, revenue: float, currency: string}
	 */
	public static function totals( $start, $end ) {
		global $wpdb;

		$row = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT COUNT(*) AS leads, SUM(CASE WHEN status = 'won' THEN 1 ELSE 0 END) AS won,
					SUM(CASE WHEN status = 'won' AND (currency = '' OR currency = %s) THEN COALESCE(value, 0) ELSE 0 END) AS revenue
				FROM %i WHERE status <> 'spam' AND created_at >= %s AND created_at < %s",
				self::currency( $start, $end ),
				Installer::table(),
				$start,
				$end
			)
		);

		return array(
			'leads'    => (int) ( $row->leads ?? 0 ),
			'won'      => (int) ( $row->won ?? 0 ),
			'revenue'  => (float) ( $row->revenue ?? 0 ),
			'currency' => self::currency( $start, $end ),
		);
	}

	/**
	 * Enquiries of each of the last days and of the same days a week before, in the time zone
	 * of the site; today is the last.
	 *
	 * @param int $count Days.
	 * @return array<string, array{label: string, long: string, now: int, before: int}>
	 */
	public static function recent_days( $count = 7 ) {
		global $wpdb;

		$zone  = wp_timezone();
		$today = ( new \DateTimeImmutable( 'today', $zone ) );
		$first = $today->modify( '-' . ( 2 * $count - 1 ) . ' days' );
		$days  = array();

		for ( $i = $count - 1; $i >= 0; $i-- ) {
			$day = $today->modify( '-' . $i . ' days' );

			$days[ $day->format( 'Y-m-d' ) ] = array(
				'label'  => wp_date( 'D', $day->getTimestamp() ),
				'long'   => wp_date( get_option( 'date_format' ), $day->getTimestamp() ),
				'now'    => 0,
				'before' => 0,
			);
		}

		$times = (array) $wpdb->get_col(
			$wpdb->prepare(
				"SELECT created_at FROM %i WHERE status <> 'spam' AND created_at >= %s LIMIT %d",
				Installer::table(),
				$first->setTimezone( new \DateTimeZone( 'UTC' ) )->format( 'Y-m-d H:i:s' ),
				self::MAX_ROWS
			)
		);

		foreach ( $times as $time ) {
			$local = ( new \DateTimeImmutable( $time, new \DateTimeZone( 'UTC' ) ) )->setTimezone( $zone )->format( 'Y-m-d' );

			if ( isset( $days[ $local ] ) ) {
				++$days[ $local ]['now'];
				continue;
			}

			$later = ( new \DateTimeImmutable( $local, $zone ) )->modify( '+' . $count . ' days' )->format( 'Y-m-d' );

			if ( isset( $days[ $later ] ) ) {
				++$days[ $later ]['before'];
			}
		}

		return $days;
	}

	/**
	 * Revenue won this month, added up day by day up to today, and last month day by day.
	 *
	 * @param string $currency Currency.
	 * @return array{days: int, now: float[], before: float[]}
	 */
	public static function month_pace( $currency ) {
		global $wpdb;

		$zone   = wp_timezone();
		$start  = ( new \DateTimeImmutable( 'first day of this month', $zone ) )->setTime( 0, 0 );
		$prev   = $start->modify( '-1 month' );
		$today  = (int) ( new \DateTimeImmutable( 'now', $zone ) )->format( 'j' );
		$utc    = new \DateTimeZone( 'UTC' );
		$rows   = (array) $wpdb->get_results(
			$wpdb->prepare(
				"SELECT won_at, value FROM %i WHERE status = 'won' AND won_at >= %s AND ( currency = '' OR currency = %s ) LIMIT %d",
				Installer::table(),
				$prev->setTimezone( $utc )->format( 'Y-m-d H:i:s' ),
				$currency,
				self::MAX_ROWS
			)
		);
		$now    = array_fill( 0, $today, 0.0 );
		$before = array_fill( 0, (int) $prev->format( 't' ), 0.0 );

		foreach ( $rows as $row ) {
			$local = ( new \DateTimeImmutable( (string) $row->won_at, $utc ) )->setTimezone( $zone );
			$day   = (int) $local->format( 'j' ) - 1;

			if ( $local >= $start ) {
				// A deal dated later today, e.g. by another time zone, still counts for today.
				$now[ min( $day, $today - 1 ) ] += (float) $row->value;
			} else {
				$before[ $day ] += (float) $row->value;
			}
		}

		// Added up, so each day shows what was won up to it.
		$sum = static function ( array $days ) {
			$total = 0.0;
			foreach ( $days as $i => $value ) {
				$total     += $value;
				$days[ $i ] = $total;
			}

			return $days;
		};

		return array(
			'days'   => (int) $start->format( 't' ),
			'now'    => $sum( $now ),
			'before' => $sum( $before ),
		);
	}
}