<?php
/**
 * Notice repository.
 *
 * @package HostKhata
 */

namespace HostKhata\Database\Repositories;

defined( 'ABSPATH' ) || exit;

use HostKhata\Database\Tables;
use HostKhata\Support\Date;
use HostKhata\Support\Helpers;

/**
 * Reads and writes notices (announcements for customers).
 *
 * @since 0.1.0
 */
final class NoticeRepository extends AbstractRepository {

	/**
	 * Columns written to the notices table and their formats.
	 */
	const COLUMNS = array(
		'title'         => '%s',
		'body'          => '%s',
		'notice_type'   => '%s',
		'audience'      => '%s',
		'audience_ids'  => '%s',
		'starts_at_gmt' => '%s',
		'ends_at_gmt'   => '%s',
		'send_email'    => '%d',
		'status'        => '%s',
		'created_by'    => '%d',
	);

	/**
	 * Sortable columns.
	 */
	const ORDERBY = array(
		'id'      => 'id',
		'title'   => 'title',
		'status'  => 'status',
		'starts'  => 'starts_at_gmt',
		'created' => 'created_at_gmt',
	);

	/**
	 * Finds a notice.
	 *
	 * @since 0.1.0
	 *
	 * @param int $id Notice id.
	 * @return array<string, mixed>|null
	 */
	public function find( int $id ): ?array {
		$table = Tables::name( 'notices' );
		$row   = $this->row( "SELECT * FROM {$table} WHERE id = %d", array( $id ) );

		return null === $row ? null : $this->hydrate( $row );
	}

	/**
	 * Lists notices.
	 *
	 * @since 0.1.0
	 *
	 * @param array<string, mixed> $args `search`, `status`, `orderby`, `order`, `page`, `per_page`.
	 * @return array{items: array<int, array<string, mixed>>, total: int}
	 */
	public function query( array $args ): array {
		$table  = Tables::name( 'notices' );
		$where  = array( '1 = 1' );
		$params = array();

		$search = trim( (string) ( $args['search'] ?? '' ) );

		if ( '' !== $search ) {
			$where[]  = 'title LIKE %s';
			$params[] = '%' . $this->wpdb->esc_like( $search ) . '%';
		}

		if ( ! empty( $args['status'] ) ) {
			$where[]  = 'status = %s';
			$params[] = (string) $args['status'];
		}

		$where_sql = implode( ' AND ', $where );
		$orderby   = self::ORDERBY[ $args['orderby'] ?? 'id' ] ?? 'id';
		$order     = 'asc' === strtolower( (string) ( $args['order'] ?? 'desc' ) ) ? 'ASC' : 'DESC';
		$per_page  = max( 1, min( 100, (int) ( $args['per_page'] ?? 20 ) ) );
		$offset    = max( 0, ( (int) ( $args['page'] ?? 1 ) - 1 ) * $per_page );
		$total     = (int) $this->value( "SELECT COUNT(*) FROM {$table} WHERE {$where_sql}", $params );
		$rows      = $this->rows( "SELECT * FROM {$table} WHERE {$where_sql} ORDER BY {$orderby} {$order}, id DESC LIMIT %d OFFSET %d", array_merge( $params, array( $per_page, $offset ) ) );

		return array(
			'items' => array_map( array( $this, 'hydrate' ), $rows ),
			'total' => $total,
		);
	}

	/**
	 * Number of notices per status.
	 *
	 * @since 0.1.0
	 *
	 * @return array<string, int>
	 */
	public function status_counts(): array {
		$table  = Tables::name( 'notices' );
		$counts = array();

		foreach ( $this->rows( "SELECT status, COUNT(*) AS total FROM {$table} GROUP BY status" ) as $row ) {
			$counts[ (string) $row['status'] ] = (int) $row['total'];
		}

		return $counts;
	}

	/**
	 * Inserts a notice.
	 *
	 * @since 0.1.0
	 *
	 * @param array<string, mixed> $data Notice columns.
	 * @return int New id, or 0.
	 */
	public function insert( array $data ): int {
		$now  = Date::now_gmt();
		$row  = $this->to_row( $data );
		$row += array(
			'created_at_gmt' => $now,
			'updated_at_gmt' => $now,
		);

		return $this->insert_row( Tables::name( 'notices' ), $row, $this->formats( self::COLUMNS, $row ) );
	}

