<?php
/**
 * Writes customer rows to Statbird's own table.
 *
 * @package Statbird
 */

namespace Statbird;

defined( 'ABSPATH' ) || exit;

/**
 * The wp_statbird_customers table.
 */
class Store {

	/**
	 * Table name.
	 *
	 * @return string
	 */
	public static function table() {
		global $wpdb;
		return $wpdb->prefix . 'statbird_customers';
	}

	/**
	 * Inserts or updates customer rows in one query.
	 *
	 * Only the purchase facts are written here. Scores, groups and risk are added by the
	 * scoring step and are left untouched.
	 *
	 * @param array[] $rows Rows from Orders::customers_page() or Orders::customer().
	 * @param string  $now    MySQL datetime (GMT) stamped on every row.
	 * @param int     $run_id Rebuild run the rows belong to.
	 * @return int Rows written.
	 */
	public static function upsert( array $rows, $now, $run_id ) {
		global $wpdb;
		if ( ! $rows ) {
			return 0;
		}
		$values = array();
		foreach ( $rows as $r ) {
			$orders   = (int) $r['orders'];
			$first_ts = strtotime( $r['first_order'] . ' UTC' );
			$last_ts  = strtotime( $r['last_order'] . ' UTC' );
			// Average days between purchases; 0 until a customer has bought twice.
			$gap      = ( $orders > 1 && $last_ts > $first_ts ) ? round( ( $last_ts - $first_ts ) / DAY_IN_SECONDS / ( $orders - 1 ), 2 ) : 0;
			$values[] = $wpdb->prepare(
				'(%s, %d, %s, %s, %s, %s, %d, %f, %f, %s, %d)',
				$r['customer_key'],
				(int) $r['user_id'],
				substr( (string) $r['email'], 0, 190 ),
				substr( (string) ( $r['name'] ?? '' ), 0, 190 ),
				$r['first_order'],
				$r['last_order'],
				$orders,
				(float) $r['spent'],
				$gap,
				$now,
				$run_id
			);
		}
		$table = self::table();
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- every value is prepared above; $table is the plugin's own table.
		$wpdb->query(
			"INSERT INTO $table (customer_key, user_id, email, name, first_order, last_order, orders, spent, avg_gap_days, updated_at, run_id)
			VALUES " . implode( ',', $values ) . '
			ON DUPLICATE KEY UPDATE user_id = VALUES(user_id), email = VALUES(email), name = VALUES(name), first_order = VALUES(first_order),
				last_order = VALUES(last_order), orders = VALUES(orders), spent = VALUES(spent),
				avg_gap_days = VALUES(avg_gap_days), updated_at = VALUES(updated_at), run_id = VALUES(run_id)'
		);
		// phpcs:enable
		return count( $rows );
	}

	/**
	 * Removes a customer who no longer has any counted orders.
	 *
	 * @param string $customer_key MD5 key.
	 */
	public static function delete( $customer_key ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->delete( self::table(), array( 'customer_key' => $customer_key ), array( '%s' ) );
	}

	/**
	 * After a full rebuild, removes rows from earlier runs: customers who no longer have any
	 * counted orders. Rows refreshed by an order change during the rebuild carry the current
	 * run, so they stay.
	 *
	 * @param int $run_id The run that just finished.
	 * @return int Rows removed.
	 */
	public static function prune_except( $run_id ) {
		global $wpdb;
		$table = self::table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name only.
		return (int) $wpdb->query( $wpdb->prepare( "DELETE FROM $table WHERE run_id <> %d", $run_id ) );
	}

	/**
	 * Number of customers stored.
	 *
	 * @return int
	 */
	public static function count() {
		global $wpdb;
		$table = self::table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name only.
		return (int) $wpdb->get_var( "SELECT COUNT(*) FROM $table" );
	}
}