<?php
/**
 * The SMS log table.
 *
 * @package VibrateSMS
 */

namespace VibrateSMS;

defined( 'ABSPATH' ) || exit;

/**
 * One row per recipient per send. Rows of one API request share a batch_key,
 * which is also the request's Idempotency-Key, so a retry of the same batch
 * can never be billed or delivered twice.
 *
 * Status lifecycle:
 *   pending  -> sending -> queued -> in_progress -> sent -> delivered
 *                       -> failed
 *   blocked  (never sent: invalid number, destination or rate limit)
 */
final class Log {

	const DB_VERSION        = '1';
	const DB_VERSION_OPTION = 'vibrate_sms_db_version';

	const STATUSES        = array( 'pending', 'sending', 'queued', 'in_progress', 'sent', 'delivered', 'failed', 'blocked' );
	const OPEN_STATUSES   = array( 'queued', 'in_progress', 'sent' );
	const CACHE_GROUP     = 'vibrate_sms';
	const MAX_ERROR_CHARS = 500;

	/**
	 * Full table name.
	 */
	public static function table(): string {
		global $wpdb;
		return $wpdb->prefix . 'vibrate_sms_log';
	}

	/**
	 * Create or upgrade the table when the stored schema version differs.
	 * Runs on every load (one autoloaded option read), because updating a
	 * plugin by upload never fires the activation hook, and on multisite each
	 * site gets its table the first time it loads the plugin.
	 */
	public static function maybe_upgrade(): void {
		if ( get_option( self::DB_VERSION_OPTION ) === self::DB_VERSION ) {
			return;
		}
		self::install();
	}

	/**
	 * Create the table and the plugin's options.
	 */
	public static function install(): void {
		global $wpdb;

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$table   = self::table();
		$charset = $wpdb->get_charset_collate();

		// dbDelta is picky: two spaces after PRIMARY KEY, one field per line.
		$sql = "CREATE TABLE {$table} (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  created_at datetime NOT NULL,
  updated_at datetime NOT NULL,
  batch_key char(36) NOT NULL,
  recipient varchar(32) NOT NULL,
  message text NOT NULL,
  units smallint(5) unsigned NOT NULL DEFAULT 1,
  status varchar(20) NOT NULL,
  source varchar(40) NOT NULL,
  object_id bigint(20) unsigned NOT NULL DEFAULT 0,
  user_id bigint(20) unsigned NOT NULL DEFAULT 0,
  run_id varchar(64) NOT NULL DEFAULT '',
  attempts tinyint(3) unsigned NOT NULL DEFAULT 0,
  error_code varchar(64) NOT NULL DEFAULT '',
  error_message varchar(500) NOT NULL DEFAULT '',
  PRIMARY KEY  (id),
  KEY batch_key (batch_key),
  KEY run_id (run_id),
  KEY status_created (status,created_at),
  KEY recipient_created (recipient,created_at),
  KEY source_object (source,object_id),
  KEY created_at (created_at)
) {$charset};";

		dbDelta( $sql );

		foreach ( Settings::options() as $name => $autoload ) {
			// add_option() is a no-op when the option exists.
			add_option( $name, Settings::TOKEN === $name ? '' : array(), '', $autoload ? 'yes' : 'no' );
		}

