<?php
/**
 * Report queries.
 *
 * @package HostKhata
 */

namespace HostKhata\Database\Repositories;

defined( 'ABSPATH' ) || exit;

use HostKhata\Database\Tables;

/**
 * Totals and breakdowns for the dashboard and the reports. Read only.
 *
 * Money is always in minor units. Date ranges are `YYYY-MM-DD` and include both
 * ends. Payments count only when confirmed; refunds are subtracted.
 *
 * @since 0.1.0
 */
final class ReportRepository extends AbstractRepository {

	/**
	 * Money received per month, refunds subtracted.
	 *
	 * @since 0.1.0
	 *
	 * @param string $from First day.
	 * @param string $to   Last day.
	 * @return array<int, array{month: string, total: int, payments: int}>
	 */
	public function revenue_by_month( string $from, string $to ): array {
		$table = Tables::name( 'payments' );
		$rows  = $this->rows(
			"SELECT DATE_FORMAT( created_at_gmt, '%%Y-%%m' ) AS month, SUM( CASE WHEN type = 'refund' THEN -amount ELSE amount END ) AS total, SUM( type = 'payment' ) AS payments FROM {$table} WHERE status = 'completed' AND created_at_gmt >= %s AND created_at_gmt < %s GROUP BY month ORDER BY month ASC",
			array( $from . ' 00:00:00', $this->next_day( $to ) . ' 00:00:00' )
		);

		return array_map(
			static function ( array $row ) {
				return array(
					'month'    => (string) $row['month'],
					'total'    => (int) $row['total'],
					'payments' => (int) $row['payments'],
				);
			},
			$rows
		);
	}

	/**
	 * Money received per payment method.
	 *
	 * @since 0.1.0
	 *
	 * @param string $from First day.
	 * @param string $to   Last day.
	 * @return array<int, array{method: string, total: int, payments: int}>
	 */
	public function revenue_by_method( string $from, string $to ): array {
		$table = Tables::name( 'payments' );
		$rows  = $this->rows(
			"SELECT gateway, SUM( CASE WHEN type = 'refund' THEN -amount ELSE amount END ) AS total, SUM( type = 'payment' ) AS payments FROM {$table} WHERE status = 'completed' AND created_at_gmt >= %s AND created_at_gmt < %s GROUP BY gateway ORDER BY total DESC",
			array( $from . ' 00:00:00', $this->next_day( $to ) . ' 00:00:00' )
		);

		return array_map(
			static function ( array $row ) {
				return array(
					'method'   => '' !== (string) $row['gateway'] ? (string) $row['gateway'] : '—',
					'total'    => (int) $row['total'],
					'payments' => (int) $row['payments'],
				);
			},
			$rows
		);
	}

	/**
	 * Received, refunded and invoiced in a period.
	 *
	 * @since 0.1.0
	 *
	 * @param string $from First day.
	 * @param string $to   Last day.
	 * @return array{received: int, refunded: int, invoiced: int, invoices: int, tax: int}
	 */
	public function totals( string $from, string $to ): array {
		$payments = Tables::name( 'payments' );
		$invoices = Tables::name( 'invoices' );

		$money = $this->row(
			"SELECT COALESCE( SUM( CASE WHEN type = 'payment' THEN amount ELSE 0 END ), 0 ) AS received, COALESCE( SUM( CASE WHEN type = 'refund' THEN amount ELSE 0 END ), 0 ) AS refunded FROM {$payments} WHERE status = 'completed' AND created_at_gmt >= %s AND created_at_gmt < %s",
			array( $from . ' 00:00:00', $this->next_day( $to ) . ' 00:00:00' )
		);

		$billed = $this->row(
			"SELECT COUNT(*) AS invoices, COALESCE( SUM( total ), 0 ) AS invoiced, COALESCE( SUM( tax_total ), 0 ) AS tax FROM {$invoices} WHERE status NOT IN ('draft','cancelled') AND issue_date >= %s AND issue_date <= %s",
			array( $from, $to )
		);

		return array(
			'received' => (int) ( $money['received'] ?? 0 ),
			'refunded' => (int) ( $money['refunded'] ?? 0 ),
			'invoiced' => (int) ( $billed['invoiced'] ?? 0 ),
			'invoices' => (int) ( $billed['invoices'] ?? 0 ),
			'tax'      => (int) ( $billed['tax'] ?? 0 ),
		);
	}

