<?php
/**
 * Form analytics.
 *
 * @package Dealroot
 */

namespace Dealroot;

defined( 'ABSPATH' ) || exit;

/**
 * Daily counters per form: how many visitors started it, how many sent it, and the field
 * visitors were at when they left without sending. Only totals and field labels are stored.
 *
 * phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
 */
class Form_Stats {

	/**
	 * New forms and fields accepted per day. The counters are public, so a flood of invented
	 * names must not fill the table.
	 */
	const MAX_FORMS_PER_DAY  = 50;
	const MAX_FIELDS_PER_DAY = 30;

	/**
	 * Day from which every visitor is counted, also forms sent by script. Earlier days counted
	 * fewer and are left out of the figures.
	 */
	const SINCE_OPTION = 'dealroot_form_counting_since';

	/**
	 * Starts the counting of every visitor today: the counters of today, kept the old way until
	 * now, start again.
	 */
	public static function start_counting() {
		global $wpdb;

		$today = wp_date( 'Y-m-d' );

		$wpdb->query( $wpdb->prepare( 'DELETE FROM %i WHERE day >= %s', Installer::forms_table(), $today ) );
		update_option( self::SINCE_OPTION, $today, false );
	}

	/**
	 * Day from which the forms are counted the way they are now, '' for a new site.
	 *
	 * @return string Y-m-d
	 */
	public static function since() {
		$since = (string) get_option( self::SINCE_OPTION, '' );

		return preg_match( '/^\d{4}-\d{2}-\d{2}$/', $since ) ? $since : '';
	}

	/**
	 * Days of a period in the site's time zone, from the day the forms are counted as now.
	 *
	 * @param string $from UTC start, '' for all time.
	 * @param string $to   UTC end, '' for now.
	 * @return array{0: string, 1: string}
	 */
	private static function days( $from, $to ) {
		list( $start, $end ) = Reports::sql_days( $from, $to );

		return array( max( $start, self::since() ), $end );
	}

	/**
	 * Counts a form that was started and either sent or left.
	 *
	 * @param string $form   Form name as shown on the page.
	 * @param bool   $sent   Whether it was sent.
	 * @param string $field  Label of the last field used before leaving.
	 * @param string $source Form plugin and form ID, e.g. "cf7:5", to use the form's own title.
	 * @return bool
	 */
	public static function record( $form, $sent, $field = '', $source = '' ) {
		if ( preg_match( '/^([a-z0-9]+):(\d{1,10})$/', $source, $match ) && Form_Selection::is_excluded( $match[1], $match[2] ) ) {
			return false;
		}

		$form = mb_substr( sanitize_text_field( self::title( $form, $source ) ), 0, 191 );

		if ( '' === $form || ( ! self::exists( $form, '' ) && self::count_today( '' ) >= self::MAX_FORMS_PER_DAY ) ) {
			return false;
		}

		$ok = self::add( $form, '', 1, $sent ? 1 : 0, $sent ? 0 : 1 );

		$field = mb_substr( sanitize_text_field( $field ), 0, 191 );

		if ( ! $sent && '' !== $field && ( self::exists( $form, $field ) || self::count_today( $form ) < self::MAX_FIELDS_PER_DAY ) ) {
			$ok = self::add( $form, $field, 0, 0, 1 ) && $ok;
		}

		return $ok;
	}

	/**
	 * Title the form plugin gives the form, as stored with its leads.
	 *
	 * @param string $form   Name from the page.
	 * @param string $source Form plugin key and form ID.
	 * @return string
	 */
	public static function title( $form, $source ) {
		if ( ! preg_match( '/^([a-z0-9]+):(\d{1,10})$/', $source, $match ) ) {
			return $form;
		}

		foreach ( Forms\Registry::integrations() as $class ) {
			if ( is_a( $class, Forms\Integration::class, true ) && $class::key() === $match[1] && $class::is_available() ) {
				$title = $class::form_title( (int) $match[2] );

				return '' !== $title ? $title : $form;
			}
		}

		return $form;
	}

