<?php
/**
 * Product 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 products together with their price rows.
 *
 * @since 0.1.0
 */
final class ProductRepository extends AbstractRepository {

	/**
	 * Sortable columns: public name to SQL expression.
	 */
	const ORDERBY = array(
		'sort_order' => 'p.sort_order',
		'name'       => 'p.name',
		'id'         => 'p.id',
		'created'    => 'p.created_at_gmt',
		'type'       => 'p.type',
	);

	/**
	 * Columns written to the products table and their formats.
	 */
	const COLUMNS = array(
		'group_id'      => '%d',
		'type'          => '%s',
		'name'          => '%s',
		'slug'          => '%s',
		'description'   => '%s',
		'features'      => '%s',
		'custom_fields' => '%s',
		'addon_ids'     => '%s',
		'tax_class'     => '%s',
		'stock_limit'   => '%d',
		'is_popular'    => '%d',
		'status'        => '%s',
		'sort_order'    => '%d',
	);

	/**
	 * Finds a product with its prices.
	 *
	 * @since 0.1.0
	 *
	 * @param int $id Product id.
	 * @return array<string, mixed>|null
	 */
	public function find( int $id ): ?array {
		return $this->find_by( 'p.id = %d', $id );
	}

	/**
	 * Finds a product 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( 'p.slug = %s', $slug );
	}

	/**
	 * Lists products with their prices.
	 *
	 * Arguments: `search`, `group_id`, `type`, `status`, `orderby`, `order`, `page`, `per_page`.
	 *
	 * @since 0.1.0
	 *
	 * @param array<string, mixed> $args Query arguments.
	 * @return array{items: array<int, array<string, mixed>>, total: int}
	 */
	public function query( array $args ): array {
		$args = array_merge(
			array(
				'search'   => '',
				'group_id' => 0,
				'type'     => '',
				'status'   => '',
				'orderby'  => 'sort_order',
				'order'    => 'asc',
				'page'     => 1,
				'per_page' => 50,
			),
			$args
		);

		$products = Tables::name( 'products' );
		$groups   = Tables::name( 'product_groups' );
		$from     = "FROM {$products} p LEFT JOIN {$groups} g ON g.id = p.group_id";
		$where    = array( '1 = 1' );
		$params   = array();

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

		if ( '' !== $search ) {
			$like     = '%' . $this->wpdb->esc_like( $search ) . '%';
			$where[]  = '(p.name LIKE %s OR p.slug LIKE %s OR p.description LIKE %s)';
			$params[] = $like;
			$params[] = $like;
			$params[] = $like;
		}

		if ( (int) $args['group_id'] > 0 ) {
			$where[]  = 'p.group_id = %d';
			$params[] = (int) $args['group_id'];
		}

		foreach ( array( 'type', 'status' ) as $column ) {
			if ( '' !== (string) $args[ $column ] ) {
				$where[]  = "p.{$column} = %s";
				$params[] = (string) $args[ $column ];
			}
		}

		$where_sql = implode( ' AND ', $where );
		$total     = (int) $this->value( "SELECT COUNT(*) {$from} WHERE {$where_sql}", $params );

		$orderby  = self::ORDERBY[ $args['orderby'] ] ?? self::ORDERBY['sort_order'];
		$order    = 'desc' === strtolower( (string) $args['order'] ) ? 'DESC' : 'ASC';
		$per_page = max( 1, min( 100, (int) $args['per_page'] ) );
		$offset   = max( 0, ( (int) $args['page'] - 1 ) * $per_page );

		$rows = $this->rows(
			"SELECT p.*, g.name AS group_name {$from} WHERE {$where_sql} ORDER BY {$orderby} {$order}, p.id ASC LIMIT %d OFFSET %d",
			array_merge( $params, array( $per_page, $offset ) )
		);

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

	/**
	 * Active products in active groups, for the public listing.
	 *
	 * @since 0.1.0
	 *
	 * @param string $group_slug Only this group, or an empty string for all.
	 * @param string $type       Only this product type, or an empty string for all.
	 * @return array<int, array<string, mixed>>
	 */
	public function public_listing( string $group_slug = '', string $type = '' ): array {
		$products = Tables::name( 'products' );
		$groups   = Tables::name( 'product_groups' );
		$where    = array( "p.status = 'active'", "g.status = 'active'" );
		$params   = array();

		if ( '' !== $group_slug ) {
			$where[]  = 'g.slug = %s';
			$params[] = $group_slug;
		}

		if ( '' !== $type ) {
			$where[]  = 'p.type = %s';
			$params[] = $type;
		}

		$rows = $this->rows(
			"SELECT p.*, g.name AS group_name FROM {$products} p INNER JOIN {$groups} g ON g.id = p.group_id WHERE " . implode( ' AND ', $where ) . ' ORDER BY g.sort_order ASC, p.sort_order ASC, p.id ASC LIMIT 100',
			$params
		);

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

	/**
	 * Finds the domain product for an extension, for example `com` finds `.com`.
	 *
	 * @since 0.1.0
	 *
	 * @param string $tld Extension without the dot.
	 * @return array<string, mixed>|null
	 */
	public function find_tld( string $tld ): ?array {
		return $this->find_by( "p.type = 'domain' AND p.name = %s", '.' . ltrim( strtolower( $tld ), '.' ) );
	}

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

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

	/**
	 * Of the given ids, those that are products of the given type.
	 *
	 * @since 0.1.0
	 *
	 * @param int[]  $ids  Product ids.
	 * @param string $type Product type.
	 * @return int[]
	 */
	public function ids_of_type( array $ids, string $type ): array {
		if ( array() === $ids ) {
			return array();
		}

		$table        = Tables::name( 'products' );
		$placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
		$rows         = $this->rows( "SELECT id FROM {$table} WHERE type = %s AND id IN ({$placeholders})", array_merge( array( $type ), array_map( 'intval', $ids ) ) );

		return array_map( 'intval', array_column( $rows, 'id' ) );
	}

	/**
	 * Inserts a product, placing it last.
	 *
	 * @since 0.1.0
	 *
	 * @param array<string, mixed> $data Product columns; lists are given as arrays.
	 * @return int New id, or 0 on failure.
	 */
	public function insert( array $data ): int {
		$table = Tables::name( 'products' );
		$now   = Date::now_gmt();
		$row   = $this->to_row( $data );

		$row['sort_order']     = (int) $this->value( "SELECT COALESCE( MAX( sort_order ), 0 ) + 1 FROM {$table}" );
		$row['created_at_gmt'] = $now;
		$row['updated_at_gmt'] = $now;

		return $this->insert_row( $table, $row, $this->formats_for( $row ) );
	}

	/**
	 * Updates a product.
	 *
	 * @since 0.1.0
	 *
	 * @param int                  $id   Product 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( 'products' ), $row, array( 'id' => $id ), $this->formats_for( $row ), array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
	}

	/**
	 * Deletes a product and its prices.
	 *
	 * @since 0.1.0
	 *
	 * @param int $id Product id.
	 * @return bool
	 */
	public function delete( int $id ): bool {
		$this->wpdb->delete( Tables::name( 'product_prices' ), array( 'product_id' => $id ), array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching

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

	/**
	 * Replaces all price rows of a product.
	 *
	 * @since 0.1.0
	 *
	 * @param int                              $id   Product id.
	 * @param array<int, array<string, mixed>> $rows price_type, billing_cycle, period_months, price, setup_fee.
	 * @return void
	 */
	public function replace_prices( int $id, array $rows ): void {
		$table = Tables::name( 'product_prices' );

		$this->wpdb->delete( $table, array( 'product_id' => $id ), array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching

		foreach ( $rows as $row ) {
			$this->insert_row(
				$table,
				array(
					'product_id'    => $id,
					'price_type'    => (string) $row['price_type'],
					'billing_cycle' => (string) $row['billing_cycle'],
					'period_months' => (int) $row['period_months'],
					'price'         => (int) $row['price'],
					'setup_fee'     => (int) $row['setup_fee'],
				),
				array( '%d', '%s', '%s', '%d', '%d', '%d' )
			);
		}
	}

	/**
	 * Counts the records that use a product: order lines and hosting services.
	 *
	 * @since 0.1.0
	 *
	 * @param int $id Product id.
	 * @return int
	 */
	public function reference_count( int $id ): int {
		$items    = Tables::name( 'order_items' );
		$services = Tables::name( 'services' );

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

	/**
	 * Removes a product id from the add-on lists of all products.
	 *
	 * @since 0.1.0
	 *
	 * @param int $addon_id Deleted add-on id.
	 * @return void
	 */
	public function forget_addon( int $addon_id ): void {
		$table = Tables::name( 'products' );
		$rows  = $this->rows( "SELECT id, addon_ids FROM {$table} WHERE addon_ids IS NOT NULL AND addon_ids <> '' AND addon_ids <> '[]'" );

		foreach ( $rows as $row ) {
			$ids = array_map( 'intval', Helpers::json_decode_array( $row['addon_ids'] ) );

			if ( in_array( $addon_id, $ids, true ) ) {
				$this->update( (int) $row['id'], array( 'addon_ids' => array_values( array_diff( $ids, array( $addon_id ) ) ) ) );
			}
		}
	}

	/**
	 * Saves a display order.
	 *
	 * @since 0.1.0
	 *
	 * @param int[] $ids Product 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 product 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 {
		$products = Tables::name( 'products' );
		$groups   = Tables::name( 'product_groups' );
		$row      = $this->row(
			"SELECT p.*, g.name AS group_name FROM {$products} p LEFT JOIN {$groups} g ON g.id = p.group_id WHERE {$condition}",
			array( $value )
		);

		if ( null === $row ) {
			return null;
		}

		return $this->with_prices( array( $this->hydrate( $row ) ) )[0];
	}

	/**
	 * Adds the price rows to products using one query.
	 *
	 * @param array<int, array<string, mixed>> $products Products.
	 * @return array<int, array<string, mixed>>
	 */
	private function with_prices( array $products ): array {
		if ( array() === $products ) {
			return array();
		}

		$ids          = array_column( $products, 'id' );
		$table        = Tables::name( 'product_prices' );
		$placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
		$rows         = $this->rows( "SELECT * FROM {$table} WHERE product_id IN ({$placeholders}) ORDER BY period_months ASC, id ASC", $ids );
		$by_product   = array();

		foreach ( $rows as $row ) {
			$by_product[ (int) $row['product_id'] ][] = array(
				'id'            => (int) $row['id'],
				'price_type'    => $row['price_type'],
				'billing_cycle' => $row['billing_cycle'],
				'period_months' => (int) $row['period_months'],
				'price'         => (int) $row['price'],
				'setup_fee'     => (int) $row['setup_fee'],
			);
		}

		foreach ( $products as &$product ) {
			$product['prices'] = $by_product[ $product['id'] ] ?? array();
		}

		return $products;
	}

	/**
	 * Casts a database row.
	 *
	 * @param array<string, mixed> $row Database row.
	 * @return array<string, mixed>
	 */
	private function hydrate( array $row ): array {
		$row['id']            = (int) $row['id'];
		$row['group_id']      = (int) $row['group_id'];
		$row['sort_order']    = (int) $row['sort_order'];
		$row['is_popular']    = (bool) $row['is_popular'];
		$row['stock_limit']   = null === $row['stock_limit'] ? null : (int) $row['stock_limit'];
		$row['features']      = Helpers::json_decode_array( $row['features'] );
		$row['custom_fields'] = Helpers::json_decode_array( $row['custom_fields'] );
		$row['addon_ids']     = array_map( 'intval', Helpers::json_decode_array( $row['addon_ids'] ?? '' ) );
		$row['group_name']    = (string) ( $row['group_name'] ?? '' );

		return $row;
	}

	/**
	 * Keeps known columns and encodes lists for storage.
	 *
	 * @param array<string, mixed> $data Input.
	 * @return array<string, mixed>
	 */
	private function to_row( array $data ): array {
		$row = array_intersect_key( $data, self::COLUMNS );

		foreach ( array( 'features', 'custom_fields', 'addon_ids' ) as $column ) {
			if ( isset( $row[ $column ] ) && is_array( $row[ $column ] ) ) {
				$row[ $column ] = Helpers::json_encode( array_values( $row[ $column ] ) );
			}
		}

		if ( isset( $row['is_popular'] ) ) {
			$row['is_popular'] = $row['is_popular'] ? 1 : 0;
		}

		return $row;
	}

	/**
	 * Placeholder formats in the order of a row's keys.
	 *
	 * @param array<string, mixed> $row Column values.
	 * @return string[]
	 */
	private function formats_for( array $row ): array {
		return array_map(
			static function ( string $column ) {
				return self::COLUMNS[ $column ] ?? '%s';
			},
			array_keys( $row )
		);
	}
}