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

namespace HostKhata\Database\Repositories;

defined( 'ABSPATH' ) || exit;

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

/**
 * Reads and writes invoices and their lines.
 *
 * "Overdue" is not stored: it is an unpaid or partly paid invoice whose due date
 * has passed, worked out when it is read.
 *
 * @since 0.1.0
 */
final class InvoiceRepository extends AbstractRepository {

	/**
	 * Sortable columns: public name to SQL expression.
	 */
	const ORDERBY = array(
		'id'      => 'i.id',
		'number'  => 'i.invoice_number',
		'issued'  => 'i.issue_date',
		'due'     => 'i.due_date',
		'total'   => 'i.total',
		'status'  => 'i.status',
		'created' => 'i.created_at_gmt',
	);

	/**
	 * Columns written to the invoices table and their formats.
	 */
	const COLUMNS = array(
		'invoice_number' => '%s',
		'customer_id'    => '%d',
		'order_id'       => '%d',
		'type'           => '%s',
		'status'         => '%s',
		'issue_date'     => '%s',
		'due_date'       => '%s',
		'subtotal'       => '%d',
		'discount_total' => '%d',
		'tax_total'      => '%d',
		'credit_applied' => '%d',
		'total'          => '%d',
		'amount_paid'    => '%d',
		'public_token'   => '%s',
		'notes'          => '%s',
		'paid_at_gmt'    => '%s',
	);

	/**
	 * Columns written to the invoice_items table and their formats.
	 */
	const ITEM_COLUMNS = array(
		'invoice_id'   => '%d',
		'item_type'    => '%s',
		'description'  => '%s',
		'related_type' => '%s',
		'related_id'   => '%d',
		'quantity'     => '%d',
		'unit_price'   => '%d',
		'tax_rate_bp'  => '%d',
		'tax_amount'   => '%d',
		'total'        => '%d',
		'period_start' => '%s',
		'period_end'   => '%s',
	);

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

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

	/**
	 * Finds an invoice by its public link token.
	 *
	 * @since 0.1.0
	 *
	 * @param string $token Token.
	 * @return array<string, mixed>|null
	 */
	public function find_by_token( string $token ): ?array {
		return '' === $token ? null : $this->find_by( 'i.public_token = %s', $token );
	}

	/**
	 * Invoices 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 i.order_id = %d ORDER BY i.id ASC', array( $order_id ) ) );
	}

	/**
	 * Lists invoices.
	 *
	 * Arguments: `search`, `status` (a stored status or `overdue`), `customer_id`,
	 * `order_id`, `hide_drafts`, `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,
				'order_id'    => 0,
				'hide_drafts' => false,
				'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[] = '(i.invoice_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 ( 'overdue' === $args['status'] ) {
			$where[]  = "i.status IN ('unpaid','partially_paid') AND i.due_date < %s";
			$params[] = Date::today_gmt();
		} elseif ( '' !== (string) $args['status'] ) {
			$where[]  = 'i.status = %s';
			$params[] = (string) $args['status'];
		}

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

		if ( ! empty( $args['hide_drafts'] ) ) {
			$where[] = "i.status <> 'draft'";
		}

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

		$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}, i.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 invoices per status, with `overdue` counted on top.
	 *
	 * @since 0.1.0
	 *
	 * @return array<string, int>
	 */
	public function status_counts(): array {
		$table  = Tables::name( 'invoices' );
		$counts = array();

		foreach ( $this->rows( "SELECT status, COUNT(*) AS total FROM {$table} GROUP BY status" ) as $row ) {
			$counts[ (string) $row['status'] ] = (int) $row['total'];
		}

		$counts['overdue'] = (int) $this->value( "SELECT COUNT(*) FROM {$table} WHERE status IN ('unpaid','partially_paid') AND due_date < %s", array( Date::today_gmt() ) );

		return $counts;
	}

	/**
	 * Amount still owed on a customer's open invoices.
	 *
	 * @since 0.1.0
	 *
	 * @param int $customer_id Customer id.
	 * @return int Minor units.
	 */
	public function outstanding_for_customer( int $customer_id ): int {
		$table = Tables::name( 'invoices' );

		return (int) $this->value( "SELECT COALESCE( SUM( total - credit_applied - amount_paid ), 0 ) FROM {$table} WHERE customer_id = %d AND status IN ('unpaid','partially_paid')", array( $customer_id ) );
	}

