<?php
declare( strict_types=1 );

namespace Dashbread\Notifications;

/** The notifications table: one row per user per event. Pruned after 90 days by cron. */
final class Store {

	public const DB_VERSION = '1';

	public static function table(): string {
		global $wpdb;
		return $wpdb->prefix . 'dashbread_notifications';
	}

	public static function install(): void {
		global $wpdb;
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		$charset = $wpdb->get_charset_collate();
		$table   = self::table();
		// dbDelta() parses the statement itself and cannot take prepare() placeholders; the only
		// interpolations are the table name (prefix + constant) and WordPress's charset clause.
		dbDelta( "CREATE TABLE {$table} (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  user_id bigint(20) unsigned NOT NULL,
  type varchar(64) NOT NULL,
  title varchar(255) NOT NULL DEFAULT '',
  body text NULL,
  url varchar(2048) NULL,
  icon varchar(64) NULL,
  data longtext NULL,
  read_at datetime NULL,
  created_at datetime NOT NULL,
  PRIMARY KEY  (id),
  KEY user_unread (user_id, read_at),
  KEY user_created (user_id, created_at),
  KEY type (type)
) {$charset};" );
		update_option( 'dashbread_db_version', self::DB_VERSION );
	}

	public static function maybe_upgrade(): void {
		if ( (string) get_option( 'dashbread_db_version', '' ) !== self::DB_VERSION ) {
			self::install();
		}
	}

	/** @param array{title?:string,body?:string,url?:string,icon?:string,data?:array} $payload */
	public static function insert( int $user_id, string $type, array $payload ): int {
		global $wpdb;
		$wpdb->insert( self::table(), array( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- Dashbread's own table.
			'user_id'    => $user_id,
			'type'       => sanitize_key( $type ),
			'title'      => wp_strip_all_tags( (string) ( $payload['title'] ?? '' ) ),
			'body'       => wp_kses_post( (string) ( $payload['body'] ?? '' ) ),
			'url'        => esc_url_raw( (string) ( $payload['url'] ?? '' ) ),
			'icon'       => sanitize_key( (string) ( $payload['icon'] ?? '' ) ),
			'data'       => wp_json_encode( $payload['data'] ?? array() ),
			'created_at' => current_time( 'mysql', true ),
		), array( '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s' ) );
		return (int) $wpdb->insert_id;
	}

	/** @return array<int,array<string,mixed>> */
	public static function list( int $user_id, bool $unread_only = false, int $page = 1, int $per_page = 20 ): array {
		global $wpdb;
		$offset = max( 0, $page - 1 ) * $per_page;
		if ( $unread_only ) {
			$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE user_id = %d AND read_at IS NULL ORDER BY created_at DESC, id DESC LIMIT %d OFFSET %d', self::table(), $user_id, $per_page, $offset ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- Dashbread's own table.
		} else {
			$rows = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE user_id = %d ORDER BY created_at DESC, id DESC LIMIT %d OFFSET %d', self::table(), $user_id, $per_page, $offset ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- Dashbread's own table.
		}
		return array_map( array( self::class, 'shape' ), $rows ?: array() );
	}

	public static function unread_count( int $user_id ): int {
		global $wpdb;
		return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE user_id = %d AND read_at IS NULL', self::table(), $user_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- Dashbread's own table.
	}

	public static function mark_read( int $user_id, int $id ): bool {
		global $wpdb;
		return false !== $wpdb->update( self::table(), array( 'read_at' => current_time( 'mysql', true ) ), array( 'id' => $id, 'user_id' => $user_id ), array( '%s' ), array( '%d', '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- Dashbread's own table.
	}

	public static function mark_all_read( int $user_id ): void {
		global $wpdb;
		$wpdb->query( $wpdb->prepare( 'UPDATE %i SET read_at = %s WHERE user_id = %d AND read_at IS NULL', self::table(), current_time( 'mysql', true ), $user_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- Dashbread's own table.
	}

	public static function prune( int $days = 90 ): void {
		global $wpdb;
		$wpdb->query( $wpdb->prepare( 'DELETE FROM %i WHERE created_at < %s', self::table(), gmdate( 'Y-m-d H:i:s', time() - $days * DAY_IN_SECONDS ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- Dashbread's own table.
	}

	/** @param array<string,mixed> $row @return array<string,mixed> */
	private static function shape( array $row ): array {
		$created = strtotime( (string) $row['created_at'] . ' UTC' ) ?: time();
		$icon    = (string) ( $row['icon'] ?: 'bell' );
		// Any icon in the full set draws as itself; a name the set doesn't have stays a bell.
		$icon    = ( 'circle' === \Dashbread\Shell\Icons::resolve( $icon ) && 'circle' !== $icon ) ? 'bell' : $icon;
		return array(
			'id'        => (int) $row['id'],
			'type'      => (string) $row['type'],
			'title'     => (string) $row['title'],
			'body'      => (string) $row['body'],
			'url'       => (string) $row['url'],
			'icon'      => $icon,
			'iconSvg'   => \Dashbread\Shell\Icons::inner( $icon ),
			'data'      => json_decode( (string) $row['data'], true ) ?: array(),
			'read'      => null !== $row['read_at'],
			'createdAt' => gmdate( 'c', $created ),
			'ago'       => sprintf( /* translators: %s: human time diff */ __( '%s ago', 'dashbread' ), human_time_diff( $created ) ),
		);
	}
}