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

namespace HostKhata\Database\Repositories;

defined( 'ABSPATH' ) || exit;

use HostKhata\Database\Tables;
use HostKhata\Domain\Orders\OrderStatus;
use HostKhata\Support\Date;
use HostKhata\Support\Helpers;

/**
 * Reads and writes orders and their lines.
 *
 * @since 0.1.0
 */
final class OrderRepository extends AbstractRepository {

	/**
	 * Sortable columns: public name to SQL expression.
	 */
	const ORDERBY = array(
		'id'      => 'o.id',
		'number'  => 'o.order_number',
		'created' => 'o.created_at_gmt',
		'total'   => 'o.total',
		'status'  => 'o.status',
	);

	/**
	 * Columns written to the orders table and their formats.
	 */
	const COLUMNS = array(
		'order_number'     => '%s',
		'customer_id'      => '%d',
		'status'           => '%s',
		'subtotal'         => '%d',
		'discount_total'   => '%d',
		'tax_total'        => '%d',
		'total'            => '%d',
		'coupon_id'        => '%d',
		'coupon_code'      => '%s',
		'payment_method'   => '%s',
		'ip_address'       => '%s',
		'customer_notes'   => '%s',
		'admin_notes'      => '%s',
		'paid_at_gmt'      => '%s',
		'fulfilled_at_gmt' => '%s',
	);

	/**
	 * Columns written to the order_items table and their formats.
	 */
	const ITEM_COLUMNS = array(
		'order_id'      => '%d',
		'product_id'    => '%d',
		'item_type'     => '%s',
		'description'   => '%s',
		'billing_cycle' => '%s',
		'quantity'      => '%d',
		'unit_price'    => '%d',
		'setup_fee'     => '%d',
		'tax_amount'    => '%d',
		'total'         => '%d',
		'domain_name'   => '%s',
		'meta'          => '%s',
	);

	/**
	 * Finds an order with its lines.
	 *
	 * @since 0.1.0
	 *
	 * @param int $id Order id.
	 * @return array<string, mixed>|null
	 */
	public function find( int $id ): ?array {
		return $this->find_by( 'o.id = %d', $id );
	}

	/**
	 * Finds an order by its number.
	 *
	 * @since 0.1.0
	 *
	 * @param string $number Order number.
	 * @return array<string, mixed>|null
	 */
	public function find_by_number( string $number ): ?array {
		return $this->find_by( 'o.order_number = %s', $number );
	}

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

		$from   = $this->from_clause();
		$where  = array( '1 = 1' );
		$params = array();

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

		if ( '' !== $search ) {
			$like    = '%' . $this->wpdb->esc_like( $search ) . '%';
			$where[] = '(o.order_number LIKE %s OR c.first_name LIKE %s OR c.last_name LIKE %s OR c.company LIKE %s OR u.user_email LIKE %s)';
			$params  = array_merge( $params, array_fill( 0, 5, $like ) );
		}

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

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

