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

namespace HostKhata\Database\Repositories;

defined( 'ABSPATH' ) || exit;

use HostKhata\Database\Tables;
use HostKhata\Support\Date;

/**
 * Reads and writes payments and refunds.
 *
 * @since 0.1.0
 */
final class PaymentRepository extends AbstractRepository {

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

	/**
	 * Columns written to the payments table and their formats.
	 */
	const COLUMNS = array(
		'invoice_id'      => '%d',
		'customer_id'     => '%d',
		'type'            => '%s',
		'gateway'         => '%s',
		'status'          => '%s',
		'amount'          => '%d',
		'transaction_id'  => '%s',
		'sender_number'   => '%s',
		'note'            => '%s',
		'verified_by'     => '%d',
		'verified_at_gmt' => '%s',
		'created_at_gmt'  => '%s',
	);

	/**
	 * Finds a payment.
	 *
	 * @since 0.1.0
	 *
	 * @param int $id Payment id.
	 * @return array<string, mixed>|null
	 */
	public function find( int $id ): ?array {
		$row = $this->row( $this->select_sql() . ' WHERE p.id = %d', array( $id ) );

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

	/**
	 * Payments and refunds of an invoice, oldest first.
	 *
	 * @since 0.1.0
	 *
	 * @param int $invoice_id Invoice id.
	 * @return array<int, array<string, mixed>>
	 */
	public function for_invoice( int $invoice_id ): array {
		return array_map( array( $this, 'hydrate' ), $this->rows( $this->select_sql() . ' WHERE p.invoice_id = %d ORDER BY p.id ASC', array( $invoice_id ) ) );
	}

	/**
	 * Sum of the refunds made against one payment.
	 *
	 * @since 0.1.0
	 *
	 * @param int $payment_id Payment id.
	 * @return int Minor units.
	 */
	public function refunded_amount( int $payment_id ): int {
		$table = Tables::name( 'payments' );

		return (int) $this->value( "SELECT COALESCE( SUM( amount ), 0 ) FROM {$table} WHERE type = 'refund' AND status = 'completed' AND transaction_id = %s", array( 'refund-of-' . $payment_id ) );
	}

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

		$where  = array( '1 = 1' );
		$params = array();

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

		if ( '' !== $search ) {
			$like    = '%' . $this->wpdb->esc_like( $search ) . '%';
			$where[] = '(p.transaction_id LIKE %s OR p.sender_number LIKE %s OR i.invoice_number LIKE %s OR c.first_name LIKE %s OR c.last_name LIKE %s)';
			$params  = array_merge( $params, array_fill( 0, 5, $like ) );
		}

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

		foreach ( array( 'customer_id', 'invoice_id' ) as $column ) {
			if ( (int) $args[ $column ] > 0 ) {
				$where[]  = "p.{$column} = %d";
				$params[] = (int) $args[ $column ];
			}
		}

		$where_sql = implode( ' AND ', $where );
		$total     = (int) $this->value( "SELECT COUNT(*) {$this->from_clause()} 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(
			$this->select_sql() . " WHERE {$where_sql} ORDER BY {$orderby} {$order}, p.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 payment or refund.
	 *
	 * @since 0.1.0
	 *
	 * @param array<string, mixed> $data Payment columns.
	 * @return int New id, or 0 on failure.
	 */
	public function insert( array $data ): int {
		$row = array_intersect_key( $data, self::COLUMNS );

		if ( ! isset( $row['created_at_gmt'] ) ) {
			$row['created_at_gmt'] = Date::now_gmt();
		}

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

	/**
	 * Tells whether a payment with the same method and reference already exists.
	 *
	 * @since 0.1.0
	 *
	 * @param string $gateway        Payment method.
	 * @param string $transaction_id Reference such as a bKash transaction id.
	 * @return bool
	 */
	public function reference_exists( string $gateway, string $transaction_id ): bool {
		if ( '' === $transaction_id ) {
			return false;
		}

		$table = Tables::name( 'payments' );

		return (int) $this->value( "SELECT COUNT(*) FROM {$table} WHERE type = 'payment' AND gateway = %s AND transaction_id = %s", array( $gateway, $transaction_id ) ) > 0;
	}

	/**
	 * Updates a payment.
	 *
	 * @since 0.1.0
	 *
	 * @param int                  $id   Payment 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 );

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

	/**
	 * Number of payments waiting to be checked.
	 *
	 * @since 0.1.0
	 *
	 * @param int $invoice_id Invoice id, or 0 for all invoices.
	 * @return int
	 */
	public function pending_count( int $invoice_id = 0 ): int {
		$table = Tables::name( 'payments' );

		return $invoice_id > 0
			? (int) $this->value( "SELECT COUNT(*) FROM {$table} WHERE type = 'payment' AND status = 'pending' AND invoice_id = %d", array( $invoice_id ) )
			: (int) $this->value( "SELECT COUNT(*) FROM {$table} WHERE type = 'payment' AND status = 'pending'" );
	}

	/**
	 * Number of payments of an invoice.
	 *
	 * @since 0.1.0
	 *
	 * @param int $invoice_id Invoice id.
	 * @return int
	 */
	public function count_for_invoice( int $invoice_id ): int {
		$table = Tables::name( 'payments' );

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

	/**
	 * Casts numeric columns.
	 *
	 * @param array<string, mixed> $row Database row.
	 * @return array<string, mixed>
	 */
	private function hydrate( array $row ): array {
		foreach ( array( 'id', 'invoice_id', 'customer_id', 'amount', 'verified_by' ) as $column ) {
			$row[ $column ] = (int) $row[ $column ];
		}

		foreach ( array( 'note', 'verified_at_gmt', 'invoice_number', 'customer_first_name', 'customer_last_name', 'customer_company', 'recorder_name' ) as $column ) {
			$row[ $column ] = (string) ( $row[ $column ] ?? '' );
		}

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

		return $row;
	}

	/**
	 * The FROM part shared by queries.
	 *
	 * @return string
	 */
	private function from_clause(): string {
		$payments  = Tables::name( 'payments' );
		$invoices  = Tables::name( 'invoices' );
		$customers = Tables::name( 'customers' );

		return "FROM {$payments} p LEFT JOIN {$invoices} i ON i.id = p.invoice_id LEFT JOIN {$customers} c ON c.id = p.customer_id LEFT JOIN {$this->wpdb->users} v ON v.ID = p.verified_by";
	}

	/**
	 * The SELECT part shared by queries.
	 *
	 * @return string
	 */
	private function select_sql(): string {
		return "SELECT p.*, i.invoice_number AS invoice_number, c.first_name AS customer_first_name, c.last_name AS customer_last_name, c.company AS customer_company, v.display_name AS recorder_name {$this->from_clause()}";
	}
}