	/**
	 * Tax charged on invoices issued in a period, per rate.
	 *
	 * Lines copied from orders keep the tax amount but not the rate, so the rate
	 * is worked out from the amounts and rounded to the nearest half percent.
	 *
	 * @since 0.1.0
	 *
	 * @param string $from First day.
	 * @param string $to   Last day.
	 * @return array<int, array{rate_bp: int, net: int, tax: int}>
	 */
	public function tax_by_rate( string $from, string $to ): array {
		$items    = Tables::name( 'invoice_items' );
		$invoices = Tables::name( 'invoices' );

		$rows = $this->rows(
			"SELECT CASE WHEN ii.tax_rate_bp > 0 THEN ii.tax_rate_bp WHEN ii.tax_amount <> 0 AND ii.quantity * ii.unit_price <> 0 THEN ROUND( ii.tax_amount * 10000 / ( ii.quantity * ii.unit_price ) / 50 ) * 50 ELSE 0 END AS rate_bp, SUM( ii.quantity * ii.unit_price ) AS net, SUM( ii.tax_amount ) AS tax FROM {$items} ii INNER JOIN {$invoices} i ON i.id = ii.invoice_id WHERE i.status NOT IN ('draft','cancelled') AND i.issue_date >= %s AND i.issue_date <= %s GROUP BY rate_bp HAVING tax <> 0 OR rate_bp > 0 ORDER BY rate_bp ASC",
			array( $from, $to )
		);

		return array_map(
			static function ( array $row ) {
				return array(
					'rate_bp' => (int) $row['rate_bp'],
					'net'     => (int) $row['net'],
					'tax'     => (int) $row['tax'],
				);
			},
			$rows
		);
	}

	/**
	 * What is owed, by how late it is.
	 *
	 * @since 0.1.0
	 *
	 * @param string $today Today, `YYYY-MM-DD`.
	 * @return array<int, array{bucket: string, invoices: int, amount: int}> Every bucket, in order, even when empty.
	 */
	public function aging( string $today ): array {
		$table = Tables::name( 'invoices' );
		$rows  = $this->rows(
			"SELECT CASE WHEN due_date >= %s THEN 'current' WHEN DATEDIFF( %s, due_date ) <= 30 THEN '1_30' WHEN DATEDIFF( %s, due_date ) <= 60 THEN '31_60' WHEN DATEDIFF( %s, due_date ) <= 90 THEN '61_90' ELSE '90_plus' END AS bucket, COUNT(*) AS invoices, SUM( total - credit_applied - amount_paid ) AS amount FROM {$table} WHERE status IN ('unpaid','partially_paid') GROUP BY bucket",
			array( $today, $today, $today, $today )
		);

		$found = array();

		foreach ( $rows as $row ) {
			$found[ (string) $row['bucket'] ] = array(
				'invoices' => (int) $row['invoices'],
				'amount'   => (int) $row['amount'],
			);
		}

		$result = array();

		foreach ( array( 'current', '1_30', '31_60', '61_90', '90_plus' ) as $bucket ) {
			$result[] = array(
				'bucket'   => $bucket,
				'invoices' => $found[ $bucket ]['invoices'] ?? 0,
				'amount'   => $found[ $bucket ]['amount'] ?? 0,
			);
		}

		return $result;
	}