	/**
	 * Forms of a period, most started first.
	 *
	 * @param string $from  UTC start, '' for all time.
	 * @param string $to    UTC end, '' for now.
	 * @param int    $limit Maximum forms.
	 * @return array<string, array{starts: int, sent: int, left: int, field: string, field_left: int}>
	 */
	public static function report( $from, $to, $limit = 10 ) {
		global $wpdb;

		$table               = Installer::forms_table();
		list( $start, $end ) = self::days( $from, $to );

		$totals = (array) $wpdb->get_results(
			$wpdb->prepare(
				"SELECT form, SUM(starts) AS starts, SUM(sent) AS sent, SUM(left_at) AS left_at FROM %i
				WHERE field = '' AND day >= %s AND day < %s
				GROUP BY form ORDER BY starts DESC LIMIT %d",
				$table,
				$start,
				$end,
				(int) $limit
			)
		);

		$forms = array();

		foreach ( $totals as $row ) {
			if ( (int) $row->starts > 0 ) {
				$forms[ (string) $row->form ] = array(
					'starts'     => (int) $row->starts,
					'sent'       => (int) $row->sent,
					'left'       => (int) $row->left_at,
					'field'      => '',
					'field_left' => 0,
				);
			}
		}

		if ( ! $forms ) {
			return array();
		}

		$names = array_keys( $forms );

		$fields = (array) $wpdb->get_results(
			$wpdb->prepare(
				"SELECT form, field, SUM(left_at) AS left_at FROM %i
				WHERE field <> '' AND day >= %s AND day < %s AND form IN (" . implode( ',', array_fill( 0, count( $names ), '%s' ) ) . ')
				GROUP BY form, field',
				array_merge( array( $table, $start, $end ), $names )
			)
		);

		foreach ( $fields as $row ) {
			$form = (string) $row->form;

			if ( isset( $forms[ $form ] ) && (int) $row->left_at > $forms[ $form ]['field_left'] ) {
				$forms[ $form ]['field']      = (string) $row->field;
				$forms[ $form ]['field_left'] = (int) $row->left_at;
			}
		}

		return $forms;
	}

	/**
	 * Every field visitors left the forms at, per form, the most left first.
	 *
	 * @param string   $from  UTC start, '' for all time.
	 * @param string   $to    UTC end, '' for now.
	 * @param string[] $names Form names.
	 * @return array<string, array<string, int>> Form => field => visitors who left there.
	 */
	public static function fields( $from, $to, array $names ) {
		global $wpdb;

		$names = array_values( array_filter( array_map( 'strval', $names ), 'strlen' ) );

		if ( ! $names ) {
			return array();
		}

		list( $start, $end ) = self::days( $from, $to );

		$rows = (array) $wpdb->get_results(
			$wpdb->prepare(
				"SELECT form, field, SUM(left_at) AS left_at FROM %i
				WHERE field <> '' AND day >= %s AND day < %s AND form IN (" . implode( ',', array_fill( 0, count( $names ), '%s' ) ) . ')
				GROUP BY form, field ORDER BY left_at DESC',
				array_merge( array( Installer::forms_table(), $start, $end ), $names )
			)
		);

		$fields = array();

		foreach ( $rows as $row ) {
			if ( (int) $row->left_at > 0 ) {
				$fields[ (string) $row->form ][ (string) $row->field ] = (int) $row->left_at;
			}
		}

		return $fields;
	}

	/**
	 * The fields where most visitors left a form without sending it, over all forms.
	 *
	 * @param string $from  UTC start, '' for all time.
	 * @param string $to    UTC end, '' for now.
	 * @param int    $limit Maximum fields.
	 * @return array<int, array{form: string, field: string, left: int}>
	 */
	public static function stuck_fields( $from, $to, $limit = 6 ) {
		global $wpdb;

		list( $start, $end ) = self::days( $from, $to );

		$rows = (array) $wpdb->get_results(
			$wpdb->prepare(
				"SELECT form, field, SUM(left_at) AS left_at FROM %i
				WHERE field <> '' AND day >= %s AND day < %s
				GROUP BY form, field ORDER BY left_at DESC LIMIT %d",
				Installer::forms_table(),
				$start,
				$end,
				(int) $limit
			)
		);

		return array_values(
			array_filter(
				array_map(
					static function ( $row ) {
						return array(
							'form'  => (string) $row->form,
							'field' => (string) $row->field,
							'left'  => (int) $row->left_at,
						);
					},
					$rows
				),
				static function ( $row ) {
					return $row['left'] > 0;
				}
			)
		);
	}

	/**
	 * Removes counters older than the retention window.
	 *
	 * @param int $days Age in days.
	 * @return int Rows deleted.
	 */
	public static function purge_older_than( $days ) {
		global $wpdb;

		if ( $days < 1 ) {
			return 0;
		}

		return (int) $wpdb->query( $wpdb->prepare( 'DELETE FROM %i WHERE day < %s', Installer::forms_table(), wp_date( 'Y-m-d', time() - (int) $days * DAY_IN_SECONDS ) ) );
	}

	/**
	 * Whether today's counter for a form or field exists.
	 *
	 * @param string $form  Form name.
	 * @param string $field Field label, '' for the form.
	 * @return bool
	 */
	private static function exists( $form, $field ) {
		global $wpdb;

		$day = wp_date( 'Y-m-d' );

		return (bool) $wpdb->get_var( $wpdb->prepare( 'SELECT 1 FROM %i WHERE hash = %s', Installer::forms_table(), md5( $day . '|' . $form . '|' . $field ) ) );
	}

	/**
	 * Counters of today: forms when $form is '', otherwise the fields of that form.
	 *
	 * @param string $form Form name or ''.
	 * @return int
	 */
	private static function count_today( $form ) {
		global $wpdb;

		if ( '' === $form ) {
			return (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE day = %s AND field = ''", Installer::forms_table(), wp_date( 'Y-m-d' ) ) );
		}

		return (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE day = %s AND form = %s AND field <> ''", Installer::forms_table(), wp_date( 'Y-m-d' ), $form ) );
	}

	/**
	 * Adds to the counters of today.
	 *
	 * @param string $form    Form name.
	 * @param string $field   Field label, '' for the form itself.
	 * @param int    $starts  Started.
	 * @param int    $sent    Sent.
	 * @param int    $left_at Left without sending.
	 * @return bool
	 */
	private static function add( $form, $field, $starts, $sent, $left_at ) {
		global $wpdb;

		$day = wp_date( 'Y-m-d' );

		$result = $wpdb->query(
			$wpdb->prepare(
				'INSERT INTO %i (day, hash, form, field, starts, sent, left_at) VALUES (%s, %s, %s, %s, %d, %d, %d) ON DUPLICATE KEY UPDATE starts = starts + VALUES(starts), sent = sent + VALUES(sent), left_at = left_at + VALUES(left_at)',
				Installer::forms_table(),
				$day,
				md5( $day . '|' . $form . '|' . $field ),
				$form,
				$field,
				$starts,
				$sent,
				$left_at
			)
		);

		return false !== $result;
	}
}