<?php
/**
 * Product group repository.
 *
 * @package HostKhata
 */

namespace HostKhata\Database\Repositories;

defined( 'ABSPATH' ) || exit;

use HostKhata\Database\Tables;

/**
 * Reads and writes product groups (categories of packages).
 *
 * @since 0.1.0
 */
final class GroupRepository extends AbstractRepository {

	/**
	 * Every group in display order, with the number of products in it.
	 *
	 * @since 0.1.0
	 *
	 * @return array<int, array<string, mixed>>
	 */
	public function all(): array {
		$groups   = Tables::name( 'product_groups' );
		$products = Tables::name( 'products' );
		$rows     = $this->rows(
			"SELECT g.*, ( SELECT COUNT(*) FROM {$products} p WHERE p.group_id = g.id ) AS product_count FROM {$groups} g ORDER BY g.sort_order ASC, g.id ASC"
		);

		return array_map( array( $this, 'hydrate' ), $rows );
	}

	/**
	 * Finds a group by id.
	 *
	 * @since 0.1.0
	 *
	 * @param int $id Group id.
	 * @return array<string, mixed>|null
	 */
	public function find( int $id ): ?array {
		return $this->find_by( 'g.id = %d', $id );
	}

	/**
	 * Finds a group by slug.
	 *
	 * @since 0.1.0
	 *
	 * @param string $slug Slug.
	 * @return array<string, mixed>|null
	 */
	public function find_by_slug( string $slug ): ?array {
		return $this->find_by( 'g.slug = %s', $slug );
	}

	/**
	 * Tells whether a slug is taken.
	 *
	 * @since 0.1.0
	 *
	 * @param string $slug    Slug.
	 * @param int    $exclude Group id to ignore, when renaming a group.
	 * @return bool
	 */
	public function slug_exists( string $slug, int $exclude = 0 ): bool {
		$table = Tables::name( 'product_groups' );

		return null !== $this->value( "SELECT id FROM {$table} WHERE slug = %s AND id <> %d LIMIT 1", array( $slug, $exclude ) );
	}

	/**
	 * Inserts a group, placing it last.
	 *
	 * @since 0.1.0
	 *
	 * @param array<string, mixed> $data name, slug, description, status.
	 * @return int New id, or 0 on failure.
	 */
	public function insert( array $data ): int {
		$table = Tables::name( 'product_groups' );
		$next  = (int) $this->value( "SELECT COALESCE( MAX( sort_order ), 0 ) + 1 FROM {$table}" );

		return $this->insert_row(
			$table,
			array(
				'name'        => (string) $data['name'],
				'slug'        => (string) $data['slug'],
				'description' => (string) ( $data['description'] ?? '' ),
				'sort_order'  => $next,
				'status'      => (string) ( $data['status'] ?? 'active' ),
			),
			array( '%s', '%s', '%s', '%d', '%s' )
		);
	}

	/**
	 * Updates a group.
	 *
	 * @since 0.1.0
	 *
	 * @param int                  $id   Group id.
	 * @param array<string, mixed> $data Changed columns: name, slug, description, status, sort_order.
	 * @return bool
	 */
	public function update( int $id, array $data ): bool {
		$formats = array(
			'name'        => '%s',
			'slug'        => '%s',
			'description' => '%s',
			'status'      => '%s',
			'sort_order'  => '%d',
		);
		$row     = array_intersect_key( $data, $formats );

		if ( array() === $row ) {
			return true;
		}

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

	/**
	 * Deletes a group.
	 *
	 * @since 0.1.0
	 *
	 * @param int $id Group id.
	 * @return bool
	 */
	public function delete( int $id ): bool {
		return false !== $this->wpdb->delete( Tables::name( 'product_groups' ), array( 'id' => $id ), array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
	}

	/**
	 * Number of products in a group.
	 *
	 * @since 0.1.0
	 *
	 * @param int $id Group id.
	 * @return int
	 */
	public function product_count( int $id ): int {
		$table = Tables::name( 'products' );

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

	/**
	 * Saves a display order: the group at position 0 gets sort order 1, and so on.
	 *
	 * @since 0.1.0
	 *
	 * @param int[] $ids Group ids in the wanted order.
	 * @return void
	 */
	public function reorder( array $ids ): void {
		foreach ( array_values( $ids ) as $position => $id ) {
			$this->update( (int) $id, array( 'sort_order' => $position + 1 ) );
		}
	}

	/**
	 * Finds one group by a condition with a single placeholder.
	 *
	 * @param string     $condition WHERE condition.
	 * @param int|string $value     Value.
	 * @return array<string, mixed>|null
	 */
	private function find_by( string $condition, $value ): ?array {
		$groups   = Tables::name( 'product_groups' );
		$products = Tables::name( 'products' );
		$row      = $this->row(
			"SELECT g.*, ( SELECT COUNT(*) FROM {$products} p WHERE p.group_id = g.id ) AS product_count FROM {$groups} g WHERE {$condition}",
			array( $value )
		);

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

	/**
	 * Casts a row.
	 *
	 * @param array<string, mixed> $row Database row.
	 * @return array<string, mixed>
	 */
	private function hydrate( array $row ): array {
		$row['id']            = (int) $row['id'];
		$row['sort_order']    = (int) $row['sort_order'];
		$row['product_count'] = (int) ( $row['product_count'] ?? 0 );

		return $row;
	}
}