	/**
	 * Tells whether any invoice line refers to a record.
	 *
	 * @since 0.1.0
	 *
	 * @param string $related_type `service` or `domain`.
	 * @param int    $related_id   Record id.
	 * @return int Number of lines.
	 */
	public function lines_for_record( string $related_type, int $related_id ): int {
		$table = Tables::name( 'invoice_items' );

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

	/**
	 * Ids of open invoices that fall due 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_due_on( string $day, int $limit = 500 ): array {
		$table = Tables::name( 'invoices' );
		$rows  = $this->rows( "SELECT id FROM {$table} WHERE status IN ('unpaid','partially_paid') AND due_date = %s ORDER BY id ASC LIMIT %d", array( $day, max( 1, $limit ) ) );

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

	/**
	 * Tells whether a live invoice already bills a record for a period.
	 *
	 * @since 0.1.0
	 *
	 * @param string $related_type `service` or `domain`.
	 * @param int    $related_id   Record id.
	 * @param string $period_start Start of the billed period, `YYYY-MM-DD`.
	 * @return bool
	 */
	public function has_period_line( string $related_type, int $related_id, string $period_start ): bool {
		$items    = Tables::name( 'invoice_items' );
		$invoices = Tables::name( 'invoices' );

		return (int) $this->value(
			"SELECT COUNT(*) FROM {$items} ii INNER JOIN {$invoices} i ON i.id = ii.invoice_id WHERE ii.related_type = %s AND ii.related_id = %d AND ii.period_start = %s AND i.status <> 'cancelled'",
			array( $related_type, $related_id, $period_start )
		) > 0;
	}

	/**
	 * Ids of open invoices that were due before a day.
	 *
	 * @since 0.1.0
	 *
	 * @param string $day   `YYYY-MM-DD`; invoices due strictly before it are returned.
	 * @param int    $limit Maximum number of ids.
	 * @return int[]
	 */
	public function ids_due_before( string $day, int $limit = 500 ): array {
		$table = Tables::name( 'invoices' );
		$rows  = $this->rows( "SELECT id FROM {$table} WHERE status IN ('unpaid','partially_paid') AND due_date < %s ORDER BY id ASC LIMIT %d", array( $day, max( 1, $limit ) ) );

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

	/**
	 * Ids of records that have an open invoice line due before a day.
	 *
	 * @since 0.1.0
	 *
	 * @param string $related_type `service` or `domain`.
	 * @param string $day          `YYYY-MM-DD`.
	 * @param int    $limit        Maximum number of ids.
	 * @return int[]
	 */
	public function overdue_record_ids( string $related_type, string $day, int $limit = 500 ): array {
		$items    = Tables::name( 'invoice_items' );
		$invoices = Tables::name( 'invoices' );
		$rows     = $this->rows(
			"SELECT DISTINCT ii.related_id AS id FROM {$items} ii INNER JOIN {$invoices} i ON i.id = ii.invoice_id WHERE ii.related_type = %s AND i.status IN ('unpaid','partially_paid') AND i.due_date < %s ORDER BY ii.related_id ASC LIMIT %d",
			array( $related_type, $day, max( 1, $limit ) )
		);

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

	/**
	 * Inserts an invoice.
	 *
	 * @since 0.1.0
	 *
	 * @param array<string, mixed> $data Invoice columns.
	 * @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( 'invoices' ), $row, $this->formats( self::COLUMNS, $row ) );
	}

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

	/**
	 * Number of payments and refunds of any status recorded on an invoice.
	 *
	 * @since 0.1.0
	 *
	 * @param int $invoice_id Invoice id.
	 * @return int
	 */
	public function payment_count( int $invoice_id ): int {
		return (int) $this->value( 'SELECT COUNT(*) FROM ' . Tables::name( 'payments' ) . ' WHERE invoice_id = %d', array( $invoice_id ) );
	}

	/**
	 * Deletes an invoice and its lines.
	 *
	 * @since 0.1.0
	 *
	 * @param int $id Invoice id.
	 * @return bool
	 */
	public function delete( int $id ): bool {
		$this->wpdb->delete( Tables::name( 'invoice_items' ), array( 'invoice_id' => $id ), array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching

		return false !== $this->wpdb->delete( Tables::name( 'invoices' ), array( 'id' => $id ), array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
	}

	/**
	 * Replaces all lines of an invoice.
	 *
	 * @since 0.1.0
	 *
	 * @param int                              $invoice_id Invoice id.
	 * @param array<int, array<string, mixed>> $items      Lines.
	 * @return bool False when a line could not be saved.
	 */
	public function replace_items( int $invoice_id, array $items ): bool {
		$table = Tables::name( 'invoice_items' );

		$this->wpdb->delete( $table, array( 'invoice_id' => $invoice_id ), array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching

		foreach ( $items as $item ) {
			$row = array_intersect_key( $item, self::ITEM_COLUMNS );

			foreach ( array( 'period_start', 'period_end' ) as $column ) {
				if ( array_key_exists( $column, $row ) && ( null === $row[ $column ] || '' === $row[ $column ] ) ) {
					unset( $row[ $column ] );
				}
			}

			$row['invoice_id'] = $invoice_id;

			if ( 0 === $this->insert_row( $table, $row, $this->formats( self::ITEM_COLUMNS, $row ) ) ) {
				return false;
			}
		}

		return true;
	}

	/**
	 * Takes a named lock; used to serialise payments on one invoice.
	 *
	 * @since 0.1.0
	 *
	 * @param int $invoice_id Invoice id.
	 * @return bool
	 */
	public function lock( int $invoice_id ): bool {
		return 1 === (int) $this->wpdb->get_var( $this->wpdb->prepare( 'SELECT GET_LOCK( %s, 5 )', $this->lock_name( $invoice_id ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared
	}

	/**
	 * Releases the lock taken by {@see InvoiceRepository::lock()}.
	 *
	 * @since 0.1.0
	 *
	 * @param int $invoice_id Invoice id.
	 * @return void
	 */
	public function unlock( int $invoice_id ): void {
		$this->wpdb->get_var( $this->wpdb->prepare( 'SELECT RELEASE_LOCK( %s )', $this->lock_name( $invoice_id ) ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared
	}

	/**
	 * Lock name for an invoice, unique per site.
	 *
	 * @param int $invoice_id Invoice id.
	 * @return string
	 */
	private function lock_name( int $invoice_id ): string {
		return $this->wpdb->prefix . 'hk_invoice_' . $invoice_id;
	}

	/**
	 * Finds one invoice by a WHERE clause and attaches its lines.
	 *
	 * @param string     $where Clause with one placeholder.
	 * @param int|string $value Value.
	 * @return array<string, mixed>|null
	 */
	private function find_by( string $where, $value ): ?array {
		$row = $this->row( $this->select_sql() . " WHERE {$where}", array( $value ) );

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

		$invoice          = $this->hydrate( $row );
		$invoice['items'] = $this->items( $invoice['id'] );

		return $invoice;
	}

	/**
	 * Lines of an invoice.
	 *
	 * @param int $invoice_id Invoice id.
	 * @return array<int, array<string, mixed>>
	 */
	private function items( int $invoice_id ): array {
		$table = Tables::name( 'invoice_items' );
		$rows  = $this->rows( "SELECT * FROM {$table} WHERE invoice_id = %d ORDER BY id ASC", array( $invoice_id ) );

		return array_map(
			static function ( array $row ) {
				foreach ( array( 'id', 'invoice_id', 'related_id', 'quantity', 'unit_price', 'tax_rate_bp', 'tax_amount', 'total' ) as $column ) {
					$row[ $column ] = (int) $row[ $column ];
				}

				$row['period_start'] = (string) $row['period_start'];
				$row['period_end']   = (string) $row['period_end'];

				return $row;
			},
			$rows
		);
	}

	/**
	 * Casts numeric columns and adds the balance and overdue flag.
	 *
	 * @param array<string, mixed> $row Database row.
	 * @return array<string, mixed>
	 */
	private function hydrate( array $row ): array {
		foreach ( array( 'id', 'customer_id', 'order_id', 'subtotal', 'discount_total', 'tax_total', 'credit_applied', 'total', 'amount_paid' ) as $column ) {
			$row[ $column ] = (int) $row[ $column ];
		}

		$row['balance']    = $row['total'] - $row['credit_applied'] - $row['amount_paid'];
		$row['is_overdue'] = in_array( $row['status'], array( 'unpaid', 'partially_paid' ), true ) && (string) $row['due_date'] < Date::today_gmt();

		foreach ( array( 'public_token', 'notes', 'paid_at_gmt', 'customer_first_name', 'customer_last_name', 'customer_company', 'customer_email' ) 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;
	}

	/**
	 * Keeps known columns and turns empty optional values 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 );

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

		return $row;
	}

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

		return "FROM {$invoices} i LEFT JOIN {$customers} c ON c.id = i.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 i.*, 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()}";
	}
}