		$where_sql = implode( ' AND ', $where );
		$total     = (int) $this->value( "SELECT COUNT(*) {$from} 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 o.*, c.first_name AS customer_first_name, c.last_name AS customer_last_name, c.company AS customer_company, u.user_email AS customer_email {$from} WHERE {$where_sql} ORDER BY {$orderby} {$order}, o.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 orders per status.
	 *
	 * @since 0.1.0
	 *
	 * @return array<string, int>
	 */
	public function status_counts(): array {
		$table  = Tables::name( 'orders' );
		$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;
	}

	/**
	 * How many units of a product are held by orders that have not been released.
	 *
	 * @since 0.1.0
	 *
	 * @param int $product_id Product id.
	 * @return int
	 */
	public function stock_used( int $product_id ): int {
		$orders       = Tables::name( 'orders' );
		$items        = Tables::name( 'order_items' );
		$released     = OrderStatus::released();
		$placeholders = implode( ',', array_fill( 0, count( $released ), '%s' ) );

		return (int) $this->value(
			"SELECT COALESCE( SUM( i.quantity ), 0 ) FROM {$items} i INNER JOIN {$orders} o ON o.id = i.order_id WHERE i.product_id = %d AND o.status NOT IN ({$placeholders})",
			array_merge( array( $product_id ), $released )
		);
	}

	/**
	 * Inserts an order.
	 *
	 * @since 0.1.0
	 *
	 * @param array<string, mixed> $data Order columns; `order_number` and `customer_id` are required.
	 * @return int New id, or 0 on failure.
	 */
	public function insert( array $data ): int {
		$now  = Date::now_gmt();
		$row  = array_intersect_key( $data, self::COLUMNS );
		$row += array(
			'created_at_gmt' => $now,
			'updated_at_gmt' => $now,
		);

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

	/**
	 * Inserts an order line.
	 *
	 * @since 0.1.0
	 *
	 * @param array<string, mixed> $item Line columns; `meta` is given as an array.
	 * @return int New id, or 0 on failure.
	 */
	public function insert_item( array $item ): int {
		$row = array_intersect_key( $item, self::ITEM_COLUMNS );

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

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

	/**
	 * Updates an order.
	 *
	 * @since 0.1.0
	 *
	 * @param int                  $id   Order id.
	 * @param array<string, mixed> $data Changed columns.
	 * @return bool
	 */
	public function update( int $id, array $data ): bool {
		$row                   = array_intersect_key( $data, self::COLUMNS );
		$row['updated_at_gmt'] = Date::now_gmt();

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

	/**
	 * Deletes an order and its lines. Only used to roll back an order that could not be completed.
	 *
	 * @since 0.1.0
	 *
	 * @param int $id Order id.
	 * @return void
	 */
	public function delete( int $id ): void {
		$this->wpdb->delete( Tables::name( 'order_items' ), array( 'order_id' => $id ), array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
		$this->wpdb->delete( Tables::name( 'orders' ), array( 'id' => $id ), array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
	}

	/**
	 * Takes a named database lock, so two requests cannot both pass a stock check.
	 *
	 * @since 0.1.0
	 *
	 * @param string $name    Lock name.
	 * @param int    $seconds How long to wait.
	 * @return bool True when the lock was obtained.
	 */
	public function lock( string $name, int $seconds = 5 ): bool {
		return '1' === (string) $this->value( 'SELECT GET_LOCK( %s, %d )', array( $this->lock_name( $name ), $seconds ) );
	}

	/**
	 * Releases a named lock.
	 *
	 * @since 0.1.0
	 *
	 * @param string $name Lock name.
	 * @return void
	 */
	public function unlock( string $name ): void {
		$this->value( 'SELECT RELEASE_LOCK( %s )', array( $this->lock_name( $name ) ) );
	}

	/**
	 * Lock names are per database, so two sites on one server do not block each other.
	 *
	 * @param string $name Lock name.
	 * @return string
	 */
	private function lock_name( string $name ): string {
		return substr( 'hk_' . md5( $this->wpdb->prefix . $name ), 0, 60 );
	}

	/**
	 * The FROM part shared by list queries: orders with customer and user.
	 *
	 * @return string
	 */
	private function from_clause(): string {
		$orders    = Tables::name( 'orders' );
		$customers = Tables::name( 'customers' );

		return "FROM {$orders} o LEFT JOIN {$customers} c ON c.id = o.customer_id LEFT JOIN {$this->wpdb->users} u ON u.ID = c.user_id";
	}

	/**
	 * Finds one order by a condition with a single placeholder, with its lines.
	 *
	 * @param string     $condition WHERE condition.
	 * @param int|string $value     Value.
	 * @return array<string, mixed>|null
	 */
	private function find_by( string $condition, $value ): ?array {
		$from = $this->from_clause();
		$row  = $this->row(
			"SELECT o.*, c.first_name AS customer_first_name, c.last_name AS customer_last_name, c.company AS customer_company, u.user_email AS customer_email {$from} WHERE {$condition}",
			array( $value )
		);

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

		$order          = $this->hydrate( $row );
		$items          = Tables::name( 'order_items' );
		$order['items'] = array_map( array( $this, 'hydrate_item' ), $this->rows( "SELECT * FROM {$items} WHERE order_id = %d ORDER BY id ASC", array( $order['id'] ) ) );

		return $order;
	}

	/**
	 * Casts an order row.
	 *
	 * @param array<string, mixed> $row Database row.
	 * @return array<string, mixed>
	 */
	private function hydrate( array $row ): array {
		foreach ( array( 'id', 'customer_id', 'subtotal', 'discount_total', 'tax_total', 'total', 'coupon_id' ) as $column ) {
			$row[ $column ] = (int) $row[ $column ];
		}

		$name                  = trim( ( $row['customer_first_name'] ?? '' ) . ' ' . ( $row['customer_last_name'] ?? '' ) );
		$row['customer_name']  = '' !== $name ? $name : (string) ( $row['customer_company'] ?? '' );
		$row['customer_email'] = (string) ( $row['customer_email'] ?? '' );

		return $row;
	}

	/**
	 * Casts an order line row.
	 *
	 * @param array<string, mixed> $row Database row.
	 * @return array<string, mixed>
	 */
	private function hydrate_item( array $row ): array {
		foreach ( array( 'id', 'order_id', 'product_id', 'quantity', 'unit_price', 'setup_fee', 'tax_amount', 'total' ) as $column ) {
			$row[ $column ] = (int) $row[ $column ];
		}

		$row['meta'] = Helpers::json_decode_array( $row['meta'] );

		return $row;
	}
}