<?php
/**
 * Privacy-preserving built-in form analytics repository.
 *
 * @package SubmissionInbox
 */

namespace SUBIN\Forms;

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

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Analytics are stored in a plugin-owned aggregate event table.
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- The table name is built from the trusted WordPress prefix; dynamic values use placeholders.

/**
 * Stores anonymous form lifecycle events and reads portfolio funnels.
 */
class FormAnalyticsRepository {
	const EVENT_VIEW        = 'view';
	const EVENT_START       = 'start';
	const EVENT_INTERACTION = 'interaction';
	const EVENT_SUBMIT      = 'submit';

	/**
	 * Record an event once without retaining the client event id.
	 *
	 * @param int    $form_id Form id.
	 * @param string $event_type Event type.
	 * @param string $event_id Anonymous page-visit or submission id.
	 * @param string $occurred_at Optional local MySQL datetime used by tests/imports.
	 * @return bool|\WP_Error True when inserted, false when already recorded.
	 */
	public function record_event( $form_id, $event_type, $event_id, $occurred_at = '' ) {
		global $wpdb;

		$form_id    = absint( $form_id );
		$event_type = sanitize_key( (string) $event_type );
		$event_id   = trim( sanitize_text_field( (string) $event_id ) );
		if ( $form_id <= 0 || ! in_array( $event_type, self::event_types(), true ) || ! $this->valid_event_id( $event_id ) ) {
			return new \WP_Error( 'subin_form_analytics_invalid_event', __( 'The form analytics event is invalid.', 'submission-inbox' ) );
		}

		$created_at = $this->normalize_datetime( $occurred_at );
		$event_key  = hash_hmac(
			'sha256',
			'form-analytics:v1|' . $form_id . '|' . $event_type . '|' . $event_id,
			wp_salt( 'nonce' )
		);
		$table = $wpdb->prefix . 'subin_form_analytics_events';

		$inserted = $wpdb->query(
			$wpdb->prepare(
				"INSERT IGNORE INTO {$table} (form_id, event_type, event_key, occurred_on, created_at)
				VALUES (%d, %s, %s, %s, %s)",
				$form_id,
				$event_type,
				$event_key,
				substr( $created_at, 0, 10 ),
				$created_at
			)
		);

		if ( false === $inserted ) {
			return new \WP_Error(
				'subin_form_analytics_storage_failed',
				__( 'The form analytics event could not be stored.', 'submission-inbox' ),
				array( 'database_error' => sanitize_text_field( (string) $wpdb->last_error ) )
			);
		}

		return 1 === (int) $inserted;
	}

	/**
	 * Read 7- and 30-day funnels for many forms with one database query.
	 *
	 * @param array<int,int|string> $form_ids Form ids.
	 * @param string                $as_of Optional local date (Y-m-d), inclusive.
	 * @return array<int,array<string,array<string,int|float>>>
	 */
	public function get_funnel_aggregates( array $form_ids, $as_of = '' ) {
		global $wpdb;

		$form_ids = array_values( array_unique( array_filter( array_map( 'absint', $form_ids ) ) ) );
		if ( empty( $form_ids ) ) {
			return array();
		}

		$as_of_date = $this->normalize_date( $as_of );
		$timezone   = wp_timezone();
		$as_of_time = \DateTimeImmutable::createFromFormat( '!Y-m-d', $as_of_date, $timezone );
		if ( ! $as_of_time ) {
			$as_of_time = new \DateTimeImmutable( 'today', $timezone );
		}
		$start_7  = $as_of_time->modify( '-6 days' )->format( 'Y-m-d' );
		$start_30 = $as_of_time->modify( '-29 days' )->format( 'Y-m-d' );

		$aliases = array(
			'views_7'         => array( self::EVENT_VIEW, $start_7 ),
			'starts_7'        => array( self::EVENT_START, $start_7 ),
			'interactions_7'  => array( self::EVENT_INTERACTION, $start_7 ),
			'submits_7'       => array( self::EVENT_SUBMIT, $start_7 ),
			'views_30'        => array( self::EVENT_VIEW, $start_30 ),
			'starts_30'       => array( self::EVENT_START, $start_30 ),
			'interactions_30' => array( self::EVENT_INTERACTION, $start_30 ),
			'submits_30'      => array( self::EVENT_SUBMIT, $start_30 ),
		);
		$selects = array();
		$params  = array();
		foreach ( $aliases as $alias => $config ) {
			$selects[] = "SUM(CASE WHEN event_type = %s AND occurred_on >= %s THEN 1 ELSE 0 END) AS {$alias}";
			$params[]  = $config[0];
			$params[]  = $config[1];
		}

		$id_placeholders = implode( ',', array_fill( 0, count( $form_ids ), '%d' ) );
		$params          = array_merge( $params, $form_ids, array( $start_30, $as_of_date ) );
		$table           = $wpdb->prefix . 'subin_form_analytics_events';
		$sql             = "SELECT form_id, " . implode( ', ', $selects ) . "
			FROM {$table}
			WHERE form_id IN ({$id_placeholders})
				AND occurred_on BETWEEN %s AND %s
			GROUP BY form_id";
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Dynamic SELECT fragments and the ID list contain generated placeholders only.
		$rows = $wpdb->get_results( $wpdb->prepare( $sql, ...$params ), ARRAY_A );

		$aggregates = array();
		foreach ( $form_ids as $form_id ) {
			$aggregates[ $form_id ] = array(
				'7d'  => $this->empty_funnel(),
				'30d' => $this->empty_funnel(),
			);
		}
		foreach ( is_array( $rows ) ? $rows : array() as $row ) {
			$form_id = absint( $row['form_id'] ?? 0 );
			if ( ! isset( $aggregates[ $form_id ] ) ) {
				continue;
			}
			$aggregates[ $form_id ]['7d']  = $this->funnel_from_row( $row, '7' );
			$aggregates[ $form_id ]['30d'] = $this->funnel_from_row( $row, '30' );
		}

		return $aggregates;
	}

