<?php
/**
 * Listings in the plugin's own table (WordPress database). Never touches WooCommerce data.
 *
 * @package Tidelist
 */

namespace Tidelist\Data;

use InvalidArgumentException;

/**
 * Listing table access.
 */
final class ListingRepository implements ListingStore {

	const TABLE = 'tidelist_listings';

	/**
	 * Columns the plugin may write, with their wpdb formats.
	 */
	const WRITABLE = array(
		'product_id'      => '%d',
		'variation_id'    => '%d',
		'channel'         => '%s',
		'account_id'      => '%d',
		'remote_id'       => '%s',
		'remote_url'      => '%s',
		'remote_status'   => '%s',
		'remote_category' => '%d',
		'status'          => '%s',
		'payload_hash'    => '%s',
		'valid_to'        => '%s',
		'last_synced_at'  => '%s',
		'last_error'      => '%s',
	);

	/**
	 * Full table name.
	 */
	public static function table(): string {
		global $wpdb;
		return $wpdb->prefix . self::TABLE;
	}

	/**
	 * CREATE TABLE statement for dbDelta.
	 *
	 * A simple product has variation_id 0 (a unique key cannot hold NULLs the way we need);
	 * the repository shows it as null.
	 *
	 * @param string $charset_collate Charset/collation clause.
	 */
	public static function schema( string $charset_collate ): string {
		$table = self::table();
		return "CREATE TABLE {$table} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			product_id bigint(20) unsigned NOT NULL,
			variation_id bigint(20) unsigned NOT NULL DEFAULT 0,
			channel varchar(20) NOT NULL DEFAULT '',
			account_id bigint(20) unsigned NOT NULL DEFAULT 0,
			remote_id varchar(64) NOT NULL DEFAULT '',
			remote_url varchar(255) NOT NULL DEFAULT '',
			remote_status varchar(30) NOT NULL DEFAULT '',
			remote_category bigint(20) unsigned NOT NULL DEFAULT 0,
			status varchar(20) NOT NULL DEFAULT 'draft',
			payload_hash char(40) NOT NULL DEFAULT '',
			valid_to datetime NULL,
			last_synced_at datetime NULL,
			last_error text NULL,
			created_at datetime NOT NULL,
			updated_at datetime NOT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY item (product_id, variation_id, channel, account_id),
			KEY status (status),
			KEY remote (channel, remote_id)
		) {$charset_collate};";
	}

	/**
	 * Validates and completes the fields of a new listing (pure).
	 *
	 * @param array  $fields Fields.
	 * @param string $now    Current time (Y-m-d H:i:s, UTC).
	 * @return array Row to insert.
	 * @throws InvalidArgumentException On missing or invalid data.
	 */
	public static function prepare_insert( array $fields, string $now ): array {
		$data = self::prepare_update( $fields );
		if ( empty( $data['product_id'] ) ) {
			throw new InvalidArgumentException( 'A listing needs a product_id.' );
		}
		if ( empty( $data['channel'] ) ) {
			throw new InvalidArgumentException( 'A listing needs a channel.' );
		}
		return array_merge(
			array(
				'variation_id' => 0,
				'account_id'   => 0,
				'status'       => ListingStatus::DRAFT,
			),
			$data,
			array(
				'created_at' => $now,
				'updated_at' => $now,
			)
		);
	}

	/**
	 * Keeps only writable columns and validates them (pure).
	 *
	 * @param array $fields Fields. A null variation_id means a simple product.
	 * @throws InvalidArgumentException On an invalid status.
	 */
	public static function prepare_update( array $fields ): array {
		$data = array_intersect_key( $fields, self::WRITABLE );
		if ( array_key_exists( 'variation_id', $data ) ) {
			$data['variation_id'] = (int) $data['variation_id'];
		}
		if ( isset( $data['status'] ) && ! ListingStatus::is_valid( (string) $data['status'] ) ) {
			throw new InvalidArgumentException( 'Unknown listing status "' . $data['status'] . '".' ); // phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Plain-text message, escaped wherever it is shown.
		}
		return $data;
	}

	/**
	 * Row as the plugin sees it: numbers as integers, variation_id 0 as null (pure).
	 *
	 * @param array $row Database row.
	 */
	public static function normalize_row( array $row ): array {
		foreach ( array( 'id', 'product_id', 'account_id', 'remote_category' ) as $key ) {
			if ( isset( $row[ $key ] ) ) {
				$row[ $key ] = (int) $row[ $key ];
			}
		}
		if ( array_key_exists( 'variation_id', $row ) ) {
			$row['variation_id'] = (int) $row['variation_id'] > 0 ? (int) $row['variation_id'] : null;
		}
		return $row;
	}

	/**
	 * One listing.
	 *
	 * @param int $id Listing ID.
	 */
	public function find( int $id ): ?array {
		global $wpdb;
		$table = self::table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- own table.
		$row = $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', $table, $id ), ARRAY_A );
		return is_array( $row ) ? self::normalize_row( $row ) : null;
	}

	/**
	 * The listing of one shop item on one marketplace account.
	 *
	 * @param int      $product_id   Product ID.
	 * @param int|null $variation_id Variation ID or null.
	 * @param string   $channel      Channel ID.
	 * @param int      $account_id   Account ID.
	 */
	public function find_for_item( int $product_id, ?int $variation_id, string $channel, int $account_id ): ?array {
		global $wpdb;
		$table = self::table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- own table.
		$row = $wpdb->get_row(
			$wpdb->prepare( 'SELECT * FROM %i WHERE product_id = %d AND variation_id = %d AND channel = %s AND account_id = %d', $table, $product_id, (int) $variation_id, $channel, $account_id ),
			ARRAY_A
		);
		return is_array( $row ) ? self::normalize_row( $row ) : null;
	}

	/**
	 * Inserts a listing.
	 *
	 * @param array $fields Fields.
	 * @throws InvalidArgumentException On invalid data or a duplicate item.
	 */
	public function insert( array $fields ): int {
		global $wpdb;
		$data = self::prepare_insert( $fields, gmdate( 'Y-m-d H:i:s' ) );
		if ( null !== $this->find_for_item( (int) $data['product_id'], (int) $data['variation_id'], (string) $data['channel'], (int) $data['account_id'] ) ) {
			throw new InvalidArgumentException( 'This item already has a listing on this account.' );
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- own table.
		$wpdb->insert( self::table(), $data );
		return (int) $wpdb->insert_id;
	}

	/**
	 * Updates a listing.
	 *
	 * @param int   $id     Listing ID.
	 * @param array $fields Fields to change.
	 */
	public function update( int $id, array $fields ): void {
		global $wpdb;
		$data = self::prepare_update( $fields );
		if ( ! $data ) {
			return;
		}
		$data['updated_at'] = gmdate( 'Y-m-d H:i:s' );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- own table.
		$wpdb->update( self::table(), $data, array( 'id' => $id ) );
	}

	/**
	 * Listings, newest first.
	 *
	 * @param array $filters Filters.
	 * @param int   $limit   Maximum rows.
	 * @param int   $offset  Rows to skip.
	 * @return array<int, array>
	 */
	public function all( array $filters = array(), int $limit = 0, int $offset = 0 ): array {
		global $wpdb;
		$table  = self::table();
		$where  = array( '1=1' );
		$params = array();
		if ( isset( $filters['status'] ) ) {
			$statuses = array_values( array_filter( array_map( 'strval', (array) $filters['status'] ), array( ListingStatus::class, 'is_valid' ) ) );
			if ( ! $statuses ) {
				return array();
			}
			$where[] = 'status IN (' . implode( ',', array_fill( 0, count( $statuses ), '%s' ) ) . ')';
			$params  = array_merge( $params, $statuses );
		}
		if ( isset( $filters['channel'] ) ) {
			$where[]  = 'channel = %s';
			$params[] = (string) $filters['channel'];
		}
		foreach ( array( 'account_id', 'product_id' ) as $key ) {
			if ( isset( $filters[ $key ] ) ) {
				$where[]  = $key . ' = %d';
				$params[] = (int) $filters[ $key ];
			}
		}
		$sql = 'SELECT * FROM %i WHERE ' . implode( ' AND ', $where ) . ' ORDER BY id DESC';
		if ( $limit > 0 ) {
			$sql     .= ' LIMIT %d OFFSET %d';
			$params[] = $limit;
			$params[] = max( 0, $offset );
		}
		$params = array_merge( array( $table ), $params );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- own table; $sql only joins fixed fragments with placeholders, values go through prepare().
		$rows = (array) $wpdb->get_results( $wpdb->prepare( $sql, $params ), ARRAY_A );
		return array_map( array( self::class, 'normalize_row' ), $rows );
	}

	/**
	 * Number of listings per status.
	 *
	 * @return array<string, int>
	 */
	public function count_by_status(): array {
		global $wpdb;
		$table = self::table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- own table.
		$rows = (array) $wpdb->get_results( $wpdb->prepare( 'SELECT status, COUNT(*) AS n FROM %i GROUP BY status', $table ), ARRAY_A );
		$out  = array();
		foreach ( $rows as $row ) {
			$out[ (string) $row['status'] ] = (int) $row['n'];
		}
		return $out;
	}
}