<?php
/**
 * Database layer.
 *
 * @package ReplyPing
 */

namespace PlugWren\ReplyPing;

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

/**
 * Subscription storage.
 */
final class Db {

	const DB_VERSION = '2';
	const PURGE_HOOK = 'replyping_purge_pending';

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

	/**
	 * Create or update the table.
	 */
	public static function install() {
		global $wpdb;
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$table   = self::table();

		// Remove pre-existing duplicates so the unique key can be added.
		$exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table; schema check must be fresh.
		if ( $exists === $table ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table; one-off duplicate cleanup.
			$wpdb->query( $wpdb->prepare( 'DELETE t1 FROM %i t1 INNER JOIN %i t2 ON t1.post_id = t2.post_id AND t1.comment_id = t2.comment_id AND t1.email = t2.email AND t1.type = t2.type AND t1.id > t2.id', $table, $table ) );
		}

		$charset = $wpdb->get_charset_collate();

		$sql = "CREATE TABLE {$table} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			post_id bigint(20) unsigned NOT NULL DEFAULT 0,
			comment_id bigint(20) unsigned NOT NULL DEFAULT 0,
			email varchar(190) NOT NULL DEFAULT '',
			type varchar(10) NOT NULL DEFAULT 'post',
			token_hash char(64) NOT NULL DEFAULT '',
			status varchar(15) NOT NULL DEFAULT 'pending',
			consent_text text NOT NULL,
			created_at datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
			confirmed_at datetime DEFAULT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY sub_unique (post_id,comment_id,email,type),
			KEY post_id (post_id),
			KEY email (email),
			KEY token_hash (token_hash)
		) {$charset};";

		dbDelta( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- dbDelta cannot take prepared SQL; table name is built from $wpdb->prefix.
		update_option( 'replyping_db_version', self::DB_VERSION );
	}

	/**
	 * Run the installer when the stored schema version is outdated.
	 */
	public static function maybe_upgrade() {
		if ( get_option( 'replyping_db_version' ) !== self::DB_VERSION ) {
			self::install();
		}
	}

	/**
	 * Hash a raw token.
	 *
	 * @param string $token Raw token.
	 * @return string
	 */
	public static function hash_token( $token ) {
		return hash( 'sha256', (string) $token );
	}

	/**
	 * Generate a random raw token.
	 *
	 * @return string
	 */
	public static function new_token() {
		return wp_generate_password( 40, false, false );
	}

	/**
	 * Key used in unsubscribe links (HMAC, so no raw token needs storing).
	 *
	 * @param object $sub Subscription row.
	 * @return string
	 */
	public static function unsubscribe_key( $sub ) {
		return hash_hmac( 'sha256', 'replyping_unsub|' . (int) $sub->id . '|' . $sub->email, wp_salt( 'auth' ) );
	}

	/**
	 * Get a row by ID.
	 *
	 * @param int $id Row ID.
	 * @return object|null
	 */
	public static function get( $id ) {
		global $wpdb;
		$table = self::table();
		return $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', $table, absint( $id ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table; lookups must be fresh.
	}

	/**
	 * Get a row by raw token.
	 *
	 * @param string $token Raw token.
	 * @return object|null
	 */
	public static function get_by_token( $token ) {
		global $wpdb;
		$table = self::table();
		$hash  = self::hash_token( $token );
		$row   = $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE token_hash = %s', $table, $hash ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table; token lookups must be fresh.
		if ( $row && hash_equals( $row->token_hash, $hash ) ) {
			return $row;
		}
		return null;
	}

	/**
	 * Find an existing row for the same target.
	 *
	 * @param int    $post_id    Post ID.
	 * @param int    $comment_id Comment ID.
	 * @param string $email      Email.
	 * @param string $type       Type.
	 * @return object|null
	 */
	public static function find( $post_id, $comment_id, $email, $type ) {
		global $wpdb;
		$table = self::table();
		return $wpdb->get_row( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table; duplicate checks must be fresh.
			$wpdb->prepare(
				'SELECT * FROM %i WHERE post_id = %d AND comment_id = %d AND email = %s AND type = %s LIMIT 1',
				$table,
				absint( $post_id ),
				absint( $comment_id ),
				$email,
				$type
			)
		);
	}

