<?php
/**
 * Sent-SMS log: one row per event, sent later by the background queue.
 *
 * @package Smsportal
 */

namespace Smsportal;

use WP_Error;

defined( 'ABSPATH' ) || exit;

/**
 * Rows of {prefix}smsportal_messages. One event = one row = at most one SMS: the row's request_id
 * goes to smsportal.app, which answers a repeat with the first result instead of sending again.
 */
class Messages {

	const TABLE = 'smsportal_messages';

	const STATUSES = array( 'queued', 'sending', 'waiting_phone', 'sent', 'delivered', 'failed', 'expired', 'blocked_stop', 'limit', 'rate_limited' );

	/**
	 * Statuses smsportal reports through webhooks.
	 */
	const REPORT_STATUSES = array( 'sent', 'delivered', 'failed', 'expired' );

	const MAX_ERROR_LENGTH = 500;

	const REQUEST_ID_PREFIX = 'wp-';
	const REQUEST_ID_HASH   = 48;

	/**
	 * Column widths of the short text fields (longer values are cut).
	 */
	const FIELD_LENGTHS = array(
		'event_label' => 191,
		'object_type' => 32,
		'object_id'   => 64,
	);

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

	/**
	 * CREATE TABLE statement for dbDelta.
	 *
	 * @return string
	 */
	public static function schema(): string {
		global $wpdb;
		$table   = self::table();
		$charset = $wpdb->get_charset_collate();
		return "CREATE TABLE {$table} (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  created_at datetime NOT NULL,
  updated_at datetime NOT NULL,
  rule_id bigint(20) unsigned DEFAULT NULL,
  event_label varchar(191) NOT NULL DEFAULT '',
  object_type varchar(32) NOT NULL DEFAULT '',
  object_id varchar(64) NOT NULL DEFAULT '',
  recipient varchar(16) NOT NULL,
  message text NOT NULL,
  request_id varchar(64) NOT NULL,
  status varchar(16) NOT NULL,
  sms_batch_id varchar(64) DEFAULT NULL,
  error text,
  attempts smallint(5) unsigned NOT NULL DEFAULT 0,
  expires_at datetime DEFAULT NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY request_id (request_id),
  KEY status (status),
  KEY sms_batch_id (sms_batch_id),
  KEY recipient (recipient),
  KEY created_at (created_at)
) {$charset};";
	}

	/**
	 * Store a message and schedule it. Does no HTTP request, so it never slows the page down.
	 *
	 * @param array<string,mixed> $m recipient, message, event_label, object_type, object_id,
	 *                               dedupe_key, rule_id, expires_at (Unix time or null),
	 *                               check_limits (true for numbers typed by customers: see Limits),
	 *                               rule_limit (false: skip the per-rule hourly cap, e.g. for appointment reminders).
	 * @return int|WP_Error Row ID (the existing one when the dedupe key was seen before).
	 */
	public static function enqueue( array $m ) {
		$recipient = Phone::normalize( (string) ( $m['recipient'] ?? '' ), Phone::default_country_code() );
		if ( null === $recipient ) {
			return new WP_Error( 'smsportal_invalid_phone', __( 'This is not a valid phone number.', '85pl-android-sms-api' ) );
		}
		$message = (string) ( $m['message'] ?? '' );
		if ( '' === trim( $message ) ) {
			return new WP_Error( 'smsportal_empty_message', __( 'The message is empty.', '85pl-android-sms-api' ) );
		}

		$dedupe_key = (string) ( $m['dedupe_key'] ?? '' );
		$request_id = self::request_id( '' === $dedupe_key ? wp_generate_uuid4() : $dedupe_key );
		$existing   = self::id_by_request_id( $request_id );
		if ( null !== $existing ) {
			return $existing;
		}

		$status = Blocklist::is_blocked( $recipient ) ? 'blocked_stop' : 'queued';
		$reason = 'queued' === $status && ! empty( $m['check_limits'] ) ? Limits::check( $recipient, isset( $m['rule_id'] ) && ( $m['rule_limit'] ?? true ) ? absint( $m['rule_id'] ) : 0 ) : null;
		if ( null !== $reason ) {
			$status = 'rate_limited';
		}
		$id = self::insert( $m, $recipient, $message, $request_id, $status, $reason );
		if ( null === $id ) {
			// Lost a race with the same event: the other row is the one.
			$existing = self::id_by_request_id( $request_id );
			return null !== $existing ? $existing : new WP_Error( 'smsportal_db_error', __( 'The message could not be saved.', '85pl-android-sms-api' ) );
		}

		if ( 'queued' === $status ) {
			Queue::schedule( $id );
		} elseif ( null !== $reason ) {
			Limits::log_stopped( isset( $m['rule_id'] ) ? absint( $m['rule_id'] ) : 0, $reason );
		}
		return $id;
	}

	/**
	 * Idempotency key sent to smsportal.app: site-specific, so sites sharing an account stay apart. Built from
	 * the stored site address, not home_url(), which translation plugins (WPML, Polylang) change per language.
	 *
	 * @param string $dedupe_key Event key.
	 * @return string
	 */
	public static function request_id( string $dedupe_key ): string {
		return self::REQUEST_ID_PREFIX . substr( hash( 'sha256', get_option( 'home' ) . '|' . $dedupe_key ), 0, self::REQUEST_ID_HASH );
	}

	/**
	 * One row.
	 *
	 * @param int $id Row ID.
	 * @return array<string,string|null>|null
	 */
	public static function get( int $id ): ?array {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table, the queue needs the current state.
		$row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}smsportal_messages WHERE id = %d", $id ), ARRAY_A );
		return is_array( $row ) ? $row : null;
	}

	/**
	 * Update fields of a row (updated_at is set automatically).
	 *
	 * @param int                 $id     Row ID.
	 * @param array<string,mixed> $fields Column => value.
	 * @return void
	 */
	public static function update( int $id, array $fields ): void {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table.
		$wpdb->update(
			self::table(),
			array_merge( $fields, array( 'updated_at' => current_time( 'mysql', true ) ) ),
			array( 'id' => $id )
		);
	}

	/**
	 * Apply a status reported by smsportal to the row(s) of a batch. SENT and DELIVERED win over
	 * failed/expired (the phone's late report counts); nothing overrides delivered.
	 *
	 * @param string      $batch_id  smsportal batch ID.
	 * @param string|null $recipient E.164 number, or null to match the whole batch.
	 * @param string      $status    sent, delivered, failed or expired.
	 * @param string|null $error     Reason for failed/expired.
	 * @return int Rows changed.
	 */
	public static function apply_report( string $batch_id, ?string $recipient, string $status, ?string $error ): int {
		global $wpdb;
		if ( ! in_array( $status, self::REPORT_STATUSES, true ) || '' === $batch_id ) {
			return 0;
		}
		$error = in_array( $status, array( 'sent', 'delivered' ), true ) ? null : mb_substr( (string) $error, 0, self::MAX_ERROR_LENGTH );
		// Delivered is final: the only rule needed, since SENT and DELIVERED may override anything else.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table, conditional status change.
		$changed = $wpdb->query(
			$wpdb->prepare(
				"UPDATE {$wpdb->prefix}smsportal_messages SET status = %s, error = NULLIF(%s, ''), updated_at = %s WHERE sms_batch_id = %s AND status <> 'delivered' AND ( %s = '' OR recipient = %s )",
				$status,
				(string) $error,
				current_time( 'mysql', true ),
				$batch_id,
				(string) $recipient,
				(string) $recipient
			)
		);
		return false === $changed ? 0 : (int) $changed;
	}

	/**
	 * The SMS a reply from this number most likely answers: the newest row that left the phone
	 * (has a batch ID and is waiting_phone, sent or delivered), else the newest row to the number.
	 *
	 * @param string $recipient E.164 number.
	 * @return array<string,string|null>|null
	 */
	public static function latest_for_recipient( string $recipient ): ?array {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table.
		$row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}smsportal_messages WHERE recipient = %s ORDER BY ( sms_batch_id IS NOT NULL AND status IN ( 'waiting_phone', 'sent', 'delivered' ) ) DESC, id DESC LIMIT 1", $recipient ), ARRAY_A );
		return is_array( $row ) ? $row : null;
	}

	/**
	 * IDs of rows in a status that have not changed since a moment, oldest first.
	 *
	 * @param string $status Status.
	 * @param string $before UTC time (Y-m-d H:i:s).
	 * @param int    $limit  Rows.
	 * @return array<int,int>
	 */
	public static function stale_ids( string $status, string $before, int $limit ): array {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table, the sweep needs the current state.
		$ids = $wpdb->get_col( $wpdb->prepare( "SELECT id FROM {$wpdb->prefix}smsportal_messages WHERE status = %s AND updated_at < %s ORDER BY id ASC LIMIT %d", $status, $before, max( 1, $limit ) ) );
		return array_map( 'intval', is_array( $ids ) ? $ids : array() );
	}

	/**
	 * Put a row stuck in "sending" back in the queue (its worker died). Only when it has not
	 * changed since $before, so a worker that is still sending keeps it.
	 *
	 * @param int    $id     Row ID.
	 * @param string $before UTC time (Y-m-d H:i:s).
	 * @return bool True when the row went back to queued.
	 */
	public static function release( int $id, string $before ): bool {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table, atomic status change.
		$changed = $wpdb->query(
			$wpdb->prepare(
				"UPDATE {$wpdb->prefix}smsportal_messages SET status = 'queued', updated_at = %s WHERE id = %d AND status = 'sending' AND updated_at < %s",
				current_time( 'mysql', true ),
				$id,
				$before
			)
		);
		return 1 === $changed;
	}

	/**
	 * Newest rows first, for the admin lists.
	 *
	 * @param int $limit  Rows.
	 * @param int $offset Rows to skip.
	 * @return array<int,array<string,string|null>>
	 */
	public static function recent( int $limit, int $offset = 0 ): array {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table, the log must be current.
		$rows = $wpdb->get_results( $wpdb->prepare( "SELECT id, created_at, event_label, object_type, object_id, recipient, status, error FROM {$wpdb->prefix}smsportal_messages ORDER BY id DESC LIMIT %d OFFSET %d", max( 1, $limit ), max( 0, $offset ) ), ARRAY_A );
		return is_array( $rows ) ? $rows : array();
	}

	/**
	 * Number of rows.
	 *
	 * @return int
	 */
	public static function count(): int {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table.
		return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->prefix}smsportal_messages" );
	}

	/**
	 * Messages created since the start of the current month (UTC), by outcome.
	 *
	 * @return array{total:int,delivered:int,failed:int}
	 */
	public static function month_counts(): array {
		global $wpdb;
		$since = gmdate( 'Y-m-01 00:00:00' );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table, live counters.
		$row = $wpdb->get_row( $wpdb->prepare( "SELECT COUNT(*) AS total, SUM( status = 'delivered' ) AS delivered, SUM( status IN ( 'failed', 'expired' ) ) AS failed FROM {$wpdb->prefix}smsportal_messages WHERE created_at >= %s", $since ), ARRAY_A );
		return array(
			'total'     => (int) ( $row['total'] ?? 0 ),
			'delivered' => (int) ( $row['delivered'] ?? 0 ),
			'failed'    => (int) ( $row['failed'] ?? 0 ),
		);
	}

	/**
	 * Take a queued row for sending: only one worker wins, and the attempt is counted.
	 *
	 * @param int $id Row ID.
	 * @return bool True when this caller may send it.
	 */
	public static function claim( int $id ): bool {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table, atomic status change.
		$changed = $wpdb->query(
			$wpdb->prepare(
				"UPDATE {$wpdb->prefix}smsportal_messages SET status = 'sending', attempts = attempts + 1, updated_at = %s WHERE id = %d AND status = 'queued'",
				current_time( 'mysql', true ),
				$id
			)
		);
		return 1 === $changed;
	}

	/**
	 * Expiry of a row as Unix time.
	 *
	 * @param array<string,string|null> $row Row.
	 * @return int|null
	 */
	public static function expires_at( array $row ): ?int {
		if ( empty( $row['expires_at'] ) ) {
			return null;
		}
		$time = strtotime( $row['expires_at'] . ' UTC' );
		return false === $time ? null : $time;
	}

	/**
	 * Insert the row.
	 *
	 * @param array<string,mixed> $m          Input.
	 * @param string              $recipient  E.164 number.
	 * @param string              $message    Text.
	 * @param string              $request_id Idempotency key.
	 * @param string              $status     queued, blocked_stop or rate_limited.
	 * @param string|null         $error      Why it was not queued.
	 * @return int|null Row ID, or null when the insert failed.
	 */
	private static function insert( array $m, string $recipient, string $message, string $request_id, string $status, ?string $error ): ?int {
		global $wpdb;
		$now        = current_time( 'mysql', true );
		$rule_id    = isset( $m['rule_id'] ) ? absint( $m['rule_id'] ) : 0;
		$expires_at = isset( $m['expires_at'] ) ? absint( $m['expires_at'] ) : 0;

		$row = array(
			'created_at'   => $now,
			'updated_at'   => $now,
			'rule_id'      => $rule_id > 0 ? $rule_id : null,
			'recipient'    => $recipient,
			'message'      => $message,
			'request_id'   => $request_id,
			'status'       => $status,
			'attempts'     => 0,
			'expires_at'   => $expires_at > 0 ? gmdate( 'Y-m-d H:i:s', $expires_at ) : null,
			'sms_batch_id' => null,
			'error'        => $error,
		);
		foreach ( self::FIELD_LENGTHS as $field => $length ) {
			$row[ $field ] = mb_substr( sanitize_text_field( (string) ( $m[ $field ] ?? '' ) ), 0, $length );
		}

		// Explicit formats: WordPress declares a column named object_id as a number (%d), which would cut an
		// object ID such as "12-f1a4" to 12. NULLs stay NULL whatever the format.
		$formats = array();
		foreach ( array_keys( $row ) as $field ) {
			$formats[] = in_array( $field, array( 'rule_id', 'attempts' ), true ) ? '%d' : '%s';
		}

		$suppress = $wpdb->suppress_errors( true );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- custom table.
		$inserted = $wpdb->insert( self::table(), $row, $formats );
		$wpdb->suppress_errors( $suppress );
		return false === $inserted ? null : (int) $wpdb->insert_id;
	}

	/**
	 * Row ID for a request ID.
	 *
	 * @param string $request_id Idempotency key.
	 * @return int|null
	 */
	private static function id_by_request_id( string $request_id ): ?int {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom table, must see rows written by this request.
		$id = $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$wpdb->prefix}smsportal_messages WHERE request_id = %s", $request_id ) );
		return null === $id ? null : (int) $id;
	}
}