	/**
	 * The customers who paid the most in a period.
	 *
	 * @since 0.1.0
	 *
	 * @param string $from  First day.
	 * @param string $to    Last day.
	 * @param int    $limit Maximum number of customers.
	 * @return array<int, array{customer_id: int, name: string, email: string, total: int}>
	 */
	public function top_customers( string $from, string $to, int $limit = 10 ): array {
		$payments  = Tables::name( 'payments' );
		$customers = Tables::name( 'customers' );
		$rows      = $this->rows(
			"SELECT c.id, c.first_name, c.last_name, c.company, u.user_email, SUM( CASE WHEN p.type = 'refund' THEN -p.amount ELSE p.amount END ) AS total FROM {$payments} p INNER JOIN {$customers} c ON c.id = p.customer_id LEFT JOIN {$this->wpdb->users} u ON u.ID = c.user_id WHERE p.status = 'completed' AND p.created_at_gmt >= %s AND p.created_at_gmt < %s GROUP BY c.id, c.first_name, c.last_name, c.company, u.user_email HAVING total > 0 ORDER BY total DESC LIMIT %d",
			array( $from . ' 00:00:00', $this->next_day( $to ) . ' 00:00:00', max( 1, $limit ) )
		);

		return array_map(
			static function ( array $row ) {
				$name = trim( $row['first_name'] . ' ' . $row['last_name'] );

				return array(
					'customer_id' => (int) $row['id'],
					'name'        => '' !== $name ? $name : (string) $row['company'],
					'email'       => (string) $row['user_email'],
					'total'       => (int) $row['total'],
				);
			},
			$rows
		);
	}

	/**
	 * Counts for the dashboard.
	 *
	 * @since 0.1.0
	 *
	 * @param string $today  Today, `YYYY-MM-DD`.
	 * @param string $month  First day of this month, `YYYY-MM-DD`.
	 * @param string $in_30  Date 30 days from today.
	 * @return array<string, int>
	 */
	public function dashboard_counts( string $today, string $month, string $in_30 ): array {
		$t = static function ( string $key ) {
			return Tables::name( $key );
		};

		$count = function ( string $sql, array $params = array() ): int {
			return (int) $this->value( $sql, $params );
		};

		return array(
			'customers'        => $count( "SELECT COUNT(*) FROM {$t( 'customers' )} WHERE status <> 'anonymised'" ),
			'new_customers'    => $count( "SELECT COUNT(*) FROM {$t( 'customers' )} WHERE created_at_gmt >= %s", array( $month . ' 00:00:00' ) ),
			'setup_queue'      => $count( "SELECT COUNT(*) FROM {$t( 'orders' )} WHERE status IN ('paid_awaiting_setup','processing')" ),
			'orders_waiting'   => $count( "SELECT COUNT(*) FROM {$t( 'orders' )} WHERE status IN ('pending','awaiting_payment')" ),
			'overdue_invoices' => $count( "SELECT COUNT(*) FROM {$t( 'invoices' )} WHERE status IN ('unpaid','partially_paid') AND due_date < %s", array( $today ) ),
			'overdue_amount'   => $count( "SELECT COALESCE( SUM( total - credit_applied - amount_paid ), 0 ) FROM {$t( 'invoices' )} WHERE status IN ('unpaid','partially_paid') AND due_date < %s", array( $today ) ),
			'outstanding'      => $count( "SELECT COALESCE( SUM( total - credit_applied - amount_paid ), 0 ) FROM {$t( 'invoices' )} WHERE status IN ('unpaid','partially_paid')" ),
			'pending_payments' => $count( "SELECT COUNT(*) FROM {$t( 'payments' )} WHERE type = 'payment' AND status = 'pending'" ),
			'services_due'     => $count( "SELECT COUNT(*) FROM {$t( 'services' )} WHERE status = 'active' AND next_due_date IS NOT NULL AND next_due_date <= %s", array( $in_30 ) ),
			'domains_expiring' => $count( "SELECT COUNT(*) FROM {$t( 'domains' )} WHERE status = 'active' AND expires_on IS NOT NULL AND expires_on <= %s", array( $in_30 ) ),
			'active_services'  => $count( "SELECT COUNT(*) FROM {$t( 'services' )} WHERE status = 'active'" ),
			'active_domains'   => $count( "SELECT COUNT(*) FROM {$t( 'domains' )} WHERE status = 'active'" ),
			'failed_emails'    => $count( "SELECT COUNT(*) FROM {$t( 'email_queue' )} WHERE status = 'failed'" ),
		);
	}

	/**
	 * Day after a date, so a range can be compared with `<`.
	 *
	 * @param string $date `YYYY-MM-DD`.
	 * @return string
	 */
	private function next_day( string $date ): string {
		return gmdate( 'Y-m-d', strtotime( $date . ' +1 day UTC' ) );
	}
}