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

namespace HostKhata\Database\Repositories;

defined( 'ABSPATH' ) || exit;

use HostKhata\Database\Tables;
use HostKhata\Domain\Customers\Tags;
use HostKhata\Support\Date;

/**
 * Reads and writes customer rows. A customer is a profile row joined to its WordPress user.
 *
 * @since 0.1.0
 */
final class CustomerRepository extends AbstractRepository {

	/**
	 * Sortable columns: public name to SQL expression.
	 */
	const ORDERBY = array(
		'id'      => 'c.id',
		'name'    => 'c.first_name',
		'email'   => 'u.user_email',
		'company' => 'c.company',
		'status'  => 'c.status',
		'credit'  => 'c.credit_balance',
		'created' => 'c.created_at_gmt',
	);

	/**
	 * Columns written to the customers table and their formats.
	 */
	const COLUMNS = array(
		'user_id'         => '%d',
		'first_name'      => '%s',
		'last_name'       => '%s',
		'company'         => '%s',
		'phone'           => '%s',
		'address_1'       => '%s',
		'address_2'       => '%s',
		'city'            => '%s',
		'state'           => '%s',
		'postcode'        => '%s',
		'country'         => '%s',
		'status'          => '%s',
		'tags'            => '%s',
		'reminders_muted' => '%d',
	);

	/**
	 * Finds a customer by id.
	 *
	 * @since 0.1.0
	 *
	 * @param int $id Customer id.
	 * @return array<string, mixed>|null
	 */
	public function find( int $id ): ?array {
		return $this->find_by( 'c.id = %d', $id );
	}

	/**
	 * Finds a customer by WordPress user id.
	 *
	 * @since 0.1.0
	 *
	 * @param int $user_id User id.
	 * @return array<string, mixed>|null
	 */
	public function find_by_user_id( int $user_id ): ?array {
		return $this->find_by( 'c.user_id = %d', $user_id );
	}

	/**
	 * Tells which of the given WordPress users already have a customer.
	 *
	 * @since 0.1.1
	 *
	 * @param int[] $user_ids User ids.
	 * @return int[] The ids that are linked to a customer.
	 */
	public function linked_user_ids( array $user_ids ): array {
		$user_ids = array_values( array_filter( array_map( 'intval', $user_ids ) ) );

		if ( array() === $user_ids ) {
			return array();
		}

		$table = Tables::name( 'customers' );
		$marks = implode( ',', array_fill( 0, count( $user_ids ), '%d' ) );
		$rows  = $this->rows( "SELECT user_id FROM {$table} WHERE user_id IN ({$marks})", $user_ids );

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

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

		$customers = Tables::name( 'customers' );
		$users     = $this->wpdb->users;
		$from      = "FROM {$customers} c INNER JOIN {$users} u ON u.ID = c.user_id";
		$where     = array( '1 = 1' );
		$params    = array();

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

		if ( '' !== $search ) {
			$like    = '%' . $this->wpdb->esc_like( $search ) . '%';
			$clauses = array( 'c.first_name LIKE %s', 'c.last_name LIKE %s', 'c.company LIKE %s', 'c.phone LIKE %s', 'u.user_email LIKE %s', 'u.display_name LIKE %s', 'u.user_login LIKE %s' );
			$params  = array_merge( $params, array_fill( 0, count( $clauses ), $like ) );

			if ( ctype_digit( $search ) ) {
				$clauses[] = 'c.id = %d';
				$params[]  = (int) $search;
			}

			$where[] = '(' . implode( ' OR ', $clauses ) . ')';
		}

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

		if ( '' !== (string) $args['tag'] ) {
			$where[]  = 'c.tags LIKE %s';
			$params[] = '%,' . $this->wpdb->esc_like( (string) $args['tag'] ) . ',%';
		}

		$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 c.*, u.user_email, u.user_login, u.display_name {$from} 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 customer. `user_id` is required.
	 *
	 * @since 0.1.0
	 *
	 * @param array<string, mixed> $data Column values, with `tags` as a list.
	 * @return int New customer 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( 'customers' ), $row, $this->formats_for( $row ) );
	}

	/**
	 * Updates a customer.
	 *
	 * @since 0.1.0
	 *
	 * @param int                  $id   Customer id.
	 * @param array<string, mixed> $data Changed columns only.
	 * @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( 'customers' ), $row, array( 'id' => $id ), $this->formats_for( $row ), array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
	}

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

	/**
	 * Counts the records that belong to a customer in the billing tables.
	 *
	 * @since 0.1.0
	 *
	 * @param int $id Customer id.
	 * @return array<string, int> Counts keyed by object type: order, invoice, service, domain, payment.
	 */
	public function related_counts( int $id ): array {
		$counts = array();

		foreach ( OwnershipRepository::OWNED_TABLES as $type => $key ) {
			$table           = Tables::name( $key );
			$counts[ $type ] = (int) $this->value( "SELECT COUNT(*) FROM {$table} WHERE customer_id = %d", array( $id ) );
		}

		return $counts;
	}

	/**
	 * Every distinct tag in use.
	 *
	 * @since 0.1.0
	 *
	 * @return string[]
	 */
	public function all_tags(): array {
		$table = Tables::name( 'customers' );
		$rows  = $this->rows( "SELECT DISTINCT tags FROM {$table} WHERE tags <> ''" );
		$tags  = array();

		foreach ( $rows as $row ) {
			$tags = array_merge( $tags, Tags::from_storage( (string) $row['tags'] ) );
		}

		$tags = array_values( array_unique( $tags ) );
		sort( $tags );

		return $tags;
	}

	/**
	 * Counts customers per status.
	 *
	 * @since 0.1.0
	 *
	 * @return array<string, int>
	 */
	public function count_by_status(): array {
		$table  = Tables::name( 'customers' );
		$rows   = $this->rows( "SELECT status, COUNT(*) AS total FROM {$table} GROUP BY status" );
		$counts = array();

		foreach ( $rows as $row ) {
			$counts[ (string) $row['status'] ] = (int) $row['total'];
		}

		return $counts;
	}

	/**
	 * Finds one customer with a WHERE clause on a single id.
	 *
	 * @param string $condition WHERE condition with one %d placeholder.
	 * @param int    $value     Value.
	 * @return array<string, mixed>|null
	 */
	private function find_by( string $condition, int $value ): ?array {
		$customers = Tables::name( 'customers' );
		$users     = $this->wpdb->users;
		$row       = $this->row(
			"SELECT c.*, u.user_email, u.user_login, u.display_name FROM {$customers} c INNER JOIN {$users} u ON u.ID = c.user_id WHERE {$condition}",
			array( $value )
		);

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

	/**
	 * Casts a database row to typed values.
	 *
	 * @param array<string, mixed> $row Database row.
	 * @return array<string, mixed>
	 */
	private function hydrate( array $row ): array {
		$row['id']              = (int) $row['id'];
		$row['user_id']         = (int) $row['user_id'];
		$row['credit_balance']  = (int) $row['credit_balance'];
		$row['reminders_muted'] = (bool) $row['reminders_muted'];
		$row['tags']            = Tags::from_storage( (string) $row['tags'] );

		return $row;
	}

	/**
	 * Placeholder formats in the same order as the keys of a row.
	 *
	 * @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 only known columns and converts tags 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['tags'] ) && is_array( $row['tags'] ) ) {
			$row['tags'] = Tags::to_storage( $row['tags'] );
		}

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

		return $row;
	}
}