<?php
/**
 * Coupon 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 coupons.
 *
 * @since 0.1.0
 */
final class CouponRepository extends AbstractRepository {

	/**
	 * Sortable columns: public name to SQL expression.
	 */
	const ORDERBY = array(
		'id'      => 'c.id',
		'code'    => 'c.code',
		'expires' => 'c.expires_on',
		'used'    => 'c.used_count',
	);

	/**
	 * Columns written to the coupons table and their formats.
	 */
	const COLUMNS = array(
		'code'               => '%s',
		'type'               => '%s',
		'value'              => '%d',
		'starts_on'          => '%s',
		'expires_on'         => '%s',
		'usage_limit'        => '%d',
		'per_customer_limit' => '%d',
		'applies_to'         => '%s',
		'status'             => '%s',
	);

	/**
	 * Order statuses that do not count as a use of the coupon.
	 */
	const UNUSED_ORDER_STATUSES = array( 'cancelled', 'refunded', 'fraud' );

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

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

	/**
	 * Finds a coupon by its code.
	 *
	 * @since 0.1.0
	 *
	 * @param string $code Coupon code, any case.
	 * @return array<string, mixed>|null
	 */
	public function find_by_code( string $code ): ?array {
		$table = Tables::name( 'coupons' );
		$row   = $this->row( "SELECT * FROM {$table} WHERE code = %s", array( strtoupper( trim( $code ) ) ) );

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

	/**
	 * Tells whether a code is taken.
	 *
	 * @since 0.1.0
	 *
	 * @param string $code    Coupon code.
	 * @param int    $exclude Coupon id to ignore.
	 * @return bool
	 */
	public function code_exists( string $code, int $exclude = 0 ): bool {
		$table = Tables::name( 'coupons' );

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

	/**
	 * Lists coupons.
	 *
	 * Arguments: `search`, `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'   => '',
				'status'   => '',
				'orderby'  => 'id',
				'order'    => 'desc',
				'page'     => 1,
				'per_page' => 20,
			),
			$args
		);

		$table  = Tables::name( 'coupons' );
		$where  = array( '1 = 1' );
		$params = array();

		if ( '' !== trim( (string) $args['search'] ) ) {
			$where[]  = 'c.code LIKE %s';
			$params[] = '%' . $this->wpdb->esc_like( strtoupper( trim( (string) $args['search'] ) ) ) . '%';
		}

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

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

		$rows = $this->rows(
			"SELECT c.* FROM {$table} c WHERE {$where_sql} ORDER BY {$orderby} {$order}, c.id DESC LIMIT %d OFFSET %d",
			array_merge( $params, array( $per_page, $offset ) )
		);

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

	/**
	 * Inserts a coupon.
	 *
	 * @since 0.1.0
	 *
	 * @param array<string, mixed> $data Coupon columns; applies_to is an array.
	 * @return int New id, or 0 on failure.
	 */
	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( 'coupons' ), $row, $this->formats_for( $row ) );
	}

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

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

	/**
	 * Number of orders that used a coupon.
	 *
	 * @since 0.1.0
	 *
	 * @param int $id Coupon id.
	 * @return int
	 */
	public function reference_count( int $id ): int {
		$orders = Tables::name( 'orders' );

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

	/**
	 * How many times a customer used a coupon, not counting cancelled, refunded or fraudulent orders.
	 *
	 * @since 0.1.0
	 *
	 * @param int $coupon_id   Coupon id.
	 * @param int $customer_id Customer id.
	 * @return int
	 */
	public function customer_uses( int $coupon_id, int $customer_id ): int {
		$orders       = Tables::name( 'orders' );
		$placeholders = implode( ',', array_fill( 0, count( self::UNUSED_ORDER_STATUSES ), '%s' ) );

		return (int) $this->value(
			"SELECT COUNT(*) FROM {$orders} WHERE coupon_id = %d AND customer_id = %d AND status NOT IN ({$placeholders})",
			array_merge( array( $coupon_id, $customer_id ), self::UNUSED_ORDER_STATUSES )
		);
	}

	/**
	 * Adds one to the used counter. Done in SQL so two orders at once cannot lose a count.
	 *
	 * @since 0.1.0
	 *
	 * @param int $id Coupon id.
	 * @return void
	 */
	public function increment_used( int $id ): void {
		$table = Tables::name( 'coupons' );

		$this->wpdb->query( $this->prepare( "UPDATE {$table} SET used_count = used_count + 1 WHERE id = %d", array( $id ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from the Tables whitelist; value prepared by prepare().
	}

	/**
	 * Takes one off the used counter, never below zero. Used when an order is cancelled or refunded.
	 *
	 * @since 0.1.0
	 *
	 * @param int $id Coupon id.
	 * @return void
	 */
	public function decrement_used( int $id ): void {
		$table = Tables::name( 'coupons' );

		$this->wpdb->query( $this->prepare( "UPDATE {$table} SET used_count = GREATEST( used_count - 1, 0 ) WHERE id = %d", array( $id ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from the Tables whitelist; value prepared by prepare().
	}

	/**
	 * 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 )
		);
	}

	/**
	 * Keeps known columns and encodes lists and empty dates 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 );

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

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

		return $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['value']              = (int) $row['value'];
		$row['usage_limit']        = (int) $row['usage_limit'];
		$row['per_customer_limit'] = (int) $row['per_customer_limit'];
		$row['used_count']         = (int) $row['used_count'];
		$row['starts_on']          = (string) $row['starts_on'];
		$row['expires_on']         = (string) $row['expires_on'];

		$applies           = Helpers::json_decode_array( $row['applies_to'] );
		$row['applies_to'] = array(
			'product_ids' => array_map( 'intval', (array) ( $applies['product_ids'] ?? array() ) ),
			'cycles'      => array_values( (array) ( $applies['cycles'] ?? array() ) ),
		);

		return $row;
	}
}