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

namespace HostKhata\Database\Repositories;

defined( 'ABSPATH' ) || exit;

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

/**
 * Reads and writes domain records.
 *
 * @since 0.1.0
 */
final class DomainRepository extends AbstractRepository {

	/**
	 * Sortable columns: public name to SQL expression.
	 */
	const ORDERBY = array(
		'id'      => 'd.id',
		'domain'  => 'd.domain_name',
		'status'  => 'd.status',
		'expires' => 'd.expires_on',
		'created' => 'd.created_at_gmt',
	);

	/**
	 * Columns written to the domains table and their formats.
	 */
	const COLUMNS = array(
		'customer_id'   => '%d',
		'order_id'      => '%d',
		'service_id'    => '%d',
		'domain_name'   => '%s',
		'tld'           => '%s',
		'registrar'     => '%s',
		'status'        => '%s',
		'registered_on' => '%s',
		'expires_on'    => '%s',
		'renewal_years' => '%d',
		'auto_renew'    => '%d',
		'whois_privacy' => '%d',
		'nameservers'   => '%s',
		'notes'         => '%s',
	);

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

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

	/**
	 * Tells whether a domain name is already recorded.
	 *
	 * @since 0.1.0
	 *
	 * @param string $domain_name Domain name.
	 * @param int    $exclude     Domain id to ignore.
	 * @return bool
	 */
	public function name_exists( string $domain_name, int $exclude = 0 ): bool {
		$table = Tables::name( 'domains' );

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

	/**
	 * Domains created from an order.
	 *
	 * @since 0.1.0
	 *
	 * @param int $order_id Order id.
	 * @return array<int, array<string, mixed>>
	 */
	public function for_order( int $order_id ): array {
		return array_map( array( $this, 'hydrate' ), $this->rows( $this->select_sql() . ' WHERE d.order_id = %d ORDER BY d.id ASC', array( $order_id ) ) );
	}

	/**
	 * Lists domains.
	 *
	 * Arguments: `search`, `status`, `customer_id`, `expiring_before`, `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,
				'expiring_before' => '',
				'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[] = '(d.domain_name LIKE %s OR d.registrar LIKE %s OR c.first_name LIKE %s OR c.last_name LIKE %s OR u.user_email LIKE %s)';
			$params  = array_merge( $params, array_fill( 0, 5, $like ) );
		}

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

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

		if ( '' !== (string) $args['expiring_before'] ) {
			$where[]  = 'd.expires_on IS NOT NULL AND d.expires_on <= %s';
			$params[] = (string) $args['expiring_before'];
		}

		$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}, d.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 domains per status.
	 *
	 * @since 0.1.0
	 *
	 * @return array<string, int>
	 */
	public function status_counts(): array {
		$table  = Tables::name( 'domains' );
		$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;
	}

	/**
	 * Number of invoice lines that refer to a domain.
	 *
	 * @since 0.1.0
	 *
	 * @param int $id Domain id.
	 * @return int
	 */
	public function invoice_reference_count( int $id ): int {
		$table = Tables::name( 'invoice_items' );

		return (int) $this->value( "SELECT COUNT(*) FROM {$table} WHERE related_type = 'domain' AND related_id = %d", array( $id ) );
	}

	/**
	 * Ids of domains that expire on a day.
	 *
	 * @since 0.1.0
	 *
	 * @param string $day   `YYYY-MM-DD`.
	 * @param int    $limit Maximum number of ids.
	 * @return int[]
	 */
	public function ids_expiring_on( string $day, int $limit = 500 ): array {
		$table = Tables::name( 'domains' );
		$rows  = $this->rows( "SELECT id FROM {$table} WHERE status IN ('active','grace','expired') AND expires_on = %s ORDER BY id ASC LIMIT %d", array( $day, max( 1, $limit ) ) );

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

	/**
	 * Ids of active domains that expire on or before a day.
	 *
	 * @since 0.1.0
	 *
	 * @param string $day   `YYYY-MM-DD`.
	 * @param int    $limit Maximum number of ids.
	 * @return int[]
	 */
	public function ids_renewing_by( string $day, int $limit = 1000 ): array {
		$table = Tables::name( 'domains' );
		$rows  = $this->rows( "SELECT id FROM {$table} WHERE status = 'active' AND expires_on IS NOT NULL AND expires_on <= %s ORDER BY expires_on ASC, id ASC LIMIT %d", array( $day, max( 1, $limit ) ) );

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

	/**
	 * Ids of active domains that expired before a day.
	 *
	 * @since 0.1.0
	 *
	 * @param string $day   `YYYY-MM-DD`; domains expiring strictly before it are returned.
	 * @param int    $limit Maximum number of ids.
	 * @return int[]
	 */
	public function ids_expired_before( string $day, int $limit = 500 ): array {
		$table = Tables::name( 'domains' );
		$rows  = $this->rows( "SELECT id FROM {$table} WHERE status = 'active' AND expires_on IS NOT NULL AND expires_on < %s ORDER BY id ASC LIMIT %d", array( $day, max( 1, $limit ) ) );

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

	/**
	 * Inserts a domain.
	 *
	 * @since 0.1.0
	 *
	 * @param array<string, mixed> $data Domain columns; `customer_id` and `domain_name` are required.
	 * @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( 'domains' ), $row, $this->formats( self::COLUMNS, $row ) );
	}

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

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

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

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

	/**
	 * The SELECT part shared by queries.
	 *
	 * @return string
	 */
	private function select_sql(): string {
		return "SELECT d.*, 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 {$this->from_clause()}";
	}

	/**
	 * Keeps known columns, stores name servers as lines and turns empty dates into NULL.
	 *
	 * @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['nameservers'] ) && is_array( $row['nameservers'] ) ) {
			$row['nameservers'] = implode( "\n", $row['nameservers'] );
		}

		foreach ( array( 'auto_renew', 'whois_privacy' ) as $column ) {
			if ( isset( $row[ $column ] ) ) {
				$row[ $column ] = $row[ $column ] ? 1 : 0;
			}
		}

		foreach ( array( 'registered_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 {
		foreach ( array( 'id', 'customer_id', 'order_id', 'service_id', 'renewal_years' ) as $column ) {
			$row[ $column ] = (int) $row[ $column ];
		}

		$row['auto_renew']    = (bool) $row['auto_renew'];
		$row['whois_privacy'] = (bool) $row['whois_privacy'];
		$row['registered_on'] = (string) $row['registered_on'];
		$row['expires_on']    = (string) $row['expires_on'];
		$row['notes']         = (string) $row['notes'];
		$row['nameservers']   = array_values( array_filter( preg_split( '/\R/', (string) $row['nameservers'] ) ) );

		$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;
	}
}