	/**
	 * Updates a notice.
	 *
	 * @since 0.1.0
	 *
	 * @param int                  $id   Notice id.
	 * @param array<string, mixed> $data Changed columns.
	 * @return bool
	 */
	public function update( int $id, array $data ): bool {
		$row                   = $this->to_row( $data );
		$row['updated_at_gmt'] = Date::now_gmt();

		return false !== $this->wpdb->update( Tables::name( 'notices' ), $row, array( 'id' => $id ), $this->formats( self::COLUMNS, $row ), array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
	}

	/**
	 * Deletes a notice and who read it.
	 *
	 * @since 0.1.0
	 *
	 * @param int $id Notice id.
	 * @return bool
	 */
	public function delete( int $id ): bool {
		$this->wpdb->delete( Tables::name( 'notice_reads' ), array( 'notice_id' => $id ), array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching

		return false !== $this->wpdb->delete( Tables::name( 'notices' ), array( 'id' => $id ), array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
	}

	/**
	 * Published notices a customer should see now: inside their dates, for them, and not dismissed.
	 *
	 * @since 0.1.0
	 *
	 * @param int $customer_id Customer id.
	 * @param int $limit       Maximum number of notices.
	 * @return array<int, array<string, mixed>>
	 */
	public function active_for( int $customer_id, int $limit = 20 ): array {
		$table = Tables::name( 'notices' );
		$reads = Tables::name( 'notice_reads' );
		$now   = Date::now_gmt();

		$rows = $this->rows(
			"SELECT n.*, r.dismissed AS dismissed FROM {$table} n LEFT JOIN {$reads} r ON r.notice_id = n.id AND r.customer_id = %d WHERE n.status = 'published' AND (n.starts_at_gmt IS NULL OR n.starts_at_gmt <= %s) AND (n.ends_at_gmt IS NULL OR n.ends_at_gmt >= %s) AND (r.dismissed IS NULL OR r.dismissed = 0) ORDER BY n.id DESC LIMIT %d",
			array( $customer_id, $now, $now, max( 1, $limit * 3 ) )
		);

		$notices = array();

		foreach ( $rows as $row ) {
			$notice = $this->hydrate( $row );

			if ( 'selected' === $notice['audience'] && ! in_array( $customer_id, $notice['audience_ids'], true ) ) {
				continue;
			}

			$notices[] = $notice;
		}

		return array_slice( $notices, 0, $limit );
	}

	/**
	 * Remembers that a customer dismissed a notice.
	 *
	 * @since 0.1.0
	 *
	 * @param int $notice_id   Notice id.
	 * @param int $customer_id Customer id.
	 * @return void
	 */
	public function dismiss( int $notice_id, int $customer_id ): void {
		$table = Tables::name( 'notice_reads' );

		$this->wpdb->query( $this->prepare( "INSERT INTO {$table} (notice_id, customer_id, dismissed, read_at_gmt) VALUES (%d, %d, 1, %s) ON DUPLICATE KEY UPDATE dismissed = 1", array( $notice_id, $customer_id, Date::now_gmt() ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter
	}

	/**
	 * Number of customers who read a notice.
	 *
	 * @since 0.1.0
	 *
	 * @param int $id Notice id.
	 * @return int
	 */
	public function read_count( int $id ): int {
		$table = Tables::name( 'notice_reads' );

		return (int) $this->value( "SELECT COUNT(*) FROM {$table} WHERE notice_id = %d", array( $id ) );
	}

	/**
	 * Keeps known columns, stores the audience ids as JSON and empty dates as NULL.
	 *
	 * @param array<string, mixed> $data Input.
	 * @return array<string, mixed>
	 */
	private function to_row( array $data ): array {
		$row = array_intersect_key( $data, self::COLUMNS );

		if ( isset( $row['audience_ids'] ) && is_array( $row['audience_ids'] ) ) {
			$row['audience_ids'] = Helpers::json_encode( array_values( $row['audience_ids'] ) );
		}

		foreach ( array( 'starts_at_gmt', 'ends_at_gmt' ) as $column ) {
			if ( array_key_exists( $column, $row ) && '' === $row[ $column ] ) {
				$row[ $column ] = null;
			}
		}

		return $row;
	}

	/**
	 * Casts columns.
	 *
	 * @param array<string, mixed> $row Database row.
	 * @return array<string, mixed>
	 */
	private function hydrate( array $row ): array {
		$row['id']            = (int) $row['id'];
		$row['send_email']    = (bool) $row['send_email'];
		$row['created_by']    = (int) $row['created_by'];
		$row['audience_ids']  = array_map( 'intval', Helpers::json_decode_array( $row['audience_ids'] ?? '' ) );
		$row['starts_at_gmt'] = (string) $row['starts_at_gmt'];
		$row['ends_at_gmt']   = (string) $row['ends_at_gmt'];

		return $row;
	}
}