		update_option( self::DB_VERSION_OPTION, self::DB_VERSION, true );
	}

	/**
	 * Current time in UTC, MySQL format.
	 */
	public static function now(): string {
		return gmdate( 'Y-m-d H:i:s' );
	}

	/**
	 * Insert a row.
	 *
	 * @param array<string, mixed> $row Column values.
	 * @return int Row id, 0 on failure.
	 */
	public static function insert( array $row ): int {
		global $wpdb;
		$now  = self::now();
		$data = array(
			'created_at'    => $now,
			'updated_at'    => $now,
			'batch_key'     => (string) $row['batch_key'],
			'recipient'     => substr( (string) $row['recipient'], 0, 32 ),
			'message'       => (string) $row['message'],
			'units'         => (int) ( $row['units'] ?? 1 ),
			'status'        => (string) $row['status'],
			'source'        => substr( (string) $row['source'], 0, 40 ),
			'object_id'     => (int) ( $row['object_id'] ?? 0 ),
			'user_id'       => (int) ( $row['user_id'] ?? 0 ),
			'run_id'        => '',
			'attempts'      => 0,
			'error_code'    => substr( (string) ( $row['error_code'] ?? '' ), 0, 64 ),
			'error_message' => self::clip( (string) ( $row['error_message'] ?? '' ) ),
		);
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- custom table.
		$ok = $wpdb->insert( self::table(), $data, array( '%s', '%s', '%s', '%s', '%s', '%d', '%s', '%s', '%d', '%d', '%s', '%d', '%s', '%s' ) );
		return $ok ? (int) $wpdb->insert_id : 0;
	}

	/**
	 * Atomically move a batch from pending to sending and return its rows.
	 * Only one worker can win: the UPDATE is the lock.
	 *
	 * @param string $batch_key Batch key.
	 * @return object[] Claimed rows (empty when another worker owns the batch
	 *                  or nothing is pending).
	 */
	public static function claim_batch( string $batch_key ): array {
		global $wpdb;
		$table = self::table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table.
		$claimed = $wpdb->query( $wpdb->prepare( "UPDATE %i SET status = 'sending', updated_at = %s WHERE batch_key = %s AND status = 'pending'", $table, self::now(), $batch_key ) );
		if ( ! $claimed ) {
			return array();
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return (array) $wpdb->get_results( $wpdb->prepare( "SELECT * FROM %i WHERE batch_key = %s AND status = 'sending' ORDER BY id ASC", $table, $batch_key ) );
	}

	/**
	 * Update every row of a batch that is in one status.
	 *
	 * @param string               $batch_key   Batch key.
	 * @param string               $from_status Only rows in this status.
	 * @param array<string, mixed> $fields      Columns to set.
	 * @return int Rows changed.
	 */
	public static function update_batch( string $batch_key, string $from_status, array $fields ): int {
		global $wpdb;
		$fields = self::prepare_fields( $fields );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table.
		$changed = $wpdb->update( self::table(), $fields, array( 'batch_key' => $batch_key, 'status' => $from_status ) );
		return (int) $changed;
	}

	/**
	 * Update one row.
	 *
	 * @param int                  $id     Row id.
	 * @param array<string, mixed> $fields Columns to set.
	 */
	public static function update_row( int $id, array $fields ): void {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table.
		$wpdb->update( self::table(), self::prepare_fields( $fields ), array( 'id' => $id ) );
	}

	/**
	 * Rows of a batch.
	 *
	 * @param string $batch_key Batch key.
	 * @return object[]
	 */
	public static function batch( string $batch_key ): array {
		global $wpdb;
		$table = self::table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return (array) $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE batch_key = %s ORDER BY id ASC', $table, $batch_key ) );
	}

	/**
	 * Rows that belong to one API run.
	 *
	 * @param string $run_id Vibrate run id.
	 * @return object[]
	 */
	public static function run( string $run_id ): array {
		global $wpdb;
		$table = self::table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return (array) $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE run_id = %s', $table, $run_id ) );
	}

	/**
	 * One row.
	 *
	 * @param int $id Row id.
	 */
	public static function get( int $id ): ?object {
		global $wpdb;
		$table = self::table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$row = $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', $table, $id ) );
		return $row ? $row : null;
	}

	/**
	 * Messages counted against the hourly limits: everything recently created
	 * by a rate limited source that was not blocked.
	 *
	 * @param string[] $exempt_sources Sources that do not count.
	 * @param string   $recipient      Only this recipient ('' for all).
	 * @param int      $seconds        Window length.
	 */
	public static function count_recent( array $exempt_sources, string $recipient = '', int $seconds = HOUR_IN_SECONDS ): int {
		global $wpdb;
		$table  = self::table();
		$since  = gmdate( 'Y-m-d H:i:s', time() - $seconds );
		$where  = 'created_at >= %s AND status <> %s';
		$params = array( $table, $since, 'blocked' );
		if ( '' !== $recipient ) {
			$where   .= ' AND recipient = %s';
			$params[] = $recipient;
		}
		if ( $exempt_sources ) {
			$where .= ' AND source NOT IN (' . implode( ',', array_fill( 0, count( $exempt_sources ), '%s' ) ) . ')';
			$params = array_merge( $params, array_values( $exempt_sources ) );
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- placeholders built above.
		return (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE {$where}", $params ) );
	}

	/**
	 * Run ids with rows that may still change status, least recently checked
	 * first.
	 *
	 * @param int $limit   How many runs.
	 * @param int $settle  Skip runs checked (or sent) less than this many
	 *                     seconds ago: receipts take a moment.
	 * @param int $max_age Ignore rows older than this many seconds.
	 * @return string[]
	 */
	public static function open_runs( int $limit, int $settle = MINUTE_IN_SECONDS, int $max_age = 2 * DAY_IN_SECONDS ): array {
		global $wpdb;
		$table   = self::table();
		$since   = gmdate( 'Y-m-d H:i:s', time() - $max_age );
		$settled = gmdate( 'Y-m-d H:i:s', time() - max( 0, $settle ) );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$ids = $wpdb->get_col(
			$wpdb->prepare(
				"SELECT run_id FROM %i WHERE status IN ('queued','in_progress','sent') AND run_id <> '' AND created_at >= %s AND updated_at <= %s GROUP BY run_id ORDER BY MIN(updated_at) ASC LIMIT %d",
				$table,
				$since,
				$settled,
				$limit
			)
		);
		return array_map( 'strval', (array) $ids );
	}

	/**
	 * Mark open rows of a run as checked without changing their status, so
	 * the refresher moves on to other runs.
	 *
	 * @param string $run_id Run id.
	 */
	public static function touch_run( string $run_id ): void {
		global $wpdb;
		$table = self::table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->query( $wpdb->prepare( "UPDATE %i SET updated_at = %s WHERE run_id = %s AND status IN ('queued','in_progress','sent')", $table, self::now(), $run_id ) );
	}

	/**
	 * Batches stuck in "sending" (the worker died mid-request).
	 *
	 * @param int $older_than Seconds since the claim.
	 * @return array<int, object{batch_key: string, created_at: string}>
	 */
	public static function stale_sending( int $older_than ): array {
		global $wpdb;
		$table  = self::table();
		$before = gmdate( 'Y-m-d H:i:s', time() - $older_than );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return (array) $wpdb->get_results( $wpdb->prepare( "SELECT batch_key, MIN(created_at) AS created_at FROM %i WHERE status = 'sending' AND updated_at < %s GROUP BY batch_key LIMIT 50", $table, $before ) );
	}

	/**
	 * Batches still pending long after they should have been dispatched
	 * (the background queue never ran them).
	 *
	 * @param int $older_than Seconds since the last change.
	 * @return string[]
	 */
	public static function stale_pending( int $older_than ): array {
		global $wpdb;
		$table  = self::table();
		$before = gmdate( 'Y-m-d H:i:s', time() - $older_than );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return array_map( 'strval', (array) $wpdb->get_col( $wpdb->prepare( "SELECT batch_key FROM %i WHERE status = 'pending' AND updated_at < %s GROUP BY batch_key LIMIT 50", $table, $before ) ) );
	}

	/**
	 * Latest rows for one object (an order).
	 *
	 * @param string[] $sources   Sources to include.
	 * @param int      $object_id Object id.
	 * @param int      $limit     Max rows.
	 * @return object[]
	 */
	public static function for_object( array $sources, int $object_id, int $limit = 10 ): array {
		global $wpdb;
		$table        = self::table();
		$placeholders = implode( ',', array_fill( 0, count( $sources ), '%s' ) );
		$params       = array_merge( array( $table ), array_values( $sources ), array( $object_id, $limit ) );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- placeholders built above, one per value.
		return (array) $wpdb->get_results( $wpdb->prepare( "SELECT * FROM %i WHERE source IN ({$placeholders}) AND object_id = %d ORDER BY id DESC LIMIT %d", $params ) );
	}

	/**
	 * Page of rows for the log screen.
	 *
	 * @param array<string, mixed> $args status, source, search, per_page, page, orderby, order.
	 * @return array{items: object[], total: int}
	 */
	public static function query( array $args ): array {
		global $wpdb;
		$table  = self::table();
		$where  = array( '1=1' );
		$params = array();

		if ( ! empty( $args['status'] ) && in_array( $args['status'], self::STATUSES, true ) ) {
			$where[]  = 'status = %s';
			$params[] = $args['status'];
		}
		if ( ! empty( $args['source'] ) ) {
			$where[]  = 'source = %s';
			$params[] = (string) $args['source'];
		}
		if ( ! empty( $args['object_id'] ) ) {
			$where[]  = 'object_id = %d';
			$params[] = (int) $args['object_id'];
		}
		if ( isset( $args['search'] ) && '' !== $args['search'] ) {
			$digits = preg_replace( '/\D/', '', (string) $args['search'] );
			$like   = '%' . $wpdb->esc_like( (string) $args['search'] ) . '%';
			if ( '' !== $digits && strlen( $digits ) >= 3 ) {
				// Search "050-1234567" as well as "972501234567".
				$local    = 0 === strpos( $digits, '0' ) ? substr( $digits, 1 ) : $digits;
				$where[]  = '(recipient LIKE %s OR message LIKE %s OR run_id = %s)';
				$params[] = '%' . $wpdb->esc_like( $local ) . '%';
				$params[] = $like;
				$params[] = (string) $args['search'];
			} else {
				$where[]  = '(message LIKE %s OR run_id = %s)';
				$params[] = $like;
				$params[] = (string) $args['search'];
			}
		}

		$order    = isset( $args['order'] ) && 'asc' === strtolower( (string) $args['order'] ) ? 'ASC' : 'DESC';
		$per_page = max( 1, min( 200, (int) ( $args['per_page'] ?? 20 ) ) );
		$page     = max( 1, (int) ( $args['page'] ?? 1 ) );
		$offset   = ( $page - 1 ) * $per_page;
		$sql      = implode( ' AND ', $where );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- custom table; the clauses and ORDER BY direction are fixed strings, every value is a placeholder.
		$total = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE {$sql}", array_merge( array( $table ), $params ) ) );
		$items = (array) $wpdb->get_results( $wpdb->prepare( "SELECT * FROM %i WHERE {$sql} ORDER BY id {$order} LIMIT %d OFFSET %d", array_merge( array( $table ), $params, array( $per_page, $offset ) ) ) );
		// phpcs:enable

		return array(
			'items' => $items,
			'total' => $total,
		);
	}

	/**
	 * Delete rows older than the retention period, in small chunks so a huge
	 * log never locks the table for long.
	 *
	 * @param int $days Keep this many days.
	 * @return int Rows deleted.
	 */
	public static function purge( int $days ): int {
		global $wpdb;
		if ( $days < 1 ) {
			return 0;
		}
		$table   = self::table();
		$before  = gmdate( 'Y-m-d H:i:s', time() - $days * DAY_IN_SECONDS );
		$deleted = 0;
		for ( $i = 0; $i < 20; $i++ ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$n = (int) $wpdb->query( $wpdb->prepare( 'DELETE FROM %i WHERE created_at < %s LIMIT 1000', $table, $before ) );
			$deleted += $n;
			if ( $n < 1000 ) {
				break;
			}
		}
		return $deleted;
	}

	/**
	 * Delete rows by id (privacy eraser).
	 *
	 * @param int[] $ids Row ids.
	 */
	public static function delete_ids( array $ids ): int {
		global $wpdb;
		$ids = array_values( array_filter( array_map( 'intval', $ids ) ) );
		if ( ! $ids ) {
			return 0;
		}
		$table        = self::table();
		$placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- one %d per id.
		return (int) $wpdb->query( $wpdb->prepare( "DELETE FROM %i WHERE id IN ({$placeholders})", array_merge( array( $table ), $ids ) ) );
	}

	/**
	 * Rows for any of the given recipients or order ids (privacy tools).
	 *
	 * @param string[] $recipients Normalised phones.
	 * @param int[]    $order_ids  Order ids.
	 * @param int      $limit      Max rows.
	 * @param int      $offset     Offset.
	 * @return object[]
	 */
	public static function for_person( array $recipients, array $order_ids, int $limit, int $offset ): array {
		global $wpdb;
		$table   = self::table();
		$clauses = array();
		$params  = array( $table );
		if ( $recipients ) {
			$clauses[] = 'recipient IN (' . implode( ',', array_fill( 0, count( $recipients ), '%s' ) ) . ')';
			$params    = array_merge( $params, array_values( $recipients ) );
		}
		if ( $order_ids ) {
			$clauses[] = "(source LIKE 'woocommerce%%' AND object_id IN (" . implode( ',', array_fill( 0, count( $order_ids ), '%d' ) ) . '))';
			$params    = array_merge( $params, array_map( 'intval', $order_ids ) );
		}
		if ( ! $clauses ) {
			return array();
		}
		$params[] = $limit;
		$params[] = $offset;
		$where = implode( ' OR ', $clauses );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- clauses built above from fixed strings, one placeholder per value.
		return (array) $wpdb->get_results( $wpdb->prepare( "SELECT * FROM %i WHERE {$where} ORDER BY id ASC LIMIT %d OFFSET %d", $params ) );
	}

	/**
	 * Drop the table (uninstall).
	 */
	public static function drop(): void {
		global $wpdb;
		$table = self::table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
		$wpdb->query( $wpdb->prepare( 'DROP TABLE IF EXISTS %i', $table ) );
		delete_option( self::DB_VERSION_OPTION );
	}

	/**
	 * Clip long error text to the column size.
	 *
	 * @param string $text Text.
	 */
	public static function clip( string $text ): string {
		$text = Text::plain( $text );
		if ( Text::length( $text ) <= self::MAX_ERROR_CHARS ) {
			return $text;
		}
		// Cut on a character boundary, leaving room for the ellipsis.
		return (string) preg_replace( '/^(.{0,' . ( self::MAX_ERROR_CHARS - 4 ) . '}).*$/us', '$1', $text ) . '...';
	}

	/**
	 * Add updated_at and clip text columns.
	 *
	 * @param array<string, mixed> $fields Columns.
	 * @return array<string, mixed>
	 */
	private static function prepare_fields( array $fields ): array {
		$fields['updated_at'] = self::now();
		if ( isset( $fields['error_message'] ) ) {
			$fields['error_message'] = self::clip( (string) $fields['error_message'] );
		}
		if ( isset( $fields['error_code'] ) ) {
			$fields['error_code'] = substr( (string) $fields['error_code'], 0, 64 );
		}
		if ( isset( $fields['run_id'] ) ) {
			$fields['run_id'] = substr( (string) $fields['run_id'], 0, 64 );
		}
		return $fields;
	}
}