	/**
	 * Remove analytics events when forms are permanently deleted.
	 *
	 * @param array<int,int|string> $form_ids Form ids.
	 * @return bool
	 */
	public function delete_for_forms( array $form_ids ) {
		global $wpdb;

		$form_ids = array_values( array_unique( array_filter( array_map( 'absint', $form_ids ) ) ) );
		if ( empty( $form_ids ) ) {
			return true;
		}

		$table        = $wpdb->prefix . 'subin_form_analytics_events';
		$placeholders = implode( ',', array_fill( 0, count( $form_ids ), '%d' ) );
		$deleted      = $wpdb->query(
			$wpdb->prepare( "DELETE FROM {$table} WHERE form_id IN ({$placeholders})", ...$form_ids )
		);

		return false !== $deleted;
	}

	/**
	 * Allowed lifecycle events.
	 *
	 * @return array<int,string>
	 */
	public static function event_types() {
		return array( self::EVENT_VIEW, self::EVENT_START, self::EVENT_INTERACTION, self::EVENT_SUBMIT );
	}

	/**
	 * Build an empty funnel.
	 *
	 * @return array<string,int|float>
	 */
	private function empty_funnel() {
		return array(
			'views'            => 0,
			'starts'           => 0,
			'interactions'     => 0,
			'submissions'      => 0,
			'start_rate'       => 0.0,
			'conversion_rate'  => 0.0,
			'completion_rate'  => 0.0,
		);
	}

	/**
	 * Convert one SQL row to a normalized funnel.
	 *
	 * @param array<string,mixed> $row Aggregate row.
	 * @param string              $suffix Column suffix.
	 * @return array<string,int|float>
	 */
	private function funnel_from_row( array $row, $suffix ) {
		$views        = absint( $row[ 'views_' . $suffix ] ?? 0 );
		$starts       = absint( $row[ 'starts_' . $suffix ] ?? 0 );
		$interactions = absint( $row[ 'interactions_' . $suffix ] ?? 0 );
		$submissions  = absint( $row[ 'submits_' . $suffix ] ?? 0 );

		return array(
			'views'           => $views,
			'starts'          => $starts,
			'interactions'    => $interactions,
			'submissions'     => $submissions,
			'start_rate'      => $views > 0 ? round( ( $starts / $views ) * 100, 1 ) : 0.0,
			'conversion_rate' => $views > 0 ? round( ( $submissions / $views ) * 100, 1 ) : 0.0,
			'completion_rate' => $starts > 0 ? round( ( $submissions / $starts ) * 100, 1 ) : 0.0,
		);
	}

	/**
	 * Validate an opaque, short-lived client identifier.
	 *
	 * @param string $event_id Event id.
	 * @return bool
	 */
	private function valid_event_id( $event_id ) {
		$length = strlen( (string) $event_id );
		return $length >= 16 && $length <= 128 && 1 === preg_match( '/^[A-Za-z0-9._:-]+$/', (string) $event_id );
	}

	/**
	 * Normalize a local datetime.
	 *
	 * @param string $value Datetime.
	 * @return string
	 */
	private function normalize_datetime( $value ) {
		$value = sanitize_text_field( (string) $value );
		if ( 1 === preg_match( '/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/', $value ) ) {
			return $value;
		}

		return current_time( 'mysql' );
	}

	/**
	 * Normalize an inclusive local report date.
	 *
	 * @param string $value Date.
	 * @return string
	 */
	private function normalize_date( $value ) {
		$value = sanitize_text_field( (string) $value );
		if ( 1 === preg_match( '/^\d{4}-\d{2}-\d{2}$/', $value ) ) {
			return $value;
		}

		return wp_date( 'Y-m-d', current_time( 'timestamp' ), wp_timezone() );
	}
}