	/**
	 * Insert a row.
	 *
	 * @param array $data Row data (token_hash required).
	 * @return int Insert ID or 0.
	 */
	public static function insert( array $data ) {
		global $wpdb;
		$now = current_time( 'mysql', true );
		// The unique key rejects duplicates; silence the resulting DB error.
		$suppress = $wpdb->suppress_errors( true );
		$ok       = $wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table; subscription writes.
			self::table(),
			array(
				'post_id'      => absint( $data['post_id'] ),
				'comment_id'   => absint( $data['comment_id'] ),
				'email'        => $data['email'],
				'type'         => $data['type'],
				'token_hash'   => $data['token_hash'],
				'status'       => $data['status'],
				'consent_text' => $data['consent_text'],
				'created_at'   => $now,
				'confirmed_at' => ( 'active' === $data['status'] ) ? $now : null,
			),
			array( '%d', '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s' )
		);
		$wpdb->suppress_errors( $suppress );
		return $ok ? (int) $wpdb->insert_id : 0;
	}

	/**
	 * Update status (and optionally token hash).
	 *
	 * @param int         $id         Row ID.
	 * @param string      $status     Status.
	 * @param string|null $token_hash New token hash.
	 */
	public static function update_status( $id, $status, $token_hash = null ) {
		global $wpdb;
		$data   = array( 'status' => $status );
		$format = array( '%s' );
		if ( 'active' === $status ) {
			$data['confirmed_at'] = current_time( 'mysql', true );
			$format[]             = '%s';
		}
		if ( null !== $token_hash ) {
			$data['token_hash'] = $token_hash;
			$format[]           = '%s';
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table; subscription writes.
		$wpdb->update( self::table(), $data, array( 'id' => absint( $id ) ), $format, array( '%d' ) );
	}

	/**
	 * Unsubscribe every row for an email, optionally limited to a post.
	 *
	 * @param string   $email   Email.
	 * @param int|null $post_id Post ID or null for all posts.
	 */
	public static function unsubscribe( $email, $post_id = null ) {
		global $wpdb;
		$table = self::table();
		if ( null === $post_id ) {
			$wpdb->query( $wpdb->prepare( 'UPDATE %i SET status = %s WHERE email = %s', $table, 'unsubscribed', $email ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table; subscription writes.
		} else {
			$wpdb->query( $wpdb->prepare( 'UPDATE %i SET status = %s WHERE email = %s AND post_id = %d', $table, 'unsubscribed', $email, absint( $post_id ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table; subscription writes.
		}
	}

	/**
	 * Active subscriptions that should receive a notification for a comment.
	 *
	 * @param int $post_id   Post ID.
	 * @param int $parent_id Parent comment ID.
	 * @param int $after_id  Return rows with ID greater than this.
	 * @param int $limit     Max rows.
	 * @return array
	 */
	public static function get_recipients( $post_id, $parent_id, $after_id = 0, $limit = 51 ) {
		global $wpdb;
		$table = self::table();
		return (array) $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table; recipients must be fresh.
			$wpdb->prepare(
				"SELECT * FROM %i WHERE status = 'active' AND post_id = %d AND ( type = 'post' OR ( type = 'replies' AND comment_id = %d AND %d > 0 ) ) AND id > %d ORDER BY id ASC LIMIT %d",
				$table,
				absint( $post_id ),
				absint( $parent_id ),
				absint( $parent_id ),
				absint( $after_id ),
				max( 1, absint( $limit ) )
			)
		);
	}

	/**
	 * Delete pending rows older than 7 days.
	 */
	public static function purge_pending() {
		global $wpdb;
		$table  = self::table();
		$cutoff = gmdate( 'Y-m-d H:i:s', time() - 7 * DAY_IN_SECONDS );
		$wpdb->query( $wpdb->prepare( 'DELETE FROM %i WHERE status = %s AND created_at < %s', $table, 'pending', $cutoff ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table; scheduled purge.
	}

	/**
	 * All rows for an email.
	 *
	 * @param string $email Email.
	 * @return array
	 */
	public static function get_by_email( $email ) {
		global $wpdb;
		$table = self::table();
		return (array) $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE email = %s ORDER BY id ASC', $table, $email ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table; lookups must be fresh.
	}

	/**
	 * Delete all rows for an email.
	 *
	 * @param string $email Email.
	 * @return int Rows deleted.
	 */
	public static function delete_by_email( $email ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table; subscription writes.
		$count = $wpdb->delete( self::table(), array( 'email' => $email ), array( '%s' ) );
		return (int) $count;
	}
}